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

工作经常使用的SQL整理，实战篇，地址一览：

　　工作经常使用的SQL整理，实战篇（一）

　　工作经常使用的SQL整理，实战篇（二）

　　工作经常使用的SQL整理，实战篇（三）

　　接着上一篇“工作经常使用的SQL整理，实战篇（一）”继续讨论，这一篇中主要讨论增删改查，连接，分组和排序，通配符，视图，存储过程和事务，游标，触发器这些东西。

6.增删改查

插入

--插入用户表数据

insertintoTse_User(UserID,UserName,RealName,Email,Mobile)

values(111,'zhangsan','zhangsan','zs@126.com','')

--插入产品表数据

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'%@126.com'

　　--匹配所有包含@的邮箱

　　SELECT*FROMTse_UserWITH(NOLOCK)WHEREEmailLIKE'%@%'

　　--匹配16开头，后面跟一个任意字符的邮箱

　　SELECT*FROMTse_UserWITH(NOLOCK)WHEREEmailLIKE'%@16_.com'

--匹配除126以外的所有邮箱

SELECT*FROMTse_UserWITH(NOLOCK)WHEREEmailNOTLIKE'%@126.com'

10.视图

　　删除视图

　　IFEXISTS(SELECT*FROMSYSOBJECTSWHEREName='V_Tse_TotalInfo')

　　DROPVIEWV_Tse_TotalInfo

　　创建视图

--包含用户表，产品表和订单表关联后的所有信息

　　CREATEVIEWV_Tse_TotalInfo

　　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.存储过程和事务

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

　　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

更多相关文章

随机推荐