杭州多测师10期--课程笔记--多表查询及题目
已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）1：dept表中有4条记录：
部门号(dept1)部门名称(dept_name )
101 财务
102 销售
103 IT技术
104 行政
2：emp表中有6条记录：
员工号 员工姓名 年龄 入职时间 收入 部门号对应字段名称为： (sid name age worktime_start incoming dept2)
1789 张三 35 1980/1/1 4000 101
1674 李四 32 1983/4/1 3500 101
1776 王五 24 1990/7/1 2000 101
1568 赵六 57 1970/10/11 7500 102
1564 荣七 64 1963/10/11 8500 102
1879 牛八 55 1971/10/20 7300 103
1.列出每个部门的平均收入及部门名称;
emp incoming
deptdept_name
avg
group by
selectavg(incoming ),dept_name from dept inner joinempondept.dept1=emp.dept2.
方法一：selectavg(incoming ),dept_name from (select * from dept left joinempondept.dept1=emp.dept2)c group by dept_name;
方法二：select avg(incoming),dept_name from dept leftJOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name ;
2.财务部门的收入总和；
dept "财务"
emp incoming
sum
selectsum(incoming ),dept_name from dept inner joinempondept.dept1=emp.dept2where dept_name="财务" ;
方法一：select sum(incoming) from (select *fromdept,emp where dept.dept1=emp.dept2)c where dept_name='财务';
方法二：selectsum(incoming ) from dept inner joinempondept.dept1=emp.dept2where dept_name="财务" ;
方法三：select sum(incoming) fromemp where dept2=(selectdept1fromdeptwheredept_name="财务");
3.It技术部入职员工的员工号
emp sid
dept it技术部门
name ，sid
方法一：select emp.sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name="IT技术 ";
方法二：select sid from (select * from dept,emp where dept.dept1=emp.dept2)c where dept_name='IT技术';
方法三：selectsidfromempwhere dept2=(selectdept1fromdept where dept_name="IT技术") ;
方法四：selectsid fromdept,empwhere dept1=dept2anddept_name="IT技术" ;
4.财务部门收入超过2000元的员工姓名
emp incoming>2000
dept财务
方法一：SELECT name,incoming FROM Dept INNER JOIN Emp on Dept1=DEPT2 WHERE incoming>2000 AND Dept_name="财务";
方法二：SELECT emp.name fromempwhere dept2=(select dept1 from dept where dept_name='财务') and incoming>2000;
方法三：select name from (select * from dept,emp where dept.dept1=emp.dept2)c where incoming>2000 and dept_name= '财务';
方法四：select name from (select*from dept inner join emp on dept1=dept2 where incoming>2000)s where dept_name='财务'
5.找出销售部收入最低的员工的入职时间；
emp
dept
max
方法一：select name,woektime_start from (select *,min(incoming) from dept inner join emp on dept1=dept2 group by dept_name)s where dept_name='销售'
方法二：select woektime_start FROM emp INNER JOIN dept on dept.dept1=emp.dept2 whereincoming =(select min(incoming) from emp INNER JOIN dept on emp.dept2=dept.dept1 where dept.dept_name="销售")and dept_name="销售";
方法三：（这种方法可以，弊端在与有重复的数据，只显示一条）
selecta.dept_name,b.sid,b.name,b.woektime_startfrom dept ajoin emp b ona.dept1 =b.dept2 where a.dept_name = "销售"ORDER BY b.incoming asc LIMIT 1;
6.找出年龄小于平均年龄的员工的姓名，ID和部门名称
思路：条件n：ame，sid ，dept_name
方法一:SELECT name,sid,dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 where age< (SELECT AVG(age) FROM emp);
方法二：
select sid,name,dept_name from (select * from emp,dept where dept1=dept2)s where age<(select avg(age)from emp);
7.列出每个部门收入总和高于9000的部门名称
条件： groupby dept_name
sum (incoming) >9000
结果：dept_name
方法一：
select s.dept_name from (select a.dept_name,sum(b.incoming) as k from dept a join emp b on a.dept1 = b.dept2GROUP BYa.dept_name HAVING k >9000)s;
方法二：
SELECT dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2GROUP BY dept_name having sum(incoming)>90000;
方法三：
SELECT dept_name FROM
( SELECT sum( incoming ) a, dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 GROUP BY dept_name ) b
WHERE a > 9000;
方法四：SELECT dept_name from (SELECT sum(incoming) as s,dept2 from emp GROUP BY dept2 having s>9000)t1on dept.dept1=t1.dept2;
8.查出财务部门工资少于3800元的员工姓名
dept_name =财务 dept
incoming<3800 emp
name
方法一：
SELECT name FROM dept INNER JOIN emp on dept.dept1=emp.dept2where incoming<3800and dept_name="财务";
方法二：
select name from emp where incoming<3800 anddept2=(SELECT dept1 from dept WHERE dept_name='财务');
方法三
select name from (select*from dept,emp where dept1=dept2)s where incoming<3800 and dept_name="财务"
9.求财务部门最低工资的员工姓名；
条件
min
dept表 dept_name=财务
emp 表 min（ incoming ）
结果：员工姓名name
方法一：
select namefrom dept inner join emp on dept.dept1=emp.dept2 where
dept2=(select dept1 from dept where dept_name="财务" ) and incoming=(select min(incoming) from emp ,dept where emp.dept2=dept.dept1 anddept_name="财务");
方法二：
selectnamefrom emp ,deptwheredept1=dept2anddept_name="财务"ORDER BY incoming ascLIMIT 1
10.找出销售部门中年纪最大的员工的姓名
条件：dept表 dept_name="销售"
emp max（age）
结果：name
方法一:select name "姓名", age "年龄" from dept,emp where dept.dept1=emp.dept2 and
age=(select max(age) from dept,emp where dept.dept1=emp.dept2 and dept_name="销售")and dept_name="销售";
方法二：（缺陷是多个同样数据就显示一个）
SELECT name,agefrom emp INNER JOIN dept on emp.dept2=dept.dept1WHERE dept_name="销售"ORDER BYagedescLIMIT 0,1;
11.求收入最低的员工姓名及所属部门名称：
条件：emp min（incoming ）
结果：name，dept_name
方法一：
SELECT NAME,dept_name FROM dept inner joinempondept.dept1=emp.dept2 WHERE incoming=(SELECT MIN(incoming) FROM emp );
方法二：
SELECT name ,dept_name FROM(SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2)a WHERE incoming =( SELECT MIN(incoming) FROM emp);
方法三：SELECT name,dept_name fromemp left join dept on emp.dept2=dept.dept1 where incoming=(SELECT min(incoming) from emp left join dept on emp.dept2=dept.dept1);
12.求李四的收入及部门名称
条件：emp name=“李四”
结果：incoming dept_name
方法一：
SELECT incoming,dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2where name="李四"
方法二：
select name,dept_name from(select * from dept,emp where dept.dept1=emp.dept2)c where name='李四';
方法三：select incoming,dept_name from (select*from dept,emp where dept1=dept2)s where name="李四";
13.求员工收入小于4000元的员工部门编号及其部门名称
条件：
emp incoming<4000
结果：dept1 ,deptname
方法一：select dept1,dept_name from(select * from dept,emp where dept.dept1=emp.dept2)cwhere incoming<4000;
方法二：select dept1,dept_name from emp left join dept on emp.dept2=dept.dept1 where incoming<4000;
方法三：
select dept1,dept_name from deptwheredept1 in (selectdept2fromempwhereincoming<4000) ;
14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
方法一：（缺陷：重复就显示一个）
SELECT name "姓名",incoming "收入",dept_name"部门" from(select * from dept,emp where dept.dept1=emp.dept2 order by incoming desc) b group bydept_nameORDER BYincoming desc;
方法二：
SELECT name "姓名",incoming "收入",dept_name"部门" from (select * from dept,emp where dept.dept1=emp.dept2)a where
(a. incoming ,a.dept_name )in (select max(incoming),dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_nameorder by incoming desc) order by incoming desc;
方法三:
selectb.name,a.dept_name, b.incomingfrom(select max(incoming) as c,dept_name from dept, empwheredept1=dept2 groupby dept_name)a LEFT JOIN ( select* fromemp ,deptwheredept1=dept2)bona.dept_name=b.dept_nameanda.c =b.incoming orderbyb.incoming desc;
15.求出财务部门收益最高的俩位员工的姓名，工号，收益
条件： dept dept_name="财务" max（incoming） limit 2
结果 ：emp name sid incoming
方法一：select name,sid,incoming from emp inner join dept on dept1=dept2 where dept_name='财务' order by incoming desc limit 2;
方法二：select * from (select name,sid,incoming from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务" order by incoming desc)as s where sid limit 0,2
方法三: select a.dept1,a.dept_name,b.name,b.incoming from dept a ,emp b wherea.dept1 = b.dept2and a.dept_name ='财务'ORDER BYb.incoming desc LIMIT 2;
16.查询财务部低于平均收入的员工号与员工姓名：（按所有的平均薪资）
条件： dept dept_name=“财务” avg（incoming） >
结果：sid name
方法一：
select sid,name fromempwhere incoming<(select avg(incoming)from emp) and dept2=(select dept1 from dept where dept_name='财务');
方法二：
SELECT sid,name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE incoming<(SELECT avg(incoming) FROM emp)and dept_name="财务";
备注（在多表中求得平均值）SELECT sid,name fromemp left join dept on emp.dept2=dept.dept1 where dept_name="财务" and incoming<(SELECT avg(incoming) from emp left join dept on emp.dept2=dept.dept1)
方法三：
select sid,namefrom (select * from dept,emp where dept.dept1=emp.dept2)cwhere incoming<(select avg(incoming) from emp) and dept_name='财务';
17.列出部门员工数大于1个的部门名称；
条件：dept count（name）
结果：dept_name
方法一：select dept_name from dept where dept1 in(select dept2 from emp group by dept2 having count(dept2)>1)
方法二：SELECT DISTINCT(dept_name) FROM empINNER JOIN dept ON emp.dept2 = dept.dept1WHERE (SELECT count(name) from emp)>1;
方法三：SELECT dept_name from(SELECTdept_name,count(name)s fromdept INNER JOIN emp on dept1=dept2 GROUP BY dept_name having s>1)d
18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
条件：emp incoming<=750 and incoming>3000
结果：age ,dept_name
方法一：select age, dept2 from emp where incoming >3000 and incoming<=7500;
方法二：
select age,dept2from (select * from dept,emp where dept.dept1=emp.dept2)cwhere 7500>=incoming and incoming>3000;
方法三：SELECT name,age,dept1FROMdept inner JOIN emp on dept.dept1=emp.dept2 WHERE incoming BETWEEN 3000 and 7500andincoming!=3000;
错误写法：
SELECT name,age,dept1FROM dept inner JOIN emp on dept.dept1=emp.dept2 WHERE incoming BETWEEN 3000 and 7500;（包含3000了）
19.求入职于20世纪70年代的员工所属部门名称；
条件：20世纪 表示1900-1999内， 70年代 ：大于等于1970~小于1980 woerktime =“197%”
结果： dept dept_name
方法一：select dept_name from emp left join dept on emp.dept2=dept.dept1 wherewoektime_start >="19700101" and woektime_start<"19800101";
方法二：SELECT name,dept_name FROMdept RIGHT JOIN emp on dept.dept1=emp.dept2WHERE worktime BETWEEN "1970-01-01" and "1979-12-31";
方法三：select dept_name "部门"from dept,emp wheredept.dept1=emp.dept2 andwoektime_start like "197%" ;
方法四：select dept_name from emp inner join dept on dept1=dept2 wherewoektime_start >="19700101" and woektime_start<="19791231";
20.查找张三所在的部门名称；
条件： emp name=“张三”
结果：dept dept_name
方法一：
select dept_name from emp inner join dept on dept1=dept2 where name='张三';
方法二：
select dept_name from dept where dept1=(select dept2 from emp where name='张三');
方法三：
select dept_name "部门" from dept,emp where dept2=dept1 and name="张三";
21.列出每一个部门中年纪最大的员工姓名，部门名称；
条件：group by dept_name max(age)
结果：name，dept_name
方法一：sel ect name "姓名",dept_name "部门名" from dept,emp where dept1=dept2 and
(dept_name,age) in (select dept_name,max(age) from dept,emp where dept1=dept2 group by dept_name )；
方法二：
selectb.name,a.dept_name, b.incomingfrom(select max(age) as c,dept_name from dept, empwheredept1=dept2 groupby dept_name)a LEFT JOIN ( select* fromemp ,deptwheredept1=dept2)bona.dept_name=b.dept_nameanda.c =b.age
方法三：（是一种方法，当出现多条相同数据，就显示一条）
select name,dept_name from (select * from emp inner join dept on dept.dept1=emp.dept2 order by age desc) a group by dept_name ;
22.列出每一个部门的员工总收入及部门名称；
条件: groupby dept_name
结果;sum(incoming) dept_name
方法一：SELECT sum(incoming),dept_name FROMdept leftJOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name
方法二：select sum(incoming),dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept1;
方法三：（不合理的写法，答案正确）
SELECT sum(incoming),dept_name FROM dept iNNER JOIN emp on dept.dept1=emp.dept2 WHERE (SELECT sum(incoming) FROM emp ) GROUP by dept_name;
方法四：select dept_name,sum(incoming)from (select * from dept,emp where dept.dept1=emp.dept2)cgroup by dept_name；
23.列出部门员工收入大于7000的员工号，部门名称；
条件： dept dept_name
emp incoming>7000 sid
结果： sid dept_name
方法一：SELECT sid,dept_name FROM dept left JOIN emp on dept1=dept2 where incoming>7000
方法二：select a.sid,a.dept_name from(select * from emp inner join dept on emp.dept2=dept.dept1 where incoming>7000) a;
方法三：
select sid,dept_namefrom (select * from dept,emp where dept.dept1=emp.dept2)c where incoming>7000;
24.找出哪个部门还没有员工入职；
条件：dept_name name或sid 判断
结果：dept_name
方法一：
select dept_namefrom dept left join emp on dept.dept1=emp.dept2 group by dept1 having (count(sid)=0)
方法二：select * from dept where dept1 not in ( select dept2 from emp);
方法三：select dept_name fromemp right join dept on dept1=dept2 where name is null;
方法四：
select dept_name from dept left join emp on dept.dept1=emp.dept2where name is NULL;
方法五：SELECT dept_name FROM (SELECT * FROM emp RIGHT JOIN dept ON emp.dept2 = dept.dept1)a WHEREsid is NULL;
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
条件：二次排序 orderby desc从大到小 早到晚asc
结果：所有信息
方法一：
SELECT * fromdept left join emp on dept.dept1=emp.dept2 ORDER BY dept1 desc,woektime_start asc ;
方法二：（部门编号104不显示，内连接方法）
select * fromdept a ,emp b where a.dept1 = b.dept2 ORDER BYa.dept1 desc,b.woektime_start asc;
方法三：SELECT * from empORDER BY dept2 DESC ,woektime_start asc;
26.求出财务部门工资最高员工的姓名和员工号
条件： dept_name ="财务" max（incoming）
结果：name，sid
方法一：SELECT name,sidfrom dept left join emp on dept.dept1=emp.dept2 where incoming=(SELECT max(incoming) from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务")and dept_name="财务" ;
方法 二：
select name, sid from emp inner join dept on dept1= dept2 where dept_name='财务' order by incoming desc limit 1
方法三：SELECT name,sid FROM dept,emp WHERE dept.dept1=emp.dept2 AND (SELECT max(incoming) from (SELECT incoming from dept a ,emp b where a.dept1=b.dept2 and dept_name="财务")s )=incominganddept_name="财务";
方法四：（缺陷有重复数据）
select name,sid FROM dept d INNER JOIN emp e on d.dept1=e.dept2 WHERE dept_name="财务" AND incoming in(SELECT MAX(incoming) FROM emp GROUP BY dept2);#
方法五：
SELECT name,dept1FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 WHERE dept_name='财务'ORDER BY incoming DESCLIMIT 0,1;
27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
条件：emp incming max ( age)
结果：name dept_name
方法一：
select name,dept_name from dept left join emp on dept.dept1=emp.dept2 where age=(SELECT max(age) from dept left join emp on dept.dept1=emp.dept2 WHERE incoming between 7500 and 8500 )andincoming between 7500 and 8500 ;
方法二：
SELECT name,dept_name from (SELECT * from emp INNER JOIN dept on emp.dept2=dept.dept1 ORDER BY age desc) a where incoming>=7500 and incoming<=8500 LIMIT 1;
方法三：
SELECT name,dept_name FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b WHERE age=(SELECT max(age) FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b)
方法四：
SELECT name,dept_name FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b ORDER BY age desc LIMIT 1
建表语句：
===================================
create table student(
stu_no int,
stu_name varchar(10),
sex char(1),
age int(3),
edit varchar(20) )
DEFAULTcharset=utf8;
insert into student values
(1,'wang','男',21,'hello'),
(2,'小明','女',22,'haha2'),
(3,'hu','女',23,'haha3'),
(4,'li','男',25,'haha4');
create table course(
c_no int,
c_name varchar(10)
)
DEFAULTcharset=utf8;
insert into course values
(1,'计算机原理'),
(2,'java'),
(3,'c'),
(4,'php'),
(5,'py');
#roptable sc;
create table sc(
sc_no int,
stu_no int,
c_no int,
score int(3))
DEFAULTcharset=utf8;
insert into sc values
(1,1,1,80),
(2,2,2,90),
(3,2,1,85),
(4,2,3,70),
(5,2,4,95),
(6,2,5,89);
============================================
select * from student ;
select * from course ;
select* from sc ;
============================================
学生表
D:\0-IT\2\有道云\weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4\38a820d9954548d1b5fd8e4f5f54d0f5\cb4ab233a4e7.png
课程表
D:\0-IT\2\有道云\weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4\56770bf97ba54ab2af5dfb4295109091\e2caa9bd3ae2.png
选课表
D:\0-IT\2\有道云\weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4\a68b8c1c1d374f5da19b7daaf8c1af5c\0c0a9d2340d2.png
Student学生表（学号、姓名、性别、年龄、编辑） asa
Course课程表（课程编号、课程名称） as b
sc选课表（选课编号、学号、课程编号、成绩）as c
============================================
题目：
（1）写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名
（2）写一个SQL语句，查询“小明”同学选修的课程名称
（3）写一个SQL语句，查询选修了5门课程的学生学号和姓名
============================================
三表连接：
方法一：三表隐藏内连接
格式：select*from 表1 , 表2 ，表3 where表1.关联的字段=表3.关联字段and 表2.关联字段=表3.表3字段
select*fromstudent as a , course as b , scasc wherea.stu_no=c.stu_no and b.c_no=c.c_no
方法二：三表普通内连接
格式：
select*from表1 INNER JOIN表3 on 表1.关联的字段=表3.关联字段 inner join 表2on 表2.关联字段=表3.表3字段
select*fromstudent as a INNER JOINscasc on a.stu_no=c.stu_noinner joincourse asbon b.c_no=c.c_no
方法三：三表普通左连接
格式：select*from表1 left JOIN表3 on 表1.关联的字段=表3.关联字段 left join 表2on 表2.关联字段=表3.表3字段
select*fromstudent as a left JOINscasc on a.stu_no=c.stu_noleft joincourse asbon b.c_no=c.c_no
方法四：三表普通右连接
格式：select*from表1 rightJOIN表3 on 表1.关联的字段=表3.关联字段 right join 表2on 表2.关联字段=表3.表3字段
select*fromstudent as a right JOINscasc on a.stu_no=c.stu_noright joincourse asbon b.c_no=c.c_no
方五：先合两表，在两表合并成一表与第三个表合并
selects.stu_no ,s.stu_name from ( selecta.stu_no ,stu_name,c_nofromstudent a,sc c wherea.stu_no=c.stu_no )s,courseas bwheres.c_no=b.c_no
============================================
解题思路：
（1）写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名
条件： c_name =“计算机原理” course表
结果：stu_no ， name student表
方法一：
SELECT a.stu_no, a.stu_name from student a join sc c on a.stu_no = c.stu_no join course b on c.c_no = b.c_nowhere b.c_name = "计算机原理"
（2）写一个SQL语句，查询“小明”同学选修的课程名称
方法一：
SELECT b.c_name FROM student a ,course b , sc cWHERE a.stu_no=c.stu_noand b.c_no=c.c_noand a.stu_name="小明";
方法二：
select c.stu_name,b.c_namefrom sc ajoincoursebon a.c_no = b.c_nojoinstudent3 con a.sc_no = c.stu_nowherec.stu_name = '小明' ;
方法三：
（3）写一个SQL语句，查询选修了5门课程的学生学号和姓名
selecta.stu_no,a.stu_name from student1as a,course as b, scas c wherea.stu_no= c.stu_no and b.c_no=c.c_no
group by a.stu_no having count(c_name)=5;
页:
[1]