mysql常用命令/语句学习三

时间:2022-04-27 07:57:35

这篇主要用来记录如下操作的一些简单应用.
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)