数据库后台编程技术：填补代码

最新推荐文章于 2023-05-07 15:03:28 发布

Lw中

最新推荐文章于 2023-05-07 15:03:28 发布

阅读量1.1k

点赞数 3

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_43894879/article/details/105612083

版权

数据库专栏收录该内容

18 篇文章 0 订阅

订阅专栏

First
设在SQL Server 2008某数据库中有商品表和销售表，表的定义如下：
CREATE TABLE 商品表(
商品号 char(10) PRIMARY KEY,
商品名 char(20) NOT NULL,
销售总量 int DEFAULT 0)

CREATE TABLE 销售表(
商品号 char(10),
销售时间 datetime,
销售数量 int NOT NULL,
PRIMARY KEY(商品号,销售时间),
FOREIGN KEY(商品号) REFERENCES 商品表(商品号))

现要创建一个具有如下功能的触发器：每当在销售表中插入一条销售记录时，修改商品表中对应商品的销售总量，假设一次只插入一条销售记录。请补全下列代码。
CREATE TRIGGER tri_insert on______FOR______
AS______商品表______销售总量=______+
(SELECT 销售数量 FROM____________)
WHERE 商品号 IN (SELECT 商品号 FROM Inserted)

解题思路：
使用FOR或AFTER选顶定义的触发器为后触发器，即只有在引发触发器执行的语句中的操作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。而使用INSTEAD OF选顶定义的触发器为前触发器。这种模式的触发器中，指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代引发语句的操作。
在触发器语句中可以使用两个特殊的临时工作表：INSERTED表和DELETED表。这两个表是在用户自行数据的更改操作时，SQL Server自动创建和管理的。其中INSERTED表是用于存储INSERT和UPDATE语句所影响的行的副本。而DELETED表用于存储DELETE和UPDATED语句所影响的行的副本。
建立触发器语法是：

CreateTRIGGER trigger_name
ON { table | view }
{
	{ { FOR |AFTER | INSTEAD OF } { [ Insert ] [ , ] [ Update ] }
		AS
		[{ IF Update (column )
			[{ AND | or } Update ( column )]
				[…n ]
		| IF ( COLUMNS_UpdateD ( ) { bitwise_operator } updated_bitmask)
			{ comparison_operator } column_bitmask […n ]
		}]
		sql_statement […n ]
	}
}

从触发器的语法中可知，【1】处应该填写的是执行触发器的表，【2】处应该填写指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。从题目"CPEATE TRIGGER tri_insert on【1】FOR【2】“知，本题创建的触发器是是后触发器。该触发器的功能是"每当在销售表中插入一条销售记录时，修改商品表中对应商品的销售总量”。本题是在销售表上建立后触发器，需要执行的是插入语句。故可推导出【1】处应填写：销售表，【2】处应填写：INSERT。
根据触发器语法规则知，AS是触发器要执行的操作。本题要求的是更新商品表中的销售总量。更新语句釆用UPDATE关键字，其语法格式为：UPDATE 表名 SET语句。因此【3】处应填写：UPDATE，【4】处应填写：SET，【5】处应填写：商品表.销售总量。
本题中，是使用INSERT和UPDATED关键字对商品表进行操作，因此影响的是INSERTED表的内容。故【6】处应填写：Inserted。

Second
设在SQL Server 2008某数据库中有商品表和销售表，表的定义如下：
CREATE TABLE 商品表(
商品号 char(10) PRIMARY KEY,
商品名 char(20) NOT NULL,
单价 int DEFAULT 0)

CREATE TABLE 销售表(
商品号 char(10),
销售时间 datetime,
销售数量 int NOT NULL,
PRIMARY KEY(商品号,销售时间),
FOREIGN KEY(商品号) REFERENCES 商品表(商品号))

现要创建一个具有如下功能的用户自定义标量函数：根据指定的商品号，返回该商品的销售总金额。请补全下列代码（请不要为表起别名）。
CREATE FUNCTION dbo.f_TotalQty(@GoodID_____)
_____ int
AS
_____
RETURN(SELECT _________ (单价 * 销售数量) FROM 商品表
JOIN ______
ON _______
WHERE 商品号 = _______)
______

解题思路：
标量函数的语法格式：

CREATE FUNCTION [函数名](@参数名 参数类型）
RETURNS [返回值类型]
AS
BEGIN
[sql语句]
End

对比语法格式可以发现，【1】处应该填写参数类型，由表的定义可以看出，商品号（GoodID)是字符型，因此此处应该填写：char，【2】处应该填写：RETURNS,【3】处应该填写：BEGIN,【8】处应该填写：END。
题目要求"根据指定的商品号，返回该商品的销售总金额"。商品金额=单价*销售数量。求指定商品的销售总金额，必然会用到SUM函数，因此可以推断出【4】处应填写：SUM。单价是在商品表中，而销售数量在销售表中，因此必须釆用两个表进行连接查询，在查询中要保证两个表中的商品号相同。两表进行连接运算包括内连接、左连接、右连接，后二者统称为外连接。内连接（INNER JOIN)主表和从表都存在的记录才会查出来，外连接（OUTER JOIN)如果主表中存在的记录，从表中不存在则用NULL替代，由此可见内连接符合题意。
内连接SQL语句如下：
SELECT * FROM a
(INNER，可省略）JOIN b
ON a.aID =b.bID
等同于以下SQL句：
SELECT *
FROM a, b
WHERE a.aID = b.bID
从语法可以推测出【5】处应填写：销售表，【6】处应填写：商品表.商品号=销售表.商品号或销售表.商品号=商品表.商品号。由于题目是指定输入商品号，因此【7】处的商品号应该是一个参数，即@GoodID。

Third
设在SQL Server 2008某数据库中有汽车表和销售表，表的定义如下：
CREATE TABLE 汽车表(
汽车型号 char(10) PRIMARY KEY,
汽车名称 char(20),
颜色 char(10),
价格 int)

CREATE TABLE 销售表(
汽车型号 char(10),
销售时间 datetime,
销售数量 int,
PRIMARY KEY(汽车型号,销售时间),
FOREIGN KEY(汽车型号) REFERENCES 汽车表(汽车型号))

现要创建一个具有如下功能的存储过程：查询指定日期范围内汽车的销售情况，列出汽车型号和销售总数量，包括没有被销售过的汽车。请补全下列代码。
CREATE ______ p1
@start_date date, @end_date date
AS
SELECT ______ .汽车型号, _______
FROM 汽车表 ______ 销售表 ON 汽车表.汽车型号 = 销售表.汽车型号
WHERE 销售时间 BETWEEN _______ AND _______
GROUP BY 汽车表.汽车型号

解题思路：
存储过程(Stored Procedure)是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
建立存储过程的语句：

CREATE { PROCEDURE | PR0C } [schema_name]存储过程名
[{@ [存储过程参数] [参数类型] data_type}
[=默认值] [OUT|OUTPUT]
]  [,…n] [WITH RECOMPILE]
AS
BEGIN
SQL语句
END

    【1】CREATE是用来建立存储过程的，根据建立存储过程的语法可知此处应该填写：PROCEDURE（或者PROC）。
     【2】BEGIN和END之间应该填写的是处理任务的SQL语句。题目要求查询指定日期范围内汽车的销售情况，列出汽车型号和销售总数量。从FROM语句知，查询的是汽车的内容，故此处应该填写：汽车表。
     【3】题目要求查询的销售总量，因此应该对销售数量求和。SQL语句中求和函数使用的是SUM()函数。故此处应该填写：SUM(销售数量）。
     【4】汽车表中有汽车的基本信息，却没有销售信息；而销售表中有汽车销售信息，没有汽车信息，如果想既知道汽车信息，又知道销售信息，必须将两张表连接进行查询。根据【4】后面的ON关键字提醒，本处应该使用JOIN……ON语法。
连接查询包括内连接和外连接查询。使用内连接时，仅当匹配的内容两个表中都存在时才显示出来。显然内连接不符合题意。因此必须使用外连接。外连接又分为三种类型：
左外连接（LEFT [OUTER] JOIN)：结果表中保留连接条件左边关系中的所有内容；
右外连接（RIGHT [OUTER] JOIN)：结果表中保留连接条件右边关系中的所有内容；
全外连接（FULL [OUTER] JOIN)：结果表中保留连接条件左、右边关系中的所有内容；
其中OUTER关键字可以省略。
题目要求查询包括没有销售和销售的所有的汽车，而汽车表在左边，销售表在右边，因此只有左外连接符合要求。故此处应该填写LEFT JOIN或LEFT OUTER JOIN。
     【5】题目要求查询指定日期之间范围内的销售情况，根据"WHERE 销售时间BETWEEN【5】AND【6】"知【5】和【6】的作用是确定查询的时间范围。在根据存储过程的参数@start_date、@end_date名可以推断出@start_date是查询的开始时间，@end_date是查询的结束时间。故此处应填写@start_date。
     【6】根据【5】空的解析可知【6】处应填写结束的时间，故此处应填写@end_date。

Fourth
设在SQL Server 2008某数据库中有汽车表和销售表，表的定义如下：
CREATE TABLE 汽车表(
汽车型号 char(10) PRIMARY KEY,
汽车名称 char(20),
颜色 char(10),
销售总数量 int default 0)

CREATE TABLE 销售表(
汽车型号 char(10),
销售时间 datetime,
销售数量 int,
PRIMARY KEY(汽车型号,销售时间),
FOREIGN KEY(汽车型号) REFERENCES 汽车表(汽车型号))

现要创建一个具有如下功能的后触发型触发器：每当在销售表中插入数据时，自动更新汽车表中相应型号的汽车的销售总数量（假定一次只插入一行数据）。请补全下列代码。
CREATE TRIGGER tri_sales
ON _____ AFTER _______
AS
DECLARE @Sales_Amount int
SET @Sales_Amount= (
SELECT 销售数量 FROM _______ )
UPDATE 汽车表 SET 销售总数量 = 销售总数量 + _______
WHERE 汽车型号 IN ( ______ )

解题思路
本题考察的是触发器的相关语法
创建触发器的语法如下：

  CREATE TRIGGER [schema_name.] trigger_name
  ON  {table | view}
  {FOR |AFTER |INSTEAD OF}
  { [INSERT ] [,] [UPDATE] [,] [DELETE]}
  AS  {sql_statement]

    其中，[ ]括起的部分是可选内容，{ }括起的部分是必选内容，由|间隔开的部分是任选其一的内容。

ltable，填写与本触发器相关的数据表的表名。
lview，填写与本触发器相关的视图的视图名。
lfor和after都是指后触发型触发器，操作执行完毕后才执行该触发器。for和after可以认为没有区别。
linstead of是前触发型触发器。
linsert指插入操作。
lupdate指更新操作。
ldelete指删除操作。
las后面的sql语句是本触发器将要执行的操作。
由题可知，创建了一个名为tri_sales的触发器，且题目指明是后触发型触发器（由此判定需要用for或after）。题目要求在销售表（由此判定填写销售表，位置【1】）中插入数据（由此判定填写INSERT，位置【2】）时更新数据，更新的数据是汽车表中相应型号的汽车的销售总数量。
更新操作是将汽车表中的销售数量更新，更新为原本的销售数量+本次销售表中新增的销售数量。此时涉及一个临时表inserted，该表用于存放对表中数据行的修改信息，是触发器执行时自动创建的，放在内存中，是临时表。当触发器工作完成，立即被删除。inserted是只读表，不能向它写入内容。
inserted表用来存储INSERT和UPDATE语句所影响的行的副本。意思就是在inserted表中临时保存了被插入或被更新后的记录行。在执行 INSERT 或UPDATE 语句时，新加行被同时添加到inserted表和触发器表中。因此，可以从inserted表检查插入的数据是否满足需求，如不满足则回滚撤消操作。
所以，本次是插入数据所触发的触发器，所涉及的数据会被写入临时表inserted，故本次插入的销售数量就是临时表inserted中的销售数量，所以位置【3】应填写INSERTED。位置【3】所在语句就是将该销售数量写入变量@Sales_Amount。
位置【4】和位置【5】所在语句是update（更新），根据题意可知，需要将新增的销售数量（已存入@Sales_Amount中的），与原有的销售总数量（汽车表.销售总数量）相加，用得到的新值更新替换原本的销售总数量（汽车表.销售总数量），所以位置**【4】应填@Sales_Amount**。
由于位置【5】需要提供本次销售的汽车型号，即inserted.汽车型号，同时通过sql的学习可知本处只能写子查询，所以位置【5】必须填写select语句，使用select语句查询inserted表中的汽车型号，故本处应填写SELECT 汽车型号 FROM INSERTED。

Fifth
设在SQL Server 2008某数据库中有汽车表和销售表，表的定义如下：
CREATE TABLE 汽车表(
汽车型号 char(10) PRIMARY KEY,
汽车名称 char(20),
颜色 varchar(10),
价格 int)

CREATE TABLE 销售表(
汽车型号 char(10),
销售时间 datetime,
销售数量 int,
PRIMARY KEY(汽车型号,销售时间),
FOREIGN KEY(汽车型号) REFERENCES 汽车表(汽车型号))

现要创建一个具有如下功能的存储过程：查询指定型号的汽车的销售总数量，并且销售总数量用输出参数返回。请补全下列代码。
CREATE _____ P1
@model varchar(10), @total int _____
AS
_______ @total = (
SELECT _______
FROM 销售表 s JOIN 汽车表 c ON ______
WHERE 汽车型号 = _____ )

解题思路
本题考察的是存储过程以及select查询的语法。
创建存储过程的语法如下：

CREATE  { PROC | PORCEDURE }  [schema_name.] procedure_name
    [{@parameter [type_schema_name.] data_type}
      [=default][OUT|OUTPUT]
    ][,…n][WITH RECOMPILE]
AS {sql_statement  }

其中，[ ]括起的部分是可选内容，{ }括起的部分是必选内容，由|间隔开的部分是任选其一的内容。
lprocedure_name是存储过程的名字。
l@parameter是存储过程的参数，即从外界接收到的要进行处理或计算的数据，最多可接收2100个参数。
ldata_type是该参数的数据类型。
lOUTPUT指该参数是输出参数，该参数的值将被返回给过程的调用方。

由于是创建存储过程，所以位置【1】应填PROC或PROCEDURE。
由于需要查询指定型号的汽车的销售总数量，那么参数就需要包含汽车型号（根据数据类型可以确认用@model存储）和销售总数量（根据数据类型可以确认用@total存储）。由于销售总数量需要用输出参数返回，所以位置【2】应填Output。
as之后的部分是该存储过程将要执行的SQL语句，其功能是查询出@model中存储的型号的汽车所对应的总销量，并将其存储到@total变量中，即为@total变量赋值，其语法是
set 变量名={select查询}
所以，位置【3】应填SET。
由于位置【4】应填写查询最终需要显示的内容（总的销售数量），所以应填SUM(销售数量)。
位置【5】需要填写的是内连接(join)的条件，所以应填
s.汽车型号=c.汽车型号或c.汽车型号=s.汽车型号
位置【6】需要填写查询条件。故此处应填@model，即只查询汽车型号是@model中的那个型号的记录。

Sixth
设在SQL Server某数据库中有房屋出租表，表的定义如下：
CREATE TABLE 出租表(
房屋号 char(10),
出租日期 datetime,
租期 int not null,–月数
到期日期 datetime,
PRIMARY KEY(房屋号,出租日期));

现要创建一个具有如下功能的触发器：每当在出租表中插入一行数据（房屋号，出租日期，租期）时，自动计算出本行数据的"到期日期"。请补全下列代码。
CREATE TRIGGER tri ON _____ FOR insert
AS
_____ 出租表 SET 到期日期 = dateadd( ______ ,租期,出租日期)
WHERE 房屋号 = (SELECT 房屋号 FROM _____ )
and ______

解题思路
创建触发器的语法如下：

 CREATE TRIGGER [schema_name.] trigger_name
      ON  {table | view}
      {FOR |AFTER |INSTEAD OF}
      { [INSERT ] [,] [UPDATE] [,] [DELETE]}
      AS  {sql_statement]

其中，[ ]括起的部分是可选内容，{ }括起的部分是必选内容，由|间隔开的部分是任选其一的内容。
table，填写与本触发器相关的数据表的表名。
view，填写与本触发器相关的视图的视图名。
for和after都是指后触发型触发器，操作执行完毕后才执行该触发器。for和after可以认为没有区别。
instead of是前触发型触发器。
insert指插入操作。
update指更新操作。
delete指删除操作。
as后面的sql语句是本触发器将要执行的操作。

由题可知，要求在出租表（由此判定填写出租表，位置1）中插入数据时更新数据（由此判定填写update，位置2），更新的数据是出租表中相应房屋的到期日期（到期日期是出租日期+租期（即月份），所以此处应填写month，位置3）。
更新操作是将出租表中的到期日期更新，更新为出租日期+租期（月份）。此时涉及一个临时表inserted，该表用于存放对表中数据行的修改信息，是触发器执行时自动创建的，放在内存中，是临时表。当触发器工作完成，立即被删除。inserted是只读表，不能向它写入内容。
inserted表用来存储INSERT和UPDATE语句所影响的行的副本。意思就是在inserted表中临时保存了被插入或被更新后的记录行。在执行INSERT或UPDATE语句时，新加行被同时添加到inserted表和触发器表中。因此，可以从inserted表检查插入的数据是否满足需求，如不满足则回滚撤消操作。
所以，本次是插入数据所触发的触发器，所涉及的数据会被写入临时表inserted，故本次插入的出租日期就是临时表inserted中的出租日期，所以位置4应填写inserted。
由于位置5需要提供本次更新的出租日期，即inserted.出租日期，同时通过sql的学习可知本处只能使用子查询，所以位置5必须填写select语句，使用select语句查询inserted表中的出租日期，故本处应填写：出租日期 = (select 出租日期 form inserted)。
SQL语言中，大小写不敏感，使用大写或小写字母均可。

Seventh
设在SQL Server某数据库中有房屋及租赁表，表的定义如下:
CREATE TABLE 房屋表（
房屋号 char(10) PRIMARY KEY,
房屋地址 char(20) not null:
面积 int,
月租金 int)

CREATE TABLE 租赁表（
房屋号 char(10),
租赁日期 datetime,
租赁月数 int not null,
本次总租金 int,
PRIMARY KEY(房屋号,租赁日期),
FOREIGN KEY(房屋号) REFERENCES房屋表(房屋号)）

现要创建一个具有如下功能的触发器：每当在租赁表中插入一行数据（房屋号，租赁日期，租赁月数）时，自动计算出该房屋的本次总租金。请补全下列代码。
CREATE TRIGGER tri ON ______ FOR ______
AS
______ @x int --声明保存月租金的变量
SET @x = (SELECT 月租金 FROM 房屋表 WHERE 房屋号=(SELECT 房屋号 FROM ______))
UPDATE 租赁表 SET 本次总租金=租赁月数* ______
FROM 租赁表 as a JOIN ______ as b
on a.房屋号=b.房屋号
and a.租赁日期=b.租赁日期

解题思路
创建后触发型触发器的命令语法是：

CREATE  TRIGGER  [触发器名称]
ON  [与触发器相关联的表或视图名]
FOR  [引发触发器执行的操作]
AS  [触发器将要执行的SQL语句序列]

其中引发触发器执行的操作包括insert（插入）、update（更新）和delete（删除）。
结合题意可知问题1应填租赁表，问题2应填insert，SQL语句序列应计算本次总租金。
问题3需要声明int型变量，变量名为@x，需要填写declare。
问题4需要检索出插入的房屋号对应的月租金，并将月租金赋值给变量@x，所以表名为inserted（因为使用的是插入的数据），所以应填inserted。
问题5需要计算总租金，总租金是租赁月数*月租金，而月租金已经被存储到@x中，所以应填@x。
问题6等同问题4，应填inserted。

Eighth
设在SQL Server某数据库中有房屋及租赁表，表的定义如下：
CREATE TABLE 房屋表(
房屋号 char(10) PRIMARY KEY,
房屋地址 char(20) not null,
面积 int,
月租金 int)
CREATE TABLE 租赁表(
房屋号 char(10),
租赁日期 datetime,
租赁月数 int not null,
PRIMARY KEY(房屋号,租赁日期),
FOREIGN KEY(房屋号) REFERENCES 房屋表(房屋号))

现要创建一个具有如下功能的存储过程：根据输入的房屋号，计算该房屋的历史租金总和并用输出参数返回。请补全下列代码。
CREATE ______ P1
@room char(10), ----输入参数：房屋号
@total int ______ ----输出参数：租金总和
AS
______ @total=(
SELECT ______ (月租金 * 租赁月数)
FROM 房屋表 a JOIN 租赁表 b ON a.房屋号=b.房屋号
WHERE ______ )

解题思路
创建存储过程，故①处应填procedure。
②是输出参数，故应填output。
③此处是为@total赋值，但课本上各例题中此处均未书写任何内容，故此处是考察赋值的另一种写法，set 变量名=表达式，故应填set。
④此处在求和，故应填sum。
⑤此处是条件，需要在各表中查询房屋号是@room的房屋信息，故应填a.房屋号=@room,b.房屋号=@room。（注意使用英文标点）

Lw中

关注

3
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库后台编程技术：填补代码

First设在SQL Server 2008某数据库中有商品表和销售表，表的定义如下：CREATE TABLE 商品表(商品号 char(10) PRIMARY KEY,商品名 char(20) NOT NULL,销售总量 int DEFAULT 0)CREATE TABLE 销售表(商品号 char(10),销售时间 datetime...
复制链接

扫一扫