SQL整理九

+---------------------------------------------------------------------------------------+
球队信息表：tb_team
CREATE TABLE IF NOT EXISTS `tb_team` (
  `team_id` int(11) NOT NULL auto_increment COMMENT 'KEY',
  `team_name` varchar(50) NOT NULL COMMENT '球队名称',
  `team_coach` varchar(20) default NULL COMMENT '教练',
  `team_message` text COMMENT '球队信息',
  `team_members` text COMMENT '队员列表',
  `alliance_id` int(11) default NULL COMMENT '联盟ID',
  `team_create_time` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '添加时间',
  PRIMARY KEY  (`team_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='球队信息表' AUTO_INCREMENT=5

INSERT INTO `tb_team` VALUES (1, '湖人', '教练1', '球队信息', '队员列表', 1, '2003-12-11 00:00:00');
INSERT INTO `tb_team` VALUES (2, '火箭', '教练2', '球队信息', '队员列表', 1, '2003-12-11 00:00:00');
INSERT INTO `tb_team` VALUES (3, '太阳', '教练3', '球队信息', '队员列表', 1, '2003-12-11 00:00:00');
INSERT INTO `tb_team` VALUES (4, '小牛', '教练4', '球队信息', '队员列表', 1, '2003-12-11 00:00:00');
INSERT INTO `tb_team` VALUES (5, '公牛', '教练5', '球队信息', '队员列表', 1, '2003-12-11 00:00:00');
INSERT INTO `tb_team` VALUES (6, '尼克', '教练6', '球队信息', '队员列表', 1, '2003-12-11 00:00:00');
INSERT INTO `tb_team` VALUES (7, '活塞', '教练7', '球队信息', '队员列表', 1, '2003-12-11 00:00:00');
INSERT INTO `tb_team` VALUES (8, '骑士', '教练8', '球队信息', '队员列表', 1, '2003-12-11 00:00:00');
+---------------------------------------------------------------------------------------+
赛程信息表：tb_match
CREATE TABLE IF NOT EXISTS `tb_match` (
  `match_id` int(11) NOT NULL auto_increment COMMENT 'KEY',
  `local_id` int(11) default NULL COMMENT '主队ID',
  `guest_id` int(11) default NULL COMMENT '客队ID',
  `match_date` date NOT NULL default '0000-00-00',
  `match_time` time NOT NULL default '00:00:00' COMMENT '比赛时间',
  `live_tv` varchar(50) default NULL COMMENT '电视转播台',
  `match_ot` tinyint(4) NOT NULL default '0' COMMENT '是否加时赛',
  `match_off` tinyint(4) NOT NULL default '0' COMMENT '是否完赛',
  `match_update_time` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '赛程最后更新时间',
  `match_show` tinyint(4) NOT NULL default '0' COMMENT '赛程是否显示',
  `balltype_id` int(11) default NULL,
  PRIMARY KEY  (`match_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='赛程信息表' AUTO_INCREMENT=3 

INSERT INTO `tb_match` VALUES (1, 1, 2, '2009-06-27', '00:20:09', 'espn', 0, 1, '2009-06-24 12:00:00', 1, 1);
INSERT INTO `tb_match` VALUES (2, 3, 8, '2009-06-29', '00:20:09', 'espn', 0, 1, '2009-06-24 12:00:00', 1, 1);
INSERT INTO `tb_match` VALUES (3, 4, 5, '2009-06-29', '00:20:09', 'espn', 0, 0, '2009-06-24 12:00:00', 1, 1);
INSERT INTO `tb_match` VALUES (4, 3, 6, '2009-06-30', '00:20:09', 'espn', 0, 0, '2009-06-24 12:00:00', 1, 1);
INSERT INTO `tb_match` VALUES (5, 1, 7, '2009-06-30', '00:20:09', 'espn', 0, 1, '2009-06-24 12:00:00', 1, 1);
INSERT INTO `tb_match` VALUES (6, 6, 4, '2009-07-01', '00:20:09', 'espn', 0, 1, '2009-06-24 12:00:00', 1, 1);
INSERT INTO `tb_match` VALUES (7, 2, 7, '2009-07-01', '00:20:09', 'espn', 0, 0, '2009-06-24 12:00:00', 1, 1);
INSERT INTO `tb_match` VALUES (8, 5, 1, '2009-07-01', '00:20:09', 'espn', 0, 0, '2009-07-04 12:00:00', 1, 1);
+---------------------------------------------------------------------------------------+
比分信息表：tb_score
CREATE TABLE IF NOT EXISTS `tb_score` (
  `score_id` int(11) NOT NULL auto_increment COMMENT 'KEY',
  `match_id` int(11) NOT NULL COMMENT '赛程ID',
  `team_id` int(11) NOT NULL COMMENT '球队ID',
  `sc1` int(3) default '0' COMMENT '比分',
  `sc2` int(3) default '0' COMMENT '比分',
  `sc3` int(3) default '0' COMMENT '比分',
  `sc4` int(3) default '0' COMMENT '比分',
  `sc5` int(3) default '0' COMMENT '比分',
  `sc6` int(3) default '0' COMMENT '比分',
  `sc7` int(3) default '0' COMMENT '比分',
  `sc8` int(3) default '0' COMMENT '比分',
  `sc9` int(3) default '0' COMMENT '比分',
  `sc10` int(3) default '0' COMMENT '比分',
  `sc11` int(3) default '0' COMMENT '比分',
  `sc12` int(3) default '0' COMMENT '比分',
  PRIMARY KEY  (`score_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='比分信息表' AUTO_INCREMENT=3 ;

INSERT INTO `tb_score` VALUES (1, 1, 1, 12, 17, 15, 17, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (2, 1, 2, 23, 14, 27, 24, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (3, 2, 3, 12, 17, 15, 41, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (4, 2, 8, 23, 14, 25, 12, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (5, 3, 4, 12, 19, 15, 14, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (6, 3, 5, 23, 14, 22, 40, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (7, 4, 3, 12, 32, 15, 41, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (8, 4, 6, 23, 14, 22, 13, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (9, 5, 1, 12, 34, 15, 41, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (10, 5, 7, 12, 14, 22, 22, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (11, 6, 6, 12, 12, 15, 36, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (12, 6, 4, 23, 14, 22, 24, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (13, 7, 2, 12, 41, 15, 31, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (14, 7, 7, 23, 14, 22, 19, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (15, 8, 5, 16, 35, 12, 32, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `tb_score` VALUES (16, 8, 1, 21, 31, 12, 31, 0, 0, 0, 0, 0, 0, 0, 0);
+--------------------------------------------------------------------------------+
要求：
1.查询指定日期的赛程信息，信息内容包括以下内容 比如查询 2009-06-27 的赛程信息
+----+----------+----------+---------+---------+------------+---------+---------+
|主键  主队名字     客队名字    主队分数   客队分数     比赛日期    比赛时间  是否完赛
+----+----------+----------+---------+---------+------------+---------+---------+
| id  local_name guest_name local_sc  guest_sc   match_date  match_time match_off
+----+----------+----------+---------+---------+------------+---------+---------+
|  1     湖人       火箭         61        88      2009-06-27 12:25:42     1     
+----+----------+----------+---------+---------+------------+---------+---------+

未完待续