1、MySQL连接的使用
- 我们已经学会了如何在一张表中读取数据，这是相当简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
- 这里将向大家介绍如何使用MySQL的JOIN在两个或者多个表中查询数据。
- 你可以在SELECT、UPDATE和DELETE语句中使用MySQL的JOIN来联合多表查询。
- JOIN按照功能大致分为三类：a、INNER JOIN（内连接或者等值连接）：获取两个表中字段匹配关系的记录；b、LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录；RIGHT JOIN（右连接）：与LEFT JOIN相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
- 在命令行提示符中使用INNER JOIN：我们在RUNOOB数据库中有两张表tcount_tbl和runoob_tbl。两张表中的数据如下：
mysql> use RUNOOB;Database changedmysql> SELECT * FROM tcount_tbl; +---------------+--------------+ | runoob_author | runoob_count | +---------------+--------------+ | 菜鸟教程 | 10 | | RUNOOB.COM | 20 | | Google | 22 | +---------------+--------------+3 rows in set (0.01 sec) mysql> SELECT * from runoob_tbl; +-----------+---------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 | | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 | | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | | 5 | 学习 C | FK | 2017-04-05 | +-----------+---------------+---------------+-----------------+5 rows in set (0.01 sec)
- 接下来我们就使用MySQL的INNER JOIN（也可以省略INNER使用JOIN，效果一样）来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.00 sec
- 以上SQL语句等价于：
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.01 sec)
- 在命令行提示符中使用LEFT JOIN：MySQL LEFT JOIN会读取左表数据表的全部数据，即使右边表无对应数据。
- 尝试一下实例，以runoob_tbl为左表，tcount_tbl为右表，理解MySQL LEFT JOIN的应用：
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | 5 | FK | NULL | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
- 以上实例中使用了LEFT JOIN，该语句会读取左边的数据表runoob_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的runoob_author字段值。
- 在命令行提示符中使用RIGHT JOIN：MySQL RIGHT JOIN会读取右边数据表的全部数据，即使左边表便无对应数据。
- 尝试一下实例，以runoob_tbl为左表，tcount_tbl为右表，理解MySQL RIGHT JOIN的应用：
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
- 以上实例中使用了RIGHT JOIN，该语句会读取右边的数据表中的tcount_tbl的所有选取的字段数据，即使在左侧表runoob_tbl中没有对应的runoob_author字段值。
2、MySQL NULL值的处理
- 我们已经知道MySQL使用SQL SELECT命令以及WHERE子句来读取数据表中的数据，但是当提供的查询条件字段为NULL时，该命令就无法正常工作。
- 为了处理这种情况，MySQL提供了三大运算符：a、IS NULL：当列的值时NULL，此运算符返回true；b、IS NOT NULL：当列的值不为NULL，运算符返回true；c、<=>：比较运算符（不同于=运算符），当比较的两个值为NULL时，返回true。
- 关于NULL的条件比较运算符时比较特殊的。你不能使用= NULL或者!= NULL在列中查找NULL值。
- 在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即NULL = NULL返回false。MySQL中处理NULL使用IS NULL 和IS NOT NULL运算符。
- 以下实例中假设数据库RUNOOB中的表runoob_test_tbl含有两列runoob_author和runoob_count、runoob_count中设置插入NULL值。
- 创建数据表runoob_test_tbl：
root@host# mysql -u root -p password;Enter password:*******mysql> use RUNOOB;Database changedmysql> create table runoob_test_tbl
-> (
-> runoob_author varchar(40) NOT NULL,
-> runoob_count INT
-> );Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('RUNOOB', 20);mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('菜鸟教程', NULL);mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('Google', NULL);mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('FK', 20);
mysql> SELECT * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| 菜鸟教程 | NULL |
| Google | NULL |
| FK | 20 |
+---------------+--------------+
4 rows in set (0.01 sec)- 以下实例中你可以看到 = 和 != 运算符是不起作用的：
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL; Empty set (0.00 sec) mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count != NULL; Empty set (0.01 sec)
- 查找数据表中runoob_test_tbl列是否为NULL，必须使用IS NULL和IS NOT NULL，如下实例：
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL; +---------------+--------------+ | runoob_author | runoob_count | +---------------+--------------+ | 菜鸟教程 | NULL | | Google | NULL | +---------------+--------------+ 2 rows in set (0.01 sec) mysql> SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL; +---------------+--------------+ | runoob_author | runoob_count | +---------------+--------------+ | RUNOOB | 20 | | FK | 20 | +---------------+--------------+ 2 rows in set (0.01 sec)
3、MySQL 正则表达式
- 在前面我们已经了解到MySQL可以通过LIKE...%来进行模糊匹配。
- MySQL同样也支持其他正则表达式的匹配，MySQL中使用REGEXP操作符来进行正则表的匹配。
- 如果你了解到PHP或者Perl，那么操作起来就非常的简单，因为MySQL的正则表达式匹配与这些脚本的类似。
- 下表中的正则表达式可应用于REGEXP操作符中：
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
- 了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的SQL语句，以下我们将列出几个小实例（表名为person_tbl）来加深我们的理解：
- 查询name字段中以'st'为开头的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
- 查询name字段以'ok'为结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
- 查询name字段中包含'mar'字符串的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
- 查找name字段中以元音字符开头或者以'ok'字符串结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
