MySQL基礎(chǔ)知識(shí)總結(jié)

dayl5-MySQL

數(shù)據(jù)庫(kù)

i數(shù)據(jù)庫(kù)概念（了解）

l.i什么是數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)就是用來(lái)存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)!

數(shù)據(jù)庫(kù)存儲(chǔ)數(shù)據(jù)的優(yōu)先：

?可存儲(chǔ)大量數(shù)據(jù)；

?方便檢索；

?保持?jǐn)?shù)據(jù)的一致性、完整性；

?安全，可共享；

?通過(guò)組合分析，可產(chǎn)生新數(shù)據(jù)。

1.2數(shù)據(jù)庫(kù)的發(fā)展歷程

?沒(méi)有數(shù)據(jù)庫(kù)，使用磁盤文件存儲(chǔ)數(shù)據(jù)；

?層次結(jié)構(gòu)模型數(shù)據(jù)庫(kù)；

?網(wǎng)狀結(jié)構(gòu)模型數(shù)據(jù)庫(kù)；

?關(guān)系結(jié)構(gòu)模型數(shù)據(jù)庫(kù)：使用二維表格來(lái)存儲(chǔ)數(shù)據(jù);

?關(guān)系?對(duì)象模型數(shù)據(jù)庫(kù)；

MySQL就是關(guān)系型數(shù)據(jù)庫(kù)！

1.3常見(jiàn)數(shù)據(jù)庫(kù)

?Oracle(神喻)：甲骨文(最高！)：

?DB2：IBM；

?SQLServer：微軟；

?Sybase：賽爾斯；

?MySQL：甲骨文；

1.4理解數(shù)據(jù)庫(kù)

?RDBMS=管理員(manager)+倉(cāng)庫(kù)(database)

?database=N個(gè)table

?table：

＞表結(jié)構(gòu)：定義表的列名和列類型！

＞表記錄：一行一行的記錄！

我們現(xiàn)在所說(shuō)的數(shù)據(jù)庫(kù)泛指“關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)(RDBMS-Relationaldatabasemanagement

system)”，即“數(shù)據(jù)庫(kù)服務(wù)器”。

當(dāng)我們安裝了數(shù)據(jù)庫(kù)服務(wù)器后，就可以在數(shù)據(jù)庫(kù)服務(wù)器中創(chuàng)建數(shù)據(jù)庫(kù)，每個(gè)數(shù)據(jù)庫(kù)中還可以包

含多張表。

數(shù)據(jù)庫(kù)表就是一個(gè)多行多列的表格。在創(chuàng)建表時(shí)，需要指定表的列數(shù)，以及列名稱，列類型等

信息。而不用指定表格的行數(shù)，行數(shù)是沒(méi)有上限的。下面是tab_student表的結(jié)構(gòu)：

s_idvarchar(lO)

s_namevarchar(20)

s_ageint

s_sexvarchar(lO)

當(dāng)把表格創(chuàng)建好了之后，就可以向表格中添加數(shù)據(jù)了。向表格添加數(shù)據(jù)是以行為單位的！下面

是s_student表的記錄：

s_ids_names_ages_sex

S_1001zhangSan23male

S_1002HSi32female

S_1003wangWu44male

大家要學(xué)會(huì)區(qū)分什么是表結(jié)構(gòu)，什么是表記錄。

操作。登錄MySQL需要使用MySQL的客戶端程序：mysql.exe

?登錄：mysql-uroot-p123-hlocalhost；

>-u：后面的root是用戶名，這里使用的是超級(jí)管理員root；

>-p：后面的123是密碼，這是在安裝MySQL時(shí)就已經(jīng)指定的密碼；

>-h：后面給出的localhost是服務(wù)器主機(jī)名，它是可以省略的，例如：mysql-uroot-p123：

?退出：quit或exit；

在登錄成功后，打開(kāi)windows任務(wù)管理器，會(huì)有一個(gè)名為mysql.exe的進(jìn)程運(yùn)行，所以mysql.exe

是客戶端程序。

SQL語(yǔ)句

1SQL概述

1.1什么是SQL

SQL(StructuredQueryLanguage)是“結(jié)構(gòu)化查詢語(yǔ)言”，它是對(duì)關(guān)系型數(shù)據(jù)庫(kù)的操作語(yǔ)言。它

可以應(yīng)用到所有關(guān)系型數(shù)據(jù)庫(kù)中，例如:MySQL、Oracle>SQLServer等。SQ標(biāo)準(zhǔn)(ANSI/ISO)有:

?SQL-92：1992年發(fā)布的SQL語(yǔ)言標(biāo)準(zhǔn)；

?SQL:1999：1999年發(fā)布的SQL語(yǔ)言標(biāo)簽；

?SQL2003：2003年發(fā)布的SQL語(yǔ)言標(biāo)簽；

這些標(biāo)準(zhǔn)就與JDK的版本一樣，在新的版本中總要有一些語(yǔ)法的變化。不同時(shí)期的數(shù)據(jù)庫(kù)對(duì)不

同標(biāo)準(zhǔn)做了實(shí)現(xiàn)。

雖然SQL可以用在所有關(guān)系型數(shù)據(jù)庫(kù)中，但很多數(shù)據(jù)庫(kù)還都有標(biāo)準(zhǔn)之后的一些語(yǔ)法，我們可以

稱之為“方言”。例如MySQL中的LIMIT語(yǔ)句就是MySQL獨(dú)有的方言，其它數(shù)據(jù)庫(kù)都不支持！當(dāng)然,

Oracle或SQLServer都有自己的方言。

1.2語(yǔ)法要求

?SQL語(yǔ)句可以單行或多行書寫，以分號(hào)結(jié)尾;

?可以用空格和縮進(jìn)來(lái)來(lái)增強(qiáng)語(yǔ)句的可讀性；

?關(guān)鍵字不區(qū)別大小寫，建議使用大寫；

2分類

?DDL(DataDefinitionLanguage)：數(shù)據(jù)定義語(yǔ)言，用來(lái)定義數(shù)據(jù)庫(kù)對(duì)象：庫(kù)、表、列等;

?DML(DataManipulationLanguage)：數(shù)據(jù)操作語(yǔ)言，用來(lái)定義數(shù)據(jù)庫(kù)記錄(數(shù)據(jù))：

?DCL(DataControlLanguage)：數(shù)據(jù)控制語(yǔ)言，用來(lái)定義訪問(wèn)權(quán)限和安全級(jí)別；

?DQL(DataQueryLanguage)：數(shù)據(jù)查詢語(yǔ)言，用來(lái)查詢記錄(數(shù)據(jù))。

3DDL

3.1基本操作

?查看所有數(shù)據(jù)庫(kù)名稱：SHOWDATABASES：

?切換數(shù)據(jù)庫(kù)：USEmydbl,切換到mydbl數(shù)據(jù)庫(kù)；

3.2操作數(shù)據(jù)庫(kù)

?創(chuàng)建數(shù)據(jù)庫(kù)：CREATEDATABASE[IFNOTEXISTS]mydbl；

創(chuàng)建數(shù)據(jù)庫(kù)，例如：CREATEDATABASEmydbl,創(chuàng)建一個(gè)名為mydbl的數(shù)據(jù)庫(kù)。如果這個(gè)數(shù)據(jù)

已經(jīng)存在，那么會(huì)報(bào)錯(cuò)。例如CREATEDATABASEIFNOTEXISTSmydbl,在名為mydbl的數(shù)據(jù)庫(kù)不存

在時(shí)創(chuàng)建該庫(kù)，這樣可以避免報(bào)錯(cuò)。

?刪除數(shù)據(jù)庫(kù)：DROPDATABASE[IFEXISTS]mydbl；

刪除數(shù)據(jù)庫(kù)，例如：DROPDATABASEmydbl,刪除名為mydbl的數(shù)據(jù)庫(kù)。如果這個(gè)數(shù)據(jù)庫(kù)不存

在，那么會(huì)報(bào)錯(cuò)。DROPDATABASEIFEXISTSmydbl,就算mydbl不存在，也不會(huì)的報(bào)錯(cuò)。

?修改數(shù)據(jù)庫(kù)編碼：ALTERDATABASEmydblCHARACTERSETutf8

修改數(shù)據(jù)庫(kù)mydbl的編碼為utf8,注意，在MySQL中所有的UTF-8編碼都不能使用中間的

即UTF-8要書寫為UTF8o

3.3數(shù)據(jù)類型

MySQL與Java一樣，也有數(shù)據(jù)類型。MySQL中數(shù)據(jù)類型主要應(yīng)用在列上。

常用類型：

?int：整型

?double：浮點(diǎn)型，例如double[5,2）表示最多5位，其中必須有2位小數(shù)，即最大值為999.99；

?decimal：泛型型，在表單錢方面使用該類型，因?yàn)椴粫?huì)出現(xiàn)精度缺失問(wèn)題；

?char：固定長(zhǎng)度字符串類型；

?varchar：可變長(zhǎng)度字符串類型；

?text：字符串類型；

?blob：字節(jié)類型；

?date：日期類型，格式為：yyyy-MM-dd；

?time：時(shí)間類型，格式為：hh:mm:ss

?timestamp：時(shí)間戳類型；

3.4操作表

?創(chuàng)建表：

CREATETABLE表名（

列名列類型，

列名列類型，

);

例如:

CREATETABLEStU(

sidCHAR(6),

snameVARCHAR(20),

ageINT,

genderVARCHAR(IO)

)；

再例如:

CREATETABLEemp(

eidCHAR(6),

enameVARCHAR(50),

ageINT,

genderVARCHAR(6h

birthdayDATE,

hiredateDATE,

salaryDECIMAL。,2),

resumeVARCHAR(IOOO)

?查看當(dāng)前數(shù)據(jù)庫(kù)中所有表名稱：SHOWTABLES；

?查看指定表的創(chuàng)建語(yǔ)句：SHOWCREATETABLEemp,查看emp表的創(chuàng)建語(yǔ)句;

?查看表結(jié)構(gòu)：DESCemp,查看emp表結(jié)構(gòu)；

?刪除表：DROPTABLEemp,刪除emp表；

?修改表：

1.修改之添加列：給stu表添加classname列：

ALTERTABLEstuADD(classnamevarchar(lOO));

2.修改之修改列類型：修改stu表的gender列類型為CHAR(2)：

ALTERTABLEstuMODIFYgenderCHAR(2);

3.修改之修改列名：修改stu表的gender列名為sex：

ALTERTABLEstuchangegendersexCHAR(2);

4.修改之刪除列：刪除stu表的classname列：

ALTERTABLEstuDROPclassname;

5.修改之修改表名稱：修改stu表名稱為student：

ALTERTABLEstuRENAMETOstudent;

4DML

4.1插入數(shù)據(jù)

語(yǔ)法：

INSERTINTO表名(列名1,列名2,…)VALUES(值1,值2)

|INSERTINTOstu(sid,sname,age,gender)VALUESCS_lOOlLzhangSan',

INSERTINTOstu(sidzsname)VALUESCs.lOOl','zhangSan');

語(yǔ)法：

INSERTINTO表名VALUES（值I,值2,…）

因?yàn)闆](méi)有指定要插入的列，表示按創(chuàng)建表時(shí)列的順序插入所有列的值:

INSERTINTOstuVALUESCs.lOOZ','liSi',32,'female'）;

注意：所有字符串?dāng)?shù)據(jù)必須使用單引用!

4.2修改數(shù)據(jù)

語(yǔ)法：

UPDATE表名SET列名1=值1,…列名出值n［WHERE條件］

UPDATEstuSETsname^zhangSanSan^age='32',gender^female"WHEREsid="s_1001";

UPDATEstuSETsname='liSi',age='2O'WHEREage>50ANDgender^male^

UPDATEstuSETsname='wangWu',age='3O'WHEREage>60ORgender=*female/;

UPDATEstuSETgender^female,WHEREgenderISNULL

UPDATEstuSETage=age+lWHEREsname="zhaoLiu,;

4.3刪除數(shù)據(jù)

語(yǔ)法：

DELETEFROM表名［WHERE條件］

DELETEFROMstuWHEREsid='s1001'003B

DELETEFROMstuWHEREsname=/chenQi/ORage>30;

DELETEFROMstu;

語(yǔ)法：

TRUNCATETABLE表名

TRUNCATETABLEstu;

雖然TRUNCATE和DELETE都可以刪除表的所有記錄,但有原理不同。DELETE的效率沒(méi)有

TRUNCATE高!

TRUNCATE其實(shí)屬性DDL語(yǔ)句，因?yàn)樗窍菵ROPTABLE,再CREATETABLEo而且TRUNCATE冊(cè)I]

除的記錄是無(wú)法回滾的，但DELETE刪除的記錄是可以回滾的（回滾是事務(wù)的知識(shí)！

5DCL

5.1創(chuàng)建用戶

語(yǔ)法：

CREATEUSER用戶名@地址IDENTIFIEDBY1密碼,;

CREATEUSERuserl@localhostIDENTIFIEDBY'123';

CREATEUSERuser2@'%'IDENTIFIEDBY'123';

5.2給用戶授權(quán)

語(yǔ)法：

GRANT權(quán)限1,...,權(quán)限nON數(shù)據(jù)庫(kù).*TO用戶名

GRANTCREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECTONmydbl.*TOuserl@localhost;

GRANTALLONmydbl.*TOuser2@localhost;

5.3撤銷授權(quán)

語(yǔ)法：

REVOKE權(quán)限1,一權(quán)限nON數(shù)據(jù)庫(kù).*FORM用戶名

REVOKECREATE,ALTER,DROPONmydbl.*FROMuserl@localhost;

5.4查看用戶權(quán)限

語(yǔ)法：

SHOWGRANTSFOR用戶名

SHOWGRANTSFORuserl@localhost;

5.5刪除用戶

語(yǔ)法：

DROPUSER用戶名

DROPUSERuserl@localhost;

5.6修改用戶密碼

語(yǔ)法：

USEmysql;

UPDATEUSERSETPASSWORD=PASSWORD('密碼')WHEREUser='用戶名'andHost='IP’;

FLUSHPRIVILEGES;

UPDATEUSERSETPASSWORD=PASSWORD('1234')WHEREUser='user2aandHost^localhost*;

FLUSHPRIVILEGES;

數(shù)據(jù)查詢語(yǔ)法（DQL）

DQL就是數(shù)據(jù)查詢語(yǔ)言，數(shù)據(jù)庫(kù)執(zhí)行DQL語(yǔ)句不會(huì)對(duì)數(shù)據(jù)進(jìn)行改變，而是讓數(shù)據(jù)庫(kù)發(fā)送結(jié)果集

給客戶端。

語(yǔ)法：

SELECTselectionjist/*要查詢的列名稱*/

FROMtablejist/*要查詢的表名稱*/

WHEREcondition/*行條件

GROUPBYgrouping_columns/*對(duì)結(jié)果分組*/

HAVINGcondition/*分組后的行條件*/

ORDERBYsorting_columns/*對(duì)結(jié)果分組*/

LIMIToffset_start,row_count/*結(jié)果限定*/

創(chuàng)建名：

?學(xué)生表：Stu

〔字段名稱字段類型說(shuō)明

sidchar(6)學(xué)生學(xué)號(hào)

snamevarchar(SO)學(xué)生姓名

ageint學(xué)生年齡

gendervarchar(SO)學(xué)生性別

CREATETABLEstu(

sidCHAR(6),

snameVARCHAR(50)z

ageINT

genderVARCHAR(50)

)；

INSERTINTOstuVALUESCS.lOOl','liuYi',35,'male');

INSERTINTOstuVALUES('S_1002'/'chenEr',15,'female');

INSERTINTOstuVALUES('S_1003'z'zhangSan',95,'male');

,

INSERTINTOstuVALUES(S_1004'/'liSi；65z'female')；

INSERTINTOstuVALUES('S_1005\'wangWu；55,'male');

INSERTINTOstuVALUESCS.IOOS','zhaoLiu',75,'female');

INSERTINTOstuVALUES('S_1007','sunQj',25,'male');

INSERTINTOstuVALUES('S_1008'z'zhouBa',45,'female');

INSERTINTOstuVALUES('S_1009','wuJiu',85,'male')；

INSERTINTOstuVALUESCS.IOIO；'zhengShi',5,'female')；

INSERTINTOstuVALUESCS.lOll；'xxx',NULL,NULL);

?雇員表：emp

|字段名稱字段類型說(shuō)明

empnoint員工編號(hào)

enamevarchar(SO)員工姓名

jobvarchar(5O)員工工作

mgrint領(lǐng)導(dǎo)編號(hào)

hiredatedate入職日期

saldecimal(7,2)月薪

commdecimal。,2)獎(jiǎng)金

deptnoint部分編號(hào)

CREATETABLEemp(

empnoINI；

enameVARCHAR(50),

jobVARCHAR(50),

mgrINI

hiredateDATE,

salDECIMAL(7,2),

commdecimal。,2),

deptnoINT

）；

,,

INSERTNTOempvalues(7369；SMITH；CLERK/7902,,1980-12-17',800,NULL/20);

INSERTNTOempvalues(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);

,,

INSERTNTOempvalues(7521/WARD；SALESMAN77698；1981-02-22',1250,500,30);

,,

INSERTNTOempvalues(7566；JONES；MANAGER'z7839；1981-04-02/2975,NULL/20);

INSERTNTOempvalues(7654,'MARTN,SALESMAN7698,'1981-09-28',1250,1400,30);

INSERTNT。empvalues(7698,'BLAKE','MANAGER',7839,'1981-05?01',2850,NULL,30);

INSERTNTOempvalues(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);

INSERTNTOempvalues(7788,SCOTT,'ANALYST,7566,,1987-04-19',3000,NULL,20);

INSERTNTOempvalues(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

INSERTNTOempvalues(7844；TURNER,.,SALESMAN,,7698；1981-09-08',1500,0,30);

INSERTNTOempvalues(7876,'ADAMS','CLERK,,7788,'1987-05-23',1100,NULL,20);

,,,

INSERTNTOempvalues(7900；JAMES；CLERK/7698；1981-12-03,950,NULL,30);

,,,

INSERTNTOempvalues(7902/'FORD；ANALYST/7566；1981-12-03,3000zNULL..20);

,,

INSERTNTOempvalues(7934zMILLER'/'CLERK/7782；1982-01-23'z1300/NULL,10);

?部分表：dept

字段名稱字段類型說(shuō)明

deptnoint部分編碼

dnamevarchar(SO)部分名稱

locvarchar(50)部分所在地點(diǎn)

CREATETABLEdept(

deptnoIN1

dnamevarchar(14),

locvarchar(13)

INSERTINTOdeptvalues(10,'ACCOUNTING；'NEWYORK');

INSERTINTOdeptvalues(20,'RESEARCH；'DALLAS');

INSERTINTOdeptvalues(30,'SALES；'CHICAGO');

INSERTINTOdeptvalues(40,'OPERATIONS','BOSTON');

1基礎(chǔ)查詢

1.1查詢所有列

SELECT*FROMstu;

1.2查詢指定列

SELECTsid,sname,ageFROMstu;

2條件查詢

2.1條件查詢介紹

條件查詢就是在查詢時(shí)給出WHERE子句，在WHERE子句中可以使用如下運(yùn)算符及關(guān)鍵字:

?=、！=、<>、<、<=、>、>=；

?BETWEEN...AND：

?IN(set)；

?ISNULL：

?AND；

?OR：

?NOT；

2.2杳詢性別為女，并且年齡50的記錄

SELECT*FROMstu

WHEREgender="female'ANDge<50;

2.3杳詢學(xué)號(hào)為S/001,或者姓名為HSi的記錄

SELECT*FROMstu

WHEREsid='S_1001'ORsname='liSi';

2.4查詢學(xué)號(hào)為S」001,S_1002,S_1003的記錄

SELECT*FROMstu

WHEREsidIN('S_10017S_10027S_1003');

2.5查詢學(xué)號(hào)不是S_1001,S_1002,S_1003的記錄

SELECT*FROMtab_student

WHEREs_numberNOTIN(5_1001',5_1002',5_1003');

2.6查詢年齡為null的記錄

SELECT*FROMstu

WHEREageISNULL;

2.7查詢年齡在20到40之間的學(xué)生記錄

SELECT*

FROMstu

WHEREage>=20ANDage<=40;

或者

SELECT*

FROMstu

WHEREageBETWEEN20AND40;

2.8查詢性別非男的學(xué)生記錄

SELECT*

FROMstu

WHEREgender!='male,;

或者

SELECT*

FROMstu

WHEREgendero'male';

或者

SELECT*

FROMstu

WHERENOTgender='male';

2.9查詢姓名不為null的學(xué)生記錄

SELECT*

FROMstu

WHERENOTsnameISNULL;

或者

SELECT*

FROMstu

WHEREsnameISNOTNULL;

3模糊查詢

當(dāng)想查詢姓名中包含a字母的學(xué)生時(shí)就需要使用模糊查詢了。模糊查詢需要使用關(guān)鍵字LIKEo

3.1查詢姓名由5個(gè)字母構(gòu)成的學(xué)生記錄

SELECT*

FROMstu

WHEREsnameLIKE'

模糊查詢必須使用LIKE關(guān)鍵字。其中匹配任意一個(gè)字母，5個(gè)表示5個(gè)任意字母。

3.2查詢姓名由5個(gè)字母構(gòu)成，并且第5個(gè)字母為“i”的學(xué)生記錄

SELECT*

FROMstu

WHEREsnameLIKE'i';

3.3查詢姓名以“z”開(kāi)頭的學(xué)生記錄

SELECT*

FROMstu

WHEREsnameLIKE'z%';

其中"％”匹配0~n個(gè)任何字母。

3.4查詢姓名中第2個(gè)字母為“i”的學(xué)生記錄

SELECT*

FROMstu

WHEREsnameLIKE

3.5查詢姓名中包含“a”字母的學(xué)生記錄

SELECT*

FROMstu

WHEREsnameLIKE

4字段控制查詢

4.1去除重復(fù)記錄

去除重復(fù)記錄（兩行或兩行以上記錄中系列的上的數(shù)據(jù)都相同），例如emp表中sal字段就存在

相同的記錄。當(dāng)只查詢emp表的sal字段時(shí)，那么會(huì)出現(xiàn)重復(fù)記錄，那么想去除重復(fù)記錄，需要使

用DISTINCT：

SELECTDISTINCTsalFROMemp;

4.2查看雇員的月薪與傭金之和

因?yàn)閟al和comm兩列的類型都是數(shù)值類型，所以可以做加運(yùn)算。如果sal或comm中有一個(gè)字

段不是數(shù)值類型，那么會(huì)出錯(cuò)。

SELECT*,sal+commFROMemp;

comm列有很多記錄的值為NULL.因?yàn)槿魏螙|西與NULL相加結(jié)果還是NULL,所以結(jié)算結(jié)果可

能會(huì)出現(xiàn)NULLo下面使用了把NULL轉(zhuǎn)換成數(shù)值0的函數(shù)IFNULL：

SELECT*,sal+IFNULL(comm,0)FROMemp;

4.3給列名添加別名

在上面查詢中出現(xiàn)列名為sal+IFNUU(comm,0),這很不美觀，現(xiàn)在我們給這一列給出一個(gè)別名，

為total：

SELECT*,sal+IFNULL(comm,0)AStotalFROMemp;

給列起別名時(shí)，是可以省略AS關(guān)鍵字的：

SELECT*/sal+IFNULL(comm/0)totalFROMemp;

5排序

5.1查詢所有學(xué)生記錄，按年齡升序排序

SELECT*

FROMstu

ORDERBYsageASC;

或者

SELECT*

FROMstu

ORDERBYsage;

5.2查詢所有學(xué)生記錄，按年齡降序排序

SELECT*

FROMstu

ORDERBYageDESC;

5.3查詢所有雇員，按月薪降序排序，如果月薪相同時(shí)，按編號(hào)升序排序

SELECT*FROMemp

ORDERBYsalDES^empnoASC;

6聚合函數(shù)

聚合函數(shù)是用來(lái)做縱向運(yùn)算的函數(shù)：

?COUNT()：統(tǒng)計(jì)指定列不為NULL的記錄行數(shù)：

?MAX()：計(jì)算指定列的最大值，如果指定列是字符串類型，那么使用字符串排序運(yùn)算;

?MINO：計(jì)算指定列的最小值，如果指定列是字符串類型，那么使用字符串排序運(yùn)算；

?SUM()：計(jì)算指定列的數(shù)值和，如果指定列類型不是數(shù)值類型，那么計(jì)算結(jié)果為0；

?AVGO：計(jì)算指定列的平均值，如果指定列類型不是數(shù)值類型，那么計(jì)算結(jié)果為0；

6.1COUNT

當(dāng)需要縱向統(tǒng)計(jì)時(shí)可以使用COUNT()o

?查詢emp表中記錄數(shù)：

SELECTCOUNT(*)ASentFROMemp;

?查詢emp表中有傭金的人數(shù)：

SELECTCOUNT(comm)entFROMemp;

注意，因?yàn)閏ount。函數(shù)中給出的是comm列，那么只統(tǒng)計(jì)comm列非NULL的行數(shù)。

?查詢emp表中月薪大于2500的人數(shù)：

SELECTCOUNT(*)FROMemp

WHEREsal>2500;

?統(tǒng)計(jì)月薪與傭金之和大于2500元的人數(shù)：

SELECTCOUNT(*)ASentFROMempWHEREsal+IFNULUcomm/O)>2500;

?查詢有傭金的人數(shù)，以及有領(lǐng)導(dǎo)的人數(shù)：

SELECTCOUNT(comm),COUNT(mgr)FROMemp;

6.2SUM和AVG

當(dāng)需要縱向求和時(shí)使用sum()函數(shù)。

?查詢所有雇員月薪和：

SELECTSUM(sal)FROMemp;

?查詢所有雇員月薪和，以及所有雇員傭金和：

SELECTSUM(sal),SUM(comm)FROMemp;

?查詢所有雇員月薪+傭金和：

SELECTSUMfsakIFNULUcomm.O))FROMemp;

?統(tǒng)計(jì)所有員工平均工資：

SELECTSUM(sal),COUNT(sal)FROMemp;

或者

SELECTAVG(sal)FROMemp;

6.3MAX和MIN

?查詢最高工資和最低工資：

SELECTMAX(sal),MIN(sal)FROMemp;

7分組查詢

當(dāng)需要分組查詢時(shí)需要使用GROUPBY子句，例如查詢每個(gè)部門的工資和，這說(shuō)明要使用部分

來(lái)分組。

7.1分組查詢

?查詢每個(gè)部門的部門編號(hào)和每個(gè)部門的工資和；

SELECTdeptno,SUM(sal)

FROMemp

GROUPBYdeptno;

?查詢每個(gè)部門的部門編號(hào)以及每個(gè)部門的人數(shù)：

SELECTdeptno,COUNT(*)

FROMemp

GROUPBYdeptno;

?查詢每個(gè)部門的部門編號(hào)以及每個(gè)部門工資大于1500的人數(shù)：

SELECTdeptno^OUNTf*)

FROMemp

WHEREsal>1500

GROUPBYdeptno;

7.2HAVING子句

?查詢工資總和大于9000的部門編號(hào)以及工資和：

SELECTdeptno,SUM(sal)

FROMemp

GROUPBYdeptno

HAVINGSUM(sal)>9000;

注意，WHERE是對(duì)分組前記錄的條件，如果某行記錄沒(méi)有滿足WHERE子句的條件，那么這行

記錄不會(huì)參加分組：而HAVING是對(duì)分組后數(shù)據(jù)的約束。

8LIMIT

LIMIT用來(lái)限定查詢結(jié)果的起始行，以及總行數(shù)。

8.1查詢5行記錄，起始行從0開(kāi)始

SELECT*FROMempLIMIT0,5;

注意，起始行從0開(kāi)始，即第一行開(kāi)始！

8.2查詢10行記錄，起始行從3開(kāi)始

SELECT*FROMempLIMIT3,10;

8.3分頁(yè)查詢

如果一頁(yè)記錄為10條，希望查看第3頁(yè)記錄應(yīng)該怎么查呢？

?第一頁(yè)記錄起始行為0,一共查詢10行；

?第二頁(yè)記錄起始行為10,一共查詢10行；

?第三頁(yè)記錄起始行為20,一共查詢10行；

完整性約束

完整性約束是為了表的數(shù)據(jù)的正語(yǔ)性！如果數(shù)據(jù)不正確，那么一開(kāi)始就不能添加到表中。

1主鍵

當(dāng)某一列添加了主鍵約束后，那么這一列的數(shù)據(jù)就不能重復(fù)出現(xiàn)。這樣每行記錄中其主鍵列的

值就是這一行的唯一標(biāo)識(shí)。例如學(xué)生的學(xué)號(hào)可以用來(lái)做唯一標(biāo)識(shí)，而學(xué)生的姓名是不能做唯一標(biāo)識(shí)

的，因?yàn)閷W(xué)習(xí)有可能同名。

主鍵列的值不能為NULL,也不能重愛(ài)！

指定主鍵約束使用PRIMARYKEY關(guān)鍵字

?創(chuàng)建表：定義列時(shí)指定主鍵：

CREATETABLEstu(

sidCHAR(6)PRIMARYKEY；

snameVARCHAR(20)z

ageINI

genderVARCHAR(IO)

)；

?創(chuàng)建表：定義列之后獨(dú)立指定主鍵：

CREATETABLEstu(

sidCHAR(6),

snameVARCHAR(20h

ageINI；

genderVARCHAR(IO),

PRIMARYKEY(sid)

)；

?修改表時(shí)指定主鍵：

AUERTABLEstu

ADDPRIMARYKEY(sid);

?刪除主鍵(只是刪除主鍵約束，而不會(huì)刪除主鍵列)：

ALTERTABLEstuDROPPRIMARYKEY;

2主鍵自增長(zhǎng)

MySQL提供了主鍵自動(dòng)增長(zhǎng)的功能！這樣用戶就不用再為是否有主鋌是否重復(fù)而煩惱了。當(dāng)主

鍵設(shè)置為自動(dòng)增長(zhǎng)后，在沒(méi)有給出主鍵值時(shí)，主鍵的值會(huì)自動(dòng)生成，而且是最大主鍵值+1，也就不

會(huì)出現(xiàn)重復(fù)主鍵的可能了。

?創(chuàng)建表時(shí)設(shè)置主鍵自增長(zhǎng)(主鍵必須是整型才可以自增長(zhǎng))：

CREATETABLEstu(

sidINTPRIMARYKEYAUTOJNCREMENT,

snameVARCHAR(20),

ageINT；

genderVARCHAR(IO)

)；

?修改表時(shí)設(shè)置主鍵自增長(zhǎng)：

ALTERTABLEstuCHANGEsidsidINTAUTOJNCREMENT;

?修改表時(shí)刪除主鍵自增長(zhǎng)：

ALTERTABLEstuCHANGEsidsidINT;

3非空

指定非空約束的列不能沒(méi)有值，也就是說(shuō)在插入記錄時(shí)，對(duì)添加了非空約束的列一定要給值；

在修改記錄時(shí)，不能把非空列的值設(shè)置為NULL。

?指定非空約束：

CREATETABLEstu(

sidINTPRIMARYKEYAUTOJNCREMENT,

snameVARCHAR(IO)NOTNULL,

ageINI

genderVARCHAR(IO)

當(dāng)為sname字段指定為非空后，在向stu表中插入記錄時(shí)，必須給sname字段指定值，否則會(huì)

報(bào)錯(cuò)：

INSERTINTOstu(sid)VALUES⑴；

插入的記錄中sname沒(méi)有指定值，所以會(huì)報(bào)錯(cuò)！

4唯一

還可以為字段指定唯一約束！當(dāng)為字段指定唯一約束后，那么字段的值必須是唯一的。這一點(diǎn)

與主鍵相似！例如給stu表的sname字段指定唯一約束：

CREATETABLEtab_ab(

sidINTPRIMARYKEYAUTOJNCREMENT,

snameVARCHAR(IO)UNIQUE

)；

INSERTINTOsname(sid,sname)VALUESflOOl,'zs');

INSERTINTOsname(sid,sname)VALUES(1002r'zs');

當(dāng)兩次插入相同的名字時(shí)，MySQL會(huì)報(bào)錯(cuò)！

5外鍵

主外鍵是構(gòu)成表與表關(guān)聯(lián)的唯一途徑！

外鍵是另一張表的主鍵！例如員工表與部門表之間就存在關(guān)聯(lián)關(guān)系，其中員工表中的部門編號(hào)

字段就是外鍵，是相對(duì)部門表的外鍵。

我們?cè)賮?lái)看BBS系統(tǒng)中：用戶表(t_user)、分類表(t_section)、帖子表(t_topic)三者之間的

關(guān)系。

例如在t_section表中sid為1的記錄說(shuō)明有一個(gè)分類叫java,版主是t_user表中uid為1的用戶，

即zs!

例如在t_topic表中tid為2的記錄是名字為“Java是咖啡”的帖子，它是java版塊的帖子，它

的作者是ww.

外鍵就是用來(lái)約束這一列的值必須是另一張表的主鍵值！??！

?創(chuàng)建t_user表，指定uid為主鍵列：

CREATETABLEt_user(

uidINTPRIMARYKEYAUTOJNCREMENT,

unameVARCHAR(20)UNIQUENOTNULL

)；

?創(chuàng)建t_section表，指定sid為主鍵列，ujd為相對(duì)t_user表的uid列的外鍵：

CREATETABLEt_section(

sidINTPRIMARYKEYAUTOJNCREMENT,

snameVARCHAR(30),

UjdINI；

CONSTRAINTfk_t_userFOREIGNKEY(u_id)REFERENCESt_user(uid)

)；

?修改t_section表，指定u_id為相對(duì)t_user表的uid列的外鍵：

AUERTABLEt_section

ADDCONSTRAINTfk_t_user

FOREIGNKEY(u_id)

REFERENCESt_user(uid);

?修改t_section表，刪除u_id的外鍵約束：

AUERTABLEt_section

DROPFOREIGNKEYfk_t_user;

6表與表之間的關(guān)系

?一對(duì)一：例如jperson表和jcard表，即人和身份證。這種情況需要找出主從關(guān)系，即誰(shuí)

是主表，誰(shuí)是從表。人可以沒(méi)有身份證，但身份證必須要有人才行，所以人是主表，而身

份證是從表。設(shè)計(jì)從表可以有兩種方案：

>在t_card表中添加外鍵列（相對(duì)t_user表），并且給外鍵添加唯一約束；

>給t_card表的主鍵添加外鍵約束（相對(duì)Juser表），即t_card表的主鍵也是外鍵。

?一對(duì)多（多對(duì)一）：最為常見(jiàn)的就是一對(duì)多！一對(duì)多和多對(duì)一，這是從哪個(gè)角度去看得出來(lái)

的。t_user和t_section的關(guān)系，從t_user來(lái)看就是一對(duì)多，而從t_section的角度來(lái)看就是

多對(duì)一！這種情況都是在多方創(chuàng)建外鍵！

?多對(duì)多：例如t_stu和Jteacher表，即一個(gè)學(xué)生可以有多個(gè)老師，而一個(gè)老師也可以有多

個(gè)學(xué)生。這種情況通常需要?jiǎng)?chuàng)建中間表來(lái)處理多對(duì)多關(guān)系。例如再創(chuàng)建一張表t_stu_tea表,

給出兩個(gè)外鍵，一個(gè)相對(duì)tstu表的外鍵，另一個(gè)相對(duì)tteacher表的外鍵。

編碼

1查看MySQL編碼

SHOWVARIABLESLIKE'char%";

nysql>showvariableslike,charz,；

V

■Uariable_nanie■Ualue

icharactei*_set_client!ut￡8

■character_set_connection!utf8

!character.set-database!utf8

!chai'actei*_set_filesystem!binary

!character-set-results!ut￡8

!character_set_seruer!utf8

icharacter_set_system!utf8

■charactei*_sets_dii*!D:\Progran

IZ------------------------------------------------------------------------1

因?yàn)楫?dāng)初安裝時(shí)指定了字符集為UTF8,所以所有的編碼都是UTF8。

?character_set_client：你發(fā)送的數(shù)據(jù)必須與client指定的編碼一致！”服務(wù)器會(huì)使用該編碼

來(lái)解讀客戶端發(fā)送過(guò)來(lái)的數(shù)據(jù)；

?character_set_connection：通過(guò)該編碼與client一致！該編碼不會(huì)導(dǎo)致亂碼！當(dāng)執(zhí)行的是查

詢語(yǔ)句時(shí)，客戶端發(fā)送過(guò)來(lái)的數(shù)據(jù)會(huì)先轉(zhuǎn)換成connection指定的編碼。但只要客戶端發(fā)送

過(guò)來(lái)的數(shù)據(jù)與client指定的編碼一致，那么轉(zhuǎn)換就不會(huì)出現(xiàn)問(wèn)題：

?character_set_database：數(shù)據(jù)庫(kù)默認(rèn)編碼，在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，如果沒(méi)有指定編碼，那么默認(rèn)

使用database編碼；

?character_set_server：MySQL服務(wù)器默認(rèn)編碼；

?character_set_results：響應(yīng)的編碼,即查詢結(jié)果返回給客戶端的編碼。這說(shuō)明客戶端必須使

用result指定的編碼來(lái)解碼；

2控制臺(tái)編碼

修改character_set_client>character_set_results^character_set_connection為GBK,就不會(huì)出現(xiàn)

亂碼了。但其實(shí)只需要修改character_set_client和character_set_resultso

控制臺(tái)的編碼只能是GBK,而不能修改為UTF8,這就出現(xiàn)一個(gè)問(wèn)題?？蛻舳税l(fā)送的數(shù)據(jù)是GBK,

而character_set_dient為UTF8,這就說(shuō)明客戶端數(shù)據(jù)到了服務(wù)器端后一定會(huì)出現(xiàn)亂碼。既然不能修

改控制臺(tái)的編碼，那么只能修改character_set_client為GBK了。

服務(wù)器發(fā)送給客戶端的數(shù)據(jù)編碼為character_set_result,它如果是UTF8,那么控制臺(tái)使用GBK

解碼也一定會(huì)出現(xiàn)亂碼。因?yàn)闊o(wú)法修改控制臺(tái)編碼，所以只能把character_set_result修改為GBK。

?修改character_set_client變量：setcharacter_set_dient=gbk;

?修改character_set_results變量：setcharacter_set_results=gbk;

設(shè)置編碼只對(duì)當(dāng)前連接有效，這說(shuō)明每次登錄MySQL提示符后都要去修改這兩個(gè)編碼，但可以

通過(guò)修改配置文件來(lái)處理這一問(wèn)題：配置文件路徑：D:\ProgramFiles\MySQL\MySQLServer5.1\my.ini

#CL工ENTSECT工ON

#女潛至這干：不要修改錯(cuò)了

#

#Thefollowingoptionswillk

#Notethatonlyulierrtapplic

#七。readthissection.Ifyot

#honorthesevalues,youneec

#MySQLclientlibraryinitial

#

[clierrt]

port=3306

[mysql]這個(gè)修改是一勞永逸的

defaul七一charau~ter~-se七=|gbk|

3MySQL工具

使用MySQL工具是不會(huì)出現(xiàn)亂碼的，因?yàn)樗鼈儠?huì)每次連接時(shí)都修改character_set_client>

character_set_resultSscharacter_set_connection的編碼。這樣對(duì)my.ini上的配置覆蓋了，也就不會(huì)出

現(xiàn)亂碼了。

MySQL數(shù)據(jù)庫(kù)備份與還原

備份和恢復(fù)數(shù)據(jù)

1生成SQL腳本

在控制臺(tái)使用mysqldump命令可以用來(lái)生成指定數(shù)據(jù)庫(kù)的腳本文本，但要注意，腳本文本中只

包含數(shù)據(jù)庫(kù)的內(nèi)容，而不會(huì)存在創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句！所以在恢復(fù)數(shù)據(jù)時(shí)，還需要自己手動(dòng)創(chuàng)建一個(gè)

數(shù)據(jù)庫(kù)之后再去恢復(fù)數(shù)據(jù)。

mysqldump-u用戶名-p密碼數(shù)據(jù)庫(kù)名〉生成的腳本文件路徑

C：\>nysqldunp-uroot-pl23nydbl>C：\nydbl.sql

C：\>

現(xiàn)在可以在C盤下找到mydbl.sql文件了!

注意，mysqldump命令是在Windows控制臺(tái)下執(zhí)行，無(wú)需登錄mysql!!!

2執(zhí)行SQL腳本

執(zhí)行SQL腳本需要登錄mysqL然后進(jìn)入指定數(shù)據(jù)庫(kù)，才可以執(zhí)行SQL腳本！”

執(zhí)行SQL腳本不只是用來(lái)恢復(fù)數(shù)據(jù)庫(kù)，也可以在平時(shí)編寫SQL腳本，然后使用執(zhí)行SQL腳本來(lái)

操作數(shù)據(jù)庫(kù)！大家都知道，在黑屏下編寫SQL語(yǔ)句時(shí)，就算發(fā)現(xiàn)了錯(cuò)誤，可能也不能修改了。所以

我建議大家使用腳本文件來(lái)編寫SQL代碼，然后執(zhí)行之！

SOURCEC:\mydbl.sql

1>sourcebl.s

注意，在執(zhí)行腳本時(shí)需要先行核查當(dāng)前數(shù)據(jù)庫(kù)中的表是否與腳本文件中的語(yǔ)句有沖突！例如在

腳本文件中存在createtablea的語(yǔ)句，而當(dāng)前數(shù)據(jù)庫(kù)中已經(jīng)存在了a表，那么就會(huì)出錯(cuò)！

還可以通過(guò)下面的方式來(lái)執(zhí)行腳本文件：

mysql-uroot-pl23mydbl＜c:\mydbl.sql

mysql-u用戶名-p密碼數(shù)據(jù)庫(kù)＜要執(zhí)行腳本文件路徑

|C：-iwoot-p123mydblXc:.sql

這種方式無(wú)需登錄mysql!

多表查詢

多表查詢有如下幾種：

?合并結(jié)果集；

?連接查詢

＞內(nèi)連接

＞外連接

。左外連接

令右外連接

?全外連接（MySQL不支持）

＞自然連接

?子查詢

1合并結(jié)果集

1.作用：合并結(jié)果集就是把兩個(gè)select語(yǔ)句的查詢結(jié)果合并到一起！

2.合并結(jié)果集有兩種方式：

?UNION：去除重復(fù)記錄,例如：SELECT*FROMtlUNIONSELECT*FROMt2；

UNIONALL：不去除重復(fù)記錄,例如:SELECT*FROMtlUNIONALLSELECT*FROMt2o

乜表t2表

ISELECT*FROMtlUNIONSELECT*FROMt2I

SELECT*FROMtlUNIONALLSELECT*FROMt2

3.要求：被合并的兩個(gè)結(jié)果：列數(shù)、列類型必須相同。

2連接查詢

連接查詢就是求出多個(gè)表的乘積，例如tl連接t2,那么查詢出的結(jié)果就是

tlt2

SELECT*FROMtl,t2

連接查詢會(huì)產(chǎn)生笛卡爾積，假設(shè)集合A={a,b},集合B={0,l,2},則兩個(gè)集合的笛卡爾積為

{t0),(a,l),(a,2),(b,0),(b,l),(b,2)}?？梢詳U(kuò)展到多個(gè)集合的情況。

那么多表查詢產(chǎn)生這樣的結(jié)果并不是我們想要的，那么怎么去除重復(fù)的，不想要的記錄呢，當(dāng)

然是通過(guò)條件過(guò)濾。通常要查詢的多個(gè)表之間都存在關(guān)聯(lián)關(guān)系，那么就通過(guò)關(guān)聯(lián)關(guān)系去除笛卡爾積。

你能想像到emp和dept表連接查詢的結(jié)果么？emp一共14行記錄，dept表一共4行記錄，那

么連接后查詢出的結(jié)果是56行記錄。

也就你只是想在查詢emp表的同時(shí)，把每個(gè)員工的所在部門信息顯示出來(lái)，那么就需要使用主

外鍵來(lái)去除無(wú)用信息了。

□eicpnoenaice3ngrhxredatesalCOJWdeptnoieptnodname1OC

10132000-12-178000.00(NULL)20