SQL Cookbook

PS:SQL语句基于Oracle 11g,使用账户scott，操作表为emp和dept表。

一、检索数据

1.查找满足多个条件的行：

1 SELECT
2     *
3 FROM
4     emp
5 WHERE
6     DEPTNO = 10
7 OR comm IS NOT NULL
8 OR sal <= 2000
9 AND DEPTNO = 20;

其返回部门10中所有员工+所有得到提成的员工+部门20中工资不超过2000美金的员工。

 1 SELECT
 2     *
 3 FROM
 4     emp
 5 WHERE
 6     (
 7         deptno = 10
 8         OR comm IS NOT NULL
 9         OR sal <= 2000
10     )
11 AND DEPTNO = 20;

其仅仅返回部门20中符合条件的员工

 

2.在where子句中引用取别名的列：

 1 SELECT
 2     *
 3 FROM
 4     (
 5         SELECT
 6             sal AS salary,
 7             comm AS commision
 8         FROM
 9             emp
10     ) x
11 WHERE
12     salary < 5000;

 

3.连接列值：

1 SELECT
2     ename || ' WORKS AS A ' || JOB
3 FROM
4     emp
5 WHERE
6     deptno = 10;

 

4.在select语句中使用条件逻辑：

 1 SELECT
 2     ename,
 3     sal,
 4     CASE
 5 WHEN sal <= 2000 THEN
 6     'UNDERPAID'
 7 WHEN sal >= 4000 THEN
 8     'OVERPAID'
 9 ELSE
10     'OK'
11 END AS status
12 FROM
13     emp;

 

5.限制返回行数：

1 SELECT
2     *
3 FROM
4     emp
5 WHERE
6     ROWNUM <= 5;

Oracle中使用ROWNUM函数来得到每行的行号，但是ROWNUM=5返回为空；ROWNUM=1返回第1行。

 

6.从表中随机返回n条记录：

 1 SELECT
 2     *
 3 FROM
 4     (
 5         SELECT
 6             ename,
 7             JOB
 8         FROM
 9             emp
10         ORDER BY
11             dbms_random.VALUE()
12     )
13 WHERE
14     ROWNUM <= 5;

 

7.为每行数据加个id:

 1 SELECT ROWNUM AS ID, E .* FROM emp E; 

 

二、查询结果排序

1.按子串最后两个字符排序：

1 SELECT
2     ename,
3     JOB
4 FROM
5     emp
6 ORDER BY
7     "SUBSTR" (JOB, LENGTH("JOB") - 2);

 

2.对于一个字母数字混合的列，分别按字母或数字进行排序：

(1)使用sys登录，给scott用户授权创建视图的权力：

 1 GRANT CREATE VIEW TO scott; 

(2)scott用户创建一个字母数字混合列的视图：

1 CREATE VIEW v AS SELECT
2     ename || ' ' || deptno AS DATA
3 FROM
4     emp;

(3)使用replace和translate函数解决这个问题：

replace()函数：字符串级别的替换

translate()函数：字符级别的替换

 1>按照字母排序：

首先使用translate()函数，将所有的数字替换成#号，得到字符串1。然后使用replace()函数将字符串1中的#消除，得到字符串2。最后再使用replace()函数将混列列中的字母消除，仅仅剩下数字。

 1 SELECT
 2     DATA
 3 FROM
 4     v
 5 ORDER BY
 6     "REPLACE" (
 7         DATA,
 8         "REPLACE" (
 9             "TRANSLATE" (
10                 DATA,
11                 '0123456789',
12                 '##########'
13             ),
14             '#',
15             ''
16         ),
17         ''
18     );

2>按照字母排序：

首先使用translate()函数将所有数字替换成#号，得到字符串1。接着使用replace()函数将所有的#号消除即可。

 1 SELECT
 2     DATA
 3 FROM
 4     v
 5 ORDER BY
 6     "REPLACE" (
 7         "TRANSLATE" (
 8             DATA,
 9             '0123456789',
10             '##########'
11         ),
12         '#',
13         ''
14     );

 

3.处理排序空值：

Oracle 11g提供了解决方法。一般需要使用一个附加的列来进行排序

(1)空值排在最后

1 SELECT
2     ename,
3     sal,
4     comm
5 FROM
6     emp
7 ORDER BY
8     comm NULLS LAST;

 

(2)空值排在最前

1 SELECT
2     ename,
3     sal,
4     comm
5 FROM
6     emp
7 ORDER BY
8     comm NULLS FIRST;

 

4.根据条件选项进行排序：

 1 SELECT
 2     ename,
 3     sal,
 4     JOB,
 5     comm
 6 FROM
 7     emp
 8 ORDER BY
 9     CASE
10 WHEN JOB = 'SALESMAN' THEN
11     comm
12 ELSE
13     sal
14 END;

 

三、操作多个表

1.多个表的数据集的叠加：

 1 SELECT
 2     ename AS ename_and_dname,
 3     deptno
 4 FROM
 5     emp
 6 WHERE
 7     DEPTNO = 10
 8 UNION ALL
 9     SELECT
10         '---------------------------',
11         NULL
12     FROM
13         dual
14     UNION ALL
15         SELECT
16             dname AS ename_and_dname,
17             deptno
18         FROM
19             dept;

 union all要求select列表的项目数和对应项目的数据类型必须要匹配。union和union all区别为：union筛选掉重复项，而union all包含所有项。

 

2.在一个表中查找其他表不匹配的记录：

1 SELECT
2     D .*
3 FROM
4     dept D,
5     emp E
6 WHERE
7     D .DEPTNO = E .DEPTNO (+)
8 AND E .DEPTNO IS NULL;

此为查找没有职员的部门。

 

3.在运算和比较时使用NULL值：

 1 SELECT
 2     ename,
 3     comm
 4 FROM
 5     emp
 6 WHERE
 7     "COALESCE" (comm, 0) < (
 8         SELECT
 9             comm
10         FROM
11             emp
12         WHERE
13             ename = 'WARD'
14     );

 

四、插入、更新与删除

1.插入默认值：

(1)有这样一张表：

1 CREATE TABLE D (
2     ID INTEGER DEFAULT 0,
3     foo VARCHAR (10)
4 );

(2)在这样的表中插入一行：

1 INSERT INTO D (ID, foo)
2 VALUES
3     (NULL, 'foo');

注意：对于默认值，需要指定为NULL值的。

 

2.从一个表向另外的表中复制行：

1 INSERT INTO dept_east (deptno, dname, loc) SELECT
2     deptno,
3     dname,
4     loc
5 FROM
6     DEPT
7 WHERE
8     loc IN ('NEW YORK', 'BOSTON');

 

3.一次向多个表中插入记录：

 1 INSERT ALL
 2 WHEN loc IN ('NEW YORK', 'BOSTON') THEN
 3     INTO dept_east (deptno, dname, loc)
 4 VALUES
 5     (deptno, dname, loc)
 6 WHEN loc IN ('CHICAGO') THEN
 7     INTO dept_mid (deptno, dname, loc)
 8 VALUES
 9     (deptno, dname, loc)
10 ELSE
11     INTO dept_west (deptno, dname, loc)
12 VALUES
13     (deptno, dname, loc) SELECT
14         deptno,
15         dname,
16         loc
17     FROM
18         dept;

 

4.合并记录：

 1 MERGE INTO emp_commission ec USING (SELECT * FROM emp) emp ON (ec.empno = EMP.empno)
 2 WHEN MATCHED THEN
 3     UPDATE
 4 SET ec.comm = 1000 DELETE
 5 WHERE
 6     (sal < 2000)
 7 WHEN NOT MATCHED THEN
 8     INSERT (
 9         ec.empno,
10         ec.ename,
11         ec.deptno,
12         ec.comm
13     )
14 VALUES
15     (
16         EMP.empno,
17         EMP.ename,
18         EMP.deptno,
19         emo.comm
20     );

 

5.删除重复记录：

(1)建立如下的表，并且插入数据：

 1 CREATE TABLE dupes (ID INTEGER, NAME VARCHAR(10));
 2 
 3 INSERT INTO dupes
 4 VALUES
 5     (1, 'NAPOLEON');
 6 
 7 INSERT INTO dupes
 8 VALUES
 9     (2, 'DYNAMITE');
10 
11 INSERT INTO dupes
12 VALUES
13     (3, 'DYNAMITE');
14 
15 INSERT INTO dupes
16 VALUES
17     (4, 'SHE SELLS');
18 
19 INSERT INTO dupes
20 VALUES
21     (5, 'SEA SHELLS');
22 
23 INSERT INTO dupes
24 VALUES
25     (6, 'SEA SHELLS');
26 
27 INSERT INTO dupes
28 VALUES
29     (7, 'SEA SHELLS');

(2)删除重复的姓名组，只保留一个ID：

 1 DELETE
 2 FROM
 3     dupes
 4 WHERE
 5     ID NOT IN (
 6         SELECT
 7             MIN (ID)
 8         FROM
 9             dupes
10         GROUP BY
11             NAME
12     );

 

五、元数据查询

1.列出模式中的表：

 1 SELECT table_name FROM all_tables; 

 

2.列出表的列：

1 SELECT
2     column_name,
3     data_type,
4     column_id
5 FROM
6     all_tab_columns
7 WHERE
8     OWNER = 'SCOTT';

 

3.列出表的索引：

 1 SELECT
 2     table_name,
 3     index_name,
 4     column_name,
 5     column_position
 6 FROM
 7     SYS.ALL_IND_COLUMNS
 8 WHERE
 9     table_name = 'EMP'
10 AND table_owner = 'SCOTT';

 

4.列出表的约束：

 1 SELECT
 2     A .table_name,
 3     A .constraint_name,
 4     b.column_name,
 5     A .constraint_type
 6 FROM
 7     all_constraints A,
 8     all_cons_columns b
 9 WHERE
10     A .table_name = 'EMP'
11 AND A . OWNER = 'SCOTT'
12 AND A .table_name = b.table_name
13 AND A . OWNER = b. OWNER
14 AND A .constraint_name = b.constraint_name;

 

5.列出没有相应索引的外键：

 1 SELECT
 2     A .table_name,
 3     A .constraint_name,
 4     A .column_name,
 5     c.index_name
 6 FROM
 7     all_cons_columns A,
 8     all_constraints b,
 9     all_ind_columns c
10 WHERE
11     A .table_name = 'EMP'
12 AND A . OWNER = 'SCOTT'
13 AND b.constraint_type = 'R'
14 AND A . OWNER = b. OWNER
15 AND A .table_name = b.table_name
16 AND A .constraint_name = b.constraint_name
17 AND A . OWNER = c.table_owner (+)
18 AND A .table_name = c.table_name (+)
19 AND A .column_name = c.column_name (+)
20 AND c.index_name IS NULL;

 

六、使用字符串

1.遍历字符串

将表EMP中的ENAME值为"KING"的字符串显示为4行，每行都包含一个字母。

(1)创建表T10，并且插入数据：

 1 CREATE TABLE T10 (ID INT);
 2 INSERT INTO T10
 3 VALUES
 4     (1);
 5 
 6 INSERT INTO T10
 7 VALUES
 8     (2);
 9 
10 INSERT INTO T10
11 VALUES
12     (3);
13 
14 INSERT INTO T10
15 VALUES
16     (4);
17 
18 INSERT INTO T10
19 VALUES
20     (5);
21 
22 INSERT INTO T10
23 VALUES
24     (6);
25 
26 INSERT INTO T10
27 VALUES
28     (7);
29 
30 INSERT INTO T10
31 VALUES
32     (8);
33 
34 INSERT INTO T10
35 VALUES
36     (9);
37 
38 INSERT INTO T10
39 VALUES
40     (10);

(2)实现功能：

 1 SELECT
 2     "SUBSTR" (E .ename, iter.pos, 1) AS C
 3 FROM
 4     (
 5         SELECT
 6             ename
 7         FROM
 8             emp
 9         WHERE
10             ename = 'KING'
11     ) E,
12     (SELECT ID AS pos FROM t10) iter
13 WHERE
14     iter.pos <= LENGTH (E .ename);

原理：先选出10个'KING"值，再从中选出‘KING’长度1-4个来，最后利用迭代器对每个位置的字符截取就可得到结果。

 

2.将字符和数字数据分离：

 1 SELECT
 2     REPLACE (
 3         "TRANSLATE" (
 4             DATA,
 5             '0123456789',
 6             '0000000000'
 7         ),
 8         '0'
 9     ) ename,
10     REPLACE (
11         "TRANSLATE" (
12             LOWER (DATA),
13             'abcdefghijklmnopqrstuvwxyz',
14             RPAD ('z', 26, 'z')
15         ),
16         'z'
17     ) sal
18 FROM
19     (
20         SELECT
21             ename || sal DATA
22         FROM
23             emp
24     );

 

3.去除非字母数字数据的记录：

(1)创建视图：

 1 CREATE VIEW v1 AS SELECT
 2     ename AS DATA
 3 FROM
 4     emp
 5 WHERE
 6     deptno = 10
 7 UNION ALL
 8     SELECT
 9         ename || ', $' || CAST (sal AS CHAR(4)) || '.00' AS DATA
10     FROM
11         emp
12     WHERE
13         deptno = 20
14     UNION ALL
15         SELECT
16             ename || deptno AS DATA
17         FROM
18             emp
19         WHERE
20             deptno = 30;

(2)实现功能：

 1 SELECT
 2     DATA
 3 FROM
 4     v1
 5 WHERE
 6     "TRANSLATE" (
 7         LOWER (DATA),
 8         '0123456789abcdefghijklmnopqrstuvwxyz',
 9         RPAD ('a', 36, 'a')
10     ) = RPAD ('a', LENGTH(DATA), 'a');

 

4.按字符串中的数值排序：

(1)创建视图：

1 CREATE VIEW v2 AS SELECT
2     E .ename || ' ' || CAST (E .empno AS CHAR(4)) || ' ' || D .dname AS DATA
3 FROM
4     emp E,
5     dept D
6 WHERE
7     E .deptno = D .deptno;

(2)实现功能：

 1 SELECT
 2     DATA
 3 FROM
 4     v
 5 ORDER BY
 6     "TO_NUMBER" (
 7         "REPLACE" (
 8             "TRANSLATE" (
 9                 DATA,
10                 "REPLACE" (
11                     "TRANSLATE" (
12                         DATA,
13                         '0123456789',
14                         '##########'
15                     ),
16                     '#',
17                     ''
18                 ),
19                 RPAD ('#', 20, '#')
20             ),
21             '#',
22             ''
23         )
24     );

首先利用translate()函数将数字替换成#号，然后，利用replace()行将#号删除，得到仅剩字母的串。再通过translate()函数将原串中于仅剩字母串中相同的替换成#号，在用replace()删除#号，则所得的串仅仅剩下数字，在用to_number()函数将数字串变成数字。

注意：此处不能先将非数字变成#号，然后删除#号仅仅剩下数字。因为串中非数字类型不一定只有字母，可能有特殊符号，希腊文，什么的。

 

5.将表中行序号相同的行合成一行：

(1)有如下的表：

1 SELECT
2     deptno,
3     ename
4 FROM
5     emp
6 ORDER BY
7     1;

想要将deptno相同的行，合成一行，ename包含所有deptno相同的数据。如：10 clark;10 king;10 miller;-》10 clark,king,miller

(2)实现功能：

要实现功能，需要使用到Oracle中的ltrim()函数，sys_connect_by_path()和row_number()函数。ltrim(s1,s2)函数保证s1的第一个字符不出现在s2中。sys_connect_by_path(s1,s2)函数，将父节点s1下所有子节点按s2连接成一列。row_number()函数返回结果集中行的序列号，从1开始。

 1 SELECT
 2     deptno,
 3     LTRIM (
 4         SYS_CONNECT_BY_PATH (ename, ','),
 5         ','
 6     ) ename
 7 FROM
 8     (
 9         SELECT
10             deptno,
11             ename,
12             ROW_NUMBER () OVER (
13                 PARTITION BY deptno
14                 ORDER BY
15                     empno
16             ) rn,
17             COUNT (*) OVER (PARTITION BY deptno) cnt
18         FROM
19             emp
20     )
21 WHERE
22     LEVEL = cnt START WITH rn = 1 CONNECT BY PRIOR deptno = deptno
23 AND PRIOR rn = rn - 1;

首先，子查询中：

 1 SELECT
 2     deptno,
 3     ename,
 4     ROW_NUMBER () OVER (
 5         PARTITION BY deptno
 6         ORDER BY
 7             empno
 8     ) rn,
 9     COUNT (*) OVER (PARTITION BY deptno) cnt
10 FROM
11     emp;

找出deptno,ename,同时rn列统计出每个deptno的序号，cnt统计出每个deptno出现的数目。序号rn的目的是用来作为遍历树。

然后，在外层查询中，设置遍历树的层数为cnt,开始的数为1，要求deptno相同。rn小的是rn大的父行，在用sys_connect_by_name将所有ename和逗号连接起来，最后，去除第一个逗号即可。

 

6.按照字母顺序重新组成字符串：

 1 SELECT
 2     old_name,
 3     "REPLACE" (
 4         SYS_CONNECT_BY_PATH (c, ' '),
 5         ' ',
 6         ''
 7     ) new_name
 8 FROM
 9     (
10         SELECT
11             E .ename old_name,
12             "SUBSTR" (E .ename, iter.pos, 1) c,
13             ROW_NUMBER () OVER (
14                 PARTITION BY E .ename
15                 ORDER BY
16                     SUBSTR (E .ename, iter.pos, 1)
17             ) rn,
18             COUNT (*) OVER (PARTITION BY E .ename) cnt
19         FROM
20             emp E,
21             (SELECT ROWNUM pos FROM emp) iter
22         WHERE
23             iter.pos <= LENGTH (E .ename)
24         ORDER BY
25             1
26     ) x
27 WHERE
28     LEVEL = cnt START WITH rn = 1 CONNECT BY PRIOR rn = rn - 1
29 AND PRIOR old_name = old_name;

此处用到了三层select(比原作者少一层)，最内部的select选出行号作为iter迭代器，中间曾的select对每个ename中的字母拆分，排序，统计出rn序号，以便建立遍历树，最外层的select将每个字母按序号组合即可。

 

7.判别可以作为数值的字符串

(1)创建视图：

 1 CREATE VIEW v AS SELECT
 2     "REPLACE" (mixed, ' ', '') AS mixed
 3 FROM
 4     (
 5         SELECT
 6             "SUBSTR" (ename, 1, 2) || CAST (deptno AS CHAR(4)) || SUBSTR (ename, 3, 2) AS mixed
 7         FROM
 8             EMP
 9         WHERE
10             deptno = 10
11         UNION ALL
12             SELECT
13                 CAST (empno AS CHAR(4)) AS mixed
14             FROM
15                 EMP
16             WHERE
17                 deptno = 20
18             UNION ALL
19                 SELECT
20                     ename AS mixed
21                 FROM
22                     EMP
23                 WHERE
24                     deptno = 30
25     ) x;

(2)实现功能：

 1 SELECT
 2     TO_NUMBER (
 3         CASE
 4         WHEN "REPLACE" (
 5             "TRANSLATE" (
 6                 mixed,
 7                 '0123456789',
 8                 '9999999999'
 9             ),
10             '9',
11             ''
12         ) IS NOT NULL THEN
13             "REPLACE" (
14                 "TRANSLATE" (
15                     mixed,
16                     "REPLACE" (
17                         "TRANSLATE" (
18                             mixed,
19                             '0123456789',
20                             '9999999999'
21                         ),
22                         '9',
23                         ''
24                     ),
25                     "RPAD" ('#', LENGTH(mixed), '#')
26                 ),
27                 '#',
28                 ''
29             )
30         ELSE
31             mixed
32         END
33     ) mixed
34 FROM
35     v
36 WHERE
37     "INSTR" (
38         "TRANSLATE" (
39             mixed,
40             '0123456789',
41             '9999999999'
42         ),
43         '9'
44     ) > 0;

首先使用translate()和replace()函数将数字删除，然后利用translate函数将串中数字替换成‘#’，最后删除'#'号仅剩下数字，转化为数字即可。其中where中instr()函数用来查找是否有数字。

 

posted @ 2015-05-20 19:11  XavierJZhang  阅读(378)  评论(0)  编辑  收藏  举报