SQL基本SELECT查询语句内外连接PPT课件

1、2021-10-23Microsoft SQL Server 20051SELECT语法总结 SELECT */字段名,字段名 FROM 表名 WHERE 条件表达式 GROUP BY 分组的字段名 HAVING 条件表达式 ORDER BY 排序的字段名 ASC | DESC 上述“ ”表示可选项，“ | ”表示取其中之一。第1页/共66页2021-10-23Microsoft SQL Server 20052简单Select 查询 语法： SELECT 字段1,字段2, FROM 表名 SELECT * FROM 表名 SELECT 字段1,字段2, FROM 表名 WHERE 查询条件

2、SELECT * FROM 表名 WHERE 查询条件 简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。 -第2页/共66页2021-10-23Microsoft SQL Server 20053简单Select 查询SELECT StuId,StuName,DeptId,Sex FROM Tb_StudentSELECT * FROM Tb_Student第3页/共66页2021-10-23Microsoft SQL Server 20054简单Select 查询SELECT StuId,StuName,D

3、eptId,Sex FROM Tb_Student WHERE Sex=FSELECT * FROM Tb_Teacher WHERE Sex=F and DeptId= 08 第4页/共66页2021-10-23Microsoft SQL Server 20055WHERE子句使用的运算符类别类别运算符运算符说明说明比较运算符=、=、=、比较两个表达式逻辑运算符AND、OR、NOT组合两个表达式的运算结果或取反范围运算符BETWEEN、NOT BETWEEN查询值是否在范围内列表运算符IN、NOT IN查询值是否属于列表值之一字符匹配符LIKE、NOT LIKE字符串是否匹配未知值IS NU

4、LL、IS NOT NULL查询值是否为NULL第5页/共66页2021-10-23Microsoft SQL Server 20056比较运算符和表达式 运运 算算 符符含含 义义=等于大于=大于等于=小于等于不等于SELECT * FROM Tb_Student WHERE StuName张三SELECT * FROM Tb_Student WHERE Sex= F第6页/共66页2021-10-23Microsoft SQL Server 20057逻辑运算符和表达式 运算符运算符含含 义义AND如果两个布尔表达式都为 TRUE，那么就为 TRUEOR如果两个布尔表达式中的一个为 TRU

5、E，那么就为 TRUENOT对任何其他布尔运算符的值取反SELECT * FROM Tb_Student WHERE Sex= M and Sex= FSELECT * FROM Tb_Student WHERE ClassId= S040201101 and Sex= FSELECT * FROM Tb_Student WHERE NOT Sex= M第7页/共66页2021-10-23Microsoft SQL Server 20058范围搜索运算符和表达式 SELECT * FROM Tb_Student WHERE Age BETWEEN 20 TO 22- SELECT * FROM

6、 Tb_Student WHERE Age NOT BETWEEN 20 TO 22- SELECT * FROM Tb_Student WHERE Age IN (20,21,22)- 相当于 SELECT * FROM Tb_Student WHERE Age=20 OR Age=21 OR Age=22类别类别运算符运算符说明说明范围运算符BETWEEN、NOT BETWEEN查询值在范围内列表运算符IN、NOT IN查询值属于列表值之一第8页/共66页2021-10-23Microsoft SQL Server 20059模式匹配运算符和表达式 SELECT * FROM Tb_Stu

7、dent WHERE StuName LIKE 张%通配符通配符说明说明示例示例%匹配零个或多个字符的任意字符串。LIKE %林% 姓名中包含有“林”的信息。_下划线匹配任何单个字符。一个汉字只使用一个_表示LIKE 林_ 姓名第一个字为“林”并且名字只有两个字。 匹配指定范围 (a-f) 或集合(abcdef) 中的任何单个字符。LIKE 张王% 姓张或姓王的信息。匹配不属于指定范围 (a-f) 或集合 (abcdef) 中的任何单个字符。LIKE 王林% 姓王且第二个字不是林的信息。SELECT * FROM Tb_Student WHERE StuName NOT LIKE 张三第9页/

8、共66页2021-10-23Microsoft SQL Server 200510未知值条件 NULL表示未知的，不可用或将在以后添加的数据。NULL值与零、零长度的字符串的含义不同。 在WHERE子句中，使用IS NULL或IS NOT NULL条件可以查询某一数据值是否为NULL的数据信息。 例如：SELECT * FROM Tb_Grade WHERE RetestScore IS NOT NULLSELECT * FROM Tb_CourseWHERE CourseName IS NULL第10页/共66页2021-10-23Microsoft SQL Server 200511字符串

9、运算符和表达式 字符串表达式是用字符串连接符（）将字符型常量、变量、字符串函数连接组成的式子。字符串表达式的计算结果包括两个字符串内的空格和所有字符，仍然是字符型。 如 我爱+ 我家 表达式的值为: 我爱 我家 - 例如：Beijing+ Huanying + Ning! 请写出结果！第11页/共66页2021-10-23Microsoft SQL Server 200512运算符的优先级 运算符的优先级决定了表达式的计算和比较操作顺序。运算符的优先级由高到低的顺序为： 1）括号：（）。 2）正、负或取反运算：+、-、。 3）乘、除、求模运算：*、/、。 4）加、减、字符连接运算：+、-、+。

10、 5）比较运算：、!、!、!。 6）位运算：、|。 7）逻辑非运算：NOT。 8）逻辑与运算：AND。 9） BETWEEN、IN、LIKE、OR等运算。 10）赋值运算：。第12页/共66页2021-10-23Microsoft SQL Server 200513ORDER BY子句 使用SELECT语句进行数据查询，为了按一定顺序浏览数据，可以使用ORDER BY子句对生成的查询结果进行排序。ORDER BY子句在SELECT语句中的语法格式如下：- SELECT 字段1,字段2, FROM 表名 WHERE 查询条件 ORDER BY 排序表达式 ASC | DESC第13页/共66页2

11、021-10-23Microsoft SQL Server 200514ORDER BY子句SELECT StuId,StuName,Sex FROM Tb_Student WHERE DeptId=08 ORDER BY StuNameSELECT StuId,StuName,Sex FROM Tb_Student WHERE DeptId=08 ORDER BY StuName DESC第14页/共66页2021-10-23Microsoft SQL Server 200515ORDER BY子句SELECT StuId,StuName,Sex FROM Tb_Student WHERE

12、DeptId=08 ORDER BY StuName,Sex 结果如何？第15页/共66页2021-10-23Microsoft SQL Server 200516 ORDER BY子句 ORDER BY子句总是位于WHERE子句（如果有的话）后面，可以包含一个或多个列，每个列之间以逗号分隔。这些列可能是表中定义的列，也可能是SELECT子句中定义的计算列。 - 在SQL Server 2005中，汉字比其它字符值都要大，而汉字之间的大小比较是取决于其拼音首字母的大小。 如果没有特别说明，值是以升序序列进行排序的。当然，你也可以选择使用ASC关键字指定升序排序。 - 如果你希望以降序排列，则在

13、ORDER BY后面使用DESC关键字。 第16页/共66页2021-10-23Microsoft SQL Server 200517GROUP BY 子句 使用SELECT语句进行数据查询，为了按特定条件分组浏览数据，可以使用GROUP BY子句对某一列数据的值进行分类，形成结果集。GROUP BY子句在SELECT语句中的语法格式如下：- SELECT 字段1,字段2, FROM 表名 WHERE 查询条件 GROUP BY 字段1,字段2, WITH ROLLUP | CUBE ORDER BY 排序表达式 ASC | DESC- CUBE 生成的结果集显示了所选列中值的所有组合（包括字

14、段1、字段2、字段1+字段2）的分组统计结果和总的汇总统计的聚合；ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。（包括字段1、字段1+字段2的分组统计结果和总的汇总统计的聚合）第17页/共66页2021-10-23Microsoft SQL Server 200518聚合函数 聚合函数是SQL语言中一种特殊的函数，比如SUM, COUNT, MAX, MIN, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。换句话说，上面几个返函数的值建立在对多条记录进行统计的基础上。 SUM是求和函数。 SELECT ClassId,SUM(Score) FROM Tb_

15、Grade WHERE ClassId=S0408011 COUNT函数用来计算记录的数量。 Select COUNT(StuID) FROM Tb_Grade WHERE ClassId=S0408011 MAX, MIN, AVG函数分别是用来求最大值、最小值和平均值。 SELECT MAX(Score), MIN(Score), AVG(Score) FROM Tb_Grade WHERE ClassId=S0408011第18页/共66页2021-10-23Microsoft SQL Server 200519GROUP BY 子句 常用统计函数函数名功能COUNT求组中项数SUM求和

16、，返回表达式中所有值的和AVG求均值，返回表达式中所有值的平均值MAX求最大值，返回表达式中所有值的最大值MIN求最小值，返回表达式中所有值的最小值ABS求绝对值，返回表达式的绝对值ASCII求ASCII码，返回字符型数据的ASCIIRAND产生随机数，返回一个位于0和1之间的随机数第19页/共66页2021-10-23Microsoft SQL Server 200520GROUP BY子句-显示每个学生的班级编码和成绩SELECT ClassId,TotalScore FROM Tb_Grade-汇总每个班级的平均成绩SELECT ClassId,AVG(TotalScore)FROM T

17、b_GradeGROUP BY ClassId第20页/共66页2021-10-23Microsoft SQL Server 200521-SELECT ClassId,ClassName,AVG(TotalScore)FROM Tb_Grade,Tb_ClassGROUP BY ClassIdGROUP BY子句-汇总每个班级（含班级名称）的平均成绩?SELECT ClassId,ClassName,AVG(TotalScore)FROM Tb_GradeGROUP BY ClassId-SELECT Tb_Grade.ClassId,ClassName,AVG(TotalScore)FRO

18、M Tb_Grade,Tb_ClassWHERE Tb_Grade.ClassId=Tb_Class.ClassIdGROUP BY Tb_Grade.ClassId第21页/共66页2021-10-23Microsoft SQL Server 200522GROUP BY子句 -显示班级名称的汇总各班平均成绩 SELECT ClassId,ClassName,AVG(TotalScore) AS AvgScore FROM Tb_Grade,Tb_Class WHERE ClassId=ClassId GROUP BY ClassId,ClassName当两个表中都有ClassId字段时，要

19、加上表前缀，说明是那个表的ClassId字段SELECT列表中出现的字段都必须要在GROUP BY列表中出现，聚合函数字段除外当要显示相关主键表中的其它字段时，如这儿的ClassName字段，则在WHERE子句中通过主外键关系进行连接第22页/共66页2021-10-23Microsoft SQL Server 200523GROUP BY 子句 查询各个班级的最大成绩和最小成绩？ SELECT Tb_Grade.ClassId,ClassName,MAX(TotalScore) AS MaxScore, MIN(TotalScore) AS MinScore FROM Tb_Grade,Tb

20、_Class WHERE Tb_Grade.ClassId=Tb_Class.ClassId GROUP BY Tb_Grade.ClassId,ClassName第23页/共66页2021-10-23Microsoft SQL Server 200524GROUP BY 子句 GROUP BY分类汇总，分项统计 紧跟在GROUP BY后面的字段就是分组条件。 如果在前面选择列表中出现的非聚合字段未出现在GROUP BY后面，SQL语句就会出错。思考题： 按照不同班级和性别统计学生平均年龄。 第24页/共66页2021-10-23Microsoft SQL Server 200525HAVIN

21、G子句 HAVING子句相当于一个用于组（分类汇总）的WHERE子句，它指定了组或聚合的查询条件。HAVING子句通常与GROUP BY子句一起使用。HAVING子句的语法为：- SELECT 字段1,字段2, FROM 表名 WHERE 查询条件 GROUP BY字段1,字段2, HAVING 组查询条件第25页/共66页2021-10-23Microsoft SQL Server 200526HAVING子句 显示各班级学生平均总成绩大于73的班级编号。 SELECT Tb_Grade.ClassId, AVG(TotalScore) AS AvgScore FROM Tb_Grade G

22、ROUP BY Tb_Grade.ClassId HAVING AVG(TotalScore)73第26页/共66页2021-10-23Microsoft SQL Server 200527HAVING子句 求各个班级的最大成绩和最小成绩,且最低成绩小于50分的？ SELECT Tb_Grade.ClassId, MAX(TotalScore) AS MaxScore, MIN(TotalScore) AS MinScore FROM Tb_Grade GROUP BY Tb_Grade.ClassId HAVING MIN(TotalScore)50第27页/共66页2021-10-23Mi

23、crosoft SQL Server 200528HAVING子句 HAVING子句用来对分组后的数据进行筛选，它后面会跟一个条件表达式。 当在GROUP BY 子句中使用HAVING子句时，查询结果中只返回满足HAVING条件的组。第28页/共66页2021-10-23Microsoft SQL Server 200529SELECT语法总结 SELECT */字段名,字段名 FROM 表名 WHERE 条件表达式 GROUP BY 分组的字段名 HAVING 条件表达式 ORDER BY 排序的字段名 ASC | DESC 上述“ ”表示可选项，“ | ”表示取其中之一。第29页/共66页

24、2021-10-23Microsoft SQL Server 200530计算列 SELECT后面还可以是计算列。在SELECT后面的计算列，只是位于该SELECT语句中，是临时性的，它由一个表达式产生。 SELECT StuId, StuName+(+Sex+) FROM Tb_Student 上面语句中的StuName+(+Sex+)就构成了一个计算列，它的值由两个字符型值和一对括号连接组成。 第30页/共66页2021-10-23Microsoft SQL Server 200531列别名 在上面的结果集中，列名都是英文。这样看上去很不直观，我们希望能够给列名换上诸如“学号、姓名”等这样

25、的名字。只需在SELECT语句中，给列取个别名即可。AS可以省去。 SELECT StuId AS 学号, StuName+ (+Sex+) AS 姓名性别 FROM Tb_Student SELECT StuId 学号, StuName+ (+Sex+) 姓名性别 FROM Tb_Student SELECT 学号=StuId,姓名性别=StuName+(+Sex+) FROM Tb_Student第31页/共66页2021-10-23Microsoft SQL Server 200532 当然，我们也可以给表指定列别名，给表取别名和给列取别名类似，但只可以用 “原始表名 AS 别名”和 “

26、原始表名 别名”这两种方式。 SELECT * FROM Tb_Student ST 或者 SELECT * FROM Tb_Student AS ST- 字段别名不能放在WHERE子句中,而表别名可以放在WHERE子句中。 表别名第32页/共66页2021-10-23Microsoft SQL Server 200533多表查询第33页/共66页2021-10-23Microsoft SQL Server 200534 查询全院各系部拥有班级的基本情况，显示系部名称、班级编码和班级名称； 多表查询第34页/共66页2021-10-23Microsoft SQL Server 200535 U

27、SE Teaching_System GO SELECT d.*, c.* FROM Tb_Class AS c, Tb_Dept AS d 多表查询第35页/共66页2021-10-23Microsoft SQL Server 200536 笛卡尔乘积多表查询第36页/共66页2021-10-23Microsoft SQL Server 200537多表查询第37页/共66页2021-10-23Microsoft SQL Server 200538多表查询第38页/共66页2021-10-23Microsoft SQL Server 200539 USE Teaching_System GO

28、 SELECT d.*, c.* FROM Tb_Class AS c, Tb_Dept AS d WHERE c.DeptId=d.DeptId多表查询第39页/共66页2021-10-23Microsoft SQL Server 200540多表查询 SELECT StuId,StuName,DeptId,SpecId,ClassId,Sex FROM Tb_Student SELECT StuId 学号,StuName 姓名,DeptId 系部,SpecId 专业,ClassId 班级,Sex 性别 FROM Tb_Student第40页/共66页2021-10-23Microsoft

29、SQL Server 200541两表连接 SELECT StuId 学号,StuName 姓名,DeptName 系部, SpecId 专业,ClassId 班级,Sex 性别 FROM Tb_Student TS,Tb_Dept TD WHERE TS.DeptId=TD.DeptId第41页/共66页2021-10-23Microsoft SQL Server 200542多表连接基础 在实际查询应用中，用户所需要的数据并不全部在一个表中，这时就需要多表查询，从多个表中抽取我们所需要的数据信息。 多表查询实际上是通过各个表之间的共同列的相关性（主外键关系）来查询数据的，这是关系数据库查询

30、数据的主要特征。 多表查询首先要用WHERE子句在表之间建立连接关系。 在进行多表查询时，可以遵循以下基本原则： （1）SELECT子句列表中，字段前要加上基表名称； （2）FROM子句应包括所有使用的基表； （3）WHERE子句应在表之间建立连接关系。第42页/共66页2021-10-23Microsoft SQL Server 200543两表连接 SELECT StuId 学号,StuName 姓名,DeptId 系部编码, DeptName 系部, SpecId 专业,ClassId 班级,Sex 性别 FROM Tb_Student TS,Tb_Dept TD WHERE TS.De

31、ptId=TD.DeptId SELECT StuId 学号,StuName 姓名, TS.DeptId 系部编码, DeptName 系部, SpecId 专业,ClassId 班级,Sex 性别 FROM Tb_Student TS,Tb_Dept TD WHERE TS.DeptId=TD.DeptId第43页/共66页2021-10-23Microsoft SQL Server 200544多表连接 SELECT StuId 学号,StuName 姓名,DeptName 系部, SpecName 专业,ClassName 班级,Sex 性别 FROM Tb_Student TS,Tb_

32、Dept TD,Tb_Spec TSP, Tb_Class TC WHERE TS.DeptId=TD.DeptId AND TS.SpecId=TSP.SpecId AND TS.ClassId=TC.ClassId要求显示学生姓名和所在系名、专业、班级名称?第44页/共66页2021-10-23Microsoft SQL Server 200545各种连接查询 内连接：内连接是一种常用的数据连接查询方式。它使用比较运算符进行多个表之间的比较连接操作。内连接会消除与另一个表的任何不匹配的数据行。 外连接：外连接会返回FROM子句中提到的至少一个表的所有符合查询条件的数据行（包括连接中不匹配的

33、数据行），外连接分为左外连接、右外连接和完全连接。 交叉连接：当对两个表使用交叉连接查询时，将返回来自这两个表的各行的所有可能的数据行组合。 自连接：表自身的链接参见教材p119。第45页/共66页2021-10-23Microsoft SQL Server 200546两个样例表 SELECT * FROM Tb_Dept SELECT ClassId,ClassName,DeptId FROM Tb_Class第46页/共66页2021-10-23Microsoft SQL Server 200547内连接 SELECT DeptName 系部名称, ClassId 班级编码, Class

34、Name 班级名称 FROM Tb_Dept INNER JOIN Tb_Class ON Tb_Class.DeptId=Tb_Dept.DeptId等同于： SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM Tb_Class,Tb_Dept WHERE Tb_Dept.DeptId=Tb_Class.DeptId第47页/共66页2021-10-23Microsoft SQL Server 200548外连接-左外连接 SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM

35、 Tb_Dept LEFT OUTER JOIN Tb_Class ON Tb_Dept.DeptId=Tb_Class.DeptId 左边表中列全显示第48页/共66页2021-10-23Microsoft SQL Server 200549外连接-右外连接 SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM Tb_Dept RIGHT OUTER JOIN Tb_Class ON Tb_Dept.DeptId=Tb_Class.DeptId 右边表中列全显示第49页/共66页2021-10-23Microsoft SQL Serv

36、er 200550外连接-完全连接 SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM Tb_Dept FULL OUTER JOIN Tb_Class ON Tb_Dept.DeptId=Tb_Class.DeptId第50页/共66页2021-10-23Microsoft SQL Server 200551交叉连接 SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM Tb_Dept CROSS JOIN Tb_Class第51页/共66页2021-10-23Microso

37、ft SQL Server 200552交叉连接不使用WHERE子句时，返回的结果是一个基本表中符合条件的行数乘以另一个基本表中符合条件的行数。使用WHERE子句时，返回的结果是被连接的两个基本表所有行的笛卡尔积中满足WHERE条件的所有行的行数。参见教材p118第52页/共66页2021-10-23Microsoft SQL Server 200553联合查询（UNION） 如果有多个不同的查询结果，但又希望将这些查询结果放在一起显示，组成一组数据。在这种情况下，可以使用UNION子句。使用UNION子句的查询又称为联合查询，它可以将两个和多个查询结果集组合成为单个结果集。语法如下： SEL

38、ECT select_list FROM table_list WHERE search_conditions UNION ALL SELECT select_list FROM table_list WHERE search_conditions 其中，ALL关键字为可选的，如果在UNION子句中使用该关键字，则返回全部数据行；如果不使用该关键字，则返回结果中删除重复数据行。第53页/共66页2021-10-23Microsoft SQL Server 200554联合查询（UNION） SELECT * FROM Tb_Spec WHERE SpecId08 UNION SELECT St

39、uId,StuName,Sex FROM Tb_Student WHERE ClassIdS0408011第54页/共66页2021-10-23Microsoft SQL Server 200555章节综合练习 每个学年结束，班主任都要对班级学生成绩进行分析，然后初定学年奖学金获得者的人选。让我们运用数据库查询的知识帮助04网络1班的班主任刘老师完成以下两个任务：根据学院奖学金评选规定，初定获奖学金学生名单；（每位同学的课程平均成绩必须高于75分）对该班级开设的各门课程分别进行成绩分析。显示课程编号、学生学号和成绩的明细并分别汇总每门课程的平均成绩。 第55页/共66页2021-10-23Mi

40、crosoft SQL Server 200556章节综合练习第56页/共66页2021-10-23Microsoft SQL Server 200557任务一 USE Teaching_System GO SELECT StuId 学号, AVG(TotalScore) AS 平均成绩 FROM Tb_Grade WHERE ClassId=S0408011 GROUP BY StuId HAVING AVG(TotalScore)=75第57页/共66页2021-10-23Microsoft SQL Server 200558任务二 USE Teaching_System GO SELECT CourseId AS 课程编号, StuId AS 学号,ROUND(TotalScore,2) AS 成绩 FROM Tb_Grade WHERE ClassId=S0408011 ORDER BY CourseId COMPUTE AVG(ROUND(TotalScore,2) ) BY CourseId第58页/共66页2021-10-23