约束
- 主键约束
- 概述:约束是作用于表中字段上的规则,用于限制存储在表中的数据
- 目的:保证数据库中数据的正确,有效性和完整性
- 分类
约束 描述 关键 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE 主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRINARY KEY 默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT 检查约束(8.O.16版本之后) 保证字段满足某一个条件 CHECK 外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 FOREIGN KEY
-
外键约束
- 概念:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
- 添加外键
CREATE TABLE 表名( 字段名 数据类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERRENCES 主表 (主表列表) ); ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键名) REFRENCES 主表(主表列名);
-
删除外键
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 更新时的操作 ON DELETE 删除时的操作;
多表查询
多表关系
基本分为三种
- 一对多(多对一)
- 案例:部门 与 员工的关系
- 关系:一个部门对应多个员工,一个员工对应一个部门
- 实现: 在多的一方建立外键,指向一的一方的主键
- 多对多
- 案例:学生 与 课程的关系
- 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方外键
- 一对一
- 案例:用户 和 用户详情的关系
- 关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的(UNIQUE)
多表查询
- 多表查询分类
- 连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接:
- 左外连接:查询左表所有数据,以及两张表交集部分数据
- 右外连接:查询右表所有数据,以及两张表交集部分数据
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
- 子查询
- 连接查询
- 连接查询
- 内连接
- 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
内连接查询的时两张表交集的部分
- 隐式内连接
- 外连接
- 左外连接(相当于查询表1(左表)的所有数据 包含 表1 和 表2交集部分的数据)
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
- 右外连接(相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据)
SELECT 字段列表 FROM 表1 EIGHT [OUTER] JOIN 表2 ON 条件...;
- 左外连接(相当于查询表1(左表)的所有数据 包含 表1 和 表2交集部分的数据)
- 自连接(可以是内连接查询,也可以是外连接查询)
- 语法
SELECT 字段列表 FROM 表1 别名A JOIN 表A 别名B ON 条件...;
- 语法
- 内连接
- 联合查询 - union,union all
- 对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A... UNION [ALL] SELECT 字段列表 FROM 表B...;
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
- 对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
- 子查询
- SQL语句中嵌套SELECT 语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部的语句可以是INSERT / UPDATE / DELETE /SELECT 的任何一个。
- 根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 返回结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
- 常用操作符 : = <> > >= < <=
- 列子查询(子查询结果为一列)
- 子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
- 常用的操作符:IN 、NOT IN 、 ANY、 SOME 、 ALL
操作符 描述 IN 在指定的集合范围之内,多选一 NOT IN 不在指定的集合范围之内 ANY 子查询返回列表中,有任意一个满足即可 SOME 与ANY等同,使用SOME的地方都可以使用ANY ALL 子查询返回列表的所有值都必须满足
- 行子查询(子查询结果为一行)
- 子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
- 常用操作符: = 、 <> 、IN 、 NOT IN
- 表子查询(子查询结果为多行多列)
- 子查询返回的结果是多行多列,这种子查询称为表子查询。
- 常用的操作符:IN。
- 标量子查询(子查询结果为单个值)
- 根据子查询位置,分为:WHERE 之后、FROM 之后、SELECT 之后。
- SQL语句中嵌套SELECT 语句,称为嵌套查询,又称子查询。
事务
事务简介
事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
默认MySql的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
事务操作
-
- 查看/设置事务提交方式
SELECT @@autocommit; SET @@autocommit=0;
- 提交事务
COMMIT;
-
回滚事务
ROLLBACK;
- 查看/设置事务提交方式
-
D
-
开启事务
START TRANSACTION 或 BEGIN;
-
提交事务
COMMIT;
-
回滚事务
ROLLBACK;
-
事务四大特性(ACID)
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有数据都保持一致状态。
- 隔离性:(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,他对数据库中的数据的改变就是永久的。
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。 |
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable Read(默认) | × | × | √ |
Serializable | × | × | × |
--查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
--设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE]
注意: 事务隔离级别越高,数据越安全,但是性能越低。