全国计算机等级考试二级VFP教程——第三章.ppt

第三章 关系数据库标准语言SQL,第一节 SQL概述,什么是SQL语言 SQL（Structured Query Language，结构化查询语言）是美国国家标准局ANSI确认的关系型数据库语言的标准。VFP支持SQL。VFP的SQL命令使用Rushmore技术来优化性能，并且一个SQL命令可以代替多个VFP命令。因此在VFP中，应尽可能采用SQL命令来代替一般的VFP命令。,第一节 SQL概述,SQL语言主要特点 SQL语言是一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等功能，可以完成数据库活动的全部工作。 SQL语言是一种高度非过程化的语言，它没有必要一步步地告诉计算机“如何”去做，只需要描述清楚用户“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。 SQL语言非常简洁，它接近自然语言（英语），因此容易学习和掌握。 SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。,第一节 SQL概述,SQL语言分类的命令动词,第一节 SQL概述,第三章 关系数据库标准语言SQL,第二节 SQL语言定义功能表的定义,CREATE TABLE命令是创建一张数据库表或自由表。 语法： CREATE TABLE 表文件名; NAME 长表名 FREE ; (字段名1 字段类型 (宽度 , 小数位数 ) ; CHECK 逻辑表达式 1 ; ERROR 逻辑表达式2 ; DEFAULT 逻辑表达式3 ; NULL|NOT NULL; primary key; , 字段名2 字段类型 ),第二节 SQL语言定义功能表的定义,使用SQL建立表的语句建立一个与自由表“金牌榜”结构完全一样的自由表golden.dbf。请将该SQL语句存储在文件one.prg中，否则不得分。金牌榜表结构为金牌榜（国家代码 c 3，金牌数 i，银牌数 i，铜牌数 i） create table golden (国家代码 c(3), 金牌数 i, 银牌数 i,铜牌数 i),第二节 SQL语言定义功能表的定义,create table 销售明细表(顺序号 c(6) primary key,日期 d,商品号 c(6),商品名 c(10),金额 n(10,2),第二节 SQL语言定义功能表的定义,建立一个“工资表”（各字段类型和宽度与“教师表”的对应字段相同），其中职工号为关键字。教师表（职工号 字符型8，姓名 字符型 6，工资 数值型 10,2 ） CREATE TABLE 工资表(职工号 C(8) primary KEY,姓名 C(6) ,工资 N(10,2),第二节 SQL语言定义功能表的定义,建立一个仓库1表，其中字段仓库号为字符型宽度是5，主索引，城市字段是字符型宽度是10，面积字段是整型，且该字段必须大于0，提示信息为“面积应该大于0！”。 Create table 仓库1(仓库号 c(5) primary key,城市 c(10),面积 I check (面积0) error “面积应该大于0！”),第二节 SQL语言定义功能表的定义,建立一个职工1表，其中仓库号字段是字符型，宽度是5，职工号是字符型，宽度5，主索引，工资是整型，该字段在1000至5000之间，提示信息是“工资值的范围在1000-5000！”，缺省值为1200，并建立于仓库1表的永久性关系。 Create table 职工1(仓库号 c(5),职工号 c(5) primary key,工资 I check(工资=1000 and 工资=5000) error “工资值的范围在1000-5000！” default 1200,foreign key 仓库号 tag 仓库号 references 仓库1),第二节 SQL语言定义功能表的定义,使用create table建立表并建立永久性关系的方法（假设A表是主表，B表是子表。） 首先使用create table建立A表，并建立A表的公共字段为主索引。然后建立B表，并使用foreign key 表达式 来建立一个普通索引，使用tag 索引名 references A表名 来建立联系。,第二节 SQL语言定义功能表的定义,命令中还有一些关键字或短语的意义： name 长表名 为建立的表指定一个长表名 free 建立的表不添加到当前数据库中，即建立一个自由表。 null 或 not null 说明字段允许或不允许为空值。 unique 说明建立候选索引 from array 数值名 说明根据指定数组的内容建立表。 注意：如果当前没有打开数据库，那么使用create table 建立的表为自由表，如果当前打开了某个数据库，不带free 关键字的create table 命令建立的表为该数据库表。,第二节 SQL语言定义功能表的定义,第三章 关系数据库标准语言SQL,第三节 SQL语言定义功能表结构的修改,Alter table 是修改表结构的命令，它的结构比较复杂，只能通过例题的方式去记忆。 使用SQL语句为表“金牌榜”增加一个字段“奖牌总数”（整数型），同时为该字段设置有效性规则：奖牌总数=0。请将该SQL语句存储在文件one.prg中，否则不得分。 alter table 金牌榜 add 奖牌总数 I check 奖牌总数=0,第三节 SQL语言定义功能表结构的修改,将score表的“成绩”字段的名称修改为“考试成绩”。 alter table score rename 成绩 to 考试成绩 使用SQL命令（alter table ）为student 表建立一个候选索引，索引名和索引表达式都是“学号”，并将相应的SQL命令保存在three.prg文件中。 alter table student add unique 学号 tag 学号,第三节 SQL语言定义功能表结构的修改,使用SQL语句alter table unique将“课程表”中的“课程号”定义为候选索引，索引名是temp，并将该语句存储在文件one.prg中，否则不给分。 alter table 课程表 add unique 课程号 tag temp,第三节 SQL语言定义功能表结构的修改,使用SQL的语句alter table命令为“评委表”的“评委编号”字段增加有效性规则：评委编号的最左边两字符是11（使用left函数），并将该SQL语句存储在three.prg中，否则不得分。 alter table 评委表 alter 评委编号 set check left(评委编号,2)=“11“,第三节 SQL语言定义功能表结构的修改,使用SQL语句为“教师表”的“职工号”字段增加有效性规则：职工号的最左边四位字符是“1102”，并将改SQL语句存储在three.prg中，否则不得分。 alter table 教师表 alter 职工号 set check left(职工号,4)=“1102“,第三节 SQL语言定义功能表结构的修改,将“订购单1”表的“订购单号”字段的宽度改为6（原来为5）。 alter table 订购单1 alter 订购单号 c(6) 删除订购单1表总金额字段的有效性规则。 alter table 订购单1 alter 总金额 drop check,第三节 SQL语言定义功能表结构的修改,删除订购单1表中的金额字段。 alter table 订购单1 drop 金额 为订购单1表建立候选索引，先按职工号排序，再按供应商号排序，索引名为emp_sup。 alter tabel 订购单1 add unique 职工号+供应商号 tag emp_sup 删除订购单1表的候选索引emp_sup alter table 订购单1 drop unique tag emp_sup,第三节 SQL语言定义功能表结构的修改,第三章 关系数据库标准语言SQL,第四节 SQL语言定义功能表的删除,Drop table 表名 删除数据库表 要删除数据库表，必须先打开该数据库，然后使用该命令，就把数据库中的信息和磁盘上的和该表相关的文件都删除。 如果没有打开所属数据库，使用了该命令，则会删除表相关的文件，但数据库中会留有该表的信息。 删除自由表 直接使用该命令删除。,第四节 SQL语言定义功能表的删除,第三章 关系数据库标准语言SQL,第五节 SQL语言操作功能插入数据,INSERTSQL命令 语法：INSERT INTO 表文件名 ； (字段名1 , ) VALUES (表达式1 , ) 功能：在表的末尾添加一条记录。 说明： 字段名可以是表中的部分或全部字段，缺省时指全部字段； VALUES中的表达式是字段的值，必须与字段名列表顺序一致（字段名缺省时，必须与表中字段顺序一致），且数据类型相匹配。,第五节 SQL语言操作功能插入数据,向教师表（姓名c,职称c,原工资n(10,2),新工资n(10,2)）插入一条新记录，该教师的信息：姓名“林红”，职称“讲师”，原工资10000，新工资10200，同时将SQL语句存储于新建的文本文件teacher.txt中。 insert into 教师 values(“林红“,“讲师“,10000,10200),第五节 SQL语言操作功能插入数据,用SQL命令向score1表插入一条记录：学号为“993503433”、课程号为“0001”、成绩是99。 insert into score1 values(“993503433“,“0001“,99),第五节 SQL语言操作功能插入数据,使用SQL语句向自由表golden.dbf中添加一条记录（”011”,9,7,11）。请将该SQL语句存储在文件two.prg中，否则不得分。 insert into golden values(“011“,9,7,11),第五节 SQL语言操作功能插入数据,用SQL INSERT语句插入元组(“p7”,”PN7”,1020)到“零件信息”表（注意不要重复执行插入操作），并将相应的SQL语句存储在文件one.prg中。 insert into 零件信息 values(“p7“,“PN7“,1020),第五节 SQL语言操作功能插入数据,除了以上的插入形式，还有下面两种格式： Insert into 表名 from array 数组名 从指定的数组中插入记录值。 Insert into 表名 from memvar 根据同名的内存变了来插入记录值，如果同名的变量不存在，那么相应的字段为默认值或空值。,第五节 SQL语言操作功能插入数据,第三章 关系数据库标准语言SQL,第六节 SQL语言操作功能更新数据,UPDATESQL命令 语法：UPDATE 表文件名 SET 字段1=表达式 ; ， WHERE 条件表达式 功能：以新值更新表中的字段值 说明： 字段 更新的字段 表达式 更新字段的新值 WHERE子句 指更新的条件，若缺省，则指每一条记录均用相同值更新。 用UPDATESQL命令更新表时，表不必事先打开，且命令执行后，记录指针指向表的最后一条记录，并没有指向记录的结束标记。,第六节 SQL语言操作功能更新数据,使用SQL语句更改表金牌榜所有记录的奖牌总数字段值，改值应为“金牌数”、“银牌数”、“铜牌数”三项之和。请将该SQL语句存储在文件two.prg中，否则不得分。 update 金牌榜 set 奖牌总数=金牌数+银牌数+铜牌数,第六节 SQL语言操作功能更新数据,用SQL语句对自由表教师完成下列操作：将职称为“教授”的教室新工资一项设置为原工资的120%，其他教室的新工资与原工资相等。 update 教师 set 新工资=原工资*1.2 where 职称=“教授“ update 教师 set 新工资=原工资 where 职称!=“教授“,第六节 SQL语言操作功能更新数据,用SQL语句将rate_exchange.dbf表中外币名称为“美元”的卖出价修改为829.01，并将相应的SQL语句保存为文本文件two.txt. update rate_exchange set 卖出价=829.01 where 外币名称=“美元“,第六节 SQL语言操作功能更新数据,将student表中学号为99035001的学生的院系字段值修改为“经济”。 update student set 院系=“经济“ where 学号=“99035001“,第六节 SQL语言操作功能更新数据,第三章 关系数据库标准语言SQL,第七节 SQL语言操作功能删除数据,DELETESQL命令 语法： DELETE FROM 表文件名 WHERE 条件表达式 功能：逻辑删除指定的记录 说明： WHERE 子句指出删除的条件，缺省时则指所有的记录。 DELETESQL命令允许表在不预先打开的情况下删除指定的记录。,第七节 SQL语言操作功能删除数据,用SQL delete语句从零件信息表中删除单价小于600的所有记录，并将相应的SQL语句存储在文件two.prg中。 delete from 零件信息 where 单价600,第七节 SQL语言操作功能删除数据,利用SQL DELETE命令删除stock_bk.dbf表中“股票代码”为“60000”的股票。 delete from stock_bk where 股票代码=“600000“,第七节 SQL语言操作功能删除数据,第三章 关系数据库标准语言SQL,第八节 SQL语言查询功能,SQL语言的核心就是查询功能。SQL语言的查询命令也称作SELECT命令，它的基本形式由SELECT-FROM-WHERE查询块组成，多个查询块可以嵌套执行。 一个查询可以用一条SELECTSQL语句来完成。查询保存在一个扩展名为.qpr的文件内。文件中保存的是实现查询的SELECTSQL命令，而并非查询的结果。查询的结果叫查询的输出去向。,第八节 SQL语言查询功能,什么是查询？ 所谓“查询”是指向一个数据库发出检索信息的请求，它使用一些条件提取特定的记录。查询的运行结果是一个基于表和视图的动态的数据集合。 查询必须基于确定的数据源。 从类型上讲，数据源可以是自由表、数据库表或视图； 从数量上讲，源表可以是单张表或多张相关的表。 一般地说，基于多表的查询将更能显示查询的优势并且也更有意义。,第八节 SQL语言查询功能,创建查询的方法 用CREATE QUERY命令创建查询，其实就是使用查询设计器创建查询。 选择“文件/新建”菜单或“新建”工具按钮或在项目管理器的“数据”选项卡中创建查询，其实也是打开查询设计器创建查询。 直接用SELECTSQL语句创建查询。,第八节 SQL语言查询功能,VFP的SQL- SELECT命令的语句模块 select ; &排序依据 to/into &查询输出设置,第八节 SQL语言查询功能,例1：将score1表中的记录按学号从低到高排序，在学号相同的情况下再按成绩从高到底排序，排序结果存入表NEW_ORDER。 select *; from score1; order by 学号,成绩 desc; into table NEW_ORDER,第八节 SQL语言查询功能,第八节 SQL语言查询功能,例2：从score1表中统计选修了课程的学生人数（选修多门时，只计算1次），统计结果保存在一个新表NEW_TABLE中，表中只有一个字段，字段名为学生人数。 select count(distinct 学号) as 学生人数; from score1; into table NEW_TABLE,例3： select 外币名称,现钞买入价,卖出价; from rate_exchange; into table rate_ex,第八节 SQL语言查询功能,例4： select 客户号,身份证,姓名,工作单位; from 客户; where 性别=“男“; into table TABA,第八节 SQL语言查询功能,第八节 SQL语言查询功能,第八节 SQL语言查询功能,例5： SELECT Stock_name.股票代码,股票简称, 买入价,现价,持有数量; FROM stock_name INNER JOIN stock_sl ; ON Stock_name.股票代码 = Stock_sl.股票代码; ORDER BY Stock_name.股票代码,第八节 SQL语言查询功能,通过查询设计器建立查询 打开查询设计器的方法： 在命令窗口输入create query命令。 在“文件”菜单，选择“新建”，点“查询”然后“新建文件”。,第八节 SQL语言查询功能,使用查询设计器建立查询步骤 1、在使用命令或用菜单新建查询后，会弹出“选择表或试图”对话框，从该对话框选择查询需要用到的表或试图。如果需要选择自由表，需要点击“其他”按钮选择。,2、当选择多个表时，会自动弹出“联接条件”对话框，询问用户两表之间使用的相联字段和联接类型。,第八节 SQL语言查询功能,3、在查询设计器窗口中，字段选项卡对于select语句中的select部分。如果需要输入公式等表达式，可以在“函数和表达式”框中填写，然后点“添加”即可。请注意如果你在“函数和表达式”框输入错误，则VFP会以为你输入的是字符串，并在你添加后，帮你在错误的公式前后加上引号。反过来也提示你公式或表达式输入错误。,第八节 SQL语言查询功能,4、如果需要筛选条件则在筛选选项卡中设置，对应where语句，其中不等于是在“否”上打勾，然后选择条件“=”；如果有多个筛选条件，必须在逻辑中设置多个筛选条件之间的关系。,第八节 SQL语言查询功能,5、如果需要分组就在“分组依据”选项卡中设置，对应group by语句，如果有分组条件，则在该选项卡中的“满足条件”对话框里设置（通筛选选项卡类似）。,第八节 SQL语言查询功能,6、如果需要排序则在“排序依据”选项卡中设置，相当于order by语句。,第八节 SQL语言查询功能,7、在“杂项”选项卡中可以设置distinct，top等参数。它们在代码中对应的是在select语句中的。,第八节 SQL语言查询功能,8、最后可以设置查询的输出去向，可以点击查询设计器按钮框中的查询去向按钮。对应于into table 语句等。如果要输出到文本文件，可以在“屏幕”按钮中设置。,第八节 SQL语言查询功能,9、所有的查询建立好后，单击“运行”按钮，检测查询运行结果。如果没有错误，根据题目要求保存该查询，请注意保存查询和查询输出去向是两回事。这里需要提醒的是在单击“运行”按钮后，不是所有的查询结果都会自动弹出来的，因为这依靠你设置的查询去向，如果是保存至表，则需要点击“显示”菜单中的“浏览”查看。,第八节 SQL语言查询功能,第三章 关系数据库标准语言SQL,第九节 视图的定义,什么是视图？ 视图是一个定制的虚拟表，可以是本地的、远程的或带参数的。在关系数据库中，视图也称作窗口，即操作表的窗口，是从表中派生出来的虚表，试图不独立存在，试图是依赖于表，是在数据