Oracle 数据库学习笔记（二）

一、表空间的基本操作

1.1 基础知识准备

在 Oracle 数据库中，表空间的大小分配如下
段 — 区 — 块

表空间为逻辑结构，数据文件为物理结构

1.2 如何创建一个表空间

这段代码的意思是：创建一个表空间，并命名为 my_mobile，表存储的位置为 d盘下的 tableSpace 文件，并命名为 my_module.dbf 文件，大小为 100M，空间满后会自己增加，每次增加 32M，大小没有限制

create tablespace my_mobile
datafile 'd:/tableSpace/my_mobile.dbf' 
size 100m autoextend on next 32m maxsize unlimited;

1.3 如何删除表空间？

表空间是一个文件，真真实实存存放在物理硬盘殇，如果我们不需要该表空间的时候，就可以对其进行删除处理。但是我们不能直接删除表空间（.dbf）文件，这样会报错。因此我们需要先使用命令行删除在 数据库中的表空间，然后才可以在物理磁盘中删除表空间文件

因此正确的删除表空间的步骤如下：

1. 使用命令行删除表空间
2. 然后才可以删除物理磁盘上的表空间

drop tablespace my_mobile

drop 是个很万能的语句，它还能够直接清空表字段中的数据

二、表的相关操作

2.1 创建表

语法

create table .表名称(
	字段名称1 字段类型 [default 默认值],
	字段名称2 字段类型 [default 默认值],
	.....
	字段名称n 字段类型 [default 默认值]
)

eg：创建一个学生类型的信息表

create table tb_student(
       stuId number, -- 学生的ID
       stuName nvarchar(8), -- 学生的姓名
       stuIdCard number(18,0), -- 学生身份证
       stuGender nvarchar2(2) default '男', -- 学生性别，默认为 0
       stuAge number(3), -- 学生的年龄
)

2.2 插入数据 insert

语法：

1. 形式一

insert into 表名(参数1，参数2，参数3...参数n) values (参数1对应的值，参数2对应的值，....,参数n对应的值);

2. 形式二

insert into 表名 values(参数1对应的值，参数2对应的值....，参数n对应的值)

两者的区别如下：

1. 前者是指定类型插入，可以选择性的插入指定的数据
2. 后者必须插入表所有属性的值，也就是一个都不能漏掉

eg：以上面的学生表为例

insert into tb_student (stuId,stuIdCard,stuGender,stuAge,tid) values(20171111034,320101197905156354,'男',19,2)

2.3 表创建（约束）

在数据库的设计中，约束是必不可少的支持，使用约束可以更好的保证数据库中数据的完整性

举几个例子：

1. 人的身份证有 18 位，这个不加约束的话，有的人乱填，就会出现很多问题
2. 一个人的性别分为 ‘男’，‘女’，‘未知’，这也不能乱填
3. 每当学校新生入学的时候，总是会必不可免的出现有人同名的情况，但是他们的身份证不一样，所以可以另外添加一个学号属性，这样这个学生就是唯一的啦
4. 还有人的年龄一般不会超过 150岁，所以插入数据的时候也需要加一个判断，年龄是否超出呢？是不是

约束的分类：

1. 主键约束：主键是唯一的表示，本身不能为空（比如在学校你想确认一个同学，可以通过他的学号定位到他）
2. 唯一约束：在一个表中一次只允许建立一个主键约束，而其他列不希望出现重复值的情况下，可以使用唯一约束。一张表中的唯一约束可以有多个，并且允许有空值
   ，空值只能有一个
3. 检查约束：检查一列的内容是否合格
   • 例如：年龄输入加一个限制，在0 ~ 150之间
   • 例如：性别，只能是男或者女
4. 非空约束：字段里面的内容不能为空
5. 外键约束：在两张表中进行约束操作，这就要区别一下父表和子表的区别啦，父表的 ID 可以对 子表的 ID 具有约束租用

2.3.1 主键约束

关键字：primary key

主键约束一般都是在 id 上使用，而且本身已经默认了内容不能为空且唯一
主键约束可以在建立表的时候指定

eg：还是以上面创建学生表为例

create table tb_student(
       stuId number primary key, -- 学生的ID，学生的 ID 作为主键，而且不为空
       stuName nvarchar(8), -- 学生的姓名
       stuIdCard number(18,0), -- 学生身份证
       stuGender nvarchar2(2) default '男', -- 学生性别，默认为 0
       stuAge number(3), -- 学生的年龄
		-- 另一种创建 主键的方式
		-- constraint tb_student_stuId_pk primary key(stuId) pk 是我另外给主键创建的一个名称
)

这个在插入数据的时候，才会出来问题，如果插入两个数据的 ID 相同就会报错

2.3.2 非空约束

关键字：not null

使用非空约束，表示一个字段的内容不允许为空，即：插入数据的时候必须插入内容

eg：只用在每个列属性后面加上 not null 就可以啦

create table tb_student(
       stuId number primary key, -- 学生的ID，学生的 ID 作为主键，而且不为空
       stuName nvarchar(8) not null, -- 学生的姓名
       stuIdCard number(18,0) not null, -- 学生身份证
       stuGender nvarchar2(2) default '男', -- 学生性别，默认为 0
       stuAge number(3) not null -- 学生的年龄
)

2.3.3 唯一约束

关键字：unique

表示一个字段中的内容是唯一的，其他列不允许重复

eg：使用了这个关键字就要好好注意一下

create table tb_student(
       stuId number primary key, -- 学生的ID，学生的 ID 作为主键，而且不为空
       stuName nvarchar(8) not null, -- 学生的姓名
       stuIdCard number(18,0) unique not null, -- 学生身份证
       stuGender nvarchar2(2) default '男', -- 学生性别，默认为 0
       stuAge number(3) not null -- 学生的年龄
		-- 给唯一约束 创建别名
		-- 格式 constraint 表名_列名_uk unique (列1,列2),
		-- constaint tb_student_stuName_uk unique(stuName)
)

2.3.4 检查约束

关键字：check

使用检查约束来判断一个列中插入的内容是否合法，例如：年龄的取值范围，性别的取值范围

注意：

1. 可以使用 where(条件判断) 表达式后面就可以使用 check

eg：性别 和 年龄的检查约束

create table tb_student(
       stuId number primary key, -- 学生的ID，学生的 ID 作为主键，而且不为空
       stuName nvarchar(8) not null, -- 学生的姓名
       stuIdCard number(18,0) unique not null, -- 学生身份证
		--- test
       stuGender nvarchar2(2) default '男' check(stuGender in ('男'，'女','不详')), -- 学生性别，默认为 0
       stuAge number(3) not null check (stuAge between 0 and 150)-- 学生的年龄
		-- 检查约束另外起名
		-- constraint tb_student_stuAge_ck check (stuAge between 0 and 150)
		--  constraint tb_student_stuGender_ck check (stuGender in ('男'，'女','不详'))
)

2.3.5 外键约束

关键字：

1. foreign key
2. REFERENCES

主-外键约束是针对两张表的约束

有一张表 TA ，且表有主键，另外一张表中的某一列的数据完全取自于 TA，此时就 TB 表中该列为外键列，添加主外约束来限制它 （TB 外键列上添加）

是不是有点绕口，我换个说法给大家

有两张表 A和B，A 表中的主键记为 AId，表B 的主键记为 BID，表 B 的 BID 加一个 外键，引入 AId，也就是说，以后 Aid 中的所有值，BID 才可以使用

eg：

1. 先创建一个 表A， person 类型

CREATE TABLE person (
	pid VARCHAR2（18），
	name VARCHAR2（200）NOT NULL，
	age NUMBER（3）NOT NULL，
	birthday DATE，
	sex VARCHAR2（2）DEFAULT'男”，
	CONSTRAINT person_pid_pk PRIMARY KEY（pid），
	CONSTRAINT person_name_uk UNIQUE（name），
	CONSTRAINT person_age_ck CHECK（age BETWEEN O AND 150）,
	CONSTRAINT person_sex_ck CHECK（sex IN（"男，’女，“中））
）；

2. 创建一个表 B

CREATE TABLE book (
	bid NUMBER PRIMARY KEY NOT NULL, 
	bname VARCHAR(30), 
	bprice NUMBER(5,2), 
	pid VARCHAR2(18) REFERENCES person(pid) -- 通过 references 引入外键
	-- constraint person_book_pid_fk foreign key(pid) references person(pid)
);

这样就完成了主外键的操作，但是有几点需要注意

1. 主表先创建，然后插入数据，再创建附表（主外键）才可以创建
2. 如过要删除主表中的某一条数据，如果此列的数据被附表的主外键引用，则不能删除
3. 希望一个表中数据在删除时，可以自动删除其对应的子表记录，则可以使用级联删除操作

总结：

CREATE TABLE person(
 	pid VARCHAR2（18），
 	name VARCHAR2（200）NOT NULL，
 	age NUMBER（3）NOT NULL，
 	birthday DATE，
 	sex VARCHAR2（2）DEFAULT'男，
 	classid CONSTRAINT person_pid_pk PRIMARY KEY（pid），
 	CONSTRAINT person_name_uk UNIQUE（name），	 
 	CONSTRAINT person_age_Ck CHECK（age BETWEEN O AND 150），
 	CONSTRAINT person_sex_ck CHECK（sex IN‘男’，女，”中”））
）；
CREATE TABLE book
（
	bid NUMBER PRIMARY KEY NOT NULL，
	bname 	VARCHAR（30），bprice NUMBER（5，2），
	pid VARCHAR2（18）CONSTRAINT person_book_pid_fk FOREIGN KE（pid）REFERENCES person（pid）

2.4 序列

2.4.1 创建序列

序列是一个计数器，它并不会与特定的表相关联。通过创建 Oracle 序列可以实现表的主键自增。序列的用途一般用来填充主键和计数

特性

• 自动填充唯一的数值
• 共享对象
• 主要用于提供主键值
• 代替应用代码
• 将序列装入内存可以提高访问效率

语法：

create sequence 序列名
	increment by n, -- 步长，每次增加的数据
	start with n, -- 从 n 开始计数
	maxvalue n| nomaxvalue, -- 最大值、无最大值
	minvalue n| nominvalue, -- 最小值、无最小值
	cycle | nocycle -- 循环序列，不循环
	cache n | nocache; -- 缓冲池，将数据加载到内存，提高性能，但是容易损失数据

创建序列：

从 100 开始，每次增加10个步长，最大值 1w，不循环，不进入缓冲池

create sequence dept_deptid_seq
	increment by 10
	start width 100
	maxvalue 10000
	nocache
	nocycle

2.4.2 查询序列

nextval 和 currval 伪例

• nextval 返回序列中下一个有效的值，任何用户都可以引用
• currval 中存放序列的当前值
• nextval 应在 currval 之前指定，二者应同时有效

select 序列名.currval from dual --dual 是虚拟表，不存在的表

2.4.3 修改序列的值

使用 alert 关键字进行修改

修改序列的增量，最大值，最小值，循环选项，或是否装入内存（不能修改序列的初始值）

alert sequence 表名
	increment by 20
	maxvalue 99999
	nocache
	nocycle

修改限制：

• 必须是序列的拥有者或对序列有 alert 权限
• 只有将来的序列值会被改变
• 改变序列的初始值只能通过删除序列之后重建序列的方法实现

2.4.4 序列问题

• 将序列值装入内存可提高访问效率
• 序列会在下面的情况出现裂缝
  • 回滚
  • 系统异常
  • 多个表同时使用同一个序列
• 如果不将序列的值装入内存（nocache），可使用表 user_sequences 查看当前的有效值

2.4.5 删除序列

drop sequence 表名;
- sequence dropped

2.5 复制

1. 复制一个一模一样的数据
   select * from emp1 as select * from emp

赋值结构不复制数据
select * from emp1 as select * from emp where 1 == 2

三、作业总结

3.1 创建一个学生表student 学生ID (主键) 学生身份证(唯一不允许为空) 学生的性别默认男(男，女) 学生的年龄(10-30) 老师ID(外键)

create table tb_student(
       stuId number primary key, -- 主键，内容默认，且不为空
       stuIdCard number(18,0) unique not null, -- 唯一 不为空
       stuGender nvarchar2(2) default '男',
       stuAge number(3) not null check(stuAge between 10 and 30),
       pid number(10) REFERENCES tb_teacher(tid), -- 主外键
       constraint tb_student_stuGender_ck check(stuGender in('男','女'))
)

3.2 创建一个老师表teacher 老师ID(主键) 老师身份证(唯一不允许为空) 老师的性别默认女（男,女） 老师的年龄(20-70)

create table tb_teacher(
       tid number(10) primary key, -- 主键
       tIdCard number(18,0) unique not null, -- 身份唯一不为空
       tSex nvarchar2(2) default '女',
       tage number(3) check(tage between 20 and 70),
       constraint tb_teacher_tSex_ck check(tSex in('男','女'))
)

3.3 插入学生的信息5条，插入老师的信息3条，

insert into tb_student (stuId,stuIdCard,stuGender,stuAge,tid) values(20171111034,320101197905156354,'男',19,2)
insert into tb_student (stuId,stuIdCard,stuGender,stuAge,tid) values(20171111024,120101197905156334,'女',19,3)
insert into tb_student (stuId,stuIdCard,stuGender,stuAge,tid) values(20171111014,420201198905156354,'男',20,4)
-- 教师数据中没有对应的 tid 值，所以这里是无法插入进去的
insert into tb_student (stuId,stuIdCard,stuGender,stuAge,tid) values(20171111004,510101197905157354,'女',21,8)
insert into tb_student (stuId,stuIdCard,stuGender,stuAge,tid) values(20171111044,320101192605156354,'男',19,9)

insert into tb_teacher (tid,tIdCard,tSex,tage) values(2,420201198905156354,'女',27)
insert into tb_teacher (tid,tIdCard,tSex,tage) values(3,420201198905156355,'女',37)
insert into tb_teacher (tid,tIdCard,tSex,tage) values(4,420201198905156356,'男',26)

-- 检验
select * from tb_student
select * from tb_teacher

3.4 删除老师和学生的信息 ------------------ ok

3.4.1 删除学生的所有信息

drop table tb_student

3.4.2 删除老师的所有信息

drop table tb_teacher

3.5 修改1和2的约束为自定义的约束名称

-- 学生 id 的约束
-- primary 约束
CONSTRAINT  tb_student_stuId_pk1  PRIMARY KEY(stuId)
-- unique 约束
CONSTRAINT tb_student_stuIdCard_uk UNIQUE(stuIdCard) ,
-- check
CONSTRAINT tb_student_stuAge_ag CHECK(stuAge BETWEEN 10 AND 30) 
constraint tb_student_stuGender_ck check(stuGender in('男','女'))

-- 老师 id 的约束
-- primary
CONSTRAINT  tb_teacher_tid_pk2  PRIMARY KEY(tid)
-- unique
CONSTRAINT tb_teacher_tIdCard_tk UNIQUE(tIdCard) 
-- check
CONSTRAINT tb_teacher_tage_ag1 CHECK(tage BETWEEN 20 AND 70) 
constraint tb_teacher_tSex_ck check(tSex in('男','女'))

3.6 修改级联删除

create table tb_student(
       stuId number primary key, -- 主键，内容默认，且不为空
       stuIdCard number(18,0) unique not null, -- 唯一 不为空
       stuGender nvarchar2(2) default '男',
       stuAge number(3) not null check(stuAge between 10 and 30),
       pid number(10) REFERENCES tb_teacher(tid), -- 主外键
       constraint tb_student_stuGender_ck check(stuGender in('男','女')),

-- 级联删除操作
       constraint tb_teacher_tb_student_pid fk foreign key(pid) references tb_teacher(tid) on delete cascade

-- 设置null 操作
	  constraint tb_teacher_tb_student_pid fk foreign key(pid) references tb_teacher(tid) on delete set null 
)

3.7 创建一个序列按2来自增，没有最大值,缓存的数量为30

create SEQUENCE seq_test
INCREMENT BY 2
start width 1
nomaxvalue 
cache 30