【第二阶段 day13 MySQL】统计案例聚合函数分组过滤事物表强化

最新推荐文章于 2022-09-21 11:00:12 发布

是九九鸭~

最新推荐文章于 2022-09-21 11:00:12 发布

阅读量180

点赞数

分类专栏：第二阶段文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/oriettahuiru/article/details/120524622

版权

第二阶段专栏收录该内容

12 篇文章 0 订阅

订阅专栏

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外键foreign key

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

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

6.2非空约束not null

6.3唯一约束unique

6.4主键约束primary key

6.5默认约束default

6.6检查约束check

DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除，慎用会丢失数据
 
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
 
sex CHAR(2) DEFAULT '男', #默认值
 
phone CHAR(18),
 
age INT,
 
CHECK (age>0 AND age<=200),
 
);
 
CREATE TABLE tb_user_address (
 
user_id INT PRIMARY KEY NOT NULL,
 
address VARCHAR(200),
 
foreign key(user_id) REFERENCES tb_user(id)
 
);
 
DESC tb_user;

);