《PHP+MySQL动态网页设计》课件 第8章 MySQL数据库管理与应用

第8章MySQL数据库管理与应用8.1MySQL数据库概述MySQL数据库概述MySQL是一个小型的关系型数据库管理系统，由瑞典MySQLAB公司开发，目前属于Oracle公司。MySQL是一个真正多用户、多线程的结构化查询语言（SQL）数据库服务器，其所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL运行速度快、执行效率与稳定性高、操作简单、非常易于使用，是目前最流行的数据库管理系统应用软件之一。MySQL数据库概述MySQL软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都首选MySQL作为网站数据库。由于其社区版的性能卓越，搭配PHP、Linux和Apache可组成良好的Web开发环境。MySQL的官方网站是http:///，在该网站上可以免费下载其最新版本和各种技术资料，目前MySQL发布的最新版本是5.7.16。PPT模板下载：/moban/行业PPT模板：/hangye/节日PPT模板：/jieri/PPT素材下载：/sucai/PPT背景图片：/beijing/PPT图表下载：/tubiao/优秀PPT下载：/xiazai/PPT教程：/powerpoint/Word教程：/word/Excel教程：/excel/资料下载：/ziliao/PPT课件下载：/kejian/范文下载：/fanwen/试卷下载：/shiti/教案下载：/jiaoan/

谢谢！第8章MySQL数据库管理与应用8.2MySQL数据库的常见操作1.数据库的连接与关闭2.创建新用户并授权3.创建数据库4.数据库的备份与还原本节内容MySQL数据库的常见操作MySQL采用的是“客户机/服务器”体系结构，要连接上服务器，需要使用MySQL客户端程序。MySQL客户机主要用于传递SQL命令给服务器，并显示执行后的结果。可以与服务器运行在同一台机器上，也可以在网络中的两台机器上分别运行。在使用客户机连接服务器之前，一定要确保成功启动数据库服务器，才能监听客户机的连接请求。MySQL数据库的常见操作MySQL客户端程序可以使用phpMyAdmin软件，通过Web形式直接管理MySQL数据库。也可以使用Windows操作系统中的命令行程序（cmd.exe），通过执行系统命令来管理，这样可以更好地熟悉数据库的操作命令。1.数据库的连接与关闭当你连接一个MySQL服务器时，你的身份是由你从那台连接的主机和你指定的用户名来决定的，所以MySQL在认定身份中会考虑你的主机名和登录的用户名，只有客户机所在的主机被授予权限才能去连接MySQL服务器。连接MySQL服务器使用mysql命令，其语法格式如下： mysql–h服务器主机地址–u用户名–p用户密码1.数据库的连接与关闭示例8-1：使用管理员用户名“root”、密码“123456”连接本机的MySQL服务器。1.数据库的连接与关闭说明：在该控制台中输入SQL命令并发送，就可以对MySQL数据库服务器进行管理。每条SQL命令都要以分号（“;”）结束，然后按回车键进行发送。可以将一条SQL命令拆成多行，最后使用一个分号结束即可。可以通过\c来取消当前行的输入。可以通过\q、exit、或者quit来结束当前会话，退出客户机。以下两条命令也可实现同样的功能： mysql–uroot–p123456 mysql–hlocalhost–uroot–p1234562.创建新用户并授权创建新用户并授权撤销用户权限删除用户2.1创建新用户并授权为MySQL创建新用户的方法有两种：一种是使用GRANT语句；另一种是直接操作MySQL授权表mysql.user。其中使用GRANT语句的方法更简明、不容易出错。GRANT语句的语法格式如下：GRANT权限ON数据库.数据表TO用户名@登录主机IDENTIFIEDBY'密码';2.1创建新用户并授权示例8-2：以root用户登录到mySQL控制台，创建一个新用户“webuser”，密码为“12345678”，该用户可以在任何主机上登录，并对所有数据库的数据表具有查询、插入、修改和删除的权限。2.2撤销用户权限撤销用户权限使用REVOKE语句，其语法格式如下：REVOKE权限ON数据库.数据表FROM用户名@登录主机;2.2撤销用户权限示例8-3：以root用户登录到mySQL控制台，撤销用户“webuser”对所有数据库的数据表所具有的插入、修改和删除的权限。2.3删除用户删除用户使用DROPUSER语句，其语法格式如下：DROPUSER用户名@登录主机;

2.3删除用户示例8-4：以root用户登录到mySQL控制台，删除用户“webuser”。3.创建数据库创建数据库显示当前数据库服务器下的所有数据库列表选择数据库删除数据库3.1创建数据库连接到MySQL服务器以后，我们就可以创建数据表，并对数据表内容进行操作和管理。在建立数据表之前，首先需要创建一个数据库。创建数据库使用CREATEDATABASE语句，其语法格式如下：CREATEDATABASE[IFNOTEXISTS]数据库名称;3.1创建数据库示例8-5：以root用户登录到mySQL控制台，创建数据库webInfo。3.2显示当前数据库服务器下的所有数据库列表显示当前数据库服务器下的所有数据库列表使用SHOWDATABASES语句，常用来查看某一个数据库是否存在。其语法格式如下：SHOWDATABASES;3.2显示当前数据库服务器下的所有数据库列表示例8-6：显示当前数据库服务器下的所有数据库列表。3.3选择数据库选择一个数据库作为当前默认的数据库使用USE语句，其语法格式如下：USE数据库名称;3.3选择数据库示例8-7：选择“webInfo”作为当前默认的数据库。3.4删除数据库删除一个指定的数据库使用DROPDATABASE语句，其语法格式如下：DROPDATABASE[IFEXISTS]数据库名称;3.4删除数据库示例8-8：删除数据库“webInfo”。4.数据库的备份与还原备份数据库还原数据库4.1备份数据库备份数据库使用mysqldump命令，可以将数据库中的对象备份为一个的脚本文件。其语法格式如下：mysqldump-uusername-p--databasesdb1[db2…]>backup.sqlmysqldump-uusername-pdb[table1table2…]>backup.sql4.1备份数据库示例8-9：备份数据库webInfo。4.2还原数据库还原数据库使用mysql命令，可以通过一个之前备份的脚本文件还原数据库。其语法格式如下：mysql-uusername-p[db]<backup.sql4.2还原数据库示例8-10：通过示例8-9备份的脚本文件还原数据库webInfo。PPT模板下载：/moban/行业PPT模板：/hangye/节日PPT模板：/jieri/PPT素材下载：/sucai/PPT背景图片：/beijing/PPT图表下载：/tubiao/优秀PPT下载：/xiazai/PPT教程：/powerpoint/Word教程：/word/Excel教程：/excel/资料下载：/ziliao/PPT课件下载：/kejian/范文下载：/fanwen/试卷下载：/shiti/教案下载：/jiaoan/

谢谢！第8章MySQL数据库管理与应用8.3数据表的创建与管理1.数据类型2.创建数据表3.修改数据表4.删除数据表本节内容数据表的创建与管理数据表是数据库中一个非常重要的对象，也是其他对象的基础。一个数据库中可以包含一张或多张表，表是数据的集合，是用来存储数据和操作数据的逻辑结构。数据在表中是按照行和列的格式来组织排列的，每一行代表一条唯一的记录，每一列代表记录的一个属性。1.数据类型MySQL的数据类型主要分为以下三大类：数值类型、字符串类型和日期/时间类型。1.1数值类型MySQL中的数值类型分为整型和浮点型两种。整型中又分为TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种；浮点型又分为FLOAT、DOUBLE、DECIMAL三种。1.1数值类型数值类型及其取值范围如下表所示。1.2字符串类型字符串类型可以用来存储任何一种值，所以它是最基本的数据类型之一。MySQL支持以单引号或双引号包含的字符串，例如"MySQL"、'MySQL'，它们表示的是同一个字符串。1.2字符串类型字符串类型及其取值范围如下表所示。1.3日期/时间类型日期/时间类型是用来存储诸如“2016-9-1”或者“12:30:00”这一类的日期/时间的值。日期/时间类型及其取值范围如下表所示。1.4NULL值NULL意味着“没有值”或“未知值”，可以将NULL值插入到数据表中并从表中检索它们，也可以测试某个值是否为NULL，但能对NULL值进行算术计算。如果对NULL值进行算术运算，其结果还是NULL。在MySQL中，0或NULL都意味着假，而其余值都意味着真。

2.创建数据表数据库创建以后，使用USE语句选定这个新创建的数据库作为当前默认的数据库，然后就可以在该数据库中创建数据表了。创建数据表使用CREATETABLE语句，其语法格式如下： CREATETABLE[IFNOTEXISTS]表名称(

字段名1数据类型[属性][索引],

字段名2数据类型[属性][索引], …

字段名n数据类型[属性][索引]

)[表类型][表字符集];2.创建数据表通过对学生表（student）中所存储数据的分析，设计出如下表所示的表结构。2.创建数据表示例8-11：创建数据库stuInfo，在该数据库中创建学生表（student）。2.1查看数据表的列表数据表成功创建后，可以在MySQL控制台中使用“SHOWTABLES”语句进行查看，其语法格式如下：SHOWTABLES;2.1查看数据表的列表示例8-12：查看数据库stuInfo中所有数据表。2.2查看数据表的结构可以使用“DESCRIBE”或“DESC”语句查看数据表的结构，其语法格式如下：DESCRIBE表名; 或： DESC表名;2.2查看数据表的结构示例8-13：查看数据库stuInfo中学生表（student）的结构。2.3查看数据表的创建语句可以使用“SHOWCREATETABLE”语句查看数据表的创建语句，其语法格式如下：SHOWCREATETABLE表名;2.3查看数据表的创建语句示例8-14：查看数据库stuInfo中学生表（student）的创建语句。3.修改数据表修改数据表是指修改表的结构，包括添加新的字段、修改原有字段的数据类型等、删除原有的字段等。修改数据表使用ALTERTABLE语句，其语法格式如下：ALTERTABLE表名称

ADD字段名数据类型[属性][索引][FIRST|AFTER列名];| MODIFY列名数据类型[属性][索引];| CHANGE列名新列名数据类型[属性][索引];| DROP列名;| AUTO_INCREMENT=n;| RENAMEAS新表名;3.修改数据表示例8-15：在学生表（student）的出生日期birthday字段的后面添加一个新的入学日期EntryDate字段。3.修改数据表示例8-16：将学生表（student）的入学日期entryDate字段的数据类型更改为TIMESTAMP。3.修改数据表示例8-17：将学生表（student）的入学日期entryDate字段的名字更改为rxDate、数据类型更改为DATETIME。3.修改数据表示例8-18：删除学生表（student）的入学日期rxDate字段。4.删除数据表当某张数据表不再需要时，可以使用DROPTABLE语句进行删除。DROPTABLE语句的语法格式如下：DROPTABLE[IFEXISTS]表名称;4.删除数据表示例8-19：把学生表（student）删除。PPT模板下载：/moban/行业PPT模板：/hangye/节日PPT模板：/jieri/PPT素材下载：/sucai/PPT背景图片：/beijing/PPT图表下载：/tubiao/优秀PPT下载：/xiazai/PPT教程：/powerpoint/Word教程：/word/Excel教程：/excel/资料下载：/ziliao/PPT课件下载：/kejian/范文下载：/fanwen/试卷下载：/shiti/教案下载：/jiaoan/

谢谢！第8章MySQL数据库管理与应用8.4数据表内容的管理1.添加数据2.修改数据3.删除数据本节内容数据表内容的管理创建了数据表，就可以向表中添加数据；在插入了数据后，就可以对数据进行修改或者删除操作。1.添加数据使用INSERT语句可以向表中添加数据。INSERT语句的语法格式如下：INSERT[INTO]表名[(字段名1,字段名2,...,字段名n)]VALUES(值1,值2,...,值n);说明：INSERT语句也可以一次性插入多行数据，即在VALUES子句的后面加上多个表达式列表，并以逗号隔开。1.添加数据示例8-20：向学生表（student）中添加一行数据。1.添加数据示例8-21：向学生表（student）中添加多行数据。2.修改数据使用UPDATE语句可以对表中的一列或多列数据进行修改，修改时必须指定需要修改的字段，并且赋予新值；通过WHERE子句可以限定要更新的数据行。UPDATE语句的语法格式如下：UPDATE表名SET字段名1=值1[,字段名2=值2,…,字段名n=值n][WHERE条件];2.修改数据示例8-22：修改学生表（student）中学号ID字段值为3的数据记录，将出生日期birthday字段的值更改为“1993-11-25”，将备注remark字段的值更改为“班长”。3.删除数据使用DELETE语句可以删除表中的一条或多条数据记录，通过WHERE子句可以限定要删除的数据行，否则清空整个数据表。DELETE语句的语法格式如下：DELETEFROM表名[WHERE条件];3.删除数据示例8-23：删除学生表（student）中学号ID字段值为3的数据记录。PPT模板下载：/moban/行业PPT模板：/hangye/节日PPT模板：/jieri/PPT素材下载：/sucai/PPT背景图片：/beijing/PPT图表下载：/tubiao/优秀PPT下载：/xiazai/PPT教程：/powerpoint/Word教程：/word/Excel教程：/excel/资料下载：/ziliao/PPT课件下载：/kejian/范文下载：/fanwen/试卷下载：/shiti/教案下载：/jiaoan/

谢谢！第8章MySQL数据库管理与应用8.5数据查询1.选择字段2.WHERE子句3.ORDERBY子句4.多表查询5.统计函数6.GROUPBY子句7.LIMIT子句8.嵌套查询9.带子查询的数据更新本节内容数据查询数据查询是指数据库管理系统按照用户指定的条件，从数据库相关表中检索满足条件的数据的过程。（1）SELECT语句SELECT语句主要用于数据的查询检索，是SQL语言的核心，也是使用频率最高的一条语句。SELECT语句可以让数据库服务器根据用户的要求，从数据库的表中检索出所需要的数据，并按照用户指定的格式进行整理并返回。（1）SELECT语句SELECT语句的语法格式如下：SELECT[ALL|DISTINCT]*|字段列表FROM表名[WHERE查询条件][GROUPBY分组字段[HAVING分组条件]][ORDERBY排序字段[ASC|DESC]][LIMIT[初始位置,]记录数];（2）示例数据库-学生数据库stuInfo班级表（department）department(id,deptNo,deptName)（2）示例数据库-学生数据库stuInfo学生表（student）student(id,sNo,sName,sex,birthday,dept_id,photo,remark)（2）示例数据库-学生数据库stuInfo课程表（course）course(id,cNo,cName,credit,remark)（2）示例数据库-学生数据库stuInfo成绩表（score）score(s_id,c_id,grade)1.选择字段选择所有字段选择指定字段定义字段别名使用DISTINCT关键字1.1选择所有字段在SELECT子句中可以使用星号（*），显示表中所有的字段。其语法格式如下：SELECT*FROM表名;1.1选择所有字段示例8-24：显示department表中的所有信息。1.1选择所有字段示例8-25：按列输出department表中的所有信息。1.2选择指定字段选择指定字段的语法格式如下：SELECT字段名1[,字段名2,…,字段名n]FROM表名;1.2选择指定字段示例8-26：从student表中查询出班级编号（dept_id），学号（sNo）、姓名（sName）、和性别（sex）的学生信息。1.3定义字段别名默认情况下返回的查询结果以字段名作为列标题的，可以为返回的字段指定一个新的列标题，也可给通过计算产生的新列指定一个列标题。其语法格式如下：SELECT字段名1[AS]列标题1[,字段名2[AS]列标题2,…]FROM表名;1.3定义字段别名示例8-27：以“学号

姓名

性别

出生日期”作为列标题显示学生信息。1.4使用DISTINCT关键字如果返回的查询结果中包含重复的记录，可以使用DISTINCT关键字取消重复的数据，只返回其中的一条。其语法格式如下：SELECTDISTINCT字段列表FROM表名;1.4使用DISTINCT关键字示例8-28：查询score表，显示选修了课程的学生ID，如果有多个相同的ID，只需显示一个即可。2.WHERE子句在实际工作中，大部分查询并不是针对表中所有数据记录的查询，而是要找出满足某些条件的数据记录。我们可以在SELECT语句中使用WHERE子句，其语法格式如下：SELECT*|字段列表FROM表名WHERE查询条件;说明：查询条件可以是：比较表达式、逻辑表达式、以及其他一些谓词构成的表达式（字符串模糊匹配LIKE、数据范围BETWEEN、列表数据IN、空值判定ISNULL等）。2.1使用比较运算符WHERE子句允许使用的比较运算符如下表所示：2.1使用比较运算符示例8-29：查询student表中女学生的信息。2.1使用比较运算符示例8-30：查询course表中超过4个学分（credit）的课程信息。2.2使用逻辑运算符WHERE子句允许使用的逻辑运算符如下表所示：2.2使用逻辑运算符示例8-31：查询student表中1995年出生的学生信息。2.3使用LIKE进行模糊查询在WHERE子句中，通过LIKE关键字与“%”和“_”两个通配符的使用，可以对数据表中的数据进行模糊查询。这两个通配符的含义如下所示：百分号（%）：表示匹配0个或者任意多个字符。下划线（_）：表示匹配任意一个字符。2.3使用LIKE进行模糊查询示例8-32：从student表中检索出所有姓“杨”的学生信息。2.3使用LIKE进行模糊查询示例8-33：从student表中检索出姓名的第二个字是“伟”和“先”的学生信息。2.4使用BETWEENAND进行范围比较查询在WHERE子句中，可以使用BETWEENAND关键字对指定字段的某一范围内的数据进行比较查询，其与使用“>=”且“<=”的功能一样。其语法格式如下：字段名[NOT]BETWEEN值1AND值22.4使用BETWEENAND进行范围比较查询示例8-34：使用BETWEENAND关键字实现示例8-31的功能。2.4使用BETWEENAND进行范围比较查询示例8-35：从score表中查询出成绩不在60-89分之间的学生成绩信息。2.5使用IN进行范围比对查询如果字段的取值范围不是一个连续的区间，而是一些离散的值，可以使用IN关键字对指定字段进行范围比对查询。其语法格式如下：字段名[NOT]IN(值1[,值2,值3,…])2.5使用IN进行范围比对查询示例8-36：查询student表中学号（sNo）为1308013101，1309122503，1312054904的学生信息。2.6空值（NULL）的判断空值（NULL）是一个特殊的值，它仅仅是一个符号，不等于空字符串，也不等于0。空值判断的语法格式如下：字段名IS[NOT]NULL2.6空值（NULL）的判断示例8-37：检索course表中备注（remark）为空的课程记录。3.ORDERBY子句在通常情况下，数据库中的数据记录行在显示时是无序的，它按照数据记录插入数据库时的顺序排列，因此用SELECT语句查询的结果也是无序的。使用ORDERBY子句可以将查询结果进行排序显示，其语法格式如下：SELECT*|字段列表FROM表名[WHERE查询条件]ORDERBY字段名1[ASC|DESC][,字段名2[ASC|DESC]][,…];3.ORDERBY子句示例8-38：查询student表中的男生信息，按照出生日期（birthday）的降序排列。3.ORDERBY子句示例8-39：查询student表中的数据，先按性别（sex）的降序排列，当性别相同时再按照学号（sNo）的升序排列。4.多表查询多表查询的本质是多张表通过关联的列的连接，所以多表查询也称为连接查询。多表（连接）查询的语法格式有如下两种：第一种语法格式：SELECT*|字段列表FROM表名1[连接类型]JOIN表名2ON连接条件[[连接类型]JOIN表名3ON连接条件][…]WHERE查询条件;第二种语法格式：SELECT*|字段列表FROM表名1,表名2[,表名3,…表名n]WHERE连接条件AND查询条件;4.1内连接（INNERJOIN）内连接（INNERJOIN）是指多个表通过连接条件中共享列的值进行的比较连接，INNER关键字可以省略，当未指明连接类型时，默认为内连接。内连接值显示两个表中所有匹配数据的行，如下图所示。4.1内连接（INNERJOIN）示例8-40：查询所有女生的学生ID、学号、姓名、性别和班级名称。4.1内连接（INNERJOIN）示例8-41：查询学号（sNo）为“1308013101”学生的学生ID、学号、姓名、性别、课程名和成绩。4.2外连接（OUTERJOIN）外连接显示包含来自一个表中所有行和来自另一个表中匹配行的结果集，如下图所示。外连接主要又分为左外连接和右外连接。4.2外连接（OUTERJOIN）示例8-42：显示所有女生的学生ID、学号、姓名、性别、课程ID和成绩。5.统计函数MySQL不仅可以查询返回满足条件的记录，还可以对数据进行统计汇总。常用的SQL统计函数如下表所示。5.统计函数示例8-43：统计学号（sNo）为“1308013101”的学生选修课程的最高分、最低分、平均分和总分。5.统计函数示例8-44：统计student表中的男生人数。5.统计函数示例8-45：统计已选修课程的学生人数。6.GROUPBY子句使用GROUPBY子句，可以显示分组的汇总数据。该子句的功能是按照指定的字段，先将数据分成多个组（相同字段的值为一组），然后对每个组汇总出一个数据。即，结果集中每个组都有一行汇总数据。6.GROUPBY子句GROUPBY子句的语法格式为：SELECT字段名1[,字段名2,…],统计函数FROM表名[WHERE查询条件]GROUPBY字段名1[,字段名2,…][HAVING分组条件]6.GROUPBY子句示例8-46：分组统计男、女学生的人数。6.GROUPBY子句示例8-47：分组统计被学生选修超过1次的课程名称、选修次数和平均分。6.GROUPBY子句示例8-48：分组统计选修超过2门课程、且平均成绩高于80分的学生学号、姓名、选修门数和平均分。7.LIMIT子句使用LIMIT子句，可以限制SELECT语句返回的记录数。LIMIT子句通常位于SELECT语句的最后面，其语法格式如下：SELECT*|字段列表FROM表名[WHERE查询条件][ORDERBY排序字段[ASC|DESC]][LIMIT[初始位置,]记录数];说明：初始位置指定从查询结果集中的哪一条记录开始返回，如果省略，则表示从第1条记录开始返回，第1条记录的位置为0。7.LIMIT子句示例8-49：返回年龄最小的5位同学的信息。7.LIMIT子句示例8-50：返回课程编号（cNo）为“01001”的课程的第2~4名成绩，包括学号、姓名、课程名称、成绩。8.嵌套查询嵌套查询是指一个SELECT语句的WHERW子句中还包含另外一个SELECT语句，外层的SELECT语句称为外部查询或父查询，内层的SELECT语句称为内部查询或子查询，子查询需要使用圆括号“（）”括起来。SQL语言允许多层嵌套查询，即一个子查询中还可以有其他子查询。嵌套查询的求解方法是由里向外处理，即每个子查询都是在上一级查询之前求解，子查询的结果用于建立其父查询的查询条件。8.1子查询的返回值为单列单值如果子查询的返回值为单列单值，可以通过使用“=”、“!=”、“>”、“<