alin的学习之路（数据库篇：四）（oracle数据库表的操作，oracle其他对象：视图、索引、序列、同义词）
程序员文章站
2022-05-07 08:02:06
...
alin的学习之路（数据库篇：四）（oracle数据库表的操作，oracle其他对象：视图、索引、序列、同义词）
1. oracle数据库表的操作
数据库表是基本的数据存储集合，由行和列组成。
数据库的表要注意命名规则：
- 必须以字母开头
- 必须在 1–30 个字符之间
- 允许包含的字符: A–Z, a–z, 0–9, _, $, 和 #
- 不能和用户定义的其他对象（表）重名
- 不能是Oracle 的保留字
- Oracle默认存储表名是都是大写
- 表名不区分大小写
1. 1 表的创建
创建表的关键字： create table 表名 …
- 数据库表中的数据类型
-- varchar2(size):
字符串(长度是可变的), size是指定字符串的最大长度, 会根据实际长度占用实际的内存大小
不知道字段实际的长度
-- char(size):
定长字符串, 字段会占用固定大小的内存, 内存大小为 size
比如: 性别
-- number(p, s)
数字类型, 可以是整数, 也可以是浮点数
p: 数字的位数
s: 代表小数点以后有多少位 number(3) ==> 最大值: 999, number(5,2) --> 最大值: 999.99
可以这么写: number, 表示是以整数, 长度是没有限制的
-- date: 日期类型, 日期的表示格式(参考日期单行函数的位置对应的表)
-- yyyy: 四位数的年, mm-两位数的月份, dd-两位数的日期
-- hh24: 24小时制的时, mi-> 两位数的分钟, ss->两位数的秒
- 创建数据库表
-- 语法：
-- 注意：创建数据库表时要注意命名规则，否则会报错
-- []中的内容可以省略, 是可选的, default 是关键字, 给当前列指定默认值
-- 假设这个表被创建了, 通过insert插入数据, 但是没有给这一列指定数据, 默认就是null, 如果指定了默认值就会显示默认值
create table 表名(
列名1 列的类型 [default 默认值],
列名2 列的类型 [default 默认值],
列名3 列的类型 [default 默认值],
列名4 列的类型 [default 默认值],
列名5 列的类型 [default 默认值],
... --- 最后一列的后边没有逗号
);
- 创建新表（先不添加约束）
-- 创建一个学生表 student
create table student(
id number(3), -- 学号最大三位数
name varchar2(20), -- 姓名最大20个字节
sex char(6), -- 性别占用6个字节
age number(2) -- 年龄最大两位数
);
- 基于原有的表创建一个新的表
- 基于一个原有的表出创建一个新的表有一个好处：不用手动去添加一些约束，直接使用和原表相同的约束，需要不同的需求可以再进行修改。
-- 基于一个原有的表复制出来一个新的表结构，可以使用如下语句
create table 表名 as select *|列名 from 要复制的表名;
-- 如上的语句会创建出来一个和原有表数据内容相同的表，如果不需要原有的数据内容，需要在from后面加上[ where 不成立的条件]
create table 表名 as select *|列名 from 要复制的表名 where 不成立的条件（如1=2）;
1.2 表的修改
修改表的关键字： alter table 表名 …
- 添加一个新的列
alter table 表名 add 新的列的列名 列的类型;
alter table 表名 add 新的列的列名 列的类型 default 默认值;
-- 查看当前用户下的所有表：select * from tab;
-- 在当前用户下有一个test表，对它进行列的添加
SQL> desc test;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(30) Y
LOC VARCHAR2(30) Y
-- 添加一个新的列名字叫person，类型是number(2)
SQL> alter table test add person number(2);
Table altered
-- 添加后查看表的列
SQL> desc test;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(30) Y
LOC VARCHAR2(30) Y
PERSON NUMBER(2) Y
- 修改列的属性
alter table 表名 modify 列名 新的属性（类型）;
alter table 表名 modify 列名 新的属性（类型）default 默认值;
-- 查看原先的test表属性
SQL> desc test;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(30) Y
LOC VARCHAR2(30) Y
PERSON NUMBER(3) Y
-- 修改属性
SQL> alter table test modify person number(3) default 0;
Table altered
-- 查看修改后的属性
SQL> desc test;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(30) Y
LOC VARCHAR2(30) Y
PERSON NUMBER(3) Y 0
-- 插入一条记录进行测试
SQL> insert into test(deptno, dname, loc) values(1, '后台', '北京');
1 row inserted
SQL> select * from test;
DEPTNO DNAME LOC PERSON
------ ------------------------------ ------------------------------ ------
1 后台 北京 0
- 列重命名
- 注意：rename 后面的 column 关键字不要丢
alter table 表名 rename column 旧列名 to 新列名;
-- 修改test表中的loc列名为location
SQL> alter table test rename column loc to location;
Table altered
SQL> desc test;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(30) Y
LOCATION VARCHAR2(30) Y
PERSON NUMBER(3) Y 0
- 删除列
- 注意：rename 后面的 column 关键字不要丢
alter table 表名 drop column 列名;
-- 查看test的表结构
SQL> desc test;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(30) Y
LOCATION VARCHAR2(30) Y
PERSON NUMBER(3) Y 0
-- 删除test的person列
SQL> alter table test drop column person;
Table altered
-- 查看删除后test的表结构
SQL> desc test;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(30) Y
LOCATION VARCHAR2(30) Y
1.3 表的重命名和删除
- 表的重命名
rename 旧的表名 to 新的表名;
SQL> rename test to test1;
Table renamed
-- 查看当前用户下所有的表
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$mMnkyUVCz7zgUAB/AQAblQ==$0 TABLE
BIN$mMnkyUVFz7zgUAB/AQAblQ==$0 TABLE
BIN$mMnkyUVGz7zgUAB/AQAblQ==$0 TABLE
BIN$mMnocU69zc3gUAB/AQAbmQ==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
STUDENT TABLE
TEST1 TABLE
10 rows selected
- 删除表
-- 删除表并放入回收站
drop table 表名;
-- 删除表并永久删除，不放入回收站
drop table 表名 purge;
---- 从数据库回收站还原数据库表
flashback table 表名 to before drop;
-- 清空回收站
purge recyclebin;
1.4 表约束
表约束: 约束的是表中的字段(列)
1.4.1 表约束的种类:
- 非空约束
- 在插入数据的时候, 这个字段值不能指定为NULL
- not null
- 唯一约束
- 这个字段值在当前表中不允许重复
- unique
- 检查性约束
- 插入数据的时候, 指定的字段的值, 必须是创建表的时候指定的某些数值才可以, 否则就不允许插入
- check
- 主键约束
- 非空约束 + 唯一约束
- 一个表中一般只有一主键
- primary key
- 外键约束
- 当前表中的某个字段的取值, 受其他表中某个字段的限制
- emp表中的deptno的取值是有要求的, 必须在某个范围内
- 这个范围得看 dept表, 这个表中也有一deptno字段
- 这个表中部门编号的集合(10, 20, 30, 40)
- 在emp表中 deptno取值必须在 (10, 20, 30, 40) 集合范围内, 否则不允许数据的插入
- foreign key
- 当前表中的某个字段的取值, 受其他表中某个字段的限制
1.4.2 表约束的添加
-- 对这个表的创建进行约束的添加
create table person(
id number , -- 主键
age number, -- 非空
sex varchar(5), -- 检查约束
name varchar(20), -- 唯一
deptno number -- 外键
);
- 表级别添加约束
-- 表级别的约束的约束条件应该添加到列定义完之后, 也就是所有都写在最下边
-- 约束名: 随意指定, 不重复即可
-- 约束条件：非空(not null)，唯一(unique)，检查(check)，主键(primary key)，外键(foreign key)
-- 指定约束的语法： constaint 约束名（自定义） 约束条件（字段名[列名]）
create table person(
id number , -- 主键
age number not null, -- 非空 该约束不能写在表级约束下，只能写在列级别的约束上
sex varchar(5), -- 检查约束
name varchar(20), -- 唯一
deptno number, -- 外键
--------------- 表级别约束条件 ---------------
constraint pk_xxx_id primary key(id), -- 主键
constraint ck_xxx_sex check(sex in('man', 'woman')), -- 检查
constraint un_xxx_name unique(name), -- 唯一
-- 当前表中中deptno字段的取值受限制, 受dept表的deptno字段的限制
constraint fk_xxx_deptno foreign key(deptno) references dept(deptno)
);
-- 以上代码在plsql中运行需要注意缩进，否则运行不正确
- 列级别的约束
-- 对这个表的创建进行约束的添加
-- 可以不加constraint关键字表示约束名会由系统帮助加上
create table person1(
id number primary key, -- 主键
age number not null, -- 非空
sex varchar(5) check(sex in('man', 'woman')), -- 检查约束
name varchar(20) unique, -- 唯一
deptno number references dept(deptno) -- 外键
);
-- 注意点：check后面要跟上列名;外键不写foreign key，只写references，其他的约束条件也不写()内的列名
-- 列级别的约束也可以显示的加上约束名，注意：约束名不能重复，否则不会执行成功
create table person2(
id number constraint pk_xxxx_id primary key, -- 主键
age number constraint nl_xxxx_age not null, -- 非空
sex varchar(5) constraint ck_xxxx_sex check(sex in('man', 'woman')), -- 检查约束
name varchar(20) constraint un_xxxx_name unique, -- 唯一
deptno number constraint fk_xxxx_deptno references dept(deptno) -- 外键
);
-- 表创建成功后，通过sql语句：select * from tab; 可以查看是否创建成功
- 关于外键约束
-- 首先查看person表的内容
SQL> select * from person;
ID AGE SEX NAME DEPTNO
---------- ---------- ----- -------------------- ----------
1 30 man tom 45
2 30 woman lily 45
3 30 woman kate 45
-- 可以发现deptno中有外键约束，并且部门号是45
-- dept表中删除没有使用过的数据，可以删除成功
-- 但是如果其中的数据作为了其他表的外键存在，那么就不能删除成功
SQL> delete from dept where deptno=45;
delete from dept where deptno=45
ORA-02292: 违反完整约束条件 (SCOTT.FK_XXX_DEPTNO) - 已找到子记录
-- 如果想要删除dept表中的该数据，该如果操作？
-- 解决方案: 不是修改当前的dept表, 修改引用dept表数据的表 --> person表, 这个表中有外键
-- 可以在设置外键约束的时候指定: 当dept表中的某个值被删除了, person表就不能使用这个值了,将这个字段设置为NULL
-- 所以也就是要修改外键约束
-- 设置方式:
-- deptno number constraint fk_x1_deptno references dept(deptno) on delete set null
-- 约束是不能直接修改的, 可以先删除之前的约束, 然后重新添加
-- 删除某个字段的约束
alter table 表名 drop constraint 约束名;
-- 添加约束
alter table 表名 add constraint 约束名 约束的类型(字段名/列名);
-- 删除约束
alter table person drop constraint fk_xxx_deptno;
-- 添加约束
alter table person add constraint fk_xxx_deptno foreign key(deptno) references dept(deptno) on delete set null;
-- 测试
SQL> alter table person drop constraint fk_xxx_deptno;
Table altered
SQL> alter table person add constraint fk_xxx_deptno foreign key(deptno) references dept(deptno) on delete set null;
Table altered
SQL> delete from dept where deptno=45;
1 row deleted
SQL> select * from person;
ID AGE SEX NAME DEPTNO
---------- ---------- ----- -------------------- ----------
1 30 man tom
2 30 woman lily
3 30 woman kate
SQL> select * from dept;
DEPTNO DNAME LOC
------ ------------------------------ ------------------------------
50 黑胡子海贼团 新世界
60 D之一族 ONE PIECE
10 周刊少年Jump Tokyo
20 草帽海贼团 东海
30 红心海贼团 北海
40 红发海贼团 新世界
-- 可以发现dept表中的deptno=45已经被删除了，并且person表中的值变为了null
-- 关键：在约束中添加 on delete set null
2. Oracle中的其他对象
2.1 视图
视图是一张虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来*定义视图的查询所引用的表，并且在引用视图时动态生成，视图不是真实存在磁盘上的。
将复杂的查询, 简单化, 方便使用, 多么复杂的sql语句都可以使用视图进行封装, 封装好之后得到视图对象, 后边直接对视图进行操作, 就可以得到表数据.
简单的来说，视图通常就是封装了一些查询的结果，有两个作用：一个是封装起一些比较复杂的查询，使用视图只需要查询这个视图即可，方便后面使用。二是以掩盖一些数据不让查询者看到。
由查询表变成了查询视图。
- 创建视图
-- 创建视图
create view 视图名
as
select *|列名|表达式, ... from 表名 where 条件;
-- 视图的使用
select * from 视图名;
-- 视图的删除
drop view 视图名;
- 视图的使用举例
select d.dname "部门名称", e.ename "员工", e.job "职务", e.sal "原工资",
case e.job
when '船长' then
e.sal + 1000
when '厨师' then
e.sal + 800
else
e.sal + 400
end "涨后工资"
from emp e, dept d where e.deptno=d.deptno;
-- 上述的一大段可以使用视图来封装，方便后续的使用
-- 使用视图对复杂的sql语句进行封装
create view mysal
as
select d.dname "部门名称", e.ename "员工", e.job "职务", e.sal "原工资",
case e.job
when '船长' then
e.sal + 1000
when '厨师' then
e.sal + 800
else
e.sal + 400
end "涨后工资"
from emp e, dept d where e.deptno=d.deptno;
SQL> create view myview as select empno, ename, job, mgr, hiredate, deptno from emp;
create view myview as select empno, ename, job, mgr, hiredate, deptno from emp
ORA-01031: 权限不足
-- 默认普通用户是没有权限去创建视图的，需要管理员去赋予它权限
-- 首先切换管理员权限
SQL> conn / as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as sys@192.168.31.52:1521/orcl AS SYSDBA
SQL> show user;
User is "SYS"
SQL> grant create view to scott;
Grant succeeded
-- 收回权限使用 revoke create view from 用户名;
-- 权限赋予后回到scott用户创建视图成功
SQL> create view myview as select empno, ename, job, mgr, hiredate, deptno from emp;
View created
-- 使用视图
SQL> select * from myview;
EMPNO ENAME JOB MGR HIREDATE DEPTNO
----- -------------------------------------------------- -------------------- ----- ----------- ------
7369 文斯莫克·山治 厨师 7566 1980/12/17 20
7499 罗罗诺亚·索隆 剑士 7566 1981/2/20 20
7521 娜美 航海士 7566 1981/2/22 20
7566 蒙奇·D·路飞 船长 7839 1981/4/2 20
7654 妮可·罗宾 考古学家 7566 1981/9/28 20
7698 特拉法尔加·D·瓦铁尔·罗 船长 7839 1981/5/1 30
-- 删除视图
SQL> drop view myview;
View dropped
赋予创建视图权限：grant create view to 用户名;
收回创建视图权限：revoke create view from 用户名;
-- 查询当前用户下的全部视图
SQL> select view_name from user_views;
VIEW_NAME
------------------------------
MYVIEW
-
视图总结
-
视图的使用和数据库表的使用是一样的, 视图更简单, 已经将要查询的数据封装好了
-
视图是动态生成的, 视图中的数据不占用磁盘空间, 数据表数据占用磁盘空间
-
视图查询更安全, 假设一个表有10列, 有5列是敏感数据, 不想让别人看
- 如果是数据表, 想查那一列就查那一列
- 视图在封装的时候, 只查询不敏感的5列, 敏感的数据别人通过视图就看不到了
-
2.2 索引
通俗的来讲，索引在表中的作用，相当于书的目录对书的作用。使用索引能够提高数据的查询效率。
当用户创建索引, oracle会维护一个索引表, 该表记录了创建索引使用的列和rowid的对应关系, 查询的
时候可以通过列的值找到对应的行地址.
通过索引可以快速定位数据, 提高查询效率
- 创建和删除索引
-- 创建索引
-- 注意：如果某个字段已经被创建了索引, 那么就不能在创建新的索引了
create index 索引名 on 表名(列名1，列名2，……);
-- 删除索引
drop index 索引名;
- 查看索引
select index_name from user_indexes;
- 哪些列需要创建索引?
- 在查询的时候, where后边的判断条件中, 哪些字段被经常使用, 就给哪个字段设置索引就可以了
2.3 序列
序列(SEQUENCE)是***生成器，可以为表中的行自动生成***，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。
其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。
序列就是数据库给我们维护的一个计数器, 帮我我们数数:
比如要录入一个班级的学生信息, 输入学号, 从 1开始到100
有点类似于enum枚举
- 创建和删除序列
-- 创建序列
-- 使用默认属性
CREATE SEQUENCE 序列名;
-- 自定义序列属性
CREATE SEQUENCE 序列名
[INCREMENT BY n] -- 序列的步长，可省略默认为1，如果出现负值， 按照此步长递减
[START WITH n] -- 序列的初始值(即产生的第一个值)，默认为1
[{MAXVALUE/ MINVALUE n| NOMAXVALUE}] -- MAXVALUE 定义序列生成器能产生的最大值
-- MINVALUE定义序列生成器能产生的最小值
-- NOMAXVALUE是默认选项， 没有最大和最小值
[{CYCLE|NOCYCLE}] -- CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环，默认不循环
[{CACHE n| NOCACHE}]; -- CACHE(缓冲)定义存放序列的内存块的大小，默认为20。
-- NOCACHE表示不对序列进行内存缓冲。
-- 删除序列
drop sequence 序列名;
-- 查看当前用户下的序列
select sequence_name from user_sequences;
- 序列的使用
-- 主要是两个方法
SQL> create sequence myseq;
Sequence created
对象名.nextval 表示获取下一个序列值
-- 得到序列中的下一个值, 第一次从序列中取值的时候, 必须先调用nextval, 否则得不到正确数据
对象名.currval 表示获取当前的序列值
-- 从序列中取出当前的值, 只是取值, 序列中的值不会增长
-- 第一次从序列中取值的时候, 不能使用currval, 取不出数据
SQL> select myseq.currval from dual;
select myseq.currval from dual
ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义
-- 从序列中取值
SQL> select myseq.nextval from dual;
NEXTVAL
----------
1
SQL> select myseq.nextval from dual;
NEXTVAL
----------
2
-- 使用insert into 搭配序列使用
SQL> desc person;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
ID NUMBER
AGE NUMBER
SEX VARCHAR2(5) Y
NAME VARCHAR2(20) Y
DEPTNO NUMBER Y
SQL> select myseq.nextval from dual;
NEXTVAL
----------
4
SQL> insert into person values(myseq.nextval, 20, 'man', 'xiaoming', 20);
1 row inserted
SQL> select * from person;
ID AGE SEX NAME DEPTNO
---------- ---------- ----- -------------------- ----------
1 30 man tom 45
2 30 woman lily 45
3 30 woman kate 45
5 20 man xiaoming 20
2.4 同义词
Oracle的同义词（synonyms）从字面上理解就是别名的意思，和视图的功能类似，就是一种映射关系。
同义词就是表的别名, 主要用户数据库表的跨用户访问
在scott用户下访问 emp表, 直接指定表名即可
直接使用表名, 表示这个表属于当前用户
如果在一个luffy用户下, 访问scott用户下的emp表, 应该怎么写?
用户名.表名
如果使用使用上述方式访问其他用户的表这个名字有可能会很长, 为了写起了更方便可以设置别名
这个别名就叫同义词
简单来说：同义词就是一个其他用户下的表的别名，代替了 用户名.表名 的写法。
- 创建同义词
-- 创建同义词
create synonym 同义词的名字 for 用户名.表名;
-- 删除同义词
drop synonym 同义词的名字;
-- 查看当前用户下的同义词
select synonym_name from user_synonyms;
- 同义词的使用
-- scott用户下有一张表: emp
-- 有另外一个用户: luffy, 需要访问 scott用户名下的 emp 表: scott.emp
-- 使用管理员, 创建新用户 luffy
-- 语法: create user 用户名 identified by 密码;
-- 新创建的用户没有连接数据的权限, 需要授权: grant create session to 用户名;
-- 创建luffy用户 : create user 用户名 identified by 密码;
SQL> show user;
User is "SYS"
SQL> create user luffy identified by luffy;
User created
SQL> grant create session to luffy;
Grant succeeded
-- 创建luffy用户成功并且赋予连接数据库的权限成功后，切换到luffy用户
SQL> conn luffy/luffy
SQL> show user;
User is "LUFFY"
SQL> select * from scott.emp;
select * from scott.emp
ORA-00942: 表或视图不存在
-- luffy用户现在还没有访问scott下的表的权限，需要scott用户给它赋予
-- 并且luffy用户现在也没有创建同义词的权限，创建同义词的权限需要管理员赋予
-- 首先赋予创建同义词的权限，需要切换到管理员用户
SQL> show user;
User is "SYS"
SQL> grant create synonym to luffy;
Grant succeeded
-- 创建同义词，但是select使用该同义词时显示并不存在，需要scott用户给它赋予访问的权限
SQL> show user;
User is "LUFFY"
SQL> create synonym myemp for scott.emp;
Synonym created
SQL> select * from myemp;
select * from myemp
ORA-00942: 表或视图不存在
-- 切换到scott用户赋予luffy访问它的权限
-- 操作: select / insert / delete / update
-- 语法: grant 操作 on 表名 to 用户名;
-- 授权luffy用户可以查询emp表
SQL> show user;
User is "SCOTT"
SQL> grant select on emp to luffy;
Grant succeeded
SQL> show user;
User is "LUFFY"
SQL> select * from myemp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------------------------------------- -------------------- ----- ----------- -------
7369 文斯莫克·山治 厨师 7566 1980/12/17 3200.00 800.00 20
7499 罗罗诺亚·索隆 剑士 7566 1981/2/20 3450.00 300.00 20
-- 到此创建成功
-- 查看当前用户下的同义词
SQL> select synonym_name from user_synonyms;
SYNONYM_NAME
------------------------------
MYEMP
-- 删除同义词
SQL> drop synonym myemp;
Synonym dropped
总结：用户需要的权限：1.连接数据库权限。2.创建同义词的权限。3.操作其他用户表的权限。
3. some small point
-
效率：多表查询高于子查询
-
查看当前用户下的所有表：select * from tab;
上一篇： 2018最火刑侦题（Python constraint库求解）
下一篇： 第三章