《MySQL教程（第4版）》高职全套教学课件

《MySQL实用教程（第4版）》第1章

数据库基础第2章

数据类型第3章

数据库和表第4章

查询和视图第5章

索引与数据完整性第6章SQL扩展语言和系统函数第7章

过程式数据库对象第8章

数据库备份与恢复第9章

用户管理和权限控制第10章

事务管理和多用户操作全套可编辑PPT课件

第1章

数据库基础——数据库基本概念全套可编辑PPT课件

01数据库及其系统数据库数据库管理系统数据库系统数据库及其系统1．数据库数据库（Database,简称DB）是存放数据的仓库，而且这些数据存在一定的关联，并按一定的格式存放在计算机内。例如，把一个学校的学生、课程、成绩等数据有序地组织并存放在计算机内，就构成一个数据库。2．数据库管理系统数据库管理系统（DataBaseManagementSystem,简称DBMS）按一定的数据模型组织数据形成数据库，并对其进行管理。简单地说，DBMS就是管理数据库的系统（软件）。数据库管理员（DataBaseAdministrator，简称DBA）通过DBMS对数据库进行管理。数据库及其系统3．数据库系统数据、数据库、数据库管理系统与操作数据库的应用程序，加上支撑它们的硬件平台、软件平台和与数据库有关的人员一起构成一个完整的数据库系统。如图所示描述了数据库系统的构成。02数

据

模

型层次模型网状模型关系模型数据模型1．层次模型层次模型将数据组织成一对多关系的结构，采用关键字来访问其中每一层次的每一部分。它存取方便且速度快；结构清晰，容易理解；容易实现数据修改和数据库扩展；检索关键属性十分方便。但缺点是结构不够灵活；同一属性的数据要存储多次，冗余大；不适合拓扑空间数据的组织。如图所示为按层次模型组织的数据示例。数据模型2．网状模型网状模型是具有多对关系的数据组织方式。它能明确而方便地表示数据间的复杂关系；数据冗余小。但网状结构的复杂，增加了用户查询和定位的困难；需要存储数据间联系的指针，使得数据量增大；数据的修改不方便。如图所示为按网状模型组织的数据示例。数据模型3．关系模型关系模型以记录组或二维数据表的形式组织数据。例如，在学生成绩管理系统的3个表中，“学生”表涉及的主要信息有学号、姓名、性别、出生时间、专业、总学分和备注；“课程”表涉及的主要信息有课程号、课程名、开课学期、学时和学分；“成绩”表涉及的主要信息有学号、课程号和成绩。如表1、表2和表3所示分别描述了这3个表的部分数据。表1“学生”表学号姓名性别出生时间专业总学分备注081101王林男1994-02-10计算机50

081103王燕女1993-10-06计算机50

081108林一帆男1993-08-05计算机52已提前修完一门课程081202王林男1993-01-29通信工程40有一门课程不及格，待补考081204马琳琳女1993-02-10通信工程42

数据模型表2“课程”表课程号课程名开课学期学时学分0101计算机基础18050102程序设计与语言26840206离散数学4684表3“成绩”表学号课程号成绩学号课程号成绩08110110180081108101850811011027808110810264081101206760811082068708110310162081202101650811031027008120410191数据模型表中的一行称为一个记录，一列称为一个字段，每列的标题（列名）称为字段名。如果给每个关系表起一个名字，则有若干个字段的关系表可表示为如下形式：表名(字段名,...)通常把关系表的结构称为关系模式。在关系表中，如果一个字段或几个字段组合的值可唯一标识其对应的记录，则称该字段或字段组合为码。03关

系

数

据

库关系型数据库管理系统关系型数据库语言关系数据库1．关系型数据库管理系统从当前国际数据库管理系统（DataBaseManagementSystem，简称DBMS）排名中可以看出来，关系型数据库管理系统（RelationalDatabaseManagementSystem，简称RDBMS）绝对是DBMS的主流。RDBMS就是建立在关系模型基础上的数据库，其中使用最多的RDBMS分别是Oracle、MySQL和SQLServer。MySQL是目前流行的开放关系数据库管理系统之一。2．关系型数据库语言结构化查询语言（StructuredQueryLanguage，简称SQL）就是关系型数据库的查询语言，用于定义数据库及其对象，操作数据库对象数据和控制操作数据库的安全性等，所有提供数据库产品的厂商都支持SQL语言，但它们又各有其特点。为了在各个数据库厂商之间取得更大的统一性，美国国家标准学会（AmericanNationalStandardsInstitute,简称ANSI）于1986年发布了第一个SQL标准，并于1989年发布了第二个版本，该版本已经被广泛地采用。ANSI在1992年更新了SQL标准的版本，即SQL92和SQL2,并于1999年再次更新为SQL99和SQL3标准。第1章

数据库基础——数据库设计01概念模型一对一的联系（1∶1）一对多的联系（1∶n）多对多的联系（m∶n）概念模型概念模型用于信息世界的建模，常用的E-R模型、扩充的E-R模型、面向对象模型及谓词模型。通常，E-R模型把每一类数据对象的个体称为“实体”，把每一类对象个体的集合称为“实体集”。例如，在学生成绩管理系统中主要涉及“学生”和“课程”两个实体集，其他非主要的实体可以很多，如班级、班长、任课教师、辅导员。每个实体集涉及的信息项称为属性。就“学生”实体集而言，它的属性有学号、姓名、性别、出生时间、专业、总学分和备注。“课程”实体集属性有课程号、课程名、开课学期、学时和学分。实体集中的实体彼此是可区别的。如果实体集中的属性或最小属性组合的值能唯一标识其对应实体，则将该属性或属性组合称为码。码可能有多个，对于每一个实体集，可指定一个码为主码。实体集A和实体集B之间存在各种关系，通常把这些关系称为“联系”，将实体集及实体集联系的图示称为实体-联系（EntityRelationship，E-R）模型。概念模型E-R图就是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）联系的E-R图到关系模式的转换（1∶n）联系的E-R图到关系模式的转换（m∶n）联系的E-R图到关系模式的转换逻

辑

模

型1．（1∶1）联系的E-R图到关系模式的转换对于（1∶1）联系，既可以单独对应一个关系模式，也可以不单独对应一个关系模式。（1）联系单独对应一个关系模式，则由联系的属性、参与联系的各实体集的主码属性构成关系模式，其主码可选参与联系的实体集的任一方的主码。例如，考虑图描述的“班级（bj）”与“班长（bz）”实体集通过属于（sy）联系的E-R模型，可设计如下关系模式（下横线表示该字段为主码）：bj（班级编号，院系，专业，人数）bz（学号，姓名）sy（学号，班级编号）（2）联系不单独对应一个关系模式，联系的属性及一方的主码加入另一方实体集对应的关系模式中。例如，考虑图描述的“班级（bj）”与“班长（bz）”实体集通过属于（sy）联系的E-R模型，可设计如下关系模式：bj（班级编号，院系，专业，人数）bz（学号，姓名，班级编号）或者：bj（班级编号，院系，专业，人数，学号）bz（学号，姓名）逻

辑

模

型2．（1∶n）联系的E-R图到关系模式的转换对于（1∶n）的联系，既可以单独对应一个关系模式，也可以不单独对应一个关系模式。（1）联系单独对应一个关系模式，则由联系的属性、参与联系的各实体集的主码属性构成关系模式，n端的主码作为该关系模式的主码。例如，考虑图描述的“班级（bj）”与“学生（xs）”实体集E-R模型可设计如下关系模式：bj（班级编号，院系，专业，人数）xs（学号，姓名，性别，出生时间，专业，总学分，备注）sy（学号，班级编号）（2）联系不单独对应一个关系模式，则将联系的属性及1端的主码加入n端实体集对应的关系模式中，主码仍为n端的主码。例如，图描述的“班级（bj）”与“学生（xs）”实体集E-R模型可设计如下关系模式：bj（班级编号，院系，专业，人数）xs（学号，姓名，性别，出生时间，专业，总学分，备注，班级编号）逻

辑

模

型3．（m∶n）联系的E-R图到关系模式的转换对于（m∶n）联系，单独对应一个关系模式，该关系模式包括联系的属性、参与联系的各实体集的主码属性，该关系模式的主码由各实体集的主码属性共同组成。例如，图描述的“学生（xs）”与“课程（kc）”实体集之间的联系可设计如下关系模式：xs（学号，姓名，性别，出生时间，专业，总学分，备注）kc（课程号，课程名，开课学期，学时，学分）xs_kc（学号，课程号，成绩）关系模式xs_kc的主码是由“学号”和“课程号”两个属性组合起来构成的，一个关系模式只能有一个主码。03物

理

模

型物

理

模

型物理模型是面向计算机物理表示的模型，描述了数据在储存介质上的组织结构，它不但与具体的DBMS有关，而且与操作系统和硬件有关。每一种逻辑数据模型在实现时都有其对应的物理数据模型。DBMS为了保证其独立性与可移植性，大部分物理数据模型的实现工作由系统自动完成，而设计者只设计索引、聚集等特殊结构。第1章

数据库基础——MySQL安装、配置和运行01MySQL安装和配置MySQL安装和配置MySQL5.7可以通过安装包方式，也可以通过压缩包方式安装和配置。安装包和压缩包可从MySQL官网（/downloads）免费下载。在安装MySQL前，须确保系统中已安装了最新的Microsoft.NETFramework。02MySQL数据库试运行启动MySQL服务登录MySQL实例设置MySQL扩展中文字符集赋予用户root所有权限MySQL数据库试运行1．启动MySQL服务打开“Windows任务管理器”，在“进程”页可以看到MySQL进程mysqld.exe，在“服务”页可以看到对应的“MySQL57”服务已经启动了，如图。MySQL数据库试运行2．登录MySQL实例右击Windows开始按钮，选择“运行(R)”，在“运行”对话框中输入“cmd”后按“Enter”键，进入Windows命令行窗口。说明：Windows命令行窗口是默认黑色背景白字，可以设置窗口属性修改为白色背景黑字。在Windows命令行窗口中输入下列命令（加黑文本）改变当前目录到指定目录：C:\...>cdC:\ProgramFiles\MySQL\MySQLServer5.7\bin输入下列命令（加黑文本）连接MySQL实例，登录MySQL：C:\ProgramFiles\MySQL\MySQLServer5.7\bin>mysql-uroot-p回车后，在系统提示“Enterpassword:”处输入密码（安装MySQL时设置）：Enterpassword:123456MySQL数据库试运行显示如图所示的欢迎信息。显示提示符“mysql>”表示进入了MySQL的命令行模式。在“mysql>”下输入“quit”，可退出MySQL返回Windows命令行模式。MySQL数据库试运行3.设置MySQL扩展中文字符集为了让MySQL数据库能够支持中文，必须设置系统字符集编码。输入MySQL的SQL命令：SETCHARACTER_SET_DATABASE='gbk';SETCHARACTER_SET_SERVER='gbk';STATUS;将数据库和服务器的字符集均设为gbk（中文），系统显示修改后MySQL的状态信息，可查看当前系统字符集如图。MySQL数据库试运行4.赋予用户root所有权限操作MySQL时，由于不同操作系统默认的权限存在差异，有些功能无法使用，建议进行下列操作权限设置。mysql>USEmysql;GRANTALLPRIVILEGESON*.*TO'root'@'%'IDENTIFIEDBY'123456'WITHGRANTOPTION;FLUSHPRIVILEGES;说明：USEmysql：打开系统数据库mysql。GRANT…：赋予root用户（登录密码123456）所有权限，这样才能操作该MySQL实例所有功能，否则不能操作部分功能。执行结果如图。第1章

数据库基础——常用MySQL界面工具01界面工具简介图形化客户端基于Web的管理工具界面工具简介1．图形化客户端这类工具采用C/S架构，用户通过安装在计算机中的客户端软件连接并操作后台的MySQL数据库，原理如图，客户端是图形化用户界面（GUI）。除了MySQL官方提供的管理工具MySQLAdministrator和MySQLWorkbench外，还有很多第三方开发的优秀工具，比较著名的有Navicat、SequelPro、HeidiSQL、SQLMaestroMySQLToolsFamily、SQLWave、dbForgeStudio、DBToolsManager、MyDBStudio、AquaDataStudio、SQLyog、MySQL-Front、SQLBuddy等。界面工具简介2．基于Web的管理工具这类工具采用B/S架构，用户无须在计算机中安装客户端，管理工具运行于Web服务器上，如图所示。用户机器只要带有浏览器，就能以访问Web页面的方式操作MySQL数据库。02图形化界面工具创建MySQL服务器实例连接Navicat界面和功能简介MySQL语句的执行图形化界面工具1．创建MySQL服务器实例连接首先需要连接MySQL服务器实例，然后在该连接下操作MySQL。启动Navicat，单击“连接”按钮，创建MySQL服务器实例连接，如图。其中：连接名：是用户自己定义的连接MySQL服务器的名称。主机：指定MySQL服务器对应的计算机。如果MySQL服务器就在本机（笔者主机名HUAWEI）上，主机名可以使用HUAWEI，也可使用localhost或IP地址。端口：在安装MySQL时，选择默认通过“TCP/IP”协议3306端口号访问MySQL服务器，所以这里指定端口号3306。图形化界面工具2．Navicat界面和功能简介Navicat创建上述连接后，界面显示如图。图形化界面工具3．MySQL语句的执行MySQL语句在查询编辑器窗口输入和执行。单击“新建查询”按钮，系统新建一个查询编辑器窗口页。已经创建的查询编辑器窗口页仍然存在，但可以随时关闭。查询编辑器窗口系统除了显示子功能外，还显示连接和数据库下拉列表框供用户选择，表示查询指定连接下选择的数据库。也就是说，在不同的查询编辑器窗口下，可以使用不同用户连接的MySQL实例中的不同数据库。图形化界面工具在文本编辑区输入SQL语句，单击“”，在其下部“结果”选项卡显示SQL语句的执行结果。如图。第2章

数

据

类

型——数

值

型01整

数

类

型整数类型整数类型包括的数据类型如表。数据类型字节数有符号范围无符号范围（含）tinyint1-128~127（-27~27-1）0~255（0~28-1）smallint2-32768~32767（-215~215-1）0~65535（0~216-1）mediumint3-8388608~8388607（-223~223-1）0~16777215（0~224-1）intinteger4-2147483648~2147483647（-231~231-1）0~4294967295（0~232-1）bigint8±9.22×1018（-263~263-1）0~1.84×1019（0~264-1）02实

数

类

型单精度浮点类型双精度浮点类型定点类型实数类型实数类型包括浮点类型和定点类型，浮类点类型分为单精度浮点型（float和real）和双精度浮点型（double），它们遵循IEEE754标准；而定点型为decimal或者numeric型，实数类型定义格式如下：实数类型名(长度.小数位)1．单精度浮点类型表达单精度浮点数的float或real类型数据占4字节，其中1位表示符号位，8位表示指数，23位表示尾数。其中：有符号数表示范围：（-3.402823466E+38~1.175494351E-38)，0，(1.175494351E-38~3.402823466351E+38）；无符号数表示范围：0，（1.175494351E-38~3.402823466E+38）。float可以用含小数的数值表示，也可以用科学记数法表示，但只保证6位有效数字的准确性。定义float(m,d)表示数值总长为m位，其中有d位小数。例如，列设置为float(10,2)，输入-3584678.268，查询结果为-3584678.25。实数类型2．双精度浮点类型表达双精度浮点数double类型数据占8字节，其中1位表示符号位，11位表示指数，52位表示尾数。其中：有符号数表示范围：（-1.7976931348623157E+308~2.2250738585072014E-308），0，（2.2250738585072014E-308~1.7976931348623157E+308）；无符号数表示范围：0,（2.2250738585072014E-308~1.7976931348623157E+308）。定义double(m,d)表示数值总长为m位，其中有d位小数。double只保证16位有效数字的准确性。实数类型3．定点类型定点类型为decimal或者numeric类型，它们是同义词，用于存储定点数，保存必须为确切精度的值，例如会计系统中的货币数据。定点类型数值总长度范围为1～65，默认为10位数字，没有小数位。定义decimal(m,d)表示总长度m位，其中有d位小数。decimal(m)没有小数。小数部分超出d位，若四舍五入后，整数部分没有超出范围，则只给出警告，但能成功操作并四舍五入删除多余的小数位后保存；若四舍五入后，整数部分超出范围，则会报错，并拒绝处理。例如，列设置为decimal(10,2)，输入-3584678.268，查询结果为-3584678.27。03位

类

型位

类

型位类型就是bit类型，bit(n)型允许存储n位二进制位值（n取值为1～64，默认为1）。与tinyint相比，即使仅仅存储0和1两种状态的值，也需要1个字节，而bit(1)存储0和1两种状态的值只需要1个二进制位。如果我们存储的状态不超过2n，就可以使用bit(n)。另外，向位类型列输入的值只要<2n，就可以表达为二进制、十进制、十六进制等（例如0b10011、63、0x41），但bit(n)查询显示输出的是二进制值。第2章

数

据

类

型——字符串和二进制串型01字符串类型字符串描述字符集字符排序规则字符串类型1．字符串描述字符串数据类型如表。数据类型最多存放字符个数（字节）char(n)255varchar(n)65535（2）tinytext255（1）text65535（2）mediumtext16777215（3）longtext4294967295（4）字符串类型说明：（1）char(n)和varchar(n)括号中的n代表字符的个数，不是字节数。例如，采用UTF8字符集，存放n个纯ASCII码字符，占用n个字节。（2）实际上，tinytext、text、mediumtext、longtexttext类型就相当于varchar(n)，n就是该类型最大可存放的字符个数。例如：text类型相当于varchar(65535)。（3）如果一个表中的任何列都有可变长度，结果会使整个行变长。如果一张表包含任何变长的列（varchar、text等），则所有大于3个字符的char列都将被改变为varchar列。（4）可变长度实际存储空间除了字符本身外，还需要额外存储当前字符占用的空间字节数，而它本身占的字节为其数据类型最大字符数。例如：varchar(60000)，当前值'abc'，占用空间为3+2=5，前者是内容占用，后者为长度占用，因为最大长度60000需要用2个字节才能存放。（5）字符串数据类型数据用单引号和双引号括起，内容可以是平台字符，也可以是转义字符。例如“\'”为单引号，“\n”为回车。字符串类型2．字符集ASCII码及其字符集ASCII码即美国标准信息交换码，是目前世界上的计算机中使用最广泛的西文字符集编码。西文字符集由拉丁字母、数字、标点符号及一些特殊符号组成。在标准ASCII码表中，20H～7EH部分属于可打印字符，共95个。GB2312-80编码及其字符集1981年，我国颁布信息交换汉字编码的第一个国家标准，称为GB2312-80。其字符集由以下3部分组成。（1）字母、数字和各种符号，包括拉丁字母、俄文、日文平假名与片假名、希腊字母、汉语拼音等，共682个。（2）一级常用汉字，共3755个，按汉语拼音排列。（3）二级常用汉字，共3008个，按偏旁部首排列。GB2312-80字符集构成一个二维码表，它分成94行和94列，行号称为区号，列号称为位号，如图。字符串类型GB2312-80字符集的每个汉字或符号在码表中都有各自的位置，即所在的区号及位号。用区号及位号组合在一起的编码就是该汉字区位码。例如，“中”字的区号是54，位号是48，区位码是（54，48），用十六进制表示为（36H，30H）。为了与西文字符共存，汉字内码不能与ASCII码冲突。因为ASCII码用一个字节表示，最高位为0；汉字内码用2个字节表示，每个字节的最高位为1。如图。字符串类型GBK编码及其字符集GB2312编码汉字太少，缺少繁体字，无法满足人名、地名、古籍整理、古典文献研究等应用的需要。于是在1995年推出了“汉字内码扩充规范”，称为GBK标准，它在GB2312的基础上增加了大量的汉字（包括繁体字）和符号，共21003个汉字和883个图形符号。GB2312中的字符仍然采用原来的编码，仅仅对新增加的符号和汉字进行另外编码。GBK汉字内码也使用双字节表示，但仅第1字节最高位为“1”，如图。字符串类型Unicode编码及UTF8字符集Unicode只是字符集，却没有规定该如何存储、传输，而UTF规定了字符如何传输和存储。UTF又分为UTF-8、UTF-16和UTF-32，它们都可以完全表示Unicode标准中的所有字符，而UTF-8使用较多。UTF-8为单字节可变长编码，它根据Huffman编码的思想（最常出现的字符编码尽量短），以及不同符号使用不同的字节长度表示，因此UTF-8采用了1～4个字节表示一个符号变长的编码方式。从Unicode到UTF-8的编码方式如表。Unicode编码(十六进制)UTF-8字节流(二进制)字符000000-00007F0xxxxxxxASCII字符000080-0007FF110xxxxx10xxxxxx拉丁、希腊、阿拉伯……000800-00FFFF1110xxxx10xxxxxx10xxxxxx中、日、韩统一整理出来的称为CJK汉字010000-10FFFF11110xxx10xxxxxx10xxxxxx10xxxxxx其他字符。字符串类型3．字符排序规则字符排序又称字符序，MySQL中众多的排序规则可以归纳为以下两大类排序集：（1）字符集_语言/other_ci/cs这是默认的排序集，当字符无须区分大小写时采用该集合中的规则，规则命名以其对应的字符集作为前缀，例如：字符序utf8_general_ci，表明它是字符集utf8的字符序。ci对大小写不敏感，而cs对大小写敏感。utf8字符集对应的collation没有cs。（2）字符集_bin当字符需要区分大小写时，选择该排序集。每个字符集对应一定数量的排序规则，可以指定数据集如何排序以及字符串的排序规则。MySQL提供了4个等级的默认字符集以及排序规则，分别是服务器、数据库、表和列级别。02二进制串类型二进制数据类型大二进制数据类型二进制串类型1．二进制数据类型二进制数据类型包括binary和varbinary，它们类似于字符串类型char和varchar，但不同的是，它们存储的不是字符串，而是二进制串。所以它们没有字符集，并且排序和比较需要基于列字节的数值。二进制数据类型列可以保存数值，也可以保存字符串，均变成对应的字节保存。而且经常将声音、图像、视频等文件用系统函数转换成字节数据保存在其中。当保存binary值时，在它们右侧填充0x00值以达到指定长度。取值时不删除尾部的字节。二进制串类型2．大二进制数据类型大二进制数据类型为blob类型，是一个二进制对象，可以存储数据量很大的二进制数据，也可视为足够大的varbinary列，可用于存储大的图片、视频等。有4种blob类型：tinyblob、blob、mediumblob和longblob，它们可容纳值的最大长度不同。tinyblob类型：最大长度为255（28-1）字节。blob类型：最大长度为65535（216-1）字节。mediumblob类型：最大长度为16777215（224-1）字节。longblob类型：最大长度为4294967295（232-1）字节。实际长度与编码有关，比如UTF8编码，长度会减半。二进制串类型说明：（1）当保存或检索blob和text列的值时不删除尾部空格，这与varbinary和varchar列相同。（2）比较时将用空格对text进行扩充以适合比较的对象，正如char和varchar。（3）对于blob和text列的索引，必须指定索引前缀的长度。对于char和varchar，前缀长度是可选的。（4）blob和text列不能有默认值。（5）mysqlconnector/odbc将blob值定义为longvarbinary，将text值定义为longvarchar。（6）blob或text对象的最大值由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。（7）每个blob或text值分别由内部分配的对象表示。第2章

数

据

类

型——日期时间型日期时间型1．日期类型日期类型包含datetime、date和timestamp类型，这3者都用来表示日期/时间。（1）datetime类型：以'yyyy-mm-ddhh:mm:ss'（年-月-日时:分:秒）格式检索和显示。支持的范围为'1000-01-0100:00:00'～'9999-12-3123:59:59'。（2）date类型：用'yyyy-mm-dd'（年-月-日）格式检索和显示。支持的范围为'1000-01-01'～'9999-12-31'。（3）timestamp类型：包含日期和时间，范围为'1970-01-0100:00:01'utc～'2038-01-1903:14:07'utc。该类型能够自动存储记录修改的时间。timestamp的显示尺寸必须是偶数且在2～14范围内。如果指定0显示尺寸或比14大，尺寸被强制设定为14。在1～13范围内的奇数值尺寸被强制设定为下一个更大的偶数。将它设为NULL，默认为当前的日期和时间。可以使用任何常见格式指定datetime、date和timestamp值。对于包括日期部分间隔符的字符串值，如果日和月的值都小于10，则不需要指定两位数，例如'1979-6-9'与'1979-06-09'相同。如果一个数值是8位或14位长，则假定为'yyyymmdd'或'yyyymmddhhmmss'格式，前4位数表示年。如果为一个datetime或timestamp对象分配一个date值，结果值的时间部分被设置为'00:00:00'。函数返回的结果，其值适合datetime、date或者timestamp上下文，如now()或current_date。日期时间型2．时间类型time值的范围为'-838:59:59'～'838:59:59'。（1）可以用各种格式指定time值，如下。'dhh:mm:ss.fraction'格式的字符串。还可以用'hh:mm:ss.fraction'、'hh:mm:ss'、'hh:mm'、'dhh:mm:ss'、'dhh:mm'、'dhh'或'ss'。这里d表示日期，取值范围为0～31。'hhmmss'格式的没有间隔符的字符串，假定是有意义的时间。例如，'101112'被理解为'10:11:12'。hhmmss格式的数值，假定是有意义的时间。例如，101112被理解为'10:11:12'。（2）函数返回的结果，其值适合time上下文，如current_time。（3）对于指定为包括时间部分间隔符的字符串的time值，如果时、分或者秒值小于10，则不需要指定两位数。例如，'8:3:2'与'08:03:02'相同。（4）如果time值没有冒号，则最右侧的两位数表示秒，time值表示过去的时间而不是当天的时间。例如，'1112'和1112被解释为'00:11:12'（11分12秒），'12'和12被解释为'00:00:12'。相反，如果time值中使用冒号，则肯定被看作当天的时间。例如，'11:12'表示'11:12:00'，而不是'00:11:12'。（5）无效time值被转换为'00:00:00'。由于'00:00:00'本身是一个合法的time值，所以只从一个'00:00:00'值还不能判断原来的值是'00:00:00'还是不合法的。日期时间型3．年类型年类型就是year类型，是一个单字节类型，用于表示年。MySQL以yyyy格式检索和显示year值，范围为1901～2155。（1）可以指定各种格式的year值，如下。4位字符串：如'2018'。4位数字：如2018。2位字符串：'00'～69'转换为2000～2069，'70'～'99'转换为1970～1999。例如，'18'表示2018。一个字符串'0'或'00'可能被解释为0000。2位整数：范围为1～99。1～69转换为2001～2069，70～99转换为1970～1999。不能直接将零指定为数字并将它解释为2000。（2）函数返回的结果，其值适合year上下文，如now()。（3）非法year值被转换为0000。第2章

数

据

类

型——枚举、集合、键值和空间型01枚举类型和集合类型枚举类型：enum集合类型：set枚举类型和集合类型1．枚举类型：enum（1）定义enum('值1','值2','值3'...,'值n')定义需要enum需要设置成员表，成员为字符串（尾部空格将自动被删除），即使成员是数字，也必须是加引号的字符串。成员之间用“,”分隔。enum类型创建时指定的成员就是枚举值，每个枚举值位置对应一个索引编号，MySQL存储的就是这个索引编号。（2）存储enum类型存储的不是成员的字符串，而是成员的索引编号，定义1～255个成员的枚举索引编号需要1个字节存储；对于256～65535个成员，索引编号需要2个字节存储。例如，“专业”枚举定义enum('计算机','通信工程','人工智能')，该列可以取的值和对应的索引编号如表。成员索引编号NULLNULL“0'计算机'1'通信工程'2'人工智能'3枚举类型和集合类型2．集合类型：set（1）定义SET('值1','值2','值3'...,'值n')set类型成员不能为空，最少需要一个成员，最多包含64个成员。实际内容为这些成员构成的组合，1～8个成员的集合占1个字节，9～16个成员的集合占2个字节，17～24个成员的集合占3个字节，25～32个成员的集合占4个字节，33～64个成员的集合占8个字节。（2）存储set类型存储的不是成员的字符串，而是其二进制位信息。例如：“兴趣”集合定义set('唱歌','跳舞','智力游戏','足球运动','书法')。“兴趣”成员和存储值表示如表。成员二进制值十进制值'唱歌'11'跳舞'102'智力游戏'1004'足球运动'10008'书法'100001602键值（JSON）类型JSON对象JSON数组键值（JSON）类型1．JSON对象JSON类型的数据是以JSON对象的形式提供的，一个标准的JSON对象包含一组键值对，用逗号分隔，以“{”和“}”字符括起来：{"键1":"值1",...}创建JSON对象采用JSON_OBJECT系统函数格式如下：JSON_OBJECT(键1,值1,键2,值2,...)创建JSON对象和存储遵循的规则基本如下：（1）键名（无论中英文）都必须以双引号（"）括起来。（2）键值是字符串型（无论中英文）必须加双引号（"）或单引号（'）括起来。（3）键值是数值型（整数或实数类型），可以以数值或字符串两种格式存储。当以数值存储时，直接写出数值即可；而以字符串存储时，键值需加双引号或单引号。（4）键值是日期时间型，可以用两种方式给出：一种是直接以字符串形式写出；另一种是通过系统内置的日期时间函数获得。JSON对象构建的数据如图。键值（JSON）类型2．JSON数组当需要同时存储很多JSON对象或者标量值时，可将它们集中在一起构造出一个数组数据结构来统一管理。JSON数组包含多个值，这些值由逗号分隔格式如下：JSON_ARRAY(值,...)JSON数组元素显示包含在“[”和“]”中如下：[{键1":"值1",...},...]JSON数组构建的数据如图。03空

间

类

型单一空间类空间集合类空

间

类

型1．单一空间类（1）点类型：point(xy)存放一个坐标值，用空格分隔。例如：point(18206)。point(经度，维度)存放一个地理位置。例如：point(121.81534239.224632)。（2）线类型：linestring(点,…)由一系列点连接而成，点与point格式一致。如果线从头至尾没有交叉，那就是简单的；如果起点和终点重叠，那就是封闭的。例如：linestring(1025,1530,6840)。（3）多边形类型：polygon(点,…)可以是一个实心平面形，即没有内部边界，也可以有空洞，类似纽扣。最简单的就是只有一个外边界的情况。例如：polygon((010,1020),(2030,3010))。（4）geometry类型：它是单一空间类型的基类，point、linestring、polygon都是geometry的子类。例如：geometry(point(4010),linestring(1010,2020,1040),polygon((4040,2045,4530,4040))空间类型几何图形图例如图。空

间

类

型2．空间集合类multipoint、multilinestring、multipolygon、geometrycollection数据类型都是空间集合类，由多个point、linestring或polygon组合而成。（1）multipoint描述多个点。例如：multipoint(1040),(4030),(2020),(3010)，或者multipoint(1040,4030,2020,3010)。（2）multilinestring描述多条线。例如：multilinestring((1010,2020,1040),(4040,3030,4020,3010))。（3）multipolygon描述多个多边形。例如：multipolygon(((4040,2045,4530,4040)),((2035,1030,1010,305,4520,2035),(3020,2015,2025,3020)))。geometrycollection描述任何类型的对象的集合。空间集合类型图例如图。第3章

数据库和表——MySQL数据库MySQL数据库安装

MySQL

系统时，就生成了系统使用的数据库，包括information_schema、mysql和performance_schema等，MySQL把有关DBMS自身的管理信息都保存在这几个数据库中，如果删除了它们，MySQL将无法正常工作，故请读者操作时千万留神！如果安装时选择安装实例数据库，则系统还有另外两个实例数据库sakila和world。通过以下命令可以查看MySQL已有的数据库：SHOWDATABASES;命令执行结果如图。01创建数据库创建数据库创建数据库语句如下：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[创建选项,...]其中，创建选项：[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校对规则名IFNOTEXISTS：在创建数据库前须进行判断，只有该数据库目前尚不存在时才可执行CREATEDATABASE操作。用此选项可以避免出现数据库已经存在却再新建的错误。DEFAULT：指定默认值。CHARACTERSET：指定数据库字符集。COLLATE：指定字符集的校对规则。说明：如果在MySQL环境下采用下列命令设置了字符集，每个数据库创建时不需要单独重新设置：SETCHARACTER_SET_DATABASE='gbk';SETCHARACTER_SET_SERVER='gbk';创建数据库【例】创建test数据库。CREATEDATABASEtest;说明：如果创建数据库不指定选项就使用默认选项参数。如果已经创建了名为test的数据库，重复创建时系统将会提示数据库已经存在的错误信息，不能再创建。使用IFNOTEXISTS子句可不显示错误信息：CREATEDATABASEIFNOTEXISTStest;SHOWDATABASES; #显示的数据库中多了test02修改数据库修改数据库修改数据库语句如下：ALTER{DATABASE|SCHEMA}[数据库名]修改选项[,修改选项]...其中，修改选项：[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校对规则名说明：ALTERDATABASE用于更改数据库的全局特性，这些特性储存在数据库目录中的db.opt文件中。用户必须有对数据库进行修改的权限，才可使用ALTERDATABASE。修改数据库的选项与创建数据库的相同，功能不再重复说明。如果语句中将数据库名称忽略，则修改当前（默认）数据库。【例】修改数据库test的默认字符集和校对规则。ALTERDATABASEtestDEFAULTCHARACTERSETgb2312DEFAULTCOLLATEgb2312_chinese_ci;03删除数据库删除数据库删除数据库语句如下：DROPDATABASE[IFEXISTS]数据库名使用IFEXISTS子句，可避免删除不存在的数据库时出现MySQL错误信息。例如，删除test数据库：DROPDATABASEtest;SHOWDATABASES; #显示数据库中没有test04打开和关闭数据库打开和关闭数据库数据库创建后，在同一个会话中就自动打开。下列语句打开指定数据库，使其成为当前数据库：USE数据库名关闭数据库，其后会话就没有当前数据库了。第3章

数据库和表——MySQL表01创

建

表列定义及基本属性列键属性列其他属性列数据类型虚拟列（生成列）由原有的表创建新表创

建

表创建表语句如下：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名(列定义,...[表索引][完整性约束])[表选项]说明：TEMPORARY：包含此关键字表示新建的表为临时表，否则创建的表通常称为持久表。IFNOTEXISTS：在创建表前加上一个判断，只有该表目前尚不存在时才创建。列定义：列又称字段。列定义包括列名、数据类型和宽度等，还可包含是否允许空值和完整性约束。表索引：UNIQUEKEY(...)|PRIMARYKEY(...)|INDEX(...)：第1项指定部分列（或单列）值的唯一性，第2项列作为主键，第3项列创建索引。完整性约束：CHECK、FOREIGNKEY(…)：前者定义部分列（或单列）值数据完整性，后者定义本表与参考表的记录完整性。表选项：指定表的属性。创

建

表1．列定义及基本属性列按照下列形式定义：列名数据类型[(长度和小数)][空值][键][字符集][列其他属性][注释]其中：列名：必须符合标识符规则，长度不能超过64个字符，而且在表中要唯一。如果有MySQL保留字则必须用单引号括起来。数据类型[(长度和小数)]：列保存数据类型。整数型、实数型和字符串型需要指定长度，实数型还需要指定小数位。空值：NOTNULL|NULL：指定该列是否允许为空，前者为不允许为空，后者为可以为空。如果不指定，则默认为NULL。字符集：如果列数据类型为字符串型，可以指定存储字符的字符集和校对规则：CHARACTERSET字符集名COLLATE校对规则名注释：COMMENT'注释内容'，列的描述内容，说明列的作用。创

建

表2．列键属性列键属性如下：PRIMARYKEY：列设置为主键。一个表只能定义一个主键，主键一定要为NOTNULL。UNIQUE：列设置为唯一键。将确保所有值都有不同的值，只有NULL值可以重复。一个表可以设置多个列为唯一键。创

建

表3．列其他属性列还可以指定下列属性：AUTO_INCREMENT：设置自增属性，只有数据类型为整型的列才能设置此属性。当插入NULL值或0，将列原来值增1，顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且必须能被索引。DEFAULT：指定列默认值，默认值必须为一个常数。其中，BLOB和TEXT类型列不能被赋予默认值。UNSIGNED：对于整数类型，指定为无符号整数。ZEROFILL：可用于任何数值类型，用0填充所有剩余列空间。例如，无符号INT的默认宽度是10，因此，当值为4时，将它表示为0000000004。IDENTITY：包含系统所生成序号值的一个标识列，该序号值唯一标识表中的一列，可以作为键值。每个表只能有一个列被设置为标识属性，该列数据类型只能是整型。定义标识属性时，可指定其种子（起始）值、增量值，二者的默认值均为1。系统自动更新标识列值。例如：idintNOTNULLIDENTITYidintNOTNULLIDENTITY(1,1)创

建

表4．列数据类型列数据类型按照下列形式描述：整数类型名[(长度)][UNSIGNED][ZEROFILL]实数类型名[(长度.小数位)][UNSIGNED][ZEROFILL]大数据类型名字符串类型名(长度)[BINARY|ASCII|UNICODE]文本类型名[BINARY]日期时间类型名空间类型名位类型：bit[n]枚举类型：enum(值,...)集合类型：set(值,...)键值类型：json其中，具体类型名如下：整数类型名：tinyint|smallint|mediumint|int|integer|bigint实数类型名：real|double|decimal|numeric大数据类型名：tinyblob|blob|mediumblob|longblob字符串类型名：char|varchar|tinytext|text|mediumtext|longtext日期时间类型名：date|time|datetime|timestamp|year创

建

表5．虚拟列（生成列）虚拟列又称生成列，按照下列形式描述：列名数据类型GENERATEDALWAYSAS(列生成表达式)照表达式计算的值同步变化。【例】在xscj数据库中创建一个学生表，表名为xs。输入以下命令：CREATEDATABASExscj;USExscj;CREATETABLExs(学号 char(6) NOTNULLPRIMARYKEY,姓名 char(4) NOTNULL,专业 char(10) NULL,性别 tinyint(1) NOTNULLDEFAULT1,出生日期 date NOTNULL,总学分 tinyint(1) NULL,备注 text NULL,照片 blob NULL);创

建

表说明：（1）PRIMARYKEY：表示将“学号”列定义为主键。（2）DEFAULT1：表示“性别”的默认值为1。实际上，性别如果仅保存2种状态，可以定义为bit(1)。已经创建的表可以使用以下命令显示表结构：DESCRIBE表名;例如：USExscj; #打开xscj数据库SHOWTABLES; #显示xscj数据库中包含的表DESCRIBExs; #显示xs表结构创

建

表6．由原有的表创建新表除了全新创建，用户也可以直接复制数据库中原有表的结构和数据，用这种方式十分方便、快捷。由原有的表创建新表语句如下：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[LIKE源表名]|[AS(SELECT语句)];说明：（1）使用LIKE关键字创建一个与“源表”相同结构的新表，源表的列名、数据类型、是否空值、主键、默认值、索引、约束、分区等都将被复制，但是源表的记录不会复制，因此创建的新表是一个空表。（2）使用AS关键字可以复制SELECT语句查询的结果表，但源表的一些属性（如主键、生成列等）却不会被复制。创

建

表【例】在xscj数据库中，复制xs表创建表名为xs1的表；再创建一个名为xs2的表，包含xs表的部分指定列。打开xscj数据库：USExscj;CREATETABLExs1LIKExs; #复制xs表创建xs1表结构CREATETABLExs2AS(SELECT学号,姓名,专业,总学分FROMxs); #复制xs表部分列创建xs2表

SHOWTABLES; #（a）DESCRIBExs2; #（b）显示结果如图。

02修

改

表增加修改删除列增加删除列、索引和完整性约束修改表选项修

改

表ALTERTABLE用于修改原有表的结构。例如，可以增加（删减）列、创建（取消）索引、更改原有列的类型、重命名列或表，还可以更改表的注释和表的类型。修改表语句如下：ALTER[IGNORE]TABLE表名[ADD列定义] /*增加列*/[DROP列名] /*删除列*/[MODIFY列名列属性] /*修改列属性*/[ALTER列名SETDEFAULT值|DROPDEFAULT] /*设置默认值和删除默认值*/[RENAME表名 /*表更名*/[CHANGE原列名新列定义修改...] /*修改列名同时修改列属性*/[ADD主键|索引|完整性约束] /*增加表索引和完整性约束*/[DROP列名|索引名|主键|完整性约束名] /*删除表列、索引、主键和完整性约束*/[ORDERBY列名,...] /*列排序*/[表选项] /*增加修改表属性*/修

改

表1．增加修改删除列下面介绍增加修改删除列描述形式。（1）增加列ADD[COLUMN]列定义[FIRST|AFTER列名]列定义参考CREATETABLE语句。FIRST：指定增加列为第1列。AFTER列名：增加在指定列后面。（2）修改和删除指定列的默认值ALTER[COLUMN]SETDEFAULT值|DROPDEFAULT（3）修改列的名称和定义CHANGE旧列名列定义[FIRST|AFTER列名]（4）修改列属性MODIFY[COLUMN]列名列属性（5）删除列DROP列名修

改

表【例】修改xs2表结构。USExscj;ALTERTABLExs2 ADDCOLUMN考评tinyintNULL;ALTERTABLExs2 CHANGE考评考评分tinyint;ALTERTABLExs2 DROP考评分;ALTERTABLExs2 MODIFY专业char(12)NOTNULL;DESCRIBExs2;说明：第1句：在xs2表中增加新的一列“考评”。第2句：把xs2表“考评”列名变更为“考评分”。第3句：把xs2表的“考评分”列删除。第4名：把xs2表“专业”列数据类型改为char(12)。（6）指定记录排序列ORDERBY列名,...用于在创建新表时，让各行（记录）按一定的顺序排列。修

改

表2．增加删除列、索引和完整性约束下面介绍增加删除列、列索引和完整性约束的描述形式。（1）增加列、列索引和完整性约束ADD{INDEX|KEY}索引名索引定义|ADDPRIMARYKEY主键定义|ADDUNIQUE唯一性键名唯一性定义|ADDFOREIGNKEY外键名外键定义|ADDCHECK(完整性约束条件)【例】在xscj数据库的xs2表中，增加主键和生成列（专业编号）。USExscj;ALTERTABLExs2 ADD专业编号char(2)GENERATEDALWAYSAS(SUBSTRING(学号,3,2)), ADDPRIMARYKEY(学号);DESCRIBExs2;修

改

表说明：①xs2表采用“CREATE…ASSELECT…”方式创建，虽然原xs表包含“学号”列主键，但xs2表没有主键。这里给xs2表增加“学号”列主键。②“专业编号”列为char(2)数据类型，它由学号列的第3、4位生成，是虚拟的列。执行后，xs2表的结构如图。修

改

表（2）删除列、列索引、主键和完整性约束DROP[COLUMN]列名|DROP{INDEX|KEY}索引名|DROPPRIMARYKEY|DROPFOREIGNKEY外键约束名|CHECK完整性约束名（3）修改表索引名和表名RENAME{INDEX|KEY}原索引名TO新索引名3．修改表选项具体定义与CREATETABLE语句一样。03表删除和更名更改表名表删除表删除和更名1．更改表名除了上面的ALTERTABLE命令用“RENAME新表名”修改表名，还可以直接用下列语句来更改表的名字。RENAMETABLE

原表名TO新表名,...2．表删除当需要删除一个表时可以使用下列语句。DROP[TEMPORARY]TABLE[IFEXISTS]表名,...说明：这个命令将表的描述、完整性约束、索引及与表相关的权限等一并删除。第3章

数据库和表——表记录的操作01插

入

记

录插入新记录插入图片用已有表记录插入当前表记录替换旧记录系统模式插入记录1．插入新记录向表中插入全新的记录用下列语句。INSERT[选项][INTO]表名[(列名,...)]VALUES({表达式|DEFAULT},...),...或者INSERT[选项][INTO]表名[(列名,...)]|SET列名={表达式|DEFAULT},...[ONDUPLICATEKEYUPDATE列名=表达式,...]插入记录说明：（1）INTO子句：如果只给表的部分列插入数据，需要指定这些列。若没有指定列，表示对所有列插入数据，值的顺序与表结构定义的顺序相同。（2）

VALUES子句：包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若表名后不给出列名，则要在VALUES子句中给出每一列（除IDENTITY和timestamp类型的列）的值，如果列值为空，则值必须为NULL，否则会出错。（3）

选项：LOW_PRIORITY：可以使用在INSERT、DELETE和UPDATE等操作中，当原有客户端正在读取数据时，延迟操作的执行，直到没有其他客户端从表中读取数据为止。DELAYED：若使用此关键字，则服务器会把待插入的行放到一个缓冲器中，而发送INSERTDELAYED语句的客户端会继续运行。HIGH_PRIORITY：可以使用在SELECT和INSERT操作中，使操作优先执行。IGNORE：使用此关键字，在执行语句时出现的错误就会被当作警告处理。ONDUPLICATEKEYUPDATE…：使用此选项插入行后，若导致UNIQUEKEY或PRIMARYKEY出现重复值，则根据UPDATE后的语句修改旧行（使用此选项时DELAYED被忽略）。（4）

SET子句：SET子句用于给列指定值，使用SET子句时表名的后面省略列名。要插入数据的列名在SET子句中指定，列名等号后面为指定数据，未指定的列，其值为默认值。插入记录【例】向xscj数据库的xs表（表中列包括学号、姓名、专业、性别、出生日期、总学分、照片、备注）中插入如下一行记录：221101，王林，计算机，1，2004-02-10，15使用下列语句插入记录：USExscj;INSERTINTOxsVALUES('221101','王林','计算机',1,'2004-02-10',15,NULL,NULL);若xs表中性别采用默认值，照片和备注为NULL，插入记录：INSERTINTOxs(学号,姓名,专业,出生日期,总学分)VALUES('221104','韦严平','计算机','2004-08-26',12);使用SET子句插入记录：INSERTINTOxsSET学号='221201',姓名='刘华',专业='通信工程',性别=DEFAULT,出生日期='2004-06-10',