SQL学习总结Word格式.docx
《SQL学习总结Word格式.docx》由会员分享，可在线阅读，更多相关《SQL学习总结Word格式.docx（19页珍藏版）》请在冰豆网上搜索。
Charindex
用来寻找一个指定的字符在另一个字符串中的起始位置
SELECTCHARINDEX（‘lucia'
’Iamlucia'
）返回：
6
Len
返回传递给它的字符串长度
Selectlen（‘sql’）
Lower
将传递给他的字符串变成小写
Selectlower（‘SQL’）返回sql
Upper
将传递给他的字符串变成大写
Ltrim
清空字符左边的空格
Selectltrim（‘你好’）返回你好
Rtrim
清空字符右边的空格
Right
从字符串右边返回指定数目字符
Selectright（‘玛丽莲.梦露，2’）梦露
Replace
替换一个字符串中的字符
Selectreplace（‘连玉龙’,‘龙’,‘凤'
）
返回：
连玉凤
Studiff
在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串
Selectstudiff（'
ABCDEFG'
2,3,'
我的音乐我的世界'
A我的音乐我的世界EFG
日期函数
Getdate
取得当前的系统日期
Selectgetdate（）
Dateadd
将指定的数值添加到指定的日期后面的日期
Selectdateadd（mm,4,’01/01/1999’）
返回05/01/19999
Datediff
两个日期之间的指定日期
Selectdatediff（mm,’01/01/1999’,’05/01/1999’）
Datename
日期中指定日期部分的字符串
Selectdatename（dw，‘01/01/2000’）
Datepart
日期中指定日期部分的整数形式
Selectdatepart（day，‘01/15/2000’）
数学函数
Convert
用来转变数据类型
Selectconvert（varchar（5），1234）
Current_user
返回当前用户的名字
Selectcurrent_user，返回你登录的用户名
Datalength
给指定表达式的字节数
Selectdatalegth（‘中国’）
Host_name
返回当前用户所登录的计算机名字
Selectuser_name
（1）
从任何数据库中返回‘abo’
备注：
日期函数中datediff可以用来计算时间差（例如，年龄）getdate（）用来设置默认
这些函数容易记也容易忘，尤其是书写的格式规范。
5like用法
*通配符
*%包含零个或更多字符串
*_（下划线）任何单个字符
*[]指定范围（[a-f]）或集合[abcdf]中任何的一个单个字符
*[^]不属于指定范围[a-f]或集合[abcdef]的任何单个字符
例
SELECT*FROM数据表
WHERE编号LIKE‘00[^8]%[AC]%’
可能会查询出的编号值为（a）。
A、0090ACD
B、007_AFF
C、008&
DCG
D、008C
6.聚合函数
*Count（）,*Max（）,*Min（）,*Avg（），sum（）
6.1分组查询—groupby—having
Select任职部门，count（*）
Fromlucia工作室
Where目前的薪资>
=2000
Groupby任职部门
Havingcount（*）>
4
7.多表查询
*innerjoin内连接:
两张表的顺序颠倒对结果没有影响
*leftjoin左连接：
左边的表是主表，表的顺序不能颠倒
*rightjoin右连接：
右边的表是主表，表的顺序不能颠倒
SELECTS.姓名,C.课程编号,C.笔试成绩
FROMStudentInfoASS
INNERJOINScoreInfoASC
ONC.学号=S.学号
8.建表的三大范式
v第一范式的目标是确保每列的原子性
v如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）
v
v如果一个表中个字段关系满足1NF，并且除了主键以外的其它列，都依赖于该主键，则满足第二范式（2NF）
v第二范式要求每个表只描述一件事情（去除不依赖主键或部分依赖主键的列）
由第二范式可知：
产品价格不是依赖订单的编号
v如果一个关系满足2NF，并且除了主键以外的其它列都不传递依赖于主键列，则满足第三范式（3NF）
v第三范式的目标是确保每列都直接依赖主键
由第三范式可知：
产品的结果是依赖产品编号而不是直接依赖订单编号的
9.主键（primarykey），外键（foreignkey）创建/添加语法
ConstraintPK_主键名称primarykey（主键名称）
ConstraintFK_外键的名称foreignkey（外键名称）
References父表（父表字段）
修改
Altertable子表
AddConstraintFK_外键的名称foreignkey（外键名称）
10变量（局部变量，全局变量）
局部变量
*声明局部变量
DECLARE@变量名数据类型
*赋值
*SET@变量名=值
*SELECT@变量名=值
全局变量
是系统的，是不能改变的
较常用的是：
变量
变量含义
@@ERROR
最后一个T-SQL错误的错误号
@@IDENTITY
最后一次插入的标识值
@@LANGUAGE
当前使用的语言的名称
@@MAX_CONNECTIONS
可以创建的同时连接的最大数目
@@ROWCOUNT
受上一个SQL语句影响的行数
@@SERVERNAME
本地服务器的名称
@@TRANCOUNT
当前连接打开的事务数
@@VERSION
SQLServer的版本信息
11.if……else,while,caseend,whenthen用法例题
=============ifelse===================
ifexists（select*fromsysobjectswherename='
NewTable'
droptableNewTable
select姓名,S.学号,笔试成绩,机试成绩,是否通过=case
when笔试成绩>
60and机试成绩>
60then1
else0
end
intoNewTablefromstudentinfoass
leftjoinscoreinfoasc
ons.学号=c.学号
go
==============while===================
declare@writtenAvgdecimal
declare@labavgdecimal
select@writtenAvg=avg（笔试成绩）fromstudentscore
select@labavg=avg（机试成绩）fromstudentscore
if（@writtenAvg>
@labavg）
begin
print（'
笔试成绩大于机试成绩'
while（1=1）
updatestudentscoreset机试成绩=机试成绩+1
if（selectmax（机试成绩）fromstudentscore）>
=97
break
end
else
机试成绩大于笔试成绩'
updatestudentscoreset笔试成绩=笔试成绩+1
if（selectmax（笔试成绩）fromstudentscore）=97
select*fromstudentscore
============caseend==============
select姓名,学号
笔试成绩=
case
when笔试成绩isnullthen'
缺考'
elseconvert（varchar（5）,笔试成绩）
end
机试成绩=
case
when机试成绩isnullthen'
elseconvert（varchar（5）,机试成绩）
是否通过=
case
when是否通过=1then'
是'
when是否通过=0then'
否'
fromNewTable
===========whenthen=====================
select*fromnewTable
12子查询（in，exists）
SELECT姓名FROMStudentInfo
WHERE学号
IN
（SELECT学号FROMScoreInfo）
GO
基本上in可以等价于“=”可是“=”只能是子查询返回的是单个结果，如果是多个查询结果只能用in
Exists用法用例：
IFEXISTS（SELECT*FROMsysdatabasesWHEREname=‘LuciaBank'
DROPDATABASELuciaBank
14存储过程
优点：
<
1>
执行的速度更快
2>
允许模块化程序设计
3>
提高系统安全性
4>
减少网络流通量
14.1分类
系统存储过程
“sp_”或者是“XP_”
常用系统存储过程
Sp_database：
列出服务器上的所有数据库
Sp_helpdb：
报告有关指定数据库或所有数据库的信息
Sp_rename：
更改数据库的名称
Sp_tables：
返回当前环境下可查询的对象的列表
sp_columns：
返回某个表列的信息
sp_help：
查看某个表的所有信息
sp_helpconstraint：
查看某个表的约束
sp_helpindex:
查看某个表的索引
sp_password:
添加或修改登录帐户的密码
sp_helptext:
显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本
14.2自定义的存储过程
14.2.1定义存储过程的语法和调用的语法
CREATEPROC[EDURE]存储过程名
@参数1数据类型=默认值OUTPUT,
……,
@参数n数据类型=默认值OUTPUT
AS
SQL语句
调用的语法
EXEC过程名[参数]
14.2.2不带参数的存储过程
/*------------------------------------------------------
存储过程（不带参数）
题目：
查看本次考试的平均分数，并查看没有通过的学生的名单
----------------------------------------------------------*/
createprocedurep_scoreInfo
as
declare@writtenAvgdecimal
declare@labAvgdecimal
select@writtenAvg=avg（笔试成绩）,@labAvg=avg（机试成绩）fromstudentscore
print'
笔试成绩的平均分'
+convert（nvarchar（5）,@writtenAvg）
机试成绩的平均分'
+convert（nvarchar（5）,@labAvg）
60and@labAvg>
60）
本班的成绩优秀'
本班的成绩很差'
-------------------------------------------------------'
print'
本班成绩不及格的人名单'
selects.学号,姓名,笔试成绩,机试成绩
fromstudentinfos,studentscorec
wheres.学号=c.学号
and笔试成绩<
60or机试成绩<
60
go
execp_scoreInfo
14.2.3带参数的存储过程
存储过程的参数分两种：
输入参数,输出参数
例题
/*--------------------------------------------------------------
带参数的存储过程
题目：
可以规定及格的分数
-----------------------------------------------------------------*/
createprocedureproc_scoreinfoHaveParams
（
@writePassdecimal,--默认值是60
@labPassdecimal
declare@writtenAvgdecimal
print'
@writePassand@labAvg>
@labPass）
@writePassor机试成绩<
@labPass
execproc_scoreinfoHaveParams45,35
*--------------------------------------------------------------
带参数（输出）的存储过程
查看本次考试的平均分数，并查看没有通过的学生的人数
createprocedureproc_outputscoreInfo
@sumNotPassintoutput,
@writePassdecimal=60,--默认值是60
@labPassdecimal=60
declare@writtenAvgdecimal
select@sumNotPass=count（*）fromstudentscorewhere笔试成绩<
@writtenAvgor机试成绩=@labAvg
declare@sumint
execproc_outputscoreInfo@sumoutput,35,35
***************************************'
if@sum>
=3
本班成绩不及格的人数是'
+convert（varchar（5）,@sum）+'
及格人太少，及格分数要在调低'
及格人数适中，及格分数线可以'
dropprocproc_outputscoreInfo
14.2.4RAISERROR用法
语法：
RAISERROR（msg_id|msg_str,severity,stateWITHoption[,...n]]）
msg_id：
在sysmessages系统表中指定用户定义错误信息
msg_str：
用户定义的特定信息，最长255个字符
severity：
定义严重性级别。
用户可使用的级别为0–18级
state：
表示错误的状态，1至127之间的值
option：
指示是否将错误记录到服务器错误日志中
当输入的及格分数不再1到100之间则报错
declare@sumint,@tint
execproc_outputscoreInfo@sumoutput,80
set@t=@@error
错误号：
'
+convert（nvarchar（10）,@t）
if@t<
raiserror（'
及格线错误，请重新输入（1——100之间）'
16,1）
return
15事物
15.1事物概念
v事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作
v这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行
v事务是一个不可分割的工作逻辑单元
15.2事物四个属性
◆原子性（Atomicity）：
事务是一个完整的操作。
事务的各步操作是不可分的（原子的）；
要么都执行，要么都不执行
◆一致性（Consistency）：
当事务完成时，数据必须处于一致状态
◆隔离性（Isolation）：
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
◆永久性（Durability）：
事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性
15.3管理事务T_SQL语句
开始事务：
beigintransaction
提交事务：
committransaction
回滚事物：
rollbacktransaction
15.4判断出错的语句@@ERROR
v15.5事务的分类：
◆显性事务：
用BEGINTRANSACTION明确指定事务的开始，这是最常用的事务类型
◆隐性事务：
通过设置SETIMPLICIT_TRANSACTIONSON语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。
当该事务完成时，再下一个T-SQL语句又将启动一个新事务
◆自动提交事务：
这是SQLServer的默认模式，它将每条单独的T-SQL语句视为一个事务，如果成功执行，则自动提交；
如果错误，则自动回滚
15.6例题
---------------------------------
--转账事物处理
--------------------------------
begintransaction
declare@errorcountint
set@errorcount=0
updateclientAccountset余额=余额-1000
where账号='
6226900707220987'
set@errorcount=@errorcount+@@error
updateclientAccountset余额=余额+1000
WHERE账号='
6226900707220654'
set@errorcount=@e
升级会员 