mysql查询入职时间比上级早的语句_数据库面试题（一）子查询及面试题练习

矫情吗；* 2022-10-27 13:56 3阅读 0赞

一、子查询的基本理论

1.子查询概念：

()

子查询也叫做内部查询，包含子查询的语句称为外部查询或主查询

2.子查询的分类

非相关子查询  ---  用的最多

子查询的结果作为外部查询的条件

子查询的结果作为表

相关子查询(关联)

依赖于外部查询的数据，外部查询每执行一次，子查询就执行一次

外部查询先查询，在执行子查询

子查询的结果作为列

案例：

![1b6b9e58a94fba31b0365c0f638f5bd2.png][]

如：第一题，建表

drop table if existstb\_lemon\_score;create table tb\_lemon\_score( sname varchar(20), course varchar(20), score tinyint);inserttb\_lemon\_scorevalues('张三', '语文', 71),

('张三', '数学', 75),

('李四', '语文', 76),

('李四', '数学', 90),

('王五', '语文', 81),

('王五', '数学', 100),

('王五', '英语', 90 );

查询：查询出每门课程都大于80分的学生姓名

方式一

select sname,score from tb\_lemon\_score group by sname having min(score)>80;

![bb9b1c2306da67ffdda044b92862061f.png][]

方式二：子查询的结果作为外部查询的条件

先查询出小于80的

select \* from tb\_lemon\_score where sname not in (select sname from tb\_lemon\_score where score<=80);

![6132cae168683675f2e7bc7ed6dc5945.png][]

方式三：子查询的结果作为表

先查询出每个人的最小成绩，结果作为表，在查询大于80分的学生

\--每人的最小成绩

select sname,min(score) 最小成绩 from tb\_lemon\_score group bysname;--在结果中查询

select \* from (select sname,min(score) 最小成绩 from tb\_lemon\_score group by sname) t where t.最小成绩>80;

![e57d0be9bd7e6a1df5e8cd2644b63425.png][]

![1b5c583bdf186b03acaeef79f938f7e6.png][]

方式四：相关子查询(关联)

外部查询先查询，在执行子查询

EXISTS ：运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False

not exists ：EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录：

select \* from tb\_lemon\_score t1 where not exists(select \* from tb\_lemon\_score t2 where t1.sname=t2.sname and t2.score<=80);

![b6d0f81685e82a89e43d91d938d64dc0.png][]

案例2：

\#用sql语句查询所有购入商品为两种或两种以上的购物人--非关联子查询

SELECT sname FROM (SELECT sname,COUNT(merch) FROM shopping\_list GROUP BY sname HAVING COUNT(merch)>=2) aslist;--关联子查询

SELECT DISTINCT sname FROM shopping\_list s1 WHERE EXISTS(SELECT \* FROM shopping\_list s2 WHERE s2.sname=s1.sname GROUP BY s2.sname HAVING COUNT(s2.merch)>=2)

面试题

![bd86ea0a663abf5130a078247b74934a.png][]

1.查询出所有语文成绩超过平均水平的学生姓名、语文成绩

\--先两条链接，1表id == 2表id 查询后只有三条，and 语文成绩大于平均成绩

select s.name,sc.chinese from表1 s,表2 scwhere s.id = sc.id and sc.chinese > (select avg(chinese) from表1);--相当于 inner join on

select s.name,sc.chinese from 表1 s inner join表2 scon s.id = sc.id and sc.chinese > (select avg(chinese) from 表1);

![4b930eb9af668eafceeaf71ca703e327.png][]

2.查询出所有学生的姓名、语文成绩

select s.name,sc.chinese from 学生表1 s left join 成绩表2 sc on s.id = sc.id;

======

\--同上关联子查询，查询结果作为列

select s.name,(select sc.chiinese from 成绩表2 sc where 学生表.id = 成绩表.id ) from 学生表1 s；

![de10b40f9ad3bc7df37cee618c3dca1a.png][]　两条结果都相同

![75b8d80759efaf4918e3b7523d2c5c06.png][]

3.导出所有语文或数学成绩大于90分的学生信息到exce1中

select s.name,sc.chinese,sc.math from学生表1 s,成绩表2 scwhere s.id = sc.id and (sc.math>=90 or sc.chinese>=90);

![84f35a2cd7df79bccbc41bfc03b93890.png][]

\--非相关子查询，结果作为一张表

select s.\*,t.\* from 学生表 s,(select sc.\* from 成绩表 sc where sc.math>90 or sc.chinese>=90) t where s.id = t.id;

![97d3c45b6d21a802ab29327684ccc020.png][]

练习题：

1.建表及数据

\--部门表--CREATE TABLE DEPT( DEPTNO INT PRIMARY KEY, -- 部门编号 DNAME VARCHAR(14) , -- 部门名称 LOC VARCHAR(13) -- 部门地址 ) ;

create table dept ( deptno int primary key, dname varchar ( 14 ), loc varchar ( 13) );insert intodeptvalues(10, 'accounting', 'new york');insert intodeptvalues(20, 'research', 'dallas');insert intodeptvalues(30, 'sales', 'chicago');insert intodeptvalues(40, 'operations', 'boston');--员工表--CREATE TABLE EMP ( EMPNO INT PRIMARY KEY, -- 员工编号 ENAME VARCHAR(10), -- 员工名称 JOB VARCHAR(9), -- 工作 MGR DOUBLE, -- 直属领导编号 HIREDATE DATE, -- 入职时间 SAL DOUBLE, -- 工资 COMM DOUBLE, -- 奖金 DEPTNO INT, -- 部门号 FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));

create table emp (empno int primary key,ename varchar (10),job varchar (9),mgr double,hiredate date,sal double,comm double,deptno int,foreign key (deptno) referencesdept (deptno));insert intoempvalues(7369, 'smith', 'clerk', 7902, '1980-12-17', 800, null, 20);insert intoempvalues(7499, 'allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30);insert intoempvalues(7521, 'ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30);insert intoempvalues(7566, 'jones', 'manager', 7839, '1981-04-02', 2975, null, 20);insert intoempvalues(7654, 'martin', 'salesman', 7698, '1981-09-28', 1250, 1400, 30);insert intoempvalues(7698, 'blake', 'manager', 7839, '1981-05-01', 2850, null, 30);insert intoempvalues(7782, 'clark', 'manager', 7839, '1981-06-09', 2450, null, 10);insert intoempvalues(7788, 'scott', 'analyst', 7566, '1987-07-13', 3000, null, 20);insert intoempvalues(7839, 'king', 'president', null, '1981-11-17', 5000, null, 10);insert intoempvalues(7844, 'turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30);insert intoempvalues(7876, 'adams', 'clerk', 7788, '1987-07-13', 1100, null, 20);insert intoempvalues(7900, 'james', 'clerk', 7698, '1981-12-03', 950, null, 30);insert intoempvalues(7902, 'ford', 'analyst', 7566, '1981-12-03', 3000, null, 20);insert intoempvalues(7934, 'miller', 'clerk', 7782, '1982-01-23', 1300, null, 10 );

2、根据emp和dept表完成下列作业：

\-- 1.列出至少有三个员工的所有部门和部门信息。

SELECT dept.\* from dept WHERE dept.DEPTNO IN(SELECT DEPTNO from emp GROUP BY emp.DEPTNO HAVING COUNT(emp.DEPTNO)>=3);

![e447b0806ab35e195fa0c3c3266979c7.png][]

\-- 2.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称

SELECT e1.EMPNO, e1.ENAME,d.DNAME FROMemp e1,dept dWHERE e1.HIREDATE < (SELECT e2.HIREDATE FROM emp e2 WHERE e2.empno=e1.MGR)AND d.DEPTNO=e1.DEPTNO;

![d45c0d5b9eff34b872d5147de94d70e1.png][]

\-- 3.列出职位为“CLERK”的姓名和部门名称，部门人数：

SELECT emp\_name,dept\_name,emp\_num FROM(SELECTemp.ENAME emp\_name,

dept.DNAME dept\_name,

(SELECT COUNT(emp.EMPNO) FROM emp WHERE emp.DEPTNO=dept.DEPTNO) emp\_numFROMemp,deptWHERE emp.JOB='CLERK' AND emp.DEPTNO=dept.DEPTNO

)as t

![31b0470017ff94c046836af1a73d29dc.png][]

\*\*\*\*\*\*\*请大家尊重原创，如要转载，请注明出处：转载自：https://www.cnblogs.com/shouhu/，谢谢！！\*\*\*\*\*\*\*

[1b6b9e58a94fba31b0365c0f638f5bd2.png]: /images/20221024/ccab16c071a34dfdaa5d6fa56a611775.png
[bb9b1c2306da67ffdda044b92862061f.png]: /images/20221024/8c6a19928df147ff8d27d0c41c53eff7.png
[6132cae168683675f2e7bc7ed6dc5945.png]: /images/20221024/17dae9db707e40a5b28eb4920077d03a.png
[e57d0be9bd7e6a1df5e8cd2644b63425.png]: /images/20221024/172612e4e5c64281a9ba1d0588312fa2.png
[1b5c583bdf186b03acaeef79f938f7e6.png]: /images/20221024/219fcfe97f814537bc137c4dd3f32466.png
[b6d0f81685e82a89e43d91d938d64dc0.png]: /images/20221024/2bc111ba1fba46dc8c7ff0da89dcfd2c.png
[bd86ea0a663abf5130a078247b74934a.png]: /images/20221024/b03f2d3120a14072bbc457b9aa6729fc.png
[4b930eb9af668eafceeaf71ca703e327.png]: /images/20221024/72916a0501964e8d9116aa9a4ff12791.png
[de10b40f9ad3bc7df37cee618c3dca1a.png]: /images/20221024/64aa6fef36a043f2b56da5e3d430ea90.png
[75b8d80759efaf4918e3b7523d2c5c06.png]: /images/20221024/6d01cccf1c52493286e90ea383502db4.png
[84f35a2cd7df79bccbc41bfc03b93890.png]: /images/20221024/a416dc9bc4c34360944c92dc622e7a38.png
[97d3c45b6d21a802ab29327684ccc020.png]: /images/20221024/f24dec8f55814f658ded22733f0febc3.png
[e447b0806ab35e195fa0c3c3266979c7.png]: /images/20221024/2fbbeb95a715433b985e82946b10df3d.png
[d45c0d5b9eff34b872d5147de94d70e1.png]: /images/20221024/0bf3833a0db9458c8784552c9eae3755.png
[31b0470017ff94c046836af1a73d29dc.png]: /images/20221024/04c2c52a0fbb4aa494060bf168109d3b.png