五千字 DDL、DML、DQL、DCL 超详解

SQL语句，根据其功能，主要分为四类：DDL、DML、DQL、DCL。

• DDL (Data Definition Language) 数据定义语言，用来定义数据库对象(数据库，表， 字段)

• DML (Data Manipulation Languag) 数据操作语言，用来对数据库表中的数据进行增删改

• DQL (Data Query Language) 数据查询语言，用来查询数据库中表的记录

• DCL (Data Control Language) 数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

DDL-数据库

SHOW DATABASES;

CRREATE DATABASE 数据库名;

USE 数据库名;

SELECT DATABASES();

DROP DATABASE 数据库名;


查询所有数据库

show databases ;

查询当前数据库

select database();

创建数据库

create database() [if not exists]  数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;

例: create database itcast;

删除数据库

drop database [if exists]数据库名

切换数据库

use 数据库名 ;

DDL-表操作

SHOW TABLES;

CREATE TABLE 表名(字段 字段类型 , 字段 字段类型);

DESC 表名;

SHOW CREATE TABLE 表名

ALTER TABLE 表名 ADD/MODIFY/CHANGE/DROP/RENAME TO..;

DROP TABLE 表名;

查询当前数据库所有表

show tables;

查看指定表结构

desc 表名 ;

通过这条指令，我们可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信息。

查看指定表的建表语句

show create table 表名 ;

通过这条指令，主要是用来查看建表语句的，而有部分参数我们在创建表的时候，并未指定也会查询 到，因为这部分是数据库的默认值，如：存储引擎、字符集等。

创建表结构

创建表示例:

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE NOT NULL
);

这个例子中，user_id是主键，它自动递增；name是一个最多可以存储100个字符的字符串；email也是一个最多可以存储100个字符的字符串，它被标记为UNIQUE和NOT NULL，意味着它必须是唯一的且不能为空。

修改表

添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [ COMMENT 注释 ] [ 约束 ];

假设我们有一个名为students的表，现在我们想要添加一个新的字段age，数据类型为整数:

ALTER TABLE students ADD COLUMN age INT;

修改数据类型

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

ALTER TABLE Student MODIFY id TINYINT;

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

ALTER TABLE Student CHANGE username usernamechange varchar(30) COMMENT '昵称';

删除字段

ALTER TABLE 表名 DROP 字段名;

ALTER TABLE Student DROP username;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

将Student 表的表名修改为 Student1

 ALTER TABLE Student RENAME TO Student1;

删除表

DROP TABLE [ IF EXISTS ] 表名;

删除指定表, 并重新创建表

TRUNCATE TABLE 表名;

注意: 在删除表的时候，表中的全部数据也都会被删除。

DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进 行增、删、改操作。 添加数据（INSERT） 修改数据（UPDATE） 删除数据（DELETE)

添加数据

给指定字段添加数据

 INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

查询数据的SQL语句

select * from employee;

给全部字段添加数据

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, ...) ;

修改数据

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

修改id为1的数据, 将name修改为xiaobai

update employee set name = 'xiaobai'

删除数据

DELETE FROM 表明 [ WHERE 条件 ] ;

删除name为小明的员工

delete from employee where name= 'xiaoming'

• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即 可)。

DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记 录。

DQL 查询语句，语法结构如下：

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

基础查询

SELECT * FROM 表名 ;

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;

* 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）

字段设置别名

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;

SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名

条件查询

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

查询年龄等于18 或 20 或 40 的员工信息

select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,20,40);

查询姓名为两个字的员工信息

select * from emp where name like '__';

查询身份证号最后一位是X的员工信息

select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

聚合函数

介绍 将一列数据作为一个整体，进行纵向计算 。

常见的聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法

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

注意 : NULL值是不参与所有聚合函数运算的

select count(*) from emp; -- 统计的是总记录数
select count(id) from emp; -- 统计的是id字段不为null的记录数

• count(1) 是一个聚合函数，用于计算所选记录的数量。括号中的参数表示要对哪些列进行计数，这里使用数字1表示对所有记录进行计数。

分组查询

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

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组 之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB

统计各个工作地址上班的男性及女性员工的数量

select workaddress, gender, count(*) '数量' from  emp GROUP BY gender,workaddress;

排序查询

语法

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

排序方式

ASC : 升序(默认值) DESC: 降序

select *  from emp ORDER BY age asc;
select * from emp order by age;

 根据入职时间, 对员工进行降序排序

select * from emp order by entrydate desc;

根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序

select * from emp order by age asc , entrydate desc;

分页查询

语法

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

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

查询第1页员工数据, 每页展示10条记录

select * from emp limit 0,10;
select * from emp limit 10;

查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数

select * from emp limit 10,10;

查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。

select * from emp where gender = '男' and ( age between 20 and 40 ) and name like'___';

执行顺序

.=====>

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

查询年龄大于15的员工姓名、年龄，并根据年龄进行升序排序。

select name , age from emp where age > 15 order by age asc;

在查询时，我们给emp表起一个别名 e，然后在select 及 where中使用该别名。

select e.name , e.age from emp e where e.age > 15 order by age asc;

正常执行

此时，此时我们可以给select后面的字段起别名，然后在 where 中使用这个别名，然后看看是否可 以执行成功。

select e.name ename , e.age eage from emp e where eage > 15 order by age asc;

SQL报错

由此我们可以得出结论: from 先执行，然后执行 where ， 再执行select 。

select e.name ename , e.age eage from emp e where e.age > 15 order by eage asc;

结果执行成功。 那么也就验证了: order by 是在select 语句之后执行的。

综上所述，我们可以看到DQL语句的执行顺序为： from ... where ... group by ... having ... select ... order by ... limit ...

DCL

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访 问权限。

管理用户

查询用户

select * from mysql.user;

查询的结果如下:

其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以 远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一 个用户。

创建用户

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

 create user 'xiaobai'@'localhost' identified by '123456';

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
 alter user 'xiaobai'@'%' identified with mysql_native_password by '1234';

删除用户

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

drop user 'xiaobai'@'localhost';

注意事项:

在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。

主机名可以使用 % 通配。

这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库 管理员）使用。

权限控制
 

MySQL中定义了很多种权限，但是常用的就以下几种：

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

查询权限

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

show grants for 'xiaobai'@'%';

授予权限

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

grant all on test.* to 'xiaobai'@'%';

撤销权限

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

revoke all on test.* from 'xiaobai'@'%';