sql 索引视图建立删除

最新推荐文章于 2022-03-13 01:41:06 发布

原创最新推荐文章于 2022-03-13 01:41:06 发布 · 183 阅读

0 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何在SQL中创建唯一索引、非聚集索引及视图，并演示了增删改查操作和约束的添加与移除。通过具体实例展示了索引的创建与删除、视图的创建与查询等过程。

--创建索引
create unique index uni on bank (customerName) --创建表bank上的唯一索引，索引名uni与表中的customerName列对应
drop index bank.uni --删除表bank的索引名为uni的索引
--创建非聚集索引 fillfactor表示填充因子，指定一个0~100的值，表示索引页填满的空间所占百分比
create nonclustered index nonclu on bank(currentMoney) with fillfactor=30 
drop index bank.nonclu
--按照指定的索引查询
select * from bank
(index=nonclu)
where currentMoney=80

insert into bank (customerName,currentMoney)
select '飞鸟',102
union select '假面',150
union select '娟娟',104

select * from bank 
select * from stuInfo_1
go

alter table stuInfo_1
add constraint pk_name primary key (stuNo)

alter table stuInfo_1
drop constraint pk_name

alter table bank 
add constraint pk_name1 primary key(bank_id)

alter table bank 
drop constraint pk_name1

alter table bank
add constraint fk_name foreign key(bank_id)references stuInfo_1(stuNo)

alter table bank
drop constraint fk_name

drop table bank

create table bank(
bank_id varchar(8) not null,
customerName varchar(20) not null,
currentMoney money
)

insert into bank(bank_id,customerName,currentMoney)
select 's253002','feiniao',120
union select 's2530001','jiamian',210
union select 's2530006','都都',112
union select 's2530004','飞鸟',210
union select 's2530005','假面',150
union select 's2530003','娟娟',140

select * from bank
select * from stuInfo_1
select * from view_bank_stuInfo_1
go

drop view view_bank_stuInfo_1 --删除视图

--创建视图(视图一般是仅作为查询使用)
create view view_bank_stuInfo_1
as 
select 姓名=stuName,stuNo,stuSex,stuAge,stuSeat,stuAddress,currentMoney from stuInfo_1 inner join bank on bank_id=stuNo