第4章结构查询语言

第4章结构化查询语言4.1SQL概述4.2数据查询4.3数据操作4.4数据定义4.2数据查询4.2.1select命令基本格式SELECT<*>|[DISTINCT]<查询项>FROM<数据表>[WHERE<条件>][GROUPBY<分组表达式>[HAVING<筛选条件>][ORDERBY<排序项>[ASC|DESC]INTO<目的地>SELECT命令的基本结构是SELECT…FROM…WHERE，它包含输出字段…数据来源…查询条件。其中SELECT和FROM是必须的。说明：（1）SELECT子句:*表示选出的记录中包括重复记录，DISTINCT表示选出的记录中不包括重复记录。（2）FROM子句及其选项：用于指定查询的表。（3）WHERE子句：WHERE子句中指定筛选条件。（4）GROUPBY子句：对记录按<组表达式>值分组，常用于分组统计。（5）HAVING子句：当含有GROUPBY子句时，HAVING子句可用做记录查询的限制条件。（6）ORDERBY子句：指定查询结果中记录按<表达式>排序，默认升序。4.2.2简单查询主要基于单个表，可以有简单的查询条件。这样的查询用SELECT和FROM短语构成无条件查询。或由SELECT、FROM和WHERE短语构成条件查询。1.查询部分字段例例：

找出成绩表中学生的学号、姓名、计算机成绩。SELECT学号,姓名,计算机FROM成绩表2.使用distinct短句Distinct的作用是去掉查询结果的重复值。例：列出所有学生姓名，去掉重名。SELECTDISTINCT姓名AS学生名单FROM学生3.查询所有字段例：列出所有学生信息。SELECT*FROM学生&&“*”是通配符，表示所有字段4.条件查询格式：select<数据项>from数据表where<条件表达式>例：列出所有姓陈的学生名单。SELECT姓名,学号FROM学生WHEREleft(名,2)=“陈”4.2.3带特殊运算符的条件查询WHERE子句中的条件表达，除了使用关系比较运算和逻辑运算符号之外，还可以使用特殊运算符号，如IN、BETWEEN…AND…和LIKE等。Between…and…在”…”和”…”之间例：

在学生表中检索出入学成绩在500~520分范围内的学生信息。SELECT*FROM学生WHERE入学成绩BETWEEN500AND520这个查询条件等价与:(入学成绩>=500)and(入学成绩<=520)2.LikeLike是字符串匹配运算符，通配符”%”表示0个或多个字符，另外还有一个通配符”-”表示一个字符。例:从学生表中检索出全部姓李的信息。SELECT*FROM学生WHERE姓名LIKE"李%“3.InIn运算符后面接一个集合，集合形式为（元素1，元素2，元素3，….)例:从学生表中检索出福建、江西、湖南籍学生的信息SELECT*FROM学生WHERE姓名LIKE"李%“4.not4.2.4简单的计算查询COUNT——计数SUM——求和AVG——计算平均值MAX——计算最大值MIN——计算最小值例：将所有的学生数学成绩四舍五入，只显示学号、姓名和数学成绩SELECT

学号，姓名，ROUND(数学,0)AS数学成绩FROM

学生例：求出所有学生的数学成绩平均分、最高分、最低分。

SELECTAVG(数学)AS数学成绩平均分,MAX(数学)AS数学成绩最高分,MIN(数学)AS数学成绩最低分FROM学生4.2.5.分组统计（GROUP）查询查询结果可以分组，其格式是：GROUPBY<分组选项1>[,<分组选项2>…]其中<分组选项>可以是字段名，SQL函数表达式，也可以是列序号（最左边为1）。例分别统计男女人数。SELECT性别,COUNT(性别)FROM学生GROUPBY性别例分别统计男女中少数民族学生人数。SELECT性别,COUNT(性别)FROM学生GROUPBY性别WHERE少数民族否4.2.6排序查询输出ORDERBY短语的格式如下：ORDERBY<排序项1>[ASC|DESC][,<排序项2>[ASC|DESC]…]可以按一列或多列排序，ASC：升序，DESC：降序。默认为ASC。例例对学生表，请输出数学成绩最高的前五名学生的信息SELECT*TOP5FROM学生ORDERBY数学DESC4.2.7简单联接查询（多表查询）

联接查询是基于多个表的查询。表之间的联系是通过字段值来体现的，这种字段通常称为联接字段。1、等值联接与非等值联接查询当联接运算符为“=”时，称为等值联接，使用其他运算符（>、<、>=、<=、！）时称为非等值联接。12例：查询所有学生的成绩单，要求给出学号、姓名、性别、计算机成绩、数学成绩。SELECTa.学号,a.姓名,b.计算机,b.数学FROM学生a,成绩表b

WHEREa.学号=b.学号其中：a.学号=b.学号是联接条件注：短语FROM学生a,表示选择学生表，并将学生表的别名设为a;短语SELECTa.学号，表示取学生表的学号字段。说明：·当FROM之后有多个关系时，各个关系相互之间肯定有一种联系，否则无法构成检索表达式。

当FORM指定的不同关系中含有相同的属性名时，必须加表名作为属性名的前缀，以区别属性所属的关系。例如“a.学号”，“b.学号”。2.自联接查询一个表与其自已进行联接，称为自身联接。例：列出成绩表中，计算机成绩高于数学成绩的学生的学号、计算机成绩和数学成绩。

SELECTa.学号,a.计算机,a.数学FROM成绩表a,成绩bWHEREa.计算机>b.数学

ANDa.学号=b.学号

注：在自联接时，对数据表必须使用别名。4.2.8超连接查询

VisualFoxPro提供的SELECT命令，在FROM子句中提供一种称之为联接的子句。联接分为内部联接和外部联接。外部联接又分为左外联接、右外联接和全外联接。

格式为：Select…from<数据表1>innerjoin|leftjoin|rightjoin|fulljoin<数据表2>on条件

1.内部联接（InnerJoin）实际上，上面例子全部都是内部联接。所谓内部联接是指包括符合条件的每个表格中的记录。也就是说是所有满足联接条件的记录都包含在查询结果中。例

列出少数民族学生的学号、姓名及计算机成绩。SELECTa.学号,a.姓名,b.计算机FROM学生a,成绩表bWHEREa.学号=b.学号ANDa.少数民族否如采用内部联接方式？SELECTa.学号,a.姓名,b.计算机FROM

学生aINNERJOIN成绩表bONa.学号=b.学号WHEREa.少数民族否所得到的结果完全相同2.外部联接（OuterJoin）(1)左外联接也叫左联接（LeftJoin），其系统执行过程是左表的某条记录与右表的所有记录依次比较，若有满足联接条件的，则产生一个真实值记录。若都不满足，则产生一个含有NULL值的记录。接着，左表的下一记录与右表的所有记录依次比较字段值，重复上述过程，直到左表所有记录都比较完为止。联接结果的记录个数与左表的记录个数一致。(左表记录全部显示，右表只显示满足条件的记录）SELECTa.学号,a.姓名,b.计算机FROM

学生aleftJOIN成绩表bONa.学号=b.学号（2）右外联接也叫右联接（RightJoin），其系统执行过程是右表的某条记录与左表的所有记录依次比较，若有满足联接条件的，则产生一个真实值记录；若都不满足，则产生一个含有NULL值的记录。接着，右表的下一记录与左表的所有记录依次比较字段值，重复上述过程，直到左表所有记录都比较完为止。联接结果的记录个数与右表的记录个数一致。。(右表记录全部显示，左表只显示满足条件的记录）SELECTa.学号,a.姓名,b.计算机FROM

学生arightJOIN成绩表bONa.学号=b.学号（3）全外联接也叫完全联接（FullJoin），其系统执行过程是先按右联接比较字段值，然后按左联接比较字段值，重复记录不记入查询结果中。SELECTa.学号,a.姓名,b.计算机FROM学生afullJOIN成绩表bONa.学号=b.学号

6.3.3嵌套查询有时候一个SELECT命令无法完成查询任务，需要一个子SELECT的结果作为条件语句的条件，即需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令，这种查询称为嵌套查询。通常把仅嵌入一层子查询的SELECT命令称为单层嵌套查询，把嵌入子查询多于一层的查询称为多层嵌套查询。VisualFoxPro只支持单层嵌套查询。1.返回单值的子查询例列出计算机成绩为85分的所有学生的籍贯。select姓名,籍贯fromh:\vfp\学生where姓名in(sele姓名fromh:\vfp\成绩表where计算机=85)selea.姓名,a.籍贯fromh:\vfp\学生a,h:\vfp\成绩表bwherea.姓名=b.姓名andb.计算机=85202.返回一组值的子查询若某个子查询返回值不止一个，则必须指明在WHERE子句中应怎样使用这些返回值。通常使用条件ANY（或SOME）、ALL和IN（1）ANY运算符的用法例:列出选修“计算机”课的学生中期末成绩比选修“数学”的最低成绩要高的学生的学号和计算机成绩及数学成绩。SELECT学号,计算机,数学FROM成绩表WHERE计算机>ANY

(SELECT数学FROM成绩表)（2）ALL运算符的用法例:例:列出选修“计算机”课的学生中期末成绩比选修“数学”的最高成绩还要高的学生的学号和计算机成绩及数学成绩。SELECT学号,计算机,数学FROM成绩表WHERE计算机>all

(SELECT数学FROM成绩表)（3）IN运算符的用法例:列出江西籍或福建籍的所有学生的姓名、计算机成绩、数学成绩。SELECT姓名、计算机、数学FROM成绩表WHERE籍贯IN;(SELECT籍贯FROM学生WHERE籍贯=″江西″OR籍贯=″福建″)注：IN是属于的意思，等价于“=ANY”，即等于子查询中任何一个值。输出合并是指将两个查询结果进行集合并操作，其子句格式是：[UNION[ALL]<SELECT命令>]其中ALL表示结果全部合并。若没有ALL，则重复的记录将被自动取掉。合并的规则是：（1）不能合并子查询的结果。（2）两个SELECT命令必须输出同样的列数。（3）两个表各相应列出的数据类型必须相同，数字和字符不能合并。（4）仅最后一个<SELECT命令>中可以用ORDERBY子句，且排序选项必须用数字说明。4.2.11.输出合并（UNION）例列出“福建”籍或计算机成绩为85分所有学生的学号及姓名。SELECT学号,姓名FROM学生WHERE籍贯=江西UNION

SELECT学号,姓名FROM成绩表WHERE计算机=85各子句的含义：①<目标>有如下3种形式：a.ARRAY<数组名>。将查询结果存到指定数组名的内存变量数组中。b.CURSOR<临时表>。将输出结果存到一个临时表。c.DBF<表>|TABLE<表>。将结果存到一个表，如该表已经打开，则系统自动关闭它。如执行SETSAFETYOFF,则重新打开。没有指定后缀，则默认为.dbf。②TOFILE<文件名>[ADDITIVE]将结果输出到指定文本文件，ADDITIVE表示将结果添加到文件后面。③TOPRINTER将结果送打印机输出。4.2.12查询结果输出命令：[INTO<目标>|[TOFILE<文件名>[ADDITIVE]|TOPRINTER]例将上例的查询结果保存到test1.txt文本文件中。SELECTa.学号,a.姓名,a.性别,c.课程名,b.成绩FROM学生a,选课b,课程c;WHEREa.学号=b.学号ANDb.课程号=c.课程号ORDERBYa.性别,c.课程名,b.成绩DESCTOFILEtest1

例查询学生所学课程和成绩，输出学号、姓名、课程名和成绩，并将查询结果存入testtable表中。SELECTa.学号,a.姓名,b.课程号,b.成绩FROM学生a,选课bWHEREa.学号=b.学号INTOCURSORtestSELECTa.学号,a.姓名,b.课程名,a.成绩FROMtesta,课程bWHEREa.课程号=b.课程号INTOTABLEtesttableORDERBYa.学号VisualFoxPro支持两种SQL插入命令，其格式是：格式1：INSERTINTO<表名>[(字段名1[<字段名2>[,…]])]VALUES(<表达式1>[,<表达式2>[,…]])该命令在指定的表尾添加一条新记录，其值为VALUES后面表达式的值。当需要插入表中所有字段的数据时，表名后面的字段名可以缺省，但插入数据的格式及顺序必须与表的结构完全吻合；若只需要插入表中某些字段的数据，就需要列出插入数据的字段名，当然相应表达式的数据位置应与之对应。4.3数据操纵4.3.1插入记录例向学生表中添加记录。INSERTINTO学生(学号,姓名)VALUES(″231109″,″李成功″)格式2：INSERTINTO<表名>FROMARRAY<数组名>|FROMMEMVAR]

该命令在指定的表尾添加一条新记录，其值来自于数组或对应的同名内存变量。例已经定义了数组A（5），A中各元素的值分别是：AA（1）=″231013″，A（2）=″张阳″，A（3）=″女″，A（4）={^1988-09-10}，A（5）=.T.。利用该数组向学生表中添加记录。INSERTINTO学生FROMARRAYA

在VisualFoxPro中，DELETE可以为指定的数据表中的记录加删除标记。命令格式是：DELETEFROM[<数据库名>!]<表名>[WHERE<条件表达式>该命令从指定表中，根据指定的条件逻辑删除记录。例将“学生”表所有男生的记录逻辑删除。DELETEFROM学生WHERE性别=″男″4.3.2删除记录

4.3.3更新记录格式：UPDATE[<数据库名>!]<表名>SET<字段名1>=<表达式1>[,<字段名2>=<表达式2>…][WHERE<逻辑表达式>]功能：更新记录时对存储在表中的记录进行修改例将“学生”表中姓名为杨阳的学生的外语成绩改为93。UPDATE学生SET外语=93WHERE姓名=“杨阳”例所有男生的各科成绩加20分

UPDATE选课SET成绩=成绩+20WHERE学号IN(SELECT学号FROM学生WHERE性别=男)4.4数据定义4.4.1建立数据表格式：CREATETABLE|DBF<表名1>[NAME<长表名>][FREE](<字段名1><类型><宽度>[,<小数位数>])[NULL|NOTNULL][CHECK<条件表达式1>[ERROR<出错显示信息>]][DEFAULT<表达式1>][PRIMARYKEY|UNIQUE]REFERENCES<表名2>[TAG<标识1>][<字段名2><类型>(<宽度>[,<小数位数>])[NULL|NOTNULL][CHECK<条件表达式2>[ERROR<出错显示信息>]][DEFAULT<表达式2>][PRIMARYKEY|UNIQUE]REFERENCES<表名3>[TAG<标识2>参数说明：FREE：指明所创建的表为自由表。NULL、NOTNULL——该字段是否允许“空值”，其默认值为NULL。CHECK<条件表达式>——用来检测字段的值是否有效，这是实行数据库的一种完整性检查。ERROR<出错显示信息>——当完整性检查有错误，即条件表达式的值为假时的提示信息。DEFAULT<表达式>——为一个字段指定的默认值。PRIMARYKEY——指定该字段为关键字段，它能保证关键字段的唯一性和非空性，非数据库表不能使用该参数。UNIQUE——指定该字段为一个侯选关键字段。注意，指定为关键或侯选关键的字段都不允许出现重复值，这称为对字段值的唯一性约束。REFERENCES<表名>——这里指定的表作为新建表的永久性父表，新建表作为子表。TAG<标识>——父表中的关联字段，若缺省该参数，则默认父表的主索引字段作为关联字段。2023/2/435数据类型说明字段类型字段宽度小数位说明CN-字符型字段的宽度位ND--日期型(Date)T--日期时间型(Datetime)NND数值字段类型(Numeric)，宽度位N,小数位DFND浮点数值字段类型(Float)，宽度位N,小数位DI--整数类型(Integer)B-D双精度类型(Double)Y--货币型(Currency)L--逻辑型(Logic)M--备注型(Memo)G--通用型(General)2023/2/4操作步骤如下：（1）用CREATE命令建立数据库。CREATEDATABASEf:\vfp\图书管理（2）用CREATE命令建立读者表。createtablef:\vfp\读者(读者证号c(3)primkey,姓名c(8),性别c(2)check性别='男'or性别='女'error"性别只能为男或为女"default"男",身份c(8),电话号码c(11))例

利SQL命令建立图书管理数据库，其中包含3个表：读者表、图书表和借阅表。2023/2/4（3）建立图书表。createtablef:\vfp\图书(条形码c(8)primkey,书名c(40),分类号c(20),作者c(20),出版时间d)（4）建立借阅表createtablef:\vfp\借阅(条形码c(8)reference图书,读者证号c(3)reference读者,借阅时间d,还书时间d)4.4.2修改数据表修改表结构的命令是ALTERTABLE，该命令有一下几种种格式：1.增加字段ALTERTABLE<表名1>ADD[COLUMN]<字段名><字段类型>[(<宽度>[,<小数位数>])][NULL|NOTNULL][CHECK<逻辑表达式>[ERROR<出错显示信息>]][DEFAULT<表达式>][PRIMARYKEY|UNIQUE][REFERENCES<表名2>[TAG<标识名>]]例：为成绩表增加一个数值型的数学分析字段。ALTERTABLE成绩表ADD数学分析n（6，1）CHECK数学分析>=0and数学分析<=100ERROR″学时应该大于16！″)2.修改字段ALTERTABLE<表名1>ALTER[COLUMN]<字段名><字段类型>[(<宽度>[,<小数位数>])][NULL|NOTNULL][CHECK<逻辑表达式>[ERROR<出错显示信息>]][DEFAULT<表达式>][PRIMARYKEY|UNIQUE][REFERENCES<表名2>[TAG<标识名>]]OPENDATABASE图书管理ALTERTABLE学生ALTER

学号C(10）例：将图书表中条形码字段的宽度由原来的8改为10命令可以修改字段的类型、宽度、有效性规则、错误信息、默认值，定义主关键字和联系等；但是不能修改字段名.3.定义和删除字段的有效性规则、默认值ALTERTABLE<表名>ALTER[COLUMN]<字段名>[NULL|NOTNULL][SETDEFAULT<表达式>[SETCHECK<逻辑表达式>[ERROR<出错显示信息>]][DROPDEFAULT][DROPCHECK]该格式命令主要用于定