mysql 基本操作 三

1.alter

创建测试表

MariaDB [jason]> create table testalter_tbl(i int,c char(1));
Query OK, 0 rows affected (0.08 sec)

MariaDB [jason]> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

删除 i 字段

MariaDB [jason]> alter table testalter_tbl drop i;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

添加字段

MariaDB [jason]> alter table testalter_tbl add i int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

将字段添加在指定位置

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> alter table testalter_tbl add i int first
    -> ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [jason]> alter table testalter_tbl drop i;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> alter table testalter_tbl add i int after c;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改字段类型及名称

MariaDB [jason]> alter table testalter_tbl modify c char(10);
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c     | char(10) | YES  |     | NULL    |       |
| i     | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

用change 修改 change 旧名字  新名字 字段类型

MariaDB [jason]> alter table testalter_tbl change i j bigint;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [jason]> alter table testalter_tbl change j j int;
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c     | char(10) | YES  |     | NULL    |       |
| j     | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

alter 对null 和默认值的影响

Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | 100     |       |
+-------+------------+------+-----+---------+-------+

修改字段默认值

MariaDB [jason]> alter table testalter_tbl alter j set default 1000;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | 1000    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除默认值

MariaDB [jason]> alter table testalter_tbl alter j drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show columns from testalter_tbl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

修改表的引擎

MariaDB [jason]> SHOW TABLE STATUS LIKE 'testalter_tbl' \G;
*************************** 1. row ***************************
           Name: testalter_tbl
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-02-13 20:50:40
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [jason]> alter table testalter_tbl engine=myisam;
Query OK, 0 rows affected (0.03 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> SHOW TABLE STATUS LIKE 'testalter_tbl' \G;
*************************** 1. row ***************************
           Name: testalter_tbl
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 13792273858822143
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-02-13 20:59:30
    Update_time: 2019-02-13 20:59:30
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

修改表名

MariaDB [jason]> alter table testalter_tbl rename to testalter;

2.索引

添加，删除索引

MariaDB [jason]> alter table testalter add index index_cj(c,j); //index_cj 是索引名字，一个索引中可以包含>=1 个列
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show index from testalter;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| testalter |          1 | index_cj |            1 | c           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| testalter |          1 | index_cj |            2 | j           | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

MariaDB [jason]> drop index index_cj on testalter; 
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

添加，删除 unique 索引

unique 列中 值的组合必须唯一

MariaDB [jason]> alter table testalter add unique uq_c(c);
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show index from testalter;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| testalter |          0 | uq_c     |            1 | c           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MariaDB [jason]> drop index uq_c on testalter;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

添加，删除主键

MariaDB [jason]> alter table testalter add primary key(c,j);
Query OK, 0 rows affected (0.00 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show index from testalter;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| testalter |          0 | PRIMARY  |            1 | c           | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| testalter |          0 | PRIMARY  |            2 | j           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MariaDB [jason]> alter table testalter drop primary key;
Query OK, 0 rows affected (0.00 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [jason]> show index from testalter;
Empty set (0.00 sec)