MySQL学习笔记
一、数据库
数据库基本概念
数据库（Database）：数据库是一种有组织的数据集合，通常电子化，并存储在计算机系统中，数据库管理系统（DBMS）用于管理和操作这些数据。
关系型数据库（Relational Database）：关系型数据库是基于关系模型的数据库，使用表格来表示数据及其相互关系，常见的关系型数据库包括MySQL、PostgreSQL、Oracle和SQL Server等。
数据库的组成
表（Table）：表是数据库的基本存储单位，由行（记录）和列（字段）组成，每个表都有一个名称，用于标识和引用。
行（Record/Row）：表中的每一行代表一条记录，包含若干个字段值。
列（Field/Column）：表中的每一列代表一个字段，每一列都有自己的名称和数据类型。
数据库的设计范式
第一范式（1NF）：确保每列都是原子的，不可再分。
第二范式（2NF）：在1NF的基础上，消除非主属性对码的部分函数依赖。
第三范式（3NF）：在2NF的基础上，消除非主属性对码的传递函数依赖。
常见数据库产品
MySQL：一种开源的关系型数据库管理系统，以性能高、成本低、可靠性好而被广泛采用。
PostgreSQL：一种强大的开源对象关系型数据库系统，支持多种高级特性。
Oracle：一种商业关系型数据库管理系统，适用于大型企业级应用。
SQL Server：微软开发的一种关系型数据库管理系统，常用于Windows平台的企业解决方案。
二、MySQL
MySQL的特点
开源：MySQL是开源的，免费提供社区版，适用于各种规模的应用。
跨平台支持：MySQL支持多种操作系统，包括Linux、Windows、Unix等。
高性能：MySQL具有高性能和高速度的特点，适用于Web应用和企业级应用。
MySQL的架构
连接管理和安全性：处理客户端连接和验证。
SQL引擎：解析和执行SQL语句。
存储引擎：管理数据的存储和检索，不同的存储引擎提供不同的功能，最常用的有InnoDB和MyISAM。
插件架构：允许通过动态加载的方式扩展MySQL的功能，例如全文索引、存储过程等。
MySQL的应用场景
Web应用管理系统（CMS）、博客平台（WordPress、Drupal）等。
数据分析：如日志分析、数据仓库等。
云计算服务：如Amazon Web Services（AWS）、Microsoft Azure等提供的数据库即服务（DBaaS）。
三、DQL语言
基础查询
SELECT语法：SELECT column1, column2, ... FROM table_name;
示例:SELECT first_name, last_name FROM users;
条件查询
WHERE子句：SELECT * FROM table_name WHERE condition;
示例:SELECT * FROM users WHERE age > 25;
排序查询
ORDER BY子句：SELECT * FROM table_name ORDER BY column_name [ASC|DESC];
示例:SELECT * FROM users ORDER BY last_name ASC;
单行函数
常见函数：AVG(),COUNT(),MAX(),MIN(),SUM()
示例:SELECT AVG(salary) FROM employees;
分组函数
GROUP BY子句：SELECT column1, aggregate_function(column2) FROM table_name WHERE condition GROUP BY column1;
示例:SELECT department, COUNT(*) FROM employees GROUP BY department;
分组查询
HAVING子句：SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;
示例:SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
连接查询
INNER JOIN：返回两个表中匹配的记录。
LEFT JOIN：返回左表中的所有记录以及右表中匹配的记录。
RIGHT JOIN：返回右表中的所有记录以及左表中匹配的记录。
示例:SELECT a.first_name, b.last_name FROM table1 a INNER JOIN table2 b ON a.id = b.id;
SQL99标准
SQL99标准：引入了诸如递归查询等新特性，使得SQL的功能更加强大。
子查询
子查询（Subquery）：在一个SQL语句中嵌套另一个SQL语句。
示例:SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');
分页查询
LIMIT子句：SELECT * FROM table_name LIMIT offset, row_count;
示例:SELECT * FROM users LIMIT 10, 20;
联合查询
UNION和UNION ALL：用于合并多个查询结果集。UNION去重，UNION ALL不去重。
示例:SELECT * FROM table1 UNION ALL SELECT * FROM table2;
四、DML语言
插入语句
INSERT INTO语法：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
示例:INSERT INTO users (first_name, last_name, email) VALUES ('John', 'Doe', 'john@example.com');
修改语句
UPDATE语法：UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
示例:UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
删除语句
DELETE语法：DELETE FROM table_name WHERE condition;
示例:DELETE FROM users WHERE id = 1;
五、DDL语言
库的管理
创建库：CREATE DATABASE db_name;
删除库：DROP DATABASE db_name;
查看库：SHOW DATABASES;
表的管理
创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
删除表：DROP TABLE table_name;
修改表：ALTER TABLE table_name ADD column_name datatype;或ALTER TABLE table_name DROP COLUMN column_name;或ALTER TABLE table_name RENAME TO new_table_name;等。
查看表结构：DESCRIBE table_name;或SHOW COLUMNS FROM table_name;
数据类型
整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
浮点类型：FLOAT,DOUBLE
字符串类型：CHAR,VARCHAR,TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT
日期和时间类型：DATE,TIME,DATETIME,TIMESTAMP,YEAR
二进制类型：BIT,BINARY,VARBINARY
常见约束
主键（PRIMARY KEY）：唯一标识记录。
外键（FOREIGN KEY）：建立表之间的关系。
唯一键（UNIQUE）：保证列中的所有值是唯一的。
非空（NOT NULL）：列不能包含NULL值。
默认值（DEFAULT）：当插入记录时如果没有指定值，则使用默认值。
自增长列
AUTO_INCREMENT：用于生成唯一的数字序列，通常与主键一起使用。
示例:CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50));
六、DCL语言
创建用户
CREATE USER：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
示例:CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
删除用户
DROP USER：DROP USER 'username'@'host';
示例:DROP USER 'newuser'@'localhost';
用户授权
GRANT语句：GRANT privileges ON database.table TO 'username'@'host';
示例:GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';
撤销授权
REVOKE语句：REVOKE privileges ON database.table FROM 'username'@'host';
示例:REVOKE ALL PRIVILEGES ON mydb.* FROM 'newuser'@'localhost';
刷新授权
FLUSH PRIVILEGES：重新加载所有权限，使其立即生效。
示例:FLUSH PRIVILEGES;
查看授权
SHOW GRANTS：查看用户的权限。
示例:SHOW GRANTS FOR 'newuser'@'localhost';
修改密码
SET PASSWORD：修改用户密码。
示例:SET PASSWORD FOR 'newuser'@'localhost' = 'newpassword123';
忘记密码
找回方式：可以通过跳过授权表的方式来重置root用户密码，步骤如下：
1. 停止MySQL服务：systemctl stop mysqld或service mysqld stop
2. 启动MySQL服务并跳过授权表：mysqld_safe --skip-grant-tables &
3. 登录MySQL：mysql -u root
4. 修改root用户密码：FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'Newpassword123';`
5. 退出MySQL并重新启动服务：exit; systemctl start mysqld或service mysqld start。
七、TCL语言
事务控制
事务的特性（ACID）：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
开始事务：START TRANSACTION;或BEGIN;
提交事务：COMMIT;
回滚事务：ROLLBACK;
保存点：在事务中使用保存点，可以部分回滚，设置保存点：SAVEPOINT savepoint_name;，回滚到保存点：ROLLBACK TO SAVEPOINT savepoint_name;。
八、安装与部署
安装MySQL单实例
步骤：下载MySQL安装包，根据操作系统选择合适的安装包，运行安装程序，按照指引完成安装，配置MySQL服务器，设置root用户密码，启动MySQL服务，并进行简单测试。
示例命令：Linux下可以使用包管理器安装，sudo apt-get install mysql-server（Ubuntu）或sudo yum install mysql-server（CentOS），启动MySQL服务：sudo systemctl start mysqld，登录MySQL：mysql -u root -p。
安装MySQL主从复制
主从复制：主从复制用于实现数据的同步和读写分离，主数据库负责写操作，从数据库负责读操作，从库定期从主库获取数据更新。
配置步骤：在主库上创建复制用户并授权，编辑主库配置文件开启二进制日志，在从库上配置要连接的主库信息，启动从库并检查复制状态。
示例命令：主库上创建复制用户：CREATE USER 'replica'@'%' IDENTIFIED BY 'password';，授权：GRANT REPLICATION SLAVE ON *.TO 'replica'@'%';，编辑配置文件[mysqld] binlog-do-db=mydb server-id=1，从库上停止MySQL服务，编辑配置文件添加要连接的主库信息，启动从库并检查状态：SHOW SLAVE STATUS\G;。
安装MySQL读写分离
读写分离：通过负载均衡器将读操作分配到从库，将写操作分配到主库，提高系统的并发处理能力，常用的负载均衡器有HAProxy、F5等。
配置步骤：搭建负载均衡器，配置健康检查和分发策略，配置主从复制，确保主从数据一致，在应用层配置数据源，使读写请求分别发送到主从库。
示例工具：使用HAProxy作为负载均衡器，配置文件示例：``frontend frontend_mysql bind *:3306 mode tcp default_backend mysql_servers maxconn 2000`，`backend mysql_servers balance roundrobin server master master_ip:3306 check``。
安装MySQL分片集群
分片集群：分片集群用于水平拆分数据，将数据分布到多个节点上，提高系统的存储能力和查询性能，常用的分片中间件有Mycat、ShardingSphere等。
配置步骤：选择分片中间件，并根据其文档进行配置，配置数据库分片规则和路由策略，部署中间件和各个数据库节点，启动服务并进行测试，监控和维护分片集群的运行状态。
示例工具：使用Mycat作为分片中间件，配置文件示例：``dataNodes { dataHost1=192.168.0.1:3306 dataHost2=192.168.0.2:3306 }`，`schemas { schema1=dataNodes }``，启动Mycat并检查状态。