MySQL 教程（基础篇）第06话：MySQL 常用 SQL 语句大全——DDL、DML 和 DCL 详解

❤️ 个人主页：水滴技术
🚀 支持水滴：点赞👍 + 收藏⭐ + 留言💬
🌸 订阅专栏：MySQL 教程：从入门到精通

---

大家好，我是水滴~~

根据对 RDBMS 赋予的指令类别的不同，SQL 语言可以分为三类（或者叫子语言）：DDL、DML 和 DCL。下面对它们进行详细介绍，以及在 MySQL 中各类 SQL 语句的操作示例。

一、DDL（数据定义语言）

DDL（Data Definition Language，数据定义语言）用来创建或删除数据库及表等对象。DDL 包含以下几种指令：CREATE、DROP 和 ALTER 等。

1. 数据库操作

可以使用 SHOW DATABASES 命令查看所有已创建的数据库。

1.1 创建数据库

创建一个数据库：

CREATE DATABASE <数据库名称>;

创建一个数据库，并且只有数据库不存在时才会创建（避免了因重复创建而报错）：

CREATE DATABASE IF NOT EXISTS <数据库名称>;

创建一个数据库，并指定默认字符集（可以通过 SHOW CHARACTER SET 命令显示所有字符集）：

CREATE DATABASE <数据库名称> CHARACTER SET = <字符集名称>;

创建一个数据库，并指定默认的排序规则（可以通过 SHOW COLLATION 命令显示所有的排序规则）：

CREATE DATABASE <数据库名称> COLLATE = <排序规则名称>;

1.2 修改数据库

数据库创建完后，其名称是不可以修改的。

修改数据库的默认字符集：

ALTER DATABASE <数据库名称> CHARACTER SET = <字符集名称>;

修改数据库的默认排序规则：

ALTER DATABASE <数据库名称> COLLATE <排序规则名称>;

1.3 删除数据库

删除一个数据库：

DROP DATABASE <数据库名称>;

删除一个数据库，并且只有数据库存在时才会删除（避免了因数据库不存在而报错）：

DROP DATABASE IF EXISTS <数据库名称>;

2. 表操作

数据库创建好后，可以通过 USE <数据库名称> 命令选择数据库为当前数据库，然后就可以操作该数据库中的表了；
可以使用 SHOW TABLES 命令查看当前数据库中所有已创建的表。

2.1 创建表

创建一个表相对较复杂些，这里需要重点讲一下，下面是建表的语法：

CREATE TABLE [IF NOT EXISTS] <表名> (
  <列名> <列数据类型> [列约束],
  <列名> <列数据类型> [列约束],
  <列名> <列数据类型> [列约束],
  [表约束]
) [表选项];

其中：

• IF NOT EXISTS：为非必填项，与上面创建数据库时用法一样，用于避免重复创建而报错。
• 表名：为该表指定一个名称。
• 列名：一个表中可以有多个列，每个列都需要指定一个名称。
• 列数据类型：每个列必须指定一个数据类型，并可以指定长度。例如：BIGINT，INT，VARCHAR(10)。 MySQL 中的数据类型较多，后面会出一篇文章介绍，这里就不展开说了。
• 列约束：可以为每个列设定约束，为非必填项。常用的约束有：
  • NOT NULL表示该列非空；
  • NULL表示该列可为空（默认）；
  • DEFAULT用于指定默认值；
  • AUTO_INCREMENT表示该列为自增序列，每个表只能为一个列设置自增序列；
  • COMMENT指定列的注释；
  • PRIMARY KEY指定该列为主键，一张表只能有一个主键；
  • UNIQUE 为该列创建一个唯一索引约束，该列中的值必须是不同的。
• 表约束：可以为该表添加一些额外约束，例如：
  • PRIMARY KEY：主键也可以在这里声明，需要指定列名和索引类型：PRIMARY KEY (列名) USING 索引类型，索引类型如：BTREE；
  • UNIQUE KEY ：唯一索引也可以在这里声明，需要指定索引名称、列名和索引类型：UNIQUE KEY 索引名称 (列名, ...) USING 索引类型；
  • KEY：用于创建其他类型的索引，需要指定索引名称、列名和索引类型：KEY 索引名称 (列名, ...) USING 索引类型。
• 表选项：可以为表设置一些选项，常见的用：
  • ENGING：指定表的存储引擎，例如：InnoDB 、MyISAM；
  • AUTO_INCREMENT：表中自增序列的初始值；
  • [DEFAULT] CHARSET：表的默认字符集，如果未指定则使用数据库的字符集；
  • [DEFAULT] COLLATE：表的默认排序规则；
  • COMMENT：表的注释。

下面是创建一个表的示例：

CREATE TABLE `table_name` (
  `column1` BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `column2` VARCHAR(32),
  `column3` INTEGER DEFAULT 18，
  `column4` INTEGER NOT NULL，
  KEY `i_column4` (`column4`) USING BTREE
) ENGING = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='表注释';

2.2 修改表

修改表的存储引擎：

ALTER TABLE <表名> ENGINE = <存储引擎名称>;

重置当前的自增序列值：

ALTER TABLE <表名> AUTO_INCREMENT = 15;

也可以多个选项一起修改，如修改默认字符集和注释：

ALTER TABLE <表名> CHARSET=utf8 COMMENT='表注释';

修改表名：

ALTER TABLE <表名> RENAME <新表名>;

添加列：

ALTER TABLE <表名> ADD <列名> <列数据类型> [列约束];

删除列：

ALTER TABLE <表名> DROP <列名>;

修改列名：

ALTER TABLE <表名> RENAME COLUMN <列名> TO <新列名>;

修改列属性：

ALTER TABLE <表名> MODIFY <列名> [列数据类型] [列约束];

添加索引：

ALTER TABLE <表名> ADD INDEX <索引名称> (<列名>, ...) USING <索引类型>;

删除索引：

ALTER TABLE <表名> DROP INDEX <索引名称>;

修改索引名称：

ALTER TABLE <表名> RENAME INDEX <索引名称> TO <新索引名称>;

2.3 删除表

删除一个表：

DROP TABLE <表名>;

删除一个表，并且只有在表存在时才会删除（避免了因表不存在而报错）：

DROP TABLE IF EXISTS <表名>;

删除多个表：

DROP TABLE <表名1>, <表名2>, ...;

二、DML（数据操纵语言）

DML（Data Manipulation Language，数据操作语言）用来查询或变更表中的记录。DML 包含以下几种指令：SELECT、INSERT、UPDATE 和 DELETE 等。

1. 插入语句（INSERT）

插入一条记录：

INSERT INTO <表名> (<列名1>, <列名2>) VALUES (<列值1>, <列值2>);

插入多条记录：

INSERT INTO 
  <表名> (<列名1>, <列名2>) 
VALUES 
  (<列值1>, <列值2>), (<列值1>, <列值2>), (<列值1>, <列值2>);

2. 更新语句（UPDATE）

更新所有记录：

UPDATE <表名> SET <字列名1> = <列值1>, <列名2> = <列值2>;

根据条件更新记录：

UPDATE <表名> SET <列名1> = <列值1> WHERE <列名2> = <列值2>;

按顺序更新记录：

UPDATE <表名> SET <列名1> = <列值> ORDER BY <列名2> DESC;

只更新前n条记录：

UPDATE <表名> SET <列名1> = <列值> ORDER BY <列名2> DESC LIMIT n; 

多表联合更新：

UPDATE 
  <表名1>, <表名2> 
SET 
  <表名1>.<列名> = <表名2>.<列名> 
WHERE 
  <表名1>.id = <表名2>.id;

3. 删除语句（DELETE）

删除所有记录：

DELETE FROM <表名>;

根据条件删除记录：

DELETE FROM <表名> WHERE <表列> = <列值>;

按顺序删除记录：

DELETE FROM <表名> WHERE <列名2> = <列值> ORDER BY <列名2> = <列值>

只删除前n条记录：

DELETE FROM <表名> WHERE <列名2> = <列值> ORDER BY <列名2> = <列值> LIMIT n;

4. 查询语句（SELECT）

很多材料将查询语句单独划分成一类，称为 DQL（Data Query Language，数据查询语言）。

4.1 简单查询

查询所有列：

SELECT * FROM <表名>;

查询指定列：

SELECT <列名1>, <列名2> FROM <表名>;

条件查询：

SELECT <列名1>, <列名2> FROM <表名> WHERE <列名3> = <列值>;

结果排序（AES为正序；DESC为倒序）：

SELECT <列名1>, <列名2> FROM <表名> ORDER BY <列名3> DESC;

查询前n条记录：

SELECT <列名1>, <列名2> FROM <表名> LIMIT n;

分组查询：

SELECT <列名1>, COUNT(<列名2>) FROM <表名> GROUP BY <列名1>;

分组后筛选条件：

SELECT <列名1>, MAX(<列名2>) FROM <表名> GROUP BY <列名1> HAVING MAX(<列名2>) > 10;

4.2 关联查询（JOIN 子句）

左连接查询：即使在右表（t2）中没有匹配到数据，也返回左表（t1）的查询结果（LEFT JOIN 等于 LEFT OUTER JOIN）。

SELECT * FROM <表1> t1 LEFT JOIN <表2> t2 ON t1.id = t2.id;

右连接查询：即使在左表（t1）中没有匹配到数据，也返回右表（t2）的查询结果（RIGHT JOIN 等于 RIGHT OUTER JOIN）。

SELECT * FROM <表1> t1 RIGHT JOIN <表2> t2 ON t1.id = t2.id;

内连接查询：返回两个表完全匹配到的数据（INNER JOIN 等于 CROSS JOIN 等于 JOIN）。

SELECT * FROM <表1> t1 INNER JOIN <表2> t2 ON t1.id = t2.id;

4.3 联合查询（UNION 子句）

UNION 联合了多个子查询的结果，要求每个子查询返回的列数相同。

联合查询，结果集去重（UNION 等于 UNION DISTINCT）：

SELECT <列1>, <列1> FROM <表1>
UNION
SELECT <列1>, <列1> FROM <表2>
UNION
SELECT <列1>, <列1> FROM <表3>;

联合查询，取所有结果：

SELECT <列1>, <列1> FROM <表1>
UNION ALL
SELECT <列1>, <列1> FROM <表2>
UNION ALL
SELECT <列1>, <列1> FROM <表3>;

三、DCL（数据控制语言）

DCL（Data Control Language，数据控制语言）是一种可对数据访问权限进行控制的指令。DCL 包含GRANT 和 REVOKE 等指令。

1. 创建用户

创建一个用户（其中主机名如果使用“%”，则表示所有主机都可以登录该用户）：

CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'password';

查看所有创建好的用户：

SELECT * FROM mysql.user;

2. 授予权限

将数据库的所有权限授予用户：

GRANT ALL ON <数据库名>.* TO 'user_name'@'host_name';

将数据库中指定表的所有权限授予用户：

GRANT ALL ON <数据库名>.<表名> TO 'user_name'@'host_name';

将数据库的查询和插入权限授予用户（MySQL 还有很多权限，例如：DELETE, UPDATE, DROP等）：

GRANT SELECT, INSERT ON <数据库名>.* TO 'user_name'@'host_name';

显示用户的权限：

SHOW GRANT FOR 'user_name'@'host_name';

3. 撤销权限

将数据库的所有权限撤销：

REVOKE ALL ON <数据库名>.* FROM 'user_name'@'host_name';

将数据库的删除和修改权限撤销：

REVOKE DELETE, UPDATE ON <数据库名>.* FROM 'user_name'@'host_name';

---

专栏推荐

• 《IDEA 教程：从入门到精通》
• 《MySQL 教程：从入门到精通》