1 LIKE
SQL LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。
mysql> select * from tb1;
+----+-------------+----------+------------+
| id | title | author | date |
+----+-------------+----------+------------+
| 1 | 学习mysql | lihaogn | 2018-07-13 |
| 2 | 学习java | zhangsan | 2018-06-30 |
+----+-------------+----------+------------+
2 rows in set (0.01 sec)
// 使用like进行模糊查询
mysql> select * from tb1 where author like '%li%';
+----+-------------+---------+------------+
| id | title | author | date |
+----+-------------+---------+------------+
| 1 | 学习mysql | lihaogn | 2018-07-13 |
+----+-------------+---------+------------+
1 row in set (0.00 sec)
2 UNION操作符
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
数据表:
mysql> select * from websites;
+----+----------+---------------------------+---------+-------+
| id | webName | url | country | alexa |
+----+----------+---------------------------+---------+-------+
| 1 | google | https://www.google.cm/ | USA | 1 |
| 2 | 淘宝 | https://www.taobao.com/ | CN | 13 |
| 3 | 微博 | http://weibo.com/ | CN | 20 |
| 4 | Facebook | https://www.facebook.com/ | USA | 3 |
+----+----------+---------------------------+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from apps;
+----+------------+-------------------------+---------+
| id | app_name | url | country |
+----+------------+-------------------------+---------+
| 1 | QQ APP | http://im.qq.com/ | CN |
| 2 | 微博 APP | http://weibo.com/ | CN |
| 3 | 淘宝 APP | https://www.taobao.com/ | CN |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)
操作:
// 不会显示重复的值
mysql> select country from websites
-> union
-> select country from apps
-> order by country;
+---------+
| country |
+---------+
| CN |
| USA |
+---------+
2 rows in set (0.00 sec)
// 加上all,显示全部的值
mysql> select country from websites
-> union all
-> select country from apps
-> order by country;
+---------+
| country |
+---------+
| CN |
| CN |
| CN |
| CN |
| CN |
| USA |
| USA |
+---------+
7 rows in set (0.00 sec)
mysql> select country,webName from websites
-> where country='CN'
-> union all
-> select country,app_name from apps
-> where country='CN'
-> order by country;
+---------+------------+
| country | webName |
+---------+------------+
| CN | 淘宝 |
| CN | 微博 APP |
| CN | 微博 |
| CN | 淘宝 APP |
| CN | QQ APP |
+---------+------------+
5 rows in set (0.00 sec)