旅游管理信息系统3.pptVIP

* 逻辑运算符使用练习 ? Select * From Hotel Where not Price2250 ? Select Tno,Stime,PriceRz From Train Where Station=‘昆明’ and Estation=‘北京 ? Select * From Hotel Where not (Price2200 and Price2300) ? Select * From Hotel Where not Price2200 and Price2300 * 范围查询练习 ? --选择级别为三星，标间价格在200--300之间的所有记录,利用Between...And ? Select * From Hotel Where Hlevel=‘三星级 And Price2 Between 200 And 300 ? ? --查询标间价格在[100,200]或者在[250,300]的记录 ? Select * From Hotel Where (Price2 Between 100 And 200) Or (Price2 Between 250 And 300) ? Select * From Hotel Where (Price2 =100 And Price2 =200) Or (Price2 =250 And Price2 =300) * 列表查询练习 ? --选择级别为三星和四星的所有记录,利用IN ? Select * From Hotel Where Hlevel IN(‘三星级’ , ‘四星级 ) ? ? --查询车次为54、42的记录，利用IN ? *Select * From Train Where Tno IN(‘54’ , ‘42 ) * 练习 --选择级别为四星或者为准四星的记录，用逻辑条件OR Select * From Hotel Where Hlevel=‘四星级’ or Hlevel=‘准四星级 --选择级别为四星或者为准四星的记录，用IN Select * From Hotel Where Hlevel In (‘四星级’ ,‘准四星级 ) --选择级别不为四星的记录，用 Select * From Hotel Where Hlevel‘四星级’ --选择级别不为四星的记录，用NOT IN Select * From Hotel Where Hlevel Not In(‘四星级’ ) * 模式查询条件 ? 模式查询条件常用来返回符合某种格式的所有记录，通常使用LIKE或NOT LIKE关键字来指定模式查询条件。 ? LIKE关键字使用通配符来表示字符串需要匹配的模式。 * 模式查询条件通配符 * 练习 --模糊查询LIKE Select * From Place Where Pname Like ‘%公园 Select * From Place Where Pname Like ‘%山% * 使用聚合函数统计 ? 聚合函数（例如SUM、AVG、COUNT、COUNT(*)、MAX 和MIN）在查询结果集中生成汇总值。聚合函数可以应用于表中的所有行、WHERE 子句指定的表的子集或表中一组或多组行。应用聚合函数后，每组行都将生成一个值 * 聚合函数练习 Select Avg(Price2) From Hotel Select Avg(Price2) From Hotel Where Hlevel=‘三星级’ Select Count(*) From Hotel Where Hlevel=‘三星级’ Select Avg(Price2) As 平均标间价, Count(*) As 总记录数 From Hotel Where Hlevel=‘三星级’ Select Max(Price2),Min(Price2),Sum(Price2) As 总价,Avg(Price2) As 平均标间价, Count(*) As 总记录数 From Hotel Where Hlevel=‘三星级’ * Group By和聚合函数的分组统计 ? --求各星级的标间均价 ? Select Hlevel,Avg(Price2) From Hotel Group By Hlevel ? ? --求各地登记的景点数 ? Select Count(*) As 景点数 From Place Group by AreaId Select AreaId ,Count(*) As 景点数 From Place Group by AreaId Select AreaName ,Count(*) As 景点数 From Place,Area where Area.Ar