工作经常使用的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整理，实战篇（三） ”~
如果您有什么问题，欢迎在下面评论，我们一起讨论，谢谢~
如果您觉得还不错，不妨点下右下方的推荐，有您的鼓励我会继续努力的~
