1小时钟回顾MySQL语法(中)
六.聚集函數
?
使用聚集函數,可以方便的分析數據,主要有以下幾種應用場景:
1.確定表的行數 (統計)
2.對某一列值進行求和
3.找出表的列 最大值,最小值 或平均值
特點: 使用聚集函數,返回的結果 是單行單列
用處:一般用于子查詢 或? 與分組搭配使用.? 更多的是搭配分組使用
?
以下函數 distinct 為可選值?
6.1 AVG([DISTINCT] expr)? ? ?#求某一列平均值 會自動去除 內容為null 的列
例如:select avg(age) from student # 求 學生的平均年齡?
?
6.2 COUNT([DISTINCT] expr)? #統計某一列出現的行數?會自動去除?內容為null 的列
例如: select * from student? ?# 統計共有多少條學生記錄
?
6.3 MAX([DISTINCT] expr)? #求出某一列的最大值??
例如: select max(age) from student? ?# 計算學生中最大的年齡是
?
6.4 MIN([DISTINCT] expr)? #求出某一列的最小值
例如: select min(age) from student? ?# 計算學生中最小的年齡是
?
max 與 min 通常用來查找 數值或日期的最 大/小 值
?
6.5 SUM([DISTINCT] expr)? # 對某一列的所有值進行求和
例如: select sum(age) from student? ?# 統計學生年齡的總和
?
?
六-2.數據分組
?
一般聚集函數都是與分組搭配使用,分組是按照某一特點 把表中的數據分成多個組,分組后分別再進行聚集操作
?
6.1 關鍵字 : group by cloum
select sex,count(*) as 總人數? from? student group by sex #統計不同性別的人數
特點:先分組 再聚集
需注意:
1.雖然沒有明確要求, 但是一般 用于分組的列 , 都要在 select 語句中查詢出
2.關鍵字優先級: group by? 必需在 where 語句之后 , order by 之前
3.group by 后不可跟聚集函數, 不可跟別名
4.如果分組中存在null值, 會將null單獨作為一個分組. 如果有多個null ,可將多個null作為一個分組
?
6.2 過濾分組數據 HAVING?
where 與 having 的區別
where 是對原始數據的每行數據進行過濾, 不存在分組的概念,
having 是對分組后的每組數的每一行進行的過濾
?
HAVING支持所有的 where子句中的操作符,語法完全一致 .
舉個例子 :從學生表中分別統計男生,女生中 ,年齡在20歲以上的 人數,并且人數在5個以上
select sex,count(*)? from student where age > 20?group by sex? having count(*) > 5
having 后的表達式,必須是 select 后面出現的非別名的有效表達式,?
?
6.3 分組后排序 按照人數進行排序 (各個關鍵字的順序不可以錯)
?#having 后 可以使用 select 語句中的別名
例如:顯示每個地區的總人口數和總面積.僅顯示那些面積超過1000000的地區。
SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000
?
6.4 關鍵字的順序
從前至后: select? , from? , where? , group by ,? ?having ,? order by? ,limit
?
七 .子查詢
?
從1個查詢中,查詢出的結果 ,被其他查詢利用;
1.where 子句中的 子查詢
假設現在有兩個表 ,學生表 student (sid,sname...)? 與 成績表 score(id,sid,degree...)
成績表中列出了每個學生的成績,但是成績表中只存儲了 學生的id,并沒有存儲學生的姓名
先有一個需求,查詢出任意一門成績在90分以上的學生的sid 與 姓名
分析: 現在兩張表中,沒有一張表能同時包含,成績 與 學生姓名,此時需要先從成績表中查詢出 大于90分的學生id集合
? ? ? 再從學生表中,查詢出 id在上述查詢結果集中的 學生的姓名即可
select sid,name from student where? sid in ( select sid from score where score > 90)
?
如果要查詢 90分以下的,則只需要再 in 前面加上 not 即表示否定
select sid,name from student where? sid not in ( select sid from score where score > 90)
注意:
1.如需使用子查詢,則要將子查詢 用() 引起來
2.子查詢可以嵌套多層, 凡涉及到的子查詢 最好每個都要格式化顯示, 便于閱讀,上述SQL語句改造如下(雖然還是不好看.格式化語句在復雜的SQL語句時,顯得格外重要)
??select sid,name from student
where? sid in (
select sid?from score where score > 90)
3.sql的執行 總是從內向外執行
?
2.子查詢作為計算字段
假設有兩張表 , 客戶表(customers)與訂單表(orders) ,客戶表 存放客戶的相關信息, 主鍵為c_id ,
訂單表存放客戶c_id 與 客戶所下的訂單號
現有需求,需要顯示每位客戶的id, name ,與他們的訂單總量
select c_id ,name, (
select count(*) from orders
where? orders.c_id = customers.c_id ) AS? order_count
from customers?
如上所示, 將從訂單表中查詢的統計的數量作為計算字段, 進行展示
注意:
1.select子句作為計算字段, 必需要保證 子查詢的行數與 外層 的行數保持一致 即沒一行,都對應計算字段的一個值 ,不然會報錯
2.因為from的優先級最高,因此可以在子句中訪問到 customers的c_id ,但是兩張表中都有c_id 這個字段,進行條件關聯時,需要指明是哪個表下的(表明.列名)即完全限定名,否則會出現歧義
?
?
八.聯結表
?此章節后,所有的表均采用 <<sql必知必會 >> 書籍中所用的表與數據 , 構建表,去插入數據的腳本,請前往以下網址自行下載:http://www.forta.com/books/0672327120/
?
表之間的關聯如下所示,即通過外鍵
?
?
?
正如上面所提到的例子, 訂單表,與客戶表 ,往往兩個關聯的表之間都是 一個表中只保存著另一個表中的主鍵即可(即外鍵)
優勢:
1.當表中更新了內容后, 不需要更改關聯表,便于表的維護
2.減少了字段的冗余
3.表的專一性更強, 只保存某些信息
?
?
8.1.使用where 子句進行聯結??
例:查詢產品名稱,價格,供應商名稱 關聯條件 即 供應商id
select?products.prod_name,products.prod_price,vendors.vend_name?
from??vendors, products
where??vendors.vend_id =?products.vend_id
只會篩選出 符合where 條件的記錄
?
8.2 笛卡爾積?
select * from?vendors,products? 如果么有建立where條件 , 這時查詢出來的結果數 =?vendors行數 *?products? 行數, 進行組合 這種現象叫做笛卡爾積
去除笛卡爾積就需要使用 where 條件進行表的聯結
?
8.3 內部聯結
使用where子句進行聯結 為 等值聯結. 可以更換為內聯, 同樣也是只匹配出 滿足聯結條件的數據 結果與 where條件聯結 完全一致
其中inner 可以省略, 使用join 默認就是內聯
關鍵字: table1 inner join table2? on 條件
select??* from?vendors inner join?products? ?on?vendors.vend_id =?products.vend_id
?
8.4 支持聯結多張表
例如 : 訂單明細表中會存儲商品的ID ,此時可以 將 供應商, 商品,訂單 三張表關聯
select * from orderitems
inner join products? on orderitems.prod_id =??products.prod_id
inner join vendors on products.vend_id =? vendors?.vend_id?
聯結表時是非常消耗性能的,應當減少不必要的聯結
?
8.5 聯結時,也可以使用表的別名,優勢:
1.縮短SQL
2.通過別名,允許在單句SQL中多次使用 相同的一張表
select??* from?vendors? as v
inner join?products? as p
on v.v_id = p.v_id
?
8.6 自聯結
即用一張表,關聯自己
需求: prod_id 為 DTNTR 的產品存在質量問題,想知道該產品的供應商下的其他產品是否也存在質量問題
分析: 可以通過 prod_id = 'DTNTR?'? 查詢出 vend_id (供應商id) ,再根據 查詢得到的 vend_id 得到所有的產品,因此可以使用子查詢完成
select * from products
where vend_id = (
select vend_id from products where prod_id = 'DTNTR'
)
?
除了子查詢外,還可以通過表的自聯結來查詢出 該供應商下的所有產品,
注意:因為要查詢供應商下所有商品, 因此關聯條件應該是供應商的id?
select p1.* from products as p1 , products as p2?
where?p1.vend_id =?p2.vend_id? and? p1.prod_id = 'DTNTR'
?
上述:p1.* 表示結果集中只顯示 p1下的所有列,? p2中的不予顯示 ,這樣就避免相同的列重復出現多次
?
8.7 外鏈接
外鏈接分為左外聯 與 右外聯? ? ?
左聯結:
需求:查看每位顧客的訂單記錄
由于存在顧客沒有下過訂單, 如果仍然使用內聯接 ,就會導致檢索出的顧客 不是全部顧客,也就是未能滿足需求,此時需要用到外聯結
方式1:使用左聯結
select * from customers
left outer join orders
on customers.cust_id = orders.cust_id
方式2:使用右聯結
?select * from customers?
right outer join?orders?
on customers.cust_id = orders.cust_id
?
特點:
1.左與右是相對概念 ,是針對 表在 outer join 語句的左右位置來判定的?
2.其中left outer join? 與 right outer join 中的 outer可以省略
3.要顯示左邊全部記錄,則使用 left outer join 即可 ,同理要顯示 右邊表全部記錄,則使用right outer join 即可
?
一張自制圖表示inner join? ,left outer join,right outer join
?
?
?
8.8 聯結 后 使用聚集函數?
?
需求: 查詢出所有客戶所下的訂單數?
所有客戶即要檢索出客戶表中所有記錄
?
select customers.cust_id,cust_name , COUNT(orders.order_num) as order_counts
from customers
left outer join orders
on customers.cust_id = orders.cust_id
GROUP BY customers.cust_id
?
小結:
1.平時需求中,更多的是使用內部聯結,外部聯結需要分清方向
2.聯結時為了消除笛卡爾積 ,所有的連接都應使用 聯結條件
3.聯結條件要使用正確, 否則會出現很多錯誤的結果
?
?
九.組合查詢?
?
9.1 關鍵字: union
作用:將兩個SQL語句執行的結果 ,組合在一起 進行返回,作用如下圖所示
?
?
?
?
舉個栗子:假如需要查詢出價格小于等于5的所有物品的一個列表 ,同時包含供應商1001 和 1002 生產的所有產品(沒有價格限制)
該需求可以使用where 條件句 使用 or 連接兩個條件 來實現,如下所示:
select * from products
where prod_price <= 5 or
vend_id in (1001,1002)
order by vend_id
?
同樣也可以使用 union 來完成,代碼如下:
select * from products where prod_price <= 5
UNION
select * from products where vend_id in (1001,1002)
order by vend_id
?
union規則:
1.必需是兩條或兩條以上的select 子句之間使用union ,3個select語句 則 需要使用兩次union
2.union 連接的兩個select 結果集必需擁有相同數量的列,表達式,或聚集函數
3.結果集中 對應的列 ,必需保證數據可以兼容
?
9.2 union? 與 union all
union 會自動去除重復的行,而 union all 則不會,因此union all的效率 要高于 union
如果需要顯示所有的行,則應該使用union all?
?
9.3 union 與 order by
如果要對 union后的結果進行排序 ,則只能在最后一個select 語句 末尾 加上 order by ,其他的 select語句則不能加!
union 可以使復雜的 sql 語句 閱讀起來更加清晰,直觀 , 必須要時應當使用 union 代替復雜的 where子句
?
?
十.全文本檢索
?
10.理解全文本檢索
?
并不是所有數據庫存儲引擎都支持全文本檢索, mysql中最常用的搜索引擎為MyISAM和 InnoDB ,前者?MyISAM 引擎支持全文本檢索,而mysql5.6版本之前,InnoDB不支持
在mysql5.6之后的版本innodb 引擎也開始支持?
第八章給出的樣例表中 :productnotes 的存儲引擎為:MyISAM
?
之前的模糊匹配 like 關鍵字 利用 % 與 _ 匹配文本? , 如果滿足不了需求,還可以使用 關鍵則 regexp 使用正則進行匹配更加復雜的值
但是使用like 與 regexp 都存在幾個重要限制:
1.性能問題 : 通配符 與 正則表達式 ,通常會要求匹配表中所有的行(通常情況,檢索的列基本都沒有建立索引),隨著數據不斷的累積,行數不斷的增加,耗時也會越來越久
2.明確控制: 使用通配 與 正則,很難控制同時? a.滿足匹配關鍵字的條件? ?b.滿足 排除關鍵字的條件?
例如:我想要篩選 包含 Comment, 但是內容不包含vendor的行?雖然like 和 正則 可以輕松滿足行中包含 comment關鍵字的要求,但是并不容易過濾掉包含vendor的內容
3.智能化結果 : 1個匹配 通常只會匹配到 滿足關鍵字的行, 他不會關心,這個關鍵字出現了一次或是多次 .換言之如果一個關鍵字重復出現多次,可能匹配程度就越高,但是like 正則 只會默認的對結果進行展示,并不關心匹配的優先級
如果我的需求是 想要查詢包含指定關鍵字的行, 同時 關鍵字出現在內容前端的 或者 關鍵字出現 多次的 會在結果集展示中排在靠前的位置 這種需求不是 like 與 regexp 能滿足的
此時這種需求 可以使用全文本檢索來實現 , 此時MySQL不需要分別查看每個行,不需要分別分析和處理每個詞.MySQL可以快速有效地決定哪些詞匹配
哪些詞不匹配,他們的匹配頻率等等...
?
10.2 使用全文本檢索
?兩個關鍵字 match(希望檢索的列名) ,?against(search_content)
在productnotes表中, 搜索出包含 rabbit 的行,此時語法為:
select note_text from productnotes
where match(note_text) against('rabbit')
結果如下:
?
同樣也可以使用 like 進行查找
?select note_text from productnotes?where note_text like '%rabbit%'
結果如下:
?
乍一看,都能正確按照需求檢索出滿足條件的行, 僅僅是排序不一致而已;但是仔細看一下,就是這個排序有很大的關系
怎么說呢? like 查找出的結果集中, 第一行的尾部 包含 rabbit 關鍵字 , 第二行 是第四個單詞就包含了 rabbit 關鍵字;
通常情況下,越靠前的那個更加是你想要找的那個結果,因此此時 全文檢索相對 like 提供了排序的功能.并且在數據量越來越大的時候,能更明顯的縮短檢索所需的時間
?
此時變化一下我們的sql語句,將 match() against() 作為計算字段,如下:
select note_text ,match(note_text) against('rabbit') as rank from productnotes
得出的結果集為:
?
?從結果可以看到, 不匹配的等級 rank 都為 0 ,匹配到兩條記錄rank值 都有1個大于0的值,而這個值 就表示了匹配的等級 ,等級值越大就表示該結果更可能是你想要的行,這一點是like所做不到的;
?
10.3 全文檢索 之 查詢擴展?
?查詢拓展的目的是,設法放寬全文本檢索搜索的結果范圍
假如: 我想找到note_text 列中包含 anvils的內容, 但是只有一行內容包含 anvils ,但是我還想找出 與 這一行 內容非常相似的行 ,那我該怎么辦呢?
此時就需要使用到 查詢拓展, 他會幫你開啟腦洞模式, 查詢拓展會對 查詢內容 與 索引 掃描兩次 ,來完成這次檢索:
首先:正兒八級的找出滿足條件的所有行 (在這個例子中,滿足條件的只有一行)
其次:MySQL 會檢索出第一步篩選出來的行,找出這些行中 有用的詞(有用的詞這個標準由mysql自行判斷)
最后:mysql 會根據 輸入的關鍵字? 和 第二步 得到的有用的詞? ,再進行一遍檢索, 將此次檢索的結果集作為最終結果返回;
?
使用查詢拓展的語法為: against(select_content WITH QUERY EXPANSION)
不使用查詢拓展的結果如下:
select note_text from productnotes where MATCH(note_text) AGAINST('anvils')
?
?使用查詢擴展的結果如下:
select note_text from productnotes
where MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION)
?
?分析查詢的結果:這次返回了7行. 第一行是包含我們要檢索的內容 等級自然最高 ,第二行 雖然與anvils無關,但是他包含了第一行當中的兩個單詞 customer 與 recommend 所以也被檢索了出來
同理第三行也包含了這兩個相同的詞.雖然mysql很智能的幫你篩選出來了一些與 你想查詢結果 非常相近的結果,但是同時也極大的增多了返回的行數,更多的可能是你不想要的
所以視情況決定要不要使用查詢拓展
?
10.4 全文檢索 之 布爾文本搜索
格式 match(列名)? against(search_content IN BOOLEAN MODE)
功能:
1.要匹配的詞
2.要排斥的詞
3.排列提示
4.表達式分組
5.另外一些內容
注意:
1.布爾文本搜索沒有fulltext也可以使用,但是執行效率會降低
2.在正常的檢索中默認會將結果集按照等級值降序排列,但布爾文本表達式中,不會對查詢的結果按照等級值降序排列.
?
布爾操作符?
+ 表示該詞必需存在
-? 排除,表示該詞必須不能出現
> 包含,且出現該詞增加等級值
< 包含,且出現該詞降低等級值
() 多個操作符嵌套作用一個 或 一組詞語
~ 取消一個詞的排序值
* 詞尾通配符,匹配任意個數的任意字符
"" 定義一個短語, 匹配與 包含 必需 是針對這個短語整體的操作
?
需求:查詢productnotes中note_text 中 包含heavy 但不包含任意以 rope開頭的單詞
首先查詢出所有包含 note_text 的記錄
select note_text from productnotes
where match(note_text) AGAINST('heavy' IN BOOLEAN MODE)
共兩條記錄,結果如下:
?
此時第1條記錄雖然包含了 heavy 但是同時也包含了 ropes , 這不是我們想要的,需要使用排除符 將其排除 ,使用語法如下:
select note_text from productnotes?
where match(note_text) AGAINST('heavy -rope*'? IN BOOLEAN MODE)
結果如下:
成功的排除了第一行包含ropes單詞的記錄...
?
其他操作符介紹:
無操作符
需求:檢索出包含 rabbit 或 bait? 的記錄
select note_text from productnotes?
where match(note_text) AGAINST('rabbit bait' IN BOOLEAN MODE)
?
?
+ : 必需包含?
需求:檢索出同時包含 rabbit 與 bait 的記錄
select note_text from productnotes
where match(note_text) AGAINST('+rabbit +bait' IN BOOLEAN MODE)
?
?
"":雙引號內 整體匹配 或 排除
需求:檢索出 包含 ''rabbit bait" 整體的 記錄
select note_text from productnotes?
where match(note_text) AGAINST('"rabbit bait"' IN BOOLEAN MODE)
?
?>: 出現該詞會提高等級評分?
< : 出現該詞會降低等級評分?
需求:檢索出 包含 rabbit 或 carrot的記錄, 并且 包含rabbit 的等級要高. carrot的優先級要降低
select note_text from productnotes
where match(note_text) AGAINST('>rabbit <carrot' IN BOOLEAN MODE)
?
():多個操作符嵌套作用一個 或 一組詞語
需求:檢索必需同時包含safe 和 combination 的記錄.并且降低出現?combination? 的等級
select note_text from productnotes
where match(note_text) AGAINST('+safe +(<combination)' IN BOOLEAN MODE)
?
?
10.5 全文檢索 之 中文
由于中文分隔符的原因,不能很好的支持中文(日文)的全文檢索, 可以說基本不支持 .
如果需要適配,提供簡單思路:
1.內容中加上合適空格 逗號 或其他分隔符,?
2.同時到配置文件my.ini文件 中修改最小檢索長度,
ft_min_word_len = 2(ft_min_word_len 默認是4 )
修改后,保存文件 需重啟mysql服務方能生效
查看當前ft_min_word_len? 的值 ,使用命令 :?SHOW VARIABLES LIKE 'ft_min_word_len'
最好是使用Apache組織 開發的?Lucene 全文檢索工具類.
?
10.6 全文檢索使用說明:
1 在索引全文本數據時,短詞被忽略且從索引中排除。短詞定義為具有3個或3個以下字符的詞(可以更改) 2 mysql帶有一個內建的非用詞表(stopword),這些詞在全文本搜索中被忽略 (如需要這個表可覆蓋) 3 mysql規定了一條50%規則,如果一個詞出現在50%以上的行中,則將它忽略,不使用與in boolean mode 4 如果表中的行少于3行,全文本搜索不返回結果(因為每次詞或者不出現,或者至少50%出現) 5 忽略詞中的個單引號,例如: dot't 索引為 dont 6.不具有詞分隔符的語言(漢語,日語) 不能恰當的返回全文檢索的結果轉載于:https://www.cnblogs.com/lzzRye/p/9359284.html
總結
以上是生活随笔為你收集整理的1小时钟回顾MySQL语法(中)的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 意想不到的JavaScript(每日一题
- 下一篇: 课时5:闲聊之Python的数据类型