50个常用sql语句网上流行的学生选课表的例子

以你之姓@ 2022-02-22 14:44 198阅读 0赞

### 深入理解自查询和联合查询 ###

LIMIT m,n : 表示从第m+1条开始，取n条数据；

LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。

**创建以下四个表：**

Student(Sid,Sname,Sage,Ssex) 学生表

create table student(
        id int not null COMMENT 'id',
        name varchar(255) default null COMMENT '姓名',
        age int default null COMMENT '年龄',
        sex int default null COMMENT '性别 1:女;2:男',
        primary key (id)
    )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC  COMMENT "学生表";

插入数据：

insert into student(id,name,age,sex) values
    (1,"天明",17,2),
    (2,"月儿",15,1),
    (3,"卫庄",25,2),
    (4,"盗跖",16,2),
    (5,"金庸",20,1),
    (6,"田言",20,1),
    (7,"柯洁",18,2);

Teacher(Tid,Tname) 教师表

CREATE TABLE `teacher` (
      id int NOT NULL COMMENT 'id',
      name varchar(255) DEFAULT NULL COMMENT '姓名',
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='教师表';

插入数据：

insert into teacher values
    (1,"墨子"),
    (2,"鬼谷子"),
    (3,"东皇太一"),
    (4,"伏念"),
    (5,"盖聂");

Course(Cid,Cname,Tid) 课程表

CREATE TABLE `course` (
        id int NOT NULL COMMENT 'id',
        name varchar(255) DEFAULT NULL COMMENT '课程名',
        teacher_id int default null comment '教师id',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='课程表';

插入数据：

insert into course values
    (1,"语文",1),
    (2,"数学",1),
    (3,"英语",2),
    (4,"物理",2),
    (5,"地理",3),
    (6,"化学",3),
    (7,"生物",4),
    (8,"历史",5),
    (9,"美术",5);

SC(Sid,Cid,score) 成绩表

CREATE TABLE `mark` (
        id int NOT NULL comment 'id',
        student_id int default null comment '学生id',
        course_id int default null comment '课程id',
        score int default null comment '分数',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='学生分数表';

插入数据：

insert into mark values
    (1,1,1,80),
    (2,1,2,60),
    (3,1,3,50),
    (4,1,4,10),
    (5,1,5,0),
    (6,2,1,50),
    (7,2,1,50),
    (8,2,7,60),
    (9,3,1,0),
    (10,3,2,10),
    (11,3,3,90),
    (12,3,4,100),
    (13,4,2,80),
    (14,4,3,70),
    (15,5,1,30),
    (16,5,2,40);

**问题： **

1.  查询“1”课程比“2”课程成绩高的所有学生的姓名; 
    
        select s1.`name`
        from mark m1 
        inner join mark m2 on m1.student_id=m2.student_id
        inner join student s1 on s1.id=m1.student_id
        where m1.course_id=1 and m2.course_id=2 and m1.score>m2.score;
        
        select s1.name from 
        (select m1.student_id from 
        (select student_id,score from mark where course_id=1) m1,
        (select student_id,score from mark where course_id=2) m2
        where m1.student_id=m2.student_id and m1.score>m2.score
        ) t1
        inner join student s1 on s1.id=t1.student_id;
2.  查询平均成绩大于60分的同学的姓名和平均成绩; 
    
        select student.name,avg(mark.score) sc
        from mark 
        left join student on student.id=mark.student_id
        group by mark.student_id 
        having sc>60;
3.  查询所有同学的学号、姓名、选课数、总成绩; 
    
        select student.id,student.name,sum(mark.score) allScore,count(mark.id) allCourse 
        from mark 
        left join student on student.id=mark.student_id
        group by mark.student_id;
4.  查询姓“鬼”的老师的个数; 
    
        select count(*) from teacher
        where name like '鬼%';
5.  查询没学过“鬼谷子”老师课的同学的学号、姓名; 
    
        SELECT Sid,Sname FROM student
        WHERE Sid NOT IN(
        SELECT sid FROM sc,course,teacher 
        WHERE tname="鬼谷子" AND course.tid=teacher.tid
        AND sc.cid=course.cid);
6.  查询学过“1”并且也学过编号“2”课程的同学的学号、姓名; 
    
        SELECT sid,sname FROM student 
        WHERE sid IN (
        SELECT a.sid FROM 
        (SELECT sid FROM sc WHERE cid=1 GROUP BY sid) a,
        (SELECT sid FROM sc WHERE cid=2 GROUP BY sid) b
        WHERE a.sid=b.sid
        );
7.  查询学过“鬼谷子”老师所教的所有课的同学的学号、姓名; 
    
        第一种：
        SELECT sid,COUNT(cid) FROM sc 
        WHERE cid IN(
        	#鬼谷子老师所教的课程的cid
        	SELECT cid FROM course,teacher 
        	WHERE course.tid=teacher.tid AND teacher.tname="鬼谷子")
        GROUP BY sid
        #同学所学鬼谷子老师的课程的数目要等于鬼谷子老师所教的课程的数据
        HAVING COUNT(cid)=(SELECT COUNT(cid) FROM course,teacher 
        	WHERE course.tid=teacher.tid AND teacher.tname="鬼谷子");
8.  查询至少学过学号为“1”同学所有门课的其他同学学号和姓名; 和第7题的关键点相同
    
        第一种：
        SELECT sid,sname FROM student WHERE sid IN (
            #查询（所学课程和1号学生所学课程相同的课程）的门数的学生ID
        	SELECT sid FROM sc WHERE cid IN(
        		SELECT cid FROM sc WHERE sid=1)
        	GROUP BY sid 
        	HAVING COUNT(sc.cid)=(SELECT COUNT(cid) FROM sc WHERE sid=1)
        ) and sid <>1;
        
        第二种：
        SELECT sc.sid,sname,COUNT(cid) FROM sc,student
        WHERE cid IN (SELECT cid FROM sc WHERE sid=1) AND sc.sid<>1 AND sc.sid=student.sid
        GROUP BY sid 
        HAVING COUNT(cid)=(SELECT COUNT(cid) FROM sc WHERE sid=1);
9.  查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名;
    
        SELECT sid,COUNT(cid) FROM sc WHERE sid IN (
        	SELECT sid FROM sc 
        	WHERE cid IN (SELECT cid FROM sc WHERE sid=2) AND sid <>2
        	GROUP BY sid
        	HAVING COUNT(cid)=(SELECT COUNT(cid) FROM sc WHERE sid=2)
        )
        GROUP BY sid 
        HAVING COUNT(cid)=(SELECT COUNT(cid) FROM sc WHERE sid=2);
10. 查询课程编号“1”的成绩比课程编号“2”课程低的所有同学的学号、姓名; 
    
        SELECT sid,sname FROM student WHERE sid IN (
        	SELECT sc1.sid FROM sc sc1,sc sc2
        	WHERE sc1.sid=sc2.sid 
        		AND sc1.cid=1 
        		AND sc2.cid=2 
        		AND sc1.score<sc2.score
        );
11. 查询所有课程成绩小于60分的同学的学号、姓名;  
    
        SELECT sid,sname
        FROM student
        WHERE sid NOT IN(
        	SELECT DISTINCT sid
        	FROM sc
        	WHERE score>60
        );
12. 查询没有学全所有课的同学的学号、姓名;
    
        SELECT sid,sname FROM student WHERE sid IN(
        	SELECT sid FROM sc 
        	GROUP BY sid 
                HAVING COUNT(sc.cid) < (SELECT COUNT(cid)FROM course)
        );
    
        SELECT student.sid,sname FROM student,sc
        	WHERE student.sid=sc.sid 
                GROUP BY sid 
                HAVING COUNT(cid)<(SELECT COUNT(cid) FROM course);
13. 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名;   
     
    
        SELECT DISTINCT student.sid,sname FROM student,sc
        	WHERE student.sid=sc.sid 
                AND sid !=1 
                AND cid IN(
        		SELECT cid FROM sc WHERE sid=1
        	);
14. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
    
        SELECT cid,MAX(score) 最高分,MIN(score) 最低分 FROM sc
        GROUP BY cid;
15. 查询课程ID，各科平均成绩从低到高和及格率
    
        SELECT a.cid,b.avgs 平均成绩,a.ss/b.ss 及格率 FROM 
            (SELECT cid,SUM(score) ss FROM sc WHERE score>=60 GROUP BY cid) a,
            (SELECT cid,SUM(score) ss,AVG(score) avgs FROM sc GROUP BY cid) b
        WHERE a.cid=b.cid
        ORDER BY 平均成绩 ASC;
16. 查询不同老师所教不同课程平均分从低到高显示 
    
        SELECT sc.cid,teacher.tid 教师ID,tname 教师名字,AVG(score) 平均分 FROM sc,course,teacher
        WHERE sc.cid=course.cid AND course.tid=teacher.tid
        GROUP BY cid 
        ORDER BY 平均分 ASC;
17. 查询男生、女生人数 
    
        SELECT aa.女,bb.男 FROM
        (SELECT COUNT(ssex) 女 FROM student 
        WHERE ssex='女' 
        GROUP BY ssex) aa,
        (SELECT COUNT(ssex) 男 FROM student 
        WHERE ssex='男' 
        GROUP BY ssex) bb;
18. 检索“2”课程分数小于60，按分数降序排列的同学学号 
    
        SELECT sid,score FROM sc 
        WHERE cid=2 AND score >60 
        ORDER BY score DESC;
19. 查询每门课程被选修的学生数 
    
        SELECT cid,COUNT(cid) FROM sc
        GROUP BY cid;
20. 查询出只选修了一门课程的全部学生的学号和姓名 
    
        SELECT student.sid,student.sname FROM sc,student
        WHERE sc.sid=student.sid
        GROUP BY sid
        HAVING COUNT(sc.sid)=1;
21. 查询各科成绩前三名的记录:(不考虑成绩并列情况) 
    
        SELECT t1.sid,t1.cid,Score
        FROM SC t1
        WHERE
        #在同一个课程下，比此成绩高的学生的个数大于3就不符合要求
        (SELECT COUNT(cid)
        FROM SC
        WHERE t1.cid= cid AND t1.score<score)<3
        #按照cid排序，cid相同的情况按照score排序
        ORDER BY t1.cid,score DESC;
22. 查询不及格的课程，并按课程号从大到小排列 
    
        SELECT cid FROM sc WHERE score<60 ORDER BY cid DESC;
23. 查询课程编号为3且课程成绩在60分以上的学生的学号和姓名; 
    
        SELECT student.sid,sname FROM student,sc 
        WHERE cid=3 AND score > 60 AND student.sid=sc.sid;
24. 查询选修“鬼谷子”老师所授课程的学生中，成绩最高的学生姓名及其成绩 
    
        SELECT sc.sid,sname,score FROM student,sc 
        WHERE sc.sid=student.sid AND cid IN(
                SELECT cid FROM course,teacher 
                WHERE  course.tid=teacher.tid AND teacher.tname="鬼谷子") 
        ORDER BY score DESC LIMIT 1;
        
        
        SELECT sname,score FROM student
        LEFT JOIN sc ON sc.sid=student.sid
        LEFT JOIN course ON course.cid=sc.cid
        LEFT JOIN teacher ON teacher.tid=course.tid
        WHERE teacher.tname='鬼谷子'
        ORDER BY score DESC LIMIT 0,1;
25. 查询各个课程及相应的选修人数 
    
        SELECT cid,COUNT(sid) FROM sc GROUP BY cid;
26. 查询不同课程成绩相同的学生的学号、课程号、学生成绩 
    
        SELECT DISTINCT aa.sid,aa.cid,aa.score FROM sc aa,sc bb 
        WHERE aa.score=bb.score AND aa.sid <> bb.sid;
27. 查询学生平均成绩及其名次 
    
        SELECT 1+(
                #根据sid，平均成绩组合的两个相同的表，
        	SELECT COUNT(DISTINCT 平均成绩1)
        	FROM (SELECT sid,AVG(score) AS 平均成绩1 FROM sc GROUP BY sid) AS T1
        	WHERE 平均成绩1 > T2.平均成绩2
        )AS 名次,sid AS 学生学号,平均成绩2
        FROM(
        	SELECT sid,AVG(score) 平均成绩2
        	FROM sc
        	GROUP BY sid
        )AS T2
        ORDER BY 平均成绩2 DESC;
28. 检索至少选修两门课程的学生学号 
    
        SELECT sid,COUNT(cid) 
        FROM sc 
        GROUP BY sid 
        HAVING COUNT(cid)>1;
29. 查询全部学生都选修的课程的课程号和课程名 
    
        SELECT COUNT(sid) 
        FROM sc 
        GROUP BY cid 
        HAVING COUNT(sid) = (SELECT COUNT(cid) FROM course);
30. 查询没学过“鬼谷子”老师讲授的任一门课程的学生姓名 
    
        SELECT sid,sname FROM student WHERE sid NOT IN (
        	SELECT sc.sid FROM sc,course,teacher 
        	WHERE sc.cid=course.cid AND course.tid=teacher.tid AND teacher.tname="鬼谷子"
        );
31. 查询两门以上不及格课程的同学的学号及其平均成绩 
    
        SELECT sid,AVG(score) FROM sc 
        GROUP BY sid 
        HAVING sid IN (
            SELECT sid FROM sc WHERE score <60 GROUP BY sid HAVING COUNT(cid)>2
        );
32. 查询姓“田”的学生名单 
    
        SELECT COUNT(sid) FROM student WHERE sname LIKE "田%";
33. 查询同名同性学生名单，并统计同名人数 
    
        select Sname,count(*) from Student group by Sname having count(*)>1;
34. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 
    
        SELECT cid,AVG(score) FROM sc GROUP BY AVG(score),cid DESC;
35. 查询平均成绩大于60的所有学生的学号、姓名和平均成绩 
    
        SELECT student.sid,sname,AVG(score) FROM student,sc
        WHERE student.sid=sc.sid 
        GROUP BY sc.sid
        HAVING AVG(score) > 60;
36. 查询课程名称为“数据库”，且分数低于60的学生姓名和分数 
    
        SELECT sname,score FROM student,sc,course
        WHERE student.sid=sc.sid AND sc.cid=course.cid AND course.cname="百步飞剑" AND score<60;
37. 查询所有学生的选课情况; 
    
        SELECT student.sid,sname,sc.cid,score,cname 
        FROM student,sc,course
        WHERE student.sid=sc.sid AND sc.cid=course.cid;
38. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数; 
    
        SELECT sname,cname,score 
        FROM student,sc,course
        WHERE student.sid=sc.sid AND sc.cid=course.cid AND sc.score>70;
39. 统计每门课程的学生选修人数（超过5人的课程才统计）。  
    要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列 
    
        SELECT cid,COUNT(sid) FROM sc 
        GROUP BY cid 
        HAVING COUNT(sid)>5 
        ORDER BY COUNT(sid) DESC,cid;
40. 统计各科成绩,各分数段人数:课程ID,课程名称,\[100-85\],\[85-70\],\[70-60\],\[ <60\] 
    
        SELECT sc.cid,cname,aa.a1 AS '[100-85]',bb.a1 '[85-70]',cc.a1 '[70-60]',dd.a1 '[<60]'
        FROM sc,course,
        (SELECT cid,COUNT(sid) a1 FROM sc WHERE score>=85 GROUP BY cid) aa,
        (SELECT cid,COUNT(sid) a1 FROM sc WHERE 85>score>=70 GROUP BY cid) bb,
        (SELECT cid,COUNT(sid) a1 FROM sc WHERE 70>score>=60 GROUP BY cid) cc,
        (SELECT cid,COUNT(sid) a1 FROM sc WHERE score<60 GROUP BY cid) dd
        WHERE course.cid=sc.cid 
        AND sc.cid=aa.cid 
        AND sc.cid=bb.cid 
        AND sc.cid=dd.cid 
        AND sc.cid=cc.cid;
41. 把“SC”表中“鬼谷子”老师教的课的成绩都更改为此课程的平均成绩; 
    
        方法一：
        UPDATE SC,Course,Teacher  SET score=
        (SELECT AVG(SC_2.score) from SC SC_2 where SC_2.Cid=SC.Cid)  
        WHERE SC.Cid=Course.Cid AND Course.Tid=Teacher.Tid AND Teacher.Tname='鬼谷子';
        
        方法二：
        UPDATE sc,
        (SELECT sc.cid,AVG(sc.score) avgs 
        FROM sc,course,teacher
        WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname="鬼谷子"  
        GROUP BY sc.cid) target
        SET score=target.avgs WHERE sc.cid= target.cid;
42. 删除学习“鬼谷子”老师课的SC表记录; 
    
        DELETE sc
        FROM course,teacher
        WHERE sc.cid=course.cid AND course.tid=teacher.tid AND tname="鬼谷子";
43. 向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“3”课程的同学的成绩为学号“2” 号课的平均成绩; 
    
        INSERT sc
        SELECT Sid,3,(SELECT AVG(score) FROM sc WHERE cid=2) FROM Student
        WHERE Sid NOT IN (SELECT Sid FROM SC WHERE Cid=3); 
44. 面试题
    
    1.  1  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70][]  
        使用case 字段 when 值 then  else end  
        ![20200504005715901.png][]
    2.  bb

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70]: /images/20220222/375cd7c8adb74a8f9f201bbc1f9eeaab.png
[20200504005715901.png]: /images/20220222/8e2dc065a6fa492fb9cabeee64c9271d.png