学习ALTER TABLE删除、添加和修改字段和类型
mysql> alter table alter_tab01 drop col01; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
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)
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)
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)
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)
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)
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
【MySQL牛客】10.获取所有非manager的员工emp_no
网友评论