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;