如何创建索引
使用T-SQL语句创建索引的语法：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX  index_name
ON table_name (column_name…)
[WITH FILLFACTOR=x]
q      UNIQUE表示唯一索引，可选
q      CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选
q      FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比
在stuMarks表的writtenExam列创建索引：
USE stuDB
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_writtenExam')
DROP INDEX stuMarks.IX_writtenExam
/*--笔试列创建非聚集索引：填充因子为30％--*/
CREATE NONCLUSTERED INDEX IX_writtenExam
ON stuMarks(writtenExam)
WITH FILLFACTOR= 30
GO
/*-----指定按索引IX_writtenExam查询----*/
SELECT * FROM stuMarks (INDEX=IX_writtenExam)
WHERE writtenExam BETWEEN 60 AND 90
  动作  　使用聚簇索引  　使用非聚簇索引
  列经常被分组排序  　应  　应
  返回某范围内的数据  　应  　不应
  一个或极少不同值  　不应  　不应
  小数目的不同值  　应  　不应
  大数目的不同值  　不应  　应
  频繁更新的列  　不应  　应
  外键列  　应  　应
  主键列  　应  　应
  频繁修改索引列  　不应  　应
-
---通配符
phone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
stuName like Name[^0-9]---不包含0-9
----通过insert select将现有表数据添加到新表中
----需要预先创建表
insert into table1(id,name,sex)
select id,name,sex from table2
----通过select into将现有表数据添加到新表中
---不用预先创建表
select table1.id,table1.name,table1.sex into table2 from table1
----将现有表数据添加到新表中，并在过程中修改..
select identity(int,1,1) as table1.id,table1.name,table1.sex into table2 from table1
----一次插入多行
insert  student(name,address)
select 'aa','hubei' union
select 'aa','hubei' union
select 'aa','hubei'
----删除表数据
truncate table tableName
----删除表结构
delete table tableName
-----添加主键约束
alter table student add constraint pK_stuNo primary key(stuNo)
-----添加唯一约束
alter table student add constraint UQ_stuNo UNIQUE(stuNo)
-----添加默认约束
alter table student add constraint DEFAULT('地址不详') for stuAddess
-----添加检查约束
alter table student add constraint check(stuAge BETWEEN 18 and 40)
-----添加外键约束
alter table student add constraint FK_stuNo foreign key(stuNO) references stuInfo(stuNo)
-----删除约束
drop constraint [约束名称]
-
---声明变量
declare @stuNo int
select top 1 @stuNo=stuId from  student
----常见输出
print @@error
select @@error as errorID
print @@server
name
----类型转换
print '当期错误号:'+convert(varchar(5),@@error)
------if else应用
declare @stuAvg float
set @stuAvg=60
if(@stuAvg>70)
begin
print 'if'
end
else
begin
print 'else'
end
------while用法
-
---demo
declare @n int
while(1=1)
begin
select @n=count(*) from student where stuMark<60
if(@n>0)
update student set stumark=stumark+10
else
break
end
----自定义的(有误)
declare @count int
while(1=1)
begin
select @count=count(stuid) from student where stuid%2=0
if(@count>0)
begin
update student set sex='女'
continue
end
else
break
end
-----case分支语句
print '成绩分级如下：'
select stuNo，成绩=case
when stuMark<60 then '差'
when stuMark between 60 and 70 then '良'
else '好'
end
from student
----高级查询
----没有外键的表联合查询
select * from student,sheng,shi where student.stusheng=sheng.shengid and student.stushi=shi.shiid
----事物
begin transaction
declare @error int
set @error=0
insert into student values('李双','sdf',1,'','')
set @error=@error+convert(int,@@error)
if @error<>0
begin
print '操作失败'
rollback transaction
end
else
begin
print '操作成功'
commit transaction
end
-----创建索引
if exists(select name from sysindexes where name='IX_stuAddress')
drop index student.IX_stuAddress
create nonclustered(clustered)(unique) index IX_stuAddress
on student(stuAddress)
with fillfactor=30
go
-----按索引查询
select * from student(index=IX_stuAddress) where stuaddress like '%湖北武汉%'
--------索引
USE stuDB
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_writtenExam')
DROP INDEX stuMarks.IX_writtenExam 
create index Affiliation.IX_mobile
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_address')
DROP INDEX Affiliation.IX_address 
CREATE NONCLUSTERED INDEX IX_address
ON Affiliation(Address)
WITH FILLFACTOR= 30
go
select id,address from affiliation where address like '%广州%'
go
declare @d datetime
set @d=getdate()
select * from Affiliation
select [语句执行时间(毫秒)]=datediff(ms,@d,getDate())
-------视图
create view view_name
as
select * from student
go
-----使用视图
select * from view_name
-----存储过程
if exists(select name from sysobjects where name='proc_name')
drop procedure  proc_name
foreign key references用法create procedure selectStuinfo
@stuMark int,
@stuSheng int
as
select * from student
go
exec proc_name 60,1
--exce proc_name @stuMark=60,@stuSheng=1
----存储过程带默认输入参数
if exists(select name from sysobjects where name='proc_name')
frop procedure proc_name
create procedure selectStuinfo
@stuMark int=60,
@stuSheng int=1
as
select * from student
go
exec proc_name--都采用默认值
exec proc_name 2--成绩为默认值，省份为指定的
exec proc_
name 80,2--都指定值
-
---创建带输出参数的存储过程
if exists(select name from sysobjects where name='proc_name')
drop procedure  proc_name
create procedure selectStuinfo
@stuName varchar(20) output
@stuMark int,
@stuSheng int
as
select * from student
go
exec proc_name output 60,1
-
--调用输出参数
declare @name varchar(20)
exec proc_name @name output,80
print @name
经典代码..
---------------计算总页数----
set @pagecount=ceiling(@rscount/cast(@pagesize as float))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。