《数据库原理与应用》实验报告

一、实验目的

1、使用企业管理器和T-SQL 语句创建和管理数据库。

2、熟练掌握使用企业管理器和T-SQL 语句创建、修改和删除表。

3、熟练掌握使用企业管理器和T-SQL 语句插入、修改和删除表数据。

4、牢记SELECT语句的基本语法格式；

5、熟练掌握使用SQL语句进行单表查询，尤其要熟练掌握GROUP BY子句、HAVING子句和集聚函数；

6、牢记SELECT语句的基本语法格式；

7、熟练掌握使用SQL 标准语句和T-SQL 扩展语句进行连接查询。

8、熟练掌握IN子查询

9、熟练掌握比较子查询和EXISTS子查询

二、实验内容

实验一

1、利用企业管理器创建产品销售数据库CPXS。

Create datebase CPXS;

2、CPXS数据库包含如下三个表：

1、CP（产品编号，产品名称，价格，库存量）产品

CREATE TABLE CP(

产品编号 CHAR(6) NOT NULL,

产品价格 CHAR(30) NOT NULL,

价格 FLOAT(8),

库存 INT

)

2、XSS（客户编号，客户名称，地区，负责人，电话）销售商

CREATE TABLE XSS(

客户编号 CHAR(6) NOT NULL,

客户名 CHAR(30) NOT NULL,

地区 CHAR(10),

负责人 CHAR(8),

电话 CHAR(12)

)

3、CPXSB （产品编号，客户编号，销售日期，数量，销售额）产品销售表

CREATE TABLE CPXSB(

产品编号 CHAR(6),

客户编号 CHAR(6),

销售日期 DATETIME,

数量 INT,

销售额 FLOAT(8)

)

4、用T-SQL 语句向CP 表插入如下记录：

INSERT INTO cp VALUES(

"200001","柜式空调",3000,200);

INSERT INTO cp VALUES(

"200002","微波炉",1000,100);

INSERT INTO cp VALUES(

"200003","抽油烟机",1200,50)

5、用T-SQL 语句向CP 表中增加“产品简介”列，varchar(50)，允许为NULL 。

ALTER TABLE cp ADD COLUMN 产品简介 VARCHAR(50); 6、将CP 表中每种商品的价格打8折。 UPDATE CP SET 价格=`价格`*0.8;

7、将CP 表中价格打9折后小于1500的商品删除。

DELETE FROM CP WHERE (价格*0.9)<1500; 实验二

1、简单查询

a. 查询各种产品的产品编号、产品名称和价格。

SELECT 产品编号,产品名称,价格

FROM cp;

b. 查询地区在“南京”的客户编号和客户名称，结果中各列的标题分别指定为：

Customer id 和 Customer name。

select 客户编号 as Customer_id,客户名称 as Customer_name

from xss

where 地区='南京';

c. 在CP表中增加1列，标题为“评价”，按以下规则确定：

若价格小于1000，内容为“廉价产品”，若价格在1000-2000之间，内容为“一般产品”，若价格在在2000-3000之间，内容为“昂贵产品”，若价格大于3000，内容为“很昂贵产品”。

SELECT 产品编号,产品名称,CASE

WHEN 价格<=1000 THEN '廉价产品'

WHEN 价格<=2000 THEN '一般产品'

WHEN 价格<=3000 THEN '昂贵产品'

ELSE '很昂贵产品' END'平价'

FROM cp;

d. 求各产品编号、名称和产品总值。 SELECT 产品编号,产品名称,(价格*库存量) AS 产品总值 FROM CP;

e. 查询至少购买了至少一种产品的客户编号。

SELECT 客户编号 FROM cpxsb GROUP BY `客户编号` HAVING COUNT(`

数量`)>=1;

f ． 查询价格在1000-2000的产品信息。

SELECT * FROM CP WHERE 1000<价格 AND `价格

`<2000;

g. 查询产品名称含有”空调”的产品情况。

SELECT * FROM CP WHERE 产品名称 LIKE "%空调%";

2、使用分组和集聚函数

先将CPXSB 表数据修改如下图所示：

再完成如下查询：

a.

计算所有产品总价格。 SELECT SUM(价格*库存量) AS 产品总值 FROM CP;

b. 求各种产品20XX 年3月18日销售额。

-- DATETIME 类型后面会有具体的时间，所以后面也得加% SELECT 销售额 FROM cpxsb WHERE 销售日期

LIKE

"20%-03-18%";

c.求购买二种以上产品的客户编号。

-- 按照客户编号进行分组，统计出现的行数

SELECT 客户编号 FROM cpxsb GROUP BY `客户编号` HAVING COUNT(客户编号)>=2;

实验三

1、查询在20XX年3月18日有销售的产品名称（不允许重复）

SELECT DISTINCT 产品名称 FROM cpxsb,cp WHERE cp.`产品编号

`=cpxsb.`产品编号` AND CONVERT(销售日期,CHAR(30)) LIKE

"%20__-03-18%";

2、查询名称为“家电市场”的客户在20XX年3月18日购买的产品名称和数量

SELECT 产品名称,数量 FROM xss, cpxsb,cp

WHERE xss.`客户编号`=cpxsb.`客户编号` AND xss.`客户名称`="

家电市场"

AND cp.`产品编号`=cpxsb.`产品编号` AND cpxsb.`销售日期` LIKE "20%-03-18%";

因为没有购买所以没有记录

3、查找所有产品的销售日期、客户名称和数量

SELECT 产品名称,IFNULL(销售日期,0)AS 销售日期,IFNULL(客户名称,0)AS 客户名称,IFNULL(数量,0)AS 数量

FROM CP LEFT OUTER JOIN cpxsb ON cp.`产品编号`=cpxsb.`产品编号` LEFT OUTER JOIN xss ON cpxsb.`客户编号`=xss.`客户编号`

4、查询所有产品的名称，销售总量和销售总额

SELECT `产品名称`,IFNULL(SUM(`数量`),0) AS 销售总量

,IFNULL(SUM(数量*价格),0) AS 销售总额

FROM CP LEFT OUTER JOIN CPXSB

ON cp.`产品编号`=cpxsb.`产品编号` GROUP BY 产品名称

5、查询所有客户的名称，购买总量和购买总额

SELECT 客户名称,SUM(数量) AS 购买总量, SUM(数量*价格) AS 购买总额 FROM XSS,cpxsb,cp

WHERE xss.`客户编号`=cpxsb.`客户编号` AND cp.`

产品编号

`=cpxsb.`产品编号`GROUP BY xss.`客户编号

`;

6、查询在20XX年3月18日没有销售的产品名称（不允许重复）

SELECT 产品名称 FROM cp

WHERE cp.`产品编号` NOT IN

(SELECT DISTINCT 产品编号 FROM cpxsb WHERE 销售日期 LIKE

"20%-03-18%");

7、查询销售量大于所有20XX 年3月18日销售的各产品销售数量的产品、编号

SELECT 产品名称,cpxsb.产品编号 FROM CP,cpxsb

WHERE cpxsb.`产品编号`=cp.`产品编号` AND cpxsb.数量>ALL(

SELECT 数量 FROM cpxsb WHERE 销售日期 LIKE "20%-03-18%")

8、查询购买了所有产品的客户的名称。

SELECT 客户名称 FROM xss

WHERE NOT EXISTS(

SELECT * FROM CP

WHERE NOT EXISTS (

SELECT * FROM CPXSB

WHERE cpxsb.`产品编号`=cp.`产品编号`

AND xss.`客户编号`=cpxsb.`客户编号`))group by 客户名称

9、查询购买了客户编号为“000001”的客户购买的所有产品的客户

的名称。

SELECT 客户名称 FROM xss

WHERE 客户编号<>'000001' AND NOT EXISTS

(SELECT * FROM cpxsb

WHERE cpxsb.客户编号='000001' AND NOT EXISTS

(SELECT * FROM CPXSB CC

WHERE CC.产品编号=cpxsb.`产品编号` AND XSS.`客户编号`=CC.`客户编号`));

10、查询购买总额最多的客户的名称；

SELECT 客户名称 FROM XSS,CPXSB,CP

WHERE xss.`客户编号`=cpxsb.`客户编号`AND cp.`产品编号`=cpxsb.`产品编号`

GROUP BY xss.`客户名称` ORDER BY SUM(数量*价格) DESC LIMIT 1

11、查询销售总额前三名的产品名称

SELECT 产品名称 AS 销售总额前三的产品 FROM CP,CPXSB

WHERE cp.`产品编号`=cpxsb.`产品编号`

GROUP BY cpxsb.`产品编号`ORDER BY SUM(销售额) DESC LIMIT 3

实验四

1、实体完整性的实现

（1）对CP表、 CPXSB表、XSS表，定义主键约束；

ALTER TABLE CP ADD CONSTRAINT PK_CP PRIMARY KEY(产品编号);

ALTER TABLE XSS ADD CONSTRAINT PK_XSS PRIMARY KEY(客户编号);

ALTER TABLE CPXSB ADD CONSTRAINT PK_CPXSB PRIMARY KEY(产品编号,客户编号)

（2）在CP表的产品名称列定义一个唯一约束；

ALTER TABLE cp ADD UNIQUE(产品名称)

（3）在XSS表的客户名称列定义一个非空约束；

ALTER TABLE XSS

CHANGE 客户名称

客户名称 CHAR(30) NOT NULL

2、参照完整性的实现

（1）建立CP表与CPXSB之间的参照关系，当对主表CP 表进行更新和删除操作时，从表 CPXSB采用NO ACTION方式。

（2）建立CPXSB与XSS表之间的参照关系，当对主表XSS表进行更新和删除操作时，从表 CPXSB采用 CASCADE（级联）方式。

（3）增加外键之后，分别在三个表中增加和删除数据，触发外键，证明其有效性。

3、自定义完整性的实现

（1）在CP 表的价格列上定义大于等于0的检查约束。

ALTER TABLE CPXSB ADD CONSTRAINT FK_CPXSB FOREIGN KEY(产

品编号) REFERENCES CP(产品编号)

ON DELETE NO ACTION ON UPDATE NO ACTION

（2）在CP 表的库存量列上定义大于等于0，小于等于1000的检查约束。 ALTER TABLE CP ADD CONSTRAINT CHK_KUCUN CHECK(库存量>=0 AND 库存量<=1000);

（3）在CPXSB 表的数量列上定义大于等于0，小于等于500的检查约束。 ALTER TABLE CPXSB ADD CONSTRAINT CHK_CPXSB_COUNT CHECK(0<=数量 AND 数量<=500);

（4）在CPXSB 表的销售额列上定义大于等于0的检查约束。

ALTER TABLE CPXSB ADD CONSTRAINT CHK_CPXSB_SALES CHECK(销售额>=0);

（5）在以上两个表中增加数据，触发约束，证明其有效性。

insert into cp VALUES('100010','哈雷摩托车',200,-100,null)

实验五

1、在产品销售数据库CPXS 中创建价格小于2000 的产品视图

VIEW_CP_PRICE2000，要求加密并保证对该视图的更新都要符合价格小于2000 这个条件；

CREATE VIEW VIEW_CP_PRICE2000 AS (

SELECT * FROM CP WHERE 价格<2000)WITH CHECK OPTION;

WITH CHECK OPTION

2、创建各客户购买产品的情况VIEW_GMQK 视图，包括客户编号、客户名称

、产品编号、产品名称、价格，购买日期、购买数量。

CREATE VIEW VIEW_GMQK(客户编号,客户名称,产品编号,产品名称

,价格,购买日期,购买数量) AS

(SELECT CPXSB.客户编号,客户名称,cpxsb.产品编号,产品名称,

价格,销售日期,数量 FROM XSS,cpxsb,CP

WHERE xss.`客户编号`=cpxsb.`客户编号` AND cpxsb.`

产品编

号`=cp.`产品编号`) WITH CHECK OPTION;

3、创建分区视图：在CPXS数据库中创建CP1和CP2两个表，CP1表中为编号

小于等于’ 100010’产品数据， CP1表中为编号大于‘100010’产品

数据，以分区列为产品编号，创建可更新的分区视图VIEW_CP12。

-- CREATE TABLE CP1 LIKE CP;

-- INSERT INTO CP1 SELECT * FROM CP WHERE 产品编号<='100010';

-- ALTER TABLE CP1 ADD CONSTRAINT CHK_CP1 CHECK(产品编号

<='100010');

-- CREATE TABLE CP2 LIKE CP;

-- INSERT INTO CP2 SELECT * FROM CP WHERE 产品编号>='100010';

-- ALTER TABLE CP2 ADD CONSTRAINT CHK_CP2 CHECK(产品编号

>'100010');

CREATE VIEW view_cp12 AS SELECT * FROM CP1 UNION ALL SELECT * FROM CP2;

4、基于VIEW_CP_PRICE2000视图，查询价格在2000以下产品的产品编号、

名称和价格。

SELECT 产品编号,产品名称,价格 FROM view_cp_price2000;

5、基于VIEW_GMQK 视图，查询各客户在20004 年3月18 日购买产品的情况。

SELECT*FROM VIEW_GMQK

WHERE CONVERT(VARCHAR(20),销售日期,21)LIKE'2004-03-18%';

6、对视图VIEW_12进行以下数据更新。

（1）插入一条CP记录（'100042','数码相机',3500,2）。

INSERT INTO VIEW_CP12VALUES(100042,'数码相机',3500,2,NULL);

（2）将产品编号为’100042’的价格改为3000。

UPDATE VIEW_CP12SET价格=3000 WHERE产品编号='100042';

（3）删除产品编号为’100042’的产品。

DELETE FROM VIEW_CP12 WHERE 产品编号='100042'; 7、将VIEW_CP_PRICE2000视图改为不加密。

--解密视图

ALTER VIEW VIEW_CP_PRICE2000 AS

SELECT * FROM CP WITH CHECK OPTION ;

8、将VIEW-GMQK 视图删除。

-- 删除VIEW_GMQK

DROP VIEW VIEW_GMQK ;

实验六

1、变量的定义和赋值

创建一名为Customer_name 的局部变量，并在SELECT 语句中使用该变量查找“广电公司”购买产品的情况。

DECLARE @Customer_name char (30)='广电公司';

SELECT CPXSB .客户编号,地区,客户名称,销售日期 AS 购买时间,数量,销售额

AS 消费金额

FROM CPXSB ,XSS

WHERE CPXSB .客户编号=XSS .客户编号 AND 客户名称=@Customer_name ;

2、用户自定义数据类型定义、使用和删除

用 SQL 命令定义一名为Customer_id 的用户自定义数据类型，要求 char(6)，NOT NULL ，并把该自定义数据类型用来定义XSS 表中的客户编号，然后删除该自定义数据类型，请叙述该过程，并写出相关语句。

创建自定义数据类型，通过EXEC SP_ADDTYPE 语句把属性特征包装起来

EXEC sp_addtype Customer_id ,'char(6)','not null';

修改表的列，用自定义类型修饰

ALTER TABLE XSS ALTER COLUMN 客户编号 Customer_id ;

无法删除类型 'dbo.Customer_id'，因为它正由对象 'XSS' 引用。可能还有其他对象在引用此类型。

先接触自定义类型的引用，才能删除

ALTER TABLE XSS ALTER COLUMN 客户编号 char (6) not

null;

EXEC sp_droptype Customer_id;

创建用户定义类型方法一：

CREATE TYPE SSN

FROM varchar(11)NOT NULL;

删除用户定义类型

drop type ssn

定义两个用户定义类型方法二：

exec sp_addtype iq,'float','null'

exec sp_addtype shoesize,'float','null'

指定约束条件

create rule iq_range as@range between 1 and 200 create rule shoesize_range as@range between 1 and 20

绑定约束类型

exec sp_bindrule'iq_range','iq'

exec sp_bindrule'shoesize_range','shoesize'

删除用户定义类型

exec sp_droptype iq

exec sp_droptype shoesize

3、T-SQL语言编程

（1）用T-SQL语言编程输出3～300之间能被7整除的数。

--用T-SQL语言编程输出～之间能被整除的数。

declare@i int=3

while@i<=300

begin

if@i%7=0

begin

print@i

end

set@i=@i+1

end

（2）用T-SQL语言编程输出100以内的素数。

--用T-SQL语言编程输出以内的素数。

declare@i int=2

declare@j int=2

declare@sum int=0

while@i<=10

begin

while@j<@i

begin

if@i%@j=0

begin

break;

end

set@j=@j+1

end

if@j=@i

begin

set@sum=@sum+@i

end

set@j=2

set@i=@i+1

end

print@sum

实验七

1、无参存储过程。编写一无参存储过程用于查询每个客户购买产品的情况

（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。

a)-- 创建无参存储过程

b)alter proc usp_Purchase_information

c)as

d)begin

e)select cpxsb.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销

售日期AS购买日期,数量AS购买数量

f)from cp left join CPXSB

g)on cp.产品编号=CPXSB.产品编号left join xss

h)on xss.客户编号=CPXSB.客户编号

i)end

j)-- 调用存储过程

k)exec usp_Purchase_information

2、带有参数的存储过程。编写一加密存储过程，查询指定客户购买产品的情况。并调用该存储过程查询客户编号为“000002”的客户购买情况。

alter proc usp_encryption

(

@Cnum char (6)

)with encryption

as

begin

select

cpxsb .客户编号,

CPXSB .产品编号,

客户名称,

产品名称,

价格,

销售日期 AS 购买日期,

数量 AS 购买数量

from CP ,xss ,CPXSB where cp .产品编号=CPXSB .产品编号 and xss .客户编号=CPXSB .客户编号 and cpxsb .客户编号=@Cnum ;

end

--存储过程调用

exec usp_encryption @Cnum ='000002'

3、带有通配符参数的存储过程。编写一存储过程，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。

alter proc

usp_Tong

@Cname char(30)='%冰箱%'

as

begin

select distinct*

from CPXSB,CP

where cpxsb.产品编号=cp.产品编号and产品名称like@Cname

end

-- 调用

exec usp_Tong

4、带有OUTPUT参数的存储过程。编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用输入和输出参数。并调用该存储过程查询名称为“家电市场”的客户在20XX年购买“洗衣机”的数量。

alter proc usp_output

@Xname char(30),

@Time char(4),

@Cname char(30),

@Count int output

as

begin

select@Count=数量from CP,CPXSB,XSS

where cp.产品编号=CPXSB.产品编号and xss.客户编号=CPXSB.客户编号

and XSS.客户名称=@Xname and convert(char(30),CPXSB.销售日期,21)

like'%'+@Time+'%'

and CP.产品名称=@Cname

end

go

declare@total int

exec usp_output@Xname='家电市场',@Cname='洗衣机

',@Time='2004',@Count=@total output

select isnull(@total,0)as购买数量

5、编写存储过程，对产品销售表进行插入操作，并通过触发器保证插入时，产品编号与 CP 表中的对应字段一致，销售商编号与销售商表中对应字段一致。

create trigger trig_insert

on CPXSB

for insert

as

begin

declare @cpbh char (6)

declare @khbh char (6)

--插入临时表

set @cpbh =(select inserted .产品编号 from inserted )

set @khbh =(select inserted .客户编号 from inserted )

if (@cpbh not in (select 产品编号 from cp ))

begin

print '插入数据中产品编号与CP 表中产品编号不一致'

print @cpbh

rollback transaction -- 回滚

end

else

print '插入一行数据成功'

if (@khbh not in (select 客户编号 from XSS ))

begin

print '插入数据中客户编号与XSS 表中客户编号不一致'

print @khbh

rollback transaction

end

else

print '插入一行数据成功' end

-- 已经受到外键约束，触发器不起作用

insert into CPXSB (产品编号,客户编号,销售日期,数量,销售额)

values ('100004','000009','2008-04-08',3,1000)

6、在CPXSB 上创建一after 触发器，若对产品编号列和客户编号列修改，则给出提示信息，并取消修改操作。

create trigger trig_update

on

CPXSB

after update

as

begin

if update(产品编号)

begin

print'不能修改产品编号'

rollback transaction

end

if update(客户编号)

begin

print'不能修改客户编号'

rollback transaction

end

end

实验八

（1）根据据产品名称，查询该产品的相关信息；（函数名为FU_CP）if exists(select*from sysobjects where name='FU_CP'and

type='FN')

drop function FU_CP

go

-- 内联函数，返回的是一个表查询

create function dbo.FU_CP(@Cname char(30))

returns table

as

return(

select*from CP where产品名称=@Cname

)

go

（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。

-- 内嵌表值函数

create function dbo.FU1_CPXS(@year int,@quarter

int,@Cname char(30))

returns table

as

return

(select产品名称,sum(数量)as销售数量,sum(销售额)as销售金额

from CPXSB,CP

where cp.产品编号=cpxsb.产品编号

and DATEPART(YEAR,销售日期)=@year

and DATEPART(QUARTER,销售日期)=@quarter

and CP.产品名称=@Cname

group by产品名称

)

go

select*from dbo.FU1_CPXS(2004,1,'洗衣机')

go

-- 多语句表值函数

create function FU2_CPXS(@year int,@quarter int,@Cname

char(30))

returns@xssj table

(产品名称char(30),

销售数量int,

销售金额float

)

as

begin

insert into@xssj

select cp.产品名称,SUM(数量)as销售数量,SUM(销售额)as销售总额

from CPXSB,CP

where CPXSB.产品编号=cp.产品编号

and cp.产品名称=@Cname

and DATEPART(YEAR,销售日期)=@year

and DATEPART(quarter,销售日期)=@quarter

group by产品名称

return

end

go

（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU3_CPXS）

create function FU3_CPXS(@year int,@quarter int,@Xname

char(30))

returns table

as

return

(

select产品名称,SUM(数量)as采购数量,SUM(销售额)as总金额

from CPXSB,XSS,CP

where CPXSB.客户编号=XSS.客户编号

and cp.产品编号=cpxsb.产品编号

and客户名称=@Xname

and DATEPART(year,销售日期)=@year

and DATEPART(quarter,销售日期)=@quarter

group by产品名称

)

2、函数的调用

（1）对函数FU_CP，查询产品名称为“MP3”的产品情况；

select*from dbo.FU_CP('冰箱')

（2）对函数 FU1_CPXS，查询20XX 年第3季度彩色电视机的销售数量和销售金额；

select*from dbo.FU1_CPXS(2004,3,'洗衣机')

（3）对函数 FU2_CPXS，查询20XX年第1季度洗衣机的销售数量和销售金额select*from FU2_CPXS(2004,1,'洗衣机')

（4）对函数FU3_CPXS，查询广电公司20XX年第1季度销售的产品名称、销售数量和销售金额。

select*from FU3_CPXS(2004,1,'广电公司')

三、实验小结

本文来源：https://www.bwwdw.com/article/im4e.html