MYSQL“一课一得“

MySQL数据库概述

MySQL是一个流行的开源关系型数据库管理系统（RDBMS），它使用结构化查询语言（SQL）来管理数据。MySQL由于其稳定性、易用性、高性能和可扩展性，被广泛应用于各种应用场景，包括网站开发、数据仓库、数据分析等。

MySQL是一种广泛使用的关系型数据库管理系统，它基于SQL（结构化查询语言）进行数据操作和管理。在学习过程中，我深刻理解了以下几个核心知识点：

数据库设计：了解如何创建数据库和表，以及如何设计合理的数据库结构以满足实际业务需求。
数据类型：掌握MySQL中的各种数据类型，如整型、浮点型、日期时间型、字符串型等，并理解它们的存储方式和适用场景。
SQL语言：熟练运用SQL语言进行数据的增删改查（CRUD），包括SELECT、INSERT、UPDATE和DELETE等基本语句。
索引与优化：理解索引的作用和原理，学习如何通过创建索引来提高查询效率，并掌握一些基本的性能优化技巧。
事务管理：了解事务的概念、ACID特性以及如何在MySQL中使用事务来保证数据的一致性和完整性。

数据类型

MySQL支持多种数据类型，用于存储不同类型的数据。以下是一些常用的数据类型：

数值类型：如 INT, FLOAT, DOUBLE, DECIMAL 等。
日期和时间类型：如 DATE, TIME, DATETIME, TIMESTAMP 等。
字符串类型：如 CHAR, VARCHAR, TEXT, BLOB 等。

常用基础代码:

创建数据库 

-- 创建数据库
CREATE DATABASE my_database;

使用数据库 

-- 使用数据库
USE my_database;

创建表 

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
);

插入数据 

-- 插入数据
INSERT INTO employees (id, name, age, email) VALUES (1, 'John Doe', 30, 'john@example.com');

查询数据 

-- 查询数据
SELECT * FROM employees WHERE age > 25;

更新数据 

-- 更新数据
UPDATE employees SET age = 31 WHERE id = 1;

删除数据 

-- 删除数据
DELETE FROM employees WHERE id = 1;

数据完整性约束

MySQL支持多种数据完整性约束，以确保数据的准确性和可靠性。以下是一些常用的约束：

PRIMARY KEY：主键约束，确保表中每行数据的唯一性。

案例：创建一个 students 表，其中 student_id 列作为主键，确保每个学生的ID是唯一的。

-- 创建学生表，设置student_id为主键
CREATE TABLE students (
    student_id INT AUTO_INCREMENT, -- 学生ID，自动增量
    first_name VARCHAR(50),       -- 学生名
    last_name VARCHAR(50),        -- 学生姓
    birth_date DATE,              -- 出生日期
    PRIMARY KEY (student_id)      -- 设置student_id为主键
);

在这个例子中，student_id 列被设置为主键，AUTO_INCREMENT 属性使得每次插入新记录时，如果没有指定 student_id 的值，MySQL会自动为其分配一个唯一的值。 

UNIQUE：唯一约束，确保某列中的值不重复。

案例：在 students 表中，first_name 和 last_name 列应该总是包含学生的姓名，因此我们可以为这两列添加非空约束。

-- 添加email列并设置为唯一
ALTER TABLE students
ADD COLUMN email VARCHAR(255) UNIQUE; -- email列，确保唯一性

在这个例子中，email 列被设置了唯一约束，这意味着不能有两行数据具有相同的电子邮件地址。

NOT NULL：非空约束，确保某列必须有值。

案例：在 students 表中，first_name 和 last_name 列应该总是包含学生的姓名，因此我们可以为这两列添加非空约束。

-- 修改first_name和last_name列为非空
ALTER TABLE students
MODIFY first_name VARCHAR(50) NOT NULL, -- first_name列，必须有值
MODIFY last_name VARCHAR(50) NOT NULL;   -- last_name列，必须有值

在这个例子中，first_name 和 last_name 列被设置为非空，这意味着在插入或更新记录时，这两列必须包含有效的值。 

FOREIGN KEY：外键约束，确保表中的数据引用另一个表中的数据

案例：假设我们还有一个名为 courses 的表，其中包含了课程的信息。在 students 表中，我们可以添加一个 course_id 列来关联学生所选修的课程。

-- 创建课程表，设置course_id为主键
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT,       -- 课程ID，自动增量
    course_name VARCHAR(100),           -- 课程名称
    instructor VARCHAR(50),             -- 授课教师
    PRIMARY KEY (course_id)             -- 设置course_id为主键
);

-- 在学生表中添加course_id列，并将其设置为外键参照课程表的course_id
ALTER TABLE students
ADD COLUMN course_id INT,              -- 课程ID列
ADD FOREIGN KEY (course_id) REFERENCES courses(course_id); -- 设置course_id为外键，参照courses表的course_id

在这个例子中，course_id 列在 students 表中被定义为外键，它参照了 courses 表中的 course_id 主键。这确保了只有当 courses 表中存在对应的 course_id 时，才能在 students 表中插入或更新记录。 

快捷功能

 1.命令行界面（CLI）快捷键

        连接与退出
        连接MySQL服务器：mysql -u 用户名 -p，然后输入密码。
        退出MySQL：exit或quit命令，或者使用快捷键\q。

2.命令行编辑

        移动光标：Ctrl+A（开头）、Ctrl+E（结尾）、Ctrl+B（向前）、Ctrl+F（向后）。
        删除字符：Ctrl+D（当前位置）、Ctrl+K（到行尾）、Ctrl+U（到行首）。
        粘贴内容：Ctrl+Y（粘贴之前删除的内容）。
        查找历史命令：Ctrl+P（上一条）、Ctrl+N（下一条）、Ctrl+R（向后搜索匹配的命令）` Ctrl+G（退出搜索）。

图形用户界面（GUI）工具快捷键（以YOG软件为例）

对象浏览器
        刷新：F5
        焦点设置：Ctrl+B
SQL 窗口
        新建查询编辑器：Ctrl+T
        焦点设置：Ctrl+E
        撤销/重做：Ctrl+Z（撤销）、Ctrl+Y（重做）
        剪切/复制/粘贴：Ctrl+X（剪切）、Ctrl+C（复制）、Ctrl+V（粘贴）
        查找/替换：Ctrl+H（替换）
        导航：Ctrl+G（转到）
        执行查询：F8（执行当前查询并编辑结果集）、F9（执行当前查询）、Shift+F9（执行全部查询）
        插入/更新/删除/选择语句：Alt+Shift+I（插入）、Alt+Shift+U（更新）、Alt+Shift+D（删除）、Alt+Shift+S（选择）
        结果集操作：Ctrl+R（设置焦点于结果面板中的活动标签）、Ctrl+L（切换结果窗口/表数据以表格/文本方式显示）、Ctrl+Alt+C（以 CSV, SQL, Excel 等导出表数据）等。

MySQL编辑器或IDE中的快捷键

执行SQL

        执行整篇SQL脚本：Ctrl + Shift + Enter
        执行当前行：Ctrl + Enter

格式化SQL

        格式化sql语句（美化sql语句）：Ctrl + B

自动补全

        自动补全：Ctrl + Space，或使用edit->Auto-complete手动展示补全。

请注意，这些快捷键可能会因MySQL的版本、使用的工具或环境的不同而有所差异。在使用时，建议查阅相应工具的官方文档或帮助文件以获取最准确的信息

注意事项

1. 表设计
        合理规划表结构：确保表的设计符合数据存储和查询需求，包括选择适当的数据类型、定义主键、索引等。
        避免过度列化：不要为了存储单个属性而创建过多的列，尽量将相关属性合并到一个列中。
考虑数据完整性：根据业务需求，使用适当的约束（如主键约束、唯一约束、非空约束等）来保证数据的完整性。
2. 查询优化
        使用索引：为经常查询的列创建索引，可以提高查询性能，但要注意索引的数量和正确性。        重要的SQL语句中的WHERE条件列、ORDER BY、GROUP BY、DISTINCT字段等必须被索引。
        避免使用SELECT *：明确指定需要的列，避免不必要的数据传输和解析。
        合理使用连接(JOIN)：减少连接的数量，选择合适的连接类型，并确保连接条件有效。
        避免使用子查询：子查询往往效率较低，可以考虑使用连接或其他方法来替代。
3. 数据安全
        管理用户权限：合理设置用户的访问权限，限制对敏感数据的访问。
        加密敏感数据：对于存储敏感信息的列，可以使用加密技术来保护数据的安全性。
        定期备份数据：定期备份数据是防止数据丢失的重要措施。
4. 性能优化
        优化查询语句：分析和优化慢查询，避免使用全表扫描，使用合适的索引等。
        监控性能指标：使用性能监控工具来监测数据库的性能指标，及时发现和解决性能问题。
        考虑分表或分区：对于大型数据表，可以考虑使用分表或分区来提高查询性能和数据管理效率。
5. 数据库管理
        定期执行维护任务：如备份、优化表、检查数据完整性等。
        及时更新补丁：保持数据库软件的最新版本，修复已知的安全漏洞和性能问题。
        监控日志：定期查看数据库日志，及时发现和解决潜在的问题。
6. 其他注意事项
        避免在数据库中进行数学运算：数据库不擅长数学运算和逻辑判断，尽量在应用程序层面完成这些操作。
        避免使用大表做JOIN：大表JOIN操作可能导致性能问题，应尽量避免。
SQL语句规范：
        SQL语句尽可能简单，大SQL语句尽可能拆成小SQL语句。
        事务要简单，整个事务的时间长度不要太长，SQL结束后及时提交。
        限制单个事务所操作的数据集大小，不能超过10000条记录。
        禁止使用触发器、函数、存储过程（在不需要它们的情况下）。
        当需要分页查询时，注意limit的效率，可以采用子查询或其他方式优化

索引

索引是数据库管理系统中的一个重要概念，用于提高查询性能。通过在表的一个或多个列上创建索引，可以加快查询速度。

创建普通索引

假设有一个名为 employees 的表，想要在 department_id 列上创建一个索引以加快查询速度：

CREATE INDEX idx_department_id ON employees(department_id);

在这个例子中，idx_department_id 是索引的名称，employees 是表名，department_id 是列名。

创建唯一索引

如果希望确保 employees 表中的 email 列包含唯一的电子邮件地址，可以创建一个唯一索引： 

CREATE UNIQUE INDEX idx_unique_email ON employees(email);

在这个例子中，idx_unique_email 是索引的名称，employees 是表名，email 是列名。

创建复合索引

如果经常按照多个列一起查询数据，可以创建一个复合索引：

CREATE INDEX idx_compound ON employees(department_id, first_name);

在这个例子中，idx_compound 是索引的名称，employees 是表名，department_id 和 first_name 是列名，它们一起构成了复合索引。

删除索引

如果决定不再需要某个索引，可以使用以下命令将其删除：

DROP INDEX idx_department_id ON employees;

在这个例子中，idx_department_id 是要删除的索引的名称，employees 是表名。

添加注释

在创建表或索引时，可以添加注释来描述它们的用途，这有助于其他开发者理解数据库设计：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    COMMENT '员工信息表'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这个例子中，COMMENT '员工信息表' 是对 employees 表的注释。

视图

视图（View）是一种虚拟的表，它是基于一个或多个表的查询结果构建而成的。视图并不实际存储数据，而是作为一个预定义的查询结果被存储在数据库中，并可以像表一样被查询和使用。视图的主要作用是简化复杂的查询操作，提高数据的安全性，以及对数据进行抽象化处理，便于共享和维护23。

创建视图的基本语法

创建视图的基本语法如下：

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

其中，view_name 是视图的名称，column1, column2, ... 是要选择的列，table_name 是要查询的表，condition 是查询条件。例如，如果有一个名为 Customers 的表，包含 CustomerID, CustomerName, City 三列，想要创建一个视图，只选择 CustomerName 和 City 列，并且只包含 City 为 "Paris" 的行，可以使用以下命令：

CREATE VIEW ParisCustomers AS SELECT CustomerName, City FROM Customers WHERE City = 'Paris';

创建成功后，可以像查询表一样使用视图：

SELECT * FROM ParisCustomers;

使用视图的注意事项

• 视图并不实际存储数据，它只是一个预定义的查询结果。当从视图中查询数据时，实际上是对基础表进行查询操作。
• 视图可以根据基础表的变化自动更新，确保视图数据的实时性。
• 视图的数据安全性较高，使用视图的用户不接触数据表，从而保护数据不被未经授权的访问。
• 视图可以简化复杂的查询逻辑，提高查询的复用性和可维护性。

修改和删除视图的操作

• 修改视图通常可以通过再次执行创建视图的语句来实现，或者使用 ALTER VIEW 语句进行修改。
• 删除视图可以使用 DROP VIEW 命令，例如 DROP VIEW view_name;。如果视图不存在，使用 IF EXISTS 选项可以避免抛出异常。

备份与恢复

MySQL支持多种备份和恢复策略，以确保数据的安全性和可恢复性。常用的备份工具包括mysqldump，而恢复通常涉及将数据导入到数据库中。

安全性

MySQL提供了多种安全特性，包括用户权限管理、密码加密、访问控制列表（ACL）等，以确保数据库的安全。

进一步优化

除了基本的操作外，MySQL还提供了许多优化工具和策略，如查询优化、分区、复制等，以提高数据库的性能和可扩展性。

这只是一个MySQL数据库基础的简要概述和代码示例。为了深入学习和掌握MySQL，建议查阅官方文档和参考书籍，并进行大量的实践和练习。

数据库基础概念

        数据库定义：数据库是一个存放计算机数据的仓库，按照特定的数据结构对数据进行组织和存储。通过数据库提供的多种方法，可以方便地对数据进行管理。
数据库分类：
        关系型数据库：使用表格形式存储数据，如MySQL、Oracle、SQL Server等。
非关系型数据库：通常不使用表格，而是使用键值对、文档、图形等方式存储数据，如Redis、MongoDB等。
        数据库管理系统（DBMS）：用于管理和操作数据库的大型软件，能建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。

MySQL数据库介绍

        MySQL简介：MySQL是一个开源的中小型关系型数据库管理系统，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，被广泛应用于互联网上的大中小型网站、游戏公司、电商平台等。
        MySQL版本：MySQL官方提供了两种不同的版本：社区版本（MySQL Community Server）和商业版本（MySQL Enterprise Edition）。社区版本免费，但不提供技术支持；商业版本收费，并提供技术支持。

MySQL数据库操作基础

SQL（结构化查询语言）：是访问数据库的标准化语言，用于操作关系型数据库管理系统（RDBMS），如MySQL、Oracle等。
SQL语句分类：
DDL（数据定义语言）：用于定义或改变数据库结构，如CREATE、ALTER、DROP等。
DML（数据操纵语言）：用于处理数据，如INSERT、UPDATE、DELETE等。
DQL（数据查询语言）：用于查询数据，如SELECT等。
DCL（数据控制语言）：用于控制用户对数据库的访问权限，如GRANT、REVOKE等。

数据表基础：

主键：唯一标识表中的每一行数据，一个数据表只能有一个主键。
外键：用于关联两张表。
索引：对数据库表中一列或多列的值进行排序的一种结构，使用索引可以提高查找效率。

MySQL数据类型与命名规则

数值类型	INT,FLOAT,DOUBLE
日期类型	DATE,TIME,DATETIME
字符串类型	CHAR,VARCHAR

总结

MySQL课程的学习是一次宝贵的经历。我不仅掌握了数据库的基本知识和技能，还学会了如何批判性地思考和解决问题。展望未来，我将继续深化我的MySQL知识，并将它们应用于更广泛的领域中。