MySQL™ 参考手册（常见查询的示例）

头像
博弈
    阅读 9 分钟
    1

    常见查询的示例

    以下是如何解决MySQL的一些常见问题的示例。

    一些示例使用shop表为某些商人（经销商）保留每件商品的价格（商品编号），假设每个商人的每件商品只有一个固定价格，那么（article、dealer）是记录的主键。

    启动命令行工具mysql并选择一个数据库：

    shell> mysql your-database-name

    要创建和填充示例表，请使用以下语句：

    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 |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

    其他解决方案是使用LEFT JOIN或按价格降序排序所有行，并使用特定于MySQL的LIMIT子句获取第一行：

    SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
    如果有几件最贵的商品，每一件的价格都是19.95，LIMIT只会显示其中的一件。

    每组最大列数

    任务：找到每件商品的最高价格。

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

    包含某一列的组最大值的行

    任务：针对每一件商品，找出价格最高的经销商。

    这个问题可以通过像这样的子查询来解决：

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

    前面的示例使用相关子查询，这可能是低效的，解决此问题的其他可能性是在FROM子句中使用不相关的子查询、LEFT JOIN或带有视窗函数的公共表表达式。

    不相关的子查询：

    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;

    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的工作原理是，当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;

    使用用户定义的变量

    你可以使用MySQL用户变量来记住结果，而无需将它们存储在客户端的临时变量中。

    例如，要查找价格最高和最低的商品，你可以执行以下操作：

    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 |
+---------+--------+-------+
    也可以在用户变量中存储数据库对象（如表或列）的名称，然后在SQL语句中使用此变量，但是，这需要使用准备好的语句。

    使用外键

    在MySQL中，InnoDB表支持检查外键约束。

    仅仅为了连接两个表，不需要外键约束，对于InnoDB以外的存储引擎，定义列时可以使用REFERENCES tbl_name(col_name)子句，这没有实际效果，仅作为备忘录或注释，当前定义的列旨在引用另一个表中的列，在使用这种语法时要意识到：

    • MySQL不执行任何类型的检查以确保col_name实际存在于tbl_name中（甚至tbl_name本身也存在）。
    • MySQL不对tbl_name执行任何类型的操作，例如删除行以响应对你定义的表中的行所采取的操作，换句话说，这种语法无论如何都不会引发ON DELETE或ON UPDATE行为（虽然你可以在REFERENCES子句中编写ON DELETE或ON UPDATE子句，但也会将其忽略）。
    • 此语法创建一个列，它不会创建任何类型的索引或键。

    你可以使用如此创建的列作为连接列，如下所示：

    CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p INNER JOIN shirt s
   ON s.owner = p.id
 WHERE p.name LIKE 'Lilliana%'
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

    以这种方式使用时，REFERENCES子句不会显示在SHOW CREATE TABLE或DESCRIBE的输出中：

    SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

    以这种方式使用REFERENCES作为列定义中的注释或“提醒”与MyISAM表一起使用。

    在两个键上搜索

    使用单个键的OR得到了很好的优化，AND的处理也是如此。

    一个棘手的情况是结合OR在两个不同的键上搜索：

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

    这种情况已经过优化。

    你还可以使用组合了两个单独SELECT语句的输出的UNION来有效地解决这个问题。

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

    计算每日访问量

    下面的示例展示了如何使用bit组函数计算用户每月访问Web页面的天数。

    CREATE TABLE t1 (year YEAR(4), 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 |
+------+-------+------+

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

    使用AUTO_INCREMENT

    AUTO_INCREMENT属性可用于为新行生成唯一标识：

    CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

    返回：

    +----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

    没有为AUTO_INCREMENT列指定值，因此MySQL会自动分配序列号，除非启用NO_AUTO_VALUE_ON_ZERO SQL模式，否则你还可以向列显式指定0以生成序列号，例如：

    INSERT INTO animals (id,name) VALUES(0,'groundhog');

    如果列声明为NOT NULL，则还可以为列分配NULL以生成序列号，例如：

    INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

    当你将任何其他值插入AUTO_INCREMENT列时，该列将设置为该值并重置序列，以便下一个自动生成的值按顺序从最大列值开始，例如：

    INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

    更新现有的AUTO_INCREMENT列值也会重置AUTO_INCREMENT序列。

    你可以使用LAST_INSERT_ID() SQL函数或mysql_insert_id() C API函数检索最新自动生成的AUTO_INCREMENT值，这些函数是特定于连接的，因此它们的返回值不受另一个也执行插入的连接的影响。

    对AUTO_INCREMENT列使用足够大以容纳你将需要的最大序列值的最小整数数据类型，当列达到数据类型的上限时，下一次生成序列号的尝试将失败，如果可能，请使用UNSIGNED属性以允许更大的范围。例如，如果使用TINYINT，则允许的最大序列号为127，对于TINYINT UNSIGNED，最大值为255。

    对于多行插入，LAST_INSERT_ID()和mysql_insert_id()实际上从第一个插入的行返回AUTO_INCREMENT键，这使得多行插入可以在复制设置中的其他服务器上正确复制。

    要以除1以外的AUTO_INCREMENT值开始，请使用CREATE TABLE或ALTER TABLE设置该值，如下所示：

    mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

    MyISAM说明

    • 对于MyISAM表，可以在多列索引的辅助列上指定AUTO_INCREMENT，在这种情况下，AUTO_INCREMENT列的生成值计算为MAX(auto_increment_column) + 1 WHERE prefix = given-prefix，当你想要将数据放入有序组时，这非常有用。

      CREATE TABLE animals (
   grp ENUM('fish','mammal','bird') NOT NULL,
   id MEDIUMINT NOT NULL AUTO_INCREMENT,
   name CHAR(30) NOT NULL,
   PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
   ('mammal','dog'),('mammal','cat'),
   ('bird','penguin'),('fish','lax'),('mammal','whale'),
   ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

      返回：

      +--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

      在这种情况下（当AUTO_INCREMENT列是多列索引的一部分时），如果删除任何组中具有最大AUTO_INCREMENT值的行，则重用AUTO_INCREMENT值，即使对于MyISAM表也会发生这种情况，因为通常不会重复使用AUTO_INCREMENT值。

    • 如果AUTO_INCREMENT列是多个索引的一部分，MySQL使用以AUTO_INCREMENT列开头的索引生成序列值（如果有）。例如，如果animals表包含索引PRIMARY KEY (grp, id)和INDEX (id)，MySQL将忽略PRIMARY KEY以生成序列值，因此，该表将包含单个序列，而不是每个grp值的序列。
    上一篇：在批处理模式下使用mysql
    下一篇：在Apache中使用MySQL
    mysql
    博弈
    2.5k 声望1.5k 粉丝

    态度决定一切

    « 上一篇
    MySQL™ 参考手册（在Apache中使用MySQL）
    下一篇 »
    MySQL™ 参考手册（MySQL程序概述）

    引用和评论

    推荐阅读
    头像
    Activiti 用户指南（网关）

    博弈阅读 3.5k

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 1.9k

    头像
    Mysql数据库sql优化案例

    杜若赞 5阅读 2.1k

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 302

    头像
    高效工程师的七个习惯

    Bytebase赞 3阅读 590

    头像
    sqlmap注入语句学习，帮助你排查SQL注入漏洞

    TANKING赞 2阅读 522

    头像
    业务系统大事务导致mysql死锁问题的排查与解决

    我不是码农阅读 1.4k

    0 条评论
    得票最新