MySQL数据库-多表查询和事务

 # 创建部门表
 CREATE TABLE dept(
     id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
     NAME VARCHAR(20) -- 部门名称
 );
 INSERT INTO dept (NAME) VALUES ('开发部'),('销售部'),('市场部');
 
 # 创建员工表
 CREATE TABLE emp (
     id INT PRIMARY KEY AUTO_INCREMENT, -- 员工工号
     NAME VARCHAR(10), -- 员工姓名
     gender CHAR(1), -- 员工性别
     salary DOUBLE, -- 员工工资
     join_date DATE, -- 入职日期
     dept_id INT, -- 部门编号
     FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
 );
 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白展堂','男',6000,'2013-12-14',2);
 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('吕秀才','男',5500,'2015-12-12',1);
 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('燕小六','男',3000,'2011-03-16',3);
 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('佟湘玉','女',9000,'2010-02-17',2);
 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('郭芙蓉','女',4500,'2019-08-22',1);

多表查询

笛卡尔积现象

两个集合中的所有对象进行了所有可能性的组合！

多表联查的查询分类：

内连接查询

用左表的记录去匹配右边表的记录，如果符合条件则结果显示。

（1）隐式内连接

 语法：select 字段名列表 FROM 左表,右表 WHERE 条件;

SELECT * FROM emp,dept WHERE emp.NAME = '白展堂' AND emp.dept_id = dept.id;

（2）显示内连接

 语法：select 字段名列表 FROM 左表 [INNER]JOIN 右表 ON 条件;

SELECT * FROM emp inner join dept on emp.NAME = '白展堂' AND emp.dept_id = dept.id;

     # 总结内连接的查询步骤：
     （1）确定查询哪些表；
     （2）确定表连接的条件；
     （3）确定查询的条件；
     （4）确定查询哪些字段；

     # 依据查询步骤书写标准格式：
     SELECT 
         e.id, -- 员工表中的id
         e.NAME, -- 员工表中的姓名
         e.salary, -- 员工表中的工资
         d.NAME 
     FROM 
         emp e inner join dept d
     ON  
         e.dept_id = d.id 
     WHERE 
         e.NAME = '白展堂';

外连接查询

在内连接的基础之上，保证了左表或者右表的数据全部显示, 否则，显示的是null。

（1）左外连接

查询的是左表所有数据以及其交集部分。

语法：

SELECT 字段列表 FROM 左表 LEFT [OUTER] JOIN  右表 ON 链接条件 WHERE 查询条件；

         # 向部门表中插入一条数据
         INSERT INTO dept (name) VALUES('人事部');
         
         -- 使用内连接查询多表
         SELECT * FROM dept d INNER JOIN emp e ON d.id = e.dept_id;
         
         -- 使用左外连接查询多表
         SELECT * FROM dept d LEFT outer JOIN emp e ON d.id=e.dept_id;

（2）右外连接

查询的是右表所有数据以及其交集部分。

语法：

SELECT 字段列表 FROM 左表 RIGHT [OUTER] JOIN  右表 ON 链接条件 WHERE 查询条件;

     # 向员工表中插入一条数据
     INSERT INTO emp values (NULL,'邢捕头','男',4500,'2012-10-10',NULL); 
     
     -- 使用内连接查询
     SELECT * FROM dept d INNER JOIN emp e ON d.id=e.dept_id ;
     
     -- 使用右连接查询
     SELECT * FROM dept d RIGHT OUTER JOIN emp e ON d.id=e.dept_id;

子查询

子查询特点：（1）一个查询的结果作为了另一个查询的条件（2）有查询的嵌套，内部的查询被称为子查询（3）子查询要使用括号

 -- 查询'开发部'的所有员工的信息
 -- 1.先查询出‘开发部’的id
 SELECT id FROM dept WHERE NAME='开发部';
 
 -- 2.再依据查询出的id，在emp表中查询该id所对应员工的信息
 SELECT * FROM emp WHERE dept_id=1;
 
 -- 使用子查询方式
 SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE NAME='开发部');

子查询分类：

（1）子查询的结果是单行单列
（2）子查询的结果是多行单列
（3）子查询的结果是多行多列

（1）子查询的结果是单行单列

特点：子查询结果是单行单列时，where条件后面使用的是比较运算符：<、>、=、<=、>=、!=、<>

 # 查询最高薪资的员工是谁？
 
 -- 1）.查询出最高薪资是多少？emp
 SELECT MAX(salary) FROM emp;
 -- 2）.根据查询到的最高薪资找到员工表中对于的员工是谁
 SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
 
 # 查询薪资小于平均工资的员工？
 
 -- 1）.查询出平均薪资
 SELECT AVG(salary) FROM emp;
 -- 2）.员工表中查询薪资小于平均值的员工
 SELECT * FROM emp WHERE salary <= (SELECT AVG(salary) FROM emp);
（2）子查询的结果是多行单列  特点：子查询结果是多行单列，结果集像是一个数组，where后的条件使用 IN。

 # 查询开发部和销售部所有员工信息
 
 -- 1）.先查询开发部和销售部的部门id
 --SELECT * FROM dept WHERE NAME = '开发部' OR NAME = '销售部';
 SELECT id FROM dept WHERE NAME IN ('开发部' ,'销售部');
 
 --2）.依据部门id在员工表中查询符合条件的员工信息
 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','销售部'));
 
 # 查询工资大于5000的员工，都来自于哪个部门？
 
 -- 1）.查询工资大于5000 员工的部门id 
 SELECT dept_id FROM emp WHERE salary >5000;
 
 -- 2）.依据部门id，查询对应的部门名称
 SELECT name FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary >5000) ;
（3）子查询的结果是多行多列 特点：子查询的结果是多行多列，将该结果做为from关键字之后的表来使用。 注意的是子查询作为表是需要取别名，否则在查询的这个过程是无法访问到该表的任何子段。

 # 查询2012年以后入职员工的所有信息包括部门名称
 
 -- 1）.查询出2012-10-10年以后的入职员工
 SELECT * FROM emp WHERE join_date >= '2012-10-10';
 
 -- 2）.查询所有部门信息，是需要通过上面查询的结果拿到dept_id,再在dept查询id对应的部门名称
 SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date >= '2012-10-10') e WHERE d.id=e.dept_id;
 
 ----------------------------------------------------------------------------
  也可以使用内连接实现：
 SELECT * FROM emp e join dept d on e.dept_id = d.id WHERE join_date >= '2012-10-10';
 -- 以上SQL查询是内连接的标准格式（推荐使用）
 
 SELECT * FROM emp e, dept d WHERE e.dept_id = d.id AND join_date >= '2012-10-10';
 -- 以上SQL查询把连接条件和查询条件都作为了查询条件（不推荐，结构不清晰，sql可读性差）

总子查询结

子查询结果只要是单列，则在 WHERE 后面作为条件

(1) 单行单列，使用比较运算符：<、>、=、<=、>=、!=、<>

(2) 多行单列，使用IN 运算符
子查询结果只要是多列，则在 FROM 后面作为表进行二次查询

SQL课堂练习

SQL数据：

 子查询结果 多行多列练习

 -- 部门表
 CREATE TABLE dept (
   id INT PRIMARY KEY, -- 部门id
   dname VARCHAR(50), -- 部门名称
   loc VARCHAR(50) -- 部门所在地
 );
 
 -- 添加4个部门
 INSERT INTO dept(id,dname,loc) VALUES 
 (10,'教研部','北京'),
 (20,'学工部','上海'),
 (30,'销售部','广州'),
 (40,'财务部','深圳');
 
 -- 职务表
 CREATE TABLE job (
   id INT PRIMARY KEY, -- 职务id
   jname VARCHAR(20), -- 职务名称
   description VARCHAR(50) -- 职务描述
 );
 
 -- 添加4个职务
 INSERT INTO job (id, jname, description) VALUES
 (1, '董事长', '管理整个公司，接单'),
 (2, '经理', '管理部门员工'),
 (3, '销售员', '向客人推销产品'),
 (4, '文员', '使用办公软件');
 
 -- 员工表
 CREATE TABLE emp (
   id INT PRIMARY KEY, -- 员工id
   ename VARCHAR(50), -- 员工姓名
   job_id INT, -- 职务id
   mgr INT , -- 上级领导
   joindate DATE, -- 入职日期
   salary DECIMAL(7,2), -- 工资
   bonus DECIMAL(7,2), -- 奖金
   dept_id INT, -- 所在部门编号
   CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
   CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
 );
 
 -- 添加员工
 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
 (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
 (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
 (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
 (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
 (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
 (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
 (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
 (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
 (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
 (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
 (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
 (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
 (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
 (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
 
 -- 工资等级表
 CREATE TABLE salarygrade (
   grade INT PRIMARY KEY,   -- 级别
   losalary INT,  -- 最低工资
   hisalary INT -- 最高工资
 );
 
 -- 添加5个工资等级
 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
 (1,7000,12000),
 (2,12010,14000),
 (3,14010,20000),
 (4,20010,30000),
 (5,30010,99990);
 
 SELECT * FROM dept;
 SELECT* FROM job;
 SELECT * FROM emp;
 SELECT * FROM salarygrade;

SQL问题：

 例1：查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
 
 例2：查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
 
 例3：查询员工姓名，工资，工资等级
 
 例4：查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
 
 例5：查询出部门编号、部门名称、部门位置、部门人数
 
 例6：查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询

课下SQL练习

MySQL数据库–50道SQL强化练习题

https://www.cnblogs.com/chenglaoshi/p/12795289.html

课堂SQL练习参考答案：

 例1：查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
 # 方式一
 SELECT  
     emp.id,emp.ename, emp.salary,job.jname, job.description  
 FROM 
     emp join job
 ON  
     emp.job_id = job.id;
 
 # 方式二
 SELECT  
     emp.id,emp.ename, emp.salary,job.jname, job.description  
 FROM 
     emp,job
 where   
     emp.job_id = job.id;
     
 
 例2：查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
 SELECT 
     e.id,e.ename,e.salary, j.jname,j.description, d.dname,d.loc 
 FROM 
     emp e, job j, dept d
 WHERE e.dept_id = d.id and e.job_id = j.id;
 
 例3：查询员工姓名，工资，工资等级
 SELECT 
     e.ename, e.salary, s.grade 
 FROM 
     emp e, salarygrade s
 WHERE 
     e.salary BETWEEN s.losalary AND s.hisalary;
     
 
 例4：查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
 SELECT 
     e.ename,e.salary,j.jname,j.description, d.dname,d.loc, s.grade
 FROM
     emp e, job j, dept d, salarygrade s
 WHERE
     e.dept_id = d.id and e.job_id = j.id  
 AND     
     e.salary >= s.losalary and e.salary<= s.hisalary;
 
 
 例5：查询出部门编号、部门名称、部门位置、部门人数
 SELECT
     d.id,d.dname,d.loc, e.num
 FROM
     dept d, (SELECT emp.dept_id , COUNT(id) AS num FROM emp GROUP BY emp.dept_id) e 
 WHERE 
     d.id =  e.dept_id;
 
 
 例6：查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
 SELECT 
     e1.ename,  e2.ename
 FROM
     emp e1 -- 员工表
 LEFT join
     emp e2 -- 领导表
 on
     e1.mgr = e2.id;

事务管理

事务介绍
事务的四大特性-ACID
事务的隔离级别

1. 事务的应用场景

在实际的开发过程中的转账业务，转账是一个用户扣钱，另一个用户加钱的过程。如果其中有一条 SQL 语句出现异常，则这个转账过程执行失败。

事务所管控的是一个整体概念，要么同时成功，要么同时失败！ 其过程中的某一环节出现异常，则整个结果都应该是失败，而非部分业务成功部分业务失败。

 -- 创建数据表
 CREATE TABLE account (
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(10),
     money DOUBLE
 );
 -- 添加数据
 INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

（1）事务使用过程：

执行成功：开启事务——> 执行SQL语句——> 成功则提交事务：commit
执行失败：开启事务——> 执行SQL语句——> 失败则回滚事务：rollback

（2）执行的SQL：

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

事务开启之后, 所有的操作都会临时保存到事务日志中，事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志。

1.1. MySQL中事务的操作：

MYSQL 中可以有两种方式进行事务的操作。

手动提交事务，需要开启事务
自动提交事务，MySQL默认是自动提交事务的；Oracle数据库默认是手动提交。【DML commit】

修改事务的默认提交方式：

查看事务的默认提交方式：SELECT @@autocommit; 其中，@@表示全局变量的意思。而值 1 代表开启自动提交。 0 代表关闭自动提交

设置手动提交方式： set @@autocommit = 0;

MySQL 进行事务回滚还可以设置回滚点

设置回滚点： savepoint 回滚点名称;

回到回滚点：rollback to 回滚点名称;

设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

事务的四大特性 - ACID

原子性【Atomicity】

每一个事务都是一个整体，不可以进行拆分。事务中的所有SQL语句要么同时执行成功，要么同时结果失败！

一致性【Consistency】

事务是在执行前数据库的状态与执行之后数据库的状态应该保持一致。

1000+1000 = 1000+1000

隔离性【Isolation】

事务和事务直接的操作是隔离的，是分开的，互不影响。

持久性【Durability】

一旦事务执行成功，对数据库的影响是持久的。

事务的隔离级别【了解】

在数据库操作过程中，当多个用户操作同一数据库表时，也是存在数据并发安全问题！

隔离级别：
- 读未提交 read uncommitted
- 读已提交 read committed
- 可重复读 repeatable read
- 串行化 serializable
注意：隔离级别越高，性能越差，安全性越高！
解决问题：
- 问题1：当一个事务读到了另一个事务未提交的数据时，这个操作是不安全的，被称之脏读
- 问题2：在同一事务当中，两次读到的数据是不一致的情况。被称之为不可重复读(虚读)
- 问题3：当一个事务操作数据表中的所有记录，另一个事务做了添加操作，结果是第一个事务查询不到自己所做的修改【DML，被称之为幻读。
如何解决：

读未提交：可以产生脏读、不可重复读、幻读；

读已提交：可以产生不可重复读、幻读；

可重复读：可以产生幻读；

串行化：可以解决所有的问题。
查询数据库的隔离级别
```
 select @@tx_isolation;
```
MySQL数据库的默认隔离级别是：可重复读【repeatable read】

Oracle数据库的默认隔离级别是：读已提交【read committed】

设置数据库的隔离级别

set session transaction isolation level 事务的隔离级别;

posted on 2022-03-26 23:31 零度的脚步阅读(221) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

MySQL数据库-多表查询和事务

MySQL数据库-多表查询和事务

多表查询

笛卡尔积现象

多表联查的查询分类：

内连接查询

外连接查询

子查询

总子查询结

SQL课堂练习

课下SQL练习

事务管理

1. 事务的应用场景

1.1. MySQL中事务的操作：

事务的四大特性 - ACID

事务的隔离级别【了解】

导航

公告