8、Oracle中的创建和管理表（DDL）
最近项目要用到Oracle，奈何之前没有使用过，所以在B站上面找了一个学习视频，用于记录学习过程以及自己的思考。
视频链接：
【尚硅谷】Oracle数据库全套教程，oracle从安装到实战应用
如果有侵权，请联系删除，谢谢。
学习目标：
- 描述主要的数据库对象
- 创建表
- 描述各种数据类型
- 修改表的定义
- 删除，重命名和清空表
1、常见的数据库对象
| 对象 | 描述 |
|---|---|
| 表 | 基本的数据存储集合，由行和列组成。 |
| 视图 | 从表中抽出的逻辑上相关的数据集合。 |
| 视图 | 提供有规律的数值。 |
| 索引 | 提高查询的效率 |
| 同义词 | 给对象起别名 |
1.1 Oracle 数据库中的表
用户定义的表:
- 用户自己创建并维护的一组表
- 包含了用户所需的信息
如：SELECT * FROM user_tables;查看用户创建的表
数据字典:
- 由 Oracle Server 自动创建的一组表
- 包含数据库信息
1.2、查询数据字典
- 查看用户定义的表.
select table_name from user_tables;
- 查看用户定义的各种数据库对象
SELECT DISTINCT object_type FROM user_objects;
结果：
OBJECT_TYPE |
---------------+
INDEX PARTITION|
TABLE PARTITION|
SEQUENCE |
TRIGGER |
TABLE |
INDEX |
VIEW |
- 查看用户定义的表, 视图, 同义词和序列
select * from user_catalog;
结果：
TABLE_NAME |TABLE_TYPE|
------------------------------+----------+
APWJ06 |TABLE |
APWJ06_SEQ |SEQUENCE |
BIN$F+5ZVUIVvVjgYBGsAgAo5Q==$0|TABLE |
BIN$Fgw30nMia/jgYBGsBgBYGQ==$0|TABLE |
BIN$Fzf9Yeh1EzvgYBGsAgA5ag==$0|TABLE |
BIN$Fzf9Yeh8EzvgYBGsAgA5ag==$0|TABLE |
BIN$Fzf9YehnEzvgYBGsAgA5ag==$0|TABLE |
BIN$HQdt+RevHwzgYBGsAgBlgw==$0|TABLE |
2、命名规则
表名和列名:
- 必须以字母开头
- 必须在 1–30 个字符之间
- 必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
- 必须不能和用户定义的其他对象重名
- 必须不能是Oracle 的保留字
3、CREATE TABLE 语句
必须具备:
- CREATE TABLE权限
- 存储空间
必须指定:
- 表名
- 列名, 数据类型, 尺寸
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
3.1、创建表
- 语法
CREATE TABLE dept (deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.
- 确认
describe dept;
不一定能够查询，使用 DBeaver 不能够正确查询，会报错。
3.2、数据类型
3.3、使用子查询创建表
使用 AS subquery 选项，将创建表和插入数据结合起来
- 指定的列和子查询中的列要一一对应
- 通过列名和默认值定义列
CREATE TABLE table
[(column, column...)]
AS subquery;
复制现有的表：
create table emp1 as select * from employees;
create table emp2 as select * from employees where 1=2;
--创建的emp2是空表
使用子查询创建表举例
CREATE TABLE dept80
AS SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date FROM employees WHERE department_id = 80;
Table created.
4、ALTER TABLE 语句
使用 ALTER TABLE 语句可以:
- 追加新的列
- 修改现有的列
- 为新追加的列定义默认值
- 删除一个列
- 重命名表的一个列名
4.1、ALTER TABLE 语句
使用 ALTER TABLE 语句追加, 修改, 或删除列的语法.
-- 追加
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);
-- 修改
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
-- 删除
ALTER TABLE table
DROP COLUMN column_name;
-- 重命名
ALTER TABLE table_name RENAME COLUMM old_column_name
TO new_column_name
4.2、追加一个新列
使用 ADD 子句追加一个新列
ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
Table altered.
新列是表中的最后一列
4.3、修改一个列
可以修改列的数据类型, 尺寸和默认值
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
Table altered.
ALTER TABLE dept80
MODIFY (salary number(9,2) default 1000);
Table altered.
对默认值的修改只影响今后对表的修改
4.4、删除一个列
使用 DROP COLUMN 子句删除不再需要的列.
ALTER TABLE dept80
DROP COLUMN job_id;
Table altered.
4.5、重命名一个列
使用RENAME COLUMN [table_name] TO子句重命名列
ALTER TABLE dept80
RENAME COLUMN job_id TO id;
Table altered.
5、删除表
数据和结构都被删除- 所有正在运行的相关事务被提交
- 所有相关索引被删除
DROP TABLE 语句不能回滚
DROP TABLE dept80;
Table dropped.
6、清空表
TRUNCATE TABLE 语句:
- 删除表中所有的数据
- 释放表的存储空间
TRUNCATE TABLE detail_dept;
Table truncated.
- TRUNCATE语句
不能回滚 - 可以使用 DELETE 语句删除数据,可以回滚
对比：
delete from emp2;
select * from emp2;
rollback;
select * from emp2;
7、改变对象的名称
执行RENAME语句改变表, 视图, 序列, 或同义词的名称
RENAME dept TO detail_dept;
Table renamed.
必须是对象的拥有者
总结
以上这些DDL的命令，除了操作外，皆不可回滚！
创建和管理表(DDL) CRAETE TABLE /TRUNCATE TABLE /ALTER TABLE /REANME...TO/DROP TABLE ..操作完以后，自动commit;
所以，rollback对其操作，没有效果
1.创建表
1）直接创建
create table emp1(
name varchar2(20),
salary number(8,2)default 1000,
id number(4),
hire_date date
);
2）通过子查询的方式创建
create table emp2
as
select last_name name,employee_id id,hire_date
from employees;
或者
create table emp2
as
select last_name name,employee_id id,hire_date
from employees
where department_id = 80;/where 1=2;
2.修改表
1)增加新的列
alter table emp1
add(birthday date)
2)修改现有的列
alter table emp1
modify(name varchar2(25) default 'abc')
3)重命名现有的列
alter table emp1
rename column salary to sal;
4)删除现有的列
alter table emp1
drop column birthday;
3.清空表中的数据(与delete from table_name区分开)
truncate table emp2;
4.重命名表
rename emp2 to emp3;
5.删除表
drop table emp3;
