Oracle 数据库学习笔记（二）

Gorit

发布于 2021-12-09 14:11:40

9400

发布于 2021-12-09 14:11:40

文章被收录于专栏：Gorit 带你学全栈系列

Oracle 数据库学习笔记（二）

一、表空间的基本操作

1.1 基础知识准备

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

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

1.2 如何创建一个表空间

基本语法：

创建临时表空间或者永久表空间

CREATE [TEMPORARY] TABLESPACE
tablespace_ name
TEMPFILE |DATAFILE 'xx.dbf' SIZE xX

示例： 下面段代码的意思是：创建一个表空间，并命名为 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 修改表空间状态

设置联机或脱机状态

ALTER TABLESPACE tablespace_ name
ONLINE | OFFLINE;

设置只读或可读写状态

ALTER TABLESPACE tablespace_ name
READ ONLY | READ WRITE

1.4 表空间数据文件修改

增加数据文件

ALTER TABLESPACE tablespace_ name
ADD DATAFILE 'xx.dbf' SIZE xx;

1.5 如何删除表空间？

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

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

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

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

语法：

形式一

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

形式二

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

两者的区别如下：

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

eg：以上面的学生表为例

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

2.3 表创建（约束）

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

举几个例子：

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

约束的分类：

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

2.3.1 主键约束

关键字：primary key

主键约束可以确认每一行数据的唯一性

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 相同就会报错

使用了主键约束，就相当于使用了非空约束

在修改表的时候增加主键约束 语法：add coinstraint constraint_name primary key(column_name1 .....);

-- 给学生 id 加上主键
alter table tb_student add contraint pk_id primary key(stuId);

更改约束名称 语法：rename constraint old_name to new_name

alter table tb_student rename pk_id to new_pk_id;

删除主键约束 约束的禁用语法：disable | enable constraint constraint_name

alter table tb_student disable constraint new_pk_id; -- 约束禁用

alter table drop constraint new_pk_id; -- 删除主键

alter table drop primary key; -- 删除主键约束

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 -- 学生的年龄
)

修改表时增加约束 语法：alter table table_name modify column_name dataType NOT null;

alter table tb_student modify stuName nvarchar(8） not null;

修改表时去掉非空约束 语法：alter table table_name modify column_name dataType 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 -- 学生的年龄
)

另一种方式设置唯一约束

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 -- 学生的年龄
		-- 给唯一约束 创建别名
		-- 格式 constraint 表名_列名_uk unique (列1,列2),
		-- constaint tb_student_stuName_uk unique(stuName)
       CONSTRAINT stu_Id_uk UNIQUE（stuIdCard ） -- 唯一约束的另一种方式
)

修改表的时候添加唯一约束

alter table tb_student add constraint stu_Id_uq unique(stuIdCard);

删除唯一约束

alter table xxx disable | enable constraint xxx; -- 暂时禁用
alter table xxx drop constraint xxx; -- 永久删除

2.3.4 检查约束

关键字：check

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

注意：

可以使用 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 ('男'，'女','不详'))
)

删除检查约束

alter table xxx disable | enable constraint xxx; -- 暂时禁用
alter table xxx drop constraint xxx; -- 永久删除

2.3.5 外键约束

创建表时设置外键约束
在修改表时添加外键约束
删除外键约束

关键字：

foreign key
REFERENCES

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

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

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

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

eg：

先创建一个表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（"男，’女，“中））
）；

创建一个表 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)
);

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

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

总结：

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）

删除外键约束

alter table xxx disable | enable constraint xxx; -- 暂时禁用
alter table xxx drop constraint xxx; -- 永久删除

2.3.6 总结

约束总共分为五类，分别是：

主键约束 primary key
非空约束 not null
检查约束 check
外键约束 foreign key
唯一约束 unique

在使用的时候，如果表已经创建好，我们需要额外添加约束关系的话，可以使用如下方法

增加约束

alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）

删除约束

alter table 表名 disable | enable constraint 约束类型; 
alter table 表名 drop constraint 约束类型; -- 永久删除

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 复制

复制一个一模一样的数据 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 删除老师和学生的信息

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2019/09/28 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

sql

登录后参与评论

0 条评论

热度