MySQL作业：三色球，概率题，子查询【诗书画唱】
use tableDemo（使用tableDemo表）
1、创建表tbfact表，表结构是：
id int primary key 自动增长
r1 int
r2 int
r3 int
r4 int
r5 int
r6 int
b7 int
qs varchar(7)
create table tbfact(
id int primary key auto_increment,
r1 int,
r2 int,
r3 int,
r4 int,
r5 int,
r6 int,
b7 int,
qs varchar(7)
);
2、将tb_fact.sql中的所有数据插入到tbfact表中。
INSERT INTO tbfact VALUES ('1', '1', '9', '10', '16', '22', '24', '11', '2004009');
INSERT INTO tbfact VALUES ('111', '6', '9', '18', '20', '25', '33', '6', '2004119');
INSERT INTO tbfact VALUES ('112', '7', '8', '18', '21', '27', '32', '10', '2004120');
INSERT INTO tbfact VALUES ('113', '7', '13', '16', '18', '30', '32', '10', '2004121');
INSERT INTO tbfact VALUES ('266', '1', '5', '12', '14', '21', '27', '3', '2005152');
INSERT INTO tbfact VALUES ('267', '4', '5', '7', '21', '26', '29', '1', '2005153');
INSERT INTO tbfact VALUES ('268', '1', '12', '15', '19', '21', '28', '3', '2005154');
INSERT INTO tbfact VALUES ('419', '1', '14', '20', '25', '27', '31', '15', '2006152');
INSERT INTO tbfact VALUES ('420', '1', '7', '11', '20', '30', '33', '10', '2006153');
INSERT INTO tbfact VALUES ('421', '7', '14', '18', '20', '30', '33', '13', '2006154');
INSERT INTO tbfact VALUES ('573', '11', '17', '21', '29', '30', '33', '8', '2007152');
INSERT INTO tbfact VALUES ('574', '1', '4', '19', '20', '25', '31', '15', '2007153');
INSERT INTO tbfact VALUES ('575', '2', '4', '7', '9', '14', '29', '3', '2007154');
INSERT INTO tbfact VALUES ('722', '3', '5', '9', '22', '26', '28', '9', '2008148');
INSERT INTO tbfact VALUES ('723', '10', '14', '22', '28', '29', '33', '2', '2008149');
INSERT INTO tbfact VALUES ('724', '4', '19', '22', '24', '29', '32', '2', '2008150');
INSERT INTO tbfact VALUES ('725', '6', '8', '10', '14', '17', '19', '6', '2008151');
INSERT INTO tbfact VALUES ('726', '1', '4', '6', '22', '26', '30', '8', '2008152');
INSERT INTO tbfact VALUES ('727', '1', '4', '18', '21', '24', '30', '16', '2008153');
INSERT INTO tbfact VALUES ('728', '2', '5', '7', '21', '22', '26', '8', '2008154');
INSERT INTO tbfact VALUES ('729', '4', '21', '23', '24', '30', '31', '4', '2009001');
INSERT INTO tbfact VALUES ('730', '10', '14', '17', '25', '29', '33', '14', '2009002');
INSERT INTO tbfact VALUES ('879', '6', '8', '10', '16', '25', '30', '14', '2009151');
INSERT INTO tbfact VALUES ('880', '3', '4', '19', '21', '27', '28', '5', '2009152');
INSERT INTO tbfact VALUES ('881', '6', '7', '8', '20', '21', '25', '10', '2009153');
INSERT INTO tbfact VALUES ('882', '1', '7', '12', '14', '18', '25', '16', '2009154');
INSERT INTO tbfact VALUES ('1185', '8', '10', '12', '15', '22', '27', '13', '2011150');
INSERT INTO tbfact VALUES ('1186', '7', '11', '16', '19', '31', '33', '10', '2011151');
INSERT INTO tbfact VALUES ('1187', '4', '10', '11', '12', '21', '26', '13', '2011152');
INSERT INTO tbfact VALUES ('1329', '3', '5', '8', '19', '20', '27', '9', '2012141');
INSERT INTO tbfact VALUES ('1330', '5', '18', '22', '28', '29', '31', '6', '2012142');
INSERT INTO tbfact VALUES ('1331', '7', '8', '18', '25', '30', '32', '6', '2012143');
1、查询2010年的所有数据。
select * from tbfact where qs like '2010%'
2、查询2005年到2007年的b7列的所有数据。
select b7 from tbfact where qs like '2005%' or qs like '2006%' or qs like '2007%'
3、统计b7列中出现的数值以及各数值出现的次数。
select b7 , count(b7) from tbfact group by b7
4、显示表中r1到r6的和值（每行的和）以及期数。
select (r1+r2+r3+r4+r5+r6) , qs from tbfact
5、统计表中r4列中出现过的数字及这些数字出现的次数。
select r4 , count(r4) from tbfact group by r4
6、查询表中r1到r6的和值（每行的和）的最大值。
select max(r1+r2+r3+r4+r5+r6) from tbfact
7、统计2012年r1到r6的和值以及和值出现的次数。
select (r1+r2+r3+r4+r5+r6) ,count(r1+r2+r3+r4+r5+r6) from tbfact where qs like '2012%' group by (r1+r2+r3+r4+r5+r6)
8、查询出现了4个连续数字的期数的所有信息（r1到r6列已按照从小到大的顺序排列）。
select * from tbfact where ((r1-r2)=-1 and (r2-r3)=-1 and (r3-r4)=-1) or((r2-r3)=-1 and (r3-r4)=-1 and (r4-r5)=-1) or ((r3-r4)=-1 and (r4-r5)=-1 and (r5-r6)=-1)
9、查询2009年r1到r6列的和值的平均值。
select avg(r1+r2+r3+r4+r5+r6) from tbfact where qs like '2009%' group by (r1+r2+r3+r4+r5+r6)
10、统计b7列中数字7出现的概率。
select (select count(b7) from tbfact where b7=7)/ (select count(b7) from tbfact)
