MySQL查询练习
- 准备数据表
mysql> create database work;
Query OK, 1 row affected (0.01 sec)
mysql> use work
Database changed
学生表student:(学号，姓名，出生年月，所在班级)
mysql> create table student(
-> sno varchar(20) primary key,
-> sname varchar(20) not null,
-> sbirthday datetime,
-> class varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
教师表teacher(教师编号,教师名字,教师性别,出生年月,职称，所在部门)
mysql> create table teacher(
-> tno varchar(20) primary key,
-> tname varchar(20) not null,
-> tsex varchar(20) not null,
-> tbirthday datetime,
-> prof varchar(20) not null,
-> depart varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)
课程表course:(课程号,课程名称,教师编号)
mysql> create table course(
-> cno varchar(20) primary key,
-> cname varchar(20) not null,
-> tno varchar(20) not null,
-> foreign key(tno) references teacher(tno)
-> );
Query OK, 0 rows affected (0.02 sec)
成绩表score(学号,课程号,成绩)
mysql> create table score(
-> sno varchar(20) primary key,
-> cno varchar(20) not null,
-> degree decimal,
-> foreign key(sno) references student(sno),
-> foreign key(cno) references course(cno)
-> );
Query OK, 0 rows affected (0.03 sec)
- 往数据表中添加数据
添加学生信息
mysql> insert into student values('101','zeng','1977-09-01','95033');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values('102','ming','1975-09-01','95031');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values('103','wang','1976-09-01','95033');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values('104','li','1977-02-01','95033');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values('105','lu','1972-02-09','95031');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values('106','jun','1974-06-03','95031');
Query OK, 1 row affected (0.01 sec)
添加教师信息
mysql> insert into teacher values('804','cheng','m','1958-09-01','lecturer','computer');
Query OK, 1 row affected (0.01 sec)
mysql> insert into teacher values('805','xun','m','1969-09-01','lecturer','math');
Query OK, 1 row affected (0.00 sec)
mysql> insert into teacher values('806','ping','w','1972-09-01','professor','computer');
Query OK, 1 row affected (0.00 sec)
mysql> insert into teacher values('807','bing','w','1977-08-16','professor','math');
Query OK, 1 row affected (0.01 sec)
添加课程信息
mysql> insert into course values('3-105','computer','804');
Query OK, 1 row affected (0.01 sec)
mysql> insert into course values('4-105','math','805');
Query OK, 1 row affected (0.00 sec)
mysql> insert into course values('3-107','computer','806');
Query OK, 1 row affected (0.00 sec)
mysql> insert into course values('4-115','math','807');
Query OK, 1 row affected (0.00 sec)
添加成绩信息
mysql> insert into score values('101','3-105','86');
Query OK, 1 row affected (0.01 sec)
mysql> insert into score values('102','4-105','96');
Query OK, 1 row affected (0.00 sec)
mysql> insert into score values('103','3-105','89');
Query OK, 1 row affected (0.00 sec)
mysql> insert into score values('104','3-105','66');
Query OK, 1 row affected (0.00 sec)
mysql> insert into score values('105','3-107','81');
Query OK, 1 row affected (0.00 sec)
mysql> insert into score values('106','4-115','76');
Query OK, 1 row affected (0.01 sec)
- 查询练习
3.1 查询student表中的所有记录的sname，class类
mysql> select sname,class from student;
+-------+-------+
| sname | class |
+-------+-------+
| zeng | 95033 |
| ming | 95031 |
| wang | 95033 |
| li | 95033 |
| lu | 95031 |
| jun | 95031 |
+-------+-------+
6 rows in set (0.00 sec)
3.2 查询教师所有的单位即不重复的depart列(distinct排除重复）
mysql> select distinct depart from teacher;
+----------+
| depart |
+----------+
| computer |
| math |
+----------+
2 rows in set (0.00 sec)
3.3 查询score表中成绩在60到80之间的所有记录
方法一：(查询区间 between...and...)
mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 104 | 3-105 | 66 |
| 106 | 4-115 | 76 |
+-----+-------+--------+
2 rows in set (0.00 sec)
方法二：(直接使用运算符比较)
mysql> select * from score where degree>60 and degree<80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 104 | 3-105 | 66 |
| 106 | 4-115 | 76 |
+-----+-------+--------+
2 rows in set (0.08 sec)
3.4 查询score表中成绩为85，86，96的记录
mysql> select * from score where degree in(85,86,96);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 86 |
| 102 | 4-105 | 96 |
+-----+-------+--------+
2 rows in set (0.00 sec
3.5 查询teacher表中"computer"系或性别为"女"的记录
mysql> select * from teacher where depart="computer" or tsex="m";
+-----+-------+------+---------------------+-----------+----------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+------+---------------------+-----------+----------+
| 804 | cheng | m | 1958-09-01 00:00:00 | lecturer | computer |
| 805 | xun | m | 1969-09-01 00:00:00 | lecturer | math |
| 806 | ping | w | 1972-09-01 00:00:00 | professor | computer |
+-----+-------+------+---------------------+-----------+----------+
3 rows in set (1.63 sec)
3.6 以class降序查询student表中的所有记录(asc或默认表示升序，desc表示降序)
mysql> select * from student order by class desc;
+-----+-------+---------------------+-------+
| sno | sname | sbirthday | class |
+-----+-------+---------------------+-------+
| 101 | zeng | 1977-09-01 00:00:00 | 95033 |
| 103 | wang | 1976-09-01 00:00:00 | 95033 |
| 104 | li | 1977-02-01 00:00:00 | 95033 |
| 102 | ming | 1975-09-01 00:00:00 | 95031 |
| 105 | lu | 1972-02-09 00:00:00 | 95031 |
| 106 | jun | 1974-06-03 00:00:00 | 95031 |
+-----+-------+---------------------+-------+
6 rows in set (1.66 sec)
3.7 以cno升序、degree降序查询score表的所有记录
mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 89 |
| 101 | 3-105 | 86 |
| 104 | 3-105 | 66 |
| 105 | 3-107 | 81 |
| 102 | 4-105 | 96 |
| 106 | 4-115 | 76 |
+-----+-------+--------+
6 rows in set (0.00 sec)
3.8 查询"95031"班的学生人数
mysql> select count(*) from student where class="95031";
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
3.9 查询score表中的最高分的学生学号和课程号。(子查询或者排序)
子查询：
mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno |
+-----+-------+
| 102 | 4-105 |
+-----+-------+
1 row in set (0.00 sec)
排序：(当最大值有多个时,使用排序需要注意，若最大值个数出现错误查询结果将不准确)
limit ：第一个数字表示开始位置，第二个数字表示查询多少条
mysql> select sno,cno from score order by degree desc limit 0,1;
+-----+-------+
| sno | cno |
+-----+-------+
| 102 | 4-105 |
+-----+-------+
1 row in set (0.00 sec)
3.10 查询每门课的平均成绩
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 80.3333 |
| 3-107 | 81.0000 |
| 4-105 | 96.0000 |
| 4-115 | 76.0000 |
+-------+-------------+
4 rows in set (0.00 sec)
3.11 查询score表中至少有2名学生选修的并以3开头的课程的平均分数(group by后面跟条件需要用having)
mysql> select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like '3%';
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 80.3333 |
+-------+-------------+
1 row in set (0.00 sec)
3.12 查询分数大于70，小于90的sno列
mysql> select sno,degree from score where degree>70 and degree<90;
+-----+--------+
| sno | degree |
+-----+--------+
| 101 | 86 |
| 103 | 89 |
| 105 | 81 |
| 106 | 76 |
+-----+--------+
4 rows in set (0.00 sec)
mysql> select sno,degree from score where degree between 70 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 101 | 86 |
| 103 | 89 |
| 105 | 81 |
| 106 | 76 |
+-----+--------+
4 rows in set (0.00 sec)
3.13 查询所有学生的sname，cno，和degree列
mysql> select sname,cno,degree from student,score where student.sno=score.sno;
+-------+-------+--------+
| sname | cno | degree |
+-------+-------+--------+
| zeng | 3-105 | 86 |
| ming | 4-105 | 96 |
| wang | 3-105 | 89 |
| li | 3-105 | 66 |
| lu | 3-107 | 81 |
| jun | 4-115 | 76 |
+-------+-------+--------+
6 rows in set (0.00 sec)
3.14 查询所有学生的sno,cname和degree列
mysql> select sno,cname,degree from course,score where course.cno=score.cno;
+-----+----------+--------+
| sno | cname | degree |
+-----+----------+--------+
| 101 | computer | 86 |
| 103 | computer | 89 |
| 104 | computer | 66 |
| 105 | computer | 81 |
| 102 | math | 96 |
| 106 | math | 76 |
+-----+----------+--------+
6 rows in set (0.00 sec)
3.15 查询所有学生的sname,cname和degree列
mysql> select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
+-------+----------+--------+
| sname | cname | degree |
+-------+----------+--------+
| zeng | computer | 86 |
| wang | computer | 89 |
| li | computer | 66 |
| lu | computer | 81 |
| ming | math | 96 |
| jun | math | 76 |
+-------+----------+--------+
6 rows in set (0.00 sec)
3.16 查询"95031"班学生每门课的平均分
mysql> select cno,avg(degree) from score where sno in (select sno from student where class = "95031")
-> group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-107 | 81.0000 |
| 4-105 | 96.0000 |
| 4-115 | 76.0000 |
+-------+-------------+
3 rows in set (0.00 sec)
3.17 查询选修"3-105"课程的成绩高于"104"号同学的所有同学的记录
mysql> select * from score where cno='3-105' and degree>(select degree from score where sno='104' and cno='3-105');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 86 |
| 103 | 3-105 | 89 |
+-----+-------+--------+
2 rows in set (0.00 sec)
3.18 查询成绩高于学号为"104"，课程号为"3-105"的成绩的所有记录
mysql> select * from score where degree>(select degree from score where sno='104' and cno='3-105');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 86 |
| 102 | 4-105 | 96 |
| 103 | 3-105 | 89 |
| 105 | 3-107 | 81 |
| 106 | 4-115 | 76 |
+-----+-------+--------+
5 rows in set (1.74 sec)
3.19 查询和学号104，101同学同年出生的所有学生的sno，sname和sbirthday列
mysql> select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (104,101));
+-----+-------+---------------------+
| sno | sname | sbirthday |
+-----+-------+---------------------+
| 101 | zeng | 1977-09-01 00:00:00 |
| 104 | li | 1977-02-01 00:00:00 |
+-----+-------+---------------------+
2 rows in set (0.00 sec)
3.20 查询"ping"教师任课的学生成绩
mysql> select degree from score where cno=(select cno from course where tno=(select tno from teacher where tname='ping'));
+--------+
| degree |
+--------+
| 81 |
+--------+
1 row in set (0.00 sec)
3.21 查询选修某课程的同学人数多于2人的教师姓名
mysql> select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>2));
+-------+
| tname |
+-------+
| cheng |
+-------+
1 row in set (0.00 sec)
3.22 查询"95033"班和"95031"班全体学生的记录
mysql> select * from student where class in ('95033','95031');
+-----+-------+---------------------+-------+
| sno | sname | sbirthday | class |
+-----+-------+---------------------+-------+
| 101 | zeng | 1977-09-01 00:00:00 | 95033 |
| 102 | ming | 1975-09-01 00:00:00 | 95031 |
| 103 | wang | 1976-09-01 00:00:00 | 95033 |
| 104 | li | 1977-02-01 00:00:00 | 95033 |
| 105 | lu | 1972-02-09 00:00:00 | 95031 |
| 106 | jun | 1974-06-03 00:00:00 | 95031 |
+-----+-------+---------------------+-------+
6 rows in set (0.00 sec)
3.23 查询成绩在85分以上的课程cno
mysql> select cno,degree from score where degree>85;
+-------+--------+
| cno | degree |
+-------+--------+
| 3-105 | 86 |
| 4-105 | 96 |
| 3-105 | 89 |
+-------+--------+
3 rows in set (0.00 sec)
3.24 查询出"computer"部门的教师所教课程的成绩表
mysql> select degree,cno from score where cno in (select cno from course where tno in (select tno from teacher where depart='computer'));
+--------+-------+
| degree | cno |
+--------+-------+
| 86 | 3-105 |
| 89 | 3-105 |
| 66 | 3-105 |
| 81 | 3-107 |
+--------+-------+
4 rows in set (0.00 sec)
3.25 查询出"computer"与"math"中独特职称(是指对方没有的职称)的tname和prof（union求并集）
mysql> select tname,prof from teacher where depart='math' and prof not in(select prof from teacher where depart='computer') union
-> select tname,prof from teacher where depart='computer' and prof not in(select prof from teacher where depart='math');
Empty set (0.37 sec)
