SQL
SQL通用语法
-
SQL语句可以单行或者多行书写
-
SQL语句可以使用空格或者缩进增强语句的可读性（数量不限制）
-
MySql数据库的SQL语句不区分大小写，关键字建议使用大写
-
注释
-
单行注释
-- 这是一行注释 # 这是一行注释 -
多行注释
/* 这是一段注释 */
-
SQL分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据对象（数据库，表，字段） |
| DML | Data Manipulation Language | 数据操作语言，用来对数据表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据表中的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户，控制数据库的访问权限 |
DDL
DDL-数据库操作
-
查询
-
查询所有数据库
SHOW DATABASES; -
查询当前数据库
SELECT DATABASE();
-
-
创建
CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];[IF NOT EXISTS]数据库是否存在，如果数据库存在则不创建[DEFAULT CHARSET 字符集]数据库的字符集常用utf8mb4[COLLATE 排序规则]数据库排序规则通常情况不用指定，常用utf8mb4_0900_ai_ci
-
删除
DROP DATABASE [IF EXISTS] 数据库名称;[IF EXISTS]数据库是否存在，如果数据库存在则删除，不存在则不执行删除
-
使用
USE 数据库名称;
DDL-数据表操作
-
创建
CREATE TABLE 数据表名称( 字段1 类型 [约束] [COMMENT 注释], 字段2 类型 [约束] [COMMENT 注释], ...... 字段n 类型 [约束] [COMMENT 注释] )[COMMENT 注释];[约束]参见CONSTRAINT[COMMENT 注释]数据表和字段的注释说明
-
查询
-
查询当前数据库中所有的表
SHOW TABLES; -
查询表结构
DESC 数据表名称; -
查询指定数据表的建表语句
SHOW CREATE TABLE 数据表名称;
-
-
修改
-
添加字段
ALTER TABLE 数据表名称 ADD 字段名称 类型(长度) [COMMENT 注释] [约束];[COMMENT 注释]字段的解释说明[约束]参见CONSTRAINT
-
修改字段的数据类型
ALTER TABLE 数据表名称 MODIFY 字段名称 新的数据类型(长度); -
修改字段名称和类型
ALTER TABLE 数据表名称 CHANGE 旧的字段名称 新的字段名称 类型(长度) [COMMENT 注释] [约束];[COMMENT 注释]新字段的解释说明[约束]新字段约束，参见CONSTRAINT
-
删除字段
ALTER TABLE 数据表名称 DROP 字段名称; -
修改数据表名称
ALTER TABLE 数据表名称 RENAME TO 新的数据表名称;
-
-
删除
-
删除数据表
DROP TABLE [IF EXISTS] 数据表名称; -
删除数据表，并重新创建该表
TRUNCATE TABLE 数据表名称;
-
注意：详细字段类型请参见MySql中的数据类型
DML
DML-添加数据
-
指定字段添加数据
INSERT INTO 数据表名称(字段1,字段2,......) VALUES(值1,值2,......); -
全部字段添加数据，值的顺序和数据表中的字段顺序一致
INSERT INTO 数据表名称 VALUES(值1,值2,......); -
批量添加数据
-
指定字段批量添加数据
INSERT INTO 数据表名称(字段1,字段2,......) VALUES(值1,值2,......),(值1,值2,......),......; -
全部字段批量添加数据，值的顺序和数据表中的字段顺序一致
INSERT INTO 数据表名称 VALUES(值1,值2,......),(值1,值2,......),......;
-
DML-修改数据
UPDATE 数据表名称 SET 字段1=值1,字段2=值2,...... [WHERE 条件];
[WHERE 条件]如果不写条件则修改数据表中的全部数据
DML-删除数据
DELETE FROM 数据表名称 [WHERE 条件];
[WHERE 条件]如果不写条件则删除数据表中的全部数据
DQL
SELECT 字段列表 FROM 表名列表 [WHERE 条件列表] [GROUP BY 分组字段列表] [HAVING 分组后条件列表] [ORDER BY 排序字段列表] [LIMIT 分页参数];
[WHERE 条件列表]参见DQL-条件查询[GROUP BY 分组字段列表]参见DQL-分组查询[HAVING 分组后条件列表]参见DQL-分组查询[ORDER BY 排序字段列表]参见DQL-排序查询[LIMIT 分页参数]参见DQL-分页查询
DQL-基本查询
-
查询多个字段
SELECT 字段1,字段2,...... FROM 表名列表; -
查询所有字段
SELECT * FROM 表名列表; -
设置别名
SELECT 字段1[ AS 别名1],字段2[ AS 别名2],...... FROM 表名列表;[ AS 别名]将该字段名称在结果中设置为别名内容显示
-
去除重复数据
SELECT DISTINCT 字段列表 FROM 表名列表;
DQL-条件查询
-
语法
SELECT 字段列表 FROM 表名列表 WHERE 条件列表; -
条件
-
比较运算符
比较运算符 功能 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 BETWEEN...AND... 在某个范围内（含最小，最大值） IN(...) 在in之后的列表中的值，多选一 LIKE 占位符 模糊匹配（_匹配单个字符，%匹配人任意个字符） IS NULL 是NULL -
逻辑运算符
逻辑运算符 功能 AND 或 && 并且（多个条件同时成立） OR 或 || 或者（多个条件任意一个成立） NOT 或 ! 非，不是
-
DQL-分组查询
-
聚合函数，将一列数据作为整体，进行纵向计算
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 语法
SELECT 聚合函数(字段列表) FROM 表名列表;注意：每列中的null值不参与计算
-
分组查询
-
语法
SELECT 字段列表 FROM 表名列表 [WHERE 条件列表] [GROUP BY 分组字段列表] [HAVING 分组后条件列表]; -
where 和 having 的区别
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
- 判断条件不同：where不能对聚合函数进行判断，而having可以
-
DQL-排序查询
-
语法
SELECT 字段列表 FROM 表名列表 ORDER BY 字段1 排序方式1,字段2 排序方式2,.....; -
排序方式
- ASC：升序（默认）
- DESC：降序
注意：多字段排序，当第一个字段值相同时才会根据第二个字段排序
DQL-分页查询
-
语法
SELECT 字段列表 FROM 表名列表 LIMIT 起始索引,查询记录数;
注意
- *起始索引从0开始，起始索引=(查询页码-1)每页记录数
- 分页查询时数据库的方言，不同的数据库有不同的实现，MySql是LIMIT
- 如果查询的是第一页的数据，起始索引可以省略，直接简写为LIMIT 10
DQL-编写顺序
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
DQL-执行顺序
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
SELECT
字段列表
ORDER BY
排序字段列表
LIMIT
分页参数
DCL
DCL-管理用户
-
查询用户
USE mysql; SELECT * FROM user; -
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; -
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'; -
删除用户
DROP USER '用户名'@'主机名';
注意： 主机名可以使用%通配
DCL-权限控制
| 权限 | 描述 |
|---|---|
| ALL,ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库，表，视图 |
| CREATE | 创建数据库，表 |
注意： 其他权限请参考MySql官方文档
-
查询权限
SHOW GRANTS FOR '用户名'@'主机名'; -
授予权限
GRANTS 权限列表 ON 数据库名称.数据库表名称 TO '用户名'@'主机名';mariadb
GRANT 权限列表 ON 数据库名称.数据库表名称 TO '用户名'@'主机名'; -
撤销权限
REVOKE 权限列表 ON 数据库名称.数据库表名称 FROM '用户名'@'主机名';
FUNCTION
字符串函数
| 函数 | 描述 |
|---|---|
| CONCAT(S1,S2,...Sn) | 字符串拼接，将S1，S2，...Sn拼接成一个字符串 |
| LOWER(str) | 将字符串str全部转为小写 |
| UPPER(str) | 将字符串str全部转为大写 |
| LPAD(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| RPAD(str,n,pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
数值函数
| 函数 | 描述 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
日期函数
| 函数 | 描述 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
| 函数 | 功能 |
|---|---|
| IF(value , t, f) | 如果value为true，则返回t，否则返回f |
| IFNULL(value1, value2) | 如果value1不为空，返回value1，否则返回value2 |
| CASE WHEN [val1] THEN [res1] ... ELSE [default] END | 如果val1为true，返回res1，...否则返回default默认值 |
| CASE [expr] WHEN [val1] THEN [res1] ... ELSE[default] END | 如果expr的值等于val1，返回res1，... 否则返回default默认值 |
CONSTRAINT
概述
- 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
- 目的：保证数据库中的数据正确，有效性和完整性分类：
分类
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一且不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | DEFAULT |
| 检查约束 | 保证字段满足某一个条件 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立联系，保证数据的一致性和完整性 | FOREIGN KEY |
外键语法
-
添加外键
CREATE TABLE 数据表名称( 字段名称 数据类型, ...... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) );ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名); -
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; -
外键删除或更新行为
行为 描述 NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 RESTRICT一致) RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 NOACTION一致) CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null (这就要求该外键允许取nul)。 SET DEFAULT 父表有变更时，子表将外键列设置成一个默认的值(lnnodb不支持) 语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;
多表查询
多表关系
- 一对多（多对一）：在多的一方设置外键，关联一的一方的主键
- 多对多：建立中间表，中间表包含两个外键，关联两张表的主键
- 一对一：用于表结构的拆分，在其中任何一方设置外键（unique），关联另一方的主键
多表查询概述
- 概述：指从多张表中查询数据
- 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A和B的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）
- 分类
- 连接查询
- 内连接：相当于查询A，B交集部分数据
- 外连接
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
- 子查询：SQL语句中嵌套SELECT语句，称为嵌套查询，又成子查询
- 连接查询
内连接
相当于查询A，B交集部分数据
-
隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件......; -
显式内连接
SELECT 字段列表 FROM 表1 [inner] JOIN 表2 ON 连接条件......;[inner]可以省略不写
外连接
-
左外连接：查询左表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件......; -
右外连接：查询右表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件......;
自连接
当前表与自身的连接查询，自连接必须使用表别名
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件......;
联合查询
-
union,union all
SELECT 字段列表 FROM 表A...... UNION [ALL] SELECT 字段列表 FROM 表B......;- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
- union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重
子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又成子查询
SELECT * FROM 表1 WHERE 列1 = (sElECT 列1 FROM 表2);
-
子查询外部的语句可以是DML和DQL的任何一个
-
根据子查询结果不同分类
-
标量子查询（子查询结果为单个值）
子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种子查询称为标量子查询
常用的操作符号：
=<>>>=<<= -
列子查询（子查询结果为1列）
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询
常用的操作符号：
INNOTINANYSOMEALL操作符 描述 IN 指定集合范围之内，多选一 NOTIN 不在指定集合范围之内 ANY 子查询返回列表中，有任意一个满足即可 SOME 与ANY等同，使用SOME的地方都可以使用ANY ALL 子查询返回列表的所有值都必须满足 -
行子查询（子查询结果为1行）
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
常用的操作符号：
=<>INNOTIN -
表子查询（子查询结果为多行多列）
子查询返回的结果是多行多列，这种子查询称为表子查询
常用的操作符号：
IN
-
-
根据子查询位置分类
- WHERE之后
- FROM之后
- SELECT之后
BUSINESS
简介
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功要么同时失败
操作
-
查询或设置事务的提交方式
SELECT @@autocommit; SET @@autocommit = 0;- 0-手动提交
- 1-自动提交
-
提交事务
COMMIT; -
回滚事务
ROLLBACK; -
开启事务
START TRANSACTION 或 BEGIN;
特性
- 原子性（Atomicity）：事务四不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
- 隔离性（Isonlation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性（Durability）：事务一但提交或回滚，他对数据库中的数据的改变时永久的
并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读到另外一个事务还没有提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重读读 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影 |
隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed(Oracle Default) | × | √ | √ |
| Repeatable Read(MySql Default) | × | × | √ |
| Serializable | × | × | × |
-
查看事务额隔离级别
SELECT @@TRANSACTION_ISOLATION -
设置事务的隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE];
注意：事务的隔离级别越高，数据越安全，但是性能越低
