马果老师整理
序列: sequence
序列是一种连续的序号,它可以动态产生
- oracle不存在自动增长列,要自动生成主键值,一般采用下列方式:
方式1:使用uuid生成一个32位随机字符串作为主键
- select sys_guid() from dual;
方式2:使用序列生成一组连续的序号
- 序列的创建语法:
create sequence 序列名
start with 1 -------------------序列的初始值从1开始
increment by 1------------------每一次递增1
minvalue 1----------------------最小值为1(如果序列设置有最大值,并且序列可以重复使用
--当序列到达最大值后,会重新恢复成最小值
maxvalue 10--------------------最大值10
cycle -------------------------当前序列到达最大后,可以重新恢复成最小值,重复使用
cache 5------------------------提前创建5个序列号,存放在数据库中,使用时可以直接获取
---创建序列时,有些属性,可以省略不写
---最精简的语法如下:
- create sequence inf_seq
- 默认:初始值为1,递增量为1,没有最小值,没有最大值,不循环使用,不缓存序列号
- 序列名的命名习惯: 表名_seq
--使用序列,生成序列号
- select inf_seq.nextval from dual
---查询当前用户下面有哪些序列
- select sequence_name from user_sequences
---删除序列
- drop sequence inf_seq;
---测试,通过序列,给主键赋值
drop table inf;
create table inf
(
id number(11) primary key,
name varchar2(20),
score number(11)
)
insert into inf values(inf_seq.nextval,'jack',100);
select * from inf;
视图: view
- 视图是数据表查询的结果以另一种方式对数据进行呈现
创建视图的语法
- create or replace view 视图名 as 查询语句;
--示例:
- create or replace view inf_vw as select * from inf
--注意:创建视图,如果权限不足,授权权即可
以sysdba或者是sysoper的身份登录,然后执行下列语句,即可
- grant create view to d91
注意:授权的方式有两种:
方式1、在cmd窗口,用system账号登录,进行授权 (这种方式,每一个同学都可使用)
方式2:使用工具,登录时指定类型为:sysdba或者sysoper
- --注意:如果要使用sysdba的权限,当前windows用户必须要是administrator身份才可以
- --这种方式,授权成功以后,要切换成normal权限,才可以正常操作
查询视图:select * from inf_vw;
问题1:视图本身有没有数据?
- 视图本身是没有数据,它只是呈现数据表中的数据.
问题2:为什么使用视图?
- 使用视图可以将繁琐的语句进行封装,让语句操作起来,更为简单
- 通过视图可以指定数据表中的哪些数据可以显示,哪些数据不能显示
问题3:对视图进行增删改,会不会影响到数据表中的数据?
- 如果是基于单表产生的视图,对视图增删改,就是对数据表的增删改,会影响到数据表的数据
- 如果是基于多表产生的视图,一般只执行查询操作
查询有哪些视图
- select view_name from user_views;
删除视图
- drop view inf_vw; --删除视图,不会影响到数据表的数据
索引
- 索引: index
- 作用:它主要是用于在海量数据查询中,加快查询效率
创建索引的语法:
- create index 索引名 on 表(字段);
- create index inf_idx on inf(name);
create table inf
(
id number(11) primary key,
name varchar2(20),
age number(11)
)
在添加200W条测试数据时,耗时大约1分钟
以及录入测试数据
begin
for i in 1..2000000
loop
insert into inf values(i,'username'||i,23);
end loop;
end;
create index inf_idx on inf(name);
查询数据:
- select * from inf where name='username123' or name='username12345' or name='username123456';
- 情况1:没有索引的情况下,耗时时间为: 0.374
- 情况2:有索引的情况下,耗时时间为: 0.016
问题:使用索引为什么可以加快查询效率?
- 在数据库,查询数据时,默认会用查询条件对数据表中的每一条数据进行筛选,所有数据都匹配一次,才可以得到最终的查询结果,这样导致的问题是:数据表的数据越多,匹配全部数据耗时越长
- 当创建索引时,数据库将会按照一定规则,重新排列数据,将不同的数据,按规则存放一起
- 建索引以后,数据会按规则组织在一起,查询的时候,只会用条件匹配指定区域,不再进行所有数据匹配,由于查询的范围小了,所以速度就快了
注意:并不是所有表都适合建立索引,有些情况下,创建索引,反而会影响到查询效率
不适合建立索引的几种场景:
- 数据表中的数据本身就很少
- 如果建立索引的字段,没有成为查询条件,建立索引毫无意义
- 如果数据表的数据会频繁修改,也不适合建立索引
刚才我们针对于inf表,只创建一个索引: inf_idx,我们查看一下,inf表中,会有几个索引?
在创建数据表时,如果表中指定了主键,系统将会自动针对于主键,创建一个:主键索引
PLSQL
- PLSQL: Procedure Language/Structed Query Language
问题1:什么是plsql语法?
- plsql是一种结构化\过程化的查询语言,它主要用于编写: 存储过程、游标、触发器等对象
plsql语法的基本格式:
declare
----此处,为声明部份,可以在处声明变量、游标等对象
begin
----此处,plsql语句块的主体部份
end;
示例1:基本结构
declare
--声明变量msg,并且给变量赋值
msg varchar2(20) :='hello';
begin
--将变量的值,输出显示
dbms_output.put_line(msg);
end;
- := ----------用于进行赋值
- dbms_output.put_line(变量); ----用于输出显示变量的值,并且换行 (DataBase Management System)
如果没有变量或者游标需要声明,declare可以忽略不写
begin
dbms_output.put_line(12345);
end;
- dbms_output.put(变量);
- dbms_output.put_line(变量);
这两个语法,都是讲指定的内容,进行输出,区别是:
if(条件判断) ---------------------此处的()可以忽略 不写
then 要执行的代码;
end if;
- put():这种方式输出不换行,它只有在换行以后,它输出的内容才可以显示
- put_line(): 这种方式输出会换行,并且会将内容直接进行显示
逻辑判断结构
if结构
语法:
if(条件判断) ---------------------此处的()可以忽略 不写
then 要执行的代码;
end if;
--示例
declare
score number(11):=98;
begin
if score>=60
then dbms_output.put_line('考核通过');
end if;
end;
if...else 结构
语法:
if(条件判断)
then 条件满足时要执行的代码;
else
条件不满足时要执行代码;
end if;
--示例:
declare
score number(11):=58;
begin
if score>=60
then dbms_output.put_line('考核通过');
else
dbms_output.put_line('考核不通过');
end if;
end;
结构3:多重if
语法:
if 条件判断1
then 要执行的代码;
elsif 条件判断2
then 要执行的代码;
elsif 条件判断3
then 要执行的代码;
else
前面条件都匹配不上,最后默认执行的代码
end if;
--示例
declare
score number(11):=98;
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;
结构4:case...when
语法:
case
when 条件1 then 代码1;
when 条件2 then 代码2;
when 条件3 then 代码3;
else
代码4;
end case;
--示例
declare
score number(11):=78;
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;
循环结构
- while循环
- for循环
while循环
语法: while(循环条件)
loop
要执行的代码l;
end loop;
--示例:
declare
i number(11):=1;
begin
while(i<6)
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
for循环
语法:
for i in 开始值..结束值
loop
end loop;
--示例:
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
打印直角三角形
begin
for i in 1..5
loop
for j in 1..i
loop
dbms_output.put('*');
end loop;
dbms_output.put_line('');
end loop;
end;
plsql语句的增删改查
- 注意:增删改与普通的sql语句没有差异,查询有比较大的差异
什么是plsql语句? 什么是普通sql语句?
- 包含在begin...end之间的sql的语句称为:psql语句
- 没有包含在begin..end之间的语句,称为:普通sql语句
plsql语句
begin
insert into inf values(1,'jack',23);
end;
普通语句
insert into inf values(1,'jack',23);
增删改
plsql语句
begin
insert into inf values(1,'张三丰',23);
commit;
end;
普通语句
insert into inf values(2,'李小龙',23);
commit;
-------普通sql语句的查询
select * from inf where id=1;
-------plsql语句的查询
begin
select * from inf where id=1; --此语法是错误语法
end;
/*
在plsql语法中,要执行查询,查询到的每一个字段,都需要定义一个变量去接收
查询的结果,需要用into赋值给变量
注意:今天我们还没有讲到游标,所以不能返回多行数据,今天只能按主键查询,返回一条记录
*/
plsql查询语句的写法:
declare
--定义两个变量,用于保存从数据库中查询到的结果
myname varchar2(20);
myage number(11);
begin
select name,age into myname,myage from inf where id=1;
dbms_output.put_line('姓名:'||myname);
dbms_output.put_line('年龄:'||myage);
end;
问题:如果数据表中有100个字段,难道还需要定义100个变量接收吗?
不需要,在plsql中,有一种特殊的数据类型:
- 变量名称 表名%rowtype;
- r inf%rowtype;
r的类型为inf表中行的类型,inf表中,一行数据包含哪些字段,r中就会包含哪些属性
declare
f inf%rowtype;
begin
select * into f from inf where id=2;
dbms_output.put_line('编号:'||f.id);
dbms_output.put_line('姓名:'||f.name);
dbms_output.put_line('年龄:'||f.age);
end;