数据库第五天

一.准备数据

1.创建数据库selectTest

mysql> create database selectTest;
Query OK, 1 row affected (0.12 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db                 |
| how2java           |
| information_schema |
| mysql              |
| performance_schema |
| selecttest         |
| sys                |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql> use selectTest;
Database changed

建表

1.学生表Student5

– 学号
– 姓名
– 性别
– 出生年月日
– 所在班级

mysql> create table student5(
    -> sno varchar(20) primary key,
    -> sname varchar(20) not null,
    -> ssex varchar(1) not null,
    -> sbirthday datetime,
    -> class varchar(20)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> show tables;
+----------------------+
| Tables_in_selecttest |
+----------------------+
| student5             |
+----------------------+
1 row in set (0.00 sec)

学生表创建成功。

2.教师表Teacher5

– 教师编号
– 教师姓名
– 教师性别
– 出生年月日
–职称
–所在部门

mysql> create table teacher(
    -> tno varchar(20) primary key,
    -> tname varchar(20) not null,
    -> tsex varchar(10) not null,
    -> tbirthday datetime,
    -> prof varchar(20) not null,
    -> depart varchar(20) not null
    -> );
Query OK, 0 rows affected (0.47 sec)

3.成绩表Score5

– 学号
– 课程表
– 成绩 decimal类型表示十进制小数，误差比浮点数更小。

mysql> create table score(
    -> sno varchar(20) not null,
    -> cno varchar(20) not null,
    -> degree decimal,
    -> foreign key(sno) references student5(sno),
    -> foreign key(cno) references course(cno),
    -> primary key(sno,cno)
    -> );
Query OK, 0 rows affected (0.43 sec)

4.课程表Course5

– 课程号
– 课程名称
– 教师编号

mysql> create table course(
    -> cno varchar(20) primary key,
    -> cname varchar(20) not null,
    -> tno varchar(20) not null,
    -> foreign key(tno) references teacher(tno)
    -> );
Query OK, 0 rows affected (0.63 sec)

三.往数据表中添加数据

1.学生表

mysql> select * from student5;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 王明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95034 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95035 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95039 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95037 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)

2.教师表

mysql> select * from teacher;
+-----+-----------+------+---------------------+-----------+-----------------+
| tno | tname     | tsex | tbirthday           | prof      | depart          |
+-----+-----------+------+---------------------+-----------+-----------------+
| 801 | 张日名    | 女   | 1975-07-01 00:00:00 | 副教授    | 软件工程系      |
| 802 | 肖永鹏    | 男   | 1974-07-01 00:00:00 | 教授      | 软件工程系      |
| 803 | 张友      | 男   | 1970-10-01 00:00:00 | 教授      | 软件工程系      |
| 804 | 贾宝龙    | 男   | 1988-03-01 00:00:00 | 辅导员    | 软件工程系      |
| 805 | 梦晓      | 男   | 1980-04-01 00:00:00 | 讲师      | 体育系          |
| 806 | 罗娜      | 女   | 1978-06-01 00:00:00 | 讲师      | 计算机系        |
+-----+-----------+------+---------------------+-----------+-----------------+
6 rows in set (0.00 sec)

3.课程表

mysql> select * from course;
+-------+-----------------------+-----+
| cno   | cname                 | tno |
+-------+-----------------------+-----+
| 6-166 | 数字电路              | 801 |
| 6-167 | 排球                  | 805 |
| 6-168 | 计算机组成原理        | 802 |
| 6-169 | 概率论                | 803 |
| 6-170 | 算法                  | 806 |
+-------+-----------------------+-----+
5 rows in set (0.00 sec)

4.成绩表

select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 6-166 |     86 |
| 102 | 6-167 |     88 |
| 103 | 6-168 |     78 |
| 104 | 6-169 |     90 |
| 105 | 6-170 |     85 |
| 106 | 6-167 |     82 |
+-----+-------+--------+
6 rows in set (0.00 sec)

二.查询练习

1.查询student表的所有记录

** *是表示所有字段的意思 **

mysql> select * from student5;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 王明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95034 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95035 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95039 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95037 |
+-----+--------+------+---------------------+-------+

2.查询student表的某些字段

mysql> select sno,sname,ssex from student5;
+-----+--------+------+
| sno | sname  | ssex |
+-----+--------+------+
| 101 | 曾华   | 男   |
| 102 | 王明   | 男   |
| 103 | 王丽   | 女   |
| 104 | 李军   | 男   |
| 105 | 王芳   | 女   |
| 106 | 陆军   | 男   |
+-----+--------+------+

3.查询教师单位的总和即不重复的depart列

mysql> select distinct depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 软件工程系      |
| 体育系          |
| 计算机系        |
+-----------------+

4.查询score表成绩在70-80之间的所有记录

mysql> select * from score where degree between 70 and 80;

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 6-167 |     80 |
| 102 | 6-167 |     78 |
| 103 | 6-166 |     80 |
| 105 | 6-166 |     70 |
| 105 | 6-167 |     72 |
| 105 | 6-168 |     75 |
+-----+-------+--------+

• 直接使用运算符比较
  and表示并且的意思。

mysql> select * from score where degree > 60 and degree < 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 102 | 6-167 |     78 |
| 105 | 6-166 |     70 |
| 105 | 6-167 |     72 |
| 105 | 6-168 |     75 |
+-----+-------+--------+
4 rows in set (0.04 sec)

5.查询表中成绩是85,87或者88的记录

mysql> select * from score where degree in(85,87,88);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 6-168 |     85 |
| 102 | 6-166 |     85 |
| 104 | 6-166 |     85 |
| 104 | 6-168 |     87 |
+-----+-------+--------+

6.查询student5表中’‘95031’'班级或者性别为‘女’的同学记录

or表示或者的意思。

mysql> select * from student5 where class='95031' or ssex='女';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 102 | 王明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95034 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95039 |
+-----+--------+------+---------------------+-------+
3 rows in set (0.00 sec)

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

mysql> select * from student5 order by class desc;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95039 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95037 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95035 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95034 |
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 王明   | 男   | 1975-10-02 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)

• 升序（不加asc也会默认升序排列）

mysql> select * from student5 order by class asc;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 102 | 王明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95034 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95035 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95037 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95039 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)

以cno升序、degree降序查询score表的所有记录

mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 6-166 |     86 |
| 102 | 6-166 |     85 |
| 104 | 6-166 |     85 |
| 103 | 6-166 |     80 |
| 105 | 6-166 |     70 |
| 104 | 6-167 |     89 |
| 103 | 6-167 |     82 |
| 101 | 6-167 |     80 |
| 102 | 6-167 |     78 |
| 105 | 6-167 |     72 |
| 102 | 6-168 |     90 |
| 104 | 6-168 |     87 |
| 101 | 6-168 |     85 |
| 103 | 6-168 |     81 |
| 105 | 6-168 |     75 |
+-----+-------+--------+
15 rows in set (0.00 sec)

9.查询‘软件工程系’的教师人数

统计count

mysql> select count(*) from teacher where depart='软件工程系';
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.05 sec)

10.查询score表中的最高分的学生学号和课程号（子查询或者排序）

1.子查询方式

mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 102 | 6-168 |
+-----+-------+
1 row in set (0.04 sec)

解析语句
mysql> select sno,cno from score where degree=(select max(degree) from score);
这个语句分两步
1.找到最高分

mysql> select max(degree) from score;
+-------------+
| max(degree) |
+-------------+
|          90 |
+-------------+
1 row in set (0.00 sec)

2.找最高分的sno和cno

mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 102 | 6-168 |
+-----+-------+
1 row in set (0.00 sec)

3.排序方式

limit 0,1
第一个数字0是指开始的位置，第二个数字1是指查询几条。

mysql> select sno,cno,degree from score order by degree desc limit 0,1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 102 | 6-168 |     90 |
+-----+-------+--------+
1 row in set (0.00 sec)

mysql> select sno,cno,degree from score order by degree desc limit 0,3;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 102 | 6-168 |     90 |
| 104 | 6-167 |     89 |
| 104 | 6-168 |     87 |
+-----+-------+--------+
3 rows in set (0.00 sec)

11.查询某门课的平均成绩

avg()函数用于计算平均数

mysql> select avg(degree) from score where cno = '6-166';
+-------------+
| avg(degree) |
+-------------+
|     81.2000 |
+-------------+
1 row in set (0.04 sec)

12.用一个sql语句一次性查询所有课的平均成绩

group by cno：按照课程号cno进行分组。

mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 6-166 |     81.2000 |
| 6-167 |     80.2000 |
| 6-168 |     83.6000 |
+-------+-------------+
3 rows in set (0.00 sec)

13.查询score表中至少3名学生选修的并以6开头的课程

like模糊查询
having count(cno) 表示cno这个字段出现的次数
count( * )中的 * 表示group by cno中的所有字段，count(*)表示这些字段每个出现的次数。

mysql> select cno,avg(degree),count(*) from score group by cno having count(cno)>=3 and cno like '6%';
+-------+-------------+----------+
| cno   | avg(degree) | count(*) |
+-------+-------------+----------+
| 6-166 |     81.2000 |        5 |
| 6-167 |     80.2000 |        5 |
| 6-168 |     83.6000 |        5 |
+-------+-------------+----------+
3 rows in set (0.00 sec)