MySQL系列-语法说明以及基本操作（三）

最新推荐文章于 2024-07-23 13:22:23 发布

七夜琉璃

最新推荐文章于 2024-07-23 13:22:23 发布

阅读量818

点赞数 25

分类专栏： MySQL 文章标签： mysql adb android

本文链接：https://blog.csdn.net/qiyeliuli/article/details/139625660

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1、前言

主要讲解MySQL的各种连接的含义。

2、MySQL INNER JOIN：内连接查询

内连接是通过在查询中设置连接条件的方式，来移除查询结果集中某些数据行后的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。

在 MySQL FROM 子句中使用关键字 INNER JOIN 连接两张表，并使用 ON 子句来设置连接条件。如果没有任何条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。

内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只用关键字 JOIN。使用内连接后，FROM 子句中的 ON 子句可用来设置连接表的条件。
【实例 1】
在 tb_students_info 表和 tb_departments 表之间，使用 INNER JOIN 语法进行内连接查询，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT id,name,age,dept_name
    -> FROM tb_students_info INNER JOIN tb_departments
    -> WHERE tb_students_info.dept_id=tb_departments.dept_id;
+----+--------+------+-----------+
| id | name   | age  | dept_name |
+----+--------+------+-----------+
|  1 | Dany   |   25 | Computer  |
|  2 | Green  |   23 | Chinese   |
|  3 | Henry  |   23 | Math      |
|  4 | Jane   |   22 | Computer  |
|  5 | Jim    |   24 | Computer  |
|  6 | John   |   21 | Math      |
|  7 | Lily   |   22 | Computer  |
|  8 | Susan  |   23 | Economy   |
|  9 | Thomas |   22 | Chinese   |
| 10 | Tom    |   23 | Economy   |
+----+--------+------+-----------+
10 rows in set (0.00 sec)

3、MySQL LEFT/RIGHT JOIN：外连接查询

MySQL 中内连接是在交叉连接的结果集上返回满足条件的记录；而外连接先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

外连接更加注重两张表之间的关系。按照连接表的顺序，可以分为左外连接和右外连接。

左外连接又称为左连接，在 FROM 子句中使用关键字 LEFT OUTER JOIN 或者 LEFT JOIN，用于接收该关键字左表（基表）的所有行，并用这些行与该关键字右表（参考表）中的行进行匹配，即匹配左表中的每一行及右表中符合条件的行。

在左外连接的结果集中，除了匹配的行之外，还包括左表中有但在右表中不匹配的行，对于这样的行，从右表中选择的列的值被设置为 NULL，即左外连接的结果集中的 NULL 值表示右表中没有找到与左表相符的记录。

【实例 1】在 tb_students_info 表和 tb_departments 表中查询所有学生，包括没有学院的学生，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name,dept_name
    -> FROM tb_students_info s
    -> LEFT OUTER JOIN tb_departments d
    -> ON s.dept_id = d.dept_id;
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| Dany   | Computer  |
| Jane   | Computer  |
| Jim    | Computer  |
| Henry  | Math      |
| John   | Math      |
| Green  | Chinese   |
| Thomas | Chinese   |
| Susan  | Economy   |
| Tom    | Economy   |
| Lily   | NULL      |
+--------+-----------+
10 rows in set (0.03 sec)

结果显示了 10 条记录，name 为 Lily 的学生目前没有学院，因为对应的 tb_departments 表中并没有该学生的学院信息，所以该条记录只取出了 tb_students_info 表中相应的值，而从 tb_departments 表中取出的值为 NULL。

右外连接又称为右连接，在 FROM 子句中使用 RIGHT OUTER JOIN 或者 RIGHT JOIN。与左外连接相反，右外连接以右表为基表，连接方法和左外连接相同。在右外连接的结果集中，除了匹配的行外，还包括右表中有但在左表中不匹配的行，对于这样的行，从左表中选择的值被设置为 NULL。

【实例 2】在 tb_students_info 表和 tb_departments 表中查询所有学院，包括没有学生的学院，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name,dept_name
    -> FROM tb_students_info s
    -> RIGHT OUTER JOIN tb_departments d
    -> ON s.dept_id = d.dept_id;
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| Dany   | Computer  |
| Green  | Chinese   |
| Henry  | Math      |
| Jane   | Computer  |
| Jim    | Computer  |
| John   | Math      |
| Susan  | Economy   |
| Thomas | Chinese   |
| Tom    | Economy   |
| NULL   | History   |
+--------+-----------+
10 rows in set (0.00 sec)

4、MySQL子查询详解

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。

子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。

【实例 1】在 tb_departments 表中查询 dept_type 为 A 的学院 ID，并根据学院 ID查询该学院学生的名字，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id IN
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type= 'A' );
+-------+
| name  |
+-------+
| Dany  |
| Henry |
| Jane  |
| Jim   |
| John  |
+-------+
5 rows in set (0.01 sec)

【实例 2】与前一个例子类似，但是在 SELECT 语句中使用 NOT IN 关键字，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id NOT IN
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type='A');
+--------+
| name   |
+--------+
| Green  |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
+--------+
5 rows in set (0.04 sec)

【实例 3】查询 tb_departments 表中是否存在 dept_id=1 的供应商，如果存在，就查询tb_students_info 表中的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_students_info
    -> WHERE EXISTS
    -> (SELECT dept_name
    -> FROM tb_departments
    -> WHERE dept_id=1);
+----+--------+---------+------+------+--------+------------+
| id | name   | dept_id | age  | sex  | height | login_date |
+----+--------+---------+------+------+--------+------------+
|  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 |
|  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 |
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
|  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
| 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.00 sec)

由结果可以看到，内层查询结果表明 tb_departments 表中存在 dept_id=1 的记录，因此 EXSTS 表达式返回 TRUE，外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询，返回所有的记录。

EXISTS 关键字可以和条件表达式一起使用。

【实例 4】查询 tb_departments 表中是否存在 dept_id=7 的供应商，如果存在，就查询 tb_students_info 表中的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_students_info
    -> WHERE EXISTS
    -> (SELECT dept_name
    -> FROM tb_departments
    -> WHERE dept_id=7);
Empty set (0.00 sec)

5、MySQL GROUP BY：分组查询

在 MySQL SELECT 语句中，允许使用 GROUP BY 子句，将结果集中的数据行根据选择列的值进行逻辑分组，以便能汇总表内容的子集，实现对每个组而不是对整个结果集进行整合。
注意：GROUP BY 子句中的各选择列必须也是 SELECT 语句的选择列清单中的一项。

对于 GROUP BY 子句的使用，需要注意以下几点。

GROUP BY 子句可以包含任意数目的列，使其可以对分组进行嵌套，为数据分组提供更加细致的控制。
GROUP BY 子句列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。
若在 SELECT 语句中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。
除聚合函数之外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
若用于分组的列中包含有 NULL 值，则 NULL 将作为一个单独的分组返回；若该列中存在多个 NULL 值，则将这些 NULL 值所在的行分为一组。

【实例】根据 dept_id 对 tb_students_info 表中的数据进行分组，将每个学院的学生姓名显示出来，输入的SQL语句和执行结果如下所示。

mysql> SELECT dept_id,GROUP_CONCAT(name) AS names
    -> FROM tb_students_info
    -> GROUP BY dept_id;
+---------+---------------+
| dept_id | names         |
+---------+---------------+
|       1 | Dany,Jane,Jim |
|       2 | Henry,John    |
|       3 | Green,Thomas  |
|       4 | Susan,Tom     |
|       6 | Lily          |
+---------+---------------+
5 rows in set (0.02 sec)

6、MySQL HAVING：指定过滤条件

HAVING 子句和 WHERE 子句非常相似，HAVING 子句支持 WHERE 子句中所有的操作符和语法，但是两者存在几点差异：

WHERE 子句主要用于过滤数据行，而 HAVING 子句主要用于过滤分组，即 HAVING子句基于分组的聚合值而不是特定行的值来过滤数据，主要用来过滤分组。
WHERE 子句不可以包含聚合函数，HAVING子句中的条件可以包含聚合函数。 HAVING 子句是在数据分组后进行过滤，WHERE 子句会在数据分组前进行过滤。
WHERE子句排除的行不包含在分组中，可能会影响 HAVING 子句基于这些值过滤掉的分组。
【实例】根据 dept_id 对 tb_students_info 表中的数据进行分组，并显示学生人数大于1的分组信息，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT dept_id,GROUP_CONCAT(name) AS names
    -> FROM tb_students_info
    -> GROUP BY dept_id
    -> HAVING COUNT(name)>1;
+---------+---------------+
| dept_id | names         |
+---------+---------------+
|       1 | Dany,Jane,Jim |
|       2 | Henry,John    |
|       3 | Green,Thomas  |
|       4 | Susan,Tom     |
+---------+---------------+
4 rows in set (0.07 sec)

7、MySQL REGEXP：正则表达式查询

MySQL中正式表达式通常被用来检索或替换符合某个模式的文本内容，根据指定的匹配模式匹配文中符合要求的特殊字符串。

例如，从一个文件中提取电话号码，查找一篇文章中重复的单词或替换用户输入的敏感语汇等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，常用于复杂的查询。

MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式，下表列出了 REGEXP 操作符中常用的匹配列表。
在这里插入图片描述
查询以特定字符或字符串开头的记录
字符“^”匹配以特定字符或者字符串开头的文本。

【实例 1】在 tb_departments 表中，查询 dept_name 字段以字母“C”开头的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^C';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.05 sec)

在 tb_departments 表中有两条记录的 dept_name 字段值是以字母 C 开头的，返回结果有 2 条记录。

8、MySQL INSERT：插入数据（添加数据）

8.1、向表中的全部字段添加值

mysql> INSERT INTO tb_courses
    -> (course_id,course_name,course_grade,course_info)
    -> VALUES(1,'Network',3,'Computer Network');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
+-----------+-------------+--------------+------------------+
1 row in set (0.00 sec)

INSERT 语句中没有指定插入列表，只有一个值列表。在这种情况下，值列表为每一个字段列指定插入的值，并且这些值的顺序必须和 tb_courses 表中字段定义的顺序相同

mysql> INSERT INTO tb_courses
    -> VLAUES(3,'Java',4,'Java EE');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
+-----------+-------------+--------------+------------------+
3 rows in set (0.00 sec)

注意：虽然使用 INSERT 插入数据时可以忽略插入数据的列名称，若值不包含列名称，则 VALUES 关键字后面的值不仅要求完整，而且顺序必须和表定义时列的顺序相同。如果表的结构被修改，对列进行增加、删除或者位置改变操作，这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称，就不会受到表结构改变的影响。

9、MySQL UPDATE：修改数据（更新数据）

根据条件修改表中的数据
在 tb_courses 表中，更新 course_id 值为 2 的记录，将 course_grade 字段值改为 3.5，将 course_name 字段值改为“DB”，输入的 SQL 语句和执行结果如下所示。

mysql> UPDATE tb_courses_new
    -> SET course_name='DB',course_grade=3.5
    -> WHERE course_id=2;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM tb_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            4 | Computer Network |
|         2 | DB          |          3.5 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            4 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)