mysql常用命令/语句学习三

来源：互联网发布：unity3d虚拟仿真编辑：程序博客网时间：2024/06/11 00:44

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

阅读全文

0 0