练习Day03

一，统计练习

–1，代码

#统计 名字里包含a的员工人数
select * from emp 
select count(1) from emp where ename like '%a%'
#统计 普通员工 的平均工资
select avg(sal) from emp where job='员工'
#统计 2019年入职的员工的总人数
select count(1) from emp where year(hiredate)=2019
#统计 2号部门每年的工资开销
select sum(sal+ ifnull(comm,0) )*12 年开销 from emp where deptno=2

二，分组

–1，概述

使用group by实现分组，使用having在分组后的结果上继续添加过滤条件

–2，测试

#分组  group by 字段名 -- 按照指定字段分组
#如果查询时,出现了聚合列和非聚合列,通常要按照非聚合列分组

#查询 每个部门里 的最高薪和人名
select deptno,ename,max(sal) from emp 
group by deptno

#查询 每种岗位 的平均工资和岗位名称
select avg(sal),job from emp group by job #按非聚合列分组

#查询 每个部门 的平均工资
select avg(sal) from emp group by deptno #分析需求，要按照部门分组
#统计 部门 出现的次数
select count(1) from emp GROUP BY deptno

#having 在分组后的结果中,继续添加过滤条件

#查询 每种岗位 的平均工资和岗位名称 --进一步查员工的
select avg(sal),job from emp 
#where job='员工' #分组前需要过滤,使用where --高效
group by job
#having job='员工' #分组后需要过滤,使用having --相对低效

#统计 部门 出现的次数--再过滤次数>1的
select count(1),deptno from emp 
#where count(1) > 1 #分组前过滤用where,但是where里不能出现聚合函数
group by deptno
having count(1) > 1 #分组后过滤用having

#查询 每个部门 的平均工资 --再过滤>10000的
select avg(sal),deptno from emp 
#where avg(sal)>10000  #where里不能有聚合函数
group by deptno
having avg(sal)>10000 #分组后过滤having

三，事务

–1，概述

能保证多条SQL要么全成功要么全失败
4个特性：ACID
原子性：多个SQL处于同一个事务里，要么全成功要么全失败
一致性：保证数据在不同的电脑里是一致的
隔离性：数据库支持并发访问，保证事务间是隔离的，互不影响
持久性：对数据库的操作是永久的

–2，事务的隔离级别

读未提交：性能最好，数据的安全性最差
读提交：Oracle的默认的隔离级别 – 性能较好，安全性较差
可重复读：Mysql的默认的隔离级别 – 性能较差，安全性较好
串行化：安全性最高，但是表级的锁，效率低

–3，事务的处理

#Mysql默认就开启了事务，但是每条SQL一个事务
begin;#开启事务
insert into dept values(null,'java开发部','北京');
insert into dept values(null,'php开发部','上海');
commit;#提交事务--会对数据库产生持久影响
#rollback;#回滚事务--不会对数据库产生持久影响

四，字段约束

–1，概述

外键约束：把两张表之间的关系，通过两个表的主键来表示
默认约束：给指定的字段设置默认值
检查约束：给字段增加检查条件，符合才能操作，不符合不能操作

–2，测试

create table h(
	id int primary key auto_increment,
	sex char(3) default '女' #default设置默认值，给指定的字段设置默认值
)


create table i(
	id int primary key auto_increment,
	age int,
	check(age>0 and age<=150)#检查约束：给字段增加检查条件，符合才能操作，不符合不能操作
)

–3，外键约束

#外键约束
create table tb_user(
	id int primary key auto_increment,
	name varchar(20),
	age int
)
create table tb_user_addr(
	user_id int primary key auto_increment,
	addr varchar(200) ,
	#描述了两张表之间通过哪个字段关联着--外键约束
	#  外键    (子表的字段)   参考      主表名称(主表的字段)
	foreign key(user_id)    references   tb_user(id)
)

五，索引

–1，概述

好处：给加完索引的列，提高查询效率
坏处：索引本质上就是一张表，如果表的体积太大，比较占内存
主键本身就有索引、
分类：
单值索引(一个索引只包含着一个列)
复合索引(一个索引包含着多个列)
唯一索引(一个索引只包含着一个列，但是要求列的值不能相同)

–2，创建索引

#1. 创建 普通索引： create index 索引名 on 表名(字段名)
#经常被查询的字段建议加索引,
create index dname_index on dept(dname)
#2. 查看索引(观察三列：表名/字段名/索引名)
show index from dept
#3. 使用索引,按照索引列查的快
explain
select * from dept where dname like '%a%'
#4. 创建 唯一索引：alter table 表名 add unique(字段名)
	#alter table dept add unique(loc) loc的值已经重复了，不能使用唯一索引
alter table dept add unique(dname)
#5. 创建 复合索引：给多个字段加一个索引
alter table dept add index fuhe(dname,loc)
#6. 删除 索引 :alter table 表名 drop index 索引名
alter table dept drop index dname_index