世界杯外围投注官网

练习题 表一

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;