07第七章SQL语言的应用

1isualFoxPro第七章26.1SQL语言概述6.2数据定义6.3数据查询6.4数据操纵3SQL被作为关系数据库的标准语言。SQL语句可以用来执行各种各样的操作。目前流行的关系数据库管理系统，如Oracle、Sybase、SQLServer、VisualFoxPro等都采用了SQL语言标准。SQL（1）SQL是一种一体化的语言,它集数据定义、数据查询、数据操纵和数据控制功能于一体，可以独立完成数据库的全部操作。（2）SQL语言是一种高度非过程化的语言。它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”。6.1SQL语言概述4（3）SQL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE。另外SQL的语法也非常简单，它很接近英语自然语言，因此容易学习和掌握。（4）SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用5VisualFoxPro在SQL方面支持数据定义、数据查询和数据操纵功能，由于VisualFoxPro自身在安全控制方面的缺陷，所以它没有提供数据控制功能。本章讲述VisualFoxPro中SQL的语法、功能与应用。66.2.1建立表结构CREATETABLE表名1(字段名1类型(宽度[,小数位数])[NULL|NOTNULL][CHECK条件表达式1[ERROR出错显示信息]][DEFAULT表达式1][PRIMARYKEY|UNIQUE][TAG标识1]，字段名2类型(宽度[,小数位数])……6.2数据定义7表名1：要建立的表的名称。字段名1、字段名2、……：所要建立的新表的字段名。宽度及小数位数：字段宽度及小数位数。NULL、NOTNULL：该字段是否允许“空值”，其默认值为NULL，即允许“空”值。CHECK条件表达式——用来检测字段的值是否有效，这是实行数据库的一种完整性检查。8ERROR出错显示信息——输入数据时，系统会自动检测所输入的字段值不满足条件表达式时，系统自动显示这里所提示的出错信息。DEFAULT表达式——为一个字段指定的默认值。PRIMARYKEY——指定该字段为关键字段，非数据库表不能使用该参数。UNIQUE——指定该字段为一个侯选关键字段。9从以上命令格式可以看出，用CREATETABLE命令建立表可以完成用表设计器完成的所有功能。除了建立表的基本功能外，它还包括满足实体完整性的主关键字（主索引）PRIMARYKEY、定义域完整性的CHECK约束及出错提示信息ERROR、定义默认值的DEFAULT等。10例1利用SQL命令建立3个表：学生表、选课表和课程表。建立学生表CREATEDATABASE学生管理createtable学生(学号c(11);primarykey,;姓名c(8),;入学成绩n(5,1);check(入学成绩0);error成绩应该大于零)11建立课程表。CREATETABLE课程(课程号C(5);PRIMARYKEY,课程名C(20),学分N(1))建立选课表。CREATETABLE选课(学号C(5),;课程号C(5),;成绩ICHECK(成绩=0and成绩=100);ERROR成绩值的范围0-100！;DEFAULT60)126.2.2删除表删除表的SQLDROPTABLE表名直接从磁盘上删除所指定的表文件。要删除数据库中的表时，最好应使数据库是当前打开的数据库，在数据库中进行操作。136.2.3修改表结构修改表结构的命令是ALTERTABLE，该命令有2种格式。格式1（向已存在的表结构中增加字段）ALTERTABLE表名1ADD[COLUMN]字段名字段类型[(宽度[,小数位数])][NULL|NOTNULL][CHECK逻辑表达式[ERROR出错显示信息]][DEFAULT表达式][PRIMARYKEY|UNIQUE]14例2为课程表增加一个整数类型的学时altertable课程add学时I;check学时1615格式2（修改已有表中字段的规则）ALTERTABLE表名ALTER[COLUMN]字段名[NULL|NOTNULL][SETDEFAULT表达式[SETCHECK逻辑表达式[ERROR出错显示信息]][DROPDEFAULT][DROPCHECK]该格式命令主要用于定义、修改和删除有效性规则以及默认值定义。例3删除学时字段的有效性规则。ALTERTABLE课程;ALTER学时DROPCHECK16格式3（删除表中字段，更改字段名等）ALTERTABLE表名[DROP[COLUMN]字段名][SETCHECK逻辑表达式[ERROR出错显示信息]][DROPCHECK][ADDPRIMARYKEY字段表达式TAG标识名][DROPPRIMARYKEY]….[RENAMECOLUMN旧字段名TO新字段名]]该格式命令主要用于删除字段,修改字段名;定义、修改和删除表一级的有效性规则（主键、外键等）例3删除“学时”字段。ALTERTABLE课程;DROP[COLUMN]学时例4改“单位”为“开课系别”ALTERTABLE课程；Renamcolumn单位to开课系别17VisualFoxPro的SQLSELECT命令的语法格式是：SELECT选项1[AS显示列名],选项2[AS显示列名]…FROM表名JOIN表名ON联接条件…WHERE联接条件1[AND联接条件2…]GROUPBY分组列名1[HAVING过滤条件]ORDERBY排序选项1[，排序选项2][ASC|DESC][INTO目标|[TOFILE文件名]6.3数据查询186.3.1基本查询所谓基本查询是指无条件查询，其格式是：SELECT[DISTINCT]选项1,选项2……FROM表名其中DISTINCT表示输出无重复结果的记录。选项可以是字段名、表达式或函数。表名代表要查询的表。19例4列出学生名单。SELECT*FROMSTUD命令中的*表示输出所有字段，数据来源是学生表，所有内容以浏览方式显示。例5列出所有选课的学生，去掉重复的学号。SELECTDISTINCT学号AS选课学生;fromSCSELECT命令中的选项，不仅可以是字段名，还可以是表达式，也可以是一些函数。20例6将所有的学生入学成绩四舍五入，只显示学号、姓名和入学成绩。SELECT学号,姓名,；ROUND(入学成绩,0)AS入学成绩；FROMSTUD例7求出所有学生的入学成绩平均分。SELECTAVG(入学成绩)AS平均分；FROMSTUD由以上两例可见，直接使用VisualFoxPro提供的各种SQL函数在输出时进行计算，便可得到相应的输出结果。216.3.2带条件查询WHERE是条件语句关键字，是可选项，其格式是：WHERE条件表达式其中条件表达式可以是单表的条件表达式，也可以是多表之间的条件表达式。例8求出男同学的入学成绩平均分。SELECTAVG(入学成绩);AS平均分FROMstud;WHERE性别=男22条件表达式是指查询的结果集合应满足的条件，如果某行条件为真就包括该行记录。下面是可用于条件表达式中几个特殊运算符的意义和使用方法。字段BETWEEN范围始值AND范围终值字段IN结果集合或者字段IN(子查询)字段LIKE字符表达式23例9列出系别代号为“01”和“03”的学生名单。SELECT学号,姓名,系别代号;FROMstud;WHERE系别代号IN(“01,“03)SELECT学号,姓名,系别代号;FROMstud;WHERE系别代号=“01OR系别代号=“0324例10列出入学成绩在560分到650分之间的学生名单。SELECT学号,姓名,入学成绩;FROMstud;WHERE入学成绩BETWEEN560AND650SELECT学号,姓名,入学成绩;FROMstud;WHERE入学成绩=560；AND入学成绩=65025例11SELECT学号,姓名;FROMstud;WHERE姓名LIKE刘%SELECT学号,姓名;FROMstud;WHERE姓名=刘266.3.3嵌套查询有时候一个SELECT命令无法完成查询任务，需要一个子SELECT的结果作为条件语句的条件，即需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令，这种查询称为嵌套查询。271.返回单值的子查询例12列出选修“计算机应用”的学生的学号。SELECT学号FROMSC；WHERE课程号=;(SELECT课程号FROMKC；WHERE课程名=″计算机应用″)上述SQL语句首先在KC表中找出“计算机应用”的课程号（比如“KC0003”），然后再在SC表中找出课程号等于“KC0003”的记录，列出这些记录的学号。282.返回一组值的子查询若某个子查询返回值不止一个，则必须指明在WHERE子句中应怎样使用这些返回值。通常使用条件IN。例13列出选修“计算机应用”或“法律概论”的所有学生的学号。select学号fromsc;where课程号in;(select课程号fromkc;where课程名=计算机应用or;课程名=法律概论)296.3.4多表查询在一个表中进行查询，一般说来是比较简单的，而在多表之间查询就比较复杂，必须处理表和表间的联接关系。使用SELECT命令进行多表查询是很方便的。等值联接等值联接是按对应字段的共同值将一个表中的记录与另一个表中的记录相联接。30例14输出所有学生的成绩单，要求给出学SELECTstud.学号,姓名,课程号,成绩;FROMSTUD,SC;WHERESTUD.学号=SC.学号31例15列出女生的选课情况，要求列出学号、姓名、课程名和学分。selecta.学号,姓名,课程名,学分;Fromstuda,scb,kcc;wherea.学号=b.学号;andb.课程号=c.课程号;and性别=女326.3.5联接查询VisualFoxPro提供的SELECT命令，在FROM子句中提供一种称之为联接的子句。联接分为内部联接和外部联接。外部联接又分为左外联接、右外联接和全外联接。1.内部联接（InnerJoin）实际上，上面例子全部都是内部联接。所谓内部联接是指包括符合条件的每个表格中的记录。也就是说是所有满足联接条件的记录都包含在查询结果中。33例16输出所有学生的成绩单，要求给出学号、姓名、课程号和成绩。（连接格式1）SELECTa.学号,姓名,课程号,成绩;FROMSTUDa,SCb;WHEREa.学号=b.学号可以用内部联接：（连接格式2）selecta.学号,姓名,课程号,成绩;fromstudainnerjoinscb;ona.学号=b.学号346.3.6查询结果处理1.排序输出（ORDER）SELECT的查询结果是按查询过程中的自然顺序给出的，因此查询结果通常无序，如果希望查询结果有序输出，需要ORDERBY子句配合。35例17按性别顺序列出学号、姓名、性别、课程名及成绩，性别相同的按成绩由高到低排序。SELECTSTUD.学号,姓名,性别,课程名,成绩;FROMSTUDJOINSCJOINKC;ONSC.课程号=KC.课程号;ONSTUD.学号=SC.学号;ORDERBY性别ASC,成绩DESC362.重定向输出（INTO或TO）INTO是可选项，表示查询结果可以重定输出方向，其格式如下INTO目标“目标”为有如下31.ARRAY数组名：将查询结果存到指定数组中。2.CURSOR临时表：将输出结果存到一个临时表，这个表的操作与其他表一样，不同的是，一旦被关闭就被删除3.DBF|TABLE表：将结果存到一个表。TO目标1.TOFILE文件名2.