[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NIr0tYNc-1680505151717)(./assets/XuanZiShare_QQ_3336392096.jpg)]
玄子Share-BCSP助学手册之数据库开发
前言：
此文为玄子，学习 BCSP 一二期后整理的文章，文中对知识点的解释仅为个人理解，源码均可直接复制运行
配套PPT，站点源码，等学习资料请加文章封面联系方式
针对课程的一些调整：
- 如果你当前正在学习
BCSP S1课程的C sharp语言和SQL Server数据库技术并不影响阅读浏览这篇文章，SQL Server与MySQL的绝大部分SQL语法都是相通的，关于SQL语法我均写了详细的介绍- 一期
C sharp语言和SQL Server数据库技术与二期JAVA开发之JAVA数据库编程这两本书的相似度较高，所以针对数据库编程，只写了二期的JAVA开发之JAVA数据库编程- 这篇文章与之前的
玄子Share-BCSP助学手册之JAVA开发以及玄子Share-BCSP助学手册之前端开发属于同一系列Java开发之Java数据库编程这本书所学，几乎就是以后工作的日常操作，所以这篇文章，会加入更多关于我对数据库及JDBC的理解，相对前两篇文章，会比较啰嗦，请谅解- 文章演示的数据库为BCSP配套数据库
hospital文章第八章附件有源文件，可直接复制运行- 文章的部分知识相对课程有增改，以课本为准
- 课程中部分软件涉及版权无法展示安装教程，可加封面联系方式获取软件和安装教程
目录
文章目录
- 玄子Share-BCSP助学手册之数据库开发
- 目录
- @[toc]
- 一、课程软件的安装与卸载
- 1.1 MySQL 版本选择与安装方式
- 1.1.1 MySQL 版本选择
- 1.1.2 MySQL 安装方式
- 1.2 MySQL 压缩包安装教程
- 1.2.1 下载链接
- 1.2.2 安装 MySQL
- 1.2.3 添加配置文件
- 1.2.4 配置环境变量
- 1.2.5 安装 MySQL 服务
- 1.2.6 删除配置文件代码
- 1.2.7 验证 MySQL 配置
- 1.2.8 完成下课
- 1.3 MySQL 安装器安装教程
- 1.3.1 下载链接
- 1.3.2 安装 MySQL
- 1.3.3 配置环境变量
- 1.3.4 验证 MySQL 配置
- 1.3.5 完成下课
- 1.4 MySQL 卸载教程
- 1.4.1 压缩包卸载
- 1.4.2 安装器卸载
- 1.5 MySQL管理工具安装
- 1.5.1 下载链接
- 1.5.2 Navicate 安装
- 1.5.3 SqlYog 安装
- 1.5.4 PowerDesigner 安装
- 二、MySQL 数据库设计
- 2.1 为什么需要设计数据库
- 2.1.1 软件项目开发周期中数据库设计
- 2.1.2 设计数据库的步骤
- 2.2 为什么使用 E-R 图
- 2.2.1 E-R 图概念
- 2.3 数据库设计模型转换
- 2.3.1 关系型数据库设计分类
- 2.3.2 各模型之间的元素对应关系
- 2.3.3 数据模型转换方案
- 2.4 实体关系约束转换
- 2.4.1 一对多关系的两个实体
- 2.4.2 一对一关系的两个实体
- 2.4.3 多对多关系的两个实体
- 2.5 PowerDesigner 软件
- 2.5.1 绘制数据库模型图
- 2.5.2 生成 SQL 代码
- 2.6 数据库设计范式
- 2.6.1 三大数据库设计范式
- 2.7 规范化和性能的关系
- 2.7.1 演示案例
- 2.8 用三大范式设计表结构
- 2.8.1 实现思路
- 三、MySQL 基础
- 3.1 MySQL 简介
- 3.1.1 MySQL 发展历史
- 3.1.2 MySQL 的优势
- 3.1.3 MySQL 安装教程
- 3.2 命令行连接 MySQL
- 3.2.1 启动服务命令
- 3.2.2 停止服务命令
- 3.2.3 登录数据库命令
- 3.2.4 查看当前版本和用户信息
- 3.3 数据库管理指令
- 3.3.1 创建数据库
- 3.3.2 查看数据库列表
- 3.3.3 选择数据库
- 3.3.4 删除数据库
- 3.4 Navicat 数据库管理工具
- 3.4.1 安装教程
- 3.4.2 Navicat 连接 MySQL
- 3.4.3 使用 Navicat 创建数据库
- 3.5 结构化查询语言
- 3.5.1 DML (数据操作语言)
- 3.5.2 DDL (数据定义语言)
- 3.5.3 DQL (数据查询语言)
- 3.5.4 DCL (数据控制语言)
- 3.6 MySQL 数据类型
- 3.6.1 数值类型
- 3.6.2 日期类型
- 3.6.3 字符串
- 3.7 DDL (数据定义语言)
- 3.7.1 创建表
- 3.7.2 修改表
- 3.7.3 删除表
- 3.8 MySQL 注释
- 3.8.1 单行注释
- 3.8.2 多行注释
- 3.9 MySQL 字段的约束及属性
- 3.9.1 主键约束
- 3.9.2 外键约束
- 3.9.3 主键与外键的区别
- 3.9.4 主外键建立后注意事项
- 3.10 MySQL 注释与字符集编码
- 3.10.1 注释
- 3.10.2 设置字符集编码
- 3.10.3 演示案例
- 3.11 MySQL 查看表
- 3.11.1 查看数据库中的表
- 3.11.2 描述表结构
- 3.12 MySQL 的存储引擎
- 3.12.1 存储引擎的类型
- 3.12.2 查看系统所支持的存储引擎类型
- 3.12.3 常用的 MySQL 存储引擎
- 3.12.4 InnoDB 和 MyISAM 存储引擎比较
- 3.12.5 适用场合
- 3.12.6 查看当前存储引擎
- 2.12.7 修改存储引擎
- 3.12.8 设置表的存储引擎
- 3.12.9 数据表的存储文件
- 3.12.10 存储位置
- 3.13 MySQL 系统帮助
- 3.13.1 详细帮助
- 四、MySQL 高级（DML 增删改）
- 4.1 DML 数据操纵语言
- 4.2 插入数据
- 4.2.1 一次向表中插入一条记录
- 4.2.2 一次向表中插入多行数据
- 4.2.3 将查询结果插入到新表
- 4.3 更新数据
- 4.3.1 更新数据记录
- 4.4 删除数据
- 4.4.1 删除数据记录
- 4.4.2 格式化表记录
- 4.5 比较 DROP、DELETE 与 TRUNCATE 语句
- 4.5.1 执行速度
- 4.5.2 使用选择
- 五、MySQL 高级（DQL 查）
- 5.1 DQL 数据查询语言
- 5.2 基础查询
- 5.2.1 查询全部行和列
- 5.2.2 查询部分列
- 5.2.3 AS 别名
- 5.3 运算符
- 5.3.1 算术运算符
- 5.3.2 比较运算符
- 5.3.3 逻辑运算符
- 5.3.4 位运算符
- 5.3.5 运算符优先级
- 5.4 条件查
- 5.4.1 精确查询
- 5.4.2 单条件查询
- 5.4.3 多条件查询
- 5.5 排序查
- 5.5.1 升序排序
- 5.5.2 降序排序
- 5.5.3 多字段排序
- 5.6 常用函数
- 5.6.1 字符串函数
- 5.6.2 时间日期函数
- 5.6.3 聚合函数
- 4.6.4 数学函数
- 5.7 分组查
- 5.7.1 GROUP BY
- 5.7.2 HAVING
- 5.8 分页查
- 5.8.1 下标 5 开始查询 5 条
- 5.8.2 每页 m 个数据查第 n 页
- 5.9 模糊查
- 5.9.1 通配符
- 5.9.2 LIKE
- 5.9.3 BETWEEN
- 5.9.4 IS NULL
- 5.10 子查询
- 5.10.1 IN 子查询
- 5.10.2 NOT IN 子查询
- 5.10.3 EXISTS
- 5.10.4 EXISTS 子查询
- 5.10.5 NOT EXIST 子查询
- 5.10.6 子查询小结
- 5.10.7 子查询注意事项
- 5.11 连接查
- 5.11.1 JOIN
- 5.11.2 内连接
- 5.11.3 外连接
- 六、企业级开发技术
- 6.1 存储过程
- 6.1.1 什么是存储过程
- 6.1.2 存储过程可以包含
- 6.1.3 存储过程的优缺点
- 6.1.4 创建存储过程
- 6.1.5 定义存储过程的参数
- 6.1.6 调用存储过程
- 6.1.7 查看存储过程状态
- 6.1.8 查看存储创建代码
- 6.1.9 修改存储过程
- 6.1.10 删除存储过程
- 6.1.11 存储过程中的变量
- 6.1.12 存储过程控制语句
- 6.1.13 IF-ELSE 条件语句
- 6.1.14 CASE 条件语句
- 6.1.15 WHILE 循环语句
- 6.1.16 LOOP 循环语句
- 6.1.17 REPEAT 循环语句
- 6.1.18 迭代语句
- 6.2 事务
- 6.2.1 什么是事务
- 6.2.2 事务的特性
- 6.2.3 如何创建事务
- 6.2.4 事务处理
- 6.2.5 自动关闭和开启事务
- 6.2.6 遵循原则
- 6.3 视图
- 6.3.1 为什么需要视图
- 6.3.2 什么是视图
- 6.3.3 视图的好处
- 6.3.4 创建 / 查看视图
- 6.3.5 使用视图注意事项
- 6.3.6 演示案例
- 6.4 索引
- 6.4.1 什么是索引
- 6.4.2 常用索引类型
- 6.4.3 创建/删除索引
- 6.4.4 创建索引的指导原则
- 6.4.5 使用索引时注意事项
- 6.4.6 查看索引 / 删除索引
- 6.5 数据库的备份与恢复
- 6.5.1 为什么进行数据库备份
- 6.5.2 数据的备份与恢复
- 6.5.3 mysqldump 备份数据库
- 6.5.4 Navicat 备份数据库
- 6.5.5 恢复数据库
- 6.5.6 mysql 恢复数据库
- 6.5.7 source 恢复数据库
- 6.5.8 Navicat 导入数据
文章目录
- 玄子Share-BCSP助学手册之数据库开发
- 目录
- @[toc]
- 一、课程软件的安装与卸载
- 1.1 MySQL 版本选择与安装方式
- 1.1.1 MySQL 版本选择
- 1.1.2 MySQL 安装方式
- 1.2 MySQL 压缩包安装教程
- 1.2.1 下载链接
- 1.2.2 安装 MySQL
- 1.2.3 添加配置文件
- 1.2.4 配置环境变量
- 1.2.5 安装 MySQL 服务
- 1.2.6 删除配置文件代码
- 1.2.7 验证 MySQL 配置
- 1.2.8 完成下课
- 1.3 MySQL 安装器安装教程
- 1.3.1 下载链接
- 1.3.2 安装 MySQL
- 1.3.3 配置环境变量
- 1.3.4 验证 MySQL 配置
- 1.3.5 完成下课
- 1.4 MySQL 卸载教程
- 1.4.1 压缩包卸载
- 1.4.2 安装器卸载
- 1.5 MySQL管理工具安装
- 1.5.1 下载链接
- 1.5.2 Navicate 安装
- 1.5.3 SqlYog 安装
- 1.5.4 PowerDesigner 安装
- 二、MySQL 数据库设计
- 2.1 为什么需要设计数据库
- 2.1.1 软件项目开发周期中数据库设计
- 2.1.2 设计数据库的步骤
- 2.2 为什么使用 E-R 图
- 2.2.1 E-R 图概念
- 2.3 数据库设计模型转换
- 2.3.1 关系型数据库设计分类
- 2.3.2 各模型之间的元素对应关系
- 2.3.3 数据模型转换方案
- 2.4 实体关系约束转换
- 2.4.1 一对多关系的两个实体
- 2.4.2 一对一关系的两个实体
- 2.4.3 多对多关系的两个实体
- 2.5 PowerDesigner 软件
- 2.5.1 绘制数据库模型图
- 2.5.2 生成 SQL 代码
- 2.6 数据库设计范式
- 2.6.1 三大数据库设计范式
- 2.7 规范化和性能的关系
- 2.7.1 演示案例
- 2.8 用三大范式设计表结构
- 2.8.1 实现思路
- 三、MySQL 基础
- 3.1 MySQL 简介
- 3.1.1 MySQL 发展历史
- 3.1.2 MySQL 的优势
- 3.1.3 MySQL 安装教程
- 3.2 命令行连接 MySQL
- 3.2.1 启动服务命令
- 3.2.2 停止服务命令
- 3.2.3 登录数据库命令
- 3.2.4 查看当前版本和用户信息
- 3.3 数据库管理指令
- 3.3.1 创建数据库
- 3.3.2 查看数据库列表
- 3.3.3 选择数据库
- 3.3.4 删除数据库
- 3.4 Navicat 数据库管理工具
- 3.4.1 安装教程
- 3.4.2 Navicat 连接 MySQL
- 3.4.3 使用 Navicat 创建数据库
- 3.5 结构化查询语言
- 3.5.1 DML (数据操作语言)
- 3.5.2 DDL (数据定义语言)
- 3.5.3 DQL (数据查询语言)
- 3.5.4 DCL (数据控制语言)
- 3.6 MySQL 数据类型
- 3.6.1 数值类型
- 3.6.2 日期类型
- 3.6.3 字符串
- 3.7 DDL (数据定义语言)
- 3.7.1 创建表
- 3.7.2 修改表
- 3.7.3 删除表
- 3.8 MySQL 注释
- 3.8.1 单行注释
- 3.8.2 多行注释
- 3.9 MySQL 字段的约束及属性
- 3.9.1 主键约束
- 3.9.2 外键约束
- 3.9.3 主键与外键的区别
- 3.9.4 主外键建立后注意事项
- 3.10 MySQL 注释与字符集编码
- 3.10.1 注释
- 3.10.2 设置字符集编码
- 3.10.3 演示案例
- 3.11 MySQL 查看表
- 3.11.1 查看数据库中的表
- 3.11.2 描述表结构
- 3.12 MySQL 的存储引擎
- 3.12.1 存储引擎的类型
- 3.12.2 查看系统所支持的存储引擎类型
- 3.12.3 常用的 MySQL 存储引擎
- 3.12.4 InnoDB 和 MyISAM 存储引擎比较
- 3.12.5 适用场合
- 3.12.6 查看当前存储引擎
- 2.12.7 修改存储引擎
- 3.12.8 设置表的存储引擎
- 3.12.9 数据表的存储文件
- 3.12.10 存储位置
- 3.13 MySQL 系统帮助
- 3.13.1 详细帮助
- 四、MySQL 高级（DML 增删改）
- 4.1 DML 数据操纵语言
- 4.2 插入数据
- 4.2.1 一次向表中插入一条记录
- 4.2.2 一次向表中插入多行数据
- 4.2.3 将查询结果插入到新表
- 4.3 更新数据
- 4.3.1 更新数据记录
- 4.4 删除数据
- 4.4.1 删除数据记录
- 4.4.2 格式化表记录
- 4.5 比较 DROP、DELETE 与 TRUNCATE 语句
- 4.5.1 执行速度
- 4.5.2 使用选择
- 五、MySQL 高级（DQL 查）
- 5.1 DQL 数据查询语言
- 5.2 基础查询
- 5.2.1 查询全部行和列
- 5.2.2 查询部分列
- 5.2.3 AS 别名
- 5.3 运算符
- 5.3.1 算术运算符
- 5.3.2 比较运算符
- 5.3.3 逻辑运算符
- 5.3.4 位运算符
- 5.3.5 运算符优先级
- 5.4 条件查
- 5.4.1 精确查询
- 5.4.2 单条件查询
- 5.4.3 多条件查询
- 5.5 排序查
- 5.5.1 升序排序
- 5.5.2 降序排序
- 5.5.3 多字段排序
- 5.6 常用函数
- 5.6.1 字符串函数
- 5.6.2 时间日期函数
- 5.6.3 聚合函数
- 4.6.4 数学函数
- 5.7 分组查
- 5.7.1 GROUP BY
- 5.7.2 HAVING
- 5.8 分页查
- 5.8.1 下标 5 开始查询 5 条
- 5.8.2 每页 m 个数据查第 n 页
- 5.9 模糊查
- 5.9.1 通配符
- 5.9.2 LIKE
- 5.9.3 BETWEEN
- 5.9.4 IS NULL
- 5.10 子查询
- 5.10.1 IN 子查询
- 5.10.2 NOT IN 子查询
- 5.10.3 EXISTS
- 5.10.4 EXISTS 子查询
- 5.10.5 NOT EXIST 子查询
- 5.10.6 子查询小结
- 5.10.7 子查询注意事项
- 5.11 连接查
- 5.11.1 JOIN
- 5.11.2 内连接
- 5.11.3 外连接
- 六、企业级开发技术
- 6.1 存储过程
- 6.1.1 什么是存储过程
- 6.1.2 存储过程可以包含
- 6.1.3 存储过程的优缺点
- 6.1.4 创建存储过程
- 6.1.5 定义存储过程的参数
- 6.1.6 调用存储过程
- 6.1.7 查看存储过程状态
- 6.1.8 查看存储创建代码
- 6.1.9 修改存储过程
- 6.1.10 删除存储过程
- 6.1.11 存储过程中的变量
- 6.1.12 存储过程控制语句
- 6.1.13 IF-ELSE 条件语句
- 6.1.14 CASE 条件语句
- 6.1.15 WHILE 循环语句
- 6.1.16 LOOP 循环语句
- 6.1.17 REPEAT 循环语句
- 6.1.18 迭代语句
- 6.2 事务
- 6.2.1 什么是事务
- 6.2.2 事务的特性
- 6.2.3 如何创建事务
- 6.2.4 事务处理
- 6.2.5 自动关闭和开启事务
- 6.2.6 遵循原则
- 6.3 视图
- 6.3.1 为什么需要视图
- 6.3.2 什么是视图
- 6.3.3 视图的好处
- 6.3.4 创建 / 查看视图
- 6.3.5 使用视图注意事项
- 6.3.6 演示案例
- 6.4 索引
- 6.4.1 什么是索引
- 6.4.2 常用索引类型
- 6.4.3 创建/删除索引
- 6.4.4 创建索引的指导原则
- 6.4.5 使用索引时注意事项
- 6.4.6 查看索引 / 删除索引
- 6.5 数据库的备份与恢复
- 6.5.1 为什么进行数据库备份
- 6.5.2 数据的备份与恢复
- 6.5.3 mysqldump 备份数据库
- 6.5.4 Navicat 备份数据库
- 6.5.5 恢复数据库
- 6.5.6 mysql 恢复数据库
- 6.5.7 source 恢复数据库
- 6.5.8 Navicat 导入数据
一、课程软件的安装与卸载
1.1 MySQL 版本选择与安装方式
1.1.1 MySQL 版本选择
按照教材上的要求，统一使用MySQL版本号为5.7.40的版本
1.1.2 MySQL 安装方式
MySQL 的安装方式有两种
- 安装器直接安装
- 压缩包配置安装
安装器安装，快速便捷，安装过程均为图像操作界面，但缺点是卸载，极其麻烦，不仅要删除MySQL文件，还要删除注册表，等相关信息。如果卸载不干净，影响以后的安装配置等。
压缩包安装，相对比较麻烦，需要解压后，自行配置MySQL服务等，但优点是，卸载非常简单，只需要把解压文件删除即可。
我推荐使用，压缩包解压配置的方式安装MySQL，虽然麻烦了一点，但后期使用MySQL出现问题，需要重新安装MySQL就会很舒服。
两种安装方式我都写有教程，不用纠结使用哪种安装方式，能安装上就行了。
1.2 MySQL 压缩包安装教程
1.2.1 下载链接
官方 https://downloads.mysql.com/archives/community/
度盘 https://pan.baidu.com/s/1fuGdLIDXx2-2b5mEdqEh3g?pwd=xzsr
1.2.2 安装 MySQL
注意选择版本号为5.7.40
下载完后我们会得到一个以 MySQL 开头命名的压缩包，我们需要把这个压缩包解压到本地
这里可以把 MySQL 文件名后面的-winx64删除掉（度盘下载的压缩包我已经删除过了）
推荐将文件解压到 C 盘的 Program Files 文件下，即解压路径为C:\Program Files\mysql-5.7.40
1.2.3 添加配置文件
在C:\Program Files\mysql-5.7.40文件夹内新建一个文本文档，【强制】命名为：my.ini
注意开启显示文件拓展名设置
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6MTON64E-1680505151719)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230202161947.png)]
如果在度盘下载的压缩包，我在压缩包中已经写入过
my.ini文件，只需要注意自己的安装路径是否和配置文件内路径一致即可
使用记事本方法打开my.ini文件，将下面的配置文件粘贴进去
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\Program Files\mysql-5.7.40
# 设置mysql数据库的数据的存放目录
datadir=C:\Program Files\mysql-5.7.40\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#跳过密码
skip-grant-tables
basedir：就是mysql的安装路径
datadir：只需要把
\data前面的路径替换即可文本里的代码前面是没有数字的
1.2.4 配置环境变量
- 右键此电脑
- 属性
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vFEYWmLM-1680505151720)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224010.png)]
- 高级系统设置
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2Jvho0qN-1680505151720)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224026.png)]
- 环境变量
- 下方系统变量，找到
Path双击进入 - 新建系统变量，变量值为 mysql 安装
bin目录（实际路径以自己安装时设置为准） - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UWGsNLCv-1680505151720)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224206.png)]
C:\Program Files\mysql-5.7.40\bin
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nAcuOV6C-1680505151721)(./assets/image-20230202162835333.png)]
- 配置好后，保存退出
- 按下
Win + R然后通过管理员身份打开cmd - 输入
mysql --version查看环境是否配置成功
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LLgb1aFy-1680505151721)(./assets/image-20230202163232310.png)]
1.2.5 安装 MySQL 服务
- 按下键盘
Win + R打开运行输入cmd以管理员身份运行 - cd 到 mysql 文件的 bin 目录中
cd /d C:\Program Files\mysql-5.7.40\bin
- 输入
mysqld -install命令安装 MySQL 服务 - 继续输入
mysqld --initialize-insecure初始化 data 目录 - 输入
net start mysql启动 MySQL 服务 - 成功启动后，输入
mysql -uroot登录 MySQL - 成功登录之后，输入下面代码
update mysql.user set authentication_string=password('root') where user='root' and Host = 'localhost';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R19vHJIr-1680505151721)(./assets/image-20230202164124547.png)]
- 这里的
root就是登录密码，账户名也是root或者你可以把密码改成自己喜欢的 - 再输入
flush privileges;刷新一下权限 - 最后输入
exit退出 MySQL 即可
1.2.6 删除配置文件代码
退出后回到my.ini配置文件中，用#注释掉或者直接删除掉最后一行代码skip-grant-tables然后保存退出
到这里就已经成功下载并配置好了 MySQL
1.2.7 验证 MySQL 配置
-
命令行先输入
net stop mysql关闭 MySQL 服务 -
在输入
net start mysql开启 MySQL 服务 -
这里报错的请自己到服务中手动打开 MySQL 服务后重新尝试
-
登录数据库
mysql -uroot -proot
mysql -u账户名 -p密码
注意密码前后不能有空格
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jtq4hKDd-1680505151722)(./assets/image-20230202165147101.png)]
最后可以输入一个查询版本信息的SQl语句玩一下：
select version(),user();代码后面有分号
1.2.8 完成下课
MySQL 压缩包安装教程
1.3 MySQL 安装器安装教程
1.3.1 下载链接
官方 https://downloads.mysql.com/archives/installer/
度盘 https://pan.baidu.com/s/1fuGdLIDXx2-2b5mEdqEh3g?pwd=xzsr
1.3.2 安装 MySQL
注意选择版本号为5.7.40
下载完后我们会得到一个以 MySQL 开头命名的msi安装器，直接双击运行安装
- 提示是否更新，点击
NO即可 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6lj6MQpD-1680505151722)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223027.png)]
- 勾选下方
Custom自定义安装 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8vd7wGfA-1680505151722)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223154.png)]
- 左边找到
MySQL Server 5.7.40 - X64点击箭头添加到右边 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kWQT73Io-1680505151723)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223349.png)]
- 点击
Execute执行安装即可 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6PIUK1yZ-1680505151723)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223415.png)]
- 这个界面保持默认状态
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YzCTyX8E-1680505151723)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223532.png)]
- 设置
MySQL登录密码(默认设置为root) - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7xyidwJJ-1680505151724)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330225947.png)]
- 这里的
MySQL57就是服务器名称，点击Next下一步 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AilhThqY-1680505151724)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223658.png)]
- 安装完毕
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z0gACAZs-1680505151724)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223754.png)]
1.3.3 配置环境变量
- 右键此电脑
- 属性
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4EQ2490G-1680505151725)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224010.png)]
- 高级系统设置
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-55SMl73C-1680505151725)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224026.png)]
- 环境变量
- 下方系统变量，找到
Path双击进入 - 新建系统变量，变量值为 mysql 安装
bin目录（实际路径以自己安装时设置为准） - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CWpD0bgX-1680505151725)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224206.png)]
C:\Program Files\MySQL\MySQL Server 5.7\bin
- 配置好后，保存退出
- 按下
Win + R然后通过管理员身份打开cmd - 输入
mysql --version查看环境是否配置成功
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vgN2Wkk3-1680505151726)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223945.png)]
1.3.4 验证 MySQL 配置
- 再次通过按下
Win + R打开cmd窗口 - 输入账号密码
mysql -uroot -proot（-u后的root为管理员账号，-p后的root为之前设置的MySQL登录密码，没有空格直接跟密码） - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L3TcWMjz-1680505151726)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224416.png)]
最后可以输入一个查询版本信息的SQl语句玩一下：
select version(),user();代码后面有分号
1.3.5 完成下课
MySQL 安装器安装教程
1.4 MySQL 卸载教程
1.4.1 压缩包卸载
压缩包安装方式安装的MySQL，卸载就直接删除掉解压后的MySQL文件夹即可，然后删除配置的 MySQL 环境变量
1.4.2 安装器卸载
- 按下
Win + R打开cmd窗口输入services.msc打开服务 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3fExTYAf-1680505151726)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230901.png)]
- 找到MySQL停止服务
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0tX0Dh2y-1680505151727)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230926.png)]
- 打开控制面板
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N1DT3AWs-1680505151727)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230617-1680189633074-23.png)]
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7W7SmbU9-1680505151727)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230629.png)]
- 这里的两个软件都要卸载掉
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eCYirzJ1-1680505151727)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230648.png)]
到这里只是卸载了MySQL软件，MySQL还有许多文件并没有删掉，如果不删除干净，会影响后续安装导致安装失败。
- 删除MySQL在电脑硬盘上物理位置上的所有文件
C:\Program Files (x86)\MySQL内所有文件- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I2Npsz7U-1680505151728)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231218.png)]
C:\ProgramData\MySQL内所有文件，该文件是默认隐藏的，设置显示后即可见- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EM6E3XqD-1680505151728)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231312.png)]
C:\Documents and Settings\All Users\Application Data\MySQL内所有文件- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qHUkWA1Y-1680505151728)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231346.png)]
到这里也只是删掉了MySQL所有残余文件，还需要删除MySQL的注册表信息
- 按下
Win + R打开cmd窗口输入regedit打开注册表 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RymJdYic-1680505151728)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231549.png)]
- 删除以下三项内所有信息（可直接粘贴至地址栏）
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LMMlQDt1-1680505151729)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231609.png)]
HKEY_LOCAL_MACHINE/SYSTEM/ControlSet001/Services/Eventlog/Application/MySQLHKEY_LOCAL_MACHINE/SYSTEM/ControlSet002/Services/Eventlog/Application/MySQLHKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Eventlog/Application/MySQL- 到此MySQL算是彻底卸载完毕
1.5 MySQL管理工具安装
1.5.1 下载链接
Navicate官网 https://www.navicat.com/en/products
SqlYog官网 https://sqlyog.en.softonic.com/
度盘 https://pan.baidu.com/s/1fuGdLIDXx2-2b5mEdqEh3g?pwd=xzsr
1.5.2 Navicate 安装
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V0WCDtED-1680505151729)(./assets/image-20230205154315466-1680219755644-4.png)]
Navicate 涉及版权无法分享，加封面联系方式获取软件
1.5.3 SqlYog 安装
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EBevRr1J-1680505151729)(./assets/image-20230331164902240.png)]
SqlYog 涉及版权无法分享，加封面联系方式获取软件
1.5.4 PowerDesigner 安装
PowerDesigner 涉及版权无法分享，加封面联系方式获取软件
二、MySQL 数据库设计
2.1 为什么需要设计数据库
良好的数据库设计
- 降低应用程序的开发难度
- 具备高效的查询效率
- 具备良好的扩展性
糟糕的数据库设计
- 出现数据操作异常、修改复杂、数据冗余等问题
- 影响程序的性能，甚至会导致程序崩溃
数据库设计是对数据库中实体以及这些实体之间的关系进行规划和结构化的过程，当数据库比较复杂时，需要设计数据库
2.1.1 软件项目开发周期中数据库设计
- 需求分析阶段：分析客户的业务和数据处理需求
- 概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整
- 详细设计阶段：应用三大范式审核数据库结构
- 代码实现阶段：物理实现数据库，编码实现应用
- 软件测试阶段：……
- 上线部署：……
2.1.2 设计数据库的步骤
收集信息
- 通过对业务人员的访谈等方法，充分了解用户需求，理解数据库需要存储的业务信息（数据）及需要提供的操作
标识实体 （Entity）
- 标识数据库要管理的关键对象或实体，实体一般是名词
标识每个实体的属性（Attribute）
标识实体之间的关系（Relationship）
数据库设计中，不能重复出现含义相同的实体
2.2 为什么使用 E-R 图
2.2.1 E-R 图概念
E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型
| 符号 | 含义 |
|---|---|
| □ | 实体，一般是名词 |
| ○ | 属性，一般是名词 |
| ♢ | 关系，一般是动词 |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uryJFnze-1680505151729)(./assets/image-20230331234821123.png)]
- E-R图表示映射关系时，也可以通过添加箭头区分一对多和多对一
- 箭头指向映射关系为一的实体
- 在业务人员与开发人员的沟通中，E-R图能够极大的提高彼此之间的沟通效率
2.3 数据库设计模型转换
2.3.1 关系型数据库设计分类
-
概念数据模型设计
-
逻辑数据模型设计
-
物理数据模型设计
2.3.2 各模型之间的元素对应关系
| 概念数据模型 | 逻辑数据模型 | 物理数据模型 |
|---|---|---|
| 实体（Entity） | 实体（Entity） | 表（Table） |
| 属性（Attribute） | 属性（Attribute） | 列（Column） |
| 标识符（Identifier） | 标识符（Primary Identifier/ Foreign Identifier） | 键（Primary key/ Foreign key） |
| 关系（Relationship） | 关系（Relationship） | 参照完整性约束（Reference） |
2.3.3 数据模型转换方案
基本转换原理
-
将E-R图中每一个实体转换成一个表，实体的属性转换为表的列，实体的标识符转换为表的主键
-
将实体关系转化为表之间的引用完整性约束
- 根据关系的不同类型，通过外键引用主键的方式有所不同
2.4 实体关系约束转换
2.4.1 一对多关系的两个实体
-
一般会各自转换为一张表，且后者对应的表引用前者对应的表
-
一个表中的主键对应另一个表中可重复的字段
-
主键的值是不能重复的，关联的字段是可以重复的
-
存在一个值对应一个值（一对一）或者一个值对应多个值（一对多）
-
2.4.2 一对一关系的两个实体
- 一般是一个主键对应一个不可重复的字段
2.4.3 多对多关系的两个实体
- 除了将多对多关系中的两个实体各自转换为表外，一般还会创建第3个表，称为连接表
- 将多对多关系划分为两个一对多关系，将这两个表主键都插入到第3个表中
2.5 PowerDesigner 软件
- Sybase 公司开发的一款强大的数据库设计软件
- 开发人员经常使用的数据库建模工具
- 能够使用设计好的数据库模型直接生成SQL建表语句
- 极大的提升数据库的开发效率
推荐使用的版本：PowerDesigner 16.5
PowerDesigner 涉及版权无法分享，加封面联系方式获取软件
2.5.1 绘制数据库模型图
- 创建物理数据模型
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NSQxf2Hy-1680505151730)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230331235814.png)]
- 绘制模型图
- 创建表实体
- 添加属性
- 添加实体之间的映射关系
- 两个实体间连线，箭头指向主表
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CLDTxjJf-1680505151730)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401000646.png)]
2.5.2 生成 SQL 代码
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CR37kNwz-1680505151730)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401000958.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-620c82G5-1680505151730)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401001019.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YWqefGaX-1680505151731)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401001132.png)]
生成的 SQL文件可直接拿到 Navicate 运行
PowerDesigner中开发数据库模型图时，需要指定该模型图是为数据库管理系统开发的，因为PowerDesigner支持用数据库模型图生成数据库创建脚本的功能
2.6 数据库设计范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则
2.6.1 三大数据库设计范式
-
第一范式
- 第一范式的目标是确保每列保持原子性
- 如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）
-
第二范式
- 第二范式的目标是确保表中的每列都和主键相关
- 要求每个表只描述一件事情
-
第三范式
- 第三范式的目标是确保每列都和主键列直接相关，而不是间接相关
- 如果一个关系满足第二范式，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）
2.7 规范化和性能的关系
为满足某种商业目标，数据库性能比规范化数据库更重要
-
通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
-
通过在给定的表中插入计算列（如成绩总分），以方便查询
-
在数据规范化同时，要综合考虑数据库的性能
2.7.1 演示案例
| 名称 | 商品型号 | 单价 | 数量 | 金额 |
|---|---|---|---|---|
| 电视机 | 29英寸 | 2500 | 40 | 100000 |
金额 = 单价 * 数量，是冗余的列
为了提高查询统计速度，以空间换取时间
不要轻易违反数据库设计的规范化原则，如果处理不好，可能会适得其反，使应用程序运行速度更慢
2.8 用三大范式设计表结构
2.8.1 实现思路
-
向各表中插入数据，查看表中的每个属性列是否存在重复、插入异常、更新异常和删除异常
-
对照三大范式的定义，解决表中的异常问题
-
第一范式的目标是确保每列都是不可再分的最小数据单元，查看每列是否都满足第一范式
-
第二范式的每列与主键相关，不相关的放入别的表中，即要求一个表只描述一件事情
-
第三范式要求表中各列必须和主键直接相关，不能间接相关，查看各表，满足第三范式，对于不满足三大范式的表要进行拆分
三、MySQL 基础
3.1 MySQL 简介
MySQL 是一款流行的开源数据库，也是一个关系型数据库管理系统
在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一
3.1.1 MySQL 发展历史
| 时间 | 里程碑 |
|---|---|
| 1996 年 | MySQL 1.0 发布。它的历史可以追溯到 1979 年，作者 Monty 用 BASIC 设计的一个报表工具 |
| 1996 年 | 10 月 3.11.1 发布。MySQL 没有 2.x 版本 |
| 2000 年 | ISAM 升级成 MyISAM 引擎。MySQL 开源 |
| 2003 年 | MySQL 4.0 发布，集成 InnoDB 存储引擎 |
| 2005 年 | MySQL 5.0 版本发布，提供了视图、存储过程等功能 |
| 2008 年 | MySQL AB 公司被 Sun 公司收购，进入 Sun MySQL 时代 |
| 2009 年 | Oracle 收购 Sun 公司，进入 Oracle MySQL 时代 |
| 2010 年 | MySQL 5.5 发布，InnoDB 成为默认的存储引擎 |
| 2016 年 | MySQL 发布 8.0.0 版本 |
为什么没有 MySQL6、MySQL7？
MySQL5.6 可以当成 MySQL6.x
MySQL5.7 可以当成 MySQL7.x
3.1.2 MySQL 的优势
-
运行速度快
-
使用成本低
- 价格：MySQL对多数个人来说是免费的
-
容易使用
- 与其他大型数据库的设置和管理相比，其复杂程度较低，容易学习
-
可移植性强
- 能够工作在众多不同的系统平台上，例如：Windows 、Linux、Unix、MacOS等
-
适用用户广
-
丰富的接口
- 提供了用于C 、C++、Eiffel、Java、Perl、PHP、Python、Rudy和TCL 等语言的API
-
支持查询语言
- MySQL可以利用标准SQL语法和支持ODBC（开放式数据库连接）的应用程序
-
安全性和连接性
-
安全性和连接性; 十分灵活和安全的权限和密码系统，允许主机验证。连接到服务器时，所有的密码均采用加密形式，从而保证了密码安全
-
并且由于MySQL是网络化的，因此可以在因特网网上的任何地方访问，提高数据共享效率
-
3.1.3 MySQL 安装教程
官网 https://downloads.mysql.com/archives/community/
CSDN安装教程 https://blog.csdn.net/qq_62283694/article/details/129869634
安装教程见第一章、课程软件的安装与卸载
3.2 命令行连接 MySQL
-
首先检查 MySQL 服务是否启动
-
电脑按下
Win + R 输入 cmd打开dos窗口
3.2.1 启动服务命令
net start mysql
3.2.2 停止服务命令
net stop mysql
3.2.3 登录数据库命令
mysql –h服务器主机地址 –u用户名 –p密码
参数说明：
-h指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略
-u登录的用户名
-p告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8JsAEWtR-1680505151731)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205152901.png)]
3.2.4 查看当前版本和用户信息
SELECT VERSION(),USER();
分号为结束符，不可缺
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o9xTmzMo-1680505151731)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205152928.png)]
3.3 数据库管理指令
3.3.1 创建数据库
CREATE DATABASE 数据库名;
Query OK：SQL语句执行成功
1 row affected：操作影响的行数
(0.00 sec)：操作执行时间
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DpMpT55B-1680505151731)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153046.png)]
3.3.2 查看数据库列表
SHOW databases;
student：用户数据库
其他的是系统数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QCK4UKto-1680505151732)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153258.png)]
3.3.3 选择数据库
USE 数据库名;
使用数据表前，必须先选择该数据库！
ERROR 1049 (42000): Unknown database 'a'数据库不存在，报错
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-koRXtrhJ-1680505151732)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153925.png)]
3.3.4 删除数据库
DROP DATABASE 数据库名;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xdtnWtQD-1680505151732)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205154227.png)]
3.4 Navicat 数据库管理工具
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4vyid8fY-1680505151733)(./assets/image-20230205154315466-1680219755644-4.png)]
Navicat Premium是一款功能强大的、可支持多连接的数据库管理工具
允许在单一程序中同时连接多达7种数据库
-
MySQL
-
MariaDB
-
MongoDB
-
SQL Server
-
SQLite
-
Oracle
-
PostgreSQL
3.4.1 安装教程
官网 https://www.navicat.com/en/products
推荐使用：Navicat Premium 16 最新版
Navicate 涉及版权无法分享，加封面联系方式获取软件
3.4.2 Navicat 连接 MySQL
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CEDaEPEB-1680505151733)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205154815-1680247296828-4.png)]
3.4.3 使用 Navicat 创建数据库
- 通过操作向导创建数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P8jlztF6-1680505151733)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205160159-1680247296828-6.png)]
- 字符集要选择
utf8点击 SQL 预览可以查看建表语句
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s8CwxGQl-1680505151733)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205160210-1680247296828-7.png)]
- 通过SQL语句创建数据库
3.5 结构化查询语言
3.5.1 DML (数据操作语言)
操作数据库中所包含的数据
- INSERT UPDATE DELETE
3.5.2 DDL (数据定义语言)
创建和删除数据库对象等操作
- CREATE DROP ALTER
3.5.3 DQL (数据查询语言)
对数据库中的数据进行查询
- SELECT
3.5.4 DCL (数据控制语言)
控制数据库组件的存取许可、存取权限等
- GRANT COMMIT ROLLBACK
3.6 MySQL 数据类型
MySQL 中定义数据字段的类型对你数据库的优化是非常重要的
MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型
3.6.1 数值类型
| 类型 | 说明 | 取值范围 | 存储需求 |
|---|---|---|---|
| TINYINT | 非常小的数据 | 有符值：-27~27-1 无符号值：0~28-1 | 1字节 |
| SMALLINT | 较小的数据 | 有符值：-215~215-1 无符号值：0~216-1 | 2字节 |
| MEDIUMINT | 中等大小的数据 | 有符值：-223~223-1 无符号值：0~224-1 | 3字节 |
| INT | 标准整数 | 有符值：-231~231-1 无符号值：0~232-1 | 4字节 |
| BIGINT | 较大的整数 | 有符值：-263~263-1 无符号值：0~264-1 | 8字节 |
| FLOAT | 单精度浮点数 | ±1.1754351e-38 | 4字节 |
| DOUBLE | 双精度浮点数 | ±2.2250738585072014e-308 | 8字节 |
| DECIMAL | 字符串形式的浮点数 | Decimal（M，D） | M+2个字节 |
**UNSIGNED 属性：**标识为无符号数，非负数
**ZEROFILL 属性：**宽度（位数）不足以0填充
若某数值字段指定了 ZEROFILL 属性，将自动添加 UNSIGNED 属性
数值位数小于宽度，以0填充
数值位数大于宽度，显示实际位数
3.6.2 日期类型
| 日期类型 | 格式 | 取值范围 | 用途 |
|---|---|---|---|
| DATE | YYYY-MM-DD | 1000-01-01 / 9999-12-31 | 日期值 |
| TIME | HH:MM:SS | -835:59:59 / 838:59:59 | 时间值或持续时间 |
| YEAR | YYYY | 1901~2155 | 年份值 |
| DATETIME | YYYY-MM-DD hh:mm:ss | 1000-01-01 00：00：00 到 9999-12-31 23：59：59 | 混合日期和时间值 |
| TIMESTAMP | YYYY-MM-DD hh:mm:ss | ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | 混合日期和时间值，时间戳 |
若某日期字段默认值为系统当前日期，可指定为
TIMESTAMP类型
3.6.3 字符串
| 字符串类型 | 大小 | 用途 |
|---|---|---|
| CHAR[(M)] | 0-255 bytes | 定长字符串 |
| VARCHAR[(M)] | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
char(m) 和 varchar(m) 中括号中 m 代表字符的个数，并不代表字节个数
比如 CHAR(30) 就可以存储 30 个字符
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择
3.7 DDL (数据定义语言)
3.7.1 创建表
CREATE TABLE table_name (column_name column_type);
CREATE TABLE [IF NOT EXISTS] 表名 (字段 1 数据类型 [ 字段属性 | 约束 ][ 索引 ][ 字段备注 ],字段 2 数据类型 [ 字段属性 | 约束 ][ 索引 ][ 字段备注 ],字段 n 数据类型 [ 字段属性 | 约束 ][ 索引 ][ 字段备注 ]
)[ 表类型 ][ 表字符集 ][ 表备注 ];
IF NOT EXISTS 判断表是否存在，存在就不重复建表了
多字段使用逗号分隔，保留字用撇号括起来
演示案例
CREATE TABLE IF NOT EXISTS xuanzi(id INT UNSIGNED AUTO_INCREMENT,name VARCHAR(100) NOT NULL,phone VARCHAR(40) NOT NULL,date DATE,PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 如果你不想字段为
NULL可以设置字段的属性为NOT NULL，在操作数据库时如果输入该字段的数据为NULL，就会报错- AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加1
- PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔
- ENGINE 设置存储引擎，CHARSET 设置编码
3.7.2 修改表
- 修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
# 修改表名称
ALTER TABLE XuanZi RENAME TO patient;
- 添加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [属性];
# 添加字段
ALTER TABLE patient add XuanZi VARCHAR(20) NOT NULL;
- 修改字段
ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];
#修改字段
ALTER TABLE patient CHANGE XuanZiemail email VARCHAR(20);
- 删除表中的字段
ALTER TABLE 表名 DROP 字段名;
# 删除字段
ALTER TABLE patient DROP XuanZi;
- 添加主键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 PRIMARY KEY 表名（主键字段）;
# 添加主键约束 添加约束 约束名称 约束类型 表（字段）
ALTER TABLE patient ADD CONSTRAINT pk_patientID PRIMARY KEY patient(patientID);
- 添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 FOREIGN KEY（外键字段）REFERENCES 关联表名（关联字段）;
# 添加外键约束 添加约束 约束名称 约束类型 （添加外键的字段） 关联 外键（字段）
ALTER TABLE prescription ADD CONSTRAINT fk_patientID_patient FOREIGN KEY (patientID) REFERENCES patient(patientID);
MySQL中，
MyISAM存储类型的表不支持外键。因此，可以通过建立逻辑关联的方式保证数据的完整性和一致性
3.7.3 删除表
DROP TABLE [IF EXISTS] 表名;
在删除表之前，先使用
IF EXISTS语句验证表是否存在，执行 DROP TABLE 的表是空表，则可以直接删除如果表中已存储了业务数据，则需和数据库管理员联系，先对数据库数据进行备份并确认，再执行删除表及数据的操作
3.8 MySQL 注释
3.8.1 单行注释
# 单行注释
-- 单行注释
3.8.2 多行注释
/** 多行注释
*/
3.9 MySQL 字段的约束及属性
| 名称 | 关键字 | 说明 |
|---|---|---|
| 主键约束 | PRIMARY KEY(PK) | 设置该字段为表的主键 可唯一标识该表记录 |
| 外键约束 | FOREIGN KEY(FK) | 用于在两表之间建立关系， 需要指定引用主表的哪一字段 |
| 自动增长 | AUTO_INCREMENT | 设置该列为自增字段 默认每条自增1 通常用于设置主键 |
| 非空约束 | NOT NULL | 字段不允许为空 |
| 默认约束 | DEFAULT | 赋予某字段默认值 |
| 唯一约束 | UNIQUE KEY(UK) | 设置字段的值是唯一的 允许为空，但只能有一个空值 |
3.9.1 主键约束
主键：使用数据库表中某一字段或某几个字段唯一标识所有记录
- 单字段主键
CREATE TABLE [IF NOT EXISTS] 表名 (字段 1 数据类型 PRIMARY KEY,#……
);
-- 定义字段的同时指定主键
CREATE TABLE [IF NOT EXISTS] 表名 (字段 1 数据类型 ,#……[CONSTRAINT< 约束名 >] PRIMARY KEY[ 列名 ]
);
-- 定义所有字段之后指定主键
- 多字段联合主键
CREATE TABLE [IF NOT EXISTS] 表名 (#……PRIMARY KEY [字段1,字段2,…… ]
);
3.9.2 外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 FOREIGN KEY（外键字段）REFERENCES 关联表名（关联字段）;
# 添加外键约束 添加约束 约束名称 约束类型 （添加外键的字段） 关联 外键（字段）
其他约束的设置方式同理
3.9.3 主键与外键的区别
| 定义 | 作用 | 个数 | |
|---|---|---|---|
| 主键 | 唯一标识一条记录，不能出现重复且不能出现空值 | 保证数据完整性 | 主键只能有一个 |
| 外键 | “从表”中对应于“主表”的列，在从表中称为外键或者引用键“主表”中的列应为主键或者唯一键 | 和其他表建立关联，保持数据的一致性 | 一个表可以有多个外键 |
3.9.4 主外键建立后注意事项
- 当主表中没有对应的记录时，不能将记录添加到子表
- 不能更改主表中的值而导致子表中的记录孤立
- 子表存在与主表对应的记录，不能从主表中删除该行
- 删除主表前，先删子表
3.10 MySQL 注释与字符集编码
3.10.1 注释
-
COMMENT关键字
-
在表结构中可以看到，与代码注释不同、
CREATE TABLE test (id int(11) UNSIGNED COMMENT '编号'
)COMMENT='测试表';
3.10.2 设置字符集编码
-
默认情况下，MySQL所有数据库、表、字段等使用 MySQL 默认字符集
-
为了存储独特的数据，可在创建表时指定字符集
CREATE TABLE [IF NOT EXISTS] 表名（#……
）CHARSET = 字符集名;
3.10.3 演示案例
CREATE TABLE `checkitem` (`checkItemID` int(4) NOT NULL AUTO_INCREMENT COMMENT '检查项目编号',`checkItemName` varchar(50) NOT NULL COMMENT '检查项目名称',`checkItemCost` float NOT NULL COMMENT '检查项目价格',PRIMARY KEY (`checkItemID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '检查项目表';
3.11 MySQL 查看表
3.11.1 查看数据库中的表
SHOW TABLES;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jWvAlJks-1680505151734)(./assets/image-20230331173945642.png)]
3.11.2 描述表结构
DESCRIBE 表名;
DESC 表名;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7DaaQFMd-1680505151734)(./assets/image-20230331173500098.png)]
为了避免
DOS窗口出现中文乱码，可执行SET NAMES gbk;
3.12 MySQL 的存储引擎
存储引擎，是存储数据的核心组件，指定了表的存储类型和存储形式
3.12.1 存储引擎的类型
- InnoDB
- MyISAM
- Memory
- CSV
- 等9种
3.12.2 查看系统所支持的存储引擎类型
SHOW ENGINES;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eeyRlZ2F-1680505151734)(./assets/image-20230331232103064.png)]
3.12.3 常用的 MySQL 存储引擎
InnoDB 存储引擎
- 支持具有提交、回滚和崩溃恢复能力的事务控制
MyISAM 存储引擎
- 不支持事务，也不支持外键约束，访问速度比较快
3.12.4 InnoDB 和 MyISAM 存储引擎比较
| 功能 | InnoDB | MyISAM |
|---|---|---|
| 事务处理 | 支持 | 不支持 |
| 外键约束 | 支持 | 不支持 |
| 表空间大小 | 较大 | 较小 |
| 数据行锁定 | 支持 | 不支持 |
3.12.5 适用场合
- 使用 MyISAM：不需事务，空间小，以查询访问为主
- 使用InnoDB：多删除、更新操作，安全性高，事务处理及并发控制
3.12.6 查看当前存储引擎
MySQL 5.7 版本默认的存储引擎是InnoDB
- 查看当前默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine%';
-- default_storage_engine% 默认存储引擎
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ScwOnk4I-1680505151735)(./assets/image-20230331232525679.png)]
2.12.7 修改存储引擎
- 修改
my.ini配置文件
default-storage-engine=MyISAM
-- MyISAM 改为其他类型的存储引擎
修改配置文件后，需要重启MySQL服务设置才会生效
3.12.8 设置表的存储引擎
数据表默认使用当前 MySQL 默认的存储引擎
创建表时，可以设置表的存储引擎类型
CREATE TABLE 表名 (
# ……
) ENGINE= 存储引擎 ;
演示案例
CREATE TABLE `checkitem` (`checkItemID` int(4) NOT NULL AUTO_INCREMENT COMMENT '检查项目编号',`checkItemName` varchar(50) NOT NULL COMMENT '检查项目名称',`checkItemCost` float NOT NULL COMMENT '检查项目价格',PRIMARY KEY (`checkItemID`) USING BTREE
) ENGINE = MyISAM;
3.12.9 数据表的存储文件
MyISAM 类型表文件
-
*.frm表结构定义文件 -
*.MYD数据文件 -
*.MYI索引文件
InnoDB 类型表文件
-
*.frm表结构定义文件 -
ibdata1文件
3.12.10 存储位置
因操作系统而异，可查my.ini
datadir="C:\ProgramData\MySQL\MySQL Server 5.7\Data"
innodb_data_home_dir="D:/MySQL Datafiles/"
3.13 MySQL 系统帮助
HELP 查询内容;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VoIInVr7-1680505151735)(./assets/image-20230331233452271.png)]
3.13.1 详细帮助
HELP contents;
#查看帮助文档目录列表
HELP Data Definition;
#查看数据定义
HELP CREATE DATABASE;
#查看CREATE DATABASE的帮助信息
四、MySQL 高级（DML 增删改）
4.1 DML 数据操纵语言
DML（Data Manipulation Language）DML对数据库中表记录的执行操作
-
插入（INSERT）
-
插入单行数据
-
插入多行数据
-
将查询结果插入到新表
-
-
更新（UPDATE）
-
删除（DELETE）
软件开发人员日常使用最频繁的操作，必备技能！！！
4.2 插入数据
4.2.1 一次向表中插入一条记录
INSERT INTO 表名 [(字段名列表)] VALUES (值列表);
# 增
INSERT into gradeinfo (grade) VALUES ('ACCP');
字段名是可选的，如省略，则依次插入所有字段
多个列表和多个值之间使用逗号分隔
值列表和字段名列表一 一对应
如果插入表中的部分字段数据，则字段名列表必填
每个数据值的数据类型、精度和小数位数必须与相应的列匹配
不能为标识列指定值（主键）
如果在设计表的时候就指定了某列不允许为空，则必须插入数据
如果字段具有默认值，可以使用
DEFAULT关键字来代替插入的数值
4.2.2 一次向表中插入多行数据
INSERT INTO 表名 [(字段名列表)] VALUES (值列表1), (值列表2), (值列表n);
# 增
INSERT INTO gradeinfo (grade) VALUES ('ACCP'),('BCSP');
为避免表结构发生变化引发的错误，建议插入数据时写明具体字段名！
4.2.3 将查询结果插入到新表
INSERT INTO 表名（字段1,字段2 , ……） SELECT 字段1，字段2 , …… FROM 原表;
# 事先创建且与插入数据字段相符
CREATE TABLE patient_address (SELECT patientName, address FROM patient ) ;
# 无须事先创建
如果新表已存在，将会报错！
4.3 更新数据
4.3.1 更新数据记录
UPDATE 表名 SET 字段1=值1, 字段2=值2, …, 字段n=值n [WHERE 条件];
# 更新表数据
UPDATE gradeinfo SET gradeinfo.grade='BCSP' WHERE gradeinfo.id=2;
4.4 删除数据
4.4.1 删除数据记录
DELETE FROM 表名 [WHERE条件];
# 删除表信息
DELETE FROM patient WHERE patientID =1
DELETE条件删除语句中，如果未指定WHERE条件语句，则将删除表中的所有数据
DELETE FROM gradeinfo WHERE gradeinfo.id=1;
4.4.2 格式化表记录
TRUNCATE TABLE 表名;
# 格式化表
TRUNCATE TABLE gradeinfo;
使用 TRUNCATE 语句删除表中记录后，将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比 DELETE 语句快
4.5 比较 DROP、DELETE 与 TRUNCATE 语句
| DROP | DELETE | TRUNCATE | |
|---|---|---|---|
| 类型 | DDL 会隐式提交，不能回滚 | DML 每次从表中删除一行数据的同时将改行的删除操作记录在redo和undo表空间中，以便进行回滚和重做操作 需要手动提交操作才能生效，可通过ROLLBACK撤销操作 | DDL 会隐式提交，不会记录日志，不能回滚 |
| 功能 | 删除表结构及所有数据，并将标所占用的空间全部释放 | 可根据条件删除表中满足条件的数据，如果不指定WHERE子句，则删除表中所有记录 | 删除表中所有记录，并将重建表结构 |
4.5.1 执行速度
一般来说，DROP>TRUNCATE>DELETE
4.5.2 使用选择
- 使用 DROP、TRUNCATE 语句时，要慎重
- 如果使用 DELETE 语句删除表中部分数据要带上 WHERE 子句，且要注意表空间要足够大
- 如果要删除表，使用 DROP 语句
- 如果要保留表但删除表中所有数据，如果与事务无关可以使用 TRUNCATE 语句
- 如果与事务有关，则使用 DELETE 语句
五、MySQL 高级（DQL 查）
5.1 DQL 数据查询语言
Data Query Language，用于查询数据库的表中数据，是数据库中最为核心的语言，使用频率最高
5.2 基础查询
5.2.1 查询全部行和列
SELECT * FROM 表名称
SELECT * FROM patient
# 查询 patient 病人表中的所有字段 * 表示所有字段
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jVlfeBMl-1680505151735)(./assets/image-20230402180217861.png)]
5.2.2 查询部分列
SELECT 字段1,字段2,字段n FROM 表名称
SELECT patientID,patientName,gender FROM patient
# 查询 patient 病人表中的部分字段（多个字段之间使用，分隔）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m85Vz4WT-1680505151735)(./assets/image-20230402180548009.png)]
有的时候我们不想让用户看到所有的信息，比如用户密码这类的隐私信息，我们就可以通过写具体的字段，就只查询部分信息
在开发中，推荐使用以查询部分列的方式代替
SELECT *查询全部列，可提高执行效率并养成良好编码习惯
5.2.3 AS 别名
- 使用 AS 关键字，可以为表、字段、查询结果指定别名
SELECT 字段列表 FROM 表名 AS 表的别名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kruxYeDT-1680505151736)(./assets/image-20230402185049461.png)]
SELECT 字段1 AS 别名,字段2 FROM 表名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7EUPY9Tr-1680505151736)(./assets/image-20230402185113942.png)]
- 使用空格，简便的方法
SELECT 字段列表 FROM 表名 表的别名;
# 中间是空格
为某个表命名了别名后，在 SELECT 语句中出现该表的字段需要指定表名时，就必须统一使用该表的别名；否则将产生语法错误
5.3 运算符
运算符是一种符号，用来进行列间或变量之间的比较和数学运算
MySQL 常的运算符
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
5.3.1 算术运算符
| 运算符 | 作用 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / 或 DIV | 除法 |
| % 或 MOD | 取余 |
在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为 NULL
5.3.2 比较运算符
| 符号 | 描述 | 备注 |
|---|---|---|
| = | 等于 | |
| <>, != | 不等于 | |
| > | 大于 | |
| < | 小于 | |
| <= | 小于等于 | |
| >= | 大于等于 | |
| BETWEEN | 在两值之间 | |
| NOT BETWEEN | 不在两值之间 | |
| IN | 在集合中 | |
| NOT IN | 不在集合中 | |
| <=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
| LIKE | 模糊匹配 | |
| REGEXP 或 RLIKE | 正则式匹配 | |
| IS NULL | 为空 | |
| IS NOT NULL | 不为空 |
5.3.3 逻辑运算符
| 运算符号 | 作用 |
|---|---|
| NOT 或 ! | 逻辑非 |
| AND | 逻辑与 |
| OR | 逻辑或 |
| XOR | 逻辑异或 |
逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0
5.3.4 位运算符
| 运算符号 | 作用 |
|---|---|
| & | 按位与 |
| | | 按位或 |
| ^ | 按位异或 |
| ! | 取反 |
| << | 左移 |
| >> | 右移 |
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数
5.3.5 运算符优先级
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mp3zBLFS-1680505151736)(./assets/1011652-20170416163043227-1936139924.png)]
5.4 条件查
如果我们查询时，并不想显示所有的用户信息，只要查出满足指定条件的部分用户，或者精确到某一个用户的信息，就需要使用到 WHERE 关键字对 sql 语句增加查询条件
5.4.1 精确查询
SELECT * FROM patient WHERE patient.patientID = 1
# 通过患者 ID 精确查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KUZevOxW-1680505151736)(./assets/image-20230402181442241.png)]
这里我们就把患者 ID 作为查询条件，查询出患者 ID 为 1 的患者信息
5.4.2 单条件查询
SELECT * FROM patient WHERE patient.patientID < 10
# 查询病人 ID 小于 10 的患者信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e3vZq3LU-1680505151737)(./assets/image-20230402182833603.png)]
SELECT * FROM patient WHERE patientName ='玄子'
# 查询所有病人姓名为玄子的患者信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9K4WXiUB-1680505151737)(./assets/image-20230402224936557.png)]
5.4.3 多条件查询
-
如果查询条件中包含的条件不止一个，则条件根据逻辑关系的不同可以分为
与条件和或条件两种 -
与条件表示要求同时满足两个以上的条件，使用 AND 关键字可以构造与条件。 -
或条件表示几个条件中只需满足其中一个的条件，使用 OR 关键字可以构造或条件 -
AND
SELECT * FROM patient WHERE patientName ='玄子' AND address='长春市'
# 多条件查询 姓名为 玄子 且住址为 长春市
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VhDvBOJE-1680505151737)(./assets/image-20230402230857608.png)]
- OR
SELECT * FROM patient WHERE patientName ='玄子' OR address='长春市'
# 多条件查询 姓名为 玄子 '或者' 住址为 长春市
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3lhsvtBH-1680505151738)(./assets/image-20230402231059106.png)]
姓名 和 住址 这两个条件满足一个即可
5.5 排序查
5.5.1 升序排序
SELECT * FROM patient ORDER BY birthDate
# 通过病人的出生年月进行排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4FFD7tJN-1680505151738)(./assets/image-20230402183208506.png)]
这里我们使用 ORDER BY 关键字，对病人的出生年月进行排序，默认是从小到大，也就是
升序排列
5.5.2 降序排序
SELECT * FROM patient ORDER BY birthDate DESC
# 通过病人的出生年月进行降序排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q2iQ5WLm-1680505151738)(./assets/image-20230402183508864.png)]
使用降序排列只需要 在最后加上一个
DESC关键字即可升序排序后面其实也有一个关键字
ASC不过ORDER BY默认就是升序排列，所以ASC加不加都可以
5.5.3 多字段排序
SELECT * FROM patient ORDER BY birthDate ASC,patientID DESC
# 通过病人的出生年月和ID进行多字段排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wYIBOqfI-1680505151738)(./assets/image-20230402183948955.png)]
如果想要对多个字段进行排序，可直接使用
,分隔。然后写第二个需要排序的字段和排序方式即可
5.6 常用函数
MySQL 中将一些常用的数据处理操作封装起来，作为函数提供给程序员使用，可以提高程序员开发效率
MySQ L支持的常用函数
-
字符串函数
-
时间日期函数
-
聚合函数
-
数学函数
5.6.1 字符串函数
| 函数名 | 作用 | 举例 |
|---|---|---|
| CONCAT(str1,str2,strn) | 连接字符串str1、str2、strn为一个完整字符串 | SELECT CONCAT( ‘MySQL’,’ is powerful.'); 返回：MySQL is powerful. |
| LOWER(str) | 将字符串str中所有字符变为小写 | SELECT LOWER( ‘MySQL is powerful.’); 返回：mysql is powerful. |
| UPPER(str) | 将字符串str中所有字符变为大写 | SELECT UPPER( ‘MySQL is powerful.’); 返回：MYSQL IS POWERFUL. |
| SUBSTRING(str,num,len) | 返回字符串str的第num个位置开始长度为len的子字符串 | SELECT SUBSTRING( ‘MySQL is powerful.’,10,8); 返回：powerful |
| INSERT(str,pos,len,newstr) | 将字符串str从pos位置开始，len个字符长的子串替换为字符串newstr | SELECT INSERT( ‘MySQL is powerful.’,10,0,'very '); 返回：MySQL is very powerful. |
5.6.2 时间日期函数
| 函数名 | 作用 | 举例（部分结果与当前日期有关） |
|---|---|---|
| CURDATE() | 获取当前日期 | SELECT CURDATE(); 返回：2020-08-03 |
| CURTIME() | 获取当前时间 | SELECT CURTIME(); 返回：16:54:40 |
| NOW() | 获取当前日期和时间 | SELECT NOW(); 返回：2020-08-03 16:55:00 |
| WEEK(date) | 返回日期date为一年中的第几周 | SELECT WEEK(NOW()); 返回：31 |
| YEAR(date) | 返回日期date的年份 | SELECT YEAR(NOW()); 返回：2020 |
| HOUR(time) | 返回时间time的小时值 | SELECT HOUR(NOW()); 返回：16 |
| MINUTE(time) | 返回时间time的分钟值 | SELECT MINUTE(NOW()); 返回：56 |
| DATEDIFF(date1,date2) | 返回日期参数date1和date2之间相隔的天数 | SELECT DATEDIFF(NOW(), ‘2019-8-8’); 返回：361 |
| ADDDATE(date,n) | 计算日期参数date加上n天后的日期 | SELECT ADDDATE(NOW(), 5); 返回：2020-08-07 16:57:28 |
| UNIX_TIMESTAMP(date) | 将日期转换成时间戳 | SELECT UNIX_TIMESTAMP(‘2020-9-1’); 返回：1598889600 |
| DATE_ADD(d，INTERVAL expr type) | 计算起始日期 d 加上一个时间段后的日期 | SELECT DATE_ADD(‘2017-06-15’, INTERVAL 10 DAY); 返回：2017-06-25 |
DATE_ADD(d，INTERVAL expr type)type 参数可以是下列值：MICROSECOND，SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，YEAR，SECOND_MICROSECOND，MINUTE_MICROSECOND，MINUTE_SECOND，HOUR_MICROSECOND，HOUR_SECOND，HOUR_MINUTE，DAY_MICROSECOND，DAY_SECOND，DAY_MINUTE，DAY_HOUR，YEAR_MONTH
5.6.3 聚合函数
| 函数名 | 作用 |
|---|---|
| COUNT() | 返回某字段的行数 |
| MAX() | 返回某字段的最大值 |
| MIN() | 返回某字段的最小值 |
| SUM() | 返回某字段的和 |
| AVG() | 返回某字段的平均值 |
4.6.4 数学函数
| 函数名 | 作用 | 举例 |
|---|---|---|
| CEIL(x) | 返回大于或等于数值x的最小整数 | SELECT CEIL(-2.1); 返回：32 |
| FLOOR(x) | 返回小于或等于数值x的最大整数 | SELECT FLOOR(-2.1); 返回：-3 |
| RAND() | 返回0~1间的随机数 | SELECT RAND(); 返回：0.15013303621684485 |
MySQL 中还有许多专业领域或不常用的函数，想具体了解见文章附件：
第八章 8.4 MySQL 函数
5.7 分组查
5.7.1 GROUP BY
如果我们想要对，查询结果分组，比如按照，考试的科目ID 进行分组。就要使用GROUP BY 关键字对 subjectNo进行分组，首先我们可以看到成绩表中有四个字段分别是studentNo 学生学号，subjectNo 课程编号，examDate 考试日期，studentResult 考试成绩。其中subjectNo有三个编号我们就可以对其分组查询查出这三个编号
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8hVxOeul-1680505151739)(./assets/image-20230402190256908.png)]
- 分组考试成绩表中的科目编号
SELECT subjectNo FROM result GROUP BY subjectNo
# 分组考试成绩表中的科目编号
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dpKhySou-1680505151739)(./assets/image-20230402190524327.png)]
这样分组就只能看到成绩表中的科目 ID，并不能查询其他字段，所以我们通常搭配
聚合函数使用
- 分组科目编号，查询对应科目考试成绩的平均分
SELECT subjectNo,AVG(studentResult) FROM result GROUP BY subjectNo
# 分组科目编号，查询对应科目考试成绩的平均分
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dqe6C0N7-1680505151739)(./assets/image-20230402231713206.png)]
5.7.2 HAVING
我们在使用 GROUP BY 分组后还想要进一步筛选查询结果，就需要使用 HAVING 关键字进一步筛选，这里并不能使用 WHERE
- 分组考试成绩表中的科目编号，后再次筛选
SELECT subjectNo,AVG(studentResult) AS avg FROM result GROUP BY subjectNo HAVING avg >60
# 分组科目编号，查询对应科目考试成绩的平均分 后继续筛选 成绩在 60 分以上的 科目编号
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cJGMQfEo-1680505151739)(./assets/image-20230402231957986.png)]
5.8 分页查
SELECT <字段名列表>
FROM <表名或视图>
[WHERE <查询条件>]
[GROUP BY <分组的字段名>] [HAVING <条件>]
[ORDER BY <排序的字段名> [ASC 或 DESC]]
[LIMIT [位置偏移量，]行数];
5.8.1 下标 5 开始查询 5 条
SELECT * FROM patient LIMIT 5,5
# 位置偏移量：第1条记录的位置偏移量是0，第2条记录的位置偏移量是1……
# 行数：显示记录的条数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fd3OMIR4-1680505151740)(./assets/image-20230402233340898.png)]
使用LIMIT子句时，第1条记录的位置是0！
LIMIT 子句经常和 ORDER BY 子句一起使用，即先对查询结果进行排序，再根据 LIMIT 子句的参数返回指定的数据
5.8.2 每页 m 个数据查第 n 页
SELECT * FROM patient LIMIT (n-1)*m,m
# 每页 m 个第 n 页
5.9 模糊查
5.9.1 通配符
模糊查询一般使用关键字 LIKE 主要用于匹配列中的数据
| 通配符 | 解释 | 示例 |
|---|---|---|
| _ | 一个字符 | A LIKE ‘V_’，则符合条件的A如“VR”、“VC”等 |
| % | 任意长度的字符串 | B LIKE ‘SQL%’，则符合条件的A如“SQL Server”、“SQL Server高级编程”等 |
| [] | 括号中所指定范围内的一个字符 | C LIKE ‘900[1-2]’，则符合条件的C如“9001”或“9002” |
| [^] | 不在括号中所指定范围内的任意一个字符 | D LIKE ‘900[^1-2]’，则符合条件的D如“9003”或“9007”等 |
5.9.2 LIKE
select * from Students where StudentName like '张%'
# 名字以张开头的人
select * from Students where StudentName like '%张'
# 名字以张结尾的人
select * from Students where StudentName like '%张%'
# 名字中包含张的人
select * from Students where StudentName like '张_'
# 名字为张姓的单名人
select * from Students where StudentName like '张[1-4]'
# 查询结果为张姓的张1到张4
select * from Students where StudentName like '张[^1-4]'
# 查询结果为张姓的除了张1到张4的所有张姓单名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MdIkWfGk-1680505151740)(./assets/image-20230402234207243.png)]
匹配的字符串必须加单引号或者双引号
默认情况下，LIKE 关键字匹配字符串时候不区分大小写，可以在 LIKE 关键字后添加 BINARY 关键字来区分大小写。
如果查询内容中有通配符字符，就需要加转义字符
\
5.9.3 BETWEEN
SELECT 字段 FROM 表名 WHERE 列名 [NOT] BETWEEN 起始值 AND 最终值
SELECT * FROM patient WHERE patient.patientID BETWEEN 5 AND 15
# 查询 ID 在 5 到 15 的患者信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Yj4nOAan-1680505151740)(./assets/image-20230402235352933.png)]
NOT 可选参数，表示取反
查询指定范围内所有值，包括起始值和最终值
5.9.4 IS NULL
IS NULL关键字判断该列的值是否为空值，空值不是空字符串
SELECT 字段 FROM 表名 WHERE 列名 IS [NOT] NULL
SELECT * FROM patient WHERE address IS NOT NULL
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C4LXMfO7-1680505151740)(./assets/image-20230402235909456.png)]
NOT 可选参数，表示取反
5.10 子查询
子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询
SELECT …… FROM 表1 WHERE 字段1 比较运算符 (子查询);
SELECT * FROM patient WHERE patientID IN(1,2,3,4,5,9)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nND3jETL-1680505151741)(./assets/image-20230403000309493.png)]
SELECT * FROM patient WHERE patientID IN(SELECT patientID FROM patient WHERE patient.patientID BETWEEN 5 AND 15)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CyUcFjRk-1680505151741)(./assets/image-20230403000418635.png)]
先执行子查询，返回所有来自子查询的结果
再执行外围的父查询，返回查询的最终结果
将子查询和比较运算符联合使用
必须保证子查询返回的值不能多于一个
5.10.1 IN 子查询
- 如果子查询的结果为多个值，就会导致代码报错
- 解决方案就是使用 IN 关键字，将 = 替换成 IN
SELECT …… FROM 表名 WHERE 字段名 IN (子查询);
5.10.2 NOT IN 子查询
-
与
in一样，查询结果有多条使用 -
获取的值是不包含在查询结果里面的值
SELECT …… FROM 表名 WHERE 字段名 NOT IN (子查询);
5.10.3 EXISTS
DROP TABLE IF EXISTS temp;
CREATE TABLE log (… … #省略建表语句
) ;
5.10.4 EXISTS 子查询
- 子查询有返回结果： EXISTS 子查询结果为 TRUE
- 子查询无返回结果： EXISTS 子查询结果为 FALSE，外层查询不执行
SELECT …… FROM 表名 WHERE EXISTS (子查询);
5.10.5 NOT EXIST 子查询
- 与 EXISTS 子查询相反
- 子查询有返回结果： NOT EXIST子查询结果为 FALSE
- 子查询无返回结果： NOT EXIST子查询结果为 TRUE，外层查询执行
5.10.6 子查询小结
当一个查询是另一个查询的条件时，称之为子查询
-
任何允许使用表达式的地方都可以使用子查询
-
嵌套在父查询SELECT语句的子查询，可包括
-
SELECT 子句
-
SELECT (子查询) [AS 列别名] FROM 表名; -
FROM 子句
-
SELECT * FROM (子查询) AS 表别名; -
WHERE 子句
-
GROUP BY 子句
-
HAVING 子句
-
5.10.7 子查询注意事项
通常，将子查询放在比较条件的右边以增加可读性
子查询可以返回单行或多行数据，此时要选择合适的关键字
-
子查询的返回是单行数据时，比较条件中可以使用比较运算符
-
子查询的返回是多行数据时，比较条件中需要使用IN或 NOT IN 关键字
-
如果判断子查询是否有数据返回时，需要使用 EXISTS 或 NOT EXISTS 关键字
只出现在子查询中、而没有出现在父查询中的列不能包含在输出列中
5.11 连接查
5.11.1 JOIN
JOIN 按照功能大致分为如下三类
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录
- **LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录
5.11.2 内连接
最典型、最常用的连接查询，根据两张表中共同的列进行匹配，两个表存在主外键关系时，通常会使用内连接查询
SELECT …… FROM 表1 INNER JOIN 表2 ON ……
INNER JOIN用来连接两个表
INNER可以省略
ON用来设置两个表之间的关联条件
SELECT * FROM department_checkitem
INNER JOIN department ON department_checkitem.depID=department.depID
INNER JOIN checkitem ON department_checkitem.checkItemID=checkitem.checkItemID
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-44TG9TbL-1680505151741)(./assets/image-20230403001247130.png)]
5.11.3 外连接
特点
- 外连接可以分为主表和从表
- 主表的数据会被完全显示出来
- 从表中只显示满足连接条件的数据
- 外连接结果一般会比主表和从表中数据量最小的表中的数据多
- 左外连
SELECT * FROM prescription
LEFT JOIN patient ON prescription.patientID=patient.patientID
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YH5evtqd-1680505151741)(./assets/image-20230403001525010.png)]
- 右外连
SELECT * FROM prescription
RIGHT JOIN patient ON prescription.patientID=patient.patientID
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RTEgtetw-1680505151742)(./assets/image-20230403001641747.png)]
六、企业级开发技术
6.1 存储过程
关于存储过程我只能说请看下图，这是阿里巴巴发布的《阿里巴巴Java开发手册（终极版）v1.3版本》在 MySQL 第七条中强制指出禁止使用存储过程
所以对于存储过程不必深究，做到会写能看懂即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sJYPoLWJ-1680505151742)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230329084802.png)]
6.1.1 什么是存储过程
Stored Procedure
- 是一组为了完成特定功能的 SQL 语句集合
- 经编译后保存在数据库中
- 通过指定存储过程的名字并给出参数的值
- MySQL5.0 版本开始支持存储过程，使数据库引擎更加灵活和强大
6.1.2 存储过程可以包含
- 可带参数，也可返回结果
- 可包含数据操纵语句、变量、逻辑控制语句等
6.1.3 存储过程的优缺点
优点
- 减少网络流量
- 提升执行速度
- 减少数据库连接次数
- 安全性高
- 复用性高
缺点
- 可移植性差
SQL 最大的缺点还是 SQL 语言本身的局限性 SQL 本身是一种结构化查询语言，我们不应该用存储过程处理复杂的业务逻辑让 SQL 回归它
结构化查询语言的功用。复杂的业务逻辑，还是交给代码去处理吧
6.1.4 创建存储过程
CREATE[DEFINER = { user | CURRENT_USER }] # 定义DEFINER默认为当前用户
PROCEDURE 存储过程名[SQL SECURITY { DEFINER | INVOKER } | …]# 指定DEFINER或INVOKER权限
BEGIN…
END
| 特性 | 说明 |
|---|---|
| LANGUAGE SQL | 表示存储过程语言，默认SQL |
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | 表示存储过程要做的工作类别默认值为CONTAINS SQL |
| SQL SECURITY { DEFINER | INVOKER } | 指定存储过程的执行权限默认值是DEFINERDEFINDER：使用创建者的权限INVOKER：用执行者的权限 |
| COMMENT ‘string’ | 存储过程的注释信息 |
如果省略 SQL SECURITY 特性，则使用 DEFINER 属性指定调用者，且调用者必须具有 EXECUTE 权限，必须在 mysql.user 表中如果将 SQL SECURITY 特性指定为 INVOKER，则 DEFINER 属性无效
6.1.5 定义存储过程的参数
IN：指输入参数
- 该参数的值必须在调用存储过程时指定
- 存储过程中可以使用该参数，但它不能被返回
OUT：指输出参数
- 该参数可以在存储过程中发生改变，并可以返回
INOUT：指输入输出参数
- 该参数的值在调用存储过程时指定
- 在存储过程中可以被改变和返回
如果需要定义多个参数，需要使用
,进行分隔
6.1.6 调用存储过程
CALL 存储过程名([参数1,参数2, …]);
# 根据存储过程的定义包含相应的参数
存储过程调用类似于Java中的方法调用
6.1.7 查看存储过程状态
SHOW PROCEDURE STATUS
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rX9E7eXk-1680505151742)(./assets/image-20230403133422488.png)]
6.1.8 查看存储创建代码
SHOW CREATE PROCEDURE 存储过程名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZecEAgWI-1680505151742)(./assets/image-20230403133612810.png)]
6.1.9 修改存储过程
ALTER PROCEDURE 存储过程名[特性………]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yXKiOGs2-1680505151743)(./assets/image-20230403133809483.png)]
6.1.10 删除存储过程
DROP PROCEDURE 存储过程名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A4maPeh0-1680505151743)(./assets/image-20230403133956314.png)]
6.1.11 存储过程中的变量
与Java语言类似，定义存储过程时可以使用变量
DECLARE 变量名[,变量名...] 数据类型 [DEFAULT 值];
给变量进行赋值
SET 变量名 = 表达式值[,变量名=表达式...] ;
定义存储过程时，所有局部变量的声明一定要放在存储过程体的开始；否则，会提示语法错误
系统变量
- 指 MySQL 全局变量，以
@@开头，形式为@@变量名
用户自定义变量
- 局部变量
- 一般用于SQL的语句块中，如：存储过程中的BEGIN和END语句块
- 作用域仅限于定义该变量的语句块内
- 生命周期也仅限于该存储过程的调用期间
- 在存储过程执行到END时，局部变量就会被释放
- 会话变量
- 是服务器为每个客户端连接维护的变量，与MySQL客户端是绑定的
- 也称作用户变量
- 可以暂存值，并传递给同一连接中其他SQL语句进行使用
- 当MySQL客户端连接退出时，用户变量就会被释放
- 用户变量创建时，一般以
@开头，形式为@变量名
演示案例
- 根据病人名称和检查项目ID输出最后一次检查时间
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_exam_GetLastExamDateByPatientNameAndDepID`(IN patient_name VARCHAR(50), IN dep_id INT,OUT last_exam_date DATETIME)
BEGIN#Routine body goes here...DECLARE patient_id INT; #声明局部变量SELECT patientID INTO patient_id FROM patient WHERE patientName= patient_name;SELECT patient_id; #输出病人的IDSELECT MAX(examDate) INTO last_exam_date FROM prescription WHERE patientID = patient_id AND depID = dep_id;
END
- 调用存储过程
SET @patient_name='夏颖';
SET @dep_id =1;
CALL proc_exam_GetLastExamDateByPatientNameAndDepID(@patient_name, @dep_id, @last);SELECT @last;
6.1.12 存储过程控制语句
与Java语言的流程控制语句类似，MySQL提供的控制语句
- 条件语句
- IF-ELSE IF-ELSE 条件语句
- CASE 条件语句
- 循环语句
- WHILE 循环
- LOOP 循环
- REPEAT循环
- 迭代语句
6.1.13 IF-ELSE 条件语句
IF 条件 THEN 语句列表[ELSEIF 条件 THEN 语句列表][ELSE 语句列表]
END IF;
根据病人的家庭收入，返还补贴不同比例的医疗费用
- 家庭年收入在5000元以下的返还当年总医疗费用的20%
- 家庭年收入在10000以下的返还当年总医疗费用的15%
- 家庭年收入在30000以下的返还总医疗费用的5%
- 30000元以上或未登记的不享受医疗费用返还
- 输入病人编号和年份，计算该患者当年的应返还的医疗费用
CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_CH05_4`(IN patient_ID INT ,IN in_year VARCHAR(50),OUT ou_subsidy FLOAT )
BEGINDECLARE tital_Cost FLOAT;DECLARE totial_income FLOAT;SELECT incomeMoney INTO totial_income FROM income WHERE patientID =patient_ID;SELECT sum(checkItemCost) INTO tital_Cost FROM prescription
INNER JOIN checkitem ON prescription.checkItemID=checkitem.checkItemID
WHERE patientID=patient_ID AND examDate >= CONCAT(in_year,'-01-01')
AND examDate <= CONCAT(in_year,'-12-31');IF totial_income>=0 AND totial_income<5000 THENSET ou_subsidy =tital_Cost*0.2;
ELSEIF totial_income>=5000 AND totial_income<10000 THENSET ou_subsidy =tital_Cost*0.15;
ELSEIF totial_income>=10000 AND totial_income<30000 THENSET ou_subsidy =tital_Cost*0.05;
ELSESET ou_subsidy =0;
END IF;
END
6.1.14 CASE 条件语句
CASEWHEN 条件 THEN 语句列表[WHEN 条件 THEN 语句列表][ELSE 语句列表]
END CASE;
CASE 列名WHEN 条件值 THEN 语句列表[WHEN 条件值 THEN 语句列表][ELSE 语句列表]
END CASE;
使用CASE语句实现返还补贴不同比例的医疗费用
CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_CH05_5`(IN patient_ID INT ,IN in_year VARCHAR(50),OUT ou_subsidy FLOAT )
BEGINDECLARE tital_Cost FLOAT;DECLARE totial_income FLOAT;SELECT incomeMoney INTO totial_income FROM income WHERE patientID =patient_ID;SELECT sum(checkItemCost) INTO tital_Cost FROM prescription
INNER JOIN checkitem ON prescription.checkItemID=checkitem.checkItemID
WHERE patientID=patient_ID AND examDate >= CONCAT(in_year,'-01-01')
AND examDate <= CONCAT(in_year,'-12-31');CASE WHEN totial_income>=0 AND totial_income<5000 THENSET ou_subsidy =tital_Cost*0.2;WHEN totial_income>=5000 AND totial_income<10000 THENSET ou_subsidy =tital_Cost*0.15;WHEN totial_income>=10000 AND totial_income<30000 THENSET ou_subsidy =tital_Cost*0.05;WHEN totial_income>=30000 AND totial_income<0 THENSET ou_subsidy =0;
END CASE;END
在某种情况下（例如，做等值判断），使用第二种写法更加简洁但是，因为CASE后面有列名，功能上会有一些限制
6.1.15 WHILE 循环语句
[label:] WHILE 条件 DO语句列表
END WHILE [label]
- 首先判断条件是否成立。如果成立，则执行循环体
- label为标号，用于区分不同的循环，可省略
- 用在begin、repeat、while 或者loop 语句前
假设有测试表test，有Id字段、Val字段
- 根据输入的行数要求，批量插入测试数据
DECLARE rand_val FLOAT;
WHILE rows > 0 DOSELECT RAND() INTO rand_val;INSERT INTO test VALUES(NULL, rand_val);SET rows = rows - 1;
END WHILE;
6.1.16 LOOP 循环语句
[label:] LOOP语句列表
END LOOP [label] ;
不需判断初始条件，直接执行循环体
LEAVE label ;
遇到 LEAVE 语句，退出循环
批量插3个新的检查项目，检查项目名称为胃镜、肠镜和支气管纤维镜，各项检查的价格均为70元
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_checkitem_insert`( IN checkitems VARCHAR(100))
BEGINDECLARE comma_pos INT;DECLARE current_checkitem VARCHAR(20);loop_label: LOOPSET comma_pos = LOCATE(',', checkitems);SET current_checkitem = SUBSTR(checkitems, 1, comma_pos-1);IF current_checkitem <> '' THENSET checkitems = SUBSTR(checkitems, comma_pos+1);ELSESET current_checkitem = checkitems;END IF;INSERT INTO checkitem(checkItemName,checkItemCost) VALUES(current_checkitem,70);IF comma_pos=0 OR current_checkitem='' THENLEAVE loop_label;# 退出loop_label标识的程序块END IF;END LOOP loop_label;# LOOP循环结束
END
6.1.17 REPEAT 循环语句
[label:] REPEAT语句列表
UNTIL 条件
END REPEAT [label]
- 先执行循环操作再判断循环条件
- 与 LOOP 循环语句相比较相同点
- 不需要初始条件直接进入循环体
- 不同点：REPEAT 语句可以设置退出条件
使用REPEAT循环语句编码实现，根据输入的行数要求，向测试表test中批量插入测试数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_CH05_7`(IN rows INT )
BEGINDECLARE rand FLOAT;REPEATSELECT RAND() INTO rand;INSERT INTO test (val)VALUES(rand);SET rows = rows -1 ;UNTIL rows <= 0 END REPEAT;END
6.1.18 迭代语句
ITERATE label;
- 从当前代码处返回到程序块开始位置，重新执行
- ITERATE关键字可以嵌入到LOOP、WHILE和REPEAT程序块中
输入需增加数据行数，随机产生的测试数据必须大于0.5
CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_CH05_8`(IN rows INT)
BEGIN DECLARE rand FLOAT; random_lbl : REPEATSELECT RAND() INTO rand; IF rand< 0.5 THEN ITERATE random_lbl; END IF;INSERT INTO test (val) VALUES (rand);SET rows=rows-1; UNTIL rows<=0 END REPEAT;
END
6.2 事务
6.2.1 什么是事务
TRANSACTION
- 是将一系列数据操作捆绑成为一个整体进行统一管理机制
- 多个操作作为一个整体向系统提交，要么都执行、要么都不执行
- 是一个不可分割的工作逻辑单元
6.2.2 事务的特性
事务必须具备的属性，简称 ACID 属性
| ACID | 属性描述 |
|---|---|
| 原子性：Atomicity | 事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行 |
| 一致性：Consistency | 当事务完成时，数据必须处于一致状态 |
| 隔离性：Isolation | 并发事务之间彼此隔离、独立，不应以任何方式依赖于或影响其他事务 |
| 持久性：Durability | 事务完成后，它对数据库的修改被永久保持 |
6.2.3 如何创建事务
MySQL中支持事务的存储引擎
-
InnoDB支持事务操作
-
通过 UNDO 日志和 REDO 日志实现对事务的支持
-
UNDO 日志
- 复制事务执行前的数据，用于在事务发生异常时回滚数据
-
REDO 日志
- 记录在事务执行中，每条对数据进行更新的操作
- 当事务提交时，该内容将被刷新到磁盘
-
-
MyISAM不支持事务操作
实现事务的方式
- SQL语句
- 设置自动提交关闭或开启
在执行命令SET autocommit=0，禁止当前会话的自动提交后，后面的SQL语句将作为事务中的语句一同提交
6.2.4 事务处理
开始事务
BEGIN ;
或
START TRANSACTION;
提交事务
COMMIT ;
回滚（撤销）事务
ROLLBACK ;
使用事务实现小王和小张之间的转账操作
USE paycorp;
BEGIN;UPDATE account SET balance=balance-2000 WHERE accountName='小王';UPDATE account SET balance=balance+2000 WHERE accountName='小张';
COMMIT;
小王和小张的总账户余额和转账前保持一致，数据库中数据从一个一致性状态更新到另一个一致性状态
6.2.5 自动关闭和开启事务
-
默认情况下，每条单独的SQL语句视为一个事务
-
关闭默认提交状态后，可手动开启、关闭事务
关闭/开启自动提交
SET autocommit = 0|1;
- 状态值为0：关闭自动提交
- 值为1：开启自动提交
关闭自动提交后，从下一条SQL语句开始将会开启新事务，需使用COMMIT或ROLLBACK语句结束该事务
6.2.6 遵循原则
事务尽可能简短
- 事务启动至结束后在数据库管理系统中保留大量资源，以保证事务的原子性、一致性、隔离性和持久性
- 如果在多用户系统中，较大的事务将会占用系统的大量资源，使得系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃
事务中访问的数据量尽量最少
- 当并发执行事务处理时，事务操作的数据量越少，事务之间对操作数据的争夺就越少
查询数据时尽量不要使用事务
- 对数据进行浏览查询操作并不会更新数据库的数据时，尽量不使用事务查询数据，避免占用过量的系统资源
在事务处理过程中尽量不要出现等待用户输入的操作
- 处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间占用资源，有可能造成系统阻塞
6.3 视图
6.3.1 为什么需要视图
不同的人员关注不同的数据
保证信息的安全性
6.3.2 什么是视图
视图是一张虚拟表
- 表示一张表的部分数据或多张表的综合数据
- 其结构和数据是建立在对表的查询基础上
视图中不存放数据
- 数据存放在视图所引用的原始表中
一个原始表，根据不同用户的不同需求，可以创建不同的视图
- 筛选表中的行
- 防止未经许可的用户访问敏感数据
- 降低数据库的复杂程度
- 将多个物理数据表抽象为一个逻辑数据表
6.3.3 视图的好处
开发人员
- 限制数据检索更容易
- 维护应用程序更方便
最终用户
- 结果更容易理解
- 获得数据更容易
6.3.4 创建 / 查看视图
使用SQL语句创建视图
CREATE VIEW view_name AS<SELECT 语句>;
-- 一般以view_xxx或v_xxx格式命名
使用SQL语句删除视图
DROP VIEW [IF EXISTS] view_name;
-- 删除前先判断 视图是否存在
使用SQL语句查看视图
SELECT 字段1, 字段2, …… FROM view_name;
6.3.5 使用视图注意事项
-
视图中可以使用多个表
-
一个视图可以嵌套另一个视图，但最好不要超过3层
-
对视图数据进行添加、更新和删除操作会直接影响所引用表中的数据
-
当视图数据来自多个表时，不允许添加和删除数据
查看所有视图
USE information_schema;
SELECT * FROM views;
使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询
6.3.6 演示案例
CREATE VIEW VIEW_1
AS
SELECT prescription.*,patient.patientName FROM prescription INNER JOIN patient ON prescription.patientID=patient.patientID;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2qBB3p49-1680505151743)(./assets/image-20230403144209697.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uhkTiT31-1680505151744)(./assets/image-20230403144259240.png)]
6.4 索引
6.4.1 什么是索引
是对数据库表中一列或多列值进行排列的一种结构
-
作用
-
大大提高数据库的检索速度
-
改善数据库性能
-
-
MySQL索引按存储类型分类
-
B-树索引（BTREE）
- InnoDB、MyISAM均支持
-
哈希索引（HASH）
-
-
数据存储在数据表中，而索引是创建在数据库表对象上
-
由表中的一个字段或多个字段生成的键组成
- 通过索引，可以极大的提升数据查询效率，改善数据库的性能
Indexes Use Key Values to Locate Data
（根据索引键查找定位数据行）
6.4.2 常用索引类型
普通索引
- 基本索引类型
- 允许在定义索引的列中插入重复值和空值
唯一索引
- 索引列数据不重复
- 允许有空值
主键索引
- 主键列中的每个值是非空、唯一的
- 一个主键将自动创建主键索引
复合索引
- 将多个列组合作为索引
全文索引
- 支持值的全文查找
- 允许重复值和空值
6.4.3 创建/删除索引
创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column_name [length] …);
-- 唯一索引、全文索引或空间索引，可选
如果创建索引是未指定创建索引的类型，则创建的索引为普通索引通过CREATE INDEX语句无法创建主键索引，主键索引的创建语句
删除索引
ALTER TABLE tablename ADD PRIMARY KEY(column)
删除表时，该表的所有索引同时会被删除
6.4.4 创建索引的指导原则
按照下列标准选择建立索引的列
- 频繁搜索的列
- 经常用作查询选择的列
- 经常排序、分组的列经常用作连接的列（主键/外键）
不要使用下面的列创建索引
- 仅包含几个不同值的列
- 表中仅包含几行
6.4.5 使用索引时注意事项
-
查询时减少使用
*返回表的全部列，不要返回不需要的列 -
索引应该尽量小，在字节数小的列上建立索引
-
WHERE 子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
-
避免在 ORDER BY 子句中使用表达式
-
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理
6.4.6 查看索引 / 删除索引
查看已创建的索引信息
SHOW INDEX FROM table_name;
删除索引
DROP INDEX index_name ON table_name;
删除表时，该表的所有索引将同时被删
除删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除
如果组成索引的所有列都被删除，则整个索引将被删除
6.5 数据库的备份与恢复
6.5.1 为什么进行数据库备份
可能导致数据丢失的意外状况
- 数据库故障
- 突然断电
- 病毒入侵
- 人为误操作
- 程序错误
- 运算错误
- 磁盘故障
- 灾难（如火灾、地震）和盗窃
6.5.2 数据的备份与恢复
数据备份
- 是容灾的基础
- 指为防止系统出现操作失误或系统故障导致数据丢失，而将全部或部分数据集合从应用主机的硬盘或阵列复制到其他的存储介质的过程
- MySQL数据备份的常用方法
- mysqldump 备份数据库
- Navicat 备份数据库
数据恢复
- 是指通过技术手段，将保存在硬盘等存储介质上的丢失的数据进行抢救和恢复的技术
6.5.3 mysqldump 备份数据库
mysqldump 命令——MySQL的客户端常用逻辑备份工具
- 将 CREATE 和 INSERT INTO 语句保存到文本文件
- 属于 DOS 命令
mysqldump [options] database [table1,[table2]…] > [path]/filename.sql
- [options] 的选项参数
- –u username: 表示用户名
- –h host: 表示登录的主机名称，如本机为主机可省略
- –p password: 表示登录密码
- database
- 需备份的数据库名
- [table1,[table2]…]
- 需备份的表名若省略，备份所有表
- [path]/filename.sql
- 备份文件名称
mysqldump是DOS系统下的命令
在使用时，无须进入mysql命令行；否则，将无法执行
示例
以root账户登录到MySQL服务器，使用mysqldump命令备份hospital数据库，将SQL脚本保存到e:\hospital.sql中
mysqldump -uroot -proot hospital > e:\hospital.sql
导出的SQL脚本中两种注释
- 以
--开头：关于SQL语句的注释信息 - 以
/*!开头,*/结尾：是关于MySQL服务器相关的注释
为保证账户密码安全，命令中可不写密码
但是，参数
-p必须有，回车后根据提示写密码
常用参数选项
| 参数 | 描述 |
|---|---|
| -add-drop-table | 在每个CREATE TABLE语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table取消 |
| –add-locks | 该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句 |
| 好处：防止记录被再次导入时，其他用户对表进行的操作 | |
| -t或-no-create-info | 只导出数据，而不添加CREATE TABLE语句 |
| -c或–complete-insert | 在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用 |
| -d或–no-data | 不写表的任何行信息，只转储表的结构 |
备份文件包含的主要信息
- 备份后文件包含信息MySQL服务器及mysqldump工具的版本号
- 备份账户的名称
- 主机信息
- 备份的数据库名称
- SQL语句注释和服务器相关注释
- CREATE和INSERT语句
6.5.4 Navicat 备份数据库
Navicat也可以用于导出数据库的备份脚本
- 右键点选数据库->转储SQL文件->结构和数据…
- 选择导出文件的保存路径和文件名后，便可导出数据库脚本
6.5.5 恢复数据库
在需要恢复数据库数据时，对导出的SQL备份脚本执行导入操作
- 使用mysql命令
- 使用source命令
- 使用Navicat导入数据
6.5.6 mysql 恢复数据库
mysql为DOS命令
mysql –u username –p [dbname] < filename.sql
-- –u 用户名
-- –p 数据库名
-- filename.sql 备份文件名
在执行该语句之前，必须在MySQL服务器中创建新数据库
因为导出的备份文件中只包含表的备份，而不包含创建的库的语句
因此执行导入操作时必须指定数据库名，且该数据库必须存在
6.5.7 source 恢复数据库
除了在命令行中导入数据以外，还可以在数据库已连接状态下导入数据
source filename;
登录MySQL服务后使用
执行该命令前，先创建并选择恢复后的目标数据库
CREATE DATABASE hospitalDB; #创建数据库
USE hospitalDB; #选择要导入数据库的数据库
source e:\hospital.sql #导入数据
# SQL脚本文件后面不要加字符 ;
6.5.8 Navicat 导入数据
Navicat中导入数据的操作步骤
- 右键单击要导入数据的数据库
- 右键单击
运行SQL文件…快捷菜单项 - 在导入窗口，选择要运行的SQL文件
- 点击
开始按钮开始导入数据
![[python]浅谈Flask的SSTI漏洞](https://img-blog.csdnimg.cn/dea56a6d1afb49a1b66e86f75b15f11e.png)
