1、 导入hellodb.sql生成数据库

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
(2) 以ClassID为分组依据，显示每组的平均年龄
(3) 显示第2题中平均年龄大于30的分组及平均年龄
(4) 显示以L开头的名字的同学的信息

[root@Centos8 ~]# dnf install -y mariadb-server;systemctl enable --now mariadb
[root@Centos8 ~]# mysql < hellodb_innodb.sql
MariaDB [hellodb]> select name,age from students where age >25 and gender ='M';
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Xu Xian      |  27 |
| Sun Dasheng  | 100 |
+--------------+-----+
7 rows in set (0.001 sec)
MariaDB [hellodb]> select classid,avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       1 |  20.5000 |
|       2 |  36.0000 |
|       3 |  20.2500 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
8 rows in set (0.001 sec)

MariaDB [hellodb]> select classid,avg(age) from students group by classid having AVG(age) > 30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       2 |  36.0000 |
|       5 |  46.0000 |
+---------+----------+
3 rows in set (0.004 sec)
MariaDB [hellodb]> select * from students where name like 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)

2、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

mysql8.0前，创建并赋权
grant Usage on *.* to magedu@'192.168.0.%' identified by '123';
mysql8.0,必须先创建后赋权
create user magedu@'192.168.1.%' identified by '123';
grant Usage on *.* to magedu@'192.168.1.%';

2、总结mysql常见的存储引擎以及特点。

MyISAM 引擎特点

• 不支持事务
• 表级锁定
• 读写相互阻塞，写入不能读，读时不能写
• 只缓存索引
• 不支持外键约束
• 不支持聚簇索引
• 读取数据较快，占用资源较少
• 不支持MVCC（多版本并发控制机制）高并发
• 崩溃恢复性较差
• MySQL5.5.5 前默认的数据库引擎
  MyISAM 存储引擎适用场景
• 只读（或者写较少）
• 表较小（可以接受长时间进行修复操作）
  MyISAM 引擎文件
• tbl_name.frm 表格式定义
• tbl_name.MYD 数据文件
• tbl_name.MYI 索引文件

InnoDB引擎特点

• 行级锁
• 支持事务，适合处理大量短期事务
• 读写阻塞与事务隔离级别相关
• 可缓存数据和索引
• 支持聚簇索引
• 崩溃恢复性更好
• 支持MVCC高并发
• 从MySQL5.5后支持全文索引
• 从MySQL5.5.5开始为默认的数据库引擎

InnoDB数据库文件

• 所有InnoDB表的数据和索引放置于同一个表空间中
• 每个表单独使用一个表空间存储表的数据和索引
• 启用：innodb_file_per_table=ON (MariaDB 5.5以后版是默认值)
• 参看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_pe
  r_table

3、总结MySQL查询缓存优化总结。

MySQL查询缓存解释
缓存完整的SELECT查询结果，也就是查询缓存。保存查询返回的完整结果。当查询命中该缓存，mysql会立刻返回结果，跳过了解析、优化和执行阶段，查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有数据都将失效

命中条件
　　Mysql判断缓存命中的方法很简单：缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括如下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些都有可能影响返回结果信息。
　　当判断查询缓存是否命中时，Mysql不会解析、正规化或者参数化的查询语句，而是直接使用Sql语句和客户端发送过来的其他原始信息(Sql)。任何字符上的不同，例如注释，任何的不同都会导致缓存不命中，所以在编写Sql语句的时候，需要特别注意这一点，通常使用统一的编码规则是一个好的习惯，在这里这个好习惯可能让你的系统运行的更快

当查询语句有一些不确定的数据时，则不会被缓存，例如白喊函数NOW()或者CURRENTDATE()的查询不会被缓存

如果查询语句中包含任何不确定的函数，那么在查询缓存中是不可能找到缓存结果的，即使之前刚刚执行这样的查询

导致没有命中条件
1、缓存碎片
2、内存不足
3、数据修改

特别注意
Mysql的查询缓存在很多时候可以提升查询性能，在使用的时候，有一些问题需要特别注意。首先，打开查询缓存对 读，写 操作都会带来额外的消耗：
1、读查询在开始之前必须先检查是否命中缓存
2、如果这个读查询可以被缓存，那么当完成执行后，Mysql若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统开销
3、这对写操作也会影响，因为当向某个表写入数据的时候，Mysql必须将对应表的所有缓存都设置失效。
4、对于存储引擎InnoDB用户来说，事务的一些特性会限制查询缓存的作用。当一个语句在事务中修改某个表，Mysql会将这个表对应的查询缓存都设置失效。在事务提交前该表的查询都无法被缓存，只能在事务提交后才能被缓存。因此长时间运行的事务，会大大降低查询缓存的命中率
5、inner JOIN 和 其他连接 查询 如果其中一个表数据发生变化 则直接导致 缓存失效

缓存配置参数
query_cache_type: 是否打开缓存
可选项

1. OFF: 关闭
2. ON: 总是打开
3. DEMAND: 只有明确写了SQL_CACHE的查询才会吸入缓存

如果不想所有查询都进入查询缓存，但是又希望某些查询走查询缓存，那么可以将 query_cache_type 设置成 DEMAND ，然后在希望缓存的查询上加上SQL_CACHE。这虽然需要在查询中加入额外的语法，但是可以让你非常自由的控制那些查询需要被缓存。相反如果不希望缓存 加上SQL_NO_CACHE

4、MySQL日志各类总结。

1. 概述
与大多数关系型数据库，日志文件是MySQL数据库的一个重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。
这些日志能够帮助我们定位mysqld内部发生的事件，数据库性能故障。记录数据的变更历史，用户恢复数据库等等。

2. mysql日志组成
（1）错误日志：记录启动、运行或停止mysqld时出现的问题。
（2）通用日志：记录建立的client连接和运行的语句。
（3）更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。
（4）二进制日志：记录全部更改数据的语句。还用于复制。
（5）慢查询日志：记录全部运行时间超过long_query_time秒的全部查询或不使用索引的查询。
（6）Innodb日志：innodb redo log
缺省情况下，全部日志创建于mysqld数据文件夹中。
能够通过刷新日志。来强制mysqld来关闭和又一次打开日志文件（或者在某些情况下切换到一个新的日志）。
当你运行一个FLUSH LOGS语句或运行mysqladmin flush-logs或mysqladmin refresh时，则日志被老化。
对于存在MySQL复制的情形下，从复制server将维护很多其它日志文件，被称为接替日志。

3. 通用查询日志
3.1 启动和设置通用查询日志
通用查询日志记录了mysql的所有用户操作，包括启动和关闭服务、执行查询和更新语句等。
mysql服务器默认情况下并没有开启通用查询日志。如果需要通用查询日志，可以通过修改my.ini或my.cnf配置文件来开启。在my.ini或my.cnf的[mysqld]组下加入log选项或者启动mysql时通过–log[=file_name]或-l [file_name]选项启动它。假设没有给定file_name的值， 默认名是host_name.log。通用查询日志将默认存储在mysql数据目录中的hostname.log文件中。hostname是mysql数据库的主机名
my.in中形式如下：

[mysqld]
log[=path/[filename]]

mysqld依照它接收的顺序记录语句到查询日志。这可能与运行的顺序不同。
不同于更新日志和二进制日志，它们在查询运行后。可是不论什么一个锁释放之前记录日志。
查询日志包括全部语句，而二进制日志不包括仅仅查询数据的语句。
server又一次启动和日志刷新不会产生新的一般查询日志文件。

3.2 查看通用查询日志
通用查询日志中记录了用的所有操作。通过查看通用查询日志，可以了解用户对mysql进行的操作。通用查询日志是以文本文件形式存储在文件系统中的，可以使用文本编辑器直接打开通用日志文件进行查看，Windows下可以使用记事本,Linux下可以使用vim、gedit等

3.3 删除通用查询日志
通用查询日志是以文本文件的形式存储在文件系统中的。通用查询日志记录用户的所有操作，因此在用户查询、更新频繁的情况下，通用查询日志会增长得很快。DBA可以定期删除比较早的通用日志，以节省磁盘空间，可以用直接删除日志文件的方式删除通用查询日志。要重新建立新的日志文件，可使用语句：

mysqladmin -flush logs

或者直接删除日志文件。
3.4 通用查询日志的系统变量
log_output=[none|file|table|file,table] #通用查询日志输出格式
general_log=[on|off] #是否启用通用查询日志
general_log_file[=filename] #通用查询日志位置及名字

4. 二进制日志

二进制日志就是我们经常说的binlog，主要记录mysql数据库的变化。
二进制日志以一种有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。
二进制日志包含关于每个更新数据库的语句的执行时间信息。他不包含没有修改任何数据的语句，例如select语句
使用二进制日志的最大目的是最大可能地恢复数据库，因为二进制日志包含备份后进行的所有更新.

4.1 启动和设置二进制日志
默认情况下，二进制日志是关闭的，可以通过修改mysql的配置文件来启动和设置二进制日志,my.ini中[mysqld]组下面有几个设置是关于二进制日志的：

log-bin[=PATH/[FILENAME]]
expire_logs_days=10
max_binlog_size=100M

log-bin定义开启二进制日志；path表明日志文件所在的目录路径；
filename指定了日志文件的名称，如文件的全名是filename.0001，filename.0002等，除了上述文件之外，还有一个成为filename.index的文件，文件内容为所有日志的清单，可以使用记事本打开该文件filename.index文件的内容，当前只有一个binlog文件：.\C:\ProgramData\MySQL\MySQL Server 5.7\logs\bin-log.000001
expire_logs_days定义了mysql清除过期日志的时间，即二进制日志自动删除的天数。默认值为0，表示“没有自动删除”。当mysql启动或刷新二进制日志时可能删除该文件
max_binlog_size定义了单个文件的大小限制，如果二进制日志写入的内容大小超出给定值，日志就会发生滚动（关闭当前文件，重新打开一个新的日志文件）。不能将该变量设置为大于1GB或小于4096字节。默认值是1GB，如果正在使用大事务 ，二进制日志文件大小还可能超过max_binlog_size的定义大小。
可以通过SHOW VARIABLES语句来查询日志设置，使用show VARIABLES 语句查看日志设置

image.png

可以看到log_bin为ON，max_binlog_size为104857600字节，换算为MB为100MB，MYSQL重新启动之后，就可以看到新产生的文件后缀为.000001和.index的两个文件，文件名称默认为主机名称，我已经在配置文件中指定了文件名，所以不会以默认主机名生成，如下，因为我之前就开启了二进制日志，所以重启后后缀为000002：

4.2 查看二进制日志
mysql二进制日志是经常用到的。当mysql创建二进制日志文件时，首先创建一个以filename为名称，以index为后缀的文件；
再创建一个以filename为名称，以“.000001”为后缀的文件。当mysql服务重新启动一次，以“.000001”为后缀的文件会增加一个，
并且后缀名加1递增；如果日志长度超过了max_binlog_size的上限（默认是1GB）也会创建一个新的日志文件
show binary logs语句可以查看当前二进制日志文件个数和文件名。mysql二进制日志并不能直接查看，如果要查看日志内容，
可以通过mysqlbinlog命令查看,使用show binary logs语句查看二进制日志文件个数和文件名:

image.png

可以看到，当前有两个二进制日志文件，因为我把mysql服务重启了一次，日志文件的个数和mysql服务启动的次数相同。每启动一次mysql服务，将会产生一个新的日志文件,使用mysqlbinlog查看二进制日志,mysqlbinlog是一个单独的exe，需要在命令行里执行我们把binlog文件里面的内容导出到binlog.txt

4.3 删除二进制日志
mysql的二进制日志可以配置自动删除，同时mysql也提供了安全的手动删除二进制日志的方法，删除所有的二进制日志文件使用

RESET MASTER;

执行该语句，所有二进制日志将被删除，mysql 会重新创建二进制日志，新的日志文件扩展名将重新从000001开始编号，只删除部分二进制日志文件使用PURGE MASTER LOGS;语法如下：

PURGE {MASTER | BINARY} LOGS TO
'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'

第一种方法指定文件名，执行该命令将删除文件名编号比指定文件名编号小的所有日志文件
第二种方法指定日期，执行该命令将删除指定日期以前的所有日志文件。

4.4 查看二进制日志里的操作记录
使用如下命令：show binlog events;
比如想查看某一个二进制日志里面的记录，但又不想用mysqlbinlog，可以使用show binlog events
比如我想查看’bin-log.000002′这个binlog文件的内容，执行如下命令:

image.png

可以看到’bin-log.000002′这个binlog文件记录了哪些SQL命令,如果想知道binlog文件的创建时间，就需要mysqlbinlog工具来查看

4.5 使用二进制日志还原数据库
如果mysql服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用mysqlbinlog工具从指定的时间点开始（例如，最后一次备份）直到现在，或另外一个指定的时间点的日志中恢复数据,要想从二进制日志恢复数据，需要知道当前二进制日志文件的路径和文件名。一般可以从配置文件（即my.cnf或者my.ini，文件名取决于mysql服务器的操作系统）中找到路径.
mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename |mysql -uuser -ppass

option是一些可选项，filename是日志文件名,比较重要的两对option参数是
–start-datetime、–stop-datetime
–start-position、–stop–position
–start-date、–stop-date可以指定恢复数据库的起始时间点和结束时间点
–start-position、–stop–position可以指定恢复数据的开始位置和结束位置

4.6 暂时停止二进制日志
如果在mysql的配置文件配置启动了二进制日志，mysql会一直记录二进制日志，修改配置文件，可以停止二进制日志，
但是需要重启mysql数据库。mysql提供了暂时停止二进制日志的功能。通过 SET SQL_LOG_BIN 语句可以使mysql暂停或者启动二进制日志

SET sql_log_bin={0|1}

其中0是停止，其实启用

5. 错误日志

错误日志文件包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
在MYSQL中，错误日志也是非常重要的，mysql将启动和停止数据库信息以及一些错误信息记录到错误日志中
5.1 启动和设置错误日志
在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为hostname.err。
例如：mysql所在服务器主机名为mysql-db，记录错误信息的文件名为mysql-db.err。如果执行了FLUSH LOGS，错误日志文件会重新加载
错误日志的启动和停止以及日志文件名，都可以通过修改my.ini（或者my.cnf）来配置。错误日志的配置项是log-error。
在[mysqld]下配置log-error，在启动错误日志。如果需要指定文件名，则配置项如下：

[mysqld]
log-error=[path/[file_name]]

path为日志文件所在的目录路径，filename为日志文件名。修改配置项后，需要重启mysql服务才生效.

5.2 查看错误日志
通过错误日志可以监视系统的运行状态，便于及时发现故障，修复故障。mysql错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看mysql错误日志,如果不知道日志文件的存储路径，可以使用 show variables; 语句查看错误日志的存储路径。
语句如下:

show variables LIKE 'log_error';

5.3 删除错误日志
mysql的错误日志以文本文件的形式存储在文件系统中，可以直接删除,对于mysql5.5.7以前的版本，flush logs可以将错误日志文件重命名为filename.err_old，并创建新的日志文件。但是从mysql5.5.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。
如果日志文件不存在，mysql启动或者执行flush logs时会创建新的日志文件,在运行状态下删除错误日志文件后，mysql并不会自动创建日志文件。flush logs在重新加载日志的时候，如果文件不存在，则会自动创建。所以在删除错误日志之后，如果需要重建日志文件需要在服务器端执行以下命令：

mysqladmin -u root -p flush-logs

或者在客户端登录mysql数据库，执行flush logs语句

flush logs;

删除err文件，并用flush logs语句重建log-error文件.

6. 慢查询日志

慢查询日志是记录查询时长超过指定时间的日。慢查询日志主要用来记录执行时间较长的查询语句,通过慢查询日志，可以找出执行时间较长、执行效率较低的语句，然后进行优化

6.1 启动和设置慢查询日志
mysql中慢查询日志默认是关闭的，可以通过配置文件my.ini或my.cnf中的log-slow-queries选项打开，也可以在mysql服务启动的时候使用–log–slow-queries[=file_name]启动慢查询日志。启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阀值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志
文件中。
在my.ini或者my.cnf文件中开启慢查询日志的配置如下：

slow-query-log=1
slow_query_log_file="DESKTOP-EJUQL8D-slow.log"
long_query_time=10

path为日志文件所在目录路径，filename为日志文件名。如果不指定目录和文件名称，默认存储在数据目录中文件名为hostname-slow.log，hostname是mysql服务器的主机名。参数n是时间值，单位是秒。如果没有设置long-query_time选项，默认时间为10秒

6.2 查看慢查询日志
mysql的慢查询日志是以文本形式存储的，可以直接使用文本编辑器查看。在慢查询日志中，记录着执行时间较长的查询语句，用户可以从慢查询日志中获取执行效率较低的查询语句，为查询优化提供重要的依据,查看慢查询日志的一些参数

show variables like '%slow%';

6.3 删除慢查询日志
和通用查询日志一样，慢查询日志也可以直接删除。删除后在不重启服务器的情况下，需要执行

mysqladmin -u root -p flush logs

重新生成日志文件，或者在客户端登录到服务器执行 flush logs; 语句重建日志文件,官方mysql的慢查询日志在这里有一个缺陷，就是查询阀值只能是1秒或以上，如果要设置一秒以下就无能为力了,这时候如果想找出1秒以下的慢查询SQL，可以使用percona提供的microslow-patch来突破限制，将慢查询时间阀值减小到毫秒级别

7. 总结

平时应打开哪些日志
日志既会影响mysql的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地开启日志。
根据不同的使用环境，考虑开启不同的日志。
例如开发环境中优化查询效率低的语句，可以开启慢查询日志，或者生产环境中发现某些SQL执行特别慢也可以开启
如果磁盘空间不是特充足可以在高峰期间开启，在捕获到查询慢的SQL之后再关闭慢查询日志
如果需要搭建复制环境，那么就一定要开启二进制日志，如果数据特别重要也建议开启二进制日志，以便数据库损坏的时候也可以通过二进制日志
挽救一部分数据
通用日志无论在哪种情况下，一般不建议开启