感谢被转载作者 zhangxu3739
DISTINCT
过滤重复值
*distinct
必须放置在所有
column
之前
.
当一个字段时
,
只传回唯一值
.
当二个字段以上时
,
传回字段组合的唯一值
.
实际执行了排序操作
sort
Aslias
字段别名
*
在
SELECT
选取字段后可接
as
或空白，来替代字段别名。
SQL > SELECT job
职位
FROM emp;
或
SQL > SELECT job “
职位
” FROM emp;
或
SQL > SELECT job AS
职位
FROM emp;
两者结果相同
如果含有空格、特殊字符以及大小写需要用双引号
FROM
表格来源
*
在
FROM
子句中可接表格或用子查询来替代表格
.
SQL > SELECT * FROM emp ;
SQL > SELECT * FROM ( SELECT job FROM emp) ;
Arithmetic Expressions
数学表示式
运算优先级
* / + -
SQL > SELECT 120*salary+100 FROM employees;
ß
要注意
salary
先乘
120
再加
100
*
在使用字段运算时，字段值如果是
CHAR
型态，回传时会自动转型为
NUMBER
SQL > CREATE TABLE b ( b1 CHAR(10) ) ;
SQL > INSERT INTO b VALUES (‘2’) ;
SQL > SELECT b1 * 2 FROM b ;
ß
success!!
SQL > INSERT INTO b VALUES (‘a’) ;
SQL > SELECT b1 * 2 FROM b ;
ß
false!!
当字段运算遇到
NULL
时
,
则传回
NULL
SQL > SELECT NULL*2 COLUMN1 , 123*2 COLUMN2 FROM dual;
COLUMN1 123*2
246
空值是一个未标明、未知、最大的值，不同于零和空格值
Concatenation Operator
连结操作数，注意连接字符串时是使用’’单引号，而字段别名是使用””双引号
连结操作数
||
用来连接两个字段值
,
其值不分
number,char,date
型别
.
SELECT last_name || ' is a ' || job_id
AS "Employee Details"
FROM employees;
比较
SQL and iSQL*Plus的差异
|
SQL
|
iSQL*Plus
|
|
是一个与
Oracle Server
通讯储存资料的语言
|
可识别
SQL
叙述并传送到
Server
|
|
美国国际标准协会
(ANSI)
标准
SQL
|
是
Oracle
专有执行
SQL
叙述的接口
|
|
在数据库操作定义窗体
|
在数据库不允许操作数值
|
|
不可以将字符串断行
|
可以利用
dash(_)
来作字符串断行
|
|
不能缩短
|
可缩短
|
|
使用函数来执行一些资料格式化
|
使用指令来格式化资料
|
启动
oracle
的
http
服务
（
apache
）
http://127.0.0.1/isqlplus
Displaying Table Structure
显示表格结构
DESC[RIBE] tablename
传回字段名称
(Name)
、是否存入
NULL(Null?)
、资料型别
(Type)
iSQL*Plus
在执行
DML
语句时，如果非法关闭
,
将执行
rollback
（会产生表锁）
,
注销执行
commit
|
2.
Restricting and Sorting Data
[限制及排序资料]
|
2-1.Limit the rows retrieved by a query
限制请求取回的行资料
2-2.Sort the rows retrieved by a query
排序请求取回的行资料
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
|
操作数
|
表示意义
|
|
=
|
相等
|
|
>
|
大于
|
|
>=
|
大于或等于
|
|
<
|
小于
|
|
<=
|
小于或等于
|
|
<>
|
!=
与
^=
皆表示不相等
|
|
BETWEEN...AND...
|
包含两个数值之间的值
(
相异
)
|
|
IN(set)
|
符合清单数值中的任一笔数值
|
|
LIKE
|
符合部分字符
|
|
IS NULL
|
是一个
NULL
值
|
使用
BETWEEN
条件，要注意大小值放置顺序，当大小反置，将不回传回任何数值
.
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
或
→ WHERE salary BETWEEN
3500
AND
2500;
ß
将没有任何资料列被选取
※如果MIN_VALUE与MAX_VALUE值相等,等同于=MIN_VALUE
IN Condition
( IN (100, 101, 201) )
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
Or last_name IN ('Hartstein', 'Vargas');
凡符合
(100,101,201)
或
('Hartstein', 'Vargas')
值条件
%
包含零个或多个字符
_
只包含一个字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
*从employees窗体取回last_name字段，第二位字符是o的字符串.
*
如果需查询包含
_
字符的字符串时，可用
/
符号
.
SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE' %SA/_%' ESCAPE ' / ' ; →
注意要加
ESCAPE ‘ / ‘ ;
相同如果要搜寻包含
SA/
的字符串时
→select a from b where a like'%SA/_%' ESCAPE '_';
如果含有
%
select
employee_id, last_name, job_id
from employees
where first_name like'%/%%'
escape '/'
where first_name like'%/%%'
escape '/'
如果含有
’
select
employee_id, last_name, job_id
from employees
where first_name like'%''%'
where first_name like'%''%'
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL; ←
注意
!! NULL
值是用
IS NULL
，不是
xxx = NULL
Update
表时用
update tablename set column_name=null……
|
操作数
|
表示意义
|
|
AND
|
两者同时成立传回
TRUE,
否则传回
FALSE
|
|
OR
|
两者其中成立传回
TRUE
|
|
NOT
|
如果条件是FALSE则传回TRUE
|
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%';
AND
真值表
OR
真值表
NOT
真值表
*
注意真值表
NULL
结果
注记: NOT 操作数也可用于其它SQL 操作数, 像是 BETWEEN, LIKE, 及 NULL.
... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP')
... WHERE salary NOT BETWEEN 10000 AND 15000
... WHERE last_name NOT LIKE '%A%'
... WHERE commission_pct IS NOT NULL
|
评定次序
|
操作数
|
|
1
|
数学操作数 ＊ ／ ＋ －
|
|
2
|
连接操作数 ||
|
|
3
|
比较条件 > , < , = , >= , <= …etc
|
|
4
|
IS [NOT] NULL, LIKE, [NOT] IN
|
|
5
|
[NOT] BETWEEN
|
|
6
|
NOT 逻辑条件式
|
|
7
|
AND 逻辑条件式
|
|
8
|
OR 逻辑条件式
|
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = 'SA_REP'
OR job_id = 'AD_PRES'
AND salary > 15000;
* AND 比OR优先,所以先AND后再OR运算
SELECT last_name, job_id, salary
FROM employees
WHERE (job_id = 'SA_REP'
OR job_id = 'AD_PRES')
AND salary > 15000;
* 括号优先运算,然后再AND运算
ORDER BY
子句
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr} [ASC|DESC]];
– ASC: ascending order递增 (这是默认值)
– DESC: descending order递减
*ORDER BY 也可以使用多栏排序及代号表示,如SELECT * FROM emp ORDER BY 3 asc,4 desc;
使用字段别名来排序
SQL > SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;
注意!! Subquerice中不能使用order by clause
如下面语句不能执行
selectemployee_id, last_name
from employees where department_id in(
select department_id from departments
order by department_id )
select department_id from departments
order by department_id )
不能使用order by语句
|
3.
Single-Row Functions
[单列函数]
|
3-1.Describe various types of functions available in SQL
说明
SQL
中各种有用的函数类型
3-2.Use character, number, and date functions in SELECT statement
在
SELECT
叙述中使用字符
,
数字
,
日期函数
3-3.Use conversion functions 使用转换函数
dual 虚拟表，含有一列dummy虚拟一行数据X
SQL 函数的型别分为Single-Row与Multiple-Row，先看看单列函数：
• Character 字符串函数,像LOWER(),UPPER(),INITCAP,SUBSTR()…etc.
• Number 数值函数,像 MAX(),MIN(),ROUND(),SQRT(),FLOOR(),CEIL()…etc.
• Date 日期函数,像ADD_MONTHS( Date,Number ),NEXT_DAY(),LAST_DAY()…etc
• Conversion 转换函数,像TO_CHAR(),TO_NUMBER(),TO_DATE()…etc.
• General 一般函数,像NVL(), NVL2(), NULLIF(), COALSECE(), CASE, DECODE
*Multiple-Row在后面的课程将会说明,像是MAX(),MIN(),AVG(),SUM()
|
函数
|
动作
|
|
LOWER(column | expression)
|
小写转换
|
|
UPPER(column | expression)
|
大写转换
|
|
INITCAP(column | expression)
|
前缀转换,即使全大写或全小写字符串,一律转换成前缀大写其余小写.
|
|
CONCAT(column1 | expression1
, column2 | expression2)
|
连结字符串,将两个字段或字符串连结;相当于 (||) 操作数
|
|
SUBSTR(column | expression,m[,n])
|
取出子字符串,将字段或字符串中取出子字符串,m表示起始字数,n表示取出字数
|
Exp.
SQL> SELECT ename,SUBSTR(ename,2),SUBSTR(ename,2,3) FROM emp;
ENAME SUBSTR(ENAME,2) SUBSTR(ENAME,2,3)
---------- -------------------------- -----------------------------
SMITH MITH MIT
ALLEN LLEN LLE
WARD ARD ARD
：
：
字符串函数
(续)
|
函数
|
动作
|
|
LENGTH(column | expression)
|
传回一个NUMBER的字符串字符数.
|
|
INSTR(column | expression,
’string’ , [,m] , [n] )
|
传回string在column | expression中的位置的数字.
1.当传入只有一个数值,也就是指m字数开始搜寻
2.当传入两个数值时,指从m开始，第n个出现的位置
3.当传入一个负数值,指从右方搜寻,正数值则从左方搜寻
|
|
LPAD(column | expression, n,
'string')
RPAD(column | expression, n,
'string')
|
1. LPAD与RPAD之间差异，在于补足字的左方还是右方.
2. n,代表字符串字符总数,
3. string代表当字段或字符串字符总数不足n时,以string补足n值
4. 当n>column/expression字数时,同substr只取该colmn前n个字符
|
|
TRIM(leading | trailing | both
, trim_character FROM
trim_source)
|
1.如果没有其它参数,传回前后空白去除之trim_source.
2.当传带入参数时,
SQL > SELECT TRIM( ‘l’ FROM ‘lllHello World!!llllll’) FROM DUAL;
SQL > -----------------
Hello World!!
|
|
REPLACE(
text,
search_string,
replacement_string)
|
搜寻text字符串中search_string,替换成replacement_string
SQL > select replace('this is a book','book','dog') from dual;
----------------------------------
this is a dog
|
字符串函数
(范例)
|
函数
|
结果
|
|
CONCAT('Hello', 'World')
SUBSTR('HelloWorld',1,5)
LENGTH('HelloWorld')
INSTR('HelloWorld', 'W')
LPAD(salary,10,'*')
RPAD(salary, 10, '*')
TRIM('H' FROM 'HelloWorld')
|
HelloWorld
Hello
10
6
*****24000
24000*****
elloWorld
|
|
函数
|
动作
|
|
ROUND(column | expression, n)
|
传回小数第n位,四舍五入的值
|
|
TRUNC(column | expression,n)
|
传回小数第n位,无条件舍的值
|
|
MOD(m,n)
|
传回m除以n的余数
|
Exp.
ROUND(45.926, 2) → 45.93
TRUNC(45.926, 2) → 45.92
MOD(1600, 300) → 100
SQL > SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1) FROM DUAL;
SQL > SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(45.923,-2) FROM DUAL;
SQL > SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP';
Oracle Date
格式
Oracle database dates 储存在内部是以数字格式,占7个byte,包含century, year, month, day, hours,
minutes, seconds.
如下所示：
世纪年 月日 时分 秒
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
19 94 06 07 5 10 43
|
操作数
|
结果
|
说明
|
|
date + number
|
日期
|
日期加上number的天数
|
|
date - number
|
日期
|
日期减掉number的天数
|
|
date - date
|
天数
|
两个日期之间相差天数
|
|
date + number/24
|
日期
|
加入小时到日期
|
|
函数
|
说明
|
|
MONTHS_BETWEEN( Date1,Date2 )
|
两个日期间相差的月份,如果第二个参数比第一个参数时间还大(新),则传回负数值
|
|
ADD_MONTHS( Date,Number )
|
日期月份值加上Number值,当Number为负数,则月份
往前计算.
|
|
NEXT_DAY(Date,Number|Week)
|
计算下一个星期的日期,Number 1代表星期日,2代表星期一,3：二……7：六
|
|
LAST_DAY(Date,Number)
|
计算该月份的最后一天
|
|
ROUND(Date,MONTH|YEAR)
|
如果date的日或年已过一半,则传回下月或下年的第一天.
|
|
TRUNC(Date,MONTH|YEAR)
|
传回该月第一天或传回该年度第一天
|
Exp.
• MONTHS_BETWEEN ('01-SEP-95' , '11-JAN-94') → 19.6774194
• ADD_MONTHS ('11-JAN-94',6) → ' 11-JUL-94'
• NEXT_DAY ('01-SEP-95','FRIDAY') → ' 08-SEP-95'
• LAST_DAY('01-FEB-95') → ' 28-FEB-95'
SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
ADD_MONTHS (hire_date, 6) REVIEW,
NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 36;
*假设SYSDATE = '25-JUL-95':
•ROUND(SYSDATE,'MONTH') → 01-AUG-95 因为日期已过一半,所以回传下月期第一天
•ROUND(SYSDATE ,'YEAR') → 01-JAN-96 因为月期已过一半,所以回传下年度第一天
•TRUNC(SYSDATE ,'MONTH')→ 01-JUL-95 无条件日期归回该月份第一天 01-JUL-95
•TRUNC(SYSDATE ,'YEAR') → 01-JAN-95 无条件月日期归回该年度第一天 01-01-95
SQL > SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH')
FROM employees
WHERE hire_date LIKE '%97';
日期默认格式DD-MON_RR格式
Oracle
可以自动转换的型别
|
转换来源型别
|
转换后型别
|
|
VARCHAR2 or CHAR
|
NUMBER
|
|
VARCHAR2 or CHAR
|
DATE
|
|
NUMBER
|
VARCHAR2
|
|
DATE
|
VARCHAR2
|
SQL 提供三个函数去从一个数值转换到另一个资料型别
|
函数
|
说明
|
|
TO_CHAR ( number | date , [ fmt],
[nlsparams] )
|
转换一个number或date成为VARCHAR2
fmt为指定的格式,像指定date格式：‘DD-MM-YY’
指定number的格式：’$99999.99’
|
|
TO_NUMBER ( char , [ fmt ],
[ nlsparams ])
|
转换一个包含digit的字符字符串成为number型别
fmt为指定的格式,像 ‘99999.99’
|
|
TO_DATE ( char , [ fmt ] , [ nlsparams ] )
|
转换一个字符字符串成为date型别
fmt为指定的格式
|
Oracle
时间格式分为Date及Time
*Date 的元素
|
元素
|
说明
|
|
SCC or CC
|
Century; server prefixes B.C. date with -
|
|
Years in dates YYYY or SYYYY
|
Year; server prefixes B.C. date with -
|
|
YYY or YY or Y
|
Last three, two, or one digits of year
|
|
Y,YYY
|
Year with comma in this position
|
|
IYYY, IYY, IY,
|
I Four, three, two, or one digit year based on the ISO standard
|
|
SYEAR or YEAR
|
Year spelled out; server prefixes B.C. date with -
|
|
BC or AD
|
B.C.A.D. indicator
|
|
B.C. or A.D.
|
B.C./A.D. indicator with periods
|
|
Q
|
Quarter of year
|
|
MM
|
Month: two-digit value
|
|
MONTH
|
Name of month padded with blanks to length of nine characters
|
|
MON
|
Name of month, three-letter abbreviation
|
|
RM
|
Roman numeral month
|
|
WW or W
|
Week of year or month
|
|
DDD or DD or D
|
Day of year, month, or week
|
|
DAY
|
Name of day padded with blanks to a length of nine characters
|
|
DY
|
Name of day; three-letter abbreviation
|
|
J
|
Julian day; the number of days since 31 December 4713 B.C.
|
*Date 的元素
|
元素
|
说明
|
|
AM or PM
|
Meridian indicator
|
|
A.M. or P.M.
|
Meridian indicator with periods
|
|
HH or HH12 or HH24
|
Hour of day, or hour (1–12), or hour (0–23)
|
|
MI
|
Minute (0–59)
|
|
SS
|
Second (0–59)
|
|
SSSSS
|
Seconds past midnight (0–86399)
|
Date元素的格式
•Time elements format the time portion of the date.
HH24:MI:SS AM → 15:45:32 PM
•Add character strings by enclosing them in double quotation marks.
DD "of" MONTH → 12 of OCTOBER
•Number suffixes spell out numbers.
ddspth → fourteenth
|
函数
|
说明
|
|
NVL
NVL(expr1, expr2)
|
转换一个null值成为一个实际的值
|
|
NVL2
NVL2(expr1, expr2, expr3)
|
如果expr1 不是 null 则return expr2.
如果expr1是 null ,NVL2 return expr3.
Expr1的参数可以是任何的资料型别
|
|
NULLIF
NULLIF (expr1, expr2)
|
比较expr1与expr2如果相等则传回null,如果两者不相等则传回expr1
|
|
COALESCE
COALESCE (expr1, expr2, ... exprn)
|
传回expr1,expr2,expr3….中,第一个非null值
|
嵌套函数
几个一般函数结合到一起
–
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
Exp.范例(结果同下范例)
SQL > SELECT last_name, job_id, salary,
CASE job_idWHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK'THEN 1.15*salary
WHEN 'SA_REP'THEN 1.20*salary
ELSE salaryEND "REVISED_SALARY"
FROM employees;
– DECODE 函数
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
exp.范例(结果同上范例)
SQL > 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;
|
4.
Displaying Data from Multiple Tables
[从多表中显示资料]
|
4-1. Write SELECT statements to access data from more than one table using equality and nonequality joins
撰写
SELECT
叙述从多个窗体中使用相等与不相等连结方式存取资料
4-2. View data that generally does not meet a join condition by using outer joins
使用
outer join
来检视一般看不到的资料
4-3. Join a table to itself using a self-join
使用self - join自我连结窗体
*表格连结 JOIN
窗体的连结,相等的连结一般使用两个窗体中,有相同字段及内容,当两的表格被join后,只能看到两个条件联系的字段值是相同的row,其余是看不到的,除非使用外部连结 (+) or OUTER JOIN.
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
n窗体的连结需要n-1个条件式,所以两个窗体需要1个条件式,举个例子：要找出在纽约办公室的职员,
employees窗体只存放职员name,deptNo…等,dept窗体存放deptNo,loc,tel…等.
SELECT e.name,d.loc
FROM employees e,dept d
WHERE e.deptno=d.deptno AND d.loc=’纽约’ ;
没有相等的连结，参考SG的例子：
|
我们要利用JOB_GRADES的各级别的范围值来对EMPLOYEES的SALARY来评比级别,
SELECT e.last_name , e.salary , j.grade_level
FROM employees e , job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;←重要!!
|
*
使用ANSI窗体连结的方式
Table1 Join Table2 on Table1.col=Table2.col
Exp.
SQL > SELECT name,sal FROM employees e,dept dWHERE e.deptno=d.deptno ← 一般
SQL > SELECT name,sal FROM employees eJOIN dept d ON e.deptno=d.deptno ← ANSI语法
*外部连结Outer Join
当两个窗体外部连结条件,两个字段值没有match时,可以使用外部连结(+) 或 Outer Join
*自我连结 Self Join
使用同样窗体不同别名,利用别名来连结窗体,像是
SQL > SELECT e1.name,e1.sal FROM employee e1,employee e2 WHERE e1.empno=e2.empno ;
*
建立Cross Joins来产生笛卡尔积
SQL > SELECT last_name, department_name
FROM employees
CROSS JOIN departments;
等同于无关联
*
建立 Natural Joins来自动连结两个窗体.
注意!!两个表格中必须要有字段名称型别一样才能自动连结.
SQL > SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations;
*
建立Join与 USING 子句
•如果数个字段拥有相同的字段名,但资料型别却不相同, 这个 NATURAL JOIN 子句可以去修改USING子句注：当一个字段相同时,使用USING 子句去对照该唯一字段
•没有使用一个表格名称或字段参考别名
•NATURAL JOIN 和 USING 子句是彼此独立的
SQL > SELECT e.employee_id, e.last_name, d.location_id
FROM employees e JOIN departments d
USING (department_id);
*
建立 Join与 ON 子句
Join…On是ANSI窗体连结的语法,目的是将多个窗体连结查询
SQL > 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 ) ;
*
使用Join…On子句建立三个窗体的连结
SQL > 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 ;
使用一般窗体连结方式
SQL > SELECT employee_id, city, department_name
FROM employees, departments, locations
WHERE employees.department_id = departments.department_id
AND departments.location_id = locations.location_id;
使用表格连结时,当连结条件字段值没有match部分并不会出现在查询结果中,如果需包含没有符合
条件的rows也能出现,则必须使用外部连结的方式,外部连结可以用ANSI语法的Join…On,也可以
把两个窗体的共同字段用where condition使其连结,其条件应为n个窗体数的n-1个条件.
*LEFT OUTER JOIN
外部连结
SQL > 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);←注意!! LEFT方向指定要显示的资料
结果底下使用(+)外部连结操作数相同
SQL > SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id (+) = e.department_id;←注意!!符号(+)方向指定数据忽略不显示
*RIGHT OUTER JOIN
外部连结
SQL > SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
结果底下使用(+)外部连结操作数相同
SQL > SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id (+);
*FULL OUTER JOIN
外部连结
SQL > SELECT e.last_name , e.department_id , d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON ( e.department_id = d.department_id);
SQL > 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 )
AND e.manager_id = 149 ;
|
5.Aggregating Data using Group Functions[使用群组函数来统计数据]
|
5-1. Identify the available group functions
分辨使用群组函数的时机
5-2. Use group functions 使用群组函数
5-3. Group data using the GROUP BY clause
使用GROUP BY子句来群组资料
5-4. Include or exclude grouped rows by using the HAVING clause
包含或不含群组列使用HAVING子句
当你需要使用字段的统计值时,如 AVG、COUNT、MAX、MIN、STDDEV、SUM、VARIANCE…等,
来取回统计值.
|
函数
|
说明
|
|
AVG ( [ DISTINCT|ALL ] n )
|
传回平均值
|
|
COUNT ( { * | [ DISTINCT | ALL ] expr } )
|
传回资料笔数
|
|
MAX ( [ DISTINCT | ALL ] expr )
|
传回最大值
|
|
MIN ( [ DISTINCT | ALL ] expr )
|
传回最小值
|
|
STDDEV ( [ DISTINCT | ALL ] x )
|
传回标准差
|
|
SUM ( [ DISTINCT | ALL ] n )
|
传回总和
|
|
VARIANCE ( [ DISTINCT | ALL ] x )
|
传回异数估计值
|
*当使用( * )传回平均值,包含null字段(count使用)
( expr )传回平均值,不包含null字段
( ALL n|ex)
( DISTINCT expr )传回平均值,不包含null字段及重复值←注意,计算DISTINCT会忽略重复值
SQL > SELECT AVG(salary) , MAX(salary),
MIN(salary) , SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
使用群组函数遇到null字段时,如果要将其计算在内,可以使用单列函数的NVL(),
SQL > SELECT AVG ( NVL ( commission_pct , 0 ) )
FROM employees ;
*
建立群组资料,使用GROUP BY 子句
当使用选取字段中,包含群组函数与非群组函数,其针对非群组函数字段,需使用group by来定义该字段
为群组资料.
SQL > SELECT column , group_function ( column )
FROM table
[ WHERE condition ]
[ GROUP BY group_by_expression ] ←注意!!顺序错误将无法执行.
[ ORDER BY column ] ;
当条件式中需要使用群组函数时,不可使用where condition,应改以having替代
SQL > SELECT department_id , AVG ( salary )
FROM employees
HAVING AVG ( salary ) > 8000
GROUP BY department_id ;
SQL > SELECT column , group_function
FROM tabl e
[ WHERE condition ]
[ GROUP BY group_by_expression ] ←只要符合WHERE→GROUP BY→ORDER BY
[ HAVING group_condition ]HAVING 可以放置WHERE与ORDER之间.
[ ORDER BY column ] ;
ORDER BY放在最后面
SQL > 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 ) ;
*
使用嵌套群组函数
SQL > SELECT MAX ( AVG ( salary ) )
FROM employees
GROUP BY department_id ;
*所有群组函数都会忽略null值.
当使用GROUP BY字段没有在SELECT清单中时,将以GROUP BY分类及排序如下范例
SQL> SELECT deptno,AVG(sal) FROM emp
2 GROUP BY deptno;
DEPTNO AVG(SAL)
--------- ---------
10 2916.6667
20 2175
30 1566.6667
SQL > SELECT AVG(sal) FROM emp GROUP BY deptno;
AVG(SAL)
---------
2916.6667
2175
1566.6667
在群组函数排序数据.
SQL > SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary);← 当使用ORDER BY时，将以此排序资料.
注意!!单列函数会忽略NULL值，运算中遇到NULL值将传回NULL，如100+NULL*10传回NULL
|
6.
Subqueries
[子查询]
|
6-1. Describe the types of problems that subqueries can solve[描写「次查询」能够解决的问题]
6-2. Define subqueries [撰写「次查询」指令]
6-3. List the types of subqueries[
辨识不同种类的「次查询」
]
6-4. Write single-row and multiple-row subqueries[
撰写「单行次查询」与「多行次查询」
]
SQL > SELECT select_list
FROM table
WHERE expr operator
( SELECT select_list
FROM table ) ;
*传回”最低薪资”的员工
SQL > SELECT empno,ename,sal
FROM emp
WHERE sal = ( SELECT min(sal) FROM emp);
*
关于EXISTS (只能使用于子查询)
SQL > SELECT ename,job,sal FROM EMP
WHERE EXISTS ( SELECT deptno FROM emp WHERE sal=1000)
如果EXISTS条件成立,则传回子句全部资料,如果不成立则传回null
*
单列次查询
「次查询」只会传回一字段值,使用单列函数比较操作数=、>、>=、<、<=、<>
SQL > SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);
在子查询中使用群组函数
SQL > SELECT last_name, job_id, salary
FROM employees 2500
WHERE salary =
(SELECT MIN(salary)
FROM employees);
HAVING
子句与子查询
SQL > SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
( SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
*
多列次查询
「次查询」可以传回多笔资料(同一个字段) ,使用多列函数比较操作数IN、ANY、ALL
SQL > SELECT ename,job,sal
2 FROM emp
3 WHERE deptno IN
4 ( SELECT deptno
5 FROM dept
6 WHERE dname in (‘ACCOUNTING’,’SALES’));
*
内嵌视观表
位于FROM子句的「次查询」,可做为临时产生的结果集
在FROM子句使用「次查询」
SQL > SELECT e.ename,subq.loc
2
FROM emp e,
3
( SELECT deptno,loc
4
FROM dept
5
WHERE loc in (‘NEW YORK,’DALLAS’)) subq
6
WHERE e.deptno=subq.deptno
其中subq是「内嵌视观表」
「内嵌视观表」与 Top-N查询技巧
SQL > SELECT ename,job,sal,rownum
2
FROM ( SELECT ename,job,sal FROM emp
3
ORDER BY sal)
4
WHERE rownum<=3)
ENAME JOB SAL ROWNUM
-------------- -------- -------- -----------------
SMITH CLERK 800 1
JAMES CLERK 9502
ADAMS CLERK 1100 3
*
多重字段次查询
「次查询」传回多笔资料( 每笔资料可能包含多个字段)
SQL > SELECT deptno,ename,job,sal
2 FROM emp
3 WHERE (deptno,sla) in
4 (SELECT deptno,max(sal)
5 FROM emp
6 GROUP BY deptno);
*With
子句与「次查询」
当需要重复执行相同的「次查询」,可以利用With子句将「次查询」执行结果储存起来
SQL > With summary as
2
( SELECT dname,sum(sal) as dept_total
3
FROM emp,dept
4
WHERE emp.deptno=dept.deptno
5
Group by dname)
6
SELECT dname,dept_total
7
FROM summary
8
WHERE dept_total >
9
( SELECT sum(dept_total)*1/3
10
FROM summary
11
order by dept_total desc;
注意!!在次查询中不能使用ORDER BY，否则将发生错误.
|
7.Producing Readable Output with iSQL*Plus[在SQL*Plus产生美观的输出报表]
|
7-1. Produce queries that require a substitution variable[
使用替代变量撰写
SQL
叙述句
]
7-2. Produce more readable output[
产生可读性较高的输出结果
]
7-3. Create and execute script files[
建立与执行指令
]
*
使用 &替代变量
可以使用 & 替代变量叙述句有
• WHERE 条件 • ORDER BY 子句
• Column 表示式 • Table names
• Entire SELECT statements
date和character类型的需要使用单引号，例如’&employee_name’
SQL > SELECT employee_id, last_name, job_id,
&column_name
FROM employees
WHERE &condition
ORDER BY &order_column;
DEFINE 产生的变量,其生命周期直到SQL*PLUS联机终止,或使用UNDEFINE指令
针对已存在的变量可以使用DEFINE宣告相同的变量名,其变量值将被复盖.
已建立的变量可在SQL叙述中使用
SQL > DEFINE employee_num = 200
SQL > SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num;
使用 && 符号,不同与 & 符号地方, & 输入后直接带入变量且其它叙述句无法再使用
而 && 输入后带入变量后,其它叙述句可以继续使用变量,该变量相当于使用DEFINE
宣告的变量,如果 &&变量不再使用时,可使用UNDEFINE将变量取消
*指令集中可以使用@@呼叫另一个指令集; @@ 会告诉SQL*PLUS 在相同的目录下搜寻其它指令集
*ACCEPT 另一种定义变的方法
SQL > var_empno prompt ‘Enter EMPNO now ==>’
Select ename , job , deptno , sal
From emp
Where empno = &var_empno ;
*ARRAYSIZE [ARRAY] {15|n}
控制SQL*PLUS每次从数据库撷取的资料笔数,可设定1到5000,ARRAYSIZE值越大,执行效率越好
*AUTOCOMMIT [AUTO] {OFF|ON|IMMEDIATE|n}
自动交易确认,如果设定ON将会在每个叙述自动COMMIT,IMMEDIATE作用与ON相同
AUTOCOMMIT n 可以设定Oracle会在n个SQL叙述句被执行后自动确认,n不能为负数,也不能超过2,000,000,000
*AUTOTRACE [AUTO] {OFF | ON | TRACEONLY | EXPLAIN | STATISTICS}
产生一个追踪报告
*CMDSEP [CMDS] {;|c|OFF|ON}
控制SQL叙述句的结束字符,预设的结束字符为分号(;)
*COLSEP [COLSEP] {|text}
COMPATIBILITY [COM] {V7 | V8 | NATIVE
指定所连接的ORACLE数据库版本,V7是ORACLE 7,V8是ORACLE 8或ORACLE 8I,NATIVE 由数据库指定
DESCRIBE [DESCRIBE] DEPTH {1|N|ALL} LINENUM {ON|OFF} INDENT {ON|OFF}
设定以递归形式描述某对象的层级数目, DEPTH 的有效值为1到50
ECHO [ECHO] {OFF|ON}
控制指令集被执行时,执行结果是否响应在屏幕上
EDITFILE [EDITF] {file_name[.ext]
设定edit指令预设的文件名称,可以加入档案路径与附文件名
EMBEDDED [EMB] {OFF|ON}
控制报表的起始位置
ESCAPE [ESC] {/|c|OFF|ON}
SQL 所使用叙述句的跳脱字符
FEEDBACK [FEED] {6|n|OFF|ON}
FLAGGER [FLAGGER] {OFF|ENTRY | INTERMEDIATE | FULL}
检查SQL叙述句是否符合ANSI/ISO SQL 92标准
FLUSH [FLU] {OFF|ON}
HEADING [HEA] {OFF|ON}
控制输出报表是否印出字段名称
HEADSETP [HEADS] {||c|OFF|ON}
LINESIZE [LIN] {80|n}
设定SQL*PLUS的单行显示长度
LOBOFFSET [LOBOF] {n|1}
设定LOB 对象的起始位置,以便撷取与显示
LONG [LONG] {80|n}
设定LONG,CLOB,NCLOB与NCLOB等资料的最大显示寛度
LONGCHUNKSIZE [LONGC] {80|n}
设定SQL*PLUS每次撷取LONG,CLOB与NCLOB字段值的大小
NEWPAGE [NEWP] {1n|NONE}
设定报表起始位置与标题之间需要间隔几行
NULL [NULL] {text}
设定select 叙述句如何显示NULL值
NUMFORMAT [NUMF] {format}
设定数字资料预设的显示格式
NUMWIDTH [NUM] {10|n}
设定数字资料预设的显示寛度
PAGESIZE [PAGES] {24|n}
设定每页资料显示笔数
PAUSE [PAU] {OFF|ON|text}
控制报表执行时如何卷动屏幕
RECSEP [RECSEP] {WRAPPED|EACH|OFF}
告诉SQL*PLUS如何配置数据列分隔行
RECSEPCHAR [RECSEPCHAR] {|c}
定义「数据列分隔字符」
SERVEROUTPUT [SERVEROUT] {OFF|ON} SIZE {N}
是否将DBMS_OUTPUT_LINE()的输出结果显示到屏幕;
SHOWMODE [SHOW] {OFF|ON}
使用set指令更改SQL*PLUS系统时,是否列出新旧设定值
SQLCASE [SQLC] {MIXED|LOWER|UPPER}
在SQL叙述句或PL/SQL程序区块执行前转换大小写字母
SQLNUMBER [SQLN] {OFF|ON}
设定SQL指令第二行以后的提示字符
SQLPREFIX [SQLPRE] {#|c}
设定SQL*PLUS前导符.
SQLPROMPT [SQLP] {SQL>|text}
设定SQL*PLUS提示字符
SQLTERMINATOR [SQLT] {;|c|OFF|ON}
将SQL叙述句结束字符设定为c
SUFFIX [SUF] {SQL|text}
设定SQL*PLUS指令集的附文件名,预设是sql
TAB [TAB] {OFF|ON}
控制SQL*PLUS如何在输出结果如何显示空格
TERMOUT [TERM] {OFF|ON}
控制SQL*PLUS指令集的执行结果如何显示
TIME [TI] {OFF|ON}
控制系统时间的显示方式
TIMING [TIMI] {OFF|ON}
控制时间统计值的显示方式
VERIFY [VER] {OFF|ON}
控制SQL*PLUS是否显示替代变量更改前后的内容
UNDERLINE [UND] {-|c|ON|OFF}
将字段间隔的符号替代,预设为--------,语法 SET UNDERLINE *,变更为 **********
*
格式代输出结果
COLUMN [col] FORMAT {fmt} HEADING {string}
COLUMN Format Models
|
元素
|
说明
|
范例
|
结果
| ||
|
9
|
Single zero-suppression
Digit
|
999999
|
1234
| ||
|
0
|
Enforces leading zero
|
099999
|
001234
| ||
|
$
|
Floating dollar sign
|
$9999
|
$1234
| ||
|
L
|
Local currency
|
L9999
|
L1234
| ||
|
.
|
Position of decimal point
|
9999.99
|
1234.00
| ||
|
,
|
Thousand separator
|
9,999
|
1,234
| ||
|
8.
Manipulating Data
[数据处理动作]
| |||||
8-1. Describe each DML statement[
熟悉各种
DML
叙述句
]
8-2. Insert rows into a table[
将资料新增至表格
]
8-3. Update rows in a table[
更改表格既有资料
]
8-4. Delete rows from a table[
删除表格资料
]
8-5. Merge rows in a table[
合并表格资料
]
8-6. Control transactions[
控制数据库交易
]
DML语句:数据处理语言（Data Manipulation Language）如;select、insert、update、delete、merge
DDL语句:数据定义语言（Data Definition Language）如;create、alter、drop、truncate、rename
DCL语句:数据控制语言（Data Control Language）如;grant、revoke
*
将资料新增至表格
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
一次只能增加一条记录
下列三个insert叙述句示范如何将资料新增至EMPLOYEE表格
1.使用字段以NULL替代值,也可以使用 ’’ 来代替NILL值的输入,数字与字符串皆可.
SQL > INSERT INTO employee (empid,lastname,firstname,
2
salary,dept,hire_date)
3
VALUES (‘39334’,’SMITH’,’GINA’,75000,NULL,’15-MAR-97’);
2.完整insert叙述句
SQL > INSERT INTO employee ( empid,lastname,firstname,
2
salary,dept,hire_date)
3
VALUES (‘49539’,’LEE’,’QIAN’,90000,’504A’,’30-MAY-99’)
3.字段省略时,将以内定NULL输入
SQL > INSERT INTO employee (empid,lastname,firstname,
2
hire_date)
3
VALUES(‘60403’,’HARPER’,’ROD’)
另外也可使用替代变量来动态指定输入值.
SQL > INSERT INTO departments
(department_id, department_name, location_id)
VALUES (&department_id, '&department_name',&location);
注意字符串格式使用单引号
*
利用其它表格的资料执行INSERT叙述句
注意两者包含的表格字段,顺序也互相对应,如下两者皆对应,所以INSERT毌须指定字段
语法：
INSERT INTO table [ column (, column) ] subquery;
SQL > INSERT INTO scott.employee ( SELECT * FROM master.employee);
使用一个子查询来INSERT资料
INSERT INTO table [ column (, column) ] subquery;
SQL > INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
•没有使用VALUES子句.
•子查询中INSERT子句的字段要吻合.
插入
NULL值的方法
|
方法
|
说明
|
|
Implicit 隐含
|
column及value中将字段省略
|
|
Explicit 明确
|
在数值清单中明确使用NULL关键词,
或使用空字符串(‘’)在字符串或日期格式中
|
*
更改表格既有资料
语法：
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
SQL > UPDATE employee
2
SET salary = 99000
3
WHERE lastname = ‘SPANKY’
SQL > UPDATE employee
2
SET firstname = ‘ATHENA’,lastname = ‘BAMBINA’
3
WHERE empid = 49392’;
SQL > UPDATE employee
2
SET lastname = ( SELECT ename FROM emp WHERE empno = 7844)
3
WHERE empid = ‘49392’;
*
使用子查询,更改两个字段值
找出employee_id=114的员工,并将job_id与salary值改成与employee_id为205的值相同.
SQL > 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 = 114;
*
在一个INSERT叙述中使用子查询
SQL >INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary,←注意!!必须包含PK及NOT NULL字段
department_id
FROM employees
WHERE department_id = 50)
VALUES ( 99999, 'Taylor', 'DTAYLOR',
TO_DATE('07-JUN-99', 'DD-MON-RR' ),
'ST_CLERK', 5000, 50);
*
使用WITH CHECK OPTION关键词DML 叙述
SQL > INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);
INSERT INTO
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
The WITH CHECK OPTION Keyword
Specify WITH CHECK OPTION to indicate that, if the subquery is used in place of a table in an INSERT,
UPDATE, or DELETE statement, no changes that would produce rows that are not included in the subquery
are permitted to that table.
In the example shown, the WITH CHECK OPTION keyword is used. The subquery identifes rows that are
in department 50, but the department ID is not in the SELECT list, and a value is not provided for it in the
VALUES list. Inserting this row would result in a department ID of null, which is not in the subquery.
*
使用DEFAULT默认值
SQL > INSERT INTO departments
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);
SQL > UPDATE departments
SET manager_id =DEFAULT WHERE department_id = 10;
Default值是在create table时声明
如果后来alter table修改为default时，原有null值不会产生变化
*
以其它表格来更新列值
使用子查询从其它的表格中取出值,并用它来变更字段值.
SQL > 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);
*
删除表格资料
DELETE叙述句只能删除一笔(一列)资料,无法只删除部份字段资料
SQL > DELETE FROM employee WHERE lastname = ‘TURNER’
如果未指定WHERE条件式,则表格资料将全部删除
SQL > DELETE FROM employee;
*
合并表格资料
MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
*
交易控制的重要性
set transaction 这个指令可有可无,当您执行第一个DML(insert、update与delete)叙述句时便会自动开启交易.
commit 结束目前的交易,将资料更新至数据库
rollback 结束目前的交易,忽略所有资料更改动作.
savepoint 设定交易断点,可以将交易退回至回此断点..
*
隐含的交易处理
•底下情形将会自动执行commit确认交易.
– DDL叙述被执行时.
– DCL叙述被执行时.
–一般从iSQL*Plus离开,没有下COMMIT 或 ROLLBACK 的命令时.
• 当发生一个不正常的iSQL*Plus终止或系统错误时,将会自动rollback.
可以制定savepoint，进行对某部分操作进行rollback，如果名称与之前的重复，原有的将会别覆盖。
存在锁现象，一个用户在update表时，如果没有commit，其他用户可以查询为被update之前的数据，但不能修改这些数据，这些数据已被前一用户locked，只有执行commit后，才可释放
|
9. Creating and Managing Tables[建立与维护表格]
|
9-1. Describe the main database objects[
描述主要的数据库对象
]
9-2. Create tables[
定义表格字段时可使用的资料型别
]
9-3. Describe the datatypes that can be used when specifying column definition
[定义表格字段时可使用的资料型别]
9-4. Alter table definitions[
更改表格定义
]
9-5. Drop, rename and truncate tables[
删除、更名与截断表格
]
*
数据库对象
|
对象
|
说明
|
|
Table
|
Table 表格,基本储存单位; 由字段与列组成
|
|
View
|
VIEW 视观表,描述由一个或多个表格的子集合
|
|
Sequence
|
Sequence 序列,产生连续的数值.
|
|
Index
|
Index 索引,有效改善相同请求的效能
|
|
Synonym
|
Synonym 同义字,给一个对象替代名称.
|
Data dictionary view有四个范畴; 每一个范畴都有独立的前缀对应使用.
|
前缀
|
说明
|
|
USER_
|
这个视观表包含使用者相关对象信息.
|
|
ALL_
|
These views contain information about all of the tables (object tables
and relational tables) accessible to the user.
|
|
DBA_
|
These views are restricted views, which can be accessed only by
people who have been assigned the DBA role.
|
|
V$
|
These views are dynamic performance views, database server
performance, memory, and locking.
|
*
向Data Dictionary来提出请求
•查阅使用者所拥有的表格名称
SQL > SELECT table_name
FROM user_tables;
•查阅使用者所拥有个别的对象格式
SQL > SELECT DISTINCT object_type
FROM user_objects;
•使用者所拥有对象清单,像表格,视界, 同意字和序列.
SQL > SELECT *
FROM user_catalog;
Table 和 column 命名规则
•首字必须为字母
•长度在 1 – 30 之间
•只能包含A–Z, a–z, 0–9, _, $, 及 # 符号
•在同一个使用者中的他其对象名称,其名称不能重复
•不能为Oracle Server 的保留字
*
建立表格
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
*
建立暂时性表格
「暂时性表格」的生命周期可以维持到联机阶段结束,或是交易结束,每个联机阶段只能存取「暂时性表格」内属于自己的资料.
SQL > CREATE global temporary table temp_emp
2
empno number,
3
ename varchar2(10);
*藉由其它表格资料产生一个新表格(包含字段、资料、属性NOT NULL，但不包含约束条件)
SQL > CREATE TABLE emp_copy
2
AS SELECT * FROM EMP
3
WHERE deptno=10;
如果只复制部份字段时,则需要指定字段名称
SQL > CREATE TABLE emp_copy
2
(empno,sal) AS
3
SELECT empno,sal FROM emp
4
WHERE deptno=10;
*
资料型别与字段定义
|
资料型别
|
说明
|
|
VARCHAR2(n)
|
可存放变量长度的字符串,长度为n字节;n最大可以到4,000字节
|
|
NVARCHAR2(n)
|
可存放变量长度的Unicode字符串,长度为n字节;n最大可以到4,000字节
|
|
CHAR(n)
|
可存放固定长度的字符串,长度为n字节;n最大可以到2,000字节
|
|
NCHAR(n)
|
可存放固定长度的Unicode字符串,长度为n字节;n最大可以到2,000字节
|
|
NUMBER(n,[m])
|
可存数值资料,总长度为n位数;n最高可以到38位数;此外,NUMBER资料型别还允许您指定m位小数,如果未指定n,预设是38
|
|
DATE
|
可存放日期信息,长度为7个字节
|
|
RAW
|
类似VARCHAR2的变量长度资料型别,可存放高逹2,000字节的二进制数据
|
|
LONG
|
可存放高逹2GB的文字资料
|
|
LONG RAW
|
可存放高逹2GB的二进制数据
|
|
ROWID
|
可存放表格内每笔资料的实体位置
|
|
BLOB
|
可存放大量二进制数据,最高到4GB
|
|
CLOB
|
可存放大量文字资料,最高到4GB
|
|
NCLOB
|
可存放大量Unicode文字资料,最高到4GB
|
|
BFILE
|
存放档案指针,指向ORACLE以外的操作系统档案
|
*ORACLE 数据库对象名称必须以英文字母开头,不能超过30个字符,且不区分大小写,
数据库名称长度必须小于8个字符,数据库链接「databaselink」最大128个宇元
*NUMBER(n,[m]) 针对n,m的限制,小数点允许超出m位,超出部份以m位数四舍五入,针对n超出ORACLE
会提出错误讯息.
BLOB、CLOB与NCLOB数据域位值如果小于4KB,就直接储存于表格内,否则就在字段内存放一个指针.
每个表格只能宣告一个LONG或LONG RAW型别的字段,且总字段最多只能有1.000个字段.
*
字段默认值
如果没有指定该字段欲存放的资料,ORACLE将会自动存入您指定的默认值.(没存入资料与存入NULL值不同)
预设数值default 0,预设字符串default ‘YOU FORGOT ME’
SQL > CREATE TABLE display
2
(col1 VARCHAR2(10),
3
col2 NUMBER default 0);
*
在表格加入新字段
SQL > ALTER TABLE employee ADD ( hire_date DATE);
*
更改字段资料型别
SQL > ALTER TABLE products MODIFY(lastname VARCHAR2(25));
增加字段大小一般比较没什么问题,但如果要缩短字段大小,更改字段资料型别前,必须先
将字段资料全部COPY出来 → 将字段全部设为NULL → 将字段大小变更 → 最后将资料COPY回来
*
删除表格字段
间接删除表格字段
SQL > ALTER TALBE employee SET UNUSED COLUMN salary;
SQL > ALTER TABLE employee DROP UNUSED COLUMNS;
直接删除表格字段
SQL > ALTER TABLE employee DROP COLUMN salary;
*
删除表格
SQL > DROP TALBE emp_copy2;
*
截断表格
SQL > TRUNCATE TABLE tester;
*
更改对象名称(必须是对象的拥有者才能变更)
SQL > RENAME tester TO test2;
*
更改表格名称
SQL > ALTER TABLE tester3 TO tester;
*
为表格或字段加上批注
SQL > COMMENT ON TABLE employee IS
2 “This is a table containing employees’;
SQL > COMMENT ON TALBE employee.empid IS
2 “unique text identifier for employees’;
*
可以藉由data dictionary来检视相关批注.
– ALL_COL_COMMENTS
– USER_COL_COMMENTS
– ALL_TAB_COMMENTS
– USER_TAB_COMMENTS
|
10.Including Constraints[加入约束条件]
|
10-1. Describe constraints[
了解「约束条件」
contraints
的用途
]
10-2. Create and maintain constraints[
建立与维护约束条件
]
*约束条件的种类
|
约束条件
|
说明
|
存入NULL
|
允许重复
|
|
PRIMARY KEY
|
「主键」字段,如果是「复合式主键」其组合也必须唯一
|
╳
|
╳
|
|
FOREIGN KEY
|
「外来键」字段,该字段’的资料必须对应「父表格」的「主键」字段
|
○
|
○
|
|
UNIQUE
|
该约束条件的字段不能存放重复资料
|
○
|
╳
|
|
CHECK
|
该约束条件的字段可以检核字段值是否符合某个规则,或一组事先指定的资料
|
○
|
○
|
|
NOT NULL
|
该约束条件的字段不允许NULL值存放
|
╳
|
○
|
*
建立「主键」约束条件
SQL > CREATE TABLE employee
2
(empid VARCHAR2(5),
3
lastname VARCHAR2(25),
4
firstname VARCHAR2(25),
5
salary NUMBER(10,4),
6
constraint pk_employee_01 ←表格层级
7
primary key(empid));
SQL > CREATE TABLE employee
2
(empid VARCHAR2(5)
3
constraint pk_employee_01 primary key,←字段层级
4
lastname VARCHAR2(25),
5
firstname VARCHAR2(25),
6
salary number(10,4));
如果未指定约束条件,ORACLE将会自动为您命名约束条件,如
SQL > CREATE TABLE employee
2
(department_num NUMBER(5) primary key, ←字段层级
3
lastname VARCHAR2(25),
4
firstname VARCHAR2(25),
5
salary NUMBER(10,4));
*
复合式主键
复合式主键只能定义成「表格层级」，单一字段的「主键」可以定义「表格层级」与「字段层级」
SQL > CREATE TABLE name
2
(firstname VARCHAR2(10),
3
lastname VARCHAR2(10),
4
constraint pk_name_01
5
PRIMARY KEY(firstname,lastname));
*CHECK
约束条件
SQL > CREATE TABLE employee
2
(empid VARCHAR2(5) PRIMARY KEY,
3
lastname VARCHAR2(25) NOT NULL,
4
firstname VARCHAR2(25),
5
salary NUMBER(10,4) CHECK (salary<=250000),
6
department_num NUMBER(5)
7
REFERENCES department (department_num),
8
Govt_id number(10) unique);
*
在既有表格上加入约束条件
ORACLE在加入约束条件前会针对字段已存在的字段值,先检核字段是符合约束条件,
SQL > ALTER TABLE employee ADD CONSTRAINT
2
pk_employee_01 PRIMARY KEY (empid);
SQL > ALTER TABLE employee ADD CONSTRAINT
2
fk_employee_01 FOREIGN KEY(department_num)
3
REFERENCES department (department_num);
SQL > ALTER TABLE employee ADD CONSTRAINT
2
ck_employee_01 CHECK (salary <= 250000)
SQL > ALTER TABLE employee ADD CONSTRAINT
2
uk_employee_01 UNIQUE (govt_id);
SQL > ALTER TABLE employee MODIFY ←注意!! NOT NULL的变更方式
2 (lastname NOT NULL)
*
停用/启用约束条件
SQL > ALTER TABLE employee DISABLE PRIMARY KEY;
SQL > ALTER TABLE employee DISABLE CONSTRAINT uk_employee_01;
SQL > ALTER TABLE employee ENABLE PRIMARY KEY;
SQL > ALTER TABLE employee ENABLE uk_employee_01;
*
移除约束条件
SQL > ALTER TABLE employee DROP UNIQUE (govt_id);
SQL > ALTER TABLE employee DROP PRIMARY KEY CASCADE;
SQL > ALTER TABLE employee DROP CONSTRAINT ck_employee_01
NOT NULL 必须用ALTER TABLE MODIFY来修改
SQL > ALTER TABLE employee MODIFY (lastname NULL);
SQL > ALTER TABLE employee MODIFY (lastname NOT NULL);
*
删除被「外来键」参照的「父表格」
SQL > DROP TABLE department CASCADE CONSTRAINTS;
On delete cascade 当父表行被删除时，同时删除子表关联的行
On delete set null 把关联的外键值设置为null
|
11. Creating Views[建立视观表]
|
11-1. Describe a view[
描述「视观表」
view
的用途
]
11-2. Create, alter the definition, and drop a view[
建立、更改与移除视观表
]
11-3. Retrieve data through a view[
查询视观表的资料
]
11-4. Insert, update and delete data through a view[
透过视观表执行新增、修改与删除资料
]
11-5. Create and use an inline view[
建立「内嵌视观表」
(inline view)]
11-6. Perform Top 'N' Analysis[
执行
Top-N
查询
]
如果视观表欲存取的表格不存在,您将无法建立这个视观表。在create view叙述句内加上force关键词可以解决这问题---Oralce仍会建立这个视观表,但是该视观表会被标示成无效。Exp.Create Force View Name…
*视观表的定义均储存在USER_VIEWS数据字典视观表。此外,建立视观表时不能包含for update子句.
*CREATE OR REPLACE VIEW叙述句
利用视观表及DECODE函数,来隐藏业务员的奖金,且使用USER变量让自己才能看到自的奖金.
SQL > CREATE OR REPLACE VIEW emp_view as
2
( select empno, ename, job, mgr, hiredate,
3
decode(ename, user, sal, 0) as sal,
4
decode(ename, user, nvl(comm, 0), 0) as comm,
5
deptno from emp;
*
透过视观表更改「基底表格」资料有哪些限制
․透过视观表存取「基底表格」资料时,必须遵守表格的约束条件。举例来说,透过视观表新增资料至「基底表格」时不能违反表格的「PRIMARY KEY」约束条件.
如果「基底表格」含有一个NOT NULL字段,但是该字段并未包含在视观表内,那么您无法透过视观表来新增资料；除非该字段已设定一个默认值.
․一般来说,您可以透过视观表来删除「基底表格」资料,即使视观表并未包含「基底表格」所有字段
․如果视观表内某个字段是由「单列函数」、user、sysdate或是rowid等关键词所产生,那么您无法透过视观表更改其字段值.
․如果视观表所定义的select叙述句含有group by子句、群组函数或是distinct子句,就无法新增、修改或删除「基底表格」资料.
*建立包含约束条件的视观表
SQL > CREATE OR REPLACE VIEW emp_view as
2
(select empno, ename, job, deptno
3
FROM emp
4
WHERE deptno =10)
5
WITH CHECK OPTION CONSTRAINT emp_view_constraint;
这个emp_view视观表是以deptno=10做为限制条件.
*使用USER_CONTRAINTS资料字典文件查询使用者所有约束条件的相关信息
SQL > SELECT constraint_name, constraint_type
2 FROM user_constraints;
CONSTRAINT_NAMEC
------------------------------------
SYS_C00905P
SYS_C00903 C
PK_EMPLOYEE_01p
EMP_VIEW_CONSTRAINTV
P(PRIMARY KEY)、R(FOREIGN KEY)、C(CHECK)、U(UNIQUE)、V(WITH CHECK OPTION)
*
建立只读视观表
SQL > CREATE OR REPLACE VIEWemp_view
2
AS(SELECT * FROM emp)
3
WITH READ ONLY;
*建立复杂视观表
SQL > CREATE VIEW emp_dept_view as
2
( SELECT empno, ename, job , dname, loc
3
FROM emp e,dept d
4
WHERE e.deptno = d.deptno
5
and job in (‘ANALYST’,’CHECK’,’MANAGER));
*
数据库对象
|
Table
|
TABLE 由字段及列组成一个基本的储存单位
|
|
View
|
VIEW 由一个或多个表格资料子集合逻辑表示.
|
|
Sequence
|
Sequence 产生主键值.
|
|
Index
|
INDEX 索引,对相同的请求改善效能.
|
|
Synonym
|
Synonym 同义字,给一个对象替代名称.
|
*简单的视界和复杂的视界
|
Feature
|
简单视观表
|
复杂视观表
|
|
Number of tables
|
One
|
One or more
|
|
Contain functions
|
No
|
Yes
|
|
Contain groups of data
|
No
|
Yes
|
|
DML operations through a view
|
Yes
|
Not always
|
*
建立一个视界
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
OR REPLACE如果视界已存在,重新产生一个视界.
FORCE创造视界不管基底表格是否存在
NOFORCE只有在基底表格存在的时后才产生视界(这是默认值.)
view 视界的名称.
alias specifies names for the expressions selected by the view’s query (The
number of aliases must match the number of expressions selected by the
view.)
subquery是一个复杂的SELECT 叙述(在SELECT清单中你可以使用字段别名.)
WITH CHECK OPTIONspecifies that only rows accessible to the view can be inserted or updated
constraint 赋予CHECK OPTION约束条件一个名称.
WITH READ ONLY 确定不是DML作业(只读的视界).
SQL > CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
SQL > CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
SQL > CREATE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)
AS SELECT employee_id, last_name, salary*12
FROM employees
WHERE department_id = 50;
*
修改视观表
SQL > 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;
*
产生一个复杂的视观表
SQL > CREATE 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, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
*
产生一个复杂的视观表包含群组函数来显示两个表格的值.
SQL > CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROMemployees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
*
在视观表上执行DML的规范
•你可以在简单的视观表上执行DML作业.
•如果视观表包含底下叙述,你将无法移除列:
–群组函数
–一个GROUP BY 子句
– DISTINCT关键词
–虚拟字段ROWNUM 关键词
•如果视观表包底下叙述,你将无法修改资料:
–群组函数
–一个GROUP BY 子句
– DISTINCT关键词
–虚拟字段ROWNUM 关键词
–字段定义表逹式,exp: SALARY * 12.
*如果视观表包底下叙述,你将无法透过视观表新增资料:
–群组函数
–一个GROUP BY 子句
– DISTINCT关键词
–虚拟字段ROWNUM 关键词
–字段定义表逹,exp: SALARY * 12.
–在基底表格有NOT NULL 字段,该字段却没有包含在VIEW的SELECT中.
*使用WITH CHECK OPTION子句
•你可以确保DML作业在视观表上执行仍然在the domain of the view by 使用 WITH CHECK OPTION子句.
• 任何试图变更在视观表中部门编号任一列都会错误,因为他违反WITH CHECK OPTION约束条件..
SQL > CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;
*
移除一个视观表
你可以移除一个视观表,并不会因此遗失资料,因为一个视观表是在数据库基底表格之下.
SQL > DROP VIEW empvu80;
*
内嵌视观表
•一个内嵌视观表是一个子查询的别名 (或是关连名称)你可以使用SQL叙述.
•主要请求在FROM子句的子查询名称是一个内嵌视观表的范例.
•一个内嵌视观表并不是一个schema对象.
SQL > SELECT a.last_name, a.salary, a.department_id, b. maxsal
FROM employees a, (SELECT department_id, max(salary) maxsal
FROM employees
GROUP BY department_id)b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;
Top-n 分析
•Top-n queries ask for the n largest or smallest values of a column. For example:
– What are the ten best selling products?
– What are the ten worst selling products ?
•Both largest values and smallest values sets are considered top-n queries.
|
12. Creating Other Database Objects[建立其它资料对象]
|
12-1. Create, maintain and use sequences[
建立、维护与使用「序列」
(sequences)]
12-2. Create and maintain indexes[
建立与维护「索引」
(index)]
12-3. Create private and public synonyms[
建立公用与私有「同义字」
synonym]
*一个sequence:
•自动产生唯一号码
•是一个共有的对象
•是一个典型用来产生primary key value
•替换应用码
•当cached在memory中,sequence values 可提升效能.
*建立一个SEQUENCE叙述语法
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
sequence 定义序列名称
INCREMENT BY n specifies the interval between sequence numbers where n is an
integer (如果省略这个子句,序列会以1来递增.)
START WITH n specifies the first sequence number to be generated (如果省略这个子句,序列会以1初始.)
MAXVALUE n specifies the maximum value the sequence can generate
NOMAXVALUE specifies a maximum value of 10^27 for an ascending sequence and
–1 for a descending sequence (This is the default option.)
MINVALUE n specifies the minimum sequence value
NOMINVALUE specifies a minimum value of 1 for an ascending sequence and –
(10^26) for a descending sequence (This is the default option.)
CYCLE | NOCYCLE specifies whether the sequence continues to generate values afte r
reaching its maximum or minimum value (NOCYCLE is the default option.)
CACHE n | NOCACHE specifies how many values the Oracle Server preallocates and
keep in memory (Oracle Server预设的caches 20 values.)
NEXTVAL 和 CURRVAL虚拟字段
•NEXTVAL传回下一个序列值.
•CURRVAL 取的目前的序列值.
*Rules for Using NEXTVAL and CURRVAL
You can use NEXTVAL and CURRVAL in the following contexts:
• The SELECT list of a SELECT statement that is not part of a subquery
• The SELECT list of a subquery in an INSERT statement
• The VALUES clause of an INSERT statement
• The SET clause of an UPDATE statement
You cannot use NEXTVAL and CURRVAL in the following contexts:
• The SELECT list of a view
• A SELECT statement with the DISTINCT keyword
• A SELECT statement with GROUP BY, HAVING, or ORDER BY clauses
• A subquery in a SELECT, DELETE, or UPDATE statement
• The DEFAULT expression in a CREATE TABLE or ALTER TABLE statement
For more information, see Oracle9i SQL Reference, “Pseudocolumns” and “CREATE SEQUENCE section.”
|
13. Controlling User Access[使用者权限管理]
|
13-1. Create users[
建立使用者帐号
]
13-2. Create Roles to ease setup and maintenance of the security model
[利用「角色」来简化数据库安全性管理机制]
13-3. Use the GRANT and REVOKE statements to grant and revoke object privileges
[
执行
grant
与
revoke
叙述来授予撤回权力
]
*
建立使用者帐号
SQL > CREATE USER user_nameIDENTIFIED BY password;
帐号不能超过30个字符,第一个字符以外,其余字母可以包含$,#,_等字符.
*
建立由操作系统验证的使用者帐号
SQL > CREATE USER OPS$user_nameIDENTIFIED EXTERNALLY;
这里的使用者帐号必须对应至操作系统下某个既有帐号,前面冠上OPS$.
接着可以直接登入ORACLE数据库
c:/windows> c:/oracle/ora81/bin/sqlplus /
*
赋予系统权限给使用者
SQL > GRANT CREATE SESSION TO turner;
SQL > GRANT CREATE TABLE TO turner;
SQL > GRANT CREATE SEQUENCE TO turner;
SQL > GRANT CREATE PROCEDURETO turner;
*
撤销已赋予的系统权限
SQL > REVOKE CREATE SESSION FROM turner;
*
数据库存取权限
控制哪些人可以存取资料,或是更改联机阶段设定,包括:create session、alter session、restricted session
*
使用者管理权限
管理ORACLE数据库的使用者帐号,包括:create user、become user、alter user、drop user.
*
表格管理权限
控管哪些使用者可以建立或维护表格,相关权限包括:create table、create any table 、select any table、insert any table、update any table与create any table,其中create table或create any table权限也允许您删除表格,此外create table权限还允许您在表格上建立索引,并执行analyze指令.如果是截断truncate表格资料,您必须拥有drop any table权限才行.
*
索引管理权限
在表格上建立索引可以提升SQL叙述句的执行效能,相关权限包括:create any index、alter any index与drop any index,注意,没有create index这种系统权限,create table权限即允许您更改或删除表格上的索引.
*
同义字管理权限
同义字(synonym)可视为数据库对象的别名:它允许您利用不同名称来参照数据库对象,如果是执行create public synonym叙述句建立的同义字,那么数据库内所有使用者都可以参照它:相关权限包括:create synonym、create any synonym、drop any synonym、create public synonym与drop public synonym.
*
视观表管理权限
视观表是一种包含SQL叙述包的对象,使用方式类似表格,相关权限包括create view、create any view与drop any view。create view权限也允许您更改或删除自己的表格.
*
序列管理权限
序列(sequence)可以根据您定义的规则产生一连串的数字,相关权限包括:create sequence、create any sequence、alter any sequence、drop any sequence与select any sequence。Create sequence权限允许您删除自己建立的序列.
*
角色管理权限
角色(role)可用来简化权限管理.您可以建立一个角色,赋予其权限,然后再将角色赋予给使用者,相关权限包括:create role、drop any role、grant any role与alter any role
*
交易管理权限
解决ORACLE数据库分布式交易可能产生的问题,相关权限包括force transaction与execute any procedure。
*PL/SQL
管理权限
允许您在。ORACLE内建立不同的PL/SQL程序区块,相关权限包括:create procedure 、create any procedure、alter any procedure、drop any procedure与execute any procedure。create procedure权限也允许您更改或删除您自己撰写的PL/SQL程序区块.
*
触发器管理权限
触发器(trigger)是在表格上撰写的PL/SQL程序代码。如果您在表格上执行与触发器相关的DML叙述句,触发器就会自动被执行。相关权限包括:create trigger、create any tirger、alter any triger与drop any triger。create triger权限也允许您更改或删除自己建立的触发器。
*
查询使用者被赋予的系统权限
USER_SYS_PRIVS 显示使用者被赋予的系统权限
SESSION_PRIVS 显示使用者被赋予的系统权限
*
更改密码
SQL > ALTER USER scottIDENTIFIED BY new_password
*
对象存取权限
建立数据库对象的使用者将拥有其完整存取权限。也就是说，他可以再将「对象存取权限」赋予其它使用者。ORACLE数据库对象的存取权限包括以下几种:
․SELECT允许使用者查询表格、序列、视观表或快照(snapshot)的资料。
․INSERT允许使用者将资料新增至表格。您甚至可以限制使用者只能新增表格内特定字段值。
․UPDATE允许使用者更改表格资料,您甚至可以限制使用者只能更改表格内特定字段值。
․DELETE允计使用者删除表格资料
․ALTER允许使用者可以更改表格或序列的定义。其它数据库对象的alter权限均视为系统管理权限。
․INDEX允许使用者在表格上建立索引。
․REFERENCES允许使用在表格上建立「外来键」,参照其它表格。
․EXECUTE允许使用者执行预存程序或函数。
要将对象存取权限赋予其它使用者,必需需符合下列两者之一:第一,本身是对象拥有者(建立对象的人),第二,您是以with grant option的方式被赋予数据库对象的存取权限，因此可以再将权限赋予给其它人。
*
赋予ORACLE对象存取权限
SQL > GRANT select, update, insertON emp TO turner;
SQL > GRANT referencesON emp.empno TO turner;
*
赋予ORACLE对象存取权限(限定字段)
SQL > GRANT update (department_name, location_id)
ON departments
TO scott, manager;
*
将「对象存取权限」赋予所有使用者
SQL > GRANT select, update, insertON emp TO public;
*
一次将对象所有存取权限赋予使用者
SQL > GRANT ALL ON emp TO turner;
*
赋予某权限管理能力
SQL > GRANT create table TO tunerWITH ADMIN OPTION系统存取权限
SQL > GRANT select, update, ON emp TO turnerWITH GRANT OPTION对象存取权限
*
撤销ORACLE对象存取权限
SQL > REVOKE select, update, insertON emp FROM turner;
SQL > REVOKE referenceON emp.empno FROM turner;
当您执行revoke指令撤销某个表格的REFERENCE权限时,如果加上CASCADE CONSTRAINTS选项,将会一并删除「子表格」的外来键。
*撤销系统权限之串联效应
系统管理权限并不会发生串联效应,举个例子来说明
使用者A将系统管理权限WITH ADMIN OPTION给使用者B,使用者B又将create table系统权限赋予使用者C,使用者A将使用者B权限移后,使用者C依然可以create table.
USER A → USER B → USER C
(删除)
*撤销对象权限之串联效应
对象管理权限会发生串联效应,举个例子来说明
使用者A将对象权限WITH GRANT OPTION给使用者B,使用者B又将权限赋予使用者C,使用者A将使用者B权限移后,使用者C便无法再使用该对象的权限.
USER A → USER B → USER C
(删除)
*
考OCP前,赋予或撤销「对象存取权限」的注意事项
․如果赋予的权限牵涉到表格内两个字段,您无法单独撤销某个字段的存取权限,应该先将两个字段的存取权撤销,然后再赋予其中一个字段的存取权限
․如果使用者被赋予reference权限,并且建立一个外来键去参照其它表格。当您执行revoke指令撒销references权限时必须加上cascade constrants
SQL > REVOKE REFERENCES ON emp FROM spanky CASCADE CONSTRANTS
․insert、update与reference权限可以针对表格字段进行赋予动作。然而，如果使用者只拥有某些字段的insert权限，而非所有字段。那么其它未被赋予insert权限的字段不可以是NOT NULL
․如果使用者A拥有某个预存程序的执行权限，可透过该程序存取表格。那么存取这些表格的权限必须赋予该程序的拥有者，而不是赋予使用者A
․根据权限赋予或撤销的时机不同，权限更改结果也许不会立即生效。针对使用者、角色或PUBLIC所赋予或撤销的权限将会立刻生效。但是针对使用者、角色或PUBLIC所赋予或撤销的角色将不会立刻生效；除非使用者执行set role叙述句重新取得该角色的权限，或是在权限更改之后重新建立联机。
*
利用角色来简化权限管理工作
为了有效使用角色，权限管理过程必须包含四个步骤：
1.根据工作职责需要，将使用者划分不同群组
2.为每个使用者群组定义一个「角色」
3.赋予每个「角色」适当的数据库权限
4.将「角色」赋予相对应的使用者
*
建立「角色」
SQL > CREATE ROLE rpt_writer;
SQL > CREATE ROLE data_changer;
*
「角色」的进阶管理(提供角色一个密码)
SQL > ALTER ROLE data_changer
2 IDENTIFIED BY password;
*
将权限赋予给「角色」
SQL > GRANT selectON emp TO rpt_writer;
SQL > GRANT update, delete, insertON emp TO data_changer;
*
将「角色」赋予给使用者
SQL > GRANT rpt_writerTO turner;
SQL > GRANT data_changerTO ford;
如果已经赋予使用者某个「角色」，然后又将「对象存取权限」加入该「角色」，那么使用者将会立即取得新权限。
*
设定使用者的「预设角色」
SQL > ALTER USER fordDEFAULT ROLE NONE;
*
启用目前的「角色」
SQL > SET ROLE data_changerIDENTIFIED BY highly#secure;
*
撤销与删除角色
SQL > REVOKE rpt_writerFROM turner;
SQL > REVOKE data_changerFROM ford;
SQL > DROP ROLE rpt_writer;
SQL > DROP ROLE data_chagner;
*ORACLE
数据库事先定义的「角色」(针对OCP考试中几个重要的)
CONNECT 包含下列权限：create table、create cluster、create session、create view、create sequence…
RESOURCE 包含下列权限：create table、create cluster、create trigger与create procudure
DBA 可管理所有系统权限
*
与「角色」有关的资料字典视观表
USER_ROLE_PRIVS查询您被赋予的「角色」
ROLE_ROLE_PRIVS查询某「角色」被赋予的其它「角色」
ROLE_TAB_PRIVS查询某「角色」被赋予的「系统管理权限」
SESSION_ROLES查询目前目前联机阶段所拥有的「角色」
*
标准 DBA权限
|
CREATE USER
|
授与可以建立其它Oracle使用者的权限(这个权限需要DBA的角色才可以)
|
|
DROP USER
|
授与可以删除其它的使用者的权限.
|
|
DROP ANY TABLE
|
授与可以删除任何schema下的表格
|
|
BACKUP ANY TABLE
|
授与可以使用export工具备份任何schema下的表格.
|
|
SELECT ANY TABLE
|
授与可以在任何schema下读取表格,视界或快照(snapshot).
|
|
CREATE ANY TABLE
|
授与可以在任何schema下建立表格的权限
|
*
标准 User权限
|
CREATE SESSION
|
连接到数据库
|
|
CREATE TABLE
|
在使用者的schema下建立表格
|
|
CREATE SEQUENCE
|
在使用者的schema下建立序列(sequence)
|
|
CREATE VIEW
|
在使用者的schema下建立视界(view)
|
|
CREATE PROCEDURE
|
在使用者的schema下建立储存程序,函数或package
|
*
对象权限
|
ALTER
|
◎
|
◎
| ||
|
DELETE
|
◎
|
◎
| ||
|
EXECUTE
|
◎
| |||
|
INDEX
|
◎
| |||
|
INSERT
|
◎
|
◎
| ||
|
REFERENCES
|
◎
|
◎
| ||
|
SELECT
|
◎
|
◎
|
◎
| |
|
UPDATE
|
◎
|
◎
|
340
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
