MySQL--数据库约束与聚合查询和联合查询_mysql查询联合约束-优快云博客

本文链接：https://blog.youkuaiyun.com/lecok/article/details/148667384

1.数据库约束

1.数据库约束

通过对数据约束，目的是保证数据库避免被插入/修改非法数据，从而达到安全性。

1.1 约束类型

NOT NULL：指示某列不能存储NULL值。
UNIQUE：保证某列的每行必须有唯一的值。
DEFAULT：规定没有给列赋值时的默认值。
PRIMARYKEY：NOT NULL和UNIQUE的结合，确保某列（两个列或多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY：保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK：保证列中的值符合指定的体哦阿健。对于MySQL数据库，对CHECK子句进行分析，但忽略CHECK子句。

1.2 NOT NULL约束

1.3 UNIQUE约束

1.4 DEFAULT约束

1.5 PRIMARY KEY约束

primary key（主键）是最重要的约束，是标记每一条记录唯一的身份标识，是not null和unique的结合体，一般修饰数据类型为int的字段。一个表中只能有一个主键，但是可以修饰一列或多列，修饰多列称为联合主键，但主要修饰的还是单列更多一点。

如何保证主键的唯一性呢？

MySQL中提供了一种“自增主键”（primary key auto_increment）的机制。自增主键修饰的是整型类型的字段，程序员插入数据时，不必手动插入，服务器会自动从1开始分配主键，依次递增主键的值。

unique和primary key约束会让每一次插入数据时都会先触发一次查询操作，通过查询确认该值是由已经存在。这样的约束会导致数据库执行效率低，意味着数据库是比较吃资源比较慢的系统。MySQL中会对带有unique和primary key约束的列自动生成索引，从而加快查询速度。

1.6 FOREIGN KEY约束

foreign key（外键）描述两个表之间的联系关系。被foreign key修饰的表称为子表，被父表所约束。父表中与子表关联的列必须是被unique或primary key修饰的，父表被引用的列与子表被约束的列不一定要同名。

其中references意思是引用，子表中被约束的字段的值要在父表中的该字段中存在，子表中的数据是可以被删除的，父表中的被引用的数据不能够被修改或删除，该列中没有被子表的列所引用到的值是可以进行修改和删除的。

父表是不能够直接被删的，可以先删子表，再删父表。

1.7 CHECK约束（了解）

能在MySQL中使用，但是没实际效果，不执行该约束，相当于忽略该语句。

2.表的设计

2.1 一对一

比如一个学生只能有一个账号，一个账号只能被一个学生拥有。

通过关联关系将两个表关联起来。也可以将两个表合并成一个表：

2.2 一对多

一个学生对应一个班级，一个班级对应多个学生。

MySQL中没有数组类型，所以不能用第一种，只能使用第二种。

2.3 多对多

一个学生可以选择多门课程，一门课程可以包含多个学生。

3.新增

insert into 表名1 select * from 表名2;

将查询的结果当成新增数据插入到表中。查询的结果的字段的列数及其类型都要跟插入数据的目标表中的字段匹配。

创建两个字段相同的表，在其中一个表中插入几条记录。

在另一个表中以查询方式插入记录。

如果两个表的字段不一致，要选择查询相同的字段进行插入，否则插入失败。

4.查询

4.1 聚合查询

4.1.1 聚合函数

表达式查询是针对列和列之间的计算，聚合查询是针对行和行之间的计算。sql中提供了一些“聚合函数”，通过这些聚合函数完成行和行之间的计算。这些计算可以实现一些简单的统计操作。

COUNYT([DISTINCT]expr)	返回查询到的数据的数量
SUM([DISTINCT]expr)	返回查询到的数据的总和，不是数字没有意义。
AVG([DISTINCT]expr)	返回查询到的数据的平均值，不是数字没有意义。
MAX([DISTINCT]expr)	返回查询到的数据的最大值，不是数字没有意义。
MIN([DISTINCT]expr)	返回查询到的数据的最小值，不是数字没有意义。

select count(列名/*) from 表名；

获取该列的行数。

这条语句是先执行select *，再执行count的。count的结果是可以参与后续的算数操作和sql语句操作的。

如果count括号里的是列而不是*的话，会有一下问题：

如果该列里有空值（NULL），则不会将空值算入count。
指定具体列，是可以进行去重的，select count(distinct 列名) from 表名；

select sum(列名) from 表名;

获取该列的总和。

如果sum的列中有null，则会自动排除掉null，不会让null参与运算。

sum括号里不能是*，只能是列。

sum括号里的列不能是非数字类型的，否则有一下问题：

报了四个警告。

会自动尝试将该数据转换为double类型，如果转换成功就会当成正常数据进行运算，如果不能就报错。

sum括号里是可以写表达式的。

以下的函数用法和sum函数用法基本一致。

select avg(列名) from 表名;

获取该列的平均值。

select max(列名) from 表名;

获取该列最大值。

select min(列名) from 表名;

获取该列最小值。

4.1.2 GROUP BY子句

select 聚合函数(列名) from 表名 group by 列名;

使用group by进行分组。

如果不配合聚合函数进行分组的话，此时的结果是查询出的每一组的代表数据或者直接报错。

报错是因为数据库的配置问题，此处不多介绍。

4.1.3 HAVING

group by子句还可以跟条件语句进行搭配，但是需要区分条件是分组前的条件还是分组后的条件。

分组前的条件：where子句要写在group by之前：select 聚合函数(列名) from 表名 where 条件 group by 列名;

分组后的条件：需要使用having子句描述条件，having子句要写在group by后面：select 聚合函数(列名) from 表名 group by 列名 having 条件;

4.2 联合查询（多表查询）

联合查询又称多表查询。学习联合查询前先了解笛卡尔积，联合查询其实是以多个表进行笛卡尔积组成的一个新的表(核心思路是进行排列组合)。联合查询运行过程中会产生大量数据，因此在实际开发中应克制地使用联合查询。

4.2.1 内连接

创建以下四个表，有三个内连接的例子。

4.2.1.1 查询许仙的成绩

“许仙”需要用到student表中的name，而成绩需要用到score表中的score。所以需要将student和score这两个表进行联合查询。

1、先把两个表进行笛卡尔积

总共有160行。

2、加上连接条件，筛选有效数据

当描述多个表中的其中一个属性时，要用“表名.属性名”。

此处的另一种写法：select * from 表1 inner join 表2 on 条件语句;（inner可以省略）

3、加上查询条件，保留有用列

4.2.1.2 查询所有学生各自的总成绩

同样是将student和score进行联合查询。这里要运用聚合查询的sum（列名）和分组查询group by子句。

1、先把两个表进行笛卡尔积

2、加上连接条件，筛选有效数据

3、加上查询条件，保留有用列

4.2.1.3 查询学生各个课程的成绩

需要将student表和course表和score表进行联合查询。

1、先把三个表进行笛卡尔积

总共960行。

2、加上连接条件，筛选有效数据

3、加上查询条件，保留有用列

4.2.2 外连接

如果两个表中的记录都存在对应关系，则内连接和外连接的结果是一致的，如果存在不对应关系，则内连接和外连接的结果不一致。

外连接又分为左外连接和右外连接。

join/inner join表示内连接，lift join表示左外连接，right join表示右外连接。

左外连接是以左侧表为基准，保证左侧表数据都能出现在最终结果中，如果右侧表中没有对应数据，则显示null。右外连接同理。

4.2.3 自连接

一张表跟自身进行联合查询，应用场景是行和行之间进行比较

查询计算机原理分数比Java分数高的成绩信息，计算机原理分数和Java分数都是在同一张表中的，要进行同一张表的两行比较就需要对该表进行自连接。

alias：别名。此处报错是因为有同名表，只需要给两张表起个别名就行了。

加上条件，筛选有效数据。

此时这张表就可以进行每个学生的不同课程的分数比较了。

再加上查询条件（计算机原理课程（课程3）比Java课程（课程1）的分数高）就可以得到预期结果了。

4.2.4 子查询

将多个简单的sql拼接成一个复杂的sql语句，实际上就是进行套娃，将一个查询语句当作其查询的结果放在另一个查询语句当中。平时大家都是将复杂的代码拆分成简单的代码，因为复杂的代码不方便阅读和理解，此处的子查询是反其道而行之，违背了程序员一贯的原则，化简为繁，在开发中不建议使用子查询，此处了解一下即可。

单行查询：返回一行记录的子查询。

查询与“不想毕业”同学同班的同学