MySQL查询语句练习题45题版
免费 - Sunrise - - 浏览量： 0 - 文章来源
设有一数据库，包括四个表：学生表（student）、课程表（course）、成绩表（score）以及教师信息表（teacher）。表结构及数据如下，请完成题目。
表（一）student (学生表) ：
| 属性名 | 数据类型 | 可否为空 | 含 义 |
|---|---|---|---|
| id | varchar (20) | 否 | 学号（主码） |
| name | varchar (20) | 否 | 学生姓名 |
| sex | varchar (20) | 否 | 学生性别 |
| birthday | datetime | 可 | 学生出生年月 |
| class | varchar (20) | 可 | 学生所在班级 |
表（二）course（课程表）：
| 属性名 | 数据类型 | 可否为空 | 含 义 |
|---|---|---|---|
| id | varchar (20) | 否 | 课程号（主码） |
| name | varchar (20) | 否 | 课程名称 |
| teacher_id | varchar (20) | 否 | 教工编号（外码） |
表（三）score(成绩表)：
| 属性名 | 数据类型 | 可否为空 | 含 义 |
|---|---|---|---|
| student_id | varchar (20) | 否 | 学号（外码）） |
| course_id | varchar (20) | 否 | 课程号（外码） |
| degree | Decimal(4,1) | 可 | 成绩 |
| 主码：student_id+ course_id |
表（四）teacher(教师表)：
| 属性名 | 数据类型 | 可否为空 | 含 义 |
|---|---|---|---|
| id | varchar (20) | 否 | 教工编号（主码） |
| name | varchar (20) | 否 | 教工姓名 |
| sex | varchar (20) | 否 | 教工性别 |
| birthday | datetime | 可 | 教工出生年月 |
| professionession | varchar (20) | 可 | 职称 |
| departmentment | varchar (20) | 否 | 教工所在部门 |
表1-2数据库中的数据：
表（一）student:
| student_id | name | sex | birthday | class |
|---|---|---|---|---|
| 108 | 曾华 | 男 | 1977-09-01 | 95033 |
| 105 | 匡明 | 男 | 1975-10-02 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 | 95031 |
表（二）course:
| course_id | name | student_id |
|---|---|---|
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
表（三）score:
| student_id | course_id | degree |
|---|---|---|
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 101 | 3-105 | 64 |
| 107 | 3-105 | 91 |
| 108 | 3-105 | 78 |
| 101 | 6-166 | 85 |
| 107 | 6-166 | 79 |
| 108 | 6-166 | 81 |
表（四）teacher:
| student_id | name | sex | birthday | professionession | departmentment |
|---|---|---|---|---|---|
| 804 | 李诚 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 | 助教 | 电子工程系 |
#建学生信息表student
create table student (
id varchar(20) not null primary key,
name varchar(20) not null,
sex varchar(20) not null,
birthday datetime,
class varchar(20)
);
#建立教师表
create table teacher (
id varchar(20) not null primary key,
name varchar(20) not null,
sex varchar(20) not null,
birthday datetime,
professionession varchar(20),
departmentment varchar(20) not null
);
#建立课程表course
create table course (
id varchar(20) not null primary key,
name varchar(20) not null,
teacher_id varchar(20) not null,
foreign key(teacher_id) references teacher(id)
);
#建立成绩表
create table score (
student_id varchar(20) not null,
foreign key(student_id) references student(id),
course_id varchar(20) not null,
foreign key(course_id) references course(id),
degree decimal
);
#建分数级别表grade
create table grade(
low int(3) not null,
upp int(3) not null,
rank char(1) not null
);
#添加学生信息
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','1974-06-03','95031');
#添加教师表
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
#添加课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
#添加成绩表
insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','3-105','64');
insert into score values('105','3-105','91');
insert into score values('109','3-105','78');
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');
# 添加分数级别表
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');提问
1、查询student表中的所有记录的name、sex和class列。
2、查询教师所有的单位即不重复的departmanet列。
3、查询student表的所有记录。
4、查询score表中成绩在60到80之间的所有记录。
5、查询score表中成绩为85，86或88的记录。
6、查询student表中“95031”班或性别为“女”的同学记录。
7、以class降序查询student表的所有记录。
8、以course_id升序、degree降序查询score表的所有记录。
9、查询“95031”班的学生人数。
10、查询score表中的最高分的学生学号和课程号。（子查询或者排序）
11、查询每门课的平均成绩。
12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询分数大于70，小于90的student_id列。
14、查询所有学生的name、course_id和degree列。
15、查询所有学生的student_id、name和degree列。
16、查询所有学生的name、课程name和degree列。
17、查询“95033”班学生的平均分。
18、假设使用如下命令建立了一个grade表：
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
22、查询和学号为108、101的同学同年出生的所有学生的student_id、name和birthday列。
23、查询“张旭“教师任课的学生成绩。
24、查询选修某课程的同学人数多于5人的教师姓名。
25、查询95033班和95031班全体学生的记录。
26、查询存在有85分以上成绩的课程course_id。
27、查询出“计算机系“教师所教课程的成绩表。
28、查询“计算机系”与“电子工程系“不同职称的教师的name和Prof。
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的course_id、student_id和degree,并按degree从高到低次序排序。
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的course_id、student_id和degree。
31、查询所有教师和同学的name、sex和birthday。
32、查询所有“女”教师和“女”同学的name、sex和birthday。
33、查询成绩比该课程平均成绩低的同学的成绩表。
34、查询所有任课教师的name和departmanet。
35、查询所有未讲课的教师的name和departmanet。
36、查询至少有2名男生的班号。
37、查询student表中不姓“王”的同学记录。
38、查询student表中每个学生的姓名和年龄。
39、查询student表中最大和最小的birthday日期值。
40、以班号和年龄从大到小的顺序查询student表中的全部记录。
41、查询“男”教师及其所上的课程。
42、查询最高分同学的student_id、course_id和degree列。
43、查询和“李军”同性别的所有同学的name。
44、查询和“李军”同性别并同班的同学name。
45、查询所有选修“计算机导论”课程的“男”同学的成绩表。答案
1、 查询student表中的所有记录的name、sex和class列。
#单表查询
select name,sex,class from student;2、 查询教师所有的单位即不重复的department列。
#单表查询
select distinct department from teacher;3、 查询student表的所有记录。
#单表查询
select * from student4、 查询score表中成绩在60到80之间的所有记录。
#单表查询
select * from score where degree between 60 and 80;5、 查询score表中成绩为85，86或88的记录。
#单表查询
select * from score where degree in (85,86,88);6、 查询student表中“95031”班或性别为“女”的同学记录。
#单表查询
select * from student where class= '95031' or sex='女';7、 以class降序查询student表的所有记录。
#单表查询
select * from student order by class desc;8、 以course_id升序、degree降序查询score表的所有记录。
#单表查询
select * from score order by course_id asc,degree desc;9、 查询“95031”班的学生人数。
#单表查询
select count(*) from student where class = '95031';10、查询score表中的最高分的学生学号和课程号。（子查询或者排序）
#单表查询
select student_id,course_id from score where degree = (
select max(degree) from score);
select student_id,course_id from score order by degree desc limit 0,1;11、 查询每门课的平均成绩。
#单表查询
select course_id,avg(degree) from score group by course_id;12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
#单表查询
select course_id,avg(degree) from score
where course_id like '3%'
group by course_id
having count(student_id) >=5;
select avg(degree) from score where course_id like '3%' and course_id in (select course_id from score group by course_id having count(*)>=5);13、查询分数大于70，小于90的student_id列。
#单表查询
select student_id from score where degree >70 and degree (select max(degree ) from score where student_id='109' and course_id='3-105' );
#109同学，没有选修3-105课
select * from score where course_id = '3-105' and degree > (
select max(degree) from score where student_id ='109')and degree1));
#选了多门课程并且是这个课程下不是最高分的
select * from score a where student_id in (select student_id from score group by student_id having count(*)>1) and degree(
select degree from score where course_id = '109' and course = '3-105');22、查询和学号为108、101的同学同年出生的所有学生的student_id、name和birthday列。
#单表查询
select student_id,name,birthday from student where year(birthday) in (
select year(birthday) from student where student_id='108' or '101');23、查询“张旭“教师任课的学生成绩。
#多表查询
#用where,内连接，子查询
select student_id,degree from score where course_id in (
select course_id from course where teacher_id in(
select teacher_id from teacher where name = '张旭'));
select student_id,degree from score,course,teacher where
score.course_id=course.course_id and course.teacher_id=teacher.teacher_id and name = '张旭' ;
select student_id,degree from score inner join course on score.course_id=course.course_id inner join teacher on teacher.teacher_id=course.teacher_id where name ='张旭' ;24、查询选修某课程的同学人数多于5人的教师姓名。
#多表查询
#用where,内连接，子查询
select name from teacher where teacher_id in (
select teacher_id from course where course_id in (
select course_id from score group by course_id having count(student_id)>5));
select name from teacher, course where teacher.teacher_id=course.teacher_id and course.course_id =(select course_id from score group by course_id having count(*)>5);25、查询95033班和95031班全体学生的记录。
#多表查询
#用where,内连接，子查询
select * from student,score,course where student.student_id=score.student_id and score.course_id=course.course_id and class = '95033' or '95031';26、 查询存在有85分以上成绩的课程course_id。
select course_id from score where degree >85;27、查询出“计算机系“教师所教课程的成绩表。
#多表查询
#用where,内连接，子查询
select * from score where course_id in (
select course_id from course where teacher_id in (
select teacher_id from teacher where department = '计算机系'));
#先找交集再not in
select score.student_id,score.course_id,score.degree from score,course,teacher where score.course_id=course.course_id and course.teacher_id=teacher.teacher_id and department = '计算机系';
select score.student_id,score.course_id,score.degree from score inner join course on score.course_id=course.course_id inner join teacher on teacher.teacher_id =course.teacher_id where department ='计算机系';28、查询“计算 机系”与“电子工程系“不同职称的教师的name和profession。
select name,profession from teacher where profession not in (
select profession from teacher where profession in (select profession from teacher where department = '计算机系') and department = '电子工程系');
select name,profession from teacher where profession not in (
select profession from teacher where department = '计算机系' or department ='电子工程系' group by profession having count(*)>1);
select name,profession from teacher where department ='计算机系' and profession not in( select profession from teacher where department ='电子工程系')
union
select name,profession from teacher where department ='电子工程系' and profession not in( select profession from teacher where department ='计算机系');29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的course_id、student_id和degree,并按degree从高到低次序排序。
select course_id,student_id,degree from score where course_id = '3-105' and degree >
any(select degree from score where course_id = '3-245') order by degree desc;
select course_id,student_id,degree from score where course_id = '3-105' and degree >
(select min(degree) from score where course_id = '3-245') order by degree desc;
#大于任何一个或者大于最小的30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的course_id、student_id和degree。
select course_id,student_id,degree from score where course_id = '3-105' and degree > all(select degree from score where course_id = '3-245');31、 查询所有教师和同学的name、sex和birthday。
select name as '学生',sex,birthday from student
union
select name as '老师',sex,birthday from teacher;32、查询所有“女”教师和“女”同学的name、sex和birthday。
select name,sex,birthday from student where sex = '女'
union
select name,sex,birthday from teacher where sex = '女';33、 查询成绩比该课程平均成绩低的同学的成绩表。
select * from score a where degree 这是一种特殊形式的父子表连接（自连接）SQL选择查询写法。对于这种特殊的写法，数据库引擎会以特殊的方式检索父查询表里的数据。如果搞不清楚这种特殊的检索方式，我们很难从该SQL语句的表面逻辑理出个中道理。
> 现在我们来分拆该SQL语句里的父查询和子查询
> 1）语句中的父查询
> select * from score a where degree 2）语句中的子查询
> select avg(degree) from score b where a.course_id=b.course_id
> 请注意这个子查询的from子句里只有一张表 b ，但是where子句里却出现了第二张表 a ，
>
>如果单独运行这个子查询，因为子查询没有列出表a，系统会要求输入a.course_id或者直接报错，反正无法顺利执行，但是表a可以在父查询里的from子句中找到，面对这种情况数据库引擎会采取逐条取主查询记录与子查询实施比对以确定是否检出该条记录，最后汇总各次检索的结果输出整个记录集。
> 这个特殊的SQL语句检索过程大致如下：
>
>取出首条记录的a.course_id用作过滤，子查询里以avg函数得到该课程的平均分，主查询以分数比对平均分，满足条件保留否则抛弃（degree小于平均分的留下）；
> 跟着判断父查询表下一条记录，处理过程相同，最后合并各次判断结果从而的到最终结果。
> 这种特殊的写法可以规避输出包含非分组字段，而分组不得输出非分组字段的矛盾。
## 34、 查询所有任课教师的name和department。
```mysql
select name,department from teacher where teacher_id in (
select teacher_id from course);35 、查询所有未讲课的教师的name和department。
select name,department from teacher where teacher_id not in (
select teacher_id from course where course_id in (
select course_id from score ));36、查询至少有2名男生的班号。
select class from student where sex = '男'
group by class
having count(sex) >1;37、查询student表中不姓“王”的同学记录。
select * from student where name not like '王%%';38、查询student表中每个学生的姓名和年龄。
select name,year(now())-year(birthday) from student;39、查询student表中最大和最小的birthday日期值。
select max(birthday),min(birthday) from student;40、以班号和年龄从大到小的顺序查询student表中的全部记录。
select * from student order by class desc,birthday;41、查询“男”教师及其所上的课程。
#需要两个表的信息用where或者连接查询
select course_id,name,name from course,teacher where course.teacher_id=teacher.teacher_id and sex='男';
select course_id,name,name from course inner join teacher on course.teacher_id=teacher.teacher_id where sex='男';42、查询最高分同学的student_id、course_id和degree列。
select student_id,course_id,degree from score order by degree desc limit 0,1;
select student_id,course_id,degree from score where degree = (
select max(degree) from score);43、查询和“李军”同性别的所有同学的name。
select name from student where sex = (
select sex from student where name = '李军');44、查询和“李军”同性别并同班的同学name。
select name from student where sex = (
select sex from student where name = '李军')
and class = (
select class from student where name = '李军');45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select * from score where course_id = (
select course_id from course where name = '计算机导论') and
student_id in (select student_id from student where sex = '男');