查询数据.

Presentation on theme: "查询数据."— Presentation transcript:

1 查询数据

2 主要内容 SELECT语句基本结构 简单查询 高级查询 重点、难点 使用表的连接进行多表查询 子查询

3 项目要求 对sportsmeet数据库中的athlete表、
score表、item等表进行简单查询、模糊查询、分组查询以及联接查询和子查询 。

4 SELECT语句基本结构 SELECT语句语法 SELECT [ALL|DISTINCT] 〈目标列表达式〉[，〈目标列表达式〉]
FROM 〈表名或视图名〉[，〈表名或视图名〉] [WHERE 〈条件表达式〉] [GROUP BY 〈列名1〉 [HAVING 〈条件表达式〉]] [ORDER BY 〈列名2〉 [ASC | DESC]

5 简单查询 —选择列 所有列 指定列 使用*，列出所有字段名称 例：查询athlete表中所有运动员的详细信息 列出部分字段名称
简单查询 —选择列 所有列 使用*，列出所有字段名称 例：查询athlete表中所有运动员的详细信息 SELECT *FROM athlete 指定列 列出部分字段名称 SELECT * FROM Goods 例：查询athlete表中所有运动员编码、姓名和性别列的信息。 SELECT a_athleteid, a_name, a_sex FROM athlete

6 简单查询 —选择列 计算列 使用别名 计算表达式 例：查询所有运动员的号码、姓名和年龄 原字段名 AS 字段别名
简单查询 —选择列 计算列 计算表达式 例：查询所有运动员的号码、姓名和年龄 SELECT a_athleteid, a_name, YEAR(getdate())-YEAR(a_birth) FROM athlete 使用别名 原字段名 AS 字段别名 SELECT g_ID,g_Name,g_Price*g_Number FROM Goods SELECT g_ID AS 商品号,g_Name AS 商品名称, g_Price*g_Number AS 总价值 FROM Goods 例：查询所有运动员的号码、姓名和年龄同时以汉字标题来表示 SELECT a_athleteid AS 运动员编号, a_name AS 姓名, YEAR(getdate())-YEAR(a_birth) AS 生日 FROM athlete

7 简单查询 —选择行 简单条件查询 复合条件查询 例：查询athlete表T002组的运动员信息。 AND和OR可用来联结多个查询条件
简单查询 —选择行 简单条件查询 例：查询athlete表T002组的运动员信息。 SELECT * FROM athlete WHERE a_teamid='T002' 复合条件查询 AND和OR可用来联结多个查询条件 SELECT * FROM Goods WHERE g_Status = '热点‘ SELECT c_ID ,c_Name ,c_Gender ,c_Address,year(getdate())-year(c_Birth) FROM Customers WHERE (c_Gender='女' AND LEFT(c_Address,4)='湖南')OR(year(getdate())-year(c_Birth)<30) 例：查询athlete表T002组的女运动员编号,姓名和籍贯。 SELECT a_athleteid,a_name,a_address FROM athlete WHERE a_teamid=‘t002’ AND a_sex=‘女’

8 简单查询 —选择行 指定范围查询 指定集合查询 例:查询年龄在18～22岁的运动员的名称、籍贯和年龄(age)
简单查询 —选择行 指定范围查询 例:查询年龄在18～22岁的运动员的名称、籍贯和年龄(age) SELECT a_Name,a_Address,Year(GetDate())-Year(a_Birth) as age FROM athlete WHERE Year(GetDate())-Year(a_Birth) BETWEEN 18 AND 22 指定集合查询 SELECT c_Name,c_Address,Year(GetDate())-Year(c_Birth) as age FROM Customers WHERE Year(GetDate())-Year(c_Birth) BETWEEN 20 AND 25 例:查询籍贯为“湖南”或“广东”运动员的详细信息 SELECT * FROM athlete WHERE LEFT(c_Address,2) IN (‘广东','湖南')

9 简单查询 —选择行 字符匹配 例:查询所有姓李的运动员的详细信息 [NOT] LIKE ‘〈模式串〉‘ [ESCAPE ‘〈转义字符〉‘]
简单查询 —选择行 字符匹配 [NOT] LIKE ‘〈模式串〉‘ [ESCAPE ‘〈转义字符〉‘] _：代表任意单个字符 %：代表任意长度（长度可以为0）的字符串 [ ]：代表指定范围(如[x-z])或集合(如[aceg])中的任意一个字符 [^]：代表不属于指定范围(如[^x-z])或集合(如[^aceg])的任意一个字符 例:查询所有姓李的运动员的详细信息 SELECT * FROM athlete WHERE a_Name LIKE ‘李%'

10 例:在athlete表中，查询姓“李”且名只有一个汉字的运动员的a_athleteid和a_name信息。
SELECT a_athleteid, a_name FROM athlete WHERE a_name LIKE '李_' 例:在athlete表中，查询姓名中第二个字为“丽”字的运动员a_athleteid和a_name信息。 SELECT a_athleteid, a_name FROM athlete WHERE a_name LIKE '_丽%' 例:查询运动员号的最后一位数字不是2-4的运动员信息 SELECT c_Name,c_TrueName,c_Phone,c_ FROM Customers WHERE c_TrueName LIKE '吴_‘ SELECT c_Name,c_TrueName,c_Phone,c_ FROM Customers WHERE c_TrueName LIKE '_欢%' SELECT * FROM Customers WHERE c_ID like '____[^2-4]' SELECT * FROM athlete WHERE a_athlete like '____[^2-4]'

11 简单查询 —选择行 涉及空值的查询 例： 查询运动员姓名中包含%的运动员信息 例：查询暂时没有成绩的运动员编号。
简单查询 —选择行 例： 查询运动员姓名中包含%的运动员信息 SELECT * FROM athlete WHERE a_Name LIKE ‘%\%%' ESCAPE '\' 涉及空值的查询 例：查询暂时没有成绩的运动员编号。 SELECT g_Price,g_Discount FROM Goods WHERE g_Name LIKE '三星SGH\_C408' ESCAPE '\' SELECT s_athleteid FROM score WHERE s_score IS NULL

12 简单查询 —选择行 消除重复取值行 前N行 DISTINCT 例：查询参加比赛的运员编号 TOP n [percent]
简单查询 —选择行 消除重复取值行 DISTINCT 例：查询参加比赛的运员编号 SELECT DISTINCT s_athleteid FROM score 前N行 TOP n [percent] SELECT DISTINCT c_ID FROM Orders 例:查询前3个运动员的详细信息。 SELECT TOP 3 * FROM athlete

13 简单查询 —排序 order by asc、desc 例：查询I006的项目成绩信息，先按成绩列降序排列。
简单查询 —排序 order by asc、desc 例：查询I006的项目成绩信息，先按成绩列降序排列。 SELECT s_athleteid,s_itemid,s_grade FROM score WHERE s_itemid='I006' ORDER BY s_grade 例：查询I006的项目成绩信息，先按成绩列降序排列，然后再按运动员号升序排列。 SELECT g_ID,g_Name,g_Price FROM Goods WHERE t_ID='01' ORDER BY g_Price DESC SELECT s_athleteid,s_itemid,s_grade FROM score WHERE s_itemid='I006' ORDER BY s_grade DESC,s_athleteid

14 简单查询 TOP...WITH TIES 例：查询I006的项目成绩信息的前4行，按成绩列降序排列。
SELECT TOP 4 s_athleteid,s_itemid,s_grade FROM score WHERE s_itemid='I006' ORDER BY s_grade DESC SELECT TOP 3 g_ID,g_Name,g_Price FROM Goods WHERE t_ID='01' ORDER BY g_Price DESC SELECT TOP 4 WITH TIES s_athleteid,s_itemid,s_grade FROM score WHERE s_itemid='I006' ORDER BY s_grade DESC

15 简单查询 聚合函数 SUM,AVG,MAX,MIN,COUNT 例：计算score表中，I001项目的所有成绩总和平均成绩。
SELECT SUM(s_grade) AS 总成绩, AVG(s_grade) AS 平均成绩 FROM score WHERE s_itemid='I001' 例：计算score表中，I001项目的最好成绩和最差成绩的差值。 SELECT MAX(s_grade) AS -MIN(s_grade) FROM score WHERE s_itemid='I002' SELECT TOP 3 g_ID,g_Name,g_Price FROM Goods WHERE t_ID='01' ORDER BY g_Price DESC 例：统计athlete表中，运动员的数量。 SELECT COUNT(*) AS 运动员总数 FROM athlete

16 简单查询 —分组 Group by …having 例:查询athlete表中，各组运动员的数量。
简单查询 —分组 Group by …having 例:查询athlete表中，各组运动员的数量。 SELECT a_teamid, COUNT(a_athleteid) AS 人数 FROM athlete GROUP BY a_teamid 例:查询athlete表中，运动员的数量大于3的组的信息。 SELECT t_ID 类别号, COUNT(t_ID) 商品数 FROM Goods GROUP BY t_ID SELECT a_teamid, COUNT(a_athleteid) AS 人数 FROM athlete GROUP BY a_teamid HAVING COUNT(a_athleteid)>3

17 简单查询 —分组 Group by …having
简单查询 —分组 Group by …having 例:查询项目参赛人数大于3的每个项目参赛人数,最好成绩,和最差成绩信息，并按项目人数升序排列。 SELECT s_itemid, count(s_athleteid) ,max(s_grade),min(s_grade) FROM score GROUP BY s_itemid HAVING count(s_athleteid) >3 ORDER BY count(s_athleteid) SELECT o_ID 订单编号, sum(d_Price*d_Number) 总金额 FROM OrderDetails GROUP BY o_ID HAVING sum(d_Price*d_Number) >5000

18 简单查询 —分组 WITH CUBE 例:查询athlete表中，各组中男、女运动员的数量及汇总值。
简单查询 —分组 WITH CUBE 例:查询athlete表中，各组中男、女运动员的数量及汇总值。 SELECT a_teamid,a_sex,COUNT(a_athleteid) AS 人数 FROM athlete GROUP BY a_teamid,a_sex WITH rollup SELECT o_ID,d_Price,d_Number FROM OrderDetails ORDER BY o_ID COMPUTE SUM(d_Price),SUM(d_Number)

19 简单查询 —数据汇总 COMPUTE BY 例:在score表中，查询I001项目的成绩信息，并求出平均成绩、最大值、最小值。
简单查询 —数据汇总 COMPUTE BY 例:在score表中，查询I001项目的成绩信息，并求出平均成绩、最大值、最小值。 SELECT s_athleteid, s_itemid, s_grade FROM score WHERE s_itemid='I001' COMPUTE AVG(s_grade),MAX(s_grade),MIN(s_grade) SELECT o_ID,d_Price,d_Number FROM OrderDetails ORDER BY o_ID COMPUTE SUM(d_Price),SUM(d_Number) SELECT s_athleteid, s_itemid, s_grade FROM score ORDER BY s_itemid COMPUTE AVG(s_grade),MAX(s_grade),MIN(s_grade) BY s_itemid

20 教学示例数据库（sportsmeet）P68
athlete a_athleteid （运动员编号） a_name （名称） a_teamid （运动队编号） a_address （籍贯） … a001 韦洁名 01 广东 a002 余佳 06 广西 team t_teamid (运动队编号) t_teamname (运动队名称) t_count (人数) t001 红牛 … t002 火箭 t003 时空

21 athlete.a_teamid=team.t_teamid
联接查询 —内联接 联接过程 athlete.a_teamid=team.t_teamid a_athleteid （运动员编号） a_name （名称） a_teamid （运动队编号） … a001 韦洁名 t001 a002 余佳 t003 t_teamid (运动队编号) t_teamname (运动队名称) t_count (人数) t001 红牛 略 t002 火箭 team athlete 联接结果 a_athleteid （运动员编号） a_name （名称） a_teamid （运动队编号） … t_teamid (运动队编号) t_teamname (运动队名称) t_count (人数) a001 韦洁名 t001 红牛 略 athlete team

22 联接查询 —内联接 内联接语法 例:需要了解查询所有运动员报名参加比赛项目的情况,包括运动员姓名,项目编号,成绩。
联接查询 —内联接 内联接语法 FROM 表名1 [INNER JOIN] 表名2 ON 连接条件 例:需要了解查询所有运动员报名参加比赛项目的情况,包括运动员姓名,项目编号,成绩。 SELECT Goods.g_ID, Goods.t_ID, Types.t_Name, Goods.g_Name FROM Goods JOIN Types ON Goods.t_ID= Types. t_ID SELECT a_name, s_itemid, s_grade FROM athlete JOIN score ON a_athleteid = s_athleteid

23 想一想 需要了解所有运动员报名参加比赛项目的情况,包括运动员姓名,项目名称,成绩。
SELECT a_athleteid, a_name, i_itemname, s_grade FROM athlete JOIN score ON a_athleteid = s_athleteid JOIN item ON s_itemid=i_itemid

24 athlete.a_teamid=team.t_teamid
联接查询 —左外联接 联接过程 athlete.a_teamid=team.t_teamid a_athleteid （运动员编号） a_name （名称） a_teamid （运动队编号） … a001 韦洁名 t001 a002 余佳 t003 t_teamid (运动队编号) t_teamname (运动队名称) t_count (人数) t001 红牛 略 t002 火箭 team athlete 联接结果 a_athleteid （运动员编号） a_name （名称） a_teamid （运动队编号） … t_teamid (运动队编号) t_teamname (运动队名称) t_count (人数) a001 韦洁名 t001 红牛 略 a002 余佳 t003 null athlete team

25 联接查询 —左外联接 左外联接语法 例:需要了解所有运动员报名参加比赛项目的情况，如果有运动员没有报名参赛也需要显示其基本信息。
联接查询 —左外联接 左外联接语法 FROM 表名1 LEFT JOIN 表名2 ON 连接条件 例:需要了解所有运动员报名参加比赛项目的情况，如果有运动员没有报名参赛也需要显示其基本信息。 SELECT a_athleteid, a_name, s_itemid, s_grade FROM athlete LEFT JOIN score ON a_athleteid = s_athleteid

26 athlete.a_teamid=team.t_teamid
联接查询 —右外联接 联接过程 athlete.a_teamid=team.t_teamid a_athleteid （运动员编号） a_name （名称） a_teamid （运动队编号） … a001 韦洁名 t001 a002 余佳 t003 t_teamid (运动队编号) t_teamname (运动队名称) t_count (人数) t001 红牛 略 t002 火箭 team athlete 联接结果 a_athleteid （运动员编号） a_name （名称） a_teamid （运动队编号） … t_teamid (运动队编号) t_teamname (运动队名称) t_count (人数) a001 韦洁名 t001 红牛 略 null t002 火箭 athlete team

27 联接查询 —右外联接 右外联接语法 例:需要了解所有运动员报名参加比赛项目的情况，如果有运动员没有报名参赛也需要显示其基本信息。
联接查询 —右外联接 右外联接语法 FROM 表名1 RIGHT JOIN 表名2 ON 连接条件 例:需要了解所有运动员报名参加比赛项目的情况，如果有运动员没有报名参赛也需要显示其基本信息。 SELECT a_athleteid, a_name, s_itemid, s_grade FROM score RIGHT JOIN athlete ON a_athleteid = s_athleteid

28 联接查询 —交叉联接和自联接 交叉联接（CROSS JOIN） 自联接 例:对athlete表和team表进行交叉连接。
联接查询 —交叉联接和自联接 交叉联接（CROSS JOIN） 例:对athlete表和team表进行交叉连接。 SELECT * FROM athlete CROSS JOIN team 自联接 例:需要了解比“李巧良”年龄大的运动员的号码、姓名和出生年份，查询结果按出生年月升序排序。 SELECT G2.g_ID 商品号,G2.g_Name 商品名称,G2.g_Price 价格 FROM Goods G1 JOIN Goods G2 ON G1.g_Name='三星SGH_C178' AND G1.g_Price<=G2.g_Price ORDER By G2.g_Price SELECT X1.a_athleteid, X1.a_name, YEAR(X1.a_birth) FROM athlete AS X1 JOIN athlete AS X2 ON X2.a_name = '李巧良' AND X1.a_birth<X2.a_birth ORDER BY X1.a_birth

29 子查询 子查询 父查询 athlete Select…… Select…… From…… From…… Where……
a_athleteid （运动员编号） a_name （名称） a_teamid （运动队编号） a_address （籍贯） … a001 韦洁名 01 广东 a002 李巧良 06 子查询 父查询 Select…… From…… Where…… Select…… From…… Where 字段1 >{ } Select后不能借别的字段

30 子查询 使用IN或NOT IN的子查询 例:需要了解与“李巧良”同个地址的运动员信息。 SELECT a_address
FROM athlete WHERE a_name='李巧良' step1 SELECT * FROM athlete step2 SELECT g_ID,g_name,t_ID FROM Goods WHERE t_ID in (SELECT t_ID FROM Goods WHERE g_name='摩托罗拉W380') WHERE a_address IN ( ) = ‘广东广州'

31 想一想 需要了解参加了“女子跳高”项目的运动员姓名、性别和生日。 SELECT a_name,a_sex,a_birth
FROM athlete WHERE s_athleteid IN (SELECT s_athleteid FROM score WHERE i_itemid IN (SELECT i_itemid FROM item WHERE i_itemname='女子跳高')) SELECT o_ID,o_Date,o_Sum FROM orders WHERE o_id IN (SELECT o_id FROM orderdetails WHERE g_id IN (SELECT g_id FROM goods WHERE g_name='红双喜牌兵乓球拍'))

32 子查询 使用比较运算符的子查询 例:查询红牛队的队员人数 SELECT count(*) AS 红牛队人数 FROM athlete
WHERE a_teamid=(SELECT t_teamid FROM team WHERE t_name='红牛') SELECT count(distinct o_ID) AS 订单数 FROM Orderdetails WHERE g_id=(SELECT g_ID FROM Goods WHERE g_name='红双喜牌兵乓球拍')

33 子查询 使用ANY或ALL的子查询 例:需要了解查询比T001组任一运动员小的会员信息，查询结果按降序排列。
SELECT a_athleteid, a_name, a_birth FROM athlete WHERE a_birth> ALL( SELECT a_birth FROM athlete WHERE a_teamid = 'T001' ) AND a_teamid <> 'T001' ORDER BY a_birth DESC SELECT c_ID,c_Name,YEAR(GETDATE())-YEAR(c_Birth) Age,c_Address FROM Customers WHERE LEFT(c_Address,4)<>'湖南长沙' AND c_Birth>ALL(SELECT c_Birth FROM Customers WHERE LEFT(c_Address,4)= '湖南长沙') ORDER BY Age DESC

34 子查询 使用EXISTS的子查询 例：查询所有报名参加了“I001”项目的运动员a_athleteid和a_name信息。
SELECT a_athleteid, a_name FROM athlete WHERE EXISTS( SELECT * FROM score WHERE a_athleteid=s_athleteid AND s_itemid='I001' ) SELECT o_id,c_id FROM orders WHERE EXISTS (SELECT * FROM orderdetails WHERE o_id= orders.o_id AND g_id='060001')

35 相关子查询 例：在score表中，查询运动员成绩比该项目平均成绩大的信息。
SELECT s_athleteid, s_itemid, s_grade FROM score AS X1 WHERE s_grade >( SELECT AVG(s_grade) FROM score AS X2 WHERE X1.s_itemid = X2.s_itemid) SELECT * FROM goods AS A WHERE g_Price>( SELECT AVG(g_Price) FROM goods AS B WHERE B.t_ID= A.t_ID)

36 小结 简单查询方法 联接查询方法 子查询方法 联合查询方法

37 RENSHI GONGZI BUMEN

38 想一想 ？ 涉及一个表 查询RENSHI表中职工的姓名,性别和职务 查询在部门号为02中工作的人员属于哪些民族(RENSHI)
人事(部门号,职工编号,姓名,性别,民族,出生日期,学历,职称,职务,备注) 工资(部门,职工编号,基本工资,奖金,书报费,洗理费,房补,会费,住房基金) 部门(部门号,部门名) ？ 涉及一个表 查询RENSHI表中职工的姓名,性别和职务 查询在部门号为02中工作的人员属于哪些民族(RENSHI) 查询基本工资大于400的职工编号(GONGZI) 查询部门号和按部门号将职工的奖金汇总的金额 (GONGZI , 到SUM函数) 查询职工名称中含有“社”字的职工信息 将职工工资表按基本工资多少降序排列

39 想一想 人事(部门号,职工编号,姓名,性别,民族,出生日期,学历,职称,职务,备注) 工资(部门,职工编号,基本工资,奖金,书报费,洗理费,房补,会费,住房基金) 部门(部门号,部门名) ？ 涉及多个表 查询奖金在300以上的职工编号和部门名称(用INNER JOIN,LEFT JOIN,RIGHT JOIN 分别实现) 查询GONGZI基本工资高于各部门平均工资的职工编号

40 想一想 LIKE匹配字符有哪几种？如果要查询的字符中包含匹配字符应该如何处理？ 进行连接查询时应注意什么？ ？