一对多

之前学的部门表和员工表那里就是一对多的关系，这里就不演示

多对多

-- 多对多:如下的学生表、课程表之间的关系

-- (1)案例:学生与课程之间的关系 -- (2)关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择 -- (3)实现:建立第三张中间表，中间表至少包含两个外键，其中一个外键是关联学生表的主键，另一个外键是关联课程表之间的主键

create database if not exists bilibili; #创建数据库bilibili use bilibili; #选择bilibili数据库，即可进行下面的代码演示 drop database if exists bilibili; #代码演示结束后删除bilibili数据库。这个小文件全部代码演示完再删 show databases; #查看是否删除完成

create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null,'黛绮丝','2022567890'),(null,'谢逊','2022567891'),(null,'殷天正','2022567892'),(null,'韦一笑','2022567893');

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values (null,'Java'),(null,'PHP'),(null,'MySQL'),(null,'Hadoop');

create table student_course( #学生表和课程表的中间表
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,3,1),(null,4,4);

验证，右键student_course表，点击diagrams，点击Show Visualization，即可查看可视化的表结构关系

一对一

-- 一对一，简单来说就是把一张复杂的表拆分成若干个简单的表，然后还要给这若干个表加上一个字段，这个字段要加上唯一约束关联主表

-- (1)案例:用户与用户详情的关系 -- (2)关系:一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率 -- (3)实现:在任意一方加入外键，关联另外一方的主键，并且设置外键的约束为唯一，即为unique

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1: 男 , 2: 女',
    phone char(11) comment '手机号',
    salary int comment '工资',
    dept varchar(10) comment '部门'
) comment '用户基本信息表';
insert into tb_user(id,name,age,gender,phone,salary,dept) values
         (null,'黄渤',45,'1',18845678901,4500,'管理部'),#id字段不用指定，我们设置了自增
         (null,'冰冰',35,'2',16845678902,4100,'财政部'),
         (null,'码云',55,'1',15045678903,3900,'后勤部'),
         (null,'李彦宏',50,'1',18845678904,5100,'销售部'),
         (null,'于凯',32,'2',18845678905,5500,'运营部'),
         (null,'江天',null,null,null,5200,'人事部');

create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userid int unique comment '用户ID',
    job varchar(10) comment '职位',
    managerid int comment '上级领导id',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育表';
insert into tb_user_edu(id,degree,major,primaryschool,middleschool,university,userid,job,managerid) values
         (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1,'师长',2),#id字段不用指定，我们设置了自增
         (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2,'总部',null),
         (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3,'团长',1),
         (null,'本科','数学','阳泉区第一小学','阳泉区第一中学','清华大学',4,'连长',3),
         (null,'硕士','语文',null,'阳泉区第一中学','厦门大学',null,'排长',4);

验证,点击打开tb_user_edu表，会发现userid字段是外键，关联的是tb_user表的id字段，

tb_user_edu表的userid字段加了唯一约束，即tb_user_edu表的一行(也叫一条记录)对应的就是tb_user表中一个用户的信息

多表查询

-以上面的tb_user表、tb_user_edu表为例

xxxxxxxxxx
#单表查询语法
select * from tb_user_edu;

xxxxxxxxxx
#多表查询语法，查到30条数据，原因:两张表各5、6条数据，共查询到的就是5*6为30条数据。称为笛卡尔积
select * from tb_user_edu,tb_user;

#上面那行的多表查询会造成无效的笛卡尔积，也就是查询出来的数据多出来一大堆没用的，例如tb_user表中我们查黄渤的小学数据会出现4个小学，我们 #只想查到静安区小学(这个才是正确的，其它学校是由于笛卡尔积造成的无效数据，解决如下)

xxxxxxxxxx
#也就是where一下双方的外键、被外键联系的主键
select * from tb_user_edu,tb_user where tb_user_edu.userid = tb_user.id;

#此时上面那行查到的数据就是tb_user_edu表、tb_user表的总和数据，一张表的形式呈现，不显示无效数据

#注意外键字段不能为null，例如tb_user_edu表的第5行的userid字段(也是我们设置的外键字段)为null，所以上面的那个多表查询语句是查不了这条 #数据的，也就是原本是查5条数据，由于第5条数据的外键字段为null，则实际只能查出来4条数据。同理tb_user表的null值也查不了

当字段里面的某个值为null时，可以用下面学的‘左、右外连接'查询的方式来解决，就可以查到两张表的所有数据，包含null的数据

多表查询的分类

1、连接查询

内连接:相当于查询A、B交集部分数据

外连接:

(1)左外连接:查询左表所有数据，以及两张表交集部分数据

(1)右外连接:查询右表所有数据，以及两张表交集部分数据

自连接:当前表与自身的连接查询，自连接必须使用表别名

2、子查询

内连接

连接条件依旧是上面多表查询用过的tb_user.id = tb_user_edu.userid。原因:tb_user_edu表的外键userid关联的是tb_user表的id字段

内连接有两种形式

(1)隐式内连接。查询到的数据是两张表的公共存在的数据，即查的是交集

xxxxxxxxxx
select 字段列表 from 表1,表2 where 条件 ...;

(2)显示内连接。查询到的数据是两张表的公共存在的数据，即查的是交集

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

总结

显示内连接比隐式内连接的性能高，有更快的执行速度

inner关键字可省略不写

内连接演示

-- 1. 查询每一个用户的名字，及关联的专业的名称，要求使用隐式内连接实现。操作tb_user_edu表、tb_user表 -- 消除笛卡尔积:使用连接条件，在tb_user_edu表的userid(外键)字段=tb_user表的id(被外键关联)字段

select tb_user.name,tb_user_edu.major from tb_user_edu,tb_user where tb_user_edu.userid = tb_user.id;

注意userid为null的是查不到的，因为userid是tb_user_edu的外键，内查询原理就是通过外键建立连接条件来查两张表交集的内容

上面那条内连接查询语句还需要一个小小的优化，当表名比较长是，使用表名.就会造成代码很长，我们可以该表名起一个别名，再使用内连接查询语句，如下

select er.name,edu.major from tb_user_edu as edu,tb_user as er where edu.userid = er.id;

注意如果已经为表起了别名就必须使用表别名.，不能使用表名.，例如起了别名之后就不能使用tb_user_edu.userid或tb_user.id

-- 2. 查询每一个用户的名字，及关联的专业的名称，要求使用显示内连接实现。操作tb_user_edu表、tb_user表

select tb_user.name,tb_user_edu.major from tb_user_edu inner join tb_user on tb_user_edu.userid = tb_user.id;

注意上面那行，inner join左边的表是外键表，右边的是被关联表

xxxxxxxxxx
#当然也可以使用别名，as可省略
select er.name,edu.major from tb_user_edu as edu inner join tb_user as er on edu.userid = er.id;
#inner关键字可省略
select er.name,edu.major from tb_user_edu as edu join tb_user as er on edu.userid = er.id;

外连接

连接条件依旧是上面内连接用过的tb_user.id = tb_user_edu.userid。原因:tb_user_edu表的外键userid关联的是tb_user表的id字段

外连接有两种形式

(1)左外连接。查询表1(也就是左表)的所有数据，也查询表1和表2交集部分的数据

xxxxxxxxxx
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;

(2)右外连接。查询表2(也就是右表)的所有数据，也查询表1和表2交集部分的数据

select 字段列表 from 表1 right [outer] join 表2 on 条件 ...;

总结

outer关键字可省略不写

实际开发中，左外连接会用的多一些。或者把右外连接的写法改为左外链接

外连接演示

左外连接的写法可以转为右外连接的写法。换关键字，调2个表的位置即可

-- 1. 查询tb_user_edu表的所有数据，及对应的tb_user表的姓名，要求使用左外连接实现。操作tb_user_edu表、tb_user表

select tb_user_edu.*,tb_user.name from tb_user_edu left outer join tb_user on tb_user.id = tb_user_edu.userid;
select edu.*,er.name from tb_user_edu as edu left outer join tb_user as er on er.id = edu.userid; #使用别名，简化代码长度

-- 优点:可以把字段中，为null值的也查出来，原理:左外连接会把左表的所有数据都查出来。写在left outer join左边的表就是左表

-- 2. 查询tb_user表的所有数据，及对应的tb_user_edu表的大学，要求使用右外连接实现。操作tb_user_edu表、tb_user表

select tb_user.*,tb_user_edu.university from tb_user_edu right outer join tb_user on tb_user.id = tb_user_edu.userid;
select er.*,edu.university from tb_user_edu as edu right outer join tb_user as er on er.id = edu.userid; #使用别名，简化代码长度

-- 优点:可以把字段中，为null值的也查出来，原理:右外连接会把右表的所有数据都查出来。写在right outer join右边的表就是右表，左表的null值也会被查到

-- 3. 其实我们可以使用第1点的left左外连接的写法，完成上面第2点的right右外连接相同的效果。或者使用第2点的右外连接的写法完成第1点的左外连接相同的效果。如下

select tb_user_edu.*,tb_user.name from tb_user_edu left outer join tb_user on tb_user.id = tb_user_edu.userid;
select tb_user_edu.*,tb_user.name from tb_user right outer join tb_user_edu on tb_user.id = tb_user_edu.userid;

上面的两条多表查询语句的效果是一样的，以tb_user_edu为左表，即会完全把tb_user_edu表的数据查出来。tb_user作为右表只是查我们需要的即可，当然也可全查

select tb_user.*,tb_user_edu.university from tb_user_edu right outer join tb_user on tb_user.id = tb_user_edu.userid;
select tb_user.*,tb_user_edu.university from tb_user left outer join tb_user_edu on tb_user.id = tb_user_edu.userid;

上面的两条多表查询语句的效果是一样的，以tb_user为左表，即会完全把tb_user表的数据查出来。tb_user_edu作为右表只是查我们需要的即可，当然也可全查

自连接

-- 自连接查询，可以是内连接查询，也可以是外连接查询

select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;

自连接演示

-- 1. 查询职务job，及其直属领导managerid。操作tb_user_edu表

难点:在tb_user_edu表中很容易查到职务(job)，但是领导managerid只给出了数字，我们需要把数字理解为tb_user_edu表的id，例如师长

的数字是null,那么师长就没有领导，也就是师长是最高的。又例如连长右边的数字为3，表示连长的领导是id为3的job，也就是连长的领导是团长

注意:跟外键没关系，自连接是纯纯一张表内的操作，只跟自己有关

select 自己.job,上级.job from tb_user_edu as 自己,tb_user_edu as 上级 where 自己.managerid = 上级.id;#自连接

当已知'目标managerid的条件id'要去查'字段里面的值managerid'是'其它字段的值job'的答案时，就可以把manager、id字段作为我们

的连接条件，然后查出这个目标job，注意为了代码能阅读最好要加别名

我们有5条数据，只查到显示出4条数据，原因:总部是最高的，它的直属领导为null，所以查不到总部的直属领导。解决:使用外连接，如下

select 自己.job,上级.job from tb_user_edu as 自己 join tb_user_edu as 上级 on 自己.managerid = 上级.id;#内连接，一样存在上述问题
select 自己.job,上级.job from tb_user_edu as 自己 left join tb_user_edu as 上级 on 自己.managerid = 上级.id;#外连接，完美解决

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集，语法如下，注意一条语句是分三行写

联合查询可以在一张表，或者多张表之间使用，这里只演示一张表时的使用

对于联合查询的多张表的列数必须保持一直，也就是多条查询语句返回的字段数量、字段类型要保持一致

select 字段列表 from 表A ...

union [all]

select 字段列表 from 表B ...;

第一行查询的结果和第三行查询的结果会合并起来，一起输出

-- 1. 将薪资低于5000的员工，和年龄大于40的员工全部查询出来。操作tb_user表

select * from tb_user where salary < 5000; #只查薪资
select * from tb_user where age > 40; #只查年龄

-- 3行合并为一条语句，作用是把第1行查询的结果和第3行查询的结果会合并起来，一起输出

xxxxxxxxxx
select * from tb_user where salary < 5000
union all
select * from tb_user where age > 40;

-- 注意上面的联合查询是存在重复输出的，毕竟是2次查询都是一张表的，难免会有重复输出 -- 解决:使用前面学的or，但是or可能不会走索引，导致索引失效，当数据量大且索引失效，搜索就会变成全表扫描，降低查询效率，一般不建议用or

select * from tb_user where salary < 5000 or age > 40;# 使用or，可以达到去重，缺点是只能在一张表用。or是前面学的，不是本节课的重点

-- 最佳的去重方案，去掉联合查询的all关键字即可，如下

select * from tb_user where salary < 5000
union
select * from tb_user where age > 40;

-- 联合查询弊端，当两条查询语句的返回字段不一致时就会报错。例如第1条语句返回所有字段，第2条语句只返回name字段。报错无解，这是规则

select * from tb_user where salary < 5000
union
select name from tb_user where age > 40;

子查询

概念: SQL语句中嵌套select语句，称为嵌套查询，又称子查询，例如下面那行的(select column1 from t2)就是子查询

select * from t1 where column1 = (select column1 from t2);

注意:子查询外部的语句可以是insert / update / delete / select 的任何一个

根据子查询结果不同，分类以下四类: 1、标量子查询，即子查询结果为单个值 2、列子查询，即子查询结果为一列 3、行子查询，即子查询结果为一行 4、表子查询，即查询结果为多行多列

根据子查询的位置不同，分为以下三类: 1、where之后 2、from之后 3、select之后*/

标量子查询

-- 1. 查询销售部的所有员工信息

分析:emp表可查询所有员工的信息，但是没有直接给部门信息，部门在dept表中，我们怎么拿到部门名呢。如下

在'emp表的dept_id外键字段'中关联了'dept表的id字段'，由此我们可以把上面的需求分为两步，如下

-- 为了不跟上面那些代码冲突，我们创建新的两张表，进行下面的代码演示，注意从这开始，下面用的都是这两张表

create table dept(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id,name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

create table emp(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    job varchar(10) comment '职位',
    salary int comment '工资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '部门id',
    constraint fk_deptid foreign key (dept_id) references dept(id)
) comment '用户基本信息表';
insert into emp(id,name,age,job,salary,entrydate,managerid,dept_id) values
         (null,'金庸',66,'总裁',20000,'2000-01-01',null,5),#id字段不用指定，我们设置了自增
         (null,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
         (null,'杨逍',33,'开发',8400,'2000-11-03',2,1),
         (null,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
         (null,'常遇春',43,'开发',10500,'2004-09-07',3,1),
         (null,'小邵',19,'心理师',6600,'2004-10-12',2,1),
         (null,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
         (null,'周芷若',19,'会计',4800,'2006-06-02',7,3),
         (null,'丁敏君',23,'出纳',5250,'2009-05-13',7,3),
         (null,'赵敏',20,'市场部总监',12500,'2004-10-12',1,2),
         (null,'鹿杖客',56,'职员',3750,'2006-10-03',10,2),
         (null,'鹤笔翁',19,'职员',3750,'2007-05-09',10,2),
         (null,'方东白',19,'职员',5500,'2009-02-12',10,2),
         (null,'张三丰',88,'销售总监',14000,'2004-10-12',1,4),
         (null,'俞莲舟',38,'销售',4600,'2004-10-12',14,4),
         (null,'宋远桥',40,'销售',4600,'2004-10-12',14,4),
         (null,'陈友谅',42,null,2000,'2011-10-12',1,null);

第一步: 查询出dept表的销售部的id

select id from dept where name = '销售部'; #查询的值是4

第二步: 根据查询出的销售部的id，以及emp表的dept_id外键字段，去这个销售部门对应的emp表的员工信息

select * from emp where dept_id = 4;

优化。上面的是2条语句，如何使用1条语句，如下

select * from emp where dept_id = (select id from dept where name = '销售部'); #由于已经有1个select查询关键字，所以要用括号

括号里面的查询语句，就称为子查询

又由于该子查询返回的记录(也叫数据)只有一条(也叫一行)，所以该子查询就称为标量子查询

-- 2. 查询在'方东白'员工入职日期后，入职的其他员工信息。同样也是分为两步，如下

第一步: 查询'方东白'的入职日期

select entrydate from emp where name = '方东白'; #查询的值是2009-02-12

第二步: 查询指定入职日期之后的入职的员工信息

select * from emp where entrydate > '2009-02-12';

优化。上面的是2条语句，如何使用1条语句，如下

select * from emp where entrydate > (select entrydate from emp where name = '方东白'); #由于已经有1个select查询关键字，所以要用括号

括号里面的查询语句，就称为子查询

又由于该子查询返回的记录(也叫数据)只有一条(也叫一行)，所以该子查询就称为标量子查询

列子查询

列子查询: 子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询

常用的操作符: in、not in、any、some、all

-- 1. 查询'销售部' 和 '市场部'的所有员工信息。分两步

第一步:查询销售部和市场部的部门id

select id from dept where name = '销售部' or name = '市场部'; #查询的值是2和4

第二步:根据查询出来的部门id来查询员工信息

xxxxxxxxxx
#in (2,4) 的意思是dept_id为2和4，注意不是连续的区间，而是具体的值，多个值用逗号隔开
select * from emp where dept_id in (2,4);

如何将上面的2条语句合并为1条，如下

xxxxxxxxxx
#由于已经有1个select查询关键字，所以要用括号
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

括号里面的查询语句，就称为子查询

又由于该子查询返回的记录(也叫数据)有一列多行(这里是一列两行)，所以该子查询就称为列子查询

-- 2. 查询比财务部所有人工资都高的员工信息。分两步

第一步:查询所有财务部人员的工资

xxxxxxxxxx
#先查询dept表的财务部的id,查询的值为3
select id from dept where name = '财务部';
#由于dept表没有工资数据，我们需要根据查询到的值，以及dept表的外键字段，查询到emp表的工资数据
select salary from emp where dept_id = 3;

如何将上面的2条语句合并为1条，如下

xxxxxxxxxx
#由于已经有1个select查询关键字，所以要用括号
select salary from emp where dept_id = (select id from dept where name = '财务部');

第二步:查询比财务部所有人的工资都高的员工信息

select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));

上面那行all后面的括号里面的称为子查询。all表示必须全部满足

又由于该子查询返回的记录(也叫数据)有一列多行(这里是一列三行)，所以该子查询就称为列子查询

-- 3. 查询比研发部其中任意一人工资高的员工信息。分两步

第一步:查询所有研发部人员的工资

xxxxxxxxxx
#先查询dept表的研发部的id,查询的值为1
select id from dept where name = '研发部';
#由于dept表没有工资数据，我们需要根据查询到的值，以及dept表的外键字段，查询到emp表的工资数据
select salary from emp where dept_id = 1;

如何将上面的2条语句合并为1条，如下

xxxxxxxxxx
#由于已经有1个select查询关键字，所以要用括号
select salary from emp where dept_id = (select id from dept where name = '研发部');

第二步:查询比研发部任意一人的工资高的员工信息

select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

上面那行all后面的括号里面的称为子查询，all表示满足其中一个

select * from emp where salary > some (select salary from emp where dept_id = (select id from dept where name = '研发部'));

上面那行some后面的括号里面的称为子查询。some表示满足其中一个。all和some的效果一样，上面两条语句的查询是一样的效果

又由于该子查询返回的记录(也叫数据)有一列多行(这里是一列五行)，所以该子查询就称为列子查询

行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询

常用的操作符:=、<>、in、not in

-- 1. 查询与'张无忌'的薪资及直属领导相同的员工信息。分两步

第一步:查询张无忌的薪资和直属领导

select salary,managerid from emp where name = '张无忌';#查询的结果分别是12500、1

第二步:查询与'张无忌'的薪资及直属领导相同的员工信息

select * from emp where salary = 12500 and managerid = 1;#以前的写法
select * from emp where (salary,managerid) = (12500,1);#现在的写法:意思是salary等于12500，并且managerid等于1
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');#优化写法

上面那行右边括号里面的查询语句，就称为子查询

又由于该子查询返回的记录(也叫数据)有一行多列(这里是一行两列)，所以该子查询就称为行子查询。'='连接

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用的操作符:in

-- 1. 查询与'鹿杖客'，'宋远桥'的职位和薪资相同的员工信息。分两步

第一步:查询'鹿杖客'，'宋远桥'的职位和薪资

select job,salary from emp where name = '鹿杖客' or name = '宋远桥';

第二步:查询与'鹿杖客'，'宋远桥'的职位和薪资相同的员工信息

select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥');

上面那行右边括号里面的查询语句，就称为子查询。in表示多选一，即满足子查询结果的任意一个即可。in在列子查询那里有讲过

又由于该子查询返回的记录(也叫数据)有多行多列(这里是两行两列)，所以该子查询就称为表子查询。'in'连接

-- 2. 查询入职日期是'2006-01-01'之后的员工信息，及其部门信息。分两步

第一步:查询入职日期是'2006-01-01’的员工信息

xxxxxxxxxx
#查询到的数据是一张新表，为了方便下面那行描述，我们称这个新表为临时表
select * from emp where entrydate > '2006-01-01';

第二步:查询这部分员工对应的部门信息。我们需要上面那行根据查询到的临时表(取个别名为e)，使用左外连接，以及emp表的外键字段,查询到dept的部门数据

select e.*,dept.* from (select * from emp where entrydate > '2006-01-01') as e left join dept on e.dept_id = dept.id;

上面那行括号里面的查询语句，就称为子查询

又由于该子查询返回的记录(也叫数据)有多行多列(这里是六行八列)，所以该子查询就称为表子查询。为简化代码长度可使用别名

因为emp表的'陈友谅'对应的dept_id外键为null，如果想查询到这条数据，就需要使用前几节课学的外连接

多表查询-案例练习

-- 准备数据，共三张表。其中两张是上几节课一直用的dept表、emp表，第三张表是下面新建的salgrade表

xxxxxxxxxx
create table salgrade(
    grade int, #薪资等级
    losal int, #最低薪资
    hisal int  #最高薪资
) comment '薪资等级表';
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);
#为dept表新增一条数据
insert into dept (id,name) values (6,'人事部');

-- 1. 查询员工的姓名、年龄、职位、部门信息。使用隐式内连接，内连接展示的是两张表交集存在的数据。操作emp表、dept表

select emp.name,emp.age,emp.job,dept.name from emp,dept where emp.dept_id = dept.id; #连接条件:emp.dept_id = dept.id
select e.name,e.age,e.job,d.name from emp as e,dept as d where e.dept_id = d.id; #如果表使用了别名，那么连接条件也必须使用别名

-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。使用显示内连接。操作emp表、dept表

select emp.name,emp.age,emp.job,dept.name from emp inner join dept on emp.dept_id = dept.id where emp.age < 30;
select e.name,e.age,e.job,d.name from emp as e inner join dept as d on e.dept_id = d.id where e.age < 30;#使用别名，as可省略

-- 3. 查询拥有员工的部门id、部门名称。使用内连接。注意我们人事部是没有员工的。操作emp表、dept表

select dept.id,dept.name from emp,dept where emp.dept_id = dept.id;#共6个部门，按理来说查出来是5个部门有数据，但是有重复的笛卡尔积
select distinct dept.id,dept.name from emp,dept where emp.dept_id = dept.id;#优化上面那行，distinct关键字去重
select distinct d.id,d.name from emp as e,dept as d where e.dept_id = d.id;#别名

-- 4. 查询所有年龄大于40岁的员工，及其所属的部门名称;如果没有员工分配部门，也需要展示出来。使用外连接，外连接展示的是两张表并集存

在的数据。操作emp表、dept表

select emp.*,dept.name from emp left join dept on emp.dept_id = dept.id where emp.age > 40;
select e.*,d.name from emp as e left join dept as d on e.dept_id = d.id where e.age > 40;#别名

-- 5. 查询所有员工的工资等级。操作emp表、salgrade表。两张表的连接关系:emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal

select emp.name,salgrade.grade from emp,salgrade where emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal;#隐式内连接
select e.name,s.grade from emp as e,salgrade as s where e.salary >= s.losal and e.salary <= s.hisal;#别名
select emp.name,salgrade.grade from emp,salgrade where emp.salary between salgrade.losal and salgrade.hisal;#第二种写法

-- 6. 查询'研发部'所有员工的信息及工资等级。操作emp表、salgrade表、dept表。结论:当要联查n张表，至少会有n-1个连接条件，这题的所有连接条件如下

第一个连接条件:emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal 或 emp.salary between salgrade.losal and salgrade.hisal

第二个连接条件:emp.dept_id = dept.id

多个连接条件中间使用and分隔，即我们同时写上面两个连接条件，要使用and作为分割。为了让代码容易看懂，可使用括号

查询条件:dept.name = '研发部'。查询条件和连接条件也是用and分隔

隐式内连接

select emp.*,salgrade.grade from emp,dept,salgrade where (emp.dept_id = dept.id) and (emp.salary >= salgrade.losal and emp.salary) and (dept.name = '研发部');

-- 当我们代码长度很长时，选中这条代码，右键，点击Reformat Code，即可格式化代码，代码就会分行显示啦。例如把上面那条查询语句格式化

select emp.*, salgrade.grade
from emp,
     dept,
     salgrade
where (emp.dept_id = dept.id)
  and (emp.salary >= salgrade.losal and emp.salary)
  and (dept.name = '研发部');
select e.*,s.grade from emp as e,dept as d,salgrade as s where (e.dept_id = d.id) and (e.salary >= s.losal and e.salary) and (d.name = '研发部');#别名

-- 7. 查询'研发部'员工的平均工资。操作emp表、dept表。这道题的这两张表的连接条件:emp.dept_id = dept.id。使用avg聚合函数求平均数 -- 当同时有连接条件和查询条件，需要注意格式:where + 连接条件 + and + 查询条件 或 where + (连接条件) + and + (查询条件)

select avg(emp.salary) from emp,dept where (emp.dept_id = dept.id) and (dept.name = '研发部');
select avg(e.salary) from emp as e,dept as d where (e.dept_id = d.id) and (d.name = '研发部');#别名

-- 8. 查询工资比'灭绝'高的员工信息。分两步 -- 第一步:查询'灭绝'的薪资

select salary from emp where name = '灭绝';#查询到的值是8500

-- 第二步:查询工资比'灭绝'高的员工信息

select * from emp where salary > 8500;

有没有想到前几节课学的子查询，我来帮你复习，如下

如何将上面两步的的2条语句合并为1条，如下

xxxxxxxxxx
select * from emp where salary > (select salary from emp where name = '灭绝');#由于已经有1个select查询关键字，所以要用括号

括号里面的查询语句，就称为子查询

又由于该子查询返回的记录(也叫数据)只有一条(也叫一行一列)，所以该子查询就称为标量子查询

-- 9. 查询比平均薪资高的员工信息。分两步

-- 第一步:查询员工的平均薪资。使用avg聚合函数

select avg(salary) from emp;#查询到的值为8132.3529

-- 第二步:查询比平均薪资高的员工信息

select * from emp where salary > 8132.3529;

如何将上面两步的的2条语句合并为1条，如下

xxxxxxxxxx
select * from emp where salary > (select avg(salary) from emp);#由于已经有1个select查询关键字，所以要用括号

括号里面的查询语句，就称为子查询

又由于该子查询返回的记录(也叫数据)只有一条(也叫一行一列)，所以该子查询就称为标量子查询

-- 10. 查询低于本部门平均工资的员工信息。分两步

-- 第一步:查询指定部门平均薪资

select avg(emp.salary) from emp where emp.dept_id = 1;#例如求部门1的平均薪资，查询到的值是9800。同理查其他部门的平均薪资，如下
select avg(emp.salary) from emp where emp.dept_id = 2;
select avg(emp.salary) from emp where emp.dept_id = 3;
select avg(emp.salary) from emp where emp.dept_id = 4;
select avg(emp.salary) from emp where emp.dept_id = 5;
select avg(emp.salary) from emp where emp.dept_id = 6;#第六个部门没有员工，所以查不到平均薪资

-- 第二步:查询低于本部门平均工资的员工信息,注意要使用别名

select * from emp as e2 where e2.salary < (select avg(emp.salary) from emp where emp.dept_id = e2.dept_id);

-- 为了直观的验证是否是低于本部门的平均工资，我们可以把本部门的平均工资也查出来，并把查询到的平均工资字段取一个别名，如下,

select *,(select avg(emp.salary) from emp where emp.dept_id = e2.dept_id) as '平均薪资' from emp as e2 where e2.salary < (select avg(emp.salary) from emp where emp.dept_id = e2.dept_id);

-- 当我们代码长度很长时，选中这条代码，右键，点击Reformat Code，即可格式化代码，代码就会分行显示啦。例如把上面那条查询语句格式化，如下。查询效果是一样的

select *, (select avg(emp.salary) from emp where emp.dept_id = e2.dept_id) as '平均薪资'
from emp as e2
where e2.salary < (select avg(emp.salary) from emp where emp.dept_id = e2.dept_id);

-- 11. 查询所有的部门信息，并统计部门的员工人数。分两步

-- 第一步:查询所有的部门信息

select id,name from dept;

-- 第二步:统计部门的员工人数。使用聚合函数count

xxxxxxxxxx
#例如统计1号部门的员工人数，查询结果为5。由此我们可以写出下一行代码
select count(*) from emp where dept_id = 1;
#查询出的结果:总人数是16。我们整理一下让它一个部门一个部门显示
select count(*) from emp as e,dept as d where e.dept_id = d.id;
#name后面的括号里面的是子查询语句，注意要取别名
select d.id,d.name,(select count(*) from emp as e where e.dept_id = d.id) as '人数' from dept as d;

-- 12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称

-- 准备数据，在最上面的20~40行有建表语句、插入数据语句，自行去运行一下。操作student表、course表、student_course表

联查三张表，至少2个连接条件，分别如下。注意使用连接条件的作用是消除笛卡尔积

第一个连接条件:student.id = student_course.studentid

第二个连接条件:course.id = student_course.courseid

select s.name,s.no,c.name from student as s,student_course as sc,course as c where (s.id = sc.studentid) and (sc.courseid = c.id);

建议上面那行使用别名(已使用)，简化代码长度