MySQL 常用查询总结

查看当前连接状态信息

STATUS;

查看可用的存储引擎

SHOW ENGINES;

查看是否自动提交事务

SELECT @@autocommit;

查看当前连接和事务的隔离级别

select @@tx_isolation;
select @@global.tx_isolation;

查看当前数据库

SELECT database();

从当前表结构创建出新表

CREATE TABLE new_tbl LIKE orig_tbl;

查询一个表中含有重复的记录数

SELECT id, count(*) FROM users  group by name having count(*) > 1;

删除重复的记录并保留一个最小的ID

DELETE a FROM user a, user b WHERE a.id > b.id AND a.mail = b.mail;

删除重复的记录并保留一个最大的ID

DELETE a FROM user a, user b WHERE a.id < b.id AND a.mail = b.mail;

查看表信息

SHOW TABLE STATUS LIKE TABLE_NAME

查看索引信息

SHOW INDEX FROM TABLE_NAME

查看执行计划

EXPLAIN SELECT * FROM TABLE_NAME

添加索引

1. CREATE INDEX email_idx ON `TABLE_NAME`;
2. ALTER TABLE TABLE_NAME ADD INDEX email_idx (email);

删除索引

1. DROP INDEX email_idx ON `TABLE_NAME`;
2. ALTER TABLE `TABLE_NAME` DROP INDEX INDEX_NAME;

删除有外键约束的记录

InnoDB不允许删除一个被FOREIGN KEY表约束引用的表，除非设置了FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHEECKS = 0;

替换

UPDATE tbl_name 
SET 
    field_name = REPLACE(field_name,
        string_to_find,
        string_to_replace)
WHERE
    conditions;

许博的博客