1. SQL概述
1.1 SQL的特点
- 具有综合统一性，不同的数据库支持的SQL稍有不同
- 非过程化语言
- 语言简捷，用户易接受
- 以一种语言结构提供两种使用方式
1.2 SQL语言的组成
- DDL（数据定义语言）
- DML（数据操纵语言）
- DCL（数据控制语言）
- DQL（数据查询语言）
2. MySQL的基本操作
2.1 DDL
-
常用操作
功能 SQL 查看所有的数据据库 SHOW DATABASES; 创建数据库 CREATE DABASE [IF EXISTS] mydb1 [CHARSET=utf8]; 选择要操作的数据库 USE mydb1; 删除数据库 DROP DATABASE [IF EXISTS] mydb1; 修改数据库编码 ALTER DATABASE mydb1 CHARSET SET utf8; 查当前数据库的所有表 SHOW TABLES; 查看指定表的创建语句 SHOW CREATE TABLE 表名; 查看表结构 DESC 表名; 删除表 DROP TABLE 表名; 修改表添加列 ALTER TABLE 表名 ADD 列名 类型(长度) [约束]； 修改列明和类型 ALTER TABLE 表名 change 旧列名 新列名 类型(长度) [约束]; 修改表删除列 ALTER TABLE 表名 DROP 列名; 修改表名 RENAME TABLE 表名 TO 新表名； -
数据类型
-
数值类型
类型 大小(字节) TINYINT 1 SMALLINT 2 MEDIUMINT 3 INT 4 BIGINT 8 FLOAT 4 DOUBLE 8 DECIMAL -
日期和时间类型
类型 格式 DATE YYYY-MM-DD TIME HH:MM:SS YEAR YYYY DATETIME YYYY-MM-DD HH:MM:SS TIMESTAMP YYYYMMDD HHMMSS -
字符串类型
类型 大小(字节) 用途 CHAR 0-255 定长字符串 VARCHAR 0-6535 变长 TINYBLOB 0-255 不超过255个字符的二进制字符串 TINYTEXT 0-255 短文本字符串 BLOB 0-65535 二进制形式的长文本数据 TEXT 0-65535 长文本数据 MEDIUMBLOB 0-16 777 215 二进制形式的中长度文本数据 MEDIUMTEXT 0-16 777 215 中等长度文本数据 LONGBLOB 0-4 294 967 295 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295 极大文本数据
-
2.2 DML
-
常用操作
功能 SQL 数据插入 INSERT INTO 表 [(列名1，列名2...)] VALUES(值1，值2...) 数据修改 UPDATE 表名 SET 字段名=值，字段名=值 ... [WHERE 条件]； 数据删除 DELECT FROM 表名 [WHERE 条件] -
约束constraint
- 主键约束PRIMARY KEY
- 自增长约束AUTO_INCREMENT
- 非空约束NOT NULL
- 唯一约束UNIQUE
- 默认约束DEFAULT
- 零填充约束ZEROFILL
- 外键约束（谨慎使用）
2.3 DQL
SELECT
[ALL|DISTINCT]
<目标列的表达式1> [别名],
<目标列的表达式2> [别名]...
FROM <表名|视图名> [别名]，<表名/视图名> [别名]...
[WHERE <条件表达式>]
[GROUP BY <列名>]
[HAVING <条件表达式>]
[ORDER BY <列表> [ASC|DESC]]
[LIMIT <数字或列表>];
3. 什么是实体关系模型（ER模型）
实体-关系模型（Entity-Relationship Model，简称ER模型）是一种用于数据库设计的概念工具。它描述了现实世界中各种实体以及它们之间的关系。在ER模型中，实体表示现实世界中的一个独立对象，可以是具体的物理对象或抽象的概念。关系表示实体之间的联系或连接方式，可以是一对一、一对多或多对多的关系。
4. 如何使用ER图进行数据库设计
使用ER图进行数据库设计通常包含以下步骤：
- 确定实体：识别出系统中存在的主要实体，并为每个实体确定一个适当的名称。
- 确定属性：为每个实体确定相应的属性，这些属性描述了实体的特征。
- 确定关系：确定实体之间的关系，包含一对一、一对多和多对多的关系。
- 绘制ER图：根据上述信息，使用矩形框表示实体，使用菱形框表示关系，并使用连线表示实体和关系之间的联系。
- 优化设计：通过检查ER图，消除冗余，确定主键和外键，并进行必要的优化。
5. MySQL数据库的约束条件
在MySQL数据库中，约束用于对表中的数据进行限制和约束，确保数据的完整性和一致性。MySQL支持以下几种约束：
5.1.主键约束（PIRMARY KEY）
- 主键约束用于唯一标识表中的记录。一个表只能有一个主键，且主键不能包含NULL值。
- 主键的值必须是唯一的，用于确保表中每条记录的唯一性。
- 在创建表或修改表结构时，可以给某个字段添加主键约束。
5.2. 唯一约束（UNIQUE）
- 唯一约束用于确保表中某个字段的取值唯一，同一个表可以有多个唯一约束。
- 唯一约束允许NULL值，但是在表中同一字段中只能有一个NULL值。
5.3. 非空约束（NOT NULL）
- 非空约束用于确保表中某个字段的值不能为NULL。
- 如果一个字段被指定为非空约束，则在插入或更新数据时，必须提供字段的非空值。
5.4. 外键约束(FOREIGN KEY)
- 外键约束用于确保表与表之间的关联完整性。
- 外键约束建立了两个表之间的关系，并指定了外键与其关联的主键之间的关系。
- 外键约束可以用于限制表之间的数据操作，如级联更新和级联删除。
5.5. 默认约束
- 默认约束用于指定字段在未显示插入值时的默认值。
- 默认约束在插入新记录时，如果没有为字段提供值，则使用默认值。
5.6. 检查约束
- 检查约束用于检查指定值是否满足check定义的值范围。
6. SQL注入和防御
SQL注入是一种常见的安全漏洞，它利用应用程序对用户输入数据的处理不当（未经过过滤和验证），使得恶意用户可以通过构造恶意的输入数据来破坏SQL语句的结构，执行恶意的SQL代码，从而绕过应用程序的安全机制，获取、修改或删除数据库中的数据。为防止SQL注入攻击，可以采取以下几种措施：
- 使用参数化查询（Prepared Statements）或存储过程（Stored Procedures）来执行SQL语句，在执行之前将用户输入作为参数传递，而不是将其直接拼接到SQL语句中。
- 对用户输入进行充分的过滤和验证，包括输入长度限制、类型检查、白名单过滤等，以过滤恶意代码和特殊字符。
- 最小化数据库账号的权限，限制数据库账号的能力，确保其只能执行所需的操作，避免攻击者通过注入获得对整个数据库的完全访问权限。
- 进行安全审计和漏洞扫描，及时发现和修复潜在的安全漏洞。
7. SELECT语句完整的执行顺序
SELECT <字段名>
FROM <表名>
JOIN <表名>
ON <连接条件>
WHERE <筛选条件>
GROUP BY <字段名>
HAVING <筛选条件>
UNION
ORDER BY <字段名>
LIMIT <限制行数>
- FROM子句组装来自不同数据源的数据，产生虚拟表1
- ON子句是JOIN的连接条件，符合连接条件的行数会被记录在虚拟表2
- JOIN子句中如果制定了LEFT JOIN，那么保留表中未匹配的行数就会作为外部行添加到虚拟表2中，产生虚拟表3；如果有多个JOIN连接，会重复执行步骤1-3，直到处理完所有表。
- WHERE子句基于指定的条件对虚拟表3进行筛选，符合条件的记录hi被插入到虚拟表4中
- GROUP BY子句根据子句中的列，对虚拟表2中的记录进行分组，产生虚拟表5
- 使用聚合函数进行计算
- 使用HAVING子句对虚拟表5进行过滤，符合条件的记录会被插入到虚拟表6中
- SELECT的字段选择指定的列，插入到虚拟表7中
- UNION连接的两个SELECT查询语句会重复执行步骤1-7，产生两个虚拟表7，UNION会将这些记录合并到虚拟表8中
- 使用ORDER BY对结果集虚拟表8进行排序，产生虚拟表9
- LIMIT取出指定的记录，返回结果集
8. 数据库连接池
数据库连接池是一种用于管理和维护数据库连接的技术，旨在提高数据库访问的性能和效率。它通过在应用程序和数据库之间建立一组预先分配的数据库连接，并且这些连接可以被重复使用，而不需要频繁地创建和销毁连接。
数据库连接池的主要优势包括：
- 提高性能：由于链接池中的连接已经被创建并准备好使用，可以避免频繁地创建和销毁连接地开销，从而提供更快地数据库访问速度。
- 优化资源利用：连接池可以管理连接的数量，根据应用程序的需求动态调整连接地数量，避免连接过多或不足地情况，从而优化数据库资源的利用。
- 连接可重用：连接池中地连接可以被多个线程或请求重复使用，避免每次请求都需要创建新连接的开销，提高数据库访问地效率。
- 连接管理和监控：连接池可以提供连接的管理和监控功能，包括连接的创建、验证、保持活动状态、回收和释放等，确保连接地可用性和稳定性。
9. 数据库三大范式
数据库设计凡是是一组规则和原则，用于规范化数据库模式，确保数据库地结构更加合理、灵活和高效。常用的数据库设计范式包括第一范式、第二范式、第三范式、BC范式等。
- 第一范式（1NF）：要求数据库表中的每个字段具有原子性，即每个字段不能再分解成更小的数据项。此外，每个字段必须有一个唯一的名称，确保表中没有重复的数据。
- 第二范式（2NF）：在满足1NF的基础上，要求表中的非主键字段必须完全依赖于完整主键，而不能依赖于部分主键。换句话说，非主键字段必须与完整主键相关，而不是与部分主键相关。
- 第三范式（3FN）:在满足2NF的基础上，要求表中的非主键字段之间不存在传递依赖关系。即非主键字段只能依赖于表中的主键字段，而不能依赖于其他非主键字段。
范式设计的原则是为了减少数据冗余、提高数据一致性和完整性，以及简化数据更新操作。范式的级别越高，数据库模式的结构越规范，但有时可能需要根据实际情况进行灵活处理，根据具体需求权衡范式和性能的关系。
10. 反范式设计
反范式化的主要思想是在设计数据库时引入冗余数据，以避免频繁的连接操作。这样可以提高查询性能，减少连接操作的开销。此外，反范式化还可以简化复杂的数据模型，使得查询和管理数据变得更加直观和高效。然而，反范式化也存在一些问题和风险，需要谨慎使用。增加冗余数据可能会导致数据不一致问题，因此必须确保在更新数据库时对冗余数据进行正确的处理。此外，反范式化还可以增加存储空间的需求，并且对数据模型的修改和维护会更加困难。
11. JDBC编程的基本步骤
JDBC（Java DataBase Connectivity）是一种用在Java应用程序和数据库之间进行连接和交互的API。JDBC的编程步骤如下：
- 加载数据库驱动程序
- 建立数据库连接
- 创建Statement
- 发送并执行SQL
- 处理SQL执行结果
- 关闭数据库连接