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 insertedSQL> 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 alteredSQL> 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                          452         30 woman lily                         453         30 woman kate                         45-- 可以发现deptno中有外键约束，并且部门号是45
-- dept表中删除没有使用过的数据，可以删除成功
-- 但是如果其中的数据作为了其他表的外键存在，那么就不能删除成功
SQL> delete from dept where deptno=45;
delete from dept where deptno=45ORA-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 alteredSQL> alter table person add constraint fk_xxx_deptno foreign key(deptno) references dept(deptno) on delete set null;Table alteredSQL> delete from dept where deptno=45;1 row deletedSQL> 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 PIECE10 周刊少年Jump                   Tokyo20 草帽海贼团                     东海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 '船长' thene.sal + 1000when '厨师' thene.sal + 800elsee.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 '船长' thene.sal + 1000when '厨师' thene.sal + 800elsee.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 empORA-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 SYSDBASQL> 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      207499 罗罗诺亚·索隆                                      剑士                  7566 1981/2/20       207521 娜美                                               航海士                7566 1981/2/22       207566 蒙奇·D·路飞                                       船长                  7839 1981/4/2        207654 妮可·罗宾                                         考古学家              7566 1981/9/28       207698 特拉法尔加·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 dualORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义-- 从序列中取值
SQL> select myseq.nextval from dual;NEXTVAL
----------1SQL> 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
----------4SQL> insert into person values(myseq.nextval, 20, 'man', 'xiaoming', 20);1 row insertedSQL> select * from person;ID        AGE SEX   NAME                     DEPTNO
---------- ---------- ----- -------------------- ----------1         30 man   tom                          452         30 woman lily                         453         30 woman kate                         455         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 createdSQL> grant create session to luffy;Grant succeeded-- 创建luffy用户成功并且赋予连接数据库的权限成功后，切换到luffy用户
SQL> conn luffy/luffySQL> show user;
User is "LUFFY"SQL> select * from scott.emp;
select * from scott.empORA-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 createdSQL> select * from myemp;
select * from myempORA-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 succeededSQL> 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     207499 罗罗诺亚·索隆               	   剑士                  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

1. 效率：多表查询高于子查询

2. 查看当前用户下的所有表：select * from tab;