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