4.20SQL学习

---4.20学习--
--IF（) ELSE语句--
USE bs
GO
DECLARE @message varchar(255)
IF (SELECT AVG(price) FROM titles WHERE type='business')<$20
BEGIN
SET @message='这是一些优秀的经济类书籍:'
PRINT @message
SELECT title
FROM titles
WHERE type='business'
END
ELSE
BEGIN
SET @message='平均书价超过$20.'
PRINT @message
END
GO

--WHILE循环
USE pubs
GO
WHILE NOT EXISTS(SELECT price FROM titles WHERE price<$30)
BEGIN
UPDATE titles
SET price=price*1.5
SELECT MAX(price) FROM titles IF(SELECT MAX(price) FROM titles)>$50
BREAK
ELSE
CONTINUE
END
PRINT'书价太贵了!'
GO

--游标
USE pubs
GO
--定义游标
DECLARE authors_cur CURSOR FOR
SELECT au_fname,au_lname
FROM authors
WHERE state='CA'
--打开游标
OPEN authors_cur

DECLARE @cur_rowcount INT
SELECT @cur_rowcount=@@CURSOR_ROWS
GO

--关闭游标
CLOSE authors_cur

--事务
BEGIN TRANSACTION exampletrans
USE pubs
GO
--执行一次更新操作
UPDATE titleauthor
SET royaltyper=35
FROM titleauthor,titles
WHERE royaltyper=25 AND titleauthor.title_id=titles.title_id AND title='THE Gourmet Microwave'
GO
--设置保存点
SAVE TRANSACTION exampletrans
--第二次更新操作
UPDATE titles
SET price=price*1.2
WHERE title='THE Gourmet Microwave'
GO
--回滚到保存点
ROLLBACK TRANSACTION exampletrans
PRINT '程序继续执行'
COMMIT TRANSACTION