[大数据]two-03 聚合函数 分组 事务

1.聚合函数

#max(),求最大值
SELECT MAX(sal) FROM emp; #查询emp表内最高工资
#min(),求最小值
SELECT MIN(sal) FROM emp;
#avg(),求平均值
SELECT AVG(sal) FROM emp;
#count(),求记录总和
SELECT COUNT(sal) FROM emp;
SELECT COUNT(*) FROM emp #低效
SELECT COUNT(1) FROM emp #高效

2.分组

把数据按照一些维度分成组,然后再把这一组数据继续分析

#1,分组:group by,把数据按照维度分组,后,数据分析
  #口诀:
  #什么时候要分组?查询时出现了混合列
  #按照什么分组?按照非聚合列分组
  #练习1:统计每个岗位的平均薪资
SELECT AVG(sal) FROM emp GROUP BY job;
#练习2:统计每个部门的员工人数
SELECT deptno,COUNT(1) FROM emp GROUP BY deptno
#练习3:统计每年入职的人数
SELECT YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate); 
#练习4:统计每个部门的最高薪
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;

#2.分组后过滤having
#group by后的过滤必须用having
#where里不能用别名,不能出现聚合函数,比having高效
#练习1:统计每个部门的最高薪,只要>10000的记录
SELECT deptno,MAX(sal) as max FROM emp #用as起别名，as可省略 
GROUP BY deptno 
HAVING MAX(sal)>10000;
#练习2:统计每年入职的人数,只要人数>1的记录
SELECT COUNT(1)b,YEAR(hiredate)a FROM emp 
GROUP BY a 
HAVING b>1

3.事务

3.1 概述

事务（Transaction），保证多条操作要么全部成功，要么全部失败

3.2 事务的四大特点

ACID

1. 原子性：把多条SQL看作是一个原子，要么全都执行成功，要么全都执行失败。
2. 隔离性：保证多个操作之间是隔离的
3. 持久性：多数据库的操作是持久生效的
4. 一致性：保证数据在多个系统中是一致的

3.3 隔离级别

1. 读未提交：性能最好，安全性最差
2. 读已提交：性能较差，会出现不可重复读的现象，安全性较高，Oracle数据库默认隔离级别
3. 可重复读：性能较差，不会出现不可重复读的现象，安全性较高，Mysql数据库默认隔离级别
4. 串行化：性能太差
   测试手动管理事务

#测试 手动管理事务CUD:模拟向test表中插入数据
START TRANSACTION; #开启事务
INSERT INTO test VALUES(NULL,'2');
INSERT INTO test VALUES(NULL,'3');
COMMIT; #结束事务

4. 字段约束

#字段约束:非空约束/唯一约束/主键约束/默认约束..
#1.默认约束:给字段使用default添加默认值
create table a(
 id int primary key auto_increment,
 sex varchar(10) default '男' # 默认约束，设置默认值
)
#2.检查约束:给字段使用check添加合法性的检查
create table b(
 id int primary key auto_increment,
 age int,
 check(age<100) #检查约束,age无法记录不合法数据
)
#3.外键约束:防止了冗余的数据,通过外键来描述两张表的关系
#特点是:当子表中添加数据时,子表的主键值 必须 取自主表!
       #当主表删除数据时,得保证子表没有相关的记录、
create table jd_user(
 id int primary key auto_increment,
 name varchar(20),
 password varchar(50)
)
create table jd_user_add(
 add_id int primary key,
 address varchar(50),
 # 创建外键语法：foreign key(本表的主键名) references 对方表名(对方表的主键)
 foreign key(add_id) references jd_add(id) #创建外键 
)