1.数据类型
数据类型
类型字符
说明
CHAR()
字符型
固定长度字符串，最大长度是2000字节
NVARCHAR2()
可变长度字符串，最大长度是4000字节
VARCHAR2()
NUMBER
数值型
数值类型，可包含小数
DATE
日期类型
日期/时间
需要注意的是，在oralce中使用当前的时间，可使用关键字sysdate，mysql就不同了，它需要使用函数，即sysdate()。
2.表的操作
表的操作(表及数据的增删改查)和mysql类似，详见https://www.cnblogs.com/zys2019/p/11567312.html#_label2_0。不过需要注意的是，oracle并没有mysql的limit分页查询，而是有它自己的分页方式。
2.1分页查询
oracle分页使用rownum关键字。用法如下：先假如有一个员工表emp
--查询员工信息的前5条数据 第一页数据
select rownum r,e.* from emp e where rownum <=5
--查询员工信息的6-10条数据 第二页数据
select rownum,t.* from (select rownum r,e.* from emp e where rownum <=10) t where r>5
分页规律总结：每页显示m条数据，查询第n页数据
select * from (select rownum r,e. * from 要分页的表 e where rownum<=m*n) t where r>m*n-m
2.2 给表和列添加注释
1)给表添加注释
comment on table 表名 is '注释';
2)给列添加注释
comment on column 表名.字段名 is '注释';
3.视图
3.1定义
视图是一个虚拟的表，它在物理上并不存在。视图可以把表或其他视图的数据按照一定的条件组合起来，但它并不包含数据，它只是从基表中读取数据。查询视图，本质上是对表进行关联查询。可以将复杂的查询创建成视图，提供给他人使用，他人就不需要去理解其中复杂性的业务关系或逻辑关系，使用比较多的地方是关联查询等。
3.2视图的基本操作
数据准备：执行的sql如下：
CREATE TABLE DEPT ( id NUMBER, demp_name VARCHAR2 ( 25), PRIMARY KEY ( id ) );
CREATE sequence seq_dept;
INSERT INTO DEPT
VALUES
( seq_dept.nextval,'财务部');
INSERT INTO DEPT
VALUES
( seq_dept.nextval,'人事部');
INSERT INTO DEPT
VALUES
( seq_dept.nextval,'信息部');
CREATE TABLE EMP ( id NUMBER, name VARCHAR2 (255 ), phone VARCHAR2 ( 20), dept_id NUMBER, PRIMARY KEY ( id ) );
CREATE sequence seq_emp;
INSERT INTO EMP
VALUES
( seq_emp.nextval,'张三', '15625456352', 1);
INSERT INTO EMP
VALUES
( seq_emp.nextval,'李四', '15825457552', 2);
INSERT INTO EMP
VALUES
( seq_emp.nextval,'王五', '15925456354', 3);
INSERT INTO EMP
VALUES
( seq_emp.nextval,'李六', '15925456444', 1 );
现要查询员工编号和部门的名称，语句如下：
select emp.id,dept.demp_name from emp,dept where emp.dept_id=dept.id
3.2.1创建视图
语法：or replace表示视图存在就修改，不存在就创建
create or replace view 视图名as
select 语句;
示例：
create or replace view v_emp_deptas
select emp.id,dept.demp_name from emp,dept where emp.dept_id=dept.id;
3.2.2修改视图
修改视图，增加员工的姓名信息。使用关键字or replace。
create or replace view v_emp_deptas
select emp.id,dept.demp_name,emp.name from emp,dept where emp.dept_id=dept.id;
3.2.3删除视图
删除上述创建的视图
drop view v_emp_dept
3.2.4使用视图
把视图创建成功后，就可以直接使用视图，使用方式很简单，就把视图看作一个表即可。
select * from view v_emp_dept
4.索引
4.1定义
索引在表中的作用，相当于书的目录对书的作用，可以提高 SQL 语句执行的性能。
4.1.1索引类型
索引分为B树索引和位图索引。
(1)B树索引。B树索引 又可以进行细化，如下图：
1)唯一索引：唯一索引确保在定义索引的列中没有重复值，但可以不限制NULL值。 Oracle 自动在表的主键列上创建唯一索引，使用CREATE UNIQUE INDEX语句创建唯一索引。
2)组合索引：组合索引是在表的多个列上创建的索引，索引中列的顺序是任意的如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度。
3)反向键索引：反向键索引反转索引列键值的每个字节，为了实现索引的均匀分配，避免b树不平衡通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上。创建索引时使用REVERSE关键字。
(2)位图索引
位图索引适合创建在低基数列上，位图索引不直接存储ROWID，而是存储字节位到ROWID的映射，节省空间占用。如果索引列被经常更新的话，不适合建立位图索引。总体来说，位图索引适合于数据仓库中，不适合OLTP中。
4.2索引的基本操作
数据准备，执行是脚本如下：
create table student(
id NUMBER,
name VARCHAR2(20),
age NUMBER,
ID_card VARCHAR2(20),
addr VARCHAR2(200),
PRIMARY KEY(id)
);
create sequence seq_stu;
insert into student values(seq_stu.nextval,'李敏',20,'4203251112','湖北十堰');
insert into student values(seq_stu.nextval,'赵航',30,'4203251532','湖北武汉');
insert into student values(seq_stu.nextval,'刘敏',25,'4203258946','湖北武汉');
insert into student values(seq_stu.nextval,'李慧',16,'4203254525','湖北宜昌');
4.2.1创建索引
基本语法：
create index 索引名 on 表名(列名);
1)创建唯一索引
create unique index index_id_card on student(ID_card);
2)创建组合索引
创建姓名和地址的组合索引
create index index_stu_name_addr on student(name,addr);
4.2.2修改索引
修改索引主要是修改索引的名字。修改组合索引的名字
alter index index_stu_name_addr rename to index_name_addr;
4.2.3删除索引
语法：
drop index 索引名称
删除创建的组合索引
drop index index_stu_name_addr;
5.序列
5.1定义
序列的作用是自动生成整型数值，作为一个记录的唯一标识，相当于id的自增。它最多由38个数字组成。
5.2序列的基本操作
5.2.1创建序列
语法：
create sequence 序列名
minvalue1nomaxvalue
start with1increment by1nocycle
cache20
参数说明如下：
minvalue 1：序列的最小值是1
nomaxvalue：序列无最大值限制
start with 1：序列的初始值是1
increment by 1：序列的间隔是1
cache 20：高速缓存大小是20
以上的参数都有默认值，也就是后面的这些数值，如果没有特定的要求，那么创建序列就可以简写，命令如下：
create sequence seq_user_id;
5.2.2修改序列
修改序列的步长是2
alter sequence seq_user_id increment by 2
5.2.3删除序列
drop sequence seq_user_id;
5.2.4使用序列
创建完序列后，它虽然是自增的，但是要往表里插入值，就必须使用序列
1)使用序列的下一个值
语法：
序列名.nextval
示例1：查询序列的下一个值
select seq_user_id.nextval from dual;
示例2：插入记录，保存id自增
insert into user(id,name) values(seq_user_id.nextval,'张三');
insert into user(id,name) values(seq_user_id.nextval,'李四');
2)使用序列的当前值
语法：
序列名.currval
示例：查询序列的当前值，必须是先执行查询序列下一个值才可以查询得到序列当前值
select seq_user_id.currval from dual;
6.函数
6.1定义
Oracle创建函数是通过PL/SQL自定义编写的，通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。
6.2函数的基本使用
6.2.1 find_in_set在oracle下的解决方案
先有一张表user，数据如下：
编号
姓名
爱好(0打篮球，1踢足球，2打乒乓球，3跑步，4玩游戏)
1
张三
1,3
2
李四
0,2,4
3
王五
0,1,3
4
赵柳
1,2,3,4
现在需求是查询出爱好是跑步的用户信息，mysql可直接通过find_in_set进行查询，但是oracle并没有这个函数。
mysql查询：
select * from user where find_in_set('3',爱好);
虽然oracle没有这个函数，但是可以自定义这个函数，即自己创建函数后再使用。创建函数的sql如下：
create or replace function find_in_set(arg1 in varchar2,arg2 invarchar)return number isResult number;
beginselect instr(','||arg2||',' , ','||arg1||',') into Result fromdual;return(Result);
end find_in_set;
创建之后直接使用这个函数
select * from userwhere find_in_set('3',爱好)!=0
如果需要删除函数，语法是：
drop function 函数名;
7.case when的使用
7.1表达式
oracle中CASE WHEN 表达式有两种形式：
--简单Case函数
CASE sex
WHEN'1' THEN '男'WHEN'2' THEN '女'ELSE'其他'END--Case搜索函数
CASE
WHEN sex= '1' THEN '男'WHEN sex= '2' THEN '女'ELSE'其他' END
第一种方式比较简便，推荐使用第一种。
7.2用法
CASE WHEN 在语句中不同位置的用法
1)用在select查询结果中(最常用)
selectname,age,case sex
when '1' then '男'
when '2' then '女'
else '未知' end sexfrom student
2)用在where条件中
用在where条件中，主要是用来同时满足或不同时满足的情况。
SELECT t2.*, t1.*FROM t1, t2
WHERE (
CASE WHEN t2.COMPARE_TYPE= 'A' AND t1.SOME_TYPE LIKE 'NOTHING%' THEN 1WHEN t2.COMPARE_TYPE!= 'A' AND t1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1ELSE0END
)= 1
3)用在group by分组中
在grouy中用的比较少，需要结合select的case when使用
SELECT
CASE WHEN salary<= 500 THEN '1'WHEN salary> 500 AND salary <= 600 THEN '2'WHEN salary> 600 AND salary <= 800 THEN '3'WHEN salary> 800 AND salary <= 1000 THEN '4'ELSE NULL END salary_class,--别名命名
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary<= 500 THEN '1'WHEN salary> 500 AND salary <= 600 THEN '2'WHEN salary> 600 AND salary <= 800 THEN '3'WHEN salary> 800 AND salary <= 1000 THEN '4'ELSE NULL END;
8.oracle恢复删除的数据
8.1通过时间恢复
1)查询当前系统时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
2)查询删除数据的时间点的数据
select * from 表名 as of timestamp to_timestamp('2020-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss');
3)恢复删除且已提交的数据
flashback table 表名 to timestamp to_timestamp('2020-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss');
如果在执行上面的语句，出现错误。可以尝试执行下面的命令来允许更改时间戳：
alter table 表名 enable row movement;
8.2通过scn恢复
1)获得当前数据库的scn号：需要切换到sys用户或system用户
select current_scn from v$database;
查询到的scn号为：1499223
2)查询当前scn号之前的scn
select * from 表名 as of scn 1499220;
确定删除的数据是否存在，如果存在，则恢复数据；如果不是，则继续缩小scn号
3)恢复删除且已提交的数据
flashback table 表名 to scn 1499220;
扫一扫
439
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
