MySQL数据库基础“一课一得”-CSDN博客

本文链接：https://blog.csdn.net/cgjxh/article/details/139600867

"一课一得"是一种学习方法，它强调在每次学习课程中都能获得一个明确的知识点或技能。对于MySQL数据库基础，这样的学习方法可以帮助我们逐步深入理解并掌握数据库的核心概念、查询语言（SQL）以及管理操作。

第1课：MySQL入门与安装

一、MySQL概述

什么是MySQL？
- MySQL是一个流行的开源关系型数据库管理系统（RDBMS）。
- 它使用了一种名为Structured Query Language（SQL）的数据库查询语言。
- MySQL由瑞典公司MySQL AB开发，后被Sun Microsystems收购，最终又被Oracle Corporation收购。
为什么需要MySQL？
- MySQL支持大量并发用户，并提供高性能的查询速度。
- 它具有良好的跨平台兼容性，可以在多种操作系统上运行，如Windows、Linux、Mac OS等。
- 由于其开源性质，MySQL拥有庞大的用户群体和丰富的资源支持。

二、MySQL安装

下载MySQL安装包
- 从MySQL官方网站（https://www.mysql.com/）下载适合自己操作系统的MySQL安装包。
- 注意选择稳定版本（如LTS版本），并根据需要选择社区版或企业版。
安装MySQL
- 根据所选操作系统的不同，安装步骤略有差异，但一般包括以下步骤：
  - 双击下载好的安装包，开始安装过程。
  - 选择安装类型（如“自定义”安装），以进行更多配置选项。
  - 选择安装目录和数据目录。
  - 设置root用户密码（建议使用强密码）。
  - 完成安装向导，等待安装完成。
验证安装
- 安装完成后，可以通过命令行或图形界面工具（如phpMyAdmin）来验证MySQL是否安装成功。
- 在命令行中，可以通过输入mysql -uroot -p并输入之前设置的root用户密码来登录MySQL。
- 如果能够成功登录并看到MySQL的提示符，则说明安装成功。

三、总结

MySQL是一个功能强大、性能优越的开源关系型数据库管理系统，广泛应用于各种网站和应用程序中。通过本课的学习，我们了解了MySQL的基本概念、特点和安装方法，为后续的学习和应用打下了坚实的基础。

第2课：MySQL基本命令与操作

一、连接MySQL服务器

使用命令行工具连接MySQL服务器，需要指定用户名、密码和可能的主机名。
- 命令格式：mysql -h 主机名 -u 用户名 -p
- 示例：mysql -h localhost -u root -p （默认主机名为localhost）
- 输入命令后，会提示输入密码，输入后按回车即可连接到MySQL服务器。

二、查看MySQL版本

使用SELECT VERSION();命令可以查看当前MySQL服务器的版本信息。

三、查看所有数据库

使用SHOW DATABASES;命令可以列出MySQL服务器上所有的数据库。

四、选择数据库

使用USE 数据库名;命令可以选择要操作的数据库。
- 示例：USE mydatabase;

五、查看当前选择的数据库

使用SELECT DATABASE();命令可以查看当前已经选择的数据库。

六、查看数据表

在选择了某个数据库后，可以使用SHOW TABLES;命令查看该数据库中的所有数据表。

七、退出MySQL

使用EXIT;或QUIT;命令可以退出当前MySQL连接。

八、其他常用命令

查看MySQL状态：虽然未直接给出命令，但可以使用STATUS命令（在某些MySQL版本中）或检查特定的系统变量来获取MySQL服务器的状态信息。
停止与启动MySQL服务：在Windows系统中，可以使用net stop mysql（服务名可能是mysql57或其他）来停止MySQL服务，使用net start mysql来启动服务。

九、注意事项

在执行任何数据库操作之前，建议备份重要数据以防意外丢失。
使用root用户时要特别小心，因为它具有对数据库的所有权限。
在连接MySQL服务器时，如果密码为空，可以只输入-p而不带空格，之后按回车，系统会提示输入密码。
示例代码：

第3课：SQL基础与数据增删改查
一、SQL基础

SQL（Structured Query Language）是结构化查询语言的缩写，是用于管理关系数据库的标准编程语言。它允许用户执行多种操作，如查询、更新和管理数据库中的数据。

二、数据增删改查
1.数据插入（INSERT）
- 用于向表中添加新的记录。
- 基本语法：INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
- 示例：向名为students的表中插入一条新记录，包含id、name和age字段。
- 2.数据查询（SELECT）
- 用于从表中检索数据。
- 基本语法：SELECT 列1, 列2, ... FROM 表名 WHERE 条件;
- 示例：查询students表中所有年龄大于18岁的学生姓名和年龄。
- 还可以进行更复杂的查询，如排序（ORDER BY）、分组（GROUP BY）、聚合函数（如COUNT, SUM, AVG等）等。
- 3.数据更新（UPDATE）
- 用于修改表中的数据。
- 基本语法：UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件;
- 示例：将students表中id为1的学生的年龄更新为21岁。
- 注意：UPDATE语句必须带有WHERE子句，否则将更新表中的所有记录。
- 4.数据删除（DELETE）
- 用于从表中删除记录。
- 基本语法：DELETE FROM 表名 WHERE 条件;
- 示例：删除students表中所有年龄小于18岁的学生记录。
- 注意：DELETE语句必须带有WHERE子句，否则将删除表中的所有记录。

三、注意事项

在执行数据更新和删除操作之前，请务必备份数据，以防意外丢失。
使用UPDATE和DELETE语句时，请确保WHERE子句的条件是正确的，以避免误操作。
在处理大量数据时，这些操作可能会对数据库性能产生影响，因此请谨慎使用，并在必要时考虑优化查询语句或数据库结构。

第4课：SQL高级查询与函数

一、高级查询

子查询（Subquery）
- 子查询允许将一个查询嵌套在另一个查询中。
- 示例：查找交易金额大于1000的客户。
- 子查询也可以用于计算，如计算平均订单金额。
UNION
- UNION运算符用于合并两个或多个SELECT语句的结果集，但不返回任何重复的行。
- 示例：显示employees和departments表中唯一的部门编号。
连接（Join）
- 连接用于根据相关列将多个表的行合并在一起。
- 常见的连接类型包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN）。
- 示例：使用INNER JOIN显示employees和departments表中匹配的部门编号。

二、SQL函数

字符串函数
- CONCAT：连接两个或多个字符串。
- SUBSTRING：返回字符串的子串。
- LENGTH：返回字符串的长度。
- 示例：使用CONCAT连接员工的名和姓。
数值函数
- SUM：计算数值列的总和。
- AVG：计算数值列的平均值。
- MAX/MIN：返回数值列的最大值或最小值。
- 示例：计算员工的平均工资。
日期和时间函数
- NOW()：返回当前日期和时间。
- DATE_FORMAT()：格式化日期和时间。
- DATEDIFF()：计算两个日期之间的天数差。
- 示例：格式化订单日期。
条件函数
- IF()：根据条件返回不同的值。
- 示例：根据订单数量判断库存状态。

三、注意事项

在使用高级查询和函数时，请确保了解它们的用途和限制，以避免错误或性能问题。
在进行复杂的查询时，考虑使用索引来优化性能。
当处理大量数据时，注意查询的性能和资源的消耗。

第5课：MySQL索引与性能优化

一、索引概述

MySQL中的索引是帮助数据库高效获取数据的数据结构。通过索引，数据库系统可以快速地定位到表中的特定数据，从而显著提高查询效率。索引通常是对表中的一列或多列的值进行排序的结构，MySQL支持多种类型的索引，包括B-Tree索引、哈希索引和全文索引等。

二、索引类型

B-Tree索引：
- B-Tree索引是MySQL中最常用的索引类型，它适用于各种查询场景。
- B-Tree索引通过维护一个平衡的多叉树结构，使得数据的查找、插入和删除操作都能保持较高的效率。
哈希索引：
- 哈希索引基于哈希表实现，它只支持等值查询，不支持范围查询。
- 哈希索引的查找速度非常快，但占用空间较大，且对于数据的插入和删除操作较为敏感。
全文索引：
- 全文索引主要用于文本数据的全文搜索。
- 它通过分词、建立倒排索引等方式，实现对文本数据的快速检索。

三、索引优化技巧

选择合适的列进行索引：
- 根据实际的查询场景选择合适的列进行索引。
- 一般来说，经常作为查询条件或连接表的列是创建索引的首选。
避免创建过多的索引：
- 过多的索引会导致索引维护的开销过大，降低写入性能。
- 因此，需要避免创建过多的无效索引。
使用前缀索引：
- 当字符列的长度较长时，可以考虑使用前缀索引。
- 前缀索引只会索引字符串的前几个字符，可以减少索引的大小，并提高查询效率。
避免对大列创建索引：
- 大列（如TEXT、BLOB）的数据较大，创建索引会占用较多的磁盘空间和内存资源，反而降低查询效率。
注意创建联合索引：
- 在一些有多个查询条件的查询中，可以通过创建联合索引来提高查询效率。
- 联合索引是指对多个列进行索引，可以在包含所有列的查询条件情况下加速查询速度。

四、性能优化建议

使用EXPLAIN分析查询：
- 使用EXPLAIN关键字可以分析MySQL如何处理SQL语句，从而找出性能瓶颈。
- 通过EXPLAIN的结果，可以了解索引是否被使用、表是如何被搜索和排序的等信息。
优化查询缓存：
- MySQL支持查询缓存功能，对于相同的查询语句，可以直接从缓存中获取结果，而无需再次执行查询。
- 但需要注意的是，不是所有的查询都适合使用查询缓存，如包含NOW()、RAND()等函数的查询。
分区与分表：
- 当数据表中的数据量很大时，可以考虑使用分区或分表来提高性能。
- 分区是将一个表的数据分成多个较小的、更易于管理的片段，而分表则是将一个大表拆分成多个结构相同的表。
读写分离与负载均衡：
- 通过读写分离和负载均衡等技术，可以从根本上提升数据库的性能。
- 读写分离是将读操作和写操作分别部署在不同的服务器上，而负载均衡则是将请求分发到多个服务器上，从而实现数据库的横向扩展。

第6课：MySQL用户管理与权限控制

一、用户管理

创建用户
- 使用CREATE USER语句创建新用户。
- 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
- 示例：CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
修改用户
- 使用UPDATE语句直接修改mysql.user表中的数据来更改用户信息。
- 但更推荐使用RENAME USER或ALTER USER语句来修改用户名或密码。
- 修改后，需执行FLUSH PRIVILEGES;来刷新权限。
删除用户
- 使用DROP USER语句删除用户。
- 语法：DROP USER '用户名'@'主机名';
- 示例：DROP USER 'newuser'@'localhost';

二、密码管理

修改密码
- 对于当前登录用户，可以使用ALTER USER或SET PASSWORD来修改密码。
- 对于其他用户（需要足够权限），使用ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';
密码管理策略
- MySQL提供了密码验证插件和密码策略，可以增强密码的复杂性和安全性。

三、权限控制

授予权限
- 使用GRANT语句授予用户权限。
- 语法：GRANT 权限类型 ON 数据库名.表名 TO '用户名'@'主机名';
- 示例：GRANT SELECT, INSERT ON mydb.mytable TO 'newuser'@'localhost';
查看权限
- 使用SHOW GRANTS语句查看用户的权限。
- 语法：SHOW GRANTS FOR '用户名'@'主机名';
撤销权限
- 使用REVOKE语句撤销用户的某些权限。
- 语法：REVOKE 权限类型 ON 数据库名.表名 FROM '用户名'@'主机名';