SQL 视图添加索引语法 sql视图创建索引

转载

mob64ca1402a190 2024-03-20 13:52:44

文章标签 SQL 视图添加索引语法 sql 数据库 java 数据 文章分类 数据仓库大数据

什么是索引？

索引类型

注意：通过主键约束和唯一约束而建立的聚集索引和唯一索引是不能删除的，该索引用于约束的强制执行。

索引练习

什么是视图

视图的用途

视图练习

什么是索引？

SQL Server中的数据也是按页（一页是4kb）存放。
索引：是SQL Sever 编排数据的内部方法。它为SQL提供一种方法来编排查询数据
索引页：数据库中存储索引的数据页；索引页类似于汉语字典中按拼音或笔画排序成的目录页
索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能

索引类型

唯一索引：不允许两列具有相同的索引值，创建唯一约束时自动创建唯一索引
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑(索引)顺序相同，每个表只能有一个。
非聚集索引(Non-clustered)：非聚集索引指定的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针，每个表可以有多个非聚集索引，最多：249个
单列索引：对表中单个字段建立的索引
复合索引：对表中两个或两个以上字段建立的索引，最多可以组合16个字段，但所有字段必须在同一个表中。

注意：通过主键约束和唯一约束而建立的聚集索引和唯一索引是不能删除的，该索引用于约束的强制执行。

--索引语法

--create [unique][clustered / not clustered] index index_name on table_name(column_name...) [withfillfactor=x]
--            唯一     聚集			非聚集                   索引名			    表名				列名（如果是多列，列明中间用,分隔）		
--fillfactor ：填充因子  ， 指定一个0~100之间的值，表示索引页填充的百分比

use Sales
go
select * from Employees where 姓名='李圆圆'

--创建索引
--使用 是否存在子查 询判断 是否有存在索引：ix_姓名
if exists (select * from sysindexes where name='ix_姓名')
drop index Employees.ix_姓名
go
create unique nonclustered index ix_姓名 on Employees(姓名 desc) with fillfactor=40
go

--设置执行计划，可以看到先使用哪个索引
set showplan_all on  --显示所有的执行计划
set showplan_all off  --关闭，只显示查询结果

--指定使用哪个索引查询
--不指定      select * from Employees where 姓名='李圆圆'
--指定索引的  索引跟在表的后面，如果是多表，则看索引建立在哪个表上，索引就跟在这个表后面
select * from Employees with(index=ix_姓名) where 姓名='李圆圆'



--查看索引
exec sp_helpindex Employees        --查看这个表中所有的索引   列名后面有(-)的是降序，没有的是升序
--ix_姓名	           nonclustered, unique located on PRIMARY	            姓名(-)
--pk_编号	    clustered, unique, primary key located on PRIMARY	    编号


--索引重命名     注意原索引名前要加上表名.
exec sp_rename 'Employees.ix_姓名','ix_name'

--删除索引			索引名前要加上表名.
drop index Employees.ix_name




--索引维护
	--1、查看索引碎片
	DBCC showcontig(Employees,ix_name)       --需要用到表名和索引名，中间用,分隔
	--扫描密度越高，碎片信息越少

	--2、整理索引碎片
	dbcc indexdefrag(Sales,Employees,ix_name)	--需要用到库名、表名和索引名，中间用,分隔

	--3、重建索引
	dbcc dbreindex(Employees,ix_name,60)			--填充因子改为60%

	--作用：提高查询速度
	--创建索引原则（什么样的列适合建索引）：
	--(1)频繁查询的列
	--(2)主键和外键列
	--(3)分组列
	--(4)排序列

	--6、使用命令Update statistics 数据表名  更新数据表Course的索引信息。

	update statistics Course

索引练习

use XK
go

--1、因为Course中的Teacher列经常被查询，为了加快查询速度，现创建索引（IX_Teacher）
--判断，唯一，非聚集，teacher， 降序 ，填充因子：30

if exists (select * from sysindexes where name='IX_Teacher')
drop index Course.IX_Teacher
go
create unique nonclustered  index IX_Teacher on Course(Teacher desc) with fillfactor=30
go

--2、查询“徐人凤”教师所任课程的选课人数。（查看执行计划，了解索引的优化）
--select * from Course 

set showplan_all on
select chooseNum from Course where Teacher='徐人凤'

--3使用命令DBCC  Showcontig（表名，索引名）扫描索引的碎片信息。

dbcc showcontig(Course,IX_Teacher)

--4、使用命令DBCC  indexdefrag（数据库名，数据表名，索引名）整理索引碎片。

dbcc indexdefrag(XK,Course,IX_Teacher)

--5、使用命令DBCC  dbreindex（数据表名，索引名，新的填充因子）重建索引
--将索引IX_Teacher的填充因子修改为60。

dbcc dbreindex(Course ,IX_Teacher,60)

--6、使用命令Update statistics 数据表名  更新数据表Course的索引信息。

update statistics Course

--7、查看Course表的索引。

exec sp_helpindex Course

--8、重命名Course表的索引“IX_Teacher”为“IX_教师”。

exec sp_rename 'Course.IX_Teacher','IX_教师'

--9、删除索引Course表的”IX_教师”索引

drop index Course.IX_教师

什么是视图

视图是一张虚拟表，他表示一张表的部分数据或多张表的综合数据，其结构和数据都是建立在对表的查询的基础上的
数据并不存放在视图中，而是存放在视图所引用的原始表(基表)中，视图是基表数据的一个映射
同一张原始表，根据用户的不同需求，可以创建不同的视图
基表数据发生变化，视图中的数据也发生变化，通过视图可以对及表中的数据进行增、删、查、改

视图的用途

定制数据，通过视图，使不同的用户以不同的方式看到不同/相同的数据
保护数据，防止未经许可的用户访问敏感数据
简化操作，视图中保存的是复杂的查询语句，通过调用查询视图语句，降低数据库访问的复杂程度
合并与分割数据，将多个物理数据表抽象为一个逻辑数据表，或将一个大的数据表进行分隔成多张逻辑数据表，而不用进行数据库表结构的修改。

use Sales
go
--创建视图语法
create view view_name [with encrption] AS <select 语句> [with check option]
--				    视图名    对查询语句的加密    一条查询语句     检查对数据的修改增加删除是否满足select中的条件，满足--->可以修改    不满足--->无法修改

--创建视图
if exists(select * from sysobjects where name='V_采购')     --sysobjects  所有的视图表 看是否存在这个视图
drop view V_采购				--如果存在，删除这个视图
go
create view V_采购       --视图名后面的括号里用来重命名列，可以省略不写，省略表示--->列名使用原列名
as
select 编号,姓名,商品名称,生产厂商,进货价,零售价,数量 from Employees,Goods where Employees.编号=Goods.进货员工编号

--修改视图
alter view V_采购       --视图名后面的括号里用来重命名列，可以省略不写，省略表示--->列名使用原列名
with encryption    --加密创建视图的文本
as
select 编号,姓名,商品名称,生产厂商,进货价,零售价,数量 from Employees,Goods where Employees.编号=Goods.进货员工编号
go

--查看视图创建的文本
exec sp_helptext V_采购
exec sp_helptext V_销售

--对视图信息的增加删除修改
select * from V_采购
delete from V_采购 where 姓名='赵飞燕'   --视图或函数 'V_采购' 不可更新，因为修改会影响多个基表。

update V_采购 set 姓名='赵燕' where 姓名='赵飞燕'   
--由于此修改只影响Employees(单个表)的信息，所以可以修改
--这种修改方式会同步到基表中(原数据表)，也就是说修改了原数据表中的数据

update V_采购 set 姓名='赵燕' , 零售价=零售价+100 where 姓名='赵飞燕'   
--视图或函数 'V_采购' 不可更新，因为修改会影响多个基表。修改两个表的信息，一个员工表一个商品表。所以出错

select * from Employees
update Employees set 姓名='赵飞燕' where 姓名='赵燕' 

--总结：通过视图进行数据的增加、删除、修改时，如果影响多个基表，则不能修改；如果只影响单个表的信息，才可以修改成功



--创建视图：V_男
if exists(select * from sysobjects where name='V_男')
drop view V_男
go
create view V_男      --视图名后面的括号里用来重命名列，可以省略不写，省略表示--->列名使用原列名
as
select * from Employees where 性别=1
with check option
go


select * from V_男
--对视图进行数据插入
insert into V_男(编号,姓名,性别) values ('1401','李四',default)
insert into V_男(编号,姓名,性别) values ('1305','李五',0)			
--试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。



--在视图的基础上可以再创建一个视图
select * from V_采购
if exists(select * from sysobjects where name='V_销售总数量')     --sysobjects  所有的视图表 看是否存在这个视图
drop view V_销售总数量				--如果存在，删除这个视图
go
create view V_销售总数量       --视图名后面的括号里用来重命名列，可以省略不写，省略表示--->列名使用原列名
as
select 姓名, sum(数量) 销售总数量 from V_采购 group by 姓名

--向 在视图基础上 通过 求和计算出的数据列 而组成的新视图中 插入数据
select * from V_销售总数量
insert into V_销售总数量(姓名,销售总数量) values ('李五',30)		--插入出错
--对视图或函数 'V_销售总数量' 的更新或插入失败，因其包含派生域或常量域。

--删除信息
delete from V_销售总数量 where 姓名='赵飞燕'				--视图或函数 'V_销售总数量' 不可更新，因为修改会影响多个基表。

--修改信息
update V_销售总数量 set 销售总数量=销售总数量+10 where 姓名='赵飞燕'   
--对视图或函数 'V_销售总数量' 的更新或插入失败，因其包含派生域或常量域。  

--删除视图
drop view 视图名

--重命名视图
exec sp_rename 'V_StuCou','选课详情'
exec sp_rename 'V_number','选课科目数'

视图练习

use Xk
go

--1、创建视图V_StuCou,显示学生选修课程信息   包括：学号、姓名、课程号、课程名、志愿号
--select * from Student
--select * from StuCou
--select * from Course
--select * from Class

if exists(select * from sysobjects where name='V_StuCou')
drop view V_StuCou				
go
create view V_StuCou
as
select S.StuNo,StuName,SC.CouNo,CouName,WillOrder from Student S,StuCou SC,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and StuNo is not null
go

--2、利用视图查看所有人的选课信息

select * from V_StuCou

--3、查询选课门数大于等于5门的学生学号，姓名

select StuNo 学号,StuName 姓名  from V_StuCou group by StuNo,StuName having count(CouNo)>=5

--4、统计选课门数大于等于5门的学生人数

select count(*) 超过5门的人数 from (select StuNo 学号,StuName 姓名  from V_StuCou group by StuNo,StuName having count(CouNo)>=5 ) 选课人数大于等于5的

--5、创建V_number 显示每个学生的选课门数
--视图里不能包含order by语句   要排序的话，建好视图后，利用视图查询时再排序

if exists(select * from sysobjects where name='V_number')
drop view V_number				
go
create view V_number
as
select S.StuNo 学号,StuName 姓名 ,count(CouNo) 选课门数 from Student S left join StuCou SC on S.StuNo=SC.StuNo group by S.StuNo,StuName
go
--按选课门数升序排序
select * from V_number order by 选课门数

--6、利用V_number 查找没选课的学生信息

select 学号, 姓名 from V_number where 选课门数=0

--7、通过V_number 删除没有选课的学生信息

delete from V_number where 选课门数=0
--视图或函数 'V_number' 不可更新，因为修改会影响多个基表。

--8、修改视图V_number，对其进行加密
alter view V_number with encryption 
as
select S.StuNo 学号,StuName 姓名 ,count(CouNo) 选课门数 from Student S left join StuCou SC on S.StuNo=SC.StuNo group by S.StuNo,StuName
go

--9、查看两个视图的创建文本
exec sp_helptext V_StuCou
exec sp_helptext V_number

--10、重命名视图
exec sp_rename 'V_StuCou','选课详情'
exec sp_rename 'V_number','选课科目数'

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。