建立表格

• 建立基本表格的語法

  CREATE TABLE 表格名稱
  (欄位定義, ...)
  [{ENGINE|TYPE} [=] 儲存引擎名稱]
  [CHARACTER　SET [=] 字元集名稱]
  [COLLATE [=] collation名稱]
  

• MySQL規定一個表格中至少要有一個欄位
• IF NOT EXISTS: 預防發生表格已存在的錯誤
• 設定表格中的欄位時，至少要明確的決定欄位的名稱與型態，如果有一個以上欄位，要使用逗號隔開，範例

  CREATE TABLE IF NOT EXISTS addressbook (
    name      VARCHAR(20),
    tel       VARCHAR(20),
    address   VARCHAR(20),
    birthdate DATE
  )
  

1. 表格屬性
   1. MySQL會使用伺服器預設的儲存引擎作為表格的儲存引擎，字元集與collation會使用資料庫預設的設定
   2. SHOW ENGINE: 不同應用的儲存引擎查詢
   3. 「CHARCTER SET」也可以使用比較簡短的「CHARSET」，設定範例

      CREATE TABLE IF NOT EXISTS addressbook (
        name      VARCHAR(20),
        tel       VARCHAR(20),
        address   VARCHAR(20),
        birthdate DATE
      )ENGINE = InnoDB
      CHARACTER SET = utf8
      COLLATE = utf8_unicode_ci
      

2. 字串欄位 屬性
   1. 「非二進位制、non-binary」字串可以額外設定字元集與collation
   2. 範例

      CREATE TABLE IF NOT EXISTS addressbook (
        name      VARCHAR(20),  CHARSET big5,
        tel       VARCHAR(20),
        address   VARCHAR(20),
        birthdate DATE
      )
      

3. 數值欄位屬性
   1. 專用的屬性設定: UNSIGNED, ZEROFILL, AUTO_INCREMENT
      1. UNSIGNED:
      2. ZEROFILL: 補零
      3. AUTO_INCREMENT: 與索引有關
4. 通用欄位屬性
   1. NOT NULL: 禁止某個欄位儲存「NULL」值，查詢表格欄位資訊時，是在「Null」欄位用「YES」或「NO」來表示
   1. DEFAULT: 設定欄位的預設值，預設值只能是「一個明確的值」
   1. 「BLOB」與「TEXT」欄位型態不可以使用[DEFAULT]關鍵字指定預設值
   1. 不能與其它的欄位設定造成衝突
   1. 指定的預設值要符合欄位型態
5. TIMESTAMP欄位型態與預設值
6. 使用其它表格建立一個新表格
   1. 省略欄位定義的工作，新表格會使用原有表格的欄位名稱與定義，而且在查詢敘述中傳回的資料，會直接新增到新建立的表格中
   2. 使用欄位定義來設定新表格的欄位型態與其它屬性
   3. 可以加入查詢敘述中沒有的欄位
   4. 範例

      CREATE TABLE cityoftaiwan
      SELECT Name, Population
      FROM world.city
      WHERE CountryCode='TWN'
      

   5. 只需要借用一個已經存在的表格欄位定義，可是並不需要紀錄資料，範例

      CREATE TABLE 表格名稱
      { LIKE 表格名稱 | (LIKE 表格名稱)}
      

7. 建立暫存表格
   1. CREATE [TEMPORARY] TABLE 表格名稱
      1. 「TEMPORARY」表格是每一個用戶端專屬的表格，用戶端離線後，MySQL就會自動刪除這些表格
      2. 因為「TEMPORARY」表格是用戶端專屬的表格，其它用戶端不能使用，所以不同的用戶端，使用同樣名稱建立「TEMPORARY」表格也沒有關係
      3. 「TEMPORARY」表格名稱可以跟資料庫中的表格名稱一樣
      4. 使用「ALTER TABLE」修改「TEMPORARY」表格名稱

修改表格

1. 增加欄位
   1. 語法

      ADD [COLUMN] 欄位定義 [FIRST | AFTER 欄位名稱]
      

   2. 沒有指定新增欄位的位置，MySQL會把這個欄位放在最後一個
   3. 搭配使用「FIRST」關鍵字，把新增的欄位放在第一個
   4. 使用「AFTER」關鍵字，指定新增的欄位要放在哪一個欄位後面
   5. 語法

      ALTER TABLE 
      ADD newcolumn int FIRST/AFTER two
      

   6. 增加多個欄位，都會放在最後面的位置，範例

      ALTER TABLE mytable
      ADD (newcolumn int, newcolumn2 iny)
      

2. 修改欄位
   1. CHANGE: 可以修改欄位的名稱與定義，語法

      CHANGE [COLUMN] 就欄位名稱 心蘭位定義 [FIRST | AFTER 欄位名稱]
      

   2. MODIFY: 只能修改欄位的定義，不能修改欄位名稱，語法

      MODIFY [COLUMN] 欄位定義 [FIRST | AFTER 欄位名稱]
      

   3. 刪除欄位，語法

      DROP [COLUMN] 欄位名稱
      

3. 修改表格名稱
   1. ALTER TABLE:
   2. RENAME TABLE: 可以一次修改多的表格名稱
   3. 語法

      ALTER TABLE 舊表格名稱 RENAME [TO] 新表格名稱
      RENAME TABLE 就表格名稱 TO 新表格名稱[, ...]
      

刪除表格

1. 「DROP TABLE」敘述執行刪除表格的工作時，MySQL並不會再次跟你確認是否真的要刪除，而是真的就直接刪除了，表格儲存的紀錄資料當然也不見了，沒以辦法復原。
2. 語法

   DROP TABLE [IF EXISTS] 表格名稱 [,...,]
   

索引介紹

可以利用「索引、index」預防你的資料出現問題，也可以增加查詢與維護資料的效率。

1. 主索引鍵(primary key): 一個表格通常會有一個，而且只能有一個，不可以儲「NULL」值，如ID, ...
2. 唯一索引(unique index): 設定為唯一索引的欄位值不可以重複，但是可以儲「NULL」值，如email,...
3. 非唯一索引(non-unique index): 只是用來增加查詢與維護資料效率的索引

建立索引

• 可以把建立索引的定義，加在「CREATE TABLE」敘述中
• 使用表格一陣子以後，才發覺有建立索引的需求，在這樣的情況下，你可以使用「ALTER TABLE」或「CREATE INDEX」建立需要的索引

1. 建立表格時，建立索引
   1. 語法一:

      CREATE 表格名稱(
        id     INT UNSIGNED PRIMARY KEY,
        email  VARCHAR(36) UNIQUE KEY
      

   2. 語法二:

      CREATE 表格名稱(
        id     INT UNSIGNED,
        email  VARCHAR(36),
        PRIMARY KEY(id),
        UNIQUE KEY(email)
        INDEX(name, tel, ...)
      

   3. 有時候你只想要為一個字串型態欄位的部份資料建立索引，或是指定建立的索引資料，是要依照由小到大，還是由大到小排列
      • 範例，只使用 address 欄位的錢五個字元製作索引，而且由大到小排列。(「CHAR」、「VARCHAR」、「BINARY」與「VARBINARY」型態的欄位可以指定製作索引的長度)

      CREATE TABLE 表格名稱(
        address  VARCHAR(80),
        INDEX (address (5) DESC)
      )
      

   4. 一個表格使用的儲存引擎是「MEMORY」的話，建立索引的時候還可以額外指定索引使用的「演算法、algorithm」
2. 修改表格時，建立索引
   1. 使用「ALTER TABLE」敘述建立 主索引鍵
   2. 範例

      ALTER TABLE addressbook
      ADD PRIMARY KEY (id)
      
      CREATE UNIQUE INDEX email_index
      ON addressbook (email)
      CREATE INDEX name_tel_index
      ON addressbook (name, tel)
      CREATE INDEX address_index
      ON addressbook (address (5) DESX)
      

3. 使用「CREATE INDEX」建立索引
   1. 使用「CREATE INDEX」敘述，只能 建立唯一索引與 一般索引
   2. 特別注意主索引鍵與唯一索引這兩種索引
      1. 如果表格中已經有紀錄了，而且你想要建立一個主索引鍵時，有可能會發生錯誤，例如選擇資料裡有NULL卻要建立 primary key就會錯誤
      2. 為一個已經存在、而且已經有紀錄的表格建立唯一索引時，也有可能會發生錯誤，例如有重複的資料卻要建立 unique key

索引的名稱

1. 在「CREATE TABLE」或是「ALTER TABLE」敘述中建立索引的話，你可以為建立的索引取一個名稱
2. 如果建立索引的時候沒有指定索引名稱，MySQL會幫你取一個，索引的名稱就是欄位名稱
3. 使用「CREATE INDEX」建立索引的時候，就一定要指定一個索引名稱

   CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名稱
   

刪除索引

1. 使用「ALTER TABLE」敘述可以一次刪除多個索引
2. 語法

   ALTER TABLE 表格名稱
   DROP PRIMARY KEY,
   DROP {INDEX | KEY} 牽引名稱1, 
   DROP {INDEX | KEY} 牽引名稱2
   

3. 「DROP INDEX」敘述一次只能刪除一個索引

   DROP INDEX email ON addressbook;
   

數值欄位型態與AUTO_INCREMENT

1. 如果資料表中的每一筆紀錄都需要一個遞增的數值編號，你可以選擇整數型態的欄位後，再使用「AUTO_INCREMENT」欄位屬性
2. 「AUTO_INCREMENT」欄位在你刪除紀錄以後，也不會幫你重新使用已經用過的編號
3. 一個表格只能有一個「AUTO_INCREMENT」欄位，而且要為它建立一個索引
4. 只有整數型態才可以使用「AUTO_INCREMENT」欄位屬性
5. 如果編號已經到欄位型態的最大範圍，如果再執行新增的敘述，就會造成「Duplicate entry ’65535′ for key ‘欄位名稱’」的錯誤

查詢表格與索引資訊

1. 表格相關資訊
   1. 一個資料庫中有哪一些表格: SHOW TABLES FROM 資料庫名稱
   2. 可以使用「字串樣式」設定表格名稱的條件： SHOW TABLES FROM 資料庫名稱 LIKE %y
   3. 特別的資料庫「information_schema」，有一個表格叫作「TABLES」，它儲存所有MySQL資料庫中的表格相關資訊，「TABLES」
   4. SHOW CREATE TABLE 資料庫名稱.表格名稱
2. 索引相關資訊
   1. SHOW INDEX FROM 表格名稱