MySQL-資料表操作

環境CentOS8/mysql-8.0.23

1.創建資料表

語法：

CREATE TABLE [IF NOT EXISTS]表名（
欄位1資料型別 [約束調件] [默認值]，
欄位2資料型別 [約束調件] [默認值]，
欄位3資料型別 [約束調件] [默認值]，
...
[表約束條件]
）；

簡單語法：

create table 表名（
列名1 資料型別（寬度） 約束條件，
列名2 資料型別（寬度） 約束條件，
列名3 資料型別（寬度） 約束條件，
）

在創建資料表時，必須指定資料表的表名稱，表名稱在win系統不區分大小寫，在Linux作業系統上區分，如果需要在Linux作業系統上不區分大小寫，則需要在組態檔my.cnf添加：

lower_case_table_name=1

示例：
在名稱為xiaohuagou創建資料表：

mysql> create table if not exists xiaohuagou_pixel( 
    -> id int(6),
    -> recommend char(20),
    -> follow varchar(100)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

到這里表創建完成，雖然這里沒有報錯，但是有一個警告資訊（1 warning（0.01 sec））使用SQL陳述句進行查看MySQL的警告資訊

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

是因為在MySQL的8.x版本中，不再推薦為int型別指定顯示長度，并在未來的版本中可能去掉這樣的語法，因此這里去掉int型別的顯示長度，重新創建名稱為xiaohuagou——pixel的資料表

mysql> create table xiaohuagou_pixel(
    -> id int,
    -> t_threshold varchar(20),
    -> t_edge varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

這里不再顯示警告資訊，因此在MySQL 8.x版本中創建資料表時，建議不要為int型別指定顯示長度，接下來再次查看xiaohuagou資料庫中存在的資料表

mysql> show tables;
+----------------------+
| Tables_in_xiaohuagou |
+----------------------+
| xiaohuagou_pixel     |
+----------------------+
1 row in set (0.00 sec)

2.創建資料表時指定主鍵

在MySQL中創建資料表時，可以為資料表指定主鍵（主碼），包含表中的一列或者多列，能夠唯一標識表中的一行記錄，主鍵列的資料必須唯一且非空，主鍵可以分為單列主鍵和多列主鍵，

2.1單列主鍵

只包含資料表中的一個欄位，可以在定義資料列的同時指定主鍵，也可以在定義完資料表中的所有列的同時指定主鍵

• 在定義列的同時指定主鍵
  語法：欄位 資料型別 primary key [默認值]
  示例：

mysql> create table xhg_data(
    -> id int primary key,
    -> gray_t varchar(200),
    -> threshold_t varchar(1000)
    -> );
Query OK, 0 rows affected (0.01 sec)

• 定義完資料表中的所有列之后指定主鍵
  語法：[constratint 約束條件名] primary key [欄位名]
  示例：

mysql> create table xhg_l(
    -> id int,
    -> gray_t varchar(200),
    -> threshold_t varchar(1000),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

• 查看表：desc table name;

mysql> desc xhg_data;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| gray_t      | varchar(200)  | YES  |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 復合主鍵

mysql> create table danhuatang(
    -> id int,
    -> gray_ts varchar(200),
    -> threshold varchar(1000),
    -> bottom_t varchar(1000),
    -> primary key(id,gray_ts)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc danhuatang;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | NO   | PRI | NULL    |       |
| gray_ts   | varchar(200)  | NO   | PRI | NULL    |       |
| threshold | varchar(1000) | YES  |     | NULL    |       |
| bottom_t  | varchar(1000) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

• 追加主鍵：alter table table_name add primary key(filed,...);
• 追加多列聯合（復合）主鍵：alter table table_name add primary key(number,course);

mysql> desc xiaohuagou_pixel;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int          | YES  |     | NULL    |       |
| t_threshold | varchar(20)  | YES  |     | NULL    |       |
| t_edge      | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table xiaohuagou_pixel add primary key(id,t_edge);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xiaohuagou_pixel;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int          | NO   | PRI | NULL    |       |
| t_threshold | varchar(20)  | YES  |     | NULL    |       |
| t_edge      | varchar(100) | NO   | PRI | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 追加主鍵:alter table tab_name modify 欄位primary key;

mysql> alter table xhg_l modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| gray_t      | varchar(200)  | YES  |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 追加主鍵：alter table table_name add constraint filed primary key(filed);

mysql> alter table xhg_l add constraint gray_t primary key(gray_t);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   |     | NULL    |       |
| gray_t      | varchar(200)  | NO   | PRI | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 追加主鍵：alter table table_name add constraint primary key(filed);

mysql> alter table xhg_l add constraint primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| gray_t      | varchar(200)  | NO   |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 洗掉主鍵：alter table table_name drop primary key;

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| gray_t      | varchar(200)  | YES  |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table xhg_l drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   |     | NULL    |       |
| gray_t      | varchar(200)  | YES  |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

2.2外鍵

• 定義：外鍵可以關聯資料庫中的兩張表，對應的是資料庫中的參照完整性，一張表的外鍵可以為空也可以非空，則每一個外鍵的值必須等于另一張表的主鍵的某個值，一張表的外鍵可以不是本表的主鍵，但其對應著另一張表的主鍵，在一張表中定義了外鍵之后不允許洗掉另一張表中具有關聯關系的行資料
• 作用：用來和其他表建立聯系用的
• 個數：一個表可以有多個外鍵
  由外鍵引申出兩個概念，分別是主表（父表）和從表（子表）
• 主表：兩個表具有關聯關系時，關聯欄位中主鍵所在的表為主表
• 從表：兩個表具有關聯關系時，關聯欄位中外鍵所在的表為從表
  語法：[constraint 外鍵名] foreign key filed1[filed2,flied3,…]
  語法：references 主表名 主鍵列1[主鍵列2，主鍵列3，…]
• 外鍵名：定義外鍵是為資料表指定的外鍵名稱，在同一張資料表中，外鍵的名稱必須是唯一，也就是說，在同一張資料表中，不能有相同名稱的外鍵名稱
• FOREIGN KEY：指定外鍵包含在那些欄位，可以是同一欄位,也可以是多個欄位的組合，
• REFERENCES:指定關聯的主表名稱
• 主表名：主鍵所在的表名稱
• 主鍵列：主表中定主鍵欄位，可以是一個欄位，也可以是多個欄位的組合
• 創建表時增加外鍵語法：constraint 外鍵名 foreign key (從表列名)references 主表名（主表列）；
• 創建表后添加外鍵語法：alter table table_name(從表)add constraint外鍵名（也叫約束名）foreign key(外鍵列（從表）)references 表名（主表）（列名（主表的列））；
• 示例：

mysql> show tables;
Empty set (0.00 sec)

mysql> create table dht(
    -> id int primary key,
    -> name varchar(50),
    -> number varchar(100)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> create table xhm(
    -> id int primary key,
    -> name_t varchar(50),
    -> number_t varchar(100),
    -> pixel int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table xhm add constraint zoo foreign key(pixel) references dht(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

被參考的表不能直接洗掉，要先洗掉參考的表；
以上都的操作都是物理外鍵，資料庫級別的外鍵不建議使用，避免資料庫過多的困擾

• 查詢多表資訊：desc table_name;desc table_name;

附錄MySQL資料型別