高级数据库技术

数据的并发控制#

事务#

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作

要么全做,要么全不做

转帐过程就是一个事务。

它需要两条 UPDATE 语句来完成,这两条语句是一个整体,如果其中任一条出现错误,则整个转帐业务也应取消,两个帐户中的余额应恢复到原来的数据,从而确保转帐前和转帐后的余额不变,即都是 1001 元。

-- T-SQL使用下列语句来管理事务:
TRANSACTION -- 开始事务
TRANSACTION -- 提交事务
ROLLBACK TRANSACTION -- 回滚(撤销)事务:
-- 一旦事务提交或回滚,则事务结束。

-- 判断某条语句执行是否出错:
@@ERROR; -- 使用全局变量
@@ERROR -- 只能判断当前一条T-SQL语句执行是否有错,为了判断事务中所有T-SQL语句是否有错,我们需要对错误进行累计;如:
SET @errorSum=@errorSum+@@error

ACID 属性:#

  • 原子性(Atomicity)事务是一个完整的操作。事务的各步操作是不可分的(原子的);要么都执行,要么都不执行
  • 一致性(Consistency)当事务完成时,数据必须处于一致状态
  • 隔离性(Isolation)对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务
  • 永久性(Durability)事务完成后,它对数据库的修改被永久保持,事务日志能够保持事务的永久性

事务的分类#

  1. 显示事务:用 BEGIN TRANSACTION 明确指定事务的开始,这是最常用的事务类型。
  2. 隐性事务:通过设置 SET IMPLICIT_TRANSACTIONS ON 语句,将隐性事务模式设置为打开,下一个语句自动启动一个新事务。当该事务完成时,再下一个 T-SQL 语句又将启动一个新事务
  3. 自动提交事务:这是 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

并发操作对数据的不一致性#

并发操作带来的三个问题#

  1. 丢失修改
  2. 污读
    数据污读案例
    余额应该为 1100 元才对!请看 T6 时间点,事务 A 此时查询余额为 900 元,这个数据就是脏数据,它是事务 A 造成的,明显事务没有进行隔离,渗过来了,乱套了。
  3. 不可重读
    不可重读的案例
    事务 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 锁!

三级封锁协议#

  1. 一级封锁协议
    事务 T 在修改数据对象前必须对其加 X 锁,直到事务结束才释放。可以解决“丢失修改”的问题

  2. 二级封锁协议
    在一级封锁协议的基础上,另外加上事务 T 在读取数据对象 R 前必须对其加 S 锁,读完后立即释放。可以解决“污读”的问题

  3. 三级封锁协议
    对于二级封锁协议当中的读锁,直到事务 T 结束才释放。可以解决“不可重读的问题”

死锁和活锁#

避免活锁:先来先服务算法

预防死锁:

  • 一次封锁法
    要求每个事务必须一次将所有要使用的数据全部加锁,否则就不能继续执行;
  • 顺序封锁法
    是预先对数据对象规定一个封锁顺序,所有事务都按这个顺序实行封锁。

存储过程#

存储过程的特点#

存储过程的特点

存储过程与函数的区别#

  1. 存储过程是预编译的,执行效率比函数高。
  2. 存储过程可以不返回任何值,也可以返回多个输出变量,但函数有且必须有一个返回值。
  3. 存储过程必须单独执行,而函数可以嵌入到表达式中,使用更灵活。

创建存储过程#

语法格式

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 所在系=’大数据’

触发器#

img

触发器的分类:#

  1. 按照触发事件分类:

    • DML 触发器
    • DDL 触发器
    • 登录触发器
  2. 按照触发执行方式分类

    • AFTER 触发器
    • INSTEAD OF 触发器
  3. DML 触发器

    • INSERT 触发器
    • DELETE 触发器
    • UPDATE 触发器

触发器的有点及局限性#

  1. 触发器的优点
    • 强化了约束的功能
    • 可以跟踪数据变化
    • 支持级联运行
    • 可以调用存储过程
  2. 触发器的局限性
    • 触发器性能通常比较低
    • 不恰当的使用触发器容易造成数据库维护困难。

触发器的工作原理#

  • DML 触发器

img

  • 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)

  1. 创建一个触发器,禁止修改 student 表中的姓名,并在客户端显示“不能修改姓名”。
CREATE TRIGGER up_stu_sname
ON student
FOR update
AS begin
   if update(sname)
   begin
      print'不能修改姓名'
      rollback transaction
   end
end
  1. 定义一个允许用户一次只删除一个学生信息的触发器。
CREATE TRIGGER del_stu
ON student
FOR delete
AS begin
   if(select count(*) from deleted) > 1
   begin
      print'删除操作不允许一次删除多条数据'
      rollback transaction
   end
end
  1. 创建一个触发器,实现删除学生信息时,级联删除该学生的选课信息。
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 故障)
  • 操作系统故障
  • 数据库管理系统代码错误
  • 系统断电

系统故障的影响

  • 整个系统的正常运行突然被破坏
  • 所有正在运行的事务都非正常终止
  • 内存中数据库缓冲区的信息全部丢失
  • 不破坏数据库

系统故障的恢复

  • 撤销所有未完成的事务
  • 重做所有已提交的事务

介质故障#

介质故障称为硬故障,指外存故障

  • 磁盘损坏
  • 磁头碰撞
  • 瞬时强磁场干扰

介质故障破坏数据库或部分数据库,并影响正在存取这部分数据的所有事务

介质故障比前两类故障的可能性小得多,但破坏性大得多

计算机病毒#

  • 一种人为的故障或破坏,是一些恶作剧者研制的一种计算机程序
  • 可以繁殖和传播,造成对计算机系统包括数据库的危害

计算机病毒已成为计算机系统的主要威胁,自然也是数据库系统的主要威胁

数据库一旦被破坏仍要用恢复技术把数据库加以恢复

恢复机制涉及的关键问题#

  1. 如何建立冗余数据

    • 数据转储(backup)
    • 登记日志文件(logging)
  2. 如何利用这些冗余数据实施数据库恢复

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(条件[,查询的列])

  1. 设置查询的条件
    • 查询所有数据 {} 或者不写
    • 查询 age=6 的数据
    • 查询 age=6 且性别为男的数据
  2. 设置查询的列
    • 不写 代表查询全部列或字段
    • {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(条件,新数据[,是否新增,是否修改多条])'

  1. 是否新增:条件匹配不到数据,则插入(true 为插入,false 不插入默认)
  2. 是否修改多条:匹配成功的数据都修改(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:修改器综合练习

  1. 插入数据 db.c4.insert({uname:”神龙教主”,age:888,who:”男”,other:”非国人”});
  2. 完成需求:
    • 神龙教主 改成 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

开启验证模式

  1. 添加超级管理员;

    mongo
       use admin
       db.createUser({user:”admin”,pwd:”admin888”,
          roles:[{role:”root”,db:”admin”}]
    })
    
  2. 退出卸载服务

bin\mongod --remove

  1. 重新安装需要输入账号密码的数据库服务(在原安装命令上加--auth)

mongod --install --dbpath 数据路径 --logpath 日志路径 --auth

net start mongodb

  1. 启动数据库服务,登录 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 国际」许可协议进行许可。

posted @   belhomme  阅读(57)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
more_horiz
keyboard_arrow_up light_mode palette
选择主题
menu
点击右上角即可分享
微信分享提示