MySQL-SQL-tips

1. 给表A列后增加一列：
   

   alter table TABLENAME add column C_COLUMN int default 0 after A_COLUMN;

2. MYsql中使用row_number
   

   /*A表存放客户存款(有姓名和存款金额字段)，按金额排序取前五，并带上ROW_NUMBER */
   
    SELECT @rownum:=@rownum+1 rownum, t.name, t.amount From
    (SELECT @rownum:=0,name,amount FROM A ORDER BY desc limit 5) t
   
   解析：子查询 'SELECT @rownum:=0,name,amount FROM A ORDER BY desc limit 5' 实现rownum变量的初始化0,从子查询SELECT @ROWNUM=@ROWNUM+1，
   每次遍历都会在原来基础上+1，第一次遍历为@rownum=0+1，第二次为@rownum=1+1, 以此类推直到遍历到最后一行。
   
   
   上述SQL也可以这么写：
   
   select 0 INTO @row;   /*初始化*/
   或者 SELECT @row:=0;
   
   SELECT id,class,score,@row:=@row+1 FROM partition_by_test A;  /*遍历+1操作*/

    

3. 直接执行语句(打印查询语句的结果到文件)(也可以直接执行其它语句UPDATE,INSERT等)
   

   mysql -uroot -p123 DBNAME -e "select * from a where ..." > 1.txt

    

4. 直接按条件导出INSERT 语句
   

   mysqldump -uroot -p123 databasename tbname -w"ID in (1,2)"|grep insert > test.sql
   

   　　

5. 解决INTO OUTFILE方式导出文件，如果字段太长，则会在文件中换行的问题
   

   假如USER_DESC字段太长，通过将其替换为空字符串，char(13):  回车符,char(10):  换行符
   
   SELECT replace(replace(USER_DESC,char(10),''),char(13),'') FROM A WHERE ID=10
   INTO OUTFILE 'tets.csv' fields terminated by '\t' lines terminated by '\n';

    

6. 存储过程查询结果赋值变量问题
   

   存储过程可以一次性插入几个查询出来的值INTO到不同的变量中，例如，
   SELECT SUM(A),SUM(B) ... INTO V_VARIABLE1，V_VARIABLE1;
   上面的是静态SQL, 如果是动态SQL，则需要使用@符号，例如 
   v_sql= 'SELECT SUM(A),SUM(B) ... INTO @V_VARIABLE1，@V_VARIABLE1;

    

7. TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)函数，其结果是 datetime_expr2-datetime_expr1的值

   说明:
   返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的
   单位由interval 参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同。
   
   SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01');
   SELECT TIMESTAMPDIFF(HOUR,'2009-10-01 00:00:00','2009-09-01 08:00:00');
   
   interval可是：
   SECOND 秒 SECONDS
   MINUTE 分钟 MINUTES
   HOUR 时间 HOURS
   DAY 天 DAYS
   MONTH 月 MONTHS
   YEAR 年 YEARS

    

8. MYSQL日期函数
   

   now()函数返回的是当前时间的年月日时分秒，如：2008-12-29 16:25:46
   CURDATE()函数返回的是年月日信息： 如：2008-12-29
   CURTIME()函数返回的是当前时间的时分秒信息，如：16:25:46
   另外，如果我们想对一个包含年月日时分秒日期格式化成年月日日期，可以使用DATE(time)函数，如
   DATE(now()) 返回的是 2008-12-29

    

9. MYSQL实现ROW_NUMBER() OVER(PRATITION BY A ORDER BY B DESC)功能
   

   建表测试：
   CREATE TABLE `partition_by_test` (
       `ID` INT(10) NULL DEFAULT NULL,
       `class` INT(10) NULL DEFAULT NULL,
       `score` INT(10) NULL DEFAULT NULL
   )
   COLLATE='utf8_general_ci'
   ENGINE=InnoDB;
   
   insert into partition_by_test values (1,1,80);
   insert into partition_by_test values (2,1,75);
   insert into partition_by_test values (3,1,90);
   insert into partition_by_test values (4,1,95);
   insert into partition_by_test values (5,2,105);
   insert into partition_by_test values (6,2,58);
   insert into partition_by_test values (7,2,67);
   insert into partition_by_test values (8,2,87);
   insert into partition_by_test values (9,2,99);
   insert into partition_by_test values (10,2,96);
   
   场景：
   对 class进行分组，先按CLASS排序再按score降序排序(组内排序)，并带上组内排序的序号。
   场景应用：
   如果有一张表有多个重复记录，可以根据此SQL找出组内排序后序号>1的自增ID全部删除之，达到去重的效果。
   可能要达到去重，还有一种方法，根据所有字段group by后导出到文件，清空表，文件入库，完成。但是有时候是不容许清空表的，比如生产环境上的应用一般不允许直接清表，万一在清表的过程中，有新的数据插进来了，会导致表锁死或脏数据或数据不对。
   
   SELECT @rownum:=@rownum+1 AS ROW_NUM,
   A.ID,A.CLASS,A.SCORE,
   IF(@tmp=A.class,@rank:=@rank+1,@rank:=1) AS RANK,
   @tmp:=A.class AS TMP
   FROM
   (SELECT id,class,score FROM partition_by_test ORDER BY class ASC ,SCORE DESC) A
   ,
   (SELECT @rownum :=0 , @tmp := NULL ,@rank:=NULL) B;
   
   

   
   IF语法：IF(A=B,A++,A:=1)  如果A和B相等，则A加1，否则重新赋值A等于1。
   
   SQL解析：
   
   以SELECT id,class,score FROM partition_by_test ORDER BY class ASC ,SCORE DESC 这段SQL的结果为基准。
   SELECT @rownum :=0 , @tmp := NULL ,@rank:=NULL;这一段为变量初始化语句，这个初始化，按需赋值，在此场景需求中@tmp和@rank无需初始化。
   所谓按需初始化，如果不需要row_number，则可以精简为：
   
   

   
   最后一列赋值了@tmp:=A.class,由于在IF语句结束后才会赋值@tmp:=@A.class, 刚开始@tmp的值为NULL, 在程序运行时, 第一次发现NULL<>A.class,赋值@rank:=1, 这一段的IF结束了,赋值@tmp:=A.class，此时的A.class=1，故@tmp=1=a.class，第二次@rank加1，当程序遍历到class=2时，由于IF循环(第五列)在 @tmp:=A.class(第六列) 之前，所以此时的tmp还是1，只有当IF结束，执行@tmp:=A.class(第六列)时，tmp的值才会变成2，所以(@tmp=1)<>(a.class=2),重新赋值@rank:=1，到此程序遍历此条记录结束，下次发现(@tmp=2)=(a.class=2)，执行+1操作,以此类推.... ,直到遍历完所有记录。
   
   推翻上述猜想的反例验证：
   调换第五列(RANK)和第六列(TMP)的位置，发现没有得到想要的结果，反证，上述分析成立。
   
   附sql以及结果：
   
   SELECT @rownum:=@rownum+1 AS ROW_NUM,
   A.ID,A.CLASS,A.SCORE,@tmp:=A.class AS TMP,
   IF(@tmp=A.class,@rank:=@rank+1,@rank:=1) AS RANK
   
   FROM
   (SELECT id,class,score FROM partition_by_test ORDER BY class ASC ,SCORE DESC) A
   ,
   (SELECT @rownum :=0 , @tmp := NULL ,@rank:=NULL) B;
   
   

   
   解析，第五列赋值@tmp=a.class=1,结束执行if语句，条件成立，执行@rank:=@rank+1，此时的@rank=NULL，再加也是NULL，所以最后一列显示NULL

    

10.