练习题 表一
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 &世界杯外围投注官网39;学号&世界杯外围投注官网39;,
-> sname varchar(300) not null comment &世界杯外围投注官网39;学生姓名&世界杯外围投注官网39;,
-> sage tinyint unsigned not null comment &世界杯外围投注官网39;学生年龄&世界杯外围投注官网39;,
-> ssex enum(&世界杯外围投注官网39;1&世界杯外围投注官网39;,&世界杯外围投注官网39;0&世界杯外围投注官网39;) not null default &世界杯外围投注官网39;1&世界杯外围投注官网39; comment &世界杯外围投注官网39;学生性别&世界杯外围投注官网39;,
-> sbirthday datetime default null comment &世界杯外围投注官网39;学生生日&世界杯外围投注官网39;,
-> class int not null comment &世界杯外围投注官网39;学生班级&世界杯外围投注官网39;) 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 &世界杯外围投注官网39;课程号&世界杯外围投注官网39;, cname varchar(50) not null comment &世界杯外围投注官网39;课程名称&世界杯外围投注官网39;, tno int(3) zerofill not null comment &世界杯外围投注官网39;教师编号&世界杯外围投注官网39; );
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 &世界杯外围投注官网39;学号&世界杯外围投注官网39;,
-> cno bigint(20) not null comment &世界杯外围投注官网39;课程号&世界杯外围投注官网39;,
-> mark double(4,1) not null comment &世界杯外围投注官网39;成绩&世界杯外围投注官网39;,
-> 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 &世界杯外围投注官网39;教师编号&世界杯外围投注官网39;,
tname varchar(50) not null comment &世界杯外围投注官网39;教师姓名&世界杯外围投注官网39;,
tage tinyint unsigned not null comment &世界杯外围投注官网39;教师年龄&世界杯外围投注官网39;,
tsex enum(&世界杯外围投注官网39;1&世界杯外围投注官网39;,&世界杯外围投注官网39;0&世界杯外围投注官网39;) not null default &世界杯外围投注官网39;1&世界杯外围投注官网39; comment &世界杯外围投注官网39;教师性别&世界杯外围投注官网39;,
prof varchar(100) comment &世界杯外围投注官网39;教师职称&世界杯外围投注官网39;,
depart varchar(50) comment &世界杯外围投注官网39;教师部门&世界杯外围投注官网39;
);
集合练习
查询练习:
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=&世界杯外围投注官网39;7&世界杯外围投注官网39;;
+----------+
| 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=&世界杯外围投注官网39;001&世界杯外围投注官网39;;
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=&世界杯外围投注官网39;1&世界杯外围投注官网39;
-> and course.cname=&世界杯外围投注官网39;语文&世界杯外围投注官网39;;
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=&世界杯外围投注官网39;1&世界杯外围投注官网39;
-> and course.cname=&世界杯外围投注官网39;语文&世界杯外围投注官网39;
-> order by score.mark desc;