1.创建表空间
表空间是数据库中最大的逻辑单位,一个 Oracle 数据库至少包含一个表空间,就是名为SYSTEM的系统表空间
每个表空间是由一个或多个数据文件组成的,一个数据文件只能与一个表空间相关联。
表空间的大小等于构成该表空间的所有数据文件大小之和。
默认包含如下表空间(从v$tablespace中查看):SYSTEM、SYSAUX、TEMP、UNDOTBS1、USERS
SYSTEM是系统表空间,存放系统的最基本的信息,如果SYSTEM表空间坏掉,Oracle将无法启动。
SYSAUX从10g中引入,作为SYSTEM的辅助表空间,用以减少SYSTEM表空间的负荷 。以前其他表空间中的一些组件,现在放到SYSAUX表空间中了
TEMP是临时表空间,当排序不能在分配的空间中完成时,就会使用磁盘排序的方式,即在Oracle实例中的临时表空间中进行。
UNDOTBS1是撤销表空间,是UNDO类型的表空间,保存用户进行DML操作中,修改前的数据。
USERS是数据库默认的永久表空间。
查询当前用户默认表空间
select default_tablespace from user_users;
创建表空间:
create tablespace tablespace_name datafile '表空间文件路径' size 1G autoextend on next 100M maxsize unlimited;
create tablespace:创建表空间关键字
tablespace_name:表空间名称
datafile:指定表空间文件(一般指向oracle安装根目录下oradata/数据库实例名/.dbf)
size 1G:指定表空间文件的初始大小,大小单位可以是G、M、K等
autoextend on next 100M:autoextend表示表空间大小自动扩展,on next 100M指定每次表空间满了之间扩展的大小
maxsize unlimited:maxsize指定表空间的最大存储值,unlimited表示不限制表空间的最大值(也就是只要硬盘不满,表空间就可以一直扩展)
创建一个test1的表空间(必须使用管理员用户system, sys )
create tablespace test1
datafile 'D:\app\Administrator\oradata\orcl\test1data.dbf'
size 1G
autoextend on next 100M
maxsize unlimited;
--修改表空间
ALTER database 数据库名
DATAFILE 'D:\app\Administrator\oradata\orcl\test1data.dbf' AUTOEXTEND
ON NEXT 200M MAXSIZE 2G
--修改原有的数据文件大小
alter database 数据库名datafile 'D:\app\Administrator\oradata\orcl\test1data.dbf' resize 100M;
--为表空间增加新的数据文件
alter database 数据库名 表空间名 add datafile 数据文件 size 大小
2.临时表空间
当排序不能在分配的空间中完成时,就会使用磁盘排序的方式,即在Oracle实例中的临时表空间中进行。
创建临时表空间语法:
create temporary tablespace tablespace_name tempfile '表空间文件路径' size 1G autoextend on next 100M maxsize unlimited;
temporary:表明创建的表空间是临时表空间
tempfile:指定临时表空间文件
创建临时表空间(用管理员用户)
create temporary tablespace test1temp
tempfile 'D:\app\Administrator\oradata\orcl\test1temp.dbf'
size 1G autoextend on next 100M maxsize unlimited;
3.创建用户
语法:
create user user_name
default tablespace tablespace_name
temporary tablespace temptablespace_name
identified by password;
create user:创建用户关键字
user_name:用户名
default tablespace tablespace_name:指定用户的默认表空间 tablesapce_name表空间名(如果省略默认使用users表空间)
temporary tablespace temptablespace_name:指定用户的默认临时表空间,temptablespace_name临时表空间名(如果省略默认使用temp表空间)
identified by password:identified by 指定用户的密码,password用户密码
创建一个test1用户
create user test1 --创建用户
identified by test1--设置密码
default tablespace test1 --将表存储在那个表空间文件内
temporary tablespace test1temp ;--临时表
修改用户
alter user user_name default tablespace tablespace_name temporary tablespace temptablespace_name identified by password;
4.权限
权限指的是执行特定命令或访问数据库对象的权利
权限有两种类型,系统权限和对象权限
系统权限允许用户执行某些数据库操作,如创建表就是一个系统权限
对象权限允许用户对数据库对象(如表、视图、序列等)执行特定操作
GRANT 命令可用于为用户分配权限或角色
系统权限如:
GRANT CREATE SESSION TO test1; --此系统权限允许用户连接到数据库上
GRANT CREATE TABLE TO test1; --此系统权限允许用户创建表
GRANT UNLIMITED TABLESPACE TO test1; --此系统权限允许test1用户任意使用表空间
对象权限如:
GRANT SELECT ON SCOTT.EMP TO test1; --允许用户查询 EMP 表的记录
GRANT UPDATE ON SCOTT.EMP TO test1; --允许用户更新 EMP 表中的记录
GRANT ALL ON SCOTT.EMP TO test1; --允许用户插入、删除、更新和查询 EMP 表中的记录
角色管理
角色是一组相关权限的组合,可以将权限授予角色,再把角色授予用户,以简化权限管理。
GRANT CONNECT TO test1; --CONNECT角色允许用户连接至数据库,并创建数据库表
GRANT RESOURCE TO test1; --RESOURCE角色允许用户使用数据库中的存储空间
GRANT CREATE SESSION TO test1; --此系统权限允许用户连接到数据库上
查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
给用户赋权限
grant connect,resource to test1;
revoke 收回权限
Revoke commect,resource from test1;
删除用户
drop user user_name;
删除表空间
drop tablespace tablespace_name;
5.数据类型
数值类型:
number(l,s):表示数值类型,可以表示整数和小数,l表示长度,最大长度是38,s表示精度默认是0,
number(5,2):表示整体长度是5,有2位小数
其他:int,integer 整数 float、double 浮点数(带小数)
字符串类型:
varchar2(l):变长字符串类型,l表示长度,最长4000字节
char(l):定长字符串类型,l表示长度,例 char(8) 存'abcd' 它长度是8
其他:varchar、nlschar、nlsvarchar2、
日期类型:
date:表示日期类型,可以存放世纪、纪元、年月日时分秒
timestamp:时间戳,可以存放世纪、纪元、年月日时分秒,秒后面的9位
CLOB/BLOB:
CLOB:存储大文本文件的数据类型,
BLOB:二进制文件存储类型,
LONG:存储长字符串,最大可以存2G的字符串(oracle不建议使用)
6.建表语法
create table 表名(
列名(字段名) 数据类型 约束 默认值,--约束、默认值都是可以省略的
列名 数据类型 约束 默认值,
列名 数据类型 约束 默认值,
...
表级约束 --可选
)tablespace 表空间名;
表名、列名、表空间名等都叫做标识符,它一般以字母开头,可以有_、数字、$等,长度不能超过30个英文字符;
tablespace 表空间名:是可省略的,默认表会保存到用户的默认表空间中,也可以加上,表示指定将表存入某个表空间;
create table person(
id number(11),
name varchar2(20),
age number(3),
sex number(1),
birthday date
);
删除表: drop table 表名;
注:
create table 表名 as select语句; --经常用作数据备份
create table emp_bak as select * from emp;
create table emp as select * from emp_bak;
7.约束
约束的分类:行级约束和表级约束
行级约束:建表时跟在字段定义后面,约束当前行定义的列的约束叫做行级约束
表级约束:建表时写在所有字段定义之后的约束,它可以约束某个列或者多个列
1.not null 非空约束
列名 数据类型 not null --行级约束语法
not null:表示这个列的值不能为空
注意:not null是唯一个行级约束,不能写成表级约束
2.unique 唯一约束
列名 数据类型 unique --行级约束语法
unique:表示这个列的值在整个表的数据中,是唯一的不可重复
constraint 约束名 unique(列名) --表级约束,在所有列定义之后
3.primary key 主键约束
列名 数据类型 primary key --行级约束语法
primary key:是not null和unique的结合,表示这个列的值不能为空且不能重复
constraint 约束名 primary key(列名[,列名]) --表级约束
在表级约束中,如果有多个列时,表示这几个列为联合主键()
4. foreign key 外键约束
列名 数据类型 references 主表名(主表主键列) --行级约束语法
foreign key:表示这个列的取值,只能在主表的主键列的值中去取
create table emp_t(
empno number(7) primary key, --主键约束
ename varchar2(30) not null, --非空约束
job varchar2(30),
sal number(7,2),
hiredate date,
deptno number(7) references dept(deptno) --外键约束
);
constraint 约束名 foreign key(外键列) references 主表名(主表主键列) --表级约束
5.check 检查约束
列名 数据类型 check(约束条件) --行级约束
check:表示这个列的取值要满足check后面的约束条件
drop table user_info;
create table user_info(
id number(11) primary key,
name varchar2(30) not null,
age number(3) check(age>0 and age<150),
sex number(1) default 0,
pwd varchar2(20),
constraint ck_pwd check(length(pwd)>8)
);
constant 约束名 check(约束条件) --表级约束
8.修改表和约束
1.给表添加一个列
alter table 表名 add 列名 数据类型 [约束] [default 默认值];
2.删除表中的一个列
alter table 表名 drop column 列名;
3.修改一个列
alter table 表名 modify 要修改的列名 数据类型 [约束] [default 默认值];
修改列时,如果列有数据,不能直接修改列的数据类型,只能把它长度增加,但不能减少。
如果要将有数据的列的长度减小,可以在表中增加一个新列和原来的列数据类型和长度完全相同,将要修改的列的数据放入新增加的列里
然后,1把要修改的这个列里的数据清空,修改列的数据长度,之后将数据从新增的临时列中更新到修改后的列里
2.把要修改的列删除,再增加一个同名列,修改列的数据长度,之后将数据从新增的临时列中更新到修改后的列里
alter table 表名 modify 列名 数据类型 not null; --给一个列添加not null 约束
alter table 表名 modify 列名 数据类型 null --给一个列去掉非空约束
4.增加一个约束
alter table 表名 add 表级约束语法;
alter table 表名 add constraint 约束名 unique(列名)
alter table 表名 add constraint 约束名 primary key(列名[,列名])
alter table 表名 add constraint 约束名 foreign key(外键列) references 主表名(主表主键列)
alter table 表名 add constraint 约束名 check(约束条件)
5.删除一个约束
alter table 表名 drop constraint 约束名;
表级约束有名字,行级约束系统会给分配一个名字sys_
注释 comment
给表加注释
comment on table 表名 is '注释';
comment on table user_info is '用户基础信息表';
comment on column 表名.列名 is '注释';
comment on column user_info.id is '主键';
comment on column user_info.name is '用户姓名';
comment on column user_info.age is '用户年龄';
comment on column user_info.sex is '性别';
comment on column user_info.pwd is '用户密码';
select * from user_col_comments where table_name='USER_INFO';