MySQL中文參考手冊-- 常用查詢的例子

　　下面是一些學習如何用MySQL解決一些常見問題的例子
　　
　　一些例子使用數據庫表shop包含某個商人的每篇文章(物品號)的價格假定每個商人的每篇文章有一個單獨的固定價格那麼(物品商人)是記錄的主鍵
　　
　　你能這樣創建例子數據庫表
　　CREATE TABLE shop (
　　 article INT() UNSIGNED ZEROFILL DEFAULT NOT NULL
　　 dealer　CHAR()　　　　　　　　 DEFAULT 　　 NOT NULL
　　 price　 DOUBLE()　　　　　　 DEFAULT NOT NULL
　　 PRIMARY KEY(article dealer));
　　
　　INSERT INTO shop VALUES
　　(A)(B)(A)(B)(C)
　　(D)(D);
　　
　　好了例子數據是這樣的
　　
　　SELECT * FROM shop
　　
　　++++
　　| article | dealer | price |
　　++++
　　|　　 | A　　　|　 |
　　|　　 | B　　　|　 |
　　|　　 | A　　　| |
　　|　　 | B　　　|　 |
　　|　　 | C　　　|　 |
　　|　　 | D　　　|　 |
　　|　　 | D　　　| |
　　++++
　　
　　 列的最大值
　　最大的物品號是什麼？
　　
　　SELECT MAX(article) AS article FROM shop
　　
　　++
　　| article |
　　++
　　|　　　 |
　　++
　　
　　 擁有某個列的最大值的行
　　找出最貴的文章的編號商人和價格
　　
　　在ANSISQL中這很容易用一個子查詢做到
　　
　　SELECT article dealer price
　　FROM　 shop
　　WHERE　price=(SELECT MAX(price) FROM shop)
　　
　　在MySQL中（還沒有子查詢)就用步做到
　　
　　用一個SELECT語句從表中得到最大值
　　使用該值編出實際的查詢
　　SELECT article dealer price
　　FROM　 shop
　　WHERE　price=
　　
　　另一個解決方案是按價格降序排序所有行並用MySQL特定LIMIT子句只得到的第一行
　　
　　SELECT article dealer price
　　FROM　 shop
　　ORDER BY price DESC
　　LIMIT
　　
　　注意:如果有多個最貴的文章( 例如每個)LIMIT解決方案僅僅顯示他們之一！
　　
　　 列的最大值按組只有值
　　每篇文章的最高的價格是什麼？
　　
　　SELECT article MAX(price) AS price
　　FROM　 shop
　　GROUP BY article
　　
　　+++
　　| article | price |
　　+++
　　|　　 |　 |
　　|　　 | |
　　|　　 |　 |
　　|　　 | |
　　+++
　　
　　 擁有某個字段的組間最大值的行
　　對每篇文章找出有最貴的價格的交易者
　　
　　在ANSI SQL中我可以用這樣一個子查詢做到
　　
　　SELECT article dealer price
　　FROM　 shop s
　　WHERE　price=(SELECT MAX(sprice)
　　　　　　　　　FROM shop s
　　　　　　　　　WHERE sarticle = sarticle)
　　
　　在MySQL中最好是分幾步做到
　　
　　得到一個表(文章maxprice)見 擁有某個域的組間最大值的行
　　對每篇文章得到對應於存儲最大價格的行
　　這可以很容易用一個臨時表做到
　　
　　CREATE TEMPORARY TABLE tmp (
　　　　　　article INT() UNSIGNED ZEROFILL DEFAULT NOT NULL
　　　　　　price　 DOUBLE()　　　　　　 DEFAULT NOT NULL);
　　
　　LOCK TABLES article read;
　　
　　INSERT INTO tmp SELECT article MAX(price) FROM shop GROUP BY article;
　　
　　SELECT article dealer price FROM shop tmp
　　WHERE shoparticle=tmparticel AND shopprice=tmpprice;
　　
　　UNLOCK TABLES;
　　
　　DROP TABLE tmp;
　　
　　如果你不使用一個TEMPORARY表你也必須鎖定tmp表
　　
　　它能一個單個查詢做到嗎？
　　
　　是的但是只有使用我稱之為MAXCONCAT詭計的一個相當低效的詭計
　　
　　SELECT article
　　　　　 SUBSTRING( MAX( CONCAT(LPAD(price)dealer) ) ) AS dealer
　　　+LEFT(　　　MAX( CONCAT(LPAD(price)dealer) ) ) AS price
　　FROM　 shop
　　GROUP BY article;
　　
　　++++
　　| article | dealer | price |
　　++++
　　|　　 | B　　　|　 |
　　|　　 | A　　　| |
　　|　　 | C　　　|　 |
　　|　　 | D　　　| |
　　++++
　　
　　最後例子當然能通過在客戶程序中分割連結的列使它更有效一點
　　
　　 使用外鍵
　　不需要外鍵聯結個表
　　
　　MySQL唯一不做的事情是CHECK以保證你使用的鍵確實在你正在引用表中存在並且它不自動從有一個外鍵定義的表中刪除行如果你象平常那樣使用你的鍵值它將工作得很好！
　　
　　CREATE TABLE persons (
　　　　id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
　　　　name CHAR() NOT NULL
　　　　PRIMARY KEY (id)
　　);
　　
　　CREATE TABLE shirts (
　　　　id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
　　　　style ENUM(tshirt polo dress) NOT NULL
　　　　color ENUM(red blue orange white black) NOT NULL
　　　　owner SMALLINT UNSIGNED NOT NULL REFERENCES persons
　　　　PRIMARY KEY (id)
　　);
　　
　　INSERT INTO persons VALUES (NULL Antonio Paz);
　　
　　INSERT INTO shirts VALUES
　　(NULL polo blue LAST_INSERT_ID())
　　(NULL dress white LAST_INSERT_ID())
　　(NULL tshirt blue LAST_INSERT_ID());
　　
　　INSERT INTO persons VALUES (NULL Lilliana Angelovska);
　　
　　INSERT INTO shirts VALUES
　　(NULL dress orange LAST_INSERT_ID())
　　(NULL polo red LAST_INSERT_ID())
　　(NULL dress blue LAST_INSERT_ID())
　　(NULL tshirt white LAST_INSERT_ID());
　　
　　SELECT * FROM persons;
　　+++
　　| id | name　　　　　　　　|
　　+++
　　|　 | Antonio Paz　　　　 |
　　|　 | Lilliana Angelovska |
　　+++
　　
　　SELECT * FROM shirts;
　　+++++
　　| id | style　 | color　| owner |
　　+++++
　　|　 | polo　　| blue　 |　　 |
　　|　 | dress　 | white　|　　 |
　　|　 | tshirt | blue　 |　　 |
　　|　 | dress　 | orange |　　 |
　　|　 | polo　　| red　　|　　 |
　　|　 | dress　 | blue　 |　　 |
　　|　 | tshirt | white　|　　 |
　　+++++
　　
　　SELECT s* FROM persons p shirts s
　　 WHERE pname LIKE Lilliana%
　　　 AND sowner = pid
　　　 AND lor <> white;
　　
　　+++++
　　| id | style | color　| owner |
　　+++++
　　|　 | dress | orange |　　 |
　　|　 | polo　| red　　|　　 |
　　|　 | dress | blue　 |　　 |
　　+++++
　　

From:http://tw.wingwit.com/Article/program/MySQL/201311/29382.html