mysql 命令行操作（四）高级sql语句

最新推荐文章于 2024-09-12 09:15:00 发布

kdb_viewer

最新推荐文章于 2024-09-12 09:15:00 发布

阅读量150

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/kdb_viewer/article/details/82699745

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.union，连接两个以上的select语句的结果组合到一个结果集中，多个select语句中相同的数据会被删除，多个union的select语句必须列数相同，比如第一个select选择两个列，那么后续所有union的select语句都必须是两列，不要求列的属性一致，可以用union all保留所有重复的数据。用于测试的两个表数据如下：

mysql> select * from app;
+----+------------+-------------------------+---------+
| 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 * from website
    -> ;
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
|  1 | Google        | https://www.google.com/   |     1 | USA     |
|  2 | 淘宝          | https:/www.taobao.com/    |    13 | CN      |
|  3 | 菜鸟教程      | http://www.runoob.com     |  4689 | CN      |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|  6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |
+----+---------------+---------------------------+-------+---------+
6 rows in set (0.00 sec)

将两个表中列为country的选出来并按照country排序

mysql> select country from website
    -> union
    -> select country from website
    -> order by country;
+---------+
| country |
+---------+
| CN      |
| IND     |
| USA     |
+---------+
3 rows in set (0.00 sec)

使用union all不删除重复的数据

mysql> select country from website
    -> union all
    -> select country from app
    -> order by country;
+---------+
| country |
+---------+
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| IND     |
| USA     |
| USA     |
+---------+
9 rows in set (0.00 sec)

使用union合并使用where的select语句

mysql> select country, name from website where country = 'CN'
    -> union
    -> select country, app_name from app where country = 'CN';
+---------+--------------+
| country | name         |
+---------+--------------+
| CN      | 淘宝         |
| CN      | 菜鸟教程     |
| CN      | 微博         |
| CN      | QQ APP       |
| CN      | 微博 APP     |
| CN      | 淘宝 APP     |
+---------+--------------+
6 rows in set (0.00 sec)

2.排序语句order by：使用select语句可以查询，可以使用order语句将查询的结果排序后返回，可以选择多个字段进行排序，可以使用ASC或者DESC进行升序或者降序，默认是升序，根据alexa字段进行排序的一个例子：

mysql> select * from website order by alexa asc;
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
|  6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |
|  1 | Google        | https://www.google.com/   |     1 | USA     |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|  2 | 淘宝          | https:/www.taobao.com/    |    13 | CN      |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |
|  3 | 菜鸟教程      | http://www.runoob.com     |  4689 | CN      |
+----+---------------+---------------------------+-------+---------+
6 rows in set (0.00 sec)

3.分组语句 group by：根据一个列或者多个对数据集进行分组，在分组的列上可以使用COUNT，SUM，AVG等函数，测试数据表如下：

mysql> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明   | 2016-04-22 15:25:33 |      1 |
|  2 | 小王   | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽   | 2016-04-19 15:26:02 |      2 |
|  4 | 小王   | 2016-04-07 15:26:14 |      4 |
|  5 | 小明   | 2016-04-11 15:26:40 |      4 |
|  6 | 小明   | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

根据name进行分组并统计计数：

mysql> select name, count(*) from employee_tbl group by name;
+--------+----------+
| name   | count(*) |
+--------+----------+
| 小丽   |        1 |
| 小明   |        3 |
| 小王   |        2 |
+--------+----------+
3 rows in set (0.00 sec)

根据name进行分组并计算singin之和：

mysql> select name, sum(singin) from employee_tbl group by name;
+--------+-------------+
| name   | sum(singin) |
+--------+-------------+
| 小丽   |           2 |
| 小明   |           7 |
| 小王   |           7 |
+--------+-------------+
3 rows in set (0.00 sec)

使用with rollup语句对统计后的数据计算总数和，并使用as语句改变输出的属性值：

mysql> select name, sum(singin) as sum from employee_tbl group by name with rollup;
+--------+------+
| name   | sum  |
+--------+------+
| 小丽   |    2 |
| 小明   |    7 |
| 小王   |    7 |
| NULL   |   16 |
+--------+------+
4 rows in set (0.00 sec)

这里总数是NULL，可以指定名字，方法是使用coalesce函数，函数原型coalesce（a, b, c）若a是NULL，选择b，若b是NULL，选择c，如下：

mysql> select coalesce(name, '总数'), sum(singin) as sum_count from employee_tbl group by name with rollup;
+--------------------------+-----------+
| coalesce(name, '总数')   | sum_count |
+--------------------------+-----------+
| 小丽                     |         2 |
| 小明                     |         7 |
| 小王                     |         7 |
| 总数                     |        16 |
+--------------------------+-----------+
4 rows in set (0.01 sec)

3.join语句：以上所有内容都是对于单个表的，union也是先查询单张表然后查询结果，join提供多表联合查询操作，可以在select、delete、update语句中使用。

join有3种：

inner join：内连接，获取两个表中字段匹配的记录

left join：左连接，获取左表所有记录，即使右表没有对应匹配记录

right join：右连接，获取右表所有记录，即使左表没有对应匹配记录

例如对如下两张表，多表联合查询的含义就是，用runoob中的author字段中的值，去tcount表中查runoob_count字段的值

mysql> select * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
|         1 | 学习 PHP      | 菜鸟教程      | 2017-04-12      |
|         2 | 学习 MySQL    | 菜鸟教程      | 2017-04-12      |
|         3 | 学习 Java     | RUNOOB.COM    | 2015-05-01      |
|         4 | 学习 Python   | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C        | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.00 sec)

mysql> select * from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set (0.00 sec)

使用inner join语句效果如下：

mysql> select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a inner join tcount_tbl b
    -> on a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
+-----------+---------------+--------------+

等价于使用如下where语句：

mysql> select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a, tcount_tbl b where a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
+-----------+---------------+--------------+
4 rows in set (0.15 sec)

使用left join，会显示左边全部内容，即使在右表中没有数据，没有数据的属性会显示为NULL，如下

mysql> select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a left join tcount_tbl b
    -> on a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|         5 | FK            |         NULL |
+-----------+---------------+--------------+

使用right join，会显示右表全部内容，在左表中没有内容的属性会显示为NULL

mysql> select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a right join tcount_tbl b
    -> on a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|      NULL | NULL          |           22 |
+-----------+---------------+--------------+

4.NULL值的处理：mysql中的NULL值和编程语言中的NULL值比比较特殊，NULL和任何值比较都返回false，即使是NULL = NULL，因此不能用=和！=对NULL进行比较，mysql提供的专门的运算符解决这个问题，有3种：

IS NULL：当列的值是NULL，返回true

IS NOT NULL：当列的值不为NULL，返回true

<==>：当比较的两个值都是NULL的时候，返回true

例如，对如下数据表进行操作，使用”=“判断是否是NULL不生效

mysql> select * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        |           20 |
| 菜鸟教程      |         NULL |
| Google        |         NULL |
| FK            |           20 |
+---------------+--------------+
4 rows in set (0.00 sec)

mysql> select * from runoob_test_tbl where runoob_count = NULL;
Empty set (0.01 sec)

mysql> select * from runoob_test_tbl where runoob_count is null;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |         NULL |
| Google        |         NULL |
+---------------+--------------+