Oracle SQL基础

目录

• init_NLS_config
• 00、准备工作
• SQL语句分类
• 01、基本SQL语句
• 02、对数据进行限定和排序
• 03、处理单行函数
• 04、多表查询
• 05、分组函数
• 06、子查询
• 07、多列子查询
• 09、DML语句
• 09、TRANSACTION事务
• 10、创建和管理表
• 11、约束
• 12、视图
• 13、序列
• 13、索引
• 13、同义词
• 14、用户访问控制
• 
  

init_NLS_config

确定系统NLS

NLS　 ‘National Language Support (NLS)’ 

从概念看，这个参数实际上定义了数据库的存放数据的语言环境，当我们设定一种nls的时候实际上我们是为oracle在存放数据时指定了他的语种所特 有的一些表达形式，比如选择chinese,那么它的中文字符如何存放,按什么规则排序，货币如何表示，日期格式也就被设定了。

显示系统nls值

SQL> select * from v$nls_parameters;

database_properties包含了数据库的固定属性。

SQL> select property_name ,property_value from database_properties;;

v$database包含了控制文件中的信息。

SQL> desc v$database;

修改字符集

alter session set NLS_DATE_LANGUAGE='AMERICAN' 缩写us;

 SIMPLIFIED CHINESE 缩写zhs

修改日期类型数据的格式

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';     //修改当前会话

alter system set nls_date_format='YYYY-MM-DD hh24:mi:ss' scope=spfile;     //修改保存到spfile

在windows客户端上，要手动建立这两个变量。

setenv NLS_lANG 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK' 切换语言

setenv NLS_LANG AMERICAN_AMERICA.US7ASCII

一旦数据库创建后，数据库的字符集是不能改变的。

与字符集有关的NLS_LANG参数，

NLS_LANG格式：

NLS_LANG = language_territory.charset

有三个组成部分（语言、地域和字符集），每个成分控制了NLS子集的特性。其中：

     language 指定服务器消息的语言。

     territory 指定服务器的日期和数字格式。

     charset 指定字符集

例如：

AMERICAN_AMERICA.US7SCII

AMERICAN _ AMERICA. ZHS16GBK

还有一些子集可以更明确定义NLS_LANG参数：

DICT.BASE 数据字典基本 表版本

DBTIMEZONE 数据库时区

NLS_LANGUAGE 语言

NLS_TERRITORY 地域

NLS_CURRENCY 本地货币字符

NLS_ISO_CURRENCY ISO货币字符

NLS_NUMERIC_CHARACTERS 小数字符和组 分隔开

NLS_CHARACTERSET 字符集

NLS_CALENDAR 日历系统

NLS_DATE_FORMAT 缺省的日期格式

NLS_DATE_LANGUAGE 缺省的日期语言

NLS_SORT 字符排序序列

NLS_TIME_FORMAT 时间格式

NLS_TIMESTAMP_FORMAT 时间戳格式 

SQL语句分类

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

MERGE - UPSERT operation (insert or update)

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

GRANT - gives user's access privileges to database

REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

00、准备工作

查看账户状态

select username, account_status from dba_users where username='SCOTT';

解锁账户

alter user scott account unlock

修改密码

alter user scott identified by oracle

系统默认设置

select property_name,property_value from database_properties

显示当前系统nls值
SQL> select * from v$nls_parameters ;

修改日期类型数据的格式

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'；     当前会话

alter system set nls_date_format='YYYY-MM-DD hh24:mi:ss' scope=spfile;     永久更改

01、基本SQL语句

select ename from emp

You can also select from pseudocolumns. A pseudocolumn behaves like a table column but is not actually stored in the table. You cannot insert or delete values of the pseudocolumns. The available pseudocolumns are CURRVAL, NEXTVAL, LEVEL, ROWID, and ROWNUM. 

currval返回序列的当前值,

nextval使序列增值并返回新的取值,

currval,nextval都返回number数值。 

rowid唯一标识每条记录所在的位置，它作为一个伪列在查询中出现

1、它并不能指示出行的物理位置，但可以用来定位行。 

2、rowid是存储在索引中的一组既定的值（当行确定后）。我们可以像表中普通的列一样将它选出来。
3、利用rowid是访问表中一行的最快方式。
4、rowid需要10个字节来存储，由18个字符组成分4个部分，分别是：

     6个字符的对象编号，3个字符的文件号，6个字符的块编号，3个字符的行编号。

     ROWID                   

     ------------------ 
     AAAMfPAAEAAAAAgAAH

col object_id for a15

col file_id for a15

col block_id for a15

col row_num for a15

select rowid,

substr(rowid,1,6)||' : '||dbms_rowid.rowid_object(rowid)  object_id,           --数据对象编号

substr(rowid,7,3)||' : '||dbms_rowid.rowid_relative_fno(rowid) file_id,           --文件编号

substr(rowid,10,6)||' : '||dbms_rowid.rowid_block_number(rowid) block_id,      --块编号

substr(rowid,16,3)||' : '||dbms_rowid.ROWID_ROW_NUMBER(rowid) row_num      --行编号

from emp

where empno=7788     

Character and date column headings can be truncated, but number headings cannot be truncated. The column headings appear in uppercase by default. 

You can use only the addition and subtraction operators with DATE datatypes.

SQL*Plus ignores blank spaces before and after the arithmetic operator.

Within a SQL statement, a column alias can be used in both the SELECT clause and the ORDER BY clause. You cannot use column aliases in the WHERE clause. Both alias features comply with the ANSI SQL 92 standard.

查看正在运行的SQL 

select a.program,b.spid,c.sql_text 

   from v$session a,v$process b,v$sqlarea c 

   where a.paddr=b.addr 

   and a.sql_hash_value=c.hash_value  

   and a.username is not null; 

02、对数据进行限定和排序

空值（null）

select ename, sal from emp where sal<=comm;

在comm列中存在的空值（null）的数据行将对比较表达式产生一个空值。

日期类型做条件

Examples

… WHERE hiredate='01-JAN-95'

the expr cannot be an alias. 

Mention in brief about the date TO_DATE function, when the date is to be entered in a format other than the default format. 

select ename, hiredate from emp where hiredate > '1982-01-01';     //隐式

select ename, hiredate from emp where hiredate > to_date('1982-01-01','YYYY-MM-DD');     //显式

between   and  //日期类型也可使用

运算符优先级：

select ename, job, sal ,comm from emp

where job='SALESMAN' 

OR    job='PRESIDENT' 

AND   sal> 1500

LIKE运算中，查询%或_的匹配     使用escape

select * from dept

where dname like '_\%LES' escape '\'

/ 

以列号排序

Let the students know that you can also sort by a column number in the SELECT list. The example below will sort the output in the descending order by salary.

select ename, sal from emp

order by 2 desc

以多列排序

Display name and salary of all employees. Order the result by department number and then descending order by salary.

select ename, sal, deptno from emp

order by deptno, sal desc

Show that the DEPTNO column is sorted in ascending order and the SAL column in descending order.

数值型从小到大

日期型从早到晚

字符型按字母排序

空值升序时在最下方 降序时在最上方

03、处理单行函数

single row functions 单行函数

These functions operate on single rows only and return one result per row

接受一个或多个参数并为查询中得到的每一个数据行都返回一个结果

An argument can be one of the following:

• User-supplied constant
• Variable value
• Column name
• Expression

multiple row functions 多行函数     （where 子句之后 只能跟单行函数）

   对多数据行的群组进行操作，并且每组返回一个结果

字符函数

lower('SQL Course')----->sql course 返回小写

UPPER('sql course')----->SQL COURSE 返回大写

INITCAP('SQL Course')-----> Sql Course 返回首字母大写

CONCAT('good','string')---->good string 拼接     //只能拼接2个字符串

SUBSTR('String',1,3)---->str 截取

LENGTH('String')---->6 长度

Lpad(first_name,10,'* ')右填充

Rpad(first_name,10,' *')左填充

ROUND(45.923,2)---->45.92 四舍五入

TRUNC(45.923)---->45 不入只截

to_number('1550') 显式数据类型转换

to_char(salary,'$99,999.99') 数字转字符

TRIM函数

1. trim()删除字符串两边的空格。

2. ltrim()删除字符串左边的空格。

3. rtrim()删除字符串右边的空格。     //select rtrim( '    ssmitss    ')||'ok' from dual

4. trim('字符1' from '字符串2')  分别从字符2串的两边开始，删除指定的字符1。

5. trim([leading | trailing | both] trim_char from string)　从字符串String中删除指定的字符trim_char。

   leading:从字符串的头开始删除。

   trailing:从字符串的尾部开始删除。     //select trim(trailing 's' from 'ssmitss') from dual

   borth:从字符串的两边删除。

6. tim()只能删除半角空格。

 

数字函数

round 对指定的值做四舍五入，可对日期类型操作

trunc对指定的值取整

select ename, hiredate, trunc(hiredate,'YEAR')     

from emp          //对日期也可取整

mod 返回除法后的余数

日期函数

Oracle Date Format

Oracle stores dates in an internal numeric format, 

（因为日期在oracle里是以数字形式存储的，所以可对它进行加减运算，计算是以天为单位。）

representing the century, year, month, day, hours, minutes, and seconds.

The default display and input format for any date is DD-MON-YY.

Valid Oracle dates are between January 1, （公元前）4712 , and December 31, （公元）9999 

时间格式

select to_date('2003-11-04 00:00:00' ,'YYYY-MM-DD HH24:Mi:ss')

FROM dual

MONTHS_BETWEEN //计算两个日期之间的月数

 select months_between('1994-04-01','1992-04-01') mm from dual

ADD_MONTHS     //给日期增加月份

select add_months('1992-03-01',4) am from dual

NEXT_DAY     //给定日期的下一个指定日期（下周几）

select next_day(sysdate, 'friday') n_fd from dual

LAST_DAY     //日期当前月份的最后一天

select last_day('1989-03-28') l_d from dual

ROUND四舍五入，TRUNC截断     

SELECT  empno, hiredate,
     round(hiredate,'MONTH') AS round,
     trunc(hiredate,'MONTH') AS trunc
 FROM     emp
 WHERE     empno=7788

SELECT  empno, hiredate, 

round(hiredate,'YEAR') AS round,

trunc(hiredate,'YEAR') AS trunc

 FROM emp

 WHERE empno=7839

数据类型转换

TO_CHAR

日期到字符

select ename, hiredate, to_char(hiredate, 'MM/YY') month_hired
from emp
where ename='SCOTT'

fm压缩空格或左边的'0'

to_char(hiredate, 'fmMM/YY') 

to_char(hiredate,'fmDD MM YYYY')

数字到字符：9表示数字，$本地化货币字符

select ename, to_char(sal, '$99,999.99') Salary
from emp
where ename='SCOTT'

TO_NUMBER

SELECT to_number('$123.45','$999.99') "result" FROM dual

TODATE

select to_date('1983-11-12', 'YYYY-MM-DD') tmp_DATE from dual

chr()函数将ASCII码转换为字符： ASCII码 –》 字符

ascii()函数将字符转换为ASCII码：字符 –》 ASCII码

在oracle中chr()函数和ascii()是一对反函数。

求字符对应的ASCII值

select ASCII('A') FROM dual     

decode函数用法：

SELECT job, sal,

        DECODE(job, 'ANALYST', SAL*1.1,

                          'CLERK',   SAL*1.15,

                          'MANAGER', SAL*1.20,

                                                 SAL)

                               REVISED_SALARY

   FROM   emp

/

SELECT ename, sal,

         DECODE(TRUNC(sal/1000, 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    emp

WHERE   deptno = 30  

04、多表查询

笛卡尔积

连接条件无效或被省略，两个表的所有行都发生连接，所有行的组合都会返回（n*m）

select e.ename, e.deptno, d.loc

from emp e , dept d

select e.ename, e.deptno, d.loc

from emp e , dept d

where d.deptno=30

等值连接（内连接或简单连接）

两个表的连接条件的列值必须相等，通常这样的连接包含一个主键和一个外键

select e.ename, e.deptno, d.loc

from emp e, dept d

where e.deptno=d.deptno

     and e.ename='SCOTT'

/

多于两个表的连接

create table manager

as

select ename, deptno, sal, job

from emp

/

select e.empno, m.ename, m.deptno, d.loc
from emp e, manager m, dept d
where m.deptno=d.deptno
   and m.ename=e.ename
   and e.job=UPPER('manager')

/

非等值连接

使用其他连接运算符

select e.empno, e.ename, e.sal, s.grade

from emp e, salgrade s

where e.sal

  between s.losal and hisal

/

外部连接

select e.ename, d.deptno, d.dname

from emp e, dept d

where e.deptno(+)=d.deptno

/

dept表为不缺乏连接信息的表，emp表为缺乏连接信息的表

外部连接运算符（+）放在缺少相关连接信息的表的一侧，

它能返回该表中那些在另一个表中没有得到匹配的记录

外部连接条件中不能使用IN或OR运算符与其他条件相连。

自连接

select e.empno, e.ename, e.job, e.mgr, m.ename ManagerName

from emp e, emp m

where e.mgr=m.empno

/

SQL99语法

左外连接

select e.ename,e.deptno,d.dname
from emp e
left outer join dept d
on e.deptno=d.deptno
/

右外连接

select e.ename,e.deptno,d.dname

from emp e

right outer join dept d

on e.deptno=d.deptno

/

全外连接

select e.ename,e.deptno,d.dname

from emp e

full outer join dept d

on e.deptno=d.deptno

/

05、分组函数

组函数:对一组数据进行操作，最后返回一个值，用作统计分析。

求所有的非空值

关键字DISTINCT可以排除重复值。

参数的类型可以是CHAR,VARCHAR2,NUMBER,DATE

除了COUNT(*)外，其他所有组函数都忽略空值，可以使用NVL函数处理

select之后,如果其中有一个组函数,那么其他的也要是组函数..

  avg() 平均

  count() 计数

  max() 最大值

  min() 最小值

  sum() 求和

数字类型可以使用很多组函数

select sum(sal) sum, avg(sal) avg, max(sal) max, min(sal) min, count(*) count

from emp

/

对日期类型使用MIN,MAX

select min(hiredate), max(hiredate)

from emp

COUNT(*)函数返回表中行的总数，包括重复行与数据列中含有空值的行。

COUNT(EXPR)返回expr标识的列所含非空行的数量。

在组函数中使用NVL函数

select avg(nvl(comm,0)) from emp

将分母变成有效的值14

GROUP BY创建数据组

select deptno, avg(nvl(sal,0)) from emp
group by deptno

/     

select deptno, avg(nvl(sal,0)) avgcomm
from emp
where sal > 1200
group by deptno

/          使用WHERE先过滤到一些信息

select deptno, avg(nvl(sal,0)) avgcomm

from emp

group by deptno

order by avg(nvl(sal,0))

/          对分组结果排序

对分组结果进行过滤

select deptno, avg(sal) avgcomm

from emp

where avg(sal)>2000

group by deptno

/          错误的

应该使用HAVING子句

select deptno, avg(sal) avgcomm

from emp

group by deptno

having avg(sal)>2000

/

注意：

确保SELECT列表中除了组函数的项，所有列都包含在GROUP BY子句中。

GROUP BY产生的结果，是基于分组列升序排列的。

如果使用ORDER BY子句对分组结果排序，确保ORDER BY子句在最后。

  stddev()返回标准差

  variance()返回统计方差

06、子查询

子查询返回的值可以被外部查询使用，

这样的复合查询等效与执行两个连续的查询。

单行子查询     采用单行比较运算符（>，<，=，<>)

内部SELECT子句只返回一行结果（单列）

select ename,sal

from emp

where sal > (

     select sal from emp

     where ename='JONES'）

/

和员工7369从事相同工作并且工资大于员工7876的员工的姓名和工作

select ename,job
from emp
where job=(
        select job
        from emp
        where empno=7369
        )
and
       sal > (
        select sal
        from emp
        where empno=7876
        )
/

在多行子查询中使用any      （>大于最小的，<小于最大的）

>     比子查询中返回的列表中最小的大就行

select ename, job, sal

from emp

where sal >= any (

   select sal

   from emp

   where job='CLERK')

and

   job<>'CLERK'

/

10 rows selected.

在多行子查询中使用all      （>大于最大的，<小于最小的）

>     比子查询中返回的列表中最大的大才行

select ename, job, sal

from emp

where sal >= all (

   select sal

   from emp

   where job='CLERK')

and

   job<>'CLERK'

/

8 rows selected.

工资高于所有部门的平均工资的员工

select ename, job, sal

from emp

where sal > all(

    select avg(sal)

    from emp

    group by deptno)

/

NOT运算操作符不能使用在IN,ANY,ALL操作。

07、多列子查询

多列子查询是返回多列结果的内部SELECT语句

将WHERE子句中多个条件合并成一个

列比较有成对比较与不成对比较两种

实验准备

update emp
set sal=1600,comm=300
where ename='SMITH'

/

update emp

set sal=1500,comm=300

where ename='CLARK'

/

成对比较多列子查询

主查询每一行中的列都要与子查询返回列表中的相应列进行比较

只有各列完全匹配时才显示主查询中的该数据行

select ename,deptno,sal,comm

from emp

where (sal,nvl(comm,0)) in(

        select sal, nvl(comm,0)

        from emp

        where deptno=30)

and deptno<>30

/

显示与30部门中任意一个员工的工资和奖金完全相同的员工的信息，

但该员工不是来自30部门

非成对比较多列子查询

select ename,deptno,sal,comm

from emp

where sal in(

        select sal

        from emp

        where deptno=30)

and

        nvl(comm,0) in (

        select nvl(comm,0)

        from emp

        where deptno=30)

and deptno<>30

/

两个子查询返回的值分别与主查询中的sal和comm列比较

员工的工资与30部门任意一个员工相同，同时

奖金也与30部门的任意一个员工相同，则输出该员工信息。

子查询中的空值问题

查找出没有下属的员工（自己的员工号不在经理之列的）

select e.ename

from emp e

where e.empno not in

    (select m.mgr

     from emp m)

/

no rows selected

子查询返回的结果中有一条空值

所有的条件和空值比较结果都是空值

select e.ename

from emp e

where e.empno not in

    (select nvl(m.mgr,0)

     from emp m)

/          排除空值的影响

8 rows selected.

from子句中使用子查询

select e.ename, e.sal, e.deptno, b.salavg

from emp e, (

        select deptno,avg(sal) salavg

        from emp

        group by deptno) b

where e.deptno=b.deptno

and     e.sal > b.salavg

/

员工的工资大于他所在的部门的平均工资的话，显示其信息。

09、DML语句

表的创建者默认对表有DML操作权限，但是对于其他用户，必须显式的授权。

DML语句操作时要受到各种约束性检查

建立空表

create table manager

as

select empno,ename,sal,hiredate

from emp1

where 1=2

/

从另一个表中复制数据

insert into manager1(

     select empno,ename,sal,hiredate

     from emp1

     where job='MANAGER')

/

插入操作约束：

违反非空约束

违反唯一性约束

违反完整性约束（外键约束）

违反CHECK约束

数据类型不匹配

值超出列规定的范围

更新数据

update emp1

set (job, deptno)=(

    select job, deptno

    from emp1

    where empno=7499)

where empno=7698

/

更新受到完整性约束限制

删除数据

删除受到完整性约束限制

通过delete删除的数据可以回滚，truncate语句不能够回滚，因为它是DDL语句。

09、TRANSACTION事务

事务组成：

一组DML语句

一个DDL语句

一个DCL语句

事务开始于第一条可执行的SQL语句

结束于：

COMMIT（提交）或ROLLBACK（回滚）

DDL语句被执行（提交）

DCL语句被执行（提交）

用户退出SQL.PLUS（正常退出是提交，非正常退出是回滚）

机器故障或系统崩溃（回滚）

SAVEPOINT实验

savepoint sp1；

delete from emp1 where empno=7900;

savepoint sp2;

update emp1 set ename='XIAOMAO' where empno=9000;

select * from emp1；

rollback to sp2;

select * from emp1；

rollback to sp1;

读一致性与锁定

10、创建和管理表

对象            描述

表                存储的基本单元，由行和列组成

视图            一个逻辑的数据集，数据来自一个或者多个表

序列            产生主键值

索引            目的提高查询的性能

同义词    给出对象的别名

命名表

必须以一个字母开始

可以是1-10个字符的长度

仅能包括字符： A–Z, a–z, 0–9, _, $, 和#

不能与本用户拥有的其他对象重名

不能是一个 Oracle服务器的保留字

创建表

create table stud(id number(2),name varchar(6));

新用户创建表实例

SQL> create user damao identified by damao;

SQL> grant create session to damao;

SQL> grant create table to damao;

SQL> alter user damao quota 10m on users;

修改表

alter table stud

modify (id not null)     添加非空约束

/

alter table stud

add (age number(3))     增加一列

/

alter table stud

modify (age default 16)     指定缺省值

/

alter table stud

drop colume age          丢弃一列

/

删除表

drop table stud

/

只有表的创建者或拥有DROP ANY TABLE权限的用户才能删除表。

修改表名称     RENAME 旧名 TO 新名

截断表     TRUNCATE TABLE 表名

删除所有记录并释放表的存储空间。

要求具备DELETE TABLE的权限

11、约束

列级约束：只能引用一个列，表中可以有多个列级约束。

表级约束：引用一个或多个列，通常用来定义主键。

非空约束

创建表时定义

create table stud (id number(2) not null, name varchar(4))

/

在已存在的表上添加唯一性约束

alter table stud

modify (id number(2) not null, name varchar(4))

/

查看非空约束

select constraint_name,constraint_type,table_name
from user_constraints
where owner='SCOTT'
and table_name=UPPER('stud')
/

删除非空约束

alter table stud
drop constraint SYS_C005454          SYS_C005454是系统生成的约束名
/

唯一性约束     （唯一性约束允许列中输入空值）

创建表时定义

create table stud(

     id number(2),

     name varchar2(4),

     constraint id_uk unique(id)

     )

/

在已存在的表上添加唯一性约束

alter table stud
add CONSTRAINT id_uk UNIQUE (id)
/

删除唯一约束

alter table stud
drop unique (id)
/

主键约束          （每个表只能有一个主键，集唯一性和非空）

创建表时定义

CREATE TABLE stud(

id NUMBER(2),

name VARCHAR2(6),

CONSTRAINT id_pk PRIMARY KEY (id),

CONSTRAINT name_uk UNIQUE (name))

/

在已存在表上添加约束

ALTER TABLE dept1

ADD CONSTRAINT d_pk PRIMARY KEY (deptno)

/

删除主键约束

alter table dept1
drop constraint d_pk
/

外键约束          （引用完整性约束）

和同一个表或其他表的主关键字或唯一关键字建立连接关系，

外键值必须和父表中的值匹配或者为空值。

添加外键约束

ALTER TABLE emp1
ADD CONSTRAINT e_fk FOREIGN KEY(deptno)
REFERENCES dept1(deptno)
/

测试：

delete from dept1 where deptno=30

*

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.E_FK) violated - child record found

删除外键约束

alter table emp1
drop constraint e_fk
/

ON DELETE CASCADE关键字

重建外键

ALTER TABLE emp1
DROP CONSTRAINT e_fk
/

ALTER TABLE emp1

ADD CONSTRAINT e_fk FOREIGN KEY(deptno)

REFERENCES dept1(deptno) ON DELETE CASCADE

/

测试：

delete from dept1 where deptno=30

1 row deleted.

ON DELETE CASCAD要慎用，主表中删除一行数据就可能引发从表中大量数据丢失。

CHECK约束

alter table emp1
add constraint e_no_ck check (empno >1000)
/

验证

SQL> insert into emp1 (empno) values(999);
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.E_NO_CK) violated

使约束失效/有效

alter table emp1
disable constraint e_fk
/

select constraint_name,status from user_constraints where constraint_name='E_FK'

/

alter table emp1
enable constraint e_fk

/

级联约束

测试表

CREATE TABLE test2 (

    pk NUMBER PRIMARY KEY,

    fk NUMBER,

    col1 NUMBER,

    col2 NUMBER,

    CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test2,

    CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),

    CONSTRAINT ck2 CHECK (col2 > 0)

    )

/

以下语句不能执行

ALTER TABLE test2 DROP (pk);

ALTER TABLE test2 DROP (col1);

要带cascade constraints才行

ALTER TABLE test1 DROP (col1) cascade constraints;

/

CASCADE CONSTRAINTS 将丢弃在删除列上的唯一键或主键约束。

12、视图

为什么使用视图

限制数据的存取：用户只能看到基表的部分信息。

使得复杂的查询变得容易：视图中的数据可能来自多个表。

使得数据相对比较独立：从项目开发的角度，模块对应视图，模块包含多个表，模块发生变化后只需修改相应的视图，对应的表的结构无需修改。

代表对同一数据的不同视角：不同部门的员工只能看到本部门的信息。

创建视图

CREATE VIEW empvu10
AS SELECT empno, ename, job
FROM emp1
WHERE deptno=30
/                              （定义视图的查询中不能使用ORDER BY子句）

查询视图

视图定义的SELECT语句保存在LONG型的TEXT字段中。

使用SET LONG 150增加LONG型最大显示宽度，默认为80

select * from user_views

/

语法说明

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

  [(alias[, alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY];

OR REPLACE      如果视图已存在，相当于修改视图

FORCE | [NOFORCE]      无论基表是否存在都建立视图 [只有基表存在能创建]

WITH CHECK OPTION     通过视图执行的INSERT和UPDATE操作不能创建该视图检索不到的数据行

WITH READ ONLY      禁止对视图执行DML操作

建立复杂的视图

CREATE VIEW dept_sum_vu
(dname,minsal,maxsal,avgsal)
AS SELECT d.dname,MIN(e.sal),
    MAX(sal),AVG(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname
/

数据来自多个表，查询中使用了分组函数

WITH CHECK OPTION选项示例

建立实验视图

CREATE OR REPLACE VIEW empvu20

AS SELECT *

FROM emp

WHERE deptno=20

WITH CHECK OPTION CONSTRAINT empvu20_ck

/

查询生成的视图索引

 select constraint_name,constraint_type from user_constraints where constraint_type='V'

/

通过视图更新员工部门号

UPDATE empvu20

SET deptno=10

WHERE empno=7902

/

UPDATE empvu20
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

违反了约束，因为部门号更新为10后，该视图不能再检索到该行。

TOP-N          (利用内联视图实现）

错误的     （先得到3行，为这3行排序）

select sal

  from emp1

  where rownum <= 3

  order by sal desc

/

正确的     （先对所有数据排序，再取前三行）

select sal
from ( select sal from emp1 order by sal desc)
where rownum < 4
/

视图的DML操作

含有如下情况，则不能删除视图中的数据:

含有聚组函数

含有GROUP BY 子句

含有 DISTINCT 关键字

含有ROWNUM 这个伪列

含有如下情况，不能修改该视图中的数据:

上面提到的任何一种情况。

列是由表达式来进行定义的

含有如下情况，不能增加该视图中的数据:

上面提到的任何一种情况。

在基表中包含有 NOT NULL约束的列，然而该列并没有在视图中出现

原则：只要视图中的数据不是来自基表的原始数据，就不能对该数据做DML操作。

DML示例：建立视图包含rownum

create or replace view e1_m_v

as

select ename, sal, hiredate from emp1

where job='MANAGER'

and   rownum < 3

/

对该视图做DML操作

update e1_m_v set sal=3000 where ename='BLAKE'

/

delete from e1_m_v where ename='BLAKE'

/

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

13、索引

索引能优化查询，不能优化DML操作，频繁的DML操作反而会引起大量的索引维护。

索引建立指导

适合情况不适合情况经常用于WHERE子句或作为连接条件的列表很小所含数据值范围比较的列列很少在查询中作为条件含有大量空值的列多数情况下查询出大于总记录的5%的表条件一的两个或多个组合列频繁更新的表多数情况下只查询总记录的5%的表 

索引相关的数据字典

USER_INDEXES     

SELECT index_name, index_type, table_name

FROM user_indexes

/

USER_IND_CULUMNS

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

/

开启SQL.PLUS的自动跟踪功能

set autotrace on

TABLE ACCESS FULL     未使用索引

INDEX UNIQUE SCAN     使用索引 

手工创建索引

（创建索引，在连接查询中有效的提高索引速度，数据量大时明显）

建立EMP1表ename列的索引

CREATE INDEX e_ename_idx

ON emp1 (ename)

/

索引函数

CREATE INDEX uppercase_idx ON emp (UPPER(ename))

/

SELECT * FROM emp WHERE UPPER(ename) = 'KING'

/

执行index range scan

SELECT * FROM emp

WHERE UPPER (ename) IS NOT NULL

ORDER BY UPPER (ename)

/                         为确保使用索引而不是全表扫描，要保证函数中的值为非空，

执行index full scan

删除索引

DROP INDEX e_ename_idx

/

----如果删除某个表，那么和表相关的索引，约束都自动被删除，但是视图和序列依然保留着。

索引扫描分类：
◆索引唯一扫描(index unique scan)

通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中。

◆索引范围扫描(index range scan)

在非唯一索引上，可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。

使用index rang scan的3种情况：
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(b) 在组合索引上，只使用部分列进行查询，导致查询出多行
(c) 对非唯一索引列上进行的任何查询。

◆索引全扫描(index full scan)

对整个index进行扫描，并且顺序的读取其中数据。

全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

◆索引快速扫描(index fast full scan)

扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。

分析器根据要访问的数据量和索引的填充因子等属性判断使用RANG SCAN或FULL INDEX SCA

13、同义词

同义词

In the Oracle Server, the DBA can specifically grant the CREATE PUBLIC SYNONYM to any user, allowing that user to create public synonyms.

管理员能将CREATE PUBLIC SYNONYM授权给任一用户，允许那些用户创建公共的同义词

管理员创建公共同义词

conn hr/hr

Connected.

select * from scott.dept;

/

conn sys/oracle as sysdba

Connected.

CREATE PUBLIC SYNONYM sd FOR scott.dept;

/

conn hr/hr

Connected.

select * from sd;

/

DROP PUBLIC SYNONYM sd1

/

也可以将权限给SCOTT用户

GRANT CREATE PUBLIC SYNONYM TO scott

/

conn scott/oracle

Connected.

SQL> CREATE PUBLIC SYNONYM sd1 FOR dept

/

conn hr/hr

Connected.

SQL> select * from sd1;

13、序列

创建序列语法

CREATE SEQUENCE sequence

   [INCREMENT BY n]               

//序列值间隔，默认为1     如果指定的为负数，序列值将递减产生

   [START WITH n]                    

//起始序列值，默认为1

   [{MAXVALUE n | NOMAXVALUE}]          

//最大序列值 | 最大序列值为10的27次方，降序的最大值为-1

   [{MINVALUE n | NOMINVALUE}]          

//最小序列值 | 升序最小序列值为1，降序最小序列值为-10的26次方

   [{CYCLE | NOCYCLE}]            

//序列值在达到指定最大或最小值之后继续产生序列值（从头再开始），NOCYCLE不再生产（默认值）

   [{CACHE n | NOCACHE}];          

//序列被预先分配存储到内存，NOCACHE默认值

建立序列

CREATE SEQUENCE dept_deptno
INCREMENT BY 10
START WITH 50
MAXVALUE 200
NOCACHE
NOCYCLE
/

伪列：NEXTVAL和CURRVAL

NEXTVAL：用于返回序列下一个可获取的值。引用（sequence）.NEXTVAL时，一个新的序列值产生并保存到CURRVAL伪列。

               对于每次引用都产生下一个序列值并返回，对于不同的用户引用也是一样。

CURRVAL：被引用前必须使用NEXTVAL产生一个序列值，用于提取当前用户产生的序列值。

查询序列

查询当前序列值

SELECT dept_deptno.CURRVAL FROM dual;

查询下一个返回的序列值

SELECT dept_deptno.NEXTVAL FROM dual;          注意：这样也是对NEXTVAL的引用，会产生下一个序列值

如果序列不是保存在内存中的，可以查看数据字典，这样不会产生新的序列值

SELECT sequence_name, min_value, max_value,

       increment_by,last_number

FROM   user_sequences

/

使用序列

未使用NEXTVAL时情况

SQL> INSERT INTO dept1(deptno,dname,loc)

  2  VALUES (dept_deptno.CURRVAL,'IT_DEPT','BEIJING')

  3  /

VALUES (dept_deptno.CURRVAL,'IT_DEPT','BEIJING')

        *

ERROR at line 2:

ORA-08002: sequence DEPT_DEPTNO.CURRVAL is not yet defined in this session

正确的用法

INSERT INTO dept1(deptno,dname,loc)

VALUES (dept_deptno.NEXTVAL,'IT_DEPT','BEIJING')

/

1 row created.

INSERT INTO dept1(deptno,dname,loc)

VALUES (dept_deptno.NEXTVAL,'HR_DEPT','SHANGHAI')

/

1 row created.

SELECT * FROM dept1;

    DEPTNO DNAME                LOC

---------- -------------------- --------------------

        10 ACCOUNTING           NEW YORK

        20 RESEARCH             DALLAS

        30 SALES                CHICAGO

        40 OPERATIONS           BOSTON

        50 IT_DEPT              BEIJING

        60 HR_DEPT              SHANGHAI

6 rows selected.

修改序列

ALTER SEQUENCE dept_deptno

INCREMENT BY 10

MAXVALUE 400

NOCACHE

NOCYCLE

/

Sequence altered.

删除序列

DROP SEQUENCE dept_deptno;

Sequence dropped.

14、用户访问控制

数据库安全实现以下功能：

控制数据库访问

对数据库中的特定对象设置访问权限

通过数据字典验证权限的授予及回收

为数据库字典创建同义词

数据库安全分为系统安全和数据安全

系统安全：用户名和口令，分配给用户的磁盘空间及用户的系统操作

数据库安全：对数据库对象的访问及操作

用户具备系统权限才能够访问数据库

具备对象权限才能访问数据库中的对象

SCHEMA     模式

一组对象的集合，表、视图、序列号等。模式由数据库用户所拥有并且和用户具有相同的名字。

系统权限通常由DBA授予 （100多种）    （也可被其他用户或角色授予）

典型DBA权限

CREATE USER

DROP  USER

BACKUP ANY TABLE

SELECT ANY TABLE

CREATE ANY TABLE

典型用户系统权限

CREATE SESSION

CREATE TABLE

CREATE SEQUENCE

CREATE VIEW

CREATE PROCEDURE

8种对象权限

ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE

创建用户

CREATE USER user IDENTIFIED BY password

更改密码

ALTER USER user IDENTIFIED BY password

创建角色

CREATE ROLE role

授予系统权限

GRANT sys_privs,[sys_privs] TO user|role

授予对象权限

GRANT object_privs ON object TO user|role|PUBLIC [WITH GRANT OPTION]

为用户分配角色

GRANT role TO user

实验一：     新用户登录数据库

建立tom用户     （sys）

CREATE USER tom IDENTIFIED BY tom;

授予连接权限     （sys）

GRANT CREATE SESSION TO tom;

查看tom的权限     （tom）

SELECT * FROM SESSION_PRIVS;

回收连接权限     （sys）

REVOKE CREATE SESSION FROM tom;

授予CONNECT角色     （sys）

GRANT CONNECT TO tom;          （给用户赋予某个角色后，要重新建立新会话才生效）

实验二：     新用户授予创建表的权限

授予建表权限     （sys）

GRANT CREATE TABLE TO tom;     

尝试创建表     （tom）

CREATE TABLE t1(id number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'     提示无表空间操作权

GRANT UNLIMITED TABLESPACE TO tom;     或

ALTER USER tom QUOTA 10M ON users;     分配表空间配额

回收权限     （sys）

REVOKE UNLIMITED TABLESPACE FROM tom;

REVOKE CREATE TABLE FROM tom;

授予tom用户RESOURCE角色     （sys）

GRANT RESOURCE TO tom;

查看当前用户被授予的角色     （tom）

SELECT * FROM USER_ROLE_PRIVS;

实验三：     查看当前用户所授予角色的系统权限

SELECT * FROM ROLE_SYS_PRIVS;     （tom）

实验四：     WITH GRANT OPTION（对象权限）选项

例子：

先建立两个测试用户tom和bob     （sys）

CREATE USER tom IDENTIFIED BY tomps;     GRANT CREATE SESSION TO tom;

CREATE USER bob IDENTIFIED BY bobps;     GRANT CREATE SESSION TO bob;

为tom用户授权（使用WITH GRANT OPTION）     （scott）

GRANT SELECT ON emp TO tom WITH GRANT OPTION;

检查tom用户能否访问scott.emp ....

查看tom用户得到的对象级权限     （tom）

SELECT * FROM USER_TAB_PRIVS_RECD;

OWNER      TABLE_NAME      GRANTOR      PRIVILEGE          GRANTABLE     HIERARCHY

--------------- ---------------         ------------       -----------------------  ---------             ---------

SCOTT           EMP             SCOTT          SELECT                YES                NO

                                                                                     GRANTABL可转让的

tom用户把该权限分给bob用户     （tom）

GRANT SELECT ON scott.emp TO bob;

检查bob用户能否访问scott.emp ....

查看tom用户得到的对象级权限     （bob）

select * from user_tab_privs_recd;

OWNER      TABLE_NAME      GRANTOR      PRIVILEGE          GRANTABLE      HIERARCHY

--------------- ---------------         ------------        --------------------    ----------------      ---------

SCOTT           EMP              TOM             SELECT               NO                 NO

回收对象权限

REVOKE SELECT ON emp FROM tom;     （scott）

回收了tom的对象权限后，tom二次分配给bob的对象权限也隐式的收回了。

实验五：     WITH ADMIN OPTION （系统权限）

为tom用户授予系统权限

GRANT CREATE TABLE TO tom WITH ADMIN OPTION;     （sys）

查看tom用户拥有的系统权限     

SELECT * FROM USER_SYS_PRIVS;     （tom）

USERNAME     PRIVILEGE               ADMIN_OPT

---------------    --------------------          ---------

TOM             CREATE TABLE         YES

TOM             CREATE SESSION      NO

二次分配给bob用户

GRANT CREATE TABLE TO bob;     （tom）

查看bob用户系统权限

SELECT * FROM USER_SYS_PRIVS;     （bob）

USERNAME     PRIVILEGE             ADMIN_OPT

---------------    --------------------        ---------

BOB             CREATE TABLE         NO

BOB             CREATE SESSION      NO

收回系统权限

REVOKE CREATE TABLE FROM tom;     （sys）

回收了tom的系统权限后，tom二次分配给bob的系统权限并不会自动收回。

备注：在drop有数据的用户的时候 会报错误 ORA-01922: CASCADE must be specified to drop 'username'. 

正如提示的 你只需要输入命令 drop user 用户名 cascade。使用CASCADE选项时，用户及实体马上被删除。

与权限有关的数据字典

SESSION_PRIVS          //用户当前会话拥有的系统权限

USER_ROLE_PRIVS      //用户被授予的角色

ROLE_SYS_PRIVS        //用户当前拥有的角色的系统权限

USER_SYS_PRIVS      //直接授予用户的系统权限

USER_TAB_PRIVS      //授予用户的对象权限  

包含了当前用户给其他用户的对象权限和其他用户给当前用户的对象权限

ROLE_TAB_PRIVS      //授予角色的表的权限

USER_TAB_PRIVS_RECD      //其他用户给当前用户的对象权限

USER_TAB_PRIVS_MADE      //当前用户给其他用户的对象权限

USER_COL_PRIVS_MADE      //在用户对象列一级上被分配的对象权限

USER_COL_PRIVS_RECD      //在指定列上分配给用户的对象权限