mysql（3）：基础，常用命令句使用（2）--中集

      续接上一回，上一回讲述了Mysql的很多基础语法，但是，通常工作中使用到的是高级语法，比如联合、索引、排序、分组、事务等等。下面做个总结：

 十三、 Mysql 连接的使用

       上面（mysql（3）：基础，常用命令句使用（2）--上集）是讲述如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

      在使用SELECT, UPDATE 和 DELETE 语句 中使用 Mysql 的JOIN 来 联合多表查询。使用的数据库结构及数据下载：runoob-mysql-join-test.sql。

     JOIN 按照功能大致分为如下三类：
（1）INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
（2）LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
（3）RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

在命令提示符中使用 INNER JOIN

我们在RUNOOB数据库中有两张表 tcount_tbl 和 runoob_tbl。两张数据表数据如下：

实例

尝试以下实例：

测试实例数据

mysql>useRUNOOB;Databasechangedmysql>SELECT *FROMtcount_tbl;

+---------------+--------------+

| runoob_author |runoob_count |

+---------------+--------------+

| 菜鸟教程 |10 |

|RUNOOB.COM | 20 |

|Google |22 |

+---------------+--------------+

3rowsinset(0.01sec)

mysql>SELECT *fromrunoob_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 |

+-----------+---------------+---------------+-----------------+

5rowsinset(0.01sec)

接下来我们就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：

INNER JOIN

mysql>SELECTa.runoob_id,a.runoob_author,b.runoob_countFROMrunoob_tblaINNERJOINtcount_tblbONa.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 |

+-------------+-----------------+----------------+

4rowsinset(0.00sec)

以上 SQL 语句等价于：

WHERE 子句

mysql>SELECTa.runoob_id,a.runoob_author,b.runoob_countFROMrunoob_tbla,tcount_tblbWHEREa.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 |

+-------------+-----------------+----------------+

4rowsinset(0.01sec)

---

MySQL LEFT JOIN

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

实例

尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解 MySQL LEFT JOIN 的应用：

LEFT JOIN

mysql>SELECTa.runoob_id,a.runoob_author,b.runoob_countFROMrunoob_tblaLEFTJOINtcount_tblbONa.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 |

+-------------+-----------------+----------------+

5rowsinset(0.01sec)

以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。

---

MySQL RIGHT JOIN

MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。

实例

尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解MySQL RIGHT JOIN的应用：

RIGHT JOIN

mysql>SELECTa.runoob_id,a.runoob_author,b.runoob_countFROMrunoob_tblaRIGHTJOINtcount_tblbONa.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 |

+-------------+-----------------+----------------+

5rowsinset(0.01sec)

以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

十四、MySQL NULL 值处理

      MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。为了处理这种情况，MySQL提供了三大运算符:
        IS NULL: 当列的值是 NULL,此运算符返回 true。
        IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
        <=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。

     关于 NULL 的条件比较运算是比较特殊的。

     你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回false，即 NULL = NULL 返回false 。MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

在命令提示符中使用 NULL 值

以下实例中假设数据库 RUNOOB 中的表 runoob_test_tbl 含有两列 runoob_author 和 runoob_count, runoob_count 中设置插入NULL值。

实例

尝试以下实例:

创建数据表 runoob_test_tbl

root@host# mysql -u root -p password;Enterpassword:*******

mysql>useRUNOOB;Databasechanged

mysql>createtablerunoob_test_tbl

-> (

-> runoob_authorvarchar(40)NOTNULL,

-> runoob_countINT ->);

QueryOK,0rowsaffected(0.05sec)

mysql>INSERTINTOrunoob_test_tbl(runoob_author,runoob_count)values('RUNOOB',20);

mysql>INSERTINTOrunoob_test_tbl(runoob_author,runoob_count)values('菜鸟教程',NULL);

mysql>INSERTINTOrunoob_test_tbl(runoob_author,runoob_count)values('Google',NULL);

mysql>INSERTINTOrunoob_test_tbl(runoob_author,runoob_count)values('FK',20);

mysql>SELECT *fromrunoob_test_tbl;

+---------------+--------------+

| runoob_author |runoob_count |

+---------------+--------------+

| RUNOOB |20 || 菜鸟教程 |NULL |

| Google |NULL ||FK |20 |

+---------------+--------------+

4rowsinset(0.01sec)

以下实例中你可以看到 = 和 != 运算符是不起作用的：

mysql>SELECT *FROMrunoob_test_tblWHERErunoob_count =NULL;

Emptyset(0.00sec)

mysql>SELECT *FROMrunoob_test_tblWHERErunoob_count !=NULL;

Emptyset(0.01sec)

查找数据表中 runoob_test_tbl 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL，如下实例：

mysql>SELECT *FROMrunoob_test_tblWHERErunoob_countISNULL;

+---------------+--------------+

| runoob_author |runoob_count |

+---------------+--------------+

| 菜鸟教程 |NULL |

| Google |NULL |

+---------------+--------------+

2rowsinset(0.01sec)

mysql>SELECT *fromrunoob_test_tblWHERErunoob_countISNOTNULL;

+---------------+--------------+

| runoob_author |runoob_count |

+---------------+--------------+

| RUNOOB |20 |

| FK |20 |

+---------------+--------------+

2rowsinset(0.01sec)

十五、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$';

十六、MySQL 事务

         MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

• 1、事务的原子性：一组事务，要么成功；要么撤回。
• 2、稳定性 ：有非法数据（外键约束之类），事务撤回。
• 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

• 事务控制语句：
  

• BEGIN或START TRANSACTION；显式地开启一个事务；

• COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

• ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

• SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

• RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

• ROLLBACK TO identifier；把事务回滚到标记点；

• SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

事务测试

mysql>useRUNOOB;Databasechanged

mysql>CREATETABLErunoob_transaction_test(idint(5))engine=innodb;# 创建数据表

QueryOK,0rowsaffected(0.04sec)

mysql>select *fromrunoob_transaction_test;

Emptyset(0.01sec)

mysql>begin;# 开始事务

QueryOK,0rowsaffected(0.00sec)

mysql>insertintorunoob_transaction_testvalue(5);

QueryOK,1rowsaffected(0.01sec)

mysql>insertintorunoob_transaction_testvalue(6);

QueryOK,1rowsaffected(0.00sec)

mysql>commit;# 提交事务

QueryOK,0rowsaffected(0.01sec)

mysql>select *fromrunoob_transaction_test;

+------+

| id |

+------+

| 5 |

| 6 |

+------+

2rowsinset(0.01sec)

mysql>begin;# 开始事务

QueryOK,0rowsaffected(0.00sec)

mysql>insertintorunoob_transaction_testvalues(7);

QueryOK,1rowsaffected(0.00sec)

mysql>rollback;# 回滚

QueryOK,0rowsaffected(0.00sec)

mysql>select *fromrunoob_transaction_test;# 因为回滚所以数据没有插入

+------+

|id |

+------+

|5 |

|6 |

+------+

2rowsinset(0.01sec)

mysql>

十七、MySQL ALTER命令

详见：mysql（12）：基础，ALTER 的语法介绍