N74第六周作业

标签：N74 数据库作业查询第六周 MySQL NULL 备份 SELECT

1.完成将server和client端的mysql配置默认字符集为utf8mb4

设置服务器默认的字符集

vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

设置MySQL客户端默认的字符集

vim /etc/my.cnf
# 针对MySQL客户端
[mysql]
default-character-set=utf8mb4

# 针对所有MySQL客户端
[client]
default-character-set=utf8mb4

2.掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

获取SQL命令的帮助可以通过以下几种途径：

使用SQL命令行工具：大多数SQL命令行工具都支持输入help或\h命令，以获取SQL命令的帮助信息。例如，在MySQL命令行工具中，输入\h命令将显示可用的命令列表，输入help 命令名称可以查看该命令的详细信息。
查阅SQL文档：SQL有广泛的在线文档资源，例如MySQL官方文档、Oracle官方文档、PostgreSQL官方文档等，这些文档包含了SQL命令的详细介绍和使用方法。

# MySQL官方文档 
https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

# Oracle官方文档
https://docs.oracle.com/en/database/oracle/oracle-database/index.html

# PostgreSQL官方文档
https://www.postgresql.org/docs/

搜索互联网资源：在互联网上搜索可以找到大量的SQL教程和文章，这些资源通常包含了SQL命令的使用示例和详细解释。
参考数据库管理系统的帮助文档：某些数据库管理系统（如phpMyAdmin）提供了内置的帮助文档，可以在该文档中查找SQL命令的使用方法和语法。

CREATE DATABASE testdb character set utf8 COLLATE utf8_bin;

3.总结mysql常见的数据类型

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

MySQL支持许多不同的数据类型，每种数据类型都具有不同的特性和用途。以下是MySQL常见的数据类型：

整型（INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT）：用于存储整数值，不同的整型数据类型支持的范围不同。
浮点型（FLOAT、DOUBLE、DECIMAL）：用于存储带小数点的数字，不同的浮点型数据类型支持的精度不同。
字符串型（CHAR、VARCHAR、TEXT、BLOB）：用于存储字符型数据，不同的字符串型数据类型支持的长度和存储方式不同。
日期/时间型（DATE、TIME、DATETIME、TIMESTAMP、YEAR）：用于存储日期和时间信息，不同的日期/时间型数据类型支持的精度和存储方式不同。
枚举和集合型（ENUM、SET）：用于存储具有限定值的数据，ENUM类型只能存储单个值，而SET类型可以存储多个值。

总之，选择正确的数据类型可以使MySQL数据库更高效地处理数据，提高数据库性能，因此在设计MySQL数据库时需要根据需求选择合适的数据类型。

4.创建一个主机表host，放在testdb中，要求字段

主键自增id 无符号, tinyint.
hostname可变字符长度256，可为空。
ip 可变字符长度256，可为空。
账号，可变字符长度256，可为空。
密码，可变字符长度256，可为空。
创建时间，时间类型，非空。
更新时间，时间类型，默认当前时间。
区域，只能在华南，华北，华东，三个区域之一。
端口，无符号整数，可为空。
外网地址，可变字符长度256，可为空。
内网地址，可变字符长度256，可为空。

USE testdb;
CREATE TABLE host(
  							 id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                 hostname VARCHAR(256),
                 ip VARCHAR(256),
                 account VARCHAR(256),
                 password VARCHAR(256),
                 create_time DATETIME NOT NULL,
                 update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                 region ENUM('华南'，'华北'，'华东') NOT NULL,
                 port INT UNSIGNED,
                 external_address VARCHAR(256),
                 internal_address VARCHAR(256)
);

5.给testdb.host表中添加多条数据

USE testdb;
INSERT INTO host (hostname, ip, account, password, create_time, region, port, external_address, internal_address)
VALUES ('host1', '192.168.0.1', 'root', 'password123', '2023-06-01 08:00:00', '华南', 3306, 'www.example.com', NULL),
       ('host2', '192.168.0.2', 'root', 'password456', '2023-06-01 09:00:00', '华南', 3306, 'www.example.com', NULL),
       ('host3', '192.168.0.3', 'root', 'password789', '2023-06-01 10:00:00', '华东', NULL, NULL, '192.168.1.1'),
       ('host4', '192.168.0.4', NULL, NULL, '2023-06-01 11:00:00', '华北', NULL, 'www.example.com', '192.168.1.2');

6.根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例

DDL（Data Definition Language）是数据库语言中的一类，用于定义数据库结构、模式和约束等。常用的DDL命令有以下几种：

CREATE：用于创建数据库对象，如表、视图、索引、存储过程、触发器等。
ALTER：用于修改数据库对象，如修改表结构、修改视图、修改索引等。
DROP：用于删除数据库对象，如删除表、删除视图、删除存储过程等。
TRUNCATE：用于清空表中的数据。

DDL操作通常由数据库管理员或开发人员使用，用于创建和维护数据库对象以确保数据的正确性和一致性。与DDL相对的是DML（Data Manipulation Language），用于操作数据库中的数据，如查询、插入、更新和删除数据等。

删除表

DROP TABLE 'host';

修改表名

ALTER TABLE host RENAME 'machine';

添加字段

ALTER TABLE machine ADD phone VARCHAR(11) AFTER password;

修改字段类型

ALTER TABLE machine MODIFY phone int;

修改字段名称和类型

ALTER TABLE machine CHANGE COLUMN phone mobile char(11);

删除字段

ALTER TABLE machine DROP COLUMN mobile;

修改字符集

ALTER TABLE machine character set utf8;

修改数据类型和字符集

ALTER TABLE machine CHANGE id id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT;

删除字段

ALTER TABLE machine DROP monile;

查看表结构

DESC machine;

DML（Data Manipulation Language）是数据库语言中的一类，用于操作数据库中的数据，如查询、插入、更新和删除数据等。常见的DML命令有以下几种：

SELECT：用于查询数据表中的数据。
INSERT：用于向数据表中插入新的数据。
UPDATE：用于修改数据表中的数据。
DELETE：用于删除数据表中的数据。

DML操作通常由应用程序或最终用户使用，并且是最经常执行的操作之一。与DML相对的是DDL（Data Definition Language），用于定义数据库结构、模式和约束等。

简单查询

SELECT * FROM machine WHERE id<3;
SELECT * FROM machine WHERE region='华东';
SELECT * FROM machine WHERE account IS NULL;
SELECT * FROM machine WHERE ip like '192%';

记录去重

SELECT DISTINCT region from machine;

判断是否为NULL

SELECT * FROM machine where internal_address IS NULL;

字段别名

SELECT hostname AS 主机名, ip, account AS 账号, password AS 密码, create_time AS 创建时间, region AS 地区, port AS 端口号, external_address AS 外部地址, internal_address AS 内部地址 FROM machine

分页查询

SELECT * FROM machine LIMIT 0,3;
SELECT * FROM machine LIMIT 3;

分组统计

SELECT region, count(*) AS 数量 FROM machine GROUP BY region;

7.导入hellodb库，总结DQL, alias, where子句，group by, order by, limit, having使用示例

字段别名

MariaDB [hellodb]> select stuid 学员ID, name as 姓名,gender 性别 from students;
+----------+---------------+--------+
| 学员ID   | 姓名          | 性别   |
+----------+---------------+--------+
|        1 | Shi Zhongyu   | M      |
|        2 | Shi Potian    | M      |
|        3 | Xie Yanke     | M      |
|        4 | Ding Dian     | M      |
|        5 | Yu Yutong     | M      |
|        6 | Shi Qing      | M      |
|        7 | Xi Ren        | F      |
|        8 | Lin Daiyu     | F      |
|        9 | Ren Yingying  | F      |
|       10 | Yue Lingshan  | F      |
|       11 | Yuan Chengzhi | M      |
|       12 | Wen Qingqing  | F      |
|       13 | Tian Boguang  | M      |
|       14 | Lu Wushuang   | F      |
|       15 | Duan Yu       | M      |
|       16 | Xu Zhu        | M      |
|       17 | Lin Chong     | M      |
|       18 | Hua Rong      | M      |
|       19 | Xue Baochai   | F      |
|       20 | Diao Chan     | F      |
|       21 | Huang Yueying | F      |
|       22 | Xiao Qiao     | F      |
|       23 | Ma Chao       | M      |
|       24 | Xu Xian       | M      |
|       25 | Sun Dasheng   | M      |
+----------+---------------+--------+
25 rows in set (0.000 sec)

判断是否为NULL

MariaDB [hellodb]> select * from students where classid is null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    24 | Xu Xian     |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> select * from students where classid <=> null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    24 | Xu Xian     |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> select * from students where classid is not null;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.000 sec)

#ifnull函数判断指定的字段是否为空值，如果空值则指定默认值
MariaDB [hellodb]> select stuid,name,ifnull(classID,'无班级') from students where classid is null;
+-------+-------------+-----------------------------+
| stuid | name        | ifnull(classID,'无班级')    |
+-------+-------------+-----------------------------+
|    24 | Xu Xian     | 无班级                      |
|    25 | Sun Dasheng | 无班级                      |
+-------+-------------+-----------------------------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> select distinct gender from students;
+--------+
| gender |
+--------+
| M      |
| F      |
+--------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> select * from students limit 0,3;
MariaDB [hellodb]> select * from students limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)

MariaDB [hellodb]> select * from students limit 1,3;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name       | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
|     2 | Shi Potian |  22 | M      |       1 |         7 |
|     3 | Xie Yanke  |  53 | M      |       2 |        16 |
|     4 | Ding Dian  |  32 | M      |       4 |         4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)

#查询第n页的数据，每页显示m条记录
select * from students limit (n-1)*m,m;

聚合函数

SELECT sum(age)/count(*) FROM students WHERE gender='M';
+-------------------+
| sum(age)/count(*) |
+-------------------+
|           33.0000 |
+-------------------+
1 row in set (0.001 sec)

MariaDB [hellodb]> select sum(age)/count(*) from students where gender = 'F';
+-------------------+
| sum(age)/count(*) |
+-------------------+
|           19.0000 |
+-------------------+
1 row in set (0.000 sec)

分组统计

MariaDB [hellodb]> select classid, count(*) AS 数量 FROM students group by classid;
+---------+--------+
| classid | 数量   |
+---------+--------+
|    NULL |      2 |
|       1 |      4 |
|       2 |      3 |
|       3 |      4 |
|       4 |      4 |
|       5 |      1 |
|       6 |      4 |
|       7 |      3 |
+---------+--------+
8 rows in set (0.001 sec)

MariaDB [hellodb]> select classid, gender, count(*) AS 数量 FROM students group by classid,gender;
+---------+--------+--------+
| classid | gender | 数量   |
+---------+--------+--------+
|    NULL | M      |      2 |
|       1 | F      |      2 |
|       1 | M      |      2 |
|       2 | M      |      3 |
|       3 | F      |      3 |
|       3 | M      |      1 |
|       4 | M      |      4 |
|       5 | M      |      1 |
|       6 | F      |      3 |
|       6 | M      |      1 |
|       7 | F      |      2 |
|       7 | M      |      1 |
+---------+--------+--------+
12 rows in set (0.000 sec)

分组统计

MariaDB [hellodb]> select classid,avg(age) AS 平均年龄 FROM students where classid >3 group by classid having 平均年龄 >30;
+---------+--------------+
| classid | 平均年龄     |
+---------+--------------+
|       5 |      46.0000 |
+---------+--------------+
1 row in set (0.000 sec)

MariaDB [hellodb]> select gender,avg(age) AS 平均年龄 FROM students group by gender having gender='M';
+--------+--------------+
| gender | 平均年龄     |
+--------+--------------+
| M      |      33.0000 |
+--------+--------------+
1 row in set (0.000 sec)

多字段分组统计

MariaDB [hellodb]> select classid,gender,count(*) AS 数量 FROM students group by classid , gender ;
+---------+--------+--------+
| classid | gender | 数量   |
+---------+--------+--------+
|    NULL | M      |      2 |
|       1 | F      |      2 |
|       1 | M      |      2 |
|       2 | M      |      3 |
|       3 | F      |      3 |
|       3 | M      |      1 |
|       4 | M      |      4 |
|       5 | M      |      1 |
|       6 | F      |      3 |
|       6 | M      |      1 |
|       7 | F      |      2 |
|       7 | M      |      1 |
+---------+--------+--------+
12 rows in set (0.000 sec)

MariaDB [hellodb]> select gender, group_concat(name) from students group by gender;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| gender | group_concat(name)                                                                                                                                     |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| F      | Lin Daiyu,Yue Lingshan,Ren Yingying,Wen Qingqing,Xi Ren,Diao Chan,Huang Yueying,Xiao Qiao,Lu Wushuang,Xue Baochai                                      |
| M      | Hua Rong,Shi Zhongyu,Ma Chao,Xu Xian,Lin Chong,Xu Zhu,Duan Yu,Tian Boguang,Yuan Chengzhi,Shi Qing,Yu Yutong,Ding Dian,Xie Yanke,Shi Potian,Sun Dasheng |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.006 sec)

分组统计

MariaDB [hellodb]> select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| F      |       10 |
| M      |       15 |
| NULL   |       25 |
+--------+----------+
3 rows in set (0.000 sec)

排序

MariaDB [hellodb]> select * from students order by age desc limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
|     6 | Shi Qing    |  46 | M      |       5 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)

#跳过前3个只显示后续的2个
MariaDB [hellodb]> select * from students order by age desc limit 3,2;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
+-------+--------------+-----+--------+---------+-----------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid;
+---------+----------+
| classid | sum(age) |
+---------+----------+
|       1 |       82 |
|       2 |      108 |
|       3 |       81 |
|       4 |       99 |
|       5 |       46 |
|       6 |       83 |
|       7 |       59 |
+---------+----------+
7 rows in set (0.000 sec)

MariaDB [hellodb]> select classid,sum(age) from students group by classid having classid is not null order by classid;
+---------+----------+
| classid | sum(age) |
+---------+----------+
|       1 |       82 |
|       2 |      108 |
|       3 |       81 |
|       4 |       99 |
|       5 |       46 |
|       6 |       83 |
|       7 |       59 |
+---------+----------+
7 rows in set (0.001 sec)

MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid limit 2,3;
+---------+----------+
| classid | sum(age) |
+---------+----------+
|       3 |       81 |
|       4 |       99 |
|       5 |       46 |
+---------+----------+
3 rows in set (0.000 sec)

#必须先过滤，再排序
MariaDB [hellodb]> select * from students where classid is not null order by gender desc, age asc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.000 sec)

#多列排序
MariaDB [hellodb]> select * from students order by gender desc, age asc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)

正序排序时将NULL记录排在最后

#对 classid正序排序，NULL记录排在最后
MariaDB [hellodb]> select * from students order by -classid desc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)

分组和排序

MariaDB [hellodb]> select classid,count(*) AS 数量 from students group by classid order by 数量;
+---------+--------+
| classid | 数量   |
+---------+--------+
|       5 |      1 |
|    NULL |      2 |
|       2 |      3 |
|       7 |      3 |
|       1 |      4 |
|       4 |      4 |
|       3 |      4 |
|       6 |      4 |
+---------+--------+
8 rows in set (0.000 sec)

MariaDB [hellodb]> select gender, classid,avg(age) from students where classid is not null group by gender,classid order by gender,classid;
+--------+---------+----------+
| gender | classid | avg(age) |
+--------+---------+----------+
| F      |       1 |  19.5000 |
| F      |       3 |  18.3333 |
| F      |       6 |  20.0000 |
| F      |       7 |  18.0000 |
| M      |       1 |  21.5000 |
| M      |       2 |  36.0000 |
| M      |       3 |  26.0000 |
| M      |       4 |  24.7500 |
| M      |       5 |  46.0000 |
| M      |       6 |  23.0000 |
| M      |       7 |  23.0000 |
+--------+---------+----------+
11 rows in set (0.000 sec)

MariaDB [hellodb]> select * from students order by age limit 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|    14 | Lu Wushuang  |  17 | F      |       3 |      NULL |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|    19 | Xue Baochai  |  18 | F      |       6 |      NULL |
|    15 | Duan Yu      |  19 | M      |       4 |      NULL |
|    12 | Wen Qingqing |  19 | F      |       1 |      NULL |
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
|    20 | Diao Chan    |  19 | F      |       7 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
|    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)

MariaDB [hellodb]> select * from students order by age limit 3,10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|    15 | Duan Yu      |  19 | M      |       4 |      NULL |
|    20 | Diao Chan    |  19 | F      |       7 |      NULL |
|    12 | Wen Qingqing |  19 | F      |       1 |      NULL |
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
|    16 | Xu Zhu       |  21 | M      |       1 |      NULL |
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)

MariaDB [hellodb]> select distinct age from students order by age limit 3;
+-----+
| age |
+-----+
|  17 |
|  18 |
|  19 |
+-----+
3 rows in set (0.001 sec)

MariaDB [hellodb]> select distinct age from students order by age limit 3,5;
+-----+
| age |
+-----+
|  20 |
|  21 |
|  22 |
|  23 |
|  25 |
+-----+
5 rows in set (0.000 sec)

分组和排序的次序

#顺序：group by , having, order by
MariaDB [hellodb]> select classid,count(*) from students group by classid having classid is not null order by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
|       1 |        4 |
|       2 |        3 |
|       3 |        4 |
|       4 |        4 |
|       5 |        1 |
|       6 |        4 |
|       7 |        3 |
+---------+----------+
7 rows in set (0.000 sec)

8.基于hellodb库, 总结子查询，关联查询，交叉连接，内连接，左连接，右连接，完全连接，自连接

子查询是指将一个SQL语句嵌套在另一个SQL语句中，并使用内部查询的结果作为外部查询的条件来筛选数据。子查询通常用于需要动态获取条件的情况下，比如在WHERE子句中使用子查询来动态筛选数据。

一个基本的子查询语法如下：

SELECT column
FROM table1
WHERE column_name operator
(SELECT column_name FROM table2 WHERE condition);

在这个语法中，内部查询通过SELECT、FROM和WHERE等关键字来实现条件筛选，而外部查询则通过WHERE子句中的operator运算符和内部查询的结果进行匹配。

例如，如果我们要从orders表中获取id列包含在customers表格的id列中的所有订单信息，则可以使用以下的SQL子查询：

SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers);

这个SQL语句将从orders表格中查询所有customer_id在customers表格中出现过的订单信息。在内部查询中，我们使用SELECT id FROM customers来获取所有customers表中的id列表。在外部查询中，我们使用IN运算符将内部查询的结果与orders表格中的customer_id列进行匹配，从而获得符合条件的订单信息列表。

子查询还有许多其他应用场景，例如在INSERT、UPDATE、DELETE等语句中使用子查询动态更新或删除数据。

联合查询（UNION）是 SQL 中一种常见的数据合并技术，用于在两个或多个 SELECT 语句的结果中取并集。

如果您需要从两个或多个表格中检索相似的数据，可使用 UNION 进行合并。联合查询将多个 SELECT 语句的结果组合成一个结果集，其中每个 SELECT 语句产生的行数和列数必须相同。联合查询返回的结果集中不包含重复的行。

以下是 UNION 查询的示例，假设我们有两个表 students 和 teachers：

students 表

id	name
1	Bob
2	Alice
3	Charlie

teachers 表

id	name
1	Smith
2	Johnson
4	Green

我们可以使用如下 SQL 语句来进行联合查询：

SELECT id, name FROM students
UNION
SELECT id, name FROM teachers;

这条 SQL 语句将从 students 表和 teachers 表中分别查询出学生和老师的 id 和 name 字段，并将它们合并成一个结果集。这个联合查询将删除重复行，并返回如下结果：

id	name
1	Bob
2	Alice
3	Charlie
4	Green
5	Johnson
6	Smith

该结果集包含了学生和老师表的所有行，并去除了重复行。注意，用于 UNION 的 SELECT 语句必须具有相同数目的列，且这些列必须具有相似的数据类型。

内连接（INNER JOIN）是 SQL 中常见的表格连接技术之一，也是最常用的一种。它使用一个或多个列作为键在两个或多个表格之间建立连接，返回符合连接条件的行。

内连接根据 SELECT 语句中指定的连接条件将两个或多个表格中的行匹配，并将符合条件的行连接起来，生成一个包含联接后结果的表格。内连接只返回那些在至少两个匹配表格中都存在的行。

以下是 INNER JOIN 的示例，假设我们有两个表格 t1 和 t2：

t1 表

id	name
1	Bob
2	Alice
3	Charlie
4	David

t2 表

id	age
2	30
3	25
5	40

我们可以使用如下 SQL 语句来进行 INNER JOIN 查询：

SELECT t1.id, t1.name, t2.age
FROM t1
INNER JOIN t2 ON t1.id = t2.id;

这条 SQL 语句将使用 t1 表格和 t2 表格中的 id 列进行匹配，仅返回在两个表格中都出现过的行，并将它们合并成一个结果集。该查询返回如下结果：

id	name	age
2	Alice	30
3	Charlie	25

注意，INNER JOIN 可以是多表连接。如果 INNER JOIN 中涉及到的表格不止两个，需要在 ON 子句中连接每两个表格的连接条件。

左连接（LEFT JOIN 或 LEFT OUTER JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及左表格中未匹配的行。

具体来说，左连接会先选取左表格中的所有行，并在右表格中查找与左表格中的每一行匹配的行。如果找到了匹配的行，则将两个表格中的数据合并成一行；如果没有找到匹配的行，则将左表格中的这一行与 NULL 值合并成一行。因此，左连接会保留左表格中未匹配的行。

以下是左连接的示例，假设我们有两个表格 t1 和 t2：

t1 表

id	name
1	Bob
2	Alice
3	Charlie
4	David

t2 表

id	age
2	30
3	25
5	40

我们可以使用如下 SQL 语句来进行左连接查询：

SELECT t1.id, t1.name, t2.age
FROM t1
LEFT JOIN t2 ON t1.id = t2.id;

这条 SQL 语句将返回 t1 表格中的所有行，并在 t2 表格中匹配符合条件的行。对于没有匹配到的行，将补充为 NULL 值。该查询返回如下结果：

id	name	age
1	Bob	NULL
2	Alice	30
3	Charlie	25
4	David	NULL

需要注意的是，左连接只保留左表格中未匹配的行，而右表格中未匹配的行将被过滤掉。如果需要保留右表格中的未匹配行，则需要使用右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）。

右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及右表格中未匹配的行。

具体来说，右连接会先选取右表格中的所有行，并在左表格中查找与右表格中的每一行匹配的行。如果找到了匹配的行，则将两个表格中的数据合并成一行；如果没有找到匹配的行，则将右表格中的这一行与 NULL 值合并成一行。因此，右连接会保留右表格中未匹配的行。

以下是右连接的示例，假设我们有两个表格 t1 和 t2：

t1 表

id	name
1	Bob
2	Alice
3	Charlie
4	David

t2 表

id	age
2	30
3	25
5	40

我们可以使用如下 SQL 语句来进行右连接查询：

SELECT t1.id, t1.name, t2.age
FROM t1
RIGHT JOIN t2 ON t1.id = t2.id;

这条 SQL 语句将返回 t2 表格中的所有行，并在 t1 表格中匹配符合条件的行。对于没有匹配到的行，将补充为 NULL 值。该查询返回如下结果：

id	name	age
2	Alice	30
3	Charlie	25
5	NULL	40

需要注意的是，右连接只保留右表格中未匹配的行，而左表格中未匹配的行将被过滤掉。如果需要保留左表格中的未匹配行，则需要使用左连接（LEFT JOIN 或 LEFT OUTER JOIN）。

完全连接（FULL OUTER JOIN 或 FULL JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及左右表格中未匹配的行。

具体来说，完全连接会先选取左表格和右表格中的所有行，并在彼此之间查找是否有匹配的行。如果找到了匹配的行，则将两个表格中的数据合并成一行；如果没有找到匹配的行，则将该行与 NULL 值合并成一行。因此，完全连接会保留左右表格中未匹配的行。

以下是完全连接的示例，假设我们有两个表格 t1 和 t2：

t1 表

id	name
1	Bob
2	Alice
3	Charlie
4	David

t2 表

id	age
2	30
3	25
5	40

我们可以使用如下 SQL 语句来进行完全连接查询：

SELECT t1.id, t1.name, t2.age
FROM t1
FULL JOIN t2 ON t1.id = t2.id;

这条 SQL 语句将返回 t1 表格和 t2 表格中的所有行，并在彼此之间匹配符合条件的行。对于没有匹配到的行，将补充为 NULL 值。该查询返回如下结果：

id	name	age
1	Bob	NULL
2	Alice	30
3	Charlie	25
4	David	NULL
5	NULL	40

需要注意的是，完全连接会保留左右表格中未匹配的行，因此结果集可能会很大。除非有必要，否则应谨慎使用完全连接。

自连接（Self Join）是一种在同一个表内进行连接的操作。具体来说，自连接是指将一个表格视为两个不同的表格，并在这两个“表格”之间进行连接。

自连接有时候可以用来解决一些比较复杂的数据查询问题，例如可以用自连接查询出一个员工与其经理的名字。这个查询需要在同一张表中查找员工和经理，并将它们关联起来。

以下是一个自连接的示例，假设我们有一个表格 employee：

employee 表

id	name	manager_id
1	Bob	3
2	Alice	3
3	Charlie	NULL
4	David	2

其中，manager_id 列存储了每个员工对应的经理的 id。

现在我们要查询每个员工的名字以及他们的经理的名字。我们需要将 employee 表格与自身进行连接，并将员工和经理之间的关系建立起来。下面是可以执行这个查询的 SQL 语句：

SELECT e.name AS employee_name, m.name AS manager_name
FROM employee AS e
LEFT JOIN employee AS m ON e.manager_id = m.id;

这条 SQL 语句使用了自连接和左连接来查找每个员工的名字以及他们的经理的名字。它会对 employee 表格进行两次引用，第一次引用作为员工表格，第二次引用作为经理表格。左连接保证了即使某个员工没有经理，也会返回该员工的信息，同时将经理信息设为 NULL。

查询结果如下：

employee_name	manager_name
Bob	Charlie
Alice	Charlie
Charlie	NULL
David	Alice

需要注意的是，在进行自连接时，必须为每个表格指定别名以便于区分。

9.总结SELECT语句处理顺序

一条SELECT语句的处理顺序主要分为以下几个步骤：

FROM子句：先执行FROM子句，从指定的表或视图中获取数据。
WHERE子句：在FROM子句获取的数据上执行WHERE子句，过滤出符合条件的行。需要注意的是，WHERE子句不能使用SELECT子句中定义的别名，因为它们还未被计算出来。
GROUP BY子句：在WHERE子句过滤出的数据上执行GROUP BY子句，将数据按照指定列进行分组，然后对每个分组执行聚合函数，如SUM、AVG、MAX、MIN等。
HAVING子句：在GROUP BY子句分组后的结果上再次进行过滤，筛选出符合条件的分组。
SELECT子句：计算所有SELECT子句中的表达式，包括聚合函数，生成最终结果集。需要注意的是，SELECT子句中使用的列别名只有在整个SELECT语句执行完成后才会被计算出来，因此不能在WHERE子句或GROUP BY子句中使用别名。
ORDER BY子句：在最终结果集上执行ORDER BY子句，按照指定的列进行排序，得到最终的查询结果。

需要注意的是，以上步骤并非一定按照这个顺序执行，具体的执行顺序可能受到数据库的优化器或执行计划的影响，但是SQL标准确保了语句的执行结果与以上顺序相同。

10.总结MySQL事件管理，用户管理，权限管理

MySQL事件是一种定时任务，可以在指定时间点自动执行一些指定的SQL语句。使用MySQL事件管理功能可以方便地实现各种定时操作，比如备份、统计、清理数据等。

下面是MySQL事件管理的一些重要操作：

创建事件：使用CREATE EVENT语句来创建一个新的事件，语法格式如下：

CREATE EVENT event_name
ON SCHEDULE schedule
DO
event_body

其中event_name表示事件的名称，schedule设置事件的定时计划，event_body中指定需要执行的SQL语句。

修改事件：使用ALTER EVENT语句来修改已有的事件，语法格式如下：

ALTER EVENT event_name
ON SCHEDULE schedule
DO
event_body

删除事件：使用DROP EVENT语句来删除已有的事件，语法格式如下：

DROP EVENT event_name

查看事件：使用SHOW EVENTS语句来查看当前数据库中所有的事件信息。
启用/禁用事件：使用ENABLE/DISABLE命令来启用或禁用事件，语法格式如下：

ALTER EVENT event_name ENABLE;
ALTER EVENT event_name DISABLE;

需要注意的是，事件的创建和修改需要管理员或具有SUPER权限的用户来执行，否则会提示权限不足的错误。此外，MySQL事件只能在MySQL服务器打开了事件调度器的情况下使用，如果事件调度器被禁用，则无法使用MySQL事件功能。

MySQL用户管理是指对数据库用户账号进行创建、修改、删除和权限控制等操作的过程。以下是关于MySQL用户管理的一些基本操作：

创建用户：使用CREATE USER语句来创建一个新的MySQL用户，语法格式如下：

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

其中'username'是新用户的用户名，'localhost'表示该用户只能在本地连接到MySQL服务器，IDENTIFIED BY 'password'指定用户的密码。

修改用户：使用ALTER USER语句来修改已有的MySQL用户信息，语法格式如下：

ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

删除用户：使用DROP USER语句来删除已有的MySQL用户，语法格式如下：

DROP USER 'username'@'localhost';

授权：使用GRANT语句为用户授予不同的权限，语法格式如下：

GRANT permission ON database.table TO 'username'@'localhost';

其中permission可以是SELECT、INSERT、UPDATE、DELETE等操作，database.table表示要授权的数据库和表名，'username'@'localhost'表示要授权的用户和主机限制条件。例如，授权jack用户在mydb数据库下的数据表mytable上执行SELECT和INSERT操作：

GRANT SELECT, INSERT ON mydb.mytable TO 'jack'@'localhost';

撤销授权：使用REVOKE语句撤销已有的授权，语法格式如下：

REVOKE permission ON database.table FROM 'username'@'localhost';

需要注意的是，用户管理涉及到数据库安全性问题，因此在进行创建、修改、删除和授权操作时需要谨慎，避免对数据库造成不必要的影响或安全隐患。建议仅为需要访问数据库的用户授予最小权限，定期清理无用的用户账号，以确保数据库的安全可靠。

MySQL权限管理是为了保护数据库的安全和完整性，授权用户对数据库的某些操作有不同程度的访问权限。MySQL提供了多种类型的权限，包括全局权限、数据库级权限、表级权限、列级权限等。

以下是关于MySQL权限管理的一些基本操作：

授予权限：使用GRANT语句为用户赋予相应的权限，语法格式如下：

GRANT permission ON database.table TO 'username'@'localhost';

其中permission可以是SELECT、INSERT、UPDATE、DELETE等操作，database.table表示要授权的数据库和表名，'username'@'localhost'表示要授权的用户和主机限制条件。

例如，授权jack用户在mydb数据库下的数据表mytable上执行SELECT和INSERT操作：

GRANT SELECT, INSERT ON mydb.mytable TO 'jack'@'localhost';

撤销权限：使用REVOKE语句撤销已有的权限，语法格式如下：

REVOKE permission ON database.table FROM 'username'@'localhost';

查看权限：使用SHOW GRANTS语句来查看当前用户的权限信息，例如：

SHOW GRANTS FOR 'jack'@'localhost';

全局权限：全局权限适用于所有数据库和表，使用GRANT语句授予全局权限的语法格式如下：

GRANT permission ON *.* TO 'username'@'localhost';

例如，授权jack用户具有SELECT和INSERT全局权限，则执行以下命令：

GRANT SELECT, INSERT ON *.* TO 'jack'@'localhost';

数据库级权限和表级权限：除了全局权限，MySQL还支持数据库级权限和表级权限。使用GRANT语句授予数据库级和表级权限的语法格式如下：

# 授予某个数据库的权限
GRANT permission ON database.* TO 'username'@'localhost';

# 授予某个数据表的权限
GRANT permission ON database.table TO 'username'@'localhost';

例如，授权jack用户在mydb数据库下具有SELECT和INSERT权限，则执行以下命令：

GRANT SELECT, INSERT ON mydb.* TO 'jack'@'localhost';

或者授权jack用户在mydb数据库中的mytable数据表上具有SELECT和INSERT权限，则执行以下命令：

GRANT SELECT, INSERT ON mydb.mytable TO 'jack'@'localhost';

需要注意的是，在进行MySQL权限管理时，需要谨慎操作，避免对数据库造成不必要的影响或安全隐患。建议只授予必要的权限，并定期清理不必要的用户和权限，确保数据库的安全可靠。

11.基于Apache, PHP, MySQL搭建wordpress站点

在 Rocky 8 上安装 WordPress 可以分为以下几个步骤：

Step 1：安装 Apache 和 PHP

在 Rocky 8 上可以使用以下命令来安装 Apache 和 PHP：

sudo dnf install httpd php php-mysqlnd php-json php-gd php-xml php-mbstring php-cli

安装完成后，可以通过以下命令启动 Apache 服务：

sudo systemctl start httpd

并设置开机自启：

sudo systemctl enable httpd

Step 2：安装 MariaDB 数据库

WordPress 需要使用数据库来存储数据，推荐使用 MariaDB。可以使用以下命令在 Rocky 8 上安装 MariaDB：

sudo dnf install mariadb mariadb-server

安装完成后，可以通过以下命令启动 MariaDB 服务：

sudo systemctl start mariadb

并设置开机自启：

sudo systemctl enable mariadb

然后使用以下命令来进行初次配置：

sudo mysql_secure_installation

根据提示依次设置数据库的 root 用户密码、移除匿名用户、禁止 root 远程登录等选项。

Step 3：创建 WordPress 数据库

可以使用以下命令以 root 用户身份登录到 MariaDB 数据库：

sudo mysql -u root -p

然后输入之前设置的 root 密码。

登录成功后，可以使用以下命令来创建一个新的数据库和对应的用户：

CREATE DATABASE wordpress;
CREATE USER 'wordpressuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost';
FLUSH PRIVILEGES;

需要将上述命令中的 password 替换为自己想要设置的密码。

Step 4：安装 WordPress

可以从 WordPress 的官网上下载最新版本的 WordPress。将下载后的文件解压缩并移动到 Apache 网站目录 /var/www/html/ 下：

sudo wget https://wordpress.org/latest.tar.gz
sudo tar -xzvf latest.tar.gz
sudo mv wordpress /var/www/html/

然后将 Apache 的配置文件 /etc/httpd/conf/httpd.conf 中的 AllowOverride None 改为 AllowOverride All，以允许 WordPress 使用 .htaccess 文件来重写 URL：

sudo nano /etc/httpd/conf/httpd.conf

找到以下行：

<Directory "/var/www/html">
    AllowOverride None
    ...
</Directory>

改成：

<Directory "/var/www/html">
    AllowOverride All
    ...
</Directory>

保存并关闭文件。

Step 5：配置 WordPress

编辑 WordPress 的配置文件 /var/www/html/wordpress/wp-config.php，填写数据库相关的信息：

define('DB_NAME', 'wordpress');
define('DB_USER', 'wordpressuser');
define('DB_PASSWORD', 'password');
define('DB_HOST', 'localhost');

需要将上述命令中的 password 替换为之前设置的密码。

Step 6：完成 WordPress 安装

在浏览器中输入 http://服务器IP地址/wordpress，进入 WordPress 的安装界面。根据提示填写站点相关信息和管理员账号信息，完成 WordPress 的安装。

12.总结mysql架构原理

MySQL 是一个典型的客户端/服务端模式的数据库管理系统，它的架构主要分为以下三个部分：

连接层（Connection Layer）：处理客户端连接请求，并进行身份验证、权限验证等操作。一旦连接建立成功，它将负责管理客户端和服务端之间的通信，并通过缓存、预读等技术提高数据查询的效率。
服务层（Server Layer）：该层是 MySQL 的核心组成部分，由多个线程池、缓存和存储引擎组成。所有的 SQL 查询请求都发送到该层处理，其中相关的 SQL 解析和优化工作由 Query Cache 和 Optimizer 处理，然后将查询请求发送到相应的存储引擎中执行。
存储引擎层（Storage Engine Layer）：MySQL 支持多种存储引擎，例如 InnoDB、MyISAM 等。每个存储引擎都可以独立处理数据的读写，因此在存储引擎层中，数据会被不同的存储引擎以不同的方式进行存储和管理。

其中，还有以下几个重要的组件：

连接器（Connector）：负责连接客户端和服务端、进行身份认证和权限验证。
查询缓存（Query Cache）：缓存查询结果，提高重复查询的效率。
SQL 解析器和优化器（SQL Parser & Optimizer）：将 SQL 语句转换为执行计划，进行查询优化。
存储引擎（Storage Engine）：负责数据的存储和管理，提供不同的数据访问方式、锁机制以及事务支持等功能。

总体来说，MySQL 的架构设计非常清晰，各个组件之间分工明确、相互独立，使得 MySQL 具备高效、灵活和可扩展的特性。同时，MySQL 还提供了多种配置参数以及插件接口，能够满足各种不同的需求和场景。

13.总结MyIsam和Innodb存储引擎的区别

MyISAM 和 InnoDB 是 MySQL 中两种常用的存储引擎。它们之间有以下几个主要区别：

数据库锁定方式不同：

MyISAM 存储引擎采用表级锁定，即当对一个表进行读写操作时，该表会被锁定，其他用户无法对该表进行修改操作。这种锁定方式会导致高并发下的性能问题。

而 InnoDB 存储引擎采用行级锁定，即只锁定被访问的行，而不是整张表。这样可以避免表级锁定下的性能瓶颈和冲突，并提高数据并发处理能力。

支持事务处理机制的能力不同：

MyISAM 不支持事务，因此不支持事务处理机制和回滚操作，一旦发生错误就无法恢复。而 InnoDB 支持事务处理机制，并且具有 ACID（原子性、一致性、隔离性和持久性）属性，能够保证数据库完整性和一致性。

索引方式不同：

MyISAM 存储引擎采用 B+Tree 索引结构，适合用于无事务、插入、删除操作较多的应用场景。而 InnoDB 存储引擎也使用 B+Tree 索引结构，并支持主键、唯一键和外键。

不过，在大数据量、高并发等场景下，InnoDB 的多版本并发控制（MVCC）机制，可能会导致锁竞争问题，影响数据库的性能。

数据存储方式不同：

MyISAM 存储引擎采用表级别的压缩技术，可以节省磁盘空间，提高查询速度。而 InnoDB 存储引擎则采用基于页的存储模型，支持从磁盘上读取部分页面，一方面避免了频繁的磁盘 I/O 操作，另一方面也可以更灵活地利用内存。

总体来说，MyISAM 适合读取操作比较多、写入操作比较少的应用场景，而 InnoDB 则适合数据更新操作频繁、事务处理和并发读取的场景。需要根据具体的需求和业务场景来选择适合的存储引擎。

14.总结mysql索引作用，同时总结哪些查询不会使用到索引

MySQL索引是一种重要的数据库技术，主要用于提高数据检索效率。索引可以加速查询操作并降低数据库系统的I/O代价，从而提高系统的性能。

具体来说，索引的作用主要有以下几个方面：

快速定位：索引可以帮助MySQL快速定位到需要查询的数据，避免了全表扫描，从而加快查询速度。
降低I/O代价：索引可以减少磁盘I/O次数，节约系统资源；当MySQL需要读取数据时，通过索引可以直接读取数据所在的磁盘块，减少了磁盘寻址时间，从而提高了查询效率。
改善排序性能：如果查询语句中包含了对某个列进行排序，且该列上存在索引，则MySQL可以使用索引加速排序操作，从而提高查询效率。

总之，索引的作用在于提高MySQL的查询效率和优化数据库性能，但是过多或不必要的索引也会影响数据库性能，因此需要根据具体情况进行维护和优化。

索引列参与计算，不走索引

SELECT `username` FROM `t_user` WHERE age=20;-- 会使用索引
SELECT `username` FROM `t_user` WHERE age+10=30;-- 不会使用索引！！因为所有索引列参与了计算
SELECT `username` FROM `t_user` WHERE age=30-10;-- 会使用索引

索引列使用函数，可能不走索引

-- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT username FROM t_user WHERE concat(username,'1') = 'admin1'; 
-- 会使用索引
SELECT username FROM t_user WHERE username = concat('admin','1');

索引列使用 like 语句，可能不走索引

SELECT * FROM USER WHERE username LIKE 'mysql测试%'   --走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试'   --不走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试%'  --不走索引

数据类型隐式转换，字符串列与数字直接比较，不走索引

-- stock_code字符串类型带索引
SELECT * FROM `stock_data` WHERE stock_code = '600538'  --走索引
SELECT * FROM `stock_data` WHERE stock_code = 600538  --不走索引

尽量避免 OR 操作，只要有一个字段没有索引，该语句就不走索引，不走索引！

-- stock_code带索引，open不带索引
SELECT * FROM `stock_data` WHERE `stock_code` = '600538' OR `open` = 6.62  -- 不走索引
-- stock_code带索引，up_down_pre带索引
SELECT * FROM `stock_data` WHERE `stock_code` = '600538' OR `up_down_pre` = 5.1  -- 走索引

where id !=2 或者 where id <> 2，不走索引！

SELECT * FROM t_user WHERE username <> 'mysql测试'

is null，is not null也无法使用索引，不走索引！

SELECT * FROM t_user WHERE username IS NULL -- 不走索引
SELECT * FROM t_user WHERE username IS NOT NULL -- 不走索引

索引列使用 in 语句，可能不走索引

-- stock_code数据类型为varchar
SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538')  -- 走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538','688663','688280')  -- 走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN (大量数据)  -- 不走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN (600538)  -- 不走索引

15.总结事务ACID事务特性

ACID是数据库管理中重要的概念，是指数据库事务应该具备的四个特性，分别为原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），其详细解释如下：

原子性（Atomicity）：指事务是一个不可分割的操作单元，要么全部执行成功，要么全部执行失败。如果事务执行失败，在撤销所有操作时必须将数据库恢复到事务开始前的状态，以保证数据的正确性。
一致性（Consistency）：指在事务执行结束后，数据库的状态应该满足预定义的约束条件，如外键约束、非空约束等，确保数据的正确性。
隔离性（Isolation）：指多个事务之间相互隔离，彼此不会产生影响，每个事务应该感觉不到其他事务的存在。这样可以避免由于并发访问导致的数据异常或不一致。
持久性（Durability）：指事务完成后对数据的修改是永久性的，即使出现系统故障，也可以通过数据恢复技术将数据恢复到上次提交事务的状态。

综上所述，ACID特性确保了数据库事务的可靠性和数据一致性，是数据库管理的核心概念，也是设计高度可用、高性能数据库系统必须要考虑的因素。

16.总结事务日志工作原理

事务日志（Transaction Log）是数据库管理系统中的一种重要机制，用于记录每个事务所做出的修改操作。其工作原理可以简单概括如下：

事务启动：当用户发起一个事务时，数据库开始记录该事务的相关信息，并在事务日志中创建一个新的日志文件，以记录此次事务的所有操作。
日志记录：在事务执行过程中，数据库系统将所有对数据进行的修改操作，都以日志的形式记录下来，包括对数据的插入、更新、删除等操作。每个日志条目都包含了该操作的详细信息，如何修改数据，修改之前和之后的值等。
日志缓存：由于频繁写磁盘会影响性能，为了提高性能，数据库通常会使用缓存技术，将日志信息先暂存在内存中，等到缓存满或到达一定时间后再一次性写入硬盘。
将事务提交：当事务执行完成后，如果用户提交了该事务，那么就会触发事务的提交操作，同时也会将该事务所产生的所有日志记录写入磁盘上的日志文件中。
恢复操作：当数据库系统重新启动时，会检查每个已提交的事务是否正确执行，并根据事务日志对未提交的事务进行恢复。如果有未完成的事务，则进行回滚操作，将数据库恢复到事务开始前的状态。

综上所述，事务日志是确保数据库数据正确性和可靠性的重要手段，在数据库管理中扮演着至关重要的角色。

17.总结mysql日志类型，并说明如何启动日志

MySQL的日志类型可以分为以下几类：

错误日志（Error Log）: 记录了MySQL运行过程中出现的所有错误信息。
慢查询日志（Slow Query Log）: 记录了执行时间超过指定阈值的SQL语句，方便性能优化。
查询日志（General Query Log）: 记录了MySQL服务器收到的所有 SQL 查询语句，包括查询、更新、删除等操作。
二进制日志（Binary Log）：记录了对数据库进行任何更改操作的详细情况，以实现基于日志的数据库备份和灾难恢复。
事务日志（Transaction Log）：也称为重做日志（Redo Log），记录了已提交的事务对数据库所作的修改操作，以确保数据能够在系统故障后恢复到已提交的状态。

启动MySQL的日志需要在配置文件my.cnf中进行设置。通过以下步骤启动日志：

打开my.cnf配置文件，找到[mysqld]选项，添加或修改以下参数：

# 启用错误日志
log_error = /var/log/mysql/error.log

# 启用慢查询日志，设定记录大于10秒的查询语句
slow_query_log = 1
long_query_time = 10

# 启用查询日志
general_log = 1

# 启用二进制日志
log_bin = mysql-bin

保存配置文件并重启MySQL服务，使修改生效。可以通过以下命令重启MySQL服务：

sudo systemctl restart mysql

启用事务日志一般不需要手动配置，因为它是MySQL的默认日志类型，一般情况下不需要额外设置。

18.总结二进制日志的不同格式的使用场景

MySQL的二进制日志（Binary Log）可以分为两种不同格式：语句格式（statement-based）和行格式（row-based）。

语句格式记录的是事务中执行的SQL语句，可以用于复制、恢复、回滚等操作。但是也存在一些缺点，如对于某些复杂的语句或函数可能无法正确记录，因此可能导致数据不一致的问题。

行格式记录的是每行数据的具体修改操作，更加精确地记录了事务执行过程中的数据变化。由于记录的信息比较详细，因此行格式在进行数据复制和故障恢复时更加可靠，但是也会给硬盘写入带来更大的压力，并且日志文件会相对比较大。

使用场景：

语句格式适合轻量级的系统，查询和写入都不多，而且性能要求没有那么高的场景。

对于高性能和高可靠性要求的场景，推荐使用行格式。比如需要进行主从复制、故障恢复、数据同步等操作时，行格式可以更好地保证数据一致性，减少故障发生的概率。当然，如果存储空间受限，或者IO写入压力过大，可以选择关闭二进制日志或者调整成语句格式进行记录。

19.总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证

MySQL备份类型可以分为以下几种：

物理备份：物理备份是指备份数据库的二进制文件。这包括了数据库的数据文件、日志文件等。物理备份可以快速地恢复整个数据库，但是备份文件很大，不适合用于部分恢复。
逻辑备份：逻辑备份是指备份数据库中的数据和表结构等逻辑信息。这种备份方式通常以SQL语句的形式存储备份数据。这种方式备份文件较小，但是还原时需要先创建表结构，再导入数据，时间和空间成本较高。
增量备份：增量备份是在全量备份的基础上，只备份数据库的增量部分。当数据变化不大时，使用增量备份可以减少备份文件的大小和备份的时间。
差异备份：差异备份是在全量备份的基础上，只备份两次备份之间的差异部分。与增量备份相比，差异备份虽然备份的文件较大，但只需要还原最近一次备份和差异备份即可，节省了还原数据的时间和空间成本。

综上所述，备份类型主要分为物理备份和逻辑备份，而在备份策略方面，我们可以选择全量备份、增量备份和差异备份等方式。不同的备份类型和策略可以根据实际需求进行选择和配置。

MySQL是一个流行的开源关系型数据库管理系统，而mysqldump是MySQL提供的一种非常实用的备份恢复工具。使用mysqldump可以完成对MySQL数据库内容的备份，以及将备份数据导入到新的数据库中进行恢复验证。

备份数据库：

使用mysqldump命令连接到需要备份的数据库服务器并指定用户名和密码，例如：mysqldump -u username -p password database_name > backup_name.sql
mysqldump命令会将整个数据库备份成一个.sql文件保存在本地，备份文件名称为backup_name.sql。

恢复数据库：

使用mysql命令连接到需要恢复的数据库服务器并指定用户名和密码，例如：mysql -u username -p password
在mysql命令行下执行“source”命令，将备份数据导入到新的数据库中进行恢复，例如：source backup_name.sql

验证数据库恢复：

使用SELECT命令查询新建的数据库中数据是否正确，例如：SELECT * FROM table_name;
检查查询结果是否与备份文件中的数据一致，如果一致，则说明数据库备份和恢复验证工作完成。

总之，使用mysqldump可以方便的备份MySQL数据库，而通过将备份数据导入到新的数据库进行恢复验证，可以确保备份的完整性和正确性，从而确保了数据的安全性和有效性。

XtraBackup是一个免费的开源物理备份恢复工具，它可以备份InnoDB和XtraDB存储引擎的数据。使用XtraBackup可以完成对MySQL数据库的全量备份、增量备份以及数据库恢复验证等任务。

下面介绍基于XtraBackup完成数据库备份与恢复验证的详细步骤：

下载和安装XtraBackup

可以从Percona官网下载最新的XtraBackup安装包进行安装。

数据库备份

2.1 全量备份

使用XtraBackup命令进行全量备份，备份文件会被存储在指定的目录中。

# 使用XtraBackup进行全量备份
xtrabackup --backup --user=username --password=password --target-dir=/path/to/backup/dir

2.2 增量备份

使用XtraBackup命令进行增量备份，备份文件会被存储在指定的目录中。

# 使用XtraBackup进行增量备份
xtrabackup --backup --user=username --password=password --target-dir=/path/to/incremental/backup/dir --incremental-basedir=/path/to/full/backup/dir

数据库恢复

3.1 恢复全量备份

使用XtraBackup进行全量备份恢复。

# 使用XtraBackup恢复全量备份
xtrabackup --prepare --target-dir=/path/to/backup/dir
xtrabackup --copy-back --target-dir=/path/to/backup/dir

3.2 恢复增量备份

使用XtraBackup进行增量备份恢复，需要先使用全量备份进行基础恢复。

# 使用增量备份进行恢复
xtrabackup --prepare --apply-log-only --target-dir=/path/to/full/backup/dir
xtrabackup --prepare --apply-log-only --target-dir=/path/to/full/backup/dir --incremental-dir=/path/to/incremental/backup/dir

# 最后一次增量包不用加 --apply-log-only
xtrabackup --prepare --target-dir=/path/to/full/backup/dir --incremental-dir=/path/to/incremental/backup/dir

# 复制回数据库目录，注意数据库目录必须为空,MySQL不能启动
xtrabackup --copy-back --target-dir=/path/to/full/backup/dir

chown -R mysql:mysql /var/lib/mysql

systemctl start mysqld

数据库恢复验证

4.1 连接数据库并检查数据是否正确

使用mysql命令连接到数据库并查询数据是否正确。

# 登录MySQL服务器
mysql -u username -p password

# 查询数据库中的数据
USE database_name;
SELECT * FROM table_name;

# 检查查询结果与备份文件中的数据是否一致

以上是基于XtraBackup完成数据库备份与恢复验证的详细步骤，希望对您有所帮助。

20.编写crontab，每天按表备份所有mysql数据，将备份数据放在以天为时间的目录下

0 1 * * * mkdir -p /backup/mysql/$(date +\%Y-\%m-\%d)/ && mysqldump -u root -pPASSWORD --all-databases | gzip > /backup/mysql/$(date +\%Y-\%m-\%d)/all_databases_$(date +\%Y-\%m-\%d).sql.gz

该命令会在每天凌晨1点执行备份操作，首先创建一个以当天日期命名的目录（如 /backup/mysql/2023-06-10/），然后使用 mysqldump 命令备份 MySQL 的所有数据库并使用 gzip 进行压缩，最后将备份文件以当前日期命名并保存在创建的目录下。

需要注意的是：

该命令中需要将 PASSWORD 替换为 MySQL 的密码。
如果 MySQL 安装在非默认路径下，则需要修改 mysqldump 命令的路径。
为了避免备份文件过多导致存储空间不足，建议每隔一段时间删除旧的备份文件。

21.编写crontab, 基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份

0 2 * * 1,5 xtrabackup --user=root --password=PASSWORD /backup/full_backup_$(date +\%Y-\%m-\%d)

0 2 * * 2-4 xtrabackup --user=root --password=PASSWORD --incremental /backup/inc_backup_$(date +\%Y-\%m-\%d) --incremental-basedir=/backup/full_backup_$(date -d 'last Monday' +\%Y-\%m-\%d)

该命令会在每周的周一和周五凌晨2点执行完全备份操作，备份数据存储在以日期命名的目录 /backup/full_backup_YYYY-MM-DD 中；在周二到周四凌晨2点执行增量备份操作，备份数据存储在以日期命名的目录 /backup/inc_backup_YYYY-MM-DD 中。其中 PASSWORD 需要替换为 MySQL 的密码。

这里使用了 --incremental 参数来指定增量备份模式，并通过 --incremental-baseidr 参数指定增量备份所基于的全量备份目录。每次增量备份都需要指定前一次的全量备份目录，这里使用 date -d 'last Monday' +\%Y-\%m-\%d 命令获取上一次周一的日期，以取得前一个完全备份的目录。

需要注意的是：

在进行增量备份时，需要确保完全备份目录存在并可用。
为了避免备份文件过多导致存储空间不足，建议每隔一段时间删除旧的备份文件。

22.总结mysql主从复制原理

MySQL 主从复制是一种高可用、数据分发和负载均衡的解决方案。其基本原理是将主数据库的变更记录以二进制日志的形式写入到 master 上，然后通过网络传输将这些变更记录同步到一个或多个从数据库上，从数据库重放这些日志来实现与主数据库一致的复制数据。

主从复制的流程如下：

主数据库将修改操作记录在二进制日志（Binary Log）中；
从数据库连接到主数据库（Master）并请求复制数据，主数据库将二进制日志中的内容发送给从数据库（Slave）；
从数据库将接收到的日志存储在中继日志（Relay Log）中，并重放这些日志，将数据修改操作在从数据库上执行一遍，从而使从数据库与主数据库数据保持一致；
当主数据库发生故障无法提供服务时，可以将从数据库切换为新的主数据库，从而实现高可用。

在 MySQL 主从复制中，需要考虑以下要点：

主数据库通过二进制日志将变更记录传送给从数据库，并确保每个操作被记录只有一次；
从数据库通过中继日志接收和保存主数据库发来的二进制日志，并在重放这些日志时保证主从数据一致性；
主数据库和从数据库之间通过网络通信进行数据同步，因此需要保证网络稳定和速度足够快；
主从架构中的主数据库故障处理和从库切换需要进行合理的规划和配置，建立健壮的高可用解决方案，以确保系统的持续稳定运行。

总之，MySQL 主从复制是一种非常重要的架构模式，可以实现高可用、负载均衡和数据分发等目标，对于大型应用程序和高可用性场景下的企业应用系统来说都是非常必要的。

23.实现mysql主从复制，主主复制，半同步复制，过滤复制

MySQL主从复制是指将一个MySQL服务器的数据复制到另一个MySQL服务器上，从而实现数据的备份、负载均衡等功能。下面是实现MySQL主从复制的基本操作步骤和配置文件示例。

配置主服务器（Master）

修改主服务器（Master）的my.cnf配置文件，开启二进制日志，并设置唯一的服务ID（server-id）。具体配置如下：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=<需要复制的数据库名>

其中，server-id为唯一的服务ID号，log-bin用于开启二进制日志功能，binlog-do-db指定需要复制的数据库名。

配置从服务器（Slave）

修改从服务器（Slave）的my.cnf配置文件，设置唯一的服务ID（server-id），并启用slave服务。具体配置如下：

[mysqld]
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
read-only=1

其中，server-id为唯一的服务ID号，log-bin用于开启二进制日志功能，relay-log用于开启中继日志功能，read-only表示只读模式。

创建复制用户账户

在主服务器上创建一个只有REPLICATION SLAVE权限的账户，并记录该账户的用户名和密码，以供从服务器进行连接和访问。具体命令如下：

CREATE USER '<复制用户账户>'@'<从服务器的IP地址>' IDENTIFIED BY '<复制用户账户的密码>';
GRANT REPLICATION SLAVE ON *.* TO '<复制用户账户>'@'<从服务器的IP地址>';
FLUSH PRIVILEGES;

其中，<复制用户账户>是自定义的复制用户账户名，<从服务器的IP地址>是从服务器的IP地址。

启动从服务器复制服务

在从服务器上使用以下命令连接主服务器，并设置需要获取复制数据的二进制日志文件名和位置信息，以及连接主服务器所需的账户名和密码：

CHANGE MASTER TO
MASTER_HOST='<主服务器的IP地址>',
MASTER_USER='<复制用户账户>',
MASTER_PASSWORD='<复制用户账户的密码>',
MASTER_LOG_FILE='<主服务器的二进制日志文件名>',
MASTER_LOG_POS=<主服务器的二进制日志位置>,
MASTER_CONNECT_RETRY=10;

其中，<主服务器的IP地址>是主服务器的IP地址，<复制用户账户>是刚才创建的复制用户账户，<主服务器的二进制日志文件名>和<主服务器的二进制日志位置>是使用SHOW MASTER STATUS命令获取的信息。最后，执行START SLAVE命令启动从服务器的复制服务。

START SLAVE;

[mysqld]
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
read-only=1

以上就是实现MySQL主从复制的基本操作步骤和配置文件示例。

MySQL主主复制（Master-Master Replication）是指在两个或多个MySQL服务器之间建立双向复制关系，每个服务器既可以作为主服务器，也可以作为从服务器，实现双向数据同步。下面是实现MySQL主主复制的基本操作步骤。

配置数据库服务器：在两台或多台MySQL服务器上，分别修改my.cnf文件，设置唯一的服务ID（server-id），并开启二进制日志。
创建复制用户账户：在所有服务器上创建一个只有REPLICATION SLAVE权限的账户，并记录该账户的用户名和密码，以供其他服务器进行连接和访问。
配置主服务器1：在主服务器1上使用以下命令设置从服务器1的相关信息：

CHANGE MASTER TO
MASTER_HOST='<主服务器2的IP地址>',
MASTER_USER='<复制用户账户>',
MASTER_PASSWORD='<复制用户账户的密码>',
MASTER_LOG_FILE='<主服务器2的二进制日志文件名>',
MASTER_LOG_POS=<主服务器2的二进制日志位置>,
MASTER_CONNECT_RETRY=10;

配置主服务器2：在主服务器2上使用以下命令设置从服务器2的相关信息：

CHANGE MASTER TO
MASTER_HOST='<主服务器1的IP地址>',
MASTER_USER='<复制用户账户>',
MASTER_PASSWORD='<复制用户账户的密码>',
MASTER_LOG_FILE='<主服务器1的二进制日志文件名>',
MASTER_LOG_POS=<主服务器1的二进制日志位置>,
MASTER_CONNECT_RETRY=10;

启动复制服务：在主服务器1和主服务器2上分别执行START SLAVE命令，启动复制服务。
测试：在任意一个主服务器上进行数据修改操作，然后在另外一个主服务器上使用SELECT语句检查是否同步成功。

以上就是实现MySQL主主复制的基本操作步骤。需要注意的是，在这种情况下，主服务器之间相互复制的数据可能会出现冲突，因此我们需要采取一些措施，例如设置自增ID，或者使用额外的应用程序实现数据同步冲突的解决。

24.总结GTID复制原理，并完成GTID复制集群

GTID（Global Transaction ID）全局事务标识，是MySQL5.6引入的一种用来标识唯一事务的方式，其原理在于每个事务都有一个唯一的、全局唯一的ID，可以通过这个ID来确定哪些事务已经复制到了从库并且哪些没有。

GTID复制原理很简单，主库在生成新的事务时为其分配一个全局唯一的ID，并将该事务及其ID记录到binlog中。从库在读取主库的binlog时会将其中的GTID信息记录到自己的relay log中，并在执行该事务时将该事务的GTID标记为已经复制完成。之后如果有新的事务产生，从库会通过比较主库和从库的GTID信息来确定哪些事务需要进行复制。

要完成一个GTID复制集群，我们需要按照以下步骤操作：

首先需要在主库和从库上都启用GTID模式。可以在my.cnf文件中设置gtid_mode=ON。
配置主库和从库之间的复制关系，可以使用CHANGE MASTER TO命令或者在my.cnf中指定replication参数。
在主库上创建一个用于从库连接的复制用户，并授予REPLICATION SLAVE权限。
启动从库，并使其连接到主库。
确认主从同步成功。可以通过SHOW SLAVE STATUS命令查看同步状态。
如果需要添加更多的从库，可以重复2-5步骤。在新的从库上启动时需要指定CHANGE MASTER TO语句。

完成以上步骤后，我们就成功搭建了一个GTID复制集群。在该集群中，所有的事务都会被唯一地标识，从而避免了因为复制冲突而导致的数据不一致问题。

25.总结主从复制不一致的原因，如何解决不一致，如何避免不一致

主从复制不一致的原因可以分为以下几种情况：

主库和从库之间的网络延迟导致从库无法及时收到更新。这种情况可以通过优化网络环境来解决，例如加速网络带宽或者使用高速网络设备。
主库和从库之间的时钟不同步。这种情况可以通过使用NTP服务来同步主从库之间的时间。
在进行主从复制时，可能会出现意外故障，例如主库崩溃或者复制线程异常终止等。这种情况可以通过对主从库进行监控和预警，以便及时发现和解决异常。
在主库中进行DDL操作时，由于从库复制的是基于语句的复制方式，可能会导致从库与主库的表结构不一致，从而无法同步数据。这种情况可以使用基于行的复制方式（row-based replication）或者禁止在主库中进行DDL操作来解决。
如果主从库所使用的字符集和校对规则不一致，也可能导致数据不一致的情况。这种情况可以通过在主从库之间使用相同的字符集和校对规则来避免。

为了解决主从复制不一致的问题，可以采取以下几种措施：

配置主从复制的监控和报警机制，对主从库的状态和数据同步情况进行实时监控。
定期备份主库中的数据，并在从库上进行数据恢复验证，确保数据的一致性。
避免在主库中进行DDL操作，或者采用基于行的复制方式，以减少不一致的可能性。
在主从库之间使用相同的字符集和校对规则，避免由字符集不一致导致的数据不一致情况。

为了避免主从复制不一致的问题，可以采取以下几种措施：

使用高速网络设备，提高主从库之间的通信带宽和稳定性。
采用基于行的复制方式，避免由语句不一致导致的数据不一致情况。
配置主从库的时钟同步服务，确保主从库之间的时间一致。
配置监控和报警机制，对主从库的状态和数据同步情况进行实时监控。及时处理异常情况。

26.总结数据库水平拆分和垂直拆分

数据库拆分是指将一个大型的数据库拆成多个小型数据库，以提高数据库性能和可扩展性的一种方法。数据库拆分包括水平拆分和垂直拆分两种方式。

水平拆分：

水平拆分是指把一个表中的数据根据某一规则分散到不同的服务器或节点上，使单个节点的负载得到均衡，也方便水平扩展。通常是按照某个字段的值范围来进行划分，比如按照用户ID、地理位置等，将不同的数据段存储在不同的服务器或节点上，这样可以使查询时只需要操作部分表数据，从而提高查询效率。

优点：

提高了数据库的并发处理能力和吞吐量。
加强了数据查询的并行度，降低了单节点的压力。
方便集群的动态扩容，提高了系统的可伸缩性。

缺点：

业务系统需要对分布式环境进行支持，并且必须兼顾数据一致性和事务处理。
不同的节点上的数据可能会相互影响，难以进行数据统计和报表查询。

垂直拆分：

垂直拆分是指将一个大型的表拆分为多个小型的表，每个小型的表只包含相关性较强的字段。通常是按照数据表中的列来进行分割，不同的列存储在不同的表中。它的实现方式可以通过对关系型数据库进行嵌套、继承或者视图等技术手段来进行实现。

优点：

使数据库中的冗余数据被消除，提高了查询效率。
减少了表或记录级别锁的争用，从而提高了并发处理能力。
利于对大量数据的备份和还原操作，实现快速的恢复。

缺点：

不同表之间的关联需要通过关联查询来实现，增加了查询的成本。
难以解决跨表查询的性能问题。
可能会导致应用程序的复杂性增加，需要对应用系统进行一定的改造。

27.基于mycat实现读写分离

Mycat是一款开源的分布式数据库中间件，可以实现对 MySQL 分片、读写分离、负载均衡等功能。基于 Mycat 实现读写分离可以让查询请求分发到不同的 MySQL 节点上，从而提高数据库的并发能力和性能。

在 Mycat 的配置文件中，需要定义多个数据节点，每个数据节点对应一个 MySQL 实例，其中一个 dataHost 对应一个写节点，多个 dataHost 对应多个读节点。具体配置如下：

<schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" />

<!--定义一个dataNode-->
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />

<!--定义一个dataHost: 读写分离-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
          writeType="0" dbType="mysql" dbDriver="native"
          switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- 读库列表 -->
    <readHost host="192.168.0.1" url="192.168.0.1:3306"
              user="user" password="password" />

    <readHost host="192.168.0.2" url="192.168.0.2:3306"
              user="user" password="password" />

    <!-- 写库列表 -->
    <writeHost host="192.168.0.3" url="192.168.0.3:3306"
               user="user" password="password" />
</dataHost>

28.✳总结mysql高可用方案及高可用级别，搭建MHA集群和galera cluster，尝试搭建TIDB集群

方案	高可用级别
主从复制	98-99.9%
主主复制	99%
SAN (Storage Area Network)	99.5-99.9%
DRBD,MHA,Tungsten Replicator	99.9%
NDBCluster,Galera Cluster	99.999%

29.总结mysql配置最佳实践

MySQL 是一个广泛使用的关系型数据库管理系统，在进行 MySQL 配置时，需要考虑多个方面来实现最佳实践。以下是几方面建议：

确定正确的 MySQL 版本：选择适合业务需求的 MySQL 版本，同时将 MySQL 与其他软件版本相匹配。
配置正确的存储引擎：选择正确的存储引擎可以大大提高 MySQL 数据库的性能和可靠性。常用的存储引擎包括 InnoDB 和 MyISAM。
适当地配置缓存：通过适当地配置缓存，可以显著提高 MySQL 数据库的性能。在 MySQL 中，可以使用查询缓存、内存缓存和磁盘缓存等不同类型的缓存。
合理地配置日志：日志记录对 MySQL 数据库的性能和可靠性非常重要。MySQL 提供了多种类型的日志记录功能，如二进制日志、慢查询日志和错误日志等。你应该根据自己的业务需求合理地配置这些日志。
定期备份数据库：定期备份数据库可以确保在出现故障或者数据丢失时可以快速恢复数据。你应该选择一个可靠的备份策略，并确保备份数据的完整性和一致性。
优化查询语句：查询是数据库中最常见的操作之一，优化查询语句可以显著提高 MySQL 数据库的性能。你应该针对查询语句进行性能调优，并使用索引来优化查询速度。
安全性配置：在 MySQL 中，安全性是非常重要的。你应该定期更新 MySQL 的安全补丁，并正确地配置访问控制。

以上是一些 MySQL 配置的最佳实践建议。在进行 MySQL 配置时，需要考虑业务需求、数据量、硬件设备等多个因素。

30.✳总结openvpn原理，并完成1键安装不同版本vpn脚本，可以适配rocky, ubuntu, centos主机。同时支持添加账号，注销账号

31.✳配置LAMP，要求域名使用主从dns, dns解析到2个apache节点，apache和php在同一个节点，mariadb使用mycat读写分离并且要求后端为MHA集群。架构规划图及解析一次请求和响应的流程和实践过程

标签：N74,数据库,作业,查询,第六周,MySQL,NULL,备份,SELECT
From： https://blog.51cto.com/u_15954840/6444283

1.完成将server和client端的mysql配置默认字符集为utf8mb4

2.掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

3.总结mysql常见的数据类型

4.创建一个主机表host，放在testdb中，要求字段

5.给testdb.host表中添加多条数据

6.根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例

DDL（Data Definition Language）是数据库语言中的一类，用于定义数据库结构、模式和约束等。常用的DDL命令有以下几种：

DML（Data Manipulation Language）是数据库语言中的一类，用于操作数据库中的数据，如查询、插入、更新和删除数据等。常见的DML命令有以下几种：

7.导入hellodb库，总结DQL, alias, where子句，group by, order by, limit, having使用示例

8.基于hellodb库, 总结子查询，关联查询，交叉连接，内连接，左连接，右连接，完全连接，自连接

子查询是指将一个SQL语句嵌套在另一个SQL语句中，并使用内部查询的结果作为外部查询的条件来筛选数据。子查询通常用于需要动态获取条件的情况下，比如在WHERE子句中使用子查询来动态筛选数据。

联合查询（UNION） 是 SQL 中一种常见的数据合并技术，用于在两个或多个 SELECT 语句的结果中取并集。

内连接（INNER JOIN）是 SQL 中常见的表格连接技术之一，也是最常用的一种。它使用一个或多个列作为键在两个或多个表格之间建立连接，返回符合连接条件的行。

左连接（LEFT JOIN 或 LEFT OUTER JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及左表格中未匹配的行。

右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及右表格中未匹配的行。

完全连接（FULL OUTER JOIN 或 FULL JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及左右表格中未匹配的行。

自连接（Self Join）是一种在同一个表内进行连接的操作。具体来说，自连接是指将一个表格视为两个不同的表格，并在这两个“表格”之间进行连接。

9.总结SELECT语句处理顺序

10.总结MySQL事件管理，用户管理，权限管理

MySQL事件是一种定时任务，可以在指定时间点自动执行一些指定的SQL语句。使用MySQL事件管理功能可以方便地实现各种定时操作，比如备份、统计、清理数据等。

MySQL用户管理是指对数据库用户账号进行创建、修改、删除和权限控制等操作的过程。以下是关于MySQL用户管理的一些基本操作：

MySQL权限管理是为了保护数据库的安全和完整性，授权用户对数据库的某些操作有不同程度的访问权限。MySQL提供了多种类型的权限，包括全局权限、数据库级权限、表级权限、列级权限等。

11.基于Apache, PHP, MySQL搭建wordpress站点

12.总结mysql架构原理

13.总结MyIsam和Innodb存储引擎的区别

14.总结mysql索引作用，同时总结哪些查询不会使用到索引

MySQL索引是一种重要的数据库技术，主要用于提高数据检索效率。索引可以加速查询操作并降低数据库系统的I/O代价，从而提高系统的性能。

索引列参与计算，不走索引

索引列使用函数，可能不走索引

索引列使用 like 语句，可能不走索引

数据类型隐式转换，字符串列与数字直接比较，不走索引

尽量避免 OR 操作，只要有一个字段没有索引，该语句就不走索引，不走索引！

where id !=2 或者 where id <> 2，不走索引！

is null，is not null也无法使用索引，不走索引！

索引列使用 in 语句，可能不走索引

15.总结事务ACID事务特性

16.总结事务日志工作原理

17.总结mysql日志类型，并说明如何启动日志

18.总结二进制日志的不同格式的使用场景

19.总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证

MySQL备份类型可以分为以下几种：

MySQL是一个流行的开源关系型数据库管理系统，而mysqldump是MySQL提供的一种非常实用的备份恢复工具。使用mysqldump可以完成对MySQL数据库内容的备份，以及将备份数据导入到新的数据库中进行恢复验证。

XtraBackup是一个免费的开源物理备份恢复工具，它可以备份InnoDB和XtraDB存储引擎的数据。使用XtraBackup可以完成对MySQL数据库的全量备份、增量备份以及数据库恢复验证等任务。

20.编写crontab，每天按表备份所有mysql数据，将备份数据放在以天为时间的目录下

21.编写crontab, 基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份

22.总结mysql主从复制原理

23.实现mysql主从复制，主主复制，半同步复制，过滤复制

MySQL主从复制是指将一个MySQL服务器的数据复制到另一个MySQL服务器上，从而实现数据的备份、负载均衡等功能。下面是实现MySQL主从复制的基本操作步骤和配置文件示例。

MySQL主主复制（Master-Master Replication）是指在两个或多个MySQL服务器之间建立双向复制关系，每个服务器既可以作为主服务器，也可以作为从服务器，实现双向数据同步。下面是实现MySQL主主复制的基本操作步骤。

24.总结GTID复制原理，并完成GTID复制集群

25.总结主从复制不一致的原因，如何解决不一致，如何避免不一致

26.总结数据库水平拆分和垂直拆分

水平拆分：

垂直拆分：

27.基于mycat实现读写分离

28.✳总结mysql高可用方案及高可用级别，搭建MHA集群和galera cluster，尝试搭建TIDB集群

29.总结mysql配置最佳实践

30.✳总结openvpn原理，并完成1键安装不同版本vpn脚本，可以适配rocky, ubuntu, centos主机。同时支持添加账号，注销账号

31.✳配置LAMP，要求域名使用主从dns, dns解析到2个apache节点，apache和php在同一个节点，mariadb使用mycat读写分离并且要求后端为MHA集群。架构规划图及解析一次请求和响应的流程和实践过程

相关文章

赞助商

阅读排行

联合查询（UNION）是 SQL 中一种常见的数据合并技术，用于在两个或多个 SELECT 语句的结果中取并集。