数据库中的约束，聚合函数以及联合查询

目录

数据库中的约束

not null

unique

default

primary key

foreign key

表的设计

聚合函数（查询）

分组

联表查询（多表查询）

内连接

外连接

左外连接

右外连接

自连接

子查询

合并查询

---

数据库中的约束

为了保证我们存入数据库中的数据得靠谱，mysql就提供了一些机制，对数据进行检查。这个机制也就是约束。约束一旦被确定好后，后续在进行增删改的时候，就会判断要操作的是否符合约束从，符合就会执行操作，不符合则就报错。

我们需要在创建表的时候就对字段设置好约束。下面价绍一下各约束，check这个约束几乎不怎么使用，我这里就不介绍了。

not null

设置该列约束为非空，也就是该列的值必须有，不可以设置为null更改的话也不可以更为空

#使用的时候直接把not null 跟在字段后面，可以看下面例子：

create table student(id int, name varchar(20) not null);

设置完以后，我们查看表结构发现name字段的null列变成no，也就是不允许为空

这时候我们进行插入更新操作时，如果name的值为null，就报错了

unique

这个关键字用来保证数据的唯一性，也是加在字段名后面。不允许指定列的不同行有相同的内容。

create table student(id int unique, name varchar(20));

为什么我们添加了unique约束后，就知道有没有重复的呢？带有unique的字段，在执行更新插入操作的时候会先自己进行一次查询，看表中对应字段是否存在与要插入的内容一致，如果不存在就插入（更改），存在就无法插入（更改），直接报错。

default

这个关键字是设置字段的默认值的，插入的时候我们可以指定字段插入，对于某些字段我们并没有插入值，服务器会给这样的字段设置的默认值为null，我们如果不想用他设置的默认值，我们可以自己设置默认值。这个也是直接跟在字段后面。

create table student(id int, name varchar(20) default '管理员');

primary key

这个关键字表示主键约束，主键也就是一条记录的身份标识。一个表中只可以有一个主键。这个主键的功能等同于非空和unique的结合体（也就是主键的功能是非空和防止重复）。

create table student(id int primary key, name varchar(20));

一般我们会将主键设置为整型，并且将整型的主键设置为自增形式，用户可以不用再传入主键的值。（自增只针对主键为整型的时候）

#直接在primary key后面加上auto_increment就行
create table student(id int primary key auto_increment, name varchar(20));

1.当主键设置为了自增的时候，对于这个字段，我们如果传入Null或者不传入值的时候，数据库会按照自增直接给我们分配一个数据。

2.我们也可以自己传入值，数据库会记录主键字段的已有内容的最大值，再次自动分配的时候从最大值后面开始。

foreign key

这个关键字是涉及外键的，涉及到两个表之间的约束，父表约束子表，子表被父表约束。

#先创建父类
create table classes(classId int primary key auto_increment, name varchar(20));

#再创建子类
 create table student(id int primary key, name varchar(20), classId int, foreign key(classId) references classes(classId));

1.插入或删除子表中受约束的这一列中的数据时，要保证插入或删除的元素在父表中能查到。（这种有外键约束的也会触发查询操作，会去在父表中查询一下看存不存在这个元素）

2.删除/修改父表中的元素时，要去看在子表中有没有被使用，如果被使用了也不可以进行删除，修改。（也会触发查询操作，会去查父表要删除的元素在子表中有没有被使用，如果被使用，就不可以执行删除或者更改操作）。

3.primary key 和unique这两个自带索引，因为执行操作的时候可能会进场查询父表或者子表，很费时，所以，有索引就会大大提升速度。因此也就规定没有索引，就不可以建立这样的外键。

表的设计

数据库中的关系分为四种：一对一，一对多，多对多和没关系。

一对一：例如一个人只能有一个身份证号，一个身份证号也只能对应一个人。

一对多：一个人只可以在一个班级中，一个班级中却可以包括很多人。

多对多：一个人可以选修不同的课程，而一门课程可以被很多人选用。

聚合函数（查询）

sql中涉及到的聚合函数如下：（这个聚合函数是针对行和行之间的计算）

分组

通过group by来进行分组，通过指定列进行分组，值相同的放到一组。

1.select后面的字段要么在group by后面，要不就在聚合函数里面，否则无任何意义。

2.group by也可以结合条件进行查询操作。

3.group by可以结合条件的关键字不仅有where还有having。where条件用在分组前，在分组前，对结果集进行过滤，然后再进行分组。having用于分组完成后，对分组后的结果集进行过滤。

select name,avg(salary) from emp where name != '张三' group by name having avg(salary) > 5000;

联表查询（多表查询）

联表操作，我们必须要清楚笛卡尔积！笛卡尔积：将两个表的记录通过排列组合的方式结合起来构成一个更大的表。笛卡尔积后的列数就是原来两个表的列数之和，笛卡尔积后的行数就是原来两个表的行数之积。

内连接

我们通过笛卡尔积后可以得到一个很大的组合表，这里是两个表的所有组合，我们再根据两个表的连接关系，对表进行筛选。

#此时就是两个表进行了笛卡尔积
slect * from stu1,stu2;

#我们要筛选出我们想要的内容，就得增加条件
#注意！！！在条件中会用到字段名，我们需要通过表名.字段名来进行调用
select * from stu1,stu2 where stu1.name = stu2.name and 其他条件;



#上面是我们内连接的一种方式，我们还可以通过下面这种方式进行内连接。
select * from stu1 (inner) join stu2 on 条件; 

外连接

如果我们有两个表A,B，当B表中每一条记录都能在A表中找到对应的，A表中每一个记录也都能在B表中找到对应的，此时内外连接结果一样。否则如果不能一一对应，则内外连接就有区别了，外连接分为左连接和右连接。

左外连接

select 字段名  from 表名1 left join 表名2 on 连接条件;

左外连接：以左侧表为主，左侧表的每个记录都会在最后的临时结果集中，如果遇到了左表存在右表不存在的数据，就会把对应的列值置为空。

右外连接

select 字段 from 表名 1 right join 表名 2 on 连接条件 ;

右外连接：以右侧表为主，右侧表的每个记录都会存在在最后的结果集里面，如果遇到了右侧表中存在，左侧表中不存在的，会把对应列置为null。

自连接

本质上就是自己和自己进行笛卡尔积，把行之间的关系转为列之间的关系。

我们查询的时候多个表的名字不可以相同，所以我们需要起别名。

select * from score as s1, score as s2 where 条件;

子查询

指嵌入到其他sql语句中的select语句，也可以称为嵌套查询。说白了就是将多条sql语句合成一个sql语句

#例：
select name from student where id = 1;#假设输出的为'张三'
select math,chinese from score where name = '张三';


#下面这个就是嵌套查询(将上面两个合起来了)

select math,chinese from score where name = (select name from student where id = 1);

合并查询

把多个select后的结果集合并成一个结果集。使用关键字union(会自动去掉结果集中重复行)或者union all（这个不会将结果集中重复行去掉）使用这个合并的前提是：每个 SELECT 语句必须具有相同数量的列，且对应列的数据类型必须相似

select ... from ... where 条件
union(all)
select ... from ... where 条件

SQL 查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >

select > distinct > order by > limit