【mysql入门笔记02 2022.10.03】

函数

• 函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

• mysql中内置了很多字符串函数，常用如下

函数	功能
concat(s1,s2,…sn)	字符串拼接，将s1,s2…sn拼接成1个字符串
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个长度的字符串

concat(s1,s2,…sn)

lower(str)

upper(str)

lpad(str,n,pad)

rpad(str,n,pad)

trim(str)

substring(str,start,len)

案例

• 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的0补齐，如1号员工的工号为00001。
  
  
  

数值函数

• 常见数值函数如下

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

ceil(x)

floor(x)

mod(x,y)

rand()

round(x,y)

案例

• 根据数据库的函数，生成一个六位数的随机验证码
  

日期函数

• 常见日期函数如下

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

curdate()

curtime()

now()

year(date)

month(date)

day(date)

date_add(date,interval expr type)

datediff(date1,date2)

• 前者时间-后者时间
  

案例

• 查询所有员工的入职天数，并根据入职天数倒叙排序
  
  

流程函数

• 常用的一类函数，可以在sql语句中实现条件筛选，从而提高语句的效率

函数	功能
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默认值

if(value,t,f)

• true和flase位置应该是expr条件表达式

ifnull(value1,value2）

• 如果第一个值不为null则返回第一个值，如果为null，则返回第二个值

case when [val1] then [res1]…else[default] end

• 查询员工的工作地址，如果是北京、上海则是一线城市，其他为二线城市
  
  

case [expr] when [val1] then [res1]…else[default] end

案例

• 统计学员成绩，大于等于85为优秀，大于等于60为几个，否则展示不及格
  

约束

概述

• 约束是作用于表中字段上的规则，用于限制存储在表中的数据。
• 目的是保证数据中数据的正确、有效性和完整性
• 分类, 约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

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

约束演示

• 根据需求，创建表结构,注意最后一列

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，且自动增长	PRIMARY KEY,AUTO_INCREMENT
name	姓名	varchar(10)	不为空，且唯一	NOT NULL,UNIQUE
age	年龄	int	大于0并且小于等于120	CHECK
status	状态	char(1)	如果没有指定该值，默认为1	DEFAULT
gender	性别	char(1)	无	无

• 建表语句如下
  
  
• id 自增，无需添加 验证主键约束
  
• name 验证非空约束和唯一约束
  
• age 验证检查约束
  
• 验证非空约束和唯一约束时已向数据申请了主键。
  
• status 默认值为1，验证默认约束
  
  
• 以上命令行操作也可图形化界面操作

外键约束

外键说明及环境搭建

• 用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
  
• 部门ID和另一张表的部门ID主键，dep_id就是部门表的外键，外键关联主键，包含外键的为子表，包含主键的为父表，但这层只是逻辑层面的，假如删除父表的数据，不会影响子表，但是子表的员工数据不完整了。
• 创建两张表
  
  
• 添加数据
  
  
• 目前两张表只有逻辑上有关系，如果删除1号研发部门，则员工表的数据不完整了，没有外键关联，无法保证数据的完整性和一致性

外键语法

创建表的时候直接添加

• 添加外键
• create table 表名(
  字段名 数据类型
  …
  …
  [constraint ] [外键名称] foreign key(外键字段名） references 主表(主表列名)

表创建完毕后额外添加

• alter table 表名 add constraint 外键名称 foreign key(外键字段名) feferences 主表(主表列名);

演示

• 尝试删除部门表中的数据，报错如下
  
• 删除外键
  
• 再次删除部门表，测试，可以正常删除
  

删除/更新行为

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致）
RESTRICT	当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则不允许删除/更新。(与NO ACTION 一致)
CASCADE	当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则也删除/更新外键在子表中的记录
SET NULL	当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则设置自表中该外键值为null(这就要求该外键允许取NULL)
SET DEFAULT	父表有更新时，子表将外键列设置成一个默认的值(innodb不支持)

cascade

• alter table 表名 add constraint 外键名称 foreign key(外键字段名) feferences 主表(主表列名) ON UPDATE CASCADE ON
  DELETE CASCADE;

 alter table empp add constraint fk_empp_dep_id foreign key (dept_id) references dept(id) ON UPDATE CASCADE ON DELETE CASCADE;

• 将研发部的ID修改为6，查看员工表的信息也都变了
  
  
• 如果删除部门表的某一部门，则员工表的信息也会被删除
  
  

set null

 alter table empp add constraint fk_empp_dep_id foreign key (dept_id) references dept(id) ON UPDATE SET NULL ON DELETE SET NULL;

• 在navicat中，右键设计表
  

多表查询

多表关系

• 在项目开发中，在进行数据库表结构设计时，会根据也无需求及业务模块之间的关系，分析并设计表结构，由于业务之间的相互关联，所以各个表结构之间也存在着各种联系

一对多（多对一）

• 案例 部门和员工的关系
• 关系 一个部门对应多个员工，一个员工对应多个部门
• 实现 在多的亿方建立外键 ，指向一的一方主键，如下
  

多对多

• 案例 学生和课程的关系
• 关系 一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
  

一对一

• 案例 用户与用户详情的关系
• 关系 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
• 实现 在任意一方加入外键，关联另外一方的主键，并且设置外建为唯一的（UNIQUE）
  
  
• 注意关键语句
  

多表查询概述

• 从多张表中查询数据
• 演示

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hd                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> use  test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept           |
| empp           |
| user           |
+----------------+
3 rows in set (0.00 sec)

• 笛卡尔积现象：当查询数据时，发现有20条数据，因为dept表 中4条，用户表中5条，乘积获得。
• 笛卡尔积是指在数学中，A集合和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

mysql> select * from dept,empp;
+----+--------+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+----+--------+------+----------+--------+------------+-----------+---------+
|  6 | 研发部 |  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       6 |
|  4 | 销售部 |  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       6 |
|  3 | 财务部 |  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       6 |
|  2 | 市场部 |  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       6 |
|  6 | 研发部 |  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       6 |
|  4 | 销售部 |  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       6 |
|  3 | 财务部 |  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       6 |
|  2 | 市场部 |  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       6 |
|  6 | 研发部 |  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       6 |
|  4 | 销售部 |  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       6 |
|  3 | 财务部 |  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       6 |
|  2 | 市场部 |  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       6 |
|  6 | 研发部 |  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       6 |
|  4 | 销售部 |  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       6 |
|  3 | 财务部 |  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       6 |
|  2 | 市场部 |  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       6 |
|  6 | 研发部 |  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       6 |
|  4 | 销售部 |  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       6 |
|  3 | 财务部 |  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       6 |
|  2 | 市场部 |  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       6 |
+----+--------+----+--------+------+----------+--------+------------+-----------+---------+
20 rows in set (0.00 sec)

• 正确查询方式

mysql> select * from empp,dept where empp.dept_id = dept.id;
+----+--------+------+----------+--------+------------+-----------+---------+----+--------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id | id | name   |
+----+--------+------+----------+--------+------------+-----------+---------+----+--------+
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       1 |  1 | 研发部 |
|  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       1 |  1 | 研发部 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       1 |  1 | 研发部 |
|  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       1 |  1 | 研发部 |
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       1 |  1 | 研发部 |
+----+--------+------+----------+--------+------------+-----------+---------+----+--------+
5 rows in set (0.00 sec)

内连接

• 相当于查询A表和B表交集部分数据
• 查询演示：查询每一个员工的姓名和关联的部门名称

mysql> select empp.name,dept.id from empp,dept where empp.dept_id = dept.id;
+--------+----+
| name   | id |
+--------+----+
| 张无忌 |  1 |
| 杨逍   |  1 |
| 韦一笑 |  1 |
| 常遇春 |  1 |
| 小昭   |  1 |
+--------+----+
5 rows in set (0.00 sec)

• 但当用户的部门ID值为空的时，是查询不到数据的
  
• 别名效果演示
• from 后加别名，给empp加别名e,dept加别名d，则将语句中的表名全部替换为别名，效果一样

mysql> select e.name,d.id from empp e,dept d where e.dept_id = d.id;
+--------+----+
| name   | id |
+--------+----+
| 金庸   |  2 |
| 张无忌 |  5 |
| 杨逍   |  5 |
| 韦一笑 |  5 |
| 常遇春 |  5 |
| 小昭   |  5 |
+--------+----+
6 rows in set (0.00 sec)

隐式内连接

• select 字段列表 from 表1, 表2 where 条件…;

mysql> select e.name,d.id from empp e  [inner 可省略] join dept d where e.dept_id = d.id;
+--------+----+
| name   | id |
+--------+----+
| 金庸   |  1 |
| 张无忌 |  5 |
| 杨逍   |  5 |
| 韦一笑 |  5 |
| 常遇春 |  5 |
| 小昭   |  5 |
+--------+----+
6 rows in set (0.00 sec)

显式内连接

• select 字段列表 from 表1 [inner] join, 表2 on 连接条件…;

外连接

• 左外用的较多，经常把右外改成左外

左外连接

• 查询左表所有数据，以及两张表交集部分数据。
• select 字段列表 from 表1 left [outer 可省略] join 表2 on 条件…;
• 示例 查询empp表格的所有数据和对应的部门信息

mysql> select e.*, d.name from  empp e left outer join dept d on e.dept_id=d.id;
+----+--------+------+----------+--------+------------+-----------+---------+--------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id | name   |
+----+--------+------+----------+--------+------------+-----------+---------+--------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       1 | 总经办 |
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 | 研发部 |
|  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       5 | 研发部 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 | 研发部 |
|  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       5 | 研发部 |
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 | 研发部 |
|  7 | 啊文   |   32 | 财务     |   5000 | 2008-01-01 |         4 |    NULL | NULL   |
+----+--------+------+----------+--------+------------+-----------+---------+--------+
7 rows in set (0.00 sec)

右外连接

• 查询游标所有数据，以及两张表交集部分数据。
• select 字段列表 from 表1 right [outer] join 表2 on 条件…;
• 示例 查询dept表格的所有数据和对应的部员工信息

mysql> select d.*,e.* from  empp e right outer join dept d on e.dept_id=d.id;
+----+--------+------+--------+------+----------+--------+------------+-----------+---------+
| id | name   | id   | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+--------+------+----------+--------+------------+-----------+---------+
|  1 | 总经办 |    1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       1 |
|  2 | 市场部 | NULL | NULL   | NULL | NULL     |   NULL | NULL       |      NULL |    NULL |
|  3 | 财务部 | NULL | NULL   | NULL | NULL     |   NULL | NULL       |      NULL |    NULL |
|  4 | 销售部 | NULL | NULL   | NULL | NULL     |   NULL | NULL       |      NULL |    NULL |
|  5 | 研发部 |    6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |
|  5 | 研发部 |    5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       5 |
|  5 | 研发部 |    4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |
|  5 | 研发部 |    3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       5 |
|  5 | 研发部 |    2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
+----+--------+------+--------+------+----------+--------+------------+-----------+---------+
9 rows in set (0.00 sec)

联合查询

• 就是把多次查询的结果合并起来，形成一个新的查询结果集
• 对于联合查询，多张表的列数必须保持一直，字段类型也需要保持一致。
• union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重。
• 语法 select 字段列表 from 表 A…
  UNION [ALL]
  select 字段列表 from 表B …;
• 示例 将薪资低于8000的员工和年龄小于50的员工全部查询

mysql> select * from empp where salary < 8000
    -> union
    -> select *from empp where age < 50;
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |
|  7 | 啊文   |   32 | 财务     |   5000 | 2008-01-01 |         4 |    NULL |
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
|  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       5 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |
|  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       5 |
+----+--------+------+----------+--------+------------+-----------+---------+
6 rows in set (0.00 sec)

自连接

• 当前表与自身的连接查询，自连接必须使用表别名
• 语法 select 字段列表 from 表A 别名A join 表B 别名B ON 条件…;
• 自连接查询，可以是内连接查询，也可以是外连接查询
• 示例 查询员工和所属领导名字（managerid）

mysql> select a.name,b.name from empp a,empp b where a.managerid = b.id;
+--------+--------+
| name   | name   |
+--------+--------+
| 张无忌 | 金庸   |
| 杨逍   | 张无忌 |
| 韦一笑 | 张无忌 |
| 常遇春 | 杨逍   |
| 小昭   | 张无忌 |
| 啊文   | 韦一笑 |
+--------+--------+
6 rows in set (0.00 sec)

• 示例 查询所有员工 及领导的名字，如果员工没有领导，也需要查询出来

mysql> select a.name,b.name from empp a left join empp b on a.managerid = b.id;
+--------+--------+
| name   | name   |
+--------+--------+
| 金庸   | NULL   |
| 张无忌 | 金庸   |
| 杨逍   | 张无忌 |
| 韦一笑 | 张无忌 |
| 常遇春 | 杨逍   |
| 小昭   | 张无忌 |
| 啊文   | 韦一笑 |
+--------+--------+
7 rows in set (0.00 sec)

子查询

概念及语法

• SQL语句中嵌套select语句，称为嵌套查询，又称为查询
• 语法 select * from t1 where column1=(select column1 from t2);
• 子查询外部的语句可以使insert update delete select 的任何一个

子查询结果分类

标量子查询

• 子查询的结果为单个值可以是数字，字符串，日期等，常用的符号有 = <> <= < <=
• 示例1 查询所有销售员工信息

mysql> select * from empp where dept_id = (select id from dept where name = '销售部');
+----+--------+------+------+--------+------------+-----------+---------+
| id | name   | age  | job  | salary | induction  | managerid | dept_id |
+----+--------+------+------+--------+------------+-----------+---------+
|  5 | 常遇春 |   43 | 财务 |  10500 | 2004-09-07 |         3 |       4 |
+----+--------+------+------+--------+------------+-----------+---------+
1 row in set (0.00 sec)
- 示例 2 查询在韦一笑之后入职的员工信息
mysql> select * from empp where induction > (select induction from empp where name = '韦一笑');
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
|  5 | 常遇春 |   43 | 财务     |  10500 | 2004-09-07 |         3 |       4 |
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |
|  7 | 啊文   |   32 | 财务     |   5000 | 2008-01-01 |         4 |       3 |
+----+--------+------+----------+--------+------------+-----------+---------+
4 rows in set (0.00 se

列子查询

• 子查询结果为一列（可以是多行）
• 常用操作符

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

• 案例一 查询销售部和财务部的所有员工，先查询市场部和财务部的员工信息，在查询销售部和财务部的员工ID

mysql> select * from empp where dept_id in (select id from dept where name='销售部' or name='财务部');
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  7 | 啊文   |   32 | 财务     |   5000 | 2008-01-01 |         4 |       3 |
|  3 | 杨逍   |   33 | 销售总监 |   8400 | 2000-11-03 |         2 |       4 |
|  5 | 常遇春 |   43 | 财务     |  10500 | 2004-09-07 |         3 |       4 |
+----+--------+------+----------+--------+------------+-----------+---------+
3 rows in set (0.00 sec)

• 案例二 查询比财务部所有人工资都高的人，先查询财务部的部门ID，在查询财务部所有人员的工资，再查询结果。
• 实现方式还可以是工资高于财务部最高工资的人

mysql> select * from empp where salary > all (select salary from empp where dept_id = (select id from dept where name = '财务部'));
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       1 |
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
|  3 | 杨逍   |   33 | 销售总监 |   8400 | 2000-11-03 |         2 |       4 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |
|  5 | 常遇春 |   43 | 财务     |  10500 | 2004-09-07 |         3 |       4 |
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |
+----+--------+------+----------+--------+------------+-----------+---------+
6 rows in set (0.00 sec)

• 案例三 比研发部任意一人工资高的员工信息

mysql> select * from empp where salary > any (select salary from empp where dept_id = (select id from dept where name = '研发部'));
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       1 |
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
|  3 | 杨逍   |   33 | 销售总监 |   8400 | 2000-11-03 |         2 |       4 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |
|  5 | 常遇春 |   43 | 财务     |  10500 | 2004-09-07 |         3 |       4 |
+----+--------+------+----------+--------+------------+-----------+---------+
5 rows in set (0.00 sec)

行子查询

• 子查询结果为一行，可以是多列.
• 常用操作符 ：=、<>、in、not in
• 示例 查询与张无忌的薪资和直属领导相同的员工信息

mysql> select salary,managerid from empp where name = '张无忌';
+--------+-----------+
| salary | managerid |
+--------+-----------+
|  12500 |         1 |
+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from empp where (salary,managerid) = (12500,1);
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
+----+--------+------+----------+--------+------------+-----------+---------+
1 row in set (0.00 sec)
mysql> select * from empp where (salary,managerid) = (select salary,managerid from empp where name = '张无忌');
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
+----+--------+------+----------+--------+------------+-----------+---------+
1 row in set (0.00 sec)

表子查询

• 子查询结果为多行多列
• 常用的操作符是in
• 示例1 查询与常遇春，小昭职位和薪资相同的员工信息

mysql> select job,salary from empp where name = '常遇春' or name ='小昭';
+--------+--------+
| job    | salary |
+--------+--------+
| 财务   |  10500 |
| 程序员 |   6600 |
+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from empp where(job,salary) in ( select job,salary from empp where name = '常遇春' or name ='小昭');
+----+--------+------+--------+--------+------------+-----------+---------+
| id | name   | age  | job    | salary | induction  | managerid | dept_id |
+----+--------+------+--------+--------+------------+-----------+---------+
|  5 | 常遇春 |   43 | 财务   |  10500 | 2004-09-07 |         3 |       4 |
|  6 | 小昭   |   19 | 程序员 |   6600 | 2004-10-12 |         2 |       5 |
+----+--------+------+--------+--------+------------+-----------+---------+
2 rows in set (0.00 sec)

• 示例2 查询入职日期是2004年之后的员工信息，及其部门信息

mysql>  select * from empp where induction > '2004-01-01';
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
|  5 | 常遇春 |   43 | 财务     |  10500 | 2004-09-07 |         3 |       4 |
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |
|  7 | 啊文   |   32 | 财务     |   5000 | 2008-01-01 |         4 |       3 |
+----+--------+------+----------+--------+------------+-----------+---------+
4 rows in set (0.00 sec)
mysql>  select e.*, d.* from  (select * from empp where induction > '2004-01-01') e left join dept d on e.dept_id =d.id;
+----+--------+------+----------+--------+------------+-----------+---------+------+--------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id | id   | name   |
+----+--------+------+----------+--------+------------+-----------+---------+------+--------+
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |    5 | 研发部 |
|  5 | 常遇春 |   43 | 财务     |  10500 | 2004-09-07 |         3 |       4 |    4 | 销售部 |
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |    5 | 研发部 |
|  7 | 啊文   |   32 | 财务     |   5000 | 2008-01-01 |         4 |       3 |    3 | 财务部 |
+----+--------+------+----------+--------+------------+-----------+---------+------+--------+
4 rows in set (0.00 sec)

子查询位置

• where之后
• from之后
• select 之后

多表查询案例

• 当sql语句条件越来越多越复杂时，长度过长无法只管查看，可以选中sql语句进行格式化。
• 新建工资登记表，并插入数据

create table salgrade(
grade int,
losal int,
hisal int
);
insert into salgrade values (1,0,3000); 
insert into salgrade values (2,3001,5000); 
insert into salgrade values (3,5001,8000); 
insert into salgrade values (4,8001,10000); 
insert into salgrade values (5,10001,15000); 
insert into salgrade values (6,15001,20000); 
insert into salgrade values (7,20001,25000); 
insert into salgrade values (8,25001,30000); 

• 查询员工的姓名、年龄、职位、部门信息（隐式内连接）

mysql> select e.name ,e.age, e.job, d.name from  empp e ,dept d where e.dept_id = d.id;
+--------+------+----------+--------+
| name   | age  | j ob      | name   |
+--------+------+----------+--------+
| 金庸   |   66 | 总裁     | 总经办 |
| 张无忌 |   20 | 项目经理 | 研发部 |
| 杨逍   |   33 | 销售总监 | 销售部 |
| 韦一笑 |    4 | 开发     | 研发部 |
| 常遇春 |   43 | 财务     | 销售部 |
| 小昭   |   19 | 程序员   | 研发部 |
| 啊文   |   32 | 财务     | 财务部 |
+--------+------+----------+--------+
7 rows in set (0.00 sec)

• 查询年龄小于30岁的员工姓名、年龄、职位、部门信息（显式内连接）

mysql> select e.name ,e.age, e.job, d.name from  empp e inner join dept d on  e.dept_id = d.id where e.age < 30;
+--------+------+----------+--------+
| name   | age  | job      | name   |
+--------+------+----------+--------+
| 张无忌 |   20 | 项目经理 | 研发部 |
| 韦一笑 |    4 | 开发     | 研发部 |
| 小昭   |   19 | 程序员   | 研发部 |
+--------+------+----------+--------+
3 rows in set (0.00 sec)

• 查询拥有员工的部门ID、部门名称：即求取员工表和部门表的交集，内连接即可

mysql> select distinct d.id ,d.name from empp e ,dept d where e.dept_id=d.id;
+----+--------+
| id | name   |
+----+--------+
|  1 | 总经办 |
|  3 | 财务部 |
|  4 | 销售部 |
|  5 | 研发部 |
+----+--------+
4 rows in set (0.00 sec)

• 查询所有年龄大于40岁的员工，及其归属部门的名称;如果员工没有分配部门，也需要展示出来

mysql> select e.*,d.name from empp e left join dept d on e.dept_id = d.id where e.age > 40;
+----+--------+------+------+--------+------------+-----------+---------+--------+
| id | name   | age  | job  | salary | induction  | managerid | dept_id | name   |
+----+--------+------+------+--------+------------+-----------+---------+--------+
|  1 | 金庸   |   66 | 总裁 |  20000 | 2000-01-01 |      NULL |       1 | 总经办 |
|  5 | 常遇春 |   43 | 财务 |  10500 | 2004-09-07 |         3 |       4 | 销售部 |
+----+--------+------+------+--------+------------+-----------+---------+--------+
2 rows in set (0.00 sec)

• 查询所有员工的工资等级：连接条件 empp.salary >= salgrade.losal and empp.salary <= salgrade.hisal

mysql> select e.*,s.grade from empp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;   在什么什么之间也可以用between and
+----+--------+------+----------+--------+------------+-----------+---------+-------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id | grade |
+----+--------+------+----------+--------+------------+-----------+---------+-------+
|  7 | 啊文   |   32 | 财务     |   5000 | 2008-01-01 |         4 |       3 |     2 |
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |     3 |
|  3 | 杨逍   |   33 | 销售总监 |   8400 | 2000-11-03 |         2 |       4 |     4 |
|  5 | 常遇春 |   43 | 财务     |  10500 | 2004-09-07 |         3 |       4 |     5 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |     5 |
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |     5 |
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       1 |     6 |
+----+--------+------+----------+--------+------------+-----------+---------+-------+
7 rows in set (0.00 sec)

• 查询研发部所有员工的信息和工资等级：三张表（连接条件3-1，两个两个表的梳理，多个连接条件用and连接）

mysql>  select e.* ,s.grade from empp e,dept d ,salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部';
+----+--------+------+----------+--------+------------+-----------+---------+-------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id | grade |
+----+--------+------+----------+--------+------------+-----------+---------+-------+
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |     3 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |     5 |
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |     5 |
+----+--------+------+----------+--------+------------+-----------+---------+-------+
3 rows in set (0.00 sec)

• 查询研发部的员工平均工资

mysql> select * from empp e,dept d where e.dept_id = d.id and d.name = '研发部';
+----+--------+------+----------+--------+------------+-----------+---------+----+--------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id | id | name   |
+----+--------+------+----------+--------+------------+-----------+---------+----+--------+
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |  5 | 研发部 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |  5 | 研发部 |
|  6 | 小昭   |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |  5 | 研发部 |
+----+--------+------+----------+--------+------------+-----------+---------+----+--------+
3 rows in set (0.00 sec)
mysql> select avg(e.salary) from empp e,dept d where e.dept_id = d.id and d.name = '研发部';
+---------------+
| avg(e.salary) |
+---------------+
|    10033.3333 |
+---------------+
1 row in set (0.00 sec)

• 查询工资比小昭高的员工信息

mysql> select * from empp where salary > (select salary from empp where name = '小昭');
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       1 |
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
|  3 | 杨逍   |   33 | 销售总监 |   8400 | 2000-11-03 |         2 |       4 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |
|  5 | 常遇春 |   43 | 财务     |  10500 | 2004-09-07 |         3 |       4 |
+----+--------+------+----------+--------+------------+-----------+---------+
5 rows in set (0.00 sec)

• 查询比平均工资高的员工信息

mysql> select * from empp where salary > (select avg(salary) from empp);
+----+--------+------+----------+--------+------------+-----------+---------+
| id | name   | age  | job      | salary | induction  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       1 |
|  2 | 张无忌 |   20 | 项目经理 |  12500 | 2005-12-05 |         1 |       5 |
|  4 | 韦一笑 |    4 | 开发     |  11000 | 2002-02-05 |         2 |       5 |
+----+--------+------+----------+--------+------------+-----------+---------+
3 rows in set (0.00 sec)

• 查询低于本部门平均工资的员工信息

mysql> select avg(e1.salary) from empp e1 where e1.dept_id = 1;
+----------------+
| avg(e1.salary) |
+----------------+
|     20000.0000 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from empp e2 where e2.salary < (select avg(e1.salary) from empp e1 where e1.dept_id=e2.dept_id);
+----+------+------+----------+--------+------------+-----------+---------+
| id | name | age  | job      | salary | induction  | managerid | dept_id |
+----+------+------+----------+--------+------------+-----------+---------+
|  3 | 杨逍 |   33 | 销售总监 |   8400 | 2000-11-03 |         2 |       4 |
|  6 | 小昭 |   19 | 程序员   |   6600 | 2004-10-12 |         2 |       5 |
+----+------+------+----------+--------+------------+-----------+---------+
2 rows in set (0.00 sec)

• 查询所有部门信息并统计部门的员工人数

mysql> select d.id ,d.name , (select count(*) from empp e where e.dept_id = d.id) from dept d;
+------+--------+------------------------------------------------------+
| id   | name   | (select count(*) from empp e where e.dept_id = d.id) |
+------+--------+------------------------------------------------------+
|    1 | 总经办 |                                                    1 |
|    2 | 市场部 |                                                    0 |
|    3 | 财务部 |                                                    1 |
|    4 | 销售部 |                                                    2 |
|    5 | 研发部 |                                                    3 |
+------+--------+------------------------------------------------------+
5 rows in set (0.00 sec)

• 查询所有学生的选课情况，并展示出学生名称，学号，课程名称

mysql> select s.name,s.no,c.name from student s,student_course sc,course c where s.id = sc.studentid and sc.courseid = c.id;

事务

事务简介

• 事务一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
• 如银行转账， 查询A账户余额，A账户转入B账户，A减少1000，B账户则增加1000
  
• 默认MYSQL的事务是自动提交的，也就是说当执行一条DML语句，mysql会立即隐式的提交事务。

事务操作

新建环境

create table account(
id int auto_increment primary key  comment '主键',
name varchar(10) comment '姓名',
money int comment '余额'
);
insert into account(id,name,money) values(null,'张三',2000),(null,'李四',2000);

银行转账演示

• 正常情况

mysql> select * from account where name ='张三';
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
+----+------+-------+
1 row in set (0.00 sec)

mysql>
mysql> update account  set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> update account  set money = money + 1000 where name = '李四';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

• 转账错误演示

mysql> select * from account where name ='张三';
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> update account  set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> 转账错误演示....
    ->
    -> update account  set money = money + 1000 where name = '李四';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '转账错误演示....
update account  set money = money + 1000 where name = '\00E6' at line 1

• 每一条SQL都是一个事务，sql执行完成，事务会自动提交。

控制事务

查看/设置事务提交方式一

• select @@autocommit 查询事务提交方式
• set @@@ autocommit = 0或1 0为手动提交，1为自动提交

提交事务

• commit;

回滚事务

• rollback;

示例

• 查询当前数据库事务提交方式

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

• 修改事务提交方式为手动

mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

• 执行sql，语句执行完毕，但发现数据库中的表未更新数据

mysql> select * from account where name ='张三';
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> update account  set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account  set money = money + 1000 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account  set money = money + 1000 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

• 提交事务

mysql> commit;

• 程序异常情况

mysql> select * from account where name ='张三';
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
+----+------+-------+
1 row in set (0.02 sec)
mysql> update account  set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> 程序出错....
update account  set money = money + 1000 where name = '李四';
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '程序出错....
update account  set money = money + 1000 where name = '李å›' at line 1

• 回滚事务

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

查看/设置事务提交方式二

开启事务

• start transaction 或 begin;

提交事务

commit;

回滚事务

rollback;

示例二

mysql> set @@autocommit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.02 sec)

• 程序异常情况，开启事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where name ='张三';
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
+----+------+-------+
1 row in set (0.03 sec)
mysql> update account  set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> 程序出错....
update account  set money = money + 1000 where name = '李四';
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '程序出错....
update account  set money = money + 1000 where name = '李å›' at line 1

• 回滚事务

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

事务四大特性

• 原子性(Atomicity)：事务是不可分割的最小操作单元，要门全部成功，要门全部失败。
• 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性(Isolation)：数据库系统提供的隔离机制，保证食物在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
• 注意 mysql的数据库文件存放位置默认是隐藏的，需要手动取消隐藏
  

并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行,但是在插入数据时，又发现这行数据已经存在，好像出现了‘幻影’

脏读

• 事务A正在更新数据ID-1,而事务B正在读取事务A，但事务A未执行完毕

不可重复读

• 事务A查询数据，事务A有两条查询，但是第一条和第三条查询的数据不一致，因为期间事务B对数据库进行的数据提交变更

幻读

事务隔离级别

事务隔离级别介绍

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

• 从上到下，隔离级别越来越高
• 事务隔离级别越高，数据越安全，但是性能越低，一般会选用数据库的默认级别。

事务隔离级别查看

• 查询事务隔离级别

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.02 sec)

• 设置事务隔离级别
• set [session| global] transaction isolation level {read uncommited|read committed|repeatable read|seralizable}
• session 只对当前会话窗口有效
• global 针对所有客户端窗口有效

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.03 sec)

示例

• 开启两个CMD会话窗口（上面会话一，下面会话二），其中窗口一设置会话隔离级别 read uncommitted

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

• 脏读复现
  
• 窗口一更改会话隔离级别 read committed，解决脏读问题

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

• 不可重复读复现
  
• 会话一修改隔离级别 repeatbale read,解决了不可重复读问题
  
• 只有当会话一的事务提交后，再次查询，方能查询到正确数据
  
• 幻读复现
  
• 会话一也提交事务后再次查询方能 查询到正常数据
• 修改隔离级别serializable（串行化）,解决幻读