课程设计sql2000

很好1 create proc query_book as select * from book go exec query_book 2 Create proc insert_book @param1 char(10),@param2 varchar(20),@param3 money,@param4 money output with encryption ---------加密 as insert book(编号,书名,价格) values(@param1,@param2,@param3) select @param4=sum(价格) from book go 根据提供的文件内容，我们可以归纳出以下几个关键的知识点： ### 一、存储过程的创建与执行 #### 1. 查询所有图书信息的存储过程 - **语法**：`CREATE PROCEDURE 存储过程名称 AS SELECT 语句 FROM 表名` - **示例**： ```sql CREATE PROCEDURE query_book AS SELECT * FROM book; GO EXEC query_book; ``` - **解析**：这段代码定义了一个名为 `query_book` 的存储过程，用于查询 `book` 表中的所有记录。使用 `EXEC` 调用该存储过程。 #### 2. 插入图书信息并计算总价格的存储过程 - **语法**：`CREATE PROCEDURE 存储过程名称 @参数1 数据类型, @参数2 数据类型, @参数3 数据类型, @参数4 数据类型 OUTPUT WITH ENCRYPTION AS INSERT 语句 SELECT 语句` - **示例**： ```sql CREATE PROCEDURE insert_book @param1 char(10), @param2 varchar(20), @param3 money, @param4 money OUTPUT WITH ENCRYPTION AS INSERT INTO book (编号, 书名, 价格) VALUES (@param1, @param2, @param3); SELECT @param4 = SUM(价格) FROM book; GO DECLARE @total_price money; EXEC insert_book '003', 'Delphiؼָ', $100, @total_price; PRINT '总价为' + CONVERT(varchar, @total_price); GO ``` - **解析**：此存储过程 `insert_book` 接收四个参数，其中前三个用于插入数据到 `book` 表，第四个参数用于输出插入后表中所有记录的总价格。 - **注意**：此示例中的 `WITH ENCRYPTION` 选项用于加密存储过程的定义，防止其他用户查看其具体内容。 ### 二、临时表的创建与使用 #### 1. 创建并填充临时表 - **语法**：`CREATE PROCEDURE 存储过程名称 AS SELECT ... INTO #临时表名 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段 IF @@ERROR = 0 PRINT 'Good' ELSE PRINT 'Fail'` - **示例**： ```sql CREATE PROCEDURE temp_sale AS SELECT a.产品, a.单价, b.数量, b.数量 AS 总价 INTO #temptable FROM Products a INNER JOIN Sales b ON a.产品 = b.产品 IF @@ERROR = 0 PRINT 'Good' ELSE PRINT 'Fail'; GO ``` - **解析**：这里创建了一个名为 `temp_sale` 的存储过程，通过连接 `Products` 和 `Sales` 表，将结果保存到一个名为 `#temptable` 的临时表中，并根据错误码输出提示信息。 ### 三、触发器的创建与使用 #### 1. 更新库存的触发器 - **语法**：`CREATE TRIGGER 触发器名称 ON 表 FOR INSERT AS UPDATE 表 SET UnitsInStock = UnitsInStock - 新增数量 FROM 表 AS P INNER JOIN Inserted AS I ON P.产品ID = I.产品ID` - **示例**： ```sql USE Northwind; GO CREATE TRIGGER OrdDet_Insert ON [OrderDetails] FOR INSERT AS UPDATE P SET UnitsInStock = P.UnitsInStock - I.Quantity FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID; ``` - **解析**：当向 `OrderDetails` 表中插入新记录时，触发器 `OrdDet_Insert` 会自动更新 `Products` 表中对应产品的库存量。 #### 2. 防止删除类别的触发器 - **语法**：`CREATE TRIGGER 触发器名称 ON 表 FOR DELETE AS UPDATE 表 SET Discontinued = 1 FROM 表 AS P INNER JOIN deleted AS d ON P.字段 = d.字段` - **示例**： ```sql USE Northwind; GO CREATE TRIGGER Category_Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID; ``` - **解析**：当从 `Categories` 表中删除类别时，触发器 `Category_Delete` 会自动将 `Products` 表中对应类别下的产品设置为已下架状态。 #### 3. 防止修改员工ID的触发器 - **语法**：`CREATE TRIGGER 触发器名称 ON 表 FOR UPDATE AS IF UPDATE(字段名) BEGIN RAISERROR('错误消息', 严重级别, 状态) ROLLBACK TRANSACTION END` - **示例**： ```sql USE Northwind; GO CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS IF UPDATE(EmployeeID) BEGIN RAISERROR('Transaction cannot be processed. EmployeeID number cannot be modified.', 10, 1) ROLLBACK TRANSACTION END; ``` - **解析**：当尝试更新 `Employees` 表中的 `EmployeeID` 字段时，触发器 `Employee_Update` 将引发错误并回滚事务。 ### 四、数据处理与查询优化 #### 1. 查询特定学生信息 - **语法**：`DECLARE @变量 数据类型 SET @变量 = 值 SELECT 语句 WHERE 条件 = @变量` - **示例**： ```sql USE teachdb; DECLARE @name nvarchar(10); SET @name = '张三'; SELECT * FROM student WHERE s_name = @name; DECLARE @number char(8); SELECT @number = s_no FROM student WHERE s_name = @name; SELECT * FROM student WHERE (s_no = CONVERT(char, CONVERT(int, @number) + 1)) OR (s_no = CONVERT(char, CONVERT(int, @number) - 1)); GO ``` - **解析**：首先声明变量 `@name` 并赋值为 `'张三'`，然后查询 `student` 表中姓名为 `张三` 的学生信息；接着查询学生编号比当前学生编号大或小1的学生信息。 #### 2. 处理平均分及排名 - **语法**：`DECLARE @变量 数据类型 SELECT @变量 = AVG(成绩) FROM 表名 PRINT '平均分:' + CONVERT(varchar, @变量) IF (@变量 > 70) BEGIN PRINT '优秀!' SELECT TOP 3 * FROM 表名 ORDER BY 成绩 DESC END ELSE BEGIN PRINT '一般!' SELECT TOP 3 * FROM 表名 ORDER BY 成绩 ASC END` - **示例**： ```sql DECLARE @myfloat float; SELECT @myfloat = AVG(score) FROM bishichengji; PRINT '平均分为:' + CONVERT(varchar(8), @myfloat); IF (@myfloat > 70) BEGIN PRINT '优秀!'; SELECT TOP 3 * FROM bishichengji ORDER BY score DESC; END ELSE BEGIN PRINT '一般!'; SELECT TOP 3 * FROM bishichengji ORDER BY score ASC; END; ``` - **解析**：首先计算 `bishichengji` 表中所有学生的平均分数，并将其赋值给变量 `@myfloat`。如果平均分数大于70，则输出 “优秀!”，并按照成绩降序显示成绩最高的前三名学生；否则输出 “一般!”，并按照成绩升序显示成绩最低的前三名学生。 #### 3. 自动调整不及格学生的分数 - **语法**：`DECLARE @变量 数据类型 WHILE (条件) BEGIN SELECT @变量 = COUNT(*) FROM 表名 WHERE 条件 IF (@变量 > 0) UPDATE 表名 SET 成绩 = 成绩 + 增加值 WHERE 成绩 <= 最大值 ELSE BREAK END PRINT '已调整成绩' SELECT * FROM 表名` - **示例**： ```sql DECLARE @n int; WHILE (1 = 1) BEGIN SELECT @n = COUNT(*) FROM bishichengji WHERE score < 60; IF (@n > 0) UPDATE bishichengji SET score = score + 2 WHERE score <= 98; ELSE BREAK; END PRINT '已调整不及格学生的分数'; SELECT * FROM bishichengji; ``` - **解析**：循环遍历 `bishichengji` 表中不及格的成绩，每次循环都将成绩小于60分的学生的成绩提高2分（但不超过98分），直到没有不及格的学生为止。 #### 4. 成绩等级划分 - **语法**：`PRINT 'ABCDE等级示例:' SELECT 字段, 等级 = CASE WHEN 成绩 < 60 THEN 'E' WHEN 成绩 BETWEEN 60 AND 69 THEN 'D' WHEN 成绩 BETWEEN 70 AND 79 THEN 'C' WHEN 成绩 BETWEEN 80 AND 89 THEN 'B' ELSE 'A' END FROM 表名` - **示例**： ```sql PRINT 'ABCDE等级示例:'; SELECT s_name, 等级 = CASE WHEN score < 60 THEN 'E' WHEN score BETWEEN 60 AND 69 THEN 'D' WHEN score BETWEEN 70 AND 79 THEN 'C' WHEN score BETWEEN 80 AND 89 THEN 'B' ELSE 'A' END FROM bishichengji; ``` - **解析**：此示例为每个学生分配一个基于其成绩的等级。成绩低于60分的学生被标记为 “E” 等级，成绩在60至69分之间的学生被标记为 “D”，以此类推。 以上是关于SQL Server 2000中存储过程、触发器以及数据处理的一些基础知识及其具体应用示例。这些技术可以帮助开发者更高效地管理和操作数据库。