MySQL Crash Course #13# Chapter 21. Creating and Manipulating Tables

標籤：style   指令碼   add   move   explain   comment   engine   example   default   

之前 manipulate 表裡的資料，現在則是 manipulate 表本身。

INDEX

• 建立多列構成的主鍵
• 自動成長的規定
• 查看上一次插入的自增 id 
• 盡量用預設值替代 NULL
• 外鍵不可以跨引擎

• 添加欄位與刪除欄位 & 定義外鍵

• 複雜表結構的修改

• 刪除表與修改表名

非常工整的 。 。模範指令碼：

CREATE TABLE customers(  cust_id      int       NOT NULL AUTO_INCREMENT,  cust_name    char(50)  NOT NULL ,  cust_address char(50)  NULL ,  cust_city    char(50)  NULL ,  cust_state   char(5)   NULL ,  cust_zip     char(10)  NULL ,  cust_country char(50)  NULL ,  cust_contact char(50)  NULL ,  cust_email   char(255) NULL ,  PRIMARY KEY (cust_id)) ENGINE=InnoDB;

To create a primary key made up of multiple columns

Simply specify the column names as a comma delimited list, as seen in this example:

CREATE TABLE orderitems(  order_num  int          NOT NULL ,  order_item int          NOT NULL ,  prod_id    char(10)     NOT NULL ,  quantity   int          NOT NULL ,  item_price decimal(8,2) NOT NULL ,  PRIMARY KEY (order_num, order_item)) ENGINE=InnoDB;

自動成長的規定

CREATE TABLE `manga` (  `manga_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘漫畫id‘,  `manga_name` varchar(40) NOT NULL COMMENT ‘漫畫名字‘,  `manga_discription` varchar(120) DEFAULT NULL COMMENT ‘漫畫描述‘,  `manga_status` tinyint(4) NOT NULL DEFAULT ‘0‘ COMMENT ‘漫畫描述‘,  PRIMARY KEY (`manga_id`)) ENGINE=InnoDB AUTO_INCREMENT=1012 DEFAULT CHARSET=utf8 COMMENT=‘漫畫表‘  

每個表只允許有一個自增列，並且它必須被索引（例如，把它設定為主鍵）

查看上一次插入的自增 id ，

必須是自增的！自訂插入的不算！

mysql> INSERT INTO manga    -> (manga_name) VALUES (‘what‘);Query OK, 1 row affected (0.00 sec)mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|             1012 |+------------------+1 row in set (0.00 sec)

Using DEFAULT Instead of NULL Values

Many database developers use DEFAULT values instead of NULL columns, especially in columns that will be used in calculations or data groupings.

Foreign Keys Can‘t Span Engines 

There is one big downside to mixing engine types. Foreign keys (used to enforce referential integrity, as explained in Chapter 1, "Understanding SQL") cannot span engines. That is, a table using one engine cannot have a foreign key referring to a table that uses another engine.

 添加欄位與刪除欄位 & 定義外鍵

ALTER TABLE vendorsADD vend_phone CHAR(20);

ALTER TABLE VendorsDROP COLUMN vend_phone;

修改表這一操作經常被用來定義外鍵：

ALTER TABLE orderitemsADD CONSTRAINT fk_orderitems_ordersFOREIGN KEY (order_num) REFERENCES orders (order_num);ALTER TABLE orderitemsADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id)REFERENCES products (prod_id);ALTER TABLE ordersADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id)REFERENCES customers (cust_id);ALTER TABLE productsADD CONSTRAINT fk_products_vendorsFOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

文法：ALTER TABLE table_name ADD CONSTRAINT fk_id FOREIGN KEY (外鍵欄位名) REFERENCES 外表表明（外表中對應的主鍵欄位名）;

FK_ID 是外鍵的名稱。更多外鍵相關的內容請參考 外鍵約束

 複雜表結構的修改

Complex table structure changes usually require a manual move process involving these steps:

1. Create a new table with the new column layout.
2. Use the INSERT SELECT statement (see Chapter 19, "Inserting Data," for details of this statement) to copy the data from the old table to the new table. Use conversion functions and calculated fields, if needed.
3. Verify that the new table contains the desired data.
4. Rename the old table (or delete it, if you are really brave).
5. Rename the new table with the name previously used by the old table.
6. Re-create any triggers, stored procedures, indexes, and foreign keys as needed.

 刪除表與修改表名

DROP TABLE customers2;

RENAME TABLE backup_customers TO customers,             backup_vendors TO vendors,             backup_products TO products;

 

MySQL Crash Course #13# Chapter 21. Creating and Manipulating Tables