MySQL基礎學習總結

MySQL基礎學習總結
1、MySQL基礎概念

mysql邏輯架構如下：

　　每個用戶端串連都會在伺服器中擁有一個線程，這個串連的查詢只會在這個單獨的線程中執行。

　　MySQL是分層的架構。上層是伺服器層的服務和查詢執行引擎，下層是儲存引擎。雖然有很多不同作用的外掛程式API，但儲存引擎API還是最重要的。如果能理解MySQL在儲存引擎和服務層之間處理查詢時如何通過API來回互動，就能抓住MySQL的核心基礎架構的精髓。

　　資料庫系統實現了各種死結檢測和死結逾時機制，InnoDB目前處理死結的機制是，將持有最少行級排它鎖的事務進行復原。MySQL服務層不管理事務，事務是由下層的儲存引擎實現的。

　　注意，SQL語句一般以';'或者'\g'結束。

• 資料庫(database): 儲存有組織的資料的容器。
• 表(table): 某種特定類型資料的結構化清單。
• 列(column): 表中的一個欄位，所有表都是由一個或多個列組成的。
• 資料類型(datatype): 所容許的資料的類型，每個表列都有相應的資料類型，它限制該類所儲存的類型。
• 行(row): 是表中的一個記錄。
• 主鍵(primary key): 是一列(或者一組列)，其值能夠唯一區分表中每個行。

 

2、使用MySQL

• mysql -h host -u username -p 與資料庫建立串連
• use database_name 選擇資料庫
• show databases 顯示所有的資料庫
• show tables 顯示當前資料庫下所有的表
• show columns from table_name 顯示表的列資訊，作用和desc table_name是一樣的

MySQL支援的其他show語句還有：

• show status 顯示MySQL伺服器狀態資訊
• show create database_name和show create table_name 分別顯示建立特定資料庫和資料表的MySQL語句
• show grants 顯示授予使用者的安全許可權
• show errors和show warnings 顯示伺服器錯誤或警告資訊

 

3、檢索資料

　　SQL語句中最常用的就是select語句了，它用來在一個或多個表中檢索資料，select使用樣本如下：

• select column_name from table_name 從table_name資料表中檢索column_name列，檢索單個列
• select column1_name, column2_name from table_name 檢索多個列
• select * from table_name 檢索所有列

  

　　注意，select返回所有匹配的行，但是，如果我們不想每個值都每次出現，怎麼辦呢？例如，想讓中輸出的id唯一，這樣可以再select語句中添加distinct關鍵字，select distinct id from goods，這樣顯示結果如下：

  　　有時我們想限制輸出的結果，比如返回第一行或前幾行，可使用limit子句，如下所示：

• select * from goods limit 2 返回前2行結果，注意，當行數不夠時，只能返回資料表中有限的行數
• select * from goods limit 2, 2 返回從行2開始的2行

　　當然，我們也可以使用完全限制來進行資料檢索：

select goods.id, goods.name from goods 輸出goods表中所有記錄

 

4、排序檢索資料

　　排序檢索資料主要使用select語句的order by子句，根據需要排序檢索出的資料，select語句預設返回結果是沒有特定順序的，在排序檢索資料時也可以指定排序的方向，比如升序或者降序等，order by子句預設為升序排列。

　   (這個是降序配列)

　　有時，我們需要對多個列排序怎麼辦呢？這時可以使用如下sql語句來執行，select * from goods order by id desc, name，注意，這裡是對id進行降序排列，如果id相同時，對name進行升序排列。如果想對多個列進行降序排列，需要對每個列指定desc關鍵字。

　　使用order by和limit的組合，我們能夠找到一個列中最高或者最低的值，比如這裡還用goods表做測試，先給goods表增加一個num欄位(alter table goods add num int)，並添加上對應的值，最後goods表內容為：

 

　　找出剩餘數量最多的的水果是：

　　 注意：select語句的order by子句對檢索出的資料進行排序，這個字句必須出現在select語句中的最後一條子句。至於為什麼，這個我暫時還不知道哈。。。

 

5、過濾資料

　　select語句中，資料可以根據where子句指定的過濾條件進行過濾，where子句在表名(from子句)之後給出，比如，select id, name from goods where id = 2，該語句只顯示id為2記錄的id和name。注意：如果同時使用where和order by子句，應該讓order by子句在where之後，否則會產生錯誤。

　　where子句操作符如下：

• =   等於
• <> 不等於
• != 不等於
• <  小於
• >  大於
• >= 大於等於
• between  在指定的兩個值之間(比如select * from goods where id between 1 and 2)

　　我們在建立表時，可以指定其中的列是否可以不包含值，在一個列不包含值時，其值為空白值null，select語句有一個特殊的where子句，用來檢測具有null值的列，比如：select * from goods where num is null 就把num是空值得記錄給列印出來。

 

 

6、資料過濾

　　常用的select子句在過濾資料時使用的是單一的條件，為了進行更強的過濾控制，可以下多個where子句，這些子句有兩種方式：以and子句和or子句的方式使用。

• select * from goods where id = 2 and num > 10 檢索id為2並且num大於10的記錄
• select * from goods where id = 3 or num >  15 檢索id為3並且num大於15的記錄

　　假如多個and和or語句放在一起，則優先處理and操作符，此時可以使用圓括弧來改變其優先順序。圓括弧還可以指定in操作符的條件範圍，範圍中的每個條件都可以進行匹配。

　　where子句中的not操作符只有否定它之後的任何條件這一作用。

 

7、用萬用字元進行過濾

　　使用like操作符進行通配搜尋，以便對資料進行複雜過濾。

百分比符號(%)操作符 搜尋中，%表示任何字元出現任意次數。

底線(_)萬用字元，它用來匹配單個字元而不是多個字元。

萬用字元很有用，但這是有代價的，萬用字元的搜尋處理一般比其他搜尋花費時間長，這裡有一些技巧：

• 不要過度使用萬用字元，如果其他動作符能達到同樣的目的，就應該使用其他動作符。
• 在確實需要使用萬用字元時，除非絕對必要，否則不要把它們用在搜尋模式的開始處，把萬用字元放在開始處，搜尋起來是最慢的。
• 注意萬用字元位置，位置不對可能不會返回想要的結果。

 

 8、Regex

　　Regex的作用是匹配文本，將一個模式(Regex)與一個文本串進行比較。mysql的where子句對Regex提供了初步支援，允許你指定Regex ，過濾select檢索出的資料。先看一下表記錄：

• select * from goods where name regexp '香' order by num desc 檢索出name中有'香'的所有記錄
• select * from goods where name regexp '香.' 檢索出name中有'香'的所有記錄，'.'表示匹配任意一個字元
• select * from goods where name regexp '香|瓜' 檢索處name中有'香'或者'瓜'的所有記錄

 

　　如果記錄匹配Regex，則就會被檢索出來，使用下面Regex重複元字元可以進行更強的控制。

• *   匹配0個或多個
• +   匹配1個或多個(等於{1,})
• ?   匹配0個或1個(等於{0,1})
• {n} 指定書目的匹配
• {n,} 不少於指定資料的匹配
• {n,m} 匹配指定資料的範圍(m不超過255)
• ^  文本的開始
• $  文本的結束
• [[:<:]]  詞的開始
• [[:>:]]  詞的結束

　　注意：regexp和like作用類似，regexp和like不同之處在於，like匹配整個串而regexp匹配子串，利用定位器，通過'^'開始每個運算式，用'$'結束每個運算式，可以使regexp的作用和like一樣。

 

9、建立計算欄位

　　拼接將值串連到一起構成單個值，在mysql的select語句中，可使用concat()函數來拼接兩個列。

　　從中我們可以看到新的計算資料行的名字只是一個值，我們可以使用別名來使得它更有意義。同時也可以對檢索出的資料進行算術運算，加減乘除都是支援的。

 

 

10、使用資料處理函數

 

常用的文本處理函數

函數	說明
left()	返回串左邊的字
length()	返回串的長度
locate()	找出串的一個子串
lower()	將串轉換為小寫
ltrim()	去掉串左邊的空格
right()	返回串右邊的字元
rtrim()	返回串右邊的字元
soundex()	返回串的soundex值
substring()	返回子串的字元
upper()	將串轉換為大寫

 

11、摘要資料

 　　經常需要摘要資料而不是把它們檢索出來，為此mysql提供了專門的函數，以便分析和報表產生。常用的例子有：確定表中行數、擷取表中行組的和、找出表列的最大值(最小值或平均值)。聚集合函式運行在行組上，計算和放回單個值得函數，mysql提供了5個聚集合函式，這些函數一般比自己的用戶端應用程式中計算要快得多。

• AVG()   返回某列的平均值
• COUNT() 返回某列的行數
• MAX()   返回某列的最大值
• MIN()   返回某列的最小值
• SUM()   返回某列之和

 　　使用關鍵字distinct使得只選擇不同num的記錄來參與計算。

 

12、分組資料

　　sql聚集合函式可以用來摘要資料，這使得我們能夠對行計數、計算平均值、擷取最大最小值不用檢索所有資料。而建立分組允許把資料分為多個邏輯組，以便對每個組進行聚集計算。其涉及到group by子句和having子句。

 

 在使用group by子句前，需要知道一些重要的規定：

• group by子句可以包含任意數目的列，這使得能對分組進行嵌套，為資料分組提供更細緻的控制。
• 如果在group by子句中嵌套了分組，資料將在最後規定的分組上匯總。換句話說，在建立分組時，指定的所有列都一起計算。
• group by子句中列出的每個列都必須是檢索列或有效運算式，但不能是聚集合函式。 
• 如果分組列中具有null值，則將null作為一個分組返回，如果有多個null值，它們將分為一組。
• group by子句必須出現在where子句後，order by子句前。 

　　除了能用group by分組資料外，mysql還允許過濾分組，規定包括哪些分組，排除哪些分組。過濾分組需使用having子句，因為where過濾的是行而不是分組。注意：where是在資料分組前進行過濾，having是在資料分組後進行過濾。

　　group by和order by經常完成相同的工作，但是二者是非常不同的：

order by	group by
排序產生的輸出	分組行，但輸出可能不是分組的順序
任意列都可以使用(甚至非選擇的列)	只可能使用選擇列或運算式列，而且必須使用每個挑選清單達式
不一定需要	如果與聚集合函式一起使用列（或運算式），則必須使用

　　下面是select語句中子句的順序，以在select中使用時必須遵循的次序為順序。

子句	說明	是否必須使用
select	要返回的列或運算式	是
from	從中檢索資料的表	僅再從表選擇資料時使用
where	行級過濾	否
group by	分組說明	僅在按組計算聚集時使用
having	組級過濾	否
order by	輸出排序次序	否
limit	要檢索的行數	否

 

13、使用子查詢

　　什麼是子查詢呢？子查詢就是嵌套在其他查詢中的查詢， 在where子句中使用子查詢，應該保證select語句具有和where子句中相同數目的列，通常，子查詢將返回單個列並且與單個列匹配。

 

14、連接表

　　外鍵為某一個表中的一列，它包含另一個表的主索引值，定義了兩個表的關係。如果資料存放區在多個表中，使用串連可用單條select語句檢索出需要的資料。應該保證所有的連連接都有where子句，否則mysql將返回比想要的多的多的資料，因為此時檢索出的行數目是第一個表行數乘以第二個表行數。

　　連接是SQL中最重要最強大的特性。關係表的設計就是要保證把資訊分解成多個表，一類資料一個表。各表通過某些常用的值(即關係設計中的關係relational)互相關聯。

SELECT vend_name, prod_name, prod_price FROM vecdors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name; 建立串連表

      使用WHERE子句作為過濾條件，它只包含匹配給定給定條件的行。沒有WHERE子句，第一個表中的每個行將於第二個表中的每個行配對，而不管它們邏輯上是否可以配對在一起。SQL對一條SELECT語句中可以連接的表的數目沒有限制。

      基於兩個表之間相等測試的聯結稱為內部聯結。其實，對於這種連接可以使用稍微不同的文法來明確指定連接的類型，下面的SELECT語句返回與前面例子完全相同的資料。

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

 

15、建立進階連接

　　除了可以給列名和計算欄位其別名外，還可以給表起別名。

　　除了使用內部聯結(或稱為等值聯結)的簡單連結，還可以使用自連接、自然連接、外部連結。

• 自連接：可以在單條SELECT語句中不止一次引用相同的表。
• 自然連接：無論何時對錶進行連接，應該至少有一個列出現在不止一個表中(被連接的列)，標準的連接(內部聯結)返回所有資料，甚至相同的列多次出現。自然連接排除多次出現，使每個列只返回一次。
• 外部連接：許多連接將一個表中的行與另一個表中的行相關聯，但有時候會需要包含沒有關聯線的那些行。

 

• select * from persion as haha; 使用表別名
• select proid_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = ‘DTNTR’); 找到生產ID為DTNTR的物品的供應商，然後找到這個供應商生產的其他物品。這種方式使用了子查詢，下面是使用自連接的操作:
• SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = ‘DTNTR’;
• SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id; 這是內部聯結方式，下面是外部連接方式
• SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

 

16、組合查詢

　　利用union操作符和組合多條SQL查詢，讓給出的多條select語句結果組合成單個結果集。注意，union必須有2條或者2條以上的select語句組成，union中每個查詢必須包含相同的列、運算式或聚集合函式（不過每個列不需要以相同的次序列出）。union會自動去除重複的行，這個是預設的行為，如果不想這樣，使用union all而不是使用union。

　　如何對union語句的輸出進行排序呢？使用order by子句時，必須放在union最後一條select語句之後，對於結果集，只能有一種定序，所以不允許使用功能多條order by子句。

 

17、插入資料

　　插入語句insert一般會有產生輸出，一般只會列印影響的行數。insert時如果不提供列名，則必須給每個表列提供一個值，如果提供列名，則必須對每個列出的列給出一個值，否則報錯。

插入一個完整的行：

 

插入多個行：

 

18、更新和刪除資料

　　分別使用update語句和delete語句來進行更新和刪除資料操作。先把goods表中資料清除掉一部分，goods表資料如下：

• update goods set num = 1 where name = '蘋果'; 此時對應蘋果的num變為1
• delete from goods where num = 7; 刪除num為7的記錄，也就是name為橘子的記錄。

注意，這裡有一些使用update和delete語句的一些注意事項：

• 除非確實打算更新和刪除每一行，否則絕對不要使用不帶where子句的update或delete語句。
• 保證每個表都有主鍵，儘可能像where子句那樣使用它。
• 在對update或delete語句使用where子句前，應該先用select進行測試，保證過濾的是正確的記錄，以防止編寫的where子句不正確。
• mysql沒有撤銷功能，應小心使用update或delete功能。

 

19、建立和操縱表

 　　利用create table建立表，必須給出表的名字，在關鍵字create table只有給出；表列的名字和定義，用逗號隔開。

create table vendors (    vend_id int not null auto_increment,    vend_name char(50) not null,    vend_address char(50) null default '*',    vend_city char(40) null,    primary key (vend_id)) engine=InnoDB;

　　這條語句建立一個vendors表，vend_id和vend_name是必須的，指定為not null，其他的為非必須的，指定為null，null為預設設定。auto_increment關鍵字告訴mysql，本列每當增加一行時自動增量，可以保證該列值唯一。每個表只允許有一個auto_increment列，而且它必須被索引(如，通過使它為主鍵)。default為預設值。

　　注意，主鍵必須唯一，表中的每個行必須具有唯一的主鍵，如果主鍵使用單個列，則它的值必須唯一；如果使用多個列，則這些列的組合必須唯一。外鍵不能垮跨越引擎。mysql內部打包了多種引擎，以下幾個是需要知道的引擎：

• InnoDB是一個可靠的交易處理引擎，它不支援全文本搜尋。
• MEMORY在功能等同於MyISAM，但由於資料存放區在記憶體，所有速度很快（特別適合用暫存資料表）。
• MyISAMysql是一個效能極高的引擎，它支援全文本搜尋，但不支援交易處理。

　　使用alter table語句更新表結構。

 

　　刪除表（不是其內容，而是整個表）使用drop語句即可。drop table table_name; 這條語句刪除table_name表，只要它存在，注意，刪除表時沒有確認，也不能撤銷。

　　使用rename table語句重新命名表。

 

20、視圖

　　視圖是虛擬表，與包含資料的表不一樣，視圖只包含使用時動態檢索資料的查詢。視圖僅僅是用來查看儲存在別處資料的一種設施，視圖本身不包含資料，因此它們返回的資料是從其他表中檢索出來的。視圖提供了一種MySQL的SELECT語句層次的封裝，可用來簡化資料處理以及重新格式化基礎資料或保護基礎資料。

使用視圖的常見應用：

• 重用SQL語句
• 簡化複雜的SQL操作，在編寫查詢後，可以方便地重用它而不必知道具體的查詢細節
• 使用表的組成部分而不是整個表
• 保護資料，授予客戶表的特定部分存取權限而不是全部
• 更改資料格式和表示，視圖可返回與底層表的表示和格式不同的資料

　　使用視圖簡化複雜的連接，視圖使用create view語句來建立，使用show create view viewname來查看建立視圖的語句；用drop刪除視圖，文法為drop view viewname。

 

 

　　通常，視圖是可更新的（可以對它們使用insert、update和delete），更新一個視圖將更新其基表，因為視圖本身是沒有任何資料的。但是，並非所有的視圖是可更新的，如果mysql不能正確的確定被更新的基資料，則不能被更新，即如果有一下操作，視圖不允許更新：分組、連接、子查詢、並、聚集合函式、distinct等。

參考

　　1、MySQL必知必會 

　　2、高效能MySQL 

本文永久更新連結地址：