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

原创 于 2017-12-10 22:47:07 发布 · 373 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文深入讲解MySQL中的连接查询、NULL值处理、正则表达式、事务管理及表结构修改等高级主题，帮助读者掌握实际工作中常用的复杂操作。

      续接上一回，上一回讲述了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 > use RUNOOB ; Database changed mysql > 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字段值：

INNER JOIN

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 语句等价于：

WHERE 子句

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 )

MySQL LEFT JOIN

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

实例

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

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 字段值。

MySQL RIGHT JOIN

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

实例

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

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 字段值。




十四、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; Enter password :*******
mysql > use RUNOOB ; Database changed
mysql > 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 )



十五、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 > use RUNOOB ; Database changed
mysql > CREATE TABLE runoob_transaction_test ( id int ( 5 ) ) engine = innodb ; # 创建数据表
Query OK , 0 rows affected ( 0.04 sec )

mysql > select * from runoob_transaction_test ;
Empty set ( 0.01 sec )

mysql > begin ; # 开始事务
Query OK , 0 rows affected ( 0.00 sec )

mysql > insert into runoob_transaction_test value ( 5 ) ;
Query OK , 1 rows affected ( 0.01 sec )

mysql > insert into runoob_transaction_test value ( 6 ) ;
Query OK , 1 rows affected ( 0.00 sec )

mysql > commit ; # 提交事务
Query OK , 0 rows affected ( 0.01 sec )

mysql > select * from runoob_transaction_test ;
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set ( 0.01 sec )

mysql > begin ; # 开始事务
Query OK , 0 rows affected ( 0.00 sec )

mysql > insert into runoob_transaction_test values ( 7 ) ;
Query OK , 1 rows affected ( 0.00 sec )

mysql > rollback ; # 回滚
Query OK , 0 rows affected ( 0.00 sec )

mysql > select * from runoob_transaction_test ; # 因为回滚所以数据没有插入
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set ( 0.01 sec )

mysql >


十七、MySQL ALTER命令

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





评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

后台技术汇

对你的帮助，是对我的最好鼓励。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值