数据库约束的类型
NOT NULL型
某一列不能存储null值,在创建表的时候,将不能为空的列加上这个约束
create table 表名(属性 类型 not null,…)
本示例就以学生的id不能为空演示
下面插入数据:
UNIQUE 型
保证某列的每行必须有唯一的值,也是在创建表的时候进行约束
测试用例:
DEFAULT:默认值约束
下面设置默认值:
如果我们在创建表的过程中,如果name输入为空,那么此时表中会把其设置为默认值匿名
PRIMARY KEY:主键约束
是一个很重要的约束条件,对于一个表来说,只能指定一列来作为主键约束,并且我们可以发现主键约束的这一列不允许重复,也不允许为空,结合了unique和not null约束
下面我们在深入的了解一下,自增主键,这个一般适用于序号的增加
测试用例:
注意自增主键也是可以自己手动设置的,如果你这次特别指定了这次id从10开始,那么后面插入的数据将会从11开始插起,10之前没用的就用不了了。
FOREIGN KEY:外键约束
外键约束针对的是两张表而言的,将两张表进行关联,这里我们以学生表和班级表为例,学生表中有classsort对应着班级表的id,通过id就可以把学生表和班级表相关联起来了
学生表是依赖于班级表的,所以我们先创建一个班级表,学生表就称为子表,班级表被称为父表,我们可以利用外键约束,将学生表中的classid与班级表中的id相关联即可
本例中的外键约束sql语句如下:
create table student(id int primary key auto_increment,name varchar(20),classid int ,foreign key (classid) references class(id));
注意:
1 外键约束是两张表之间的关联,所以子表中的记录(classid)要在父表中存在(id)
2 对子表插入(修改),其范围都必须在父表之内(本例中,班级表中的id总共有3个,那么在给子表classid输入值的时候,只能在1到3之间)
3 对于父表,如果子表引用了其中的属性(id),那就不可以删除和更改,除非子表是没有对那个属性引用(如果父表中现在有个id为4的,子表没有引用这个id),那么才可以操作!
外键约束的工作原理
在对子表插入新的数据的时候,会根据对应的值前往父表查询,查询到了之后才可以进行插入操作,这里的查询就是依据索引来完成的(外键约束就要求父表被引用的这一列必须有索引,索引会根据primary key或者unique自动创建索引)
新增操作
这里的新增操作不是之前所学的一列一列插入,而是整表的进行插入,但是有一点必须注意,类型必须对应上
insert into 表1 select 表2中选取对应表1的字段 from 表2;
先创建如下的三个表:
开始测试:
第三个表多了一个age,那么我们在插入的时候就和student1的类型匹配上就可以了
另外对于新增操作也可以搭配我们之前学过的分页去重,条件处理
聚合查询
聚合函数
关于聚合查询是不同于我们之前学过的增删查改正对于列而言,这里是针对于行与行的运算
count(字段) 返回查询的数据总量
sum(字段) 返回查询的数据的总和
avg(字段) 返回值是数据的平均值
max(字段) 返回查询数据的最大值
min(字段) 返回查询数据的最小值
这里除了第一个函数意外,其他四个返回值如果不是数字是没有意义的
select 聚合函数 from 表名;
以count为例:
表示chinese数据有7行
sum函数:
这里的聚合函数搭配了条件语句,计算总分但是是语文成绩大于70分的人的总分
聚合函数对每行的使用,结果计算与null无关,就是假如有第八行数据,第八行的三列成绩设置为null,对其使用聚合函数计算,其计算结果null是不会参与的,依旧还是按照7个人算平均值等
聚合函数也是可以设置称别名的
group by子句
group by子句是根据行的值相等就分为一组,我们可以通过一组示例代码来理解一下什么是group by 子句
测试用例:
create table display( id int primary key auto_increment, name varchar(20) not null, role varchar(20) not null, salary numeric(11,2) ); insert into display(name, role, salary) values ('张三','服务员', 1000.20), ('李四','游戏陪玩', 2000.99), ('孙悟空','游戏角色', 999.11), ('猪八戒','游戏角色', 333.5), ('小乔','游戏角色', 700.33), ('王五','董事长', 12000.66);
通过角色名来分组: select * from display group by role;
我们可以发现role这一列有着相同的行,分组之后在第二个表只显示了第一个,这就是根据role来分组,相同的值被分到了同一组,显示了第一个
结合聚合函数对group by进行使用
从这里可以更好的看出组中的数据,比如游戏角色组中,有三个人,最高的确实是999.11,最低的是333.50
having使用
having是使用在分组之后进行筛选条件,接着引用上面的例子进行使用
执行语句:select role ,avg(salary) as avg from display group by role having avg>1000;
与where区别
where也是可以筛选条件的,但是where适用的是分组之前,如果对行与行数据分组,那么分组之后必须使用having!
MySQL数据库的约束+进阶版新增与查询-2