SQL基础

数据库基础

从据库对象的目录依次深入，是入门数据库的一个不错的方法。可以全面的，系统的了解数据库。
想深入，就必须多逛逛官网啊SQL语言参考

数据库备份设备
exec sp_addumpdevice 'disk','device_name','d:\device_name.bak'
删除备份设备
exec sp_dropdevice device_name
数据库完全备份
backup database DB_Names to device_name
数据库差异备份
backup database DB_Names to device_name with differential
日志备份
backup log DB_Names to device_name
查看备份文件
RESTORE LABELONLY FROM AdvWrks2008R2Backup
数据库还原

restore database DB_Names from device_name with file=1,norecovery
新建数据库登录身份认证登录名
create login [NT SERVICE\name] from windows with default_database=[DB_Club]
新建密码登录名
create login loginname with password=N'password', default_database=[master] 
删除登录名
drop user loginUser
为登录名创建用户
create user loginUser for login [NT SERVICE\name]

Connect授权
grant connect to username
拒绝connect授权
deny connect to username
表操作权限授权
grant select,update,delete on tablename to username
拒绝表操作权限授权
deny select,update,delete on tablename to username
创建架构
create schema schemaname authorization username
撤销用户查询表授权
revoke select on tablename from username
撤销用户创建存储过程的权利
revoke create procedure from username

创建数据库角色
exec sp_addrole rolename
给予角色建表权限
grant create table to rolename
从角色中权限授予用户
exec sp_addrolemember 'rolename','username'
删除角色
exec sp_droprole 'rolename'
从角色中删除用户
exec sp_droprolemember 'rolename','username'

创建触发器
create trigger triggername on database for drop_table as print'删除表触发trigger！'
create trigger triggername on [dbo].[TB＿Tablename] instead of delete as print'删除操作被trigger取代！'
create trigger triggername on [dbo].[TB＿Tablename] after delete as print'删除操作后数据回滚！' rollback
修改触发器
alter trigger triggername on tb_tbname for insert as print'修改触发器！'
禁用触发器
disable trigger triggername on database
启用触发器
enable trigger triggername on tablename
删除触发器
drop trigger triggername

创建存储过程
create procedure sp_pronamee @argument char(3) as print'执行存储过程！'
修改存储过程
alter procedure sp_proname as print'修改存储过程！'
删除存储过程
drop procedure procname

创建函数
create function function_name(@params nvarchar[,@params nvarchar])
returns [nvarchar|@table_name table(id int,column_name nvarchar(20))]
as
begin
insert into @table_name values(1,'值')--如果返回表变量则需要修改表，并且只能空return
reutrn @params
end

创建类型（表）
crate type type_name as table(columnName nvarchar(20))

发出警报
if object_id('tr_triggername','tr') is not null raiserror 50005 N'发出警报'

创建视图
create view viewname as select * from Tb_Book
删除视图
drop view [viewname] as ....
修改视图
alter view as

新建索引
create [unique]|[clustered| noclustered ] index index_name on { table | view }(column [asc|desc])
删除索引
drop index index_name on tableName

游标
declare cursorName cursor [global|Local]{forward_only|scroll} for (select * form tableName)
open cursorName
fetch next cursorName into @ccc
print @ccc---处理语句
close cursorName
deallocate cursorName

--查询语法
Select [字段名[,字段名1]|*]
from [TableName]
[inner|left|rigth|full|Crss] join [TableName]
where 判断条件
分组
group by
--统计
with cube|rollup
--分组条件
having
--联合|排除
Union [All]|Except
--排序
order by

--插入记录
insert into values
--新建表并录入查询数据
select into from where
--像结构一致的表里添加数据
insert into select from where
--更新数据
update set where

--新建表
creae table [tableName](
columnName [int|navarchar|类型] primary key not null//创建主键,且不为空
columnName 数据类型 not null foreign key references TableName(ColumnName)--创建外键
columnName [int|bigint] identity(1,1)--自增字段
columnName 数据类型 check (columnName like '')--like约束，*%代表任意多的字符，[0-9]代表0-9的数字.代表一个字符
constraint 约束名称 Foreign key(columnName) references TableName(ColumnName)--另一种添加约束的途径
)
新建字段
alter tableName add columnName ......
新建约束
alter tableName add constraint constraintName 约束类型 ...............
修改字段
alter table alter column columnName .......
修改表|字段|触发器|存储过程等数据库对象名称
sp_rename 'oldName','newName'
删除表
drop table [tableName]
清空表
truncate table tableName
删除字段
alter table tableName drop column columnName
删除约束
alter table tableName drop constraint constraintName
禁用约束
alter table tableName uncheck constraint constraintName
启用约束
alter table tableName check constraint constraintName

语句块
begin [语句] end
判断语句
if [判断语句]
循环语句
while [判断条件]
递归循环(1到20循环)
with tt as (select 1 dg union all select dg+1 from tt where dg<20 )select * from tt
分支语句
case [字段,值，或者为空] when [值|判断条件] then [值|操纵语句] else [值|操纵语句] end
异常抓取
try [可能存在异常的语句] end try
catch [异常处理语句] end catch

字符串截取
substring('要截断的字符',[开始位置],[结束位置])
从左边开始截取
left('要截取的字符',[长度])
从右边开始截取
right('要截取的字符',[长度])
得到字符串长度
len('字符串')
charindex('匹配字符或正则','要搜索的字符串',[开始的位置（可以为空）])
字符串替换
replace('要搜索的字符串','匹配字符或正则','替换的字符')

获取当前系统时间
Date()
获取时间年份
yeaar(Date)
获取月份
month()
时间比较
datediff([year|month|day|Week|Hour],Date1，Date2)
时间修改
dateadd([year|month|day|Week|Hour],调整值,Date)

类型转化
cast('要转化的字符' as 要转化的类型) 
convert(类型,'转化的字符'[,长度])

数据提供服务，访问不经常访问的资源（Excel）
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;
数据提供服务，其他资源提供CRUD（Excel）
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Trigger_v2.xlsx;','SELECT * FROM [sheetName$A1:E12]')
打开XML文件(文件需要载入内存并分析见sp_xml_preparedocument 函数)
OPENXML(@xml,'节点XQuery',[0,1,2,8]) with (XML[节点|属性名] 参数类型[,XML[节点|属性名] 参数类型])--第三个参数为读取方式,with可以用XQuery

链接服务器，提供频繁的数据访问与修改（打开Excel）
EXEC sp_addlinkedserver 'ExcelSource',
   'ACE 12.0',
   'Microsoft.ACE.OLEDB.12.0',
   'F:\myTestOledbQueryExcel.xlsx',
   NULL,
   'Excel 12.0';
SELECT * FROM ExcelSource...sheetName
删除链接服务器
EXEC sp_droplinkedsrvlogin @rmtsrvname='ExcelSource'[, @locallogin='Excel 12.0']
查询服务器，提供像表一样的数据源(可以CRUD)
select * from OPENQUERY ( linked_server ,'查询语句')