194、航空网的几个航班查询题：

johnny

194、航空网的几个航班查询题：


表结构如下：
flight{flightID,StartCityID ,endCityID,StartTime}
city{cityID, CityName)
实验环境：
create table city(cityID int auto_increment primary key,cityName varchar(20));
create table flight (flightID int auto_increment primary key,
StartCityID int references city(cityID),
endCityID  int references city(cityID),
StartTime timestamp);
//航班本来应该没有日期部分才好，但是下面的题目当中涉及到了日期
insert into city values(null,'北京'),(null,'上海'),(null,'广州');
insert into flight values
(null,1,2,'9:37:23'),(null,1,3,'9:37:23'),(null,1,2,'10:37:23'),(null,2,3,'10:37:23');


1、查询起飞城市是北京的所有航班，按到达城市的名字排序


参与运算的列是我起码能够显示出来的那些列，但最终我不一定把它们显示出来。各个表组合出来的中间结果字段中必须包含所有运算的字段。

select  * from flight f,city c
where f.endcityid = c.cityid and startcityid =
(select c1.cityid from city c1 where c1.cityname = "北京")
order by c.cityname asc;

mysql> select flight.flightid,'北京' startcity, e.cityname from flight,city e wh
ere flight.endcityid=e.cityid and flight.startcityid=(select cityid from city wh
ere cityname='北京');

mysql> select flight.flightid,s.cityname,e.cityname from flight,city s,city e wh
ere flight.startcityid=s.cityid and s.cityname='北京' and flight.endCityId=e.cit
yID order by e.cityName desc;


2、查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）
select c1.CityName,c2.CityName,f.StartTime,f.flightID
from city c1,city c2,flight f
where f.StartCityID=c1.cityID
and f.endCityID=c2.cityID
and c1.cityName='北京'
and c2.cityName='上海'
3、查询具体某一天（2005-5-8）的北京到上海的的航班次数
select count(*) from
(select c1.CityName,c2.CityName,f.StartTime,f.flightID
from city c1,city c2,flight f
where f.StartCityID=c1.cityID
and f.endCityID=c2.cityID
and c1.cityName='北京'
and c2.cityName='上海'
and 查帮助获得的某个日期处理函数(startTime) like '2005-5-8%'

mysql中提取日期部分进行比较的示例代码如下：
select * from flight where date_format(starttime,'%Y-%m-%d')='1998-01-02'