MySQL使用总结

获取数据表的大小

获取某个表的大小

按字节

SELECT (data_length+index_length) tablesize
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

以MB为单位

SELECT (data_length+index_length)/power(1024,2) tablesize_mb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

列出所有表的大小

SELECT (data_length+index_length)/power(1024,2) tablesize_mb, table_name FROM information_schema.tables WHERE table_schema='mydb' order by tablesize_mb;

每列打印到单独的行

在每个sql语句的分号前加上\G即可, 这样在列比较多时比较方便观察数据, 这时可以句子结尾可以不加;号

select * from table_name limit 3 \G

显示当前连接信息

显示当前用户为SELECT USER();
显示当前数据库SELECT DATABASE();
显示当前状态, 直接用status;即可, 甚至可以简化为\s, 并且包括了当前用户和数据库的信息

查找表中多余的重复记录（多个字段）

简单说就是多个字段相同的纪录，要找出这样的纪录直接用group by多个字段就好了

SELECT * from table_name 
where name=target_name and color=target_color
group by name, color having count(*) > 1;

比如对于如下的纪录，找出名字和颜色都相同的纪录有哪些

id	name	color
1626	lucy	green
1627	lucy	green
1628	Gimmy	yellow

结果会是

id	name	color
1626	lucy	green

结果name和color都相同的纪录中的一条，因为上面sql的语义就是找出两个字段都相同的一个纪录，其实这也有一个好处，如果你的重复纪录最多只有两条，选出其中一个删掉就能达到去重的效果了
尝试

delete from table_name 
where name=target_name and color=target_color
group by name, color having count(*)>1;

出错(MySQL中)

You can't specify target table 'user_userboxperm' for update in FROM clause

MySQL不能引用内查询的结果对表数据进行修改，所以可以通过把表放入子查询中的方法来更新数据

    delete from table_name 
    where id in (
        select id from (
            select * from table_name where name=target_name and color=target_color
            group by name, color having count(*) > 1
        )as a
    );

想得到所有重复记录的话

SELECT * FROM table_name a
WHERE (a.id, a.name) IN 
(SELECT id, name FROM table_name GROUP BY id, name HAVING COUNT(*) > 1);

这个方法很简单，但是效率比较低，一万条以上记录可能需要不太适用。对于量比较大的表，推荐使用建临时表的方式。

CREATE TABLE temp_table as ( 
    SELECT id, name
    FROM table_name 
    GROUP BY id, name HAVING COUNT(*) >1 
); 

SELECT *　FROM table_name a, temp_table b
WHERE a.id = b.id AND a.name = b.name;

随机查询Ｎ条数据

通常使用rand()函数来实现，但需要注意的是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描，如此一来效率就会很慢。
使用where子句实现：

SELECT * FROM table_name
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM table_name)) )
ORDER BY id LIMIT 1;

使用join实现(获取的Ｎ条数据连续)：

SELECT * FROM table_name AS t1 JOIN 
(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table_name)-(SELECT MIN(id) FROM table_name))
　+(SELECT MIN(id) FROM table_name)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

使用join实现(获取的Ｎ条数据不连续)：

SELECT * FROM table_name AS t1 JOIN 
(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table_name)-(SELECT MIN(id) FROM table_name))+　(SELECT MIN(id) FROM table_name)) AS id from table_name limit 50) AS t2 
on t1.id=t2.id
ORDER BY t1.id LIMIT 1;

查询某个字段是否包含某子串

最简单的方法是使用LIKE来查询

SELECT * FROM  table_name WHERE name LIKE '%keyword%';

可以使用locate, instr函数来代替LIKE查询，速度也会快一些
locate函数的用法是locate(substr, str, pos)
locate、position 和 instr 的差別只是参数的位置不同，同时locate 多一个请始位置的参数外，两者是一样的

SELECT * FROM table_name WHERE locate('keyword', name)>0;
SELECT * FROM table_name WHERE position('keyword' IN name);
SELECT * FROM table_name WHERE instr(name, 'keyword')>0;

删除同一前缀的所有表

通过group_contact将所有目标表添加到drop语句中，表信息来自于information_schema.tables

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE 'prefix_%';

获取全部数据表的建表语句

获得某数据库中已存在的表的建表语句

mysqldump -h localhost -u root -p --no-data --compact DB_NAME

在DB_NAME后面加上以TABLE_NAME(表之间用空格分开)则可以导出具体某几个表。可以用>重定向到一个.sql文件。查看一个表使用

SHOW CREATE TABLE TABLE_NAME;

导入与导出

mysqldump -uroot -p -hlocalhost --single-transaction DB_NAME > backup

使用--single-transaction不对数据库加锁，要加锁可以用--lock-all-tables，还可以加上| gzip > backup.tar.gz进行压缩

从.sql文件导入

mysql -uroot -p -hlocalhost  -Ddb_name < backup.sql