3.MySQL表的设计

my## 1.表的设计

三大范式

1 .1一对一

1.2 一对多

1.3 多对多

建立表描述

描述每个同学的每个科目的考试成绩
①创建一个表写每个同学的名字

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | 甲   |
|  2 | 乙   |
|  3 | 丙   |
|  4 | 丁   |
+----+------+
4 rows in set (0.04 sec)

②创建一个表描述每个科目的成绩

mysql> select * from course;
+----+--------+
| id | name   |
+----+--------+
|  1 | 语文   |
|  2 | 数学   |
|  3 | 英语   |
|  4 | 物理   |
+----+--------+
4 rows in set (0.00 sec)

③创建中间表描述,1对应语文4对应丁
成绩表包含学生表中学生姓名和课程表中课程名称,组成某学生某学科的成绩

mysql> create table score(courseId int,studentId int,score decimal(3,1));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into score values(1,4,88);
Query OK, 1 row affected (0.04 sec)

中间表courseid 和studentid字段设置成外键更严谨

mysql> create table score(courseId int,foreign key(courseId)references course(id),
    -> studentId int,foreign key(studentId)references student(id),score decimal(3,1));-- 加外键
Query OK, 0 rows affected (0.05 sec)
mysql>  insert into score values(1,4,88);
Query OK, 1 row affected (0.04 sec)

表示甲在语文科目上是90
由于是多对多的关系,courseid 和studentid存在重复情况

2新增 子查询

(将表1的内容直接插入到表2中)

mysql> select * from user;
+----+--------+--------------+
| id | name   | decription   |
+----+--------+--------------+
|  1 | 张三   | 语文老师     |
|  2 | 李四   | 数学老师     |
|  3 | 张花   | 英语老师     |
+----+--------+--------------+
3 rows in set (0.00 sec)

mysql> select * from user2;
Empty set (0.00 sec)

mysql> insert into user2 select name,decription from user;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user2;
+--------+--------------+
| name   | decription   |
+--------+--------------+
| 张三   | 语文老师     |
| 李四   | 数学老师     |
| 张花   | 英语老师     |
+--------+--------------+
3 rows in set (0.00 sec)

将从user中查询到的数据插入到user2中(从user中查询name,decription两列然后插入user2中)

子查询得到的列的数目,顺序,类型都得和插入的表的列的数目,顺序,类型一致,列的名字一致不一致,无所谓

3查询

3.1聚合查询

(1)聚合函数
①count:计算结果的行数

mysql> select * from user;
+----+--------+--------------+
| id | name   | decription   |
+----+--------+--------------+
|  1 | 张三   | 语文老师     |
|  2 | 李四   | 数学老师     |
|  3 | 张花   | 英语老师     |
+----+--------+--------------+
3 rows in set (0.04 sec)

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.04 sec)

mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
|           3 |
+-------------+
1 row in set (0.03 sec)

!!!count不计算NULL的值
!!!注意count和列之间的不能有空格,也就是和()之间不能有空格,count本来是一个函数,如果和(name)之间存在空格,此时就相当于把count当成一个列名
②sum返回查询数据的总和,只能用于数字
③avg返回查询数据的平均值,只能用于数字
④max返回查询数据的最大值,只能用于数字
⑤min返回查询数据的最小值,只能用于数字

mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 张三   |  90.0 |
|    2 | 李四   |  88.0 |
|    3 | 张花   |  96.0 |
+------+--------+-------+
3 rows in set (0.00 sec)

mysql> select sum(score) from student;
+------------+
| sum(score) |
+------------+
|      274.0 |
+------------+
1 row in set (0.04 sec)

mysql> select avg(score) from student;
+------------+
| avg(score) |
+------------+
|   91.33333 |
+------------+
1 row in set (0.03 sec)

mysql> select max(score) from student;
+------------+
| max(score) |
+------------+
|       96.0 |
+------------+
1 row in set (0.04 sec)

mysql> select min(score) from student;
+------------+
| min(score) |
+------------+
|       88.0 |
+------------+
1 row in set (0.00 sec)

mysql> select avg(score) from student where score < 90; -- 求所有分数小于90同学的平均成绩,即可以加限定条件
+------------+
| avg(score) |
+------------+
|   88.00000 |
+------------+
1 row in set (0.04 sec)

mysql>

(2)group by 子句
把得到的查询结果按照一定的规则分组(可能分成多个组),再根据分组进行查询

查询员工表中每个岗位对应的最高工资,最低工资,平均工资

mysql> select * from emp; -- 员工表
+------+--------+-----------+--------+
| id   | name   | role      | salary |
+------+--------+-----------+--------+
|    1 | 张三   | 老师      |    100 |
|    2 | 张一   | 老师      |    200 |
|    3 | 张二   | 老师      |    300 |
|    4 | 张四   | 班主任    |    300 |
|    5 | 张五   | 班主任    |    250 |
|    6 | 张六   | 班主任    |    690 |
|    7 | 张七   | 市场      |    500 |
|    8 | 张八   | 市场      |    520 |
|    9 | 张九   | 市场      |    450 |
+------+--------+-----------+--------+
9 rows in set (0.00 sec)

mysql> select role from emp group by role;
+-----------+
| role      |
+-----------+
| 市场      |
| 班主任    |
| 老师      |
+-----------+
3 rows in set (0.00 sec)

mysql> select role ,avg(salary) from emp group by role;
+-----------+-------------+
| role      | avg(salary) |
+-----------+-------------+
| 市场      |    490.0000 |
| 班主任    |    413.3333 |
| 老师      |    200.0000 |
+-----------+-------------+
3 rows in set (0.04 sec)

mysql> select role ,avg(salary),max(salary),min(salary) from emp group by role;
+-----------+-------------+-------------+-------------+
| role      | avg(salary) | max(salary) | min(salary) |
+-----------+-------------+-------------+-------------+
| 市场      |    490.0000 |         520 |         450 |
| 班主任    |    413.3333 |         690 |         250 |
| 老师      |    200.0000 |         300 |         100 |
+-----------+-------------+-------------+-------------+
3 rows in set (0.00 sec)

(3)having子句
(自我理解,having就是定位的意思)
查找出所有平均工资高于250的岗位和平均薪资

mysql> select role,avg(salary) from emp group by role having avg(salary) > 250;
+-----------+-------------+
| role      | avg(salary) |
+-----------+-------------+
| 市场      |    490.0000 |
| 班主任    |