oracle学习笔记十——创建和管理表(DDL语句)

十.创建和管理表(属于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的数据类型

oracle的数据类型

注:

  rowid是个伪列:字符串,索引中存储的就是rowid
  SQL>select rowid,empno,ename from emp;
-->结果:

伪列rowid示例

(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

  -->结果:
oracle回收站的存储形式

  --结果说明
    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;

-->结果:
数据字典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唯一冲突
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 206,013评论 6 481
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 88,205评论 2 382
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 152,370评论 0 342
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 55,168评论 1 278
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 64,153评论 5 371
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,954评论 1 283
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,271评论 3 399
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,916评论 0 259
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 43,382评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,877评论 2 323
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,989评论 1 333
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,624评论 4 322
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,209评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,199评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,418评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,401评论 2 352
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,700评论 2 345

推荐阅读更多精彩内容