三、约束
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | not null |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | default |
| 检查约束(8.0.16版本之后） | 保证字段值满足某一个条件 | check |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致 | foreign key |
一.约束演示
| 约束条件 | 约束关键词 | 字段名 | 字段含义 | 字段类型 |
|---|---|---|---|---|
| 主键，且自动增长 | primary key，auto_increment | id | id唯一标识 | int |
| 不为空，且唯一 | not null，unique | name | 姓名 | varchar（10） |
| 大于0，且小于等于120 | check | age | 年龄 | int |
| 如果未指定该值，默认为1 | default | status | 状态 | char（1） |
| 无 | gender | 性别 | char（1） |
对应建表语句
create table tb_use( id int auto_increment primary key comment 'ID唯一标识', name varchar(10) not null unique comment '姓名', age int check (age>0 && age<=120) comment '年龄', status char(1) default '1' comment '状态', gender char(1) comment '性别' );
二.外键约束
一、语法
1.添加外键
1.1定义 外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
1.2添加 外键
alter table 表名 add constraint 外键名称 foreign key （外键字段名）references 主表 (主表列名);
eg. 为emp表的dept_id字段添加外键约束,关联dept表的主键id。
alter table add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
二.删除外键
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/(set null) on delete cascade/(set null);
外键的删除和更新行为： eg : alter table emp constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null
四、多表查询
一、多表关系
- 一对多：在多的一方建立外键，指向一的一方的主键。
- 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
- 一对一：在任意一方加入外键，关联另外一方的主键，并设置外键为唯一的（UNIQUE）
二、多表查询
2.1、连接查询
2.1.1内连接
内连接查询的是两张表交集的部分
- 隐式内连接
select 字段列表 from 表1、表2 where 条件 ；
- 显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ；
例子：
select e.name,d.name from emp e,dept d where emp.dept_id =dept.id;
select e.name,d.name from emp e inner join dept don e.dept_id = d.id;
别名：在表名加空格取别名
2.1.2外连接
外连接查询表1（左表）的所有数据（包括两表相交）select
- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件；
- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件；
例子：
查询emp表的所有数据和对应的部门信息
select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id
内外连接区别：
当两表有数据无外键连接时，内连接只会显示有外键连接的部分，而外连接会显示有外键相连的所有信息，而未连接的部分也会做成表，但数据会用NULL代替。
2.1.3自连接
自连接查询，可以是内连接查询也可以是外连接查询。
自连接必须取别名。
select 字段列表 from 表A 别名A join 表B on 连接条件 ；
例子：
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;
3.联合查询
select 字段列表 from 表A
union[all]
select 字段列表 from 表B；
- all 会将查询到的重复内容也打印出来，不加all就不会
- 查询的字段列表内容要相同
4.子查询（嵌套查询）
select * from t1 where cplumn1=(select column1 from t2);
4.1标量子查询
子查询返回的结果是单个值
select * from emp where dept_id = (select id from dept where name = '销售部' );
4.2列表子查询
子查询返回的结果是一列（可以是多行）
| 操作符 | 描述 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') );
4.3行子查询
子查询返回的结果是一行（可以是多列）
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
4.4表子查询
子查询返回的结果是多行多列
查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name ='鹿杖客' or name = '宋远桥' );
查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
五、事务
事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。转账操作。
一、事务操作
1.1设置事务提交方式
select @@autocommit;查看事务提交方式
set @@autocommit = 0;
@@autocommit=1为自动提交，@@autocommit=0为手动提交
手动提交：任务结束后，用户要输入提交事务`commit;`才能生效任务，可以用于反应任务是否出错
- 提交事务
commit;
- 回滚事务
rollback;
1.2开启事务
start transaction 或 begin;
- 提交事务
commit
- 回滚事务
rollback
二、事务四大特征
- 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
三、并发事务问题
| 问题 | 描述 |
|---|---|
| 赃读 | 一个事务读到另外一个事务还没有提交的数据。 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据。经过设置后解决不可重复读问题，再查询数据又没有了 |
四、事务隔离级别
(解决并发事务问题)
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted（读未提交） | 无法解决 | 无法解决 | 无法解决 |
| Read committed（读已提交） | 可以解决 | 无法解决 | 无法解决 |
| Repeatable Read(默认)（可重复读） | 可以解决 | 可以解决 | 无法解决 |
| Serializable（串行化） | 可以解决 | 可以解决 | 可以解决 |
SERIALIZABLE是 MySQL 中最高的隔离级别，在该隔离级别下，会使用行级锁定机制，对读取的所有数据行都加上排他锁，避免了幻读的问题。当一个事务访问某个表时，其他事务不能对该表执行任何操作（包括读），除非该事务结束。这种方法可以确保事务的隔离性，但是可能会导致更多的锁竞争和性能下降。因此，使用SERIALIZABLE隔离级别需要根据具体情况进行权衡和选择。
-
查看事务隔离级别
select @@transaction_isolation
-
设置事务隔离级别
set [session|global] transaction level （read uncommitted | Read committed | Repeatable Read | Serializable）;
session 当前会话窗口有效
global 所以会话窗口有效
当设置为serializable级别时，另外一个会话要等前面的提交后才能提交。
- 事务隔离级别越高，数据越安全，但性能越低。
总结
以上是数据库的基础篇内容，全都是由自己边学边记的内容，可能不是特别全面和完善，希望多多指正，共同进步，后面会继续出进阶篇和运维篇章的.
