mysql常用技能分享

標籤：如何   首字母   order   修改   表示   靈活   group   details   左串連   

一，MySQL查詢的五種子句：

1，where(條件查詢)，常用的運算子:

①比較子

> , < , = , != , >= , <= , in( ) , between val1 and val2

②邏輯運算子

邏輯非：not / ! , 邏輯或：or / || , 邏輯與：and / &&

③模糊查詢“like”

萬用字元：% 任一字元，例如：where name like ‘蘋果%’

_ 匹配單個字元，例如：where name like ‘蘋果__’

 

2，group by(分組)，一般情況下和統計函數(彙總函式)一起使用

mysql五種統計函數:

①max：求最大值 例如查詢每個分類下面最高價格的商品：

select cat_id max(price) from goods group_by cat_id

②min：求最小值

③sum：求總和，例如：select sum(goods_number) from goods

④avg：求平均值，例如：求每個分類的商品平均價格

select cat_id avg(price) from goods group_by cat_id

⑤count：求商品條數，例如：select count(*) from goods

可以把每個欄位名當成變數來理解，它可以進行運算：

例：查詢本店每個商品價格比市場價低多少；select goods_id,goods_name,goods_price-market_price from goods;

可以用as給欄位名起別名，group by前面如果沒有使用彙總函式，預設就取每個分組的第一行資料

 

3，having與where的異同

having與where類似，可以篩選資料，where後的運算式怎麼寫，having後的運算式就怎麼寫

where針對錶中的列發揮作用，查詢資料

having針對查詢結果中的列發揮作用，篩選資料

#查詢本店商品價格比市場價低多少錢，輸出低200元以上的商品

select goods_id,good_name,market_price - shop_price as s from goods having s>200 ;

#如果用where的話則是：

select goods_id,goods_name from goods where market_price - shop_price > 200;

#同時使用where與having

select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s > 200;

4，order by

（1） order by price  //預設升序排列

（2）order by price desc //降序排列

（3）order by price asc //升序排列，與預設一樣

（4）order by rand() //隨機排列，效率不高

#按欄目號升序排列，每個欄目下的商品價格降序排列：

 select * from goods where cat_id !=2 order by cat_id,price desc;

5，limit：limit [offset,] N

offset 位移量，可選，不寫則相當於limit 0,N。N取出條目

#取價格4-6高的商品

select good_id,goods_name,goods_price from goods order by good_price desc limit 3,3;

二，mysql子查詢

1，where型子查詢

（把內層查詢結果當作外層查詢的比較條件）

#不用order by 來查詢最新的商品

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

#取出每個欄目下最新的產品(goods_id唯一)

select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

2，from型子查詢(把內層的查詢結果供外層重新查詢)

#用子查詢查出掛科兩門及以上的同學的平均成績

思路：#先查出哪些同學掛科兩門以上

select name,count(*) as gk from stu where score < 60 having gk >=2;

#以上查詢結果當做一個表放入下面的查詢中去

select name from (select name,count(*) as gk from stu having gk >=2) as t;

#找出這些同學了，那麼再計算他們的平均分

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

3，exists型子查詢（把外層查詢結果拿到內層，看內層的查詢是否成立）

#查詢哪些欄目下有商品，欄目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

如果商品表中的商品的欄目id等於某個欄目的id那麼證明該欄目下面存在商品，所以這些欄目在條件下成立。會被查詢出來。

三，union的用法

UNION用於把來自許多SELECT語句的結果組合到一個結果集合中

四，內串連，左串連，右串連，全串連

表T1：                     表T2：id name passwd        id jifen dengji1  jack   jpw             1   20    32  tom   tpw　　　　  3   50    4

1，內串連

如果想把使用者資訊、積分、等級都列出來，那麼一般會這樣寫：普通:select * from T1, T2 where T1.id = T2.id內連:select * from T1 inner join T2 on T1.id = T2.id後者的效率比前面的效率高多了，建議使用內連

2，左串連

顯示左表T1中的所有行，並把右表T2中符合條件加到左表T1中；右表T2中不符合條件，就不用加入結果表中，並且NULL表示。SQL語句：select * from T1 left join T2 on T1.id = T2.id運行結果 :T1.id name  passwd  T2.id  jifen   dengji   1    　jack    jpw    　　1    　20    3   2       tom    tpw      NULL   NULL   NULL   

3，右串連

顯示右表T2中的所有行，並把左表T1中符合條件加到右表T2中；左表T1中不符合條件，就不用加入結果表中，並且NULL表示。SQL語句：select * from T1 right join T2 on T1.id = T2.id運行結果：T1.id   name   passwd   T2.id   jifen   dengji   1        jack      jpw         1        20      3   NULL   NULL   NULL        3        50      4

4，全串連

顯示左表T1、右表T2兩邊中的所有行，即把左連接果表 + 右連接果表組合在一起，然後過濾掉重複的。SQL語句：select * from T1 full join T2 on T1.id = T2.id運行結果   T1.id   name   passwd   T2.id   jifen   dengji   1         jack      jpw        1         20    3   2         tom      tpw     NULL   NULL   NULL   NULL   NULL   NULL        3      50      4 

四，MySQL的索引

1，什麼是索引

一個索引是儲存的表中一個特定列的值資料結構（最常見的是B-Tree）。索引是在表的列上建立。所以，要記住的關鍵點是索引包含一個表中列的值，並且這些值儲存在一個資料結構中。說到底索引就是對資料列的值進行結構化排序的一個東西。記住這一點：索引是一種資料結構

B-Tree 是最常用的用於索引的資料結構。因為它們是時間複雜度低， 尋找、刪除、插入操作都可以可以在對數時間內完成。另外一個重要原因儲存在B-Tree中的資料是有序的。資料庫管理系統（RDBMS）通常決定索引應該用哪些資料結構。但是，在某些情況下，你在建立索引時可以指定索引要使用的資料結構。

2，索引是怎麼提升效能的？

因為索引基本上是用來儲存列值的資料結構，這使尋找這些列值更加快速。如果索引使用最常用的資料結構-B-Tree-那麼其中的資料是有序的。有序的列值可以極大的提升效能。下面解釋原因。

假設我們在 Employee_Name這一列上建立一個B-Tree索引。這意味著當我們用之前的SQL尋找姓名是‘Jesus’的僱員時，不需要再掃描全表。而是用索引尋找去尋找名字為‘Jesus’的僱員，因為索引已經按照按字母順序排序。索引已經排序意味著查詢一個名字會快很多，因為首字母為‘J’的員工都是排列在一起的。另外重要的一點是，索引同時儲存了表中相應行的指標以擷取其他列的資料。

3，資料庫索引裡究竟存的是什嗎？

你現在已經知道資料庫索引是建立在表的某列上的，並且儲存了這一列的所有值。但是，需要理解的重點是資料庫索引並不儲存這個表中其他列（欄位）的值。舉例來說，如果我們在Employee_Name列建立索引，那麼列Employee_Age和Employee_Address上的值並不會儲存在這個索引當中。

索引儲存了指向表中某一行的指標

如果我們在索引裡找到某一條記錄作為索引的列的值，如何才能找到這一條記錄的其它值呢？這是很簡單 - 資料庫索引同時儲存了指向表中的相應行的指標。指標是指一塊記憶體地區， 該記憶體地區記錄的是對硬碟上記錄的相應行的資料的引用。因此，索引中除了儲存列的值，還儲存著一個指向所在行資料的索引。也就是說，索引中的Employee_Name這列的某個值（或者節點）可以描述為 (“Jesus”, 0x82829)， 0x82829 就是包含 “Jesus”那行資料在硬碟上的地址。如果沒有這個引用，你就只能訪問到一個單獨的值（“Jesus”），而這樣沒有意義 

4，雜湊表索引是怎麼工作的？

雜湊表是另外一種你可能看到用作索引的資料結構-這些索引通常被稱為雜湊索引。使用雜湊索引的原因是，在尋找值時雜湊表效率極高。所以，如果使用雜湊索引，對於比較字串是否相等的查詢能夠極快的檢索出的值。例如之前我們討論過的這個查詢(SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’) 就可以受益於建立在Employee_Name 列上的雜湊索引。哈系索引的工作方式是將列的值作為索引的索引值（key），和索引值相對應實際的值（value）是指向該表中相應行的指標。因為雜湊表基本上可以看作是關聯陣列，一個典型的資料項目就像“Jesus => 0x28939″，而0x28939是對記憶體中表中包含Jesus這一行的引用。在哈系索引的中查詢一個像“Jesus”這樣的值，並得到對應行的在記憶體中的引用，明顯要比掃描全表獲得值為“Jesus”的行的方式快很多。

5，雜湊索引的缺點

雜湊表是無順序的資料結構，對於很多類型的查詢語句雜湊索引都無能為力。舉例來說，假如你想要找出所有小於40歲的員工。你怎麼使用雜湊索引進行查詢？因為雜湊表只適合查詢索引值對-也就是說查詢相等的查詢（例：like “WHERE name = ‘Jesus’）。雜湊表的索引值映射也暗示其鍵的儲存是無序的。這就是為什麼雜湊索引通常不是資料庫索引的預設資料結構-因為在作為索引的資料結構時，它沒有B-Tree那麼靈活

6，聚簇索引：

實際儲存的順序結構與資料存放區的物理結構是一致的，所以通常來說物理順序結構只有一種，那麼一個表的聚簇索引也只能有一個，通常預設都是主鍵，設定了主鍵，系統預設就為你加上了聚簇索引，當然有人說我不想拿主鍵作為聚簇索引，我需要用其他欄位作為索引，當然這也是可以的，這就需要你在設定主鍵之前自己手動的先添加上唯一的聚簇索引，然後再設定主鍵，這樣就木有問題啦。總而言之，聚簇索引是順序結構與資料存放區物理結構一致的一種索引，並且一個表的聚簇索引只能有唯一的一條。

7，非聚簇索引：

非聚簇索引記錄的物理順序與邏輯順序沒有必然的聯絡，與資料的儲存物理結構沒有關係；一個表對應的非聚簇索引可以有多條，根據不同列的約束可以建立不同要求的非聚簇索引。

8，主鍵索引和唯一索引的區別

主鍵建立後一定包含一個唯一性索引，唯一性索引不一定就是主鍵。

唯一性索引列允許空值，而主鍵列不允許為空白值。

主鍵列在建立時，已經預設為空白值 + 唯一索引了。

主鍵可以被其他表引用為外鍵，而唯一索引不能。

一個表最多隻能建立一個主鍵，但可以建立多個唯一索引。

主鍵更適合那些不容易更改的唯一標識，如自動遞增列、社會安全號碼等。

總結索引使用原則：

1:不要索引資料量不大的表，對於小表來講，表掃描的成本並不高。

2:不要設定過多的索引，在沒有叢集索引的表中，最大可以設定249個非叢集索引，過多的索引首先會帶來更大的磁碟空間，而且在資料發生修改時，對索引的維護是特別消耗效能的。

3:合理應用複合索引，有某些情況下可以考慮建立包含所有輸出資料行的覆蓋索引。

4:對經常使用範圍查詢的欄位，可能考慮叢集索引。

5:避免對不常用的列，邏輯性列，大欄位列建立索引。

MySQL只對以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時候的like(不以萬用字元%或_開頭的情形)

來源[http://www.cnblogs.com/rollenholt/archive/2012/05/15/2502551.html]，[http://blog.csdn.net/weiliangliang111/article/details/51333169]

感謝以上博友分享！

mysql常用技能分享