【网学网提醒】：网学会员为需要朋友们搜集整理了SqlServer技巧大全相关资料，希望对各位网友有所帮助!
SQLServer技巧大全
数据完整性1.实体完整性约束：数据行不能有重复，每一行数据都由主键来唯一确定2.域完整性约束：实现了对输入到特定列的数值的限制3.引用完整性约束：用来保持表之间已定义的关系4.自定义完整性约束：用来定义特定的规则SQLServer中存在五种约束，分别是：主键约束、外键约束、检查约束、默认约束和唯一性约束如果建立了主表和子表的关系，则：●●●子表中的相关项目的数据，在主表中必须存在主表中相关项的数据更改了，则子表对应的数据项也应当随之更改在删除子表之前，不能够删除主表
T-SQL的组成：◇DML(数据操作语言)用来查询\插入\删除和修改数据库中的数据◇DCL(数据控制语言)用来控制数据库组件的存取许可、存取权限等的命令◇DDL(数据定义语言)用来建立数据库、数据库对象和定义其列，大部分是以create开头的命令使用INSERT插入数据行INSERT[INTO]<表名>[列名]VALUES<值列表>一次插入多行数据1.通过INSERTSELECT语句将现有表中的数据添加到新表INSERTINTOTongXuLu(‘’，’地址’，’电子邮件’)SELECTSName，SAddress，SEmailFROMStudents2.通过SELECTINTO语句将现有表中的数据添加新表SELECTStudents.SName，Students.SAddress，Students.SEmailINTOTongXuLuFROMStudents创建一个新的标识列SELECTIDENTITY(数据类型，标识种子，标识增长量)AS列名INTO新表FORM原始表3.通过UNION关键字合并数据进行插入INSERTStudents(SName,SGrade,SSex)Select‘女生1’，7，0UNION…Select‘女生n’，7，0使用T-SQL更新数据UPDATE<表名>SET<列名=更新值>[WHERE<更新条件>]使用T-SQL删除数据DELECTFROM<表名>[WHERE<删除条件>]使用TRUNCATETABLE删除数据TRUNCATETABLE表名
使用SELECT语句进行查询SELECT<列名>FROM<表名>[WHERE<查询条件表达式>][ORDERBY<排序的列名>[ASC或DESC]]查询空行SelectSNamefromStudentswhereSEmailisnull查询返回限制的行数Selecttop5SName，SAddressfromStudentswhereSSex=0Selecttop20percentSName，SAddressfromStudentswhereSSex=0字符串函数
日期函数
数学函数
系统函数
使用LINK进行模糊查询Select*fromCardwhereIDLINK‘00[^8]%[A，C]%’使用BETWEEN在某个范围内进行查询Select*fromScorewhereScoreBETWEEN60AND80使用IN在列举值内进行查询SelectSNamefromStudentswhereSAddressIn(‘北京’，’广州’，’上海’)SQLServer中的聚合函数1.SUM：返回表达式中所有数值的总和2.AVG：返回表达式中所有数值的平均值3.MAX和MIN：返回表达式中的最大值和最小值4.COUNT：返回提花的表达式中非空值的计数使用GroupBy进行分组查询SelectCourseID，AVG(Score)fromScoreGroupByCourseID使用HAVING子名进行分组筛选SelectStudentID，CourseID，AVG(Score)fromScoreGroupByStudentID，CourseIDHavingcount(Score)>1多表联接查询1.内联接：根据表中共同的列来进行匹配(1)在WHERE子句中指定联接条件SelectStudents.SName，Score.CourseID，Score.ScorefromStudents，ScorewhereStudents.Score=Score.StudentID(2)在FROM子句中使用JOIN…ONSelectS.SName，C.CourseID，C.ScorefromStudentsasSINNERJOINScoreasCon(S.Score=C.StudentID)2.外联接//使用NOT来对限制条件″取反″操作//查询不是八朋发行的A卡或C卡
(1)左外联接查询：结果集包括LEFTOUTER子名中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没匹配行，则在相关的结果集行中右表的所有选择列均为空值
SelectS.SName，C.CourseID，C.ScorefromStudentsasSLEFTOUTERJOINScoreasConS.Score=C.StudentID
(2)右外联接查询：返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值
SelectTitles.Title_id，Titles.Title，Publishers.Pub_namefromTitlesRIGHTOUTERJOINPublisherson
Titles.Pub_id=Publishers.Pub_id
(3)完整外查询：FULLJOIN或FULLOUTERJOIN，返回左表和右表中的所有行，当某行在另一个表中没有匹配行时，则另一个表的选择列包含空值，如果表之间有匹配行，则整个结果集行包含基表的数据值
3.交叉联接：返回左表中的所有行，左表中的每一行与右表中的所有行再一一组合，相当于两个表″相乘″
select*fromspt_valuescrossjoinMSreplication_options
数据库设计：就是将数据库中的数据对象以及这些数据对象之间关系，进行规划和结构化的过程
设计数据库的步骤：
(1)收集信息
(2)标识对象(实体)
(3)标识每个对象需要存储的详细信息(属性)
(4)标识对象(实体)之间的关系
实体-关系模型：
1.实体：指现实世界中具有区分其他事物的特征或属性并与其他实体有联系的对象
2.属性：实体的特征
3.关系：两个或多个实体之间的联系
4.映射基数：表示可以通过关系与该实体关联的其他实体的个数
一对一、一对多、多对一和多对多
5.实体关系图
E-R图的组成包括以下几部分
♂矩形表示实体集
♂椭圆形表示属性
♂菱形表示关系集
♂直线用来连接属性和实体集，也用来连接实体集和关系集
数据规范化：从关系数据库表中除去冗余数据的过程。如果使用得当规范化是用于获得高效的关系数据库表的逻辑结构的最好和最容易的方法。规范化数据时，应执行下列操作
1.将数据库的结构精简为最简单的形式
2.从表中删除冗余的列
3.标识所有依赖于其他数据的数据
规范设计
1.第一范式(1NF，NormalFormate)
确保每列的原子性。如果每列(或每个属性值)都是不可再分的最小数据单元(也称为最小的原子单元)，则满足第一范式
2.第二范式(2NF)
第二范式在第一范式的基础上，更进一层，其目标是确保表中的每列都和主键相关：如果一个关系满足1NF，并且除了主键以外的其他列，都依赖于该主键，则满足第二范式
3.第三范式(3NF)
第三范式在第二范式的基础上，更进一层，第三范式的目标是确保每列都和主键列直接相关，而不是间接相关。如果一个关系满足2NF，并且除了主键以外的其他列都不依赖于主键列，则满足第三范式SQLServer中的数据类型
用T-SQL建立文件夹目录：execxp_cmdshell'mdc:\wuguanfeng'语法：EXECxp_cmdshellDOS命令[NO_OUTPUT]其中EXEC表示调用存储过程，NO_OUTPUT为可选参数，设置执行DOS命令后是否输出返回信息如果不能执行xp_cmdshell，则用命令行参数sp_configure'showadvancedoption',1数据库文件由以下3部分绘成1.主数据文件：*.mdf2.次要数据文件：*.ndf3.日志文件：*.ldf创建数据库Createdatabase数据库名on[primary](<数据库文件参数>[，…n][<文件组参数>])[Logon]({<日志文件参数>[，…n]})文件的具体参数语法如下：([NAME=逻辑文件名，]FILENAME=物理文件名[，SIZE=大小][，MAXSIZE={最大容量｜UNLIMITED}][，FILEGROWTH=增长量])[，…n]其中，[]表示可选部分，{}表示必需的部分删除数据库Ifexists(select*fromsysdatabaseswherename=’数据库名’)Dropdatabase数据库名创建表Createtable表名(字段1数据类型列的特征，字段2数据类型列的特征，…)删除表Ifexists(select*fromsysobjectswherename=’表名’)Droptable表名
使用SQL语句创建约束---添加主键约束(将stuNo作为主键)ALTERTABLEstuInfoADDCONSTRAINTPK_stuNoPRIMARYKEY(stuNo)---添加唯一约束(身份证号唯一，因为每个人的身份证号全国唯一)ALTERTABLEstuInfoADDCONSTRAINTUQ_stuIDUNIQUE(stuID)---添加默认约束(如果地址不填，默认为″地址不祥″)ALTERTABLEstuInfoADDCONSTRAINTDF_stuAddressDEFAULT(‘地址不祥’)FORstuAddress---添加检查约束(要求年龄只能在15～40岁之间)ALTERTABLEstuInfoADDCONSTRAINTCK_stuAgeCHECK(stuAgeBETWEEN15AND40)---添加外键约束(主表stuInfo和从表stuMarks建立关系，关联字段为stuNo)ALTERTABLEstuMarksADDCONSTRAINTFK_stuNoFOREIGNKEY(stuNo)REFERENCESstuNo(stuNo)使用SQL语句删除约束ALTERTABLEstuInfoDROPCONSTRAINTDF_stuAddress使用SQL语句创建登录创建登录账户Windows：EXECsp_grantlogin‘windows域名\域账户’SQL：EXECsp_addlogin‘账户名’，’密码’创建数据库用户EXECsp_grantdbaccess‘登录账户’，’数据库用户’给数据库用户授权Grant权限[ON表名]TO数据库用户局部变量DECLARE@variable_nameDataTypevariable_name为局部变量的名称，DataType为数据类型使用SET语句或SELECT语句赋值全局变量
输出语句Print局部变量或字符串Select局部变量as自定义列名IF-ELSE条件语句IF(条件)语句或语句块ELSE语句或语句块同Java语言一样，ELSE为可选，如果有多条语句，需要使用语句块，用BEGIN…END表示为了把输出的表格数据和文本消息显示在同一个窗口中，需要做如下设置：单击MicrosoftSQLServerStudio的菜单中″工具″?″选项″命令，选择″查询结果″选项，将″显示结果的默认方式″设置为″以文本格式显示结果″WHILE循环语句WHILE(条件)语句或语句块[BREAK]使用BREAK关键字从最内层的WHILE循环中退出CASE多分支语句CASEWHEN条件1THEN结果1WHEN条件2THEN结果2[ELSE其他结果]END批处理语句：它是一条或多条SQL语句的集合，主要好处是能够简化数据库的管理、提高语句执行的效率，批处理结束的标志是″GO″子查询：一般来说，表连接都可以用子查询替换，但反过来说却不一定，有的子查询不能用表连接替换，子查询比较灵活、方便，形式多样，适合于作为查询的筛选条件，而表连接更适合于查看多表的数据
IN和NOTIN子查询
IN后面的子查询可以返回多条记录，用于限制某列的筛选范围
EXISTS和NOTEXISTS子查询
从理论上讲，EXISTS可以作为WHILE语句的子查询，但一般用于IF语句的存在检测，如果子查询的结果非空，则EXISTS(子查询)将返回true，否则返回false
合并多个表中的数据有以下3种方法
4.联合(Union)----合并多个数据表中的行
5.子查询----将一个查询包含到另一个查询中
6.连接----合并多个数据表中的列
事务：是指一组相互依赖的操作单元的集合，用来保证对数据库的正确修改，保持数据的完整性，如果一个事务的某个单元操作失败，将取消本次事务的全部操作。数据库事务必须具备以下特征(简称ACID)：
(1)原子性(Atomicity)：事务是一个完整的操作，条元素是不可分的，所有元素必须作为一个整体提交或回滚，如果事务中的任何元素失败，则整个事务将失败
(2)一致性(Consistency)：当事务完成时，数据必须处于一致状态
(3)隔离性(Isolation)：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或在另一个使用相同数据的事务结束之后访问这些数据
(4)持久性(Durability)：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障，也将一直保持
开始事务：BEGINTRANSACTION
提交事务：COMMITTRANSACTION
回滚事务：ROLLBACKTRANSACTION
事务的分类有以下3种
◆◆◆显式事务：用BEGINTRANSACTION明确指定事务的开始
隐式事务：通过设置SETIMPLICIT_TRANSACTIONSON语句，将隐式事务模式设置为打开。当以隐式事务操作时，SQLServer将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需提交或回滚事务
自动提交事务：这是SQLServer的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交。如果错误，则自动回滚
索引：可以大大提高数据库的检索速度，改善数据库性能
唯一索引：不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则一般情况下大多数数据库都不允许创建唯一索引。创建了唯一约束，将自动创建唯一索引，尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束
主键索引：在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。它要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据
聚集索引：在聚集索引中，表中条行的物理顺序与键值的逻辑(索引)顺序相同。表只能包含一个聚集索引。如果不是聚集索引，则表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引有更快的数据访问速度。在SQLServer中，一个表只能创建一个聚集索引，但可以有多个(最多249个)非聚集索引，设置某列为主键，该列就默认为聚集索引
创建索引
CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名
ON表名(列名[，列名]…)[WITHFILLFACTOR=x]
UNIQUE指定唯一索引，可选；CLUSTERED、NONCLUSTERED指定是聚集索引还是非聚集索引，可选；FILLFACTOR表示填充因子，指定0～100的值，该值指示索引面填满的空间所占的百分比
删除索引
IFEXISTS(select*fromsysindexeswherename=’索引名’)DROPINDEX表名.索引名指定按索引查询Select*from表名(索引名)where条件视图：存在于数据库中的″虚拟表″，用户可以像使用表一样使用视图创建视图CREATEVIEW视图名AS
修改视图ALTERVIEW视图名AS删除视图IFEXISTS(select*fromsysobjectswherename=视图名)DROPVIEW视图名常用的系统存储过程
存储过程的优点(1)允许模块化程序设计(2)允许更快地执行(3)减少网络流量(4)可作为安全机制使用创建存储过程CREATEPROC存储过程名[{@参数1数据类型}[=默认值][OUTPUT]，……，{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句修改存储过程ALTERPROCp_selectGoods@goodsIDint=1--定义参数
ASSelect*fromtb_goodswhereID>@goodsID删除存储过程IFESISTS(select*fromsysobjectswherename=存储过程名)DROPPROC存储过程名获取指定存储过程语句EXECsp_helptext[@objname=]'name'参数说明●●sp_helptext：系统存储过程名称[@objname=]'name'：对象的名称，对象必须在当前数据库中存在
/*---创建并调用不带参数的存储过程---*/CREATEPROCp_selectUserASselect*fromtb_userwhereStatus=＇普通用户＇EXECp_selectUser/*---创建并调用带输入参数的存储过程---*/CREATEPROCp_selectGoods@goodsIDint=1ASSelect*fromtb_goodswhereID=@goodsIDEXECp_selectGoods@goodsID=13/*---创建并调用带输出参数的存储过程---*/CREATEPROCp_getMaxID@orderIDvarchar(30)outputASset@orderID='创建带返回参数的存储过程'DECLARE@maxIDvarchar(30)EXECp_getMaxID@maxIDoutputSELECT@maxID处理错误信息RAISERROR：用于在SQLServer系统返回错误信息的同时返回用户指定的信息，语法：RAISERROR({msg_id|msg_str}{,severity,state})[,argument[,...n]][WITHoption[,...n]]♂msg_id：存储于sysmessages表中的用户定义的错误信息。用户定义错误信息的错误号应大于50000♂msg_str：是一条特殊消息，最多可包含400个字符♂severity：用户定义的与消息关联的严重级别。从0-18之间为严重级别，19-25之间的为严重级别只能由sysadmin固定服务器角色成员使用，必须选择WITHLOG选项♂state：从1-127的任意整数，表示有关错误调用状态的信息♂argument：用于取代msg_str中定义的变量或取代msg_id的消息参数，替代总数不能超过20个♂option：错误的自定义选项RAISERROR(‘及格线错误，请指定0-100之间的分数，统计中断退出’,16,1)引发系统错误，指定错误的严重级别为16，调用状态为1(默认)。错误的严重级别大于10，将自动设置系统全局变量@@ERROR为非零值，表示语名执行出错备份数据库--显示返回值--执行存储过程--定义参数默认值为1
BACKUPDATABASEdatabase_nameTo[With[Differential][[,]Format|Noformat][,]{Init|Noinit}]::={{Disk|Type}='physical_backup_device_name'}＊＊＊＊＊＊＊＊database_name：备份的数据库名称To：关键字，用于指定备份设备：一个备份设备，用于存储备份数据，其中Disk表示在磁盘上存储备份数据，Tage表示在磁带设备上存储备份设备。physical_backup_device_name表示磁盘事磁带上的物理路径，通常用于指定一个备份文件Differential：指定数据库备份或文件备份应该与上一次完整备份后改变的数据库或文件部分保持一致Format：表示重写媒体头Noformat：表示指定媒体头不应写入所有用于该备份操作的卷中，并不要重写该备份设备Init：表示重写所有备份集，介保留媒体头Noinit：表示备份集将追加到指定的磁盘或磁带设备上，以保留现有的备份集，该选项是默认设置
还原数据库RESTOREDATABASEdatabase_name[From[,…n]][With[File=file_number]]＊＊＊＊＊RESTOREDATABASE：关键字database_name：要还原的数据库名称From：关键字，表示从哪个备份设备上还原：用于指定一个备份设备，可以是一个磁盘或磁带上的备份文件File=file_number：标识要还原的备份集，file_number为1表示备份媒体上的第一个备份集
附加数据库sp_attach_db[@dbname=]'dbname',[@filename1=]'filename_n'[,...]＊＊＊[@dbname=]'dbname'：要附加到服务器的数据库名称，该名称必须是惟一的Dbname：数据类型为sysname,默认值为null[@filename1=]'filename_n'：数据库文件的物理名称，包括路径，filename_n的数据类型为nvarchar(260)，默认值为null。最多可以指定16个文件名，参数名称以@filename1开始，递增到@filename16。文件名列表至少必须包括主文件，主文件包含指向数据库中其他文件的系统表。该列表还必须包括数据库分离后所有被移动的文件分离数据库sp_detach_db[@dbname]='dbname'用户自定义函数--创建自定义函数createfunctionfind(@xnvarchar(35))returnstableasreturn(select*fromdbo.spt_valueswherenamelike@x)--调用自定义函数select*fromfind('sub')--修改自定义函数alterfunctionfind()
returnstable
as
return(select*fromspt_values)
--删除自定义函数
dropfunctionfind
XML在SQLServer的应用
createtableloving(lovingcontentxml--声明XML数据类型)
declare@lovingxml--声明XML数据类型的变量
set@loving=cast('<课程><课程代号>k01<课程名称>奥运会主题曲<课程类别>歌曲类<课程内容>LOGING<学分>9'asxml)--为XML类型的变量赋值
insertlovingvalues(@loving)--将XML类型数据插入到数据表
消除重复行：selectdistinct列名from表名
查找指定数据库表的字段名,类型,注释
selectc.object_idas'对象序号',c.nameas'列名',t.nameas'数据类型',(selectvaluefrom
sys.extended_propertiesasexwhereex.major_id=c.object_idandex.minor_id=c.column_id)as'注释'from
sys.columnsascinnerjoinsys.tablesastaonc.object_id=ta.object_idinnerjoin(selectname,system_type_id
fromsys.typeswherename<>'sysname')astonc.system_type_id=t.system_type_idwhereta.name='TableName'
CAITFOR：指定触发器、存储过程或事务执行的时间、时间间隔或事件；还可以用来暂停程序的执行，直到所设定的等待时间已过才继续往下执行
waitfortime'00:00:00'
print'用来设定等待结束的时间点'
waitfordelay'00:00:05'
print'用来设定等待的时间，最多可达24小时'
产生随机数(当前的月份数*100000+当前的秒数*1000+当前的毫秒数)
declare@randnumeric(15,8)
set@rand=rand(datepart(mm,getdate())*100000+datepart(ss,getdate())*1000+datepart(ms,getdate()))
print@rand
/********************获取数据库的系统对象********************/
select*fromsys.objects
/********************查找数据库的数据库表********************/
select*fromsys.tables
/********************获取数据库的数据类型********************/
select*fromsys.types
/********************获取数据库表的字段数据********************/
select*fromsys.columnswhereobject_id=(selectobject_idfromsys.objectswherename='数据库表')
/********************获取数据库的注释********************/
select*fromsyscomments
--创建表及描述信息
createtable表(a1varchar(10),a2char(2))
--为表添加描述信息
execsp_addextendedpropertyN'MS_Description','人员信息表',N'user',N'dbo',N'table',N'表',null,null
--为字段a1添加描述信息
execsp_addextendedpropertyN'MS_Description','',N'user',N'dbo',N'table',N'表',
N'column',N'a1'
--为字段a2添加描述信息
execsp_addextendedpropertyN'MS_Description','性别',N'user',N'dbo',N'table',N'表',
N'column',N'a2'
--更新表中列a1的描述属性
execsp_updateextendedproperty'MS_Description','字段','user',dbo,'table','表','column',
a1
--删除表中列a1的描述属性
execsp_dropextendedproperty'MS_Description','user',dbo,'table','表','column',a1
--删除测试
droptable表
