Mysql 綜合練習(xí)
1、查詢男生、女生的人數(shù)；
mysql> select gender, count(sid) from student group by gender;
2、查詢姓“張”的學(xué)生名單；
3、課程平均分從高到低顯示
mysql> select course_id, cname,avg_num from course inner join
(select course_id,avg(num) as avg_num from score group by course_id) as t1
where cid=t1.course_id
group by avg_num desc;
4、查詢有課程成績小于60分的同學(xué)的學(xué)號、姓名；
mysql> select sname, sid from student where sid in (select distinct student_id from score where num < 60);
5、查詢至少有一門課與學(xué)號為1的同學(xué)所學(xué)課程相同的同學(xué)的學(xué)號和姓名；
select sid,sname from student where sid in
(select distinct student_id from score where course_id in
(select course_id from score where student_id? = 1)) ;
6、查詢出只選修了一門課程的全部學(xué)生的學(xué)號和姓名；
select * from student inner join (select student_id, count(course_id) from score group by student_id having count(course_id)=1) as bb on sid = bb.student_id ;
7、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；
select course_id, max(num),min(num) from score group by course_id ;
8、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學(xué)的學(xué)號、姓名；
# 先找到每個學(xué)生的課程編號“1”的和課程編號“2”的成績組成一張表
select t1.student_id from(select num num2,student_id from score where course_id = 2) t2 inner join(select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id
# 再找到課程編號“2”的成績比課程編號“1”課程低的所有學(xué)生的學(xué)號
select t1.student_id from(select num num2,student_id from score where course_id = 2) t2 inner join(select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1
# 再找到所有學(xué)生的學(xué)號、姓名
select sid,sname from student where sid in(select t1.student_id from(select num num2,student_id from score where course_id = 2) t2 inner join(select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1);
9、查詢“生物”課程比“物理”課程成績高的所有學(xué)生的學(xué)號；
select student_id,shengwu_score,wuli_score from (
(select student_id, num as shengwu_score from score where course_id in
(select cid from course where cname="生物")) as t1
inner join
(select student_id as student_id2, num as wuli_score from score where course_id in
(select cid from course where cname="物理")) as t2?
on t1.student_id = t2.student_id2)
where shengwu_score > wuli_score;
10、查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績;
select student_id, avg(num) from score group by student_id having avg(num) > 60;
11、查詢所有同學(xué)的學(xué)號、姓名、選課數(shù)、總成績；
select student_id,sname, sum(num), count(course_id) from score
inner join? student on student.sid = student_id group by student_id;
12、查詢姓“李”的老師的個數(shù)；
select count(tid ) as li_ciunter from ( select * from teacher where tname like "李%") as teacher;
13、查詢沒學(xué)過“張磊老師”課的同學(xué)的學(xué)號、姓名；
select distinct sname, student_id from student inner join score on student.sid =student_id where
course_id not in
(select tid from teacher where tname like "張磊老師");
14、查詢學(xué)過“1”并且也學(xué)過編號“2”課程的同學(xué)的學(xué)號、姓名；
select sname, sid from student where sid in (
select t1.1_id? from
(select student_id as 1_id from score where course_id = 1 ) t1
inner join
(select student_id as 2_id from score where course_id = 2)t2
on t1.1_id = t2.2_id);
15、查詢學(xué)過“李平老師”所教的所有課的同學(xué)的學(xué)號、姓名；
select * from student where sid in (
select distinct student_id from score where course_id in
(select cid from course where teacher_id? = (
select tid from teacher where tname = "李平老師")));
