cgb2108-day03_cgblpx的博客

一,条件查询

–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) #创建外键
)