BCSP Java 开发之 Java 数据库编程 MySQL

最新推荐文章于 2023-05-09 18:34:18 发布
最新推荐文章于 2023-05-09 18:34:18 发布
阅读量630 收藏
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_72817617/article/details/129963591
版权

BCSP Java 开发之 Java 数据库编程 MySQL

一、关系型数据库设计

1.1 数据库设计的重要性及定义

1.1.1 数据库设计的重要性

实际开发过程中，当存储量较大时，表的数量较多时，表的关系就会比较复杂。

此时就要有限考虑数据库的设计，并通 数据库三大范式 检验设计的合理性

1、失败的数据库设计造成的后果

  • 程序运行阶段会出现操作异常
  • 修改数据时会比较复杂
  • 导致数据冗余
  • 表与表之间的关系会变的比较复杂
  • 影响程序性能，甚至造成程序崩溃

2、优秀的数据库设计带来的好处

  • 降低应用程序开发的难度
  • 具有高效的查询效率
  • 具有良好的扩展性

1.1.2 数据库设计的定义

  • 根据用户的需求，在数据库管理系统上，设计数据库的结构和建立数据的过程
  • 建立数据库及其应用系统的技术，是信息系统开发和建设中的核心技术
  • 数据库设计主要包含 :需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库的实施和数据库的运行和维护。

1.1.3 数据库设计：需求分析

  • 软件生存周期中的一个重要环节
  • 分析系统在功能上需要“实现什么”，而不是考虑如何去“实现”
  • 将客户提出的要求进行分析以及整理
  • 对功能性需求的补充，软件需求分析的内容中还应该包括一些非功能需求
  • 设计限制条件，通常是对一些设计或实现方案的约束说明

1.1.4 数据库设计：概念模型 E-R 图

数据库需求分析阶段，了解系统需要实现的业务功能。

此时需要进一步的进入概要设计阶段，为了更加形象直观的体现数据库设计意图

数据库设计也有类似的图形化展示工具 E-R图 实体-练习图

通过一些具有特定含义的图形符号，提供图形化展示实体、属性和联系的方法

1、实体

  • E-R 图当中，使用矩形表示实体
  • 实体相当于数据表

2、属性

  • E-R 图中，椭圆形表示实体的属性
  • 属性与实体之间使用实线相连
  • 表示此属性归属于哪个实体
  • 属性就相当于数据表中的字段

3、联系

  • 联系指在需求分析阶段标识的实体之间的关系，使用菱形表示
  • 表示两个实体中属性的关系
  • 类属于表中的主外键关系

示例

E-R图示例

4、实体间的映射关系

  • 绘制 E-R 图时，需要表现出实体之间的映射关系

  • 即一个实体关联其他实体的个数

  • 映射关系也成为了映射基数

  • 三种映射关系

    • 一对一联系：A 中的一个实体最多与 B 中的一个实体关联
    • 一对多联系：A 中的一个实体可以与 B 中的任意数量实体关联，B 中的一个实体最多与 A 中的一个实体关联
    • 多对多联系：A 中的一个实体可以与 B 中的任意数量实体关联，反之亦然

  • 在绘制 E-R 图时，通过在连接线上分别添加数字表示映射关系 (一用1表示，多用 N 表示)

1.1.5 物理模型：数据库模型图

  • 绘制完 E-R 图后，应当对数据局库进行详细的设计，数据库的详细设计，采用数据库模型图来实现

  • 将 E-R 图转为数据库党总的多张表，并标识各表的主外键关系

  • 通过图形化的方式，显示数据库存储的信息，信息的数据类型，以及各表之间的关系

  • Navicat 在设计好数据库后，可以一键生成数据库模型图

1.2 数据库规范设计

在设计数据库的过程中，如果开发者缺乏在设计阶段对数据库评价的手段，导致数据库设计的不合理

则会导致系统性能低下甚至不稳定

1.2.1 常见数据库设计问题

数据库设计问题大部分是数据冗余引起的

下方是一张存在大量数据冗余的数据表

存在大量数据冗余的数据表

这种数据冗余的数据表，对于查询来说很方便，只需要一次简单的查询即可，但是当对于数据库的更新操作 (增、删、改) 时，就会导致数据的更新异常

  • 插入异常
    • 当在表中增加一条数据时，假设医生想要录入 “凝血五项”，但只录入了一个 “凝血”，这明显是一个不规范的检查名称
    • 当出现这样的情况时，就会导致数据库中记录的内容越来越不一致
    • 不利于系统维护和管理
  • 删除异常
    • 如果要删除表中的 “尿常规” 检查，因为表中只存在一条 “尿常规” 检查记录，则当这条记录被删除时，数据库当中就再也找不到这条检查记录了
    • 也就是说，数据库层面医院则不能再做这项检查

二、Navicat 安装及 MySQL 的安装与卸载

2.1 MySQL 压缩包安装教程

2.1.1 下载链接

官方 https://downloads.mysql.com/archives/community/

度盘 https://pan.baidu.com/s/1fuGdLIDXx2-2b5mEdqEh3g?pwd=xzsr

2.1.2 安装 MySQL

注意选择版本号为5.7.40

下载完后我们会得到一个以 MySQL 开头命名的压缩包，我们需要把这个压缩包解压到本地

这里可以把 MySQL 文件名后面的-winx64删除掉（度盘下载的压缩包我已经删除过了）

推荐将文件解压到 C 盘的 Program Files 文件下，即解压路径为C:\Program Files\mysql-5.7.40

2.1.3 添加配置文件

在C:\Program Files\mysql-5.7.40文件夹内新建一个文本文档，【强制】命名为：my.ini

注意开启显示文件拓展名设置

搜狗截图20230331171045

如果在度盘下载的压缩包，我在压缩包中已经写入过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前面的路径替换即可

文本里的代码前面是没有数字的

2.1.4 配置环境变量

  1. 右键此电脑
  2. 属性
  3. image-20230331171303104
  4. 高级系统设置
  5. image-20230331171342976
  6. 环境变量
  7. 下方系统变量，找到Path双击进入
  8. 新建系统变量，变量值为 mysql 安装bin目录（实际路径以自己安装时设置为准）
  9. image-20230331171610493
C:\Program Files\mysql-5.7.40\bin

image-20230331171626734

  1. 配置好后，保存退出
  2. 按下Win + R然后通过管理员身份打开cmd
  3. 输入mysql --version查看环境是否配置成功

image-20230331171704495

2.1.5 安装 MySQL 服务

  1. 按下键盘Win + R打开运行输入cmd以管理员身份运行
  2. cd 到 mysql 文件的 bin 目录中
cd /d C:\Program Files\mysql-5.7.40\bin
  1. 输入mysqld -install命令安装 MySQL 服务
  2. 继续输入mysqld --initialize-insecure初始化 data 目录
  3. 输入net start mysql启动 MySQL 服务
  4. 成功启动后，输入 mysql -uroot登录 MySQL
  5. 成功登录之后，输入下面代码
update mysql.user set authentication_string=password('root') where user='root' and Host = 'localhost';

image-20230331171732266

  1. 这里的root就是登录密码，账户名也是root或者你可以把密码改成自己喜欢的
  2. 再输入flush privileges;刷新一下权限
  3. 最后输入exit退出 MySQL 即可

2.1.6 删除配置文件代码

退出后回到my.ini配置文件中，用#注释掉或者直接删除掉最后一行代码skip-grant-tables然后保存退出

到这里就已经成功下载并配置好了 MySQL

2.1.7 验证 MySQL 配置

  1. 命令行先输入net stop mysql关闭 MySQL 服务

  2. 在输入net start mysql开启 MySQL 服务

  3. 这里报错的请自己到服务中手动打开 MySQL 服务后重新尝试

  4. 登录数据库

mysql -uroot -proot

mysql -u账户名 -p密码

注意密码前后不能有空格

image-20230331171803706

最后可以输入一个查询版本信息的SQl语句玩一下：select version(),user(); 代码后面有分号

2.2 MySQL 安装器安装教程

2.2.1 下载链接

官方 https://downloads.mysql.com/archives/installer/

度盘 https://pan.baidu.com/s/1fuGdLIDXx2-2b5mEdqEh3g?pwd=xzsr

2.2.2 安装 MySQL

注意选择版本号为5.7.40

下载完后我们会得到一个以 MySQL 开头命名的msi安装器，直接双击运行安装

  1. 提示是否更新，点击NO即可
  2. image-20230331172047882
  3. 勾选下方Custom自定义安装
  4. image-20230331172117327
  5. 左边找到MySQL Server 5.7.40 - X64点击箭头添加到右边
  6. image-20230331172140621
  7. 点击Execute 执行安装即可
  8. image-20230331172201463
  9. 这个界面保持默认状态
  10. image-20230331172238884
  11. 设置MySQL登录密码(默认设置为 root)
  12. image-20230331172300758
  13. 这里的MySQL57就是服务器名称，点击Next下一步
  14. image-20230331172326419
  15. 安装完毕
  16. image-20230331172351158

2.2.3 配置环境变量

  1. 右键此电脑
  2. 属性
  3. image-20230331172449048
  4. 高级系统设置
  5. image-20230331172725611
  6. 环境变量
  7. 下方系统变量，找到Path双击进入
  8. 新建系统变量，变量值为 mysql 安装bin目录（实际路径以自己安装时设置为准）
  9. image-20230331172750973
C:\Program Files\MySQL\MySQL Server 5.7\bin
  1. 配置好后，保存退出
  2. 按下Win + R然后通过管理员身份打开cmd
  3. 输入mysql --version查看环境是否配置成功

image-20230331172904283

2.2.4 验证 MySQL 配置

  1. 再次通过按下Win + R打开cmd窗口
  2. 输入账号密码mysql -uroot -proot（-u后的root为管理员账号，-p后的root为之前设置的MySQL登录密码，没有空格直接跟密码）
  3. image-20230331173001515

最后可以输入一个查询版本信息的SQl语句玩一下：select version(),user(); 代码后面有分号

2.3 MySQL 卸载教程

2.3.1 压缩包卸载

压缩包安装方式安装的MySQL，卸载就直接删除掉解压后的MySQL文件夹即可，然后删除配置的 MySQL 环境变量

2.3.2 安装器卸载

  1. 按下Win + R打开cmd窗口输入services.msc打开服务
  2. image-20230331173146419
  3. 找到MySQL停止服务
  4. image-20230331173206428
  5. 打开控制面板
  6. image-20230331173249898
  7. image-20230331173308298
  8. 这里的两个软件都要卸载掉
  9. image-20230331173328779

到这里只是卸载了MySQL软件，MySQL还有许多文件并没有删掉，如果不删除干净，会影响后续安装导致安装失败。

  1. 删除MySQL在电脑硬盘上物理位置上的所有文件
  2. C:\Program Files (x86)\MySQL内所有文件
  3. image-20230331173422056
  4. C:\ProgramData\MySQL内所有文件，该文件是默认隐藏的，设置显示后即可见
  5. image-20230331173553973
  6. C:\Documents and Settings\All Users\Application Data\MySQL内所有文件
  7. image-20230331173636141

到这里也只是删掉了MySQL所有残余文件，还需要删除MySQL的注册表信息

  1. 按下Win + R打开cmd窗口输入regedit打开注册表
  2. image-20230331173805356
  3. 删除以下三项内所有信息（可直接粘贴至地址栏）
  4. image-20230331173827529
  5. HKEY_LOCAL_MACHINE/SYSTEM/ControlSet001/Services/Eventlog/Application/MySQL
  6. HKEY_LOCAL_MACHINE/SYSTEM/ControlSet002/Services/Eventlog/Application/MySQL
  7. HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Eventlog/Application/MySQL
  8. 到此MySQL算是彻底卸载完毕

2.4 MySQL管理工具 Navicate / SqlYog 安装

2.4.1 下载链接

Navicate官网 https://www.navicat.com/en/products

SqlYog官网 https://sqlyog.en.softonic.com/

度盘 https://pan.baidu.com/s/1fuGdLIDXx2-2b5mEdqEh3g?pwd=xzsr

image-20230331174024614

Navicat Premium 是一款功能强大的、可支持多连接的数据库管理工具

Navicate / SqlYog 涉及版权无法分享，加封面联系方式获取软件

2.4.2 使用 Navicat 连接 MySQL

image-20230331174108842

image-20230331174238843

2.4.3 使用 Navicat 创建数据库

通过操作向导创建数据库

搜狗截图20230331174437

image-20230331175121015

  • 字符集要选择utf8点击 SQL 预览可以查看建库语句

image-20230331175140510

通过SQL语句创建数据库

image-20230331175216937

image-20230331175232866

2.4.4 SqlYog 使用

SqlYog 与 Navicate 操作同理

三、MySQL 入门

3.1 MySQL 简介

image-20230331175443959

MySQL 是一款流行的开源数据库，也是一个关系型数据库管理系统

在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一

3.1.1 发展历史

  • 由瑞典 MySQL AB 公司开发

  • 2008年， MySQL AB公司被SUN公司收购

  • 2009年，SUN公司被Oracle公司收购

3.1.2 优势

  • 运行速度快

  • 使用成本低

  • 容易使用

  • 可移植性强

  • 适用用户广

3.1.3 MySQL 安装

官网 https://downloads.mysql.com/archives/community/

安装教程 https://blog.csdn.net/qq_62283694/article/details/128853617

推荐使用 MySQL5.7

3.2 命令行连接 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服务器主机地址 本机登录可以省略

image-20230331175538293

3.3 Navicat 数据库管理工具

3.3.1 Navicat Premium

Navicat Premium是一款功能强大的、可支持多连接的数据库管理工具

image-20230331175820751

允许在单一程序中同时连接多达7种数据库

  1. MySQL

  2. MariaDB

  3. MongoDB

  4. SQL Server

  5. SQLite

  6. Oracle

  7. PostgreSQL

官网 https://www.navicat.com/en/products

推荐使用：Navicat Premium 16

四、MySQL 初体验

4.1 SQL语言

4.1.1 SQL 语言概念

  • SQL 用于对数据库的操作和查询
  • SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制

4.1.2 SQL 语言由四个部分组成

​

SQL语言 作用
DDL（数据定义语言） 用于创建或删除数据库对象
DML（数据操作语言） 用于插入，修改和删除表中的数据
DQL（数据查询语言） 用于数据查询，指 SELECT 语句
DCL（数据控制语言） 用于控制数据库组件的存取许可、存取权限等

4.2 检查 MySQL 服务

4.2.1 检查 MySql 服务是否被启动

执行 MySql 的任何操作，都需要保证 MySql的服务处于启动状态

  • MySql 服务启动步骤

     1、使用 Win 键 + R键调出"运行"对话框

​ 2、输入 services .msc 打开 “服务” 窗口

​ 3、找到 MySql 服务，右键，点击启动

  • 使用命令行连接 MySql

​ 1、在命令行窗口中，可使用以下语法格式连接数据库

mysql -h 服务器主机地址 -u 用户名 -p 密码

​ 2、如果连接本机的 mysql，可以不输入服务器主机地址，如下

mysql -u 用户名 -p

​ 3、然后再次输入密码，即可连接

​ 4、使用命令 \p 即可断开连接 mysql

​ 5、除了使用 命令行窗口链接 MySql，也可以数据库管理工具来连接

登陆数据库后，可以通过输入命令操作 MySql

SQL 语句不区分大小写

  • 查看 MySql 版本信息及当前用户 语法
SELECT VERSION(),USER();

​ 运行效果

image-20230402180355910

4.3 DDL 语言

4.3.1 操作数据库

  • 创建数据库 语法
CREATE DATABASE 数据库名
  • 查询现有的所有数据库 语法
SHOW DATABASES;
  • 选择数据库 语法

​ 在操作数据之前，需要先选定表所在的数据库，选择对某个数据库的表进行操作。

USE 数据库名
  • 删除数据库 语法
DROP DATABASE 数据库名;

4.3.2 数据类型

在创建表之前，需要确定数据表的字段及每个字段的数据类型

MySQL 当中常用的的有数值类型、字符串类型和日期类型

1、数值类型

  • MySQL 中常用的数值类型
数据类型 字节数 取值范围
TINYINT[(M)] 1 字节 有符号值：-27~-27
无符号值：0~28-1
SMALLINT[(M)] 2 字节 有符号值：-215~215-1
无符号值：0~216-1
MEDIUMINT[(M)] 3 字节 有符号值：-223~223-1
无符号值：0~224-1
INT[(M)] 4 字节 有符号值：-231~231-1
无符号值：0~2432-1
FLOAT[(M,D)] 4 字节 有符号值：-3.402823466E+38~-1.175494351E-38
无符号值：1.175494351E-3.402823466E+38
DOUBLE[(M,D)] 8 字节 有符号值：-1.7976931348623157E+308~-2.2250738585072014E-308
无符号值：2.225073858072014E-308~1.7976931348623157E+308
DECIMAL[(M[,D])] M+2字节 M：最大精度位数即总位数，M 的取值范围是1~16，默认值为 10
D：最小位精度位数，D 的取值范围为 0~30
该类型可能的取值范围与 DOUBLE 相同，但有效取值范围有 M、D决定
例如，类型为 DECIMAL(5,2) ，能够存储具有五位数字和两位小数的任何值的字段
取值范围是 -999.99~999.99

  • M 代表需要显示的数字的位数

  • 若数据位数大于数据本身的显示宽度，只要不超过该类型取值范围，则以是实际位数显示

  • TINYINT、SMALLINT、MEDIUMINT、INT 都为整数类型

  • FLOAT、DOUBLE 为浮点数类型

  • DECIMAL 为定数类型

  • 当对精度要求较高时，一般使用 DECIMAL 类型

  • 表中的值都包含有符号值和无符号值，其中，有符号数可以为负数

2、字符串类型

  • MySQL 常用的字符串类型
数据类型 字节数 说明
CHAR[(M)] M 字节 固定长度字符串
M 为 0~255 的整数
VARCHAR[(M)] 可变长度 可变长度字符串
M 为 0~65535 的整数
TINYTEXT 0~255 微型文本
TEXT 0~65535 文本
  • VARCHAT 类型长度是可变的，当数据超过用户固定的范围，将会自动增长，为节约空间
  • TINYTEXT 和 TEXT 类型通常用来存储文章内容等纯文本信息

3、日期类型

  • MySQL 中常用的日期类型
数据类型 格式 取值范围
DATE YYYY-MM-DD 1000-01-04~9999-12-31
DATETIME YYYY-MM-DD hh:mm:ss 1000-01-01 00:00:00~9999-12-31 23:59:59
TIME hh:mm:ss -838:59:59~838:59:59
TIMESTAMP YYYYMMDDHHMMSS 1970 年某时刻~2038 年某时刻，精度为 1 秒
YEAR YYYY 格式的年份 1901~2155
  • 在 MySQL 5.7版本中，如果需要设置某字段默认值为系统当前时间，可指定字段类型为 TIMESTAMP ，当字段未插入数据时，默认为当前时间

4.3.2 操作数据表

在选择数据库之后，可以使用 SQL 语句创建数据表

1、创建数据表 语法

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1 数据类型 [字段属性 | 约束] [索引] [字段备注],
    字段2 数据类型 [字段属性 | 约束] [索引] [字段备注],
    ......
    字段n 数据类型 [字段属性 | 约束] [索引] [字段备注]
)[表类型] [表字符集] [表备注];
  • 表的字段也称之为列
  • 在创建数据表时，字段之间使用 , 分隔，最后一个字段不需要使用逗号
  • MySql 的注释方式有单行和多行两种
    • 单行注释 : #…
    • 多行注释 : /* … */

2、字段的约束和属性

字段约束和属性 关键字 说明
主键约束 PRIMARY KEY(PK) 设置该字段为表的主键，同时保证字段的唯一性和非空
外键约束 FOREIGN KEY(FK) 用于在两表之间建立关系，需要指定引用主表的哪一字段，作为外键的字段要求在主表中为主键
自动增长 AUTO_INCREMENT （1）使用该列为自增字段，默认每条自增1
（2）通常用于设置主键，且为整数类型
（3）可设置1初始值和步长
非空约束 NOT NULL 保证字段不能为空
默认约束 DEFAULT 保证字段总会有值，即没有插入值，也会有默认值
唯一约束 UNIQUE KEY(UK) 设置字段的值是唯一的。不可重复

3、查看数据表结构 语法

describe 数据表名

4、 删除表

删除当前数据库中的数据表

删除表 语法

DROP TABLE [IF EXISTS] 表名;

4.4 修改数据表结构

4.4.1 修改数据表的表名

  • 修改数据表的表名 语法
ALTER TABLE 旧表名 RENAME [TO] 新表名;
  • [TO] 为可选，使用与否不影响效果
  • 此语句只修改表的名称，不会修改表的结构

4.4.2 修改数据表的字段

  • 修改表中的字段包含字段名和数据类型
  • 修改数据表字段 语法
ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];
  • 若不需要修改数据类型，则与原类型保持一致，不可为空
  • 若修改时未添加属性，则修改后的字段无任何属性约束

4.4.3 给数据表添加字段

  • 向已有的数据表添加字段
  • 给数据表添加字段 语法
ALTER TABLE 表名 ADD 字段名 数据类型 [属性];

4.4.4 删除数据表的字段

  • 删除数据表当中的某个已有字段
  • 删除数据表的字段 语法
ALTER TABLE 表名 DROP 字段名

4.4.5 添加主外键约束

  • 添加主键约束 语法
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名(主键字段)
  • 添加外键约束 语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 关联表名(关联字段)

4.5 MySQL 存储引擎

4.5.1 MySQL 存储引擎概述

  • 存储引擎负责MySQL中的数据的存储和提取，是与文件打交道的子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎
  • MySQL中的数据用各种不同的技术存储在文件（或者内存）中。
  • 存储引擎制定了表的存储类型，即如何存储和索引数据、是否至此事务等
  • 存储引擎也决定了表在计算机中的存储方式

4.5.2 常用的数据库引擎

  • 不同的业务场景对存储引擎的需求不同，一般根据他们的功能选择合适的存储引擎
  • InnoDB 和 MyISAM 存储引擎功能
功能 InnoDB MyISAM
事务处理 支持 不支持
外键约束 支持 不支持
表空间大小 较大 较小
数据行锁定 支持 不支持
  • InnoDB 存储引擎
    • 事务型数据库首选引擎(事务后面会讲到)
    • 支持具有提交、回滚和崩溃恢复能力的事务控制
    • 在需要大量执行插入、更新操作，或者需要使用事务保证数据完整性的场景下，选择 InnoDB
  • MyISAM 存储引擎
    • 该存储引擎不支持事务，也不支持外键约束
    • 访问速度比较快
    • 当不需要事务处理，且以访问为主的应，适合使用该引擎

五、SQL 高级（DML）

5.1 DML 语言

DML 语句：执行 增、删、改、语句

5.1.1 插入数据语句

使用 INSERT 语句向数据表中插入数据

  • 插入单行数据 语法
INSERT INTO 表名 [(字段名列表)] VALUES (值列表);
  • 字段名列表时可选项，若省略，则依次插入所有字段
  • 值列表必须和字段名列表的数量相同，且数据类型一致
  • 多个字段名和多个值之间使用逗号隔开
  • 插入多行数据 语法
INSERT INTO 表名 [(字段名列表)] VALUES(值列表1),(值列表2),...,(值列表n);
  • 将查询结果插入新列表 语法1
INSERT INTO 表名(字段1,字段2,...) SELECT 字段1,字段2,... FROM 原表;
  • 将查询结果插入新列表 语法2
SELECT 字段1,字段2,...  INTO 新表 FROM 原表;
  • 将查询的数据插入到新创建的表中 语法
CREATE TABLE 新表(SELECT 字段1,字段2,...  FROM 原表)

5.1.2 更新数据语句

使用 UPDATE 语句更新数据表中的数据

  • 更新数据语句 语法
UPDATE 表名 SET 字段1=值1,字段2=值2,...,字段n=值n [WHERE 条件]

5.1.3 删除数据语句

从数据表中删除数据可以使用 DELETE 语句 或 TRUNCATE 语句

  • 使用 DELETE 语句删除数据 语法
DELETE FROM 表名 [WHERE 条件]
  • 使用 TRUNCATE 语句清除整个表 语法
TRUNCATE TABLE 表名
  • 该语句可清空表内的所有数据

六、SQL 高级（DQL）

6.1 基础查询

6.1.1 基础查询 语句

  • 数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果给客户端。
  • 查询返回的结果集是一张虚拟表。
  • 查询关键字：SELECT
SELECT * FROM 表
SELECT 列名1,列名2 FROM 表
  • ***** 表示所有列，也可以使用列名表示，用 ， 分隔
  • SELECT 要查询的列名称
  • FROM 表名称

6.1.2 AS 别名

  • 在查询中，所显示的虚拟表中的列名一般为英文，为了方便区分，我们可以使用列的别名
  • 使用关键字 AS 给列命名
  • 语法格式如下
SELECT 列名1 AS '别名' FROM 表

6.2 增强查询

6.2.1 增强查询语句

在基础查询的基础上，对查询添加一些属性，让查询数据更为精准

回顾数据查询语句 (SELECT 语句) 语法如下

SELECT *或<字段名列表> FROM <表名或视图> [WHERE <查询条件>] [GROUP BY <分组的字段名>] [HAVING <条件>] [ORDER BY <排序的字段名>] [ASC 或 DESC] [LIMIT [位置偏移量,]行数];
  • WHERE 子句为可选项，如果选择该选项，则将限定查询条件，查询结果必须满足此查询条件
  • GROUP BY 子句表明查询出来的数据按指定字段进行分组
  • HAVING 子句用于筛选组
  • ORDER BY 子句指定按什么顺序显示查询出来的数据，需要指定排序字段。默认按照升序排列，也可以通过代码指定 升序（ASC）或 降序（DESC）
  • LIMIT 来指定查询结果返回条数
    • 位置偏移量表示结果从结果集的第几条数据开始选取（记录的偏移位置从0开始开始计算，表示第一条记录，第二条记录的偏移位置是1，以此类推）
    • 行数指以偏移位置为起点，选取记录的条数

6.3 运算符

MySQL 的运算符主要有：算术运算符、比较运算符、逻辑运算符、位运算符

6.3.1 算术运算符

  • 常用的算术运算符
运算符 作用
+ 加法
- 减法
* 乘法
/ 或 DIV 除法
% 或 MOD 取余

在除法运算和模(取余)运算中，如果除数为0，将是非法除数，返回结果为NULL

6.3.2 比较运算符

  • SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。
  • 常用的比较运算符
符号 描述 备注
= 等于
<>, != 不等于
> 大于
< 小于
<= 小于等于
>= 大于等于
BETWEEN 在两值之间 >=min&&<=max
NOT BETWEEN 不在两值之间
IN 在集合中
NOT IN 不在集合中
<=> 严格比较两个NULL值是否相等 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE 模糊匹配
REGEXP 或 RLIKE 正则式匹配
IS NULL 为空
IS NOT NULL 不为空

6.3.3 逻辑运算符

  • 逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。
  • 常用的逻辑运算符
运算符号 作用 示例
NOT 或 ! 逻辑非
AND 逻辑与
OR 逻辑或 当A、B中只要有一个或者两个都为1时，结果为1，否则为0
XOR 逻辑异或 当A、B两个不同时结果为1，否则为0

6.3.4 位运算符

  • 位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数
  • 常用的位运算符
运算符号 作用 说明
& 按位与 参与&运算的两个二进制位都为 1 时，结果就为 1，否则为 0
| 按位或 参与`
^ 按位异或 参与^运算的两个二进制位不同时，结果为 1，相同时，结果为 0
~ 取反 按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变为1
<< 左移 位左移是按指定值的补码形式进行左移，左移指定位数之后，左边高位的数值被移出并丢弃，右边低位空出的位置用 0 补齐
>> 右移 位右移是按指定值的补码形式进行右移，右移指定位数之后，右边低位的数值被移出并丢弃，左边高位空出的位置用 0 补齐

6.3.5 运算符优先级

  • 运算符的使用存在一定的优先级别
  • 运算符优先级
优先级顺序 运算符
1 :=
2 ||，OR，XOR
3 &&，AND
4 NOT
5 BETWEEN，CASE，WHEN，THEN，ELSE
6 =，<=>，>，>=，<，<=，<>，!=，IS，LIKE，REGEXP，IN
7 |
8 &
9 <<，>>
10 -，+
11 *，/，DIV，%，MOD
12 ^
13 -（一元减号），~（一元比特反转）
14 !
  • 最低优先级为： :=
  • 最高优先级为： !、BINARY、 COLLATE。

6.4 条件查询

使用 WHERE 关键字来对查询的结果进行筛选

6.4.1 单条件查询

  • 使用 WHERE 关键字，根据查询条件，来筛选数据
  • 单条件查询 语法
WHERE 列名 FROM 表名 WHERE 列名 运算符 值

6.4.2 多条件查询

  • 多条件可以对查询更加精确
  • 多条件查询关键字
    • AND：必须满足两边的条件才会查询出来
    • OR：满足其中一个条件，并且不满足另一个条件时，前后要变化两次，才会被查询出来
  • WHERE 子句中，使用AND、OR 可以把两个或多个过滤条件结合起来
  • 多条件查询语句 语法
WHERE 列名 FROM 表名 WHERE 列名 运算符 值 AND 列名 运算符 值
WHERE 列名 FROM 表名 WHERE 列名 运算符 值 OR 列名 运算符 值

6.4.3 范围查询

  • 使用关键字 BETWEEN AND
  • BETWEEN AND:需要两个参数，起始值和最终值。如果查询的记录在这个范围内就被返回
  • 语法格式如下
WHERE 列名 NOT BETWEEN 起始值 AND 最终值
  • NOT：可选参数，表示取反
  • 查询指定范围内所有值，包括起始值和最终值。

6.4.4 空值查询

  • 使用关键字 IS NULL
  • IS NULL关键字判断该列的值是否为空值，空值不是空字符串
  • 语法格式如下
WHERE 列名 IS NOT NULL
  • NOT：可选参数，表示取反

6.5 模糊查询

模糊查询一般使用关键字 LIKE 主要用于匹配列中的数据

6.5.1 通配符

  • 一般在使用 LIKE 进行模糊查询时，与通配符一起结合使用
  • 常用的通配符
通配符 描述
% 替代0或多个字符
_ 替代一个字符，字符长度不能为0
[…] 字符列中任何单一字符
[^…] 不在字符列中任何单一字符

6.5.2 LIKE 模糊查询

  • 模糊查询一般使用关键字 LIKE 主要用于匹配列中的数据
  • 语法格式如下
where NOT LIKE 字符串

  • NOT：可选项，表示取反

  • 字符串：用来匹配的字符串，可以是完整的字符串或者通配符

  • 语法格式如下

WHERE 列1 LIKE '%123'  #表示 列1的值结尾为123即可匹配
WHERE 列1 LIKE '123%'  #表示 列1的值开头为123即可匹配
WHERE 列1 LIKE '%123%'  #表示 列1的值包含为123即可匹配
WHERE 列1 LIKE '_123' #表示列1的值为 X123，X 表示任意字符
WHERE 列1 LIKE '[张李王]三' #将找出“张三”、“李三”、“王三”（而不是“张李王三”）
WHERE 列1 LIKE '[^张李王]三' #将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
  • 注意事项
  1. 匹配的字符串必须加单引号或者双引号。
  2. 默认情况下，LIKE关键字匹配字符串时候不区分大小写，可以在LIKE关键字后添加BINARY关键字来区分大小写。
  3. 如果查询内容中有通配符字符，就需要加转义字符 “\”.

6.6 常用函数

基于已有数据进行数据的统计分析计算等需求

6.6.1 字符串函数

  • 可以对字符串进行各种处理
  • MySQL 中常用的字符串处理函数
函数名 函数说明 函数示例
CONCAT(str1,str2,…,strn) 将字符串 str1、str2、…、strn连接为一个完整的字符串 SELECT CONCAT(‘L’,‘Q’,‘E’);
返回：LQE
LOWER(str) 将字符穿 str 中所有字符变为小写 SELECT LOWER(‘LqE’)
返回：lqe
UPPER(str) 将字符串 str 中所有的字符变为大写 SELECT UPPER(‘lQe’)
返回：LQE
SUBSTRING(str,num,len) 返回字符串 str 的第 num个位置开始，长度为 len 的子字符串 SELECT SUBSTRING(‘ABCDEFG’,3,4)
返回：CDEF
INSERT(str,pos,len,newstr) 将字符串 str 从 pos 位置开始，将len 个字符长度的子串替换为字符串 newstr SELECT INSERT(‘1234’,3,0,‘ABCDE’)
返回：12ABCDE34

6.6.2 日期函数

  • 获取当前日期，对日期进行操作
  • MySQL 中常用的日期函数
函数名 函数说明 示例
CURDATE() 获取当前日期 SELECT CURDATE();
返回：2023-03-11
CURTIME() 获取当前时间 SELECT CURTIME();
返回：01:54:30
NOW() 获取当前日期和时间 SELECT NOW();
返回：2023-03-11 01:55:12
WEEK(date) 返回参数 date 为一年中的第几周 SELECT WEEK(NOW());
返回: 10
YEAR(date) 返回参数 date 的年份 SELECT YEAR(NOW());
返回：2023
HOUR(time) 返回参数 time 的小时值 SELECT HOUR(NOW())；
返回：1
MINUTE(time) 返回参数 time 的分钟值 SELECT HOUR(NOW());
返回：57
DATEDIFF(date1,date2) 返回参数 date1 和参数 date2 之间相隔的天数 SELECT DATEDIFF(NOW(),‘2019-8-8’)
返回：1311
ADDDATE(date,n) 计算参数 date 在 n 天后的日期 SELECT ADDDATE(NOW(),4);
返回：2023-3-15 01:58:36

6.6.3 聚合函数

  • 对已有数据进行汇总，常见的有求和、平均值、最大值、最小值等
  • MySQL 中常用的聚合函数
函数名 函数说明
COUNT() 返回某字段的行数
MAX() 返回某字段的最大值
MIN() 返回某字段的最小值
SUM() 返回某字段的和
AVG 返回某字段的平均值

6.6.4 数学函数

  • 可以进行数值运算
  • MySQL 中常见的数学函数
函数名 函数说明 示例
CELL(x) 返回大于或等于数值 x 的最小整数，向上取整 SELECT CELL(2.1)
返回：3
FLOOR(x) 返回小于或等于数值 x 的最大整数，向下取整 SELECT FLOOR(2.9)
返回：2
RAND() 返回 0~1 的随机数 SELECT RANG();
返回：0.15013303621684485

6.7 分组查询

6.7.1 GROUP BY

  • GROUP BY 语句根据一个或多个列对结果集进行分组，一般和聚合函数一起使用
  • GROUP BY 语法格式 如下
SELECT COUNT(*) FROM 表名 GROUP BY 分组的字段
  • 分组字段：按照表中的某列值进行分组
  • 分组可以按单个字段也可以按多个字段

6.7.2 HAVING

  • HAVING 语句对分组查询的结果进行条件限定，用来限制分组后的显示，符合条件表达式的结果将被显示

  • HAVING 语法格式 如下

SELECT COUNT(*) FROM 表名 GROUP BY 分组的字段 HAVING 条件表达式

6.8 排序查询

6.8.1 单字段排序

  • ORDER BY 关键字用于对结果集进行升序或降序排序
  • ORDER BY 关键字默认按升序对记录进行排序
  • 也可以使用关键字对记录进行升序或降序：升序ASC 降序DESC
  • 单字段排序 语法格式如下
SELECT * FROM 表名 ORDER BY 进行排序的列 ASC|DESC
  • 进行排序的列：按照表中哪列的值进行排序

6.8.2 多字段排序

  • ORDER BY 后面可以跟多个字段进行排序

  • 多字段排序 语法格式如下

SELECT * FROM 表名 order by A1 asc , A2  desc
  • 指的是用 A1 列升序，A2 列降序
  • 排序的时候 前者先用A1升序排列，若有并列，则并列部分按A2降序

6.9 分页查询

6.9.1 单参数分页查询

  • 当查询的数据有很多条时，通过真分页的方式，每次取出所需数据。对于不同的数据，实现分页有不同的方式

  • 分页查询使用关键字 LIMIT

  • LIMIT出现在查询语句的最后，可以使用一个参数或两个参数来限制取出的数据。其中第一个参数代表偏移量：offset（可选参数），第二个参数代表取出的数据条数：rows

  • LIMIT 单参数用法

    • 当指定一个参数时，默认省略了偏移量，即偏移量为0，从第一行数据开始取，一共取rows条
    • 单参数语法格式
    /* 查询前5条数据 */
SELECT * FROM 表名 LIMIT 5;

6.9.2 双参数分页查询

  • LIMIT 双参数用法

    • 当指定两个参数时，需要注意偏移量的取值是从0开始的,此时可以有两种写法
    • 双参数语法格式
    /* 查询第1-10条数据 */
SELECT * FROM 表名 LIMIT 0,10;
/* 查询第11-20条数据 */
SELECT * FROM 表名 LIMIT 10 OFFSET 10;
/* 查询第21-30条数据 */
SELECT * FROM 表名 LIMIT 20 OFFSET 10;
/* 查询第N页的十条数据 */
SELECT * FROM 表名 LIMIT (N-1)*10,10;
    • 以上参数可以用 ， 分隔，也可以使用关键字 OFFSET 分隔

6.10 子查询

6.10.1 基本的子查询

  • 子查询指一个查询语句嵌套在另一个查询语句内部的查询，内部的查询是外部查询的条件
  • 当内查询的结果为单个值时，可以使用 = 号来接收这个值
  • 语法格式如下
SELECT * FROM 表1 WHERE 列1=(Where 列2 FROM 表2)

4.1.1 IN 子查询

  • 使用子查询，如果结果返回的多个值，而 子查询跟随在 比较运算符之后，就会导致报错，因为比较运算符只能匹配一个值，如下
  • 在病人表中查询做过 ”尿常规“ 检查项目的病人
SELECT patientName FROM patient WHERE checkItemID=(SELECT checkItemID FROM prescription WHERE checkItemName='尿常规');

  • 如果子查询的结果为多个值，就会导致代码报错

  • 解决方案就是使用 IN 关键字，将 = 替换成 IN，如下

SELECT patientName FROM patient WHERE checkItemID IN (SELECT checkItemID FROM prescription WHERE checkItemName='尿常规');

4.1.2 NOT IN 子查询

  • 匹配结果集以外数据的情况
  • 与 IN 关键字类似，只是将 IN 查询结果以外的数据返回

4.2.1 EXISTS/NOT EXISTS 子查询

  • EXISTS 是一个布尔运算符返回true或false。 EXISTS 经常使用的在一个子查询，以测试一个“存在”状态

  • EXISTS 在 WHERE 子句中作为子查询时使用，若子查询中有数据，则 EXISTS 子查询为 true，其外层查询语句会被执行，反之，不会执行

  • EXISTS 语法

SELECT .... FROM 表名 WHERE EXISTS(子查询)
  • EXISTS 子查询允许添加 NOT 关键字实现相反的操作

6.11 多表连接查

在查询数据时，需要查询数据关联的另一个表的那一列，需要从多个表中选择数据项的情况，就是多表查询

6.11.1 内连接查询

  • 内连接查询一般使用关键字 INNER JOIN 连接的数据表 ON 匹配条件
  • INNER JOIN 后面跟的是要连接的表
  • ON后面的括号内设置条件，如果括号内的条件匹配，那么则返回连接的数据库的那一行记录
  • 内连接查询 语法格式如下
Select 表1.值2,表2.值2 From 表1 INNER JOIN 表2 ON 表1.值1=表2.值1

6.11.2 外连接查询

  • 外连接查询主要分为左右外连接查询
  • 他与内连接查询类似，不同的时，如果主表或者从表中没有匹配的值，则会以null显示
  • 左外连接查询：在查询时，如果主表查询的数据在从表中没有对应的数据，则从表的数据则会以null显示
  • 右外连接查询：在查询时，如果主表查询的数据在从表中没有对应的数据，则主表的数据则会以null显示
  • 左外连接关键字：LEFT JOIN 连接的数据表 ON 匹配条件
  • 右外连接关键字 RIGHT JOIN 连接的数据表 ON 匹配条件
  • 外连接语法格式如下
Select 表1.值2,表2.值2 From 表1 LEFT JOIN 表2 ON 表1.值1=表2.值1
Select 表1.值2,表2.值2 From 表1 RIGHT JOIN 表2 ON 表1.值1=表2.值1

6.12 查询语句的优先级

6.12.1 查询语句的优先级顺序

  • 在执行查询时，使用的各种关键字存在一定的先后顺序
  • 如果使用不当，则会报错
优先级顺序 关键字
1 LIMIT
2 ORDER BY
3 HAVING
4 GROUP BY
5 WHERE
6 INNER JOIN
7 ON
  • ON 的优先级最高
  • LIMIT 的优先级最低

七、存储过程

7.1 认识存储过程

7.1.1 存储过程简介

  • 存储过程 是一组为了完成特定功能的 SQL 语句集合

  • 经编译后保存在数据库中

  • 通过指定存储过程的名字并给出参数的值

  • 可带参数，也可返回结果

  • 可包含数据操纵语句、变量、逻辑控制语句等

7.1.2 MySQL 存储过程的优缺点

存储过程的优点

  • 减少网络流量 存储过程一般存储在数据库服务器上，应用程序不需要发送多个 SQL 语句，而只需要发送存储过程的名称和参数
  • 提升执行速度 在 MySQL 中，对于存储过程只在创建时进行编译，然后将编译好的存储过程放在缓存中。若多次重复调用存储过程，则使用缓存中的编译版本
  • 减少数据库连接次数 执行多条 SQL 语句，应用程序需要多次连接数据库，使用存储过程，应用程序只需要连接一次数据库
  • 安全性高 数据库管理员可以对访问数据库存储过程的应用授予权限，而不提供基础数据表的访问权限
  • 高复用性 存储过程是封装的一个特定的功能块，对于任何应用程序都是可复用的透明的

存储过程的缺点

  • 内存增加 如果使用许多存储过程，则使用这些存储过程的每个连接的内存使用量将显着增加
  • 移植性差
  • 开发和维护存储过程并不容易
  • 调试存储过程很困难

7.2 使用存储过程

7.2.1 创建和调用存储过程

创建存储过程

  • 使用 CREATE PROCEDURE 语句创建存储过程
  • 创建存储过程 语法
CREATE PROCEDURE 过程名([过程参数[,...]])
	[特性] #可选项，用于设置存储过程的行为
	存储过程体
  • 存储过程的常用特性
特性 说明
LANGUAGE SQL 存储过程语音，默认为 SQL
CONTAINS SQL
NO SQL
READS SQL DATA
MODIFIES SQL DATA		 存储过程要做哪类工作
默认为CONTAINS SQL
SQL SECURITY{DEFINER | INVOKER} 用来指定存储过程的执行权限
默认值为 DEFINER
DEFINER 使用创建者的权限执行
INVOKER 使用执行者的权限执行
COMMENT ‘string’ 存储过程的注释信息

示例 编写存储过程，输出病人总人数

DROP PROCEDURE IF EXISTS proc_patient_countPatient;
DELIMITER //   #声明分隔符
CREATE PROCEDURE proc_patient_countPatient()
BEGIN  	#过程体开始
SELECT COUNT(*) FROM patient;
END //  #过程体结束
DELIMITER ;   #恢复默认分隔符

1、删除存储过程

  • 在创建存储过程之前，应先判断存储过程是否存在，如果存在，则删除
  • 删除存储过程 语法
DROP PROCEDURE 过程名

2、声明语句分隔符

  • MySQL 中，默认使用 ; 作为分隔符，使用 DELIMITER 关键字可以改变分隔符
  • 创建过程前，首先声明分隔符
  • 将分隔符设置为 $$ 或 // 语法如下
DELIMITER $$
DELIMITER //
  • 如果没有声明分隔符，编译器就会把存储过程当成 SQL 语句处理，会出现报错
  • 存储过程最后，要把分隔符还原 语法如下
DELIMITER ;

3、定义存储过程的参数

  • MySQL 中，存储过程的参数包括 3 种类型
  • IN 输入参数。该参数的值必须在调用存储过程时指定，在存储过程中可以使用该参数，但他不能被返回
  • OUT 输出参数。该参数可以再存储过程中发生改变并可以返回
  • INOUT 输入输出参数.该参数的值在调用存储过程时指定，在存储过程中可以被改变和返回。
  • 定义参数 语法
[IN|OUT|INOUT] 参数名 类型
  • 示例
DELIMITER // #声明分隔符
CREATE PROCEDURE proc_patient_countPatient2(IN patientNum INT)
#省略......
DELIMITER ; #恢复默认分隔符
  • 调用 存储过程时，调用out类型的参数，需要有一个会话变量接收
DELIMITER // #声明分隔符
CREATE PROCEDURE proc_patient_countPatient3(OUT patientNum INT)
SELECT COUNT(*) INTO patientNum FROM patient;
DELIMITER ; #恢复默认分隔符
#调用
ALL  proc_patient_countPatient3(@patientNum);
  • INTO 就是将查询的值给参数 patientNum

4、过程体的标识

  • 在定义存储过程的过程体时，需要标识开始和结束。
  • 定义过程体 语法
BEGIN ...... END 分隔符
  • END 后面必须使用 DELIMITER 语句中设置的分隔符为结束

调用存储过程

  • MySQL 中使用 CALL 关键字调用存储过程
  • 调用存储过程 语法
CALL 存储过程名 (参数列表);

创建和调用存储过程 示例

  • 编写存储过程，输出病人总人数
#如果存储过程存在，则删除
DROP PROCEDURE IF EXISTS proc_patient_countPatient;
DELIMITER //  #声明分隔符
CREATE PROCEDURE proc_patient_countPatient()
BEGIN #过程体开始
 SELECT COUNT(*) FROM patient;
END // #过程体结束
DELIMITER ;   #恢复默认分隔符

#调用
CALL roc_patient_countPatient();

7.2.2 存储过程中的变量

  • 类似 Java 与其他编程语言，定义存储过程时可以使用变量

  • 声明变量 语法

DECLARE 变量名[,变量名...] 数据类型 [DEFAULT 值];
  • 示例 声明交易时间变量 trade_time，并设置默认值为 2020-07-10 代码如下
DECLARE trade_time DATE DEFAULT '2020-07-10';

  • 所有局部变量的声明一定要放在存储过程体的开始，必须在BEGIN后，否则会提示语法错误

  • 声明变量后，可以给变量赋值

  • 变量赋值 语法

SET 变量名=表达式值[,变量名=表达式...];
  • 示例 设置变量 total 的值为100
set total=100;

7.2.3 MySQL 用户自定义变量

  • MySQL 中变量包括 用户自定义变量 和 系统变量两种
  • MySQL 用户自定义变量又包括 局部变量 和 会话变量
  • 局部变量
    • 一般用于 SQL 的语句块中，如存储过程中的 BEGIN 和 END 语句块中
    • 作用于仅限于定义该变量的语句块内，语句块结束，变量释放
  • 会话变量
    • 也成为用户变量
    • 服务器为每个客户端连接维护的变量，与MySQL客户端是绑定的
    • 可以暂存值，并传递给同一连接中其他SQL语句进行使用
    • 当默认DEFINER = CURRENT_USERMySQL客户端连接退出时，用户变量就会被释放
    • 用户变量创建时，一般以“@”开头，形式为“@变量名”

7.2.4 设置用户权限

  • 存储过程中，可用关键字 DEFINER 赋值语句规定对存储过程访问的安全控制
  • 使用 DEFINER 和 SQL SECURITY 特性控制存储过程的执行 语法
DELIMITER //
CREATE [DEFINER={user|CURRENT_USER}] 
PROCEDURE 存储过程名
[SQL SECURITY {DEFINER|INVOKER|....}]
BEGIN
....
END //
DELIMITER ;
  • DEFINER 指定创建者用户
  • SQL SECURITY 用以指定 定义者 (DFINER) 或 调用者 (INVOKER)

7.2.5 查看存储过程

对创建后的存储过程进行查看

1、查看存储过程的状态

  • 查看以创建的存储过程 语法
SHOW PROCEDURE STATUS
  • 查看指定的数据库中创建的存储过程 语法
SHOW PROCEDURE STATUS WHERE DB='数据库名'
  • 也可以通过 LIKE 关键字匹配存储过程名称 语法如下
SHOW PROCEDURE STATUS LIKE '%patient%';

2、查看存储过程的创建代码

  • 查看存储过程的代码 语法
SHOW CREATE PROCEDURE 存储过程名

5.2.6 修改存储过程

  • 对已创建的存储过程进行修改
  • 只能修改存储过程定义的特性 语法如下
ALTER PROCEDURE 存储过程名 [特性......];
  • ALTER 关键字只能修改存储过程的属性
  • 如果要修改存储过程中过程体的内容，需要先删除该存储过程，然后进行重新创建

7.2.7 删除存储过程

  • 删除已有的存储过程 语法
DROP PROCEDURE 存储过程名

7.3 存储过程的控制语句

MySQL 提供的控制语句包括条件语句、循环语句、迭代语句

7.3.1 条件语句

MySQL 提供了两种控制语句，分别是 IF-ELSEIF-ELSE 条件语句和 CASE 条件语句

1、IF-ELSEIF-ELSE 条件语句

  • IF-ELSEIF-ELSE 语法
IF 条件 THEN 语句列表
	[ELSEIF 条件 THEN 语句列表]
	[ELSE 语句列表]
END IF;
  • 如果 IF 后的条件为真，则执行 THEN 后的语句列表

2、CASE 条件语句

  • MySQL 中的CASE 条件语句有两种写法
  • 第一种 语法
CASE
  WHEN 条件 THEN 语句列表
 [WHEN 条件 THEN 语句列表]
 [ELSE 语句列表]
END CASE;
  • 第二种 语法
CASE 列名
  WHEN 条件 THEN 语句列表
 [WHEN 条件 THEN 语句列表]
 [ELSE 语句列表]
END CASE;
  • 在 CASE 语句中，如果条件为真，则相应的 SQL 语句列表会被执行
  • 如果没有匹配条件，则会在 ELSE 子句里的语句列表被执行
  • CASE 语句的两种写法可以实现相同的功能
  • 如果做等值判断，使用第二种写法更加简洁
  • 因为 CASE 后面有列名，功能上会有一些限制

7.3.2 循环语句

MySQL 语句提供多种循环语句，包括 WHILE 循环语句、LOOP 循环语句 和 REPEAT 循环语句

1、WHILE 循环语句

  • WHILE 循环是最普遍使用的循环语句
  • WHILE 循环 语法
[lable:] WHILE 条件 DO
  语句列表
 END WHILE [lable]
  • 首先判断条件是否成立，若成立，则执行循环体
  • label 及标号，用于区分不同的循环体，可以被省略

2、LOOP 循环

  • LOOP 循环 语法
[lable:] LOOP
	语句列表
END LOOP [lable];
  • LOOP 循环结构不需要判断初始条件，直接执行循环体
  • 直到遇到 LEAVE 语句才退出循环
  • LEAVE 语法
LEAVE lable;

3、REPEAT 循环语句

  • REPEAT 循环 语法
[lable:] REPEAT
 语句列表
UNTLL 条件
END REPEAT [label]
  • REPEAT 循环不需要初始条件就可以直接进入循环体
  • REPEAT 循环有退出条件
  • 每次循环一次后，判断 UNTLL 后的条件是否成立，如果成立，则跳出循环

7.3.3 迭代语句

  • ITERATE 关键字可以嵌入 LOOP、WHILE、REPEAT 程序块中
  • 执行 ITERATE 语句时，跳出本次循环，继续下一次循环
  • ITERATE 语法
ITERATE lable;
  • label 参数表示循环的标志，ITERATE 语句必须跟在循环标志前面

八、事务、视图、索引、备份和恢复

8.1 事务

8.1.1 事务介绍

  • 事务 (Transaction) 将一些列数据操作捆绑成为一个整体统一管理的机制
  • 多个操作作为一个整体向系统提交，要么都执行、要么都不执行
  • 不可分割的工作逻辑单元

8.1.2 为什么需要事务

  • 事务包含了一组操作，这些操作可以是一条SQL语句、一组SQL语句或整个程序。
  • 如果其中一个操作不成功，这些操作都不会执行，前面执行的操作也会回滚原状态，用来保证数据的一致性和完整性。
  • 例如，就像银行转账，张三给李四转账，只有当张三的钱转走了，并且李四账户的钱收到了之后才会事务提交，否则事务会回滚到转账前的状态，保证数据的一致性，保证数据不会出错。

8.1.3 事务的四大特性

事务是最哦为单个逻辑工作单元执行的一系列操作，必须遵循四个特性

原子性、一致性、隔离性、持久性

  • 原子性
    • 事务是一个完整的操作
    • 事务的各步操作是不可分的(原子的)
    • 要么一起执行完毕，要么都不执行
  • 一致性
    • 事务完成前，数据必须处于一致状态
  • 隔离性
    • 一个事务在完成之前对其他事务是不可见的
    • 它不可以任何方式依赖或影响其他事务
  • 持久性
    • 一旦事务提交，不论系统是否故障，事务处理结果都是永久的
    • 事务成功完成之后对于数据库的改变是永久性的

8.1.4 在 MySQL 中使用事务

  • 默认设置下，每个 SQL 语句就是一个事务，即执行 SQL 语句后自动提交

  • 实现事务主要有两种方式

    • 使用 BEGIN 或 START TRANSACTION 开启一个事务
    • 执行 SET autocommit=0 禁止当前会话自动提交

两种实现事务的语法格式如下

1、执行事务的 SQL 语句语法格式

  • 使用 BEGIN 或 START TRANSACTION 开启一个事务
  • 提交事务 语法
#开启事务
BEGIN ; 或 START TRANSACTION; 
 #一组作为事务运行的 SQL 语句
 #提交事务
 COMMIT；

  • BEGIN 或 START TRANSACTION 标志着事务的起止点，其后的 SQL 语句不会被提交生效

  • 当运行到 COMMIT 语句后，标志着一个事务被提交成功

  • 自事务开始至提交语句之间执行的所有数据更新将被永久的保存在数据库文件中，并释放连接时占用的资源

  • 回滚（撤销）事务 语法

#开启事务
BEGIN;或 START TRANSACTION;
 #一组作为事务运行的 SQL 语句
 #回滚事务
 ROLLBACK;
  • 事务可以被重新提交，也可能因为执行某些语句不成功或其他异常情况而终止
  • 为保证事务的一致性，要是有 ROLLBACK 语句清除自事务起始点至该语句所做的所有数据更新操作

2、设置自动提交关闭或开启

  • MySQL 设置默认每条 SQL 语句就是一个事务，每执行一个 SQL 语句就会默认提交
  • MySQL 允许修改默认设置 autocommit 的值，及一条 SQL 语句不会被默认提交
  • 设置自动提交关闭或开启的语法
SET autocommit=0|1
  • autocommit 的值为0时，表示关闭自动提交
  • autocommit 的值为1时，表示开启自动提交

8.2 索引

8.2.1 索引简介

  • 使有序的内容在查找时消耗的时间更少，效率更高
  • 索引是对数据表中的一列值或多列值进行排列的一种结构
  • 索引是创建在数据表对象上的，有一个字段或多个字段生产的键组成

8.2.2 索引的分类

  • MySQL 中常用的索引
    • 普通索引（INDEX）
    • 唯一索引（UNIQUE）
    • 主键索引（PRIMARY KEY）
    • 全文索引（FULLTEXT）
  • 普通索引
    • MySQL 中的基本索引类型
    • 该类索引对索引的数据没有任何限制
    • 允许定义重复值或空值
    • 唯一任务是加快对数据的访问速度
  • 唯一索引
    • 唯一索引不允许出现两行具有相同的索引值
    • 现有数据中如果有重复的键值，则大多数数据库不允许创建唯一索引
  • 主键索引
    • 一种特殊的唯一索引
    • 不允许有空值
    • 创建数据表时，如果指定了主键，则会自动创建主键索引
  • 全文索引
    • 全文索引的作用是在定义索引的列上支持值的全文查找
    • 允许全文索引列中有重复值和空值
    • 全文索引只能在 CHAR、VARCHAR、TEXT 类型的列上创建

8.2.3 在 MySQL 中使用索引

1、创建索引

  • 使用 CREATE INDEX 可以在已经存在的表上添加索引
  • 添加索引语法
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名(列名)
  • UNIQUE、FULLTEXT、SPATIAL：分别表示唯一索引、全文索引、空间索引，为可选参数

2、查看索引

  • 使用 SHOW INDEX 关键字可以查看已创建的索引
  • 查看索引 语法
SHOW INDEX FORM 表名

3、删除索引

  • 删除索引 语法
DROP INDEX 索引名 ON 表名
  • 删除表时，该列的所有索引将同时被删除
  • 删除表中的列时，若要删除的列为索引的组成部分，则该列也会从索引中被删除
  • 如果组成索引的所有列被删除，则整个索引被删除

8.3 视图

8.3.1 需要视图的原因

  • 不同身份的用户所查询的数据范围不同，只将用户需要的某些列展示给用户，不展示多余的列
  • 使用视图，可以定制用户数据，聚焦特定的数据
  • 也可以借助视图，对代码进行封装保存

8.3.2 视图的概念

1、视图是一张虚拟表

  • 表示一张表的部分数据或多张表的综合数据
  • 其结构和数据是建立在对表的查询基础上

2、视图中不存放数据

  • 数据存放在视图所引用的原始表中

3、一个原始表，根据不同用户的不同需求，可以创建不同的视图

8.3.3 视图的好处

1、给开发人员带来好处

  • 限制数据检索更容易
  • 维护应用程序更方便

2、给数据库的最终用户带来的好处

  • 结果更容易理解
  • 获得数据更容易

8.3.4 在 MySQL 中使用视图

1、创建视图

  • 创建视图 语法
CREATE VIEW 视图名 AS <SELECT 语句>;
  • 视图名一般以 view_xxx 或 v_xxx 来命名

2、查询视图中的数据

  • 查询已创建的视图中的数据 语法
SELECT * FROM 视图名

3、删除视图

  • 与数据表一样，若将要创建的视图在数据库中已经存在，则先删除视图才能创建成功
  • 删除视图 语法
DROP VIEW [IF EXISTS] 视图名;

4、使用视图的注意事项

  • 每个视图可以使用多张表
  • 与查询相识，一个视图可以嵌套另一个视图，但尽量不要超过 3 层
  • 对视图数据进行添加、更新、删除操作实际上是直接操作引用表中的数据

8.4 数据库备份和恢复

8.4.1 数据库备份

1、使用 mysqldump 备份数据库

  • 通过 mysqldump 命令可以将指定的数据库和数据表导出为 SQL 脚本
  • 使用 mysqldump 备份数据库是在命令行中实现的 命令如下
mysqldump [options] database [table1.table2,...] > [path]/filename.sql
  • options：连接数据库的参数，主要内容如下
    • -u username：用户名
    • -h host：登录用户的主机名称。若为本机，则省略
    • -p password：登录密码
  • database：需要备份的数据库
  • table：需要备份的数据表，可指定多张表。若备份整个数据库，则省略
  • filename.sql：备份文件的名称
  • mysqldump 是 DOS 命令，无需进入 mysql 命令行

2、使用 Navicat 备份数据库

  • 使用 Navicat 也可以导出数据库的备份脚本
  • 选择要备份的数据库，右键之后选择 “转储 SQL 文件” --> “结构和数据…” 命令 即可导出

8.4.2 数据库恢复

1、使用 MySQL 命令导入数据

  • 使用导出的 SQL 备份脚本，在需要恢复时通过 MySQL 命令对其进行导入操作
  • 导入SQL 备份脚本 命令如下
mysql -u username -p dbname < filename.sql
  • username：登录数据库系统的用户名
  • dbname：导入目标数据库的数据库名
  • filename.sql：数据库备份后的文件地址

2、使用 source 命令恢复数据、

  • 可以在已连接数据库的状态下导入数据
  • 使用 source 命令 语法如下
source filename;
  • filename 为 SQL备份后的文件地址

3、使用 Navicat 的数据导入功能导入数据

  • 也可以使用 Navicat 直接导入数据
  • 右键选择要导入数据的数据库，执行 “运行 SQL 文件…” 命令，之后在打开的导入窗口，选择要运行的 SQL 文件，单机 “开始”，即可开始导入数据

九、JDBC

9.1 JDBC 介绍

JDBC （Java Database Connectivity，Java数据库连接）

9.1.1 使用 JDBC 的原因

  • Java 通过 JDBC 技术实现对各种数据库的访问，充当 Java 应用程序与各种数据库之间进行对话的媒介
  • 通过 JDBC 可以将程序中的数据持久的保存到数据库当中
  • Sun 公司提供了 JDBC 的接口规范------JDBC API

9.1.2 JDBC 的工作原理

  • JDBC 主要有 JDBC API、JDBC Driver Manager 和 JDBC 驱动组成
Java应用程序
JDBCAPI
JavaJDBCManager
JDBC驱动
JDBC驱动O
MySQL
Oracle

1、JDBC API

  • JDBC API 提供了一套 Java 应用程序与各种数据库交互的标准接口
  • 其中 Connection（连接）接口、Statement 接口、ResultSet（结果集）接口、preparedStatement 接口等都属于 JDBC 接口

2、JDBC Driver Manager

  • JDBC Driver Manager 是 JDBC 体系结构的支柱
  • 负责管理各种 JDBC 驱动
  • JDBC Driver Manager 位于 JDK 的 java.sql 包中

3、JDBC 驱动

  • JDBC 驱动不包含在 JDK中，由第三方中间厂商提供
  • 负责连接各种数据库

9.1.3 JDBC API

  • JDBC API 是 Java 应用与各种数据库交互的标准接口
  • 主要功能是建立与数据库的连接，发送 SQL 语句，返回处理结果
  • JDBC API 主要类/接口 的功能
类/接口 作用
DriverManager 类 装载驱动程序，并为创建新的数据库连接提供支持
Connection 接口 负责连接数据库并担任传送数据的任务
Statement 接口 由 Connection 产生，负责执行 SQL 语句
PreparedStatement 接口 Statement 的子接口，也由 Connection 产生，负责执行 Sql语句
与 Statement 接口的区别：PreparedStatement 借口具有高安全性、
高性能、高可读性和高可维护性等优点
ResultSet 接口 负责保存和处理 Statement 执行后产生的查询结果

9.2 使用 JDBC 连接数据库

9.2.1 使用 JDBC 连接数据库的方法

1、加载 JDBC 驱动

  • JDBC 驱动由数据库厂商或第三方中间件厂商提供，使用 JDBC 连接数据库前要先下载对应版本的驱动
  • 可以在 MySQL 广为下载对应的 JDBC 驱动 JAR 包
  • 导入 JDBC 的驱动 JAR 包后，需要加载驱动
  • 驱动类的类名为 com.mysql.jdbc.Driver
  • 通常使用 Class.forName 加载 JDBC 驱动
  • 使用 Class.forName 加载驱动 语法
try{
   
    Class.forName("JDBC驱动类的名称")
}catch(ClassNotFoundException){
   
    //异常输出代码
}

2、与数据库建立连接

  • JDBC 驱动类加载后，需要建立与 MySQL 数据库连接
  • 建立连接使用 DriverManager 类，DriverManager 类是 JDBC 的管理层，作用于用户和驱动程序之间
  • getConnection() 方法用于建立与数据库的连接
  • 调用 getConnection() 方法 需要放入参数：数据库连接地址字符串、连接数据库的用户名和密码
  • 建立连接数据库 语法
Connection conn=DriverManager.getConnection(数据库连接地址字符串，数据库用户名，密码)
  • 数据库连接字符串 语法格式
jdbc:数据库://ip:端口/数据库名称 [?连接参数=参数值]
  • 数据库： JDBC 连接的目标数据库，如 MySQL 数据库
  • ip：JDBC 所连接的目标数据库地址，如果是本地数据库，则可以使用 localhost。即本地主机名
  • 端口：连接数据库的端口号，如果连接 MySQL 数据库，则默认端口号为 3306
  • 数据库名称：目标数据库的名称，如 hospital
  • 连接参数：连接数据库时的参数配置。
  • Connection 接口常用的方法
方法名称 作用
Statement createStatement() 创建一个 Statement 对象并将 SQL 语句发送到数据库
PreparedStatement preparedStatement(String sql) 创建一个 PreparedStatement 对象并将参数化的 SQL 语句发送到数据库
boolean isClosed() 查询此 Connection 对象是否已经被关闭。若已关闭，返回true
void close() 立即释放此 Connection 对象的数据库和 JDBC 资源

9.3 使用 Statement 执行数据库操作

9.3.1 使用 Statement 接口更新数据

  • 获取 Connection 对象后，需要使用 Connection 对象的 createStatement() 方法创建 Statement 对象，在通过 Statement 对象 将 SQL 语句发送到 MySQL 服务器中执行操作

  • 在 Statement 接口中执行 SQL 命令的 3 种常用方法

方法名称 作用
ResultSet executeQuery(String sql) 可以执行 SQL 查询并获取 ResultSet 对象
int executeUpdate(Sting sql) 可以执行插入、删除、更新等操作，返回值时执行该操作所影响的行数
boolean execute(String sql) 可以执行任意 SQL 语句。若结果为 Result 对象，则返回 true；若结果为
更新计数或不存在任何结果，则返回 false
  • 使用 Statement 接口更新数据库中数据的步骤，与插入、修改、删除数据类似，都可以使用 executeUpdate() 方法
  • 使用Statement执行对象实现新增操作 示例
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
 * 准备工作：
 * 1.从官网下载MySql驱动包
 * 2.将驱动包引入到项目工程中
 */
public class LX_insert {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
   

        Scanner input=new Scanner(System.in);
        //设置连接数据库需要的参数
        //获取驱动类
        String driver="com.mysql.jdbc.Driver";
        //数据库连接地址
        String url="jdbc:mysql://localhost:3306/hospital?useUnicode=true&characterEncoding=UTF-8";
        //数据库用户名
        String user="root";
        //数据库密码
        String password="root";

        //加载驱动
        Class.forName(driver);
        //建立数据库连接
        Connection conn= DriverManager.getConnection(url,user,password);
        //创建 Statement 对象
        Statement statement=conn.createStatement();
        //获取要添加的数据
        System.out.print("请输入患者密码:");
        String patientPassWord=input.next();
        System.out.print("请输入患者性别:");
        String patientGender=input.next();
        System.out.print("请输入患者姓名:");
        String patientName=input.next();
        //拼接 SQL 语句
        StringBuffer sql=new StringBuffer("INSERT INTO patient(password,gender,patientName) VALUES ('");
        sql.append(patientPassWord);
        sql.append("','");
        sql.append(patientGender);
        sql.append("','");
        sql.append(patientName);
        sql.append("');");
        System.out.println(sql);
        //发送 SQL 语句,并接收结果
        int effectRowNum= statement.executeUpdate(sql.toString());
        //判断是否添加成功
        if(effectRowNum>0){
   
            System.out.println("添加成功");
        }else{
   
            System.out.println("添加失败");
        }
        //关闭资源
        statement.close();
        conn.close();
    }
}

运行结果

请输入患者密码:111111
请输入患者性别:男
请输入患者姓名:无名氏
INSERT INTO patient(password,gender,patientName) VALUES ('111111','男','无名氏');
添加成功

9.3.2 使用 Statement 接口和 ResultSet 接口查询数据

  • 使用 JDBC 查询数据库中的数据时，需要使用 ResultSet 对象来接收从数据库中返回的数据

  • ResultSet 常用方法

方法名 说明
boolean next() 将游标从当前位置向下移动一行
boolean previous() 游标从当前位置向上移动一行
void close 关闭 ResultSet 对象
int getInt(int colIndex) 以 int 形式获取结果集当前行指定列号值
int getInt(String colLabel) 以 int 形式获取结果集当前行指定列名值
float getFloat(int colIndex) 以 float 形式获取结果集当前行指定列号值
float getFloat(String colLabel) 以float形式获取结果集当前行指定列名值
String getString(int colIndex) 以String形式获取结果集当前行指定列号值
String getString(String colLabel) 以String形式获取结果集当前行指定列名值
  • 使用 Statement 接口和 ResultSet 接口查询数据 示例
import java.sql.*;

/**
 * 准备工作：
 * 1.从官网下载MySql驱动包
 * 2.将驱动包引入到项目工程中
 */
public class LX_Select {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //设置连接数据库需要的参数
        //获取驱动类
        String driver="com.mysql.jdbc.Driver";
        //数据库连接地址
        String url="jdbc:mysql://localhost:3306/hospital?useUnicode=true&characterEncoding=UTF-8";
        //数据库用户名
        String user="root";
        //数据库密码
        String password="root";

        //加载驱动
        Class.forName(driver);
        //建立数据库连接
        Connection conn= DriverManager.getConnection(url, user, password);
        //创建 Statement 对象
        Statement statement=conn.createStatement();
        //SQL 语句
        String sql="SELECT * FROM patient";
        //向 MySQL 发送 SQL 语句,并接收结果
        ResultSet rs=statement.executeQuery(sql);
        //输出结果
        //判断结果是否还有下一个
        while(rs.next()){
            //如果有下一个 输出 patientID列、patientName列
            System.out.println("病人编号:"+rs.getString("patientID")+"病人姓名:"+rs.getString("patientName"));
        }
        //关闭资源
        rs.close();
        statement.close();
        conn.close();

    }
}

运行结果

病人编号:1病人姓名:夏颖
病人编号:2病人姓名:李政
病人编号:3病人姓名:李沁
病人编号:4病人姓名:李思雨
病人编号:5病人姓名:夏天
病人编号:6病人姓名:刘占波
病人编号:7病人姓名:廖慧颖
病人编号:8病人姓名:李伟忠
病人编号:9病人姓名:姚维新
病人编号:10病人姓名:陈建
病人编号:11病人姓名:林永清
病人编号:12病人姓名:李亚
病人编号:13病人姓名:张菲

9.4 使用 PreparedStatement 接口防止 SQL 注入

9.4.1 SQL 注入攻击

  • SQL 注入攻击是是一种注入攻击，应用程序没有对用户输入数据的合法性进行判断，导致应用程序存在安全隐患，最终达到欺骗服务器执行恶意的SQL命令
  • 用户提交一段 SQL 代码，执行超出其权限的数据操作就被成为 SQL 注入攻击
  • 现在为医院管理系统开发登录功能，要求用户输入密码和姓名，判断是否通过验证

代码演示

import java.sql.*;
import java.util.Scanner;
import java.sql.*;

/**
 * 准备工作：
 * 1.从官网下载MySql驱动包
 * 2.将驱动包引入到项目工程中
 */

public class LX_SQL {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
   
        Scanner input=new Scanner(System.in);
        //设置连接数据库需要的参数
        //获取驱动类
        String driver="com.mysql.jdbc.Driver";
        //数据库连接地址
        String url="jdbc:mysql://localhost:3306/hospital?useUnicode=true&characterEncoding=UTF-8";
        //数据库用户名
        String user="root";
        //数据库密码
        String password="root";
        //接收用户输入的姓名和密码
        System.out.print("请输入患者姓名:");
        String patientName=input.next();
        System.out.print("请输入患者密码:");
        String patientPassword=input.next();
        //加载驱动
        Class.forName(driver);
        //建立数据库连接
        Connection conn= DriverManager.getConnection(url, user, password);
        //创建 Statement 对象
        Statement statement=conn.createStatement();
        //SQL 语句
        StringBuffer sql=new StringBuffer("SELECT * FROM patient where patientName='");
        sql.append(patientName);
        sql.append("'and password='");
        sql.append(patientPassword);
        sql.append("';");
        //向 MySQL 发送 SQL 语句,并接收结果
        ResultSet rs=statement.executeQuery(sql.toString());
        //判断输入的是否正确
        if(rs.next()){
   
            System.out.println("登录成功");
            System.out.println("病人姓名:"+rs.getString("patientName"));
        }else{
   
            System.out.println("登录失败");
        }
        //关闭资源
        rs.close();
        statement.close();
        conn.close();

    }
    }

效果演示

请输入患者姓名:夏颖
请输入患者密码:123456
登录成功
病人姓名:夏颖

使用 SQL注入攻击 效果演示

请输入患者姓名:*-*
请输入患者密码:100'OR'1'='1
登录成功
病人姓名:夏颖
  • 由上方演示可看出，这是典型的 SQL 注入攻击
  • 原因时在使用 Statement 接口方法时要进行 SQL 语句的拼接。拼接不仅繁琐且容易出错
  • 使用 PreparedStatement 可以规避这些问题

9.4.2 使用 PreparedStatement 接口查询数据

  • PreparedStatement 接口继承自 Statement 接口
  • PreparedStatement 接口的常用方法
方法 作用
boolean execute() 执行 SQL 语句，该语句可以是任何 SQL 语句。若结果为 Result 对象，
则返回 true；若结果是更新计数或没有结果，则返回 false
ResultSet executeQuery() 执行 SQL 查询，并返回该查询生成的 ResultSet 对象
int executeUpdate() 执行 SQL 语句，该语句必须是一个 DML 语句，如 INSERT、UPDATE 或 DELETE
语句；或者是无返回内容的 SQL 语句和 DDL 语句。返回值时执行该操作所影响的行数
void sexXxx(int index,xxx x) 方法名 Xxx 和第二个参数的 xxx 均表示如 int、float、double 等基本数据类型，
并且两个类型必须一致，参数列表中的 x 表示方法的形式参数。把指定数据类型 (xxx)
的值 x 设置为 index 位置的参数。根据参数类型的不同，常见的方法有
setInt(int index,int x)、setFloat(int index,float x),setDouble(int index,double x) 等
void setObject(int index,Object x) 除基本数据类型外，参数类型也可以使 Object，可以将 Object 对象 x 设置为 index 位置的参数

9.4.3 创建 PreparedStatement 对象

1、创建 PreparedStatement 对象

  • 通过 Connection 接口的 prepareStatement (String sql) 方法创建 PreparedStatement 对象

  • 在创建 PreparedStatement 对象时应设置号该对象要执行的 SQL 语句

  • SQL 语句可以具有一个或多个输入参数时，这些输入参数的值在 PreparedStatement 创建时违背指定，而

    是为每个参数保留一个 ？ 作为占位符

  • 因为 SQL 语句的参数已经固定，所以不会出现 SQL 注入的漏洞

  • 在 SQL 语句中使用 ？ 作为占位符 演示

String sql="SELECT * FROM patient where patientName=? and password=?"

2、设置输入参数的值

  • 通过调用 setXxx() 方法完成参数赋值
  • Xxx 时与该参数类型相应的类型，若参数是 String 类型，则使用方法 setString()
  • setXxx() 方法的第一个参数选择设置某个参数 (从1开始计数)
  • 第二个参数用于设置该参数的值
  • 设置 SQL 语句中参数的值 演示
String sql="SELECT * FROM patient where patientName=? and password=?";
PreparedStatement pps=conn.prepareStatement(sql);
pps.setString(1,"夏颖");
pss.setString(2,"123456")

3、使用 PreparedStatement 升级医院管理系统开发登录功能

  • 代码演示
import java.sql.*;
import java.util.Scanner;
import java.sql.*;

/**
 * 准备工作：
 * 1.从官网下载MySql驱动包
 * 2.将驱动包引入到项目工程中
 */

public class LX_SQL {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
   
        Scanner input=new Scanner(System.in);
        //设置连接数据库需要的参数
        //获取驱动类
        String driver="com.mysql.jdbc.Driver";
        //数据库连接地址
        String url="jdbc:mysql://localhost:3306/hospital?useUnicode=true&characterEncoding=UTF-8";
        //数据库用户名
        String user="root";
        //数据库密码
        String password="root";
        //接收用户输入的姓名和密码
        System.out.print("请输入患者姓名:");
        String patientName=input.next();
        System.out.print("请输入患者密码:");
        String patientPassword=input.next();
        //加载驱动
        Class.forName(driver);
        //建立数据库连接
        Connection conn= DriverManager.getConnection(url, user, password);
        //SQL 语句
        String sql="SELECT * FROM patient where patientName=? and password=?";
        //创建 PreparedStatement 对象
        PreparedStatement pps=conn.prepareStatement(sql);
        //设置参数值
        pps.setString(1,patientName);
        pps.setString(2,patientPassword);
        //输出 SQL 语句
        System.out.println(pps.toString());
        //接收查询数据
        ResultSet rs=pps.executeQuery();
        //判断输入的是否正确
        if(rs.next()){
   
            System.out.println("登录成功");
            System.out.println("病人姓名:"+rs.getString("patientName"));
        }else{
   
            System.out.println("登录失败");
        }
        //关闭资源
        rs.close();
        pps.close();
        conn.close();

    }
    }

运行效果展示

请输入患者姓名:夏颖
请输入患者密码:123456
com.mysql.jdbc.JDBC4PreparedStatement@20ad9418: SELECT * FROM patient where patientName='夏颖' and password='123456'
登录成功
病人姓名:夏颖

测试 SQL 注入攻击 运行效果展示

请输入患者姓名:夏颖
请输入患者密码:100'OR'1'='1
com.mysql.jdbc.JDBC4PreparedStatement@20ad9418: SELECT * FROM patient where patientName='夏颖' and password='100\'OR\'1\'=\'1'
登录失败

9.4.4 PreparedStatement 的优势

  • 可读性高和可维护性高
  • SQL 语句的执行性能高
  • 安全性高

十、数据访问和 DAO 模式

10.1 Properties 配置文件

10.1.1 Properties 配置文件简介

  • Java 中有一个比较重要的 Properties 类，可以读取 Java 配置文件
  • 可以把常用的配置信息写在配置文件中，方便软件工程师进行维护和修改
  • 即可以将操作数据库所用的服务器参数写入配置文件中
  • Java 配置文件通常是扩展名为 .properties 的文件
  • 文件中以 “键=值” 的格式存储数据
  • 可以使用 “#” 添加注释
  • 为数据库访问添加的配置文件会被命名为 datebase.properties

10.1.2 使用 Properties 配置文件的步骤

1. 添加 properties 文件

  • 选中项目下的 src 文件并右击，在弹出的快捷键菜单中执行 “New-File” 命令
  • 再打开的 “New File” 创建文件，后缀为 properties

2. 编辑配置文件

  • 在 database.properties 文件中添加配置信息

  • 在连接数据库时，可能会修改的部分时数据库驱动 (driver)、数据库连接 URL (url)、

    连接数据库的用户名 (user) 和密码 (password)

  • 按照 Properties 文件格式填写配置文件，如下

dirver=com.mysql.jdbc.Driver;
url=jdbc:mysql://localhost:3306/hospital?useUnicode=ture&characterEncoding=UTF-8
user=root
password=root
  • 配置文件中的数据格式和 Java 中的 Map 结构相似，等号之前是 键、等号之后是指
  • 配置文件当中，键应该唯一，但值可以不唯一

3. 读取配置信息

  • 使用 java.util 包下的 Properties 类读取配置文件，Properties 类继承自 Hashtable 类
  • Properties 类的常用方法
方法 描述
String getProperty(String key) 用指定的键在此属性列表中搜索属性，通过参数 key 得到其所对应的值
Object serProperty(String key,String value) 通过调用基类 Hashtable 的 put() 方法设置键-值对
void load(InputStream inStream) 从输入流中读取属性列表 (键和元素对)，通过对指定文件进行装载获取
该文件中所有的键-值对
void clear() 清除所装载的键-值对，该方法由基类 Hashtable 提供

10.1.3 使用 Properties 配置文件来配置文件的参数

  • 编写一个 DBUtil 工具类，来方便以后数据库的连接
  • 编写 inti() 方法，用来配置连接数据库的参数
public class DBUtil {
   
    public static String driver;
    public static String url;
    public static String user;
    public static String password;
    //静态方法，创建该类时自动调用
    static{
   
        init();
    }
    //配置文件
    public static void init(){
   
        String file="database.properties";
        InputStream in=DBUtil.class.getClassLoader().getResourceAsStream(file);
        Properties ties=new Properties();
        try {
   
            ties.load(in);
        } catch (IOException e) {
   
            throw new RuntimeException(e);
        }
        driver= ties.getProperty("driver");
        url=ties.getProperty("url");
        user=ties.getProperty("user");
        password=ties.getProperty("password");
    }
  • 再次连接数据库时，则不需要在配置 driver 等参数，直接调用该类的静态变量即可

10.2 DAO 模式

10.2.1 DAO 模式 概念

  • DAO（Data Access Object）数据访问对象。主要的功能就是用于进行数据操作的，在程序的标准开发架构中属于数据层的操作
  • 方便在不同的数据库之间切换
  • DAO 模式主要有三个参与者
    • 数据访问对象接口： 定义在一个模型对象上要执行的标准操作
    • 数据访问对象实体类：实现数据访问对象接口，负责从数据库等存储机制中获取数据
    • 模型对象/数值对象：用来存储获取到的数据的类对象
  • DAO模式的UML图
Student
-String name
-int rollNo
+Studnet()
+getName() : String
+setName() : void
+getRollNo() : String
+setName() : void
StudnetDao
+getInsertStudents() : void
+deleteStudent() : void
+updateStudent() : void
+selectAllStudnet() : list
DaoPatternDemo
+main() : void
StudentDaoImpl
-students List
+StudentDaoImpl() : List
+getInsertStudents() : void
+deleteStudent() : void
+updateStudent() : void
+selectAllStudnet() : list
  • 上图中，Student 为模型对象/数值对象（实体类）
  • StudentDao 为数据访问对象接口，规定了对student类型的数据要进行那些操作
  • StudentDaoImpl 是数据访问对象实体类的角色，具体实现了如何从数据库获取数据，来进行这些增删改查的操作

10.2.2 实体类

  • Entity（实体类）是在 Java 项目中，与数据表相对应的类

  • 实体类特征

    • 属性一般使用private修饰
    • 提供public修饰的getter/setter方法
    • 实体类提供无参构造方法，根据业务提供有参构造
    • 实现java.io.Serializable接口，支持序列化机制，可以将该对象转换成字节序列而保存在磁盘上或在网络上传输

  • 操作数据库时，会将数据库中的实体及关系以实体类的方式组织起来

  • 通常在 Java 工程中，创建一个名为 entity 的 Package 集合保存实体类

  • 示例

以下表格为 patient 表中的字段，构造 patient 实体，实体类中的属性与 patient 表中的字段相对应

名 类型 长度 小数点 不是null 虚拟 键 注释
patientID int 4 0 ☑ □ 主键 病人编号
password varchar 20 0 ☑ □ 登录密码
birthDate date 0 0 □ □ 出生日期
gender varchar 4 0 ☑ □ 性别
patirentName varchar 50 0 ☑ □ 病人姓名
phoneNum varchar 50 0 □ □ 联系电话
email varchar 70 0 □ □ 邮箱
identityNum varchar 20 0 □ □ 身份证号
address varchar 255 0 □ □ 地址

在 Java 项目中构造 patient 表的实体类

package cn.QiaoYun.entity;

/*
 * 患者表
 * */
public class Patient {
   

  private int patientId;
  private String password;
  private String birthDate;
  private String gender;
  private String patientName;
  private String phoneNum;
  private String email;
  private String identityNum;
  private String address;

  public int getPatientId() {
   
    return patientId;
  }

  public void setPatientId(int patientId) {
   
    this.patientId = patientId;
  }

  public String getPassword() {
   
    return password;
  }

  public void setPassword(String password) {
   
    this.password = password;
  }

  public String getBirthDate() {
   
    return birthDate;
  }

  public void setBirthDate(String birthDate) {
   
    this.birthDate = birthDate;
  }

  public String getGender() {
   
    return gender;
  }

  public void setGender(String gender) {
   
    this.gender = gender;
  }

  public String getPatientName() {
   
    return patientName;
  }

  <
最低0.47元/天 解锁文章
兰巧儿
关注
专栏目录
GameOfLife：用于人生游戏的Java模拟软件[学校项目]
02-14
项目主要采用了Java语言进行开发，Java作为一种跨平台的面向对象编程语言，以其强大的类库和稳定的性能，成为了开发此类应用的理想选择。同时，项目中可能运用了JavaFX库，这是一个用于创建桌面和移动应用程序的Java...
java 数据库 编程_Java MySQL数据库编程详解
weixin_30120421的博客
02-24 522
Java数据库编程一般可以分为以下几个步骤：1、使用之前记得要在工程的build path中添加MySQL-connector-Java的 jar 包2、加载和注册驱动程序，com.mysql.jdbc.Driver为JDBC驱3、java驱动程序名称Class.forName("com.mysql.jdbc.Driver");4、连接数据库// mysql为驱动程序类型，localhost为数...
JAVA进阶 MySQL数据库编程技术
张锦的博客
05-09 2606
仅为个人学习使用。
MySQL数据库之Java中如何使用数据库【JDBC编程】
qq_61903414的博客
08-15 1874
在实际的开发过程中，比如新增数据，删除数据等SQL语句由于可能数据量级大，不能一条一条的手动敲代码，而是利用各种数据库所提供的API。不同的数据库API有所差异，但在Java里则提供了风格统一的数据库API，只要掌握这套Java的API，就可以操作各种数据库。创建后找到下载的驱动包点击复制后拷贝到该目录，然后右击拷贝的jar，添加为库就好了。与基本流程一样，先创建数据源，然后建立连接，然后构造请求，发送请求，释放资源。这里注意的是，先创建的后释放，后创建的先释放。在官网找到与数据库对象的驱动包下载即可。..
java mysql开发_Java数据库开发
weixin_42547335的博客
01-18 222
Nosql数据库使用场景首先需要确认一个问题，nosql能做什么？在现在的开发领域中nosql可以实现文档存储(BSON、JSON)、缓存存储、图像缓存(图像搜索)，但是对于nosql的具体应用场景完全要根据你实际的业务来讲；· 在传统的开发之中由于经常要使用到多表查询，性能很差，所以可以将一些经常显示的数据整理到文档型的nosql数据库(MongoDB)，但是现在这个文档型的nosql使用越来越...
Java开发手册之MySQL数据库
weixin_53954158的博客
03-08 555
说明：以学生和成绩的关系为例，学生表中的 student_id是主键，那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。说明：float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不 正确的结果。如果存储的数据范围超过decimal 的范围，建议将。说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用。
BCSP小高组测评题.pdf
最新发布
07-13
形成 “数字教育+科技创新”的新质生产力，推进北京高水平人才高地建设工作，北京青少年科技教育协会拟组织 BCSP-X 活动，面向北京市中小学生进行信息学能力测评，旨在进行信息学人才的早发现、早培养。 活动内容 ...
北京市bcsp-x样题
05-25
这是第一届bcsp 这是样题 文件列表 BCSP-X2024第一轮报名通知.pdf BCSP-X活动方案.pdf 活动范围.zip 样例题目.zip 希望可以帮助到大家 更多报名内容请关注 文件内报名通知 登陆网站后报名
北大青鸟 S1java全部课件
11-03
【北大青鸟S1java全部课件】是针对初学者设计的一套全面的Java学习资源，旨在帮助学员从零开始掌握Java编程语言的基础知识和核心概念。北大青鸟作为知名的IT职业教育机构，其课程设计严谨，注重理论与实践相结合，为...
Java开发手册五（MySQL数据库）
ITwolf的博客
06-13 308
(一)建表规约1.【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（1表示是，0表示否）。说明：任何字段如果为非负数，必须是unsigned。注意：POJO类中的任何布尔类型的变量，都不要加is前缀，所以，需要在<resultMap>设置从is_xxx到Xxx的映射关系。数据库表示是与否的值，使用tinyint类型，坚持is_xxx的命名方式是为了明确其取值含义与取值范围。正例：表达逻辑删除的字段名is_deleted，1表示删除，0表示未
MySQL数据库（Java的数据库编程：JDBC）
yanxiang3306
11-16 8288
JDBC，即Java Database Connectivity，java数据库连接。是一种用于执行SQL语句的Java API，它是Java中的数据库连接规范。这个API由 java.sql.*,javax.sql.* 包中的一些类和接口组成，它为Java开发人员操作数据库提供了一个标准的API，可以为多种关系数据库提供统一访问.说白了就是用Java语言来操作数据库。原来我们操作数据库是在控制台使用SQL语句来操作数据库，JDBC是用Java语言向数据库发送SQL语句。
【JAVA入门】MySQL数据库
qq_49047454的博客
10-26 1047
数据库指的是长期存在计算机内、有组织、可共享的、大量数据的集合。数据是按照特定的数据模型来组织、存储在数据库中的。MySQL 是一款安全、跨平台、高效的，并与 PHP、Java 等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持，由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的MySQL关键字。
最全的MySQL数据库入门教程（Java教程）
Aa112233aA1的博客
01-18 975
什么是数据库？MySQL是什么？SQL是什么？ 数据库，用来存储数据。由表组成，可以有0到n张表， 表就像是Excel，它适合存放一些简单的文字信息—— 一个人的身份证号码 电话号码 姓名 Excel不适合存放什么信息呢？ 照片 电影 小说 这些单个 大体量的文件对象，不会放在数据库里。 比如说，淘宝上所有的商品，背后的商品信息、店铺信息、销量等数据，就躺在数据库中的表格中，但是店铺上的宣传视频、图片、音乐，不会放在数据库中。 世界上有各种各样的数据库，但幸运的是，你只需要学会MySQL基本使用方法，就可以
JAVA(数据库mysql基本应用)
weixin_54255580的博客
12-22 736
1.create database name;创建数据库 use name;使用数据库 2.create table name（ 列名+约束 列名+约束 列名+约束 ...... 列名+约束 ） 创建表 3常用的语句为：增删改查 添加语句 ： insert into 表名 values（数据，数据，数据，） 删除语句 ： delete from 表名 where 条件 修改语句 ：Update 表名 set 列名=值，列名=值 where 条件 查询语句 ： se...
【JAVA开发规范学习之 MySQL数据库】
t194978的博客
08-06 253
数据库
java web开发（mysql开发）
嵌入式-老费，一个分享专业嵌入式知识的blog
10-03 948
​ 【 声明：版权所有，欢迎转载，请勿用于商业用途。 联系信箱：feixiaoxing @163.com】 选择了java作为项目开发语言，一般就不再会选择商业数据库了，比如说sql server，oracle之类的，除非是项目有特殊需求。大多数情况下，开发者都会选择mysql作为应用数据库。前面谈到了mvc中的c和v，即servlet和jsp，那么现在就应该说一说m了，也就是model。 model，美其名曰模型，大多数情况下就是数据库。而数据库本身大多数又是和业务绑定在
java进阶--数据库篇（Mysql）
weixin_44149429的博客
11-09 1440
MySQL数据库 MySQL索引 MySQL事务 MySQL分库分表
javaweb学习笔记
WOAIWXN的博客
11-01 392
目录 第一章 开发工具 1.1、JDK安装 1.2、Tomcat安装 1.3、IDEA安装 1.4、IDEA集成Tomcat 1.5、IDEA运行JavaWeb 第二章 XML（选学） 2.1、XML的概述 2.2、XML的语法 2.3、XML的解析 第三章 YAML（选学） 3.1、YAML的概述 3.2、YAML的语法 3.3、YAML的解析 第四章 Servlet 4.1、Servlet概述 4.2、Servlet语法格式
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值