Linux学习记录6

1. 简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序

• DDL（DataDefinitionLanguage）：数据定义语言，用来定义数据库对象：库、表、列等；

• DML（DataManipulationLanguage）：数据操作语言，用来定义数据库记录（数据）；

• DQL（DataQueryLanguage）：数据查询语言，用来查询记录（数据）；

• DCL（DataControlLanguage）：数据控制语言，用来定义访问权限和安全级别。

mysql各个关键字查询时候的先后顺序

1.查询中用到的关键词主要包含六个，并且他们的顺序依次为select--from--where--group by--having--order by其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行。

from--where--group by--having--select--order by

• from:需要从哪个数据表检索数据
• where:过滤表中数据的条件
• group by:如何将上面过滤出的数据分组
• having:对上面已经分组的数据进行过滤的条件
• select:查看结果集中的哪个列，或列的计算结果
• order by :按照什么样的顺序来查看返回的数据

2.from后面的表关联，是自右向左解析的而where条件的解析顺序是自下而上的。也就是说，在写SQL文的时候，尽量把数据量大的表放在最右边来进行关联，而把能筛选出大量数据的条件放在where语句的最下面。

2. 自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。

1）以classid为分组，统计出每个班级的人数，只显示前三个

mysql> select classid, count(*) 数量 from students group by classid limit 3;
+---------+--------+
| classid | 数量   |
+---------+--------+
|       2 |      3 |
|       1 |      4 |
|       4 |      4 |
+---------+--------+
3 rows in set (0.01 sec)

2）统计每个班的男生和女生人数，并按照班级号排序

mysql> select classid,gender, count(*)   数量 from students group by classid,gender order by classid;
+---------+--------+--------+
| 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.00 sec)

3）统计每个班学生的平均年龄，筛选出平均年龄最大的前三个

mysql> select classid,avg(age) from students group by classid order by avg(age) desc limit 3;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       5 |  46.0000 |
|       2 |  36.0000 |
+---------+----------+
3 rows in set (0.00 sec)

4）筛选出平均年龄大于22的班级

mysql> select classid,avg(age) from students group by classid having avg(age)>22;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|       2 |  36.0000 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|    NULL |  63.5000 |
+---------+----------+
4 rows in set (0.00 sec)

5）按姓名排序，选出前三名学生

mysql> select * from students order by name limit 3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|    20 | Diao Chan |  19 | F      |       7 |      NULL |
|     4 | Ding Dian |  32 | M      |       4 |         4 |
|    15 | Duan Yu   |  19 | M      |       4 |      NULL |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

6）按照年龄从大到小排序，筛选出前五名学生

mysql> select * from students order by age desc limit 5 ;
+-------+--------------+-----+--------+---------+-----------+
| 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 |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)

7）筛选出成绩前五名学生

mysql> select s.name,sc.score from students as s left outer join scores as sc on s.stuid=sc.stuid order by score desc limit 5;
+-------------+-------+
| name        | score |
+-------------+-------+
| Shi Potian  |    97 |
| Shi Qing    |    96 |
| Shi Zhongyu |    93 |
| Lin Daiyu   |    93 |
| Ding Dian   |    89 |
+-------------+-------+
5 rows in set (0.00 sec)

8）筛选出平均分前三名的班级

mysql> select s.classid,avg(sc.score) from students as s left outer join scores as sc on s.stuid=sc.stuid group by s.classid order by avg(sc.score) desc limit 3;
+---------+---------------+
| classid | avg(sc.score) |
+---------+---------------+
|       5 |       96.0000 |
|       2 |       83.2500 |
|       4 |       80.0000 |
+---------+---------------+
3 rows in set (0.00 sec)

9）筛选出低于平均分的学生

mysql> select s.name,sc.score from students as s left outer join scores as sc on s.stuid=sc.stuid having score < (select avg(score) from scores) order by score;
+------------+-------+
| name       | score |
+------------+-------+
| Yu Yutong  |    39 |
| Shi Potian |    47 |
| Lin Daiyu  |    57 |
| Yu Yutong  |    63 |
| Ding Dian  |    71 |
| Xie Yanke  |    75 |
+------------+-------+
6 rows in set (0.00 sec)

10）筛选出男性的平均年龄

mysql> select gender,avg(age) from students group by gender having gender='M';
+--------+----------+
| gender | avg(age) |
+--------+----------+
| M      |  33.0000 |
+--------+----------+
1 row in set (0.00 sec)

11）选出每班年龄大于平均年龄的人数

mysql> select classid,count(*) from students where age > (select avg(age) from students) group by classid order by count(*);
+---------+----------+
| classid | count(*) |
+---------+----------+
|       4 |        1 |
|       5 |        1 |
|    NULL |        1 |
|       2 |        2 |
+---------+----------+
4 rows in set (0.01 sec)

3. xtrabackup备份和还原数据库练习

1 备份过程

1）完全备份：
[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@centos8 ~]#mkdir /backup/
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base
2）第一次修改数据

3）第一次增量备份
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc1 --
incremental-basedir=/backup/base
4）第二次修改数据

5）第二次增量
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc2 --
incremental-basedir=/backup/inc1
6）[root@centos8 ~]#scp -r /backup/* 目标主机:/backup/
#备份过程生成三个备份目录
/backup/{base，inc1，inc2}

2还原过程

1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base

2）合并第1次增量备份到完全备份
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base

5）还原属性：
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql

6）启动服务：
[root@centos8 ~]#service mysqld start

4. 实现mysql主从复制，主主复制和半同步复制

见前面文章

juejin.cn/post/726301…

5. 用mycat实现mysql的读写分离

见前面文章

juejin.cn/post/726423…

6. 实现openvpn部署，并且测试通过，输出博客或者自己的文档存档。

见前面文章

juejin.cn/spost/72643…

7. mysql如何实现崩溃后恢复？

MySQL数据库崩溃可能是由于以下原因所导致的：

1.硬件故障：如电源故障、硬盘坏道等。

2.操作系统故障：如操作系统崩溃、系统资源不足等。

3.网络故障：如网络中断、网络延迟等。

4.软件故障：如MySQL服务崩溃、MySQL配置错误等。

MySQL数据库恢复的方法

当MySQL数据库发生崩溃时，我们需要采取一些措施来恢复数据。下面是一些常见的MySQL数据库恢复方法：

1.备份恢复：如果你有备份，可以使用备份文件来恢复数据。在MySQL崩溃后，你可以使用备份文件来恢复数据库。

2.log日志文件来恢复数据。

3.noDBnoDBnoDB引擎，那么可以使用MySQL的崩溃恢复机制来恢复数据。

4.使用MyISAM引擎：MyISAM是MySQL的另一种存储引擎，它不支持事务和崩溃恢复。如果你使用的是MyISAM引擎，那么可以使用MySQL的修复命令来恢复数据。

5.Toolkit可以用来恢复MySQL数据库。

如何预防MySQL数据库崩溃

当然，最好的方法是预防MySQL数据库崩溃。以下是一些预防措施：

1.定期备份数据：定期备份数据是非常重要的，可以确保你有备份文件可用于恢复数据。

2.优化MySQL配置：优化MySQL配置可以提高MySQL的性能和稳定性，从而减少MySQL崩溃的可能性。

3.使用监控工具：使用监控工具可以实时监测MySQL的状态，及时发现问题并采取措施。

4.使用合适的存储引擎：选择合适的存储引擎可以提高MySQL的性能和稳定性，从而减少MySQL崩溃的可能性。

8. myisam和innodb各自在什么场景使用？

MyISAM和InnoDB两者的应用场景：

1. MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
2. InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

但是实际场景中，针对具体问题需要具体分析，一般而言可以遵循以下几个问题：

• 数据库是否有外键？
• 是否需要事务支持？
• 是否需要全文索引？
• 数据库经常使用什么样的查询模式？在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。
• 数据库的数据有多大？ 大尺寸倾向于innodb，因为事务日志，故障恢复。