1、多表查询

多表查询概述：

同时查询多张表获取需要的数据，组成完整的信息返回给用户

多表查询语法：

  1. select
  2.         字段列表
  3. from
  4.         表名列表
  5. where
  6.         条件列表
  7. group by
  8.         分组字段
  9. having
  10.         分组之后的条件
  11. order by
  12.         排序
  13. limit
  14.         分页

多表查询分类：

笛卡尔积：

多张表的记录进行结合，这种现象被称之为笛卡尔积（交叉连接）

  1. select ... from     左表        右表    ;    
  2. example1:
  3.     select    *    from biao1,biao2;
  4. example2:
  5.     select count(*) from biao1,biao2;

内连接：

拿左表的记录去匹配右表的记录，若符合条件显示（两张表的交集）

  1. -- 隐式内连接
  2. select    ...    from    左表，右表    where    连接条件;
  3. -- 显示内连接【推荐，不知道为啥，反正就推荐】
  4. select    ...    from    左表    [inner]    join 右表    on    连接条件;

外连接：

外连接又分为左外连接和右外连接
意思就是如果是左外连接就显示左边表的全部记录，显示右边表符合条件的记录，其余为null
右外连接反之，显示右边表的全部，左边的符合条件的就显示，其余也为null

  1. select    ...    from    左表    left    [outer]    join    右表    on    连接条件;【推荐】
  2. select    ...    from    左表    right    [outer]    join    右表    on    连接条件;

子查询：

一条select语句执行结果，作为另一条select语法的一部分，其实就是select语句的嵌套！

2、多表查询案例

  1. -- 创建数据库db3
  2. CREATE DATABASE db3;
  3. -- 使用db3
  4. USE db3;
  5. -- 创建相关表
  7. -- 多表案例
  8. -- 部门表
  9. CREATE TABLE dept (
  10.   id INT PRIMARY KEY AUTO_INCREMENT, -- 部门id
  11.   dname VARCHAR(50), -- 部门名称
  12.   loc VARCHAR(50) -- 部门位置
  13. );
  15. -- 添加4个部门
  16. INSERT INTO dept(id,dname,loc) VALUES 
  17. (10,'教研部','北京'),
  18. (20,'学工部','上海'),
  19. (30,'销售部','广州'),
  20. (40,'财务部','深圳');
  22. -- 职务表
  23. CREATE TABLE job (
  24.   id INT PRIMARY KEY,
  25.   jname VARCHAR(20), -- 职务名称
  26.   description VARCHAR(50) -- 职务描述
  27. );
  29. -- 添加4个职务
  30. INSERT INTO job (id, jname, description) VALUES
  31. (1, '董事长', '管理整个公司，接单'),
  32. (2, '经理', '管理部门员工'),
  33. (3, '销售员', '向客人推销产品'),
  34. (4, '文员', '使用办公软件');
  36. -- 员工表
  37. CREATE TABLE emp (
  38.   id INT PRIMARY KEY, -- 员工id
  39.   ename VARCHAR(50), -- 员工姓名
  40.   job_id INT, -- 职务id  外键
  41.   mgr INT , -- 上级领导
  42.   joindate DATE, -- 入职日期
  43.   salary DECIMAL(7,2), -- 工资 99999.99
  44.   bonus DECIMAL(7,2), -- 奖金 99999.99
  45.   dept_id INT, -- 所在部门编号  外键
  46.   CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  47.   CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
  48. );
  50. -- 添加员工
  51. INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
  52. (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
  53. (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
  54. (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
  55. (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
  56. (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
  57. (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
  58. (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
  59. (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
  60. (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
  61. (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
  62. (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
  63. (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
  64. (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
  65. (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
  67. -- 工资等级表
  68. CREATE TABLE salarygrade (
  69.   grade INT PRIMARY KEY,  -- 等级
  70.   losalary INT, -- 最低工资
  71.   hisalary INT -- 最高工资
  72. );
  74. -- 添加5个工资等级
  75. INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
  76. (1,7000,12000),
  77. (2,12010,14000),
  78. (3,14010,20000),
  79. (4,20010,30000),
  80. (5,30010,99990);
  82. -- 案例练习：
  83. /*
  84.     案例一
  85.     查询所有的员工信息，显示员公编号，员工姓名，工资，职务名称，职务描述
  86. */
  87. SELECT 
  88.     e.id,        #员工编号
  89.     e.ename,    #员工姓名
  90.     e.salary,    #员工工资
  91.     j.jname,    #职务名称
  92.     j.description    #职务描述
  93. FROM
  94.     emp AS e,    #员工表
  95.     job AS j     #职务表
  96. WHERE
  97.     e.job_id = j.id;    #当员工表中的职务id外键等于职务表中的主键时
  98. /*
  99.     案例二
  100.     查询所有的员工信息，显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
  101. */
  102. SELECT
  103.     e.id,
  104.     e.ename,
  105.     e.salary,
  106.     j.jname,
  107.     j.description,
  108.     d.dname,
  109.     d.loc
  110. FROM
  111.     emp e,
  112.     job j,
  113.     dept d
  114. WHERE
  115.     e.job_id = j.id AND e.dept_id = d.id ;
  116. /*
  117.     案例三
  118.     查询员工的姓名，工资，工资等级
  119. */
  120. SELECT
  121.     e.ename,
  122.     e.salary,
  123.     s.grade
  124. FROM
  125.     emp e,
  126.     salarygrade s
  127. WHERE
  128.     e.salary BETWEEN s.losalary AND s.hisalary  #员工表中的工资跟等级表中的数值范围作比较
  129. ORDER BY grade DESC;    #按照工资等级降序显示
  130. /*
  131.     案例四
  132.     查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
  133. */    
  134. SELECT
  135.     e.ename AS '员工姓名',
  136.     e.salary AS '员工工资',
  137.     j.jname AS '职务名称',
  138.     j.description    AS '职务描述',
  139.     d.dname    AS '部门名称',
  140.     d.loc AS '部门描述',
  141.     s.grade AS '工资等级'
  143. FROM
  144.     emp e,
  145.     job j,
  146.     dept d,
  147.     salarygrade s
  149. WHERE
  150.     e.job_id = j.id AND
  151.     e.dept_id = d.id AND
  152.     e.salary BETWEEN s.losalary AND s.hisalary;
  153. /*
  154.     案例五
  155.     查询出部门编号，部门名称，部门位置，部门人数
  156. */
  157. -- 先根据员工表 统计部门数和每个部门的人数
  158. SELECT dept_id,COUNT(id) FROM emp GROUP BY dept_id;
  159. -- 统计部门表
  160. SELECT
  161.     d.id,
  162.     d.dname,
  163.     d.loc,
  164.     e.num    #由员工表统计的个部门的具体人数
  165. FROM
  166.     dept d,
  167.     (SELECT dept_id,COUNT(id) AS num FROM emp GROUP BY dept_id) e
  168. WHERE
  169.     d.id = e.dept_id;
  170. -- PS：查询出来的一个表，可作为一个具体的表参与后续的查询操作
  171. /*
  172.     案例六
  173.     查询每个员工的名称及其上级领导的名称
  174. */
  175. -- 方法一  缺点：如果一个员工没有上级领导的话，则不会显示出来
  176. SELECT
  177.     yuangong.id '员工编号',
  178.     yuangong.ename '员工姓名',
  179.     yuangong.mgr '上级领导编号',
  180.     lingdao.ename '上级领导姓名'
  181. FROM
  182.     emp yuangong,
  183.     emp lingdao
  184. WHERE
  185.     yuangong.mgr = lingdao.id;
  186. -- 方法二
  187. SELECT 
  188.     yuangong.id,
  189.     yuangong.ename,
  190.     lingdao.id,
  191.     lingdao.ename
  192. FROM
  193.     emp yuangong
  194. LEFT OUTER JOIN
  195.     emp lingdao    
  196. ON
  197.     yuangong.mgr = lingdao.id;

3、用户权限DCL

接触不到，个人了解
做到大佬的境界自然而然就会了

4、事务

如果一个包含多个步骤的业务操作，被事务管理，那么这些个操作要么同时成功，要么同时失败
案例：用户转账操作

事务的操作步骤：

1、开启事务：begin / start transaction
2、回滚：rollback
3、提交：commit
如果事务的所有操作都成功那么提交，要是某一个失败则回滚。

事务的提交方式：

自动提交：
mysql就是自动提交的，一条DML语句会自动提交一次
手动提交：
Oracle数据库默认是手动提交事务，需要先开启事务，再提交

修改事务的提交方式：

查看事务的提交方式：
select @@autocommit；1：代表自动提交 0：代表手动提交
show variables like ‘autocommit’; NO：代表自动提交 OFF：代表手动提交
修改默认提交方式：改成手动提交
set @@autocommit = 0 ；
set autocommit = off ；

事务的四大特征（ACID）

1、原子性（Atomicity）：是不可分割的最小操作单位，要么同时成功，要么同时失败
2、一致性（Consistency）：事务操作前后，保证数据的一致性
3、隔离性（Isolation）：多个事务之间，相互独立，互不干扰
4、持久性（Durability）：当事务提交或回滚后，数据库会持久化的保存数据

事务的隔离性：

多个事务之间的隔离性，相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题
1、脏读：一个事务，读取到另一个事务中没有提交的数据
2、不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
3、幻读：一个事务操作数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 | | 1 | 读未提交 | read uncommitted | 是 | 是 | 是 | | | —- | —- | —- | —- | —- | —- | —- | | 2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle和SQL Server | | 3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL | | 4 | 串行化 | serializable | 否 | 否 | 否 | |

PS：隔离级别从小到大安全性越来越高，但是效率越来越低
查询当前数据库的隔离级别：
show variables like ‘%isolation’ ;
临时修改隔离级别：
set session transaction isolation level 级别字符串 ;