MySQL进阶复习 — 字段约束、表的设计、聚合函数

1.数据库的约束

数据库的约束针对的是表中的字段，那么数据库约束具体有哪些呢？

• NOT NULL：表示某列不能存储NULL值；
• UNIQUE：保证某列的值是唯一的；
• DEFAULT：规定没有给列赋值时的默认值；
• PRIMARY KEY：NOT NULL和UNIQUE的结合，确保某列或多列有唯一的标识，有助于更容易快速找到表中一个特定的记录，通常和AUTO_INCREMENT搭配使用，称为自增主键；
• FOREIGN KEY：使一个表关联另外一个表，保证一个表中的数据匹配另一个表中的值的参照完整性；
• CHECK：保证列中的值符合指定的条件，对于MySQL数据库，对CHECK句子进行分析，但是忽略了CHECK字句。

具体我将创建一张表，去演示不同的约束发挥的作用：

– 创建一张student表，共包含四个字段
create table student(
student_id int(10) primary key auto_increment,
student_name varchar(10) not null,
email varchar(20) unique,
hobit varchar(20) default “学习”
);

primary key auto_increment 主键自增长：

insert into student(student_name,email,hobit) values(“小唐”,“123456789@163.com”,“学习Java”);

可以看出，当我们指定列插入数据不指定student_id列时，数据插入成功，student_id默认从1开始；如果删除了这条数据，重新插入一条，student_id将从2开始，也就是说使用自增主键后，数值在上一次插入的数据基础上加1；当我们使用drop命令删除表后，student_id才从1开始。

not null约束：

insert into student(student_name,email,hobit) values(null,“111111111@163.com”,“打篮球”);

我们可以看出，当插入数据中student_name指定为null时，插入数据失败，原因：该列有非空not null约束。

unique唯一约束：
insert into student(student_name,email,hobit) values(“小特”,“123456789@163.com”,“踢足球”);

可以发现，当插入数据中email列出现重复值时，插入数据失败，原因：该列具有唯一性unique约束。

default默认值约束：
insert into student(student_name,email) values(“小康”,“ktrina@163.com”);

从数据库语句执行结果可以看出，不指定hobit列时，数据插入成功，默认值为“学习”。

foreign key外键约束：

外键约束一般发生在两张表之间，外键约束用于关联另外一张表的主键或唯一键，语法：foreign key (列名) references 主表名(主键约束的列名)
给一张student表和class表：

那么先创哪一张表呢？子表or主表？

先创子表student时我们发现是创建不了的，出现的错误提示不能添加外键约束，那么一定是先创建主表再创建子表？没错就是这样的操作顺序

铁子们，真的是这样非常nice哦！
那我们再想一下删除表的时候应该先删除子表还是主表呢？接着来看

先删除主表出现了错误，因此我们先要删除子表，再去删除主表：

按照先删除子表再删除主表的顺序，我们就将两张表成功删除！
再一次敲重点：
创建表：先主表再子表   删除表：先子表再主表

当我们插入数据的时候，只有class中出现的class_id，我们才能在student关联插入数据：

这是因为class表中没有4这个calss_id，因此插入失败。
删除数据的时候，当class的class_id在student出现时，先删除student中的数据，再删除class中数据；当class中的class_id没有在student出现时也就说没有被关联，可以直接删除class中的数据。

check约束：

对某个字段约束，插入数据时该字段只能选择规定的值。
create table userr(
id int,
name varchar(20),
sex varchar(1),
check (sex = “男” or sex = “女”)
);
这里的check约束限定了插入数据sex只能为男或者女，但是在MySQL中输入其他也是不报错的，Oracle中输入其他会报错。

2.表的设计

数据库表之间的关系共有三种形式：一对一、一对多、多对多

一对一：人和身份证之间的关系

一对多：班级和学生之间的关系

多对多：学生和课程之间的关系，一个学生可以选择多门课，一个课可以被多个学生选择。
不管在哪一张表中写另外一张表的id字段都是不合理的，因此创建中间表表示学生与课程之间多对多的关系

通过中间表的学生id和课程id就能确定课程的名称和学生的姓名。

3.聚合函数

常见的聚合函数：

聚合函数	函数说明
count(expr)	返回查询到数据的数量
sum(expr)	返回查询到数据的总和，不是数字则无意义
avg(expr)	返回查询到数据的平均值，不是数字无意义
max(expr)	返回查询到数据的最大值，不是数字则无意义
min(expr)	返回查询到数据的最小值，不是数字则无意义

首先我们先创建一张表并插入一些数据：

聚合函数使用示例：

• count

--查询得到的是插入数据的数量
select count(*) from exam_test;  

查询结果：
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> insert into exam_result values(2,"唐僧",66.1,88.2,99.3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
|    6 | 孙权      |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
|    2 | 唐僧      |    66.1 | 88.2 |    99.3 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)

-- 查询id去重后数据的数量
mysql> select count(distinct id) from exam_result; 
+--------------------+
| count(distinct id) |
+--------------------+
|                  7 |
+--------------------+
1 row in set (0.02 sec)

• sum

-- 查询所有人的英语总分
mysql> select sum(english) from exam_result;
+--------------+
| sum(english) |
+--------------+
|        542.8 |
+--------------+
1 row in set (0.00 sec)

-- 查询英语成绩小于50的人的英语总分
mysql> select sum(english) from exam_result where english < 50;
+--------------+
| sum(english) |
+--------------+
|         75.0 |
+--------------+
1 row in set (0.00 sec)

• avg

-- 查询所有人数学平均分
mysql> select avg(math) from exam_result;
+-----------+
| avg(math) |
+-----------+
|  83.71250 |
+-----------+
1 row in set (0.01 sec)

• max

-- 查询语文最高成绩
mysql> select max(chinese) from exam_result;
+--------------+
| max(chinese) |
+--------------+
|         88.0 |
+--------------+
1 row in set (0.01 sec)

• min

-- 查询语文成绩最低分
mysql> select min(chinese) from exam_result;
+--------------+
| min(chinese) |
+--------------+
|         55.5 |
+--------------+
1 row in set (0.01 sec)

-- 查询语文成绩大于75分的人中语文最低分
mysql> select min(chinese) from exam_result where chinese > 75;
+--------------+
| min(chinese) |
+--------------+
|         82.0 |
+--------------+
1 row in set (0.00 sec)

特别注意：where语句后不能跟聚合函数

-- where后面使用聚合函数导致发生了错误
mysql> select id,name,math from exam_result where max(english) > 80;
ERROR 1111 (HY000): Invalid use of group function

---

group by分组查询

首先创建一张工资表，然后插入一些数据：

查询每个角色的最高工资、最低工资和平均工资：

按照下面这条sql执行查到的是所有人中的最低最高和平均工资，这显然不是我们想要的查询结果。

mysql> select max(salary),min(salary),avg(salary) from emp;
+-------------+-------------+-------------+
| max(salary) | min(salary) | avg(salary) |
+-------------+-------------+-------------+
|    12000.66 |      333.50 | 2839.131667 |
+-------------+-------------+-------------+
1 row in set (0.00 sec)

这才是按照角色分组后，每个角色的最低最高和平均工资；执行顺序是：先进行group by分组，再进行select查询。

mysql> select role,max(salary),min(salary),avg(salary) from emp group by role;
+--------------+-------------+-------------+--------------+
| role         | max(salary) | min(salary) | avg(salary)  |
+--------------+-------------+-------------+--------------+
| 服务员       |     1000.20 |     1000.20 |  1000.200000 |
| 游戏角色     |      999.11 |      333.50 |   677.646667 |
| 游戏陪玩     |     2000.99 |     2000.99 |  2000.990000 |
| 董事长       |    12000.66 |    12000.66 | 12000.660000 |
+--------------+-------------+-------------+--------------+
4 rows in set (0.00 sec)

---

having过滤

利用group by子句进行分组后，需要对分组结果再进行条件过滤时，不能再去使用where语句，而需要使用having，和where目的一样都是为了过滤条件。

-- 显示平均工资低于1500的角色和该角色的平均工资
mysql> select role,avg(salary) from emp group by role having avg(salary) < 1500;
+--------------+-------------+
| role         | avg(salary) |
+--------------+-------------+
| 服务员       | 1000.200000 |
| 游戏角色     |  677.646667 |
+--------------+-------------+
2 rows in set (0.00 sec)