cgb2108-day03

小鱼儿 2022-09-13 05:25 155阅读 0赞

### 文章目录 ###

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

## 一,条件查询 ##

### –1,测试 ###

#limit:分页,限制数据展示的条目
    #展示两条员工信息
    SELECT * FROM emp LIMIT 2 #取前几条
    SELECT * FROM emp LIMIT 2,3#从n+1条数据开始展示,总共展示的条数
    #order by:排序,默认升序asc,降序DESC--字典顺序
    SELECT * FROM emp ORDER BY sal ASC #按照sal排序,默认升序
    SELECT * FROM emp ORDER BY sal DESC #降序
    SELECT * FROM emp ORDER BY ename #按照字母升序顺序
    SELECT * FROM emp ORDER BY hiredate #按照数字大小升序排
    SELECT * FROM emp ORDER BY job 
    #了解::汉字排序时会查utf8里对应的数字,按照数字升序排序
    #统计需求:
    #练习1:统计2019年以前入职的员工信息
    SELECT * FROM emp WHERE hiredate<'2019-1-1'
    SELECT * FROM emp WHERE YEAR(hiredate)<2019
    #练习2:统计员工的年薪
    SELECT sal,comm,sal*13+IFNULL(comm,0)*13 年薪 FROM emp

## 二,聚合函数 ##

### –1,概述 ###

把一列的所有值聚合起来,然后做数据分析  
常见的聚合函数: count() max() min() avg() sum()

### –2,测试 ###

#1.聚合函数:把一列的所有值聚合在一起
    SELECT sal FROM emp#查询员工工资
    SELECT MAX(sal) FROM emp #查询最高工资
    SELECT MIN(sal) FROM emp #查询最低工资
    #SUM查询工资总和,AVG平均工资
    SELECT SUM(sal),AVG(sal) FROM emp
    #count()统计总记录数
    SELECT COUNT(sal) FROM emp
    SELECT COUNT(comm) FROM emp #不统计null元素
    SELECT COUNT(*) FROM emp #低效
    SELECT COUNT(1) FROM emp #高效

## 三,分组 ##

### –1,概述 ###

就是把数据,按照一些维度分成组,然后再把这一组数据继续分析

### –2,测试 ###

#1,分组:group by,把数据按照维度分组,后,数据分析
      #口诀:
      #什么时候要分组?查询时出现了混合列
      #按照什么分组?按照非聚合列分组
    #练习1:统计每个岗位的平均薪资
    SELECT job,AVG(sal) FROM emp GROUP BY job 
    #练习2:统计每个部门的员工人数
    SELECT deptno,COUNT(1) FROM emp GROUP BY deptno
    #练习3:统计每年入职的人数
    SELECT YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate)
    #练习4:统计每个部门的最高薪
    SELECT deptno,MAX(sal) FROM emp GROUP BY deptno
    
    #2.分组后过滤having
    #练习1:统计每个部门的最高薪,只要>10000的记录
    SELECT MAX(sal) a,deptno FROM emp 
    GROUP BY deptno #按照非聚合列分组
    HAVING a>10000 #group by后的过滤必须用having
    #练习2:统计每年入职的人数,只要人数>1的记录
    SELECT COUNT(1),YEAR(hiredate) a FROM emp
    #where COUNT(1)>1 
    #where里不能用别名,不能出现聚合函数,比having高效
    GROUP BY a # 按照非聚合列a分组
    HAVING COUNT(1)>1 # 按照人数b过滤
    #having和where能互换吗?效率谁高?

## 四,事务 ##

### –1,概述 ###

Transaction,保证多条操作要么全成功,要么全失败.  
事务的四大特性:  
1,原子性: 把多条SQL,看做是一个原子,密不可分,要么全成功,要么全失败.  
2,隔离性: 数据库为了保证性能也支持高并发,但是有安全隐患.保证多个操作之间是隔离的  
3,持久性: 对数据库的操作是持久生效影响的  
4,一致性: 保证数据在多个系统中是一致的  
隔离级别: 从上至下,安全性越来越好,但是性能越来越差  
1, 读未提交: 性能最好,但是安全性最差  
2, 读已提交: 性能较差,安全性较高,Oracle数据库的默认隔离级别  
3, 可重复读: 性能较差,安全性较高,MySQL数据库的默认隔离级别  
4, 串行化: 性能太差

### –2,测试 ###

MySQL数据库会为每条SQL提供事务管理.每条SQL执行前MySQL会自动开启事务,SQL执行结束MySQL会自动结束事务.  
如果想要自己管理事务: 开启事务/结束事务(commit/rollback)

#测试 手动管理事务CUD:模拟向c表中插入数据
    START TRANSACTION; #开启事务
    INSERT INTO c VALUES(NULL,'654321');
    INSERT INTO c VALUES(NULL,'123456');
    COMMIT; #结束事务

## 五,字段约束 ##

### –1,测试 ###

#字段约束:非空约束/唯一约束/主键约束/默认约束..
    #1.默认约束:给字段使用default添加默认值
    CREATE TABLE e(
     id INT PRIMARY KEY AUTO_INCREMENT,
     sex VARCHAR(10) DEFAULT '男' #默认约束,设置默认值
    )
    #2.检查约束:给字段使用check添加合法性的检查
    CREATE TABLE f(
     id INT PRIMARY KEY AUTO_INCREMENT,
     age INT,
     CHECK(age<100)#检查约束,age录入不合法数据时无法保存
    )
    #3.外键约束:防止了冗余的数据,通过外键来描述两张表的关系
    #特点是:当子表中添加数据时,子表的主键值 必须 取自主表!
           #当主表删除数据时,子表没有相关的记录
    CREATE TABLE tb_user(
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(20),
     PASSWORD VARCHAR(20)
    )
    CREATE TABLE tb_user_addr(
     user_id INT PRIMARY KEY,
     address VARCHAR(100) ,
     #描述和tb_user表的关系,外键
    #语法:foreign key(本表的主键名) references 对方表名(对方表的主键)
     FOREIGN KEY(user_id) REFERENCES tb_user(id) #创建外键
    )