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