MySQL数据库技术.ppt

第5讲 MySQL数据操作,课程：PHP网站开发,温故知新,知识回顾 PHP浏览器数据传递和处理。 PHP表单和表单元素数据运用。 用户注册页面的实现。 本节课知识要点 phpMyAdmin管理使用MySQL数据库。 MySQL的增删改查基本SQL语句综合操作。,项目导引：企业新闻发布系统数据库,我们思考几个问题，新闻发布系统中的新闻信息存放在什么地方？用户的账户信息和发布的新闻等内容又到哪里去了？这些内容又以什么形式存放呢？ 以上内容按照一定的格式存放在数据库（Data Base）中，数据库就是存放数据的仓库，其可以快速、安全的存储、处理大量的数据。 PHP开发网站使用数据库：PHP可以与MySQL、ACCESS、SQL Server、ORACAL等多种数据库组合使用。 PHP开发最常用的数据库：在这些数据库中，MySQL是世界上最为流行、开放源码、完全网络化、跨平台的数据库，能够满足多数中小型企业的需求，绝大多数PHP网站采用MySQL作为网站的数据库。,8-1 数据库概述,1. 数据库 数据库是一种专门存储信息和维护信息的容器，严格地说数据库是“按照数据结构来组织、存储和管理信息的仓库”。 外观上，数据库中的二维表（简称数据库表）和电子表格Excel是相同的。数据库表是由列和行构成，数据库表中的一列称为一个字段，每个字段用于存储某种数据类型的数据；数据库表中的一行称为一条记录，每条记录包含表中的一条详细信息。,8-1 数据库概述,2. 关系数据库管理系统（RDBMS） 关系数据库管理系统（RDBMS或relational database management system）就是管理关系数据库的软件系统。关系数据库管理系统主要功能就是创建关系数据库，并且在关系数据库中创建各种数据库对象（表、索引、视图、存储过程等）以及维护各个数据库对象。对于初学者而言，关系数据库管理系统最重要的功能莫过于创建数据库、创建数据库表以及完成数据库表记录的添加、修改、删除和查询等操作。,8-1 数据库概述,3. 结构化查询语言SQL 结构化查询语言的英文全称是structured query language，简称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. 主键（Primary Key） 数据库表要求表中的每一行记录都必须是唯一的，即在同一张表中不允许出现完全相同的两条记录。在设计数据库时，为了保证记录的“唯一性”，最为普遍、最为推荐的做法是为表定义一个主键（primary key）。数据库表中主键有以下两个特征： 1表的主键可以由一个字段构成，也可以由多个字段构成（这种情况称为复合主键）。 2数据库表中主键的值具有唯一性且不能取空值（NULL），当数据库表中的主键由多个字段构成时，每个字段的值不能取NULL值。,8-2 数据库的设计,3. 实体间的关系与外键（Foreign Key） 班级实体和班主任实体之间为一对一关系，班级实体和学生实体之间为一对多关系，学生实体和课程实体之间为多对多关系。实体间的关系可以通过外键来表示。如果表A中的一个字段a对应于表B的主键b，则字段a称为表A的外键。此时存储在表A中字段a的值，同时这个字段值也是表B主键b的值。,8-2 数据库的设计,4. 约束（constraint） 常用的约束条件有六种： 主键（primary key）约束 外键（foreign key）约束 唯一性（unique）约束 默认值（default）约束 非空（not NULL）约束 检查（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-01 00:00:00到9999-12-31 23:59:59。MySQL以YYYY-MM-DD HH:MM:SS格式来显示DATETIME值，但是允许使用字符串或数字把值赋给DATETIME的列。 TIMESTAMP：时间戳，占4个字节，范围是1970-01-01 00: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、MySQL GUI Tools、MySQL ODBC Connector等，这些工具需要安装之后才能使用。 phpMyAdmin是一个用PHP开发的基于Web方式的MySQL管理工具，它的官方网站是。PHP集成开发环境WampServer中包含有phpMyAdmin组件。,8.4.1 phpMyAdmin数据库,图44 phpMyAdmin主界面,1.创建数据库 在主界面中可以创建数据库，创建数据库需要指定数据库的名字和编码方式，“新闻发布系统” 数据库名称为db_news，采用utf-8的编码方式，如图45所示。单击“创建”按钮完成数据库的创建。,图45 创建数据库,8.4.1 phpMyAdmin数据库,2.创建数据表 数据库创建后，进入创建数据表的页面，如图46所示。在此，输入数据表的名称和字段数量，例如输入表名为“tb_type”，字段数量为“3”。,图46 创建数据表,8.4.1 phpMyAdmin数据库,单击“执行”按钮，进入创建数据表字段的界面，如图47所示。在此可以设置字段的字段名称、数据类型、长度值、默认值、整理编码方式、属性、是否为空(Null)、索引和自动增长（A_I）等内容。设置完毕后，单击“保存”按钮，完成数据表结构的创建。,图47 设置表字段,8.4.1 phpMyAdmin数据库,数据表创建成功后，进入数据表“结构”页面，如图48所示，在这里可以修改表结构，如添加字段、删除字段、设置主键、索引、修改字段名称等。单击导航中“删除”按钮，可以将数据表删除。,图48 管理数据表,8.4.1 phpMyAdmin数据库,3.添加数据 在数据表页面，单击“插入”按钮，进入添加数据页面，如图49所示，输入“值”后，单击“执行”按钮，即可将数据添加到数据表中。,图49 添加数据,8.4.1 phpMyAdmin数据库,4运行SQL语句 在数据表页面，单击“插入”按钮，进入添加数据页面，如图49所示，输入“值”后，单击“执行”按钮，即可将数据添加到数据表中。,图410 SQL语句执行界面,8.4.1 phpMyAdmin数据库,5.数据库的备份 对于数据库的管理经常需要备份和还原，在phpMyAdmin中我们通过导出、导入方式备份还原数据库。选择db_shop数据，单击导航栏中的“导出”按钮，进入导出界面，如图411所示。在此，我们可以选择导出的数据表，选择导出数据表的具体内容和导出的保存形式，在页面下方指定保存的文件名，最后，将SQL文件保存到磁盘上，完成数据备份。,图411数据导出界面,8.4.1 phpMyAdmin数据库,6数据库的还原 单击导航栏中的“Import”按钮，进入SQL文件导入界面，如图412所示。单击“浏览”按钮，选择需要导入的SQL脚本文件，然后单击“执行”按钮，系统将执行SQL文件中的SQL命令，完成数据还原。 数据库创建好之后，会保存在MySQL安装路径下的data文件中，每一个数据库对应一个文件夹，通过文件夹操作也可以实现数据库的备份还原。 掌握phpMyAdmin的基本操作后，使用phpMyAdmin完成商城数据库及数据表的创建。,图412数据导入部分界面,8.4.1 phpMyAdmin数据库,实验任务1：phpMyAdmin数据库综合练习,8.4.2 SQL之数据库的创建与管理,8.4.2 SQL之数据库的创建与管理 MySQL是基于客户机/服务器结构的数据库管理系统，通过客户机连接服务器成功后，再通过必要的操作指令对其进行操作，这种数据库操作指令被称为SQL（Structured Query Language）语言，即结构化查询语言。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.2 SQL之数据库的创建与管理,8.4.2 SQL之数据库的创建与管理,安装Navicat for MySQL,8.4.2 SQL之数据库的创建与管理,数据操作和数据查询语言 几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like %value1% like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 desc 总数：select count * as totalcount from table1 求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1,（2） 添加记录 向表中添加一条或多条数据记录，使用INSERT.VALUES语句实现，其语法格式如下： INSERT INTO tbl_name SET col_name=expression, col_name=expression, . INSERT INTO tbl_name(col_name,.) VALUES (expression,.),(.),. 【例4-7】为tb_type和tb_book表添加记录。注：图书表的photo字段保存的是图片的完整路径。 6.修改数据 修改数据表中的数据可以使用UPDATE语句，其语法格式如下： UPDATE tbl_name SET col_name1=expr1,col_name2=expr2,. WHERE where_definition 需要注意的是，当没有WHERE子句指定修改条件时，则表中所有记录的指定列都被修改。 【例4-8】将tb_type表中类别名称为“教育类”的名字修改为“高职教育类”。,8.4.2 SQL之数据库的创建与管理,7.删除数据 将数据表中数据删除的语句是DELETE，其语法格式为： DELETE FROM tbl_name WHERE where_definition 需要注意的是，当没有WHERE子句指定删除条件时，则删除表中所有记录。 【例4-9】删除tb_type 表中类别名称为“高职教育类”的记录。 4.4.4 SQL之数据库的查询管理 数据查询是数据库的核心操作，用户可以通过查询获得所需要的数据。查询操作可以通过SELECT语句实现，该语句在执行时会根据要求从一个或多个数据表中选取特定的行和列，形成一个临时表传送给用户。 其中： select_expression：描述结果集的列，列与列之间用逗号分隔； FROM table_list：用于指定产生查询结果集的数据来源的表或视图的名称； WHERE where_definition：用于指定所检索的数据应该满足的条件； GROUP BY col_name：用于分组统计时指定分组的条件；,8.4.2 SQL之数据库的创建与管理,HAVING where_definition：与GROUP BY子句一起使用，用于对分组统计的结果设置条件，进行组数据选择； ORDER BY sorting_columns ASC|DESC：指定在SELECT语句返回的列中所使用的排序顺序；ASC和DESC用于指定行是按升序还是按降序排列； LIMIToffset, rows:用来限制SELECT语句返回的行数。 需要注意，SELECT语句中的子句必须按以上语法格式的顺序给出，例如，HAVING子句必须在GROUP BY子句之后，ORDER BY子句之前。 1.基本查询 2.条件查询 3.排序查询 4.统计查询 5.连接查询 6.子查询 7.限制返回行数,8.4.2 SQL之数据库的创建与管理,实验任务2：phpMyAdmin数据库综合练习,课堂小结,理解MySQL数据库的创建与管理、数据类型及数据表的创建与管理。 掌握数据添加、修改、删除和查询等SQL操作。 掌握phpMyAdmin数据管理平台和Navicat for MySQL 的基本使用方法。,