007 SG SQL中的注意01

最新推荐文章于 2025-12-24 19:00:25 发布

原创最新推荐文章于 2025-12-24 19:00:25 发布 · 594 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #join #date #function #table #null

Oracle 专栏收录该内容

19 篇文章

订阅专栏

本文深入探讨了SQL查询的细节，包括别名、特殊符号处理、模式匹配、运算符优先级、ORDER BY、单行函数、日期操作、转换函数、连接类型以及分组函数的应用。通过实例解析，帮助读者掌握SQL查询的高级特性。

1. 起别名时,使用到特殊符号,例如 # , 要加双引号 " "

SELECT WORK_DATE "#"

FROM ZHPPEXP05

WHERE WORK_DATE = '20080104'

2.DISTINCT 关键字要放在查询的第一个

    SELECT DISTINCT WORK_DATE ,
                     SEQ
       FROM   ZHPPEXP05
     WHERE WORK_DATE = '20080104'

3.模式匹配中的escape

    SELECT *
    FROM   ZHPPEXP05
    WHERE WORK_DATE = '20080104'
    AND BUYER2 LIKE 'XAA%\_' ESCAPE '\'

    SELECT *
    FROM   ZHPPEXP05
    WHERE WORK_DATE = '20080104'
    AND BUYER2 LIKE 'XAA\_%' ESCAPE '\'

    BUYER2 = 'XAA_CHICAGO'

第一个SQL语句将%放在前, 表示查询 XAA******_ 这样的字符串 , 其中的 _ 已经不是通配符 , 而是实实在在的_ , 因为使用了 ESCAPE .

第二个SQL语句将%放在后, 表示查询 XAA_***** 这样的字符串 , 其中的_ 也不是通配符号 , 而是实实在在的_ , 因为使用了 ESCAPE .

由于 BUYER2 为 'XAA_CHICAGO' , 所以第一个查询无结果 , 第二个查询有结果 .

4.运算符优先级别

1	算术运算符
2	连接运算符
3	比较运算符
4	IS [ NOT ] NULL , LIKE
5	[ NOT ] BETWEEN
6	NOT 逻辑运算符
7	AND 逻辑运算符
8	OR 逻辑运算符

一定注意括号 ( ) 的使用 .

5. ORDER BY

order by 中可以使用别名 , 运算号 , 返回单值的函数( single row function ) 等等

    SELECT *
    FROM   ZHPPEXP05
    WHERE WORK_DATE = '20080104'
    ORDER BY PLANGIQTY1 + 1000

6.single row function

charactor : LOWER , UPPER , INITCAP , CONCAT , SUBSTR , LENGTH , INSTR , LPAD | RPAD , TRIM , REPLACE

number : ROUND , TRUNC , MOD

date : oracle database stores dates in an internal numeric format : century , year , month , day , hours , minutes , seconds .

: 计算date :

A date + | - number = date ( + | - days )

SELECT SYSDATE - 1 可以换成 + 1
FROM DUAL

返回的结果是相同时间的前一天( - ) , 或者是后一天 ( + )

B date + | - 1/24 = date (+ | - hours )

SELECT SYSDATE - 1/24 可以换成 + 1/24
FROM DUAL

返回的结果是相差的小时的date类型

C date - date = number ( 只能做减法运算 , 加法不合理 )

SELECT SYSDATE - (SYSDATE - 1)
FROM DUAL

返回的结果是相差的天数

MONTHS_BETWEEN , ADD_MONTHS , NEXT_DAY , LAST_DAY ,ROUND ,TRUNC

                          SELECT ROUND(SYSDATE,'MONTH') FROM DUAL
返回结果 : 2012/02/01

                          SELECT ROUND(ADD_MONTHS(SYSDATE,6),'YEAR') FROM DUAL
                          返回结果 : 2013/01/01

                          SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL
                          返回结果 : 2012/01/01

                          SELECT TRUNC(ADD_MONTHS(SYSDATE,6),'YEAR') FROM DUAL
                          返回结果 : 2012/01/01

conversion function :

Implicit conversion ( 隐式转换自动 )

varchar or char	number
varchar or char	date
number	varchar2
date	varchar2

这些隐式转换是有条件的, 例如char 类型中只包含类似 '123' 这样的串才能进行隐式转换 , 条件苛刻 .

Explicit conversion ( 显式转换 )

General function : these functions can be used in any data type and pertain to using null value .

NVL , NVL2 , NULLIF , COALESCE

NVL( exp1, exp2 ) 如果 exp1 为 NULL , 返回 exp2

NVL2( exp1, exp2 , exp3 ) 如果 exp1 不是 null , 返回 exp2 否则返回 exp3

NULLIF( exp1 ,exp2 ) 比较 exp1 , exp2 , 如果相等, 返回 null , 否则返回 exp1

COALESCE( exp1, exp2 , exp3, ......) 返回第一个不是null的参数 expN

case , decode( if - then - else ) logistic function

    SELECT WORK_DATE,
       SEQ,
       CASE VBELN
       WHEN '0000029331' THEN 'SO 01'
       WHEN '0000029335' THEN 'SO 02'
       WHEN '0000029341' THEN 'SO 03'
       ELSE 'SO OTHERS'
       END TEST_CASE
    FROM ZHPPEXP05
    WHERE WORK_DATE = '20080104'

    SELECT WORK_DATE,
       SEQ,
       DECODE ( VBELN , --> 这个是 exp | column

                                            '0000029331' , 'SO 01' ,
                                            '0000029335' , 'SO 02' ,
                                            '0000029341' , 'SO 03' ,
                            --> 这个是 default     'SO OTHERS' )TEST_DECODE
       FROM ZHPPEXP05
       WHERE WORK_DATE = '20080104'

其中decode函数, 语法为 decode( exp1 | colunm ,

search1 , result1 ,

search2 , result2,

[ defalut ] )别名

注意 decode 函数的格式 , 阅读清楚 .

7.连接的类型

oracle : Equijoin , Nonequijoin , outer join , self join

sql : cross join , Natural join , Using clause , Full or two sided outer join , Arbitrary join conditions for outer join . ( Arbitrary = ON )

ORACLE 标准

Equijoin : select a.* from a , b where a.id = b.id

    Nonequijoin : SELECT e.last_name ,
                                              e.salary ,
         j.grade_level
                                  FROM employees e ,
   job_grades j
                              WHERE e.salary between j.lowest_sal and j.highest_sal

也可以使用如 >, >=, < ,<=等等

outer join : select a.* from a, b where a.id = b.id ( + )

注意 : ( + ) 只能出现在一边 , 并且出现在数据有缺失的一边 .

A condition involving an outer join cannot use the IN operator or be linked to another condition by the OR operator .^ ? ^

self join : SELECT worker.last_name || 'works for' || manager.last_name
FROM employees worker , employees manager

WHERE worker.manager_id = manager.employee_id

SQL 标准

cross join : Cartesian join ( 笛卡尔基连接 )

Natural join : 自然连接 , 两个table中相同的name就会做为连接的条件 , 同时如果是两个 table 列名相同 , 但是此列的类型不同 , 就会出错 .

                             SELECT department_id, department_name
                                             location_id , city
                                FROM departments
                                NATURAL JOIN locations

where department_id in ( 20 , 50 ) --> 可以增加条件 , 使用 where 语句
Using clause : Using clause 跟 natural join 类似 , natural join 当两个table列名相同 , 但同时类型不同时就不能使用 , 而 using clause 可以弥补这一点

它只是针对 , using clause 里的内容进行连接 .

                                SELECT department_id, department_name
                                                 location_id , city
                                    FROM departments d join location l
                                    USING ( location_id )                           --> 这两个table即便有别的同名的列也不进行比较,只比较这一个
                                  WHERE location_id = 1400                  --> 此处 location_id , 不能使用 d.location_id 或者 l.location_id , 只能直接使用 location_id

    ON                  :   SELECT e.employee_id , e.last_name
                                                 d.department_id , d.location_id
                                   FROM   employees e join departments d

ON ( e.department_id = d.department_id )

AND e.manager_id = 141 --> 此处连接附加条件要使用 AND , 不能使用 WHERE

   left-right-full   :    SELECT e.employee_id , e.last_name
                                                 d.department_id , d.location_id
                                  FROM   employees e left outer join departments d                  --> 左边table少数据, 左空 , 右 data
                                      ON    ( e.department_id = d.department_id )

                                 SELECT e.employee_id , e.last_name
                                                  d.department_id , d.location_id
                                   FROM   employees e right outer join departments d              --> 右边table少数据, 左data , 右空
                                       ON    ( e.department_id = d.department_id )

                                 SELECT e.employee_id , e.last_name
                                                 d.department_id , d.location_id
                                   FROM   employees e full outer join departments d                 -->左右table都少数据左空 , 右 data & 左data , 右空
                                       ON    ( e.department_id = d.department_id )