数据库原理实验

博主：酱油瓶
发布时间：2022 年 05 月 21 日
1791 次浏览
暂无评论
16406字数
分类：学习软件工程

> 这是本人数据原理的实验，放到博客归档，可供参考

# 实验一 单表查询

> 日期：2022.5.1

## **一、实验目的**

1. 掌握指定列或全部列查询
2. 掌握按条件查询
3. 掌握对查询结果排序
4. 掌握使用聚集函数的查询
5. 掌握分组统计查询

## **二、实验内容**

### 1. 指定列或全部列查询

1. 查询商品表（Product）中所有商品的信息

查询语句：

```sql
   SELECT *
   FROM dbo.Product
   ```

查询结果：

![image-20220502211451323](https://lsky-picture.stdcdn.com/uploads/2022/05/3923b28a054c792a40fc3f48f3e72b09.png)
2. 检索客户表（Customer）中前5位客户的公司名称、联系人姓名和地址。

查询语句：

```sql
   SELECT top 5 CompanyName,ContactName,Address
   FROM dbo.Customer
   ```

查询结果：

![image-20220502211814783](https://lsky-picture.stdcdn.com/uploads/2022/05/c4fb77df9b707f12ecd7daae0ef03bb0.png)
3. 从员工表（Employee）中查询所有员工的部门信息。

查询语句：

```sql
   SELECT DISTINCT DepartmentID
   FROM dbo.Employee
   ```

查询结果：

![image-20220502212308838](https://lsky-picture.stdcdn.com/uploads/2022/05/c3b1962383bf24c0847d77739e09be54.png)
4. 查询将员工表中（Employee）所有员工的工资提高10%后的信息，输出字段为：员工姓名，原工资，提高后工资。

查询语句：

```sql
   SELECT EmployeeName,Salary,Salary*1.1 as 提高后工资
   FROM dbo.Employee
   ```

查询结果：

![image-20220502212643232](https://lsky-picture.stdcdn.com/uploads/2022/05/18e3c92606ae92796c4700e7c5f54755.png)
5. 统计员工表（Employee）中的职工数。

查询语句：

```sql
   SELECT COUNT(*) as 职工数
   FROM dbo.Employee
   ```

查询结果：

![image-20220502213439932](https://lsky-picture.stdcdn.com/uploads/2022/05/9d101556efa63e7852a15445efe8ed92.png)

### 2. 按条件查询

1. 检索员工表所有姓李和姓章的员工信息。

查询语句：

```sql
   SELECT *
   FROM dbo.Employee
   WHERE EmployeeName like '李%' OR EmployeeName like '章%'
   ```

查询结果：

![image-20220502220807371](https://lsky-picture.stdcdn.com/uploads/2022/05/35b73d705e8bb586028152fe0c78b98b.png)
2. 检索员工表姓李的名字只有一个汉字的员工信息。

查询语句：

```sql
   SELECT *
   FROM dbo.Employee
   WHERE EmployeeName like '李_'
   ```

查询结果：

![image-20220502220846475](https://lsky-picture.stdcdn.com/uploads/2022/05/17d5c1214cb7b81d03f61b0a85d955b1.png)
3. 查询员工表中工资在3400以下的女性员工姓名和工资信息。

查询语句：

```sql
   SELECT EmployeeName,Salary
   FROM dbo.Employee
   WHERE Sex = '女' AND Salary < 3400
   ```

查询结果：

![image-20220502221225040](https://lsky-picture.stdcdn.com/uploads/2022/05/15c1eed17df0fd0c25adb003025a3f0f.png)
4. 查询员工表中工资在5000到7000之间的员工信息。（Between）

查询语句：

```sql
   SELECT *
   FROM dbo.Employee
   WHERE Salary BETWEEN 5000 AND 7000
   ```

查询结果：

![image-20220502221724672](https://lsky-picture.stdcdn.com/uploads/2022/05/181f26d58dcd4bd0e72e533ce67b269a.png)
5. 检索销售订单表（Sell_Order）中，员工编号为1、5、7的员工接收订单的信息。（IN）

查询语句：

```sql
   SELECT *
   FROM dbo.Sell_Order
   WHERE EmployeeID IN (1,5,7)
   ```

查询结果：

![image-20220502221939133](https://lsky-picture.stdcdn.com/uploads/2022/05/56cf6eb910c34751d6f38a8354cb416a.png)
6. 检索部门表（Department）中主管位置不为空的部门信息。

查询语句：

```sql
   SELECT *
   FROM dbo.Department
   WHERE Manager IS NOT NULL
   ```

查询结果：

![image-20220502222112059](https://lsky-picture.stdcdn.com/uploads/2022/05/cdc89fbe384f63eada5eb9627e0f3b6c.png)

### 3. 分组统计查询

1. 查询员工表中男女员工的平均工资。输出字段性别，平均工资。

查询语句：

```sql
   SELECT Sex as '性别', AVG(Salary) as '平均工资'
   FROM dbo.Employee
   GROUP BY Sex
   ```

查询结果：

![image-20220502222439861](https://lsky-picture.stdcdn.com/uploads/2022/05/c9a471e29b342cf9af3afbffaf6cfdd1.png)
2. 查询销售订单表中各种商品的订货总数，输出字段为商品编号，订货总数。

查询语句：

```sql
   SELECT ProductID,SUM(SellOrderNumber) as '订货总数'
   FROM dbo.Sell_Order
   GROUP BY ProductID
   ```

查询结果：

![image-20220502222857767](https://lsky-picture.stdcdn.com/uploads/2022/05/b32eaf41d5f9262c70d6dd87423c3a5f.png)
3. 查询销售订单表中，订购两种以上商品的客户编号，订购商品种类。

查询语句：

```sql
   SELECT CustomerID, COUNT(ProductID) as '商品种类'
   FROM dbo.Sell_Order
   GROUP BY CustomerID
   HAVING COUNT(ProductID) > 1
   ```

查询结果：

![image-20220502223406757](https://lsky-picture.stdcdn.com/uploads/2022/05/4a26c08ac4540afbb1e3fa9316a95181.png)

### 4. 对查询结果排序

1. 查询员工表中男女员工的平均工资。输出字段性别，平均工资，按照平均工资升序排列。

查询语句：

```sql
   SELECT Sex as '性别', AVG(Salary) as '平均工资'
   FROM dbo.Employee
   GROUP BY Sex
   ORDER BY '平均工资' ASC
   ```

查询结果：

![image-20220502223724070](https://lsky-picture.stdcdn.com/uploads/2022/05/4e02278b105d72cbd11d64283d279f2c.png)
2. 查询销售订单表中各种商品的订货总数，输出字段为商品编号，订货总数，按照订货总数降序排列。

查询语句：

```sql
   SELECT ProductID,SUM(SellOrderNumber) as '订货总数'
   FROM dbo.Sell_Order
   GROUP BY ProductID
   ORDER BY '订货总数' DESC
   ```

查询结果：

![image-20220502223854054](https://lsky-picture.stdcdn.com/uploads/2022/05/a56cd8ca0c33be680eb1adf0ad29140c.png)

# 实验二 连接查询

> 日期：2022.5.9

## 一、实验目的

掌握涉及一个以上数据表的查询方法。

## 二、实验过程

1. 连接查询

1. 查询“国皓科技有限公司”的订单信息

查询语句：

```sql
      SELECT *
      FROM dbo.Customer,dbo.Sell_Order
      WHERE dbo.Customer.CustomerID = dbo.Sell_Order.CustomerID AND dbo.Customer.CompanyName = '国皓科技有限公司'
      ```

查询结果：

![image-20220504223550639](https://lsky-picture.stdcdn.com/uploads/2022/05/dbaddd0cf06a1c74950d6c40888e2d36.png)
   2. 查询“三川实业有限公司”订购的商品信息，输出字段为：CompanyName,ProductName,Price,SellOrderNumber。

查询语句：

```sql
      SELECT CompanyName,ProductName,Price,SellOrderNumber
      FROM Customer,Sell_Order,Product
      WHERE Customer.CustomerID = Sell_Order.CustomerID AND
      Sell_Order.ProductID = Product.ProductID AND
      Customer.CompanyName = '三川实业有限公司'
      ```

查询结果：

![image-20220509110427860](https://lsky-picture.stdcdn.com/uploads/2022/05/606c29430a139a003e27084e59bdaa13.png)
   3. 查询各公司各种商品的订单数量，输出字段为：CompanyName,ProductName,订单数量，按照订单数量升序排列。

查询语句：

```sql
      SELECT CompanyName,ProductName,COUNT(Sell_Order.SellOrderID) AS '订单数量'
      FROM Customer,Sell_Order,Product
      WHERE Customer.CustomerID = Sell_Order.CustomerID AND
      Sell_Order.ProductID = Product.ProductID
      GROUP BY Product.ProductName,Customer.CompanyName
      ORDER BY '订单数量' ASC
      ```

查询结果：

![image-20220509112332479](https://lsky-picture.stdcdn.com/uploads/2022/05/b375fbbccb141723e32dcb9f1c6efb5e.png)
   4. 查询各公司各种商品的订货总量、总金额。输出字段为：CompanyName,ProductName,订货总量，总金额。按照总金额降序排列。

查询语句：

```sql
      SElECT CompanyName,ProductName,SUM(SellOrderNumber) '订货总量',Price*SUM(SellOrderNumber) '总金额'
      FROM Customer C,Sell_Order S,Product P
      WHERE C.CustomerID = S.CustomerID AND
      P.ProductID = S.ProductID
      GROUP BY ProductName,CompanyName,Price
      ORDER BY '总金额' DESC
      ```

查询结果：

![image-20220509114947020](https://lsky-picture.stdcdn.com/uploads/2022/05/0add8502bf20ccbca5f44d319e0a44e5.png)
2. 自身连接

1. 查询和“章宏”同一部门的员工号，员工姓名。

查询语句：

```sql
      SELECT SECOND.EmployeeID,SECOND.EmployeeName
      FROM Employee FIRST,Employee SECOND
      WHERE FIRST.EmployeeName = '章宏' AND
      SECOND.DepartmentID = FIRST.DepartmentID
      ```

查询结果：

![image-20220509115710326](https://lsky-picture.stdcdn.com/uploads/2022/05/fe4134e2eba75229910aa53a45d7c29d.png)
   2. 查询既订购过3号产品，又订购过4号产品的客户号。

查询语句：

```sql
      SELECT C.CustomerID
      FROM Sell_Order S1, Sell_Order S2,Customer C
      WHERE C.CustomerID = S1.CustomerID AND
      S2.CustomerID = C.CustomerID AND
      S1.ProductID = '3'AND
      S2.ProductID = '4'
      GROUP BY C.CustomerID
      ```

查询结果：

![image-20220509120222127](https://lsky-picture.stdcdn.com/uploads/2022/05/84778aba9d47e91cfe9ec82027a64a7c.png)
3. 外连接

1. 查询没有接收到订单的员工姓名。

查询语句：

```sql
      SELECT EmployeeName
      FROM Employee LEFT OUTER JOIN Sell_Order
      ON Employee.EmployeeID = Sell_Order.EmployeeID
      WHERE SellOrderID IS NULL
      ```

查询结果：

![image-20220509120934893](https://lsky-picture.stdcdn.com/uploads/2022/05/dcd04bec0bc66e9610978c213d7c13b5.png)
   2. 查询没有任何订购信息的客户公司名。

查询语句：

```sql
      SELECT CompanyName
      FROM Customer LEFT OUTER JOIN Sell_Order
      ON Customer.CustomerID = Sell_Order.CustomerID
      WHERE SellOrderID IS NULL
      ```

查询结果：

![image-20220509121342736](https://lsky-picture.stdcdn.com/uploads/2022/05/e68a79ac3dcb033c8706701fb871949c.png)
   3. 查询没有被订购的商品名称。

查询语句：

```sql
      SELECT ProductName
      FROM Product LEFT OUTER JOIN Sell_Order
      ON Product.ProductID = Sell_Order.ProductID
      WHERE SellOrderID IS NULL
      ```

查询结果：

![image-20220509121700638](https://lsky-picture.stdcdn.com/uploads/2022/05/3b99ce2596827c1a493870a63bdc1894.png)

# 实验三 嵌套查询

> 日期： 2022.5.12

## 一、实验目的

掌握嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。

## 二、实验内容

1）**返回一个值的子查询**

1. 查询和“章宏”同一部门的员工号，员工姓名。

查询语句：

```sql
   SELECT EmployeeID,EmployeeName
   FROM Employee
   WHERE DepartmentID IN
   	(	SELECT DepartmentID
   		FROM Employee
   		WHERE EmployeeName = '章宏'
   	)
   ```

查询结果：

![image-20220512205819590](https://lsky-picture.stdcdn.com/uploads/2022/05/d421cc3c2472e2623c39684924a644b7.png)
2. 查询年龄最小的员工姓名、性别和工资。

查询语句：

```sql
   SELECT EmployeeName,Sex,Salary
   FROM Employee
   WHERE BirthDate IN
   	(	SELECT MAX(BirthDate)
   		FROM Employee
   	)
   ```

查询结果：

![image-20220512210144545](https://lsky-picture.stdcdn.com/uploads/2022/05/fad5accd01f30b396b036c08a15d2513.png)
3. 查询比平均工资高的员工姓名和工资。

查询语句：

```sql
   SELECT EmployeeName,Salary
   FROM Employee
   WHERE Salary >
   	(	SELECT AVG(Salary)
   		FROM Employee
   	)
   ```

查询结果：

![image-20220512211021990](https://lsky-picture.stdcdn.com/uploads/2022/05/200766d24e677c20674d292b3bc9b63e.png)

2）**返回一组值的子查询(嵌套查询和连接查询分别实现)**

1. 查询目前还没有接收到销售订单的员工姓名和工资信息。

嵌套查询语句：

```sql
   SELECT EmployeeName,Salary
   FROM Employee
   WHERE EmployeeID NOT IN 
   	(	SELECT EmployeeID 
   		FROM Sell_Order
   	)
   ```

查询结果：

![image-20220512211425064](https://lsky-picture.stdcdn.com/uploads/2022/05/bf33d23263e22ac9d8921a165288fa54.png)

连接查询语句：

```sql
   SELECT EmployeeName,Salary
   FROM Employee LEFT OUTER JOIN Sell_Order ON (Employee.EmployeeID = Sell_Order.EmployeeID)
   WHERE SellOrderID IS NULL
   ```

查询结果：

![image-20220512214952669](https://lsky-picture.stdcdn.com/uploads/2022/05/fa496d194b1e16c284b3a03f35ebe288.png)
2. 查询订购牛奶的客户名称和联系地址。

查询语句：

```sql
   SELECT CompanyName,Address
   FROM Customer
   WHERE CustomerID IN
   	(	SELECT CustomerID
   		FROM Sell_Order
   		WHERE ProductID IN
   			(	SELECT ProductID
   				FROM Product
   				WHERE ProductName = '牛奶'
   			)
   	)
   ```

查询结果：

![image-20220512212150902](https://lsky-picture.stdcdn.com/uploads/2022/05/3a0e7458347c3edb1c104551fdc4ab24.png)

连接查询语句：

```sql
   SELECT ContactName,Address
   FROM Customer,Product,Sell_Order
   WHERE Sell_Order.ProductID=Product.ProductID AND 
   Customer.CustomerID=Sell_Order.CustomerID AND 
   ProductName='牛奶'
   ```

查询结果：

![image-20220512215108448](https://lsky-picture.stdcdn.com/uploads/2022/05/695f0b4ee0d4ff25638315eb68935cb3.png)
3. 查询客户表中订购商品总数量超过400的客户信息，输出公司名称，联系电话。

查询语句：

```sql
   SELECT CompanyName,Phone
   FROM Customer
   WHERE CustomerID IN
   	(	SELECT CustomerID
   		FROM Sell_Order
   		GROUP BY CustomerID
   		HAVING SUM(SellOrderNumber)>400
   	)
   ```

查询结果：

![image-20220512212639021](https://lsky-picture.stdcdn.com/uploads/2022/05/bbb8dad6abb85aacdf783cf6958a8de6.png)

连接查询语句：

```sql
   SELECT CompanyName,Phone
   FROM Customer C JOIN Sell_Order S
   ON C.CustomerID=S.CustomerID 
   GROUP BY CompanyName,Phone
   HAVING SUM(SellOrderNumber)>400
   ```

查询结果：

![image-20220512215315211](https://lsky-picture.stdcdn.com/uploads/2022/05/6728f7d9c9c7111a0ae6940dbd60537a.png)

# 实验四 **数据更新和视图的创建**

> 日期：2022.5.20

## 一、实验目的

1.掌握数据更新语句

2.掌握视图创建的SQL语句

## 二、实验内容

1. 向客户表中插入一行：客户编号为34，公司名称为“人民低电压”，联系人为“南辉”。

插入语句：

```sql
   INSERT
   INTO Customer(CustomerID,CompanyName,ContactName)
   VALUES(34,'人民低电压','南辉')
   ```

运行结果：

![image-20220521152443239](https://lsky-picture.stdcdn.com/uploads/2022/05/d3853edb046204199d70eaf8f7fd4954.png)
2. 先定义一张表day_total，该表中包含两个属性列（销售日期 smalldatetime,销售订单数 int），在Sell_order中统计每天的销售订单数，并将结果插入到day_total表中。

语句：

```sql
   CREATE TABLE day_total(销售日期 smalldatetime, 销售订单 int)
   INSERT
   INTO day_total
   SELECT SellOrderDate,COUNT(SellOrderID)
   FROM Sell_Order
   GROUP BY SellOrderDate
   ```

运行结果：

![image-20220521153609806](https://lsky-picture.stdcdn.com/uploads/2022/05/7ac5aa180d9c9eae10b5edbec3eb2e02.png)

![image-20220521153641990](https://lsky-picture.stdcdn.com/uploads/2022/05/a3397b39a9d4fec50dc3265468bf8faa.png)
3. 将商品表中所有商品的价格上浮20%。

语句：

```sql
   UPDATE Product
   SET Price=Price*1.2
   ```

结果：

![image-20220521154107349](https://lsky-picture.stdcdn.com/uploads/2022/05/3ddb8f8ca32130b6b0e89efa12869086.png)

![image-20220521154214253](https://lsky-picture.stdcdn.com/uploads/2022/05/668a582ade2ad7471ea5a58fb9555492.png)
4. 将商品表中库存量小于10的商品库存量置零。

语句：

```sql
   UPDATE Product
   SET ProductStockNumber = 0 
   WHERE ProductStockNumber < 10
   ```

结果：

![image-20220521154339880](https://lsky-picture.stdcdn.com/uploads/2022/05/6b788822241dceca421d5ed652029be0.png)
5. 删除采购订单表Purchase_Order中商品库存量小于0的订单。

语句：

```sql
   DELETE
   FROM Purchase_order
   WHERE ProductID IN 
   	( SELECT ProductID
   	  FROM Sell_Order
   	  WHERE SellOrderNumber < 0
   	  )
   ```

结果：

![image-20220521155406343](https://lsky-picture.stdcdn.com/uploads/2022/05/998a1d2fd01855fe0cebdd51955d27ff.png)
6. 删除员工EmployeeName为“余杰”的所有销售订单。（自己任意指定一个员工名）

语句：

```sql
   DELETE
   FROM Sell_Order
   WHERE EmployeeID IN 
   	( SELECT EmployeeID
   	  FROM Employee
   	  WHERE EmployeeName = '余杰'
   	  )
   ```

结果：

![操作](https://lsky-picture.stdcdn.com/uploads/2022/05/8b0fdedf06f1a15b9a5afa32f98e93be.png)

![更新前](https://lsky-picture.stdcdn.com/uploads/2022/05/8208d8fb5a5c2e6f6326bdcf97ce8f10.png)

![更新后](https://lsky-picture.stdcdn.com/uploads/2022/05/db151efcfff5fd1db2d530d777d58ce2.png)
7. 创建视图View_employee，输出员工号，姓名，性别，部门号

语句：

```sql
   CREATE VIEW View_employee(员工号,姓名,性别,部门号)
   AS
   SELECT EmployeeID,EmployeeName,Sex,DepartmentID
   FROM Employee
   ```

结果：

![image-20220521161301981](https://lsky-picture.stdcdn.com/uploads/2022/05/e2d6dbafaa62bd21733734c1f0efd03b.png)

![image-20220521161322697](https://lsky-picture.stdcdn.com/uploads/2022/05/3e605dfbf4bc2ca9c8d8044030b75da3.png)
8. 基于视图View_employee和部门表，查询各部门名称、各部门员工人数。

语句：

```sql
   SELECT D.DepartmentName '部门名称',COUNT('员工号') '各部门员工人数'
   FROM Department D,View_employee E
   WHERE D.DepartmentID = E.部门号
   GROUP BY D.DepartmentName
   ```

结果：

![image-20220521162051781](https://lsky-picture.stdcdn.com/uploads/2022/05/198c03806460d04e70f33fd71f159be6.png)
9. 创建客户订单信息视图View_customerorder，包括客户公司名称，订购商品名称，单价，订购日期。

语句：

```sql
   CREATE VIEW View_customerorder(客户公司名称,订购商品名称,单价,订购日期)
   AS
   SELECT CompanyName,ProductName,Price,SellOrderDate
   FROM Customer C,Sell_Order S,Product P
   WHERE C.CustomerID = S.CustomerID AND
   S.ProductID = P.ProductID
   ```

结果：

![image-20220521162934932](https://lsky-picture.stdcdn.com/uploads/2022/05/ea3aa777628946971bb95363ed73aa1b.png)

![image-20220521162956401](https://lsky-picture.stdcdn.com/uploads/2022/05/32d11f8788385b0e71f7712a27e26764.png)
10. 基于视图View_customerorder，查询“三川实业有限公司”订购商品情况。

语句：

```sql
SELECT *
FROM View_customerorder
WHERE 客户公司名称 = '三川实业有限公司'
```

结果：

![image-20220521163126851](https://lsky-picture.stdcdn.com/uploads/2022/05/85a61f9cc4fc6c1328f4d52a28680b4e.png)

# 实验五 数据库编程—存储过程

> 日期：2022.5.21

## 一、实验目的

1. 掌握数据库编程各种控制语句的使用
2. 掌握存储过程的创建

## 二、实验内容

1. 编程计算`S=2+4+6+……+100`

语句：

```sql
   DECLARE @i int,@s int;
   SET @i=2;
   SET @s=0;
   WHILE (@i<101)
   	BEGIN
   		SET @s += @i;
   		SET @i += 2;
   	END
   	PRINT @s
   GO
   ```

结果：

![image-20220521201928814](https://lsky-picture.stdcdn.com/uploads/2022/05/9e089aa2a2ea61ca152b906d2aa26bf3.png)
2. 编程计算`S=1+（1+3）+（1+3+5）+……+（1+3+5……+51）`

语句：

```sql
   DECLARE @s int,@i int,@t int;
   SET @s = 0;
   SET @i = 1;
   SET @t = 1;
   WHILE (@i <= 51)
   	BEGIN
   		SET @s += @t;
   		SET @i += 2;
   		SET @t += @i;
   	END
   PRINT @s;
   GO
   ```

结果：

![image-20220521205214605](https://lsky-picture.stdcdn.com/uploads/2022/05/9208815429fba9b5631ed1e55cddf71b.png)
3. 查询是否有“打印机”的订单，如果有，输出“目前有打印机的订单”，如果没有，输出“目前没有打印机的订单”。

语句：

```sql
   DECLARE @n int;
   SET @n =
   (	SELECT COUNT(SellOrderID) 
   	FROM Sell_Order 
   	WHERE ProductID IN 
   	(	SELECT ProductID 
   		FROM Product 
   		WHERE ProductName = '打印机' 
   	)
   )

IF(@n=0)
   	BEGIN
   		PRINT '目前没有打印机订单'
   	END
   ELSE
   	BEGIN
   		PRINT '目前有打印机订单'
   	END
   GO
   ```

结果：

![image-20220521205144236](https://lsky-picture.stdcdn.com/uploads/2022/05/a85c18c996581e38cc31bceab0cf6bf7.png)
4. 在销售管理数据库中创建存储过程`proc_select`，实现查询所有员工的功能。

语句：

```sql
   CREATE PROCEDURE proc_select
   AS
   BEGIN
   	SELECT *
   	FROM Employee
   END

EXECUTE proc_select
   ```

运行结果：

![image-20220521214543716](https://lsky-picture.stdcdn.com/uploads/2022/05/ac3f624b8ef7557d47859613f34ff202.png)

查询结果：

![image-20220521214606658](https://lsky-picture.stdcdn.com/uploads/2022/05/aeaa478ee7da67b1f0e959d0c2472cc3.png)
5. 在销售管理数据库中创建存储过程`proc_employee_order`，要求实现如下功能：根据员工的姓名查询该员工的奖金情况，奖金根据该员工接收订单的总金额计算得到（奖金=总金额*5%），调用存储过程，查询员工王孔若和蔡慧敏的奖金。

语句：

```sql
   CREATE PROCEDURE proc_employee_order
   	@name varchar(20)
   AS
   BEGIN
   	SELECT E.EmployeeName ,SUM(P.Price*S.SellOrderNumber*0.05) '奖金'
   	FROM Employee E,Product P,Sell_Order S
   	WHERE E.EmployeeID = S.EmployeeID AND P.ProductID = S.ProductID AND E.EmployeeName = @name
   	GROUP BY E.EmployeeName
   END

EXECUTE proc_employee_order '王孔若'
   EXECUTE proc_employee_order '蔡慧敏'
   ```

运行结果：

![image-20220521212736256](https://lsky-picture.stdcdn.com/uploads/2022/05/5274478e57a2e9131b8cc2f1d9302e84.png)

查询结果：

![image-20220521212841181](https://lsky-picture.stdcdn.com/uploads/2022/05/cd0b919255d047224c2028fd0c80eb4e.png)
6. 在销售管理数据库中创建存储过程`proc_customer_order`, 要求实现如下功能：根据客户的公司名称查询该客户的订单情况，如果该公司没有订购商品，则输出“某某公司没有订购商品”，否则输出订购商品的相关信息，包括公司名称，联系人姓名，订购商品名称，订购数量，单价。调用存储过程，查询“通恒机械有限公司”订购商品情况。

语句：

```sql
   CREATE PROCEDURE proc_customer_order
   	@name varchar(100)
   AS
   BEGIN
   	IF (SELECT COUNT(S.SellOrderID)
   		FROM Customer C,Sell_Order S,Product P
   		WHERE C.CustomerID = S.CustomerID AND  P.ProductID = S. ProductID AND C.CompanyName = @name
   		)=0
   		BEGIN
   			PRINT @name+'没有订购商品'
   		END
   	ELSE
   		BEGIN
   			SELECT C.CompanyName '公司名称',C.ContactName '联系人姓名',P.ProductName '订购商品名称',S.SellOrderNumber '订购数量',P.Price '单价'
   			FROM Customer C,Sell_Order S,Product P
   			WHERE C.CustomerID = S.CustomerID AND  P.ProductID = S. ProductID AND C.CompanyName = @name
   		END
   END
   ```

运行结果：

![image-20220521214316316](https://lsky-picture.stdcdn.com/uploads/2022/05/566dbe6f3953ddfd86277ffc16df0098.png)

查询结果：

![image-20220521214458050](https://lsky-picture.stdcdn.com/uploads/2022/05/7501d212a9baef0e70bf59a755fc18bd.png)

# 实验六 数据库编程-触发器

> 日期：2022.5.22

## 一、实验目的

1. 掌握触发器的概念，了解触发器的类型
2. 掌握存储过程的创建与执行方法

## 二、实验内容

1. 创建触发器trigger_delete，实现以下功能：当订单表的数据被删除时，显示提示信息“订单表记录被修改了”。

语句：

```sql
   CREATE TRIGGER trigger_delete
   ON Sell_Order
   FOR DELETE
   AS
   BEGIN
   	PRINT '订单表记录被修改了'
   END
   ```
   运行结果：

![image-20220522204809013](https://lsky-picture.stdcdn.com/uploads/2022/05/0cf763af8d6cb5de16256304ccdb434a.png)
2. 对Sell_Order表创建名为reminder的触发器，当用户向Sell_Order表中插入或修改记录时，自动显示Sell_Order表中的记录。

由于在部门Department中已存在名为reminder的触发器，故更名为sellorder_reminder

语句：

```sql
   CREATE TRIGGER sellorder_reminder
   ON Sell_Order
   FOR UPDATE
   AS
   BEGIN
   	SELECT *
   	FROM Sell_Order
   END
   ```
   运行结果：

![image-20220522204836550](https://lsky-picture.stdcdn.com/uploads/2022/05/aeb992614b74814cba8ba64dd8649c76.png)
3. 对Employee表中创建名为emp_updtri的触发器，实现如下功能：当修改姓名时，自动检查订单表，确定是否有该员工的订单，如果存在该员工，则撤销操作。

语句：

```sql
   CREATE TRIGGER emp_updtri
   ON Employee
   FOR UPDATE
   AS
   BEGIN
   	IF UPDATE(EmployeeName)
   		DECLARE @id int
   		BEGIN
   			SELECT @id=EmployeeID
   			FROM DELETED
   			IF EXISTS(SELECT * FROM Sell_Order WHERE Sell_Order.EmployeeID=@id)
   			BEGIN
   				PRINT'不能执行操作'
   				ROLLBACK
   			END
   		END
   END
   ```
   运行结果：

![image-20220522205935770](https://lsky-picture.stdcdn.com/uploads/2022/05/f39177ea7889662ec85e0f1009c81138.png)
4. 创建一个INSERT触发器，当在Employee表中插入一条新员工记录时，如果是“人事部”的员工，则撤销该插入操作，并返回出错消息。

语句：

```sql
   CREATE TRIGGER employee_insert
   ON Employee
   INSTEAD OF INSERT
   AS
   BEGIN
   	DECLARE @name varchar(100);
   	SET @name =
   	(
   		SELECT DepartmentName
   		FROM Department D,inserted I
   		WHERE D.DepartmentID = I.DepartmentID
   	)
   	IF (@name = '人事部')
   	BEGIN
   		PRINT '该员工所属人事部，撤销本次插入操作'
   		ROLLBACK
   	END
   END
   ```
   运行结果：

![image-20220524191451390](https://lsky-picture.stdcdn.com/uploads/2022/05/f3f5d0a6c01d3680f5c14edf3f6842a7.png)
5. 创建一个product_order_delete的触发器，其功能是：当删除商品表中的商品记录时，同时删除订单表中相应的订单，并显示提示信息“有关商品已被删除”。

语句：

```sql
   CREATE TRIGGER product_order_delete
   ON Product
   FOR DELETE
   AS
   BEGIN
   	DECLARE @productid int
   	SET @productid = 
   	(
   		SELECT ProductID
   		FROM deleted
   	)
   	DELETE
   	FROM Purchase_order
   	WHERE Purchase_order.ProductID = @productid
   END
   ```
   运行结果：

![image-20220524192749781](https://lsky-picture.stdcdn.com/uploads/2022/05/721dbf77c2c560eab4bfae9b10535330.png)
6. 创建一个名为employee_deleted的触发器，其功能是：当对Employee表进行删除操作时，首先检查订单表，如果删除的员工没有接收订单，可以删除该员工的消息，否则撤销删除，显示“无法修改”的信息。

语句：

由于存在employee_deleted的触发器，故更名emp_deleted

```sql
   CREATE TRIGGER emp_deleted
   ON Employee
   FOR DELETE 
   AS
   BEGIN
   	DECLARE @employeeid int
   	SET @employeeid =
   	(
   		SELECT EmployeeID
   		FROM deleted
   	)
   	IF(
   		SELECT COUNT(*)
   		FROM Sell_Order
   		WHERE @employeeid = Sell_Order.EmployeeID
   	)!=0
   	BEGIN
   		PRINT '无法修改'
   		ROLLBACK
   	END
   END
   ```
   运行结果：

![image-20220524193715558](https://lsky-picture.stdcdn.com/uploads/2022/05/0682a769d9f82a6bae6a2503560213e8.png)

最后修改：2022 年 06 月 03 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

数据库原理实验

酱油瓶 • 2022 年 05 月 21 日

> 这是本人数据原理的实验，放到博客归档，可供参考

# 实验一 单表查询

> 日期：2022.5.1

## **一、实验目的**

1. 掌握指定列或全部列查询
2. 掌握按条件查询
3. 掌握对查询结果排序
4. 掌握使用聚集函数的查询
5. 掌握分组统计查询

## **二、实验内容**

### 1. 指定列或全部列查询

1. 查询商品表（Product）中所有商品的信息

查询语句：

```sql
   SELECT *
   FROM dbo.Product
   ```

查询结果：

查询语句：

```sql
   SELECT top 5 CompanyName,ContactName,Address
   FROM dbo.Customer
   ```

查询结果：

![image-20220502211814783](https://lsky-picture.stdcdn.com/uploads/2022/05/c4fb77df9b707f12ecd7daae0ef03bb0.png)
3. 从员工表（Employee）中查询所有员工的部门信息。

查询语句：

```sql
   SELECT DISTINCT DepartmentID
   FROM dbo.Employee
   ```

查询结果：

查询语句：

```sql
   SELECT EmployeeName,Salary,Salary*1.1 as 提高后工资
   FROM dbo.Employee
   ```

查询结果：

![image-20220502212643232](https://lsky-picture.stdcdn.com/uploads/2022/05/18e3c92606ae92796c4700e7c5f54755.png)
5. 统计员工表（Employee）中的职工数。

查询语句：

```sql
   SELECT COUNT(*) as 职工数
   FROM dbo.Employee
   ```

查询结果：

![image-20220502213439932](https://lsky-picture.stdcdn.com/uploads/2022/05/9d101556efa63e7852a15445efe8ed92.png)

### 2. 按条件查询

1. 检索员工表所有姓李和姓章的员工信息。

查询语句：

```sql
   SELECT *
   FROM dbo.Employee
   WHERE EmployeeName like '李%' OR EmployeeName like '章%'
   ```

查询结果：

![image-20220502220807371](https://lsky-picture.stdcdn.com/uploads/2022/05/35b73d705e8bb586028152fe0c78b98b.png)
2. 检索员工表姓李的名字只有一个汉字的员工信息。

查询语句：

```sql
   SELECT *
   FROM dbo.Employee
   WHERE EmployeeName like '李_'
   ```

查询结果：

![image-20220502220846475](https://lsky-picture.stdcdn.com/uploads/2022/05/17d5c1214cb7b81d03f61b0a85d955b1.png)
3. 查询员工表中工资在3400以下的女性员工姓名和工资信息。

查询语句：

```sql
   SELECT EmployeeName,Salary
   FROM dbo.Employee
   WHERE Sex = '女' AND Salary < 3400
   ```

查询结果：

![image-20220502221225040](https://lsky-picture.stdcdn.com/uploads/2022/05/15c1eed17df0fd0c25adb003025a3f0f.png)
4. 查询员工表中工资在5000到7000之间的员工信息。（Between）

查询语句：

```sql
   SELECT *
   FROM dbo.Employee
   WHERE Salary BETWEEN 5000 AND 7000
   ```

查询结果：

查询语句：

```sql
   SELECT *
   FROM dbo.Sell_Order
   WHERE EmployeeID IN (1,5,7)
   ```

查询结果：

![image-20220502221939133](https://lsky-picture.stdcdn.com/uploads/2022/05/56cf6eb910c34751d6f38a8354cb416a.png)
6. 检索部门表（Department）中主管位置不为空的部门信息。

查询语句：

```sql
   SELECT *
   FROM dbo.Department
   WHERE Manager IS NOT NULL
   ```

查询结果：

![image-20220502222112059](https://lsky-picture.stdcdn.com/uploads/2022/05/cdc89fbe384f63eada5eb9627e0f3b6c.png)

### 3. 分组统计查询

1. 查询员工表中男女员工的平均工资。输出字段性别，平均工资。

查询语句：

```sql
   SELECT Sex as '性别', AVG(Salary) as '平均工资'
   FROM dbo.Employee
   GROUP BY Sex
   ```

查询结果：

查询语句：

```sql
   SELECT ProductID,SUM(SellOrderNumber) as '订货总数'
   FROM dbo.Sell_Order
   GROUP BY ProductID
   ```

查询结果：

查询语句：

```sql
   SELECT CustomerID, COUNT(ProductID) as '商品种类'
   FROM dbo.Sell_Order
   GROUP BY CustomerID
   HAVING COUNT(ProductID) > 1
   ```

查询结果：

![image-20220502223406757](https://lsky-picture.stdcdn.com/uploads/2022/05/4a26c08ac4540afbb1e3fa9316a95181.png)

### 4. 对查询结果排序

1. 查询员工表中男女员工的平均工资。输出字段性别，平均工资，按照平均工资升序排列。

查询语句：

```sql
   SELECT Sex as '性别', AVG(Salary) as '平均工资'
   FROM dbo.Employee
   GROUP BY Sex
   ORDER BY '平均工资' ASC
   ```

查询结果：

查询语句：

```sql
   SELECT ProductID,SUM(SellOrderNumber) as '订货总数'
   FROM dbo.Sell_Order
   GROUP BY ProductID
   ORDER BY '订货总数' DESC
   ```

查询结果：

![image-20220502223854054](https://lsky-picture.stdcdn.com/uploads/2022/05/a56cd8ca0c33be680eb1adf0ad29140c.png)

# 实验二 连接查询

> 日期：2022.5.9

## 一、实验目的

掌握涉及一个以上数据表的查询方法。

## 二、实验过程

1. 连接查询

1. 查询“国皓科技有限公司”的订单信息

查询语句：

```sql
      SELECT *
      FROM dbo.Customer,dbo.Sell_Order
      WHERE dbo.Customer.CustomerID = dbo.Sell_Order.CustomerID AND dbo.Customer.CompanyName = '国皓科技有限公司'
      ```

查询结果：

查询语句：

查询结果：

查询语句：

查询结果：

查询语句：

查询结果：

![image-20220509114947020](https://lsky-picture.stdcdn.com/uploads/2022/05/0add8502bf20ccbca5f44d319e0a44e5.png)
2. 自身连接

1. 查询和“章宏”同一部门的员工号，员工姓名。

查询语句：

查询结果：

![image-20220509115710326](https://lsky-picture.stdcdn.com/uploads/2022/05/fe4134e2eba75229910aa53a45d7c29d.png)
   2. 查询既订购过3号产品，又订购过4号产品的客户号。

查询语句：

查询结果：

![image-20220509120222127](https://lsky-picture.stdcdn.com/uploads/2022/05/84778aba9d47e91cfe9ec82027a64a7c.png)
3. 外连接

1. 查询没有接收到订单的员工姓名。

查询语句：

```sql
      SELECT EmployeeName
      FROM Employee LEFT OUTER JOIN Sell_Order
      ON Employee.EmployeeID = Sell_Order.EmployeeID
      WHERE SellOrderID IS NULL
      ```

查询结果：

![image-20220509120934893](https://lsky-picture.stdcdn.com/uploads/2022/05/dcd04bec0bc66e9610978c213d7c13b5.png)
   2. 查询没有任何订购信息的客户公司名。

查询语句：

```sql
      SELECT CompanyName
      FROM Customer LEFT OUTER JOIN Sell_Order
      ON Customer.CustomerID = Sell_Order.CustomerID
      WHERE SellOrderID IS NULL
      ```

查询结果：

![image-20220509121342736](https://lsky-picture.stdcdn.com/uploads/2022/05/e68a79ac3dcb033c8706701fb871949c.png)
   3. 查询没有被订购的商品名称。

查询语句：

```sql
      SELECT ProductName
      FROM Product LEFT OUTER JOIN Sell_Order
      ON Product.ProductID = Sell_Order.ProductID
      WHERE SellOrderID IS NULL
      ```

查询结果：

![image-20220509121700638](https://lsky-picture.stdcdn.com/uploads/2022/05/3b99ce2596827c1a493870a63bdc1894.png)

# 实验三 嵌套查询

> 日期： 2022.5.12

## 一、实验目的

掌握嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。

## 二、实验内容

1）**返回一个值的子查询**

1. 查询和“章宏”同一部门的员工号，员工姓名。

查询语句：

```sql
   SELECT EmployeeID,EmployeeName
   FROM Employee
   WHERE DepartmentID IN
   	(	SELECT DepartmentID
   		FROM Employee
   		WHERE EmployeeName = '章宏'
   	)
   ```

查询结果：

![image-20220512205819590](https://lsky-picture.stdcdn.com/uploads/2022/05/d421cc3c2472e2623c39684924a644b7.png)
2. 查询年龄最小的员工姓名、性别和工资。

查询语句：

```sql
   SELECT EmployeeName,Sex,Salary
   FROM Employee
   WHERE BirthDate IN
   	(	SELECT MAX(BirthDate)
   		FROM Employee
   	)
   ```

查询结果：

![image-20220512210144545](https://lsky-picture.stdcdn.com/uploads/2022/05/fad5accd01f30b396b036c08a15d2513.png)
3. 查询比平均工资高的员工姓名和工资。

查询语句：

```sql
   SELECT EmployeeName,Salary
   FROM Employee
   WHERE Salary >
   	(	SELECT AVG(Salary)
   		FROM Employee
   	)
   ```

查询结果：

![image-20220512211021990](https://lsky-picture.stdcdn.com/uploads/2022/05/200766d24e677c20674d292b3bc9b63e.png)

2）**返回一组值的子查询(嵌套查询和连接查询分别实现)**

1. 查询目前还没有接收到销售订单的员工姓名和工资信息。

嵌套查询语句：

```sql
   SELECT EmployeeName,Salary
   FROM Employee
   WHERE EmployeeID NOT IN 
   	(	SELECT EmployeeID 
   		FROM Sell_Order
   	)
   ```

查询结果：

![image-20220512211425064](https://lsky-picture.stdcdn.com/uploads/2022/05/bf33d23263e22ac9d8921a165288fa54.png)

连接查询语句：

```sql
   SELECT EmployeeName,Salary
   FROM Employee LEFT OUTER JOIN Sell_Order ON (Employee.EmployeeID = Sell_Order.EmployeeID)
   WHERE SellOrderID IS NULL
   ```

查询结果：

![image-20220512214952669](https://lsky-picture.stdcdn.com/uploads/2022/05/fa496d194b1e16c284b3a03f35ebe288.png)
2. 查询订购牛奶的客户名称和联系地址。

查询语句：

查询结果：

![image-20220512212150902](https://lsky-picture.stdcdn.com/uploads/2022/05/3a0e7458347c3edb1c104551fdc4ab24.png)

连接查询语句：

查询结果：

查询语句：

```sql
   SELECT CompanyName,Phone
   FROM Customer
   WHERE CustomerID IN
   	(	SELECT CustomerID
   		FROM Sell_Order
   		GROUP BY CustomerID
   		HAVING SUM(SellOrderNumber)>400
   	)
   ```

查询结果：

![image-20220512212639021](https://lsky-picture.stdcdn.com/uploads/2022/05/bbb8dad6abb85aacdf783cf6958a8de6.png)

连接查询语句：

```sql
   SELECT CompanyName,Phone
   FROM Customer C JOIN Sell_Order S
   ON C.CustomerID=S.CustomerID 
   GROUP BY CompanyName,Phone
   HAVING SUM(SellOrderNumber)>400
   ```

查询结果：

![image-20220512215315211](https://lsky-picture.stdcdn.com/uploads/2022/05/6728f7d9c9c7111a0ae6940dbd60537a.png)

# 实验四 **数据更新和视图的创建**

> 日期：2022.5.20

## 一、实验目的

1.掌握数据更新语句

2.掌握视图创建的SQL语句

## 二、实验内容

1. 向客户表中插入一行：客户编号为34，公司名称为“人民低电压”，联系人为“南辉”。

插入语句：

```sql
   INSERT
   INTO Customer(CustomerID,CompanyName,ContactName)
   VALUES(34,'人民低电压','南辉')
   ```

运行结果：

语句：

运行结果：

![image-20220521153609806](https://lsky-picture.stdcdn.com/uploads/2022/05/7ac5aa180d9c9eae10b5edbec3eb2e02.png)

![image-20220521153641990](https://lsky-picture.stdcdn.com/uploads/2022/05/a3397b39a9d4fec50dc3265468bf8faa.png)
3. 将商品表中所有商品的价格上浮20%。

语句：

```sql
   UPDATE Product
   SET Price=Price*1.2
   ```

结果：

![image-20220521154107349](https://lsky-picture.stdcdn.com/uploads/2022/05/3ddb8f8ca32130b6b0e89efa12869086.png)

![image-20220521154214253](https://lsky-picture.stdcdn.com/uploads/2022/05/668a582ade2ad7471ea5a58fb9555492.png)
4. 将商品表中库存量小于10的商品库存量置零。

语句：

```sql
   UPDATE Product
   SET ProductStockNumber = 0 
   WHERE ProductStockNumber < 10
   ```

结果：

![image-20220521154339880](https://lsky-picture.stdcdn.com/uploads/2022/05/6b788822241dceca421d5ed652029be0.png)
5. 删除采购订单表Purchase_Order中商品库存量小于0的订单。

语句：

```sql
   DELETE
   FROM Purchase_order
   WHERE ProductID IN 
   	( SELECT ProductID
   	  FROM Sell_Order
   	  WHERE SellOrderNumber < 0
   	  )
   ```

结果：

语句：

```sql
   DELETE
   FROM Sell_Order
   WHERE EmployeeID IN 
   	( SELECT EmployeeID
   	  FROM Employee
   	  WHERE EmployeeName = '余杰'
   	  )
   ```

结果：

![操作](https://lsky-picture.stdcdn.com/uploads/2022/05/8b0fdedf06f1a15b9a5afa32f98e93be.png)

![更新前](https://lsky-picture.stdcdn.com/uploads/2022/05/8208d8fb5a5c2e6f6326bdcf97ce8f10.png)

![更新后](https://lsky-picture.stdcdn.com/uploads/2022/05/db151efcfff5fd1db2d530d777d58ce2.png)
7. 创建视图View_employee，输出员工号，姓名，性别，部门号

语句：

```sql
   CREATE VIEW View_employee(员工号,姓名,性别,部门号)
   AS
   SELECT EmployeeID,EmployeeName,Sex,DepartmentID
   FROM Employee
   ```

结果：

![image-20220521161301981](https://lsky-picture.stdcdn.com/uploads/2022/05/e2d6dbafaa62bd21733734c1f0efd03b.png)

语句：

结果：

语句：

结果：

![image-20220521162934932](https://lsky-picture.stdcdn.com/uploads/2022/05/ea3aa777628946971bb95363ed73aa1b.png)

语句：

```sql
SELECT *
FROM View_customerorder
WHERE 客户公司名称 = '三川实业有限公司'
```

结果：

![image-20220521163126851](https://lsky-picture.stdcdn.com/uploads/2022/05/85a61f9cc4fc6c1328f4d52a28680b4e.png)

# 实验五 数据库编程—存储过程

> 日期：2022.5.21

## 一、实验目的

1. 掌握数据库编程各种控制语句的使用
2. 掌握存储过程的创建

## 二、实验内容

1. 编程计算`S=2+4+6+……+100`

语句：

```sql
   DECLARE @i int,@s int;
   SET @i=2;
   SET @s=0;
   WHILE (@i<101)
   	BEGIN
   		SET @s += @i;
   		SET @i += 2;
   	END
   	PRINT @s
   GO
   ```

结果：

![image-20220521201928814](https://lsky-picture.stdcdn.com/uploads/2022/05/9e089aa2a2ea61ca152b906d2aa26bf3.png)
2. 编程计算`S=1+（1+3）+（1+3+5）+……+（1+3+5……+51）`

语句：

结果：

语句：

IF(@n=0)
   	BEGIN
   		PRINT '目前没有打印机订单'
   	END
   ELSE
   	BEGIN
   		PRINT '目前有打印机订单'
   	END
   GO
   ```

结果：

语句：

```sql
   CREATE PROCEDURE proc_select
   AS
   BEGIN
   	SELECT *
   	FROM Employee
   END

EXECUTE proc_select
   ```

运行结果：

![image-20220521214543716](https://lsky-picture.stdcdn.com/uploads/2022/05/ac3f624b8ef7557d47859613f34ff202.png)

查询结果：

语句：

EXECUTE proc_employee_order '王孔若'
   EXECUTE proc_employee_order '蔡慧敏'
   ```

运行结果：

![image-20220521212736256](https://lsky-picture.stdcdn.com/uploads/2022/05/5274478e57a2e9131b8cc2f1d9302e84.png)

查询结果：

语句：

运行结果：

![image-20220521214316316](https://lsky-picture.stdcdn.com/uploads/2022/05/566dbe6f3953ddfd86277ffc16df0098.png)

查询结果：

![image-20220521214458050](https://lsky-picture.stdcdn.com/uploads/2022/05/7501d212a9baef0e70bf59a755fc18bd.png)

# 实验六 数据库编程-触发器

> 日期：2022.5.22

## 一、实验目的

1. 掌握触发器的概念，了解触发器的类型
2. 掌握存储过程的创建与执行方法

## 二、实验内容

1. 创建触发器trigger_delete，实现以下功能：当订单表的数据被删除时，显示提示信息“订单表记录被修改了”。

语句：

```sql
   CREATE TRIGGER trigger_delete
   ON Sell_Order
   FOR DELETE
   AS
   BEGIN
   	PRINT '订单表记录被修改了'
   END
   ```
   运行结果：

由于在部门Department中已存在名为reminder的触发器，故更名为sellorder_reminder

语句：

```sql
   CREATE TRIGGER sellorder_reminder
   ON Sell_Order
   FOR UPDATE
   AS
   BEGIN
   	SELECT *
   	FROM Sell_Order
   END
   ```
   运行结果：

语句：

由于存在employee_deleted的触发器，故更名emp_deleted

![image-20220524193715558](https://lsky-picture.stdcdn.com/uploads/2022/05/0682a769d9f82a6bae6a2503560213e8.png)

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

数据库原理实验

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款