MySQL数据库教程(微课版第2版) 课件全套 郑阿奇 第1-9章 MySQL初步- MySQL事务管理

第1章MySQL初步——数据库和数据模型MySQL教程（第2版）01数据库系统1．数据库2．数据库管理系统数据库系统数据库系统一般由数据库、数据库管理系统（DBMS）、应用系统、数据库管理员和用户构成。DBMS是数据库系统的基础和核心。如图。数据库系统1．数据库数据库是按照数据结构来组织、存储和管理数据的仓库。是一个可长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。2．数据库管理系统数据库管理系统（DBMS）是数据库系统的核心组成部分，主要完成对数据库的操作与管理功能，实现数据库对象的创建、数据库存储数据的查询、添加、修改与删除操作和数据库的用户管理、权限管理等。02数据模型1.层次模型2.网状模型3.关系模型数据模型1.层次模型将数据组织成一对多关系的结构，用树形结构表示实体及实体间的联系

。如图为按层次模型组织的数据示例。数据模型2.网状模型用连接指令或指针来确定数据间的网状连接关系，是具有多对多类型的数据组织方式。如图为按网状模型组织的数据示例。数据模型3.关系模型以记录组或数据表的形式组织数据，以便于利用各种实体与属性之间的关系进行存储和变换，不分层也无指针，是建立空间数据和属性数据之间关系的一种非常有效的数据组织方法

。例如，网上商城管理系统所涉及的商品类别、商品、供货商、用户、订单、订单项等表中，其中商品表主要信息包括商品编号、商品名称、价格、库存量和商品图片等，部分数据如表。商品编号商品名称价格库存量1A0101洛川红富士苹果冰糖心10斤箱装44.8036011A0201烟台红富士苹果10斤箱装29.8056981A0302阿克苏苹果冰糖心5斤箱装29.80126801B0501库尔勒香梨10斤箱装69.8089021B0601砀山梨10斤箱装大果19.90145321B0602砀山梨5斤箱装特大果16.9068341GA101智利车厘子2斤大樱桃整箱顺丰包邮59.8054202A1602[王明公]农家散养猪冷冻五花肉3斤装118.003752B1701Tyson/泰森鸡胸肉454g*5去皮冷冻包邮139.0016822B1702[周黑鸭]卤鸭脖15g*50袋99.0059633BA301波士顿龙虾特大鲜活1斤149.0028003C2205[参王朝]大连6-7年深海野生干海参1188.0012034A1601农家散养草鸡蛋40枚包邮33.906904C2402青岛啤酒500ml*24听整箱112.0023427数据模型随着数据库应用领域的进一步拓展与深入，对象数据、空间数据、图像与图形数据、声音数据、关联文本数据及海量仓库数据等出现，为了适应应用需要，数据模型向下列几个发展。对传统关系模型的扩充，以实现关系模型嵌套，支持关系继承及关系函数等。（1）（2）XML从数据交换领域发展到了数据存储与业务描述领域，数据库系统都已支持对XML的存储与处理。（3）研究新的数据模型，在数据构造器与数据处理原语上都有了新的突破。（4）用面向对象的思维方式与方法来描述客观实体，支持面向对象建模，支持对象存取与持久化，支持代码级面向对象数据操作，成为面向对象数据模型。第1章MySQL初步——数据库设计01概念模型1．一对一的联系（1∶1）2．一对多的联系（1∶n）3．多对多的联系（m∶n）概念模型概念模型用于信息世界的建模，最常用的是E-R模型、扩充的E-R模型、面向对象模型及谓词模型。通常，E-R模型把每一类数据对象的个体称为“实体”，而每一类对象个体的集合称为“实体集”，例如，在网上商城管理系统中主要涉及“商品”、“供货商”和“用户”等多个实体集。如果用矩形框表示实体集，用带椭圆框表示属性，用线段连接实体集与属性，当一个属性或属性组合指定为主码时，在实体集与属性的连接线上标记一斜线，则可以用如图的形式描述网上商城管理系统中的实体集及每个实体集涉及的属性。概念模型1．一对一的联系（1∶1）A中的一个实体至多与B中的一个实体相联系，B中的一个实体也至多与A中的一个实体相联系。例如，“用户”与“收件人”这两个实体集之间的联系是一对一的联系，因为一个用户对应一个收件人，反过来，一个收件人对应一个用户，“用户”与“收件人”两个实体集的E-R模型如图。2．一对多的联系（1∶n）A中的一个实体可以与B中的多个实体相联系，而B中的一个实体至多与A中的一个实体相联系。例如，“供货商”与“商品”这两个实体集之间的联系是一对多的联系，因为一个供货商户可提供若干商品，反过来，一个特定商品只能属于一个供货商。“供货商”与“商品”两个实体集的E-R模型如图。概念模型3．多对多的联系（m∶n）A中的一个实体可以与B中的多个实体相联系，而B中的一个实体也可与A中的多个实体相联系。例如，“用户”与“商品”这两个实体集之间的联系是多对多的联系，因为一个用户可购买多个商品，反过来，一个商品可被多个用户购买。“用户”与“商品”两个实体集的E-R模型如图。概念模型实际应用中，用户订货产生订单，在订单中下单对应商品，并确定数量。如图。02逻辑模型1．（1∶1）联系的E-R图到关系模式的转换2．（1∶n）联系的E-R图到关系模式的转换3．（m∶n）联系的E-R图到关系模式的转换逻辑模型1．（1∶1）联系的E-R图到关系模式的转换对于（1∶1）的联系，既可单独对应一个关系模式，也可以不单独对应一个关系模式。联系单独对应一个关系模式，则由联系属性、参与联系的各实体集的主码属性构成关系模式，联系不单独对应一个关系模式，联系的属性及一方的主码加入另一方实体集对应的关系模式中。其主码可选参与联系的实体集的任一方的主码。2．（1∶n）联系的E-R图到关系模式的转换对于（1∶n）的联系，既可单独对应一个关系模式，也可以不单独对应一个关系模式。联系单独对应一个关系模式，则由联系的属性、参与联系的各实体集的主码属性构成关系模式，n端的主码作为该关系模式的主码。联系不单独对应一个关系模式，则将联系的属性及1端的主码加入n端实体集对应的关系模式中，主码仍为n端的主码。3．（m∶n）联系的E-R图到关系模式的转换对于（m∶n）的联系，单独对应一个关系模式，该关系模式包括联系的属性、参与联系的各实体集的主码属性，该关系模式的主码由各实体集的主码属性共同组成。03物理模型物理模型物理模型是面向计算机物理表示的模型，描述了数据在储存介质上的组织结构，它不但与具体的DBMS有关，而且还与操作系统和硬件有关。每一种逻辑数据模型在实现时都有其对应的物理数据模型。DBMS为了保证其独立性与可移植性，大部分物理数据模型的实现工作由系统自动完成，而设计者只设计索引、聚集等特殊结构。第1章MySQL初步——数据库应用系统01数据库应用系统结构1.B/S架构的应用系统2.C/S架构的应用系统3.移动客户端APP数据库应用系统结构1.B/S架构的应用系统基于Web的数据库应用采用三层（浏览器/Web服务器/数据库服务器）模式，也称B/S架构，如图。2.C/S架构的应用系统C/S架构的应用系统要求客户端上安装应用程序。应用程序与数据库、数据库管理系统之间的关系如图。3.移动客户端APP移动客户端APP应用非常流行，但从结构上仍然可认为是C/S结构。普通的C/S结构的数据库应用程序安装在PC机上，而移动客户端APP安装在移动端（手机）上。02应用系统的数据接口1．ODBC数据库接口2．ADO.NET数据库接口3．JDBC数据库接口4.WebService应用系统的数据接口1．ODBC数据库接口ODBC即开放式数据库互连（OpenDataBaseConnectivity），是微软公司推出的一种实现应用程序和关系数据库之间通信的接口标准。符合该标准的数据库就可以通过SQL语句编写的程序对数据库进行操作，但只针对关系数据库。ODBC本质上是一组数据库访问API（应用程序编程接口），由一组函数调用组成，核心是SQL语句。2．ADO.NET数据库接口ADO.NET提供了面向对象的数据库视图，并且在其对象中封装了许多数据库属性和关系。最重要的是，它通过多种方式封装和隐藏了很多数据库访问的细节。可以完全不知道对象在与ADO.NET对象交互，也不用担心数据移动到另一个数据库或者从另一个数据库获得数据等细节问题。如图显示了通过ADO.NET访问数据库的接口模型。应用系统的数据接口3．JDBC数据库接口通过JDBC对数据库的访问包括4个主要组件：Java应用程序、JDBC驱动器管理器、驱动器和数据源。在JDBCAPI中有两层接口：应用程序层和驱动程序层。前者使开发人员可以通过SQL调用数据库和取得结果，后者处理与具体数据库驱动程序的所有通信。使用JDBC接口操作数据库有如下优点：（1）JDBCAPI与ODBC十分相似，有利于用户理解。使编程人员从复杂的驱动器调用命令和函数中解脱出来，而致力于应用程序功能的实现。JDBC支持不同的关系数据库，增强了程序的可移植性。（2）（3）应用系统的数据接口4.WebServiceWebService能使得运行在不同机器上的不同应用无须借助附加的、专门的第三方软件或硬件，就可相互交换数据或集成。它是自描述、自包含的可用网络模块，并可以执行具体的业务功能。WebService也很容易部署，为整个企业甚至多个组织之间的业务流程的集成提供了一个通用机制。第1章MySQL初步——MySQL数据库01MySQL安装运行1.MySQL安装2.MySQL运行MySQL安装运行1.MySQL安装目前常用的MySQL版本为MySQL5.6、MySQL5.7和MySQL8.0。本书介绍的MySQL基本内容均适用于上述版本。可通过MySQL官方网站上免费下载MySQL的安装包，在安装MySQL前，请确保系统中安装了相应版本的Microsoft.NETFramework。初学者可按照默认选项进行安装，系统会根据设备配置情况自动安装。MySQL安装运行2.MySQL运行（1）启动MySQL服务。安装配置完成后，打开Windows任务管理器，可以看到MySQL服务进程mysqld.exe已经启动，如图。MySQL安装运行（2）登录MySQL数据库。进入Windows命令行，输入：C:\...>cdMySQL安装目录\bin进入MySQL可执行程序目录，再输入：MySQL安装目录\bin>mysql-uroot-p按“Enter”键后，系统提示下列信息，输入root用户密码““”njnu123456：Enterpassword:njnu123456显示欢迎信息后出现下列提示符：mysql>表示进入MySQL的命令行模式，在命令行提示符“mysql>”后输入“quit”，可退出MySQL命令行回到操作系统状态。MySQL安装运行（3）设置MySQL中文字符集编码。查看当前连接系统的字符集参数：showvariableslike'char%';将数据库和服务器的字符集均设置为gbk（中文）：setcharacter_set_database='gbk';setcharacter_set_server='gbk';查看字符集设置后的结果：status;（4）为了在操作MySQL时防止由于不同操作系统默认的权限差异而不能使用某些功能，建议初学者使用下列命令设置操作权限：usemysql;grantallprivilegeson*.*to‘root’@’%’identifiedby‘njnu123456’withgrantoption;flushprivileges;02MySQL常用界面工具1．图形化客户端2．基于Web的管理工具MySQL常用界面工具1．图形化客户端图形化客户端这类工具采用C/S架构，用户通过安装在桌面计算机上的客户端软件连接并操作后台的MySQL数据库，原理如图，客户端是图形化用户界面（GUI）。除了MySQL官方提供的管理工具MySQLAdministrator和MySQLWorkbench，还有很多第三方开发的优秀工具，比较著名的有：Navicat、SequelPro、HeidiSQL、SQLMaestroMySQLToolsFamily、SQLWave、dbForgeStudio、DBToolsManager、MyDBStudio、AquaDataStudio、SQLyog、MYSQLFront和SQLBuddy等。MySQL常用界面工具2．基于Web的管理工具基于Web的管理工具采用B/S架构，用户计算机上无需再安装客户端，管理工具运行于Web服务器上，如图。用户设备只需安装浏览器，即可以访问Web页的方式操作MySQL数据库里的数据。第2章MySQL数据库和表——MySQL数据库MySQL教程（第2版）01创建数据库1.查看数据库2.创建数据库3.设置当前数据库创建数据库1.查看数据库查看MySQL系统的已有的数据库，输入命令：showdatabases;系统会列出已有的数据库。MySQL系统使用的数据库3个：information_schema、mysql和performance_schema，它们都是MySQL安装时系统自动创建的，MySQL把有关DBMS自身的管理信息都保存在这几个数据库中，如果删除了它们，MySQL将不能正常工作。2.创建数据库使用CREATEDATABASE或CREATESCHEMA命令可以创建数据库。创建数据库的语法格式如下：CREATE[IFNOTEXISTS]数据库名[DEFAULT]CHARACTERSET字符集|[DEFAULT]COLLATE校对规则名创建数据库【例2.1】创建学生成绩数据库，数据库名称xscj。mysql>createdatabasexscj如果已经创建了数据库（例如mytest），重复创建时系统会提示数据库已经存在，系统显示错误信息。使用IFNOTEXISTS选项从句可不显示错误信息，如图。3.设置当前数据库USE命令的语法格式如下：USE数据库名;例如：指定当前数据库为学生成绩数据库（xscj）。mysql>usexscj02修改数据库修改数据库创建数据库后，如果需要修改数据库的参数，可以使用ALTERDATABASE命令，其他格式与CREATEDATABASE相同。【例2.2】修改学生成绩数据库（xscj）默认字符集和校对规则。语句及结果如图。03删除数据库删除数据库用户已经创建的数据库需要删除，可以使用DROPDATABASE命令。语法格式如下：DROPDATABASE[IFEXISTS]数据库名这里，还可以使用IFEXISTS子句，避免删除不存在的数据库时出现MySQL错误信息。第2章MySQL数据库和表——MySQL表01创

建

表1.全新创建2.复制已有表创建创

建

表1．全新创建语法格式如下：CREATETABLE[IFNOTEXISTS]表名([列定义]...|[表索引定义])[表选项][select语句];创

建

表【例2.3】在学生成绩数据库（xscj）中也创建一个学生情况表，表名xs。（1）输入以下命令：usexscjcreatetablexs(

学号 char(6) notnullprimarykey,

姓名 char(8) notnull,

专业名 char(10) null,

性别 tinyint(1) notnulldefault1,

出生日期 date notnull,

总学分 tinyint(1) null,

照片 blob null,

备注 text null);创

建

表（2）用showtables命令显示xscj数据库中产生的学生（xs）表，用describexs命令可以显示xs表的结构，如图。创

建

表2．复制已有表创建如果创建的表与已有表相似，用户也可直接复制数据库中已有表的结构和数据，然后对表进行修改。语法格式如下：CREATETABLE[IFNOTEXISTS]表名[LIKE已有表名]|[AS(复制表记录)];【例2.4】在mytest数据库中，用复制的方式创建一个名为user_copy1的表，表结构直接取自user表；另再创建一个名为user_copy2的表，其结构和内容（数据）都取自user表。（1）用user表创建新表。USEmytestcreatetableuser_copy1likeuser;CREATETABLEuser_copy2AS(SELECT*FROMuser);创

建

表执行过程及结果，如图。创

建

表（2）查询发现user_copy1表中没有记录，而user_copy2表中包含user表中所有记录，如图。02修改

表1.修改表结构2.更改表名修

改

表1．修改表结构语法格式如下：ALTERTABLE表名 ADD列定义[FIRST|AFTER列名] MODIFY列定义 ALTER列名{SETDEFAULT值|DROPDEFAULT} CHANGE列名原列名 DROP列名 RENAME[TO]新表名【例2.5】在xscj数据库的xs表中，增加“奖学金等级”一列，并将表中的“姓名”列删除。USExscjALTERTABLExs ADD奖学金等级tinyintNULL, DROPCOLUMN姓名;修

改

表2．更改表名除了上面的ALTERTABLE命令，还可以直接用RENAMETABLE语句来更改表的名字。RENAMETABLE的语法格式如下：RENAMETABLE老表名TO新表名...【例2.6】将mytest数据库中的user_copy1表重命名为user1，user_copy2表重命名为user2，再将user2表更名为userb。RENAMETABLEuser_copy1TOuser1,user_copy2TOuser2;ALTERTABLEuser2RENAMETOuserb;03删除

表1.修改表结构2.更改表名删

除

表语法格式如下：DROPTABLE[IFEXISTS]表名...使用这个命令将表的描述、表的完整性约束、索引及和表相关的权限等一并删除。【例2.7】删除表uesrb。DROPTABLEIFEXISTSuserb;第2章MySQL数据库和表——表记录的操作01插入记录1.插入新记录2.已有表中插入新记录3.插入图片插入记录1．插入新记录语法格式如下：INSERT[INTO]表名 [(列名,...)]VALUES({expr|DEFAULT},...) |SET列名={EXPR|DEFAULT},...【例2.8】向学生成绩数据库（xscj）的表xs（表中列包括学号、姓名、专业名、性别、出生日期、总学分、照片、备注）中插入如下一行：221101，王林，计算机，1，2004-02-10，15，NULL，NULL使用下列语句：USExscjINSERTINTOxs VALUES('221101','王林','计算机',1,'2004-02-10',15,NULL,NULL);插入记录若表xs中专业列的默认值为“计算机”，照片、备注列的默认值为NULL，插入例中那行数据也可以使用如下命令：INSERTINTOxs(学号,姓名,性别,出生日期,总学分) VALUES('221101','王林',1,'2004-02-10',15);与下面这个命令的效果相同：INSERTINTOxs VALUES('221101','王林',default,1,'2004-02-10',15,NULL,NULL);当然，也可以使用SET子句来实现：INSERTINTOxs SET学号='221101',姓名='王林',专业名=default,性别=1,出生日期='2004-02-10',总学分=15;执行结果如图。插入记录2．已有表中插入新记录语法格式如下：INSERT[INTO]表名[(列名,...)]SELECT语句【例2.9】将mytest数据库中的user表记录插入user1表中。USEmytestINSERTINTOuser1SELECT*FROMuser;命令执行前后的效果如图。

插入记录3．插入图片MySQL还支持图片的插入，图片一般可以以路径的形式来存储。当然也可以直接插入图片本身，只要用LOAD_FILE()函数即可。【例2.10】向xs表中插入如下一行记录：221102，程明，计算机，1，2005-02-01，15，picture.jpg，NULL其中，图片路径为D:\IMAGE\picture.jpg，可使用如下语句：INSERTINTOxs VALUES('221102','程明','计算机',1,'2005-02-01',15,'D:\IMAGE\picture.jpg',NULL);也可使用这个语句直接存储图片本身：INSERTINTOxs VALUES('221102','程明','计算机',1,'2005-02-01',15,load_file('D:\IMAGE\picture.jpg'),NULL);执行结果如图。02修改记录1.替换旧记录2.修改单个表3.修改多个表修改记录1．替换旧记录使用REPLACE语句除了可以插入表中不存在的记录，同时可以替换已经存在的旧记录。REPLACE语句格式与INSERT的相同。【例2.11】若例2.10中的记录行已经被插入，其中学号为主键（PRIMARYKEY），现在想再插入下列一行记录：221101，刘华，通信工程，1，2004-06-10，13，NULL，NULL若直接使用INSERT语句，会产生图所示错误。使用REPLACE语句，则可以成功插入，如图。修改记录2．修改单个表语法格式如下：UPDATE[LOW_PRIORITY][IGNORE]表名 SET列名1=expr1[,列名2=expr2...] [WHERE条件]【例2.12】将学生成绩数据库（xscj）的学生（xs）表中的所有学生的总学分都增加10。将姓名为“刘华”的学生的备注填写为“辅修计算机专业”，学号改为“221201”。UPDATExs SET总学分=总学分+10;UPDATExs SET学号='221201',备注='辅修计算机专业’ WHERE姓名='刘华';SELECT学号,姓名,总学分,备注FROMxs;执行结果如图。修改记录3．修改多个表语法格式如下：UPDATE表名,表名... SET列名1=expr1[,列名2=expr2...] [WHERE条件]【例2.13】mytest数据库的表user和表userb中都有两列idint(11)、passwordchar(8)，其中id为主键。当表user中id值与userb中id值相同时，将表user中对应的password值修改为“11111111”，将表userb中对应的password值改为“22222222”。USEmytestUPDATEuser,userbSETuser.password='11111111',userb.password='22222222’ WHEREuser.id=userb.id;修改后的结果如图。

03删除记录1.从单个表中删除记录2.从多个表中删除记录3.清除表数据删除记录1．从单个表中删除记录语法格式如下：DELETEFROM表名[WHERE条件]【例2.14】删除mytest数据库userb表中的“周何骏”的记录。USEmytestDELETEFROMuserb WHEREusername='周何骏';或者DELETEFROMuserb WHEREid=2;删除记录2．从多个表中删除记录语法格式如下：DELETE[LOW_PRIORITY][QUICK][IGNORE]表名[.*][,表名[.*]...]

FROM参考表 [WHERE条件]或：DELETE[LOW_PRIORITY][QUICK][IGNORE] FROM表名[.*][,表名[.*]...] USING参考表 [WHERE条件]删除记录【例2.15】删除user1表中id值等于user表中id值的所有行和userb表中id值等于user表中id值的所有行，使用如下语句：DELETEuser1,userb FROMuser1,userb,user WHEREuser1.id=user.idANDuserb.id=user.id;命令执行结果如图。3．清除表数据语法格式如下：TRUNCATETABLE表名第3章MySQL查询和视图——MySQL数据查询MySQL教程（第2版）MySQL数据库查询使用SELECT语句可以从一个或多个表中选取符合某种条件的特定的行和列，结果通常是生成一个临时表。下面介绍SELECT语句，它是SQL的核心。SELECT语句的语法格式如下：SELECT [ALL|DISTINCT|DISTINCTROW]

列... [FROM表...] [WHERE条件] [GROUPBY{列名|表达式|position}[ASC|DESC],...] [HAVING条件] [ORDERBY{列名|表达式|position}[ASC|DESC],...]01选择输出列1.选择指定的列2.定义列别名3.替换查询结果中的数据4.计算列值5.消除结果集中的重复行6.聚合函数选择输出列1．选择指定的列使用SELECT语句选择一个表中的某些列，各列名之间要以逗号分隔，所有列用“*”表示。语法格式为如下：SELECT*|列名,列名,...FROM表名【例3.1】查询xscj数据库的xs表中各个学生的姓名、专业名和总学分。USExscj SELECT姓名,专业名,总学分 FROMxs;选择输出列2．定义列别名当希望查询结果中的列标题显示为自己选择的列标题，可以在列名之后使用AS子句。语法格式如下：SELECT...列名[AS列别名]【例3.2】查询xs表中计算机专业学生的学号、姓名和总学分，将结果中各列的标题分别指定为number、name和mark。SELECT学号ASnumber,姓名ASname,总学分ASmark FROMxs WHERE专业名='计算机';执行结果如图。选择输出列3．替换查询结果中的数据语法格式如下：CASE WHEN条件1THEN表达式1 WHEN条件2THEN表达式2 ... ELSE表达式NEND【例3.3】查询xs表中计算机专业各学生的学号、姓名和总学分，对总学分按如下规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于15，替换为“不及格”；若总学分为15～50，替换为“合格”；若总学分大于50，替换为“优秀”；将总学分列的标题更改为“等级”。选择输出列替换操作代码如下：SELECT学号,姓名,CASE WHEN总学分ISNULLTHEN'尚未选课’ WHEN总学分<15THEN'不及格’ WHEN总学分>=15AND总学分<=50THEN'合格’ ELSE'优秀'ENDAS等级FROMxsWHERE专业名='计算机';执行结果如图。选择输出列4．计算列值输出列可使用表达式表示。语法格式如下：SELECT表达式...【例3.4】按120分制重新计算成绩，显示cj表中学号为221101学生的成绩信息。SELECT学号,课程号,成绩*1.20AS成绩120 FROMcj WHERE学号='221101';执行结果如图。选择输出列5．消除结果集中的重复行对表只选择其某些列时，输出的结果可能会出现重复行。语法格式如下：SELECTDISTINCT|DISTINCTROW列名...【例3.5】对xs表只选择专业名列和总学分列，消除结果集中的重复行。SELECTDISTINCT专业名,总学分 FROMxs;执行结果如图。选择输出列6．聚合函数SELECT的输出列还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。除COUNT()函数外，聚合函数都会忽略空值。表3.1列出了一些常用的聚合函数。函

数

名说

明COUNT()统计记录数，返回int类型整数MAX()求最大值MIN()求最小值SUM()返回表达式中所有值的和AVG()求组中值的平均值STD()或STDDEV()返回给定表达式中所有值的标准差VARIANCE()返回给定表达式中所有值的方差GROUP_CONCAT()返回由属于一组的列值连接组合而成的结果BIT_AND()逻辑或BIT_OR()逻辑与BIT_XOR()逻辑异或选择输出列（1）COUNT()函数COUNT()函数用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。语法格式如下：COUNT({[ALL|DISTINCT]表达式}|*)其中，表达式的数据类型可以是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认值为ALL。使用COUNT(*)时将返回检索行的总数目，不论其是否包含NULL值。【例3.6】求学生的总数。SELECTCOUNT(*)AS'学生总数’ FROMxs;执行结果如图。选择输出列【例3.7】统计备注不为空的学生数目。SELECTCOUNT(备注)AS'备注不为空的学生数目’ FROMxs;执行结果如图。【例3.8】统计总学分在50分以上的人数。SELECTCOUNT(总学分)AS'总学分50分以上的人数’ FROMxs WHERE总学分>50;执行结果如图。选择输出列（2）MAX()函数和MIN()函数MAX()函数和MIN()函数分别用于求表达式中所有值项的最大值与最小值。语法格式如下：MAX/MIN([ALL|DISTINCT]表达式)【例3.9】求选修101课程的学生的最高分和最低分。SELECTMAX(成绩),MIN(成绩) FROMcj WHERE课程号='101';执行结果如图。选择输出列（3）SUM()函数和AVG()函数SUM()函数和AVG()函数分别用于求表达式中所有值项的总和与平均值。语法格式如下：SUM/AVG([ALL|DISTINCT]表达式)【例3.10】求学号为221101的学生所学课程的总成绩。SELECTSUM(成绩)AS'课程总成绩’ FROMcj WHERE学号='221101';执行结果如图。【例3.11】求选修101课程的学生的平均成绩。SELECTAVG(成绩)AS'课程101平均成绩’ FROMcj WHERE课程号='101';执行结果如图。选择输出列（4）VARIANCE()函数和STDDEV()函数VARIANCE()函数和STDDEV()函数分别用于计算特定的表达式中的所有值的方差和标准差。语法格式如下：VARIANCE/STDDEV([ALL|DISTINCT]表达式)【例3.12】求选修101课程的成绩的方差。SELECTVARIANCE(成绩) FROMcj WHERE课程号='101';执行结果如图。【例3.13】求选修101课程的成绩的标准差。SELECTSTDDEV(成绩) FROMcj WHERE课程号='101';执行结果如图。选择输出列（5）GROUP_CONCAT()函数MySQL支持一个特殊的聚合函数GROUP_CONCAT()。该函数返回来自一个组中指定列的所有非NULL值，这些值一个接着一个放置，中间用逗号隔开，并表示为一个长长的字符串。这个字符串的长度是有限制的，标准值是1024。语法格式如下：GROUP_CONCAT({[ALL|DISTINCT]表达式}|*)【例3.14】查询选修了206课程的学生的学号。SELECTGROUP_CONCAT(学号) FROMcj WHERE课程号='206';执行结果如图。选择输出列（6）BIT_AND()函数、BIT_OR()函数和BIT_XOR()函数。存在与二进制运算符&（与）、|（或）和^（异或）相对应的聚合函数，分别是BIT_AND、BIT_OR、BIT_XOR。语法格式如下：BIT_AND|BIT_OR|BIT_XOR({[ALL|DISTINCT]表达式}|*)【例3.15】有一个表bits，其中有一列bin_value上有3个integer值，即1、3、7，获取在该列上执行BIT_OR的结果。SELECTBIN(BIT_OR(bin_value)) FROMbits;02数据来源1.引用一个表2.多表连接数据来源1．引用一个表用户可以用如下两种方式引用表。第一种方式是使用USE语句让一个数据库成为当前数据库，FROM子句中指定表名应该属于当前数据库。第二种方式是在指定表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：SELECT*FROMdb2.tb;数据来源2．多表连接（1）全连接。将各个表用逗号分隔，就指定了一个全连接。使用FROM子句产生的中间结果是一个新表，新表是每个表的每行都与其他表中的每行交叉以产生的所有可能组合。这种连接方式会潜在地产生数量非常多的行，因为可能得到的行数为每个表行数之积！使用WHERE子句设定条件将结果集减小为易于管理的大小，这样的连接即等值连接（连接条件中两表对应）。【例3.16】查找所有学生选过的课程名和课程号。使用如下语句：SELECTDISTINCTkc.课程名,cj.课程号FROMkc,cjWHEREkc.课程号=cj.课程号;执行结果如图。数据来源（2）JOIN连接。语法格式如下：JOIN表ON连接条件使用JOIN关键字的连接主要分为如下3种。①内连接。指定了INNER关键字的连接是内连接。【例3.17】查找所有学生选过的课程名和课程号。可以使用以下语句：SELECTDISTINCT课程名,cj.课程号 FROMkcINNER JOINcjON(kc.课程号=cj.课程号);它的功能是合并两个表，返回满足条件的行。内连接是系统默认的，可以省略INNER关键字。数据来源【例3.18】查找选修了206课程且成绩为80分及以上的学生的姓名及成绩。SELECT姓名,成绩 FROMxsJOINcjONxs.学号=cj.学号 WHERE课程号='206'AND成绩>=80;执行结果如图。数据来源【例3.19】查找选修了“计算机导论”课程且成绩为80分及以上学生的学号、姓名、课程名及成绩。SELECTxs.学号,姓名,课程名,成绩 FROMxsJOINcjONxs.学号=cj.学号 JOINkcONcj.课程号=kc.课程号 WHERE课程名='计算机导论'AND成绩>=80;执行结果如图。数据来源【例3.20】查找课程不同、成绩相同的学生的学号、课程号和成绩。SELECTa.学号,a.课程号,b.课程号,a.成绩 FROMcjASa JOINcjASbONa.成绩=b.成绩ANDa.学号=b.学号ANDa.课程号!=b.课程号;执行结果如图。【例3.21】查找kc表中所有学生选过的课程名。SELECT课程名 FROMkcINNERJOINcjUSING(课程号);数据来源②外连接。指定了OUTER关键字的连接为外连接，其中的OUTER关键字均可省略。外连接包括以下。左外连接（LEFTOUTERJOIN）：结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，右表被选择的列输出为NULL。右外连接（RIGHTOUTERJOIN）：结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。自然连接（NATURALJOIN）：自然连接包括自然左外连接（NATURALLEFTOUTERJOIN）和自然右外连接（NATURALRIGHTOUTERJOIN）。【例3.22】查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要显示其情况。SELECTxs.*,课程号 FROMxsLEFTOUTERJOINcjONxs.学号=cj.学号;数据来源【例3.23】查找被选修了的课程的选修情况和所有开设的课程名。SELECTcj.*,课程名 FROMcjRIGHTJOINkcONcj.课程号=kc.课程号;结果显示如图。【例3.24】使用自然连接查询所有学生选过的课程名和课程号。SELECT课程名,课程号FROMkc WHERE课程号IN (SELECTDISTINCT课程号FROMkcNATURALRIGHTOUTERJOINcj);数据来源③交叉连接。指定了CROSSJOIN关键字的连接是交叉连接。在不包含连接条件时，交叉连接结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。在MySQL中，CROSSJOIN语法上与INNERJOIN等同，两者可以互换。【例3.25】列出学生所有可能的选课情况。SELECT学号,姓名,课程号,课程名 FROMxsCROSSJOINkc;另外，STRAIGHT_JOIN连接用法和INNERJOIN连接基本相同。不同的是，STRAIGHT_JOIN后不可以使用USING子句替代ON条件。【例3.26】使用STRAIGHT_JOIN连接查找所有学生选过的课程名和课程号。SELECTDISTINCT课程名,cj.课程号 FROMkcSTRAIGHT_JOINcjON(kc.课程号=cj.课程号);03查询条件1.比较运算2.模式匹配3.范围比较4.空值比较5.子查询查询条件WHERE子句的基本格式为：WHERE条件条件格式如下：表达式<比较运算符>表达式 /*比较运算*/|逻辑表达式<逻辑运算符>逻辑表达式|表达式[NOT]LIKE表达式[ESCAPE'ESC字符’] /*LIKE运算符*/|表达式[NOT][REGEXP|RLIKE]表达式 /*REGEXP运算符*/|表达式[NOT]BETWEEN表达式AND表达式 /*指定范围*/|表达式IS[NOT]NULL /*判断是否为空值*/|表达式[NOT]IN(子查询|表达式[,…n]) /*IN子句*/|表达式<比较运算符>{ALL|SOME|ANY}(子查询) /*比较子查询*/|EXIST(子查询) /*EXIST子查询*/使用WHERE子句会根据条件对FROM子句一行一行地进行判断，当条件为TRUE的时候，这一行就被包含到WHERE子句的中间结果中。查询条件1．比较运算比较运算用于比较两个表达式值，当两个表达式值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，将返回UNKNOWN。MySQL支持的比较运算符有=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<=>（相等或都等于空）、<>（不等于）、!=（不等于）等。【例3.27】查询xs表中学号为221101的学生的情况。SELECT姓名,学号,总学分 FROMxs WHERE学号='221101';执行结果如图。查询条件【例3.28】查询xs表中总学分大于50分的学生的情况。SELECT姓名,学号,出生日期,总学分 FROMxs WHERE总学分>50;执行结果如图。MySQL支持一个特殊的等于运算符“<=>”，当两个表达式值彼此相等或都等于空值时，它的返回值为TRUE，其中有一个空值或都是非空值但不相等时，返回值就是FALSE。其中没有UNKNOWN的情况。查询条件【例3.29】查询xs表中备注为空的学生的情况。SELECT姓名,学号,出生日期,总学分 FROMxs WHERE备注<=>NULL;可以通过逻辑运算符（AND、OR、XOR和NOT）组成更为复杂的查询条件。查询xs表中专业为计算机、性别为女（0）的学生的情况。SELECT姓名,学号,性别,总学分 FROMxs WHERE专业名='计算机'AND性别=0;执行结果如图。查询条件2．模式匹配（1）LIKE运算符。LIKE运算符用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值TRUE或FALSE。语法格式如下：表达式[NOT]LIKE表达式[ESCAPE'esc字符']使用LIKE进行模式匹配时，常使用特殊符号_和%，可进行模糊查询。“%”代表0个或多个字符，“_”代表单个字符。由于MySQL默认不区分大小写，要区分大小写时需要更换字符集的校对规则。【例3.30】查询xs表中姓“王”学生的学号、姓名及性别。SELECT学号,姓名,性别 FROMxs WHERE姓名LIKE'王%';执行结果如图。查询条件【例3.31】查询xs表中学号倒数第二个数字为0的学生的学号、姓名及专业名。SELECT学号,姓名,专业名 FROMxs WHERE学号LIKE'%0_';执行结果如图。【例3.32】查询xs表中名字包含下画线学生的学号和姓名。SELECT学号,姓名 FROMxs WHERE学号LIKE'%#_%'ESCAPE'#';查询条件（2）REGEXP运算符。REGEXP运算符用来执行更复杂的字符串比较运算。REGEXP是正规表达式的缩写，但它不是SQL标准的一部分。REGEXP运算符的一个同义词是RLIKE。语法格式如下：表达式[NOT][REGEXP|RLIKE]表达式属于REGEXP运算符的特殊字符如表。特殊字符含

义特殊字符含

义^匹配字符串的开始部分[abc]匹配方括号里出现的字符串abc$匹配字符串的结束部分[a-z]匹配方括号里出现的a～z之间的1个字符.匹配任何一个字符（包括回车和新行）[^a-z]匹配方括号里出现的不在a～z之间的1个字符*匹配星号之前的0个或多个字符的任何序列|匹配符号左边或右边出现的字符串+匹配加号之前的1个或多个字符的任何序列[[..]]匹配方括号里出现的符号（如空格、换行、括号、句号、冒号、加号、连字符等）?匹配问号之前0个或多个字符[[:<:]和[[:>:]]匹配一个单词的开始和结束{n}匹配括号前的内容出现n次的序列[[::]匹配方括号里出现的字符中的任意一个字符()匹配括号里的内容

查询条件【例3.33】查询姓李的学生的学号、姓名和专业名。SELECT学号,姓名,专业名 FROMxs WHERE姓名REGEXP'^李';执行结果如图。【例3.34】查询学号里包含4、5、6学生的学号、姓名和专业名。SELECT学号,姓名,专业名 FROMxs WHERE学号REGEXP'[4,5,6]';执行结果如图。查询条件【例3.35】查询学号以21开头、以02结尾的学生学号、姓名和专业名。SELECT学号,姓名,专业名 FROMxs WHERE学号REGEXP'^21.*02$';执行结果如图。查询条件3．范围比较用于范围比较的关键字有两个：BETWEEN和IN。当要查询的条件是某个值的范围时，可以使用BETWEEN关键字指出查询范围。语法格式如下：表达式[NOT]BETWEEN表达式1AND表达式2当不使用NOT时，若表达式的值在表达式1的值与表达式2的值之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。使用IN关键字可以指定一个值表，值表中列出所有可能的值，当表达式的值与值表中的任一个值匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：表达式IN(表达式[,…,n])查询条件【例3.36】查询xs表中不在2003年出生学生的情况。SELECT学号,姓名,专业名,出生日期 FROMxs WHERE出生日期NOTBETWEEN'2003-1-1'AND'2003-12-31';执行结果如图。查询条件【例3.37】查询xs表中专业名为“计算机”“通信工程”或“无线电”的学生的情况。SELECT* FROMxs WHERE专业名IN('计算机','通信工程','无线电');该语句与下句等价：SELECT* FROMxs WHERE专业名='计算机'OR专业名='通信工程'OR专业名='无线电';查询条件4．空值比较当需要判定一个表达式的值是否为空值时，使用ISNULL关键字。语法格式如下：表达式IS[NOT]NULL当不使用NOT时，若表达式的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例3.38】查询总学分尚不定学生的情况。SELECT* FROMxs WHERE总学分ISNULL;查询条件5．子查询（1）IN子查询。IN子查询用于进行给定值是否在子查询结果集中的判断。语法格式如下：表达式[NOT]IN(子查询)当表达式的值与子查询结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。【例3.39】查找选修了课程号为206课程的学生的姓名、学号。SELECT姓名,学号 FROMxs WHERE学号IN (SELECT学号 fromcj WHERE课程号='206’ );执行结果如图。查询条件【例3.40】查找未选修离散数学的学生的姓名、学号、专业名。SELECT姓名,学号,专业名 FROMxs WHERE学号NOTIN ( SELECT学号 FROMcj WHERE课程号IN (SELECT课程号 FROMkc WHERE课程名='离散数学’ ) );执行结果如图。查询条件（2）比较子查询。这种子查询可以被认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算。语法格式如下：表达式{<|<=|=|>|>=|!=|<>}{ALL|SOME|ANY}(子查询)其中：ALL用于指定表达式的值要与子查询结果集中的每个值都进行比较，当表达式的值与查询结果集中的每个值都满足比较的关系时，才返回TRUE，否则返回FALSE；SOME或ANY是同义词，表示表达式的值只要与子查询结果集中的某个值满足比较的关系时，就返回TRUE，否则返回FALSE；如果子查询的结果集只返回一行数据，可以通过比较运算符直接比较。查询条件【例3.41】查找选修了离散数学学生的学号。SELECT学号 FROMcj WHERE课程号= ( SELECT课程号 FROMkc WHERE课程名='离散数学’ );执行结果如图。查询条件【例3.42】查找xs表中比所有通信工程专业学生年龄都小的学生的学号、姓名、专业名、出生日期。SELECT学号,姓名,专业名,出生日期 FROMxs WHERE出生日期>ALL ( SELECT出生日期 FROMxs WHERE专业名='通信工程’ );执行结果如图。查询条件【例3.43】查找cj表中课程号为206课程的成绩不低于课程号为101课程的最低成绩的学生的学号。SELECT学号 FROMcj WHERE课程号='206'AND成绩>=ANY ( SELECT成绩 FROMcj WHERE课程号='101’ );执行结果如图。查询条件（3）EXISTS子查询。EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOTEXISTS，其返回值与EXIST的刚好相反。语法格式如下：[NOT]EXISTS(子查询)【例3.44】查找选修课程号为206学生的姓名。SELECT姓名 FROMxs WHEREEXISTS ( SELECT* FROMcj WHERE学号=xs.学号AND课程号='206’ );执行结果如图。查询条件这类子查询称为相关子查询，因为子查询的条件依赖于外层查询中的某些值。其处理过程：首先找外层SELECT中xs表的第一行，根据该行的学号列值处理内层SELECT，若结果不为空，则WHERE条件就为真，就把该行的姓名值取出作为结果集的一行；然后找xs表的第2、3等行，重复上述处理过程直到xs表的所有行都查找完为止。【例3.45】查找选修了全部课程学生的姓名。SELECT姓名 FROMxs WHERENOTEXISTS ( SELECT* FROMkc WHERENOTEXISTS ( SELECT* FROMcj WHERE学号=xs.学号AND课程号=kc.课程号 ) );查询条件MySQL区分了4种类型的子查询：①返回一个表的子查询是表子查询；②返回带有一个或多个值的一行的子查询是行子查询；③返回一行或多行，但每行上只有一个值的是列子查询；④只返回一个值的是标量子查询，从定义上讲，每个标量子查询都是一个列子查询和行子查询。上面介绍的子查询都属于列子查询。另外，子查询还可以用在SELECT语句的其他子句中。子查询可以用在FROM子句中，但必须为子查询产生的中间表定义一个别名。【例3.46】从xs表中查找总学分大于50分的男学生的姓名和学号。SELECT姓名,学号,总学分 FROM(SELECT姓名,学号,性别,总学分 FROMxs WHERE总学分>50 )ASstudent WHERE性别='1';执行结果如图。查询条件【例3.47】从xs表中查找所有女学生的姓名、学号，以及与221101号学生的年龄差距。SELECT学号,姓名,YEAR(出生日期)- YEAR((SELECT出生日期 FROMxs WHERE学号='221101’ ))AS年龄差距 FROMxs WHERE性别='0';执行结果如图。查询条件【例3.48】查找与221101号学生性别相同、总学分相同学生的学号和姓名。SELECT学号,姓名 FROMxs WHERE(性别,总学分)=(SELECT性别,总学分 FROMxs WHERE学号='221101’ );执行结果如图。04分

组分

组语法格式如下：GROUPBY{列名|表达式|列顺序}[ASC|DESC],...[WITHROLLUP]【例3.49】查询各专业名及对应的学生数。SELECT专业名,COUNT(*)AS'学生数’ FROMxs GROUPBY专业名;执行结果如图。分

组【例3.50】求被选修的各门课程的平均成绩和选修该课程学生的人数。SELECT课程号,AVG(成绩)AS'平均成绩',COUNT(学号)AS'选修人数’ FROMcj GROUPBY课程号;执行结果如图。分

组【例3.51】查询每个专业的男生人数、女生人数、总人数，以及学生总人数。SELECT专业名,性别,COUNT(*)AS'人数’ FROMxs GROUPBY专业名,性别 WITHROLLUP;执行结果如图。将上述语句与不带ROLLUP操作符的GROUPBY子句的执行情况进行比较：SELECT专业名,性别,COUNT(*)AS'人数’ FROMxs GROUPBY专业名,性别;执行结果如图。分

组【例3.52】在xscj数据库中产生一个结果集，包括每门课程各专业的平均成绩、每门课程的总平均成绩和所有课程的总平均成绩。SELECT课程名,专业名,AVG(成绩)AS'平均成绩’ FROMcj,kc,xs WHEREcj.课程号=kc.课程号ANDcj.学号=xs.学号 GROUPBY课程名,专业名 WITHROLLUP;执行结果如图。05分组条件分组条件HAVING子句的语法格式如下：HAVING条件其中，条件的定义和WHERE子句中条件的类似，不过HAVING子句中的条件可以包含聚合函数，而WHERE子句中的则不可以。SQL标准要求HAVING子句必须引用GROUPBY子句中的列或用于聚合函数中的列。MySQL允许HAVING子句引用SELECT清单中的列和外部子查询中的列。【例3.53】查找平均成绩为85分及以上的学生的学号和平均成绩。SELECT学号,AVG(成绩)AS'平均成绩’ FROMcj GROUPBY学号 HAVINGAVG(成绩)>=85;执行结果如图。分组条件【例3.54】查找选修课程超过2门且成绩都为80分及以上的学生的学号。SELECT学号 FROMcj WHERE成绩>=80 GROUPBY学号 HAVINGCOUNT(*)>2;执行结果如图。分组条件【例3.55】查找通信工程专业平均成绩为85分及以上的学生的学号和平均成绩。SELECT学号,AVG(成绩)AS'平均成绩’ FROMcj WHERE学号IN (SELECT学号 FROMxs WHERE专业名