数据库案例分析
--职工（职工号，姓名，性别，年龄，电话，EMAIL）
--在职工表中插入记录（Z01，唐僧，男，
--35，88888888，88@88.book）
INSERT INTO 职工
V ALUES('Z01','唐僧','男',35,'88888888','88@88.book')
--图书类别（类别编号，类别名称）
--图书（图书号，类别，书名，出版社代号，库存量，价格）ALTER TABLE 图书ADD CONSTRAINT C1 FOREIGN KEY(类别) REFERENCES 图书类别(类别编号)
--客户（编号，名称，信用度，联系人，联系电话）
SELECT * FROM 客户WHERE 名称LIKE '浙江___'
--常用数学函数
SUM
A VG
COUNT
MAX
MIN
GROUP BY
R(A,B,C,D,E,G,H,I)
F={AB-->C,A-->DE,B-->GH,D-->I} R1(ABC) R2(ADE) R3(BGH) R4(DI)
题目：
唐僧师徒四人从西天取经回来，决定成立一图书销售公司，为了提高销售效率，经研究决定采用数据库技术，建立一数据库如下：
职工（职工号，姓名，性别，年龄，电话，EMAIL）
出版社（出版社代号，名称，地址，联系电话）
图书类别（类别编号，类别名称）
图书（图书号，类别，书名，出版社代号，库存量，价格）
客户（编号，名称，信用度，联系人，联系电话）
销售单（单号，销售职工号，客户编号，销售时间）
销售明细（销售单号，图书号，销售量，折扣，金额）
--触发器，当删除销售单时，若发现销售明细记录多于一条时，撤销删除操作
CREATE TRIGGER trg1
ON 销售单
AFTER DELETE
AS
BEGIN
IF (SELECT COUNT(*) FROM 销售明细WHERE 销售单号IN(SELECT 单号FROM DELETED )>1
ROLLBACK TRANSACTION
END
--SQL命令动词
--数据定义：CREATE ALTER DROP
--数据操纵：SELECT INSERT UPDA TE DELETE
--数据控制：GRANT REVOKE DENY
--封锁协议：一级，二级，三级
--范式：1NF,2,3,BC
R(A,B,C) F={AB→C,C→A}
R1(A,B) R2(B,C)
AB BC
2NF? 3NF? BCNF?
非主属性对码的依赖
--闭包
R1 n R2→R1-R2
R(A,B,C) F={A→B,B→C}
F1={A→C,AB→C,ABC→C}
最小函数依赖集
现要求数据库开发商通过SQL语句解决以下问题：
1、在职工表中插入记录（Z01，唐僧，男，35，88888888，88@88.book）
2、在出版社中插入记录（C01，铁扇出版社，北京故宫路110号，110110）
3、修改表“图书”，添加外码约束C1，实现“图书”表中“类别”字段与“图书类别”表
中“类别编号”字段关联
4、查找客户名称中包含“浙江”的个人信息
5、查找“类别名称”为“精装版”的图书信息
SELECT * FROM 图书类别,图书WHERE 类别名称=’精装版’ AND 类别=类别编号改写成：
SELECT * FROM 图书WHERE 类别IN(SELECT 类别编号FROM 图书类别WHERE 类别名称=’精装版’)
--特殊的比较运算符：BETWEEN….AND….
6、查找库存量<10的图书信息
SELECT * FROM 图书WHERE 库存量<10
--统计库存量超过1000的各类别图书的库存量，并按库存量从高到低进行排序
SELECT 类别,SUM(库存量) FROM 图书GROUP BY 类别
HA VING SUM(库存量)>1000
ORDER BY SUM(库存量) DESC
7、查找职工姓名为“孙悟空”的销售明细
职工（职工号，姓名，性别，年龄，电话，EMAIL）
出版社（出版社代号，名称，地址，联系电话）
图书类别（类别编号，类别名称）
图书（图书号，类别，书名，出版社代号，库存量，价格）
客户（编号，名称，信用度，联系人，联系电话）
销售单（单号，销售职工号，客户编号，销售时间）
销售明细（销售单号，图书号，销售量，折扣，金额）
--连接查询
SELECT 销售明细.* FROM 销售明细,销售单,职工WHERE 销售明细.销售单号=销售单.单号AND 销售单.销售职工号=职工.职工号AND 姓名=’孙悟空’
--子查询
SELECT * FROM 销售明细WHERE 销售单号IN(SELECT 单号FROM 销售单WHERE 销售职工号IN(SELECT 职工号FROM 职工WHERE 姓名=’孙悟空’))
8、用UPDATE语句更新“销售明细”中的金额字段，表达式为：金额=销售量*价格*（1-
折扣）
UPDATE 销售明细SET 金额=销售量*价格*(1-折扣)
9、按销售职工号分组统计销售总金额，并按销售总金额进行降序排序
销售单（单号，销售职工号，客户编号，销售时间）
销售明细（销售单号，图书号，销售量，折扣，金额）
SELECT 销售职工号,SUM(金额) FROM 销售明细,销售单WHERE 销售明细.销售单号=销售单.单号GROUP BY 销售单.销售职工号
10、通过连接查询或子查询查找：职工姓名，客户姓名，图书名，销售量，金额。
职工（职工号，姓名，性别，年龄，电话，EMAIL）
出版社（出版社代号，名称，地址，联系电话）
图书类别（类别编号，类别名称）
图书（图书号，类别，书名，出版社代号，库存量，价格）
客户（编号，名称，信用度，联系人，联系电话）
销售单（单号，销售职工号，客户编号，销售时间）
销售明细（销售单号，图书号，销售量，折扣，金额）
SELECT 姓名,名称,书名,销售量,金额FROM 职工,图书,客户,销售单,销售明细
WHERE 职工.职工号=销售单.销售职工号AND 图书.图书号=销售明细.图书号AND 客户.编号=销售单.客户编号AND 销售单.单号=销售明细.销售单号
11、删除名称为“红孩儿出版社”的出版社记录。
DELETE FROM 出版社WHERE 名称=’红孩儿出版社’
触发器与存储过程
1、在删除销售单同时删除其下的销售明细,怎么办？
2、在销售明细表插入数据、修改或删除数据时要进行以下操作（1）以“金额＝销售量＊价格＊（折扣/10）”对金额进行自动计算（其中的价格来自“图书”表）；（2）销售量发生变化后，“图书”表中的库存量也要相应发生变化，怎么办？
触发器实例二：
在学生成绩表中，要求修改成绩列时进行修改过程跟踪。通过触发器实现。
3、建立一存储过程，实现销售明细数据的插入操作，要对拟输入数据进行控制，要求销售单号、图书号不能为空，且（销售单号＋图书号）不能重复），并将操作结果状态通过输出参数返回。
--建立一存储过程，实现销售明细数据的插入操作，要对拟输入数据进行控制，要求销售单号、图书号不能为空，且（销售单号＋图书号）不能重复），并将操作结果状态通过输出参数返回。
CREATE PROCEDURE pro_IN_XSMX
(
@XSDH NVARCHAR(10),
@TSH NVARCHAR(10),
@XSL INT,
@ZK FLOAT,
@ZXJG CHAR(20)OUTPUT
)
AS
BEGIN
IF@XSDH=''
BEGIN
SET@ZXJG='销售单号为空！'
RETURN
END
IF@TSH=''
BEGIN
SET@ZXJG='图书号为空！'
RETURN
END
IF (SELECT COUNT(*)FROM销售明细
WHERE销售单号=@XSDH AND图书号=@TSH)>=1 BEGIN
SET@ZXJG='记录重复，请检查!'
RETURN
END
INSERT INTO销售明细(销售单号,图书号,销售量,折扣) VALUES(@XSDH,@TSH,@XSL,@ZK)
SET@ZXJG='插入成功!'
END
1、在职工表中插入记录（Z01，唐僧，男，35，88888888，
88@88.book）
答案：
（1）insert into 职工（职工号，姓名，性别，年龄，电话，EMAIL）values(Z01，唐僧，男，35，88888888，88@88.book）（2）insert into 职工（职工号，姓名，性别，年龄，电话，EMAIL）values(‘Z01’，’唐僧’，’男’，35，’88888888’，’88@88.book’）
？
3、修改表“图书”，添加外码约束C1，实现“图书”表中“类别”字段与“图书类别”表中“类别编号”字段关联答案：
（1）UPDATE 图书….
（2）ALTER TABLE 图书ADD C1 WHERE…
（3）ALTER TABLE 图书ADD CONSTRAINT C1 FOREIGN KEY(类别) REFERENCES 图书类别(类别编号) 5、查找“类别名称”为“精装版”的图书信息
（1）SELECT ‘精装版’ FROM 图书类别
（2）SELECT 图书.* FROM 图书, 图书类别WHERE 类别名称=’精装版’ AND 图书.类别=图书类别.类别编号
通过子查询提高效率：
SELECT * FROM 图书WHERE 类别IN (SELECT 类别编
号FROM 图书类别WHERE 类别名称=’精装版’)
7、查找职工姓名为“孙悟空”的销售明细
（1）SELECT * FROM 销售明细WHERE 姓名=’孙悟空’ ???
用子查询完成
SELECT * FROM 销售明细WHERE 销售单号IN (SELECT 单号FROM 销售单WHERE 销售职工号IN (SELECT 职工号FROM 职工WHERE 姓名=’孙悟空’)) 9、按销售职工号分组统计销售总金额，并按销售总金额大小进行排序
问题：未用GROUP BY ，ORDER BY，SUM或之一SELECT 销售职工号,SUM(金额)
FROM 销售单,销售明细
WHERE 销售单.单号=销售明细.销售单号
GROUP BY 销售职工号
ORDER BY SUM(金额) DESC
10、通过连接查询或子查询查找：职工姓名，客户姓名，图书名，销售量，金额。
问题：连接表不全，找不到实际数据
SELECT 姓名,名称,书名,销售量,金额
FROM 职工,客户,图书,销售明细,销售单
WHERE 销售明细.销售单号=销售单.单号AND 销售单.销售职工号=职工.职工号AND 销售单.客户编号=客户.编号AND 销售明细.图书号=图书.图书号