1、创建数据表
1.1 语法
CREATE TABLE <表明>(
字段1 数据类型 [列级别约束条件] [默认值],
字段2 数据类型 [列级别约束条件] [默认值],
......
[表级别约束条件]
);
常用的数据:整数,浮点数,精确小数,二进制,日期/时间,字符串等数据类型
示例一
创建TB_EMP1表
CREATE TABLE TB_EMP1 (
ID NUMBER(11),
NAME VARCHAR2(25),
DEPTID NUMBER(11),
SALARY NUMBER(8,2)
);
1.2 查询表结构
DESC TB_EMP1;
2、主键约束语法 PRIMARY KEY CONSTRAINT
2.1 概念
主键约束: 数据唯一，并且不不允许空，分为单字段主键和多字段联合主键 类似我们的身份证
2.2 单字段主键语法如下
2.2.1 字段名 数据类型 PRIMARY KEY [默认值]
2.2.2 定义完所有列后采用以下语句
[CONSTRAINT <约束名>] PRIMARY KEY(字段名)
示例2
创建TB_EMP2表和TB_EMP3表，定义主键为ID
--2.2.1
CREATE TABLE TB_EMP2 (
ID NUMBER(11) PRIMARY KEY,
NAME VARCHAR2(25),
DEPTID NUMBER(11),
SALARY NUMBER(8,2)
);
--2.2.2
CREATE TABLE TB_EMP3 (
ID NUMBER(11),
NAME VARCHAR2(25),
DEPTID NUMBER(11),
SALARY NUMBER(8,2),
CONSTRAINT PK_ID PRIMARY KEY(ID)
);
2.3 多字段联合主键
[CONSTRAINT <约束名>] PRIMARY KEY(字段名1,字段2.....字段N)*/
示例3
CREATE TABLE TB_EMP4 (
ID NUMBER(11),
NAME VARCHAR2(25),
DEPTID NUMBER(11),
SALARY NUMBER(8,2),
CONSTRAINT PK_ID_NAME PRIMARY KEY(ID,NAME)
);
2.4 使用ALTER TABLE 语句为数据库修改主键约束和删除约束
语法如下
ALTER TABLE <表名> ADD [CONSTRAINTS <约束名称>] PRIMARY KEY(字段名称)
ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>
示例4（主键和联合主键）
ALTER TABLE TB_EMP1 ADD CONSTRAINTS PK_ID1 PRIMARY KEY(ID,NAME);
ALTER TABLE TB_EMP1 DROP CONSTRAINTS SYS_C008827;
ALTER TABLE TB_EMP1 ADD PRIMARY KEY(ID,NAME);
2.5 查询表的约束
根据视图USER_CONSTRAINTS可以查询外键的类型
P 主键
R 外键
U 唯一
3、外键约束 FOREIGN KEY CONSTRAINT
3.1 概念
可以是空值，若不为空值时，必须等于另一个数据库中主键的某一个值,并且数据类型一致
主表:相关联字段中主键所在的表为主表
从表:相关联字段中外键所在的表为从表
3.2 语法如下
[CONSTRAINT <外键名>] FOREIGN KEY (字段) REFERENCES <主表名>(主键列)
示例5
CREATE TABLE TB_DEPT (
ID NUMBER(11) PRIMARY KEY,
NAME VARCHAR2(22),
LOCATION VARCHAR2(50)
);
CREATE TABLE TB_EMP5 (
ID NUMBER(11),
NAME VARCHAR2(25),
DEPTID NUMBER(11),
SALARY NUMBER(8,2),
CONSTRAINT ID2 PRIMARY KEY(ID),
CONSTRAINT FK_ID FOREIGN KEY(DEPTID) REFERENCES TB_DEPT(ID)
);
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TB_EMP5';
--删除数据表
DROP TABLE TB_EMP5;
--重新创建表 经过测试语法[CONSTRAINT <外键名>] 是可以忽略不写
CREATE TABLE TB_EMP5 (
ID NUMBER(11),
NAME VARCHAR2(25),
DEPTID NUMBER(11),
SALARY NUMBER(8,2),
PRIMARY KEY(ID),
FOREIGN KEY(DEPTID) REFERENCES TB_DEPT(ID)
);
3.3 修改数据表时进行外键的添加
语法如下
ALTER TABLE <表名> ADD [CONSTRAINTS <约束名称>] FOREIGN KEY(外键约束的字段名称) REFERENCE <主表名>(字段名称)[ON DELETE CASCADE]
示例6
CREATE TABLE TB_EMP6 (
ID NUMBER(11),
NAME VARCHAR2(25),
DEPTID NUMBER(11),
SALARY NUMBER(8,2),
PRIMARY KEY(ID)
);
ALTER TABLE TB_EMP6 ADD CONSTRAINTS FK_ID2 FOREIGN KEY (DEPTID) REFERENCES TB_DEPT(ID);
3.4 移除外键约束
语法如下
ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>
示例7
ALTER TABLE TB_EMP6 DROP CONSTRAINTS FK_ID2;
3.5 修改约束键名称
语法如下
ALTER TABLE <表名> RENAME CONSTRAINTS <旧约束名> TO <新约束名>
示例8
ALTER TABLE TB_EMP5 RENAME CONSTRAINTS TEST1 TO TEST2;
4、唯一约束 UNIQUE CONSTRAINT
4.1 概念
要求该列唯一,允许为空,可以确保一列或者多列
4.2 语法如下
4.2.1 在定义好的列之后直接指定唯一约束
字段名 数据类型 UNIQUE
4.2.2 在指定的所有列之后指定唯一约束
[CONSTRAINT <约束名>] UNIQUE (字段1,字段2...字段N)
多字段联合唯一 保证是两个字段值不能相同 可以AB AA BA BB,但是不能两个字段都相同
示例9
--4.2.1
CREATE TABLE TB_DEPT1 (
ID NUMBER(11) PRIMARY KEY,
NAME VARCHAR2(22) UNIQUE,
LOCATION VARCHAR2(50)
);
--测试插入两台NAME字段相同的值
INSERT INTO TB_DEPT1 VALUES(1,'1','1');
INSERT INTO TB_DEPT1 VALUES(2,'1','1');--ORA-00001:违反唯一约束条件(TEST.SYS_C008835) SYS_C008835 约束名称
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TB_DEPT1'; --SYS_C008835
INSERT INTO TB_DEPT1 VALUES(2,NULL,'1');
INSERT INTO TB_DEPT1 VALUES(3,NULL,'1');
INSERT INTO TB_DEPT1 VALUES(4,NULL,'1');
select * from TB_DEPT1;
desc TB_DEPT1;
-- 4.2.2
CREATE TABLE TB_DEPT2 (
ID NUMBER(11) PRIMARY KEY,
NAME VARCHAR2(22),
LOCATION VARCHAR2(50),
UNIQUE (NAME,LOCATION)
);
CREATE TABLE TB_DEPT3 (
ID NUMBER(11) PRIMARY KEY,
NAME VARCHAR2(22),
LOCATION VARCHAR2(50),
CONSTRAINT UNIQUE_NAEM_LOCA UNIQUE (NAME,LOCATION)
);
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TB_DEPT3';
INSERT INTO TB_DEPT3 VALUES(1,NULL,'1');
INSERT INTO TB_DEPT3 VALUES(2,NULL,'1');
INSERT INTO TB_DEPT3 VALUES(3,NULL,'2');
INSERT INTO TB_DEPT3 VALUES(4,'1','2');
INSERT INTO TB_DEPT3 VALUES(13,'1','1');
INSERT INTO TB_DEPT3 VALUES(14,'2','1');
INSERT INTO TB_DEPT3 VALUES(15,'2','2');
INSERT INTO TB_DEPT3 VALUES(16,'1','2');
select * from TB_DEPT3;
4.3 在修改数据表时添加和删除唯一约束
语法如下
ALTER TABLE <表名> ADD [CONSTRAINTS <约束名称>] UNIQUE (字段1,字段2...字段n);
ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>;
示例10
CREATE TABLE TB_DEPT4 (
ID NUMBER(11) PRIMARY KEY,
NAME VARCHAR2(22),
LOCATION VARCHAR2(50)
);
ALTER TABLE TB_DEPT4 ADD CONSTRAINTS UNIQUE_NAME_LOCATION UNIQUE(NAME,LOCATION);
ALTER TABLE TB_DEPT4 DROP CONSTRAINTS UNIQUE_NAME_LOCATION;
ALTER TABLE TB_DEPT4 ADD UNIQUE(NAME,LOCATION);
总结
UNIQUE 和 PRIMARY KEY区别
UNIQUE 数据表字段中可以拥有多个UNIQUE 字段，但是PRIMARY KEY 只能拥有一个
UNIQUE 数据表字段中可以为NULL,PRIMARY KEY不能为空
5、检查约束 CHECK
5.1 概念
规定每一列能够输入的值,
5.2 语法如下
[CONSTRAINT <约束名称>] CHECK (检查条件)
示例11
CREATE TABLE TB_EMP7 (
ID NUMBER(11),
NAME VARCHAR2(25),
GENDER VARCHAR2(2),
AGE NUMBER(2),
PRIMARY KEY(ID),
CONSTRAINT CHECK_GENDER CHECK (GENDER = '男' OR GENDER = '女')
);
CREATE TABLE TB_EMP8 (
ID NUMBER(11),
NAME VARCHAR2(25),
GENDER VARCHAR2(2),
AGE NUMBER(2),
PRIMARY KEY(ID),
CHECK (GENDER = '男' OR GENDER = '女')
);
INSERT INTO TB_EMP7 VALUES(1,'梁自祥','男',30);
INSERT INTO TB_EMP7 VALUES(2,'梁自祥',NULL,30);
INSERT INTO TB_EMP8 VALUES(1,'梁自祥',null,30);--可以null值
INSERT INTO TB_EMP8 VALUES(2,'梁自祥','哈',30);--OCR-02290:违反检查约束条件
5.3 在修改表数据时添加和删除检查约束
语法如下
ALTER TABLE <表名> ADD [CONSTRAINTS <约束名称>[ CHECK (检查条件)
ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>
示例12
CREATE TABLE TB_EMP9 (
ID NUMBER(11),
NAME VARCHAR2(25),
GENDER VARCHAR2(2),
AGE NUMBER(2),
PRIMARY KEY(ID)
);
ALTER TABLE TB_EMP9 ADD CONSTRAINTS CHECK_GENDER_1 CHECK (GENDER = '男' OR GENDER = '女');
ALTER TABLE TB_EMP9 DROP CONSTRAINTS CHECK_GENDER_1;
ALTER TABLE TB_EMP9 ADD CHECK (GENDER = '男' OR GENDER = '女');
总结
CONSTRAINTS <约束名称> 可以省略不写
主键、外键、唯一、检查约束删除语法一致，为 ALTER TABLE <表名> DROP CONSTRAINTS <约束名称>
6、非空约束 NOT NULL CONSTRAINT
6.1 概念
指定的字段不能为空,
6.2 语法如下
字段名 数据类型 NOT NULL
示例13
CREATE TABLE TB_DEPT5 (
ID NUMBER(11) PRIMARY KEY,
NAME VARCHAR2(22) NOT NULL ,
LOCATION VARCHAR2(50)
);
INSERT INTO TB_DEPT5(ID,NAME,LOCATION) VALUES(1,'2','11');
INSERT INTO TB_DEPT5(ID,NAME,LOCATION) VALUES(2,NULL,'11');--ORA-01400:无法将NULL插入
INSERT INTO TB_DEPT5(ID,NAME,LOCATION) VALUES(2,'','11');--ORA-01400:无法将NULL插入
6.3 在修改表数据时添加和删除非空约束
语法如下
ALTER TABLE <表名> MODIFY <字段名> NOT NULL
ALTER TABLE <表名> MODIFY <字段名> NULL*/
示例14
CREATE TABLE TB_DEPT6 (
ID NUMBER(11) PRIMARY KEY,
NAME VARCHAR2(22) NOT NULL ,
LOCATION VARCHAR2(50)
);
DESC TB_DEPT6;
--删除非空约束
ALTER TABLE TB_DEPT6 MODIFY NAME NULL;
INSERT INTO TB_DEPT6(ID,NAME,LOCATION) VALUES(3,NULL,'11');
--添加非空约束
ALTER TABLE TB_DEPT6 MODIFY NAME NOT NULL;--ORA-02296:无法启用非空约束，提示空值，删除数据后重新启用
INSERT INTO TB_DEPT6(ID,NAME,LOCATION) VALUES(3,NULL,'11');--ORA-01400:无法将NULL插入
