11. 数据表的创建与管理(DDL)


之前我们使用的都是系统提供的数据表,实际环境中许哟啊自定义数据表,但是需要注意的是,数据表的定义属于SQL的DDL(数据定义语言)类,其不受到事务的控制,也就是说DML的操作才会受到事务的控制。
在Oracle中,如果现在的事务被提交,并发生了DDL操作,所有未提交的事务将自动提交。
实际开发中,数据表的创建要么是在数据库系统安装完成时做这样的操作,或者最简单的方式就是停机维护。
例如:
在会话1中对emp表执行update操作,会开启一个事务,在会话2中也对dept表执行update操作,对emp表执行instert操作,都会开启相应的事务在会话3中在对emp表执行delete操作,也会开启一个事务。
此时在会话1中执行commit,当前事务结束,并在次执行create table操作(此操作属于ddl中的定义语言,而ddl定义语言的特点是只要发生了ddl操作,所有未提交的事务都将自动提交),因此会导致会话2和会话3中的执行的操作(不管是进行中的操作还是等待的的操作)都会直接commit。

4.1常用数据类型

创建表其实就是创建表结构
数据表是由一堆数据所组成,每一个数据都要求由自己的数据类型。
在这里插入图片描述

4.1.1常用的数据类型

1.VARCHAR2(n)
一般要保存200个字以内的数据,都采用这种类型。(Oracle叫VARCHAR2,其他数据库VARCHAR),----》姓名,地址,新闻关键字
例如:Varchar(10)----》表示此字段只能存10个字符
2.NUMBER
直接编写NUMBER表示的就是数字,包括小数和整数
1)NUMBER(n):表示最多N位整数
-----》number(2)----最多2位整数
2)NUMBER(n,m):表示m小数位,n-m的整数位
------》number(7,2)----5位整数和2位小数
3.DATE
描述日期时间
1)Oracle中的DATE类型是包含有时间数据的
------》类似于 08-SEP-81+时分秒
2)其他数据库的DATE,只有日期
-----》类似于08-SEP-81
3)MYSQL中有DATE(只显示日期)和TIME(只显示时间),同功能为DATETIME(日期+时间)

4.CLOB
描述大文本数据(4G)
可以保存大的字符文件---->字符大对象(Character large object)
5.BLOB
描述二进制数据(4G)-----(binary large object)
如:电影,音乐,文字等,一般不考虑BLOB类型,不建议使用

4.2创建数据表

4.2.1创建数据表语法

定义列名称时尽可能见名知意,列名称的定义一般建议字母开头,中文也可以,但不能使用。
CREATE TABLE 表名称(
列名称 数据类型 【default 默认值】,
列名称 数据类型 【default 默认值】,
列名称 数据类型 【default 默认值】,
列名称 数据类型 【default 默认值】,
。。。。
列名称 数据类型 【default 默认值】----->最后一行定义的列名称不能写逗号
);

4.2.2例如

1.数据表student的创建
SQL> CREATE TABLE student(
2 name VARCHAR2(5),
3 id NUMBER(4),
4 sex VARCHAR2(2)
5 ) ;
在这里插入图片描述

查看创建的表
在这里插入图片描述

查看创建的student表结构
在这里插入图片描述

2.数据表member的创建
SQL> CREATE TABLE member(
2 mid VARCHAR2(20),
3 name VARCHAR2(50) DEFAULT ‘no name’,-----没有名字默认是no name
4 age NUMBER(3),
5 salary NUMBER(10,2),
6 birthday DATE DEFAULT SYSDATE,
7 note CLOB
8 );
在这里插入图片描述

给数据表中添加数据yyn数据
在这里插入图片描述

在member表中查看数据
在这里插入图片描述

如果查看有问题,可以使用col 字段名 for a数字—修改列的大小
在这里插入图片描述

如果在创建的时候不写名字----默认就是no name

此时在创建数据表是采用的默认值只是数据本身的一种实现,在实际环境中作用不大。

4.3复制表

在之前的操作中可以自定义数据表,但是可以在数据库中支持一种简便的方法,就是复制表,可以根据已有的数据表或者查询语句来进行数据表的动态创建。
1.复制表的基本语法
CREATE TABLE + 新表名 AS 子查询
比如:CREATE TABLE yynemp AS SELECT * FROM emp;
新表名必须以字母开头
2.例如
1)创建一个名为yynemp的表
SQL> CREATE TABLE yynemp AS SELECT * FROM emp;
在这里插入图片描述

2)查询新创建的yynemp的表
SQL> SELECT * FROM tab;
在这里插入图片描述

查看yyntmp表中的数据
在这里插入图片描述

2)将部门20的雇员复制到emp1表中
SQL> CREATE TABLE emp1 as select * from emp where deptno=20;
在这里插入图片描述

以上的表操作都是根据已有的表进行了复制,甚至在做表创建的时候也可以操作复杂一些,也可以直接用自查询的结果创建。
4)将emp表结构复制到emp2中
此时只需要给定一个永远为假的条件就行,不管是什么条件只要是假的条件就可以。
在这里插入图片描述

只是将表结构复制过来,没有复制数据----》因此没有数据
在这里插入图片描述

5)创建一张数据表,将部门表数据统计结构保存在该表中
在这里插入图片描述
在这里插入图片描述

统计月信息的时候,可以采用此种模式,将长时间的操作结构保存在数据表中,方便浏览
(1)SQL> select deptno dno,count(empno) count
2 from emp
3 group by deptno;
在这里插入图片描述

(2)SQL> select d.deptno,d.dname,d.loc,temp.count
2 from dept d,(
3 select deptno dno,count(empno) count
4 from emp
5 group by deptno
6 )temp
7 where d.deptno=temp.dno(+);
在这里插入图片描述

3)SQL> create table yyntest as
2 select d.deptno,d.dname,d.loc,temp.count
3 from dept d,(
4 select deptno dno,count(empno) count
5 from emp
6 group by deptno
7 )temp
8 where d.deptno=temp.dno(+);
在这里插入图片描述

4)查看yyntest表信息
在这里插入图片描述

4.3修改表

在创建表之后,需要更改表结构,或更改删除列,可以使用alter table语句完成

4.3.1数据语法格式

使用 alter table 语句可以添加数据列,修改或删除列
1.添加数据列的语法
ALTER TABLE 表名 ADD (
新的字段 新的数据类型 【DEFAULT 默认值】,
新的字段 新的数据类型 【DEFAULT 默认值】,
新的字段 新的数据类型 【DEFAULT 默认值】,
新的字段 新的数据类型 【DEFAULT 默认值】
。。。。
);
使用此方法添加的列为数据表里的最后一列

在这里插入图片描述在这里插入图片描述

2.修改数据列的语法
ALTER TABLE 表名 MODIFY (
新的列名 新的数据类型 【DEFAULT 默认值】,
新的列名 新的数据类型 【DEFAULT 默认值】,
新的列名 新的数据类型 【DEFAULT 默认值】,
新的列名 新的数据类型 【DEFAULT 默认值】
。。。。
);
3.删除数据列的语法
ALTER TABLE 表名 DROP (列名);
在这里插入图片描述

使用DROP语句进行删除操作,那么数据表中所有数据和结构都会被删除,所有待定事务处理都会被提交。所有索引都会被删除。无法回退drop table语句

4.删除表的所有行的语法
Truncate TABLE 表名;
Truncate table 语句是另一个用于删除表的所有行并且释放该表使用的存储空间的ddl语句,如果使用truncate语句,无法回退行删除操作。
Delete table 语句也可以删除表里所有的行,但不会释放存储空间。
Truncate table 命令要快一些,删除表里的所有行不会激活表的删除触发器。
在这里插入图片描述

4.5重命名表

4.5.1重名名语句格式

RENAME 原来的表名 TO 新的表名
原来的表名可以是表,视图,序列,新的表名也可以是这些。
在这里插入图片描述

alter table 旧表名 rename to 新名
4.6面试三种删除语句的区别(delete/truncate/drop)
1.Delete :删除----》属于DML语句----》会触发事务
保证事务具有回滚的特性,有回滚的特性
2.Truncate:截断----》属于DDL语句----》不会触发事务
不存在回滚
3.Drop:删表-----》属于DDL语句-----》不会触发事务
不存在回滚
4.DML语句和DDL语句的区别是是否被事务管理
保证事务具有回滚的特性,有回滚的特性
1.语言+事务区别
1)Delete属于DML操作,具有事务特性
2)Truncate和drop 属于DDL操作,不被事务管理
2.速度区别
1)Delete被事务管理,如果执行delete操作,会产生undo信息(还原数据),记录还原数据要 保存在undo表空间的数据文件中。
Undotbs2表空间----》包含undotbs2数据文件----》数据文件保存在硬盘中
Delete操作会涉及到大量的物理IO(记录undo落盘),执行的速度很慢
比如:要删除1万行数据,那么这对应的1万行还原数据要往硬盘里存放,执行的速度很慢
2)Truncate/drop这2个操作不被事务管理,删除数据不产生undo信息,删除速度很快
3.不同角度的区别
从事务角度来考虑数据安全问题:rollback
1)Delete更安全
2)Truncate和drop更危险
从操作角度来考虑:
1)truncate和drop性能更高
2)delete性能差
4.操作对象不同
1)delete针对行记录操作
Delete from 表名 where 条件
2)truncate和drop针对表操作
Truncate table 表名
Drop table 表名
5.保留结构+释放空间的区别
1)Delete 和truncate 会删除数据,但是保留表结构
Delete from test -----》test 所有行记录被删除—》保留表结构
----》delete只删除段中数据块中的行记录
----》如果一个段有1万个数据块,使用delete删除操作,段中还是这些数据块
Truncate table test ----》test所有行记录被删除—》保留表结构
----》truncate 会回收数据表的高水位线,truncate不仅删除段中数据块中的行记录,还会释放段中的数据块
----》如果1个段有1万个数据块,使用truncate删除操作,段中保留的就是和该表结构的块,其他数据块,都没有了。
2)drop会删除数据和表结构
Drop table test ----》test表被删除
6.举例
Test表—》sys用户下存在test表
1)delete操作实验
在这里插入图片描述

3)drop操作实验
在这里插入图片描述

A)回收站(recyclebin)(必须在普通用户下操作)
Sys用户和system用户默认不使用回收站功能,删除就是删除了,
新建普通用户yyn
Create user yyn identified by 123456;
在这里插入图片描述

给普通用户yyn权限
SQL> grant dba to yyn;
在这里插入图片描述

创建表,并使用drop删除,然后在查看
在这里插入图片描述

使用show recyclebin—》查看回收站中的内容
11g引入回收站机制,删除表时,并没有真的删除表,而是进行了重命名
BIN$s4cUYbLjrUfgU8aAqMBY2Q==$0----》这个是回收站的表名
在这里插入图片描述

SQL> select count(*) from “BIN$s4cUYbLjrUfgU8aAqMBY2Q==$0”;
通过查看回收站的表名,数据还是可以统计出来,回收站的表名需要用双引号引起来
在这里插入图片描述

查看原来的test表,就是该表不存在
在这里插入图片描述

B)还原drop表的机制(flashback)
使用flashback table “回收表名“ to before drop;
SQL> flashback table “BIN$s4cUYbLjrUfgU8aAqMBY2Q==$0” to before drop;
在这里插入图片描述
查看回收站SQL> show recyclebin

在这里插入图片描述

Test表又可以查询到
在这里插入图片描述

C)清空回收站(purge)
SQL> purge recyclebin;
在这里插入图片描述

4)truncate操作实验
在这里插入图片描述

7.危险级别
第一truncate 第二drop 第三 delete
安全性排序:
Truncate < drop < delete

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值