【Oracle】Oracle基础

sql基础

DDL

CREATE TABLE 
--添加字段
ALTER TABLE TABLE_NAME ADD  DEMO_NAME VARCHAR2(20 CHAR);
--添加字段注释
COMMENT ON COLUMN TABLE_NAME.DEAM_NAME IS '名称';
	-- 删除
DROP TABLE -- 删除表
DROP INDEX -- 删除索引

--清空表结构（删除所有数据，比delete速度快，delete可进行事务提交的，可进行数据回滚）
TRUNCATE TABLE TABLE_NAME 

索引

--创建索引
create index 索引名 on 表名(字段名1,字段名2,......,字段名n);       --普通索引
create unique index 索引名 on 表名(字段名1,字段名2,......,字段名n);--唯一索引
--删除索引
drop index 索引名;
alter table 表名 drop constraint 主键名;
--修改索引
alter index 索引名 rename to 新索引名;
--重建索引：重建索引可以减少硬盘碎片和提高数据库系统的性能
alter index 索引名 rebuild;
--整理碎片:对索引的无用空间进行合并，减少硬盘碎片和提高数据库系统的性能。
alter index 索引名 coalesce;

DML

INSERT INTO TABLE_NAME() VALUES();
UPDATE TABLE_NAME SET DEMO_NAME = '01' WHERE DEMO_ID = '1';
DELETE TABLE_NAME WHERE ID = '1';

DQL

SELECT * FROM TABLE_NAME ;
(一)单表查询
1、简单的条件查询
WHERE
LIKE '%GE%'
AND 
OR 
BETWEEN A AND B 
IN() --结果集排除NULL值 在ORACLE中,IN的最大条数是1000条,
EXISTS()--结果集不排除NULL值 且子查询数据量大时效率比IN高
IS NOT NULL <>
2、去重查询
SELECT DISTINCT DEMO_NAME FROM A_DEMO 
SELECT DEMO_NAME FROM TABLE_NAME GROUP BY DEMO_NAME HAVING COUNT(DEMAE_NAME)>1;
3、排序查询
SELECT * FROM TABLE_NAME ORDER BY DEMO_NO ASC;
SELECT * FROM TABLE_NAME ORDER BY DEMO_NO DESC;
4、基于伪列的查询
ROWID       --每条记录的物理地址  唯一 不会变
ROWNUM      --给每条数据标个数，1、2、3..... 会变  不同的ORDER BY ROWNUM 值也不同
-- 使用ROWNUM 进行分页
SELECT * FROM (SELECT ROWNUM R ,T.* FROM TABLE_NAME T WHERE ROWNUM <10) WHERE R >5;
5、聚合统计
SUM()
AVG()
MAX()
MIN()
count()
GROUP BY --分组聚合
GROUP BY HAVING -- 对分组聚合后的数据进行条件查询
（二）连接查询
1、内连接查询
--返回左表和右表相同的记录数
SELECT * FROM B_DEMO B ,A_DEMO A WHERE B.A_DEMO_NO = A.DEMO_NO;
2、左外连接
--返回左表中所有的记录数，加上和右表连接相等的记录数(右表不重复 则记录数和左表相同)
SELECT B.*,A.DMEO_NAME FROM B_DEMO B LEFT JOIN A_DEMO A ON B.A_DEMO_NO = A.DEMO_NO;
3、右外连接
--返回右表中所有的记录数，加上和左表连接相等的记录数
SELECT B.*,A.DMEO_NAME FROM B_DEMO B RIGHT JOIN A_DEMO A ON B.A_DEMO_NO = A.DEMO_NO;
（三）子查询
SELECT (SELECT * FROM TABLE_NAME1 ) FROM TABLE_NAME;
SELECT * FROM (SELECT * FROM TABLE_NAME1);
SELECT * FROM TABLE_NAME WHERE DEMO_NO IN (SELECT DEMO_NO FROM TABLE_NAME1);
（四）分页查询
-- 使用ROWNM进行分页
SELECT * FROM (SELECT ROWNUM R ,T.* FROM TABLE_NAME T WHERE ROWNUM <10) WHERE R >5;
-- 基于排序的分页
SELECT * FROM (
	SELECT ROWNUM R ,T.* FROM (
		SELECT * FORM TABLE_NAME ORDER BY DEMO_NAME DESC) T 
		WHERE ROWNUM <30)
WHERE R > 5;
（五）单行函数
1、字符函数
SELECT LENGTH('STRING') FROM DUAL; --求字符串长度
SUBSTR() -- 求字符串字串 第一位是1 例如 SUBSTR('P_20211212',3)  从第三位开始截取
CONCAT() -- 字符串拼接 CONCAT(DEMO_NAME,'色')
2、数值函数
SELECT ROUND(100.243) FROM DUAL; --四舍五入
SELECT TRUNC(100.123,2) FROM DUAL -- 截取函数 例如保留两位小数
SELECT MOD(123,3) FROM DUAL; -- 取余函数
3、日期函数
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;  --加月函数
SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL;  --日期截取 年初
SELECT TRUNC(SYSDATE,'MM') FROM DUAL; -- 日期截取 月初
4、转换函数
TO_CHAR(TO_DATE(V_DATE_DATE,'YYYYMMDD'),'YYYY/MM/DD')
5、其他函数
SELECT NVL(NULL,0) FROM DUAL; -- 空值处理函数
SELECT NVL2(B_NAME,B_NAME,'空值') FROM table_name; --为不为空取B_NAME，为空取 '空值'  
SELECT DECODE(B_NO ,1,'1号',2,'2号',3,'3号') FROM TABLE_NAME; --DECODE 相当于 CASE WHEN 
SELECT DEMO_NAME , (CASE EDATE 
						WHEN '99991231' THEN '有效'
						ELSE '过期'
						END) 有效标志 FROM A_DEMO;
六、行列转换
行转列CASE WHEN  
列转行UNION ALL
七、分析函数（子查询也可以实现）
SELECT ROW_NUMBER() OVER(PARTITION BY DEMO_NAME ORDER BY SDATE DESC) A ,T.* FROM A_DEMO T; --123
SELECT DENSE_RANK() OVER(PARTITION BY DEMO_NAME ORDER BY SDATE DESC) A ,T.* FROM A_DEMO T; --113

八、集合运算
--并集
SELECT * FROM B_DMEO WHERE B_NO >=3
UNION
SELECT * FROM B_DEMO WHERE B_NO <=5
--交集
SELECT * FROM B_DEMO WHERE B_NO >=2;
INTERSECT 
SELECT * FROM B_DEMO WHERE B_NO <=5;
--差集
SELECT * FROM B_DEMO WHERE B_NO >=2;
MINUS
SELECT * FROM B_DEMO WHERE B_NO <=5;