Oracle数据库操作语句格式总结

Oracle数据库操作语句格式总结

Oracle数据库是甲骨文公司开发的高安全性，高效率的付费数据库。

Oracle数据库端口号：1521

创建数据库

create database 库名
[on primary]
(name='',filename='',
size= ,maxsize= ,filegrowth= ),
......

连接数据库

--1.连接命令
--若是以特权用户连接则必须带[as sysdba/sysoper]
connect username/password@orcl

--2.断开连接
discribe

表空间

Oracle数据库在逻辑上可以划分为一系列的逻辑空间，每一个逻辑空间就可以称为一个表空间。

--1.创建表空间
create tablespace tablespace_name datafile
'd:....dbf' size []m;
extent management local --指定表空间的管理方式local或dictionary
uniform size []m;--设定区的分配方式autoallocate或uniform
segment space management manual--设定段的管理方式manual或auto

--2.修改表空间
alter tablespace tablespace_name 
add datafile/tempfile
'd:...' size[]m;
--resize []m;  改变数据文件的大小
--autoextend on next []m maxsize []m;  数据文件自动扩展

--3. 设置默认表空间
alter database default tablespace tablespace_name;
alter database default temporary tablespace tablespace_name;

--4.表空间重命名
alter tablespace tablespace_name
rename to tablespace_name;

--5.表空间的备份
alter tablespace tablespace_name 
begin backup  --设置为备份模式
end backup  --结束备份模式

--6.删除表空间
drop tablespace tablespace_name
including contents  --表空间非空应带子句  
and datafiles  --删除操作系统下的数据文件
cascade constraints;  --删除参照完整性约束

安全管理

用户

Oracle数据库中默认有四个用户。创建用户需要超级管理员权限。

SYS：数据库中具有最高权限的数据库管理员，可以启动、修改和关闭数据库，拥有数据字典。

--1.创建用户
create user user_name identified
[by password | externally | globally as 'external_name']
[default tablespace tablespace_name]
[temporary tablespace temp_tablespace_name]
[quota n k|m|unlimited on tablespace_name]
[profile profile_name]
[password expire]
[account lock|unlock];

--2.修改用户
alter user user_name
...

--3.删除用户
drop user user_name [cascade];

user_name：用于设置新建用户名，在数据库中用户名必须是唯一的。

identified：用于指明用户身份认证方式。

by password：用于设置用户的数据库身份认证，其中password为用户口令。

externally：用于设置用户的外部身份认证。

globally as 'external_name'：用于设置用户的全局身份认证，其中external_name为Oracle的安全管理服务器相关信息。

default tablespace：用于设置用户的默认表空间。

temporary tablespace：用于设置用户的临时表空间。

quota：用于指定用户在特定表空间上的配额，即用户在该表空间中可以分配的最大空间。

profile：用于为用户指定概要文件，默认值为default，采用系统默认的概要文件。

password expire：用于设置用户口令的初始状态为过期，用户在首次登录数据库时必须修改口令。

account lock|unlock：用于设置用户初始状态为锁定|不锁定，默认为不锁定。

权限

权限就是执行特定类型SQL命令或访问其他用户的对象的权利。用户在数据库中可以执行什么样的操作，以及可以对哪些对象进行操作，完全取决于该用户所拥有的权限。

grant语句是将指定操作对象的指定操作权限授予指定的用户。如果指定了with grant option子句，则获得某种权限的用户还可以把这种权限在授予别的用户，但在收回权限时，将级联收回。

用户被授予的权限可由DBA或其他授权者用revoke语句收回。

--1.系统权限
grant 权限,...
--create session 数据库连接权限
--create database link 创建数据库链接
--create any index  创建索引
--create role 创建角色
--create sequence  创建修改删除序列
--create synonym  创建修改删除同义词
--create table  创建修改删除表
--create tablespace  创建表空间
--create trigger 创建修改删除触发器
--create view  创建修改删除视图
--create user  创建用户
to 用户,...;
[with admin option]  --允许用户再授权

--2.对象权限
grant 权限
--select on emp
--select any table
--insert any table
--update any table
--delete any table
to 用户

--3.权限回收
revoke <权限>...
from <用户>

角色

角色就是一系列相关权限的集合。

--1.创建角色
create role role_name
[not identified]
[identified by password]

--2.修改角色
alter role role_name
...

--3.删除角色
drop role role_name

role_name：用于指定自定义角色名称，该名称不能与任何用户名或其他角色相同

not identified：用于指定该角色由数据库授权，使该角色生效时不需要口令

identified by password：用于设置角色生效时的认证口令。

数据管理

数据查询

select [all|DISTINCT]
<目标列表达式>[别名],<目标列表达式>[别名]...
from <表名或视图名>[别名],<表名或视图名>[别名]...
where <条件表达式>
group by <列名1>
having <条件表达式>
order by <列名2>

插入数据

insert语句的功能是将指定的元组插入到指定的关系中，其中属性列的顺序要与常量值一一对应，常量1的值赋给属性列1，常量2的值赋给属性列2，依次类推。

insert
into <表名> (<属性列>...)
values (常量...)

修改数据

update语句的功能是修改指定关系中满足where子句条件的元组，其中set子句给出指定列的修改方式及修改后的值。若省略where子句，则说明要修改关系中的所有元组，在where子句中可以嵌套子查询。

update <表名>
set<列名>=<表达式>...
where <条件>

删除数据

delete语句的功能是删除指定关系中满足where子句条件的所有元组。当where语句省略时，表示要删除关系中的全部元组，但表的定义仍存放在数据字典中。delete语句删除的是关系中的数据，而不是表的定义。

delete
from <表名>
where <条件>

数据文件

--1.创建数据文件
alter tablespace tablespace_name
add datafile/tempfile  --数据文件/临时数据文件
'd:....dbf' size 10m;

--2.修改数据文件大小
alter database 
datafile 'd:...'
resize ...
--设置数据文件为自动增长方式
autoextend on next []m maxsize []m | unimited

--3.改变数据文件的名称或位置
--改变同一表空间中的数据文件的名称或位置
alter tablespace tablespace_name rename datafile
'd:...','d:...'to
'd:...'.'d:...';
--改变多个表空间中的数据文件的名称或位置
alter database_name rename file
'd:...','d:...'to
'd:...','d:...';

--4.删除数据文件
alter tablespace tablespace_name 
drop datafile 'd:...' 
alter tablespace temp_name 
drop 'd:...'

模式对象

模式是指一系列逻辑数据结构或对象的集合。

模式与用户相对应，一个模式只能被一个数据库用户所拥有，并且模式的名称与这个用户的名称相同。

数据表

create table 表名
(列名 数据类型 完整性约束,
 ......
)

--1.添加列
alter table table_name
add(列名 数据结构 [完整性约束],...);

--2.修改列名
alter table table_name
rename 列名 oldname to newname

--3.删除列
alter table table_name
drop column 列名 [cascade constraints]

--4.表重命名
alter table old_name rename to newname
rename old_name to new_name

--5.删除表
drop table table_name
[cascade constraints][purge]

表约束

在表中定义的用于维护数据库完整性的一些规则。通过对表中列定义约束，可以防止在执行DML操作时，将不符合要求的数据插入到表中。

在Oracle数据库中对列的约束包括主键约束、惟一性约束、检查约束、外键约束和空/非空约束等5种，定义方法有表级约束和列级约束2种。

PRIMARY KEY  --主键约束
UNIQUE  --唯一性约束
CHECK  --检查约束
FOREIGN KEY  --外键约束
NULL/NOT NULL  --空值约束

--1.添加约束
alter table table_name
add constraint p_pk primary key(id);
add constraint p_uk unique(sname);
add constraint p_ck check(sage between 20 and 30);
add constraint p_fk foreign key(sno) references student(sno) on delete cascade;
modify resume not null;  
--添加空值约束必须使用modify代替add子句。

--2.删除约束
alter table table_name
drop unique(sname); --删除指定内容的约束
drop constraint p_ck;  --删除指定名称的约束
drop constraint p_uk keep index  --保留唯一性索引
drop constraint p_pk cascade  --删除引用该约束的其他约束

索引

--1.创建索引
create [unique][bitmap] index index_name
on table_name (列名[asd|desc],...)
[expression]
[reverse]
[parameter_list]
--unique表示建立惟一性索引；
--bitmap表示建立位图索引；
--asc/desc指定索引值的排列顺序，asc升序排序，desc降序排序，缺省值为asc；
--reverse表示建立反键索引；
--parameter_list用于指定索引的存放位置、存储空间分配和数据块参数设置。

--2.合并索引
alter index table_name coalesce

--3.重建索引
alter index table_name rebuild

--4.索引重命名
alter index old_name to new_name

--5.删除索引
drop index table_name

分区表

范围分区：按照分区列值的范围来对表进行分区的。

create table table_name(
列名 数据类型 完整性约束,
    ...
)
partition by range(列名)
(partition 分区名 values less than()
 tablespace tablespace_name,
 partition 分区名 values less than()
 tablespace tablespace_name,
 ...
);

列表分区：如果分区列的值并不能划分范围（非数值类型或日期类型），同时分区列的取值范围只是一个包含少数值的集合，则可以对表进行列表分区（LIST），如按地区、性别等分区。

create table table_name(
列名 数据类型 完整性约束,
    ...
)
partition by list(列名)
(partition 分区名 values('[]')
 tablespace tablespace_name,
 partition 分区名 values('[]')
 tablespace tablespace_name,
 ...
);

散列分区：在进行范围分区或列表分区时，由于无法对各个分区中可能具有的记录数量进行预测，可能导致数据在各个分区中分布不均衡，某个分区中数据很多，而某个分区中数据很少。此时可以采用散列分区（HASH）方法，在指定数量的分区中均等地分配数据。

create table table_name(
列名 数据类型 完整性约束,
    ...
)
--1.
partition by hash(列名)
(partition 分区名 tablespace tablespace_name,
 partition 分区名 tablespace tablespace_name,
 ...
 );
--2.
partition by hash(列名)
partition 2 store in(tablespace_name1,tablespace_name2);

复合分区包括：范围-列表复合分区，范围-散列复合分区。

范围-列表复合分区：先对表进行范围分区，然后再对每个分区进行列表分区，即在一个范围分区中创建多个列表子分区。

create table table_name(
列名 数据类型 完整性约束,
    ...
)
partition by range(列名)
subpartition by list(列名)
(
partition 分区名 values less than([])
(subpartition 分区名 values('[]') 
 tablespace tablespace_name ,
 ...),
partition 分区名 values less than([])
(subpartition 分区名 values('[]') 
 tablespace tablespace_name ,
 ...),
 ...
 );

范围-散列复合分区：先对表进行范围分区，然后再对每个分区进行散列分区，即在一个范围分区中创建多个散列子分区。

create table table_name(
列名 数据类型 完整性约束,
    ...
)
partition by range(列名)
subpartition by hash(列名)
(
partition 分区名 values less than([])
(subpartition 分区名 tablespace tablespace_name,
 subpartition 分区名 tablespace tablespace_name,
 ...),
partition 分区名 values less than([])
(subpartition 分区名 tablespace tablespace_name,
 subpartition 分区名 tablespace tablespace_name,
 ...),
 ...
 );

分区索引：分区表中的各个分区单独建立索引分区，各个索引分区之间是相互独立的。

--在分区表的sname列上创建本地分区索引
on index_name(sname) local;

--为分区表的sage列建立基于范围的全局分区索引
create idnex table_name
on table_name(sage)
global partition by range(sage)
(partition 分区名 values less than([])
 tablespace tablespace_name,
 partition 分区名 values less than([])
 tablespace tablespace_name,
 ...
)

--为分区表创建全局非分区索引。
create index table_name
on table_name(sname)

序列

序列用于产生惟一序号的数据库对象，用于为多个数据库用户依次生成不重复的连续整数。

--1.语法
create sequence sequence_name
[increment by n]
[start with n]
[maxvalue n | nomaxvalue]
[minvalue n | nominvalue]
[cycle | nocycle]
[cache n | nocache];

--2.修改序列
alter sequence sequence_name
...

--3.删除序列
drop sequence sequence_name

INCREMENT BY：设置相邻两个元素之间的差值，即步长，默认值为1；

START WITH：设置序列初始值，默认值为1；

MAXVALUE：设置序列最大值

NO MAXVALUE：设置默认情况下，递增序列的最大值为1027，递减序列的最大值为-1；

MINVALUE：设置序列最小值；

NOMINVALUE：设置默认情况下，递增序列的最小值为1， 递减序列的最小值为-1026；

CYCLE|NOCYCLE：指定当序列达到其最大值或最小值后，是否循环生成值，NOCYCLE 是默认选项。

CACHE|NOCACHE：设置是否在缓存中预先分配一定数量的数据值，以提高获取序列值的速度，默认为缓存20个值。

同义词

同义词是数据库中表、索引、视图或其他模式对象的一个别名。

--1.创建同义词
create [public] synonym synonym_name
for table_name

--2.删除同义词
drop [public] synonym synonym_name

数据库链接

数据库链接是在分布式数据库应用环境中的一个数据库与另一个数据库之间的通信途径，将远程数据库映射到本地。

create [public] database link link_name
[connect to [current user] | [user inentified by password] ]
using connect_string--指定服务名

CONNECT TO子句用于设置与远程数据库建立连接的方式。

CURRENT USER子句指明用当前数据库用户连接远程数据库。

user IDENTIFIED BY password用于设置连接远程数据库的用户名和口令。

视图

视图是从一个或多个表或视图中提取出来的数据的一种表现形式。在数据库中只有视图的定义，而没有实际对应“表”的存在，因此视图是一个“虚”表。当对视图进行操作时，系统根据视图定义临时生成数据。

--1.创建视图create view view_nameasselect *from ...--2.修改视图create or replace view view_nameasselect *from ...--3.重命名试图sp_rename oldname,newname;4.--删除视图drop view view_name;