一、sql语句的执行顺序
from——where——group by——having——select——order by
group by的使用方法：select 组名，聚合函数（count、max、min、sum、avg等) from tables group by 组名：
二、EXISTS的使用（返回true或false)
SQL> select * from student where exists(select * from address where zz='zhengzhou');
1、先执行from
2、再执行where条件
a、exists中的内容是否有值
b、如果有值，返回student所有值，如果没有值，则返回空
三、新表做测试
1、创建新表（select结果作为条件）
（1）
create table student(sno number(6) ,birthday date, sname varchar2(12));
insert into student values(1,'11-JAN-81','zhangsan');
insert into student values(2,'10-MAR-82','lisi');
insert into student values(3,'06-JAN-83','wangwu');
insert into student values(4,'26-JAN-83','zhaoliu');
（2）
create table address(sno number(6) , zz varchar2(12));
insert into address values(1, 'zhengzhou');
insert into address values(2, 'kaifeng');
insert into address values(3, 'luoyang');
insert into address values(4, 'zhengzhou');
（3）题目：找出zz是郑州的学生中，sno最大的学生的sname。
SQL> select sname from student where sno=(select max(sno) from address where zz='zhengzhou');
2、创建新表2——group by 练习
create table student2(xh number,xm varchar2(10),nl int);
insert into student2 values (1,'A',21);
insert into student2 values (2,'B',22);
insert into student2 values (3,'A',23);
insert into student2 values (4,'A',24);
insert into student2 values (5,'A',25);
insert into student2 values (6,'C',26);
insert into student2 values (7,'B',27);
任务：查找xm有相同的纪录，并显示出来，如下：
XH XM NL
---------- ---------- ----------
1 A 21
3 A 23
4 A 24
5 A 25
2 B 22
7 B 27
（1）SQL>select count(*) from student2;
（2）SQL>select xm,count(*) from student2 group by xm;
（3）SQL>select xm,count(*) from student2 group by xm having(count(*));
（4）SQL> select * from student2 where xm in(select xm from student2 group by xm having(count(*)>1));
3、创建新表3——自连接的使用
CREATE TABLE Manager (
ID char(10) ,
name varchar2(10) ,
managerID char(10)
);
insert into Manager values('001', 'zhangyi', '004');
insert into Manager values('002', 'zhanger', '004');
insert into Manager values('003', 'zhangsan', '003');
insert into Manager values('004', 'zhangsi', '004');
要求：显示ID、name、manager的name.
ID NAME MANAGER的name
---------- ---------- ----------
001 zhangyi zhangsi
002 zhanger zhangsi
003 zhangsan zhangsan
004 zhangsi zhangsi
（1）查看表的自连接：SQL> select * from manager a,manager b;
（2）查看a.managerID的编号和b.ID一致的那一行，即为满足条件
SQL> select * from manager a,manager b where a.managerID=b.ID;
（3）过滤上述的表 并按照a.ID排序
SQL> select a.ID,a.NAME,b.NAME mangagerName from manager a,manager b where a.managerID=b.ID;
SQL> select a.ID,a.NAME,b.NAME mangagerName from manager a,manager b where a.managerID=b.ID order by a.ID asc;
4、select case when使用
方法一：算式在条件中
SELECT CASE
WHEN 条件1 THEN action1
WHEN 条件2 THEN action2
WHEN 条件3 THEN action3
…..
ELSE actionN
END CASE
方法二：算式在case 后，结果在条件中
SELECT CASE selector
WHEN value1 THEN action1
WHEN value2 THEN action2
WHEN value3 THEN action3
…..
ELSE actionN
END [CASE]
(1)创建表：
create table transcript3(sno number, km varchar2(10), score number,grade char(6)); insert into transcript3 values(1, 'Chinese', 65,null); insert into transcript3 values(2, 'Math', 76,null); insert into transcript3 values(3, 'English', 86,null); insert into transcript3 values(4, 'Chinese', 94,null);
（2）实现grade更新为优秀、良好、中等、及格、不及格等方式
update transcript3 set grade=( select grade from (select sno, CASE WHEN SCORE>=90 THEN 'youxiu' WHEN SCORE>=80 and SCORE<90THEN 'lianghao' WHEN SCORE>=70 and SCORE<80 THEN 'zhongdeng' WHEN SCORE>=60 THEN 'jige' ELSE 'bujige' END grade From transcript3 ) a where transcript3.sno=a.sno);
四、复杂更新语句的使用
1、创建表
create table T1(a int ,b int ,c int ,d int ,e int); create table T2(a int ,b int ,c int ); insert into T1 values(1,2,3,4,5); insert into T1 values(10,20,3,4,5); insert into T1 values(10,20,4, 40,50); insert into T2 values( -1, -1 , 3); insert into T2 values( -2, -2, 4);
2、实现语句：
update t1 set a= (select a from t2 where t1.c= t2.c ) , b =(select b from t2 where t1.c= t2.c) where t1.c in (select c from t2);
五、分析函数的使用（ROW_NUMBER、RANK、DENSE_RANK）
以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始
ROW_NUMBER 返回连续的排序，不论值是否相等
RANK 具有相等值的行排序相同，序数随后跳跃
DENSE_RANK 具有相等值的行排序相同，序号是连续的
1、创建表 transcrpit;
create table transcript(sno number,km varchar2(15),score number); insert into transcript valudes(1,'Chinese',60); insert into transcript valudes(1,'Math',60); insert into transcript valudes(2,'English',60); insert into transcript valudes(2,'Math',70); insert into transcript valudes(3,'Chinese',80);
2、用法：
（1）row_number
SQL> select sno,km,score,row_number() over(order by score desc) row_number from transcript;
（2）rank
SQL> select sno,km,score,rank() over(order by score desc) row_number from transcript;
（3）dense_rank
SQL> select sno,km,score,dense_rank() over(order by score desc) row_number from transcript;
六、DECODE函数
1、在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。
当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。
事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。
需要注意的是，这里的if、then及else 都可以是函数或计算表达式。
2、创建表
Create table student5(id number,name varchar2(10),sex char(1)); Insert into student5 values(1, 'zhang', '1'); Insert into student5 values(2, 'wang', '2'); Insert into student5 values(3, 'li', '1');
3、SQL语句
SQL> select id,name,decode(sex,1,'male',2,'female')from student5;
4、创建表
Create table sales(month char(2),sales_tv number,sales_computer number);
Insert into sales values('01', 10, 18);
Insert into sales values('02', 28, 20);
Insert into sales values('03', 36, 33);
得到每个行中，两列值中，TV和computer中的较大值，通过decode配合两数相减sign()的值来判定，谁的值大，谁的值小
SQL> select month,decode(sign(sales_tv-sales_computer),1,sales_tv,0,sales_tv,-1,sales_computer) as maxiser from sales;
七、oracle的行列转换
1、创建表
create table sales2(product varchar2(10), jidu char(2), sale number);
insert into sales2 values('TV', '01', 100);
insert into sales2 values('TV', '02', 200);
insert into sales2 values('TV', '03', 300);
insert into sales2 values('kongtiao', '01', 50);
insert into sales2 values('kongtiao', '02', 150);
insert into sales2 values('kongtiao', '03', 180);
2、sql语句
select a.product, sum(decode(a.jidu,'01',a.sale,0)) jidu1, sum(decode(a.jidu,'02',a.sale,0)) jidu2, sum(decode(a.jidu,'03',a.sale,0)) jidu3, sum(decode(a.jidu,'04',a.sale,0)) jidu4 from sales2 a group by a.product;
行列转换：
八、ROWNUM使用
1、创建表
create table yggz bh number(6),gz number); insert into yggz values(1,1000); insert into yggz values(2,1100); insert into yggz values(3,900); insert into yggz values(4,2000); insert into yggz values(5,1500); insert into yggz values(6,3000); insert into yggz values(7,1400); insert into yggz values(8,1200);
2、rownum 作用与注意事项（与sqlserver中的top类似，oracle中没有top使用）
作用：对查询结果，输出前若干条记录
注意：只能与<、<=、between and（且between中开始必须是1）连用
输出：第3-5行
（1）用minus
select * from yggz where rownum<=5 minus select * from yggz where rownum<=2;
（2）用rownum直接取出后，嵌套，获取第3行到第5行数据
SQL> select * from (select yggz.*,rownum m from yggz) where m<=5 and m>=3;
嵌套子查询：select * from (select * from yggz); 或select a.* from (select * from yggz) a; 后面这句在SqlServer中也这样用，但第一句在SqlServer中不能使用
按照工资从高到低排序后，输出第3到第5行
（1）用minus
select * from (select * from yggz order by gz desc) where rownum <=5 minus select * from (select * from yggz order by gz desc) where rownum <=2;
（2）select * from (select a.*,rownum rn from (select * from yggz order by gz desc) a) where rn<=5 and rn >=3;
九、删除重复记录（非完全一致，或完全一致），ROWID
1、创建表
create table student6(sno number(6) , sname varchar2(10), sage int ); insert into student6 values(1, 'AA', 21); insert into student6 values(2, 'BB', 22); insert into student6 values(3, 'CC', 23); insert into student6 values(3, 'CC', 34); insert into student6 values(3, 'CC', 35); insert into student6 values(3, 'CC', 36);
2、rowid
rowid的信息有：数据库对象号、数据文件号、数据块号、行号
通过rowid查询
第一种方式：删除sno相同的行，即重复的行，通过rowID，group分组
SQL>select * from student6 where sno in(select sno from student6 group by sno having(count(*)>1)) and rowid not in(select min(rowid) from student6 group by sno having(count(*)>1));
第二种方式：自连接方式，笛卡尔
DELETE FROM student6 WHERE ROWID IN (SELECT A.ROWID FROM student6 A,student6 B WHERE A.sno=B.sno AND A.ROWID > B.ROWID);
第三种方式：嵌套查询,
十、GROUP BY GROUPING SETS的使用
可以用GROUP BY GROUPING SETS来进行分组自定义汇总，可以应用它来指定你需要的总数组合。
其格式为： GROUP BY GROUPING SETS ((list), (list) ... )
这里（list）是圆括号中的一个列序列，这个组合生成一个总数。要增加一个总和，必须增加一个(NUlL)分组集。
对于scott.emp表，如果要查询：各部门sal大于2000的员工，进行汇总，得到各部门的sal总和、以及总共的sal总和。
SELECT CASE WHEN a.deptno IS NULL THEN 'heji' WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 'xiaoji' ELSE '' || a.deptno END deptno, a.empno,a.ename,SUM(a.sal) total_sal FROM scott.emp a WHERE a.sal>2000 GROUP BY GROUPING SETS((a.deptno),(a.deptno,a.empno,a.ename),());
文章评论