【第二阶段 day03】统计案例 聚合函数 分组 过滤

1.统计案例 1.1 统计2019年之前入职的员工信息

#统计2019年之前入职的员工信息
select * from em where hiredate<'2019-1-1';
select * from em where year(hiredate)<2019;

1.2 统计员工的年薪

#统计员工的薪资
select ename,job,sal,comm,sal*13+ifnull(comm,0)*13 from em; 

2.聚合函数

把一列的所有的值集合起来,然后做数据分析
常见的聚合函数:count(),max(),min(),sum(),avg()

2.1 MAX()最大值

2.1.1查询成员的最高工资

select max(sal) from em;

2.2 MIN()最小值

2.2.1查询成员的最低工资

select min(sal) from em;

2.3 avg()平均数

2.3.1查询成员工资的平均

selectT avg(sal) from em;

2.4 sum()求和

2.4.1统计成员工资的总和

select sum(sal) from em;

2.5 count()统计个数

2.5.1统计成员记录个数

select count(*) from em;

3.分组 group by

查询结果中,如果出现了聚合列与非聚合列,一定要分组
把数据按照维度分组后进行数据分析

• 什么时候要分组?
  查询时出现混合列
• 按照什么分组?
  按照非聚合列分组

3.1 按照岗位分组,统计每个岗位的平均薪资

SELECt job,AVG(sal) FROM em 
GROUP BY job;

3.2.按照部门分组,统计每个部门的人数

select deptno,count(1) from em 
group by deptno;

3.3统计每年入职的员工总数

select year(hiredate),count(*) from em 
group by YEAR(hiredate);

3.4.统计每个部门的最高薪

select deptno,max(sal) from em 
group by deptno;

4.过滤 having

4.1 .统计每个部门的最高薪,过滤,显示薪资大于10000的情况

select deptno,max(sal) from em 
group by deptno 
having max(sal)>10000;

4.2统计每年入职的员工总数,显示人数大于1的情况

select year(hiredate),count(*) from em 
group by YEAR(hiredate) 
having count(1)>1;

where里不能用别名且不能出现聚合函数,比having高效

5.事物Transaction

• 是指作为单个逻辑工作单元执行的一系列操作,要么完全执行,要么完全不执行
• 都执行成功才算成功,否则恢复到未执行这一系列操作之前
• 事物就是将一堆的SQL语句(增删改查)一起执行,都执行成功才算成功

5.1事物的四个特性(ACID)

• 原子性:把多个操作看作是一个原子,密不可分,要么全成功,要么全失败
• 一致性:保证数据在多个系统中是同步的
• 隔离性:数据库为了保证性能也支持高并发,但是有安全隐患,保证多个操作之间是隔离的
• 持久性:对数据库增删改的操作是持久生效影响的

5.2隔离级别

从上往下,性能越来越不好,安全性越来越好

• 读未提交:性能最好,安全性最差
• 读已提交:性能较好,安全性较高,是Oracle数据库默认的隔离级别
• 可重复度:性能一般,安全性较好,是mysql数据库默认的隔离级别
• 串行化:安全性最好,性能最差

5.3测试

mysql数据库会为每一条SQL提供事务管理,每条SQL执行之前MySQL会自动开启事物,SQL执行结束

BEGIN;
INSERT INTO test VALUES(NULL,'nana');
INSERT INTO test VALUES(NULL,'wanwan');
COMMIT;#结束事物.如果不添加这个,数据添加会有问题
SELECt * FROM test;

6.表强化 6.1外键

子表中主键的值必须取自主表

当子表中添加数据时,子表的主键值必须取自主表
当主表删除数据时,子表不能有相关的记录

CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,#主键约束
NAME VARCHAR(50) NOT NULL UNIQUE,#非空约束+唯一约束
sex CHAR(2) DEFAULT '男',#默认约束,设置默认值
age INT
);
CREATE TABLE tb_addrss(
user_id INT PRIMARY KEY NOT NULL,
address VARCHAR(30),
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);