# 中医药Java **Repository Path**: Zhang-Meng-021101/source ## Basic Information - **Project Name**: 中医药Java - **Description**: 一个Java学习仓库 - **Primary Language**: Java - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 2 - **Created**: 2025-10-19 - **Last Updated**: 2025-10-19 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # 一. 数据库介绍 ## 1. 什么是数据库? 1. 存储数据的仓库。 2. 本质上是一个文件系统，还是以文件的方式存在服务器的电脑上的。 3. 所有的关系型数据库都可以使用通用的 SQL 语句进行管理(操作)。 ## 2. 常见的关系型数据库 > 数据来源: https://db-engines.com/en/ranking/relational+dbms ![image-20241012140430256](image.assets/image-20241012140430256.png) MySQL ( sun公司收购了 MySQL，而 Sun 公司又被 Oracle 收购 ) - 特点：开源、易学易用、成本低、性能高、可扩展性强、支持多种存储引擎。 - 应用场景：网站、电子商务、网络游戏、数据仓库等各种规模的项目。 Oracle - 特点：面向对象、支持大规模并发、可扩展、高可用性、长期稳定支持。 - 应用场景：大型企业应用、金融、电信、医疗等行业的数据管理和分析。 SQL Server：MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用。 DB2 ：IBM 公司的数据库产品,收费的。常应用在银行系统中。 SQLite: 嵌入式的小型数据库，应用在手机端，如：Android。 ## 3. 关系型与非关系型 数据库按发展顺序，分为**：网状数据库、层次数据库、关系数据库、面向对象数据库**。其中关系数据库是理论最成熟、应用最广泛的数据库。在大量数据的查找、排序操作上非常成熟且快速，并对数据库的并发、隔离有非常完善的解决方案。 所有类型数据库排名 ![image-20241012142110375](image.assets/image-20241012142110375.png) 关系型数据库 最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织. ![image-20241012142537298](image.assets/image-20241012142537298.png) 优点： 1、易于维护：都是使用表结构，格式一致； 2、使用方便：SQL语言通用，可用于复杂查询； 3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。 缺点： 1、读写性能较差，尤其是海量数据的高效率读写； 2、硬盘I/O要求高：网站的用户并发性非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘I/O是一个很大的瓶颈 3、拓展困难：在基于web的结构当中，数据库是最难进行横向扩展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库却没有办法像web server和app server那样简单的通过添加更多的硬件和服务节点来扩展性能和负载能力。当需要对数据库系统进行升级和扩展时，往往需要停机维护和数据迁移。 4、性能欠佳：在关系型数据库中，导致性能欠佳的最主要原因是多表的关联查询，以及复杂的数据分析类型的复杂SQL报表查询。为了保证数据库的ACID特性（原子性、一致性、隔离性、持久性），必须尽量按照其要求的范式进行设计，关系型数据库中的表都是存储一个格式化的数据结构。 非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。 ![image-20241012142936534](image.assets/image-20241012142936534.png) **面向高性能并发读写的key-value数据库**： 是一种以键值对存储数据的一种数据库，类似Java中的map，主要特点是具有极高的并发读写性能。 主流代表为Redis， Amazon DynamoDB， Memcached， Microsoft Azure Cosmos DB和Hazelcast **面向海量数据访问的面向文档数据库**： 主要特点是在海量的数据中可以快速的查询数据。文档存储通常使用内部表示法，可以直接在应用程序中处理，主要是JSON。JSON文档也可以作为纯文本存储在键值存储或关系数据库系统中。 主流代表为MongoDB，Amazon DynamoDB，Couchbase， Microsoft Azure Cosmos DB和CouchDB **面向搜索数据内容的搜索引擎**： 搜索引擎是专门用于搜索数据内容的NoSQL[数据库管理](https://cloud.tencent.com/product/dbbrain?from_column=20065&from=20065)系统。 主要是用于对海量数据进行近实时的处理和分析处理，可用于机器学习和数据挖掘。主流代表为Elasticsearch，Splunk，Solr，MarkLogic和Sphinx **面向可扩展性的**[**分布式数据库**](https://cloud.tencent.com/product/tddbms?from_column=20065&from=20065)： 主要特点是具有很强的可拓展性，普通的关系型数据库都是以行为单位来存储数据的，擅长以行为单位的读入处理，比如特定条件数据的获取。因此，关系型数据库也被成为面向行的数据库。相反，面向列的数据库是以列为单位来存储数据的，擅长以列为单位读入数据。 这类数据库想解决的问题就是传统数据库存在可扩展性上的缺陷，这类数据库可以适应数据量的增加以及数据结构的变化，将数据存储在记录中，能够容纳大量动态列。由于列名和记录键不是固定的，并且由于记录可能有数十亿列，因此可扩展性存储可以看作是二维键值存储。 主流代表为Cassandra，HBase，Microsoft Azure Cosmos DB， Datastax Enterprise和Accumulo **CAP理论** 一个分布式系统不可能同时满足C(一致性)、A(可用性)、P(分区容错性/严格性)三个基本需求，并且最多只能满足其中的两项。对于一个分布式系统来说，分区容错是基本需求，否则不能称之为分布式系统，因此需要在C和A之间寻求平衡 一致性是指更新操作成功并返回客户端完成后，所有节点在同一时间的数据完全一致。可用性是指服务一直可用，而且是正常响应时间。分区容错性是指分布式系统在遇到某节点或网络分区故障的时候，仍然能够对外提供满足一致性和可用性的服务。 优点： 1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。 2、查询便捷：可以根据需要去添加自己需要的字段，为了获取用户的不同信息，不像关系型数据库中，要对多表进行关联查询。仅需要根据id取出相应的value就可以完成查询。 3、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘； 4、高扩展性：Nosql基于键值对，数据之间没有耦合性，所以非常容易水平扩展。关系型数据库有类似join这样的多表查询机制的限制导致扩展很艰难。 5、成本低：nosql数据库部署简单，基本都是开源软件。 缺点： 1、不提供sql支持，学习和使用成本较高； 2、无事务处理； 3、只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库显的更为合适。 4、不适合持久存储海量数据 ## 4. 数据库管理系统 数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。 数据库管理程序(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。 数据库管理系统、数据库和表的关系如图所示： ![image-20241012145734649](image.assets/image-20241012145734649.png) 上图所示: 1) 一个数据库服务器包含多个库。 2) 一个数据库包含多张表。 3) 一张表包含多条记录。 对于关系数据库而言，最基本的数据存储单元就是数据表，我们可以简单的把数据库理解为大量数据表的集合。 数据表示存储数据的逻辑单元，数据表可以理解为表格，其中每一行称为一条记录，每一列称为一个字段。为数据库建表时，通常需要指定该表包含多少列，每列的数据类型信息，不需要指定数据表包含多少行，因为数据库表的行是动态改变的。每行用于保存一条用户数据，除此之外还需要为每个数据表指定一个特殊列，该特殊列的值可以唯一的确定一条记录，则该特殊列被称为主键列。 # 二. mysql8 和 navicat 安装 ## 1. 安装 和 目录结构 听我说. Mysql8版本 默认安装到C盘: ![image-20241012145627461](image.assets/image-20241012145627461.png) 同时还会出现在隐藏目录ProgramData中: ![image-20241012145637715](image.assets/image-20241012145637715.png) 其中重点目录: | 目录结构 | 描述 | | ----------------------------- | -------------------------------- | | MySQL Server 8.0\bin 普通 | 所有mysql的可执行文件 | | MySQL Server 8.0\my.ini 隐藏 | Mysql的配置文件,一般不建议去修改 | | MySQL Server 8.0\Data 隐藏 | 数据库文件所在的文件夹 | ## 2. 启动与登录 Mysql: 分为 mysql 服务器 和 mysql 客户端工具 , 工作台workbench 是 msyql的客户端工具 . 你要确保 mysql服务 启动成功, 才能使用 客户端工具 连接 服务 ### A. 服务启动方式一 服务 -> mysql80 -> 右键 ![image-20241012144416655](image.assets/image-20241012144416655.png) ### B. 服务启动方式二 前提: 配置环境变量 ![image-20241012144820083](image.assets/image-20241012144820083.png) ### C. 桌面任务栏右下角小海豚图标 (部分同学没有 我也没有) ### D. 控制台连接数据库 ==先把mysql里的bin路径配置到 系统环境变量的 path中.== MySQL 是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的 root 账号，使用安装时设置的密码即可登录。 ```sql mysql -u用户名 -p密码 -h主机 ``` 由于MySQL 服务在本机,所以-h可以不写: ![image-20241012145001541](image.assets/image-20241012145001541.png) 后输入密码方式: ![image-20241012145012995](image.assets/image-20241012145012995.png) 退出: ![image-20241012145024016](image.assets/image-20241012145024016.png) ![image-20241012145132017](image.assets/image-20241012145132017.png) -h之后是要连接的mysql服务端的地址 , 因为是本机 就可以写 localhost 或 127.0.0.1 也可以省略不写 -h 假设 mysql服务器在 201.12.34.233 上面 , 则 -h后就得写201.12.34.233 ## 3. Navicat图形化工具-客户端 Navicat Premium 是一套数据库开发工具，让你从单一应用程序中同时连接 MySQL、MariaDB、MongoDB、SQL Server、Oracle、PostgreSQL 和 SQLite 数据库。它与 Amazon RDS、Amazon Aurora、Amazon Redshift、Microsoft Azure、Oracle Cloud、MongoDB Atlas、阿里云、腾讯云和华为云等云数据库兼容。你可以快速轻松地创建、管理和维护数据库。 ![image-20241012145533656](image.assets/image-20241012145533656.png) 安装听我说. 使用navicat 连接数据库: ![image-20241012145543910](image.assets/image-20241012145543910.png) 目前常用的数据库图形化工具有很多,你可以使用SQLyog等客户端工具. # 三. SQL ## 1. sql作用 Structured Query Language 结构化查询语言。 1) 是一种所有关系型数据库的查询规范，不同的数据库都支持。 2) 通用的数据库操作语言，可以用在不同的数据库中。 3) 不同的数据库 SQL 语句有一些区别。 ![image-20241012150015216](image.assets/image-20241012150015216.png) ## 2. sql语法 SQL语句的关键字不区分大小写，即：select和SELECT的作用完全一样。 在SQL命令中也可能需要使用标识符，标识符可以用于定义表名、列名，也可用于定义变量等。这些标识符的命名规则如下： 1) 标识符通常必须以字母开头。 2) 标识符包括字母、数字和三个特殊字符（# _ $）。 3) 不要使用当前数据库系统的关键字、保留字，通常建议使用多个单词连缀而成，单词之间以_分隔。 4) sql语句以分号结尾. sql语句可以使用空格和回车增加语句的美化可读性。 5) 关键字的大小写不区分, 数值的大小写区分。 注: 多个单词组成, 数据库里推荐 使用下划线连接 , 比如: 商品名称 goods_name 在java里 推荐驼峰 , goodsName , 之后 会把数据库表和java里的类 进行对应, 在没有框架或其他工具类的支持下,我们可以暂时的设计数据库里相关名字时, 向java妥协一下, 采用跟java一样的驼峰方式命名. goodsName goods_name ## 3. sql分类 1) Data Definition Language (DDL 数据定义语言) 如：建库，建表。 2) Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改。 3) Data Query Language(DQL 数据查询语言)，如：对表中的查询操作。 4) Data Control Language(DCL 数据控制语言)，如：对用户权限的设置。 ## 4. DDL DDL语句是操作数据库对象的语句，包括创建（create）、删除（drop）和修改（alter）数据库对象。 前面已经介绍过，最基本的数据库对象是数据表，数据表示存储数据的逻辑单元。 但数据库里绝不仅仅是包含数据表，也包含如下几种常见的数据库对象： | 对象名称 | 对应关键字 | 描述 | | -------- | ---------- | ---------------------------------------------------------------------------- | | 表 | table | 表是存储数据的逻辑单元，以行和列的形式存在：列就是字段，行就是记录 | | 约束 | constraint | 执行数据校验的规则，用于保证数据完整性的规则 | | 视图 | view | 一个或者多个数据表里数据的逻辑显示，视图并不存储数据 | | 索引 | index | 用于提高查询性能，相当于书的目录 | | 函数 | function | 用于完成一次特定的计算，具有返回值 | | 存储过程 | procedure | 用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境 | | 触发器 | trigger | 相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理 | | 库 | database | 一个数据库 | 因为存在上面几种数据数据库对象，所以在create语句后面可以跟不同的关键字，用于表示要创建哪种对象，例如创建表使用create table，创建索引使用create index，创建视图使用create view，drop和alter后面也需要跟不同的关键字来表示删除、修改哪种数据库对象。 ```java # 显示多少个数据库 show databases; # 创建数据库 CREATE DATABASE 数据库名; # 判断数据库是否已经存在，不存在则创建数据库 CREATE DATABASE IF NOT EXISTS 数据库名; # 查看所有数据库 SHOW DATABASES; # 查看某个数据库的定义信息 SHOW CREATE DATABASE 数据库名; # 删除数据库 DROP DATABASE IF EXISTS 数据库名; # 使用/切换数据库 USE 数据库名; ``` 创建表 ```sql CREATE TABLE 表名 ( 字段名 1 字段类型 1, 字段名 2 字段类型 2 ); ``` mysql支持的详细的数据类型 | 列类型 | 说明 | | ------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | tinyint/smallint/mediumint/int/bigint | 1字节/2字节/3字节/4字节/8字节整数，又可分为有符号和无符号两种。这些整数类型的区别仅仅是表示的范围不同。 int默认最多11位, ==int(2) 必须是2位数字==。 | | float/double | 单精度、双精度浮点类型。 | | decimal/dec | 精确小数类型，相对于float和double不会产生精度丢失的问题 ==decimal(6,2)==。9999.99 120.12 `
`==6:最多6个数字 2:小数点后必须为2位== | | ==date== | 日期类型，不能保存时间，当把Java中的Date对象保存到date列时，时间部分丢失。yyyy-MM-dd | | time | 时间类型，不能保存日期，当把Java中的Date对象保存到date列时，日期部分丢失。HH:mm:ss | | ==datetime== | 日期、时间类型。
yyyy-MM-dd HH:mm:ss | | timestamp | 时间戳类型 不能没有值. 默认是系统当前时间。 ==mysql里 表的列的值无论什么类型都可以赋值null 表示没数据== | | year | 年类型，仅仅保存时间的年份。 | | ==char== | 定长字符串类型 ==char(10) 必须是10个字符的字符串==。 | | ==varchar== | 可变长度字符串类型 , ==varchar(10) 最多10个字符==。 该类型一般不超过100/255个字符 | | binary | 定长二进制字符串类型，它以二进制形式保存字符串。 | | varbinary | 可变长度的二进制字符串类型，它以二进制形式保存字符串。 | | tinyblob/blob/mediumtblob/longblob | 1字节/2字节/3字节/4字节的二进制大对象，可以用于存储图片，音乐等二进制数据，分别可存储255B/64KB/16MB/4GB大小。==不建议(禁止)向数据库存放大文件, 读取很慢! 本质是文件的IO== | | tinytext/textmediumtext/longtext | 1字节/2字节/3字节/4字节的文本对象，可以用于存储超长的字符串，分别可存储255B/64KB/16MB/4GB大小的文本。 可以是纯文本文件,比如说 存一整个网页的代码 | ```java create table if not exists student( stu_id int , stu_name varchar(20), birthday date ); ``` ```sql # 查看当前数据库里的所有表 SHOW TABLES; # 查看表结构 DESC 表名; # 查看创建表的sql语句 SHOW CREATE TABLE 表名; # 删除表 DROP TABLE IF EXISTS 表名; # 修改表结构之添加列ADD ALTER TABLE 表名 ADD 列名 类型; # 修改表结构之修改列类型 MODIFY ALTER TABLE 表名 MODIFY 列名 新类型; # 修改表结构之修改列名 CHANGE ALTER TABLE 表名 CHANGE 旧列名 新列名 类型; # 修改表结构之删除列 DROP ALTER TABLE 表名 DROP 列名; # 修改表结构之修改表名 RENAME TABLE 表名 TO 新表名; ``` ## 5. DML 用于对表中的记录进行==增删改==操作 ```sql # 插入记录 INSERT [INTO] 表名 [ 字段名] VALUES ( 字段值); /* INSERT INTO 表名：表示往哪张表中添加数据 ( 字段名 1, 字段名 2, …) ： 要给哪些字段设置值 VALUES (值 值 1, 值 值 2, …) ：设置具体的值 */ ``` ```sql # 插入全部字段 INSERT INTO VALUES (值1,值2,....); # 插入部分字段 INSERT INTO 表名 (字段名1,字段名2,....) VALUES (值1,值2,....); ``` 注: 没有添加数据的字段值会使用NULL ```sql 插入所有列 insert into stu (stu_id,stu_name,birthday) values (1,'紫霞仙子','1000-10-10'); # 插入所有列 insert into stu values (2,'青霞仙子','1000-10-09'); # 插入部分列 insert into stu (stu_id,stu_name) values (3,'至尊宝'); ``` ![image-20241012152246299](image.assets/image-20241012152246299.png) ```sql # 更新表记录 UPDATE 表名 SET 列名= 值 [WHERE 条件表达式] /* UPDATE: 需要更新的表名 SET: 修改的列值 WHERE: 符合条件的记录才更新 你可以同时更新一个或多个字段。 你可以在 WHERE 子句中指定任何条件。 */ # 不带条件修改数据 UPDATE 表名 SET 字段名 = 值; -- 修改所有的行 # 带条件修改数据 UPDATE 表名 SET 字段名= 值 WHERE 字段名=值; ``` ```sql # stu表新增一列sex alter table stu add sex char(1); # 不带条件修改 , 修改所有人的性别为'女' update stu set sex = '女'; # 带条件修改 , 修改stu_id是3的birthday为'1000-05-05' update stu set birthday = '1000-05-05' where stu_id = 3; # 同时修改sex和birthday update stu set sex = '男',birthday = '1000-05-10' where stu_id = 3; ``` ![image-20241012152530478](image.assets/image-20241012152530478.png) ```sql # 删除表记录 DELETE FROM 表名 [WHERE 条件表达式] /* 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。 你可以在 WHERE 子句中指定任何条件 */ # 不带条件删除数据 DELETE FROM 表名 # 带条件删除数据 DELETE FROM 表名 WHERE 字段名=值; # 使用truncate删除表中的记录 TRUNCATE TABLE 表名; /* truncate 和 delete 的区别： Truncate 相当于删除表的结构，再创建一张表。 */ ``` ```sql # 带条件删除数据, 删除stu_id为2的数据 delete from stu where stu_id = 2; # 不带条件删除数据 , 删除表中的所有数据 delete from stu; ``` ## 6. DQL ### A. 基本查询 ```sql # 1. 查询表所有行和列的数据 SELECT * FROM 表名; # 查询所有的学生: select * from stu; # 2. 查询指定列的数据,多个列之间以逗号分隔 SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名; # 查询stu表中的stu_name 和sex 列 select stu_name , sex from stu; # 3. 指定列的别名进行查询 SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名; SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名; # 使用别名 select stu_name as 名字 , birthday as 生日 from stu; # 表使用别名 select s.stu_name as 名字 , s.birthday as 生日 from stu as s; # 4. 清除重复值 # 查询指定列并且结果不出现重复数据 SELECT DISTINCT 字段名 FROM 表名; # 查询stu表中的性别 select sex from stu; # 去掉重复的记录 select distinct sex from stu; # 5. 查询结果参与运算 SELECT 列名 1 + 固定值 FROM 表名; # 6. 某列数据和其他列数据参与运算: SELECT 列名 1 + 列名 2 FROM 表名; 准备数据：添加数学，中文成绩列,给每条记录添加对应的数学和中文成绩，查询的时候将数学和中文的成绩相加 # 查询所有人的数学成绩,以及数学+5分的结果 select math , math+5 from stu; # 查询数学和中文的和 select * , math+chinese as 总成绩 from stu; # as 可以省略 select * , math+chinese 总成绩 from stu; ``` ### B. 条件查询 为什么要条件查询? 如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对记录进行过滤。 ```sql # where筛选 SELECT 字段名 FROM 表名 WHERE 条件; 流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回 ``` 数据准备: ```sql 创建一个学生表，包含如下列： CREATE TABLE student3 ( id int, -- 编号 name varchar(20), -- 姓名 age int, -- 年龄 sex varchar(5), -- 性别 address varchar(100), -- 地址 math int, -- 数学 english int -- 英语 ); INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78), (2,'马化腾',45,'女','深圳',98,87), (3,'马景涛',55,'男','香港',56,77), (4,'柳岩7',20,'女','湖南',76,65), (5,'柳青',20,'男','湖南',86,NULL), (6,'刘德华',57,'男','香港',99,99), (7,'马德',22,'女','香港',99,99), (8,'德玛西亚',18,'男','南京',56,65); ``` | 比较运算符 | 说明 | | ------------------------------ | -------------------------------------------------------------------------------------------------------- | | > 、< 、<= 、>= 、= 、<> , != | <>在 SQL 中表示不等于，在 mysql 中也可以使用!=没有== | | BETWEEN...AND | 在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾 age between 100 and 200 | | IN( 集合) | 集合表示多个值，使用逗号分隔 age in (18,19,20) | | LIKE ' 张%' | 模糊查询 % _ ‘张_’ | | IS NULL | 查询某一列为 NULL 的值，注：不能写=NULL | ```sql -- 查询 math 分数大于 80 分的学生 select * from student3 where math>80; -- 查询 english 分数小于或等于 80 分的学生 select * from student3 where english <=80; -- 查询 age 等于 20 岁的学生 select * from student3 where age = 20; -- 查询 age 不等于 20 岁的学生，注：不等于有两种写法 select * from student3 where age <> 20; select * from student3 where age != 20; ``` | 逻辑运算符 | 说明 | | ---------- | -------------------------------------- | | and 或 && | 与，SQL 中建议使用前者，后者并不通用。 | | or 或\|\| | 或 | | not 或 ! | 非 | ```sql -- 查询 age 大于 35 且性别为男的学生(两个条件同时满足) select * from student3 where age>35 and sex='男'; -- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足) select * from student3 where age>35 or sex='男'; -- 查询 id 是 1 或 3 或 5 的学生 select * from student3 where id=1 or id=3 or id=5; ``` ```sql # in关键字 SELECT 字段名 FROM 表名 WHERE 字段 in ( 数据 1, 数据 2...); -- in 里面的每一个数据都会作为一次条件 , 只要满足条件的就会显示 ``` ```sql -- 查询 id 是 1 或 3 或 5 的学生 select * from student3 where id in(1,3,5); -- 查询 id 不是 1 或 3 或 5 的学生 select * from student3 where id not in(1,3,5); ``` ```sql # 范围查询 BETWEEN 值 值 1 AND 值 值 2 /* 表示从值 1 到值 2 范围，包头又包尾 比如：age BETWEEN 80 AND 100 相当于： age>=80 && age<=100 */ ``` ```sql -- 查询 english 成绩大于等于 75，且小于等于 90 的学生 select * from student3 where english between 75 and 90; ``` ```sql # like关键字 # LIKE 表示模糊查询 SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串'; ``` | 通配符 | 说明 | | ------ | ------------------ | | % | 匹配任意多个字符串 | | _ | 匹配一个字符 | ```sql -- 查询姓马的学生 select * from student3 where name like '马%'; select * from student3 where name like '马'; -- 查询姓名中包含'德'字的学生 select * from student3 where name like '%德%'; -- 查询姓马，且姓名有两个字的学生 select * from student3 where name like '马_'; ``` ### C. 排序查询 ```sql 通过 ORDER BY 子句，可以将查询出的结果进行排序( 排序只是显示方式，不会影响数据库中数据的顺序) SELECT 字段名 FROM 表名 WHERE 字段= 值 ORDER BY 字段名 [ASC|DESC]; ASC: 升序，默认值 DESC: 降序 ``` 单列排序: 只按某一个字段进行排序，单列排序。 组合排序: 同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。 ```sql SELECT 字段名 FROM 表名 WHERE 字段= 值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC]; ``` ```sql -- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序 select * from student order by age desc, math asc; ``` ### D. 聚合函数 之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值。==聚合函数会忽略空值 NULL==。 | SQL中的聚合函数 | 作用 | | --------------- | ---------------------- | | max( 列名) | 求这一列的最大值 | | min( 列名) | 求这一列的最小值 | | avg( 列名) | 求这一列的平均值 | | count( 列名) | 统计这一列有多少条记录 | | sum( 列名) | 对这一列求总和 | ```sql SELECT 聚合函数( 列名) FROM 表名; ``` ```sql - 查询学生总数 select count(id) as 总人数 from student; select count(*) as 总人数 from student; -- 查询年龄大于 20 的总数 select count(*) from student where age>20; -- 查询数学成绩总分 select sum(math) 总分 from student; -- 查询数学成绩平均分 select avg(math) 平均分 from student; -- 查询数学成绩最高分 select max(math) 最高分 from student; -- 查询数学成绩最低分 select min(math) 最低分 from student; ``` ### E. 分组查询 ```sql 分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组 SELECT 字段 1, 字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件]; ``` GROUP BY 怎么分组的？ 将分组字段结果中相同内容作为一组，如按性别将学生分成 2 组。 image-20241012153958409 GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。 ==分组的目的就是为了统计，一般分组会跟聚合函数一起使用。== ```sql -- 按性别进行分组，求男生和女生数学的平均分 select sex, avg(math) from student3 group by sex; ``` ![image-20241012154018216](image.assets/image-20241012154018216.png) 注意：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的 ```sql 查询男女各多少人 1) 查询所有数据,按性别分组。 2) 统计每组人数 select sex, count(*) from student3 group by sex; 查询年龄大于 25 岁的人,按性别分组,统计每组的人数 1) 先过滤掉年龄小于 25 岁的人。 2) 再分组。 3) 最后统计每组的人数 select sex, count(*) from student3 where age > 25 group by sex ; 查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据 -- 对分组查询的结果再进行过滤 SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2; ``` having 与 where 的区别: | 子名 | 作用 | | ------------ | --------------------------------------------------------------------------------------------------------------------------- | | where 子句 | 1) 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。2) where 后面不可以使用聚合函数 | | having 子句 | 1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。2) having 后面可以使用聚合函数 | ### F. 分页查询 数据准备 ```sql INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (9,'唐僧',25,'男','长安',87,78), (10,'孙悟空',18,'男','花果山',100,66), (11,'猪八戒',22,'男','高老庄',58,78), (12,'沙僧',50,'男','流沙河',77,88), (13,'白骨精',22,'女','白虎岭',66,66), (14,'蜘蛛精',23,'女','盘丝洞',88,88); ``` LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。 ```sql SELECT *| 字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT子句]; LIMIT offset,length; offset ：起始行数，从 0 开始计数，如果省略，默认就是 0 length ： 返回的行数 ``` ```sql -- 查询学生表中数据，从第 3 条开始显示，显示 6 条。 select * from student3 limit 2,6; ``` ![image-20241012154151830](image.assets/image-20241012154151830.png) ```sql -- 如果第一个参数是 0 可以省略写： select * from student3 limit 5; -- 最后如果不够 5 条，有多少显示多少 select * from student3 limit 10,5; M要查的页数 N每页几条 公式: limit (M-1)*N ,N ``` 所有查询的顺序: select 列 from 表 where 分组 having 排序 limit ## 7. DCL ### A. 用户 我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。 ```sql # 创建用户 CREATE USER ' 用户名'@' 主机名' IDENTIFIED BY '密码’; ``` ![image-20241012171647553](image.assets/image-20241012171647553.png) ```sql # 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123 create user 'user1'@'localhost' identified by '123'; # 创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123 create user 'user2'@'%' identified by '123'; ``` 注：创建的用户名都在 mysql 数据库中的 user 表中可以查看到，密码经过了加密。 ![image-20241012171712787](image.assets/image-20241012171712787.png) ```sql # 删除用户 DROP USER ' 用户名'@' 主机名'; # 删除用户user2 drop user 'user2'@'%'; ``` ### B. 权限 用户创建之后，没什么权限！需要给用户授权 ```sql # 授权语法 GRANT 权限 1, 权限 2... ON 数据库名. 表名 TO ' 用户名'@' 主机名’; 我们使用root账户 来创建用户 和 给其授权 , 授权本身也是个权限. 如果我们想要把授权这个权限 给 普通用户 , Grant ................................................to 用户@主机地址 with grant option; ``` ![image-20241012171847543](image.assets/image-20241012171847543.png) ```sql # 给 user1 用户分配对 demo这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询 grant create,alter,insert,update,select on demo.* to 'user1'@'localhost'; # 给 user2 用户分配所有权限，对所有数据库的所有表 grant all on *.* to 'user2'@'%'; ``` ```sql # 撤销授权语法 REVOKE 权限 1, 权限 2... ON 数据库. 表名 revoke all on test.* from 'user1'@'localhost'; ' 用户名'@' 主机名'; ``` ![image-20241012171934719](image.assets/image-20241012171934719.png) ```sql # 撤销 user1 用户对 demo数据库所有表的操作的权限 revoke all on demo.* from 'user1'@'localhost'; ``` ```sql # 查看权限语法 SHOW GRANTS FOR ' 用户名'@'主机名’; # 查看 user1 用户的权限 show grants for 'user1'@'localhost'; ``` ### C. 修改密码 ```sql ALTER user '用户名'@'主机名' IDENTIFIED by '新密码'; # 修改root用户的密码为 root alter user 'root'@'localhost' IDENTIFIED by 'root'; # MYSQL8 版本下 账户的密码 加密规则 要改一下 alter user 'root'@'localhost' IDENTIFIED with mysql_native_password by 'root'; ``` # 四. 表的约束 对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无 法插入到表中。约束在创建表的时候添加比较合适。 | 约束名 | 约束关键字 | | -------- | ---------------------- | | 主键 | primary key | | 唯一 | unique | | 非空 | not null | | 外键 | foreign key | | 检查约束 | check 注：mysql 不支持 | | 默认值 | default | ## 1. 主键约束 主键的作用:用来唯一标识数据库中的每一条记录。 ![image-20241012165118068](image.assets/image-20241012165118068.png) 哪个字段应该作为表的主键？ 通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。 如：身份证，学号不建议做成主键。 有时候直接也就使用了 ,当然也可以进行新设计一列 做主键. 主键关键字: primary key , 特点: 非空 not null 唯一。 创建主键方式: ```sql # 1. 在创建表的时候给字段添加主键 字段名 字段类型 PRIMARY KEY -- 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键 create table st5 ( id int primary key, -- id 为主键 name varchar(20), age int ); desc st5; ``` ![image-20241012165253707](image.assets/image-20241012165253707.png) ```sql # 2. 在已有表中添加主键 ALTER TABLE 表名 ADD PRIMARY KEY(字段名); ``` ```sql # 插入重复的主键值 insert into st5 values (1, '关羽', 30); -- 错误代码： 1062 Duplicate entry '1' for key 'PRIMARY' insert into st5 values (1, '关云长', 20); select * from st5; -- 插入 NULL 的主键值, Column 'id' cannot be null insert into st5 values (null, '关云长', 20); ``` ```sql # 删除主键 -- 删除 st5 表的主键 alter table st5 drop primary key; -- 添加主键 alter table st5 add primary key(id); ``` ```sql # 主键自增 # 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值 AUTO_INCREMENT 表示自动增长( 字段类型必须是整数类型 ) ``` ```sql -- 创建表学生表 st6, 包含字段(id, name, age)将 id 做为主键 create table st6 ( id int primary key auto_increment, -- id 为主键 name varchar(20), age int ); -- 插入数据 insert into st6 (name,age) values ('小乔',18); insert into st6 (name,age) values ('大乔',20); -- 另一种写法 insert into st6 values(null,'周瑜',35); select * from st6; ``` ![image-20241012165525627](image.assets/image-20241012165525627.png) 修改自动增长的默认起始值: 默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下列 SQL 语法 ```sql # 创建表时指定起始值 CREATE TABLE 表名( 列名 int primary key AUTO_INCREMENT ) AUTO_INCREMENT=起始值; ``` ```sql -- 指定起始值为 1000 create table st4 ( id int primary key auto_increment, name varchar(20) ) auto_increment = 1000; insert into st4 values (null, '孔明'); select * from st4; ``` ![image-20241012165805131](image.assets/image-20241012165805131.png) ```sql # 创建好以后修改起始值 ALTER TABLE 表名 AUTO_INCREMENT= 起始值; ``` ```sql alter table st4 auto_increment = 2000; insert into st4 values (null, '刘备'); select * from st4; ``` ![image-20241012170047909](image.assets/image-20241012170047909.png) ==DELETE 和 TRUNCATE 对自增长的影响:== DELETE：删除所有的记录之后，自增长没有影响。保留自增序列的值. TRUNCATE：删除以后，自增长又重新开始。TRUNCATE是把表结构DROP 之后 再CREATE。 --- 组合主键 ```sql -- 创建一个学生表 st10，组合主键 (id , name) create table st10 ( id int, name varchar(20), sex char(1), address varchar(20) default '广州', PRIMARY key (id,name) -- 联合主键: id和name的值 合起来 唯一 非空即可. ); insert into st10 values(1,'小美','女','东京'); -- [Err] 1062 - Duplicate entry '1-小美' for key 'PRIMARY' insert into st10 values(1,'小美','男','长清'); ``` ## 2. 唯一约束 什么是唯一约束： 表中某一列不能出现重复的值。 ```sql # 唯一约束的基本格式 字段名 字段类型 UNIQUE 实现唯一约束: 的列值 只能是 null 或者 必须唯一. -- 创建学生表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生 create table st7 ( id int, name varchar(20) unique ); -- 添加一个同名的学生 insert into st7 values (1, '张三'); select * from st7; -- Duplicate entry '张三' for key 'name' insert into st7 values (2, '张三'); -- 重复插入多个 null 会怎样？ insert into st7 values (2, null); insert into st7 values (3, null); -- null 没有数据 , 不存在重复的问题 ``` ## 3. 非空约束 和 默认值约束 什么是非空约束：列值不能为 null。 ```sql # 非空约束的基本语法格式 字段名 字段类型 NOT NULL -- 创建表学生表 st8, 包含字段(id,name,gender)其中 name 不能为 NULL create table st8 ( id int, name varchar(20) not null, gender char(1) ); -- 添加一条记录其中姓名不赋值 insert into st8 values (1,'张三疯','男'); select * from st8; -- Column 'name' cannot be null insert into st8 values (2,null,'男'); ``` ```sql # 默认值约束的语法格式 字段名 字段类型 DEFAULT 默认值 -- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州 create table st9 ( id int, name varchar(20), address varchar(20) default '广州' ); -- 添加一条记录,使用默认地址 insert into st9 values (1, '李四', default); insert into st9 (id,name) values (2, '李白'); -- 添加一条记录,不使用默认地址 insert into st9 values (3, '李四光', '深圳'); select * from st9; ``` ![image-20241012170606002](image.assets/image-20241012170606002.png) ## 4. 外键约束 ### A. 单表的缺点 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据 ```sql CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); -- 添加数据 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳'); ``` 缺点: 1) 数据冗余 2) 后期还会出现增删改的问题 image-20241012170841466 解决方案: image-20241012170849377 设计两张表 ```sql -- 创建部门表(id,dep_name,dep_location) -- 一方，主表 create table department( id int primary key auto_increment, dep_name varchar(20), dep_location varchar(20) ); -- 创建员工表(id,name,age,dep_id) -- 多方，从表 create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int -- 外键对应主表的主键 ); -- 添加 2 个部门 insert into department values(null, '研发部','广州'),(null, '销售部', '深圳'); select * from department; -- 添加员工,dep_id 表示员工所在的部门 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2); select * from employee; ``` 问题：当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门， 实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id image-20241012170957443 ==需要约束 dep_id 只能是 department 表中已经存在 id== ### B. 外键约束 什么是外键：在从表中与主表主键对应的那一列，如：员工表中的 dep_id 主表： 一方，用来约束别人的表 (被从表参照) 从表： 多方，被别人约束的表 (引用主表) image-20241012171031134 ```sql # 新建从表时增加外键 [CONSTRAINT] [ 外键约束名称] FOREIGN KEY( 外键字段名) REFERENCES 主表名(主键字段名) 级联操作 # 已有从表 修改表结构的方式增加外键 ALTER TABLE 从表 ADD [CONSTRAINT] [ 外键约束名称] FOREIGN KEY ( 外键字段名) REFERENCES 主表( 主键字段名); ``` ```sql -- 1) 删除副表/从表 employee drop table employee; -- 2) 创建从表 employee 并添加外键约束 emp_depid_fk -- 多方，从表 create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- 外键对应主表的主键 -- 创建外键约束 constraint emp_depid_fk foreign key (dep_id) references department(id) ); -- 3) 正常添加数据 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2); select * from employee; -- 4) 部门错误的数据添加失败 -- 插入不存在的部门 -- Cannot add or update a child row: a foreign key constraint fails INSERT INTO employee (NAME, age, dep_id) VALUES ('老张', 18, 6); ``` ```sql # 删除外键 ALTER TABLE 从表 drop foreign key 外键名称; ``` ```sql -- 删除 employee 表的 emp_depid_fk 外键 alter table employee drop foreign key emp_depid_fk; -- 在 employee 表情存在的情况下添加外键 alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id); ``` 更新和删除 ```sql -- 要把部门表中的 id 值 2，改成 5，能不能直接更新呢？ -- Cannot delete or update a parent row: a foreign key constraint fails update department set id=5 where id=2; -- 要删除部门 id 等于 1 的部门, 能不能直接删除呢？ -- Cannot delete or update a parent row: a foreign key constraint fails delete from department where id=1; ``` 级联操作: 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作 | 级联操作语法 | 描述 | | ------------------ | ---------------------------------------------------------------------------------------- | | ON UPDATE CASCADE | 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新 | | ON DELETE CASCADE | 级联删除 | | ON DELETE SET NULL | 删除时从表外键字段设置为NULL | ```sql -- 删除 employee 表，重新创建 employee 表，添加级联更新和级联删除 drop table employee; create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- 外键对应主表的主键 -- 创建外键约束 constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete set null ); -- 再次添加数据到员工表和部门表 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2); -- 删除部门表？能不能直接删除？ # [Err] 1217 - Cannot delete or update a parent row: a foreign key constraint fails drop table department; -- 把部门表中 id 等于 1 的部门改成 id 等于 10 update department set id=10 where id=1; select * from employee; select * from department; -- 删除部门号是 2 的部门 delete from department where id=2; ``` ![image-20241012171429951](image.assets/image-20241012171429951.png) # 五. 表和表之间的关系 现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们 在设计表的时候，就应该体现出表与表之间的这种关系！ | 关系 | 例子描述 | | ------ | ------------------------------------------------------------------------- | | 一对一 | 相对使用比较少。 | | 一对多 | 最常用的关系 部门和员工 大小分类 | | 多对多 | 学生选课表 和 学生表， 一门课程可以有多个学生选择，一个学生选择多门课程 | ## 1. 一对一 一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表。 两种建表原则： | 一对一的建表原则 | 说明 | | ---------------- | --------------------------------------------------------------- | | 外键唯一 | 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE | | 外键是主键 | 主表的主键和从表的主键，形成主外键关系 | ![image-20241012172332322](image.assets/image-20241012172332322.png) ## 2. 一对多 一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，大分类和小分类... 一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键 ![image-20241012172403732](image.assets/image-20241012172403732.png) ==1:n 反过来是 1:1== ## 3. 多对多 多对多（m:n） 例如：老师和学生，学生和课程，用户和角色 多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主键。 ![image-20241012172439600](image.assets/image-20241012172439600.png) ==1:n 反过来 1:n== # 六. 备份和还原 ## 1. CMD下的操作 在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。 这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。 cmd 里, 未登录的时候 ```sql mysqldump -u 用户名 -p 密码 数据库 > 文件的路径 ``` 备份 数据库demo的数据 到d:\demo.sql文件中 image-20241016135138853 还原格式：mysql 中的命令，需要登录后才可以操作 ```sql USE 数据库； SOURCE 导入文件的路径; ``` 创建demo2数据库, 把d:\demo.sql里的表和数据 还原到demo2库中 image-20241016135322112 > 这里 windows CMD里 路径分隔符 使用 / > > \ 容易报错 ## 2. Navicat 下的操作 备份数据库中的数据: 1) 选中数据库, 右键 ”转储sql文件” “结构和数据” 2) 指定导出路径，保存成.sql 文件即可。 image-20241016135407065 ![image-20241016135416936](image.assets/image-20241016135416936.png) 还原数据库中的数据 1) 创建新数据库demo3 2) 表区域右键“运行 SQL 文件”， 指定要执行的 SQL 文件，执行 3) 表区域右键刷新 ![image-20241016135447170](image.assets/image-20241016135447170.png) ![image-20241016135455046](image.assets/image-20241016135455046.png) ![image-20241016135500840](image.assets/image-20241016135500840.png) # 七. 索引 索引是存放在模式中的一个数据库对象，虽然索引总是从属于数据表，但它和数据表一样从属于数据库对象。创建索引的唯一作用就是加速对表的查询，索引通过使用快速路径访问方法来快速定位数据，从而减少了磁盘的I/O。 索引作为数据库对象，在数据字典中独立存放，但不能独立存在，必须从属于某个表。 创建索引有两种方式： - 自动：当在表上定义主键约束、唯一约束和外键约束时，系统会为该数据列自动创建对应的索引。 - 手动 ```sql create index index_name on table_name (column[,column]…); ``` ```sql # 在stu表的 stu_name 和 birthday 列上 创建索引 create index name_birth on stu (stu_name , birthday); # 查看stu表上的索引 show index from stu; ``` image-20241016135824046 删除索引的方式也有两种： - 自动：数据表被删除时，该表上的索引自动删除 - 手动 ```sql drop index 索引名 on 表名 ``` ```sql # 删除stu上的 name_birth 的索引 drop index name_birth on stu; ``` # 八. 多表查询 ## 1. 什么是多表查询 数据准备 ```sql # 创建部门表 create table dept( id int primary key auto_increment, name varchar(20) ); insert into dept (name) values ('开发部'),('市场部'),('财务部'); # 创建员工表 create table emp ( id int primary key auto_increment, name varchar(10), gender char(1), -- 性别 salary double, -- 工资 join_date date, -- 入职日期 dept_id int, foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键) ); insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1); insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2); insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2); insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3); insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1); ``` 比如: 我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。 如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。 image-20241016140902352 ## 2. 笛卡尔积 ```sql -- 需求：查询所有的员工和所有的部门 select * from emp,dept; ``` image-20241016141003090 如何清除笛卡尔积现象的影响? 我们发现不是所有的数据组合都是有用的，只有 员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。 ```sql -- 设置过滤条件 select * from emp,dept where emp.dept_id = dept.id; -- 查询员工和部门的名字 select emp.name, dept.name from emp,dept where emp.dept_id = dept.id; ``` ## 3. 内连接 用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键 ### A: 隐式内连接 看不到 JOIN 关键字，条件使用 WHERE 指定 ```sql SELECT 字段名 FROM 左表, 右表 WHERE 条件 ``` ```sql select * from emp,dept where emp.dept_id = dept.id; ``` image-20241016141235900 ### B: 显式内连接 使用 INNER JOIN ... ON 语句, 可以省略 INNER ```sql SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件 ``` ```sql # 查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能查询出需要的数据，使用内连接 select * from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧'; # 确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称 select e.id,e.name,e.gender,e.salary,d.name from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧'; ``` image-20241016141642517 我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名. 内连接查询步骤： 1) 确定查询哪些表 2) 确定表连接的条件 3) 确定查询的条件 4) 确定查询的字段 ### C: 左外连接 左外连接：使用 LEFT OUTER JOIN ... ON，OUTER 可以省略 ```sql SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件 ``` 用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工) ```sql -- 在部门表中增加一个销售部 insert into dept (name) values ('销售部'); select * from dept; -- 使用内连接查询 select * from dept d inner join emp e on d.id = e.dept_id; -- 使用左外连接查询 select * from dept d left join emp e on d.id = e.dept_id; ``` image-20241016142148387 用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工) ### D: 右外连接 右外连接：使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略 ```sql SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件 ``` 用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证右表的数据全部显示 ```sql -- 在员工表中增加一个员工 insert into emp values (null, '沙僧','男',6666,'2013-12-05',null); select * from emp; -- 使用内连接查询 select * from dept inner join emp on dept.id = emp.dept_id; -- 使用右外连接查询 select * from dept right join emp on dept.id = emp.dept_id; ``` image-20241016142303337 用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证右表的数据全部显示 ### E: 子查询 ```sql -- 需求：查询开发部中有哪些员工 select * from emp; -- 通过两条语句查询 select id from dept where name='开发部' ; select * from emp where dept_id = 1; -- 使用子查询 select * from emp where dept_id = (select id from dept where name='开发部'); ``` 子查询概念: 1) 一个查询的结果做为另一个查询的条件 2) 有查询的嵌套，内部的查询称为子查询 3) 子查询要使用括号 #### Ⅰ : 子查询的结果是单行单列 ```sql -- 1) 查询最高工资是多少 select max(salary) from emp; ``` ![image-20241016142633811](image.assets/image-20241016142633811.png) 子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、=等 ```sql SELECT 查询字段 FROM 表 WHERE 字段=(子查询); ``` 查询工资最高的员工是谁？ ```sql -- 2) 根据最高工资到员工表查询到对应的员工信息 select * from emp where salary = (select max(salary) from emp); ``` ![image-20241016143044973](image.assets/image-20241016143044973.png) 查询工资小于平均工资的员工有哪些？ ```sql -- 1) 查询平均工资是多少 select avg(salary) from emp; -- 2) 到员工表查询小于平均的员工信息 select * from emp where salary < (select avg(salary) from emp); ``` ![image-20241016143115178](image.assets/image-20241016143115178.png) #### Ⅱ: 子查询结果是多行单列 ```sql select dept_id from emp where join_date < '2013-05-01'; ``` ![image-20241016142727083](image.assets/image-20241016142727083.png) 子查询结果是单列多行，结果集类似于一个数组，父查询使用 IN 运算符 ```sql SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询); ``` 查询工资大于 5000 的员工，来自于哪些部门的名字 ```sql -- 先查询大于 5000 的员工所在的部门 id select dept_id from emp where salary > 5000; -- 再查询在这些部门 id 中部门的名字 [Err] 1242 - Subquery returns more than 1 row select name from dept where id = (select dept_id from emp where salary > 5000); -- 使用in select name from dept where id in (select dept_id from emp where salary > 5000); ``` ![image-20241016143221889](image.assets/image-20241016143221889.png) 查询开发部与财务部所有的员工信息 ```sql -- 先查询开发部与财务部的 id select id from dept where name in('开发部','财务部'); -- 再查询在这些部门 id 中有哪些员工 select * from emp where dept_id in (select id from dept where name in('开发部','财务部')); ``` ![image-20241016143257220](image.assets/image-20241016143257220.png) #### Ⅲ: 子查询结果是多行多列 ```sql select * from emp where salary between 4000 and 8000; ``` ![image-20241016142755862](image.assets/image-20241016142755862.png) 子查询结果只要是多列，肯定在 FROM 后面作为表 ```sql SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件; ``` 子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段 查询出 2011 年以后入职的员工信息，包括部门名称 ```sql -- 查询出 2011 年以后入职的员工信息，包括部门名称 -- 在员工表中查询 2011-1-1 以后入职的员工 select * from emp where join_date >='2011-1-1'; -- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.id= e.dept_id ; ``` ![image-20241016143336953](image.assets/image-20241016143336953.png) 也可以使用表连接 ```sql select * from emp inner join dept on emp.dept_id = dept.id where join_date >='2011-1-1'; ``` --- 子查询小结: 子查询结果只要是单列，则在 WHERE 后面作为条件 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询 # 九. 视图 视图看上去像一个数据表，但它不是数据表，因为它并不能存储数据。视图只是一个或多个数据表中数据的==逻辑显示==，使用视图有以下几点好处： 1、 可以限制对数据的访问 2、 可以使复杂的查询变得简单 3、 提供了数据的独立性 4、 提供了对相同数据的不同显示 因为视图只是数据表中数据的逻辑显示，即一个查询结果，所以创建视图就是建立视图名和查询语句的关联。 创建视图的语法格式如下 ```sql create or replace view 视图名 as subquery查询结果 ``` 从上面的语法可以看出，创建、修改视图都可以使用上面的语法。上面语法的含义是，如果该视图不存在，则创建视图；如果指定视图名已经存在，则使用新视图替换原有视图。后面的subquery是一个查询语句，这个查询语句可以非常的复杂。 ```sql create or replace view view_emp_dept as select e.*,d.name as dname from emp e left join dept d on e.dept_id = d.id; ``` 通过建立视图的语法规则可以看出，所谓的视图的本质，其实就是一条被命名好的SQL查询语句。 建立视图后，使用视图与使用数据表就没有什么区别了，但通常只是查询视图数据，不能修改视图里的数据，因为视图本身没有存储任何数据。 修改视图里的数据 , 原表也会受到影响. 为了强制不允许改变视图的数据，MySQL允许在创建视图时使用with check option子句，使用该子句创建的视图不允许修改，如下所示： ```sql create or replace view view_emp_sal5000 as select * from emp where salary>=5000 with check option; ``` 大部分数据库都使用with check option来强制不允许修改视图的数据，但是Oracle采用with read only来强制不允许修改视图的数据 删除视图 ```sql drop view 视图名; drop view view_emp_dept; ``` 查询视图, 视图是个表, show tables; 即可 # 十. 数据库函数 每个数据库都会在标准的SQL基础上扩展一些函数,SQL中的函数和java语言中的方法有点相似，但SQL中的函数是独立的程序单元，也就是说，调用函数时无须使用任何类、对象作为调用者，而是直接使用函数即可。 ```sql # 执行函数的语法格式 function_name（参数1，参数2，参数3.。。。。。。） ``` ```sql # 选出emp表中tea_name列的长度 select char_length(name) from emp; # 为指定日期添加一定的时间 # 使用date_add函数需要两个参数，日期、数值类型带单位 select date_add('1998-01-02',interval 12 month); -- year month day # 使用adddate函数更简单 select adddate('1998-01-02',3); #只能加天数 # 获取当前日期 select curdate(); # 获取当前时间 select curtime(); # 对字符串使用MD5进行加密 select MD5('test'); ``` MySQL还提供了一个case函数，该函数是一个流程控制函数。 case函数有两个用法，case函数的第一个用法的语法格式如下 ```sql case value when compare_value1 then result1 when compare_value2 then result2 else result3 end case函数用value和后面的compare_value1、compare_value2、…依次进行比较，如果value和指定的compare_value1相等，则返回对应的result，否则返回else后的result。 ``` ```sql # gender为 ‘女’显示为 ‘美女’ select name , (case gender when '女' then '美女' else gender end) gender from emp; ``` case函数的第二个用法的语法格式如下 ```sql case when condition1 then result1 when condition2 then result2 …….. else result end 在第二种用法中，condition1、conditon2都是返回一个boolean值的条件表达式，因此这种用法更加灵活 ``` ```sql # 查询每个人的姓名,薪水,和提升之后的薪水. # 提升方案: 小于4000的 提升为原来的2倍 在4000到6000之间的提升为原来的1.5倍 , 大于6000的不变 select name , salary , ( case when salary<=4000 then salary*2 when salary>4000 and salary<=6000 then salary*1.5 else salary end ) 提升后的薪水 from emp; ``` ![image-20241016145000578](image.assets/image-20241016145000578.png) 虽然此处介绍了一些MySQL常用函数的简单用法，但通常不推荐在Java程序中使用特定数据库的函数，因为将导致程序代码与特定的数据库耦合；如果把程序移植到其他数据库系统上时，可以能需要打开源码，重新修改SQL语句。 # 十一. 事务 ## 1. 事务的应用场景 什么是事务： 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转 账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。 事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。 模拟: ```sql -- 创建数据表 CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); -- 添加数据 INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000); ``` 模拟张三给李四转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句： 张三账号-500 李四账号+500 ```sql -- 张三账号-500 update account set balance = balance - 500 where name='张三'; -- 李四账号+500 update account set balance = balance + 500 where name='李四'; ``` 假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。我们需要保证其中一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。 ## 2. 手动提交事务 MYSQL 中可以有两种方式进行事务的操作： 1) 手动提交事务 2) 自动提交事务 默认是自动提交事务 | 功能 | Sql语句 | | -------- | ------------------ | | 开启事务 | start transaction; | | 提交事务 | commit; | | 回滚事务 | rollback; | ![image-20241016145551338](image.assets/image-20241016145551338.png) ## 3. 事务原理 事务开启之后, 所有的操作都会==临时保存到事务日志==中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接) image-20241016145623913 ## 4. 回滚点 在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。 回滚点的操作语句: | 回滚点的操作语句 | 语句 | | ---------------- | ---------------- | | 设置回滚点 | savepoint 名字 | | 回到回滚点 | rollback to 名字 | 具体操作: 1) 将数据还原到 1000 2) 开启事务 3) 让张三账号减 3 次钱，每次 10 块 4) 设置回滚点：savepoint three_times; 5) 让张三账号减 4 次钱，每次 10 块 6) 回到回滚点：rollback to three_times; 7) 分析执行过程 8) 提交回滚点之前的成功操作. 总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。 ```sql start transaction; #开启一次事务 # 操作 insert into student (stu_name,stu_motto) values ('小松','打虎'); # 创建一个 保存点 savepoint one; # 操作 insert into student (stu_name,stu_motto) values ('小鲁','大和尚'); # 尝试 回滚 到 保存点 one 成功? rollback to one; # 提交 保存点处的 部分事务. commit; # 设置本次连接的 提交方式为 不自动提交, 也就是 每次都默认开启事务, 直到commit或rollback 或 DDL set autocommit = 0; 0/1 ``` ## 5. 事务隔离级别 事务四个特性 | 事务特性 | 含义 | | -------------------- | ------------------------------------------------------------------------------------------------------------------ | | 原子性（Atomicity) | 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。 | | 一致性（Consistency) | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是2000 ，转账后2个人总金额也是2000。 | | 隔离性（Isolation ） | 事务与事务之间不应该相互影响，执行时保持隔离的状态。 ==(对共同的数据)== | | 持久性（Durability ) | 一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。(事务执行成功,提交后,会将变化改到数据库文件里) | 事务隔离级别 事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题： | 并发访问的问题 | 含义 | | ------------------------------ | ----------------------------------------------------------------------------------------------------------------------- | | 脏读 (是问题需要解决) | 一个事务读取到了另一个事务中尚未提交(有可能作废)的数据 | | 不可重复读 (一种现象不是问题) | 一个事务中两次读取的数据 内容 不一致，要求的是一个事务中多次读取时数据是一致的， 这是事务 update 时引发的问题 | | 幻读 (一种现象不是问题) | 一个事务中两次读取的数据的 数量 不一致，要求在一个事务多次读取的数据的数量是一致的，这是 insert 或 delete 时引发的问题 | MySQL数据库的四种隔离级别 上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。 | 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | | ---- | -------- | ---------------- | ---- | ---------- | ---- | | 1 | 读未提交 | read uncommitted | 是 | 是 | 是 | | 2 | 读已提交 | read committed | 否 | 是 | 是 | | 3 | 可重复读 | repeatable read | 否 | 否 | 是 | | 4 | 串行化 | serializable | 否 | 否 | 否 | 隔离级别越高，性能越差，安全性越高。 MySQL的默认隔离级别为 第3级别 repeatable read ```sql # 查看数据库隔离级别 show variables like '%transaction_isolation%'; ``` ![image-20241016150709735](image.assets/image-20241016150709735.png) 设置当前会话(连接) 或 全局 的事务隔离级别: ```sql SET SESSION | GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串; ``` ## 6. 脏读演示 首先把account表的balance恢复为1000 navicat 里,设置当前会话的隔离级别为最低 , 开启事务 ```sql # 设置当前会话的事务隔离级别为最低 set session transaction isolation level read uncommitted; # 开启事务 start transaction; # 查看数据库隔离级别 show variables like '%transaction_isolation%'; ``` ![image-20241016150816315](image.assets/image-20241016150816315.png) 打开CMD窗口,进入相同的数据库中, 并开启事务 ![image-20241016150829178](image.assets/image-20241016150829178.png) CMD窗口,更新两个人的账户, 但是未提交! ![image-20241016150923427](image.assets/image-20241016150923427.png) Navicat 查询账户 ```sql # 查询账户 select * from account; ``` ![image-20241016150944514](image.assets/image-20241016150944514.png) CMD窗口回滚 ![image-20241016150952937](image.assets/image-20241016150952937.png) Navicat 查询账户,钱没了 ```sql # 查询账户 select * from account; ``` ![image-20241016151008931](image.assets/image-20241016151008931.png) 解决: 提升事务隔离级别。 一般采用MySQL默认的隔离级别! 既可以防止脏读也可以防止不可重复读现象。最高隔离级别 serializable是串行化, 一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读。但是读写效率最低. # 十二. JDBC ## 1. JDBC入门 通过使用JDBC，Java程序可以非常方便地操作各种主流数据库。Java程序使用JDBC API以统一的方式来连接不同的数据库，并可以获得SQL语句访问数据库的结果，因此掌握标准的SQL语句是学习JDBC编程的基础 JDBC的全称是Java Database Connectivity，即：Java数据库连接，是一种可以执行SQL语句的Java API。程序通过JDBC API连接到关系数据库，并使用结构化查询语言（SQL语句，数据库标准的查询语言）来完成数据库的查询、更新。 与其他数据编程环境相比，JDBC为数据库开发提供了标准的API，所以使用JDBC开发的数据库应用可以跨平台运行，而且跨数据库。也就是说，如果使用JDBC开发一个数据库应用，这个应用既可以在Windows平台上运行，也可以在Unix等其他平台上运行。既可以是MySQL数据库，也可以使Orcle数据库。 ==JDBC 规范定义 接口 ，具体的实现由各大数据库厂商来实现。== JDBC 是 Java 访问数据库的标准规范，真正怎么操作数据库还需要具体的实现类，也就**是数据库驱动**。每个 数据库厂商根据自家数据库的通信格式编写好自己数据库的驱动。所以我们只需要会调用 JDBC 接口中的方法即可，数据库驱动由数据库厂商提供。 使用JDBC的好处 1) 程序员如果要开发访问数据库的程序，只需要会调用 JDBC 接口中的方法即可，不用关注类是如何实现的。 2) 使用同一套 Java 代码，进行少量的修改就可以访问其他 JDBC 支持的数据库 image-20241016151611198 JDBC 规范 对程序员来说 屏蔽了不同数据库的差异. JDBC开发使用到的包 | 会使用到的包 | 说明 | | ------------ | ------------------------------------------------------------ | | java.sql | 所有与 JDBC 访问数据库相关的接口和类 | | javax.sql | 数据库扩展包，提供数据库额外的功能。如：连接池 | | 数据库的驱动 | 由各大数据库厂商提供，需要额外去下载，是对 JDBC 接口实现的类 | JDBC核心API介绍 | 接口或类 | 作用 | | ----------------------- | ------------------------------------------------------------------- | | DriverManager类 | 1) 管理和注册数据库驱动2) 得到数据库连接对象 (会话) | | Connection 接口 | 一个连接对象，可用于创建 Statement 和 PreparedStatement 对象 | | Statement 接口 | 一个 SQL 语句对象，用于将 SQL 语句发送给数据库服务器。 | | PreparedStatement 接口 | 一个 SQL 语句对象，是 Statement 的子接口 | | ResultSet 接口 | 用于封装数据库查询的结果集(表)，返回给客户端 Java 程序 | | CallableStatement 接口 | 一个 SQL 语句对象，是 PreparedStatement 的子接口 , 用于调用存储过程 | | ResultSetMetaData接口 | 获取关于 ResultSet 对象中列的类型和属性信息的对象(结果集表的表结构) | | DataSource 接口 | 连接池对象 | JDBC编程步骤概览 1. 导入mysql的驱动jar包 2. 加载数据库驱动。通常我们使用Class类的forName()静态方法来加载驱动 3. 通过DriverManager获取数据库连接对象connection,当使用DriverManager来获取数据库连接时，通常需要传入三个参数，数据库的URL，登录数据库的用户名和密码。 4. 通过Connection对象创建Statement对象。 5. 使用Statement执行SQL语句。 6. 操作结果集，如果执行的SQL语句是查询语句，则执行结果返回一个ResultSet对象，该对象里保存了SQL语句查询的结果。 7. 回收数据库资源，包括关闭ResultSet、Statement和Connection等资源。 ## 2. PreparedStatement 接口 sql注入问题 假设: 数据库有用户表tab_user , 字段有主键id , 用户名 user_name,唯一 , 密码 user_pass 观察下面登录的代码会出现什么问题？ ```java //注册驱动 获取连接对象省略 Scanner scan = new Scanner(System.in); System.out.println("请输入用名："); String name = scan.nextLine(); System.out.println("请输入密码："); String pass = scan.nextLine(); String sql = "select * from tab_user where user_name=? and user_pass=?"; Statement statement = conn.createStatement(); boolean flag = statement.execute(sql); if(flag){ System.out.println("登录成功！"); } //关闭 省略 ``` 如果在程序运行时输入： 用户名：newboy 密码: a' or '1'='1 ```sql select * from tab_user where user_name='newboy' and user_pass='a' or '1'='1' ``` 上述语句 where后面 变为 条件1 and 条件2 or true , 无论用户名和密码的条件1和条件2是否为true,最后都进行了逻辑或操作 ,'1'='1' 为true , 整个where就是true 我们让用户输入的密码和 SQL 语句进行字符串拼接。用户输入的内容作为了 SQL 语句语法的一部分，改变了原有 SQL 真正的意义，以上问题称为 SQL 注入。要解决 SQL 注入就不能让用户输入的密码和我们的 SQL 语句进行简单的字符串拼接。 使用PreparedStatement 替代Statement,防止sql注入,安全性更好. PreparedStatement 是 Statement 接口的子接口，继承于父接口中所有的方法。它是一个预编译的 SQL 语句. ![image-20241016152200067](image.assets/image-20241016152200067.png) 1) 因为有预先编译的功能，提高 SQL 的执行效率。 2) 可以有效的防止 SQL 注入的问题，安全性更高。 PreparedStatement需要使用问号占位符来代替Statement中需要拼接进入sql的参数. | PreparedStatement中设置参数的方法 | 描述 | | -------------------------------------------- | ------------------------------------- | | void setDouble(int parameterIndex, double x) | 将指定参数设置为给定 Java double 值。 | | void setFloat(int parameterIndex, float x) | 将指定参数设置为给定 Java float值。 | | void setInt(int parameterIndex, int x) | 将指定参数设置为给定 Java int 值。 | | void setLong(int parameterIndex, long x) | 将指定参数设置为给定 Java long 值。 | | void setObject(int parameterIndex, Object x) | 使用给定对象设置指定参数的值。 | | void setString(int parameterIndex, String x) | 将指定参数设置为给定 Java String 值。 | parameterIndex 参数索引 从1开始.也就是第一个?的索引为1. ```java public static void main(String[] args) throws ClassNotFoundException, SQLException { // 加载驱动类 String driver = "com.mysql.cj.jdbc.Driver"; Class.forName(driver); // 数据库连接 String url = "jdbc:mysql://localhost:3306/demo2?useSSL=false&serverTimezone=Hongkong"; String user = "demo2user"; String password = "123456"; Connection con = DriverManager.getConnection(url, user, password); // sql模板 String sql = "select * from tsinger where sname = ? and sex = ?"; // 预编译声明对象 PreparedStatement pstmt = con.prepareStatement(sql); // 设置占位符的值 pstmt.setString(1, "张三"); pstmt.setString(2, "a or 1=1"); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { System.out.println("查到了数据!"); }else{ System.out.println("未查到数据"); } // 关闭; rs.close(); pstmt.close(); con.close(); } ``` 结果: 没有出现sql注入问题 结论: 推荐使用PreparedStatement来执行SQL语句，而不使用Statement来执行SQL语句。 ## 3. 规范化代码 以上的演示中遇到的异常都进行了throws声明, 此外还可以使用try-catch-finally语句进行捕获处理. 演示: 查询指定姓名和性别的歌手信息 ```java public static void main(String[] args) { String driver = "com.mysql.cj.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/demo2?useSSL=false&serverTimezone=Hongkong"; String user = "demo2user"; String password = "123456"; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName(driver); con = DriverManager.getConnection(url, user, password); String sql = "select * from tsinger where sname = ? and sex = ?";// sql模板 pstmt = con.prepareStatement(sql); pstmt.setString(1, "洛天依");// 设置占位符的值 pstmt.setString(2, "女"); rs = pstmt.executeQuery(); if (rs.next()) { System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString("sex")+"\t"+rs.getString("display")); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { if (rs!=null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt!=null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con!=null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } ``` ## 4. ResultSetMetaData接口 当执行SQL查询后可以通过移动记录指针来遍历ResultSet的每条记录，但是程序可能不知道该ResultSet结果集中包含了哪些数据列，以及每个数据列的数据类型。那么可以通过ResultSetMetaData来获取关于ResultSet的描述信息。 MetaData是元数据的意思，即描述其他数据的数据。因此ResultSetMetaData封装了描述ResultSet对象的数据. ResultSet提供了一个getMetaData()的方法，该方法返回了该ResultSet对应的ResultMetaData对象。一旦获得了ResultSetMetaData对象，就可以通过ResultSetMetaData对象提供的大量的方法，来获取相关ResultSet的描述信息，常用的方法有如下几个： | 方法 | 作用 | | ------------------------------------- | ------------------------- | | int getColumnCount() | 返回该ResultSet的列数量。 | | String getColumnName(int columnIndex) | 返回指定索引的列名。 | | int getColumnType(int column) | 返回指定索引的列类型。 | 演示: 查询emp中每个员工的信息和所在部门的信息,为了节省篇幅异常采取throws声明方式处理 ```java public static void main(String[] args) throws ClassNotFoundException, SQLException { String driver = "com.mysql.cj.jdbc.Driver"; Class.forName(driver); String url = "jdbc:mysql://localhost:3306/demo2?useSSL=false&serverTimezone=Hongkong"; String user = "demo2user"; String password = "123456"; Connection con = DriverManager.getConnection(url, user, password); String sql = "select * from emp e left join dept d on e.dept_id = d.id"; PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); //获取结果集元数据对象 ResultSetMetaData rsmd = rs.getMetaData(); //获取列数 int columnCount = rsmd.getColumnCount(); //遍历 结果集列名 for (int i = 1; i <=columnCount; i++) { System.out.print(rsmd.getColumnName(i)+"\t"); } System.out.println(); //遍历结果集 while(rs.next()) { for (int i = 1; i <=columnCount; i++) { System.out.print(rs.getString(i)+"\t"); } System.out.println(); } rs.close(); pstmt.close(); con.close(); } ``` ## 5. 工具类 JDBCUtils 什么时候自己创建工具类? 如果一个功能经常要用到，我们建议把这个功能做成一个工具类，可以在不同的地方重用。 上述所有的案例中出现了很多次重复的代码,可以把这些公共代码抽取出来。 创建JDBCUtils包含三部分内容: 1) 把几个字符串存放到properties配置文件中,通过Properties集合获取 2) 设计获取数据库连接的方法 getConnection 3) 设计关闭所有资源的方法 close 将名为mysql.properties的配置文件放置项目目录下 ```properties #driverClass driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/demo2?useSSL=false&serverTimezone=Asia/Shanghai user=demo2user password=123456 ``` ```java public class JDBCUtils { private static Properties prop = new Properties();//properties集合存放配置文件里的属性 /** * 加载配置文件 注册驱动 */ static { try { prop.load(new FileInputStream("mysql.properties")); Class.forName(prop.getProperty("driver"));//加载驱动类 } catch (Exception e) { e.printStackTrace(); throw new RuntimeException("JDBC加载配置文件出现问题!"); } } /** * 获取数据库连接 * @return */ public static Connection getConnection() { try { Connection con = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("user"), prop.getProperty("password")); return con; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("JDBCUtils.getConnection:创建数据库连接出现问题!"); } } /** * 关闭资源 * @param rs * @param stmt * @param con */ public static void close(ResultSet rs , Statement stmt , Connection con) { if (rs!=null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stmt!=null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con!=null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } ``` ## 6. 事务处理 JDBC连接也提供了事务支持，JDBC连接的事务支持由Connection提供，Connection默认打开自动提交，即关闭事务。在这种情况下，每条SQL语句一旦执行，便会立即提交到数据库，永久生效，无法对其进行回滚操作。 可以调用Connection的setAutoCommit(boolean auto)方法来关闭自动提交，开始事务.如果所有的SQL语句都执行成功了，程序可以调用Connection的commit()方法来提交事务,如果任意一条SQL执行失败，则应该调用Connection的rollback()方法回滚事务. ```java public static void main(String[] args){ Connection con = JDBCUtils.getConnection(); PreparedStatement pstmt = null; try { con.setAutoCommit(false);// 关闭自动提交, 开启事务 pstmt = con.prepareStatement("update account set balance = balance- ? where id = ?"); pstmt.setInt(1, 500); pstmt.setInt(2, 1); int rows = pstmt.executeUpdate(); System.out.println(rows+"行受影响!"); System.out.println(1/0); pstmt = con.prepareStatement("update account set balance = balance+ ? where id = ?"); pstmt.setInt(1, 500); pstmt.setInt(2, 2); rows = pstmt.executeUpdate(); System.out.println(rows+"行受影响!"); con.commit(); System.out.println("提交了!"); } catch (Exception e) { e.printStackTrace(); try { con.rollback(); System.out.println("回滚了!"); } catch (SQLException e1) { e1.printStackTrace(); } }finally { JDBCUtils.close(null, pstmt, con); } } ``` ![image-20241016153025047](image.assets/image-20241016153025047.png) ## 7. 数据库连接池 数据库连接的建立以及关闭是极其耗费系统资源的操作，在多层结构的应用环境中，这种资源的耗费对系统性能影响尤为明显。通过前面章节中所提到的使用DriverManager获得的数据库连接，一个数据库连接对象对应一个物理数据库连接，每次操作都打开一个物理连接，使用完成后立即关闭连接。频繁地打开、关闭连接将造成系统性能的降低。 数据库连接池的解决方案是：当应用程序启动时，系统主动建立足够的数据库连接，并将这些连接组成一个连接池。每次应用程序请求数据库连接时，无须重新打开连接，而是从连接池中取出已有的连接使用，使用完成后不再关闭数据库连接，而是直接将连接归还给连接池。通过使用连接池，将大大提高程序的运行效率。 对于共享资源的情况，有一个通用的设计模式：资源池（Resource Pool），用于解决资源的频繁请求、释放所造成的性能下降。为了解决数据库连接的频繁请求、释放。JDBC2.0规范引入了数据库连接池技术，数据库连接池是Connection对象的工厂。数据库连接池的常用参数如下： 数据库的初始连接数 连接池的最大连接数 连接池的最小连接数 连接池每次增加的容量 JDBC的数据库连接池使用DataSource来表示，DataSource只是一个接口，该接口通常由商用服务器，例如WebLogic或者WebSphere等提供实现，也有一些开源组织提供实现，例如C3P0和Druid等。 --- Druid连接池是阿里巴巴开源的数据库连接池项目。是当前性能顶尖的数据库连接池之一, 还内置强大的监控功能,这里只是介绍基本使用: 加入Druid的jar包: ![image-20241016153118851](image.assets/image-20241016153118851.png) 定义配置文件: 名称： druid.properties或自行设置 路径：直接将文件放在src目录下或放在source floder类型的config目录下 ![image-20241016153135445](image.assets/image-20241016153135445.png) 改写上节课里的JDBCUtils工具类 改为使用Druid连接池提供连接,另外加入返回连接池对象的方法,通常连接池对象仅仅需要一个即可. ```properties driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/demo2?useSSL=false&serverTimezone=Asia/Shanghai username=demo2user password=123456 filters=stat initialSize=2 maxActive=300 maxWait=60000 timeBetweenEvictionRunsMillis=60000 minEvictableIdleTimeMillis=300000 validationQuery=SELECT 1 testWhileIdle=true testOnBorrow=false testOnReturn=false poolPreparedStatements=false maxPoolPreparedStatementPerConnectionSize=200 ``` ```java public class JDBCUtils { private static Properties prop = new Properties();//properties集合存放配置文件里的属性 private static DataSource dataSource; /** * 加载配置文件 */ static { try { InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"); prop.load(is); dataSource = DruidDataSourceFactory.createDataSource(prop); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException("JDBCUtils加载配置文件出现问题!"); } } /** * 获取数据库连接池对象 * @return */ public static DataSource getDataSource() { return dataSource; } /** * 获取数据库连接 * @return */ public static Connection getConnection() { try { return dataSource.getConnection(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("JDBCUtils.getConnection:获取数据库连接出现问题!"); } } /** * 关闭资源 * @param rs * @param stmt * @param con */ public static void close(ResultSet rs , Statement stmt , Connection con) { if (rs!=null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stmt!=null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con!=null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } ``` ```java public static void main(String[] args) { Connection connection = JDBCUtils.getConnection(); System.out.println(connection); } ``` ![image-20241016153234970](image.assets/image-20241016153234970.png) ## 8. JDBCTemplate Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发.==尤其是简化了JDBC查询操作把结果集封装为java对象的过程==. ### A: 使用步骤 1) 导入jar包 ![image-20241016153505924](image.assets/image-20241016153505924.png) 2) 创建JdbcTemplate对象。依赖于数据源DataSource ```java JdbcTemplate template = new JdbcTemplate(连接池); ``` 3) 调用JdbcTemplate的方法来完成增删改查的操作 | 方法名 | 作用 | | ---------------- | ----------------------------------------------------------------------------------------- | | update() | 执行DML语句。增、删、改语句 | | query() | 查询结果，将结果封装为JavaBean对象 | | queryForObject() | 查询结果，将结果封装为对象一般用于聚合函数的查询 | | queryForMap() | 查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合 | | queryForList() | 查询结果将结果集封装为list集合,将每一条记录封装为一个Map集合，再将Map集合装载到List集合中 | 注: query()方法的参数一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装. 下面使用junit单元测试来演示 增删改查的操作: junit单元测试可以让方法独立执行,需要导入如下jar包: ![img](image.assets/wps1CAD.tmp.jpg) ### B: 增删改操作 ```java public class TemplateTest { private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource()); @Test public void testInsert() throws Exception { String sql = "insert into tsinger (sname,sex,salary) values(?,?,?)"; Object[] args = {"尼古拉斯凯奇","男",12345}; int rows = jdbcTemplate.update(sql, args); System.out.println(rows+"行受影响!"); } @Test public void testUpdate() throws Exception { //修改 sid为1019的记录 的display为 "实验使用" String sql = "update tsinger set display = ? where sid = ?"; Object[] params = {"实验使用",1019}; int rows = jdbcTemplate.update(sql, params); System.out.println(rows); } @Test public void testDelete() throws Exception { //删除 sid为1019的记录 String sql = "delete from tsinger where sid = ?"; Object[] params = {1019}; int rows = jdbcTemplate.update(sql, params); System.out.println(rows); } } ``` ### C: 查询操作: 结果集封装为对象 ```java @Test public void testQueryForBeanList() throws Exception { //查询所有的歌手信息 封装为List String sql = "select * from tsinger"; List singers = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Singer.class)); for (Singer singer : singers) { System.out.println(singer); } } ``` ### D: 查询操作: 结果集封装为map集合 ```java @Test public void testQueryForMap() throws Exception { //查询sid是1005的记录 String sql = "select * from tsinger where sid = ?"; Object[] params = {1005}; Map map = jdbcTemplate.queryForMap(sql, params); System.out.println(map); } ``` ### E: 查询操作: 聚合函数查询 ```java @Test public void testQueryForCount() throws Exception { //查询女歌手人数 String sql = "select count(*) cs from tsinger where sex = ?"; Object[] params = {"女"}; Integer cs = jdbcTemplate.queryForObject(sql, Integer.class, params); System.out.println(cs.intValue()); } ```