MySQL數(shù)據(jù)庫(kù)基礎(chǔ)與實(shí)例教程第2章.pptx

1、123 通過(guò)本章的學(xué)習(xí)，讀者可以掌握一些常用的MySQL命令，從而對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行一些簡(jiǎn)單的管理。 本章知識(shí)點(diǎn)較為繁雜，希望保持一份兒耐心。4字符集以及字符序設(shè)置MySQL概述 MySQL數(shù)據(jù)庫(kù)管理MySQL表管理5系統(tǒng)變量?jī)?nèi)容一覽 1123MySQL由瑞典MySQL AB公司開(kāi)發(fā)。2008年1月MySQL被美國(guó)的SUN公司收購(gòu)。2009年4月SUN公司又被美國(guó)的甲骨文（Oracle）公司收購(gòu)。MySQL服務(wù)的安裝MySQL的特點(diǎn)MySQL服務(wù)的配置45啟動(dòng)與停止MySQL服務(wù)MySQL配置文件2.1 MySQL概述 267MySQL由瑞典MySQL AB公司開(kāi)發(fā)。2008年1月MySQ

2、L被美國(guó)的SUN公司收購(gòu)。2009年4月SUN公司又被美國(guó)的甲骨文（Oracle）公司收購(gòu)。連接MySQL服務(wù)器關(guān)系數(shù)據(jù)庫(kù)MySQL客戶機(jī)2.1 MySQL概述 3 MySQL是一個(gè)單進(jìn)程多線程、支持多用戶、基于客戶機(jī)/服務(wù)器（Client/Server簡(jiǎn)稱C/S）的關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng) 。 性能高效 跨平臺(tái)支持 簡(jiǎn)單易用 開(kāi)源 支持多用戶2.1.1 MySQL特點(diǎn)MySQL的使用流程 2.1.1 MySQL特點(diǎn)MySQL其他概念（請(qǐng)參看本書(shū)內(nèi)容）： MySQL服務(wù) MySQL服務(wù)實(shí)例 MySQL服務(wù)器 端口號(hào)2.1.1 MySQL特點(diǎn)MySQL服務(wù)的安裝（請(qǐng)參看本書(shū)內(nèi)容）。建議自學(xué)，并上機(jī)操作

3、。注意：本書(shū)使用的MySQL為5.6版本?？梢缘奖緯?shū)指定的網(wǎng)址下載MySQL圖形化安裝包mysql-5.6.5-m8-win32.msi。 2.1.2 MySQL服務(wù)的安裝MySQL服務(wù)的配置（請(qǐng)參看本書(shū)內(nèi)容）。建議自學(xué)，并上機(jī)操作。配置過(guò)程中的其他知識(shí)點(diǎn)： OLAP與OLTP Enable Strict Mode選項(xiàng) 字符集/字符序 MySQL超級(jí)管理員root賬戶 my.ini配置文件中 2.1.3 MySQL服務(wù)的配置MySQL服務(wù)的啟動(dòng)與停止（請(qǐng)參看本書(shū)內(nèi)容）。建議自學(xué)，并上機(jī)操作。2.1.4 啟動(dòng)與停止MySQL服務(wù)my.ini配置文件包含了多種參數(shù)選項(xiàng)組，每個(gè)參數(shù)選項(xiàng)組通過(guò)“ ”指

4、定，每個(gè)參數(shù)選項(xiàng)組可以配置多個(gè)參數(shù)信息。通常情況下，每個(gè)參數(shù)遵循“參數(shù)名=參數(shù)值”這種配置格式，參數(shù)名一般是小寫字母，參數(shù)名大小寫敏感。常用的參數(shù)選項(xiàng)組有“client”、“mysql”以及“mysqld”參數(shù)選項(xiàng)組。2.1.5 MySQL配置文件client參數(shù)選項(xiàng)組： 配置了MySQL自帶的MySQL5.6 命令行窗口可以讀取的參數(shù)信息。 常用的參數(shù)是port（默認(rèn)值是3306）。 修改該port值會(huì)導(dǎo)致新打開(kāi)的MySQL5.6 命令行窗口無(wú)法連接MySQL服務(wù)器。2.1.5 MySQL配置文件mysql參數(shù)選項(xiàng)組： 配置了MySQL客戶機(jī)程序mysql.exe可以讀取的參數(shù)信息。 常用的

5、參數(shù)有“prompt”、“default-character-set=gbk”。 修改 “mysql”參數(shù)選項(xiàng)組中的參數(shù)值，將直接影響新打開(kāi)的MySQL客戶機(jī)。2.1.5 MySQL配置文件mysqld參數(shù)選項(xiàng)組： 配置了MySQL服務(wù)程序mysqld.exe可以讀取的參數(shù)信息，mysqld.exe啟動(dòng)時(shí)，將mysqld參數(shù)選項(xiàng)組的參數(shù)信息加載到服務(wù)器內(nèi)存，繼而生成MySQL服務(wù)實(shí)例。2.1.5 MySQL配置文件mysqld參數(shù)選項(xiàng)組： 常用的參數(shù)有“port”、“basedir”、“datadir”、“character-set-server”、“sql_mode”、“max_connec

6、tions”以及“default_storage_engine”等。2.1.5 MySQL配置文件mysqld參數(shù)選項(xiàng)組： 修改“mysqld”參數(shù)選項(xiàng)組的參數(shù)值，只有重新啟動(dòng)MySQL服務(wù)，將修改后的配置文件參數(shù)信息加載到服務(wù)器內(nèi)存后，新配置文件才會(huì)在新的MySQL服務(wù)實(shí)例中生效。 如果“mysqld”參數(shù)選項(xiàng)組的參數(shù)信息出現(xiàn)錯(cuò)誤，將會(huì)導(dǎo)致MySQL服務(wù)無(wú)法啟動(dòng)。2.1.5 MySQL配置文件MySQL客戶機(jī)（本書(shū)使用前兩個(gè)）： MySQL5.6命令行窗口 CMD命令提示符窗口 WEB瀏覽器（例如phpMyAdmin） 第三方客戶機(jī)程序（例如MySQL-Front、MySQLManager

7、for MySQL等）2.1.6 MySQL客戶機(jī)phpMyAdmin2.1.6 MySQL客戶機(jī)MySQL-Front2.1.6 MySQL客戶機(jī)MySQLManager for MySQL2.1.6 MySQL客戶機(jī)2.1.6 MySQL客戶機(jī)HeidiSQLMySQL客戶機(jī)連接MySQL服務(wù)器須提供： 合法的登錄主機(jī)：解決“from”的問(wèn)題。 合法的賬戶名以及密碼：解決“who”的問(wèn)題。 MySQL服務(wù)器主機(jī)名（或IP地址）：解決“to”的問(wèn)題。 端口號(hào)：解決“多卡多待”的問(wèn)題。MySQL客戶機(jī)與MySQL服務(wù)器是同一臺(tái)主機(jī)時(shí)，主機(jī)名可以使用localhost（或者127.0.0.1）。

8、2.1.7 連接MySQL服務(wù)器當(dāng)MySQL客戶機(jī)與MySQL服務(wù)器是同一臺(tái)主機(jī)時(shí)，打開(kāi)命令提示符窗口，輸入mysql -h 127.0.0.1 -P 3306 -u root proot或者mysql -h localhost -P 3306 -u root proot然后回車（注意-p后面緊跟密碼root），即可實(shí)現(xiàn)本地MySQL客戶機(jī)與本地MySQL服務(wù)器之間的成功連接。2.1.7 連接MySQL服務(wù)器2.1.7 連接MySQL服務(wù)器123 MySQL由瑞典MySQL AB公司開(kāi)發(fā)，默認(rèn)情況下MySQL使用的是latin1字符集。 由此可能導(dǎo)致MySQL數(shù)據(jù)庫(kù)不夠支持中文字符串查詢或者發(fā)

9、生中文字符串亂碼等問(wèn)題。 MySQL字符集與字符序字符集及字符序概念MySQL的字符集轉(zhuǎn)換過(guò)程45MySQL字符集的設(shè)置SQL腳本文件2.2 字符集以及字符序設(shè)置24 字符（Character）是人類語(yǔ)言最小的表義符號(hào)，例如A、B等。給定一系列字符，對(duì)每個(gè)字符賦予一個(gè)數(shù)值，用數(shù)值來(lái)代表對(duì)應(yīng)的字符，這個(gè)數(shù)值就是字符的編碼（Character Encoding）。 給定一系列字符并賦予對(duì)應(yīng)的編碼后，所有這些“字符和編碼對(duì)”組成的集合就是字符集(Character Set)。 2.2.1 字符集及字符序概念2.2.1 字符集及字符序概念 字符序(Collation)是指在同一字符集內(nèi)字符之間的比較規(guī)

10、則。一個(gè)字符集包含多種字符序，每個(gè)字符序唯一對(duì)應(yīng)一種字符集。 MySQL字符序命名規(guī)則是：以字符序?qū)?yīng)的字符集名稱開(kāi)頭，以國(guó)家名居中（或以general居中），以ci、cs或bin結(jié)尾。 ci表示大小寫不敏感，cs表示大小寫敏感，bin表示按二進(jìn)制編碼值比較。2.2.1 字符集及字符序概念 使用MySQL命令show character set;即可查看當(dāng)前MySQL服務(wù)實(shí)例支持的字符集、字符集默認(rèn)的字符序以及字符集占用的最大字節(jié)長(zhǎng)度等信息 latin1支持西歐字符、希臘字符等 gbk支持中文簡(jiǎn)體字符 big5支持中文繁體字符 utf8幾乎支持世界所有國(guó)家的字符。2.2.2 MySQL字符集及

11、字符序 使用MySQL命令show variables like character%;即可查看當(dāng)前MySQL服務(wù)實(shí)例使用的字符集。2.2.2 MySQL字符集及字符序 character_set_client：MySQL客戶機(jī)字符集。 character_set_connection：數(shù)據(jù)通信鏈路字符集，當(dāng)MySQL客戶機(jī)向服務(wù)器發(fā)送請(qǐng)求時(shí)，請(qǐng)求數(shù)據(jù)以該字符集進(jìn)行編碼。 character_set_database：數(shù)據(jù)庫(kù)字符集。 character_set_filesystem：MySQL服務(wù)器文件系統(tǒng)字符集，該值是固定的binary。2.2.2 MySQL字符集及字符序 characte

12、r_set_results：結(jié)果集的字符集，MySQL服務(wù)器向MySQL客戶機(jī)返回執(zhí)行結(jié)果時(shí)，執(zhí)行結(jié)果以該字符集進(jìn)行編碼。 character_set_server：MySQL服務(wù)實(shí)例字符集。 character_set_system：元數(shù)據(jù)(字段名、表名、數(shù)據(jù)庫(kù)名等) 的字符集，默認(rèn)值為utf8。2.2.2 MySQL字符集及字符序 使用MySQL命令“show collation;”即可查看當(dāng)前MySQL服務(wù)實(shí)例支持的字符序。2.2.2 MySQL字符集及字符序2.2.3 MySQL字符集的轉(zhuǎn)換過(guò)程 方法1：修改my.ini配置文件，可修改MySQL默認(rèn)的字符集。 方法2：MySQL提供下

13、列MySQL命令可以“臨時(shí)地”修改MySQL“當(dāng)前會(huì)話的”字符集以及字符序。2.2.4 MySQL字符集的設(shè)置 set character_set_client = gbk; set character_set_connection = gbk; set character_set_database = gbk; set character_set_results = gbk; set character_set_server = gbk; set collation_connection = gbk_chinese_ci ; set collation_database = gbk_chin

14、ese_ci ; set collation_server = gbk_chinese_ci ;2.2.4 MySQL字符集的設(shè)置 方法3：使用MySQL命令 “set names gbk;”可以“臨時(shí)一次性地”設(shè)置character_set_client、character_set_connection以及character_set_results的字符集為gbk 方法4：連接MySQL服務(wù)器時(shí)指定字符集 mysql -default-character-set=字符集 -h 服務(wù)器IP地址 -u 賬戶名 p密碼2.2.4 MySQL字符集的設(shè)置SQL基本的執(zhí)行方法（兩種）： . C:mys

15、qlinit.sql source C:mysqlinit.sql2.2.5 SQL腳本文件123 數(shù)據(jù)庫(kù)是存儲(chǔ)數(shù)據(jù)庫(kù)對(duì)象的容器。MySQL數(shù)據(jù)庫(kù)的管理主要包括數(shù)據(jù)庫(kù)的創(chuàng)建、選擇當(dāng)前操作的數(shù)據(jù)庫(kù)、顯示數(shù)據(jù)庫(kù)結(jié)構(gòu)以及刪除數(shù)據(jù)庫(kù)等操作 。查看數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)顯示數(shù)據(jù)庫(kù)結(jié)構(gòu)45選擇當(dāng)前操作的數(shù)據(jù)庫(kù)刪除數(shù)據(jù)庫(kù)2.3 MySQL數(shù)據(jù)庫(kù)管理 create database choose;成功創(chuàng)建choose數(shù)據(jù)庫(kù)后，數(shù)據(jù)庫(kù)根目錄下會(huì)自動(dòng)創(chuàng)建數(shù)據(jù)庫(kù)目錄。2.3.1 創(chuàng)建數(shù)據(jù)庫(kù) 使用MySQL命令show databases;即可查看MySQL服務(wù)實(shí)例上所有的數(shù)據(jù)庫(kù) 2.3.2 查看數(shù)據(jù)庫(kù) 使用MySQL命令

16、show create database choose;可以查看choose數(shù)據(jù)庫(kù)的相關(guān)信息（例如MySQL版本ID號(hào)、默認(rèn)字符集等信息）。2.3.3 顯示數(shù)據(jù)庫(kù)結(jié)構(gòu) 執(zhí)行“use choose;”命令后，后續(xù)的MySQL命令以及SQL語(yǔ)句將自動(dòng)操作choose數(shù)據(jù)庫(kù)中所有數(shù)據(jù)庫(kù)對(duì)象。2.3.4 選擇當(dāng)前操作的數(shù)據(jù)庫(kù)刪除student數(shù)據(jù)庫(kù)，使用SQL語(yǔ)句drop database student;2.3.5 刪除數(shù)據(jù)庫(kù)123表是數(shù)據(jù)庫(kù)中最為重要的數(shù)據(jù)庫(kù)對(duì)象 設(shè)置默認(rèn)的存儲(chǔ)引擎MyISAM和InnoDB存儲(chǔ)引擎創(chuàng)建數(shù)據(jù)庫(kù)表45顯示表結(jié)構(gòu)表記錄的管理2.4 MySQL表管理 4467表是數(shù)據(jù)庫(kù)中

17、最為重要的數(shù)據(jù)庫(kù)對(duì)象 刪除表InnoDB表空間2.4 MySQL表管理 45 MySQL提供了插件式（Pluggable）的存儲(chǔ)引擎，存儲(chǔ)引擎是基于表的，同一個(gè)數(shù)據(jù)庫(kù)，不同的表，存儲(chǔ)引擎可以不同。甚至同一個(gè)數(shù)據(jù)庫(kù)表，在不同的場(chǎng)合可以應(yīng)用不同的存儲(chǔ)引擎。 2.4.1 MyISAM和InnoDB存儲(chǔ)引擎 使用MySQL命令“show engines;”，即可查看MySQL服務(wù)實(shí)例支持的存儲(chǔ)引擎。 2.4.1 MyISAM和InnoDB存儲(chǔ)引擎1InnoDB存儲(chǔ)引擎的特點(diǎn) 支持外鍵（Foreign Key） 支持事務(wù)（Transaction）：如果某張表主要提供OLTP支持，需要執(zhí)行大量的增、刪、改

18、操作（insert、delete、update語(yǔ)句），出于事務(wù)安全方面的考慮，InnoDB存儲(chǔ)引擎是更好的選擇。 最新版本的MySQL已經(jīng)開(kāi)始支持全文檢索。2.4.1 MyISAM和InnoDB存儲(chǔ)引擎2MyISAM存儲(chǔ)引擎的特點(diǎn) MyISAM具有檢查和修復(fù)表的大多數(shù)工具。 MyISAM表可以被壓縮 MyISAM表最早支持全文索引 但MyISAM表不支持事務(wù) 但MyISAM表不支持外鍵（Foreign Key）。如果需要執(zhí)行大量的select語(yǔ)句，出于性能方面的考慮，MyISAM存儲(chǔ)引擎是更好的選擇。2.4.1 MyISAM和InnoDB存儲(chǔ)引擎 MySQL5.6默認(rèn)的默認(rèn)的存儲(chǔ)引擎是Inno

19、DB。使用MySQL命令set default_storage_engine=MyISAM;可以“臨時(shí)地”將MySQL“當(dāng)前會(huì)話的”存儲(chǔ)引擎設(shè)置為MyISAM，使用MySQL命令“show engines;”可以查看當(dāng)前MySQL服務(wù)實(shí)例默認(rèn)的存儲(chǔ)引擎。 2.4.2 設(shè)置默認(rèn)的存儲(chǔ)引擎 使用SQL語(yǔ)句“create table表名”即可創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)表。例如： use choose;set default_storage_engine=InnoDB;create table my_table(today datetime,name char(20);2.4.3 創(chuàng)建數(shù)據(jù)庫(kù)表 成功創(chuàng)建InnoD

20、B存儲(chǔ)引擎的my_table表后，MySQL服務(wù)實(shí)例會(huì)在數(shù)據(jù)庫(kù)目錄choose中自動(dòng)創(chuàng)建一個(gè)名字為表名、后綴名為frm的文件。my_table.frm。2.4.3 創(chuàng)建數(shù)據(jù)庫(kù)表將my_table表的存儲(chǔ)引擎修改為MyISAM。use choose;alter table my_table engine=MyISAM;2.4.3 創(chuàng)建數(shù)據(jù)庫(kù)表 使用MySQL命令“des table_name;”即可查看表名為table_name的表結(jié)構(gòu)。2.4.4 顯示表結(jié)構(gòu) 使用MySQL命令“show create table table_name;”，查看名為table_name表的詳細(xì)信息。 2.4.4

21、 顯示表結(jié)構(gòu)use choose;insert into my_table values(now(),a);insert into my_table values(now(),a);insert into my_table values(now(),NULL);insert into my_table values(now(),);select * from my_table; 2.4.5 表記錄的管理任務(wù)布置1：完成本書(shū)場(chǎng)景描述1 ：MyISAM表記錄的管理的任務(wù)要求。任務(wù)布置2：完成本書(shū)場(chǎng)景描述2：InnoDB表記錄的管理的任務(wù)要求。2.4.5 表記錄的管理 1.共享表空間：MySQL服務(wù)

22、實(shí)例承載的所有數(shù)據(jù)庫(kù)的所有InnoDB表的數(shù)據(jù)信息、索引信息、各種元數(shù)據(jù)信息以及事務(wù)的回滾（UNDO）信息，全部存放在共享表空間文件中。 默認(rèn)情況下該文件位于數(shù)據(jù)庫(kù)根目錄下，文件名是ibdata1，且文件的初始大小為10M?？梢允褂肕ySQL命令“show variables like innodb_data_file_path;”查看該文件的的屬性。 2.4.6 InnoDB表空間2.4.6 InnoDB表空間 2.獨(dú)享表空間：如果將全局系統(tǒng)變量innodb_file_per_table的值設(shè)置為ON（innodb_file_per_table的默認(rèn)值為OFF），那么之后再創(chuàng)建InnoDB存

23、儲(chǔ)引擎的新表，這些表的數(shù)據(jù)信息、索引信息都將保存到獨(dú)享表空間文件。2.4.6 InnoDB表空間獨(dú)享表空間的設(shè)置2.4.6 InnoDB表空間use choose;alter table my_table engine=InnoDB;create table second_table(today datetime,name char(20);2.4.6 InnoDB表空間2.4.6 InnoDB表空間任務(wù)布置3：完成本書(shū)場(chǎng)景描述3的任務(wù)要求。2.4.6 InnoDB表空間 使用SQL語(yǔ)句drop table table_name;即可刪除名為table_name的表。 刪除表后，MySQL服務(wù)

24、實(shí)例會(huì)自動(dòng)刪除該表結(jié)構(gòu)定義文件（例如second_table.frm文件），以及數(shù)據(jù)、索引信息。該命令慎用！ 2.4.7 刪除表123MySQL數(shù)據(jù)庫(kù)中，變量分為系統(tǒng)變量（以開(kāi)頭）以及用戶自定義變量（以開(kāi)頭）。 查看系統(tǒng)變量的值全局系統(tǒng)變量與會(huì)話系統(tǒng)變量設(shè)置系統(tǒng)變量的值2.5 系統(tǒng)變量66 每一個(gè)MySQL客戶機(jī)成功連接MySQL服務(wù)器后，都會(huì)產(chǎn)生與之對(duì)應(yīng)的會(huì)話。 會(huì)話期間，MySQL服務(wù)實(shí)例會(huì)在MySQL服務(wù)器內(nèi)存中生成與該會(huì)話對(duì)應(yīng)的會(huì)話系統(tǒng)變量，這些會(huì)話系統(tǒng)變量的初始值是全局系統(tǒng)變量值的拷貝。 2.5.1 全局系統(tǒng)變量與會(huì)話系統(tǒng)變量 由于各會(huì)話在會(huì)話期間所做的操作不盡相同，為了標(biāo)記各個(gè)會(huì)話

25、，會(huì)話系統(tǒng)變量又新增了12個(gè)變量。2.5.1 全局系統(tǒng)變量與會(huì)話系統(tǒng)變量2.5.1 全局系統(tǒng)變量與會(huì)話系統(tǒng)變量 使用“show global variables;”命令即可查看MySQL服務(wù)器內(nèi)存中所有的全局系統(tǒng)變量信息（有393項(xiàng)之多）。 使用“show session variables;”命令即可查看與當(dāng)前會(huì)話相關(guān)的所有會(huì)話系統(tǒng)變量以及所有的全局系統(tǒng)變量（有405項(xiàng)之多），此處session關(guān)鍵字可以省略。 2.5.2 查看系統(tǒng)變量的值 MySQL中有一些系統(tǒng)變量?jī)H僅是全局系統(tǒng)變量，例如 innodb_data_file_path。 show global variables like

26、innodb_data_file_path; show session variables like innodb_data_file_path; show variables like innodb_data_file_path;2.5.2 查看系統(tǒng)變量的值 MySQL中有一些系統(tǒng)變量?jī)H僅是會(huì)話系統(tǒng)變量，例如MySQL連接ID會(huì)話系統(tǒng)變量pseudo_thread_id。 show session variables like pseudo_thread_id; show variables like pseudo_thread_id;2.5.2 查看系統(tǒng)變量的值 MySQL中有一些系統(tǒng)變量

27、既是全局系統(tǒng)變量，又是會(huì)話系統(tǒng)變量，例如系統(tǒng)變量character_set_client 既是全局系統(tǒng)變量，又是會(huì)話系統(tǒng)變量。2.5.2 查看系統(tǒng)變量的值 此時(shí)查看會(huì)話系統(tǒng)變量的方法： show session variables like character_set_client; show variables like character_set_client; 此時(shí)查看全局系統(tǒng)變量的方法： show global variables like character_set_client;2.5.2 查看系統(tǒng)變量的值作為MySQL編碼規(guī)范，MySQL中的系統(tǒng)變量以兩個(gè)“”開(kāi)頭。 global

28、僅僅用于標(biāo)記全局系統(tǒng)變量； session僅僅用于標(biāo)記會(huì)話系統(tǒng)變量； 首先標(biāo)記會(huì)話系統(tǒng)變量，如果會(huì)話系統(tǒng)變量不存在，則標(biāo)記全局系統(tǒng)變量。 2.5.2 查看系統(tǒng)變量的值任務(wù)布置4：完成本書(shū)場(chǎng)景描述4的任務(wù)要求。2.5.2 查看系統(tǒng)變量的值方法一：修改MySQL源代碼，然后對(duì)MySQL源代碼重新編譯（該方法適用于MySQL高級(jí)用戶，這里不作闡述）。方法二：最為簡(jiǎn)單的方法是通過(guò)修改MySQL配置文件，繼而修改MySQL系統(tǒng)變量的值（fai方法需要重啟MySQL服務(wù)）。方法三：在MySQL服務(wù)運(yùn)行期間，使用“set”命令重新設(shè)置系統(tǒng)變量的值。 2.5.2 查看系統(tǒng)變量的值 設(shè)置全局系統(tǒng)變量的值的方法： set global.innodb_file_p