MySQL資料檢索+查詢+全文本搜尋

標籤：

【0】README 0.1）本文部分文字描述轉自“MySQL 必知必會”，旨在review “MySQL資料檢索+查詢+全文本搜尋” 的基礎知識；
【1】使用子查詢 1）查詢定義：任何sql 語句都是查詢。但此術語一般指 select語句；SQL 還允許建立子查詢，即嵌套在其他查詢中的查詢； 2）利用子查詢進行過濾（where子句，in子句）

2.1）可以把一條select語句返回的結果用於另一條select語句的where子句；
3）作為計算欄位使用子查詢 3.1）使用子查詢的另一種方式是建立計算欄位：如需要顯式供應商的訂單總數和供應商的id和name；

Attention）顯然， select id,name,(select count(*) from product p where p.vendor=v.id group by vendor) as my_count from vendor v;與其他的SQL不同，因為 where p.vendor=v.id group by vendor) as my_count from vendor v 子句 使用了完全限定名比較 內部表和外部表格的id是否相等；
【2】連接表 1）主鍵+外鍵： 1.1）主鍵：能夠唯一標識每一行資料； 1.2）外鍵：外鍵為某個表中的一列，它包含另一個表的主鍵值，定義了兩個表之間的關係； 2）建立連接：
3）笛卡爾積：由沒有連接條件的表關係返回的結果為笛卡爾積。檢索出的行的數目是第一個表中的行數乘以第二個表中的行數；
Attention）不要忘記where子句：應該保證所有連接都有where 子句，否則MySQL 將返回比想要的資料多得多的資料；
4）內部連接（=等值連接） 4.1）定義：目前為止使用的連接稱為等值連接，它基於兩個表之間的相等測試。這種連接也稱為內部連接；

select v.id,v.name from product p, vendor v where p.vendor = v.id;select v.id,v.name from product p inner join vendor v on p.vendor = v.id;

5）連接多個表
5.1）我們需要找出購買wang供應商的生產商品的客戶id；

select customer_id from t_order o,product p,vendor v where o.prod_id=p.id and p.vendor = v.id and v.name='wang';

【3】建立進階連接  for spec info，please visit  MySQL的自然連接+外部連接（左外串連，右外串連）+內部連接

【4】組合查詢 1）MySQL允許執行多個查詢（多條select），並將結果作為單個查詢結果集返回。這些組合查詢通常稱為並（union）或複合查詢； 2）有兩種case，需要使用組合查詢；

case1）在單個查詢中從不同的表返回類似結構的資料； case2）對單個表執行多個查詢，按單個查詢返回資料；

3）建立組合查詢：可用union 操作符來組合數條SQL 查詢；利用union， 可給出多條select 語句，將它們的結果組成成單個結果集； 4）使用union
5）使用union規則（rules）

r1）union必須由兩條或兩條以上的select 語句組成，語句之間用關鍵字 union分割； r2）union 中的每個查詢必須包含系統的列，運算式或聚集合函式（不過各個列不需要以相同的次序給出）； r3）列數量類型必須相容：類型不必完全相同，但必須是 DBMS可以隱含地轉換的類型；

6）包含或取消重複的行 6.1）problem+solution：

problem）第一條select語句返回4行，第二條select語句返回3行，而在用union組合兩條select語句後，只返回了6行而不是7行；原因是 union從查詢結果集中自動去除了重複的行（在使用union時，重複的行被自動取消）； solution）如果想要返回所有匹配行，可以使用 union all 而不是 union；

7）對組合查詢結果排序：在使用union 組合查詢時，只能使用一條 order by 語句，它必須出現在最後一條select 語句之後。
Attention）使用union時，也可以使用不同的表進行組合； 【5】全文本搜尋 1）只有 myisam 引擎支援全文本搜尋； 2）為了進行全文本搜尋，必須索引被搜尋的列，而且要隨著資料的改變不斷地重新索引；

2.1）啟用全文本搜尋支援：一般在建立表時啟用全文本搜尋；create table 語句接收fulltext 子句，它給出被索引列的一個逗號分隔的列表；

看個荔枝）通過create示範fulltext子句的使用：

對以上代碼的分析（Analysis）：

A1）MySQL根據子句fulltext(note_text)的指示對它進行索引。這裡的fulltext索引單個列，如果需要也可以索引多個列； A2）在定義之後，MySQL自動維護該索引。在增加、更新或刪除行時，索引隨之自動更新；

Attention）不要在匯入資料時使用fulltext： 更新索引要花時間，雖然不是很多，但畢竟要花時間。如果正在匯入資料到一個新表，此時不應該啟用fulltext索引。應該首先匯入所有資料，然後再修改表，定義 fulltext，這樣有助於更快地匯入資料；

2.2）進行全文本搜尋：使用match()函數和against()函數質心全文本搜尋，match()函數指定被搜尋的列，而against()指定要使用的搜尋運算式； Attention）以下荔枝轉自：http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#fulltext-search
2.3）全文檢索搜尋帶查詢擴充

全文檢索搜尋支援查詢擴充功能 (特別是其多變的“盲查詢擴充功能” )。若搜尋詞句的長度過短, 那麼使用者則需要依靠全文檢索搜尋引擎通常缺乏的內隱知識進行查詢。這時，查詢擴充功能通常很有用。例如, 某位搜尋 “database” 一詞的使用者，可能認為“MySQL”、“Oracle”、“DB2” and “RDBMS”均為符合 “databases”的項，因此都應被返回。這既為內隱知識。

在下列搜尋詞句後添加WITH QUERY EXPANSION，啟用盲查詢擴充功能(即通常所說的自動相關性反饋)。它將執行兩次搜尋，其中第二次搜尋的搜尋詞句是同第一次搜尋時找到的少數頂層檔案串連的原始搜尋詞句。這樣,假如這些檔案中的一個 含有單詞 “databases” 以及單詞 “MySQL”, 則第二次搜尋會尋找含有單詞“MySQL” 的檔案，即使這些檔案不包含單詞 “database”。下面的例子顯示了這個不同之處：

2.4）布爾全文索引：利用IN BOOLEAN MODE修改程式， MySQL 也可以執行布爾全文檢索搜尋，提供關於如下內容的細節（Details）

D1）要匹配的詞；

D2）要排斥的詞；

D3）排列提示（某些詞比其他詞更重要，更重要的詞等級更高）；

D4）運算式分組；

關於全文本搜尋的使用說明（specification）

s1）在索引全文本資料時，短詞被忽略且從索引中排除。短詞定義為那些具有3個或3個以下字元的詞（如果需要，這個數字3可以修改）； s2）MySQL 帶有一個內建的非用詞列表，這些詞在索引全文本資料時總是被忽略。如果需要，可以覆蓋這個列表； s3）許多詞出現的頻率很高，搜尋它們沒有用處，因此，MySQL規定了一條50%規則，如果一個詞出現在 50% 以上的行中， 則將它作為一個非用詞忽略，50%規則不用於 in boolean mode 中； s4）如果表中的行數少於3行，則全文本搜尋不返回結果（因為每個次或者不出現，或者至少出現在50%的行中）； s5）忽略詞中的單引號，如，don‘t == dont； s6）不具有詞分隔字元的語言（包括日文和中文）不能恰當地返回全文本搜尋結果； s7）僅在 MyISAM資料庫引擎中支援全文本搜尋；

MySQL資料檢索+查詢+全文本搜尋