mySQL多表联查

第三章：多表联查

1.约束：

1.1概念：

什么是约束呢？约束就是不可以有重复值，也不可以有空值
(官方语言：约束是作用于表中列上的规则，用于限制加入表的数据，约束的存在保证了数据库中数据的正确性、有效性和完整性)
例

添加约束可以在添加数据的时候就限制不正确的数据，年龄是3000，数学成绩是-5分这样无效的数据，继而保障数据的完整性

1.2约束分类

约束有主键约束，非空约束，唯一约束，默认约束，检查约束

1.2.1主键约束：关键字（PRIMARY KEY）

主键约束是一行数据中的唯一的数据，要求非空，唯一，就是说不能是空值也不能重复

在mysql中勾选主键约束会默认勾选唯一约束

-- 创建表时添加约束
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY
)
-- 例
CREATE TABLE user(
id int PRIMARY KEY
)
-- 建表后添加约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名)
-- 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY(字段名)

1.2.3唯一约束：关键字(UNIQUE)

唯一约束是不能重复，例表中的值要不同

-- 添加表时添加唯一约束
CREATE TABLE  表名(
列名 数据类型 UNIQUE 
)
-- 例
CREATE TABLE user(
id int UNIQUE
)
-- 建表后添加约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE
-- 例
ALTER TABLE user MODIFY id int UNIQUE
-- 删除主键约束
ALTER TABLE 表名 DROP INDEX 字段名
-- 例
ALTER TABLE user DROP INDEX id

1.2.4非空约束：关键字(not null)

非空约束要保证列表中的值不能是空值

-- 添加表时添加非空约束
CREATE TABLE 表名(
列名 数据类型 not null
)
-- 例
CREATE TABLE user (
id int not null
)
-- 添加表后添加约束
ALTER TABLE MODIFY 字段名 数据类型

1.2.5默认约束：关键字(DEFAULT)

保存数据时，未指定值则采用默认值

-- 创建表时添加检查约束
CREATE TABLE 表名(
列名 数据类型 DEFAULT
)
-- 例
CREATE TABLE user(
id int DEFAULT
                 )
-- 添加表后添加约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

1.2.6检查约束：关键字(CHECK)

保证列表中的值满足任意一条条件

注意注意：MySQL不支持检查约束。

这样是不是就没办法保证年龄在指定的范围内了？从数据库层面不能保证，以后可以在java代码中进行限制，一样也可以实现要求。

1.2.7外键约束：

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

但是外键约束必须要求其他表要有主键，唯一，非空约束

看如何理解上面的概念呢？如下图有两张表，员工表和部门表：

员工表中的dep_id字段是部门表的id字段关联，也就是说1号学生张三属于1号部门研发部的员工。你如果想直接删除部门表则会报错以下，而我们上面说的两张表的关系只是我们认为它们有关系，此时需要通过外键让这两张表产生数据库层面的关系，这样你要删除部门表中的1号部门的数据将无法删除。要想删除部门表中的1号部门的数据先删除emp员工表的数据，才能删除1号部门

你如果想直接删除部门表则会报错以下

-- 1语法
-- 创建表时外键约束
CREATE TABLE EMP (
列名 数据类型，
       [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
)
-- 建表后添加约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键自动名称) REFERENCES 主表名称(列表名称)
-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

1.2.8 约束练习

根据需求，为表添加合适的约束

-- 员工表
CREATE TABLE emp (
    id INT,  -- 员工id，主键且自增长
    ename VARCHAR(50), -- 员工姓名，非空且唯一
    joindate DATE,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
);

上面一定给出了具体的要求，我们可以根据要求创建这张表，并为每一列添加对应的约束。建表语句如下：

DROP TABLE IF EXISTS emp;

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

通过上面语句可以创建带有约束的 emp 表，约束能不能发挥作用呢。接下来我们一一进行验证，先添加一条没有问题的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

验证主键约束，非空且唯一

INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);

执行结果如下：

从上面的结果可以看到，字段 id 不能为null。那我们重新添加一条数据，如下：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

执行结果如下：

从上面结果可以看到，1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);

执行结果如下：

验证非空约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

执行结果如下：

从上面结果可以看到，ename 字段的非空约束生效了。

验证唯一约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

执行结果如下：

从上面结果可以看到，ename 字段的唯一约束生效了。

验证默认约束

INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

执行完上面语句后查询表中数据，如下图可以看到王五这条数据的bonus列就有了默认值0。

==注意：默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。==

如下：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);

执行完上面语句后查询表中数据，如下图可以看到赵六这条数据的bonus列的值是null。

验证自动增长： auto_increment 当列是数字类型并且唯一约束

重新创建 emp 表，并给id列添加自动增长

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

接下来给emp添加数据，分别验证不给id列添加值以及给id列添加null值，id列的值会不会自动增长：

INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);

执行结果如下

id自动增长了

2.多表联查

什么是多表联查呢？

多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据。先将一下表关系

2.1.表关系（一对多）：

如：部门和员工
一个部门对应多个员工，看下图

实现方式

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

案例
我还是用tb_emp表和tb_dept表举例

经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）：

建表语句如下：

DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
-- 部门表
CREATE TABLE tb_dept (
id int primary key auto_increment 
dep_name varchar(20)
addr varchar(20)
)
-- 员工表
CREATE TABLE tb_emp(
id int PRIMARY KEY auto_increment 
    emp_name varchar(20)
    age varchar(20)
-- 添加外键 dept_id,关联dept表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES tb_dept(id)
)

查看表结构模型图：

2.2表关系（多对多）：

什么是多对多呢：

比如一个商品对应多个订单，一个订单包含多个商品

实现方式

建立第三张表，中间表必须包含两个表的主键，分别关联两方主键

案例

以订单表和商品表为例

订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键指向两张表的主键：

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_ordr
DROP TABLE IF EXISTS tb_goods
-- 订单表
CREATL TABLE tb_order(
id  int PRIMARY key AUTO_INCREMENT
    payment double(10,2)
    payment_type TINYINT
    status TINYINT
)
-- 商品表
CREATE TABLE tb_order_goods(
id int PRIMARY KEY AUTO_INCREMENT
    title varchar(100) 
    price double(10,2)
)
-- 订单商品中间表
CREATE TABLE tb_order_goods(
id int PRIMARY KEY AUTO_INCREMENT
    order_id int
    goods_id int
    count int 
)
-- 建表后添加外键
ALTER TABLE tb_order_goods add CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id)
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

看表结构模型图：

2.3表关系(一对一)：

什么是一对一呢：

一对一就是将一个表拆分开来，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

实现方法：

在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

案例：

以用户表举例

看上面，我们在使用的过程中只用得到id，photo，nickname，age，gendercity，此时我们可以将这一张表分成两张表，来提升查询性能

-- 建表语句
create table tb_user_desc (
    id int primary key auto_increment,
    city varchar(20),
    edu varchar(10),
    income int,
    status char(2),
    des varchar(100)
);

create table tb_user (
    id int primary key auto_increment,
    photo varchar(100),
    nickname varchar(50),
    age int,
    gender char(1),
    desc_id int unique,
    -- 添加外键
    CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)    
);

查看表结构：

2.4多表联查：

先创建一些数据

CREATE TABLE dept(
    did INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(20)
);

# 创建员工表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    gender CHAR(1), -- 性别
    salary DOUBLE, -- 工资
    join_date DATE, -- 入职日期
    dep_id INT,
    FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);

2.4.1：笛卡尔积

什么是笛卡尔积呢：

看以下结果

创建好了之后先做个练习：执行下面的多表查询语句

select * from emp , dept;  -- 从emp和dept表中查询所有的字段数据

看结果

看完结果我们得知孙悟空他跟部门表所有得部门都有关系，如果孙悟空整个员工属于1号部门得，但他还关联者2，3，4部门，得只这都是一些无线得数据，这就叫做笛卡尔积

笛卡尔积又是怎么来得呢：

笛卡尔积是交叉连接得来得

看图

怎么防止出现笛卡尔积呢？

我们要通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据，

select * from emp , dept where emp.dep_id = dept.did;

效果如下

上面语句就是连接查询，那么多表查询都有哪些呢？

连接查询
- 内连接查询：相当于查询AB交集数据
- 外连接查询
  - 左外连接查询：相当于查询A表所有数据和交集部门数据
  - 右外连接查询：相当于查询B表所有数据和交集部分数据
子查询

2.4.2：内连接

概念：内连接相当与查询ab表得交集数据

内连接又分为两种内连接

2.4.2.1：隐式内连接

语法

select 字段列表 from 表名1，表名2 where 条件

案例

SELECT * FROM emp , dept WHERE emp.dep_id = dept.did;

结果如下
查询emp的name ,gender,dept表的dname

select emp.name,emp.gender,dept.dname where emp.dep_id=dept.did

结果如下

2.4.2.2：显式内连接

语法

select * from 表名1 inner join 表名2 on 条件
-- 例
select * from emp inner join dept on emp.dep_id = dept.did;
-- 上面语句中的inner可以省略，可以书写为如下语句
select * from emp  join dept on emp.dep_id = dept.did;

2.4.3：外连接

2.4.3.1左外连接

语法

select * from 表名1 LEFT OUTER JOIN 表名2 on 条件

案例
查询emp表所有数据和对应的部门信息

SELECT * FROM emp LEFT JOIN dept ON emp.`dep_id` = dept.`did`;

结果如下

结果先显示了左表的所有数据(包括为null值)及与右表关联的数据

2.4.3.2右外连接

语法

select * from 表1 right join 表2 on 条件
select * from emp right join dept on empdep_id =dept.did

案例

查询dept表所有数据和对应的员工信息

select * from emp right join dept on emp.dep_id = dept.did;

看结果他先查询到了右表的数据及左边的数据

3子查询

概念：查询中嵌套查询，称嵌套查询为子查询。

什么是嵌套查询呢？先举个例子

查询工资高于猪八戒的员工信息

我们要先查询出猪八戒的员工信息

select salary from emp where name='猪八戒'

再查询出高于猪八戒的工资的员工信息

select * from emp where salary >3600

第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换

select * from emp where salary >select salary from emp where name='猪八戒'

这就是嵌套查询

子查询根据查询结果不同，作用不同
- 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
- 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
- 子查询语句结果是多行多列，子查询语句作为虚拟表

3.1标量子查询

概念：
- 子查询返回的结果是单个值(数子，字符串，日期等)，最简单的形式，这种子查询就称为标量子查询
- 常见的操作符有：= ,<>,>,>=,<,<=

案例

查询 '财务部' 和 '市场部' 所有的员工信息

-- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
select did from dept where dname = '财务部' or dname = '市场部';

select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

查询入职日期是 '2011-11-11' 之后的员工信息和部门信息

-- 查询入职日期是 '2011-11-11' 之后的员工信息
select * from emp where join_date > '2011-11-11' ;
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;

案例

-- 查询销售部的所有员工信息
select did from detp where dname = '销售部'
select * from emp where did=(select did from detp where dname = '销售部')
-- 查询在'方东白'入职之后的员工信息
select entrydate from emp where name ='方东白'
select * from emp where entrdate > (select entrydate from emp where name ='方东白')

3.2列子查询

概念
- 子查询返回的结果是一列或者多行，这种子查询就叫列子查询
- 常见的操作符有：in（在指定的范围之内，多选一），not in（不在指定的范围之内），any(在子查询返回列表中，只要满足列表中任意一个)， some（跟any一样），all（在子查询返回列表中，要满足所有条件）
案例

-- 查询销售部和市场部的所有员工信息
select did from detp where dname = '销售部' or  ='市场部'
select * from emp where did in (select did from detp where dname = '销售部' or  ='市场部')
-- 查询比财务部所有人工资都高的员工信息
select did from dept where dname ='财务部'
select salary from emp where did = (select did from dept where dname ='财务部')
select * from emp where did >all(select salary from emp where did = (select did from dept where dname ='财务部'))
-- 查询出比研发部其中任意一个员工工资高的所有员工信息
一
select did from dept where dname ='研发部'
select salary from emp where did=(select did from dept where dname ='研发部')
select * from emp where did > ANY (select salary from emp where did=(select did from dept where dname ='研发部'))
二
select did from dept where dname ='研发部'
select salary from emp where did=(select did from dept where dname ='研发部')
select * from emp where did > some (select salary from emp where did=(select did from dept where dname ='研发部'))

3.3行子查询

概念
- 子查询返回的结果是一行或者多列，就叫行子查询
- 常用的操作符有：=,in,not in
案例

-- 查询与张无忌 的薪资及直属领导相同的员工信息
select salary, managerid from emp where name ='张无忌'
select * from emp where (salary,managerdid) =(select salary, managerid from emp where name ='张无忌')
-- 注意：由于是多列的，所以将字段写成集合形式

3.4表子查询

概念

子查询返回的结果是多行多列，这种叫做表子查询
常见的操作符有 in

-- 查询与鹿杖客，宋远桥的职位和薪资相同的员工信息
select job，salary from emp where name ='鹿杖客' or name= '宋远桥'
select * from emp (job,salary) =(select job，salary from emp where name ='鹿杖客' or name= '宋远桥')

3.5 案例

环境准备：

-- 部门表
CREATE TABLE dept (
  did INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 员工表
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)
);
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);
                
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');


-- 添加员工
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);


-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

需求

查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

/*
    分析：
        1. 员工编号，员工姓名，工资 信息在emp 员工表中
        2. 职务名称，职务描述 信息在 job 职务表中
        3. job 职务表 和 emp 员工表 是 一对多的关系 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;

-- 方式二 ：显式内连接
SELECT
    emp.id,
    emp.ename,
    emp.salary,
    job.jname,
    job.description
FROM
    emp
INNER JOIN job ON emp.job_id = job.id;

查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

/*
    分析：
        1. 员工编号，员工姓名，工资 信息在emp 员工表中
        2. 职务名称，职务描述 信息在 job 职务表中
        3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

        4. 部门名称，部门位置 来自于 部门表 dept
        5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/

-- 方式一 ：隐式内连接
SELECT
    emp.id,
    emp.ename,
    emp.salary,
    job.jname,
    job.description,
    dept.dname,
    dept.loc
FROM
    emp,
    job,
    dept
WHERE
    emp.job_id = job.id
    and dept.id = emp.dept_id
;

-- 方式二 ：显式内连接
SELECT
    emp.id,
    emp.ename,
    emp.salary,
    job.jname,
    job.description,
    dept.dname,
    dept.loc
FROM
    emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id

查询员工姓名，工资，工资等级

/*
    分析：
        1. 员工姓名，工资 信息在emp 员工表中
        2. 工资等级 信息在 salarygrade 工资等级表中
        3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
    emp.ename,
    emp.salary,
    t2.*
FROM
    emp,
    salarygrade t2
WHERE
    emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

/*
    分析：
        1. 员工编号，员工姓名，工资 信息在emp 员工表中
        2. 职务名称，职务描述 信息在 job 职务表中
        3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

        4. 部门名称，部门位置 来自于 部门表 dept
        5. dept 和 emp 一对多关系 dept.id = emp.dept_id
        6. 工资等级 信息在 salarygrade 工资等级表中
        7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
    emp.id,
    emp.ename,
    emp.salary,
    job.jname,
    job.description,
    dept.dname,
    dept.loc,
    t2.grade
FROM
    emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

查询出部门编号、部门名称、部门位置、部门人数

/*
    分析：
        1. 部门编号、部门名称、部门位置 来自于部门 dept 表
        2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
        3. 使用子查询，让部门表和分组后的表进行内连接
*/
-- 根据部门id分组查询每一个部门id和员工数
select dept_id, count(*) from emp group by dept_id;

SELECT
    dept.id,
    dept.dname,
    dept.loc,
    t1.count
FROM
    dept,
    (
        SELECT
            dept_id,
            count(*) count
        FROM
            emp
        GROUP BY
            dept_id
    ) t1
WHERE
    dept.id = t1.dept_id

4.事务

概念
- 事务是一组操作的集合，他是一个不可分隔的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失效
- 数据库的事务（Transaction）是一种机制、一个操作序列，包含了==一组数据库操作命令==。

这些概念不好理解，接下来举例说明，如下图有一张表

张三和李四账户中各有2000块钱，现李四需要转换1000块钱给张三，具体的转账操作为

第一步：查询张三账户余额

SELECT * FROM account WHERE NAME='张三';

第二步：从张三账户金额 -1000

UPDATE account SET money=money-1000 WHERE NAME='张三';

第三步：给李四账户金额 +1000

UPDATE account SET money=money+1000 WHERE NAME='李四';

4.1语法

开启事务
```
START TRANSACTION;
或者  
BEGIN;
```
提交事务
```
commit;
```
回滚事务
```
rollback;
```
不添加事务演示
```
SELECT * FROM account WHERE NAME='张三';
UPDATE account SET money=money-1000 WHERE NAME='张三';
出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
UPDATE account SET money=money+1000 WHERE NAME='李四';
```
结果：
看转账过程中第二步完成后出现了异常第三步没有执行，就会造成李四账户金额少了1000，而张三金额并没有多1000；这样的系统是有问题的。如果解决呢？使用事务可以解决上述问题
开启事务代码

第一种 -- 开启事务
select TRANSACTION
UPDATE account SET money=money-1000 WHERE NAME='张三';
出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
UPDATE account SET money=money+1000 WHERE NAME='李四';
-- 回滚事务
rollback;
-- 提交事务
commit;

二-- 开启事务
BEGIN
UPDATE account SET money=money-1000 WHERE NAME='张三';
出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
UPDATE account SET money=money+1000 WHERE NAME='李四';
-- 回滚事务
rollback;
-- 提交事务
commit;
三 -- 改提交方法
-- mysql中默认提交的方式是自动提交
SELECT @@autocommit =0 -- 将自动提交方式改为手动提交

结果

事务处理图

上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。

4.2事务的四大特性

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

原子性就像刚才的银行转账一样，如果有一步出错，就会全部错了

一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

一致性就是张三和李四的账户都得是一样得

隔离性（Isolation） :多个事务之间，操作的可见性
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

mySQL多表联查

第三章：多表联查

1.约束：

1.1概念：

1.2约束分类

1.2.1主键约束：关键字（PRIMARY KEY）

1.2.3唯一约束：关键字(UNIQUE)

1.2.4非空约束：关键字(not null)

1.2.5默认约束：关键字(DEFAULT)

1.2.6检查约束： 关键字(CHECK)

1.2.7外键约束：

1.2.8 约束练习

2.多表联查

2.1.表关系（一对多）：

2.2表关系（多对多）：

2.3表关系(一对一)：

2.4多表联查：

2.4.1：笛卡尔积

2.4.2：内连接

2.4.2.1：隐式内连接

2.4.2.2：显式内连接

2.4.3：外连接

2.4.3.1左外连接

2.4.3.2右外连接

3子查询

3.1标量子查询

3.2列子查询

3.3行子查询

3.4表子查询

3.5 案例

4.事务

4.1语法

4.2事务的四大特性

陈礼浩

引用和评论

mybatis_Plus

2025年最新反编译微信小程序的教程及工具

手写一个动态海洋和天空效果的vue hooks

你可能不知道的图片加载相关知识

原生JS大揭秘—JS代码执行原理解刨

原生electron起步-从零到一完成构建和打包

LRU算法，你别跑，我就要吃透你

1.2.6检查约束：关键字(CHECK)