cgb2105-day03

一时失言乱红尘 2021-09-11 02:28 204阅读 0赞

### 文章目录 ###

*   *  一，统计练习
     *   *  \--1，代码
     *  二，分组
     *   *  \--1，概述
         *  \--2，测试
     *  三，事务
     *   *  \--1，概述
         *  \--2，事务的隔离级别
         *  \--3，事务的处理
     *  四，字段约束
     *   *  \--1，概述
         *  \--2，测试
         *  \--3，外键约束
     *  五，索引
     *   *  \--1，概述
         *  \--2，创建索引

## 一，统计练习 ##

### –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，外键约束 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70]

#外键约束
    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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70]: /images/20210911/f60d06c1ec3247da91a7ee494813bcfb.png