Oracle（四）

1、常用的数据类型

varchar、varchar2 :表示一个字符串
NUMBER(n)：表示一个整数，长度是n
NUMBER(m,n):表示一个任意小数，例如：Number(10,2)--8位整数，2位小数
DATE 表示日期类型(重点掌握)   
CLOB 大对象，表示大文本数据类型，可存4G

BLOB 大对象，表示二进制数据，可存4G

2、再识伪劣(rowid)

回忆：rownum也是一个伪劣，用在分页显示数据条数

select rowid,ename from emp;

结果：

说明：表中的每一行数据都有一个唯一的rowid，那其实我们也可以通过这个唯一的rowid去查询表中的数据。你可以理解为 rowid 像一个指针一样，指向了数据库的文件，这些数据库的文件就是以 .DBF结尾的文件，这些文件在我们Oracle 安装路径下的 D:\app\oradata\orcl 目录里面，这些文件保存的就是表中的数据，那这个rowid 就指向了表中的数据。

作用：快速的定位某行具体的数据的位置

select * from emp where rowid='AAAR3sAAEAAAACXAAA';-- 注意不同的电脑此行号可能不一样

相关链接：点击打开链接(rowid的深入详解)

3、利用子查询复制表结构、数据

看下面的sql语句：

create table testtable2 as  select* from testtable ;         --(1)复制表和数据
create table testtable2 as  select* from testtable where 1=1;--(2)复制表和数据--同上
create table testtable2 as  select* from testtable where 1=2;--(3)复制表的定义(结构),不复制数据
insert into testtable2 select * from testtable;              --(4)复制数据---一次插入多条数据(注意！！！)

语法：create table 表1 as select 字段或者部分字段 from 表2 where(逻辑表达式)

具体：where 1=2：表示如果是一个假的条件，那么 testtable2 创建的时候,只要了emp表中的字段，没有要 emp表中的数据

          where 1=1：那么这个testtable2不仅和emp表中的字段一样，并且emp表中的所有数据也复制到了testtable2表中

需求1：我们通过一个查询语句，查出来一些数据(临时表)，我想把这些数据保存到一张表中，就可以使用子查询来创建一张表

select e.empno,e.ename,e.sal,e.sal*12 as "年薪",d.dname from emp e,dept d where e.deptno=d.deptno;

说明：上面这条语句查出来的 是一张临时表,我想把这张临时表中的数据 保存到另一张新建的表中,我们就可以用子查询来创建表

create table newTable as select e.empno,e.ename,e.sal,e.sal*12 as "年薪",d.dname from emp e,dept d where e.deptno=d.deptno;
select * from newTable;-- 已经存在数据了

-------------------------

4、修改表中的字段

4.1 增加一个字段 alter table 表名 add (字段名 数据类型(长度));   

  alter table newTable add (y varchar(1));-- 注意括号阔上

4.2 修改字段(类型)     alter table 表名 modify 旧字段名 新的数据类型;    

alter table testtab modify tname varchar(10);-- 修改字段的名称和数据类型

4.3 删除字段       alter table 表名 drop column 字段名  

 alter table testtab drop column sex;

4.4 重命名字段    alter table 表名 rename column 旧字段名 to 新字段名; 

 alter table testtab rename column tname to username;-- 修改字段的名称

5、重命名表名   rename 旧表名 to 新表名;    

 rename testtab to testtable;-- 理解为修改表名

6、删除表          drop table 表名;   

drop table testtable;

补充：使用 drop删除表，并没有真正的删除这张表，这张表还留在数据库的回收站中，可以从回收站中把这张表再还原回来

思考：如何还原呢？具体参见：点击打开链接(工作中)

如何正真的删除表呢？

（1）查看回收站  

show recyclebin;

说明：这一串你不认识的东西,就是你刚才删掉的表

（2）清空回收站 

purge recyclebin;

此时回收站中就没有数据了

思路2：直接删除数据不走回收站(英文的含义)

drop table 表名 purge;-- 慎用

扩展：Windows下"shift+delete"可以删除数据不走回收站。

需求2：一张表被删除后,你就不能查这张表了,但是你还想差表中的数据怎么办？

思路：可以通过回收站中的表名来查询表中的数据，如下：

select * from "BIN$FpETXCv1R36NfZ1+bN0AJQ==$0"; --注意表名带上双引号,不加双引号查不出来

需求3：从回收站中还原删除的表(闪回技术)

flashback table testtable to before drop;

惊不惊喜，意不意外

思考：比如第一次我先创建一张表 testtable 然后删除,，然后我再创建一张表还叫 testtable 然后再删除，那么这时回收站中有两张同名的表，这时我从回收站中还原一下表：

 flashback table testtable to before drop; -- 请问还原的是哪张表?

说明：还原的是最后一次删除的那张testtable表

需求4：那么问题又来了，我想再还原第一次的那张表，这时会发生两张重名表，那就还原不成功了，怎么办? 

flashback table testtable to before drop rename to testtable2; -- 那就还原时改个名字, 

细节：不是所有的人都有回收站,，没有回收站的人删了还原不了了，不走回收站；管理员登陆(sys)是没有回收站的  

--------------------------

7、约束

    7.1 check 检查性约束    

 create talbe test3(
	tid number,
	tname varcher(20),
	sex varchar(2) check (sex in('男','女')),   --意思就是sex这个字段的值,只能是男或者女,不能是其他值.
	sal number check (sal>0) --检查工资 只能是大于0 不能是0和负值
		   );

    7.2  not null 非空
    7.3  unique 唯一
    7.4 primary key 主键  意思就是非空且唯一

    7.5 foreign key 外键约束

需求5：我们创建一张表把这些个约束都用一下

 create table student(
	sid number primary key,
	sname varchar(32) not null,
	sex varchar(3) check(sex in('男','女')),  --检查性别只能是这两种的一种 比如 工资要大于0  sal number check(sal>0)
	email varchar2(40) unique not null,
	deptno number references dept(deptno) on delete set null -- 关联外键的时候 我们 on delete set null 级联置空
	         );
insert into student values(1,'zhangsan','男','abc@163.com',10);-- 插入数据

级联删除的区别

-- 级联删除: on delete casecade 当删除主表中的数据时,相关联的子表数据也一并删除
-- 级联置空 :on delete set null 当删除主表中的数据时,相关联的子表的外键置为null

on delete casecade 的功能是在主数据删除的时候，从属数据一并删除,常用于强耦合关系中。

on delete set null   的功能是在主数据删除的时候，从属数据不会删除，只是将从属数据的关联属性字段设置成NULL，从而变为无主待关联数据，这个常用于弱耦合关系中。

相关链接：点击打开链接

--------------------------

8  数据库的其他对象

    8.1 视图对象

sqlplus sys/123456 as sysdba; -- （1）管理员身份登陆--cmd
show user                     -- （2）显示当前用户是不是管理员
grant create view to scott;   -- （3）授予scott 创建视图的权限

需求6：创建一张视图myview，视图中的数据来自从emp表中的数据(子查询)

create view myview as select * from emp;  

等价说法：把从emp表中的数据查出来，以视图的形式保存

注意：创建视图需要权限，所以我们需要用管理员身份登陆，然后给scott用户 赋予创建视图的权限,scott用户才能创建视图

需求7：只想创建只读视图

create view myview as select * from emp with read only;  -- 创建只读视图,只能查看视图,不能增删改

出发点：视图是一种虚表，视图建立在已有表(基表)的基础上，对于单表可以通过改变视图来影响基表，这不是我们希望的。

视图的本质：视图的创建是要依赖一条子查询语句，将子查询语句查出的数据以视图的形式保存。

需求8：让视图只能创建、删除、或者替换，但是不能修改

create or replace view  myview as select * from emp where sal>1000; -- create or replace view 创建或者替换视图 如果视图存在就替换

 注意：我们可以通过视图插入数据 更新数据 删除数据 但是不建议这么做；一般情况下我们是不通过视图对表进行增删改操作，我们创建视图是为了查询，不忘初心。

    8.2  序列对象 sequence 

        出发点： 为了实现和mysql中 auto_increment自增长的功能(并不仅仅是自增长)

        序列本质：其实是一个数组存储在内存中, 可以提高访问效率  

    看一下创建序列的语法，也就是创建这个数组：

语法：CREATE SEQUENCE 序列名 
	[INCREMENT BY n]                   -- 可选参数 步长 默认值1 每次自增1
	[START WITH n]                     -- 可选参数 起始值 默认是1
	[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]-- 可选参数 最大值和最小值 也可以不定义最大值 NOMAXVALUE
	[{CYCLE|NOCYCLE}]                  -- 循环和不循环  默认值是不循环NOCYCLE  因为循环可能会重复 那么主键是不能重复的,所以选不循环
	[{CACHE n|NOCACHE}];               -- CACHE数组长度  NOCACHE 数组长度不固定

例1：

create sequence myseq9
		increment by 1  -- 步长(相邻两个索引的方差)默认值是1
		start with 1    -- 起始索引值，默认值是 1
		maxvalue 9999   -- 数组的最大索引值
		nocycle         -- 不循环
		nocache;	-- 长度不固定
create sequence myseq10; -- 完成默认的功能

说明：序列创建成功后，有一个指针一样的东西默认在这个序列的第一个数的前一个位置 ，我们可以通过创建好的这个序列中的两个属性：nextval 和  currval 来让指针下移一位和获取指针当前的位置  

需求9：让某个键从1开始自增长

步骤：

（1）我们先让指针往下移动一位

（2）然后我们再查询一下指针的当前位置

（3）接下来我们可以往表中插入值并把序列插入进去即可

相应的SQL语句(可能效率比较高)

-- (1)创建序列
create sequence myseq ;-- 序列创建成功后,有一个指针一样的东西,默认在这个序列的第一个数的前一个位置
-- (2)通过伪表让序列的指针下移一位   
select myseq.nextval from dual;  --显示1(说明移下来了)
-- (3)然后我们再查询一下 指针的当前位置   
select myseq.currval from dual;-- 显示1
-- (4)创建表并向表中插入数据
create table mytable(sid number,sname varchar(20));
insert into mytable values(myseq.currval,'a'); 
insert into mytable values(myseq.nextval,'b');    
insert into mytable values(myseq.nextval,'c');
insert into mytable values(myseq.nextval,'d');
insert into mytable values(myseq.nextval,'e');
insert into mytable values(myseq.nextval,'f');
insert into mytable values(myseq.nextval,'g');
-- (5)显示数据
select * from mytable;

结果(后续补充)

遇到的问题：用Oracle11g创建序列后插入数据的初始值老是从2开始(创建序列后直接通过nextval获取当前的值)

简单解释：Oracle从 11.2.0.1版本开始，提供了一个“延迟段创建”特性：·当我们使用序列作为插入数据时，如果使用了“延迟段”技术，则跳过序列的第一个值。

方式1：更改数据库的“延迟段创建”特性为false（需要有相应的权限）

ALTER SYSTEM SET deferred_segment_creation=FALSE;

方式2(采用)：

-- 在创建表时让seqment立即执行;
create table mytable(sid number,sname varchar(20)) SEGMENT CREATION IMMEDIATE;

相关链接：点击打开链接

说明：nextval 和 currval也是两个伪列

    8.3 索引对象 index

理解:  可以理解为一本书的目录或者书签，查询(出发点)起来就比较快

需求10：比如我们要查询emp表中10号部门的员工 

 select * from emp where deptno=10;

分析：我们这张员工表的数据 有10号、20号、30号部门的员工，表中的数据每条数据没有规律，3个部门的员工相互穿插，这样我们在查询10号部门的员工时，得一行一行去挨个查，筛选出10号部门的员工，所以查询起来比较慢 。那我们可以给对这些数据创建一个目录，把3个部门的员工分别归类，10号、20号、30号部门分别归一块，这样分门别类后，可以通过这个索引(目录) 再查询起来就比较快了;那其实这个索引里面放的是每条记录的 rowid只不过把这个rowid 分门别类了。

创建索引： on 字段

create index myindex on emp(deptno);-- 针对哪个字段创建的

说明：为emp表中的部门号建立一个索引，这个索引表由Oracle自动维护，我们不用管，然后再查，这样查询效率就高了  

select * from emp where deptno=10;-- 可以测下时间

索引：索引的类型有两种 一种是默认类型 B树索引，另一种是 位图索引(矩阵)，相关博客--点击打开链接

    8.4 同义词对象 synonym(不是太懂)

    出发点：方便访问其他用户的对象，可以给其他用户的表、视图起别名

需求11：scott用户要查 hr用户下的表 

-- (1)管理员登陆
sqlplus sys/root as sysdba; 
-- (2) -- 授权 scott 可以查询hr下的employees这张表
grant select on hr.employees to scott 
-- (3)那么scott 才可以去查 hr用户的表(但是还不能为hr.employees起别名--同义词，创建同义词还是需要权限,没有权限会报错)
select count(*) from hr.employees;
-- (4)授权scott用户可以创建同义词对象
grant create synonym to scott;-- 系统账号登陆 
-- (5)然后创建同义词 才能成功
create synonym myhr for hr.employees ;
-- (6)然后我们再查hr.employees 这张表可以通过同义词来查
select count(*) from myhr;

注意：我们默认起的同义词是私有同义词 只有自己能用 你想让别人也能用 可以起公有同义词 通过public 即可

create public synonym myhr for hr.employees ;

未完待续。。。