欢迎光临
免费的PDF电子书下载网站
当前位置：网志栈 > 计算机/网络 > MySQL数据库应用与开发 PDF下载

MySQL数据库应用与开发 PDF下载

2020-12-23 分类：计算机/网络 阅读(1734)

编辑推荐

10小时微信视频,全程语音讲解。理论与实践结合：从基础知识学习，*终能综合所学内容开发应用。体现*MySQL特性 精心设计的习题：在重点难点部分，为读者精心设计了习题。这些习题有助于读者更好地理解重点，提高分析代码的能力，同时在编程中规避常见错误。 ；

内容简介

本书采用MySQL 5.7.17版本软件，全面系统地讲述了MySQL数据库的基础知识和基本操作，以及各种常用数据库对象的创建和管理、MySQL语言及其应用、数据库的备份与恢复、安全管理、日志管理与性能优化等。对数据操作中较为常用的数据检索、数据完整性、视图、存储过程、触发器、并发控制等内容进行了详细的阐述，并介绍了利用PHP访问MySQL数据库的方法和利用JSP开发MySQL数据库应用系统的基本过程。 全书体系完整、结构安排合理、内容翔实、例题丰富、可操作性强,并对主要操作单元配制微课视频。内容涵盖了MySQL数据库要用到的主要知识点。 本书适合作为高等院校本科、专科计算机及相关专业数据库管理和应用系统开发课程的教材，也可作为从事数据库管理与开发的信息技术领域的科技工作者的参考用书。另外，本书还配有辅导教材《MySQL数据库应用与开发习题解答与上机指导》，以帮助读者进一步巩固所学MySQL数据库的知识。

作者简介

暂无

MySQL数据库应用与开发 PDF下载

目录

目录
第1章MySQL数据库概述
1.1认识MySQL数据库
1.2数据库的基本概念
1.2.1信息与数据库
1.2.2结构化查询语言（SQL）
1.2.3数据库管理系统
1.2.4数据库系统
1.3关系数据库理论
1.3.1概念模型及其表示方法
1.3.2数据模型
1.3.3关系运算
1.4MySQL数据库软件的使用
1.4.1MySQL 5.7的安装和配置步骤

前沿

前言

Oracle公司的MySQL是目前最流行的关系数据库管理系统之一。MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL数据库以其精巧灵活、运行速度快、经济适用性强、开放源码等优势，作为网站数据库获得许多中小型网站的开发公司的青睐。MySQL性能卓越，搭配PHP和Apache可组成良好的软件开发环境，并且已经大量部署到中小型企业和高校的教学平台。 本书从教学实际需求出发，结合初学者的认知规律，由浅入深、循序渐进地讲解MySQL数据库管理与开发过程中的知识。全书以MySQL数据库软件和数据库对象的基本操作为主线，将数据库理论内容嵌入到实际操作中去介绍，能够让学生在操作过程中进一步认知数据管理的理念，体察数据操作的优势，提高数据处理的能力。全书体系完整、可操作性强，以大量的例题对常用知识点操作进行示范，所有的例题全部通过调试，内容涵盖了设计一个数据库应用系统要用到的主要知识。重点操作还增加了Workbench软件可视化操作的详细过程，并对主要操作单元配制100多个微课视频。本书共分15章，现将本书的主要内容简单介绍如下： 第1章MySQL数据库概述。介绍有关MySQL数据库管理系统的基础知识和关系数据库理论。第2章MySQL语言基础。介绍MySQL的数据类型、运算符、常用函数和表达式等。第3章MySQL数据库的基本操作。介绍MySQL数据库的设计、创建和管理的基本操作，以及利用MySQL Workbench管理数据库的基本操作等内容。第4章表及数据完整性。介绍MySQL数据表的创建和管理、数据的常用操作和数据完整性的实现等内容。第5章数据检索。介绍利用select语句进行数据查询的内容，包括单表查询、多表连接、子查询及使用正则表达式进行模糊查询等。第6章索引和视图。介绍索引和视图的创建及管理，以及视图的应用等。第7章MySQL编程基础。主要介绍变量、begin...end语句块的应用，自定义函数创建和维护管理，MySQL的控制流语句的应用。第8章存储过程、游标和触发器。通过介绍存储过程的创建、应用和管理，并利用存储过程实现了游标、触发器和事件等数据库对象的创建及应用。第9章并发事务与锁机制。介绍事务的并发处理机制和锁机制的功能和应用。第10章权限管理及安全控制。介绍MySQL权限系统的工作原理、账户管理、权限管理等MySQL数据库安全常见问题。第11章备份与恢复。介绍MySQL数据库的备份和恢复的基本理论和基本操作，还介绍表的导入与导出等基本操作。第12章MySQL性能优化。介绍优化MySQL服务器的方法、优化查询的概念和操作。第13章MySQL日志文件管理。分别介绍错误日志、二进制日志、通用查询日志和慢查询日志的文件管理和应用。
第14章使用PHP操作MySQL数据库。介绍PHP语言的特点和搭建PHP MySQL的集成开发环境的过程，以及使用PHP操作MySQL数据库的常见方法。第15章基于JSP技术的MySQL数据库应用开发实例。介绍基于JSP技术的MySQL数据库应用开发实例的数据库设计、在线考试系统的应用开发、运行与测试过程。本书由姜桂洪、孙福振、苏晶等编写，由姜桂洪统稿。在本书编写过程中还参阅了大量的数据库方面的文献和网站资料，在此对提供者一并深表感谢。  另外，本书还配有辅导教材《MySQL数据库应用与开发习题解答与上机指导》(姜桂洪等编著)，内容包括本书所有习题的详尽参考答案、模拟试题、MySQL软件安装配置的常见操作、MySQL数据库的常用可视化软件Workbench、Navicat和phpMyAdmin的安装、配置和基本操作方法，以及按本书章节顺序配备的实验及实验指导等。由于作者水平有限，书中纰漏之处在所难免，恳请读者批评指正。编者2018年1月

免费在线读

第5章数 据 检 索

数据检索是指从数据库中按照预定条件查询数据，及引用相关数据进行计算而获取所需信息的过程。查询数据是数据库操作中最常用、最重要的操作。MySQL是通过select语句查询实现数据检索的。本章将介绍利用select语句进行单表查询、多表连接和子查询的详细操作。5.1基本查询语句

select语句

select语句是SQL语言从数据库中获取信息的一个基本语句。该语句可以实现从一个或多个数据库中的一个或多个表中查询信息，并将结果显示为另外一个二维表的形式，称为结果集(result set)。select语句的基本语法格式可归纳如下： 
select ［all|distinct］selection_list
from  table_source  
［where search_condition］ 
［group by grouping_columns］ ［with rollup］
［having search_condition］
［order by order_expression ［asc|desc］］
［limit count］
说明：  ［］： 表示可选项格式。 select： 描述结果集的列，是一个用逗号分隔的表达式列表。每个选择列表表达式通常是对从中获取数据源列的引用，但也可能是其他表达式。all是默认值，代表所有行。distinct取消结果集中的重复行。 from： 指定所要查询数据源，如表、视图、表达式等。可以指定两个以上的表，表与表之间用逗号隔开。 where： 定义源表中的行要满足select语句的要求所必须达到的条件。 group by： 用于对查询结构根据grouping_columns的值进行分组。使用带rollup操作符的group by子句，指定在结果集内不仅包含由group by提供的正常行，还包含汇总行。 having： having子句是应用于分组结果集的附加条件。having子句通常与group by子句一起使用，用来在group by子句后选择行。 order by： 用于对查询结果进行排序。 asc|desc： 用于指定行的排序，asc代表升序，是默认值，desc代表降序。 limit： 限制查询的输出结果行。通常与order by子句一起使用。下面先介绍select语句的简单应用。(1) 使用select语句查询一个数据表。使用select语句时，首先要确定所要查询的列。“*”代表所有的列。【例51】查询teaching数据库course表中的所有数据。代码和运行结果如下：  
mysql> use teaching;
Database changed
mysql> select * from  course;
-------- --------- ------ -------- ----- ------
| courseno| cname| type| period| exp| term|
-------- --------- ------ -------- ----- ------
| c05103| 电子技术| 必修| 64|  16|2|
| c05109| C语言| 必修| 48|  16|2| 
| c05127| 数据结构| 必修| 64|  16|2|
| c05138| 软件工程| 选修| 48|   8|5|
| c06108| 机械制图| 必修| 60|   8|2|
| c06127| 机械设计| 必修| 64|   8|3|
| c06172| 铸造工艺| 选修| 42|  16|6|
| c08106| 经济法| 必修| 48|   0|7|
| c08123| 金融学| 必修| 40|   0|5|
| c08171| 会计软件| 选修| 32|   8|8|
-------- --------- ------ -------- ----- ------
10 rows in set (0.06 sec)
这是查询整个表中所有列的操作，还可以针对表中的某一列或多列进行查询。(2) 查询表中的指定列。针对表中的多列进行查询，只要在select后面指定要查询的列名即可，多列之间用“，”分隔。【例52】查询student表中的studentno、sname和phone数据。代码和运行结果如下：  
mysql> select studentno,sname,phone from student; 
----------- ------ -----------
| studentno| sname| phone|
----------- ------ -----------
| 18122210009| 许东山| 13623456778|
| 18122221324| 何白露| 13178978999|
| 18125111109| 敬横江| 15678945623|
| 18125121107| 梁一苇| 13145678921|
| 18135222201| 凌浩风| 15978945645|
| 18137221508| 赵临江| 12367823453|
| 19111133071| 崔依歌| 15556845645|
| 19112100072| 宿沧海| 12545678998|
| 19112111208| 韩山川| 15878945612|
| 19122203567| 封月明| 13245674564|
| 19123567897| 赵既白| 13175689345|
| 19126113307| 梅惟江| 13245678543|
----------- ------ -----------
12 rows in set (0.03 sec)
(3) 可以从一个或多个表中获取数据。使用select语句进行查询，需要确定所要查询的数据在哪个表中，或在哪些表中，在对多个表进行查询时，同样使用“，”对多个表进行分隔。进行多表查询，主要采用多表连接或子查询的方式，也可以通过where子句中使用连接运算来确定表之间的联系，然后根据这个条件返回查询结果。5.2单 表 查 询单表查询是指从一张表中查询所需要的数据。下面将通过select语句的各个子句的应用介绍在单表上进行查询的常见操作。5.2.1select...from基本子句的使用select子句的主要功能是输出字段或表达式的值，form子句的主要功能是指定数据源。这两个子句在进行数据库表查询时，都是必选项。下面结合select子句的输出项的操作介绍查询语句的基本操作。1. 为字段取别名利用select语句查询数据时，输出项一般显示创建表时定义的字段名。MySQL可以为查询显示的每个输出字段或表达式取一个别名，以增加结果集的可读性。例如，可以用as关键字给字段取一个中文名。实现给select子句中的各项取别名，其语法格式为： 
select 项的原名as别名 
【例53】在student表中查询出生日期在2001年以后的学生的学号、姓名、电话和年龄。分析： 可以通过as为列或表达式更改名称，增加可读性。代码和运行结果如下： 
mysql> select  studentno as 学号,sname as 姓名,
-> phone as 手机号,year(now())-year(birthdate) as  年龄
-> from  student
-> where year(birthdate)>2001;
----------- ------- ------------ ------
| 学号| 姓名| 手机号| 年龄|
----------- ------- ------------ ------
| 19112100072| 宿沧海| 12545678998|   15|
| 19122203567| 封月明| 13245674564|   15|
| 19123567897| 赵既白| 13175689345|   15|
| 19126113307| 梅惟江| 13245678543|   14|
----------- ------- ------------ ------
4 rows in set (0.03 sec)
2. 使用谓词过滤记录如果希望一个列表没有重复值，可以利用distinct子句从结果集中除去重复的行。当使用distinct子句时，需要注意以下事项： (1) 选择列表的行集中，所有值的组合决定行的唯一性。(2) 数据检索包含任何唯一值组合的行，如果不指定distinct子句则将所有行返回到结果集中。【例54】在score表中查询期末成绩中有高于95的学生的学号和课程号,并按照学号排序。分析： 不管学生有几门课的成绩高于95，只要有一门就可以显示，利用distinct子句可将重复行消除。代码和运行结果如下： 
mysql> select  distinct studentno,courseno
-> from   score
-> where final>95
-> order  by studentno;
----------- --------
| studentno| courseno|
----------- --------
| 18125111109| c08106|
| 18137221508| c08171|
| 19112100072| c06108|
| 19122203567| c05103|
| 19123567897| c06127|
----------- --------
5 rows in set (0.00 sec)

where子句

5.2.2使用where子句过滤结果集1. 查询符合指定条件的记录数据
如果要从很多记录中查询出指定的记录，那么就需要一个查询的条件。设定查询条件应用的是where子句，通过where子句可以实现很多复杂的条件查询。在使用where子句时，需要使用一些比较运算符来确定查询的条件。【例55】查询表student中入学成绩在800分以上的学生的学号、姓名和电话信息。分析： 本例中要求输出学号、姓名和电话信息，即为select子句输出表列数据源为表student，条件为入学成绩在800分以上。代码和运行结果如下： 
mysql> select  studentno,sname,phone
-> from   student
-> where  entrance>800;
----------- ------ -----------
| studentno| sname| phone|
----------- ------ -----------
| 18122221324| 何白露| 13178978999|
| 18135222201| 凌浩风| 15978945645|
| 19122203567| 封月明| 13245674564|
| 19123567897| 赵既白| 13175689345|
----------- ------ -----------
4 rows in set (0.00 sec)
2. 带in关键字的查询in关键字可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足查询条件，该记录将被查询出来； 如果不在集合中，则不满足查询条件。实际上，使用in搜索条件相当于用or连接两个比较条件，如“x in(10，15)”相当于表达式“x=10 or x=15”。也可以使用not in关键字查询不在某取值范围内的记录行数据。【例56】查询学号分别为18135222201、18137221508和19123567897的学生学号、课程号、平时成绩和期末成绩。分析： 检索条件中枚举某些确定值的范围，一般可以利用in关键字来实现。代码和运行结果如下： 
mysql> select studentno,courseno ,daily ,final
-> from score
-> where studentno in(18135222201,18137221508,19123567897);
----------- --------- ------ -----
| studentno| courseno| daily| final|
----------- --------- ------ -----
| 18135222201| c05109|  99.0|  92.0|
| 18135222201| c08171|  95.0|  82.0|
| 18137221508| c08106|  80.0|  95.0|
| 18137221508| c08123|  78.0|  89.0|
| 18137221508| c08171|  88.0|  98.0|
| 19123567897| c05103|  85.0|  77.0|
| 19123567897| c06127|  99.0|  99.0|
----------- --------- ------ -----
7 rows in set (0.07 sec)
3. 带between and的范围查询在where子句中，可以使用between搜索条件检索指定范围内的行。使用between搜索条件相当于用and连接两个比较条件，如“ x between 10 and 27”相当于表达式“ x>=10 and x<=27 ”。由此可见，在生成结果集中，边界值也是符合条件的。检索条件指定排除某个范围的值，一般可以利用not between关键字来实现。【例57】查询选修课程号为c05109的学生学号和期末成绩，并且要求平时成绩在80~95分。分析： 检索条件设置在某个范围内，一般可以利用between关键字来实现。代码和运行结果如下： 
mysql> select studentno, final
-> from score
-> where courseno=c05109 and daily between 80 and 95;
----------- -------
| studentno| final|
----------- -------
| 18122221324|  77.0|
| 18125121107|  62.0|
| 19112100072|  86.0|
| 19112111208|  91.0|
----------- -------
4 rows in set (0.00 sec)
4. 带like的字符匹配查询使用通配符结合的like搜索条件，通过进行字符串的比较来选择符合条件的行。当使用like搜索条件时，模式字符串中的所有字符都有意义，包括开头和结尾的空格。like主要用于字符类型数据。字符串内的英文字母和汉字都算一个字符。也可用通配符并使用not like作为查询条件。like属于较常用的比较运算符，通过它可以实现模糊查询。它有两种通配符： “%”和下画线“_”：  “%”可以匹配一个或多个字符，可以代表任意长度的字符串，长度可以为0。 “_”只匹配一个字符。【例58】在student表中显示所有姓何或姓韩的学生的姓名、生日和Email。分析： 设置where条件实现上述要求，需要采用or和like等逻辑运算。like操作符可以和通配符一起将列的值与某个特定的模式作比较，列的数据类型可以是任何字符串类型。代码和运行结果如下： 
mysql> select  sname, birthdate, Email
-> from  student
-> where   sname  like 何% or sname  like 韩%;
------ ----------- -------------
| sname| birthdate| Email|
------ ----------- -------------
| 何白露| 2000-12-04| heyy@sina.com|
| 韩山川| 2001-02-14| han@163.com|
------ ----------- -------------
2 rows in set (0.03 sec)
5. 用is null关键字查询空值涉及空值的查询用null来表示。create table语句或alter table语句中的null表明在列中允许存在被称为null的特殊数值，它不同于数据库中的其他任何值。在select语句中，where子句通常会返回比较的计算结果为真的行。那么，在where子句中，如何处理null的值的比较呢？为了取得列中含有null的行，MySQL语句包含了操作符功能is ［not］ null。说明：  一个字段值是空值或者不是空值，要表示为“is null”或“is not null”； 不能表示为“=null”或“<>null”。 如果写成“字段=null”或“字段<>null”，系统的运行结果都直接处理为null值，按照false处理而不报错。where子句有以下通用格式： 
column is ［not］ null
下面通过例题介绍空值查询的方法。【例59】在se_score表中添加成绩字段score，查询se_score表中学生的学号、课程号和成绩。分析： 学生选修课程表se_course中的成绩允许空值，以此是否成绩为空值作为查询条件，即可查到学生的选课情况。代码和运行结果如下： 
mysql> alter table se_course
-> add score float(3,1) null aften teacherno;
Query OK, 0 rows affected (1.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select  studentno, courseno,teacherno, score
-> from  se_course
-> where score is null;
------------ ---------- ---------- -------
| studentno| courseno| teacherno|score|
------------ ---------- ---------- -------
|19120000111| co1236| t01237|  null|
|19120000222| co1237| t01239|  null|
------------ ---------- ---------- -------
2 rows in set (0.00 sec)
6. 带and的多条件查询where子句的主要功能是利用指定的条件选择结果集中的行。符合条件的行出现在结果集中，不符合条件的行将不出现在结果集中。利用where子句指定行时，条件表达式中的字符型和日期类型值要放到单引号内，数值类型的值直接出现在表达式中。【例510】在score表中显示期中成绩高于90分、期末成绩高于85分的学生学号、课程号和成绩。分析： 设置where条件实现上述要求，需要采用and逻辑运算，将两个比较运算表达式连接起来。代码和运行结果如下： 
mysql> select studentno,courseno,daily,final
-> from score
-> where daily >=90 and final >= 85;
----------- ---------- ------- -------
| studentno| courseno| daily| final|
----------- ---------- ------- -------
| 18135222201| c05109|  99.0|  92.0|
| 19112100072| c06108|  97.0|  97.0|
| 19123567897| c06127|  99.0|  99.0|
----------- ---------- ------- -------
3 rows in set (0.00 sec)
7. 带or的多条件查询带or的多条件查询，实际上是指只要符合多条件中的一个，记录就会被搜索出来； 如果不满足这些查询条件中的任何一个，这样的记录将被排除掉。or可以用来连接两个条件表达式。而且，可以同时使用多个or关键字连接多个条件表达式。【例511】查询计算机学院的具有高级职称教师的教师号、姓名和从事的专业。分析： where子句设置的条件包括部门和职称，其中高级职称又包括教授和副教授两类，需要包括or和and两种逻辑运算。代码和运行结果如下： 
mysql> select teacherno,tname, major
-> from teacher
-> where department=计算机学院 and (prof=副教授or prof=教授);
--------- -------- --------
| teacherno| tname| major|
--------- -------- --------
| t05001| 苏超然| 软件工程|
| t05003| 孙释安| 网络安全|
| t05011| 卢敖治| 软件工程|
--------- -------- --------
3 rows in set (0.06 sec)
5.2.3使用order by子句对结果集排序

order by子句

使用order by子句可以对查询的结果进行升序(asc)或降序(desc)排列。排序可以依照某个列的值，若列值相等则根据第2个属性的值，以此类推。利用order by子句进行排序，需要注意如下事项和原则： (1) 默认情况下，结果集按照升序排列。也可以在输出项的后面加上关键字desc来实现降序输出。对含有null值的列进行排序时，如果是按升序排列，null值将出现在最前面，如果是按降序排列，null值将出现在最后。(2) order by子句包含的列并不一定出现在选择列表中。(3) order by子句可以通过指定列名、函数值和表达式的值进行排序。(4) order by子句不可以使用text、ntext或image类型的列。(5) 在order by子句中可以同时指定多个排序项。【例512】在student表中查询高于850分的学生学号、姓名和入学成绩，并按照入学成绩的降序排列。分析： 升序asc是默认值，而降序desc必须表明，也可以给字段取别名。代码和运行结果如下： 
mysql> select studentno 学号,sname 姓名 ,entrance 入学成绩
-> from student
-> where entrance>850
-> order by entrance  desc;
----------- ------- --------
| 学号| 姓名| 入学成绩|
----------- ------- --------
| 19123567897| 赵既白|  999|
| 19122203567| 封月明|  898|
| 18122221324| 何白露|  879|
| 18135222201| 凌浩风|  867|
----------- ------- --------
4 rows in set (0.02 sec)
【例513】在score表中查询总评成绩大于90分的学生的学号、课程号和总评成绩，并先按照课程号的升序、再按照总评成绩的降序排列。总评成绩计算公式如下： 
总评成绩=daily*0.2 final*0.8
分析： 本例利用表达式作比较和排序的依据。代码和运行结果如下： 
mysql> select courseno 课程号,daily *0.2 final*0.8 as 总评,studentno 学号
-> from score
-> where daily *0.2 final*0.8>90
-> order by courseno, daily *0.2 final*0.8 desc;
-------- ----- ------------
| 课程号| 总评|  学号|
-------- ----- ------------
| c05103| 91.4| 19122203567|
| c05109| 93.4| 18135222201|
| c06108| 97.0| 19112100072|
| c06108| 93.8| 19112111208|
| c06127| 99.0| 19123567897|
| c08106| 95.0| 18125111109|
| c08106| 92.0| 18137221508|
| c08123| 90.6| 18125111109|
| c08171| 96.0| 18137221508|
-------- ----- ------------
9 rows in set (0.00 sec)
5.2.4group by子句和having子句的使用

group by子句

group by子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用group by子句，则只为select语句报告一个聚合值。将一列或多列定义成为一组，使组内所有的行在那些列中的数值相同。出现在查询的select列表中的每一列都必须同时出现在group by子句中。1. 使用group by关键字来分组单独使用group by关键字，查询结果只显示每组的一条记录。【例514】利用group by子句对score表数据分组，显示每个学生的学号和平均总评成绩。总评成绩计算公式如下： 
总评成绩=daily*0.3 final*0.7
分析： 通过学号分组，可以求出每个学生的平均总评成绩。avg()函数用于求平均值，round()函数用于对平均值的某位数据进行四舍五入。代码和运行结果如下： 
mysql> select studentno 学号, round(avg(daily*0.3 final*0.7),2) as平均分
-> from score
-> group by studentno;
----------- ------
| 学号| 平均分|
----------- ------
| 18122210009|  85.15|
| 18122221324|  75.50|
| 18125111109|  90.13|
| 18125121107|  78.00|
| 18135222201|  90.00|
| 18137221508|  90.40|
| 19111133071|  76.70|
| 19112100072|  91.65|
| 19112111208|  91.20|
| 19122111208|  70.30|
| 19122203567|  87.37|
| 19123567897|  89.20|
| 19126113307|  79.45|
----------- ------
13 rows in set (0.04 sec)
2. group by关键字与group_concat()函数一起使用使用group by关键字和group_concat()函数查询，可以将每个组中的所有字段值都显示出来。【例515】使用group by关键字和group_concat()函数对score表中的studentno字段进行分组查询。可以查看选学该门课程的学生学号。代码和运行结果如下： 
mysql> select courseno 课程号,group_concat(studentno) 选课学生学号
-> from score  
-> group by courseno ;
---------- ----------------------------------------------
| 课程号|选课学生学号|
---------- ----------------------------------------------
| c05103| 18122210009,18122221324,18125121107,……|
| c05108| 19122203567|
| c05109| 18122210009,18122221324,18125121107,……|
| c06108| 19112100072,19112111208,19126113307|
| c06127| 19122111208,19122203567,19123567897|
| c08106| 18125111109,18137221508|
| c08123| 18125111109,18137221508|
| c08171| 18125111109,18135222201,18137221508,19126113307|
---------- ----------------------------------------------
8 rows in set (0.00 sec)
3. group by关键与having一起使用select语句中的where和having子句控制用数据源表中的哪些行来构造结果集。where和having是筛选，这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用来构造结果集。having子句通常与group by子句结合使用，尽管指定该子句时也可以不带group by。having子句指定在应用where子句的筛选后要进一步应用的筛选。【例516】查询选课在3门以上且各门课程期末成绩均高于75分的学生的学号及其总成绩，查询结果按总成绩降序列出。分析： 可以利用having子句筛选分组结果，使之满足count(*)>=3的条件即可。代码和运行结果如下： 
mysql> select studentno 学号,sum(daily*0.3 final*0.7) as 总分
-> from  score
-> where final>=75
-> group by  studentno
-> having count(*)>=3
-> order by sum(daily*0.3 final*0.7) desc;
------------ -------
|学号| 总分|
------------ -------
| 18137221508| 271.2|
| 18125111109| 270.4|
| 19122203567| 262.1|
------------ -------
3 rows in set (0.00 sec)
5.2.5用limit限制查询结果的数量

limit子句

limit是用来限制查询结果的数量的子句。可以指定查询结果从哪条记录开始显示。还可以指定一共显示多少条记录。limit可以指定初始位置，也可以不指定初始位置。【例517】查询student表的学号、姓名、出生日期和电话，按照entrance进行降序排列，显示前3条记录。代码和运行结果如下： 
mysql> select studentno,sname,birthdate,phone
-> from  student  
-> order by  entrance  desc  
-> limit  3;
------------ -------- ----------- ------------
| studentno| sname| birthdate| phone|
------------ -------- ----------- ------------
| 19123567897| 赵既白| 2002-08-04|  13175689345|
| 19122203567| 封月明| 2002-09-09|   13245674564| 
| 18122221324| 何白露| 2000-12-04|   13178978999|
------------ -------- ----------- ------------
3 rows in set (0.00 sec)
使用limit还可以从查询结果的中间部分取值。首先要定义两个参数，参数1是开始读取的第1条记录的编号(注意在总查询结果中，第1条记录编号为0)； 参数2是要查询记录的个数。【例518】查询score表中，期末成绩final高于85分的，按照平时成绩daily进行升序排列，从编号2开始，查询5条记录。代码和运行结果如下： 
mysql> select  *  from  score  
-> where  final>85  
-> order  by  daily  asc  
-> limit  2,5;
------------- --------- ------ -------
| studentno| courseno| daily| final|
------------- --------- ------ -------
| 18122210009| c05109|  77.0|  91.0|
| 18125111109| c08171|  77.0|  92.0|
| 18137221508| c08123|  78.0|  89.0|
| 18125111109| c08106|  79.0|  99.0|
| 19122203567| c06127|  79.0|  88.0|
------------- --------- ------ -------
5 rows in set (0.00 sec)
5.3聚合函数查询

聚合函数

MySQL的常用聚合函数包括count()、sum()、avg()、max()和min()等。其中，count()用来统计记录的条数； sum()用来计算字段的值的总和； avg()用来计算字段的值的平均值； max()用来查询字段的最大值； min()用来查询字段的最小值。利用聚合函数可以满足表中记录的聚合运算。例如，需要计算学生成绩表中的平均成绩，可以使用avg()函数。group by关键字通常需要与聚合函数一起使用。5.3.1count()函数count()函数对于除“*”以外的任何参数，返回所选择聚合中非null值的行的数目； 对于参数“*”，返回选择聚合所有行的数目，包含null值的行。没有where子句的count(*)是经过内部优化的，能够快速地返回表中所有的记录总数。【例519】通过查询求18级学生的总数。分析： 求学生数即为求符合要求的记录行数，一般利用count()函数实现。代码和运行结果如下： 
mysql> select count(studentno) as 18级学生数
-> from student
-> where substring(studentno,1,2) =18;
----------
| 18级学生数|
----------
|  6|
----------
1 row in set (0.03 sec)
5.3.2sum()函数和avg()函数sum()函数可以求出表中某个字段取值的总和。avg()函数可以求出表中某个字段取值的平均值。【例520】查询score表中学生的期末总成绩大于270分的学生学号、总成绩及平均成绩。分析： 先按照studentno对final值进行分组，再利用sum()函数和avg()函数分别求期末总成绩和平均值，然后进行期末总成绩大于270分学生的筛选。代码和运行结果如下： 
mysql> select studentno 学号, sum(final) 总分, avg(final)  平均分
-> from score
-> group by studentno
-> having sum(final)>270
-> order by studentno;
----------- ------- --------
|学号| 总分| 平均分|
----------- ------- --------
| 18125111109| 283.0| 94.33333|
| 18137221508| 282.0| 94.00000|
| 19122203567| 275.0| 91.66667|
----------- ------- --------
3 rows in set (0.00 sec)
5.3.3max()函数和min()函数max()函数可以求出表中某个字段取值的最大值,min()函数可以求出表中某个字段取值的最小值。【例521】查询选修课程号为c05109的课程的期末最高分、最低分及之间相差的分数。分析： 分别利用max()和min()函数求得final的最大最小值。代码和运行结果如下： 
mysql> select  max(final) 最高分, min(final) 最低分, 
-> max(final)- min(final) as  分差
-> from score
-> where (courseno = c05109);
------ ------ ----
| 最高分| 最低分| 分差|
------ ------ ----
| 92.0| 62.0| 30.0|
------ ------ ----
1 row in set (0.02 sec)
5.3.4利用group by子句与with rollup一起进行统计MySQL中with rollup的应用，可以在分组统计数据的基础上再进行相同的总体统计。例如，对于成绩表中，查询某一门课的平均值和所有成绩的平均值，普通的group by语句是不能实现的。【例522】查询score表中每一门课的期末平均值和所有成绩的平均值。分析： 如果使用有with rollup子句的group by语句，则可以实现这个要求。代码和运行结果如下： 
mysql> select courseno 课程号,avg(final) 课程期末平均分
-> from score
-> group by courseno with  rollup;
------- ------------
| 课程号| 课程期末平均分|
------- ------------
| c05103|79.83333|
| c05108|89.00000|
| c05109|83.00000|
| c06108|91.33333|
| c06127|84.66667|
| c08106|97.00000|
| c08123|90.50000|
| c08171|87.75000|
| null|85.82143|
------- ------------
9 rows in set (0.00 sec)
运行结果中，最后一行即为所有成绩的平均分。5.4多 表 连 接连接是关系型数据库中常用的多表查询数据的模式，连接可以根据各个表之间的逻辑关系来利用一个表中的数据选择另外的表中的行实现数据的关联操作。要在数据库中完成复杂的查询，必须将两个或两个以上的表连接起来。连接条件可在from或where子句中指定。连接条件与where和having搜索条件组合，用于控制from子句引用的数据源中所选定的行。MySQL处理连接时，查询引擎从多种可能的方法中选择最高效的方法处理连接。尽管不同连接的物理执行可以采用多种不同的优化，但逻辑序列都是通过应用from、where和having子句中的连接条件和搜索条件实现的。连接条件中用到的字段虽然不必具有相同的名称或相同的数据类型，但是如果数据类型不相同，则必须兼容或可进行隐性转换。MySQL显式定义了连接操作，增强了查询的可读性。被显式定义的与连接有关的关键字如下： (1) Inner Join： 内连接，结果只包含满足条件的列。(2) Left Outer Join： 左外连接，结果包含满足条件的行及左侧表中的全部行。(3) Right Outer Join： 右外连接，结果包含满足条件的行及右侧表中的全部行。(4) Cross Join： 结果只包含两个表中所有行的组合，指明两表间的笛卡儿操作。5.4.1内连接

内连接
内连接(Inner Join)查询是通过比较数据源表间共享列的值，从多个源表检索符合条件的行的操作。可以使用等号运算符的连接，也可以连接两个不相等的列中的值。【例523】查询选修课程号为c05109的学生的学号、姓名和期末成绩。分析： 本例中要求所输出的列分别在student表和score表中，可以通过studentno列、使用内连接的方式连接两个表，找出选修课程号为c05109的行。程序中两个表存在相同的列studentno，引用时需要标明该列所属的源表。代码和运行结果如下： 
mysql> select student.studentno,sname,final
-> from   student inner join score
-> on student.studentno= score.studentno
-> where  score.courseno = c05109;
---------- ------ ------
| studentno| sname| final|
---------- ------ ------
| 18122210009| 许东山|  91.0|
| 18122221324| 何白露|  77.0|
| 18125121107| 梁一苇|  62.0|
| 18135222201| 凌浩风|  92.0|
| 19111133071| 崔依歌|  82.0|
| 19112100072| 宿沧海|  86.0|
| 19112111208| 韩山川|  91.0|
---------- ------ ------
7 rows in set (0.01 sec)
还有一种方法，就是直接通过where子句的复合条件查询，可以实现与内连接的同样结果。代码如下： 
mysql> select student.studentno,sname,final 
-> from   student,score 
-> where  student.studentno= score.studentno 
-> and score.courseno = c05109;
5.4.2外连接

外连接

外连接(Outer Join)包括满足搜索条件的连接表中的所有行，甚至包括在其他连接表中没有匹配行的一个表中的行。对于当一个表中的行与其他表中的行不匹配时返回的结果集行，为解析为不存在相应行的表的所有结果集列提供null值。外连接会返回from子句中提到的至少一个表或视图中的所有行，只要这些行符合任何where或having搜索条件。将检索通过左外部连接引用的左表中的所有行，以及通过右外部连接引用的右表中的所有行。外连接是使用outer join关键字将两个表连接起来。外连接生成的结果集不仅包含符合连接条件的行数据，而且还包括左表(左外连接时的表)、右表(右外连接时的表)中所有的数据行。1. 左外连接左外连接(Left Outer Join)是指将左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并在右表的相应列中添加null值。【例524】在mysqltest数据库中利用左外连接方式查询学生的学号、姓名、平时成绩和期末成绩。分析： 当右表中的行与左表中的行不匹配时，左外连接方式将会将右表的所有结果集列赋予null值。代码和运行结果如下： 
mysql> use mysqltest;
Database changed
mysql> select  student02.studentno,sname,daily,final
-> from  student02  left join score1
-> on student02.studentno=score1.studentno;
----------- ------- ------ -----
| studentno| sname| daily| final|
----------- ------- ------ -----
| 16101111111| 张思睿|  null|  null|
| 16302222222| 李佛|  null|  null|
| 18133333333| 王发务|  null|  null|
----------- ------- ------ -----
3 rows in set (0.09 sec)
2. 右外连接右外连接(Right Outer Join)也是外部连接的一种，其中包含join子句中最右侧表的所有行。如果右侧表中的行与左侧表中的行不匹配，将为结果集中来自左侧表的所有列分配null值。【例525】利用右外连接方式查询教师的排课情况。分析： 当左表中的行与右表中的行不匹配时，右外连接方式将会将左表的所有结果集列赋予null值。代码和运行结果如下： 
mysql> select teacher.teacherno,tname, major, courseno
-> from  teacher right join teach_course
-> on teacher.teacherno = teach_course.teacherno;
--------- ------ -------- --------
| teacherno| tname| major| courseno|
--------- ------ -------- --------
| t05001| 苏超然| 软件工程| c05109|
| t05002| 常杉| 会计学| c05127|
| t05003| 孙释安| 网络安全| c05127|
| t05011| 卢敖治| 软件工程| c05138|
| t05017| 茅佳峰| 软件测试| c05127|
| t06011| 夏南望| 机械制造| c06127|
| t06023| 葛庭宇| 铸造工艺| c06172|
| t07019| 韩既乐| 经济管理| c08123|
| t08017| 时观| 金融管理| c08106|
| null| null| null| c09091|
--------- ------ -------- --------
10 rows in set (0.00 sec)

交叉连接

5.4.3交叉连接交叉连接(Cross Join )是在没有where子句的情况下，产生的表的笛卡儿积。两个表作交叉连接时，结果集大小为二者行数之积。该种方式在实际过程中用得很少。【例526】显示student表和score表的笛卡儿积。分析： 其结果集336行数据，应是student表数据行数与score表行数的乘积数。代码和运行结果如下： 
mysql> select  student.studentno,sname,score.*
-> from   student cross join score;
----------- ------- ---------- -------- ------ ------
| studentno| sname| studentno| courseno| daily| final|
----------- ------- ---------- -------- ------ ------
| 18122210009| 许东山| 18122210009| c05103|  87.0|  82.0|
| 18122221324| 何白露| 18122210009| c05103|  87.0|  82.0|
…
| 19122203567| 封月明| 19126113307| c08171|  88.0|  79.0|
| 19123567897| 赵既白| 19126113307| c08171|  88.0|  79.0|
| 19126113307| 梅惟江| 19126113307| c08171|  88.0|  79.0|
----------- ------- ---------- -------- ------ ------
336 rows in set (0.01 sec)

连接多个表
5.4.4连接多个表从理论上说，对于使用select语句进行连接的表数目没有上限。但在一条select语句中连接的表多于10个，那么数据库就很可能达不到最优化设计，MySQL引擎的执行计划会变得非常烦琐。需要注意的是，对于3个以上关系表的连接查询，一般遵循下列规则： 连接n个表至少需要n-1个连接条件，以避免笛卡儿积的出现。为了缩小结果集，采用多于n-1个连接条件或使用其他条件都是允许的。【例527】查询18级学生的学号、姓名、课程名、期末成绩及学分。分析： 本例要求输出的各项分别存在于student、 course和score三个表中，因此至少需要创建两个连接条件。每16个学时，计为1学分。代码和运行结果如下： 
mysql> select student.studentno,sname,cname,final,round(period/16,1)
-> from score  join student on  student.studentno=score.studentno
-> join  course on  score.courseno=course.courseno
-> where  substring(student.studentno,1,2)=18;
----------- ------- ---------- ------- ----------------
| studentno| sname| cname| final|round(period/16,1)|
----------- ------- ---------- ------- ----------------
| 18122210009| 许东山| 电子技术|  82.0|4.0|
| 18122210009| 许东山| C语言|  91.0|3.0|
| 18122221324| 何白露| 电子技术|  62.0|4.0|
| 18122221324| 何白露| C语言|  77.0|3.0|
| 18125111109| 敬横江| 经济法|  99.0|3.0|
| 18125111109| 敬横江| 金融学|  92.0|2.5|
| 18125111109| 敬横江| 会计软件|  92.0|2.0|
| 18125121107| 梁一苇| 电子技术|  91.0|4.0|
| 18125121107| 梁一苇| C语言|  62.0|3.0|
| 18135222201| 凌浩风| C语言|  92.0|3.0|
| 18135222201| 凌浩风| 会计软件|  82.0|2.0|
| 18137221508| 赵临江| 经济法|  95.0|3.0|
| 18137221508| 赵临江| 金融学|  89.0|2.5|
| 18137221508| 赵临江| 会计软件|  98.0|2.0|
----------- ------- ---------- ------- ----------------
14 rows in set (0.00 sec)
5.4.5合并多个结果集union操作符可以将多个select语句的返回结果组合到一个结果集中。当要检索的数据在不同的结果集中，并且不能够利用一个单独的查询语句得到时，可以使用union合并多个结果集。将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。union运算不同于使用连接合并两个表中的列的运算。使用union合并两个查询结果集时，所有查询中的列数和列的顺序必须相同且数据类型必须兼容。union操作符基本语法格式如下： 
select_statement union ［all］ select_statement
其中，格式中的参数说明如下： (1) select_statement： select语句。(2) union： 指定组合多个结果集并返回为单个结果集。(3) all： 将所有行合并到结果中，包括重复的行。如果不指定，将删除重复的行。【例528】在mysqltest数据库中利用student表创建student01，将student01和student表的部分查询结果集合并。分析： 虽然两个表的结构不同，但需要合并的两个结果集结构和列的数据类型兼容。代码和运行结果如下： 
mysql> createtable student01 as 
-> select studentno,sname,phone from teaching.student;
Query OK, 12 rows affected (0.43 sec)
Records: 12  Duplicates: 0  Warnings: 0
mysql> select studentno,sname,phone from student01 
-> where phone like \%131%
-> union
-> select studentno,sname,phone from teaching.student 
-> where phone like \%132%;
---------- ------- -----------
| studentno| sname|  phone|
---------- ------- -----------
| 18122221324| 何白露| 13178978999|
| 18125121107| 梁一苇| 13145678921|
| 19123567897| 赵既白| 13175689345|
| 19122203567| 封月明| 13245674564|
| 19126113307| 梅惟江| 13245678543|
---------- ------- -----------
5 rows in set (0.02 sec)
5.5子查询子查询就是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。部分子查询和连接可以相互替代，使用子查询也可以替代表达式。通过子查询可以把一个复杂的查询分解成一系列的逻辑步骤，利用单个语句的组合解决复杂的查询问题。(1) 子查询的执行过程。MySQL对嵌套查询的处理过程是从内层向外层处理，即先处理最内层的子查询，然后把查询的结果用于其外查询的查询条件，再层层向外求解，最后得出查询结果。(2) 子查询和连接的关系。一般情况下，包含子查询的查询语句可以写成连接查询的方式。因此，通过子查询也可以实现多表之间的查询。在有些方面，多表连接的性能要优于子查询，原因是连接不需要查询优化器执行排序等额外的操作。(3) 子查询中的常见运算。子查询中可以包括in、not in、any、all、exists、not exists等逻辑运算符，也可以包含比较运算符，如“=”“!=”“>”和“<”等。(4) 子查询的类型。根据子查询的结果又可以将MySQL子查询分为4种类型。 返回一个表的子查询是表子查询。 返回带有一个或多个值的一行的子查询是行子查询。 返回一行或多行，但每行上只有一个值的是列子查询。 只返回一个值的是标量子查询。从定义上讲，每个标量子查询都是一个列子查询和行子查询。(5) 使用子查询时应该注意如下的事项：  子查询需要用括号括起来。子查询中也可以再包含子查询，嵌套可以多至32层。 当需要返回一个值或一个值列表时，可以利用子查询代替一个表达式。也可以利用子查询返回含有多个列的结果集替代表或连接操作相同的功能。 子查询不能够检索数据类型为varchar(max)、nvarchar(max)和varbinary(max)的列。 子查询使用order by时，只能在外层使用，不能在内层使用。5.5.1利用子查询做表达式

select子句中的子查询

在MySQL语句中，可以把子查询的结果当成一个普通的表达式来看待，用在其外查询的选择条件中。此时子查询必须返回一个值或单个列值列表，此时的子查询可以替换where子句中包含in关键字的表达式。【例529】查询学号为18125121107的学生的入学成绩、所有学生的平均入学成绩及该学生成绩与所有学生的平均入学成绩的差。分析： 利用子查询求学生的平均入学成绩，作为select语句的输出项表达式。代码和运行结果如下： 
mysql> select studentno,sname,entrance ,
-> (select avg(entrance) from student ) 平均成绩,
-> entrance -(select avg(entrance) from student ) 分差
-> from student
-> where studentno=18125121107; 
------------ ------- --------- --------- ---------
| studentno| sname| entrance| 平均成绩| 分差|
------------ ------- --------- --------- ---------
| 18125121107| 梁一苇|  777| 807.0833| -30.0833|
------------ ------- --------- --------- ---------
1 row in set (0.04 sec)
5.5.2利用子查询生成派生表

from子句中的子查询
select的数据源由from子句指定，from子句可以指定单个表或者多个表，还可以查询来自视图、临时表或结果集的数据源。即可以利用子查询生成一个派生表，用于替代from子句中的数据源表，派生表可以定义一个别名，即子查询的结果集可以作为外层查询的源表。实际上是在from子句中使用子查询作为派生表数据源。【例530】查询期末成绩高于85分、总评成绩高于90分的学生的学号、课程号和总评成绩。分析： 利用子查询过滤出期末成绩高于85分的结果集，以TT命名，然后再对结果集TT中的数据进行查询。代码和运行结果如下： 
mysql> select TT.studentno 学号 ,TT.courseno 课程号 ,
->TT.final*0.8 TT.daily*0.2  总评
-> from  (select *  from score  where final>85) as TT
-> where TT.final*0.8 TT.daily*0.2>90;
----------- --------- -----
|学号|课程号| 总评|
----------- --------- -----
| 18125111109| c08106| 95.0|
| 18125111109| c08123| 90.6|
| 18135222201| c05109| 93.4|
| 18137221508| c08106| 92.0|
| 18137221508| c08171| 96.0|
| 19112100072| c06108| 97.0|
| 19112111208| c06108| 93.8|
| 19122203567| c05103| 91.4|
| 19123567897| c06127| 99.0|
----------- --------- -----
9 rows in set (0.00 sec)
5.5.3where子句中的子查询

where子句中的子查询
where语句中的子查询实际上是将子查询的结果作为该语句条件中的一部分，然后利用这个条件过滤本层查询的数据。1. 带比较运算符的子查询子查询可以作为动态表达式，该表达式可以随着外层查询的每一行的变化而变化。即查询处理器为外部查询的每一行计算子查询的值，每次计算一行，而该子查询每次都会作为该行的一个表达式取值并返回到外层查询。使得动态执行的子查询与外部查询有一个非常有效的连接，从而将复杂的查询分解为多个简单而相互关联的查询。查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=等。比较运算符在子查询时使用得非常广泛。创建关联子查询时，外部查询有多少行，子查询就执行多少次。【例531】查询期末成绩比选修该课程平均期末成绩低的学生的学号、课程号和期末成绩。分析： 在本例中，对score表采用别名形式，一个表就相当于两个表。子查询执行时使用的a.courseno相当于一个常量。在别名为b的表中根据分组计算平均分。然后与外层查询的值进行比较，该过程很费时间。代码和运行结果如下： 
mysql> select studentno,courseno,final
-> from score as a
-> where final < (select avg(final)
->from score as b
->where a.courseno=b.courseno
->group by  courseno );
----------- ------- -----
| studentno| courseno| final|
----------- ------- -----
| 18122221324| c05103|  62.0|
| 18122221324| c05109|  77.0|
| 18125121107| c05109|  62.0|
| 18135222201| c08171|  82.0|
| 18137221508| c08106|  95.0|
| 18137221508| c08123|  89.0|
| 19111133071| c05103|  69.0|
| 19111133071| c05109|  82.0|
| 19122111208| c06127|  67.0|
| 19123567897| c05103|  77.0|
| 19126113307| c06108|  82.0|
| 19126113307| c08171|  79.0|
----------- ------- -----
12 rows in set (0.05 sec)
2. 带in关键字的子查询当子查询返回的结果列包含一个值时，比较运算符就符合查询要求。假如一个子查询返回的结果集是值的列表，这时比较运算符就可以用in运算符代替。in运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。not in的作用与in刚好相反。【例532】获取期末成绩中含有高于93分的学生的学号、姓名、电话和Email。分析： 利用操作符in可以允许指定一个表达式(或常量)集合，可以利用select语句的子查询输出表达式(或常量)集合。代码和运行结果如下： 
mysql> select studentno,sname,phone,Email
-> from student
-> where studentno in ( select studentno
->from score
->where final>93);
----------- ------- ----------- --------------
| studentno| sname| phone| Email|
----------- ------- ----------- --------------
| 18125111109| 敬横江|  15678945623| jing@sina.com|
| 18137221508| 赵临江| 12367823453| ping@163.com|
| 19112100072| 宿沧海|  12545678998| su12@163.com|
| 19112111208| 韩山川| 15878945612| han@163.com|
| 19122203567| 封月明| 13245674564| jiao@126.com|
| 19123567897| 赵既白| 13175689345| pingan@163.com|
----------- ------- ----------- --------------
6 rows in set (0.00 sec)
3. 带exists关键字的子查询使用exists关键字时，内层查询语句不返回查询的记录,而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值(true)，否则，将返回一个假值(false)。当返回的值为true时，外层查询语句将进行查询； 当返回的值为false时，外层查询语句不进行查询或者查询不出任何记录。not exists与exists的工作方式类似，即当not exists与exists刚好相反，使用not exists关键字时，当返回的值是true时，外层查询语句不执行查询； 当返回值是false时，外层查询语句将执行查询。【例533】查询student表中是否存在2001年12月12日以后出生的学生，如果存在，输出学生的学号、姓名、生日和电话。分析： 只要存在一行数据符合条件，则where条件就返回true，于是输出所有行。代码和运行结果如下： 
mysql> select studentno,sname,birthdate,phone
-> from student
-> where exists (
-> select *
-> from student
-> where birthdate < 2001-12-12);
------------- -------- ----------- -----------
| studentno| sname| birthdate| phone|
------------- -------- ----------- -----------
| 18122210009| 许东山| 1999-11-05| 13623456778|
| 18122221324| 何白露| 2000-12-04| 13178978999|
| 18125111109| 敬横江| 2000-03-01| 15678945623|
| 18125121107| 梁一苇| 1999-09-03| 13145678921|
| 18135222201| 凌浩风| 2001-10-06| 15978945645|
| 18137221508| 赵临江| 2000-02-13| 12367823453|
| 19111133071| 崔依歌| 2001-06-06| 15556845645|
| 19112100072| 宿沧海| 2002-02-04| 12545678998|
| 19112111208| 韩山川| 2001-02-14| 15878945612|
| 19122203567| 封月明| 2002-09-09| 13245674564|
| 19123567897| 赵既白| 2002-08-04| 13175689345|
| 19126113307| 梅惟江| 2003-09-07| 13245678543|
------------- -------- ----------- -----------
12 rows in set (0.03 sec)
4. 对比较运算进行限制的子查询

对比较运算进行限制的子查询

all、some和any运算都是对比较运算的进一步限制。all指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回true，否则返回false。some或any是同义词，表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回true，否则返回false。【例534】查找score表中所有比c05109课程期末成绩都高的学号、姓名、电话和期末成绩。分析： 本题输出项是学号、姓名、电话和期末成绩，分别存在于student表和score表，因此外层查询先做一个内连接。在此基础上，从外层查询数据源中找出每一个期末成绩final的值，让该值分别与子查询中的c05109课程的每一个值进行比较，当该外层final值比内层的每一个c05109课程成绩都高时，即为查询结果集中的一行记录。以此类推，即可得到本题的结果集。代码和运行结果如下： 
mysql> select student.studentno,sname, phone,final
-> from score inner join student
->   on score.studentno= student.studentno
-> where  final >all
-> (select final from score where courseno= c05109);
----------- ------- ---------- ------
| studentno| sname| phone| final|
----------- ------- ---------- ------
| 18125111109| 敬横江| 15678945623|  99.0|
| 18137221508| 赵临江| 12367823453|  95.0|
| 18137221508| 赵临江| 12367823453|  98.0|
| 19112100072| 宿沧海| 12545678998|  97.0|
| 19112111208| 韩山川| 15878945612|  95.0|
| 19122203567| 封月明| 13245674564|  98.0|
| 19123567897| 赵既白| 13175689345|  99.0|
----------- ------- ---------- ------
7 rows in set (0.03 sec)
如果将本段代码的all关键字换成any或some关键字，在进行数值比较时，外层查询数据源中每一个期末成绩final的值，只要比内层查询中的任何一个c05109课程成绩高，即为查询结果集中的一行记录。5.5.4利用子查询插入、更新与删除数据

利用子查询处理数据
利用子查询修改表数据，就是利用一个嵌套在insert、update或delete语句的子查询成批地添加、更新和删除表中的数据。1. 利用子查询插入记录insert语句中的select子查询可用于将一个或多个其他的表或视图的值添加到表中。使用select子查询可同时插入多行。【例535】将student表中2001年以后出生的学生记录添加到student02表中。分析： 子查询的选择列表必须与insert语句列的列表匹配。如果insert语句没有指定列的列表，则选择列表必须与正向其插入的表或视图的列匹配且顺序一致。代码和运行结果如下： 
mysql> insert into  mysqltest.student02
-> (select  * from student
-> where birthdate>=2001-12-31);
Query OK, 4 rows affected (0.49 sec)
Records: 4  Duplicates: 0  Warnings: 0
2. 利用子查询更新数据update语句中的select子查询可用于将一个或多个其他的表或视图的值进行更新。使用select子查询可同时更新多行数据。实际上是通过将子查询的结果作为更新条件表达式中的一部分。【例536】将student表中入学成绩低于800分的所有学生的期末成绩增加5%。分析： 利用update成批修改表数据，可以在where子句中利用子查询实现。代码和运行结果如下： 
mysql> update  score
-> set final= final*1.05
-> where studentno in
-> (select studentno
-> from  student
-> where entrance <800);
Query OK, 18 rows affected (0.03 sec)
Rows matched: 18  Changed: 18  Warnings: 0
同样在delete语句中利用子查询可以删除符合条件的数据行。实际上是通过将子查询的结果作为删除条件表达式中的一部分。 5.6使用正则表达式进行模糊查询

正则表达式
正则表达式通常用来检索或替换符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些词语等。正则表达式强大而且灵活，可以应用于非常复杂的查询。本节将详细讲解如何使用正则表达式来查询。正则表达式的查询能力比通配字符的查询能力更强大，而且更加灵活。正则表达式可以应用于非常复杂的查询。MySQL中，使用regexp关键字来匹配查询正则表达式。正则表达式的基本语法格式如下: 
where  字段名  regexp  操作符 
MySQL中使用regexp操作符指定正则表达式的字符匹配模式，regexp操作符中常用的字符匹配选项如表51所示。

表51正则表达式中常用的字符匹配选项

选项说明示例
^匹配文本的开始字符^b： 匹配以字母b为开头的字符串，如big $匹配文本的结束字符st$： 匹配以st结尾的字符串，如test.匹配任何单个字符b. t： 匹配任何b和t之间有一个字符，如bit*匹配零个或多个在它前面的字符*n： 匹配字符n前面有任意个字符，如fn  匹配前面的字符1次或多次ba ： 匹配以b开头后面紧跟至少有一个a，如bay、bare、battle <字符串>匹配包含指定的字符串的文本fa： 字符串至少要包含fa，如fan ［字符集合］匹配字符集合中的任何一个字符［xz］： 匹配x或z，如dizzy ［^］匹配不在括号中的任何字符［^abc］： 匹配任何不包含a、b或c的字符串字符串{n,}匹配前面的字符串至少n次b{2,}： 匹配两个或更多的b，如bb、bbb 字符串{m,n}匹配前面的字符串至少m次，至多n次。如果n为0，m为可选参数b{2,4}： 匹配至少2个b，最多4个b，如bb、bbbb、bbb
1. 查询以特定字符或字符串开头的记录使用字符“^”可以匹配以特定字符或字符串开头的记录。【例537】查询student表中姓“赵”的学生的部分信息。代码和运行结果如下： 
mysql> select studentno,sname,birthdate, phone 
->  from student
->  where sname  regexp ^赵;
----------- ------ ----------- ------------
| studentno| sname| birthdate| phone| 
----------- ------ ----------- ------------
| 18137221508| 赵临江| 2000-02-13| 12367823453| 
| 19123567897| 赵既白| 2002-08-04| 13175689345| 
----------- ------ ----------- ------------
2 rows in set (0.00 sec)
2. 查询以特定字符或字符串结尾的记录使用字符“$”可以匹配以特定字符或字符串结尾的记录。【例538】查询student表中学生电话号码尾数为5的学生部分信息。代码和运行结果如下： 
mysql> select  studentno, sname, phone, Email
-> from student
-> where phone regexp 5$;
----------- -------- ------------ -------------
| studentno| sname| phone| Email|
----------- -------- ------------ -------------
| 18135222201| 凌浩风| 15978945645| tang@163.com|
| 19111133071| 崔依歌| 15556845645| cui@126.com|
| 19123567897| 赵既白| 13175689345| pingan@163.com|
----------- -------- ------------ -------------
3 rows in set (0.00 sec)
3. 用符号“.”来替代字符串中的任意一个字符用正则表达式来查询时，可以用“.”来替代字符串中的任意一个字符。【例539】要实现查询学生姓名sname字段中以“赵”开头，以“江”结束，中间包含两个字符的学生信息，可以通过正则表达式查询来实现，其中正则表达式中“^”表示字符串的开始位置，$表示字符串的结束位置，.表示除“＼n”以外的任何单个字符(此例中汉字按两个字符计算)。代码和运行结果如下： 
mysql> select studentno, sname, phone 
-> from student 
-> where  sname regexp ^赵..江$;
----------- ------ -----------
| studentno| sname| phone|
----------- ------ -----------
| 18137221508| 赵临江| 12367823453|
----------- ------ -----------
1 row in set (0.00 sec)
4. 匹配指定字符串正则表达式可以匹配字符串。当表中的记录包含这个字符串时，就可以将该记录查询出来。如果指定多个字符串，需要用符号“|”隔开。只要匹配这些字符串中的任意一个即可。【例540】查询学生电话号码出现131或132数字的学生信息。代码和运行结果如下： 
mysql> select  studentno, sname, phone, Email
->  from student
->  where phone regexp 131|132;
----------- ------- ----------- -------------
| studentno| sname| phone| Email|
----------- ------- ----------- -------------
| 18122221324| 何白露| 13178978999| heyy@sina.com|
| 18125121107| 梁一苇| 13145678921| bing@126.com|
| 19122203567| 封月明| 13245674564| jiao@126.com|
| 19123567897| 赵既白| 13175689345| pingan@163.com|
| 19126113307| 梅惟江| 13245678543| zhu@163.com|
----------- ------- ----------- -------------
5 rows in set (0.00 sec)
由于MySQL中不同字符集的影响，在使用正则表达式时需要多练习，多上机实验才能更好地掌握，并实现举一反三的学习效果。5.7小结本章介绍了MySQL数据库常见的查询方法。利用选择、投影和连接理论知识，实现对指定字段、指定记录和使用like关键字及通配符的查询，以及使用and和or来实现多条件查询、分组查询、连接查询、子查询等。分组查询经常和聚合函数一起使用，而且使用方法非常灵活。使用limit关键字来限制查询结果的条数是MySQL数据库的特色。本章的难点是使用正则表达式来查询。正则表达式的功能很强大，使用起来很灵活。具体需要掌握的主要内容如下：  select语句的一般格式及各个子句的作用。 在where子句中使用like、in、between关键字时的各种操作。 select语句中利用聚合函数实现计算和统计操作。 连接查询的格式、分类和应用。 select语句中使用子查询的技巧。 学习使用正则表达式进行查询。习题51. 选择题
(1) select语句中使用关键字可以将重复行屏蔽。
A.  order byB. havingC. topD. distinct(2) 在select语句中，可以使用子句，将结果集中的数据行根据选择列的值进行逻辑分组，以便能汇总表内容的子集，即实现对每个组的聚集计算。A.   limitB. group byC. whereD. order by(3) 使用空值查询时，表示一个列RR不是空值的表达式是。A.  RR is nullB. RR = nullC. RR <> nullD. RR is not null (4) select*fromcitylimit 5,10描述正确的是。A.  获取第6条到第10条记录B. 获取第5条到第10条记录C.  获取第6条到第15条记录D. 获取第5条到第15条记录(5)  select语句中用于实现关系的选择运算的短语是。A.   forB.  whileC.  whereD.  condition(6) 关于select语句以下哪一个描述是错误的？A.   select语句用于查询一个表或多个表的数据B.   select语句属于数据操作语言(DML)C.   select语句的输出列必须是基于表的列D.   select语句表示数据库中一组特定的数据记录(7)  select语句的执行过程是从数据库中选取匹配的特定记录和字段，并将这些数据组织成一个结果集，然后以的形式返回。A.  结构体数组B. 系统表C. 永久表D. 临时表(8) 现有订单表orders，包含用户信息userid,产品信息productid，以下语句能够返回至少被订购过两回的productid。A.   select productid from orders where count(productid)>1B.   select productid from orders where max(productid)>1C.   select productid from orders where having count(productid)>1 group by productidD.   select productid from orders group by productid having count(productid)>12. 思考题(1) 简述select语句的各个子句的作用。(2) MySQL中通配符与正则表达式的区别是什么？(3) 说明在select语句中使用聚合函数应该注意的问题。(4) 将null与其他值比较会产生什么结果？数值列中存在null会产生什么结果?(5) 简述连接查询和利用union语句合并结果集的应用区别。(6) 什么情况下使用limit来限制查询结果的数量？3. 上机练习题(本题利用teaching数据库进行操作)(1) 查询course表中的所有记录。(2) 查询student表中女生的人数。(3) 查询teacher表中每一位教授的教师号、姓名和专业名称。(4) 按性别分组，求出student表中每组学生的平均年龄。(5) 创建新表，新表中包括学号、学生姓名、课程号和总评成绩。其中,总评成绩=final*0.8 daily*0.2 。(6) 统计每个学生的期末成绩平均分。(7) 输出student表中年龄最大的男生的所有信息。(8) 查询teacher表中没有职称的职工的教师号、姓名、专业和部门。

MySQL数据库应用与开发 pdf下载声明

本pdf资料下载仅供个人学习和研究使用，不能用于商业用途，请在下载后24小时内删除。如果喜欢，请购买正版

pdf下载地址

版权归出版社和作者所有，下载链接已删除。如果喜欢，请购买正版！

链接地址：MySQL数据库应用与开发

标签：

相关推荐

随机推荐

热门标签

图像 多媒体 (3653)程序设计 (788)mysql (15)redis (6)分布式 (4)kafka (2)红包 (2)缓存 (1)事务 (1)elasticsearch (1)postgresql (1)MongoDB (1)TCP/IP (1)高并发 (1)无广告免费影视 (1)免费HTTP代理 (1)