第一部分 對Navicat的操作

        1.連接

        1.1創(chuàng)建新連接

        首先啟動Navicat for MySQL，點擊左上角的【連接】(如圖1.1.1):

        點擊之后便會出現(xiàn)如圖所示的界面(如圖1.1.2):

        連接名可以任意取，主機名或IP地址則填寫你需要連接數(shù)據(jù)庫的IP地址，如是本地，則填

        localhost，但推薦填寫 127.0.0.1，這表示回送地址，指本地機，一般是用來測試的。端口默認(rèn)為3306，用戶名默認(rèn)是root，而密碼則是安裝時自己填寫的密碼。

        填寫完后，可點擊該界面左下角的測試連接，如顯示連接成功則可操縱數(shù)據(jù)庫，如失敗，請仔細(xì)檢查，或度娘。

        2.數(shù)據(jù)庫

        2.1創(chuàng)建新數(shù)據(jù)庫

        雙擊你創(chuàng)建的連接，你會發(fā)現(xiàn)有幾個MySQL預(yù)先創(chuàng)建好的數(shù)據(jù)庫，這個你可暫且不管。自己新建幾個數(shù)據(jù)庫。

        右擊你新建的連接，點擊新建數(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)一些選項。

        右擊表，新建表。

        如圖1.3.2所示，每一行數(shù)據(jù)有六列，從左到右分別是:數(shù)據(jù)的名稱(最好是英文)，該數(shù)據(jù) 的類型(可度娘MySQL的數(shù)據(jù)類型，可對應(yīng)java的基本數(shù)據(jù)類型)，該數(shù)據(jù)允許的長度，允許保留的小數(shù)點位數(shù)，允不允許可以為空值，最后一個是設(shè)置主鍵。

        一個表最好有一個主鍵，同一個表里每一行數(shù)據(jù)的主鍵值各不相同，這樣才能將同一表的數(shù)據(jù)區(qū)分開來，操縱數(shù)據(jù)也方便得多。

        如圖1.3.3

        填寫完之后，點擊左上角的保存，此時會彈出一個窗口讓你填寫表名，表名寫入之后返回Navicat主窗口，就會發(fā)現(xiàn)自己新建的數(shù)據(jù)庫里多了一個表。

        依次，我們新建四個表，并且使四個表之間相互聯(lián)系。

        圖1.3.4

        點擊進入之后，表內(nèi)有字段，這些字段便是之前創(chuàng)建表時填寫的，如圖1.3.5

        圖1.3.5

        3.2設(shè)計表

        之前設(shè)計的四個表分別是，transaction(交易表)、transactionProductAssign(交易與產(chǎn)品對應(yīng)表)、product(產(chǎn)品表)、productItem(產(chǎn)品明細(xì)表)。

        Transaction是交易表，里面的每一行數(shù)據(jù)儲存著一條交易信息，transactionProductAssign是交易與產(chǎn)品對應(yīng)表，里面儲存的是每一條交易對應(yīng)的產(chǎn)品號。交易表和交易與產(chǎn)品對應(yīng)表是一對多的關(guān)系，即，一條交易對應(yīng)多個產(chǎn)品。如圖1.3.6、圖1.3.7所示:

        圖 1.3.6

        圖 1.3.7

        交易表里有一條交易信息，交易ID(即transactionID)為20140818183847274，而交易與產(chǎn)品對應(yīng)表里有兩條數(shù)據(jù)，雖然它們的transactionProductAssignID不同，productItemID也不同，但他們的transactionID相同。也就是說，這兩條交易與產(chǎn)品對應(yīng)信息屬于transactionID為20140818183847274的這筆交易。

        交易對應(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ì)信息。

        說通俗點，就是，一個人去買東西，買了一次東西就是一筆交易(存在transaction表里面)。而這筆交易買了很多的產(chǎn)品(對應(yīng)關(guān)系存在transactionProductAssign表里面)。這些產(chǎn)品可能有:腎6，腎5S，心相印面巾紙，知音面巾紙(具體產(chǎn)品存在productItem表里面)。其中，腎6、腎5S都屬于蘋果系列，心相印面巾紙、知音面巾紙都屬于紙巾系列，所以蘋果和紙巾便是一種產(chǎn)品的統(tǒng)稱(存在productI表里面)。

        那為什么要這樣設(shè)計呢,為什么不把這些設(shè)計在一張表里面，這樣省時省事。

        但是，如果你仔細(xì)研究，你就會發(fā)現(xiàn)，這里面暗藏玄機。

        首先就看這一點來說，如果所有的交易信息、產(chǎn)品信息全在一個表的一行數(shù)據(jù)里，第一不美觀，第二讓人很難看得下去。最重要的一點是，為了消除冗余。

        其次就直觀這點來說，交易與產(chǎn)品對應(yīng)，產(chǎn)品與產(chǎn)品版本對應(yīng)，這樣能讓人一目了然，使整個數(shù)據(jù)庫都連接了起來，便于以后的擴展與修改設(shè)計。

        這只是一個很小的例子，其實數(shù)據(jù)庫設(shè)計有著很大的學(xué)問在里面。

        慢慢研究，你會發(fā)現(xiàn)，設(shè)計數(shù)據(jù)庫其實是一項很偉大的工程～

        4.視圖

        4.1什么是視圖

        視圖是從一個或幾個基本表(或視圖)導(dǎo)出的表。它與基本表不同，是一個虛表。數(shù)據(jù)庫只存放視圖的定義，而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在原來的基本表中。所以基本表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也就隨之改變了。從這個意義上講，視圖就像一個窗口，透過它可以看到數(shù)據(jù)庫中自己感興趣的數(shù)據(jù)及其變化。

        4.2視圖的好處

        4.2.1

        視圖能簡化用戶操作視圖機制使用戶可以將注意力集中在所關(guān)心地數(shù)據(jù)上。如果這些數(shù)據(jù)不是直接來自基本表，則可以通過定義視圖，使數(shù)據(jù)庫看起來結(jié)構(gòu)簡單、清晰，并且可以簡化用戶的的數(shù)據(jù)查詢操作。例如，那些定義了若干張表連接的視圖，就將表與表之間的連接操作對用戶隱藏起來了。換句話說，用戶所作的只是對一個虛表的簡單查詢， 而這個虛表是怎樣得來的，用戶無需了解。

        4.2.2

        視圖使用戶能以多種角度看待同一數(shù)據(jù)。視圖機制能使不同的用戶以不同的方式看待同一數(shù)據(jù)，當(dāng)許多不同種類的用戶共享同一個數(shù)據(jù)庫時，這種靈活性是非常必要的。

        4.2.3

        視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性數(shù)據(jù)的物理獨立性是指用戶的應(yīng)用程序不依賴于數(shù)據(jù)庫的物理結(jié)構(gòu)。數(shù)據(jù)的邏輯獨立性是指當(dāng)數(shù)據(jù)庫重構(gòu)造時，如增加新的關(guān)系或?qū)υ械年P(guān)系增加新的字段，用戶 的應(yīng)用程序不會受影響。層次數(shù)據(jù)庫和網(wǎng)狀數(shù)據(jù)庫一般能較好地支持?jǐn)?shù)據(jù)的物理獨立性，而對于邏輯獨立性則不能完全的支持。 在關(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 SX.Sno=SY.Sno;

        這樣盡管數(shù)據(jù)庫的邏輯結(jié)構(gòu)改變了(變?yōu)镾X和SY兩個表了)，但應(yīng)用程序不必修改，因為新建立的視圖定義為用戶原來的關(guān)系，使用戶的外模式保持不變，用戶的應(yīng)用程序通過視圖仍然能夠查找數(shù)據(jù)。

        當(dāng)然，視圖只能在一定程度上提供數(shù)據(jù)的邏輯獨立，比如由于視圖的更新是有條件的，因此應(yīng)用程序中修改數(shù)據(jù)的語句可能仍會因為基本表構(gòu)造的改變而改變。 

        另外，值得注意的是，因為視圖也是一種表，是虛擬表，不能與已有的表(視圖)出現(xiàn)重名。并且將視圖看成與表一樣的東西，更加容易理解使用規(guī)則。

        4.3 對于視圖的創(chuàng)建

        視圖的創(chuàng)建與表差不多。右擊視圖，新建視圖。

        圖1.4.1

        你可在視圖創(chuàng)建工具里選擇一個之前創(chuàng)建的表，雙擊那個表，勾上你想要的數(shù)據(jù)，如圖1.4.2

        圖 1.4.2

        然后點保存。輸入視圖名后，便可看到你所創(chuàng)建的視圖，如圖1.4.3

        圖 1.4.3

        第二部分 常見的一些MySQL語句與索引 1.寫在開始

        一般來說，創(chuàng)建好新表之后，不能直接用Navicat手動輸入數(shù)據(jù)。Navicat 是以直覺化的圖形用戶界面而建的，讓你可以以安全并且簡單的方式創(chuàng)建、組織、訪問并共用信息。

        那如何對數(shù)據(jù)庫里的數(shù)據(jù)進行增刪查改呢, 這時候，就需要用到MySQL語句了。你可以編寫程序，拼寫MySQL語句傳到數(shù)據(jù)庫進行數(shù)據(jù)操作，也可以直接運行命令行來操作。這里，我們只示范命令行的拼寫MySQL語句。而對于數(shù)據(jù)較多的情況，我們則需要使用索引來優(yōu)化我們的程序。

        2.MySQL語句

        2.1 創(chuàng)建表

        CREATE TABLE 'emp' (

        'id' INTEGER UNSIGNED NOT NULL 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，而主鍵 約束則用來檢驗重復(fù)值。字段“dept”被賦予了默認(rèn)值(當(dāng)沒設(shè)定內(nèi)容時為“sales”)。你可以在創(chuàng)建表的時候指定“引擎”。否則會使用默認(rèn)引擎 (MyISAM)。我們在這里使用“InnoDB”是因為它支持外鍵和事務(wù)處理。

        2.2 查詢語句(查)

        在平常生活中，我們對于數(shù)據(jù)的查詢是做得最多的一件事。所以我們這里主要講解一下關(guān)于查詢的語句。

        2.2.1單表查詢語句

        SELECT * FROM [表名] WHERE ([條件]);

        *表示查詢所有的字段，也可以只選擇個別字段。這樣就能將一行或幾行符合條件的數(shù)據(jù)從具體的某個表里取出來。

        比如 SELECT * FROM transaction WHERE transactionID=‘20140818183847274’。因為之前就在 transaction 里面加入的數(shù)據(jù)，所以改sql語句運行后的結(jié)果是:

        圖 2.2.1

        2.2.2選擇列

        SELECT [字段名],[字段名] FROM [表名]

        這個很好理解，就不多做解釋。

        如圖:

        圖 2.2.2

        2.2.3 多表查詢語句

        SELECT a.[字段名],b.[字段名] FROM [表名] a,[表名] b

        圖 2.2.4

        這樣就相當(dāng)于 將表ialarm的引用賦給a，transaction的引用賦給b，然后直接使用a. 和 b. 來去字段值

        2.2.4連表查詢語句

        SELECT DISTINCT tra.transactionID,pri.productID,pri.productItemID,pro.productName, pri.productVersion,tra.transactionProductAssignID,pro.productCode,pri.price FROM Transaction,transactionProductAssign AS tra,product AS pro,productItem AS pri WHERE pri.productID=pro.productID AND transaction.transactionID=tra.transactionID AND

        tra.productItemID=pri.productItemID AND tra.transactionID="20140901204513645"

        這里有一個關(guān)鍵詞，AS。所謂連表，就是將兩個或兩個以上的表連在一起，看做一個表來取數(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 * FROM源數(shù)據(jù)表把源數(shù)據(jù)表的記錄添加到目標(biāo)數(shù)據(jù)表

        2.4 更新語句(改)

        UPDATE 數(shù)據(jù)表 SET 字段名=字段值 WHERE 條件表達式

        UPDATE數(shù)據(jù)表 SET 字段1=值1,字段2=值2 ?? 字段n=值n WHERE 條件表達式

        UPDATE數(shù)據(jù)表 SET 字段1=值1,字段2=值2 ?? 字段n=值n

        2.5 刪除語句(刪)

        DELECT FROM 數(shù)據(jù)表 WHERE 條件表達式

        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刪除主鍵

        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)重要的影響，理解索引也是進行數(shù)據(jù)庫性能調(diào)優(yōu)的起點?？紤]如下情況，假設(shè)數(shù)據(jù)庫中一個表有10^6條記錄，DBMS的 頁面大小為4K，并存儲100條記錄。如果沒有索引，查詢將對整個表進行掃描，最壞的情況下，如果所有數(shù)據(jù)頁都不在內(nèi)存，需要讀取10^4個頁面，如果這 10^4個頁面在磁盤上隨機分布，需要進行10^4次I/O，假設(shè)磁盤每次I/O時間為10ms(忽略數(shù)據(jù)傳輸時間)，則總共需要100s(但實際上要好 很多很多)。如果對之建立B-Tree索引，則只需要進行l(wèi)og100(10^6)=3次頁面讀取，最壞情況下耗時30ms。這就是索引帶來的效果，很多 時候，當(dāng)你的應(yīng)用程序進行SQL查詢速度很慢時，應(yīng)該想想是否可以建索引。索引是快速搜索的關(guān)鍵。MySQL索引的建立對于MySQL的高效運行是很重要的。

        3.2 索引的簡單使用

        假如我們創(chuàng)建了一個 mytable表:

        CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT

        NULL ); 我們隨機向里面插入了10000條記錄，其中有一條:5555, admin。

        在查找username="admin"的記錄 SELECT * FROM mytable WHERE username='admin';時，如果在username上已經(jīng)建立了索引，MySQL無須任何掃描，即準(zhǔn)確可找到該記錄。相反，MySQL會掃描 所有記錄，即要查詢10000條記錄。索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。

        MySQL索引類型包括:

        3.2.1普通索引

        這是最基本的索引，它沒有任何限制。它有以下幾種創(chuàng)建方式:

        創(chuàng)建索引

        CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR類型，length可以小于字段實際長度;如果是BLOB和TEXT類型，必須指定 length，下同。

        修改表結(jié)構(gòu)

        ALTER mytable ADD INDEX [indexName] ON (username(length))

        創(chuàng)建表的時候直接指定

        CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

        刪除索引的語法:

        DROP INDEX [indexName] ON mytable;

        3.2.2唯一索引

        它與前面的普通索引類似，不同的就是:索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式:

        創(chuàng)建索引

        CREATE 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)) );

        3.2.3主鍵索引

        它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引:

        CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

        當(dāng)然也可以用 ALTER 命令。記住:一個表只能有一個主鍵。

        3.2.4組合索引

        為了形象地對比單列索引和組合索引，為表添加多個字段:

        CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT

        NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

        為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個索引里:ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

        建表時，usernname長度為 16，這里用 10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。

        如果分別在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低于我們的組合索引。雖然此時有了三個索引，但MySQL只能用到其中的那個它認(rèn)為似乎是最有效率的單列索引。

        建立這樣的組合索引，其實是相當(dāng)于分別建立了下面三組組合索引:

        usernname,city,age usernname,city usernname 為什么沒有 city，age這樣的組合索引呢,這是因為MySQL組合索引“最左前綴”的結(jié)果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都 會用到該組合索引，下面的幾個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 city="鄭州"

        3.2.5建立索引的時機

        到這里我們已經(jīng)學(xué)會了建立索引，那么我們需要在什么情況下建立索引呢,一般來說，在WHERE和JOIN中出現(xiàn)的列需要建立索引，但也不完全如此， 因為MySQL只對<，<=，=，>，>=，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才需建立索引。因為在以通配符%和_開頭作查詢時，MySQL不會使用索引。例如下句會使用索引:

        SELECT * FROM mytable WHERE username like'admin%' 而下句就不會使用:

        SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE時應(yīng)注意以上的區(qū)別。

        3.2.6索引的不足之處

        上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點:

        雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。

        建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴(yán)重，但如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快。

        索引只是提高效率的一個因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語句。

        3.2.7使用索引的注意事項

        使用索引時，有以下一些技巧和注意事項:

        索引不會包含有NULL值的列

        只要列中包含有NULL值都將不會被包含在索引中，復(fù)合索引中只要有一列含有NULL值，那么這一列對于此復(fù)合索引就是無效的。所以我們在數(shù)據(jù)庫設(shè)計時不要讓字段的默認(rèn)值為NULL。

        使用短索引

        對串列進行索引，如果可能應(yīng)該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

        索引列排序

        MySQL查詢只使用一個索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會使用索引的。因此數(shù)據(jù)庫默認(rèn)排序可以符合要求的情況下不要使用排序操作;盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。

        like語句操作

        一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

        不要在列上進行運算

        select * from users where YEAR(adddate)<2007; 將在每個行上進行運算，這將導(dǎo)致索引失效而進行全表掃描，因此我們可以改成

        select * from users where adddate<?2007-01-01;

        不使用NOT IN和<>操作以上，就對其中MySQL索引類型進行了介紹。