2012语句速查.docVIP

oracle SQL: Structure Query Language 常见数据类型 Char(n) 定长字符串 Varchar2(n) 变长字符串 Number(m,p) 数字类型 Date 日期类型 SQL语句分类 查询语句:SELECT DML语句(数据操作语言)INSERT/UPDATE/DELETE/MERGE DDL语句(数据定义语言)CREATE/ALTER/DROP/TRUNCATE DCL语句(数据控制语言)GRANT/REVOKE 事务控制语句:COMMIT/ROLLBACK/SAVEPOINT 一.SELECT 语句 基本查询 格式 SELECT {*,column[alias], ...} FROM table; 例子 SELECT * FROM departments; SELECT department_id,department_name FROM departments; 带算数表达式的查询语句 SELECT last_name,salary,salary*12 FROM employees; SELECT last_name,salary,salary*12+100 FROM employees; 包含连接表达式的SELECT语句 SELECT last_name || job_id FRM employees; SELECT last_name|| work at || job_id FROM employees; 包含字段别名的SELECT语句 SELECT lastname 姓名,salary 薪水 FROM employees. 包含文本字符串的SELECT语句 SELECT last_name|| is a || job_id AS Employee Details FROM employees; 去重关键字DISTINCT SELECT DISTINCT department_id FROM employees; 取得日期格式的查询语句 SELECT * FROM v$nls_parameters; 取得当前日期日期的查询语句 SELECT sysdate FROM DUAL; 带限制条件的查询语句 格式 SELECT [DISTINCT] {*, column [alias], ...} FROM table [WHERE condition(s)] 包含比较操作符的查询语句 SELECT last_name FROM employees WHERE salary=1500; 常见操作符  = = = BETWEEN ...AND IN (list) LIKE IS NULL 例子 SELECT * FROM employees WHERE manageer_id IN(1,2,3); SELECT last_name FROM employees WHERE last_name IS NULL; 包含通配符的SELECT语句 %表示零或多个字符 _表示一个字符 例子 SELECT last_name FROM employees WHERE last_name LIKE _A%; WHERE条件的逻辑运算 逻辑运算符 AND OR NOT 例子 SELECT * FROM employee WHERE salary1 AND job_id=C; SELECT * FROM employee WHERE job_id NOT IN(Clerk,Manager); SELECT * FROM employee WHERE (job_id=A OR job_id=C) AND salary1000; 包含排序的查询语句 排序关键字 ORDER BY ASC 升序 缺省 DESC 降序 例子 SELECT * FROM employees ORDER BY hire_date DESC; SELECT * FROM employees ORDER BY hire_date DESC, salary ASC; 函数 函数的基本格式 Function_name(column|expression,[arg1,arg2,...]) 字符函数 LOWER,UPPER,INITCAP,CONCAT,SUBSTR,LENGTH, INSTR,LPAD,RPAD,TRIM,REPLACE. LOW强制小写 UPPER强制大学 INITCAP首字母大写 例子 SELECT * FROM employees WHERE LOWER(last_name) =blake; 字符串连接CONCAT