……继上一篇MySQL的开发总结之后，适当的练习还是很有必要的……
SQL语法多变，不敢保证唯一，也不敢保证全对，如果错误欢迎指出，即刻修改。
一、现有表结构如下图
TABLENAME:afinfo
|
Id |
name |
age |
birth |
sex |
memo |
|
1 |
徐洪国 |
37 |
1979-03-23 |
男 |
高中 |
|
2 |
王芳 |
26 |
1988-02-06 |
女 |
本科 |
|
3 |
李达康 |
24 |
1990-04-02 |
男 |
硕士 |
|
4 |
侯亮平 |
30 |
1984-09-12 |
女 |
博士 |
|
5 |
徐夫子 |
27 |
1987-12-30 |
男 |
大专 |
|
6 |
…… |
…… |
…… |
…… |
…… |
1）请编写sql语句对年龄进行升序排列
mysql> select * from afinfo
-> order by birth;
2）请编写sql语句查询对“徐”姓开头的人员名单
mysql> select * from afinfo
-> where name like '徐%';
3）请编写sql语句修改“李达康”的年龄为“45”
mysql> update afinfo
-> set age=
-> where name='李达康';
4）请编写sql删除王芳这表数据记录。
mysql> delete from afinfo
-> where name='王芳';
二、现有以下学生表和考试信息表
学生信息表（student）
|
姓名name |
学号code |
|
张三 |
001 |
|
李四 |
002 |
|
马五 |
003 |
|
甲六 |
004 |
考试信息表(exam)
|
学号code |
学科subject |
成绩score |
|
001 |
数学 |
80 |
|
002 |
数学 |
75 |
|
001 |
语文 |
90 |
|
002 |
语文 |
80 |
|
001 |
英语 |
90 |
|
002 |
英语 |
85 |
|
003 |
英语 |
80 |
|
004 |
英语 |
70 |
1）查询出所有学生信息，SQL怎么编写？
mysql> select * from student;
2）新学生小明，学号为005，需要将信息写入学生信息表，SQL语句怎么编写？
mysql> insert into student values('小明','');
3）李四语文成绩被登记错误，成绩实际为85分，更新到考试信息表中，SQL语句怎么编写？
mysql> update exam,student
-> set exam.score=
-> where student.code=exam.code
-> and student.name='李四'
-> and exam.subject='语文';
4）查询出各科成绩的平均成绩，显示字段为：学科、平均分，SQL怎么编写？
mysql> select subject 学科,avg(score) 平均分
-> from exam
-> group by subject;
5）查询出所有学生各科成绩，显示字段为：姓名、学号、学科、成绩，并以学号与学科排序，没有成绩的学生也需要列出，SQL怎么编写？
mysql> select s.name 姓名,s.code 学号,e.subject 学科,e.score 成绩
-> from student s
-> left join exam e
-> on s.code=e.code
-> order by 学号,学科;
6）查询出单科成绩最高的，显示字段为：姓名、学号、学科、成绩，SQL怎么编写？
mysql> select s.name 姓名,s.code 学号,e.subject 学科,e.score 成绩
-> from student s
-> join exam e
-> on s.code=e.code
-> where (e.subject,e.score) in
-> (
-> select subject,max(score)
-> from exam
-> group by subject
-> );
7）列出每位学生的各科成绩，要求输出格式：姓名、学号、语文成绩、数学成绩、英语成绩，SQL怎么编写？
mysql> select s.name 姓名,s.code 学号,
-> sum(if(e.subject='语文',e.score,)) 语文成绩,
-> sum(if(e.subject='数学',e.score,)) 数学成绩,
-> sum(if(e.subject='英语',e.score,)) 英语成绩
-> from student s
-> left join exam e
-> on s.code=e.code
-> group by s.name,s.code;
三、根据要求写出SQL语句
表结构：
student(s_no,s_name,s_age,sex) 学生表
teacher(t_no,t_name) 教师表
course(c_no,c_name,t_no) 课程表
sc(s_no,c_no,score) 成绩表
基础表数据(个人铺的)：根据题目需要自行再铺入数据
mysql> select * from student;
+------+--------+-------+------+
| s_no | s_name | s_age | sex |
+------+--------+-------+------+
| | 张三 | | 男 |
| | 李四 | | 女 |
| | 马五 | | 男 |
| | 甲六 | | 女 |
| | 乙七 | | 男 |
+------+--------+-------+------+
rows in set (0.00 sec) mysql> select * from teacher;
+------+--------+
| t_no | t_name |
+------+--------+
| | 叶平 |
| | 赵安 |
| | 孙顺 |
| | 刘六 |
+------+--------+
rows in set (0.00 sec) mysql> select * from course;
+------+--------------+------+
| c_no | c_name | t_no |
+------+--------------+------+
| | 企业管理 | |
| | 马克思 | |
| | UML | |
| | 数据库 | |
+------+--------------+------+
rows in set (0.05 sec) mysql> select * from sc;
+------+------+-------+
| s_no | c_no | score |
+------+------+-------+
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
+------+------+-------+
rows in set (0.00 sec)
1、查询“001”课程比“002”课程成绩高的所有学生的学号。
mysql> select a.s_no
-> from
-> (select s_no,score from sc where c_no='') a,
-> (select s_no,score from sc where c_no='') b
-> where a.score>b.score
-> and a.s_no=b.s_no;
2、查询平均成绩大于60分的同学的学号和平均成绩。
mysql> select s_no,avg(score)
-> from sc
-> group by s_no
-> having avg(score)>;
3、查询所有同学的学号、姓名、选课数、总成绩。
mysql> select student.s_no,student.s_name,count(sc.c_no),sum(sc.score)
-> from student
-> left join sc
-> on student.s_no=sc.s_no
-> group by student.s_no,student.s_name;
4、查询姓李的老师的个数。
mysql> select count(*)
-> from teacher
-> where t_name like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名
mysql> select student.s_no,student.s_name
-> from student
-> where student.s_no not in
-> (
-> select distinct(sc.s_no)
-> from sc
-> join course
-> on course.c_no=sc.c_no
-> join teacher
-> on teacher.t_no=course.t_no
-> where t_name='叶平'
-> );
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名。
mysql> select student.s_no,student.s_name
-> from student
-> join sc
-> on sc.s_no=student.s_no
-> where c_no=''
-> and exists
-> (select * from sc where sc.s_no=student.s_no and c_no='');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名。
mysql> select student.s_no,student.s_name
-> from student
-> join sc
-> on sc.s_no=student.s_no
-> join course
-> on course.c_no=sc.c_no
-> join teacher
-> on teacher.t_no=course.t_no
-> where teacher.t_name='叶平';
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名。
mysql> select student.s_no,student.s_name
-> from student
-> join (select s_no,score from sc where c_no='') a
-> on a.s_no=student.s_no
-> join (select s_no,score from sc where c_no='') b
-> on b.s_no=student.s_no
-> where a.s_no=b.s_no
-> and a.score>b.score;
9、查询所有课程成绩小于60分的同学的学号、姓名。
mysql> select student.s_no,student.s_name
-> from student
-> join sc
-> on sc.s_no=student.s_no
-> where sc.score<;
10、查询没有学全所有课的同学的学号、姓名。
mysql> select student.s_no 学号,student.s_name 姓名
-> from student
-> left join sc
-> on sc.s_no=student.s_no
-> group by student.s_no,student.s_name
-> having count(*) < (
-> select count(*) from course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名。
mysql> select student.s_no,student.s_name
-> from student
-> join sc
-> on sc.s_no=student.s_no
-> where sc.c_no in
-> (
-> select c_no
-> from sc
-> where s_no=''
-> )
-> and student.s_no != '';
12、查询至少学过学号为“1001”同学所有一门课的其他同学学号和姓名。
mysql> select distinct sc.s_no,s_name
-> from student,sc
-> where student.s_no=sc.s_no
-> and c_no in
-> (select c_no from sc where s_no=)
-> and student.s_no != '';
13、把“sc”表中“叶平”老师叫的课的成绩都更改为此课程的平均成绩。
mysql> set @ye_avg_score=
-> (
-> select avg(score)
-> from
-> (
-> select sc.score
-> from sc
-> join course
-> on course.c_no=sc.c_no
-> join teacher
-> on teacher.t_no=course.t_no
-> where teacher.t_name='叶平'
-> ) azi
-> ); mysql> update sc
-> set score=@ye_avg_score
-> where c_no in
-> (
-> select c_no
-> from course
-> join teacher
-> on teacher.t_no=course.t_no
-> where teacher.t_name='叶平'
-> );
14、查询和“1002”号同学学习的课程完全相同的其他同学学号和姓名。
mysql> select s_no,s_name
-> from student
-> where s_no in (
-> select distinct s_no from sc where c_no in
-> (select c_no from sc where s_no='')
-> group by s_no
-> having count(*)=(select count(*) from sc where s_no='')
-> and s_no<>''
-> );
15、删除学习“叶平”老师课的sc表记录。
mysql> set @ye_c_no=(select c_no from course,teacher where course.t_no=teacher.t_no and t_name=’叶平’);
mysql> delete from sc
-> where c_no=@ye_c_no;
16、向sc表中插入一些记录，这些记录要求符合一下条件：没有上过编号“003”课程的同学学号
mysql> select distinct s_no from sc
-> where c_no not in (select c_no from sc where c_no='')
-> and s_no not in (select s_no from sc where c_no='');
17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分。
mysql> select c_no 课程ID,max(score) 最高分,min(score) 最低分
-> from sc
-> group by c_no;
18、按照平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“马克思”三门的课程成绩，按如下形式显示：学生ID,数据库，企业管理，马克思，有效课程数，有效平均分。
mysql> select sc.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,)) 有效平均分
-> from sc,course
-> where sc.c_no=course.c_no
-> group by sc.s_no
-> order by desc;
19、查询不同老师所教不同课程平均分从高到低显示。
mysql> select c_no,avg(score)
-> from sc
-> group by c_no
-> order by 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= order by score desc limit ,)
-> union
-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no= order by score desc limit ,)
-> union
-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no= order by score desc limit ,)
-> union
-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no= order by score desc limit ,);
21、统计各科成绩，各分数段人数：课程ID，课程名称，【100-85】，【85-70】，【70-60】，【<60】
mysql> select course.c_no 课程ID,c_name 课程名称,
-> count(case when score> and score<= then score end) '[85-100]',
-> count(case when score> and score<= then score end) '[70-85]',
-> count(case when score>= and score<= then score end) '[60-70]',
-> count(case when score< then score end) '[<60]'
-> from course,sc
-> where course.c_no=sc.c_no
-> group by course.c_no,c_name;
22、查询每门课程被选修的学生数
mysql> select c_no 课程ID,count(s_no) 学生人数
-> from sc
-> group by c_no;
23、查询出只选修了一门课程的全部学生的学号和姓名
mysql> select student.s_no 学号,student.s_name 姓名,count(c_no) 选课数
-> from student
-> join sc
-> on sc.s_no=student.s_no
-> group by student.s_no,student.s_name
-> having count(c_no)=;
24、查询同名同性学生名单，并统计同名人数。
mysql> select s_name 姓名,count(*)
-> from student
-> group by s_name
-> having count(*)>;
25、查询1994年出生的学生名单（注：student表中sage列的类型是datatime）
mysql> select * from student
-> where year(curdate())-s_age='';
26、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。
mysql> select c_no 课程ID,avg(score)
-> from sc
-> group by c_no
-> order by avg(score) asc,c_no desc;
27、查询平均成绩都大于85的所有学生的学号，姓名和平均成绩
mysql> select student.s_no 学号,s_name 姓名,avg(score) 平均成绩
-> from student,sc
-> where student.s_no=sc.s_no
-> group by student.s_no,s_name
-> having avg(score)>;
28、查询课程名称为“数据库”且分数低于60的学生姓名和分数
mysql> select s_name 学生姓名,score 分数
-> from student,sc,course
-> where student.s_no=sc.s_no and sc.c_no=course.c_no
-> and c_name='数据库'
-> and score<;
29、查询所有学生的选课情况
mysql> select student.s_no 学号,student.s_name 姓名,group_concat(c_no) 所选课程ID
-> from student,sc
-> where student.s_no=sc.s_no
-> group by student.s_no,student.s_name;
30、查询任何一门课程成绩在90分以上的姓名、课程名称和分数。
mysql> select s_name 姓名,c_name 课程名称,score 分数
-> from student,sc,course
-> where student.s_no=sc.s_no and sc.c_no=course.c_no
-> and score >
-> order by s_name;
31、查询不及格的课程，并按课程号从大到小排序。
mysql> select s_no 学生ID,c_no 不及格课程ID
-> from sc
-> where score<
-> order by c_no desc;
32、求选修了课程的学生人数。
mysql> select count(*) 已选课程人数
-> from
-> (
-> select distinct(sc.s_no) from student
-> left join sc
-> on sc.s_no=student.s_no
-> where c_no is not null
-> ) as ayixuan;
33、查询选修了“冯老师”所授课程的学生中，成绩最高的学生姓名及其成绩。
mysql> select s_name 学生姓名,score 成绩
-> from student,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 ;
34、查询各个课程及相应的选修人数。
mysql> select course.c_no 课程ID,course.c_name 课程名,count(s_no) 选修人数
-> from course
-> join sc
-> on course.c_no=sc.c_no
-> group by course.c_no,course.c_name;
35、查询不同课程成绩相同的学生的学号、课程号、学生成绩。
mysql> select a.s_no 学号,group_concat(a.c_no) 课程号,a.score 学生成绩
-> from sc a,sc b
-> where a.score=b.score and a.c_no<>b.c_no
-> group by a.s_no,a.score;
36、查询每门课程最好的前两名。
mysql> select a.s_no,a.c_no,a.score
-> from sc a
-> where
-> (select count(distinct score) from sc b where b.c_no=a.c_no and b.score>=a.score)<=
-> order by a.c_no,a.score desc;
37、检索至少选修两门课程的学生学号。
mysql> select s_no 学生学号
-> from sc
-> group by s_no
-> having count(*)>=;
38、查询全部学生都选修的课程的课程号和课程名。
mysql> select course.c_no 课程号,c_name 课程名
-> from course
-> join sc on course.c_no=sc.c_no
-> join (
-> select c_no,count(s_no) from sc group by c_no
-> having count(s_no)=(select count(*) from student)) as a
-> on course.c_no=a.c_no;
39、查询没有学过“叶平”老师讲授的任一门课程的学号姓名。
mysql> select student.s_no 学号,student.s_name 姓名
-> from student
-> join sc
-> on sc.s_no=student.s_no
-> where sc.s_no not in
-> (
-> select s_no
-> from course,teacher,sc
-> where course.t_no=teacher.t_no and sc.c_no=course.c_no
-> and teacher.t_name='叶平'
-> );
40、查询两门以上不及格课程的同学的学号及其平均成绩。
mysql> select s_no 学号,avg(score) 平均成绩
-> from sc
-> where s_no in (
-> select s_no from sc
-> where score<
-> group by s_no
-> having count(*)>)
-> 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> select distinct cno 出版社编号
-> from books
-> where tcategory='计算机';
2、查询南开大学出版社的“经济”类或“数学”类图书的信息。
mysql> select *
-> from books,book_concern
-> where books.cno=book_concern.cno
-> and cname='南开大学出版社'
-> and tcategory in ('数学','经济');
3、查询编号为“20001”的出版社出版图书的平均价格。
mysql> select cno 出版社编号,avg(tprice) 图书均价
-> from books
-> where cno='';
4、查询至少出版过20套图书的出版社，在查询结果中按出版社编号的升序顺序显示满足条件的出版社编号、出版社名称和每个出版社出版的图书套数。
mysql> select b.cno 出版社编号,cname 出版社名称,count(*) 图书套数
-> from books b,book_concern c
-> where b.cno=c.cno
-> group by b.cno,cname
-> having count(*)>
-> order by b.cno;
5、查询比编号为“20001”的出版社出版图书套数多的出版社编号。
mysql> select cno 出版社编号
-> from books
-> group by cno
-> having count(*)>(select count(*) from books where cno='');
五、一道关于group by的经典面试题：
有一张shop表如下，有三个字段article、author、price，选出每个author的price最高的记录（要包含所有字段）。
mysql> select * from shop;
+---------+--------+-------+
| article | author | price |
+---------+--------+-------+
| | B | 9.95 |
| | A | 10.99 |
| | C | 1.69 |
| | B | 19.95 |
| | A | 6.96 |
+---------+--------+-------+
rows in set (0.02 sec)
1、使用相关子查询
mysql> select article,author,price
-> from shop s1
-> where price = (
-> select max(s2.price)
-> from shop s2
-> where s1.author=s2.author);
2、使用非相关子查询
mysql> select article,s1.author,s1.price
-> from shop s1
-> join (
-> select author,max(price) price
-> from shop
-> group by author) s2
-> on s1.author=s2.author and s1.price=s2.price;
3、使用left join语句（毕竟子查询在有些时候，效率会很低）
mysql> select s1.article,s1.author,s1.price
-> from shop s1
-> left join shop s2
-> on s1.author=s2.author and s1.price<s2.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> select a.name 姓名
-> from
-> (select name,count(*) anum from NO_6 where fenshu> group by name) a,
-> (select name,count(*) bnum from NO_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> select year,
-> sum(if(month=,amount,)) M1,
-> sum(if(month=,amount,)) M2,
-> sum(if(month=,amount,)) M3,
-> sum(if(month=,amount,)) M4
-> from NO_7
-> group by year;
八、已知表A =login_ftp记录着登录FTP服务器的计算机IP、时间等字段信息
请写出SQL查询表A中存在ID重复三次以上的记录。
mysql> select IP from login_ftp
-> group by IP
-> having count(*)>;
九、创建存储过程，要求具有游标(遍历表)示例
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR();
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 < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP; CLOSE cur1;
CLOSE cur2;
END;