所涉及的表:
student 表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(0) NOT NULL,
`sname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`sage` int(0) NULL DEFAULT NULL,
`ssex` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, '龙大', 18, '男');
INSERT INTO `student` VALUES (102, '熊二', 19, '男');
INSERT INTO `student` VALUES (103, '张三', 18, '男');
INSERT INTO `student` VALUES (104, '李四', 19, '女');
INSERT INTO `student` VALUES (105, '王五', 20, '男');
INSERT INTO `student` VALUES (106, '李华', 19, '男');
INSERT INTO `student` VALUES (107, '李红', 19, '女');
INSERT INTO `student` VALUES (108, '李明', 20, '男');
INSERT INTO `student` VALUES (109, '贝贝', 19, '女');
INSERT INTO `student` VALUES (110, '娜娜', 20, '女');
SET FOREIGN_KEY_CHECKS = 1;
teacher 表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(0) NOT NULL,
`tname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '叶平');
INSERT INTO `teacher` VALUES (2, '李龙');
INSERT INTO `teacher` VALUES (3, '李逍遥');
INSERT INTO `teacher` VALUES (4, '朱钊');
INSERT INTO `teacher` VALUES (5, '李逍遥');
SET FOREIGN_KEY_CHECKS = 1;
sc 表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sid` int(0) NOT NULL COMMENT '学号',
`cid` int(0) NULL DEFAULT NULL COMMENT '课程号',
`score` int(0) NULL DEFAULT NULL COMMENT '分数'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (101, 3001, 90);
INSERT INTO `sc` VALUES (102, 3001, 85);
INSERT INTO `sc` VALUES (103, 3001, 76);
INSERT INTO `sc` VALUES (105, 3001, 87);
INSERT INTO `sc` VALUES (106, 3001, 66);
INSERT INTO `sc` VALUES (108, 3001, 96);
INSERT INTO `sc` VALUES (101, 3002, 92);
INSERT INTO `sc` VALUES (102, 3002, 81);
INSERT INTO `sc` VALUES (103, 3002, 93);
INSERT INTO `sc` VALUES (104, 3002, 73);
INSERT INTO `sc` VALUES (105, 3002, 65);
INSERT INTO `sc` VALUES (108, 3002, 96);
INSERT INTO `sc` VALUES (101, 3003, 96);
INSERT INTO `sc` VALUES (102, 3003, 85);
INSERT INTO `sc` VALUES (103, 3003, 76);
INSERT INTO `sc` VALUES (104, 3003, 63);
INSERT INTO `sc` VALUES (105, 3003, 59);
INSERT INTO `sc` VALUES (106, 3003, 56);
INSERT INTO `sc` VALUES (107, 3003, 91);
INSERT INTO `sc` VALUES (108, 3003, 86);
INSERT INTO `sc` VALUES (101, 3004, 100);
INSERT INTO `sc` VALUES (102, 3004, 83);
INSERT INTO `sc` VALUES (103, 3004, 75);
INSERT INTO `sc` VALUES (104, 3004, 69);
INSERT INTO `sc` VALUES (105, 3004, 50);
INSERT INTO `sc` VALUES (106, 3004, 52);
INSERT INTO `sc` VALUES (107, 3004, 87);
INSERT INTO `sc` VALUES (108, 3004, 78);
INSERT INTO `sc` VALUES (109, NULL, NULL);
INSERT INTO `sc` VALUES (110, NULL, NULL);
SET FOREIGN_KEY_CHECKS = 1;
course 表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(0) NOT NULL,
`cname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`tid` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (3001, '语文', 4);
INSERT INTO `course` VALUES (3002, '数学', 2);
INSERT INTO `course` VALUES (3003, '英语', 1);
INSERT INTO `course` VALUES (3004, '物理', 3);
SET FOREIGN_KEY_CHECKS = 1;
-- 所用到的表 student ; sc ; course ; teacher;
(1)查询“3001”课程的所有学生的学号与分数;
SELECT sid,score FROM sc WHERE cid='3001';
SELECT * FROM sc WHERE cid='3001';
(2)查询“3001”课程比“3002”课程成绩高的所有学生的学号与分数;
SELECT sid,score
FROM sc WHERE
SELECT score from sc where cid='3001' or cid='3002';
SELECT score from sc where cid='3001' and sid ='101'
SELECT score from sc where cid='3002' and sid ='101'
SELECT a.sid,a.score from (SELECT sid, score from sc where cid='3001') as a ,(SELECT sid, score from sc where cid='3002') as b
WHERE a.score > b.score and a.sid=b.sid
-- 答案:
SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="3001") a,
(SELECT sid,score FROM sc WHERE cid="3002") b
WHERE a.score>b.score AND a.sid=b.sid
(3)查询每个同学的所有课程的平均成绩大于60分的同学的学号和平均成绩;
SELECT AVG(score) from sc WHERE sid='101'
SELECT * from sc WHERE sid='101'
SELECT sc.sid, AVG(score) from sc GROUP BY sc.sid HAVING AVG(score)>60
-- 答案1:
SELECT *
FROM (SELECT AVG(score) a ,sid from sc GROUP BY sid ) b
WHERE b.a >60
-- 标准答案2: 分组后筛选用 having
SELECT sid,AVG(score)
FROM sc
GROUP BY sid HAVING AVG(score)>60
(4)查询所有同学的学号、姓名、选课数、总成绩
SELECT *
FROM student s,sc
WHERE s.sid=sc.sid
SELECT s.sid ,s.sname ,
FROM sc ,student s
WHERE s.sid=sc.sid
SELECT sid, COUNT(*),sum(score) from sc GROUP BY sid ORDER BY sid
写法1
SELECT s.sname,b.*
FROM student s ,
(SELECT sid, COUNT(*),sum(score) from sc GROUP BY sid ORDER BY sid ) b
WHERE s.sid=b.sid
写法2
SELECT s.sid 学号,s.sname 姓名,b.c 选课数 ,b.d 总成绩
FROM student s ,
(SELECT sid, COUNT(*) c,sum(score) d from sc GROUP BY sid ORDER BY sid ) b
WHERE s.sid=b.sid
写法3
select s.sid as 学号,s.sname as 姓名,count(sc.cid) as 选课数,SUM(sc.score) as 总成绩
FROM student s,sc
WHERE s.sid=sc.sid
GROUP BY s.sid
标准答案:
select s.sid as 学号,s.sname as 姓名,count(sc.cid) as 选课数,SUM(sc.score) as 总成绩
from student s INNER JOIN sc sc
on s.sid=sc.sid
GROUP BY s.sid
select s.sid as 学号,s.sname as 姓名,count(sc.cid) as 选课数,SUM(sc.score) as 总成绩
from student s INNER JOIN sc
on s.sid=sc.sid
GROUP BY sc.sid
select *
from student s INNER JOIN sc
on s.sid=sc.sid
GROUP BY sc.sid
我的答案:
select s.sid as 学号,s.sname as 姓名,count(sc.sid) as 选课数,SUM(sc.score) as 总成绩
from student s INNER JOIN sc sc
on s.sid=sc.sid
GROUP BY s.sid
(5)查询姓“李”的老师的个数; count(*)
select count(tname)
from teacher
WHERE tname like '李%';
标准答案:去重了
select count(distinct(Tname))
from teacher
where tname like '李%';
(6)查询学过“叶平”老师课的同学的学号、姓名
第一步:查询叶平老师的课程编号(子查询)
select cid
from course
where tid in(SELECT tid from teacher where tname='叶平')
第二步:根据课程编号查询 对应的学生学号(子查询)
select sid
from sc
where cid in(select cid from course where tid in(SELECT tid from teacher where tname='叶平'))
第三步:根据学生学号 查询学生(子查询)
select sid ,sname
from student
WHERE sid in (select sid from sc where cid in(select cid from course where tid in(SELECT tid from teacher where tname='叶平')))
答案:
SELECT s.sid AS "学号", s.sname AS "姓名"
FROM student s, sc sc, course c, teacher t
WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="叶平"
(7)查询没有学过“叶平”老师课的同学的学号、姓名
select sid ,sname
from student
WHERE sid not in (select sid from sc where cid in(select cid from course where tid in(SELECT tid from teacher where tname='叶平')))
答案:
SELECT s.sid, s.sname
FROM student s
WHERE s.sid NOT IN (
SELECT s.sid
FROM student s, sc sc, course c, teacher t
WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="叶平"
)
(8)查询学过“3001”并且也学过编号“3002”课程的同学的学号、姓名
第一种:(1)根据3001 可以查到那些学生学了3001课程 a
(2)根据3002 可以查到那些学生学了3002课程 b
两张表通过学号关联 可以查出都选的学号
通过学号可以查询到学生姓名
SELECT sid FROM sc WHERE cid='3001' a
SELECT sid FROM sc WHERE cid='3002' b
SELECT a.sid ,s.sname
from (SELECT sid FROM sc WHERE cid='3001') a,
(SELECT sid FROM sc WHERE cid='3002') b,
student s
WHERE a.sid=b.sid and a.sid=s.sid
答案:
SELECT s.sid, s.sname
FROM student s, sc sc
WHERE s.sid=sc.sid AND sc.cid="3001" AND EXISTS(
SELECT * FROM sc AS sc2 WHERE sc2.sid=sc.sid AND sc2.cid="3002"
)
(9)查询学过“叶平”老师所教的所有课的同学的学号、姓名;
(10)查询所有课程成绩都小于60分的同学的学号、姓名
标准答案:
select DISTINCT(s.sid) , s.sname
from student s, sc
WHERE s.sid=sc.sid and sc.score<60
SELECT sid, sname FROM student
WHERE sid NOT IN (
SELECT DISTINCT(sc.sid) FROM student s, sc sc
WHERE sc.sid=s.sid AND sc.score>60)
(11)查询没有学全所有课的同学的学号、姓名;
查询都有哪些课程编号
select cid from course
select *
from sc
where sc.cid in(select cid from course)
select count(*) c,sid
from sc GROUP BY sid having c<4
(SELECT sid
from (select count(*),sid from sc GROUP BY sid having count(*)<4) b) c
方式1:
select s.sid ,s.sname
from student s,
(select count(*),sid from sc GROUP BY sid having count(*)<4) b
where s.sid=b.sid
方式2:
select s.sid ,s.sname
from student s
where s.sid in(SELECT sid
from (select count(*),sid from sc GROUP BY sid having count(*)<4) b)
方式3:
select s.sid ,s.sname
from student s
where s.sid in(SELECT sid
from (select count(*),sid from sc GROUP BY sid having count(*)<(SELECT COUNT(cid) FROM course)) b)
答案:
SELECT sid, sname FROM student
WHERE sid NOT IN(
SELECT s.sid FROM student s, sc sc
WHERE sc.sid=s.sid
GROUP BY s.sid
HAVING COUNT(sc.cid)=(
SELECT COUNT(cid) FROM course))
查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT cid, max(score),min(score)
from sc
group by cid
答案:
SELECT cid AS "课程ID", MAX(score) AS "最高分", MIN(score) AS "最低分"
FROM sc
GROUP BY cid
(13)按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT avg(score),
from sc
group by cid ORDER BY desc
答案:
(方式一)
SELECT sc.cid AS "课程ID",c.cname AS "课程名", AVG(sc.score) AS "平均成绩",
SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 AS "及格百分数"
FROM sc sc, course c
WHERE sc.cid=c.cid
GROUP BY sc.cid
ORDER BY AVG(sc.score) ASC,
SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 DESC
(方式二)
SELECT sc.cid AS "课程ID",c.cname AS "课程名", IFNULL(AVG(sc.score),0) AS "平均成绩",
100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS "及格百分数"
FROM sc sc, course c
WHERE sc.cid = c.cid
GROUP BY sc.cid
ORDER BY AVG(sc.score) ASC,
100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
(14)查询所有学生的所有科目的成绩单(学号、姓名、语文、数学、英语、物理、平均分、总分(按照总分由高到低排序))
查询有哪些课程
SELECT c.cid
from course c group by c.cname
SELECT s.sid ,s.sname,c.cname
from sc sc, course c,student s
where sc.sid=s.sid and sc.cid=c.cid
答案:
SELECT s.sid AS "学号", s.sname AS "姓名",
SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",
SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",
SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "总分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
ORDER BY IFNULL(SUM(sc.score),0) DESC
(15)查询所有学生的信息 并按首字母排序 -- gbk_chinese_ci
select s.sname from student s
ORDER BY CONVERT(s.sname USING 'gbk') ASC
(15)查询总分排名在200-300(包含200和第300)之间的学生所有成绩单信息
SELECT s.sid AS "学号", s.sname AS "姓名",
SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",
SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",
SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "总分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
HAVING IFNULL(SUM(sc.score),0) BETWEEN 200 AND 300
ORDER BY IFNULL(SUM(sc.score),0) DESC
(16)查询总分排名在前四名的学生所有成绩单信息
SELECT s.sid AS "学号", s.sname AS "姓名",
SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",
SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",
SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "总分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
ORDER BY IFNULL(SUM(sc.score),0) DESC
LIMIT 0,4
(17)查询总分排名在前二名到四名的学生所有成绩单信息(limit 1,3表示从第二条数据开始,连续三条数据)
SELECT s.sid AS "学号", s.sname AS "姓名",
SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",
SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",
SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "总分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
ORDER BY IFNULL(SUM(sc.score),0) DESC
LIMIT 1,3
(18)查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT sid,AVG(score) AS 平均成绩 FROM sc GROUP BY sid ) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次, sid as 学生学号,平均成绩
FROM (SELECT sid,AVG(score) 平均成绩
FROM sc GROUP BY sid ) AS T2
ORDER BY 平均成绩 desc