实验12：综合.doc

GDOU-B-11-112
广东海洋大学学生实验报告书
实验名称
实验12：综合
课程名称
数据库原理及应用
成绩

学院（系）
软件学院
专业
软件工程
班级

学生姓名

学号

实验地点

实验日期

实验目的：
综合使用所学知识完成实验。
实验内容

数据查询
1. 查询Toys表，按玩具类别（cCategoryid）统计每一类玩具的平均重量。
SELECT AVG(siToyWeight * 1.0) FROM Toys GROUP BY cCategoryId;
2. 查询Country表，按首字母相同来分组统计，统计首字母相同的国家名称数目。查询的部分结果示例如下：

SELECT LEFT(cCountry, 1), COUNT(*) FROM Country GROUP BY LEFT(cCountry, 1);

3. 对于Orderdetail表，查询订购了相同玩具的订单编号。
SELECT cToyId, cOrderNo FROM OrderDetail
WHERE cToyId IN (
SELECT cToyId FROM OrderDetail
GROUP BY cToyId
HAVING COUNT(cOrderNo) >= 2
) ORDER BY cToyId;

4. 对于Orders表，统计使用最频繁的运输模式（cShippingmodeid）。
SELECT cShippingModeId, COUNT(*)
FROM Orders
GROUP BY cShippingModeId
ORDER BY COUNT(*) DESC;

5. 对于orders表，查询运输和包装费用占整个花费比重最高的订单。
SELECT * FROM Orders
WHERE (mShippingCharges + mGiftWrapCharges) / mTotalCost >= ALL (
SELECT (mShippingCharges + mGiftWrapCharges) / mTotalCost FROM Orders
)

6. 编写一个存储过程，对于PickofMonth表操作，接收输入型参数为年份，输出参数值为该年中销售额最高的一个季度。
CREATE PROC getLargestSeason @Year INT = NULL, @Season INT OUT
AS
BEGIN
IF (@Year IS NULL)
BEGIN
PRINT '年份不能为空'
RETURN 0
END

SELECT @Season = (siMonth / 4) + 1 FROM PickOfMonth
GROUP BY (siMonth / 4) + 1
HAVING SUM(iTotalSold) >= ALL (
SELECT SUM(iTotalSold) FROM PickOfMonth
WHERE iYear = @Year
GROUP BY ((siMonth / 4) + 1)
)
END

DECLARE @test INT
EXEC getLargestSeason 2001, @test OUTPUT
SELECT @test

7. 编写一个触发器，实现Toybrand表和toys表有关cBrandid列的级联删除功能。
CREATE TRIGGER trigDeleteBrandId ON ToyBrand
AFTER DELETE AS
BEGIN
DECLARE @id CHAR(3)
SELECT @id = cBrandId FROM DELETED;

DELETE FROM Toys WHERE cBrandId = @id;
END

数据定义，创建如下的表格Users

列名
描述
数据类型
约束
Userid
用户序号
int (自动增长的列)

UserName
用户名
varchar（20）

UserPwd
用户密码
varchar（20）

Role
角色类别
int

Email
电子邮件
varchar（50）

Info
信息
text

CREATE TABLE Users (
UserId INT IDENTITY(1,1),
UserName VARCHAR(20),
UserPwd VARCHAR(20),
Role INT,
Email VARCHAR(50),
Info TEXT
)

1． 创建一个存储过程，接受用户名和密码，判断输入的用户名和密码是否错误，如果错误返回状态1，并返回输出型参数消息‘用户名或密码错误’，否则返回状态0。
CREATE PROC procCheckInfo @Name VARCHAR(20), @Pwd VARCHAR(20)
AS
BEGIN
IF NOT EXISTS(SELECT * FROM Users WHERE UserName = @Name AND UserPwd = @Pwd)
BEGIN
PRINT '用户名或密码错误';
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
END

2． 创建一个触发器，当对Users表插入一行时，需要进行如下判断：用户名不能已经存在，用户名长度需要超过8个字符，密码需要包含字母和数字字符并且长度不小于8，否则禁止插入。
CREATE TRIGGER trigUserInfo ON Users
AFTER INSERT AS
BEGIN
DECLARE @UserName VARCHAR(20);
DECLARE @UserPwd VARCHAR(20);
SELECT @UserName = UserName, @UserPwd = UserPwd FROM INSERTED;
IF (SELECT COUNT(*) FROM Users WHERE UserName = @UserName) >= 2
BEGIN
PRINT '用户名已经存在';
ROLLBACK TRAN;
RETURN;
END

IF (LEN(@UserName) <= 8)
BEGIN
PRINT '用户名长度需要超过8位';
ROLLBACK TRAN;
RETURN;
END

IF (LEN(@UserPwd) < 8)
BEGIN
PRINT '密码长度不得少于8';
ROLLBACK TRAN;
RETURN;
END

IF NOT (@UserPwd LIKE '%[A-Za-z]%' AND @UserPwd LIKE '%[0-9]%')
BEGIN
PRINT '密码需要包含字母和数字字符';
ROLLBACK TRAN;
RETURN;
END
END

3． 创建一个存储过程，判定如果今天是一个月的第一天，则打印消息“Today is the first day of the month”,并且返回状态值1，否则打印错误消息并返回状态0。
CREATE PROC isFirstDay AS
BEGIN
IF (DATEPART(DD, GETDATE()) = 1)
BEGIN
PRINT 'Today is the first day of the month';
RETURN 1;
END
ELSE
BEGIN
PRINT 'Today is not the first day of the month';
RETURN 0;
END
END

4． 创建一个触发器，以实现保证对User表的增删改操作只在工作日进行。
CREATE TRIGGER trigTimeLimit ON Users
AFTER INSERT, DELETE, UPDATE AS
BEGIN
IF NOT ((SELECT DATEPART(DW, GETDATE())) BETWEEN 2 AND 6)
BEGIN
PRINT '增删改操作只在工作日进行'
ROLLBACK TRAN;
END
END

钟子期听懂了俞伯牙的琴音——“巍巍乎若高山，荡荡乎若流水”,俞伯牙视其为知音。钟子期死后，面对江边一抔黄土，俞伯牙发出“此曲终兮不复弹，三尺瑶琴为君死”的感慨，摔琴而去，从此，高山流水，知音难觅。
　　红楼里，宝钗与黛玉皆爱宝玉，宝钗看重功名，常拿一些伦理纲常来压制他的不羁与顽劣，黛玉却从未提及这些，因她懂得他的心性，她说“ 你既为我之知己，自然我亦是你之知己”,造化弄人，木石前缘虽是虚空一场，却怀金悼玉，梦萦千古，今日读来依然荡气回肠！
　　不是所有的相遇都可以相知，不是所有的相知都可以永恒。生命里，我们只愿结交那些心性相宜的人，统一的语言，相同的志趣，将彼此的心灵拉近，一份懂得，不言不语，却在默契里滋生。
　　懂得，是两颗心的对望，潜生一种心灵感应，不发一言，便可知会。一声懂得，没有千言万语，却可以令人眸中含泪，心中蕴暖。
　　这世间太多人情薄凉，你是否觉得，有一个真正懂你的人，是一种幸福与慰藉呢？茫茫人海，你不孤单，有人愿与你同运命，共风雨，如此，多好！
　　风懂云的情怀，它，轻轻的吹送，云姿更加漫妙；雪懂梅的寒傲，它，悄悄的绽放，梅骨愈加清奇；泉懂山的伟岸，它，静静的流淌，山林更为葱茂；雨懂花的心思，它，无声的洒落，花香尤为清绝……
　　杏花疏影小楼边，一腔笛韵委婉悠扬；山亭古寺四月间，深涧桃花兀自娇娆；暗香疏影黄昏后，东篱素菊暗香盈袖；柴门冬雪夜归人，红泥火炉绿蚁新醅……若懂得，景与物，也相宜。
彷徨失意时，一句懂得，是严冬的一场花开春暖，茫然无助时，一句懂得， 是酷暑的一阵清凉细雨，心与心的贴近，皆因一个“懂得”而欣慰，美好。