Oracle数据库教程(第二章 表的操作)笔记

二、表的操作

2.1 数据类型

Oracle除了提供的数值类型,字符类型,日期时间类型之外,还有以下最常见的几种:

• 字符类型
• 数值类型
• 日期时间类型
• ROWID 类型

1. 字符类型

char: 用于存储固定长度的字符串。范围是1-2000字节

varchar2: 用于存储变长,而非固定长度的字符串。

3. 数值类型

number:用于存储整数或者浮点数, 精度为38位

number(p, s): p表示精度(总长度) s表示小数位置且四舍五入

int

3. 日期时间类型

DATE,可以存储日期和时间组合数据。最常用的获取日期的方法是通过SYSDATE函数,可以获取当前系统的日期值。

select sysdate from dual;

注：dual是每个用户默认都用于dual表，里面没有数据；当想显示一个常量值/固定值时，oracle必须得带上表名，这时一般使用dual表。

输出：25-APR-21

to_char函数可以将输出的日期值转换成用户自己指定的格式,该函数语法格式为：
to_char(日期值,‘yyyy-mm-dd’)

select to_char(sysdate,'yyyy-mm-dd') from dual;

输出：

2021-04-25

4. ROWID数据类型

(对数据的行做的编号，默认隐藏。每张表都有，建表时数据库系统自己加的，用户没法控制或修改，但它可方便用户修改表数据，解决一些特殊问题)

也被成为’伪列类型’, 用于在Oracle内部存储表中每条记录的物理地址, 在Oracle内部通过ROWID来定位所需记录的。

select t.rowid,t.* from emp t;

输出：

注：AAAR3s是数据库对象编号,AAE是文件标号,AAAACX是块编号,最后三位AAN是行编号

2.2 创建表

创建表:
create table 表名 (列1 数据类型 约束, 列2 数据类型 约束…)

例:创建一个学生信息表student,包括学号,姓名,性别,年龄,系别编号,班级编号,建档日期。

create table student
(
sno number(10) not null,
name varchar2(10),
sex varchar2(5),
age number,
departno varchar2(6) not null,
classno varchar2(6) not null,
regdate date
);

注：oracle查数据严格区别大小写；在tab中记录的表名为大写，若想从tab表中查询student表,必须大写。

select * from tab where tname='STUDENT';

2.3 复制表:

语法格式：
create table 表名 as select…语句

例: 创建student表的副本。
create table student2 as select * from student;

2.4 维护表

2.4.1 添加或者删除字段

格式：
alter table 表名 add(列名 数据类型)

例1:给student表添加一个phone新字段

alter table student add phone varchar2(11);

例2:给student表添加多个字段

alter table student add (address varchar2(20),email varchar2(20));

注：多个字段要用括号()括起来

例3::删除phone字段

alter table student drop column phone;

注：注意要加关键字column

例3:同时删除address,email字段

alter table student drop(address,email);

注意:删除多个列的时候不要加上 column

总结：删除字段时用drop子句，不管删除几个，字段两边都加上括号，就不用记column关键字了。

2.4.2 修改字段的数据类型

格式:alter table 表名 modify 列名 要修改的数据类型

例:把student表中phone字段的数据类型改为number，长度为11

alter table student add phone varchar2(20);

alter table student modify phone number(11);

desc student;

2.4.3 重命名/修改表名

格式:alter table 原表名称 rename to 新表名称

例1:修改表名

alter table student rename to stu;

select * from tab;

例2: 修改列名:

alter table 表名 rename column 原列名 to 新列名;

alter table s1 rename column name to sname;
desc stu;

2.5 删除表

格式:
drop table 表名 [cascade constraints];

注意:如果该表存在约束,则必须使用“cascade constraints”这个可选的子句才能删除。

例:删除stu表

drop table stdent2;
select * from tab;

--创建主表class
create table class(
classno int primary key,
classname varchar2(20)
);
--创建从表stu
create table stu(
sno int primary key,
sname varchar2(20),
classno int,
constraint fk_classno foreign key (classno) references class(classno)
);

这时用drop table不加关键字删除class表

drop table class;

会返回

drop table class
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

此时得用关键字删除带有约束的表

drop table class cascade constraints;
select * from tab;

2.6 还原表

当用户需要还原该表时,可以使用 flashback table 语句(这是一种闪回技术)来进行还原。

flashback table class to before drop;

清空删除表的残留文件(清空回收站之后无法还原表)

purge recyclebin;

注: 清空不是系统回收站，是指数据库系统软件自己的回收站，在数据库服务器上面，不在客户端电脑上面。

清空回收站之后再还原表失败输出：

SQL> drop table class cascade constraints;

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.

SQL> flashback table class to before drop;
flashback table class to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

在还原表时遇到返回空的问题：
原因是flashback没有打开。
参考链接1.
参考链接2.

• 用ARCHIVE LOG LIST 可以查看期模式状态时归档模式还是非归档模式
archive log list;

返回：

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 25
Current log sequence 27

rac 修改归档模式，需要关闭所有的实例

rac1 上执行
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 188743680 bytes
Fixed Size 1266512 bytes
Variable Size 83889328 bytes
Database Buffers 100663296 bytes
Redo Buffers 2924544 bytes
Database mounted.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

rac2 上
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

rac1 上继续
SQL> alter database archivelog;

SQL> alter database flashback on;

SQL> alter database open ;

三、数据的完整性和约束性

为了维护数据库中数据的完整性，在创建表的时候常常需要定义一些约束。
约束类型:
• 非空约束 not null
• 主键约束 primary key
• 唯一约束 unique
• 外键约束 foreign key
• 检查约束 check
• 默认约束 default

什么时候定义?
• 创建表时
• 修改表时

3.1 非空约束

• 非空约束是限制必须为某个列提供值。

• 空值(null)是不存在的值,它既不是数字,也不是空字符串,而是不存在,未知的情
况。

例:

1. 创建表时添加非空约束

--创建Books表，要求BookNo(图书编号)，BookName不为null
create table Books
(
BookNo number(10) not null,
BookName varchar2(10) not null,
author varchar2(5),
price number
);

2. 创建完成后修改非空约束
   alter table modify

例:
为Books表中price字段设置非空约束

alter table Books modify price not null;

3. 删除非空约束(改某个列为空)

alter table Books modify price null;

3.2 主键约束

• 主键约束用于唯一的标识表中每一行记录
• 在一个表中,最多有一个主键约束,
• 可以由两个或者两个以上列来组成(联合主键)。
• 主键约束默认具有非空约束的特性。

1. 创建表的同时添加主键约束

例:创建表book,包括name,no,price,author,其中no为主键约束。(同Mysql)

create table book1
(
No number(10) ,         --后面直接加primary key也正确，但无法像sql server借助工具看到约束名
Name varchar2(10),
author varchar2(5),
price number,
constraint PK_NO primary key(no)  --最后写约束，区别sql server创建表的同时添加约束
);

2. 创建表时未定义主键约束，可使用以下格式来添加主键约束
   alter table 表名 add constraint 约束名字 primary key(列名)

例:

alter table Books add constraint PK_B primary key(BookNo);

3. 删除主键约束
   alter table 表名 drop constraint 约束的名字

例:
删除Books表的主键约束

alter table Books 
drop constraint PK_B;

3.3 唯一约束

• 唯一性约束(unique)
• 强调所在的列不能有相同的值,可以有空值。
• 格式:constraint 约束名 unique

1. 创建表的同时添加唯一约束

例:
创建一个会员Members,并要求为该表的QQ列定义一个唯一性约束

--创建一个会员Members,并要求为该表的QQ列定义一个唯一性约束
create table Members
(
MemNo number(4) primary key,
MemName varchar2(20) not null,
Phone varchar2(20),
Email varchar2(30),
QQ varchar2(20) constraint QQ_UK unique
);

2. 给现有的列添加唯一约束
   alter table 表名 add constraint 约束的名字 unique(列名)

例:
给Member表Email列添加唯一约束

alter table Members add constraint UK_E unique(Email);

3. 删除email列的唯一约束
   alter table 表名 drop constraint 约束名
   例:

alter table Members drop constraint UK_E;

3.4 外键约束

• 外键约束使用两个表进行关联
• 外键是指==“当前表”引用“另外一个表”==(即被引用表)的某个列
• 被引用的列必须具有主键约束或者唯一约束

1. 添加外键约束
   例：
   利用Members表创建新表m1

create table m1
as select * from Members;

设置m1的QQ列引用Members表的QQ列。

alter table m1
add constraint FK_QQ foreign key(QQ) references members(QQ);

2. 创建表的时候创建约束

create table Mem
(
no number not null,
QQ varchar2(20) ,
constraint FK_Q foreign key(QQ) references MEMBERS(QQ)
);

查看某一个表中约束的名字

1. select constraint_name, constraint_type from user_constraints where table_name=‘大写的表名’;
2. select constraint_name from user_cons_columns where table_name=‘大写表名’ and column_name=‘大写列名’;

constraint_type说明:
• C (check constraint on a table) C 表示 CHECK 约束。
• P (primary key) P 表示主键
• U (unique key) U 表示唯一
• R (referential integrity) R 表示引用(外键)
• V (with check option, on a view)V表示视图
• O (with read only, on a view)O表示只读

例1：
查看表m1中约束的名字

select * from user_constraints where table_name='m1'

例2

select constraint_name from user_cons_columns where table_name='MEMBERS' and column_name='MEMNO';

输出：

CONSTRAINT_NAME
------------------------------
SYS_C0011115

注：这时表名和列名都得大写。否则返回no rows selected。

3.5 检查约束

1. 创建表同时创建约束

例:创建一个书本信息表,给价格添加check约束

create table bookinfo(
bookid number,
bookname char,
price number,
author char,
constraint CK_price check(price>=10 and price<=100)
);

2. 添加check约束

alter table 表名 add constraint 约束名 check (条件)

例:
把表中bookid添加一个check约束

alter table bookinfo 
add constraint CK_ID check(bookid>1 and bookid<10);

3.6 默认约束

1. 创建表时创建默认约束

例：
创建一个person1表,年龄默认设置为18

create table person1
(
name varchar2(10) not null,
id number(10),
sex varchar2(10),
age number(5) default 18  --
)

2. 添加默认约束

alter table 表名 modify 列名 default 默认值

例:
对person1表的sex加入默认约束

alter table person1 modify sex default 'male';