《SELECT查询语句》PPT课件.ppt

2019/6/27,Microsoft SQL Server 2005,1,Lecture 5 基本SELECT查询语句,- 目标要求： 掌握使用数据定义(DDL)定义数据库、数据表等对象。 熟练掌握简单查询、嵌套查询、连接查询、分组与计算查询、集合的并运算。,2019/6/27,Microsoft SQL Server 2005,2,结构化查询语言(SQL),结构化查询语言：一种数据库查询和编程语言，广泛应用于访问、查询、更新和管理关系数据库系统中的数据。 - SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的标准数据查询语言，IBM 公司最早使用在其开发的数据库系统中。1986年10月，美国 ANSI 对 SQL 进行规范后，以此作为关系型数据库管理系统的标准语言，1987年得到国际标准组织的支持下成为国际标准。 - SQL语言包含3个部分： 数据定义(DDL)语言(如CREATE, DROP,ALTER等语句) 数据操纵(DML)语言(SELECT,INSERT, UPDATE, DELETE语句) 数据控制语言(如GRANT,REVOKE, ROLLBACK等语句),2019/6/27,Microsoft SQL Server 2005,3,数据定义语言,数据定义语言 (Data Definition Language, DDL) 是SQL语言集中，负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。,2019/6/27,Microsoft SQL Server 2005,4,CREATE指令,CREATE是负责数据库对象的建立 CREATE DATABASE CREATE TABLE CREATE INDEX：创建数据表索引。 CREATE PROCEDURE：创建存储过程。 CREATE FUNCTION：创建用户自定义函数。 CREATE VIEW：创建视图。 CREATE TRIGGER：创建触发器。,2019/6/27,Microsoft SQL Server 2005,5,ALTER指令,ALTER ALTER 是负责数据库对象修改的指令，例如： ALTER TABLE Tb_Student ADD column_b VARCHAR(20) NULL ; - 在数据表 Tb_Student中加入一个新的字段，名称为 column_b，字段类型为 varchar(20)，允许 NULL值。 ALTER TABLE Tb_Student DROP COLUMN column_b ; - 在数据表 Tb_Student中移除 column_b 字段。,2019/6/27,Microsoft SQL Server 2005,6,DROP指令,DROP DROP 则是删除数据库对象的指令，并且只需要指定要删除的数据库对象名称即可，在 DDL 语法中算是最简单的。 例如： DROP TABLE MyTable; - 刪除 MyTable 数据表。 DROP VIEW MyView; - 刪除 MyView 视图。,2019/6/27,Microsoft SQL Server 2005,7,数据操纵语言,数据操纵语言 (Data Manipulation Language) 是SQL语言中，负责对数据库对象执行数据存取工作的指令集，以SELECT、INSERT、UPDATE、DELETE四种指令为核心。 DML的主要功能即是存取数据，因此其语法都是以读取与写入数据库为主，除了INSERT以外，其他三种指令都可能需搭配WHERE指令来过滤数据范围，或是不加WHERE指令来存取全部的数据。,2019/6/27,Microsoft SQL Server 2005,8,INSERT指令,INSERT 是将数据插入到数据库对象中的指令，可以插入数据的数据库对象有数据表以及可更新视图两种。 基本格式有： INSERT INTO 数据表或可更新视图名称 (字段1,字段2,字段3, .) VALUES (值1, 值2, 值3, .) - 只需要根据对应的字段來设定插入值。 INSERT INTO 数据表或可更新视图名称 VALUES (值1, 值2, 值3, .) - 必須要指定所有字段的插入值。 例如： INSERT INTO MyTable (字段1,字段2,字段3,字段4,字段5) VALUES (1, 2, 3, 4, 5),2019/6/27,Microsoft SQL Server 2005,9,UPDATE 指令,UPDATE 指令是依给定条件，将符合条件的数据表中的数据更新为新的数值，其基本格式为： UPDATE 数据表或可更新视图名称 SET 字段1=值1,字段2=值2,字段3=值3, . WHERE 指定条件 例如： UPDATE MyTable SET 字段1 = 3,字段2 = 5, 字段4 = 5 WHERE 字段3 = 198273,2019/6/27,Microsoft SQL Server 2005,10,DELETE 指令,DELETE 指令从数据库对象中删除数据的指令，基本语法为： DELETE FROM 数据表或可更新视图名称 WHERE 给定条件 例如： DELETE FROM MyTable WHERE 字段3 = 1918299 值为字符时，加西文单引号。,2019/6/27,Microsoft SQL Server 2005,11,数据控制语言,数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据存取权进行控制的指令，它可以控制特定用户对数据表、视图、存储过程、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。 GRANT 的语法结构为： GRANT 权限 ON 要授予权限的数据库对象 TO 用户 WITH 授权选项 DENY 的语法结构为： DENY 权限 ON 要拒绝权限的数据库对象 TO 用户 权限可以是INSERTUPDATE等，参见教材p230,2019/6/27,Microsoft SQL Server 2005,12,Select 查询,2019/6/27,Microsoft SQL Server 2005,13,简单Select 查询,语法： SELECT 字段1,字段2, FROM 表名 SELECT * FROM 表名 SELECT 字段1,字段2, FROM 表名 WHERE 查询条件 SELECT * FROM 表名 WHERE 查询条件,简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。 -,2019/6/27,Microsoft SQL Server 2005,14,简单Select 查询,SELECT StuId,StuName,DeptId,Sex FROM Tb_Student,SELECT * FROM Tb_Student,2019/6/27,Microsoft SQL Server 2005,15,简单Select 查询,SELECT StuId,StuName,DeptId,Sex FROM Tb_Student WHERE Sex=F,SELECT * FROM Tb_Teacher WHERE Sex=F and DeptId= 08,2019/6/27,Microsoft SQL Server 2005,16,WHERE子句使用的运算符,2019/6/27,Microsoft SQL Server 2005,17,比较运算符和表达式,SELECT * FROM Tb_Student WHERE StuName张三,SELECT * FROM Tb_Student WHERE Sex= F,2019/6/27,Microsoft SQL Server 2005,18,逻辑运算符和表达式,SELECT * FROM Tb_Student WHERE Sex= M and Sex= F,SELECT * FROM Tb_Student WHERE ClassId= S040201101 and Sex= F,SELECT * FROM Tb_Student WHERE NOT Sex= M,2019/6/27,Microsoft SQL Server 2005,19,范围搜索运算符和表达式,SELECT * FROM Tb_Student WHERE Age BETWEEN 20 TO 22 - SELECT * FROM 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,2019/6/27,Microsoft SQL Server 2005,20,模式匹配运算符和表达式,SELECT * FROM Tb_Student WHERE StuName LIKE 张%,SELECT * FROM Tb_Student WHERE StuName NOT LIKE 张三,2019/6/27,Microsoft SQL Server 2005,21,未知值条件,NULL表示未知的，不可用或将在以后添加的数据。NULL值与零、零长度的字符串的含义不同。 在WHERE子句中，使用IS NULL或IS NOT NULL条件可以查询某一数据值是否为NULL的数据信息。 例如：,SELECT * FROM Tb_Grade WHERE RetestScore IS NOT NULL,SELECT * FROM Tb_Course WHERE CourseName IS NULL,2019/6/27,Microsoft SQL Server 2005,22,字符串运算符和表达式,字符串表达式是用字符串连接符（）将字符型常量、变量、字符串函数连接组成的式子。字符串表达式的计算结果包括两个字符串内的空格和所有字符，仍然是字符型。 如 我爱+ 我家 表达式的值为: 我爱 我家 - 例如：Beijing+ Huanying + Ning! 请写出结果！,2019/6/27,Microsoft SQL Server 2005,23,运算符的优先级,运算符的优先级决定了表达式的计算和比较操作顺序。 运算符的优先级由高到低的顺序为： 1）括号：（）。 2）正、负或取反运算：+、-、。 3）乘、除、求模运算：*、/、。 4）加、减、字符连接运算：+、-、+。 5）比较运算：、!、!、!。 6）位运算：、|。 7）逻辑非运算：NOT。 8）逻辑与运算：AND。 9） BETWEEN、IN、LIKE、OR等运算。 10）赋值运算：。,2019/6/27,Microsoft SQL Server 2005,24,ORDER BY子句,使用SELECT语句进行数据查询，为了按一定顺序浏览数据，可以使用ORDER BY子句对生成的查询结果进行排序。ORDER BY子句在SELECT语句中的语法格式如下： - SELECT 字段1,字段2, FROM 表名 WHERE 查询条件 ORDER BY 排序表达式 ASC | DESC,2019/6/27,Microsoft SQL Server 2005,25,ORDER BY子句,SELECT StuId,StuName,Sex FROM Tb_Student WHERE DeptId=08 ORDER BY StuName,SELECT StuId,StuName,Sex FROM Tb_Student WHERE DeptId=08 ORDER BY StuName DESC,2019/6/27,Microsoft SQL Server 2005,26,ORDER BY子句,SELECT StuId,StuName,Sex FROM Tb_Student WHERE DeptId=08 ORDER BY StuName,Sex 结果如何？,2019/6/27,Microsoft SQL Server 2005,27,ORDER BY子句,ORDER BY子句总是位于WHERE子句（如果有的话）后面，可以包含一个或多个列，每个列之间以逗号分隔。这些列可能是表中定义的列，也可能是SELECT子句中定义的计算列。 - 在SQL Server 2005中，汉字比其它字符值都要大，而汉字之间的大小比较是取决于其拼音首字母的大小。 如果没有特别说明，值是以升序序列进行排序的。当然，你也可以选择使用ASC关键字指定升序排序。 - 如果你希望以降序排列，则在ORDER BY后面使用DESC关键字。,2019/6/27,Microsoft SQL Server 2005,28,GROUP BY 子句,使用SELECT语句进行数据查询，为了按特定条件分组浏览数据，可以使用GROUP BY子句对某一列数据的值进行分类，形成结果集。GROUP BY子句在SELECT语句中的语法格式如下： - SELECT 字段1,字段2, FROM 表名 WHERE 查询条件 GROUP BY 字段1,字段2, WITH ROLLUP | CUBE ORDER BY 排序表达式 ASC | DESC - CUBE 生成的结果集显示了所选列中值的所有组合（包括字段1、字段2、字段1+字段2）的分组统计结果和总的汇总统计的聚合； ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。（包括字段1、字段1+字段2的分组统计结果和总的汇总统计的聚合）,2019/6/27,Microsoft SQL Server 2005,29,聚合函数,聚合函数是SQL语言中一种特殊的函数，比如SUM, COUNT, MAX, MIN, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。换句话说，上面几个返函数的值建立在对多条记录进行统计的基础上。 SUM是求和函数。 SELECT ClassId,SUM(Score) FROM Tb_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,2019/6/27,Microsoft SQL Server 2005,30,GROUP BY 子句,常用统计函数,2019/6/27,Microsoft SQL Server 2005,31,GROUP BY子句,-显示每个学生的班级编码和成绩 SELECT ClassId,TotalScore FROM Tb_Grade - -汇总每个班级的平均成绩 SELECT ClassId,AVG(TotalScore) FROM Tb_Grade GROUP BY ClassId,2019/6/27,Microsoft SQL Server 2005,32,- SELECT ClassId,ClassName,AVG(TotalScore) FROM Tb_Grade,Tb_Class GROUP BY ClassId,GROUP BY子句,-汇总每个班级（含班级名称）的平均成绩? SELECT ClassId,ClassName,AVG(TotalScore) FROM Tb_Grade GROUP BY ClassId,- SELECT Tb_Grade.ClassId,ClassName,AVG(TotalScore) FROM Tb_Grade,Tb_Class WHERE Tb_Grade.ClassId=Tb_Class.ClassId GROUP BY Tb_Grade.ClassId,2019/6/27,Microsoft SQL Server 2005,33,GROUP BY子句,-显示班级名称的汇总各班平均成绩 SELECT Tb_Grade.ClassId,ClassName,AVG(TotalScore) AS AvgScore FROM Tb_Grade,Tb_Class WHERE Tb_Grade.ClassId=Tb_Class.ClassId GROUP BY Tb_Grade.ClassId,ClassName,当两个表中都有ClassId字段时，要加上表前缀，说明是那个表的ClassId字段,SELECT列表中出现的字段都必须要在GROUP BY列表中出现，聚合函数字段除外,当要显示相关主键表中的其它字段时，如这儿的ClassName字段，则在WHERE子句中通过主外键关系进行连接,2019/6/27,Microsoft SQL Server 2005,34,GROUP BY 子句,查询各个班级的最大成绩和最小成绩？,SELECT Tb_Grade.ClassId,ClassName,MAX(TotalScore) AS MaxScore, MIN(TotalScore) AS MinScore FROM Tb_Grade,Tb_Class WHERE Tb_Grade.ClassId=Tb_Class.ClassId GROUP BY Tb_Grade.ClassId,ClassName,2019/6/27,Microsoft SQL Server 2005,35,GROUP BY 子句,GROUP BY分类汇总，分项统计 紧跟在GROUP BY后面的字段就是分组条件。 如果在前面选择列表中出现的非聚合字段未出现在GROUP BY后面，SQL语句就会出错。 思考题： 按照不同班级和性别统计学生平均年龄。,2019/6/27,Microsoft SQL Server 2005,36,HAVING子句,HAVING子句相当于一个用于组（分类汇总）的WHERE子句，它指定了组或聚合的查询条件。HAVING子句通常与GROUP BY子句一起使用。HAVING子句的语法为： - SELECT 字段1,字段2, FROM 表名 WHERE 查询条件 GROUP BY字段1,字段2, HAVING 组查询条件,2019/6/27,Microsoft SQL Server 2005,37,HAVING子句,显示各班级学生平均总成绩大于73的班级编号。 SELECT Tb_Grade.ClassId, AVG(TotalScore) AS AvgScore FROM Tb_Grade GROUP BY Tb_Grade.ClassId HAVING AVG(TotalScore)73,2019/6/27,Microsoft SQL Server 2005,38,HAVING子句,求各个班级的最大成绩和最小成绩,且最低成绩小于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,2019/6/27,Microsoft SQL Server 2005,39,HAVING子句,HAVING子句用来对分组后的数据进行筛选，它后面会跟一个条件表达式。 当在GROUP BY 子句中使用HAVING子句时，查询结果中只返回满足HAVING条件的组。,2019/6/27,Microsoft SQL Server 2005,40,SELECT语法总结,SELECT select_list FROM table_list WHERE search_conditions GROUP BY group_by_list HAVING search_conditions ORDER BY order_list ASC | DESC ,上述“ ”表示可选项，“ | ”表示取其中之一。,2019/6/27,Microsoft SQL Server 2005,41,计算列,SELECT后面还可以是计算列。在SELECT后面的计算列，只是位于该SELECT语句中，是临时性的，它由一个表达式产生。 SELECT StuId, StuName+(+Sex+) FROM Tb_Student 上面语句中的StuName+(+Sex+)就构成了一个计算列，它的值由两个字符型值和一对括号连接组成。,2019/6/27,Microsoft SQL Server 2005,42,列别名,在上面的结果集中，列名都是英文。这样看上去很不直观，我们希望能够给列名换上诸如“学号、姓名”等这样的名字。只需在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,2019/6/27,Microsoft SQL Server 2005,43,当然，我们也可以给表指定列别名，给表取别名和给列取别名类似，但只可以用 “原始表名 AS 别名”和 “原始表名 别名”这两种方式。 SELECT * FROM Tb_Student ST 或者 SELECT * FROM Tb_Student AS ST - 字段别名不能放在WHERE子句中,而表别名可以放在WHERE子句中。,表别名,2019/6/27,Microsoft SQL Server 2005,44,多表查询,2019/6/27,Microsoft SQL Server 2005,45,查询全院各系部拥有班级的基本情况，显示系部名称、班级编码和班级名称；,多表查询,2019/6/27,Microsoft SQL Server 2005,46,USE Teaching_System GO SELECT d.*, c.* FROM Tb_Class AS c, Tb_Dept AS d,多表查询,2019/6/27,Microsoft SQL Server 2005,47,笛卡尔乘积,多表查询,2019/6/27,Microsoft SQL Server 2005,48,多表查询,2019/6/27,Microsoft SQL Server 2005,49,多表查询,2019/6/27,Microsoft SQL Server 2005,50,USE Teaching_System GO SELECT d.*, c.* FROM Tb_Class AS c, Tb_Dept AS d WHERE c.DeptId=d.DeptId,多表查询,2019/6/27,Microsoft SQL Server 2005,51,多表查询,SELECT StuId,StuName,DeptId,SpecId,ClassId,Sex FROM Tb_Student,SELECT StuId 学号,StuName 姓名,DeptId 系部,SpecId 专业,ClassId 班级,Sex 性别 FROM Tb_Student,2019/6/27,Microsoft SQL Server 2005,52,两表连接,SELECT StuId 学号,StuName 姓名,DeptName 系部, SpecId 专业,ClassId 班级,Sex 性别 FROM Tb_Student TS,Tb_Dept TD WHERE TS.DeptId=TD.DeptId,2019/6/27,Microsoft SQL Server 2005,53,多表连接基础,在实际查询应用中，用户所需要的数据并不全部在一个表中，这时就需要多表查询，从多个表中抽取我们所需要的数据信息。 多表查询实际上是通过各个表之间的共同列的相关性（主外键关系）来查询数据的，这是关系数据库查询数据的主要特征。 多表查询首先要用WHERE子句在表之间建立连接关系。 在进行多表查询时，可以遵循以下基本原则： （1）SELECT子句列表中，字段前要加上基表名称； （2）FROM子句应包括所有使用的基表； （3）WHERE子句应在表之间建立连接关系。,2019/6/27,Microsoft SQL Server 2005,54,两表连接,SELECT StuId 学号,StuName 姓名,DeptId 系部编码, DeptName 系部, SpecId 专业,ClassId 班级,Sex 性别 FROM Tb_Student TS,Tb_Dept TD WHERE TS.DeptId=TD.DeptId,SELECT StuId 学号,StuName 姓名, TS.DeptId 系部编码, DeptName 系部, SpecId 专业,ClassId 班级,Sex 性别 FROM Tb_Student TS,Tb_Dept TD WHERE TS.DeptId=TD.DeptId,2019/6/27,Microsoft SQL Server 2005,55,多表连接,SELECT StuId 学号,StuName 姓名,DeptName 系部, SpecName 专业,ClassName 班级,Sex 性别 FROM Tb_Student TS,Tb_Dept TD,Tb_Spec TSP, Tb_Class TC WHERE TS.DeptId=TD.DeptId AND TS.SpecId=TSP.SpecId AND TS.ClassId=TC.ClassId,要求显示学生姓名和所在系名、专业、班级名称?,2019/6/27,Microsoft SQL Server 2005,56,各种连接查询,内连接：内连接是一种常用的数据连接查询方式。它使用比较运算符进行多个表之间的比较连接操作。内连接会消除与另一个表的任何不匹配的数据行。 外连接：外连接会返回FROM子句中提到的至少一个表的所有符合查询条件的数据行（包括连接中不匹配的数据行），外连接分为左外连接、右外连接和完全连接。 交叉连接：当对两个表使用交叉连接查询时，将返回来自这两个表的各行的所有可能的数据行组合。 自连接：表自身的链接参见教材p119。,2019/6/27,Microsoft SQL Server 2005,57,两个样例表,SELECT * FROM Tb_Dept SELECT ClassId,ClassName,DeptId FROM Tb_Class,2019/6/27,Microsoft SQL Server 2005,58,内连接,SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 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,2019/6/27,Microsoft SQL Server 2005,59,外连接-左外连接,SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM Tb_Dept LEFT OUTER JOIN Tb_Class ON Tb_Dept.DeptId=Tb_Class.DeptId 左边表中列全显示,2019/6/27,Microsoft SQL Server 2005,60,外连接-右外连接,SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM Tb_Dept RIGHT OUTER JOIN Tb_Class ON Tb_Dept.DeptId=Tb_Class.DeptId 右边表中列全显示,2019/6/27,Microsoft SQL Server 2005,61,外连接-完全连接,SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM Tb_Dept FULL OUTER JOIN Tb_Class ON Tb_Dept.DeptId=Tb_Class.DeptId,2019/6/27,Microsoft SQL Server 2005,62,交叉连接,SELECT DeptName 系部名称, ClassId 班级编码, ClassName 班级名称 FROM Tb_Dept CROSS JOIN Tb_Class,2019/6/27,Microsoft SQL Server 2005,63,交叉连接,不使用WHERE子句时，返回的结果是一个基本表中符合条件的行数乘以另一个基本表中符合条件的行数。 使用WHERE子句时，返回的结果是被连接的两个基本表所有行的笛卡尔积中满足WHERE条件的所有行的行数。 参见教材p118,2019/6/27,Microsoft SQL Server 2005,64,联合查询（UNION）,如果有多个不同的查询结果，但又希望将这些查询结果放在一起显示，组成一组数据。在这种情况下，可以使用UNION子句。使用UNION子句的查询又称为联合查询，它可以将两个和多个查询结果集组合成为单个结果集。语法如下： SELECT select_list FROM table_list WHERE search_conditions UNION ALL SELECT select_list FROM table_list WHERE search_conditions 其中，ALL关键字为可选的，如果在UNION子句中使用该关键字，则返回全部数据行；如果不使用该关键字，则返回结果中删除重复数据行。,2019/6/27,Microsoft SQL Server 2005,65,联合查询（UNION）,SELECT * FROM Tb_Spec WHERE SpecId08 UNION SELECT StuId,StuName,Sex FROM Tb_Student WHERE ClassIdS0408011,2019/6/27,Microsoft SQL Server 2005,66,章节综合练习,每个学年结束，班主任都要对班级学生成绩进行分析，然后初定学年奖学金获得者的人选。让我们运用数据库查询的知识帮助04网络1班的班主任刘老师完成以下两个任务： 根据学院奖学金评选规定，初定获奖学金学生名单；（每位同学的课程平均成绩必须高于75分） 对该班级开设的各门课程分别进行成绩分析。显示课程编号、学生学号和成绩的明细并分别汇总每门课程的平均成绩。,2019/6/27,Microsoft SQL Server 2005,67,章节综合练习,2019/6/27,Microsoft SQL Server 2005,68,任务一,USE Teaching_System GO SELECT StuId 学号, AVG(TotalScore) AS 平均成绩 FROM Tb_Grade WHERE ClassId=S0408011 GROUP BY StuId HAVING AVG(TotalScore)=75,2019/6/27,Microsoft SQL Server