内链接查询与备份
内连接
查询来自tb_student_info表的所有字段 （下文以s表表示）
查询来自tb_course表的所有字段 （下文以c表表示）
mysql> select * from tb_students_info;
+----+-------+-----+-----+--------+------------+
| id | name | age | sex | height | course_idd |
+----+-------+-----+-----+--------+------------+
| 1 | Dany | 25 | 1 | 160 | 1 |
| 2 | Green | 25 | 1 | 158 | 2 |
| 3 | Henry | 23 | 2 | 185 | 1 |
| 4 | Jane | 22 | 1 | 162 | 3 |
| 5 | Jim | 24 | 2 | 175 | 2 |
| 6 | John | 24 | 2 | 172 | 4 |
| 7 | Lily | 22 | 1 | 165 | 4 |
| 8 | Susan | 23 | 1 | 170 | 5 |
| 9 | Tomas | 22 | 2 | 178 | 5 |
| 10 | Tom | 23 | 2 | 165 | 5 |
+----+-------+-----+-----+--------+------------+
10 rows in set (0.00 sec)
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
+----+-------------+
5 rows in set (0.00 sec)
语法 select [字段] from table inner join table on 接判断语句
查询s表下的name字段和c表下的course_name字段 来自c和s两个表的内连接数据 判断s表下的course_idd与c表下的id字段一直的显示
mysql> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_idd = c.id;
+-------+-------------+
| name | course_name |
+-------+-------------+
| Dany | Java |
| Green | MySQL |
| Henry | Java |
| Jane | Python |
| Jim | MySQL |
| John | Go |
| Lily | Go |
| Susan | C++ |
| Tomas | C++ |
| Tom | C++ |
+-------+-------------+
10 rows in set (0.00 sec)
查询s表下的name字段和和c表下的course_name字段 从 s的course_idd字段等于5并且c的course_name字段等于c++
mysql> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_idd = 5 and c.course_name = 'C++' ;
+-------+-------------+
| name | course_name |
+-------+-------------+
| Susan | C++ |
| Tomas | C++ |
| Tom | C++ |
+-------+-------------+
3 rows in set (0.00 sec)
mysql> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.height < 172 and s.sex =2 and c.course_name = 'C++' ;
+------+-------------+
| name | course_name |
+------+-------------+
| Tom | C++ |
+------+-------------+
1 row in set (0.00 sec)
左链接
mysql> select s.name,c.course_name from tb_students_info s left join tb_course c on s.course_idd = c.id;
+--------+-------------+
| name | course_name |
+--------+-------------+
| Dany | Java |
| Henry | Java |
| Green | MySQL |
| Jim | MySQL |
| Jane | Python |
| John | Go |
| Lily | Go |
| Susan | C++ |
| Tomas | C++ |
| Tom | C++ |
| LiMing | NULL |
+--------+-------------+
11 rows in set (0.00 sec)
右链接
mysql> select s.name,c.course_name from tb_students_info s right join tb_course c on s.course_idd = c.id;
+-------+-------------+
| name | course_name |
+-------+-------------+
| Dany | Java |
| Green | MySQL |
| Henry | Java |
| Jane | Python |
| Jim | MySQL |
| John | Go |
| Lily | Go |
| Susan | C++ |
| Tomas | C++ |
| Tom | C++ |
| NULL | HTML |
+-------+-------------+
11 rows in set (0.00 sec)
分组查询
mysql> select name,age from s group by age;
+-------+-----+
| name | age |
+-------+-----+
| Jane | 22 |
| Henry | 23 |
| Jim | 24 |
| Dany | 25 |
+-------+-----+
4 rows in set (0.00 sec)
mysql> select group_concat(name),age from s group by age;
+------------------------+-----+
| group_concat(name) | age |
+------------------------+-----+
| Jane,Lily,Tomas,LiMing | 22 |
| Henry,Susan,Tom | 23 |
| Jim,John | 24 |
| Dany,Green | 25 |
+------------------------+-----+
4 rows in set (0.00 sec)
mysql> select age,sex,group_concat(name) from s group by age;
+-----+-----+------------------------+
| age | sex | group_concat(name) |
+-----+-----+------------------------+
| 22 | 1 | Jane,Lily,Tomas,LiMing |
| 23 | 2 | Henry,Susan,Tom |
| 24 | 2 | Jim,John |
| 25 | 1 | Dany,Green |
+-----+-----+------------------------+
4 rows in set (0.00 sec)
mysql> select age,sex,group_concat(name) from s group by age,sex;
+-----+-----+--------------------+
| age | sex | group_concat(name) |
+-----+-----+--------------------+
| 22 | 1 | Jane,Lily,LiMing |
| 22 | 2 | Tomas |
| 23 | 1 | Susan |
| 23 | 2 | Henry,Tom |
| 24 | 2 | Jim,John |
| 25 | 1 | Dany,Green |
+-----+-----+--------------------+
6 rows in set (0.00 sec)
mysql> select group_concat(name),age,group_concat(sex),count(age) from s groupp by age;
+------------------------+-----+-------------------+------------+
| group_concat(name) | age | group_concat(sex) | count(age) |
+------------------------+-----+-------------------+------------+
| Jane,Lily,Tomas,LiMing | 22 | 1,1,2,1 | 4 |
| Henry,Susan,Tom | 23 | 2,1,2 | 3 |
| Jim,John | 24 | 2,2 | 2 |
| Dany,Green | 25 | 1,1 | 2 |
+------------------------+-----+-------------------+------------+
4 rows in set (0.00 sec)
mysql> select sex,age,group_concat(name) from s group by sex with rollup;
+-----+-----+------------------------------------------------------------+
| sex | age | group_concat(name) |
+-----+-----+------------------------------------------------------------+
| 1 | 25 | Dany,Green,Jane,Lily,Susan,LiMing |
| 2 | 23 | Henry,Jim,John,Tomas,Tom |
| NULL | 23 | Dany,Green,Jane,Lily,Susan,LiMing,Henry,Jim,John,Tomas,Tom |
+-----+-----+------------------------------------------------------------+
3 rows in set (0.00 sec)
子查询
mysql> select name,age from s where s.course_idd in (select id from c where c.id = 5);
+-------+-----+
| name | age |
+-------+-----+
| Susan | 23 |
| Tomas | 22 |
| Tom | 23 |
+-------+-----+
3 rows in set (0.00 sec)
全量备份
全量备份
[root@YL ~]# mysqldump -uroot -p654321 --all-databases > all-$(date '+%Y-%m-%d-%H:%M:%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# ls
all-2022-07-28-20:17:47.sql
备份liuyang库的s表和c表
[root@YL ~]# mysqldump -uroot -p654321 liuyang s c > table-$(date '+%Y-%m-%d-%H:%M:%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# ls
all-2022-07-28-20:17:47.sql
anaconda-ks.cfg
mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
mysql57-community-release-el7-11.noarch.rpm
passwd
table-2022-07-28-20:21:19.sql
备份liuyang库
[root@YL ~]# mysqldump -uroot -p654321 --databases liuyang > liuyang-$(date '+%Y-%m-%d-%H:%M:%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# ls
all-2022-07-28-20:17:47.sql
anaconda-ks.cfg
liuyang-2022-07-28-20:24:07.sql
模拟误删liuyang数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| liuyang |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database liuyang;
Query OK, 2 rows affected (0.00 sec)
mysql> exit
Bye
[root@YL ~]# ls
all-2022-07-28-20:17:47.sql
anaconda-ks.cfg
liuyang-2022-07-28-20:24:07.sql
mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
mysql57-community-release-el7-11.noarch.rpm
passwd
table-2022-07-28-20:21:19.sql
//恢复liuyang数据库
[root@YL ~]# mysql -uroot -p654321 < liuyang-2022-07-28-20\:24\:07.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# mysql -uroot -p654321 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| liuyang |
| mysql |
| performance_schema |
| sys |
+--------------------+
重启mysql
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
server-id=1
log-bin=mysql_bin
[root@YL mysql]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@YL mysql]#
完全备份
[root@YL ~]# mysqldump -uroot -p654321 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202207282045.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# ll
total 661640
-rw-r--r--. 1 root root 877507 Jul 28 20:45 all-202207282045.sql
增加内容
mysql> insert into s values(12,'liu',18,1,'177',3),(13,'yang',19,1,166,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from s;
+----+--------+-----+-----+--------+------------+
| id | name | age | sex | height | course_idd |
+----+--------+-----+-----+--------+------------+
| 1 | Dany | 25 | 1 | 160 | 1 |
| 2 | Green | 25 | 1 | 158 | 2 |
| 3 | Henry | 23 | 2 | 185 | 1 |
| 4 | Jane | 22 | 1 | 162 | 3 |
| 5 | Jim | 24 | 2 | 175 | 2 |
| 6 | John | 24 | 2 | 172 | 4 |
| 7 | Lily | 22 | 1 | 165 | 4 |
| 8 | Susan | 23 | 1 | 170 | 5 |
| 9 | Tomas | 22 | 2 | 178 | 5 |
| 10 | Tom | 23 | 2 | 165 | 5 |
| 11 | LiMing | 22 | 1 | 180 | 6 |
| 12 | liu | 18 | 1 | 177 | 3 |
| 13 | yang | 19 | 1 | 166 | 2 |
+----+--------+-----+-----+--------+------------+
13 rows in set (0.00 sec)
mysql> update s set age = 25 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from s;
+----+--------+-----+-----+--------+------------+
| id | name | age | sex | height | course_idd |
+----+--------+-----+-----+--------+------------+
| 1 | Dany | 25 | 1 | 160 | 1 |
| 2 | Green | 25 | 1 | 158 | 2 |
| 3 | Henry | 23 | 2 | 185 | 1 |
| 4 | Jane | 25 | 1 | 162 | 3 |
| 5 | Jim | 24 | 2 | 175 | 2 |
| 6 | John | 24 | 2 | 172 | 4 |
| 7 | Lily | 22 | 1 | 165 | 4 |
| 8 | Susan | 23 | 1 | 170 | 5 |
| 9 | Tomas | 22 | 2 | 178 | 5 |
| 10 | Tom | 23 | 2 | 165 | 5 |
| 11 | LiMing | 22 | 1 | 180 | 6 |
| 12 | liu | 18 | 1 | 177 | 3 |
| 13 | yang | 19 | 1 | 166 | 2 |
+----+--------+-----+-----+--------+------------+
13 rows in set (0.01 sec)
mysql差异备份恢复
模拟删除数据
[root@YL ~]# mysql -uroot -p654321 -e 'drop database liuyang;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# mysql -uroot -p654321 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
刷新创建新的二进制日志
[root@YL ~]# mysqladmin -uroot -p654321 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# ll /opt/data/
total 122996
-rw-r-----. 1 mysql mysql 56 Jul 27 18:54 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 27 18:54 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 18:54 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 18:54 client-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 27 18:54 client-key.pem
-rw-r-----. 1 mysql mysql 1093 Jul 28 20:39 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 28 20:57 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 28 20:57 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 18:54 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 28 20:53 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jul 28 20:33 mysql
恢复完全备份
[root@YL ~]# mysql -uroot -p654321 < all-202207282045.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# mysql -uroot -p654321 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| liuyang |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@YL ~]# mysql -uroot -p654321 -e 'show tables from liuyang;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| Tables_in_liuyang |
+-------------------+
| c |
| s |
+-------------------+
[root@YL ~]# mysql -uroot -p654321 -e 'select * from liuyang.s;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+-----+-----+--------+------------+
| id | name | age | sex | height | course_idd |
+----+--------+-----+-----+--------+------------+
| 1 | Dany | 25 | 1 | 160 | 1 |
| 2 | Green | 25 | 1 | 158 | 2 |
| 3 | Henry | 23 | 2 | 185 | 1 |
| 4 | Jane | 22 | 1 | 162 | 3 |
| 5 | Jim | 24 | 2 | 175 | 2 |
| 6 | John | 24 | 2 | 172 | 4 |
| 7 | Lily | 22 | 1 | 165 | 4 |
| 8 | Susan | 23 | 1 | 170 | 5 |
| 9 | Tomas | 22 | 2 | 178 | 5 |
| 10 | Tom | 23 | 2 | 165 | 5 |
| 11 | LiMing | 22 | 1 | 180 | 6 |
恢复差异备份
//检查误删除数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 219 | Query | 1 | 294 | BEGIN |
| mysql_bin.000003 | 294 | Table_map | 1 | 350 | table_id: 177 (liuyang.s) |
| mysql_bin.000003 | 350 | Write_rows | 1 | 418 | table_id: 177 flags: STMT_END_F |
| mysql_bin.000003 | 418 | Xid | 1 | 449 | COMMIT /* xid=970 */ |
| mysql_bin.000003 | 449 | Anonymous_Gtid | 1 | 514 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 514 | Query | 1 | 589 | BEGIN |
| mysql_bin.000003 | 589 | Table_map | 1 | 645 | table_id: 177 (liuyang.s) |
| mysql_bin.000003 | 645 | Update_rows | 1 | 715 | table_id: 177 flags: STMT_END_F |
| mysql_bin.000003 | 715 | Xid | 1 | 746 | COMMIT /* xid=972 */ |
| mysql_bin.000003 | 746 | Anonymous_Gtid | 1 | 811 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 811 | Query | 1 | 912 | drop database liuyang |
| mysql_bin.000003 | 912 | Rotate | 1 | 959 | mysql_bin.000004;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql> exit
Bye
[root@YL ~]#
[root@YL ~]# mysqlbinlog --stop-position=794 /opt/data/mysql_bin.000003 | grep -uroot -p654321
grep: invalid option -- 't'
Usage: grep [OPTION]... PATTERN [FILE]...
Try 'grep --help' for more information.
使用mysqlbinlog恢复差异备份
[root@YL ~]# mysqlbinlog --stop-position=794 /opt/data/mysql_bin.000003 | mysql -uroot -p654321
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@YL ~]# mysql -uroot -p654321 -e 'select * from liuyang.s;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+-----+-----+--------+------------+
| id | name | age | sex | height | course_idd |
+----+--------+-----+-----+--------+------------+
| 1 | Dany | 25 | 1 | 160 | 1 |
| 2 | Green | 25 | 1 | 158 | 2 |
| 3 | Henry | 23 | 2 | 185 | 1 |
| 4 | Jane | 25 | 1 | 162 | 3 |
| 5 | Jim | 24 | 2 | 175 | 2 |
| 6 | John | 24 | 2 | 172 | 4 |
| 7 | Lily | 22 | 1 | 165 | 4 |
| 8 | Susan | 23 | 1 | 170 | 5 |
| 9 | Tomas | 22 | 2 | 178 | 5 |
| 10 | Tom | 23 | 2 | 165 | 5 |
| 11 | LiMing | 22 | 1 | 180 | 6 |
| 12 | liu | 18 | 1 | 177 | 3 |
| 13 | yang | 19 | 1 | 166 | 2 |
+----+--------+-----+-----+--------+------------+
浙公网安备 33010602011771号