likes
comments
collection
share

Java MySQL

作者站长头像
站长
· 阅读数 33

认识MySQL

  1. 前端：
  • 作用：前端主要负责展示页面数据给用户。这意味着前端开发者需要设计用户界面（UI）和用户体验（UX），确保用户可以直观地与软件交互。
  • 技术：前端开发者可能会使用HTML、CSS和JavaScript等技术来构建和美化网页，以及与后端进行通信（例如，通过API请求数据）。
  1. 后端：
  • 作用：后端是连接数据库、控制视图跳转和给前端传递数据的部分。这意味着后端开发者需要处理业务逻辑、数据验证、安全性以及与数据库的交互。
  • 技术：后端开发者可能会使用Java、Python、Ruby等编程语言，以及框架（如Spring、Django）和数据库连接技术（如JDBC）来构建后端服务。
  • JDBC：Java Database Connectivity（JDBC）是一个Java API，用于执行SQL语句和访问数据库。它允许Java程序与各种关系数据库进行交互。
  1. 数据库：
  • 作用：数据库用于存储和管理数据。在这个上下文中，MySQL是一个流行的关系型数据库管理系统（RDBMS），用于存储和检索应用程序需要的数据。
  • 技术：数据库管理员（DBA）或数据库开发者可能会使用SQL（结构化查询语言）来查询、插入、更新和删除数据。
  1. 程序员的分类：
  • 只会CRUD（Create, Read, Update, Delete，即创建、读取、更新、删除），可以混饭吃 = 初级
  • 操作系统，数据结构和算法 = 中级
  • 离散数学，数字电路，编译原理+实战经验 = 高级

为什么学习数据库

  1. 岗位技能需求：随着信息技术的发展，数据库作为存储、管理和查询数据的核心组件，几乎在所有的软件开发项目中都有应用。无论是前端开发者、后端开发者、数据分析师、系统架构师还是数据库管理员（DBA），掌握数据库的知识和技能都是他们日常工作的一部分。
  2. 数据处理的重要性：在当今数据驱动的社会，数据被认为是新的石油，是企业和组织的重要资产。数据不仅用于决策支持、业务分析，还用于机器学习、人工智能等多个领域。因此，了解如何存储、查询、分析和保护这些数据变得至关重要。
  3. 软件体系的核心：数据库几乎可以说是软件体系中最核心的一个组件。在大多数应用中，数据库都是后端服务的基础，它负责存储和管理业务逻辑所需的数据。没有数据库，许多现代应用将无法正常运作，因为它们需要持久化存储来确保数据的可用性和一致性。

什么是数据库( DataBase , 简称 DB )

定义：数据库是一个关键组件，用于存储、管理、查询和维护应用程序所需的数据。简而言之，数据库是一个按照数据结构来组织、存储和管理数据的仓库。它是长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。它确保了数据的完整性、安全性和一致性，并支持对数据进行增加、删除、修改和查询等操作。

作用：数据存储、管理(增删改查)、保护、共享、备份与恢复；支持业务逻辑；性能优化

两种数据库类型

关系型数据库（SQL）

数据结构特点

  • 表格化组织：关系型数据库的主要数据组织形式是表格（或称为关系）。每个表格包含行（记录）和列（字段），其中行代表具体的数据记录，而列则代表记录中的各个属性或数据项。
  • 明确的关系：关系型数据库中的数据表之间通过主键和外键等约束来建立明确的关系。主键是表格中唯一标识数据记录的属性，而外键则用于连接不同表格中的数据，确保数据的完整性和一致性。
  • 数据完整性：关系型数据库支持事务处理，确保数据的完整性。ACID是数据库事务的四个关键属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这些属性保证了在多个操作同时发生时，数据库能够保持数据的完整性和一致性。
  • 标准查询语言：关系型数据库使用结构化查询语言（SQL）来管理和查询数据。这使得用户可以通过SQL语句方便地进行数据的检索、更新、删除等操作，提高数据处理的效率和灵活性。

常见关系型数据库系统：

  • MySQL、Oracle、SQL Server、SQLite、DB2等。

非关系型数据库（NoSQL）

数据结构特点

  • 灵活性：非关系型数据库的数据结构并不局限于固定的表结构，每个记录（或元组）都可以具有不同的字段，并且这些字段可以按需动态修改。这种灵活性使得开发者能够在不改变整个数据库结构的前提下，轻松地为记录添加新的字段或属性，从而轻松应对快速变化的数据需求，存储多样化的数据，例如用户评论、日志文件、社交媒体内容等。
  • 非表格形式：非关系型数据库可以使用键值对（key-value）、文档（document）或图形（graph）等形式来存储数据。这些数据结构更适合处理非结构化或半结构化的数据，能够处理更为复杂、多样化的数据类型。
  • 水平可扩展性：由于非关系型数据库通常具有水平可扩展性，因此可以轻松地通过添加更多的服务器来增加数据库的容量和性能。这种特性使得非关系型数据库非常适合处理大规模、高并发的数据读写请求，适用于高性能场景。

常见非关系型数据库系统：

  • Redis、MongoDB、Cassandra、CouchDB、HBase、Neo4j等。

什么是DBMS

数据库管理系统 ( DataBase Management System )： 它是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库。DBMS提供数据定义功能、数据存取功能、数据库运行管理功能、数据库的建立和维护功能以及数据库的传输等功能。按功能划分，数据库管理系统大致可分为6个部分，包括 模式翻译、应用程序的编译、交互式查询、数据的组织与存取、事务运行管理、数据库的维护。

Java MySQL Java MySQL

MySQL简介

概念：是现在流行的开源的，免费的关系型数据库

特点 :

  • 免费，开源数据库
  • 小巧，功能齐全
  • 使用便捷
  • 可运行于Windows或Linux操作系统
  • 可适用于中小型甚至大型网站应用

命令行操作

DBMS中SQL语句分类

名称解释命令
DDL（数据定义语言)定义和管理数据对象，如数据库，数据表等CREATE、DROP、ALTER
DML（数据操作语言）用于操作数据库对象中所包含的数据INSERT、UPDATE、DELETE
DQL（数据查询语言）用于查询数据库数据SELECT
DCL（数据控制语言)用于管理数据库的语言，包括管理权限及数据更改GRANT、commit、rollback

在SQL中，命令关键字不区分大小写

反引号``：在MySQL中，如果表名或字段名包含特殊字符（如空格、连字符、点、美元符号等）或它们是SQL的关键字（如 SELECT, FROM, WHERE 等），那么通常需要用反引号（``）来引用它们。

作用：告诉数据库管理系统（DBMS）该标识符（表名或字段名）应该被当作一个标识符而不是一个关键字或其他特殊字符来处理。

数据库管理中逐步细化的过程 操作数据库 > 操作数据库中的表 > 操作数据库表中的数据

操作数据库

  1. 连接到MySQL数据库（在命令行界面中执行）
  • 使用命令行工具mysql连接到MySQL服务器。格式如下： 
    mysql -h [主机地址] -u [用户名] -p
[主机地址]：可以输入服务器的IP地址、域名或者主机名
[用户名]：指定连接到MySQL服务器时使用的用户名
-p：提示用户输入与指定用户名关联的密码
  • 如果是连接到本地MySQL服务器，可以使用： 
    mysql -u [用户名] -p
  • 输入密码后，如果成功，将看到MySQL的提示符mysql>

  1. 创建数据库（以下在数据库管理系统中执行）

    CREATE DATABASE IF NOT EXISTS [数据库名];

    所有的创建和删除操作尽量加上判断，以免报错

  2. 查看创建数据库的完整语句

    SHOW CREATE DATABASE [数据库名];

    执行这个命令后，MySQL将返回一个结果集，其中包含两行数据。第一行是数据库名，第二行是创建该数据库的SQL语句。这个SQL语句可以用来在其他MySQL服务器上重新创建具有相同配置的相同数据库。需要具有足够权限。

  3. 删除数据库

  • 在删除数据库之前，要确保已经备份了所有重要的数据，因为删除操作是不可逆的。

    DROP DATABASE [数据库名];
  1. 刷新数据库

  • 在MySQL中，FLUSH命令可以用来刷新或重置某些服务器资源。例如，要刷新权限，可以使用：

    FLUSH PRIVILEGES;

    这将重新加载授权表，使得任何最近的权限更改生效。

  1. 查看所有数据库
  • 在MySQL提示符下，输入以下命令查看所有数据库： 
    SHOW DATABASES;
  1. 选择数据库
  • 使用USE命令选择一个数据库进行操作： 
    USE [数据库名];

操作数据库中的表

  1. 查看该表的信息（结构与列信息）
  • 要查看表的信息，首先需要选择数据库，然后使用DESCRIBE或DESC命令查看表的结构： 
    USE [数据库名];
DESCRIBE [表名];   //或者 DESC [表名];
  • 或者，也可以使用SHOW COLUMNS命令来查看表的列信息： 
    SHOW COLUMNS FROM [表名];

  1. 查看数据表的定义语句

    SHOW CREATE TABLE [表名]

    执行这个命令后，MySQL将返回一个结果集，其中包含两个列：Table 和 Create Table 。Table 列显示表名，而 Create Table 列则包含用于创建该表的完整SQL语句。需要具有足够权限。

  2. 查看数据库中的表

  • 使用SHOW TABLES;命令查看当前数据库中的所有表。
  1. 其他
  • 创建表：CREATE TABLE 表名 (列定义);
  • 修改表：ALTER TABLE 表名 ADD/DROP/MODIFY 列定义;
  • 删除表：DROP TABLE 表名;

操作数据库表中的数据

  1. 修改密码

    要修改MySQL用户的密码，可以使用SET PASSWORD命令或者ALTER USER命令。以下是两种方法的示例：

  • 使用SET PASSWORD命令（适用MySQL 5.7.6前的老旧版本）：

    SET PASSWORD FOR [用户名]@[主机名] = PASSWORD([新密码]);

  • 使用ALTER USER命令（推荐，因为更灵活且安全）：

    ALTER USER [用户名]@[主机名] IDENTIFIED BY [新密码];

    注意，在修改密码时，需要具有足够的权限来执行这些命令。通常，这意味着开发者需要以 root 用户或其他具有管理员权限的用户身份登录到MySQL服务器。

  1. 执行SQL语句
  • 在MySQL提示符下，可以直接输入SQL语句并执行，例如： 
    SELECT * FROM [表名];
  1. 其他
  • 插入数据：INSERT INTO 表名 (列名) VALUES (值);
  • 查询数据：SELECT 列名 FROM 表名 WHERE 条件;
  • 更新数据：UPDATE 表名 SET 列名 = 新值 WHERE 条件;
  • 删除数据：DELETE FROM 表名 WHERE 条件;

其他操作

  1. 执行SQL脚本文件
  • 使用SOURCE命令执行SQL脚本文件： 
    SOURCE [文件路径];
  1. 退出MySQL
  • 使用EXIT命令退出MySQL命令行工具。
  1. sql的注释 
    --    当行注释 
/* 
      多行注释 
*/

数据库

数据库的列（数据）类型

数值

类型说明取值范围字节
tinyint十分小的数据-27 到 27 - 1 (有符号)0 到 28 - 1 (无符号)1个字节
smallint较小的数据-215 到 215 - 1 (有符号)0 到 216 - 1 (无符号)2个字节
mediumint中等大小数据-223 到 223 - 1 (有符号)0 到 224 - 1 (无符号)3个字节
int标准的整数-231 到 231 - 1 (有符号)0 到 232 - 1 (无符号)4个字节
bigint较大的数据-263 到 263 - 1 (有符号)0 到 264 - 1 (无符号)8个字节
float单精度浮点数约 (3.4e-38,1.17e+38)0 , (-3.4e-38,-1.17e+38) (IEEE 754 标准)4个字节
double双精度浮点数约 (2.23e-308,1.80e+308)0 , (-2.23e-308,-1.80e+308) (IEEE 754 标准)8个字节
decimal字符串形式的浮点数依赖于定义的精度和刻度可变

说明： 这里的 E 或 e 是科学计数法中的一个标准符号，用于表示乘以 10 的某个幂次。而并不是指自然常数 e（约等于 2.71828）。在科学计数法中，E-38 或 e-38 表示 10 的 -38 次方，即 10^-38。 decimal： [列名] DECIMAL(P,D) 意味着它可以存储最多 P 位数，其中 D 位是小数部分。存储数值区间是[-最大数字,+最大数字]。 字符串形式的浮点数不具有舍入误差，在数据库中通常用于存储精确的数值，尤其是当涉及到货币或其他需要精确计算的场合时。

字符串

类型说明字节
char字符串固定大小的0-255
varchar可变字符串0-65535
tinytext微型文本2^8 -1
text文本串2^16-1 保存大文本

日期时间

类型说明字节
date[年]-[月]-[日]      [ ]表示数字日期格式
time[时]:[分]:[秒]时间格式
datetime[年]-[月]-[日] [时]:[分]:[秒]最常用
timestamp时间戳1970.1.1 到现在的毫秒数 用于记录精确时间
year[年]

NULL 没有值，未知 注意：使用null进行运算，结果为null

数据库的字段属性

UNSIGNED

  • 声明该列只能存储非负整数

ZEROFILL

  • 如果字段的值不足以填满指定的宽度，那么会在值的左侧填充 0，以确保整数字段总是具有指定的显示宽度
  • 要求列的值必须是非负的
  • 例如 INT(5) ZEROFILL。这里的 5 是显示宽度，意味着当值显示时，它应该总是有5位数

AUTO_INCREMENT

  • 自增，自动在上一条记录的基础上+1（默认）

  • 自增属性通常用于为主键生成唯一的ID值，新记录插入时该值自动递增，确保每条记录有唯一标识符

  • 被设置为 Auto_Increment 的列中的记录必须是整数类型

  • 如果列同时设置了 ZEROFILL 和 AUTO_INCREMENT 属性，MySQL 会确保新生成的自动增长值在显示时满足 ZEROFILL 的要求

  • 可以设置自增的起始值和步长（在MySQL中，可以在创建表时设置起始值，而步长通常是在会话级别或全局级别设置的，而非针对单个表）

    语法格式

    CREATE TABLE table_name (  
    column_name INT NOT NULL AUTO_INCREMENT,  
    -- 其他列定义  
    PRIMARY KEY (column_name)  
) AUTO_INCREMENT = START_VALUE;
--table_name 是要创建的表的名称
--column_name 是要设置为自增的列的名称
--START_VALUE 是起始值

NOT NULL 非空

  • 如果设置为NOT NULL，则该列必须有值，否则就会报错；

DEFAULT 默认

  • 当创建表时，可以在列定义中使用 DEFAULT 关键字来指定默认值
  • 如果列的数据类型允许NULL值（即列的定义中包含了NULL），并且没有为该列指定默认值，那么该列的默认值就是NULL
  • 如果列的数据类型不允许NULL值（即列的定义中没有包含NULL），并且没有为该列指定默认值，那么在尝试插入没有该列值的新记录时，MySQL将抛出一个错误

拓展

每一个表，都必须存在以下5个字段！未来做项目用的，表示一个记录存在的意义

  • id 主键
  • version 乐观锁
  • is_delect 伪删除
  • gmt_create 创建时间
  • gmt_update 修改时间

主键（Primary Key）是数据库表中用于唯一标识每条记录的一列或一组列。主键的作用是确保表中的每条记录都具有唯一的标识符，以便能够轻松地进行检索、更新和删除操作。

主键具备以下特点：

  • 唯一性：主键列的值必须是唯一的，任何两条记录不能拥有相同的主键值。
  • 非空性：主键列的值不能为空，即不允许出现 NULL 值。
  • 一般选择数字类型或者字符串类型来作为主键，通常使用整数类型或者GUID（全局唯一标识符）。
  • 一个表只能有一个主键，但主键可以由多个字段组合而成，这种主键称为复合主键。

通过定义主键，可以确保表中的数据记录在逻辑上都有一个独一无二的标识符，从而使得数据库系统能够高效地管理和操作数据。

数据库表

两个概念

  • 字段（Field）是数据库表格中的列，它代表了具有相同属性的数据集合。
  • 记录（Record）则是数据库表格中的行，它由若干个字段值构成。

创建数据库表

创建数据库表的语法格式

CREATE TABLE [IF NOT EXISTS] `表名` (
  `字段1` [列类型1] [属性1] [索引1] [注释1],
  `字段2` [列类型2] [属性2] [索引2] [注释2],
  `字段3` [列类型3] [属性3] [索引3] [注释3],
  ...
  `字段N` 列类型N [属性N] [索引N] [注释N],
  PRIMARY KEY (`主键字段`)
) [表类型] [表字符集设置] [注释];

说明：

  • [IF NOT EXISTS] 是一个可选的关键词，表示 如果表已经存在，CREATE TABLE语句将不会执行任何操作，也不会显示错误
  • 表名 是表的名称，需要用反引号（`）括起来，当表名包含特殊字符或与MySQL保留字冲突时必须使用
  • 字段N 等是表中的列名，也需要用反引号括起来
  • [列类型N] 等是对应列的数据类型，如 INT、VARCHAR(255)、TEXT 等
  • [属性] 是可选的列属性，如 NOT NULL、DEFAULT 默认值、AUTO_INCREMENT 等
  • [索引] 是可选的索引定义，如 UNIQUE、FULLTEXT、FOREIGN KEY 等
  • [注释] 是可选的注释，注释内容用--引出
  • PRIMARY KEY (主键字段) 定义了表的主键，主键字段名也需要用反引号括起来
  • [表类型] 是可选的，用于指定表的存储类型，如 ENGINE=InnoDB
  • [表字符集设置] 是可选的，用于指定表的字符集和校对规则，如 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  • [注释] 是可选的，用于给整个表添加注释
  • （）内除了最后一个语句，其他
  • 所有语句后面加 ,(英文的)

以下是一个具体的例子：

CREATE TABLE IF NOT EXISTS `users` (     -- 创建一个名为 users的表，如果该表不存在  
  `id` INT NOT NULL AUTO_INCREMENT,      -- 定义一个名为 id的整数字段，不允许为空，并设置为自动增长  
  `username` VARCHAR(50) NOT NULL,       -- 定义一个名为 username的变长字符串字段，最大长度为50，不允许为空  
  `email` VARCHAR(100) NOT NULL UNIQUE,  -- 定义一个名为 email的变长字符串字段，最大长度为100，不允许为空，并且必须是唯一的  
  `password` VARCHAR(255) NOT NULL,      -- 定义一个名为 password的变长字符串字段，最大长度为255，不允许为空  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 定义一个名为 created_at的时间戳字段，默认值为当前时间戳  
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 定义一个名为 updated_at的时间戳字段，默认值为当前时间戳，当记录更新时自动更新为当前时间戳  
  PRIMARY KEY (`id`)                     -- 将 id字段设置为主键  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';  -- 设置表的存储引擎为InnoDB，字符集为utf8mb4，并添加注释'用户表'

数据库表的类型

数据库引擎：

MYISAMINNODB
事物支持不支持支持
数据行锁定不支持 （它只表锁）支持
外键约束不支持支持
全文索引支持支持（MySQL 5.6版本之后）
表空间大小较小较大，约为MYISAM2倍

适用场景：

  • MyISAM : 节约空间及相应速度；主要是读操作，并且不需要事务支持、行级锁定和外键约束
  • 适用 InnoDB : 安全性高，事务处理及多用户操作数据表，多表多用户操作；需要事务支持、行级锁定和外键约束

然而，随着MySQL的发展，InnoDB已经变得越来越流行，因为它提供了更好的数据完整性和并发性能。

物理存储层面：MySQL 数据库管理系统以文件的形式保存数据库和表的数据。这些文件通常位于 MySQL 服务器的 data 目录下，data 目录下每个子文件夹的名称对应一个数据库名，文件夹的名字就是数据库的名字。在这个文件夹内，每个表都会有与之对应的文件。

MySQL 支持多种存储引擎，每种存储引擎在物理存储层面上的实现方式会有所不同。 InnoDB 和 MyISAM 是 MySQL 中最常见的两种存储引擎。

MyISAM 存储引擎 对于 MyISAM 存储引擎来说，每个表在物理上会有三个文件：

  1. *.frm：这是表的结构定义文件，它存储了表的元数据，如列名、数据类型等。
  2. *.MYD：这是数据文件，存储了表的实际数据。
  3. *.MYI：这是索引文件，存储了表的索引信息，用于加快查询速度。

InnoDB 存储引擎 对于 InnoDB 存储引擎来说，通常只有一个 *.frm 文件用于存储表的结构定义。

InnoDB 的数据和索引信息则存储在表空间中。表空间可以是单个文件，也可以是多个文件组成的文件组。 在 MySQL 的默认配置中，InnoDB 的表空间存储在名为 ibdata1 的文件中。这个文件还包含了其他的信息，如 undo 日志、系统更改日志等。

从 MySQL 5.6 开始，InnoDB 还支持将表空间和索引信息存储在单独的文件中，这被称为“文件表空间”（file-per-table）。在这种模式下，每个 InnoDB 表都会有一个与之对应的 .ibd 文件位于与 *.frm 文件相同的目录下，用于存储该表的数据和索引信息。

总结一下，对于InnoDB表： 无论是否启用“文件表空间”特性（file-per-table），ibdata1文件都是InnoDB存储引擎的共享表空间文件，都会包含InnoDB的内部数据和元数据，如undo日志、系统更改日志等。而*.frm文件始终用于存储表结构定义，并位于对应数据库的文件夹内

  • 如果启用了文件表空间特性，ibdata1文件将只包含InnoDB的内部数据和元数据。而每个InnoDB表都将有一个独立的.ibd文件来存储该表的数据和索引信息，.ibd文件与*.frm文件位于同一级目录下
  • 如果未启用文件表空间特性，ibdata1文件除了包含内部数据和元数据外，还会包含所有InnoDB表的数据和索引信息。而每个InnoDB表仍然会有一个*.frm文件用于存储表结构定义，但这个文件位于对应数据库的文件夹内，而不是与ibdata1文件在同一级目录下

设置数据表字符集

MySQL 数据库管理系统支持多种字符集，这些字符集决定了如何存储和比较字符串数据。字符集的选择对于能够正确存储和检索各种语言（特别是非英语语言，如中文）的数据至关重要。

MySQL 的默认字符集是 Latin1，这是一个单字节字符集，主要支持西欧语言，并不完全支持中文字符。因此，在创建数据库、数据表或数据列时，通常需要显式地指定字符集以支持中文。

以下是如何设置 MySQL 数据库、数据表和数据列的字符集：

  1. 数据库级别的字符集设置： 在创建数据库时，可以使用 CHARACTER SET 关键字来指定字符集。例如，要创建一个使用 UTF-8 字符集的数据库，可以使用以下 SQL 语句：
CREATE DATABASE 数据库名 CHARACTER SET utf8;
--CHARACTER SET utf8 与 CHARSET=utf8 二者等效
--如果需要支持需要存储所有 Unicode 字符，应该使用 utf8mb4 而不是 utf8
  1. 数据表级别的字符集设置： 在创建数据表时，也可以指定字符集。例如：
CREATE TABLE 表名 (
  列名 数据类型,
  ...
) CHARACTER SET utf8;
  1. 数据列级别的字符集设置： 此外，还可以为单个数据列指定字符集。这通常用于当表中的不同列需要使用不同的字符集时。例如：
CREATE TABLE 表名 (
  列名1 数据类型 CHARACTER SET utf8,
  列名2 数据类型 CHARACTER SET latin1,
  ...
);
  1. 配置文件设置： 除了通过 SQL 语句设置字符集外，还可以在 MySQL 的配置文件 my.ini（或 my.cnf，取决于操作系统和安装方式）中设置默认字符集。例如，可以设置 character-set-server 参数来指定服务器的默认字符集：
[mysqld]
character-set-server=utf8

具体操作（Windows系统）：

  1. 终止MySQL服务：通过服务管理器或命令行来停止 MySQL 服务。例如，使用命令行执行以下命令：net stop mysql
  2. 在MySQL的安装目录下找到my.ini，如果没有找到则把my-medium.ini 或 my-large.ini 等模板文件复制一份并重命名为my.ini
  3. 打开 my.ini 文件，然后在 [client] 和 [mysqld] 段下添加以下行来设置默认字符集，保存并关闭
[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
  1. 启动MySQL服务 保存并关闭 my.ini 文件后，通过服务管理器或命令行来启动 MySQL 服务。例如，使用命令行执行以下命令：net start mysql

这样，如果没有在创建数据库、数据表或数据列时显式地指定字符集，MySQL 将使用配置文件中指定的默认字符集。

总之，为了确保能够正确地存储和检索各种语言的数据，建议在创建数据库、数据表或数据列时显式地指定适当的字符集。如果未指定，MySQL 将使用配置文件或系统默认值，这可能会导致数据丢失或错误。

修改和删除数据表字段

SQL中 ALTER TABLE 命令可以用于在已经存在的表上进行各种修改，如添加、修改、重命名或删除字段，以及重命名表本身

  1. 表 新增字段
--在表中添加一个新的字段
ALTER TABLE 表名 ADD 新字段 列属性
ALTER TABLE test ADD age INT(10)     --在test表中添加了一个名为age的新字段，其数据类型为INT，并且最大长度为10
  1. 表 修改字段类型的数据类型或长度
ALTER TABLE 表名 MODIFY 字段 列属性
ALTER TABLE test MODIFY age INT(1)    --将test表中的age字段的数据类型修改为INT(1)
  1. 表 修改字段名（可以同时修改其数据类型或长度）
ALTER TABLE 表名 CHANGE 老字段名 新字段名 列属性
ALTER TABLE test CHANGE age age1 INT(10)    --将test表中的age字段重命名为age1，并将其数据类型设置为INT(10)
  1. 表 删除字段
ALTER TABLE 表名 DROP 字段名
ALTER TABLE test DROP age1    --从test表中删除了age1字段
  1. 修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1    --将teacher表重命名为teacher1
  1. 删除表
DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS student    --检查数据库中是否存在名为student的表。如果存在，则删除student表

MySQL数据管理

外键（了解）

外键（Foreign Key）是关系型数据库中用于建立两个表之间联系的一种数据库约束。它用于确保引用完整性，即两个表之间的数据关联性和一致性。

概念

  • 定义：在一个表中，如果有某个列（或列的组合）的值必须引用另一个表的主键值（或者是NULL，如果外键列允许NULL值），那么这个列（或列的组合）就被称为外键。
  • 主表和从表：拥有主键的表通常被称为主表（Parent Table），而拥有外键的表被称为从表（子表）（Child Table 或 Foreign Table），从表中的外键列引用主表的主键列。

作用

  1. 建立表之间的关系：外键用于建立两个表之间的关联，这样可以通过一个表的数据来查询或操作另一个表的数据。
  2. 约束：外键是一种数据库约束，用于限制对数据的操作，确保数据的准确性和可靠性（确保引用的数据在主表中存在；防止对表进行某些可能会破坏引用完整性的操作）。
  • 用于约束作用时，意味着在 表中，外键列中的每一个值都必须是 指定列中的一个有效值 此时，外键列可以有 NULL 值。当外键列允许 NULL 值 时，外键约束不会检查该值是否对应于 表中指定列的任何值，因为 NULL 表示没有引用任何值。

理解

可以想象有两个表：一个是“学生”表，另一个是“课程”表。众多学生选修多门课程是一种多对多的关系，为了表示这种关系，可以创建一个“选课”表，其中包含学生ID和课程ID两个外键列。这两个外键列分别引用“学生”表的主键和“课程”表的主键。

通过这种方式，外键不仅保证了数据的完整性（例如，不会有一个不存在的学生ID或课程ID出现在“选课”表中），而且还建立了表之间的关联，使得我们可以轻松地查询某个学生选修了哪些课程，或者哪些学生选修了某一门课程。

使用外键示例

在创建表的时候，使用外键

-- 如果不存在名为`grade`的表，则创建该表
CREATE TABLE IF NOT EXISTS `grade` (
    `gradeid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '年级id',  -- `gradeid`列，类型为INT(10) UNSIGNED，不允许为空，自动增长，并带有注释'年级id'
    `gradename` VARCHAR(100) NOT NULL COMMENT '年级名称',  -- `gradename`列，类型为VARCHAR(100)，不允许为空，并带有注释'年级名称'
    PRIMARY KEY(`gradeid`)                                 -- 设置`gradeid`列为主键
) ENGINE=INNODB DEFAULT CHARSET=utf8;                      -- 设置表的存储引擎为InnoDB，字符集为utf8

-- 如果不存在名为`student`的表，则创建该表
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学号',  -- `id`列，类型为INT(10) UNSIGNED，不允许为空，自动增长，并带有注释'学号'
    `name` VARCHAR(200) NOT NULL COMMENT '姓名',              -- `name`列，类型为VARCHAR(200)，不允许为空，并带有注释'姓名'
    `sex` ENUM('男', '女') NOT NULL COMMENT '性别',           -- `sex`列，使用ENUM类型限制值为'男'或'女'，不允许为空，并带有注释'性别'
    `gradeid` INT(10) UNSIGNED NOT NULL COMMENT '学生的年级', -- `gradeid`列，类型为INT(10) UNSIGNED，不允许为空，并带有注释'学生的年级'
    `pwd_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希',      -- `pwd_hash`列，类型为VARCHAR(255)，不允许为空，用于存储密码哈希值，并带有注释'密码哈希'
    `birthday` DATETIME DEFAULT NULL COMMENT '生日',          -- `birthday`列，类型为DATETIME，允许为空，并带有注释'生日'
    `address` VARCHAR(200) DEFAULT NULL COMMENT '家庭住址',   -- `address`列，类型为VARCHAR(200)，允许为空，并带有注释'家庭住址'
    `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',         -- `email`列，类型为VARCHAR(100)，允许为空，并带有注释'邮箱'
    PRIMARY KEY(`id`),                                       -- 设置`id`列为主键
    KEY `FK_gradeid` (`gradeid`),                            -- 为`gradeid`列创建一个名为`FK_gradeid`的索引
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)  -- 添加外键约束，`FK_gradeid`引用`grade`表的`gradeid`列
) ENGINE=INNODB DEFAULT CHARSET=utf8;                        -- 设置表的存储引擎为InnoDB，字符集为utf8

-- 创建年级表grade，包含gradeid（年级ID）和gradename（年级名称）两个字段  
CREATE TABLE `grade`(  
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '',   -- gradeid字段，整数类型，不允许为空，自增，无注释内容  
   `gradename` VARCHAR(50) NOT NULL COMMENT '',            -- gradename字段，字符串类型，最大长度50，不允许为空，无注释内容  
   PRIMARY KEY (`gradeid`)                                 -- 设置gradeid字段为主键  
)ENGINE=INNODB DEFAULT CHARSET=utf8;                       -- 使用InnoDB引擎，字符集为utf8  
  
-- 创建学生表student，包含多个字段，并在最后添加了一个外键约束  
CREATE TABLE IF NOT EXISTS `student`(  
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',       -- id字段，整数类型，不允许为空，自增，注释为“学号”  
   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',-- name字段，字符串类型，最大长度30，不允许为空，默认值为“匿名”，注释为“姓名”  
   `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',-- pwd字段，字符串类型，最大长度20，不允许为空，默认值为“123456”，注释为“密码”  
   `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',    -- sex字段，字符串类型，最大长度2，不允许为空，默认值为“女”，注释为“性别”  
   `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',      -- birthday字段，日期时间类型，允许为空，注释为“出生日期”  
   `gradeid` INT(10) NOT NULL COMMENT '学生的年级',          -- gradeid字段，整数类型，不允许为空，注释为“学生的年级”  
   `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',   -- address字段，字符串类型，最大长度100，允许为空，注释为“家庭住址”  
   `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',          -- email字段，字符串类型，最大长度50，允许为空，注释为“邮箱”  
   PRIMARY KEY(`id`)                                         -- 设置id字段为主键  
)ENGINE=INNODB DEFAULT CHARSET=utf8;                         -- 使用InnoDB引擎，字符集为utf8  
  
-- 向student表添加外键约束，将gradeid字段作为外键，引用grade表的gradeid字段  
ALTER TABLE `student`  ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);  
-- ALTER TABLE 从表名称 ADD CONSTRAINT 外键约束名 FOREIGN KEY (从表中作为外键的列名) REFERENCES 主表名 (主表的主键列名);

外键约束名：用于标识特定外键约束的名称，在创建或修改表结构时指定，这个名称通常遵循某种命名约定，以便能够清晰地表示该约束的用途和它所涉及的表和列。一般命名为 fk_主表名_从表名

删除有外键关系的表时，必须先删除从表，再删除主表

一些语法

*.* 表示所有库的所有表 库名.表名 表示某库下面的某表 表名.列名 表示某表下面的某列 但 表名.记录名 是错误的，记录通过WHERE子句中的条件定位

DML数据操作

管理数据库数据方法：

  • 通过SQLyog等管理工具管理数据库数据
  • 通过DML语句管理数据库数据

定义：SQL（结构化查询语言）的一个子集，用于管理数据库中的数据。DML语句允许用户查询、插入、更新和删除数据库中的数据。

  • INSERT：用于向数据库表中插入新的数据行
  • UPDATE：用于修改数据库表中的数据
  • DELETE：用于从数据库表中删除数据

添加数据 insert

INSERT INTO 语句用于向数据库表中插入新的记录。这些记录由值组成，这些值对应于表中的列（字段）。

插入单个值

只需要为表中的特定列插入值时，可以指定列名。例如：

INSERT INTO 表名(列名) VALUES(插入值)   --只指定了一个列名，因此只需要提供一个值

插入多个值

INSERT INTO 表名 (列名1, 列名2, 列名3,..., 列名n)
VALUES  
  (值11, 值12, 值13, ... , 值1n),  -- 插入行1的值
  (值21, 值22, 值23, ... , 值2n),  -- 插入行2的值
  (值31, 值32, 值33, ... , 值3n),  -- 插入行3的值
  ...
  (值n1, 值n2, 值n3, ... , 值nn);  -- 插入行n的值
  --值qp，q对应行，p对应列

省略列名

如果表中的所有列都需要值，并且提供的值的顺序与表中的列的顺序一致，则可以省略列名，并且为所有非自增列提供值。但是，如果表有自增的主键列（如ID），通常应该省略该列，以便数据库可以自动生成值。

INSERT INTO 表名 VALUES (值1, 值2, ... , 值n);
  • 确保提供的值的数量、类型和顺序与表中的列相匹配（除非您明确指定了列名）
  • 注意使用正确的引号来包围字符串值（通常是单引号）

修改数据 update

UPDATE语句用于修改现有记录中的值，可以将新值设置为常量、变量、表达式（SET column_name = column_name * 2）或嵌套的SELECT查询的结果。

修改指定记录(id)的指定字段

UPDATE 表名 SET 列名=新值 WHERE id = n;
UPDATE `student` SET name=sk WHERE id = 1;  --修改`student`表中id为1的记录的name字段，将其值设置为sk

修改所有记录的指定字段

UPDATE 表名 SET 列名=新值
UPDATE `student` SET `name`='长江7号';  --修改`student`表中所有记录的name字段，将其值设置为`长江7号`

修改满足多个条件的记录的指定字段

UPDATE 表名 SET 列名1=新值1 WHERE 列名1=现值1 AND 列名2=现值2 AND 列名3=现值3 AND ... AND 列名n=现值n;
UPDATE `student` SET `name`='长江7号' WHERE `name`='sk' AND sex='男'  
--修改student表中 name字段的值是sk、sex字段的值是男 的记录的name字段，将其值设置为`长江7号`

修改多个字段，用逗号隔开 列名1=新值1,列名2=新值2,列名3=新值3, ... ,列名n=新值n

字段值设置为当前的时间 UPDATE 表名 SET 列名=CURRENT_TIME (WHERE...)

将新值设置为嵌套的 SELECT 查询的结果

UPDATE 要更新的表 SET 要更新的字段 = (SELECT 从中获取值的字段 FROM 从中检索数据的表 WHERE ... ) WHERE ... ;

删除数据

DELETE命令

作用：DELETE命令用于删除表中的一行或多行数据。可以使用WHERE子句来指定要删除的记录，如果不使用WHERE子句，那么将删除表中的所有记录。

语法格式：

DELETE FROM 表名 (WHERE ...);

注意：DELETE命令是一个事务性的操作，可以回滚。这意味着，如果在一个事务中执行了DELETE命令，但在事务提交之前发生了错误或决定回滚事务，那么被删除的数据将被恢复。

TRUNCATE命令

作用：TRUNCATE命令用于永久删除表中的所有数据，但不删除表本身。与DELETE命令不同，TRUNCATE命令无法回滚。

语法格式：

TRUNCATE TABLE 表名;

区别

  1. 速度与日志记录：通常，相较于DELETE命令，TRUNCATE命令更快。因为TRUNCATE命令不记录每一行的删除操作，而是直接释放表中的数据页。而DELETE命令会记录每一行的删除操作，这会增加额外的性能开销和日志文件的大小。。
  2. 约束 如果表中有外键约束
  • 当尝试使用DELETE命令时，如果该记录的外键值在另一个表中作为主键或唯一键存在（即有其他表依赖于这个外键值），数据库通常会阻止删除操作，以保持数据的一致性和完整性。
  • 当尝试使用TRUNCATE命令时，不会受到外键约束的影响。
  1. 回滚
  • TRUNCATE是一个DDL（数据定义语言）操作，通常不会被包含在事务中。这意味着，一旦 TRUNCATE 命令执行，即使在一个事务中，该操作也不能被回滚。
  • DELETE 是一个DML（数据操作语言）操作，可以被包含在事务中，在事务提交之前发生了错误或决定回滚事务，那么被删除的数据将被恢复。
  1. 自增与计数器
  • 当使用DELETE命令删除表中的记录时，自增计数器并不会被重置。这意味着，即使删除了所有的记录，下一个插入的记录仍然会从之前的最大自增值之后开始递增。例如，如果表中最后一个记录的自增值是100，然后删除了所有记录，下一个插入的记录的自增值将是101。
  • 当使用TRUNCATE命令删除表中的所有记录时，该表的自增列（如果存在的话）的计数器会被重置。这意味着，如果之后向表中插入新的记录，其自增值将从初始值（通常是1）开始。
  1. 触发器：DELETE命令会触发与表相关的DELETE触发器，而TRUNCATE命令不会触发任何触发器。

数据库管理系统（DBMS）中不同存储引擎如何处理自增属性和它们在数据库重启后的行为

InnoDB：将自增计数器存储在内存中（断电即失），数据库服务器重启后重新计算。 MyISAM：将自增计数器存储在磁盘上的文件中（不会丢失），数据库服务器重启后继续使用之前的值。

DQL数据查询

DQL（Data Query Language）是SQL（Structured Query Language，结构化查询语言）的一个子集，专门用于查询数据库 中的数据。

SELECT 语句

  • DQL的主要组成部分，它允许用户从数据库的一个或多个表中检索数据。
  • 它是数据库语言中最核心、最重要、使用频率最高的语句
  • 返回一个由行和列组成的结果集，即数据表
-- SELECT 语句开始  
SELECT     
    --指定是否返回所有行或仅返回唯一不同的行  
    --ALL 默认值，会返回所有满足 WHERE 子句条件的行，包括重复行。这意味着如果数据库表中有重复的记录，查询结果也会包含这些重复的记录。
    --DISTINCT 会返回唯一不同的行，即结果集中的每一行都是唯一的，重复的行只会被返回一次。
    ALL 或 DISTINCT 或 省略关键字   
      
！必需，指定要查询的列或表达式
    [要查询的列名或表达式列表]    
      
    -- 可选，用于将查询结果存储到新表中  
    INTO 新表
      
！必需，指定要查询的表或视图
    FROM  [要查询的表或视图的名称]    
      
    -- 可选，设置过滤记录的条件，只返回满足指定条件的记录
    WHERE search_condition   
      
    -- 可选，用于将 结果集 中的记录进行分组，指定的 一个 或 多个列同时 具有相同值的记录归为一组
    GROUP BY 列名
      
    -- 可选，只在GROUP BY子句之后使用，基于分组的结果指定一个条件，只有满足该条件的分组才会被包含在最终的结果集中，用于过滤分组后的结果，
    HAVING search_condition    
      
    -- 可选，用于对结果集进行排序。可以指定升序（ASC,默认）或降序（DESC）  
    --排序会基于字符的 Unicode 值、默认的字典顺序或指定的排序规则对字符串进行排序
    ORDER BY 要排序的列名 [ASC 或 DESC]    
      
    -- 可选，用于先跳过指定的行数n再返回结果集  
    OFFSET 整数n ROWS    
      
    -- 可选，用于限制返回的行数n。通常出现在OFFSET子句之后，用于指定从跳过的行数之后开始返回多少行数据
    FETCH FIRST 整数n ROWS ONLY    --FETCH FIRST 与 FETCH NEXT 等价，可以互换使用
    或
    LIMIT 整数n                    --在 MySQL 中与 FETCH {FIRST | NEXT} 等效
      
    -- 可选，指定查询结果的可访问性。这些选项不是所有数据库都支持  
    FOR 
    BROWSE
 或 READ ONLY                 --指定结果集是只读的，意味着用户不能修改结果集中的任何数据
 或 UPDATE [OF column_list]   --允许用户更新结果集中的数据。如果指定了column_list，则只有指定列可以被更新
      
    -- 可选，用于在查询结果上设置锁以支持并发控制。通常用于确保在事务处理过程中，被选中的行不会被其他事务修改或删除，直到当前事务完成。这些选项不是所有数据库都支持
    
    FOR UPDATE 
    [OF column_list] 可选，允许指定要锁定哪些列。如果指定了列列表，则只有这些列的数据会被锁定，而不是整行
    ROWLOCK    --仅锁定被查询的行
 或 PAGELOCK   --锁定包含被查询行的数据页
 或 TABLOCK    --锁定整个表
-- SELECT 语句结束

AS 子句作为别名

AS 子句用于为数据列、表或计算的结果赋予一个新的别名。这个新名称在后续的查询中可以作为引用。

主要目的：提高查询的可读性和简洁性，尤其是在处理复杂查询时。 有效区域：别名只在当前的查询语句中有效，不会影响数据库中的实际表或列名。

  1. 给数据列取一个新别名： 当查询 结果集 中的某个列名太长、复杂或不易理解时，可以使用AS子句为该列指定一个简短或更具描述性的别名。这个别名只在当前的查询 结果集 中有效，并不会改变原表的结构或列名

    SELECT 原列名 AS 新列名 FROM 表名;

  2. 给表取一个新别名： 当在查询中涉及多个表，并且需要多次引用同一个表时，可以为表指定一个别名，以简化查询语句。此外，在连接查询中，通常也需要为表指定别名，以区分来自不同表的相同列名。

    SELECT 表名1.列名3, 表名2.列名3  
FROM 原表名1 AS 新表名1, 原表名2 AS 新表名2  
WHERE ...

    从两个表（表名1和表名2）中选择特定的列（表名1.列名3 和 表名2.列名3），并将这两个表分别重命名为 新表名1 和 新表名2。然后，必须使用WHERE 子句指定哪些行满足特定的连接条件，否则查询将无法执行。 这样，在查询的其余部分就可以使用 新表名 来引用这两个表。

    连接条件：如果两个表通过某个共同的键（如用户ID或订单ID）相关联，那么 WHERE 子句可能会包含类似于新表名1.id = 新表名2.id的条件。例如：

    SELECT a.column_name, b.column_name 
FROM table1 AS a, table2 AS b 
WHERE a.id = b.id;

    从 table1 和 table2 中选取数据，其中 table1 中的行与 table2 中的行通过 id 列的值关联起来。查询结果将包含 table1 中的 column_name 列和 table2 中的 同名列，并且这些列的值是基于两个表中的 id 列值相等的条件合并的，两个id列中的数据必须是相同的数据类型

  3. 把经计算或总结的结果用另一个新名称来代替：当在查询中进行计算或汇总操作时，可以使用AS子句为计算结果指定一个别名。这对于使查询结果更易于理解或进一步处理特别有用。

    SELECT 计算表达式 AS 新列名 FROM 表名;    
--保留计算表达式外的括号有时可以提高查询的可读性，尤其是当执行更复杂的表达式或函数调用时。

    从一个表中选取数据，并对这些数据执行一个计算表达式，然后将计算结果作为新列（别名为 新列名）返回

    计算表达式可以包含各种算术运算、字符串操作、日期和时间函数以及其他SQL内置函数。算术运算可以是不同列之间的对应行列值进行算术运算，用列名作为“操作数”，这种运算相当于创建新的列，其值基于其他列的计算结果 [被存储为数值数据类型（如整数、浮点数等）的列才能进行算术运算]

或者

也可以在表名后面直接加上空格和别名，而不使用 AS 关键字： FROM 原名 新名

使用表达式查询

SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION();                  -- 查询版本号
SELECT 100*3-1 AS 计算结果;         -- 表达式

-- StudentResult 列的值被增加了1，并且结果列被命名为“提分后”
SELECT `studentno`, `StudentResult`+1 AS '提分后' FROM 从中选择数据的表名;
--执行这个查询将返回一个结果集，其中包含两列：studentno 列显示学生的编号，而 提分后 列显示 StudentResult 列值加 1 后的结果

避免SQL返回结果中包含 ’ . ’ , ’ * ’ 和’ () ’等字符，这可能会干扰应用程序的逻辑或解析

 where条件语句

用于过滤查询结果，只返回满足指定条件的记录。

运算符含义语法
=,<>,!=,>,<,>=,<=
BETWEEN … AND …在某个范围内（闭区间）WHERE 列名 BETWEEN 起始值 AND 终止值;选择 指定列的值在 起始值 和 终止值 之间的记录
AND 或 &&添加多个且条件AND sex = 男
OR 或 ‖添加多个或条件OR sex = 男
NOT选择不符合指定条件的记录固定搭配 NOT [EXISTS|IN|BETWEEN|LIKE]与and,or的组合不固定，可将NOT当作“不是”
IN选择指定字段有指定值的记录WHERE 列名 IN (值1, 值2, 值3，...);选择 指定列的值等于值1, 值2, 值3，...的记录
LIKE模糊匹配
IS NULL选择指定列中没有值（即为NULL）的记录WHERE 列名 IS NULL选择表中指定列值为 NULL 的记录
IS NOT NULL选择指定列中有值（即不为NULL）的记录WHERE 列名 IS NOT NULL选择表中指定列值不为 NULL 的记录
LIKE模糊匹配详见 ##模糊查询
  • 逻辑运算符尽量使用英文

以上操作符可以组合使用

模糊查询

LIKE 运算符 语法格式：WHERE 列名 LIKE pattern; pattern是一个包含通配符的字符串，用于定义搜索模式。LIKE运算符支持以下通配符：

% （百分号） ：代表零个、一个或多个字符。例如，'L%'会匹配所有以字母“L”开头的字符串。 _ （下划线） ：代表一个单一的字符。例如，'L_'会匹配所有以字母“L”开始，并且长度为2的字符串，如“La”或“Lb” 。 [list of characters] ：指定集合中的任何单个字符；同样地，可以使用范围指定。例如，'[abc]'会匹配任何包含“a”、“b”或“c”的单个字符；[a-z]匹配任何小写字母。 [^list of characters] 或 [!list of characters] ：指定不在列表或范围内的任何单个字符。例如，'[^0-9]'会匹配任何不是数字的单个字符。（“匹配”=“相当于”）

示例

SELECT * FROM productsWHERE product_name LIKE 'A[1234]_[^x]%';
-   以字母"A"开头；第二个字符可以是数字"1"、"2"、"3"或"4"中的任意一个；
-   第三个字符是一个字符；第四个字符不是字母"x"；右侧部分可以包含任何字符

联表查询 Join...ON...

七种Join：

Java MySQL

  1. INNER JOIN (内连接)

    描述：当两个表中的指定列有匹配的值时，返回这些匹配的行。 默认的连接类型，JOIN 关键字默认情况下指的是 INNER JOIN 语法：

    SELECT 表名.列名1, 表名.列名2, 表名.列名3 , ... 
FROM 表名1  
INNER JOIN 表名2 ON 表名1.列名 = 表名2.列名;

    运行过程 1.表名1.列名 中的每一行都会检查在 表名2.列名 中是否有某行符合连接条件 2.如果 表名2.列名 中某一行与 表名1.列名 当前行符合连接条件，那么按 指定列名 返回这两行的数据值，组成 结果集 中的行

    ON 表名1.列名 = 表名2.列名;中 两个列中的数据必须是相同的数据类型

  2. LEFT JOIN (左连接)

    描述：返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则结果中对应的列将为 NULL。 语法：

    SELECT 表名.列名1, 表名.列名2, 表名.列名3 , ... 
FROM 左表名 
LEFT JOIN 右表名 ON 左表名.列名 = 右表名.列名;

    运行过程 1.左表名1.列名 中的每一行都会检查在 右表名2.列名 中是否有某行符合连接条件 2.如果 右表名2.列名 中某一行与 左表名1.列名 当前行符合连接条件，那么按 指定列名 返回这两行的数据值，组成 结果集 中的行 3.如果 右表名2.列名 中没有与 左表名1.列名 中当前行 符合连接条件的 行 ，则按 指定列名 返回左表名1.列名的当前行的数据值，指定列名 中 左表没有的列 的值赋为 NULL，组成 结果集 中的行

  3. RIGHT JOIN (右连接) 描述：返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则结果中对应的列将为 NULL。 语法：

    SELECT 表名.列名1, 表名.列名2, 表名.列名3 , ... 
FROM 右表名 
RIGHT JOIN 左表名 ON 右表名.列名 = 左表名.列名;

    运行过程 1.右表名1.列名 中的每一行都会检查在 左表名2.列名 中是否有某行符合连接条件 2.如果 左表名2.列名 中某一行与 右表名1.列名 当前行符合连接条件，那么按 指定列名 返回这两行的数据值，组成 结果集 中的行 3.如果 左表名2.列名 中没有与 右表名1.列名 中当前行 符合连接条件的 行 ，则按 指定列名 返回右表名1.列名的当前行的数据值，指定列名 中 左表没有的列 的值赋为 NULL，组成 结果集 中的行

示例

假设有两个表：students（学生表）和 scores（成绩表）。

students 表：

| student_id | student_name |
|------------|--------------|
| 1          | Alice        |
| 2          | Bob          |
| 3          | Charlie      |
| 4          | Dave         |

scores 表：

| score_id | student_id | subject  | grade |
|----------|------------|----------|-------|
| 1        | 1          | Math     | 90    |
| 2        | 1          | English  | 85    |
| 3        | 3          | Math     | 78    |
| 4        | 4          | English  | 92    |

INNER JOIN（内连接）

查询：

SELECT students.student_name, scores.subject, scores.grade
FROM students
INNER JOIN scores ON students.student_id = scores.student_id;

结果：

| student_name | subject  | grade |
|--------------|----------|-------|
| Alice        | Math     | 90    |
| Alice        | English  | 85    |
| Charlie      | Math     | 78    |
| Dave         | English  | 92    |

LEFT JOIN（左连接）

查询：

SELECT students.student_name, scores.subject, scores.grade
FROM students
LEFT JOIN scores ON students.student_id = scores.student_id;
--WHERE StudentResult IS NULL   添加这句返回 缺考的同学

结果：

| student_name | subject  | grade |
|--------------|----------|-------|
| Alice        | Math     | 90    |
| Alice        | English  | 85    |
| Bob          | NULL     | NULL  |
| Charlie      | Math     | 78    |
| Dave         | English  | 92    |

RIGHT JOIN（右连接）

查询：

SELECT students.student_name, scores.subject, scores.grade
FROM students
RIGHT JOIN scores ON students.student_id = scores.student_id;

结果：

| student_name | subject  | grade |
|--------------|----------|-------|
| Alice        | Math     | 90    |
| Alice        | English  | 85    |
| Charlie      | Math     | 78    |
| Dave         | English  | 92    |

注意，在实际应用中，RIGHT JOIN 不如 LEFT JOIN 常见，因为通常我们更关心主表（如学生表）的数据，并希望即使它们没有相关的数据（如成绩）也能被返回。

自连接

自连接（self-join）是指一个表与其自身进行连接。这通常用于查询表内的相关记录，例如查找父子关系、层级关系等

用途：用于处理包含一个具有层次结构或关联关系的数据列的表，自连接可以将同一列中具有关系的数据值 组合 成一行记录，在结果集中呈现。这通过连接条件（即具体关系，通常是主键列和外键列关系）来实现的，该条件决定了将列中的哪些行的值放在同一记录。 例如，返回一个员工表，其中每个员工都有一个经理。通过使用自连接，可以编写一个查询，将每个员工及其经理的信息组合在同一行记录中。这样，就可以在同一行中看到员工的信息以及他们的经理的信息。

自连接是一种特殊类型的内连接（inner join），其中至少有两个表是相同的。

SELECT
新表名1.列名a AS 新列名1,
新表名2.列名a AS 新列名2         --两个"列名"是同一个列，给同一列取不同的别名
FROM 原表名 新表名1             
JOIN 原表名 新表名2             --两个要连接的表是同一个表，给同一表取不同的别名
ON 新表名1.列名 = 新表名2.列名; --两个"列名"可以相同，这是连接两个 表实例 的条件
WHERE condition;               --可选，用于进一步过滤结果集
  • 新表名1，2 代表 表的两个特定实例，因为它们有一列的别名不同
  • 给同一列取不同的别名的原因：列名a中的值需要在 结果集 中分成两列 —— 新列名1，新列名2
  • 列名a中的值 根据 新表名1.列名 = 新表名2.列名 的条件 组合成 记录

示例

创建 employees 表并插入一些示例数据：

-- 创建 employees 表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- 插入示例数据
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'John Doe', NULL),       -- John Doe 没有经理
(2, 'Jane Smith', 1),        -- Jane Smith 的经理是 John Doe
(3, 'Mike Johnson', 1),      -- Mike Johnson 的经理是 John Doe
(4, 'Emily Davis', 2),       -- Emily Davis 的经理是 Jane Smith
(5, 'David Lee', 3);         -- David Lee 的经理是 Mike Johnson

使用自连接来查询每个员工及其经理的信息：

-- 使用自连接查询员工及其经理的信息
SELECT 
    e1.name AS employee_name,
    e2.name AS manager_name
FROM 
    employees e1
JOIN 
    employees e2 ON e1.manager_id = e2.id;

这个查询将返回以下结果：

employee_name | manager_name
--------------|-------------
Jane Smith    | John Doe
Mike Johnson  | John Doe
Emily Davis   | Jane Smith
David Lee     | Mike Johnson

在结果中，可以看到每个员工及其经理的名字。注意，John Doe没有经理，因此在结果中没有他的记录。如果想要包括没有经理的员工，可以使用LEFT JOIN来代替JOIN

排序和分页

排序（ORDER BY）

ORDER BY 子句用于对查询结果集进行排序。它可以出现在 SELECT、INSERT、UPDATE 和 DELETE 语句中，但通常与 SELECT 语句一起使用。

SELECT ...
FROM ...
ORDER BY 要排序的列名 [ASC | DESC];
  • ASC：升序排序，即从最小值到最大值（默认）
  • DESC：降序排序，即从最大值到最小值。
  • 对字符串进行排序会基于字符的 Unicode 值、默认的字典顺序或指定的排序规则

示例

SELECT name, age
FROM employees
ORDER BY age ASC;

这个查询将返回 employees 表中所有员工的 name 和 age 列，并按照 age 列升序排序。

分页（LIMIT）

LIMIT 子句用于限制由查询返回的记录数量，常用于分页。

SELECT ...
FROM ...
ORDER BY 要排序的列名
LIMIT offset, length;
  • offset：用于先跳过指定的行数n再开始返回记录。
  • length：要返回的记录数。

示例

SELECT name, age
FROM employees
ORDER BY age
LIMIT 0, 5;

这个查询将返回 employees 表中 age 列排序后的前 5 条记录。

组合使用

通常，会先使用 ORDER BY 再使用 LIMIT 子句，组合使用，以便对结果集进行排序和分页。

示例

SELECT name, age
FROM employees
ORDER BY age DESC
LIMIT 0, 5;

这个查询将返回 employees 表中 age 列降序排序后的前 5 条记录。

子查询

MySQL中的子查询（也称为子选择或子选择查询）是一个嵌套在其他查询中的查询。子查询可以出现在许多不同的位置，如SELECT、FROM、WHERE和HAVING子句中，以及INSERT、UPDATE和DELETE语句中。

(SELECT 子列名 FROM 子表名 WHERE 条件)：子查询返回 子表名 中满足 条件 的记录的 子列名 的值，该值用于与 主表名 中的 列名 进行比较

如果 子表名 和 主表名 是同一个表，那么子查询通常用于筛选满足 条件 的记录，然后主查询从这些筛选后的记录中选择需要的字段。 如果 子表名 和 主表名 是不同的表，那么子查询通常用于从一个表中获取数据，然后主查询使用这些数据来过滤另一个表中的记录。 主表名 和 子表名 是否相同取决于查询逻辑和想从哪些表中获取数据

[表名].[列名] 表示 [表名] 中的 [列名]列

类型

  1. 标量子查询：返回的结果集只有一行一列。通常与单行操作符（如 =, >, <, >=, <=, <> 等）一起使用。例如，使用 MAX() 或 COUNT() 函数的子查询。
SELECT 列名1                           --结果集 中需要包含的唯一字段
FROM 主表名                              --主查询的数据来源表
WHERE 列名 = (SELECT 子列名 FROM 子表名 WHERE 条件);    --()内为子查询部分
  • 标量子查询中，子查询部分的 条件 必须只能返回一个值（或零个值，如果没有匹配项）
  • WHERE 列名 = (...); 是主查询的过滤条件，它确保只有列值与子查询返回的值相等的记录会被选中
  1. 列子查询：返回的结果集只有一列，但可以有多行。通常与多行操作符（如 IN, ANY/SOME, ALL, NOT IN 等）一起使用。
SELECT 列名1, 列名2 ...                  --结果集 中需要包含的字段
FROM 主表名                              --主查询的数据来源表
WHERE 列名 IN (SELECT 子列名 FROM 子表名 WHERE 条件);    --用于筛选数据来源表中的记录，()内为子查询部分
  • IN 操作符要求主查询中的 列名 与 子查询 返回的结果集中的至少一个值匹配，所以列名 必须是子查询 返回的结果集中的一个值
  1. 行子查询：返回一个行的子查询。
SELECT *                              -- * 表示从数据来源表中选择所有列
FROM 主表名
WHERE column1 = (SELECT columnA FROM table2 WHERE condition)  
  AND column2 = (SELECT columnB FROM table2 WHERE condition) 
  AND column3 = (SELECT columnC FROM table2 WHERE condition); -- 如果有更多的列需要比较，继续添加更多的AND条件

行子查询的子查询返回的结果集一般包含多个列

  1. 表子查询：返回一个表的子查询，通常与FROM子句一起使用。
SELECT [列名列表]  --多个列名
FROM [主表名]

JOIN [子查询] AS [别名] ON [主表别名].[主表列名] = [子查询别名].[子查询列名];  
--返回 主表和子查询结果集（虚拟表）中满足连接条件（ON子句中指定的条件）的行的集合
或者
WHERE [条件];      --条件返回多个行
返回满足指定条件的行

使用场景&注意事项

使用场景

  1. 数据验证：在WHERE子句中，可以使用子查询来验证数据。例如，检查一个值是否存在于另一个表中。
  2. 数据聚合：使用子查询进行数据的聚合，如计算平均值、最大值、最小值等。
  3. 排序和限制：使用子查询与ORDER BY和LIMIT结合，对数据进行排序和限制。
  4. 数据插入、更新和删除：在INSERT、UPDATE和DELETE语句中，使用子查询来插入、更新或删除数据。

注意事项

  1. 性能：子查询可能会导致性能问题，特别是当它们涉及大量数据时。确保对相关的表进行适当的索引，并考虑查询优化。
  2. 可读性：虽然子查询可以提供强大的功能，但它们也可能使查询变得复杂和难以阅读。在编写复杂的子查询时，注意保持代码清晰和注释。
  3. 限制：并非所有SQL功能都与子查询兼容。在某些情况下，可能需要寻找其他方法来实现想要的功能。

MySQL函数

常用函数

数学运算函数

数学运算函数返回
SELECT ABS(n);n的绝对值
SELECT CEILING(n);大于或等于 n 的最小整数（向上取整）
SELECT FLOOR(n);小于或等于 n 的最大整数（向下取整）
SELECT RAND();返回一个0~1之间的随机数
SELECT SIGN(n);符号函数：n为 负数返回-1、正数返回1、0返回0

字符串函数

字符串函数返回
SELECT CHAR_LENGTH('字符串');字符串包含的字符数
SELECT CONCAT('字符串a','字符串b','字符串c');多个字符串合并为一个字符串，参数可以有多个
SELECT INSERT('初始字符串',n,m,'要插入的字符串');替换字符串，从初始字符串的第n个字符开始计数的 m个字符替换为 要插入的字符串（包括第n个字符，下同）
SELECT LOWER('字符串');字符串中的所有字母转换为小写字母
SELECT UPPER('字符串');字符串中的所有字母转换为大写字母
SELECT LEFT('字符串',n);字符串中左边n个字符
SELECT RIGHT('字符串',n);字符串中右边n个字符
SELECT REPLACE('字符串A','字符串a','字符串b');将 字符串A 中的 字符串a 替换为 字符串b
SELECT SUBSTR('字符串',n,m);截取从第n个字符开始计数的 m个字符
SELECT INSTR('字符串A,字符串a');字符串A中最靠左的字符串a的第一个字符的索引（即该字符的位次（从1开始计数））
SELECT REVERSE('字符串');将字符串中的字符倒序排列

字符串可以是 单个字符

日期时间函数

日期时间函数返回
SELECT CURRENT_DATE();SELECT CURDATE();当前年月日，返回格式为 YYYY-MM-DD
SELECT NOW(); SELECT LOCALTIME(); （以连接到数据库的客户端的时区来返回值）SELECT SYSDATE(); （以连接到数据库的服务端的时区来返回值）当前年月日时分秒，返回格式为 YYYY-MM-DD HH:MM:SS
SELECT YEAR(NOW());当前 年
SELECT MONTH(NOW());当前 月
SELECT DAY(NOW());当前 日
SELECT HOUR(NOW());当前 时
SELECT MINUTE(NOW());当前 分
SELECT SECOND(NOW());当前 秒

系统信息函数

系统信息函数返回
SELECT SYSTEM_USER();启动MySQL服务器进程时所用的操作系统用户名
SELECT VERSION();当前MySQL服务器的版本号
SELECT USER();当前数据库连接的用户名和主机名

聚合函数

函数名称返回
SELECT COUNT( ) FROM 表名表中指定(所有)列的记录总和数
SELECT SUM(字段/表达式) FROM 表名;表中字段或表达式一列的所有值的总和
SELECT AVG(字段/表达式) FROM 表名;表中字段或表达式一列的所有值的平均值
SELECT MAX(字段/表达式) FROM 表名;表中字段或表达式一列的所有值中最大的值
SELECT MIN(字段/表达式) FROM 表名;表中字段或表达式一列的所有值中最小的值

COUNT()详析

  • SELECT COUNT(字段名) FROM 表名; 用于统计指定表中指定字段的非空值的数量，即不统计值为null 的记录。 特殊的，如果字段名是某个表的主键或具有唯一约束，那么COUNT函数将返回表中的记录数，因为主键或具有唯一约束的字段不能有重复值或NULL值。

  • SELECT COUNT(*) FROM 表名; SELECT COUNT(1) FROM 表名; 从含义上讲，count(1) 与 count(*) 都统计指定表中的记录数量，无论字段是否为空，即包含字段为null 的记录。它们的效率几乎没有差别。

  • 执行效率的一些对比： 列名为主键，count(列名)会比count(1)快 列名不为主键，count(1)会比count（列名）快 如果表多个列并且没有主键，则count（1）的执行效率优于count（) 如果表有主键，则 count（主键）的执行效率最优 如果表只有一个字段，则 count（）的执行效率最优

SUM()详析

  • SUM() 函数会忽略 NULL 值。即 NULL值将不会被计入总和
  • SUM() 函数只能用于数值类型的字段。如果尝试对非数值类型的字段使用，将会得到一个错误
  • 如果想要在一个查询中同时获得多个聚合结果，可以在 SELECT 子句中列出多个 SUM() 函数，并使用逗号分隔它们，每个函数对应一个想要加总的字段
  • 可以使用别名标识算出的总和，提高代码的可读性
  • 表达式包括字段之间、字段与常量值之间的运算
  • 示例 
    SELECT 
    SUM(字段名1) AS 别名a,   --使用别名 total_product_sales 标识这个总和
    SUM(字段名2) AS 别名b    --同时获得多个聚合结果
FROM 表名;
--查询的结果将包含两列：total_product_sales 和 total_service_sales，分别对应两个字段的总和

    SELECT SUM(字段名1 + 字段名2) AS 别名a;
SELECT SUM(字段名1 + 常数值) AS 别名b  

SELECT SUM(字段名) AS 别名c
FROM 表名
WHERE 条件       --例如 YEAR(date_column) > 2023;

MAX()&MIN()详析

  • MAX() 和 MIN() 函数可以用于非数值类型的字段（如文本或日期），它们根据字符编码或日期顺序来确定“最大”或“最小”的值
  • 对于文本数据，MAX() 函数返回字符编码值“最大”的字符串，而 MIN() 函数返回字符编码值“最小”的字符串 对于日期数据，MAX() 函数返回最晚的日期，而 MIN() 函数返回最早的日期

总结

  • 以上聚合函数都忽略 NULL 值 （除了count(1)、count(*)）
  • 以上聚合函数只有 SUM AVG 只能用于数值类型的字段。如果尝试对非数值类型的字段使用，将会得到一个错误
  • 可以在聚合函数中使用表达式，而不仅仅是字段名

示例

假设有一个名为sales的表，其中包含以下字段：product_id（产品ID）、sale_date（销售日期）、quantity（销售数量）和price（产品价格）。

-- 选择需要查询的字段  
SELECT     
    product_id,                              -- 产品ID，用于分组和识别不同的产品  
    COUNT(quantity) AS total_sales,          -- 计算每个产品的总销售数量  
    SUM(quantity * price) AS total_revenue,  -- 计算每个产品的总销售额（销售数量乘以单价）  
    AVG(price) AS average_price,             -- 计算每个产品的平均销售价格  
    MAX(price) AS max_price,                 -- 计算每个产品的最高销售价格  
    MIN(price) AS min_price                  -- 计算每个产品的最低销售价格  
FROM                    -- 指定从销售表中选择数据，其中包含了产品的销售数据  
    sales  
WHERE                   -- 设定过滤条件，只选择数量大于0的记录
    quantity > 0 
GROUP BY                -- 按照产品ID进行分组，以便为每个产品聚合数据
    product_id            
HAVING                  -- 对分组后的数据进行过滤，只选择总销售数量大于10的产品组  
    total_sales > 10    
ORDER BY                -- 对结果进行排序，按照总销售额降序排列  
    total_revenue DESC;

注意

  • 在WHERE子句中，只能过滤那些还没有经过分组的原始数据记录。一旦数据被分组，就只能在HAVING子句中使用聚合函数及其派生列来对分组后的结果进行过滤。
  • SELECT子句中定义的聚合函数、派生列（别名）在分组之前是不可用的。 因为这些函数和派生列是基于分组后的数据计算的。 如 此例中的聚合函数（如COUNT()、SUM()、AVG()、MAX()和MIN()）以及它们的派生列（例如，total_sales、total_revenue、average_price、max_price和min_price），它们在分组之前不可用。

数据库级别的MD5加密（扩展）

定义：一种在数据库中存储敏感信息（如密码）时使用的加密技术。MD5，全称Message-Digest Algorithm 5（信息-摘要算法5），是一种哈希算法，而不是加密算法。它对信息进行摘要采集，再通过一定的位运算，最终获取一个固定长度的MD5字符串。

用途：保护存储在数据库中的敏感信息，尤其是密码。当用户在数据库中注册或设置密码时，密码不会以明文形式存储，而是通过MD5算法加密后再存储。这样，即使数据库被非法访问，攻击者也无法直接获取到用户的明文密码。

语法格式

对 字段名 中的数据进行MD5加密。（使用UPDATE语句和MySQL内置的MD5()函数来实现这一点）

UPDATE 表名 SET 字段名 = MD5(字段名);

加密指定字段和特定记录 的密码，使用WHERE子句来指定条件

UPDATE 表名 SET 字段名 = MD5(字段名) WHERE 条件;

在插入新数据时自动对密码进行加密

INSERT INTO 表名 VALUES(字段值1,字段值2,MD5(字段值3));   --自动对字段值3进行加密

当用户尝试登录时，需要从表中选择特定用户，并验证其输入的密码是否与数据库中存储的加密密码匹配

SELECT * FROM 表名 WHERE 用户字段名 ='用户名字段值' AND 已被加密的字段名=MD5('被加密的字段值');

事务

事务（Transaction）是数据库操作的基本单位，它是一组一起执行的数据库操作，这些操作要么全部执行，要么全部不执行。事务是确保数据库从一个一致状态转移到另一个一致状态的过程。在关系型数据库中，事务通常包含一系列的SQL语句，这些SQL语句按照特定的顺序执行，以完成某种业务逻辑。

关键属性

即ACID原则，是评价一个事务处理系统是否可靠的标准。这四个属性分别是：

  1. 原子性（Atomicity） ：原子性是指事务是一个不可分割的工作单位，以确保事务的整体性。事务中的操作要么全部完成，要么全部不完成。 如果事务在执行过程中发生错误，那么它会被回滚到事务开始前的状态，就好像这个事务从来没有执行过一样。原子性确保事务的整体性，使得事务内的操作要么全部成功，要么全部失败。
  2. 一致性（Consistency） ：一致性是指事务必须使数据库从一个一致性状态转变到另一个一致性状态。在事务开始之前和事务结束以后，数据库的有效性和准确性没有被破坏。它确保写入数据库的任何数据都必须符合预先定义的规则、约束和完整性要求，包括字段类型、数据约束（如外键约束、唯一性约束等）和业务约束（如账户总金额不变等），防止不符合规则的数据被写入数据库中。
  3. 隔离性（Isolation） ：多个事务并发执行时，一个事务的操作不应影响其他事务。隔离性通过锁机制实现，使得并发执行的事务之间不会互相干扰。 隔离性有助于防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  4. 持久性（Durability） ：一旦事务完成，即使系统发生故障或崩溃，其所做的修改仍然可以保留在数据库中，重新启动后数据库还能恢复到事务成功结束时的状态。通常，事务的持久性是通过数据库系统的日志和恢复机制来实现的。

事务隔离级别

在数据库事务管理中，隔离性（Isolation）是确保事务在执行时不受其他事务的影响。然而，当事务的隔离性实施不当时，会产生一些问题，这些问题通常与事务的并发执行有关。下面是对您提到的三个问题的详细解释：

脏读 脏读是指一个事务读取到了另一个事务尚未提交的数据。换句话说，如果一个事务正在修改某些数据，并且这些数据还没有被提交（即还在修改过程中），而另一个事务此时读取了这些数据，那么第二个事务就读取到了“脏”数据。如果这些“脏”数据在第一个事务中被回滚（因为某些原因没有被提交），那么第二个事务读取到的数据就是不准确的。

不可重复读 不可重复读是指在一个事务内，多次读取同一行数据时，由于其他事务的修改，导致每次读取的结果不同。这通常发生在读取操作与写入操作并发执行时。例如，事务A先读取了一条记录，然后事务B修改了这条记录并提交，接着事务A再次读取这条记录时，发现数据已经改变。

幻读 幻读是指在一个事务内读取某些行的集合时，由于其他事务插入了新的行，导致后续读取时出现了“幻影”行。这与不可重复读类似，但区别在于不可重复读是针对同一行数据的多次读取，而幻读是针对多行数据的读取。例如，事务A先查询了一个范围内的所有记录，然后事务B在这个范围内插入了一条新记录并提交，接着事务A再次查询这个范围内的记录时，发现多了一条之前没有的记录。

为了避免这些问题，数据库管理系统提供了不同的事务隔离级别。这些隔离级别定义了事务之间的可见性和交互程度，从而平衡了数据的一致性和并发性能。常见的隔离级别包括：

  • 读未提交（Read Uncommitted）：允许读取尚未提交的数据，可能导致脏读、不可重复读和幻读。
  • 读已提交（Read Committed）：只允许读取已经提交的数据，避免了脏读，但可能导致不可重复读和幻读。
  • 可重复读（Repeatable Read）：在同一个事务内多次读取同一行数据时，保证读取的数据是一致的，避免了脏读和不可重复读，但可能导致幻读。
  • 串行化（Serializable）：最严格的隔离级别，强制事务串行执行，避免了脏读、不可重复读和幻读，但可能降低并发性能。

基本语法

自动提交模式 如果数据库处于自动提交模式，当执行一个SQL语句（如INSERT、UPDATE或DELETE）时，那么这个SQL语句会立即被提交到数据库，意味着它的更改会立即生效。在这种模式下，不需要显式地调用COMMIT来提交事务。

语句 SET autocommit = 0;：关闭自动提交模式。在关闭自动提交模式后，需要显式地调用COMMIT或ROLLBACK来控制事务的提交或回滚。 SET autocommit = 1;：重新开启自动提交模式。

  • 在MySQL中，默认开启自动提交模式。
  • 当想要执行一个事务时，出于对原子性的遵守，应该首先关闭自动提交模式。

开始、提交、回滚 START TRANSACTION：这标记一个事务的开始。从这条语句开始，直到遇到COMMIT或ROLLBACK，所有的SQL语句都会被视为这个事务的一部分。 COMMIT：这将提交当前事务。当调用此语句时，事务中的所有更改都会永久保存到数据库中。 ROLLBACK：这将回滚当前事务。当调用此语句时，事务中的所有更改都会被撤销，数据库将回到事务开始前的状态。

保存点 保存点是事务中的一个中间点，您可以在此点上选择性地回滚事务，而不是回滚整个事务。

  • SAVEPOINT 保存点名称：在事务中设置一个保存点。之后，您可以选择回滚到这个保存点，而不是回滚整个事务。（保存点名称自定义）
  • ROLLBACK TO SAVEPOINT 保存点名称：这将回滚事务到指定的保存点。保存点之后的更改会被撤销，但保存点之前的更改仍然会保留。
  • RELEASE SAVEPOINT 保存点名称：这将删除指定的保存点。

模拟转账场景

-- 创建一个名为shop的数据库，并设置字符集为utf8，排序规则为utf8_general_ci
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;  
USE `shop`;  -- 切换到shop数据库

CREATE TABLE `account` (  
    `id` INT(11) NOT NULL AUTO_INCREMENT,  -- id字段，用于唯一标识每个账户，设置为自增  
    `name` VARCHAR(32) NOT NULL,           -- name字段，用于存储账户名称，最大长度为32个字符  
    `cash` DECIMAL(9,2) NOT NULL,          -- cash字段，用于存储账户余额，数据类型为DECIMAL，保留两位小数  
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;      -- 在shop数据库中创建一个名为account的表，用于存储账户信息。使用InnoDB引擎，设置默认字符集为utf8

INSERT INTO account (`name`, `cash`)  
VALUES ('A', 2000.00), ('B', 10000.00);    -- 向account表中插入两条数据，模拟用户A和用户B的账户信息
SET autocommit = 0;  -- 关闭自动提交，确保转账操作作为一个事务执行
START TRANSACTION;   -- 开始一个事务，标记事务的起始点
UPDATE account SET cash = cash - 500 WHERE `name` = 'A';  -- 更新用户A的账户余额，减去500元
UPDATE account SET cash = cash + 500 WHERE `name` = 'B';  -- 更新用户B的账户余额，增加500元
COMMIT;              -- 提交事务，确保转账操作被永久保存到数据库中
rollback;            --用于在出现问题时回滚事务
SET autocommit = 1;  -- 恢复自动提交，确保后续操作可以立即生效

索引

 作用与分类

作用

  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表和表之间的连接，实现表与表之间的参照完整性
  • 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化

分类

  1. 主键索引 (Primary Key) ：主键索引是唯一标识表中每一行的索引。一个表只能有一个主键，且主键的值必须是唯一的

    -- 在创建表时指定主键
CREATE TABLE table_name (  
    column1 datatype PRIMARY KEY,  
    column2 datatype,  
    ...  
);
--在这种情况下，列`column1`被指定为主键，因此它将包含唯一且非空的值，并且自动创建一个名为PRIMARY的索引用于加速检索

-- 或者在已存在的表上添加主键：
ALTER TABLE table_name       --使用ALTER TABLE语句，将现有列指定为主键
ADD PRIMARY KEY (column1);

  2. 唯一索引 (Unique Key) ：唯一索引确保索引中的所有值都是唯一的。与主键不同，一个表可以有多个唯一索引。此外，唯一索引的列可以包含空值。

    CREATE TABLE table_name (  
    column1 datatype UNIQUE,  
    column2 datatype,  
    ...  
);  

-- 或者在已存在的表上添加唯一索引  
ALTER TABLE table_name  
ADD UNIQUE (column1);  

-- 或者使用CREATE UNIQUE INDEX 在已存在的表上添加唯一索引
CREATE UNIQUE INDEX index_name  --index_name（索引名）是可选的
ON table_name (column1);

  3. 常规索引 (Index) ：常规索引是最常见的索引类型，它不要求索引的值是唯一的。

    CREATE TABLE table_name (  
    column1 datatype,  
    column2 datatype,  
    ...  
    INDEX index_name (column1),  
    ...  
);  

-- 或者在已存在的表上添加常规索引  
ALTER TABLE table_name  
ADD INDEX index_name (column1);  

-- 或者使用CREATE INDEX  
CREATE INDEX index_name    --index_name（索引名）是可选的
ON table_name (column1);

  4. 全文索引 (FullText) ：全文索引主要用于文本搜索，它们允许数据库系统快速定位到包含特定词语或短语的文本数据。需要注意的是，并非所有的数据库系统都支持全文索引，且它们通常在特定的数据库引擎（MyISAM和MySQL 5.6以后的InnoDB引擎中可用）中才可用。

    CREATE TABLE table_name (  
    column1 datatype,  
    column2 datatype,  
    ...  
    FULLTEXT index_name (column1),  
    ...  
) ENGINE=MyISAM;  

-- 或者在已存在的表上添加全文索引  
ALTER TABLE table_name  
ADD FULLTEXT index_name (column1);

索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

索引的数据结构

  1. 索引类型:
  • Hash索引：Hash索引基于哈希表实现，可以快速定位到单条记录，因此查询单条记录非常快。但是，Hash索引不支持范围查询，而且当数据量增加或数据发生变化时，可能需要重新哈希，这可能会导致性能问题。
  • B-tree索引（或B+树索引）：B-tree（或B+tree）是一种平衡的多路搜索树，常用于数据库和文件系统中。它支持范围查询，并且可以很好地处理大量数据。在B+tree中，所有的值都出现在叶子节点中，并且叶子节点之间以指针相连，这有助于范围查询和顺序访问。InnoDB存储引擎默认使用B+tree作为索引结构。
  1. 不同的存储引擎支持的索引类型:
  • InnoDB：这是MySQL的默认存储引擎，它支持事务、行级别锁定，以及B-tree和Full-text索引。它不支持Hash索引。
  • MyISAM：虽然MyISAM不支持事务和行级别锁定，但它支持B-tree和Full-text索引。MyISAM常用于只读或大量读取的场景。
  • Memory：这种存储引擎将所有数据存储在RAM中，因此速度非常快。但它不支持事务和行级别锁定，只支持B-tree和Hash索引，不支持Full-text索引。
  • NDB：NDB是MySQL Cluster的存储引擎，它支持事务、行级别锁定，并且只支持Hash索引。它不支持B-tree和Full-text索引。
  • Archive：这种存储引擎用于存档和压缩数据。它不支持事务、行级别锁定，也不支持B-tree、Hash和Full-text索引。

数据库用户管理

创建用户

使用SQLyog 创建用户，并授予权限演示

Java MySQL Java MySQL

基本命令

刷新权限

FLUSH PRIVILEGES 命令用于重新加载授权表，使对表所做的更改生效。命令在修改了用户权限或者添加/删除了用户之后使用。

增加用户

CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串) 命令用于在MySQL中创建新用户，需要管理员权限，并且只能创建用户，不能赋予权限。创建用户时需要指定用户名和密码，并且密码需要用引号括起来。如果要在创建用户的同时设置密码，且密码以明文形式给出，那么应该忽略PASSWORD关键词。

  • 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限
  • 只能创建用户，不能赋予权限。所以在创建用户之后，通常需要使用 GRANT 命令来赋予用户相应的权限
  • 用户名和密码，都需要使用引号。如，'user_name'@'192.168.1.1'，user_name 是用户名，192.168.1.1 是该用户可以从哪个主机地址登录
  • 如果在创建用户的同时设置密码，想要密码以明文形式给出，那么应该忽略PASSWORD关键词。想要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

重命名用户

RENAME USER 旧名 TO 新名; 命令用于重命名已存在的用户。

设置密码

SET PASSWORD = PASSWORD('密码') 为当前用户设置或更改密码 SET PASSWORD FOR 用户名 = PASSWORD('密码') 为指定用户设置或更改密码 使用PASSWORD()函数可以对密码进行加密处理，但现代MySQL版本通常推荐使用更安全的密码验证插件

删除用户

DROP USER 用户名 命令用于删除指定用户及其相关权限。

分配权限/添加用户

GRANT 命令用于给用户分配权限。可以指定用户可以访问的数据库、表以及可以执行的操作。在分配权限的同时，也可以设置用户的密码（如果用户在之前不存在）

GRANT 权限类型 ON 数据库名.表名 TO '用户名'@'登录主机' IDENTIFIED BY '密码';

  • 权限类型：可以是单个权限（如SELECT、INSERT、UPDATE、DELETE等），也可以是多个权限的组合，使用逗号分隔。如果要授予用户所有权限，可以使用ALL PRIVILEGES。
  • 数据库名.表名：指定权限适用的数据库和表。使用*代表所有数据库或所有表，例如*.*表示所有数据库的所有表，数据库名.*表示数据库名下的所有表。
  • 用户名：将要授予权限的MySQL用户名。
  • 登录主机：用户可以从哪个主机连接到MySQL服务器。使用localhost表示用户只能从本机连接，使用%表示用户可以从任何IP地址连接。为了安全起见，通常建议限制登录主机。
  • 密码：用户的密码。如果用户在之前不存在，这个命令会创建用户并设置密码；如果用户已经存在，则仅设置或修改密码（取决于MySQL的版本和配置）。

查看权限

SHOW GRANTS 命令用于数据库管理员或用户查看特定用户或当前登录用户所拥有的权限。这些权限通常包括对数据库、表、列等的访问和操作权限。

SHOW GRANTS FOR '用户名'@'主机名';

  • 用户名：要查询的用户名。如果要查询当前登录用户的权限，可以使用 CURRENT_USER 或省略用户名部分。
  • 主机名：用户所在的主机名或IP地址。如果要查询用户从任何主机登录的权限，可以使用通配符 %。

返回：执行 SHOW GRANTS 命令后，MySQL将返回一个结果集，其中包含用户的授权信息。这些信息通常以 GRANT 语句的形式呈现，展示了用户可以执行的具体操作以及这些操作适用的范围（如数据库、表、列等）。 权限：执行 SHOW GRANTS 命令的用户必须拥有足够的权限来查看其他用户的授权信息。通常，只有数据库管理员或具有适当权限的用户才能执行此命令。

撤消权限

REVOKE 命令用于撤销用户的权限。可以撤销用户对特定数据库或表的特定权限。

REVOKE 权限类型 [, 权限类型] ... ON [对象类型] 结构名称 FROM user [, 用户名] ...

  • 权限类型：要撤销的权限类型，如 SELECT、INSERT、UPDATE 等。可以指定多个权限类型，用逗号分隔。
  • 对象类型：指定对象类型，不同的对象类型可能具有不同的权限集，因此需要明确指定对象类型。如 DATABASE（整个数据库）、TABLE（表）、VIEW（视图） 等。如果不指定对象类型，则默认为 TABLE。
  • 结构名称：要撤销权限的数据库、表或其他对象的名称。
  • 用户名：要撤销权限的用户名或角色名。可以指定多个用户或角色，用逗号分隔。

REVOKE ALL PRIVILEGES 命令可以撤销用户的所有权限。这对于确保数据库的安全性非常重要，特别是在用户离职或转岗时。

权限类型&表维护命令（拓展）

权限类型

MySQL的权限系统允许数据库管理员精细地控制用户对数据库的访问和操作。下面是一些关键权限及其解释：

  • ALL [PRIVILEGES]：授予用户在数据库上的所有权限，除了GRANT OPTION。
  • ALTER：允许用户修改表的结构。
  • CREATE：允许用户创建新的表或数据库。
  • DELETE：允许用户从表中删除记录。
  • DROP：允许用户删除表或数据库。
  • EXECUTE：允许用户运行存储过程。
  • INDEX：允许用户创建或删除索引。
  • INSERT：允许用户向表中插入新记录。
  • SELECT：允许用户查询表中的记录。
  • SUPER：允许用户执行一些高级管理操作，如设置全局变量或关闭服务器。
  • UPDATE：允许用户修改表中的现有记录。
  • GRANT OPTION：允许用户将自己的权限授予其他用户。

表维护命令

  • ANALYZE TABLE：此命令用于分析和存储表的关键字分布信息，这有助于优化查询性能。
  • CHECK TABLE：这个命令用于检查表是否有错误。通过指定不同的选项（如QUICK、FAST、MEDIUM、EXTENDED或CHANGED），你可以控制检查的详细程度。
  • OPTIMIZE TABLE：当数据表经过大量的增删改操作后，数据文件可能会产生碎片，这个命令用于整理这些碎片，提高表的访问性能。

MySQL备份

数据库备份的必要性

  1. 数据安全性：备份是防止数据丢失或损坏的最有效手段。无论是由于硬件故障、软件错误、人为错误还是恶意攻击，备份都能确保数据的完整性和可用性。
  2. 灾难恢复：在遭遇自然灾害、电力故障或任何其他形式的严重问题导致系统完全崩溃时，备份是快速恢复服务的唯一途径。
  3. 合规性要求：某些行业或法律可能有关于数据保留和恢复的要求，因此，定期进行数据库备份并保留备份文件以满足这些合规性要求是至关重要的。
  4. 业务连续性：对于企业而言，数据库的可用性直接关系到业务的连续性。备份可以确保在发生问题时，业务能够迅速恢复正常运行。

MySQL数据库备份的方法

  1. mysqldump备份工具：
  • 说明：mysqldump是MySQL提供的一个命令行工具，同时也是一个客户端程序，它可以从命令行或脚本中调用，以生成数据库的备份。mysqldump的主要功能是将MySQL数据库的结构和数据导出为一个包含SQL语句的文件。这些SQL语句可以在需要时执行，以重建数据库并填充数据。
  • 作用 转储数据库：mysqldump能够完整地导出数据库的结构（表、视图、存储过程、触发器等）以及数据，生成一个或多个SQL文件。这些文件可以作为数据库的备份，或在需要时用于恢复数据。 搜集数据库进行备份：通过执行mysqldump命令，管理员可以定期搜集数据库的当前状态，并将其保存为备份文件。这些备份文件可以在数据库发生意外损坏或丢失时用于恢复。 数据迁移：由于mysqldump生成的SQL文件是跨平台的，因此它不仅可以用于备份和恢复同一个MySQL服务器上的数据，还可以用于将数据迁移到另一个MySQL服务器，甚至是其他兼容SQL的数据库系统。
  1. 数据库管理工具：
  • 例子：如SQLyog、Navicat、phpMyAdmin等。
  • 作用：这些工具通常提供图形化界面，使得备份过程更加直观和简单。它们通常也支持多种备份选项和格式。
  1. 直接拷贝数据库文件和相关配置文件：
  • 方法：在MySQL的某些配置（如使用InnoDB存储引擎且启用了innodb_file_per_table）下，可以直接复制数据库文件和相关的配置文件作为备份。
  • 注意事项：这种方法需要确保在复制过程中数据库处于一致状态，通常需要在数据库关闭或处于某种安全状态（如使用FLUSH TABLES WITH READ LOCK）时进行。此外，还需要备份二进制日志以确保数据的一致性。

MySQL数据库备份的操作

使用数据库管理工具SQLyog导出

使用数据库管理工具SQLyog导出数据库或表的步骤：

  1. 打开SQLyog工具，并连接到目标数据库服务器。
  2. 在左侧的数据库列表中，找到并选中你想要导出的数据库或表。
  3. 右键点击选中的数据库或表，选择“备份/导出”选项。
  4. 在弹出的对话框中，你会看到多种备份和导出选项。通常，选择“备份数据库，转储到SQL”是最常见的导出方式，因为它会生成一个包含SQL语句的文件，这些语句可以在其他MySQL服务器上执行以重建数据库或表。
  5. 在接下来的对话框中，你可以进一步自定义导出的内容。例如，你可以选择导出哪些表、是否导出存储过程和函数、是否包含数据等。（如下图）
  6. 选择一个保存导出文件的路径和文件名。确保你知道这个文件的保存位置，以便之后可以找到并使用它。
  7. 点击“导出”按钮开始导出过程。根据数据库的大小和复杂度，这个过程可能需要一些时间。
  8. 导出完成后，你可以在指定的路径下找到生成的SQL文件。这个文件就是你的数据库或表的备份，可以在需要时用于恢复数据或迁移到其他服务器。
Java MySQL

MySQL命令行备份和还原数据库

MySQL命令行备份和还原数据库的步骤：

  1. 配置环境变量 配置环境变量通常是为了在命令行中更方便地运行MySQL相关的命令，而不需要每次都指定完整的路径。一旦环境变量配置正确，就可以在任何地方通过mysql、mysqldump等命令来操作MySQL。

  2. 备份（导出）和还原（导入）数据、数据库

    导出（备份）语法格式

    1.1. 导出单张表

    mysqldump -u[用户名] -p[密码] [数据库名] [表名] > [备份文件路径]

    1.2. 导出多张表

    mysqldump -u[用户名] -p[密码] [数据库名] [表名1] [表名2] ... > [备份文件路径]

    1.3. 导出整个数据库

    mysqldump -u[用户名] -p[密码] [数据库名] > [备份文件路径]

    1.4. 导出多个数据库

    mysqldump -u [用户名] -p[密码] --databases [数据库名1] [数据库名2] ... > [备份文件路径]

    导出时携带条件

    mysqldump -u[用户名] -p[密码] [数据库名] [表名] --where="[条件]" > [备份文件路径]

    导入（还原）语法格式 登录MySQL后使用source命令

    2.1. 登录MySQL 2.1.1. 使用 -p 且带上密码

    mysql -u[用户名] -p[密码]

    2.1.2. 只使用 -p 而不带密码。这将提示输入密码

    mysql -u[用户名] -p

    执行 mysql -uroot -p 命令后，命令行界面通常会显示类似这样的提示：

    Enter password:

    此时，应该输入 root 用户的密码（注意，输入时密码字符不会显示，这是为了保护密码的隐私）。输入完毕后，按回车键，如果密码正确，将登录到MySQL服务器，并看到MySQL的命令行提示符，通常是：

    mysql>

    这意味着后续的SQL操作（比如创建表、插入数据、查询数据等）都会默认在这个数据库上执行，除非你再次切换到其他数据库。 在这个提示符下，就可以输入SQL命令来操作MySQL数据库了。

    2.2. 选择数据库并导入数据（还原） 在MySQL命令行界面中，使用 source 命令：

    USE [数据库名];
SOURCE [备份文件路径];

    执行 use  命令后，MySQL返回了 Database changed 的消息，这意味着已经成功切换到了数据库 [数据库名] 。

    不登录MySQL直接使用mysql客户端命令

    mysql -u[用户名] -p[密码] [数据库名] < [备份文件路径]

在上面的语法格式中：

  • [用户名] 是MySQL数据库的用户名。
  • [密码] 是对应用户的密码。注意，-p和密码之间没有空格，如果直接在命令行中写密码，这样做存在安全风险。推荐的做法是只使用 -p 参数，命令执行后会提示输入密码。
  • [数据库名] 是想要备份或还原的数据库的名称。
  • [表名] 是想要备份的表的名称。
  • [备份文件路径] 是想要将备份数据保存到的文件路径和文件名。
  • -u[用户名] -p[密码] 中 -u 和 [用户名] 之间，以及 -p 和密码之间不应该有空格。

规范化数据库设计

需要数据库设计的原因——数据库比较复杂

良好的数据库设计 :

  • 节省数据的存储空间
  • 能够保证数据的完整性
  • 方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

  1. 需求分析阶段：分析客户的业务和数据处理需求
  2. 概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整.

设计数据库（针对个人博客系统）

设计数据库的步骤（针对个人博客系统）涉及到多个环节，这些环节确保了数据库的完整性、有效性和可维护性。下面对个人博客系统数据库设计步骤的详细解释：

  1. 收集信息，分析需求

    这是数据库设计的起点。在这一阶段，需要深入了解个人博客系统的业务需求，包括用户管理、博客文章发布、分类管理、友链管理、系统信息设置以及用户之间的互动（如关注、评论等）。通过收集和分析这些信息，可以明确数据库需要支持的功能和特性。

  2. 标识实体 实体就是数据库设计过程中需要关注的具体或抽象对象，它们通过属性和关系在数据库中得以表示和存储。

    在确定了业务需求后，接下来要标识出系统中的各个实体。 首先确定 实体——需要管理的关键对象，然后为每个实体设计相应的表来存储其数据。标识实体是设计表的基础，而表则是实现实体数据存储的物理结构。

    对于个人博客系统，主要的实体包括：

  • 用户（User）：代表博客系统的注册用户，包括登录注销、个人信息管理等功能。
  • 分类（Category）：用于管理博客文章的分类，包括创建、编辑和删除分类。
  • 文章（Blog/Post）：存储博客文章的具体内容。
  • 友链（Links）：管理其他博客或网站的友情链接。 *系统信息（Setting）：存储博客系统的配置信息，如关键字、主要字段等。
  • 说说（Status/Mood）：用户发表的心情或简短动态。

  1. 标识每个实体的属性 属性是用来描述实体的特征的，它包含了关于实体的具体信息，这些信息以字段的形式存储在数据库中。

    对于每个实体，需要进一步确定其需要存储的详细信息或属性。例如：

  • 用户（User）：id、用户名、密码、邮箱、个人简介、头像等。
  • 分类（Category）：id、分类名称、创建者（user_id）、创建时间等。
  • 文章（Blog/Post）：id、标题、内容、分类（category_id）、作者（user_id）、发布时间、浏览量等。
  • 友链（Links）：id、链接名称、链接地址、描述、添加者（user_id）等。
  • 系统信息（Setting）：关键字、配置项名称、配置值等。
  • 说说（Status/Mood）：id、内容、发布者（user_id）、发布时间等。

  1. 标识实体之间的关系 实体之间的关系描述了它们如何相互关联和交互，通过表之间的关联来实现。

    对于个人博客系统，主要的关系包括：

  • 用户与文章：一对多关系，一个用户可以发表多篇文章。
  • 用户与分类：一对多关系，一个用户可以创建多个分类。
  • 用户与用户：多对多关系，通过关注功能实现用户之间的关联。
  • 用户与友链：一对多关系，一个用户可以添加多个友链。
  • 用户与说说：一对多关系，一个用户可以发表多条说说。
  • 文章与评论：一对多关系，一篇文章可以接收多个评论，每个评论关联到特定的用户。

  1. 设计数据库结构

    在确定了实体和它们之间的关系后，就可以开始设计数据库的物理结构了。这包括选择合适的数据库类型（如关系型数据库MySQL），设计表结构（包括字段名、数据类型、约束等），以及建立表之间的关系（如外键约束）。

三大范式

三大范式是数据库设计中的基本原则，用于指导如何组织数据库中的表和字段，以确保数据的准确性和完整性，同时减少数据冗余和异常。下面是对三大范式的详细解释以及规范化和性能关系的理解：

第一范式 (1st NF)

原则：确保每列的原子性，即每列都是不可分割的最小数据单元。 解释：第一范式是最基础的数据库设计规范，它要求数据库表的每一列都是不可分割的原子数据项，而不能是集合、数组或其他数据结构的组合。这有助于避免数据冗余和不一致性。 Java MySQL

第二范式 (2nd NF)

原则：在满足第一范式的基础上，每个表只描述一件事情，即表中的所有列都必须完全依赖于主键。 解释：第二范式要求数据库表具有更好的结构，确保表中的每一列都与主键相关，并且只与主键相关。如果表中存在只依赖于主键的一部分的列，那么这些列应该被分离到新的表中。这有助于减少数据冗余和增强数据的一致性。 Java MySQL Java MySQL

第三范式 (3rd NF)

原则：在满足第二范式的基础上，除了主键以外的其他列都不传递依赖于主键列。 解释：第三范式要求数据库表中的每一列都与主键直接相关，而不是通过其他非主键列间接相关。这意味着非主键列之间不应该存在传递依赖关系。通过消除传递依赖，可以进一步减少数据冗余和增强数据的独立性。 Java MySQL

规范化和性能关系

  • 关联查询的表不得超过三张表：这是一个经验性的建议，旨在避免过于复杂的关联查询，提高查询性能。但实际情况可能因业务需求和数据量的大小而有所不同。
  • 为满足某种商业目标，数据库性能比规范化数据库更重要：在某些情况下，为了满足特定的业务需求或优化性能，可能需要对数据库结构进行一定的折中。这并不意味着完全放弃规范化原则，而是需要根据实际情况进行权衡和调整。
  • 在数据规范化的同时，要综合考虑数据库的性能：规范化数据库结构有助于减少数据冗余和提高数据一致性，但过度规范化可能导致查询性能下降。因此，在设计数据库时，需要综合考虑数据规范化和性能之间的关系，找到一个平衡点。
  • 通过在给定的表中添加额外的字段以减少搜索时间：这是一种优化性能的方法，通过在表中添加冗余数据或计算列来减少查询时的计算量和搜索时间。这虽然可以提高查询性能，但可能会增加数据冗余和维护成本。

 JDBC

数据库驱动

JDBC（Java Database Connectivity）是Java提供的一套用于执行SQL语句的API，通过JDBC，Java程序可以连接到任何提供了JDBC驱动的数据库上，并且可以使用SQL语言来访问该数据库。

数据库驱动相当于Java程序与数据库之间的一个中间件或桥梁，负责建立连接、转换数据、执行SQL语句以及处理错误等工作。它使得Java程序能够方便地访问和操作数据库中的数据。

Java MySQL

JDBC

SUN公司（现在称为Oracle Corporation，因为SUN已被Oracle收购）为了简化开发人员的对数据库的操作，提供了一个Java操作数据库的规范，俗称JDBC，这些规范的实现由具体的厂商去做。 对于开发人员来说， 只需要掌握 JDBC 接口的操作即可

第一个JDBC程序

创建测试数据库

步骤 1: 创建一个普通项目

首先，在IDE（如IntelliJ IDEA, Eclipse等）中创建一个新的Java项目。 在IntelliJ IDEA中，Create New Preject → Next → Next → 命名 → Finish

步骤 2: 导入数据库驱动

JDBC只是一个规范，实际与数据库交互的代码由各个数据库的供应商提供，这些代码以jar包的形式存在，被称为“数据库驱动”。

  1. 下载JDBC驱动：
  • 访问数据库供应商的官方网站或可信的Maven仓库，找到对应数据库的JDBC驱动。
  • 下载JDBC驱动的JAR文件。
  1. 将JAR文件添加到项目：
  • 在你的项目文件夹中创建一个 lib 目录（如果还没有的话），用于存放第三方库。 操作：右键点击项目 → New → Directory → 在弹出窗口输入lib → 点击 OK
  • 将下载的JAR文件复制到 lib 目录中。 在电脑文件在找到已下载的JAR文件 → 复制文件 → 右键点击lib目录 → “粘贴”（或 Ctrl+V）
  1. 将JAR 文件添加到项目的库
  • 右键点击 lib 目录 → Add as library → 点击弹出窗口的 OK

步骤 3: 编写测试代码（创建主程序）

  • 右键点击与 lib 同一级别目录的 src → 鼠标光标移到 New → 点击右侧目录的 Package → 在弹出窗口输入com.[自定义名1].[自定义名2] → 右键点击 src 目录下的[自定义名2] → 鼠标光标移到 New → 点击右侧目录的 Java Class → 在弹出窗口输入[自定义名]

    • lib 目录通常用于存放项目依赖的库文件，而 src 目录则用于存放源代码。
    • 鼠标光标移到 New：表示要创建新的文件或目录
    • 点击右侧目录的 Package：表示创建一个新的包（Package）
    • 在弹出窗口输入 com.[自定义名1].[自定义名2]：表示为一个新的包指定名称，按照 Java 的包命名惯例，通常使用反向域名作为包名的前缀。这将在的项目文件系统中创建一个新的目录结构，类似于这样： 
      src/  
└── com/  
    └── [自定义名1]/  
        └── [自定义名2]/
    • 右键点击 src 目录下的 [自定义名2] → New → Java Class：表示将要在已经创建好的包下创建新的 Java 类
    • 在弹出窗口输入 [Java 类名]：表示为一个新的 Java 类指定名称

主程序Java 类代码如下：

package com.[自定义名1].[自定义名2];

//我的第一个jdbc程序
public class [Java 类名] {
    public static void main(String[] args) throws Exception {
    
        //1. 加载驱动。 Class.forName方法 用于动态加载类并返回其 Class 对象。这里用于加载 MySQL 的 JDBC 驱动类
        Class.forName("com.[自定义名1].[自定义名2]");
        
        //2. 用户信息和url
        String url = "jdbc:mysql://[数据库服务器的地址]:[端口号,默认3306]/[要连接的数据库名称]?useUnicode=true&characterEncoding=utf8&useSSL=true";
        //? 及之后的部分通常用于指定数据库连接的各种参数，这是通常需要但语法上是可以没有的。这些参数使用 `&` 分隔。
        String name = "[数据库的用户名]";  //默认为 root
        String password = "[数据库用户的密码]";

        //3. 连接成功，返回数据库对象  connection代表数据库
        Connection [Connection对象名]= DriverManager.getConnection(url,name,password);
        
        //4. 创建用于执行SQL语句对象
        Statement [Statement对象名] = [Connection对象名].createStatement();
        //使用 Connection 对象的 createStatement 方法创建一个 Statement 对象，这个对象用于执行静态的SQL语句
        
        //5. 执行SQL查询并返回结果
        String sql="SELECT * FROM users";
        ResultSet [ResultSet对象名] = [Statement对象名].executeQuery(sql);//返回的结果集，集中封装了全部查询的结果
        //定义一个SQL查询字符串 sql ，然后使用 Statement 对象的 executeQuery 方法执行这个查询，并返回一个包含了查询结果的所有行的 ResultSet 对象
        
        while([ResultSet对象名].next()){
            System.out.println("id+"+resultSet.getObject("id"));
            System.out.println("name+"+resultSet.getObject("NAME"));
            System.out.println("password+"+resultSet.getObject("PASSWORD"));
            System.out.println("email+"+resultSet.getObject("email"));
            System.out.println("birthday+"+resultSet.getObject("birthday"));  //字段名自定义
        }
        //注意这里的列名`"NAME"`和`"PASSWORD"`是区分大小写的，需要确保它们与数据库中的列名大小写一致
        //6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
        //最后，需要关闭所有打开的资源以避免资源泄露。
        //关闭JDBC资源时需要遵守一定的顺序，首先关闭 ResultSet，然后关闭 Statement，最后关闭 Connection。
    }
}

补充说明

第2步：

  • useUnicode=true: 这个参数表示在连接数据库时使用Unicode字符集。
  • characterEncoding=utf8: 指定字符编码为UTF-8，这样可以确保在传输数据时字符能够正确编码。
  • useSSL=true: 表示在连接时使用SSL加密。这有助于增加数据传输的安全性。

第3步：

  • Connection是一个接口，代表了Java应用程序与数据库之间的连接。它定义了一系列与数据库交互的方法，例如执行SQL语句、获取结果集等。
  • [Connection对象名]是Connection接口的一个实例，或者说是对象。通过调用DriverManager.getConnection(url, name, password)方法，请求DriverManager创建一个到指定数据库的连接，并将这个连接的引用赋值给connection变量。一旦有了这个connection对象，就可以通过它执行各种数据库操作了。
  • 如果连接成功，getConnection 方法将返回一个 Connection 对象，该对象代表与数据库的一个活动连接。通过这个 Connection 对象，可以进一步执行SQL语句、查询数据等操作。

第5步：

Statement对象 是Java数据库连接（JDBC）API中的一个关键组件，用于执行静态SQL语句并返回它所生成的结果。以下是Statement对象的主要方法：

  • executeQuery() 方法用于执行SQL查询语句，通常用于从数据库中检索数据。它返回一个 ResultSet 对象，该对象包含查询结果集。 例如： 
    String sql = "SELECT * FROM users";  
    ResultSet resultSet = statement.executeQuery(sql);
  • execute() 方法用于执行任意类型的SQL语句，它可以返回 ResultSet（对于查询语句），或者返回受影响的行数（对于更新、插入和删除操作）。因此，此方法更加通用，但需要检查返回的类型来正确处理结果。例如： 
    String sql = "UPDATE users SET name='John' WHERE id=1";  
boolean isResultSet = statement.execute(sql);  
if (isResultSet) {  
    ResultSet resultSet = statement.getResultSet();  
    // 处理查询结果  
} else {  
    int rowsAffected = statement.getUpdateCount();  
    // 处理更新、插入或删除的行数  
}
  • executeUpdate() 方法用于执行更新、插入或删除操作。它返回一个 int 值，表示受影响的行数。如果尝试使用此方法执行查询操作，虽然不会抛出异常，但返回的受影响的行数将始终是 -1。例如： 
    String sql = "DELETE FROM users WHERE id=1";  
int rowsAffected = statement.executeUpdate(sql);  
System.out.println("Rows affected: " + rowsAffected);
  • 在执行任何SQL语句之前，通常需要通过调用 Connection 对象的 createStatement() 方法创建一个 Statement 对象。Statement [Statement对象名] = [Connection对象名].createStatement();

ResultSet对象 是 JDBC API 中用于表示查询结果集的对象。当执行一个 SQL 查询语句（如 SELECT 语句）时，数据库服务器会返回查询结果，这些结果就是通过 ResultSet 对象来获取的。ResultSet 对象内部封装了查询结果的所有行和列。 下面的 resultset代表[ResultSet对象名]

获得指定的数据类型

不知道列类型的情况下使用

  • resultset. getobject();
Object obj = resultSet.getObject(columnIndex); // 使用列索引  
// 或者  
Object obj = resultSet.getObject(columnName); // 使用列名  
  
if (obj == null) {  
    // 处理null值的情况  
} else {  
    if (obj instanceof String) {  
        String strValue = (String) obj;  
        // 处理字符串值  
    } else if (obj instanceof Integer) {  
        int intValue = (Integer) obj;  
        // 处理整数值  
    } else if (obj instanceof Float) {  
        float floatValue = (Float) obj;  
        // 处理浮点数值  
    } else if (obj instanceof Date) {  
        Date dateValue = (Date) obj;  
        // 处理日期值  
    } else {  
        // 处理其他类型或未知类型  
        // 可以考虑调用obj.toString()获取其字符串表示  
    }  
}

知道列的类型就使用指定的类型

  • resultset.getString(); //列是字符串类型
  • resultse. getInt(); //列是整数类型
  • resultset.getFloat(); //列是浮点类型
  • resultSet.getDouble() //列是双精度浮点类型
  • resultset.getDate(); //列是日期类型

()内可以是[列索引]或[列名]

遍历结果集（指针操作）

ResultSet对象 是数据库查询结果存储类的一个实例，用于封装从数据库查询出来的结果集。 以下是Statement对象的主要方法，操作 ResultSet 对象中的“指针”或“游标”，以遍历查询结果集中的行，用于控制结果集的访问和导航 下面的 resultset代表[ResultSet对象名]

遍历结果集

  • resultSet.next() 方法用于将结果集的指针从当前位置移动到下一行。在遍历结果集时，通常在一个循环中使用 next() 方法来迭代每一行。每次调用 next() 时，它都会返回 true 如果还有下一行，否则返回 false。
while (resultSet.next()) {
    // 处理当前行数据
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    // ...
}

指针位置操作

  • resultSet.beforeFirst() 方法将结果集的指针移动到第一行之前。这通常用于在重新开始遍历结果集之前重置位置。
resultSet.beforeFirst();
// 现在 resultSet 的指针在第一行之前，可以重新开始遍历
  • resultSet.afterLast() 方法将结果集的指针移动到最后一行之后。这通常用于在不知道结果集内容的情况下进行某些定位操作，或者在遍历完所有行之后将指针移动到末尾。
resultSet.afterLast();
// 现在 resultSet 的指针在最后一行之后
  • resultSet.previous() 方法将结果集的指针移动到前一行。它通常与 next() 方法结合使用，以在结果集中双向遍历。
if (resultSet.next()) { // 移动到第一行
    // 处理第一行数据
    // ...
    
    while (resultSet.previous()) { // 从第一行开始反向遍历
        // 处理之前行的数据
        // ...
    }
}
  • resultSet.absolute(int row) 方法将结果集的指针直接移动到指定的行号。行号是从 1 开始的，而不是从 0。如果指定的行号不存在，则指针会被移动到结果集的末尾之后，并且 next() 方法将返回 false。
resultSet.absolute(5); // 将指针移动到第5行
if (resultSet.isAfterLast()) {
    // 第5行不存在，或者结果集为空
} else {
    // 处理第5行的数据
    // ...
}
  • 使用完 ResultSet 后，应该关闭它以释放数据库资源。这通常通过调用 resultSet.close() 方法来完成。

释放资源

在使用完 ResultSet、Statement 和 Connection 后，必须显式地关闭它们以释放资源。这是因为这些对象占用了数据库连接、内存和其他系统资源。如果不关闭它们，可能会导致资源泄露，进而影响应用的性能和稳定性。

关闭资源的顺序应该是：

  1. resultSet.close()：关闭 ResultSet 对象。
  2. statement.close()：关闭 Statement 或 PreparedStatement 对象。
  3. connection.close()：关闭 Connection 对象，释放与数据库的物理连接。

Statement对象详解

Statement 对象

Statement 是 JDBC API 中的一个核心对象，用于向数据库发送 SQL 语句，对数据库进行增删改査。通过 Statement 对象，可以将 SQL 语句发送到数据库进行执行，并获取结果（如果适用）。

executeUpdate 方法

executeUpdate 方法用于向数据库发送 增、删、改的 SQL 语句，该语句导致数据库中的记录被更新（如 INSERT、UPDATE 或 DELETE）。此方法执行完后返回一个整数，表示受影响的行数(即增删改语句导致了数据库几行数据发生了变化)。

两种写法

  • int [变量名] = [Statement对象].executeUpdate("[SQL语句]"); //直接将 SQL 语句作为参数传递
  • String [变量名1] = "[SQL语句]"; int [变量名2] = [Statement对象].executeUpdate([变量名1]); //先将 SQL 语句存储在字符串变量中再作为参数传递

这两段代码最终执行的 SQL 语句和返回的结果都是相同的，没有区别的。在实际应用中，使用字符串变量（第二种）来存储 SQL 语句通常会使代码更易于阅读和维护。

示例：

// 创建一个Statement对象，用于执行SQL语句  
Statement stmt = connection.createStatement();  
  
// 使用executeUpdate方法执行一个INSERT SQL语句，将'value1'和'value2'分别插入到table_name表的column1和column2列中
// executeUpdate方法受该 INSERT SQL语句影响的行数（在这个例子中，应该是 1，除非有某种错误或约束阻止了插入）
int rowsAffected = stmt.executeUpdate("INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')");  
  
// 打印此次INSERT操作影响的行数  
System.out.println("Rows affected: " + rowsAffected);

executeQuery 方法

executeQuery 方法用于向数据库发送查询语句（如 SELECT），并返回一个 ResultSet 对象，该对象包含查询结果。

示例：

// 创建一个Statement对象，用于执行SQL语句  
Statement stmt = connection.createStatement();  
  
// 使用Statement对象执行查询语句，并返回查询结果的ResultSet对象  
ResultSet rs = stmt.executeQuery("SELECT * FROM tablename");  
  
// 遍历ResultSet对象，处理查询结果的每一行数据  
while (rs.next()) {  
    // 获取名为"column1"的列的值，并转换为String类型  
    String column1Value = rs.getString("column1");  
    // 获取名为"column2"的列的值，并转换为String类型  
    String column2Value = rs.getString("column2");  
      
    // ... 在这里可以添加更多代码来处理每一行的数据，比如输出、计算等  
      
    // 示例：输出查询到的数据  
    System.out.println("Column1: " + column1Value + ", Column2: " + column2Value);  
}  
  
// 关闭ResultSet对象，释放相关资源  
rs.close();
  • 在执行数据库操作时，应注意处理可能出现的 SQLException，这是 JDBC 操作中常见的异常类型。

SQL注入

SQL注入（SQL Injection）是一种常见的网络攻击手段，攻击者通过在应用程序的输入字段中插入或“注入”恶意的SQL代码，从而操纵原本的应用程序所执行的SQL语句，达到非法获取、篡改或删除数据库中数据的目的。

import com.kuang.lesson02.utils.JdbcUtils; // 导入JdbcUtils工具类，用于获取数据库连接等操作
import java.sql.Connection; // 导入Connection接口，代表一个数据库连接
import java.sql.ResultSet; // 导入ResultSet接口，用于处理SQL查询结果集
import java.sql.SQLException; // 导入SQLException类，处理SQL异常
import java.sql.Statement; // 导入Statement接口，用于执行SQL语句

// SQL注入演示类
public class SQL注入 {

    public static void main(String[] args) {
        // 调用login方法，传入用户名和密码进行登录验证
        login("blue", "123456");
    }

    // 登录业务方法
    public static void login(String username, String password) {
        Connection conn = null; // 声明Connection对象，用于数据库连接，初始化为null
        Statement st = null; // 声明Statement对象，用于执行SQL语句，初始化为null
        ResultSet rs = null; // 声明ResultSet对象，用于处理查询结果，初始化为null

        try {
            // 调用JdbcUtils的getConnection方法获取数据库连接
            conn = JdbcUtils.getConnection();
            // 通过连接对象创建Statement对象，用于执行SQL语句
            st = conn.createStatement();
            // 拼接SQL查询语句，存在SQL注入风险
            String sql = "select * from users where `NAME`= '" + username + "' AND `password`='" + password + "'";

            // 执行查询语句，返回结果集
            rs = st.executeQuery(sql);

            // 遍历结果集，如果结果集中有数据，则打印用户名和密码
            if (rs.next()) {
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }

        } catch (SQLException throwables) {
            // 捕获并打印SQLException异常信息
            throwables.printStackTrace();
        } finally {
            // 释放资源，无论是否发生异常，finally块中的代码都会执行
            JdbcUtils.release(conn, st, rs);
        }
    }
}

注意：这个代码示例存在严重的SQL注入风险，因为它直接将用户输入拼接到SQL查询语句中。在真实的应用程序中，应该使用PreparedStatement来避免SQL注入攻击。

PreparedStatement 对象

Preparedstatement 的 SQL注入的本质：把传递进来的参数当做字符，，并自动处理可能存在的转义字符

import com.kuang.lesson02.utils.JdbcUtils; // 导入JdbcUtils工具类，用于获取数据库连接等  
import java.sql.Connection; // 导入Java SQL的Connection接口，代表数据库连接  
import java.sql.PreparedStatement; // 导入Java SQL的PreparedStatement接口，用于执行预编译的SQL语句 
import java.sql.SQLException; // 导入Java SQL的异常类，用于处理SQL相关的异常  
import java.util.Date; // 导入Java的Date类，用于获取当前日期和时间  
  
public class TextInsert { // 定义一个名为TextInsert的公共类  
  
    public static void main(String[] args) { // 主函数，程序的入口点  
  
        Connection conn = null; // 声明一个Connection类型的变量conn，并初始化为null  
        PreparedStatement st = null; // 声明一个PreparedStatement类型的变量st，并初始化为null  
  
        //使用 try-catch-finally 结构来执行数据库操作，并处理可能出现的异常
        try {  
            conn = JdbcUtils.getConnection(); // 通过JdbcUtils工具类获取数据库连接，并赋值给conn  
  
            // 定义一个SQL插入语句，使用 ?作为参数占位符  
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";  
            st = conn.prepareStatement(sql); // 使用conn的prepareStatement方法预编译SQL语句，并赋值给st  
  
            // 使用`PreparedStatement`的 setXXX 方法来为预编译的SQL语句中的占位符设置具体的参数值  
            st.setInt(1,4); // 设置第一个占位符的值为整数4  
            st.setString(2,"blue"); // 设置第二个占位符的值为字符串"blue"  
            st.setString(3,"123456"); // 设置第三个占位符的值为字符串"123456"  
            st.setString(4,"24736743@qq.com"); // 设置第四个占位符的值为字符串"24736743@qq.com"  
  
            // 设置第五个占位符的值为当前日期（注意：java.sql.Date只包含日期部分，不包含时间部分）  
            // java.util.Date表示Java中的日期和时间，而java.sql.Date是Java SQL中用于数据库操作的日期类型  
            //当需要将Java中的日期对象插入到SQL数据库的DATE字段中时，需要将 java.util.Date 转换为 java.sql.Date 。这是因为JDBC期望使用 java.sql.Date 来与SQL DATE字段交互。
            st.setDate(5,new java.sql.Date(new Date().getTime()));  
  
            // 执行插入操作，并返回受影响的行数  
            int i = st.executeUpdate();  
            if (i>0){  
                System.out.println("插入成功！"); // 如果受影响的行数大于0，则输出插入成功的消息  
            }  
  
        } catch (SQLException throwables) {  
            // 如果在执行SQL语句的过程中出现异常，则捕获该异常并输出异常信息  
            throwables.printStackTrace();  
        } finally {  
            // 无论是否出现异常，都执行finally块中的代码，用于释放数据库资源  
            JdbcUtils.release(conn,st,null); // 调用JdbcUtils的release方法释放数据库连接和PreparedStatement对象  
        }  
    }  
}

代码中的login方法演示了如何正确地使用PreparedStatement来防止SQL注入攻击。当尝试使用SQL注入测试字符串登录时，由于PreparedStatement的作用，注入攻击被有效地阻止。PreparedStatement能够确保用户输入被正确地转义和处理，不会被解释为SQL代码的一部分，从而避免了潜在的安全风险。

防止SQL注入

import com.kuang.lesson02.utils.JdbcUtils;  
import java.sql.*;  
  
// SQL注入演示类  
public class SQL注入 {  
  
    public static void main(String[] args) {  
        // 正常登录，用户名和密码正确时能够登录成功  
        login("lisi", "123456");  
  
        // SQL注入攻击尝试，但由于使用了PreparedStatement，这种注入方式会被防止  
        // 此处传入的是SQL注入测试字符串，目的是尝试绕过验证，但由于PreparedStatement的使用，它不会成功  
        login("'' or 1=1", "'' or 1=1");  
    }  
  
    // 登录业务方法  
    public static void login(String username, String password) {  
        Connection conn = null; // 数据库连接对象  
        PreparedStatement st = null; // PreparedStatement对象，用于执行预编译的SQL语句  
        ResultSet rs = null; // 结果集对象，用于存储查询结果  
  
        try {  
            conn = JdbcUtils.getConnection(); // 获取数据库连接  
  
            // 使用PreparedStatement防止SQL注入  
            // PreparedStatement会将传递进来的参数当做纯字符处理，并自动处理可能存在的转义字符  
            // 即便传入的参数中有特殊字符（如单引号），也会被正确地处理，不会被解释为SQL语句的一部分  
            String sql = "select * from users where `NAME`=? and `PASSWORD`=?"; // SQL查询语句，其中?是参数占位符  
  
            st = conn.prepareStatement(sql); // 创建PreparedStatement对象  
            st.setString(1, username); // 设置第一个占位符的值为用户名  
            st.setString(2, password); // 设置第二个占位符的值为密码  
  
            rs = st.executeQuery(); // 执行查询，并获取结果集  
            while (rs.next()) { // 遍历结果集  
                System.out.println(rs.getString("NAME")); // 打印用户名  
                System.out.println(rs.getString("PASSWORD")); // 打印密码  
            }  
  
        } catch (SQLException throwables) { // 捕获并处理SQLException异常  
            throwables.printStackTrace(); // 打印异常堆栈信息  
        } finally {  
            JdbcUtils.release(conn, st, rs); // 释放数据库连接、PreparedStatement对象和结果集对象  
        }  
    }  
}

代码中 login方法 是一个模拟用户登录过程的业务方法，它接受两个参数：username（用户名）和password（密码）。该方法的主要目的是验证给定的用户名和密码是否与数据库中存储的信息匹配。

代码中的login方法演示了如何正确地使用PreparedStatement来防止SQL注入攻击。当尝试使用SQL注入测试字符串登录时，由于PreparedStatement的作用，注入攻击被有效地阻止。PreparedStatement能够确保用户输入被正确地转义和处理，不会被解释为SQL代码的一部分，从而避免了潜在的安全风险。

创建数据库连接与语句对象的代码 在 JDBC-第一个JDBC程序-创建测试数据库-java类（第二个程序） 与 JDBC-PreparedStatement 对象（第一个程序）中的区别

第一个程序中的Connection conn = null; PreparedStatement st = null;与第二个程序中的Connection conn = DriverManager.getConnection(url, name, password); Statement st = conn.createStatement();之间的主要区别在于对象的初始化和赋值时机。

  1. 第一个程序中的代码：

    Connection conn = null;
PreparedStatement st = null;

    在第一个程序中，conn和st被声明并初始化为null。这通常是一个好的编程实践，因为它可以避免在对象被实际赋值之前意外地使用它们（这可能导致NullPointerException）。然而，在这两行代码之后，你通常会在某个地方（可能是在一个try块中）看到对conn和st的实际赋值操作。

  2. 第二个程序中的代码：

    Connection conn = DriverManager.getConnection(url, name, password);
Statement st = conn.createStatement();

    在第二个程序中，conn对象是通过调用DriverManager.getConnection方法立即初始化的，该方法使用提供的数据库URL、用户名和密码来建立数据库连接。一旦连接成功，就使用conn.createStatement方法创建一个Statement对象，用于执行SQL语句。

两者的主要区别如下：

  • 初始化时机：在第一个程序中，对象被声明并初始化为null，稍后才进行实际赋值。在第二个程序中，对象在声明的同时就被初始化。

  • 使用类型：第一个程序声明了使用PreparedStatement，但实际上并没有展示其初始化代码。PreparedStatement通常用于执行带参数的SQL语句，并且比Statement更安全、更高效。第二个程序则使用了Statement，这是执行不带参数或简单SQL语句的方式，但不如PreparedStatement安全和高效。

  • 错误处理：在第二个程序中，如果DriverManager.getConnection或conn.createStatement抛出异常（例如SQLException），那么这些异常需要被捕获并处理。第一个程序没有展示错误处理，但可以预期在实际应用中这些操作会放在try-catch块中，并包含相应的异常处理逻辑。

在实践中，通常推荐的做法是：

  • 初始化对象为null（或者根本不要声明它们，直接在try块中初始化），以避免在它们被赋值之前被使用。
  • 使用try-with-resources语句（如果Java版本支持）或try-catch-finally块来确保资源（如Connection和Statement对象）在使用后被正确关闭，即使在发生异常的情况下也是如此。
  • 优先使用PreparedStatement而不是Statement，以提高安全性和性能。

从一个工具类中获取一个数据库连接

  • 在第一个程序中，直接写成conn = 工具类类名.getConnection();
  • 在第二个程序中，Connection [Connection对象名]= DriverManager.getConnection(url,name,password); 改为 Connection [Connection对象名] = 工具类类名.getConnection();

使用IDEA连接数据库

在IntelliJ IDEA中连接数据库的具体点击操作如下：

  1. 首先，确保IDEA右侧有Database窗口。如果没有，可以点击View -> Tool Windows -> Database来打开它。
  2. 在Database窗口中，点击右侧的“+”号。
  3. 在弹出的菜单中，选择“Data Source”，然后选择想要连接的数据库类型，比如MySQL。 Java MySQL
  4. 在接下来的界面中，填写数据库的相关信息，如主机名、端口、数据库名、用户名和密码等。这些信息取决于 要连接的数据库配置。 Java MySQLJava MySQL
  5. 点击“Test Connection”来测试连接是否成功。如果成功，会显示一个成功的提示（显示绿色的successful）。
  6. 如果测试成功，则依次点击“Apply”、“OK”或来保存设置并连接数据库。
  7. 等待数据库加载完毕，显示目录 完成上述步骤后，就成功地在IDEA中连接了数据库。可以在Database窗口中看到你连接的数据库以及数据库下的所有表，还可以进行各种数据库操作，如查询、更新、删除等。

以下是一些操作的具体说明：

选择数据库 Java MySQL

查看数据库和表

  1. 查看数据库结构：连接成功后，点击数据库名称，右侧会展开显示该数据库下的所有表。
  2. 查看表结构：在右侧展开的表列表中，点击你感兴趣的表名，底部或右侧的标签页中显示该表的结构，包括列名、数据类型等信息。
  3. 查看数据库内容：双击数据库名称
  4. 查看表内容：双击表名称，会打开一个新的标签页，显示该表的所有数据行，允许查看、编辑或添加删除记录 Java MySQL

执行更新、删除等操作

  • 直接在表上操作：双击表名称打开新的标签页显示该表的内容，双击字段值直接输入新值进行修改，单值修改完毕 → 点击空格 → 点击提交图标 Java MySQL
  • 编写SQL语句操作 Java MySQL

错误处理 Java MySQL

  1. 导入/导出数据：
    • 右键点击表名，选择“Import Data”或“Export Data”来导入或导出数据。

注意

  • 执行更新、删除等操作时务必小心，确保你的操作是正确的，并且已经做好了数据备份。
  • IDEA的Database插件可能随着版本更新而有所变化，具体操作可能会有所不同。如果遇到问题，建议查阅IDEA的官方文档或相关教程。
  • 在连接数据库之前，确保IDEA已经安装了相应数据库的插件，并且本地或远程数据库已经正确配置并可以访问。

JDBC操作事务

流程 1.开启事务 conn.setAutoCommit(false); 2.一组业务执行完毕，提交事务 3.可以在catch语句中定义回滚，但默认失败会回滚

import com.kuang.lesson02.utils.JdbcUtils; // 导入JdbcUtils工具类，用于获取数据库连接  
import java.sql.Connection;                // 导入连接数据库使用的Connection接口  
import java.sql.PreparedStatement;         // 导入PreparedStatement接口，用于执行SQL语句  
import java.sql.ResultSet;                 // 导入ResultSet接口，用于处理查询结果  
import java.sql.SQLException;              // 导入SQLException类，用于处理数据库异常  
  
public class TestTransaction1 {  
    public static void main(String[] args) {  
        Connection conn = null; // 定义Connection对象，用于连接数据库  
        PreparedStatement st = null; // 定义PreparedStatement对象，用于执行SQL语句  
        ResultSet rs = null; // 定义ResultSet对象，用于处理查询结果（这里虽然未用到查询，但常规写法中会初始化）  
  
        try {  
            // 获取数据库连接  
            conn = 工具类类名.getConnection();  
  
            // 关闭数据库的自动提交功能，从而开启事务  
            conn.setAutoCommit(false);  // 设置自动提交为false，意味着之后的数据库操作会处于事务中  
  
            // SQL语句1：从名为'A'的账户中减去100元  
            String sql1 = "update account set money = money-100 where name = 'A'";  
            // 创建PreparedStatement对象并执行SQL语句1  
            st = conn.prepareStatement(sql1);  
            st.executeUpdate(); // 执行更新操作，不返回结果集  
  
            // SQL语句2：给名为'B'的账户加上100元  
            String sql2 = "update account set money = money+100 where name = 'B'";  
            // 创建新的PreparedStatement对象并执行SQL语句2  
            st = conn.prepareStatement(sql2);  
            st.executeUpdate(); // 执行更新操作，不返回结果集  
  
            // 所有业务操作完成，提交事务  
            conn.commit(); // 提交事务，使所有之前的数据库操作生效  
            System.out.println("成功"); // 输出成功信息  
  
        // 如果在执行数据库操作时发生异常  
        } catch (SQLException e) {  
            try {  
                // 如果有异常，则回滚事务，撤销之前的所有数据库操作  
                conn.rollback(); // 回滚事务  
            } catch (SQLException e1) {  
                // 如果回滚操作也发生异常，则打印异常堆栈信息  
                e1.printStackTrace();  
            }  
            // 打印原始异常堆栈信息  
            e.printStackTrace();  
        } finally {  
            // 无论是否发生异常，最后都要释放数据库资源  
            工具类类名.release(conn, st, rs); // 释放数据库连接、PreparedStatement对象和ResultSet对象  
        }  
    }  
}

数据库连接池

数据库连接 → 执行完毕 →释放连接 → 释放，这十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

连接池 是一个预先创建并维护一组数据库连接的容器。当应用程序需要连接数据库时，它会从连接池中获取一个已经存在的连接，而不是创建一个新的连接。当连接使用完毕后，它会被归还给连接池，而不是被销毁。这样可以避免频繁地创建和销毁连接，从而提高性能。

  • 最小连接数 是指连接池中始终保持的最低连接数量。最大连接数 是指连接池可以容纳的最大连接数量，超过此值则排队等待。
  • 等待超时（ms） 是指当连接池中的连接都被占用，并且新的连接请求需要等待时，等待的最长时间。如果在等待超时设置值内有连接被释放，则新的连接请求可以获得一个连接；如果超过这个时间仍然没有连接被释放，则新的连接请求会失败。

编写连接池：实现DataSource接口

开源数据源实现

  • 对于DBCP，你需要引入commons-dbcp和commons-pool的jar包。
  • 对于C3P0，需要c3p0和mchange-commons-java的jar包。
  • 对于Druid，直接引入Druid的jar包。

使用连接池DBCP创建工具类

  1. 从 Maven 中央仓库或其他来源下载所需的 jar 包。

  2. 将下载的JAR包件复制到 lib 目录中。 在电脑文件在找到已下载的JAR文件 → 复制文件 → 右键点击lib目录 → “粘贴”（或 Ctrl+V）

    Java MySQL

  3. 右键击 lib 目录 → Add as library → 点击弹出窗口的 OK

  4. 在类目录(src)下加入dbcp的配置文件：dbcpconfig.properties 右键击 src 目录 → 鼠标光标移到 New → 点击右侧目录的 File → 在弹出窗口输入dbcpconfig.properties → 点击弹出窗口的 OK → 把dbcp的配置文件复制粘贴到出现的空白页中

    在 IntelliJ IDEA 或其他 Java 集成开发环境（IDE）中，当涉及到与 MySQL 或其他数据库的连接时，.properties 文件通常用于存储数据库连接参数。

    dbcpconfig.properties的配置内容如下：

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:数据库类型://主机名:端口号/数据库名      //例如: jdbc:mysql://localhost:3306/jdbcstudy
username=数据库连接的用户名                      //例如: 默认的root
password=数据库连接的密码

//以下内容可以全部删去或自定义
#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
  1. 创建并配置工具类
    1. 创建一个新的包 右键点击与 lib 同一级别目录的 src → 鼠标光标移到 New → 点击右侧目录的 Package → 在弹出窗口输入[自定义名]
    2. 创建了一个名为 utils 的子包（存放工具类）。 右键点击 src 目录下的[自定义名] → 鼠标光标移到 New → 点击右侧目录的 Package → 在弹出窗口输入 utils
    • 这些工具类可能包含一些静态方法，用于执行一些常见的任务，如数据库连接、文件操作等。当需要在多个地方重复执行数据库连接、查询、更新等操作时，可以创建一些工具类来简化这些操作。这些工具类可以放在 utils 包中。
    1. 在 utils 包内创建一个 Java 类（工具类） 右键点击 src 目录下的 utils → 鼠标光标移到 New → 点击右侧目录的 Java Class → 在弹出窗口输入 JdbcUtils_DBCP
    • 这个类可能用于封装与数据库连接相关的操作JdbcUtils_DBCP 类可能会包含一些静态方法，用于获取数据库连接、关闭连接、执行查询等。这样，其他代码就可以通过调用这些静态方法来简化数据库操作，而不需要自己处理连接细节。
    1. 由于数据源的存在，工具类JdbcUtils_DBCP 类中负责连接数据库（即driver,url,username,password部分）、加载驱动(Class.forName();)、获取连接(DriverManager.getConnection(url,name,password);) 的代码可以省略删去
    2. 创建数据源 在静态代码块（static{}）外，定义一个类范围内可见的数据源变量。代码实现： private static DataSoure DataSoure变量名 = null; 在加载dbcpconfig.properties配置文件的代码（即InputStream、Properties、properties.load()）后，创建数据源。代码实现： DataSoure变量名 = BasicDataSourceFactory.createDataSource(Properties变量名);
    3. 从数据源中获取数据库连接 代码实现：（在静态代码块后） public static Connection getConnection() throws SQLException{ return DataSoure变量名.getConnection(); }

示例

//下面是一个Java导入语句，它用于引入数据库连接池库 Apache Commons DBCP 2库中的BasicDataSource类
import org.apache.commons.dbcp2.BasicDataSource;

//数据库连接工具类
public class JdbcDBCP {
-------------
    /**
     * 在java中，编写数据库连接池需实现java.sql.DataSource 接口，每一种数据库连接池都是DataSource接口的实现
     * DBCP连接池就是java.sql.DataSource接口的一个具体实现
     */
-------------
    //定义一个类范围内可见的数据源变量
    private static DataSoure dataSource = null;
    
    //在静态代码块中创建数据库连接池
    static{
        try{
            //加载dbcpconfig.properties配置文件
            InputStream in = JdbcDBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);           //in是InputStream对象
            //创建数据源，Factory工厂模式，用于创建对象，createDataSource()方法，用于加载一个Properties流，在本例中即为 properties，返回一个数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        }catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }
    
   //从数据源中获取数据库连接
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }
    
 /**
   * 释放资源，释放的资源包括Connection数据库连接对象，负责执行SQL命 
   *令的Statement对象，存储查询结果的ResultSet对象
   */
    public static void release(Connection conn,Statement st,ResultSet rs){
        if(rs!=null){
            try{
                //关闭存储查询结果的ResultSet对象
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(st!=null){
            try{
                //关闭负责执行SQL命令的Statement对象
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if(conn!=null){
            try{
                //将Connection连接对象还给数据库连接池
                conn.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

使用连接池C3PO创建工具类

与 使用连接池DBCP创建工具类 操作类似，不同的是：

  • 导入import com.mchange.v2.c3p0.ComboPooledDataSource; ComboPooledDataSource 是C3P0数据库连接池库中的一个核心类，它提供了管理和复用数据库连接的功能。
  • 配置文件File命名为 c3p0-config.xml
  • c3p0-config.xml的配置信息内容如下：
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>       //缺省（默认）配置
    <!-- 默认数据库连接池配置 -->  
    <default-config>    
        <!-- JDBC连接字符串，用于连接MySQL数据库 -->  
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>    
        <!-- 数据库驱动类名 -->  
        <property name="driverClass">com.mysql.jdbc.Driver</property>    
        <!-- 数据库用户名 -->  
        <property name="user">root</property>    
        <!-- 数据库密码 -->  
        <property name="password">123456</property>    
    
        <!-- 连接池初始化时创建的连接数 -->  
        <property name="initialPoolSize">10</property>    
        <!-- 连接池每次需要获取连接时增加的连接数 -->  
        <property name="acquireIncrement">5</property>  
        <!-- 连接池维护的最小连接数 -->  
        <property name="minPoolSize">5</property>    
        <!-- 连接池允许的最大连接数 -->  
        <property name="maxPoolSize">20</property>  
    </default-config>  >  
  
<!-- 名为MySQL的数据库连接池配置-->
    <name-config name="MySQL">  
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=ture&amp;characterEncoding=utf8&amp;useSSL=true</property>  
        <property name="driverClass">com.mysql.jdbc.Driver</property>  
        <property name="user">root</property>  
        <property name="password">123456</property>  
    
        <property name="initialPoolSize">10</property>  
        <property name="acquireIncrement">5</property>
        <property name="minPoolSize">5</property>  
        <property name="maxPoolSize">20</property>
    </default-config>  
 
    还可以命名 ORACLE 配置：
    把<name-config name="MySQL">改为<name-config name="ORACLE">
    <property name="jdbcUrl"></property> 中改为 ORACLE 配置
    
</c3p0-config>

  • 创建数据源，代码实现： 配置文件写法（使用ComboPooledDataSource类创建） ComboPooledDataSource InputStream对象名 = new ComboPooledDataSource() —— 使用默认的数据库连接池配置 ComboPooledDataSource InputStream对象名 = new ComboPooledDataSource("配置名") —— 使用名为配置名的数据库连接池配置 或 dataSource = new ComboPooledDataSource(); dataSource = new ComboPooledDataSource("配置名");

    代码写法

try {  
    dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");  
    dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/your_database?useSSL=false&serverTimezone=UTC");  
    dataSource.setUser("your_username");  
    dataSource.setPassword("your_password");  
              
    // 其他属性设置...  
    dataSource.setInitialPoolSize(3);  
    dataSource.setMinPoolSize(3);  
    dataSource.setMaxPoolSize(15);  
    dataSource.setMaxIdleTime(30);  
} catch (PropertyVetoException e) {  
    e.printStackTrace();  
}
转载自:https://juejin.cn/post/7337207433867640859
评论
请登录