MySQL數(shù)據(jù)庫知識講義

nos^i/M

1MySQL入門

i.i數(shù)據(jù)庫概述

?MySQL是什么？

MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，在WEB應(yīng)用方面MySQL是最好的RDBMS（Relational

DatabaseManagementSystem：關(guān)系數(shù)據(jù)庫管理系統(tǒng)）應(yīng)用軟件之一。

?什么是數(shù)據(jù)庫

數(shù)據(jù)庫就是存儲數(shù)據(jù)的倉庫，其本質(zhì)是一個文件系統(tǒng)，數(shù)據(jù)按照特定的格式將數(shù)據(jù)存儲起來，用戶

可以通過SQL對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行增加，修改，刪除及查詢操作。

?什么是關(guān)系型數(shù)據(jù)庫

數(shù)據(jù)庫中的記錄是有行有列的數(shù)據(jù)庫就是關(guān)系型數(shù)據(jù)庫，與之相反的就是NoSQL數(shù)據(jù)庫了。

?數(shù)據(jù)庫和表

數(shù)據(jù)庫管理系統(tǒng)（DBMS：mysql軟件）

數(shù)據(jù)庫管理系統(tǒng)（DataBaseManagementSystem,DBMS）：指一種操作和管理數(shù)據(jù)庫的大型軟件，用

于建立、使用和維護數(shù)據(jù)庫，對數(shù)據(jù)庫進(jìn)行統(tǒng)一管理和控制，以保證數(shù)據(jù)庫的安全性和完整性。用

戶通過數(shù)據(jù)庫管理系統(tǒng)訪問數(shù)據(jù)庫中表內(nèi)的數(shù)據(jù)。（記錄）

?常見的數(shù)據(jù)庫管理系統(tǒng)

令MYSQL：開源免費的數(shù)據(jù)庫，小型的數(shù)據(jù)庫.已經(jīng)被Oracle收購了.MySQL5.5版本之后

都是由Oracle發(fā)布的版本。

令Oracle：收費的大型數(shù)據(jù)庫，Oracle公司的產(chǎn)品。Oracle收購SUN公司，收購MYSQL。

令DB2：IBM公司的數(shù)據(jù)庫產(chǎn)品，收費的。常應(yīng)用在銀行系統(tǒng)中.在中國的互聯(lián)網(wǎng)公司,

要求去I0E（IBM小型機、Oracle數(shù)據(jù)庫、EMC存儲設(shè)備）

令SQLServer：Microsoft公司收費的中型的數(shù)據(jù)庫。C#、.net等語言常使用。

令SyBase:已經(jīng)淡出歷史舞臺。提供了一個非常專業(yè)數(shù)據(jù)建模的工具PowerDesigner.

令SQLite:嵌入式的小型數(shù)據(jù)庫，應(yīng)用在手機端。

我們要學(xué)習(xí)的數(shù)據(jù)庫:MYSQL

?MySQL發(fā)展歷程

?MySQL的歷史可以追溯到1979年，一個名為MontyWidenius的程序員在為TcX的小公司打

工，并且用BASIC設(shè)計了一個報表工具，使其可以在4MHz主頻和16KB內(nèi)存的計算機上運行。

當(dāng)時，這只是一個很底層的且僅面向報表的存儲引擎，名叫Unireg。

?1990年，TcX公司的客戶中開始有人要求為他的API提供SQL支持。Monty直接借助于mSQL

的代碼，將它集成到自己的存儲引擎中。令人失望的是，效果并不太令人滿意，決心自己重

寫一個SQL支持。

?1996年，MySQL1.0發(fā)布，它只面向一小撥人，相當(dāng)于內(nèi)部發(fā)布。

?到了1996年10月，MySQL3.11.1發(fā)布(MySQL沒有2.x版本)，最開始只提供Solaris下的

二進(jìn)制版本。一個月后，Linux版本出現(xiàn)了?在接下來的兩年里，MySQL被依次移植到各個平

臺。

?1999?2000年，MySQLAB公司在瑞典成立。Monty雇了幾個人與Sleepycat合作，開發(fā)出了

BerkeleyDB引擎，由于BDB支持事務(wù)處理，因此MySQL從此開始支持事務(wù)處理了。

?2000,MySQL不僅公布自己的源代碼，并采用GPL(GNUGeneralPublicLicense)許可協(xié)議，

正式進(jìn)入開源世界。同年4月，MySQL對舊的存儲引擎ISAM進(jìn)行了整理，將其命名為MylSAM,

?2001年，集成HeikkiTuuri的存儲引擎InnoDB,這個引擎不僅能持事務(wù)處理，并且支持行

級鎖。后來該引擎被證明是最為成功的MySQL事務(wù)存儲引擎。MySQL與InnoDB的正式結(jié)合版

本是4.0

?2003年12月，MySQL5.0版本發(fā)布，提供了視圖、存儲過程等功能。

?2008年1月，MySQLAB公司被Sun公司以10億美金收購，MySQL數(shù)據(jù)庫進(jìn)入Sun時代。在

Sun時代，Sun公司對其進(jìn)行了大量的推廣、優(yōu)化、Bug修復(fù)等工作。

?2008年11月，MySQL5.1發(fā)布，它提供了分區(qū)、事件管理，以及基于行的復(fù)制和基于磁盤的

NDB集群系統(tǒng)，同時修復(fù)了大量的Bug。

?2009年4月，Oracle公司以74億美元收購Sun公司，自此MySQL數(shù)據(jù)庫進(jìn)入Oracle時代，

而其第三方的存儲引擎InnoDB早在2005年就被Oracle公司收購。

?2010年12月，MySQL5.5發(fā)布，其主要新特性包括半同步的復(fù)制及對SIGNAL/RESIGNAL的異

常處理功能的支持，最重要的是InnoDB存儲引擎終于變?yōu)楫?dāng)前MySQL的默認(rèn)存儲引擎。MySQL

5.5不是時隔兩年后的一次簡單的版本更新，而是加強了MySQL各個方面在企業(yè)級的特性。

Oracle公司同時也承諾MySQL5.5和未來版本仍是采用GPL授權(quán)的開源產(chǎn)品。

1.2MySQL安裝

?安裝

參考MySQL安裝圖解.doc

劭MySQL安裝圖解.doc

?配置MysQL服務(wù)

bin

M售理

這?本ifiKS(C:)?ProgramFiles(x86)?MySQL?MySQLServer5.5?bin

修改日期類型‘大小

名稱

8KB

34KB

3,381KB

3,656KB

3,766KB

3,644KB

3,684KB

3,997KB

use

8,657KB

：)3,395KB

3,358KB

3,471KB

3,930KB

4,022KB

3,925KB

7,995KB

3,988KB

3,920KB

CMySQLInstanceConfig.exe2010/8/2614:57遮位2,904KB

?啟動和關(guān)閉（必須以管理運行cmd命令窗口）

QS管理員:C:\Windows\System32\cmd.exe

C：\Windows\system32>netstartmysql

MySQL服務(wù)正在啟動

MySQL服務(wù)已經(jīng)啟動成功。

C：\Windows\system32>netstopmysql

MySQL服務(wù)定在停止..

MySQL服務(wù)已成功停止。

?登錄

格式1：cmd>mysql-u用戶名-p密碼

例如：mysql-uroot-proot

C：MJsei'S\LiKepei>nysql-uroot-proot

WelcometotheMySQLmonitor.Commandsendwith；or

Voui*MySQLconnectionidis2

Serueruersion：5.5.2?MySQLCommunitySeruer<GPL>

Copyright<c>2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheii*respective

oviners.

Type'help；‘or'5'forhelp.Type*\c*tocleai*thecurrentinputstatement.

nysql>

格式2：cmd>mysql-user二用戶名一host二ip地址一password二密碼

1.3SQLyog安裝與使用

?安裝：

提供的SQLyog軟件為免安裝版，可直接使用

?使用：

輸入用戶名、密碼，點擊連接按鈕，進(jìn)行訪問MySQL數(shù)據(jù)庫進(jìn)行操作

SQLyogUltimate-|LKP/gjp-root9locdhost)

S?<￡)WBgNQ)E5向一忌t表⑻具曰。IB0招工總曲■□皿

▼I’力安，。二二,*噓卬三

1,，

.rcot€<>calhSl^

.Query工QueryBolder字3Wh5if3

ttw、thn?e自動力儺：g】-?下一杯英，［d1.Sp>8卜》列比喇標(biāo)英，［dUErwr］。列出新詢標(biāo)笠.

Bi.r?bu?

,3gjp.iedger

s■gjp.sort

■&Vwwf

￡4,StoredProcs

ffi1Functiont

-ATriggers

▲Event*

ij?mformation.schefna

國N?ny?ql

w.peWonn4nce.ichwa

ffiMtest

在Query窗口中，輸入SQL代碼，選中要執(zhí)行的SQL代碼，按F8鍵運行，或按執(zhí)行按鈕運行。

2SQL語句

2.1SQL語句介紹

?什么是SQL?

全稱StructuredQueryLanguage,簡稱SQL,中文叫結(jié)構(gòu)化查詢語言。

關(guān)系數(shù)據(jù)庫語言的國際標(biāo)準(zhǔn)。SQL92、SQL99

不只是mysql還有其他數(shù)據(jù)庫，在SQL92或者SQL99這些國際SQL標(biāo)準(zhǔn)基礎(chǔ)之上，它們還擴展

了自己的一些SQL語句，比如MySQL中的limit關(guān)鍵字

?SQL分類：

■數(shù)據(jù)定義語言：簡稱DDL(DataDefinitionLanguage),用來定義數(shù)據(jù)庫對象：數(shù)據(jù)庫，

表，列等。關(guān)鍵字：create,alter,drop等

■數(shù)據(jù)操作語言：簡稱DML(DataManipulationLanguage),用來對數(shù)據(jù)庫中表的記錄進(jìn)行

更新。關(guān)鍵字：insert,delete,update等

■數(shù)據(jù)控制語言：簡稱DCL(DataControlLanguage),用來定義數(shù)據(jù)庫的訪問權(quán)限和安全級

別，及創(chuàng)建用戶；關(guān)鍵字：grant等

■數(shù)據(jù)查詢語言：簡稱DQL(DataQueryLanguage),用來查詢數(shù)據(jù)庫中表的記錄。關(guān)鍵字：

select,from,where等

2.2SQL語句使用

2.2.1數(shù)據(jù)庫操作：database

?創(chuàng)建數(shù)據(jù)庫

語法：

*createdatabase數(shù)據(jù)庫名；

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

iysql>createdatabaseday21_l；

!uei、yOK,1rov?affected<0.00sec>

iysql>createdatabaseday21_2charactersetgbk；

!ueryOK,1rovjaffected<0.01sec)

?查看數(shù)據(jù)庫

查看數(shù)據(jù)庫服務(wù)器中的所有的數(shù)據(jù)庫:showdatabases;

查看某個數(shù)據(jù)庫的定義的信息:showcreatedatabase數(shù)據(jù)庫名;

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

dropdatabase數(shù)據(jù)庫名稱；

?其他的數(shù)據(jù)庫操作命令

切換數(shù)據(jù)庫：use數(shù)據(jù)庫名；

查看正在使用的數(shù)據(jù)庫：selectdatabase（）;

?例如：

#創(chuàng)建數(shù)據(jù)庫

createdatabaseday06;

#使用數(shù)據(jù)庫

useday06;

2.2.2表操作：table

2.2.2.1字段類型

?常用的類型有：

數(shù)字型：int

浮點型:double

字符型：varchar（可變長字符串）

日期類型：date（只有年月日，沒有時分秒）

datetime（年月日，時分秒）

boolean類型：不支持

分類類型名稱說明

整數(shù)類型tinylnt很小的整數(shù)

smallint小的整數(shù)

mediumint中等大小的整數(shù)

int(integer)普通大小的整數(shù)

小數(shù)類型float單精度浮點數(shù)

double雙精度浮點數(shù)

decimal(m,d)壓縮嚴(yán)格的定點數(shù)開發(fā)時用

日期類型yearYYYY190P2155

timeUH:MM:SS-838:59:59~838:59:59

dateYYYY-MM-DD1000-01-0P9999-12-3

datetime-開發(fā)用YYYY-MM-DDHH:MM:SS1000-01-0100:00:00"9999-12-31

23:59:59

timestampYYYY-MM-DDIIH:MM:SS1970“0廣0100:00:01

UTC?2038-01-1903:14:07UTC

文本、二進(jìn)制CHAR(M)M為0~255之間的整數(shù)

類型VARCHAR(M)M為0~65535之間的整數(shù)

TINYBLOB允許長度0~255字節(jié)

BLOB允許長度0~65535字節(jié)

MEDIUMBLOB允許長度0^167772150字節(jié)

LONGBLOB允許長度0^4294967295字節(jié)

TINYTEXT允許長度0~255字節(jié)

TEXT允許長度0~65535字節(jié)

MEDIUMTEXT允許長度0~167772150字節(jié)

LONGTEXT允許長度0^4294967295字節(jié)

VARBINARY(M)允許長度0~M個字節(jié)的變長字節(jié)字符串

BINARY(M)允許長度0~M個字節(jié)的定長字節(jié)字符串

?創(chuàng)建表

createtable表名（

字段名類型（長度）約束，

字段名類型（長度）約束

）;

單表約束：

*主鍵約束：primarykey

*唯一約束：unique

*非空約束：notnull

*注意：主鍵約束=唯一約束+非空約束

?例如：

###創(chuàng)建分類表

CREATETABLEsort（

sidINTPRIMARYKEY,#分類ID

snameVARCHAR（IOO）#分類名稱

);

sname

?查看表

查看數(shù)據(jù)庫中的所有表：showtables;

查看表結(jié)構(gòu)：desc表名；

?刪除表

droptable表名；

?修改表

altertable表名add列名類型（長度）約束；一修改表添加列.

altertable表名modify列名類型（長度）約束；一修改表修改列的類型長度及約束.

altertable表名change舊列名新列名類型（長度）約束；一修改表修改列名.

altertable表名drop列名；--修改表刪除列.

renametable表名to新表名；一修改表名

altertable表名characterset字符集；一修改表的字符集

2.2.3插入記錄：insert

?語法：

insertinto表（列名1,列名2,列名3..）values（值1,值2,值3..）;一向表中插入某些列

insertinto表values（值1,值2,值3..）;一向表中插入所有列

insertinto表（列名1,列名2,列名3..）valuesselect（列名1,列名2,列名3..）from表

insertinto表valuesselect*from表

?注意：

1.列名數(shù)與values后面的值的個數(shù)相等

2.列的順序與插入的值得順序一致

3.列名的類型與插入的值要一致.

4.插入值得時候不能超過最大長度.

5.值如果是字符串或者日期需要加引號''（一般是單引號）

?例如：

INSERTINTOsort(sid,sname)VALUESCsOOl','電器')；

INSERTINTOsort(sid,sname)VALUESCs002','服飾');

INSERTINTOsortVALUESCs003','化妝品')；

INSERTINTOsortVALUES('s004'書籍');

sidsname

□sOOl電器

□s002服飾

□s003化妝品

□s004書籍

*(NULL)(NULL)

2.2.4更新記錄：update

?語法：

update表名set字段名=值，字段名=值；

update表名set字段名=值，字段名=值where條件;

?注意：

1.列名的類型與修改的值要一致.

2.修改值得時候不能超過最大長度.

3.值如果是字符串或者日期需要加.

2.2.5刪除記錄：delete

?語法：

deletefrom表名［where條件］;

?面試題：

刪除表中所有記錄使用deletefrom表名；還是用truncatetable表名；

刪除方式：delete-條-條刪除,不清空auto_increment記錄數(shù)。

truncate直接將表刪除，重新建表，auto_increment將置為零，從新開始。

2.3CMD中文亂碼

?修改my.ini文件，然后重啟mysql服務(wù)器

D:\java\MySQL\MySQLServer5.5\my.ini-Notepad2

文件任)編輯出視圖凹設(shè)置(S)幫助(2)

」」乂川立O「X小省|簫&|畫|.Y，叱II

55A[nysql]

56

57deFault-character-set=gbk

co

3SQL查詢

查詢語句，在開發(fā)中使用的次數(shù)最多，此處使用“products”商品表。

?主鍵類型，本案例采用int類型，實際案例采用是varchar類型

?時間類型，本案例采用date類型，實際案例采用datetime類型。

3.1準(zhǔn)備工作

創(chuàng)建商品表：

CREATETABLEproducts（

pidINTPRIMARYKEYAUTO_INCREMENT,#自增加AUTO_INCREMENT

pnameVARCHAR（20）,#商品名稱

priceDOUBLE,#商品價格

pdateDATE,#日期

sidVARCIIAR（20）#分類ID

INSERTINTOproductsVALUES（NULL,'泰國大榴蓮’，98,NULL,'sOOl'）;

INSERTINTOproductsVALUES（NULL,'新疆大棗'，38,NULL,"s002'）;

INSERTINTOproductsVALUES（NULL,'新疆切糕'，68,NULL,'sOOf）;

INSERTINTOproductsVALUES（NULL,'十三香'，10,NULL,'s002'）;

INSERTINTOproductsVALUES（NULL,'老干媽'，20,NULL,'s002'）;

3.2查詢語法

SELECT［DISTINCT］*［列名，列名］FROM表［WHERE條件］.

3.3簡單查詢

?SQL語法關(guān)鍵字：

■SELECT

■FROM

?案例：

1.查詢所有的商品.select*fromproduct;

2.查詢商品名和商品價格.selectpname,pricefromproduct;

3.別名查詢，使用的as關(guān)鍵字，as可以省略的.

3.1表別名：select*fromproductasp;

3.2列別名：selectpnameaspnfromproduct;

4.去掉重復(fù)值.selectdistinctpricefromproduct;

5.查詢結(jié)果是表達(dá)式（運算簧詢）：將所有商品的價格+10元進(jìn)行顯示.

selectpname,price+10fromproduct;

3.4條件查詢

?SQL語法關(guān)鍵字：

■WHERE

?案例：

1.查詢商品名稱為十三香的商品所有信息：

*select*fromproductwherepname='十三香'；

2.查詢商品價格>60元的所有的商品信息：

*select*fromproductwhereprice>60;

where后的條件寫法：

*>,<,=,>=,<=,<>

*like使用占位符一和％_代表一個字符%代表任意個字符.

*select*fromproductwherepnamelike'%新%';

*in在某個范圍中獲得值（exists）.

*select*fromproductwherepidin（2,5,8）;

比較運算符><<=>==<>大于、小于、大于（小于）等于、不等于

BETWEEN...AND...顯示在某一區(qū)間的值（含頭含尾）

顯示在in列表中的值，例：i在100,200）

IN(set)

模糊查詢，Like語句中，%代表零個或多個任意字符，

LIKE，張」

代表一個字符，例first_namelike（_a%）;

判斷是否為空

ISNULL

邏輯運算符and多個條件同時成立

or多個條件任一成立

not不成立，例：wherenot(salary>100);

3.5排序

?SQL語法關(guān)鍵字：

■ORDERBY

■ASC（升序）DESC（降序）

?案例:

1.查詢所有的商品，按價格進(jìn)行排序.(asc-升序,desc-降序)

*select*fromproductorderbyprice;

2.查詢名稱有新的商品的信息并且按價格降序排序.

*select*fromproductwherepname1ike'%新%'orderbypricedesc;

3.6聚合函數(shù)(組函數(shù))

?特點：只對單列進(jìn)行操作

?常用的聚合函數(shù)：

■sum()；求某一列的和

■avg()：求某一列的平均值

■max()：求某一列的最大值

■min()：求某一列的最小值

■count()：求某一列的元素個數(shù)

?案例：

1.獲得所有商品的價格的總和:selectsum(price)fromproduct;

2.獲得所有商品的平均價格：selectavg(price)fromproduct;

3.獲得所有商品的個數(shù):selectcount(*)fromproduct;

3.7分組

?SQL語法關(guān)鍵字：

■GROUPBY

■HAVING

?案例：

1.根據(jù)eno字段分組，分組后統(tǒng)計商品的個數(shù).

*selectcid,count(*)fromproductgroupbycid;

2.根據(jù)eno分組，分組統(tǒng)計每組商品的平均價格，并且平均價格>60;

*selectcid,avg(price)fromproductgroupbycidhavingavg(price)>60;

?注意事項：

1.select語句中的列(非聚合函數(shù)列)，必須出現(xiàn)在groupby子句中

2.groupby子句中的列，不一定要出現(xiàn)在select語句中

3.聚合函數(shù)只能出現(xiàn)select語句中或者h(yuǎn)aving語句中，一定不能出現(xiàn)在where語句中。

3.8其他查詢語句

union集合的并集(不包含重復(fù)記錄)

unionall集合的并集(包含重復(fù)記錄)

4SQL查詢之語法順序和執(zhí)行順序（重要）

?MySQL查詢語法順序

1.SELECT

2.FROM

3.LEFTJOIN

4.ON

5.WHERE

6.GROUPBY

7.HAVING

8.ORDERBY

9.LIMIT

?執(zhí)行順序

示例SQL：

SELECT*FROMuserLEFTJOINorderONuser,id=order,uidWHEREorder,price>1000GROUP

BYuser,nameHAVINGcount（1）>5ORDERBYuser,nameLIMIT0,10

1.FROM（將最近的兩張表，進(jìn)行笛卡爾積）--VT1

2.0N（將VT1按照它的條件進(jìn)行過濾）--VT2

3.LEFTJOIN（保留左表的記錄）--VT3

4.WHERE（過濾VT3中的記錄）一VT4…VTn

5.GROUPBY（對VT4的記錄進(jìn)行分組）--VT5

6.HAVING（對VT5中的記錄進(jìn)行過濾）--VT6

7.SELECT（對VT6中的記錄，選取指定的列）一VT7

8.ORDERBY（對VT7的記錄進(jìn)行排序）一游標(biāo)

9.LIMIT（對排序之后的值進(jìn)行分頁）

?WHERE條件執(zhí)行順序（影響性能）

1.MYSQL：從左往右去執(zhí)行WHERE條件的。

2.Oracle：從右往左去執(zhí)行WHERE條件的。

?結(jié)論：寫WHERE條件的時候，優(yōu)先級高的部分要去編寫過濾力度最大的條件語句。

5多表之間的關(guān)系

如上圖所示，實際業(yè)務(wù)數(shù)據(jù)庫中的表之間都是有關(guān)系的，我們接下來主要要學(xué)習(xí)的就是如何分析表

關(guān)系及建立表關(guān)系。

1.分類表

createtablecategory(

cidvarchar(32)primarykey,

cnamevarchar(100)

)；

2.商品表

createtableproduct(

pidvarchar(32)primarykey,

pnamevarchar(40),

pricedouble

)；

3.訂單表

createtableorders(

oidvarchar(32)primarykey,

totalpricedouble

)；

4.訂單項表

createtableorderitern(

oidvarchar(50),

pidvarchar(50)

）；

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

表與表之間的關(guān)系,說的就是表與表之間數(shù)據(jù)的關(guān)系。

?一對一關(guān)系

■常見實例：一夫一妻

?一對多關(guān)系

■常見實例：會員和訂單

?多對多關(guān)系（需要中間表實現(xiàn)）

■常見實例：商品和訂單

5.2外鍵

如何表示表與表之間的關(guān)系呢？就是使用外鍵來約束的。

要想理解外鍵，我們先去理解表的角色：主表和從表（需要建立關(guān)系才有J'主從表的角色區(qū)分）

任products送

a4區(qū)▼

pid

name

price

cidFK

現(xiàn)在我們有兩張表“分類表”和“商品表”。

目前從表的聲明上來說，沒有關(guān)系，但是我們有個需求：

商品應(yīng)該有所屬的分類，這個時候需要將分類表和商品表建立關(guān)系，如何建立?

按照以上需求分析：

主表是:商品表。主表中,應(yīng)該有一個字段去關(guān)聯(lián)從表,而這個關(guān)聯(lián)字段就是外鍵。

從表是:分類表。從表中,應(yīng)該有一個字段去關(guān)聯(lián)主表,而這個關(guān)聯(lián)字段就是主鍵。

?主鍵外鍵的理解

MS3^

Scategory|S3里products區(qū)

Z*cid主催Qpid

cnamename

price

cid研

?如何操作外鍵

■主表添加外里的格手______________________________________________________________

altertable表名add[constraint][約束名稱]foreignkey（主表外鍵字段）references

從表（從表主鍵）

■主表刪除外鍵的格式：

altertable表名dropforeignkey外鍵約束名稱

■使用外鍵目的：

■保證數(shù)據(jù)完整性（數(shù)據(jù)保存在多張表中的時候）

■在互聯(lián)網(wǎng)項目中，一般情況下，不建議建立外鍵關(guān)系。

5.3一對一關(guān)系（了解）

在實際工作中，一對一在開發(fā)中應(yīng)用不多，因為一對一完全可以創(chuàng)建成一張表

案例：一個丈夫只能有一個妻子

?建表語句：

?一對一關(guān)系創(chuàng)建方式1之外鍵唯一：

■添加外鍵列wid,指定該列的約束為唯一（不如儂一約燙就是一對多關(guān)系）

ALTERTABLEhusbandADDwidINTUNIQUE;

■添加外鍵約束

altertablehusbandaddforeignkey(wid)referenceswife(id);

?一對一關(guān)系創(chuàng)建方式2之主鍵做外鍵：(課后作業(yè))

■思路：使用主表的主鍵作為外鍵去關(guān)聯(lián)從表的主鍵

5.4一對多關(guān)系

案例：一個分類對應(yīng)多個商品

?注意事項：

-對多關(guān)系和一對一關(guān)系的創(chuàng)建很類似,唯一區(qū)別就是外鍵不唯一。

?一對多關(guān)系創(chuàng)建：

■添加外鍵列

■添加外鍵約束

?案例：

1.在商品表中添加一條記錄，該記錄的cid在分類表中不存在

2.在分類表中，刪除一條記錄，這條記錄在商品表中有外鍵關(guān)聯(lián)

5.5多對多關(guān)系

案例：同一個商品對應(yīng)多個訂單，一個訂單對應(yīng)多個商品

?注意事項：

■需要中間表去完成多對多關(guān)系的創(chuàng)建

■多對多關(guān)系其實就是兩個一對多關(guān)系的組合

?多對多關(guān)系創(chuàng)建：

■創(chuàng)建中間表，并在其中創(chuàng)建多對多關(guān)系中兩張表的外鍵列

■在中間表中添加外鍵約束

■在中間表中添加聯(lián)合主鍵約束

6多表關(guān)聯(lián)查詢

我們已經(jīng)學(xué)會了如何在一張表中讀取數(shù)據(jù)，這是相對簡單的，但是在真正的應(yīng)用中經(jīng)常需要從多個

數(shù)據(jù)表中讀取數(shù)據(jù)。

本章節(jié)我們將向大家介紹如何使用MySQL的JOIN在兩個或多個表中查詢數(shù)據(jù)。

你可以在SELECT,UPDATE和DELETE語句中使用MySQL的JOIN來聯(lián)合多表查詢。

JOIN按照功能大致分為如下三類：

?CROSSJOIN（交叉連接）

?INNERJOIN（內(nèi)連接或等值連接）。

?OUTERJOIN（外連接）

?建表語句：

insertintocategory(cid,cname)values('cOOl','家電')；

insertintocategory(cid,cname)values('c002','服飾')；

insertintocategory(cid,cname)values('c003','化妝品')；

insertintoproduct(pid,pname,price,cid)values('pOOl','聯(lián)想'，5000,'cOOl');

insertintoproduct(pid,pname,price,cid)values('p002',‘海爾'，3000,*cOOT);

insertintoproduct(pid,pname,price,cid)values('p003','雷神'，5000,'cOOT);

insertintoproduct(pid,pname,price,cid)values('p004','阿迪'，1000,'c002');

insertintoproduct(pid,pname,price,cid)values('p005'而寸克'，1200,'c002,);

insertintoproduct(pid,pname,price,cid)values('p006','NB',800,'c002');

insertintoproduct(pid,pname,price,cid)values('p007','彪馬'，600,'c002');

insertintoproduct(pid,pname,price,cid)values('p008',‘雪花秀'，1500,'c003');

insertintoproduct(pid,pname,price,cid)values('p009','悅詩風(fēng)吟'，1100,'c003');

6.1交叉連接

交叉連接的關(guān)鍵字：CROSSJOIN

交叉連接也叫笛卡爾積連接

笛片外積是指在數(shù)學(xué)中，兩個集合X和Y的笛卡爾積(Cartesianproduct),又稱直積，表示為X*Y,

第一個對象是X的成員而第二個對象是Y的所有可能有序?qū)Φ钠渲幸粋€成員。

交叉連接的表現(xiàn)：行數(shù)相乘、列數(shù)相加

?隱式交叉連接

SELECT*FROMA,B

?顯式交叉連接

SELECT*FROMACROSSJOL\:B

案例：查詢商品表和分類表的笛卡爾積。

，多表查詢…準(zhǔn)備語句.sql

自動完成：［Tab］-下一個標(biāo)簽，［Ctrl+Space］一列出所有標(biāo)簽，［Ctd+Enter］＜列出匹取標(biāo)簽

3SELECT*FROMcategory;

4SELECT*FROMproduct；交叉連接SQL語句

6一隱式交叉連接J，

71SELECT*FROMproduct,category;|

t2Profiler牛3信息曷4表致?lián)?信息

Y0叵限腑第一行：o

廠二一I(只讀)?曷再出畬看|一言言?

pnamepricecname

性總5000C001京電

5000c0'：2強緯分類表記錄

聯(lián)想5000COG3化妝品

海爾3000CO01冢電

海爾3000c002弟的

海爾3000cOC3化妝品

蕾種5000cOCl豕電

雷神5000c002服飾

常神5000c003化妝品

阿迨1000C001★電一

阿

isC002魔飾

阿化妝品

i￡克c003

耐

克C001京電

時

主c002展飾婀連酰錄總數(shù)

向

c003化妝品

□二

prp3京電

□06

服飾

□口06

□06化妝品

PO07京亳

□R07

原飾

select*fromproduct,categoryLIMIT0,1000

II

6.2內(nèi)連接

內(nèi)連接的關(guān)鍵字：INNERJOIN

內(nèi)連接也叫等值連接，內(nèi)聯(lián)接使用比較運算符根據(jù)每個表共有的列的值匹配兩個表中的行。

?隱式內(nèi)連接

SELECT*FROMA,BWHEREA.id=B.id

?顯式內(nèi)連接

SELECT*FROMAINNERJOINBONA.id=B.id

6.3外連接

外聯(lián)接可以是左向外聯(lián)接、右向外聯(lián)接或完整外部聯(lián)接。也就是說外連接又分為：左外連接、右外

連接、全外連接

外連接需要有主表或者保留表的概念。

在FROM子句中指定外聯(lián)接時，可以由下列幾組關(guān)鍵字中的一組指定：

?左外連接：LEFTJOIN或者LEFTOUTERJOIN

SELECT*FROMALEFTJOINBONA.id=B.id

案例:查詢分類信息,關(guān)聯(lián)查詢商品信息

?右外連接：：RIGHTJOIN或者RIGHTOUTERJOIN

SELECT*FROMARIGHTJOINBONA.id=B.id

?全外連接（MySQL不支持）：FULLJOIN或FULLOUTERJOIN

SELECT*FROMAFULLJOINBONA.id=B.id

外連接總結(jié)：

?通過業(yè)務(wù)需求，分析主從表

?如果使用LEFTJOIN,則主表在它左邊

?如果使用RIGHTJOIN,則主表在它右邊

?查詢結(jié)果以主表為主，從表記錄匹配不到，則補null

6.4分頁查詢

MySQL的分頁關(guān)鍵字是：LIMIT

LIMIT關(guān)鍵字不是SQL92標(biāo)準(zhǔn)提出的關(guān)鍵字，它是MySQL獨有的語法。

通過Limit關(guān)鍵字，MySQL實現(xiàn)了物理分頁。

分頁分為邏輯分頁和物理分頁

邏輯分頁：將數(shù)據(jù)庫中的數(shù)據(jù)查詢到內(nèi)存之后再進(jìn)行分頁。

物理分頁：通過UMIT關(guān)鍵字，直接在數(shù)據(jù)庫中進(jìn)行分頁，最終返回的數(shù)據(jù)，只是分頁后的數(shù)據(jù)。

?格式：

SELECT*FROMtableLIMIT[offset,]rows

offset：編譯量

rows：每頁多少行記錄。

?案例

1.分頁查詢商品表，每頁3條記錄，查第一頁

6.5子查詢

?定義

子查詢允許把一個查詢嵌套在另一個查詢當(dāng)中。

子查詢，又叫內(nèi)部查詢，相對于內(nèi)部查詢，包含內(nèi)部查詢的就稱為外部查詢

子查詢可以包含普通select可以包括的任何子句，比如：distinctAgroupby、orderby、

1imit>join和union等；

但是對應(yīng)的外部查詢必須是以下語句之一：select、insert、update>delete.

?位置

select中、from后、where中.

groupby和orderby中無實用意義。

?舉例

1.查詢“化妝品”分類下的商品信息

6.6查詢原理

第一、單表查詢：根據(jù)WHERE條件過濾表中的記錄，形成中間表（這個中間表對用戶是不可見的）;

然后根據(jù)SELECT的選擇列選擇相應(yīng)的列進(jìn)行返回最終結(jié)果。

第二、兩表連接查詢：對兩表求積（笛卡爾積）并用ON條件和連接連接類型進(jìn)行過濾形成中間表;

然后根據(jù)WHERE條件過濾中間表的記錄，并根據(jù)SELECT指定的列返回查詢結(jié)果。

第三、多表連接查詢：先對第一個和第二個表按照兩表連接做查詢，然后用查詢結(jié)果和第三個表做

連接查詢，以此類推，直到所有的表都連接上為止，最終形成一個中間的結(jié)果表，然后根據(jù)WHERE

條件過濾中間表的記錄，并根據(jù)SELECT指定的列返回查詢結(jié)果。

理解SQL查詢的過程是進(jìn)行SQL優(yōu)化的理論依據(jù)。

7MySQL架構(gòu)

7.1邏輯架構(gòu)圖1

Connectors

NaiivoCAPI.JDBC.ODBC..NET.PHP.Pod.Python.Ruby.Cobol

?Connectors

指的是不同語言中與SQL的交互

?ManagementServeices&Utilities：

系統(tǒng)管理和控制工具

?ConnectionPool:連接池

*管理緩沖用戶連接，線程處理等需要緩存的需求。

*負(fù)責(zé)監(jiān)聽對MySQLServer的各種請求，接收連接請求，轉(zhuǎn)發(fā)所有連接請求到線程管理模塊。每

一個連接上MySQLServer的客戶端請求都會被分配（或創(chuàng)建）一個連接線程為其單獨服務(wù)。

*而連接線程的主要工作就是負(fù)責(zé)MySQLServer與客戶端的通信，接受客戶端的命令請求，傳遞

Server端的結(jié)果信息等。線程管理模塊則負(fù)責(zé)管理維護這些連接線程。包括線程的創(chuàng)建，線程的

cache等。

?SQLInterface:SQL接口

接受用戶的SQL命令，并且返回用戶需要查詢的結(jié)果。比如selectfrom就是調(diào)用SQLInterface

?Parser:解析器

*SQL命令傳遞到解析器的時候會被解析器驗證和解析。

主要功能：

a.將SQL語句進(jìn)行語義和語法的分析，分解成數(shù)據(jù)結(jié)構(gòu)，然后按照不同的操作類型進(jìn)行分類，

然后做出針對性的轉(zhuǎn)發(fā)到后續(xù)步驟，以后