Linux，第六篇
Linux,第六篇
简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序
- DDL: Data Defination Language 数据定义语言
- CREATE，DROP，ALTER
- DML: Data Manipulation Language 数据操纵语言
- INSERT，DELETE，UPDATE
- 软件开发：CRUD
- DQL：Data Query Language 数据查询语言
- SELECT
- DCL：Data Control Language 数据控制语言
- GRANT，REVOKE
查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎
SELECT语句的执行流程：
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY --> LIMIT
自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。
sql语句应用场景为班级学生教师查询类，电子商城筛选价格牌子类，由于道理互通，导入hellodb.sql生成数据库作为例子
-
在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
MariaDB [hellodb]> select age,name from students where age >25; +-----+--------------+ | age | name | +-----+--------------+ | 53 | Xie Yanke | | 32 | Ding Dian | | 26 | Yu Yutong | | 46 | Shi Qing | | 33 | Tian Boguang | | 27 | Xu Xian | | 100 | Sun Dasheng | +-----+--------------+ 7 rows in set (0.000 sec) -
以ClassID为分组依据，显示每组的平均年龄
MariaDB [hellodb]> select avg(age),classid from students group by classid; +----------+---------+ | avg(age) | classid | +----------+---------+ | 63.5000 | NULL | | 20.5000 | 1 | | 36.0000 | 2 | | 20.2500 | 3 | | 24.7500 | 4 | | 46.0000 | 5 | | 20.7500 | 6 | | 19.6667 | 7 | +----------+---------+ 8 rows in set (0.000 sec) -
显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select avg(age),classid from students group by classid having avg(age)>30; +----------+---------+ | avg(age) | classid | +----------+---------+ | 63.5000 | NULL | | 36.0000 | 2 | | 46.0000 | 5 | +----------+---------+ 3 rows in set (0.000 sec) -
显示以L开头的名字的同学的信息
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 | +-------+-------------+-----+--------+---------+-----------+ -
显示TeacherID非空的同学的相关信息
MariaDB [hellodb]> select * from students where teacherid 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 | +-------+-------------+-----+--------+---------+-----------+ 5 rows in set (0.000 sec) -
以年龄排序后，显示年龄最大的前10位同学的信息
MariaDB [hellodb]> select * from students order by age desc limit 10; +-------+--------------+-----+--------+---------+-----------+ | 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 | | 24 | Xu Xian | 27 | M | NULL | NULL | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 17 | Lin Chong | 25 | M | 4 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | +-------+--------------+-----+--------+---------+-----------+ 10 rows in set (0.000 sec) -
查询年龄大于等于20岁，小于等于25岁的同学的信息
MariaDB [hellodb]> select * from students where age between 20 and 25; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | +-------+---------------+-----+--------+---------+-----------+ 10 rows in set (0.000 sec) -
以ClassID分组，显示每班的同学的人数
MariaDB [hellodb]> select count(*),classid from students group by classid; +----------+---------+ | count(*) | classid | +----------+---------+ | 2 | NULL | | 4 | 1 | | 3 | 2 | | 4 | 3 | | 4 | 4 | | 1 | 5 | | 4 | 6 | | 3 | 7 | +----------+---------+ 8 rows in set (0.000 sec) -
以Gender分组，显示其年龄之和
MariaDB [hellodb]> select sum(age),gender from students group by gender ; +----------+--------+ | sum(age) | gender | +----------+--------+ | 190 | F | | 495 | M | +----------+--------+ 2 rows in set (0.000 sec) -
以ClassID分组，显示其平均年龄大于25的班级
MariaDB [hellodb]> select c.class,c.classid from classes c left outer join students s on c.classid=s.classid group by s.classid having avg(s.age)>25; +----------------+---------+ | class | classid | +----------------+---------+ | Emei Pai | 2 | | Riyue Shenjiao | 5 | +----------------+---------+ 2 rows in set (0.000 sec) -
以Gender分组，显示各组中年龄大于25的学员的年龄之和
MariaDB [hellodb]> select sum(age),gender from students where age >25 group by gender ; +----------+--------+ | sum(age) | gender | +----------+--------+ | 317 | M | +----------+--------+ 1 row in set (0.000 sec) -
显示前5位同学的姓名、课程及成绩
MariaDB [hellodb]> select st.stuid,st.name,st.age,soc.courseid,soc.score,co.course from students st left outer join scores soc on st.stuid=soc.stuid left outer join courses co on soc.courseid=co.courseid where st.stuid<=5; +-------+-------------+-----+----------+-------+----------------+ | stuid | name | age | courseid | score | course | +-------+-------------+-----+----------+-------+----------------+ | 1 | Shi Zhongyu | 22 | 2 | 77 | Kuihua Baodian | | 1 | Shi Zhongyu | 22 | 6 | 93 | Weituo Zhang | | 2 | Shi Potian | 22 | 2 | 47 | Kuihua Baodian | | 2 | Shi Potian | 22 | 5 | 97 | Daiyu Zanghua | | 3 | Xie Yanke | 53 | 2 | 88 | Kuihua Baodian | | 3 | Xie Yanke | 53 | 6 | 75 | Weituo Zhang | | 4 | Ding Dian | 32 | 5 | 71 | Daiyu Zanghua | | 4 | Ding Dian | 32 | 2 | 89 | Kuihua Baodian | | 5 | Yu Yutong | 26 | 1 | 39 | Hamo Gong | | 5 | Yu Yutong | 26 | 7 | 63 | Dagou Bangfa | +-------+-------------+-----+----------+-------+----------------+ 10 rows in set (0.001 sec) -
显示其成绩高于80的同学的名称及课程
MariaDB [hellodb]> select st.stuid,st.name,st.age,soc.courseid,soc.score,co.course from students st left outer join scores soc on st.stuid=soc.stuid left outer join courses co on soc.courseid=co.courseid where soc.score>80; +-------+-------------+-----+----------+-------+----------------+ | stuid | name | age | courseid | score | course | +-------+-------------+-----+----------+-------+----------------+ | 1 | Shi Zhongyu | 22 | 6 | 93 | Weituo Zhang | | 2 | Shi Potian | 22 | 5 | 97 | Daiyu Zanghua | | 3 | Xie Yanke | 53 | 2 | 88 | Kuihua Baodian | | 4 | Ding Dian | 32 | 2 | 89 | Kuihua Baodian | | 6 | Shi Qing | 46 | 1 | 96 | Hamo Gong | | 7 | Xi Ren | 19 | 1 | 86 | Hamo Gong | | 7 | Xi Ren | 19 | 7 | 83 | Dagou Bangfa | | 8 | Lin Daiyu | 17 | 3 | 93 | Jinshe Jianfa | +-------+-------------+-----+----------+-------+----------------+ 8 rows in set (0.000 sec) -
取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
MariaDB [hellodb]> select st.stuid,st.name,st.age,soc.courseid,co.course,avg(soc.score) from students st left outer join scores soc on st.stuid=soc.stuid left outer join courses co on soc.courseid=co.courseid group by soc.stuid having avg(soc.score) order by avg(soc.score) desc limit 3; +-------+-------------+-----+----------+----------------+----------------+ | stuid | name | age | courseid | course | avg(soc.score) | +-------+-------------+-----+----------+----------------+----------------+ | 6 | Shi Qing | 46 | 1 | Hamo Gong | 96.0000 | | 1 | Shi Zhongyu | 22 | 2 | Kuihua Baodian | 85.0000 | | 7 | Xi Ren | 19 | 1 | Hamo Gong | 84.5000 | +-------+-------------+-----+----------+----------------+----------------+ 3 rows in set (0.000 sec) -
显示每门课程课程名称及学习了这门课的同学的个数
MariaDB [hellodb]> select count(st.stuid) 课程学习人数,soc.courseid,co.course 课程名称 from students st left outer join scores soc on st.stuid=soc.stuid left outer join courses co on soc.courseid=co.courseid where co.course is not null group by course; +--------------------+----------+----------------+ | 课程学习人数 | courseid | 课程名称 | +--------------------+----------+----------------+ | 2 | 7 | Dagou Bangfa | | 2 | 5 | Daiyu Zanghua | | 3 | 1 | Hamo Gong | | 1 | 3 | Jinshe Jianfa | | 4 | 2 | Kuihua Baodian | | 1 | 4 | Taiji Quan | | 2 | 6 | Weituo Zhang | +--------------------+----------+----------------+ 7 rows in set (0.000 sec) -
显示其年龄大于平均年龄的同学的名字
MariaDB [hellodb]> select name,age from students where age > (select avg(age) from students); +--------------+-----+ | name | age | +--------------+-----+ | Xie Yanke | 53 | | Ding Dian | 32 | | Shi Qing | 46 | | Tian Boguang | 33 | | Sun Dasheng | 100 | +--------------+-----+ 5 rows in set (0.000 sec) -
显示其学习的课程为第1、2，4或第7门课的同学的名字
MariaDB [hellodb]> select st.stuid,st.name,st.age,soc.courseid,co.course from students st left outer join scores soc on st.stuid=soc.stuid left outer join courses co on soc.courseid=co.courseid group by soc.courseid having soc.courseid in (1,2,4,7); +-------+-------------+-----+----------+----------------+ | stuid | name | age | courseid | course | +-------+-------------+-----+----------+----------------+ | 5 | Yu Yutong | 26 | 1 | Hamo Gong | | 1 | Shi Zhongyu | 22 | 2 | Kuihua Baodian | | 8 | Lin Daiyu | 17 | 4 | Taiji Quan | | 5 | Yu Yutong | 26 | 7 | Dagou Bangfa | +-------+-------------+-----+----------+----------------+ 4 rows in set (0.000 sec) -
显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
MariaDB [hellodb]> select * from students s left outer join (select classid,count(*) num,avg(age) avgage from students group by classid having num >=3 and classid is not null) as nums on s.classid=nums.classid group by s.stuid having age > avgage ; +-------+---------------+-----+--------+---------+-----------+---------+------+---------+ | StuID | Name | Age | Gender | ClassID | TeacherID | classid | num | avgage | +-------+---------------+-----+--------+---------+-----------+---------+------+---------+ | 2 | Shi Potian | 22 | M | 1 | 7 | 1 | 4 | 20.5000 | | 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | 3 | 36.0000 | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | 4 | 24.7500 | | 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | 4 | 20.2500 | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 6 | 4 | 20.7500 | | 16 | Xu Zhu | 21 | M | 1 | NULL | 1 | 4 | 20.5000 | | 17 | Lin Chong | 25 | M | 4 | NULL | 4 | 4 | 24.7500 | | 18 | Hua Rong | 23 | M | 7 | NULL | 7 | 3 | 19.6667 | | 21 | Huang Yueying | 22 | F | 6 | NULL | 6 | 4 | 20.7500 | +-------+---------------+-----+--------+---------+-----------+---------+------+---------+ 9 rows in set (0.000 sec) -
统计各班级中年龄大于全校同学平均年龄的同学
MariaDB [hellodb]> select * from students where age > (select avg(age) from students);
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.000 sec)
xtrabackup备份和还原数据库练习
1 安装xtrabackup包
[root@centos8 ~]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
2 在原主机做完全备份到/backup
[root@centos8 ~]#mkdir /backup
[root@centos8 ~]#xtrabackup -uroot --backup --target-dir=/backup/base
#目标主机无需创建/backup目录,直接复制目录本身
[root@centos8 ~]#scp -r /backup/ 目标主机:/
3 在目标主机上还原
注意：恢复主机MySQL服务停止，并且数据目录为空
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@centos8 ~]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base
2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
3）还原属性
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
4）启动服务
[root@centos8 ~]#service mysqld start
实现mysql主从复制，主主复制和半同步复制
主从复制
#主节点，10.0.0.134
dnf -y install mysqld-server;
vim /etc/my.cnf
[mysqld]
server-id=134
log-bin
systemctl restart mysqld
mysql
:43: (root@localhost) [(none)]> show master logs;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| localhost-bin.000001 | 157 | No |
+----------------------+-----------+-----------+
#创建复制用户
mysql>create user 'repluser'@'10.0.0.%';
mysql>grant replication slave on *.* to 'repluser'@'10.0.0.%';
#从节点，10.0.0.136
vim /etc/my.cnf
[mysqld]
server-id=136
log-bin
systemctl restart mysqld
mysql
:52: (root@localhost) [(none)]> change master to master_host='10.0.0.134',
-> master_user='repluser',master_password='',
-> master_port=3306,master_log_file='localhost-bin.000001',
-> master_log_pos=157;
0: (root@localhost) [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.134
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: localhost-bin.000001
Read_Master_Log_Pos: 621
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 794
Relay_Master_Log_File: localhost-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主主复制仅作特点描述
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
主主复制的配置步骤：
- 各节点使用一个惟一server_id
- 都启动binary log和relay log
- 创建拥有复制权限的用户账号
- 定义自动增长id字段的数值范围各为奇偶
- 均把对方指定为主节点，并启动复制线程
范例：实现两个节点的主主复制模型
半同步复制
#主节点，10.0.0.134
[root@localhost ~]# rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_replica.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/semisync_source.so
mysql
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插件
mysql>SHOW PLUGINS; #查看插件
quit
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=134
log-bin
rpl_semi_sync_master_enabled=ON
[root@localhost ~]# systemctl restart mysqld
mysql
mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql> SHOW GLOBAL STATUS LIKE '%semi%';
#从节点，10.0.0.136
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=136
log-bin
rpl_semi_sync_slave_enabled=ON
[root@localhost ~]# systemctl restart mysqld
#注意:如果已经实现主从复制,需要stop slave;start slave;
mysql> stop slave;
mysql> start slave;
mysql> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
#验证方法
#从节点10.0.0.136
mysql
stop slave;
#主节点10.0.0.134
Database changed
:07: (root@localhost) [hellodb]> insert teachers values(7,'T',19,'F');
Query OK, 1 row affected (10.00 sec)
#从节点10.0.0.136
start slave
#主节点10.0.0.134
:07: (root@localhost) [hellodb]> insert teachers values(8,'U',20,'F');
Query OK, 1 row affected (0.00 sec)
#从节点10.0.0.136
:08: (root@localhost) [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
:08: (root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | X | 18 | M |
| 6 | Z | 19 | F |
| 7 | T | 19 | F |
| 8 | U | 20 | F |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)
用mycat实现mysql的读写分离
时间不够，而且目前mycat在中型企业往上已经很少用了，先不做
实现openvpn部署，并且测试通过，输出博客或者自己的文档存档。
暂时无时间实现，日后看重要程度和时间选择补回还是跳过
mysql如何实现崩溃后恢复？
通过mysqldump，xtrabackup等备份工具进行恢复，先在异机尝试恢复数据库，恢复成功后再替代原有数据库
防止手段，使用Galera部署mysql集群
myisam和innodb各自在什么场景使用？"
MyISAM 存储引擎适用场景
- 只读（或者写较少）
- 表较小（可以接受长时间进行修复操作）
MyISAM 引擎特点
- 不支持事务
- 表级锁定
- 读写相互阻塞，写入不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5 前默认的数据库引擎
InnoDB引擎适用场景
与myisam相反，目前已经是mysql主流存储引擎
InnoDB引擎特点
- 行级锁
- 支持事务，适合处理大量短期事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
浙公网安备 33010602011771号