MySql-函数-约束-多表查询-事务总结

一、函数

(1)字符串函数：

函数	功能
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个长度的字符串

(2)数值函数:

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand()	返回0~1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

(3)日期函数:

函数	功能
curdate()	返回当前日期
curtimr()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
mouth(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval,exprtype)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff	返回起始时间date1 和 结束时间date2之间的天数

(4)流程函数:

函数	功能
if(value,t,f)	如果value为true，则返回t，否则返回f
ifnull(value1 , value2)	如果value1不为空，返回value1，否则返回value2
case where [ vall ] then [ res1 ]…else [ default ] end	如果val1为true，返回res1，… 否则返回default默认值
case [ expr ] where [ vall ] then [ res1 ]…else [ default ] end	如果expr的值等于val1，返回res1，… 否则返回default默认值

二、约束

约束	描述	关键字
非空约束	限制该字段的数据不能为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	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 no action 一致) 默认行为
cascade	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
set null	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。
set default	父表有变更时，子表将外键列设置成一个默认的值 (innodb不支持)

具体语法：

• alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名) on update cascade on delete cascade;

三、多表查询

分类：

• 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据
    • 自连接：当前表与自身的连接查询，自连接必须使用表别名
• 子查询

(1)内连接:

• 隐式内连接：select 字段列表 from 表1,表2 where 条件 …
• 显式内连接：select 字段列表 from 表1 [ inner ] join 表2 on 连接条件…;

  注意事项:一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

(2)外连接：

• 左外连接：select 字段列表 from 表1 left [ outer ] join 表2 on 条件…;

  左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

• 右外连接：select 字段列表 from 表1 right [ outer ] join 表2 on 条件…;

  右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据

注意事项：
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

(3)自连接：
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

• select 字段列表 from 表a 别名a join 表a 别名b on 条件…;

注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

(4)联合查询：
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

• selete 字段列表 from 表A … union [ all ] selete 字段列表 from 表B…

  对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
  union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

(5)子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

标量子查询（子查询结果为单个值）：

子查询返回的结果是单个值（数字、字符串、日期等）。(如：select id from dept where name = ‘销售部’😉

列子查询(子查询结果为一列)：

常用的操作符：in 、not in 、 any 、some 、 all

操作符	描述
in	在指定的集合范围之内，多选一
not in	不在指定的集合范围之内
any	子查询返回列表中，有任意一个满足即可
some	与any等同，使用some的地方都可以使用any
all	子查询返回列表的所有值都必须满足

行子查询(子查询结果为一行)：

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、in 、not in

表子查询(子查询结果为多行多列):

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符:in

根据子查询位置，分为：
A. where之后
B. from之后
C. select之后

四、事务：

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

控制事务一:

• 查看事务提交方式 ：selete @@autocommit ;
• 设置事务提交方式 : set @@autocommit = 0 ;(1是自动，0是手动)
• 提交事务：commit;
• 回滚事务:rollback;

  注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

控制事务二:

• 开启事务：start transaction 或 begin
• 提交事务：commit
• 回滚事务：rollback

事务四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
Serializable	×	×	×

• 查看事务隔离级别：selete @@transaction_isolation
• 设置事务隔离级别：set [ session ] [ global] transaction isolation level 隔离级别

  注意：事务隔离级别越高，数据越安全，但是性能越低。