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);
内连接
- 自连接
顾名思义,自身与自身进行连接
【示例】查询学生“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;
- 不等连接
就是内连接使用例如>,>=,<,<=,!=等符号运算
【示例】查询和“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));
综合示例