1、PHP+MySQL动态网页技术教程,第4章 商城数据库的创建管理,4.1项目导引：商城数据库,我们思考几个问题，图书商城中的图书信息存放在什么地方？用户的注册账户信息和提交订单时填写的邮寄地址等内容又到哪里去了？这些内容又以什么形式存放呢？以上内容按照一定的格式存放在数据库（Data Base）中，数据库就是存放数据的仓库，其可以快速、安全的存储、处理大量的数据。现在的网站几乎都是基于数据库的，使用PHP开发网站，也离不开数据库，PHP可以与MySQL、ACCESS、SQL Server、ORACAL等多种数据库组合使用。在这些数据库中，MySQL是世界上最为流行、开放源码、完全网络化、跨平台
2、的数据库，能够满足多数中小型企业的需求，绝大多数PHP网站采用MySQL作为网站的数据库。本章将使用MySQL完成图书商城数据库的创建，为开发图书商城网站提供数据存储保证。,4.2项目分析:Ecshop系统的数据库,商城中的数据存放在数据库中，那么数据在数据库中又以什么形式存储呢？ 下面，我们先看一看Ecshop系统的数据库，安装好Ecshop之后（WampServer环境下），在浏览器中输入http:/localhost/phpmyadmin/，打开MySQL的管理工具phpMyAdmin，从中选择Ecshop数据库，即出现如图41所示页面。在页面的左侧有许多文件，我们称它为“表”，此数据库
3、中共有87张表，MySQL数据库就是通过表来组织管理数据。,图4-1 Ecshop数据库中的表,4.2项目分析,任意单击一张表（如商品表ecs_goods），就会在此页面的右侧显示出这张表的内容：表的顶部是每项内容的标题（如goods_id、goods_name），我们称之为表的字段名；在标题栏的下面有很多行，每一行代表一个具体产品，我们称之为表的记录，如图42所示。数据就是以这种形式存储在数据库中。,图4-2 Ecshop表的内容,4.2项目分析,在图4-2所示页面顶部单击“结构”按钮，查看商品表的具体结构，如图43所示。表的结构主要包括：字段名称、字段的数据类型、字段的整理编码方式、字段的
4、属性等内容。,图4-3 Ecshop表的具体结构,4.3技术准备,1.数据表一个数据库中可以有多个数据表，每个数据表的名称必须是唯一的，表中每个字段的名称也必须是唯一的，每个字段都有对应的数据类型和取值范围。二维表中能唯一区分、确定不同记录的属性或属性组合，称为该表的主键。主键具有唯一性和非空性。例如：图书编号为图书表的主键，用户编号为用户表的主键。在图书类别表中，typeid（类别编号）字段为主键，在图书表中也有typeid（类别编号）字段，并且与图书类别表中的typeid（类别编号）字段是对应关系。这里我们把typeid（类别编号）字段称为图书类别表的主键，图书表的外键。,4.3技术准备,
5、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到92233720368547
6、75807，无符号数字的范围是0到18446744073709551615。,4.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是小数位数
7、。DECIMAL(M,D)：一个未压缩(unpack)的浮点数字，“未压缩”意味着数字作为一个字符串被存储，值的每一位使用一个字符。小数点，并且对于负数，“-”符号不在M中计算。如果D是0，值将没有小数点或小数部分。DECIMAL值的最大范围与DOUBLE相同，但是对一个给定的DECIMAL列，实际的范围可以通过M和D的选择被限制。如果D被省略，它被设置为0。如果M被省掉，它被设置为10。,4.3技术准备,（2）日期和时间类型DATE：日期型，占3个字节，支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许使用字符串或数字把值赋
8、给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位数字格
9、式的年(缺省是4位)，占1个字节，允许的值是1901到2155，如果使用2位，1970-2069(70-69)。MySQL以YYYY格式来显示YEAR值，但是允许把使用字符串或数字值赋给YEAR列。,4.3技术准备,（3）字符串（字符）类型CHAR(M)：定长字符串，当存储时，总是用空格填满右边到指定的长度。M的范围是0255个字符。 VARCHAR(M)：变长字符串，当值被存储时，尾部的空格被删除。M的范围是0255个字符。 BLOB：二进制对象，可以保存图片、声音等二进制数据。BLOB类型根据其容纳值的长度不同，分为TINYBLOB（最大长度255字节）、BLOB（最大长度65535字节）
10、、MEDIUMBLOB（最大长度16777215字节）和LONGBLOB（最大长度4GB字节）类型。 TEXT：文本，用来保存字符数据。TEXT类型根据其容纳值的长度不同，分为TINYTEXT（最大长度255字节）、TEXT（最大长度65535字节）、MEDIUMTEXT（最大长度16777215字节）和LONGTEXT（最大长度4GB字节）类型。 ENUM(value1,value2,.)：枚举，一个仅有一个值的字符串对象，这个值选自值列表value1、value2， .，或NULL。值列表最多能有65535不同的值。 SET(value1,value2,.) ：集合，有零个或多个值的一个字
11、符串对象，其中每一个必须从值列表value1、 value2、.选出。一个SET最多能有64个成员。,4.3技术准备,3.商城数据库设计依据“天天书屋”商城的用户需求分析和功能结构分析，商城主要包含tb_user（用户表）、tb_book（图书表）、tb_type（图书类别表）、tb_order（订单表）和tb_admin（系统管理员表）。,4.3技术准备,4.4项目实施,通过前面的学习我们知道商城数据库都有哪些表组成了，那么我们该如何创建数据库呢？对MySQL数据库的创建管理主要包括两种方式，一种是通过图形管理工具创建管理数据库，第二种是通过MySQL的客户端程序创建管理数据库，客户端程序的
12、管理是通过SQL语句来实现。 4.4.1 phpMyAdmin之商城数据库创建管理MySQL常用的图形化管理工具有phpMyAdmin、MySQLDumper、Navicat、MySQL GUI Tools、MySQL ODBC Connector等，这些工具需要安装之后才能使用。下面以phpMyAdmin为例，来创建和管理图书商城数据库。phpMyAdmin是一个用PHP开发的基于Web方式的MySQL管理工具，它的官方网站是http:/。PHP集成开发环境WampServer中包含有phpMyAdmin组件，安装好WampServer后，单击WampServer图标 ，执行phpMyAdm
13、in，即可启动该工具，其主界面如图44所示。,4.4项目实施,图44 phpMyAdmin主界面,4.4项目实施,1.创建数据库在主界面中可以创建数据库，创建数据库需要指定数据库的名字和编码方式，“天天书屋”商城系统数据库名称为db_shop，采用GB2312的编码方式，如图45所示。单击“创建”按钮完成数据库的创建。,图45 创建数据库,4.4项目实施,2.创建数据表数据库创建后，进入创建数据表的页面，如图46所示。在此，输入数据表的名称和字段数量，例如输入表名为“tb_type”，字段数量为“3”。,图46 创建数据表,4.4项目实施,单击“执行”按钮，进入创建数据表字段的界面，如图47所
14、示。在此可以设置字段的字段名称、数据类型、长度值、默认值、整理编码方式、属性、是否为空(Null)、索引和自动增长（A_I）等内容。设置完毕后，单击“保存”按钮，完成数据表结构的创建。,图47 设置表字段,4.4项目实施,数据表创建成功后，进入数据表“结构”页面，如图48所示，在这里可以修改表结构，如添加字段、删除字段、设置主键、索引、修改字段名称等。单击导航中“删除”按钮，可以将数据表删除。,图48 管理数据表,4.4项目实施,3.添加数据在数据表页面，单击“插入”按钮，进入添加数据页面，如图49所示，输入“值”后，单击“执行”按钮，即可将数据添加到数据表中。,图49 添加数据,4.4项目实
15、施,4运行SQL语句在数据表页面，单击“插入”按钮，进入添加数据页面，如图49所示，输入“值”后，单击“执行”按钮，即可将数据添加到数据表中。,图410 SQL语句执行界面,4.4项目实施,5.数据库的备份对于数据库的管理经常需要备份和还原，在phpMyAdmin中我们通过导出、导入方式备份还原数据库。选择db_shop数据，单击导航栏中的“导出”按钮，进入导出界面，如图411所示。在此，我们可以选择导出的数据表，选择导出数据表的具体内容和导出的保存形式，在页面下方指定保存的文件名，最后，将SQL文件保存到磁盘上，完成数据备份。,图411数据导出界面,4.4项目实施,6数据库的还原单击导航栏中
16、的“Import”按钮，进入SQL文件导入界面，如图412所示。单击“浏览”按钮，选择需要导入的SQL脚本文件，然后单击“执行”按钮，系统将执行SQL文件中的SQL命令，完成数据还原。 数据库创建好之后，会保存在MySQL安装路径下的data文件中，每一个数据库对应一个文件夹，通过文件夹操作也可以实现数据库的备份还原。掌握phpMyAdmin的基本操作后，使用phpMyAdmin完成商城数据库及数据表的创建。,图412数据导入部分界面,4.4项目实施,4.4.2 SQL之数据库的创建与管理MySQL是基于客户机/服务器结构的数据库管理系统，通过客户机连接服务器成功后，再通过必要的操作指令对其进
17、行操作，这种数据库操作指令被称为SQL（Structured Query Language）语言，即结构化查询语言。SQL语言结构简洁，功能强大，自IBM公司1981年推出以来，SQL语言得到了广泛的应用，目前MySQL、Oracle、SQLServer、Sybase、DB2等数据库都采用SQL作为查询语言。SQL语言包含以下四部分：数据定义语言（DDL）：用于定义和管理数据库对象，包括数据库、数据表、索引、视图等。例如：create、drop、alter等语句； 数据操作语言（DML）：用于操作数据库对象中所包含的具体数据。例如：insert、update、delete语句；数据查询语言(D
18、QL):用于查询数据库对象中的所包含的数据。例如：select语句；数据控制语言（DCL）：用来管理数据库的语言，包含管理权限及数据更改。例如：grant、revoke、commit、rollback等语句。,4.4项目实施,1登录MySQL服务器左键单击WampServer 图标，选择“MySQL控制台”，进入MySQL客户端，如图所示。由于WampServer安装时没有设置用户密码，直接回车即可，当提示符变为“mysql”时，就代表以root用户身份登录到MySQL服务器。,4.4项目实施,2创建数据库创建数据库是创建其它数据库对象的基础，其语法格式如下： CREATE DATABASE
19、db_name;其中： db_name是要创建的数据库名称； “;”是SQL语句的默认结束标志。 【例4-1】创建名称为db_shop的数据库，命令如下所示：,4.4项目实施,3查看数据库查看当前服务器中数据库的语法格式如下：SHOW DATABASES LIKE wild;其中： LIKE wild是可选项，用来指定显示模式匹配的数据库； wild是一个字符串，它可以包含SQL通配符“_”（匹配单个字符）和“%”（匹配任意数目字符）。 【例4-2】显示当前服务器中名称前两个字符是db的数据库，命令如下所示：,4.4项目实施,4.指定当前数据库服务器中可能会存有多个数据库，如果使用某个数据库，
20、需要将其指定为当前数据库，其语法格式如下： USE db_name; 【例4-3】将db_shop数据库设置成为当前数据库，命令如下所示：,4.4项目实施,5删除数据库删除数据库的语法格式如下：DROP DATABASE db_name;删除命令会删除数据库中的所有表和数据，需要小心地使用该命令！ 4.4.3 SQL之数据表的创建与管理 1.创建数据表创建数据库后，需要为数据库创建表来保存数据。创建表的基本 语法格式如下：CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name (col_name type NOT NULL | NULL DEFAULT de
21、fault_value AUTO_INCREMENTPRIMARY KEY) 其中：,4.4项目实施,TEMPORARY：用来创建临时表； IF NOT EXISTS：用来判断准备新建的表是否存在，如果不存在才创建该表，从而避免出现表已经存在无法新建的错误； col_name type：列名及其数据类型； NOT NULL | NULL：指定列是否为空，默认为空（NULL）； DEFAULT default_value：为列指定默认值，默认值必须是常量； AUTO_INCREMENT：指定列为自动编号，该列必需是整数类型； PRIMARY KEY：指定列为主键，主键列不能为空。 【例4-4】在
22、db_shop数据库中创建tb_type（图书分类）表。,图414 创建tb_type表,4.4项目实施,【例4-5】在db_shop数据库中我们再创建一个图书表（tb_book），创建代码如下所示： CREATE TABLE tb_book (bookid int(11) NOT NULL auto_increment,typeid int(11) NOT NULL,isbn int(11) NOT NULL,bookname varchar(50) NOT NULL,author varchar(30) NOT NULL,pubhouse varchar(30) NOT NULL,pubd
23、ate varchar(12) NOT NULL,bookprice float NOT NULL,vipprice float NOT NULL,photo varchar(100) NOT NULL,introduction varchar(1000) NOT NULL,recommend tinyint(1) NOT NULL,newbook tinyint(1) NOT NULL,PRIMARY KEY (bookid) ) ENGINE=MyISAM DEFAULT CHARSET=gb2312 ;,4.4项目实施,2查看数据库表及列信息 查看当前服务器中数据库表清单的语法格式如下：
24、 SHOW TABLES LIKE wild; 查看数据表的列信息语法格式如下： SHOW COLUMNS FROM tbl_name FROM db_name LIKE wild; 【例4-6】查看db_shop数据库中tb_type表的列信息。 在命令提示符下输入以下语句： SHOW COLUMNS FROM tb_type; 运行结果如图415所示：,图415 tb_type表列信息,4.4项目实施,3.修改表 数据表创建后，可以使用ALTER TABLE语句对表结构进行修改，下面列举几个修改命令： 增加列：ALTER TABLE tbl_name ADD col_name TYPE;
25、删除列：ALTER TABLE tbl_name DROP col_name; 改变列：ALTER TABLE tbl_name MODIFY col_name TYPE; 更名列：ALTER TABLE tbl_name CHANGE old_col_name col_name; 更改表名：ALTER TABLE tbl_name RENAME new_ tbl_name;,4.4项目实施,4.删除表当数据库中不需要某些表时，可以将其删除。删除数据表的语法格式如下：DROP TABLE IF EXISTS tbl_name ,tbl_name,.删除数据表时，表中的数据和表定义同时被删除，应
26、该小心使用删除命令。 5.添加数据数据库用表存储和管理数据。新表建好后，表中并不包含任何数据记录，要想实现数据的存储必须向表中添加数据。向表中添加数据，可以使用INSERT语句实现。添加的数据中如果有汉字，一定要注意创建数据表时的字符编码，如字符编码为gb2312，则在添加数据时，需要先设置编码。 （1）设置编码mysql客户端默认的字符集是utf8，如果修改该字符集为gb2312，可以使用“SET NAMES gb2312”命令，其运行结果如图416所示：,图416设置gb2312编码,4.4项目实施,（2） 添加记录向表中添加一条或多条数据记录，使用INSERT.VALUES语句实现，其语
27、法格式如下： 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_defini
28、tion 需要注意的是，当没有WHERE子句指定修改条件时，则表中所有记录的指定列都被修改。 【例4-8】将tb_type表中类别名称为“教育类”的名字修改为“高职教育类”。,4.4项目实施,7.删除数据将数据表中数据删除的语句是DELETE，其语法格式为： DELETE FROM tbl_name WHERE where_definition 需要注意的是，当没有WHERE子句指定删除条件时，则删除表中所有记录。 【例4-9】删除tb_type 表中类别名称为“高职教育类”的记录。 4.4.4 SQL之数据库的查询管理数据查询是数据库的核心操作，用户可以通过查询获得所需要的数据。查询操作可以
29、通过SELECT语句实现，该语句在执行时会根据要求从一个或多个数据表中选取特定的行和列，形成一个临时表传送给用户。其中： select_expression：描述结果集的列，列与列之间用逗号分隔； FROM table_list：用于指定产生查询结果集的数据来源的表或视图的名称； WHERE where_definition：用于指定所检索的数据应该满足的条件； GROUP BY col_name：用于分组统计时指定分组的条件；,4.4项目实施,HAVING where_definition：与GROUP BY子句一起使用，用于对分组统计的结果设置条件，进行组数据选择； ORDER BY so
30、rting_columns ASC|DESC：指定在SELECT语句返回的列中所使用的排序顺序；ASC和DESC用于指定行是按升序还是按降序排列； LIMIToffset, rows:用来限制SELECT语句返回的行数。 需要注意，SELECT语句中的子句必须按以上语法格式的顺序给出，例如，HAVING子句必须在GROUP BY子句之后，ORDER BY子句之前。 1基本查询以下查询均在db_shop数据库中完成。 （1）选择指定的列数据表中通常存有多列信息，如果只需要查询部分列信息时，可以把需要的列名按照用户要求依次列在SELECT语句后面即可，字段与字段之间用英文半角逗号隔开；如果需要查询
31、所有信息，可以使用“*”号代替表中所有的字段。 【例4-10】查询tb_book表中图书的名称（bookname）及其作者名称（author）。,4.4项目实施,（2）指定列别名在缺省情况下，查询结果中的列标题是表中的列名或者无列标题。如果希望对列标题进行修改，或者为没有标题的列加上标题时，可以使用AS子句改变查询结果中的列标题。其语法格式为： SELECT column_name AS column_alias 【例4-11】查询tb_book表中图书的名称（bookname）及其作者名称（author），结果中的标题显示为图书名称和作者。 2条件查询数据表中可以存储成千上万条记录，一般情况
32、下，我们查询时并不需要将数据表中所有数据都显示出来，而是有目的的查询数据。为了在数据表中查找满足条件的记录，需要使用WHERE子句，通过使用它指定一系列查询条件来保证查询结果集中只包含所需的记录。条件查询的语法格式如下： SELECT select_expression,.FROM table_list WHERE where_definition 在使用WHERE子句时，它必须紧跟在FROM子句后面，其中where_definition条件是一个表达式，其常用运算符如下：,4.4项目实施,关系比较：=、=、= 范围比较：BETWEEN AND（在某个范围内）、NOT BETWEEN AND（
33、不在某个范围内） 列表比较：IN（在某个列表内）、NOT IN（不在某个列表中） 字符串模式匹配：LIKE（标准的SQL模式匹配）、REGEXP（扩展的正则表达式模式匹配） 多重条件：AND（&）、OR（|）、NOT（！） （1）关系比较查询 【例4-12】查询tb_book表中图书价格高于36元的图书名称（bookname）及其作者名称（author）。 （2）范围比较查询当要查询的条件是某个范围时，使用BETWEEN关键字指出查询范围，其语法格式如下： expression NOT BETWEEN expression1 AND expression2 其中，expression1是范围的
34、下限值，expression2是范围的上限值；使用BETWEEN时，数据范围包含边界值，使用NOT BETWEEN时，数据范围不包含边界值。 【例4-13】查询tb_book表中图书价格在36元至48元的图书名称（bookname）及其作者名称（author）。,4.4项目实施,（3）列表比较查询当要查询的条件属于某一列表值之一时，使用IN关键字指出查询列表，其语法格式如下： expression NOT IN （expression1，expression2，expression3，） 【例4-14】查询tb_book表中作者为“杨学全”、“李英杰”、“龙应台”的图书名称（bookname）
35、及其作者名称（author）。 （4）字符串模式匹配查询字符串模式匹配查询，又称为模糊查询，是在查询条件中使用字符串匹配符号LIKE或REGEXP把表达式与含有通配符的字符串进行比较，实现对字符串的模糊查询。 使用 LIKE运算符的标准的SQL模式匹配查询LIKE是MySQL提供的标准的SQL模式匹配中用到的运算符，它通常和“_”（匹配单个字符）、“%”（匹配任意数目字符）通配符搭配使用进行模糊查询，其语法格式如下： expression NOT LIKE match_ expression 【例4-15】查询tb_book表中图书名称中含“VB”内容的图书名称（bookname）及其作者（a
36、uthor）。,4.4项目实施,使用REGEXP运算符的扩展的正则表达式模式匹配查询 MySQL除了使用标准的SQL模式进行简单的模糊查询外，还可以使用正则表达式完成复杂的模糊查询。当使用这类模式进行匹配查询时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们作用相同)，其语法格式如下： expression NOT REGEXP|RLIKE match_ expression 扩展正则表达式常用的一些匹配字符有： ：匹配字符串的开始部分； $：匹配字符串的结尾部分； .：匹配任何单个的字符； *：匹配零个或多个星号前面的字符； +：匹配1个或多个加号前面的
37、字符； |：匹配符号左边或右边出现的字符串； n：匹配括号前的内容出现n次； （）：匹配括号里面的内容； .：匹配在方括号内的任何字符；例如，“abc”匹配“a”、“b”或“c”。为了命名字符的一个范围，使用一个“-”。“a-z”匹配任何小写字母，而“0-9”匹配任何数字。,4.4项目实施,【例4-16】查询tb_book表中图书名称中含“VB”或“SQL”内容的图书名称（bookname）及其作者（author）。 （5）多重条件查询多重条件查询是指查询条件有多个，需要使用逻辑运算符进行条件连接的查询，常用的逻辑运算符有NOT、AND、OR。 【例4-17】查询tb_book表中图书名称中含
38、“VB”内容并且作者姓“李”的图书名称（bookname）及其作者（author）。 3排序查询在执行查询操作时，默认情况下查询结果以数据存储在表中的顺序显示。为了有助于数据的浏览和查阅，在实际应用中经常需要对数据进行排序。对查询结果排序的子句是ORDER BY，语法格式如下： ORDER BY sorting_columns ASC | DESC,. 【例4-18】按价格降序显示tb_book表中图书名称（bookname）和价格（bookprice）。,4.4项目实施,4统计查询在实际应用中，经常会对查询结果进行分类、统计和汇总等操作，这些操作可以通过聚合函数、GROUP BY子句等来实现
39、。常用的聚合函数有AVG()、COUNT()、MAX()、MIN()、SUM()等。 （1）使用聚合函数 【例4-19】统计tb_book表中图书名称中含“VB”内容的图书的种数、平均价格、最高价格和最低价格。 （2）使用GROUP BY子句单独使用聚合函数只能返回单一的汇总结果，如果需要显示分组统计数据，就需要使用GROUP BY子句的简单形式。该子句和聚合函数一起使用可以实现对数据的分组统计，在查询结果中，每一组将统计出一个结果。 【例4-20】统计显示tb_book表中每个出版社图书的图书种数。 （3）使用HAVING子句限制分组结果HAVING与WHERE子句都是搜索条件语句，其区别在
40、于WHERE搜索条件在进行分组操作之前应用，跟在FROM子句之后；而HAVING搜索条件在进行分组操作之后应用，跟在GROUP BY子句之后。 【例4-21】统计显示tb_book表中图书种数大于3的出版社的名称及图书种数。,4.4项目实施,5.连接查询在数据库应用中，经常需要从两个或多个表中查询数据，这时，就需要使用连接查询。连接查询主要包含内连接和外连接。 （1）内连接使用内连接时，如果连接表中的相关字段满足连接条件，则从连接表中提取数据并组合成新的记录作为结果集，其语法格式如下： SELECT select_expression FROM table_reference1 INNER J
41、OIN table_reference2 ON conditional_expr 【例4-22】连接tb_type表和tb_book表，查询图书的分类情况，查询结果包含图书的分类号（typeid）、分类名称（typename）及图书名称（bookname）。 （2）外连接在内连接中，只有满足条件的记录才能在结果集里输出。而外连接扩展了内连接的结果，除返回所有满足条件的记录外，还会返回一部分或全部不满足条件的记录。外连接分为左外连接、右外连接和全外连接，其语法格式如下：,4.4项目实施,其中： LEFT：指左外连接，结果集中除了满足连接条件的记录外，还有左表中不满足连接条件的记录，该记录对应的右
42、表列上自动填充NULL值。 RIGHT：指右外连接，结果集中除了满足连接条件的记录外，还有右表中不满足连接条件的记录，该记录对应的左表列上自动填充NULL值。 FULL：指全外连接，结果集中除了满足连接条件的记录外，还有左、右表中不满足连接条件的记录，在左、右表的相应列上填充NULL值。 【例4-23】用tb_type表左外连接tb_book表，查询图书的分类情况，查询结果包含图书的分类号（typeid）、分类名称（typename）及图书名称（bookname）。,4.4项目实施,6.子查询在查询语句中，可以将一条查询语句嵌套在另外一条查询语句中作为条件的一部分使用，被嵌套的查询语句称为子查
43、询，嵌套子查询的查询语句称为父查询。嵌套查询的求解方法是由里向外，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立父查询的查找条件。子查询通常与IN或者比较运算符结合使用，当子查询的结果为单值时，使用比较运算符，结果为多值时，使用IN运算符。 【例4-24】使用子查询显示“计算机类”图书信息。 7限制返回行数使用LIMIT子句可以限制查询语句返回的记录数，语法如下： LIMIT offset, rows 其中： offset用来指定要返回的第一行的偏移量，其初始行的偏移量是0； rows用来指定返回行的行数。 例如，“LIMIT 2”表示返回结果集中前2条记录；“LIMIT 1，5”
44、表示从第2行开始返回5条记录，即返回第26条记录。 【例4-25】查询显示tb_book表中第2和第3条记录。,4.5技术拓展,4.5.1 数据完整性数据完整性是指存储在数据库中的数据正确无误并且相关数据具有一致性。数据完整性可以由约束来实现，约束是数据库系统提供的自动强制数据完整性的机制，它通过定义表中列的取值规则来维护数据的完整性。约束可以在创建数据表时创建，也可以在已存在的表上添加。在MySQL数据库中，可以创建主键约束、唯一约束、非空约束、默认约束、外键约束等。 1主键约束主键约束是最常用的约束，一般的数据表中都应该有一个主键约束。主键约束是将表中的一列或多列定义成一个主键来唯一标识表
45、中的每行记录，其有如下特点：每个表中只能有一个主键，主键可以由一列组成，也可以由多列的组合而成；主键值必须唯一并且不能为空，对于多列组合的主键，某列值可以重复，但列的组合值必须唯一。例如在图书表中可以将图书编号设置为主键，用来保证表中的图书记录具有唯一性。在项目实施中，每个表都创建了主键，创建数据表的同时创建主键约束的代码参考项目实施代码即可，下面介绍修改主键的方法。,4.5技术拓展,添加主键约束的语法格式如下：ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,.) 删除主键约束的法格式如下： ALTER TABLE tbl_name D
46、ROP PRIMARY KEY 【例4-26】将tb_type表中typeid设置为主键。 2.唯一约束唯一约束用来限制表中非主键列中不允许输入重复值。唯一约束有如下特点：一个表中可以定义多个唯一约束；每个唯一约束可以定义到一列上，也可以定义到多列上；空值可以出现在某列中一次。例如在图书类别表中可以将类别编码作为主键，用来保证记录的唯一性。如果不允许有同名类别存在，应该为类别名称列定义唯一约束，保证非主键列中不出现重复值。添加唯一约束语法格式如下： ALTER TABLE tbl_name ADD UNIQUE (index_col_name,.) 删除唯一约束语法格式如下： ALTER TA
47、BLE tbl_name DROP INDEX index_col_name 【例4-27】为tb_type表中TypeName字段设置唯一约束。,4.5技术拓展,3.非空约束非空约束用于设定某列值不能为空来实现数据完整性。如果指定某列不能为空，则在进行添加记录时，此列必须添加数据。例如对于管理员表，有一个管理员，就必须有相应的密码，这时，就应该设置密码列不能空。非空约束使用NOT NULL选项实现，如下面创建管理员表（tb_admin）的代码中，用户名（tb_admin）和密码（password）字段都设置了非空约束。在此代码中，id int(11) NOT NULL AUTO_INCREM
48、ENT的作用是为表创建自增量字段id，自增量字段必须是数值类型，且其值不能重复，其作用是每增加一条记录，该字段的值就自动加1。 4.默认约束默认约束用来为表中某列建立一个默认值，当用户添加记录时，如果没有为该列提供输入值，则系统会自动将默认值赋给该列。使用默认约束可以提高输入记录的速度。添加默认约束的语法格式为： ALTER TABLE tbl_name ALTER COLUMN columnname SET DEFAULT default values; 【例4-28】为图书信息表（tb_book）中出版社字段（pubhouse）添加默认值“东软电子出版社”。,4.5技术拓展,5外键约束外键
49、是指一个表中的一列或列组合，这些列可以引用同一个表或其他表的主键约束列或唯一约束列。通过外键约束可以为相关联的两个表建立联系，实现数据的参照完整性，维护两表之间数据的一致性关系。外键的取值要依赖于它引用的主键值或唯一约束字段的值，也就是引用表中主键或唯一约束列存在什么值，外键才可以使用什么值。例如，如果要求tb_book中“TypeID”列的取值，必须是tb_type表中“TypeID”列的列值之一，这就应该在tb_book表的“TypeID”上创建外键约束，使tb_book表和tb_type表中的图书类别号具有一致性，从而防止tb_book表中出现错误的图书类别号。添加外键约束的语法格式如下： ALTER TABLE tbl_name ADD CONSTRAINT symbol FOREIGN KEY (col_name,.) REFERENCES tbl_name(col_name,.) 【例4-29】为tb_book表中TypeID字段添加外键约束，从而保证在tb_book表中输入TypeID 值有效，即与tb_type表中的图书类型号（TypeIID）具有一致性。,
