Oracle指令大全.doc

--sql structured query language --DML--Data Manipulation Language--数据操作语言 query information (SELECT), add new rows (INSERT), modify existing rows (UPDATE), delete existing rows (DELETE), perform a conditional update or insert operation (MERGE), see an execution plan of SQL (EXPLAIN PLAN), and lock a table to restrict access (LOCK TABLE). --DDL--Data Definition Language--数据定义语言 create, modify,drop, or rename objects (CREATE,ALTER,DROP,RENAME), remove all rows from a database object without dropping the structure (TRUNCATE), manage access privileges (GRANT,REVOKE), audit database use (AUDIT,NOAUDIT) and add a description about an object to the dictionary (COMMENT). --Transaction Control事务控制语句 save the changes(COMMIT) or discard the changes (ROLLBACK) made by DML statements. Also included in the transaction-control statements are statements to set a point or marker in the transaction for possible rollback (SAVEPOINT) and to define the properties for the transaction (SET TRANSACTION). Used to manage the properties of the database. There isonly one statement in this category (ALTER SYSTEM). --DCL--Data Control Language--与开发关系不是很密切,用于权限的分配与回收 grant,revoke,data control --Session Control control the session properties (ALTER SESSION) and to enable/disable roles (SET ROLE). --System Control select的用法 --每个员工的所有信息 select * from emp --每个人的部门编号，姓名，薪水 select deptno,ename,sal from emp; --每个人的年薪 select ename,sal*12 from emp; --计算2*3的值 select 2*3 from emp; --计算2*3的值(dual) select 2*3 from dual; select * from dual; --得到当前时间 select sysdate from dual --可以给列起别名,比如求每个人的年薪 select ename,sal*12 salperyear from emp; --如果别名中有空格，需要用双引号 select ename,sal*12 sal per year from emp; --如果没有内容，则为空 select comm from emp; --当空字段参与计算，则结果是null --例如：计算每个人的全年的收入包括月薪和年终奖 select ename,sal*12+comm from emp; --可以将多个字符串拼在一起。比如：求每个人的薪水，格式为smith-sal-123 select ename||-sal-||sal from emp; --如果字符串中有单引号，需要用另外一个单引号转义，比如：这样一个字符串： hes friend select ename||s sal