mysql基礎2

標籤：產生   建立表   key   注釋   別名   values   處理   屬性   操作   

一、列屬性

 所謂的列屬性，就是在定義一個列（欄位）的時候對該列設定的額外的資訊或約束！

null和not null

預設情況下，欄位都允許為空白（預設值為null），如果加上not null，意思就是這個欄位不可為空，所以，not null也叫作非空約束！

當我們沒有給一個not null屬性的欄位插入值的時候，系統會首先判斷該欄位有沒有一個預設值，如果沒有，就報錯！

default

自訂預設值屬性，也叫作自訂預設值約束，通常就是配合not null一起使用！

也就是說，在給一個欄位加上not null屬性的時候，也往往給它設定一個default屬性，這樣一來，如果給這個欄位插入值的時候，就以插入的值為標準，如果沒有給該欄位插入值，就以預設值為標準！

 

在插入資料的時候，也可以直接插入default關鍵字，注意這裡的關鍵字不能用引號括起來！

primary key

簡稱PK，也叫作主鍵屬性或者主鍵約束！主要的鍵，主要的欄位！

主鍵的概念：

可以唯一標識某條記錄的欄位或欄位的組合（組合主鍵）！

設定主鍵有兩種方式：

 第一種：在定義一個欄位的時候直接在後面進行設定primary key

第二種：定義完欄位後再定義主鍵

 

 效果是一樣的，但是如果定義組合主鍵的話，只能用第二種方式：

注意：

組合主鍵的含義是兩個或多個欄位組合在一起形成一個主鍵！而不是所有的欄位都是主鍵，因為主鍵只能有一個！

unique key

也叫作唯一鍵屬性或唯一鍵約束！增加該屬性後，該欄位的值就不能重複！

定義的方式和主鍵是類似的。

也就是說，唯一鍵可以有很多個！

另外，唯一鍵和主鍵的一個區別是：唯一鍵允許為空白，但是主鍵不可為空！

unique key 可以簡寫成unique

auto_increment

自增長屬性，或者自增長約束！

作用是每次插入記錄的時候，自動的為某個欄位的值加1（基於上一個記錄）

注意：

使用這個屬性有兩個條件：

1，  該欄位類型必須為整型

2，  該欄位上必須存在索引（後面講，主鍵也叫作主鍵索引，唯一鍵也叫作唯一鍵索引）

每次插入主鍵欄位的時候，就可以直接插入null！這裡的插入null不是真正的將null這個值插入到主鍵（主鍵不允許為null），而是告訴系統這裡開啟自動載入機制！預設從1開始！

如果想從100開始自動成長，需要增加表選項：auto_increment 自動成長初始值

如果想重設自動成長，可以使用truncate 表名文法！

comment

是專門為列做注釋的（描述的），與其他的注釋符不同之處在於，這裡的注釋內容屬於列定義的一部分：

二、外鍵

foreign key 也叫作外鍵屬性或外鍵約束

外鍵約束主要體現在以下的兩個方面：

1，  增加子表記錄的時候，是否有與之對應的父表記錄！

2，  當刪除或更改父表記錄的時候，從表應該如何處理相關的記錄！

外鍵在子表上定義！

文法格式如下：

foreign key(子表欄位) references 父表名(父表主鍵)，應該先建立父表，再建立子表，當插入子表記錄的時候，需要在父表中有與之對應的記錄！

設定級聯操作

所謂的級聯操作，就是在操作父表的時候，會以什麼樣的形式影響到子表，也叫作關聯操作或者關聯動作！

主表操作主要體現在以下兩個方面：

 主表更新

文法形式為：on update[級聯操作]

主表刪除

文法形式為：on delete[級聯操作]

級聯操作常見的有三種形式：

cascade：同步操作，或者串聯操作！也就是當主表記錄刪除或更新的時候，從表也進行相應的刪除或更新！

set null：設定為null，也就是當主表記錄刪除或更新的時候，從表中的外鍵欄位設定為空白

restrict：拒絕主表的更新或刪除

 刪除外鍵的文法：

 alter table 表名drop foreign key 外鍵名;

注意：這裡的外鍵名並不是欄位名，一般如果在建立外鍵的時候沒有給該外鍵起一個名字，系統會自動分配一個外鍵名字！可以通過show create table語句來查看外鍵名

增加外鍵的文法：

alter table 表名 add foreign key 外鍵定義

 

注意：

1），  外鍵約束只有InnoDB儲存引擎才支援！

2），  在實際項目中，往往會用到外鍵的設計思想，但往往不會真正的從文法上進行外鍵約束，因為外鍵約束的級聯操作可能會帶來一些現實的邏輯問題！另外，使用外鍵會較低mysql的效率！

三、儲存引擎

使用者在建立表的時候，要根據實際需要合理的選擇儲存引擎，在windows下，預設的儲存引擎為InnoDB，當然，可以在資料庫的設定檔my.ini中進行修改：

修改後需要重啟MySQL伺服器！

一張資料表，是由三部分構成的：結構  資料  索引

Myisam儲存引擎是把這三個部分分開儲存！而InnoDB儲存引擎的表在資料庫目錄下只儲存表結構！而表的資料和索引其實都在同一個資料空間內

選擇儲存引擎的依據：

1，  功能：外鍵、事務支援，高並發量等

2，  效能

總體來說，就是在滿足功能的基礎上追求效能！在我們的項目中，如果一張表90%都是查詢操作，就用Myisam，一般用預設的InnoDB

四、其他資料操作蠕蟲複製

就是在已有的資料的基礎之上，將原來的資料進行複製，插入到相對應的表中！

文法規則：

insert into 表名 select *|欄位列表from 表名

注意：當一個表中的資料複製到另一個表中的時候，需要注意資料的來源要與被插入的表的欄位數量和類型要保持一致！

總結蠕蟲複製的優點：

1，  可以以最快的速度複製另外一張表的資料

2，  在短期內產生大量的資料，以測試伺服器的壓力

主鍵重複

如果在進行資料更新的時候，某條記錄的主鍵已經存在，但是又需要將最新的資料更新到該記錄中

策略一：

可以使用以下的文法：

insert into 表名[欄位列表] values(值列表) on duplicate key update 欄位1=值1，欄位2=值2……

 也就是說，當主鍵不衝突的時候，相當於一條插入語句，當主鍵有衝突的時候，相當於一條更新語句！

策略二：

如果主鍵重複，直接刪除原紀錄再插入

replace into 表名[欄位列表] values(值列表);

在插入之前進行一次判斷，判斷有沒有主鍵重複，如果沒有，跟普通的插入語句沒有區別，如果有主鍵衝突，先刪除以前的記錄，再插入新記錄！

修改資料

標準文法：

update 表名 set 欄位1=值1，欄位2=值2……where條件;

還可以加上orderby子句和limit子句：

update 表名 set 欄位1=值1，欄位2=值2……[where條件][order by 欄位名 asc|desc][limit 資料量]

刪除資料

標準文法：

delete from 表名 [where條件] [order by 欄位名 asc|desc][limit 資料量]

還有一個類似刪除功能的文法：

truncate table 表名;  或 truncate 表名;

注意：該語句不屬於DML，屬於DDL

相當於做了兩件事情：

1，  先把原表drop掉！

2，  再按以前的原表的結構重新建立一次！

五、資料查詢

select [select選項] *|欄位列表 [as 欄位別名] from 資料來源 [where子句][group by子句][having子句][order by子句][limit子句];

 以上的文法一般只是單表查詢，另外還有多表查詢，多表查詢又有聯集查詢、子查詢、串連查詢（左串連，右串連，內串連，外串連，自然串連）

注意：

1），  from後面的子句往往稱之為：五子句，也叫五子查詢！

2），  五子查詢都可以沒有，但是，如果要有，就必須按順序寫！

1、select選項和別名select選項  含義：就是系統在查詢到相關資料之後，如何顯示！

這裡的select選項有兩個值：

all：也是預設值（預設值），保留所有的查詢結果！

distinct：去重，去掉重複的查詢結果！

別名：所謂的別名，就是給欄位或其他運算式等標識符另起一個名字，基本文法如下：

欄位|運算式|表|子查詢  [as]  別名

這裡的as可以省略，但是為了增加可讀性，一般還是寫上！

2、虛擬表

查詢語句的比較完整的文法：

select [select選項] *|欄位列表 [as 欄位別名] from 資料來源 [where子句][group by子句][having子句][order by子句][limit子句];

 但是，一條真實的sql語句，有可能連欄位沒有！

典型的，select語句可以當計算機使用：

 

但是，理論上認為一條sql語句必須從一個資料來源中去擷取資料！

所以，為了保證sql語句文法的結構完整性，在Mysql中執行select語句的時候在適當的時候會自動建立一個虛擬表！這個虛擬表就是當資料來源來使用！

虛擬表的名稱：dual

3、where子句

文法：where 運算式

功能：通過限定的運算式的條件對資料進行過濾，得到我們想要的結果

流程：逐一取出每一條記錄，先通過目前記錄來計算where後面運算式的值，如果計算的結果為真（非0），就返回來記錄，如果計算的結構為假（0），則不返回記錄！相當於對所有的記錄做了一次遍曆！

where子句後面往往配合MySQL運算子一起使用（做條件判斷）

MySQL運算子  MySQL支援以下的運算子：關係運算子

<  >

<=  >=

=   !=(<>)

注意：這裡的等於是一個等號

between and

做數值範圍限定，相當於數學上的閉區間！

比如：

between A and B相當於 [A,B]       另外，between and 的前面還可以加上not，代表相反！

in和not in

文法形式：in|not in（集合）

表示某個值出現或沒出現在一個集合之中！

邏輯運算子

&&  and

||   or

！   not

where子句的其他形式空值查詢

select *|欄位列表 from 表名 where 欄位名 is [not]  null

模糊查詢

也就是帶有like關鍵字的查詢，常見的文法形式是：

select *|欄位列表from 表名 where 欄位名 [not] like ‘萬用字元字串’;

所謂的萬用字元字串，就是含有萬用字元的字串！

MySQL中的萬用字元有兩個：

_   ：代表任意的單個字元

%   ：代表任意的字元

4、group by子句   也叫作分組統計查詢語句！文法：group by 欄位1[，欄位2]……分組統計查詢的主要作用不是分組，而是統計！或者說分組的目的就是針對每一個分組進行相關的統計！

此時，就需要使用系統中的一些統計函數！

統計函數（彙總函式）

sum()：求和，就是將某個分組內的某個欄位的值全部相加

max()：求某個組內某個欄位的最大值

min()：求某個組內某個欄位的最小值

avg()：求某個組內某個欄位的平均值

count()：統計某個組內非null記錄的個數（行數），通常就是用count(*)來表示！

注意：

統計函數都是可以單獨的使用的！但是，只要使用統計函數，系統預設的就是需要分組，如果沒有group by子句，預設的就是把整個表中的資料當成一組！

多欄位分組

group by 欄位1[，欄位2]……

作用是：先根據欄位1進行分組，然後再根據欄位2進行分組！

多欄位分組的結果就是分組變多了！

回溯（su）統計

回溯統計就是向上統計！在進行分組統計的時候，往往需要做上級統計！

比如，先統計各個班的總人數，然後各個班的總人數再相加，就可以得到一個年級的總人數！

在MySQL中，其實就是在語句的後面加上with rollup即可！

5、having子句

having子句和where子句一樣，也是用來篩選資料的，通常是對group by之後的統計結果再次進行篩選！

having子句和where子句有什麼區別？

二者的比較：

1），  如果語句中只有having子句或只有where子句的時候，此時，它們的作用基本是一樣的！

2），  二者的本質區別是：where子句是把磁碟上的資料篩選到記憶體上，而having子句是把記憶體中的資料再次進行篩選！

3），  where子句的後面不能使用統計函數，而having子句可以！因為只有在記憶體中的資料才可以進行運算統計！

6、order by子句

根據某個欄位進行排序，有升序和降序！

文法形式為：

order by 欄位1[asc|desc]

預設的是asc，也就是升序！如果要降序排序，需要加上desc！

多欄位排序

order by 欄位1[asc|desc]，欄位2[asc|desc]……

比如：order by score asc,age desc

也就是說，先按分數進行升序排序，如果分數一樣的時候，再按年級進行降序排序！

7、limit子句

limit就是限制的意思，所以，limit子句的作用就是限制查詢記錄的條數！

文法

limit  offset,length

其中，offset是指位移量，預設為0，而length是指需要顯示的記錄數！

例：現在想顯示記錄的第4條到第8條    limit 3,5;

分頁原理

假如在項目中，需要使用分頁的效果，就應該使用limit子句！

比如，每頁顯示10條記錄：

第1頁：limit 0,10

第2頁：limit 10,10

第3頁：limit 20,10

如果用$pageNum代表第多少頁，用$rowsPerPage代表每頁顯示的長度

limit ($pageNum - 1)*$rowsPerPage, $rowsPerPage

 

 

 

 

mysql基礎2