SQL 笔记.docx
《SQL 笔记.docx》由会员分享，可在线阅读，更多相关《SQL 笔记.docx（31页珍藏版）》请在冰豆网上搜索。
SQL笔记
1.SQLServer的数据类型
1.varchar（n）长度为n个字节的可变长度且非unicode的字符数据。
n必须是一个介于1到8000之间的数值。
存储大小为输入字节的实际长度，而不是n个字节。
2.nvarchar（n）包含n个字节的可变长度的unicode字符数据。
n必须是一个介于1到4000之间的数值。
字节存储大小是所输入字符个数的两倍。
3.什么是Unicode编码：
Unicode（统一码、万国码、单一码）是一种在计算机上使用的字符编码。
它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。
4.char（n）固定长度，非Unicode字符数据，长度为n个字节。
n的取值范围为1至8,000，存储大小是n个字节。
CHAR字段上的索引效率级高，比如定义char（10），那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间。
（char的存储速度比varchar快）
5.Money货币数据类型。
用于十进制货币值。
2.通配符
通配符
解释
示例
‘_’
一个字符
ALike'C_'
%
任意长度的字符串
BLike'CO_%'
[]
括号中所指定范围内的一个字符
CLike'9W0[1-2]'
[^]
不在括号中所指定范围内的一个字符
DLike‘%[A-D][^1-2]'
3.插入多行数据
1.insertintoTongxunlu（姓名，地址，电子邮件）
selectsName,sAddress,sEmail
fromStudent
2.selectStudents.SName,Students.SAddress,Students.SEmail
intoTongXunLu
fromStudents
3.selectStudents.SName,Students.SAddress,Students.SEmail,identity（int,1,1）asStudentID
intoTongXunLuEX
fromStudents
4.INSERTSTUDENTS（SName,SGrade,SSex）
SELECT'张可',7,1UNION
SELECT'李扬',4,0UNION
SELECT'杨晓',2,0UNION
SELECT'汤美',3,0UNION
SELECT'苏三东',7,1UNION
SELECT'王立岩',3,1UNION
5.字符串函数
函数名
描述
举例
CHARINDEX
用来寻找一个指定的字符串在另一个字符串中的起始位置
SELECTCHARINDEX（'ACCP','MyAccpCourse',1）
返回：
4
LEN
返回传递给它的字符串长度
SELECTLEN（'SQLServer课程'）
返回：
12
LOWER
把传递给它的字符串转换为小写
SELECTLOWER（'SQLServer课程'）
返回：
sqlserver课程
UPPER
把传递给它的字符串转换为大写
SELECTUPPER（'sqlserver课程'）
返回：
SQLSERVER课程
LTRIM
清除字符左边的空格
SELECTLTRIM（'周智宇'）
返回：
周智宇（后面的空格保留）
RIGHT
从字符串右边返回指定数目的字符
SELECTRIGHT（'买卖提.吐尔松',3）
返回：
吐尔松
REPLACE
替换一个字符串中的字符
SELECTREPLACE（'莫乐可切.杨可','可','兰'）
返回：
莫乐兰切.杨兰
STUFF
在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串
SELECTSTUFF（'ABCDEFG',2,3,'我的音乐我的世界'）
返回：
A我的音乐我的世界EFG
CEILING
返回大于或等于所给数字表达式的最小整数
SELECTCEILING（43.5）
返回：
44
FLOOR
取小于或等于指定表达式的最大整数
SELECTFLOOR（43.5）
返回：
43
Sign
对于正数返回+1，对于负数返回-1，对于0则返回0
SELECTSIGN（-43）
返回：
-1
Sqrt
取浮点表达式的平方根
SELECTSQRT（9）
返回：
3
CONVERT
用来转变数据类型
SELECTCONVERT（VARCHAR（5），12345）
返回：
字符串12345
CURRENT_USER
返回当前用户的名字
SELECTCURRENT_USER
返回：
你登录的用户名
DATALENGTH
返回用于指定表达式的字节数
SELECTDATALENGTH（'中国A盟'）
返回：
7
HOST_NAME
返回当前用户所登录的计算机名字
SELECTHOST_NAME（）
返回：
你所登录的计算机的名字
SYSTEM_USER
返回当前所登录的用户名称
SELECTSYSTEM_USER
返回：
你当前所登录的用户名
7.什么是事务
1.事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作
2.这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行
3.事务是一个不可分割的工作逻辑单元
8.事务必须具备以下四个属性，简称ACID属性：
1.原子性（Atomicity）：
事务是一个完整的操作。
事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
2.一致性（Consistency）：
当事务完成时，数据必须处于一致状态
3.隔离性（Isolation）：
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
4.永久性（Durability）：
事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性
9.如何创建事务
1.开始事务：
BEGINTRANSACTION
2.提交事务：
COMMITTRANSACTION
3.回滚（撤销）事务：
ROLLBACKTRANSACTION
……关键语句讲解………
BEGINTRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE@errorSumINT
SET@errorSum=0--初始化为0，即无错误
/*--转账：
张三的账户少1000元，李四的账户多1000元*/
UPDATEbankSETcurrentMoney=currentMoney-1000
WHEREcustomerName='张三'
SET@errorSum=@errorSum+@@error
UPDATEbankSETcurrentMoney=currentMoney+1000
WHEREcustomerName='李四'
SET@errorSum=@errorSum+@@error--累计是否有错误
IF@errorSum<>0--如果有错误
BEGIN
print'交易失败，回滚事务'
ROLLBACKTRANSACTION
END
ELSE
BEGIN
print'交易成功，提交事务，写入硬盘，永久的保存'
COMMITTRANSACTION
END
GO
print'查看转账事务后的余额'
SELECT*FROMbank
GO
10.什么是视图
视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
视图的用途
1.筛选表中的行
2.防止未经许可的用户访问敏感数据
3.降低数据库的复杂程度
4.将多个物理数据库抽象为一个逻辑数据库
IFEXISTS（SELECT*FROMsysobjectsWHERE
name='view_stuInfo_stuMarks'）--检测是否存在
DROPVIEWview_stuInfo_stuMarks--删除视图
GO
CREATEVIEWview_stuInfo_stuMarks--创建视图
AS
SELECT姓名=stuName,学号=stuInfo.stuNo,
笔试成绩=writtenExam,机试成绩=labExam,
平均分=（writtenExam+labExam）/2
FROMstuInfoLEFTJOINstuMarks
ONstuInfo.stuNo=stuMarks.stuNo
GO
SELECT*FROMview_stuInfo_stuMarks--使用视图
11.存储过程
1.执行速度更快
2.允许模块化程序设计
3.提高系统安全性
4.减少网络流通量
常用的系统存储过程
sp_databases
列出服务器上的所有数据库。
sp_helpdb
报告有关指定数据库或所有数据库的信息
sp_renamedb
更改数据库的名称
sp_tables
返回当前环境下可查询的对象的列表
sp_columns
回某个表列的信息
sp_help
查看某个表的所有信息
sp_helpconstraint
查看某个表的约束
sp_helpindex
查看某个表的索引
sp_stored_procedures
列出当前环境中的所有存储过程
sp_password
添加或修改登录帐户的密码。
sp_helptext
显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
EXECsp_databases
EXECsp_renamedb'Northwind','Northwind1'
USEstuDB
GO
EXECsp_tables
EXECsp_columnsstuInfo
EXECsp_helpstuInfo
EXECsp_helpconstraintstuInfo
EXECsp_helpindexstuMarks
EXECsp_helptext'view_stuInfo_stuMarks'
EXECsp_stored_procedures
常用的扩展存储过程：
xp_cmdshell
1.可以执行DOS命令下的一些的操作
2.以文本行方式返回任何输出
3.调用语法：
a）EXECxp_cmdshellDOS命令[NO_OUTPUT]
创建数据库bankDB，要求保存在D:
\bank
USEmaster
GO
EXECxp_cmdshell'mkdird:
\bank',NO_OUTPUT
IFEXISTS（SELECT*FROMsysdatabases
WHEREname='bankDB'）
DROPDATABASEbankDB
GO
CREATEDATABASEbankDB
（
…
）
GO
EXECxp_cmdshell'dirD:
\bank\'--查看文件
创建不带参数的存储过程
CREATEPROCEDUREproc_stu
AS
DECLARE@writtenAvgfloat,@labAvgfloat
SELECT@writtenAvg=AVG（writtenExam）,
@labAvg=AVG（labExam）FROMstuMarks
print'笔试平均分：
'+convert（varchar（5）,@writtenAvg）
print'机试平均分：
'+convert（varchar（5）,@labAvg）
IF（@writtenAvg>70AND@labAvg>70）
print'本班考试成绩：
优秀'
ELSE
print'本班考试成绩：
较差'
print'--------------------------------------------------'
print'参加本次考试没有通过的学员：
'
SELECTstuName,stuInfo.stuNo,writtenExam,labExam
FROMstuInfoINNERJOINstuMarksON
stuInfo.stuNo=stuMarks.stuNo
WHEREwrittenExam<60ORlabExam<60
GO
带输入参数的存储过程
CREATEPROCEDUREproc_stu
@writtenPassint,
@labPassint
[WITHENCRYPTION]//可选项加密存储过程
AS
print'--------------------------------------------------'
print'参加本次考试没有通过的学员：
'
SELECTstuName,stuInfo.stuNo,writtenExam,
labExamFROMstuInfo
INNERJOINstuMarksON
stuInfo.stuNo=stuMarks.stuNo
WHEREwrittenExam<@writtenPass
ORlabExam<@labPass
GO
调用带参数的存储过程
1.EXECproc_stu60,55
2.--或这样调用：
EXECproc_stu@labPass=55,@writtenPass=60
输入参数的默认值
CREATEPROCEDUREproc_stu
@writtenPassint=60,
@labPassint=60
AS
print'--------------------------------------------------'
print'参加本次考试没有通过的学员：
'
SELECTstuName,stuInfo.stuNo,writtenExam,
labExamFROMstuInfo
INNERJOINstuMarksON
stuInfo.stuNo=stuMarks.stuNo
WHEREwrittenExam<@writtenPass
ORlabExam<@labPass
GO
调用带参数默认值的存储过程
EXECproc_stu--都采用默认值
EXECproc_stu64--机试采用默认值
EXECproc_stu60,55--都不采用默认值
EXECproc_stu@labPass=55
带输出参数的存储过程
CREATEPROCEDUREproc_stu
@notpassSumintOUTPUT,--输出（返回）参数：
表示没有通过的人数
@writtenPassint=60,
@labPassint=60
AS
……--统计并返回没有通过考试的学员人数
SELECTstuName,stuInfo.stuNo,writtenExam,
labExamFROMstuInfoINNERJOINstuMarks
ONstuInfo.stuNo=stuMarks.stuNo
WHEREwrittenExam<@writtenPass
ORlabExam<@labPass
SELECT@notpassSum=COUNT（stuNo）
FROMstuMarksWHEREwrittenExam<@writtenPass
ORlabExam<@labPass
GO
调用带输出参数的存储过程
/*---调用存储过程----*/
DECLARE@sumint--调用时必须带OUTPUT关键字，返回结果将存放在变量@sum中
EXECproc_stu@sumOUTPUT,64
print'--------------------------------------------------'
IF@sum>=3
print'未通过人数：
'+convert（varchar（5）,@sum）+'人,
超过60%,及格分数线还应下调'
ELSE
print'未通过人数：
'+convert（varchar（5）,@sum）+'人,
已控制在60%以下，及格分数线适中'
GO
★output就是这个存储过程返回的值也可以说输出的值
--创建存储过程求最大值
CREATEPROCEDURE[dbo].[P_Max]
@aint,--输入
@bint,--输入
@Returncintoutput--输出
AS
if（@a>@b）
set@Returnc=@a
else
set@Returnc=@b
--调用
declare@Returncint
execP_Max2,3,@Returncoutput
select@Returnc
--存储过程set用法
createprocT001
as
declare@sqlvarchar（100）
set@sql='select*fromerp_user'
exec（@sql）
触发器
触发器是一种特殊的存储过程，类似于事件函数，SQLServer™允许为INSERT、UPDATE、DELETE创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列T-SQL语句。
触发器可以在查询分析器里创建，也可以在表名上点右键->“所有任务”->“管理触发器”来创建，不过都是要写T-SQL语句的，只是在查询分析器里要先确定当前操作的数据库。
创建触发器用CREATETRIGGER
CREATETRIGGER触发器名称
ON表名
FORINSERT、UPDATE或DELETE
AS
T-SQL语句
注意：
触发器名称是不加引号的。
如下是联机丛书上的一个示例，当在titles表上更改记录时，发送邮件通知MaryM。
CREATETRIGGERreminder
ONtitles
FORINSERT,UPDATE,DELETE
AS
EXECmaster..xp_sendmail'MaryM',
'Don''tforgettoprintareportforthedistributors.'
二、删除触发器
用查询分析器删除
在查询分析器中使用droptrigger触发器名称来删除触发器。
也可以同时删除多个触发器：
droptrigger触发器名称,触发器名称...
注意：
触发器名称是不加引号的。
在删除触发器之前可以先看一下触发器是否存在：
ifExists（selectnamefromsysobjectswherename=触发器名称andxtype='TR'）
用企业管理器删除
在企业管理器中，在表上点右键->“所有任务”->“管理触发器”，选中所要删除的触发器，然后点击“删除”。
三、重命名触发器
用查询分析器重命名
execsp_rename原名称,新名称
sp_rename是SQLServer™自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。
用企业管理器重命名
在表上点右键->“所有任务”->“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定”。
四、more....
INSTEADOF
执行触发器语句，但不执行触发触发器的SQL语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行delete语句。
例：
createtriggerf
ontbl
insteadofdelete
as
insertintoLogs...
IFUPDATE（列名）
检查是否更新了某一列，用于insert或update，不能用于delete。
例：
createtriggerf
on tbl
for update
as
ifupdate（status） orupdate（title）
sql_statement--更新了status或title列
inserted、deleted
这是两个虚拟表，inserted保存的是insert或update之后所影响的记录形成的表，deleted保存的是delete或update之前所影响的记录形成的表。
例：
createtriggertbl_delete
ontbl
fordelete
as
declare@titlevarchar（200）
select@title=titlefromdeleted
insertintoLogs（logContent）values（'删除了title为：
'+title+'的记录'）
说明：
如果向inserted或deleted虚拟表中取字段类型为text、image的字段值时，所取得的值将会是 null。
五、查看数据库中所有的触发器
在查询分析器中运行：
use数据库名
go
select*fromsysobjectswherextype='TR'
sysobjects保存着数据库的对象，其中xtype为TR的记录即为触发器对象。
在name一列，我们可以看到触发器名称。
六、sp_helptext查看触发器内容
用查询分析器查看
use数据库名
go
execsp_helptext'触发器名称'
将会以表的样式显示触发器内容。
除了触发器外，sp_helptext还可以显示规则、默认值、未加密的存储过程、用户定义函数、视图的文本
用企业管理器查看
在表上点右键->“所有任务”->“管理触发器”，选择所要查看的触发器存储过程
七、sp_helptrigger用于查看触发器的属性
sp_helptrigger有两个参数：
第一个参数为表名；第二个为触发器类型，为char（6）类型，可以是INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。
例：
use数据库名
go
execsp_helptriggertbl
八、递归、嵌套触发器
递归分两种，间接递归和直接递归。
我们举例解释如下，假如有表1、表2名称分别为T1、T2，在T1、T2上分别有触发器G1、G2。
∙间接递归：
对T1操
升级会员 