DB问题 ORACLE SQL语句整理(建表语句，简单语句，函数，复杂函数)

1:建表相关:
以创建一个两个字段的表为例 表名：TEST ，字段：ID，NAME

操作	SQL语句
删除表	DROP TABLE TEST;
创建表	CREATE TABLE TEST (“ID” VARCHAR2(100 BYTE) NULL, “NAME” VARCHAR2 (200 BYTE)NULL);
增加主键	ALTER TABLE TEST ADD PRIMARY KEY (“ID”);
添加字段注释	COMMENT ON COLUMN TEST.“ID” IS “这是字段ID”;
添加索引	CREATE INDEX TEST_INDEX ON TEST(NAME);

2：字段类型说明：
只写我用过的

字段类型	描述
CHAR	固定长度字符串（MAX：2000）
VARCHAR2	可变长度字符串（MAX：4000）
NVARCHAR2	根据字符集可变长度 （MAX：4000）
TIPS	同样是存入“你好123abc”，char占2000个字节，varchar2占11个字节，NVARCHAR2占16个字节，char字段如果存入的数据小于设定的字段长度，会用空格填满该字段，varchar2会将汉字按照2个字节存入，字母按照一个字节存入，NVARCHAR2会将字符全部按照1个字节或者两个字节存入，看你数据库设定的字符集
DATE	在数据库中存入固定7个字节，分别对应：世纪+100，年，月，天，小时+1，分+1，秒+1
TIMESTAMP	长度为3，3代表毫秒的3位数：2000-01-01 00:00:00.000
CLOB	长文本（MAX：4G）
NUMBER	数字类型（LEN:(1-38),LEN:(-84-127)）
TIPS	NUMBER类型字段长度（P，S）P为有效位，123.456有效位是6，S大于0时，精确到S位，并且四舍五入，如果S设定为2，123.456则录入为123.46；S小于0时，精确到整数的S位，如果S为-2，则123.456录入为100

3：简单SQL整理：
假设有TEST表，字段有 ID，NAME
假设有TEST1表，字段有 IDD，NAMEE

描述	SQL
插入一条(全字段)	INSERT INTO TEST VALUES(“001”,“张三”);
插入一条（部分字段）	INSERT INTO TEST(ID) VALUES(“001”);
插入多条	INSERT ALL INTO TEST (ID) VALUES(“002”) INTO TEST1(IDD) VALUES(“001”) SELECT 1 FROM DUAL;
更新一条	同MYSQL
删除一条	同MYSQL
查询(无分页)	SELECT ID,NAME FROM TEST WHERE 1=1;
查询(分页)	SELECT * FROM (SELECT T.*,RN FROM (这个括号内放你的SQL语句) T) WHERE RN BETWEEN (这里放你的开始行) AND (这里放你的结束行);
TIPS	ORACLE用的伪列，没用过研究这个需要半小时或者几个小时时间

4：简单函数整理：
假设你有如下表TEST

ID(VARCHAR2(50))	NAME(VARCHAR2(100))	AGE(NUMBER(3,0))	GENDER(NUMBER(1,0))	DEPARMENT(VARCHAR2(200))
001	张三	18	1	1
002	李四	19	NULL	1
003	王五	20	0	1
004	赵六	19	0	2
005	NULL	20	1	2

函数名	描述	语句	结果
NVL(A,B)	if判断，A为空返回 B，否则返回A	SELECT NVL(NAME,ID) FROM TEST WHERE ID =‘005’;	005
NVL(A,B,C)	A===NULL?C:B	略	略
INSTR(A,B)	类似于JS的indexOf	SELECT (ID,‘3’) FROM TEST WHERE ID = ‘003’;	3
SUBSTR(STR,START,LEN)	类似于JS的substr	略	略
LPAD(STR,LEN,PSTR)	从左向右填充字符串	SELECT LPAD(‘hello’,‘10’,‘s’) FROM DUAL;	ssssshello
RPAD(STR,LEN,PSTR)	从右向左填充字符串	SELECT RPAD(‘hello’,‘10’,‘s’) FROM DUAL;	hellossssss
DECODE(T,V1,R1,V2,R2…D)	类似于JS的SWTICH	SELECT DECODE(GENDER,‘1’,‘男’,‘0’,‘女’,‘未知’) FROM DUAL;	GENDER字段：1男，0：女，null：未知
CONCAT(或者两个竖线，打不出来 o(╥﹏╥)o)	字符串拼接，类似于JS的 ‘+’	SELECT CONCAT(‘HELLO’,‘WORLD’) FROM DUAL;	HELLOWORLD
REPLACE	字符串替换	SELECT REPLACE(‘ABC’,‘A’,‘Z’) FROM DUAL;	ZBC
ABS	绝对值	略	略
CEIL(NUM)	返回大于等于NUM的最小整数	略	略
FLOOR(NUM)	返回小于等于NUM的最大整数	略	略
ROUND(NUM，LEN)	对小数据后面LEN四舍五入	SELECT ROUND(1,2345,3) FROM DUAL;	1.235
SYSDATE	mysql的NOW()	SELECT SYSDATE-1 FROM DUAL;	昨天此时
ADD_MONTHS(D,N)	对日期运算	SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL;	上个月此时
LAST_DAY(D)	返回该月最后一天的日期	略	略
TO_CHAR	把时间转化为字符串	略	略
TO_DATE	把字符串转化为时间	SELECT TO_DATE(‘DATE’,‘FORMAT’) FROM DUAL;	略
TIPS	年月日时分秒:	‘yyyy-mm-dd hh24:mi:ss’

扩展：TRUNC截取数字和日期：
1：截取数字(不四舍五入)
TRUNC(NUM，LEN)
LEN>0 保留多少位小数点，
SELECT TRUNC(123.4567,2) FROM DUAL; // 返回123.45
LEN<0 保留多少位整数
SELECT TRUNC(123.4567,-2) FROM DUAL; // 返回100

2：截取时间：
1：本年第一天：
SELECT TRUNC(SYSDATE,‘YEAR’) FROM DUAL;
SELECT TRUNC(SYSDATE,‘yy’) FROM DUAL;
SELECT TRUNC(SYSDATE,‘yyyy’) FROM DUAL;
2: 本季度第一天
SELECT TRUNC(SYSDATE,‘q’) FROM DUAL;
3：本月第一天
SELECT TRUNC(SYSDATE,‘month’) FROM DUAL;
SELECT TRUNC(SYSDATE,‘mm’) FROM DUAL;
4：本周第一天（周日）
SELECT TRUNC(SYSDATE,‘day’) FROM DUAL;
5：当前小时开始时间
SELECT TRUNC(SYSDATE,‘HH’) FROM DUAL;
6：当前分钟开始时间
SELECT TRUNC(SYSDATE,‘MI’) FROM DUAL;

复合函数

函数	描述
AVG	平均值
COUNT	数据条数
MAX	最大值
MIN	最小值
SUM	总和

SELECT AVG(AGE) FROM TEST GROUP BY DEPARTMENT; // 查询以部门分组的平均年龄
SELECT COUNT(1) FROM TEST GROUP BY DEPARTMENT; // 查询每个部门有多少人
SELECT MAX(AGE) FROM TEST GROUP BY DEPARTMENT; // 查询以部门分组的最大年龄
SELECT MIN(AGE) FROM TEST GROUP BY DEPARTMENT; // 查询以部门分组的最小年龄
SELECT SUM(AGE) FROM TEST GROUP BY DEPARTMENT; // 查询以部门分组的年龄总和

拓展常用函数：
1：CASE WHEN THEN ELSE END；
SELECT CASE
WHEN AGE < 20
THEN ‘少年’
WHEN AGE < 40
THEN ‘壮年’
WHEN AGE <60
THEN ‘中年’
ELSE ‘老年’
END AS STATUS
FROM TEST；

STATUS：字段名

2：LISTAGG
分组后拼接(根据ID排序)
SELECT LISTAGG(NAME,’,’) WITHIN GROUP(ORDER BY ID DESC) NAMES FROM TEST GROUP BY DEPARTMENT;
// 王五，李四，张三
// ，赵六

3：START WITH …GROUP BY
假设有以下树形结构表 TREE

ID	NAME	PID
1	顶级节点	#
2	父节点1	1
3	父节点2	1
4	子节点1-1	2
5	子节点1-1-1	4

树形结构：
顶级节点(1)
-------父节点1(2)
-----------子节点1-1(4)
-------------------子节点1-1-1(5)
-------父节点2(3)
查询父节点1以下所有子节点（包括父节点1）
SELECT ID,NAME,PID FROM TREE START WITH ID = ‘2’ CONNECT BY PRIOR ID = PID;
// 查询结果： 父节点1，子节点1-1，子节点1-1-1

子节点1-1 以上所有父节点（包含子节点1-1）
SELECT ID,NAME,PID FROM TREE START WITH ID = ‘4’ CONNECT BY ID = PRIOR PID;
// 查询结果：子节点1-1，父节点1，顶级节点

查询所有父节点1下面所有子节点（不包括父节点1）
SELECT ID,NAME,PID FROM TREE START WITHN PID = ‘2’ CONNECT BY PRIOR ID = PID;
//查询结果：子节点1-1，子节点1-1-1

5：ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 分组聚合
一般用来查询根据。。分组，根据时间倒序的最新一条数据
SELECT IDCARD,UPDATE_TIME,RN FROM
(SELECT IDCARD,UPDATE_TIME,ROW_NUMBER() OVER(PARTITION BY IDCARD ORDER BY UPDATE_TIME) FROM TEST) WHERE RN = 1;

TIPS:
1:查询时严禁使用 SELECT * ，ORACLE在解析过程中会将 * 号依次转换为所有字段，转换时是通过查询数据字典完成的，会加大数据库的工作量
2：WHERE 条件的顺序（有待考察）
SQL是从右向左执行，把ISDEL这种索引想放到WHERE条件的最后面会增加查询速度（我没感觉出来），SELECT ID,NAME FROM TEST WHERE NAME LIKE ‘%张%’ AND ISDEL = ‘0’；
3: 使用EXISTS和NOT EXISTS来替换 IN和 NOT IN
①：EXISTS对外表用LOOP逐条查询
②：IN就是多个OR叠加，最大条数1000条
③：IN会使索引失效
④：如果IN内的条件已经确定是少量的，比如50条以下，20条一下，用IN

4：多表查询时使用表别名
SELECT A.ID,A.NAME,B.ID BID,B.NAME BNAME
FROM TEST A LEFT JOIN MY_TEST B ON A.ID = B.AID
WHERE A.NAME LIKE ‘%张%’

5：表名和字段名用大写，mysql用小写（规范…）

6：事务执行： START 【SQL代码】 END；