频道澳门葡京手机版网址
登录注册
澳门葡京手机版网址 > 网络 > 其他 > 正文

Mysql连表查询习题

2018-11-30 10:51:37           
收藏   我要投稿

练习题 表一

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;

上一篇:Centos7 编译安装搭建LAMP平台
下一篇:Linux 操作命令
相关文章
图文推荐

关于大家 | 联系大家 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 澳门葡京手机版网址_澳门新莆京娱乐_www.88807.com - 点此进入--致力于做实用的IT技术学习网站

XML 地图 | Sitemap 地图