数据库基本操作杂记(二)

1、查看表结构

方法一

DESCRIBE [database_name.]table_name

database_name.可选，表明查询指定数据库中的表结构。若不指定则查询当前数据库下的表结构。

方法二

SHOW CREATE TABLE table_name

2、导出备份数据

mysqldump --user='username' -p database_name [table_name] > /path/of/the/xxx.sql

3、修改表结构

-- 增加列
ALTER TABLE table_name ADD COLUMN column_name column_type

-- 修改列
ALTER TABLE table_name CHANGE column_old_name column_new_name column_type

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name

4、重命名表

RENAME TABLE table_old_name TO table_new_name

5、查看sql是怎么执行的

EXPLAIN you sql

比如：

EXPLAIN SELECT * FROM birdwatchers.humans WHERE name_last = 'Hollar'

-- 未建索引
+-----+------------ +--------+---------------+------------+---------+-------+------+------+
| id  | select_type | table  | possible_keys | key        | key_len | ref   | rows |Extra |
+-----+------------ +--------+---------------+------------+---------+-------+------+------+
| 1   | SAMPLE      | humans |   NULL        | NULL       |  NULL   | NULL  | 4    |      | 
+-----+------------ +--------+---------------+------------+---------+-------+------+------+


-- 建了索引
+-----+------------ +--------+---------------+------------+---------+-------+------+------+
| id  | select_type | table  | possible_keys | key        | key_len | ref   | rows |Extra |
+-----+------------ +--------+---------------+------------+---------+-------+------+------+
| 1   | SAMPLE      | humans | human_names   | human_names| 78      | const | 1    |      |
+-----+------------ +--------+---------------+------------+---------+-------+------+------+

EXPLAIN对sql性能分析有用，主要看possible_keys 及 key两列，众所周知索引可以加快查询速度。