mysql常用命令/语句学习三

这篇主要用来记录如下操作的一些简单应用.
NULL值操作
模式匹配/正则
union
join

1: NULL值操作
NULL是特殊的值，不能使用普通比较符(例如=、<或!=)來比较，只能使用IS NULL和IS NOT.
例

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
1 row in set (0.00 sec)

mysql> select 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
1 row in set (0.00 sec)

mysql> select 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+---------------+------------+----------------+
1 row in set (0.00 sec)

2: 模式匹配
LIKE模式匹配:
之前有讲到过,可以用LIKE子句代替等号进行模糊匹配.
LIKE 通常与%一同使用，其中%代表任意个数(包括0个)的字元, 也可以和一起使用, 符号代表任意一个字元.
例:
SELECT * FROM pet WHERE name LIKE ‘b%’ ;
SELECT * FROM pet WHERE name LIKE ‘%w%’;
SELECT * FROM pet WHERE name LIKE ‘_‘;(匹配到五个任意字元)

REGEXP模式匹配(正则表达式匹配):
如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有和整個值匹配，模式才匹配)。
userful reference:
https://dev.mysql.com/doc/refman/5.7/en/regexp.html

模式	描述
^	匹配输入字符串开始位置
$	匹配输入字符串的结束位置
.	匹配除了’\n’之外的任何单个字符.如果要匹配’\n’在内的任何字符, 可以使用’[.\n]’
[….]	字符集合,匹配包含的任意一个字符. 例’[abc]’可以匹配”plain”中的’a’
p1\|p2\|p3	匹配 p1 或 p2 或 p3。例，’z\|food’ 能匹配 “z” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。
+	匹配前面的子表达式0次或者多次, 等价于{0,}
{n}	匹配n次, n为非负整数
{n,m}	匹配次数n到m次之间


SELECT * FROM pet WHERE name REGEXP BINARY '^B'; (name以B开头的记录)
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^B';

+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
| Bowser | Diane  | dog     | m    | 1979-08-31 | NULL  |
+--------+--------+---------+------+------------+-------+

SELECT * FROM pet WHERE name REGEXP 'fy$';

SELECT * FROM pet WHERE name REGEXP 'w';

SELECT * FROM pet WHERE name REGEXP '^.....$';

SELECT * FROM pet WHERE name REGEXP '^.{5}$';

useful reference:
http://www.runoob.com/mysql/mysql-regexp.html

3: UNION操作符
UNION操作符用于连接两个或以上的select语句的结果, 组合到一个结果集合当中, 多个select语句会删除重复的数据. UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值.
注意: union两边的columns数要相等,否则会报错.

格式:

select1 union [all|distinct] select2;

例:

mysql> select name from pet where owner = "harold" union select name from petnew where age="24";
+--------+
| name |
+--------+
| Fluffy |
| Buffy |
+--------+
2 rows in set (0.00 sec)

mysql> select name from pet where owner = "harold" union all select name from petnew where age="24";
+--------+
| name |
+--------+
| Fluffy |
| Buffy  |
| Fluffy |
+--------+
3 rows in set (0.00 sec)

mysql> select name from pet where owner = "harold" union select age from petnew where age="24";
+--------+
| name |
+--------+
| Fluffy |
| Buffy  |
| 24 |
+--------+
3 rows in set (0.00 sec)

mysql> select owner from pet where owner = "harold" union select name,age from petnew where age="24";
ERROR 1222 (21000): The used SELECT statements have a different number of columns

4: JOIN连接
实际应用中, 我们可能会从多个数据表中读取数据.
Inner join: 默认状态, 内连接, 获取这些表格中字段匹配关系的记录
left join: 左连接, 获取左表格所有记录, 不管右表格有没有
right join: 右连接, 获取右表格所有记录, 即使左表格没有相应的记录.
看起来很难理解, 举例子就比较直观了:

mysql> select * from fruitnew;
+---------+------+--------+
| name | age | color |
+---------+------+--------+
| apple   |   19 | green  |
| banana  |    3 | yellow |
| orange  |   24 | orange |
| coconut |    8 | white  |
| Bowser | 7 | black |
+---------+------+--------+
5 rows in set (0.00 sec)

mysql> select * from petnew;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2017-07-12 |   24 |
| Claws    | 1994-03-17 | 2017-07-12 |   23 |
| Buffy    | 1989-05-13 | 2017-07-12 |   28 |
| Fang     | 1990-08-27 | 2017-07-12 |   26 |
| Bowser   | 1979-08-31 | 2017-07-12 |   37 |
| Chirpy   | 1997-12-09 | 2017-07-12 |   19 |
| Whistler | 1997-12-09 | 2017-07-12 |   19 |
| Slim     | 1996-04-29 | 2017-07-12 |   21 |
| Puffball | 1999-03-30 | 2017-07-12 | 18 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

内部连接:

mysql> select a.name, a.age, b.name, b.age from petnew a inner join fruitnew b on a.age = b.age;
+----------+------+--------+------+
| name | age | name | age |
+----------+------+--------+------+
| Fluffy   |   24 | orange |   24 |
| Chirpy   |   19 | apple  |   19 |
| Whistler | 19 | apple | 19 |
+----------+------+--------+------+
3 rows in set (0.00 sec)

注意: 这里面的a, b只是为了标记, 只要能对应上, 换成其他的也可以.

mysql> select t2.name, t2.age, t1.name, t1.age from petnew t2 inner join fruitnew t1 on t2.age = t1.age;

这个的输出结果也是一样的. 其中in后面还可以加where语句.

左连接:

mysql> select a.name, a.birth, b.name, b.color from petnew a left join fruitnew b on a.age = b.age;
+----------+------------+--------+--------+
| name | birth | name | color |
+----------+------------+--------+--------+
| Fluffy   | 1993-02-04 | orange | orange |
| Claws    | 1994-03-17 | NULL   | NULL   |
| Buffy    | 1989-05-13 | NULL   | NULL   |
| Fang     | 1990-08-27 | NULL   | NULL   |
| Bowser   | 1979-08-31 | NULL   | NULL   |
| Chirpy   | 1997-12-09 | apple  | green  |
| Whistler | 1997-12-09 | apple  | green  |
| Slim     | 1996-04-29 | NULL   | NULL   |
| Puffball | 1999-03-30 | NULL | NULL |
+----------+------------+--------+--------+
9 rows in set (0.00 sec)

mysql> select * from petnew a left join fruitnew b on a.age = b.age;
+----------+------------+------------+------+--------+------+--------+
| name | birth | CURDATE() | age | name | age | color |
+----------+------------+------------+------+--------+------+--------+
| Fluffy   | 1993-02-04 | 2017-07-12 |   24 | orange |   24 | orange |
| Claws    | 1994-03-17 | 2017-07-12 |   23 | NULL   | NULL | NULL   |
| Buffy    | 1989-05-13 | 2017-07-12 |   28 | NULL   | NULL | NULL   |
| Fang     | 1990-08-27 | 2017-07-12 |   26 | NULL   | NULL | NULL   |
| Bowser   | 1979-08-31 | 2017-07-12 |   37 | NULL   | NULL | NULL   |
| Chirpy   | 1997-12-09 | 2017-07-12 |   19 | apple  |   19 | green  |
| Whistler | 1997-12-09 | 2017-07-12 |   19 | apple  |   19 | green  |
| Slim     | 1996-04-29 | 2017-07-12 |   21 | NULL   | NULL | NULL   |
| Puffball | 1999-03-30 | 2017-07-12 | 18 | NULL | NULL | NULL |
+----------+------------+------------+------+--------+------+--------+
9 rows in set (0.00 sec)

右连接:

mysql> select * from petnew a right join fruitnew b on a.age = b.age;
+----------+------------+------------+------+---------+------+--------+
| name | birth | CURDATE()  | age | name | age | color |
+----------+------------+------------+------+---------+------+--------+
| Chirpy   | 1997-12-09 | 2017-07-12 |   19 | apple |   19 | green |
| Whistler | 1997-12-09 | 2017-07-12 |   19 | apple |   19 | green |
| NULL     | NULL       | NULL       | NULL | banana |    3 | yellow |
| Fluffy   | 1993-02-04 | 2017-07-12 |   24 | orange |   24 | orange |
| NULL     | NULL       | NULL       | NULL | coconut |    8 | white |
| NULL     | NULL       | NULL       | NULL | Bowser  |    7 | black |
+----------+------------+------------+------+---------+------+--------+
6 rows in set (0.00 sec)

更多相关文章

随机推荐