SQL_Note
/**********存储过程*********/
sp_系统存储过程
xp_系统扩展存储过程
存储过程是序编译SQL语句的集合，要比函数的执行速度要快，分为系统存储过程和用户自定义存储过程
exec 执行字符串，也可执行存储过程http://www.njxzc.com
create proc创建存储过程
OBJECT_ID()用来检测对象是否存在，包括存储过程或者数据表等等。参数'p'表示存储过程。
如:
/**********执行存储过程'test'例一*********/
IF OBJECT_ID('test','P') IS NOT NULL
create proc test as select * from S_INFO
go
exec test
/**********执行存储过程'test'例二*********/
IF OBJECT_ID('test','P') IS NOT NULL
create proc test
@a INT,@b INT //此存储过程需要传入两个整型参数
as select @a+@b
go
exec test 12+25//传入两个参数
/**********执行存储过程分页*********/
create proc page
@currentpage int=1,//当前第几页
@count int=10//每页有几条
as
declare @sql nvarcher(1000)
set @sql='select top'+cast(@count as nvarchar(3))
set @sql=@sql+'* from s_info where s_id not in('
set @sql=@sql+n'select top '+cast(((@currentpage-1)*@count) as nvarchar(3))
set @sql=@sql+n's_id from s_info'
exec(@sql)
go
exec page 1,3
/**********触发器*********/
自动机制用触发器实现，即触发器是一种自动机制，实现表与表之间的约束。
事务把几个操作当作一个整体，要么一起执行，要么一个也不执行
SET XACT_ABORT(ON/OFF)//当事务出现错误时，是否自动回滚
BEGIN TRANSACTION //开始一个事务
...
...
COMMIT TRAN
事务隔离级别：提交读，可重复读，可串行读。
提交读可避免脏读。
设置事务隔离级别:SET TRANSACTION ISOLATION LEVEL
read uncommitted:允许脏读，即允许读取别人修改但未提交的行；
read committed:待别人提交后再读，可以避免脏读；
repeatable read:其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据；
snapshot:
serialzable:
//以下为创建一个触发器：在更新CARD表的同时把结果查出来
CREATE TRIGGER TEST
ON CARD FOR UPDATE AS
SELECT * FROM CARD
/**********视图*********/
创建视图：(注：视图是个虚拟的表，并没有实际存储内容)
CREATE VIEW VW_TEST
AS
SELECT S_NAME,SCORE
FROM COURSE C INNER JOIN S_INFO S
ON C.S_ID=S.S_ID
GO
SELECT * FROM VW_TEST
/**********索引*********/
索引类型：聚集索引；非聚集索引；唯一索引
索引的作用是增加搜索效率，但要牺牲一定的空间，即用空间换效率。
每个表只能有一个聚集索引，可以有多个非聚集索引
聚集索引的意思是：物理存储和逻辑存储相关联，即如果聚集存储（逻辑存储）为升序，则在硬盘上的物理存储顺序也为升序，反之则为降序。
非聚集索引的意思是：物理存储和逻辑存储不关联，即假如逻辑存储为升序，则物理存储的存储方式不一定为升序。
唯一索引和主键约束的区别：唯一性索引可以为空，但只可有一个为空，因为空值也必须是唯一的
索引默认是升序、且是非聚集索引
创建索引：
CREATE INDEX IN_TEST
ON S_INFO(S_NAME)
GO
SELECT * FROM S_INFO whith(index(in_test))
/**********模糊查询*********/
select * from S_INFO WHERE S_NAME LIKE '[张周]%'//查询姓张或者姓周的人
select * from S_INFO WHERE S_ID LIKE'[1-3]'//查找编号是1-3之前的人
select * from S_INFO WHERE S_ID LIKE'[^1-3]'//查找编号不在1-3之前的人
alter table *** add constraint FK_本表名_字段名 refrences 被引用表名（字段名）//建立外键约束
sqlserver中的model(模板库)中创建一个数据库后，这个模板库中的表都会存在于任何新建的数据库中
数据库的扩展名可以随意改，即.mdf可以改为任何的扩展名：.xxxxxx
创建数据库关系图
约束：1、检查约束，如添加表达式：sex='男' or sex='女'；
2、主键约束；3、默认值约束；4、外键约束
DB_ID()返回数据库是否存在，用法如下：
if db_id(N'test') is not null
drop database test
go 注：不加N也可以执行，不加N是ascall编码格式，加N是unicode编码格式（宽字符格式）
truncate和delete的区别是前者删除表中所有的字段，且前者删除的操作没有记录日志，也就相当于没有可恢复性
declare声明变量，@为局部变量，@@为全局变量，用法如下：
declare @A int
set @A=100,
也可用如下方式给变量赋值：
select @A=AVG(num) from Table
替换值的两种方式的SQL例子如下：1、select s.Intid as 编号,s.Strname as 姓名,ISNULL(b.Strcaption,'未知') as 学历 from person as s left join BaseCode as b on b.Intid=s.Inteducationid left join BaseCode as d on d.Intid=s.Intnationid
解释：ISNULL(b.Strcaption,'未知')的意思是，如果b.Strcaption的值为null,则在执行查询后的结果集中学历项中所有的NULL值全部显示“未知”，而不是NULL。
2、select s.Intid as 编号,s.Strname as 姓名,'学历'=CASE when b.Strcaption is null then '未知' else b.Strcaption end from person as s left join BaseCode as b on b.Intid=s.Inteducationid left join BaseCode as d on d.Intid=s.Intnationid
解释：此处用case...when...then...end去替换ISNULL，再举例如下：
case table.colum1
when 'value1' then 'value2'
when 'value3' then 'value4'
end此例的意思是如果table表中的colum1的值是value1，则在结果集中替换为value2来显示，value3同理
数据库有了登录名还不能进行操作，必须把用户名映射成一个用户名（即每个数据库下都有一个安全性下，安全性下有一个用户，右击某个用户名某看其属性，就可以看到此用户是由哪个登录名映射出来的）
创建登录名需要系统存储过程：sp_addlogin '登录名','密码'
把登录名映射成用户：sp_grantdbaccess '用户名','密码'
给某个用户分配某些权限：GRANT 操作权限（如：select或update等等） ON '表名' TO '用户'
创建角色：SP_ADDROLE '角色名'
给角色添加用户：SP_ADDROLEMEMBER
给角色分配权限：同给用户分配权限类似
sp_系统存储过程
xp_系统扩展存储过程
存储过程是序编译SQL语句的集合，要比函数的执行速度要快，分为系统存储过程和用户自定义存储过程
exec 执行字符串，也可执行存储过程http://www.njxzc.com
create proc创建存储过程
OBJECT_ID()用来检测对象是否存在，包括存储过程或者数据表等等。参数'p'表示存储过程。
如:
/**********执行存储过程'test'例一*********/
IF OBJECT_ID('test','P') IS NOT NULL
create proc test as select * from S_INFO
go
exec test
/**********执行存储过程'test'例二*********/
IF OBJECT_ID('test','P') IS NOT NULL
create proc test
@a INT,@b INT //此存储过程需要传入两个整型参数
as select @a+@b
go
exec test 12+25//传入两个参数
/**********执行存储过程分页*********/
create proc page
@currentpage int=1,//当前第几页
@count int=10//每页有几条
as
declare @sql nvarcher(1000)
set @sql='select top'+cast(@count as nvarchar(3))
set @sql=@sql+'* from s_info where s_id not in('
set @sql=@sql+n'select top '+cast(((@currentpage-1)*@count) as nvarchar(3))
set @sql=@sql+n's_id from s_info'
exec(@sql)
go
exec page 1,3
/**********触发器*********/
自动机制用触发器实现，即触发器是一种自动机制，实现表与表之间的约束。
事务把几个操作当作一个整体，要么一起执行，要么一个也不执行
SET XACT_ABORT(ON/OFF)//当事务出现错误时，是否自动回滚
BEGIN TRANSACTION //开始一个事务
...
...
COMMIT TRAN
事务隔离级别：提交读，可重复读，可串行读。
提交读可避免脏读。
设置事务隔离级别:SET TRANSACTION ISOLATION LEVEL
read uncommitted:允许脏读，即允许读取别人修改但未提交的行；
read committed:待别人提交后再读，可以避免脏读；
repeatable read:其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据；
snapshot:
serialzable:
//以下为创建一个触发器：在更新CARD表的同时把结果查出来
CREATE TRIGGER TEST
ON CARD FOR UPDATE AS
SELECT * FROM CARD
/**********视图*********/
创建视图：(注：视图是个虚拟的表，并没有实际存储内容)
CREATE VIEW VW_TEST
AS
SELECT S_NAME,SCORE
FROM COURSE C INNER JOIN S_INFO S
ON C.S_ID=S.S_ID
GO
SELECT * FROM VW_TEST
/**********索引*********/
索引类型：聚集索引；非聚集索引；唯一索引
索引的作用是增加搜索效率，但要牺牲一定的空间，即用空间换效率。
每个表只能有一个聚集索引，可以有多个非聚集索引
聚集索引的意思是：物理存储和逻辑存储相关联，即如果聚集存储（逻辑存储）为升序，则在硬盘上的物理存储顺序也为升序，反之则为降序。
非聚集索引的意思是：物理存储和逻辑存储不关联，即假如逻辑存储为升序，则物理存储的存储方式不一定为升序。
唯一索引和主键约束的区别：唯一性索引可以为空，但只可有一个为空，因为空值也必须是唯一的
索引默认是升序、且是非聚集索引
创建索引：
CREATE INDEX IN_TEST
ON S_INFO(S_NAME)
GO
SELECT * FROM S_INFO whith(index(in_test))
/**********模糊查询*********/
select * from S_INFO WHERE S_NAME LIKE '[张周]%'//查询姓张或者姓周的人
select * from S_INFO WHERE S_ID LIKE'[1-3]'//查找编号是1-3之前的人
select * from S_INFO WHERE S_ID LIKE'[^1-3]'//查找编号不在1-3之前的人
alter table *** add constraint FK_本表名_字段名 refrences 被引用表名（字段名）//建立外键约束
sqlserver中的model(模板库)中创建一个数据库后，这个模板库中的表都会存在于任何新建的数据库中
数据库的扩展名可以随意改，即.mdf可以改为任何的扩展名：.xxxxxx
创建数据库关系图
约束：1、检查约束，如添加表达式：sex='男' or sex='女'；
2、主键约束；3、默认值约束；4、外键约束
DB_ID()返回数据库是否存在，用法如下：
if db_id(N'test') is not null
drop database test
go 注：不加N也可以执行，不加N是ascall编码格式，加N是unicode编码格式（宽字符格式）
truncate和delete的区别是前者删除表中所有的字段，且前者删除的操作没有记录日志，也就相当于没有可恢复性
declare声明变量，@为局部变量，@@为全局变量，用法如下：
declare @A int
set @A=100,
也可用如下方式给变量赋值：
select @A=AVG(num) from Table
替换值的两种方式的SQL例子如下：1、select s.Intid as 编号,s.Strname as 姓名,ISNULL(b.Strcaption,'未知') as 学历 from person as s left join BaseCode as b on b.Intid=s.Inteducationid left join BaseCode as d on d.Intid=s.Intnationid
解释：ISNULL(b.Strcaption,'未知')的意思是，如果b.Strcaption的值为null,则在执行查询后的结果集中学历项中所有的NULL值全部显示“未知”，而不是NULL。
2、select s.Intid as 编号,s.Strname as 姓名,'学历'=CASE when b.Strcaption is null then '未知' else b.Strcaption end from person as s left join BaseCode as b on b.Intid=s.Inteducationid left join BaseCode as d on d.Intid=s.Intnationid
解释：此处用case...when...then...end去替换ISNULL，再举例如下：
case table.colum1
when 'value1' then 'value2'
when 'value3' then 'value4'
end此例的意思是如果table表中的colum1的值是value1，则在结果集中替换为value2来显示，value3同理
数据库有了登录名还不能进行操作，必须把用户名映射成一个用户名（即每个数据库下都有一个安全性下，安全性下有一个用户，右击某个用户名某看其属性，就可以看到此用户是由哪个登录名映射出来的）
创建登录名需要系统存储过程：sp_addlogin '登录名','密码'
把登录名映射成用户：sp_grantdbaccess '用户名','密码'
给某个用户分配某些权限：GRANT 操作权限（如：select或update等等） ON '表名' TO '用户'
创建角色：SP_ADDROLE '角色名'
给角色添加用户：SP_ADDROLEMEMBER
给角色分配权限：同给用户分配权限类似
