SQL练习,mysql快速入门

SQL练习一

1.准备数据

/*
Navicat MySQL Data Transfer

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL AUTO_INCREMENT,
  `dname` varchar(20) NOT NULL,
  `loc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of dept
-- ----------------------------
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');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(11) NOT NULL AUTO_INCREMENT,
  `ename` varchar(20) NOT NULL,
  `job` varchar(10) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` double DEFAULT NULL,
  `comm` double DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of emp
-- ----------------------------
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-04-19', '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-05-23', '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');

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL,
  `losal` double DEFAULT NULL,
  `hisal` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

emp职员表,salgrade薪资登记表,dept部门

其中中包含一定重复的题目!!!!

 

-- 查询部门表
select * from dept;
-- 查询员工表
select * from emp;
-- 查询薪资登记表
select * from salgrade;

1、查找部门30中员工的详细信息。

select * from emp where emp.DEPTNO=30;
2、找出从事clerk工作的员工的编号、姓名、部门号。
select EMPNO,ENAME,DEPTNO from emp where job='CLERK'
3、检索出奖金多于基本工资的员工信息。
select * from emp where comm>sal;


4、检索出奖金多于基本工资60%的员工信息。
select * from emp where sal*0.6<COMM




5、找出10部门的经理、20部门的职员 的员工信息。
select * from emp where (DEPTNO=10 and job='MANAGER') or DEPTNO=20;
6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
select * from emp where (DEPTNO=10 and job='MANAGER') or DEPTNO=20 or sal>2000;


7、找出获得奖金的员工的工作。
select job from emp where comm is not null;
8、找出奖金少于100或者没有获得奖金的员工的信息。
select * from emp where comm is null or comm<200;
9、找出姓名以A、B、S开始的员工信息。
select * from emp where ENAME like 'A%' OR ENAME like 'B%' OR ENAME like 'S%';
10、找到名字长度为6个字符的员工信息。
SELECT * FROM EMP WHERE ENAME LIKE "______";
11、名字中不包含R字符的员工信息。
select * from emp where ename not like "%R%";
12、返回员工的详细信息并按姓名排序。
select * from emp e left join dept  d on e.DEPTNO=d.DEPTNO left join salgrade s on  e.SAL>s.losal and e.sal<=s.HISAL; 

1.查询emp表,
2.左连接dept表,右连接salgrade表
3.on条件删选出部门编号一致的,再删选出收入等级
4.select 展示数据



13、返回员工的信息并按员工的工作年限降序排列。
select * from emp order by HIREDATE desc;
14、返回员工的信息并按工作降序工资升序排列。
select * from emp order by job desc,sal asc;
15、计算员工的日薪(按30天)。
select *,sal/30 as 日薪 from emp ;
16、找出姓名中包含A的员工信息。
select * from emp where ename like "%A%"

第二部分练习
1、返回拥有员工的部门名、部门号。(dept,emp)	
select * from  dept  where DEPTNO in((select DEPTNO as den from emp e group by DEPTNO)) 
2、工资水平多于smith的员工信息。
select * from emp where sal> (select sal from emp where ename="smith")
3、返回员工和所属经理的姓名。（自连接）
select e1.ENAME 员工,e2.ENAME 领导 from emp  e1 join emp e2 on e1.mgr=e2.EMPNO; 
4、返回   雇员的雇佣日期早于其领导雇佣日期的      员工及其领导姓名。(在日期类型可以直接比较)
select * from emp e1 join emp  e2 on e1.HIREDATE<e2.HIREDATE and
e1.mgr=e2.EMPNO; 

5、返回员工姓名及其所在的部门名称。
select ENAME,dept.DNAME from emp join dept on emp.DEPTNO=dept.DEPTNO
6、返回从事clerk工作的员工姓名和所在部门名称。
select ename,dept.DNAME from emp  join dept on dept.DEPTNO=emp.DEPTNO and emp.JOB="CLERK"
7、返回部门号及其本部门的最低工资。
select emp.DEPTNO,min(emp.SAL) from emp group by DEPTNO
 1 每个部门的最低工资  部门编号和工资
 select DEPTNO,min(sal),avg(sal) from emp group by DEPTNO
 2 部门编号和工资  join  部门
8、返回销售部(sales)所有员工的姓名。
	select ename from emp join dept on emp.DEPTNO=dept.DEPTNO and dname="SALES"
9、返回工资多于平均工资的员工。
select * from emp where sal>(select avg(sal) from emp)
10、返回与SCOTT从事相同工作的员工。
select * from emp where JOB=(select job from emp where ename="SCOTT")
11、返回与30部门员工工资水平相同的员工姓名与工资。
select * from emp where sal=
(select avg(sal) from emp group by DEPTNO having DEPTNO=30)
12、返回工资高于30部门所有员工工资水平的员工信息。
select * from emp where DEPTNO !=30 and  sal>(
select max(sal) from emp group by DEPTNO having DEPTNO=30)

 
13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。
select * from dept join (select DEPTNO,count(*) from emp group by DEPTNO) as cout on dept.DEPTNO=cout.DEPTNO
14、返回员工的姓名、所在部门名及其工资。
select emp.ENAME,emp.SAL,dept.DNAME from emp,dept where  emp.DEPTNO=dept.DEPTNO
15、返回员工的详细信息。(包括部门名)
select emp.*,dept.DNAME from emp,dept where emp.DEPTNO=dept.DEPTNO
16、返回员工工作及其从事此工作的最低工资。
select emp.JOB,emp.ENAME,(select min(sal) from emp) from emp 
17、返回不同部门经理的最低工资。
select min(sal) from emp where JOB='MANAGER' group by JOB
18、计算出员工的年薪，并且以年薪排序。
select emp.ENAME,sal*12 as yearsal from emp order by yearsal  
19、返回工资处于第四级别的员工的姓名。
select emp.ENAME from emp join salgrade on emp.SAL>salgrade.LOSAL and emp.SAL<salgrade.HISAL  where salgrade.GRADE=4
-- 连接查询,筛选其中出连接中正确信息,where 筛选第四级别


20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
select min(sal),max(sal) from emp join salgrade on emp.SAL>salgrade.LOSAL and 
emp.SAL<salgrade.HISAL join dept on emp.DEPTNO=dept.DEPTNO
where salgrade.GRADE in (2) group by salgrade.GRADE




21、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资
参考20题

select * from emp join dept on emp.DEPTNO=dept.DEPTNO join
 salgrade on emp.SAL>salgrade.LOSAL and emp.SAL<salgrade.HISAL
 and salgrade.GRADE=2 and dept.DEPTNO=emp.DEPTNO






- 11.查询工资大于2000的雇员,按照部门编号进行分组,分组后平均薪水大于1500,按平均工资倒序排列


select *  from emp where sal > 2000 group by deptno having avg(sal)>1500 order by sal DESC


- 12.查询那些薪水在平均薪水之上的员工信息

select * from emp e1 join (select avg(sal) as avgsal from emp) avgsal on e1.sal >avgsal

-- 此处的第一个as avgsal 是指定输出结果名,后面的是做外外层查询的别名
- 13.查找员工姓名和部门名称  emp    dept
select emp.ename,dept.dname from emp join dept on emp.deptno=dept.deptno

- 14.查找每个部门挣钱最多的那个人的名字
select * from emp join (
select max(sal) as maxsal from emp group by deptno
) maxsal on emp.sal=maxsal


必须要两个联合起来,才能显示正确字段名
- 15.求员工的部门名称和薪水等级
select * from emp join dept on emp.deptno=dept.deptno join salgrade  on emp.sal>salgrade.losal and emp.sal<salgrade.hisal
- 16.查询每个员工的上级领导
1-------select * from emp e1 join emp e2 on e1.mgr=e2.empno or e1.mgr is null and e1.ename=e2.ename
1.解释

		from emp表
		join 连接emp自身
		on 筛选笛卡尔积,同时放开查询出为空的,通过ename筛选出无效数据
		select 展示
		根据上面的,on在where前面执行,所以1比2相对好一些,本质来说是一致的

2---------------select * from emp e1,emp e2 where  e1.mgr=e2.empno or e1.mgr is null and e1.ename=e2.ename;	
	直接笛卡尔,where条件筛选,实际执行效果是where执行效率比join on 高一点,
	主要是join是在from之后执行的,所以没有单次from两张表效率高
	
	
	
	
	
	
	
	
	
	
	

- 17.求部门平均薪水的等级 
select * from dept,(select deptno,avg(sal) avgsal from emp  group by deptno ) as avg_sal,salgrade where dept.deptno=avg_sal.deptno and avgsal>salgrade.losal and avgsal<salgrade.hisal

分析:
	尽量在查询开始前准备好数据
	from 的时候查询出来所有的数据,
	where 的时候进行筛选
	最后展示数据
	
	select * from dept,salgrade where (select deptno,avg(sal) avgsal from emp  group by deptno  ) is not null;
	from 后面跟多张表,就是tb1*tb2,也就是全连接
	这样会筛选出更多数据,所以最好还是使用内链接,可以提高速度
	where后面的子查询会每条数据遍历一次,所以严重影响速度
	可以将子查询放在from后面,这样就只有一次
	
	所以总的来说
	子查询<from后的全链接子查询<内链接查询
	select * from dept inner join (select deptno,AVG(sal) avgsal from emp group  by deptno) as avgsal on dept.deptno=avgsal.deptno
	
- 18.求部门平均的薪水等级 
同上


- 19.哪些人是领导

select e2.* from  emp e1 join emp e2 on e1.mgr=e2.empno
- 20.求平均薪水最高的部门编号 

select deptno,max(depavg.avgsal)  from  (select deptno,avg(sal) avgsal from emp group by deptno) depavg


- 21.求平均薪水最高的部门名称

select * from dept join (
select deptno,max(depavg.avgsal)  from  (select deptno,avg(sal) avgsal from emp group by deptno) depavg ) maxavg on dept.deptno=maxavg.deptno


-- 练习题目
-- 1.计算每个人的年薪并按照年薪的从高到底排序  order by desc 
select emp.*,(sal+IFNULL(comm,0))*12 as saltatol from emp order by saltatol desc;
-- ifnull(),函数,判断是否为空,为空返回参数二

-- 2.查询奖金不为空的员工信息
select * from emp where comm is not null ;

-- 3.查询月薪在[800,1500）之间的员工信息

select *,IFNULL(COMM,0) as comm  from emp where  emp.SAL >=800 and emp.SAL<1500;
-- 4.查询部门下的职位（去重部门编号、职位）   在部门下再分组
select * from emp GROUP BY DEPTNO,JOB;
-- 根据部门编号分完组之后,再根据job分组,所以出来的是结果只有同一个唯一的一个job信息
-- 5.查询薪水大于2000的员工信息，并且按照部门编号的升序、名字的降序方式排序
select * from  emp where sal >2000 order by DEPTNO asc ,ename desc;
-- 6.查询薪水大于1500的员工个数
select COUNT(*) from emp where sal >1500;
-- 7.查询薪水大于2500的员工所属部门数量(高薪部门的个数)
select emp.DEPTNO,count(*) from emp where SAL>1100 group by DEPTNO; 
查询emp 表 
查询范围是sal>1100的人 
通过部门编号分组,
分完组之后查询emp的部门编号,以及这个部门的数量

-- 8.查询每个部门的平均薪水


select emp.DEPTNO,avg(SAL) from emp group by DEPTNO


-- 9.查询每个部门岗位相同薪资最高的信息
查出岗位相同的最高薪资
select DEPTNO,job,max(sal) from emp group by JOB; 

from emp 
group by job  -- 获得的是  每组相同工作的分组,
max(sal) -- 求出分组后的聚合信息
select -- 部门id,工作id
-- 10.按照部门编号进行分组求部门平均薪且平均薪水大于2000的，并按照部门编号升序排序
select * from (select DEPTNO,avg(sal) as avg_sal from emp group by  DEPTNO ) t where t.avg_sal>2000;
select DEPTNO,avg(sal) as avg_sal from emp group by  DEPTNO having avg_sal>2000; 
from 职工表
group by 部门
avg 聚合平均值
having 过滤
select 

-- SQL 执行的顺序
1.确定从哪里取数据  from
2.确定从哪里联合数据  join
3.联合数据的条件 on
4.确定过滤字段 where
5.确定分组方式 group by
6.确定分组后的聚合函数结果  avg,sum
7.确定分组之后的结果筛选  having
8.确定展示的数据  select 
9.是否去除展示的重复值 distinct
10.确定展示数据的排序规则  order by

 

SQL练习二

准备数据

CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

使用hive的准备参数

-- 条件准备
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES;
select @@sql_mode;

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 

SQL练习题---经典50

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 

select * from student stu left join score s1 on s1.s_id=stu.s_id
and s1.c_id=01
left join 
score s2 on s2.s_id=stu.s_id
and s2.c_id=02
where s1.s_score>s2.s_score
-- 解析
左连接查询出student中id为01的值,再次左连接出为02有成绩的,最后筛选出成绩大于02的

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数


select * from student stu join score s1 on stu.s_id=s1.s_id
and s1.c_id=01
join score s2 on stu.s_id=stu.s_id and s2.c_id=01
where s1.s_score<s2.s_score

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select student.s_id,student.s_name,avg(score.s_score) as avgsco from student join score on student.s_id=score.s_id
group by s_id having avgsco>=60

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)
select student.s_id,student.s_name,avg(IFNULL(score.s_score,0)) as avgsco from student left join score  on student.s_id=score.s_id or student.s_id=null
group by s_id having avgsco<60

-- 解析
	左连接student,条件筛选放开null值
	id分组,聚合平均值,为空返回0,
	having过滤后展示


-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select student.s_id,student.s_name,count(score.c_id),sum(score.s_score) from student join score on student.s_id=score.s_id 
group by student.s_id



-- 6、查询"李"姓老师的数量 
select count(*) from teacher where t_name like "李%"
-- 7、查询学过"张三"老师授课的同学的信息 

select * from student,score,course,teacher where 
course.c_id=score.c_id
and student.s_id=score.s_id 
and teacher.t_name="张三"
and teacher.t_id=course.t_id
order by student.s_name

-- 连接查询

select * from student join score on score.s_id=student.s_id
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id and teacher.t_name="张三"





-- 8、查询没学过"张三"老师授课的同学的信息 
select * from student where student.s_id not in (
select student.s_id from student join score on score.s_id=student.s_id
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id and teacher.t_name="张三"
)
 -- 张三老师教的课
 -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息


select * from student s1 join score s2 on s1.s_id =s2.s_id
join score sc2 on sc2.s_id=s1.s_id and s2.s_id=s1.s_id 
and s2.c_id=01 
and sc2.c_id=02
 -- 解析,连接两遍之后,其中






 -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student where s_id not in (
select s1.s_id from student s1 join score s2 on s1.s_id =s2.s_id
join score sc2 on sc2.s_id=s1.s_id and s2.s_id=s1.s_id 
and s2.c_id=01 and sc2.c_id=02)


-- 11、查询没有学全所有课程的同学的信息

select * from (
select student.s_name,count(score.c_id) countcourse from student join score on student.s_id=score.s_id group by student.s_name) stucour where (select count(*) from course)!=stucour.countcourse;
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
-- 直接查询01学号的学生的课程,通过课程招人,去重
select distinct s_name from student join score on student.s_id=score.s_id join (select score.c_id ucourse,score.s_id uid from student JOIN score on score.s_id=student.s_id where student.s_id='01' ) u1 on  u1.ucourse=score.c_id  and student.s_id!='01'  order by s_name


-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
-- 找到01课程中有没有修的人
select s_name,count(ucourse) otrcoun from 
(select student.*,courlist.* from student join (select score.c_id ucourse from student JOIN score on score.s_id=student.s_id where student.s_id='01' )  courlist join score on student.s_id=score.s_id and score.c_id=courlist.ucourse) t1 group by s_name  having otrcoun=(select count(score.c_id ) from student JOIN score on score.s_id=student.s_id where student.s_id='01' ) 
-- 解析
思路,根据01这个id找到01选择的课程,然后拿着这些课程去找修过课程的同学,通过计数,查看哪些人是修过这些课程,并且这些课程的数量等于01同学的数量

1.根据01这个id找到01选择的课程
(select score.c_id ucourse from student JOIN score on score.s_id=student.s_id where student.s_id='01' )  记做A
2 然后拿着这些课程去找修过课程的同学

(select student.*,courlist.* from student join A  courlist join score on student.s_id=score.s_id and score.c_id=courlist.ucourse)  记做B
3通过计数,查看哪些人是修过这些课程,并且这些课程的数量等于01同学的数量
select s_name,count(ucourse) otrcoun from B t1 group by s_name  having otrcoun=A的count()课程数

 

 

 

 

更优写法请到评论区分享!!!未完!!!