Navicat與Mysql詳解

1、Navicat與mysql詳解第一部分 對(duì)Navicat的操作1.連接1.1創(chuàng)建新連接首先啟動(dòng)Navicat for MySQL，點(diǎn)擊左上角的【連接】（如圖1.1.1）：點(diǎn)擊之后便會(huì)出現(xiàn)如圖所示的界面（如圖1.1.2）：連接名可以任意取，主機(jī)名或IP地址則填寫(xiě)你需要連接數(shù)據(jù)庫(kù)的IP地址，如是本地，則填 localhost，但推薦填寫(xiě) ，這表示回送地址，指本地機(jī)，一般是用來(lái)測(cè)試的。端口默認(rèn)為3306，用戶名默認(rèn)是root，而密碼則是安裝時(shí)自己填寫(xiě)的密碼。填寫(xiě)完后，可點(diǎn)擊該界面左下角的測(cè)試連接，如顯示連接成功則可操縱數(shù)據(jù)庫(kù)，如失敗，請(qǐng)仔細(xì)檢查，或度娘。2.數(shù)據(jù)庫(kù)2.1創(chuàng)建新數(shù)據(jù)庫(kù)

2、雙擊你創(chuàng)建的連接，你會(huì)發(fā)現(xiàn)有幾個(gè)MySQL預(yù)先創(chuàng)建好的數(shù)據(jù)庫(kù)，這個(gè)你可暫且不管。自己新建幾個(gè)數(shù)據(jù)庫(kù)。右擊你新建的連接，點(diǎn)擊新建數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)名自取，字符集一般選uft8，排序規(guī)則可自選。3.表3.1創(chuàng)建新表雙擊你創(chuàng)建的數(shù)據(jù)庫(kù)，如圖1.3.1，我創(chuàng)建的數(shù)據(jù)庫(kù)名叫test，雙擊之后會(huì)變出現(xiàn)一些選項(xiàng)。右擊表，新建表。 如圖1.3.2所示，每一行數(shù)據(jù)有六列，從左到右分別是：數(shù)據(jù)的名稱（最好是英文），該數(shù)據(jù)的類(lèi)型（可度娘MySQL的數(shù)據(jù)類(lèi)型，可對(duì)應(yīng)java的基本數(shù)據(jù)類(lèi)型），該數(shù)據(jù)允許的長(zhǎng)度，允許保留的小數(shù)點(diǎn)位數(shù)，允不允許可以為空值，最后一個(gè)是設(shè)置主鍵。一個(gè)表最好有一個(gè)主鍵，同一個(gè)表里每一行數(shù)據(jù)的主鍵值各

3、不相同，這樣才能將同一表的數(shù)據(jù)區(qū)分開(kāi)來(lái)，操縱數(shù)據(jù)也方便得多。填寫(xiě)完之后，點(diǎn)擊左上角的保存，此時(shí)會(huì)彈出一個(gè)窗口讓你填寫(xiě)表名，表名寫(xiě)入之后返回Navicat主窗口，就會(huì)發(fā)現(xiàn)自己新建的數(shù)據(jù)庫(kù)里多了一個(gè)表。依次，我們新建四個(gè)表，并且使四個(gè)表之間相互聯(lián)系。圖3.2設(shè)計(jì)表之前設(shè)計(jì)的四個(gè)表分別是，transaction（交易表）、transactionProductAssign（交易與產(chǎn)品對(duì)應(yīng)表）、product（產(chǎn)品表）、productItem（產(chǎn)品明細(xì)表）。Transaction是交易表，里面的每一行數(shù)據(jù)儲(chǔ)存著一條交易信息，transactionProductAssign是交易與產(chǎn)品對(duì)應(yīng)表，里面儲(chǔ)存的是

4、每一條交易對(duì)應(yīng)的產(chǎn)品號(hào)。交易表和交易與產(chǎn)品對(duì)應(yīng)表是一對(duì)多的關(guān)系，即，一條交易對(duì)應(yīng)多個(gè)產(chǎn)品。如圖1.3.6、圖1.3.7所示：交易對(duì)應(yīng)多個(gè)產(chǎn)品，一條交易與產(chǎn)品對(duì)應(yīng)信息則對(duì)應(yīng)一個(gè)具體產(chǎn)品（即交易與產(chǎn)品對(duì)應(yīng)表、產(chǎn)品明細(xì)表是一對(duì)一的關(guān)系）。而product（即產(chǎn)品表）則是，一條產(chǎn)品數(shù)據(jù)代表著產(chǎn)品，而這個(gè)產(chǎn)品又有很多種類(lèi)、版本，所以，一個(gè)產(chǎn)品信息對(duì)應(yīng)多個(gè)產(chǎn)品明細(xì)信息。說(shuō)通俗點(diǎn)，就是，一個(gè)人去買(mǎi)東西，買(mǎi)了一次東西就是一筆交易（存在transaction表里面）。而這筆交易買(mǎi)了很多的產(chǎn)品（對(duì)應(yīng)關(guān)系存在transactionProductAssign表里面）。這些產(chǎn)品可能有：腎6，腎5S，心相印面巾紙，知音面

5、巾紙（具體產(chǎn)品存在productItem表里面）。其中，腎6、腎5S都屬于蘋(píng)果系列，心相印面巾紙、知音面巾紙都屬于紙巾系列，所以蘋(píng)果和紙巾便是一種產(chǎn)品的統(tǒng)稱（存在productI表里面）。那為什么要這樣設(shè)計(jì)呢？為什么不把這些設(shè)計(jì)在一張表里面，這樣省時(shí)省事。但是，如果你仔細(xì)研究，你就會(huì)發(fā)現(xiàn)，這里面暗藏玄機(jī)。首先就看這一點(diǎn)來(lái)說(shuō)，如果所有的交易信息、產(chǎn)品信息全在一個(gè)表的一行數(shù)據(jù)里，第一不美觀，第二讓人很難看得下去。最重要的一點(diǎn)是，為了消除冗余。其次就直觀這點(diǎn)來(lái)說(shuō)，交易與產(chǎn)品對(duì)應(yīng)，產(chǎn)品與產(chǎn)品版本對(duì)應(yīng)，這樣能讓人一目了然，使整個(gè)數(shù)據(jù)庫(kù)都連接了起來(lái)，便于以后的擴(kuò)展與修改設(shè)計(jì)。這只是一個(gè)很小的例子，其實(shí)數(shù)據(jù)

6、庫(kù)設(shè)計(jì)有著很大的學(xué)問(wèn)在里面。慢慢研究，你會(huì)發(fā)現(xiàn)，設(shè)計(jì)數(shù)據(jù)庫(kù)其實(shí)是一項(xiàng)很偉大的工程！4.視圖4.1什么是視圖視圖是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表。它與基本表不同，是一個(gè)虛表。數(shù)據(jù)庫(kù)只存放視圖的定義，而不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在原來(lái)的基本表中。所以基本表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也就隨之改變了。從這個(gè)意義上講，視圖就像一個(gè)窗口，透過(guò)它可以看到數(shù)據(jù)庫(kù)中自己感興趣的數(shù)據(jù)及其變化。4.2視圖的好處視圖能簡(jiǎn)化用戶操作視圖機(jī)制使用戶可以將注意力集中在所關(guān)心地?cái)?shù)據(jù)上。如果這些數(shù)據(jù)不是直接來(lái)自基本表，則可以通過(guò)定義視圖，使數(shù)據(jù)庫(kù)看起來(lái)結(jié)構(gòu)簡(jiǎn)單、清晰，并且可以簡(jiǎn)化用戶的的數(shù)據(jù)查詢操作

7、。例如，那些定義了若干張表連接的視圖，就將表與表之間的連接操作對(duì)用戶隱藏起來(lái)了。換句話說(shuō)，用戶所作的只是對(duì)一個(gè)虛表的簡(jiǎn)單查詢， 而這個(gè)虛表是怎樣得來(lái)的，用戶無(wú)需了解。 視圖使用戶能以多種角度看待同一數(shù)據(jù)。視圖機(jī)制能使不同的用戶以不同的方式看待同一數(shù)據(jù)，當(dāng)許多不同種類(lèi)的用戶共享同一個(gè)數(shù)據(jù)庫(kù)時(shí)，這種靈活性是非常必要的。視圖對(duì)重構(gòu)數(shù)據(jù)庫(kù)提供了一定程度的邏輯獨(dú)立性數(shù)據(jù)的物理獨(dú)立性是指用戶的應(yīng)用程序不依賴于數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)。數(shù)據(jù)的邏輯獨(dú)立性是指當(dāng)數(shù)據(jù)庫(kù)重構(gòu)造時(shí)，如增加新的關(guān)系或?qū)υ械年P(guān)系增加新的字段，用戶 的應(yīng)用程序不會(huì)受影響。層次數(shù)據(jù)庫(kù)和網(wǎng)狀數(shù)據(jù)庫(kù)一般能較好地支持?jǐn)?shù)據(jù)的物理獨(dú)立性，而對(duì)于邏輯獨(dú)立性則

8、不能完全的支持。在關(guān)許數(shù)據(jù)庫(kù)中，數(shù)據(jù)庫(kù)的重構(gòu)造往往是不可避免的。重構(gòu)數(shù)據(jù)庫(kù)最常見(jiàn)的是將一個(gè)基本表“垂直”地分成多個(gè)基本表。例如：將學(xué)生關(guān)系Student（Sno，Sname，Ssex，Sage，Sdept）分為SX（Sno，Sname，Sage）和SY（Sno，Ssex，Sdept）兩個(gè)系。這時(shí)原表Student為SX表和SY表自然連接的結(jié)果。如果建立個(gè)視圖StudentCREATE VIEW Student（Sno，Sname，Ssex，Sage，Sdept）AS SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept FROM SX，SY WHERE

9、 SX.Sno=SY.Sno；這樣盡管數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)改變了（變?yōu)镾X和SY兩個(gè)表了），但應(yīng)用程序不必修改，因?yàn)樾陆⒌囊晥D定義為用戶原來(lái)的關(guān)系，使用戶的外模式保持不變，用戶的應(yīng)用程序通過(guò)視圖仍然能夠查找數(shù)據(jù)。當(dāng)然，視圖只能在一定程度上提供數(shù)據(jù)的邏輯獨(dú)立，比如由于視圖的更新是有條件的，因此應(yīng)用程序中修改數(shù)據(jù)的語(yǔ)句可能仍會(huì)因?yàn)榛颈順?gòu)造的改變而改變。另外，值得注意的是，因?yàn)橐晥D也是一種表，是虛擬表，不能與已有的表(視圖)出現(xiàn)重名。并且將視圖看成與表一樣的東西，更加容易理解使用規(guī)則。4.3 對(duì)于視圖的創(chuàng)建視圖的創(chuàng)建與表差不多。右擊視圖，新建視圖。圖圖圖第二部分 常見(jiàn)的一些MySQL語(yǔ)句與索引1.

10、寫(xiě)在開(kāi)始一般來(lái)說(shuō)，創(chuàng)建好新表之后，不能直接用Navicat手動(dòng)輸入數(shù)據(jù)。Navicat 是以直覺(jué)化的圖形用戶界面而建的，讓你可以以安全并且簡(jiǎn)單的方式創(chuàng)建、組織、訪問(wèn)并共用信息。那如何對(duì)數(shù)據(jù)庫(kù)里的數(shù)據(jù)進(jìn)行增刪查改呢？這時(shí)候，就需要用到MySQL語(yǔ)句了。你可以編寫(xiě)程序，拼寫(xiě)MySQL語(yǔ)句傳到數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)操作，也可以直接運(yùn)行命令行來(lái)操作。這里，我們只示范命令行的拼寫(xiě)MySQL語(yǔ)句。而對(duì)于數(shù)據(jù)較多的情況，我們則需要使用索引來(lái)優(yōu)化我們的程序。2.MySQL語(yǔ)句 2.1 創(chuàng)建表CREATE TABLE 'emp' ( 'id' INTEGER UNSIGNED NOT N

11、ULL AUTO_INCREMENT, 'name' VARCHAR(45) NOT NULL, 'lastname' VARCHAR(45), 'dept' VARCHAR(45) DEFAULT 'sales', PRIMARY KEY ('id')ENGINE = InnoDB;）ENGINE = InnoDB; 上述語(yǔ)句在指定數(shù)據(jù)庫(kù)中創(chuàng)建了一個(gè)名為“emp”的表。同時(shí)會(huì)有一個(gè)有主鍵約束的自增長(zhǎng)（AUTO_INCREMENT）字段“id”，自增保證了每次插入新行的時(shí)候id都會(huì)加1，而主鍵 約束則用來(lái)檢驗(yàn)重復(fù)值

12、。字段“dept”被賦予了默認(rèn)值（當(dāng)沒(méi)設(shè)定內(nèi)容時(shí)為“sales”）。你可以在創(chuàng)建表的時(shí)候指定“引擎”。否則會(huì)使用默認(rèn)引擎 （MyISAM）。我們?cè)谶@里使用“InnoDB”是因?yàn)樗С滞怄I和事務(wù)處理。2.2 查詢語(yǔ)句（查）在平常生活中，我們對(duì)于數(shù)據(jù)的查詢是做得最多的一件事。所以我們這里主要講解一下關(guān)于查詢的語(yǔ)句。2.2.1單表查詢語(yǔ)句SELECT * FROM 表名 WHERE (條件);  *表示查詢所有的字段，也可以只選擇個(gè)別字段。這樣就能將一行或幾行符合條件的數(shù)據(jù)從具體的某個(gè)表里取出來(lái)。比如 SELECT * FROM

13、transaction WHERE transactionID=。因?yàn)橹熬驮?transaction 里面加入的數(shù)據(jù)，所以改sql語(yǔ)句運(yùn)行后的結(jié)果是：圖2.2.2選擇列SELECT 字段名,字段名 FROM 表名這個(gè)很好理解，就不多做解釋。如圖：圖2.2.3 多表查詢語(yǔ)句SELECT a.字段名,b.字段名 FROM 表名 a,表名 b圖這樣就相當(dāng)于 將表ialarm的引用賦給a，transaction的引用賦給b，然后直接使用a. 和 b. 來(lái)去字段值2.2.4連表查詢語(yǔ)句SELECT DISTINCT tra.transactionID,ductID,duct

14、ItemID,ductName,ductVersion,tra.transactionProductAssignID,ductCode,pri.price FROM Transaction,transactionProductAssign AS tra,product AS pro,productItem AS priWHERE ductID=ductID AND transaction.transactionID=tra.transactionID AND 這里有一個(gè)關(guān)鍵詞，AS。所謂連表，就是將兩個(gè)或兩個(gè)以上的表連在一起

15、，看做一個(gè)表來(lái)取數(shù)據(jù)，以提高效率。最后查詢出來(lái)的數(shù)據(jù)會(huì)合成一個(gè)結(jié)果集返回。2.3插入語(yǔ)句（增）INSERT INTO  數(shù)據(jù)表 (字段1,字段2,字段3 ) VALUES (值1,值2,值3 )INSERT  INTO  數(shù)據(jù)表 VALUES (值1,值2,值3 )"  不指定具體字段名表示將按照數(shù)據(jù)表中字段的順序，依次添加  INSERT  INTO  目標(biāo)數(shù)據(jù)表 SELECT *&#

16、160;FROM源數(shù)據(jù)表把源數(shù)據(jù)表的記錄添加到目標(biāo)數(shù)據(jù)表2.4 更新語(yǔ)句（改）UPDATE  數(shù)據(jù)表 SET 字段名=字段值 WHERE 條件表達(dá)式UPDATE數(shù)據(jù)表 SET 字段1=值1,字段2=值2  字段n=值n WHERE 條件表達(dá)式UPDATE數(shù)據(jù)表 SET 字段1=值1,字段2=值2  字段n=值n 2.5 刪除語(yǔ)句（刪）DELECT FROM 數(shù)據(jù)表 WHERE 條件表達(dá)式 

17、0; DELECT FROM 數(shù)據(jù)表  沒(méi)有條件將刪除數(shù)據(jù)表中所有記錄2.6 插入更新語(yǔ)句（混合語(yǔ)句）INSERT INTO 表名 (字段名) VALUES(對(duì)應(yīng)字段值)ON DUPLICATE KEY UPDATE name='default name', lastname='default last name'2.7添加主鍵ALTER TABLE 'TABLE_NAME' DROP PRIMARY KEY, ADD PRIMARY KEY ('COLUMN_NAME');2.8

18、刪除主鍵ALTER TABLE 'TABLE_NAME' DROP PRIMARY KEY;2.9添加索引2.9.1建表時(shí)創(chuàng)建索引CREATE TABLE 'emp' ( Name varchar(45), 'id' INT, INDEX(ID)2.9.2 為已經(jīng)存在的表創(chuàng)建索引Create Index id_index on emp(ID);3. 索引3.1寫(xiě)在前面索引對(duì)查詢的速度有著至關(guān)重要的影響，理解索引也是進(jìn)行數(shù)據(jù)庫(kù)性能調(diào)優(yōu)的起點(diǎn)?？紤]如下情況，假設(shè)數(shù)據(jù)庫(kù)中一個(gè)表有106條記錄，DBMS的 頁(yè)面大小為4K，并存儲(chǔ)100條記錄。如果沒(méi)有索引

19、，查詢將對(duì)整個(gè)表進(jìn)行掃描，最壞的情況下，如果所有數(shù)據(jù)頁(yè)都不在內(nèi)存，需要讀取104個(gè)頁(yè)面，如果這 104個(gè)頁(yè)面在磁盤(pán)上隨機(jī)分布，需要進(jìn)行104次I/O，假設(shè)磁盤(pán)每次I/O時(shí)間為10ms(忽略數(shù)據(jù)傳輸時(shí)間)，則總共需要100s(但實(shí)際上要好 很多很多)。如果對(duì)之建立B-Tree索引，則只需要進(jìn)行l(wèi)og100(106)=3次頁(yè)面讀取，最壞情況下耗時(shí)30ms。這就是索引帶來(lái)的效果，很多 時(shí)候，當(dāng)你的應(yīng)用程序進(jìn)行SQL查詢速度很慢時(shí)，應(yīng)該想想是否可以建索引。索引是快速搜索的關(guān)鍵。MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的。3.2 索引的簡(jiǎn)單使用假如我們創(chuàng)建了一個(gè) mytable表：CREAT

20、E TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );   我們隨機(jī)向里面插入了10000條記錄，其中有一條：5555, admin。在查找username="admin"的記錄 SELECT * FROM mytable WHERE username='admin'時(shí)，如果在username上已經(jīng)建立了索引，MySQL無(wú)須任何掃描，即準(zhǔn)確可找到該記錄。相反，MySQL會(huì)掃描 所有記錄，即要

21、查詢10000條記錄。索引分單列索引和組合索引。單列索引，即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引，但這不是組合索引。組合索引，即一個(gè)索包含多個(gè)列。MySQL索引類(lèi)型包括：普通索引這是最基本的索引，它沒(méi)有任何限制。它有以下幾種創(chuàng)建方式：創(chuàng)建索引CREATE INDEX indexName ON mytable(username(length); 如果是CHAR，VARCHAR類(lèi)型，length可以小于字段實(shí)際長(zhǎng)度；如果是BLOB和TEXT類(lèi)型，必須指定 length，下同。修改表結(jié)構(gòu)ALTER mytable ADD INDEX indexName ON (username(lengt

22、h) 創(chuàng)建表的時(shí)候直接指定CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX indexName (username(length)   );  刪除索引的語(yǔ)法：DROP INDEX indexName ON mytable;唯一索引它與前面的普通索引類(lèi)似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：創(chuàng)建索引CREATE

23、UNIQUE INDEX indexName ON mytable(username(length) 修改表結(jié)構(gòu)ALTER mytable ADD UNIQUE indexName ON (username(length) 創(chuàng)建表的時(shí)候直接指定CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE indexName (username(length)   ); 主鍵索引它是一種特殊的

24、唯一索引，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引：CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  當(dāng)然也可以用 ALTER 命令。記?。阂粋€(gè)表只能有一個(gè)主鍵。組合索引為了形象地對(duì)比單列索引和組合索引，為表添加多個(gè)字段：CREATE TABLE mytable(   ID INT NOT NULL,  

25、;  username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  );  為了進(jìn)一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個(gè)索引里：ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表時(shí)，usernname長(zhǎng)度為 16，這里用 10。這是因?yàn)橐话闱闆r下名字的長(zhǎng)度不會(huì)超過(guò)10，這樣會(huì)加速索引查詢速度

26、，還會(huì)減少索引文件的大小，提高INSERT的更新速度。如果分別在 usernname，city，age上建立單列索引，讓該表有3個(gè)單列索引，查詢時(shí)和上述的組合索引效率也會(huì)大不一樣，遠(yuǎn)遠(yuǎn)低于我們的組合索引。雖然此時(shí)有了三個(gè)索引，但MySQL只能用到其中的那個(gè)它認(rèn)為似乎是最有效率的單列索引。建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面三組組合索引：usernname,city,age   usernname,city   usernname  為什么沒(méi)有 city，age這樣的組合索引呢？這是因?yàn)镸ySQL組合索引“最左前綴”的結(jié)果。簡(jiǎn)單的理解就

27、是只從最左面的開(kāi)始組合。并不是只要包含這三列的查詢都 會(huì)用到該組合索引，下面的幾個(gè)SQL就會(huì)用到這個(gè)組合索引：SELECT * FROM mytable WHREE username="admin" AND city="鄭州"  SELECT * FROM mytable WHREE username="admin" 而下面幾個(gè)則不會(huì)用到：SELECT * FROM mytable WHREE age=20 AND city="鄭州"  SELECT * FROM mytable WHREE c

28、ity="鄭州"建立索引的時(shí)機(jī)到這里我們已經(jīng)學(xué)會(huì)了建立索引，那么我們需要在什么情況下建立索引呢？一般來(lái)說(shuō)，在WHERE和JOIN中出現(xiàn)的列需要建立索引，但也不完全如此， 因?yàn)镸ySQL只對(duì)<，<=，=，>，>=，BETWEEN，IN，以及某些時(shí)候的LIKE才會(huì)使用索引。例如：SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city='鄭州' 此時(shí)就需要對(duì)city和age建立索引，由于mytable表的userame也出現(xiàn)在了JOIN子句中，也有對(duì)它建立索引的必要。剛才提到只有某些時(shí)候的LIKE才需建立索引。因?yàn)樵谝酝ㄅ浞?和_開(kāi)頭作查詢時(shí)，MySQL不會(huì)使用索引。例如下句會(huì)使用索引：SELECT * FROM mytable WHERE username like'admin%' 而下句就不會(huì)使用：SELEC