MySQL-ALTER TABLE命令學習[20180503]

請點..頭像發表於2018-05-03

學習ALTER TABLE刪除、新增和修改欄位和型別

CREATE TABLE alter_tab01(

id int,

col01 char(20))

engin=InnoDB default charset=utf8;

刪除欄位

ALTER TABLE <tab_name> DROP <col_name>;

mysql> alter table alter_tab01 drop col01;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

新增欄位

ALTER TABLE <tab_name> ADD <col_name> TYPE;

ALTER TABLE <tab_name> ADD <col_name> TYPE [ FIRST| AFTER <col_name>];

ALTER TABLE <tab_name> ADD <col_name> TYPE NOT NULL;

mysql> alter table alter_tab01 add col01 char(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table alter_tab01 add col02 char(20) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table alter_tab01 add col03 char(20) after id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table alter_tab01 add col04 char(20) not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show columns from alter_tab01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col02 | char(20) | YES  |     | NULL    |       |
| id    | int(11)  | YES  |     | NULL    |       |
| col03 | char(20) | YES  |     | NULL    |       |
| col01 | char(20) | YES  |     | NULL    |       |
| col04 | char(20) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改欄位型別及名稱

ALTER TABLE <tab_name> MODIFY <col_name> TYPE;

ALTER TABLE <tab_name> CHANGE <old_name> <new_name> TYPE;

mysql> alter table alter_tab01 modify col02 varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table alter_tab01 change col02 new_col02 char(2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show columns from alter_tab01;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| new_col02 | char(2)  | YES  |     | NULL    |       |
| id        | int(11)  | YES  |     | NULL    |       |
| col03     | char(20) | YES  |     | NULL    |       |
| col01     | char(20) | YES  |     | NULL    |       |
| col04     | char(20) | NO   |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改欄位NOT NULL約束與預設值

ALTER TABLE <tab_name> MODIFY <col_name> TYPE NOT NULL DEFAULT <預設值>;

mysql> alter table alter_tab01 modify id bigint not null default 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show columns from alter_tab01;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| new_col02 | char(2)    | YES  |     | NULL    |       |
| id        | bigint(20) | NO   |     | 1       |       |
| col03     | char(20)   | YES  |     | NULL    |       |
| col01     | char(20)   | YES  |     | NULL    |       |
| col04     | char(20)   | NO   |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改欄位的預設值

ALTER TABLE <tab_name> ALTER <col_name> SET DEFAULT <預設值>;

ALTER TABLE <tab_name> ALTER <col_name> DROP DEFAULT;

mysql> alter table alter_tab01 alter new_col02 set default `01`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show columns from alter_tab01;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| new_col02 | char(2)    | YES  |     | 01      |       |
| id        | bigint(20) | NO   |     | 1       |       |
| col03     | char(20)   | YES  |     | NULL    |       |
| col01     | char(20)   | YES  |     | NULL    |       |
| col04     | char(20)   | NO   |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
 
mysql> alter table alter_tab01 alter new_col02 drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show columns from alter_tab01;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| new_col02 | char(2)    | YES  |     | NULL    |       |
| id        | bigint(20) | NO   |     | 1       |       |
| col03     | char(20)   | YES  |     | NULL    |       |
| col01     | char(20)   | YES  |     | NULL    |       |
| col04     | char(20)   | NO   |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改表的儲存引擎

mysql> show table status like `alter_tab01`G
*************************** 1. row ***************************
           Name: alter_tab01
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
Auto_increment: NULL
    Create_time: 2018-05-03 16:11:39
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)
 
mysql> alter table alter_tab01 engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show table status like `alter_tab01`G
*************************** 1. row ***************************
           Name: alter_tab01
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
Avg_row_length: 0
    Data_length: 0
Max_data_length: 54887620458577919
   Index_length: 1024
      Data_free: 0
Auto_increment: NULL
    Create_time: 2018-05-03 16:12:35
    Update_time: 2018-05-03 16:12:35
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

修改表的名稱

ALTER TABLE <tab_name> RENAME TO <new_name>;

mysql> alter table alter_tab01 rename to alter_tab02;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show table status like `alter_tab02`G
*************************** 1. row ***************************
           Name: alter_tab02
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
Auto_increment: NULL
    Create_time: 2018-05-03 16:14:02
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

mysql的ALTER TABLE命令
2020-11-20
MySql
Linux 命令學習
2018-06-26
Linux
ip命令學習
2024-05-28
[20180503]檢視提示使用索引.txt
2018-05-07
索引
[20180503]珅與分隔符.txt
2018-05-03
學習一個 Linux 命令：shutdown 命令
2021-03-30
Linux
Shell學習【test命令】
2019-04-10
virsh命令學習（一）
2024-04-28
dos命令的學習
2021-07-26
linux基礎命令學習
2019-02-13
Linux
Linux命令學習(重定向)
2018-04-07
Linux
Redis 學習筆記命令
2018-05-28
Redis筆記
python學習命令總結
2018-08-30
Python
lsof命令學習筆記
2020-11-13
筆記
tail命令學習例項
2021-01-06
AI
linux學習筆記---一些命令學習
2020-11-30
Linux筆記
容器Docker學習系列五~命令學習history,save, import
2018-08-19
DockerImport
容器Docker學習系列五~命令學習history,save,import
2018-08-27
DockerImport
學習Linux基本命令（一）
2018-11-27
Linux
linux進階命令學習一
2019-02-14
Linux
Git 學習之命令詳解
2019-12-26
Git
【Linux學習筆記】reboot命令
2020-10-07
Linux筆記boot
git學習常用命令
2019-05-10
Git
Shells命令列學習筆記
2019-05-04
命令列筆記
Linux | 常用命令學習
2018-03-30
Linux
Linux 常用命令學習
2021-05-21
Linux
學習一個 Linux 命令：pstree
2021-05-03
Linux
PacVim：一個學習 vim 命令的命令列遊戲
2018-06-12
命令列遊戲
Hive學習之常用互動命令
2018-11-30
Hive
Linux的常用命令學習
2019-02-26
Linux
Linux命令和shell指令碼學習
2018-07-20
Linux指令碼
[20200224]windows命令學習筆記.txt
2020-02-24
Windows筆記
git高階命令學習記錄
2020-11-02
Git
shell和bash指令碼命令學習
2024-06-11
指令碼
Linux學習之iostat命令詳解
2020-12-07
LinuxiOS
學習Linux必須掌握的命令!
2020-12-07
Linux
[20201123]timeout 命令學習筆記.txt
2020-11-23
筆記
每天學習一個Linux命令-目錄
2018-11-19
Linux

MySQL-ALTER TABLE命令學習[20180503]

相關文章