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)

create table進階學習(一)
2013-01-08
create table進階學習(三)
2013-01-10
create table進階學習(四)
2013-01-22
【AIX 命令學習】mkvg 命令
2010-09-06
AI
create table進階學習系列(八)
2013-01-22
create table進階學習系列(五)
2013-01-22
create table進階學習系列(七)
2013-01-23
create table進階學習系列(九)
2013-01-23
asmcmd命令學習
2015-04-02
ASM
nohup命令學習
2013-04-18
AIX 命令學習
2013-03-20
AI
ip命令學習
2024-05-28
【AIX 命令學習】migratepv 命令
2010-09-06
AI
create table進階學習(二)_全域性臨時表_global temporary table
2013-01-09
Shell學習【test命令】
2019-04-10
dos命令的學習
2021-07-26
Linux 命令學習
2018-06-26
Linux
virsh命令學習（一）
2024-04-28
mysql的ALTER TABLE命令
2020-11-20
MySql
【AIX 命令學習】errpt 命令示例！
2010-08-30
AI
Bootstrap Table 學習筆記之列引數（一）
2017-09-20
boot筆記
create table進階學習系列(十一)之cluster
2013-01-24
學習一個 Linux 命令：shutdown 命令
2021-03-30
Linux
【AIX 命令學習】lsvg 命令引數使用！
2010-09-06
AISVG
tail命令學習例項
2021-01-06
AI
lsof命令學習筆記
2020-11-13
筆記
Redis 學習筆記命令
2018-05-28
Redis筆記
Git命令學習筆記
2017-05-08
Git筆記
TCL學習之info命令
2017-04-28
linux命令學習——ps
2016-05-11
Linux
Linux命令學習（一）
2011-03-22
Linux
全面學習oracle flashback特性(2.1)--Flashback Table之RECYCLEBIN
2008-07-03
Oracle
容器Docker學習系列五~命令學習history,save,import
2018-08-27
DockerImport
容器Docker學習系列五~命令學習history,save, import
2018-08-19
DockerImport
linux學習筆記---一些命令學習
2020-11-30
Linux筆記
【AIX 命令學習】errpt 命令引數使用！
2010-08-30
AI
NGUI學習筆記（六）：ScrollView、Grid和Table
2017-04-20
NGUI筆記View
Git 學習之命令詳解
2019-12-26
Git

MySQL-ALTER TABLE命令學習[20180503]

相關文章