mysql> show databases;
±-------------------+
| Database |
±-------------------+
| aa |
| ab |
| abc |
| ad |
| bb |
| cc |
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
10 rows in set
mysql> show create database cc;
±---------±------------------------------------------------------------+
| Database | Create Database |
±---------±------------------------------------------------------------+
| cc | CREATE DATABASE cc
/*!40100 DEFAULT CHARACTER SET utf8 */ |
±---------±------------------------------------------------------------+
1 row in set
mysql> alter database cc default character set gbk collate gbk_bin;
Query OK, 1 row affected
mysql> show create database cc;
±---------±---------------------------------------------------------------------------+
| Database | Create Database |
±---------±---------------------------------------------------------------------------+
| cc | CREATE DATABASE cc
/*!40100 DEFAULT CHARACTER SET gbk COLLATE gbk_bin */ |
±---------±---------------------------------------------------------------------------+
1 row in set
mysql> alter database cc default character set utf8 collate utf8_bin;
Query OK, 1 row affected
mysql> show create database cc;
±---------±-----------------------------------------------------------------------------+
| Database | Create Database |
±---------±-----------------------------------------------------------------------------+
| cc | CREATE DATABASE cc
/*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
±---------±-----------------------------------------------------------------------------+
1 row in set
mysql> alter database cc default character set utf8;
Query OK, 1 row affected
mysql> show create database cc;
±---------±------------------------------------------------------------+
| Database | Create Database |
±---------±------------------------------------------------------------+
| cc | CREATE DATABASE cc
/*!40100 DEFAULT CHARACTER SET utf8 */ |
±---------±------------------------------------------------------------+
1 row in set
mysql> alter database cc default character set utf8 collate utf8_bin;
Query OK, 1 row affected
mysql> drop database dd;
1008 - Can’t drop database ‘dd’; database doesn’t exist
mysql> drop database if exists aa;
Query OK, 1 row affected
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| ab |
| abc |
| ad |
| bb |
| cc |
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
9 rows in set
mysql> drop database ab;
Query OK, 1 row affected
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| abc |
| ad |
| bb |
| cc |
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
8 rows in set
mysql> alter table xy rename ab_xy;
1046 - No database selected
mysql> use cc;
Database changed
mysql> alter table xy rename ab_xy;
Query OK, 0 rows affected
mysql> show tables;
±-------------+
| Tables_in_cc |
±-------------+
| ab_xy |
±-------------+
1 row in set
mysql> desc ab_xy;
±------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-----------±-----±----±--------±------+
| xh | char(6) | NO | PRI | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| zp | blob | YES | | NULL | |
| bz | text | YES | | NULL | |
±------±-----------±-----±----±--------±------+
8 rows in set
mysql> alter table ab_xy change xh workxh int(8);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ab_xy;
±-------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±-----------±-----±----±--------±------+
| workxh | int(8) | NO | PRI | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| zp | blob | YES | | NULL | |
| bz | text | YES | | NULL | |
±-------±-----------±-----±----±--------±------+
8 rows in set
mysql> alter table ab_xy add age int(4);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ab_xy;
±-------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±-----------±-----±----±--------±------+
| workxh | int(8) | NO | PRI | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| zp | blob | YES | | NULL | |
| bz | text | YES | | NULL | |
| age | int(4) | YES | | NULL | |
±-------±-----------±-----±----±--------±------+
9 rows in set
mysql> alter table ab_xy modify zp varchar(10) first;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ab_xy;
±-------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±------------±-----±----±--------±------+
| zp | varchar(10) | YES | | NULL | |
| workxh | int(8) | NO | PRI | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| bz | text | YES | | NULL | |
| age | int(4) | YES | | NULL | |
±-------±------------±-----±----±--------±------+
9 rows in set
mysql> alter table ab_xy modify workxh int(8) after age;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ab_xy;
±-------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±------------±-----±----±--------±------+
| zp | varchar(10) | YES | | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| bz | text | YES | | NULL | |
| age | int(4) | YES | | NULL | |
| workxh | int(8) | NO | PRI | NULL | |
±-------±------------±-----±----±--------±------+
9 rows in set
mysql> mysql> show databases;
±-------------------+
| Database |
±-------------------+
| aa |
| ab |
| abc |
| ad |
| bb |
| cc |
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
10 rows in set
mysql> show create database cc;
±---------±------------------------------------------------------------+
| Database | Create Database |
±---------±------------------------------------------------------------+
| cc | CREATE DATABASE cc
/*!40100 DEFAULT CHARACTER SET utf8 */ |
±---------±------------------------------------------------------------+
1 row in set
mysql> alter database cc default character set gbk collate gbk_bin;
Query OK, 1 row affected
mysql> show create database cc;
±---------±---------------------------------------------------------------------------+
| Database | Create Database |
±---------±---------------------------------------------------------------------------+
| cc | CREATE DATABASE cc
/*!40100 DEFAULT CHARACTER SET gbk COLLATE gbk_bin */ |
±---------±---------------------------------------------------------------------------+
1 row in set
mysql> alter database cc default character set utf8 collate utf8_bin;
Query OK, 1 row affected
mysql> show create database cc;
±---------±-----------------------------------------------------------------------------+
| Database | Create Database |
±---------±-----------------------------------------------------------------------------+
| cc | CREATE DATABASE cc
/*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
±---------±-----------------------------------------------------------------------------+
1 row in set
mysql> alter database cc default character set utf8;
Query OK, 1 row affected
mysql> show create database cc;
±---------±------------------------------------------------------------+
| Database | Create Database |
±---------±------------------------------------------------------------+
| cc | CREATE DATABASE cc
/*!40100 DEFAULT CHARACTER SET utf8 */ |
±---------±------------------------------------------------------------+
1 row in set
mysql> alter database cc default character set utf8 collate utf8_bin;
Query OK, 1 row affected
mysql> drop database dd;
1008 - Can’t drop database ‘dd’; database doesn’t exist
mysql> drop database if exists aa;
Query OK, 1 row affected
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| ab |
| abc |
| ad |
| bb |
| cc |
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
9 rows in set
mysql> drop database ab;
Query OK, 1 row affected
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| abc |
| ad |
| bb |
| cc |
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
8 rows in set
mysql> alter table xy rename ab_xy;
1046 - No database selected
mysql> use cc;
Database changed
mysql> alter table xy rename ab_xy;
Query OK, 0 rows affected
mysql> show tables;
±-------------+
| Tables_in_cc |
±-------------+
| ab_xy |
±-------------+
1 row in set
mysql> desc ab_xy;
±------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-----------±-----±----±--------±------+
| xh | char(6) | NO | PRI | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| zp | blob | YES | | NULL | |
| bz | text | YES | | NULL | |
±------±-----------±-----±----±--------±------+
8 rows in set
mysql> alter table ab_xy change xh workxh int(8);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ab_xy;
±-------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±-----------±-----±----±--------±------+
| workxh | int(8) | NO | PRI | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| zp | blob | YES | | NULL | |
| bz | text | YES | | NULL | |
±-------±-----------±-----±----±--------±------+
8 rows in set
mysql> alter table ab_xy add age int(4);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ab_xy;
±-------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±-----------±-----±----±--------±------+
| workxh | int(8) | NO | PRI | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| zp | blob | YES | | NULL | |
| bz | text | YES | | NULL | |
| age | int(4) | YES | | NULL | |
±-------±-----------±-----±----±--------±------+
9 rows in set
mysql> alter table ab_xy modify zp varchar(10) first;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ab_xy;
±-------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±------------±-----±----±--------±------+
| zp | varchar(10) | YES | | NULL | |
| workxh | int(8) | NO | PRI | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| bz | text | YES | | NULL | |
| age | int(4) | YES | | NULL | |
±-------±------------±-----±----±--------±------+
9 rows in set
mysql> alter table ab_xy modify workxh int(8) after age;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ab_xy;
±-------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±------------±-----±----±--------±------+
| zp | varchar(10) | YES | | NULL | |
| xm | char(8) | NO | | NULL | |
| zym | char(10) | NO | | NULL | |
| xb | tinyint(4) | NO | | NULL | |
| csrq | date | NO | | NULL | |
| zxf | tinyint(4) | NO | | NULL | |
| bz | text | YES | | NULL | |
| age | int(4) | YES | | NULL | |
| workxh | int(8) | NO | PRI | NULL | |
±-------±------------±-----±----±--------±------+
9 rows in set
mysql>