1. 建表
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql> ALTER TABLE test_db COMMENT = 'test time table'
-> ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_db |
+----------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE test_db MODIFY COLUMN update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test_db;
Empty set (0.01 sec)
2. 描述表结构
mysql> DESCRIBE test_db;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| test_status | varchar(24) | YES | | NULL | |
| create_time | datetime | YES | | CURRENT_TIMESTAMP | |
| update_time | datetime | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)
3. 查看建表语句
mysql> SHOW CREATE TABLE test_db;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_db | CREATE TABLE `test_db` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test_status` varchar(24) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='test time table' |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
4. 存储sql
# 1. 存储整个库
mysqldump -uroot -p test > /root/test/mysql-test/db/test.sql
# 2. 存储库的结构
mysqldump -uroot -p -d test > /root/test/mysql-test/db/test_structure.sql
# 3. 存储表的结构
mysqldump -uroot -p -d test test_db > /root/test/mysql-test/db/test_tb_structure.sql
# 4. 存储表
mysqldump -uroot -p test test_db > /root/test/mysql-test/db/test_tb.sql
5. 使用sql生成表和数据
在新的地址
create database test;
use test;
show tables;
source /xxxx/xxx.sql
mysql> source test_db.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
6. 删除数据表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_db |
| test_db1 |
| test_db11 |
+----------------+
3 rows in set (0.00 sec)
mysql> drop table test_db11;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_db |
| test_db1 |
+----------------+
2 rows in set (0.00 sec)
mysql>