oracle -- 表操作

语法：

CREATE TABLE schema_name.table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ...
    table_constraint
 );
 

案例1：直接创建表，不设置约束

CREATE TABLE tbl_students (
	stu_num CHAR(10) NOT NULL,
	stu_name VARCHAR2(10) NOT NULL,
	stu_sex CHAR(4) NOT NULL,
	stu_age NUMBER(3),
	stu_tel NUMBER(11)
)
 
// 给学生表新增一列
ALTER TABLE tbl_students ADD stu_email VARCHAR2(20);
 
// 新增
ALTER TABLE tbl_students ADD stu_email VARCHAR2(20);
 
// 修改列，仅支持修改类型和约束
ALTER TABLE tbl_students MODIFY stu_email VARCHAR2(50)
 
// 删除列
ALTER TABLE tbl_students DROP COLUMN stu_email
 
// 删除表
DROP TABLE PERSONS;

注：alter不能和本身冲突，比如性别保存男，3个字符，再修改会2个字符就会报错

> ORA-01441: cannot decrease column length because some value is too big

案例2：主键

主键 数据表中的一个或多个字段，用于唯一表示数据表中的一条数据
主键所在字段唯一且不为空，分为单列主键和多列主键

单列主键：

创建成功后，查看设计表时，可以看到键

// 在创建表时创建主键
CREATE TABLE tbl_students (
	stu_num CHAR(10) primary key,
	stu_name VARCHAR2(10) NOT NULL,
	stu_sex CHAR(2) NOT NULL,
	stu_age NUMBER(3),
	stu_tel NUMBER(11)
)
 
CREATE TABLE tbl_students (
	stu_num CHAR(10),
	stu_name VARCHAR2(10) NOT NULL,
	stu_sex CHAR(2) NOT NULL,
	stu_age NUMBER(3),
	stu_tel NUMBER(11)，
	primary key(stu_num)
)
 
// 为列添加约束 
ALTER TABLE tbl_students ADD CONSTRAINTS pk_student primary key(stu_num)

 注：添加主键的修改不能和本身冲突，当表stu_num未标注为not null时，如果数据没有null，也是可以添加约束的，但是如果数据有null或者重复，则会报错，造成添加失败

> ORA-01449: column contains NULL values; cannot alter to NOT NULL

ORA-02437: cannot validate (WATERBOSSFACTORY.PK_STUDENT) - primary key violated（用重复的值）

案例3：联合主键

使用两个或两个以上的字段作为主键

// 创建联合主键
CREATE TABLE tbl_grads(
	course_id char(3),
	course_name VARCHAR2(50) ,
	stu_num CHAR(10),
	stu_name VARCHAR2(10),
	score NUMBER(3),
	primary key (course_id,stu_num)
)
 
// 下面的会报错， ORA-02260: table can have only one primary key
CREATE TABLE tbl_grads(
	course_id char(3) primary key,
	course_name VARCHAR2(50) ,
	stu_num CHAR(10),
	stu_name VARCHAR2(10),
	score NUMBER(3) primary key
)
 
ALTER TABLE tbl_grads ADD CONSTRAINTS pk_grads primary key(course_id,stu_num)

联合主键仅

 注：

（1）只要有一个为空或者联合主键所在的字段都重复，则插入数据或者alter添加主键就会失败

ORA-01449: column contains NULL values; cannot alter to NOT NULL

> ORA-02437: cannot validate (WATERBOSSFACTORY.PK_GRADS) - primary key violated  两字段都重复

案例4：外键约束

  主外键关联，即限定外键字段的值必须来自与其它数据表中的关联字段，一般是主键

// 创建表时指定外键 如果外键关联的表不存在，则会报错 ORA-00942: table or view does not exist
CREATE TABLE tbl_students(
	stu_num CHAR(10) primary key,
	stu_name VARCHAR2(10) NOT NULL,
	stu_sex CHAR(4) NOT NULL,
	stu_age NUMBER(3),
	stu_tel NUMBER(11),
	stu_cid CHAR(3) not NULL,
	CONSTRAINT fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade
)
 
// on delete cascade代表删除班级表的主键时，学生表的相关数据也会删除
 
// 添加外键约束
ALTER TABLE tbl_students ADD CONSTRAINTS fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade;
 
// 删除外键约束
ALTER TABLE TBL_STUDENTS DROP CONstraints fk_student_classes;

注：

（1）两个表的删除逻辑是必须先删除班级表，然后再删除学生表，因为学生表的外键指向了班级表的主键

（2）设置了on delete cascade后，在删除班级表中的数据时，会同时删除掉班级的学生

案例5：CHECK约束

是检查约束，用于限定每一列能够输入的值，以保证数据的正确性

// 添加check约束
CREATE TABLE tbl_students(
	stu_num CHAR(10) primary key,
	stu_name VARCHAR2(10) NOT NULL,
	stu_sex CHAR(4) NOT NULL,
	stu_age NUMBER(3),
	stu_tel NUMBER(11),
	stu_cid CHAR(3) not NULL,
	CONSTRAINT fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade,
	CONSTRAINT ck_student_sex CHECK(stu_sex='男' or stu_sex='女')，
	CONSTRAINT ck_student_age CHECK(stu_age BETWEEN 6 AND 30)
)
 
ALTER TABLE TBL_STUDENTS DROP constraints ck_student_age;
 
ALTER TABLE tbl_students add constraints ck_student_age CHECK(stu_age BETWEEN 6 AND 30);

添加好check约束后如下：

 此时添加数据不满足约束时，会报错

 

案例6：UNIQUE约束

用于限定字段的唯一性，唯一键添加方式有三种，一种是在字段直接添加unique，一种是使用CONSTRANT指定，这两种的区别是直接添加unique时唯一键的名称是随机的，CONSTRANT是指定的。

// 电话是唯一的，添加唯一约束
CREATE TABLE tbl_students(
	stu_num CHAR(10) primary key,
	stu_name VARCHAR2(10) NOT NULL,
	stu_sex CHAR(4) NOT NULL,
	stu_age NUMBER(3),
	stu_tel NUMBER(11) UNIQUE,
	stu_cid CHAR(3) not NULL ,
	CONSTRAINT fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade,
	CONSTRAINT ck_student_sex CHECK(stu_sex='男' or stu_sex='女')，
	CONSTRAINT ck_student_age CHECK(stu_age BETWEEN 6 AND 30)
)
 
 
CREATE TABLE tbl_students(
	stu_num CHAR(10) primary key,
	stu_name VARCHAR2(10) NOT NULL,
	stu_sex CHAR(4) NOT NULL,
	stu_age NUMBER(3),
	stu_tel NUMBER(11),
	stu_cid CHAR(3) not NULL ,
	CONSTRAINT fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade,
	CONSTRAINT uq_student_tel UNIQUE(stu_tel),
	CONSTRAINT ck_student_sex CHECK(stu_sex='男' or stu_sex='女')，
	CONSTRAINT ck_student_age CHECK(stu_age BETWEEN 6 AND 30)
)
 
 
ALTER TABLE TBL_STUDENTS DROP constraints uq_student_tel;

添加唯一键后可以在唯一键的地方查看

如果添加重复的数据，会报错

 案例7：NOT NULL

 已经一直使用，不再赘述