29、分组查询
将查询结果按照1个或多个字段进行分组,字段相同的为一组
例如
SELECT * from student group BY stu_gender;
此时会得到上图,当group by单独使用时,只能显示出每组的第一条记录,因此group by 单独使用作用不大
一般会与group_concat联用。
例如:select department,group_concat(‘name’) from employee group by department;
在使用分组时,select后i满直接跟的字段一边也出现在group by之后
譬如使用 select name,gender from employee group by gender,name;
30、SQL分组聚合函数
1、分组求和/平均/最大/最小 直接在group_concat()后面加sum/average/max/min
例如:select department,group_concat(salary),sum(salary) from employee group by department;
2、查询各个分组的项目数,在后面加上stu_gender
SELECT stu_gender,count(*)from student group BY stu_gender;
练习:查询每个部门的部门名称以及每个部门工资大于1500的人数
Select department group_concat(name),count(*) from employee where salary > 1500 group by department;
31、group by+having
group by+having——用来分组查询后指定一些条件来输出结果
总的来说,having的作用和where类似,但having只能作用于group by
案例:select department, group_concat(salary),sum(salary) from employee group by department having sum(salary)>9000;
Having与where的区别
1、having是在分组后对数据进行过滤,而where是在分组前对数据进行过滤
2、Having后面可以使用统计函数而where后面不可以
3、Where是对分组前记录的条件,如果某行记录没有满足where子句设定的条件,那么这行记录不会参加分组;而having是对分组后数据的约束
练习:查询工资大于2000且部门工资总和大于6000的部门名称以及工资和
Select department group_concat(salary) where salary>2000 from employee having sum(salary)>=6000;
公式的书写顺序
Select ——from ——where——group by——having——order by——limit
公式的执行顺序
From——where——group by——having——select——order by——limit
32、Limit
Limit函数可以限制去调取记录的数目
语法结构limit (参数1,参数2)
参数1:从哪一行开始取数
参数2:一共要查几行
注意:角标编号从0开始
例如想要取前三行
应用:分页
33、实体完整性
表中的每一条记录都代表一个实体(entity),实体完整性的作用就是标识每一行数据不重复
约束类型:
主键约束(primary key):
特点:每个表中要有一个主键且数据唯一,且不能为null
添加方式:
1、在建表时直接设置主键
Create table 表名(字段1 数据类型 primary key,字段2 数据类型);
Create table 表名(字段1 数据类型,字段2 数据类型,primary key(要设置主键的字段));
Create table 表名(字段1 数据类型,字段2 数据类型,primary key(主键1,主键2...));
2、先创建表,再修改表设置主键
Alter table 表名t ADD constraint primary KEY(列名)
联合主键
在设置主键时,可以设置多个变量,此时必须要所有被设置为逐渐的字段同时相同时,才会违反联合主键约束。
唯一约束(unique):
特点:指定列的数据不能重复但是可以为空值
语法结构:create table 表名(字段名1,数据类型 字段2 数据类型 unique);
自动增长列(auto_increment):
特点:指定列的数据会自动增长(一般会将主键设置为自动增长列),即使数据被删除,还是会从删除的序号继续往下
格式:create table student(id int primary key auto_increment,name varchar(30));
35、域完整性
域代表着当前单元格,域完整性即使限制当前单元格的数据是否正确,不对照此列的其他单元格比较
域完整性约束
数据类型:数值,日期,字符串
非空约束(not null)
默认值约束(default)作用:在没有输入值时,自动填充为某一值
案例:create table stu(id int not null auto_increment,gender default “男”);
36、参照完整性
参照完整性指代的是表与表之间的关系
通常情况下可以通过设置两表之间的主键和外键关系,或者编写量表的触发器来实现
作用:在有对应参照完整性的两张表格,在对他们进行数据插入,更新删除的过程中,系统都会将被修改的表格与另一张对应表格进行对照,从而阻止一些不正确的数据的操作。
数据库的主键和外键类型必须一致;
两个表必须得要是InnoDB类型
设置参照完整性后,外键当中的内值,必须为主键中的内容
一个表设置当中的字段为主键,设置了主键的表成为主表,设置外键的表称为子表
操作:利用navicat新建一张表
选中索引选项卡——栏位:选中项目——索引类型:unique (注意应当一列一列的设置索引,否则只有当选中的列内容完全相同才会报错)
37、如何添加参照完整性?
方法1:Create table score(sid int,score int,constraint sc_st_fk foreign key(sid) references stu(id));
Alter table score add constraint sc_st_fk foreign key(sid) references stu(id);
方法2:选中表——对象信息——外键