5-mySQL-常见查询示例

举个例子，如果我们有一个表 shop，里面保存了某些交易（交易商）的每件商品（物品编号）的价格。假设每个交易者对于每件商品有一个固定的价格，假设（article, dealer）是记录的主键。

我们选择 mysql 中的一个数据库：
这里我们创建一个 test 数据库。

use test;

创建 shop 表并填充。

CREATE TABLE shop(
    -> article INT UNSIGNED DEFAULT '0000' NOT NULL,
    -> dealer CHAR(20)      DEFAULT ''     NOT NULL,
    -> price  DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    -> PRIMARY KEY(article, dealer)

INSERT INTO shop VALUES 
    -> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    -> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

可以查看该表的内容

SELECT * FROM shop ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+

接下来就是正式学习的过程了。

列的最大值

比如我们想要知道最高的商品编号

 SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
|       4 |
+---------+

保持某一列的最大值的行

比如我们想要找到最昂贵商品的数量，经销商和价格。

SELECT article, dealer, price
    -> FROM shop
    -> WHERE price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+

我们也可以使用别的方法来解决

法二：使用 LEFT JOIN 比较大小

SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.price < s2.price
    -> WHERE s2.article IS NULL;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+

法三：使用 LIMIT 子句获取第一行

 SELECT article, dealer, price
    -> FROM shop
    -> ORDER BY price DESC
    -> LIMIT 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+

每组最大列数

为了查找每件商品的最高价格

 SELECT article, MAX(price) AS price
    -> FROM shop
    -> GROUP BY article
    -> ORDER BY article;
+---------+-------+
| article | price |
+---------+-------+
|       1 |  3.99 |
|       2 | 10.99 |
|       3 |  1.69 |
|       4 | 19.95 |
+---------+-------+

保持某一列的分组最大值的行

再来完成一个任务，对于每件商品，找到价格最昂贵的经销商。

SELECT article, dealer, price
    -> FROM shop s1
    -> WHERE price=(SELECT MAX(s2.price)
    -> FROM shop s2
    -> WHERE s1.article = s2.article)
    -> ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+

这个示例展示的是一个相关联的子查询，查询效率不高，所以提供了两种查询方法。

法一：不相关的子查询。

SELECT s1.article, dealer, s1.price
    -> FROM shop s1
    -> JOIN(
    -> SELECT article, MAX(price) AS price
    -> FROM shop
    -> GROUP BY article) AS s2
    -> ON s1.article = s2.article AND s1.price = s2.price
    -> ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+

法二：使用 LEFT JOIN

SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
    -> WHERE s2.article IS NULL
    -> ORDER BY s1.article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+

是根据以下LEFT JOIN原理进行工作：当 s1.price处于最大值时，不 s2.price存在更大的值，因此对应的s2.article值为 NULL。

WITH s1 AS (
    ->    SELECT article, dealer, price,
    ->           RANK() OVER (PARTITION BY article
    ->                            ORDER BY price DESC
    ->                       ) AS `Rank`
    ->      FROM shop
    -> )
    -> SELECT article, dealer, price
    ->   FROM s1
    ->   WHERE `Rank` = 1
    -> ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+

使用用户定义的变量

如果要查找价格最高和最低的商品，可以这样子操作

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
|                   1.25 |                  19.95 |
+------------------------+------------------------+

SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+

搜索两个键

一种比较棘手的情况是结合两个键来搜索两个不同的键 OR
比如：

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

当然也可以通过 UNION 结合使用两个单独的 SELECT 语句的输出来有效解决问题。

每个 SELECT 搜索仅搜索一个键，并可以对其优化：

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

计算每天的访问量

下面看个例子，说明如何使用位组功能来计算用户每月访问网页的天数。
先创建一个表

 CREATE TABLE t1(year YEAR,
    -> month INT UNSIGNED,
    -> day INT UNSIGNED
    -> );

填充元素

 INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);

这个表中包含用户对该页面的访问的年月日值。

如果想要知道这些访问每个月中发生了多少天，可以这样子查询：

 SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
    -> GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |

该查询将计算表中每个年/月组合出现多少天，并自动删除重复的条目。