Oracle-SQL学习
分类:Oracle
1、DML:select、delete、update、insert语句;
2、DDL:create table、trunct、drop、alter table语句;
3、DCL:grant、revoke语句(数据库控制语句);
4、TCL:commit、rollback语句。
一、写基本的SQL SELECT语句(Writing Basic SQL SELECT Statements)
1、格式:SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
备注:(1)DISTINCT:去除重复行;
(2)alias:别名(as 别名、”别名”、‘ ’)如有特殊敏感字符如#$将其放入“”中;
(3)* /、+、- :优先级从左到右降低;
(4)NULL:算数表达式中包含NULL时,值为NULL;
(5)||连接符:将字段或字符串等连接起来;
(6)字符串或日期用‘’括起来(select last_name ||' Month salary '||salary Monthly from ..);
二、限制和排序数据(Restricting and Sorting Data)
(一)Restricting Data
1、格式:[WHERE condition(s)]; 用WHERE子句
2、比较条件关系符(Comparison Conditions):=、<>、>、<、>=、<=、!=、^=
3、其它比较条件关系符(Other Comparison Conditions):between and 、in(set)、like、is null
备注:(1)“%”代表0个或多个任意字符;“_”代表单个任意字符;
(2)‘%SA*_%’ ESCAPE ‘*’ 或 '%SA\_%' ESCAPE '\'代表子串为 “SA_”的字符串;
4、逻辑条件关系符(Logical Conditions):and、or、not(二)排序数据(sorting data)
1、格式:[ORDER BY {column, expr,alias} [ASC|DESC]]; 放在最后
ASC
升序(默认值)
DESC降序
2、排序可为字段位置序号,如2;字段名称;别名三、单行函数(Single-Row Functions)
1、格式:function_name [(arg1, arg2,...)]
2、组成:字符函数(character)、数值型函数(number)、日期函数(data)、转换函数(conversion)、general函数(general)
3、LOWER()小写;
UPPER()大写;
CONCAT('Hello', 'World') HelloWorld;
SUBSTR('HelloWorld',1,5) Hello;
LENGTH('HelloWorld') 10;
INSTR('HelloWorld', 'W') 6;
ROUND(45.926, 2) 45.93;
TRUNC(45.926, 2)
45.92;
MOD(1600, 300) 100;SYSDATE 返回系统日期及时间,可用alter sesson set nls_date_format = ‘yyyy-mm-dd Hh24:mi:ss’,日期型数据可以算术运算;
INITCAP(‘abce’)
Abce;
LPAD、RPAD固定长字符串,不足时用指定字符填充,L-左,R-右;4、自动转换数据类型:
(1)Varchar2 or char to number
(2)varchar2 or char to date
(3)number to varchar2
(4)date to varchar2
7、general函数:
• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, ..., exprn)
8、CASE表达式
(1)格式:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END(2)例子:
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROMemployees;
9、DECODE函数(1)例子一:
SELECT last_name, job_id, salary,
DECODE(job_id,
'IT_PROG',1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY -----为前面函数表达式的别名
FROMemployees;
(2)例子二:求税率SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROMemployees
WHEREdepartment_id = 80;
四、多表查询(Displaying Data from Multiple Tables)1、Oracle语法查询
(1)同等连接查询格式:
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
备注:同等连接N张表至少需要N-1个连接条件。
(2)非同等连接查询格式:
Ⅰ、SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column(+);
备注:无的端用(+)。
(3)自连接查询:
例子:
SELECT worker.last_name || ' works for '
|| manager.last_name
FROMemployees worker, employees manager
WHEREworker.manager_id = manager.employee_id ;
2、SQL 国际标准:(1)交叉连接( Cross Joins)笛卡尔积
格式例子:SELECT last_name, department_name
FROM
employees
CROSS JOIN departments ;等价于
SELECT last_name, department_name
FROM employees, departments;
(2)自然连接(Natural Joins):系统自动为字段名相同的字段建立连接,如类型不同则名字相同,则反回错误。例子:SELECT department_id, department_name,
location_id, city
FROMdepartments
NATURAL JOIN locations ;等价于
SELECT department_id, department_name,
departments.location_id, city
FROM departments, locations
WHERE departments.location_id = locations.location_id;
(3)USING子句连接(Joins with the USING Clause)SELECT e.employee_id, e.last_name, d.location_id
FROM
employees e JOIN departments d
USING (department_id) ;等价于
SELECT employee_id, last_name,
employees.department_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
(4)ON子句连接(Joins with the ON Clause)例子:
SELECT employee_id, city, department_name
FROM
employees e
JOINdepartments d
ONd.department_id = e.department_id
JOINlocations l
ONd.location_id = l.location_id;
(5)LEFT OUTER JOINSELECT e.last_name, e.department_id, d.department_name
FROM
employees e
LEFT OUTER JOIN departments dON
(e.department_id = d.department_id) ;
等价于SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id (+) = e.department_id;
(6)RIGHT OUTER JOINSELECT e.last_name, e.department_id, d.department_name
FROM
employees e
RIGHT OUTER JOIN departments dON
(e.department_id = d.department_id) ;
等价于SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id (+);
(7)FULL OUTER JOINSELECT e.last_name, e.department_id, d.department_name
FROM
employees e
FULL OUTER JOIN departments dON
(e.department_id = d.department_id) ;
等价于SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id (+) = d.department_id
UNION
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id (+);
五、成组查询(Aggregating Data Using Group Functions)1、语法:
SELECT [column,] group_function(column), ...
FROM
table
[WHERE condition][GROUP BY column]
[ORDER BY column];
2、HAVING子句的语法:
SELECT column, group_function
FROM
table
[WHERE condition][GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
例子:
SELECT
department_id, MAX(salary)
FROMemployees
GROUP BY department_idHAVING
MAX(salary)>10000 ;
备注:(1)avg(nvl(字段,0))所有人的平均值;
(2)SELECT COLUMN必在GROUP BY 中,GROUP BY中的字段可不在SELECT中;
(3)GROUP BY后字段时为多次分组统计,先以第一个字段分组统计,再在第一次的基础上分组;
(4)HAVING是组条件关键字;
(5)在所有分组函数中,均呼略NULL,用NVL等赋值。
六、子查询(Subqueries)
1、子查询语法:
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
2、分类:Single-row subqueries: Queries that return only one row from the inner SELECT statement
(1)只返回一行数据;
(2)使用比较操作符:=、>、>=、<、<=、<>;
(3)子查询无返回值,则主查询亦无返回值。
Multiple-row subqueries: Queries that return more than one row from the inner SELECT statement
(1)只返回多于一行数据;
(2)使用比较操作符:in、any、all;
例子:
SELECT employee_id, last_name, job_id, salary
FROM
employees
WHEREsalary < ALL ――――not in
(SELECT salary
FROMemployees
WHERE job_id = 'IT_PROG')
ANDjob_id <> 'IT_PROG';
(3)子查询无返回值,则主查询亦无返回值。备注:(1)< any表示小于最大的值;
(2)< all表示小于最小的值。