Oracle学习

oralce表查询关键字

1.使用逻辑操作符号

 select * from emp where (sal <> 500 or job = 'MANAGER') and ename like 'J%';

2.使用order by字句

 select * from emp order by sal desc

3.取别名(中文需加双引号)

 select e.empno "部门编号",mgr m from emp e;

4.聚合函数的使用

 求工资最高的人：select ename,sal from emp where sal = (select max(sal) from emp);

5.group by 和having语句

 select  avg(sal), max(sal), deptno from emp group by deptno having avg(sal)< 2000;
 顺序：group by->having->order by

6.多表查询

笛卡尔积：多表查询的条件是至少不能少于表的个数N-1才能排除笛卡尔集（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）

 显示各个员工的姓名，工资及工资的级别
 select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
 ​
 查询员工名和上级名
 select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;
 ​

7.单行子查询

 查询和smith同部门的员工
 select * from emp where deptno=(select e.deptno from emp e where e.ename='SMITH')

8.多行子查询

 查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
 select e2.ename,e2.job,e2.sal,e2.deptno from emp e2 where e2.job in(select distinct e.job from emp e where e.deptno=10)

9.多列子查询

查询与SMITH 的部门和岗位完全相同的所有雇员
SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');

10.用查询的结果创建新表

CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;

11.union

union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行
union all 该操作符与union相似，但是它不会取消重复行，而且不会排序。
intersect 使用该操作符用于取得两个结果集的交集。
minus 使用该操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据

SELECT ename, sal, job FROM emp WHERE sal >2500
	UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

新建表：

CREATE TABLE USERS( ID NUMBER(4) PRIMARY KEY, NAME VARCHAR(10), SEX CHAR(2), ADDR VARCHAR2(20), BIRTHDAY DATE, SAL NUMBER(8,2) );

查看表的结构：DESC USERS;

插入数据：i:nsert into users(id,name,sex,ADDR,BIRTHDAY,sal)values(1,'Jack','1','USA.Los Angeles',to_date('20140214','yyyymmdd'),1000);

删除数据：delete users where users.name='Bob';

改：update users set users.name='Jackie' where users.name='Jack';

增：select * from users;

12.exists

#查询table1中存在的数据在table2中不存在的数据
select * from table1 t1 where not exists (select bh from table2 t2 where t1.bh=t2.bh);
#查询table1、table2中都存在的数据
select * from table1 t1 where exists (select bhfrom table2 t2 where t1.bh=t2.bh)

oracle常用函数

lower(char)：将字符串转换为小写的格式

upper(char)：将字符串转换为大写的格式

length(char)：返回字符串的长度

substr(char,m,n)：截取字符串的子串，n代表取几个字符

replace(char1,search_string,repacle_string)

instr(C1,C2,I,J)：C1-被搜索的字符串 C2-希望搜索的字符串 I-搜索的开始位置，默认为1 J-出现的位置，默认为1；若存在则返回索引，若不存在则返回<1

to_char()函数：可以将时间转换为字符串

to_date()函数：可以将字符串类型转换为date类型

oracle结构

注：dual表的介绍

dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录，可以用它来做很多事情。

1.查看当前用户，可以执行下面语句：select user from dual

2.用来调用系统函数

3.得到序列的下一个值或当前值，用下面语句

select your_sequence.nextval from dual;--获取序列的your_sequence的下一个值

select your_sequence.currval from dual;--获取序列your_sequence的当前值

4.可以用于计算

1.view视图

一、视图是查询结果的一个封装，视图中的所有数据都来自它查询的表，视图本身不存储任何数据。视图能封装复杂的查询结果

二、创建视图语法：create [or replace] view 视图名 as 查询语句 [with read only],视图默认是可以修改。

2.sequence序列

一、oracle的sequence类似mysql的auto_increment，id自动增长。

二、创建sequence语法：create sequence 序列名 [start with num] [increment by increment] [maxvalue num|nomaxvalue] [minvalue num| nominvalue] [cycle| nocycle] [cache num | nocache]

语法解析：

1、start with：从某一个整数开始，升序、降序默认值均为1

2、increment by：增长数，升序、降序默认值均为1

3、maxvalue：最大值

4、nomaxvalue:最大值的默认选项，升序的最大值是10^27，降序默认值是1

5、minvalue：指最小值

6、nominvalue:最小值默认值选项，升序默认值是 1 ，降序默认值是 10的26 次方

7、cycle：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。

8、nocycle：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认 NOCYCLE 。

9、cache：使用 CACHE 选项时，该序列会根据序列规则 预生成一组序列号 。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列 号的效率。 Oracle默认会生产 20 个序列号 。

10、nocache：不预先在内存中生成序列号。

11、cache num：缓存数

三、序列创建之后，可以通过序列队形的currval和nextval两个"伪列"，分别访问该序列的当前值和下一个值，currval必须在nextval调用之后才能使用。

四、使用alter sequence可以修改序列，在修改序列时有如下限制：

1、不能修改序列的初始值。

2、最小值不能大于当前值。

3、最大值不能小于当前值。

alter sequence seq1 cache 10

五、删除序列对象：drop sequence seq2;

3.index索引

一、索引相当于一本书的目录，能过提供检索的速度，如果某一列需要经常作为查询条件，则有必要为其创建索引，能显著提供效率。

二、索引语法：CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])

语法解析：

1、unique指定索引列上的值必须是唯一的，称为唯一索引，否则就是普通索引。

2、index_name：指定索引名。

3、table_name：指定要为哪个表创建索引。

4、column_name：指定要对哪个列创建索引。我们可以对多列创建索引，这种索引称为组合索引。

三、Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。

测试

---创建表
	create table person( pid number(32) primary key, pname varchar2(16) not null, paddress varchar2(16) not null);

---使用 PLSql 语法插入 500万条数据。plsql 是 Oracle 对原生sql的封装，是 oralce 自己独有的
    declare
    begin
      for i in 1..5000000 loop
        insert into person values(i,'姓名'||i,'地址'||i);
        end loop;
        commit;
    end;

---在没有使用索引的情况下，查询 panme='姓名4000000' 的用户 。耗时：3~5秒
select * from person where pname = '姓名4000000';

--实际中建表后就应该设置索引，这里已经有500万条数据后再创建索引花了56秒【CPU是i5运行很慢】
create index index_pname on person(pname);--为 pname 列创建索引

---使用索引的情况下，查询 panme='姓名4000000' 的用户 。耗时：0.01秒
select * from person where pname = '姓名4000000';

--在没有复合索引的情况查询 panme='姓名4000000' 且 paddress='地址4000000' 的用户，耗时：0.032
select * from person where pname = '姓名4000000' and paddress = '地址4000000';
--为 panme 、paddress 创建复合索引后再次查询。耗时：0.25        与没建复合索引区别不是很明显
create index index_pname_paddress on person(pname,paddress);--花了 46秒

select * from person where pname = '姓名4000000' and paddress = '地址3500000';

--经实测发现，pname 建了复合索引之后，select * from person where pname = '姓名4000000'; 速度更慢了

四、索引的原理底层使用是平衡二叉树。数据库中索引index的概念与目录的概念非常类似。如果某列出现在查询的条件而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。如果该列出现在查询条件中，oracle会自动引用该索引，先从索引表中查出符合记录的rowid,由于rowid是记录的物理地址，因此可以根据rowid快速的定位到具体的记录，表中的数据很多时，引用索引带来的查询效率非常可观。

五、如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该 列创建索引。当从很多行的表中查询少数行时 ，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的 10%时，索引就非常有用。

六、索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。

4.cursor游标

一、游标：用于操作查询的结果集，类似JDBC的ResultSet。Oracle中的游标需要结合PLSQL使用。

语法：cursor 游标名[(参数名 参数类型)] is 查询结果集

二、开发步骤

5.触发器

一、触发器：是一个与表关联的、存储的PL/SQL程序，当用户执行了insert、update、delete操作之后，oracle自动地执行触发器中定义的语句序列。

作用：

1.数据确认：如员工涨薪后，新工资不能少于之前的工资。

2.安全性检查：如禁止非工作时间插入新员工。

3.做审计，跟踪上所做的数据操作等。

4.数据的备份与同步。

类型:

语句级触发器：在指定的操作语句之前或者之后执行一次，不管这个语句影响了多少行语句。

行级触发器：触发语句作用的每一条记录都被触发，在行级触发器中使用old和new伪记录变量，识别值的状态。

二、语法

创建：
create [or replace] trigger 触发器名
before/after
insert/update/delete [of 列名]
on 表名
[for each row [when(条件)]]
declare
...
begin
	PLSQL块
end

删除：drop trigger 触发器名

三、实例

--新员工入职后，输出 "欢迎加入" 字符串。创建触发器
create or replace trigger trig_show_hello
after             --after 表示操作后触发
insert on emp
declare
begin
dbms_output.put_line('欢迎加入');
end;

--插入员工。插入成功后就会触发上面的 trig_show_hello 触发器
insert into emp values(9996,'华安','MANAGER','7698',sysdate,9888.87,300,30);

--更新所有员工的薪水，同一加 100，创建触发器，更新完成后给出提示
create or replace trigger tric_update_sal
after update on emp
for each row --表示行级触发器
declare
begin
   -- :old 表示操作前的记录行，:new 表操作后的记录行
    dbms_output.put_line('原来工资：'||:old.sal|| ' 现在薪水：'||:new.sal);
end;

--更新员工薪水。自动触发上面的 tric_update_sal 触发器
update emp set sal = sal+100;

6.序列+触发器模拟主键自增

-模拟 mysql 中主键 id 的自增属性 auto_increment
--Oralce 中可以使用 序列 sequence 结合 触发器 trigger 达到同样的效果

--先建一张表
create table person2(
pid number(32) primary key,
pname varchar2(16)
);

create sequence person2_id_sequ;--创建一个序列。默认从1开始，每次递增1，没有最大值

--创建触发器
create or replace trigger trig_person2_add_pid
before insert on person2 --在插入数据前触发，因为需要修改 pid 的 null值
for each row  --行级触发器
declare
begin
    dbms_output.put_line('新增员工名称为：'||:new.pname);--打印语句，可以删除
    select person2_id_sequ.nextval into :new.pid from dual;--正式插入前修改新记录的  pid 字段值
end;

--插入用户
insert into person2 values(null,'华安');--插入前触发器会自动通过序列修改 pid 的 null 值为具体的数字
insert into person2 values(2,'华安');--此时自己设置 pid 也是无效的
select * from person2;