SQL案例解析笔记

1、表连接的使用

1.1、交叉连接（CROSS JOIN）

分类	SQL	说明
CROSS JOIN	SELECT A.N,B.N FROM A CROSS JOIN B	以CROSS JOIN为关键字
缺WHERE条件表达式	SELECT A.N,B.N FROM A, B	没有WHERE条件表达式

1.2、内连接（INNER JOIN）

分类	SQL	说明
使用JOIN	SELECT A.N,B.N FROM A INNER JOIN B ON A.N=B.N	可将INNER JOIN简化成JOIN
不使用JOIN	SELECT A.N,B.N FROM A,B WHERE A.N=B.N	常用写法

1.3、外连接（OUTER JOIN）

Oracle可以使用“（+）”符号实现外连接，如A.Column=B.Column（+），即输出表A的所有数据。

2、子查询的使用

2.1、通用表表达式

WITH Emp as (SELECT  e.dept_id,count(*)  emp_count  FROM employeese  GROUP BY e.dept_id)

SELECT d.dept_name,e.emp_count  from departments d,emp e

WHERE d.dept_id=e.dept_id  ORDER  BY  emp_count  DESC;

3、集合的使用

限制条件：字段数量需一致电；字段类型需兼容；字段名称以第一个数据集为依据；排序需将ORDER BY置于最后。

3.1、并集（UNION）

SELECT ‘B’ AS COL UION ALL SELECT ‘A’ AS VAL;

SELECT ‘B’ AS COL UION SELECT ‘A’ AS VAL;

3.2、交集（INTERSECT）

SELECT N FROM A INTERSECT SELECT N FROM B;

3.1、差集（MINUS）

SELECT N FROM A MINUS SELECT N FROM B;

4、CASE表达式

SELECT VAL,

       CASE VAL  WHEN  NULL  THEN  1  ELSE 0 END AS  "Simple(Is Null)",

       CASE WHEN VAL IS NULL THEN 1 ELSE 0 END AS  "Search(Is Null)"

FROM (

  SELECT 1 VAL FROM DUAL UNION ALL

  SELECT 2 VAL FROM DUAL UNION ALL

  SELECT NULL VAL FROM DUAL

) D

注：对于NULL值的运算，必须是IS NULL或IS NOT NULL两种形式，脚本中简单型结果为错误。

5、DISTINCT的使用

6、特殊排序法

6.1、CASE分段排序

ORDER BY

CASE WHEN <Condition>

THEN 0 ELSE 1 END  --重要数据，以条件式转换为0，其他则1

       [,ColumnName]

6.1.1、分段同序

在同一字段中以特定值进行分段，一段在前，一段在后，同时升序或降序。

SELECT * FROM SCOTT.EMP T

ORDER BY CASE WHEN EMPNO>=7900  THEN0 ELSE 1 END,EMPNO;

6.1.2、分段反序

在同一字段中以特定值进行分段，一段升序，一段降序。

SELECT * FROM SCOTT.EMP T

ORDER BY CASE WHEN EMPNO>=7900  THEN1 ELSE 0 END,

CASE WHEN  EMPNO>=7900  THEN0-EMPNOELSE  EMPNO END;--针对数值

SELECT * FROM SCOTT.EMP T ORDER BY

  CASE WHEN SUBSTR(ENAME,0,1)>'C'  THEN 1 ELSE 0 END,

  CASE WHEN SUBSTR(ENAME,0,1)<='C'  THEN ROW_NUMBER()  OVER(ORDERBY ENAME DESC)

  ELSE ROW_NUMBER()  OVER(ORDERBY ENAME ASC)  END;--针对字符

6.2、NULL排序

6.3、字符串位置排序

SELECT A.*

  FROM (SELECT '春天'  SEASON FROM DUAL UNION

        SELECT '夏天'   FROM DUALUNION

        SELECT '秋天'   FROM DUAL UNION

        SELECT '冬天'   FROM DUAL) A

 ORDER BY INSTR('春天夏天秋天冬天',SEASON)

7、SEQUENCE的使用

CREATE SEQUENCEEVENT_LOG_SEQ

       START WHIT1    --从多少开始

       INCREMENT  BY1   --每次增加

       MINVALUE  1      --最小值

       MAXVALUE  1      --最大值

       NOCACHE         --不缓存

       NOCYCLE         --当取至最大值后，是否循环再由最小值开始

8、表连接更新数据

方法	SQL	说明
SUBQUERY (子查询)	CREATE TABLE  SCOTT.EMP2 AS SELECT T.*  FROM SCOTT.EMP T WHERE DEPTNO=20; UPDATE SCOTT.EMP2 SET SAL=SAL*1.5,DEPTNO=40 WHERE DEPTNO=20; --SELECT* FROM SCOTT.EMP2 T;   UPDATE SCOTT.EMP A SET (SAL,DEPTNO)= (SELECT SAL,DEPTNO FROM SCOTT.EMP2 B    WHERE A.EMPNO=B.EMPNO) WHERE A.EMPNO= (SELECT EMPNO FROM SCOTT.EMP2 B    WHERE  A.EMPNO=B.EMPNO);	使用JOIN方法时，请留意WHERE子句的使用。 当省略WHERE子句时， Oracle将默认把所有的值清空(NULL)
INLINE-VIEW                     (虚拟表)	ALTER TABLE  SCOTT.EMP2 ADD PRIMARY KEY(EMPNO); UPDATE (SELECT A.SAL, B.SAL AS SAL2, A.DEPTNO,B.DEPTNO  AS DEPTNO2 FROM SCOTT.EMP A,SCOTT.EMP2B WHERE A.EMPNO=B.EMPNO)  --虚拟表 SET SAL=SAL2,DEPTNO=DEPTNO2; --SELECT T.* FROM SCOTT.EMP T;	EMP2数据表必须有主键(PK)， 否则将发生ORA-01779错误

9、删除重复数据

DROP TABLE  scott.emp2;

create table  scott.emp2 as select t.*   from SCOTT.EMP t  where  deptno=20;

INSERT INTO  SCOTT.EMP2 VALUES (7370,    'SMITH',     'CLERK',     7902, DAte'1980-12-17',  800.00,      '',   20);

INSERT INTO  SCOTT.EMP2 VALUES (7371,    'SMITH',     'CLERK',     7902, DAte'1980-12-17',  800.00,      '',   20);

INSERT INTO  SCOTT.EMP2 VALUES (7903,    'FORD',      'CLERK',     7902, DAte'1980-12-17',  800.00,      '',   20);

INSERT INTO  SCOTT.EMP2 VALUES (7904,    'FORD',      'CLERK',     7902, DAte'1980-12-17',  800.00,      '',   20);

INSERT INTO  SCOTT.EMP2 VALUES (7567,    'JONES',     'CLERK',     7902, DAte'1980-12-17',  800.00,      '',   20);

select * from SCOTT.emp2 t;

JOIN	NOT IN
DELETE SCOTT.EMP2 A WHERE A.ROWID>(SELECT  MIN(B.ROWID)                 FROM SCOTT.EMP2 B WHERE B.ENAME=A.ENAME);	DELETE SCOTT.EMP2 A WHERE A.ROWID NOT IN (SELECT MIN(B.ROWID)                     FROM SCOTT.EMP2 B GROUP BY B.ENAME);

 10、清空表数据

DELETE <table_name>;

  数据删除时会将还原数据写到事务日志上，然后再进行删除，因而执行上会额外耗费一些时间。

TRUNCATE TABLE <table_name>;

  迅速清除数据，此命令具有不可恢复的特性，使用时请慎重。

11、日期函数

--定义格式

select to_char(sysdate,'YYYY"年"MM"月"DD"日"')  from dual;

--日期加减运算

select base as"当前时间",

       base + interval '1-2' year to month as  "1年2月后",

       base + interval '1' year as  "1年后",

       base + interval '1' month as  "1月后",

       base + interval '1' day as  "1天后",

       base + interval '1' hour as  "1小时后",

       base + interval '1' minute as "10分钟后",

       base + interval '1' second as "10秒后",

       base + interval '10:30' minute(3)  tosecond as  "10分30秒后",

       base + interval '21:10:30' day to second as "2天1小时10分30秒后",

       base as  "当前时间"  from(selectsysdate as base  from dual);

 

--截断函数

select trunc(base)  as "YYYY-MM-DD",

       trunc(base,'YY')  as "当年1月1号",

       trunc(base,'MM')  as "当月1号",

       trunc(base,'HH24')  as "整点",

       last_day(base)  as "当月底",

       last_day(base)+1  as "下月1号"  from(select  sysdateas base from dual);

select base,

       round(base) "round",--四舍五入，12点进位一天,时间归零

       trunc(base)"trunc"--时间归零

       from (select date'2010-03-04'+10/24  as base  from dual  union

             select date'2010-03-04'+14/24  from dual);

 

12、行列转换

SELECT T.DEPTNO,ENAME,

       CASE WHEN T.DEPTNO=10  THEN SAL  END  "SAL10",

       CASE WHEN T.DEPTNO=20  THEN SAL  END  "SAL20",

       CASE WHEN T.DEPTNO=30  THEN SAL  END  "SAL30"

 FROM SCOTT.EMP T ORDER BY T.DEPTNO;

SELECT JOB,FLDIDX,

       CASE WHEN FLDIDX=1  THEN ENAME  END FLD1,

       CASE WHEN FLDIDX=2  THEN ENAME  END FLD2,

       CASE WHEN FLDIDX=3  THEN ENAME  END FLD3

  FROM (SELECT T.JOB,T.ENAME,

       ROW_NUMBER()  OVER(PARTITION  BY JOB ORDER BY ENAME) FLDIDX

       FROM  SCOTT.EMP T);

SELECT JOB,

       max(CASE  WHEN FLDIDX=1  THEN  ENAME  END) FLD1,

       max(CASE  WHEN FLDIDX=2  THEN  ENAME  END) FLD2,

       max(CASE  WHEN FLDIDX=3  THEN  ENAME  END) FLD3

  FROM (SELECT T.JOB,T.ENAME,

       ROW_NUMBER()  OVER(PARTITION  BY JOB ORDER BY ENAME) FLDIDX

       FROM  SCOTT.EMP T)

 GROUP BY JOB;

13、存储过程

存储过程按照是否有返回值可分成程序（Procedure）及函数（Function）两大类。

13.1、程序

CREATE [OR REPLACE] PROCEDURE [SCHEMA.]PROCEDURE_NAME

[(

ARGUMENT [IN | OUT | IN OUT] DATATYPE

[,ARGUMENT [IN | OUT | IN OUT] DATATYPE] ...

)]/*声明自变量，若无自变量时请去除括号，否则将发生错误*/

{IS|AS}

/*声明断落*/  

BEGIN

/*执行断落*/

EXCEPTION

/*异常处理*/

END [PROCEDURE_NAME];                       

实例：

13.2、函数

CREATE [ORREPLACE]  FUNCTION   [SCHEMA.]FUNCTION_NAME

[(

[ARGUMENT [IN  |  OUT  |  INOUT] DATATYPE

[,ARGUMENT [IN  |  OUT  |  INOUT] DATATYPE] ...

)] RETURN DATATYPE

{IS  |  AS}

/*声明断落*/  

BEGIN

/*执行断落*/

RETURN  VALUE;

EXCEPTION

/*异常处理*/

END [FUNCTION_NAME];                       

实例：

CREATE  OR  REPLACE  FUNCTION func_OrderQty

(

OrderDate DATE,

Days NUMBER:=1

) RETURN  number

AS

    /*宣告段落*/

    Qty number;

BEGIN

    /*執行段落*/

    SELECT  COUNT  (*)  INTO Qty FROM Orders

    WHERE Order_Date>= OrderDate  AND Order_Date<OrderDate+Days;

    RETURN(Qty);

END;

13.3、触发器

CREATE [ORREPLACE]  TRIGGER [SCHEMA.]TRIGGER_NAME

{BEFORE   |  AFTER}

{[INSERT] [OR] [UPDATE] [OR] [DELETE]}  ON  <TABLE_NAME>

[FOR  EACH  ROW]

        [WHEN_CLAUSE]

DECLARE

BEGIN

END;

判断触发器由何种DML命令触发

函数	返回
IF INSERTING  THEN          ... END IF;	如果触发器命令是INSERT，则为TRUE
IF UPDATING  THEN ... END IF;	如果触发器命令是UPDATE，则为TRUE
IF DELETING  THEN ... END IF;	如果触发器命令是DELETE，则为TRUE

13.4、游标

声明DECLEAR	CURSOR cursor_name [( argument datatype [,argument datatype] ... )] IS select_statement;
开户OPEN	OPEN cursor_name; OPEN cursor_name(argument);
提取FETCH	LOOP FETCH cursor_name  INTOvariable; EXIT WHEN cursor_name%NOTFOUND;                        ... END LOOP;
关闭CLOSE	CLOSE cursor_name;

14、异质数据源

 

15、IN-LIST的使用

IN的上限为1000，可使用其他函数

--TABLE函数

CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767);

SELECT COLUMN_VALUE FROM TABLE(SPLIT_TBL(1,2,3,'A','B','C'));

SELECT C.LOT_NO,TO_CHAR(C.GOOD_DIE/C.GROSS_DIE*100,'990.00') YIELD

  FROM CP_LOT_SUM C,TABLE(SPLIT_TBL('XXX814000','YXG018000','ZKV979000')) L

 WHERE C.LOT_NO=L.COLUMN_VALUE;

--MEMEBEROF

CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767);

SELECT C.LOT_NO,TO_CHAR(C.GOOD_DIE/C.GROSS_DIE*100,'990.00') YIELD

  FROM CP_LOT_SUM C

 WHERE C.LOT_NO MEMBER OF SPLIT_TBL('XXX814000','YXG018000','ZKV979000');

16、序列与随机数

--产生数值序列

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=5;

--产生日期序列

SELECT TRUNC(SYSDATE,'MM')+LEVEL-1 CALENDAR_DATE FROM DUAL

CONNECT BY LEVEL<=TO_CHAR(LAST_DAY(SYSDATE),'DD');

--随机字符（密码、验证码）

SELECT DBMS_RANDOM.STRING('U',5)"5个大写字母",

       DBMS_RANDOM.STRING('L',5)"5个小写字母",

       DBMS_RANDOM.STRING('A',5)"5个大小写字母",

       DBMS_RANDOM.STRING('X',5)"5个大写字母及数字",

       DBMS_RANDOM.STRING('P',5)"5个可视字符"

  FROM DUAL CONNECT BY LEVEL<=5;

--随机数值

SELECT DBMS_RANDOM.VALUE,--产生0.0至1.0(不含)浮点数值

       DBMS_RANDOM.VALUE(0,11),--产生low至high(不含)浮点数值

       TRUNC(DBMS_RANDOM.VALUE(1,11))--产生1至10整数值

  FROM DUAL CONNECT BY LEVEL<=5;

--随机抽取三名员工

SELECT  *  FROM

(SELECT  *  FROM  SCOTT.EMP  ORDER BY  DBMS_RANDOM.VALUE())  WHERE  ROWNUM<=3;