《程序设计课件第5章.ppt》由会员分享，可在线阅读，更多相关《程序设计课件第5章.ppt（65页珍藏版）》请在三一办公上搜索。
1、基本要求与基本知识点（1）掌握SQL的数据查询功能，包括简单查询、关联查询及超联结（2）掌握SQL的数据操作功能（3）掌握SQL的数据定义功能,第5章 关系数据库标准语言SQL,（1）SQL的数据查询功能（2）SQL的数据操作功能（3）SQL的数据定义功能,教学重点与难点,SQL是结构化查询语言Structured Query Language的缩写，SQL包括数据定义、数据查询、数据操纵和数据控制等功能，其中查询是SQL的主体，SQL已作为关系数据库管理系统的标准化语言。本章主要讲述SQL的数据定义、数据查询、数据操纵等功能。,5.1 SQL概述,SQL的定义功能可以定义数据库、表、试图、存
2、储过程、索引等，VFP主要支持表和视图的定义。1、表的定义CREATE TABLE|DBF 表名 FREE(字段名1 字段类型(宽度，小数点位)NULL|NOT NULLCHECK ERROR 出错信息DEFAULT 表达式 PRIMARY KEY|UNIQUE(侯选索引)，字段名2,5.2 数据定义,【功能】定义(创建)一个表。【说明】用CREATE TABLE命令可以完成第3章介绍的“表设计器”的所有操作，各选项作用如下：(1)TABLE与DBF等价，都是建立表文件；指定新建表的名称。(2)FREE：指定建立自由表，即新建立的表不添加到打开的数据库中。(3)(,)：指定字段名、字段的数据类
3、型、字段宽度及小数位数。字段类型可以用类型字符表示，例如字符型用C，数值型用N表示。,(4)NULL|NOT NULL：指定该字段值可以为空或不能为空，默认值是NOT NULL。(5)CHECK：指定该字段的合法值以及该字段值的约束条件；ERROR：指定在浏览或编辑窗口中为该字段输入的值不符合CHECK子句的条件时显示的提示信息。,(6)DEFAULT：为该字段指定一个默认值，表达式的数据类型与该字段的数据类型要一致。每添加一条记录时，该字段自动取该默认值。(7)KEY：为该字段创建一个主索引，索引标识名与字段名相同。主索引字段值必须惟一；UNIQUE：为该字段创建一个候选索引，索引标识名与字
4、段名相同。,例1：建立成绩表Create table 成绩表(Xh C(7),kh C(2),cj N(5,1)）例2：建立一个学生数据库，并建立属于学生数据库的学生表和成绩表Create database studentCreate table 学生表(xh c(7)primary key,xm c(8),xb c(2)default“男”,bj c(20)default“2002级会计1班”,zf n(5,1)check(zf=0 and zf=500)Error”总分的值超界”),例2：用SQL语句创建学生基本信息表stud1，stud1表由学号、姓名、性别、班级、出生日期、是否团员、特
5、长、照片组成。要求以学号为主索引，姓名不允许有空值。(为区别于第3章所创建的stud表，此处使用表名stud1。)创建表之后，显示表的结构。,CREATE TABLE stud1(学号 C(9)PRIMARY KEY,姓名 C(8)NOT NULL,性别 C(2),班级 C(14),出生日期 D,是否团员 L,特长 M,照片 G)LIST STRU,注意：创建表后，新创建的表即成为当前数据表，可以在命令窗口中输入命令：APPEND FROM 把已经存在的表中的数据追加到新建表中。但要求两个表的结构要完全一致。Crea table sc1(学号 C(9),课程号 C(3),成绩 N(6,2)Ap
6、pen from sc,格式1：ALTER TABLE ADD|ALTER COLUMN(，)NULL|NOT NULLCHECK ERRORDEFAULTPRIMARY KEY|UNIQUE该格式主要是用于添加新的字段或修改已有字段的类型、宽度、有效性规则、错误信息、缺省值、主关键字和联系等，但不能修改字段名、删除字段、删除已经定义的有效性规则。例如：Alter table new2 add km c(12),2、表结构的修改,例1.在stud1表中增加“籍贯”字段，字段类型为字符型，长度为10。ALTER TABLE stud1 ADD 籍贯 C(10)例2.将stud1表的“籍贯”字段宽
7、度修改为14，设置合法值的左面6个字符为“北京市”或“上海市”。ALTER TABLE stud1 ALTER 籍贯C(14);CHECK LEFT(籍贯,6)=北京市.OR.LEFT(籍贯,6)=上海市【注意】执行命令前需要将stud1表中所有记录的“籍贯”字段开头三个汉字设置为北京市或上海市，以满足CHECK规则。,2、表结构的修改,格式2：ALTER TABLE 表名 ALTER COLUMN 字段名 NULL|NOT NULL SET CHECK 表达式 ERROR 出错信息 SET DEFAULT 表达式 DROP DAFAULT DROP CHECK【功能】修改指定表中指定字段的D
8、EFAULT和CHECK约束规则，不影响原有表中的数据。【说明】：指明被修改表的表名。ALTER COLUMN：指明要修改列的字段名。NULL|NOT NULL：指定该字段可以为空或不能为空。SET DEFAULT：重新设置该字段的默认值。,SET CHECK ERROR：重新设置该字段的合法值限定，要求该字段的原有数据满足合法值限定条件。DROP DEFAULT：删除默认值。DROP CHECK：删除该字段的合法值限定。,例 删除stud1表中“籍贯”字段的合法值约束，然后设置字段中左面6个字符的默认值为“山东省”。ALTER TABLE stud1 ALTER 籍贯 DROP CHECKA
9、LTER TABLE stud1 ALTER 籍贯 SET DEFALUT LEFT(籍贯,6)=“山东省”例：alter table new1 alter zf;set check(zf=0 and zf=500);error“总分值应在0500之间”;set dafault 0,格式3：ALTER TABLE DROP COLUMN RENAME COLUMN TO SET CHECK ERROR DROP CHECKADD PRIMARY KEY TAG FOR DROP PRIMARY KEYADD UNIQUE TAG FOR DROP UNIQUE TAG【功能】删除指定表中的指定
10、字段，修改字段名，修改指定表的完整性规则，包括添加或删除索引及表的合法值限定。【说明】,DROP COLUMN：从指定表中删除指定的字段。SET CHECK ERROR：为该表指定合法值限定及错误提示信息。DROP CHECK：删除该表的合法值限定。,ADD PRIMARY KEY TAG：为该表建立主索引，一个表只能有一个主索引。DROP PRIMARY KEY：删除该表的主索引。ADD UNIQUE TAG：为该表建立候选索引，一个表可以有多个候选索引。,DROP UNIQUE TAG：删除该表的候选索引。修改自由表时，不能使用DEFAULT、PRIMARY KEY等子句或SET子句。,例
11、 删除stud1表中的“籍贯”字段，将“特长”字段名改为“专长”。ALTER TABLE stud1 DROP 籍贯 RENAME COLUMN 特长 TO 专长,例如：将new2表的kh字段名改为kchalter table new2 rename kh to kch例如:建立一个按照xh+kch进行索引的索引方式Alter table new2 add unique xh+kchTag xh_kch,DROP TABLE 将指定的表文件从磁盘上删除，如果是数据库中的表文件应将表文件所属的数据库打开，再进行删除，即将数据表文件从磁盘上删除，同时，清除表与数据库的关系。Open databas
12、e data1Drop table 学生表,3、表的删除,主要完成数据的插入、删除和更新等操作1、插入格式1 INSERT INTO VALUES()功能：在指定的表文件末尾追加一条记录。将表达式表中的值赋给中相应的各字段；,5.3 数据操纵,说明：指定表文件中的各个字段，如果省略，按表文件字段的顺序依次赋值。：指定要追加的记录各个字段的值。在INTO子句中没有出现的字段，新记录将取空值(或默认值)，但表定义中说明了NOT NULL的字段不能取空值。,格式2（只适用于VFP的特殊格式）Insert into dbf_name from arraay 数组名|from memvar从指定的数组中
13、插入记录的值或从同名的内存变量来插入记录的值，如果同名的内存变量不存在，则相应的字段为默认值或空值例如：在new2表中插入一条新记录Declare aa(5)Aa(1)=“1313”,aa(2)=“0005”,aa(3)=82Insert into new2 from array aa,用表达式方式追加第1条记录：INSERT INTO stud1(学号,姓名,性别,班级,出生日期,是否团员,籍贯);VALUES(200621054,刘玉坤,男,06自动化,1989/04/28,.T.,浙江绍兴),例如 分别使用3种不同的方法在stud1表的末尾追加3条记录，然后显示数据表的记录。,用数组方式
14、追加第2条记录：DIMENSION DA(9)DA(1)=200605011DA(2)=夏雨薇DA(3)=女DA(4)=06国际贸易DA(5)=1989/09/02DA(6)=.F.DA(9)=湖南长沙INSERT INTO stud1 FROM ARRAY DA,用内存变量方式追加第3条记录：学号=200626068姓名=柳依依性别=女班级=06社会工作籍贯=广西桂林出生日期=1989/02/21是否团员=.T.INSERT INTO stud1 FROM MEMVAR,update 表名 set 字段名1=表达式1，字段名1=表达式1 where 功能：更新指定表文件中满足WHERE条件的
15、记录数据。其中SET子句用于指定字段和修改的值WHERE子句用于指定更新的记录，如果省略WHERE，则更新表中所有记录。,2、更新,【例】将SC1选课表中所有课程号为02的成绩加上5分，然后再将SC1选课表中所有记录的成绩提升10%。UPDATE SC1 SET 成绩=成绩+5;WHERE 课程号=02UPDATE SC1 SET 成绩=成绩*(1+0.1),2、更新,3、删除格式：delete from 表名 where 该删除为逻辑删除，如需执行物理删除则执行packDelete from new2 where cj60【例】删除STUD1表中所有88年出生的记录。DELETE FROM
16、STUD1;WHERE YEAR(出生日期)=88,完成单表或多表中数据的查询，又称Select查询1.语句格式Select distinct,From 表名1,as Where group by,having order by asc|desc,5.4 数据查询,功能：根据where子句中的条件表达式，从指定的表或视图中找出满足条件的元组，按照select子句中指定的列和顺序显示查询到的元组结果。Select子句:说明要输出的结果元组中需要的字段From子句：说明查询的数据来源，包括表和视图Where子句：说明查询的条件,Group by子句：用于对查询结果进行分组，可以利用分组进行分组汇总
17、Having子句：与group by结合使用，用于限定分组必须要满足的条件。Order by子句：对查询的结果进行排序SQL的查询主要有简单查询、联接查询、嵌套查询、使用库函数查询等,简单查询指基于单个表的查询，或仅由SELECT-FROM子句构成的无条件查询，或由 SELECT-FROM-WHERE构成的简单条件查询。）*的使用 例如：从学生成绩表中查询所有学生信息select*from 学生成绩表,.简单查询,）查询学生成绩表中高数成绩在80分以上的 元组,输出姓名、学号、高数三列信息 select 姓名，学号，高数;from 学生成绩表 where 高数=80,Where子句后可以是复合
18、条件,可以包括、=、=、=、!=以及逻辑运算符and、or、not,.简单查询,3）空值NULL的使用查询家庭住址为空的元组Select*From 学生表Where 家庭住址 is NULL说明：NULL不是一个具体的值，它只是一个表示空值的谓词，其一般格式为：列名 is not null不能写成“列名=NULL”,4)Distinct谓词的使用:去掉结果中的重复元组例如：列出图书馆中所有图书的书名和出版社Select distinct 书名,出版社 From 图书,5)between,in,like三种谓词的使用Betweenand 谓词:用于确定条件的范围或区间例如：查询高数成绩在60分和
19、90分之间的学生Select*From 学生成绩表Where 高数 between 60 and 90Betweenand等价于高数=60 and 高数=90,In谓词的使用：表示包含的关系例如：在学生选修成绩表中查询选修了课程01和02两门课的所有学生的成绩信息Select*From 选修表Where 课号 in(“01”,”02”)In谓词的使用相当于or的用法相当于Where 课号=“01”or 课号=“02”相反的操作：Where 课号 not in(“01”,”02”),Like谓词的使用：进行字符串的匹配比较 下划线：匹配任意一个字符 百分号：匹配任意多个字符例如：查询学生信息表中
20、家庭住址是青岛地区的学生信息Select*From 学生信息表Where 家庭住址 like“青岛%”也可以使用not like进行匹配,6）group by 子句的使用例如：将学生选修课成绩表按照学号进行分组显示Select*From 选修表；Group by 学号7）OEDER by子句的使用Select*From 学生成绩表；Where 总分300 Order by 高数,注：如果语句在输入时一行写不完，则可以采用分号做续行符,1）基于多个表的查询在from子句中指定查询的表，在from或where子句中指明查询时的联接条件。例如：查询选修过课程的所有学生信息Select*from ne
21、w1,new2 Where new1.xh=new2.xh上述联接为等值联接，即只有满足条件的记录才会在结果中显示。,3.联接查询,主要在from 子句中，不放在where子句中格式为：select*from table inner|left|right|full join table on 联接条件注意：VFP的select语句的联接格式只适用于两个表的联接，如果有多个表的联接，则还应使用where格式(即标准格式)。,2）超联接,【例】查询并显示各个学生的学号、所学课程及课程成绩，要求按成绩排序。SELECT A.学号,B.课程名,B.成绩;FROM SC A INNER JOIN COU
22、RSE B;ON A.课程号=B.课程号 ORDER BY 成绩此查询的WHERE等价表达式为：SELECT A.学号,B.课程名,B.成绩;FROM SC A,COURSE B;WHERE A.课程号=B.课程号 ORDER BY 成绩,内联接,【例】对COUESE表和SC表实现左联接查询，结果包含学号、课程名、成绩字段。SELECT 学号,课程名,成绩 FROM COURSE A;LEFT JOIN SC B;ON A.课程号=B.课程号 ORDER BY 学号 结果中包括course表中的所有记录，以及sc表中匹配的记录 首先从左边表(A表)中的第一条记录开始，在B表中查找，若找到，显示
23、相应字段内容；若找不到，相应的字段显示NULL值。,左外联接,【例】对COUESE表和SC表实现右联接查询，结果包含学号、课程名、成绩字段。SELECT 学号,课程名,成绩 FROM COURSE A;RIGHT JOIN SC B;ON A.课程号=B.课程号 ORDER BY 学号 结果中包括表sc中的所有记录，以及course 表中匹配的记录,右外联接,即查询结果中即包括满足联接条件的两个表中的记录，还包括两个表中不满足联接条件的记录，相应的字段为空值。例如：将SC和COURSE进行完全联接SELECT 学号,课程名,成绩 FROM COURSE A;FULL JOIN SC B;ON
24、A.课程号=B.课程号 ORDER BY 学号,完全联接,5）别名与自联接查询别名：关系引用时可以采用其别名，方便操作。Select from new as x where x.xh=“1111”and x.zf=300自联接：在同一个关系上进行的自我联接。例如：查询new2表中选修的所有课程成绩大于80分以上的所有学生Select x.xh,x.kh,x.cj distinct from new2 as x,new2 as y where x.xh=y.xh and x.cj=80,即在一个select-from-where查询块的where或having中嵌入另一个查询块，称为子查询，并允
25、许多层嵌套。部分联接查询可以通过嵌套完成。但order 子句不能出现在子查询中。另外，查询的结果只能是外层表中的字段。ANY|ALL|SOME()或 NOT EXISTS(子查询),4.嵌套查询,【说明】包括SQL支持的关系运算符：=、!=、#、=、=、=以及5.4.2节中提到的特殊运算符。ANY、ALL、SOME是量词。其中ANY和SOME是同义词。在进行比较运算时只要子查询中有一条记录为真，则结果为真；而ALL则要求子查询中的所有记录都为真，结果才为真。,EXISTS是谓词。用来检查子查询中是否有结果返回(是否为空)。NOT EXISTS表示结果集为空。注意exists不是运算符号，所以e
26、xists前后都不能有比较项或运算符，而且子查询中select后一般为*,【例】查询显示所有成绩大于85分的学生姓名、班级和成绩。SELECT 姓名,班级,SC.成绩 AS 成绩 FROM;STUD WHERE 学号 IN(SELECT 学号 FROM SC WHERE 成绩85),【例】在SC表中查找选修02号课的学生中成绩比选修51号课的最低成绩都要高的学生的学号和成绩。SELECT 学号,课程号,成绩 FROM SC WHERE;课程号=02 AND 成绩 ANY(SELECT 成绩 FROM SC WHERE 课程号=51)说明:先找出选修了51号课程的所有学生成绩(结果是69，73，
27、82，74，80)，然后找出选修02号课程中成绩高于51号课程最低成绩的那些学生的学号。,【例】查询没有选修任何课程的学生信息Select*from stud where not exists;(select*from sc where stud.学号=学号)对应的in子句操作Select*from stud where学号not in;(select 学号 from sc where 学号=sc.学号),【例】查询借阅过图书的读者信息Select*from 读者 where exists;(select*from 借阅;where 借阅证号=读者.借阅证号),(1)使用系统函数：count,
28、sum,avg,max,min【例】在SC表中按课程号对记录分组并汇总成绩信息，检索出课程名和成绩信息，用平均成绩为字段名显示平均成绩。SELECT 课程名,AVG(成绩)AS 平均成绩 FROM COURSE,SC;GROUP BY 课程名,SC.课程号;WHERE SC.课程号=COURSE.课程号,5.分组与计算查询,【例】列出各门课的平均成绩、最高成绩、最低成绩。SELECT 课程名,AVG(成绩)AS 平均成绩,MAX(成绩)AS 最高分,;MIN(成绩)AS 最低分 FROM COURSE,SC;GROUP BY 课程名,SC.课程号;WHERE SC.课程号=COURSE.课程号
29、,(1)使用系统函数,主要采用order by 子句进行分组查询例.按班级分类显示学生的姓名、课程名、成绩，同一班级按成绩排序。SELECT A.姓名,A.班级,C.课程名,B.成绩;FROM STUD A,SC B,COURSE C;WHERE A.学号=B.学号.AND.B.课程号=C.课程号；ORDER BY A.班级,B.成绩,(2)排序,例.显示成绩在80至90之间的学生的姓名、课程名和成绩，按课程名排序，同一课程名按成绩由高到低排序。SELECT A.姓名,C.课程名,B.成绩;FROM STUD A,SC B,COURSE C;WHERE A.学号=B.学号.AND.B.课程号=
30、C.课程号.AND.;B.成绩 BETWEEN 80.AND.90;ORDER BY C.课程名,B.成绩 DESC,(2)排序,主要采用group by 子句进行分组查询格式：Group by 字段1，字段2having 条件Having子句用于进一步限定分组的条件。只能跟在group by子句的后面，条件中必须有函数的使用，否则可以直接放在where子句中而不用加having子句，因为在where子句中不能使用函数。查询中一般用where限定元组，用group by 限定分组，用having限定分组的选择条件,(3)分组,【例】查找最少选修了三门课程的学生的姓名。SELECT 姓名 FRO
31、M STUD WHERE 学号 IN;(SELECT 学号 FROM SC GROUP BY 学号 HAVING COUNT(*)=3)【注意】虽然HAVING和WHERE都是条件子句，但它们作用不同。WHERE子句用来指定表中各行应满足的条件(限定记录)；而HAVING子句是用来指定每一分组满足的条件(限定分组的条件)，只有满足HAVING条件的那些组才能显示在结果中。,每一个SELECT语句都能获得一个或一组记录。若要把多个SELECT语句的结果合并为一个结果，可用集合操作来完成。集合操作主要包括：并操作UNION、交操作INTERSECT和差操作MENUS。将SQL查询的结果通过并运算符
32、合并成一个查询结果例如：查询出版社为“高教出版社”，单价在30元以上的图书Select*from 图书 where 出版社=“高教出版社”;Union;Select*from 图书 where 单价=30,6.集合查询,【例】显示计算机系(学号字段的第5、6两个字符是“23”)的学生以及所有的男同学的学号、姓名、性别、班级信息。实质上是求计算机系的所有学生与男生的并集。查询语句代码如下：SELECT 学号,姓名,性别,班级 FROM STUD;WHERE SUBSTR(学号,5,2)=23 UNION;SELECT 学号,姓名,性别,班级 FROM STUD;WHERE 性别=“男”【例】显示
33、选修了大学英语或大学计算机基础的学生的学号和课程号。SELECT 学号,课程号 FROM SC;WHERE 课程号=01 UNION;SELECT 学号,课程号 FROM SC;WHERE 课程号=51,SQL语句中并没有提供直接进行交集和差集的操作，但可以用其他方法来实现。【例】显示既选修了01课程又选修了02课程的学生的学号和课程号。SELECT 学号 FROM SC WHERE 课程号=01;AND 学号 IN(SELECT学号 FROM SC;WHERE 课程号=02)【例】显示选修了01课程而没有选修02课程学生的名单(就是选修01课程的学生与选修02课程学生的差集)。SELECT
34、学号 FROM SC WHERE 课程号=01;AND 学号 NOT IN(SELECT 学号;FROM SC WHERE 课程号=02),1）显示部分结果 select top 数值表达式 percent表示显示前几条记录或前百分之几的记录，top必须与order by 子句结合使用。例如：显示sc表中前5条记录Select*top 5 from sc order by 学号Select*top 5 percent from sc;Order by 学号,7.查询结果的显示和定向,2)将查询的结果存放到数组中格式：into array 数组名Select*from sc into array
35、 a1存放查询结果的数组为二维数组。3）将查询的结果存放到临时文件中Into cursor 产生的文件为只读的表文件，查询结束时临时文件为当前文件，当关闭文件后该临时文件将自动删除，一般用临时文件存放一些临时结果或中间结果。,4)将查询的结果存放到永久表中Into dbf|table 表名5)将查询的结果存放到文本文件中To file 文件名additive如果to短语和into短语同时使用，则to 短语将被忽略6)将查询的结果直接输出到打印机To printer【例5.32】对STUD表的记录按“班级”升序排列，用排序结果生成永久表STUD2.DBF。SELECT*FROM STUD INT
36、O DBF STUD2;ORDER BY 班级,8.视图的定义视图是一个定制的虚表、可以是本地的、远程的，视图的数据来源可以是一个表或多个表，或其他的视图，视图可以更新，但视图只是显示指定表的部分信息，它依赖于表，不能独立存在。1)定义格式：CREATE VIEW 视图名列名1，列名2 AS SELECT查询如果不指定列名则采用表中的字段名。例如：依据new2表建立一个视图，视图中包括成绩大于80分以上的记录Create view v_new2 as;select*from new2 where cj=80,2）视图的删除Drop view 视图名视图数据以来于数据表，所以视图的插入、修改、删除有相应的规则。视图依据单表建立的则可以进行插入、更新由多表导出时，不允许进行插入、更新和删除,
