[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2siD2CaR-1680452775172)(./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助学手册之数据库开发
- 目录
-
- 一、课程软件的安装与卸载
-
- 一、MySQL 数据库设计
-
- 二、MySQL 基础
-
- 三、MySQL 高级(DML 增删改)
-
- 四、MySQL 高级(DQL 查)
-
文章目录
- 玄子Share-BCSP助学手册之数据库开发
- 目录
- 一、课程软件的安装与卸载
- 一、MySQL 数据库设计
- 二、MySQL 基础
- 三、MySQL 高级(DML 增删改)
- 四、MySQL 高级(DQL 查)
一、课程软件的安装与卸载
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-a0DSJlu3-1680452775175)(./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-zRYkur5D-1680452775175)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224010.png)]
- 高级系统设置
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ns4a7N4s-1680452775176)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224026.png)]
- 环境变量
- 下方系统变量,找到
Path
双击进入 - 新建系统变量,变量值为 mysql 安装
bin
目录(实际路径以自己安装时设置为准) - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ib7f9SYO-1680452775176)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224206.png)]
C:\Program Files\mysql-5.7.40\bin
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FXhdBrpK-1680452775176)(./assets/image-20230202162835333.png)]
- 配置好后,保存退出
- 按下
Win + R
然后通过管理员身份打开cmd
- 输入
mysql --version
查看环境是否配置成功
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0k3WqTg7-1680452775177)(./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-7SjRc77W-1680452775177)(./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-pL1gZHQG-1680452775177)(./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-sce3W2ik-1680452775178)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223027.png)]
- 勾选下方
Custom
自定义安装 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ONQYo1Nx-1680452775178)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223154.png)]
- 左边找到
MySQL Server 5.7.40 - X64
点击箭头添加到右边 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0lhaJyPe-1680452775178)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223349.png)]
- 点击
Execute
执行安装即可 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HFOidKaO-1680452775179)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223415.png)]
- 这个界面保持默认状态
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cnQ7SJpS-1680452775179)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223532.png)]
- 设置
MySQL
登录密码(默认设置为root
) - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UE8ymeRA-1680452775179)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330225947.png)]
- 这里的
MySQL57
就是服务器名称,点击Next
下一步 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mSwZeTZi-1680452775180)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223658.png)]
- 安装完毕
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gbC3NdMl-1680452775180)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330223754.png)]
1.3.3 配置环境变量
- 右键此电脑
- 属性
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fYmKBfNM-1680452775180)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224010.png)]
- 高级系统设置
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-snaV8hLC-1680452775181)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330224026.png)]
- 环境变量
- 下方系统变量,找到
Path
双击进入 - 新建系统变量,变量值为 mysql 安装
bin
目录(实际路径以自己安装时设置为准) - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nkJd29lX-1680452775181)(./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-D1A1SHyX-1680452775181)(./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-HHlKgKkf-1680452775181)(./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-DFq3SRhd-1680452775182)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230901.png)]
- 找到MySQL停止服务
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ajGBq0SF-1680452775182)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230926.png)]
- 打开控制面板
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u2vqWAyb-1680452775182)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230617-1680189633074-23.png)]
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZV2AkLrx-1680452775183)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230629.png)]
- 这里的两个软件都要卸载掉
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-egSTSxJs-1680452775183)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330230648.png)]
到这里只是卸载了MySQL软件,MySQL还有许多文件并没有删掉,如果不删除干净,会影响后续安装导致安装失败。
- 删除MySQL在电脑硬盘上物理位置上的所有文件
C:\Program Files (x86)\MySQL
内所有文件- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZXtTJhin-1680452775183)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231218.png)]
C:\ProgramData\MySQL
内所有文件,该文件是默认隐藏的,设置显示后即可见- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UqBsRbhi-1680452775184)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231312.png)]
C:\Documents and Settings\All Users\Application Data\MySQL
内所有文件- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Lx4n1ngS-1680452775184)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231346.png)]
到这里也只是删掉了MySQL所有残余文件,还需要删除MySQL的注册表信息
- 按下
Win + R
打开cmd
窗口输入regedit
打开注册表 - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QMN3zxMz-1680452775184)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231549.png)]
- 删除以下三项内所有信息(可直接粘贴至地址栏)
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V3aAEVZF-1680452775184)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230330231609.png)]
HKEY_LOCAL_MACHINE/SYSTEM/ControlSet001/Services/Eventlog/Application/MySQL
HKEY_LOCAL_MACHINE/SYSTEM/ControlSet002/Services/Eventlog/Application/MySQL
HKEY_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-11Z9eOnJ-1680452775185)(./assets/image-20230205154315466-1680219755644-4.png)]
Navicate 涉及版权无法分享,加封面联系方式获取软件
1.5.3 SqlYog 安装
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WL1ik6qI-1680452775185)(./assets/image-20230331164902240.png)]
SqlYog 涉及版权无法分享,加封面联系方式获取软件
1.5.4 PowerDesigner 安装
PowerDesigner 涉及版权无法分享,加封面联系方式获取软件
一、MySQL 数据库设计
1.1 为什么需要设计数据库
良好的数据库设计
- 降低应用程序的开发难度
- 具备高效的查询效率
- 具备良好的扩展性
糟糕的数据库设计
- 出现数据操作异常、修改复杂、数据冗余等问题
- 影响程序的性能,甚至会导致程序崩溃
数据库设计是对数据库中实体以及这些实体之间的关系进行规划和结构化的过程,当数据库比较复杂时,需要设计数据库
1.1.1 软件项目开发周期中数据库设计
- 需求分析阶段:分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图,确认需求信息的正确和完整
- 详细设计阶段:应用三大范式审核数据库结构
- 代码实现阶段:物理实现数据库,编码实现应用
- 软件测试阶段:……
- 上线部署:……
1.1.2 设计数据库的步骤
收集信息
- 通过对业务人员的访谈等方法,充分了解用户需求,理解数据库需要存储的业务信息(数据)及需要提供的操作
标识实体 (Entity)
- 标识数据库要管理的关键对象或实体,实体一般是名词
标识每个实体的属性(Attribute)
标识实体之间的关系(Relationship)
数据库设计中,不能重复出现含义相同的实体
1.2 为什么使用 E-R 图
1.2.1 E-R 图概念
E-R图也称实体-联系图(Entity Relationship Diagram),提供了表示实体类型、属性和联系的方法,用来描述现实世界的概念模型
符号 | 含义 |
---|---|
□ | 实体,一般是名词 |
○ | 属性,一般是名词 |
♢ | 关系,一般是动词 |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O1srqicJ-1680452775185)(./assets/image-20230331234821123.png)]
- E-R图表示映射关系时,也可以通过添加箭头区分一对多和多对一
- 箭头指向映射关系为一的实体
- 在业务人员与开发人员的沟通中,E-R图能够极大的提高彼此之间的沟通效率
1.3 数据库设计模型转换
1.3.1 关系型数据库设计分类
-
概念数据模型设计
-
逻辑数据模型设计
-
物理数据模型设计
1.3.2 各模型之间的元素对应关系
概念数据模型 | 逻辑数据模型 | 物理数据模型 |
---|---|---|
实体(Entity) | 实体(Entity) | 表(Table) |
属性(Attribute) | 属性(Attribute) | 列(Column) |
标识符(Identifier) | 标识符(Primary Identifier/ Foreign Identifier) | 键(Primary key/ Foreign key) |
关系(Relationship) | 关系(Relationship) | 参照完整性约束(Reference) |
1.3.3 数据模型转换方案
基本转换原理
-
将E-R图中每一个实体转换成一个表,实体的属性转换为表的列,实体的标识符转换为表的主键
-
将实体关系转化为表之间的引用完整性约束
- 根据关系的不同类型,通过外键引用主键的方式有所不同
1.4 实体关系约束转换
1.4.1 一对多关系的两个实体
-
一般会各自转换为一张表,且后者对应的表引用前者对应的表
-
一个表中的主键对应另一个表中可重复的字段
-
主键的值是不能重复的,关联的字段是可以重复的
-
存在一个值对应一个值(一对一)或者一个值对应多个值(一对多)
-
1.4.2 一对一关系的两个实体
- 一般是一个主键对应一个不可重复的字段
1.4.3 多对多关系的两个实体
- 除了将多对多关系中的两个实体各自转换为表外,一般还会创建第3个表,称为连接表
- 将多对多关系划分为两个一对多关系,将这两个表主键都插入到第3个表中
1.5 PowerDesigner 软件
- Sybase 公司开发的一款强大的数据库设计软件
- 开发人员经常使用的数据库建模工具
- 能够使用设计好的数据库模型直接生成SQL建表语句
- 极大的提升数据库的开发效率
推荐使用的版本:PowerDesigner 16.5
PowerDesigner 涉及版权无法分享,加封面联系方式获取软件
1.5.1 绘制数据库模型图
- 创建物理数据模型
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EDc5F9ML-1680452775185)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230331235814.png)]
- 绘制模型图
- 创建表实体
- 添加属性
- 添加实体之间的映射关系
- 两个实体间连线,箭头指向主表
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oNYMr01F-1680452775186)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401000646.png)]
1.5.2 生成 SQL 代码
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CDd9lWFK-1680452775186)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401000958.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-twgjW8p2-1680452775186)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401001019.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gINNm8ez-1680452775187)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401001132.png)]
生成的 SQL文件可直接拿到 Navicate 运行
PowerDesigner中开发数据库模型图时,需要指定该模型图是为数据库管理系统开发的,因为PowerDesigner支持用数据库模型图生成数据库创建脚本的功能
1.6 数据库设计范式
为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则
1.6.1 三大数据库设计范式
-
第一范式
- 第一范式的目标是确保每列保持原子性
- 如果每列都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式(1NF)
-
第二范式
- 第二范式的目标是确保表中的每列都和主键相关
- 要求每个表只描述一件事情
-
第三范式
- 第三范式的目标是确保每列都和主键列直接相关,而不是间接相关
- 如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式(3NF)
1.7 规范化和性能的关系
为满足某种商业目标,数据库性能比规范化数据库更重要
-
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
-
通过在给定的表中插入计算列(如成绩总分),以方便查询
-
在数据规范化同时,要综合考虑数据库的性能
存放商品信息的表
商品名称 | 商品型号 | 单价 | 数量 | 金额 |
---|---|---|---|---|
电视机 | 29英寸 | 2500 | 40 | 100000 |
金额 = 单价 * 数量,是冗余的列
为了提高查询统计速度,以空间换取时间
不要轻易违反数据库设计的规范化原则,如果处理不好,可能会适得其反,使应用程序运行速度更慢
1.8 用三大范式设计表结构
1.8.1 实现思路
-
向各表中插入数据,查看表中的每个属性列是否存在重复、插入异常、更新异常和删除异常
-
对照三大范式的定义,解决表中的异常问题
-
第一范式的目标是确保每列都是不可再分的最小数据单元,查看每列是否都满足第一范式
-
第二范式的每列与主键相关,不相关的放入别的表中,即要求一个表只描述一件事情
-
第三范式要求表中各列必须和主键直接相关,不能间接相关,查看各表,满足第三范式,对于不满足三大范式的表要进行拆分
二、MySQL 基础
2.1 MySQL 简介
MySQL 是一款流行的开源数据库,也是一个关系型数据库管理系统
在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一
2.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
2.1.2 MySQL 的优势
-
运行速度快
-
使用成本低
- 价格:MySQL对多数个人来说是免费的
-
容易使用
- 与其他大型数据库的设置和管理相比,其复杂程度较低,容易学习
-
可移植性强
- 能够工作在众多不同的系统平台上,例如:Windows 、Linux、Unix、MacOS等
-
适用用户广
-
丰富的接口
- 提供了用于C 、C++、Eiffel、Java、Perl、PHP、Python、Rudy和TCL 等语言的API
-
支持查询语言
- MySQL可以利用标准SQL语法和支持ODBC(开放式数据库连接)的应用程序
-
安全性和连接性
-
安全性和连接性; 十分灵活和安全的权限和密码系统,允许主机验证。连接到服务器时,所有的密码均采用加密形式,从而保证了密码安全
-
并且由于MySQL是网络化的,因此可以在因特网网上的任何地方访问,提高数据共享效率
-
2.1.3 MySQL 安装教程
官网 https://downloads.mysql.com/archives/community/
CSDN安装教程 https://blog.csdn.net/qq_62283694/article/details/129869634
安装教程见第一章、课程软件的安装与卸载
2.2 命令行连接 MySQL
-
首先检查 MySQL 服务是否启动
-
电脑按下
Win + R 输入 cmd
打开dos
窗口
2.2.1 启动服务命令
net start mysql
2.2.2 停止服务命令
net stop mysql
2.2.3 登录数据库命令
mysql –h服务器主机地址 –u用户名 –p密码
参数说明:
-h
指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略
-u
登录的用户名
-p
告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cCzdMWP9-1680452775187)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205152901.png)]
2.2.4 查看当前版本和用户信息
SELECT VERSION(),USER();
分号为结束符,不可缺
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YSveTjCL-1680452775187)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205152928.png)]
2.3 数据库管理指令
2.3.1 创建数据库
CREATE DATABASE 数据库名;
Query OK:SQL语句执行成功
1 row affected:操作影响的行数
(0.00 sec):操作执行时间
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vp3VClQJ-1680452775187)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153046.png)]
2.3.2 查看数据库列表
SHOW databases;
student:用户数据库
其他的是系统数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r8HqsvJz-1680452775188)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153258.png)]
2.3.3 选择数据库
USE 数据库名;
使用数据表前,必须先选择该数据库!
ERROR 1049 (42000): Unknown database 'a'
数据库不存在,报错
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-siwmW6Cn-1680452775188)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153925.png)]
2.3.4 删除数据库
DROP DATABASE 数据库名;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FQrRBvuQ-1680452775188)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205154227.png)]
2.4 Navicat 数据库管理工具
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RqgO2oiK-1680452775188)(./assets/image-20230205154315466-1680219755644-4.png)]
Navicat Premium是一款功能强大的、可支持多连接的数据库管理工具
允许在单一程序中同时连接多达7种数据库
-
MySQL
-
MariaDB
-
MongoDB
-
SQL Server
-
SQLite
-
Oracle
-
PostgreSQL
2.4.1 安装教程
官网 https://www.navicat.com/en/products
推荐使用:Navicat Premium 16 最新版
Navicate 涉及版权无法分享,加封面联系方式获取软件
2.4.2 Navicat 连接 MySQL
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-INKTBmPz-1680452775189)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205154815-1680247296828-4.png)]
2.4.3 使用 Navicat 创建数据库
- 通过操作向导创建数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Vo8PXjn-1680452775189)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205160159-1680247296828-6.png)]
- 字符集要选择
utf8
点击 SQL 预览可以查看建表语句
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XtwQcHFs-1680452775189)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205160210-1680247296828-7.png)]
- 通过SQL语句创建数据库
2.5 结构化查询语言
名称 | 解释 | 命令举例 |
---|---|---|
DML (数据操作语言) | 操作数据库中所包含的数据 | INSERT UPDATE DELETE |
DDL (数据定义语言) | 创建和删除数据库对象等操作 | CREATE DROP ALTER |
DQL (数据查询语言) | 对数据库中的数据进行查询 | SELECT |
DCL (数据控制语言) | 控制数据库组件的存取许可、存取权限等 | GRANT COMMIT ROLLBACK |
2.6 MySQL 数据类型
MySQL 中定义数据字段的类型对你数据库的优化是非常重要的
MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型
2.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填充
数值位数大于宽度,显示实际位数
2.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
类型
2.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 类型,可存储的最大长度不同,可根据实际情况选择
2.7 DDL (数据定义语言)
2.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 设置编码
2.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
存储类型的表不支持外键。因此,可以通过建立逻辑关联的方式保证数据的完整性和一致性
2.7.3 删除表
DROP TABLE [IF EXISTS] 表名;
在删除表之前,先使用
IF EXISTS
语句验证表是否存在,执行 DROP TABLE 的表是空表,则可以直接删除如果表中已存储了业务数据,则需和数据库管理员联系,先对数据库数据进行备份并确认,再执行删除表及数据的操作
2.8 MySQL 注释
# 单行注释
-- 单行注释
/**
多行注释
*/
2.9 MySQL 字段的约束及属性
名称 | 关键字 | 说明 |
---|---|---|
主键约束 | PRIMARY KEY(PK) | 设置该字段为表的主键 可唯一标识该表记录 |
外键约束 | FOREIGN KEY(FK) | 用于在两表之间建立关系, 需要指定引用主表的哪一字段 |
自动增长 | AUTO_INCREMENT | 设置该列为自增字段 默认每条自增1 通常用于设置主键 |
非空约束 | NOT NULL | 字段不允许为空 |
默认约束 | DEFAULT | 赋予某字段默认值 |
唯一约束 | UNIQUE KEY(UK) | 设置字段的值是唯一的 允许为空,但只能有一个空值 |
2.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,…… ]
);
2.9.2 外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 FOREIGN KEY(外键字段)REFERENCES 关联表名(关联字段);
# 添加外键约束 添加约束 约束名称 约束类型 (添加外键的字段) 关联 外键(字段)
其他约束的设置方式同理
2.9.3 主键与外键的区别
定义 | 作用 | 个数 | |
---|---|---|---|
主键 | 唯一标识一条记录,不能出现重复且不能出现空值 | 保证数据完整性 | 主键只能有一个 |
外键 | “从表”中对应于“主表”的列,在从表中称为外键或者引用键“主表”中的列应为主键或者唯一键 | 和其他表建立关联,保持数据的一致性 | 一个表可以有多个外键 |
2.9.4 主外键建立后注意事项
- 当主表中没有对应的记录时,不能将记录添加到子表
- 不能更改主表中的值而导致子表中的记录孤立
- 子表存在与主表对应的记录,不能从主表中删除该行
- 删除主表前,先删子表
2.10 MySQL 注释与字符集编码
2.10.1 注释
-
COMMENT关键字
-
在表结构中可以看到,与代码注释不同、
CREATE TABLE test (
id int(11) UNSIGNED COMMENT '编号'
)COMMENT='测试表';
2.10.2 设置字符集编码
-
默认情况下,MySQL所有数据库、表、字段等使用 MySQL 默认字符集
-
为了存储独特的数据,可在创建表时指定字符集
CREATE TABLE [IF NOT EXISTS] 表名(
#……
)CHARSET = 字符集名;
2.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 = '检查项目表';
2.11 MySQL 查看表
2.11.1 查看数据库中的表
SHOW TABLES;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KrMUMt3q-1680452775190)(./assets/image-20230331173945642.png)]
2.11.2 描述表结构
DESCRIBE 表名;
DESC 表名;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sRQDHIAb-1680452775190)(./assets/image-20230331173500098.png)]
为了避免
DOS
窗口出现中文乱码,可执行SET NAMES gbk;
2.12 MySQL 的存储引擎
存储引擎,是存储数据的核心组件,指定了表的存储类型和存储形式
2.12.1 存储引擎的类型
- InnoDB
- MyISAM
- Memory
- CSV
- 等9种
2.12.2 查看系统所支持的存储引擎类型
SHOW ENGINES;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2gaCeQLA-1680452775190)(./assets/image-20230331232103064.png)]
2.12.3 常用的 MySQL 存储引擎
InnoDB 存储引擎
- 支持具有提交、回滚和崩溃恢复能力的事务控制
MyISAM 存储引擎
- 不支持事务,也不支持外键约束,访问速度比较快
2.12.4 InnoDB 和 MyISAM 存储引擎比较
功能 | InnoDB | MyISAM |
---|---|---|
事务处理 | 支持 | 不支持 |
外键约束 | 支持 | 不支持 |
表空间大小 | 较大 | 较小 |
数据行锁定 | 支持 | 不支持 |
2.12.5 适用场合
- 使用 MyISAM:不需事务,空间小,以查询访问为主
- 使用InnoDB:多删除、更新操作,安全性高,事务处理及并发控制
2.12.6 查看当前存储引擎
MySQL 5.7 版本默认的存储引擎是InnoDB
- 查看当前默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine%';
-- default_storage_engine% 默认存储引擎
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OJYyNsJ1-1680452775190)(./assets/image-20230331232525679.png)]
2.12.7 修改存储引擎
- 修改
my.ini
配置文件
default-storage-engine=MyISAM
-- MyISAM 改为其他类型的存储引擎
修改配置文件后,需要重启MySQL服务设置才会生效
2.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;
2.12.9 数据表的存储文件
MyISAM 类型表文件
-
*.frm
表结构定义文件 -
*.MYD
数据文件 -
*.MYI
索引文件
InnoDB 类型表文件
-
*.frm
表结构定义文件 -
ibdata1
文件
2.12.10 存储位置
因操作系统而异,可查my.ini
datadir="C:\ProgramData\MySQL\MySQL Server 5.7\Data"
innodb_data_home_dir="D:/MySQL Datafiles/"
2.13 MySQL 系统帮助
HELP 查询内容;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BnzkDCvW-1680452775191)(./assets/image-20230331233452271.png)]
HELP contents;
#查看帮助文档目录列表
HELP Data Definition;
#查看数据定义
HELP CREATE DATABASE;
#查看CREATE DATABASE的帮助信息
三、MySQL 高级(DML 增删改)
3.1 DML 数据操纵语言
DML(Data Manipulation Language)DML对数据库中表记录的执行操作
-
插入(INSERT)
-
插入单行数据
-
插入多行数据
-
将查询结果插入到新表
-
-
更新(UPDATE)
-
删除(DELETE)
软件开发人员日常使用最频繁的操作,必备技能!!!
3.2 插入数据
- INSERT 一次向表中插入一条记录
INSERT INTO 表名 [(字段名列表)] VALUES (值列表);
# 增
INSERT into gradeinfo (grade) VALUES ('ACCP');
字段名是可选的,如省略,则依次插入所有字段
多个列表和多个值之间使用逗号分隔
值列表和字段名列表一 一对应
如果插入表中的部分字段数据,则字段名列表必填
每个数据值的数据类型、精度和小数位数必须与相应的列匹配
不能为标识列指定值(主键)
如果在设计表的时候就指定了某列不允许为空,则必须插入数据
如果字段具有默认值,可以使用
DEFAULT
关键字来代替插入的数值
- INSERT 一次向表中插入多行数据
INSERT INTO 表名 [(字段名列表)] VALUES (值列表1), (值列表2), (值列表n);
# 增
INSERT INTO gradeinfo (grade) VALUES ('ACCP'),('BCSP');
为避免表结构发生变化引发的错误,建议插入数据时写明具体字段名!
- INSERT 将查询结果插入到新表
INSERT INTO 表名(字段1,字段2 , ……) SELECT 字段1,字段2 , …… FROM 原表;
# 事先创建且与插入数据字段相符
CREATE TABLE patient_address (SELECT patientName, address FROM patient ) ;
# 无须事先创建
如果新表已存在,将会报错!
3.3 更新数据
- UPDATE 更新数据记录
UPDATE 表名 SET 字段1=值1, 字段2=值2, …, 字段n=值n [WHERE 条件];
# 更新表数据
UPDATE gradeinfo SET gradeinfo.grade='BCSP' WHERE gradeinfo.id=2;
3.4 删除数据
- DELETE 删除数据记录
DELETE FROM 表名 [WHERE条件];
# 删除表信息
DELETE FROM patient WHERE patientID =1
DELETE条件删除语句中,如果未指定WHERE条件语句,则将删除表中的所有数据
DELETE FROM gradeinfo WHERE gradeinfo.id=1;
- TRUNCATE 格式化表记录
TRUNCATE TABLE 表名;
# 格式化表
TRUNCATE TABLE gradeinfo;
使用 TRUNCATE 语句删除表中记录后,将重置自增列,表结构及其字段、约束、索引保持不变,执行速度比 DELETE 语句快
3.5 比较 DROP、DELETE 与 TRUNCATE 语句
DROP | DELETE | TRUNCATE | |
---|---|---|---|
类型 | DDL 会隐式提交,不能回滚 | DML 每次从表中删除一行数据的同时将改行的删除操作记录在redo和undo表空间中,以便进行回滚和重做操作 需要手动提交操作才能生效,可通过ROLLBACK撤销操作 | DDL 会隐式提交,不会记录日志,不能回滚 |
功能 | 删除表结构及所有数据,并将标所占用的空间全部释放 | 可根据条件删除表中满足条件的数据,如果不指定WHERE子句,则删除表中所有记录 | 删除表中所有记录,并将重建表结构 |
-
执行速度
- 一般来说,DROP>TRUNCATE>DELETE
-
使用 DROP、TRUNCATE 语句时,要慎重
-
如果使用 DELETE 语句删除表中部分数据要带上 WHERE 子句,且要注意表空间要足够大
-
如果要删除表,使用 DROP 语句
-
如果要保留表但删除表中所有数据,如果与事务无关可以使用 TRUNCATE 语句
-
如果与事务有关,则使用 DELETE 语句
四、MySQL 高级(DQL 查)
4.1 DQL 数据查询语言
Data Query Language,用于查询数据库的表中数据,是数据库中最为核心的语言,使用频率最高
4.2 基础查询
4.2.1 查询全部行和列
SELECT * FROM 表名称
SELECT * FROM patient
# 查询 patient 病人表中的所有字段 * 表示所有字段
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gFwZXQU0-1680452775191)(./assets/image-20230402180217861.png)]
4.2.2 查询部分列
SELECT 字段1,字段2,字段n FROM 表名称
SELECT patientID,patientName,gender FROM patient
# 查询 patient 病人表中的部分字段(多个字段之间使用,分隔)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3hbQsPnA-1680452775191)(./assets/image-20230402180548009.png)]
有的时候我们不想让用户看到所有的信息,比如用户密码这类的隐私信息,我们就可以通过写具体的字段,就只查询部分信息
在开发中,推荐使用以查询部分列的方式代替
SELECT *
查询全部列,可提高执行效率并养成良好编码习惯
4.2.3 AS 别名
- 使用 AS 关键字,可以为表、字段、查询结果指定别名
SELECT 字段列表 FROM 表名 AS 表的别名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZnMQW1qT-1680452775191)(./assets/image-20230402185049461.png)]
SELECT 字段1 AS 别名,字段2 FROM 表名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l1VsOFOz-1680452775192)(./assets/image-20230402185113942.png)]
- 使用空格,简便的方法
SELECT 字段列表 FROM 表名 表的别名;
# 中间是空格
为某个表命名了别名后,在 SELECT 语句中出现该表的字段需要指定表名时,就必须统一使用该表的别名;否则将产生语法错误
4.3 运算符
运算符是一种符号,用来进行列间或变量之间的比较和数学运算
MySQL 常的运算符
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
4.3.1 算术运算符
运算符 | 作用 |
---|---|
+ | 加法 |
- | 减法 |
* | 乘法 |
/ 或 DIV | 除法 |
% 或 MOD | 取余 |
在除法运算和模运算中,如果除数为0,将是非法除数,返回结果为 NULL
4.3.2 比较运算符
符号 | 描述 | 备注 |
---|---|---|
= | 等于 | |
<>, != | 不等于 | |
> | 大于 | |
< | 小于 | |
<= | 小于等于 | |
>= | 大于等于 | |
BETWEEN | 在两值之间 | |
NOT BETWEEN | 不在两值之间 | |
IN | 在集合中 | |
NOT IN | 不在集合中 | |
<=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时,其所得值为1;而当一个操作码为NULL时,其所得值为0 |
LIKE | 模糊匹配 | |
REGEXP 或 RLIKE | 正则式匹配 | |
IS NULL | 为空 | |
IS NOT NULL | 不为空 |
4.3.3 逻辑运算符
运算符号 | 作用 |
---|---|
NOT 或 ! | 逻辑非 |
AND | 逻辑与 |
OR | 逻辑或 |
XOR | 逻辑异或 |
逻辑运算符用来判断表达式的真假。如果表达式是真,结果返回 1。如果表达式是假,结果返回 0
4.3.4 位运算符
运算符号 | 作用 |
---|---|
& | 按位与 |
| | 按位或 |
^ | 按位异或 |
! | 取反 |
<< | 左移 |
>> | 右移 |
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数,进行位运算。然后再将计算结果从二进制数变回十进制数
4.3.5 运算符优先级
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RyUOQYdg-1680452775192)(./assets/1011652-20170416163043227-1936139924.png)]
4.4 条件查
如果我们查询时,并不想显示所有的用户信息,只要查出满足指定条件的部分用户,或者精确到某一个用户的信息,就需要使用到 WHERE 关键字对 sql 语句增加查询条件
4.4.1 精确查询
SELECT * FROM patient WHERE patient.patientID = 1
# 通过患者 ID 精确查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-32mEc43Q-1680452775193)(./assets/image-20230402181442241.png)]
这里我们就把患者 ID 作为查询条件,查询出患者 ID 为 1 的患者信息
4.4.2 单条件查询
SELECT * FROM patient WHERE patient.patientID < 10
# 查询病人 ID 小于 10 的患者信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hdzj5O6y-1680452775193)(./assets/image-20230402182833603.png)]
SELECT * FROM patient WHERE patientName ='玄子'
# 查询所有病人姓名为玄子的患者信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cGpu9H0P-1680452775193)(./assets/image-20230402224936557.png)]
4.4.3 多条件查询
-
如果查询条件中包含的条件不止一个,则条件根据逻辑关系的不同可以分为
与
条件和或
条件两种 -
与
条件表示要求同时满足两个以上的条件,使用 AND 关键字可以构造与
条件。 -
或
条件表示几个条件中只需满足其中一个的条件,使用 OR 关键字可以构造或
条件 -
AND
SELECT * FROM patient WHERE patientName ='玄子' AND address='长春市'
# 多条件查询 姓名为 玄子 且住址为 长春市
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pT37B9Zw-1680452775193)(./assets/image-20230402230857608.png)]
- OR
SELECT * FROM patient WHERE patientName ='玄子' OR address='长春市'
# 多条件查询 姓名为 玄子 '或者' 住址为 长春市
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QK2zuxrF-1680452775194)(./assets/image-20230402231059106.png)]
姓名 和 住址 这两个条件满足一个即可
4.5 排序查
4.5.1 升序排序
SELECT * FROM patient ORDER BY birthDate
# 通过病人的出生年月进行排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-58oB4Z1M-1680452775194)(./assets/image-20230402183208506.png)]
这里我们使用 ORDER BY 关键字,对病人的出生年月进行排序,默认是从小到大,也就是
升序排列
4.5.2 降序排序
SELECT * FROM patient ORDER BY birthDate DESC
# 通过病人的出生年月进行降序排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-drmnYbE8-1680452775194)(./assets/image-20230402183508864.png)]
使用降序排列只需要 在最后加上一个
DESC
关键字即可升序排序后面其实也有一个关键字
ASC
不过ORDER BY
默认就是升序排列,所以ASC
加不加都可以
4.5.3 多字段排序
SELECT * FROM patient ORDER BY birthDate ASC,patientID DESC
# 通过病人的出生年月和ID进行多字段排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lNK0RUnE-1680452775195)(./assets/image-20230402183948955.png)]
如果想要对多个字段进行排序,可直接使用
,
分隔。然后写第二个需要排序的字段和排序方式即可
4.6 常用函数
MySQL 中将一些常用的数据处理操作封装起来,作为函数提供给程序员使用,可以提高程序员开发效率
MySQ L支持的常用函数
-
字符串函数
-
时间日期函数
-
聚合函数
-
数学函数
4.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. |
4.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
4.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 函数
4.7 分组查
4.7 .1 GROUP BY
如果我们想要对,查询结果分组,比如按照,考试的科目ID 进行分组。就要使用GROUP BY 关键字对 subjectNo
进行分组,首先我们可以看到成绩表中有四个字段分别是studentNo 学生学号
,subjectNo 课程编号
,examDate 考试日期
,studentResult 考试成绩
。其中subjectNo
有三个编号我们就可以对其分组查询查出这三个编号
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f59aLIip-1680452775195)(./assets/image-20230402190256908.png)]
- 分组考试成绩表中的科目编号
SELECT subjectNo FROM result GROUP BY subjectNo
# 分组考试成绩表中的科目编号
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JvgUGdg5-1680452775195)(./assets/image-20230402190524327.png)]
这样分组就只能看到成绩表中的科目 ID,并不能查询其他字段,所以我们通常搭配
聚合函数
使用
- 分组科目编号,查询对应科目考试成绩的平均分
SELECT subjectNo,AVG(studentResult) FROM result GROUP BY subjectNo
# 分组科目编号,查询对应科目考试成绩的平均分
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V0h7RxlI-1680452775196)(./assets/image-20230402231713206.png)]
3.3.5 HAVING
我们在使用 GROUP BY 分组后还想要进一步筛选查询结果,就需要使用 HAVING 关键字进一步筛选,这里并不能使用 WHERE
- 分组考试成绩表中的科目编号,后再次筛选
SELECT subjectNo,AVG(studentResult) AS avg FROM result GROUP BY subjectNo HAVING avg >60
# 分组科目编号,查询对应科目考试成绩的平均分 后继续筛选 成绩在 60 分以上的 科目编号
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7r34uYnf-1680452775196)(./assets/image-20230402231957986.png)]
4.8 分页查
SELECT <字段名列表>
FROM <表名或视图>
[WHERE <查询条件>]
[GROUP BY <分组的字段名>] [HAVING <条件>]
[ORDER BY <排序的字段名> [ASC 或 DESC]]
[LIMIT [位置偏移量,]行数];
4.8.1 下标 5 开始查询 5 条
SELECT * FROM patient LIMIT 5,5
# 位置偏移量:第1条记录的位置偏移量是0,第2条记录的位置偏移量是1……
# 行数:显示记录的条数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mgnpd9w8-1680452775196)(./assets/image-20230402233340898.png)]
使用LIMIT子句时,第1条记录的位置是0!
LIMIT 子句经常和 ORDER BY 子句一起使用,即先对查询结果进行排序,再根据 LIMIT 子句的参数返回指定的数据
4.8.2 每页 m 个数据查第 n 页
SELECT * FROM patient LIMIT (n-1)*m,m
# 每页 m 个第 n 页
4.9 模糊查
4.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”等 |
4.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-BSGsAa5S-1680452775197)(./assets/image-20230402234207243.png)]
匹配的字符串必须加单引号或者双引号
默认情况下,LIKE 关键字匹配字符串时候不区分大小写,可以在 LIKE 关键字后添加 BINARY 关键字来区分大小写。
如果查询内容中有通配符字符,就需要加转义字符
\
4.9.3 BETWEEN
SELECT 字段 FROM 表名 WHERE 列名 [NOT] BETWEEN 起始值 AND 最终值
SELECT * FROM patient WHERE patient.patientID BETWEEN 5 AND 15
# 查询 ID 在 5 到 15 的患者信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p8NjsdG2-1680452775197)(./assets/image-20230402235352933.png)]
NOT 可选参数,表示取反
查询指定范围内所有值,包括起始值和最终值
4.9.4 IS NULL
IS NULL关键字判断该列的值是否为空值,空值不是空字符串
SELECT 字段 FROM 表名 WHERE 列名 IS [NOT] NULL
SELECT * FROM patient WHERE address IS NOT NULL
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9q7ck4wv-1680452775197)(./assets/image-20230402235909456.png)]
NOT 可选参数,表示取反
4.10 子查询
子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询
SELECT …… FROM 表1 WHERE 字段1 比较运算符 (子查询);
SELECT * FROM patient WHERE patientID IN(1,2,3,4,5,9)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GEOq0AVp-1680452775197)(./assets/image-20230403000309493.png)]
SELECT * FROM patient WHERE patientID IN(SELECT patientID FROM patient WHERE patient.patientID BETWEEN 5 AND 15)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RLCZII3H-1680452775198)(./assets/image-20230403000418635.png)]
先执行子查询,返回所有来自子查询的结果
再执行外围的父查询,返回查询的最终结果
将子查询和比较运算符联合使用
必须保证子查询返回的值不能多于一个
4.10.1 IN 子查询
- 如果子查询的结果为多个值,就会导致代码报错
- 解决方案就是使用 IN 关键字,将 = 替换成 IN
SELECT …… FROM 表名 WHERE 字段名 IN (子查询);
4.10.2 NOT IN 子查询
-
与
in
一样,查询结果有多条使用 -
获取的值是不包含在查询结果里面的值
SELECT …… FROM 表名 WHERE 字段名 NOT IN (子查询);
4.10.3 EXISTS
DROP TABLE IF EXISTS temp;
CREATE TABLE log (
… … #省略建表语句
) ;
4.10.4 EXISTS 子查询
- 子查询有返回结果: EXISTS 子查询结果为 TRUE
- 子查询无返回结果: EXISTS 子查询结果为 FALSE,外层查询不执行
SELECT …… FROM 表名 WHERE EXISTS (子查询);
4.10.5 NOT EXIST 子查询
- 与 EXISTS 子查询相反
- 子查询有返回结果: NOT EXIST子查询结果为 FALSE
- 子查询无返回结果: NOT EXIST子查询结果为 TRUE,外层查询执行
4.10.6 子查询小结
当一个查询是另一个查询的条件时,称之为子查询
-
任何允许使用表达式的地方都可以使用子查询
-
嵌套在父查询SELECT语句的子查询,可包括
-
SELECT 子句
-
SELECT (子查询) [AS 列别名] FROM 表名;
-
FROM 子句
-
SELECT * FROM (子查询) AS 表别名;
-
WHERE 子句
-
GROUP BY 子句
-
HAVING 子句
-
4.10.7 子查询注意事项
通常,将子查询放在比较条件的右边以增加可读性
子查询可以返回单行或多行数据,此时要选择合适的关键字
-
子查询的返回是单行数据时,比较条件中可以使用比较运算符
-
子查询的返回是多行数据时,比较条件中需要使用IN或 NOT IN 关键字
-
如果判断子查询是否有数据返回时,需要使用 EXISTS 或 NOT EXISTS 关键字
只出现在子查询中、而没有出现在父查询中的列不能包含在输出列中
4.11 连接查
4.11.1 JOIN
JOIN 按照功能大致分为如下三类
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录
- **LEFT JOIN(左连接):**获取左表所有记录,即使右表没有对应匹配的记录
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录
4.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-horlEBu7-1680452775198)(./assets/image-20230403001247130.png)]
4.11.3 外连接
特点
- 外连接可以分为主表和从表
- 主表的数据会被完全显示出来
- 从表中只显示满足连接条件的数据
- 外连接结果一般会比主表和从表中数据量最小的表中的数据多
- 左外连
SELECT * FROM prescription
LEFT JOIN patient ON prescription.patientID=patient.patientID
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-srd5oXFK-1680452775198)(./assets/image-20230403001525010.png)]
- 右外连
SELECT * FROM prescription
RIGHT JOIN patient ON prescription.patientID=patient.patientID
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-89KWUMAS-1680452775198)(./assets/image-20230403001641747.png)]