原创

MySQL(五):MySQL 常用又不易记住的 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 limitoffset

    -- 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很大会影响性能
正文到此结束
本文目录