SQLSever期末考试作业复习卷一

1.请简述数据完整性约束的内容有哪些？

（1）实体完整性：实体完整性是指关系数据库中的所有的表都必须有主键，而且表中不允许存在无主键值的记录和主键值相同的记录。
（2）参照完整性：也称为引用完整性，参照完整性就是描述实体之间的联系的，这里的实体之间可以是不同的实体也可以是相同的实体。限制一个关系中某列的取值受另一个关系中某列的取值范围约束的特点称为参照完整性。
（3）用户自定义完整性：针对某一具体应用领域定义的数据约束条件。用户自定义完整性实际上就是指明关系中属性的取值范围，也就是属性的域，这样可以限制关系中的属性的取值类型及取值范围，防止属性的值与应用语义矛盾。

2.索引分为哪几种类型？分别是什么？它们的主要区别是什么？

一类是聚集索引（Clustered Index，也称为聚簇索引），另一类是非聚集索引（Non-Clustered Index，也称为非聚簇索引）。
聚集索引对数据按索引关键字值进行物理排序；非聚集索引不对数据按索引关键字值进行物理排序和存储；非聚集索引的叶节点不是存放数据的数据页。

1.请简述数据完整性约束的内容有哪些。
（1）实体完整性：实体完整性是指关系数据库中的所有的表都必须有主键，而且表中不允许存在无主键值的记录和主键值相同的记录。
（2）参照完整性：也称为引用完整性，参照完整性就是描述实体之间的联系的，这里的实体之间可以是不同的实体也可以是相同的实体。限制一个关系中某列的取值受另一个关系中某列的取值范围约束的特点称为参照完整性。
（3）用户自定义完整性：针对某一具体应用领域定义的数据约束条件。用户自定义完整性实际上就是指明关系中属性的取值范围，也就是属性的域，这样可以限制关系中的属性的取值类型及取值范围，防止属性的值与应用语义矛盾。

2.索引分为哪几种类型？分别是什么？它们的主要区别是什么？
一类是聚集索引（Clustered Index，也称为聚簇索引），另一类是非聚集索引（Non-Clustered Index，也称为非聚簇索引）。
聚集索引对数据按索引关键字值进行物理排序；非聚集索引不对数据按索引关键字值进行物理排序和存储；非聚集索引的叶节点不是存放数据的数据页。

编程题

1.在C盘创建一个“你的名字+学号”的文件夹。创建一个名为example+你的学号的数据库（如:学号为1号的同学建立的数据库名字为example01）,数据文件名为example.mdf（此物理文件存放在C盘刚建的“你的名字+学号”的文件夹下），日志文件名为example.ldf(此日志文件也存放在C盘下，你的名字+学号的文件夹下)，主文件和日志文件的大小为25MB，最大尺寸为500MB，增长速度为5MB.以下操作在此数据库中进行操作。（保留源代码或设计视图）

2.创建一个新的数据表，表名为“商品基本信息表”，表中各个字段的要求如下表所示。（保留源代码或设计视图）
商品基本信息表
字段名称 字段类型 字段长度 允许空 主键
商品编码 Char 20 No yes
商品名称 Varchar 50 yes no
库存数量 Int 20 yes no
价格 Money 8 yes no
类别 Char 20 yes no
生产地址 Char 30 yes no
生产日期 Datetime yes no

3.创建一个新的数据表，表名为“用户基本信息表”，表中各个字段的要求如下表所示。（保留源代码或设计视图）
用户基本信息表
字段名称 字段类型 字段长度 允许空 主键
用户编号 Char 10 no Yes
姓名 Char 10 Yes no
性别 Char 2 Yes no
所在地址 varchar 50 yes no

4.创建一个新的数据表，表名为“订单信息表”，表中各个字段的要求如下表所示。（保留源代码或设计视图）

订单信息表
字段名称 字段类型 字段长度 允许空 主键
订单编码 Char 20 no yes
商品编码 Char 10 no no
用户编号 Char 10 no no
订单数量 int 8 no no
订单金额 Int 25 no no

5.使用insert语句向“商品基本信息表”中插入7条记录，具体数据要求如下表所示。（保留源代码或设计视图）
商品基本信息表
商品
编码 商品名称 库存数量 价格 类别 生产地址 生产日期
100001 脉动 34 4 饮料 江西经济技术开发区 2019年5月
100002 果粒橙 50 3.5 饮料 武汉高新技术产业园 2019年3月
200001 芒果干 24 12 零食 苏州科技园区 2019年5月
200002 喜之郎 12 2.5 果冻 武汉高新技术开发区 2019年6月
300001 毛巾 67 16 生活用品 山东省临沂市工业园区 2018年11月
300002 袜子 89 3 生活用品 江西省上饶市工业园区 2018年3月
300003 牙膏 55 10 生活用品 江西省共青工业园 2020年3月

6.使用insert语句向“用户基本信息表”中插入7条记录，具体数据要求如下表所示。（保留源代码或设计视图）
用户基本信息表
用户编号 姓名 性别 所在地址
JSJ01 高志远 男 江西省
JSJ02 杨大鹏 男 江西省
JSJ03 李小燕 女 广州省
JSJ04 钱江 男 安徽省
JJX01 张波 男 广西省
WYX01 赵晨阳 女 广西省
WYX02 刘军 男 安徽省

7.使用insert语句向“订单信息表”中插入6条记录，具体数据要求如下表所示。（保留源代码或设计视图）

订单信息表
订单编码 商品编码 用户编号 订单数量 订单金额
100001 200001 JSJ01 5 60
100002 200001 JSJ03 7 84
200001 300001 WYX01 4 64
300002 100002 JJX01 5 17.5
200002 200002 JSJ03 8 20
300001 300003 WYX02 33 330

8.创建一个查询，查询商品价格大于10元的所有商品信息。（保留源代码或设计视图

9.创建一个查询，查询每个省份所购买商品的数量。（保留源代码或设计视图）

10.创建一个查询，查询购买商品的用户基本信息。（保留源代码或设计视图）

11.创建一个查询，查询每个商品类别下有几种商品。（保留源代码或设计视图）

12.数据更新，将江西省生产的商品价格上调15%。（保留源代码或设计视图）

13.创建一个新的视图，要求如下：显示所有男性用户购买的商品信息。（保留源代码或设计视图）

14.创建一个查询，查询用户基本信息表中用户编号以JS开头的用户基本信息。（保留源代码或设计视图）

15.创建一个索引，为订单信息表中的订单数量属性创建一个非聚集性索引，索引名为order_index。（保留源代码或设计视图）

16.删除订单信息表中的order_index索引。（保留源代码或设计视图）

参考答案

--1.建库
create database example225 on(
	name='example.mdf',
	filename='C:\徐艳阳225\example.mdf',
	size=25,
	maxsize=500,
	filegrowth=5
)
LOG on(
	name='example.ldf',
	filename='C:\徐艳阳225\example.ldf',
	size=25,
	maxsize=500,
	filegrowth=5
)
go
use example225
--2.创建一个新的数据表，表名为“商品基本信息表”，表中各个字段的要求如下表所示。（保留源代码或设计视图）
create table 商品基本信息表 (
	商品编码	char(20) PRIMARY KEY,
	商品名称	varchar(50),
	库存数量	int,
	价格		money,
	类别		char(20),
	生产地址	char(30),
	生产日期	datetime
)
--3.创建一个新的数据表，表名为“用户基本信息表”，表中各个字段的要求如下表所示。（保留源代码或设计视图）
create table 用户基本信息表(
	用户编号	char(10) PRIMARY KEY,
	姓名		char(10),
	性别		char(2),
	所在地址	varchar(50),
)
--4.创建一个新的数据表，表名为“订单信息表”，表中各个字段的要求如下表所示。（保留源代码或设计视图）
create table 订单信息表 (
	订单编码	Char(20) PRIMARY KEY,
	商品编码	Char(20),
	用户编号	Char(10),
	订单数量	int,
	订单金额	int
)
--5.使用insert语句向“商品基本信息表”中插入7条记录，具体数据要求如下表所示。（保留源代码或设计视图）
insert into 商品基本信息表 values('100001','脉动',34,4,'饮料','江西经济技术开发区','2019-5-1');
insert into 商品基本信息表 values('100002','果粒橙',50,3.5,'饮料','武汉高新技术产业园','2019-3-1');
insert into 商品基本信息表 values('200001','芒果干',24,12,'零食','苏州科技园区','2019-5-1');
insert into 商品基本信息表 values('200002','喜之郎',12,2.5,'果冻','武汉高新技术开发区','2019-6-1');
insert into 商品基本信息表 values('300001','毛巾',67,16,'生活用品','山东省临沂市工业园区','2018-1-1');
insert into 商品基本信息表 values('300002','袜子',89,3,'生活用品','江西省上饶市工业园区','2018-3-1');
insert into 商品基本信息表 values('300003','牙膏',55,10,'生活用品','江西省共青工业园','2020-3-1');

--6.使用insert语句向“用户基本信息表”中插入7条记录，具体数据要求如下表所示。（保留源代码或设计视图）
insert into 用户基本信息表 values('JSJ01','高志远','男','江西省');
insert into 用户基本信息表 values('JSJ02','杨大鹏','男','江西省');
insert into 用户基本信息表 values('JSJ03','李小燕','女','广州省');
insert into 用户基本信息表 values('JSJ04','钱江','男','安徽省');
insert into 用户基本信息表 values('JJJX01','张波','男','广西省');
insert into 用户基本信息表 values('WYX01','赵晨阳','女'	,'广西省');
insert into 用户基本信息表 values('WYX02','刘军','男','安徽省');	

--7. 使用insert语句向“订单信息表”中插入6条记录，具体数据要求如下表所示。（保留源代码或设计视图）
insert into 订单信息表 values('100001','200001','JSJ01',5,60);
insert into 订单信息表 values('100002','200001','JSJ03',7,84);
insert into 订单信息表 values('200001','300001','WYX01',4,64);
insert into 订单信息表 values('300002','100002','JJX01',5,17.5);
insert into 订单信息表 values('200002','200002','JSJ03',8,20);
insert into 订单信息表 values('300001','300003','WYX02',33,330);

--8.创建一个查询，查询商品价格大于10元的所有商品信息。（保留源代码或设计视图)
select * from 商品基本信息表 where 价格>10;

--9. 创建一个查询，查询每个省份所购买商品的数量。（保留源代码或设计视图）
select 所在地址,sum(订单数量) as 数量  from 
订单信息表 join 用户基本信息表 on 订单信息表.用户编号 = 用户基本信息表.用户编号
group by 用户基本信息表.所在地址

--9.--错误写法
select 所在地址,订单数量 as 数量 from 
订单信息表 join 用户基本信息表 on 订单信息表.用户编号 = 用户基本信息表.用户编号
group by 用户基本信息表.所在地址,订单数量

--10.创建一个查询，查询购买商品的用户基本信息。（保留源代码或设计视图）
select 用户基本信息表.用户编号,姓名,性别,所在地址 from 
用户基本信息表 join 订单信息表 on 用户基本信息表.用户编号 = 订单信息表.用户编号

--11.创建一个查询，查询每个商品类别下有几种商品。（保留源代码或设计视图）
select 类别,count(*) 数量 from 商品基本信息表
group by 类别


--12.数据更新，将江西省生产的商品价格上调15%。（保留源代码或设计视图）
update 商品基本信息表 set 价格=价格*1.15
where 生产地址 like '江西%'

select * from 商品基本信息表

--13.创建一个新的视图，要求如下：显示所有男性用户购买的商品信息。（保留源代码或设计视图）
create view Shopinfo_Man(商品编码,商品名称,库存数量,价格,类别,生产地址,生产日期)
as select 商品基本信息表.商品编码,商品名称,库存数量,价格,类别,生产地址,生产日期
from 订单信息表 join 商品基本信息表 on
订单信息表.商品编码 = 商品基本信息表.商品编码
join 用户基本信息表 on 订单信息表.用户编号 = 用户基本信息表.用户编号
where 性别='男'
select * from Shopinfo_Man
drop view Shopinfo_Man

--13，zhushisheng
create view Man_shop
as
 select 商品基本信息表.商品编码,商品名称,库存数量,价格,类别,生产地址,生产日期 from 商品基本信息表 inner join 订单信息表 on 商品基本信息表.商品编码=订单信息表.商品编码
 inner join 用户基本信息表 on 用户基本信息表.用户编号=订单信息表.用户编号
 where 用户基本信息表.性别='男'
 select * from Man_shop

--14. 创建一个查询，查询用户基本信息表中用户编号以JS开头的用户基本信息。（保留源代码或设计视图）
select * from 用户基本信息表 
where 用户编号 like 'JS%'

--15.创建一个索引，为订单信息表中的订单数量属性创建一个非聚集性索引，索引名为order_index。（保留源代码或设计视图）
create nonclustered index order_index 
on 订单信息表(订单数量)

--16.删除订单信息表中的order_index索引。（保留源代码或设计视图）
drop index 订单信息表.order_index


--17.创建一个新的触发器，完成的功能是当在用户基本信息表中插入一条新记录后，触发该触发器，
--显示“你成功插入了一条新记录”的提示信息。（保留源代码或设计视图）（5分）
create trigger mytrigger1 on 用户基本信息表 
after insert 
as declare @msg char(30)
set @msg='你成功插入了一条新记录'
print @msg

drop trigger myTrigger1

insert 用户基本信息表(用户编号) values ('test002')

select * from 用户基本信息表
delete  from 用户基本信息表 where 用户编号='test001' or 用户编号='test002'

--18.创建一个新的触发器，触发器的功能：在订单信息表中插入一条新记录，
--如果该订单中的订单数量超过库存数量，则提示‘该商品数量不足’，
--如果该订单数量没有超过库存数量，则插入这条记录，显示‘你购买成功！’。
create trigger mytrigger2 on 订单信息表
after insert 
as declare @msg1 char(30)
set @msg1='该商品数量不足'
if((select 订单数量 from 订单信息表) > (select 库存数量 from 商品基本信息表)) 
	print @msg1

insert 订单信息表 values ('4000001','400001','insert001',10,50)