select student.s_id,s_name,s_birth,s_sex,s_score from student,score where student.s_id=score.s_id;--建表
--学生表
CREATETABLE`Student`(`s_id`VARCHAR(20),`s_name`VARCHAR(20)NOTNULLDEFAULT'',`s_birth`VARCHAR(20)NOTNULLDEFAULT'',`s_sex`VARCHAR(10)NOTNULLDEFAULT'',PRIMARYKEY(`s_id`));--课程表
CREATETABLE`Course`(`c_id`VARCHAR(20),`c_name`VARCHAR(20)NOTNULLDEFAULT'',`t_id`VARCHAR(20)NOTNULL,PRIMARYKEY(`c_id`));--教师表
CREATETABLE`Teacher`(`t_id`VARCHAR(20),`t_name`VARCHAR(20)NOTNULLDEFAULT'',PRIMARYKEY(`t_id`));--成绩表
CREATETABLE`Score`(`s_id`VARCHAR(20),`c_id`VARCHAR(20),`s_score`INT(3),PRIMARYKEY(`s_id`,`c_id`));--插入学生表测试数据
insert into Student values('01','赵雷','1990-01-01','男');
insert into Student values('02','钱电','1990-12-21','男');
insert into Student values('03','孙风','1990-05-20','男');
insert into Student values('04','李云','1990-08-06','男');
insert into Student values('05','周梅','1991-12-01','女');
insert into Student values('06','吴兰','1992-03-01','女');
insert into Student values('07','郑竹','1989-07-01','女');
insert into Student values('08','王菊','1990-01-20','女');--课程表测试数据
insert into Course values('01','语文','02');
insert into Course values('02','数学','01');
insert into Course values('03','英语','03');--教师表测试数据
insert into Teacher values('01','张三');
insert into Teacher values('02','李四');
insert into Teacher values('03','王五');--成绩表测试数据
insert into Score values('01','01',80);
insert into Score values('01','02',90);
insert into Score values('01','03',99);
insert into Score values('02','01',70);
insert into Score values('02','02',60);
insert into Score values('02','03',80);
insert into Score values('03','01',80);
insert into Score values('03','02',80);
insert into Score values('03','03',80);
insert into Score values('04','01',50);
insert into Score values('04','02',30);
insert into Score values('04','03',20);
insert into Score values('05','01',76);
insert into Score values('05','02',87);
insert into Score values('06','01',31);
insert into Score values('06','03',34);
insert into Score values('07','02',89);
insert into Score values('07','03',98);
习题
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.*,b.s_02 FROM(SELECT student.*,score.s_score AS s_01 FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='01')AS a LEFTJOIN(SELECT student.*,score.s_score AS s_02 FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='02')AS b ON a.s_id=b.s_id WHERE a.s_01>b.s_02
SELECT
score1.s_id,
score1.s_score c01,
score2.s_score c02
FROM
score score1
LEFTJOIN score score2 ON score1.s_id = score2.s_id
WHERE
score1.c_id ='01'AND score2.c_id ='02'GROUPBY
score1.s_id
HAVING
c01 > c02
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT*FROM(SELECT student.*,score.s_score AS s_01 FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='01')AS a LEFTJOIN(SELECT student.*,score.s_score AS s_02 FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='02')AS b ON a.s_id=b.s_id WHERE a.s_01<b.s_02
--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.s_id,student.s_name,a.avg1 FROM(SELECT s_id,AVG(s_score) avg1 FROM score GROUPBY s_id HAVING avg1 >=60) a,
student WHERE a.s_id = student.s_id
SELECT
a.s_id,
student.s_name,
a.avg1
FROM(SELECT s_id,AVG( s_score ) avg1 FROM score GROUPBY s_id HAVINGAVG( s_score )>=60) a
JOIN student ON a.s_id = student.s_id
SELECT
student.s_id,
student.s_name,AVG(score.s_score)AS avg_score
FROM student LEFTJOIN score ON student.s_id=score.s_id GROUPBY student.s_id HAVING avg_score >='60'--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--(包括有成绩的和无成绩的)SELECT a.s_id,student.s_name,a.avg1 FROM(SELECT s_id,sum(s_score)/3 avg1 FROM score GROUPBY s_id HAVING avg1 <60) a,
student WHERE a.s_id = student.s_id
SELECT student.s_id,student.s_name,AVG(score.s_score)AS avg_score FROM student LEFTJOIN score ON student.s_id=score.s_id GROUPBY student.s_id HAVING avg_score <'60'OR avg_score ISNULL--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.*,student.s_name FROM(SELECT s_id,COUNT(c_id),SUM(s_score)FROM score GROUPBY s_id) a,
student
WHERE a.s_id = student.s_id
SELECT student.s_id,student.s_name,ifnull(a.co,0),IFNULL(a.su,0)FROM
student
LEFTJOIN(SELECT s_id,COUNT(c_id) co,SUM(s_score) su FROM score GROUPBY s_id) a
ON
student.s_id = a.s_id
SELECT
a.s_id,
student.s_name,
a.count1,
a.sum1
FROM(SELECT s_id,COUNT( c_id ) count1,SUM( s_score ) sum1 FROM score GROUPBY s_id ) a
JOIN student ON a.s_id = student.s_id
SELECT student.s_id,student.s_name,COUNT(score.c_id)AS'选课总数',SUM(score.s_score)AS'总成绩'FROM student LEFTJOIN score ON student.s_id=score.s_id GROUPBY student.s_id
--6、查询"李"姓老师的数量
SELECTCOUNT(t_name)FROM teacher WHERE t_name LIKE'李%'SELECTCOUNT(teacher.t_id)AS'数量'FROM teacher WHERE teacher.t_name LIKE'李%'--7、查询学过"张三"老师授课的同学的信息
SELECT student.*FROM course,teacher,score,student WHERE teacher.t_name ='张三'AND teacher.t_id = course.t_id AND course.c_id=score.c_id AND score.s_id = student.s_id
SELECT score.*,a.t_name FROM score,(SELECT course.*,teacher.t_name FROM teacher,course WHERE teacher.t_id=course.t_id AND t_name='张三') a WHERE score.c_id = a.c_id
SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='02'SELECT b.*,teacher.t_name FROM(SELECT a.*,course.t_id FROM(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex,score.c_id FROM student LEFTJOIN score ON student.s_id=score.s_id)AS a LEFTJOIN course ON a.c_id=course.c_id)AS b LEFTJOIN teacher ON b.t_id=teacher.t_id WHERE teacher.t_name='张三'--8、查询没学过"张三"老师授课的同学的信息
SELECT*FROM student WHERE s_id not IN(SELECT student.s_id FROM course,teacher,score,student WHERE teacher.t_name ='张三'AND teacher.t_id = course.t_id AND course.c_id=score.c_id AND score.s_id = student.s_id)SELECT c.*FROM(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student)AS c
WHERE c.s_id NOTIN(SELECT b.s_id FROM(SELECT a.*,course.t_id FROM(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex,score.c_id FROM student LEFTJOIN score ON student.s_id=score.s_id)AS a LEFTJOIN course ON a.c_id=course.c_id)AS b LEFTJOIN teacher ON b.t_id=teacher.t_id WHERE teacher.t_name='张三')-- 张三老师教的课
SELECT course.c_id,course.c_name FROM course LEFTJOIN teacher ON course.t_id=teacher.t_id WHERE teacher.t_name='张三'-- 有张三老师课成绩的st.s_id
-- 不在上面查到的st.s_id的学生信息,即没学过张三老师授课的同学信息
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT s1.*FROM(SELECT student.*,score.s_score FROM student LEFTJOIN score ON student.s_id = score.s_id WHERE score.c_id ='01') s1
left JOIN(SELECT student.*,score.s_score FROM student LEFTJOIN score ON student.s_id = score.s_id WHERE score.c_id ='02') s2
on s1.s_id = s2.s_id
SELECT s1.*FROM(SELECT student.*FROM score,student WHERE score.s_id = student.s_id AND score.c_id ='01') s1,(SELECT student.*FROM score,student WHERE score.s_id = student.s_id AND score.c_id ='02') s2
WHERE s1.s_id = s2.s_id
SELECT s1.*FROM(SELECT student.*FROM score,student WHERE score.s_id = student.s_id AND score.c_id ='01') s1 WHERE s_id IN(SELECT student.s_id FROM score,student WHERE score.s_id = student.s_id AND score.c_id ='02')SELECT inner_a.*FROM(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='01')AS inner_a
INNERJOIN(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='02')AS inner_b
ON inner_a.s_id=inner_b.s_id
--10、查询学过编号为“01”但是没有学过编号为“02”的课程的同学的信息
SELECT s1.*FROM(SELECT student.*FROM score,student WHERE score.s_id = student.s_id AND score.c_id ='01') s1 WHERE s_id NOTIN(SELECT student.s_id FROM score,student WHERE score.s_id = student.s_id AND score.c_id ='02')SELECT a.*FROM(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='01')AS a
WHERE a.s_id NOTIN(SELECT inner_a.s_id FROM(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='01')AS inner_a
INNERJOIN(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='02')AS inner_b
ON inner_a.s_id=inner_b.s_id
)--11、查询没有学全所有课程的同学的信息
SELECT student.*,s.count1 FROM student LEFTJOIN(SELECT s_id,COUNT(c_id) count1 FROM score GROUPBY s_id) s
on student.s_id = s.s_id WHERE s.count1 <3 or s.count1 is NULLSELECT a.*FROM(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id)AS a
WHERE a.s_id NOTIN(SELECT a_12.s_id FROM(SELECT a_1.*FROM((SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='01')AS a_1
INNERJOIN(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='02')AS a_2
ON a_1.s_id=a_2.s_id
))AS a_12
INNERJOIN(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE score.c_id='03')AS a_3
ON a_12.s_id=a_3.s_id
)GROUPBY a.s_id
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECTDISTINCT
student.*FROM
student,
score
WHERE
student.s_id = score.s_id
AND
c_id IN(SELECT
c_id
FROM
score
WHERE
s_id ='01')SELECTDISTINCT a.s_id,a.s_name,a.s_birth,a.s_sex FROM(SELECT student.s_id,student.s_name,student.s_birth,student.s_sex,score.c_id FROM student LEFTJOIN score ON student.s_id=score.s_id)AS a
WHERE a.c_id IN(SELECT score.c_id FROM student LEFTJOIN score ON student.s_id=score.s_id WHERE student.s_id='01')--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT s1.*,s2.s_score sc2,s3.s_score sc3 FROM score s1,score s2,score s3 WHERE s1.s_id = s2.s_id AND s2.s_id = s3.s_id AND s2.c_id ='02'AND s1.c_id ='01'AND s3.c_id ='03'SELECT b.*,c03.s_score sc03 FROM(SELECT a.*,c02.s_score sc02 FROM(SELECT student.*,c01.s_score sc01 FROM student LEFTJOIN(SELECT*FROM score WHERE score.c_id ='01') c01 on student.s_id = c01.s_id) a
LEFTJOIN(SELECT*FROM score WHERE score.c_id ='02') c02
ON a.s_id = c02.s_id) b
LEFTJOIN(SELECT*FROM score WHERE score.c_id ='03') c03
ON b.s_id = c03.s_id
--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT*FROM
student
WHERE
s_id NOTIN(SELECT
score.s_id
FROM
score,
course,
teacher
WHERE
score.c_id = course.c_id
AND course.t_id = teacher.t_id
AND teacher.t_name ='张三')SELECT a.*FROM(SELECT student.s_id,student.s_name FROM student)AS a
WHERE a.s_id NOTIN(SELECT student.s_id FROM student,score,course,teacher WHERE student.s_id=score.s_id AND score.c_id=course.c_id AND course.t_id=teacher.t_id AND teacher.t_name='张三'GROUPBY student.s_id)--15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT student.s_id,student.s_name,b.avg1
FROM
student,(SELECT s_id,COUNT(c_id)FROM score WHERE s_score<60GROUPBY s_id) a,(SELECT s_id,AVG(s_score) avg1 FROM score GROUPBY s_id) b
WHERE student.s_id = a.s_id AND a.s_id = b.s_id
--SELECT s_id,SUM(IF(s_score<60,1,0))FROM score GROUPBY s_id
--SELECT s_id,COUNT(c_id),AVG(s_score)FROM score WHERE s_score<60GROUPBY s_id
----SELECT s_id,count(IF(s_score<60,s_score,NULL)) co1,AVG(s_score)FROM score GROUPBY s_id HAVING co1>=2SELECT b.*FROM(SELECT a.s_id,a.s_name,AVG(a.s_score)AS avg1,COUNT(IF(a.ifscore='不及格',1,null))AS scoreNum
FROM(SELECT student.s_id,student.s_name,score.s_score,IF(score.s_score<60,'不及格',score.s_score)AS ifscore FROM student,score WHERE student.s_id=score.s_id)AS a
GROUPBY a.s_id)AS b
WHERE
b.scoreNum >='2'--16、检索"01"课程分数小于60,按分数降序排列的学生信息
SELECT student.s_id,student.s_name,student.s_birth,student.s_sex,score.s_score
FROM student,score
WHERE student.s_id=score.s_id AND score.c_id='01'AND score.s_score<60ORDERBY score.s_score DESC--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s_id,AVG(s_score),SUM(s_score)FROM score GROUPBY s_id
select st.s_id, st.s_name, s.c_id, s.s_score, a.n from student st,(select s_id,avg(s_score) n from score where 1=1GROUPBY s_id) a, score s where st.s_id = a.s_id and st.s_id = s.s_id;SELECT student.s_id,student.s_name,student.s_birth,student.s_sex,course.c_name,score.s_score
FROM student,course,score
WHERE student.s_id=score.s_id AND score.c_id=course.c_id
ORDERBY score.s_score DESC--18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
SELECT c_id,MAX(s_score),MIN(s_score),AVG(s_score),COUNT(IF(s_score>=60,s_score,NULL))/COUNT(s_score)FROM score GROUPBY c_id
--19、按各科成绩进行排序,并显示排名(实现不完全)RANK() 函数只有8.0 以上版本支持
SELECT s_id,c_id,s_score,RANK()over(partition by c_id ORDERBY s_score DESC)FROM score
SELECT s_id,c_id,s_score,DENSE_RANK()over(partition by c_id ORDERBY s_score DESC)FROM score
--20、查询学生的总成绩并进行排名
SELECT s_id,sum1,RANK()over(ORDERBY sum1 DESC)FROM(SELECT s_id,SUM(s_score) sum1 FROM score GROUPBY s_id) a
--21、查询不同老师所教不同课程平均分从高到低显示
--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
--23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
--24、查询学生平均成绩及其名次
--25、查询各科成绩前三名的记录
--26、查询每门课程被选修的学生数
--27、查询出只有两门课程的全部学生的学号和姓名
--28、查询男生、女生人数
--29、查询名字中含有"风"字的学生信息
--30、查询同名同性学生名单,并统计同名人数
--31、查询1990年出生的学生名单
--32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
--34、查询课程名称为"数学",且分数低于60的学生姓名和分数
--35、查询所有学生的课程及分数情况;
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
--37、查询不及格的课程
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
--39、求每门课程的学生人数
--40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
--42、查询每门功成绩最好的前两名
--43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,
--44、检索至少选修两门课程的学生学号
--45、查询选修了全部课程的学生信息
--46、查询各学生的年龄
--47、查询本周过生日的学生
--48、查询下周过生日的学生
--49、查询本月过生日的学生
--50、查询下月过生日的学生
```
创建表select student.s_id,s_name,s_birth,s_sex,s_score from student,score where student.s_id=score.s_id;--建表--学生表CREATE TABLE `Student`(`s_id` VARCHAR(20),`s_name` VARCHAR(20) NOT NULL DEFAULT '',`s_birth` VARCHAR(20) NOT NULL DEFAULT '',`s_sex` VAR