MySQL系列(5)： 常用SQL,压缩备份还原

整理下以前记录的一些常用但一时想不起的 SQL 语句，有些极其简单，有些则需要思考下。

常规 SQL

1. 根据其它表的条件更新：

   update users u, score s SET s.`isConsumer` = 1 
       where s.`userId` = u.`aqUserid` and u.`aqRoleId` = 16;

2. 查询存在重复的数据

   select id,name,phone_number from tbu_user 
       group by phone_number 
       having count(phone_number) > 1;

3. 删除表中重复数据，只保留一条

   DELETE FROM users_bank 
       WHERE id NOT IN (
           SELECT minid FROM (
               SELECT MIN(id) AS minid FROM users_bank GROUP BY userId) b);

4. 删除同一张表里查出的数据, 使用中间表

   DELETE FROM tbu_user WHERE id IN (
       SELECT temp.id FROM (
           SELECT id FROM tbu_user u WHERE u.`id` NOT IN (
               SELECT user_id FROM tbu_user_result)) temp);
   
   DELETE FROM business WHERE userId IN (
       SELECT t.userId FROM (
           SELECT b.`userId` FROM business b,users u 
               WHERE b.`userId` = u.`aqUserid` AND u.`aqRoleId` != 15) t);

5. 内联接实现分组取最大ID或最大时间的值

   SELECT * FROM tbs_reg_count t WHERE t.id IN (SELECT MAX(id)
   FROM tbs_reg_count t WHERE create_datetime 
       BETWEEN DATE_FORMAT('2018-11-23 00:00:00','%Y-%m-%d 00:00:00') 
           AND DATE_FORMAT('2018-11-24 00:00:00','%Y-%m-%d 23:59:59')
   GROUP BY sys_user_id, DATE_FORMAT(create_datetime,'%Y-%d-%m'));

6. 查时间范围是否有交集或包含

   SELECT * FROM tbs_price WHERE sys_user_id = 1 AND
   (((start_datetime < '2018-11-21 10:00:00') AND (start_datetime > '2018-11-18 10:00:00')) OR
   ((end_datetime > '2018-11-18 10:00:00') AND (end_datetime < '2018-11-21 10:00:00')) OR
   ((start_datetime > '2018-11-18 10:00:00') AND (end_datetime) < '2018-11-21 10:00:00'));

7. 避免 sum() 函数 NPE 问题
   当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意 NPE 问题。

   SELECT IF(ISNULL(SUM(pay_money)),0,SUM(pay_money)) FROM tbu_auth_order;

8. 插入从其它表查询到的数据

   insert into sys_role_menu(roleid,roleName,menuid,menuName) 
       select sys_role.`id`,sys_role.`roleName`,sys_menu.`menu_id`,sys_menu.`menu_name` from sys_role,sys_menu;

NOT IN

注意：MySQL 的 NOT IN 查询值中不能存在 NULL 值，否则结果会一直为空。大 NOT IN 的查询子句里增加非空判断。

select * from student where name not in (
    select name from student where name is not null and score < 60)

GROUP BY

-- 分组查询：group by

-- 单独使用
SELECT * FROM film_actor GROUP BY actor_id;

-- 与聚合函数一起使用,分组后计算，取ID值最小即第一条记录显示
SELECT actor_id, COUNT(film_id) FROM film_actor GROUP BY actor_id;

-- 与 HAVING 一起使用，"having"条件表达式用于限制输出结果，只有满足条件才显示。功能是分组后过滤
SELECT actor_id, COUNT(film_id) FROM film_actor GROUP BY actor_id HAVING COUNT(film_id) > 30;

-- 与 group_concat()函数一起使用，会把每个分组中指定字段值都显示出来
SELECT actor_id,GROUP_CONCAT(film_id),COUNT(film_id) FROM film_actor GROUP BY actor_id HAVING COUNT(film_id) > 40;

-- 与with rollup一起使用,会在所有记录的最后加上一条记录，这条记录是上面所有记录的总和
SELECT actor_id,GROUP_CONCAT(film_id),COUNT(film_id) FROM film_actor GROUP BY actor_id  WITH ROLLUP;

压缩备份与还原

1. 压缩备份：导出整个数据库

   mysqldump -u root -p 123 > xxxx.sql
   
   -- 压缩导出
   mysqldump -u root -p 123| gzip > db_name2017070802.sql.gz
   
   -- 导出表数据,db_name:数据库名；sys_user:表名
   mysqldump -u root -p db_name sys_user > sys_user.sql

2. 备份还原

   -- 压缩 sql 文件还原
   gunzip < anqi2017070802.sql.gz | mysql -uroot -p db_name
   
   -- sql 文件还原
   mysql -u admin -p 123 < /data/mysql_backup/xxx.sql

3. 导出数据表结构

   mysqldump -uroot -p -d db_name > db_name.sql

4. 导入数据库表结构

   mysql -uroot database < db_name.sql
   
   mysql>use db_name
   mysql>source db_name.sql

5. 导出到 XML 文件

   mysql -u user_name -xml -e 'SELECT * FROM table_name' > table_name.xml

其它注意项

1. count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
2. 不要使用 count(列名) 或 count(常量) 来替代 count(*)，count(*) 是SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL 和 非NULL 无关。
   count(*) 会统计值为NULL的行，而 count(列名) 不会统计此列为 NULL 值的行。
3. 在 varchar 字段上建立索引时，必须指定索引长度，通常情况下，长度为 20 的索引，区分度会高达 90% 以上。
   可以使用 count(distinct left(列名, 索引长度))/count(*) 计算区分度来确定索引长度。
4. 利用延迟关联或者子查询优化超多分页场景
   MySQL 的分页查询关键字：limit offset,rows;
   MySQL并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
   正例：先快速定位需要获取的 id 段，然后再关联：SELECT a.* FROM tb1 a, (select id from tb1 where … LIMIT 100000,20) b where a.id=b.id
5. MySQL limit 与 offset

   -- limit,下例，从第 2 行数据开始读取 1 行。注意，MySQL 行索引(偏移起始位)是从 0 开始。
   -- limit m, n; 表示从第 m 行索引位置开始读，读 n 条数据。
   select * from order where user_id = 100 limit 2,1;
   
   -- offset,下例，指偏移 1 行数据，读取 2行。
   select * from order where user_id = 100 limit 2 offset 1;

相关参数

1. MySQL的Limit详解
2. mysql查询时，offset过大影响性能的原因与优化方法
3. mysql 证明为什么用limit时，offset很大会影响性能