创建、维护表与约束

数据类型

• 字符型
• char自动补齐空值（定长字符型）
• varchar2不自动补齐空置（变长字符型）
• 数字型
• number(p,s)p为总长度，s为小数点后保留位数
• 日期型
• date年月日时分秒
• timestamp
• 图片类型
• blob

default定义默认值（如果不插入值则为默认值）

创建表

create table class
(name varchar2(20),
class number(20)
classno number(20))

修改表

• alter table emp
  add（添加列）/modify（修改列）/drop（删除列）
• 修改列名

alter table emp 
rename column mgr to managerno 

删除表

drop table emp

重命名表

rename emp to employee

截断表

truncate table emp

• 截断表是删除表中所有数据，使用rollback不可以回滚

数据字典

• oracle数据库里的表（sys用户中）
• 查询数据字典

select table_name
from user_tables

• 查看数据字典结构

desc user_tables

约束

• constraint（列级、表级）

create table class(
name varchar(20) not null/unique)

• 约束命名写在constraint后

• 主键数据唯一，并且不能为空值

create table class(
id number constraint class_id_pk primary key,
 name varchar(20) 
 )

• 外键必须是其他表里的主键或者唯一键

create table student(
id number,
name varchar2(20),
class_id number,
constraint student_classid_fk foreign key(class_id) references class(id)
on delete cascade
)

• on delete cascade级联删除：主键行删除，将所有引用该主键的外键信息删除
• on delete set null：主键行删除，将所有引用该主键的外键信息变为null
• check约束：确保列中的数据满足条件

create table emp(
sal number(8,2) constraint emp_sal_min check (sal>0)
)

• 追加约束

alter table emp 
add constraint emp_sal_ch check (sal>0)

• 删除约束

alter table emp 
drop primary key|unique(column)|constraint emp_sal_ch

• 删除该约束和与其相关的所有约束

alter table dept 
drop primary key emp_sal_ch cascade

• 启用和禁用约束

alter table emp 
disable constraint emp_sal_ch

alter table emp 
enable constraint emp_sal_ch

• 相关数据字典

select *
from user_constraints|user_cons_columns

练习2 通过子查询的方式创建一个表dept10，该表保存10号部门的员工数据。

create table dept10 as (select * from emp where deptno=10)

练习3.1 在员工表中添加一个性别列，列名为gender，类型为char(2)，默认值为“男”。

alter table emp 
add(gender char(2) default '男')

3.2 修改员工表中性别列的数据类型为char(4)。

alter table emp 
modify(gender char(4))

3.3 修改员工表中性别列的默认值为“女”。

alter table emp 
modify(gender default '女')

3.4 删除员工表中的性别列。

alter table emp 
drop (gender)

课后作业2.创建表date_test，包含列d，类型为date型。试向date_test表中插入两条记录，一条当前系统日期记录，一条记录为“1998-08-18”。

create table date_test 
(d date );
insert into date_test values (sysdate);
insert into date_test values ('1998-08-18')

3. 创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表。

create table dtest as (select * from dept where 1=0)
insert into  dtest (select * from dept where deptno<40)

4. 创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。

create table empl as(select * from emp where 1=0)
insert into empl (select * from emp where deptno<=30)

5. 试为学生表student增加一列学生性别gender，默认值“女”。

alter table student add (gender varchar2(6) default '女')

6.试修改学生姓名列数据类型为定长字符型10位。

alter table student modify(XM char(10))

约束练习题

练习1.1 学校有一个选课系统，其中包括如下关系模式：
系
（系编号：主键，
系名称：唯一键，
系主任：非空约束，
系所在校区：取值范围只能在南湖校区和浑南校区）
班级
（班级编号：主键，
班级名称：唯一键，
所属系：外键）

create table chooseclass 
(xno number(10) constraint chooseclass_xno_pk primary key,
xname varchar2(20) constraint chooseclass_xname_uk unique,
xleader varchar2(20) constraint chooseclass_xleader_nn not null,
xschool varchar2(30) constraint chooseclass_xschool_ch check (xschool in ('南湖校区','浑南校区'))
);

create table class2
(cno number(10) constraint class2_cno_pk primary key,
cname varchar2(20) constraint class2_cname_uk unique,
cx number(10) constraint class2_cx_fk references chooseclass(xno)
)

1.2 创建学生表，包含如下属性：
学号 定长字符型 10位 主键
姓名 变长字符型 20位 非空
性别 定长字符型 2位 取值范围只能为男或女
出生日期 日期型
所在班级

create table student2
(id char(10) constraint student2_id_pk primary key,
name varchar2(20) constraint student2_name_nn not null,
sex char(2) constraint student2_sex_ch check (sex in ('男','女')),
birthday date,
cno number(10))

课后作业3. 创建copy_emp，要求格式同emp表完全一样。

create table copy_emp as
(select * from emp where 1=0)

4. 创建copy_dept，要求格式同dept表完全一样。

create table copy_dept as 
(select * from dept where 1=0)

6. 追加copy_dept表中主键deptno

alter table copy_dept
add constraint copydept_deptno_pk primary key (deptno)