mysql 15道语句练习题
表名和字段
–1.学生表
Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) --教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) --学生编号,课程编号,分数
ps:插入数据就不演示了
练习题及语句
1 -- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 2 SELECT S.s_id AS 学生编号,S.s_name AS 学生姓名, A.s_score AS 分数 3 FROM Student AS S 4 INNER JOIN Score AS A 5 ON S.s_id=A.s_id and A.c_id='01' 6 LEFT JOIN Score as C 7 ON C.s_id=S.s_id and C.c_id='02' 8 WHERE A.c_id>C.c_id; 9 10 -- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 11 SELECT S.s_id AS 学生编号,S.s_name AS 学生姓名, A.s_score AS 分数 12 FROM Student AS S 13 INNER JOIN Score AS A 14 ON S.s_id=A.s_id and A.c_id='01' 15 LEFT JOIN Score as C 16 ON C.s_id=S.s_id and C.c_id='02' 17 WHERE A.c_id<C.c_id; 18 19 -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 20 SELECT S.s-id AS 学生编号,S.s_name AS 学生姓名,AVG(C.s_score) AS 平均成绩 21 FROM Student AS S 22 INNER JOIN Score AS C 23 ON S.s_id=C.s_id 24 GROUP BY S.s_id,S.s_name,C.s_id 25 HAVING AVG(C.s_score)>=60; 26 27 -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 28 -- (包括有成绩的和无成绩的) 29 30 select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 31 student b 32 left join score a on b.s_id = a.s_id 33 GROUP BY b.s_id,b.s_name HAVING avg_score <60 34 union 35 select a.s_id,a.s_name,0 as avg_score from 36 student a 37 where a.s_id not in ( 38 select distinct s_id from score); 39 40 -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 41 42 SELECT S.s_id AS 学生编号,S.s_name AS 学生姓名,COUNT(C,c_id) AS 选课总数,SUM(C.s_score) AS 总成绩 43 FROM Student AS S 44 INNER JOIN Score AS C ON S.s_id=C.s_id 45 GROUP BY S.s_id,S.s_name ; 46 47 -- 6、查询"李"姓老师的数量 48 select count(t_id) from teacher where t_name like '李%'; 49 50 -- 7、查询学过"张三"老师授课的同学的信息 51 SELECT S.s_id AS 学生编号, S.s_name AS 学生姓名,S.s_sex AS 性别 52 FROM Student AS S WHERE S.s_id IN 53 (SELECT G.s_id FROM Score AS G 54 WHERE G.s_id=S.s_id AND G.c_id IN 55 (SELECT C.c_id FROM Couse AS C 56 WHERE C.c_id=G.c_id AND C.t_id IN 57 (SELECT T.t_id FROM Teacher AS T WHERE T.t-id=C.t_id AND T.t_name = '张三'))); 58 59 -- 8、查询没学过"张三"老师授课的同学的信息 60 61 SELECT S.s_id AS 学生编号, S.s_name AS 学生姓名,S.s_sex AS 性别 62 FROM Student AS S WHERE S.s_id IN 63 (SELECT G.s_id FROM Score AS G 64 WHERE G.s_id=S.s_id AND G.c_id IN 65 (SELECT C.c_id FROM Couse AS C 66 WHERE C.c_id=G.c_id AND C.t_id IN 67 (SELECT T.t_id FROM Teacher AS T WHERE T.t-id=C.t_id AND T.t_name ! = '张三'))); 68 69 -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 70 71 Select * 72 From student 73 Where s_id=(Select s_id 74 From score as s01 ,score as s02 75 Where s01.s_score=’01’and s02.s_score=’02’ 76 ) 77 78 79 -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 80 Select * 81 From student AS s 82 INNER JOIN Score as a 83 On s.s_id =a.s_id 84 Group by s.c_id 85 Having (select s_id From Score Where c_id =’01’)!=(select s_id 86 From score 87 Where c_id =’02’); 88 89 -- 11、查询没有学全所有课程的同学的信息 90 Select s_id,s_name,s_birth ,s_sex 91 From Student 92 where s_id not in ( 93 select s_id 94 from Score 95 where c_id = ‘01’) or 96 s_id not in( 97 select s_id 98 from Score 99 where c_id = ‘02’); 100 101 -- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 102 Select * 103 From student 104 Where s_id=( 105 Select s_id 106 From score 107 Where c_id on( 108 Select c_id 109 From score 110 Where s_id=’01’ 111 )) 112 113 -- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 114 115 Select * 116 From student 117 Where s_id=( 118 Select s_id 119 From score 120 Where c_id =( 121 Select c_id 122 From score 123 Where s_id=’01’ 124 )) 125 126 -- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 127 128 SELECT S.s_name AS 学生姓名 129 FROM Student AS S WHERE S.s_id NOT IN 130 (SELECT G.s_id FROM Score AS G 131 WHERE G.s_id=S.s_id AND G.c_id IN 132 (SELECT C.c_id FROM Couse AS C 133 WHERE C.c_id=G.c_id AND C.t_id IN 134 (SELECT T.t_id FROM Teacher AS T WHERE T.t-id=C.t_id AND T.t_name = '张三'))); 135 136 -- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 137 138 SELECT s.s_id as 学号,s.s_name as学生姓名 AVG(a.s_score) 139 FROM Score AS a,Student as s 140 GROUP BY s_id 141 HAVING COUNT(c_id)>=2 AND a.s_core<60
还有什么不足的地方请大佬赐教!!!