《数据库原理》实验八 T_SQL编程

本系列传送门：

1. 实验二 SQL Server SSMS工具创建和管理数据库及数据表
2. 实验三 SQL Server SSMS工具添加数据
3. 实验四 SQL的数据定义语句
4. 实验五 数据库完整性约束的实现与验证
5. 实验六 SQL数据查询实验
6. 实验六（2） SQL数据查询—连接查询
7. 实验七 SQL的数据更新和视图
8. 实验八 T_SQL编程
9. 实验九 存储过程和触发器
10. 实验十 索引和数据库的安全性

1. 本实验的数据来源

可参考 实验三 SQL Server SSMS工具添加数据的导入脚本。

2. 实验目的

1、 掌握SQL定义实体完整性、参照完整性、用户自定义完整性的方法
2、 加深理解并通过实践操作验证RDBMS针对实体完整性、参照完整性、用户自定义完整性的违约处理机制。
3、 掌握规则和默认值的SQL定义语句

3. 实验内容

以电子商务系统数据库ecommerce为例

1、在ecommerce数据库，针对会员表member首先创建一个“呼和浩特地区”会员的视图view_hohhot，然后通过该视图查询来自“呼和浩特”地区的会员信息，用批处理命令语句将问题进行分割，并分别以注释进行语句作用的说明描述

go
create view view_hohhot as (
	select * from member
	where member.address like '%呼和浩特%'
);
go

2、创建一个保存类别编号值的局部变量，并以set语句进行赋值，然后在product表中查询产品类别编号为局部变量值的产品名称proname、单价price、成本价cost、库存量stock。

declare @id int;
set @id = 10101;
select proname, price, cost, stock from product
where prono = @id;

3、查询供应商表supplier，如果存在供应商编号14001的记录，则显示“编号为14001的供应商记录已经存在”的消息，并输出此供应商的详细信息；否则插入一条供应商记录（14001，‘京顺通讯有限公司’，‘刘燕’，‘北京市朝阳区’，‘1111111’）

2023年5月17日 感谢：@ibuopat 勘误 🥳

declare @id as int = 14001;
if exists(
	select *
	from supplier
	where supno = @id
)
begin
	select * from supplier
	where supno = @id
	print '编号为14001的供应商记录已经存在'
end
else
begin
	insert into supplier
	values (@id,'京顺通讯有限公司','刘燕','北京市朝阳区','1111111')
end

4、查询商品表product的商品编号、商品名称、单价、库存量、商品类别名称信息，其中商品类别使用CASE语句，当类别编号为101时，则类别名称为手机；当类别编号为201时，则类别名称为激光打印机；当类别编号为202时，则类别名称为喷墨打印机，当类别编号为301时，则类别名称为平板电脑∙∙∙∙∙∙（具体参照商品表）

select prono, proname, price, stock, case
	when product.catno in (
		select category.catno from category
		where category.catno = product.catno
	) then (select category.catname from category
		where category.catno = product.catno)
	end as catname
from product;

5、在product表中，对产品编号为10102的商品单价进行加价，每次加价50，加大单价等于或高于3000即可，并统计加价的次数，打印输出产品名称、新单价和加价次数

declare @plus int = 50, @id int = 10102, @price int;
select @price = price from product where prono = @id;
declare @count int = 0;
while (@price<3000)
	begin
	update product set price = price + @plus where prono = @id;
	select @price = price from product where prono = @id;
	set @count = @count + 1;
	end
select proname, price, @count from product where prono = @id;

6、自行设计一个多条件查询问题，并使用CASE语句去解决，要求CASE后面没有任何参数表达式，在WHEN子句后接有比较表达式，更加比较表达式的值，执行相对应的THEN后面的语句

成绩打分：（随便写的，源数据库中没有）

select stu_id,student_name,score,
case 
	when score < 60 then '不及格' 
	when score >= 60 and score < 80 then '良好'
	when score >= 80 then '优秀'
end as rank
from exam_score