- 论坛徽章:
- 0
|
3.6.1. 列的最大值
“最大的物品号是什么?”
SELECT MAX(article) AS article FROM shop; +---------+ | article | +---------+ | 4 | +---------+ 3.6.2. 拥有某个列的最大值的行
任务:找出最贵物品的编号、销售商和价格。
这很容易用一个子查询做到:
SELECT article, dealer, priceFROM shopWHERE price=(SELECT MAX(price) FROM shop);另一个解决方案是按价格降序排序所有行并用MySQL特定LIMIT子句只得到第一行:
SELECT article, dealer, priceFROM shopORDER BY price DESCLIMIT 1;注:如果有多项最贵的物品( 例如每个的价格为19.95),LIMIT解决方案仅仅显示其中一个!
3.6.3. 列的最大值:按组
任务:每项物品的的最高价格是多少?
SELECT article, MAX(price) AS price FROM shop GROUP BY article +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+ 3.6.4. 拥有某个字段的组间最大值的行
任务:对每项物品,找出最贵价格的物品的经销商。
可以用这样一个子查询解决该问题:
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article); 3.6.5. 使用用户变量
你可以清空MySQL用户变量以记录结果,不必将它们保存到客户端的临时变量中。(参见 9.3节,“用户变量”.)。
例如,要找出价格最高或最低的物品的,其方法是:
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 | +---------+--------+-------+ 3.6.6. 使用外键
在MySQL中,InnoDB表支持对外部关键字约束条件的检查。参见15.2节,“InnoDB存储引擎”。还可以参见 1.8.5.5节,“外键”。
只是联接两个表时,不需要外部关键字。对于除InnoDB类型的表,当使用REFERENCES tbl_name(col_name)子句定义列时可以使用外部关键字,该子句没有实际的效果,只作为备忘录或注释来提醒,你目前正定义的列指向另一个表中的一个列。执行该语句时,实现下面很重要:
· MySQL不执行表tbl_name 中的动作,例如作为你正定义的表中的行的动作的响应而删除行;换句话说,该句法不会致使ON DELETE或ON UPDATE行为(如果你在REFERENCES子句中写入ON DELETE或ON UPDATE子句,将被忽略)。
· 该句法可以创建一个column;但不创建任何索引或关键字。
· 如果用该句法定义InnoDB表,将会导致错误。
你可以使用作为联接列创建的列,如下所示:
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, shirt s WHERE p.name LIKE 'Lilliana%' AND s.owner = p.id 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: shirtCreate 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在列定义中,按这种方式使用REFERENCES作为注释或“提示”适用于表MyISAM和BerkeleyDB。
3.6.7. 根据两个键搜索
可以充分利用使用单关键字的OR子句,如同AND的处理。
一个比较灵活的例子是寻找两个通过OR组合到一起的关键字:
SELECT field1_index, field2_index FROM test_tableWHERE field1_index = '1' OR field2_index = '1'该情形是已经优化过的。参见7.2.6节,“索引合并优化”。
还可以使用UNION将两个单独的SELECT语句的输出合成到一起来更有效地解决该问题。参见13.2.7.2节,“UNION语法
”。
每个SELECT只搜索一个关键字,可以进行优化:
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'UNIONSELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; |
|