Oralce学习笔记(3)

1.   删除内容重复的记录

需求：将表中内容的重复的记录删除，剩下的都是不重复的记录。

方法1：

创建一个临时表，并把加了distinct的查询放到临时表中，删除原表，将临时表的更名为原表名。

create table t1 as select distinct * fromt_student;

drop table t_student purge;

alter table t1 rename to t_student;

这种方法从表面上看是达到目的了，但新创建出来的表的结构(约束)被改变了，是不可取的做法。

方法2：

创建一个临时表，并把加了distinct的查询放到临时表中，清空原表(即保留原表的结构)，将临时表的记录放到原表中。

create table t1 as select distinct *  from t_student;

truncate table t_student;

insert into t_student select * from t1;

方法3：

使用子查询，结合rowid。

rowid概念：每一条记录的id，伪列。唯一的，固定的，属于某一个表，其值是一个指针，它指向本行数据存储的地址。是一物理上存在列，可以像其它实际列一样进行运算。

 

结合rowid，先按表中的所有列进行分组，取每一组中最大或者最小的rowid。然后删除余下的。

delete from t_student where rowid not in( select min(rowid) from t_student group byname,age);

方法4：

使用相关子查询，结合rowid。

delete from t_student t1 wheret1.rowid<>(select min(t2.rowid) from t_student t2 where t2.name=

t1.name and t2.age=t1.age);

2.   管理用户安全

2.1用户权限系统概述

对象权限粒度可粗可细。可以把权限设置到某一个表，也可以到某一个表的某一个字段。

A、权限

  系统权限(登录/能否创建表/删除表...)

  对象权限 (对某一张表[对象]能否访问select,insert,delete,update)，视图，序列等其它对象)

  表1,系统权限表

 ID   权限名称    权限描述

 1     登录(CREATE SESSION)

 2     建表(CREATE TABLE)  

B、用户

  表2,用户表

 ID   用户名    密码   状态

  1   scott      test 0

  2   system     test 0

  3   ht         test 1

 

为了表示某一个用户有哪些权限

  表3,用户权限表(用户与权限是多对多的关系)

  用户ID   权限ID

   1        1

   1        2

   2        1

   3        2

C、角色

   表4,角色表

  ID    角色名 

   1     MANAGER

   2     开发人员

 

  表5,角色权限表

    角色ID   权限ID

    1         1

    1         2

    2         2

  表6,用户角色表，把角色分配给用户

  用户ID   角色ID

  1         1

  2         1

用户，角色，权限均为多对多的关系。

当用户少的时候，可以不用角色的功能，当用户多的时候，使用角色可以简化对权限的维护。

 

内置的用户：SYS 帐户，数据拥有者,基本表，及数据字典表属于SYS帐户，执行创建数据库，启动及关闭数据等，数据库操作员， DBA 权限.

SYSTEM帐户，数据库管理员DBA。

2.2权限的基本应用

创建账户

create user test identified by test;

删除用户

drop user test cascade;

修改账户密码

alter user test identified by t1;

锁定账户

alter user test account lock;

解锁账户

alter user test account unlock;

授予账户登录的权限

grant create session to test;

授予账户创建表的权限

grant create table to test;

一次性赋予多个权限(用逗号隔开)

grant create session,create table o test;

回收权限

revoke create session, create table fromtest;

配额(11g默认会给用户分配users表空间的访问配额)

alter user test quota unlimited on users;

2.3权限

1）系统权限

Oracle定义好的，系统一共有多少功能，软件开完以后，就确定了，软件使用者无法改变系统权限。

2）对象权限

对表等对象是否可以执行DML操作(select ,insert ,delete,update)。

授予对象权限

grant select on scott.emp to test;

回收对象权限

revoke select on scott.emp from test;

将自己的对象权限授予其他用户

scott登录后帐号把他的表给其它访问

grant select on emp to test;

2.4权限级联

A、权限级联：是否可以把自己的权限赋给别人。

对系统权限来说:AdminOption

对对象权限来说:GrantOption

来指定是否可以把一个权限进一步赋予别人。

grant create table to test with adminoption;(用户test可以将创建表的权限授予其他用户)

grant select on scott.emp to test withgrant option;

给t1赋予create session权限，并让可以把权限授权他人,使用admin option选项

grant create session to t1 with adminoption

 

B、关于级联收回：对于系统权限,当使用级联的时候，不级联收回。对于对象权限，会级联收回。

2.5角色

创建角色

create role MYROLE;

给角色赋予权限

grant create session,create table to myrole;

把角色赋予t3

grant myrole to t3;

删除角色

drop role myrole;

系统定义的角色:

CONNECT

RESOURCE--开发人员所需要的权限

SELECT_CATALOG_ROLE--登录oem

2.6概要文件

配置用户的所遵循一些基本信息。安全性上的一些方案，用户的管理规则。

每个用户只能被关联到一个概要文件。

如：

控制资源消费

管理帐户状态和密码有效期

3.   表空间

3.1概述

数据库物理上是一些磁盘文件，逻辑上会通过表空间来管理数据文件。建表的时候，可以指定表空间，或者通过建用户的时候，指定用户的默认表空间。一个表空间可以对应多个数据文件。一个数据文件只能属于一个表空间。

 

用户的默认表空间(USERS)：用户登录进来，所创建的对象，默认存放表空间。

临时表空间：当我们写sql语句的时候，会需要一些空间来存放临时的计算结果。

表空间SYSTEM，是系统数据文件所用的表空间。

3.2表空间的基本应用

创建表空间

 

create tablespace data01 datafile'f:\data01.dbf' size 10m uniform size 128k;

删除表空间(非空时，会将表空间的所有表一起删除)

drop tablespace data01 includingcontents;

给用户指定表空间

alter user test default tablespace data01;

指定用户在某一个表空间的配额

alter user test quota unlimited on data01;

在创建表的时候指定表空间

create table t1(id number(3),namevarchar(20)) tablespace data01;

 

改变表空间的状态

表空间的四种状态：

offline脱机

online在线

read only只读

read write读写

 

修改表空间的状态

格式：altertablespace 名字 状态

alter tablespace data01 online;

3.3表空间及表的查询

查找表空间中的表

Select * from all_tables wheretablespace_name='DATA01';

查看一个表在哪个表空间

Select tablespace_name from user_tableswhere table_name='EMP';

Select tablespace_name from all_tableswhere table_name='EMP'

表空间的扩展

给表空间添加新的数据文件

Alter tablespace data01 add datafile'd:\test\data0102.dat' size 20m;

把已有表空间的数据文件调大

alter database  datafile 'd:\test\data0102.dat' resize 50m;

让表空间中数据文件设置自动增长

ALTER DATABASE

   DATAFILE 'D:\TEST\DATA1.DBF' AUTOEXTEND

   ON NEXT  512K MAXSIZE  1024M

 

实战练习，把数据库文件移到其它地方

A、找到数据文件是属于哪一个表空间

 Select tablespace_name from dba_data_fileswhere file_name= 'F:\TEST\DATA01.DBF';

B、把表空间脱机

 alter tablespace data01 offline;

 

C、移动文件

 hostmove F:\TEST\DATA01.DBF d:\test\DAA01.DBF

D、修改数据字典,把文件位置改变

Alter tablespace data01 rename datafile'F:\TEST\DATA01.DBF' to 'D:\TEST\DAA01.DBF';

E、上线-联机

alter tablespace data01 online;

4.   创建和管理表

4.1数据库对象概述

表: 基本的数据存储集合，由行和列组成。

视图: 从表中抽出的逻辑上相关的数据集合。

索引: 提高查询的效率

序列: 提供有规律的数值。

同义词: 给对象起别名

表是Orcale最常用的一种数据库对象。

对数据库对象进行定义，维护的语句都称为DDL语句。

4.2数据类型

VARCHAR2(size)  可变长字符数据（存储不固定,空间会少一点。存储的性能会差一点.）

CHAR(size)        定长字符数据（存储空间是固定,空间占得要多一点。存取的性能会好一点。）

NUMBER(p,s)    可变长数值数据（NUMBER(5,2)长度是5位，小数2位）

DATE     日期型数据

LONG    可变长字符数据，最大可达到2G

CLOB     字符数据，最大可达到4G

RAW and LONG RAW        原始的二进制数据

BLOB     二进制数据，最大可达到4G

BFILE    存储外部文件的二进制数据，最大可达到4G

ROWID  行地址

4.3表的创建和维护

创建表

create table t1(id number(2),namevarchar2(20));

create table t2 as select empno,ename fromscott.emp;（列必须是合法的列名）

添加字段

alter table 表名 add (列名 类型);

alter table t2 add age number(3);//增加age列

删除字段

alter table 表名 drop column 列名

alter table t2 drop column nianling;//删除nianling列

修改字段类型

alter table 表名 modify 列名  数据类型

alter table t2 modify age varchar2(3);//修改age列的类型为varchar2

修改字段名

alter table 表名 renmae column 列名1 to 列名2

alter table t2 rename column age tonianling;//更改age为nianling

删除表

drop table 表名 [purge]

drop table t2 [purge];//加上purge就直接删除表，不放到回收站，清空回收站（purge recyclebin）

清空表

TRUNCATE TABLE 表名.（把内容清空,表结果还在)

truncate table t1;

修改数据对象(包含表)的名称

rename 对象名 to 新名

rename t1 to t2;//t1改名为t2

5.约束

5.1概述

约束是表一级的限制

如果存在依赖关系，约束可以防止错误的删除数据

约束的类型：

NOT NULL非空约束，列的值不能为空

UNIQUE唯一约束，列的值不能重复，可以出现1个或多个null值。

PRIMARY KEY主键约束，列的值非空且唯一（非空与唯一约束组合）

FOREIGN KEY外键约束，列的值引用了另一张表的主键的值

CHECK检查约束，列的值必须符合某种条件

约束可以定为表级约束或列级约束。

5.2约束的使用

NOT NULL非空约束：

create table t1 (id number(5) not null,name varchar(20),agenumber(3));//列级约束

create table t3 (id number(5),namevarchar(20),age number(3) constraintNOT_NULL_ID not null);

表创建好后要修改列为非空约束要使用modify。

alter table t3 modify name not null;

UNIQUE唯一约束：

create table t4(id number(3) unique,namevarchar2(20));

create table t5(id number(3) unique,namevarchar2(20), constraint UNIQUE_NAMEunique(name));

PRIMARY KEY主键约束：

主键字段可以是单字段或者是多字段的组合Oracle为主键创建对应的唯一性索引

create table t7(id number(6) primarykey,name varchar2(10));

复合组建

create table t8(firstnamevarchar2(10),lastname varchar2(10), CONSTRAINT PK_name primarykey(firstname,lastname));

FOREIGN KEY外键约束：

某一个字段的值关联的另外一个表(同一个表)中的主键字段。确保整个数据完整性。

create table t9(id number(4) primarykey,deptno number,CONSTRAINTfk_deptno00 FOREIGN KEY(deptno) REFERENCES dept(deptno));

CHECK检查约束：

定义一个表达式，要求我们插入数据必须满足表达式所设置条件。否则就是非法数据。

create table t10 (id number(4),agenumber(3),CONSTRAINT chk_age18 CHECK(age between 18 and 50))

注意：不要在 CHECK 约束中使用日期或系统变量做为条件。

5.3约束的维护

增加约束

ALTER TABLE table ADD [CONSTRAINTconstraint]    type (column);

删除约束

ALTER TABLE employees DROP CONSTRAINT  emp_manager_fk;

alter table t10 drop CONSTRAINT CHK_AGE18;

6.视图(View)

6.1概念

从物理数据动产生逻辑上的数据显示。一个表可以创建很多个的视图。一个视图也可以从是从多张表创建。

视图其它可以看成是一个保存到数据诨中sql语句。语句对应的数据是动态的。

视图是一种虚表.

视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

向视图提供数据内容的语句为SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.

视图向用户提供基表数据的另一种表现形式

6.2视图的作用

简化复杂查询

限制数据访问

提供数据的相互独立

同样的数据，可以有不同的显示方式

注意：使用视图不能提高性能

6.3视图的创建和管理

创建视图

create or replace View  视图的名称 [(列名,列名)] as  select(子查询 )

create or replace view empview as select *from scott.emp;

 

视图应用

用视图简化复杂查询

查询出大于部门平均工资的员工的姓名，部门名称，工资，及部门平均工资

select e.ename,e.sal,d.dname,ad.avgsal fromemp e,dept d,(select deptno,avg(sal) avgsal from emp group by deptno) ad wheree.deptno=ad.deptno and e.deptno=d.deptno and e.sal>ad.avgsal

 

可以在视图上执行DMl语句(产生的影响将反映到基表上)

insert into emp20(empno,ename,job,deptno)values(999,'ada','ddd',30);

 

创建视图使用withcheck option，在直接对视图进行数据操作时，会检测查询条件，要求插入的数据必须满足设置查询条件。

create or replace view emp20 as select*  from emp where deptno=30 with check option;

创建视图，限制在视图执行DML语句

create or replace view emp10 as select *from emp where deptno=10 with read only;

视图中涉及到从多个表查询或者包括组函数时，就不能执行DML语句

SQL> create or replace viewavgsal(deptno,avgsal) as select deptno,avg(sal) from emp group by deptno;

删除视图

drop view 视图名称

drop view empview;

7.序列(sequence)

序列是用来在数据库产生唯一的连续的数值的数据库对象。(oracle中没有auto_inrement)

注意：回滚事务，出现异常时会导致序列断裂(不连续) 。

创建序列

create sequence 序列名称 [参数]

create sequence ms;

使用序列:

序列名称.CURRVAL

序列名称.NEXTVAL

 

序列的应用：

create sequence ms;

select ms.nextval from dual;//获取序列的当前值

select m.currval from dual;//获取序列的下一个值

 

insert into test(id,name,sn) values(ms.nextval,'ddd','008');

insert into t2(id,name) values(ms.nextval,'abd');

 

修改序列

ALTER SEQUENCE ms

删除序列

drop sequence ms

8.索引(Index)

为了提升查询速度而提供的一种数据库对象。对一些经常要查询字段进行排序，处理等，存在另外一索引表。(占用额外的存储空间)。用户只需创建索引，系统会自动维护。

 

当往表中插入、更新或者删除数据的时候，均需要对索引进行额外操作(数据库自动进行）。消耗额外时间。

 

索引的使用

primary key及unique约束的列会自动创建索引。

创建索引的格式：

create index  索引名 on  表名(列名[,列名2])

create index i1 on t1(id);

删除索引

drop index 索引名

drop index i1;

9.同义词

给一个数据库对象取一个别名(方便使用)。

创建同义词

create SYNONYM  名称  for 对象

create synonym emp for scott.emp;

删除同义词

drop synonym emp;