【MySQL学习】多表查询必会知识

SQL语句+Navicat 多表查询

这几天面试问了很多SQL的问题，之前本科学过，但是由于长时间不用导致已经忘掉一些重要的知识点，今天开始慢慢把他们补起来，哈哈哈哈哈。加油鸭！
PS：今天拒掉了一个很喜欢的主管的offer，真觉得好惋惜，但是为了未来发展还是想做自己喜欢的，职场还是很无情的。

多表查询

• 内连接
• 外连接
• 子查询

数据准备

• 学生表

create table t_student (
id int(10) DEFAULT NULL,
tname varchar(20) DEFAULT NULL,
gender varchar(10) DEFAULT NULL,
age int(4) DEFAULT NULL,
classno int(11) DEFAULT NULL);

insert into t_student(id,tname,gender,age,classno)
values (1001,'Alicia Florric','Female',33,1),
(1002,'Kalinda Sharma','Female',31,1),
(1003,'Cary Agos','Male',27,1),
(1004,'Diane Lockhart','Female',43,2),
(1005,'Eli Gold','Male',44,3),
(1006,'Peter Florric','Male',34,3),
(1007,'Will Gardner','Male',38,2),
(1008,'Jackquiline Florriok','Male',38,4),
(1009,'Zach Florriok','Male',14,4),
(1010,'Grace Florriok','Male',12,4);

• 班级表

create table t_class(
classno int(11) default null,
cname varchar(20) default null,
loc varchar(40) default null,
advisor varchar(20) default null);

insert into t_class(classno,cname,loc,advisor)
values
(1,'class_1','loc_1','advisor_1'),
(2,'class_2','loc_2','advisor_2'),
(3,'class_3','loc_3','advisor_3'),
(4,'class_4','loc_4','advisor_4');

• 成绩表

create table t_score(
stuid int(11),
Chinese int(4),
English int(4),
Math int(4),
Chemistry int(4),
Physics int(4));

insert into t_score (stuid,Chinese,English,Math,Chemistry,Physics)
values
(1001,90,89,92,83,80),
(1002,92,98,92,93,90),
(1003,79,78,82,83,89),
(1004,89,92,91,92,89),
(1005,92,95,91,92,89),
(1006,90,91,92,94,92),
(1007,91,90,83,88,93),
(1008,90,81,84,86,98),
(1009,91,84,85,86,93),
(1010,88,81,82,84,99);

内连接

1. 自连接
   顾名思义，自身与自身进行连接
   【示例】查询学生“Alicia Florric”所在班级的所有学生

自连接-where;
select ts1.id,ts1.tname,ts1.classno
from t_student as ts1,t_student as ts2
where ts1.classno = ts2.classno and ts2.tname = 'Alicia Florric';

自连接-ASNI;
select ts1.id,ts1.tname,ts1.classno
from t_student as ts1 INNER JOIN t_student as ts2
on ts1.classno = ts2.classno and ts2.tname = 'Alicia Florric';

2. 等值连接
就是关键词on后的条件通过等于来实现等值条件运算，说白了就是on一个＝
【示例】查询学生编号，姓名，性别，年龄，班级号，班级名称、位置和班主任信息。

等值连接-where;
select s.id,s.tname,s.gender,s.age,s.classno,c.cname,c.loc,c.advisor
from t_student as s,t_class as c where s.classno = c.classno;

等值连接-join;
select s.id,s.tname,s.gender,s.age,s.classno,c.cname,c.loc,c.advisor
from t_student as s inner join t_class as c
on s.classno = c.classno;

【示例】查询学生编号，姓名，性别，年龄，班级号，班级名称、位置和班主任信息。

select st.id,st.tname,st.gender,st.age,st.classno,c.cname,c.loc,c.advisor,
sc.Chinese+sc.English+sc.Math+sc.Chemistry+sc.Physics as total
from t_student as st,t_class as c,t_score as sc
where st.classno = c.classno and st.id = sc.stuid;
连接三个表

select st.id,st.tname,st.gender,st.age,st.classno,c.cname,c.loc,c.advisor,
sc.Chinese+sc.English+sc.Math+sc.Chemistry+sc.Physics as total
from t_student as st inner join t_class as c on st.classno = c.classno
inner join t_score as sc on st.id = sc.stuid;

3. 不等连接
   就是内连接使用例如>,>=,<,<=,!=等符号运算
   【示例】查询和“Alicia Florric"不在一个班级且年龄大于他的学生编号，姓名，性别，年龄，班级号，班级名称、位置和班主任信息。

select st1.id,st1.tname,st1.gender,st1.age,st1.classno,
c.cname,c.loc,c.advisor,
sc.Chinese+sc.English+sc.Math+sc.Chemistry+sc.Physics as total
from 
t_student as st1, t_student as st2,t_class as c,t_score as sc
where 
st1.classno != st2.classno
and st2.tname ='Alicia Florric' 
and st1.age>st2.age and 
st1.classno = c.classno and st1.id = sc.stuid;

select st1.id,st1.tname,st1.gender,st1.age,st1.classno,
c.cname,c.loc,c.advisor,
sc.Chinese+sc.English+sc.Math+sc.Chemistry+sc.Physics as total
from 
t_student as st1, t_student as st2,t_class as c,t_score as sc
where 
st1.classno != st2.classno
and st2.tname ='Alicia Florric' 
and st1.age>st2.age and 
st1.classno = c.classno and st1.id = sc.stuid;

外连接
外连接是会返回所操作表中至少一个表的所有数据记录

• 左外连接
  左连接是包括left outer子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则相关联的结果中均为空值。

构造下数据
insert into t_student values
(1011,'Maia Rindell','Female',33,5);
#############################################
select s.tname,c.cname,c.loc,c.advisor 
from t_student as s left outer join t_class as c
on s.classno = c.classno;

• 右外连接
  一样的道理

insert into t_class(classno,cname,loc,advisor)
values(6,'class_6','loc_6','advisor_6');
###########################################
select s.tname,c.cname,c.loc,c.advisor 
from t_student as s right outer join t_class as c
on s.classno = c.classno;

子查询

• 带比较符的子查询
• 带关键字的子查询
• 带exists的子查询
• 带关键字any的子查询
• 带关键字all的子查询

数据准备

• 员工表

create table t_employee(
id int(4),
name varchar(20),
gender varchar(6),
age int(4),
salary int(6),
deptno int(4));

insert into t_employee(id,name,gender,age,salary,deptno)
values
(1001,'Alicia Florric','Female',33,10000,1),
(1002,'Kalinda Sharma','Female',31,9000,1),
(1003,'Cary Agos','Male',27,8000,1),
(1004,'Eli Gold','Male',44,20000,2),
(1005,'Peter Florric','Male',34,30000,2),
(1006,'Diane Lockhart','Female',43,50000,3),
(1007,'Maia Rindell','Female',27,9000,3),
(1008,'Will Gardner','Male',36,9000,3),
(1009,'Jackquiline Florriok','Female',57,7000,4),
(1010,'Zach Florriok','Male',17,5000,5),
(1011,'Grace Florriok','Female',14,4000,5);

• 工资待遇表

create table t_slevel(
id int(4),
salary int(6),
level int(4),
description varchar(20));

insert into t_slevel(id,salary,level,description)
values
(1,3000,1,'初级'),
(2,7000,2,'中级'),
(3,3000,3,'高级'),
(4,3000,4,'特级'),
(5,3000,5,'高管');

• 部门表

create table t_dept(
deptno int(4),
deptname varchar(20),
product varchar(20),
location varchar(20));

insert into t_dept
values
(1,'develop department','pivot_gaea','west_3'),
(2,'test department','sky_start','east_4'),
(3,'operate department','cloud_4','south_4'),
(4,'maintain department','fly_4','north_5');

• 带比较符的子查询

【案例】查询薪资为高级的员工信息

select * from t_employee
where salary =
(select salary from t_slevel where level = 3);

【案例】查询哪些部门没有年龄为33的员工

select * from t_dept 
where deptno !=
(select deptno from t_employee where age =33);

• 带关键字in的子查询

【案例】查询t_employee表中deptno在t_dept中出现过的信息。

select * from t_employee
where deptno in 
(select deptno from t_dept);

【案例】查询t_employee表中deptno不在t_dept中出现过的信息。

select * from t_employee
where deptno not in 
(select deptno from t_dept);

• 带关键字exists的子查询

【案例】查询t_dept表中是否存在deptno为4的部门，如果存在就查询年龄大于40 的数据

select * from t_employee
where age >40  and
exists (select deptname from t_dept where deptno = 4)

【案例】查询t_dept表中是否存在deptno为4的部门，如果存在就查询年龄大于40 的数据

select * from t_employee
where age >40  and
exists (select deptname from t_dept where deptno = 6);

• 带关键字any的子查询

【案例】查询学生表中哪些可以获得奖学金。

加一张表；
create table t_scholarship(
id int(4),
score int(4),
level int(4),
description varchar(20));

insert into t_scholarship
VALUES (1,430,3,'三等奖学金'),
(2,440,2,'二等奖学金'),
(3,450,1,'一等奖学金');

select st.id,st.tname,
sc.Chinese+sc.English+sc.Math+sc.Chemistry+sc.Physics as total
from t_student as st,t_score as sc
where st.id = sc.stuid
and st.id in 
(select stuid from t_score 
where Chinese+English+Math+Chemistry+Physics >= any 
(select score from t_scholarship));

• 带关键字all的子查询

【案例】查询学生表中哪些可以获得一等奖学金。

select st.id,st.tname,
sc.Chinese+sc.English+sc.Math+sc.Chemistry+sc.Physics as total
from t_student as st,t_score as sc
where st.id = sc.stuid
and st.id in 
(select stuid from t_score 
where Chinese+English+Math+Chemistry+Physics >= all 
(select score from t_scholarship));

综合示例