MySQL彩票分析

Mysql 彩票分析实例

create database CP1;  #创建数据库

use cp1;

# drop table TableRaw;

# 创建表
create table if not exists TableRaw(
    rawdata varchar(200) not null unique);

# 导入数据
load data infile 'C:\\RawData.TXT' 
    into table TableRaw
    fields terminated by '\t';

select * from TableRaw;
desc tableraw;
select count(*) from TableRaw;

---------------原始数据为1个字段，对其进行拆分，先建立字段------------------

alter table TableRaw add RowNumber int primary key auto_increment; -- 自增字段，用来记录彩票张数
alter table TableRaw add FNo char(7) not null default ''; -- 本号、票号唯一代表一张彩票
alter table TableRaw add TNo char(3) not null default ''; -- 票号
alter table TableRaw add Mark char(10) not null default ''; -- 图符
alter table TableRaw add Reward char(10) not null default ''; -- 奖金
alter table TableRaw add FloatNo char(10) not null default ''; -- 漂移字符
alter table TableRaw add CNo char(8) not null default ''; -- 手工验奖
alter table TableRaw add LCode char(29) not null default ''; -- 物流码
alter table TableRaw add SCode char(41) not null default ''; -- 保安码

#重新排序
alter table tableraw modify RowNumber int first;

desc tableRaw;

-- 图符1-10，将图符字段拆分为10个单字母字段
#创建存储过程
delimiter $$
create procedure markfix()
Begin
declare n int;
declare num varchar(2);
set n=1;
set @sqltext = '';
while n<10 do
  set num=cast(n as char);
  set @sqltext = concat(@sqltext, 'add mark',num,' char(1) not null, ');
  -- 1.@sqltext = add mark1 char(1) not null,
  -- 2.@sqltext = add mark1 char(1) not null, add mark2 char(1) not null,
  -- 3.@sqltext = add mark1 char(1) not null, add mark2 char(1) not null, add mark3 char(1) not null,
  -- 9.@sqltext = add mark1 char(1) not null, add mark2 char(1) not null,....add mark9 char(1) not null,
  set n=n+1;
end while;
  set @sqltext = concat('alter table tableraw ',@sqltext, 'add mark10 char(1) not null;');
-- final. @sqltext = alter table tableraw add mark1 char(1) not null, add mark2 char(1) not null,....add mark9 char(1) not null, add mark10 char(1) not null;
end $$
delimiter ;

-- alter table tableraw add mark1 char(1) not null,
--  add mark2 char(1) not null,
--  add mark3 char(1) not null,
--  add mark4 char(1) not null,
--  add mark5 char(1) not null,
--  add mark5 char(1) not null,
--  add mark6 char(1) not null;

#drop procedure markfix;

#检查生成的sql语句
call markfix();
select @sqltext;

#执行语句