举个例子,如果我们有一个表 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 |
该查询将计算表中每个年/月组合出现多少天,并自动删除重复的条目。