学习大数据DAY09 SQL基础语法9

目录

索引视图序列同义词

索引

索引的分类

创建索引

修改索引

删除索引

作业

索引建立或使用的规则与建议

建立索引的优点

索引的缺点

SQL 性能优化问题

exists 简单使用方法

exists 和 in 的区别

带索引分析 exists 和 in 的区别

结论

视图

序列

应用序列填充主键

作业 2

建一个超市管理系统表

---

SQL 基础语法（八）

索引视图序列同义词

索引

在对包含很多行的表进行检索其中几行数据时，都应该创建索引，以加快检索速

度。

1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据

库中索引

可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索

引可以

不用翻阅整本书即可找到想要的信息。

2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来

取代默认

的全表扫描检索方式，从而提高检索效率

3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引

时，不会

影响基本的表；

4）索引一旦建立，会自动管理索引，索引删除，不会对表产生影响

5）oracle 创建主键时会自动在该列上创建索引

索引的分类

一、按存储形式（即索引中存储的内容不同）

二、按唯一性（索引列中的数据是否有重复值）

三、按列的个数（索引覆盖的列的个数）

一、 按存储形式（即索引中存储的内容不同）：

1）B-TREE 索引(索引列原始数据+ROWID)

2）位图索引(位图+ROWID)

3）反向键索引(索引列原始数据的反向存储+ROWID)

4）基于函数的索引(将索引列原始数据经函数处理后存储+ROWID)

索引的命名规范：IND_TBNAME_COLNAME

1.B-TREE 索引 BALANCE-TREE

（ORACLE 的默认索引类型，工作中最常见、使用范围最广的索引）

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

适用场景：列基数比较大的时候使用（行业、身高）

列基数：该列不重复数据的个数 COUNT(DISTINCT COL) 2.位图索引 （位图+ROWID)

说明：位图索引在创建时，会扫描整张表，为索引列的每个取值建立一个不重复

的位图

（BITMAP）来描述该取值

语法：CREATE BITMAP INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE BITMAP INDEX IND_STUDENT54_SEX ON STUDENT_54_NEW(SEX);

适用场景：列基数比较小的时候使用（性别、婚姻状况）

3.反向键索引（简称：反向索引）

说明：可以视作一种特殊的 B-TREE 索引，存储索引列的反向值

背景：为防止 B-TREE 索引在某叶上数据量占比过高而使用的一种索引

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME) REVERSE;

适用场景：原始数据分支不明显但反向数据分支明显的列（身高：集中在一米七

一米

八）

4.基于函数的索引

说明：可以视作一种特殊的 B-TREE 索引，存储函数处理后的数据

背景：在某个字段上以原值建立了索引，但是在筛选时在该字段上经常加函数，

导致索

引无法生效

语法：

CREATE INDEX IND_NAME ON TB_NAME(FUNCTION(COL_NAME));

适用场景：对某列进行筛选时经常需要配合函数使用（例如查找姓名中的首字母）

二、按唯一性（索引列中的数据是否有重复值）

1.唯一索引 --索引列中不可能出现重复值

语法：

CREATE UNIQUE INDEX IND_NAME ON TB_NAME(COL_NAME);

注意点：

1）B-TREE 索引可以建立唯一索引，位图索引不能建立唯一索引

2）如果在某列上建立了唯一约束或主键约束，ORACLE 会自动在该列上建立一个

同名的

唯一索引

2.非唯一索引 --索引列中可能出现重复值

语法：CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

三、按列的个数（索引覆盖的列的个数）

1.单列索引 --基于一个列建立的索引

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

2.复合索引(也称为联合索引） --基于两个或两个以上列建立的索引

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME1,COL_NAME2...);

创建索引

Create [unique|BITMAP] index 索引名称 on 表名（列名[，列名]） tablespace

表空间

名；

Unique 用于指定是否强制要求索引列为唯一性数据，表空间可选择是否指定，

不指定则

用默认表空间。出于性能考虑索引表空间和表的表空间要分开。

修改索引

Alter index 索引名 rename to 新索引名；

删除索引

通过 drop index 索引名；

--简单索引，给 emp 表的 ename 列创建一个索引，建立索引前后时间对比

select * from emp where ename like 'S%'

create index IX_name on emp(ename);

select * from emp where ename like 'S%'

--复合索引

create index IX_sal_job on emp(sal,job)

select ename,job,sal,mgr from emp order by job,sal

--基于函数的索引

create index IX_ename_upper on emp(upper(ename))

select * from emp where upper(ename)='SMITH'

--位图索引此版本数据库未开启

create bitmap index index_sno on student(sno);

--修改

alter index IX_ename_upper rename to IX_ename_upper1

--删除

drop index IX_ename_upper1

作业

--1.给 emp 表的 ename 列创建一个索引，建立索引前后时间对比
select ename from emp; --0.138 秒
create index i_emp_ename on emp(ename);
select ename from emp; --0.026 秒
--2.创建 job 和 sal 的复合索引，查询工资大于 2000 的 MANAGER 员工信息
create index IX_sal_job on emp(sal,job);
select * from emp
where sal>2000 and job='MANAGER';
--3.查询所有人选修 c002 课程及格的情况,自己判断如何创建索引create index sc_index on sc(sno,cno,score);
select sno,cno,score,
case when score>=60 then '及格'
else '不及格'
end as 及格情况 from sc
where cno='c002';
--4.使用小写函数来创建索引，查询带有 m 的员工信息
create index i_ename on emp(lower(ename));
select * from emp
where instr(lower(ename),'m',1,1)>0;
--5.修改删除一个索引
alter index IX_sal_job rename to IX_SJ;
drop index IX_SJ;

索引建立或使用的规则与建议

1.如果对某大表进行筛选时，某列或某几列频繁出现在 WHERE 子句中，并且检索

出的数据低于总行数的 15%（50%），应考虑在这些列上建立索引。

2.如果对某大表进行排序时，某列或某几列频繁出现在 ORDER BY 子句中，应考

虑在这些列上建立索引。

3.小表不要建立索引。

4.对于含有空值的列，如果经常在查询时查询非空值，建议在该列上建立索引；

如果经常在查询时查询空值，建议在该列上建立基于函数的索引。

5.为了提高表连接的性能，应在连接列上建立索引（建立一般普通的索引即可）

6.索引是数据库的一种实体对象，级别类似于表，会占用内存空间，ORACLE 会

自动进行索引维护，表和索引可以建立在不同的表空间。

7.通过索引可以提升数据的查询速度，但是会相对地降低 DML 语句的操作速度，

尤其是插和改的速度，ORACLE 会花费时间在索引维护上，所以说要把握好索引

的数量

8.对于列基数比较大的列，适合 B-TREE 索引，列基数比较小的列，适合位图索

引。

9.对于复合索引，至少要引用到索引列中的第一个列才会使用该索引。

10.某列可以出现在多个索引中，但相同的某列或某几列无法多次建立索引。 --

就是可以有多个组合，但不能有重复组合，不同顺序的相同几列视为不同组合

11.索引建立后并不一定会被引用，ORACLE 会分析整个 SQL 后做出最优的执行方

式。

12.ORACLE 会自动在主键约束和唯一约束列上建立唯一索引。

13.对于一般的 B-TREE 索引，通配符出现在搜索词的首位时不会引用索引

14.在索引列上使用<> !=号时，或对空值进行判断时，索引不会生效

15.rebiuld 重建索引，减少碎片，提高效率。

建立索引的优点

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时

间。

索引的缺点

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了

数据的

维护速度。

SQL 性能优化问题

一、SQL 优化

SQL 执行顺序 from——where——group by——having——select——order by

From 后面的表执行顺序从右往左，从后往前

1、 如果结果集没有影响的关联，将小的表放在后面

2、 Where 条件顺序，将过滤条数大的放在后面，过滤条数小的放在前面

3、 尽量减少对表的重复查询

4、 使用 exists 代替 in：in 后面用子查询，用 exists 代替 in（看 exists 子

查询中 where 条件，结果返回 true 或者 fasle），如果 in 后面是具体的值，还

是用 in，用 in 的 SQL 语句总是多了一种转换过程

5、 Distict,查询效率低，要先排序，再去重

6、 索引正确使用，不能使用聚合函数，不能使用 not

7、 大于等于效率要高于大于，用>=替代>，前提是整数相比较

8、 Like 效率低，使用 instr 代替 instr(name,’n’)>=1 可以代替 like‘%c%’

9、 Where 是过滤行，having 对分组的过滤

10、 要查看执行计划(F5, EXPLAIN )

11、 对 WHERE + ORDER BY 组合的优化, 在 where 进行限制后 在进行 order BY

12、 尽量少排序 ORDER BY

13、 任何地方都不要使用 select from 表，用具体的字段列表代替“ ”，不要返

回用不到的任何字段

14、 尽量用 JOIN 替换子查询

15、 尽量少使用 OR ,索引失效

16、 尽量避免 UNION 使用 UNION ALL 然后再 GROUP BY 去重

17、 尽早过滤数据, WHERE 过滤 使用 join 时 先过滤再 JOIN

18、尽量避免一条 UPDATE 更新多条记录, 用 MERGE INTO , 效率比 UPDATE 高

19、 尽量使用 TRUNCATE 替换 DELETE

20、 如果 时间列 只需要精度到天,则尽量使用 date 类型, 不使用 时间戳

21、 尽量保证在相同字段类型的比较

22、 使用 group by 去重后计数

23、 尽量使用 nvl()去空值 exists

exists 的右操作数是一个子查询，这个子查询是用来做存在性检查的，

exists() 子查询有结果 返回 逻辑 1

exists() 子查询没有结果 返回 逻辑 0

若子查询的行存在结果，即子查询能够找到匹配的记录，exists 的结果为 ture，

否则为

false

使用 exists 时，若子查询有结果，则返回 true，外表能够提取查询数据

使用 not exists 时，若子查询找不到匹配记录，则返回 true，外表能够提取查

询数据。

exists 简单使用方法

select * from emp where exists(select ename from emp where deptno>10);

exists 和 in 的区别

IN 适合于外表大而内表小的情况;EXISTS 适合于外表小而内表大的情况,这样效

率会高的。

select * from emp where exists(select deptno from dept where deptno>10)

and

deptno>10;

select * from emp where deptno

in(select deptno from dept where deptno>10)

select * from emp where

exists(select deptno from dept where deptno>10 and

dept.deptno=emp.deptno)

带索引分析 exists 和 in 的区别

in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop

循环再对内表进

行查询，一直以来认为 exists 比 in 效率高的说法是不准确的。如果查询的两个

表大小相

当，那么用 in 和 exists 差别不大；如果两个表中一个较小一个较大，则子查询

表大的用

exists，子查询表小的用 in；

例如：表 A(小表)，表 B(大表)

select * from A where cc in(select cc from B)

-->效率低，用到了 A 表上 cc 列的索引；

select * from A where exists(select cc from B where cc=A.cc)

-->效率高，用到了 B 表上 cc 列的索引。

相反的：

select * from B where cc in(select cc from A)

-->效率高，用到了 B 表上 cc 列的索引

select * from B where exists(select cc from A where cc=B.cc)

-->效率低，用到了 A 表上 cc 列的索引。 带索引分析 not in 和 not exists

not in 逻辑上不完全等同于 not exists，如果你误用了 not in，小心你的程序

存在致命 BUG，请看下面的例子：

create table t1(c1 int ,c2 int );

create table t2(c1 int ,c2 int );

insert into t1 values ( 1 , 2 );

insert into t1 values ( 1 , 3 );

insert into t2 values ( 1 , 2 );

insert into t2 values ( 1 , null );

select * from t1 where c2 not in ( select c2 from t2);

-->执行结果：无

select * from t1 where not exists ( select 1 from t2 where t2.c2 = t1.c2)

-->执行结果：1 3

结论

正如所看到的，not in 出现了不期望的结果集，存在逻辑错误，所以，请尽量

不要使用 not in(它会调用子查询)，而尽量使用 not exists（它会调用关联子

查询）。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。

如果子查询字段有非空限制，这时可以使用 not in。

如果查询语句使用了 not in，那么对内外表都进行全表扫描，没有用到索引；

而 not exists 的子查询依然能用到表上的索引。所以无论哪个表大，用 not

exists 都比 not in 要快。

视图

视图实际上是一个或多个表的预定义查询，视图的使用方法和表一样。视图不存

储数

据，他们只访问基表中的行。

创建视图

Create or replace view 视图名 as select * from 表名 with read only

Drop view 视图名；删除视图

scott 账号要使用 system 账号赋予权限才能创建视图

grant create any view to scott

create or replace view VW_emp as

select ename,dname from emp inner join dept1 on emp.deptno=dept1.deptno

select * from VW_emp

drop view VW_emp;

--视图只是一个连接

--创建一个视图 v_emp1，查询 emp 的信息

create view v_emp1 as select * from emp;

select * from v_emp1;

--创建一个只读视图 v_emp2，查询 emp 的信息 create view v_emp2 as select * from emp WITH READ ONLY;

select * from v_emp2;

--在 v_emp1 中插入一条数据

insert into v_emp1 values (8998,'老师','salesman',

7888,to_date('2022/02/28','YYYY/MM/DD'),5000,'',30)

update v_emp1 set sal=3000 where ename='老师'

delete from v_emp1 where empno=8998

select * from emp

select * from v_emp1

--在 v_emp2 试图去修改刚刚插入的数据

update v_emp2 set sal=3000 where ename='老师'

select * from v_emp2

注：就算是非只读视图，不满足表结构的特点也不能修改数据，一般视图不建议

增删改

序列

序列是一个数据库项，用于生成一个整数序列，生成的序列用来填充数字型主键

列。

创建序列

Create sequence 序列名

Start with 开始数字

Increment by 增量数

Maxvalue 最大数|nomaxvalue

Minvalue 最小数|nominvalue

应用序列填充主键

Insert into 表名

（主键，列名，列名）values（序列名.nextval,’’,’’）

Insert into 表名

（主键，列名，列名）values（序列名.currval,’’,’’）

一条 sql 语句获取一次序列值，和 nextval 调用次数无关

create sequence my_seq

start with 1

increment by 2;

select my_seq.nextval from dual;

select my_seq.currval from dual;

insert into emp values(my_seq.nextval,'test','salesman',

7888,to_date('2022/02/28','YYYY/MM/DD'),5000,'',30)

select * from emp;

--把序列的 increment by 的值改为 100

alter sequence my_seq increment by 100;

--取序列的下一个值

select my_seq.nextval from dual;

作业 2

--1.创建一个视图，查询出来 emp 表的所有数据，并用 insert into 给这个插入一
条数据
create view V_emp
as
select * from emp;
--查看视图
select * from V_emp;
insert into V_emp
values(8000,'STONE','STUDENT',7839,to_date(20240704,'yyyymmdd'),1500,500,10)
;
--2.创建一个查询 emp 表所有部门平均工资的视图，并试图去修改这个视图的数
据
create view avgsal
as
select avg(sal) as 平均工资 from emp
group by deptno;--数据不相同改不了的，试过了
--3.创建一个只读视图，查询学生的平均成绩，并试图去修改数据
create view avgsc
as
select avg(score) as 平均成绩 from sc with read only;--只读视图不可以改
--4.创建一个序列，从 1 开始，每次增长 1
create sequence emp_X
start with 1
increment by 1 ;
--5.使用序列，把序列是 8 的插入到 emp 表中作为主键
select emp_X.nextval from dual;--增加序列号
insert into emp
values(emp_X.nextval,'STONE','STUDENT',7839,to_date('2024/07/04','yyyy/mm/dd')
,1500,500,10);
--6.修改序列增长值为 10，把序列是 38 的插入到 emp 表中作为主键
alter sequence emp_X increment by 10 ;
select emp_X.nextval from dual;--增加序列号
Insert into emp
values(emp_X.nextval,'STONE','STUDENT',7839,to_date('2024/07/04','yyyy/mm/dd')
,1500,500,10);

建一个超市管理系统表

大数据培训第一阶段最终学习需要。

--建立字典表
create table t_dic(
t_type varchar2(10)，
t_name varchar2(20)，
t_key varchar2(10) primary key，
t_value varchar2(20) not null
);
--创建货物表
create table goods(
g_id
varchar2(32) primary key,
g_type varchar2(10) references t_dic(t_key),
g_name varchar2(100),
inprice number(10,2),
outprice number(10,2),
tjdw
varchar2(10) references t_dic(t_key),
qualitydate number(4) check(qualitydate>0)
);
--建立货物库存表
create table stock(
s_idvarchar2(32) primary key,
goodsid varchar2(32) references goods(g_id),
sl number（4）,
intime date default(sysdate),
productime date
);
--建立进账表
create table income(
i_id varchar2(32) primary key,
goodsid varchar2(32) references goods(g_id),
stockid varchar2(32) references stock(s_id),
sl number（4）check(sl>0),
discountnumber（4,1）,
allprice number（8,1）,
outtime date default(sysdate)
);
--建立出账表create table oncome(
o_id
varchar2(32) primary key,
goodsid varchar2(32) references goods(g_id),
stockid varchar2(32) references stock(s_id),
sl number(4,1) check(sl>0),
allprice number（8,1）,
intime date default(sysdate)
);

我嫌用代码加入数据太麻烦了，直接将 excel 表导入数据。

这里分享一下导入教程。（PL/SQL Developer 版。）

在 table 编辑用代码建好的表：

点击查询：

复制 excel 表中的内容，注意，第一列要为空。

粘贴后按照图中序号执行：1.保存。 2.提交 3.锁定

本次用的 excel 文件在这里。

链接：https://pan.baidu.com/s/1ngxRKPoOHnsk3zL_MM8c4g

提取码：awsl