索引：牺牲硬盘空间换取效率
视图的作用:1.安全;2.达到快捷在作用
函数：一系列sql在集合
存储过程:
函数和存储过程在最大区别是：函数有返回值，而存储过程没有
Jobs：调度
一.数据库函数：函数套函数原理：把内函数的结果作为外函数的参数
1.系统函数:
(1).聚合函数：查询时使用聚合函数对查询的结果集汇总(使用它时不能单行多列):select sum/avg/max/min/count(*/1)(columnName) from tableName where columnName 条件
(2).数学函数
abs()：求指定数字的绝对值  --select abs(-999.987)
ceiling()：返回不小于指定数字的最小整数，只进函数  --ceiling(43.5)=44
floor()：返回不大于指定数字的最大整数，舍于取整函数
power()：返回数值表达式的指定幂  --power(5,2)=25
sqrt()：返回数值的平方根  --sqrt(9)=3
round()：返回数值指定精度的四舍五入的值,  --round(43.543,2)=43.54
sign()：判断是正/负数  --sign(-43)=-1,sign(43)=1 sign(0)=0
(3).字符串函数
substring()：返回源字符串中从第n个字符开始指定n个长度的字符串  --substring('abc',1,2)
charindex()：返回指定的字符串在另一个字符串中的位置  --select charindex(查的字符串，所在的字符串，要查的起始位置)如:charindex('ab','cab')=2
len()：返回字符串的长度  --select len ('')
left()：从字符串左边返回指定数目的字符  --select left('abcde',3)
right()：从字符串右边返回指定数目的字符  --select right('abcde',3)
ltrim()：清除字符串最左边的空格  --select ltrim('__abc')
rtrim()：清除字符串最右边的空格
lower()：把字符串转为小写  --select lower('NIdeAIa')，可嵌套select lower(upper('a'))
upper()：把字符串转为大写  --select upper('niSHIwo')，可嵌套select upper(lower('A'))
replace()：替换字符串中的指定的字符  --select replace('所在字符串','要的字符串','改后的字符串')
stuff：删除字符串中指定长度的字符串，并在该位置插入新的字符串  --select stufff('所在的字符串','要删的位置','要删的个数','要插入的字符串')
(4).日期函数
year(date)：获取指定日期中的年份
month(date)：获取指定日期中的月份
day(date)：获取指定日期中的日份
getdate()：返回当前系统日期  --select getdate()
dateadd()：将指定数值添加到指定日期中  --dateadd(指定日期(yy/year、mm/mouth、dd/day、dw/week、dy、hh、mi、ss/second),num，日期),select dateadd(day,10,getdate())
datename()：返回日期中指定日期形式的数字  --select datename(wk,'02/22/2009')
datepark()：返回日期中指定日期形式部分的整数形式  --datepark(dd,'09/12/09')=9
datediff()：返回两日期之间指定日期形式的日期
部分的差距  --datediff(mm,'01/01/01','01/05/05')=5
(5).系统函数
convert()：转换数据类型  --select convert(newDataType,oldDataType)  --select convert(varchar(5),12345)='12345'
datalength()：返回指定表达式的字节数 --datalength('中国A盟')=7
current_user()：返回当前登录的用户名  --select current_user=dbo
system_user()：返回登录的用户名  --select system_user
user_name()：根据ID返回用户名  --user_name(ID)
host_name()：返回当前计算机名
例:updata cards set password=replace(变量(password,'old','new'))
2.自定义函数
(1).标量函数：只能返回一个值
create function functionName(parameter)
returns dataType
as
begin
内容...
return ...,
end
如:select dbo.functionName(parameter) --调用自定义函数时函数前加dbo.
(2).表值函数：返回表
create function functionName(parameter)
returns table
as
begin
return (select * from tableName)
end
如:select * from dbo.functionName(parameter)
二.存储过程：类似于编程语言中的函数(可带参，可返回参/一张或多张表)功能一样
1.存储过程的作用/优点
(1).性能：直接在数据库执行SQL语句提高执行效率，封装SQL代码，减少网络中数据的传输量
(2).安全：阻止SQL注入
(3).模块化设计提高可重用性(便于数据库管理、维护、开发)
2.存储过程的分类
(1).自定义存储过程(T-SQL存储过程(包含SQL语句),CLR存储过程(通过.NET创建))
(2).系统存储过程(以xp_(系统扩展)、sp_(系统维护)为前缀)
xp_cmdshell：执行系统命令
xp_subdirs：查看指定目录下的子目录(如:execute xp_subdirs 'e:\Wen''s Files\'，其中支持转义序列)
sp_help objectName：查看表/存储过程/函数/视图属性信息
sp_helpdb databaseName：查看数据库详细信息
sp_helpindex tableName：查看表索引
sp_helptext objectName：查看存储过程/函数/视图详细内容
sp_dboption 选项：显示/设置数据库选项
3.存储过程的使用
(1).创建存储过程
create procedure procedureName with encryption  --with encryption表示对创建的存储过程加密，是可选项(加密后任何角使用任何系统存储过程将不能查看和解密
该存储过程的任何详细内容)
parameter(@name dataType) output --带输入参数(默认情况)或带输出参数(参数的数据类型后加output的参数)的存储过程才有此步，需要返回表的结果集时不用声明输
出参数，也就不用delare一个变量变量来接收输出参数(因为已经没有输出参数了)，执行存储过程时直
接execute 存储过程名 输入参数的实参列表即可
as --存储过程的内容写在as下
begin
除create aggregate、rule、default、schema和create/alter function、trigger、procedure、view和set parseonly、showplan_all、showplan_text、showp
lan_xmluse databaseName的任意类型和数量的SQL语句
或set/select expression(输出参数=?) --用于给参数赋值,select后只跟输出参数时表示只查询输出参数的结果
end
(2).调用存储过程
declare variable(@name dataType)  --需要将输出参数返回时才有必要声明该变量来接收输出参数
execute procedureName 输入参数的实参列表(其间用，分开)，已声明的变量 output  --当把在declare
处声明的变量设置成output后，就把as之前的output型的参数赋给了
在declare处声明的变量
(3).修改存储过程:alter procedure procedureName 以下就重新定义要修改的存储过程的内容即可(修改后的存储过程执行方法必须满足修改后的存储过程的要求)
如:create procedure pn with encryption
as
begin
select * from tn
end
alter procedure pn @a int,@b int,@sum output
as
begin
select * from tt
end
declare @c int
execute pn 1,2,@c output
(4).存储过程示例:
①.带输入参数并返回int型输出参数的存储过程
create procedure Demo
@a int,@b int,@sum int output
as
begin
set @sum=@a+@b   
select @sum
end
declare @bian int
execute Demo 2,3,@bian output
②.不带参返回表结果集的存储过程
create procedure Demo1
as
begin
select * from dingwen
end
execute Demo1
4.C#应用存储过程:comdmandText="存储过程名"; comdmandType=commandType.storedprocedure
注：存储过程内的SQL语句的权限最大为该用户使用该存储过程的权限(用户使用的存储过程内的sql语句的权限受到当前使用的存储过程的权限的限制)
三.事务和触发器
1.事务：一个过程做一系列的操作
(1).必须具有的特性：原子性(事务是一个完整的操作，其各步操作不可分开执行)要么都执行，要么都不执行
一致性:
隔离性:
永久性:
(2).事务的使用
①.开始事务:begin transaction>要执行的SQL过程>判断是否有错(@@error 有错返回非0数，无错返回0)>
②.提交事务:commit transaction
③.有错则回滚事务:rollback transaction
2.触发器：对insert、delete、update语句执行时进行相关约束的机制，本质是自动运行的存储过程，不能直接调用，只能有系统调用
(1).分类:insert触发器、delete触发器、update触发器
(2).触发器的使用:
①.创建触发器
create trigger triggerName
on tableName
for trigg
erType
as
begin
内容
end
②.删除触发器:drop trigger triggerName
3.C#应用事务：在con.open()下
sqltranSaction tran=con.BeginTranSaction();
在正常情况的语句下:tranmit();
在有可能出错的语句下:llback();
datediff是字符型函数用事务同时对多张表插入数据，但是在C#只能用存储过程，故再用存储过程套用事务
create procedure  procedure_name
as
begin transaction
declare @a int
set @a=0
insert into d values('dd')
set @a=@a+@@error
insert into i values('dd')
set @a=@a+@@error
exec procedure_name
触发器实例
create trigger trg_bank
on  bank1
for update
as
declare @beforemoney money,@aftermoney money,@cn varchar(50)--声明变量
将更新的数据插入表中
select @beforemoney=currntmoney from deleted
select @cn=currntname, @aftermoney=currntmoney from  inserted
if abs(@aftermoney- @beforemoney)>2000
begin 
print '交易金额：'+convert(varchar(16)
,abs(@aftermoney-@beforemoney))
end 
else begin      ('交易金额不能小于2000，交易出错')
rollback  transaction  --回滚事务
end
update  bank1
set currntmoney=300001
where currntname='sa1' --修改数据
begin transaction--开始事务
declare @errorsum int
set @errorsum=0
set @errorsum=@errorsum+@@error
4.update的后台执行过程
(1).备份原数据，将这个数据存入deleted临时表
(2).将新数据插入inserted临时表
(3).删除原数据表中的数据
(4).插入新数据
四.索引和游标
1.索引：按页存储 什么时候该用索引：数据量大，数据相似度低,(数据量大的)多表查询时
(1).索引类型：同一张表的同一列不能同时有同一类型的索引，但可建不同类型的多个索引
①.唯一索引：记录不同数据
②.主键索引：记录不同且非空的数据
③.聚集索引：物理顺序和逻辑顺序相同
④.非聚集索引：单指逻辑顺序
(2).索引的缺点：多占存储空间，读取索引表占时间
(3).索引的创建:create type index indexName on tableName(columnName)
(4).删除索引:drop index tableName.indexName
2.游标：提供对每次处理一行/一部分行的一种结果处理机制(关系型数据库的操作对整个select中满足where子句的结果集处理)
(1).声明游标:declare cursorName cursor type for select * from tableName
(2).打开游标:open cursorName
(3).使用游标:fetchnext from cursorName
(4).关闭游标:close cursorName
五.视图:
1.创建视图:create view viewName as 查询语句
2.修改视图:alter view viewName as 查询语句
3.删除视图:drop view viewName
case多重选择：用来做等值判断，用来替换列中的显示值，功能类似于swith:sel
ect case id when 1 then '星期一' when 2 then '星期二'... else '出错了' end as '列名称' form class
select aa,bb,stuname=case when columnName 判断表达式 then 值/判断表达式 end from tableName
if(@ck_name=1 and len(@au_lname)>0 )
begin
set @sql = @sql + ' (au_lname LIKE ''%' + @au_lname + '%'') AND'
end
循环
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end

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