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

第一章 SQL的介紹

1.1、什么是sql

SQL：Structure Query Language。（結(jié)構(gòu)化查詢語言）,通過sql操作數(shù)據(jù)庫(操作數(shù)據(jù)庫,操作表,操作數(shù)據(jù))

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

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

各數(shù)據(jù)庫廠商在標準的基礎(chǔ)上做了自己的擴展。各個數(shù)據(jù)庫自己特定的語法

1.2、sql的分類

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

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

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

Data Control Language(DCL 數(shù)據(jù)控制語言)，如：對用戶權(quán)限的設(shè)置

1.3、MySQL的語法規(guī)范和要求

（1）mysql的sql語法不區(qū)分大小寫

MySQL的關(guān)鍵字和函數(shù)名等不區(qū)分大小寫，但是對于數(shù)據(jù)值是否區(qū)分大小寫，和字符集與校對規(guī)則有關(guān)。

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

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

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

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

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

（6）標點符號：

必須成對

必須英文狀態(tài)下半角輸入方式

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

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

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

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

單行注釋：#注釋內(nèi)容

單行注釋：–空格注釋內(nèi)容 其中–后面的空格必須有

多行注釋：/* 注釋內(nèi)容 */

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

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

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

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

第二章-DDL操作數(shù)據(jù)庫

2.1、創(chuàng)建數(shù)據(jù)庫(掌握)

語法

createdatabase數(shù)據(jù)庫名[characterset字符集][collate校對規(guī)則]注:[]意思是可選的意思

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

練習(xí)

創(chuàng)建一個day01的數(shù)據(jù)庫（默認字符集)

createdatabaseday01;

創(chuàng)建一個day01_2的數(shù)據(jù)庫,指定字符集為gbk(了解)

createdatabaseday01_2charactersetgbk;

2.2、查看所有的數(shù)據(jù)庫

查看所有的數(shù)據(jù)庫

語法

showdatabases;

查看數(shù)據(jù)庫的定義結(jié)構(gòu)【了解】

語法

showcreatedatabase數(shù)據(jù)庫名;

查看day01這個數(shù)據(jù)庫的定義

showcreatedatabaseday01;

2.3、刪除數(shù)據(jù)庫

語法

dropdatabase數(shù)據(jù)庫名;

刪除day01_2數(shù)據(jù)庫

dropdatabaseday01_2;

2.4、修改數(shù)據(jù)庫【了解】

語法

alterdatabase數(shù)據(jù)庫名characterset字符集;

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

alterdatabaseday01charactersetgbk;

注意：

是utf8，不是utf-8

不是修改數(shù)據(jù)庫名

2.5、其他操作

切換數(shù)據(jù)庫, 選定哪一個數(shù)據(jù)庫

use數(shù)據(jù)庫名;//注意:在創(chuàng)建表之前一定要指定數(shù)據(jù)庫.use數(shù)據(jù)庫名

練習(xí): 使用day01

useday01;

查看正在使用的數(shù)據(jù)庫

selectdatabase();

第三章-DDL操作表

3.1、創(chuàng)建表

語法

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

);

類型

數(shù)值類型

整型系列：xxxInt

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

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

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

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

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

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

位類型：bit

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

用來存儲二進制數(shù)。對于位字段，直接使用select命令將不會看到結(jié)果?？梢允褂胋it()或hex()函數(shù)進行讀取。插入bit類型字段時，使用bit()函數(shù)轉(zhuǎn)為二進制值再插入，因為二進制碼是“01”。常見 SQL 面試題：經(jīng)典 50 例

日期時間類型

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

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

timestamp和datetime的區(qū)別：

timestamp范圍比較小

timestamp和時區(qū)有關(guān)

show variables like ‘time_zone’;

set time_zone = ‘+8:00’;

timestamp受MySQL版本和服務(wù)器的SQLMode影響很大

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

字符串類型

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

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值會引起一些性能問題，特別是在執(zhí)行了大量的刪除操作時，會在數(shù)據(jù)表中留下很大的“空洞”，為了提高性能，建議定期時候用optimize table功能對這類表進行碎片整理?？梢允褂煤铣傻?Synthetic)索引來提高大文本字段的查詢性能，如果需要對大文本字段進行模糊查詢，MySql提供了前綴索引。但是仍然要在不必要的時候避免檢索大型的blob或text值。

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

set集合類型，可以包含0~64個成員。一次可以從集合中選擇多個成員。如果選擇了1-8個成員的集合，占1個字節(jié)，依次占2個，3個。。8個字節(jié)。例如：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||
+----------------+--------------+------+-----+---------+----------------+

約束

即規(guī)則,規(guī)矩 限制;

作用：保證用戶插入的數(shù)據(jù)保存到數(shù)據(jù)庫中是符合規(guī)范的

約束種類:

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

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

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

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

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

注意:

先設(shè)置了primary key 再能設(shè)置auto_increment

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

id列:

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

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

練習(xí)

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

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

3.2、查看表【了解】

查看所有的表

showtables;

查看表的定義結(jié)構(gòu)

語法

desc 表名；

練習(xí): 查看student表的定義結(jié)構(gòu)

descstudent;

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

語法

增加一列

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

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

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

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

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

練習(xí)

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

ALTERTABLEstudentADDgradeVARCHAR(20)NOTNULL;

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

altertablestudentmodifygendervarchar(20);

給學(xué)生表的grade字段修改成class字段

ALTERTABLEstudentCHANGEgradeclassVARCHAR(20)NOTNULL;

把class字段刪除

ALTERTABLEstudentDROPclass;

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

RENAMETABLEstudentTOteacher;

3.4、刪除表【掌握】

語法

droptable表名；

把teacher表刪除

droptableteacher;

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

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

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

4.1、插入記錄

語法

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

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

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

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

方式二: 插入所有的列,如果哪列不想插入值,則需要賦值為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條件]

練習(xí)

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

updateproductsetprice=5000;

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

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

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

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

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

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

4.3、刪除記錄

delete

根據(jù)條件，一條一條數(shù)據(jù)進行刪除

語法

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

類型

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

deletefromproductwherepname='蘋果電腦';

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

deletefromproductwhereprice

	

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

	
deletefromproduct;


	

	truncate 把表直接DROP掉，然后再創(chuàng)建一個同樣的新表。刪除的數(shù)據(jù)不能找回。執(zhí)行速度比DELETE快

	
truncatetable表;


	

	工作中刪除數(shù)據(jù)

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

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

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

	5.1、基本查詢語法

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


	

	5.2、簡單查詢

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

	語法

	
select*form表


	

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

	
select*fromproduct;


	

	查詢某張表特定列的記錄

	語法

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


	

	查詢商品名字和價格

	
selectpname,pricefromproduct;


	

	去重查詢 distinct

	語法

	
SELECTDISTINCT字段名FROM表名;//要數(shù)據(jù)一模一樣才能去重


	

	去重查詢商品的名字

	
SELECTDISTINCTpname,priceFROMproduct


	

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

	別名查詢

	語法

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


	

	查詢商品信息，使用別名

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


	

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

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

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


	

	注意

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

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

	5.3、條件查詢(很重要)

	語法

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


	

	運算符

	1、比較運算符

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


	

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

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


	

	3、范圍

	
區(qū)間范圍：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時，用代替值計算


	

	練習(xí)

	查詢商品價格>3000的商品

	
select*fromproductwhereprice>3000;


	

	查詢pid=1的商品

	
select*fromproductwherepid=1;


	

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

	
select*fromproductwherepid<>1;


	

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

	
select*fromproductwherepricebetween3000and6000;


	

	查詢pid在1，5，7，15范圍內(nèi)的商品

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

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


	

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

	
select*fromproductwherepnamelike'iPho%';


	

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

	
select*fromproductwhereprice>3000andnum>20;


	

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

	
select*fromproductwherepid=1orprice

	

	5.4、排序查詢

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

	環(huán)境的準備

	
#創(chuàng)建學(xué)生表(有sid,學(xué)生姓名,學(xué)生性別,學(xué)生年齡,分數(shù)列,其中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:降序


	

	案例: 以分數(shù)降序查詢所有的學(xué)生

	
SELECT*FROMstudentORDERBYscoreDESC


	

	組合排序

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

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


	

	練習(xí): 以分數(shù)降序查詢所有的學(xué)生, 如果分數(shù)一致,再以age降序

	
SELECT*FROMstudentORDERBYscoreDESC,ageDESC


	

	5.5、聚合函數(shù)

	聚合函數(shù)用于統(tǒng)計，通常會和分組查詢一起使用，用于統(tǒng)計每組的數(shù)據(jù)

	聚合函數(shù)列表

	

	語法

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


	

	案例

	
--求出學(xué)生表里面的最高分數(shù)
SELECTMAX(score)FROMstudent
--求出學(xué)生表里面的最低分數(shù)
SELECTMIN(score)FROMstudent
--求出學(xué)生表里面的分數(shù)的總和(忽略null值)
SELECTSUM(score)FROMstudent
--求出學(xué)生表里面的平均分
SELECTAVG(score)FROMstudent
--求出學(xué)生表里面的平均分（缺考了當成0分處理）
SELECTAVG(IFNULL(score,0))FROMstudent
--統(tǒng)計學(xué)生的總?cè)藬?shù)(忽略null)
SELECTCOUNT(sid)FROMstudent
SELECTCOUNT(*)FROMstudent


	

	注意: 聚合函數(shù)會忽略空值NULL

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

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


	

	5.6、分組查詢

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

	分組

	語法

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


	

	案例

	
--根據(jù)性別分組,統(tǒng)計每一組學(xué)生的總?cè)藬?shù)
SELECTsex'性別',COUNT(sid)'總?cè)藬?shù)'FROMstudentGROUPBYsex

--根據(jù)性別分組，統(tǒng)計每組學(xué)生的平均分
SELECTsex'性別',AVG(score)'平均分'FROMstudentGROUPBYsex

--根據(jù)性別分組，統(tǒng)計每組學(xué)生的總分
SELECTsex'性別',SUM(score)'總分'FROMstudentGROUPBYsex


	

	分組后篩選 having

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

	根據(jù)性別分組, 統(tǒng)計每一組學(xué)生的總?cè)藬?shù)> 5的(分組后篩選)

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


	

	根據(jù)性別分組，只統(tǒng)計年齡大于等于18的，并且要求組里的人數(shù)大于4

	
SELECTsex'性別',COUNT(sid)'總?cè)藬?shù)'FROMstudentWHEREage>=18GROUPBYsexHAVINGCOUNT(sid)>4


	

	where和having的區(qū)別【面試】

	where 子句作用

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

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

	having字句作用

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

	having后面可以使用聚合函數(shù)

	5.7、分頁查詢

	語法

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


	

	案例

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

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


	

	5.8、查詢的語法小結(jié)

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

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


	

	第六章 數(shù)據(jù)庫三范式

	好的數(shù)據(jù)庫設(shè)計對數(shù)據(jù)的存儲性能和后期的程序開發(fā)，都會產(chǎn)生重要的影響。建立科學(xué)的，規(guī)范的數(shù)據(jù)庫就需要滿足一些規(guī)則來優(yōu)化數(shù)據(jù)的設(shè)計和存儲，這些規(guī)則就稱為范式。

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

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

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

	

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

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

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

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

	

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

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

	



	

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

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

	第三范式需要確保數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關(guān)，而不能間接相關(guān)。

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

	



	

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

	第七章 外鍵約束

	7.1、外鍵約束的概念

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

	7.2、外鍵約束的作用

	外鍵約束是保證一個或兩個表之間的參照完整性,外鍵是構(gòu)建于一個表的兩個字段或是兩個表的兩個字段之間的參照關(guān)系。

	7.3、創(chuàng)建外鍵約束的語法

	在建表時指定外鍵約束

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


	

	在建表后指定外鍵約束

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


	

	7.4、刪除外鍵約束的語法

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


	

	7.5、外鍵約束的要求

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

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

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

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

	7.6、外鍵約束等級

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

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

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

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

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

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

	7.7、外鍵約束練習(xí)

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


	

	第八章 多表間關(guān)系

	8.1、一對多關(guān)系

	概念

	一對多的關(guān)系是指: 主表的一行數(shù)據(jù)可以同時對應(yīng)從表的多行數(shù)據(jù)，反過來就是從表的多行數(shù)據(jù)指向主表的同一行數(shù)據(jù)。

	應(yīng)用場景

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

	建表原則

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

	

	建表語句練習(xí)

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

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


	

	8.2、多對多關(guān)系

	概念

	兩張表都是多的一方，A表的一行數(shù)據(jù)可以同時對應(yīng)B表的多行數(shù)據(jù)，反之B表的一行數(shù)據(jù)也可以同時對應(yīng)A表的多行數(shù)據(jù)

	應(yīng)用場景

	訂單表和商品表、學(xué)生表和課程表等等

	建表原則

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

	

	建表語句練習(xí)

	
--創(chuàng)建學(xué)生表
CREATETABLEstudent(
sidINTPRIMARYKEYAUTO_INCREMENT,
snameVARCHAR(50)
);

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

--創(chuàng)建中間表
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、一對一關(guān)系(了解)

	第一種一對一關(guān)系

	我們之前學(xué)習(xí)過一對多關(guān)系，在一對多關(guān)系中主表的一行數(shù)據(jù)可以對應(yīng)從表的多行數(shù)據(jù)，反之從表的一行數(shù)據(jù)則只能對應(yīng)主表的一行數(shù)據(jù)。這種一行數(shù)據(jù)對應(yīng)一行數(shù)據(jù)的關(guān)系，我們可以將其看作一對一關(guān)系

	第二種一對一關(guān)系

	A表中的一行數(shù)據(jù)對應(yīng)B表中的一行數(shù)據(jù)，反之B表中的一行數(shù)據(jù)也對應(yīng)A表中的一行數(shù)據(jù)，此時我們可以將A表當做主表B表當做從表，或者是將B表當做主表A表當做從表

	建表原則

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

	第九章 多表關(guān)聯(lián)查詢

	多表關(guān)聯(lián)查詢是使用一條SQL語句，將關(guān)聯(lián)的多張表的數(shù)據(jù)查詢出來

	9.1、環(huán)境準備

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


--創(chuàng)建一張商品表(商品id,商品名稱,商品價格,商品數(shù)量,類別.備注:商品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、交叉查詢【了解】

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

	語法

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

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


	

	練習(xí)

	使用交叉查詢類別和商品

	
select*fromt_category,t_product;


	

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

	笛卡爾積

	假設(shè)集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}?？梢詳U展到多個集合的情況。

	9.3、內(nèi)連接查詢

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

	隱式內(nèi)連接查詢

	隱式內(nèi)連接查詢里面是沒有inner join關(guān)鍵字

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


	

	顯式內(nèi)連接查詢

	顯式內(nèi)連接查詢里面是有inner join關(guān)鍵字

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


	

	內(nèi)連接查詢練習(xí)

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

	
--1隱式內(nèi)連接方式
select*fromt_categoryc,t_productpWHEREc.cid=p.cno;

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

SELECT*fromt_categorycINNERJOINt_productpONc.cid=p.cno


	

	內(nèi)連接查詢的特點

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

	9.4、外連接查詢

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

	左外連接查詢

	概念

	以join左邊的表為主表,展示主表的所有數(shù)據(jù),根據(jù)條件查詢連接右邊表的數(shù)據(jù),若滿足條件則展示,若不滿足則以null顯示?？梢岳斫鉃椋涸趦?nèi)連接的基礎(chǔ)上保證左邊表的數(shù)據(jù)全部顯示

	語法

	
select字段fromaleft[outer]joinbon條件


	

	練習(xí)

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

	
SELECT*FROMt_categorycLEFTOUTERJOINt_productpONc.cid=p.cno


	

	右外連接查詢

	概念

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

	語法

	
select字段fromaright[outer]joinbon條件


	

	練習(xí)

	查詢所有商品所對應(yīng)的類別信息

	
SELECT*FROMt_categorycRIGHTOUTERJOINt_productpONc.cid=p.cno


	

	9.5、union聯(lián)合查詢實現(xiàn)全外連接查詢

	首先要明確，聯(lián)合查詢不是多表連接查詢的一種方式。聯(lián)合查詢是將多條查詢語句的查詢結(jié)果合并成一個結(jié)果并去掉重復(fù)數(shù)據(jù)。

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

	union的語法

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


	

	練習(xí)

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


	

	9.6、自連接查詢

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

	準備工作

	
--員工表
CREATETABLEemp(
idINTPRIMARYKEY,--員工id
enameVARCHAR(50),--員工姓名
mgrINT,--上級領(lǐng)導(dǎo)
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,'關(guān)羽',1007,'2002-01-23','13000.00');
#查詢孫悟空的上級
SELECTemployee.*,manager.enamemgrnameFROMempemployee,empmanagerwhereemployee.mgr=manager.idANDemployee.ename='孫悟空'


	

	自連接查詢練習(xí)

	查詢員工的編號，姓名，薪資和他領(lǐng)導(dǎo)的編號，姓名，薪資

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

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


	

	第十章 子查詢

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

	10.1、where型

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

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


	

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

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


	

	10.2、from型

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

	必須給子查詢?nèi)e名，即臨時表名，表的別名不要加“”和空格。

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

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


	

	10.3、exists型

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



	

	審核編輯：湯梓紅