首页 > 数据库技术 > 详细

Mysql连表查询习题

时间:2018-11-29 10:51:16      阅读:253      评论:0      收藏:0      [点我收藏+]
练习题

表一

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

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!