mysql进阶2
内连接
内连接(INNER JOIN)主要通过设置连接条件的方式,来移除查询结果中某些数据行的交叉连接。简单来说,就是利用条件表达式来消除交叉连接的某些数据行。
内连接使用 INNER JOIN 关键字连接两张表,并使用 ON 子句来设置连接条件。如果没有连接条件,INNER JOIN 和 CROSS JOIN 在语法上是等同的,两者可以互换。
内连接的语法格式如下:
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];
语法说明如下。
- 字段名:需要查询的字段名称。
- <表1><表2>:需要内连接的表名。
- INNER JOIN :内连接中可以省略 INNER 关键字,只用关键字 JOIN。
- ON 子句:用来设置内连接的连接条件。
INNER JOIN 也可以使用 WHERE 子句指定连接条件,但是 INNER JOIN …ON 语法是官方的标准写法,而且 WHERE 子句在某些时候会影响查询的性能。
多个表内连接时,在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。
内连接可以查询两个或两个以上的表。为了更好的理解,暂时只讲解两个表的连接查询。
MariaDB [runtime]> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id;
+--------+-------------+
| name | course_name |
+--------+-------------+
| Dany | Java |
| Henry
| Java |
| Green | Nysql |
| Jim | Nysql |
| Jane | Python |
| john | Go |
| Lilyi | Go |
| Suan | C++ |
| Thomas | C++ |
| Tom | C++ |
+--------+-------------+
10 rows in set (0.001 sec)
MariaDB [runtime]> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and c.course_name = 'Nysql';
+-------+-------------+
| name | course_name |
+-------+-------------+
| Green | Nysql |
| Jim | Nysql |
+-------+-------------+
2 rows in set (0.000 sec)
MariaDB [runtime]> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and (c.course_name = 'Nysql'or c.course_name = 'Python');
+-------+-------------+
| name | course_name |
+-------+-------------+
| Green | Nysql |
| Jim | Nysql |
| Jane | Python |
+-------+-------------+
MariaDB [runtime]> select count(s.name),c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and (c.course_name = 'Nysql'or c.course_name = 'Python');
+---------------+-------------+
| count(s.name) | course_name |
+---------------+-------------+
| 3 | Nysql |
+---------------+-------------+
1 row in set (0.000 sec)
MariaDB [runtime]> select count(s.name),c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and (c.course_name = 'Nysql'or c.course_name = 'Python') group by c.course_name;
+---------------+-------------+
| count(s.name) | course_name |
+---------------+-------------+
| 2 | Nysql |
| 1 | Python |
+---------------+-------------+
2 rows in set (0.001 sec)
外连接
内连接的查询结果都是符合连接条件的记录,而外连接会先将连接的表分为基表和参考表,再以基表为依据返回满足和不满足条件的记录。
外连接可以分为左外连接和右外连接2种,下面根据实例分别介绍左外连接和右外连接。
左连接,右连接
左外连接又称为左连接,使用 LEFT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件。
左连接的语法格式如下:
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
右外连接又称为右连接,右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件。
右连接的语法格式如下:
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
语法说明如下:
- 字段名:需要查询的字段名称。
- <表1><表2>:需要左连接的表名。
- LEFT OUTER JOIN:左连接中可以省略 OUTER 关键字,只使用关键字 LEFT JOIN。
- ON 子句:用来设置左连接的连接条件,不能省略。
上述语法中,"表1"为基表,"表2"为参考表。左连接查询时,可以查询出"表1"中的所有记录和"表2"中匹配连接条件的记录。如果"表1"的某行在"表2"中没有匹配行,那么在返回结果中,"表2"的字段值均为空值(NULL)。
MariaDB [runtime]> insert into tb_course (id,course_name) values (6,'HTML');
Query OK, 1 row affected (0.002 sec)
MariaDB [runtime]> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | Nysql |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
| 6 | HTML |
+----+-------------+
6 rows in set (0.000 sec)
MariaDB [runtime]> insert into tb_students_info (id,name,age,sex,height,course_id) values (11,'liming',22,'M',180,7);
Query OK, 1 row affected (0.001 sec)
MariaDB [runtime]> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | w | 160 | 1 |
| 2 | Green | 23 | w | 158 | 2 |
| 3 | Henry
| 23 | m | 185 | 1 |
| 4 | Jane | 22 | w | 12 | 3 |
| 5 | Jim | 24 | m | 175 | 2 |
| 6 | john | 21 | m | 172 | 4 |
| 7 | Lilyi | 22 | m | 165 | 4 |
| 8 | Suan | 23 | w | 170 | 5 |
| 9 | Thomas | 22 | w | 178 | 5 |
| 10 | Tom | 23 | w | 165 | 5 |
| 11 | liming | 22 | M | 180 | 7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.000 sec)
MariaDB [runtime]> SELECT s.name,c.course_name FROM tb_students_info s LEFT OUTER JOIN tb_course c ON s.course_id = c.id;
+--------+-------------+
| name | course_name |
+--------+-------------+
| Dany | Java |
| Green | Nysql |
| Henry
| Java |
| Jane | Python |
| Jim | Nysql |
| john | Go |
| Lilyi | Go |
| Suan | C++ |
| Thomas | C++ |
| Tom | C++ |
| liming | NULL |
+--------+-------------+
11 rows in set (0.000 sec)
MariaDB [runtime]> SELECT s.name,c.course_name FROM tb_students_info s right OUTER JOIN tb_course c ON s.course_id = c.id;
+--------+-------------+
| name | course_name |
+--------+-------------+
| Dany | Java |
| Green | Nysql |
| Henry
| Java |
| Jane | Python |
| Jim | Nysql |
| john | Go |
| Lilyi | Go |
| Suan | C++ |
| Thomas | C++ |
| Tom | C++ |
| NULL | HTML |
+--------+-------------+
11 rows in set (0.000 sec)
分组查询
MySQL 中,GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
使用 GROUP BY 关键字的语法格式如下:
GROUP BY <字段名>
MariaDB [runtime]> select name,sex from tb_students_info group by sex;
+--------+------+
| name | sex |
+--------+------+
| Henry
| m |
| Dany | w |
+--------+------+
2 rows in set (0.000 sec)
GROUP BY 与 GROUP_CONCAT()
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。
MariaDB [runtime]> select sex,group_concat(name) from tb_students_info group by sex;
+------+---------------------------------+
| sex | group_concat(name) |
+------+---------------------------------+
| M | liming,Lilyi,john,Jim,Henry
|
| w | Jane,Green,Suan,Thomas,Tom,Dany |
+------+---------------------------------+
2 rows in set (0.001 sec)
MariaDB [runtime]>
由结果可以看到,查询结果分为两组,sex 字段值为"女"的是一组,值为"男"的是一组,且每组的学生姓名都显示出来了。
下面根据 tb_students_info 表中的 age 和 sex 字段进行分组查询。SQL 语句和运行结果如下:
MariaDB [runtime]> select age,sex,group_concat(name) from tb_students_info group by age,sex;
+------+------+--------------------+
| age | sex | group_concat(name) |
+------+------+--------------------+
| 21 | m | john |
| 22 | m | Lilyi,liming |
| 22 | w | Jane,Thomas |
| 23 | m | Henry
|
| 23 | w | Green,Suan,Tom |
| 24 | m | Jim |
| 25 | w | Dany |
+------+------+--------------------+
7 rows in set (0.000 sec)
MariaDB [runtime]>
上面实例在分组过程中,先按照 age 字段进行分组,当 age 字段值相等时,再把 age 字段值相等的记录按照 sex 字段进行分组。
多个字段分组查询时,会先按照第一个字段进行分组。如果第一个字段中有相同的值,MySQL 才会按照第二个字段进行分组。如果第一个字段中的数据都是唯一的,那么 MySQL 将不再对第二个字段进行分组。
GROUP BY 与聚合函数
在数据统计时,GROUP BY 关键字经常和聚合函数一起使用。
聚合函数包括 COUNT(),SUM(),AVG(),MAX() 和 MIN()。其中,COUNT() 用来统计记录的条数;SUM() 用来计算字段值的总和;AVG() 用来计算字段值的平均值;MAX() 用来查询字段的最大值;MIN() 用来查询字段的最小值。
下面根据 tb_students_info 表的 sex 字段进行分组查询,使用 COUNT() 函数计算每一组的记录数
MariaDB [runtime]> select sex,count(sex) from tb_students_info group by sex;
+------+------------+
| sex | count(sex) |
+------+------------+
| m | 5 |
| w | 6 |
+------+------------+
2 rows in set (0.000 sec)
MariaDB [runtime]>
GROUP BY 与 WITH ROLLUP
WITH POLLUP 关键字用来在所有记录的最后加上一条记录,这条记录是上面所有记录的总和,即统计记录数量。
下面根据 tb_students_info 表中的 sex 字段进行分组查询,并使用 WITH ROLLUP 显示记录的总和:
MariaDB [runtime]> select sex,group_concat(name) from tb_students_info group by sex with rollup;
+------+--------------------------------------------------------------+
| sex | group_concat(name) |
+------+--------------------------------------------------------------+
| M | liming,Lilyi,john,Jim,Henry
|
| w | Jane,Green,Suan,Thomas,Tom,Dany |
| NULL | liming,Lilyi,john,Jim,Henry
,Jane,Green,Suan,Thomas,Tom,Dany |
+------+--------------------------------------------------------------+
3 rows in set (0.000 sec)
子查询
子查询是 MySQL 中比较常用的查询方法,通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用,而且可以进行多层嵌套。在实际开发时,子查询经常出现在 WHERE 子句中。
子查询在 WHERE 中的语法格式如下:
WHERE <表达式> <操作符> (子查询)
其中,操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
1)IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回值正好相反。
2)EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空,若子查询的结果集不为空,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回的值正好相反。
例 1
使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名,
MariaDB [runtime]> select name from tb_students_info where course_id in (select id from tb_course where course_name = 'JaVa');
+--------+
| name |
+--------+
| Dany |
| Henry
|
+--------+
2 rows in set (0.001 sec)
MariaDB [runtime]>
结果显示,学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行,实现效果是相同的。
首先单独执行内查询,查询出 tb_course 表中课程为 Java 的 id
MariaDB [runtime]> select id from tb_course
-> where course_name = 'JaVa';
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.000 sec)
MariaDB [runtime]>
可以看到,符合条件的 id 字段的值为 1。
然后执行外层查询,在 tb_students_info 表中查询 course_id 等于 1 的学生姓名。
MariaDB [runtime]> select name from tb_students_info where course_id in (1);
+--------+
| name |
+--------+
| Dany |
| Henry
|
+--------+
2 rows in set (0.000 sec)
MariaDB [runtime]>
在 SELECT 语句中使用 NOT IN 关键字,查询没有学习 Java 课程的学生姓名
MariaDB [runtime]> select name from tb_students_info where course_id not in (select id from tb_course where course_name = 'JaVa');
+--------+
| name |
+--------+
| Green |
| Jane |
| Jim |
| john |
| Lilyi |
| Suan |
| Thomas |
| Tom |
| liming |
+--------+
9 rows in set (0.001 sec)
MariaDB [runtime]>
使用=运算符,在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名
MariaDB [runtime]> select name from tb_students_info where course_id = (select id from tb_course where course_name = 'Python');
+------+
| name |
+------+
| Jane |
+------+
1 row in set (0.000 sec)
使用<>运算符,在 tb_course 表和 tb_students_info 表中查询出没有学习 Python 课程的学生姓名
MariaDB [runtime]> select name from tb_students_info where course_id <> (select id from tb_course where course_name = 'Python');
+--------+
| name |
+--------+
| Dany |
| Green |
| Henry
|
| Jim |
| john |
| Lilyi |
| Suan |
| Thomas |
| Tom |
| liming |
+--------+
10 rows in set (0.001 sec)
查询 tb_course 表中是否存在 id=1 的课程,如果存在,就查询出 tb_students_info 表中 age 字段大于 24 的记录
MariaDB [runtime]> select * from tb_students_info where age > 24 and exists (select course_name from tb_course where id = 1);
+----+------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+------+------+------+--------+-----------+
| 1 | Dany | 25 | w | 160 | 1 |
+----+------+------+------+--------+-----------+
1 row in set (0.001 sec)
MariaDB [runtime]>
数据库备份方案
冷备份:先把数据库停掉,然后拷贝数据库目录下的文件进行备份
物理备份
温备份
热备份:数据库正常运行的情况下进行备份,直接对数据库的数据进行备份,全量倍份、增量备份、差异备份。
备份方案 | 特点 |
---|---|
全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
增量备份 | 增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着,第一次增量备份的对象 是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件,如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时,我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use runtime ;
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
MariaDB [runtime]> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| tb_course |
| tb_students_info |
+-------------------+
2 rows in set (0.000 sec)
MariaDB [runtime]>
[root@localhost ~]# ls
anaconda-ks.cfg
[root@localhost ~]# mysqldump -uroot -p123 --all databases > kang-20220728.sql //备份所有助理
mysqldump: ambiguous option '--all' (all-databases, allow-keywords)
[root@localhost ~]# ls
anaconda-ks.cfg kang-20220728.sql
[root@localhost ~]# mysqldump -uroot -p123 runtime tb_students_info tb_course > table-20220728.sql //备份runtime下的两个表
[root@localhost ~]# ls
anaconda-ks.cfg kang-20220728.sql table-20220728.sql
[root@localhost ~]# mysqldump -uroot -p123 --databases runtime > tk-20220728.sql //备份runtime数据库。
[root@localhost ~]# ls
anaconda-ks.cfg kang-20220728.sql table-20220728.sql tk-20220728.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> drop database runtime; #删掉数据库。
Query OK, 2 rows affected (0.015 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
[root@localhost ~]# mysql -uroot -p123 <tk-20220728.sql
[root@localhost ~]# mysql -uroot -p123 -e 'show databses;'
[root@localhost ~]# mysql -uroot -p123 -e 'show databases';
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
+--------------------+
[root@localhost ~]#
MariaDB [(none)]> use runtime
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
MariaDB [runtime]> source table-20220728.sql;
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
...
MariaDB [runtime]> show tables; //#表里面的数据已经恢复
+-------------------+
| Tables_in_runtime |
+-------------------+
| tb_course |
| tb_students_info |
+-------------------+
2 rows in set (0.000 sec)
MariaDB [runtime]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> drop database runtime; #删除数据库
Query OK, 2 rows affected (0.004 sec)
MariaDB [(none)]> show databases; #查看到已经删除
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@localhost ~]# mysql -uroot -p123 < tk-20220728.sql //用备份恢复数据库
[root@localhost ~]# mysql -uroot -p123 -e 'show databases;' //查看回复的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
+--------------------+
[root@localhost ~]#
bases; #查看到已经删除
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
±-------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@localhost ~]# mysql -uroot -p123 < tk-20220728.sql //用备份恢复数据库
[root@localhost ~]# mysql -uroot -p123 -e ‘show databases;’ //查看回复的数据库
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
±-------------------+
[root@localhost ~]#