mysql> drop database if exists mysql_test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| northwind |
| performance_schema |
| pubs |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> create schema if not exists mysql_test
-> default character set utf8
-> default collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_test |
| northwind |
| performance_schema |
| pubs |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> show create database mysql_test;
+------------+---------------------------------------------------------------------+
| Database | Create Database |
+------------+---------------------------------------------------------------------+
| mysql_test | CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter schema mysql_test
-> default character set gbk
-> default collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database mysql_test;
+------------+--------------------------------------------------------------------+
| Database | Create Database |
+------------+--------------------------------------------------------------------+
| mysql_test | CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET gbk */ |
+------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop schema if exists db1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show schemas like 'mysql%';
+-------------------+
| Database (mysql%) |
+-------------------+
| mysql |
| mysql_test |
+-------------------+
2 rows in set (0.00 sec)
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
...
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
...
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
...
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
...
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
...
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86 | | Yes | 1 |
...
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
...
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
...
+--------------------------+----------+-----+---------+----------+---------+
197 rows in set (0.00 sec)
mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)
mysql> show collation like 'gb2312%';
+-------------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86 | | Yes | 1 |
+-------------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)
mysql> show collation like 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
...
+--------------------------+---------+-----+---------+----------+---------+
45 rows in set (0.00 sec)
mysql> use mysql_test;
Database changed
mysql> create table if not exists customers
-> (
-> cust_id int not null auto_increment,
-> cust_name char(50) not null,
-> cust_sex char(1) not null default 0,
-> cust_address char(50) null,
-> cust_contact char(50) null,
-> primary key(cust_id)
-> )
-> engine=innodb;
Query OK, 0 rows affected (0.27 sec)
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> show create table customers;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers | CREATE TABLE `customers` (
`cust_id` int(11) NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_sex` char(1) NOT NULL DEFAULT '0',
`cust_address` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop table if exists customers;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> create table if not exists customers
-> (
-> cust_id int not null auto_increment,
-> cust_name char(50) not null,
-> cust_sex char(1) not null default 0,
-> cust_address char(50) null,
-> cust_contact char(50) null,
-> primary key(cust_id)
-> )
-> engine=innodb default charset=gbk;
Query OK, 0 rows affected (0.19 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> alter table mysql_test.customers
-> add column cust_city char(10) not null default 'Wuhan' after cust_sex;
Query OK, 0 rows affected (1.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| cust_sex | char(1) | NO | | 0 | |
| cust_city | char(10) | NO | | Wuhan | |
| cust_address | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> alter table mysql_test.customers
-> change column cust_sex sex char(1) null default 'M';
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Wuhan | |
| cust_address | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
mysql> alter table mysql_test.customers
-> alter column cust_city set default 'Beijing';
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> alter table mysql_test.customers
-> modify column cust_name char(20) first;
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> alter table mysql_test.customers
-> drop column cust_contact;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> alter table mysql_test.customers
-> rename to mysql_test.backup_customers;
Query OK, 0 rows affected (0.16 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| backup_customers |
+----------------------+
1 row in set (0.00 sec)
mysql> rename table mysql_test.backup_customers to mysql_test.customers;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> create table if not exists mysql_test.customers_copy
-> like mysql_test.customers;
Query OK, 0 rows affected (0.20 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
| customers_copy |
+----------------------+
2 rows in set (0.00 sec)
mysql> drop table if exists mysql_test.customers_copy;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> show columns in mysql_test.customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
mysql> show columns from mysql_test.customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe mysql_test.customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> exit
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| northwind |
| performance_schema |
| pubs |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> create schema if not exists mysql_test
-> default character set utf8
-> default collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_test |
| northwind |
| performance_schema |
| pubs |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> show create database mysql_test;
+------------+---------------------------------------------------------------------+
| Database | Create Database |
+------------+---------------------------------------------------------------------+
| mysql_test | CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter schema mysql_test
-> default character set gbk
-> default collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database mysql_test;
+------------+--------------------------------------------------------------------+
| Database | Create Database |
+------------+--------------------------------------------------------------------+
| mysql_test | CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET gbk */ |
+------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop schema if exists db1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show schemas like 'mysql%';
+-------------------+
| Database (mysql%) |
+-------------------+
| mysql |
| mysql_test |
+-------------------+
2 rows in set (0.00 sec)
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
...
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
...
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
...
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
...
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
...
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86 | | Yes | 1 |
...
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
...
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
...
+--------------------------+----------+-----+---------+----------+---------+
197 rows in set (0.00 sec)
mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)
mysql> show collation like 'gb2312%';
+-------------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86 | | Yes | 1 |
+-------------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)
mysql> show collation like 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
...
+--------------------------+---------+-----+---------+----------+---------+
45 rows in set (0.00 sec)
mysql> use mysql_test;
Database changed
mysql> create table if not exists customers
-> (
-> cust_id int not null auto_increment,
-> cust_name char(50) not null,
-> cust_sex char(1) not null default 0,
-> cust_address char(50) null,
-> cust_contact char(50) null,
-> primary key(cust_id)
-> )
-> engine=innodb;
Query OK, 0 rows affected (0.27 sec)
mysql> show tables;
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> show create table customers;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers | CREATE TABLE `customers` (
`cust_id` int(11) NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_sex` char(1) NOT NULL DEFAULT '0',
`cust_address` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop table if exists customers;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> create table if not exists customers
-> (
-> cust_id int not null auto_increment,
-> cust_name char(50) not null,
-> cust_sex char(1) not null default 0,
-> cust_address char(50) null,
-> cust_contact char(50) null,
-> primary key(cust_id)
-> )
-> engine=innodb default charset=gbk;
Query OK, 0 rows affected (0.19 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> alter table mysql_test.customers
-> add column cust_city char(10) not null default 'Wuhan' after cust_sex;
Query OK, 0 rows affected (1.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| cust_sex | char(1) | NO | | 0 | |
| cust_city | char(10) | NO | | Wuhan | |
| cust_address | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> alter table mysql_test.customers
-> change column cust_sex sex char(1) null default 'M';
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Wuhan | |
| cust_address | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
mysql> alter table mysql_test.customers
-> alter column cust_city set default 'Beijing';
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> alter table mysql_test.customers
-> modify column cust_name char(20) first;
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> alter table mysql_test.customers
-> drop column cust_contact;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> alter table mysql_test.customers
-> rename to mysql_test.backup_customers;
Query OK, 0 rows affected (0.16 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| backup_customers |
+----------------------+
1 row in set (0.00 sec)
mysql> rename table mysql_test.backup_customers to mysql_test.customers;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> create table if not exists mysql_test.customers_copy
-> like mysql_test.customers;
Query OK, 0 rows affected (0.20 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
| customers_copy |
+----------------------+
2 rows in set (0.00 sec)
mysql> drop table if exists mysql_test.customers_copy;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers |
+----------------------+
1 row in set (0.00 sec)
mysql> show columns in mysql_test.customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
mysql> show columns from mysql_test.customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe mysql_test.customers;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| cust_name | char(20) | YES | | NULL | |
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(1) | YES | | M | |
| cust_city | char(10) | NO | | Beijing | |
| cust_address | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> exit