mysql连接查询应用题_MySQL连表查询练习题

建库

库名：linux50 字符集：utf8 校验规则：utf8_general_ci

mysql> create database if not exists linux5 charset utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| linux5 |

| mysql |

| performance_schema |

| test |

| world |

+--------------------+

建表

表名：student(学生表)

字段

数据类型要求

是否为空

注释

sno

最多20位

否

学号(主键)

sname

可变长

否

学生姓名

sage

最小整数，非负数

否

学生年龄

ssex

0，1

否

学生性别(1是男，0是女)默认为男)

sbirthday

时间类型

默认为空

学生生日

class

可变长

否

学生班级

mysql> create table student(

sno bigint(20) #zerofill# not null primary key auto_increment comment '学号',

sname varchar(12) not null comment '学生姓名',

sage tinyint unsigned not null comment '学生年龄',

ssex enum('0','1') not null default '1' comment '学生性别(1是男，0是女)默认为男',

sbirthday datetime comment '学生生日',

class varchar(10) not null comment '学生班级');

mysql> desc student;

+-----------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+---------------------+------+-----+---------+----------------+

| sno | bigint(20) | NO | PRI | NULL | auto_increment |

| sname | varchar(12) | NO | | NULL | |

| sage | tinyint(3) unsigned | NO | | NULL | |

| ssex | enum('0','1') | NO | | 1 | |

| sbirthday | datetime | YES | | NULL | |

| class | varchar(10) | NO | | NULL | |

+-----------+---------------------+------+-----+---------+----------------+

表名：course(课程表)

字段

数据类型要求

是否为空

注释

cno

最多20位

否

课程号(主键)

cname

可变长

否

课程名称

tno

可变长

否

教师编号

mysql> create table course(

cno bigint(20) not null primary key auto_increment comment '课程号',

cname varchar(20) not null comment '课程名称',

tno varchar(20) not null comment '教师编号');

mysql> desc course;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| cno | bigint(20) | NO | PRI | NULL | auto_increment |

| cname | varchar(20) | NO | | NULL | |

| tno | varchar(20) | NO | | NULL | |

+-------+-------------+------+-----+---------+----------------+

表名：score(成绩表)

字段

数据类型要求

是否为空

注释

sno

最多20位

否

学号(主键)

cno

最多20位

否

课程号(主键)

mark

浮点数(4,1)

否

成绩

注意：sno和cno在另外两个表中是主键，在这里应该是外键，不过咱们不需要创建，了解即可

mysql> create table score(

sno bigint(20) comment '学号',

cno bigint(20) comment '课程号',

mark float(4,1) not null comment '成绩'),

primary key (sno,cno));

mysql> desc score;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| sno | bigint(20) | NO | PRI | NULL | |

| cno | bigint(20) | NO | PRI | NULL | |

| mark | float(4,1) | NO | | NULL | |

+-------+------------+------+-----+---------+-------+

表名：teacher(教师表)

字段

数据类型要求

是否为空

注释

tno

最多20位

否

教师编号(主键)

tname

可变长

否

教师姓名

tage

最小整数，非负数

否

教师年龄

tsex

0，1

否

教师性别(1是男，0是女)默认为男)

prof

可变长

是

教师职称

depart

可变长

否

教师部门

mysql> create table teacher(

tno bigint(20) not null primary key auto_increment comment '教师编号',

tname varchar(12) not null comment '教师姓名',

tage tinyint unsigned not null comment '教师年龄',

tsex enum('0','1') not null default '1' comment '教师性别(1是男，0是女)默认为男',

prof varchar(20) comment '教师职称',

depart varchar(20) not null comment '教师部门');

mysql> desc teacher;

+--------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+-------+

| tno | bigint(20) | NO | PRI | NULL | |

| tname | varchar(12) | NO | | NULL | |

| tage | tinyint(3) unsigned | NO | | NULL | |

| tsex | enum('0','1') | NO | | 1 | |

| prof | varchar(20) | YES | | NULL | |

| depart | varchar(20) | NO | | NULL | |

+--------+---------------------+------+-----+---------+-------+

练习题

插入数据练习：

1.将自己班级小组所有人员信息插入到student表中(数据自定义)

mysql> insert into student(sname,sage,ssex,class) values('张毅',18,'1',5),('李琦',18,'1',5),('王相伟',18,'1',5),('庄晨浩',18,'1',5),('黄杨浩',18,'1',5),('巩景云',18,'0',5),('陈妙',18,'0',5),('朱慧萍',18,'0',5);

mysql> select * from student;

+-----+------------+------+------+-----------+-------+

| sno | sname | sage | ssex | sbirthday | class |

+-----+------------+------+------+-----------+-------+

| 9 | 张毅 | 18 | 1 | NULL | 5 |

| 10 | 李琦 | 18 | 1 | NULL | 1 |

| 11 | 王相伟 | 18 | 1 | NULL | 2 |

| 12 | 庄晨浩 | 18 | 1 | NULL | 5 |

| 13 | 黄杨浩 | 18 | 1 | NULL | 5 |

| 14 | 巩景云 | 18 | 0 | NULL | 5 |

| 15 | 陈妙 | 18 | 0 | NULL | 5 |

| 16 | 朱 慧萍 | 18 | 0 | NULL | 5 |

+-----+------------+------+------+-----------+-------+

2.将曾导、徐导、李导信息插入教师表中(数据自定义)

mysql> insert into teacher(tno,tname,tage,prof,depart) values(1,'曾导',20,'讲师','linux'),(2,'徐导',20,'讲师','linux'),(3,'李导',20,'讲师','linux')(4,'邱导',20,'讲师','python'),(5,'林导',20,'讲师','DBA');

mysql> select * from teacher;

+-----+--------+------+------+--------+--------+

| tno | tname | tage | tsex | prof | depart |

+-----+--------+------+------+--------+--------+

| 1 | 曾导 | 20 | 1 | 讲师 | linux |

| 2 | 徐导 | 20 | 1 | 讲师 | linux |

| 3 | 李导 | 20 | 1 | 讲师 | linux |

| 4 | 邱导 | 20 | 1 | 讲师 | python |

| 5 | 林导 | 20 | 1 | 讲师 | DBA |

+-----+--------+------+------+--------+--------+

3.将数学、语文、英语学科插入到课程表中(数据自定义)

mysql> insert into course(cno,cname,tno) values(1,'DBA',1),(2,'python',2),(3,'linux',1);

mysql> select * from course;

+-----+--------+-----+

| cno | cname | tno |

+-----+--------+-----+

| 1 | DBA | 1 |

| 2 | python | 2 |

| 3 | linux | 1 |

+-----+--------+-----+

4.将分数插入到成绩表中(数据自定义)

mysql> insert into score(sno,cno,mark) values(9,'1',98),(10,'1',8),(11,'2',80),(12,'1',65),(13,'2',98),(14,'1',98),(15,'1',98),(16,'1',98);

mysql> select * from score;

+-----+-----+------+

| sno | cno | mark |

+-----+-----+------+

| 9 | 1 | 98.0 |

| 10 | 1 | 8.0 |

| 11 | 2 | 80.0 |

| 12 | 1 | 65.0 |

| 13 | 2 | 98.0 |

| 14 | 1 | 98.0 |

| 15 | 1 | 98.0 |

| 16 | 1 | 98.0 |

+-----+-----+------+

查询练习：

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

mysql> select sname,ssex,class from student;

+------------+------+-------+

| sname | ssex | class |

+------------+------+-------+

| 张毅 | 1 | 5 |

| 李琦 | 1 | 1 |

| 王相伟 | 1 | 2 |

| 庄晨浩 | 1 | 5 |

| 黄杨浩 | 1 | 5 |

| 巩景云 | 0 | 5 |

| 陈妙 | 0 | 5 |

| 朱 慧萍 | 0 | 5 |

+------------+------+-------+

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

mysql> select distinct(depart) from teacher;

+--------+

| depart |

+--------+

| linux |

| python |

| DBA |

+--------+

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

mysql> select * from student;

+-----+------------+------+------+-----------+-------+

| sno | sname | sage | ssex | sbirthday | class |

+-----+------------+------+------+-----------+-------+

| 9 | 张毅 | 18 | 1 | NULL | 5 |

| 10 | 李琦 | 18 | 1 | NULL | 1 |

| 11 | 王相伟 | 18 | 1 | NULL | 2 |

| 12 | 庄晨浩 | 18 | 1 | NULL | 5 |

| 13 | 黄杨浩 | 18 | 1 | NULL | 5 |

| 14 | 巩景云 | 18 | 0 | NULL | 5 |

| 15 | 陈妙 | 18 | 0 | NULL | 5 |

| 16 | 朱 慧萍 | 18 | 0 | NULL | 5 |

+-----+------------+------+------+-----------+-------+

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

mysql> select * from score where mark>60 and mark<80;

+-----+-----+------+

| sno | cno | mark |

+-----+-----+------+

| 12 | 1 | 65.0 |

+-----+-----+------+

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

mysql> select * from score where mark=98 or mark=80 or mark=65;

mysql> select * from score where mark in (98,80,65);

+-----+-----+------+

| sno | cno | mark |

+-----+-----+------+

| 9 | 1 | 98.0 |

| 11 | 2 | 80.0 |

| 12 | 1 | 65.0 |

| 13 | 2 | 98.0 |

| 14 | 1 | 98.0 |

| 15 | 1 | 98.0 |

| 16 | 1 | 98.0 |

+-----+-----+------+

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

mysql> select * from student where class='1' or ssex='0';

+-----+------------+------+------+-----------+-------+

| sno | sname | sage | ssex | sbirthday | class |

+-----+------------+------+------+-----------+-------+

| 10 | 李琦 | 18 | 1 | NULL | 1 |

| 14 | 巩景云 | 18 | 0 | NULL | 5 |

| 15 | 陈妙 | 18 | 0 | NULL | 5 |

| 16 | 朱 慧萍 | 18 | 0 | NULL | 5 |

+-----+------------+------+------+-----------+-------+

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

mysql> select * from student order by class desc;

+-----+------------+------+------+-----------+-------+

| sno | sname | sage | ssex | sbirthday | class |

+-----+------------+------+------+-----------+-------+

| 9 | 张毅 | 18 | 1 | NULL | 5 |

| 12 | 庄晨浩 | 18 | 1 | NULL | 5 |

| 13 | 黄杨浩 | 18 | 1 | NULL | 5 |

| 14 | 巩景云 | 18 | 0 | NULL | 5 |

| 15 | 陈妙 | 18 | 0 | NULL | 5 |

| 16 | 朱 慧萍 | 18 | 0 | NULL | 5 |

| 11 | 王相伟 | 18 | 1 | NULL | 2 |

| 10 | 李琦 | 18 | 1 | NULL | 1 |

+-----+------------+------+------+-----------+-------+

8 rows in set (0.00 sec)

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

mysql> select * from score order by mark desc,cno asc;

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

mysql> select count(sname) from student where class=5;

+--------------+

| count(sname) |

+--------------+

| 6 |

+--------------+

1 row in set (0.00 sec)

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

mysql> select student.sno as 学号,course.cname as 课程,score.mark as 分数

from student

join score on score.sno=student.sno

join course on score.cno=course.cno

where course.tno=1;

mysql> select teacher.tname,student.sname,course.cname,score.mark

from student

join score on score.sno=student.sno

join course on score.cno=course.cno

join teacher on teacher.tno=course.tno

where teacher.tname='曾导';

+--------+--------+--------+

| 学号 | 课程 | 分数 |

+--------+--------+--------+

| 9 | DBA | 98.0 |

| 10 | DBA | 8.0 |

| 12 | DBA | 65.0 |

| 14 | DBA | 98.0 |

| 15 | DBA | 98.0 |

| 16 | DBA | 98.0 |

+--------+--------+--------+

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

mysql> select student.sname,score.mark,course.cname,teacher.tname,teacher.prof,teacher.depart

from student

join score on score.sno=student.sno

join course on score.cno=course.cno

join teacher on teacher.tno=course.tno

where student.ssex='1' and course.cname='DBA';

+-----------+------+-------+--------+--------+--------+

| sname | mark | cname | tname | prof | depart |

+-----------+------+-------+--------+--------+--------+

| 张毅 | 98.0 | DBA | 曾导 | 讲师 | linux |

| 李琦 | 8.0 | DBA | 曾导 | 讲师 | linux |

| 庄晨浩 | 65.0 | DBA | 曾导 | 讲师 | linux |

+-----------+------+-------+--------+--------+--------+

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

mysql> select student.sname,score.mark,course.cname,teacher.tname,teacher.prof,teacher.depart

from student

join score on score.sno=student.sno

join course on score.cno=course.cno

join teacher on teacher.tno=course.tno

where student.ssex='1' and course.cname='DBA'

order by mark desc;

+-----------+------+-------+--------+--------+--------+

| sname | mark | cname | tname | prof | depart |

+-----------+------+-------+--------+--------+--------+

| 张毅 | 98.0 | DBA | 曾导 | 讲师 | linux |

| 庄晨浩 | 65.0 | DBA | 曾导 | 讲师 | linux |

| 李琦 | 8.0 | DBA | 曾导 | 讲师 | linux |

+-----------+------+-------+--------+--------+--------+