Mysql连表查询习题

表一

mysql> create database linux50 charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux50            |
| ming               |
| mysql              |
| performance_schema |
| test               |
| world              |
| xudao              |
+--------------------+
8 rows in set (0.00 sec)
mysql> \u linux50
Database changed
mysql> create table student(sno bigint(20) not null primary key auto_increment comment ‘学号‘,
    -> sname varchar(300) not null comment ‘学生姓名‘,
    -> sage tinyint unsigned not null comment ‘学生年龄‘,
    -> ssex enum(‘1‘,‘0‘) not null  default ‘1‘ comment ‘学生性别‘,
    -> sbirthday datetime  default null comment ‘学生生日‘,
    -> class int not null comment ‘学生班级‘) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> select *from student;
+-----+-----------+------+------+---------------------+-------+
| sno | sname     | sage | ssex | sbirthday           | class |
+-----+-----------+------+------+---------------------+-------+
|   1 | 王丽英    |   18 | 1    | 2018-11-28 17:45:58 |     7 |
|   2 | 王亚娇    |   19 | 0    | 2018-11-28 17:50:07 |     7 |
|   3 | 程康华    |   22 | 1    | 2018-11-28 17:50:40 |     7 |
|   4 | 郭亚望    |   20 | 1    | 2018-11-28 17:51:19 |     7 |
|   5 | 文长清    |   21 | 1    | 2018-11-28 17:51:42 |     7 |
|   6 | 马慧芬    |   20 | 1    | 2018-11-28 20:54:04 |     5 |
|   7 | 王晶      |   20 | 0    | 2018-11-28 20:55:00 |     3 |
+-----+-----------+------+------+---------------------+-------+

表二

mysql> create table source(cno bigint(20) not null primary key auto_increment comment ‘课程号‘,  cname varchar(50) not null comment ‘课程名称‘,  tno int(3) zerofill not null  comment ‘教师编号‘  );
Query OK, 0 rows affected (0.02 sec)
mysql> select *from source;
+-----+--------+-----+
| cno | cname  | tno |
+-----+--------+-----+
|   1 | 语文   | 001 |
|   2 | 数学   | 002 |
|   3 | 英语   | 003 |
+-----+--------+-----+

表三

mysql> create table score(sno bigint(20) not null comment ‘学号‘,
    -> cno bigint(20) not null  comment ‘课程号‘,
    -> mark double(4,1) not null comment ‘成绩‘,
    -> primary key(sno,cno)
    -> );
   mysql> select *from score;
+-----+-----+-------+
| sno | cno | mark  |
+-----+-----+-------+
|   1 |   1 |  90.0 |
|   2 |   1 |  90.0 |
|   2 |   2 |  70.0 |
|   2 |   3 |  70.0 |
|   3 |   1 |  95.0 |
|   3 |   2 | 100.0 |
+-----+-----+-------+
6 rows in set (0.00 sec)

表四

create table teacher(cno int(3) zerofill not null primary key auto_increment comment ‘教师编号‘,
tname varchar(50) not null comment ‘教师姓名‘,
tage tinyint unsigned not null comment ‘教师年龄‘,
tsex enum(‘1‘,‘0‘) not null default ‘1‘ comment ‘教师性别‘,
prof varchar(100) comment ‘教师职称‘,
depart varchar(50) comment ‘教师部门‘
);

集合练习

查询练习：

1.查询student表中的所有记录的sname、ssex和class列。

mysql> select sname,ssex,class from student;
+-----------+------+-------+
| sname     | ssex | class |
+-----------+------+-------+
| 王丽英    | 1    |     7 |
| 王亚娇    | 0    |     7 |
| 程康华    | 1    |     7 |
| 郭亚望    | 1    |     7 |
| 文长清    | 1    |     7 |
| 马慧芬    | 1    |     5 |
| 王晶      | 0    |     3 |
+-----------+------+-------+

2.查询教师所有的单位即不重复的depart列。

mysql> select *from teacher;
+-----+--------------+------+------+--------------+--------------+
| cno | tname        | tage | tsex | prof         | depart       |
+-----+--------------+------+------+--------------+--------------+
| 001 | 增志高翔     |   23 | 1    | DBA老大      | DBA系        |
| 002 | 徐亮伟       |   24 | 1    | 讲师老大     | Linux系      |
| 003 | 李泳谊       |   26 | 1    | 综合老大     | 老男孩系     |
| 004 | 老男孩       |   24 | 1    | 公司老大     | 老男孩系     |
+-----+--------------+------+------+--------------+--------------+

3.查询student表的所有记录。

mysql> select *from student;
+-----+-----------+------+------+---------------------+-------+
| sno | sname     | sage | ssex | sbirthday           | class |
+-----+-----------+------+------+---------------------+-------+
|   1 | 王丽英    |   18 | 1    | 2018-11-28 17:45:58 |     7 |
|   2 | 王亚娇    |   19 | 0    | 2018-11-28 17:50:07 |     7 |
|   3 | 程康华    |   22 | 1    | 2018-11-28 17:50:40 |     7 |
|   4 | 郭亚望    |   20 | 1    | 2018-11-28 17:51:19 |     7 |
|   5 | 文长清    |   21 | 1    | 2018-11-28 17:51:42 |     7 |
|   6 | 马慧芬    |   20 | 1    | 2018-11-28 20:54:04 |     5 |
|   7 | 王晶      |   20 | 0    | 2018-11-28 20:55:00 |     3 |
+-----+-----------+------+------+---------------------+-------+

4.查询score表中成绩在60到80之间的所有记录。

5.查询score表中成绩为85，86或88的记录。

select * from score where Degree in (90,95,70);

6.查询student表中7班或性别为“女”的同学记录。

7.以class降序查询Student表的所有记录。

mysql> select *from student order by class desc;
+-----+-----------+------+------+---------------------+-------+
| sno | sname     | sage | ssex | sbirthday           | class |
+-----+-----------+------+------+---------------------+-------+
|   1 | 王丽英    |   18 | 1    | 2018-11-28 17:45:58 |     7 |
|   2 | 王亚娇    |   19 | 0    | 2018-11-28 17:50:07 |     7 |
|   3 | 程康华    |   22 | 1    | 2018-11-28 17:50:40 |     7 |
|   4 | 郭亚望    |   20 | 1    | 2018-11-28 17:51:19 |     7 |
|   5 | 文长清    |   21 | 1    | 2018-11-28 17:51:42 |     7 |
|   6 | 马慧芬    |   20 | 1    | 2018-11-28 20:54:04 |     5 |
|   7 | 王晶      |   20 | 0    | 2018-11-28 20:55:00 |     3 |

8.以cno升序、mark降序查询Score表的所有记录

mysql> select *from score order by cno;
+-----+-----+-------+
| sno | cno | mark  |
+-----+-----+-------+
|   1 |   1 |  90.0 |
|   2 |   1 |  90.0 |
|   3 |   1 |  95.0 |
|   2 |   2 |  70.0 |
|   3 |   2 | 100.0 |
|   2 |   3 |  70.0 |
+-----+-----+-------+
mysql> select *from score order by  mark desc;
+-----+-----+-------+
| sno | cno | mark  |
+-----+-----+-------+
|   3 |   2 | 100.0 |
|   3 |   1 |  95.0 |
|   1 |   1 |  90.0 |
|   2 |   1 |  90.0 |
|   2 |   2 |  70.0 |
|   2 |   3 |  70.0 |
+-----+-----+-------+

9.查询7班的学生人数。

mysql> select count(*)  from student where class=‘7‘;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

10.查询”曾志高翔“教师任课的学生成绩。

mysql> select teacher.tname, student.sno,student.sname,score.mark 
    -> from teacher,student,score,course
    -> where student.sno=score.sno and
    -> score.cno=course.cno
    -> and course.tno=teacher.tno 
    -> and teacher.tno=‘001‘;

11.查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。

mysql> select student.sname,score.mark,teacher.tname,teacher.prof,teacher.depart 
    -> from teacher,student,score,course
    -> where student.sno=score.sno and
    -> score.cno=course.cno and
    -> course.tno=teacher.tno and
    -> student.ssex=‘1‘
-> and course.cname=‘语文‘;

12.把11题查出的成绩按照降序排序。

mysql> select student.sname,score.mark,teacher.tname,teacher.prof,teacher.depart 
    -> from teacher,student,score,course
    -> where student.sno=score.sno and
    -> score.cno=course.cno and
    -> course.tno=teacher.tno and
    -> student.ssex=‘1‘
-> and course.cname=‘语文‘
-> order by score.mark desc;

Mysql连表查询习题

原文：http://blog.51cto.com/13859027/2323491