next up previous contents
Next: MODIFY 修改 Up: ALTER 語法 Previous: ALTER 簡介   Contents   DYWANG_HOME

ADD 新增

  1. 登入 mariadb。
    [root@kvm8 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe
    mariadb 10.3.11-MariaDB
    mycli 1.20.1
    Chat: https://gitter.im/dbcli/mycli
    Mail: https://groups.google.com/forum/#!forum/mycli-users
    Home: http://mycli.net
    Thanks to the contributor - Lewis Peckover
    
  2. 使用資料庫 dyw。
    mariadb root@localhost:(none)> use dyw;                                         
    You are now connected to database "dyw" as user "root"
    Time: 0.001s
    
  3. 查看資料表 tbl 的欄位格式。
    mariadb root@localhost:dyw> show columns from tbl;                              
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
    | title           | varchar(100) | NO   |     | <null>  |                |
    | author          | varchar(40)  | NO   |     | <null>  |                |
    | submission_date | date         | YES  |     | <null>  |                |
    +-----------------+--------------+------+-----+---------+----------------+
    
    4 rows in set
    Time: 0.023s
    
  4. ADD 在資料表 tbl 最前面增加一個欄位 ti,格式為 TINYINT。
    mariadb root@localhost:dyw> alter table tbl add ti tinyint first;               
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 0 rows affected
    Time: 0.038s
    
  5. 再查看資料表 tbl 的欄位格式。
    mariadb root@localhost:dyw> show columns from tbl;                              
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | ti              | tinyint(4)   | YES  |     | <null>  |                |
    | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
    | title           | varchar(100) | NO   |     | <null>  |                |
    | author          | varchar(40)  | NO   |     | <null>  |                |
    | submission_date | date         | YES  |     | <null>  |                |
    +-----------------+--------------+------+-----+---------+----------------+
    
    5 rows in set
    Time: 0.028s
    
  6. ADD 在資料表 tbl 欄位 id 後新加一個欄位 c,格式為一個字元 char(1)。
    mariadb root@localhost:dyw> alter table tbl add c char(1) after id;             
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 0 rows affected
    Time: 0.161s
    
  7. 再查看資料表 tbl 的欄位格式。
    mariadb root@localhost:dyw> show columns from tbl;                              
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | ti              | tinyint(4)   | YES  |     | <null>  |                |
    | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
    | c               | char(1)      | YES  |     | <null>  |                |
    | title           | varchar(100) | NO   |     | <null>  |                |
    | author          | varchar(40)  | NO   |     | <null>  |                |
    | submission_date | date         | YES  |     | <null>  |                |
    +-----------------+--------------+------+-----+---------+----------------+
    
    6 rows in set
    Time: 0.028s
    



De-Yu Wang 2020-04-07