软件开发技术基础第2版课件作者赵英良第4章节数据库及应用程序开发2_SQL.pptVIP

按商店名称排序降序排序 SELECT stor_name, city FROM stores ORDER BY store_name DESC ?按商店名称排序升序排序 SELECT stor_name, city FROM stores ORDER BY store_name ASC */57 用多个列排列 SELECT stor_name, city FROM stores ORDER BY stor_name， city ?用不出现的列 SELECT stor_name, city FROM stores ORDER BY state */57 (6) 统计函数 计算图书的平均定价 SELECT AVG(price) FROM titles ?计算type=’business’的图书的最高价格 SELECT MAX(price) FROM titles WHERE type=’business’ */57 多个计算列用逗号隔开 SELECT AVG(qty), SUM(qty), COUNT(DISTICT stor_id) FROM sales ?统计满足条件的记录个数 SELECT COUNT（*） FROM publishers WHERE state=’CA’ */57 (7) 结果分组 GROUP BY 分组列名表 SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE type in (business, mod_cook, trad_cook) GROUP BY type */57 将满足条件的记录分组统计 SELECT type, pub_id, AVG(price), SUM(price), COUNT(*) FROM titles WHERE type in (‘business’, ‘mod_cook’, ‘trad_cook’) GROUP BY type， pub_id GROUP BY 分组列名表 HAVING 条件 对GROUP的分组结果再进一步筛选。 SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE price10 GROUP BY type 使用HAVING SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE price$10 GROUP BY type HAVING AVG(price)20 使用HAVING，显示平均价格在20以上的分组 (8) 多表查询 （a）笛卡尔乘积 SELECT title_id, au_fname FROM titles, authors SELECT titles.title_id, titles.title, titleauthor.au_id FROM titles, titleauthor */57 （b ）别名 SELECT t.title_id, t.title, ta.au_id FROM titles t, titleauthor ta 等值连接 SELECT t.title_id, t.title, ta.au_id FROM titles t, titleauthor ta WHERE t.title_id=ta.title_id */57 （c）筛选 三个表格的连接 SELECT t.title_id, a.au_id FROM titles t, titleauthor ta, authors a WHERE t.title_id=ta.title_id and ta.au_id=a.au_id */57 6 视图 (1）视图的定义 从一个或多个表中导出的虚表 SQL建立视图的语句格式为： CREATE VIEW 视图名[(列名1[，列名]…)] AS 查询 */57 举例 CREATE VIEW au_view AS SELECT au_id, au_fname, au_lname FROM authors CREATE VIEW author_title AS SELECT t.title_id, a.au_id FROM titles t, titleauthor ta, authors a WHERE t.title_id=ta.title_id a