系列文章目录
本文对Oracle 常用的语法进行汇总
文章目录
一、Oracle 表&表字段操作:
1.1 DDL语句(数据定义语言)Create、Alter、Drop、Truncate:
1.1.1 建表:
建表:
CREATE TABLE 表名(
)
COMMENT ON COLUMN 注释;
-- 创建一个学生表
CREATE TABLE STUDENTS (
STUDENT_ID NUMBER(10) PRIMARY KEY, -- 学生ID,主键
FIRST_NAME VARCHAR2(50) NOT NULL, -- 学生名字,不允许为空
LAST_NAME VARCHAR2(50) NOT NULL, -- 学生姓氏,不允许为空
EMAIL VARCHAR2(100) UNIQUE, -- 学生电子邮箱,唯一
AGE NUMBER(3) CHECK (AGE >= 0 AND AGE <= 100), -- 学生年龄,检查范围在0到100之间
ENROLLMENT_DATE DATE, -- 学生入学日期
COMMENTS CLOB -- 学生备注信息
)
COMMENT ON COLUMN STUDENTS.STUDENT_ID IS '唯一标识学生的ID';
COMMENT ON COLUMN STUDENTS.FIRST_NAME IS '学生的名字';
COMMENT ON COLUMN STUDENTS.LAST_NAME IS '学生的姓氏';
COMMENT ON COLUMN STUDENTS.EMAIL IS '学生的电子邮箱地址,必须唯一';
COMMENT ON COLUMN STUDENTS.AGE IS '学生的年龄,必须在0到100岁之间';
COMMENT ON COLUMN STUDENTS.ENROLLMENT_DATE IS '学生的入学日期';
COMMENT ON COLUMN STUDENTS.COMMENTS IS '学生的备注信息';
注释COMMENT :
在Oracle中,COMMENT关键字不能在建表的时候直接放入来添加字段注释。Oracle的建表语句(CREATE TABLE)主要用于定义表的结构,包括字段名、数据类型、约束等,但不直接支持在创建表的同时为字段添加注释。
字段注释的添加通常是在表创建之后,通过单独的COMMENT ON COLUMN语句来实现的。这个语句允许你为已经存在的表的字段添加或修改注释,以提高数据库的可读性和可维护性。
表中字段的约束:
主键约束(PRIMARY KEY)
功能:主键用于唯一标识表中的每一行记录,一个表只能有一个主键。它可以是一个列或者多个列的组合,主键列的值不能为空(NOT NULL)且不能重复。
设置方法:
建表时设置:
例如,创建一个名为employees的表,其中employee_id为主键。
CREATE TABLE employees (
employee_id NUMBER(5) PRIMARY KEY,
employee_name VARCHAR2(100),
department_id NUMBER(3)
);
建表后添加:
如果表已经创建,要为employee_id添加主键约束,可以使用ALTER TABLE语句。
ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);
唯一约束(UNIQUE)
功能:确保表中的一列或多列组合的值在整个表中是唯一的,但与主键不同的是,唯一约束列可以为空(NULL)。不过,只能有一个NULL值,因为多个NULL在唯一约束的定义下是无法区分的。
设置方法:
建表时设置:
假设在employees表中,employee_email列需要设置为唯一。
CREATE TABLE employees (
employee_id NUMBER(5),
employee_name VARCHAR2(100),
department_id NUMBER(3),
employee_email VARCHAR2(100) UNIQUE
);
建表后添加:
若表已存在,要为employee_email添加唯一约束,可以这样操作。
ALTER TABLE employees
ADD CONSTRAINT uk_employee_email UNIQUE (employee_email);
非空约束(NOT NULL)
功能:强制列不接受空值(NULL),用于确保表中的某些列必须有值。
设置方法:
建表时设置:
在employees表中,要求employee_name列不能为空。
CREATE TABLE employees (
employee_id NUMBER(5),
employee_name VARCHAR2(100) NOT NULL,
department_id NUMBER(3)
);
建表后添加:
对于已创建的表,如果要为department_id列添加非空约束。
ALTER TABLE employees
MODIFY department_id NOT NULL;
外键约束(FOREIGN KEY)
功能:用于建立两个表之间的关联关系,确保一个表(子表)中的列值是另一个表(父表)中主键或唯一键列值的引用,维护数据的完整性和一致性。
设置方法:
建表时设置:
假设有departments表(父表)和employees表(子表),employees表中的department_id列引用departments表中的department_id列(主键)作为外键。
CREATE TABLE departments (
department_id NUMBER(3) PRIMARY KEY,
department_name VARCHAR2(100)
);
CREATE TABLE employees (
employee_id NUMBER(5),
employee_name VARCHAR2(100),
department_id NUMBER(3),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
建表后添加:
若表已存在,为employees表的department_id列添加外键约束,关联departments表的department_id列。
ALTER TABLE employees
ADD CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id);
检查约束(CHECK)
功能:用于限制列中的值必须满足一个特定的条件。这个条件可以是一个简单的表达式,比如列值大于某个数、列值在某个范围内等。
设置方法:
建表时设置:
在employees表中,假设要限制employee_age列的值必须大于等于 18。
CREATE TABLE employees (
employee_id NUMBER(5),
employee_name VARCHAR2(100),
department_id NUMBER(3),
employee_age NUMBER(3) CHECK (employee_age >= 18)
);
建表后添加:
对于已有的表,要为employee_salary列添加检查约束,要求工资大于 0。
ALTER TABLE employees
ADD CONSTRAINT ck_employee_salary CHECK (employee_salary > 0);
表中字段的默认值:
DEFAULT 进行默认值的设置;
SYSDATE是 Oracle 中的一个函数,用于获取系统当前日期。当插入新记录时,如果没有为hire_date列提供值,该列将自动填充为系统当前日期;对于department_id列,如果没有提供值,将默认插入 10。
CREATE TABLE employees (
employee_id NUMBER(5),
employee_name VARCHAR2(100),
hire_date DATE DEFAULT SYSDATE,
department_id NUMBER(3) DEFAULT 10
);
修改已有表列的默认值:
需要注意的是,这种修改默认值的操作不会影响已经存在于表中的记录,只会对之后插入的新记录生效。如果要更新现有记录的值,可以使用UPDATE语句单独进行操作。
ALTER TABLE employees
MODIFY department_id NUMBER(3) DEFAULT 20;
使用序列作为默认值(用于数字类型列)创建序列:
首先创建一个序列,例如创建一个名为employee_id_seq的序列,用于为employee_id列提供默认值。
这个序列从 1 开始,每次递增 1,没有最大值限制,并且不会循环。
CREATE SEQUENCE employee_id_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE;
设置默认值为序列值:
在创建表或者修改表时,可以将列的默认值设置为序列的下一个值。例如,在创建employees表时,设置employee_id列的默认值为序列employee_id_seq的下一个值。
这样,每次插入新记录时,如果没有为employee_id列提供值,该列将自动填充为序列employee_id_seq的下一个值。
CREATE TABLE employees (
employee_id NUMBER(5) DEFAULT employee_id_seq.NEXTVAL,
employee_name VARCHAR2(100),
hire_date DATE DEFAULT SYSDATE,
department_id NUMBER(3)
);
1.1.2 删除表:
drop table 表名
1.1.3 清空表数据
TRUNCATE TABLE 表名;
DELETE FROM 表名 WHERE 条件
1.1.4 表中字段处理:
添加字段、列
-- alter table 表名 add 字段名 数据类型
alter table CBS_SCAM_FC_ITEM_CONTAINER add REF_TYPE VARCHAR2(30);
添加注释
comment on column CBS_SCAM_FC_ITEM_CONTAINER.REF_TYPE is 'REF_TYPE';
修改字段的数据类型
-- alter table 表名 modify 字段名 新的数据类型
alter table CBS_SCAM_FC_ITEM_PRODUCT MODIFY GROSS_PROFIT_MARGIN NUMBER(20,4);
alter table CBS_SCAM_FC_ITEM_PRODUCT MODIFY CARGO_COUNT NUMBER(20,4);
修改字段的名字
-- alter table 表名 rename column 旧名 to 新名
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
删除字段
-- alter table 表名 drop column 要删除的字段名
ALTER TABLE table_name DROP COLUMN column_name;
重命名表名
-- alter table 表名 rename to 新表名
1.2 DML语句(数据操作语言)Insert、Update、Delete、Merge:
1.2.1 INSERT 语句
用于向表中插入新的数据行:
- 插入单条记录(指定列):
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);
INSERT INTO employees (employee_name, department_id) VALUES ('John Doe', 10);
- 插入单条记录(所有列):
INSERT INTO table_name VALUES (value1, value2, value3,...);
914



被折叠的 条评论
为什么被折叠?



