4.查询语句和视图.ppt

第六章 SELECT语句和视图 4.1 单表查询 4.2 数据源中数据表的各种连接 4.3 子查询及其逻辑运算符 4.4 关系集合运算的实现 4.5 视图 4.6 典型查询实例分析 4.7 查询语句小结 4.1 单表查询 查询语句的基本格式是： SELECT 查询列 [FROM 数据源] [WHERE 行条件表达式] [GROUP BY 分组依据] [HAVING 组选择条件] [ORDER BY 排序依据] 进货单明细表(BuyDetail) 单表查询实例：基本查询： SELECT * FROM BuyDetail SELECT BuyNo,GoodsNo,Unit,Quantity FROM BuyDetail SELECT DISTINCT BuyNo FROM BuyDetail SELECT *,Price*Quantity FROM BuyDetail SELECT *,Price*Quantity AS Amount FROM BuyDetail SELECT GoodsNo,Price,Quantity FROM BuyDetail WHERE BuyNo=B09002 AND GoodsNo LIKE 03% AND Price200 分组统计： SELECT SUM(Price*Quantity) FROM BuyDetail SELECT COUNT(*) FROM BuyDetail SELECT COUNT(DISTINCT BuyNo) FROM BuyDetail SELECT BuyNo,SUM(Price*Quantity) FROM BuyDetail GROUP BY BuyNo SELECT BuyNo,SUM(Price*Quantity) FROM BuyDetail GROUP BY BuyNo HAVING SUM(Price*Quantity)40000 排序和综合： SELECT * FROM BuyDetail ORDER BY BuyNo,Price DESC SELECT BuyNo,SUM(Price*Quantity) FROM BuyDetail GROUP BY BuyNo HAVING SUM(Price*Quantity)40000 ORDER BY SUM(Price*Quantity) 或：ORDER BY 可用ORDER BY 2取代 SELECT BuyNo,SUM(Price*Quantity) FROM BuyDetail WHERE Price100 GROUP BY BuyNo HAVING SUM(Price*Quantity)40000 ORDER BY 2 4.2 数据源中数据表的各种连接 交叉连接：即两个表的笛卡尔积运算 内连接：为缺省连接，返回所有符合连接条件的行 左（右）外连接：结果集为满足连接条件的行+所有左（右）表不满足连接条件的行，这些行对应右表的列值均为空 全连接：返回行中包括左右两边表不满足连接条件的行，即返回行一定包含左右两边表的所有行。 自连接：自连接并不是一种新的连接类型，当连接的两个表是同一张表，就称为自连接。 例：交叉连接和内连接 交叉连接：获得所有学生和课程的组合 SELECT StdName,EleName FROM Student,Elective 或： SELECT StdName,EleName FROM Student CROSS JOIN Elective 内连接：查询所有学生的姓名和所选课程名 SELECT a.StdName,b.EleName FROM Student a,Elective b,Student_Elective c WHERE a.StdId=c.StdId AND b.EleId=c.EleId 或： SELECT a.StdName,c.EleName FROM Student a JOIN Student_Elective b ON a.StdId=b.StdId JOIN Elective c ON c.EleId=b.EleId 例：外连接 查询所有学生的姓名以及选修的课程号，对没有选课的学生，课程号显示为NULL，其查询语句为： SELECT a.StdName,b.EleId FROM Student a LEFT JOIN Student_Elective b ON a.StdId=b.StdId 或： SELECT a.StdName,b.EleId FROM Student a,Student_Elective b WHERE a.StdId*=b.StdId 或： SELECT a.S