我們通過這個例子來看看
[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)計和值更加復(fù)雜?？煽樟行枰嗟拇?/p>
儲空間，還需要 MySQL 內(nèi)部進行特殊處理。可空列被索引后，每條記錄都需要一個額外的字節(jié)，還能導(dǎo)
致 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é)：
如果字段沒有設(shè)定 default ,MySQL 依據(jù)這個字段是 null 還是 not null，如果可以為 null，則為
null。如果不可以為 null，則報錯。
如果時間字段，默認為當(dāng)前時間 ，插入 0 時，默認為當(dāng)前時間。
如果是 enum 類型，默認為第一個元素。
自動增長
只能修飾 int 字段。 表明 MySQL 應(yīng)該自動為該字段生成一個唯一沒有用過的數(shù)（每次在最大 ID
值的基礎(chǔ)上加 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ù)同步：如果索引過多，當(dāng)表中數(shù)據(jù)更新的時候后，索引也要同步更
新，這就降低了效率。
索引的類型：
普通索引
唯一性索引
主鍵索引（主索引）
復(fù)合索引
最基本的索引，不具備唯一性，就是加快查詢速度
創(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));#加上名稱
方法二： 當(dāng)表創(chuàng)建完成后，使用 alter 為表添加索引
alter table 表名 add index 索引名稱 （字段1，字段2.....）;
查看索引
mysql>desc demo;
注：如果 Key 是 MUL, 就是一般性索引，該列的值可以重復(fù), 該列是一個非唯一索引的前導(dǎo)列(第一
列)或者是一個唯一性索引的組成部分但是可以含有空值 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)一次，應(yīng)該加唯一索引。唯一性允許有 NULL 值<允許為空>。
創(chuàng)建唯一索引：
方法一：創(chuàng)建表時，加唯一索引
create table 表名(列定義：unique key 索引名 （字段）;)
注意：常用在值不能重復(fù)的字段上，比如說用戶名，電話號碼，身份證號。
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;#使用完馬上恢復(fù)默認的分割符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 點幾秒，如果更復(fù)雜的表結(jié)構(gòu)查詢時間會差更多。
索引可以包含一個、兩個或更多個列。兩個或更多個列上的索引被稱作復(fù)合索引
例： 創(chuàng)建一個表存放服務(wù)器允許或拒絕的 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ān)鍵技術(shù)。它能夠利用分詞技術(shù)等多種算法智能分析出文本文字中關(guān)鍵字詞的頻率及重要性，然后按照一定的算法規(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.唯一性約束對應(yīng)使用唯一性索引
table(id pri,use,name index,pass)；
foreign key 就是表與表之間的某種約定的關(guān)系，由于這種關(guān)系的存在，我們能夠讓表與表之間的數(shù)據(jù)，更加的完整，關(guān)聯(lián)性更強。
關(guān)于完整性，關(guān)聯(lián)性我們舉個例子
例：
有二張表，一張是用戶表，一張是訂單表
如果我刪除了用戶表里的用戶，那么訂單表里面與這個用戶有關(guān)的數(shù)據(jù)，就成了無頭數(shù)據(jù)了，不
完整了。
如果我在訂單表里面，隨便插入了一條數(shù)據(jù)，這個訂單在用戶表里面沒有與之對應(yīng)的用戶。這樣
數(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]涉及；關(guān)聯(lián)cascade[k??ske?d]級聯(lián)
關(guān)于參數(shù)的解釋：
RESTRICT:?拒絕對父表的刪除或更新操作。
CASCADE:?從父表刪除或更新且自動刪除或更新子表中匹配的行。ON DELETE CASCADE 和 ON
UPDATE CASCADE?都可用
注意：on update cascade 是級聯(lián)更新的意思，on delete cascade 是級聯(lián)刪除的意思，意思就是
說當(dāng)你更新或刪除主鍵表，那外鍵表也會跟隨一起更新或刪除。
精簡化后的語法：
語法：foreign key?當(dāng)前表的字段?references?外部表名 （關(guān)聯(lián)的字段）?ENGINE =innodb
注：創(chuàng)建成功，必須滿足以下 4 個條件：
1、確保參照的表和字段存在。
2、組成外鍵的字段被索引。
3、必須使用 ENGINE 指定存儲引擎為：innodb。
4、外鍵字段和關(guān)聯(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 關(guān)鍵字，使用時，需要使用反引號``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。
外鍵字段和關(guān)聯(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í)行相應(yīng)的 select 語句完成獲得相應(yīng)的數(shù)據(jù)。
我們在怎樣的場景使用它，為什么使用視圖 ：
如果某個查詢結(jié)果出現(xiàn)的非常頻繁，要經(jīng)常拿這個查詢結(jié)果來做子查詢
視圖能夠簡化用戶的操作
視圖機制用戶可以將注意力集中在所關(guān)心的數(shù)據(jù)上。如果這些數(shù)據(jù)不是直接來自基本表，則可以通過
定義視圖，使數(shù)據(jù)庫看起來結(jié)構(gòu)簡單、清晰，并且可以簡化用戶的數(shù)據(jù)查詢操作
視圖是用戶能以不同的角度看待同樣的數(shù)據(jù)。
對于固定的一些基本表，我們可以給不同的用戶建立不同的視圖，這樣不同的用戶就可以看到自己需
要的信息了。
視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯性。
比如原來的 A 表被分割成了 B 表和 C 表，我們?nèi)匀豢梢栽?B 表和 C 表的基礎(chǔ)上構(gòu)建一個視圖 A，而
使用該數(shù)據(jù)表的程序可以不變。
視圖能夠?qū)C密數(shù)據(jù)提供安全保護
比如說，每門課的成績都構(gòu)成了一個基本表，但是對于每個同學(xué)只可以查看自己這門課的成績，因此
可以為每個同學(xué)建立一個視圖，隱藏其他同學(xué)的數(shù)據(jù)，只顯示該同學(xué)自己的
適當(dāng)?shù)睦靡晥D可以更加清晰的表達查詢數(shù)據(jù)。
有時用現(xiàn)有的視圖進行查詢可以極大的減小查詢語句的復(fù)雜程度。
語法：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;
更多技術(shù)干貨盡在公眾號 “學(xué)神來啦” 持續(xù)為大家更新前沿技術(shù)和技術(shù)干貨
