工作经常使用的SQL整理,实战篇(二)

原文:工作经常使用的SQL整理,实战篇(二)[原创]

工作经常使用的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) >=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整理,实战篇(三)”~

  如果您有什么问题,欢迎在下面评论,我们一起讨论,谢谢~

  如果您觉得还不错,不妨点下右下方的推荐,有您的鼓励我会继续努力的~

 

 

 

 

 

 

上一篇:windows 7 32 出现 werfault.exe的错误处理


下一篇:车辆管理系统之开始自己的任务(三)