1: 确定表的结构
DESC table_name;
2: 处理NULL,NVL(EPER1,EPER2),EXPER1为空,返回EXPER2,反之则返回EXPER1;NVL2(EPER1,EPER2,EPER3),EPER1不为空,返回
EPER3,为空返回EPER2,数据类型一致。
SELECT NVL(EPER1,EPER2) FROM DUAL;
3: 联结字符串
SELECT conn1||'字符串'||conn2 AS '字符串' From dual;
4: 使用子查询直接装载,/*+APPEND*/为直接装载,对大批量数据处理很快
INSERT /*+APPEND*/ INTO employee(empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno FROM emp WHERE deptno = 20;
5: 使用ALL,FIRST操作符进行多表插入,
INSERT ALL
WHEN depno=10 THEN INTO dept10
WHEN depno=20 THEN INTO dept20
WHEN depno=30 THEN INTO dept30
ELSE INTO other
SELECT * FROM emp;
6: 使用DEFAULT选项更新数据
UPDATE emp SET jop=DEFAULT WHERE ename='test';
7: 复制表数据
UPDATE employee SET depno=(SELECT depno FROM emp WHERE empno='10')
WHERE job=(Select jop From emp WHERE empno='10')
8: 使用TRUNCATE TABLE 截断表
TRUNCATE TABLE emp;
9: 事务提交与回退
COMMIT,ROLLBACK;
10: 分组函数只能出现在选择列表,HAVING 和 ORDER BY 子句中,ORDER BY 放在最后.
Select depno,max(sal),min(sal) From emp GROUP BY depno HAVING MAX(sal)>100 ORDER BY avg(sal)
11: 使用ROLLUP操作符,生成横向小计统计.CUBE生成横向和纵向统计.
Select depno,max(sal),min(sal) From emp GROUP BY ROLLUP(depno);
Select depno,max(sal),min(sal) From emp GROUP BY CUBE(depno);
12: 内连接与外连接
Select table1.COLUMN,table2.COLUMN From table1 [inner|left|right|full]
join table2 ON table1.column=table2.column;
13: 在DML,DDL中用子查询.
Create TABLE newName(depno,depname) AS Select depno,depname From emp;
14: 合并查询,UNION,取并集,UNION ALL取并集且有重复,INTERSECT,取交集,MINUS取两个结果的差集.
Select table1.COLUMN1,table1.COLUMN2 From table1
UNION [UNION ALL|INTERSECT|MINUS]
Select table2.COLUMN1,table2.COLUMN2 From table2;
一些不常用的SQL语句
最新推荐文章于 2020-10-17 10:07:37 发布