SQL命令大全 包含DDL、DML、DQL、DCL所有命令（非常全面）_ddl,dcl,dml,dql语句命令

目录

常用命令

一、DDL-定义数据库、表结构

1. 数据库操作

（1） 查询所有数据库

（2）查询当前数据库：注意不要丢掉（）

（3）创建数据库：

（4） 删除数据库

（5） 使用数据库：

2.表操作

（1）  查询

· 查询当前数据库内所有表（前提是已通过USE指令使用该数据库）

· 查询表结构

· 查询指定表的建表语句

（2）创建

· 可用字段类型

· 创建实例

（3） 修改

· 添加字段

· 只修改数据类型

· 修改字段名和数据类型

· 删除字段

· 修改表名

· 删除表

3. DDL总结

（1）数据库操作

（2）表操作

二、DML-数据增删改

1. 添加数据

2.修改数据

3.删除数据

三、DQL-查询语句-select

1.基本查询

2.条件查询（where）

3.聚合函数（count、max、min、avg、sum）

4.分组查询（group by）

5.排序查询（order by）

6.分页查询（limit）

四、DCL—控制语言

1.管理用户

（1）查询用户

（2）创建用户

（3）修改密码

（4）删除用户

2.权限控制

（1）查询权限

（2）授予权限

（3）撤销权限

常用命令

以下内容学习自黑马程序员老师课程整理而来

SHOW DATABASES;

SELECT DATABASE();

CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
 
# 示例
CREATE DATABASE stu;
CREATE DATABASE IF NOT EXISTS stu;
CREATE DATABASE IF NOT EXISTS stu DEFAULT CHARSET utf8mb4;
CREATE DATABASE IF NOT EXISTS stu DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin；

DROP DATABASE [IF EXISTS] 数据库名称;

USE 数据库名称;

2.表操作

（1）  查询

· 查询当前数据库内所有表（前提是已通过USE指令使用该数据库）

注意这里的sys是系统里的数据库，不要随意在系统库中创建表结构。

SHOW TABLES;
 
# 示例
USE sys;
SHOW TABLES;

· 查询表结构

DESC 表名称;

· 查询指定表的建表语句

SHOW CREATE TABLE 表名称;

（2）创建

创建表，这里是分行输入的，同样[]里的内容是可选参数，可以省略选用默认值，需要注意，最后一行不要丢掉分号，最后一个字段n对应这行没有逗号

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
 
# 示例
CREATE TABLE try(
id INT COMMENT '编号',
name VARCHAR(50) COMMENT '姓名',
age INT COMMENT '年龄',
gender VARCHAR(1) COMMENT '性别'
) COMMENT '用户表';
 
# 展示一下前面两个命令
DESC try; # 查询表结构
SHOW CREATE TABLE try; # 查询指定表的建表语句

查询的结果如下

· 可用字段类型

这里可以设置的字段类型有很多种，主要可以分为三类：数值型、字符串型、日期时间型

数值类型大致可分为整数和小数，然后在对他们细分精度，范围越广精度越高，则所占大小就会越大。DECIMAL对应的范围需要用户自己设置，精度指这个数总共的位数，标度指小数点后的位数，如123.45，精度为5，标度为2，设置就是decimal(5,2) ，取值范围是-999.99~999.99，因此具体大小与设置有关。 float和double也可以这样设置，注意float的有效位为7，double为15，decimal为28，即当精度超出有效位时会四舍五入为有效位。例如float f = 345.98756f，结果显示为345.9876，只显示7个有效位，对最后一位数四舍五入。

如何能够更巧妙的利用这些数据类型来节省存储空间呢？例如针对年龄而言，不会出现负数，且不会很大，采用小整数即可，且选择无符号范围（即不需要负号）。

CREATE TABLE try(
age TINYINT UNSIGNED COMMENT '年龄'
) COMMENT '用户表' ;

 字符串类型的可大致分为CHAR、VARCHAR、BLOB、TEXT。

BLOB主要针对二进制数据，例如视频、音频、软件安装包，都是可以储存的，但很少这样做。

CHAR和VARCHAR后面必须要跟一参数，即当前字符串最大存储的字符数，而这两个的区别在于CHAR是定长字符串，设置的参数是多少他就是多长，哪怕存储的并没有达到，也会采用空格进行补位，但VARCHAR则是会根据输入的具体长度，参数只是限制最长输入长度。但正因为VARCHARVARCHAR是可变的，会根据内容来计算所占用的空间，所以相比CHAR而言性能较差。

举两个例子，若是设置用户名，我们要求用户输入不得大于50个字符，但这时有用户会输入1个2个或是49个，有很多种可能，这时应该选择哪个去存储呢？答案是VARCHAR(50)，因为选用CHAR会存在很大的内存浪费。若是设置性别变量呢？只有男或女，此时CHAR则更好，CHAR(1)。

 日期类型里需要注意的是TIMESTAMP是个时间戳，范围最大只取到了2038年。

· 创建实例

设计一张员工信息表，要求如下：

1. 编号（纯数字）
2. 员工工号 (字符串类型，长度不超过10位)
3. 员工姓名（字符串类型，长度不超过10位）
4. 性别（男/女，存储一个汉字）
5. 年龄（正常人年龄，不可能存储负数）
6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
7. 入职时间（取值年月日即可）

CREATE TABLE information(
num INT COMMENT '编号',
number VARCHAR(10) COMMENT '员工工号',
name VARCHAR(10) COMMENT '姓名',
gender CHAR(1) COMMENT '性别',
age TINYINT UNSIGNED COMMENT '年龄',
idcard CHAR(18) COMMENT '身份证号',
entrydate DATE COMMENT '入职时间'
) COMMENT '员工信息表';

（3） 修改

· 添加字段

ALTER TABLE 表名 ADD 字段名 类型 (长度) [COMMENT 注释] [约束];
 
# 示例：为information表增加一个新的字段“昵称”，nickname，类型为varchar(20)
ALTER TABLE information ADD nickname VARCHAR(20) COMMENT '昵称';

· 只修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

· 修改字段名和数据类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
 
# 示例：将information表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE information CHANGE nickname username varchar(30) COMMENT '昵称'; 

· 删除字段

ALTER TABLE 表名 DROP 字段名;
 
# 示例：将emp表的字段username删除
ALTER TABLE emp DROP username;

· 修改表名

ALTER TABLE 表名 RENAME TO 新表名;
 
# 示例：将information表的表名修改为 inform
ALTER TABLE information RENAME TO inform;

· 删除表

有两种操作，第一种是直接删除，表内数据和该表都不再存在

DROP TABLE [ IF EXISTS ] 表名;

第二种删除表，并重新创建表，只删除表内数据，但该表依旧存在

TRUNCATE TABLE 表名;

3. DDL总结

（1）数据库操作

SHOW DATABASES;
CREATE DATABASE 数据库名;
USE 数据库名;
SELECT DATABSES();
DROP DATABASES 数据库名;

（2）表操作

SHOW TABLES;
CREATE TABLE 表名（字段 字段类型,字段字段类型）；
DESC 表名;
SHOW CREATE TABLE 表名;
ALTER TABLE 表名 ADD/MODIFY/CHANGE/DROP/RENAME TO ...;
DROP 表名;
TRUNCATE 表名;

二、DML-数据增删改

针对的是数据库中表的数据记录，也就是对每一行数据的操作，包括三部分：添加数据、修改数据和删除数据

1. 添加数据

# 给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
# 给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);
# 批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...),(值1, 值2, ...),(值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...),(值1, 值2, ...),(值1, 值2, ...);
 
# 示例
INSERT INTO information(num, number, name, gender, age, idcard, entrydate) VALUES(1,'1','lucy','女',22,'x12345678912345678','2000-01-01');
INSERT INTO information VALUES(2,'2','lily','女',22,'x12345678912345678','2000-01-01');
INSERT INTO information VALUES(3,'3','jony','男',25,'145845678912345678','2005-01-01'),(4,'4','Jack','男',28,'145845678912345678','2003-01-01');

注意：插入时，指定字段需要与值的顺序一致；除数值型数据外，即字符型和日期型都要包含在单引号内；输入的数据需要符合对应字段类型的要求

2.修改数据

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;
 
# 示例
UPDATE information SET name = 'ZHANGSAN' WHERE num = 1; # 将id为1对应那条数据的name值改为ZHANGSAN
UPDATE information SET name = '李四', gender = '男' WHERE num = 2; # 修改一条数据中的两个字段数据，用逗号隔开
UPDATE information SET entrydate = '2000-01-01'; # 不加条件即修改表内所有数据

where后加条件，若不加，则修改整张表对应字段数据

3.删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;
 
# 示例
DELETE FROM information WHERE gender = '女'; # 删除符合条件的数据
DELETE FROM information; # 会删除整张表的数据

同样，where后加条件，若没有，则删除整张表的所有数据

该命令不能删除某一字段的值（可以用UPDATE），更新为空字符串，似乎数字类型的也要写上''，是不可以直接空着的

UPDATE information SET name = '' WHERE num = 2;

三、DQL-查询语句-select

查询的应用有很多，例如一些购物网站、学习网站等，想要在这些网站中看到我们想看到的数据，都是需要从数据库中进行查询并展示的。而且在查询的过程中，可能还会涉及到条件搜索、范围搜索或者排序、分页等操作。关键命令就是select，但不同的查询任务会跟不同的内容。

注意编写顺序如下，顺序不能换：

SELECT
 
        字段列表
 
FROM
 
        表名列表
 
WHERE
 
        条件列表
 
GROUP BY
 
        分组字段列表
 
HAVING
 
        分组后条件列表
 
ORDER BY
 
        排序字段列表
 
LIMIT
 
        分页参数

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ; # 查询多个字段
SELECT * FROM 表名 ; # 返回所有字段
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名; # 设置别名，AS可省略，增强字段可读性
SELECT DISTINCT 字段列表 FROM 表名; # 查询时会去除重复记录
 
# 示例
select name,workno,age from information; # 查询指定字段并返回
select * from information; # 查询所有字段
select id, workno, name, gender, age, idcard, workaddress, entrydate from information;
select workaddress as '工作地址' from information; # 起别名，方便查看，as可省略
select distinct workaddress as '工作地址' from information; # 对查询结果进行去重

SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

-- 查询年龄为88的员工
select * from information where age = 88;
 
-- 年龄小于等于20
select  * from information where age <= 20;
 
-- 查询没有身份证号的信息
select * from information where idcard is null;
 
-- 查询有身份证号的员工信息
select * from information where idcard is not null;
 
-- 查询年龄不为88
select * from information where age != 88;
select * from information where age <> 88;
 
-- 查询年龄在[15,20]的员工
select * from information where age >= 15 and age <=20;
select * from information where age >= 15 && age <=20;
select * from information where age between 15 and 20; # 注意数字的顺序不能颠倒
 
-- 查询性别为女且年龄小于25的员工信息
select * from information where gender = '女' and age <25;
 
-- 查询年龄等于18或20或40的员工信息
select * from information where age = 18 or age = 20 or age = 40;
select * from information where age in(18,20,40);
 
-- 查询姓名为两个字的员工信息 _ %
select * from information where name like '__'; # 注意加''
 
-- 查询身份证号最后一位是X的员工信息
select * from information where idcard like '%X';

SELECT 聚合函数(字段列表) FROM 表名 ;

-- 统计该企业员工数量
select count(*) from information;
select count(id) from information; # 统计id中有值的总数量
 
-- 统计平均年龄
select avg(age) from information;
 
-- 统计最大年龄
select max(age) from information;
 
-- 统计西安地区的员工年龄之和
select sum(age) from information where workaddress = '西安';

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];

-- 根据性别分组，统计男性员工和女性员工的数量
select gender, count(*) from information group by gender; 
# selec加上gender是为了分辨输出的数量各属于哪一组
 
-- 根据性别分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from information group by gender;
 
-- 查询年龄小于45的员工，根据工作地址分组，获得员工数量大于等于3的工作地址
select workaddress, count(*) address_count from information where age < 45 group by workaddress having count(*)>3; 
# selec加上count可以具体看到对应的员工数量，可以数量起个别名

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

--  根据年龄对公司的员工进行升序排序
select * from information order by age ; # 升序可省略ASC
select * from information order by age desc ; # 降序排序
 
-- 根据入职时间, 对员工进行降序排序
select * from information order by entrydate desc ;
 
-- 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from information order by age , entrydate desc ;

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

-- 查询第1页员工数据, 每页展示10条记录
select * from information limit 10 ;
 
-- 查询第2页员工数据, 每页展示10条记录，起始索引 = (页码-1)*页展示记录数
select * from information limit 10, 10 ;

-- 查询年龄为20,21,22,23岁的女性员工信息。
select * from information where gender = '女' and age in (20,21,22,23) ;
 
-- 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。
select * from information where gender = '男' and ( age between 20 and 40 ) and name like '___' ; # 条件较多，可以用小括号括起条件
 
--  统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
select gender, count(*) from information where age < 60 group by gender ;
 
-- 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name, age from information where age <= 35 order by age , entrydate desc ;
 
-- 查询性别为男，且年龄在20-40 岁(含)以内的员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序,只显示前5条数据。
select * from information where gender = '男' and age between 20 and 40 order by age , entrydate limit 5 ;

1.管理用户

（1）查询用户

用户所具有的权限信息都是存放在系统数据库mysql的user表当中的，所以我们可以直接访问mysql数据库，查询user表来看用户信息

use mysql;
select * from user;

可以查询到有以下四个用户，Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一 个用户，也就是说我们要创建或删除一个用户，需要用houst和user同时定位。Y，N表示对应是否有权限

（2）创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 

主机名限制的就是这个用户名在哪一个主机上可以访问当前mysql

-- 创建用户new, 只能够在当前主机localhost访问, 密码123456;
create user 'new'@'localhost' identified by '123456' ;
 
-- 创建用户new, 可以在任意主机访问该数据库, 密码123456;
create user 'new'@'%' identified by '123456' ; 
# %可以充当任何长度的字符，所以相当于任意主机都可以访问

用户创建好后，并没有为其分配权限，所以新创建的用户在各数据库的权限是N

（3）修改密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

-- 修改用户new的访问密码为1234;
alter user 'new'@'%' identified with mysql_native_password by '1234' ;

（4）删除用户

DROP USER '用户名'@'主机名' ;

-- 删除new'@'%用户
DROP USER 'new'@'%' ;

2.权限控制

常用的几种权限：

（1）查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

show grants for 'root'@'localhost' ;

（2）授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

create user 'new'@'localhost' identified by '1234' ;
grant all on study.* to 'new'@'localhost'
show grants for 'new'@'localhost' ;

（3）撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

revoke all on study.* from 'new'@'localhost' ;

多个权限之间，使用逗号分隔 ，授权时， 数据库名和表名可以使用 * 进行通配，代表所有。