复习一些不太难的sql，有比较难的先暂时不看，因为应付面试，手写的话应该不会有特别复杂的吧
较简单的
要求查询出参加考试的各科成绩都高于60分，不管参加了多少科考试
CREATE TABLE tbl_score (
id int(10) NOT NULL,
username varchar(20) DEFAULT NULL,
course varchar(20) DEFAULT NULL,
score int(10) DEFAULT NULL,
PRIMARY KEY (id)
)
insert into tbl_score (id, username, course, score) values('1','张三','语文','50');
insert into tbl_score (id, username, course, score) values('2','张三','数学','80');
insert into tbl_score (id, username, course, score) values('3','张三','英语','90');
insert into tbl_score (id, username, course, score) values('4','李四','语文','70');
insert into tbl_score (id, username, course, score) values('5','李四','数学','80');
insert into tbl_score (id, username, course, score) values('6','李四','英语','80');
insert into tbl_score (id, username, course, score) values('7','王五','语文','50');
insert into tbl_score (id, username, course, score) values('8','王五','英语','70');
insert into tbl_score (id, username, course, score) values('9','赵六','数学','90');
select * from tbl_score where username in
(select username from tbl_score group by username having min(score) > 60)
一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合
CREATE TABLE team(
name VARCHAR(10)
)
INSERT INTO team VALUES('a');
INSERT INTO team VALUES('b');
INSERT INTO team VALUES('c');
INSERT INTO team VALUES('d');
SELECT * FROM team;
SELECT *
FROM team t1,team t2 WHERE t1.name!=t2.name;
CREATE TABLE student (
sid varchar(10) NOT NULL,
sName varchar(20) DEFAULT NULL,
sAge datetime DEFAULT '1980-10-12 23:12:36',
sSex varchar(10) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE course (
cid varchar(10) NOT NULL,
cName varchar(10) DEFAULT NULL,
tid int(20) DEFAULT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sc (
sid varchar(10) DEFAULT NULL,
cid varchar(10) DEFAULT NULL,
score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE taacher (
tid int(10) DEFAULT NULL,
tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');
insert into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),
('1002','张无极','1995-10-12 23:12:36','男'),
('1003','李奎','1992-10-12 23:12:36','女'),
('1004','李元宝','1980-10-12 23:12:36','女'),
('1005','李世明','1981-10-12 23:12:36','男'),
('1006','赵六','1986-10-12 23:12:36','男'),
('1007','田七','1981-10-12 23:12:36','女');
insert into sc(sid,cid,score) values ('1001','001',80),('1001','002',60),('1001','003',75),('1002','001',85),
('1002','002',70),('1003','004',100),('1003','001',90),('1003','002',55),('1004','002',65),('1004','003',60);
insert into course(cid,cName,tid) values ('001','企业管理',3),
('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM sc;
SELECT * FROM taacher;
查询“001”课程比“002”课程成绩高的所有学生的学号
SELECT sc1.sid FROM
(SELECT * FROM sc WHERE cid=001) sc1,
(SELECT * FROM sc WHERE cid=002) sc2
WHERE sc1.score > sc2.score AND sc1.sid=sc2.sid;
查询平均成绩大于60分的同学的学号和平均成绩
SELECT sid FROM sc GROUP BY sid HAVING avg(score)>60;
查询所有同学的学号、姓名、选课数、总成绩;
SELECT sc.*,st.sName FROM student st,
(SELECT sid,count(*),sum(score) FROM sc GROUP BY sid) sc
WHERE st.sid = sc.sid;
查询姓“李”的老师的个数
SELECT count(*) FROM taacher WHERE tName LIKE "李%";
查询没学过“叶平”老师课的同学的学号、姓名;
SELECT * FROM student WHERE sid not in(
SELECT distinct sc.sid
FROM sc,(SELECT cid
FROM course WHERE tid IN (SELECT tid FROM taacher WHERE tName="叶平")) tt
WHERE sc.cid=tt.cid);
查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
SELECT st.sid,st.sName
FROM student st,
(SELECT sid
FROM sc WHERE cid=001 or cid=002 GROUP BY sid HAVING count(*) =2) sc
WHERE st.sid = sc.sid;
查询学过“叶平”老师课的同学的学号、姓名;
SELECT * FROM student WHERE sid in(
SELECT distinct sc.sid
FROM sc,(SELECT cid
FROM course WHERE tid IN (SELECT tid FROM taacher WHERE tName="叶平")) tt
WHERE sc.cid=tt.cid);
查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
SELECT st.sid,st.sName FROM student st
WHERE sid in(
SELECT sc1.sid
FROM (SELECT * FROM sc WHERE cid=001) sc1,
(SELECT * FROM sc WHERE cid=002) sc2 WHERE sc1.sid=sc2.sid AND sc2.score < sc1.score);
查询所有课程成绩小于70分的同学的学号、姓名;
SELECT sid,sName FROM student WHERE sid in
(SELECT sc1.sid
FROM (SELECT sid,count(*) ct
FROM sc WHERE score<70 GROUP BY sid HAVING count(*)>0) sc1,
(SELECT sid,count(*) ct FROM sc GROUP BY sid) sc2
WHERE sc1.sid=sc2.sid AND sc1.ct=sc2.ct);
查询没有学全所有课的同学的学号、姓名
SELECT sid,sName FROM student WHERE sid IN(
SELECT sid
FROM sc GROUP BY sid HAVING count(*)=(SELECT count(*) FROM course));
查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名
SELECT sid,sName FROM student WHERE sid in(
SELECT distinct sid
FROM sc WHERE cid IN (SELECT cid FROM sc WHERE sid=1001));
把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
UPDATE sc sc3,
(SELECT sc1.cid,avg(score) score
FROM sc sc1,(SELECT cid
FROM course WHERE tid IN(SELECT tid FROM taacher WHERE tName='叶平')) sc2
WHERE sc1.cid=sc2.cid GROUP BY cid) sc4
SET sc3.score=sc4.score WHERE sc3.cid=sc4.cid;
删除学习“叶平”老师课的SC表记录
DELETE FROM sc WHERE cid in(
SELECT cid
FROM course WHERE tid=(SELECT tid FROM taacher WHERE tName='叶平'));
SELECT tid FROM taacher WHERE tName='叶平');
查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT cid '课程ID',max(score) '最高分',min(score) '最低分'
FROM sc
GROUP BY cid
按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，
按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
SELECT sid as 学生ID
,(SELECT score FROM sc WHERE sc.sid=t.sid AND cid='004') AS 数据库
,(SELECT score FROM sc WHERE sc.sid=t.sid AND cid='001') AS 企业管理
,(SELECT score FROM sc WHERE sc.sid=t.sid AND cid='005') AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM sc AS t
GROUP BY sid
ORDER BY avg(t.score)
查询不同老师所教不同课程平均分从高到低显示 要求显示：教师ID,教师姓名，课程ID,课程名称，平均成绩
SELECT max(co.tid) '教师id',max(ta.tName) '教师姓名', max(sc.cid) '课程id', max(co.cName) '课程名称', avg(score) '平均成绩'
FROM course co,sc sc,taacher ta
WHERE co.tid=ta.tid AND co.cid=sc.cid
GROUP BY sc.cid
统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT sc.cid,co.cName,
sum(CASE WHEN score>=85 THEN 1 ELSE 0 END) '100-85',
sum(CASE WHEN score>=70 AND score<=85 THEN 1 ELSE 0 END) '85-70',
sum(CASE WHEN score>=60 AND score<=70 THEN 1 ELSE 0 END) '70-60',
sum(CASE WHEN score<60 THEN 1 ELSE 0 END) '<60'
FROM sc sc,course co
WHERE sc.cid=co.cid
GROUP BY sc.cid,co.cName
查询每门课程被选修的学生数
SELECT cid,count(*)
FROM sc
GROUP BY cid
查询出只选修了一门课程的全部学生的学号和姓名
SELECT max(st.sName),max(st.sid)
FROM sc sc,student st
WHERE sc.sid=st.sid
GROUP BY cid HAVING count(*)=1
查询男生、女生人数
SELECT sSex,count(sSex)
FROM student
GROUP BY sSex
查询姓“张”的学生名单
SELECT * FROM student WHERE sName LIKE "张%";
查询同名同性学生名单，并统计同名人数
SELECT st1.sName
FROM student st1,student st2
WHERE st1.sName=st2.sName
GROUP BY st1.sName
HAVING count(*) >1
查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT cid,avg(score)
FROM sc
GROUP BY cid
ORDER BY avg(score) asc,cid
查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT sc.sid,max(st.sName),avg(sc.score)
FROM sc sc,student st
WHERE sc.sid=st.sid
GROUP BY sc.sid
HAVING avg(sc.score)>75;
查询课程名称为“数据库”，且分数低于60的学生姓名和分数
SELECT *
FROM course co,sc sc,student st
WHERE co.cName='数据库'
AND co.cid=sc.cid
AND sc.sid=st.sid
AND sc.score<60
查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
SELECT st.sName,co.cName,sc.score
FROM sc sc,course co,student st
WHERE sc.cid=co.cid
AND sc.sid=st.sid
AND sc.score>70;
查询不及格的课程，并按课程号从大到小排列
SELECT *
FROM sc
WHERE score<60
ORDER BY cid desc;
查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT st.sName,sc.score
FROM taacher ta,student st,course co,sc sc
WHERE ta.tName='叶平'
AND ta.tid=co.tid
AND co.cid=sc.cid
AND sc.sid=st.sid
ORDER BY score desc LIMIT 1
查询各个课程及相应的选修人数
SELECT cid,count(*)
FROM sc
GROUP BY cid
查询不同课程成绩相同的学生的学号、课程号、学生成绩
SELECT st.sid,sc1.cid,sc2.cid,sc1.score
from sc sc1,sc sc2,student st
WHERE sc1.cid!=sc2.cid
AND sc1.score=sc2.score
AND st.sid=sc1.sid;
统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT cid,count(*)
FROM sc
GROUP BY cid
HAVING count(*) > 2
ORDER BY count(*) desc,
cid asc;
检索至少选修两门课程的学生学号
SELECT sid FROM sc
GROUP BY sid
HAVING count(*) > 2;
查询全部学生都选修的课程的课程号和课程名
SELECT sc.cid,co.cName
FROM sc,course co
WHERE sc.cid=co.cid
GROUP BY sc.cid
HAVING count(*) = (SELECT count(*) FROM student)
查询没学过“叶平”老师讲授的任一门课程的学生姓名
SELECT st.sName
FROM student st,sc sc
WHERE sc.sid=st.sid
AND sc.sid not in(
SELECT sc.sid
FROM course co,sc sc
WHERE co.tid=3
AND co.cid=sc.cid)
较复杂的
或者是不太好理解的，或者是有歧义的
CREATE TABLE student (
sid varchar(10) NOT NULL,
sName varchar(20) DEFAULT NULL,
sAge datetime DEFAULT '1980-10-12 23:12:36',
sSex varchar(10) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE course (
cid varchar(10) NOT NULL,
cName varchar(10) DEFAULT NULL,
tid int(20) DEFAULT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sc (
sid varchar(10) DEFAULT NULL,
cid varchar(10) DEFAULT NULL,
score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE taacher (
tid int(10) DEFAULT NULL,
tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');
insert into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),
('1002','张无极','1995-10-12 23:12:36','男'),
('1003','李奎','1992-10-12 23:12:36','女'),
('1004','李元宝','1980-10-12 23:12:36','女'),
('1005','李世明','1981-10-12 23:12:36','男'),
('1006','赵六','1986-10-12 23:12:36','男'),
('1007','田七','1981-10-12 23:12:36','女');
insert into sc(sid,cid,score) values ('1001','001',80),('1001','002',60),('1001','003',75),('1002','001',85),
('1002','002',70),('1003','004',100),('1003','001',90),('1003','002',55),('1004','002',65),('1004','003',60);
insert into course(cid,cName,tid) values ('001','企业管理',3),
('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名
SELECT sc5.sid
FROM
(SELECT sc1.sid
FROM sc sc1 GROUP BY sid HAVING count(*) = (SELECT count(*) FROM (SELECT * FROM sc WHERE sid=1002) sc2)) sc5,
(SELECT sc3.sid
FROM sc sc3,
(SELECT cid,sid FROM sc WHERE sid=1002) sc4 WHERE sc3.cid=sc4.cid GROUP BY sid HAVING count(*)=2) sc6
WHERE sc5.sid=sc6.sid;
按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，
按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
SELECT sid '学生id',avg(score) '有效平均分',
(SELECT score FROM sc WHERE cid='004' AND sid=sc1.sid) '数据库',
(SELECT score FROM sc WHERE cid='001' AND sid=sc1.sid) '企业管理',
(SELECT score FROM sc WHERE cid='005'AND sid=sc1.sid) '英语',
count(*) '有效课程数'
FROM sc sc1
GROUP BY sid
ORDER BY avg(score);
用到了不认识的函数，现在的水平笔试应该不会到这个水平
19、按各科平均成绩从低到高和及格率的百分数从高到低排序
oracle>
SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,nvl(AVG(score),0) AS 平均成绩
,100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
WHERE t.cid=course.cid
GROUP BY t.cid
ORDER BY 100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
Mysql>
SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,IFNULL(AVG(score),0) AS 平均成绩
,100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
WHERE t.cid=course.cid
GROUP BY t.cid
ORDER BY 100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
SELECT SUM(CASE WHEN cid ='001' THEN score ELSE 0 END)/SUM(CASE cid WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN cid = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
,SUM(CASE WHEN cid = '002' THEN score ELSE 0 END)/SUM(CASE cid WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN cid = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN cid = '003' THEN score ELSE 0 END)/SUM(CASE cid WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN cid = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '003' THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN cid = '004' THEN score ELSE 0 END)/SUM(CASE cid WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN cid = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
23、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
Mysql>
select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))='1981';
Oracle>
select * from student where substr(to_char(sage,'yyyy-MM-dd'),1,4)= '1981'
# 这么写实际上是有bug的，如果出现该课程所有成绩都相同并且课程数大于2的时候，就不会显示该课程成绩了
查询每门功课成绩最好的前两名
SELECT *
FROM sc t1
WHERE (
SELECT COUNT(*)
FROM sc t2
WHERE t1.cid=t2.cid
AND t2.score>=t1.score
) <=2 ORDER BY t1.cid
// 行转列
CREATE TABLE stu_score (
grade_id varchar(10) DEFAULT NULL,
subject_name varchar(10) DEFAULT NULL,
max_score int(10) DEFAULT NULL
)
insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('1','语文',98);
insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','数学',95);
insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','政治',87);
insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','语文',97);
insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','数学',100);
insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','政治',92);
SELECT
CASE grade_id WHEN 1 THEN '一年级'
WHEN 2 THEN '二年级'
WHEN 5 THEN '五年级'
END AS '年级',
MAX(IFNULL(CASE subject_name WHEN '语文' THEN max_score END,0)) AS '语文',
MAX(IFNULL(CASE subject_name WHEN '数学' THEN max_score END,0)) AS '数学',
MAX(IFNULL(CASE subject_name WHEN '政治' THEN max_score END,0)) AS '政治'
FROM stu_score
GROUP BY grade_id
部分知识引用自：
https://zhuanlan.zhihu.com/p/38354000
https://blog.csdn.net/hellosweet1/article/details/81637307
“老人走失后，能被寻找到的点滴踪迹是碎片式的，志愿救援队要尽量多寻找他们出现的点，再将点连成线。但每当快连成线了，线索却经常断掉。”马健说。有一次他们寻找一位老人，查到他进了一个胡同，前一个监控能看到他，隔了一段距离的监控里，就没影儿了。第二天再把范围缩小，查到中间的一个商店，发现老人晚上站在这家店的玻璃门前，对着自己的影子说了五个小时的话。可这时机一错过，他们一直跑到石景山的水泥厂才找到他。
https://new.qq.com/omn/20220329/20220329A0AQ0000.html?pgv_ref=aio2015&ptlang=2052
2858
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
