mysql优化_mysql sql优化_mysql优化面试题(10)

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 fromflight,city e wh

ere flight.endcityid=e.cityid and flight.startcityid=(select cityidfrom city wh

ere cityname='北京');

mysql> select flight.flightid,s.cityname,e.cityname fromflight,city s,city e wh

ere flight.startcityid=s.cityid and s.cityname='北京' andflight.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 wheredate_format(starttime,'%Y-%m-%d')='1998-01-02'

5.查出比经理薪水还高的员工信息：

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-34607-10.html