1、回顧字段類型(列類型): 數(shù)值型, 時間日期型和字符串類型數(shù)值型: 整型和小數(shù)型(浮點型和定點型)時間日期型: datetime, date,time,timestamp, year字符串類型: 定長, 變長, 文件字符串(text 和 blob), 枚舉和集合長度: 65535 個字節(jié), varchar 達(dá)不到理論長度, NULL 占用一個字節(jié), text 文本不占用長度(但是本身占據(jù)十個字節(jié))字段屬性: 空屬性, 列描述, 默認(rèn)值字段屬性主鍵, 唯一鍵和自增長.主鍵主鍵: primary key,主要的鍵. 一張表只能有一個字段可以使用對應(yīng)的鍵, 用來唯一的約束該字段里面的數(shù)據(jù), 不能重復(fù)

2、: 這種稱之為主鍵.一張表只能有最多一個主鍵.增加主鍵SQL 操作中有多種方式可以給表增加主鍵: 大體分為三種.方案 1: 在創(chuàng)建表的時候,直接在字段之后,跟 primary key 關(guān)鍵字(主鍵本身不允許為空)優(yōu)點: 非常直接; 缺點: 只能使用一個字段作為主鍵方案 2: 在創(chuàng)建表的時候, 在所有的字段之后, 使用 primary key(主鍵字段列表)來創(chuàng)建主鍵(如果有多個字段作為主鍵,可以是復(fù)合主鍵)方案 3: 當(dāng)表已經(jīng)創(chuàng)建好之后, 額外追加主鍵:可以通過修改表字段屬性,也可以直接追加.Alter table表名add primary key(字段列表);前提: 表中字段對應(yīng)的數(shù)據(jù)本身是

3、獨立的(不重復(fù))主鍵約束主鍵對應(yīng)的字段中的數(shù)據(jù)不允許重復(fù):一旦重復(fù),數(shù)據(jù)操作失敗(增和改)更新主鍵 & 刪除主鍵沒有辦法更新主鍵: 主鍵必須先刪除,才能增加.Alter table 表名 drop primary key;主鍵分類在實際創(chuàng)建表的過程中, 很少使用真實業(yè)務(wù)數(shù)據(jù)作為主鍵字段(業(yè)務(wù)主鍵,如學(xué)號,課程號);大部分的時候是使用邏輯性的字段(字段沒有業(yè)務(wù)含義,值是什么都沒有關(guān)系), 將這種字段主鍵稱之為邏輯主鍵.Create table my_student(Idprimary key auto_increment comment 邏輯主鍵: 自增長,- 邏輯主鍵Number char(1

4、0) not nullcomment 學(xué)號, Name varchar(10) not null)自動增長自增長: 當(dāng)對應(yīng)的字段,不給值,或者說給默認(rèn)值,或者給 NULL 的時候, 會自動的被系統(tǒng)觸發(fā),系統(tǒng)會從當(dāng)前字段中已有的最大值再進(jìn)行+1 操作,得到一個新的在不同的字段.自增長通常是跟主鍵搭配.新增自增長自增長特點: auto_increment1.任何一個字段要做自增長必須前提是本身是一個索引(key 一欄有值)2.自增長字段必須是數(shù)字(整型)3.一張表最多只能有一個自增長自增長使用當(dāng)自增長被給定的值為 NULL 或者默認(rèn)值的時候會觸發(fā)自動增長.自增長如果對應(yīng)的字段輸入了值,那么自增長失

5、效: 但是下一次還是能夠正確的自增長(從最大值+1)如何確定下一次是什么自增長呢? 可以通過查看表創(chuàng)建語句看到.修改自增長自增長如果是涉及到字段改變: 必須先刪除自增長,后增加(一張表只能有一個自增長)修改當(dāng)前自增長已經(jīng)存在的值: 修改只能比當(dāng)前已有的自增長的最大值大,不能小(效)Alter table 表名 auto_increment= 值;向上修改可以思考: 為什么自增長是從 1 開始?為什么每次都是自增 1 呢?所有系統(tǒng)的變現(xiàn)(如字符集,校對集)都是由系統(tǒng)內(nèi)部的變量進(jìn)行控制的.查看自增長對應(yīng)的變量: show variables like auto_increment%;可以修改變量實

6、現(xiàn)不同的效果: 修改是對整個數(shù)據(jù)修改,而不是單張表: (修改是會話級)Set auto_increment_increment = 5; -一次自增 5測試效果: 自動使用自增長刪除自增長自增長是字段的一個屬性: 可以通過 modify 來進(jìn)行修改(保證字段沒有 auto_increment 即可)Alter table 表名 modify 字段 類型;唯一鍵一張表往往有很多字段需要具有唯一性,數(shù)據(jù)不能重復(fù): 但是一只能有一個主鍵: 唯一鍵(unique key)就可以解決表中有多個字段需要唯一性約束的問題.唯一鍵的本質(zhì)與主鍵差不多: 唯一鍵默認(rèn)的允許自動為空,而且可以多個為空(空字段不參與唯

7、一性比較)增加唯一鍵基本與主鍵差不多: 三種方案方案 1:在創(chuàng)建表的時候,字段之后直接跟unique/ unique key方案 2:在所有的字段之后增加 unique key(字段列表); - 復(fù)合唯一鍵方案 3:在創(chuàng)建表之后增加唯一鍵唯一鍵約束唯一鍵與主鍵本質(zhì)相同:唯一的區(qū)別就是唯一鍵默認(rèn)允許為空,而且是多個為空.如果唯一鍵也不允許為空: 與主鍵的約束作用是一致的.更新唯一鍵 & 刪除唯一鍵更新唯一鍵: 先刪除后新增(唯一鍵可以有多個: 可以不刪除).刪除唯一鍵Alter tableAlter tabledrop unique key; - 錯誤: 唯一鍵有多個表名表名drop index

8、索引名字; -唯一鍵默認(rèn)的使用字段名作為索引名字索引幾乎所有的索引都是建立在字段之上.索引: 系統(tǒng)根據(jù)某種算法, 將已有的數(shù)據(jù)(未來可能新增的數(shù)據(jù)),單獨建立一個文件:夠?qū)崿F(xiàn)快速的匹配數(shù)據(jù), 并且能夠快速的找到對應(yīng)表中的.文件能索引的意義1.2.查詢數(shù)據(jù)的效率約束數(shù)據(jù)的有效性(唯一性等)增加索引的前提條件: 索引本身會產(chǎn)生索引文件(有時候有可能比數(shù)據(jù)文件還大) ,會非常耗費磁盤空間.如果某個字段需要作為查詢的條件經(jīng)常使用, 那么可以使用索引(一定會想辦法增加);如果某個字段需要進(jìn)行數(shù)據(jù)的有效性約束, 也可能使用索引(主鍵,唯一鍵)中提供了多種索引 主鍵索引: primary key唯一索引:

9、unique key1.2.3.4.全文索引: fulltext index普通索引: index全文索引: 針對文章內(nèi)部的關(guān)鍵字進(jìn)行索引全文索引最大的問題: 在于如何確定關(guān)鍵字英文很容易: 英文單詞與單詞之間有空格中文很難: 沒有空格, 而且中文可以各種隨意組合(分詞: sphinx)關(guān)系將實體與實體的關(guān)系, 反應(yīng)到最終數(shù)據(jù)庫表的設(shè)計上來: 將關(guān)系分成三種: (多對一)和多對多.所有的關(guān)系都是指的表與表之間的關(guān)系.一對一, 一對多一對一一對一: 一張表的一條一定只能與另外一張表的一條進(jìn)行對應(yīng); 反之亦然.學(xué)生表:,身高,體重,狀況, 籍貫, 家庭住址,緊急聯(lián)系人表設(shè)計成以上這種形式: 符合要

10、求. 其中,身高,體重屬于常用數(shù)據(jù); 但是,籍貫,住址和聯(lián)系人屬于不常用數(shù)據(jù). 如果每次查詢都是查詢所有數(shù)據(jù),不常用的數(shù)據(jù)就會影響效率, 實際又不用.解決方案: 將常用的和不常用的信息分離常用信息表,分成兩張表不常用信息表: 保證不常用信息與常用信息一定能夠?qū)?yīng)上: 找一個具有唯一性(確定的字段來共同連接兩張表)Id(P)籍貫住址聯(lián)系人21Id(P)體重身高1Id(P)體重身高籍貫住址聯(lián)系人一個常用表中的一條: 永遠(yuǎn)只能在一張不常用表中匹配一條;反過來,一個不常用表在常用表中也只能匹配一條: 一對一的關(guān)系中的一條一對多一對多: 一的一條有一條可以對應(yīng)另外一的多條; 但是返回過, 另外一張表只能

11、對應(yīng)第一張表的一條. 這種關(guān)系就是一對多或者多對一.母親與孩子的關(guān)系:表母親,孩子兩個實體孩子表以上關(guān)系:到一個(也有可能是一條); 但是一個孩子只能找一個可以在孩子表中找到多條: 是一種典型的一對多的關(guān)系.但是以上設(shè)計: 解決了實體的設(shè)計表問題, 但是沒有解決關(guān)系問題: 孩子找不出不到孩子.也找解決方案: 在某一加一個字段指向增加一個字段,能夠找到另外一張表的中: 應(yīng)該在孩子表中增表: 因為孩子表的只能匹配到一條表的.表孩子表ID(P)名字ID表主鍵ID(P)名字ID(P)名字ID(P)名字多對多多對多: 一(A)的一條能夠?qū)?yīng)另外一張表(B)中的多條: 多對多的關(guān)系; 同時 B 表中的一條

12、也能對應(yīng) A 表中的多條老師教學(xué): 老師和學(xué)生老師表學(xué)生表以上設(shè)計方案: 實現(xiàn)了實體的設(shè)計, 但是沒有實體的關(guān)系.一個老師教過多個學(xué)生; 一個學(xué)生也被多個老師教過.解決方案: 在學(xué)生表中增加老師字段: 不管在哪段要保存多個數(shù)據(jù), 而且是與其他表有關(guān)系的字段,增加字段, 都會出現(xiàn)一個問題: 該字不符合表設(shè)計規(guī)范:增加一張新表:專門兩張表之間的關(guān)系老師表學(xué)生表中間關(guān)系表: 老師與學(xué)生的關(guān)系增加中間表之后: 中間表與老師表形成了一對多的關(guān)系:而且中間表是多表,了能夠唯一找到一表的關(guān)系; 同樣的,學(xué)生表與中間表也是一個一對多的關(guān)系: 一對多的關(guān)系可以匹配到關(guān)聯(lián)表之間的數(shù)據(jù).IDT_ID(老師)S_ID

13、(學(xué)生)1112123214S_ID(P)1男2女T_ID(P)1A男2B女S_ID(P)1男2女T_ID(P)1A男2B女學(xué)生找老師: 找出學(xué)生 id -老師找學(xué)生: 找出老師 id -中間表尋找匹配(多條) - 老師表匹配(一條)中間表尋找匹配(多條) - 學(xué)生表匹配(一條)范式范式: Normal Format, 是一種離散數(shù)學(xué)中的知識, 是為了解決一種數(shù)據(jù)的保存數(shù)據(jù)的之后, 凡是能夠通過關(guān)系尋找出來的數(shù)據(jù),堅決不再重復(fù)為了減少數(shù)據(jù)的冗余.與優(yōu)化的問題: 終極目標(biāo)是范式: 是一種分層結(jié)構(gòu)的規(guī)范, 分為六層: 每一次層都比上一層更加嚴(yán)格:范式,前提是滿足上一層范式.若要滿足下一層六層范式:

14、 1NF,2NF,3NF.6NF, 1NF 是最底層,要求最低;6NF 最,最嚴(yán)格.屬于關(guān)系型數(shù)據(jù)庫: 有空間浪費: 也是致力于節(jié)省空間: 與范式所有解決的問題不謀而合: 在設(shè)計數(shù)據(jù)庫的時候, 會利用到范式來指導(dǎo)設(shè)計.但是數(shù)據(jù)庫不單是要解決空間問題,要保證效率問題: 范式只為解決空間問題, 所以數(shù)據(jù)庫的設(shè)計又不可能完全按照范式的要求實現(xiàn): 一般情況下,只有前三種范式需要滿足.范式在數(shù)據(jù)庫的設(shè)計當(dāng)中是有指導(dǎo)意義: 但是不是強(qiáng)制規(guī)范.1NF第一范式: 在設(shè)計表數(shù)據(jù)的時候, 如果表中設(shè)計的字段的數(shù)據(jù),在取出來使用之前還需要額外的處理(拆分),那么說表的設(shè)計不滿足第一范式: 第一范式要求字段的數(shù)據(jù)具有

15、原子性: 不可再分.講師代課表講師班級教室代課時間代間（開始，結(jié)束）上表設(shè)計不存在問題: 但是如果需求是將數(shù)據(jù)查出來之后,要求顯示一個老師從什么時候開始上課,到什么時候節(jié)課: 需要將代間進(jìn)行拆分: 不符合 1NF, 數(shù)據(jù)不具有原子性, 可以再拆分.解決方案: 將代間拆分成兩個字段就解決問題.2NF第二范式: 在數(shù)據(jù)表設(shè)計的過程中,如果有復(fù)合主鍵(多字段主鍵), 且表中有字段并不是由整個主鍵來確定, 而是依賴主鍵中的某個字段(主鍵的部分): 存在字段依賴主鍵的部分的問題,稱之為部分依賴: 第二范式就是要解決表設(shè)計不允許出現(xiàn)部分依賴.講師帶課表朱元璋朱元璋李世民MaleMaleMaleD302B2

16、06B20630天30天15天2014-02-27,2014-05-052014-03-21,2014-05-302014-06-01,2014-06-200226 0320 0320 以上表中: 因為講師沒有辦法作為獨立主鍵, 需要結(jié)合班級才能作為主鍵(復(fù)合主鍵: 一個老師在一個班永遠(yuǎn)只帶一個階段的課): 代間,開始和結(jié)束字段都與當(dāng)前的代課主鍵(講師和班級): 但是并不依賴班級, 教室不依賴講師:只依賴講師,教室只依賴班級:出現(xiàn)了和教室依賴主鍵中的一部分: 部分依賴.不符合第二范式.解決方案 1:解決方案 2:可以將與講師單獨成表, 班級與教室也單獨成表.取消復(fù)合主鍵, 使用邏輯主鍵ID =

17、 講師 + 班級(業(yè)務(wù)邏輯約束: 復(fù)合唯一鍵)3NF要滿足第三范式,必須滿足第二范式.第三范式: 理論上講,應(yīng)該一的所有字段都應(yīng)該直接依賴主鍵(邏輯主鍵:代表的是業(yè)務(wù)主鍵), 如果表設(shè)計中存在一個字段, 并不直接依賴主鍵,而是通過某個非主鍵字段依賴,最終實現(xiàn)依賴主鍵: 把這種不是直接依賴主鍵,而是依賴非主鍵字段的依賴關(guān)系稱之為傳遞依賴. 第三范式就是要解決傳遞依賴的問題.講師帶課表以上設(shè)計方案中:教室都存在傳遞依賴.依賴講師存在, 講師依賴主鍵; 教室依賴班級,班級依賴主鍵:和解決方案: 將存在傳遞依賴的字段,以及依賴的字段本身單獨取出,形成一個單獨的表, 然后在需要對應(yīng)的信息的時候,講師代課

18、表使用對應(yīng)的實體表的主鍵加進(jìn)來.講師表班級表講師表:ID =講師班級表中: ID =班級逆規(guī)范化有時候, 在設(shè)計表的時候,如果一上講, 的確可以獲取到想要的數(shù)據(jù),有幾個字段是需要從另外的表中去獲取信息. 理論但是就是效率低一點. 會刻意的在某些表中,不去保存另外表的主鍵(邏輯主鍵), 而是直接保存想要的數(shù)據(jù)信息: 這樣一來,在查詢數(shù)據(jù)的時候, 一張表可以直接提供數(shù)據(jù), 而不需要多表查詢(效率低), 但是會導(dǎo)致數(shù)據(jù)冗余增加.如講師代課信息表逆規(guī)范化: 磁盤利用率與效率的對抗數(shù)據(jù)高級操作數(shù)據(jù)操作: 增刪改查新增數(shù)據(jù)基本語法Inserto表名 (字段列表) values (值列表);在數(shù)據(jù)的時候,

19、假設(shè)主鍵對應(yīng)的值已經(jīng)存在:一定會失敗!主鍵當(dāng)主鍵存在的時候(Duplicate key),可以選擇性的進(jìn)行處理: 更新和替換: 更新操作o 表名(字段列表:包含主鍵) values(值列表) on duplicate key update主鍵Insert字段=新值;主鍵Replace: 替換o 表名 (字段列表:包含主鍵) values(值列表);蠕蟲蠕蟲: 從已有的數(shù)據(jù)中去獲取數(shù)據(jù),然后將數(shù)據(jù)又進(jìn)行新增操作:數(shù)據(jù)成倍的增加.表創(chuàng)建高級操作: 從已有表創(chuàng)建新表(Create table 表名 like 數(shù)據(jù)庫.表名;表結(jié)構(gòu)): 先查出數(shù)據(jù), 然后將查出的數(shù)據(jù)新增一遍o 表名(字段列表) sel

20、ect 字段列表/* from 數(shù)據(jù)表名;蠕蟲Insert蠕蟲1.2.的意義從已有表拷貝數(shù)據(jù)到新表中可以迅速的讓表中的數(shù)據(jù)膨脹到一定的數(shù)量級: 測試表的壓力以及效率更新數(shù)據(jù)基本語法Update 表名 set 字段 = 值 where 條件;高級新增語法Update 表名 set 字段 = 值 where 條件 limit 更新數(shù)量;刪除數(shù)據(jù)與更新類似: 可以通過 limit 來限制數(shù)量Delete from 表名 where 條件 limit 數(shù)量;刪除: 如果表中存在主鍵自增長,那么當(dāng)刪除之后, 自增長不會還原思路: 數(shù)據(jù)的刪除是不會改變表結(jié)構(gòu), 只能刪除表后重建表Truncate 表名;-

21、 先刪除改變,后新增改變查詢數(shù)據(jù)基本語法Select 字段列表/* from 表名 where 條件;完整語法Select select 選項 字段列表字段別名/* from 數(shù)據(jù)源 where 條件子句 group by 子句ing 子句 order by 子句 limit 子句;Select 選項Select 選項: select 對查出來的結(jié)果的處理方式All: 默認(rèn)的,保留所有的結(jié)果Distinct: 去重, 查出來的結(jié)果,將重復(fù)給去除(所有字段都相同)字段別名字段別名: 當(dāng)數(shù)據(jù)進(jìn)行查詢出來的時候, 有時候名字并一定就滿足需求(多表查詢的時候,會有同名字段). 需要對字段名進(jìn)行重命名:

22、 別名語法字段名 as 別名;數(shù)據(jù)源數(shù)據(jù)源: 數(shù)據(jù)的來源, 關(guān)系型數(shù)據(jù)庫的來源都是數(shù)據(jù)表: 本質(zhì)上只要保證數(shù)據(jù)類似二維表,最終都可以作為數(shù)據(jù)源.數(shù)據(jù)源分為多種: 單表數(shù)據(jù)源, 多表數(shù)據(jù)源, 查詢語句單表數(shù)據(jù)源: select * from 表名;多表數(shù)據(jù)源: select* from表名 1,表名 2.;從一取出一條,去另外一積(交叉連接):匹配所有,而且全部保留:(數(shù)和字段數(shù)),將這種結(jié)果成為:積沒什么卵用, 所以應(yīng)該盡量避免.子查詢: 數(shù)據(jù)的來源是一條查詢語句(查詢語句的結(jié)果是二維表)Select * from (select語句) as表名;Where 子句Where 子句: 用來判斷數(shù)

23、據(jù),篩選數(shù)據(jù).Where 子句返回結(jié)果: 0 或者 1, 0 代表 false,1 代表 true.判斷條件:比較運(yùn)算符: , =, = ,!= , =, like, bet邏輯運(yùn)算符: &(and), |(or), !(not)n and, in/not inWhere 原理: where 是唯一一個直接從磁盤獲取數(shù)據(jù)的時候就開始判斷的條件: 從磁盤取出一條, 開始進(jìn)行where 判斷: 判斷的結(jié)果如果成立保存到內(nèi)存;如果失敗直接放棄.條件查詢 1:要求找出學(xué)生 id 為 1 或者 3 或者 5 的學(xué)生條件查詢 2:查出區(qū)間落在 180,190 身高之間的學(xué)生:Betn 本身是閉區(qū)間; be

24、tn 左邊的值必須小于或者等于右邊的值Group by 子句Group by:分組的意思, 根據(jù)某個字段進(jìn)行分組(相同的放一組,不同的分到不同的組)基本語法: groupby 字段名;分組的意思: 是為了統(tǒng)計數(shù)據(jù)(按組統(tǒng)計: 按分組字段進(jìn)行數(shù)據(jù)統(tǒng)計)SQL 提供了一系列統(tǒng)計函數(shù)Count():Max():數(shù): 每一組有多少統(tǒng)計分組后的統(tǒng)計每組中最大的值Min(): 統(tǒng)計最小值A(chǔ)vg(): 統(tǒng)計平均值Sum(): 統(tǒng)計和Count 函數(shù): 里面可以使用兩種參數(shù): *代表統(tǒng)計統(tǒng)計),字段名代表統(tǒng)計對應(yīng)的字段(NULL 不分組會自動排序: 根據(jù)分組字段:默認(rèn)升序Group by 字段 asc|desc;- 對分組的結(jié)果然后合并之后的整個結(jié)果進(jìn)行排序多字段分組: 先根據(jù)一個字段進(jìn)行分組,然后對分組后的結(jié)果再次按照其他字段進(jìn)行分組有一個函數(shù): 可以對分組的結(jié)果中的某個字段進(jìn)行字符串連接(保留該組所有的某個字段):group_concat(字段)