MySQL开发面试题

……继上一篇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=45
    -> 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('小明','005');

3）李四语文成绩被登记错误，成绩实际为85分，更新到考试信息表中，SQL语句怎么编写？

mysql> update exam,student
    -> set exam.score=85
    -> 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,0)) 语文成绩,
    -> sum(if(e.subject='数学',e.score,0)) 数学成绩,
    -> sum(if(e.subject='英语',e.score,0)) 英语成绩
    -> 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  |
+------+--------+-------+------+
| 1001 | 张三    |    23 | 男   |
| 1002 | 李四    |    19 | 女   |
| 1003 | 马五    |    20 | 男   |
| 1004 | 甲六    |    17 | 女   |
| 1005 | 乙七    |    22 | 男   |
+------+--------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from teacher;
+------+--------+
| t_no | t_name |
+------+--------+
| 2001 | 叶平   |
| 2002 | 赵安   |
| 2003 | 孙顺   |
| 2004 | 刘六   |
+------+--------+
4 rows in set (0.00 sec)

mysql> select * from course;
+------+--------------+------+
| c_no | c_name       | t_no |
+------+--------------+------+
|  001 | 企业管理      | 2001 |
|  002 | 马克思        | 2002 |
|  003 | UML          | 2003 |
|  004 | 数据库        | 2004 |
+------+--------------+------+
4 rows in set (0.05 sec)

mysql> select * from sc;
+------+------+-------+
| s_no | c_no | score |
+------+------+-------+
| 1001 | 001  |    93 |
| 1001 | 002  |    86 |
| 1001 | 004  |    92 |
| 1002 | 003  |   100 |
| 1003 | 001  |    93 |
| 1003 | 004  |    99 |
| 1004 | 002  |    75 |
| 1004 | 003  |    59 |
| 1002 | 002  |    50 |
| 1005 | 003  |    60 |
| 1005 | 002  |    60 |
+------+------+-------+
11 rows in set (0.00 sec)

1、查询“001”课程比“002”课程成绩高的所有学生的学号。

mysql> select a.s_no
    -> from
    -> 　　(select s_no,score from sc where c_no='001') a,
    -> 　　(select s_no,score from sc where c_no='002') 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)>60;

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='001'
    -> and exists
    -> (select * from sc where sc.s_no=student.s_no and c_no='002');

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='001') a
    ->   on a.s_no=student.s_no
    -> join (select s_no,score from sc where c_no='002') 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<60;

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='1001'
    -> )
    -> and student.s_no != '1001';

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=1001)
    -> and student.s_no != '1001';

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='1002')
    -> group by s_no
    -> having count(*)=(select count(*) from sc where s_no='1002')
    -> and s_no<>'1002'
    -> );

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
    &