MySQL數據庫基礎與實例教程第2章課件

孔祥盛MySQL數據庫基礎與實例教程

之MySQL基礎知識孔祥盛MySQL數據庫基礎與實例教程1123通過本章的學習，讀者可以掌握一些常用的MySQL命令，從而對MySQL數據庫進行一些簡單的管理。本章知識點較為繁雜，希望保持一份兒耐心。4字符集以及字符序設置MySQL概述MySQL數據庫管理MySQL表管理5系統(tǒng)變量內容一覽123通過本章的學習，讀者可以掌握一些常用的MySQ2123MySQL由瑞典MySQLAB公司開發(fā)。2008年1月MySQL被美國的SUN公司收購。2009年4月SUN公司又被美國的甲骨文（Oracle）公司收購。MySQL服務的安裝MySQL的特點MySQL服務的配置45啟動與停止MySQL服務MySQL配置文件2.1MySQL概述123MySQL由瑞典MySQLAB公司開發(fā)。MySQL服367MySQL由瑞典MySQLAB公司開發(fā)。2008年1月MySQL被美國的SUN公司收購。2009年4月SUN公司又被美國的甲骨文（Oracle）公司收購。連接MySQL服務器關系數據庫MySQL客戶機2.1MySQL概述67MySQL由瑞典MySQLAB公司開發(fā)。連接MySQL4

MySQL是一個單進程多線程、支持多用戶、基于客戶機/服務器（Client/Server簡稱C/S）的關系數據庫管理系統(tǒng)。性能高效跨平臺支持簡單易用開源支持多用戶2.1.1MySQL特點MySQL是一個單進程多線程、支持多用戶、基于客MySQL的使用流程

2.1.1MySQL特點MySQL的使用流程2.1.1MySQL特點MySQL其他概念（請參看本書內容）：MySQL服務MySQL服務實例MySQL服務器端口號2.1.1MySQL特點MySQL其他概念（請參看本書內容）：2.1.1MySQLMySQL服務的安裝（請參看本書內容）。建議自學，并上機操作。注意：本書使用的MySQL為5.6版本?？梢缘奖緯付ǖ木W址下載MySQL圖形化安裝包mysql-5.6.5-m8-win32.msi。

2.1.2MySQL服務的安裝MySQL服務的安裝（請參看本書內容）。2.1.2MySQMySQL服務的配置（請參看本書內容）。建議自學，并上機操作。配置過程中的其他知識點：OLAP與OLTP

EnableStrictMode選項字符集/字符序MySQL超級管理員root賬戶my.ini配置文件中2.1.3MySQL服務的配置MySQL服務的配置（請參看本書內容）。2.1.3MySQMySQL服務的啟動與停止（請參看本書內容）。建議自學，并上機操作。2.1.4啟動與停止MySQL服務MySQL服務的啟動與停止（請參看本書內容）。2.1.4啟my.ini配置文件包含了多種參數選項組，每個參數選項組通過“[]”指定，每個參數選項組可以配置多個參數信息。通常情況下，每個參數遵循“參數名=參數值”這種配置格式，參數名一般是小寫字母，參數名大小寫敏感。常用的參數選項組有“[client]”、“[mysql]”以及“[mysqld]”參數選項組。2.1.5MySQL配置文件my.ini配置文件包含了多種參數選項組，每個參數選項組通過[client]參數選項組：配置了MySQL自帶的MySQL5.6命令行窗口可以讀取的參數信息。常用的參數是port（默認值是3306）。修改該port值會導致新打開的MySQL5.6命令行窗口無法連接MySQL服務器。2.1.5MySQL配置文件[client]參數選項組：2.1.5MySQL配置文件[mysql]參數選項組：配置了MySQL客戶機程序mysql.exe可以讀取的參數信息。常用的參數有“prompt”、“default-character-set=gbk”。修改“[mysql]”參數選項組中的參數值，將直接影響新打開的MySQL客戶機。2.1.5MySQL配置文件[mysql]參數選項組：2.1.5MySQL配置文件[mysqld]參數選項組：配置了MySQL服務程序mysqld.exe可以讀取的參數信息，mysqld.exe啟動時，將[mysqld]參數選項組的參數信息加載到服務器內存，繼而生成MySQL服務實例。2.1.5MySQL配置文件[mysqld]參數選項組：2.1.5MySQL配置文件[mysqld]參數選項組：常用的參數有“port”、“basedir”、“datadir”、“character-set-server”、“sql_mode”、“max_connections”以及“default_storage_engine”等。2.1.5MySQL配置文件[mysqld]參數選項組：2.1.5MySQL配置文件[mysqld]參數選項組：修改“[mysqld]”參數選項組的參數值，只有重新啟動MySQL服務，將修改后的配置文件參數信息加載到服務器內存后，新配置文件才會在新的MySQL服務實例中生效。如果“[mysqld]”參數選項組的參數信息出現錯誤，將會導致MySQL服務無法啟動。2.1.5MySQL配置文件[mysqld]參數選項組：2.1.5MySQL配置文件MySQL客戶機（本書使用前兩個）：MySQL5.6命令行窗口

CMD命令提示符窗口

WEB瀏覽器（例如phpMyAdmin）第三方客戶機程序（例如MySQL-Front、MySQL

ManagerforMySQL等）2.1.6MySQL客戶機MySQL客戶機（本書使用前兩個）：2.1.6MySQL客phpMyAdmin2.1.6MySQL客戶機phpMyAdmin2.1.6MySQL客戶機MySQL-Front2.1.6MySQL客戶機MySQL-Front2.1.6MySQL客戶機MySQL

ManagerforMySQL2.1.6MySQL客戶機MySQL

ManagerforMySQL2.1.6M2.1.6MySQL客戶機HeidiSQL2.1.6MySQL客戶機HeidiSQLMySQL客戶機連接MySQL服務器須提供：合法的登錄主機：解決“from”的問題。合法的賬戶名以及密碼：解決“who”的問題。MySQL服務器主機名（或IP地址）：解決“to”的問題。端口號：解決“多卡多待”的問題。MySQL客戶機與MySQL服務器是同一臺主機時，主機名可以使用localhost（或者127.0.0.1）。2.1.7連接MySQL服務器MySQL客戶機連接MySQL服務器須提供：2.1.7連接當MySQL客戶機與MySQL服務器是同一臺主機時，打開命令提示符窗口，輸入mysql-h127.0.0.1-P3306-uroot–proot或者mysql-hlocalhost-P3306-uroot–proot然后回車（注意-p后面緊跟密碼root），即可實現本地MySQL客戶機與本地MySQL服務器之間的成功連接。2.1.7連接MySQL服務器當MySQL客戶機與MySQL服務器是同一臺主機時，打開命令2.1.7連接MySQL服務器2.1.7連接MySQL服務器123MySQL由瑞典MySQLAB公司開發(fā)，默認情況下MySQL使用的是latin1字符集。由此可能導致MySQL數據庫不夠支持中文字符串查詢或者發(fā)生中文字符串亂碼等問題。

MySQL字符集與字符序字符集及字符序概念MySQL的字符集轉換過程45MySQL字符集的設置SQL腳本文件2.2字符集以及字符序設置123MySQL由瑞典MySQLAB公司開發(fā)，默認25字符（Character）是人類語言最小的表義符號，例如‘A’、‘B’等。給定一系列字符，對每個字符賦予一個數值，用數值來代表對應的字符，這個數值就是字符的編碼（CharacterEncoding）。給定一系列字符并賦予對應的編碼后，所有這些“字符和編碼對”組成的集合就是字符集(CharacterSet)。

2.2.1字符集及字符序概念字符（Character）是人類語言最小的表義符號，2.2.1字符集及字符序概念2.2.1字符集及字符序概念字符序(Collation)是指在同一字符集內字符之間的比較規(guī)則。一個字符集包含多種字符序，每個字符序唯一對應一種字符集。MySQL字符序命名規(guī)則是：以字符序對應的字符集名稱開頭，以國家名居中（或以general居中），以ci、cs或bin結尾。ci表示大小寫不敏感，cs表示大小寫敏感，bin表示按二進制編碼值比較。2.2.1字符集及字符序概念字符序(Collation)是指在同一字符集內字符之使用MySQL命令showcharacterset;即可查看當前MySQL服務實例支持的字符集、字符集默認的字符序以及字符集占用的最大字節(jié)長度等信息latin1支持西歐字符、希臘字符等gbk支持中文簡體字符big5支持中文繁體字符utf8幾乎支持世界所有國家的字符。2.2.2MySQL字符集及字符序使用MySQL命令showcharacterse使用MySQL命令showvariableslike'character%';即可查看當前MySQL服務實例使用的字符集。2.2.2MySQL字符集及字符序使用MySQL命令2.2.2MySQL字符集及字符

character_set_client：MySQL客戶機字符集。character_set_connection：數據通信鏈路字符集，當MySQL客戶機向服務器發(fā)送請求時，請求數據以該字符集進行編碼。character_set_database：數據庫字符集。character_set_filesystem：MySQL服務器文件系統(tǒng)字符集，該值是固定的binary。2.2.2MySQL字符集及字符序character_set_client：MySQL客戶character_set_results：結果集的字符集，MySQL服務器向MySQL客戶機返回執(zhí)行結果時，執(zhí)行結果以該字符集進行編碼。character_set_server：MySQL服務實例字符集。character_set_system：元數據(字段名、表名、數據庫名等)的字符集，默認值為utf8。2.2.2MySQL字符集及字符序character_set_results：結果集的字符使用MySQL命令“showcollation;”即可查看當前MySQL服務實例支持的字符序。2.2.2MySQL字符集及字符序使用MySQL命令“showcollation;”2.2.3MySQL字符集的轉換過程2.2.3MySQL字符集的轉換過程方法1：修改my.ini配置文件，可修改MySQL默認的字符集。方法2：MySQL提供下列MySQL命令可以“臨時地”修改MySQL“當前會話的”字符集以及字符序。2.2.4MySQL字符集的設置方法1：修改my.ini配置文件，可修改MySQL默認的setcharacter_set_client=gbk;setcharacter_set_connection=gbk;setcharacter_set_database=gbk;setcharacter_set_results=gbk;setcharacter_set_server=gbk;setcollation_connection=gbk_chinese_ci;setcollation_database=gbk_chinese_ci;setcollation_server=gbk_chinese_ci;2.2.4MySQL字符集的設置setcharacter_set_client=g方法3：使用MySQL命令“setnamesgbk;”可以“臨時一次性地”設置character_set_client、character_set_connection以及character_set_results的字符集為gbk方法4：連接MySQL服務器時指定字符集mysql--default-character-set=字符集

-h服務器IP地址-u賬戶名–p密碼2.2.4MySQL字符集的設置方法3：使用MySQL命令“setnamesgbkSQL基本的執(zhí)行方法（兩種）：

\.C:\mysql\init.sqlsourceC:\mysql\init.sql2.2.5SQL腳本文件SQL基本的執(zhí)行方法（兩種）：2.2.5SQL腳本文件123

數據庫是存儲數據庫對象的容器。MySQL數據庫的管理主要包括數據庫的創(chuàng)建、選擇當前操作的數據庫、顯示數據庫結構以及刪除數據庫等操作

。查看數據庫創(chuàng)建數據庫顯示數據庫結構45選擇當前操作的數據庫刪除數據庫2.3MySQL數據庫管理123數據庫是存儲數據庫對象的容器。MySQL數據庫

createdatabasechoose;成功創(chuàng)建choose數據庫后，數據庫根目錄下會自動創(chuàng)建數據庫目錄。2.3.1創(chuàng)建數據庫createdatabasechoose;2.3

使用MySQL命令showdatabases;即可查看MySQL服務實例上所有的數據庫

2.3.2查看數據庫使用MySQL命令2.3.2查看數據庫

使用MySQL命令showcreatedatabasechoose;可以查看choose數據庫的相關信息（例如MySQL版本ID號、默認字符集等信息）。2.3.3顯示數據庫結構使用MySQL命令2.3.3顯示數據庫結構

執(zhí)行“usechoose;”命令后，后續(xù)的MySQL命令以及SQL語句將自動操作choose數據庫中所有數據庫對象。2.3.4選擇當前操作的數據庫執(zhí)行“usechoose;”命令后，后續(xù)的刪除student數據庫，使用SQL語句dropdatabasestudent;2.3.5刪除數據庫刪除student數據庫，使用SQL語句2.3.5刪除數據123表是數據庫中最為重要的數據庫對象

設置默認的存儲引擎MyISAM和InnoDB存儲引擎創(chuàng)建數據庫表45顯示表結構表記錄的管理2.4MySQL表管理123表是數據庫中最為重要的數據庫對象設置默認的存儲引擎M4567表是數據庫中最為重要的數據庫對象

刪除表InnoDB表空間2.4MySQL表管理67表是數據庫中最為重要的數據庫對象刪除表InnoDB表空46MySQL提供了插件式（Pluggable）的存儲引擎，存儲引擎是基于表的，同一個數據庫，不同的表，存儲引擎可以不同。甚至同一個數據庫表，在不同的場合可以應用不同的存儲引擎。

2.4.1MyISAM和InnoDB存儲引擎MySQL提供了插件式（Pluggable）的存儲引使用MySQL命令“showengines;”，即可查看MySQL服務實例支持的存儲引擎。2.4.1MyISAM和InnoDB存儲引擎使用MySQL命令“showengines1．InnoDB存儲引擎的特點支持外鍵（ForeignKey）支持事務（Transaction）：如果某張表主要提供OLTP支持，需要執(zhí)行大量的增、刪、改操作（insert、delete、update語句），出于事務安全方面的考慮，InnoDB存儲引擎是更好的選擇。最新版本的MySQL已經開始支持全文檢索。2.4.1MyISAM和InnoDB存儲引擎1．InnoDB存儲引擎的特點2.4.1MyISAM和In2．MyISAM存儲引擎的特點MyISAM具有檢查和修復表的大多數工具。MyISAM表可以被壓縮

MyISAM表最早支持全文索引

但MyISAM表不支持事務

但MyISAM表不支持外鍵（ForeignKey）。如果需要執(zhí)行大量的select語句，出于性能方面的考慮，MyISAM存儲引擎是更好的選擇。2.4.1MyISAM和InnoDB存儲引擎2．MyISAM存儲引擎的特點2.4.1MyISAM和InMySQL5.6默認的默認的存儲引擎是InnoDB。使用MySQL命令setdefault_storage_engine=MyISAM;可以“臨時地”將MySQL“當前會話的”存儲引擎設置為MyISAM，使用MySQL命令“showengines;”可以查看當前MySQL服務實例默認的存儲引擎。2.4.2設置默認的存儲引擎MySQL5.6默認的默認的存儲引擎是InnoDB。使用SQL語句“createtable表名”即可創(chuàng)建一個數據庫表。例如：

usechoose;setdefault_storage_engine=InnoDB;createtablemy_table(todaydatetime,namechar(20));2.4.3創(chuàng)建數據庫表使用SQL語句“createtable表名”即可創(chuàng)成功創(chuàng)建InnoDB存儲引擎的my_table表后，MySQL服務實例會在數據庫目錄choose中自動創(chuàng)建一個名字為表名、后綴名為frm的文件。my_table.frm。2.4.3創(chuàng)建數據庫表成功創(chuàng)建InnoDB存儲引擎的my_table表后，將my_table表的存儲引擎修改為MyISAM。usechoose;altertablemy_tableengine=MyISAM;2.4.3創(chuàng)建數據庫表將my_table表的存儲引擎修改為MyISAM。2.4.3使用MySQL命令“destable_name;”即可查看表名為table_name的表結構。2.4.4顯示表結構使用MySQL命令“destable_name;”使用MySQL命令“showcreatetabletable_name;”，查看名為table_name表的詳細信息。2.4.4顯示表結構使用MySQL命令“showcreatetablusechoose;insertintomy_tablevalues(now(),'a');insertintomy_tablevalues(now(),'a');insertintomy_tablevalues(now(),NULL);insertintomy_tablevalues(now(),'');select*frommy_table;

2.4.5表記錄的管理usechoose;2.4.5表記錄的管理任務布置1：完成本書場景描述1：MyISAM表記錄的管理的任務要求。任務布置2：完成本書場景描述2：InnoDB表記錄的管理的任務要求。2.4.5表記錄的管理任務布置1：完成本書場景描述1：MyISAM表記錄的管理的

1.共享表空間：MySQL服務實例承載的所有數據庫的所有InnoDB表的數據信息、索引信息、各種元數據信息以及事務的回滾（UNDO）信息，全部存放在共享表空間文件中。默認情況下該文件位于數據庫根目錄下，文件名是ibdata1，且文件的初始大小為10M?？梢允褂肕ySQL命令“showvariableslike‘innodb_data_file_path’;”查看該文件的的屬性。

2.4.6InnoDB表空間1.共享表空間：MySQL服務實例承載的所有數據庫的2.4.6InnoDB表空間2.4.6InnoDB表空間

2.獨享表空間：如果將全局系統(tǒng)變量innodb_file_per_table的值設置為ON（innodb_file_per_table的默認值為OFF），那么之后再創(chuàng)建InnoDB存儲引擎的新表，這些表的數據信息、索引信息都將保存到獨享表空間文件。2.4.6InnoDB表空間2.獨享表空間：如果將全局系統(tǒng)變量innodb_fi獨享表空間的設置2.4.6InnoDB表空間獨享表空間的設置2.4.6InnoDB表空間usechoose;altertablemy_tableengine=InnoDB;createtablesecond_table(todaydatetime,namechar(20));2.4.6InnoDB表空間usechoose;2.4.6InnoDB表空間2.4.6InnoDB表空間2.4.6InnoDB表空間任務布置3：完成本書場景描述3的任務要求。2.4.6InnoDB表空間任務布置3：完成本書場景描述3的任務要求。2.4.6Inn使用SQL語句droptabletable_name;即可刪除名為table_name的表。刪除表后，MySQL服務實例會自動刪除該表結構定義文件（例如second_table.frm文件），以及數據、索引信息。該命令慎用！

2.4.7刪除表使用SQL語句2.4.7刪除表123MySQL數據庫中，變量分為系統(tǒng)變量（以@@開頭）以及用戶自定義變量（以@開頭）。查看系統(tǒng)變量的值全局系統(tǒng)變量與會話系統(tǒng)變量設置系統(tǒng)變量的值2.5系統(tǒng)變量123MySQL數據庫中，變量分為系統(tǒng)變量（以@@開頭）以及67每一個MySQL客戶機成功連接MySQL服務器后，都會產生與之對應的會話。會話期間，MySQL服務實例會在MySQL服務器內存中生成與該會話對應的會話系統(tǒng)變量，這些會話系統(tǒng)變量的初始值是全局系統(tǒng)變量值的拷貝。

2.5.1全局系統(tǒng)變量與會話系統(tǒng)變量每一個MySQL客戶機成功連接MySQL服務器后，都由于各會話在會話期間所做的操作不盡相同，為了標記各個會話，會話系統(tǒng)變量又新增了12個變量。2.5.1全局系統(tǒng)變量與會話系統(tǒng)變量由于各會話在會話期間所做的操作不盡相同，為了標記各個2.5.1全局系統(tǒng)變量與會話系統(tǒng)變量2.5.1全局系統(tǒng)變量與會話系統(tǒng)變量

使用“showglobalvariables;”命令即可查看MySQL服務器內存中所有的全局系統(tǒng)變量信息（有393項之多）。使用“showsessionvariables;”命令即可查看與當前會話相關的所有會話系統(tǒng)變量以及所有的全局系統(tǒng)變量（有405項之多），此處session關鍵字可以省略。

2.5.2查看系統(tǒng)變量的值使用“showglobalvariables;”

MySQL中有一些系統(tǒng)變量僅僅是全局系統(tǒng)變量，例如innodb_data_file_path。showglobalvariableslike'innodb_data_file_path';showsessionvariableslike'innodb_data_file_path';showvariableslike'innodb_data_file_path';2.5.2查看系統(tǒng)變量的值MySQL中有一些系統(tǒng)變量僅僅是全局系統(tǒng)變量，例如

MySQL中有一些系統(tǒng)變量僅僅是會話系統(tǒng)變量，例如MySQL連接ID會話系統(tǒng)變量pseudo_thread_id。showsessionvariableslike'pseudo_thread_id';showvariableslike'pseudo_thread_id';2.5.2查看系統(tǒng)變量的值MySQL中有一些系統(tǒng)變量僅僅是會話系統(tǒng)變量，例如MMySQL中有一些系統(tǒng)變量既是全局系統(tǒng)變量，又是會話系統(tǒng)變量，例如系統(tǒng)變量character_set_client既是全局系統(tǒng)變量，又是會話系統(tǒng)變量。2.5.2查看系統(tǒng)變量的值MySQL中有一些系統(tǒng)變量既是全局系統(tǒng)變量，又是會話

此時查看會話系統(tǒng)變量的方法：showsessionvariableslike'character_set_client';showvariableslike'character_set_client';此時查看全局系統(tǒng)變量的方法：

showglobalvariableslike'character_set_client';2.5.2查看系統(tǒng)變量的值此時查看會話系統(tǒng)變量的方法：2.5.2查看系統(tǒng)變量作為MySQL編碼規(guī)范，MySQL中的系統(tǒng)變量以兩個“@”開頭。@@global僅僅用于標記全局系統(tǒng)變量；@@session僅僅用于標記會話系統(tǒng)變量；@@首先標記會話系統(tǒng)變量，如果會話系統(tǒng)變量不存在，則標記全局系統(tǒng)變量。

2.5.2查看系統(tǒng)變量的值作為MySQL編碼規(guī)范，MySQL中的系統(tǒng)變量以兩個“@”開任務布置4：完成本書場景描述4的任務要求。2.5.2查看系統(tǒng)變量的值任務布置4：完成本書場景描述4的任務要求。2.5.2查看系方法一：修改MySQL源代碼，然后對MySQL源代碼重新編譯（該方法適用于MySQL高級用戶，這里不作闡述）。方法二：最為簡單的方法是通過修改MySQL配置文件，繼而修改MySQL系統(tǒng)變量的值（fai方法需要重啟MySQL服務）。方法三：在MySQL服務運行期間，使用“set”命令重新設置系統(tǒng)變量的值。

2.5.2查看系統(tǒng)變量的值方法一：修改MySQL源代碼，然后對MySQL源代碼重新編譯設置全局系統(tǒng)變量的值的方法：set@@global.innodb_file_per_table=default;set@@global.innodb_file_per_table=ON;setglobalinnodb_file_per_table=ON;2.5.2查看系統(tǒng)變量的值設置全局系統(tǒng)變量的值的方法：2.5.2查看系統(tǒng)變量

設置會話系統(tǒng)變量的值的方法：set@@session.pseudo_thread_id=5;setsessionpseudo_thread_id=5;set@@pseudo_thread_id=5;setpseudo_thread_id=5;2.5.2查看系統(tǒng)變量的值設置會話系統(tǒng)變量的值的方法：2.5.2查看系統(tǒng)變量

MySQL中還有一些特殊的全局系統(tǒng)變量（例如log_bin、tmpdir、version、datadir），在MySQL服務實例運行期間它們的值不能動態(tài)修改，不能使用“set”命令進行重新設置，這種變量稱為靜態(tài)變量。數據庫管理員可以使用方法一或者方法二對靜態(tài)變量的值重新設置。

2.5.2查看系統(tǒng)變量的值MySQL中還有一些特殊的全局系統(tǒng)變量（例如log_步驟1：準備工作方法一：停止MySQL服務方法二：使用MySQL命令“flushtableswithreadlock;”將服務器內存中的數據“刷新”到數據庫文件中，同時鎖定所有表，以保證備份期間不會有新的數據寫入。2.6MySQL數據庫備份和恢復步驟1：準備工作2.6MySQL數據庫備份和恢復82步驟2：備份文件的選取如果數據庫中全部是MyISAM存儲引擎的表，最為簡單的數據庫備份方法就是直接“備份”整個數據庫目錄。如果某個數據庫中還存在InnoDB存儲引擎的表，此時不僅需要“備份”整個數據庫目錄，還需要備份ibdata1表空間文件以及重做日志文件ib_logfile0與ib_logfile1。2.6MySQL數據庫備份和恢復步驟2：備份文件的選取2.6MySQL數據庫備份和恢復83步驟2：備份文件的選取數據庫備份時，建議將MySQL配置文件（例如my.ini配置文件）一并進行備份。2.6MySQL數據庫備份和恢復步驟2：備份文件的選取2.6MySQL數據庫備份和恢復84步驟3：數據庫恢復首先停止MySQL服務；然后將整個數據庫目錄、MySQL配置文件（例如my.ini配置文件）、ibdata1共享表空間文件以及重做日志文件ib_logfile0與ib_logfile1復制到新MySQL服務器對應的路徑，即可恢復數據庫中的數據。2.6MySQL數據庫備份和恢復步驟3：數據庫恢復2.6MySQL數據庫備份和恢復85ThanksThanks86MySQL數據庫基礎與實例教程第2章課件87孔祥盛MySQL數據庫基礎與實例教程

之MySQL基礎知識孔祥盛MySQL數據庫基礎與實例教程88123通過本章的學習，讀者可以掌握一些常用的MySQL命令，從而對MySQL數據庫進行一些簡單的管理。本章知識點較為繁雜，希望保持一份兒耐心。4字符集以及字符序設置MySQL概述MySQL數據庫管理MySQL表管理5系統(tǒng)變量內容一覽123通過本章的學習，讀者可以掌握一些常用的MySQ89123MySQL由瑞典MySQLAB公司開發(fā)。2008年1月MySQL被美國的SUN公司收購。2009年4月SUN公司又被美國的甲骨文（Oracle）公司收購。MySQL服務的安裝MySQL的特點MySQL服務的配置45啟動與停止MySQL服務MySQL配置文件2.1MySQL概述123MySQL由瑞典MySQLAB公司開發(fā)。MySQL服9067MySQL由瑞典MySQLAB公司開發(fā)。2008年1月MySQL被美國的SUN公司收購。2009年4月SUN公司又被美國的甲骨文（Oracle）公司收購。連接MySQL服務器關系數據庫MySQL客戶機2.1MySQL概述67MySQL由瑞典MySQLAB公司開發(fā)。連接MySQL91

MySQL是一個單進程多線程、支持多用戶、基于客戶機/服務器（Client/Server簡稱C/S）的關系數據庫管理系統(tǒng)。性能高效跨平臺支持簡單易用開源支持多用戶2.1.1MySQL特點MySQL是一個單進程多線程、支持多用戶、基于客MySQL的使用流程

2.1.1MySQL特點MySQL的使用流程2.1.1MySQL特點MySQL其他概念（請參看本書內容）：MySQL服務MySQL服務實例MySQL服務器端口號2.1.1MySQL特點MySQL其他概念（請參看本書內容）：2.1.1MySQLMySQL服務的安裝（請參看本書內容）。建議自學，并上機操作。注意：本書使用的MySQL為5.6版本?？梢缘奖緯付ǖ木W址下載MySQL圖形化安裝包mysql-5.6.5-m8-win32.msi。

2.1.2MySQL服務的安裝MySQL服務的安裝（請參看本書內容）。2.1.2MySQMySQL服務的配置（請參看本書內容）。建議自學，并上機操作。配置過程中的其他知識點：OLAP與OLTP

EnableStrictMode選項字符集/字符序MySQL超級管理員root賬戶my.ini配置文件中2.1.3MySQL服務的配置MySQL服務的配置（請參看本書內容）。2.1.3MySQMySQL服務的啟動與停止（請參看本書內容）。建議自學，并上機操作。2.1.4啟動與停止MySQL服務MySQL服務的啟動與停止（請參看本書內容）。2.1.4啟my.ini配置文件包含了多種參數選項組，每個參數選項組通過“[]”指定，每個參數選項組可以配置多個參數信息。通常情況下，每個參數遵循“參數名=參數值”這種配置格式，參數名一般是小寫字母，參數名大小寫敏感。常用的參數選項組有“[client]”、“[mysql]”以及“[mysqld]”參數選項組。2.1.5MySQL配置文件my.ini配置文件包含了多種參數選項組，每個參數選項組通過[client]參數選項組：配置了MySQL自帶的MySQL5.6命令行窗口可以讀取的參數信息。常用的參數是port（默認值是3306）。修改該port值會導致新打開的MySQL5.6命令行窗口無法連接MySQL服務器。2.1.5MySQL配置文件[client]參數選項組：2.1.5MySQL配置文件[mysql]參數選項組：配置了MySQL客戶機程序mysql.exe可以讀取的參數信息。常用的參數有“prompt”、“default-character-set=gbk”。修改“[mysql]”參數選項組中的參數值，將直接影響新打開的MySQL客戶機。2.1.5MySQL配置文件[mysql]參數選項組：2.1.5MySQL配置文件[mysqld]參數選項組：配置了MySQL服務程序mysqld.exe可以讀取的參數信息，mysqld.exe啟動時，將[mysqld]參數選項組的參數信息加載到服務器內存，繼而生成MySQL服務實例。2.1.5MySQL配置文件[mysqld]參數選項組：2.1.5MySQL配置文件[mysqld]參數選項組：常用的參數有“port”、“basedir”、“datadir”、“character-set-server”、“sql_mode”、“max_connections”以及“default_storage_engine”等。2.1.5MySQL配置文件[mysqld]參數選項組：2.1.5MySQL配置文件[mysqld]參數選項組：修改“[mysqld]”參數選項組的參數值，只有重新啟動MySQL服務，將修改后的配置文件參數信息加載到服務器內存后，新配置文件才會在新的MySQL服務實例中生效。如果“[mysqld]”參數選項組的參數信息出現錯誤，將會導致MySQL服務無法啟動。2.1.5MySQL配置文件[mysqld]參數選項組：2.1.5MySQL配置文件MySQL客戶機（本書使用前兩個）：MySQL5.6命令行窗口

CMD命令提示符窗口

WEB瀏覽器（例如phpMyAdmin）第三方客戶機程序（例如MySQL-Front、MySQL

ManagerforMySQL等）2.1.6MySQL客戶機MySQL客戶機（本書使用前兩個）：2.1.6MySQL客phpMyAdmin2.1.6MySQL客戶機phpMyAdmin2.1.6MySQL客戶機MySQL-Front2.1.6MySQL客戶機MySQL-Front2.1.6MySQL客戶機MySQL

ManagerforMySQL2.1.6MySQL客戶機MySQL

ManagerforMySQL2.1.6M2.1.6MySQL客戶機HeidiSQL2.1.6MySQL客戶機HeidiSQLMySQL客戶機連接MySQL服務器須提供：合法的登錄主機：解決“from”的問題。合法的賬戶名以及密碼：解決“who”的問題。MySQL服務器主機名（或IP地址）：解決“to”的問題。端口號：解決“多卡多待”的問題。MySQL客戶機與MySQL服務器是同一臺主機時，主機名可以使用localhost（或者127.0.0.1）。2.1.7連接MySQL服務器MySQL客戶機連接MySQL服務器須提供：2.1.7連接當MySQL客戶機與MySQL服務器是同一臺主機時，打開命令提示符窗口，輸入mysql-h127.0.0.1-P3306-uroot–proot或者mysql-hlocalhost-P3306-uroot–proot然后回車（注意-p后面緊跟密碼root），即可實現本地MySQL客戶機與本地MySQL服務器之間的成功連接。2.1.7連接MySQL服務器當MySQL客戶機與MySQL服務器是同一臺主機時，打開命令2.1.7連接MySQL服務器2.1.7連接MySQL服務器123MySQL由瑞典MySQLAB公司開發(fā)，默認情況下MySQL使用的是latin1字符集。由此可能導致MySQL數據庫不夠支持中文字符串查詢或者發(fā)生中文字符串亂碼等問題。

MySQL字符集與字符序字符集及字符序概念MySQL的字符集轉換過程45MySQL字符集的設置SQL腳本文件2.2字符集以及字符序設置123MySQL由瑞典MySQLAB公司開發(fā)，默認112字符（Character）是人類語言最小的表義符號，例如‘A’、‘B’等。給定一系列字符，對每個字符賦予一個數值，用數值來代表對應的字符，這個數值就是字符的編碼（CharacterEncoding）。給定一系列字符并賦予對應的編碼后，所有這些“字符和編碼對”組成的集合就是字符集(CharacterSet)。

2.2.1字符集及字符序概念字符（Character）是人類語言最小的表義符號，2.2.1字符集及字符序概念2.2.1字符集及字符序概念字符序(Collation)是指在同一字符集內字符之間的比較規(guī)則。一個字符集包含多種字符序，每個字符序唯一對應一種字符集。MySQL字符序命名規(guī)則是：以字符序對應的字符集名稱開頭，以國家名居中（或以general居中），以ci、cs或bin結尾。ci表示大小寫不敏感，cs表示大小寫敏感，bin表示按二進制編碼值比較。2.2.1字符集及字符序概念字符序(Collation)是指在同一字符集內字符之使用MySQL命令showcharacterset;即可查看當前MySQL服務實例支持的字符集、字符集默認的字符序以及字符集占用的最大字節(jié)長度等信息latin1支持西歐字符、希臘字符等gbk支持中文簡體字符big5支持中文繁體字符utf8幾乎支持世界所有國家的字符。2.2.2MySQL字符集及字符序使用MySQL命令showcharacterse使用MySQL命令showvariableslike'character%';即可查看當前MySQL服務實例使用的字符集。2.2.2MySQL字符集及字符序使用MySQL命令2.2.2MySQL字符集及字符

character_set_client：MySQL客戶機字符集。character_set_connection：數據通信鏈路字符集，當MySQL客戶機向服務器發(fā)送請求時，請求數據以該字符集進行編碼。character_set_database：數據庫字符集。character_set_filesystem：MySQL服務器文件系統(tǒng)字符集，該值是固定的binary。2.2.2MySQL字符集及字符序character_set_client：MySQL客戶character_set_results：結果集的字符集，MySQL服務器向MySQL客戶機返回執(zhí)行結果時，執(zhí)行結果以該字符集進行編碼。character_set_server：MySQL服務實例字符集。character_set_system：元數據(字段名、表名、數據庫名等)的字符集，默認值為utf8。2.2.2MySQL字符集及字符序character_set_results：結果集的字符使用MySQL命令“showcollation;”即可查看當前MySQL服務實例支持的字符序。2.2.2MySQL字符集及字符序使用MySQL命令“showcollation;”2.2.3MySQL字符集的轉換過程2.2.3MySQL字符集的轉換過程方法1：修改my.ini配置文件，可修改MySQL默認的字符集。方法2：MySQL提供下列MySQL命令可以“臨時地”修改MySQL“當前會話的”字符集以及字符序。2.2.4MySQL字符集的設置方法1：修改my.ini配置文件，可修改MySQL默認的setcharacter_set_client=gbk;setcharacter_set_connection=gbk;setcharacter_set_database=gbk;setcharacter_set_results=gbk;setcharacter_set_server=gbk;setcollation_connection=gbk_chinese_ci;setcollation_database=gbk_chinese_ci;setcollation_server=gbk_chinese_ci;2.2.4MySQL字符集的設置setcharacter_set_client=g方法3：使用MySQL命令“setnamesgbk;”可以“臨時一次性地”設置character_set_client、character_set_connection以及character_set_results的字符集為gbk方法4：連接MySQL服務器時指定字符集mysql--default-character-set=字符集

-h服務器IP地址-u賬戶名–p密碼2.2.4MySQL字符集的設置方法3：使用MySQL命令“setnamesgbkSQL基本的執(zhí)行方法（兩種）：

\.C:\mysql\init.sqlsourceC:\mysql\init.sql2.2.5SQL腳本文件SQL基本的執(zhí)行方法（兩種）：2.2.5SQL腳本文件123

數據庫是存儲數據庫對象的容器。MySQL數據庫的管理主要包括數據庫的創(chuàng)建、選擇當前操作的數據庫、顯示數據庫結構以及刪除數據庫等操作

。查看數據庫創(chuàng)建數據庫顯示數據庫結構45選擇當前操作的數據庫刪除數據庫2.3MySQL數據庫管理123數據庫是存儲數據庫對象的容器。MySQL數據庫

createdatabasechoose;成功創(chuàng)建choose數據庫后，數據庫根目錄下會自動創(chuàng)建數據庫目錄。2.3.1創(chuàng)建數據庫createdatabasechoose;2.3

使用MySQL命令showdatabases;即可查看MySQL服務實例上所有的數據庫

2.3.2查看數據庫使用MySQL命令2.3.2查看數據庫

使用MySQL命令showcreatedatabasechoose;可以查看choose數據庫的相關信息（例如MySQL版本ID號、默認字符集等信息）。2.3.3顯示數據庫結構使用MySQL命令2.3.3顯示數據庫結構

執(zhí)行“usechoose;”命令后，后續(xù)的MySQL命令以及SQL語句將自動操作choose數據庫中所有數據庫對象。2.3.4選擇當前操作的數據庫執(zhí)行“usechoose;”命令后，后續(xù)的刪除student數據庫，使用SQL語句dropdatabasestudent;2.3.5刪除數據庫刪除student數據庫，使用SQL語句2.3.5刪除數據123表是數據庫中最為重要的數據庫對象

設置默認的存儲引擎MyISAM和InnoDB存儲引擎創(chuàng)建數據庫表45顯示表結構表記錄的管理2.4MySQL表管理123表是數據庫中最為重要的數據庫對象設置默認的存儲引擎M13267表是數據庫中最為重要的數據庫對象

刪除表InnoDB表空間2.4MySQL表管理67表是數據庫中最為重要的數據庫對象刪除表InnoDB表空133MySQL提供了插件式（Pluggable）的存儲引擎，存儲引擎是基于表的，同一個數據庫，不同的表，存儲引擎可以不同。甚至同一個數據庫表，在不同的場合可以應用不同的存儲引擎。

2.4.1MyISAM和InnoDB存儲引擎MySQL提供了插件式（Pluggable）的存儲引使用MySQL命令“showengines;”，即可查看MySQL服務實例支持的存儲引擎。2.4.1MyISAM和InnoDB存儲引擎使用MySQL命令“showengines1．InnoDB存儲引擎的特點支持外鍵（ForeignKey）支持事務（Transaction）：如果某張表主要提供OLTP支持，需要執(zhí)行大量的增、刪、改操作（insert、delete、update語句），出于事務安全方面的考慮，InnoDB存儲引擎是更好的選擇。最新版本的MySQL已經開始支持全文檢索。2.4.1MyISAM和InnoDB存儲引擎1．InnoDB存儲引擎的特點2.4.1MyISAM和In2．MyISAM存儲引擎的特點MyISAM具有檢查和修復表的大多數工具。MyISAM表可以被壓縮

MyISAM表最早支持全文索引

但MyISAM表不支持事務

但MyISAM表不支持外鍵（ForeignKey）。如果需要執(zhí)行大量的select語句，出于性能方面的考慮，MyISAM存儲引擎是更好的選擇。2.4.1MyISAM和InnoDB存儲引擎2．MyISAM存儲引擎的特點2.4.1MyISAM和InMySQL5.6默認的默認的存儲引擎是InnoDB。使用MySQL命令setdefault_storage_engine=MyISAM;可以“臨時地”將MySQL“當前會話的”存儲引擎設置為MyISAM，使用MySQL命令“showengines;”可以查看當前MySQL服務實例默認的存儲引擎。2.4.2設置默認的存儲引擎MySQL5.6默認的默認的存儲引擎是InnoDB。使用SQL語句“createtable表名”即可創(chuàng)建一個數據庫表。例如：

usechoose;setdefault_storage_engine=InnoDB;createtablemy_table(todaydatetime,namechar(20));2.4.3創(chuàng)建數據庫表使用SQL語句“createtable表名”即可創(chuàng)成功創(chuàng)建InnoDB存儲引擎的my_table表后，MySQL服務實例會在數據庫目錄choose中自動創(chuàng)建一個名字為表名、后綴名為frm的文件。my_table.frm。2.4.3創(chuàng)建數據庫表成功創(chuàng)建InnoDB存儲引擎的my_table表后，將my_table表的存儲引擎修改為MyISAM。usechoose;altertablemy_tableengine=MyISAM;2.4.3創(chuàng)建數據庫表將my_table表的存儲引擎修改為MyISAM。2.4.3使用MySQL命令“destable_name;”即可查看表名為table_name的表結構。2.4.4顯示表結構使用MySQL命令“destable_name;”使用MySQL命令“showcreatetabletable_name;”，查看名為table_name表的詳細信息。2.4.4顯示表結構使用MySQL命令“showcreatetablusechoose;insertintomy_tablevalues(now(),'a');insertintomy_tablevalues(now(),'a');insertintomy_tablevalues(now(),NULL);insertintomy_tablevalues(now(),'');select*frommy_table;

2.4.5表記錄的管理usechoose;2.4.5表記錄的管理任務布置1：完成本書場景描述1：MyISAM表記錄的管理的任務要求。任務布置2：完成本書場景描述2：InnoDB表記錄的管理的任務要求。2.4.5表記錄的管理任務布置1：完成本書場景描述1：MyISAM表記錄的管理的

1.共享表空間：MySQL服務實例承載的所有數據庫的所有InnoDB表的數據信息、索引信息、各種元數據信息以及事務的回滾（UNDO）信息，全部存放在共享表空間文件中。默認情況下該文件位于數據庫根目錄下，文件名是ibdata1，且文件的初始大小為10M?？梢允褂肕ySQL命令“showvariableslike‘innodb_data_file_path’;”查看該文件的的屬性。

2.4.6InnoDB表空間1.共享表空間：MySQL服務實例承載的所有數據庫的2.4.6InnoDB表空間2.4.6InnoDB表空間

2.獨享表空間：如果將全局系統(tǒng)變量innodb_file_per_table的值設置為ON（innodb_file_per_table的默認值為OFF），那么之后再創(chuàng)建InnoDB存儲引擎的新表，這些表的數據信息、索引信息都將保存到獨享表空間文件。2.4.6InnoDB表空間2.獨享表空間：如果將全局系統(tǒng)變量innodb_fi獨享表空間的設置2.4.6InnoDB表空間獨享表空間的設置2.4.6InnoDB表空間usechoose;altertablemy_tableengine=InnoDB;createtablesecond_table(todaydatetime,namechar(20));2.4.6InnoDB表空間usechoose;2.4.6InnoDB表空間2.4.6InnoDB表空間2.4.6InnoDB表空間任務布置3：完成本書場景描述3的任務要求。2.4.6InnoDB表空間任務布置3：完成本書場景描述3的任務要求。2.4.6Inn使用SQL語句droptabletable_name;即可刪除名為table_name的表。刪除表后，MySQL服務實例會自動刪除該表結構定義文件（例如second_table.frm文件），以及數據、索引信息。該命令慎用！

2.4.7刪除表使用SQL語句2.4.7刪除表123MySQL數據庫中，變量分為系統(tǒng)變量（以@@開頭）以及用戶自定義變量（以@開頭）。查看系統(tǒng)變量的值全局系統(tǒng)變量與會話系統(tǒng)變量設置系統(tǒng)變量的值2.5系統(tǒng)變量123MySQL數據庫中，變量分為系統(tǒng)變量（以@@開頭）以及154每一個MySQL客戶機成功連接MySQL服務器后，都會產生與之對應的會話。會話期間，MySQL服務實例會在MySQL服務器內存中生成與該會話對應的會話系統(tǒng)變量，這些會話系統(tǒng)變量的初始值是全局系統(tǒng)變量值的拷貝。

2.5.1全局系統(tǒng)變量與會話系統(tǒng)變量每一個MySQL客戶機成功連接MySQL服務器后，都由于各會話在會話期間所做的操作不盡相同，為了標記各個會話，會話系統(tǒng)變量又新增了12個變量。2.5.1全局系統(tǒng)變量與會話系統(tǒng)變量由于各會話在會話期間所做的操作不盡相同，為了標記各個2.5.1全局系統(tǒng)變量與會話系統(tǒng)變量2.5.1全局系統(tǒng)變量與會話系統(tǒng)變量

使用“showglobalvariables;”命令即可查看MySQL服務器內存中所有的全局系統(tǒng)變量信息（有393項之多）。使用“showsessionvariables;”命令即可查看與當前會話相關的所有會話系統(tǒng)變量以及所有的全局系統(tǒng)變量（有405項之多），此處session關鍵字可以省略。

2.5.2查看系統(tǒng)變量的值使用“show