18、按照平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“马克思”三门的课程成绩，按如下形式显示：学生ID,数据库，企业管理，马克思，有效课程数，有效平均分。
MySQL> selectsc.s_no 学号,-> max(case c_name when ‘数据库’then score end) 数据库,-> max(case c_name when ‘企业管理’then score end) 企业管理,-> max(case c_name when ‘马克思’then score end) 马克思,->count(sc.s_no) 有效课程数,-> avg(ifnull(score,0)) 有效平均分-> fromsc,course-> where sc.c_no=course.c_no->group by sc.s_no-> order by 6 desc;
19、查询不同老师所教不同课程平均分从高到低显示。
mysql> selectc_no,avg(score)-> fromsc->group by c_no-> order by 2 desc;
20、查询如下课程成绩第3名到第6名的学生成绩单：企业管理(001)、马克思(002)，UML(003)，数据库(004)
mysql> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=001 order by score desc limit 2,4)->union-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=002 order by score desc limit 2,4)->union-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=003 order by score desc limit 2,4)->union-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=004 order by score desc limit 2,4);
21、统计各科成绩，各分数段人数：课程ID，课程名称，【100-85】，【85-70】，【70-60】，【<60】
mysql> selectcourse.c_no 课程ID,c_name 课程名称,-> count(case when score>85 and score<=100 then score end) ‘[85-100]’,-> count(case when score>70 and score<=85 then score end) ‘[70-85]’,-> count(case when score>=60 and score<=70 then score end) ‘[60-70]’,-> count(case when score<60 then score end) ‘[<60]’
-> fromcourse,sc-> where course.c_no=sc.c_no-> group by course.c_no,c_name;
22、查询每门课程被选修的学生数
mysql> selectc_no 课程ID,count(s_no) 学生人数-> fromsc-> group by c_no;
23、查询出只选修了一门课程的全部学生的学号和姓名
mysql> selectstudent.s_no 学号,student.s_name 姓名,count(c_no) 选课数-> fromstudent->join sc-> on sc.s_no=student.s_no->group by student.s_no,student.s_name-> having count(c_no)=1;
24、查询同名同性学生名单，并统计同名人数。
mysql> select s_name 姓名,count(*)-> fromstudent->group by s_name-> having count(*)>1;
25、查询1994年出生的学生名单(注：student表中sage列的类型是datatime)
mysql> select * fromstudent-> where year(curdate())-s_age=’1994′;
26、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。
mysql> selectc_no 课程ID,avg(score)-> fromsc->group by c_no-> order by avg(score) asc,c_no desc;
27、查询平均成绩都大于85的所有学生的学号，姓名和平均成绩
mysql> selectstudent.s_no 学号,s_name 姓名,avg(score) 平均成绩-> fromstudent,sc-> where student.s_no=sc.s_no->group by student.s_no,s_name-> having avg(score)>85;
28、查询课程名称为“数据库”且分数低于60的学生姓名和分数
mysql> selects_name 学生姓名,score 分数-> fromstudent,sc,course-> where student.s_no=sc.s_no and sc.c_no=course.c_no-> and c_name=’数据库’
-> and score<60;
29、查询所有学生的选课情况
mysql> selectstudent.s_no 学号,student.s_name 姓名,group_concat(c_no) 所选课程ID-> fromstudent,sc-> where student.s_no=sc.s_no-> group by student.s_no,student.s_name;
30、查询任何一门课程成绩在90分以上的姓名、课程名称和分数。
mysql> selects_name 姓名,c_name 课程名称,score 分数-> fromstudent,sc,course-> where student.s_no=sc.s_no and sc.c_no=course.c_no-> and score > 90
-> order by s_name;
31、查询不及格的课程，并按课程号从大到小排序。
mysql> selects_no 学生ID,c_no 不及格课程ID-> fromsc-> where score<60
-> order by c_no desc;
32、求选修了课程的学生人数。
mysql> select count(*) 已选课程人数-> from
->(-> select distinct(sc.s_no) fromstudent->left join sc-> on sc.s_no=student.s_no-> where c_no is not null
-> ) as ayixuan;
33、查询选修了“冯老师”所授课程的学生中，成绩最高的学生姓名及其成绩。
mysql> selects_name 学生姓名,score 成绩-> fromstudent,sc,course,teacher-> where student.s_no=sc.s_no and sc.c_no=course.c_no and course.t_no=teacher.t_no-> and t_name=’冯老师’
->order by score-> limit 1;
34、查询各个课程及相应的选修人数。
mysql> selectcourse.c_no 课程ID,course.c_name 课程名,count(s_no) 选修人数-> fromcourse->join sc-> on course.c_no=sc.c_no-> group by course.c_no,course.c_name;
35、查询不同课程成绩相同的学生的学号、课程号、学生成绩。
mysql> selecta.s_no 学号,group_concat(a.c_no) 课程号,a.score 学生成绩-> fromsc a,sc b-> where a.score=b.score and a.c_no<>b.c_no-> group by a.s_no,a.score;
36、查询每门课程最好的前两名。
mysql> selecta.s_no,a.c_no,a.score-> fromsc a-> where
-> (select count(distinct score) from sc b where b.c_no=a.c_no and b.score>=a.score)<=2
-> order by a.c_no,a.score desc;
37、检索至少选修两门课程的学生学号。
mysql> selects_no 学生学号-> fromsc->group by s_no-> having count(*)>=2;
38、查询全部学生都选修的课程的课程号和课程名。
mysql> selectcourse.c_no 课程号,c_name 课程名-> fromcourse-> join sc on course.c_no=sc.c_no->join (-> select c_no,count(s_no) fromsc group by c_no-> having count(s_no)=(select count(*) from student)) asa-> on course.c_no=a.c_no;
39、查询没有学过“叶平”老师讲授的任一门课程的学号姓名。
mysql> selectstudent.s_no 学号,student.s_name 姓名-> fromstudent->join sc-> on sc.s_no=student.s_no-> where sc.s_no not in
->(-> selects_no-> fromcourse,teacher,sc-> where course.t_no=teacher.t_no and sc.c_no=course.c_no-> and teacher.t_name=’叶平’
-> );
40、查询两门以上不及格课程的同学的学号及其平均成绩。
mysql> selects_no 学号,avg(score) 平均成绩-> fromsc-> where s_no in(-> select s_no fromsc-> where score<60
->group by s_no-> having count(*)>2)-> group by s_no;
四、根据表1和表2的信息写出SQL
表1：books书表b
主码
列标题
列名
数据类型
宽度
小数位数
是否空值
P
书号
TNO
char
15
no
书名
TNAME
varchar
50
no
作者姓名
TAUTHOR
varchar
8
no
出版社编号
CNO
char
5
yes
书类
TCATEGORY
varchar
20
yes
价格
TPRICE
numeric
8
2
yes
表2：book_concern出版社表C
主码
列标题
列名
数据类型
宽度
小数位数
是否空值
p
出版社编号
CNO
char
5
NO
出版社名称
CNAME
varchar
20
NO
出版社电话
CPHONE
varchar
15
YES
出版社城市
CCITY
varchar
20
YES
1、查询出版过“计算机”类图书的出版社编号(若一个出版社出版过多部“计算机”类图书，则在查询结果中该出版社编号只显示一次)
mysql> selectdistinct cno 出版社编号-> frombooks-> where tcategory=’计算机’;
2、查询南开大学出版社的“经济”类或“数学”类图书的信息。
mysql> select *
-> frombooks,book_concern-> where books.cno=book_concern.cno-> and cname=’南开大学出版社’
-> and tcategory in (‘数学’,’经济’);
3、查询编号为“20001”的出版社出版图书的平均价格。
mysql> selectcno 出版社编号,avg(tprice) 图书均价-> frombooks-> where cno=’20001′;
4、查询至少出版过20套图书的出版社，在查询结果中按出版社编号的升序顺序显示满足条件的出版社编号、出版社名称和每个出版社出版的图书套数。
mysql> select b.cno 出版社编号,cname 出版社名称,count(*) 图书套数-> frombooks b,book_concern c-> where b.cno=c.cno->group by b.cno,cname-> having count(*)>20
-> order by b.cno;
5、查询比编号为“20001”的出版社出版图书套数多的出版社编号。
mysql> selectcno 出版社编号-> frombooks->group by cno-> having count(*)>(select count(*) from books where cno=’20001′);
五、一道关于group by的经典面试题：
有一张shop表如下，有三个字段article、author、price，选出每个author的price最高的记录(要包含所有字段)。
mysql> select * fromshop;+———+——–+——-+
| article | author | price |
+———+——–+——-+
| 0001 | B | 9.95 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | B | 19.95 |
| 0005 | A | 6.96 |
+———+——–+——-+
5 rows in set (0.02 sec)
1、使用相关子查询
mysql> selectarticle,author,price -> fromshop s1 -> where price =( -> selectmax(s2.price) -> fromshop s2 -> where s1.author=s2.author);
2、使用非相关子查询
mysql> selectarticle,s1.author,s1.price -> fromshop s1 ->join ( -> selectauthor,max(price) price -> fromshop ->group by author) s2 -> on s1.author=s2.author and s1.price=s2.price;
3、使用left join语句(毕竟子查询在有些时候，效率会很低)
mysql> selects1.article,s1.author,s1.price -> fromshop s1 ->left join shop s2 -> on s1.author=s2.author and s1.price where s2.article is null;
原理分析：当s1.price是当前author的最大值时，就没有s2.price比它还要大，所以此时s2的rows的值都会是null。
六、用一条SQL语句查询出每门课都大于80分的学生
name
kecheng
fenshu
张三
语文
81
张三
数学
75
李四
语文
76
李四
数学
90
王五
语文
81
王五
数学
100
王五
英语
90
mysql> selecta.name 姓名-> from
-> (select name,count(*) anum from NO_6 where fenshu>80group by name) a,-> (select name,count(*) bnum fromNO_6 group by name) b-> where a.name=b.name-> and a.anum=b.bnum;
七、怎么把这样一个表
Year
month
amount
1991
1
1.1
1991
2
1.2
1991
3
1.3
1991
4
1.4
1992
1
2.1
1992
2
2.2
1992
3
2.3
1992
4
2.4
查成这样一个结果
year
M1
M2
M3
M4
1991
1.1
1.2
1.3
1.4
1992
2.1
2.2
2.3
2.4
mysql> selectyear,-> sum(if(month=1,amount,0)) M1,-> sum(if(month=2,amount,0)) M2,-> sum(if(month=3,amount,0)) M3,-> sum(if(month=4,amount,0)) M4-> fromNO_7-> group by year;
八、已知表A =login_ftp记录着登录FTP服务器的计算机IP、时间等字段信息
请写出SQL查询表A中存在ID重复三次以上的记录。
mysql> select IP fromlogin_ftp->group by IP-> having count(*)>3;
九、创建存储过程，要求具有游标(遍历表)示例
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;
