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

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

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

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

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

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

6、的結(jié)果按一列或多列排序。ORDER BY子句的語法格式為:ORDER BY column_name ASC|DESC ,n其中ASC表示升序，為默認(rèn)值，DESC為降序。ORDER BY不能按ntext、text和image數(shù)據(jù)類型進(jìn)行排序。1.1.5 COUNT()函數(shù) count(*)對(duì)行的數(shù)目進(jìn)行計(jì)算，包含NULL。count(column)對(duì)特定的列的值具有的行數(shù)進(jìn)行計(jì)算，不包含NULL值。count()還有一種使用方式，count(1)這個(gè)用法和count(*)的結(jié)果是一樣的。1.1.6 查詢字段為NULL在mysql中，查詢某字段為空時(shí)，不可用 = 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;錯(cuò)誤的寫法：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()返回一個(gè)數(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 嵌套查詢（子查詢）嵌套查詢的意思是，一個(gè)查詢語句(select-from-where)查詢語句塊可以嵌套在另外一個(gè)查詢塊的where子句中，稱為嵌套查詢。其中外層查詢也稱為父查詢，主查詢。內(nèi)層查詢也稱子查詢，從查詢。嵌套查詢的工作方式是：先處理內(nèi)查詢，由內(nèi)向外處理，外層查詢利用內(nèi)層查詢的結(jié)果嵌套查詢不僅僅可以用于父查詢select語句使用。還可以用于insert、update、delete語句或其他子查詢中。1.2.1 子查詢?nèi)魏慰梢允褂帽磉_(dá)式的地方都可以使用子查詢，只要它返回的

14、是單個(gè)值。如果某個(gè)表只出現(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子句中用來判斷查詢的表達(dá)式是否在多個(gè)值的列表中。返回滿足in列表中的滿足條件的記錄。示例：select name from person where countryid in ( select countryid from country where countryname

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

16、9;)輸出結(jié)果為：1.2.4 all嵌套查詢all是sql中的邏輯運(yùn)算符好，如果一系列的比較都為true，那么結(jié)果才能為true。<表達(dá)式> =|<>|!=|>|>=|!>|<|<=|!<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中的邏輯運(yùn)算符號(hào)。如果子查詢有結(jié)果集返回，那么就為True。exists代表“存在”的意義，它只查找滿足條件的那些記錄。一旦找到第一個(gè)匹配的記錄后，就馬上停止查找。exists子查詢其中子查詢是一個(gè)首先的select語句，不允許有compute子句和into關(guān)鍵字。exists 的意思是，子查詢是否有結(jié)果集返回。例如：SELECT * FROM PersonWHERE exists(SELECT 1 -SELECT 0 SELECT NULL 返回結(jié)果都一樣，因?yàn)檫@三個(gè)子查詢都有結(jié)果集返回，因此總是True SELECT * FROM Person照常執(zhí)

18、行)但是如果子查詢中因?yàn)榧恿藯l件而沒有結(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)鍵字會(huì)從左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中沒有匹配的行。數(shù)據(jù)庫在通過連接兩張或多張表來返回記錄時(shí)，都會(huì)生成一張中間的臨時(shí)表，然后再將這張臨時(shí)表返回給用戶。在lef

19、t join下，on和where兩者的區(qū)別：on是在生成臨時(shí)表的時(shí)候使用的條件，不管on的條件是否起到作用，都會(huì)返回左表 (table_name1) 的行。where則是在生成臨時(shí)表之后使用的條件，此時(shí)已經(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第一個(gè)結(jié)果集：|id |No |name|-|-|-|1 |n1 |aaa|2 |n2 |(Null)|3 |n3 |(Null)| 第二個(gè)結(jié)果集：|id |No |name|-|-|-|1 |n1 |aaa|第一個(gè)sql的執(zhí)行流程：首先找到b表的name為aaa的記錄行on (a.No = b.No and =aaa)。然后找到a的數(shù)據(jù)（即使不符合b表的規(guī)則），生成臨時(shí)表返回用戶。 

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

22、RE后面，如果我們把連接條件放在了WHERE后面，那么所有的LEFT,RIGHT,等這些操作將不起任何作用，對(duì)于這種情況，它的效果就完全等同于INNER連接。對(duì)于那些不影響選擇行的條件，放在ON或者WHERE后面就可以。記?。核械倪B接條件都必需要放在ON后面，不然前面的所有LEFT,和RIGHT關(guān)聯(lián)將作為擺設(shè)，而不起任何作用。select pj.id,,IFNULL(COUNT(vm1.vmname),0) as '虛擬機(jī)數(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)生同時(shí)符合A和B的一組數(shù)據(jù)。mysql> select * from A