mysql表定義語法詳細介紹-mysql教程-PHP中文網

mysql表定義語法詳細介紹

首先我們都知道只有成功建立資料庫後，才能建立資料表，資料表是欄位的集合，在表中資料以行和列的格式儲存。

建立表格

MySQL 使用 CREATE TABLE 建立表格。其中有多個選擇，主要由表格建立定義（create definition）、表格選項定義（table options）和區分選項（partition options）等內容構成。

表建立定義

由表格列的名字、列的定義集可能的一個空值宣告、一個完整性約束或表索引項目組成，表索引項主要定義表的索引、主鍵、外鍵等。

（線上學習影片教學推薦：mysql影片教學）

#文法結構

CREATE[TEMPORARY]TABLE tbl_name
(
    字段名|数据类型[列级完整性约束条件][默认值]
    [,字段名2 数据类型[列级完整性约束条件][默认值]]
    [,....]
    [,表级完整性约束条件]
)[ENGINE=引擎类型]

登入後複製

Example:
新建一個客戶資訊

mysql> USE mysql_test
Database changed
mysql> CRATE TABLE customers
    ->(
    -> cust_id INT NOT NULL AUTO_INCREMENT,
    -> cust_name CHAR(50) NOT NULL,
    -> cust_sex CHAR(1) NOT NULL DEFAULT 0,
    -> cust_address CHAR(50) NULL
    -> cust_contact CHAR(50) NULL
    -> PRIMARY KEY(CUST_ID)
    ->)
Query OK, 0 rows affected(0.11 sec)

登入後複製

臨時表與持久表

TEMPORARY：表示臨時表，如果不選用則位持久表

TEMPORARY：表示臨時表，如果不選用則位持久表。

持久表一直存在，多個使用者或應用程式可同時使用持久性表，如果只需暫時存放資料可新增TEMPORARY 關鍵字，臨時表只能對建立它的使用者可見，斷開資料庫連接時，表會自動清除。

資料型別

資料型別指系統中所允許的資料的型別。每列都應有適當的資料類型，來限製或允許該列的資料。建表時必須為每列指定正確的資料型別及資料長度（CHAR(50)）

#MySQL 主要資料型別:

##數值型別：整數int 、浮點double、布林bool

日期和時間類型：日期型、時間戳timestamp、時間型time

字串型別：定長字元型別char、可變長字元型別varchrar

空間資料類型:單一幾何類型GEOMETRY等

關鍵字AUTO_INCREMENT

AUTO_INCREMENT: 資料型別為整型的資料列設定自增屬性（ i）,從目前指或1 開始，表中只能有一個AUTO_INCREMENT。

當一個表格列被指定為AUTO_INCREMENT 後，其值可被覆蓋，即可在表格資料插入語句中為該列指定一個值（必須唯一），則該值將取代系統自動產生的值，後續增量基於該插入的值

指定預設值

DEFAULT:用於指定MySQL在未給值的情況下預設的值（DEFAULT 0 )

如果未指定預設值，則自動為其分配一個值，如若該列可取值NULL，則預設NULL，若定義NOT NULL，則預設取決於該列的類型：

一個沒有宣告AUTO_INCREMENT 列為數字類型，預設為0

一個AUTO_INCREMENT 欄位預設為順序中的下一個值

對於除TIMESTAMP 以外的日期和時間類型，預設為類型適當的'零'值

對於表中第一個TIMESTAMP 列，預設值為當前日期和時間

NULL值

##NULL ：沒有值或缺值，允許NULL的列，插入行時可以不給該列的值；不允許NULL值的列，則該列必須有資料

NULL 和 ''是不對等的NOT NULL 列中允許'' 不允許NULL

主鍵

PRIMARY KEY :指定主鍵，主鍵必須唯一且不能為NULL，如果是單列，值必須唯一，如果是組合列，則其組合的值必須唯一

更新表

透過使用ALTER TABLE 來修改資料庫

ADD[COLUMN]：新增表列，可增加列使用逗號分隔即可

Example:

mysql> ALTER TABLE mysqle_test.customers
    -> ADD COLUMN cust_city char(10) NOT NULL DEFAULT&#39;ShenZhen&#39; AFTER cust_sex;
Query OK,0 rows affected(0.61 sec)
Records:0 Duplicates:0 Warning:0

登入後複製

AFTER：將新增的列新增至cut_sexl 列之後

FIRST：將新增的列新增到表的第一列

若使用上述關鍵字則將新增的欄位加入表最後

類似的可以使用ADDPRIMARY KEY 、ADDFOREIGN KEY 、ADD INDEX 新增對應的主鍵、外鍵、索引

CHANGE[COLUMN]: 修改表中列的名稱或資料類型，可修改多列使用逗號分隔即可

mysql> ALTER TABLE mysqle_test.customers
    -> CHANGE COLUMN cust_sex sex char(1) NULL DEFAULT &#39;M&#39;
Query OK,0 rows affected(0.66 sec)
Records:0 Duplicates:0 Warning:0

登入後複製

如果將資料類型更換，可能會遺失該列原有的數據，如果視圖改變的資料型別於原有的資料型別不相容，則SQL指令不會執行，且拋出錯誤。

再相容的情況下，該列的資料可能會被截斷，如：一列的資料類型為varchart(10),改為char(1),則該列中的資料'ShenZhen'會變為'S '###

ALTER [COLUMN]: 修改或删除指定列的默认值

mysql> ALTER TABLE mysqle_test.customers
    -> ALTER COLUMN cust_city SET  DEFAULT &#39;ShangHai&#39;
Query OK,0 rows affected(0.36 sec)
Records:0 Duplicates:0 Warning:0

登入後複製

MODIFY [COLUMN]: 修改指定列的数据类型，通过 'FIRST' 或 'AFTER' 修改列的位置

mysql> ALTER TABLE mysqle_test.customers
    -> MODIFY COLUMN cust_name char(30)  FIRST
Query OK,0 rows affected(0.20 sec)
Records:0 Duplicates:0 Warning:0

登入後複製

DROP [COLUMN]: 删除列，该列所有数据一并删除

mysql> ALTER TABLE mysqle_test.customers
    -> DROP COLUMN cust_city
Query OK,0 rows affected(0.42 sec)
Records:0 Duplicates:0 Warning:0

登入後複製

同样可使用 DROP PRIMARY KEY 、DROP FOREIGN KEY、DROP INDEX 删除对应的主键、外键、索引

RENAME[TO]:表重命名

mysql> ALTER TABLE mysqle_test.customers
    -> RENAME TOQuery OK,0 rows affected(0.42 sec)

登入後複製

重命名表

除了 ALTER TABLE 中的 RENAME TO 修改表名，还可通过 RENAME TABLE 来修改单张和多张表（以逗号分隔）

mysql> RENAME TABLE mysql_test.back.customers TO mysqle_test.customers

登入後複製

删除表

DROP[TEMPORARY]TABLE[IF EXISTS]删除一个已存在的表,可以删除多张表，前提操作人必须有权限，但是操作人在该张表上的权限不会被删除

查看表

SHOW [FULL] TABLES [{FROM|IN}db_name] [LIKE'pattern'|WHERE expr]：显示指定数据库中所有表名

Example：

mysql> USE mysql_testDatabase changedmysql> SHOW TABLES:
 Tables_in_mysql_test
 customers 1 row in set <0.01 sec>

登入後複製

SHOW [FULL] COLUMNS {FROM|IN}tb_name[{FROM|IN}db_name] 或 {DESCRIBE|DESC} tbl_name[col_name|wild]: 显示指定数据库表结构。

MySQL 支持使用 DESCRIBE 代替 SHOW COLUMNS FROM 来查看表结构

Example:

mysql> DESC mysql_test.custormes
Field         Type       Null key  Default Extra
cust_id       int<11>    NO   PRI  NULL    auto_increment
cust_name     char<50>   NO        Null
cust_sex      int<1>     NO        0

3 row in set <1.56 sec>

登入後複製