数据库的查找和更新

JY数据库的查找和更新

USE JY
GO

1、JY数据库的查找

• 声明变量保存书名和作者

DECLARE @bookname nvarchar(50), @bookauthor char(10), @bookpublisher nvarchar(50)

• 对变量赋值

SET @bookname = '蛤蟆功'

• 查询

IF EXISTS(SELECT * FROM book WHERE book_name = @bookname)
BEGIN
    SELECT @bookpublisher = book_publisher FROM book WHERE book_name = @bookname
    PRINT @bookpublisher
END
ELSE
    PRINT '没有相关图书！'

SELECT @bookauthor = book_author FROM book WHERE book_name = @bookname
PRINT @bookauthor
GO

• 利用聚合与判断查询

SELECT min(interview_times) FROM book
GO
WHILE(SELECT min(interview_times) FROM book) < 35
BEGIN
    UPDATE book SET interview_times = interview_times + 5

    IF(SELECT min(interview_times) FROM book) > 35
        BREAK
    ELSE
        CONTINUE
END

SELECT min(interview_times) FROM book
GO

2、JY数据的更新与删除

• 更新book表的数据

UPDATE book
SET interview_times = '0'  --更新的属性
FROM book
WHERE book_publisher = '电子工业出版社'  --更新满足的条件
GO

SELECT * FROM book  --查询是否更新成功
GO

• 删除reader表的数据

DELETE FROM reader
WHERE reader_id = 'r003'  --删除的条件
GO

SELECT * FROM reader
GO