MySQL常用功能整理

一、查询语句
1、字符串截取：

update sys_area set short_name = substring_index(short_name, '地区', 1) where type = 3 and short_name is not null and short_name like '%地区'

substring(str, pos); substring(str, pos, len)

2、rowNum实现

SELECT @rownum:=@rownum+1 rownum, CollectSn From
(SELECT @rownum:=0,bbgmain.* FROM qbdb.bbgmain WHERE collectsn!='' ORDER BY collectsn limit 10) t

3、获取分组中最新的信息
mysql执行顺序：
写的顺序：select … from… where…. group by… having… order by..
执行顺序：from… where…group by… having…. select … order by…
所以group by到select时只取到分组里的第一条信息
解决方法：
- where+group by（对小组进行排序）

select * from `test` where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id ) order by `date` desc

• 从form返回的数据下手脚（即用子查询）

select * from (select * from `test` order by `date` desc) `temp`  group by category_id order by `date` desc

5、查询重复字段

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录（多个字段）
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

6、MySQL order null 0 - 把null和0(零)排在最后

SELECT `vcenter_ip`, `status`, `sla_id` FROM vm_list ORDER BY sla_id is NULL, sla_id = 0, sla_id ASC;

7、获取上一次插入的自增id

select LAST_INSERT_ID()

二、数据库更新
1、根据查询出来的值更新表多个字段

UPDATE student D
  LEFT JOIN (SELECT 
        B.studentId,
                SUM(B.score) AS s_sum,
                ROUND(AVG(B.score),1) AS s_avg
           FROM score B
          WHERE b.examTime >= '2015-03-10'
          GROUP BY B.studentId) C
    ON (C.studentId = D.id)

   SET D.score_sum = c.s_sum,
       D.score_avg = c.s_avg
 WHERE D.id = ''

三、数据库插入
1、批量插入

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)   VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

四、数据库功能
2、数据库定时：

SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;

3、表结构分析

select * from go_run_record procedure analyse();

4、存储过程

DROP PROCEDURE IF EXISTS `p_update_praise_week`;
DELIMITER ;;
CREATE PROCEDURE p_update_praise_week()
BEGIN
        DECLARE w_user_id VARCHAR(64);
        DECLARE w_count INT;
        DECLARE h_user_id VARCHAR(64);
        DECLARE stops INT DEFAULT 0;

        DECLARE cur_account CURSOR FOR select a.user_id,a.count from go_user_praise_week a where a.del_flag = 0;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stops=1;
    OPEN cur_account;     

    read_loop: LOOP
                SET stops = 0;
        FETCH NEXT from cur_account INTO w_user_id,w_count;
                if stops = 1 then
                    leave read_loop;
                end if;

                select a.user_id into h_user_id from go_user_praise_history a where a.user_id = w_user_id and a.del_flag = 0;

                if h_user_id is null then
                    insert go_user_praise_history values(w_user_id,w_count,now(),'0',now());
                ELSE
                    update go_user_praise_history set `count` = `count` + w_count where user_id = w_user_id;
                end if;

    END LOOP;
    CLOSE cur_account;
END
;
DELIMITER ;

执行动态sql

--存储过程名和参数，参数中in表示传入参数，out标示传出参数，inout表示传入传出参数

create procedure p_procedurecode(in sumdate varchar(10))   
begin
     declare v_sql varchar(500);    --需要执行的SQL语句
     declare sym varchar(6);

     declare var1 varchar(20);
     declare var2 varchar(70);
     declare var3 integer;

     --定义游标遍历时，作为判断是否遍历完全部记录的标记
     declare no_more_departments integer DEFAULT 0;     

     --定义游标名字为C_RESULT 
     DECLARE C_RESULT CURSOR FOR
             SELECT barcode,barname,barnum FROM tmp_table;

    --声明当游标遍历完全部记录后将标志变量置成某个值
     DECLARE CONTINUE HANDLER FOR NOT FOUND
             SET no_more_departments=1;

     set sym=substring(sumdate,1,6);     --截取字符串，并将其赋值给一个遍历

     --连接字符串构成完整SQL语句，动态SQL执行后的结果记录集，在MySQL中无法获取，因此需要转变思路将其放置到一个临时表中（注意代码中的写法）。一般写法如下：

     --     'Create TEMPORARY Table   表名（Select的查询语句）;
     set v_sql= concat('Create TEMPORARY Table tmp_table(select aa as aacode,bb as aaname,count(cc) as ccnum from h',sym,' where substring(dd,1,8)=''',sumdate,''' group by aa,bb)');

     set @v_sql=v_sql;   --注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
     prepare stmt from @v_sql;  --预处理需要执行的动态SQL，其中stmt是一个变量
     EXECUTE stmt;      --执行SQL语句
     deallocate prepare stmt;     --释放掉预处理段

     OPEN C_RESULT;       --打开之前定义的游标
     REPEAT                      --循环语句的关键词
           FETCH C_RESULT INTO VAR1, VAR2, VAR3;   --取出每条记录并赋值给相关变量，注意顺序

          --执行查询语句，并将获得的值付给一个变量 @oldaacode（注意如果以@开头的变量可以不用通过declare语句事先声明）
           select @oldaacode:=vcaaCode from T_sum where vcaaCode=var1 and dtDate=sumdate; 
           if @oldaacode=var1 then    --判断
              update T_sum set iNum=var3 where vcaaCode=var1 and dtDate=sumdate;
           else
               insert into T_sum(vcaaCode,vcaaName,iNum,dtDate) values(var1,var2,var3,sumdate);
           end if;
     UNTIL no_more_departments  END REPEAT;    --循环语句结束
     CLOSE C_RESULT;                            --关闭游标

     DROP TEMPORARY TABLE tmp_table;       --删除临时表
end;

5、触发器
新数据：new.字段名，旧数据：old.字段名

DROP TRIGGER IF EXISTS t_afterinsert_on_record;
CREATE TRIGGER t_afterinsert_on_record 
AFTER INSERT ON go_run_record
FOR EACH ROW
BEGIN

end;