数据库面试题（一）子查询及面试题练习

一、子查询的基本理论

1.子查询概念：

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

2.子查询的分类

非相关子查询 --- 用的最多
- 子查询的结果作为外部查询的条件　　
- 子查询的结果作为表

相关子查询（关联）
- 依赖于外部查询的数据，外部查询每执行一次，子查询就执行一次
  - 外部查询先查询，在执行子查询
- 子查询的结果作为列

案例：

如：第一题，建表

drop table if exists tb_lemon_score;

create table tb_lemon_score( sname varchar(20), course varchar(20), score tinyint );

insert tb_lemon_score
values
    ( '张三', '语文', 71 ),
    ( '张三', '数学', 75 ),
    ( '李四', '语文', 76 ),
    ( '李四', '数学', 90 ),
    ( '王五', '语文', 81 ),
    ( '王五', '数学', 100 ),
    ( '王五', '英语', 90 );

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

方式一

select sname,score from tb_lemon_score group by sname having min(score)>80;

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

先查询出小于80的

select * from tb_lemon_score where sname not in (select sname from tb_lemon_score where score<=80);

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

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

-- 每人的最小成绩
select sname,min(score) 最小成绩 from tb_lemon_score group by sname;
-- 在结果中查询
select * from (select sname,min(score) 最小成绩 from tb_lemon_score group by sname) t where t.最小成绩>80;

方式四：相关子查询（关联）

外部查询先查询，在执行子查询
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);

案例2：

#用sql语句查询所有购入商品为两种或两种以上的购物人
-- 非关联子查询
SELECT sname FROM (SELECT sname,COUNT(merch) FROM shopping_list GROUP BY sname HAVING COUNT(merch)>=2) as list;
-- 关联子查询
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)

面试题

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

-- 先两条链接，1表id == 2表id  查询后只有三条，and 语文成绩大于平均成绩
select s.name,sc.chinese from 表1 s,表2 sc 
where 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 sc 
on s.id = sc.id and sc.chinese > (select avg(chinese) from 表1);

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；

　两条结果都相同　

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

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

-- 非相关子查询，结果作为一张表
select s.*,t.* from 学生表 s,(select sc.* from 成绩表 sc where sc.math>90 or sc.chinese>=90) t where s.id = t.id;

练习题：

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 into dept
values
    ( 10, 'accounting', 'new york' );
insert into dept
values
    ( 20, 'research', 'dallas' );
insert into dept
values
    ( 30, 'sales', 'chicago' );
insert into dept
values
    ( 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) references dept (deptno));

insert into emp
values
    ( 7369, 'smith', 'clerk', 7902, '1980-12-17', 800, null, 20 );
insert into emp
values
    ( 7499, 'allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30 );
insert into emp
values
    ( 7521, 'ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30 );
insert into emp
values
    ( 7566, 'jones', 'manager', 7839, '1981-04-02', 2975, null, 20 );
insert into emp
values
    ( 7654, 'martin', 'salesman', 7698, '1981-09-28', 1250, 1400, 30 );
insert into emp
values
    ( 7698, 'blake', 'manager', 7839, '1981-05-01', 2850, null, 30 );
insert into emp
values
    ( 7782, 'clark', 'manager', 7839, '1981-06-09', 2450, null, 10 );
insert into emp
values
    ( 7788, 'scott', 'analyst', 7566, '1987-07-13', 3000, null, 20 );
insert into emp
values
    ( 7839, 'king', 'president', null, '1981-11-17', 5000, null, 10 );
insert into emp
values
    ( 7844, 'turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30 );
insert into emp
values
    ( 7876, 'adams', 'clerk', 7788, '1987-07-13', 1100, null, 20 );
insert into emp
values
    ( 7900, 'james', 'clerk', 7698, '1981-12-03', 950, null, 30 );
insert into emp
values
    ( 7902, 'ford', 'analyst', 7566, '1981-12-03', 3000, null, 20 );
insert into emp
values
    ( 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);

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

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

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

SELECT emp_name,dept_name,emp_num FROM (
    SELECT emp.ENAME emp_name,
            dept.DNAME dept_name,
            (SELECT COUNT(emp.EMPNO) FROM emp WHERE emp.DEPTNO=dept.DEPTNO) emp_num 
    FROM emp,dept 
    WHERE emp.JOB='CLERK' AND emp.DEPTNO=dept.DEPTNO
) as t

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

posted @ 2020-04-01 08:22 守护往昔阅读(1186) 评论(0) 编辑收藏举报

刷新页面返回顶部

守护@往昔

守护当下，珍惜过往