mysql分组非聚合函数 mysql分组聚合查询

一 聚合函数

- 1概念

把一列的值全部取出来，聚合起来，分析最大值，最小值，平均值，求和，求个数
常见的聚合函数：max min avg sum count

- 2测试

#聚合函数:把查出来的列聚合起来分析数据
#求最高薪max 
SELECT sal FROM emp ORDER BY sal DESC LIMIT 1
SELECT MAX(sal) FROM emp #获取sal这列里的最大值
#求最低薪min 
SELECT sal FROM emp ORDER BY sal LIMIT 1
SELECT MIN(sal) MIN FROM emp
#求和sum 
SELECT SUM(sal) FROM emp
#求平均数avg
SELECT AVG(sal) FROM emp
SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) 
FROM emp
#求总个数count
SELECT COUNT(comm) FROM emp#不统计comm字段值是null的-低效
SELECT COUNT(*) FROM emp#都统计
SELECT COUNT(1) FROM emp#都统计-高效
#统计工资>8000的人数
SELECT COUNT(1) FROM emp 
WHERE sal>8000
#统计2019年入职的人数
SELECT COUNT(1) FROM emp
WHERE YEAR(hiredate)=2019

二分组（group by）

#什么时候必须分组?当查询结果中出现了聚合列和非聚合列时
#按照什么分组合理?通常按照非聚合列分组
#什么是聚合列非聚合列?使用了聚合函数max min sum avg count的就是聚合列
#统计每个部门的平均薪资
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno #按照部门编号分组
#统计每个岗位的最高薪资
SELECT MAX(sal),job FROM emp
GROUP BY job#按照岗位分组
#统计每年的入职人数
#count是聚合函数,year只是普通函数
SELECT COUNT(1),YEAR(hiredate) FROM emp
GROUP BY YEAR(hiredate)#按照非聚合列分组

#having的作用:用来对分组后的数据,进一步过滤
#统计每个部门的平均薪资,只要>10000的记录
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal)>10000
#统计每个岗位的最高薪资,只要>8000的记录
SELECT job,MAX(sal) FROM emp
#先过滤再分组高效,但是where里不能用聚合函数
#where MAX(sal)>8000  #会报错
GROUP BY job
HAVING MAX(sal)>8000
ORDER BY MAX(sal) #升序排序

#统计每年的入职人数,只要人数>1的记录
SELECT COUNT(1),YEAR(hiredate) FROM emp
GROUP BY YEAR(hiredate)
HAVING COUNT(1) > 1 
#不能改成where,后面出现了聚合函数

#统计每年的入职人数,只要2017年以后的记录
SELECT COUNT(1),YEAR(hiredate) FROM emp
WHERE YEAR(hiredate) > 2017 #高效,只是where里不能出现聚合函数
GROUP BY YEAR(hiredate)
#having YEAR(hiredate)> 2017 
#having里使用的过滤条件必须是查到的结果

三 事务

-1概念

保证SQL与具有，要么全执行，要么全失败

有四个特征ACID

1. A是原子性：值多条sql是一个院子，密不可分，如果都正确，就操作，如果有错误就会发生回滚，回到书屋执行之前。
2. C是一致性：保证了数据的一致性和完整性。
3. I是隔离性：保证多线程并发是的数据安全，多个操作之间是被隔离的
4. D是持久性：是指对数据CPU影响是持久生效的

隔离级别：读未提交 读已提交 可重复读 串行化

Mysql默认是可重复读，从前往后安全性越来越差

-2 测试

mysql> start transaction; #开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept values(18,'java','shanghai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept values(19,'java2','shanghai2');
Query OK, 1 row affected (0.00 sec)
mysql> commit; #提交事务
Query OK, 0 rows affected (0.03 sec)

四、字段约束

–1,默认约束和检查约束

#默认约束:给指定字段设置默认值
CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
sex VARCHAR(10) DEFAULT '男' #设置默认值
)

#检查约束:给指定字段设置合法值的检查规则
CREATE TABLE g(
id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
age INT,
CHECK(age>0 AND age<200)#设置检查
)

–2,外键约束

#默认约束:给指定字段设置默认值
CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
sex VARCHAR(10) DEFAULT '男' #设置默认值
)

#检查约束:给指定字段设置合法值的检查规则
CREATE TABLE g(
id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
age INT,
CHECK(age>0 AND age<200)#设置检查
)

#外键:通过指定的SQL语句描述了两张表的关系
#约束:子表添加记录时id必须在取自主表
     #删除主表记录时,必须没有被子表使用着
#怎么确定谁是子表谁是主表?子表中有外键的SQL
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
sex VARCHAR(10)
)
CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(20),
#表明和哪张表的哪个字段有关系
#foreign key(本表的主键) references 关联表(主键)
FOREIGN KEY(user_id) REFERENCES tb_user(id)
)