MySQL数据库技术.ppt

第5讲MySQL数据操作,课程：PHP网站开发,温故知新,知识回顾PHP浏览器数据传递和处理。PHP表单和表单元素数据运用。用户注册页面的实现。本节课知识要点phpMyAdmin管理使用MySQL数据库。MySQL的增删改查基本SQL语句综合操作。,项目导引：企业新闻发布系统数据库,我们思考几个问题，新闻发布系统中的新闻信息存放在什么地方？用户的账户信息和发布的新闻等内容又到哪里去了？这些内容又以什么形式存放呢？以上内容按照一定的格式存放在数据库（DataBase）中，数据库就是存放数据的仓库，其可以快速、安全的存储、处理大量的数据。PHP开发网站使用数据库：PHP可以与MySQL、ACCESS、SQLServer、ORACAL等多种数据库组合使用。PHP开发最常用的数据库：在这些数据库中，MySQL是世界上最为流行、开放源码、完全网络化、跨平台的数据库，能够满足多数中小型企业的需求，绝大多数PHP网站采用MySQL作为网站的数据库。,8-1数据库概述,1.数据库数据库是一种专门存储信息和维护信息的容器，严格地说数据库是“按照数据结构来组织、存储和管理信息的仓库”。外观上，数据库中的二维表（简称数据库表）和电子表格Excel是相同的。数据库表是由列和行构成，数据库表中的一列称为一个字段，每个字段用于存储某种数据类型的数据；数据库表中的一行称为一条记录，每条记录包含表中的一条详细信息。,8-1数据库概述,2.关系数据库管理系统（RDBMS）关系数据库管理系统（RDBMS或relationaldatabasemanagementsystem）就是管理关系数据库的软件系统。关系数据库管理系统主要功能就是创建关系数据库，并且在关系数据库中创建各种数据库对象（表、索引、视图、存储过程等）以及维护各个数据库对象。对于初学者而言，关系数据库管理系统最重要的功能莫过于创建数据库、创建数据库表以及完成数据库表记录的添加、修改、删除和查询等操作。,8-1数据库概述,3.结构化查询语言SQL结构化查询语言的英文全称是structuredquerylanguage，简称SQL。SQL是一种应用广泛的关系数据库语言，用于定义和管理关系数据库中的各种对象（表、索引、视图、存储过程等），也可以用于查询、修改和删除数据库表中的记录。,8-2数据库的设计,0.预备数据库的设计一般要从E-R模型开始，之后的步骤如下：1为每个实体建立一张表2为每个表选择一个主键（建议添加一个没有实际意义的字段作为主键）3增加外键以表示一对多关系4建立新表表示多对多关系5定义约束条件6评价关系的质量，并进行必要的改进（关于范式等知识请参考其他数据库书籍）7为每个字段选择合适的数据类型和取值范围,8-2数据库的设计,1.E-R模型E-R（Entity-Relationship）模型即实体-关系模型主要用于定义数据的存储需求，该模型已经广泛用于关系数据库设计中。E-R模型由实体、属性和关系三个基本要素构成。,8-2数据库的设计,2.主键（PrimaryKey）数据库表要求表中的每一行记录都必须是唯一的，即在同一张表中不允许出现完全相同的两条记录。在设计数据库时，为了保证记录的“唯一性”，最为普遍、最为推荐的做法是为表定义一个主键（primarykey）。数据库表中主键有以下两个特征：1表的主键可以由一个字段构成，也可以由多个字段构成（这种情况称为复合主键）。2数据库表中主键的值具有唯一性且不能取空值（NULL），当数据库表中的主键由多个字段构成时，每个字段的值不能取NULL值。,8-2数据库的设计,3.实体间的关系与外键（ForeignKey）班级实体和班主任实体之间为一对一关系，班级实体和学生实体之间为一对多关系，学生实体和课程实体之间为多对多关系。实体间的关系可以通过外键来表示。如果表A中的一个字段a对应于表B的主键b，则字段a称为表A的外键。此时存储在表A中字段a的值，同时这个字段值也是表B主键b的值。,8-2数据库的设计,4.约束（constraint）常用的约束条件有六种：主键（primarykey）约束外键（foreignkey）约束唯一性（unique）约束默认值（default）约束非空（notNULL）约束检查（check）约束,8.3数据表设计,1.数据表一个数据库中可以有多个数据表，每个数据表的名称必须是唯一的，表中每个字段的名称也必须是唯一的，每个字段都有对应的数据类型和取值范围。二维表中能唯一区分、确定不同记录的属性或属性组合，称为该表的主键。主键具有唯一性和非空性。例如：图书编号为图书表的主键，用户编号为用户表的主键。在图书类别表中，typeid（类别编号）字段为主键，在图书表中也有typeid（类别编号）字段，并且与图书类别表中的typeid（类别编号）字段是对应关系。这里我们把typeid（类别编号）字段称为图书类别表的主键，图书表的外键。,8.3数据表设计,2.数据类型（1）数值类型TINYINT：占1个字节，有符号数字的范围是-128到127，无符号的数字范围是0到255。SMALLINT：占2个字节，有符号数字的范围是-32768到32767，无符号数字的范围是0到65535。MEDIUMINT：占3个字节，有符号数字的范围是-8388608到8388607，无符号数字的范围是0到16777215。INT：占4个字节，有符号数字的范围是-2147483648到2147483647，无符号数字的范围是0到4294967295。BIGINT：占8个字节，有符号数字的范围是-9223372036854775808到9223372036854775807，无符号数字的范围是0到18446744073709551615。,8.3数据表设计,FLOAT(M,D)：占4个字节，不能无符号，允许的值是-3.402823466E+38到-1.175494351E-38，0和1.175494351E-38到3.402823466E+38。M是显示宽度，D是小数的位数。DOUBLE(M,D)：占8个字节，不能无符号，允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到1.7976931348623157E+308。M是显示宽度，D是小数位数。DECIMAL(M,D)：一个未压缩(unpack)的浮点数字，“未压缩”意味着数字作为一个字符串被存储，值的每一位使用一个字符。小数点，并且对于负数，“-”符号不在M中计算。如果D是0，值将没有小数点或小数部分。DECIMAL值的最大范围与DOUBLE相同，但是对一个给定的DECIMAL列，实际的范围可以通过M和D的选择被限制。如果D被省略，它被设置为0。如果M被省掉，它被设置为10。,8.3数据表设计,（2）日期和时间类型DATE：日期型，占3个字节，支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许使用字符串或数字把值赋给DATE列。DATETIME：日期和时间组合型，占8个字节，支持的范围是1000-01-0100:00:00到9999-12-3123:59:59。MySQL以YYYY-MM-DDHH:MM:SS格式来显示DATETIME值，但是允许使用字符串或数字把值赋给DATETIME的列。TIMESTAMP：时间戳，占4个字节，范围是1970-01-0100:00:00到2037年的某时。TIME：时间型，占3个字节，范围是-838:59:59到838:59:59。MySQL以HH:MM:SS格式来显示TIME值，但是允许使用字符串或数字把值赋给TIME列。YEAR(2|4)：2或4位数字格式的年(缺省是4位)，占1个字节，允许的值是1901到2155，如果使用2位，1970-2069(70-69)。MySQL以YYYY格式来显示YEAR值，但是允许把使用字符串或数字值赋给YEAR列。,8.3数据表设计,（3）字符串（字符）类型CHAR(M)：定长字符串，当存储时，总是用空格填满右边到指定的长度。M的范围是0255个字符。VARCHAR(M)：变长字符串，当值被存储时，尾部的空格被删除。M的范围是0255个字符。BLOB：二进制对象，可以保存图片、声音等二进制数据。BLOB类型根据其容纳值的长度不同，分为TINYBLOB（最大长度255字节）、BLOB（最大长度65535字节）、MEDIUMBLOB（最大长度16777215字节）和LONGBLOB（最大长度4GB字节）类型。TEXT：文本，用来保存字符数据。TEXT类型根据其容纳值的长度不同，分为TINYTEXT（最大长度255字节）、TEXT（最大长度65535字节）、MEDIUMTEXT（最大长度16777215字节）和LONGTEXT（最大长度4GB字节）类型。ENUM(value1,value2,.)：枚举，一个仅有一个值的字符串对象，这个值选自值列表value1、value2，.，或NULL。值列表最多能有65535不同的值。SET(value1,value2,.)：集合，有零个或多个值的一个字符串对象，其中每一个必须从值列表value1、value2、.选出。一个SET最多能有64个成员。,8.3数据表设计,3.新闻发布系统数据库设计依据“新闻发布系统”的需求分析，网站主要包含tb_users（用户表）、tb_news（新闻信息表）、tb_category（新闻类别表）等。,8.4项目实施,通过前面的学习我们知道商城数据库都有哪些表组成了，那么我们该如何创建数据库呢？对MySQL数据库的创建管理主要包括两种方式，一种是通过图形管理工具创建管理数据库，第二种是通过MySQL的客户端程序创建管理数据库，客户端程序的管理是通过SQL语句来实现。MySQL常用的图形化管理工具有phpMyAdmin、MySQLDumper、Navicat、MySQLGUITools、MySQLODBCConnector等，这些工具需要安装之后才能使用。phpMyAdmin是一个用PHP开发的基于Web方式的MySQL管理工具，它的官方网站是。PHP集成开发环境WampServer中包含有phpMyAdmin组件。,8.4.1phpMyAdmin数据库,图44phpMyAdmin主界面,1.创建数据库在主界面中可以创建数据库，创建数据库需要指定数据库的名字和编码方式，“新闻发布系统”数据库名称为db_news，采用utf-8的编码方式，如图45所示。单击“创建”按钮完成数据库的创建。,图45创建数据库,8.4.1phpMyAdmin数据库,2.创建数据表数据库创建后，进入创建数据表的页面，如图46所示。在此，输入数据表的名称和字段数量，例如输入表名为“tb_type”，字段数量为“3”。,图46创建数据表,8.4.1phpMyAdmin数据库,单击“执行”按钮，进入创建数据表字段的界面，如图47所示。在此可以设置字段的字段名称、数据类型、长度值、默认值、整理编码方式、属性、是否为空(Null)、索引和自动增长（A_I）等内容。设置完毕后，单击“保存”按钮，完成数据表结构的创建。,图47设置表字段,8.4.1phpMyAdmin数据库,数据表创建成功后，进入数据表“结构”页面，如图48所示，在这里可以修改表结构，如添加字段、删除字段、设置主键、索引、修改字段名称等。单击导航中“删除”按钮，可以将数据表删除。,图48管理数据表,8.4.1phpMyAdmin数据库,3.添加数据在数据表页面，单击“插入”按钮，进入添加数据页面，如图49所示，输入“值”后，单击“执行”按钮，即可将数据添加到数据表中。,图49添加数据,8.4.1phpMyAdmin数据库,4运行SQL语句在数据表页面，单击“插入”按钮，进入添加数据页面，如图49所示，输入“值”后，单击“执行”按钮，即可将数据添加到数据表中。,图410SQL语句执行界面,8.4.1phpMyAdmin数据库,5.数据库的备份对于数据库的管理经常需要备份和还原，在phpMyAdmin中我们通过导出、导入方式备份还原数据库。选择db_shop数据，单击导航栏中的“导出”按钮，进入导出界面，如图411所示。在此，我们可以选择导出的数据表，选择导出数据表的具体内容和导出的保存形式，在页面下方指定保存的文件名，最后，将SQL文件保存到磁盘上，完成数据备份。,图411数据导出界面,8.4.1phpMyAdmin数据库,6数据库的还原单击导航栏中的“Import”按钮，进入SQL文件导入界面，如图412所示。单击“浏览”按钮，选择需要导入的SQL脚本文件，然后单击“执行”按钮，系统将执行SQL文件中的SQL命令，完成数据还原。数据库创建好之后，会保存在MySQL安装路径下的data文件中，每一个数据库对应一个文件夹，通过文件夹操作也可以实现数据库的备份还原。掌握phpMyAdmin的基本操作后，使用phpMyAdmin完成商城数据库及数据表的创建。,图412数据导入部分界面,8.4.1phpMyAdmin数据库,实验任务1：phpMyAdmin数据库综合练习,8.4.2SQL之数据库的创建与管理,8.4.2SQL之数据库的创建与管理MySQL是基于客户机/服务器结构的数据库管理系统，通过客户机连接服务器成功后，再通过必要的操作指令对其进行操作，这种数据库操作指令被称为SQL（StructuredQueryLanguage）语言，即结构化查询语言。SQL语言结构简洁，功能强大，自IBM公司1981年推出以来，SQL语言得到了广泛的应用，目前MySQL、Oracle、SQLServer、Sybase、DB2等数据库都采用SQL作为查询语言。SQL语言包含以下四部分：数据定义语言（DDL）：用于定义和管理数据库对象，包括数据库、数据表、索引、视图等。例如：create、drop、alter等语句；数据操作语言（DML）：用于操作数据库对象中所包含的具体数据。例如：insert、update、delete语句；数据查询语言(DQL):用于查询数据库对象中的所包含的数据。例如：select语句；数据控制语言（DCL）：用来管理数据库的语言，包含管理权限及数据更改。例如：grant、revoke、commit、rollback等语句。,课堂引例：利用DML或DQL语言写出下面几个功能要求的SQL语句。1、向admin表加入一条记录(admin100，123456，admin100，1)。2、修改admin表中username为admin的密码更改为123456。3、删除admin表中一条记录(level=0)。4、查询输出admin表中所有记录，根据username倒叙排序。,8.4.2SQL之数据库的创建与管理,8.4.2SQL之数据库的创建与管理,安装NavicatforMySQL,8.4.2SQL之数据库的创建与管理,数据操作和数据查询语言几个简单的基本的sql语句选择：select*fromtable1where范围插入：insertintotable1(field1,field2)values(value1,value2)删除：deletefromtable1where范围更新：updatetable1setfield1=value1where范围查找：select*fromtable1wherefield1like%value1%like的语法很精妙，查资料!排序：select*fromtable1orderbyfield1,field2desc总数：selectcount*astotalcountfromtable1求和：selectsum(field1)assumvaluefromtable1平均：selectavg(field1)asavgvaluefromtable1最大：selectmax(field1)asmaxvaluefromtable1最小：selectmin(field1)asminvaluefromtable1,（2）添加记录向表中添加一条或多条数据记录，使用INSERT.VALUES语句实现，其语法格式如下：INSERTINTOtbl_nameSETcol_name=expression,col_name=expression,.INSERTINTOtbl_name(col_name,.)VALUES(expression,.),(.),.【例4-7】为tb_type和tb_book表添加记录。注：图书表的photo字段保存的是图片的完整路径。6.修改数据修改数据表中的数据可以使用UPDATE语句，其语法格式如下：UPDATEtbl_nameSETcol_name1=expr1,col_name2=expr2,.WHEREwhere_definition需要注意的是，当没有WHERE子句指定修改条件时，则表中所有记录的指定列都被修改。【例4-8】将tb_type表中类别名称为“教育类”的名字修改为“高职教育类”。,8.4.2SQL之数据库的创建与管理,7.删除数据将数据表中数据删除的语句是DELETE，其语法格式为：DELETEFROMtbl_nameWHEREwh