MySQL 数据库 - 函数 约束 多表查询 事务

函数

字符串函数

数值函数

日期函数

流程函数

约束

常用约束

外键约束

多表查询

多表关系

一对多

多对多

一对一

查询

内连接查询

外连接查询

自连接查询

联合查询 union, union all

子查询

标量子查询

列子查询

行子查询

表子查询

事务

四大特性 ACID

并发事务

函数

字符串函数

常用函数：

函数 功能

CONCAT(s1, s2, …, sn) 字符串拼接，将 s1, s2, …, sn 拼接成一个字符串

LOWER(str) 将字符串全部转为小写

UPPER(str) 将字符串全部转为大写

LPAD(str, n, pad) 左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符串长度

RPAD(str, n, pad) 右填充，用字符串 pad 对 str 的右边进行填充，达到 n 个字符串长度

TRIM(str) 去掉字符串头部和尾部的空格

SUBSTRING(str, start, len) 返回从字符串 str 从 start 位置起的 len 个长度的字符串

-- 拼接

SELECT CONCAT('Hello', 'World');

-- 小写

SELECT LOWER('Hello');

-- 大写

SELECT UPPER('Hello');

-- 左填充

SELECT LPAD('01', 5, '-');

-- 右填充

SELECT RPAD('01', 5, '-');

-- 去除空格

SELECT TRIM(' Hello World ');

-- 切片（起始索引为 1）

SELECT SUBSTRING('Hello World', 1, 5);

数值函数

常见函数：

函数 功能

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 后的时间值

DATEDIFF(date1, date2) 返回起始时间 date1 和结束时间 date2 之间的天数

select curdate();

select curtime();

select now();

select year(now());

select month(now());

select day(now());

select date_add(now(),interval 70 day );

select datediff('2022-06-29','2022-06-01');

流程函数

常用函数：

函数 功能

IF(value, t, f) 如果 value 为 true，则返回 t，否则返回 f

IFNULL(value1, value2) 如果 value1 不为空，返回 value1，否则返回 value2

CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END 如果 val1 为 true，返回 res1，… 否则返回 default 默认值

CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END 如果 expr 的值等于 val1，返回 res1，… 否则返回 default 默认值

select

name,

(case when age > 30 then '中年' else '青年' end)

from employee;

select

name,

(case workaddress when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址'

from employee;

约束

常用约束

约束条件 关键字

主键 PRIMARY KEY

自动增长 AUTO_INCREMENT

不为空 NOT NULL

唯一 UNIQUE

逻辑条件 CHECK

默认值 DEFAULT

create table user(

id int primary key auto_increment,

name varchar(10) not null unique,

age int check(age > 0 and age < 120),

status char(1) default '1',

gender char(1)

);

外键约束

添加外键：

CREATE TABLE 表名(

字段名 字段类型,

...

[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)

);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

-- 例子

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

多表查询

多表关系

一对多（多对一）

多对多

一对一

一对多

案例：部门与员工

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生与课程

关系：一个学生可以选多门课程，一门课程也可以供多个学生选修

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例：用户与用户详情

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

查询

合并查询（笛卡尔积，会展示所有组合结果）：

select * from employee, dept;

笛卡尔积：两个集合 A 集合和 B 集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

消除无效笛卡尔积：

select * from employee, dept where employee.dept = dept.id;

内连接查询

内连接查询的是两张表交集的部分

隐式内连接：

SELECT 字段列表 FROM 表 1, 表 2 WHERE 条件 ...;

显式内连接：

SELECT 字段列表 FROM 表 1 [ INNER ] JOIN 表 2 ON 连接条件 ...;

显式性能比隐式高

-- 查询员工姓名，及关联的部门的名称

-- 隐式

select e.name, d.name from employee as e, dept as d where e.dept = d.id;

-- 显式

select e.name, d.name from employee as e inner join dept as d on e.dept = d.id;

外连接查询

左外连接：

查询左表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表 1 LEFT [ OUTER ] JOIN 表 2 ON 条件 ...;

相当于查询表 1 的所有数据，包含表 1 和表 2 交集部分数据

右外连接：

查询右表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表 1 RIGHT [ OUTER ] JOIN 表 2 ON 条件 ...;

-- 左

select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;

select d.name, e.* from dept d left outer join emp e on e.dept = d.id; -- 这条语句与下面的语句效果一样

-- 右

select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id;

左连接可以查询到没有 dept 的 employee，右连接可以查询到没有 employee 的 dept

自连接查询

当前表与自身的连接查询，自连接必须使用表别名

语法：

SELECT 字段列表 FROM 表 A 别名 A JOIN 表 A 别名 B ON 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询

例子：

-- 查询员工及其所属领导的名字

select a.name, b.name from employee a, employee b where a.manager = b.id;

-- 没有领导的也查询出来

select a.name, b.name from employee a left join employee b on a.manager = b.id;

联合查询 union, union all

把多次查询的结果合并，形成一个新的查询集

语法：

SELECT 字段列表 FROM 表 A ...

UNION [ALL]

SELECT 字段列表 FROM 表 B ...

注意事项

UNION ALL 会有重复结果，UNION 不会

联合查询比使用 or 效率高，不会使索引失效

子查询

SQL 语句中嵌套 SELECT 语句，称谓嵌套查询，又称子查询。

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

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为：

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

列子查询（子查询结果为一列）

行子查询（子查询结果为一行）

表子查询（子查询结果为多行多列）

根据子查询位置可分为：

WHERE 之后

FROM 之后

SELECT 之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。

常用操作符：- < > > >= < <=

例子：

-- 查询销售部所有员工

select id from dept where name = '销售部';

-- 根据销售部部门 ID，查询员工信息

select * from employee where dept = 4;

-- 合并（子查询）

select * from employee where dept = (select id from dept where name = '销售部');

-- 查询 xxx 入职之后的员工信息

select * from employee where entrydate > (select entrydate from employee where name = 'xxx');

列子查询

返回的结果是一列（可以是多行）。

常用操作符：

操作符 描述

IN 在指定的集合范围内，多选一

NOT IN 不在指定的集合范围内

ANY 子查询返回列表中，有任意一个满足即可

SOME 与 ANY 等同，使用 SOME 的地方都可以使用 ANY

ALL 子查询返回列表的所有值都必须满足

例子：

-- 查询销售部和市场部的所有员工信息

select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');

-- 查询比财务部所有人工资都高的员工信息

select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));

-- 查询比研发部任意一人工资高的员工信息

select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));

行子查询

返回的结果是一行（可以是多列）。

常用操作符：=, <, >, IN, NOT IN

例子：

-- 查询与 xxx 的薪资及直属领导相同的员工信息

select * from employee where (salary, manager) = (12500, 1);

select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');

表子查询

返回的结果是多行多列

常用操作符：IN

例子：

-- 查询与 xxx1，xxx2 的职位和薪资相同的员工

select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');

-- 查询入职日期是 2006-01-01 之后的员工，及其部门信息

select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;

事务

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

基本操作：

-- 1. 查询张三账户余额

select * from account where name = '张三';

-- 2. 将张三账户余额-1000

update account set money = money - 1000 where name = '张三';

-- 此语句出错后张三钱减少但是李四钱没有增加

模拟 sql 语句错误

-- 3. 将李四账户余额+1000

update account set money = money + 1000 where name = '李四';

-- 查看事务提交方式

SELECT @@AUTOCOMMIT;

-- 设置事务提交方式，1 为自动提交，0 为手动提交，该设置只对当前会话有效

SET @@AUTOCOMMIT = 0;

-- 提交事务

COMMIT;

-- 回滚事务

ROLLBACK;

-- 设置手动提交后上面代码改为：

select * from account where name = '张三';

update account set money = money - 1000 where name = '张三';

update account set money = money + 1000 where name = '李四';

commit;

操作方式二：

开启事务：

START TRANSACTION 或 BEGIN TRANSACTION;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

操作实例：

start transaction;

select * from account where name = '张三';

update account set money = money - 1000 where name = '张三';

update account set money = money + 1000 where name = '李四';

commit;

四大特性 ACID

原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败

一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态

隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务

问题 描述

脏读 一个事务读到另一个事务还没提交的数据

不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同

幻读 一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

并发事务隔离级别：

隔离级别 脏读 不可重复读 幻读

Read uncommitted √ √ √

Read committed × √ √

Repeatable Read(默认) × × √

Serializable × × ×

√表示在当前隔离级别下该问题会出现

Serializable 性能最低；Read uncommitted 性能最高，数据安全性最差

查看事务隔离级别：

SELECT

设置事务隔离级别：

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };

SESSION 是会话级别，表示只针对当前会话有效，GLOBAL 表示对所有会话有效