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

dayl5-MySQL

數(shù)據(jù)庫

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

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

數(shù)據(jù)庫就是用來存儲和管理數(shù)據(jù)的倉庫!

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

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

?方便檢索；

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

?安全，可共享；

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

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

?沒有數(shù)據(jù)庫，使用磁盤文件存儲數(shù)據(jù)；

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

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

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

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

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

1.3常見數(shù)據(jù)庫

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

?DB2：IBM；

?SQLServer：微軟；

?Sybase：賽爾斯；

?MySQL：甲骨文；

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

?RDBMS=管理員(manager)+倉庫(database)

?database=N個table

?table：

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

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

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

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

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

含多張表。

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

信息。而不用指定表格的行數(shù)，行數(shù)是沒有上限的。下面是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é)會區(qū)分什么是表結(jié)構(gòu)，什么是表記錄。

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

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

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

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

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

?退出：quit或exit；

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

是客戶端程序。

SQL語句

1SQL概述

1.1什么是SQL

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

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

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

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

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

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

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

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

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

Oracle或SQLServer都有自己的方言。

1.2語法要求

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

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

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

2分類

?DDL(DataDefinitionLanguage)：數(shù)據(jù)定義語言，用來定義數(shù)據(jù)庫對象：庫、表、列等;

?DML(DataManipulationLanguage)：數(shù)據(jù)操作語言，用來定義數(shù)據(jù)庫記錄(數(shù)據(jù))：

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

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

3DDL

3.1基本操作

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

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

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

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

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

已經(jīng)存在，那么會報錯。例如CREATEDATABASEIFNOTEXISTSmydbl,在名為mydbl的數(shù)據(jù)庫不存

在時創(chuàng)建該庫，這樣可以避免報錯。

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

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

在，那么會報錯。DROPDATABASEIFEXISTSmydbl,就算mydbl不存在，也不會的報錯。

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

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

即UTF-8要書寫為UTF8o

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

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

常用類型：

?int：整型

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

?decimal：泛型型，在表單錢方面使用該類型，因為不會出現(xiàn)精度缺失問題；

?char：固定長度字符串類型；

?varchar：可變長度字符串類型；

?text：字符串類型；

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

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

?time：時間類型，格式為：hh:mm:ss

?timestamp：時間戳類型；

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ù)庫中所有表名稱：SHOWTABLES；

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

?查看表結(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ù)

語法：

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

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

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

語法：

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

因為沒有指定要插入的列，表示按創(chuàng)建表時列的順序插入所有列的值:

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

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

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

語法：

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ù)

語法：

DELETEFROM表名［WHERE條件］

DELETEFROMstuWHEREsid='s1001'003B

DELETEFROMstuWHEREsname=/chenQi/ORage>30;

DELETEFROMstu;

語法：

TRUNCATETABLE表名

TRUNCATETABLEstu;

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

TRUNCATE高!

TRUNCATE其實屬性DDL語句，因為它是先DROPTABLE,再CREATETABLEo而且TRUNCATE冊I]

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

5DCL

5.1創(chuàng)建用戶

語法：

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

CREATEUSERuserl@localhostIDENTIFIEDBY'123';

CREATEUSERuser2@'%'IDENTIFIEDBY'123';

5.2給用戶授權(quán)

語法：

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

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

GRANTALLONmydbl.*TOuser2@localhost;

5.3撤銷授權(quán)

語法：

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

REVOKECREATE,ALTER,DROPONmydbl.*FROMuserl@localhost;

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

語法：

SHOWGRANTSFOR用戶名

SHOWGRANTSFORuserl@localhost;

5.5刪除用戶

語法：

DROPUSER用戶名

DROPUSERuserl@localhost;

5.6修改用戶密碼

語法：

USEmysql;

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

FLUSHPRIVILEGES;

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

FLUSHPRIVILEGES;

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

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

給客戶端。

語法：

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

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

WHEREcondition/*行條件

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

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

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

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

創(chuàng)建名：

?學(xué)生表：Stu

〔字段名稱字段類型說明

sidchar(6)學(xué)生學(xué)號

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

|字段名稱字段類型說明

empnoint員工編號

enamevarchar(SO)員工姓名

jobvarchar(5O)員工工作

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

hiredatedate入職日期

saldecimal(7,2)月薪

commdecimal。,2)獎金

deptnoint部分編號

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

字段名稱字段類型說明

deptnoint部分編碼

dnamevarchar(SO)部分名稱

locvarchar(50)部分所在地點

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條件查詢介紹

條件查詢就是在查詢時給出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é)號為S/001,或者姓名為HSi的記錄

SELECT*FROMstu

WHEREsid='S_1001'ORsname='liSi';

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

SELECT*FROMstu

WHEREsidIN('S_10017S_10027S_1003');

2.5查詢學(xué)號不是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é)生時就需要使用模糊查詢了。模糊查詢需要使用關(guān)鍵字LIKEo

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

SELECT*

FROMstu

WHEREsnameLIKE'

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

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

SELECT*

FROMstu

WHEREsnameLIKE'i';

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

SELECT*

FROMstu

WHEREsnameLIKE'z%';

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

3.4查詢姓名中第2個字母為“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字段時，那么會出現(xiàn)重復(fù)記錄，那么想去除重復(fù)記錄，需要使

用DISTINCT：

SELECTDISTINCTsalFROMemp;

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

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

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

SELECT*,sal+commFROMemp;

comm列有很多記錄的值為NULL.因為任何東西與NULL相加結(jié)果還是NULL,所以結(jié)算結(jié)果可

能會出現(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)在我們給這一列給出一個別名，

為total：

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

給列起別名時，是可以省略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查詢所有雇員，按月薪降序排序，如果月薪相同時，按編號升序排序

SELECT*FROMemp

ORDERBYsalDES^empnoASC;

6聚合函數(shù)

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

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

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

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

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

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

6.1COUNT

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

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

SELECTCOUNT(*)ASentFROMemp;

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

SELECTCOUNT(comm)entFROMemp;

注意，因為count。函數(shù)中給出的是comm列，那么只統(tǒng)計comm列非NULL的行數(shù)。

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

SELECTCOUNT(*)FROMemp

WHEREsal>2500;

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

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

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

SELECTCOUNT(comm),COUNT(mgr)FROMemp;

6.2SUM和AVG

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

?查詢所有雇員月薪和：

SELECTSUM(sal)FROMemp;

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

SELECTSUM(sal),SUM(comm)FROMemp;

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

SELECTSUMfsakIFNULUcomm.O))FROMemp;

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

SELECTSUM(sal),COUNT(sal)FROMemp;

或者

SELECTAVG(sal)FROMemp;

6.3MAX和MIN

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

SELECTMAX(sal),MIN(sal)FROMemp;

7分組查詢

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

來分組。

7.1分組查詢

?查詢每個部門的部門編號和每個部門的工資和；

SELECTdeptno,SUM(sal)

FROMemp

GROUPBYdeptno;

?查詢每個部門的部門編號以及每個部門的人數(shù)：

SELECTdeptno,COUNT(*)

FROMemp

GROUPBYdeptno;

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

SELECTdeptno^OUNTf*)

FROMemp

WHEREsal>1500

GROUPBYdeptno;

7.2HAVING子句

?查詢工資總和大于9000的部門編號以及工資和：

SELECTdeptno,SUM(sal)

FROMemp

GROUPBYdeptno

HAVINGSUM(sal)>9000;

注意，WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHERE子句的條件，那么這行

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

8LIMIT

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

8.1查詢5行記錄，起始行從0開始

SELECT*FROMempLIMIT0,5;

注意，起始行從0開始，即第一行開始！

8.2查詢10行記錄，起始行從3開始

SELECT*FROMempLIMIT3,10;

8.3分頁查詢

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

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

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

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

完整性約束

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

1主鍵

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

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

的，因為學(xué)習(xí)有可能同名。

主鍵列的值不能為NULL,也不能重愛！

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

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

CREATETABLEstu(

sidCHAR(6)PRIMARYKEY；

snameVARCHAR(20)z

ageINI

genderVARCHAR(IO)

)；

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

CREATETABLEstu(

sidCHAR(6),

snameVARCHAR(20h

ageINI；

genderVARCHAR(IO),

PRIMARYKEY(sid)

)；

?修改表時指定主鍵：

AUERTABLEstu

ADDPRIMARYKEY(sid);

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

ALTERTABLEstuDROPPRIMARYKEY;

2主鍵自增長

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

鍵設(shè)置為自動增長后，在沒有給出主鍵值時，主鍵的值會自動生成，而且是最大主鍵值+1，也就不

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

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

CREATETABLEstu(

sidINTPRIMARYKEYAUTOJNCREMENT,

snameVARCHAR(20),

ageINT；

genderVARCHAR(IO)

)；

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

ALTERTABLEstuCHANGEsidsidINTAUTOJNCREMENT;

?修改表時刪除主鍵自增長：

ALTERTABLEstuCHANGEsidsidINT;

3非空

指定非空約束的列不能沒有值，也就是說在插入記錄時，對添加了非空約束的列一定要給值；

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

?指定非空約束：

CREATETABLEstu(

sidINTPRIMARYKEYAUTOJNCREMENT,

snameVARCHAR(IO)NOTNULL,

ageINI

genderVARCHAR(IO)

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

報錯：

INSERTINTOstu(sid)VALUES⑴；

插入的記錄中sname沒有指定值，所以會報錯！

4唯一

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

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

CREATETABLEtab_ab(

sidINTPRIMARYKEYAUTOJNCREMENT,

snameVARCHAR(IO)UNIQUE

)；

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

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

當(dāng)兩次插入相同的名字時，MySQL會報錯！

5外鍵

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

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

字段就是外鍵，是相對部門表的外鍵。

我們再來看BBS系統(tǒng)中：用戶表(t_user)、分類表(t_section)、帖子表(t_topic)三者之間的

關(guān)系。

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

即zs!

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

的作者是ww.

外鍵就是用來約束這一列的值必須是另一張表的主鍵值！?。?/p>

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

CREATETABLEt_user(

uidINTPRIMARYKEYAUTOJNCREMENT,

unameVARCHAR(20)UNIQUENOTNULL

)；

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

CREATETABLEt_section(

sidINTPRIMARYKEYAUTOJNCREMENT,

snameVARCHAR(30),

UjdINI；

CONSTRAINTfk_t_userFOREIGNKEY(u_id)REFERENCESt_user(uid)

)；

?修改t_section表，指定u_id為相對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)系

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

是主表，誰是從表。人可以沒有身份證，但身份證必須要有人才行，所以人是主表，而身

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

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

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

?一對多（多對一）：最為常見的就是一對多！一對多和多對一，這是從哪個角度去看得出來

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

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

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

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

給出兩個外鍵，一個相對tstu表的外鍵，另一個相對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

因為當(dāng)初安裝時指定了字符集為UTF8,所以所有的編碼都是UTF8。

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

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

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

詢語句時，客戶端發(fā)送過來的數(shù)據(jù)會先轉(zhuǎn)換成connection指定的編碼。但只要客戶端發(fā)送

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

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

使用database編碼；

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

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

用result指定的編碼來解碼；

2控制臺編碼

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

亂碼了。但其實只需要修改character_set_client和character_set_resultso

控制臺的編碼只能是GBK,而不能修改為UTF8,這就出現(xiàn)一個問題。客戶端發(fā)送的數(shù)據(jù)是GBK,

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

改控制臺的編碼，那么只能修改character_set_client為GBK了。

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

解碼也一定會出現(xiàn)亂碼。因為無法修改控制臺編碼，所以只能把character_set_result修改為GBK。

?修改character_set_client變量：setcharacter_set_dient=gbk;

?修改character_set_results變量：setcharacter_set_results=gbk;

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

通過修改配置文件來處理這一問題：配置文件路徑：D:\ProgramFiles\MySQL\MySQLServer5.1\my.ini

#CL工ENTSECT工ON

#女潛至這干：不要修改錯了

#

#Thefollowingoptionswillk

#Notethatonlyulierrtapplic

#七。readthissection.Ifyot

#honorthesevalues,youneec

#MySQLclientlibraryinitial

#

[clierrt]

port=3306

[mysql]這個修改是一勞永逸的

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

3MySQL工具

使用MySQL工具是不會出現(xiàn)亂碼的，因為它們會每次連接時都修改character_set_client>

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

現(xiàn)亂碼了。

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

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

1生成SQL腳本

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

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

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

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

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

C：\>

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

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

2執(zhí)行SQL腳本

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

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

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

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

SOURCEC:\mydbl.sql

1>sourcebl.s

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

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

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

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

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

|C：-iwoot-p123mydblXc:.sql

這種方式無需登錄mysql!

多表查詢

多表查詢有如下幾種：

?合并結(jié)果集；

?連接查詢

＞內(nèi)連接

＞外連接

。左外連接

令右外連接

?全外連接（MySQL不支持）

＞自然連接

?子查詢

1合并結(jié)果集

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

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

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

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

乜表t2表

ISELECT*FROMtlUNIONSELECT*FROMt2I

SELECT*FROMtlUNIONALLSELECT*FROMt2

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

2連接查詢

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

tlt2

SELECT*FROMtl,t2

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

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

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

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

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

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

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

外鍵來去除無用信息了。

□eicpnoenaice3ngrhxredatesalCOJWdeptnoieptnodname1OC

10132000-12-178000.00(NULL)20