SQL
全称 Structured Query Language，结构化查询语言
根据功能，分为四类
| DDL |
Data Definition Language |
数据定义语言，用来定义数据库对象（数据库，表，字段） |
| DML |
Data Manipulation Language |
数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL |
Data Query Language |
数据查询语言，用来查询数据库中表的记录 |
| DCL |
Data Control Language |
数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
DDL
Data Definition
Language，数据定义语言，用来定义数据库对象（数据库，表，字段）
数据库操作
创建
1
| CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
|
删除
1
| DROP DATABASE [IF EXISTS] 数据库名;
|
切换
查询
1
2
3
4
5
| # 查询所有数据库
SHOW DATABASES;
# 查询当前数据库
SELECT DATABASE();
|
表操作
需要先用 USE 指令进入一个数据库
创建
1
2
3
4
5
6
7
8
9
10
11
12
| CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
......
字段n 字段n类型 [COMMENT 字段n注释],
) [COMMENT 表注释];
create table sevqi(
id int comment '学号',
name varchar(5) comment '姓名'
) comment '个人';
|
删除
1
2
3
4
5
| # 删除表
DROP TABLE [IF EXISTS] 表名;
# 删除指定表，并重新创建该表
TRUNCATE TABLE 表名;
|
修改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| # 添加字段
ALTER TABLE 表名 ADD 字段名 类型 [COMMENT] [约束];
# 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型;
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 [COMMENT] [约束];
# 删除字段
ALTER TABLE 表名 DROP 字段名;
# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
|
查询
1
2
3
4
5
6
7
8
9
10
| # 查询当前数据库所有表
SHOW TABLES;
# 查询表结构
SHOW COLUMNS FROM 表名;
DESCRIBE 表名;
DESC 表名;
# 查询指定表的建表语句
SHOW CREATE TABLE 表名;
|
DML
Data Manipulation
Language，数据操作语言，用来对数据库表中的数据进行增删改 添加数据
(INSERT) 修改数据 (UPDATE) 删除数据 (DELETE)
添加
- 添加数据时，指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小，应该在字段的规定范围内。
1
2
3
4
5
6
7
8
9
10
| # 给指定字段添加数据
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, ...) ;
|
修改
如果没有条件，则修改整张表的数据
1
| UPDATE 表名 SET 字段名1=值1, 字段名2=值2, .... [WHERE 条件 ];
|
删除
如果没有条件，则删除整张表的数据
1
| DELETE FROM 表名 [WHERE 条件];
|
DQL
Data Query Language，数据查询语言，用来查询数据库中表的记录
查询关键字 SELECT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
|
基本查询
1
2
3
4
5
6
| # 起别名的话，as 可以省略，别名有时也被称为导出列
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], 字段3 [AS 别名3] ... FROM 表名;
SELECT * FROM 表名;
# 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
|
条件查询
1
| SELECT 字段列表 FROM 表名 WHERE 条件列表;
|
| > |
大于 |
| >= |
大于等于 |
| < |
小于 |
| <= |
小于等于 |
| = |
等于 |
| <> 或 != |
不等于 |
| BETWEEN ... AND ... |
在某个范围之内（含最小、最大值） |
| IN(...) |
在 in 之后的列表中的值，多选一 |
| LIKE 通配符 |
模糊匹配（ _ 匹配单个字符，%
匹配任意个字符） |
| IS NULL |
是 NULL |
搜索模式由字面值、通配符或两者组合构成的搜索条件
不要 过度
使用通配符，也不要在搜索模式的开始处就使用通配符，因为通配符搜索处理速度慢
| AND 或 && |
并且 (多个条件同时成立) |
| OR 或 || |
或者 (多个条件任意一个成立) |
| NOT 或 ! |
非 , 不是 |
AND 优先级高于 OR
聚合函数
1
| SELECT 聚合函数(字段列表) FROM 表名;
|
NULL 不参与计算
| count |
统计数量 |
| max |
最大值 |
| min |
最小值 |
| avg |
平均值 |
| sum |
求和 |
分组查询
1
| SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
|
HAVING 可以使用前面的别名
执行顺序 WHERE > 聚合函数 > HAVING
排序查询
1
| SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2, ...;
|
先根据字段1的排序方式1排序，然后再根据字段2的排序方式2排序
分页查询
1
| SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
|
注意事项:
- 起始索引从 0 开始，起始索引 =（查询页码 - 1）* 每页显示记录数
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是
LIMIT
- 如果查询的是第一页数据，起始索引可以省略
执行顺序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
SELECT
字段列表
ORDER BY
排序字段列表
LIMIT
分页参数
|
DCL
Data Control
Language，数据控制语言，用来管理数据库用户、控制数据库的访问权限 在
MySQL 中需要通过用户名@主机名的方式，来唯一标识一个用户
管理用户
创建用户
1
2
3
4
5
6
7
| CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# 创建用户 qwt, 只能够在当前主机 localhost 访问, 密码 123456
CREATE USER 'qwt'@'localhost' IDENTIFIED BY '123456';
# 创建用户 qwt, 可以在任意主机访问该数据库, 密码 123456;
CREATE USER 'qwt'@'%' IDENTIFIED BY '123456';
|
删除用户
在 MySQL 中需要通过用户名@主机名的方式，来唯一标识一个用户
修改用户密码
1
2
3
4
| ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
# 修改用户 qwt 的访问密码为 1234;
ALTER USER 'qwt'@'%' IDENTIFIED WITH mysql_native_password by '1234';
|
查询用户
1
2
3
4
5
| SELECT * FROM mysql.user;
# 或者
USE mysql;
SELECT * FROM user;
|
权限管理
常用权限
| ALL, ALL PRIVILEGES |
所有权限 |
| SELECT |
查询数据 |
| INSERT |
插入数据 |
| UPDATE |
修改数据 |
| DELETE |
删除数据 |
| ALTER |
修改表 |
| DROP |
删除数据库/表/视图 |
| CREATE |
创建数据库/表 |
查询权限
1
| SHOW GRANTS FOR '用户名'@'主机名';
|
授予权限
1
2
| # 数据库名和表名可以用 * 号表示，表示为全部的意思
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
|
撤销权限
1
2
| # 数据库名和表名可以用 * 号表示，表示为全部的意思
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
|
数据类型
MySQL
中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型
数值类型
| TINYINT |
1byte |
(-128, 127) |
(0, 255) |
小整数值 |
| SMALLINT |
2bytes |
(-32768, 32767) |
(0, 65535) |
大整数值 |
| MEDIUMINT |
3bytes |
(-8388608, 8388607) |
(0, 16777215) |
大整数值 |
| INT/INTEGER |
4bytes |
(-2147483648, 2147483647) |
(0, 4294967295) |
大整数值 |
| BIGINT |
8bytes |
(-2^63, 2^63-1) |
(0, 2^64-1) |
极大整数值 |
| FLOAT |
4bytes |
(-3.402823466 E+38, 3.402823466351 E+38) |
0 和 (1.175494351 E-38，3.402823466 E+38) |
单精度浮点数值 |
| DOUBLE |
8bytes |
(-1.7976931348623157 E+308, 1.7976931348623157 E+308) |
0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308) |
双精度浮点数值 |
| DECIMAL |
|
依赖于 M（精度）和 D（标度） 的值 |
依赖于 M（精度）和 D（标度）的值 |
小数值(精确定点数) |
字符串类型
| CHAR |
0-255 bytes |
定长字符串（需要指定长度） |
| VARCHAR |
0-65535 bytes |
变长字符串（需要指定长度） |
| TINYBLOB |
0-255 bytes |
不超过255个字符的二进制数据 |
| TINYTEXT |
0-255 bytes |
短文本字符串 |
| BLOB |
0-65 535 bytes |
二进制形式的长文本数据 |
| TEXT |
0-65 535 bytes |
长文本数据 |
| MEDIUMBLOB |
0-16 777 215 bytes |
二进制形式的中等长度文本数据 |
| MEDIUMTEXT |
0-16 777 215 bytes |
中等长度文本数据 |
| LONGBLOB |
0-4 294 967 295 bytes |
二进制形式的极大文本数据 |
| LONGTEXT |
0-4 294 967 295 bytes |
极大文本数据 |
VARCHAR 根据当前数据来设定长度，所以性能会比 CHAR
差
日期类型
| 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 |
YYYY-MM-DD HH:MM:SS |
混合日期和时间值 |
| TIMESTAMP |
4 |
1970-01-01 00:00:01 至 2038-01-19 03:14:07 |
YYYY-MM-DD HH:MM:SS |
混合日期和时间值，时间戳 |
