SQL查询的艺术学习笔记--数据索引

/*
create database idx
on 
(
name=idx,
filename='d:\data\idx.mdf',
size=100MB,
maxsize=1000MB,
filegrowth=5%
)
log on
(
name=idx_log,
filename='d:\data\idx.ldf',
size=100mb,
maxsize=1000MB,
filegrowth=5%
)
--创建基本数据库代码完成
--创建实例中的三张表（studentinfo  recruitinfo  department)
use idx
create table studentinfo
(
sno char(3) not null,
sname char(200) not null,
sex char (2) not null default '男',
address char(300) not null,
dno int  not null
)
select * from studentinfo order by sno
insert into studentinfo values ('001','张平','女','湖南QQ','2')
insert into studentinfo (sno,sname,sex,address,dno) values ('002','李山','男','北京','4')
insert into studentinfo (sno,sname,address,dno) values ('003','张伟','浙江','1')
insert into studentinfo (sno,sname,address,dno) values ('004','高守传','山东','3')
insert into studentinfo (sno,sname,sex,address,dno) values ('003','王彤','女','湖北','2')
insert into studentinfo (sno,sname,sex,address,dno) values ('006','刘小静','女','山东','6')
insert into studentinfo (sno,sname,sex,address,dno) values ('008','刘红','女','河南','5')
insert into studentinfo (sno,sname,address,dno) values ('007','张勇','河北','6')
insert into studentinfo (sno,sname,address,dno) values ('009','吴军','山西','4')
insert into studentinfo (sno,sname,address,dno) values ('010','张大山','陕西','7')
update studentinfo set dno=4 where sno=006
update studentinfo set sno='004' where sname='张伟'
update studentinfo set sno='003' where sname='王彤'
update studentinfo set sno='005' where sname='高守传'
update studentinfo set address='湖南' where sno='001'
--生成招生信息表
create table recruitinfo
(
address char(200) not null,
score float not null,
snum int not null
)
insert into recruitinfo values ('浙江',638,76)
insert into recruitinfo values ('北京',560,220)
insert into recruitinfo values ('湖南',648.5,65)
insert into recruitinfo values ('湖北',654,85)
insert into recruitinfo values ('山东',650,80)
insert into recruitinfo values ('河南',629.5,72)
insert into recruitinfo values ('河北',625,58)
insert into recruitinfo values ('山西',631,55)
insert into recruitinfo values ('陕西',635,62)
select * from recruitinfo
--生成院系表
create table department
(
dno int not null,
dname char(100) not null,
dnum int not null
)
insert into department values (1,'计算机工程系',220)
insert into department values (2,'汽车系',80)
insert into department values (3,'机械工程系',120)
insert into department values (4,'电子工程系',180)
insert into department values (5,'工程物理系',40)
insert into department values (6,'应用数学系',50)
insert into department values (7,'材料工程系',60)
select * from department order by dno
*/
--创建索引部份
use idx
select * from studentinfo
create index name_index  on studentinfo (sname)
--单字段非簇索引的创建：create index 索引名 on 表名（列名）
select sname from studentinfo
select * from studentinfo with (index(name_index))
--上一句查询是强制使用了我们刚才做的索引：nmae_index进行数据查询
create index sexname_index on studentinfo(sex,sname)
--索引字段例的排名决定了查询排序的优先级，在这里sex高于sname
select * from studentinfo with (index(sexname_index))
--上面实例可以解决我们用排序中的一些问题，即同时对两个例进行索引排序）
create unique index sno_index on studentinfo (sno desc)
--创建唯一索引 unique index 字段：sno 降序
select * from studentinfo with (index(sno_index))
--创建唯一索引后：测不能插入唯一索引字段重复值
insert into studentinfo values (010,'熊猫','男','四川',7)
--这条语句会执行成功，但是010处理为10
select * from studentinfo
update studentinfo set sno='010' where sname='熊猫'
--不成功，010和唯一索引有冲突
update studentinfo set sno='011' where sname='熊猫'
create unique index add_index on studentinfo(address)
--有重复字段索引创建不成功
insert into studentinfo values('012','林小水','女','西南','3')
select * from studentinfo
update studentinfo set sname='林大水' where sno='012'
delete studentinfo where sno='012'
--以上为非簇索引，下面我们创建簇索引(单字段）：
create clustered index  sname_cindex on studentinfo(sname)
select * from studentinfo
--索引的销毁操作：
drop index studentinfo.sname_cindex   
--drop index 表名.索引名
--创建索引（多字段）
create clustered index  sexadd_cindex on studentinfo(address,sex)
select *  from studentinfo
drop index studentinfo.sexadd_cindex
--sql  删除索引需要指定表名
--数据表视图：
create view studentinfo_view as select * from studentinfo
select * from studentinfo_view
--创建简单视图
create view oth_view as select * from studentinfo where sex='男'
--通过查询创建一个视图
select * from oth_view
create view sd_view as select sname,address from studentinfo
select * from sd_view
--创建不同字段名视图：
create view bt_view(bname,baddress,bsex) as select sname,address,sex from studentinfo where sex='男'
select * from bt_view
--利用视图可以简化表中复杂的连接
create view join_view
as 
select studentinfo.sname,studentinfo.address,studentinfo.sno,dname,score from studentinfo,department,recruitinfo
where studentinfo.address=recruitinfo.address
and studentinfo.dno=department.dno
drop view join_view
select * from join_view order by sno
select * from studentinfo
--利用创解过滤信息视图，再通过对视图集合操作得到结果集
create view boy_view
as 
select * from studentinfo where sex='男'
select * from boy_view
--从studentinfo表创建所有男生信息
create view score_view(sno,sname,sex,address,dno)
as
select studentinfo.* from studentinfo,recruitinfo 
where studentinfo.address=recruitinfo.address
and recruitinfo.score>630 and studentinfo.sex='男'
drop view score_view
select * from score_view
--多表查询出学生成绩大于630的学生信息
create view bc_view
as
select * from score_view 
where sno in (select sno from boy_view)
--通过查询两个视图创建满足集合另一视图
select * from bc_view
create view rbc_view(sname,dname)
as 
select bc_view.sname,department.dname from bc_view,department where bc_view.dno=department.dno
select * from rbc_view
drop  view rbc_view