cgb2106-day03

朴灿烈づ我的快乐病毒、 2021-09-11 03:16 275阅读 0赞

### 文章目录 ###

*   *  一,统计年薪
     *  二,聚合函数
     *   *  \--1,概述
         *  \--2,测试
     *  三,分组
     *   *  \--1,测试
     *  四,事务
     *   *  \-1,概述
         *  \-2,测试
     *  五,字段约束
     *   *  \--1,默认约束和检查约束
         *  \--2,外键约束
     *  六,索引
     *   *  \--1,概述
         *  \--2,测试

## 一,统计年薪 ##

#统计员工的年薪
    SELECT sal,comm,(sal+comm)*12 FROM emp
    SELECT sal,comm,
    (sal+IFNULL(comm,0))*16 AS 年薪 #as用来设置别名
    FROM emp

## 二,聚合函数 ##

### –1,概述 ###

把一列的值全都取出来,聚合起来,分析最大值,最小值,平均值,求和,求个数
    常见的聚合函数: max   min   avg    sum   count

### –2,测试 ###

#聚合函数:把查出来的列聚合起来分析数据
    #求最高薪max 
    SELECT sal FROM emp ORDER BY sal DESC LIMIT 1
    SELECT MAX(sal) FROM emp #获取sal这列里的最大值
    #求最低薪min 
    SELECT sal FROM emp ORDER BY sal LIMIT 1
    SELECT MIN(sal) MIN FROM emp
    #求和sum 
    SELECT SUM(sal) FROM emp
    #求平均数avg
    SELECT AVG(sal) FROM emp
    SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) 
    FROM emp
    #求总个数count
    SELECT COUNT(comm) FROM emp#不统计comm字段值是null的-低效
    SELECT COUNT(*) FROM emp#都统计
    SELECT COUNT(1) FROM emp#都统计-高效
    #统计工资>8000的人数
    SELECT COUNT(1) FROM emp 
    WHERE sal>8000
    #统计2019年入职的人数
    SELECT COUNT(1) FROM emp
    WHERE YEAR(hiredate)=2019

## 三,分组 ##

### –1,测试 ###

#什么时候必须分组?当查询结果中出现了聚合列和非聚合列时
    #按照什么分组合理?通常按照非聚合列分组
    #什么是聚合列非聚合列?使用了聚合函数max min sum avg count的就是聚合列
    #统计每个部门的平均薪资
    SELECT deptno,AVG(sal) FROM emp
    GROUP BY deptno #按照部门编号分组
    #统计每个岗位的最高薪资
    SELECT MAX(sal),job FROM emp
    GROUP BY job#按照岗位分组
    #统计每年的入职人数
    #count是聚合函数,year只是普通函数
    SELECT COUNT(1),YEAR(hiredate) FROM emp
    GROUP BY YEAR(hiredate)#按照非聚合列分组
    
    #having的作用:用来对分组后的数据,进一步过滤
    #统计每个部门的平均薪资,只要>10000的记录
    SELECT deptno,AVG(sal) FROM emp
    GROUP BY deptno
    HAVING AVG(sal)>10000
    #统计每个岗位的最高薪资,只要>8000的记录
    SELECT job,MAX(sal) FROM emp
    #先过滤再分组高效,但是where里不能用聚合函数
    #where MAX(sal)>8000 #会报错
    GROUP BY job
    HAVING MAX(sal)>8000
    ORDER BY MAX(sal) #升序排序
    
    #统计每年的入职人数,只要人数>1的记录
    SELECT COUNT(1),YEAR(hiredate) FROM emp
    GROUP BY YEAR(hiredate)
    HAVING COUNT(1) > 1 
    #不能改成where,后面出现了聚合函数
    
    #统计每年的入职人数,只要2017年以后的记录
    SELECT COUNT(1),YEAR(hiredate) FROM emp
    WHERE YEAR(hiredate) > 2017 #高效,只是where里不能出现聚合函数
    GROUP BY YEAR(hiredate)
    #having YEAR(hiredate)> 2017 
    #having里使用的过滤条件必须是查到的结果

## 四,事务 ##

### \-1,概述 ###

保证SQL语句,要么全执行成功,要么全失败  
有四个特征ACID:  
A是原子性: 是指多条SQL是一个原子,密不可分.如果都正确,就操作了数据.如果有错误的都会发生回滚,回到事务执行之前.  
C是一致性: 保证了数据的一致性和完整性.  
I是隔离性: 保证多线程并发时的数据安全,多个操作之间是被隔离的.  
D是持久性: 是指对数据CDU的影响是持久生效的.  
隔离级别:读未提交 读已提交 可重复读 串行化  
从前往后,性能越来越差,安全性越来越高.MySQL默认是可重复读

### \-2,测试 ###

先开启事务,然后执行一批SQL,再结束事务

mysql> start transaction; #开启事务
    Query OK, 0 rows affected (0.00 sec)
    mysql> insert into dept values(18,'java','shanghai');
    Query OK, 1 row affected (0.00 sec)
    mysql> insert into dept values(19,'java2','shanghai2');
    Query OK, 1 row affected (0.00 sec)
    mysql> commit; #提交事务
    Query OK, 0 rows affected (0.03 sec)

## 五,字段约束 ##

### –1,默认约束和检查约束 ###

### –2,外键约束 ###

#默认约束:给指定字段设置默认值
    CREATE TABLE f(
    id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
    sex VARCHAR(10) DEFAULT '男' #设置默认值
    )
    
    #检查约束:给指定字段设置合法值的检查规则
    CREATE TABLE g(
    id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
    age INT,
    CHECK(age>0 AND age<200)#设置检查
    )
    
    #外键:通过指定的SQL语句描述了两张表的关系
    #约束:子表添加记录时id必须在取自主表
         #删除主表记录时,必须没有被子表使用着
    #怎么确定谁是子表谁是主表?子表中有外键的SQL
    CREATE TABLE tb_user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    sex VARCHAR(10)
    )
    CREATE TABLE tb_user_address(
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    address VARCHAR(20),
    #表明和哪张表的哪个字段有关系
    #foreign key(本表的主键) references 关联表(主键)
    FOREIGN KEY(user_id) REFERENCES tb_user(id)
    )

## 六,索引 ##

### –1,概述 ###

为了提高数据库的查询效率,提供了索引的技术

### –2,测试 ###

#查看索引
    SHOW INDEX FROM emp