use tableDemo（使用tableDemo表）

1、創(chuàng)建表tbfact表，表結(jié)構(gòu)是：

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中的所有數(shù)據(jù)插入到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年的所有數(shù)據(jù)。?

select * from tbfact where? qs? like '2010%'

2、查詢2005年到2007年的b7列的所有數(shù)據(jù)。

select b7 from tbfact where? qs like? '2005%' or qs like? '2006%' or qs like? '2007%'

3、統(tǒng)計(jì)b7列中出現(xiàn)的數(shù)值以及各數(shù)值出現(xiàn)的次數(shù)。

select b7 , count(b7) from tbfact? ?group by b7

4、顯示表中r1到r6的和值（每行的和）以及期數(shù)。

select (r1+r2+r3+r4+r5+r6) , qs? from tbfact??

5、統(tǒng)計(jì)表中r4列中出現(xiàn)過的數(shù)字及這些數(shù)字出現(xiàn)的次數(shù)。

select r4 , count(r4) from tbfact? ?group by r4

6、查詢表中r1到r6的和值（每行的和）的最大值。

select? max(r1+r2+r3+r4+r5+r6) from tbfact??

7、統(tǒng)計(jì)2012年r1到r6的和值以及和值出現(xiàn)的次數(shù)。

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、查詢出現(xiàn)了4個連續(xù)數(shù)字的期數(shù)的所有信息（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、統(tǒng)計(jì)b7列中數(shù)字7出現(xiàn)的概率。?

select? (select count(b7) from? tbfact? where b7=7)/ (select count(b7) from? tbfact)? ?