MySQL基础②

上节笔记补充：

字符串分割函数 SUBSTRING_INDEX
函数语法：
SUBSTRING_INDEX(str,delim,count)
str： 待分割字符串
delim：分割符
count： 取到第几个分割后的子字符串

该函数返回分隔符delim的出现次数之前的字符串str的子字符串。
如果count为正数，则返回最后分隔符左边的所有内容(从左边计数)。
如果count为负，则返回最后分隔符右侧的所有内容(从右开始计数)。
SUBSTRING INDEX()在搜索delim时执行区分大小写的匹配。

窗口函数
rank，排序函数    
语法：rank() over (partition by 字符串  order by 字符串 desc)
partition by用于对表分组，order by功能为对分组后的结果进行排序。
 

约束

用于限制表结构中存储的数据

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.1版本后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张图的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

常用约束

约束条件	关键字
主键	PRIMARY KEY
自动增长	AUTO_INCREMENT
不为空	NOT NULL
唯一	UNIQUE
逻辑条件	CHECK
默认值	DEFAULT

外键约束

添加外键：

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 行为 ON DELETE 行为;

多表查询

基础查询

合并查询（笛卡尔积，会展示所有组合结果）：
select * from employee, dept;

消除无效笛卡尔积：
select * from employee, dept where employee.dept = dept.id;

内连接查询

隐式内连接：
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 JOIN 表A 别名B ON 条件 ...;

联合查询

语法：

SELECT 字段列表 FROM 表A ...
UNION （ALL）
SELECT 字段列表 FROM 表B ...

注：union all得出的结果不去重，union得出的结果会去重

子查询

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);

即将一个select语句嵌套进另一个select语句，以此来简化代码。

事务

SELECT @@AUTOCOMMIT;	查看事务提交方式
SET @@AUTOCOMMIT = 0;	设置事务提交方式，1为自动提交，0为手动提交，该设置只对当前会话有效
COMMIT;	提交事务
ROLLBACK;	回滚事务

四大特性

原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
一致性(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 };