<HETF>MySQL核心技術(shù)指導(dǎo)手冊Author: fuqiangCreationDate: 2016-08-03LastUpdated: DocumentRef: MySQL核心技術(shù)指導(dǎo)手冊Version: 1.0DocRef:SAVEDATE\@"yyyy-MM-dd"2016-09-10文檔控制文檔控制更改記錄日期作者版本更改參考2016-09-11傅強1.0無前版本內(nèi)容目錄文檔控制 i更改記錄 i內(nèi)容目錄 ii1. Mysql概述 41.1. Mysql說明及特性 41.2. Mysql的安裝 51.3. Mysql的界面化工具 132. Mysql引擎 142.1. Mysql引擎介紹 142.2. 存儲引擎種類 142.3. InnoDB與Myisam對比 192.4. 常用的一些語句 203. Mysql的基本對象 213.1. 表 213.2. 索引 233.3. 視圖 304. 創(chuàng)建存儲過程／函數(shù) 344.1. 存儲過程 344.2. 函數(shù) 385. 鎖機制（事務(wù)控制） 425.1. 數(shù)據(jù)庫事務(wù) 425.2. 數(shù)據(jù)庫鎖機制 466. Mysql性能優(yōu)化 596.1. 系統(tǒng)優(yōu)化 596.2. Mysql優(yōu)化配置 626.3. 應(yīng)用程序SQL語句+程序架構(gòu)調(diào)整 737. 主從復(fù)制&集群 837.1. 集群 837.2. 主從復(fù)制 897.3. 集群實施方案 1128. MysqlDBA 1248.1. 數(shù)據(jù)備份和恢復(fù) 1248.2. 數(shù)據(jù)庫遷移 1279. Mysql與其他數(shù)據(jù)庫區(qū)別（ORACLE） 13010. 常用Mysql腳本 13710.1. 數(shù)據(jù)字典導(dǎo)出（PDMReader

） 13710.2. 數(shù)據(jù)庫監(jiān)控常用腳本 14510.3. 數(shù)據(jù)庫性能查看腳本 147未結(jié)與已結(jié)問題 148未接問題 148已結(jié)問題 148未結(jié)與已結(jié)問題PAGE4of8Mysql概述Mysql說明及特性概述MySQL是一個開放源碼的小型關(guān)聯(lián)式（關(guān)系型）數(shù)據(jù)庫管理系統(tǒng)，開發(fā)者為瑞典MySQLAB公司。目前MySQL被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)站數(shù)據(jù)庫。特性（代碼可移植）使用C和C++編寫，并使用了多種編譯器進行測試，保證源代碼的可移植性。*（多操作系統(tǒng)）支持AIX、BSDi、FreeBSD、HP-UX、Linux、MacOS、NovellNetware、NetBSD、OpenBSD、OS/2Wrap、Solaris、SunOS、Windows等多種操作系統(tǒng)。*為多種編程語言提供了API。（例如c語言的一個api:intSTDCALLmysql_query(MYSQL*mysql,constchar*q);第一個參數(shù)為mysql很多重要變量的指針，第二個為sql語句）（多線程）支持多線程，充分利用CPU資源，支持多用戶。*（多語言）提供多語言支持，常見的編碼如中文的GB2312、BIG5，日文的Shift_JIS等都可以用作數(shù)據(jù)表名和數(shù)據(jù)列名。提供TCP/IP、ODBC和JDBC等多種數(shù)據(jù)庫連接途徑?？梢蕴幚頁碛猩锨f條記錄的大型數(shù)據(jù)庫。四種數(shù)據(jù)庫對比Mysql的安裝1.2.1．mysql5.0安裝包/downloads/file/?id=4066591.2.2．mysql的安裝>mysql安裝圖文教程1mysql安裝向?qū)樱础癗ext”繼續(xù)>mysql圖文安裝教程2選擇安裝類型，有“Typical（默認）”、“Complete（完全）”、“Custom（用戶自定義）”三個選項，我們選擇“Custom”，有更多的選項，也方便熟悉安裝過程>mysql圖文安裝教程3在“DeveloperComponents（開發(fā)者部分）”上左鍵單擊，選擇“Thisfeature,andallsubfeatures,willbeinstalledonlocalharddrive.”，即“此部分，及下屬子部分內(nèi)容，全部安裝在本地硬盤上”。在上面的“MySQLServer（mysql服務(wù)器）”、“ClientPrograms（mysql客戶端程序）”、“Documentation（文檔）”也如此操作，以保證安裝所有文件。點選“Change...”，手動指定安裝目錄。>mysql圖文安裝教程4

填上安裝目錄，我的是“F:\Server\MySQL\MySQLServer5.0”，也建議不要放在與操作系統(tǒng)同一分區(qū)，這樣可以防止系統(tǒng)備份還原的時候，數(shù)據(jù)被清空。按“OK”繼續(xù)。>mysql圖文安裝教程5

返回剛才的界面，按“Next”繼續(xù)>mysql圖文安裝教程7正在安裝中，請稍候，直到出現(xiàn)下面的界面>mysql圖文安裝教程8

現(xiàn)在軟件安裝完成了，出現(xiàn)上面的界面，這里有一個很好的功能，mysql配置向?qū)В挥孟蛞郧耙粯?，自己手動亂七八糟的配置my.ini了，將“ConfiguretheMysqlServernow”前面的勾打上，點“Finish”結(jié)束軟件的安裝并啟動mysql配置向?qū)А?gt;mysql圖文安裝教程9mysql配置向?qū)咏缑?，按“Next”繼續(xù)>mysql圖文安裝教程10

選擇配置方式，“DetailedConfiguration（手動精確配置）”、“StandardConfiguration（標準配置）”，我們選擇“DetailedConfiguration”，方便熟悉配置過程>mysql圖文安裝教程11

選擇服務(wù)器類型，“DeveloperMachine（開發(fā)測試類，mysql占用很少資源）”、“ServerMachine（服務(wù)器類型，mysql占用較多資源）”、“DedicatedMySQLServerMachine（專門的數(shù)據(jù)庫服務(wù)器，mysql占用所有可用資源）”，大家根據(jù)自己的類型選擇了，一般選“ServerMachine”，不會太少，也不會占滿。>mysql圖文安裝教程12

選擇mysql數(shù)據(jù)庫的大致用途，“MultifunctionalDatabase（通用多功能型，好）”、“TransactionalDatabaseOnly（服務(wù)器類型，專注于事務(wù)處理，一般）”、“Non-TransactionalDatabaseOnly（非事務(wù)處理型，較簡單，主要做一些監(jiān)控、記數(shù)用，對MyISAM數(shù)據(jù)類型的支持僅限于non-transactional），隨自己的用途而選擇了，我這里選擇“TransactionalDatabaseOnly”，按“Next”繼續(xù)。>mysql圖文安裝教程13對InnoDBTablespace進行配置，就是為InnoDB數(shù)據(jù)庫文件選擇一個存儲空間，如果修改了，要記住位置，重裝的時候要選擇一樣的地方，否則可能會造成數(shù)據(jù)庫損壞，當然，對數(shù)據(jù)庫做個備份就沒問題了，這里不詳述。我這里沒有修改，使用用默認位置，直接按“Next”繼續(xù)>mysql圖文安裝教程14選擇您的網(wǎng)站的一般mysql訪問量，同時連接的數(shù)目，“DecisionSupport(DSS)/OLAP（20個左右）”、“OnlineTransactionProcessing(OLTP)（500個左右）”、“ManualSetting（手動設(shè)置，自己輸一個數(shù)）”，我這里選“OnlineTransactionProcessing(OLTP)”，自己的服務(wù)器，應(yīng)該夠用了，按“Next”繼續(xù)>mysql圖文安裝教程15是否啟用TCP/IP連接，設(shè)定端口，如果不啟用，就只能在自己的機器上訪問mysql數(shù)據(jù)庫了，我這里啟用，把前面的勾打上，PortNumber：3306，在這個頁面上，您還可以選擇“啟用標準模式”（EnableStrictMode），這樣MySQL就不會允許細小的語法錯誤。如果您還是個新手，我建議您取消標準模式以減少麻煩。但熟悉MySQL以后，盡量使用標準模式，因為它可以降低有害數(shù)據(jù)進入數(shù)據(jù)庫的可能性。按“Next”繼續(xù)>mysql圖文安裝教程16西文編碼，第二個是多字節(jié)的通用utf8編碼，都不是我們通用的編碼，這里選擇第三個，然后在CharacterSet那里選擇或填入“gbk”，當然也可以用“gb2312”，區(qū)別就是gbk的字庫容量大，包括了gb2312的所有漢字，并且加上了繁體字、和其它亂七八糟的字——使用mysql的時候，在執(zhí)行數(shù)據(jù)操作命令之前運行一次“SETNAMESGBK;”（運行一次就行了，GBK可以替換為其它值，視這里的設(shè)置而定），就可以正常的使用漢字（或其它文字）了，否則不能正常顯示漢字。按“Next”繼續(xù)。(選utf8就好)>mysql圖文安裝教程17

選擇是否將mysql安裝為windows服務(wù)，還可以指定ServiceName（服務(wù)標識名稱），是否將mysql的bin目錄加入到WindowsPATH（加入后，就可以直接使用bin下的文件，而不用指出目錄名，比如連接，“mysql.exe-uusername-ppassword;”就可以了，不用指出mysql.exe的完整地址，很方便），我這里全部打上了勾，ServiceName不變。按“Next”繼續(xù)。選擇是否將mysql安裝為windows服務(wù)，還可以指定ServiceName（服務(wù)標識名稱），是否將mysql的bin目錄加入到WindowsPATH（加入后，就可以直接使用bin下的文件，而不用指出目錄名，比如連接，“mysql.exe-uusername-ppassword;”就可以了，不用指出mysql.exe的完整地址，很方便），我這里全部打上了勾，ServiceName不變。按“Next”繼續(xù)。>mysql圖文安裝教程18這一步詢問是否要修改默認root用戶（超級管理）的密碼（默認為空），“Newrootpassword”如果要修改，就在此填入新密碼（如果是重裝，并且之前已經(jīng)設(shè)置了密碼，在這里更改密碼可能會出錯，請留空，并將“ModifySecuritySettings”前面的勾去掉，安裝配置完成后另行修改密碼），“Confirm（再輸一遍）”內(nèi)再填一次，防止輸錯?！癊nablerootaccessfromremotemachines（是否允許root用戶在其它的機器上登陸，如果要安全，就不要勾上，如果要方便，就勾上它）”。最后“CreateAnAnonymousAccount（新建一個匿名用戶，匿名用戶可以連接數(shù)據(jù)庫，不能操作數(shù)據(jù)，包括查詢）”，一般就不用勾了，設(shè)置完畢，按“Next”繼續(xù)。>mysql圖文安裝教程19確認設(shè)置無誤，如果有誤，按“Back”返回檢查。按“Execute”使設(shè)置生效。>mysql圖文安裝教程20設(shè)置完畢，按“Finish”結(jié)束mysql的安裝與配置——這里有一個比較常見的錯誤，就是不能“Startservice”，一般出現(xiàn)在以前有安裝mysql的服務(wù)器上，解決的辦法，先保證以前安裝的mysql服務(wù)器徹底卸載掉了；不行的話，檢查是否按上面一步所說，之前的密碼是否有修改，照上面的操作；如果依然不行，將mysql安裝目錄下的data文件夾備份，然后刪除，在安裝完成后，將安裝生成的data文件夾刪除，備份的data文件夾移回來，再重啟mysql服務(wù)就可以了，這種情況下，可能需要將數(shù)據(jù)庫檢查一下，然后修復(fù)一次，防止數(shù)據(jù)出錯。Mysql的界面化工具SQLiteNavicat等等下載地址：/soft/7075.htmlnavicat注冊碼名和組織都是：iNViSiBLETEAMNavicatforMySQLEnterpriseEdition8注冊碼：NAVJ-W56S-3YUU-MVHVMysql引擎Mysql引擎介紹存儲引擎說白了就是如何存儲數(shù)據(jù)、如何為存儲的數(shù)據(jù)建立索引和如何更新、查詢數(shù)據(jù)等技術(shù)的實現(xiàn)方法。因為在關(guān)系數(shù)據(jù)庫中數(shù)據(jù)的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型（即存儲和操作此表的類型）。在Oracle和SQLServer等數(shù)據(jù)庫中只有一種存儲引擎，所有數(shù)據(jù)存儲管理機制都是一樣的。而MySql數(shù)據(jù)庫提供了多種存儲引擎。用戶可以根據(jù)不同的需求為數(shù)據(jù)表選擇不同的存儲引擎，用戶也可以根據(jù)自己的需要編寫自己的存儲引擎。

MySQL插件式存儲引擎的體系結(jié)構(gòu)存儲引擎種類2.2.1．InnoDB（默認的Mysql引擎）>概要說明：InnoDB表類型可以看作是對MyISAM（最原始）的進一步更新產(chǎn)品，它提供了事務(wù)、行級鎖機制和外鍵約束的功能。（適用于頻繁的進行更新、刪除操作）InnoDB給Mysql的表提供了事務(wù)、回滾、崩潰修復(fù)能力、多版本并發(fā)控制的事務(wù)安全、間隙鎖（可以有效的防止幻讀的出現(xiàn)）、支持輔助索引、聚簇索引、自適應(yīng)hash索引、支持熱備、行級鎖。還有InnoDB是Mysql上唯一一個提供了外鍵約束的引擎。InnoDB存儲引擎中，創(chuàng)建的表的表結(jié)構(gòu)是單獨存儲的并且存儲在.frm文件中。數(shù)據(jù)和索引存儲在一起的并且存儲在表空間中。但是默認情況下mysql會將數(shù)據(jù)庫的所有InnoDB表存儲在一個表空間中的。其實這種方式管理起來非常的不方便而且還不支持高級功能所以建議每個表存儲為一個表空間實現(xiàn)方式為：使用服務(wù)器變量innodb_file_per_table=1。缺點：InnoDB的備份恢復(fù)要麻煩一點，除非你使用了4.1以后版本提供的Mulit-tablespace支持，因為InnoDB和MyISAM不同，他的數(shù)據(jù)文件并不是獨立對應(yīng)于每張表的。而是使用的共享表空間，簡單的拷貝覆蓋方法對他不適用，必須在停掉MYSQL后對進行數(shù)據(jù)恢復(fù)。>設(shè)計原理：從物理意義上來講，InnoDB表由共享表空間、日志文件組（redo文件組）、表結(jié)構(gòu)定義文件組成。若將innodb_file_per_table設(shè)置為on，則系統(tǒng)將為每一個表單獨的生成一個table_name.ibd的文件，在此文件中，存儲與該表相關(guān)的數(shù)據(jù)、索引、表的內(nèi)部數(shù)據(jù)字典信息。表結(jié)構(gòu)文件則以.frm結(jié)尾，這與存儲引擎無關(guān)。InnoDB存儲引擎的管理是由引擎本身完成的，表空間是由分散的頁和段組成。區(qū)由64個連續(xù)的頁組成，每個頁大小為16K，即每個區(qū)大小為1MB，創(chuàng)建新表時，先有32頁大小的碎片頁存放數(shù)據(jù)，使用完后才是區(qū)的申請，（InnoDB最多每次申請4個區(qū)，保證數(shù)據(jù)的順序性能）頁類型有：數(shù)據(jù)頁、Undo頁、系統(tǒng)頁、事務(wù)數(shù)據(jù)頁、插入緩沖位圖頁、以及插入緩沖空閑列表頁。以下為InnoDB的表空間結(jié)構(gòu)圖：在InnoDB存儲引擎中，默認表空間文件是ibdata1，初始化為10M，且可以擴展，如下圖所示：修改表空間語句：Innodb_data_file_path=ibdata1:370M；ibdata2:50M:autoextend使用共享表空間存儲方式時，Innodb的所有數(shù)據(jù)保存在一個單獨的表空間里面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關(guān)數(shù)據(jù)。而在使用單獨表空間存儲方式時，每個表的數(shù)據(jù)以一個單獨的文件來存放，這個時候的單表限制，又變成文件系統(tǒng)的大小限制了。>共享表空間和獨立表空間共享表空間與獨占表空間可以通過參數(shù)innodb_file_per_table來轉(zhuǎn)換，若為1，則開啟獨占表空間，否則，開啟共享表存儲。在服務(wù)器資源有限,單表數(shù)據(jù)不是特別多的情況下,

獨立表空間明顯比共享方式效率更高.但是MySQL默認是共享表空間

。具體的共享表空間和獨立表空間優(yōu)缺點如下：共享表空間：優(yōu)點：可以放表空間分成多個文件存放到各個磁盤上（表空間文件大小不受表大小的限制，如一個表可以分布在不同步的文件上）。數(shù)據(jù)和文件放在一起方便管理。缺點：所有的數(shù)據(jù)和索引存放到一個文件中以為著將有一個很常大的文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，這樣對于一個表做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統(tǒng)計分析，日值系統(tǒng)這類應(yīng)用最不適合用共享表空間。獨立表空間：在配置文件（f）中設(shè)置：

innodb_file_per_table優(yōu)點：1．每個表都有自已獨立的表空間。2．每個表的數(shù)據(jù)和索引都會存在自已的表空間中。3．可以實現(xiàn)單表在不同的數(shù)據(jù)庫中移動。4．空間可以回收（除drop

table操作處，表空不能自已回收）a)Drop

table操作自動回收表空間，如果對于統(tǒng)計分析或是日值表，刪除大量數(shù)據(jù)后可以通過:alter

table

TableName

engine=innodb;回縮不用的空間。b)對于使innodb-plugin的Innodb使用truncate

table也會使空間收縮。c)對于使用獨立表空間的表，不管怎么刪除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。缺點：單表增加過大對于啟用了innodb_file_per_table

的參數(shù)選項之后，在每個表對應(yīng)的.idb文件內(nèi)只是存放了數(shù)據(jù)、索引和插入緩沖，而撤銷（undo）信息，系統(tǒng)事務(wù)信息，二次寫緩沖等還是存放在了原來的共享表空間內(nèi)。2.2.2．MyISAM存儲引擎MyISAM存儲引擎是Mysql中常見的存儲引擎，MyISAM存儲引擎是基于ISAM存儲引擎發(fā)展起來的。MyISAM支持全文索引、壓縮存放、空間索引（空間函數(shù)）、表級鎖、延遲更新索引鍵。但是MyISAM不支持事務(wù)、行級鎖、更無法忍受的是崩潰后不能保證完全恢復(fù)（只能手動修復(fù)）。MyISAM存儲引擎的表存儲成3個文件。文件的名字和表的名字相同。擴展名包含frm、MYD、MYI。其中frm為擴展名的文件存儲表的結(jié)構(gòu)；MYD為擴展名的文件存儲數(shù)據(jù)，其是MYData的縮寫；MYI為擴展名的文件存儲索引，其為MYIndex的縮寫。MyISAM存儲引擎的插入數(shù)據(jù)很快，空間和內(nèi)存使用比較低。如果表主要是用于插入新記錄和讀出記錄，那么選擇MyISAM存儲引擎能夠?qū)崿F(xiàn)處理的高效率。如果應(yīng)用的完整性、并發(fā)性要求很低，也可以選擇MyISAM存儲引擎。三種MyIsAm引擎：靜態(tài)MyISAM：如果數(shù)據(jù)表中的各數(shù)據(jù)列的長度都是預(yù)先固定好的，服務(wù)器將自動選擇這種表類型。因為數(shù)據(jù)表中每一條記錄所占用的空間都是一樣的，所以這種表存取和更新的效率非常高。當數(shù)據(jù)受損時，恢復(fù)工作也比較容易做。動態(tài)MyISAM：如果數(shù)據(jù)表中出現(xiàn)varchar、xxxtext或xxxBLOB字段時，服務(wù)器將自動選擇這種表類型。相對于靜態(tài)MyISAM，這種表存儲空間比較小，但由于每條記錄的長度不一，所以多次修改數(shù)據(jù)后，數(shù)據(jù)表中的數(shù)據(jù)就可能離散的存儲在內(nèi)存中，進而導(dǎo)致執(zhí)行效率下降。同時，內(nèi)存中也可能會出現(xiàn)很多碎片。因此，這種類型的表要經(jīng)常用optimizetable命令或優(yōu)化工具來進行碎片整理。壓縮MyISAM：以上說到的兩種類型的表都可以用myisamchk工具壓縮。這種類型的表進一步減小了占用的存儲，但是這種表壓縮之后不能再被修改。另外，因為是壓縮數(shù)據(jù)，所以這種表在讀取的時候要先時行解壓縮。但是，不管是何種MyISAM表，目前它都不支持事務(wù)，行級鎖和外鍵約束的功能。2.23．Memory存儲引擎這種類型的數(shù)據(jù)表只存在于內(nèi)存中。它使用散列索引，所以數(shù)據(jù)的存取速度非?？?。因為是存在于內(nèi)存中，所以這種類型常應(yīng)用于臨時表中。MEMORY存儲引擎是Mysql中的一類特殊的存儲引擎。其使用存儲在內(nèi)存中的內(nèi)存來創(chuàng)建表，而且所有數(shù)據(jù)保存在內(nèi)存中。數(shù)據(jù)安全性很低，但是查找和插入速度很快。如果內(nèi)存出現(xiàn)異常就會影響到數(shù)據(jù)的完整性，如果重啟或關(guān)機，表中的所有數(shù)據(jù)就會丟失，因此基于MEMORY存儲引擎的表的生命周期很短，一般都是一次性的。適用于某些特殊場景像查找和映射，緩存周期性的聚合數(shù)據(jù)等等。2.24．Archive存儲引擎這種類型只支持select和insert語句，而且不支持索引。常應(yīng)用于日志記錄和聚合分析方面。ARCHIVE，見名之意可看出是歸檔，所以歸檔之后很多的高級功能就不再支持了僅支持插入（insert）和查詢（select）兩種功能，

ARCHIVE存儲引擎之前還不支持索引（在Mysql5.5以后開始支持索引了），但是它擁有很好的壓縮機制。通常用于做倉庫使用。ARCHIVE存儲引擎適用于存儲日志信息或其他按時間序列實現(xiàn)的數(shù)據(jù)采集類的應(yīng)用場景中。InnoDB與Myisam對比InnoDB與Myisam的六大區(qū)別MyISAMInnoDB構(gòu)成上的區(qū)別：每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。.frm文件存儲表定義。數(shù)據(jù)文件的擴展名為.MYD(MYData)。索引文件的擴展名是.MYI(MYIndex)。基于磁盤的資源是InnoDB表空間數(shù)據(jù)文件和它的日志文件，InnoDB表的大小只受限于操作系統(tǒng)文件的大小，一般為2GB事務(wù)處理上方面:MyISAM類型的表強調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持InnoDB提供事務(wù)支持事務(wù)，外部鍵等高級數(shù)據(jù)庫功能SELECT\o"UPDATE"UPDATE,INSERT，Delete操作如果執(zhí)行大量的SELECT，MyISAM是更好的選擇1.如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，應(yīng)該使用InnoDB表2.DELETEFROMtable時，InnoDB不會重新建立表，而是一行一行的刪除。3.LOADTABLEFROMMASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對于使用的額外的InnoDB特性（例如外鍵）的表不適用對AUTO_INCREMENT的操作每表一個AUTO_INCREMEN列的內(nèi)部處理。MyISAM為INSERT和UPDATE操作自動更新這一列。這使得AUTO_INCREMENT列更快（至少10%）。在序列頂?shù)闹当粍h除之后就不能再利用。(當AUTO_INCREMENT列被定義為多列索引的最后一列，可以出現(xiàn)重使用從序列頂部刪除的值的情況）。AUTO_INCREMENT值可用ALTERTABLE或myisamch來重置對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引更好和更快的auto_increment處理如果你為一個表指定AUTO_INCREMENT列，在數(shù)據(jù)詞典里的InnoDB表句柄包含一個名為自動增長計數(shù)器的計數(shù)器，它被用在為該列賦新值。自動增長計數(shù)器僅被存儲在主內(nèi)存中，而不是存在磁盤上關(guān)于該計算器的算法實現(xiàn)，請參考AUTO_INCREMENT列在InnoDB里如何工作表的具體行數(shù)selectcount(*)fromtable,MyISAM只要簡單的讀出保存好的行數(shù)，注意的是，當count(*)語句包含where條件時，兩種表的操作是一樣的InnoDB中不保存表的具體行數(shù)，也就是說，執(zhí)行selectcount(*)fromtable時，InnoDB要掃描一遍整個表來計算有多少行鎖表鎖提供行鎖(lockingonrowlevel)，提供與\o"Oracle"Oracle

類型一致的不加鎖讀取(non-lockingreadin

SELECTs)，另外，InnoDB表的行鎖也不是絕對的，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表，例如updatetablesetnum=1where

\o"name"name

like“%aaa%”常用的一些語句查看數(shù)據(jù)庫可以支持的存儲引擎查看表的結(jié)構(gòu)等信息的若干命令Desc[ribe]tablename;

//查看數(shù)據(jù)表的結(jié)構(gòu)Showcreatetabletablename;

//顯示表的創(chuàng)建語句Showtablestatuslike‘tablename’//顯示表的當前狀態(tài)值創(chuàng)建數(shù)據(jù)庫表時設(shè)置存儲存儲引擎CreatetabletableName(columnName(列名1)

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

attri(屬性設(shè)置),columnName(列名2)

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

attri(屬性設(shè)置),……..)engine=engineName修改存儲引擎假如，若需要將表user的存儲引擎修改為archive類型，則可使用命令altertableuserengine=archiveMysql的基本對象表3.1.1.數(shù)據(jù)表數(shù)據(jù)列基本類型（紅色代表常用）//表的基本類型M指出最大的顯示尺寸。最大的合法的顯示尺寸是255。D適用于浮點類型并且指出跟隨在十進制小數(shù)點后的數(shù)碼的數(shù)量。方括號(“[”和“]”)指出可選的類型修飾符的部分。注意，如果你指定一個了為ZEROFILL，MySQL將為該列自動地增加UNSIGNED屬性。TINYINT[(M)][UNSIGNED][ZEROFILL],紅色代表常用。一個很小的整數(shù)。有符號的范圍是-128到127，無符號的范圍是0到255。SMALLINT[(M)][UNSIGNED][ZEROFILL]一個小整數(shù)。有符號的范圍是-32768到32767，無符號的范圍是0到65535。MEDIUMINT[(M)][UNSIGNED][ZEROFILL]一個中等大小整數(shù)。有符號的范圍是-8388608到8388607，無符號的范圍是0到16777215。INT[(M)][UNSIGNED][ZEROFILL]一個正常大小整數(shù)。有符號的范圍是-2147483648到2147483647，無符號的范圍是0到4294967295。INTEGER[(M)][UNSIGNED][ZEROFILL]這是INT的一個同義詞。BIGINT[(M)][UNSIGNED][ZEROFILL]一個大整數(shù)。有符號的范圍是-9223372036854775808到9223372036854775807，無符號的范圍是0到FLOAT[(M,D)][ZEROFILL]一個小(單精密)浮點數(shù)字。不能無符號。允許的值是-3.402823466E+38到-1.175494351E-38，0和1.175494351E-38到3.402823466E+38。M是顯示寬度而D是小數(shù)的位數(shù)。沒有參數(shù)的FLOAT或有<24的一個參數(shù)表示一個單精密浮點數(shù)字。DOUBLE[(M,D)][ZEROFILL]一個正常大小(雙精密)浮點數(shù)字。不能無符號。允許的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到1.7976931348623157E+308。M是顯示寬度而D是小數(shù)位數(shù)。沒有一個參數(shù)的DOUBLE或FLOAT(X)(25<=X<=53)代表一個雙精密浮點數(shù)字。DECIMAL[(M[,D])][ZEROFILL]一個未壓縮(unpack)的浮點數(shù)字。不能無符號。行為如同一個CHAR列：“未壓縮”意味著數(shù)字作為一個字符串被存儲，值的每一位使用一個字符。小數(shù)點，并且對于負數(shù)，“-”符號不在M中計算。如果D是0，值將沒有小數(shù)點或小數(shù)部分。DECIMAL值的最大范圍與DOUBLE相同，但是對一個給定的DECIMAL列，實際的范圍可以通過M和D的選擇被限制。如果D被省略，它被設(shè)置為0。如果M被省掉，它被設(shè)置為10。注意，在MySQL3.22里，M參數(shù)包括符號和小數(shù)點。NUMERIC(M,D)[ZEROFILL]這是DECIMAL的一個同義詞。DATE一個日期。支持的范圍是'1000-01-01'到'9999-12-31'。MySQL以'YYYY-MM-DD'格式來顯示DATE值，但是允許你使用字符串或數(shù)字把值賦給DATE列。DATETIME一個日期和時間組合。支持的范圍是'1000-01-0100:00:00'到'9999-12-3123:59:59'。MySQL以'YYYY-MM-DDHH:MM:SS'格式來顯示DATETIME值，但是允許你使用字符串或數(shù)字把值賦給DATETIME的列。TIMESTAMP[(M)]一個時間戳記。范圍是'1970-01-0100:00:00'到2037年的某時。MySQL以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD格式來顯示TIMESTAMP值，取決于是否M是14(或省略)、12、8或6，但是允許你使用字符串或數(shù)字把值賦給TIMESTAMP列。一個TIMESTAMP列對于記錄一個INSERT或UPDATE操作的日期和時間是有用的，因為如果你不自己給它賦值，它自動地被設(shè)置為最近操作的日期和時間。你以可以通過賦給它一個NULL值設(shè)置它為當前的日期和時間。TIME一個時間。范圍是'-838:59:59'到'838:59:59'。MySQL以'HH:MM:SS'格式來顯示TIME值，但是允許你使用字符串或數(shù)字把值賦給TIME列。YEAR[(2|4)]一個2或4位數(shù)字格式的年(缺省是4位)。允許的值是1901到2155，和0000(4位年格式)，如果你使用2位，1970-2069(70-69)。MySQL以YYYY格式來顯示YEAR值，但是允許你把使用字符串或數(shù)字值賦給YEAR列。(YEAR類型在MySQL3.22中是新類型。)CHAR(M)[BINARY]一個定長字符串，當存儲時，總是是用空格填滿右邊到指定的長度。M的范圍是1～255個字符。當值被檢索時，空格尾部被刪除。CHAR值根據(jù)缺省字符集以大小寫不區(qū)分的方式排序和比較，除非給出BINARY關(guān)鍵詞。NATIONALCHAR(短形式NCHAR)是ANSISQL的方式來定義CHAR列應(yīng)該使用缺省字符集。這是MySQL的缺省。CHAR是CHARACTER的一個縮寫。[NATIONAL]VARCHAR(M)[BINARY]一個變長字符串。注意：當值被存儲時，尾部的空格被刪除(這不同于ANSISQL規(guī)范)。M的范圍是1～255個字符。VARCHAR值根據(jù)缺省字符集以大小寫不區(qū)分的方式排序和比較，除非給出BINARY關(guān)鍵詞值。VARCHAR是CHARACTERVARYING一個縮寫。TINYBLOBTINYTEXT一個BLOB或TEXT列，最大長度為255(2^8-1)個字符。BLOBTEXT一個BLOB或TEXT列，最大長度為65535(2^16-1)個字符。MEDIUMBLOBMEDIUMTEXT一個BLOB或TEXT列，最大長度為16777215(2^24-1)個字符。LONGBLOBLONGTEXT一個BLOB或TEXT列，最大長度為4294967295(2^32-1)個字符。ENUM('value1','value2',...)枚舉。一個僅有一個值的字符串對象，這個值式選自與值列表'value1'、'value2',...,或NULL。一個ENUM最多能有65535不同的值。SET('value1','value2',...)一個集合。能有零個或多個值的一個字符串對象，其中每一個必須從值列表'value1','value2',...選出。一個SET最多能有64個成員。3.1.2.操作數(shù)據(jù)表語句>usedatabase數(shù)據(jù)庫名;//創(chuàng)建表之前先要選擇數(shù)據(jù)庫，如果沒有數(shù)據(jù)庫，則創(chuàng)建數(shù)據(jù)庫createdatabase數(shù)據(jù)庫名;>createtable商品表(//創(chuàng)建數(shù)據(jù)表PIDintAUTO_INCREMENT,//設(shè)置id自增namevarchar(20),primarykey(PID));>droptable數(shù)據(jù)庫名>altertable舊表名renameto新表名;//修改表名>altertable<表名>modify<字段名><數(shù)據(jù)類型>//修改表字段類型>altertable<表名>change<舊字段名><新字段名><新數(shù)據(jù)類型>//改字段名>altertable<表名>add<字段名><數(shù)據(jù)類型>[約束條件][first|after已存在字段名];//添加一個數(shù)據(jù)表字段>altertable<表名>modify<字段1><數(shù)據(jù)類型>first|after<字段類型2>//改變現(xiàn)有字段的位置3.1.3.一般原則(1)越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來更快。

(2)簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因為字符串的比較更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型，而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址。

(3)盡量避免NULL：應(yīng)該指定列為NOTNULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值。索引索引就是加快檢索表中數(shù)據(jù)的方法。數(shù)據(jù)庫的索引類似于書籍的索引。在書籍中，索引允許用戶不必翻閱完整個書就能迅速地找到所需要的信息。在數(shù)據(jù)庫中，索引也允許數(shù)據(jù)庫程序迅速地找到表中的數(shù)據(jù)，而不必掃描整個數(shù)據(jù)庫。先掃描索引表的內(nèi)容，如果有索引就可以迅速定位到該位置。innodb存儲引擎支持兩種常見的索引：B+樹索引和哈希索引。innodb支持哈希索引是自適應(yīng)的，innodb會根據(jù)表的使用情況自動生成哈希索引。B+樹索引就是傳統(tǒng)意義上的索引，是關(guān)系型數(shù)據(jù)庫中最常用最有效的索引。B+樹是從最早的平衡二叉樹演變而來，但是B+樹不是一個二叉樹。B+中的B不代表二叉(Binary),而是代表平衡(Balance)。本章主要講B+樹索引。3.2.1.索引原理數(shù)據(jù)庫中B+樹索引分為聚集索引（clusteredindex）和非聚集索引（secondaryindex）.這兩種索引的共同點是內(nèi)部都是B+樹，高度都是平衡的，葉節(jié)點存放著所有數(shù)據(jù)。不同點是葉節(jié)點是否存放著一整行數(shù)據(jù)。聚簇索引（創(chuàng)建主鍵的時候，自動就創(chuàng)建了主鍵的聚集索引。）聚簇索引保證關(guān)鍵字的值相近的元組存儲的物理位置也相同（所以字符串類型不宜建立聚簇索引，特別是隨機字符串，會使得系統(tǒng)進行大量的移動操作），且一個表只能有一個聚簇索引。因為由存儲引擎實現(xiàn)索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。聚簇索引的結(jié)構(gòu)大致如下聚簇索引也稱為聚集索引，聚類索引，簇集索引，聚簇索引確定表中數(shù)據(jù)的物理順序。聚簇索引類似于電話簿，后者按姓氏排列數(shù)據(jù)。由于聚簇索引規(guī)定數(shù)據(jù)在表中的物理存儲順序，因此一個表只能包含一個聚簇索引。但該索引可以包含多個列（組合索引），就像電話簿按姓氏和名字進行組織一樣。漢語字典也是聚簇索引的典型應(yīng)用，在漢語字典里，索引項是字母+聲調(diào)，字典正文也是按照先字母再聲調(diào)的順序排列。聚簇索引對于那些經(jīng)常要搜索范圍值的列特別有效。使用聚簇索引找到包含第一個值的行后，便可以確保包含后續(xù)索引值的行在物理相鄰。例如，如果應(yīng)用程序執(zhí)行的一個查詢經(jīng)常檢索某一日期范圍內(nèi)的記錄，則使用聚集索引可以迅速找到包含開始日期的行，然后檢索表中所有相鄰的行，直到到達結(jié)束日期。這樣有助于提高此類查詢的性能。同樣，如果對從表中檢索的數(shù)據(jù)進行排序時經(jīng)常要用到某一列，則可以將該表在該列上聚簇（物理排序），避免每次查詢該列時都進行排序，從而節(jié)省成本。非聚簇索引（普通索引）每個表只能有一個聚簇索引，因為一個表中的記錄只能以一種物理順序存放。但是，一個表可以有不止一個非聚簇索引。實際上，對每個表你最多可以建立249個非聚簇索引。非聚簇索引需要大量的硬盤空間和內(nèi)存。另外，雖然非聚簇索引可以提高從表中取數(shù)據(jù)的速度，它也會降低向表中插入和更新數(shù)據(jù)的速度。每當你改變了一個建立了非聚簇索引的表中的數(shù)據(jù)時，必須同時更新索引。因此你對一個表建立非聚簇索引時要慎重考慮。如果你預(yù)計一個表需要頻繁地更新數(shù)據(jù)，那么不要對它建立太多非聚簇索引。另外，如果硬盤和內(nèi)存空間有限，也應(yīng)該限制使用非聚簇索引的數(shù)量。非聚簇索引，葉級頁指向表中的記錄，記錄的物理順序與邏輯順序沒有必然的聯(lián)系。非聚簇索引則更像書的標準索引表，索引表中的順序通常與實際的頁碼順序是不一致的。非聚簇索引查找數(shù)據(jù)示意圖。B+樹算法B+樹是為磁盤及其他存儲輔助設(shè)備而設(shè)計一種平衡查找樹（不是二叉樹）。B+樹中，所有記錄的節(jié)點按大小順序存放在同一層的葉節(jié)點中，各葉節(jié)點用指針進行連接。下面演示一個B+數(shù)結(jié)構(gòu)，高度為2，每頁可放4條記錄，扇出(fanout)為5。從下圖1可以看出，所有記錄都在頁節(jié)點中，并且為順序存放，我們從最左邊的葉節(jié)點開始遍歷，可以得到所有鍵值的順序排序：5、10、15、20、25、30、50、55、60、65、75、80、85、90.圖1

高度為2的B+樹（1）B+樹的插入操作B+樹的插入必須保證插入后葉節(jié)點的記錄依然排序。同時要考慮插入B+樹的三種情況，每種情況都可能導(dǎo)致不同的插入算法。如下表所示：我們實例分析B+樹的插入，在圖1的B+樹中，我們需要插入28這個值。因為LeafPage和Indexpage都沒有滿，我們直接將記錄插入葉節(jié)點就可以了。如下圖2所示：圖2

插入鍵值28下面我們再插入70這個值，這時LeafPage已經(jīng)滿了，但是IndexPage還沒有滿，符合上面的第二種情況。這時插入LeafPage的情況為50、55、60、65、70.我們根據(jù)中間的值60拆分葉節(jié)點，可得到下圖3所示（雙項鏈表指針依然存在，沒有畫出）：圖3插入鍵值70最后我們再插入95，這個LeafPage和IndexPage都滿了，符合上面第三種情況。需要做2次拆分，如下圖4所示：圖4插入鍵值95可以看到，不管怎么變化，B+樹總會保持平衡。但是為了保持平衡，對于新插入的鍵值可能需要做大量的拆分頁操作。B+樹主要用于磁盤，拆分意味著磁盤的操作，應(yīng)該在可能的情況下盡量減少頁的拆分。因此，B+樹提供了旋轉(zhuǎn)功能。旋轉(zhuǎn)發(fā)生在LeafPage已經(jīng)滿了，但是左右兄弟節(jié)點沒有滿的情況下。這時B+樹并不是急著做頁的拆分，而是旋轉(zhuǎn)。旋轉(zhuǎn)結(jié)果如圖5所示，可以看到旋轉(zhuǎn)操作使B+樹減少了一次頁的拆分操作，高度仍然為2.圖5B+樹的旋轉(zhuǎn)操作(2)B+樹的刪除操作B+樹使用填充因子來控制數(shù)的刪除變化。填充因子可以設(shè)置的最小值為50%。B+樹的刪除操作同樣保證刪除后葉節(jié)點的記錄依然排序。根據(jù)填充因子的變化，B+樹刪除依然需要考慮三種情況，如下表所示：根據(jù)圖4的B+樹，我們進行刪除操作，首先刪除鍵值為70的這條記錄，該記錄符合上表第一種情況，刪除后如下圖6所示：圖6刪除鍵值70接著我們刪除鍵值為25的記錄，這也是屬于上表第一種情況，不同的是該值還是indexpage中的值。因此在刪除LeafPage中的25后，還需要將25的右兄弟節(jié)點28更新到IndexPage中，如下圖7所示（圖中有兩個筆誤，紅色為修正值）：圖7刪除鍵值28最后我們刪除鍵值為60的記錄。刪除Leafpage鍵值為60的記錄后，其填充因子小于50%。需要做合并操作。同樣在刪除Indexpage中相關(guān)記錄后需要做IndexPage的合并操作。3.2.2.創(chuàng)建和刪除索引（1）使用ALTERTABLE語句創(chuàng)建索引。語法如下：altertabletable_nameaddindexindex_name(column_list);altertabletable_nameaddunique(column_list);altertabletable_nameaddprimarykey(column_list);其中包括普通索引、UNIQUE索引和PRIMARYKEY索引3種創(chuàng)建索引的格式，table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。索引名index_name可選，缺省時，MySQL將根據(jù)第一個索引列賦一個名稱。另外，ALTERTABLE允許在單個語句中更改多個表，因此可以同時創(chuàng)建多個索引。創(chuàng)建索引的示例如下：mysql>usetpscDatabasechangedmysql>altertabletpscaddindexshili(tpmc);（2）使用CREATEINDEX語句對表增加索引。能夠增加普通索引和UNIQUE索引兩種。其格式如下：createindexindex_nameontable_name(column_list);createuniqueindexindex_nameontable_name(column_list);說明：table_name、index_name和column_list具有與ALTERTABLE語句中相同的含義，索引名不可選。另外，不能用CREATEINDEX語句創(chuàng)建PRIMARYKEY索引。（3）刪除索引。刪除索引可以使用ALTERTABLE或DROPINDEX語句來實現(xiàn)。DROPINDEX可以在ALTERTABLE內(nèi)部作為一條語句處理，其格式如下：dropindexindex_nameontable_name;altertabletable_namedropindexindex_name;altertabletable_namedropprimarykey;其中，在前面的兩條語句中，都刪除了table_name中的索引index_name。而在最后一條語句中，只在刪除PRIMARYKEY索引中使用，因為一個表只可能有一個PRIMARYKEY索引，因此不需要指定索引名。如果沒有創(chuàng)建PRIMARYKEY索引，但表具有一個或多個UNIQUE索引，則MySQL將刪除第一個UNIQUE索引。如果從表中刪除某列，則索引會受影響。對于多列組合的索引，如果刪除其中的某列，則該列也會從索引中刪除。如果刪除組成索引的所有列，則整個索引將被刪除。刪除索引的操作，如下面的代碼：mysql>dropindexshiliontpsc;視圖視圖是一個虛擬表，其內(nèi)容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫中以存儲的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來自由定義視圖的查詢所引用的表，并且在引用視圖時動態(tài)生成。對其中所引用的基礎(chǔ)表來說，視圖的作用類似于篩選。定義視圖的篩選可以來自當前或其它數(shù)據(jù)庫的一個或多個表，或者其它視圖。通過視圖進行查詢沒有任何限制，通過它們進行數(shù)據(jù)修改時的限制也很少。用戶只能看到視圖給他看到的內(nèi)容，所以更安全。好處：1、視圖能簡化用戶操作視圖機制使用戶可以將注意力集中在所關(guān)心地數(shù)據(jù)上。如果這些數(shù)據(jù)不是直接來自基本表，則可以通過定義視圖，使數(shù)據(jù)庫看起來結(jié)構(gòu)簡單、清晰，并且可以簡化用戶的的數(shù)據(jù)查詢操作。例如，那些定義了若干張表連接的視圖，就將表與表之間的連接操作對用戶隱藏起來了。換句話說，用戶所作的只是對一個虛表的簡單查詢，而這個虛表是怎樣得來的，用戶無需了解。2、視圖使用戶能以多種角度看待同一數(shù)據(jù)

視圖機制能使不同的用戶以不同的方式看待同一數(shù)據(jù)，當許多不同種類的用戶共享同一個數(shù)據(jù)庫時，這種靈活性是非常必要的。3、視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性數(shù)據(jù)的物理獨立性是指用戶的應(yīng)用程序不依賴于數(shù)據(jù)庫的物理結(jié)構(gòu)。數(shù)據(jù)的邏輯獨立性是指當數(shù)據(jù)庫重構(gòu)造時，如增加新的關(guān)系或?qū)υ械年P(guān)系增加新的字段，用戶的應(yīng)用程序不會受影響。層次數(shù)據(jù)庫和網(wǎng)狀數(shù)據(jù)庫一般能較好地支持數(shù)據(jù)的物理獨立性，而對于邏輯獨立性則不能完全的支持。3.3.1.視圖語句>CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER={user|CURRENT_USER}][SQLSECURITY{DEFINER|INVOKER}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]//創(chuàng)建視圖語法例子：創(chuàng)建一個員工表并插入數(shù)據(jù)CREATETABLEt_employee(IDINTPRIMARYKEYAUTO_INCREMENT,NAMECHAR(30)NOTNULL,SEXCHAR(2)NOTNULL,AGEINTNOTNULL,DEPARTMENTCHAR(10)NOTNULL,SALARYINTNOTNULL,HOMECHAR(30),MARRYCHAR(2)NOTNULLDEFAULT'否',HOBBYCHAR(30));INSERTINTOlearning.t_employee(ID,NAME,SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY)VALUES(NULL,'小紅','女',20,'人事部','4000','廣東','否','網(wǎng)球');INSERTINTOlearning.t_employee(ID,NAME,SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY)VALUES(NULL,'明日','女',21,'人事部','9000','北京','否','網(wǎng)球');INSERTINTOlearning.t_employee(ID,NAME,SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY)VALUES(NULL,'天天','男',22,'研發(fā)部','8000','上海','否','音樂');INSERTINTOlearning.t_employee(ID,NAME,SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY)VALUES(NULL,'大大','女',23,'研發(fā)部','9000','重慶','否','無');INSERTINTOlearning.t_employee(ID,NAME,SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY)VALUES(NULL,'王下','女',24,'研發(fā)部','9000','四川','是','足球');INSERTINTOlearning.t_employee(ID,NAME,SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY)VALUES(NULL,'無名','男',25,'銷售部','6000','福建','否','游戲');INSERTINTOlearning.t_employee(ID,NAME,SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY)VALUES(NULL,'不知道','女',26,'銷售部','5000','山西','否','籃球');(2)創(chuàng)建員工信息表并插入數(shù)據(jù)createTABLEt_employee_detail(IDINTPRIMARYKEY,POSCHAR(10)NOTNULL,EXPERENCECHAR(10)NOTNULL,CONSTRAINT`FK_ID`FOREIGNKEY(ID)REFERENCESt_employee(ID))INSERTINTOt_employee_detail(ID,POS,EXPERENCE)VALUES(1,'人事管理','工作二年');INSERTINTOt_employee_detail(ID,POS,EXPERENCE)VALUES(2,'人事招聘','工作二年');INSERTINTOt_employee_detail(ID,POS,EXPERENCE)VALUES(3,'初級工程師','工作一年');INSERTINTOt_employee_detail(ID,POS,EXPERENCE)VALUES(4,'中級工程師','工作二年');INSERTINTOt_employee_detail(ID,POS,EXPERENCE)VALUES(5,'高級工程師','工作三年');INSERTINTOt_employee_detail(ID,POS,EXPERENCE)VALUES(6,'銷售代表','工作二年');INSERTINTOt_employee_detail(ID,POS,EXPERENCE)VALUES(7,'銷售員','工作一年');(3)在創(chuàng)建視圖前應(yīng)先看看是否有權(quán)限SELECTSELECT_priv,create_view_privfrommysql.userWHEREuser='root'(4)創(chuàng)建視圖CREATEVIEWV_VIEW2(ID,NAME,SEX,AGE,DEPARTMENT,POS,EXPERENCE)ASSELECTa.ID,a.NAME,a.SEX,a.AGE,a.DEPARTMENT,b.POS,b.EXPERENCEFROMlearning.t_employeea,learning.t_employee_detailbWHEREa.ID=b.ID;SELECT*FROMV_VIEW2(5)查看視圖DESCRIBEV_VIEW2(5)修改視圖ALTERVIEWV_VIEW1(ID,NAME)ASSELECTID,NAMEFROMlearning.t_employee;SELECT*FROMlearning.v_view1不可更新的視圖：（如果值和表數(shù)據(jù)是一一對應(yīng)的，那么可以直接更新視圖，真實表的數(shù)據(jù)也會有相應(yīng)改變）

某些視圖是可更新的。也就是說，可以在諸如UPDATE、DELETE或INSERT等語句中使用它們，以更新基表的內(nèi)容。對于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對一的關(guān)系。還有一些特定的其他結(jié)構(gòu)，這類結(jié)構(gòu)會使得視圖不可更新。比如：·聚合函數(shù)（SUM(),MIN(),MAX(),COUNT()等）。

·DISTINCT

·GROUPBY

·HAVING

·UNION或UNIONALL

·位于選擇列表中的子查詢

·Join

·FROM子句中的不可更新視圖

·WHERE子句中的子查詢，引用FROM子句中的表。

·僅引用文字值（在該情況下，沒有要更新的基本表）。

·ALGORITHM=TEMPTABLE（使用臨時表總會使視圖成為不可更新的）。創(chuàng)建存儲過程／函數(shù)存儲過程存儲過程（StoredProcedure）是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL語句集，存儲在數(shù)據(jù)庫中，經(jīng)過第一次編譯后再次調(diào)用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。優(yōu)點：1.由于數(shù)據(jù)庫執(zhí)行動作時，是先編譯后執(zhí)行的。然而存儲過程是一個編譯過的代碼塊，所以執(zhí)行效率要比T-SQL語句高。

2.一個存儲過程在程序在網(wǎng)絡(luò)中交互時可以替代大堆的T-SQL語句，所以也能降低網(wǎng)絡(luò)的通信量，提高通信速率。

3.通過存儲過程能夠使沒有權(quán)限的用戶在控制之下間接地存取數(shù)據(jù)庫，從而確保數(shù)據(jù)的安全。4.1.1.創(chuàng)建語句CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]調(diào)用存儲過程EXECUTEProcedure_name''--存儲過程如果有參數(shù)，后面加參數(shù)格式為：@參數(shù)名=value，也可直接為參數(shù)值value刪除存儲過程dropprocedureprocedure_name--在存儲過程中能調(diào)用另外一個存儲過程，而不能刪除另外一個存儲過程1.procedure_name

：存儲過程的名稱，在前面加#為局部臨時存儲過程，加##為全局臨時存儲過程。

2.number：可選的整數(shù)，用來對同名的過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。例如，名為orders的應(yīng)用程序使用的過程可以命名為orderproc;1、orderproc;2等。DROPPROCEDUREorderproc語句將除去整個組。如果名稱中包含定界標識符，則數(shù)字不應(yīng)包含在標識符中，只應(yīng)在procedure_name前后使用適當?shù)亩ń绶?/p>

3.@parameter：存儲過程的參數(shù)。可以有一個或多個。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認值）。存儲過程最多可以有2.100個參數(shù)。

使用@符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。有關(guān)更多信息，請參見EXECUTE。

4.data_type：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括text、ntext和image）均可以用作存儲過程的參數(shù)。不過，cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)。如果指定的數(shù)據(jù)類型為cursor，也必須同時指定VARYING和OUTPUT關(guān)鍵字。有關(guān)SQLServer提供的數(shù)據(jù)類型及其語法的更多信息，請參見數(shù)據(jù)類型。

說明對于可以是cursor數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。

5.VARYING：

指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標參數(shù)。

6.default：

參數(shù)的默認值。如果定義了默認值，不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或NULL。如果過程將對該參數(shù)使用LIKE關(guān)鍵字，那么默認值中可以包含通配符（%、_、[]和[^]）。7.OUTPUT

：表明參數(shù)是返回參數(shù)。該選項的值可以返回給EXEC[UTE]。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。Text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以是游標占位符。

8.RECOMPILE:

表明SQLServer不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用RECOMPILE選項。9.ENCRYPTION:

表示SQLServer加密syscomments表中包含CREATEPROCEDURE語句文本的條目。使用ENCRYPTION可防止將過程作為SQLServer復(fù)制的一部分發(fā)布。說明在升級過程中，SQLServer利用存儲在syscomments中的加密注釋來重新創(chuàng)建加密過程。

10.FORREPLICATION

:指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。.使用FORREPLICATION選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復(fù)制過程中執(zhí)行。本選項不能和WITHRECOMPILE選項一起使用。

11.AS

:指定過程要執(zhí)行的操作。12.sql_statement

:過程中要包含的任意數(shù)目和類型的Transact-SQL語句。但有一些限制。4.1.2.存儲過程例子只返回單一記錄集的存儲過程createProcedureGetUserAccountbeginselect*fromuseraccount;end調(diào)用：callGetUserAccount();帶有out參數(shù)的存儲過程

CREATEPROCEDUREdemo_out_parameter(OUTp_outint)BEGINSELECTp_out;SETp_out=2;SELECTp_out;END;調(diào)用：SET@p_out=1;CALLdemo_out_parameter(@p_out);帶有in參數(shù)的存儲過程

CREATEPROCEDUREdemo_inout_parameter(INOUTp_inoutint)BEGINSELECTp_inout;SETp_inout=2;SELECTp_inout;END;調(diào)用：SET@p_inout=1;CALLdemo_inout_parameter(@p_inout);定義變量以及游標使用的存儲過程

BEGIN/*定義變量*/declaretmp0VARCHAR(1000);declaretmp1VARCHAR(1000);declaredoneintdefault-1;--用于控制循環(huán)是否結(jié)束/*聲明游標*/declaremyCursorcursorforselectUserName,PassWordfromuseraccount;/*當游標到達尾部時，mysql自動設(shè)置done=1*/declarecontinuehandlerfornotfoundsetdone=1;/*打開游標*/openmyCursor;/*循環(huán)開始*/myLoop:LOOP/*移動游標并賦值*/fetchmyCursorintotmp0,tmp1;--游標到達尾部,退出循環(huán)ifdone=1thenleavemyLoop;endif;/*dosomething*/--循環(huán)輸出信息selecttmp0,tmp1;--可以加入insert,update等語句/*循環(huán)結(jié)束*/endloopmyLoop;/*關(guān)閉游標*/closemyCursor;END定義條件控制的存儲過程

CREATEPROCEDUREproc2(INparameterint)begindeclarevarint;setvar=parameter+1;ifvar=0theninsertintotvalues(17);endif;ifparameter=0thenupdatetsets1=s1+1;elseupdatetsets1=s1+2;endif;end;函數(shù)MySQL函數(shù)，是一種控制流程函數(shù)，屬于數(shù)據(jù)庫用語言。4.2.1.系統(tǒng)函數(shù)（列舉部分常用函數(shù)）流程控制函數(shù)a)IF函數(shù)用法語法：IF(expr1,expr2,expr3)函數(shù)用法說明：如果expr1是TRUE(expr1<>0andexpr1<>NULL)，則IF()的返回值為expr2;否則返回值則為expr3。IF()的返回值為數(shù)字值或字符串值，具體情況視其所在語境而定b)IFNULL函數(shù)語法：IFNULL(expr1,expr2)函數(shù)用法說明：假如expr1不為NULL，則IFNULL()的返回值為expr1;否則其返回值為expr2。IFNULL()的返回值是數(shù)字或是字符串，具體情況取決于其所使用的語境字符串函數(shù)a)函數(shù)ascii(str)函數(shù)用法說明：返回值為字符串str的最左字符的數(shù)值。假如str為空字符串，則返回值為0。假如str為NULL，則返回值為NULL。ASCII()用于帶有從0到255的數(shù)值的字符b)函數(shù)BIN(N)函數(shù)用法說明：返回值為N的二進制值的字符串表示，其中N為一個longlong(BIGINT)數(shù)字。這等同于CONV(N,10,2)。假如N為NULL，則返回值為NULL。c)函數(shù)FORMAT(X,D)函數(shù)使用說明：將numberX設(shè)置為格式'#,###,###.##',以四舍五入的方式保留到小數(shù)點后D位,而返回結(jié)果為一個字符串。d)函數(shù)LEFT(str,len)函數(shù)使用說明：返回從字符串str開始的len最左字符e)函數(shù)LENGTH(str)函數(shù)使用說明：返回值為字符串str的長度，單位為字節(jié)。一個多字節(jié)字符算作多字節(jié)。這意味著對于一個包含5個2字節(jié)字符的字符串，LENGTH()的返回值為10,而CHAR_LENGTH()的返回值則為5。f)函數(shù)LOWER(str)函數(shù)使用說明：返回字符串str以及所有根據(jù)最新的字符集映射表變?yōu)樾懽帜傅淖址鹓)函數(shù)LPAD(str,len,padstr)函數(shù)使用說明：返回字符串str,其左邊由字符串padstr填補到len

字符長度。假如str的長度大于len,則返回值被縮短至len字符。有用到可以直接搜索/sugang_ximi/article/details/66647484.2.2.自定義函數(shù)創(chuàng)建一個時間轉(zhuǎn)換函數(shù)DELIMITER$$DROPFUNCTIONIFEXISTS`test`.`getdate`$$CREATEFUNCTION`test`.`getdate`(gdatedatetime)RETURNSvarchar(255)BEGINDECLARExVARCHAR(255)DEFAULT'';SETx=date_format(gdate,'%Y年%m月%d日%h時%i分%s秒');RETURNx;END$$DELIMITER;第一句是定義一個結(jié)束標識符，因為MySQL默認是以分號作為SQL語句的結(jié)束符的，而函數(shù)體內(nèi)部要用到分號，所以會跟默認的SQL結(jié)束符發(fā)生沖突，所以需要先定義一個其他的符號作為SQL的結(jié)束符；第二句是如果這個函數(shù)已經(jīng)存在了，就刪除掉，test是數(shù)據(jù)庫的名字，函數(shù)是跟數(shù)據(jù)庫相關(guān)聯(lián)的，getdate是函數(shù)的名字；第三句是創(chuàng)建一個函數(shù)，（）里是參數(shù)的名字和類型，RETURNS定義這個函數(shù)返回值的類型；函數(shù)體必須放在BEGINEND之間；DECLARE是定義函數(shù)體的變量，這里定義一個變量x,默認是空，然后SET給x變量賦值；RETURN是返回值，這里把變量x返回，x的類型必須與第三句中定義的返回類型一致。運行：SELECTgetdate('2009-06-2300:00:00');分支結(jié)構(gòu)DELIMITER$$DROPFUNCTIONIFEXIS