Oracle的sql常用技巧

来源：互联网发布：mac单windows系统编辑：程序博客网时间：2024/05/22 13:37

1.Oracle产生随机数 oracle数据库中有产生随机数的函数 DBMS_RANDOM

一）生成0到1之间的小数，包括0不包括1

SELECT DBMS_RANDOM.VALUE FROM DUAL;

二）生成随机正态分布数

SELECT DBMS_RANDOM.VALUE(0，100) FROM DUAL; //生成0-100范围内的小数，包括0不包括100

三）生成指定范围内的整数

SELECT TRUNC(DBMS_RANDOM.VALUE(0，100)) FROM DUAL; //生成0-100范围内的整数，包括0不包括100

四) 生成正态分布的随机数

SELECT DBMS_RANDOM.NORMAL FROM DUAL;

五）生成随机字符串

SELECT DBMS_RANDOM.STRING('参数1' , '参数2') FROM DUAL;

必须要指定两个参数，参数1用于指定生成随机字符串的类型，参数2用于指定生成随机字符串的长度。其中参数1 的取值有：‘U’ 指生成大写的字符 ‘L’ 指生成小写的字符 ‘X’ 数字和大写的字母 ‘A’ 大小写混合的字符 ‘P’ 可打印的字符

例如： SELECT DBMS_RANDOM.STRING('X', 3) FROM DUAL; //生成长度为3的类型是大写字母和数字的字符串

六）生成预期范围内的日期，比如我想要从2016年1月1日到2016年年底内任意的日期类型的数据，注意没有时分秒的具体数值。

第一步：获得一个2016年1月1日的基数 SELECT TO_CHAR(TO_DATE('01/01/2016','MM/DD/YYYY','J')) //得到一个2457389基数

第二步： SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457389,2457389+364)), 'J' ) FROM DUAL; //得到2016-01-01 至 2016-12-30的随机日期

2.常用的处理函数

一、 CAST (expression AS data_type) AS 用于分割两个数据类型

例如： SELECT CAST('15' AS NUMBER) FROM DUAL;

二、 DECODE(EXP,条件1，返回值1，条件2，返回值2，...，默认值)

三、 NVL(EXP，返回值1) 当EXP值为null时返回值1，不为null时返回本身和mysql的IFNULL 函数类似

四、 NVL2(EXP，返回值1，返回值2) 当exp的值为null时返回值1，不为null时返回值2

五、 SELECT TO_CHAR(2164364,'FM999,999,999.90') FROM DUAL； //把前面的数据按后面的类型进行格式化

六、 SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; //从时间中抽取年份出来

SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TIMESTAMP'2010-11-02 10:20:23') FROM DUAL; //这个可以系统计算年龄

七、 LPAD(COLUMN,参数1,参数2)

LPAD函数是在column列的前面填充指定长度的指定字符串，参数1是指定长度，参数2是指定要填充的字符，如果指定参数1的长度比column的长度要小则做截取的处理，RPAD是在column列的后面填充

八、 SELECT RPAD(16464,10,0) FROM DUAL; //返回结果是1646400000，这个可以做数据的处理

九、 SELECT SYS_GUID() FROM DUAL; //返回不重复的32位随机字符串，可以用作表的主键

3. case when 函数

Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他'

END

--Case搜索函数
CASE

WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他'

END

注意：简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。

比如可以用case函数统计分数在一些区间段的人数的总数
select
case
when tb.score >= 80 then '优秀'及格
when tb.score <80 and tb.score >= 60 then '及格'
else '不及格'
end 区间,
concat(count(1),'人') 人数
from tablename tb
group by
case
when tb.score >= 80 then '优秀'
when tb.score <80 and tb.score >= 60 then '及格'
else '不及格'
end;

查询结果如下显示：

区间人数优秀10人及格6人不及格5人

可以对一个表中的不同类型的数据做一个统计
select
country,
sum(case when tb.sex=1 then '1' else '0' end) 男性人口,
sum(case when tb.sex=2 then '2' else '0' end) 女性人口
from tablename tb
group by country

可以和update结合
update table
set salary =
case when sarlay >= 8000 then salary*0.90
when salary <4000 then salary*1015
else salary
end
这样可以一次就对整张表进行了更新，并且不会有数据前后产生的错误性

4. ORACLE的MERGE INTO 用法

/*語法：
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;
*/

/*语法
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]
*/

5. 如何把一个子查询的多个选择排成一列显示

Oracle中提供了两个函数
一、WMSYS.WM_CONCAT(table_name) 这个函数默认是把查出来的数据用逗号连接数据类型是<CLOB>类型
例如表名 table A

IDNAME1AA2BB3CC
SELECT WMSYS.WM_CONCAT(NAME) FROM A

查询出来的结果是 AA,BB,CC

SELECT REPLACE(WMSYS.WM_CONCAT(NAME),',','|') FROM A

查询出来的结果是AA|BB|CC

二、可以使用LISTAGG函数表名B

dept_noname10张三10李四10王五20赵六20马奇
SELECT DEPT_NO, LISTAGG(NAME,'|') WITHIN GROUP (ORDER BY NAME) AS NAMES FROM B GROUP BY DEPT_NO

查询出来的结果是 dept_no names

dept_nonames10李四|王五|张三20马奇|赵六
注意：WITHIN GROUP (ORDER BY ***) 字段不能少这是聚合分析类函数

0 0