cgb2107-day03

一,条件查询

–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)#检查约束,不合法时会报错
)