mysql多表查询

多表查询、数据备份修复与多实例部署

一： 多表查询

mysql> desc dept;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> desc user_name;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(10) | YES  |     | NULL    |                |
| gender    | char(1)     | YES  |     | NULL    |                |
| salary    | double      | YES  |     | NULL    |                |
| jion_date | date        | YES  |     | NULL    |                |
| dept_id   | int(11)     | YES  | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

二： 将两张表中插入数据

mysql> select * from dept;
+----+----------+
| id | name     |
+----+----------+
|  1 | yanfa    |
|  2 | shichang |
|  3 | caiwu    |
|  4 | xiaoshou |
+----+----------+
4 rows in set (0.00 sec)



mysql> select * from user_name;
+----+------------+--------+--------+------------+---------+
| id | name       | gender | salary | jion_date  | dept_id |
+----+------------+--------+--------+------------+---------+
|  1 | zhangsan   | m      |   6000 | 1999-02-15 |       1 |
|  2 | lisi       | f      |   5000 | 1997-05-26 |       2 |
|  3 | lisi       | m      |   7000 | 1998-05-12 |       2 |
|  4 | zhaofei    | m      |   6000 | 1999-01-15 |       3 |
|  5 | wangwu     | f      |   4000 | 1996-03-15 |       4 |
|  6 | wangchuang | m      |   5000 | 2002-01-09 |    NULL |
|  7 | zhouke     | f      |   5500 | 2002-06-09 |       3 |
+----+------------+--------+--------+------------+---------+
7 rows in set (0.01 sec)

三： 多表联合查询

1：交叉连接

最简单的交叉连接就是将两张表或多张表的内容进行相乘后输出，所以输出的数据及其庞大且不合理的数据命令为：

select * from <表1> cross join <表2> cross join .....
或者写成
select  * from <表1>，<表2>，.....

在查询之后加上where条件判断就可以取出想要的数据了：

查询表中字段相等的内容：

mysql> select * from dept as b,user_name as a where a.dept_id = b.id;+----+----------+----+----------+--------+--------+------------+---------+
| id | name     | id | name     | gender | salary | jion_date  | dept_id |
+----+----------+----+----------+--------+--------+------------+---------+
|  1 | yanfa    |  1 | zhangsan | m      |   6000 | 1999-02-15 |       1 |
|  2 | shichang |  2 | lisi     | f      |   5000 | 1997-05-26 |       2 |
|  2 | shichang |  3 | lisi     | m      |   7000 | 1998-05-12 |       2 |
|  3 | caiwu    |  4 | zhaofei  | m      |   6000 | 1999-01-15 |       3 |
|  3 | caiwu    |  7 | zhouke   | f      |   5500 | 2002-06-09 |       3 |
|  4 | xiaoshou |  5 | wangwu   | f      |   4000 | 1996-03-15 |       4 |
+----+----------+----+----------+--------+--------+------------+---------+
6 rows in set (0.00 sec)

2：内连接

命令格式： 、

select  （对应表中的字段名） from  <表1> inner join <表2> on  （判断条件）；

查看表中的人分别在那个部门：

mysql> select a.name,b.name from dept as a inner join user_name as b on
a.id =b.dept_id;
+----------+----------+
| name     | name     |
+----------+----------+
| yanfa    | zhangsan |
| shichang | lisi     |
| shichang | lisi     |
| caiwu    | zhaofei  |
| caiwu    | zhouke   |
| xiaoshou | wangwu   |
+----------+----------+
6 rows in set (0.00 sec)

3： 外连接

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

外连接可以分为左外连接和右外连接2种，下面根据实例分别介绍左外连接和右外连接。

（1）： 左连接

左外连接又称左连接，语法格式：

select  <需要查询的字段名>  from  <表1>  left outer join <表2> on <判断条件>；

查看表中的人分别在那个部门

mysql> select a.name,b.name from dept as a left outer join user_name as
b on a.id =b.dept_id;
+----------+----------+
| name     | name     |
+----------+----------+
| yanfa    | zhangsan |
| shichang | lisi     |
| shichang | lisi     |
| caiwu    | zhaofei  |
| caiwu    | zhouke   |
| xiaoshou | wangwu   |
+----------+----------+
6 rows in set (0.00 sec)

（2）： 右连接

右外连接又称右连接，是左连接的反向连接，语法如下：

select  <查询的字段>  from <表1> right  outer join <表2>  on <判断条件> 

查看表中的人分别在那个部门

mysql> select a.name,b.name from dept as a right outer join user_name as
 b on a.id =b.dept_id;
+----------+------------+
| name     | name       |
+----------+------------+
| yanfa    | zhangsan   |
| shichang | lisi       |
| shichang | lisi       |
| caiwu    | zhaofei    |
| caiwu    | zhouke     |
| xiaoshou | wangwu     |
| NULL     | wangchuang |
+----------+------------+
7 rows in set (0.00 sec)

4： 分组查询

group by 子句可以根据一个或多个字段对查询结果进行分组 ， 格式：

 group  by  <进行分组的字段名> ，<分组字段2>,...

mysql> select name, `gender` from user_name group by gender,name;
+------------+--------+
| name       | gender |
+------------+--------+
| lisi       | f      |
| wangwu     | f      |
| zhouke     | f      |
| lisi       | m      |
| wangchuang | m      |
| zhangsan   | m      |
| zhaofei    | m      |
+------------+--------+
7 rows in set (0.00 sec)

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来

mysql> select  `gender`,group_concat(name) from user_name group by gende
r;
+--------+----------------------------------+
| gender | group_concat(name)               |
+--------+----------------------------------+
| f      | lisi,wangwu,zhouke               |
| m      | zhangsan,lisi,zhaofei,wangchuang |
+--------+----------------------------------+
2 rows in set (0.00 sec)

5： group 聚合函数

聚合函数包括COUNT()【统计记录的条数】，SUM()【计算字段值的总和】，AVG()【计算字段值的平均值】，MAX()【计算字段值的最大值】 和 MIN()【计算字段值的最小值】。

统计字段值的总和：
select  <查询的字段>,<count（查询的字段或其他字符）>  from <查询的表>  group by <查询的字段>；

查询表中有几个人同名

mysql> select name,count(1) from user_name group by name;
+------------+----------+
| name       | count(1) |
+------------+----------+
| lisi       |        2 |
| wangchuang |        1 |
| wangwu     |        1 |
| zhangsan   |        1 |
| zhaofei    |        1 |
| zhouke     |        1 |
+------------+----------+
6 rows in set (0.00 sec)

查询表中工资最高为多少

mysql> select max(salary) from user_name ;
+-------------+
| max(salary) |
+-------------+
|        7000 |
+-------------+
1 row in set (0.00 sec)

同理，查询最低为多少

mysql> select min(salary) from user_name ;
+-------------+
| min(salary) |
+-------------+
|        4000 |
+-------------+
1 row in set (0.00 sec)

group by 与 with rollup

with pollup 关键字就是在所有查询完的记录之后加上一个值的汇总，统计所有参加统计的数量

mysql> select gender,group_concat(name)  from user_name group by gender with rollup;
+--------+-----------------------------------------------------+
| gender | group_concat(name)                                  |
+--------+-----------------------------------------------------+
| f      | lisi,wangwu,zhouke                                  |
| m      | zhangsan,lisi,zhaofei,wangchuang                    |
| NULL   | lisi,wangwu,zhouke,zhangsan,lisi,zhaofei,wangchuang |
+--------+-----------------------------------------------------+
3 rows in set (0.00 sec)

6：子查询

子查询是指将一个查询语句嵌套到另外一个查询子句中，实现更加精细的查询

查询caiwu部门有哪些人

mysql> select * from user_name where dept_id in (select id from dept where id =3);
+----+---------+--------+--------+------------+---------+
| id | name    | gender | salary | jion_date  | dept_id |
+----+---------+--------+--------+------------+---------+
|  4 | zhaofei | m      |   6000 | 1999-01-15 |       3 |
|  7 | zhouke  | f      |   5500 | 2002-06-09 |       3 |
+----+---------+--------+--------+------------+---------+
2 rows in set (0.00 sec)

四： 数据备份与恢复

1：备份方案

（1）： 全量备份： 全量备份是指将数据完整地、一次性地备份到另一个位置或设备的过程。在全量备份过程中，所有的数据都会被复制并存储，包括文件、数据库、配置等等。相比增量备份只备份更改的部分，全量备份需要更长的时间和更多的存储空间。然而，全量备份可以确保所有数据的完整性和可恢复性，在出现故障时可以快速恢复系统或数据。全量备份通常在定期基础上进行，以确保最新的数据备份   

（2）： 增量备份： 增量备份是指只备份数据发生更改的部分或新增的数据的备份策略。与全量备份不同，增量备份只会备份自上次备份以来发生更改的文件或数据块。这样可以减少备份所需的时间和存储空间。在增量备份中，每一次备份都会记录上次备份后发生的更改，并且新的备份只包含这些更改的内容。当需要恢复数据时，需要依次还原完整的备份链，即将全量备份和相应的增量备份逐个回滚。增量备份可以提供较小的备份窗口和更快的备份速度，但同时也需要花费更多的时间来还原完整的备份，

（3）： 差异备份： 差异备份是在全量备份的基础上，仅备份与上一次全量备份与这次备份之间发生了变化的数据部分。与增量备份不同的是，差异备份不仅仅备份了自上一次备份以来发生的更改部分，还包括了新增的数据。这意味着每次进行差异备份时，都会备份自上一次全量备份（或上一次差异备份）以来的所有数据变化。与全量备份相比，差异备份可以减少备份时间和存储空间的使用，但在还原数据时会需要依赖上一次全量备份和当前的差异备份。差异备份可以提供更快速的备份和还原性能，并且比增量备份的还原更快速。

2： mysql备份工具mysqldump

注： 以下全文中主机信息全部省略没有写是因为在家目录有一个存放用户信息的

.my.cnf的隐藏文件，在使用时会自动查找用户信息，避免信息泄露。

#： 语法格式  
mysqldump  [主机信息] --all-databases  >  <存放位置>  ： 全量备份数据库到指定位置

mysqldump  [主机信息]  <数据库名（如有多个用空格隔开）[数据库名  表名]>   >    <存放位置>  ： 备份指定数据库（指定数据库中指定数据表）到指定位置

mysqldump  [主机信息]  databases <数据库1>

[主机信息]： -u:  账户，-p: 密码，-h:  服务主机， -P: 监听端口

例：

#： 全量备份数据库到当前目录下指定为all-0122.sql
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lhs                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
[root@localhost ~]# ls
[root@localhost ~]# mysqldump --all-databases > all-0122.sql
[root@localhost ~]# ls
all-0122.sql  

#： 备份test库
[root@localhost ~]# mysqldump test > /opt/test/table-0313.sql
[root@localhost ~]# ls /opt/test
table-0313.sql

数据恢复

#:语法格式： 
mysql [主机信息] <  <备份文件>： 恢复指定的备份

例：

# 删除数据库中的数据表模拟误删
mysql> drop table user_name;
Query OK, 0 rows affected (0.01 sec)

mysql> drop tables dept;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
#： 启用备份恢复数据
[root@localhost ~]# mysql < /opt/test/table-0313.sql 
#： 查看数据库中数据是否恢复
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept           |
| user_name      |
+----------------+
2 rows in set (0.00 sec)

#： 模拟误删数据库
mysql> drop database test;
Query OK, 2 rows affected (0.00 sec)

mysql> drop database lhs;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
#： 使用全量备份进行恢复
[root@localhost ~]# mysql < all-0122.sql 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lhs                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

3： 差异备份

#： 差异备份需要开启二进制日志功能
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=1  #：设置服务器标识符（集群服务时用于标识独立的主机）
log-bin=mysql_bin  #：开启二进制日志功能
#：修改了数据库配置文件需要重启数据库
[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 

对数据库进行完全备份

#： 命令格式
mysqldump  [主机信息] --flush-logs --all-databases  --delete-master-logs（删除其他的备份日志） >  <备份存放位置>

#：执行完成之后，mysql存放数据的目录下会多出一个mysql_bin.00000*的日志文件

使数据库中的数据发生更改

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lhs                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql>use test
mysql> update dept set name ='canku' where id =4;
Query OK, 1 row affected (0.00 sec)

# 模拟误删数据库
mysql> drop database lhs； 
Query OK, 1 row affected (0.01 sec)
mysql> drop database test;
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

刷新二进制日志

[root@localhost ~]# mysqladmin flush-logs
[root@localhost ~]# ls /opt/data/ |grep mysql_bin
mysql_bin.000007
mysql_bin.000008
mysql_bin.index
#： 刷新完之后会多出一个mysql_bin.00000*+1 的文件

恢复完全备份：

[root@localhost ~]# mysql < /opt/test/all-0410.sql
#数据库中的表也恢复了 但是更改的数据不存在

恢复差异备份：

mysql> show binlog events in 'mysql_bin.000007';
-----省略-----
| mysql_bin.000007 | 810 | Update_rows    |         1 |         871 | table_id: 221 flags: STMT_END_F       |
----省略--------

[root@localhost ~]# mysqlbinlog --stop-position=871 /opt/data/mysql_bin.000007 |mysql

五： mysql多实例部署

#： 安装依赖
[root@localhost local]#yum -y install perl  ncurses-compat-libs


#： 将mysql源码包上传至服务器中进行解压
[root@localhost ~]# tar -xf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz 
#： 将解压后的文件移动到/usr/local下，并改名为mysql
[root@localhost ~]# mv mysql-5.7.39-linux-glibc2.12-x86_64 /usr/local/mysql
[root@localhost ~]# ls /usr/local/
apache  apr-util  etc    include  lib64    mysql  share
apr     bin       games  lib      libexec  sbin   src
#： 创建mysql系统用户和mysql系统组
 [root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -r -s /sbin/nologin -M -g mysql mysql
[root@localhost ~]# id mysql
uid=993(mysql) gid=990(mysql) groups=990(mysql)
#：递归修改mysql文件夹的属主属组
[root@localhost ~]# cd /usr/local/
[root@localhost local]# chown -R mysql:mysql mysql
[root@localhost local]# ll -d mysql/
drwxr-xr-x. 9 mysql mysql 129 Aug 29 14:04 mysql/
[root@localhost local]# ll  mysql/
total 268
-rw-r--r--.  1 mysql mysql 255074 Jun  8  2022 LICENSE
-rw-r--r--.  1 mysql mysql    566 Jun  8  2022 README
drwxr-xr-x.  2 mysql mysql   4096 Aug 29 14:04 bin
drwxr-xr-x.  2 mysql mysql     55 Aug 29 14:04 docs
-----省略-----
#： 添加mysql的环境变量  
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# . /etc/profile.d/mysql.sh 
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/apache/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

#： 建立mysql各个实例数据存放目录
[root@localhost local]# mkdir /opt/data/{3306,3307,3308}
[root@localhost local]# chown -R mysql:mysql /opt/data/
[root@localhost local]# ll -d /opt/data
drwxr-xr-x. 2 mysql mysql 6 Sep  4 16:02 3306
drwxr-xr-x. 2 mysql mysql 6 Sep  4 16:02 3307
drwxr-xr-x. 2 mysql mysql 6 Sep  4 16:02 3308

#： 初始化3306实例
[root@localhost local]# ./mysql/bin/mysqld --initialize-insecure --datadir=/opt/data/3306 --user=mysql
[root@localhost local]# ./mysql/bin/mysqld --initialize-insecure --datadir=/opt/data/3307 --user=mysql
[root@localhost local]# ./mysql/bin/mysqld --initialize-insecure --datadir=/opt/data/3308 --user=mysql
 
 

#： 生成配置文件/etc/my.cnf
[root@localhost local]# vim /etc/my.cnf 
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log
character_set_server=utf8

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log
character_set_server=utf8

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log
character_set_server=utf8


#： 配置启动脚本
[root@localhost local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost local]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

#: 启动mysql实例
[root@localhost local]#  mysqld_multi start 3306
[root@localhost local]#  mysqld_multi start 3307
[root@localhost local]#  mysqld_multi start 3308
    
#： 修改mysql数据库的密码
[root@localhost lib]#  mysql -uroot -S /tmp/mysql3306.sock -e 'set password = password("123456");' --connect-expired-password


[root@localhost lib]#  mysql -uroot -S /tmp/mysql3307.sock -e 'set password = password("123456");' --connect-expired-password

[root@localhost lib]#  mysql -uroot -S /tmp/mysql3308.sock -e 'set password = password("123456");' --connect-expired-password

#： 登录mysql
[root@localhost lib]# mysql -uroot -p - S /tmp/mysql330(6,7,8,).sock