高级数据库技术
数据的并发控制#
事务#
事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
要么全做,要么全不做
转帐过程就是一个事务。
它需要两条 UPDATE 语句来完成,这两条语句是一个整体,如果其中任一条出现错误,则整个转帐业务也应取消,两个帐户中的余额应恢复到原来的数据,从而确保转帐前和转帐后的余额不变,即都是 1001 元。
-- T-SQL使用下列语句来管理事务:
TRANSACTION -- 开始事务
TRANSACTION -- 提交事务
ROLLBACK TRANSACTION -- 回滚(撤销)事务:
-- 一旦事务提交或回滚,则事务结束。
-- 判断某条语句执行是否出错:
@@ERROR; -- 使用全局变量
@@ERROR -- 只能判断当前一条T-SQL语句执行是否有错,为了判断事务中所有T-SQL语句是否有错,我们需要对错误进行累计;如:
SET @errorSum=@errorSum+@@error
ACID 属性:#
- 原子性(Atomicity)事务是一个完整的操作。事务的各步操作是不可分的(原子的);要么都执行,要么都不执行
- 一致性(Consistency)当事务完成时,数据必须处于一致状态
- 隔离性(Isolation)对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务
- 永久性(Durability)事务完成后,它对数据库的修改被永久保持,事务日志能够保持事务的永久性
事务的分类#
- 显示事务:用 BEGIN TRANSACTION 明确指定事务的开始,这是最常用的事务类型。
- 隐性事务:通过设置 SET IMPLICIT_TRANSACTIONS ON 语句,将隐性事务模式设置为打开,下一个语句自动启动一个新事务。当该事务完成时,再下一个 T-SQL 语句又将启动一个新事务
- 自动提交事务:这是 SQL Server 的默认模式,它将每条单独的 T-SQL 语句视为一个事务,如果成功执行,则自动提交;如果错误,则自动回滚
实例:使用事务解决银行转账问题#
BEGIN TRANSACTION
/*--定义变量,用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT
SET @errorSum=0 --初始化为0,即无错误
/*--转帐:张三的帐户少1000元,李四的帐户多1000元*/
UPDATE bank SET currentMoney=currentMoney-1000
WHERE customerName='张三'
SET @errorSum=@errorSum+@@error
UPDATE bank SET currentMoney=currentMoney+1000
WHERE customerName='李四'
SET @errorSum=@errorSum+@@error --累计是否有错误
IF @errorSum<>0 --如果有错误
BEGIN
print '交易失败,回滚事务'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
print '交易成功,提交事务,写入硬盘,永久的保存'
COMMIT TRANSACTION
END
GO
print '查看转帐事务后的余额'
SELECT * FROM bank
GO
并发操作对数据的不一致性#
并发操作带来的三个问题#
- 丢失修改
- 污读
余额应该为 1100 元才对!请看 T6 时间点,事务 A 此时查询余额为 900 元,这个数据就是脏数据,它是事务 A 造成的,明显事务没有进行隔离,渗过来了,乱套了。 - 不可重读
事务 A 其实除了查询了两次以外,其他什么事情都没有做,结果钱就从 1000 变成 0 了,这就是重复读了。其实这样也是合理的,事务 B 提交了事务,数据库将结果进行了持久化,事务 A 再次读取自然就发生了变化。
并发操作破坏了事务的隔离性
封锁及其产生问题的解决#
封锁的类型#
-
排他锁(写锁,X 锁 exclusive lock)
-
共享锁(读锁,S 锁 share lock)
矩阵相容锁
T1\T2 X S - X N N Y S N Y Y - Y Y Y 总结,如果 R 上加了 X 锁后,就不能再加任何锁!如果 R 上加了 S 锁后,只能再加 S 锁而不能再加 X 锁!
三级封锁协议#
-
一级封锁协议
事务 T 在修改数据对象前必须对其加 X 锁,直到事务结束才释放。可以解决“丢失修改”的问题 -
二级封锁协议
在一级封锁协议的基础上,另外加上事务 T 在读取数据对象 R 前必须对其加 S 锁,读完后立即释放。可以解决“污读”的问题 -
三级封锁协议
对于二级封锁协议当中的读锁,直到事务 T 结束才释放。可以解决“不可重读的问题”
死锁和活锁#
避免活锁:先来先服务算法
预防死锁:
- 一次封锁法
要求每个事务必须一次将所有要使用的数据全部加锁,否则就不能继续执行; - 顺序封锁法
是预先对数据对象规定一个封锁顺序,所有事务都按这个顺序实行封锁。
存储过程#
存储过程的特点#
存储过程与函数的区别#
- 存储过程是预编译的,执行效率比函数高。
- 存储过程可以不返回任何值,也可以返回多个输出变量,但函数有且必须有一个返回值。
- 存储过程必须单独执行,而函数可以嵌入到表达式中,使用更灵活。
创建存储过程#
CREATE PROC[EDURE] procedure_name
[{@parameter data_type}[=default]
[OUT|OUTPUT][READONLY][,…n ]]
[WITH[ENCRYPTION[,…n ]]
AS {<sql_statement>[;][…n ]}[;]
-- procedure_name:存储过程的名称
-- @parameter:存储过程中的参数
-- data_type:参数的数据类型
-- Default:参数的默认值
-- OUTPUT:指示该参数是输出参数
-- READONLY:指示该参数是只读的
-- ENCRYPTION:指示加密存储
-- sql_statement:包含在过程中的一个或多个 T-SQL 语句
实例:学生数据库存储过程#
针对学生课程数据库,编写存储过程,查询大数据专业学生每个学生选课情况,包括学号,姓名,课程名,查询结果使用@sno 、@sname 和@cname 输出。
create procedure selectcourses
( @sno int output,
@sname varchar(20) output,
@cname varchar(20) output)
as
select @sno =学号,@sname=姓名,@cname=课程名
from 学生,课程,选课
where 学生.学号=选课.学号 and 选课.课程号=课程.课程号 and 所在系=’大数据’
触发器#
触发器的分类:#
-
按照触发事件分类:
- DML 触发器
- DDL 触发器
- 登录触发器
-
按照触发执行方式分类
- AFTER 触发器
- INSTEAD OF 触发器
-
DML 触发器
- INSERT 触发器
- DELETE 触发器
- UPDATE 触发器
触发器的有点及局限性#
- 触发器的优点
- 强化了约束的功能
- 可以跟踪数据变化
- 支持级联运行
- 可以调用存储过程
- 触发器的局限性
- 触发器性能通常比较低
- 不恰当的使用触发器容易造成数据库维护困难。
触发器的工作原理#
- DML 触发器
-
INSERT 触发器
当对表进行 INSERT 操作时,INSERT 触发器被激发,新的数据行被添加到创建触发器的表和 Insert 表。
-
DELETE 触发器
对表进行 DELETE 操作时,DELETE 触发器被激发,系统从被影响的表中将删除的行放入 Deleted 表中。
-
UPDATE 触发器
当执行 UPDATE 操作时,UPDATE 触发器被激活。触发器将原始行移入 Deleted 表中,把更新行插入到 Inserted 表中。
实例#
在某校教务管理系统中有一个 stu 数据库。
(1)院系 dept(学院名称 Dname,学院人数 Dcount)
(2)学生 student(学号 sno,姓名 sname,性别 ssex,年龄 sage,所在系 sdept,)
(3)课程 course(课程号 cno,课程名 cname,学分 ccredit)
(4)选课 sc(学号 sno,课程号 cno,成绩 grade)
- 创建一个触发器,禁止修改 student 表中的姓名,并在客户端显示“不能修改姓名”。
CREATE TRIGGER up_stu_sname
ON student
FOR update
AS begin
if update(sname)
begin
print'不能修改姓名'
rollback transaction
end
end
- 定义一个允许用户一次只删除一个学生信息的触发器。
CREATE TRIGGER del_stu
ON student
FOR delete
AS begin
if(select count(*) from deleted) > 1
begin
print'删除操作不允许一次删除多条数据'
rollback transaction
end
end
- 创建一个触发器,实现删除学生信息时,级联删除该学生的选课信息。
CREATE TRIGGER del_stu_cascade
ON student
INSTRAD OF delete
AS begin
DELETE SC
FROM SC, DELETED
WHERE SC.SNO = DELETED.SNO
DELETE STUDENT
FROM STUDENT, DELETED
WHERE STUDENT.SNO = DELETED.SNO
end
软件学院开设了一门“数据库系统概论”选修课,由于教室容量有限,这个班的人数不能超过 60 人。
create trigger insert_sc on sc
for insert
as
if (select count(*)
from sc,course,inserted
where sc.cno=course.cno and inserted.cno=sc.cnoand cname=
'数据库系统概论' )>60 begin
print '人数超了'
rollback transaction
end
在 Lend 表上创建 DELETE 触发器,实现如下功能,如果有图书正在借出,则不允许删除。
CREATE TRIGGER tri_Lend_D ON Lend AFTER DELETE
AS BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM deleted
WHERE ReturnTime IS Null)
BEGIN
PRINT '有图书有被借出,不能删除!'
ROLLBACK TRANSACTION --回滚事务,撤销该删除操作
END
END
数据库故障及恢复#
非预期的事务故障#
事务内部更多的故障是非预期的,是不能由应用程序处理的。
- 运算溢出
- 并发事务发生死锁而被选中撤销该事务
- 违反了某些完整性限制而被终止等
事务故障意味着
- 事务没有达到预期的终点(COMMIT 或者显式的 ROLLBACK)
- 数据库可能处于不正确状态。
事务故障的恢复:事务撤消(UNDO)
- 强行回滚(ROLLBACK)该事务
- 撤销该事务已经作出的任何对数据库的修改,使得该事务象根本没有启动一样
系统故障#
系统故障称为软故障,是指造成系统停止运转的任何事件,使得系统要重新启动。
- 特定类型的硬件错误(如 CPU 故障)
- 操作系统故障
- 数据库管理系统代码错误
- 系统断电
系统故障的影响
- 整个系统的正常运行突然被破坏
- 所有正在运行的事务都非正常终止
- 内存中数据库缓冲区的信息全部丢失
- 不破坏数据库
系统故障的恢复
- 撤销所有未完成的事务
- 重做所有已提交的事务
介质故障#
介质故障称为硬故障,指外存故障
- 磁盘损坏
- 磁头碰撞
- 瞬时强磁场干扰
介质故障破坏数据库或部分数据库,并影响正在存取这部分数据的所有事务
介质故障比前两类故障的可能性小得多,但破坏性大得多
计算机病毒#
- 一种人为的故障或破坏,是一些恶作剧者研制的一种计算机程序
- 可以繁殖和传播,造成对计算机系统包括数据库的危害
计算机病毒已成为计算机系统的主要威胁,自然也是数据库系统的主要威胁
数据库一旦被破坏仍要用恢复技术把数据库加以恢复
恢复机制涉及的关键问题#
-
如何建立冗余数据
- 数据转储(backup)
- 登记日志文件(logging)
-
如何利用这些冗余数据实施数据库恢复
MongoDB#
MongoDB 的安装#
安装在 windows 的 64 位操作系统
1. 下载https://www.mongodb.com/download-center/community
2. 解压
3. 创建服务
`mongod --install --dbpath --logpath`
- 要以管理员身份运行 DOS 窗口,否则创建失败
- 得提前创建数据和日志存放的目录
4. 启动服务
5. 验证是否安装成功
- 在 E:\,创建 mongodb 文件夹,存放解压的 mongodb 文件;
- 在 mongodb 文件夹里,创建 data 目录存放数据库文件,创建 logs 目录,存放数据库日志文件;
- 以管理员身份运行 DOS 窗口,将系统目录改为 E:\,然后再切换到 mongodb\bin 目录
- 输入命令:mongod --install --dbpath e:\mongodb\data --logpath e:\mongodb\logs\mongodb.log
- 查看 windows 服务,是否有 mongodb 服务
- 在 DOS 窗口输入命令:net start mongodb ,启动 mongodb。
- 在 DOS 窗口输入命令:mongo,检验是否安装启动成功
- 在 DOS 窗口输入命令:exit,退出数据库
- 创建服务:`bin/mongod --install --dpath 磁盘路径 --logpath 日志路径`
- 删除服务:`bin/mongod --remove`
- 启动服务:`net start mongodb`(注:service 服务名 restart/stop/start)
- 关闭服务:`net stop mongodb`
MongoDB 的增删查改#
-
查看数据库
show databases
-
选择数据库
use 数据库名
-
查看集合
show collections
-
创建数据集合
db.createCollection('集合名')
-
删除数据库
db.dropDatabase()
增加数据#
语法:db.集合名.insert({data})
{data}为 json 数据
例如:db.c1.insert({uname:"webopenfather", age:18})
使用数据一次性插入多条数据:db.c1.insert([{_id:1, uname:"z3", age:3}, {_id:1, uname:"z4", age:4}, {_id:1, uname:"z5", age:5}])
使用 for 循环语句插入超多数据:
for(var i = 1; i <= 10; i++){
db.c2.insert({uname:"a"+i, age:i})
}
删除数据#
语法:db.集合名.remove(条件,[,是否删除一条])
注意:true 为删除一条数据,false 为删除多条数据
查找数据#
语法:db.集合名.find(条件[,查询的列])
- 设置查询的条件
- 查询所有数据 {} 或者不写
- 查询 age=6 的数据
- 查询 age=6 且性别为男的数据
- 设置查询的列
- 不写 代表查询全部列或字段
- {age:1} 只显示 age 列
- {age:0} 除了 age 列,其他列都显示
注意:不管如何设置,都会显示系统自定义的_id 列
设置查询条件:列值包含比较运算符
运算符 | 作用 |
---|---|
$gt | 大于 |
$gte | 大于等于 |
$lt | 小于 |
$lte | 小于等于 |
$ne | 不等于 |
$in | in |
$nin | not in |
练习 1:查询所有数据 db.集合名.find()
练习 2:查询年龄大于 5 岁的学生 db.集合名.find({age:{$gt:5}})
练习 3:查询年龄是 5 岁、8 岁和 10 岁的数据 db.集合名.find({age:{$in:[5,8,10]}})
练习 4:只看年龄列或者年龄以外的列
只看年龄列:db.集合名.find({},{age:1})
查看年龄以外的列:db.集合名.find({},{age:0})
修改数据#
语法:'db.集合名.update(条件,新数据[,是否新增,是否修改多条])'
- 是否新增:条件匹配不到数据,则插入(true 为插入,false 不插入默认)
- 是否修改多条:匹配成功的数据都修改(true 是,false 否默认)
练习 1:将{uname:”zs1”}改为{uname:”zs2”}db.c3.update({uname:”zs1”},{uname:”zs2”})
该语句是修改数据,而不是替换数据,因此要使用语法修改器
修改器:
修改器 | 作用 |
---|---|
$inc | 递增 |
$rename | 重命名列 |
$set | 修改列值 |
$unset | 删除列 |
练习 2:给{uname:”zs10”}的年龄加 2 岁或减 2 岁db.c3.update({uname:"zs10"},{$inc:{age:2}})
练习 3:修改器综合练习
- 插入数据 db.c4.insert({uname:”神龙教主”,age:888,who:”男”,other:”非国人”});
- 完成需求:
- 神龙教主 改成 webopenfather(修改器:$set)
- age 增加 111 (修改器:$inc)
- who 改字段 sex (修改器:$rename)
- other 删除 (修改器:$unset)
具体操作:
db.c4.update({uname:”神龙教主”},{$set: {uname:”webopenfather”}})
{$inc:{age:111}}
{$rename:{who:sex}}
{$unset{other:true}}
MongoDB 数据库操作#
MongoDB 排序与分页#
db.集合名.find()
.sort({列:1/-1}) -- 排序
.skip(数字) -- 跳过指定的数据量
.limit(数字) -- 限制查询的数据量
.count() -- 统计总数量
排序
语法:db.集合名.find().sort(JSON数据)
说明:键-就是要排序的列或字段;值为 1 升序,-1 降序
按照年龄升序 db.c1.find().sort({age:1})
按照年龄降序 db.c1.find().sort({age:-1})
分页
语法:db.集合名.find().sort().skip(数字).limit(数字)
说明:.skip 跳过指定的数据量(可选),limit 限制查询数据量
1、年龄降序查询 2 条数据:db.集合名.find().sort({age:-1}).skip(0).limit(2)
2、年龄降序跳过 2 条并查询 2 条数据:db.集合名.find().sort({age:-1}).skip(2).limit(2)
MongoDB 聚合查询#
语法:
db.集合名.aggregate([
{管道:{表达式}}
... ] )
.sort({列:1/-1}) -- 排序
常用管道 | 含义 |
---|---|
$group | 将集合中的文档分组,统计结果 |
$match | 过滤数据,只输出符合条件的文档 |
$sort | 聚合数据,进一步排序 |
$skip | 跳过指定文档数 |
$limit | 限制集合数据返回文档数 |
常用表达式 | 含义 |
---|---|
$sum | 总和,$sum:1 同 count 表示统计 |
$avg | 平均值 |
$min | 最小值 |
$max | 最大值 |
练习 1:统计男生、女生各自的总年龄:db.集合名.aggregate([{$group:{_id:”$sex”,rs:{$sum:”$age”}}}]
注意:_id:用来指定分组的列,rs:指定结果
练习 2:统计男生、女生各自的总人数:db.集合名.aggregate([{$group:{_id:”$sex”,rs:{$sum:1}}}])
练习 3:统计学生总人数和平均年龄:
db.集合名.aggregate([{$group:{_id:null,
total_num:{$sum:1},
total_avg:{$avg:”$age”} }}])
练习 4:查询男生、女生人数,并按人数升序排序:
db.集合名.aggregate([ {$group:{_id:”$sex”,
rs:{$sum:1} } },
{$sort:{rs:1}} ])
MongoDB 索引创建与分析#
索引创建
语法:db.集合名.createIndex(索引列[,额外选项])
参数:
索引列的组成:{键 1:1,...键 n:-1}
说明:1-升序,-1 降序,例如:{age:-1}表示创建 age 索引并按升序方式存储数据。
额外选项:设置索引的名称或者唯一索引等
删除索引语法:
集合所有索引全部删除:db.集合名.dropIndexes()
删除集合中指定索引: db.集合名.dropIndex(索引名)
查看索引语法: db.集合名.getIndexes()
练习 1:给 name 添加普通索引:db.c1.createIndex({name:1})
练习 2:删除 name 索引: db.c1.dropIndex(“name_1”)
练习 3:给 name 创建索引并起名为 webopenfather: db.c1.createIndex({name:1},{name:”webopenfather”})
创建复合/组合索引 db.集合名.createIndex({键1:方式,键2:方式})
创建唯一索引 db.集合名.createIndex(索引列,{unique:”列名”})
分析索引
语法:db.集合名.find().explain('executionStats')
MongoDB 权限机制#
创建账号
db.createUser({user:"账号",pwd:"密码",
roles:[{role:"角色",db:"所属数据库"}]
})
角色
- 超级用户角色:root
- 数据库用户角色:read、readWrite
- 数据库管理角色:dbAdmin、userAdmin
- 集群管理角色:clusterAdmin、clusterManager、clusterMonitor、hostManage
开启验证模式
-
添加超级管理员;
mongo use admin db.createUser({user:”admin”,pwd:”admin888”, roles:[{role:”root”,db:”admin”}] })
-
退出卸载服务
bin\mongod --remove
- 重新安装需要输入账号密码的数据库服务(在原安装命令上加--auth)
mongod --install --dbpath 数据路径 --logpath 日志路径 --auth
net start mongodb
- 启动数据库服务,登录 MongoDB 数据库
mongo 服务器IP地址:端口/数据库 -u 用户名 -p 密码
练习:
use shop
db.createUser({user:”shop1”,pwd:”admin888”,
roles:[{role:”read”,db:”shop”}] -- 只读
})
db.createUser({user:”shop2”,pwd:”admin888”,
roles:[{role:”readWrite”,db:”shop”}] -- 读写
})
MongoDB 备份还原#
备份
语法:mongodump -h -port -u -p -d -o
说明:
- -h host 服务器 IP 地址,一般不写,默认本机
- -port 端口(一般不写,默认 27017)
- -u user 用户账号
- -p pwd 密码
- -d database 数据库(不指明数据库,则备份所有数据库)
- -o open 备份到指定目录下(备份前,先建 bak 目录)
练习 1:备份所有数据库
在数据库所在文件夹里,创建 bak 目录,存放数据库备份 mongodump -u admin -p admin888 -o 备份文件及路径
练习 2:备份指定数据库
在数据库所在文件夹里,创建 bak2 目录,存放指定的数据库备份 mongodump -u 数据库用户 -p 密码 -d 数据库名 -o 备份文件及路径
还原
语法:mongorestore -h -port -u -p -d --drop 备份数据目录
说明:
- -h host 服务器 IP 地址,一般不写,默认本机
- -port 端口(一般不写,默认 27017)
- -u user 用户账号
- -p pwd 密码
- -d database 数据库(不指明数据库,则还原所有数据库)
- --drop 先删除数据库再导入数据
练习 1:还原所有数据库:mongorestore -u admin -p admin888 --drop 备份文件所在路径
练习 2:还原指定数据库 mongorestore -u admin -p admin888 -d 数据库 --drop 备份路径
还原 shop 数据库,注意只能使用 shop 数据库的用户还原
Mongoose 的使用#
接连 Mongoose
//一、导入mongoose模块
const mongoose = require('mongoose');
//二、连接数据库
const db = mongoose.createConnection('mongodb://user:pass@localhost:27017/database', { useNewUrlParser: true, useUnifiedTopology: true }, err => {
if (err) {
console.log('-------------------------')
console.log('数据库连接失败', err)
console.log('-------------------------')
return;
}
console.log('数据库连接成功');
})
//三、设置数据模型(声明哪个集合,限制字段个数和字段类型)
const model = db.model('user', {
name: { type: String, default: "username" },
age: Number,
sex: { type: String }
})
// 四、创建实例操作(CURD)
// 增
const insertobj = new model({ name:”www”, age: 18, sex:”男” })//实例化数据
// 方法1:insertobj.save((err) => db.close())
// 方法2:(推荐)
insertobj.save()
.then(res => {
console.log(res)
return res
})
.catch(err => {
console.log('插入失败' + err)
return false
})
// 四、创建实例操作(CURD)
// 删
// 方法1:model.remove/deleteOne/deleteMany(条件对象,(err) =>db.close())
// 方法2:(推荐)
model.deleteOne(条件对象)
.then(res => {
console.log('删除成功')
return res.deleteCount
})
.catch(err => {
console.log('删除失败' + err)
return false
})
// 四、创建实例操作(CURD)
// 修改
// 方法1:model.update/updateOne/updateMany(条件对象,数据对象,(err) =>db.close())
// 方法2:(推荐)
model.updateOne(条件对象, 数据对象)
.then(res => {
console.log('修改成功')
return res.nModified
})
.catch(err => {
console.log('修改失败' + err)
return false
})
// 四、创建实例操作(CURD)
// 查询
// 方法1:model.find/findOne(条件对象,显示的字段数据,(err,result) =>db.close())
// 方法2:(推荐)
model.findOne(条件对象)
.then(res => { return res })
.catch(err => {
console.log(err)
return false
})
SQL 练习#
以下 SQL 语句使用ChatGPT
生成,未经过数据库验证,不保证其准确性!
表:
(1)顾客(编号,姓名,性别,年龄,城市,联系电话)
(2)产品(产品号,产品名,规格,单价,生产日期)
(3)产品销售(顾客编号,产品号,数量,购买日期)
题目:
(1)查询购买了洗发水的顾客的姓名、性别、购买数量。
SELECT 顾客.姓名,
顾客.性别,
产品销售.数量
FROM 顾客
JOIN 产品销售 ON 顾客.编号 = 产品销售.顾客编号
JOIN 产品 ON 产品销售.产品号 = 产品.产品号
WHERE 产品.产品名 = '洗发水';
上述 SQL 语句中使用了 INNER JOIN
连接三张数据表,首先连接了“顾客”和“产品销售”表,然后连接了“产品销售”和“产品”表。通过 WHERE 子句筛选出了产品名为“洗发水”的记录,并选取了对应的顾客姓名、性别和购买数量。
(2)查询购买了所有产品的顾客的编号和姓名。
SELECT 顾客.编号,
顾客.姓名
FROM 顾客
WHERE NOT EXISTS (
SELECT 产品.产品号
FROM 产品
WHERE NOT EXISTS (
SELECT *
FROM 产品销售
WHERE 产品销售.顾客编号 = 顾客.编号
AND 产品销售.产品号 = 产品.产品号
)
);
上述 SQL 语句使用了 NOT EXISTS
子查询来找出购买了所有产品的顾客。首先在外层查询中选取了所有的顾客编号和姓名,然后在 WHERE 子句中使用 NOT EXISTS 子查询,该子查询首先选取所有产品的产品号,然后再使用一个嵌套的 NOT EXISTS 子查询来判断对于每一个产品号,是否存在一个销售记录,其顾客编号为当前外层查询的顾客编号,如果存在则表示该顾客购买了该产品,否则表示该顾客没有购买该产品。只有在所有产品的销售记录都存在的情况下,才会将该顾客的编号和姓名选取出来。
(3)查询购买了超过 5 种产品的顾客编号和姓名。
SELECT 顾客.编号,
顾客.姓名
FROM 顾客
JOIN 产品销售 ON 顾客.编号 = 产品销售.顾客编号
GROUP BY 顾客.编号
HAVING COUNT(DISTINCT 产品销售.产品号) > 5;
上述 SQL 语句中使用了 JOIN
连接“顾客”和“产品销售”两张数据表,然后使用 GROUP BY 语句将记录按顾客编号进行分组,并使用 HAVING 子句来筛选出购买了超过 5 种产品的顾客。HAVING 子句中的 COUNT(DISTINCT 产品销售.产品号)用于计算每个顾客购买的不同产品数量,并将结果与 5 进行比较,只有超过 5 种产品的顾客的编号和姓名才会被选取出来。
(4)创建存储过程,查询产品购买顾客超过 100 位的产品编号和产品名。
CREATE PROCEDURE `sp_find_popular_products`() BEGIN
SELECT 产品.产品号,
产品.产品名
FROM 产品
JOIN 产品销售 ON 产品.产品号 = 产品销售.产品号
GROUP BY 产品.产品号,
产品.产品名
HAVING COUNT(DISTINCT 产品销售.顾客编号) > 100;
END
上述 SQL 语句创建了一个名为 sp_find_popular_products
的存储过程,该存储过程查询购买某个产品的顾客数量超过 100 位的产品编号和产品名。在存储过程中,使用 JOIN 连接“产品”和“产品销售”两张数据表,然后使用 GROUP BY 语句将记录按产品编号和产品名进行分组,并使用 HAVING 子句来筛选出购买某个产品的顾客数量超过 100 位的记录。最后将选取出的产品编号和产品名作为结果返回。
(5)创建 DELETE 触发器,实现删除顾客时,级联删除顾客购买产品的数据
CREATE TRIGGER `trg_delete_customer` BEFORE DELETE ON `顾客` FOR EACH ROW BEGIN
DELETE FROM 产品销售
WHERE 产品销售.顾客编号 = OLD.编号;
END
上述 SQL 语句创建了一个名为 trg_delete_customer
的 DELETE 触发器,该触发器在执行 DELETE 操作之前被触发。当删除“顾客”表中的记录时,该触发器会级联删除与该顾客相关的所有“产品销售”表中的记录。在触发器的操作中,使用 DELETE 语句从“产品销售”表中删除顾客编号为被删除的顾客记录的编号的所有记录。其中,OLD 关键字表示被删除的记录。
咱就是说 都看完了 打个赏呗(/ω\)害羞
作者:龙琰
出处:https://www.cnblogs.com/bellongyan/p/Advanced_Database_End_Period.html
版权:本作品采用「署名-非商业性使用-相同方式共享 4.0 国际」许可协议进行许可。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战