本文记录 SQL 的种类及基本用法。
一、SQL通用语法
- SQL语句可以单行或多行书写，分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL的SQL语句不区分大小写，关键字建议使用大写
- 单行注释：
--或# - 多行注释：
/* */
二、SQL语法分类
| 分类 | 说明 |
|---|---|
| DDL(Data Definition Language) | 数据定义语言，用来定义数据库对象（数据库、表、字段） |
| DML(Data Manipulation Language) | 数据操作语言，用来对数据库表中数据进行增删改 |
| DQL(Data Query Language) | 数据查询语言，用来查询数据库中表的记录 |
| DCL(Data Control Langurage) | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
三、DDL
3.1 数据库有关操作
# 查询
# 查询所有数据库
SHOW DATABASES;
# 查询当前数据库
SELECT DATABASE();
# 创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
# 删除
DROP DATABASE [IF EXISTS] 数据库名;
# 使用
USE 数据库名;
3.2 表有关操作
- 查询
# 查询当前数据库所有表
SHOW TABLES;
# 查询表结构
DESC 表名;
# 查询指定表的建表语句
SHOW CREATE TABLE 表名;
- 创建
CREATE TABLE (
字段1 字段1类型[COMMENT 字段1注释],
...
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];
- 修改
# 添加字段
ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT 注释] [约束];
# 修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT 注释] [约束];
# 删除字段
ALTER TABLE 表名 DROP 字段名;
# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
- 删除
# 删除表
DROP TABLE [IF EXISTS] 表名;
# 删除指定表，并重新创建该表
TRUNCATE TABLE 表名;
3.3 数据类型
3.3.1 数值类型
| 类型 | 大小（字节） | 有符号（SIGNED）范围 | 无符号（UNSIGNED）范围 | 描述 |
|---|---|---|---|---|
| TINYINT | 1 | [-128,127] | [0,255] | 小整数值 |
| SMALLINT | 2 | [-32768, 32767] | [0, 65535] | 大整数值 |
| MEDIUMINT | 3 | [-8388608, 8388607] | [0, 1677215] | 大整数值 |
| INT(INTERGER) | 4 | [-2147483648, 2147483647] | [0, 4294967295] | 大整数值 |
| BIGINT | 8 | [-2^63, 2^63 -1] | [0, 2^64 -1] | 极大整数值 |
| FLOAT | 4 | 单精度浮点数值 | ||
| DOUBLE | 8 | 双精度浮点数值 | ||
| DECIMAL | 依赖于M（精读）和D（标度）的值 | 依赖于M（精读）和D（标度）的值 | 小数值（精确定点数） |
“精度”与“标度”：以 123.45 为例
- 精度：5
- 标度：2
- 例子
# 年龄
age TINYINT UNSIGNED
# 分数
score double(4, 1)
3.3.2 字符串类型
| 类型 | 大小（字节） | 描述 |
|---|---|---|
| CHAR | 0-255 | 定长字符串 |
| VARCHAR | 0-65535 | 变长字符串 |
| TINYBLOB | 0-255 | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255 | 短文本字符串 |
| BLOB | 0-65535 | 二进制形式的长文本数据 |
| TEXT | 0-65535 | 长文本数据 |
| MEDIUMBLOB | 0-16777215 | 二进制形式的的中等长度文本数据 |
| MEDIUMTEXT | 0-16777215 | 中等长度文本数据 |
| LONGBLOB | 0-4294967295 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4294967295 | 极大文本数据 |
- char 与 varchar 的区别
| char | varchar | |
|---|---|---|
| 长度 | 固定 | 自动计算长度（但不能超过规定的长度） |
| 性能 | 高（不需要计算长度） | 低（需要计算长度） |
- 例子
# 用户名（长度不超过50 字节）
username varchar(50)
# 性别
gender char(1)
3.3.3 时间类型
| 类型 | 大小（字节） | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | -838: 59: 59 838: 59: 59 | HH: MM: SS | 时间值或持续时间 |
| YEAR | 1 | 1901. 2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00: 00: 00 9999-12-31 23: 59: 59 | YY-MM-DD HH: MM: SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00: 00: 01 2038-01-19 03: 14: 07 | YY-MM-DD HH: MM: SS | 混合日期和时间值，时间戳 |
- 例子
# 生日
birthday date
四、DML（记录增删改）
- 添加数据
# 给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUE(值1, 值2, ...);
# 给全部字段添加数据
INSERT INTO 表名 VALUE(值1, 值2, ...);
# 批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUE(值1, 值2, ...), (值1, 值2, ...),(值1, 值2, ...);
INSERT INTO 表名 VALUE(值1, 值2, ...), (值1, 值2, ...),(值1, 值2, ...);
- 字符串和日期的数据类型应该包含在引号中
- 修改数据
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件];
- where 条件不写就会修改表中所有数据
- 删除数据
DELETE FROM 表名 [WHERE 条件];
五、DQL
5.1 语法
SELECT
字段列表
FROM
表明列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
5.2 基本查询
- 查询多个字段
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名; # 尽量不要写 *，影响可读性和效率
- 设置别名
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ... FROM 表名 [表别名];
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
5.3 条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| <= | 小于等于 |
| = | 等于 |
| <>（或 !=) | 不等于 |
| BETWEEN… AND … | 在某个范围之内（闭区间） |
| IN(…) | 在 in 之后的列表中的值，多选一 |
| LIKE 占位符 | 模糊匹配（_匹配单个字符，%匹配任意个字符，这两个字符都要写引号） |
| IS NULL | 是 NULL |
| 逻辑运算符 | 功能 |
|---|---|
| AND 或 && | 与 |
| OR 或 || | 或 |
| NOT 或 ! | 非 |
逻辑运算符用于组合多个条件
5.4 聚合函数
- 概念
将一列数据（字段）作为一个整体，进行纵向计算。
NULL 值不参与聚合计算
- 常见聚合函数
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
5.5 分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
where 与 having 的区别：
- 执行时机不同：where 是分组前过滤，不满足条件的不参与分组；having 是分组后过滤，只对结果进行过滤
- 判断条件不同：where 不能对聚合函数进行判断，而having 可以
5.6 排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2, 排序方式2;
| 排序方式（仅两种） | 含义 |
|---|---|
| ASC（默认） | 升序 |
| DESC | 降序 |
若为多字段排序，当地一个字段相同时，才会根据第二个字段进行排序。
5.7 分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
- 起始索引从 0 开始，`起始索引 = (查询页码 - 1)* 每页显示记录数
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中的是 LIMIT
- 如果查询的是第一页数据，其实做音可以省略
5.8 执行顺序
FROM --> WHERE --> GROUP BY ---> HAVING ---> SELECT ---> ORDER BY ---> LIMIT
六、DCL
DCL(Data Control Language，数据控制语言)，用来管理数据库用户、控制数据库访问权限。
6.1 用户管理
- 查询用户
USE mysql;
SELECT * FROM user;
- 创建用户
CREATE USER '用户名'@`主机名` IDENTIFIED BY '密码';
- 修改用户密码
ALTER USER '用户名'@`主机名` IDENTIFIED WITH mysql_native_password BY '新密码';
- 删除用户
DROP USER '用户名' @'主机名';
- 主机名可以使用 % 通配；
6.2 权限控制
- 常用权限
| 权限 | 说明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
- 相关指令
# 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
# 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
# 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';