MySQL教程-连接,NULL值处理与正则表达式
连接
背景介绍:笛卡尔积
![2231a5ea1e388b068a81ffaf5a326ccb.png](https://i-blog.csdnimg.cn/blog_migrate/7dbbfa420ff832ce2071633367ded3a3.jpeg)
表A与表B的笛卡尔积简单地来说就是将A的所有记录和B的所有记录强行拼接在一起,表A有m条记录,表B有n条记录,那么A与B笛卡尔积的结果就有
![equation?tex=m%5Ctimes+n](https://i-blog.csdnimg.cn/blog_migrate/efded31b5fda68c35f6e2c4d41408a99.png)
![equation?tex=A%3D%5C%7Bx_1%2Cx_2%2C...%2Cx_n%5C%7D%28x_i%3D%28y_1%2Cy_2%2C...%2Cy_%7Bn_1%7D%29%29%2CB%3D%5C%7Bp_1%2Cp_2%2C...%2Cp_m%5C%7D%28p_i%3D%28q_1%2Cq_2%2C...%2Cq_%7Bm_1%7D%29%29](https://i-blog.csdnimg.cn/blog_migrate/717255a33f2be55fa1f94e989658c23c.png)
![equation?tex=++A+%5Cbigotimes+B%3D%5C%7B%28x%2Cy%29%5C%2Cx%5Cin+A%2Cy%5Cin+B+%5C%7D++%E5%90%AB%E6%9C%89m%5Ctimes+n%E4%B8%AA%E8%AE%B0%E5%BD%95%EF%BC%8C%E6%AF%8F%E4%B8%AA%E8%AE%B0%E5%BD%95%E7%94%B1m_1%2Bn_1%E4%B8%AA%E5%AD%97%E6%AE%B5%E7%BB%84%E6%88%90](https://i-blog.csdnimg.cn/blog_migrate/154c664b1460ae5fcc253ea01a0c58aa.png)
看一个例子
两个表的内容如下
SELECT * FROM t_blog;
+----+-------+--------+
| id | title | typeId |
+----+-------+--------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | ddd | 4 |
| 5 | eee | 4 |
| 6 | fff | 3 |
| 7 | ggg | 2 |
| 8 | hhh | NULL |
| 9 | iii | NULL |
| 10 | jjj | NULL |
+----+-------+--------+
SELECT * FROM t_type;
+----+------------+
| id | name |
+----+------------+
| 1 | C++ |
| 2 | C |
| 3 | Java |
| 4 | C# |
| 5 | Javascript |
+----+------------+
对两个表做笛卡尔积
+----+-------+--------+----+------------+
| id | title | typeId | id | name |
+----+-------+--------+----+------------+
| 1 | aaa | 1 | 1 | C++ |
| 1 | aaa | 1 | 2 | C |
| 1 | aaa | 1 | 3 | Java |
| 1 | aaa | 1 | 4 | C# |
| 1 | aaa | 1 | 5 | Javascript |
| 2 | bbb | 2 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 2 | bbb | 2 | 3 | Java |
| 2 | bbb | 2 | 4 | C# |
| 2 | bbb | 2 | 5 | Javascript |
| 3 | ccc | 3 | 1 | C++ |
| 3 | ccc | 3 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 3 | ccc | 3 | 4 | C# |
| 3 | ccc | 3 | 5 | Javascript |
| 4 | ddd | 4 | 1 | C++ |
| 4 | ddd | 4 | 2 | C |
| 4 | ddd | 4 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 4 | ddd | 4 | 5 | Javascript |
| 5 | eee | 4 | 1 | C++ |
| 5 | eee | 4 | 2 | C |
| 5 | eee | 4 | 3 | Java |
| 5 | eee | 4 | 4 | C# |
| 5 | eee | 4 | 5 | Javascript |
| 6 | fff | 3 | 1 | C++ |
| 6 | fff | 3 | 2 | C |
| 6 | fff | 3 | 3 | Java |
| 6 | fff | 3 | 4 | C# |
| 6 | fff | 3 | 5 | Javascript |
| 7 | ggg | 2 | 1 | C++ |
| 7 | ggg | 2 | 2 | C |
| 7 | ggg | 2 | 3 | Java |
| 7 | ggg | 2 | 4 | C# |
| 7 | ggg | 2 | 5 | Javascript |
| 8 | hhh | NULL | 1 | C++ |
| 8 | hhh | NULL | 2 | C |
| 8 | hhh | NULL | 3 | Java |
| 8 | hhh | NULL | 4 | C# |
| 8 | hhh | NULL | 5 | Javascript |
| 9 | iii | NULL | 1 | C++ |
| 9 | iii | NULL | 2 | C |
| 9 | iii | NULL | 3 | Java |
| 9 | iii | NULL | 4 | C# |
| 9 | iii | NULL | 5 | Javascript |
| 10 | jjj | NULL | 1 | C++ |
| 10 | jjj | NULL | 2 | C |
| 10 | jjj | NULL | 3 | Java |
| 10 | jjj | NULL | 4 | C# |
| 10 | jjj | NULL | 5 | Javascript |
+----+-------+--------+----+------------+
内连接
- 求两个表的交集
- 对于笛卡尔积,调出所有满足ON字句条件成立的记录
实例
运行以下命令
SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
在笛卡尔积中,选出typeId=id的所有记录,可以看出来,一共有
![equation?tex=1%5Ctimes+1+%2B2%5Ctimes+1%2B2%5Ctimes+1%2B2+%5Ctimes+1%3D7](https://i-blog.csdnimg.cn/blog_migrate/1b7c3d59a50dbba19e87162f2bfcb683.png)
左连接
在运行完内连接之后,左表(本例中是t_blog表)有三条记录没有被使用,或者说没有在内连接生成的表中,我们将这三条记录加到内连接形成的表当中,对于t_type的字段,我们将其设置为NULL
实例
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
+----+-------+--------+------+------+
| id | title | typeId | id | name |
+----+-------+--------+------+------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 8 | hhh | NULL | NULL | NULL |
| 9 | iii | NULL | NULL | NULL |
| 10 | jjj | NULL | NULL | NULL |
+----+-------+--------+------+------+
右连接与之同理
外连接
相当于左连接和右连接取并集,上面两个表做外连接结果如下
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
UNION
SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
+------+-------+--------+------+------------+
| id | title | typeId | id | name |
+------+-------+--------+------+------------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 8 | hhh | NULL | NULL | NULL |
| 9 | iii | NULL | NULL | NULL |
| 10 | jjj | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | Javascript |
+------+-------+--------+------+------------+
using子句
我们的ON子句中用到的语法是
table1.column_name1=table2.column_name2
当column_name1=column_name2=column_name时,可以使用using子句简化上述语法
using(column_name)
使用using在select语句中,形成的结果表将会去除using指定的列,这样可以使得表看起来比较简单,可以看下面这个实例
SELECT * FROM t_blog INNER JOIN t_type USING(id);
+----+-------+--------+------------+
| id | title | typeId | name |
+----+-------+--------+------------+
| 1 | aaa | 1 | C++ |
| 2 | bbb | 2 | C |
| 3 | ccc | 3 | Java |
| 4 | ddd | 4 | C# |
| 5 | eee | 4 | Javascript |
+----+-------+--------+------------+
自然连接
即为using子句的简化版,有左自然连接,右自然连接和普通自然连接三个版本,这里不在赘述
NULL值处理
我们看到了,在执行join操作的时候表中有些字段会被置为null,这是我们查表中不希望看到的,这里讨论以下null值处理的相关知识
你也许会问,我们在查询数据库的时候加上where条件指定某些列不为null不就可以了?但是实际上,column_name=NULL和colunm_name!=NULL不起作用!(我也不知道为什么)
例子
select * from table_name where column is not null
这条语句可以在表中选出column不是null的所有记录
mysql提供的null值处理运算符
- IS NULL当前列时NULL,此运算符返回true
- is not null
- <==>比较预算符,比较的两个值都为NULL返回true
正则表达式
用于模糊匹配,下面列出用于正则匹配的模式,需要使用REGEXP操作符
![079636bbee12271b2f20cc659e233830.png](https://i-blog.csdnimg.cn/blog_migrate/632f00550d6646067a6b8ae55d3bc9fd.jpeg)
如果需要查询name字段中以st开头的所有数据
select * from table_name where name REGEXP '^st'