工作经常使用的SQL整理,实战篇(二)
http://www.cnblogs.com/suixinpeng/p/3585856.html
工作经常使用的SQL整理,实战篇,地址一览:
工作经常使用的SQL整理,实战篇(一)
工作经常使用的SQL整理,实战篇(二)
工作经常使用的SQL整理,实战篇(三)
接着上一篇“工作经常使用的SQL整理,实战篇(一)”继续讨论,这一篇中主要讨论增删改查,连接,分组和排序,通配符,视图,存储过程和事务,游标,触发器这些东西。
6.增删改查
插入
--插入用户表数据
insert into Tse_User(UserID, UserName, RealName, Email, Mobile)
values(111, ‘zhangsan‘, ‘zhangsan‘, ‘[email protected]‘, ‘‘)
--插入产品表数据
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整理,实战篇(三)”~
如果您有什么问题,欢迎在下面评论,我们一起讨论,谢谢~
如果您觉得还不错,不妨点下右下方的推荐,有您的鼓励我会继续努力的~
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。