MYSQL 字段操作之修改数据表

1,添加单列可以指定位置，多列不可指定位置。默认为顺位添加至表末行；

添加单列：ALTER TALBLE tab_name ADD[COLUMN]col_name column_definition[FIRST|AFTER col_name]；

mysql> ALTER TABLE user2 ADD pid INT UNSIGNED;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESC USER2;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | NO   | PRI | 0       |       |
| username | varchar(10)      | NO   | UNI | NULL    |       |
| sex      | tinyint(4)       | NO   | UNI | 0       |       |
| uu       | smallint(6)      | YES  | MUL | NULL    |       |
| ii       | tinyint(4)       | YES  |     | NULL    |       |
| pid      | int(10) unsigned | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+

添加多列：ALTER TABLE tab_name ADD[COLUMN]col_name column_definition,......)

删除列：ALTER TABLE tab_name DROP[COLUMN]col_name;

删除多列：ALTER TABLE tab_name DROP[COLUMN]col_name,DROP[COLUMN]col_name......;

| id       | int(11)       | YES  |     | NULL    |       |
| price    | decimal(12,2) | NO   |     | NULL    |       |
| username | varchar(10)   | NO   |     | NULL    |       |
| sex      | tinyint(4)    | NO   |     | 0       |       |
| aa       | tinyint(4)    | NO   |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> ALTER TABLE USER2 DROP aa,DROP pid,DROP price;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint(4)  | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

添加主键约束：ALTER TABLE tab_name ADD[CONSTRAINT[symbol]] PRIMARY KEY[index_type](index_col_name);

mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint(4)  | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> ALTER TABLE user2 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | 0       |       |
| username | varchar(10) | NO   |     | NULL    |       |
| sex      | tinyint(4)  | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

添加唯一约束：ALTER TABLE tab_name ADD[CONSTRAINT[symbol]]UNIQUE[INDEX|KEY][index_name][index_type](index_col_name,......);（添加多个字段名为组合索引）

mysql> INSERT INTO user2 VALUES(1,'EE',1,1,1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user2;
+----+----------+-----+------+------+
| id | username | sex | uu   | ii   |
+----+----------+-----+------+------+
|  1 | EE       |   1 |    1 |    1 |
+----+----------+-----+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO user2 VALUES(2,'EE',1,1,1);
ERROR 1062 (23000): Duplicate entry 'EE' for key 'username'
mysql> INSERT INTO user2 VALUES(2,'rr',1,1,1);
ERROR 1062 (23000): Duplicate entry '1' for key 'sex'
mysql> INSERT INTO user2 VALUES(2,'rr',2,1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'index_name'
mysql>#“MUL”这里为组合索引，‘PRI’,‘UNI’的索引唯一不可重复；同样组合索引也不能为相同值；
mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | 0       |       |
| username | varchar(10) | NO   | UNI | NULL    |       |
| sex      | tinyint(4)  | NO   | UNI | 0       |       |
| uu       | smallint(6) | YES  | MUL | NULL    |       |
| ii       | tinyint(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> select * from user2;
+----+----------+-----+------+------+
| id | username | sex | uu   | ii   |
+----+----------+-----+------+------+
|  1 | EE       |   1 |    1 |    1 |
+----+----------+-----+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO user2 VALUES(2,'rr',2,2,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user2;
+----+----------+-----+------+------+
| id | username | sex | uu   | ii   |
+----+----------+-----+------+------+
|  1 | EE       |   1 |    1 |    1 |
|  2 | rr       |   2 |    2 |    1 |
+----+----------+-----+------+------+
2 rows in set (0.00 sec)

添加外键约束：ALTER TABLE tab_name ADD[CONSTRAINT[symbol]]FOREIGN KEY (index_name) REFERENCES ftab_name(index_name);

mysql> ALTER TABLE user2 ADD FOREIGN KEY(pid) REFERENCES corse(id);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc user2;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(11)          | NO   | PRI | 0       |       |
| username | varchar(10)      | NO   | UNI | NULL    |       |
| sex      | tinyint(4)       | NO   | UNI | 0       |       |
| uu       | smallint(6)      | YES  | MUL | NULL    |       |
| ii       | tinyint(4)       | YES  |     | NULL    |       |
| pid      | int(10) unsigned | YES  | MUL | NULL    |       |
+----------+------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

 

显示添加的所有索引及索引名

mysql> show indexes from user2\G;
*************************** 1. row ***************************
        Table: user2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: user2
   Non_unique: 0
     Key_name: username
 Seq_in_index: 1
  Column_name: username
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: user2
   Non_unique: 0
     Key_name: sex
 Seq_in_index: 1
  Column_name: sex
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: user2
   Non_unique: 0
     Key_name: index_name
 Seq_in_index: 1
  Column_name: uu
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: user2
   Non_unique: 0
     Key_name: index_name
 Seq_in_index: 2
  Column_name: ii
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 6. row ***************************
        Table: user2
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
6 rows in set (0.00 sec)