常见查询示例

假设有一个表格，是商店为某些交易员(经销商)保存每种商品(项目号)的价格。假设每个交易者的每件商品都有一个固定的价格，那么(商品，交易者)就是记录的主键

mysql> create database shop;
Query OK, 1 row affected (0.00 sec)

mysql> use shop;
Database changed

mysql> create table shop_tab(
    -> article int(4) unsigned zerofill default '0000' not null,
    -> dealer char(20) default '' not null,   
    -> price double(16,2) default '0.00' not null,
    -> primary key(article,dealer));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into shop_tab 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);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from shop_tab order by article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)

1.列的最大值

mysql> select max(price) from shop_tab;
+------------+
| max(price) |
+------------+
|      19.95 |
+------------+
1 row in set (0.00 sec)

2.包含某一列的最大值的行

使用子查询完成此操作

mysql> select * from shop_tab 
    -> where price=(select max(price) from shop_tab);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.01 sec)

按价格降序排序所有行，并使用特定于MySQL的LIMIT子句获取第一行

mysql> select * from shop_tab
    -> order by price desc
    -> limit 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

3.每种商品的最高价格

mysql> select article,max(price) as price                                             
    -> from shop_tab
    -> group by article
    -> order by article;
+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+
4 rows in set (0.00 sec)

4.对于每种商品，找到价格最贵的经销商

1解：以price为条件，使用where s1.article = s2.article或者any操作符使主查询一条记录
    对应子查询的一条记录。
    ANY 关键字必须接在一个比较操作符的后面，表示与子查询返回的任何值比较为 TRUE 则返回 TRUE
    主查询的一条记录对应子查询多条记录会产生错误
    (ERROR 1242 (21000): Subquery returns more than 1 row)。
mysql> select article,dealer,price
    -> from shop_tab as s1
    -> where price=(select max(s2.price)
    ->             from shop_tab as s2
    ->             where s1.article = s2.article
    ->             order by article
    -> );
    或者
    mysql> select article,dealer,price
    -> from shop_tab as s1
    -> where price= any(select max(s2.price)
    ->             from shop_tab as s2
    ->             order by article
    -> );
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

2解：筛选出每件商品最贵的价格作为s2，与原表S1内连接，显示两边表同时有对应的数据，
任何一边缺失数据就不显示
mysql> select s1.article,s1.dealer,s1.price 
    -> from shop_tab as s1 join(
    ->                         select article,max(price) as price 
    ->                         from shop_tab group by article) as s2 
    -> where s1.article = s2.article and s1.price = s2.price 
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

3解：s2左连接s1，通过s1.article = s2.article 两张表进行价格比较
mysql> select * from shop_tab as s1 
    -> left join shop_tab as s2 
    -> on s1.article = s2.article and s1.price < s2.price;
+---------+--------+-------+---------+--------+-------+
| article | dealer | price | article | dealer | price |
+---------+--------+-------+---------+--------+-------+
|    0001 | A      |  3.45 |    0001 | B      |  3.99 |
|    0001 | B      |  3.99 |    NULL | NULL   |  NULL |
|    0002 | A      | 10.99 |    NULL | NULL   |  NULL |
|    0003 | B      |  1.45 |    0003 | C      |  1.69 |
|    0003 | C      |  1.69 |    NULL | NULL   |  NULL |
|    0003 | D      |  1.25 |    0003 | B      |  1.45 |
|    0003 | D      |  1.25 |    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |    NULL | NULL   |  NULL |
+---------+--------+-------+---------+--------+-------+
8 rows in set (0.00 sec)
然后再选取s2中为NULL值的行
mysql> select s1.article,s1.dealer,s1.price 
    -> from shop_tab as s1 
    -> left join shop_tab as s2 
    -> on s1.article = s2.article AND s1.price < s2.price 
    -> where s2.article is null;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

5.用户自定义变量

mysql> select @min_price:=min(price),@max_price:=max(price)
    -> from shop_tab;
+------------------------+------------------------+
| @min_price:=min(price) | @max_price:=max(price) |
+------------------------+------------------------+
|                   1.25 |                  19.95 |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> select * from shop_tab
    -> where price = @min_price:=min(price)
    -> \c                                  
mysql> select * from shop_tab                                      
    -> where price = @min_price or price = @max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
2 rows in set (0.00 sec)

6.使用外键

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


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=latin1

7.使用两个键搜索

逻辑运算符or 使用一个主键能够被很好的优化，就像处理and一样
但是使用or去搜索两个不同的主键比较棘手

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

8.计算每日访问量

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

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
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 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

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

9.使用AUTO_INCREMENT

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

mysql> CREATE TABLE animals (
    -> id MEDIUMINT NOT NULL AUTO_INCREMENT,
    -> name CHAR(30) NOT NULL,
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> insert into animals(name) values ('dog'),('cat'),('penguin'),('lax'),('bird');
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | bird    |
+----+---------+
5 rows in set (0.00 sec)

AUTO_INCREMENT列没有指定值，因此MySQL自动分配序列号。也可以显式地为列分配0来生成序列号，除非启用了NO_AUTO_VALUE_ON_ZERO SQL模式（NO_AUTO_VALUE_ON_ZERO影响AUTO_INCREMENT列的处理。一般情况下可以向该列插入NULL或0生成下一个序列号）。例如

mysql> INSERT INTO animals (id,name) VALUES(0,'groundhog');
Query OK, 1 row affected (0.02 sec)

mysql> select * from animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | lax       |
|  5 | bird      |
|  6 | groundhog |
+----+-----------+
6 rows in set (0.01 sec)

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

mysql> INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
Query OK, 1 row affected (0.04 sec)

mysql> select * from animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | lax       |
|  5 | bird      |
|  6 | groundhog |
|  7 | squirrel  |
+----+-----------+
7 rows in set (0.00 sec)

将任何其他值插入AUTO_INCREMENT列时，列被设置为该值，序列被重置，以便下一个自动生成的值按照最大列值的顺序生成。例如:

mysql>INSERT INTO animals (id,name) VALUES(100,'rabbit');
Query OK, 1 row affected (0.04 sec)
mysql>INSERT INTO animals (id,name) VALUES(NULL,'mouse');
Query OK, 1 row affected (0.04 sec)
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使用LAST_INSERT_ID() SQL函数或mysql_insert_id() C API函数检索最新自动生成的值。这些函数是特定于连接的，因此它们的返回值不受另一个执行插入的连接的影响。

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

注意

对于多行插入，LAST_INSERT_ID()和mysql_insert_id()实际上从插入的第一行返回AUTO_INCREMENT键。

要从除1之外的AUTO_INCREMENT值开始，使用CREATE TABLE或ALTER TABLE设置该值，如下所示:

1
2
3

mysql>  ALTER TABLE animals AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0