工作經常使用的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整理，實戰篇（三） ”~
如果您有什么問題，歡迎在下面評論，我們一起討論，謝謝~
如果您覺得還不錯，不妨點下右下方的推薦，有您的鼓勵我會繼續努力的~
?
?
?
?
?
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
