数据控制语言与数据定义语言.doc

数据控制语言与数据定义语言常用语句一、表空间、用户、权限1二、表2三、索引6四、视图7一、表空间、用户、权限数据控制语言（data control language，DCL）数据控制语言（data definition language DDL）1、 创建表空间Create tablespace 表空间名 存放路径名称.dbf size *M（表空间大小） autoextend on next *M maxsize unliminted； 2、 创建用户Create user 用户名 identified by 密码（密码不能为数字）Default tablespace 表空间 -指定表空间（可选）Temporary tablespace 临时表空间 -指定临时表空间（可选一般式temp）Account unlock/lock 指定用户是否被锁定3、 授予用户权限授予某个用户什么权限使用 grant 权限，权限，权限 。to user with admin option可选（指将授权的权限，该用户还可以授予给其他用户）撤销某个用户什么权限使用 revoke 权限，权限，权限。from user；授予用户系统权限常用系统权限功能说明Create session 连接到数据库上Create sequence创建序列。序列是一系列数字，通常用来自动填充主键列Create synonym创建同名对象。同名对象用于饮用其他模式中的标Create table 创建表Create any table在任何模式中创建表Drop table删除表Drop any table删除任何模式中的表Create procedure创建存储过程Execute any procedure执行任何模式中的存储过程Create user创建用户Create view创建视图Select any dictionary查询任何数据字典（这个时候用户才能从客户端登陆，或者直接授予用户DBA的权限也可以）Grant create session,create sequence,create synonym,create table,create any table,drop table,drop any table,create procedure,create any procedure,create view,create user to 用户名user_name;Grant 权限 to public；将权限授予所有用户；Grant DBA to user_nameGrant resource to user_name 将resource角色授权给用户后，创建的用户才能够在相应的表空间上创建表4、 查看授予用户的系统权限通过数据字典中的user_sys_privs视图，可以查看授予用户的系统权限Select * from user_sys_privs 查看当前连接用户的系统权限5、 收回用户系统权限Revoke system_privsleges(系统权限) from user_name；授予者可以收回被授予者的权限Revoke 系统权限 from public (将赋予public的某一权限收回)Revoke create session from public6、 对象权限控制对象权限控制包括对象权限授予和收回。如果需要了解用户所具有哪些对象权限，可以通过数据字典进行查看。对象权限功能说明Select 允许执行查询操作Insert允许执行插入操作Update允许执行修改操作Delete允许执行删除操作Execute允许执行存储过程7、 授予用户对象权限Grant object_privilegs_list（对象权限列表） on table_name(表名称) to user_name(用户名) with grant option 指定别授权用户可以将其活动的权限再授予其他用户。注意：对象权限不仅可以针对表，还可以针对表中的某一列；Grant update(列名，列名。) on 表名 to user_name用户8、 查看授予用户的对象权限通过数据字典中的user_tab_privs_recd视图，可以查看授予用户的对象权限这些信息包括：owner（拥有者）、table_name(拥有对象权限的表)、grantor（授权者）、privileg（该对象被授予的权限）、grantable(是否将权限再授予其他用户)Select * from user_tab_privs_recd；9、 收回用户对象权限Revoke object_privileges(对象权限) on 表名 from user_name（用户名）Revoke insert on 表 from user_name（用户名）二、表表示Oracle数据库中最基本的对象1、 直接建立表Create table table_name(表名) (column_name(列名) datatype（列类型）constraint constraint_name(定义数据库完整性约束) default default_expression（定义列的默认值）) tablespace tablespace_name（表所在表空间）数据库完整性约束条件约束约束类型约束含义Primary keyP指定表的主键。主键由一列或多列构成，唯一表示表中的行Not nullC规定某一列不允许取空值CheckC规定一列或一组列的值必须满足指定的约束条件UniqueU指定一列或一组列不能取重复值Foreign key R指定表的外键。外键引用另外一个表中的列，构成参照约束Create table departments1( Department_id number(3) Constraint d1_pk primary key, Department_name varchar2(8) not null,Director_id number(5);建立表的同时将departments1表中的department_id设置为外关键字（foreign key）。参照表中的列必须是该表中主关键字组成部分或唯一关键字，否则不会生成外关键字。Create table teachers1( Teacher_id number(5) Constraint t1_pk primary key, Name varchar2(6) not null, Job_title varchar2(10), Hire_date date, Bonus number(4) default 800, Wage number(5), Department_id number（3） Constraint t1_fk_d1 References departments1(department_id)-使用references来指定表字段为其他表的外键);建立表，同时使用check子句约束sex列值，为register_date设置默认值，为phone_number设置unique约束。Create table students1（ Student_id number(5) Constraint s1_pk primary key, Name varchar2(10) not null, Sex varchars(6) Constraint sex_chk1 check(sex in(男,女), Register_date date default sysdate, Phone_number varchar2(12) constraint pnum_up unique);2、 间接建立表间接建立表是通过已存在的标复制的方法建立表Create table table_name(表名) as subquery(给出利用已存在得标建立表的查询即select 语句);Create table teachers3 as( Select * from teachers where department_id=101Union Select * from teachers where department_id=102);3、 获取表的相关信息获得表的相关信息可以使用SQL*Plus中的命令describe,也可以通过查询数据字典中的视图user_tables，获得表的名字（table_name），该表所占表空间名字(tablespace_name)以及该表是否是临时表等信息 Describe table_name; Select * from user_tables where table_name in(表名,表名);-表名要大写4、获得表中列的信息获得表中列的信息，一种是使用SQL*PLUS中的命令describe，另一种是通过查询数据字典中的视图user_tab_columns Describe table_name, Select * from user_tab_columns;5、修改表名称修改表名有两种方法：Alter table 原来名 rename to 现在名；（修改表）Rename table_odename to table_newname;（表重命名）6、删除表删除表可以使用tuncate table table_name语句、delete from table_name 和drop table table_name语句。Truncate table table_name语句只删除表中的所有数据Delete from table_name 语句删除表中的所有数据Drop table table_name语句将表的全部，包括表的结构（定义）以及其中的全部数据删除。Truncate table 与 delete from table_name 的区别：truncate table 删除数据速度比delete table速度快，delete语句每删除一行，并在事务日志中为所删除的每一行记录一项。Truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。对于由foreign key（外键）约束引用的表，不能使用truncate table和drop table，而应该使用不带where子句的delete语句。由于truncate table不记录在日志中，所以它不能激活触发器。Truncate table不能用于参与了索引视图的表。对于truncate table删除数据的标上增加数据时，要使用update statistics来维护索引信息。如果有rollback语句，delete操作将被撤销，单truncate不会撤销。7、添加表的列添加表的列的语句格式为：Alter table table_name(表名称) add column_name（列名称） datetype（数据类型）;Alter table students2 add sex varchar2(3);Alter table students2 add register_date date default sysdate;8、删除表的列删除表的列的语句格式为：Alter table table_name(表名称) drop column column_name(列名称);Alter table students2 drop column sex;9、修改表的列修改表的列包括修改列的长度、数据类型、修改数字列的数字精度及修改列的默认值等Alter table table_name modify column_name datetype.;Alter table teachers1 modify wage number(7,2);Alter table teachers1 modify name varchar2(10);Alter table teachers1 modify bonus default 1000;10、添加约束添加约束的语句格式为：Alter table table_name add constraint constraint_name constraint_expression;Alter table students1 add constraint s1_pk primary key(student_id);Alter table teachers1 add constraint t1_fk_d1 foreign key(department_id) ;Alter table students1 modify name name not null;Alter table students1 add constraint sex_chk1 check(sex in(男,女);Alter table students1 add constraint pnum_uq unique(phone_number);-不能取重复值11、删除约束删除约束的语句格式：Alter table table_name drop constraint constraint_name;Alter table students1 drop constraint s1_pk;12、允许/禁止约束约束在建立时默认处于允许状态。可以通过使用关键字enable或disable指定或改变约束的允许或禁止状态。约束处于允许状态，他对数据库起作用，建立了约束，但约束处于禁止状态，他对数据库不起作用。（1） 禁止约束禁止约束可以在建立约束时使用关键字disable指定，也可以在已建约束之后，使用disable constraint子句重新设置约束为禁止状态Alter table students1 add constraint pnum_uq uniue(phone_number) diable;（针对新建约束）Alter table students1 disable constraint sex_chk1;(针对已经建立的约束)（2）允许表约束允许约束可以在建立约束时指定，也可以在已建约束之后，使用enable constraint子句重新设置约束为允许状态。在设置约束为允许状态时，表中已有的数据必须全部满足约束条件，否则，允许约束的设置将失败，并返回相应的错误信息。Alter table students1 enable constraint pnum_uq;13、获得有关约束的信息通过查询数据字典中的视图user_constraints，可以获取有关约束的信息。Select owner,constraint_name,constraint_type,status from user_constraints where table_name=表名称（大写）三、索引当数据库中数据记录很多时，建立索引可以提高检索速度。索引对于数据库的作用与目录对于书的作用是一样的。1、建立索引建立索引的语句格式为：Create unique index index_nameOn table_name (column_name ,column_name)tablespace table_space;Index_name指定要建立索引的名称，column_name指定建立索引所基于的列（给出名字），可以基于多列建立索引，这种索引被称为复合索引；可选项unique指定索引列中的值必须是唯一的。Create index name_idx on students1(name);Create unique index phone_number_idx on students1(phone_number);2、获得索引的基本信息使用数据字典中的视图user_indexes，可以获得索引的索引名（index_name）、建立索引所基于的表名（table_name）、唯一性(uniqueness)、索引是否有效(status)等基本信息。Select index_name,table_name,uniqueness,status From user_indexes Where table_name=STUDENTS1(大写);3、 获得索引中列的信息使用数据字典中的视图user_ind_columns，可以获得索引中列的信息。其中包括索引名（index_name）、建立索引所基于的表名（table_name）、建立索引所基于的列名（column_name）等信息Column table_name format a15Column column_name format a15Select index_name,table_name,column_name From user_ind_columns Where table_name in(表名,表名);大写4、 修改索引的名字修改索引的名字语句的格式为：Alter index index_oldname rename to index_newname;Alter index name_idx rename to students1_name_idx;5、 删除索引删除索引语句的格式为：Drop index index_name;Drop index students1_name_idx;四、视图视图是通过对一个或多个表定义查询得到的，视图定义所依据的标被称为基表。视图是由表导出的“表”，数据库中只存在视图的定义，因此视图是一个“虚”表。使用视图可以检索数据库中的信息，使用某些视图还可以对基表进行DML操作。1、 建立视图建立视图语句的格式如下：Create or replace view view_name As subquery with red only;Create