[SQL SERVER系列]工作经常使用的SQL整理，实战篇（二）[原创]

fateame

　　工作经常使用的SQL整理，实战篇，地址一览：
　　工作经常使用的SQL整理，实战篇（一）
　　工作经常使用的SQL整理，实战篇（二）
　　工作经常使用的SQL整理，实战篇（三）
　　
　　接着上一篇“工作经常使用的SQL整理，实战篇（一）”继续讨论，这一篇中主要讨论增删改查，连接，分组和排序，通配符，视图，存储过程和事务，游标，触发器这些东西。
6.增删改查
插入
--插入用户表数据
insert into Tse_User(UserID, UserName, RealName, Email, Mobile)
values(111, 'zhangsan', 'zhangsan', 'zs@126.com', '')

--插入产品表数据
INSERT INTO Tse_Product(ProductID, ProductName, Price, Storage)
VALUES('PD00030', 'Benz', 500500.0, 30000)
　
--插入订单表数据
declare @OrderID VARCHAR(64)

--将年，月，日，时，分，秒，毫秒以字符串形式连接起来作为订单号
　　SET @OrderID = DATENAME(YEAR, GETDATE()) + DATENAME(MONTH, GETDATE()) +DATENAME(DAY, GETDATE())+
　　DATENAME(HOUR, GETDATE()) + DATENAME(MINUTE, GETDATE())+DATENAME(SECOND, GETDATE()) +DATENAME(MILLISECOND, GETDATE())
　　INSERT INTO Tse_Order(OrderID,  UserID,  ProductID,  Number,  PostTime)
　　VALUES(@OrderID,  115,  'PD00040',  10,  GETDATE())

修改
Update Tse_User set RealName = '李四' where UserID = 112

删除
Delete from Tse_User Where UserID = 111

简单查询
select * from Tse_User with(nolock)
select * from Tse_Order with(nolock) where ID >= 2

7.连接
内连接
--左右表匹配的行
SELECT * FROM Tse_Order AS O WITH(NOLOCK)
INNER JOIN Tse_User AS U WITH(NOLOCK) ON O.UserID = U.UserID
WHERE U.UserID = 111

左连接（左外连接）
--左边表中所有行，右边匹配左边, 右边为空的补NULL
SELECT * FROM Tse_User AS U WITH(NOLOCK)
LEFT JOIN Tse_Order AS O WITH(NOLOCK) ON U.UserID = O.UserID

右连接（右外连接）
--右边表中所有行，左边匹配右边，左边为空的补NULL
SELECT * FROM Tse_Order AS O WITH(NOLOCK)
RIGHT JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID

全连接
--左右表所有行，为空的补NULL
SELECT * FROM Tse_Order AS O WITH(NOLOCK)
FULL JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID

8.分组和排序
按UserID分组
SELECT UserID, COUNT(0) AS Number FROM Tse_Order WITH(NOLOCK) GROUP BY UserID

按UserID分组，订单数量大于等于3
SELECT UserID, COUNT(0) AS Number FROM Tse_Order WITH(NOLOCK) GROUP BY UserID HAVING COUNT(0) >=3

按UserID分组，订单数量大于等于1，按订单数量升序
SELECT UserID, COUNT(0) AS Number FROM Tse_Order WITH(NOLOCK) GROUP BY UserID HAVING COUNT(0) >=1 ORDER BY Number ASC

9.通配符
LIKE：匹配多个未知字符
_：匹配一个未知字符

--匹配126邮箱的
SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE '%@126.com'

　　--匹配所有包含@的邮箱
　　SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE '%@%'

　　--匹配16开头，后面跟一个任意字符的邮箱
　　SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE '%@16_.com'

--匹配除126以外的所有邮箱
SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email NOT LIKE '%@126.com'

10.视图
　　删除视图
　　IF EXISTS (SELECT * FROM SYSOBJECTS WHERE Name = 'V_Tse_TotalInfo')
　　DROP VIEW V_Tse_TotalInfo

　　创建视图
--包含用户表，产品表和订单表关联后的所有信息
　　CREATE VIEW V_Tse_TotalInfo
　　AS
　　SELECT O.OrderID, O.UserID, O.ProductID, O.PostTime, U.UserName, U.RealName,
　　U.Email, U.Mobile, P.ProductName, P.Price FROM Tse_Order AS O WITH(NOLOCK)
　　INNER JOIN Tse_User AS U WITH(NOLOCK) ON O.UserID = U.UserID
　　INNER JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID

11.存储过程和事务
创建存储过程，先删除订单表（外键表）中的记录，再删除产品表（主键表）中的记录



　　CREATE PROCEDURE [dbo].[SC_Tse_DeleteProduct]
　　(
　　    @ProductID VARCHAR(64),
　　    @Result int output
　　)
　　AS
　　BEGIN
　　    SET NOCOUNT ON;
　　
　　    BEGIN TRAN   --开始事务
　　    BEGIN
　　        DELETE FROM Tse_Order WHERE ProductID = @ProductID
　　        
　　        DELETE FROM Tse_Product WHERE ProductID = @ProductID
　　        
　　        IF (@@ERROR  0)
　　        BEGIN
　　            SET @Result = -999
　　            ROLLBACK TRAN   --回滚
　　        END
　　        ELSE
　　        BEGIN
　　            SET @Result = 888
　　            COMMIT TRAN     --提交
　　        END
　　    END
　　END

12.游标
获取所有产品的名字，以‘|’分隔，包含在输出参数@Names中



　　CREATE PROCEDURE SC_Tse_GetProductNames
(
@Names varchar(max) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
declare @ProductName varchar(64)
declare curTest cursor
for (select ProductName from Tse_Product)
open curTest                --打开游标
fetch next from curTest into @ProductName
while @@fetch_status = 0   --获取成功
begin
if (@ProductName is not null and @ProductName  '')
begin
if (@Names is null or @Names = '')
begin
set @Names = @ProductName
end
else
begin
set @Names = @Names + '|'+ @ProductName
end
end
fetch next from curTest into @ProductName
end
close curTest            --关闭游标
deallocate curTest        --释放游标
END
　　
13.触发器
因为用户编号在订单表中为外键，所以，直接删除某个用户时，如果该用户下了订单，就会提示有外键不能删除。针对这种情况，可以考虑使用触发器。
创建触发器，删除用户表中用户时，会自动先删除订单表中的订单



　　CREATE TRIGGER TR_Tse_DelUser
ON Tse_User
INSTEAD OF DELETE  --代替默认的删除
AS
BEGIN
SET NOCOUNT ON
DELETE FROM Tse_Order WHERE UserID IN (SELECT UserID FROM Deleted)
DELETE FROM Tse_User WHERE UserID IN (SELECT UserID FROM Deleted)
　　END
　　　　 使用触发器，添加订单时，产品表库存相应减少



CREATE TRIGGER TR_Tse_ADDOrder
ON Tse_Order
AFTER INSERT
AS
BEGIN
UPDATE Tse_Product SET Storage = Storage - (SELECT Number FROM INSERTED)
WHERE ProductID IN (SELECT ProductID FROM INSERTED)
　　END
　　关于SQL定时作业部门的介绍，请看“工作经常使用的SQL整理，实战篇（三）”~
　　如果您有什么问题，欢迎在下面评论，我们一起讨论，谢谢~
　　如果您觉得还不错，不妨点下右下方的推荐，有您的鼓励我会继续努力的~