ORACLE_SQL语句总结

SQL语句学习总结

数字类型NUMBER可用运算符（+ - * /），日期DATE类型只能用（+ -），日期之间+没有意义,SQL语句不区分大小写，但是

匹配字符串时严格区分大小写。'2'+2 =4,因为varchar2和number，varchar2和date之间可以自动转换。

1.空值判断(NULL)

--C_CONS用户表，cons_id为NUMBER类型，cons_name为VARCHAR类型
SELECT a.cons_name
	FROM c_cons a
--为空
--WHERE a.cons_address IS NULL
--不为空
WHERE a.cons_address IS NOT NULL

2.区间匹配(BETWEEN ...AND)

--C_CONS用户表，cons_id为NUMBER类型，cons_name为VARCHAR类型
SELECT a.cons_name
	FROM c_cons a
--在1~10之前，包含边界
WHERE a.cons_id BETWEEN 1 AND 10
--不在1~10之间
--WHERE a.cons_id NOT BETWEEN 1 AND 10

3.模糊查询(LIKE)

--C_CONS用户表，cons_id为NUMBER类型，cons_name为VARCHAR类型
SELECT a.cons_address
	FROM c_cons a
--匹配姓名包含a,b
--WHERE a.cons_name LIKE '%a%b%' OR a.cons_name LIKE '%b%a%'
--匹配姓名包含特殊字符需要转义的(第二个字母是_的用户)
WHERE a.cons_name LIKE '_\_%' ESCAPE '\'

4.存在数值中(IN)

--C_CONS用户表，cons_id为NUMBER类型，cons_name为VARCHAR类型
SELECT a.cons_address
	FROM c_cons a
--匹配id在1,2,3中的
WHERE a.cons_id IN (1,2,3)
--不在
WHERE a.cons_id NOT IN (1,2,3)

5.大小写函数（大写UPPER、小写LOWER、首字母大写INITCAP）

--C_CONS用户表，cons_id为NUMBER类型，cons_name为VARCHAR类型
SELECT a.cons_address
	FROM c_cons a
--匹配姓名叫Jack的(全部转为大写)
WHERE UPPER(a.cons_name) = 'JACK'
--全部转为小写
--WHERE LOWER(a.cons_name) = 'jack'
--首字母大写
--WHERE INITCAP(a.cons_name) = 'Jack'

6.字符控制函数（连接CONCAT、截取SUBSTR、长度LENGTH、首次出现位置INSTR、补齐位数、移除TRIM、REPLACE替换）

--连接CONCAT
CONCAT('a','b')           ab
--截取SUBSTR(角标从1开始,2代表第二个字符，4代表取4个)
SUBSTR('abcdefg',2,4)     bcde
--长度LENGTH
LENGTH('abc')             3
--首次出现位置INSTR(没有返回0)
INSTR('abcdefg','d')      4
--补齐位数（左LPAD,右RPAD,对齐常使用''）
LPAD('abcd',10,'*')       ******abcd
RPAD('abcd',10,'*')       abcd******
--移除TRIM(只去首尾)多用去空格
TRIM('a' from 'aba')      b
--REPLACE替换(替换所有)
REPLACE('ababa','a','c')  cbcbc

7.数字函数（四舍五入ROUND、直接截断TRUNC、求余MOD）

--四舍五入ROUND
ROUND(123.45,2)           123.45
ROUND(123.95)             124
ROUND(123.45,-2)          100
--直接截断TRUNC
TRUNC(129.45,2)           123.45
TRUNC(129.45)             123
TRUNC(129.45,-2)          100
--求余MOD
MOD(100,30)               10 

8.日期函数

--两个日期月数差
MONTH_BETWEEN(SYSDATE,a.date)
--向日期加几个月
ADD_MONTHS(SYSDATE,2)
--即将出现的星期几是哪天
NEXT_DAY(SYSDATE,'星期一')
--月倒数第二天
LAST_DAY(SYSDATE)-1 
--ROUND按月
ROUND(SYSDATE,'MONTH')

9.类型显示转换

--显示转换
to_char(12345.67,'999,999,999.99')    12,345.67
to_char(12345.67,'000,000,999.99')    012,345.67
to_char(12345.67,'$999,999,999.99')   $12,345.67
to_char(12345.67,'L999,999,999.99')   ￥12,345.67

10.NVL

--为空返回0
nvl(cons_id,0)
--为空返回0，不为空返回123
nvl2(cons_id,123,0)

11.CASE...WHEN,DECODE

CASE a.cons_age WHEN '20' THEN '10' 
                        WHEN '21' THEN '11' 
                        WHEN '22' THEN '12' 
                        WHEN '23' THEN '13' 
                        ELSE '14' AS price
END

DECODE(a.cons_age, 10,'10',
                           21,'11',
                           '14') AS price