第04章 数据查询

1、第四章第四章 数据查询数据查询本章要点本章要点v 本章要点v 1. 了解数据查询的相关概念：单表查询、多表查询、子查询、集合查询、系统函数和视图。v 2. 掌握单表查询、多表查询的SQL语法，并会使用相关SQL在Workbench或命令客户端中进行查询。v 3. 了解子查询和集合查询语句。v 4. 了解系统函数的功能，熟悉常用的系统函数，应用函数进行有关信息查询。v 5. 了解视图的优点，掌握视图的创建和应用。v 6. 应用SQL语句解决实际问题。引言引言v 数据库查询是数据库的核心知识和技能，数据查询可以简单分为单表查询、多表查询和子查询。单表查询相对比较简单，但是知识点较多，需要理解记忆；

2、多表查询和子查询知识较难，需要加深理解；另外MySQL数据库提供了丰富的系统函数，而函数通常在查询中使用；视图是数据库的重要内容，使用广泛，而视图的核心就是一个查询，因此把系统函数和视图内容合并到了本章。本章讲解采用“简易成绩管理系统”数据库，本数据库只有三个表，分别为学生表、课程表和选课表，表的创建在任务一的任务实施里有详细代码。 任务一任务一 单表查询单表查询v基础知识 （一）单表查询SQL一般格式 select all | distinct 列表达式 , 列表达式 from 表名 where 条件表达式 group by 列名 having 条件表达式 order by 列名 asc |

3、 desc 含义：从from表名中找出满足where条件表达式的记录，按照group列分组，按照order列排序，显示查询结果数据。MySQL数据库数据库v （二）列的选择 1未引用表查询 select可以用于未引用表的查询。示例如下： 示例4-1： 输入：select 1 + 1; 输出：2 2. 选择表中的若干列 （1）查询指定列 查询指定列在select后面指定想要检索列并指定输出顺序，语法格式如下：MySQL数据库数据库 select 列名1, 列名2, 列名3, from 表名 示例4-2：查询全体学生的学号、姓名和性别 select sno,name,gender from stu

4、dent 示例4-3：查询全体学生的姓名、学号 select name,sno from studentv（2）查询全部列 将表中所有属性列都选出来，方法有二：一是在select后面罗列所有列名，二是在select后面使用*代替所有列。 select * from 表名MySQL数据库数据库 或 select 列名, 列名, 列名 from 表名 示例4-4：查询全体学生的详细信息 select * from studentv（3）查询经过计算的列 select 子句的列名可以是表中的属性列，也可以是列的表达式 示例4-5：查看学生的出生年份。 假如当前年份为2015年，则学生的出生年份应为2

5、015-age。 select sno, name, 2015-age from student;MySQL数据库数据库v （4）列别名 可以给列和列的表达式定义别名，别名可以是英文也可以是中文。 示例4-6：出生年份使用别名。 select sno, name, 2015-age 出生年份 from student。 v （5）消除取值重复的行 要从查询结果表中移除重复值，需要在Select后面插入关键字distinc。 示例4-7：查询student表中学生属于哪些班级。 select distinct depart from student;MySQL数据库数据库v （三）行的选择 查询

6、当中可以指定想要查看的任意行，使用where子句指定选择行的条件。where子句包含逻辑表达式，表达式为真的行将成为查询结果表中的一行，反之则被忽略。1比较大小示例4-8：查询计算机学院所有学生的姓名MySQL数据库数据库 select name from student where depart=计算机; 示例4-9：查询年龄小于21的学生信息 select * from student where age2;MySQL数据库数据库 注意： （1）在select后边除了聚集函数外，一般只能出现分组列。 （2）对行进行选择时，大多使用where子句，但是在对聚集函数选择（限制）时，则使用hav

7、ing子句。v（七）返回部分查询结果 当查询结果过多时，需要返回部分查询结果，使用Limit实现，语法如下： limit offset, rows 其中offset表示其实行号，从0开始，offset可以省略，默认为0；rows表示返回的行数。 示例4-20：查询第1-3行学生的信息 Select * From studentMySQL数据库数据库 Limit 1,3;v（八）复合条件查询 当查询限定多个查询条件时，需要分析多个条件之间的逻辑关系。主要的关系有：and、or、not。And连接的两个表达式同时为真时，复合表达式为真，其余为假。Or连接的两个表达式只要有一个为真，则复合表达式为真

8、。Not将逻辑表达式的值进行翻转。 示例4-21：查询年龄大于20，性别为男的学生信息 Select * from student Where age20 and gender=男; 示例4-22：查询计算机、生物学院的学生信息 Seledct * from student Where depart=计算机 or depart=生物;MySQL数据库数据库任务二任务二 连接查询连接查询v 基础知识v （一）连接查询 连接查询就是将多个表中的数据结合在一起进行查询，而连接通常是 有条件的，连接条件格式： 表名1.列名1 表名2.列名2。 其中比较运算符有：=、=、 (select age fro

9、m student where name=王美霞)MySQL数据库数据库任务四任务四 集合查询集合查询v 基础知识 多个查询的结果可以进行集合操作，集合操作包括三种操作：并操作、交操作和差操作。这些操作都是两个或多于两个查询结果的结合操作，概念和集合理论中的概念相同，只是把查询结果看成集合，而查询结果中的行看成元素即可。 union： 将多个查询结果的行合并，作为单个结果集返回，并移除重的行。 union all： 将多个查询结果的行合并，不论是否有重复的行。 except： 返回只在except左侧存在但右侧不存在的行的集合。 intersect： 返回两个查询结果都存在的行。v （一）并操

10、作 并操作关键字为union，union用于把来自许多select语句的结果组合到一个结果集合中，语法如下：MySQL数据库数据库 select union all | distinct select union all | distinct select 列于每个select语句的对应位置的被选择的列应该类型相同。 如果您对union不使用关键词all，则所有返回的行都是唯一的，如同您已经对整个结果集合使用了distinct。如果您指定了all，您会从所有用过的select语句中得到所有匹配的行。 示例4-32：查询年龄大于20或性别为男的学生信息。 select * from studen

11、tMySQL数据库数据库 where age20 union select * from student where gender=男; v（二）交操作 交操作关键字为intersect，语法如下： select intersect all | distinct select . intersect all | distinct select MySQL数据库数据库 示例4-33：查询年龄大于20并且性别为男的学生。 select * from student where age20 intersect select * from student where gender=男;v（三）差操作

12、差操作关键字为except，语法如下： select except all | distinctMySQL数据库数据库 select except all | distinct select 示例4-34：查询大于20岁的不是男生的学生信息。 select * from student where age20 EXCEPT select * from student where gender=男;MySQL数据库数据库任务五任务五 函数查询函数查询v 基础知识 当前主流数据库都提供了丰富的系统函数，MySQL数据库也不例外，主要包括字符串函数、数学函数、日期函数等。限于篇幅，本书简单介绍比较常

13、用的函数。v （一）字符串函数 字符串函数如表4-3所示。MySQL数据库数据库MySQL数据库数据库 MySQL数据库数据库v （二）数值函数 数字函数如表4-4所示。MySQL数据库数据库MySQL数据库数据库v （三）日期和时间函数 日期和时间函数如表4-5所示。MySQL数据库数据库MySQL数据库数据库MySQL数据库数据库MySQL数据库数据库 v （四）其他函数 其他数字函数如表4-6所示。MySQL数据库数据库任务六任务六 视图视图v 基础知识v （一）视图概念 视图是从一个或几个基本表(或视图)导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的

14、数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。 视图一经定义，就可以和基本表一样被查询。也可以在一个视图之上再定义新的视图，但对视图的更新操作则有一定的限制。v （二）视图的作用 视图是数据库中及其重要的概念，是数据库体系结构中的外模式，对数据库的逻辑独立性具有重要作用。合理使用视图可以具有如下优势： 1. 简化用户操作MySQL数据库数据库 视图机制使用户可以将注意力集中在所关心的数据上，使数据库看起来结构简单、清晰，并且可以简化用户的数据查询操作。v2. 提供逻辑独立性 数据的逻辑独立性是指当数据库重构造时，用户的应用程序不会受影响。

15、v3. 提供安全保护 对不同的用户定义不同的视图，使数据不出现在不应看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能。v（三）视图SQL语法 1创建视图 create or replace view 试图名 (列名, 列名, ) as 查询 with cascaded check optionMySQL数据库数据库 其中replace子句能替换已有的视图；查询是一种select语句，给出了视图的定义，是视图定义的核心；with check option子句来防止插入或更新行。 注意：在视图定义中允许使用order by，但是从特定视图中进行查询时，如果查询具有自己or

16、der by语句，则试图定义中的order by将被忽略。 示例4-35：创建机械学院学生信息视图 create view view_jx_student as select * from student where depart=机械; 示例4-36：创建学生信息视图，视图中要有出生年份列MySQL数据库数据库 create or replace view view_year_student as select sno,name,year(now()-age_birth_year from student; select * from year_student; 示例4-37：创建学生选课信

17、息视图，报表学号，学生姓名、课程名和成绩。 create or replace view view_sc(sno,sname,cname,score) as select s.sno,,,sc.score from student s, course c, sc where s.sno=sc.sno and o=o;MySQL数据库数据库v 2. 查看视图 查看视图语法： Show create view 视图名; 示例4-38：查看视图view_jx_student。 Show create view view_jx_student; 示例4-39：查询机械学院学生信

18、息视图 Select * from view_jx_student 示例4-40：查询成绩大于70学生选课信息 Select * from view_sc where score70;MySQL数据库数据库v 3. 删除视图 删除视图语法： drop view 视图名; 示例4-41：删除view_jx_student视图 drop view view_jx_student;v 4. 修改视图 修改视图语法： alter view 试图名 as 查询 with cascaded check option 修改与查询的语法基本相同。MySQL数据库数据库实验四实验四 数据查询实验数据查询实验v

19、实验目的 熟练掌握SQL查询语句的使用。主要练习各种查询语句的构造，其中包括单表查询、连接查询、嵌套查询和集合查询。重点掌握连接查询和嵌套查询。v 实验条件 计算机（电脑） Windows 7操作系统 MySQL5.6数据库v 实验内容 1创建数据库chap4。 2创建数据表student、course和sc，表的结构描述如下。MySQL数据库数据库MySQL数据库数据库MySQL数据库数据库v 3插入数据 使用insert语句将下面三个表中的数据插入到相应的表中。 其中student插入如下数据。 Course插入如下数据。 Sc表插入如下数据。MySQL数据库数据库v 4. 单表查询 （1）查询全体学生的详细信息； （2）查询全体学生的姓名，学号和所在系； （3）查询全体学生的姓名及出生年份； （4）查询学校共有哪些系； （5）查询所有年龄在20岁以下的学生姓名及其年龄； （6）查询年龄不在2023岁之间的学生姓名、系别和年龄 。