Mysql學(xué)習(xí)筆記

1、MySQL學(xué)習(xí)筆記1 查詢語句1.1 簡單查詢1.1.1 distinct刪除重復(fù)行SELECT語句中使用ALL或distinct選項來顯示表中符合條件的所有行或刪除其中重復(fù)的數(shù)據(jù)行，默認為ALL。使用distinct選項 時，對于所有重復(fù)的數(shù)據(jù)行在SELECT返回的結(jié)果集合中只保留一行。distinct一般需要處理單一字段時使用，不能用于多個字段。例如：select   distinct   ID,AA,BB   from   tName select 

2、  distinct   ID   from   tName 區(qū)別在于: 第一種情況是把三個字段的內(nèi)容都相同的記錄認為是相同的記錄,合并一條，如果有一個字段是不同的，認為不是相同，不合并。我要消除name字段值重復(fù)的記錄，同時又要得到id字段的值，其中id是自增字段。 如何用distinct消除重復(fù)記錄的同時又能選取多個字段值？ select distinct name from t1能消除重復(fù)記錄，但只能取一個字段，現(xiàn)在要同時取id,name這2個字段的值。

3、60; select distinct id,name from t1可以取多個字段，但只能消除這2個字段值全部相同的記錄。最后解決方法： SELECT id,name FROM t1 WHERE id IN(SELECT MAX(id) FROM t1 GROUP BY name) order by id desc注意開頭的 id 的一定要，后面的order by里有的字段一定要加進select結(jié)果，要不然排序無效。1.1.2 限制返回的行數(shù)使用TOP n PERCENT選項限制返回的數(shù)據(jù)行數(shù)，TOP n說明返回n行，而TOP n

4、PERCENT時，說明n是表示一百分數(shù)，指定返回的行數(shù)等于總行數(shù)的百分之幾。例如: SELECT TOP 2 * FROM testtable SELECT TOP 20 PERCENT * FROM testable1.1.3 WHERE子句設(shè)置查詢條件WHERE子句可包括各種條件運算符:1. 比較運算符(大小比較):>、>=、=、<、<=、<>、!>、!<2. 范圍運算符(表達式值是否在指定的范圍):BETWEENANDNOT BETWEENAND3. 列表運算符(判斷表達式是否為列表中的指定項):IN (項1,項2)NOT IN (項1,項

5、2)4. 模式匹配符(判斷值是否與指定的字符通配格式相符):LIKENOT LIKE常用配字符:Ø 百分號%:可匹配任意類型和長度的字符，如果是中文，請使用兩個百分號即%。Ø 下劃線_:匹配單個任意字符，它常用來限制表達式的字符長度。Ø 方括號:指定一個字符、字符串或范圍，要求所匹配對象為它們中的任一個。:其取值也和相同，但它要求所匹配對象為指定字符以外的任一個字符。5. 空值判斷符(判斷表達式是否為空):IS NULLNOT IS NULL6. 邏輯運算符(用于多條件的邏輯連接):NOTANDOR1.1.4 Order by排序使用ORDER BY子句對查詢返回

6、的結(jié)果按一列或多列排序。ORDER BY子句的語法格式為:ORDER BY column_name ASC|DESC ,n其中ASC表示升序，為默認值，DESC為降序。ORDER BY不能按ntext、text和image數(shù)據(jù)類型進行排序。1.1.5 COUNT()函數(shù) count(*)對行的數(shù)目進行計算，包含NULL。count(column)對特定的列的值具有的行數(shù)進行計算，不包含NULL值。count()還有一種使用方式，count(1)這個用法和count(*)的結(jié)果是一樣的。1.1.6 查詢字段為NULL在mysql中，查詢某字段為空時，不可用 = null，而是 is n

7、ull，不為空則是 is not null。具體格式如下:select * from table name where column name is null;select * from table name where column name is not null;錯誤的寫法：select * from  table name  wh

8、ere  column name=null;select * from  table name  where length(column name)=0;1.1.7 IFNULL、ISNULL和NULLIF區(qū)別IFNULL用法說明：IFNULL(expr1,expr2)如果expr1不是NULL，IFNULL()返回expr1，否則它返回expr2。IFNULL()返回一個數(shù)字或字符串值，取決于它被使用的上下文環(huán)境。ISNULL用法說明：ISNULL(expr)如expr為n

9、ull，那么isnull()的返回值為1，否則返回值為0。NULLIF用法說明：NULLIF(expr1,expr2)如果expr1 = expr2成立，那么返回值為NULL，否則返回值為expr1。1.1.8 case when語句SELECT                case            #如果  

10、  when sex='1' then '男' #sex='1'，則返回值'男'    when sex='2' then '女' #sex='2'，則返回值'女'     else '其他'        #其他的返回'其他    end   &

11、#160;        #結(jié)束from  sys_user            整體理解： 在sys_user表中如果sex='1'，則返回值'男'如果sex='2'，則返回值'女' 否則返回'其他。用法一：SELECT     CASE WHEN STATE = '1

12、9; THEN '成功'     WHEN STATE = '2' THEN '失敗'    ELSE '其他' END  FROM  SYS_SCHEDULER用法二：SELECT STATE CASE WHEN '1' THEN '成功'     WHEN '2' THEN '失敗'   

13、0;ELSE '其他' END  FROM  SYS_SCHEDULER1.2 嵌套查詢（子查詢）嵌套查詢的意思是，一個查詢語句(select-from-where)查詢語句塊可以嵌套在另外一個查詢塊的where子句中，稱為嵌套查詢。其中外層查詢也稱為父查詢，主查詢。內(nèi)層查詢也稱子查詢，從查詢。嵌套查詢的工作方式是：先處理內(nèi)查詢，由內(nèi)向外處理，外層查詢利用內(nèi)層查詢的結(jié)果嵌套查詢不僅僅可以用于父查詢select語句使用。還可以用于insert、update、delete語句或其他子查詢中。1.2.1 子查詢?nèi)魏慰梢允褂帽磉_式的地方都可以使用子查詢，只要它返回的

14、是單個值。如果某個表只出現(xiàn)在子查詢中二不出現(xiàn)在外部查詢中，那么該表的列就無法包含在輸出中。select name,age from person where age > ( select age from person where name = '孫權(quán)')1.2.2 in嵌套查詢in關(guān)鍵字用于where子句中用來判斷查詢的表達式是否在多個值的列表中。返回滿足in列表中的滿足條件的記錄。示例：select name from person where countryid in ( select countryid from country where countryname

15、 = '魏國')輸出結(jié)果為：1.2.3 some嵌套查詢some在sql中的邏輯運算符號，如果在一系列比較中，有些值為True，那么結(jié)果就為True。some的語法是：<表達式> =|<>|!=|>|>=|!>|<|<=|!<some(子查詢)示例：select name from person where countryid = some -用等號和以下查詢到的值比較，如果與其中一個相等，就返回( select countryid from country where countryname = '魏國

16、9;)輸出結(jié)果為：1.2.4 all嵌套查詢all是sql中的邏輯運算符好，如果一系列的比較都為true，那么結(jié)果才能為true。<表達式> =|<>|!=|>|>=|!>|<|<=|!<all(子查詢)示例：select name from person where countryid > all -當(dāng)countryid大于以下返回的所有id，此結(jié)果才為True，此結(jié)果才返回( select countryid from country where countryname = '魏國')輸出結(jié)果為：1.2.5

17、exists嵌套查詢exists是sql中的邏輯運算符號。如果子查詢有結(jié)果集返回，那么就為True。exists代表“存在”的意義，它只查找滿足條件的那些記錄。一旦找到第一個匹配的記錄后，就馬上停止查找。exists子查詢其中子查詢是一個首先的select語句，不允許有compute子句和into關(guān)鍵字。exists 的意思是，子查詢是否有結(jié)果集返回。例如：SELECT * FROM PersonWHERE exists(SELECT 1 -SELECT 0 SELECT NULL 返回結(jié)果都一樣，因為這三個子查詢都有結(jié)果集返回，因此總是True SELECT * FROM Person照常執(zhí)

18、行)但是如果子查詢中因為加了條件而沒有結(jié)果集返回，則主語句就不執(zhí)行了：SELECT * FROM PersonWHERE exists( SELECT * FROM Person WHERE Person_Id = 100 -如果不存在Person_Id的記錄，則子查詢沒有結(jié)果集返回，主語句不執(zhí)行)1.3 連接查詢1.3.1 左連接（LEFT JOIN）LEFT JOIN 關(guān)鍵字會從左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中沒有匹配的行。數(shù)據(jù)庫在通過連接兩張或多張表來返回記錄時，都會生成一張中間的臨時表，然后再將這張臨時表返回給用戶。在lef

19、t join下，on和where兩者的區(qū)別：on是在生成臨時表的時候使用的條件，不管on的條件是否起到作用，都會返回左表 (table_name1) 的行。where則是在生成臨時表之后使用的條件，此時已經(jīng)不管是否使用了left join了，只要條件不為真的行，全部過濾掉。SQL語句：select a.id,a.No, from table1 a left join table2 b on (a.No = b.No and ='aaa');select a.id,a.No, from table1 a left join table2 b o

20、n (a.No = b.No) where ='aaa'表1：table2idNo1n12n23n3表2：table2Nonamen1aaan2bbbn3ccc第一個結(jié)果集：|id |No |name|-|-|-|1 |n1 |aaa|2 |n2 |(Null)|3 |n3 |(Null)| 第二個結(jié)果集：|id |No |name|-|-|-|1 |n1 |aaa|第一個sql的執(zhí)行流程：首先找到b表的name為aaa的記錄行on (a.No = b.No and =aaa)。然后找到a的數(shù)據(jù)（即使不符合b表的規(guī)則），生成臨時表返回用戶。 

21、第二個sql的執(zhí)行流程：首先生成臨時表，然后執(zhí)行where過濾=aaa不為真的結(jié)果集，最后返回給用戶。因為on會首先過濾掉不符合條件的行，然后才會進行其它運算，所以按理說on是最快的。在多表查詢時，on比where更早起作用。系統(tǒng)首先根據(jù)各個表之間的聯(lián)接條件，把多個表合成一個臨時表后，再由where進行過濾，然后再計算，計算完后再由having進行過濾。由此可見，要想過濾條件起到正確的作用，首先要明白這個條件應(yīng)該在什么時候起作用，然后再決定放在那里。對于JOIN參與的表的關(guān)聯(lián)操作，如果需要不滿足連接條件的行也在我們的查詢范圍內(nèi)的話，我們就必需把連接條件放在ON后面，而不能放在WHE

22、RE后面，如果我們把連接條件放在了WHERE后面，那么所有的LEFT,RIGHT,等這些操作將不起任何作用，對于這種情況，它的效果就完全等同于INNER連接。對于那些不影響選擇行的條件，放在ON或者WHERE后面就可以。記?。核械倪B接條件都必需要放在ON后面，不然前面的所有LEFT,和RIGHT關(guān)聯(lián)將作為擺設(shè)，而不起任何作用。select pj.id,,IFNULL(COUNT(vm1.vmname),0) as '虛擬機數(shù)量'from project pjLEFT JOIN(select ject_id, as vmname from vm_host vmwhere vm.status = 'A') vm1on (pj.id=ject_id )where pj.status != 'P'GROUP BY pj.id1.3.2 右連接（RIGHT JOIN）1.3.3 內(nèi)連接內(nèi)連接，也叫等值連接，inner join產(chǎn)生同時符合A和B的一組數(shù)據(jù)。mysql> select * from A