MySQL学习——查询语句

1 MAX()函数

要查询表中最高的项目编号（即列的最大值），您可以使用SQL的MAX()函数。

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

要找到最贵商品的编号、交易商和价格，可以使用一个子查询来首先确定最高的价格，然后在主查询中找出与该价格相对应的商品信息。以下是一个示例SQL查询，展示了如何完成这个任务：

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

找到每个子查询中的最高价格。

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article
ORDER BY article;

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

2  LEFT JOIN

使用 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;

3 DESC（降序）

通过对所有行按价格降序排序，并使用MySQL特有的LIMIT子句仅获取第一行，可以找到价格最高的商品。以下是这个方法的SQL查询示例：

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

4 找到每个组中具有某一列最大值的行

（1）用MAX()函数实现

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 |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

（2）用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;

使用LEFT JOIN找到每种商品最贵价格的交易商的示例：

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;

5 自定义变量查询

以下是一个如何使用用户定义的变量来找到价格最高和最低的商品的示例：

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

6 SQL查询验证外键操作

使用以下 SQL 语句创建父表和子表：

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;


CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
) ENGINE=INNODB;

要向父表中插入一行，

mysql> INSERT INTO parent (id) VALUES ROW(1);

要验证数据是否已成功插入，可以通过选择父表中的所有行来进行确认。使用以下SQL查询可以实现这一点：

mysql> TABLE parent;
+----+
| id |
+----+
|  1 |
+----+

7 使用两个关键字进行查询

（1）OR

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

（2）UNION

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';

8 位组函数

以下示例显示了如何使用位组函数来计算用户每月访问网页的天数。

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 |
+------+-------+------+

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