Oracle PL/SQL Summary

来源：互联网发布：parsley.js 中文提示编辑：程序博客网时间：2024/05/01 05:24

1. SQL语言分类：

数据查询语言： select，功能语法最复杂、最灵活DML数据操纵语言： insert, update, deleteDDL数据定义语言： create table, alter table, drop table，注意ddl语句会自动提交事务TCL事务控制语言： commit, rollback, savepointDCL数据控制语言： grant, revoke，注意dcl语句会自动提交事务

2. SELECT子句：SELECT <*, COLUMN(alias),...> FROM TABLE

SELECT * FROM emp //检索所有列SELECT ename FROM emp //检索特定列SELECT ename,hiredate FROM emp //检索日期列SELECT ename,To_Char(hiredate, 'YYYY-MM-DD') FROM emp //以特定格式检索日期列SELECT DISTINCT deptno,job FROM emp //取消重复行SELECT ename, sal*12 FROM emp //使用算术表达式SELECT ename AS "NAME", sal*12 AS "SAL" FROM emp //使用列别名SELECT ename,sal,sal+Nvl(comm,0) FROM emp //使用nvl处理NULL值SELECT ename,sal,sal+Nvl2(comm,sal+comm,sal) FROM emp //使用nvl2处理NULL值SELECT ename||' is a '||job AS "Emp Detail" FROM emp //连接字符串

3. WHERE子句：SELECT <*, COLUMN(alias),...> FROM TABLE [WHERE condition(s)]

SELECT ename,sal FROM emp WHERE sal>2000 //在where中使用数字值SELECT job,sal FROM emp WHERE ename='SCOTT' //在where中使用字符值SELECT ename,hiredate FROM emp WHERE hiredate>To_Date('1982-01-01','YYYY-MM-DD') //在where中使用日期值SELECT ename,sal FROM emp WHERE sal BETWEEN 1000 AND 2000 //在where中使用between...and...操作符SELECT ename,sal FROM emp WHERE ename LIKE 'S%' //在where中使用like操作符，‘%’表示0个或多个字条SELECT ename,sal FROM emp WHERE ename LIKE '__A%' //‘_’表示单个字条SELECT ename,sal FROM emp WHERE ename LIKE '%a_%' ESCAPE 'a' //如果将通配符%或_作为字符值使用，需要在escape之后使用转义符SELECT ename,sal FROM emp WHERE sal IN (800,1250) //在where中使用in操作符SELECT ename,sal FROM emp WHERE mgr IS NULL //在where中使用is null操作符SELECT ename,sal FROM emp WHERE comm IS NOT NULL //在where中使用逻辑操作符notSELECT ename,sal,job,deptno FROM emp WHERE deptno=20 AND/OR job='CLERK' //在where中使用逻辑操作符and，or

4. ORDER BY 子句(必须是最后一个)：SELECT <*, COLUMN(alias),...> FROM TABLE [WHERE condition(s)] [ORDER BY expr {ASC|DESC}]

SELECT ename,sal,comm FROM emp WHERE deptno=30 ORDER BY sal ASC|DESC //升序|降序排列SELECT ename,sal,comm FROM emp WHERE deptno=30 ORDER BY sal ASC, comm DESC //使用多列排序SELECT ename,sal*12 AS "TotalSal" FROM emp WHERE deptno=30 ORDER BY "TotalSal" DESC //使用列别名排序SELECT ename,sal*12 AS "TotalSal" FROM emp WHERE deptno=30 ORDER BY 2 DESC //使用列位置编号排序