[SQL SERVERシリーズ]仕事でよく使うSQL整理、実戦編(二)[オリジナル]
15144 ワード
仕事でよく使うSQL整理、実戦編、住所一覧:
仕事でよく使うSQL整理、実戦編(一)
仕事でよく使うSQL整理、実戦編(二)
仕事でよく使うSQL整理、実戦編(三)
続いて前の「仕事でよく使うSQL整理、実戦編(一)」では、この記事では主に、添削調査、接続、パケットと順序付け、ワイルドカード、ビュー、記憶プロセスとトランザクション、ビーコン、フリップフロップといったものを議論しています.
6.添削調査
挿入
--ユーザーテーブルデータの挿入
インセンス イント Ts_User(UserID、 UserName RealName メール、 Mobile)
values(111, 'zhangsan 'zhangsan '[email protected]', '')
--製品表データの挿入
INSERT INTO TuteuProduct(ProductID) Product Name Price、 Strage)
VALES('PD 00030' 'Benz 500500.0, 30000)
--注文書データの挿入
declare @Order ID VRCHAR(64)
--年、月、日、時、分、秒、ミリ秒を文字列で結んで注文番号とします.
SET @Order ID = DATENAME(YEAR) GETDATE() + DATENAME(MONTH) GETDATE() +DATENAME(DAY、 GETDATE()+
DATENAME(HOUR GETDATE() + DATENAME(MINUTE) GETDATE()+DATENAME(SECOND) GETDATE() +DATENAME(MILLISECOND) GETDATE()
INSERT INTO TsueuOrder(Order ID) UserID、 Product ID、 Number PostTime)
VALES(@OrderID、 115, 'PD 00040'、 10, GETDATE()
変更
Update Tシャツ セット RealName = '李四 where UserID = 112
削除
Delete from Tシャツ Where UserID = 111
簡単な検索
プロジェクト * from Tシャツ with(nolock)
プロジェクト * from Tsuchu Order with(nolock) where ID >= 2
7.接続
内部接続
--左右の表が一致する行
SELECT * FROM Tsuchu Order AS O WITH(NOLOCK)
INNER JOIN Tシャツ AS U WITH(NOLOCK) オン O.UserID = U.UserID
WHERE U.UserID = 111
左接続(左外部接続)
--左の表のすべての行を右に揃え、 右側が空いているのがNULLです.
SELECT * FROM Tシャツ AS U WITH(NOLOCK)
LEFT JOIN Tsuchu Order AS O WITH(NOLOCK) オン U.UserID = O.UserID
右接続(右外部接続)
--右の表のすべての行は左が右に、左が空いている補足NULLです.
SELECT * FROM Tsuchu Order AS O WITH(NOLOCK)
RIGHT JOIN Tute. AS P WITH(NOLOCK) オン O.Product ID = P.Product ID
全接続
--左右の表のすべての行を空のためにNULLを補います.
SELECT * FROM Tsuchu Order AS O WITH(NOLOCK)
FULL JOIN Tute. AS P WITH(NOLOCK) オン O.Product ID = P.Product ID
8.グループ化と並べ替え
UserIDでグループ化する
SELECT UserID、 COUNT(0) AS Number FROM Tsuchu Order WITH(NOLOCK) GROUTP BY. UserID
UserIDでグループ化すると注文数が3以上になります.
SELECT UserID、 COUNT(0) AS Number FROM Tsuchu Order WITH(NOLOCK) GROUTP BY. UserID HAVING COUNT(0) >=3
UserIDでグループ分けします.注文数が1以上で、注文数の昇順になります.
SELECT UserID、 COUNT(0) AS Number FROM Tsuchu Order WITH(NOLOCK) GROUTP BY. UserID HAVING COUNT(0) >=1 ORDER BY. Number ASC
9.ワイルドカード
LIKE:複数の未知の文字にマッチする
未知の文字とマッチする
--126メールボックスにマッチする
SELECT * FROM Tシャツ WITH(NOLOCK) WHERE メール LIKE '%@126.com'
--@を含むすべてのメールアドレスにマッチします.
SELECT * FROM Tシャツ WITH(NOLOCK) WHERE メール LIKE '%@%'
--16の先頭にマッチし、後に任意の文字のメールボックスがあります.
SELECT * FROM Tシャツ WITH(NOLOCK) WHERE メール LIKE '%@16_.com
--126以外のすべてのメールボックスにマッチします.
SELECT * FROM Tシャツ WITH(NOLOCK) WHERE メール NOT LIKE '%@126.com'
10.表示
ビューを削除
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE Name = 'VuTsu Total Info)
DROP VIEW VuTsu Total Info
ビューを作成
--ユーザーリスト、製品リストと注文書の関連後のすべての情報を含みます.
CREATE VIEW VuTsu Total Info
AS
SELECT O.OrderID、 O.UserID、 O.ProductID、 O.PostTime、 U.UserName、 U.RealName、
U.Email、 U.Mobile P.ProductName、 P.Price FROM Tsuchu Order AS O WITH(NOLOCK)
INNER JOIN Tシャツ AS U WITH(NOLOCK) オン O.UserID = U.UserID
INNER JOIN Tute. AS P WITH(NOLOCK) オン O.Product ID = P.Product ID
11.保存プロセスと事務
保存プロセスを作成し、まず注文表(外キーテーブル)の記録を削除し、製品表(メインキーテーブル)の記録を削除します.
12.ラベル
すべての製品の名前を取得し、「|」で区切って出力パラメータ@Namesに含めます.
13.トリガー
注文表にユーザー番号が外キーであるため、あるユーザーを直接削除する場合、当該ユーザが注文をしたら、外キーがあると削除できないというメッセージが表示されます.この場合、フリップフロップを使用することが考えられます.
フリップフロップを作成して、ユーザーテーブルのユーザーを削除すると、自動的に先に注文書から注文書を削除します.
もし何か問題があれば、下記のコメントを歓迎します.一緒に討論します.ありがとうございます.
悪くないと思いましたら、右下の勧めを注文してください.励ましがあれば頑張ります.
仕事でよく使うSQL整理、実戦編(一)
仕事でよく使うSQL整理、実戦編(二)
仕事でよく使うSQL整理、実戦編(三)
続いて前の「仕事でよく使うSQL整理、実戦編(一)」では、この記事では主に、添削調査、接続、パケットと順序付け、ワイルドカード、ビュー、記憶プロセスとトランザクション、ビーコン、フリップフロップといったものを議論しています.
6.添削調査
挿入
--ユーザーテーブルデータの挿入
インセンス イント Ts_User(UserID、 UserName RealName メール、 Mobile)
values(111, 'zhangsan 'zhangsan '[email protected]', '')
--製品表データの挿入
INSERT INTO TuteuProduct(ProductID) Product Name Price、 Strage)
VALES('PD 00030' 'Benz 500500.0, 30000)
--注文書データの挿入
declare @Order ID VRCHAR(64)
--年、月、日、時、分、秒、ミリ秒を文字列で結んで注文番号とします.
SET @Order ID = DATENAME(YEAR) GETDATE() + DATENAME(MONTH) GETDATE() +DATENAME(DAY、 GETDATE()+
DATENAME(HOUR GETDATE() + DATENAME(MINUTE) GETDATE()+DATENAME(SECOND) GETDATE() +DATENAME(MILLISECOND) GETDATE()
INSERT INTO TsueuOrder(Order ID) UserID、 Product ID、 Number PostTime)
VALES(@OrderID、 115, 'PD 00040'、 10, GETDATE()
変更
Update Tシャツ セット RealName = '李四 where UserID = 112
削除
Delete from Tシャツ Where UserID = 111
簡単な検索
プロジェクト * from Tシャツ with(nolock)
プロジェクト * from Tsuchu Order with(nolock) where ID >= 2
7.接続
内部接続
--左右の表が一致する行
SELECT * FROM Tsuchu Order AS O WITH(NOLOCK)
INNER JOIN Tシャツ AS U WITH(NOLOCK) オン O.UserID = U.UserID
WHERE U.UserID = 111
左接続(左外部接続)
--左の表のすべての行を右に揃え、 右側が空いているのがNULLです.
SELECT * FROM Tシャツ AS U WITH(NOLOCK)
LEFT JOIN Tsuchu Order AS O WITH(NOLOCK) オン U.UserID = O.UserID
右接続(右外部接続)
--右の表のすべての行は左が右に、左が空いている補足NULLです.
SELECT * FROM Tsuchu Order AS O WITH(NOLOCK)
RIGHT JOIN Tute. AS P WITH(NOLOCK) オン O.Product ID = P.Product ID
全接続
--左右の表のすべての行を空のためにNULLを補います.
SELECT * FROM Tsuchu Order AS O WITH(NOLOCK)
FULL JOIN Tute. AS P WITH(NOLOCK) オン O.Product ID = P.Product ID
8.グループ化と並べ替え
UserIDでグループ化する
SELECT UserID、 COUNT(0) AS Number FROM Tsuchu Order WITH(NOLOCK) GROUTP BY. UserID
UserIDでグループ化すると注文数が3以上になります.
SELECT UserID、 COUNT(0) AS Number FROM Tsuchu Order WITH(NOLOCK) GROUTP BY. UserID HAVING COUNT(0) >=3
UserIDでグループ分けします.注文数が1以上で、注文数の昇順になります.
SELECT UserID、 COUNT(0) AS Number FROM Tsuchu Order WITH(NOLOCK) GROUTP BY. UserID HAVING COUNT(0) >=1 ORDER BY. Number ASC
9.ワイルドカード
LIKE:複数の未知の文字にマッチする
未知の文字とマッチする
--126メールボックスにマッチする
SELECT * FROM Tシャツ WITH(NOLOCK) WHERE メール LIKE '%@126.com'
--@を含むすべてのメールアドレスにマッチします.
SELECT * FROM Tシャツ WITH(NOLOCK) WHERE メール LIKE '%@%'
--16の先頭にマッチし、後に任意の文字のメールボックスがあります.
SELECT * FROM Tシャツ WITH(NOLOCK) WHERE メール LIKE '%@16_.com
--126以外のすべてのメールボックスにマッチします.
SELECT * FROM Tシャツ WITH(NOLOCK) WHERE メール NOT LIKE '%@126.com'
10.表示
ビューを削除
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE Name = 'VuTsu Total Info)
DROP VIEW VuTsu Total Info
ビューを作成
--ユーザーリスト、製品リストと注文書の関連後のすべての情報を含みます.
CREATE VIEW VuTsu Total Info
AS
SELECT O.OrderID、 O.UserID、 O.ProductID、 O.PostTime、 U.UserName、 U.RealName、
U.Email、 U.Mobile P.ProductName、 P.Price FROM Tsuchu Order AS O WITH(NOLOCK)
INNER JOIN Tシャツ AS U WITH(NOLOCK) オン O.UserID = U.UserID
INNER JOIN Tute. AS P WITH(NOLOCK) オン O.Product ID = P.Product ID
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整理、実戦編(三)」をご覧ください.もし何か問題があれば、下記のコメントを歓迎します.一緒に討論します.ありがとうございます.
悪くないと思いましたら、右下の勧めを注文してください.励ましがあれば頑張ります.