频道栏目
首页 > 资讯 > Oracle > 正文

oracle基础---用户和表空间、管理表、约束

16-08-17        来源:[db:作者]  
收藏   我要投稿

sys: Oracle的一个超级用户,主要用来维护系统信息和管理实例,只能以sysdba或sysoper角色登录

system: Oracle默认的系统管理员,拥有DBA权限,通常用来管理Oracle数据库的用户、权限和存储等,只能以Normal方式登录

sysdba是一种角色,不属于用户。

scott: 是普通的用户,安装Oracle时自带的,默认密码是tiger,不过其默认是锁定状态。所以一般要先将这个用户解锁。

oracle中sysdba与sysoper的区别

normal 普通用户

sysoper 数据库操作员,主要包括 打开数据库服务器,关闭数据库服务器,备份数据库,恢复数据库,日志归档,会话限制

sysdba 数据库管理员,打开数据库服务器 关闭数据库服务器,备份数据库,恢复数据库,日志归档, 会话限制,管理功能,创建数据库

sysdba角色拥有数据库的所有权限,sysoper就是为了防止sysdba的权限过大设置的一个角色,主要是启停数据库、备份还原数据库,不能drop和create数据库,这是最主要的区别

一、用户

1-1.使用系统用户登陆

使用system用户登陆

[username/password] [@server] [as sysdba|sysoper]

如:system/root @orcl as sysdba

orcl就是自己设置的服务名

SQL> connect sys/root as sysdba;

已连接

1-2.查看登录用户

#方法一:

SQL> show user;

User is "SYS"

#方法二:dba_users数据字典

SQL> select username from dba_users;

USERNAME

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

MGMT_VIEW

SYS

SYSTEM

DBSNMP

SYSMAN

SCOTT

......

3.启用scott用户

语句:alter user 用户名 account unlock

其中用户名可以是任何用户。如果要锁定,将unlock修改为lock

alter user 用户名 identified by 密码 -->去修改密码

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

二、表空间

SQL Server数据库与Oracle数据库之间最大的区别要属表空间设计。

1.数据库

数据库顾名思义是数据的集合,而Oracle则是管理这些数据集合的软件系统,它是一个对象关系型的数据库管理系统。

2.表空间

表空间是Oracle对物理数据库上相关数据的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间,每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为system表空间)。

每个表空间由同一磁盘上的一个或多个文件组成,这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

3.实例

每一个运行中的数据库都对应着一个Oracle实例。当服务器上的Oracle数据库启动时,Oracle首先会在内存中分配一片区域-称之为System Global Area(SGA),然后启动一个或多个的Oracle进程。

SGA和这些进程合起来就称为一个Oracle的实例。实例中的内存和进程管理器用来高效地访问数据库的数据,同时为单个或多个的用户提供服务。

一个数据库可以由多个实例打开,但任何时刻一个实例只能打开一个数据库。多个实例可以同时运行在同一个机器上,它们彼此访问各自独立的物理数据库。

2-1.查看用户的表空间

1).dba_tablespaces数据字典(系统用户)

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

EXAMPLE

ORACLESPACE

2).user_tablespaces数据字典(普通用户)

SQL> select tablespace_name from user_tablespaces;

结果与上面一样

3).dba_users数据字典(系统用户)

4).user_users数据字典(普通用户)

2-2.创建表空间

# 创建永久表空间

SQL> create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;

# 创建临时表空间

SQL> create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m;

# 查看永久默认表空间文件存放的位置(需要大写)

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

# 查看临时表的文件位置

SQL> select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLESPACE';

2-3.修改表空间

1).设置联机或脱机状态

alter tablespace test1_tablespace offline ; //脱机状态是不能使用的

alter tablespace test1_tablespace online ; /联机

2).查看表空间状态

select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE'; //表空间名字要大写

3).设置只读或可读定状态

格式 : ALTER TABLESPACE tablespace_name READ ONLY | READ WRITE;

SQL> alter tablespace test1_tablespace read only ;

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS

---------

READ ONLY

SQL> alter tablespace test1_tablespace read write ;

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS

---------

ONLINE

2-2.修改数据文件

1).增加数据文件

语法:alter tablespace tablespace_name add datafile 'xx.dbf' size xx;

SQL> alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10m;

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME

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

E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1FILE.DBF

E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST2_FILE.DBF

2).删除数据文件

#不能删除表空间的第一个数据文件,要删除的话,必须删除整个表空间

语法:alter tablespace tablespace_name drop datafile 'filename.dbf';

SQL> alter tablespace test1_tablespace drop datafile 'test2_file.dbf';

Tablespace altered

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME

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

E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1FILE.DBF

2-5.删除表空间

# 删除表空间:

drop tablespace tablespace_name

# 删除表空间及数据:

drop tablespace tablespace_name[including contents];

SQL->drop tablesapce test1_tablespace including contents;

三、管理表

3-1.表中的数据类型

1、字符型(员工的姓名,爱好等)

固定字长:CHAR(n),n最大2000、NCHAR(n),n最大1000

可变长度:VARCHAR2(n)、NVARCHAR2(n)

2、数值型(年龄、工资整数和小数)

NUMBER(p有效数字,s小数点后的位数)

如:NUMBER(5,2)有效数字5位,只留2位小数,如123.45

FLOAT(n):存储二进制的位数

3、日期型

DATE类型:表示范围:公元前4712年1月1日到公元9999年12月31日,可以精确到秒

TIMESTAMP:时间戳类型,精确到小数秒

4、其它类型(大对像)

BLOB:4G的数据,以二进制

CLOB:也可以存放4GB的数据,以字符串

3-2.管理表

1).复制表数据

1.在建表时复制

create table user_info_new

as

select * from userinfo;

2.在添加时复制

SQL> insert into userinfo_new

2 select * from userinfo;

SQL> insert into userinfo_new(id, username)

2 select id, username from userinfo;

四、约束

约束最重要的作用就是定义规则。

4-1.非空约束

1).在创建表时设置非空约束

语法:create table table_name(column_name datatype not null, ...);

2).在修改表时添加非空约束

语法:alter table table_name modify column_name datatype not null;

3).在修改表时去除非空约束

语法:alter table table_name modify column_name datatype null;

4-2.主键约束

作用:确保表当中每一行数据的唯一性。

特点:非空,唯一。

一张表只能设计一个主键约束,

但主键约束可以由多个字段构成(联合主键)

1).在创建表时设置主键约束

语法1:create table table_name( column_name datatype primary key, ...);

语法2:constraint constraint_name primary key(column_name1,...)

SQL> create table userinfo

2 (id number(6,0),

3 username varchar2(20),

4 constraint pk_id_username primary key(id,username));

# 查看约束的名字

SQL> select CONSTRAINT_NAME from user_constraints where TABLE_NAME='userinfo';

2).在修改表时添加主键约束

语法:add constraint constraint_name primary key(column_nameq,...);

SQL> alter table userinfo2

2 add constraint pk_id primary key(id);

3).更改约束的名称

语法:rename constraint old_name to new_name

SQL> alter table userinfo

2 rename constraint pk_id_username to new_pk_id;

4).删除主键的约束

禁用约束语法:disable|enanle constraint constraint_name

SQL> alter table userinfo

2 disable constraint new_pk_id;

SQL> select CONSTRAINT_NAME ,status from user_constraints where TABLE_NAME='userinfo';

删除约束语法1:drop constraint constraint_name

SQL> alter table userinfo

2 drop constraint new_pk_id;

删除约束语法2:drop primary key [cascade]

SQL> alter table userinfo

2 drop primary key;

4-3.外键约束

1).在创建表时设置外键约束

语法1:CREATE TABLE table1(从表)(column_name datatype REFERENCES

table2(主表)(column_name),...);

注意:

1.设置外键约束时,主表的字段必须是主键

2.主从表中相应的字段必须是同一个数据类型

3.从表中外键字段的值必须来自主表中的相应字段的值,或者为null值

# 主表

SQL> create table typeinfo

2 (typeid varchar2(10) primary key,

3 typename varchar2(20));

SQL> create table userinfo_f

2 (id varchar2(20) primary key,

3 username varchar2(20),

4 typeid_new varchar2(10) references typeinfo(typeid));

SQL> insert into typeinfo values(1,1);

SQL> insert into userinfo_f(id,typeid_new) values(1,2);

insert into userinfo_f(id,typeid_new) values(1,2)

ORA-02291: 违反完整约束条件 (SYS.SYS_C0011150) - 未找到父项关键字

SQL> insert into userinfo_f(id,typeid_new) values(1,1);

1 row inserted

SQL> insert into userinfo_f(id,typeid_new) values(1,null);

insert into userinfo_f(id,typeid_new) values(1,null)

ORA-00001: 违反唯一约束条件 (SYS.SYS_C0011149)

SQL> insert into userinfo_f(id,typeid_new) values(2,null);

1 row inserted

语法2:CONSTRAINT constraint_name FOREIGN KEY(column_name)

REFERENCES table_name(column_name)[ON DELETE CASCADE]

SQL> create table userinfo_f2

2 (id varchar2(10) primary key,

3 username varchar2(20),

4 typeid_new varchar2(10),

5 constraint fk_typeid_new foreign key(typeid_new) references typeinfo(typeid));

Table created

# 如果要级联删除,在后面加ON DELETE CASCADE就行

2).在修改表时添加外键约束

语法:ADD CONSTRAINT constraint_name FOREIGN KEY(column_name)

REFERENCES table_name(column_name)[ON DELETE CASCADE]

SQL> create table userinfo_f4

2 (id varchar2(10) primary key,

3 usrname varchar2(20),

4 typeid_new varchar2(10));

Table created

SQL> alter table userinfo_f4

2 add constraint fk_typeid_alter

3 foreign key(typeid_new) references typeinfo(typeid);

Table altered

3).删除外键约束

禁用/启用约束:DISABLE|ENABLE CONSTRAINT constraint_name

SQL> select constraint_name, constraint_type, status from user_constraints

2 where table_name='USERINFO_F4';

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

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

SYS_C0011153 P ENABLED

FK_TYPEID_ALTER R ENABLED

SQL> alter table userinfo_f4

2 disable constraint FK_TYPEID_ALTER ;

Table altered

SQL> select constraint_name, constraint_type, status from user_constraints

2 where table_name='USERINFO_F4';

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

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

SYS_C0011153 P ENABLED

FK_TYPEID_ALTER R DISABLED

删除约束语法:DROP CONSTRAINT constraint_name;

SQL> alter table userinfo_f4

2 drop constraint FK_TYPEID_ALTER ;

Table altered

SQL> select constraint_name, constraint_type, status from user_constraints

2 where table_name='USERINFO_F4';

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

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

SYS_C0011153 P ENABLED

4-4.唯一约束

作用:保证字段值的唯一性

唯一约束与主键约束的区别:

1.主键字段值必须是非空的,唯一约束允许有一个空值

2.主键在每张表中只能有一个,而唯一约束可以有多个

1).在创建表时设置唯一约束

语法1:CREATE TABLE table_name(column_name datatype UNIQUE,...)

SQL> create table userinfo_u

2 (id varchar2(10) primary key,

3 username varchar2(20) unique,

4 userpwd varchar2(10));

Table created

语法2:CONSTRAINT constraint_name UNIQUE(column_name)

SQL> create table userinfo_u1

2 (id varchar2(10) primary key,

3 username varchar2(20) ,

4 constraint un_username unique(username));

Table created

2).在修改表时添加唯一约束

语法:ADD CONSTRAINT constraint_name UNIQUE(column_name)

SQL> create table userinfo_u2

2 (id varchar2(10) primary key,

3 username varchar2(20) );

Table created

SQL> alter table userinfo_u2

2 add constraint un_username_new unique(username);

Table altered

3).删除唯一约束

禁用/启用约束:DISABLE|ENABLE CONSTRAINT constraint_name

删除约束:DROP CONSTRAINT constraint_name;

使用同上

4-5.检查约束

1).在创建表时设置检查约束

语法:CREATE TABLE table_name(column_name datatype CHECK(expressions),..)

SQL> create table userinfo_c

2 (id varchar2(10) primary key,

3 username varchar2(20) ,

4 salary number(5,0) check(salary>0));

Table created

SQL> insert into userinfo_c values(1,'aa',-10);

insert into userinfo_c values(1,'aa',-10)

ORA-02290: 违反检查约束条件 (SYS.SYS_C0011161)

语法2:CONSTRAINT constraint_name CHECK(expressions)

SQL> create table userinfo_c1

2 (id varchar2(10) primary key,

3 username varchar2(20) ,

4 salary number(5,0) ,

5 constraint ck_salary check(salary>0));

Table created

2).在修改表时添加检查约束

语法:ADD CONSTRAINT constraint_name CHECK(expressions);

3).删除检查约束

禁用/启用约束:DISABLE|ENABLE CONSTRAINT constraint_name

删除约束:DROP CONSTRAINT constraint_name;

使用同上

相关TAG标签
上一篇:图解微软ReportBuilder3连接SqlServer2008数据库表创建报表
下一篇:Kubernetes1.3新特性:POD中的初始化容器
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站