Oracle SQL 高级编程
一、常见操作
1、解锁
alter user scott account unlock;
2、改密码
alter user scott identified by tiger;
3、查看存在的用户
select * from dba_users;
4、当前用户下存在的表
select * from user_tables;
二、help index(sql *plus命令列表)
有关某个主题的帮助, 请键入 help <主题>
常用的/、list、@(start)、@@、CONNECT、define、describe、disconnect、edit、execute、exit、list、set、show、spool、sqlplus
三、set命令
关闭sql *plus设置的命令不再被保留。
解决方法：
sql *plus启动会读两个文件($oracle_home/sqlplus/admin目录下)：glogin.sql、login.sql（优先级高）
所以可以创建自己的login.sql文件，常见的设置如下：
--_editor是预定义变量制订了当你使用edit命令的时候启动哪个编辑器、edit命令调用编辑器
define _editor = E:\Notepad++\notepad++.exe
set linesize 3000
set pagesize 1000
set timing on
set null
--每行左边的提示符
set sqlprompt '&_user@&_connect_identifier> '
四、执行命令
sql语句和sql *plus命令可以在sql *plus中执行
sql语句：分号(命令末尾或空行)或正斜线（必须空行）
sql *plus命令：直接敲回车，如describe和connect
五、5个核心的sql语句
***写在前面：建议写表名时把用户名也写上去，比如from emp，即使你是已scott登录的，也写成from scott.emp，养成好的习惯。
1、select语句
from子句（表、驶入、物化视图、分区、子分区、子查询）：处理联结语句的顺序：交叉联结（笛卡尔乘积）、内联结、外联结；
where子句(比较结果true、false、null(当作false对待))；
group by子句（select 列表中的任何非聚合字段都必须包括在group by表达式中）；
having子句；
order by子句（结果集很大时、排序是个开销很大的步骤）。
示例：
SELECT c.customer_id, COUNT(o.order_id) AS orders_ct
FROM oe.customers c
JOIN oe.orders o ON c.customer_id = o.customer_id
WHERE c.gender = 'F'
GROUP BY c.customer_id
HAVING COUNT(O.ORDER_ID) > 4
ORDER BY orders_ct, c.customer_id;
select列表（标量子查询在结果集中的每一行结果产生时都要执行一遍，代价大！）
SELECT c.customer_id, c.cust_first_name ||' ' ||c.cust_last_name, (
SELECT e.last_name
FROM hr.employees e
WHERE e.employee_id = c.account_mgr_id
)
acct_mgr
FROM oe.customers c;
2、insert语句
单表插入：子查询插入非常灵活
INSERT
INTO scott.bonus
(
ename, job, sal
)
SELECT ename, job, sal * .10
FROM scott.emp;
多表插入：
create table small_customers
(customer_id number,
sum_orders number)
;
create table medium_customers
(customer_id number,
sum_orders number)
;
create table large_customers
(customer_id number,
SUM_ORDERS NUMBER)
;
INSERT ALL
WHEN sum_orders < 10000 THEN
INTO small_customers WHEN sum_orders >= 10000 AND sum_orders < 100000 THEN
INTO medium_customers ELSE
INTO large_customers
SELECT customer_id, SUM(order_total) sum_orders
FROM oe.orders
GROUP BY customer_id ;
3、update语句
表达式更新一个单列：
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 90 ;
子查询更新一个单列：
UPDATE hr.employees employees
SET salary =
(
SELECT employees2.salary
FROM employees2
WHERE employees2.employee_id = employees.employee_id AND EMPLOYEES.SALARY != EMPLOYEES2.SALARY
)
WHERE department_id = 90 ;
在where子句中使用子查询确定要更新的数据行来更新单列：
UPDATE hr.employees
SET salary = salary * 1.10
WHERE department_id IN
(
SELECT department_id
FROM DEPARTMENTS
WHERE department_name = 'Executive'
) ;
使用select语句定义表及列的值来更新表：
UPDATE
(
SELECT e1.salary, e2.salary new_sal
FROM hr.employees e1, employees2 e2
WHERE e1.employee_id = e2.employee_id AND E1.DEPARTMENT_ID = 90
)
SET salary = new_sal;
子查询更新多列：
UPDATE hr.employees employees
SET
(
salary, commission_pct
)
=
(
SELECT employees2.salary, .10 comm_pct
FROM employees2
WHERE employees2.employee_id = employees.employee_id AND EMPLOYEES.SALARY != EMPLOYEES2.SALARY
)
WHERE department_id = 90 ;
4、delete语句
使用where子句中的筛选条件来从指定表中删除行：
DELETE
FROM employees2
WHERE department_id = 90;
使用from 子句中的子查询来删除行：
DELETE
FROM
(
SELECT *
FROM employees2
WHERE department_id = 90
);
使用where子句中的子查询来从指定表中删除行：
DELETE
FROM employees2
WHERE department_id IN
(
SELECT department_id
FROM hr.departments
WHERE department_name = 'Executive'
);
5、merge语句（Oracle9i新增的语法,用来合并UPDATE、INSERT、delete语句，常用在数据仓库中移动大量的数据）
CREATE TABLE dept60_bonuses
(
employee_id NUMBER ,bonus_amt NUMBER
);
merge INTO dept60_bonuses b USING
(
SELECT employee_id, salary, department_id
FROM hr.employees
WHERE department_id = 60
)
e ON (b.employee_id = e.employee_id)
WHEN matched THEN
UPDATE
SET b.bonus_amt = e.salary * 0.2
WHERE b.bonus_amt = 0
DELETE
WHERE (e.salary > 7500) WHEN NOT matched THEN
INSERT
(
b.employee_id, b.bonus_amt
)
VALUES
(
E.EMPLOYEE_ID, E.SALARY * 0.1
)
WHERE (e.salary < 7500);
