1、生物信息學數(shù)據(jù)庫設計,第四章 MySQL數(shù)據(jù)庫,學習內容,MySQL基礎,2,3,數(shù)據(jù)庫,數(shù)據(jù)庫(Database,簡稱DB)的定義： “按照數(shù)據(jù)結構來組織、存儲和管理數(shù)據(jù)的倉庫” J.Martin給數(shù)據(jù)庫下了一個比較完整的定義：數(shù)據(jù)庫是存儲在一起的相關數(shù)據(jù)的集合，這些數(shù)據(jù)是結構化的，無有害的或不必要的冗余，并為多種應用服務,4,數(shù)據(jù)庫,數(shù) 據(jù) 表 存儲過程 視 圖 .,產品數(shù)據(jù)庫,表,5,數(shù)據(jù)庫的發(fā)展史,人工管理階段 人力手工整理存儲數(shù)據(jù) 文件系統(tǒng)階段 使用磁盤文件來存儲數(shù)據(jù) 數(shù)據(jù)庫系統(tǒng)階段 關系型數(shù)據(jù)庫 高級數(shù)據(jù)庫階段 “關系-對象”型數(shù)據(jù)庫,6,數(shù)據(jù)庫的類型,純文本數(shù)據(jù)庫： 純文本數(shù)據(jù)庫

2、是只用空格符、制表符和換行符來分割信息的文本文件。適用于小型應用，對于大中型應用來說它存在諸多限制： 只能順序訪問，不能進行隨機訪問。 查找數(shù)據(jù)和數(shù)據(jù)關系或多用戶同時訪問進行寫操作時非常困難。 關系數(shù)據(jù)庫： 由于純文本數(shù)據(jù)庫存在諸多局限，所以人們開始研究數(shù)據(jù)模型，設計各種類型的使用方便的數(shù)據(jù)庫。在數(shù)據(jù)庫的發(fā)展史上，最具影響的數(shù)據(jù)庫模型有：層次模型、網狀模型和關系模型。其中，關系模型是目前應用最廣泛和最有發(fā)展前途的一種數(shù)據(jù)模型，其數(shù)據(jù)結構簡單，當前主流的數(shù)據(jù)庫系統(tǒng)幾乎都采用關系模型。,7,數(shù)據(jù)庫的優(yōu)點,數(shù)據(jù)按一定的數(shù)據(jù)模型組織、描述和儲存 可為各種用戶共享 冗余度較小，節(jié)省存儲空間 易擴展，編寫

3、有關數(shù)據(jù)庫應用程序,8,數(shù)據(jù)庫管理系統(tǒng),數(shù)據(jù)庫管理系統(tǒng)(Database Management System)是一種操縱和管理數(shù)據(jù)庫的大型軟件，是用于建立、使用和維護數(shù)據(jù)庫，簡稱DBMS。 關系型數(shù)據(jù)庫管理系統(tǒng)稱為RDBMS，R指Relation DBMS的作用 它對數(shù)據(jù)庫進行統(tǒng)一的管理和控制，以保證數(shù)據(jù)庫的安全性和完整性。,9,DBMS的主要功能,數(shù)據(jù)定義功能： 提供數(shù)據(jù)定義語言(DDL) 定義數(shù)據(jù)庫中的數(shù)據(jù)對象 數(shù)據(jù)操縱功能: 提供數(shù)據(jù)操縱語言(DML) 操縱數(shù)據(jù)實現(xiàn)對數(shù)據(jù)庫的基本操作 (查詢、插入、刪除和修改),10,數(shù)據(jù)庫的運行管理 保證數(shù)據(jù)的安全性、完整性 多用戶對數(shù)據(jù)的并發(fā)使用 發(fā)

4、生故障后的系統(tǒng)恢復 數(shù)據(jù)庫的建立和維護功能(實用程序) 數(shù)據(jù)庫數(shù)據(jù)批量裝載 數(shù)據(jù)庫轉儲 介質故障恢復 數(shù)據(jù)庫的重組織 性能監(jiān)視等,DBA的主要職責,11,時下流行的DBMS,Oracle 應用廣泛、功能強大，分布式數(shù)據(jù)庫系統(tǒng) “關系-對象”型數(shù)據(jù)庫 MySQL 快捷、可靠 開源、免費、與PHP組成經典的LAMP組合 SQL Server 針對不同用戶群體的五個特殊的版本 易用性好 DB2 應用于大型應用系統(tǒng)，具有較好的可伸縮性,12,數(shù)據(jù)庫系統(tǒng),數(shù)據(jù)庫系統(tǒng)（Database System，簡稱DBS）是一個實際可運行的存儲、維護和應用系統(tǒng)提供數(shù)據(jù)的軟件系統(tǒng) 數(shù)據(jù)庫系統(tǒng)構成 DBMS DB 應用

5、軟件 數(shù)據(jù)庫管理員 用戶,13,數(shù)據(jù)庫系統(tǒng)的使用,數(shù)據(jù)庫服務器,響應和提供數(shù)據(jù),應用程序,數(shù)據(jù)庫,操作和查詢,14,匈牙利命名法,匈牙利命名法由匈牙利程序員發(fā)明，他在微軟工作了多年，此命名法就是通過微軟的各種產品和文檔傳出來。多數(shù)有經驗的程序員，不管他們用的是哪門語言，都或多或少在使用它。 基本原則：變量名 = 屬性 類型 對象描述； 舉例來說，表單的名稱為form，那么在匈牙利命名法中可以簡寫為frm，則當表單變量名稱為Switchboard時，變量全稱應該為 frmSwitchboard。這樣很容易從變量名看出Switchboard是一個表單，同樣，如果此變量類型為標簽，那么就應命名成 l

6、blSwitchboard?？梢钥闯?，匈牙利命名法非常便于記憶，而且使變量名非常清晰易懂，這樣，增強了代碼的可讀性，方便各程序員之間相互交流代碼。,數(shù)據(jù)庫對象命名規(guī)范,遵循行業(yè)規(guī)范 當有相關國家/行業(yè)強制性數(shù)據(jù)結構標準規(guī)范存在時，用于存儲某業(yè)務數(shù)據(jù)的業(yè)務表在表名命名上原則上應該遵從標準規(guī)定，其表中相關字段的中文名稱（即數(shù)據(jù)項名稱）若標準規(guī)范上有規(guī)定的應遵循規(guī)定。此外，若標準規(guī)范上對數(shù)據(jù)項的類型、長度有規(guī)定的，原則上也應當遵循或保證能直接兼容保存和訪問。,15,字符范圍原則 只能使用英文字母、下劃線、數(shù)字進行命名，首位字符必須是英文字母。 分段命名原則 命名中多個單詞間采用下劃線分隔，以便閱讀同

7、時方便某些工具對數(shù)據(jù)庫對象的映射。如XXX_XXX_XXX，但不限于三段式。 勿用保留詞 數(shù)據(jù)庫對象命名不能直接使用數(shù)據(jù)庫保留關鍵字，但分段中可以使用。如USER不能用于表名、列名等，但是USER_NAME可以用于列名，USER_INFO也可以用于表名。,16,簡單命名原則 命名盡可能簡單，避免太長的命名，盡量使用縮寫形式，但是縮寫也要能夠表達命名的含義。數(shù)據(jù)庫對象命名總長度不得超過30字節(jié)，以免超過數(shù)據(jù)庫命名長度限制。建議每個單詞分段長度不要超過6位。 富有含義原則 數(shù)據(jù)庫對象命名通常用能表示其內容或者含義的英文單詞或其縮寫表示也可用其中文名稱各字詞的拼音首寫字母或者拼音簡寫方式表示。數(shù)字應

8、盡量避免使用。,17,同義性原則 對于同一含義盡量使用相同的單詞命名，不管使用英文單詞、英文縮寫還是拼音首字母，以免引起誤解。如TELEPNHOE的A表中表示固定電話號碼，在B表中就不應該用于表示移動電話號碼。盡量避免同一單詞表示多種含義的情況。 命名方式一致原則 在一個系統(tǒng)、一個項目中盡量采用一致的命名方式，都采用英文單詞或者拼音首字母。尤其要避免在一個對象命名中同時采用英文單詞和拼音首字母。,18,19,20,系統(tǒng)信息類表以T_SYS_開頭、要素類表以T_IDX_開頭、接口類表以T_INF_開頭、臨時表以T_TMP_開頭、統(tǒng)計分析系統(tǒng)表以T_TJFX_開頭等。 T_UserInfo、USE

9、R_INFO、UserInfo、T_用戶信息、TB_USER_INFO、TBL_USER_INFO、T$USER$INFO、等都是違反本規(guī)范的，正確命名為T_USER_INFO,21,列命名,1. 正確命名：USER_NAME、AUDIT_TIME、AUDIT_USER 2. 錯誤命名：USERNAME、UserName、C_USER_NAME、人員姓名，違反規(guī)范。 3. 錯誤命名：COMMENT、AUDIT，違反保留字,22,23,MySQL概述,MySQL是一個高性能、多線程、多用戶、建立在客戶/服務器結構上的開源關系型數(shù)據(jù)庫管理系統(tǒng) MySQL是世界上最受歡迎的開放源代碼數(shù)據(jù)庫，當前全世

10、界有超過1000萬的系統(tǒng)使用MySQL MySQL的官方網站為,24,MySQL數(shù)據(jù)庫,什么是MySQL MySQL是一個小型關系型數(shù)據(jù)庫管理系統(tǒng)，開發(fā)者為瑞典MySQL AB公司。目前MySQL被廣泛地應用在Internet上的中小型網站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，許多中小型 網站為了降低網站總體擁有成本而選擇了MySQL作為網站數(shù)據(jù)庫。,25,MySQl特征,使用C和C+編寫，并使用了多種編譯器進行測試，保證源代碼的可移植性 支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wra

11、p、Solaris、Windows等多種操作系統(tǒng) 為多種編程語言提供了API。這些編程語言包括C、C+、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。 支持多線程，充分利用CPU資源 優(yōu)化的SQL查詢算法，有效地提高查詢速度,26,MySQl特征,既能夠作為一個單獨的應用程序應用在客戶端服務器網絡環(huán)境中，也能夠作為一個庫而嵌入到其他的軟件中提供多語言支持，常見的編碼如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作數(shù)據(jù)表名和數(shù)據(jù)列名 提供TCP/IP、ODBC和JDBC等多種數(shù)據(jù)庫連接途徑 提供用于管理、檢查、優(yōu)化數(shù)據(jù)庫操作的管理工具 可以處理

12、擁有上千萬條記錄的大型數(shù)據(jù)庫,27,MySQL5特性,子查詢 視圖 存儲過程 觸發(fā)器 事務處理 熱備份 二進制Bit類型 等等,28,MySQL不足,不能直接處理XML數(shù)據(jù) 一些功能上支持的不夠完善和成熟 不能提供任何OLAP（實時分析系統(tǒng)）功能 等等.,29,30,MySQL應用,與其他的大型數(shù)據(jù)庫例如Oracle、DB2、SQL Server等相比，MySQL自有它的不足之處，如規(guī)模小、功能有限（MySQL Cluster的功能和效率都相對比較差）等，但是這絲毫也沒有減少它受歡迎的程度。對于一般的個人使用者和中小型企業(yè)來說，MySQL提供的功能已經綽綽有余，而且由于MySQL是開放源碼軟件

13、，因此可以大大降低總體擁有成本。,31,MySQL應用,目前Internet上流行的網站構架方式是LAMP（Linux+Apache+MySQL+PHP），即使用Linux作為操作系統(tǒng)，Apache作為Web服務器，MySQL作為數(shù)據(jù)庫，PHP作為服務器端腳本解釋器。由于這四個軟件都是自由或開放源碼軟件（FLOSS)，因此使用這種方式不用花一分錢就可以建立起一個穩(wěn)定、免費的網站系統(tǒng)。,32,MySQL管理,可以使用命令行工具管理MySQL數(shù)據(jù)庫（命令mysql 和 mysqladmin)，也可以從MySQL的網站下載圖形管理工具MySQL Query Browser。,33,MySQL存儲引擎

14、,MyISAM Mysql的默認數(shù)據(jù)庫引擎，最為常用。擁有較高的插入，查詢速度，但不支持事務 InnoDB 事務型數(shù)據(jù)庫的首選引擎，支持ACID事務，支持行級鎖定 BDB 源自Berkeley DB，事務型數(shù)據(jù)庫的另一種選擇，支持COMMIT和ROLLBACK等其他事務特性 Memory 所有數(shù)據(jù)置于內存的存儲引擎，擁有極高的插入，更新和查詢效率。但是會占用和數(shù)據(jù)量成正比的內存空間。并且其內容會在Mysql重新啟動時丟失 Merge 將一定數(shù)量的MyISAM表聯(lián)合而成一個整體，在超大規(guī)模數(shù)據(jù)存儲時很有用,34,MySQL存儲引擎,Archive 非常適合存儲大量的獨立的，作為歷史記錄的數(shù)據(jù)。因

15、為它們不經常被讀取。Archive擁有高效的插入速度，但其對查詢的支持相對較差 Federated 將不同的Mysql服務器聯(lián)合起來，邏輯上組成一個完整的數(shù)據(jù)庫。非常適合分布式應用 Cluster/NDB 高冗余的存儲引擎，用多臺數(shù)據(jù)機器聯(lián)合提供服務以提高整體性能和安全性。適合數(shù)據(jù)量大，安全和性能要求高的應用 CSV 邏輯上由逗號分割數(shù)據(jù)的存儲引擎 BlackHole 黑洞引擎，寫入的任何數(shù)據(jù)都會消失，一般用于記錄binlog做復制的中繼 另外，Mysql的存儲引擎接口定義良好。有興趣的開發(fā)者通過閱讀文檔編寫自己的存儲引擎。,35,當創(chuàng)建一個新表的時候，你可以通過添加一個ENGINE 或TYP

16、E 選項到CREATE TABLE語句來告訴MySQL你要創(chuàng)建什么類型的表： CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY; 如果省略掉ENGINE或TYPE選項，默認的存儲引擎被使用。一般的默認是MyISAM，但你可以用-default-storage-engine或-default-table-type服務器啟動選項來改變它，或者通過設置storage_engine或table_type系統(tǒng)變量來改變。 當MySQL被用MySQL配置向導安裝在Windows平臺上，InnoDB存儲引

17、擎替代MyISAM存儲引擎作為替代.,Create table limbs(thing varchar(20),legs int,arms int);,37,事務安全表(TST) 比起非事務安全表 (NTST)有幾大優(yōu)勢： 更安全。即使MySQL崩潰或遇到硬件問題，要么自動恢復，要么從備份加事務日志恢復，你可以取回數(shù)據(jù)。 你可以合并許多語句，并用COMMIT語句同時接受它們全部（如果autocommit被禁止掉）。 你可以執(zhí)行ROLLBACK來忽略你的改變（如果autocommit被禁止掉）。 如果更新失敗，你的所有改變都變回原來。（用非事務安全表，所有發(fā)生的改變都是永久的）。 事務安全存儲引

18、擎可以給那些當前用讀得到許多更新的表提供更好的部署。,38,InnoDB概述 InnoDB具有提交，回滾和崩潰恢復能力的事務安全存儲引擎。 InnoDB鎖定在行級。 InnoDB支持FOREIGN KEY強制。 InnoDB是為處理巨大數(shù)據(jù)量時的最大性能設計。它的CPU效率可能是任何其它基于磁盤的關系數(shù)據(jù)庫引擎所不能匹敵的。 InnoDB存儲引擎被完全與MySQL服務器整合，InnoDB存儲引擎為在主內存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。InnoDB存儲它的表索引在一個表空間中，表空間可以包含數(shù)個文件（或原始磁盤分區(qū)）。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統(tǒng)

19、上。 InnoDB默認地被包含在MySQL二進制分發(fā)中。Windows Essentials installer使InnoDB成為Windows上MySQL的默認表。 InnoDB被用來在眾多需要高性能的大型數(shù)據(jù)庫站點上。著名的Internet新聞站點S運行在InnoDB上。Mytrix, Inc.在InnoDB上存儲超過1TB的數(shù)據(jù)，還有一些其它站點在InnoDB上處理平均每秒800次插入/更新的負荷。,39,查看MySQL的當前存儲引擎,查看MySQL提供的存儲引擎: mysql show engines; 當前默認的存儲引擎: mysql show variable

20、s like %storage_engine%; 表所用引擎(在顯示結果里參數(shù)engine后面的就表示該表當前用的存儲引擎): mysql show create table 表名; show create table limbs;,40,MySQL最常見的應用架構,單點（Single），適合小規(guī)模應用 復制（Replication），適合中小規(guī)模應用 集群（Cluster），適合大規(guī)模應用 MySQL新版本中增加了不少新的性能，包括對主鍵的更高速度的緩存，對子查詢的更好的支持，以及應網絡約會網站所要求的，基于地理信息的查詢。 其同步開發(fā)的5.0版本則把目標對準了企業(yè)用戶，獨具以下特點：對外鍵

21、的良好支持；系統(tǒng)自動報錯機制以及對存儲過程的充分支持。,集群軟件的分類：,集群軟件根據(jù)側重的方向和試圖解決的問題，分為三大類：高性能集群（High performance cluster，HPC）、負載均衡集群（Load balance cluster， LBC），高可用性集群（High availability cluster，HAC）。,按照集群工作的層面，可分為：數(shù)據(jù)庫服務器集群，應用服務器集群，交換機集群等 按照集群的架構，可分為：共享磁盤型，非共享磁盤型。 按照集群粒度不同：基于IO；基于操作系統(tǒng)；基于數(shù)據(jù)庫實例的；基于每個數(shù)據(jù)庫的等。,高性能集群（High performance

22、cluster，HPC），它是利用一個集群中的多臺機器共同完成同一件任務，使得完成任務的速度和可靠性都遠遠高于單機運行的效果。彌補了單機性能上的不足。該集群在天氣預報、環(huán)境監(jiān)控等數(shù)據(jù)量大，計算復雜的環(huán)境中應用比較多；,什么是負載均衡集群 負載均衡集群（Load balance cluster， LBC），它是利用一個集群中的多臺單機，完成許多并行的小的工作。一般情況下，如果一個應用使用的人多了，那么用戶請求的響應時間就會增大，機器的性能也會受到影響，如果使用負載均衡集群，那么集群中任意一臺機器都能響應用戶的請求，這樣集群就會在用戶發(fā)出服務請求之后，選擇當時負載最小，能夠提供最好的服務的這臺機器

23、來接受請求并相應，這樣就可用用集群來增加系統(tǒng)的可用性和穩(wěn)定性。這類集群在網站中使用較多；,什么是高可用性集群 高可用性集群（High availability cluster，HAC），它是利用集群中系統(tǒng) 的冗余，當系統(tǒng)中某臺機器發(fā)生損壞的時候，其他后備的機器可以迅速的接替它來啟動服務，等待故障機的維修和返回。最大限度的保證集群中服務的可用性。這類系統(tǒng)一般在銀行，電信服務這類對系統(tǒng)可靠性有高的要求的領域有著廣泛的應用。,46,MySQL基本操作,mysql、mysqladmin和mysqldump MySQL管理工具和用戶操作界面,47,MySQL應用基礎,客戶端程序和實用工具介紹 myisa

24、mpack：壓縮MyISAM表以產生更小的只讀表的一個工具 mysql：MySQL命令行工具，通過交互方式輸入SQL語句或從文件以批處理模式執(zhí)行SQL語句。 mysqlaccess：檢查訪問權限的主機名、用戶名和數(shù)據(jù)庫組合的客戶端工具。 mysqladmin：執(zhí)行管理操作的客戶端程序，用于創(chuàng)建或刪除數(shù)據(jù)庫、重載授權表、將表刷新到硬盤上及重新打開日志文件 mysqlbinlog：從二進制日志讀取語句的工具。 mysqlcheck：表維護程序，用于檢查、修復、分析及優(yōu)化表。 mysqldump：將MySQL數(shù)據(jù)庫轉儲到一個文件（如SQL語句或tab分隔符文本文件）。 mysqlhotcopy：數(shù)據(jù)

25、庫備份程序，可在MySQL服務器運行時快速備份MyISAM或ISAM表。 mysqlimport：數(shù)據(jù)導入程序，使用LOAD DATA INFILE將文本文件導入MySQL數(shù)據(jù)庫的相關表中。 mysqlshow：顯示數(shù)據(jù)庫、表、列及索引相關信息的客戶端程序。 perror：顯示系統(tǒng)或MySQL錯誤代碼含義的工具。 replace：更改文件中或標準輸入中字符串的實用工具。,48,mysql命令,mysql命令 mysql -h host_name -u user_name ppassword h：當連接MySQL服務器不在同臺主機時，填寫主機名或IP地址 u：登錄MySQL的用戶名 p：登錄My

26、SQL的密碼 注意:密碼如果寫在命令行的時候一定不能有空格。如果使用的系統(tǒng)為linux并且登陸用戶名字與MySQL的用戶名相同即可不用輸入用戶名密碼，linux默認是以root登陸，windows默認用戶是ODBC,49,通用的連接方式語法,MySQL -h localhost -u root p MySQL -u root p MySQL -hlocalhost -uroot pxxx MySQL -uroot pxxx Mysql ucbuser pcbpass Show databases; Show tables;,50,mysql程序常用命令,選定默認數(shù)據(jù)庫：use dbname;

27、顯示所有數(shù)據(jù)庫：show databases; 顯示默認數(shù)據(jù)庫中所有表：show tables; 放棄正在輸入的命令：c 顯示命令清單：h 退出mysql程序：q 查看MySQL服務器狀態(tài)信息：status;,51,處理SQL文件,mysql命令 處理存放在文件里的SQL命令 格式：mysql option dbname file.sql mysql u root p -default-character-set=latin1 dbname backup.sql 注：latin1是MySQL4.0的默認字符集或者utf-8是MySQL5.0的默認字符集，如果不知道使用什么字符集的時候，可以選用

28、這兩個其中一個,52,使用的幾點提示,字符集 Windows命令窗口默認字符集為cp850或者gbk，因此存在許多字符顯示不出來的問題。 可以在mysql命令加入-default-character-set=cp850或gbk選項，cp850字符集需要再追加一個 -character-sets-dir=C:/MySQL/share/characters選項,53,連接數(shù)據(jù)庫,使用mysql命令登錄MySQL數(shù)據(jù)庫服務器 語法：mysql -h 主機名或IP地址 -u 用戶名 -p 口令 例子：mysql -uroot -p 注意：root的密碼默認為空,54,創(chuàng)建數(shù)據(jù)庫（1）,使用show d

29、atabases能夠查看MySQL系統(tǒng)中存在的數(shù)據(jù)庫,55,創(chuàng)建數(shù)據(jù)庫（2）,使用create database創(chuàng)建數(shù)據(jù)庫 使用use命令選擇數(shù)據(jù)庫 刪除數(shù)據(jù)庫 MySQL drop database accounts;,56,創(chuàng)建表,使用create table命令創(chuàng)建表 刪除表 mysql drop table acc;,57,創(chuàng)建表（2）,查看當前數(shù)據(jù)庫中的所有表,58,創(chuàng)建表（3）,使用describe（可以縮寫為desc）命令查看表的結構 desc limbs;,59,插入數(shù)據(jù)（1）,acc表的樣例數(shù)據(jù),60,插入數(shù)據(jù)（2）,使用insert命令插入數(shù)據(jù) 使用NULL值,61,查詢數(shù)

30、據(jù)（1）,select命令的格式,62,查詢數(shù)據(jù) (2),63,查詢數(shù)據(jù) (3),64,查詢數(shù)據(jù) (4)select * from limbs where thing=fish;,65,查詢數(shù)據(jù)：排序select thing as “事物”，legs,arms from limbs where thing like f%order by thingdesc;,66,程序數(shù)據(jù)：降序,67,查詢數(shù)據(jù)：別名,68,查詢數(shù)據(jù)：like（1）,69,查詢數(shù)據(jù)：like（2）,70,查詢數(shù)據(jù),將表的數(shù)據(jù)輸出到文本文件 Select * from limbs into outfile f:/int.out;

31、 Select count(*) from limbs; 行計數(shù),71,更新數(shù)據(jù),C:path C:MySQLMySQL Server 5.5bin C:mysql D cookbook -ucbuser -pcbpass f:limbs.sql Mysql D cookbook ucbuser pcbpass Select * from limbs; 一行輸出所有內容 Mysql D cookbook ucbuser -pcbpass e “select * from limbs;select now();select count(*) from limbs”,生成HTML輸出 Mysql

32、D cookbook ucbuser -pcbpass H e “select * from limbs;select now();select count(*) from limbs” 禁止頭部 Mysql D cookbook ucbuser -pcbpass skip-column-names H e “select * from limbs;select now()”,使長輸出行具備可讀性 show full columns from limbs; show full columns from limbsG 記錄交互式的Mysql會話 T f:/limbs.out select now

33、(); t,使用自定義變量 select name:=thing from limbs where legs=0; select name; select * from limbs where thing=name; set name:=thing from limbs where legs=-1; select name; /? set total=4*7+2; select total;,將MYSQL作為計算器 select 67+6-9/3; select 1=2; select zhang=yang; select zhang=Zhang;,查詢服務器版本和數(shù)據(jù)庫名 select ve

34、rsion(),database(); 如何處理NULL值 DROP TABLE IF EXISTS stu_info; # _CREATE_TABLE_ CREATE TABLE stu_info ( idVARCHAR(20), PRIMARY KEY (id), nameVARCHAR(20), class VARCHAR(10) );,INSERT INTO stu_info (name,id,class) VALUES (楊振軍,200801001,1); INSERT INTO stu_info (name,id,class) VALUES (吳楠,200801003,2); IN

35、SERT INTO stu_info (name,id,class) VALUES (白云,200801002,null); INSERT INTO stu_info (name,id) VALUES (楊超,200801004，null);,select * from stu_info where class is null; select * from stu_info where class is not null;,80,刪除數(shù)據(jù)delete from stu_info where class is nul;創(chuàng)建視圖create or replace view vw_stuinfo a

36、sselect id as 學號,name as 姓名from stu_info;show tables from cookbook;drop view vw_stuinfo;,事務控制,ROLLBACK-回滾; COMMIT- 提交。 提交數(shù)據(jù)類型： 顯式提交、隱式提交及自動提交。 (1) 顯式提交 用 COMMIT 命令直接完成的提交為顯式提交。 (2) 隱式提交 用 SQL 命令間接完成的提交為隱式提交。這些命令是： ALTER ， AUDIT ， COMMENT ， CONNECT ， CREATE ， DISCONNECT ， DROP ， EXIT ， GRANT ， NOAUDI

37、T ， QUIT ， REVOKE ， RENAME 。,(3) 自動提交 若把 AUTOCOMMIT 設置為 ON ，則在插入、修改、刪除語句執(zhí)行后， 系統(tǒng)將自動進行提交，這就是自動提交。其格式為： SQLSET AUTOCOMMIT=1 ； 所有的 DML 語句都是要顯式提交的，也就是說要在執(zhí)行完DML語句之后，執(zhí)行 COMMIT 。而其他的諸如 DDL 語句的，都是隱式提交的。也就是說，在運行那些非 DML 語句后，數(shù)據(jù)庫已經進行了隱式提交，例如 CREATE TABLE，在運行腳本后，表已經建好了，并不在需要你再進行顯式提交。 在提交事務（commit）之前可以用rollbacl回滾事

38、務。,set autocommit=off; show variables like “autocommit”;/查看自動提交狀態(tài) show columns from stu_info; insert into stu_info values(200801005,陳春艷,null);,修改提交方式也可在配置文件中進行 init-connect=set autocommit=off set autocommit=1; select * from stu_info; insert into stu_info values(200801006,邱景璇,3); select * from stu_in

39、fo; rollback; select * from stu_info;,顯示修改表所用的引擎 show create table stu_infoG alter table stu_info engine=myisam; select * from stu_info; set autocommit=off; show variables like auto%; desc stu_info;,insert into stu_info values(200800107,彭東海,1); rollback; select * from stu_info; ?,alter table stu_inf

40、o engine=innodb; show variables like autocommit; select * from stu_info; insert into stu_info values(200801008,劉燕霞,2); rollback; commit; select * from stu_info; insert into stu_info values(200801009,饒建安,3);,88,重命名表delete from stu_info where id like %9;alter table stu_info rename stud_info;show table

41、;select * from stu_info;rollback;,為什么myisam中在autocommit=off時修改后直接提交而無法回滾？ 為什么在rename后，修改的數(shù)據(jù)直接提交？還有那些語句會造成這種情形？ 設置手動提交的條件是什么？,多表查詢,創(chuàng)建表 DROP TABLE IF EXISTS stu_grade; create table stu_grade ( id varchar(20), subject varchar(20), primary key(id,subject), score decimal(4,2) );,Insert into stu_grade val

42、ues(200801001,R語言,91.5); Insert into stu_grade values(200801001,生物信息,92); Insert into stu_grade values(200801002,R語言,92.5); Insert into stu_grade values(200801002,生物信息,90.5); Insert into stu_grade values(200801012,生物信息,91);,內連接,只連接匹配的行 select as 姓名,b.subject as 課程,b.score as 成績 from stu_info

43、a ,stu_grade b where a.id=b.id; select as 姓名,b.subject as 課程,b.score as 成績 from stu_info a inner join stu_grade b on a.id=b.id;,左外連接,包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行 select as 姓名,b.subject as 課程,b.score as 成績 from stu_info a left join stu_grade b on a.id=b.id;,右外連接,包含右邊表的全部行（

44、不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行 select as 姓名,b.subject as 課程,b.score as 成績 from stu_info a right join stu_grade b on a.id=b.id;,全外連接,包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們匹配的行。Mysql不支持 select as 姓名,b.subject as 課程,b.score as 成績 from stu_info a full join stu_grade b;,交叉連接,生成笛卡爾積它不使用任何匹配或者選取條件，而

45、是直接將一個數(shù)據(jù)源中的每個行與另一個數(shù)據(jù)源的每個 行都一一匹配 select as 姓名,b.subject as 課程,b.score as 成績 from stu_info a cross join stu_grade b; select as 姓名,b.subject as 課程,b.score as 成績 from stud_info a, stu_grade b;,自己生成兩個有關聯(lián)的表，分別進行三種連接。,98,增加列alter table stu_info add birth_place int;alter table stud_info change

46、 birth_place birth_place varchar(15);alter table stud_info modifybirth_place varchar(50);alter table stud_info drop primary key;,99,改變列定義alter table stud_info add primary key(id);,在結果中選取部分行,select * from limbs limit 0,2; select * from limbs limit 3; select * from limbs limit 3,2; 處理重復值,用戶權限管理,MySQL用

47、戶包括普通用戶和root用戶。這兩種用戶的權限是不一樣的。root用戶是超級管理員，擁有所有的權限。root用戶的權限包括創(chuàng)建用戶、刪除用戶、修改普通用戶的密碼等管理權限。而普通用戶只擁有創(chuàng)建該用戶時賦予它的權限。用戶管理包括管理用戶的帳戶、權限等。,102,權限表介紹 添加用戶和權限 回收權限和刪除用戶 修改口令,1 權限表,安裝MySQL時會自動安裝一個名為mysql的數(shù)據(jù)庫。mysql數(shù)據(jù)庫下面存儲的都是權限表。用戶登錄以后，MySQL數(shù)據(jù)庫系統(tǒng)會根據(jù)這些權限表的內容為每個用戶賦予相應的權限。這些權限表中最重要的是user表、db表和host表。除此之外，還有tables_priv表、c

48、olumns_priv表、proc_priv表等。本節(jié)將介紹這些表的內容。,user表,user表是MySQL中最重要的一個權限表?？梢允褂肈ESC語句來查看user表的基本結構。user表有39個字段。這些字段大致可以分為四類，分別是用戶列、權限列、安全列和資源控制列。本小節(jié)將介紹這些字段含義。 1用戶列 2權限列 3安全列 4資源控制列,db表和host表,db表和host表也是MySQL數(shù)據(jù)庫中非常重要的權限表。db表中存儲了某個用戶對一個數(shù)據(jù)庫的權限。db表比較常用，而host表很少會用到?？梢允褂肈ESC語句來查看這兩個表的基本結構。這兩個表的表結構差不多。db表和host表的字段大

49、致可以分為兩類，分別是用戶列和權限列。 1用戶列 2權限列,tables_priv表和columns_priv表,tables_priv表可以對單個表進行權限設置。columns_priv表可以對單個數(shù)據(jù)列進行權限設置?？梢允褂肈ESC語句來查看這兩個表的基本結構。本小節(jié)將介紹這兩個表的內容。 tables_priv表包含8個字段，分別是Host、Db、User、Table_name、Table_priv、Column_priv、Timestamp和Grantor。前四個字段分別表示主機名、數(shù)據(jù)庫名、用戶名和表名。Table_priv表示對表進行操作的權限。這些權限包括Select、Inser

50、t、Update、Delete、Create、Drop、Grant、References、Index和Alter。Column_priv表示對表中的數(shù)據(jù)列進行操作的權限。這些權限包括Select、Insert、Update和References。Timestamp表示修改權限的時間。Grantor表示權限是誰設置的。,procs_priv表,procs_priv表可以存儲過程和存儲函數(shù)進行權限設置?？梢允褂肈ESC語句來查看procs_priv表的基本結構。本小節(jié)將介紹procs_priv表中各字段的含義。 procs_priv表包含8個字段，分別是Host、Db、User、Routine_n

51、ame、Routine_type、Proc_priv、Timestamp和Grantor。前三個字段分別表示主機名、數(shù)據(jù)庫名和用戶名。Routine_name字段表示存儲過程或函數(shù)的名稱。Routine_type字段表示類型。該字段有兩個取值，分別是FUNCTION和PROCEDURE。FUNCTION表示這是一個存儲函數(shù)。PROCEDURE表示這是一個存儲過程。Proc_priv字段表示擁有的權限。權限分為3類，分別是Execute，Alter Routine和Grant。Timestamp字段存儲更新的時間。Grantor字段存儲權限是誰設置的。,2添加用戶和權限,在MySQL數(shù)據(jù)庫中，可

52、以使用CREATE USER語句來創(chuàng)建新的用戶，也可以直接在mysql.user表中添加用戶。還可以使用GRANT語句來新建用戶。 1用CREATE USER語句來新建普通用戶 2用INSERT語句來新建普通用戶 3用GRANT語句來新建普通用戶,109,添加用戶 (1),使用grant命令添加用戶 Grant select,insert,update,delete on cookbook .* to cbuserlocalhost identified by cbpass;,權限管理,權限管理主要是對登錄到數(shù)據(jù)庫的用戶進行權限驗證。所有用戶的權限都存儲在MySQL的權限表中。數(shù)據(jù)庫管理員要對

53、權限進行管理。合理的權限管理能夠保證數(shù)據(jù)庫系統(tǒng)的安全。不當?shù)臋嘞拊O置可能會給數(shù)據(jù)庫系統(tǒng)帶來意想不到的危害。本節(jié)將介紹權限管理的內容。,111,權限管理,權限（ privileges） ALL 授予用戶如下的所有權限 ALTER 改變表、列和索引 CREATE 創(chuàng)建數(shù)據(jù)庫和表 DELETE 刪除表中的記錄 DROP 丟棄和刪除表和數(shù)據(jù)庫 FILE 讀、寫文件 INDEX 添加、刪除索引 INSERT 向表中插入記錄 PROCESS 查看和停止系統(tǒng)進程,112,添加用戶 (3),REFERENCES 參考 RELOAD 使用FLUSH語句 SELECT 從表中查詢記錄 SHUTDOWN 關閉MyS

54、QL數(shù)據(jù)庫 UPDATE 更新表中的記錄 USAGE 連接到MySQL數(shù)據(jù)庫，但是沒有權限,113,添加用戶：例子,例1：創(chuàng)建口令為 zhang的用戶zzg，該用戶在數(shù)據(jù)庫cookbook的所有表都有select和insert權限，并且能夠從任何主機連接到數(shù)據(jù)庫服務器 mysql grant select, insert on cookbook.* to zzglocalhost identified by “zhang”;,114,添加用戶：例子,例2：添加口令為45sdg11的用戶jane， 該用戶在數(shù)據(jù)庫myCompany的表employees上具有ALL權限，并且只能從特定的主機上連接

55、數(shù)據(jù)庫 mysql grant all on myCompany.employees to identified by “45sdg11”; 例3： mysql grant all on myCompany.employees to jane identified by “45sdg11”;,查看權限,show grants for current_user; show grants for rootlocalhost; show grants for zzglocalhost; use mysql show tables;,columns_priv db event f

56、unc general_log help_category help_keyword help_relation help_topic host ndb_binlog_index plugin proc procs_priv proxies_priv servers slow_log tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user,用戶權限查詢,select db,user from db; select user,select_priv,delete_priv, update_priv,insert_priv from user;,3回收權限和刪除用戶,在MySQL數(shù)據(jù)庫中，可以使用DROP USER語句來刪除普通用戶，也可以直接在mysql.usr表中刪除用戶。 1用DROP USER語句來刪除普通用戶 2用