sql常用整理.docx
《sql常用整理.docx》由会员分享，可在线阅读，更多相关《sql常用整理.docx（19页珍藏版）》请在冰豆网上搜索。
sql常用整理
目录概览：
1.数据库
2.表
3.临时表
4.索引和约束
5.范式
6.增删改查
7.连接
8.分组和排序
9.通配符
10.视图
11.存储过程和事务
12.游标
13.触发器
14.作业
自己亲手编写的一些常用的SQL，希望对大家有用喔，废话不多说了，直接入正题~
1.数据库
创建数据库
usemaster
ifexists（select*fromsysdatabaseswherename='OrderDB'）
dropdatabaseOrderDB
createdatabaseOrderDB
on
（
name='OrderDB_data',
filename='D:
\DB\OrderDB_data.mdf',
size=10,
filegrowth=15%
）
logon
（
name='OrderDB_log',
filename='D:
\DB\OrderDB_log.ldf',
size=3,
filegrowth=10%
）
删除数据库
dropdatabaseOrderDB
2.表
创建表
--用户表
ifexists（select*fromsysobjectswherename='Tse_User'）
droptableTse_User
CreatetableTse_User
（
IDintidentity（1,1）,
UserIDintnotnull,
UserNamevarchar（64）notnull,
RealNamevarchar（64）null,
PRIMARYKEY（UserID）
）
--产品表
ifexists（select*fromsysobjectswherename='Tse_Product'）
droptableTse_Product
CreatetableTse_Product
（
IDINTIDENTITY（1,1）,
ProductIDvarchar（64）notnull,
ProductNamevarchar（256）notnull,
Pricefloatnotnull,
Storageintnotnull,--库存
PRIMARYKEY（ProductID）
）
--订单表
ifexists（select*fromsysobjectswherename='Tse_Order'）
droptableTse_Order
CreatetableTse_Order
（
IDintidentity（1,1）,
OrderIDvarchar（64）notnull,
UserIDintnotnull,
ProductIDvarchar（64）notnull,
Numberintnotnull,--购买数量
PostTimedatetimenotnull,
PRIMARYKEY（OrderID）,
FOREIGNKEY（UserID）REFERENCESTse_User（UserID）,
FOREIGNKEY（ProductID）REFERENCESTse_Product（ProductID）
）
删除表
droptableTse_User
清空表
truncatetableTse_User清除表中所有数据，下次插入编号从1开始
deletefromTse_User清除表中所有数据，但下次插入编号从原有编号+1开始
3.临时表
生成临时表，插入数据，将员工姓名全部打印出来
usemaster
go
createtable#Employee
（
IDintidentity（1,1）,
Namevarchar（64）notnull,
primarykey（ID）
）
insertinto#Employee（Name）values（'zhangsan'）
insertinto#Employee（Name）values（'lisi'）
insertinto#Employee（Name）values（'wangwu'）
insertinto#Employee（Name）values（'tony'）
insertinto#Employee（Name）values（'mike'）
declare@iint
declare@Namevarchar（64）
declare@Countint
declare@Strnvarchar（4000）
set@i=0
select@Count=COUNT（0）from#Employee
while（@i<@Count）
begin
set@Str='selecttop1@Name=Namefrom#Employeewhereidnotin（selecttop'+
STR（@i）+'idfrom#Employee）'
execsp_executesql@Str,N'@Namevarchar（64）output',@Nameoutput
select@Name,@i
set@i=@i+1
End
查看表结构及表附加属性
SP_HELPTse_User
4.索引和约束
聚集索引确定表中数据的物理顺序。
聚集索引类似于电话簿，后者按姓氏排列数据。
由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。
但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。
非聚集索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
一个表可以创建多个非聚集索引。
创建聚集索引
CREATEUNIQUECLUSTEREDINDEX[PK_Tse_ID]ON[dbo].[Tse_User]
（--唯一聚集索引
[ID]ASC
）WITH（PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,
ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON）ON[PRIMARY]
GO
创建非聚集索引
CREATEUNIQUENONCLUSTEREDINDEX[IX_Tse_UserID]ON[dbo].[Tse_User]
（--唯一非聚集索引
[UserID]ASC
）WITH（PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,
ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON）ON[PRIMARY]
GO
约束
altertableTse_User
addconstraintCS_UserNamecheck（len（Username）>3）,
constraintCS_Emailcheck（charindex（'@',Email）>0）
5.范式
第一范式1NF
第一范式需满足两个条件：
1）每个数据行必须包含具有原子性（即不可再分）的值；
2）每个数据行必须包含一个独一无二的值，即主键。
举例：
假如客户表中存在地址列，如果经常需要按城市归类，那么，应该地址列拆分为省份，城市，县，街道地址等列。
第二范式2NF
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
也就是说在一个数据库表中，一个表中只能保存一种 数据，不可以把多种数据保存在同一张数据库表中。
举例：
比如常用的选课表中，以学号和课程号为联合主键，不能将课程名，学分等课程相关信息写入选课表，因为他们只与主键的一部分（课程号）相关。
第三范式3NF
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
举例：
订单表中以订单号为主键，用户真实姓名和邮箱等信息与用户有关，与订单没有直接关系，因此，用户真实姓名和邮箱等不能放到订单表中。
由于时间关系，余下的几个问题在下一篇中讨论，谢谢关注~，下一篇地址为：
工作经常使用的SQL整理，实战篇
（二）
接着上一篇“工作经常使用的SQL整理，实战篇
（一）”继续讨论，这一篇中主要讨论增删改查，连接，分组和排序，通配符，视图，存储过程和事务，游标，触发器这些东西。
6.增删改查
插入
--插入用户表数据
insertintoTse_User（UserID,UserName,RealName,Email,Mobile）
values（111,'zhangsan','zhangsan','zs@',''）
--插入产品表数据
INSERTINTOTse_Product（ProductID,ProductName,Price,Storage）
VALUES（'PD00030','Benz',500500.0,30000）
--插入订单表数据
declare@OrderIDVARCHAR（64）
--将年，月，日，时，分，秒，毫秒以字符串形式连接起来作为订单号
SET@OrderID=DATENAME（YEAR,GETDATE（））+DATENAME（MONTH,GETDATE（））+DATENAME（DAY,GETDATE（））+
DATENAME（HOUR,GETDATE（））+DATENAME（MINUTE,GETDATE（））+DATENAME（SECOND,GETDATE（））+DATENAME（MILLISECOND,GETDATE（））
INSERTINTOTse_Order（OrderID,UserID,ProductID,Number,PostTime）
VALUES（@OrderID,115,'PD00040',10,GETDATE（））
修改
UpdateTse_UsersetRealName='李四'whereUserID=112
删除
DeletefromTse_UserWhereUserID=111
简单查询
select*fromTse_Userwith（nolock）
select*fromTse_Orderwith（nolock）whereID>=2
7.连接
内连接
--左右表匹配的行
SELECT*FROMTse_OrderASOWITH（NOLOCK）
INNERJOINTse_UserASUWITH（NOLOCK）ONO.UserID=U.UserID
WHEREU.UserID=111
左连接（左外连接）
--左边表中所有行，右边匹配左边,右边为空的补NULL
SELECT*FROMTse_UserASUWITH（NOLOCK）
LEFTJOINTse_OrderASOWITH（NOLOCK）ONU.UserID=O.UserID
右连接（右外连接）
--右边表中所有行，左边匹配右边，左边为空的补NULL
SELECT*FROMTse_OrderASOWITH（NOLOCK）
RIGHTJOINTse_ProductASPWITH（NOLOCK）ONO.ProductID=P.ProductID
全连接
--左右表所有行，为空的补NULL
SELECT*FROMTse_OrderASOWITH（NOLOCK）
FULLJOINTse_ProductASPWITH（NOLOCK）ONO.ProductID=P.ProductID
8.分组和排序
按UserID分组
SELECTUserID,COUNT（0）ASNumberFROMTse_OrderWITH（NOLOCK）GROUPBYUserID
按UserID分组，订单数量大于等于3
SELECTUserID,COUNT（0）ASNumberFROMTse_OrderWITH（NOLOCK）GROUPBYUserIDHAVINGCOUNT（0）>=3
按UserID分组，订单数量大于等于1，按订单数量升序
SELECTUserID,COUNT（0）ASNumberFROMTse_OrderWITH（NOLOCK）GROUPBYUserIDHAVINGCOUNT（0）>=1ORDERBYNumberASC
9.通配符
LIKE：
匹配多个未知字符
_：
匹配一个未知字符
--匹配126邮箱的
SELECT*FROMTse_UserWITH（NOLOCK）WHEREEmailLIKE'%@'
--匹配所有包含@的邮箱
SELECT*FROMTse_UserWITH（NOLOCK）WHEREEmailLIKE'%@%'
--匹配16开头，后面跟一个任意字符的邮箱
SELECT*FROMTse_UserWITH（NOLOCK）WHEREEmailLIKE'%@16_.com'
--匹配除126以外的所有邮箱
SELECT*FROMTse_UserWITH（NOLOCK）WHEREEmailNOTLIKE'%@'
10.视图
删除视图
IFEXISTS（SELECT*FROMSYSOBJECTSWHEREName='V_Tse_TotalInfo'）
DROPVIEWV_Tse_TotalInfo
创建视图
--包含用户表，产品表和订单表关联后的所有信息
CREATEVIEWV_Tse_TotalInfo
AS
SELECTO.OrderID,O.UserID,O.ProductID,O.PostTime,U.UserName,U.RealName,
U.Email,U.Mobile,P.ProductName,P.PriceFROMTse_OrderASOWITH（NOLOCK）
INNERJOINTse_UserASUWITH（NOLOCK）ONO.UserID=U.UserID
INNERJOINTse_ProductASPWITH（NOLOCK）ONO.ProductID=P.ProductID
11.存储过程和事务
创建存储过程，先删除订单表（外键表）中的记录，再删除产品表（主键表）中的记录
CREATEPROCEDURE[dbo].[SC_Tse_DeleteProduct]
（
@ProductIDVARCHAR（64）,
@Resultintoutput
）
AS
BEGIN
SETNOCOUNTON;
BEGINTRAN--开始事务
BEGIN
DELETEFROMTse_OrderWHEREProductID=@ProductID
DELETEFROMTse_ProductWHEREProductID=@ProductID
IF（@@ERROR<>0）
BEGIN
SET@Result=-999
ROLLBACKTRAN--回滚
END
ELSE
BEGIN
SET@Result=888
COMMITTRAN--提交
END
END
END
12.游标
获取所有产品的名字，以‘|’分隔，包含在输出参数@Names中
CREATEPROCEDURESC_Tse_GetProductNames
（
@Namesvarchar（max）OUTPUT
）
AS
BEGIN
SETNOCOUNTON;
declare@ProductNamevarchar（64）
declarecurTestcursor
for（selectProductNamefromTse_Product）
opencurTest--打开游标
fetchnextfromcurTestinto@ProductName
while@@fetch_status=0--获取成功
begin
if（@ProductNameisnotnulland@ProductName<>''）
begin
if（@Namesisnullor@Names=''）
begin
set@Names=@ProductName
end
else
begin
set@Names=@Names+'|'+@ProductName
end
end
fetchnextfromcurTestinto@ProductName
end
closecurTest--关闭游标
deallocatecurTest--释放游标
END
13.触发器
因为用户编号在订单表中为外键，所以，直接删除某个用户时，如果该用户下了订单，就会提示有外键不能删除。
针对这种情况，可以考虑使用触发器。
创建触发器，删除用户表中用户时，会自动先删除订单表中的订单
CREATETRIGGERTR_Tse_DelUser
ONTse_User
INSTEADOFDELETE--代替默认的删除
AS
BEGIN
SETNOCOUNTON
DELETEFROMTse_OrderWHEREUserIDIN（SELECTUserIDFROMDeleted）
DELETEFROMTse_UserWHEREUserIDIN（SELECTUserIDFROMDeleted）
END
使用触发器，添加订单时，产品表库存相应减少
CREATETRIGGERTR_Tse_ADDOrder
ONTse_Order
AFTERINSERT
AS
BEGIN
UPDATETse_ProductSETStorage=Storage-（SELECTNumberFROMINSERTED）
WHEREProductIDIN（SELECTProductIDFROMINSERTED）
END
关于SQL定时作业部门的介绍，请看“工作经常使用的SQL整理，实战篇（三）”~
接着本系列前面两篇继续讨论。
有时候当你需要定时处理数据库数据时，你会怎么做呢？
当然你可以手工编写一个程序去完成任务，但写程序既需要时间也需要考验程序的稳定性，这里，介绍一种快捷的方法，这里只介绍SQL SERVER数据库中建立作业的方法，其他数据库我相信也有这个功能。
14.作业
在SQL SERVER数据库中建立作业的步骤大致如下：
1.安装完SQL SERVER后，打开SQL SERVER Management Studio，找到SQL SERVER代理，如下图。
2.在“作业”上点击鼠标右键，选择新建作业。
3.在弹出的界面上，常规属性中填写名称。
4.单击“步骤”属性，再点击新建。
5.填写步骤名称，类型，选择数据库，命令填写你要执行的存储过程或sql语句。
6.选择高级属性，由于这里只有一步，所以选择“退出报告成功的作业”，也可以选择脚本输出到文件的路径。
点击确定。
7.下一步点击“计划”-“新建”。
8.执行计划中输入名称，选择计划类型，执行频率（每天/每周/每月）,按你想要的选择。
9.还可以在通知属性中，选择作业完成后发电子邮件等操作（如果远程的话）。
10.最后点击确定，这个作业就新建好了。
11.新建成功后，从作业中可以看到这个作业。
12.另外，你还可以通过作业活动监视器监视作业的执行情况。
以上就是SQLSERVER数据库新建定时作业的全过程，希望对你有用，有错误的地方，请指出来喔，谢谢~
升级会员 