MySQL语法速查1：基础命令篇

发表 分类

1.1. 关于 SQL

SQL 是 Structure Query Language（结构化查询语言）的缩写，是关系型数据库的基本语言，由 IBM 在 20 世纪 70 年代开发出来，作为 IBM 关系数据库原型 System R 的原型关系语言，实现了关系数据库中的信息检索。

20 世纪 80 年代初，美国国家标准局（ANSI）开始着手制作 SQL 标准，并于 1986 年完成，被叫做 SQL-86。

SQL 标准几经修改，日趋完善，大多数关系型数据库系统都支持，而 (My)SQL 是在标准 SQL 的基础上进行了扩展。

1.2. SQL 分类

  • DDL（Data Definition Language）：数据定义语句
    • 主要用于操作数据库对象；
    • 常用关键字 create、drop、alter等；
  • DML（Data Manipulation Language）：数据操作语句
    • 主要用于操作数据库记录;
    • 常用关键字 insert、delete、update、select等；
  • DCL（Data Control Language）：数据控制语句
    • 主要用于控制数据的访问权限；
    • 常用关键字 grant、revoke等。

1.3. DDL

1.3.1. 创建数据库

1
CREATE DATABASE db_name;

1.3.2. 删除数据库

1
DROP DATABASE db_name;

1.3.3. 创建表

1
2
3
4
5
6
7
8
9
CREATE TABLE table_name(
    field1_name field1_type [field1_constraints],
    field2_name field2_type [field2_constraints],
    field3_name field3_type [field3_constraints],
    ...
    [PRIMARY KEY ( field1_name ),]
    [INDEX `idx_field1_name`(`field1_name`) USING BTREE COMMENT '',]
    ...
) [table_constraints];

说明：

  • field1_name 字段名，建议以下划线分隔单词，如 update_at。
  • field1_type 字段类型
    • int、char、varchar、float、decimal、datatime等。
    • 数值型的字段后跟括号，用于设置字段的显示宽度，如 int(11)。
      • 如果所插入数值的位数小于指定的宽度时会用空格填充；若同时使用 zerofill 约束，则会以 0 来填充。
      • 注意：显示宽度只用于显示，并不能限制取值范围和占用空间，每种数据类型的实际取值范围决定数据能否最终插入数据库。
      • 如果插入的值超过字段的实际取值范围，则提示：1264 - Out of range value for column xxxx
    • 字符型的字段后跟括号，用于设置可存放值的范围，如：varcahr(5)
      • 严格模式下，如果插入 6 位字符串，则提示：406 - Data too long for column xxxx。
      • 非严格模式下，如果插入 6 位字符串，则会进行截取后成功插入数据库，并且显示一个 warning。
  • field1_constraints 字段约束条件
    • NOT NULL 不允许为空，默认允许为空。
    • AUTO_INCREMENT 设置为自增列，在 MySQL 中无论 InnoDB 引擎还是 MyISAM 引擎的表，只能有一个自增列，并且自增列一定是索引列，无论是二级索引还是主键索引。
    • DEFAULT 默认值，默认 NULL。
    • COMMENT '注释信息' 字段注释
    • unsigned 数值型字段无符号，默认有符合。
    • zerofill 当所插入字段值的长度没有达到在字段类型中设置的长度时，用 0 来补全。
    • CHARACTER SET utf8mb4 设置字段的字符集。
    • COLLATE utf8mb4_bin
  • table_constraints 表约束条件
    • ENGINE=InnoDB 设置表的存储引擎，默认 InnoDB，MySQL5.x 以下默认 MyISAM。
      • MyISAM 支持全文本搜索
      • InnoDB 支持事务处理
      • MEMORY 类似MyISAM，但数据存储在内存中，速度很快（特别适合临时表）。
    • DEFAULT CHARSET=utf8 设置默认字符集
    • AUTO_INCREMENT=10 设置自增列的开始位置，查看表中自增字段的下一个值，使用 SHOW TABLE STATUS LIKE 'table_name'; 获取字段 Auto_increment 的值。
    • COMMENT '注释信息' 表的注释信息

查看表的定义

1
2
3
4
5
DESC table_name;

-- 或

SHOW CREATE TABLE table_name \G;

1.3.4. 删除表

1
2
3
4
DROP TABLE table_name；

-- 删除多张表，并且删除之前先判断是否存在
DROP TABLE IF EXISTS table1_name,table2_name,.....;

1.3.5. 修改表

注意 ⚠️：

  1. 关键字 CHANGE 和 FIRST | AFTER COLUMN 属于 MySQL 在标准 SQL 上的扩展，并不一定适用于其它数据库。

  2. field_definition 至少包含字段名和字段类型。

  • 修改表类型

    1
    ALTER TABLE table_name MODIFY [COLUMN] field_definition [FIRST | AFTER field_name];

  • 增加表字段

    1
    ALTER TABLE table_name ADD [COLUMNM] field_definition [FIRST | AFTER field_name];

  • 删除表字段

    1
    ALTER TABLE table_name DROP [COLUMN] field_name;

  • 修改字段名

    1
    ALTER TABLE table_name CHANGE [COLUMN] old_field_name field_definition [FIRST | AFTER field_name];

  • 修改字段排列顺序

    使用前面的可选参数 [FIRST | AFTER field_name]，其中：

    • ADD 新增的字段默认在表的最后位置；
    • CHANGE 或 MODIFY 默认不改变字段的位置。

  • 更改表名

    1
    ALTER TABLE table_name RENAME [TO] new_table_name;

1.3.6. 修改表-外键

1
2
3
4
5
-- 添加
ALTER TABLE table1 ADD CONSTRAINT fk_table1_table2 FOREIGN KEY (table1) REFERENCES table2 (id);

-- 删除
ALTER TABLE table1 DROP FOREIGN KEY fk_table1_table2;

1.3.7. 修改表-索引

这里只介绍了通过修改表结构的方式创建索引，此外还有其它两种方式（CREATE INDEX 或 在 CREATE TABLE 时指定 `），详见后续文章索引部分。

  • 普通索引

    1
    2
    3
    4
    5
    -- 添加
    ALTER TABLE table1 ADD INDEX idx_name(field1);

    -- 删除
    ALTER TABLE table1 DROP INDEX idx_name;

  • 主键索引

    1
    2
    3
    4
    5
    6
    7
    8
    -- 添加
    -- 先修改要设置为主键的字段：无符号、非空、自增
    ALTER TABLE table1
    MODIFY COLUMN `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (`id`);

    -- 删除
    ALTER TABLE table1 DROP PRIMARY KEY;

  • 唯一索引

    1
    2
    3
    4
    5
    -- 添加
    ALTER table mytable ADD UNIQUE [idx_name] (field1(length))

    -- 删除
    ALTER TABLE table1 DROP UNIQUE field1;

  • 查询表的索引

    1
    SHOW INDEX FROM table_name;

1.4. DML

1.4.1. 新增记录（增）

1
2
3
4
5
6
7
8
9
10
-- 插入一条
INSERT INFO table_name(field1,field2,...,fieldn) VALUES(val1,val2,...,valn);

-- 插入多条
INSERT INTO table_name(field1,field2,...,fieldn)
VALUES
(record1_val1,record1_val2,...,record1_valn),
(record2_val1,record2_val2,...,record2_valn),
...
(recordn_val1,recordn_val2,...,recordn_valn);
  • field1,field2,...,fieldn 可以不用写，但是后面 VALUES 里值的顺序需要与表中的字段顺序保持一致；
  • 如果待插入的数据，不包含以下类型的字段，可以不用写
    • 可空：自动设置为 NULL；
    • 有默认值：自动设置为默认值；
    • 自增：自动设置为下一个自增值。

1.4.2. 删除记录（删）

1
2
3
4
DELETE FROM table_name [WHERE condition];

-- 一次删除多张表
DELETE t1,t2,... FROM table1 t1,table2 t2,... [WHERE condition];

1.4.3. 更新记录（改）

1
2
3
4
UPDATE table_name SET field1=val1,field2=val2,...,fieldn=valn [WHERE condition];

-- 一次更新多个表
UPDATE table1 a,table2 b, tablen n SET a.field1=val1,...,n.fieldn=valn [WHERE condition];

1.4.4. 查询记录（查）

  • 基本

    1
    2
    3
    4
    SELECT * FROM table_name
    [WHERE condition]
    [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC], ...]
    [LIMIT offset_start,row_count];

    说明：

    • [WHERE condition] 过滤条件
    • [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC], ...] 排序
      • ASC 升序（默认）
      • DESC 降序
    • [LIMIT offset_start,row_count] 限制

  • 去重查询

    1
    SELECT distinct field1 FROM table_name [WHERE condition];

  • 聚合（汇总统计）

    1
    2
    3
    4
    5
    SELECT [field1,field2,...] func_name FROM table_name
    [WHERE condition]
    [GROUP BY field1,field2,...]
    [WITH ROLLUIP]
    [HAVING condition]

    说明：

    • func_name 聚合函数，常用：sum 求和、count(*) 记录数、max 最大值、min 最小值。
    • [WHERE condition] 聚合前按条件对结果进行过滤，应尽量使用此过滤以减少待聚合的结果集，提高聚合效率。
    • [GROUP BY field1,field2,...] 分类聚合字段
    • [WITH ROLLUIP] 是否对分类聚合后的结果进行再汇总。
    • [HAVING condition] 对聚合后的结果再进行条件过滤。

  • 内连接

    仅获取两张表中互相匹配的记录

    1
    2
    SELECT field1,field2 FROM table1 t1,table2 t2
    WHERE t1.outid=t2.outid

    其中 field1,field2,... 分别存储在两张表中。

  • 外连接

    • 左连接

      包含所有左边表中的记录，甚至是右边表中没有和它匹配的记录。

      1
      2
      SELECT field1,field2,... FROM table1 t1
      LEFT JOIN table2 t2 ON t2.outid=t1.outid

    • 右连接

      包含所有右边表中的记录，甚至是右边表中没有和它匹配的记录。

      1
      2
      SELECT field1,field2,... FROM table1 t1
      RIGHT JOIN table2 t2 ON t2.outid=t1.outid

  • 子查询

    当进行查询时，需要的条件是另一个 select 语句的结果时将用到子查询功能。

    1
    2
    3
    SELECT field1,field2,... FROM table1 WHERE outid IN(
        SELECT outid FROM table2
    );

    除了 IN，用于子查询的关键字还有：NOT IN、=、!=、EXISTS、NOT EXISTS等。

    MySQL4.1 以前的版本不支持子查询，需要用表连接来实现，如：

    1
    2
    SELECT t1.* FROM table1 t1,table2 t2
    WHERE t1.outid=t2.outid

  • 联合（合并结果集）

    1
    2
    3
    4
    5
    6
    SELECT * FROM table1
    UNION | UNION ALL
    SELECT * FROM table2
    ...
    UNION | UNION ALL
    SELECT * FROM tablen

    说明：

    • UNION ALL 把结果集直接合并在一起。
    • UNION 是将 UNION ALL 的结果集执行一次 DISTINCT，去除重复记录后的结果。

1.5. DCL

1.5.1. 授予权限

1
GRANT select,insert ON db1.* TO 'user1'@'localhost' IDENTIFIED BY '123';

1.5.2. 收回权限

1
REVOKE insert ON db1.* FROM 'user1'@'localhost';

1.6. 附录

1.6.1. 连接数据库

1
mysql [-h127.0.0.1] [-P3306] -uroot -p[password]

参数说明：

  • -h 可选，数据库地址（IP或域名），默认 127.0.0.1。
  • -P 可选，数据库端口号，默认 3306.
  • -u 数据库登录用户
  • -p 数据库登录用户对应的密码

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

MySQL [(none)]> exit
Bye

1.6.2. 如何使用系统帮助

在命令行模式下，使用 ? contents 命令查看对应分类的帮助信息。

如：

1
2
-- 查看 show 相关的所有命令等
mysql> ? show

1.6.3. 什么是元数据信息

元数据指数据的数据，比如表名、列名、列类型、索引名等表的各种属性名称。MySQL 5.0 之后提供来一个新的数据库 information_schema，用来记录 MySQL 中的元数据信息。

  • 这是一个比较特殊的数据库，它在物理上并不存在相关的目录和文件；
  • 对应数据库里使用 show tables 显示的各种“表”也并不是实际存在的物理表，而全部是视图。

常用的视图如下：

  • SCHEMATA 提供当前 MySQL 实例中所有的数据库信息，对应 show databases; 的结果。
  • TABLES 提供关于数据库中的表信息（包括视图），详细表述里某个表属于那个 schema、表类型、表引擎、创建时间等，对应 show tables from schemaname; 的结果。
  • COLUMNS 提供所有表中的列信息，对应 show columns from schemaname.tablename; 的结果。
  • STATISTICS 提供关于表索引的信息，对应 show index from schemaname.tablename; 的结果。