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;
使用同上