一. SQL基础
1. SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾
- SQL语句可以使用 空格/缩进 来增强语句可读性
- MySQL数据库的SQL语句不区分大小写,关键字建议大写
- 注释
- 单行注释:–注释内容 或 #注释内容(MySQL特有)
- 多行注释:/* 注释内容 */
2. SQL分类
分类 | 说明 |
---|---|
DDL | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | 数据操作语言,用来对数据库中的数据进行 增删改 |
DQL | 数据查询语言,用来查询数据库中表的记录 |
DCL | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
3. DDL
1. 数据库操作
查询
- 查询所有数据库
SHOW DATABASES;
- 查询当前数据库
SELECT DAATABASE();
创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
删除
DROP DATABASE [IF EXISTS] 数据库名;
使用
USE 数据库名;
2. 表操作
查询
- 查询当前数据库所有表
SHOW TABLES;
- 查询表结构
DESC 表名;
- 查询指定表的建表语句
SHOW CREATE TABLE 表名;
创建
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];
数据类型
分类 | 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
---|---|---|---|---|---|
数值类型 | TINYINT | 1 Byte | -128 - 127 | 0 - 255 | 小整数值 |
^ | SMALLINT | 2 Bytes | -32,768 - 32,767 | 0 - 65,535 | 大整数值 |
^ | MEDIUMINT | 3 Bytes | -8,388,608 - 8,388,607 | 0 - 16,777,215 | 大整数值 |
^ | INT或INTEGER | 4 Bytes | -2,147,483,648 - 2,147,483,647 | 0 - 4,294,967,295 | 大整数值 |
^ | BIGINT | 8 Bytes | -9,223,372,036,854,775,808 - 9,223,372,036,854,775,807 | 0 - 18,446,744,073,709,551,615 | 极大整数值 |
^ | FLOAT | 4 Bytes | -3.402 823 466 E+38 - -1.175 494 351 E-38, 0, 1.175 494 351 E-38 - 3.402 823 466 E+38 | 0, 1.175 494 351 E-38 - 3.402 823 466 E+38 | 单精度浮点数值 |
^ | DOUBLE | 8 Bytes | -1.797 693 134 862 315 7 E+308 - -2.225 073 858 507 201 4 E-308, 0, 2.225 073 858 507 201 4 E-308 - 1.797 693 134 862 315 7 E+308 | 0, 2.225 073 858 507 201 4 E-308 - 1.797 693 134 862 315 7 E+308 | 双精度浮点数值 |
^ | DECIMAL | - | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值 |
分类 | 类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|---|
日期和时间类型 | DATE | 3 Bytes | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
^ | TIME | 3 Bytes + 秒精度 | -838:59:59 至 838:59:59 | HH:MM:SS[.fraction] | 时间值 |
^ | YEAR | 1 Byte | 1901 至 2155 | YYYY | 年份值 |
^ | DATETIME | 8 Bytes | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS[.fraction] | 混合日期和时间值 |
^ | TIMESTAMP | 4 Bytes | 1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC | YYYY-MM-DD HH:MM:SS[.fraction] | 时间戳值 |
分类 | 类型 | 大小 | 范围 | 描述 |
---|---|---|---|---|
字符串类型 | CHAR | 0-255 Bytes | 定长字符串,最大长度为255个字符 | 固定长度的字符串 |
^ | VARCHAR | 0-65535 Bytes | 变长字符串,最大长度为65535个字符 | 可变长度的字符串 |
^ | TINYTEXT | 0-255 Bytes | 短文本字符串 | 小文本字符串 |
^ | TEXT | 0-65535 Bytes | 长文本字符串 | 文本字符串 |
^ | MEDIUMTEXT | 0-16777215 Bytes | 中等长度文本字符串 | 中等文本字符串 |
^ | LONGTEXT | 0-4294967295 Bytes | 极大文本字符串 | 极大文本字符串 |
^ | ENUM | 1-65535 Bytes | 枚举字符串,最多65535个不同的枚举值 | 枚举类型 |
^ | SET | 1-64 Bytes | 字符串对象,可以有0个或多个值,最多64个不同的值 | 字符串集合 |
二进制类型 | TINYBLOB | 0-255 Bytes | 小二进制对象 | 小二进制对象 |
^ | BLOB | 0-65535 Bytes | 二进制大对象 | 二进制大对象 |
^ | MEDIUMBLOB | 0-16777215 Bytes | 中等二进制对象 | 中等二进制对象 |
^ | LONGBLOB | 0-4294967295 Bytes | 极大二进制对象 | 极大二进制对象 |
修改
- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
- 删除字段
ALTER TABLE 表名 DROP 字段名;
- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
- 删除表
DROP TABLE [IF EXIST] 表名;
- 删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
4. DML
添加数据
- 给指定字段添加数据
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, ...);
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期类型数据应该包含在引号中。
- 插入的数据大小,应在字段的规定范围内。
修改数据
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件]; #若没有where条件,就是修改整张表中数据
删除数据
DELETE FROM 表名 [WHERE 条件]; #如果没有where条件,删除整张表数据
DELETE 不能删除某个字段的值,如果要删除某个字段的值,使用UPDATE将其改为NONE
5. DQL
DQL-语法(编写顺序)
SELECT
字段列表 -- 5
FROM
表名列表 -- 1
WHERE
条件列表 -- 2
GROUP BY
分组字段列表 -- 3
HAVING
分组后条件列表 -- 4
ORDER BY
排序字段列表 -- 6
LIMIT
分页参数 -- 7
DQL-基本查询
- 查询多个字段
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名; #全查
- 设置别名
SELECT 字段1 [AS 别名1], ... FROM 表名;
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
DQL-条件查询
- 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
- 条件
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN…AND… | 在某个范围之内,闭区间 |
IN(…) | 再in之后的列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且 |
OR 或 || | 或者 |
NOT 或 ! | 非,不是 |
DQL-聚合函数
-
介绍:将一列数据作为一个整体,进行纵向计算
-
常见聚合函数:
函数 | 功能 |
---|---|
coount | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
- 语法
SELECT 聚合函数(字段列表) FROM 表名;
DQL-分组查询
- 语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
- 注意:select * from user_tb group by gender;
这种语句会报错,因为无意义
执行顺序:where > 聚合函数 > having
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无意义
DQL-排序查询
- 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
- 排序方式
ASC: 升序(默认值)
DESC: 降序
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
DQL-分页查询
- 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
6. 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 | 查询数据 |
INSETRT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库、表、视图 |
CREATE | 创建数据库、表 |
- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
- 授予权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
- 撤销权限
REVOKE 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
二. 函数
函数是指一段可以直接被另一段程序调用的程序或代码
字符串函数
函数 | 功能 |
---|---|
CONCAT(S1,S2,…Sn) | 字符串拼接 |
LOWER(str) | 转小写 |
UPPER(str) | 转大写 |
LPAD(str ,n, pad) | 左填充,用pad对str左边填充,达到n个字符串的长度 |
RPAD | 右填充 |
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后的时间值 (type为单位) |
DATEDIFF(date1, date2) | 返回起始时间date1和结束时间date2之间的天数(date1 - date2) |
流程函数
函数 | 功能 |
---|---|
IF (value, t, f) | 如果value为真返回t,否则返回f |
IFNULL (value1, value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [val1] THEN [res1] …ELSE [default] END | 如果val1为真,返回res1,否则返回default默认值 |
CASE[expr] WHEN [val1] THEN [res1] …ELSE[default] END | 如果expr的值等于val1,返回res1,否则返回default默认值 |
三. 约束
- 概念:约束是作用于表中字段的规则,用于限制存储在表中的数据。
- 目的:保证数据库中数据的正确、有效性和完整性。
- 分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一的、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据是,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | 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 | 当在父表中删除 / 更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除 / 更新。(NO ACTION) |
CASCADE | 当在父表中删除 / 更新对应记录时,首先检查该记录是否有外键,如果有,则也删除 / 更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有外键,如果有,则设置子表中该外键值为NULL (这就要求外键允许取NULL) |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值 (Innodb不支持) |
ALTER TABLE 表名
ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段)
REFERENCES 主表名(主表字段名)
ON UPDATE CASCADE
ON DELETE CASCADE;
四. 多表查询
多表关系
- 一对多(多对一)
案例:部门 与 员工
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,指向一的一方的主键 - 多对多
案例:学生 与 课程
关系:一个学生选修多门课程,一门课程可被多个学生选择
实现:建立第三张表,中间表至少包含两个外键,分别关联两方主键 - 一对一
案例:用户 与 用户详情
关系:多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
实现:在任意一方加入外键,关联另一方的主键,并设置外键为UNIQUE
多表查询概述
多表查询分类
连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接:
- 左外连接:查询左表所有数据,以及两张表交集部分数据
- 右外连接:查询右表所有数据,以及两张表交集部分数据
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
子查询
内连接
- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
外连接
- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
自连接
SELECT 字段列表 FROM 表A 别名A, 表A 别名B ON 条件...;
自连接查询,可以是内连接查询,也可以是外连接查询
比如员工表中领导也是员工,查某个员工领导是谁
联合查询-union, union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;
联合查询的多张表列数必须一致,字段类型也必须一致
union all会将全部数据合并在一起,union all会对合并之后的数据去重
子查询
SQL 语句中嵌套 SELECT 语句,称为嵌套查询,又称子查询
SLECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
-- 外部可以是 INSERT/ UPDATE/ DELETE/ SELECT
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:
- WHERE 之后
- FROM 之后
- SELECT 之后
列子查询
常见操作符:IN、NOT IN、ANY、SOME、ALL
ANY 和 SOME 一样,子查询返回列表中,有任意一个满足即可
ALL 子查询返回列表,必须所有值都满足
五. 事务
事务简介
是一组操作的集合,他是一个不可分割的工作单位,事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
- 默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即、隐式地提交事务。
事务操作
方式1
- 查看 / 设置事务提交方式
SELECT @@autocommit; -- 1为自动,0为手动
SET @@autocommit = 0;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
方式2
- 开启事务
START TRANSACTION 或者 BEGIN; -- 开始手动控制事务
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
事务四大特性
- 原子性 (Automacity): 事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性 (Consistency): 事务完成时,必须使所有的数据都保持一致的状态
- 隔离性 (Isolation): 数据库系统提供的隔离机制,保证事务在不受外部并发操作的独立环境下运行
- 持久性 (Durability): 事务一旦提交或回滚, 它对数据库中的数据的改变就是永久的
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影” |
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable Read(默认) | × | × | √ |
Serializable | × | × | × |
-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
-- 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
# session 是会话级别,代表仅针对当前客户端窗口有效
# global 是针对所有客户端窗口有效
- 事务隔离级别越高,数据越安全。性能越低