Java 11---SELECT分组查询和子查询

导读

1.GROUP BY子句
2.HAVING子句
3.嵌套子查询
4.DML:删，改
5.事务：Transaction

GROUP BY子句

语法:
	SELECT
	FROM
	WHERE
	GROUP BY 列1,列2...
	ORDER BY;

注意：

1.执行流程
	FROM->WHERE,行的筛选->GROUP BY,把行分组->SELECT,针对组的统计
	->ORDER BY,排序
	2.WHERE:分组前的筛选,GROUP BY在WHERE之后
	3.组函数:一个组可以得到一个结果
	4.查询时,SELECT子句出现组函数或者有GROUP BY子句["组"关系],
		查询中不能出现普通的列,但可以出现分组的列
	5.可以按照多列分组,多个列值都相等的在一组

eg1:列出每个部门员工的数量，最低薪水，平均薪水，最高薪水
	SELECT dept_id,COUNT(id),MIN(salary),AVG(salary),MAX(salary)
	FROM s_emp
	GROUP BY dept_id;
	
eg2:查询每个销售对应的客户数量,销售编号,销售名字	
	SELECT c.sales_rep_id,COUNT(c.id),e.first_name
	FROM s_customer c,s_emp e
	WHERE c.sales_rep_id=e.id
	GROUP BY c.sales_rep_id,e.first_name;

eg3:查询部门编号大于40的每个部门id,部门人数,部门名
	SELECT d.id,d.name,COUNT(e.id)
	FROM s_emp e,s_dept d
	WHERE e.dept_id=d.id
	AND d.id>40
	GROUP BY d.id,d.name;

HAVING子句

	WHERE子句:行的筛选	
	HAVING:组的筛选,一定出现在分组后

语法:
	SELECT
	FROM
	WHERE 条件
	GROUP BY
	HAVING 条件
	ORDER BY;

注意：

1.执行流程
	FROM->WHERE,行的筛选->GROUP BY,分组->HAVING,组的筛选->SELECT
	->ORDER BY;
	2.有GROUP BY,才能有HAVING
	3.HAVING中只能只用组函数或者分组的列

eg1:查询部门人数高于2人的部门编号,部门人数
	SELECT dept_id,COUNT(id)
	FROM s_emp
	GROUP BY dept_id
	HAVING COUNT(id)>2;
	
eg2:查询职位包含sal(不区分大小写)的并且对应职位人数高于2人的职位名,人数
	SELECT title,COUNT(id)
	FROM s_emp
	WHERE LOWER(title) LIKE '%sal%'
	GROUP BY title
	HAVING COUNT(id)>2;  效率更高
	
	SELECT title,COUNT(id)
	FROM s_emp
	GROUP BY title
	HAVING LOWER(title) LIKE '%sal%' AND COUNT(id)>2;
	

嵌套子查询

SELECT
FROM
WHERE (
	SELECT
	FROM
	WHERE
);

注意：

		1.查询里面嵌套的查询需要用()括起来
		2.子查询可能出现的位置:WHERE子句,HAVING子句,FROM子句

WHERE子句中:
eg1:查询薪资高于Mark的薪资的员工id,薪资
SELECT id,salary
FROM s_emp
WHERE salary>(
	SELECT salary
	FROM s_emp
	WHERE first_name='Mark'
);

eg2:查询高于平均薪资的员工id,名字,薪资
SELECT id,first_name,salary
FROM s_emp
WHERE salary>(
	SELECT AVG(salary)
	FROM s_emp
);

eg3:查询薪资高于9号和10号员工薪资的员工编号,薪资

错误的:如果子查询返回多个结果,不能直接用>,=,<比较,多行子查询
SELECT id,salary
FROM s_emp
WHERE salary>(
	SELECT salary
	FROM s_emp
	WHERE id IN(9,10)
);---（错误的）

SELECT id,salary
FROM s_emp
WHERE salary>ALL(
	SELECT salary
	FROM s_emp
	WHERE id IN(9,10)
);---（正确的）

>ALL:salary大于所有的结果     AND
>ANY:salary大于任意一个即可   OR
IN:等于集合中的某一个

SELECT id,salary
FROM s_emp
WHERE salary>(
	SELECT salary
	FROM s_emp
	WHERE id=9
) AND salary>(
	SELECT salary
	FROM s_emp
	WHERE id=10
);---（正确的）

HAVING子句中:
eg1:查询部门平均薪资高于Bela薪资的所有部门编号,平均薪资
SELECT dept_id,AVG(salary)
FROM s_emp
GROUP BY dept_id
HAVING AVG(salary)>(
	SELECT salary
	FROM s_emp
	WHERE first_name='Bela'
);

FROM子句中:
	可以把一个结果集当成表,再做查询,结果集也可以取名字的
	
eg1:查询用户表第6~10条记录:经典
SELECT * 
FROM(	
	SELECT rownum rn,id,first_name,salary
	FROM s_emp
	WHERE rownum<11
)
WHERE rn>5;
	
eg2:查询薪资最高的5个员工编号,薪资,经典
SELECT *
FROM(
	SELECT id,salary
	FROM s_emp
	ORDER BY salary DESC
)WHERE rownum<6;

DML:删，改

	DML:对表数据增,删,改
	DML操作后,需要COMMIT;

UPDATE:更新数据,修改
语法:
	UPDATE 表名 SET 列名=值,列名=值...
	[WHERE 条件];
	
	注意:
		1.如果没有条件,修改所有的记录
		2.条件写法同SELECT
	
eg1:修改每个员工的薪资,+1000
	UPDATE s_emp SET salary=salary+1000;

eg2:修改41号部门每个员工的薪资为2000
	UPDATE s_emp SET salary=2000
	WHERE dept_id=41;

DELETE:删除数据行
语法:
	DELETE FROM 表名
	[WHERE 条件];
	
	注意:
		1.如果没有条件,删除表的所有数据
		2.WHERE条件同SELECT
		3.如果数据行被别的表引用,删除会失败
		
eg1:
	INSERT INTO s_dept VALUES(51,'IT',1);
	删除51号部门	
	DELETE FROM s_dept
	WHERE id=51;
	

事务：Transaction

事务:Transaction
	一个事务结束,另一个事务立刻开始
	对于增删改操作,在一个事务之内的操作
	commit:结束本次事务,把这个事务中所有的增删改操作提交
	rollback:结束本次事务,把这个事务中所有的增删改操作回滚.
	
	利用事务,可以把一个业务的多个操作放到一个事务中,达到要么都成功要么都失败

事务的四个特性:
	原子性(Atomicity):一个事务的多个操作要么都成功要么都失败
	一致性(Consistency):事务前后数据的一致
	隔离性(Isolution):一个事务中间过程的数据,另一个事务无法看到
	持久性(Durability):事务提交后的数据持久保存到数据库中,不会因为意外状况断网断电等数据丢失

事务开始的边界:
	连接到数据库,一个事务就开始了
	一个事务结束,另外一个事务就开始了
事务结束的边界:
	COMMIT:提交
		事务正常结束,所有的数据写入数据库
		当提交时,释放锁
		在SQLPLUS中,DML操作默认需要手动提交,COMMIT
		DDL,DCL都是自动提交的
	ROLLBACK:回滚
		事务异常结束,所有的数据恢复到事务开始的时候
		
		点击x关闭时,是异常关闭,  通过exit,quit是正常关闭
		异常关闭,事务会回滚,     正常关闭,事务会提交
		
	回滚到指定位置:需要指定回滚点
		保存回滚点:SAVEPOINT 名字;(名字是自己写的)
		回滚到指定回滚点:ROLLBACK TO 名字;

try{
	操作1;
	操作2;
	操作3;
	//如果都成功
	commit;
}catch(Exception e){
	rollback;
}