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;使用序列，生成序列值 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、增删改查
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;
