工作中经常用到的MySQL功能整理

粗体字段	说明
TABLENAME	数据库表名，真正使用时替换真实即可
DBNAME	数据库名称，真正使用时替换真实即可
COLUMNNAME	字段名称，真正使用时替换真实即可
INDEXNAME	索引名称，真正使用时替换真实即可

增加

1、复制表结构：
CREATE TABLE NEW_TABLENAME LIKE TABLENAME;
添加旧表记录到新创建的表
INSERT INTO NEW_TABLENAME SELECT * FROM
TABLENAME WHERE COLUMNNAME= value;

查询

1、数据库登录：mysql -u用户名 -p密码 -h 远程主机 数据库名
如：mysql -uroot -proot -h 192.168.30.130 Test
简单说明：
（1）如果本机登录可以去掉-h 192.168.30.130
（2）如果数据库的表很多导致登录很慢，可以加上-A参数不预读表结构信息，如mysql -uroot -proot -h 192.168.30.130 -A Test
2、查看数据库表状态与结构：
SHOW TABLE STATUS LIKE ‘TABLENAME’ \G;
SHOW CREATE TABLE TABLENAME\G;
3、显示当前表：
SHOW TABLES;
4、显示表索引：
SHOW INDEX FROM TABLENAME;
5、解释SQL查询语句执行过程：
EXPLAIN SELECT * FROM TABLENAME WHERE COLUMNNAME= value;
6、显示正在执行的SQL语句：
SHOW PROCESSLIST;
显示完整SQL SHOW FULL PROCESSLIST;
7、查找匹配表
SHOW TABLES LIKE ‘TABLENAME%’;
8、查询数据库下所有表名、类型、存储引擎
SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = ‘DBNAME’ ORDER BY table_name;
9、显示视图：
show table status where comment=‘view’;
10、显示视图创建语句：
SHOW CREATE VIEW VIEWNAME\G;
11、查询重复记录：
SELECT COLUMNNAME,COUNT(*) as count from TABLENAME group by COLUMNNAME having count>1;
12、慢日志分析：
MySQL配置：/etc/my.cnf
慢查询日志：/var/lib/mysql/mysql-slow.log

修改

1、添加字段：
ALTER TABLE TABLENAME ADD COLUMNNAME tinyint(1) NOT NULL DEFAULT 0;
2、添加索引：
单个字段索引：
ALTER TABLE TABLENAME ADD INDEX INDEXNAME(COLUMNNAME);
组合字段索引：
ALTER TABLE TABLENAME ADD INDEX
INDEXNAME(COLUMNNAME1,COLUMNNAME2)
3、删除字段：
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME;
4、删除索引：
ALTER TABLE TABLENAME DROP INDEX idx_name;
5、数据库表重命名：
ALTER TABLE OLD_TABLENAME RENAME NEW_TABLENAME;
6、修改字段类型：
ALTER TABLE TABLENAME MODIFY COLUMNNAME varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘’;
7、修改字段名称：
ALTER TABLE TABLENAME CHANGE OLD_COLUMNNAME NEW_COLUMNNAME varchar(255) COLLATE utf8_bin NOT NULL DEFAULT ‘’;
8、修改表的存储引擎（存储引擎MyISAM、InnoDB）：
ALTER TABLE TABLENAME ENGINE=MyISAM;
9、删除自增长主键并且重新添加主键：
ALTER TABLE TABLENAME change id id bigint(20); //先删除自增长
ALTER TABLE TABLENAME DROP PRIMARY KEY;
ALTER TABLE TABLENAME add primary key(id);
10、修改表字段值：
UPDATE TABLENAME SET COLUMNNAME=value2 WHERE COLUMNNAME=value1;

删除

1、从表中删除满足条件的数据：
DELETE FROM TABLENAME WHERE COLUMNNAME= value;
2、清空表：
TRUNCATE TABLENAME;

数据导入导出

1、将满足条件的数据导出到文件：
SELECT * FROM TABLENAME WHERE COLUMNNAME= value INTO OUTFILE ‘/tmp/data.csv’ character set utf8 FIELDS TERMINATED BY ‘,’ ENCLOSED BY “’”;
2、将导出数据导入到数据库表：
LOAD DATA LOCAL INFILE ‘/tmp/data.csv’ INTO TABLE TABLENAME character set utf8 FIELDS TERMINATED BY ‘,’ ENCLOSED BY “’”;
3、导出数据库表到文件：
mysqldump -u root -proot DBNAME TABLENAME1 TABLENAME2 >/tmp//test.sql
4、执行SQL脚本：
mysql -u root -proot DBNAME </tmp//test.sql
5、如果导入的SQL语句中存在中文则在语句头增加SET NAMES UTF8;
6、只导出数据库表结构（加-d）
mysqldump -u root -proot -d DBNAME TABLENAME1 TABLENAME2 >/tmp//test.sql