十.创建和管理表(属于DDL语句)
1.DDL语句
(1)DDL语句主要是操作数据库对象(创建,删除,修改)
(2)数据库对象(本教程涉及的数据库对象):表, 视图,序列,索引,同义词,存储过程,存储函数,触发器,包,包体,数据库链路,快照
2.数据库对象命名规则(表名和列名)
(1)必须以字母开头
(2)必须在 1–30 个字符之间
(3)必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
(4)必须不能和用户定义的其他对象重名
(5)必须不能是Oracle 的保留字
(6)Oracle默认存储是都存为大写
(7)数据库名只能是1~8位,datalink可以是128位,和其他一些特殊字符
3.管理数据表
(1)create table语句
--create table要件及语法
1)必须具备:
•CREATE TABLE权限
•存储空间
2)必须指定:
•表名
•列名, 数据类型, 数据类型的大小
3)语法
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
注:查询其他用户的表(前提得有权限)
可以看到语法中有schema.table就是为了查询其他用户的表。因为其他用户的表不属于本用户的空间,因此如果要查询其他用户下的表,要使用其他用户的用户名作为前缀。
--create table示例
1)创建表
SQL>create table test1(
tid number,
tname varchar2(20)
);
2)创建表的同时为列设置默认值
SQL>create table test2 (
tid number,
tname varchar2(20),
hiredate date default sysdate
);
--oracle的数据类型
注:
rowid是个伪列:字符串,索引中存储的就是rowid
SQL>select rowid,empno,ename from emp;
-->结果:
(2)使用子查询创建表
--使用子查询创建表的要件及语法
1)使用 AS subquery 选项,将创建表和插入数据结合起来
2)指定的列和子查询中的列要一一对应
3)通过列名和默认值定义列
4)语法
CREATE TABLE table
[(column, column...)]
AS subquery;
--子查询建表示例:
1)通过子查询创建表
SQL>create table emp20
as select * from emp where deptno=20;
2)创建表:员工,姓名,月薪,奖金,年薪, 年收入
SQL>create table empincome
as
select empno,ename,sal,comm,sal*12 annsal,sal*12+nvl(comm, 0) annincome from emp;
(3)修改表(alter)——对列操作:追加新列,修改列,删除列,重命名列
1)追加新列(图像,blob存2进制)
--语法:
ALTER TABLE table_name
ADD (column_name datatype [DEFAULT expr]
[, column_name datatype]...);
--示例:
SQL>alter table test2 add image blob;
--说明:
新列是表中的最后一列
2)修改列
--语法:
ALTER TABLE table_name
MODIFY (column_name datatype [DEFAULT expr]
[, column_name datatype]...);
--示例:
SQL>alter table test2 modify tname varchar2(40);
--说明:
可以修改列的数据类型, 尺寸, 和默认值
对默认值的修改只影响今后对表的修改
3)删除列(drop 后有个关键字column)
--语法:
--删除一列
ALTER TABLE table_name
DROP column (column_name);
--删除多列
ALTER TABLE table_name
DROP (column_name
[, column_name datatype]...);
--示例:
SQL>alter table test2 drop column image;
--说明:
很奇怪的现象,删除列时,在单列中要加关键字COLUMN,然而再删除多列的时候,不能加COLUMN关键字。
4)重命名——列名(rename后有个关键字column)
SQL>alter table test2 rename column tname to username;
注:
1.追加、修改列,列名前不用加关键词column
2.删除、重命名列,列名前要加关键词column
(4)修改表(alter)——对表操作:重命名表,删除表
1)重命名——表名(rename后
--语法:
RENAME table_name_old TO table_name_new;
--示例:
SQL>rename test2 to test2new;
--说明:
执行RENAME语句改变表, 视图, 序列, 或同义词的名称
必须是对象的拥有者
2)删除表 drop table
--语法:
drop table table_name;
--示例:
SQL>drop table test2new;
--说明:
- drop table并没有把表删掉,而是给表改了个名字放在oracle的回收站
- 数据和结构都被删除
- 所有正在运行的相关事物被提交
- 所有相关索引被删除
- DROP TABLE 语句不能回滚,但是可以闪回
补充:oracle回收站
--1.查看回收站-->结果:SQL>show recyclebin
--结果说明
original name是可以重复的
recyclebin name是不可以重复的
--2.清空回收站(就真正的删除表了)SQL>purge recyclebin
--3.所以,要删除一张表应该使用
SQL>drop table test2new purge;
说明:
加上purge,表示彻底删除一张表不经过回收站
--4.怎么把表从回收站拿回来?--闪回的内容,后面的教程再说
--5.被删除(drop)的表,可以通过回收站里的名称查看数据?
把表放到回收站,就不能通过原名查看了,但我们说了,drop table只是把表改了个名字,并添加到回收站,那么可以通过回收站的名字访问表吗?答案:可以,要加双引号,因为里面有特殊符号。select * from "BIN$cXp8ouZCQRq58B5OjRMFCg==$0"
3)truncate table:(是DDL语句)清空表
说明:
- 删除表中所有的数据
- 释放表的存储空间
- TRUNCATE语句不能回滚,不能闪回
- 可以使用 DELETE 语句删除数据,可以回滚,可以闪回。在oracle中经过优化,效率上比truncate高。
4.约束
(1)约束概述
--约束是啥?
约束是表一级的限制
--约束的作用
如果存在依赖关系,约束可以防止错误的删除数据
--约束的类型:(5种)
not null
unique
primary key
foreign key
check
(2)约束规则
1)用户可以自定义约束,也可以使用Oracle Server的sys_cn格式命名约束
2)约束创建的时机:
创建表的时候,同时创建约束
表结构创建完成后
3)约束可以定义在列一级,或者是表一级
4)通过数据字典(user_constraints)查看约束
--示例:
SQL> select owner,constraint_name,constraint_type,table_name
from user_constraints;
-->结果:(3)check约束
--示例:
SQL>create table test3(
tid number,
tname varchar2(20),
gender varchar2(4) check (gender in ('男', '女')),
sal number check (sal >0)
);
--插入示例:
--对的:
SQL>insert into test3 values(1, 'Tom', '男',1000);
--错的:
SQL>insert into test3 values(2, 'Mary','假',-1000);
SQL>insert into test3 values(2, 'Mary','女',-1000);
--说明:
- 定义每一行记录所必须满足的条件
- 下面的表达式可以使用在check约束中:
引用CURRVAL, NEXTVAL, LEVEL, 和ROWNUM
调用SYSDATE, UID, USER, 和USERENV 函数
另一个表的查询记录(子查询)
(4)外键约束
- FOREIGN KEY: 在子表中,定义了一个表级的约束
- REFERENCES: 指定表和父表中的列
- ON DELETE CASCADE: 当删除父表时,级联删除子表记录
- ON DELETE SET NULL: 将子表的相关依赖记录的外键值置为null(建议)
(5)创建一张表,包含着5种约束,并给约束起个名字
SQL>create table myperson(
pid varchar2(18) constraint myperson_pk primary key,
pname varchar2(40) constraint myperson_name not null,
gender varchar2(4) constraint myperson_gender check(gender in ('男', '女')),
sal number constraint myperson_sal_min check (sal>0)
constraint myperson_sal_notnull not null,
email varchar2(40) constraint myperson_email unique,
deptno number constraint myperson_fk references dept(deptno) on delete set null
);
注意:
外键参考的列必须是别的表的主键
--插入示例
--对的:
SQL>insert into myperson values('p001','Tom','男', 1000,'tom@126.com',10);
--下面在执行时都是错的
SQL>insert into myperson values('p001','Tom','男', 1000,'tom@126.com',10);--主键冲突
SQL>insert into myperson values('p002','Tom','男', 1000,'tom@126.com',10);--email唯一冲突