來自：CSDN，作者：肖申克的陪伴

第一章 SQL的介紹

1.1、什么是sql

SQL：Structure Query Language。（結構化查詢語言）,通過sql操作數據庫(操作數據庫,操作表,操作數據)

SQL被美國國家標準局（ANSI）確定為關系型數據庫語言的美國標準，后來被國際化標準組織（ISO）采納為關系數據庫語言的國際標準

各數據庫廠商(MySql,oracle,sql server)都支持ISO的SQL標準。

各數據庫廠商在標準的基礎上做了自己的擴展。各個數據庫自己特定的語法

1.2、sql的分類

Data Definition Language (DDL數據定義語言) 如：操作數據庫，操作表

Data Manipulation Language(DML數據操縱語言)，如：對表中的記錄操作增刪改

Data Query Language(DQL 數據查詢語言)，如：對表中數據的查詢操作

Data Control Language(DCL 數據控制語言)，如：對用戶權限的設置

1.3、MySQL的語法規范和要求

（1）mysql的sql語法不區分大小寫

MySQL的關鍵字和函數名等不區分大小寫，但是對于數據值是否區分大小寫，和字符集與校對規則有關。

ci（大小寫不敏感），cs（大小寫敏感），_bin（二元，即比較是基于字符編碼的值而與language無關，區分大小寫）

（2）命名時：盡量使用26個英文字母大小寫，數字0-9，下劃線，不要使用其他符號user_id

（3）建議不要使用mysql的關鍵字等來作為表名、字段名等，如果不小心使用，請在SQL語句中使用`（飄號）引起來

（4）數據庫和表名、字段名等對象名中間不要包含空格

（5）同一個mysql軟件中，數據庫不能同名，同一個庫中，表不能重名，同一個表中，字段不能重名

（6）標點符號：

必須成對

必須英文狀態下半角輸入方式

字符串和日期類型可以使用單引號’’

列的別名可以使用雙引號""，給表名取別名不要使用雙引號。取別名時as可以省略

如果列的別名沒有包含空格，可以省略雙引號，如果有空格雙引號不能省略。

（7）SQL腳本中如何加注釋

單行注釋：#注釋內容

單行注釋：–空格注釋內容 其中–后面的空格必須有

多行注釋：/* 注釋內容 */

#以下兩句是一樣的，不區分大小寫
showdatabases;
SHOWDATABASES;

#創建表格
#createtablestudentinfo(...);#表名錯誤，因為表名有空格
createtablestudent_info(...);

#其中name使用``飄號，因為name和系統關鍵字或系統函數名等預定義標識符重名了。
CREATETABLEt_stu(
idINT,
`name`VARCHAR(20)
);

selectidas"編號",`name`as"姓名"fromt_stu;#起別名時，as都可以省略
selectidas編號,`name`as姓名fromt_stu;#如果字段別名中沒有空格，那么可以省略""
selectidas編號,`name`as姓名fromt_stu;#錯誤，如果字段別名中有空格，那么不能省略""

第二章-DDL操作數據庫

2.1、創建數據庫(掌握)

語法

createdatabase數據庫名[characterset字符集][collate校對規則]注:[]意思是可選的意思

字符集(charset)：是一套符號和編碼。

練習

創建一個day01的數據庫（默認字符集)

createdatabaseday01;

創建一個day01_2的數據庫,指定字符集為gbk(了解)

createdatabaseday01_2charactersetgbk;

2.2、查看所有的數據庫

查看所有的數據庫

語法

showdatabases;

查看數據庫的定義結構【了解】

語法

showcreatedatabase數據庫名;

查看day01這個數據庫的定義

showcreatedatabaseday01;

2.3、刪除數據庫

語法

dropdatabase數據庫名;

刪除day01_2數據庫

dropdatabaseday01_2;

2.4、修改數據庫【了解】

語法

alterdatabase數據庫名characterset字符集;

修改day01這個數據庫的字符集(gbk)

alterdatabaseday01charactersetgbk;

注意：

是utf8，不是utf-8

不是修改數據庫名

2.5、其他操作

切換數據庫, 選定哪一個數據庫

use數據庫名;//注意:在創建表之前一定要指定數據庫.use數據庫名

練習: 使用day01

useday01;

查看正在使用的數據庫

selectdatabase();

第三章-DDL操作表

3.1、創建表

語法

createtable表名(
列名類型[約束],
列名類型[約束]
...

);

類型

數值類型

整型系列：xxxInt

int(M)，必須和unsigned zerofill一起使用才有意義

浮點型系列：float,double（或real)

double(M,D)：表示最長為M位，其中小數點后D位

例如：double(5,2)表示的數據范圍[-999.99,999.99]，如果超過這個范圍會報錯。

定點型系列：decimal（底層實際上是使用字符串進行存儲）

decimal(M,D)：表示最長為M位，其中小數點后D位

位類型：bit

字節范圍是：1-8，值范圍是：bit(1)~bit(64)，默認bit(1)

用來存儲二進制數。對于位字段，直接使用select命令將不會看到結果。可以使用bit()或hex()函數進行讀取。插入bit類型字段時，使用bit()函數轉為二進制值再插入，因為二進制碼是“01”。常見 SQL 面試題：經典 50 例

日期時間類型

日期時間類型：year, date, datetime, timestamp

注意一下每一種日期時間的表示范圍

timestamp和datetime的區別：

timestamp范圍比較小

timestamp和時區有關

show variables like ‘time_zone’;

set time_zone = ‘+8:00’;

timestamp受MySQL版本和服務器的SQLMode影響很大

表中的第一個非空的timestamp字段如果插入和更新為NULL則會自動設置為系統時間。速來！整理了 25 個 PDF 電子書免費下載

字符串類型

MySQL中提供了多種對字符數據的存儲類型，不同的版本可能有所差異。常見的有：

char,varchar，xxtext，binary，varbinary，xxblob，enum，set等等

字符串類型char,varchar(M)

char如果沒有指定寬度，默認為1個字符

varchar(M)，必須指定寬度

binary和varbinary類似于char和varchar，不同的是它們包含二進制字符串，不支持模糊查詢之類的。

一般在保存少量字符串的時候，我們會選擇char和varchar；而在保存較大文本時，通常會選擇使用text或blob系列。blob和text值會引起一些性能問題，特別是在執行了大量的刪除操作時，會在數據表中留下很大的“空洞”，為了提高性能，建議定期時候用optimize table功能對這類表進行碎片整理。可以使用合成的(Synthetic)索引來提高大文本字段的查詢性能，如果需要對大文本字段進行模糊查詢，MySql提供了前綴索引。但是仍然要在不必要的時候避免檢索大型的blob或text值。

enum枚舉類型，它的值范圍需要在創建表時通過枚舉方式顯式指定，對于1~255個成員的枚舉需要1個字節存儲；對于【 255`65535】個成員需要2個字節存儲。例如：gender enum(‘男’,‘女’)。如果插入枚舉值以外的值，會按第一個值處理。一次只能從枚舉值中選擇一個。

set集合類型，可以包含0~64個成員。一次可以從集合中選擇多個成員。如果選擇了1-8個成員的集合，占1個字節，依次占2個，3個。。8個字節。例如：hoppy set(‘吃飯’,‘睡覺’,‘玩游戲’,‘旅游’)，選擇時’吃飯,睡覺’或’睡覺,玩游戲,旅游’

示例

+----------------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+----------------+--------------+------+-----+---------+----------------+
|eid|int(11)|NO|PRI|NULL|auto_increment|
|ename|varchar(20)|NO||NULL||
|tel|char(11)|NO||NULL||
|gender|char(1)|YES||男||
|salary|double|YES||NULL||
|commission_pct|double(3,2)|YES||NULL||
|birthday|date|YES||NULL||
|hiredate|date|YES||NULL||
|job_id|int(11)|YES||NULL||
|email|varchar(32)|YES||NULL||
|mid|int(11)|YES||NULL||
|address|varchar(150)|YES||NULL||
|native_place|varchar(10)|YES||NULL||
|did|int(11)|YES||NULL||
+----------------+--------------+------+-----+---------+----------------+

約束

即規則,規矩 限制;

作用：保證用戶插入的數據保存到數據庫中是符合規范的

約束種類:

not null: 非空 ; eg: username varchar(40) not null username這個列不能有null值

unique:唯一約束, 后面的數據不能和前面重復; eg: cardNo char(18) unique; cardNo 列里面不可以有重復數據

primary key；主鍵約束(非空+唯一); 一般用在表的id列上面. 一張表基本上都有id列的, id列作為唯一標識的

auto_increment: 自動增長,必須是設置了primary key之后,才可以使用auto_increment

id int primary key auto_increment; id不需要我們自己維護了, 插入數據的時候直接插入null, 自動的增長進行填充進去, 避免重復了.

注意:

先設置了primary key 再能設置auto_increment

只有當設置了auto_increment 才可以插入null , 否則插入null會報錯

id列:

給id設置為int類型, 添加主鍵約束, 自動增長

或者給id設置為字符串類型,添加主鍵約束, 不能設置自動增長

練習

創建一張學生表(含有id字段,姓名字段不能重復,性別字段不能為空默認值為男. id為主鍵自動增長)

CREATETABLEstudent(
idINTPRIMARYKEYAUTO_INCREMENT,--主鍵自增長
NAMEVARCHAR(30)UNIQUE,--唯一約束
genderCHAR(1)NOTNULLDEFAULT'男'
);

3.2、查看表【了解】

查看所有的表

showtables;

查看表的定義結構

語法

desc 表名；

練習: 查看student表的定義結構

descstudent;

3.3、修改表【掌握，但是不要記憶】

語法

增加一列

altertable【數據庫名.]表名稱add【column】字段名數據類型;
altertable【數據庫名.]表名稱add【column】字段名數據類型first;
altertable【數據庫名.]表名稱add【column】字段名數據類型after另一個字段;

修改列的類型約束:alter table 表名 modify 字段 類型 約束 ；

修改列的名稱，類型，約束: alter table 表名 change 舊列 新列 類型 約束;

刪除一列: alter table 表名 drop 列名;

修改表名 : rename table 舊表名 to 新表名;

練習

給學生表增加一個grade字段,類型為varchar(20),不能為空

ALTERTABLEstudentADDgradeVARCHAR(20)NOTNULL;

給學生表的gender字段改成int類型，不能為空，默認值為1

altertablestudentmodifygendervarchar(20);

給學生表的grade字段修改成class字段

ALTERTABLEstudentCHANGEgradeclassVARCHAR(20)NOTNULL;

把class字段刪除

ALTERTABLEstudentDROPclass;

把學生表修改成老師表(了解)

RENAMETABLEstudentTOteacher;

3.4、刪除表【掌握】

語法

droptable表名；

把teacher表刪除

droptableteacher;

第四章-DML操作表記錄-增刪改【重點】

準備工作: 創建一張商品表(商品id,商品名稱,商品價格,商品數量.)

createtableproduct(
pidintprimarykeyauto_increment,
pnamevarchar(40),
pricedouble,
numint
);

4.1、插入記錄

語法

方式一: 插入指定列, 如果沒有把這個列進行列出來, 以null進行自動賦值了.

eg: 只想插入pname, price , insert into t_product(pname, price) values(‘mac’,18000);

insertinto表名(列,列..)values(值,值..);

注意: 如果沒有插入了列設置了非空約束, 會報錯的

方式二: 插入所有的列,如果哪列不想插入值,則需要賦值為null

insert into 表名 values(值,值....);

eg:

insertintoproductvalues(null,'蘋果電腦',18000.0,10);
insertintoproductvalues(null,'華為5G手機',30000,20);
insertintoproductvalues(null,'小米手機',1800,30);
insertintoproductvalues(null,'iPhonex',8000,10);
insertintoproductvalues(null,'iPhone7',6000,200);
insertintoproductvalues(null,'iPhone6s',4000,1000);
insertintoproductvalues(null,'iPhone6',3500,100);
insertintoproductvalues(null,'iPhone5s',3000,100);

insertintoproductvalues(null,'方便面',4.5,1000);
insertintoproductvalues(null,'咖啡',11,200);
insertintoproductvalues(null,'礦泉水',3,500);

4.2、更新記錄

語法

update表名set列=值,列=值[where條件]

練習

將所有商品的價格修改為5000元

updateproductsetprice=5000;

將商品名是蘋果電腦的價格修改為18000元

UPDATEproductsetprice=18000WHEREpname='蘋果電腦';

將商品名是蘋果電腦的價格修改為17000,數量修改為5

UPDATEproductsetprice=17000,num=5WHEREpname='蘋果電腦';

將商品名是方便面的商品的價格在原有基礎上增加2元

UPDATEproductsetprice=price+2WHEREpname='方便面';

4.3、刪除記錄

delete

根據條件，一條一條數據進行刪除

語法

deletefrom表名[where條件]注意:刪除數據用delete,不用truncate

類型

刪除表中名稱為’蘋果電腦’的記錄

deletefromproductwherepname='蘋果電腦';

刪除價格小于5001的商品記錄

deletefromproductwhereprice

	

	刪除表中的所有記錄(要刪除一般不建議使用delete語句，delete語句是一行一行執行，速度過慢)

	
deletefromproduct;


	

	truncate 把表直接DROP掉，然后再創建一個同樣的新表。刪除的數據不能找回。執行速度比DELETE快

	
truncatetable表;


	

	工作中刪除數據

	物理刪除: 真正的刪除了, 數據不在, 使用delete就屬于物理刪除

	邏輯刪除: 沒有真正的刪除, 數據還在. 搞一個標記, 其實邏輯刪除是更新 eg: state 1 啟用 0禁用

	第五章-DQL操作表記錄-查詢【重點】

	5.1、基本查詢語法

	
select要查詢的字段名from表名[where條件]


	

	5.2、簡單查詢

	查詢所有行和所有列的記錄

	語法

	
select*form表


	

	查詢商品表里面的所有的列

	
select*fromproduct;


	

	查詢某張表特定列的記錄

	語法

	
select列名,列名,列名...from表


	

	查詢商品名字和價格

	
selectpname,pricefromproduct;


	

	去重查詢 distinct

	語法

	
SELECTDISTINCT字段名FROM表名;//要數據一模一樣才能去重


	

	去重查詢商品的名字

	
SELECTDISTINCTpname,priceFROMproduct


	

	注意點: 去重針對某列, distinct前面不能先出現列名

	別名查詢

	語法

	
select列名as別名,列名from表//列別名as可以不寫
select別名.*from表as別名//表別名(多表查詢,明天會具體講)


	

	查詢商品信息，使用別名

	
SELECTpid,pnameAS'商品名',priceAS'商品價格',numAS'商品庫存'FROMproduct


	

	運算查詢(+,-,*,/,%等)

	把商品名，和商品價格+10查詢出來:我們既可以將某個字段加上一個固定值，又可以對多個字段進行運算查詢

	
selectpname,price+10as'price'fromproduct;
selectname,chinese+math+englishastotalfromstudent


	

	注意

	運算查詢字段,字段之間是可以的

	字符串等類型可以做運算查詢，但結果沒有意義

	5.3、條件查詢(很重要)

	語法

	
select...from表where條件
//取出表中的每條數據，滿足條件的記錄就返回，不滿足條件的記錄不返回


	

	運算符

	1、比較運算符

	
大于：>
小于：<
大于等于：>=
小于等于：<=
等于：=???不能用于null判斷
不等于：!=??或?<>
安全等于:<=>可以用于null值判斷


	

	2、邏輯運算符（建議用單詞，可讀性來說）

	
邏輯與：&&或 and
邏輯或：||或 or
邏輯非：! 或 not
邏輯異或：^或 xor


	

	3、范圍

	
區間范圍：between x  and  y
notbetweenxandy
集合范圍：in(x,x,x)
notin(x,x,x)


	

	4、模糊查詢和正則匹配（只針對字符串類型，日期類型）

	
like'xxx'模糊查詢是處理字符串的時候進行部分匹配
如果想要表示0~n個字符，用%
如果想要表示確定的1個字符，用_

regexp'正則'


	

	5、特殊的null值處理

	
#（1）判斷時
xxisnull
xxisnotnull
xx<=>null
#（2）計算時
ifnull(xx,代替值)當xx是null時，用代替值計算


	

	練習

	查詢商品價格>3000的商品

	
select*fromproductwhereprice>3000;


	

	查詢pid=1的商品

	
select*fromproductwherepid=1;


	

	查詢pid<>1的商品（！=）

	
select*fromproductwherepid<>1;


	

	查詢價格在3000到6000之間的商品

	
select*fromproductwherepricebetween3000and6000;


	

	查詢pid在1，5，7，15范圍內的商品

	
select*fromproductwhereid=1;
select*fromproductwhereid=5;
select*fromproductwhereid=7;
select*fromproductwhereid=15;

select*fromproductwhereidin(1,5,7,15);


	

	查詢商品名以iPho開頭的商品(iPhone系列)

	
select*fromproductwherepnamelike'iPho%';


	

	查詢商品價格大于3000并且數量大于20的商品 (條件 and 條件 and…)

	
select*fromproductwhereprice>3000andnum>20;


	

	查詢id=1或者價格小于3000的商品

	
select*fromproductwherepid=1orprice

	

	5.4、排序查詢

	排序是寫在查詢的后面，代表把數據查詢出來之后再排序

	環境的準備

	
#創建學生表(有sid,學生姓名,學生性別,學生年齡,分數列,其中sid為主鍵自動增長)
CREATETABLEstudent(
sidINTPRIMARYKEYauto_increment,
snameVARCHAR(40),
sexVARCHAR(10),
ageINT,
scoreDOUBLE
);

INSERTINTOstudentVALUES(null,'zs','男',18,98.5);
INSERTINTOstudentVALUES(null,'ls','女',18,96.5);
INSERTINTOstudentVALUES(null,'ww','男',15,50.5);
INSERTINTOstudentVALUES(null,'zl','女',20,98.5);
INSERTINTOstudentVALUES(null,'tq','男',18,60.5);
INSERTINTOstudentVALUES(null,'wb','男',38,98.5);
INSERTINTOstudentVALUES(null,'小麗','男',18,100);
INSERTINTOstudentVALUES(null,'小紅','女',28,28);
INSERTINTOstudentVALUES(null,'小強','男',21,95);


	

	單列排序

	語法: 只按某一個字段進行排序，單列排序

	
SELECT字段名FROM表名[WHERE條件]ORDERBY字段名[ASC|DESC];//ASC:升序，默認值;DESC:降序


	

	案例: 以分數降序查詢所有的學生

	
SELECT*FROMstudentORDERBYscoreDESC


	

	組合排序

	語法: 同時對多個字段進行排序，如果第1個字段相等，則按第2個字段排序，依次類推

	
SELECT字段名FROM表名WHERE字段=值ORDERBY字段名1[ASC|DESC],字段名2[ASC|DESC];


	

	練習: 以分數降序查詢所有的學生, 如果分數一致,再以age降序

	
SELECT*FROMstudentORDERBYscoreDESC,ageDESC


	

	5.5、聚合函數

	聚合函數用于統計，通常會和分組查詢一起使用，用于統計每組的數據

	聚合函數列表

	

	語法

	
SELECT聚合函數(列名)FROM表名[where條件];


	

	案例

	
--求出學生表里面的最高分數
SELECTMAX(score)FROMstudent
--求出學生表里面的最低分數
SELECTMIN(score)FROMstudent
--求出學生表里面的分數的總和(忽略null值)
SELECTSUM(score)FROMstudent
--求出學生表里面的平均分
SELECTAVG(score)FROMstudent
--求出學生表里面的平均分（缺考了當成0分處理）
SELECTAVG(IFNULL(score,0))FROMstudent
--統計學生的總人數(忽略null)
SELECTCOUNT(sid)FROMstudent
SELECTCOUNT(*)FROMstudent


	

	注意: 聚合函數會忽略空值NULL

	我們發現對于NULL的記錄不會統計，建議如果統計個數則不要使用有可能為null的列，但如果需要把NULL也統計進去呢？我們可以通過 IFNULL(列名，默認值) 函數來解決這個問題. 如果列不為空，返回這列的值。如果為NULL，則返回默認值。

	
--求出學生表里面的平均分（缺考了當成0分處理）
SELECTAVG(IFNULL(score,0))FROMstudent;


	

	5.6、分組查詢

	GROUP BY將分組字段結果中相同內容作為一組，并且返回每組的第一條數據，所以單獨分組沒什么用處。分組的目的就是為了統計，一般分組會跟聚合函數一起使用

	分組

	語法

	
SELECT字段1,字段2...FROM表名[where條件]GROUPBY列[HAVING條件];


	

	案例

	
--根據性別分組,統計每一組學生的總人數
SELECTsex'性別',COUNT(sid)'總人數'FROMstudentGROUPBYsex

--根據性別分組，統計每組學生的平均分
SELECTsex'性別',AVG(score)'平均分'FROMstudentGROUPBYsex

--根據性別分組，統計每組學生的總分
SELECTsex'性別',SUM(score)'總分'FROMstudentGROUPBYsex


	

	分組后篩選 having

	分組后的條件，不能寫在where之后，where關鍵字要寫在group by之前

	根據性別分組, 統計每一組學生的總人數> 5的(分組后篩選)

	
SELECTsex,count(*)FROMstudentGROUPBYsexHAVINGcount(sid)>5


	

	根據性別分組，只統計年齡大于等于18的，并且要求組里的人數大于4

	
SELECTsex'性別',COUNT(sid)'總人數'FROMstudentWHEREage>=18GROUPBYsexHAVINGCOUNT(sid)>4


	

	where和having的區別【面試】

	where 子句作用

	1)對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾數據，即先過濾再分組。

	2)where后面不可以使用聚合函數

	having字句作用

	having 子句的作用是篩選滿足條件的組，即在分組之后過濾數據，即先分組再過濾。

	having后面可以使用聚合函數

	5.7、分頁查詢

	語法

	
select...from....limita,b


	

	案例

	
--分頁查詢
--limit關鍵字是使用在查詢的后邊，如果有排序的話則使用在排序的后邊
--limit的語法:limitoffset,length其中offset表示跳過多少條數據，length表示查詢多少條數據
SELECT*FROMproductLIMIT0,3
--查詢product表中的前三條數據(0表示跳過0條，3表示查詢3條)

SELECT*FROMproductLIMIT3,3
--查詢product表的第四到六條數據(3表示跳過3條，3表示查詢3條)
--分頁的時候，只會告訴你我需要第幾頁的數據，并且每頁有多少條數據
--假如，每頁需要3條數據，我想要第一頁數據:limit0,3
--假如,每頁需要3條數據，我想要第二頁數據:limit3,3
--假如，每頁需要3條數據，我想要第三頁數據:limit6,3
--結論:length=每頁的數據條數，offset=(當前頁數-1)*每頁數據條數
--limit(當前頁數-1)*每頁數據條數,每頁數據條數


	

	5.8、查詢的語法小結

	
select...from...where...groupby...orderby...limit

select...from...where...
select...from...where...orderby...
select...from...where...limit...
select...from...where...orderby...imit


	

	第六章 數據庫三范式

	好的數據庫設計對數據的存儲性能和后期的程序開發，都會產生重要的影響。建立科學的，規范的數據庫就需要滿足一些規則來優化數據的設計和存儲，這些規則就稱為范式。

	6.1、第一范式: 確保每列保持原子性

	第一范式是最基本的范式。如果數據庫表中的所有字段值都是不可分解的原子值，就說明該數據庫表滿足了第一范式。

	第一范式的合理遵循需要根據系統的實際需求來定。比如某些數據庫系統中需要用到“地址”這個屬性，本來直接將“地址”屬性設計成一個數據庫表的字段就行。但是如果系統經常會訪問“地址”屬性中的“城市”部分，那么就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行存儲，這樣在對地址中某一部分操作的時候將非常方便。這樣設計才算滿足了數據庫的第一范式，如下表所示。

	

	如果不遵守第一范式，查詢出數據還需要進一步處理（查詢不方便）。遵守第一范式，需要什么字段的數據就查詢什么數據（方便查詢）

	6.2、第二范式: 確保表中的每列都和主鍵相關

	第二范式在第一范式的基礎之上更進一層。第二范式需要確保數據庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個數據庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張數據庫表中。

	比如要設計一個訂單信息表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為數據庫表的聯合主鍵，如下表所示

	

	這樣就產生一個問題：這個表中是以訂單編號和商品編號作為聯合主鍵。這樣在該表中商品名稱、單位、商品價格等信息不與該表的主鍵相關，而僅僅是與商品編號相關。所以在這里違反了第二范式的設計原則。

	而如果把這個訂單信息表進行拆分，把商品信息分離到另一個表中，把訂單項目表也分離到另一個表中，就非常完美了。如下所示

	



	

	這樣設計，在很大程度上減小了數據庫的冗余。如果要獲取訂單的商品信息，使用商品編號到商品信息表中查詢即可

	6.3、第三范式: 確保每列都和主鍵列直接相關,而不是間接相關

	第三范式需要確保數據表中的每一列數據都和主鍵直接相關，而不能間接相關。

	比如在設計一個訂單數據表的時候，可以將客戶編號作為一個外鍵和訂單表建立相應的關系。而不可以在訂單表中添加關于客戶其它信息（比如姓名、所屬公司等）的字段。如下面這兩個表所示的設計就是一個滿足第三范式的數據庫表。

	



	

	這樣在查詢訂單信息的時候，就可以使用客戶編號來引用客戶信息表中的記錄，也不必在訂單信息表中多次輸入客戶信息的內容，減小了數據冗余

	第七章 外鍵約束

	7.1、外鍵約束的概念

	在遵循三范式的前提下，很多時候我們必須要進行拆表，將數據分別存放在多張表中，以減少冗余數據。但是拆分出來的表與表之間是有著關聯關系的，我們必須得通過一種約束來約定表與表之間的關系，這種約束就是外鍵約束

	7.2、外鍵約束的作用

	外鍵約束是保證一個或兩個表之間的參照完整性,外鍵是構建于一個表的兩個字段或是兩個表的兩個字段之間的參照關系。

	7.3、創建外鍵約束的語法

	在建表時指定外鍵約束

	
createtable[數據名.]從表名(
字段名1數據類型primarykey,
字段名2數據類型,
....,
[constraint外鍵約束名]foreignkey(從表字段)references主表名(主表字段)[onupdate外鍵約束等級][ondelete外鍵約束等級]
#外鍵只能在所有字段列表后面單獨指定
#如果要自己命名外鍵約束名，建議主表名_從表名_關聯字段名_fk
);


	

	在建表后指定外鍵約束

	
altertable從表名稱add[constraint外鍵約束名]foreignkey(從表字段名)references主表名(主表被參照字段名)[onupdatexx][ondeletexx];


	

	7.4、刪除外鍵約束的語法

	
ALTERTABLE表名稱DROPFOREIGNKEY外鍵約束名;
#查看約束名SELECT*FROMinformation_schema.table_constraintsWHEREtable_name='表名稱';
#刪除外鍵約束不會刪除對應的索引，如果需要刪除索引，需要用ALTERTABLE表名稱DROPINDEX索引名;
#查看索引名showindexfrom表名稱;


	

	7.5、外鍵約束的要求

	在從表上建立外鍵，而且主表要先存在。

	一個表可以建立多個外鍵約束

	通常情況下，從表的外鍵列一定要指向主表的主鍵列

	從表的外鍵列與主表被參照的列名字可以不相同，但是數據類型必須一樣

	7.6、外鍵約束等級

	Cascade方式：在主表上update/delete記錄時，同步update/delete掉從表的匹配記錄

	Set null方式：在主表上update/delete記錄時，將從表上匹配記錄的列設為null，但是要注意子表的外鍵列不能為not null

	No action方式：如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作

	Restrict方式：同no action, 都是立即檢查外鍵約束

	Set default方式（在可視化工具SQLyog中可能顯示空白）：父表有變更時,子表將外鍵列設置成一個默認的值，但Innodb不能識別

	如果沒有指定等級，就相當于Restrict方式

	7.7、外鍵約束練習

	
--部門表
createtabledept(
idintprimarykey,
dept_namevarchar(50),
dept_locationvarchar(50)
);
--員工表
CREATETABLEemp(
eidintprimarykey,
namevarchar(50)notnull,
sexvarchar(10),
dept_idint
);
--給員工表表的dept_id添加外鍵指向部門表的主鍵
altertableempaddforeignkey(dept_id)referencesdept(id)


	

	第八章 多表間關系

	8.1、一對多關系

	概念

	一對多的關系是指: 主表的一行數據可以同時對應從表的多行數據，反過來就是從表的多行數據指向主表的同一行數據。

	應用場景

	分類表和商品表、班級表和學生表、用戶表和訂單表等等

	建表原則

	將一的一方作為主表，多的一方作為從表，在從表中指定一個字段作為外鍵，指向主表的主鍵

	

	建表語句練習

	
--創建分類表
CREATETABLEcategory(
cidINTPRIMARYKEYAUTO_INCREMENT,
cnameVARCHAR(50)
);

--創建商品表
CREATETABLEproduct(
pidINTPRIMARYKEYAUTO_INCREMENT,
pnameVARCHAR(50),
priceDOUBLE,
cidINT
)
--給商品表添加一個外鍵
altertableproductaddforeignkey(cid)referencescategory(cid)


	

	8.2、多對多關系

	概念

	兩張表都是多的一方，A表的一行數據可以同時對應B表的多行數據，反之B表的一行數據也可以同時對應A表的多行數據

	應用場景

	訂單表和商品表、學生表和課程表等等

	建表原則

	因為兩張表都是多的一方，所以在兩張表中都無法創建外鍵，所以需要新創建一張中間表，在中間表中定義兩個字段，這倆字段分別作為外鍵指向兩張表各自的主鍵

	

	建表語句練習

	
--創建學生表
CREATETABLEstudent(
sidINTPRIMARYKEYAUTO_INCREMENT,
snameVARCHAR(50)
);

--創建課程表
CREATETABLEcourse(
cidINTPRIMARYKEYAUTO_INCREMENT,
cnameVARCHAR(20)
);

--創建中間表
CREATETABLEs_c_table(
snoINT,
cnoINT
);
--給sno字段添加外鍵指向student表的sid主鍵
ALTERTABLEs_c_tableADDCONSTRAINTfkey01FOREIGNKEY(sno)REFERENCESstudent(sid);
--給cno字段添加外鍵指向course表的cid主鍵
ALTERTABLEs_c_tableADDCONSTRAINTfkey03FOREIGNKEY(cno)REFERENCEScourse(cid);


	

	8.3、一對一關系(了解)

	第一種一對一關系

	我們之前學習過一對多關系，在一對多關系中主表的一行數據可以對應從表的多行數據，反之從表的一行數據則只能對應主表的一行數據。這種一行數據對應一行數據的關系，我們可以將其看作一對一關系

	第二種一對一關系

	A表中的一行數據對應B表中的一行數據，反之B表中的一行數據也對應A表中的一行數據，此時我們可以將A表當做主表B表當做從表，或者是將B表當做主表A表當做從表

	建表原則

	在從表中指定一個字段創建外鍵并指向主表的主鍵，然后給從表的外鍵字段添加唯一約束

	第九章 多表關聯查詢

	多表關聯查詢是使用一條SQL語句，將關聯的多張表的數據查詢出來

	9.1、環境準備

	
--創建一張分類表(類別id,類別名稱.備注:類別id為主鍵并且自動增長)
CREATETABLEt_category(
cidINTPRIMARYKEYauto_increment,
cnameVARCHAR(40)
);
INSERTINTOt_categoryvalues(null,'手機數碼');
INSERTINTOt_categoryvalues(null,'食物');
INSERTINTOt_categoryvalues(null,'鞋靴箱包');


--創建一張商品表(商品id,商品名稱,商品價格,商品數量,類別.備注:商品id為主鍵并且自動增長)

CREATETABLEt_product(
pidINTPRIMARYKEYauto_increment,
pnameVARCHAR(40),
priceDOUBLE,
numINT,
cnoINT
);

insertintot_productvalues(null,'蘋果電腦',18000,10,1);
insertintot_productvalues(null,'iPhone8s',5500,100,1);
insertintot_productvalues(null,'iPhone7',5000,100,1);
insertintot_productvalues(null,'iPhone6s',4500,1000,1);
insertintot_productvalues(null,'iPhone6',3800,200,1);
insertintot_productvalues(null,'iPhone5s',2000,10,1);
insertintot_productvalues(null,'iPhone4s',18000,1,1);

insertintot_productvalues(null,'方便面',4.5,1000,2);
insertintot_productvalues(null,'咖啡',10,100,2);
insertintot_productvalues(null,'礦泉水',2.5,100,2);

insertintot_productvalues(null,'法拉利',3000000,50,null);

--給商品表添加外鍵
ALTERTABLEt_productADDFOREIGNKEY(cno)REFERENCESt_category(cid);


	

	9.2、交叉查詢【了解】

	交叉查詢其實就是將多張表的數據沒有條件地連接在一起進行展示

	語法

	
selecta.列,a.列,b.列,b.列froma,b;

selecta.*,b.*froma,b;
--或者
select*froma,b;


	

	練習

	使用交叉查詢類別和商品

	
select*fromt_category,t_product;


	

	通過查詢結果我們可以看到，交叉查詢其實是一種錯誤的做法，在查詢到的結果集中有大量的錯誤數據，我們稱交叉查詢到的結果集是笛卡爾積

	笛卡爾積

	假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以擴展到多個集合的情況。

	9.3、內連接查詢

	交叉查詢產生這樣的結果并不是我們想要的，那么怎么去除錯誤的、不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關聯關系，那么就通過**關聯關系(主外鍵關系)**去除笛卡爾積。這種通過條件過濾去除笛卡爾積的查詢，我們稱之為連接查詢。連接查詢又可以分為內連接查詢和外連接查詢，我們先學習內連接查詢

	隱式內連接查詢

	隱式內連接查詢里面是沒有inner join關鍵字

	
select[字段,字段,字段]froma,bwhere連接條件(b表里面的外鍵=a表里面的主鍵)


	

	顯式內連接查詢

	顯式內連接查詢里面是有inner join關鍵字

	
select[字段,字段,字段]froma[inner]joinbon連接條件[where其它條件]


	

	內連接查詢練習

	查詢所有類別下的商品信息,如果該類別下沒有商品則不展示

	
--1隱式內連接方式
select*fromt_categoryc,t_productpWHEREc.cid=p.cno;

--2顯示內連接方式
--查詢手機數碼這個分類下的所有商品的信息以及分類信息
SELECT*FROMt_producttpINNERJOINt_categorytcONtp.cno=tc.cidWHEREtc.cname='手機數碼';

SELECT*fromt_categorycINNERJOINt_productpONc.cid=p.cno


	

	內連接查詢的特點

	主表和從表中的數據都是滿足連接條件則能夠查詢出來，不滿足連接條件則不會查詢出來

	9.4、外連接查詢

	我們發現內連接查詢出來的是滿足連接條件的公共部分， 如果要保證查詢出某張表的全部數據情況下進行連接查詢. 那么就要使用外連接查詢了. 外連接分為左外連接和右外連接

	左外連接查詢

	概念

	以join左邊的表為主表,展示主表的所有數據,根據條件查詢連接右邊表的數據,若滿足條件則展示,若不滿足則以null顯示。可以理解為：在內連接的基礎上保證左邊表的數據全部顯示

	語法

	
select字段fromaleft[outer]joinbon條件


	

	練習

	查詢所有類別下的商品信息，就算該類別下沒有商品也需要將該類別的信息展示出來

	
SELECT*FROMt_categorycLEFTOUTERJOINt_productpONc.cid=p.cno


	

	右外連接查詢

	概念

	以join右邊的表為主表,展示右邊表的所有數據,根據條件查詢join左邊表的數據,若滿足則展示,若不滿足則以null顯示。可以理解為：在內連接的基礎上保證右邊表的數據全部顯示

	語法

	
select字段fromaright[outer]joinbon條件


	

	練習

	查詢所有商品所對應的類別信息

	
SELECT*FROMt_categorycRIGHTOUTERJOINt_productpONc.cid=p.cno


	

	9.5、union聯合查詢實現全外連接查詢

	首先要明確，聯合查詢不是多表連接查詢的一種方式。聯合查詢是將多條查詢語句的查詢結果合并成一個結果并去掉重復數據。

	全外連接查詢的意思就是將左表和右表的數據都查詢出來，然后按照連接條件連接

	union的語法

	
查詢語句1union查詢語句2union查詢語句3...


	

	練習

	
#用左外的Aunion右外的B
SELECT*FROMt_categorycLEFTOUTERJOINt_productpONc.cid=p.cno
union
SELECT*FROMt_categorycRIGHTOUTERJOINt_productpONc.cid=p.cno


	

	9.6、自連接查詢

	自連接查詢是一種特殊的多表連接查詢，因為兩個關聯查詢的表是同一張表，通過取別名的方式來虛擬成兩張表，然后進行兩張表的連接查詢

	準備工作

	
--員工表
CREATETABLEemp(
idINTPRIMARYKEY,--員工id
enameVARCHAR(50),--員工姓名
mgrINT,--上級領導
joindateDATE,--入職日期
salaryDECIMAL(7,2)--工資
);
--添加員工
INSERTINTOemp(id,ename,mgr,joindate,salary)VALUES
(1001,'孫悟空',1004,'2000-12-17','8000.00'),
(1002,'盧俊義',1006,'2001-02-20','16000.00'),
(1003,'林沖',1006,'2001-02-22','12500.00'),
(1004,'唐僧',1009,'2001-04-02','29750.00'),
(1005,'李逵',1006,'2001-09-28','12500.00'),
(1006,'宋江',1009,'2001-05-01','28500.00'),
(1007,'劉備',1009,'2001-09-01','24500.00'),
(1008,'豬八戒',1004,'2007-04-19','30000.00'),
(1009,'羅貫中',NULL,'2001-11-17','50000.00'),
(1010,'吳用',1006,'2001-09-08','15000.00'),
(1011,'沙僧',1004,'2007-05-23','11000.00'),
(1012,'李逵',1006,'2001-12-03','9500.00'),
(1013,'小白龍',1004,'2001-12-03','30000.00'),
(1014,'關羽',1007,'2002-01-23','13000.00');
#查詢孫悟空的上級
SELECTemployee.*,manager.enamemgrnameFROMempemployee,empmanagerwhereemployee.mgr=manager.idANDemployee.ename='孫悟空'


	

	自連接查詢練習

	查詢員工的編號，姓名，薪資和他領導的編號，姓名，薪資

	
#這些數據全部在員工表中
#把t_employee表，即當做員工表，又當做領導表
#領導表是虛擬的概念，我們可以通過取別名的方式虛擬
SELECTemployee.id"員工的編號",emp.ename"員工的姓名",emp.salary"員工的薪資",
manager.id"領導的編號",manager.ename"領導的姓名",manager.salary"領導的薪資"
FROMempemployeeINNERJOINempmanager
#emp employee：employee.，表示的是員工表的
#emp manager：如果用manager.，表示的是領導表的
ONemployee.mgr=manager.id#員工的mgr指向上級的id

#表的別名不要加""，給列取別名，可以用""，列的別名不使用""也可以，但是要避免包含空格等特殊符號。


	

	第十章 子查詢

	如果一個查詢語句嵌套在另一個查詢語句里面，那么這個查詢語句就稱之為子查詢，根據位置不同，分為：where型，from型，exists型。注意：不管子查詢在哪里，子查詢必須使用()括起來。

	10.1、where型

	①子查詢是單值結果(單行單列)，那么可以對其使用（=，>等比較運算符）

	
#查詢價格最高的商品信息
select*fromt_productwhereprice=(selectmax(price)fromt_product)


	

	②子查詢是多值結果，那么可對其使用（【not】in(子查詢結果)，或 >all(子查詢結果)，或>=all(子查詢結果)，any(子查詢結果)，或>=any(子查詢結果)，

	
#查詢價格最高的商品信息
SELECT*FROMt_productWHEREprice>=ALL(SELECTpriceFROMt_product)
select*fromt_productorderbypricedesclimit0,1


	

	10.2、from型

	子查詢的結果是多行多列的結果，類似于一張表格。

	必須給子查詢取別名，即臨時表名，表的別名不要加“”和空格。

	
--思路一:使用連接查詢
--使用外連接，查詢出分類表的所有數據
SELECTtc.cname,COUNT(tp.pid)FROMt_categorytcLEFTJOINt_producttpONtp.cno=tc.cidGROUPBYtc.cname

--思路二:使用子查詢
--第一步:對t_product根據cno進行分組查詢，統計每個分類的商品數量
SELECTcno,COUNT(pid)FROMt_productGROUPBYcno
--第二步:用t_category表去連接第一步查詢出來的結果，進行連接查詢,此時要求查詢出所有的分類
SELECTtc.cname,IFNULL(tn.total,0)'總數量'FROMt_categorytcLEFTJOIN(SELECTcno,COUNT(pid)totalFROMt_productGROUPBYcno)tnONtn.cno=tc.cid


	

	10.3、exists型

	
#查詢那些有商品的分類
SELECTcid,cnameFROMt_categorytcWHEREEXISTS(SELECT*FROMt_producttpWHEREtp.cno=tc.cid);



	

	審核編輯：湯梓紅