表
表示基本的数据存储结构，一个表通常由行和列（字段） 组成
创建表可以指定创建到那个表空间上， 如果不指定将默认创建到用户的默认表空间里
创建表的类型
1. 堆表 ： 数据安装组织，以无序方式放在单独的表字段中， 也称为标准表
2. 索引表 ： 数据以“B树” 解耦股，存放在主键约束所对应的的索引字段中
3. 簇表 ： 簇由供需数据库的组表组成， 在某些情况下。使用簇表可以节省空间， 提高SQL的性能
4. 分区表 ： 数据被划分为很小区间范围，并且存储到响应的分区段中，每个分区段可以独立和操作
数据类型
number（p，s）： 可##变长度数字 ## ， p：数字可用的最大位数（如果有小数，是小数点前后之和）s：小数点的最大位数，最大精度不能超过38位，
number （4,2）： 12.34
double , float ： 都是number的子类型，38精度的浮点数
real ： number的子类型18位精度的浮点数
int ： integer ，smallint ： 38位精度的整数
long ： 可变长的字符数据类型：2GB
xml
varchar2(size）： 最大长度4000字节，最小位1字节
nvarchar2（size） ： 可变长字符串，最大长度4000个字节
char（）
nchar（）
date ： 年月日
timestamp ： 年月日时分秒和毫秒
clob ： 大字符串对象可以存储4GB的字符文本
nclob ：
Blob ： 二进制文件
创建表
create table [schema.]table_name(
column_name data_type [defaulte expression] [constraint],
column_name data_type [defaulte expression] [constraint],
column_name data_type [defaulte expression] [constraint],
......
)
schema : 指定表所属的用户名
table_name : 创建表的表名
column_name : 列名,一个表每列必须具有唯一性
data_type : 列的数据类型
defaulte expression : 列的默认值
constraint : 添加约束,表示该列的值必须满足的规则
create table book(
bookid number(10) not null,
bookname varchar2(40),
author varchar2(40) ,
press varchar2(40) ,
bookprice number(4,2) ,
presstime date,
constraint pk_book_id primary key(bookid)
);
create table book2(
bookid number(10) not null,
bookname varchar2(40),
author varchar2(40) ,
press varchar2(40) ,
bookprice number(4,2) ,
presstime date,
constraint pk_book_id2 primary key(bookid)
)tablespace system;
修改表:
增加和删除列
SQL> alter table book2 add c1 number;
表已更改。
SQL> desc book2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
BOOKPRICE NUMBER(4,2)
PRESSTIME DATE
C1 NUMBER
SQL> alter table book2 drop column c1;
表已更改。
SQL> desc book2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
BOOKPRICE NUMBER(4,2)
PRESSTIME DATE
禁用指定的类(我们将不能使用该列,但是当前列还占用存储空间)
SQL> alter table book2 set unused(presstime);
表已更改。
删除标记为unused的列,可以将这些列从物理上删除,收回占用的磁盘空间
SQL> alter table book2 drop unused column;
表已更改。
修改列
SQL> alter table book2 rename column bookprice to price;
表已更改。
SQL> desc book2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
PRICE NUMBER(4,2)
SQL> alter table book2 modify price number(6,3);
表已更改。
SQL> desc book2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
PRICE NUMBER(6,3)
对表重命名
SQL> alter table book2 rename to book3;
表已更改。
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
BOOK3
BOOK
SALGRADE
BONUS
EMP
DEPT
已选择6行。
将表移动到另一个表空间
SQL> select table_name,tablespace_name from user_tables where table_name = 'BOOK4';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BOOK4 SYSTEM
SQL> alter table book4 move tablespace users;
表已更改。
SQL> select table_name,tablespace_name from user_tables where table_name = 'BOOK4';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BOOK4 USERS
删除表
drop table 表名;
删除表并立即释放该表所占用的资源空间
drop table 表名 purge;
不仅删除表,还会将引用该表的视图,约束,索引,触发器等.全部一并删除
drop table 表名 cascade constraints
完整性约束
数据库中的数据正确性和相容性,用来防止想数据库中添加不合语义的数据.
表级别约束 : 定义在一个表中,可用于表中多列
列级别约束 : 对表中的一列进行约束,只能应用一列
约束的用途分类:
- primary key (主键约束)
主键是由一列或多列组成,唯一标识
在一个表中,只能定义一个主键约束
不能重复,并且不能包含null. - foreign key (外检约束)
外键引用另一个表中的一列,取值范围只能是引用中以存在的值
在使用外键约束的时候,被引用的列应该具有主键约束,或者具有唯一性约束. - unique (唯一约束)
只能存储唯一的值,可以存储null
oralce将会自动为unique的列创建一个唯一索引
可以在同一个列上建立not null和unique约束(主键约束) - not null (非空约束)
指定一列不允许存储空值,这就是一种强制的检查约束 - check (检查约束)
指定一列的值必须满足某种条件
在check约束的表达式中,必须引用表中的一个或多个列,并且表达式的运算结构是一个布尔值.
一个列中可以定义多个检查约束
检查约束可以定义在表和列上.
SQL> alter table book4 modify bookname not null;
表已更改。
SQL> desc book4;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME NOT NULL VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
BOOKPRICE NUMBER(4,2)
PRESSTIME DATE
SQL> alter table book4 modify bookname null;
表已更改。
SQL> desc book4;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
BOOKPRICE NUMBER(4,2)
PRESSTIME DATE
添加主键:
create table table_name(
id number(10) primary key,
...
)
为已经创建的表添加主键约束
alter table table_name add constraint pk_... primary key(列名)
alter table table_name add primary key(列名)
删除约束:
alter table table_name drop constraint 约束的名称;
检查约束
create table book(
age number(3) constraint ck_age check (age>0)
)
SQL> alter table book4 add constraint ck_book4_price check(bookprice>10);
表已更改。
创建外键:
-- Create the table in the specified schema
CREATE TABLE classes
(
classid number(10) NOT NULL PRIMARY KEY, -- primary key column
cname NVARCHAR2(50) NOT NULL,
stunum NVARCHAR2(50) NOT NULL
-- specify more columns here
);
CREATE TABLE student(
sid number(4),
sname varchar2(30),
classid number(10) references classes(classid)
);
为已有表添加外键:
alter table student add constraint fk_student_cid foreign key(classid) references classes(classid)
引用类型:
cascade
如果定义外键约束时使用cascade关键字,那么当父表中被引用列删除时,子表中对应的数据将同时被删除.
set null
当删除父表中引用列数据的时候,子表中的数据列将被设置成null
no action
当删除父表中数据会被提示违反外键约束.
创建用户必须拥有create user 系统权限
修改： alter user 用户名 identified by 密码
授予用户登录权限
grant create session to user1
修改默认表空间
alter user user1 default tablespace 表空间名称
让密码失效
alter user 用户名 password expire;
锁定用户
alter user 用户名 account lock；
解锁
alter user 用户名 account unlock;
删除用户
drop user 用户名 [cascade]
登录用户
connect 用户名/密码
权限
权限是数据库中执行某种操作的能力.创建一个用户,表示该用户具有了连接和操作
数据库的资格.但是要对数据库进行实际操作,则需要为用户赋予相应的操作权限.
系统权限
系统级控制数据库的存取和使用,是否可以连接到数据库.
创建会话,创建表,创建视图,创建用户等.
系统权限分类:
包含 any 关键字的系统权限 , 可以在任何Oracle模式(用户)下进行相应的操作
不包含 any 关键字的系统权限, 只能在自身用户模式中进行相应的操作.
select any ->可以查询任何用户下面的表
select -> 只能查询自身用户下面的表
create table
create session
manage tablespace
create/alter/drop user
create view;
对象权限
模式对象控制存取和使用的机制
对表,视图,存储过程和函数等操作的权限
insert,select,update,delete,alter,index
赋权 :
grant 权限名称 to 用户名
grant create session to user1;
grant create session to user1 with admin option;
with admin option : 该用户拥有赋予其他用户赋予此权限的能力
权限回收:
revoke create table,create view from user1;
角色 :
角色是一组权限的集合.可以向用户授予角色的所有权限,也可以收回角色权限.
可以使用预定义角色,也可以自定义角色.
SQL> select role,password_required from dba_roles;
ROLE PASSWORD
------------------------------ --------
CONNECT NO
RESOURCE NO
DBA NO
SELECT_CATALOG_ROLE NO
CONNECT : 用户典型权限
alter session,create cluster,create database,create sequence,create synonym,create view等...
RESOURCE : 开发者权限
create table,操作表空间....
DBA : 管理员权限
select any table,create type,createt table....
SELECT_CATALOG_ROLE : 具有查询数据字典的权限,并执行部分存储过程和函数的权限
创建角色:
create role role_name
not identified | identified by password;
修改默认角色
alter user 用户名 default role 角色名称,角色名.....
删除角色
drop role 角色名称;
dba_roles : 数据库中的所有角色
dba_role_privs : 授予用户和角色的角色.
查询当前会话包含的所有角色
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
创建用户,授权.
权限/角色
