（中职）MySQL 8.0数据库管理与应用第3章教学课件（）.pptx

《（中职）MySQL 8.0数据库管理与应用第3章教学课件（）.pptx》由会员分享，可在线阅读，更多相关《（中职）MySQL 8.0数据库管理与应用第3章教学课件（）.pptx（29页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、（中职）MySQL 8.0数据库管理与应用第3章教学课件（工信版）第第3 3章章 数据查询数据查询3.1.1 SELECT语句的基本组成SELECT 输出项,输出项.FROM 源表WHERE 搜索条件GROUP BY 列名|表达式|列位置,.HAVING 搜索条件ORDER BY 列名|表达式|列位置ASC|DESC,.LIMIT 偏移,行数|行数 OFFSET 偏移INTO OUTFILE 文件名|INTO DUMPFILE 文件名|INTO 变量名,变量名3.1 SELECT3.1 SELECT语句概述语句概述3.1.2 常用查询工具1.mysql命令行工具使用mysql命令行工具可以通过

2、交互方式输入SQL语句或从文件以批处理模式执行SQL语句，从而完成在MySQL服务器上创建数据库、查询和操作数据的任务。2.MySQL WorkbenchMySQL Workbench是一种可视化的数据库设计和管理工具，可以用于管理MySQL服务器，也可以用于创建和管理数据库和各种数据库对象。MySQLWorkbench提供了一个内置的SQL编辑器，可以用来在数据库连接上执行各种SQL语句，还可以将编写的SQL语句或查询结果保存到文件中。3.Navicat for MySQLNavicat for MySQL是由PremiumSoft公司出品的用于管理MySQL数据库的桌面应用程序，它为MyS

3、QL数据库管理、开发和维护提供了一款直观而强大的图形界面。3.1 SELECT3.1 SELECT语句概述语句概述3.2.1 选择所有列如果要查看表中所有列的信息，可在SELECT输出项列表中使用星号（*）来选择源表或视图中的所有列。如果使用表或视图名称进行限定，则星号将被解析为对指定表或视图中的所有列的引用。当使用星号选择所有列时，查询结果中的列顺序与创建表或视图时所指定的列顺序相同。由于SELECT*将查找表中当前存在的所有列，因此每次执行SELECT*语句时，表结构的更改（通过添加、删除或重命名列）都会自动反映出来。3.2.2 选择部分列如果要从表中选择一部分列作为SELECT查询的输出

4、项，则应当在输出项列表中明确地列出每个列名，列名之间用逗号分隔。假如创建表时在表名或列名中使用了空格（不符合标识符命名规则），则编写SELECT语句时需要使用反引号“”将表名或列名括起来，例如SELECT student id,student name FROM student table，否则会出现错误信息。如果在FROM子句中指定了多个表，而这些表中又有同名的列，则在使用这些列时需要在列名前面冠以表名，以指明该列属于哪个表。例如，在student和score表都有一个名称为stuid的列。若要引用student表中的stuid列，应在输出项列表中写上student.stuid；若要引用sc

5、ore表中的stuid列，则应在输出项列表中写上score.stuid。3.2 3.2 选择查询输出项选择查询输出项3.2.3 设置列别名在SELECT语句中，可以使用AS子句来更改结果集中的列名或为派生列分配名称：列名 AS 别名3.2.4 增加派生列在输出项列表中，有些列不是指定为对列的简单引用，而是通过表中的其他列值计算而生成，例如表中有单价和数量列，可以在输出项列表中增加一个用于计算总额的表达式，其值等于单价乘以数量，此即派生列。派生列本无名称，通常应使用AS子句为其指定别名。在派生列中，可以对数值列或常量使用算术运算符或函数进行计算，也可以进行数据类型转换。MySQL支持下列算术运算

6、符：+（加）、（减）、*（乘）、/（除）、%（模，即取余数），可以使用算术运算符可以对数值数据进行加、减、乘、除运算。此外，也可以使用日期函数或常规加或减算术运算符对日期时间类型的列进行算术运算。3.2 3.2 选择查询输出项选择查询输出项3.2.5 消除重复行在输出项列表中可以使用ALL和DISTINCT修饰符，用于指定是否应返回重复的行。pALL是默认值，指定应返回所有匹配的行，包括重复行在内。pDISTINCT指定从结果集中删除重复的行。DISTINCTROW是DISTINCT的同义词。3.2.6 没有表列的SELECT语句在SELECT语句中，只要SELECT子句是必选的，其他子句一律

7、都是可选项。如果SELECT选择列表中仅包含常量、变量、函数和其他表达式，而不包含从任何表或视图中选择的列，则没有必要使用FROM子句。3.2 3.2 选择查询输出项选择查询输出项3.3.1 单表查询当从单个数据库表中查询数据时，需要在FROM子句中指定要引用的该表的名称，这种查询称为单表查询。在单表查询中，可以使用下列两种方式引用来源表。p首先使用USE语句将一个数据库设置为当前数据库，然后在FROM子句中指定要引用的表名，此时这个表就是属于当前数据库。p不设置当前数据库，在FROM子句中指定要引用的表名时在其前面冠以所属数据库的名称。如果要从多个表中查询数据时，则必须在FROM子句中指定要

8、引用的多个表名，这种查询称为多表查询。在多表查询中，需要使用某种方式对不同的表进行连接，因此多表查询也称为连接查询。连接方式分为全连接和JOIN连接，JOIN连接又分为内连接、外连接和交叉连接。3.3 3.3 选择查询数据源选择查询数据源3.3.2 全连接查询全连接是指在FROM子句中使用逗号来分隔各个表，语法格式如下：FROM 表名 AS 别名,表名 AS 别名,.其中表名指定要引用的表，AS关键字和别名都是可选的。在全连接查询中，查询结果中的列来自各个表，结果集是由各个表中的行进行交叉所得到的各种可能的组合，也称为笛卡儿乘积，结果集包含的行数是各个来源表行数的乘积。例如，A表包含200行，

9、B表300行，则以全连接方式查询A表和B表时，返回的结果集将包含200*300=60000行数据。因此，使用全连接方式时有可能生成行数非常大的结果集。在实际应用中，使用全连接查询时通常可以使用WHERE子句来设置一个条件表达式，以控制结果集的大小。3.3 3.3 选择查询数据源选择查询数据源3.3.3 内连接查询内连接是指使用关键词INNER JOIN来连接要查询的各个表，语法格式如下：FROM 表名 AS 别名 INNER JOIN 表名 AS 别名 ON 条件表达式其中表名用于指定要查询的表，AS关键字和别名是可选的。由于内连接是默认的连接方式，关键字INNER也可以省略不写。ON子句主要

10、用于指定表之间的连接条件，其他与表连接无关的条件应放在WHERE子句中。使用内连接查询时，将根据ON子句指定的连接条件合并两个表并返回满足条件的行。3.3 3.3 选择查询数据源选择查询数据源3.3.4 外连接查询外连接是指在FROM子句中使用OUTER关键字来连接要查询的两个表，具体分为以下几种形式。p左外连接（LEFT OUTER JOIN）：结果集中除了匹配行之外，还包括左表中存在但右表中不匹配的行，对于这样的行，从右表中选择的列设置为NULL。p右外连接（RIGHT OUTER JOIN）：结果集中除了匹配行之外，还包括右表中存在但左表中不匹配的行，对于这样的行，从左表中选择的列设置为

11、NULL。p自然连接（NATURAL JOIN）：其语义定义与设置ON条件的INNER JOIN相同，可以进一步分为自然左连接（NATURAL LEFT OUTER JOIN）和自然右连接（NATURAL RIGHT OUTER JOIN）。外连接只能用于连接两个表。当使用各种形式的外连接时，可以省略OUTER关键字。3.3.5 交叉连接查询交叉连接是指在FROM子句中使用CROSS JOIN来连接各个表。如果不使用连接条件，则交叉连接就是对两个表进行笛卡儿乘积运算，结果集是由第一个表的行与第二个表的行拼接后而形成，结果集的行数就等于两个表的行数的乘积。在MySQL中，从语法上讲CROSS J

12、OIN与INNER JOIN等同，两者可以互换。3.3 3.3 选择查询数据源选择查询数据源3.4.1 WHERE子句语法格式在SELECT语句中WHERE子句是一个可选项，使用时应当将其放在FROM子句的后面，语法格式如下：WHERE 搜索条件其中搜索条件定义要查询的行应满足的条件，这个条件是用运算符连接列名、常量、变量、函数等而得到的表达式，其取值为TRUE（1）、FALSE（0）或NULL。通过WHERE子句可以为查询设置一系列搜索条件，只有那些满足搜索条件的行才用于生成结果集，那些不满足搜索条件的行则不会包含在结果集内。3.4.2 比较搜索在MySQL中，可以使用比较运算符来比较两个表

13、达式的大小，语法格式如下：表达式1 比较运算符 表达式2其中表达式1和表达式2是要进行比较的表达式，其数据类型是除TEXT和BLOB之外的数据类型。MySQL支持的比较运算符如下。=：等于 ：大于 =：大于等于 ：相等或都是NULL ：不等于 !=：不等于如果两个表达式都不是NULL，则比较运算符（除外）返回布尔值TRUE或FALSE；如果两个表达式中有一个为NULL，或两个都是NULL，则返回UNKNOWN。3.4 3.4 设置查询条件设置查询条件3.4.3 判定空值前面曾经介绍了如何使用比较运算符“”来判定一个表达式是否为NULL值。实际上，MySQL还专门提供了一个用于判定空值的比较运算

14、符IS NULL，其语法格式如下。表达式 IS NOT NULL如果表达式的值为NULL，则返回TRUE，否则返回FALSE。NOT为可选项，当使用NOT时，将对运算结果取反。3.4.4 范围搜索在WHERE子句中，可以使用BETWEEN运算符来指定要搜索的范围，也可以使用NOT BETWEEN来查找指定范围之外的所有行，语法格式如下。表达式 NOT BETWEEN 起始值 AND 终止值其中表达式给出要测试的表达式，其值可能位于指定的范围内。这个范围的大小由起始值和终止值指定，起始值和终止值都是任何有效的表达式。表达式、起始值和终止值必须具有相同的数据类型。NOT指定对运算结果取反。AND用

15、作一个占位符，指示要测试的表达式的值应该处于由起始值与终止值所指定的范围内。BETWEEN运算符返回结果为布尔类型。如果测试表达式的值大于或等于起始值，并且小于或等于终止值，则BETWEEN返回TRUE，NOT BETWEEN返回FALSE。如果表达式的值小于起始值或大于终止值，则BETWEEN返回FALSE，NOT BETWEEN返回TRUE。如果要指定一个排他性范围，则应使用大于运算符（）和小于运算符（、=、=、!=、。3.8 3.8 子查询子查询3.8.3 IN子查询子查询可以通过关键字IN引入，语法格式如下。表达式 NOT IN(子查询)其中子查询为列子查询，它返回的结果集是单个列，其

16、中包含0个或多个值。使用IN引入的子查询可以用于集成员测试，也就是将一个表达式的值与子查询返回的一列值进行比较，如果该表达式的值与此列中的任何一个值相等，则集成员测试返回TRUE，否则返回FALSE。关键字NOT用于对测试结果取反。在集成员测试中，由子查询返回的结果集是单个列值的一个列表，该列的数据类型必须与测试表达式的数据类型相同。当子查询返回结果之后，外层查询将使用这些结果。使用子查询时需要注意限定列名的问题。一般规则是，语句中的列名通过同级FROM子句中引用的表来隐性限定。如果子查询的FROM子句引用的表中不存在子查询中引用的列，而外部查询的FROM子句引用的表中存在该列，则这个子查询可

17、以正确执行。3.8 3.8 子查询子查询3.8.4 ANY子查询子查询可以使用关键字ANY引入：表达式 比较运算符 ANY|SOME(子查询)ANY关键词前面必须使用一个比较运算符。子查询为列子查询，它返回的结果集是单个列，其中包含0个或多个值。使用ANY关键词引入子查询时，对表达式与子查询返回的列中的任何一个值进行比较，如果比较结果为TRUE，则返回TRUE。3.8.5 ALL子查询子查询可以使用关键字ALL引入：表达式 比较运算符 ALL(子查询)关键字ALL必须用在一个比较运算符的后面。子查询是一个列子查询，它返回的结果集是单个列，其中包含0个或多个值。使用ALL引入子查询时，将使用比较

18、运算符对表达式与子查询返回的列中的所有值进行比较，如果比较结果为TRUE，则返回TRUE。3.8 3.8 子查询子查询3.8.6 行子查询行子查询是一个能返回一个单一行的子查询，它返回的结果集包含一个以上的列值（1行N列）。下面给出两个例子。SELECT*FROM t1 WHERE(1,2)=(SELECT col1,col2 FROM t2);SELECT*FROM t1 WHERE ROW(1,2)=(SELECT col1,col2 FROM t2);其中表达式(1,2)和ROW(1,2)称为行构造器，两者是等效的。如果在表t2的一个行中，col1=1且col2=2，则WHERE表达式的

19、值为TRUE。下面两个语句在语义上是等效的。SELECT*FROM t1 WHERE(col1,col2)=(1,1);SELECT*FROM t1 WHERE col1=1 AND col2=1;3.8 3.8 子查询子查询3.8.7 EXISTS子查询子查询可以使用关键字EXISTS引入，语法格式如下。NOT EXISTS(子查询)当使用EXISTS关键字入子查询时，将进行存在性测试。外部查询的WHERE子句测试子查询返回的行是否存在。如果子查询返回任何的行，则EXISTS条件为TRUE，否则为FALSE。子查询实际上不产生任何数据，它只返回TRUE或FALSE值。NOT用于对测试结果取反

20、。3.8.8 派生表子查询除了用在SELECT语句的输出项列表和WHERE子句中，也可以用在FROM子句中，此时还允许为子查询设置一个别名。当处理具有别名的子查询时，会将返回的结果存储到一个中间表，外部查询可以从该中间表中查询数据，这种中间表称为派生表。注意：派生表只是由SELECT语句返回的虚拟表，它并不作为对象存储，仅在查询执行期间存在。与子查询不同的是，派生表必须具有别名，以便能够在外部查询中引用其名称。如果派生表没有别名，将会发生错误。3.8 3.8 子查询子查询3.8.9 公用表表达式公用表表达式（CTE）是MySQL 8.0的新特性之一。具体来说，公用表表达式就是一个命名的临时结果

21、集，它存在于单个语句的范围内，并且可以在该语句中多次引用。CTE的结构包括名称、可选的列名列表和定义CTE的子查询。定义CTE后，可以在SELECT语句中引用它。CTE的基本语法格式如下：WITH CTE名称(列名,列名,.)AS(子查询)SELECT*FROM CTE名称;子查询中的列数必须与列名列表中的列数相同。如果省略列名列表，则CTE将使用定义CTE的子查询的列名列表。与派生表类似，公用表表达式不作为对象存储，仅在查询执行期间存在。与派生表不同的是，公用表表达式可以递归引用，也可以在同一个查询中多次引用。公用表表达式提供了更好的可读性和性能，使用公用表表达式可以通过更可读的方式来构建复

22、杂查询。3.8 3.8 子查询子查询3.9.1 UNION语句UNION语句可以将多个SELECT语句的结果集组合成一个新的结果集，语法格式如下。SELECT.UNION ALL|DISTINCTSELECT.UNION ALL|DISTINCTSELECT.在每个SELECT语句的对应位置上选择的列应具有相同的数据类型。例如，在第一个语句选择的第一列应与其他语句选择的第一列具有相同的数据类型。在第一个SELECT语句中所使用的列名称将作为于结果集的列名称。各个SELECT语句都是常规的选择语句，但是受到一些限制，例如只有最后一个SELECT语句可以使用INTO OUTFILE。3.9 3.9

23、 组合查询结果组合查询结果3.9.2 应用示例在学生成绩管理数据库中查询软件专业和网络专业学号排在前3位的学生:USE sams;(SELECT major AS 专业,classname AS 班级,stuid AS 学号,stuname AS 姓名,gender AS 性别,birthdate AS 出生日期FROM studentWHERE major=软件 ORDER BY stuid LIMIT 3)UNION(SELECT major 专业,classname AS 班级,stuid AS 学号,stuname AS 姓名,gender AS 性别,birthdate AS 出生日期FROM studentWHERE major=网络 ORDER BY stuid LIMIT 3);3.9 3.9 组合查询结果组合查询结果