嵌套查询练习
2018-10-28 本文已影响0人
Gloria艳
-- 查询课程表,教师信息,使用右连接 以右表为基础，右侧没有的，直接显示为空
SELECT te.*,co.*
FROM `teachers` te
RIGHT JOIN`courses` co
ON te.`tno`=co.`tno`;
-- 查询课程表,教师信息,使用左连接
SELECT te.*,co.*
FROM `teachers` te
LEFT JOIN `courses` co
ON te.`tno`=co.`tno`;
--
SELECT sc.*,co.*
FROM `scores` sc
RIGHT JOIN `courses` co
ON sc.`cno`=co.`cno`;
-- 四表连接
SELECT
FROM
((表1
JOIN 表2
ON 相同的编号)
JOIN 表3
ON 表3和表2的相同编号)
JOIN 表4
ON 表4 和表3 的相同编号
-- 作业
-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class
FROM `students` ;
-- 2、 查询教师所有的单位即不重复的Depart列。
/*先对depart列分组统计,筛选出重复的单位称呼*/
SELECT depart
FROM`teachers`
GROUP BY depart
HAVING COUNT(*)>1;
/*在筛选出不在刚才筛选出来的重复单位称呼里面的称呼*/
SELECT *
FROM `teachers`
WHERE depart NOT IN ();
-- 合在一起就可以了
SELECT *
FROM `teachers`
WHERE depart NOT IN (
SELECT depart
FROM`teachers`
GROUP BY depart
HAVING COUNT(*)>1);
-- 3、 查询Student表的所有记录。
SELECT *
FROM `students`;
-- 4、 查询Score表中成绩在60到80之间的所有记录。 思路：在什么之间 用between...and...
SELECT *
FROM `scores`
WHERE degree BETWEEN 60 AND 80;
-- 5、 查询Score表中成绩为85，86或88的记录。 思路： 精确的查找条件中有多个条件是或者的关系用in
SELECT *
FROM `scores`
WHERE degree IN (85,86,88);
-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。 思考：怎么设置让性别为女的=1呢？？？
SELECT *
FROM `students`
WHERE class=95031 OR ssex='1';
-- 7、 以Class降序查询Student表的所有记录。 思路：升序，降序用排序order by
SELECT *
FROM `students`
ORDER BY class DESC;
-- 8、 以Cno升序、Degree降序查询Score表的所有记录。
SELECT *
FROM `scores`
ORDER BY cno ASC,degree DESC; -- 细节：order by 后面跟的表列名，谁在前，先按谁排序
-- 9、 查询“95031”班的学生人数。 思路：统计数据用count
SELECT COUNT(class)
FROM `students`
WHERE class=95031;
-- 10、查询Score表中的最高分的学生学号和课程号。
/*思路:先查找最高分，然后查询分数等于最高分的学生学号，课程号*/
SELECT sno,cno,degree -- 注意点:max只能查询具体某列的最大值，并不能直接筛选出一整行的值
FROM `scores`
WHERE degree=(
SELECT MAX(degree)
FROM `scores`);
-- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
/*-- 思路：首先按学生编号分组、统计，先筛选出选课数目>1 的
SELECT count(sno),degree
FROM`scores`
group by sno
having count(sno)>1
-- 然后筛选出最高分 -- 思考:用排序的方法怎么做？ order by degree desc LIMIT 2,3
SELECT MAX(a.degree)
FROM(
SELECT COUNT(sno),degree
FROM`scores`
GROUP BY sno
HAVING COUNT(sno)>1) a*/
SELECT *
FROM(
SELECT * -- 这里的select后面的* 表示显示表格的所有表头*/
FROM`scores`
GROUP BY sno -- 补充：count 统计 如果不需要显示count统计值时，select 里可以不写count
HAVING COUNT(sno)>1) a -- 同理:max,min 等聚合函数都是可以这样书写的
WHERE degree NOT IN(
SELECT MAX(a.degree)
FROM (
SELECT degree
FROM`scores`
GROUP BY sno
HAVING COUNT(sno)>1) a);
-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
/*思路:先选出学号109，课程号3-105的成绩，然后查询>这个成绩即可*/
SELECT *
FROM`scores`
WHERE degree>(
SELECT degree
FROM `scores`
WHERE sno=109 AND cno='3-105')
-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
-- 思路：先查询出学号108的出生年份，然后查询年份=这个年份即可
SELECT sno,sname,sbirthday
FROM`students`
WHERE YEAR(sbirthday)=( -- 知道出生日期算年份，用year（出生日期）
SELECT YEAR(sbirthday)
FROM `students`
WHERE sno=105);
-- 23、查询“张旭“教师任课的学生成绩。
/*思路：先把老师表和学生成绩表建立关联，这中间需要第三方课程表，三表关联，然后查询教师名字叫张旭即可*/
SELECT sc.`degree`,te.`tname`
FROM
(`teachers` te
JOIN`courses` co
ON te.`tno`=co.`tno`)
JOIN `scores` sc
ON co.`cno`=sc.`cno`
WHERE te.`tname`='张旭';
-- 24、查询选修某课程的同学人数多于5人的教师姓名。
/*思路：先把老师表和学生成绩表关联，同样也是通过课程表三表关联，然后查询课程人>5的，
这里有一个转换，课程人数需要通过成绩表里的课程编号统计人数，一个课程有多少个成绩，也代表几个人*/
SELECT te.`tname`,COUNT(sc.`cno`)
FROM
(`scores` sc
JOIN `courses` co
ON sc.`cno`=co.`cno`)
JOIN `teachers` te
ON co.`tno`=te.`tno`
GROUP BY sc.`cno`
HAVING COUNT(sc.`cno`)>5
-- 25、查询95033班和95031班全体学生的记录。
SELECT*
FROM`students`
WHERE class IN (95033,95031);
-- 26、查询存在有85分以上成绩的课程Cno;
SELECT degree,cno
FROM`scores`
WHERE degree>85;
-- 27、查询出“计算机系“教师所教课程的成绩表。
/*思路：首先老师表，课程表，成绩表三表关联，然后查询老师表中计算机系的*/
SELECT sc.`degree`,co.`cname`,te.`depart`
FROM
( `scores` sc
JOIN`courses` co
ON sc.`cno`=co.`cno`)
JOIN `teachers` te
ON co.`tno`=te.`tno`
WHERE te.`depart`='计算机系';
-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
/* 有两种可能，一种是以计算机系为中心，筛选电子工程系没有的职称 思路：条件是计算机系并且职称不在电子工程系里*/
SELECT tname,prof
FROM`teachers`
WHERE depart='计算机系' AND prof NOT IN (
SELECT prof
FROM`teachers`
WHERE depart='电子工程系');
/* 第二种，以两个系为中心，筛选出两个系中不同的职称 思路：先把两个系的所有职称都选出来，然后去除共同的职称
首先筛选出两个系的所有职称*/
SELECT tname,prof
FROM`teachers`
WHERE (depart='计算机系'OR depart='电子工程系')
/*其次排除两个系共同的职称*/
AND prof NOT IN (
/*再筛选出两个系共同的职称*/
SELECT prof
FROM(
/* 筛选出计算机系的所有职称 这里生成一个临时表，起一个别名*/
SELECT prof
FROM`teachers`
WHERE depart='计算机系') a
/* 查询和计算机系职称一样的*/
WHERE prof IN (
/* 筛选出电子工程系所有的职称*/
SELECT prof
FROM`teachers`
WHERE depart='电子工程系'));
-- 29、查询选修编号为“3-105”
-- 且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,
-- 并按Degree从高到低次序排序。
SELECT cno,sno,degree
FROM `scores`
WHERE cno='3-105' AND degree>(
SELECT MIN(degree) -- 至少的意思是比它最低分高就可以了
FROM `scores`
WHERE cno='3-245')
ORDER BY degree DESC;
-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree
SELECT cno,sno,degree
FROM `scores`
WHERE cno='3-105' AND degree>(
SELECT MAX(degree) -- 高于就是比它最大值还要大
FROM `scores`
WHERE cno='3-245');
-- 31、查询所有教师和同学的name、sex和birthday;
SELECT st.`sname`,st.`ssex`,st.`sbirthday`
FROM `students` st
UNION -- 思路：两个表分别查询，然后合并一下就可以了
SELECT te.`tname`,te.`tsex`,te.`tbirthday`
FROM `teachers` te;
-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT te.`tname`,te.`tsex`,te.`tbirthday`
FROM `teachers` te
WHERE te.`tsex`='女'
UNION
SELECT st.`sname`,st.`ssex`,st.`sbirthday`
FROM `students` st
WHERE st.`ssex`='1';
-- 33、查询成绩比该课程平均成绩低的同学的成绩表。 思路：先算出平均成绩，然后筛选<平均成绩的
SELECT *
FROM`scores`
WHERE degree<(
SELECT AVG(degree)
FROM`scores`)
-- 34、查询所有任课教师的Tname和Depart.
SELECT te.`tname`,te.`depart`
FROM`teachers` te
JOIN `courses` co -- join默认的是inner join（内联）
ON te.`tno`=co.`tno`;
-- 35 查询所有未讲课的教师的Tname和Depart. 思路：左关联或者有关联，然后选择任课表里没有的就可以了
SELECT te.`tname`,te.`depart`
FROM`teachers` te
LEFT JOIN `courses` co
ON te.`tno`=co.`tno`
WHERE co.`tno` IS NULL;
-- 36、查询至少有2名男生的班号。 思路：可以统计性别，也可以统计班级，条件(having)都是>=2
-- 一种统计性别的
SELECT class,COUNT(ssex)
FROM`students`
WHERE ssex=0
GROUP BY class
HAVING COUNT(ssex)>=2;
-- 另一种统计班级的
SELECT class
FROM`students`
WHERE ssex=0
GROUP BY class
HAVING COUNT(class)>=2
-- 37、查询tudent表中不姓“王”的同学记录。
SELECT*
FROM `students`
WHERE sname NOT LIKE '王%'
-- 38、查询Student表中每个学生的姓名和年龄。 思路:现在的时间-year（出生日期）
SELECT sname '姓名',2018-YEAR(sbirthday) '年龄'
FROM `students`;
-- 39、查询Student表中最大和最小的Sbirthday日期值。
SELECT MIN(sbirthday),MAX(sbirthday)
FROM `students`;
-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT class,sname,ssex,2018-YEAR(sbirthday) '年龄'
FROM `students`
ORDER BY class DESC, 年龄 DESC;
-- 41、查询“男”教师及其所上的课程。;
SELECT co.`cname`,te.`tsex`
FROM `teachers` te
JOIN `courses` co
ON te.`tno`=co.`tno`
WHERE te.`tsex`='男';
-- 42、查询最高分同学的Sno、Cno和Degree列。
SELECT sno,cno,degree
FROM `scores`
WHERE degree=(
SELECT MAX(degree)
FROM `scores`);
-- 43、查询和“李军”同性别的所有同学的Sname.
/*思路:先筛选出李军的性别，然后查询和其性别一样的就可以了*/
SELECT sname
FROM `students`
WHERE ssex=(
SELECT ssex
FROM `students`
WHERE sname='李军')
-- 44、查询和“李军”同性别并同班的同学Sname.
/*思路:先筛选出李军的性别和班级，然后查询和器性别，班级都一样的就可以了*/
SELECT sname
FROM `students` st
WHERE ssex=(
SELECT ssex
FROM `students`
WHERE sname='李军')
AND class=(
SELECT class
FROM `students`
WHERE sname='李军');
-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表;
/* 思路:先建立学生表，成绩表，课程三表的关联，然后筛选课程是计算机导论 and 性别='男'就可以了*/
SELECT sc.`degree`,co.`cname`,st.`ssex`
FROM
(`courses` co
JOIN`scores` sc
ON co.`cno`=sc.`cno`)
JOIN `students` st
ON sc.`sno`=st.`sno`
WHERE st.`ssex`=0 AND co.`cname`='计算机导论';
