oracle中的运算符、序列、视图、索引以及plsql语法

oracle中的函数有:

–日期函数:
1、sysdate
2、add_months
3、months_between
4、last_day
5、next_day
6、trunc
7、extract

–字符函数
1、length
2、ltrim,rtrim,trim
3、upper,lower
4、substr
5、replace
6、lpad,rpad
7、concat

–数学函数
1、floor
2、ceil
3、round
4、sqrt
5、power
6、dbms_random.value
dbms_radnom.string

7、sign

–逻辑判断
1、decode
2、case…when

—转换函数

1、to_date
2、to_char

–分析函数

1、row_number() over(order by 字段)
2、row_number() over(partition by order by 字段)
3、rank() over(partition by order by 字段)
4、dense_rank() over(partition by order by 字段)

—分组函数
1、group by

---分组后设置条件
group by ....having...

—排序函数
order by 字段 asc
order by 字段 desc

—聚合函数
1、count,min,max,avg,sum

几个关键字的顺序是:

   select ... where....group by...having...order by...

–oracle中的运算符

1、算术运算符 + - * /
select name,score-10 from info2;
update info2 set score=score-10

2、连接运算符：||
–作用：用于将多个字符内容，连接成一个完整的字符内容
select ename||‘的收入是’||sal from emp;
select concat(‘姓名是:’,ename) from emp;

3、比较运算符 > >= < <= = != <> in not in between not between

4、逻辑运算符 or and

5、联合运算符
union,union all,intersect(交集)

         union：---连接两张表的数据，相同部份数据，只显示1次，不同部份数据，分别显示
         
         union all:--连接两张表的数据，先显示第一张表的全部数据，再显示第二张表的全部数据
         
         intersect(交集):---只显示两张表中都有的数据

create table one
(
id number(11) primary key,
name varchar2(20),
score number(11)
)
insert into one values(1,‘张三’,100);
insert into one values(2,‘李四’,90);
insert into one values(3,‘王五’,80);

create table two
(
id number(11) primary key,
name varchar2(20),
score number(11)
)
insert into two values(1,‘张三’,100);
insert into two values(2,‘李四’,90);
insert into two values(4,’ 赵六’,70);

–union:
select * from one
union
select * from two

–union all
select * from one
union all
select * from two

–intersect(显示两张表的交集：两张表都存在的数据)
select * from one
intersect
select * from two

---

oracle中，不存在自动增列的概念：
—问题:手动给主键赋值，可能会产生重复值，如何解决该问题?

   如果希望自动给oracle的主键赋值，有下列方式:

–方式1：采用uuid(guid)给主键赋值
create table test1
(
id varchar2(32) primary key,
name varchar2(20)
)

insert into test1 values(sys_guid(),‘张三’);
select * from test1;

–方式2：采用sequence给主键赋值
sequence:序列
–它会自动生成一组连续的序号
–序列可以给主键字段赋值，也可以给普通字段赋值

–创建序列的标准语法:
create sequence 序列名
start with 初始值
increment by 递增的值
minvalue 最小值
maxvalue 最大值
是否循环使用 cycle:循环使用 no cycle 不循环
缓存的序列个数 cache

–创建序列的简化写法
create sequence 序列名;

create sequence inf_seq; 

示例:
create sequence inf_seq
start with 1
increment by 1
minvalue 1
maxvalue 10
cycle
cache 5

使用序列，生成序列值
select 序列名.nextval from dual;
示例：
select inf_seq.nextval from dual;

–查看有哪些序列
select sequence_name from user_sequences;

–删除序列
drop sequence inf_seq;

create table test3
(
id number(11) primary key,
name varchar2(20),
score number
)
insert into test3 values(inf_seq.nextval,‘jack’,98);
select * from test3;

-------------视图
创建语法: create or replace view 视图名 as 查询语句;

视图是基于查询语句的结果来创建，视图本身是没有数据的，它的数据都来自至数据表

使用视图的目的：
1、将繁琐的语句，可能通过视图进行简化

–我们可以基于单表查询创建视图，也可以基于多表查询创建视图

create or replace view test3_vw as select * from test3;
select * from test3_vw;

–用sysdba身份或者是sysoper身份给用户授权，允许用户创建视图

–方式1：直接用plsql工具，切换到sysdba身份，授权
grant create view to d93; --使用这种方式，当前用户，必须要拥windows系统管理员的身份

revoke create view from d93;

–方式2：在cmd模式下，用system账户登录

–如果是基于单表产生的视图，对视图增删改，就是对数据表的增删改
delete from test3_vw where id>2;
select * from test3;
insert into test3_vw values(3,‘andy’,100);

–如果是基于多表产生的视图，一般只能执行查询操作
create or replace view my_vw as
select ename,dname,sal,row_number() over(partition by emp.deptno order by sal desc) 名次
from emp,dept where emp.deptno=dept.deptno;

select * from my_vw;

—查看有哪些视图
select view_name from user_views;

—删除视图
drop view emp_vw;

---

索引：index
作用：用于在海量数据查询中，加快查询效率

create table test4
(
id number(11) primary key,
name varchar2(20),
score number(11)
)
–使用plsql语法，添加200万条数据
begin
for k in 1…2000000
loop
insert into test4 values(k,‘username’||k,99);
end loop;
commit;
end;

–没有索引时的查询时间: 0.328
select * from test4 where name=‘username1998’ or name=‘username123456’ or name =‘username123486’

–创建索引
create index test4_idx on test4(name);

–有索引:0.016

索引的原理:
如果没有索引时，在查询时，需要按条件，把数据表中的每一条数据逐一匹配，才可以得到最终的查询结果
—数据表的数据越多，查询速度也就越慢

       当对表中的某一个字段创建索引后，系统将会按照一定规则，重新排序数据
       ---查询数据时，只会在指定范围中查询，查找完成以后，不再查找其他范围，由于匹配的数据少了，速度自然
       就会变快

注意：并不是任何场景都适合创建索引

以下几种情况，不适合创建索引:

– 1、数据表的数据本身就很少，就不要创建索引

– 2、如果创建索引的字段，没有成为查询条件，创建索引，没有任何意义

– 3、如果数据表中的数据会频繁的修改、删除，也不适合创建索引

create table test5
(
id number(11) primary key
)

---

PL/SQL语法：

Procedure  Language :过程化的语言

Structed Query  Language:结构化查询语言

pl/sql: 它是一种结构化、过程化的查询语言

    plsql主要是用于编写: 函数、游标、存储过程、触发器

---

–plsql语法的基本格式：

declare
–声明部分：用于声明：变量、游标、常量…
begin
—plsql代码块的主体
end;

---

示例:
declare
score number(11):=100; --定义一个变量，名称为score,它的值是100
begin
dbms_output.put_line(score);
end;

–如果没有变量需要声明，declare可以忽略不写

begin
dbms_output.put_line(‘hello’);
end;

----------plsql中，可以编写哪些内容?
1、可以使用循环结构：
a、for…
b、while…

2、可以使用逻辑判断结构
a、if
b、if…else
c、if…elsif…else
d、嵌套if
e、case…when

3、增删改查

---

—while循环结构的语法:
while(循环条件)
loop
要执行的操作;
end loop;

declare
i number(11):=1;
begin
while i<6
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

begin
for k in 1…10
loop
dbms_output.put_line(k);
end loop;
end;

---

–逻辑判断：

–1、if…else
----在plsql语法中，使用了if…就必须使用end if结束判断

declare
score number(11):=48;
begin
if(score>=60)
then dbms_output.put_line(‘考试合格’);
else
dbms_output.put_line(‘考试不合格’);
end if;
end;

---

–2、if…elsif…else …end if;

declare
score number(11):=78;
begin
if(score>=90)
then dbms_output.put_line(‘优秀’);
elsif(score>=80)
then dbms_output.put_line(‘良好’);
elsif(score>=60)
then dbms_output.put_line(‘合格’);
else
dbms_output.put_line(‘不合格’);
end if;

end;

---

–case…when

declare
score number(11):=98;
begin
case
when score>=90 then dbms_output.put_line(‘优秀’);
when score>=80 then dbms_output.put_line(‘良好’);
when score>=60 then dbms_output.put_line(‘合格’);
else dbms_output.put_line(‘不合格’);
end case;

end;

---

put与put_line的区别:

put:–它输出内容，但不换行。输出的内容不能直接显示，只有遇到换行标记后才显示

put_line();-------它输出的内容，会换行显示出来

---

begin
dbms_output.put(‘hello’);
dbms_output.put(‘world’);
dbms_output.put_line(’’);
end;

begin
dbms_output.put_line(‘hello’);
end;

---

begin
for i in 1…10
loop
for k in 1…i
loop
dbms_output.put(’*’);
end loop;
dbms_output.put_line(’’);
end loop;
end;

---

plsql语法增删改查

–plsql语法中的增删改与普通sql的增删改，没有任何区别,查询有区别

问题：什么是 plsql语句，什么是普通sql语句?

答：包含在begin…end之间的语句，就是plsql。没有包含在begin…end之间的sql就是普通sql

select * from test3;

–普通sql
insert into test3 values(4,‘chris’,96);

–plsql语句
begin
insert into test3 values(5,‘cindy’,96);
end;

---------查询---------------------------------------
select * from test3;

–此代码是错误代码
/*
在plsql语句中，要执行查询，select 语句，必须配合 into语句一起使用
在plsql语法中，查询出的每一个字段，都必须定一个变量来接收
*/
begin
select * from test3;

end;

–正确的查询语法
declare
myname varchar2(20);
myscore number(11);
begin
select name,score into myname,myscore from test3 where id=5;
dbms_output.put_line(‘姓名:’||myname);
dbms_output.put_line(‘成绩:’||myscore);
end;

—使用行变量，接收返回的数据
declare
r test3%rowtype;
begin
select * into r from test3 where id=5;

   dbms_output.put_line('编号:'||r.id);
   dbms_output.put_line('姓名:'||r.name);
   dbms_output.put_line('成绩:'||r.score);

end;