SQL总结.docx

1、SQL总结-用户管理-dba_users(用户信息)、dba_tab_privs(权限信息)create user identified by ;-创建用户drop user ;-删除用户drop user cascade;-级联删除用户grant create table to ;-授予用户建表的权限revoke create table from ;-撤销用户建表的权限grant select on to ;-授予用户查看表的权限revoke select on from ;-撤销用户查看表的权限grant select () on to ;-授权用户只能查表的的两列select * fr

2、om dba_tab_privs where grantee=;-查询指定用户有哪些权限select usernameuser_id from dba_users where username=;-查询指定用户的idselect * from sysusers where id=();-通过用户id，进行查看该用户的详细密码策略select granteegranted_role from dba_role_privs where grantee=sysdba;-查看哪些用户具有sysdba权限select usernameaccount_status from dba_users;-查看所有

3、用户的当前状态(是否锁定)alter user test account lock/unlock;-锁定/解锁用户密码create user identified by default tablespace ;-新建bkjyh用户，设置密码，指定默认表空间为bkjalter user default tablespace ;-修改的默认表空间为-角色管理-dba_roles(角色信息)、dba_role_privs(用户对应的角色)create role ;-创建角色drop role ;-删除角色call sp_set_role(1);-启用角色call sp_set_role(0);-禁用

4、角色grant to ;-把角色分配给用户revoke from ;-撤销用户的角色select * from dba_roles where role=;-查询指定角色信息select granteegranted_role from dba_role_privs where grantee=;-查看指定用户具有哪些角色select granteeprivilege from dba_sys_privs where grantee=;-查看指定角色具有哪些权限grant create table to ;-授予角色建表的权限grant uppdate on to ;-授予角色查看表的权限-模

5、式管理create schema authorization ;-创建属于哪个用户的模式set schema ;-切换到drop schema ;-删除模式drop schema cascade;-级联删除模式-表管理-dba_cons_columns(所有用户下的表信息)create table ( ) storage (on );-创建表drop table ;-删除表drop table cascade;-级联删除表alter table rename to ;-更改表名select * from ;-查找表insert into () values ();-表里插入数据create t

6、able (id number name varchar2(32);-创建一张表insert into () values (.nextval);-插入序列delete from where = ;-删除表里指定数据update person.person set phone = 118114 where name = 李丽;-更改表里数据delete from ;-清空表数据alter table add column( varchar(10);-添加列alter table drop cascade;-删除列alter table alter rename to ;-修改列sp_creat

7、e_system_packages(1);-构建环境，安装包sp_create_system_packages(0);-构建环境，关闭包-建一个范围分区表，指定par2分区存储在ts_par2表空间上：create table partition_table(c1 intc2 int)partition by range(c1)(partition par1 values less than(5)partition par2 values less than(100) storage (on ts_par2);-建一个列存表，指定第二列占用的簇为15个：create vertical tabl

8、e dm(c1 int storage(section(10)c2 varchar) storage(section(15);-创建一个堆表，指定并发分支2个，非并发分支4个：create table list_table(c1 int) storage(branch (24);-表空间-v$huge_tablespace(huge表空间)create tablespace datafile size ;-创建表空间alter tablespace rename to ;-更改表空间名字drop tablespace ;- 删除表空间create huge tablespace path ;-

9、创建huge表空间select * from v$huge_tablespace;-查看huge表空间drop huge tablespace ;-删除huge表空间alter tablespace add datafile size ;-为表空间添加数据文件alter tablespace resize datafile to ;-为表空间扩展数据文件大小alter tablespace datafile autoextend on;-指定表空间的数据文件可自动扩展alter tablespace offline;-修改表空间状态为脱机alter tablespace online;-修改表

10、空间状态为联机select tablespace_namestatus from dba_tablespaces;-查看表空间状态select tablespace_namefile_name from dba_data_files;-查看表空间与数据文件的对应关系select tablespace_namebytes/1024/1024 as free_spacefile_name from dba_free_space;-查看表空间的剩余空间-日志alter databaxxxxse add logfile D:dmdbmsdataDAMENG1EE.log size 64;-为当前数据库

11、增加日志alter databaxxxxse resize logfile D:dmdbmsdataDAMENG1EE.log to 128;-扩充日志大小select file_idpathrlog_size/1024/1024 as rsize from v$rlogfile;-查看日志路径及大小-视图管理create view as select ab from emp where a=M;-创建视图查询emp表中满足a=M条件的行create or replace view as select abc from emp;-修改视图查询范围select * from ;-查询视图drop

12、 * from ;-删除视图-约束管理-user_constraints(约束信息)alter table drop constraint ;-删除约束alter table enable constraint ;-启用约束alter table disable constraint ;-禁用约束-非空约束(只能作为列级约束，不能作为表级约束)alter table modify not null;-为该表增加非空约束-唯一约束(可以是列级也可是表级)alter table add constraint unique ();-为该列添加唯一约束-主键约束(可以是列级也可是表级)alter ta

13、ble add constraint primary key ();-为该列添加主键约束-外键约束alter table add constraint foreign key () references dept ();-为该列添加主键约束-检查约束alter table add constraint check (lie800);-为该列添加检查约束(lie800)-索引管理-user_indexes(索引信息)-sysobxxxxjects(索引信息)select idname from sysobxxxxjects where name=;-查找索引idcall sp_rebuild_index();-重建索引drop index 索引