第7章MySQL數(shù)據(jù)庫(kù)操作

數(shù)據(jù)庫(kù)原理TheTheoryofDatabaseSystem第七章MySQL數(shù)據(jù)庫(kù)操作MySQL簡(jiǎn)介MySQL查詢語(yǔ)句MySQL數(shù)據(jù)庫(kù)的基本操作開(kāi)發(fā)平臺(tái)與MySQL的連接MySQL數(shù)據(jù)庫(kù)的備份、恢復(fù)和安全本講主要內(nèi)容7.1MySQL簡(jiǎn)介7.1.1MySQL（1）MySQL是目前最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一。（2）MySQL是最好的RDBMS應(yīng)用軟件之一。（3）MySQL將數(shù)據(jù)存于表中，通過(guò)外鍵建立關(guān)聯(lián)。

（1）查詢速度快，靈活性高。

（2）社區(qū)版性能卓越。

（3）軟件體積小、運(yùn)行速度快、開(kāi)放源碼。7.1.2MySQL特點(diǎn)

7.1MySQL簡(jiǎn)介MySQL采用的是客戶機(jī)/服務(wù)器（Client/Server）體系結(jié)構(gòu)：（1）一個(gè)是在數(shù)據(jù)服務(wù)器上的數(shù)據(jù)庫(kù)服務(wù)程序。（對(duì)應(yīng)Server端）（2）連接到數(shù)據(jù)庫(kù)服務(wù)器、用于用戶和服務(wù)器交互的客戶機(jī)程序。（對(duì)應(yīng)Client端）7.2MySQL的體系結(jié)構(gòu)客戶機(jī)/服務(wù)器體系結(jié)構(gòu)優(yōu)點(diǎn)：（1）服務(wù)器提供并發(fā)控制，使兩個(gè)用戶不能同時(shí)修改相同的記錄。（2）不必在數(shù)據(jù)庫(kù)所在的機(jī)器上注冊(cè)。7.2MySQL的體系結(jié)構(gòu)

7.3MySQL的查詢語(yǔ)言

MySQL使用結(jié)構(gòu)化查詢語(yǔ)言（StructuredQueryLanguage，SQL）與服務(wù)器通訊。7.3.1表、列和數(shù)據(jù)類型表是數(shù)據(jù)在一個(gè)MySQL數(shù)據(jù)庫(kù)中的存儲(chǔ)機(jī)制。7.3.1表、列和數(shù)據(jù)類型3.2表中的列描述該表所表示的實(shí)體的屬性，每個(gè)列都有一個(gè)名字及各自的特性。列由兩部分組成：數(shù)據(jù)類型(datatype)和長(zhǎng)度(length)。7.3.1表、列和數(shù)據(jù)類型 MySQL常用的數(shù)據(jù)類型有數(shù)值類型、字符串類型及日期類型等。函數(shù)：（1）函數(shù)(function)是存儲(chǔ)在數(shù)據(jù)庫(kù)中的代碼塊。（2）函數(shù)可以把計(jì)算機(jī)結(jié)果直接返回給調(diào)用的SQL。7.3.2函數(shù)和SQL語(yǔ)句SQL語(yǔ)句：（1）非過(guò)程化程序設(shè)計(jì)語(yǔ)言。（2）簡(jiǎn)單易學(xué)7.4MySQL數(shù)據(jù)庫(kù)的安裝

（1）可以官方獲取安裝文件。（2）推薦下載MySQL社區(qū)版服務(wù)器。（3）最新版本和歷史版本各有優(yōu)點(diǎn)。（4）注意最新版本需要python3.6支持。7.5MySQL數(shù)據(jù)庫(kù)的基本操作

7.5.1數(shù)據(jù)庫(kù)操作（1）登錄：mysql-h-uroot-p（2）創(chuàng)建：createdatabase數(shù)據(jù)庫(kù)名（3）顯示存在數(shù)據(jù)庫(kù)：showdatabases（4）刪除數(shù)據(jù)庫(kù)：dropdatabase數(shù)據(jù)庫(kù)名創(chuàng)建表：創(chuàng)建表之前需要用“use數(shù)據(jù)庫(kù)名”來(lái)選擇當(dāng)前操作的數(shù)據(jù)庫(kù)。createtable表名(屬性名數(shù)據(jù)類型[完整性約束條件],

屬性名數(shù)據(jù)類型[完整性約束條件], ......

屬性名數(shù)據(jù)類型[完整性約束條件]);7.5.2數(shù)據(jù)庫(kù)表的操作7.5.2數(shù)據(jù)庫(kù)表的操作創(chuàng)建表的時(shí)候創(chuàng)建索引：createtable表名(屬性名數(shù)據(jù)類型[完整性約束條件],

屬性名數(shù)據(jù)類型[完整性約束條件], ......

屬性名數(shù)據(jù)類型[完整性約束條件]，[unique|fulltext|spatial]index|key [別名](屬性名1[(長(zhǎng)度)][asc|desc]));7.5.2數(shù)據(jù)庫(kù)表的操作【例7-1】假定需要在學(xué)生數(shù)據(jù)庫(kù)（StudentDB）中創(chuàng)建一張學(xué)生表，要求包括學(xué)生的標(biāo)識(shí)信息(id，自增)，學(xué)號(hào)（code，varchar(9)，主鍵），姓名（name，varchar(20)，非空），性別（sex，varchar(2)），出生日期（birth，date），所在學(xué)院（school，school，非空），所在班級(jí)（class，varchar(10)，非空），戶籍所在地（location，varchar(16)，非空），入學(xué)成績(jī)（score，float，非空，默認(rèn)值為0）。createtablestudent(idintnotnullauto_incrementprimarykey, codevarchar(10)notnull, namevarchar(20)notnull, sexvarchar(2), birthdate, schoolvarchar(20)notnull, classvarchar(10)notnull, locationvarchar(16)notnull, scorefloatnotnulldefault0.0, uniqueindexcodeidx(codeasc));7.5.2數(shù)據(jù)庫(kù)表的操作7.5.2數(shù)據(jù)庫(kù)表的操作查看表的基本定義:describe表名;查看表默認(rèn)的存儲(chǔ)引擎和字符編碼:showcreatetable表名;修改表名:showcreatetable表名;修改字段的數(shù)據(jù)類型:altertable表名modify屬性名數(shù)據(jù)類型;增加字段:altertable表名add屬性名1數(shù)據(jù)類型[完整性約束條件][first|after屬性名2];刪除字段:altertable表名drop屬性名;修改字段的排列位置:altertable表名modify屬性名1數(shù)據(jù)類型first|after屬性名2;….7.5.3數(shù)據(jù)庫(kù)視圖操作創(chuàng)建視圖：CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]刪除視圖：DROPVIEW[IFEXISTS]view_name[,view_name2]...修改視圖：參考創(chuàng)建視圖語(yǔ)法中的ORREPLACE關(guān)鍵字。查看視圖：showcreateviewview_name7.5.3數(shù)據(jù)庫(kù)視圖操作（1）普通查詢select*from表名7.5.4數(shù)據(jù)操作語(yǔ)言7.5.4數(shù)據(jù)操作語(yǔ)言（2）查詢特定行select*from表名where屬性=實(shí)體屬性名7.5.4數(shù)據(jù)操作語(yǔ)言（3）查詢特定列select屬性名from表名where屬性=實(shí)體屬性名7.5.4數(shù)據(jù)操作語(yǔ)言（4）條件查詢使用WHERE或者HAVING從句（HAVING表達(dá)的是第二條件，需要與GROUPBY從句配合使用，不能在WHERE子句中的項(xiàng)目使用HAVING。）算術(shù)運(yùn)算符比較運(yùn)算符邏輯運(yùn)算符運(yùn)算符說(shuō)明運(yùn)算符說(shuō)明+加*乘-減/除運(yùn)算符說(shuō)明運(yùn)算符說(shuō)明<

小于!=或<>不等于<=小于等于>=大于等于=等于>

大于運(yùn)算符說(shuō)明NOT或！邏輯非OR或||邏輯或AND或&&邏輯與7.5.4數(shù)據(jù)操作語(yǔ)言（4）條件查詢查找性別為男并且分?jǐn)?shù)大于528的學(xué)生情況

7.5.4數(shù)據(jù)操作語(yǔ)言（5）模糊查詢

對(duì)于查詢條件不明確的情況下，可以使用’like’關(guān)鍵字對(duì)帶有通配符的條件進(jìn)行查詢。常用的通配符查詢姓名中帶有“李”字的學(xué)生情況。通配符說(shuō)明%包含零個(gè)或多個(gè)字符組成的任意字符串_(下劃線)任意一個(gè)字符7.5.4數(shù)據(jù)操作語(yǔ)言（6）查詢排序

使用orderby子句對(duì)查詢返回的結(jié)果按一列或多列排序。orderby子句的語(yǔ)法格式為：ORDERBYcolumn_name[ASC|DESC][,…]

按分?jǐn)?shù)升序排序7.5.4數(shù)據(jù)操作語(yǔ)言（7）查詢分組與統(tǒng)計(jì)groupby關(guān)鍵字可以將查詢結(jié)果按照某個(gè)字段或多個(gè)字段進(jìn)行分組。

GROUPBY屬性名[HAVING條件表達(dá)式][WITHROLLUP]GROUPBY關(guān)鍵字通常與集合函數(shù)一起使用。函數(shù)說(shuō)明COUNT()用于統(tǒng)計(jì)記錄的條數(shù)SUM()用于計(jì)算字段的值的總和AVG()用于計(jì)算字段的值的平均值MAX()用于查詢字段的最大值MIN()用于查詢字段的最小值7.5.4數(shù)據(jù)操作語(yǔ)言（7）查詢分組與統(tǒng)計(jì)

統(tǒng)計(jì)每班學(xué)生姓名。7.5.4數(shù)據(jù)操作語(yǔ)言（8）多表查詢

內(nèi)連接（innerjoin）：select*from表1[inner]join表2on表1和表2之間的連接條件在student表和class_table表中查詢姓名為王靜的學(xué)生情況7.5.4數(shù)據(jù)操作語(yǔ)言（8）多表查詢

外連接：分為左連接、右連接和完全連接。左連接：select*from表1leftjoin表2on表1和表2之間的連接條件右連接：select*from表1rightjoin表2on表1和表2之間的連接條件

7.6開(kāi)發(fā)平臺(tái)與MySQL數(shù)據(jù)的連接

MySQL通過(guò)連接器（Connector）和API為Java、C++、.NET、PHP、Python、C等語(yǔ)言開(kāi)發(fā)的客戶端提供訪問(wèn)數(shù)據(jù)庫(kù)提供驅(qū)動(dòng)程序和訪問(wèn)接口。同時(shí)MySQL提供了ODBC、Java(JDBC)、Perl、Ruby等語(yǔ)言（連接驅(qū)動(dòng)）的數(shù)據(jù)庫(kù)訪問(wèn)實(shí)例程序。7.6開(kāi)發(fā)平臺(tái)與MySQL數(shù)據(jù)的連接

7.6.1Java語(yǔ)言訪問(wèn)MySQL（1）建立lib文件夾，將連接的JAR文件拷貝到lib目錄下。（2）在Eclipse上右鍵單擊工程項(xiàng)目名稱，在菜單中選擇Javabuildpath項(xiàng)目。（3）在彈出的界面中選擇Libraries分頁(yè)，點(diǎn)擊AddJARs...按鈕，將拷貝進(jìn)去的JAR文件加入項(xiàng)目中。（4）在需要訪問(wèn)數(shù)據(jù)庫(kù)的代碼中引入MySQLforJavaConnector的類庫(kù)。7.6開(kāi)發(fā)平臺(tái)與MySQL數(shù)據(jù)的連接

7.6.2.NET平臺(tái)訪問(wèn)MySQL（1）創(chuàng)建相關(guān)工程項(xiàng)目。（2）添加對(duì)MySQLConnector的引用。（3）在彈出對(duì)話框中，選擇“瀏覽”，找到MySQLConnectorforNet組件的安裝位置，打開(kāi)Assemblies文件夾下的v4.0或v4.5文件夾，選擇MySql.Data.dll動(dòng)態(tài)鏈接庫(kù)，選擇完成之后點(diǎn)擊“確定”按鈕。（4）在需要訪問(wèn)MySQL數(shù)據(jù)庫(kù)的頁(yè)面中使用“usingMySql.Data.MySqlClient;”。7.7MySQL數(shù)據(jù)庫(kù)的備份與恢復(fù)

最常用的MySQL數(shù)據(jù)庫(kù)備份與恢復(fù)方式：mysqldump（1）mysqldump備份mysqldump–hhostname-P端口-u用戶名-p密碼(–database)數(shù)據(jù)庫(kù)名>文件名.sql（2）還原數(shù)據(jù)庫(kù)mysql–hhostname–uusername–ppassworddatabasename<文件名.sql（3）導(dǎo)入數(shù)據(jù)庫(kù)mysql>source/home/sa/文件名.sqlMySQL的安全性主要由系統(tǒng)的內(nèi)部安全和網(wǎng)絡(luò)安全兩部分來(lái)決定。

保障MySQL數(shù)據(jù)庫(kù)的安全性需要以下配置：（1）修改root用戶口令，刪除空