目录
数据库中的约束
为了保证我们存入数据库中的数据得靠谱,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 连接条件;
左外连接:以左侧表为主,左侧表的每个记录都会在最后的临时结果集中,如果遇到了左表存在右表不存在的数据,就会把对应的列值置为空。
右外连接
右外连接:以右侧表为主,右侧表的每个记录都会存在在最后的结果集里面,如果遇到了右侧表中存在,左侧表中不存在的,会把对应列置为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 条件