MySQL 数据库SQL语句---DDL语句
MariaDB > use mydbMariaDB > show index from tbl1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl1| 0 | PRIMARY| 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tbl1| 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| tbl1| 0 | name | 2 | gender | A | 0 | NULL | NULL | YES| BTREE | | |
| tbl1| 1 | name_2 | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
MariaDB > alter table tbl1 drop index name_2; # 删除索引name_2
Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB >show index from tbl1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl1| 0 | PRIMARY| 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tbl1| 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| tbl1| 0 | name | 2 | gender | A | 0 | NULL | NULL | YES| BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
MariaDB > desc tbl1;
+--------+----------------------+------+-----+---------+----------------+
| Field| Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned| YES| | NULL | |
| gender | enum('F','M') | YES| | M | |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
MariaDB > alter table tbl1 add ClassID TINYINT UNSIGNED NOT NULL; # 新增加一个字段
Query OK, 0 rows affected (0.08 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > desc tbl1;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned| YES| | NULL | |
| gender| enum('F','M') | YES| | M | |
| ClassID | tinyint(3) unsigned| NO | | NULL | |
+---------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
# 使用modify局部修改放到age的行后面
MariaDB > alter table tbl1 modifyClassID TINYINT UNSIGNED NOT NULL after age;
Query OK, 0 rows affected (0.11 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > desc tbl1;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned| YES| | NULL | |
| ClassID | tinyint(3) unsigned| NO | | NULL | |
| gender| enum('F','M') | YES| | M | |
+---------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
页:
[1]