oracle--分组查询与子查询

八、分组查询
select 分组字段1,分组字段2 , 聚合函数() from 表名 group by 分组字段1,分组字段2 having 条件 order by 分组字段/聚合函数;

select 分组字段1,分组字段2 , 聚合函数() from 表名 where 条件表达式 group by 分组字段1,分组字段2 order by 分组字段/聚合函数;

条件表达式：不能出现聚合函数
条件 ：可以出现聚合函数

select grades, sex, count(*) from stu group by grades, sex;

select grades, max(mathe) from stu group by grades;

--1.如果有分组：select 与from之间只能出现：分组字段名与聚合函数
--2.如果分组，又排序：排序只能出现：分组字段名与聚合函数
--3.如查分组，条件语句为：having ， having中只能出现： 分组字段名与聚合函数, 只能在 group by 的后面

--4.如果分组中使where做为条件语句，where中只能出现:分组字段名

select grades, max(age) from stu group by grades having max(age)>30 order by max(age)
select grades, max(age) from stu group by grades having grades<2 order by max(age)

select grades, max(age) from stu where grades<2 group by grades order by max(age);

习题1：对s_emp中的title进行分组，并显示部门的总人数与最高工资

数学的平均分大于75人

select last_name,avg(mathe) from stu group by last_name having avg(mathe)>75

数学平均大于75分的班级

select grades,avg(mathe) from stu group by grades having avg(mathe)>75;

按班级分组，并且将数学成绩大于50分的求平均 平均并排序
select grades, avg(mathe) from stu where mathe>50 group by grades order by avg(mathe) desc ;

习题2、对s_emp表中salary+commission_pct的总工资大于1500的人

select * from s_emp where sum(salary+commission_pct)>1500

select last_name, sum(salary+commission_pct) zgz from s_emp group by last_name having sum(salary+commission_pct)>1500

select last_name, salary, commission_pct, sum(salary+commission_pct) from s_emp group by last_name, salary, commission_pct having sum(salary+commission_pct)>1500;
注：只要判断条件中有聚合函数，就要使用分组

 

九、函数

1、lower(字段名)--小写, upper()--大写, initcap()--首字母大写

select lower(last_name) from stu where lower(last_name) like 'w%' ;
select upper(last_name) from stu where upper(last_name) like 'W%';
select initcap(last_name) from stu where initcap(last_name) like 'W%';

注：字段为字符类型

习题1：对s_emp表中last_name中含有v的记录以last_name小写，first_name大写，userid的开头字母大小显示
select lower(last_name), upper(first_name), initcap(userid) from s_emp where upper(last_name) like '%V%'

select lower(last_name), upper(first_name), initcap(userid) from s_emp where lower(last_name) like '%v%'

习题2：对s_emp表中last_name中含有大写V的记录以last_name小写，first_name大写，userid的开头字母大小显示
select lower(last_name), upper(first_name), initcap(userid) from s_emp where last_name like '%V%'

习题3：对s_emp表中last_name中含有小写v的记录以last_name小写，first_name大写，userid的开头字母大小显示
select lower(last_name), upper(first_name), initcap(userid) from s_emp where last_name like '%v%'

2、截取
substr(字符串的字段名, 开始截取的位置， 截取长度)

select substr(last_name,2, 3) from stu;

习题1：在表s_emp中查找fires_name的前两个字符中含有ma的，并显示last_name从第3个到5个字符。
select substr(last_name, 3, 3), first_name from s_emp where lower( substr(first_name, 1,2 )) like 'ma%';

select substr(last_name, 3, 3), first_name from s_emp where lower( substr(first_name, 3,3 )) like 'ber';

3、字符串长度
length(字符串的字段)

select last_name, length(last_name) from stu where length(last_name)>2;

4、round()四舍五入，trunc()截取 数值

select round(avg(mathe), 2) avg from stu;

select trunc(avg(mathe), 2) avg from stu;
习题1：在s_emp表中，查找部门的对工资涨11.12345%的平均工资，并保留3小数。 （四舍五入，与不做四舍五入）

select title, round( avg(salary*11.12345 + salary) , 3), trunc(avg(salary*11.12345 + salary), 3 ) from s_emp group by title;

十、子查询： 
--子查询的位置可以出现在where子句、having子句、from子句
--子查询还可以嵌套其他子查询，允许多层嵌套

1、< 、<=、>、>=、= 、<>、关系运算符时，
注：
1、一个select 的查询结果 要做为 另一个select的条件
2、子查询句的返回集只能有一个字段
3、子查询的返回字段只能有一个值

select * from 表名 where 字段 关系运算符（select 字段 from 表名）

select * from stu where chinese > (select avg(mathe) from stu);
select * from stu where mathe= (select max(mathe) from stu );
select * from stu where mathe< (select max(mathe) from stu where sex='男' );

习题：查找s_emp表中，比平均工资高的人， （平均工资）

select last_name, salary from s_emp where salary > ( select avg(salary) from s_emp ) 
select last_name, salary, avg(salary) from s_emp where salary > ( select avg(salary) from s_emp ) group by last_name, salary

2、all, any , in 
注：、
注：
1、一个select 的查询结果 要做为 另一个select的条件
2、子查询句的返回集只能有一个字段
3、子查询的返回字段只能有多个值，需关系运算符后加 all, any , in

all:所有
select * from stu where id >= all (select id from stu where mathe>60); // 1,3,4,11
1、>all 比最大的大
2、<all 比最小的小

any:一个
select * from stu where id <= any (select id from stu where mathe>60);

1、>any 比最小的大
2、<any 比最大的小

in:相等
select * from stu where id in (select id from stu where mathe>60); // 1,3,4,12

select * from stu where id =any (select id from stu where mathe>60); // 1,3,4,12

习题：查询出比最多班级人数少的班级

select grades, count(*) from stu group by grades having count(*) <any (select count(*) from stu group by grade