本文详细介绍了Oracle数据库中的复杂查询语句，包括group by和having语句的组合使用，各种链接操作（相等链接、左链接、右链接、全链接），集合操作符（UNION、UNION ALL、INTERSECT、MINUS）的应用，以及SQL执行顺序、exists子句、子查询、CASE WHEN表达式、分析函数和DECODE函数的用法。特别地，分析了rownum在限制查询结果数量中的作用。
1、group by和having语句
group by语句用于将信息划分为更小的组，每一组行返回针对该组的单个结果，通常配合avg()、sum()、count()等分组函数
having语句用于group by语句检索行的条件
select sid,sum(score) from studentscore group by sid; //按照某一列的数据进行分组。例如这里求每个学生的总成绩
select sid,avg(score) from studentscore group by sid having(avg(score)>60); //求平均成绩大于60分的学生
select sid,avg(score) from studentscore group by sid having(avg(score)>(select avg(score) from studentscore)); //求平均成绩大于所有学生的平均成绩的学生
2、相等链接、左链接、右链接、全链接
select s.sid,s.name,a.sid,a.city from students s,addr a where s.sid = a.sid; //相等查询，查询两张表中sid相等的学生ID、姓名和城市
select s.sid,s.name,a.sid,a.city from students s inner join addr a on s.sid = a.sid; //相等查询的另一种方式，也是查询两张表中sid相等的学生ID、姓名和城市
select s.sid,s.name,a.sid,a.city from students s,addr a where s.sid = a.sid(+); //左外连接，s表为左表，左表中的sid都显示，右表只显示与左表中sid相同的sid
select s.sid,s.name,a.sid,a.city from students s left outer join addr a on s.sid = a.sid; //左表连接的另一种方式
select s.sid,s.name,a.sid,a.city from students s,addr a where s.sid(+) = a.sid; //右外连接，a为右表，右表中的sid都显示，左表只显示与右表中sid相同的sid
select s.sid,s.name,a.sid,a.city from students s right outer join addr a on s.sid = a.sid; //右表连接的另一种方式
select s.sid,s.name,a.sid,a.city from students s full outer join addr a on s.sid = a.sid; //全连接，两张表的sid都会显示
3、集合操作符
UNION操作符返回第一个查询结果与第二查询结果的并集，重复的只出现一次。
UNION ALL操作符返回第一个查询结果与第二个查询结果的并集，重复的出现2次。
INTERSECT操作符返回第一个查询结果与第二个查询结果的交集。
MINUS操作符返回从第一个查询结果种排除第二个查询中出现的行。
SQL> select * from t1;
ID
----------
1
2
3
SQL> select * from t1 where id=1;
ID
----------
1
select * from t1 union select * from t1 where id = 1;
ID
----------
1
2
3
select * from t1 union all select * from t1 where id = 1;
ID
----------
1
2
3
1
select * from t1 minus select * from t1 where id = 1;
ID
----------
2
3
select * from t1 intersect select * from t1 where id = 1;
ID
----------
1
4、SQL语句的执行顺序
| 执行顺序 | 执行语句 |
|---|---|
| 1 | from |
| 2 | where |
| 3 | group by |
| 4 | having |
| 5 | select |
| 6 | order by |
5、exists的使用
exists用来判断查询所得的结果中，是否有满足条件的结果存在
SQL> select * from tab1 where exists(select * from tab1 where name = 2);
NAME
----------
1
2
3
SQL> select * from tab1 where exists(select * from tab1 where name = 5);
未选定行
6、子查询的使用
SQL> create table students(sid number(3),birthday date,sname varchar2(10));
SQL> insert into students values(1,to_date(19900102,'yyyymmdd'),'Tom');
SQL> insert into students values(2,to_date(19891122,'yyyymmdd'),'Jim');
SQL> insert into students values(3,to_date(19901225,'yyyymmdd'),'Jack');
SQL> insert into students values(4,to_date(19900221,'yyyymmdd'),'Adam');
SQL> create table address(sid number(3),address varchar2(20));
SQL> insert into address values(1,'北京');
SQL> insert into address values(2,'上海');
SQL> insert into address values(3,'广州');
SQL> insert into address values(4,'北京');
SQL> select * from students;
SID BIRTHDAY SNAME
---------- -------------- --------------------
1 02-1月 -90 Tom
2 22-11月-89 Jim
3 25-12月-90 Jack
4 21-2月 -90 Adam
SQL> select * from address;
SID ADDRESS
---------- ----------------------------------------
1 北京
2 上海
3 广州
4 北京
//查询地址为“北京”的学号最大的学生的姓名
SQL> select sname from students where sid = (select max(sid) from address where
address = '北京');
SNAME
--------------------
Adam
7、case when的使用
语法：
第一种写法：
case
when 条件1 then action1
when 条件2 then action2
when 条件3 then action3
......
else action N
end 结果的别名
第二种写法：
case 表达式
when 值1 then action1
when 值2 then action2
when 值3 then action3
......
else action N
end 结果的别名
举例：
SQL> select case
2 when substr('20210101',5,2) = '01' then '一月'
3 when substr('20210101',5,2) = '02' then '二月'
4 when substr('20210101',5,2) = '03' then '三月'
5 else null
6 end from dual;
CASEWHENSUBS
------------
一月
//根据学生成绩，判断学生是否“优秀”、“及格”、不及格，并更新成绩表
SQL> create table 成绩(sid number(3),成绩 number(3),级别 varchar2(10));
SQL> insert into 成绩 values(1,98,null);
SQL> insert into 成绩 values(2,89,null);
SQL> insert into 成绩 values(3,61,null);
SQL> insert into 成绩 values(4,59,null);
SQL> select * from 成绩;
SID 成绩 级别
---------- ---------- --------------------
1 98
2 89
3 61
4 59
SQL> update 成绩 set 级别=(select g from (select sid,case when 成绩 >= 90 then '
优秀' when 成绩 >= 60 then '及格'else '不及格' end g from 成绩) a where 成绩.si
d = a.sid);
SQL> select * from 成绩;
SID 成绩 级别
---------- ---------- --------------------
1 98 优秀
2 89 及格
3 61 及格
4 59 不及格
8、分析函数
分析函数用于计算完成聚集的累积排名、序号等。分析函数为每组记录返回多行。
“row_number”、“rank”、“dense_rank”用于计算一个行在一组有序行中的牌位，序号从1开始。
“row_number”返回连续的排序，无论值是否相等。
“rank”相等的值排序相同，序号随后跳跃。
“dense_rank”相同的值排序相同，序号是连续的。
SQL> select * from t1;
A B
---------- ----------
1 1
2 1
3 3
SQL> select a,b ,row_number() over(order by b desc) from t1;
A B ROW_NUMBER()OVER(ORDERBYBDESC)
---------- ---------- ------------------------------
3 3 1
1 1 2
2 1 3
SQL> insert into t1 values(4,4);
SQL> select a,b,rank() over(order by b) from t1;
A B RANK()OVER(ORDERBYB)
---------- ---------- --------------------
1 1 1
2 1 1
3 3 3
4 4 4
SQL> select a,b,dense_rank() over(order by b) from t1;
A B DENSE_RANK()OVER(ORDERBYB)
---------- ---------- --------------------------
1 1 1
2 1 1
3 3 2
4 4 3
9、decode中的if-then-else
语法：
decode(表达式,if1,then1，if2,then2,......,else)
举例：
SQL> select a,b,decode(sign(b-60),1,'及格',0,'及格',-1,'不及格') from t1;
A B DECODE(SIGN(B-60),
---------- ---------- ------------------
1 99 及格
2 75 及格
3 54 不及格
4 4 不及格
//sign()函数用来判断数字是否为正数，返回“1”表示正数，返回“0”表示零，返回“-1”表示负数
10、rownum
对查询结果输出前若干条，只能和“<”、“<=”、“between and”配合使用。使用“between and”时只能从1开始。
SQL> select * from t1;
A
----------
1
2
3
4
5
SQL> select * from t1 where rownum < 3;
A
----------
1
2
SQL> select * from t1 where rownum <= 3;
A
----------
1
2
3
SQL> select * from t1 where rownum = 3;
未选定行
SQL> select * from t1 where rownum > 3;
未选定行
SQL> select * from t1 where rownum >= 3;
未选定行
SQL> select * from t1 where rownum between 1 and 4;
A
----------
1
2
3
4
SQL> select * from t1 where rownum between 2 and 4;
未选定行
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
