Mysql 教程
简介：
MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。
在线测试工具：https://www.jyshare.com/front-end/7768/。
什么是数据库？
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：
- 1.数据以表格的形式出现
- 2.每行为各种记录名称
- 3.每列为记录名称所对应的数据域
- 4.许多的行和列组成一张表单
- 5.若干的表单组成database
RDBMS 术语
在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
- **行：**一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
- 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- **外键：**外键用于关联两个表。
- 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
- **索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:
- **表头(header): **每一列的名称;
- 列(col): 具有相同数据类型的数据的集合;
- 行(row): 每一行用来描述某条记录的具体信息;
- 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
- 键(key): 键的值在当前列中具有唯一性。
MySQL数据库
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
- MySQL 是开源的，目前隶属于 Oracle 旗下产品。
- MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL 使用标准的 SQL 数据语言形式。
- MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
- MySQL 对 PHP 有很好的支持，PHP 是很适合用于 Web 程序开发。
- MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
- MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
数据库操作
创建数据库
创建数据库是使用MySQL管理数据的第一步。以下是创建数据库的SQL语句示例：
CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
这里，IF NOT EXISTS确保如果数据库已经存在，则不会重复创建。DEFAULT CHARSET utf8mb4指定了数据库的默认字符集，而 COLLATE utf8mb4_general_ci指定了字符集的校对规则。
表操作
创建表
创建表是定义数据结构的过程。以下是创建表的SQL语句示例：
CREATE TABLE IF NOT EXISTS table_name (
column1 datatype constraints,
column2 datatype constraints,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
在这里，IF NOT EXISTS确保如果表已经存在，则不会重复创建。ENGINE=InnoDB指定了表的存储引擎，而 DEFAULT CHARSET=utf8mb4指定了表的字符集。
修改表
修改表结构可以使用 ALTER TABLE语句。以下是一些常见的修改表操作：
-- 添加新列
ALTER TABLE table_name ADD column_name datatype;
-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;
-- 修改列的数据类型
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
删除表
删除表会删除表及其所有数据。以下是删除表的SQL语句：
DROP TABLE IF EXISTS table_name;
这里，IF EXISTS确保如果表不存在，则不会产生错误。
索引
索引类型
MySQL支持多种索引类型，以下是一些常见的索引类型：
- B-Tree索引： 最常见的索引类型，适用于全键值、键值范围和键值排序的搜索。
- HASH索引： 只有精确匹配索引所有列的查询才有效。
- FULLTEXT索引： 用于全文检索，只能用于InnoDB和MyISAM存储引擎。
- SPATIAL索引： 用于空间数据类型，例如GIS数据。
索引操作
- 创建索引
CREATE INDEX index_name ON table_name (column1, column2, ...);
- 删除索引
DROP INDEX index_name ON table_name;
联合索引最左匹配原则
最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。例如某表现有索引(a,b,c)，现在你有如下语句：
select * from t where a=1 and b=1 and c =1; #这样可以利用到定义的索引（a,b,c）,用上a,b,c
select * from t where a=1 and b=1; #这样可以利用到定义的索引（a,b,c）,用上a,b
select * from t where b=1 and a=1; #这样可以利用到定义的索引（a,b,c）,用上a,c（mysql有查询优化器）
select * from t where a=1; #这样也可以利用到定义的索引（a,b,c）,用上a
select * from t where b=1 and c=1; #这样不可以利用到定义的索引（a,b,c）
select * from t where a=1 and c=1; #这样可以利用到定义的索引（a,b,c），但只用上a索引，b,c索引用不到
也就是说通过最左匹配原则你可以定义一个联合索引，但是使得多中查询条件都可以用到该索引。
值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。也就是：
select * from t where a=1 and b>1 and c =1; #这样a,b可以用到（a,b,c），c索引用不到
这条语句只有 a,b 会用到索引，c 都不能用到索引。这个原因可以从联合索引的结构来解释。
但是如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引，因为优化器会自动改写为最优查询语句
select * from t where a=1 and b >1 and c=1; #如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引
#优化器改写为
select * from t where a=1 and c=1 and b >1;
这也是最左前缀原理的一部分，索引index1:(a,b,c)，只会走a、a,b、a,b,c 三种类型的查询，其实这里说的有一点问题，a,c也走，但是只走a字段索引，不会走c字段。
另外还有一个特殊情况说明下，select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的也只会有 a与b 走索引，c不会走。
像select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快。
以index （a,b,c）为例建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。
最左匹配原则的原理
最左匹配原则都是针对联合索引来说的，所以我们可以从联合索引的原理来了解最左匹配原则。
我们都知道索引的底层是一颗 B+ 树，那么联合索引当然还是一颗 B+ 树，只不过联合索引的健值数量不是一个，而是多个。构建一颗 B+ 树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+ 树。
例子：假如创建一个（a,b,c)的联合索引，那么它的索引树是这样的：
该图就是一个形如(a,b,c)联合索引的 b+ 树，其中的非叶子节点存储的是第一个关键字的索引 a，而叶子节点存储的是三个关键字的数据。这里可以看出 a 是有序的，而 b，c 都是无序的。但是当在 a 相同的时候，b 是有序的，b 相同的时候，c 又是有序的。
通过对联合索引的结构的了解，那么就可以很好的了解为什么最左匹配原则中如果遇到范围查询就会停止了。以 select * from t where a=5 and b>0 and c =1; #这样a,b可以用到（a,b,c），c不可以 为例子，当查询到 b 的值以后（这是一个范围值），c 是无序的。所以就不能根据联合索引来确定到低该取哪一行。
总结
- 在 InnoDB 中联合索引只有先确定了前一个（左侧的值）后，才能确定下一个值。如果有范围查询的话，那么联合索引中使用范围查询的字段后的索引在该条 SQL 中都不会起作用。
- 值得注意的是，
in和=都可以乱序，比如有索引（a,b,c），语句select * from t where c =1 and a=1 and b=1，这样的语句也可以用到最左匹配，因为 MySQL 中有一个优化器，他会分析 SQL 语句，将其优化成索引可以匹配的形式，即select * from t where a =1 and b=1 and c=1
为什么要使用联合索引
**1、减少开销。**建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
**2、覆盖索引。**对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
**3、效率高。**索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！
使用索引优化查询问题：
1、创建单列索引还是多列索引？
如果查询语句中的where、order by、group 涉及多个字段，一般需要创建多列索引，比如：
select * from user where nick_name = 'ligoudan' and job = 'dog';
2、多列索引的顺序如何选择？
一般情况下，把选择性高德字段放在前面，比如：
查询sql：
select * from user where age = '20' and name = 'zh' order by nick_name;
这时候如果建索引的话，首字段应该是age，因为age定位到的数据更少，选择性更高。
但是务必注意一点，满足了某个查询场景就可能导致另外一个查询场景更慢。
3、避免使用范围查询
很多情况下，范围查询都可能导致无法使用索引。
4、尽量避免查询不需要的数据
explain select * from user where job like '%ligoudan%';
explain select job from user where job like '%ligoudan%';
同样的查询，不同的返回值，第二个就可以使用覆盖索引，第一个只能全表遍历了。
5、查询的数据类型要正确
explain select * from user where create_date >= now();
explain select * from user where create_date >= '2020-05-01 00:00:00';
第一条语句就可以使用create_date的索引，第二个就不可以。
索引失效场景
- 联合索引不满足最左匹配原则
- 使用了select *
- 索引列参与了运算
- 索引列参使用了函数
- like语句 %占位符在首部
- 使用OR关键字
- 列比较
- 不等于比较
- not in 和 not exists
- order by
数据库备份与恢复
备份数据库
备份MySQL数据库可以使用 mysqldump工具。以下是一个备份命令的示例：
bash
复制
mysqldump -u username -p database_name > backup_file.sql
这里，-u后面跟着数据库用户名，-p表示需要输入密码，database_name是要备份的数据库名，backup_file.sql是备份文件名。
恢复数据库
恢复数据库可以使用以下命令：
bash
复制
mysql -u username -p database_name < backup_file.sql
这里，mysql是连接到MySQL服务器的命令，-u后面跟着数据库用户名，-p表示需要输入密码，database_name是要恢复数据的数据库名，backup_file.sql是之前备份的文件。
安全性和权限管理
创建用户
创建新用户的SQL语句如下：
sql
复制
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
这里，'username'是新用户的名称，'host'指定了用户可以连接的MySQL服务器地址，'password'是用户密码。
授予权限
授予用户权限的SQL语句如下：
sql
复制
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'host';
这里，SELECT, INSERT, UPDATE, DELETE是授予的权限类型，database_name.*表示对指定数据库的所有表授予权限，'username'@'host'是之前创建的用户。
撤销权限
撤销用户权限的SQL语句如下：
sql
复制
REVOKE SELECT, INSERT, UPDATE, DELETE ON database_name.* FROM 'username'@'host';
这里，REVOKE是撤销权限的关键字，后面的参数与 GRANT语句相同。
- 感谢你赐予我前进的力量
-
微信 
支付宝
