ORACLE SQL 语句速查

oracleSQL: Structure Query Language常见数据类型Char(n) 定长字符串Varchar2(n) 变长字符串Number(m,p) 数字类型Date 日期类型SQL语句分类1.查询语句:SELECT2.DML语句(数据操作语言)INSERT/UPDA TE/DELETE/MERGE3.DDL语句(数据定义语言)CREATE/ALTER/DROP/TRUNCATE4.DCL语句(数据控制语言)GRANT/REVOKE5.事务控制语句:COMMIT/ROLLBACK/SA VEPOINT一.SELECT 语句1.基本查询格式SELECT {*,column[alias], ...}FROM table;例子SELECT * FROM departments;SELECT department_id,department_nameFROM departments;带算数表达式的查询语句SELECT last_name,salary,salary*12FROM employees;SELECT last_name,salary,salary*12+100FROM 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;去重关键字DISTINCTSELECT DISTINCT department_id FROM employees;取得日期格式的查询语句SELECT * FROM v$nls_parameters;取得当前日期日期的查询语句SELECT sysdate FROM DUAL;2.带限制条件的查询语句格式SELECT [DISTINCT] {*, column [alias], ...}FROM table[WHERE condition(s)]包含比较操作符的查询语句SELECT last_name FROM employees WHERE salary<=1500;常见操作符> < >= <= = <>BETWEEN ...ANDIN (list)LIKEIS 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条件的逻辑运算逻辑运算符ANDORNOT例子SELECT * FROM employee WHERE salary>1 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 salary>1000;包含排序的查询语句排序关键字ORDER BYASC 升序缺省DESC 降序例子SELECT * FROM employees ORDER BY hire_date DESC;SELECT * FROM employees ORDER BY hire_date DESC, salary ASC;2.函数函数的基本格式Function_name(column|expression,[arg1,arg2,...])字符函数LOWER,UPPER,INITCAP,CONCA T,SUBSTR,LENGTH,INSTR,LPAD,RPAD,TRIM,REPLACE.LOW强制小写UPPER强制大学INITCAP首字母大写例子SELECT * FROM employees WHERE LOWER(last_name) ='blake';字符串连接CONCA T取子串SUBSTR找到子instr INSTR补位函数LPAD去除特殊trim TRIMreplace REPLACE例子SELECT replace('oracle SQL','oracle','training') test FROM DUAL把oracle SQL中的oracle 替换成trainingSELECT last_name,CONCAT(last_name job) CAN, LENGTH(last_name) LEN,INSTR(last_name,'A') INSFROM employeesWHERE SUBSTR(job_id,1,5)='SALES'四舍五入round截取trunc求模mod例子SELECT round(45.123,2) “小数点后两位”,round(45.123,0) “取整”FROM SYS.DUAL SELECT MOD(salary,1000) FROM employees;日期类型函数SELECT sysdate FROM DUAL;oracle中日期类型的缺省显示格式是DD-MM-YY日期计算函数MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUCE,MONTHS_ BETWEEN转换函数类型转换只能发生在数字与字符或者日期与自负之间，数字与日期之间不能发生转换to_char---TO_CHAR(date,'fmt')TO_DA TETO_NUMBERTo_char日期转换如果有一个fm元素，可以去掉前面的零SELECT last_name,TO_CHAR(hire_date,'fmDD Month YYYY') AS HIREDATEFROM employees;数字转换SELECT TO_CHAR(salary,'$99.99') FROM employees;TO_DATESELECT TO_DATE('01-5月-04') FROM DUAL;SELECT TO_DATE('2004-5-1' ,'yyyy-mm-dd') FROM DUAL;TO_NUMBERSELECT TO_NUMBER('123456') FROM DUALSELECT TO_NUMBER('$123,456.00','$999,999,00') FROM DUAL;其他函数NVL(expr1,expr2)将空值转换为一个替换值NVL2(expr1,expr2,expr3)如果为空，返回expr2，不为空，返回expr3NULLIF（expr1，expr2）如果两个值相等，返回空，不相等，返回第一个值CASE表达式SELECT last_name,job_id,salaryCASE job_idWHEN 'IT_PROG' THEN 1.10*salaryWHEN 'ST_CLERK' THEN 1.15*salaryWHEN 'SA_REP' THEN 1.20*salaryELSE salaryEND "REVISED_SALARY"FROM employees;DECODE和CASE具有相同功能但是用法不同的函数SELECT job_id,salary,DECODE(job_id, ' A' ,salary*1.1,'B',salary*1.2,'C',salary*1.2,salary)REVISED_SALARYFROM employees;分组函数A VG求平均值COUNT求记录数MAX求最大值,对于日期类型，越早的日子被认为值越小，空值被当做无穷大处理MIN求最小值SUM求和例子SELECT A VG(commission_pct)FROM employees;GROUP BY格式SELECT column,group_functionFROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column]规则：1.分组函数不能出现在WHERE语句中，相同的功能应该由HA VING关键字实现2.出现在SELECT列表中的字段如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中3.在GROUP BY子句中出现的字段，可以不出现在查询列表中常见错误语句SELECT department_id,count(last_name) FROM employees;HA VING例子SELECT department_id,MAX(Salary)FROM employeesGROUP BY department_idHA VING MAX(salary)>1000;SELECT column,group_functionFROM table[WHERE condition][GROUP BY group_by_expression][HA VING group_condition][ORDER BY column];3.多表连接多表连接的种类等值连接，非等值连接，外连接，自连接常见错误如果做多表连接的时候没写WHERE条件，会得到两个表的笛卡尔乘积等值连接SELECT e.employee_id, st_nameFROM employees e ,departments dWHERE e.department_id=d.department_id非等值连接SELECT st_name,e.salary ,j.grade_levelFROM employees e,job_grades jWHERE e.salaryBETWEEN j.lowest_sal AND j.highest_sal;外连接左外连接将产生以表达式右边字段为依据的查询结果，也就是说，显示将以右边字段中的所有数据作为依据，显示右边表的所有数据，一集左边表与右边匹配的数据。