身份证验证数据库教程

身份证验证数据库教程（精选6篇）

1.身份证验证数据库教程 篇一

美图秀秀特别制作的卡通身份证应用强势登陆，这是一个在网页输入字符便能生成卡通形象并且加入标注的应用，自动生成的卡通身份证各式各样，趣味多多，绝对是大家在节日恶搞朋友的必备良品。

下面让我们一起来看看美图秀秀如何制作卡通身份证吧!

首先登录美图秀秀卡通身份证官方网页2.app.meitu.com/，我们会看到如下页面:

然后在下图标注1的框里填入你要生成的名字;

接着点击下图标注2的生成卡通形象按钮;

稍等一会就会在下面看到已经生成的卡通形象了，

大家有兴趣的话不妨试试，开心一笑吧!

2.身份证验证数据库教程 篇二

二代身份证是我国16岁以上成年人使用最为频繁的有效证件,携带持有者的惟一身份证编号、人脸相片和个人基本信息。由于二代证采用了非接触式IC卡技术,即通过二代证阅读器就能读取芯片内贮存的信息,因此基于二代证中相片的人脸识别,成为近年来兴起的一种人脸识别新应用,具有重要的研究价值和应用前景。其应用又可以分为两类:一类为身份查询系统,即事先通过二代证相片建立数据库,然后将现场采集的人脸图像与数据库进行比对,得到最为相近的结果,解决的是“这是谁”问题;另一类是身份验证系统,即分别提取二代证相片与现场采集人脸图像的特征,通过比对判定是否为同一人,解决的是“这是不是他”问题。论文所研究内容显然属于后者。

基于二代证的身份验证系统可以通过将现场实时采集的持卡人人脸图像与读卡器所提取的二代证中相片进行比对,从而完成对使用者是否为合法持有人的验证。系统可用于车站安检、金融事务办理及高考身份查验等多种场合,旨在取代传统的人工验证方式,提高验证效率和准确度。

1系统总体架构概述

验证系统的总体框架结构如图1所示,首先分别对摄像头采集的人脸图像与二代证阅读器读取的相片作图像处理和特征提取,然后通过求取特征间的相似度进行验证比对,最后得到验证结果。针对相貌变化过大导致合法持证人无法通过验证的情况,可通过手动更新本地数据库来解决。

1.1 二代证相片

经二代证阅读器读取的二代证相片为小波变换后的压缩存储图像,尺寸为102像素×126像素,如图2所示。从图例中不难看出,二代证相片图像质量低,存在较为明显的锯齿状边缘。

1.2 手动数据库更新

理论上讲,系统仅需将现场采集的人脸图像与二代证相片图像比对即可得到验证结果。但由于年龄跨度等因素的影响,相貌的变化会导致合法持证人无法通过验证。对于此类情况,本文通过MSSQL建立一个数据库,专门用于存储无法通过验证的合法持证人的身份证号及其现场采集的人脸图像。在下次验证时,如果库中存有相应的身份证号就直接将数据库中的图像与现场采集的图像做比对。利用这种手动更新数据的方法,可以有效提高二次验证的准确率。

2 系统关键技术研究

系统按不同的模块可划分为4部分:人脸图像预处理、特征提取、特征降维及相似度计算。下文将分别就这些模块中用到的关键技术做一详细介绍。

2.1 基于ASM的人脸图像预处理

主动形状模型[1](Active shape model,ASM)是建立在点分布模型(Point distribution model,PDM)的基础上,使用统计学方法通过一系列坐标点来定义某类目标的形状。ASM算法主要分为形状模型的建立、局部灰度模型的建立和目标搜索3个过程,首先对一组标有特征点图像的形状和局部灰度建立模型,然后在搜索过程中,通过调节形状和姿态参数使形状达到最优。

基于ASM的人脸图像预处理基本思路是先使用ASM方法对人脸特征点进行匹配。在匹配结果中,将得到的双眼中心点经过仿射变换对齐到固定位置,从而实现人脸的几何归一化,论文所用归一化大小为80×80。

2.2 光照预处理

光照变化通常是影响人脸识别率的一个重要因素。为降低光照对系统验证准确率的影响,提升系统鲁棒性,文中光照预处理的步骤为:(1) 通过高斯差分(DOG)对图像滤波;(2) 用直方图规整化对图像灰度属性进行校正。

高斯差分滤波相当于一个带通滤波器,分别使用两个不同大小的高斯模板对图像进行滤波,求取两者间的差值就能得到对图像进行高斯差分滤波的结果。文献[2]中指出该方法在极端光照情况下的表现尤为显著。图3所示是分别对二代证相片和现场采集图像做几何归一化和光照预处理的结果。在本系统中,设定的两个高斯模板方差分别为1和0.4,可以看到论文所用高斯差分滤波在去除光照影响的同时较好地保留了人脸细节。

2.3 LGBP特征提取

2.3.1 人脸图像的Gabor特征表示

二维Gabor函数可以精确逼近哺乳动物的视觉皮层细胞感受野,很大程度上反映人脸的不变信息,对光照姿态等具有一定的鲁棒性,同时具有良好的时频局部化特性。二维Gabor核函数定义为:

undefined

式中,z=(x,y);‖·‖表示范数运算;ku,v=kvexp(iφu),kv=kmax/fv,φu=πu/8,不同的u和v分别对应Gabor滤波器不同的方向和频率大小。将人脸图像和Gabor滤波器相卷积即可得到人脸图像的Gabor特征表示。

2.3.2 局部Gabor二值模式(LGBP)

局部二值模式(LBP)是由Ojala[3]提出的一种描述图像局部区域纹理变化的算子,其基本计算原理如下:对图像每个像素(xc,yc)的8邻域采样,每个采样点(xp,yp)的灰度值f(xp,yp),p=0,1,…,7与中心像素(xc,yc)的灰度值f(xc,yc)做二值化计算随后,通过对每个采样点赋予不同的权系数2p得到该中心像素(xc,yc)的LBP值。

文献[4]首次将Gabor特征与LBP算子相结合,首先利用多尺度和多方向的Gabor滤波器将人脸图像分解为多个Gabor幅值图,然后在Gabor幅值图的基础上利用LBP算子进行表征,这样的特征表示方式称为局部Gabor二值模式(LGBP)。LGBP 特征既能体现出LBP算子对人脸图像局部纹理变化敏感的优点,又具备了Gabor滤波器对人脸图像尺度变化、平移以及形变不敏感的优势。文中的Gabor幅值图由5个尺度和8个方向滤波得到,LBP算子采用的含不变模板(Uniform patterns)的改进LBP算子。

2.4 加权EPFDA与相似度计算

LGBP特征的优势很显著,但也存在很明显的不足,以论文80×80的归一化人脸图像来说,如果按8×8的小块将人脸分为100个互不重叠的区域,那么其直方图向量的维数将达到151040维,因此必须对其进行数据降维。论文采用了加权分段线性判别分析集成[5](Weighted EPFDA)算法。

线性判别分析(FDA)是通过寻找最优投影方向,使得属于不同类别的训练样本在该方向上的投影能被最好的分开,其缺点在于仅考虑了在全局坐标系统内的线性变换。而人脸图像存在全局与局部变化,往往不能被其很好的表示。但是可以通过局部线性来拟合全局非线性。因此EPFDA算法首先将高维直方图分为多段,每段可近似认为是线性的,随后对每段做线性判别分析,最后对分段的局部LGBP线性判别分析集成,即可得到降维后的人脸特征向量。具体的区域划分和空间直方图提取如图4所示。

在这基础上得到的相似度计算步骤如下:

(1) 将人脸图像分为M×N个互不重叠的子区域,首先求取人脸图像的LGBP直方图序列描述V:

V=(H1,H2,…,HM×N) (2)

式中为每个子区域内的LGBP直方图。

(2) 对每个通过训练可以得到相对应的投影矩阵Wundefined,由此可以得到在判别空间内的低维表示Fi:

Fi=(Wundefined)THi (3)

则人脸图像V的低维空间F可以表示为

F=(F1,F2,…,FM×N) (4)

(3) 现场采集人脸图像V与二代证相片图像V′的相似度通过先对每个降维后子区域求取余弦相似度再求和得到

undefined(5)

(4) 考虑到人脸不同区域具有不同的判别能力,采用类似于文献[6]的方法对不同人脸区域赋予不同权值。不同区域的权值分布如图5所示,亮度越大的区域表示权值越大,相反则越小。则式(5)可以调整为

undefined(6)

式中ωi为不同区域的权值。

3 实验结果与系统演示

文中数据库是由402名志愿者的二代证相片和若干张现场采集人脸图像组成,随机选取其中100人作为测试集,其余作为训练集。最终得到的ROC曲线如图6所示。

可以看到,当系统的错误接受率(FAR)分别为1.05%,5.23%,10%时,相对应的正确接受率(TPR)分别为86.67%,95.21%和96.15%。由于实验环境并未对光照、表情等因素加以严格控制,因此这样的结果还是令人满意的。

图7所示为当二代证合法持卡人通过验证时的系统截图。当通过验证时,系统会给出人脸相似度;如果未能通过验证,系统会给出警报,并记录下当前的二代证信息与人脸图像。另外对系统运行效率也进行了相关测试,具体运行平台及性能参数如表1所示。

4 结束语

论文基于二代身份证相片提出一种结合人脸LGBP特征与EPFDA算法的身份验证系统,有效地解决了论文开头所提到的二代证相片图像质量低、年龄跨度大等难点。实际数据库的实验表明,在错误接受率为1.05%时,正确接受率可以达到86.67%;性能测试的结果也证实了系统的实时性和有效性。今后的工作将围绕如何提升二代证相片质量,扩大训练样本数提升系统泛化能力来展开,希望能进一步提高系统性能。

随着二代证使用的日益频繁,该系统可广泛用于大型活动、体育赛事、海关的安检及考勤等多种场合,具有很重要的实际应用价值。

摘要：文章探讨和研究一种基于二代身份证的人脸识别智能身份验证系统。该系统针对二代证相片本身存在的低像素,年龄跨度大等问题,提出一种将人脸局部盖波二值模式(Local gabor binarypattern,LGBP)特征与分段线性判别分析集成(EPFDA)相结合的方法,并通过求取余弦相似度作为验证度量,在实际采集的数据库中验证了该方法的有效性。

关键词：二代身份证,身份验证,人脸识别

参考文献

[1]Cootes T,Taylor C,Cooper D,et al.Active shape models-their training and application[J].Computer vision and im-age understanding,1995,61(1):38-59.

[2]Tan X,Triggs B.Enhanced local texture feature sets forface recognition under difficult lighting conditions[J].IEEEtrans image processing,2010,19(6):1635-1650.

[3]Ojala T,Pietikainen M,Maenpaa T.Multiresolution gray-scale and rotation invariant texture classification with localbinary patterns[J].IEEE trans pattern analysis and ma-chine intelligence,2002,24(7):971-987.

[4]Zhang W,Shan S,Gao W,et al.Local gabor binary patternhistogram sequence(LGBPHS):A novel non-statisticalmodel for face representation and recognition[C]//ProceedingsIEEE Intl conference computer vision.2005:786-791.

[5]张文超.局部Gabor二值模式人脸描述和识别[D].哈尔滨:哈尔滨工业大学,2007.

3.身份证验证数据库教程 篇三

Windows 2008启用网络身份验证功能

。

为了提高服务器系统的安全性能，Win2008系统新推出了网络身份验证功能，该功能可以很好地预防非法攻击者随意从局域网中的任意一台计算机对目标服务器系统建立远程连接，一旦启用了该功能后，网络管理员必须在Vista、Win2008系统环境下才能通过远程桌面功能与Win2008服务器建立远程连接，其他普通计算机系统都不能与Win2008服务器系统建立远程连接，这无形之中自然会提高Win2008服务器系统的运行安全性，

在启用Win2008服务器系统的网络身份验证功能时，我们可以按照下面的设置来操作：

首先以特权账号登录进入Win2008系统桌面，单击该系统任务栏中的“服务器管理器”功能按钮，打开对应系统的服务器管理器控制台窗口，选中该窗口左侧位置处的“服务器管理”节点选项，在目标节点选项下面的“服务器摘要”设置项处点选“配置远程桌面”选项，进入对应系统的远程桌面设置对话框;

4.身份证验证数据库教程 篇四

2012年即将结束，在岁末之际，我就一年以来的工作做一下认真的总结。总结自己在过去一年的得与失，总结自己明年该如何去做的更好。总体来说，本在部门领导的正确指导下，部门同事的相互配合下，工作顺利，成长较为迅速！

一、政治态度

我热爱祖国和中国共产党，忠于人民教育事业。自2005年到督导室工作以来，便全身心投入到督导工作当中，本人一直在各方面严格要求自己，努力提高自己，以使自己更快更好的适应高职教育发展形势。勇于分析自己，正视自己，不断提高自身素质。在加强业务知识学习的同时，踏踏实实做好本职工作，争取在自己的工作岗位上作出优异的成绩。

二、教育研究和本职工作

（一）教育研究

1、主持院课题《高职院校院系两级教学督导的研究与实践》，并于2011年11期《辽宁高职学报》发表论文《高职院校院系两级教学督导工作探析》。通过对该课题的资料收集及分析，使我对高职院校的教学督导模式有了更加清晰地认识。

2、通过督导室对我校独立实训情况进行重点检查以后，撰写了新闻稿《深入实习基地，了解我院学生独立实训情况》。通过此次检查，进一步了解我院学生的实习情况，加深与实习单位的联系，为提高我院学生综合素质提供建设性的意见和建议。

3、参与辽宁省教育科学“十二五”规划课题二项。

（二）本职工作

1、学生评教工作。

（1）参与学评教软件平台的完善与改进工作，使我院教学质量监控体系进一步完善。

（2）参与学生评教指标体系的研究与讨论，使学生评教指标体系更加客观与直观，使学评教结果更具信度与效度。

（3）完成本学生网上评教工作2次，对评教过程中出现的问题及时向部门领导汇报，并向教务处等有关部门进行咨询与沟通，找出合理的解决办法。

2、教学常规的检查。本在督导室主任的带领下，督导室全体成员加大了教学常规检查，并将每日检查结果及时整理、汇总反馈给本部门领导。

3、参加社会事业学院老年服务与管理专业举行的首届专业技能大赛。通过此次大赛，学生将理论知识与实践相结合，为学生职业技能的提高打下了坚实的基础。

4、教研活动的检查。

本在领导的高度重视下，加强各学院教研活动组织情况的检查，参加教研活动8次。各院部教研活动基本按照开学初制定的教研活动计划进行，教师能够认真参加每周举行的教研活动，并完成相应的教研内容。通过对教研活动的检查，对如何确定一个合理的教研活动主题及内容都有了深刻的认识。

5、课堂教学检查。本第一学期参与了我院36岁以下青年教师听课工作，共参与听课82次，并将教师“教学建议书”及时反馈各系部主任。

6、督导室公开课活动。参加督导室与南关岭校区和夏家河校区举行的公开课活动2次，负责活动的组织和总结工作。

7、撰写“督导简报”6次。对每周的教学常规检查、教研活动及课堂教学检查等情况进行总结并上报部门领导，是领导能够及时掌握我院教学质量的整体情况。

8、参与督导室工作程序和评教制度的撰写。使我更加清晰的理解督导室工作流程，明确评教制度在教学质量监控体系中的作用。

9、做好期末考试巡考和试卷批阅巡视工作。

10、做好督导室各项日常事务的管理，配合学院档案室的规定做好各项文件资料的整理与归档工作，做好部门的资料查询提供服务工作。

三、在实践中学习，不断提高自身工作能力

在今后的工作中，要对督导室的业务知识进行系统的学习，争取在最短的时间内完成最高质量的工作。对领导交给的各项工作始终以积极地工作态度、高度的责任感和只争朝夕的精神投入到工作中。

总结这一年来的工作与学习，我的工作仍然有不足之处，需要注重锻炼自己的应变能力、协调能力以及科研能力。我相信，只要我尽自己最大的努力，将自己所有的精力和能力用在工作上，相信自己一定能够做好！

5.MySQL教程数据库教程 篇五

■ 如何利用mysql客户机程序与MySQL通信。

■ SQL 语言的基本语句。(如果您曾经使用过其他RDBMS，从而熟悉SQL，那么浏览一下这个教程，看看SQL 的MySQL版与您熟悉的版本有何差别也是很好的。)正如上一节所述， MySQL采用客户机/服务器体系结构，其中服务器运行在存放数据库的机器上，而客户机通过网络连接到服务器。这个教程主要基于mysql客户机的应用。mysql读取您的SQL 查询，将它们发送给服务器，并显示结果。mysql运行在MySQL所支持的所有平台上，并提供与服务器交互的最直接的手段，因此，它首先是一个逻辑上的客户机。

在本书中，我们将用samp_db 作为样例数据库的名称。但是有可能在您完成本例子的过程中需要使用另一个数据库名。因为可能在您的系统上已经有某个人使用了samp_db 这个名称，或者管理员给您指定了另一个数据库名称。在后面的例子中，无论是哪种情况，都用数

据库的实际名称代替samp_db。表名可以像例子所显示的那样精确地使用，即使系统中的多个人都具有他们自己的样例数据库也是如此。顺便说一下，在MySQL中，如果有人使用了相同的表名也没什么关系。一旦各个用户都具有自己的数据库， MySQL将一直保留这些数据库名，防止各用户互相干扰。

1.4.1基本要求

为了试验这个教程中的例子，必须安装MySQL。特别是必须具有对MySQL客户机和某个MySQL服务器的访问权。相应的客户机程序必须位于您的机器上。至少需要有mysql程序，最好还有mysqlimport程序。服务器也可以位于您的机器上，尽管这不是必须的。实际上，只要允许连接到服务器，那么服务器位于何位置都没有关系。若服务器正巧运行在您的机器上，适当的客户机程序又已经安装，那么就可以开始试验了。如果您尚需设法搞到MySQL，可参阅附录A“获得和安装软件”的说明。如果您正自己安装MySQL，可参阅这一章，或把它给管理员看。如果网络访问是通过一个因特网服务商(ISP)进行的，那么可查看该服务商是否拥有MySQL。如果该ISP 不提供MySQL服务，可查看附录J“因特网服务商”以得到某些选择更适合的服务商的建议。

除MySQL软件外，还需要得到创建样例数据库及其表的权限。如果您没有这种权限，可以向MySQL管理员咨询。管理员可通过运行mysql并发布如下的命令提供这种权限:

MySQL与mysql的区别

为了避免混淆，应该说明，“MySQL”指的是整个MySQLRDBMS，而“mysql”代表的是一个特定的客户机程序名。它们的发音都是相同的，但可通过不同的大小写字符和字体来区分。关于发音，MySQL的发音为“my-ess-queue-ell”。我们知道这是因为MySQL参考指南中是这样发音的。而SQL 的发音为“sequel”或“ess-queue-ell”。我不认为哪个发音更好一些。愿意读哪个音都可以，不过在您对别人读的时候，他可能会用他认为是“正确”的发音对您进行纠正。

前一个命令在paul 从localhost(服务器运行在正运行的同一主机)连接时，允许它完全访问samp_db 数据库及它的所有表。它还给出了一个口令secret。第二个命令与第一个类似，但允许paul 从任何主机上连接(“%”为通配符)。也可以用特定的主机名取代“%”，使paul只能从该主机上进行连接。(如果您的服务器允许从localhost 匿名访问，由于服务器搜索授权表查找输入连接匹配的方式的原因，这样一个GRANT 语句可能是必须的。)关于GRANT语句以及设置MySQL用户账号的更详细信息，可在第11章“常规的MySQL管理”找到。

1.4.2 取得样例数据库的分发包

这个教程在某些地方要涉及来自“样例数据库分发包”中的文件。有的文件含有帮助来设置样例数据库的查询或数据。为了得到这个分发包，可参阅附录A。在打开这个分发包时，将创建一个名为samp_db 的目录，此目录中含有所需的文件。无论您在哪个地方试验与样例数据库有关的例子，建议都移入该目录。

1.4.3 建立和中止与服务器的连接

为了连接到服务器，从外壳程序(即从UNIX 提示符，或从Windows 下的DOS 控制台)激活mysql程序。命令如下：

其中的“%”在本书中代表外壳程序提示符。这是UNIX 标准提示符之一;另一个为“$”。在Windows 下，提示符类似“c:>”。

mysql命令行的options 部分可能是空的，但更可能的是发布一条类似如下的命令：

在激活mysql时，有可能不必提供所有这些选项;确切使用的命令请咨询MySQL管理员。此外，可能还需要至少指定一个名称和一个口令。

在刚开始学习MySQL时，大概会为其安全系统而烦恼，因为它使您难于做自己想做的事。(您必须取得创建和访问数据库的权限，任何时候连接到数据库都必须给出自己的名字和口令。)但是，在您通过数据库录入和使用自己的记录后，看法就会马上改变了。这时您会很欣赏MySQL阻止了其他人窥视(或者更恶劣一些，破坏!)您的资料。

下面介绍选项的含义：

■ -hhost_name(可选择形式：--host=host_name)

希望连接的服务器主机。如果此服务器运行在与mysql相同的机器上，这个选项一般可

省略。

■ -uuser_name(可选择的形式：--uuser=user_name)您的MySQL用户名。如果使用UNIX 且您的MySQL用户名与注册名相同，则可以省去这个选项;mysql将使用您的注册名作为您的MySQL名。在Windows 下，缺省的用户名为ODBC。这可能不一定非常有用。可在命令行上指定一个名字，也可以通过设置USER 变量在环境变量中设置一个缺省名。如用下列set 命令指定paul 的一个用户名：

■ - p(可选择的形式：--password)

这个选项告诉mysql提示键入您的MySQL口令。注意：可用-pyour_password 的形式(可选择的形式： --password =your_password)在命令行上键入您的口令。但是，出于安全的考虑，最好不要这样做。选择-p 不跟口令告诉mysql在启动时提示您键入口令。例如：

在看到Enter password: 时，键入口令即可。(口令不会显到屏幕，以免给别人看到。)请注意，MySQL口令不一定必须与UNIX 或Windows 口令相同。如果完全省略了-p 选项，mysql就认为您不需要口令，不作提示。请注意： -h 和-u选项与跟在它们后面的词有关，无论选项和后跟的词之间是否有空格。而-p 却不是这样，如果在命令行上给出口令， -p 和口令之间一定不加空格。例如，假定我的MySQL用户名和口令分别为paul 和secret，希望连接到在我注册的同一机器上运行的服务器上。下面的mysql命令能完成这项工作：

在我键入命令后， mysql显示Enter password: 提示键入口令。然后我键入口令( * * * * * *表明我键入了secret)。如果一切顺利的话， mysql显示一串消息和一个“ mysql>”提示，表示它正等待我发布查询。完整的启动序列如下所示：

为了连接到在其他某个机器上运行的服务器，需要用-h 指定主机名。如果该主机为pit-viper.snake.net，则相应的命令如下所示：

在后面的说明mysql命令行的多数例子中，为简单起见，我们打算省去- h、-u和-p 选项。并且假定您将会提供任何所需的选项。

有很多设置账号的方法，从而不必在每次运行mysql时都在连接参数中进行键入。这个问题在1. 5节“与mysql交互的技巧”中介绍。您可能会希望现在就跳到该节，以便找到一些更易于连接到服务器的办法。

在建立了服务器的一个连接后，可在任何时候键入下列命令来结束会话：

还可以键入Control-D 来退出，至少在UNIX 上可以这样。

1.4.4 发布查询

在连接到服务器后，就可以发布查询了。本节介绍有关与mysql交互应该了解的一些知识。为了在mysql中输入一个查询，只需键入它即可。在查询的结尾处，键入一个分号(“;”)并按Enter 键。分号告诉mysql该查询是完整的。(如果您喜欢键入两个字符的话，也可以使用“ g”终止查询。)在键入一个查询之后， mysql将其发送到服务器上。该服务器处理此查询并将结果送回mysql，mysql将此结果显示出来。下面是一个简单的查询例子和结果：

它给出当前的日期和时间。(NOW() 函数本身并无多大用处，但可将其用于表达式中。如比较当前日期和其他日期的差异。)

mysql还在结果中显示行数计数。本书在例子中一般不给出这个计数。因为mysql需要见到分号才发送查询到服务器，所以在单一的行上不需要键入分号。如果有必要，可将一个查询分为几行，如下所示：

请注意，在键入查询的第一行后，提示符从‘mysql’ 变成了‘- >’;这表示mysql允许继续键入这个查询。这是一个重要的提示，因为如果在查询的末尾忘记了分号，此提示将有助于提醒您查询尚不完整。否则您会一直等下去，心里纳闷为什么mysql执行查询为什么这么长的时间还没完;而mysql也搞不清为什么结束查询的键入要花您那么多的时间!

大部分情况下，用大写字符、小写字符或大小写字符混合键入查询没什么关系。下列查询全是等价的：

本书中的例子用大写字符表示SQL 关键字和函数名，用小写字符表示数据库、表和列名。

如果在查询中调用一个函数，在函数名和后跟的圆括号中间不允许有空格，例：

这两个查询看上去差别不大，但第二个失败了，因为圆括号并没有紧跟在函数名的后面。如果已经开始键入一个多行的查询，而又不想立即执行它，可键入‘ c’ 来跳过(放弃)它，如：

请注意，提示符又变回了‘mysql>’，这表示mysql为键入的新查询作好了准备。可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。例如，如果在文件my_file.sql 中存放有查

询，可如下执行这些查询：

可用这种办法调用任何所需的文件。这里用后缀为“.sql”来表示该文件含有SQL 语句。执行mysql的这种方法将在输入数据到samp_db 数据库时的“增加新记录”中使用。为了装载一个表，让mysql从某个文件中读取INSERT 语句比每次用手工键入这些语句更为方便。

本教程的其余部分向您提供了许多可以自己试试的查询。这些查询以‘mysql>’ 提示为前导后跟结束分号，这些例子通常都给出了查询输出结果。可以按给出的形式键入这些查询，所得到的结果应该与自学材料中的相同。给出的查询中无提示符的或无分号语句结束符的只是用来说明某个要点，不用执行它们。(如果愿意您可以试一下，但如果试的话，请记住给语句末尾加一个分号。)本书后面的章节中，我们一般不给出‘mysql>’ 提示或SQL 语句的分号。这样做的原因是为了可以在非mysql客户机程序的语言环境(如在Perl 脚本中或PHP 脚本中)中发布查询，在这些语言环境中，既无提示符也不需要分号。在专门针对mysql输入一个查询的场合会作出相应的说明。

1.4.5 创建数据库

现在开始创建samp_db 样例数据库及其表，填充这些表并对包含在这些表中的数据进行一些简单的查询。

使用数据库涉及几个步骤：

1) 创建(初始化)数据库。

2) 创建数据库中的表。

3) 对表进行数据插入、检索、修改或删除。

检索现有数据是对数据库执行的最简单且常见的操作。另外几个最简单且常见的操作是插入新数据、更新或删除现有数据。较少使用的操作是创建表的操作，而最不常用的操作是创建数据库。

我们将从头开始，先创建数据库，再插入数据，然后对数据进行检索。为了创建一个新的数据库，用mysql连接到数据库然后发布CREATE DATABASE 语句，此语句指定了数据库名：

在创建表以及对这些表进行各种操作之前，必须先创建samp_db 数据库。创建数据库后，这个新创建的数据库并不是当前数据库。这可从执行下面的查询看出：

为了使samp_db 成为当前数据库，发布USE 语句即可：

USE 为少数几个不需要终结符的语句之一，当然，加上终结符也不会出错。HELP 是另一个不需要终结符的语句。如果想了解不需要终结符的语句有哪些，可发布HELP 语句。在发布了USE 语句后，samp_db 成为缺省数据库：

使数据库成为当前数据库的另一个方法是在激活mysql时在命令行上指定它，如下所示：

事实上，这是一个命名要使用的数据库的方法。如果需要连接参数可在数据库名前指定。例如，下列两个命令使我们能连接到在本地主机和p i t - v i per.snake.net 上的samp_db 数据库上：

除非另有指定，否则后面的例子都假定在激活mysql时，在命令行上给出samp_db 使其成为当前数据库。如果激活数据库时忘了在命令行上指定数据库，只需发布USE samp_db 语句即可。

1.4.6 创建表

本节中，我们将创建样例数据库samp_db 所需的表。我们首先考虑美国历史同盟需要的表。然后再考虑学分保存方案所需的表。在某些数据库的书籍中，在这里要大讲分析与设计、实体―关系图、标准程序以及诸如此类的东西。这里确实也可以讲这些东西，但是我宁可只讲点实用的东西，比方说，我们的数据库应该是怎样的：数据库中将包含什么内容，每个表中有哪些数据以及由决定如何表示数据而带来的一些问题。这里所作出的关于数据表示的选择并不是绝对的。在其他场合下，可能会选择不同的方式来表示类似的数据，这取决于应用的需要以及打算将数据派何用途。

1. 美国历史同盟所需的表

美国历史同盟的表设计相当简单：

■ 总统( p r e s i d e n t )表。此表含有描述每位总统的记录。同盟站点上的联机测验要使用这个表。

■ 会员( member )表。此表用来维护同盟每个会员的当前信息。这些信息将用来建立会员地址名录的书面和联机版本、发送会员资格更新提示等等。

(1) president表

president 表很简单，因此我们先讨论它。这个表将包含每位美国总统的一些基本信息：

■ 姓名。姓名在一个表中可用几种方式表示。如，可以用一个单一的列来存放完整的姓名，或者用分开的列来分别容纳名和姓。当然用单一的列更为简单，但是在使用上会带来一些限制，如：

■ 如果先输入只有名的姓名，则不可能对姓进行排序。

■ 如果先输入只有姓的姓名，就不可能对具有名的姓名进行显示。

■ 难以对姓名进行搜索。例如，如果要搜索某个特定的姓，则必须使用一个特定的模式，并且查找与这个模式匹配的姓名。这样较之只查找姓效率更低和更慢。member 表将使用单独的名和姓的列以避免这些限制。名列还存放中名(注：西方国家的姓名一般将名放在前，姓放在后，而且除了有名和姓外，有时还有中名，这是在位置上介于名和姓之间的中间名字)或首字母。这样应该不会削弱我们可能进行的任何一种排序，因为一般不可能对中名进行排序(或者甚至不会对名进行排序)。姓名即可以“ Bush, George W. ”格式显示，也可以“G e o rge W. B us h”格式显示。还有一种稍显复杂一点的情形。一个总统( Jimmy Carter)在其姓名的末尾处有一

个“ J r. ”，这时怎样做?根据名字打印的格式，这个总统的姓名显示为“ J a m e s E . C a r ter, J r.”或“C a r ter, James E., Jr.”，“J r.”与名和姓都没有关系，因此我们将建另外一个字段来存放姓名的后缀。这表明在试图确定怎样表示数据时，即使一个特殊的值也可能会带来问题。它也表明，为什么在将数据放入数据库前，尽量对数据值的类型进行了解是一个很好的想法。如果对数据了解不够，那么有可能在已经开始使用一个表后，不得不更改该表的结构。这不一定是个灾难，但通常应该避免。

■ 出生地(城市和州)。就像姓名一样，出生地也可以用单个列或多个列来表示。使用单列更为简单些，但正如姓名中的情形一样，独立的多个列使我们可以完成用单个列不方便完成的事情。例如，如果城市和州分别给出，查找各位总统出生在哪个州的记录就会更容易一些。

■ 出生日期和死亡日期。这里，唯一特殊的问题是我们不能要求都填上死亡日期，因为有的总统现在还健在。MySQL提供了一个特殊的值NULL，表示“无值”，可将其用在死亡日期列中以表示“仍然健在”。

(2) member 表

存储历史同盟会员清单的member 表在每个记录都包含单个人员的基本描述信息这一点上，类似于president 表。但是每个member 的记录所含的列更多，member 表的各列如下：

■ 姓名。使用如president 表一样的三个列来表示：姓、名(如果可能的话还有中名)、后缀。

■ ID 号。这是开始记录会员时赋给每个会员的唯一值。以前同盟未用ID 号，但现在的记录做得更有系统性，所以最好开始使用ID 号。(我希望您找到有利于使用MySQL并考虑到其他的将它用于历史同盟记录的方法。使用数字，将member 表中的记录与其他与会员有关的表中的记录相关联要更容易一些。)

■ 截止日期。会员必须定期更新他们的会员资格以免作废。对于某些应用，可能会用到最近更新的日期，但是近更新日期不适合于历史同盟。会员资格可在可变的年数内(一般为一年、二年、三年或五年)更新，而最近更新的日期将不能表示下一次更新必须在何时进行。此外，历史同盟还允许有终生会员。我们可以用未来一个很长的日期来表示终生会员，但是用NULL 似乎更为合适，因为“无值”在逻辑上对应于“永不终止”。

■ 电子邮件地址。对于有电子邮件地址的会员，这将使他们能很容易地进行相互之间的通信。作为历史同盟秘书，这使您能电子化地发送更新通知给会员，而用不着发邮政信函。这比到邮局发送信函更容易，而且也不贵。还可以用电子邮件给会员发送他们的地址名录条目的当前内容，并要求他们在有必要时更新信息。

■ 邮政地址。这是与没有电子邮件(或没有返回信息)的会员联络所需要的。将分别使用街道地址、城市、州和Zip 号。街道地址列又可以用于有诸如P.O. Box 123 而不是123 Elm St. 的会员的信箱号。我们假定所有同盟会员全都住在美国。当然，对于具有国际会员的机构，此假设过于简化了。如果希望处理多个国家的地址，还需要对不同国家的地址格式作一些工作。例如，这里的Zip 号就不是一个国际标准，有的国家有省而不是州。

■ 电话号码。与地址字段一样，这个列对于联络会员也是很有用的。

■ 特殊爱好的关键词。假定每个会员一般都对美国历史都有兴趣，但可能有的会员对某些领域有特殊的兴趣。此列记录了这些特殊的兴趣。会员可以利用这个信息来找到其他具有类似兴趣的会员。

(3) 创建表

现在我们已经作好了创建历史同盟表的准备。我们用CREATE TABLE 语句来完成这项工作，其一般格式如下：

其中tbl_name 代表希望赋予表的名称。column_specs 给出表中列的说明，以及索引的说明(如果有的话)。索引能使查找更快;我们将在第4 章“查询优化”中对其作进一步的介绍。

president 表的CREATE TABLE 语句如下所示：

如果想自己键入这条语句，则调用mysql，使samp_db 为当前数据库：

然后，键入如上所示的CREATE TABLE 语句。(请记住，语句结尾要增加一个分号，否则mysql将不知道哪儿是语句的结尾。)

为了利用来自样例数据库分发包的预先写下的描述文件来创建president 表，可从外壳程序运行下列命令：

不管用哪种方法调用mysql，都应该在命令行中数据库名的前面指定连接参数(主机名、用户名或口令)。CREATE TABLE 语句中每个列的说明由列名、类型(该列将存储的值的种类)以及一些可能的列属性组成。president 表中所用的两种列类型为VARCHAR 和DATE。VARCHAR(n)代表该列包含可变长度的字符(串)值，其最大长度为n 个字符。可根据期望字符串能有多长来选择n 值。

state 定义为VARCHAR( 2 );即所有州名都只用其两个字符的缩写来表示。其他的字符串列则需要更长一些，以便存放更长的值。

我们使用过的其他列类型为DATE。这种列类型表示该列存储的是日期值，这一点也不令人吃惊。而令人吃惊的是，日期的表示以年份开头。其标准格式为“ Y Y Y Y- M M - D D”(例如，“1999 - 07 - 18”)。这是日期表示的ANSI SQL 标准。我们用于president 表的唯一列属性为NULL(值可以缺少)和NOT NULL(必须填充值)。多数列是NOT NULL 的，因为我们总要有一个它们的值。可有NULL 值的两个列是s uff i x(多数姓名没有后缀)和death(有的总统仍然健在，所以没有死亡日期)。member 表的CREATE TABLE 语句如下所示：

将此语句键入mysql或执行下列外壳程序命令：

从列的类型来看，member 表并不很有趣：所有列中，除了一列之外，其他列都是可变长字符串。这个例外的列就是e x p i r a t i o n，为DATE 型。终止日期值有一个缺省值为“0 0 0 0 - 0 0 -0 0”，这是一个非NULL 的值，它表示未输入合法的日期值。这样做的原因是expiration 可以是NULL，它表示一个会员是终身会员。但是，因为此列可以为NULL，除非另外指定一个不同的值，否则它将取缺省值“ 0 0 0 0 - 0 0 - 0 0”。如果创建了一个新会员记录，但忘了指定终止日期，该会员将成为一个终身会员!通过采用缺省值“ 0 0 0 0 - 0 0 - 0 0”的方法，避免了这个问题。它还向我们提供了一种手段，即可以定期地搜索这个值，以找出过去未正确输入终止日期的记录。

请注意，我们“忘了”放入会员ID 号的列。这是专门为了以后练习使用ALTER TABLE语句而遗留下的。现在让我们来验证一下MySQL是否确实如我们所期望的那样创建了表。在mysql中，发布下列查询：

与MySQL3.23 一样，此输出还包括了显示访问权限信息的另一个列，这里没有给出，

因为它使每行太长，不易显示。

这个输出结果看上去和我们所期望的非常一致，除了state 列的信息显示它的类型为CHAR( 2 )。这就有点古怪了，我们不是定义它为VARCHAR(2) 了吗?是的，是这样定义的，但是MySQL已经悄悄地将此类型从VARCHAR 换成了CHAR。原因是为了使短字符串列的

存储空间利用更为有效，这里不多讨论。如果希望详细了解，可参阅第3 章中关于ALTE RTABLE 语句的介绍。但对这里的使用来说，两种类型没有什么差别。

如果发布一个DESCRIBE member 查询，mysql也会显示member 表的类似信息。DESCRIBE 在您忘了表中的列名、需要知道列的类型、了解列有多宽等的时候很有用。它对于了解MySQL存储表行中列的次序也很有用。列的这个存储次序在使用INSERT 或LOAD DATA 语句时非常重要，因为这些语句期望列值以缺省列的次序列出。DESCRIBE 可以省写为DESC，或者，如果您喜欢键入较多字符，则DESCRIB Etbl_name 另一个等同的语句为SHOW COLUMNS FROM tbl_name。

如果忘了表名怎么办?这时可以使用SHOW TABLE S。对于samp_db 数据库，我们目前

为止创建了两个表，其输出结果如下：

如果您甚至连数据库名都记不住，可在命令行上调用mysql而不用给出数据库名，然后发布SHOW DATABASES 查询：

数据库的列表在不同的服务器上是不同的，但是至少可以看到samp_db 和mysql;后一个数据库存放控制MySQL访问权限的授权表。DESCRIBE 与SHOW 查询具有可从外壳程序中使用的命令行等同物，如下：

% mysqlshow 与SHOW DATABASES 一样列出所有数据库

% mysqlshow db _ name 与SHOW TABLES 一样列出给定数据库的表

% mysqlshow db_name tbl_name 与DESCRIBE tbl_name 一样，列出给定表中的列

2. 用于学分保存方案的表

为了知道学分保存方案需要什么表，我们来看看在原来学分簿上是怎样记学分的。图1- 2示出学分簿的一页。该页的主体是一个记录学分矩阵。还有一些对学分有意义的必要信息。学生名和ID 号列在矩阵的一端。(为了简单好看，只列出了四个学生。)在矩阵顶端，记录了进行测验和测试的日期。图中示出9月3号、6号、16号和2 3号进行测验， 9月9号和10月1号进

行测试。

为了利用数据库来记录这些信息，需要一个学分表。这个表中应该包含什么记录呢?很明显，每一行都需要有学生名、测验或测试的日期以及学分。图1-3 示出了用这样的表表示的一些来自学分簿的学分。(日期以MySQL的表示格式“Y Y Y Y- M M - D D”表示。)

但是，以这种方式设置表似乎有点问题。好像少了点什么。请看图1- 3中的记录，我们分辨不出是测验的学分还是测试的学分。如果测验和测试的学分权重不同，在确定最终的学分等级时知道学分的类型是很重要的。或许可以试着从学分的取值范围来确定学分的类型(测验的学分一般比测试的学分少)，但是这样做很不方便，因为这需要进行判断，而且在数据中也不明显。可以通过记录学分的类型来进行区分，如对学分表增加一列，此列包含“ T”或“Q”以表示是“测试”或是“测验”，如图1-4 所示。这具有使学分数据类型清析易辨的优点。不利的地方是这个信息有点冗余。显然对具有同一给定日期的记录，学分的类型列总是取相同的值。9月2 3日的学分总是为“ Q”类型，而10月1日的学分其类型总是具有“ T”类型。这样令人很不满意。如果我们以这种方式记录一组测验或测试的学分，不仅要为每个新记录输入相同的日期，而且还要一再重复地输入相同的学分类型。谁会希望一再输入冗余的信息呢?

我们可以试试另外一种表示。不在score 表中记录学分类型，而是从日期上区分它们。我们可以做一个日期列表，用它来记录每个日期发生的“学分事件”(测验或测试)。然后可以将学分与这个事件列表中的信息结合，确定学分是测验学分还是测试学分。这只要将score 表记录中的日期与event 表中的日期相匹配得出事件类型即可。图1- 5示出这个表的设计并演示了score 表记录与9月2 3日这个日期相关联的工作。通过将score 表中的记录与event 表中记录相对应，我们知道这个学分来自测验。

这比根据某些猜测来推断学分类型要好得多;我们可以根据明确记录在数据库中的数据来直接得到学分类型。这也比在score 表中记录学分类型更好，因为我们只需对每个类型记录一次。

但是，在第一次听到这种事情时(即结合使用多个表中的信息)，可能会想，“嗯，这是一个好主意，但是不是要做很多工作呢?会不会使工作更复杂了?”在某种程度上，这种想法是对的。处理两个记录表比处理一个要复杂。但是再来考察一下学分簿(见图1- 2)。不是也记录了两套东西吗?考虑下列事实：

■ 在学分矩阵中用两个单元记录学分，其中每个单元都是按学生名字和日期(在矩阵的旁边和顶上)进行索引的。这代表了一组记录;与score 表的作用相同。

■ 怎样知道每个日期代表的事件类型呢?在日期上方写了字符“ T”或“Q”!因此，也在矩阵顶上记录了日期和学分类型之间的关系。它代表第二组记录;与event 表的作用相同。

换句话说，这里建议在两个表中记录信息与用学分簿记录信息所做的工作没什么不同。唯一不同的是，这两组信息在学分簿中不是那么明显地被分开。在图1- 5中所示的event 表的设计中加了一个要求，那就是日期必须是唯一的，因为要用它连接score 与event 表的记录。换句话说，同一天不能进行两次测验，或者同一天不能进行一次测验和一次测试。否则，将会在score 表中有两个记录并且在event 表中也有两个记录，全都具有相同的日期，这时就不知道应如何将score 的记录与event 的记录进行匹配。如果每天不多于一个学分事件，这就是一个永远不会出现的问题，可是事实并非如此简单。有时，一天中可能会有不止一个学分事件。我常听有的人说他们的数据，“那种古怪情况从不会出现。”然而，如果这种情况确实出现时，就必须重新设计表以适应这种情况引起的问题。最好是预先考虑以后可能出现的问题，并预先准备好怎样处理他们。因此，我们假定有时可能会需要同一天记录两组学分。我们怎样处理呢?如果出现这种情况，问题并不难解决。只要对处理数据的方式作一点小的更改，就可使同一日期上有多个事件而不会引起问题：

1) 增加一个列到event 表，并用它来给表中每个记录分配一个唯一的编号。实际上这就给了每个事件一个唯一的ID 号，因此我们称该列为event_id 列。(如果觉得这好像是做傻事，可看一下图1-2 中的学分簿，其中已经有这个特征了。事件ID 正好与学分簿分数矩阵中列号相似。这个编号可能没有清晰地写在那儿并标上“事件ID，”但是它确实在那儿。)

2) 当向score 表中输入学分时，输入的是事件ID 而不是日期。这些改变的结果如图1-6 所示。现在连接score 和event 表时，用的是事件ID 而不是日期，而且不仅用event 表来决定每个学分的类型，而且还用它来决定其日期。并且在event 表中不再有日期必须唯一这个限制，而唯一的是事件ID。这表示同一天可以有一打测试和测验，而且能够在记录里边直接保存它们。(毫无疑问，学生们听到这个一定浑身发抖。)不幸的是，从人的观点来看，图1-6 中的表设计较前一个更不能令人满意。score 表也更为抽象一些，因为它包含的从直观上可以理解的列更少。而图1-4 中此表的设计直观且容易理解，因为那个score 表具有日期和学分类型的列。当前的score 表如图1-6 所示，日期和学分类型的列都没有了。这极大地去除了作为人能够很容易考虑的一切。谁希望看到其中有“事件ID”的score 表?如果有的话，也不代表我们大多数人。

此时，可看到能够电子化地完成学分记录，且在赋予学分等级时不必做各种乏味的手工计算。但是，在考虑了如何实际在一个数据库中表示学分信息后，又会被怎样抽象和拆分组成学分信息的表示难住了。自然会产生一个问题：“根本不使用数据库可能会更好一些?或许MySQL不适合我?”正如您所猜测的那样，笔者将从否定的方面对这个问题进行回答，否则这本书就没必要再往下写了。不过，在考虑如何做一件工作时，应考虑各种情况并提问是否最好不使用数据库系统(如MySQL)而使用一些别的东西(如电子表格等)：

■ 学分簿有行和列，而电子表格也有。这使学分簿和电子表格在概念上和外观上都非常类似。

■ 电子表格能够完成计算，可以利用一个计算字段来累计每个学生的学分。但是，要对测验和测试进行加权可能有点麻烦，但这也是可以办得到的。另一方面，如果希望只查看某部分数据(如只查看学分或测试)，进行诸如男孩与女孩的比较，或以一种灵活的方式显示合计信息等，情况又大有不同了。电子表格的功能显得要差一些，而关系数据库系统完成这些工作相当容易。另外要考虑的一点是为了在关系数据库中进行表示而对数据进行抽象和分解，这个问题并不真的那么难以应付。只要考虑安排数据库使其不会以一种对您希望做的事无意义的方式来表示数据即可。但是，在确定了表示方式之后，就要靠数据库引擎来协调和表示数据了。您肯定不会希望将它视为一堆支离破碎的东西。

例如，在从score 表中检索学分时，不希望看到事件ID;但希望看到日期。这没有什么问题。数据库将会根据事件ID 从event 表中查找出日期。您还可能想要看看是测验的学分或测试的学分。这也不成问题。数据库将用相同的方法查找出学分类型，也是利用事件ID。请记住，这就是如像MySQL这样的关系数据库的优势所在，即，使一样东西与另一样东西相关联，以便从多个来源得出信息并以您实际想看到的形式提供出来。在学分保存数据的情况中，MySQL确实利用事件ID 将信息组合到了一起，而无需人工来完成这件事。

现在我们先来看看，如何使MySQL完成这种将一个东西与另一个东西相联系的工作。

假定希望看到1999年9月2 3号的学分，针对某个特定日期中给出的事件的学分查询如下所示：

相当吓人，是吗?这个查询通过将score 表的记录与event 表的记录连接(关联)来检索学生名、日期、学分和学分的类型。其结果如下所示：

您肯定注意到了，它与图1-4 中给出的表设计相同，而且不需要知道事件ID 就可得出这个结果，只需指出感兴趣的日期并让MySQL查找出哪个学分记录具有该日期即可。如果您一直担心抽象和分解会使我们损失一些东西的话，看到这个世界，就不会有这种担心了。

当然，在考虑过查询后，您还可能对其他别的东西产生担心。即，这个查询看上去有点长并且也有点复杂;是不是做了很多工作写出这样的东西只是为了查找某个给定日期的学分?是的，确实是这样。但是，在每次想要发布一个查询时，有几种方法可以避免键入多行的SQL。一般情况下，一旦您决定如何执行这样一个查询并将它保存起来后，就可以按需要多次执行它。我们将在1. 5节“与mysql交互的技巧”中介绍怎样完成这项工作。

在上述查询的介绍中，我们有点超前了。不过，这个查询比起我们要实际用来得出学分的查询是有点简单了。原因是，我们还要对表的设计作更多的修改。我们将采用一个唯一的学生ID，而不在score 表中记录学生名。(即，我们将使用来自学分簿的“ ID”列的值而不是来自“ Name”列的值。)然后，创建另一个称为student 的表来存放name 和student_id 列(见图1- 7)。

为什么要作出这种修改呢?只有一个原因，可能有两个学生有相同的名字。采用唯一的学生ID 号可帮助区分他们的学分。(这与利用唯一的事件ID 而不是日期来分辨出相同日期的测试或测验完全类似。)在对表的设计作了这样的修改后，实际用来获得给定日期的学分查询变得更为复杂了一些，这个查询如下：

如果您不能立即清楚地读懂这个查询的意思的话，也不必担心。在进一步深入这个教程之后，就能看懂这个查询了。将会从图1- 7中注意到，在student 表中增加了点学分簿中没有的东西。它包含了一个性别列。这便可以做一些简单的事情，如对班级中男孩和女孩的人数计数;也可以做一些更为复杂的事情，如比较男孩和女孩的学分。我们已经设计完了学分保存的几乎所有的表。现在只需要另外

一个表来记录出勤情况即可。这个表的内容相对较为直观，即，一个学生ID 号和一个日期(见图1- 8)。表中的每行表示特定的学生在

给定的日期缺勤。在学分时段末，我们将调用MySQL的计数功能来汇总此表的内容，以便得出每个学生的缺勤数。

既然现在已经知道学分保存的各个表的结构，现在可以创建它们了。student 表的CREATE TABLE 语句如下：

将上述语句键入mysql或执行下列外壳程序命令：

CREATE TABLE 语句创建了一个名为student 的表，它含有三列，分别为： name、s e x和s t ud e n t _ i d，

name 是一个可变长的字符串列，最多可存放20 个字符。这个名字的表示比历史同盟表中所用的表示要简单，它只用了单一的列而不是分别的名和姓列。这是因为我们已经预先知道，不存在无需做另外的工作就使得在多个列上工作得更好的查询样例。sex 表示学生是男孩还是女孩。这是一个ENUM(枚举)列，表示只能取明确地列在说明中的值之一，这里列出的值为：“F”和“M”，分别表示女和男。在某列只具有一组有限值时，ENUM 类型非常有用。我们可以用CHAR(1) 来代替它，但是ENUM 更明确规定了列可以取什么值。如果对包括一个ENUM 列的表发布一条DESCRIBE tbl_name 语句，MySQL将确切地显示可取的值有哪些。顺便说一下， ENUM 列中的值不一定只是单个字符。此列还可以定义为ENUM(‘f e m a l e’,‘m a l e’)。

student_id 为一个整数型列，它将包含唯一的ID 号。通常，大概会从一个中心资料来源处(如学校办公室)取得学生的ID 号，但在这里是我们自己定的。虽然student_id 列只包含一个数，但其定义包括几个部分：

■ INT 说明此列的值必须取整数(即无小数部分)。

■ UNSIGNED 不允许负数。

■ NOT NULL 表示此列的值必须填入。(任何学生都必须有一个ID 号。)

■ AUTO_INCREMENT 是MySQL中的一个特殊的属性。其作用为：如果在创建一个新的student 表记录时遗漏了student_id 的值(或为NULL)，MySQL自动地生成一个大于当前此列中最大值的唯一ID 号。在录入学生表时将用到这个这特性，录入学生表时可以只给出name 和sex 的值，让MySQL自动生成student_id 列值。

■ PRIMARY KEY 表示相应列的值为快速查找进行索引，并且列中的每个值都必须是惟一的。这样可防止同一名字的ID出现两次，这对于学生ID 号来说是一个必须的特性。(不仅如此，而且MySQL还要求每个AUTO_INCREMENT 列都具有一个惟一索引。)如果您不理解AUTO_INCREMENT 和PRIMARY KEY 的含义，只要将其想像为一种为每个学生产生ID 号的魔术方法即可。除了要求值唯一外，没有什么别的东西。请注意：如果确实打算从学校办公室取得学生ID 号而不是自动生成它们，则可以按相同的方法定义student_id 列，只不过不定义AUTO_INCREMENT 属性即可。event 表如下定义：

将此语句键入mysql或执行下列外壳程序的命令：

所有列都定义为NOT NULL，因为它们中任何一个值都不能省略。date 列存储标准的MySQLDATE 日期值，格式为“Y Y Y Y- M M - D D”(首先是年)。type 代表学分类型。像student 表中的sex 一样，type 也是一个枚举列。所允许的值为“T”和“Q”，分别表示“测试”和“测验”。event_id 是一个AUTO_INCREMENT 列，类似于student 表中的student_id 列。采用AUTO_INCREMENT 允许生成唯一的事件ID 值。正如student 表中的student_id 列一样，与值的惟一性相比，某个特定的值并不重要。score 表如下定义：

将此语句键入mysql或执行下列外壳程序的命令：

score 为一个INT (整型)列。即，假定学分值总是为一个整数。如果希望使学分值具有小数部分，如5 8 . 5，应该采用浮点列类型，如FLOAT或DECIMAL。student_id 列和event_id 列都是整型，分别表示每个学分所对应的学生和事件。通过利用它们来连接到student 和event 表，我们能够知道学生名和事件的日期。我们将两个列组成了PRIMARY KEY。这保证我们不会对同一测验或测试重复一个学生的学分。而且，这样还很容易在以后更改某个学分。例如，在发现学分录入错时，可以在利用MySQL的REPLACE语句放入一个新记录，替换掉旧的记录。不需要执行DELETE 语句与INSERT 语句;MySQL自动替我们做了。请注意，它是惟一的event_id 和student_id 的组合。在score 表中，两者自身都可能不惟一。一个event_id 值可有多个学分记录(每个学生对应一个记录)，而每个student_id 值都对应多个记录(每个测验和测试有一个记录)。用于出勤情况的absence 表如下定义：

将此语句键入mysql或执行下列外壳程序的命令：

student_id 和date 列两者都定义为NOT NULL，不允许省略值。应定义这两列的组合为主键，以免不当心建立了重复的记录。重要的是不要对同一天某个学生的缺旷进行重复计数。

1.4.7 增加新记录

至此，我们的数据库及其表都已经创建了，在下一节“检索信息”中，我们将看到怎样从数据库中取出数据。现在我们先将一些数据放入表中。在数据库中加入数据有几种方法。可通过发布INSERT 语句手工将记录插入某个表中。还可以通过从某个文件读取它们来增加记录，在这个文件中，记录既可以是利用L O A DDATA 语句或mysqlimport 实用程序装入的原始数据值，也可以是预先写成可馈入mysql的INSERT 语句的形式。本节介绍将记录插入表的每种方法。您所应做的是演习各种方法以明了它们是如何起作用的。然后到本节结束处运行那儿给出的命令来清除表并重装它们。这样做，能够保证表中含有作者撰写下一节时所处理的相同记录，您也能得到相同的结果。让我们开始利用INSERT 语句来增加记录，这是一个SQL 语句，需要为它指定希望插入数据行的表或将值按行放入的表。INSERT 语句具有几种形式：

■ 可指定所有列的值

例如：

“I N TO”一词自MySQL3.22.5 以来是可选的。(这一点对其他形式的INSERT 语句也成立。)VALUES 表必须包含表中每列的值，并且按表中列的存放次序给出。(一般，这就是创建表时列的定义次序。如果不能肯定的话，可使用DESCRIBE tbl_name 来查看这个次序。)在MySQL中，可用单引号或双引号将串和日期值括起来。上面例子中的NULL值是用于student 和event 表中的AUTO_INCREMENT 列的。(插入“错误”的值将导致下一个student_id 或event_id 号的自动生成。)自3.22.5 以来的MySQL版本允许通过指定多个值的列表，利用单个的INSERT语句将几行插入一个表中，如下所示：

例如:

这比多个INSERT 语句的键入工作要少，而且服务器执行的效率也更高。

■ 可以给出要赋值的那个列，然后再列出值。这对于希望建立只有几个列需要初始设置的记录是很有用的。

例如:

自MySQL3.22.5 以来，这种形式的INSERT 也允许多个值表：

在列的列表中未给出名称的列都将赋予缺省值。

■ 自MySQL3.22 .10 以来，可以col_name = value 的形式给出列和值。

例如:

在SET 子句中未命名的行都赋予一个缺省值。使用这种形式的INSERT 语句不能插入多行。将记录装到表中的另一种方法是直接从文件读取数据值。可以用LOAD DATA 语句或用mysqlimport 实用程序来装入记录。LOAD DATA 语句起批量装载程序的作用，它从一个文件中读取数据。可在mysql内使用它，如下所示：

该语句读取位于客户机上当前目录中数据文件member.txt 的内容，并将其发送到服务器装入member 表。如果您的MySQL版本低于3 . 2 2 . 15，则LOAD DATA LOCAL 不起作用，因为那时从客户机读取数据的能力是在LOAD DATA 上的。(没有LOCAL 关键字，被读取的文件必须位于服务器主机上，并且需要大多数MySQL用户都不具备的服务器访问权限。)缺省时，LOAD DATA 语句假定列值由tab 键分隔，而行则以换行符结束。还假定各个值是按列在表中的存放次序给出的。也有可能需要读取其他格式的文件，或者指定不同的列次

序。更详细的内容请参阅附录D的LOAD DATA 的条款。mysqlimport 实用程序起LOAD DATA 的命令行接口的作用。从外壳程序调用mysqlimport ，它生成一个LOAD DATA 语句：

mysqlimport 生成一个LOAD DATA 语句，此语句使member.txt 文件被装入member 表。如果您的MySQL版本低于3 . 2 2 . 15，这个实用程序不起作用，因为--local 选项需要L O A DDATA LOCAL。正如使用mysql一样，如果您需要指定连接参数，可在命令行上数据库名前指定它们。mysqlimport 从数据文件名中导出表名(它将文件名第一个圆点前的所有字符作为表名)。例如，member.txt 将被装入member 表，而president.txt 将被装入president 表。如果您有多个需要装入单个表的文件，应仔细地选择文件名，否则mysqlimport 将不能使用正确的表名。对于如像member1.txt 与member2.txt 这样的文件名， mysqlimport 将会认为相应的表名为

member1和member 2。不过，可以使用如member.1.txt 和member.2.txt 或member.txt1和member.txt2 这样的文件名。在试用过这些记录追加的方法后，应该清除各个表并重新装载它们，以便它们的内容与下一节假定的内容相同。从外壳程序执行下列命令：

每个文件都含有一个删除可能曾经插入到表中的记录的DELETE 语句，后跟一组INSERT 语句以初始化表的内容。如果不希望分别键入这些命令，可试一下下列语句：

1.4.8 检索信息

现在各个表已经创建并装有数据了，因此让我们来看看可以对这些数据做点什么。SELECT 语句允许以一般的或特殊的方式检索和显示表中的信息。它可以显示表的整个内容：

或者只显示单个行中单个列的内容：

SELECT 语句有几个子句(部件)，可以根据需要用来检索感兴趣的信息。每个子句都可简单、可复杂，从而SELECT 作为一个总的语句也繁简皆宜。但是，可以放心，本书中不会有花一个钟头来编写的长达数页的查询。(我在书中看到有很长的查询时，一般会立即跳过它们，因此我猜您也会这样。)SELECT 语句的一般形式为：SELECT 要选择的东西FROM 一个或多个表WHERE 数据必须满足的条件记住，SQL 为一个自由格式的语言，因此在您编写SELECT 查询时，语句的断行不必严格依照本书。

为了编写SELECT 语句，只需指定需要检索什么，然后再选择某些子句即可。刚才给出的子句“ FROM”、“WHERE”是最常用的，还有一些其他的子句，如GROUP BY、ORDER BY和LIMIT 等。FROM 子句一般都要给出，但是如果不从表中选择数据，也可不给出。例如，下列查询只显示某些可以直接计算而不必引用任何表的表达式的值，因此不需要用FROM 子句：

在确实使用一个FROM 子句指定了要从其中检索数据的表时， SELECT 语句的最“普通”的格式是检索所有内容。用“ *”来表示“所有列”。下面的查询将从student 表中检索所有行并显示：

各列按它们MySQL在表中存放的次序出现。该次序与发布DESCRIBE student 语句时显示的列次序相同。(例子末尾的“. . .”表示此查询返回的输出行比这里显示的还要多。)可明确地命名希望得到的一列或多列。如果只选择学生名，发布下列语句：

如果名字不止一列，可用逗号分隔它们。下列的语句与SELECT * FROM student 等价，只是明确地指出了每一列：

可按任意次序给出列：

如果有必要，同一列甚至也可以给出多次，虽然这样做一般是没有意义的。列名在MySQL中不区分大小写的。下面的查询是等同的：

数据库和表名有可能区分大小写的;这有取决服务器主机上使用的文件系统。在UNIX上运行的服务器对数据库名和表名是区分大小写的，因为UNIX 的文件名是区分大小写的。Windows 的文件名不区分大小写，因此运行在Windows 上的服务器对数据库名和表名不区分

大小写。MySQL允许您一次从多个表中选择列。我们将这个内容留到“从多个表中检索信息”小节去介绍。

1. 指定检索条件

为了限制SELECT 语句检索出来的记录集，可使用WHERE 子句，它给出选择行的条件。可通过查找满足各种条件的列值来选择行。

可查找数字值：

也可以查找串值。(注意，一般串的比较是不区分大小写的。)

可以查找日期值：

可搜索组合值：

WHERE 子句中的表达式可使用表1-1中的算术运算符、表1-2 的比较运算符和表1-3 的逻辑运算符。还可以使用圆括号将一个表达式分成几个部分。可使用常量、表列和函数来完成运算。在本教程的查询中，我们有时使用几个MySQL函数，但是MySQL的函数远不止这里

给出的这些。请参阅附录C，那里给出了所有MySQL函数的清单。

在用表达式表示一个需要逻辑运算的查询时，要注意别混淆逻辑与运算符与我们平常使用的“与”的含义。假如希望查找“出生在Vi rginia 的总统与出生在Maryland 的总统”。应该注意怎样表示“与”的关系，能写成如下的查询吗?

错了，因为这个查询的意思是“选择既出生在Vi rginia 又出生在M a r y l a n d的总统”，不可能有同时出生在两个地点的总统，因此这个查询无意义。在英语中，可以用“a n d”表示这种选择，但在SQL 中，应该用OR 来连接两个条件，如下所示：

这有时是可以觉察到的，不仅仅是在编写自己的查询时可以觉察到，而且在为他人编写查询时也可以知道。最好是在他人描述想要检索什么时仔细听，但不一定使用相同的逻辑运算符将他人的描述转录成SQL 语句。对刚才所举的例子，正确的英语等价描述为“选择出生在Vi rginia 或者出生在Maryland 的总统。”

2. NULL 值

NULL 值是特殊的;因为它代表“无值”。不可能以评估两个已知值的相同方式来将它与已知值进行评估。如果试图与通常的算术比较运算符一道使用NULL，其结果是未定义的：

为了进行NULL 值的搜索，必须采用特殊的语法。不能用= 或!= 来测试等于NULL 或不等于NULL，取而代之的是使用IS NULL 或IS NOT NULL 来测试。例如，因为我们将健在总统的死亡日期表示为NULL，那么可按如下语句查找健在的总统：

MySQL3.23 及以后的版本具有一个特殊的MySQL专有的比较运算符“ < = >”，即使是NULL 与NULL 的比较，它也是可行的。用这个比较运算符，可将前面的两个查询重写为：

3. 对查询结果进行排序

有时我们注意到，在一个表装入初始数据后，对其发布一条SELECT * FROM tbl_name查询，检索出的行与这些行 入的顺序是相同的。但不要认为这种情况是有规律的。如果在初始装入表后进行了行的删除和插入，就会发现服务器返回表的行次序被改变了。(删除记录在表中留下了未使用的“空位”，MySQL在以后插入新记录时将会试图对其填补。)缺省时，如果选择了行，服务器对返回行的次序不作任何保证。为了对行进行排序，可

使用ORDER BY 子句：

在ORDER BY 子句中，可在列名之后利用ASC 或DESC 关键字指定排序是按该列值的升序或降序进行的。例如，为了按倒序(降序)名排列总统名，可如下使用DESC：

如果在ORDER BY 子句中，对某个列名既不指定ASC 又不指定DESC，则缺省的次序为升序。在对可能包含NULL 值的列进行排序时，如果是升序排序， NULL 值出现在最前面，如果是按降序排序，NULL 值出现在最后。

查询结果可在多个列上进行排序，而每个列的升序或降序可以互相独立。下面的查询从president 表中检索行，并按出生的州降序、在每个州中再按姓氏的升序对检索结果进行排序：

4. 限制查询结果如果一个查询返回许多行，但您只想看其中的几行，则可以利用LIMIT 子句，特别是与ORDER BY 子句结合时更是如此。MySQL允许限制一个查询的输出为前n 行。下面的查询选择了5 位出生日期最早的总统：

如果利用ORDER BY birth DESC 按降序排序，将得到5 位最晚出生的总统。LIMIT 也可以从查询结果中取出中间部分。为了做到这一点，必须指定两个值。第一个值为结果中希望看到的第一个记录(第一个结果记录的编号为0 而不是1)。第二个值为希望看到的记录个数。下面的查询类似于前面那个查询，但只显示从第11行开始的5 个记录：

自MySQL3.23.2 以来，可按照一个公式来排序查询结果。例如，利用ORDER BYRAND( ) 与LIMIT 结合，从president 表中随机抽取一个记录：

5. 计算并命名输出的列值

前面的多数查询通过从表中检索值已经产生了输出结果。MySQL还允许作为一个公式的结果来计算输出列的值。表达式可以简单也可以复杂。下面的查询求一个简单表达式的值(常量)以及一个涉及几个算术运算符和两个函数调用的较复杂的表达式的值：

此查询把名和姓连接起来，中间间隔一个空格，将总统名形成一个单一字符串，而且将出生城市和州连接在一起，中间隔一个逗号，形成出生地。

在利用表达式来计算列值时，此表达式被用作列标题。如果表达式很长(如前面的一些查询样例中那样)，那么可能会出现一个很宽的列。为了处理这种情况，此列可利用AS name结构来重新命名标题。这样的名称为列别名。用这种方法可使上面的输出更有意义，如下所示：

6. 使用日期

在MySQL中使用日期时要记住的是，在表示日期时首先给出年份。1999 年7 月27 日表示为“1999 - 07 - 27”,而不是像通常那样表示为“ 07 - 27 - 1999”或“27 - 07 - 1999”。MySQL提供了几种对日期进行处理的方法。可以对日期进行的一些运算如下：

■ 按日期排序。(这点我们已经看到几次了。)

■ 查找特定的日期或日期范围。

■ 提取日期值的组成部分，如年、月或日。

■ 计算日期的差。

■ 日期增加或减去一个间隔得出另一日期。

下面给出一些日期运算的例子。

为了查找特定的日期，可使用精确的日期值或与其他日期值进行比较，将一个DATE 列与有关的日期值进行比较：

为了测试或检索日期的成分，可使用诸如YEAR( )、MONTH( ) 或DAYOFMONTH( ) 这样的函数。例如，可通过查找月份值为3 的日期，找出与笔者出生在相同月份(三月)的总统。

为了更详细，详细到天，可组合测试MONTH( ) 和DAYOFMONTH( ) 以找出在笔者的生日出生的总统：

这是一种可用来生成类似报纸上娱乐部分所刊登的那种“这些人今天过生日”清单的查询。但是，不必按前面的查询那样插入一个特殊的日期。为了查找每年的今天出生的总统，只要将他们的生日与C U R R E N T _ DATE 进行比较即可：

可从一个日期减去另一个日期。这样可以知道日期间的间隔，这对于确定年龄是非常有用的。例如，为了确定哪位总统活得最长，可将其逝世日期减去出生日期。为此，可利用函数TO _ DAYS( ) 将出生日期和逝世日期转换为天数，求出差，然后除以365 得出大概的年龄：

此查询中所用的FLOOR( ) 函数截掉了年龄的小数部分，得到一个整数。得出日期之差，还可以确定相对于某个特定日期有多长时间。这样可以告诉历史同盟的会员，他们还有多久就应该更新自己的会员资格了。计算他们的截止日期和当前日期之差，如果小于某个阈值，则不久就需要更新了。下面的查询是查找需要在60 天内更新的会员：

自MySQL3.22 以来，可使用DATE_ADD( ) 或DATE_SUB( ) 从一个日期计算另一个日期。这些函数取一个日期及时间间隔并产生一个新日期。例如：

本节中前面给出的一个查询选择70 年代逝世的总统，它对选择范围的端点使用直接的日期值。该查询可以利用一个字符串日期和一个由开始日期和时间间隔计算出的结束日期来重写：

会员更新查询可根据DATE_ADD( ) 写出如下：

本章前面给出了一个查询如下，确定不久要来检查但还没来诊所的牙科病人：

现在回过头来看，读者会更清楚这个查询的含义了。

7. 模式匹配

MySQL允许查找与某个模式相配的值。这样，可以选择记录而不用提供精确的值。为了进行模式匹配运算，可使用特殊的运算符( LIKE 和NOT LIKE)，并且指定一个包含通配符的串。字符“_”匹配任意单个字符，而“%”匹配任意字符序列(包括空序列)。使用L I K E或NOT LIKE 的模式匹配都是不区分大小写的。下列模式匹配以“W”或“w”开始的姓：

此查询给出了一个常见的错误，它对一个算术比较运算符使用了模式。这种比较成功的惟一可能是相应的 实包含串“ W %”或“w %”。下列模式匹配任意位置包含“W”或“w”的姓：

MySQL还提供基于扩展正规表达式的模式匹配。正规表达式在附录C 的REGEXP 运算符的介绍中描述。

8. 生成汇总

MySQL所能做的最有用的事情是浓缩大量的原始数据行并对其进行汇总。当学会了利用MySQL来生成汇总时，它就变成了用户强有力的好帮手了，因为手工进行汇总是一项冗长的、费时的、易出错的工作。汇总的一种简单的形式是确定在一组值中哪些值是唯一值。利用DISTINCT 关键字来删除结果中的重复行。例如，总统出生的各个州可按如下找出：

其他的汇总形式涉及计数，可利用COUNT( ) 函数。如果使用COUNT (*)，它将给出查询所选择的行数。如果一个查询无WHERE 子句，COUNT(*) 将给出表中的行数。下列查询给出共有多少人当过美国总统：

如果查询有WHERE 子句，COUNT(*) 将给出此子句选择多少行。下面的查询给出目前为止对班级进行了多少次测试：

COUNT(*) 对选中的行进行计数。而COUNT(col_name) 只对非NULL 值进行计数。下面的查询说明了这些差异：

这表示，总共有41位总统，他们中只有一个具有名字后缀，并且大多数总统都已去世。自MySQL3.23.2 以来，可以将COUNT( ) 与DISTINCT 组合对选择结果集中不同的值进行计数。例如，为了对总统出生的不同州进行计数，可执行下列查询：

可以根据汇总列中单独的值对计数值进行分解。例如，您可能根据下列的查询结果知道班级中所有学生的人数：

但是，有多少是男孩?有多少是女孩?分别得出男孩、女孩的一种方法是分别对每种性别进行计数：

虽然这个方法可行，但是它很繁锁而且并不真正适合于可能有许多不同的值的列。考虑一下怎样以这种方式确定每个州出生的总统人数。您不得不找出有哪些州，从而不能省略(SELECT DISTINCT state FROM president)，然后对每个州执行一个SELECT COUNT(*) 查询。很显然，有些事是可以简化的。所幸MySQL可以利用单个查询对一个列中不同的值进行计数。因此，针对学生表可以按如下得出男孩和女孩的人数：

如果以这种方法对值计数， GROUP BY 子句是必须的;它告诉MySQL在对值计数之前怎样进行聚集。如果将其省去，则要出错。COUNT(*) 与GROUP BY 一起用来对值进行计数比分别对每个不同的列值进行计数有更多的优点，这些优点是：

■ 不必事先知道要汇总的列中有些什么值。

■ 不用编写多个查询，只需编写单个查询即可。

■ 用单一查询就可以得出所有结果，因此可以对结果进行排序。

前两个优点对于更方便地表示查询很重要。第三个优点也较为重要，因为它提供了显示

结果的灵活性。在使用GROUP BY 子句时，其结果是在要分组的列上进行排序的，但是可以

使用ORDER BY 来按不同的次序进行排序。例如，如果想得到各州产生的总统人数，并按产

生人数最多的州优先排出，可以如下使用ORDER BY 子句：

如果希望进行排序的列是从计算得出的，则可以给该列一个别名，并在ORDER BY 子句中引用这个别名。前面的查询说明了这一点; COUNT(*) 列的别名为count。引用这样的列的另一种方法是引用它在输出结果中的位置。前面的查询可编写如下：

我不认为按位置引用列易读。如果增加、删除或重新排序输出列，必须注意检查ORDER BY子句，并且如果列号改变后还得记住它。别名就不存在这种问题。如果想与计算出来的列一道使用GROUP BY，正如ORDER BY 一样，应该利用别名或列位置来引用它。下面的查询确定在一年的每个月中出生的总统人数：

如果不想用LIMIT 子句来限制查询输出，而是利用查找特定的COUNT( ) 值来达到这个目的，可使用HAVING 子句。下面的查询给出了产生两个以上总统的州：

从更为普遍的意义上说，这是一种在要查找的列中重复值时执行的查询类型。HAVING 类似于WHERE，但它是在查询结果已经选出后才应用的，用来缩减服务器实际送到客户机的结果。除了COUNT( ) 外还有许多汇总函数。MIN( )、MAX( )、SUM( ) 和AVG( ) 函数在确定列的最大、最小、总数和平均值时都非常有用，甚至可以同时使用它们。下面的查询得出给定的测试和测验的各种数字特性。它还给出有多少学分参与了每个值的计算(有的学生可能缺旷或未计入)。

当然，如果您知道这些信息是来自测验的还是测试的，则它们就会更有意义。但是，为了产生那样的信息，还需要参考event 表;我们将在下一节“从多个表中检索信息”讨论这个查询。汇总信息是很有意思的，因为它们是那么有用，但不太好控制，容易走样。请看下列查询：

此查询选择已经去世的总统，按出生地对他们进行分组，并计算出他们逝世时的年龄，计算出平均年龄(每个州的)，然后按平均年龄进行排序。换句话说，此查询按所出生地确定已故总统的平均寿命。但这说明了什么呢?它仅仅说明您可写该查询，当然并不说明此查询是否值得写。并不是用一个数据库可以做的所有事情都同样有意义;但是，人们有时在发现可以利用自己的数据库进行查询时感到很开心。这可能说明关于转播运动会的不断增加的深奥的(空洞的)统计数据在过去几年里正在不断增多的原因。运动统计者可以使用他们的数据库来计算出某个队的历史纪录，而这些数字你可能感兴趣，也可能毫无兴致。

9. 从多个表中检索信息

到目前为止，我们所编写的查询都是从单个表中得到数据的。现在，我们将进行一件更为有趣的工作。以前笔者曾经提到过，关系DBMS 的强大功能在于它能够将一样东西与另一样东西相关联，因为这样使得能够结合多个表中的信息来解答单个表不能解答的问题。本节介绍怎样编写这种查询。在从多个表中选择信息时，需要执行一种称为连接( j o i n)的操作。这是因为需要将一个表中的信息与其他表中的信息相连接来得出查询结果。即通过协调各表中的值来完成这项工作。

我们来研究一个例子。在前面的“学分保存方案”小节中，给出了一个检索特定日期的测验或测试学分的查询，但没有解释。现在可以进行解释了。这个查询实际涉及到三种连接方法，因此我们分两步进行研究。第一步，我们构造一个对特定日期的学分进行选择的查询，如下所示：

此查询找出具有给定日期的记录，然后利用该记录中的事件ID 查找具有相同事件ID 的学分。对于每个匹配的事件记录和学分记录组合，显示学生ID、学分、日期和事件类型。此查询在两个重要方面不同于我们曾经编写过的其他查询。它们是：

■ FROM 子句给出了不止一个表名，因为我们要检索的数据来自不止一个表：

FROM event,score

■ WHERE 子句说明event 和score 表是由每个表中的event_id 值的匹配连接起来的：

where event.event_id=score.event_id

请注意，我们是怎样利用tbl_name.col_name 语法引用列，以便MySQL知道引用的是哪些表的列。(event_id 出现在两个表中，如果不用表名来限定它的话将会出现混淆。)此查询中的其他列( date、score、type)可单独使用而不用表名限定符，因为它们在表中只出现一次，从而不会出现含混。但是，一般在连接中我们对每个列都进行限定以便清晰地表示出每个列是属于哪个表。在完全限定的形式下，查询如下：

从现在起，我们将使用完全限定的形式。第二步，我们利用student 表完成查询以便显示学生名。(第一步中查询的输出给出了student_id 字段，但是名字更有意义。)名字显示是利用score 表和student 表两者都具有student_id 列，使它们中的记录可被连接这个事实来完成的。最终的查询如下：

此查询与前一个查询的差别在于：

■ student 表被增加到了FROM 子句中，因为除了event 表和score 表外还用到了它。

■ student_id 列现在不明确了(因为现在有两个引用到的表都含有此列)，因此必须限定为score.student_id 或student.student_id 以表明使用的是哪个表。

■ WHERE 子句有一个附加项，它说明根据学生ID 将score 表记录与student 表记录进行匹配。

■ 此查询是显示学生名而不是学生ID。(当然，如果愿意的话，可以两者都显示。)利用此查询，可以加入任意日期，得到该日期的学分，用学生名和学分类型完善查询结果。不一定要了解关于学生ID 或事件ID 的情况。MySQL小心地得出相关的ID 值并利用它们自动地使各表的行相配。

学分保存方案涉及的另一项工作是汇总学生的缺勤情况。缺勤情况是按学生ID 和日期在absence 表中记录的。为得到学生名(而不仅仅是ID)，我们需要根据student_id 的值将absence 表连接到student 表。下面的查询给出了学生的ID 号和名字以及缺勤计数：

注意：虽然我们在GROUP BY 子句中应用了一个限定符，但对于这个查询来说不是必须的。因为GROUP BY 子句只引用选择表中(此查询的前两行)的列。在该处只有一个名为student_id 的列，因此MySQL知道应该用哪个列。这个规则对ORDER BY 子句也成立。如果我们希望只了解哪些学生缺过勤，则此查询所产生的输出也是有用的。但是，如果我们将此清单交给学校办公室，他们可能会说，“其他的学生呢?我们需要每个学生的情况。”这是一个稍微有点不同的问题。它表示需要知道学生的缺勤数，即使没有缺勤的学生也需要知道。因为问题的不同，查询也应该不同。

为了解决上述问题，使用LEFT JOIN 而不涉及WHERE 子句中的学生ID。LEFT JOIN要求MySQL对从连接首先给出的表中选择每行生成一个输出行(即LEFT JOIN 关键字左边给出的表)。由于首先给出student 表，我们得到了每个学生的输出结果，即使是那些在absence 表中未给出的学生也都包括在输出中。此查询如下：

前面，在“生成汇总”一节中，我们执行了一个查询，它生成score 表中数据的数值特征。该查询的输出列出了事件ID，但不包括学分日期或类型，因为我们不知道怎样将score 表连接到event 表以得到学分的日期和类型。现在可以做到了。下面的查询类似于早先的那个，但是它给出了学分的日期和类型而不只是简单的数字事件ID：

可利用诸如COUNT( ) 和AVG( ) 这样的函数生成多个列上的汇总，即使这些列来自不同的表也是如此。下面的查询确定学分数，以及事件日期与学生性别的每种组合的平均学分。

我们可以使用一个类似的查询来完成学分保存方案的一个任务，即在学期末计算每个学生的总学分。相应的查询如下：

不一定要求连接必须用两个不同的表来完成。这似乎有点奇怪，但是确实可以将一个表连接到其自身。例如，可通过针对每个总统的出生地查看其他各个总统的出生地，确定几个总统是否出生在相同城市。此查询如下：

此查询有两个技巧性的东西：

■ 我们需要使用同一表的两个实例，因此建立了表的别名( p 1、p 2)，并利用它们无歧义地引用表列。

■ 每个总统的记录与自身相匹配，但是我们不希望在输出中看到同一总统出再现两次。WHERE 子句的第二行保证比较的记录为不同总统的记录，使记录不与自身匹配。可以编写一个查找出生在同一天的总统的类似查询。出生日期不能直接比较，因为那样会错过出生在不同年份的总统。我们用MONTH( ) 和DAYOFMONTH( ) 来比较出生日期的月和日，相应的查询如下：

利用DAYOFYEAR( ) 而不是MONTH( ) 和DAYOFMONTH( ) 将得出一个更为简单的查询，但是在比较闰年日期与非闰年日期时将会得出不正确的结果。迄今所执行的连接结合了来自那些在某种意义上具有逻辑关系的表中的信息，但是只有您知道该关系无意义。MySQL并不知道(或不关心)所连接的表相互之间是否相关。例如，可将event 表连接到president 表以找出在某个总统生日那天是否进行了测验或测试，此查询如下：

6.PHP下用户身份验证方式 篇六

在设计和维护网站的时候, 经常需要限制用户对某些重要文件或信息的访问。通常, 我们可以采用验证用户身份的方式来决定用户是否有权访问页面。验证用户身份是当今WEB应用程序常见的操作, 是网络中最基本、最常用的功能之一1。不仅是出于安全原因, 还要根据用户的首选项和类型来提供网站定制的功能。

2. 基本的HTTP身份验证概念

HTTP协议提供了一种非常简单、但很有效的用户验证方式。典型的验证过程如下:

(1) 客户请求了一个受限的资源。

(2) 服务器用一个401 (未授权访问) 响应消息对这个请求做响应。

(3) 客户端浏览器识别401响应, 弹出一个验证提示窗口。

(4) 用户在提示窗口中输入用户名和密码并发送给服务器进行验证。如果用户提供了正确了的凭证, 则允许访问, 否则访问被拒绝。

(5) 如果用户得到验证, 浏览器将在验证缓存中存储验证信息。

虽然HTTP验证可以有效地控制对受限资源的访问, 但它不能保证验证信息传输通道的安全。也就是说, 对于位置合适的攻击者而言, 偷窥或监视服务器与客户端之间发送的数据是轻而易举的。用户提供的用户名和密码都包含在这些传输数据中, 没有经过任何加密。因此, 为了消除这种方法可能带来的危险, 需要实现一个安全的通信通道, 一般是使用安全套接字完成的。

基于HTTP的身份验证方法的另一个主要缺点是, 我们必须关闭所有的浏览器窗口才能停止活动的领域授权。这意味着, 如果用户经过了验证, 但在离开终端时浏览器仍然处于打开状态, 其他人就可以通过劫持身份来获取机密数据。

3. PHP身份验证

在除了使用Web服务器内置的认证功能, 我们还可以使用简短的PHP代码来实现整个HTTP的验证过程。在PHP脚本中, 我们可以使用函数header () 直接给客户端的浏览器发送HTTP标头, 标头是服务器以HTTP协定传送HTML信息到浏览器前所送出的字串, 然后在客户端将会自动弹出用户名和密码输入窗口, 就可以实现我们的身份认证功能了。

在PHP中, 客户端用户输入的信息传送到服务器之后自动保存在$PHP_AUTH_USER, $PHP_AUTH_PW, 以及$PHP_AUTH_TYPE这三个全局变量中。利用这些变量, 我们可以根据实现保存在数据文件或者数据库中用户帐号信息来验证用户身份。

不过, 需要提醒使用者注意的一点是:只有在以模块方式安装的PHP中才能使用$PHP_AUTH_USER、$PHP_AUTH_PW和$PHP_AUTH_TYPE这三个变量。如果用户使用的是CGI模式的PHP则无法实现验证功能。

4. PHP验证方法

通过PHP脚本实现身份验证有多种方法。在具体应用时, 应当考虑验证需要的作用范围和复杂度。

4.1 硬编码的身份验证

要限制对资源的访问, 最简单的方法是直接在脚本中硬编码用户名和密码。如以下代码:

在这个例子中, 如果$_SERVER[‘PHP_AUTH_USER’]和$_SERVER[‘PHP_AUTH_PW’]两个变量有一个的值不等于’ADMIN’, 将无法通过验证, 浏览器会返回一个401未授权消息。

虽然使用硬编码验证十分方便快捷, 但这种方法存在一些缺点。首先, 对于当前的代码, 所有需要访问此资源的用户都必须使用相同的验证。在实际应用中, 通常每个用户都必须唯一标识, 这样才会有用户特定的首选项或资源。其次, 用户名和密码的修改只能在代码中进行, 并且只能手工调整, 可操作性较差。

4.2 基于文件的身份验证

通常在实际应用中需要为每个用户提供唯一的登录对, 这样可以记录用户特定的登录时间、活动和动作。我们可以利用文本文件轻松地实现。首先, 建立一个文本文件, 文件中每行包含一个用户名和加密密码对, 用户名和加密密码之间用冒号 (:) 分隔。在验证用户输入的登录信息时, 只须将文本文件读取到数组中, 然后循环处理数组, 搜索匹配的数据, 如找到匹配的行, 则通过验证, 如未找到, 则验证失败。

基于文件的身份验证有一个重要的安全考虑。那就是存储用户名和密码的文本文件应当存储在服务器文档根目录之外。否则, 攻击者就有可能通过强力猜测发现此文件, 泄露登录用户信息。

虽然基于文件的验证系统对于相对较小的静态验证列表非常实用, 但是如果需要处理很多用户, 或者要经常增、删、改用户, 这种方式就不太方便了。

4.3 基于数据库的身份验证

基于数据库的身份验证是目前功能最强大也是最常采用的验证方法。它不仅改善了管理的方便性, 提高了可扩展性, 而且可以集成到更大的数据库基础设施中。其验证方法实现起来非常简单, 只要对存储用户信息的表进行一个选择查询, 使用输入的用户名和密码作为查询条件即可2。相关代码如下:

4.4 基于IP的身份验证

上述三种验证方式全部采用用户名和密码的组合来验证用户身份, 这种组合并非无懈可击, 这个信息可以交给他人, 也可能被他人窃取, 还可能通过推理或强力手段猜出来。为解决这个问题, 进一步确保验证的正确性, 有时需要更高级别的访问限制来确定用户的合法性, 基于IP的身份验证即可满足这一需求。它不仅需要合法的用户名和密码, 还需要一个特定的IP地址。其实现方式亦很简单, 即在数据库验证的基础上, 对用户信息表进行修改, 增加IP信息, 查询时同时查询用户名、密码和IP地址即可。

虽然基于IP的身份验证在很大程度上满足了高级别的用户访问验证, 但专业攻击者一旦获得用户名和密码, 还是可以通过IP欺骗 (即欺骗网络使其认为数据是从某个特定的IP地址发出的) 来绕过基于IP的安全设施, 取得访问权限。

5. 其他验证方法

除上述验证方法外, 在进行应用程序设计时, 还可以采用cookies或session进行身份验证。但是用cookies进行验证, 在用户离线再上线后, 只要cookies不过期, 不用登录仍然可以保持在线, 这对于多用户共用一个计算机来说是个安全隐患。而采用session进行身份验证, session在用户浏览过程中不断的将访问信息加入到session中, 如果用户在网站内时间很长, 浏览的页面很多, 就会导致session越来越大, 浏览速度降低, 虽然这种情况不多见, 但不是我们所希望的。

6. 结束语

本文通过对PHP语言几种常见身份验证方式的讨论, 仔细分析了各种验证方式的优缺点, 使读者在进行PHP语言应用设计中, 采用何种身份验证有个比较清楚的了解。

参考文献

[1]肖亮, 基于ASP.NET的网络用户身份认证研究.昆明理工大学.2004.

[2]Luke Welling, Laura Thomson著, 武欣, 邵煜等译.PHP和MySQL Web开发.机械工业出版社, 2005.