SQL基础疏导手册

原创 M 2023-03-25

163

-----------------------------------------------编著:孟国庆
一.检索查询
1.SQL语句书写标准
1)不区分大小写
2)可以换行书写
3)关键词不能简写或分割开跨行写
4)通常一个子句一行
5)排版可增加可读行
6)开发工具里，结束语句用;是可选的。执行多个语句用；分割开它们
7)SQL*Plus里必须使用；表示一个语句结束

2.基本查询语句
语法：SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
1)SELECT子句指定你需要查询的列
2)FROM子句指定列的来源----表或视图
3)*选择所有列
例1
SELECT * FROM departments;
例2 指定列
select department_id , location_id from departments;

3.对数字,日期类型的使用算术操作符
Multiply *
Divide /
Subtract -
Add +
使用算术操作符
select last_name , salary , salary + 300 from employees;
select EMPLOYEE_ID, FIRST_NAME , SALARY from employees;
操作符优先级
select last_name , salary , 12*salary+100 from employees;
select last_name , salary , 12*(salary+100) from employees;

4.空值
null是一个不确定,未赋值,未知的值
null不等于零或空白
select last_name , job_id , salary , commission_pct from employees;
select count(COMMISSION_PCT) from employees;
空值算术操作
select last_name,12*salary*commission_pct from employees;

5.列别名
1)重命名列标题
2)有益于运算
3)直接跟在列后，可在列和别名之间用as增强可读性
4)包含空格，特殊字符，区分大小写必须用双引号引起来
select last_name as a, commission_pct b from employees;
select last_name "Name" , salary*12 "Annual_Salary" from employees;

6.连接运算符
1)连接列和字符串，列和列，字符串和字符串
2)使用||作连接标志
3)连接操作之后的列是字符型
select last_name || job_id as "Employees" from employees;
字面字符串
1)是包含在select中的字符，数字，日期值
2)字符，日期类型的必须使用单引号
3)每行每次返回一行字符串
select last_name || 'is a' || job_id as "Employee Details" from employees;
select department_name || q'[ Department's Manager id:]' || manager_id as "Department and Manager" from departments;

7.重复行
1)默认显示查询出的所有行，包括重复行
2)使用DISTINCT可以去除重复行
select department_id from employees;
select DISTINCT department_id from employees;

8.使用desc命令查看表结构
desc employees

二.限制和排序数据
1.限制行
1)where子句
2)比较条件用=, <=, BETWEEN, IN, LIKE和null
3)逻辑条件用and,or,not操作符

2.限制行的查询使用where子句
语法：SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];
select employee_id , last_name , job_id , department_id from employees where department_id=90;
字符与日期
字符,日期使用单引号引起来
字符区分大小写，日期格式敏感
默认日期显示格式:DD-MON-RR
select last_name , job_id , department_id from employees where last_name = 'Whalen';
select last_name from employees where hire_date='17-MAR-96';

3.比较操作符
简单比较运算符:< , > , = , <= , >= , <>
复杂比较运算符:
1)BETWEEN ...AND...:介于两个值之间（包括）
2)IN(set):匹配任何值列表
3)LIKE:匹配字符模式
4)IS NULL:为空值

4.使用比较运算符
select last_name , salary from employees where salary <=3000;
1)使用between条件，限制数据在某个范围用between操作符
select last_name , salary from employees where salary between 2500 and 3000;
2)使用IN条件
使用IN关系条件限制数据一般是可以被穷举的值
select employee_id , last_name , salary , manager_id from employees where manager_id in (100,101,201);
3)使用Like条件
使用like条件来执行通配符搜索有效的字符串值
搜索条件可以包含文字字符或数字 %表示零或多个字符，_表示一个字符
select first_name from employees where first_name like 'S%';
select last_name from employees where last_name like '_o%';
4)使用NULL条件
使用IS NULL或IS NOT NULL 进行比较操作
select last_name , manager_id from employees where manager_id is null;

5.逻辑条件
and 如果这两个条件都成立返回true
or 任意一个条件成立返回true
not 如果下列条件为false ,返回true
例1 and
select employee_id , last_name , job_id , salary from employees where salary >=10000 and job_id like '%MAN%';
例2 or
select employee_id , last_name , job_id , salary from employees where salary >=10000 or job_id like '%MAN%';
例3 not
select last_name , job_id from employees where job_id not in ('IT_PROG' , 'ST_CLERK' , 'SA_REP');

6.操作符规则优先原则
1)算术运算符+-*/
2)串联运算符 ||
3)比较运算符 = > >= < <=
4)IS [NOT] NULL, LIKE, [NOT] IN
5)[NOT] BETWEEN
6)不等于 <> ^= !=
7)逻辑条件not
8)and
9)or
注:可以使用括号强制改变规则优先顺序.
例1
select last_name , job_id , salary from employees where job_id='SA_REF' OR job_id='AD_PRES' AND salary > 15000;
select last_name , job_id , salary from employees where (job_id ='SA_REP' OR job_id='AD_PRES') AND salary>=15000;

7.使用order by子句
排序检索行的order by 子句:asc 升序排列，默认 , desc 降序排列
order by子句放在select语句末尾
例1
select last_name , job_id , department_id , hire_date from employees ORDER BY hire_date;
降序排序
select last_name , job_id , department_id , hire_date from employees ORDER BY hire_date desc;
排序用列别名
select employee_id ,last_name , salary*12 annsal from employees ORDER BY annsal;
排序用列所在数字位
select last_name , job_id , department_id , hire_date from employees ORDER BY 3;
多列排序
select last_name , department_id , salary from employees ORDER BY department_id , salary desc;
注意：
1)数字按照数字的大小顺序排列如升序 1到100
2)日期按照时间顺序排列如升序 1月1号到12月31号
3)字符按照字母顺序排列如升序 A到Z
4)排序认为Null是最大值

8.替代变量
1)临时变量用&，调用替代变量用&&
2)替代变量可用在:
where子句 , order by 子句 , 列表达式 , 表名 , 整个语句
3)使用变量前缀的符号（＆），以提示用户输入值
select employee_id , last_name , salary , department_id from employees where employee_id = &employee_num;
4)字符和日期使用变量要用单引号引起来
select last_name , department_id , salary*12 from employees where job_id = '&job_title';
5)列，表达式，文本
select employee_id,last_name, job_id,&column_name from employees where &condition ORDER BY &order_column;
6)如果你想重用的变量值使用&&
select employee_id , last_name , job_id , &&column_name from employees ORDER BY &column_name;

9.define与verify命令
DEFINE命令创建和分配一个值到一个变量
define employee_num = 200
select employee_id , last_name , salary , department_id from employees where employee_id = &employee_num;
UNDEFINE 取消已定义的变量
undefine employee_num
变量替换的过程新旧值是否显示用:set verify on|off
set verify on|off
select employee_id , last_name , salary from employees where employee_id = &employee_num;

三.单行函数
1)使用函数为了操作数据
2)将输入的变量处理，每行返回一个结果
3)处理返回的每一行
4)一行返回一个结果
5)可以转化数据类型
6)能嵌套使用
7)传入的变量可以是列的值，也可以是表达式

1.字符大小写函数
1)LOWER('SQL Course') 全部小写
2)UPPER('SQL Course') 全部大写
3)INITCAP('SQL Course') 首字母大写
例1
select employee_id , last_name , department_id from employees where last_name = 'higgins';
select employee_id , last_name , department_id from employees where LOWER(last_name) = 'higgins';

2.字符处理函数
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary, 10, '*') 24000*****
REPLACE ('JACK and JUE','J','BL') BLACK and BLUE
TRIM('H' FROM 'HelloWorld') elloWorld
例1
select employee_id , concat(first_name , last_name) name,
job_id,LENGTH(last_name),
instr(last_name,'a') "Contains 'a'?"
from employees
where SUBSTR(job_id,4)='REP';

3.数字函数
ROUND:按照指定的小数位四舍五入 ROUND(45.926, 2) 45.93
TRUNC:按照指定的小数位截断数据 TRUNC(45.926, 2) 45.92
MOD:两数相除，返回余数 MOD(1600, 300) 100
例1使用ROUND函数
select ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) from dual;
例2使用TRUNC函数
select TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1) FROM DUAL;
例3使用MOD函数
select last_name,salary , MOD(salary , 5000) from employees where job_id = 'SA_REP';

4.日期
1)Oracle数据库存储日期格式：世纪,年,月,日,时,分,秒
2)默认显示: DD-MON-RR.
3)默认显示年的最后2个数字
select last_name , hire_date from employees where hire_date < '01-FEB-88';

5.SYSDATE函数:是一个返回的函数搭配：Date ,Time
SELECT sysdate FROM dual;

6.日期算术运算:
1)现有日期加上或减去一个数值，可以得到一个日期.
2)两个日期相减，可以得到两个日期间的天数
3)将值除以24，可以得到小时数
select last_name , (sysdate-hire_date)/7 as WEEKS from employees where department_id = 90;

7.日期处理函数
MONTHS_BETWEEN 两日期间有多少个月 MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 19.6774194
ADD_MONTHS 指定日期增加月数 ADD_MONTHS (‘31-JAN-96',1) '29-FEB-96'
NEXT_DAY 指定日期下一天 NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95'
LAST_DAY 月最后一天 LAST_DAY ('01-FEB-95') '28-FEB-95'
ROUND 日期舍入 ROUND(SYSDATE,'MONTH|YEAR') 01-AUG-03|01-JAN-04
TRUNC 日期截取 TRUNC(SYSDATE ,'MONTH|YEAR') 01-JUL-03|01-JAN-03

四.转换函数与条件表达式
1.转换函数:隐式转换，显示转换
1)日期元素
YYYY 数字形式年份，四位数
YEAR 年份的全拼格式
MM 数字形式月份，两位
MONTH 月份的全拼写格式
MON 月份的三位缩写格式
DY 星期的三位缩写格式
DAY 星期的全拼写格式
DD 数字形式日期，两位
2)时间元素
HH24:MI:SS AM 15:45:32 PM

2.to_char函数处理日期
select last_name , TO_CHAR(hire_date , 'fmDD Month YYYY') as HIREDATE from employees;

3.to_char函数处理数字
9 代表一个数值
0 显示0
$ 使用$符号
L 使用本地货币符号
. 小数点
, 千分位分隔符
例1
select TO_CHAR(salary , '$99,999.00') salary from employees where last_name = 'Ernst';

4.to_number与to_date
1)转字符为数字用to_number函数
2)语法:TO_NUMBER(char[, 'format_model'])
3)转字符为日期用to_date函数
语法:TO_DATE(char[, 'format_model'])
注意：to_date函数里可用fx选项精确匹配之后的字符或日期是否忽略多个空格还是只能有1个空格
例1要查找1990年以前雇用的员工，使用RR日期格式，无论命令是在1999年还是现在运行，都会产生相同的结果
select last_name , TO_CHAR(hire_date , 'DD-Mon-YYYY')
from employees
where hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');

5.嵌套函数
1)单行函数可嵌套多层
2)嵌套函数的计算过程是从最内层向最外层运算
语法:F3(F2(F1(col,arg1),arg2),arg3)
例1
select last_name , UPPER(CONCAT(SUBSTR(LAST_NAME,1,8),'_US')) FROM employees where department_id = 60;
例2
select to_char(round((salary/7),2),'99G999D99','NLS_NUMERIC_CHARACTERS='',.'' ')"Formatted_Salary" from employees;

6.空值函数
1)NVL (expr1, expr2) 如果expr1为空则返回expr2否则返回expr1
2)NVL2 (expr1, expr2, expr3) 如果expr1不为空返回expr2，为空返回expr3
3)NULLIF (expr1, expr2) 如果相同返回空否则返回expr1
4)COALESCE (expr1, expr2, ..., exprn) expr1空返回expr2 , expr2为空返回expr3............exprn
注:NVL函数适用于数字,字符,日期 ;数据类型必须匹配.
例1:NVL
select last_name , salary , NVL(commission_pct,0) , (salary*12) + (salary*12*NVL(commission_pct,0)) AN_SAL
from employees;
例2:NLV2
select last_name , salary , commission_pct, NVL2(commission_pct , 'SAL+COMM' , 'SAL') income
from employees where department_id in (50,80);
例3:NULLIF
select first_name , LENGTH(first_name) "expr1" , last_name , LENGTH(last_name) "expr2" ,
NULLIF(LENGTH(first_name),LENGTH(last_name)) result
from employees;
例4:COALESCE函数
select last_name , employee_id , coalesce(to_char(commission_pct) ,
to_char(manager_id) , 'No conmmission and no manager')
from employees;
注:使用COALESCE函数而不使用NVL函数的优势在于，COALESCE函数可以使用多个替换值

7.条件表达式
提供IF-THEN-ELSE逻辑判断
2种方式: CASE表达式 , DECODE函数
CASE语法:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
例1
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"
from employees;
DECODE语法:
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
例2
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
from employees;
例3
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
from employees
where department_id = 80;

5.分组函数
常用分组函数
AVG , COUNT , MAX , MIN , STDDEV , SUM , VARIANCE
分组函数语法
SELECT group_function(column), ... FROM table [WHERE condition] [ORDER BY column];

使用AVG,SUM函数
注意:对数字使用AVG,SUM
select AVG(salary),MAX(salary),MIN(salary),sum(salary) from employees where job_id LIKE '%REP%';

使用MIN,MAX函数
注意:对数字，字符，日期类型的可使用MIN,MAX
select MIN(hire_date) , MAX(hire_date) from employees;

COUNT函数
返回总行数
select COUNT(*) from employees where department_id = 50;
返回非空的总行数
select COUNT(commission_pct) from employees where department_id = 80;

DISTINCT参数
显示唯一部门数量
返回非空唯一总行数
例
select count(DISTINCT department_id) from employees;

空值
默认分组忽略空值
nvl可以强制分组函数处理空值
select AVG(commission_pct) from employees;
对比
select AVG(NVL(commission_pct , 0)) from employees;

创建分组数据group by语法
可以把表的行分割成多个小组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
例
select department_id , AVG(salary) from employees GROUP BY department_id;
例
select AVG(salary) from employees GROUP BY department_id;
注:非分组列出现在select列表必须用group by
分组列可以不出现在select列表中

多列分组
例1
select department_id , job_id , SUM(salary)
from employees
where department_id > 40 GROUP BY department_id ,
job_id ORDER BY department_id;

使用HAVING限制分组
行被分组
分组函数已使用
匹配having的结果被显示
语法：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
例1
select department_id , MAX(salary) from employees GROUP BY department_id HAVING MAX(salary)>10000;
例2
select job_id , SUM(salary) PAYROLL
from employees
where job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);

嵌套
例：显示最大的平均薪水
select MAX(AVG(salary)) from employees GROUP BY department_id;

容易出错
select department_id , count(last_name) from employees;
报错
ORA-00937: not a single-group group function
select department_id , job_id , count(last_name) from employees GROUP BY department_id;
报错
ORA-00979: not a GROUP BY expression
select department_id, AVG(salary) from employees where AVG(salary)>8000 GROUP BY department_id;
报错
ORA-00934: group function is not allowed here

六.多表查询
1.连接类型:自然连接 , 外连接 , 交叉连接
连接语法:
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];

2.限定模糊列名
1)使用表前缀限制列名
2)使用前缀可提高性能
3)表别名可替代完整的表前缀
4)表别名给表一个简短的名字
5)使代码更短，使用更少内存
6)列别名区分不同列，但在不同表里

3.创建自然连接
1)自然连接基于连个表的所有列有相同的名字
2)从两个表选取所有匹配列的相同值
3)如果同名列数据类型不匹配就会返回错误
例
select department_id , department_name , location_id , city
from departments
NATURAL JOIN locations;

4.USING语句
1)如果个别列有相同名字但数据类型不同可用USING语句指定等值连接的列
2)有多列时Using只匹配一列
3)NATURAL JOIN 与 USING是互斥的
例
select employee_id , last_name , location_id , department_id
from employees JOIN departments
USING (department_id);
注：不要限定USING子句中使用的列。
如果在SQL语句的其他地方使用了相同的列，不要使用别名
例
SELECT l.city, d.department_name
FROM locations l JOIN departments d
USING (location_id)
WHERE d.location_id = 1400; ###############################会报错

5.ON语句
1)natural连接的条件基本上是同名列的等值连接
2)为了指定任一条件或指定列用于连接可以用ON子句
3)连接条件是与其他的查询条件分开的
4)on子句更加容易理解
例
select e.employee_id , e.last_name , e.department_id , d.department_id , d.location_id
from employees e join departments d
on (e.department_id = d.department_id);
例:三表连接
select employee_id , city , department_name from employees e
join departments d
on d.department_id = e.department_id
join locations l
on d.location_id = l.location_id;

6.连接附加条件
where语句或and语句可附加在连接语法里
select e.manager_id , e.employee_id , e.last_name , e.department_id , d.department_id , d.location_id
from employees e join departments d
on (e.department_id = d.department_id)
and e.manager_id=149;
或
select e.employee_id , e.last_name , e.department_id , d.department_id , d.location_id
from employees e join departments d
on (e.department_id = d.department_id)
where e.manager_id = 149;

7.自连接
自连接用ON
select manager.employee_id , worker.manager_id , worker.last_name emp , manager.last_name mgr
from employees worker join employees manager
on (worker.manager_id = manager.employee_id);
100 100 Russell King
146 146 King Partners

8.不等连接
select e.last_name , e.salary , j.grade_level
from employees e join job_grades j
on e.salary
between j.lowest_sal and j.highest_sal;

9.内连接与外连接
只返回两个表中匹配条件行的连接，是内连接
两个表的内连接，返回行包含在左[右]表中不匹配的行，就是左[右]外连接
两个表的内连接，返回行包含左表和右表中不匹配的行，就是完全外连接

1)左外连接 LEFT OUTER JOIN
select e.last_name , e.department_id , d.department_name
from employees e left outer join departments d
on (e.department_id = d.department_id);

2)右外连接 RIGHT OUTER JOIN
select e.last_name , d.department_id , d.department_name
from employees e right outer join departments d
on (e.department_id = d.department_id);

3)全外连接 FULL OUTER JOIN
select e.last_name , d.department_id , d.department_name
from employees e full outer join departments d
on (e.department_id=d.department_id);

10.笛卡尔链接
连接条件省略
连接条件无效
一个表所有行连接到另一个表所有行
select count(*) from employees,departments;
注：生产中要尽量避免笛卡尔连接

11.交叉连接
select last_name , department_name
from employees cross join departments;

七.子查询
子查询语法
SELECT select_list FROM tabl WHERE expr operator (SELECT select_list FROM table);
注意:在主查询执行之前,子查询(内查询)会执行一次
子查询结果被用于主查询(外查询)

子查询使用向导
1)子查询用括号括起来
2)习惯把子查询放在比较操作符右边
3)单行操作用单行子查询，多行用多行子查询
4)子查询中不需要order by除非执行TOP-N查询
例
select last_name , salary
from employees
where salary > (select salary from employees where last_name = 'Abel');

单行子查询
1)仅返回一行
2)使用单行比较符
select last_name , job_id , salary
from employees
where job_id =
(select job_id
from employees
where last_name = 'Taylor'
and job_id='SA_REP')
and salary >
(select salary
from employees
where last_name = 'Taylor'
and job_id='SA_REP);

子查询的分组函数
select last_name , job_id , salary
from employees
where salary = (select MIN(salary) from employees);

having子查询
1)先执行子查询
2)返回结果给主查询的having语句
select department_id , min(salary)
from employees
group by department_id
having min(salary)>
(select min(salary)
from employees
where department_id=50)

子查询内无结果
select last_name , job_id
from employees
where job_id =(select job_id from employees where last_name = 'Haas')

多行子查询
IN 等于列表中的任何成员
ANY 前面必须有=，!=，>，<，<=，>=
ALL 前面必须有=，!=，>，<，<=，>=
注: >any 大于子查询结果中的最小值
<any 小于子查询结果中的最大值
>all 大于子查询结果中的最大值
<all 小于子查询结果中的最小值

Any操作
select employee_id , last_name , job_id , salary
from employees
where salary <any
(select salary
from employees
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';

all操作
select employee_id , last_name , job_id , salary
from employees
where salary <all
(select salary
from employees
where job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

exists操作
EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False
not exists（不返回结果集，为真） exists与in的使用效率问题，通常情况下采用exists要比in效率高，因为in不走索引，
但要看实际情况具体使用：in适合于外表大而内表小的情况，exists适合于外表小而内表大的情况
select * from departments
where not exists
(select * from employees
where employees.department_id = departments.department_id);

null值问题
select emp.last_name
from employees emp
where emp.employee_id not in
(select mgr.manager_id
from employees mgr);

八.set操作
1.union:并集,去重
select employee_id , job_id
from employees
union
select employee_id , job_id
from job_history;

2.union all:并集,不去重
select employee_id , job_id
from employees
union all
select employee_id , job_id
from job_history
ORDER BY employee_id;

3.INTERSECT:交集
select employee_id , job_id
from employees
INTERSECT
select employee_id , job_id
from job_history;

4.MINUS:A不包含B的部分
select employee_id from employees MINUS select employee_id from job_history;

5.select列匹配
select location_id , department_name "Department" ,
TO_CHAR(NULL) "Warehouse location"
from departments
UNION
select location_id , TO_CHAR(NULL) "Department" ,state_province
from locations;
两个查询的列要匹配，如果不匹配可以用TO_CHAR(NULL) "********" 的方式进行列匹配
或
select employee_id , job_id , salary
from employees
UNION
select employee_id , job_id , 0
from job_history;

九.数据操纵语言
1.插入新行
insert into departments(department_id , department_name , manager_id , location_id)
values(112 , 'public Relations' , 100 , 1700);

2.插入null值
insert into departments (department_id , department_name )
values (31 , 'Purchasing');

3.插入特定值
insert into employees (employee_id , first_name ,
last_name , email , phone_number ,
hire_date , job_id , salary , commission_pct ,
manager_id ,department_id)
values(216 , 'Louis1' , 'Popp1' , 'LPOPP1' , '515.124.4567' ,
SYSDATE , 'AC_ACCOU' , 6900 , null , 203 , 71);

4.时期插入法
INSERT INTO employees
VALUES (207,
'Den', 'Raphealpo',
'DRAPHEAL', '515.127.4569',
TO_DATE('FEB 3,1999', 'MON DD, YYYY'),
'SA_REP', 11000, 0.2, 111, 111);

5.建立脚本
INSERT INTO departments
(department_id , department_name ,location_id)
values (&department_id , &department_name , &location);

6.从其他表复制行
create table sales_reps(id NUMBER(6), name VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2));
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

7.update语法
update table set column=values[,column=value,...] [where condition];

update employees set department_id = 50
where employee_id = 113;
缺少where则修改所有的行
update copy_emp set department_id = 110;
1)利用子查询
update employees
set job_id = (select job_id
from employees
where employee_id = 205),
salary = (select salary
from employees
where employee_id = 205)
where employee_id = 113;
2)从其他表子查询
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);

3.delete语法
delete [from] table [where condition];
1)删除行
delete from departments where department_name = 'Finance';
2)删除所有行
delete from copy_emp;
3)删除行的子查询
DELETE FROM employees
WHERE department_id =(SELECT department_id
FROM departments
WHERE department_name
LIKE '%Public%');
4)从表中删除所有行，但保留表结构
truncate table table_name;

4.事务开始与结束
commit , rollback
1)语句执行完自动退出
2)使用exits正常退出

3)显示事务控制语句LIST
rollback to savepoint B

4)用savepoint对当前事务做个保存点
回到指定保存点用 rollback to savepoint
语法
update .....
savepoint update_done;
INSERT .....
rollback to update_done;

5.隐式事务
1)事务自动提交 ddl语句，dcl语句
2)事务结束之前数据状态：数据可以恢复，当前用户可以看到dml操作结果其他用户看不到
3)事务结束数据状态：更改的数据保存到数据库落盘
DELETE FROM employees
WHERE employee_id = 99999;
INSERT INTO departments
VALUES (290, 'Corporate Tax', NULL, 1700);
commit; 事务结束

delete from copy_emp;
rollback;

列
create table test as select * from employyes;
delete from test;
rollback;
delete from test where employee_id = 205;
select * from test where employee_id=205;
commit;

6.SELECT 语句中的 FOR 更新子句
1)锁定 EMPLOYEE 表中job_id所在的行SA_REP
SELECT employee_id, salary, commission_pct, job_id
FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE
ORDER BY employee_id;
2)使用commit\rollback结束事务
如果 SELECT 语句尝试锁定被其他用户锁定，数据库会等到该行可用，然后返回 SELECT 的结果陈述。
SELECT e.employee_id, e.salary, e.commission_pct
FROM employees e JOIN departments d
USING (department_id)
WHERE job_id = 'ST_CLERK'
AND location_id = 1500
FOR UPDATE
ORDER BY e.employee_id;

INSERT 向表中添加新行
UPDATE 修改表中的现有行
DELETE 从表中删除现有行
TRUNCATE 从表中删除所有行
COMMIT 使所有挂起的更改永久化
SAVEPOINT 用于回滚到保存点标记
ROLLBACK 放弃所有挂起的数据更改
FOR UPDATE clause in SELECT 锁定由 SELECT 查询标识的行

十.使用DDL建立与管理表
要点：1.描述主要数据库对象
2.查看表结构
3.描述列定义的可用数据类型
4.建立简单的表
5.了解约束如何在创建表的的时候建立
6.描述模式对象如何工作
1.数据对象：
Table: 存储的基础单元，由行和列组成
View：一个或多个表中的数据逻辑子集
Sequence：数值生成器
Index：提高某些查询的效率
Synonym：对象的别名

2.命名原则：
1)表名列名命名必须
2)以字母开头
3)必须为1-30字符长度
4)只能包含A–Z, a–z,0–9, _, $, 和#字符
5)同一用户下，不能重名
6)不能使用oracle服务器保留字
语法:CREATE TABLE [schema.]table(column datatype [DEFAULT expr][, ...]);

3.默认值：
1)插入行时，为列指定缺省值
2)可以使用字面值,表达式,函数
3)不能使用其他列的名字和伪列
4)缺省的数据类型必须匹配列数据类型
create table a(id number(8) , hire_date date default sysdate);
表建立默认值
CREATE TABLE dept (deptno number(2) , dname VARCHAR2(13) , loc VARCHAR2(12),
create_date date default sysdate);

desc dept

4.查询用户信息
select * from tab;
select table_name from user_tables;
select object_name from user_objects where object_type='TABLE';

5.查看列名信息
select table_name , column_name from user_tab_columns where table_name = 'EMPLOYEES';

6.数据类型：
VARCHAR2(size): 可变字符型
CHAR(size) 固定字符型
NUMBER(p,s) 可变数字型
DATE 日期时间型
LONG 可变长度字符数据 (up to 2 GB)
CLOB 字符数据(up to 4 GB)
RAW and LONG RAW 二进制
BLOB 二进制 (up to 4 GB)
BFILE 存储在外部文件中的二进制数据
ROWID 64位行唯一地址标识
TIMESTAMP 带小数秒的日期
INTERVAL YEAR TO MONTH 存储间隔多少年多少月
INTERVAL DAY TO SECOND 存储间隔多少天,小时,分钟,秒

7.时间戳 TIMESTAMP
1)类型是扩展的时间日期数据类型
2)可存放年月日时分秒的几位小数
3)可加上时区
语法:TIMESTAMP[(fractional_seconds_precision)]
TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE
TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE
4)间隔Interval
INTERVAL YEAR TO Month
数据类型存储在一段时间内使用的年份和月份日期时间字段
语法:
INTERVAL YEAR [(YEAR_PRECISION)]TO MONTH
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

例：
create table B (id INTEGER CONSTRAINT coupons_pk PRIMARY KEY ,
name VARCHAR2(30) NOT NULL , duration INTERVAL YEAR(3) TO MONTH);
INTERVAL '[+] , [-] , [Y] , [M] , [YEAR[(years_precision)]] to month
+,-:可选，用于说明时间间隔是正数还是负数，默认正
[y]:可选，用于表示时间间隔年份
[m]:可选，表示时间间隔月数，如果指定了年和月，必需在INTERVAL子句中包含 TO Month
[YEAR[(years_precision)]]:可选，用于说明年数的精度(默认为2)
INTERVAL '1' YEAR 时间间隔1年
INTERVAL '11' MONTH 时间间隔11个月
INTERVAL '14' MONTH 时间间隔1年2个月
INTERVAL '1-3' YEAR TO MONTH 时间间隔1年3个月
INTERVAL '0-5' YEAR TO MONTH 时间间隔0年5个月
INTERVAL '123' YEAR(3) TO MONTH 时间间隔123年
INTERVAL '-1-5' YEAR TO MONTH 时间将为负，值为1年5个月
INTERVAL '12345' YEAR(3) 时间间隔无效，超过了时间保函精度
insert into B (id , name , duration) values(1 , 'a' ,INTERVAL '1' YEAR);
insert into B (id , name , duration) values(2 , 'b' ,INTERVAL '11' MONTH);
insert into B (id , name , duration) values(3 , 'c' ,INTERVAL '14' MONTH);
insert into B (id , name , duration) values(4 , 'd' ,INTERVAL '1-3' YEAR TO MONTH);
insert into B (id , name , duration) values(5 , 'e' ,INTERVAL '0-5' YEAR TO MONTH);
insert into B (id , name , duration) values(6 , 'f' ,INTERVAL '123' YEAR(3));
insert into B (id , name , duration) values(7 , 'g' ,INTERVAL '12345' YEAR(5));会报错

8.约束介绍：
1)NOT NULL 非空
2)UNIQUE 唯一
3)PRIMARY KEY 主键
4)FOREIGN KEY 外键
5)CHECK 检查
使用约束原则：
1)用户可以为约束命名，也可以是数据库服务器自动命名为SYS_Sn的格式
2)约束可以创建于:建表的时候 , 表建成后修改
3)约束可以在列级或表级定义—非空只能在列级别定义
4)通过数据字典可以访问表约束信息
定义约束:
语法: create table [schema.] table (column datatype [DEFAULT expr] [column_constraint],
[table_constraint]);
1)列级约束
column [CONSTRAINT constraint_name] constraint_type
create table C (employee_id number(6) constraint emp_emp_id_pk1 primary key,
first_name VARCHAR2(20));
2)表级
column , ....
[CONSTRAINT constraint_name] constraint_type(column, ...)
create table D (employee_id number(6) , first_name VARCHAR2(20) ,
job_id VARCHAR2(10) , department_id number(8) ,
department_name VARCHAR2(20) ,
constraint emp_emp_id_pk2 PRIMARY KEY (employee_id));
3)非空约束
create E (id number(6) not null);
4)唯一性约束
create table F (employee_id number(6) , last_name VARCHAR2(20) not null ,
email VARCHAR2(25) , salary number(8,2) ,
commission_pct number(2,2) , hire_date date not null ,
department_id number(6) , department_name VARCHAR2(25) ,
CONSTRAINT emp_emp_id_pk3 UNIQUE(email));
已存在的表建立约束
alter table F add constraint emp_emp_id_pk4 unique(ename);
alter table F modify(empno unique);
5)主键约束：
1)一个表只能有一个主键
2)主键是唯一的并且非空
3)可以联合主键,联合主键要求每列都非空
4)主键唯一定位一行,所有主键也叫逻辑ROWID
5)主键不是必需的,可以没有
6)主键是通过索引实现的
7)索引的名称和主键的名称相同
6)外键约束:
外键约束在列级或表级定义
外键约束只能关联到本表或其他表的主键或唯一键上
FOREIGN KEY: 表级定义关联到子表中的列
REFERENCES: 定义父表和表中列
ON DELETE CASCADE: 父表行被删除子表行被级联删除
ON DELETE SET NULL: 父表行被删除将依赖的外键值修改为空值
例1：
CREATE TABLE G (employee_id NUMBER(6) , last_name VARCHAR2(25) NOT NULL ,
email VARCHAR2(25) , salary NUMBER(8,2) ,
commission_pct NUMBER(2,2) , hire_date DATE NOT NULL , department_id NUMBER(4),
CONSTRAINT emp_dept_pk5 FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_pk6 UNIQUE(email));
例二：
create table a as select * from dept;
create table b as select * from emp;
alter table a add constraint pk_a primary key (deptno);
alter table b add constraint pk_b foreign key (deptno) references a (deptno);
违反约束
delete a where deptno=10
update b set deptno=50
建立补级约束
alter table b drop constraint pk_b;
alter table b add constraint pk_b foreign key(deptno) on delete set null;
父表的值被删除，子表的相关列自动被赋予空值
alter table b drop constraint pk_b;
alter table b add constraint pk_b foreign key(deptno) on delete cascade;
父表的值被删除，子表的相关列自动被删除

7)检查约束:
1)用户定义的条件，每一行必须满足
2)不允许使用如下表达式:
涉及CURRVAL, NEXTVAL, LEVEL和ROWNUM 等伪列
调用SYSDATE, UID, USER和USERENV函数
涉及到其他关联行的查询
CREATE TABLE H
(employee_id NUMBER(6) CONSTRAINT emp_employee_id6 PRIMARY KEY ,
first_name VARCHAR2(20) ,
last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn7 NOT NULL ,
email VARCHAR2(25) CONSTRAINT emp_email_nn8 NOT NULL CONSTRAINT emp_email_uk8 UNIQUE ,
phone_number VARCHAR2(20) ,
hire_date DATE CONSTRAINT emp_hire_date_nn9 NOT NULL ,
job_id VARCHAR2(10) CONSTRAINT emp_job_nn10 NOT NULL ,
salary NUMBER(8,2) CONSTRAINT emp_salary_ck11 CHECK (salary>0) ,
commission_pct NUMBER(2,2) ,
manager_id NUMBER(6)
CONSTRAINT emp_manager_fk12 REFERENCES employees (employee_id) ,
department_id NUMBER(4)
CONSTRAINT emp_dept_fk13 REFERENCES departments (department_id));
违反约束
update employees set department_id = 55 where department_id=110; #报错
delete from departments where department_id = 60; #报错

8)添加约束语法：
1)添加或丢弃约束
2)启用或禁用约束
3)使用MODIFY子句添加非空表

添加约束语法:
alter table table_name add [CONSTRAINT constraint] type (column);
alter table table_name MODIFY (column [CONSTRAINT constraint] NOT NULL);

9)丢弃约束:
从employees表中丢弃对经理的约束
alter table employees drop CONSTRAINT emp_dept_pk5;
丢弃departments表中的主键约束，以及关联在employees.department_id列的外键约束
alter table departments drop PRIMARY KEY CASCADE;

10)禁用约束:
DISABLE可禁用约束
可禁用相关联的约束
约束的状态可通过USER_CONSTRAINTS视图的STATUS列查询(ENABLED , DISABLED)
alter table employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE;

11)启用约束:
使用ENABLE子句可启用约束
如果启用了PRIMARY KEY 或 UNIQUE约束，就会自动创建主键或唯一索引
alter table employees ENABLE CONSTRAINT emp_emp_id_pk CASCADE;

CASCADE CONSTRAINTS
CASCADE CONSTRAINTS子句是连同DROP COLUMN子句一起使用的
1)可以把被删除列上的关联的所有的约束都丢弃
2)可以把被删除列上的关联的多列的约束丢弃
3)如果被删除的列已经被其他列引用了约束，或是与其他列共同使用了约束，
那么，在删除该列时必须使用CASCADE CONSTRAINTS

12)查看约束：
查看用户表所有约束
select constraint_name , constraint_type , search_condition
from user_constraints
where table_name = 'emp';
查看约束关联的列的名字
select constraint_name , column_name from user_cons_columns where table_name = 'EMPLOYEES';

13)子查询创建表
语法: create table table_name [(column , column....)] as table_name
例：
CREATE TABLE dept80
AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

十一.建立其他对象
1)建立简单与复杂的视图
2)视图数据查询
3)建立维护使用序列
4)建立维护索引
5)建立公有与私有同义词

1.视图的作用：
限制数据访问
复杂查询简单化
提供数据独立性
相同数据显示不同视图

2.简单视图与复杂视图区别
功能---------------------------简单视图-------------复杂视图
表的数量 1个多个
是否包含函数不包含包含
是否包含分组数据不包含包含
是否可以通过视图执行ddl语句可以通常不行

3.建立视图
语法:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
注意：
1)子查询可以包含复杂的select语法
2)子查询不能使用order by
3)统计函数，表达式等操作的列需要定义列别名
4)查询视图列要用定义的别名
例1：
create view empvu80
as select employee_id , last_name , salary
from employees
where department_id = 80;
例2:
create view salvu50
as select employee_id ID_NUMBER , last_name NAME , salary*12 ANN_SALARY
from employees
where department_id = 50;
查询：
SELECT * FROM salvu50;
查看用户的视图信息：
select * from user_views;

4.修改视图
使用create or replace修改视图定义
视图列别名与子查询中列名一一对应
create or replace view empvu80 (id_number , name , sal , department_id)
as select employee_id , first_name || ' ' || last_name , salary , department_id
from employees
where department_id = 80;

5.复杂视图
使用两个表和分组函数创建视图
create or replace view dept_sum_vu (name , minsal , maxsal , avgsal)
as select d.department_name , min(e.salary) , max(e.salary) , avg(e.salary)
from employees e join departments d
on (e.department_id=d.department_id)
group by d.department_name;

6.视图的DML操作限制:
简单视图上可以进行DML操作
delete操作限制:使用分组函数 , 使用group by语法 , 使用去除重复行语句 , 使用了rownum伪列
update修改限制:分组函数 , 使用group by语句 , 使用重复行语句 , 使用rownum伪列 , 使用了表达式.
insert操作限制:分组函数 , 使用group by , 使用去除重复行 , 使用rownum伪列 , 使用表达式 , 非空约束的列没在select列表中引用

7.使用WITH CHECK OPTION子句可以确保视图中不会包含查询范围以外的记录
create or replace view empvu20
as select * from employees
where department_id=20
WITH CHECK OPTION CONSTRAINT empvu20_ck;

8.使用WITH READ ONLY 子句,可以确保视图无法进行DML操作
create or replace view empvu10 (employee_number , employee_name , job_title)
as select employee_id , last_name , job_id
from employees
where department_id = 10
with read only;

9.删除视图
删除视图不影响表数据
drop view viewname;

10.序列
1)自动产生唯一值
2)是一个共享的对象
3)典型的用于创建主键值
4)可替代应用程序代码
5)如果将序列值缓存在内存中可以提高访问效率
语法:
CREATE SEQUENCE name
[INCREMENT BY n] --步长
[START WITH n] --启始值
[{MAXVALUE n | NOMAXVALUE}] –-最大值
[{MINVALUE n | NOMINVALUE}] –-最小值
[{CYCLE | NOCYCLE}] –-循环
[{CACHE n | NOCACHE}];--缓存
例1：
create sequence dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 10000
NOCACHE
NOCYCLE;
注：非主键序列可以使用循环，主键序列不适用。

11.nextval与currval伪列
nextval:返回下一个可用的序列值，即使争对不同用户返回值也是唯一的.
currvall:返回当前序列值，在返回序列值之前必须先分配nextval.
nextval与currval伪列使用原则:
可使用：select列表(非子查询) , insert语句的子查询select列表中， insert语句的values子句中， update语句的set子句中
不能使用：视图的select列表中
有GROUP BY,HAVING或UPDATE语句的SELECT语句
在SELECT,DELETE,UPDATE语句的子查询中
在CREATE TABLE , ALTER TABLE语句的DEFAULT表达式

12.调用序列例子
create table departments1 (department_id number(8) , department_name VARCHAR2(25) , location_id number(8));
insert into departments1 (department_id , department_name , location_id)
values (dept_deptid_seq.NEXTVAL , 'Support' , 2500);

13.修改序列
alter sequence dept_deptid_seq
increment by 20
maxvalue 99999
NOCACHE
NOCYCLE;

使用DROP SEQUENCE 命令从数据字典中丢弃序列
一旦丢失，序列将无法再关联
DROP SEQUENCE dept_deptid_seq;

14.序列修改原则:
1)可以修改序列的步长 ,最大值,最小值,cycle或cache选项.
2)修改的值对后续操作有效.
3)只有序列的所有者或具有修改序列权限的用户可以修改序列.
4)序列的起始值不能修改,如果要修改，只能重建序列.

15.查看序列信息
select sequence_name , min_value , max_value , increment_by , last_number
from user_sequences;
last_number列显示了下一个可以分配的序列值。但如果指定了CACHE , 那么该列的值并不一定准确

16.索引Indexes
1)使用一种方案对象
2)由ORACLE服务器使用,通过指针提高查询的速度
3)使用快速路径访问模式减少磁盘I/O
4)不依赖于索引的表
5)由ORACLE服务器自动使用和维护
自动：自动创建唯一索引在中定义主键或唯一约束时表定义
手动：用户可以在上创建非唯一索引列以加快对行的访问速度。

17.建立索引:
在一个或多个列
CREATE [UNIQUE][BITMAP]INDEX index ON table (column[, column]...);
例1：经常访问last_name的列提高查询速度
CREATE INDEX emp_last_name_idx ON employees(last_name);

18.可创建索引情况
1)moulie包含的数据范围很广
2)某列包含大量的空值
3)一列或多列经常作为条件出现的where子句中,或用于连接条件
4)表很大,而大多数据查询只占所有行的2%-4%
不需创建索引情况:
1)表很小
2)列不是经常用作查询条件
3)大多数查询返回的行超过所有行的2%-4%
4)表经常会更新
5)索引列作为表达式的一部分被关联

19.确定索引
USER_INDEXES字典视图包含了索引名和唯一性
USER_IND_COLUMNS视图包含了索引名表名以及列名
通常将两个视图连接查询
select ic.index_name , ic.column_name , ic.column_position col_pos , ix.uniqueness
from user_indexes ix , user_ind_columns ic
where ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';

20.基于函数的索引
基于函数的索引是指基于表达式创建的索引
索引表达式可以由列，常量，SQL函数或用户自定义函数构建
当查询中使用UPPER(department_name)作为条件时，就会使用索引，否则执行全表扫描
create index upper_dept_name_idx on departments(upper(department_name));
select * from departments where upper(department_name) = 'SALES';

21.删除索引
使用 DROP INDEX命令从数据字典中删除索引
注：只有索引的所有者或是具有drop any index权限的用户才能删除索引
drop index index_name;

22.同义词
1)对象的另一个名字，可简化对表的访问
2)易于指向其他用户所属表
3)缩短对象名字
4)PUBLIC使同义词可以由对象访问
5)私有同义词的名字不能与同用户的其他对象同名
语法
CREATE [PUBLIC] SYNONYM synonym FOR object;

23.建立同义词
create synonym d_sum for dept_sum_vu;
create public synonym dept2 for departments;
grant create public synonym to hr;
grant drop public synonym to hr;
删除同义词
drop synonym emp;
drop public synonym dept2;

十二.控制用户访问
权限
数据库安全:系统安全,数据安全
系统权限：授权访问数据库
对象权限：对数据库对象内容的操作
方案：与用户名相同，是对象的集合如表视图序列等

系统权限：超过100种系统可用权限
数据库管理员具有最高级别的权限如：
Creating new users 建立新用户
Removing users 删除用户
Removing tables 删除表
Backing up tables 备份表

创建用户语法：CREATE USER user IDENTIFIED BY password;
授权语法：GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...];
对于开发人员，通常授予如下权限：
CREATE SESSION , CREATE TABLE , CREATE SEQUENCE , CREATE VIEW , CREATE PROCEDURE
DBA能授予指定系统权限给用户
GRANT create session, create table, create sequence, create view TO demo;
具有create session 权限才能连接数据库

角色
建立角色 CREATE ROLE manager;
给角色授予权限 GRANT create table, create view TO manager;
授予角色给用户 GRANT manager TO alice;

更改用户密码
alter user demo IDENTIFIED by employ;

对象权限:
1)不同对象，其对象权限也不同
2)对象的所有着拥有对对象的所有权限
3)可以授予用户对象权限,操作其他用户对象
语法：
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
例1:授予查询employees表权限
GRANT select ON employees TO demo;
例2:授予更新指定列权限给用户和角色
GRANT update (department_name, location_id) ON departments TO demo, manager;

权限的继承：
让SCOTT用户可以给其他用户授予select和insert部门表权限
GRANT select, insert ON departments TO scott WITH GRANT OPTION;
允许所有用户查询hr用户的departments表
GRANT select ON hr.departments TO PUBLIC;
VARCHAR2
确定权限
ROLE_SYS_PRIVS 分配给角色的系统权限
ROLE_TAB_PRIVS 分配给角色的表的权限
USER_ROLE_PRIVS 用户可以访问的角色
USER_SYS_PRIVS 分配给用户的系统权限
USER_TAB_PRIVS_MADE 分配在用户对象上的对象权限
USER_TAB_PRIVS_RECD 分配给用户的对象权限
USER_COL_PRIVS_MADE 分配在用户对象列上的对象权限
USER_COL_PRIVS_RECD 分配给用户对象指定列的权限

权限的回收
使用revoke命令撤销授予用户的权限
使用WITH GRANT OPTION 子句授予用户的权限也一起被撤销了
语法：
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
例1:回收scott的的查询，增加部门表的权限
REVOKE select, insert ON departments FROM scott;

十三.管理模式对象
添加约束
使用create table语句建立索引
建立基于函数的索引-FBI
删除不在使用的列
执行flashback操作
建立与使用外部表

使用alter table语句增加修改删除列
语法:
增加列
ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype]...);
修改列
ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [, column datatype]...);
删除列
ALTER TABLE table DROP (column [, column] …);
使用add增加列
ALTER TABLE dept80 ADD (job_id VARCHAR2(9));
修改数据类型大小默认值
ALTER TABLE dept80MODIFY (last_name VARCHAR2(30));
删除你不在需要的列使用drop column语句
ALTER TABLE dept80 DROP COLUMN job_id;

set unused选项
标记一个或多个列不在使用
语法
ALTER TABLE <table_name>
SET UNUSED(<column_name> [ , <column_name>]);
OR
ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name> [ , <column_name>];
删除所有标记不在使用的列
语法
ALTER TABLE <table_name> DROP UNUSED COLUMNS;

约束
增加约束语法
ALTER TABLE <table_name> ADD [CONSTRAINT <constraint_name>] type (<column_name>);
例1给emp2表的enployee_id列添加主键约束
ALTER TABLE emp2 MODIFY employee_id PRIMARY KEY;

例2为emp2增加一个外键约束关联对象为同表内的employee_id列
ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id)
REFERENCES emp2(employee_id);

例3父表行删除子表行也被删除
ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments(department_id) ON DELETE CASCADE;

父表行删除子表行对应的列更改为空值
ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments(department_id) ON DELETE SET NULL;

延迟约束
INITIALLY DEFERRED：初始化延迟执行，事务结束时检查
INITIALLY IMMEDIATE：初始化立即执行，语句执行时检查
例1
ALTER TABLE dept2
ADD CONSTRAINT dept2_id_pk
PRIMARY KEY (department_id)
DEFERRABLE INITIALLY DEFERRED
例2
CREATE TABLE emp_new_sal
(salary NUMBER
CONSTRAINT sal_ck CHECK (salary > 100) DEFERRABLE INITIALLY IMMEDIATE,
bonus NUMBER
CONSTRAINT bonus_ck CHECK (bonus > 0 ) DEFERRABLE INITIALLY DEFERRED );

更改约束
ALTER SESSION SET CONSTRAINTS dept2_id_pk IMMEDIATE
ALTER SESSION SET CONSTRAINTS= IMMEDIATE

删除约束
ALTER TABLE emp2 DROP CONSTRAINT emp_mgr_fk;
删除外键依赖的主键约束使用cascade
ALTER TABLE dept2 DROP PRIMARY KEY CASCADE;

禁止约束
CASCADE禁止约束和有依赖的约束
ALTER TABLE emp2 DISABLE CONSTRAINT emp_dt_fk;
启用约束
有依赖用cascade
ALTER TABLE emp2 ENABLE CONSTRAINT emp_dt_fk;
注意：1)在删除约束时使用关键字cascade可以删除参照该列的那些外键和使用 on delete cascade关键字来级联
删除参照该关键字的数据，使用cascade constraint 在删除列的同时删除约束。
2)利用drop table cascade constraint可以删除关联table t的constraint来达成drop table t的目的原来
属于T1的foreign key constraints 已经跟随被删除了，但存储在T1的数据不会被删除。
3)总的来说，那些处于包含多个列的约束中的列的删除，一定要使用cascade constraint关键字，否则没有必要
例1
ALTER TABLE emp2 DROP COLUMN employee_id CASCADE CONSTRAINTS;
例2
ALTER TABLE test1 DROP (col1_pk, col2_fk, col1) CASCADE CONSTRAINTS;

更改表名和约束名字
使用 ALTER 表的重命名列子句语句来重命名表列
ALTER TABLE test RENAME COLUMN id TO test_id;
使用 ALTER 表的重命名约束子句
ALTER TABLE test RENAME CONSTRAINT mktg_pk TO new_mktg_pk;

索引Indexes
索引建立时机
自动建立：主键约束建立，唯一性约束建立
手工建立：CREATE INDEX statement create index语句
例1
CREATE TABLE NEW_EMP
(employee_id NUMBER(6) PRIMARY KEY USING
INDEX (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)),
first_name VARCHAR2(20),
last_name VARCHAR2(25));
查询
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'NEW_EMP';

函数索引
可以是基本表达式
可以是列表达式,常数,函数,自定义函数
CREATE INDEX upper_dept_name_idx ON dept2(UPPER(department_name));
SELECT * FROM dept2 WHERE UPPER(department_name) = 'SALES';

删除索引
DROP INDEX index;
DROP INDEX upper_dept_name_idx;

删除表purge语句
DROP TABLE dept80 PURGE;
Purge是直接删除表,不保留到回收站,10g开始默认删除表是改名移动到回收站

闪回语句
能在一个语句中把表恢复到指定的时间点
恢复表数据连同索引与约束信息
能返回表及其内容到指定时间点或系统变更号
修复表误操作
恢复表到较早的时间点
优点：易用性可用性执行快
语法：
FLASHBACK TABLE[schema.]table[, [ schema.]table ]...TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE } TRIGGERS ];
例1
DROP TABLE emp2;
SELECT original_name, operation, droptime FROM recyclebin;
FLASHBACK TABLE emp2 TO BEFORE DROP;

临时表
例1
CREATE GLOBAL TEMPORARY TABLE cart ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE today_sales ON COMMIT PRESERVE ROWS
AS SELECT * FROM orders WHERE order_date = SYSDATE;

外部表
建立外部表需要的目录对象
创建一个目录对象,相对应的目录可存放外部表的数据文件
CREATE OR REPLACE DIRECTORY emp_dir AS '/…/emp_dir';
GRANT READ ON DIRECTORY emp_dir TO ora_21;
GRANT WRITE ON DIRECTORY emp_dir TO ora_21;
语法
CREATE TABLE <table_name> ( <col_name> <datatype>, … )
ORGANIZATION EXTERNAL
(TYPE <access_driver_type> DEFAULT DIRECTORY <directory_name> ACCESS PARAMETERS (… ) )
LOCATION ('<location_specifier>')
REJECT LIMIT [0 | <number> | UNLIMITED];
例1
CREATE TABLE oldemp (fname char(25), lname CHAR(25))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE NOBADFILE NOLOGFILE FIELDS TERMINATED BY ','
(fname POSITION ( 1:20) CHAR,lname POSITION (22:41) CHAR))
LOCATION ('emp.dat'))
PARALLEL 5
REJECT LIMIT 200;
例2
CREATE TABLE emp_ext
(employee_id, first_name, last_name)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY emp_dir LOCATION ('emp1.exp','emp2.exp')) PARALLEL
AS
SELECT employee_id, first_name, last_name
FROM employees;

十四.使用数据字典
USER：用户自己的视图，是自己的对象
ALL：所有的你有权访问的视图
DBA：dba视图，能访问所有的视图
V$：性能相关的数据
DICTIONARY视图包含字典表或视图的名字与介绍信息
DESCRIBE DICTIONARY
SELECT * FROM dictionary WHERE table_name = 'USER_OBJECTS';

1)USER_OBJECTS:
自己拥有的所有对象
获取所有的对象的名字,类型,建立时间,最后一次修改时间,状态
all_objects有权限访问的所有对象
例1
SELECT object_name, object_type, created, status FROM user_objects ORDER BY object_type;

2)表信息
DESCRIBE user_tables
SELECT table_name FROM user_tables;

3)列信息
DESCRIBE user_tab_columns
SELECT column_name, data_type, data_length,data_precision, data_scale, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

4)约束信息
描述表约束信息
描述约束所在列信息
DESCRIBE user_constraints
DESCRIBE user_cons_columns
例1
SELECT constraint_name, constraint_type,search_condition, r_constraint_name, delete_rule, status
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
例2
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';

5)视图信息
DESCRIBE user_views
SELECT view_name FROM user_views;
SELECT text FROM user_views WHERE view_name = 'EMP_DETAILS_VIEW';

6)序列信息
DESCRIBE user_sequences
确认序列信息
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;
如果指定NOCACHE会显示下一个可用的序列号

7)索引信息
DESCRIBE user_indexes
DESCRIBE user_ind_columns
例1
SELECT index_name, table_name,uniqueness
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
例2
SELECT index_name, table_name
FROM user_indexes
WHERE table_name = 'emp_lib';
例3
DESCRIBE user_ind_columns
SELECT index_name, column_name,table_name
FROM user_ind_columns
WHERE index_name = 'lname_idx';

8)同义词信息
DESCRIBE user_synonyms
SELECT * FROM user_synonyms;

9)使用注释
COMMENT ON TABLE employees IS 'Employee Information';
COMMENT ON COLUMN employees.first_name IS 'First name of the employee';
注释信息查看
ALL_COL_COMMENTS
ALL_TAB_COMMENTS

十五.操作海量数据
子查询操作数据:
1)使用嵌入式视图查询数据
2)从其他表复制数据
3)使用来自某个表值的结果去更新一个表中的数据
4)使用来自某个表值的结果去删除一个表的行记录
例1
SELECT department_name, city
FROM departments
NATURAL JOIN (SELECT l.location_id, l.city, l.country_id
FROM loc l JOIN countries c
ON(l.country_id = c.country_id)
JOIN regions USING(region_id)
WHERE region_name = 'Europe');
例2
INSERT INTO (SELECT l.location_id, l.city, l.country_id
FROM locations l
JOIN countries c
ON(l.country_id = c.country_id)
JOIN regions USING(region_id)
WHERE region_name = 'Europe')
VALUES (3300, 'Cardiff', 'UK');
例3
SELECT location_id, city, country_id FROM loc
约束插入的行必须是在子查询中匹配的记录否则违背检查约束而报错
INSERT INTO ( SELECT location_id, city, country_id
FROM locations
WHERE country_id IN (SELECT country_id
FROM countries NATURAL JOIN regions
WHERE region_name = 'Europe') WITH CHECK OPTION )
VALUES (3600, 'Washington', 'US');#######会报错

显式默认值
建议在value中使用默认值
用户能灵活的控制默认值在哪出现何时出现
显式默认值能使用在insert,update语句中
例1
默认插入
INSERT INTO deptm3
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);
默认更新
UPDATE deptm3
SET manager_id = DEFAULT
WHERE department_id = 10;

复制表数据
使用子查询复制行
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

SQL基础疏导手册

评论