Navicat與Mysql詳解

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

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

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

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

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

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

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

8、不能完全的支持。在關(guān)許數(shù)據(jù)庫中，數(shù)據(jù)庫的重構(gòu)造往往是不可避免的。重構(gòu)數(shù)據(jù)庫最常見的是將一個基本表“垂直”地分成多個基本表。例如：將學(xué)生關(guān)系Student（Sno，Sname，Ssex，Sage，Sdept）分為SX（Sno，Sname，Sage）和SY（Sno，Ssex，Sdept）兩個系。這時原表Student為SX表和SY表自然連接的結(jié)果。如果建立個視圖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ù)庫的邏輯結(jié)構(gòu)改變了（變?yōu)镾X和SY兩個表了），但應(yīng)用程序不必修改，因?yàn)樾陆⒌囊晥D定義為用戶原來的關(guān)系，使用戶的外模式保持不變，用戶的應(yīng)用程序通過視圖仍然能夠查找數(shù)據(jù)。當(dāng)然，視圖只能在一定程度上提供數(shù)據(jù)的邏輯獨(dú)立，比如由于視圖的更新是有條件的，因此應(yīng)用程序中修改數(shù)據(jù)的語句可能仍會因?yàn)榛颈順?gòu)造的改變而改變。另外，值得注意的是，因?yàn)橐晥D也是一種表，是虛擬表，不能與已有的表(視圖)出現(xiàn)重名。并且將視圖看成與表一樣的東西，更加容易理解使用規(guī)則。4.3 對于視圖的創(chuàng)建視圖的創(chuàng)建與表差不多。右擊視圖，新建視圖。圖圖圖第二部分 常見的一些MySQL語句與索引1.

10、寫在開始一般來說，創(chuàng)建好新表之后，不能直接用Navicat手動輸入數(shù)據(jù)。Navicat 是以直覺化的圖形用戶界面而建的，讓你可以以安全并且簡單的方式創(chuàng)建、組織、訪問并共用信息。那如何對數(shù)據(jù)庫里的數(shù)據(jù)進(jìn)行增刪查改呢？這時候，就需要用到MySQL語句了。你可以編寫程序，拼寫MySQL語句傳到數(shù)據(jù)庫進(jìn)行數(shù)據(jù)操作，也可以直接運(yùn)行命令行來操作。這里，我們只示范命令行的拼寫MySQL語句。而對于數(shù)據(jù)較多的情況，我們則需要使用索引來優(yōu)化我們的程序。2.MySQL語句 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; 上述語句在指定數(shù)據(jù)庫中創(chuàng)建了一個名為“emp”的表。同時會有一個有主鍵約束的自增長（AUTO_INCREMENT）字段“id”，自增保證了每次插入新行的時候id都會加1，而主鍵 約束則用來檢驗(yàn)重復(fù)值

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

13、transaction WHERE transactionID=。因?yàn)橹熬驮?transaction 里面加入的數(shù)據(jù)，所以改sql語句運(yùn)行后的結(jié)果是：圖2.2.2選擇列SELECT 字段名,字段名 FROM 表名這個很好理解，就不多做解釋。如圖：圖2.2.3 多表查詢語句SELECT a.字段名,b.字段名 FROM 表名 a,表名 b圖這樣就相當(dāng)于 將表ialarm的引用賦給a，transaction的引用賦給b，然后直接使用a. 和 b. 來去字段值2.2.4連表查詢語句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 這里有一個關(guān)鍵詞，AS。所謂連表，就是將兩個或兩個以上的表連在一起

15、，看做一個表來取數(shù)據(jù)，以提高效率。最后查詢出來的數(shù)據(jù)會合成一個結(jié)果集返回。2.3插入語句（增）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 更新語句（改）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 刪除語句（刪）DELECT FROM 數(shù)據(jù)表 WHERE 條件表達(dá)式 

17、0; DELECT FROM 數(shù)據(jù)表  沒有條件將刪除數(shù)據(jù)表中所有記錄2.6 插入更新語句（混合語句）INSERT INTO 表名 (字段名) VALUES(對應(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建表時創(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寫在前面索引對查詢的速度有著至關(guān)重要的影響，理解索引也是進(jìn)行數(shù)據(jù)庫性能調(diào)優(yōu)的起點(diǎn)?？紤]如下情況，假設(shè)數(shù)據(jù)庫中一個表有106條記錄，DBMS的 頁面大小為4K，并存儲100條記錄。如果沒有索引

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

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

22、h) 創(chuàng)建表的時候直接指定CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX indexName (username(length)   );  刪除索引的語法：DROP INDEX indexName ON mytable;唯一索引它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(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)建表的時候直接指定CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE indexName (username(length)   ); 主鍵索引它是一種特殊的

24、唯一索引，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引：CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  當(dāng)然也可以用 ALTER 命令。記?。阂粋€表只能有一個主鍵。組合索引為了形象地對比單列索引和組合索引，為表添加多個字段：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建到一個索引里：ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表時，usernname長度為 16，這里用 10。這是因?yàn)橐话闱闆r下名字的長度不會超過10，這樣會加速索引查詢速度

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

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

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