sqlserver中常见的操作

create database Personnel_training_db
go
use Personnel_training_db
go
create table dept(
deptid int primary key identity(1,1),
deptname varchar(20) not null,
remark varchar(20) not null
)
go
select * from dept
insert into dept values('教职部','')
--1.创建存储过程 查询
create proc proc_dept
@deptname varchar(20)
as
select * from dept where deptname=@deptname
go
--执行存储过程
exec proc_dept '教职部'
--2.存储过程 查询
create proc proc_dept2
@deptname varchar(20)
as
exec('select * from dept where deptname='''+@deptname+'''')
go
proc_dept2 '教职部'
--存储过程 添加
create proc proc_add_dept
@deptname varchar(20),
@remark varchar(20)
as
insert into dept values(@deptname,@remark)
go
exec proc_add_dept '教职部','你懂得'
--存储过程删除
create proc proc_del_deptid
@deptid varchar(20)
as
exec('delete from dept where deptid in('+@deptid+')')
go
proc_del_deptid '1,2'
--查询
create proc proc_like_query_dept
@deptname varchar(20),
@remark varchar(20)
as
if @deptname <>'' and @remark <>''
begin
select * from dept where deptname=@deptname and remark=@remark
end
else if @remark !='' and @deptname=''
begin
select * from dept where remark=@remark
end
else if @deptname!='' and @remark=''
begin
select * from dept where deptname=@deptname
end
else
begin
select * from dept
end
go
exec proc_like_query_dept '',''
--带输出参数
create proc proc_set
@deptid int output
as
select @deptid= deptid from dept where deptname='学术部' and remark='这个不能还有点意思'
go
--定义输出参数变量
declare @deptid int
--执行输出参数 多个后面加逗号隔开
exec proc_set @deptid output
--输出获取的deptid
print '获取的deptid是：'+ convert(varchar(10),@deptid)
--带有返回值的存储过程
create proc proc_return
as
declare @deptcount int
--查询总条数
select @deptcount=COUNT(*) from dept
return @deptcount
go
declare @deptcount int
exec @deptcount=proc_return
print '总条数是：'+convert(varchar(10),@deptcount)
--条件 查询
create proc proc_query_like
@like varchar(20)
as
--动态的构造sql语句
declare @sql varchar(1024)
set @sql='select * from dept where 1=1'
set @sql=@sql+' and deptname like ''%'+@like+'%'''
set @sql=@sql+'or remark like ''%'+@like+'%'''
print @sql
exec(@sql)
go
proc_query_like '了'
--创建视图
/*
1、视图view
(1)G
视图是由一张或多张表的列组成的数据集，是一个虚拟表，也就是说：它不是以存储数据的
方式存在的，而存储的是一个查询的SQL语言。
(2)为什么使用视图
一方面：视图可以隐藏一些数据,限制用户只能存取表内特定的列。
另一方面：将复杂的SQL语句封装起来，便于理解和查询。
(3)如何创建视图
语法：
create view 视图名称
as
SQL语句
go
(4)删除视图
if exists(select * from sysobjects where name='视图名')
drop view 视图名
go
(5)优点 P115
视点集中：用户只能看到自己感兴趣的内容，而看不到所引用表的其他数据，提高了数据的安全性。
简化操作：视图隐藏了表与表之间的关联查询，只用一条简单的查询视图语句就可以搞定。
定制数据：视图可以让不同的用户以不能的方式看到不同或相同的数据。
合并分割数据：
安全性：可以向表一样授予访问权限。是一种安全机制。
(6)注意项：
一个视图只能包含一条SQL语句。即用一个视图获得多个结果集是不行的。
视图同样可以进行增、删、改、查的操作，修改后的数据基础表同步更新。
*/
create view view_dept
as
select * from dept
go
select * from view_dept
--索引
/*
2、索引index
为了提供查询数据的效率，引人了索引机制。
生活例子：各种书的目录。
(1)G
依赖与表建立，提供了编排表中数据的方法。是一个独立的物理数据结构。
通常一个表的数据是存放在两个页面：
数据 存放在 数据页
索引 存放在 索引页
(2)类型 4
分四类：主键索引
聚集索引 clustered
非聚集索引 nonclustered
唯一索引 unique
主键索引：在一个表中定义主键的时候自动创建主键索引，是唯一索引的特殊类型。
要求每一个值都是唯一的。
聚集索引：按照数据在表中的排序和存储 建立的索引
比如：字典的正文部分。
非聚集索引：类似于根据偏旁部首查汉字的过程。
聚集索引会影响数据的实际物理排序，在一张表中只能有一个；
非聚集索引不会影响数据的实际物理排序，在一张表中可以有多个。
唯一索引：通过唯一约束可以实现，可以是聚集索引，也可以是非聚集索引。
(3)如何创建索引
语法：
create [unique|clustered|nonclustered] index 索引名称
on 表名(列名)
with fillfactor=数组[0-100]
注意：如果前面的关键字不写，默认创建的是非聚集索引。
(4)删除索引
if exists(select * from sysindexes where name='索引名')
drop index 表名.索引名
go
(5)调用索引
select * from 表名 with(index=索引名) [where...]
(6)定义索引一般是在表中的数据很多的时候
(7)优缺点 P122
优点：大大提供查询速度
缺点：索引需要占据物理空间，创建和维护索引耗费时间。
*/
create nonclustered index index_dept
on dept(deptname)
with fillfactor=50
go
select * from dept with(index=index_dept)
--删除索引
drop index dept.index_dept