Java全栈（三）数据库技术：2.数据库之Mysql下

第七章 关联查询（联合查询,多表联查）

7.0 笛卡尔积运算

表和表之间是如何关联的？——》通过笛卡儿积运算（将两张表里任意两条记录组合在一起形成新
的记录，最终生成一张大的表的过程）

而mysql中的关联查询就是将多张表笛卡尔积运算后的结果中筛选出需要的记录。

7.1 关联查询的七种结果

（0）原始数据

（1）A∩B

（2）A

（3）A - A∩B

（4）B

（5）B - A∩B

（6）A ∪ B

（7）A∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）

7.2 如何实现？

（1）内连接

（2）外连接：左外连接、右外连接、全外连接（mysql使用union代替全外连接）

1、内连接：实现A∩B

只返回满足关联条件的记录

# sql99标准，关联条件用on来设置，筛选条件用where来设置
select 字段列表
from A表 [A表别名] inner join B表 [B表别名]
on 关联条件
where 等其他子句;

或

# sql92标准，关联条件和筛选条件都用where来设置
select 字段列表
from A表 [A表别名] , B表 [B表别名]
where 关联条件 and 等其他子句;

代码示例：

#查询员工的姓名和他所在的部门的名称
#员工的姓名在employee表中
#部门的名称在department表中

select e.ename as "员工姓名",d.dname as "部门姓名" from employee e INNER JOIN department d on e.did=d.did; # 方式1
select e.ename as "员工姓名",d.dname as "部门姓名" from employee e,department d where e.did=d.did; # 方式2
# 结果：
+--------------+--------------+
| 员工姓名     | 部门姓名     |
+--------------+--------------+
| 李白         | 外交部       |
| 杜甫         | 行政部       |
| 王安石       | 安全部       |
+--------------+--------------+




#查询薪资高于20000的男员工的姓名和他所在的部门的名称
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE salary>20000 AND gender = '男'

2、左外连接：实现A和（A - A∩B）

指的是除了返回满足关联条件的结果集以外，还会把左边的那张表完整的展示出来，
右边的那张表不满足关联条件的字段位置补空值（null)

#实现查询结果是A
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句;

#实现A -  A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

代码示例：

#查询所有员工的姓名和他所在的部门的名称
select e.ename as "员工姓名",d.dname as "部门姓名" 
from employee e left join department d 
on e.did=d.did;
+--------------+--------------+
| 员工姓名     | 部门姓名     |
+--------------+--------------+
| 李白         | 外交部       |
| 杜甫         | 行政部       |
| 王安石       | 安全部       |
| 蔡徐坤       | NULL         |
+--------------+--------------+



#查询所有没有部门的员工
select e.ename as "员工姓名",d.dname as "部门姓名" 
from employee e left join department d 
on e.did=d.did 
where e.did is null;
+--------------+--------------+
| 员工姓名     | 部门姓名     |
+--------------+--------------+
| 蔡徐坤       | NULL         |
+--------------+--------------+

3、右外连接：实现B和（B - A∩B）

指的是除了返回满足关联条件的结果集以外，还会把右边的那张表完整的展示出来，
左边的那张表不满足关联条件的字段位置补空值（null)

#实现查询结果是B
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

#实现B -  A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

代码示例：

#查询所有部门的名称，以及所有部门下的员工姓名
select e.ename as "员工姓名",d.dname as "部门姓名" 
from employee e right join department d 
on e.did=d.did;
+--------------+--------------+
| 员工姓名     | 部门姓名     |
+--------------+--------------+
| 李白         | 外交部       |
| 杜甫         | 行政部       |
| 王安石       | 安全部       |
| NULL         | 公安部       |
+--------------+--------------+

#查询那些没有员工属于它的部门名称
select e.ename as "员工姓名",d.dname as "部门姓名" 
from employee e right join department d 
on e.did=d.did 
where e.did is null;
+--------------+--------------+
| 员工姓名     | 部门姓名     |
+--------------+--------------+
| NULL         | 公安部       |
+--------------+--------------+

4、全外连接：实现（A∪B）和（A∪B - A∩B）

• 指的是除了返回满足关联条件的结果集以外，还会把两边的表完整的展示出来，
  两边不满足关联条件的字段位置补空值（null）
• mysql中使用union 将两个结果集合并起来实现全外了，连接

#实现查询结果是A∪B
#用左外的A union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union 
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;



#实现A∪B -  A∩B  
#使用左外的 (A -  A∩B)  union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

代码示例：

#查询所有员工姓名，所有部门名称，包括没有员工的部门，和没有部门的员工
select e.ename as "员工姓名",d.dname as "部门姓名" 
from employee e left join department d 
on e.did=d.did 
union 
select e.ename as "员工姓名",d.dname as "部门姓名" 
from employee e right join department d 
on e.did=d.did;
+--------------+--------------+
| 员工姓名     | 部门姓名     |
+--------------+--------------+
| 李白         | 外交部       |
| 杜甫         | 行政部       |
| 王安石       | 安全部       |
| 蔡徐坤       | NULL         |
| NULL         | 公安部       |
+--------------+--------------+


#查询那些没有部门的员工姓名和所有没有员工的部门名称

#没有部门的员工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

UNION 

#所有没有员工的部门
select e.ename as "员工姓名",d.dname as "部门姓名" 
from employee e left join department d 
on e.did=d.did where e.did is null 
union 
select e.ename as "员工姓名",d.dname as "部门姓名" 
from employee e right join department d 
on e.did=d.did where e.did is null;
+--------------+--------------+
| 员工姓名     | 部门姓名     |
+--------------+--------------+
| 蔡徐坤       | NULL         |
| NULL         | 公安部       |
+--------------+--------------+

7.3 特殊的关联查询：自连接

两个关联查询的表是同一张表，通过取别名的方式来虚拟成两张表。

如上图所示：员工表中有mid字段，表示该员工领导的id编号。即mid是eid字段的外键

select 字段列表
from 表名 别名1 inner/left/right join 表名 别名2
on 别名1.关联字段 = 别名2的关联字段
where 其他条件

代码示例：
查询员工的id，姓名，以及领导的姓名。

# 创建表
create table employee2(
	eid int primary key,
	ename char(10),
	mid int,
	foreign key(mid) references employee2(eid)
);
# 插入数据
insert into employee2(eid,ename) values(4,"赵铁林");
insert into employee2 values(1,"张三",4),(2,"李四",4),(3,"王五",4);
select * from employee2;
+-----+-----------+------+
| eid | ename     | mid  |
+-----+-----------+------+
|   1 | 张三      |    4 |
|   2 | 李四      |    4 |
|   3 | 王五      |    4 |
|   4 | 赵铁林    | NULL |
+-----+-----------+------+

#查询员工的编号，姓名，以及员工领导的名字
select e.eid as "员工编号",e.ename as "员工姓名",m.ename as "领导姓名" 
from employee2 e inner join employee2 m 
on e.mid=m.eid; # 注意：这里的关联条件应该是`员工表的mid=领导表的员工id`
+--------------+--------------+--------------+
| 员工编号     | 员工姓名     | 领导姓名     |
+--------------+--------------+--------------+
|            1 | 张三         | 赵铁林       |
|            2 | 李四         | 赵铁林       |
|            3 | 王五         | 赵铁林       |
+--------------+--------------+--------------+
#表的别名不要加""，给列取别名，可以用""，列的别名不使用""也可以，但是要避免包含空格等特殊符号。

7.4 三表查询

三表查询本质跟两表查询一样，它是先将两表进行关联查询后的结果，再跟第三张表进行关联查询。

如下：学生表和教室表、教师表都关联在了一起。
需求：查找所有学生的姓名、上课的教室名、上课的老师名

select s.sname,c.cname,t.tname
from student s INNER JOIN class c
on s.cid=c.cid
INNER JOIN teacher t # 使用前面已经关联后的表与第三张teacher表进行关联
on s.tid=t.tid;
+--------+--------------+-----------+
| sname  | cname        | tname     |
+--------+--------------+-----------+
| 张三   | JAVA班       | 李老师    |
| 李四   | JAVA班       | 黄老师    |
| 王五   | 大数据班     | 赵老师    |
| 赵六   | 大前端班     | 马老师    |
| 黑七   | 大前端班     | 田老师    |
| 吴八   | JAVA班       | 田老师    |
+--------+--------------+-----------+

第八章 select语句的七大子句

8.1 七大子句书写顺序

（1）from：从哪些表中筛选

（2）on：关联多表查询时，去除笛卡尔积

（3）where：从表中筛选的条件

（4）group by：分组依据

（5）having：在统计结果中再次筛选

（6）order by：排序

（7）limit：分页

必须按照（1）-（7）的顺序【编写】子句。

连续原始数据如下：

# 查找每个职位的男生人数
select e_pid as "职位id",count(*) as "人数"
from employ3
where sex="男"
group by e_pid;
+----------+--------+
| 职位id   | 人数   |
+----------+--------+
|        1 |      4 |
|        2 |      2 |
|        3 |      2 |
|        4 |      2 |
|        5 |      2 |
|       11 |      1 |
+----------+--------+
# 查找男生人数大于等于2的职位，并按照人数降序排列
select e_pid as "职位id",count(*) as "人数"
from employ3
where sex="男"
group by e_pid
having count(*)>=2
order by count(*) desc;
+----------+--------+
| 职位id   | 人数   |
+----------+--------+
|        1 |      4 |
|        2 |      2 |
|        3 |      2 |
|        4 |      2 |
|        5 |      2 |
+----------+--------+

# 查找男生人数大于等于2的职位，并按照人数降序排列，并分页显示（每页只能显示2条，我要第2页）
select e_pid as "职位id",count(*) as "人数"
from employ3
where sex="男"
group by e_pid
having count(*)>=2
order by count(*) desc
limit 2,2; # 第一个2表示跳过前面多少条记录，第二个2表示需要显示多少条记录
+----------+--------+
| 职位id   | 人数   |
+----------+--------+
|        3 |      2 |
|        4 |      2 |
+----------+--------+

8.2 group by与分组函数

• group by是将所有的数据，按照一定条件进行分组，组内再进行处理。

• group by需要放到where的后面

• group by后面的字段最好要是select后面声明过的，不然没有意义

  select e_pid as "职位id",count(*) as "人数"
  from employ3
  where sex="男"
  group by e_pid;
  

• 在有group by的前提下，select后的分组函数（AVG(),SUM(),MAX(),MIN(),COUNT()）是对group by分组后的每个组内的数据进行处理，每个组返回一个数据

  select e_pid as "职位id",count(*) as "人数",avg(salary) as "平均工资"
  from employ3
  where sex="男"
  group by e_pid;
  +----------+--------+--------------+
  | 职位id   | 人数   | 平均工资     |
  +----------+--------+--------------+
  |        1 |      4 | 20516.227500 |
  |        2 |      2 | 20250.290000 |
  |        3 |      2 | 15282.340000 |
  |        4 |      2 | 15348.105000 |
  |        5 |      2 | 13932.080000 |
  |       11 |      1 |  4000.330000 |
  +----------+--------+--------------+
  

• 包含在 GROUP BY 子句中的列也可以不必包含在SELECT 列表中

  SELECT   AVG(salary)
  FROM     employees
  GROUP BY department_id ;
  

• 可以使用多个字段来进行分组

  SELECT   department_id dept_id, job_id, SUM(salary)
  FROM     employees
  GROUP BY department_id, job_id ;
  

8.3 having与分组函数

• having是对group by分组后的结果再次进行筛选，需要放在group by之后
• having后面可以直接使用select后面的字段或者分组函数结果来进行筛选，也可以直接使用分组函数

  select e_pid as "职位id",count(*) as "人数"
  from employ3
  where sex="男"
  group by e_pid
  having count(*)>=2 and e_pid%2=0;
  +----------+--------+
  | 职位id   | 人数   |
  +----------+--------+
  |        2 |      2 |
  |        4 |      2 |
  +----------+--------+
  

• 获取最小工资小于2000的职位

    -- min(sal)  job   min(sal)<2000    
    -- 获取各个职位的最小工资    
    select job,min(sal)    
    from emp    
    group by job    
    order by min(sal)    
    
    -- 获取各个职位的最小工资,筛选出小于2000的    
    select job,min(sal)    
    from emp    
    group by job    
    having min(sal)<2000    
    order by min(sal)    
    
  -- 统计[人数小于4的]部门的平均工资。    
     select deptno,count(1),avg(sal)    
     from emp    
     group by deptno    
     having count(1)<4    
  -- 统计各部门的最高工资，排除最高工资小于3000的部门。    
     select deptno,max(sal)    
     from  emp    
     group by deptno    
     having max(sal) >=3000 
  

having与where的区别？

（1）where是从表中筛选的条件，而having是分组（统计）结果中再次筛选

（2）where后面不能加“分组/聚合函数”，而having后面可以跟分组函数

#统计部门平均工资高于8000的部门和平均工资
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000 #错误
GROUP BY department_id;

#统计每一个部门，薪资高于10000元的女员工的数量，显示人数超过1人
SELECT did,COUNT(*)
FROM t_employee
WHERE gender ='女' AND salary>10000
GROUP BY did
HAVING COUNT(*) > 1;

8.4 order by

• order by：后面跟上字段或者分组函数，表示按照相关内容进行排序

• 降序：desc

• 升序：要么默认，要么加asc

# 获取男生人数大于2的职位，并按照人数降序排列
select e_pid as "职位id",count(*) as "人数"
from employ3
where sex="男"
group by e_pid
having count(*)>=2
order by count(*) desc
+----------+--------+
| 职位id   | 人数   |
+----------+--------+
|        1 |      4 |
|        2 |      2 |
|        3 |      2 |
|        4 |      2 |
|        5 |      2 |
+----------+--------+

8.5 limit

limit：分页显示

limit m,n

m = 跳过的记录数。一般为（需要显示第几页 - 1）*每页的数量

n = 每页的数量

代码示例：

/*
每页显示2条 展示第三页
*/
select * from emp limit 4,2;

求员工的姓名，薪水，部门编号，部门名称，工作编号，工作名称，按照薪水排序，每页显示3条显示第4页

SELECT
	emp.`ename`,
	emp.`salary`,
	emp.`department_id`,
	dept.`dname`,
	dept.`did`,
	job.`job_name` 
FROM
	t_employee emp
	JOIN t_department dept ON emp.`department_id` = dept.`did`
	JOIN t_job job ON emp.`job_id` = job.`job_id` 
ORDER BY
	emp.`salary` 
	LIMIT 9,
	3;

第九章 子查询

嵌套在另一个查询中的查询，根据位置不同，分为：where型，from型，exists型。注意：不管子查询在哪里，子查询必须使用()括起来。

1、where型

①子查询是单值结果，那么可以对其使用（=，>等比较运算符）

②子查询是多值结果，那么可对其使用（【not】in(子查询结果)，或 >all(子查询结果)，或>=all(子查询结果)，<all(子查询结果)，<=all（子查询结果)，或 >any(子查询结果)，或>=any(子查询结果)，<any(子查询结果)，<=any（子查询结果)）

查询全公司最高工资的员工信息
select * from 员工表 where 薪资 = (select max(薪资) from 员工表);

select * from 员工表 where 薪资 > all(select salary from 员工表  where 员工编号 in(...));

2、from型

子查询的结果是多行多列的结果，类似于一张表格。

必须给子查询取别名，即临时表名，表的别名不要加“”和空格。

查询每个部门的编号，名称，平均工资
select 部门编号, 部门名称， 平均工资
from 部门表 inner join (select 部门编号，avg(薪资) from 员工表  group by 部门编号) temp
on 部门表.部门编号 = temp.部门编号

# 查询职位id，职位名称，职位对应的平均工资
select p.id,p.pname,tmp.a_salary
from position3 p inner join (select e_pid,avg(salary) as a_salary from employ3 group by e_pid) tmp
on  p.id=tmp.e_pid;
+----+-----------------------+--------------+
| id | pname                 | a_salary     |
+----+-----------------------+--------------+
|  1 | JAVA开发工程师        | 20516.227500 |
|  2 | C++开发工程师         | 20250.290000 |
|  3 | PYTHON开发工程师      | 15282.340000 |
|  4 | 测试工程师            | 13644.253333 |
|  5 | 运维工程师            | 13932.080000 |
|  6 | HR专员                |  8752.660000 |
|  7 | 人事协调专员          | 10256.300000 |
|  8 | 财务专员              |  7855.440000 |
|  9 | 总经办助手            | 15466.880000 |
| 10 | 清洁人员              |  3400.220000 |
| 11 | 食堂人员              |  4000.330000 |
+----+-----------------------+--------------+

3、exists型

查询那些有员工的部门
select 部门编号, 部门名称 from 部门表
where exists (select * from 员工表  where 部门表.部门编号 = 员工表.部门编号);

# 查询有员工的部门，即部门表中的`id`要等于员工表中的某一条记录的`e_pid`
select p.id,p.pname from position3 p
where exists(select * from employ3 e where p.id=e.e_pid);
+----+-----------------------+--------------+
| id | pname                 | a_salary     |
+----+-----------------------+--------------+
|  1 | JAVA开发工程师        | 20516.227500 |
|  2 | C++开发工程师         | 20250.290000 |
|  3 | PYTHON开发工程师      | 15282.340000 |
|  4 | 测试工程师            | 13644.253333 |
|  5 | 运维工程师            | 13932.080000 |
|  6 | HR专员                |  8752.660000 |
|  7 | 人事协调专员          | 10256.300000 |
|  8 | 财务专员              |  7855.440000 |
|  9 | 总经办助手            | 15466.880000 |
| 10 | 清洁人员              |  3400.220000 |
| 11 | 食堂人员              |  4000.330000 |
+----+-----------------------+--------------+

第十章 事务

10.1 事务概述

1、事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

​ 例如转账操作：A账户要转账给B账户，那么A账户上减少的钱数和B账户上增加的钱数必须一致，也就是说A账户的转出操作和B账户的转入操作要么全部执行，要么全不执行；如果其中一个操作出现异常而没有执行的话，就会导致账户A和账户B的转入转出金额不一致的情况，为而事实上这种情况是不允许发生的，所以为了防止这种情况的发生，需要使用事务处理。

2、事务的ACID属性：

（1）原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

（2）一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

（3）隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

（4）持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

10.2 开启和结束事务

1、mysql默认是自动提交，执行一句就提交一句。

即默认情况下，每一条sql都是一个独立的sql。

2、我想要手动提交事务：

（1）方式一：set autocommit=false; # 关闭自动提交

接下来所有语句都必须手动提交，否则就不会永久生效。即需要手动commit（提交）或者rollback（回滚）

# 准备数据
create table acount(
	id int primary key,
	aname varchar(10),
	money double(20,2)
);
insert into acount values(1,"张三",2000),(2,"李四",2000);

# 事务
set autocommit=false; # 关闭自动提交，开启事务。
update acount set money=money+500 where id=1;
update acount set money=money-500 where id=2;
commit;
select * from acount; # 数据更新成功
+----+--------+---------+
| id | aname  | money   |
+----+--------+---------+
|  1 | 张三   | 2500.00 |
|  2 | 李四   | 1500.00 |
+----+--------+---------+

update acount set money=money+500 where id=1;
update acount set money=money-500 where id=2;
rollback;
select * from acount; # 数据未更新
+----+--------+---------+
| id | aname  | money   |
+----+--------+---------+
|  1 | 张三   | 2500.00 |
|  2 | 李四   | 1500.00 |
+----+--------+---------+

（2）方式二：start transaction; # 开启单次事务

commit; 或 rollback; 此次事务结束 想要事务保证数据安全需要再次进行开启

create table acount(
	id int primary key,
	aname varchar(10),
	money double(20,2)
);
insert into acount values(1,"张三",2000),(2,"李四",2000);

# 事务
set autocommit=true; # 开启自动提交，mysql默认是开启的。
start transaction; # 开启一次事务管理
update acount set money=money+500 where id=1;
update acount set money=money-500 where id=2;
commit; // commit后本次事务就结束了
select * from acount; # 数据更新了
+----+--------+---------+
| id | aname  | money   |
+----+--------+---------+
|  1 | 张三   | 2500.00 |
|  2 | 李四   | 1500.00 |
+----+--------+---------+

start transaction; # 开启又一次事务管理
update acount set money=money+500 where id=1;
update acount set money=money-500 where id=2;
rollback; # rollback后本次事务结束
select * from acount; # 数据没有更新
+----+--------+---------+
| id | aname  | money   |
+----+--------+---------+
|  1 | 张三   | 2500.00 |
|  2 | 李四   | 1500.00 |
+----+--------+---------+

3、注意：

事务的回滚只对DML语句有效，对于DDL语句无效。即如果修改了表结构，那么回滚后表结构仍然是改变的

10.3 事务隔离级别

1、事务并发问题

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

• 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。 之后, 若 T2 回滚, T1读取的内容就是临时且无效的。

• 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新/修改了该字段。 之后, T1再次读取同一个字段, 值就不同了。

• 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入/删除了一些新的行。 之后, 如果 T1 再次读取同一个表, 就会多/少几行。

不可重复度和幻读区别：

不可重复读的重点是对数据的修改，幻读的重点在于新增或者删除。

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读

2、事务隔离级别

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。**一个事务与其他事务隔离的程度称为隔离级别。**数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

数据库提供的 4 种事务隔离级别：

隔离级别	描述
read-uncommitted	允许A事务读取其他事务未提交和已提交的数据。会出现***脏读、不可重复读、幻读***问题
read-committed	只允许A事务读取其他事务已提交的数据。可以避免脏读，但仍然会出现***不可重复读、幻读***问题
repeatable-read	确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读。但是幻读问题仍然存在。（最新的mysql对幻读问题也解决了）
serializable	确保事务可以从一个表中读取相同的行，相同的记录。在这个事务持续期间，禁止其他事务对该表执行插入、更新、删除操作。所有并发问题都可以避免，但性能十分低下。

• Oracle 支持的 2 种事务隔离级别：READ-COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
• Mysql 支持 4 种事务隔离级别。 Mysql 默认的事务隔离级别为: REPEATABLE-READ。在mysql中REPEATABLE READ的隔离级别也可以避免幻读了。

3、设置和查看隔离级别

每启动一个 mysql 程序, 就会获得一个单独的数据库连接。每个数据库连接都有一个变量 @@tx_isolation, 表示当前连接的事务隔离级别。mysql服务也有一个全局变量@@global.tx_isolation，表示所有连接的默认事务隔离级别。

• 查看当前mysql连接的隔离级别:

select @@tx_isolation;

• 查看全局的隔离级别：

select @@global.tx_isolation;

• 设置当前 mysql连接的隔离级别:

set tx_isolation ='repeatable-read';

• 设置数据库系统的全局的隔离级别:

set global tx_isolation ='read-committed';

注意：这里的隔离级别中间是减号，不是下划线。

第十一章 用户与权限

11.1 身份认证

（1）IP+用户名作为身份验证

​ 例如：

​ root@localhost：只允许在本机使用root用户登录

​ root@%：运行在任意机器上使用root用户登录

​ root@192.168.11.56：只运行在192.168.11.56主机上使用root用户登录

​ gjz@%：运行在任意机器上使用gjz用户登录

建立用户时，用于身份验证的IP地址，是连接你的服务的客户端的IP地址

例如：root@192.168.11.56，表示客户端在192.168.11.56的机器上可以访问你的mysql服务

而这个客户端，要连接你的服务时，-h后面的主机的IP地址是，服务器所在的机器的IP地址。

例如：mysql服务在192.168.11.11机器上，客户端在192.168.11.56上，那么客户端可以用root@192.168.11.56或root@%，连接时填写mysql -h192.168.11.11 -u root -p密码

（2）密码

11.2 分配权限

1、用户权限有4个权限级别

（1）全局

（2）数据库

（3）表

（4）字段

依次校验权限，如果前面通过了，后面就不校验了：

全局 > 数据库 > 表 > 字段

注意：root@localhost，这个用户始终保留所有的全局权限。

2、查看账户权限

show grants for '用户名'@'主机IP地址';

3、新建用户和删除用户

对应的语句：

CREATE USER '用户名'@'主机IP地址' IDENTIFIED BY '123456';

如果主机IP地址写：（1）192.168.29.53，就仅限于在该IP登录

​ （2）%，就表示可以从任意IP登录

drop user '用户名'@'主机IP地址';

4、授予和收回权限

对应语句：

GRANT 权限列表 ON *.* TO '用户名'@'主机IP地址';  #全局
GRANT 权限列表 ON 数据库名.* TO '用户名'@'主机IP地址'; #某个库
GRANT 权限列表 ON 数据库名.表格 TO '用户名'@'主机IP地址'; #某个库的某个表
GRANT 权限列表 ON 表名.* TO '用户名'@'主机IP地址';  #某个表的字段

例如：

GRANT SELECT ON *.* TO 'gao'@'192.168.29.53';
GRANT SELECT ON `test`.* TO 'gao'@'192.168.29.53';
GRANT UPDATE ON `test`.`t_department` TO 'gao'@'192.168.29.53';
GRANT UPDATE ON `t_department`.* TO 'gao'@'192.168.29.53';
GRANT SELECT (tid), INSERT (tid), UPDATE (tid), REFERENCES (tid) ON `1101db`.`course` TO 'gao'@'192.168.29.30';

收回权限：

revoke 权限列表 ON *.* from '用户名'@'主机IP地址';
revoke 权限列表 ON 数据库名.* from '用户名'@'主机IP地址';
revoke 权限列表 ON 数据库名.表格 from '用户名'@'主机IP地址';
revoke 权限列表 ON 表名.* from '用户名'@'主机IP地址';

第十二章 数据库设计的三大范式（附加）

1) 范式

• 必须保证数据库设计的合理性

​ 数据库设计关系整个系统的架构，关系到后续开发效率和运行效率

​ 数据库的设计主要包含了设计表结构和表之间的联系

• 如何是合理数据库

​ 结构合理

​ 冗余较小

​ 尽量避免插入删除修改异常

• 如何才能保证数据库设计水平

​ 遵循一定的规则

​ 在关系型数据库中这种规则就称为***范式***

• 什么是范式（NF= NormalForm）

​ 范式是符合某一种设计要求的总结。

​ 要想设计一个结构合理的关系型数据库，必须满足一定的范式。

2) 范式分类

• 第一范式

• 第二范式

• 第三范式

​ • 各个范式是依次嵌套包含的

​ • 范式越高，设计质量越高，在现实设计中也越难实现

​ • 一般数据库设计，只要达到第三范式，即可避免异常的出现

3) 第一范式

• 要求

• 最基本的范式
• 数据库表每一列都是不可分割基本数据项，同一列中不能有多个值
• 简单说就是要确保每列保持原子性
• 第一范式的合理遵循需要根据系统的实际需求来定

• 示例

• 用户表（用户名，家庭地址）
• 用户表（用户名，省，城市，详细地址）
• 系（系名称，系主任，系高级职称人数）
• 系（系名称，系主任，系教授人数，系副教授人数）

4) 第二范式

• 要求

• 第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
• 即在一个数据库表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

• 示例

• 学号和课程编号作为联合主键
• 课程名称只依赖于课程编号，而和学号没有关系

• 解决

• 提取出学生表
• 提取成课程表
• 提取选课表，存放选课记录

​ 学生表 选课表 课程表

5) 第三范式

• 要求

• 确保数据表中的每一列数据都和主键直接相关，而不能间接相关
• 属性不依赖于其他非主属性。

• 示例1：学生班级表

学号(主键)	学生姓名	班级编号	班级名称	班级信息
023145	张三	987654	3班	特招班
023146	李四	987654	3班	特招班
023147	王五	987655	4班	普通班
023258	赵六	987654	3班	特招班

完善之后的方案：

学号(主键)	学生姓名	班级编号
023145	张三	987654
023146	李四	987654
023147	王五	987655
023258	赵六	987654

班级编号(主键)	班级名称	班级信息
987654	3班	特招班
987655	4班	普通班

• 示例2:订单明细表

编号(主键)	图书id	图书名称	价格	作者	出版社	出版日期	数量
023145	1	精通Java	60.00	张三	清华出版社	2007	1
023146	2	Oracle	65.00	李四	机械出版社	2009	1
023147	3	JSP	87	王五	电子出版社	2014	3
023258	1	精通Java	60.00	张三	清华出版社	2007	2
023259	2	Oracle	65.00	李四	机械出版社	2009	3

完善之后的方案：分割成图书表和订单表两种表

图书id	图书名称	价格	作者	出版社	出版日期
1	精通Java	60.00	张三	清华出版社	2007
2	Oracle	65.00	李四	机械出版社	2009
3	JSP	87	王五	电子出版社	2014
4	Struts2	56	赵六	清华出版社	2005

编号(主键)	图书id	数量
023145	1	1
023146	2	1
023147	3	3
023258	2	2
023259	2	3

6) 范式的优缺点

• 优点

• 结构合理
• 冗余较小
• 尽量避免插入删除修改异常

• 缺点

• 性能降低
• 多表查询比单表查询速度慢
• 数据库的设计应该根据当前情况和需求做出灵活的处理。
• 在实际设计中，要整体遵循范式理论。
• 如果在某些特定的情况下还死死遵循范式也是不可取的，因为可能降低数据库的效率，此时可以***适当增加冗余而提高性能。***

• 示例：

​ 比如经常购物车条目的中除了条目编号，商品编号，商品数量外，可以增加经常使用的商品名称，商品价格等

图书表

图书id	图书名称	价格	作者	出版社	出版日期
1	精通Java	60	张三	清华出版社	2007
2	Oracle宝典	65	李四	机械出版社	2009
3	JSP	87	王五	电子出版社	2014
4	Struts2	56	赵六	清华出版社	2005

订单表中增加冗余列图书名称、价格，以空间换时间。

编号(主键)	图书id	图书名称	价格	数量
023145	1	精通Java	60	1
023146	2	Oracle宝典	65	1
023147	3	JSP	87	3
023258	1	精通Java	60	2

7) 总结

• 范式是指导数据设计的规范化理论，可以保证数据库设计质量

• 第一范式：字段不能再分

• 第二范式：不存在局部依赖

• 第三范式：不含传递依赖（间接依赖）

• 使用范式可以减少冗余，但是会降低性能

• 特定表的的设计可以违反第三范式，增加冗余提高性能

第十三章 经典问题(出现问题时与之对应)

1、编码问题

命令行操作sql乱码问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

修改my.ini配置文件

在路径：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini 找到my.ini文件

修改内容1：
	找到[mysql]命令，大概在63行左右，在其下一行添加 
		default-character-set=utf8
修改内容2:
	找到[mysqld]命令，大概在76行左右，在其下一行添加
		character-set-server=utf8
		collation-server=utf8_general_ci

修改完毕后，重启MySQL57服务

查看编码命令

show variables like 'character_%';
show variables like 'collation_%';

• 如果是以上配置就说明对了

命令行操作sql仍然乱码问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

原因：按照刚才的配置，服务器端认为你的客户端的字符集是utf-8，而实际上你的客户端的字符集是GBK。

查看所有字符集：SHOW VARIABLES LIKE ‘character_set_%’;

解决方案，设置当前连接的客户端字符集 “SET NAMES GBK;”

set names gbk;是为了告诉服务器,客户端用的GBK编码,防止乱码。

有的时候，这样还不能解决，例如，某些win10操作系统环境下，那么修改命令行的属性-》选项-》勾选使用旧版控制台，然后重启电脑

2、忘记root用户密码问题

1:通过任务管理器或者服务管理,关掉mysqld(服务进程)

2:通过命令行+特殊参数开启mysqld

mysql5.5

mysqld --skip-grant-tables

mysql5.7版

mysqld --defaults-file="D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini" --skip-grant-tables

此处路径以你自己的世界路径为准

3:此时,mysqld服务进程已经打开,并且,不需要权限检查.

4:mysql -uroot 无密码登陆服务器.另启动一个客户端进行

5: 修改权限表

（1） use mysql;

（2）

mysql5.5版

update user set Password = password('123456') where User = 'root';

mysql5.7版

update user set authentication_string=password('新密码') where user='root' and Host='localhost'; 

​

（3）flush privileges;

6:通过任务管理器,关掉mysqld服务进程.

7:再次通过服务管理,打mysql服务。

8:即可用修改后的新密码登陆.

3、查看字符集和校对规则

关于SQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。

_ci（大小写不敏感），_cs（大小写敏感），_bin（二元，即比较是基于字符编码的值而与language无关）

（1）查看所有字符集和校对规则

（2）查看GBK和UTF-8字符集的校对规则

show collation like 'gbk%';

show collation like 'utf8%';

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci 校对速度快，但准确度稍差。
utf8_unicode_ci 准确度高，但校对速度稍慢。

如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了。

（3）查看服务器的字符集和校对规则

（4）查看和修改某个数据库的字符集和校对规则

或

修改数据库的字符集和校对规则：

ALTER DATABASE 数据库名称 DEFAULT CHARACTER SET 字符集名称 【COLLATE 校对规则名称】;

例如：

ALTER DATABASE ceshi_db DEFAULT CHARACTER SET utf8 collate utf8_general_ci;

注意：修改了数据库的默认字符集和校对规则后，原来已经创建的表格的字符集和校对规则并不会改变，如果需要，那么需要单独修改。

（5）查看某个表格的字符集和校对规则

查看字符集：show create table users;

如果要查看校对规则：show table status from bookstore like '%users%' ;

修改某个表格的字符集和校对规则：

修改表的默认字符集：

ALTER TABLE 表名称 DEFAULT CHARACTER SET 字符集名称 【COLLATE 校对规则名称】;

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

ALTER TABLE 表名称 CONVERT TO CHARACTER SET 字符集名称 【COLLATE 校对规则名称】;

例如：ALTER TABLE ceshi_table DEFAULT CHARACTER SET gbk collate gbk_chinese_ci;