我們通過這個例子來看看
[root@xuegod63~]# mysql-u root-p123456mysql>create database xuegod2;mysql>use xuegod2;mysql>create tableworker(idintnot null,namevarchar(8)not null,passvarchar(20)not null);mysql>insert into workervalues(1,'HA','123456');mysql>insert into workervalues(1,'LB',null);ERROR1048(23000):Column'pass'cannot be null #不能為 nullmysql>insert into workervalues(2,'HPC','');
注：NOT NULL 的字段是不能插入“NULL”的，只能插入“空值”卿嘲。
我們可能有這些疑問
（1） （2）為什么 not null 的效率比 null 高 （3）判斷字段不為空的時候蚣旱，到底要 select * from table where column <> ‘’ 還是要
用 select * from table where column is not null 呢蔬啡。
“空值” 和 “NULL”有什么不一樣赤炒？
空值是不占用空間的
MySQL 中的 NULL 其實是占用空間的，下面是來自于 MySQL 官方的解釋
“NULLcolumns require additional space in the row to record whether their values areNULL.For MyISAM tables,eachNULLcolumn takes one bit extra,rounded up to the nearest byte.”
#“空列需要行中的額外空間來記錄其值是否為空蹲诀。對于 MyISAM 表，每個 NULL 列需要一個額外
的位樊卓，四舍五入到最接近的字節(jié)。
比如：一個杯子阀趴，空值’'代表杯子是真空的，NULL 代表杯子中裝滿了空氣苍匆，雖然杯子看起來都是空
的刘急，但是里面是有空氣的。
對于問題 2浸踩，為什么 not null 的效率比 null 高叔汁？
NULL 其實并不是空值，而是要占用空間检碗，所以 mysql 在進行比較的時候据块，NULL 會參與字段比
較，所以對效率有一部分影響折剃。
而且索引時不會存儲 NULL 值的另假，所以如果索引的字段可以為 NULL，索引的效率會下降很多怕犁。
因為 MySQL 難以優(yōu)化引用可空列查詢边篮，它會使索引己莺、索引統(tǒng)計和值更加復雜「杲危可空列需要更多的存
儲空間凌受，還需要 MySQL 內(nèi)部進行特殊處理∷急可空列被索引后胜蛉，每條記錄都需要一個額外的字節(jié)，還能導
致 MyISAM 中固定大小的索引變成可變大小的索引--------這也是《高性能 MySQL 第二版》介紹的解
讀：“可空列需要更多的存儲空間”：需要一個額外字節(jié)作為判斷是否為 NULL 的標志位“需要 MySQL 內(nèi)
部進行特殊處理” 所以使用 not null 比 null 效率高
對于問題 3色乾，判斷字段不為空的時候誊册，到底要 select * from table where column <> ‘’ 還是
要用 select * from table where column is not null 我們舉例看看
mysql>create tabletest(col1varchar(10)not null,col2varchar(10)null)ENGINE=MyISAM;mysql>insert into testvalues('',null);mysql>insert into testvalues('1','2');mysql>insert into testvalues('','1');mysql>select*from test;
下面我分別用這兩條語句查詢看看
mysql>select*from test where col1 is not null;
mysql>select*from test where col1<>'';
為空表示不占空間，null 占用空間
為字段指定默認的值
mysql>create tabletest2(namevarchar(8)not null,deptvarchar(25)default'SOS');mysql>insert intotest2(name)values('kko');mysql>select*from test2;
總結(jié)：
如果字段沒有設定 default ,MySQL 依據(jù)這個字段是 null 還是 not null杈湾，如果可以為 null，則為
null攘须。如果不可以為 null漆撞，則報錯。
如果時間字段于宙，默認為當前時間 浮驳，插入 0 時，默認為當前時間捞魁。
如果是 enum 類型至会，默認為第一個元素。
自動增長
只能修飾 int 字段谱俭。 表明 MySQL 應該自動為該字段生成一個唯一沒有用過的數(shù)（每次在最大 ID
值的基礎上加 1奉件。
特例：如果目前最大 ID 是 34，然后刪除 34昆著，新添加的會是 35.）县貌。
對于主鍵，這是非常 有用的凑懂∶汉郏可以為每條記錄創(chuàng)建一個惟一的標識符。
mysql>create tableitems(idintnot null auto_increment primary key,labelvarchar(20)not null);mysql>insert intoitems(label)values('aaba');mysql>insert into itemsvalues(9,'aaba');
插入一個比上面數(shù)字小的值接谨，會是下面的結(jié)果
再插入一條 id 將為多少
mysql>insert intoitems(label)values('abc');mysql>select*from items;
Id 為10mysql>insert into itemsvalues(9,'adl');ERROR1062(23000):Duplicate entry'9'forkey'PRIMARY'insert intoitems(label)values('abcs');#IDmax=11max=11delete from items where label='abcs';#IDmax=10max=11insert intoitems(label)values('abcsw');#Idmax=11max=12
原因：主鍵約束唯一
清空表中所有記錄
方法一：
delete 不加 where 條件摆碉，清空所有表記錄。但是 delete 不會清零 auto_increment 值
mysql>delete from items;Query OK,5rowsaffected(0.00sec)mysql>insert intoitems(label)values("aaaa");
方法二：
刪除表中所有記錄脓豪，清 auto_increment 值巷帝。
truncate
作用： 刪除表的所有記錄，并清零 auto_increment 值扫夜。新插入的記錄從 1 開始锅睛。
語法： truncate table name埠巨；mysql>truncate table items;Query OK,0rowsaffected(0.01sec)mysql>insert into itemsvalues(null,'abv');
mysql>insert intoitems(label)values('hkuyb');mysql>select*from items;
索引是一種特殊的文件（InnoDB 數(shù)據(jù)表上的索引是表空間的一個組成部分），它們包含著對數(shù)據(jù)表
里所有記錄的引用指針现拒。更通俗的說辣垒，數(shù)據(jù)庫索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫的查詢速度印蔬。
優(yōu)點：為了加快搜索速度勋桶，減少查詢時間。
缺點：
索引是以文件存儲的侥猬。如果索引過多例驹，占磁盤空間較大。而且他影響： insert ,update ,delete
執(zhí)行時間退唠。
索引中數(shù)據(jù)必須與數(shù)據(jù)表數(shù)據(jù)同步：如果索引過多鹃锈，當表中數(shù)據(jù)更新的時候后，索引也要同步更
新瞧预，這就降低了效率屎债。
索引的類型：
普通索引
唯一性索引
主鍵索引（主索引）
復合索引
最基本的索引，不具備唯一性垢油，就是加快查詢速度
創(chuàng)建普通索引：
方法一：創(chuàng)建表時添加索引
create table 表名（
列定義
index 索引名稱 （字段）
index 索引名稱 （字段）
）
注：可以使用 key盆驹，也可以使用 index 。index 索引名稱 （字段）滩愁，索引名稱躯喇，可以加也可以不
加，不加使用字段名作為索引名硝枉。
mysql>create tabledemo(idint(4),namevarchar(20),pwdvarchar(20),index(pwd));注意：index 和 key 是相同的mysql>create tabledemo1(idint(4),namevarchar(20),pwdvarchar(20),key(pwd));mysql>create tabledemo2(idint(4),namevarchar(20),pwdvarchar(20),keyindex_pwd(pwd));#加上名稱
方法二： 當表創(chuàng)建完成后廉丽，使用 alter 為表添加索引
alter table 表名 add index 索引名稱 （字段1，字段2.....）;
查看索引
mysql>desc demo;
注：如果 Key 是 MUL, 就是一般性索引妻味，該列的值可以重復, 該列是一個非唯一索引的前導列(第一
列)或者是一個唯一性索引的組成部分但是可以含有空值 NULL雅倒。就是表示是一個普通索引。
show create table xxx;?先查看建表時的索引名
我們先刪除索引
mysql> alter table demo drop key pwd;?#注意此處的 pwd 指的是索引的名稱,而不是
表中 pwd 的那個字段
再用 alter 添加
mysql>alter table demo addkey(pwd);mysql>alter table demo add keyindex_name(name);#也可以加其他字段索引
與普通索引基本相同弧可，但有一個區(qū)別：索引列的所有值都只能出現(xiàn)一次蔑匣，即必須唯一，用來約束內(nèi)
容棕诵，字段值只能出現(xiàn)一次裁良，應該加唯一索引。唯一性允許有 NULL 值<允許為空>校套。
創(chuàng)建唯一索引：
方法一：創(chuàng)建表時价脾，加唯一索引
create table 表名(列定義：unique key 索引名 （字段）;)
注意：常用在值不能重復的字段上，比如說用戶名笛匙，電話號碼侨把，身份證號犀变。
mysql>create tabledemo3(idint(4)auto_increment primary key,uNamevarchar(20),uPwdvarchar(20),uniqueindex(uName));
方法二：修改表時，加唯一索引
alter table 表名 add unique 索引名(字段);mysql>alter table demo3 drop key uName;mysql>alter table demo3 addunique(uName);
查詢數(shù)據(jù)庫秋柄，按主鍵查詢是最快的获枝，每個表只能有一個主鍵列，可以有多個普通索引列骇笔。主鍵列要求
列的所有內(nèi)容必須唯一省店，不允許為空
創(chuàng)建主鍵索引
方法一：創(chuàng)建表創(chuàng)建主鍵索引
mysql>create tabledemo5(idint(4)not null auto_increment,namevarchar(20)defaultnull,primarykey(id));mysql>desc demo5;
mysql>show create table demo5;mysql>show index from demo5 \G
方法二：創(chuàng)建表后添加<不推薦>如果生產(chǎn)的數(shù)據(jù)無法保證唯一，創(chuàng)建主鍵報錯
再添加
先刪除測試
刪除遇到這種情況是 auto_increment 的原因
mysql>alter table demo5 change id idint(4)not null;#先取消自增長mysql>alter table demo5 drop primary key;再刪除主鍵mysql>alter table demo5 change id idint(4)not null primary key auto_increment;
數(shù)據(jù)要符合主鍵約束才能成功
總結(jié)：主鍵索引笨触，唯一性索引區(qū)別：主鍵索引不能有 NULL懦傍，唯一性索引可以有空值
用存儲過程的方式插入 100 萬數(shù)據(jù)并測試
create tabletest_1(idintnot null auto_increment primary key,datavarchar(100),datevarchar(30));delimiter// #分割符create procedureprod_dba()begin declare iint;set i=0;whilei<1000000doinsert intotest_1(data,date)values('100','2020-01-01');set i=i+1;endwhile;end;//callprod_dba();//Query OK,1rowaffected(1min47.94sec)delimiter;#使用完馬上恢復默認的分割符select*from test_1 where id=100000;#用時0秒alter table test_1 change id idint(10)not null;alter table test_1 drop primary key;
有索引比沒索引的查詢時間快了 0 點幾秒，如果更復雜的表結(jié)構(gòu)查詢時間會差更多芦劣。
索引可以包含一個粗俱、兩個或更多個列。兩個或更多個列上的索引被稱作復合索引
例： 創(chuàng)建一個表存放服務器允許或拒絕的 IP 和 port虚吟，表記錄中 IP 和 port 要唯一寸认。
mysql>create tablefirewall(hostvarchar(15)not null,portsmallint(4)not null,accessenum('deny','allow')not null,primarykey(host,port));#聯(lián)合主鍵mysql>desc firewall;
mysql>insert into firewallvalues('10.96.52.46',22,'deny');mysql>insert into firewallvalues('10.96.52.46',21,'allow');mysql>insert into firewallvalues('10.96.52.46',21,'allow');ERROR1062(23000):Duplicate entry'10.96.52.46-21'forkey'PRIMARY'
插入兩個一樣就報錯，唯一
全文索引（也稱全文檢索）是目前搜索引擎使用的一種關鍵技術稍味。它能夠利用分詞技術等多種算法智能分析出文本文字中關鍵字詞的頻率及重要性废麻，然后按照一定的算法規(guī)則智能地篩選出我們想要的搜索結(jié)果荠卷。
MySQL 在數(shù)據(jù)量較大的情況下模庐，高并發(fā)連接的情況下。
select 語句 where bName like'%網(wǎng)%'
使用% _ 通配符油宜，不通過索引掂碱，直接全表掃描。
ABSUWU LIKE ‘%U_U’
數(shù)據(jù)庫壓力大慎冤。
MySQL 的解決方案：全文索引：3.2 開始支持全文索引疼燥。無法正確支持中文。
從 MySQL 5.7.6 開始 MySQL 內(nèi)置了 ngram 全文檢索插件蚁堤，用來支持中文分
全文索引只能用在 varchar text
創(chuàng)建全文索引：
方法一：創(chuàng)建表時創(chuàng)建
create table 表名（列定義醉者，fulltext key 索引名 （字段）;）
方法二：修改表時添加
alter table 表名 add fulltext 索引名 （字段）;ALTER TABLE `books` ADD FULLTEXT[索引名](`author`)
強烈注意：MySQL 自帶的全文索引只能用于數(shù)據(jù)庫引擎為 MyISAM 的數(shù)據(jù)表，如果是其他數(shù)據(jù)引
擎披诗，則全文索引不會生效撬即。
一般交給第三方軟件進行全文索引。
索引并非越多越好
數(shù)據(jù)量不大的不需要建立索引
列中的值變化不多不需要建立索引 row id
經(jīng)常排序（order by 字段）和分組（group by 字段）的列需要建立索引
select a.bTypeId,(select b.bTypeName from category b where a.bTypeId=b.bTypeId)bn,count(*)from books a group by bTypeId;
5.唯一性約束對應使用唯一性索引
table(id pri,use,name index,pass)呈队；
foreign key 就是表與表之間的某種約定的關系剥槐，由于這種關系的存在，我們能夠讓表與表之間的數(shù)據(jù)宪摧，更加的完整粒竖，關聯(lián)性更強颅崩。
關于完整性，關聯(lián)性我們舉個例子
例：
有二張表蕊苗，一張是用戶表沿后，一張是訂單表
如果我刪除了用戶表里的用戶，那么訂單表里面與這個用戶有關的數(shù)據(jù)岁歉，就成了無頭數(shù)據(jù)了得运，不
完整了。
如果我在訂單表里面锅移，隨便插入了一條數(shù)據(jù)熔掺，這個訂單在用戶表里面沒有與之對應的用戶。這樣
數(shù)據(jù)也不完整了非剃。
如果有外鍵的話置逻，就方便多了，可以不讓用戶刪除數(shù)據(jù)备绽，或者刪除用戶的話券坞，通過外鍵同樣刪除
訂單表里面的數(shù)據(jù)，這樣也能讓數(shù)據(jù)完整肺素。
外鍵： 每次插入或更新時恨锚，都會檢查數(shù)據(jù)的完整性。
方法一：通過 create table 創(chuàng)建外鍵
語法：
create table 數(shù)據(jù)表名稱(...,[CONSTRAINT[約束名稱]]FOREIGN KEY[外鍵字段]REFERENCES[外鍵表名](外鍵字段倍靡，外鍵字段2…..)[ON DELETE CASCADE][ON UPDATE CASCADE])constraint[k?n?stre?nt]限制references[?refr?ns?z]涉及猴伶；關聯(lián)cascade[k??ske?d]級聯(lián)
關于參數(shù)的解釋：
RESTRICT:?拒絕對父表的刪除或更新操作。
CASCADE:?從父表刪除或更新且自動刪除或更新子表中匹配的行塌西。ON DELETE CASCADE 和 ON
UPDATE CASCADE?都可用
注意：on update cascade 是級聯(lián)更新的意思他挎，on delete cascade 是級聯(lián)刪除的意思，意思就是
說當你更新或刪除主鍵表捡需，那外鍵表也會跟隨一起更新或刪除办桨。
精簡化后的語法：
語法：foreign key?當前表的字段?references?外部表名 （關聯(lián)的字段）?ENGINE =innodb
注：創(chuàng)建成功，必須滿足以下 4 個條件：
1站辉、確保參照的表和字段存在呢撞。
2、組成外鍵的字段被索引饰剥。
3殊霞、必須使用 ENGINE 指定存儲引擎為：innodb。
4捐川、外鍵字段和關聯(lián)字段脓鹃，數(shù)據(jù)類型必須一致。
例子：我們創(chuàng)建一個數(shù)據(jù)庫古沥，包含用戶信息表和訂單表
mysql>create database market;mysql>use market;mysql>create table `user`(idint(11)not null auto_increment,namevarchar(50)not nulldefault'',sexint(1)not nulldefault'0',primarykey(id))ENGINE=innodb;#創(chuàng)建時瘸右，如果表名是 sql 關鍵字娇跟，使用時，需要使用反引號``mysql>create table `order`(o_idint(11)auto_increment,u_idint(11)default'0',usernamevarchar(50),moneyint(11),primarykey(o_id),index(u_id),foreign keyorder_f_key(u_id)referencesuser(id)on delete cascade on update cascade)ENGINE=innodb;
注：on delete cascade on update cascade?添加級聯(lián)刪除和更新太颤。
確保參照的表 user 中 id 字段存在苞俘。 組成外鍵的字段 u_id 被索引。 必須使用 type 指定存儲引擎為：innodb龄章。
外鍵字段和關聯(lián)字段吃谣，數(shù)據(jù)類型必須一致。
插入測試數(shù)據(jù)
mysql>insert intouser(name,sex)values('HA',1),('LB',2),('HPC',1);mysql>insert into `order`(u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256);mysql>select*from `order`;
mysql>select id,name,sex,money,o_id from user,`order` where id=u_id;
測試級聯(lián)刪除：
mysql>delete from user where id=1;#刪除 user 表中 id 為1的數(shù)據(jù)
再查看 order 表做裙。
mysql>select*from `order`;
測試級聯(lián)更新：
更新前數(shù)據(jù)狀態(tài)
mysql>select*from `order`;
mysql>select*from user;
mysql>update user set id=6where id=2;mysql>select*from user;
測試數(shù)據(jù)完整性：
mysql>insert into `order`(u_id,username,money)values(5,'Find',346);
外鍵約束岗憋，order 表受 user 表的約束
在 order 里面插入一條數(shù)據(jù) u_id 為 5 用戶，在 user 表里面根本沒有锚贱，所以插入不進去
mysql>insert into uservalues(5,'Find',1);mysql>insert into `order`(u_id,username,money)values(5,'Find',346);#這里 u_id 只能是5mysql>select*from `order`;
語法
alter table 數(shù)據(jù)表名稱 drop foreign key 約束（外鍵）名稱mysql>alter table `order` drop foreign key order1_ibfk_1;mysql>show create table `order`;
什么是視圖：
視圖就是一個存在于數(shù)據(jù)庫中的虛擬表仔戈。
視圖本身沒有數(shù)據(jù)，只是通過執(zhí)行相應的 select 語句完成獲得相應的數(shù)據(jù)拧廊。
我們在怎樣的場景使用它监徘，為什么使用視圖 ：
如果某個查詢結(jié)果出現(xiàn)的非常頻繁，要經(jīng)常拿這個查詢結(jié)果來做子查詢
視圖能夠簡化用戶的操作
視圖機制用戶可以將注意力集中在所關心的數(shù)據(jù)上吧碾。如果這些數(shù)據(jù)不是直接來自基本表凰盔，則可以通過
定義視圖，使數(shù)據(jù)庫看起來結(jié)構(gòu)簡單倦春、清晰户敬，并且可以簡化用戶的數(shù)據(jù)查詢操作
視圖是用戶能以不同的角度看待同樣的數(shù)據(jù)。
對于固定的一些基本表溅漾，我們可以給不同的用戶建立不同的視圖山叮，這樣不同的用戶就可以看到自己需
要的信息了著榴。
視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯性添履。
比如原來的 A 表被分割成了 B 表和 C 表，我們?nèi)匀豢梢栽?B 表和 C 表的基礎上構(gòu)建一個視圖 A脑又，而
使用該數(shù)據(jù)表的程序可以不變暮胧。
視圖能夠?qū)C密數(shù)據(jù)提供安全保護
比如說，每門課的成績都構(gòu)成了一個基本表问麸，但是對于每個同學只可以查看自己這門課的成績往衷，因此
可以為每個同學建立一個視圖，隱藏其他同學的數(shù)據(jù)严卖，只顯示該同學自己的
適當?shù)睦靡晥D可以更加清晰的表達查詢數(shù)據(jù)席舍。
有時用現(xiàn)有的視圖進行查詢可以極大的減小查詢語句的復雜程度。
語法：create view 視圖名稱（即虛擬的表名） as select 語句哮笆。
在 book 數(shù)據(jù)庫創(chuàng)建視圖
mysql>use book;mysql>create view bc as select b.bName,b.price,c.bTypeName from books as b left join category as c on b.bTypeId=c.bTypeId;
可以按照普通表去訪問来颤。另外視圖表中的數(shù)據(jù)和原數(shù)據(jù)表中數(shù)據(jù)是同步的汰扭。
2. 查看視圖創(chuàng)建信息
mysql>show create view bc \G
3.查詢視圖中的數(shù)據(jù)
mysql>select*from bc where price<50\G
語法：
alter view 視圖名稱（即虛擬的表名） as select 語句。update view 視圖名稱（即虛擬的表名）set mysql>alter view bc as select b.bName,b.publishing,c.bTypeId from books as b left join category as c on b.bTypeId=c.bTypeId;mysql>select*from bc\G
查看有哪些可能視圖
show table status where comment='view'\G
drop view 視圖名福铅。
mysql>drop view bc;
更多技術干貨盡在公眾號 “學神來啦” 持續(xù)為大家更新前沿技術和技術干貨
