MySQL之生成唯一的序列号和删除重复数据

命令行：
begin 启动事务
insert into 表名 values(); 向表中插入数据
select * from 表名
rollback 回滚事务

生成唯一序列号
需要用到唯一序列号的场景：数据库主键，业务序列号如发票号，车票号，订单号等。

如何选择生成序列号的方式
1：优先选择系统提供的序列号生成方式
2：在特殊的情况下可以使用SQL方法生成序列号

MySQL: AUTO_INCREMENT
SQLServer:IDENTITY/SEQUENCE
Oracle：SEQUENCE
PgSQL:SEQUENCE

注意：mysql的auto_increment的方式如果使用事务回滚会产生空洞情况

coalesce  when  XXX  then OOO,when XXX2 then  OOO2,case  when b then B,case  where  c  then C);
case  when:相当于if else
coalesce：获取第一个有值的字段

需求：
由SQL来生成特殊的序列号，生成订单序列号，并且订单号的公式如下
YYYYMMDDNNNNNNN。如20105120000003

DECLARE v_cnt INT;
DECLARE v_timestr INT;
DECLARE rowcount BIGINT;
SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');
SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;
START TRANSACTION;
UPDATE order_seq SET order_sn = order_sn + v_cnt WHERE timestr = v_timestr;
IF ROW_COUNT() = 0 THEN
INSERT INTO order_seq(timestr,order_sn) VALUES(v_timestr,v_cnt);
END IF;
SELECT CONCAT(v_timestr,LPAD(order_sn,7,0))AS order_sn
FROM order_seq WHERE timestr = v_timestr;
COMMIT;

通过存储过程和事务等确保生成唯一序列号，可以通过创建一张表，记录某天的流水号最大是什么，然后通过某一个数可以叠加起来。

完善一下存储过程，添加表
唯一序列号 获取当前年月日+3位流水号
用orser_seq判断插入最大值

  DROP PROCEDURE IF EXISTS usp_seqnum;
  CREATE PROCEDURE usp_seqnum()
    BEGIN
      定义变量并获取相关值
      DECLARE v_cnt INT;
      DECLARE v_timestr INT;
      SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');
      SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;
      新建表
      DROP TABLE IF EXISTS im_orderseq;
      CREATE TABLE  im_orderseq(
        timestr NVARCHAR(8) NOT NULL ,
        ordersn INT(3)
      );
      START TRANSACTION;
      更新表的最值
      UPDATE im_orderseq SET ordersn = ordersn + v_cnt WHERE timestr = v_timestr;
      IF ROW_COUNT() = 0 THEN
        插入数据
        INSERT INTO im_orderseq(timestr,ordersn) VALUES(v_timestr,v_cnt);
      END IF;
      SELECT CONCAT(v_timestr,LPAD(ordersn,7,0))AS ordersn
      FROM im_orderseq WHERE timestr = v_timestr;
      COMMIT;
    END;

  CALL usp_seqnum();
  SELECT * FROM im_orderseq;

以DECLARE关键字声明的变量，只能在存储过程中使用，称为存储过程变量。

LPAD(str,len,padstr)
用字符串padstr对str进行左边填补直至它的长度达到len个字符长度，然后返回str。如果str的长度长于len，那么它将被截除到len个字符。

问题：
如何删除重复的数据？
利用主键来删除重复的数据

使用Group by和having从句来判断是否存在重复数据

1：判断数据是否重复

SELECT user_name , over, count(*) FROM user1_test GROUP BY user_name, over HAVING count(*)>1

2：删除重复数据，对于相同数据保留ID最大的

DELETE a 
FROM user1_test a JOIN(
SELECT user_name,COUNT(*),MAX(id) AS id
FROM user1_test
GROUP BY user_name HAVING COUNT(*) > 1
) b ON a.user_name = b.user_name
WHERE a.id < b.id

3：将表中查询的数据再次插入到表中

INSERT INTO xmss_wh(XM_ID,WH_ID,YJ,SH,SHR,FJ_NAME,FJ_ID) 
SELECT XM_ID,WH_ID,YJ,SH,SHR,FJ_NAME,FJ_ID FROM xmss_wh WHERE ID=28;

sql中的group by用法解析：
Group by语句从英文的字面意义上理解就是(根据by一定的规则进行分组 group)
它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。

注意：group by是先排序后分组的。
举例子说明：如果要用到group by一般用到的就是 “每这个字”，例如说明现在有一个这样的表：每个部门有多少人就要用到分组的技术

select DepartmentID as ‘部门名称’；
count(*)  as  ‘个数’  from  BasicDepartment  group by DepartmentID

这个就是使用了group by+字段进行了分组，其中我们就可以理解为我们按照了部门的名称ID ，DepartmentID将数据集进行了分组，然后再进行了各个组的统计数据分别有多少；

如果不适用count(*)等类似的函数，select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID 就会报错。

判断数据是否重复：Group by column_name having count(*)>=2;
通过多表删除，Group by后，相当于形成了一个个单独的表。

利用group by和having从句处理查询数据是否重复

select  col1_name,count(*) from table1 group by col1_name having count(*)>1

参考视频：http://www.imooc.com/learn/427