Oracle用户操作、数据类型、表格修改、约束设置详解

用户和权限

1、用户查询和创建

a) 语法

create user 用户名 identified by 密码;

b) 创建用户 bjsxt, 设定密码为 bjsxt

注意: 操作数据库对象是需要 dba 权限的

代码语言：javascript复制

create user bjsxt identified by bjsxt;

2、用户授权（grant）

Oracle 中的权限很多, 为了方便用户管理权限, 提供了角色这个概念. 角色代表一个身份, 该身份拥有固定的权限. 常见的角色: DBA: 数据库管理员 CONNECT: 临时用户, 拥有少量的权限 RESOURCE: 比较靠谱的用户, 拥有更多的权限, 不能管理数据库

a) 给 bjsxt 用户授予普通用户的权限

代码语言：javascript复制

grant connect, resource to bjsxt; 

b) 从 bjsxt 用户撤销 resource 权限

代码语言：javascript复制

revoke resource from bjsxt; 

3.、删除用户 （drop）

代码语言：javascript复制

drop user bjsxt;

4.、修改用户 （revoke）

修改用户密码

代码语言：javascript复制

alter user bjsxt identified by 123; 

锁定用户和解锁定用户

a) 锁定用户

代码语言：javascript复制

alter user bjsxt account lock; 

c) 解锁用户

代码语言：javascript复制

alter user bjsxt account unlock;

Oracle 中的数据类型

1、字符类型

a) varchar2 可变长度的字符串, 效率较低 ，注意不是varchar b) char 不可变长度的字符串, 效率较高

2、数值类型

number, 既可以表示整数, 也可以表示浮点数

3、日期时间类型

a) date, 存放日期和时间 b) timestamp, 比 date 更精确的日期时间类型

4、lob 类型

a) blob, 用于存放二进制数据, 可以用于存放文件, 图片, 音频, 视频等二进制数据. b) clob, 用于存放大文本信息.

创建表格

创建学生表, 字段包含学号、姓名、性别，年龄、入学日期、班级，email 等信息

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12),        
gender char(3),        
age number(3),        
sdate date,        
clazz varchar2(20),        
email varchar2(50) );

修改表格 （alter）

1、添加字段

代码语言：javascript复制

alter table student add (score number(3)); 

2、修改字段的类型

代码语言：javascript复制

alter table student modify (score number(5,2)); //显示五位有效数字，其中有两位小数

3、重命名字段

代码语言：javascript复制

alter table student rename column score to fenshu; 

4、删除字段

代码语言：javascript复制

alter table student drop column fenshu; 

重命名表格 （rename）

代码语言：javascript复制

rename student to stu; 

删除表格 （drop）

代码语言：javascript复制

drop table stu;

表格的约束

constraints, 约束, 用于对表格的数据进行限制, 保证表格数据的完整性和一致性. 语法: constraints 约束名 约束类型 (约束字段)

1. 主键约束(primary key)

主键是用于唯一标识一条记录的字段, 必须保证既非空又唯一. 一张表中, 只能有一个主键. Constraints: 约束

a) 在表级别定义主键约束, 对约束统一管理

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12),        
gender char(3),        
age number(3),        
sdate date,        
clazz varchar2(20),        
email varchar2(50),        
constraints pk_student primary key (sno) );

b) 表级别简化版定义主键约束, 省略约束名 create table student (

代码语言：javascript复制

sno number(4),        
sname varchar2(12),        
gender char(3),        
age number(3),        
sdate date, 
clazz varchar2(20),        
email varchar2(50),        
primary key (sno) ); 

c) 在列级别定义主键约束

代码语言：javascript复制

create table student (        
sno number(4) constraints pk_student primary key,        
sname varchar2(12),        
gender char(3),        
age number(3),        
sdate date,        
clazz varchar2(20),        
email varchar2(50) ); 

d) 在列级别简化版定义主键约束

代码语言：javascript复制

create table student (        
sno number(4) primary key,        
sname varchar2(12),        
gender char(3),        
age number(3),        
sdate date,        
clazz varchar2(20),        
email varchar2(50) );

e) 联合主键, 只能在表级别定义, 因为一个表只能有一个主键

代码语言：javascript复制

create table student (       
sno number(4),        
sname varchar2(12),        
gender char(3),        
age number(3),        
sdate date,        
clazz varchar2(20),        
email varchar2(50),        
constraints pk_student primary key (sno, email) );

2. 非空约束(not null)

字段值不允许为空, 非空约束只能在列级别定义

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12) not null,        
gender char(3),        
age number(3),        
sdate date,        
clazz varchar2(20),        
email varchar2(50),        
constraints pk_student primary key (sno) );

3. 唯一约束(unique)

要求字段值不能重复. 写在字段后的是列级约束，写在最后的是表级约束

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12) not null,        
gender char(3),        
age number(3),        
sdate date,        
clazz varchar2(20),        
email varchar2(50), -- unique,        
constraints pk_student primary key (sno),        
constraints uk_student_email unique (email));

4. 检查约束(check)

用于限定字段值的取值范围 create table student ( sno number(4), sname varchar2(12) not null, gender char(3) check (gender in (‘男’, ‘女’)), age number(3), sdate date, clazz varchar2(20), email varchar2(50), constraints pk_student primary key (sno), constraints uk_student_email unique (email), constraints ck_student_age check (age between 18 and 30) );

5. 外键约束(foreign key)

用于约束表和表之间的关系. 两张表的依赖关系. 以 emp 表和 dept 表为例, emp 表依赖 dept 表. 因此, dept 表可以被称之为主表, emp 表被称之为从表. 注意: 主表中, 只有主键或者唯一键才可以被从表参考. 从表中作为外键的列, 类型一定要和主表的被参考列相同.

a) 建立主表 clazz

代码语言：javascript复制

create table clazz (        
cno number(3) primary key,        
cname varchar2(20) not null,        
croom number(3) ); 

b) 创建从表, 并定义外键 student

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12) not null,        
gender char(3) default '男' check (gender in ('男', '女')),        
age number(3),        
sdate date,        
email varchar2(50),        
cno number(3),        
constraints pk_student primary key (sno),        
constraints uk_student_email unique (email),        
constraints ck_student_age check (age between 18 and 30),        
constraints fk_student_cno foreign key (cno) references clazz (cno) ); 

c) 也可以在列级别定义外键, 如下:

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12) not null,        
gender char(3) default '男' check (gender in ('男', '女')),        
age number(3),        
sdate date,        
email varchar2(50),        
cno number(3)  references clazz(cno), --外键的简写       
constraints pk_student primary key (sno),        
constraints uk_student_email unique (email),        
constraints ck_student_age check (age between 18 and 30) );

6. 外键中的删除问题:

主表中的数据在被引用时, 无法直接删除, 默认情况下, 需要先删除从表中对应的所有数据, 再删除主表中的数据. 可以在定义外键时, 设置删除策略为 cascade(级联), 表示当删除主表的信息时, 同时删除从表中所有关联的信息

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12) not null,        
gender char(3) default '男' check (gender in ('男', '女')),        
age number(3),        
sdate date,        
email varchar2(50),        
cno number(3),        
constraints pk_student primary key (sno),        
constraints uk_student_email unique (email),        
constraints ck_student_age check (age between 18 and 30),        
constraints fk_student_cno foreign key (cno) references clazz (cno) on delete cascade ); 

可以在定义外键时, 设置删除策略为 set null(设空), 表示当删除主表的信息时, 将从表中的相关数据设置为 null

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12) not null,        
gender char(3) default '男' check (gender in ('男', '女')),        
age number(3),        
sdate date,       
email varchar2(50),        
cno number(3),        
constraints pk_student primary key (sno),        
constraints uk_student_email unique (email),        
constraints ck_student_age check (age between 18 and 30),        
constraints fk_student_cno foreign key (cno) references clazz (cno) on delete set null ); 

在修改表格时添加约束

a) 创建主表 clazz

代码语言：javascript复制

create table clazz (        
cno number(3) primary key,        
cname varchar2(20) not null,        
croom number(3) ); 

b) 创建从表 student

代码语言：javascript复制

create table student (        
sno number(4),        
sname varchar2(12) not null,        
gender char(3) default '男',        
age number(3),        
sdate date,        
email varchar2(50),        
cno number(3) ); 

c) 给 student 表添加约束(alter table xxx add constraints) – 主键约束

代码语言：javascript复制

alter table student add constraints pk_student primary key (sno); 

– 唯一约束

代码语言：javascript复制

alter table student add constraints uk_student_email unique (email); 

– 检查约束

代码语言：javascript复制

alter table student add constraints ck_student_age check (age between 18 and 30); 
alter table student add constraints ck_student_gender check (gender in ('男','女'));

– 外键约束

代码语言：javascript复制

alter table student add constraints fk_student_cno foreign key (cno) references clazz (cno);

constraints email key null table

0 人点赞