cgb2107-day03

╰+攻爆jí腚メ 2021-09-11 03:32 327阅读 0赞

### 文章目录 ###

*   *  一,条件查询
     *   *  \--1,概述
     *  二,聚合函数
     *   *  \--1,概述
         *  \--2,测试
     *  三,分组
     *   *  \--1,概述
         *  \--2,测试
     *  四,事务
     *   *  \--1,概述
         *  \--2,操作
         *  \--3,测试
     *  五,字段约束
     *   *  \--1,概述
         *  \--2,测试

## 一,条件查询 ##

### –1,概述 ###

#limit:分页
    SELECT * FROM emp LIMIT 2 #展示前两条数据
    SELECT * FROM emp LIMIT 0,2 #展示前两条数据
    SELECT * FROM emp LIMIT 1,4 #从第n+1行开始展示,要展示的行数
    SELECT * FROM emp LIMIT 3,2
    #order by: 排序,默认是升序ASC(可以省略)
    SELECT * FROM emp ORDER BY sal ASC#按照empno排序
    SELECT * FROM emp ORDER BY sal DESC#按照empno降序
    SELECT * FROM emp ORDER BY ename#按照ename排序,字典顺序
    SELECT * FROM emp ORDER BY hiredate#按照数值排序
    SELECT * FROM emp ORDER BY job#按照汉字对应的数字排
    #统计案例:
    #查询2017年以前入职的员工信息
    SELECT * FROM emp WHERE hiredate<'2017-1-1'
    SELECT * FROM emp WHERE YEAR(hiredate) < 2017
    #可以给列设置别名 ,使用as关键字(也可以省略)
    #计算入职年份
    SELECT *,YEAR(NOW())-YEAR(hiredate) AS 年份 FROM emp 
    #统计年薪
    SELECT *,sal*13+IFNULL(comm,0)*13 年薪  FROM emp

## 二,聚合函数 ##

### –1,概述 ###

指把一列的值聚合在一起,在做分析  
聚合函数: max() min() avg() sum() count()

### –2,测试 ###

#聚合函数: max min sum avg count
    SELECT MAX(sal) FROM emp#查最高薪
    SELECT MIN(sal) FROM emp #查最低薪
    SELECT AVG(sal) FROM emp #查平均工资
    SELECT SUM(sal) FROM emp #工资总和
    
    SELECT COUNT(*) FROM emp #统计个数
    SELECT COUNT(1) FROM emp #统计个数,高效
    SELECT COUNT(empno) FROM emp #了解
    SELECT COUNT(comm) FROM emp #了解,不统计null
    
    #查询时,出现了混合(聚合列和非聚合列)列的现象
    SELECT empno,SUM(sal) FROM emp #报错
    SELECT SUM(sal) FROM emp 
    SELECT empno FROM emp

## 三,分组 ##

### –1,概述 ###

把查询结果进行分组 , 统计一组数据中的最大值,最小值…使用group by

### –2,测试 ###

#分组:GROUP BY
     #口诀:当查询时出现了聚合列和非聚合列时,必须按非聚合列分组
    SELECT MAX(sal) FROM emp #查最高薪
    #查每个部门的最高薪
    SELECT MAX(sal),deptno FROM emp GROUP BY deptno
    #查每个岗位的平均工资 
    SELECT job,AVG(sal) FROM emp GROUP BY job
    #查每年入职的人数
    SELECT YEAR(hiredate) YEAR,COUNT(1) FROM emp
    			GROUP BY YEAR(hiredate)
    #查每个部门的人数
    SELECT deptno,COUNT(1) FROM emp
    		        GROUP BY deptno	
    #having:完成分组后的过滤 
    #查每个部门的人数,只要人数>1的部门
    SELECT deptno,COUNT(1) FROM emp
    GROUP BY deptno
    HAVING COUNT(1) > 1
    #查每个岗位的平均工资,只要>8000的
    SELECT job,AVG(sal) FROM emp
    GROUP BY job
    HAVING AVG(sal)>8000#低效,分完组才过滤
    
    SELECT job,AVG(sal) FROM emp
    WHERE AVG(sal)>8000#比having高效,因为在分组前就过滤了
    #where里不能出现聚合函数
    GROUP BY job
    
    #查每年入职的人数,只要人数>1的
    SELECT COUNT(1),YEAR(hiredate) FROM emp
    GROUP BY YEAR(hiredate) #按照非聚合列分组
    HAVING COUNT(1) > 1

## 四,事务 ##

### –1,概述 ###

用来 保证多个操作 要么全成功,要么全失败.  
四个特性:ACID  
1,原子性: 多个操作, 是绑定到一起的,要么全成功,要么全失败  
2,一致性: 在多个系统中,保证数据是一致的  
3,隔离性: 在保证了性能,的同时隔离用户的操作  
4,持久性: 对数据的操作都是有持久影响的  
隔离级别:  
1,读未提交: 效率高,安全性差  
2,读已提交: 牺牲了效率,提高了安全性–Oracle数据的默认隔离级别  
3,可重复读: 牺牲了效率,提高了安全性–Mysql数据的默认隔离级别  
4,串行化: 安全性最高,但是效率太低

### –2,操作 ###

1, 开启事务: start transaction;  
2, 结束事务: commit 提交事务(最终持久性影响数据库)  
rollback 回滚事务(回滚到事务操作前)  
3, MySQL数据库默认就已经管理了事务 , 会为每条SQL提供事务 .

### –3,测试 ###

START TRANSACTION ; #开启事务
    INSERT INTO dept VALUES(NULL,'test','大钟寺');
    INSERT INTO dept VALUES(NULL,'test2','大钟寺2');
    COMMIT;#关闭事务

## 五,字段约束 ##

### –1,概述 ###

给字段添加约束, 字段的值都有了要求  
包括: 唯一 非空 主键 外键 默认 检查

### –2,测试 ###

#默认约束default: 给字段设置默认值
    CREATE TABLE h(
     id INT PRIMARY KEY AUTO_INCREMENT,#主键自增
     sex VARCHAR(10) DEFAULT '男' #默认约束
    )
    #检查约束CHECK:检查字段的值是否合法
    CREATE TABLE i(
     id INT PRIMARY KEY AUTO_INCREMENT,
     age INT,
     CHECK(age>0 AND age<18)#检查约束,不合法时会报错
    )