mysql循环建表_MySQL练习-生成随机大乐透号码

早前的一篇数据库学习文章双色球号码我做主，数据库编程随机生成双色球号码(MySQL)，后台很多网友评论说双色球都有了，大乐透也不能落下，今天把大乐透的补上，具体的技术点，请点击上面的链接查看另一篇文章。

1、创建所需的表，大乐透红球池是35个，补充两个即可，麻烦的是篮球，篮球一次要有两个，为了完全模拟开奖的情况，也采用和红球一样的做法，生成两个篮球。具体建表语句如下：

#红球表DROP TABLE IF EXISTS `bred`;CREATE TABLE `bred` ( `r` int(11) NULL DEFAULT NULL, `u` char(1) NULL DEFAULT NULL);INSERT INTO `bred` VALUES (1, '0');INSERT INTO `bred` VALUES (2, '0');INSERT INTO `bred` VALUES (3, '0');INSERT INTO `bred` VALUES (4, '0');INSERT INTO `bred` VALUES (5, '0');INSERT INTO `bred` VALUES (6, '0');INSERT INTO `bred` VALUES (7, '0');INSERT INTO `bred` VALUES (8, '0');INSERT INTO `bred` VALUES (9, '0');INSERT INTO `bred` VALUES (10, '0');INSERT INTO `bred` VALUES (11, '0');INSERT INTO `bred` VALUES (12, '0');INSERT INTO `bred` VALUES (13, '0');INSERT INTO `bred` VALUES (14, '0');INSERT INTO `bred` VALUES (15, '0');INSERT INTO `bred` VALUES (16, '0');INSERT INTO `bred` VALUES (17, '0');INSERT INTO `bred` VALUES (18, '0');INSERT INTO `bred` VALUES (19, '0');INSERT INTO `bred` VALUES (20, '0');INSERT INTO `bred` VALUES (21, '0');INSERT INTO `bred` VALUES (22, '0');INSERT INTO `bred` VALUES (23, '0');INSERT INTO `bred` VALUES (24, '0');INSERT INTO `bred` VALUES (25, '0');INSERT INTO `bred` VALUES (26, '0');INSERT INTO `bred` VALUES (27, '0');INSERT INTO `bred` VALUES (28, '0');INSERT INTO `bred` VALUES (29, '0');INSERT INTO `bred` VALUES (30, '0');INSERT INTO `bred` VALUES (31, '0');INSERT INTO `bred` VALUES (32, '0');INSERT INTO `bred` VALUES (33, '0');INSERT INTO `bred` VALUES (34, '0');INSERT INTO `bred` VALUES (35, '0');#篮球表DROP TABLE IF EXISTS `bblue`;CREATE TABLE `bblue` ( `b` int(11) NULL DEFAULT NULL, `u` char(1) NULL DEFAULT '0');INSERT INTO `bblue` VALUES (1, '0');INSERT INTO `bblue` VALUES (2, '0');INSERT INTO `bblue` VALUES (3, '0');INSERT INTO `bblue` VALUES (4, '0');INSERT INTO `bblue` VALUES (5, '0');INSERT INTO `bblue` VALUES (6, '0');INSERT INTO `bblue` VALUES (7, '0');INSERT INTO `bblue` VALUES (8, '0');INSERT INTO `bblue` VALUES (9, '0');INSERT INTO `bblue` VALUES (10, '0');INSERT INTO `bblue` VALUES (11, '0');INSERT INTO `bblue` VALUES (12, '0');

2、创建函数dlt()，两次循环为了生成红球和篮球

CREATE FUNCTION `dlt`() RETURNS varchar(100) CHARSET utf8BEGINdeclare i int default 1;declare red int;declare blue int;declare redballs varchar(100);declare blueballs varchar(100);update bred set u='0';update bblue set u='0';while i<=5 doselect r into red from bred where u='0' order by rand() limit 1;update bred set u='1' where r=red;set i = i+1;end while;set i=1;while i<=2 doselect b into blue from bblue where u='0' order by rand() limit 1;update bblue set u='1' where b=blue;set i = i+1;end while;select GROUP_CONCAT(r) into redballs from bred where u='1';select GROUP_CONCAT(b) into blueballs from bblue where u='1';RETURN concat('数据库预测大乐透-号码发布时间：',cast(now() as char(20)),'，号码为红球:',redballs,'|篮球:',blueballs);END;

3、调用函数进行预测

select dlt();

念咒增加中奖率

综上就是使用MySQL随机模拟生成大乐透号码的过程，希望你喜欢。最后再次重申，这只是用来练习MySQL的习作。如果中将，纯属意外！