MySQL 部分重点知识篇

一、数据库对象

1. 主键

• 定义 ：主键是用于唯一标识表中每一行记录的字段或字段组合。它具有唯一性和非空性特点。

• 作用 ：确保数据的完整性，便于数据的查询和管理。

• 示例 ：在学生信息表中，学号可以作为主键，因为每个学生的学号是唯一的。

创建包含主键的表的 SQL 示例 ：

CREATE TABLE student (id INT PRIMARY KEY,name VARCHAR(100),age INT
);

2. 外键

• 定义 ：外键是一个表中的字段，其值引用另一个表中的主键值，用于建立两个表之间的关联关系。

• 作用 ：维护数据的一致性和完整性，通过外键约束，确保相关数据之间的对应关系。

• 示例 ：在订单表中，客户 ID 可以作为外键，引用客户信息表中的主键客户 ID，从而关联订单和客户信息。

创建包含外键的表的 SQL 示例 ：

CREATE TABLE order (order_id INT PRIMARY KEY,customer_id INT,order_date DATE,FOREIGN KEY (customer_id) REFERENCES customer (id)
);

3. 触发器

• 定义 ：触发器是数据库中的一种特殊存储过程，当对表进行插入、更新或删除操作时，会自动触发执行。

• 作用 ：用于实现复杂的业务逻辑、数据验证、审计等功能。

• 示例 ：当向员工工资表中插入新记录时，可以触发一个触发器自动计算工资总额并更新到相应的统计表中。

4. 存储过程

• 定义 ：存储过程是一组预编译的 SQL 语句，存储在数据库中，可通过指定名称调用执行。

• 作用 ：提高代码复用性、执行效率，减少网络传输，便于维护和安全管理。

• 示例 ：创建一个存储过程用于查询指定学生的成绩信息，调用该存储过程时只需传入学生 ID 即可获取成绩数据。

二、数据操作语句

1. DROP、DELETE、TRUNCATE

操作类型	作用	特点
DROP	删除表结构及数据，删除后无法通过 ROLLBACK 操作恢复	用于删除整个表，操作后表不存在
DELETE	删除表中的指定数据行，支持 WHERE 子句进行条件筛选，可结合事务回滚	用于删除指定行，操作后表结构仍存在
TRUNCATE	清空表中所有数据，速度快，不记录删除的每行数据日志，无法回滚	用于快速删除表中所有行，操作后表结构仍存在，相当于 DELETE 语句删除所有行

2. 非关系型数据库与关系型数据库对比

数据库类型	特点	示例
关系型数据库	采用关系模型来组织和存储数据，以表格形式呈现，具有固定的模式结构，支持 SQL 查询语言，强调数据的一致性和完整性，适合处理结构化数据。	MySQL、Oracle、SQL Server 等
非关系型数据库	不采用关系模型来组织和存储数据，常见的有键值存储、文档存储、列族存储、图数据库等类型。具有灵活的数据结构、高可扩展性、高性能读写等优点，适用于处理大规模、复杂多变的数据类型。	MongoDB（文档型）、Redis（键值型）、HBase（列族型）、Neo4j（图数据库型）

3. 非关系型数据库

• 定义 ：非关系型数据库是指不同于传统关系型数据库的数据存储系统，它不采用关系模型来组织和存储数据，常见的有键值存储、文档存储、列族存储、图数据库等类型。

• 特点 ：具有灵活的数据结构、高可扩展性、高性能读写等优点，适用于处理大规模、复杂多变的数据类型。

• 示例 ：MongoDB 是一种文档型非关系型数据库，它使用 JSON 格式的文档来存储数据，便于处理半结构化数据，如在社交媒体应用中存储用户发布的各种类型的内容（文字、图片、视频等）。

三、多表联查

1. 内联结（INNER JOIN）

• 定义 ：内联结是基于两个或多个表之间的指定条件进行连接，只返回满足连接条件的记录行。

• 示例 ：查询学生信息表和成绩表，获取成绩大于 80 分的学生姓名和对应成绩。

SELECT student.name, score.grade
FROM student
INNER JOIN score ON student.id = score.student_id
WHERE score.grade > 80;

2. 左联结（LEFT JOIN）

• 定义 ：左联结返回左表中的所有记录以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则结果中右表的字段将为 NULL。

• 示例 ：查询所有学生的信息以及他们的成绩，即使某些学生没有成绩记录。

SELECT student.name, score.grade
FROM student
LEFT JOIN score ON student.id = score.student_id;

3. 右联结（RIGHT JOIN）

• 定义 ：右联结与左联结相反，返回右表中的所有记录以及左表中满足连接条件的记录。如果左表中没有匹配的记录，左表的字段将为 NULL。

• 示例 ：查询所有成绩记录以及对应的学生信息，即使某些成绩没有关联的学生记录。

SELECT student.name, score.grade
FROM student
RIGHT JOIN score ON student.id = score.student_id;

4. 外连接（OUTER JOIN）

• 定义 ：外连接包括左外连接、右外连接和全外连接（返回左右表中所有记录，匹配则显示对应字段，不匹配则为 NULL）。它是对内连接的扩展，用于获取更全面的数据视图。

• 示例 ：使用全外连接查询学生信息和成绩信息，无论是否有关联记录。

SELECT student.name, score.grade
FROM student
FULL OUTER JOIN score ON student.id = score.student_id;

5. 交叉连接（CROSS JOIN）

• 定义 ：交叉连接返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行组合。

• 示例 ：查询两个表，生成所有可能的部门与员工的组合（假设存在部门表和员工表）。

SELECT department.dept_name, employee.name
FROM department
CROSS JOIN employee;

6. 笛卡尔积

• 定义 ：笛卡尔积是数学中的概念，在数据库中指两个或多个表进行连接时，没有指定连接条件或者连接条件不正确，导致返回的结果集是参与连接的表的行数相乘的所有组合。

• 问题 ：通常情况下笛卡尔积会生成大量无意义的数据组合，会对查询性能和结果准确性产生负面影响，因此在实际开发中要谨慎处理，确保正确设置连接条件。

四、数据类型

1.VARCHAR 和 CHAR

数据类型	特点	使用场景
CHAR	定长字符串，存储速度相对较快，因为其固定长度便于数据库进行快速定位和存储操作。	适用于存储长度固定的数据，如身份证号码（18 位）、省市区代码等。
VARCHAR	变长字符串，存储空间使用更灵活，根据实际数据长度动态分配存储空间。	适用于存储长度不固定的数据，如姓名、地址、产品描述等。

五、聚合函数

1.COUNT使用的区别

函数形式	作用与特点
COUNT(*)	统计表中所有行的数量，包括 NULL 值行。它是最通用的统计方式，用于获取表中总行数。
COUNT(1)	与 COUNT() 功能类似，都是统计行数。在某些数据库实现中，COUNT(1) 可能会比 COUNT() 稍微高效，因为数字类型的计算可能比其他类型更简单，但在大多数场景下两者的性能差异可以忽略不计。
COUNT(字段)	统计指定字段非 NULL 值的行数。用于获取表中某个特定字段有实际值的记录数量，可用于数据分析中统计某个属性的有效数据量。

六、索引

1.什么是索引

• 定义 ：索引是数据库中用于提高数据查询速度的结构，它通过创建额外的数据结构来快速定位和访问表中的数据行。

• 作用 ：加快数据检索速度，减少磁盘 I/O 操作，提高数据库的整体性能。

2.使用索引的目的

• 提高查询效率 ：对于大型数据表，索引可以显著减少查询时间，特别是在需要频繁查询某些字段时。

• 优化排序和分组操作 ：索引可以辅助排序和分组操作，减少排序和分组过程中的数据比较和移动操作。

3.索引的分类

按照数据结构分类：

索引类型	数据结构特点	适用场景
B-Tree 索引	基于平衡树结构，具有良好的查找、插入和删除性能。数据按顺序存储，支持范围查询和排序操作。	适用于需要进行范围查询、排序操作或频繁插入删除的场景，如用户信息表中的姓名、年龄字段索引。
Hash 索引	基于哈希表结构，通过哈希函数快速定位数据，具有极快的查找速度。但不支持范围查询和排序。	适用于对等值查询性能要求极高的场景，如用户登录时的用户名或邮箱字段索引。
全文索引	用于对文本数据进行索引，支持全文检索操作。能够快速定位包含特定关键词的文本数据，在搜索引擎、文档管理系统等场景中广泛应用。	适用于需要对大量文本数据进行内容搜索的场景，如文章内容搜索、日志分析等。
空间索引	专门用于地理空间数据的索引，能够高效地处理地理坐标、几何形状等空间数据的查询和分析。	适用于地理信息系统（GIS）、位置服务等需要处理地理空间数据的应用场景。

按照逻辑结构分类：

索引类型	特点
普通索引	最基本的索引类型，不具有唯一性约束，用于提高查询效率。
唯一索引	索引列的值必须唯一，用于确保数据的唯一性，同时提高查询速度。在插入或更新数据时，数据库会自动检查唯一性约束。

4.创建索引

• 语法 ：

  • 创建普通索引：CREATE INDEX 索引名 ON 表名 (列名);

  • 创建唯一索引：CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

• 示例 ：为学生表的学号列创建唯一索引，确保学号的唯一性并提高查询速度。

CREATE UNIQUE INDEX idx_stu_no ON student (stu_no);