第六章SQL语言的应用1.ppt

《第六章SQL语言的应用1.ppt》由会员分享，可在线阅读，更多相关《第六章SQL语言的应用1.ppt（46页珍藏版）》请在三一办公上搜索。

1、Visual FoxPro,1,第六章,SQL语言的应用,Visual FoxPro,2,6.1 SQL语言概述6.2 数据查询6.3 数据定义6.4 数据操纵,Visual FoxPro,3,20世纪80年代初，美国国家标准协会（ANSI）开始着手制定SQL标准。SQL标准的出台使SQL作为标准关系数据库语言的地位得到了加强。目前流行的关系数据库管理系统，如Qracle、Sybase、SQL Server、Visual FoxPro等都采用了SQL语言标准，而且很多数据库都对SQL语句进行了再开发和扩展。,6.1 SQL语言概述,Visual FoxPro,4,SQL语言的特点：（1）SQL

2、是一种一体化的语言。尽管设计SQL的最初目的是查询，数据查询也是其最重要的功能之一，但SQL决不仅仅是一个查询工具，它集数据定义、数据查询、数据操纵和数据控制功能于一体，可以独立完成数据库的全部操作。（2）SQL语言是一种高度非过程化的语言。它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。（3）SQL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的9条命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。另外SQL的语法也非常简单，它很接近

3、英语自然语言，因此容易学习和掌握。,Visual FoxPro,5,（4）SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中，使用起来更方便，Visual FoxPro就是如此。这些使用方式为用户提供了灵活的选择余地。此外，尽管SQL的使用方式不同，但SQL语言的语法基本是一致的。SQL只是一种建议标准，各种数据库产品中所实现的SQL在语法、功能等方面均略有差异，本章讲述Visual FoxPro 中SQL的语法、功能与应用。,Visual FoxPro,6,6.1 SQL数据查询定义,查询在数据库和

4、表中查找与给定条件匹配的数据，并由此生成新的关系或输出。在所有的语言中，基本通用SQL语句,Visual FoxPro,7,查询的基本用法和子句,参照表：仓库表（仓库号 C，城市 C，面积 N）职工表（仓库号 C，职工号 C，工资 N）订购单表（职工号 C，供应商号 C，订货单号，订购日期）基本语句一：SELECT：查询动词，说明要查找的数据，它可以是字段名，表达式和常量FROM：确定数据的来源，指明表名或它的别名，可以是多个表,Visual FoxPro,8,查询的基本用法和子句,范例一：SELECT*FROM 职工SELECT 仓库号,城市 FROM 仓库表 ORDER BY 面积 DES

5、CSELECT DISTINCT 供应商号 FROM 订购单表SELECT*FROM 仓库表 WHERE 面积=380SELECT A.仓库号,B.城市,B.职工号,B.工资;FROM 仓库表 A,职工表 B;WHERE A.仓库号=B.仓库号,Visual FoxPro,9,基本语句二：WHERE:说明查询记录的条件，两个表之间的联接条件，在数据库中的永久连接默认为当然的条件。范例二：求解：查询为S3供应商提供服务的仓库所在的城市SELECT A.城市;FROM 仓库表 A,职工表 B,订购单表 C;WHERE A.仓库号=B.仓库号.AND.;B.职工号=C.职工号.and.C.供应商名=

6、“S3”问题：完成学生成绩的查询，,查询的基本用法和子句,Visual FoxPro,10,基本语句三：ORDER BY:对查询的结果排序GROUP BY 组表达式1，:对查询结果进行分和分类汇总 HAVING 条件表达式：限定分组条件范例三：成绩查询SELECT*FROM 成绩 ORDER BY 成绩 DESCSELECT 学号,SUM（成绩）AS 合计 FROM 成绩;ORDER BY 合计 DESC;GROUP BY 学号SELECT A.姓名,A.学号,SUM(B.成绩)AS 合计 FROM 学生 A,成绩 B;ORDER BY 合计 DESC;GROUP BY A.学号,查询的基本用

7、法和子句,Visual FoxPro,11,查询的基本用法和子句,基本查询语句四：INTO 目标|TO PRINTER|TO SCREEN|TO FILE_NAME确定查询的去向:打印机,屏幕,表和目标(默认的是屏幕)，目标可以是：ARRAY 数组名CURSOR 临时表名DBF(或TABLE)表名范例4：SELECT MAX(单价)INTO ARRAY A FROM SBSELECT 姓名,房间号,标准价格*天数 AS 实际房费;FROM 酒店管理 INTO TABLE 结算,Visual FoxPro,12,综合分析：假设有设备表SB和大修表DX,请分析以下SQL语句的功能：基本表格：SB.

8、DBF DX.DBF范例5：SELECT SB.名称,SUM(DX.费用)AS 大修总费用 from SB,DX;WHERE SB.编号=DX.编号.AND.LEFT(SB.编号,3)”038”;GROUP BY DX.编号；INTO TABLE 大修报表；ORDER BY 2,查询的基本用法和子句,Visual FoxPro,13,简单的计算功能:COUNT SUM AVG MAX MIN范例6：sele 仓库号,count(distinct 职工号)as 职工个数;from 职工 where 工资=1220;group by 仓库号,查询的基本用法和子句,Visual FoxPro,14,

9、查询的基本用法和子句,嵌套查询：SELECT*FROM 成绩 WHERE 学号 IN;（SELECT 学号 FROM 成绩 WHERE 成绩=60）问题四：列出成绩高于平均的全部学生的清单,Visual FoxPro,15,例 列出全部学生名单。例 列出所有学生姓名，去掉重名。,SELECT*FROM 学生,SELECT DISTINCT 姓名 AS 学生名单 FROM 学生,Visual FoxPro,16,例 将所有的学生入学成绩四舍五入，只显示学号、姓名和入学成绩。例 求出所有学生的入学成绩平均分。,SELECT 学号,姓名,ROUND(入学成绩,0)AS 入学成绩 FROM 学生,SE

10、LECT AVG(入学成绩)AS 入学成绩平均分 FROM 学生,Visual FoxPro,17,例 求出湖南学生入学成绩平均分。例 列出非湖南籍的学生名单。,SELECT 籍贯,AVG(入学成绩)AS 入学成绩平均分 FROM 学生 WHERE 籍贯=湖南,SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯湖南以上命令的功能等同于：SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯!=湖南或SELECT 学号,姓名,籍贯 FROM 学生 WHERE NOT(籍贯=湖南),Visual FoxPro,18,例 列出江苏籍和贵州籍的学生名单。,SELECT 学号,姓

11、名,籍贯 FROM 学生 WHERE 籍贯 IN(江苏,贵州)以上命令的功能等同于：SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯=江苏 OR 籍贯=贵州,Visual FoxPro,19,例 列出入学成绩在560分到650分之间的学生名单。例 列出所有的姓赵的学生名单。,SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 入学成绩 BETWEEN 560 AND 650在以上命令的功能等同于：SELECT 学号,姓名,专业 FROM 学生 WHERE 入学成绩=560 AND 入学成绩=650,SELECT 学号,姓名 FROM 学生 WHERE 姓名 LIKE

12、 赵%以上命令的功能等同于：SELECT 学号,姓名,专业 FROM 学生 WHERE 姓名=赵,Visual FoxPro,20,例 列出所有成绩为空值的学生学号和课程号。,SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL 在以上命令中，使用了运算符IS NULL，该运算符是测试字段值是否为空值，在查询时用“字段名 IS NOTNULL”的形式，而不能写成“字段名=NULL”或“字段名！=NULL”。,Visual FoxPro,21,例 列出选修“数据库原理”的所有学生的学号。,SELECT 学号 FROM 选课 WHERE 课程号=;(SELECT 课程号 F

13、ROM 课程 WHERE 课程名=数据库原理)上述SQL语句执行的是两个过程，首先在课程表中找出“数据库原理”的课程号（比如“01001”），然后再在选课表中找出课程号等于“01101”的记录，列出这些记录的学号。,Visual FoxPro,22,例 列出选修“01101”课的学生，这些学生的成绩比选修“01102”课的最高成绩还要高的学生的学号和成绩。,SELECT 学号,成绩FROM 选课 WHERE 课程号=01102 AND成绩ALL(SELECT 成绩 FROM 选课 WHERE 课程号=01102)该查询的含义是：先找出选修“01102”课的所有学生的成绩（比如说结果为87和72

14、），然后再在选修“01102”课的学生中选出其成绩中高于选修“01102”课的所有成绩（即高于87分）的那些学生。,Visual FoxPro,23,例 列出选修“数据库原理”或“软件工程”的所有学生的学号。,SELECT 学号 FROM 选课 WHERE 课程号 IN;(SELECT 课程号 FROM 课程 WHERE 课程名=数据库原理OR 课程名=软件工程)IN是属于的意思。,Visual FoxPro,24,例 输出所有学生的成绩单，要求给出学号、姓名、课程号、课程名和成绩。,SELECT a.学号,a.姓名,b.课程号,c.课程名,b.成绩；FROM 学生 a,选课 b,课程 c;W

15、HERE a.学号=b.学号 AND b.课程号=c.课程号,Visual FoxPro,25,例 列出男生的选课情况，要求列出学号、姓名、课程号、课程名、授课教师和学分数。,SELECT a.学号,a.姓名 AS 学生姓名,b.课程号,c.课程名,e.姓名 AS 教师姓名,c.学分 FROM 学生 a,选课 b,课程 c,授课 d,教师 e;WHERE a.学号=b.学号 AND b.课程号=c.课程号 AND;c.课程号=d.课程号 AND d.教师号=e.教师号 AND a.性别=“男”,Visual FoxPro,26,例 列出选修“01102”课的学生中，期末成绩大于学号为“2001

16、09”的学生该门课成绩的那些的学号及其成绩。SELECT a.学号,a.成绩 FROM 选课a,选课b;WHERE a.成绩b.成绩 AND a.课程号=b.课程号AND b.课程号=01102AND b.学号=190101,Visual FoxPro,27,联接查询 联接分为内部联接和外部联接。外部联接又分为左外联接、右外联接和全外联接。1.内部联接（Inner Join）实际上，上面例子全部都是内部联接。所谓内部联接是指所有满足联接条件的记录都包含在查询结果中。例 列出少数民族学生的学号、课程号及成绩。SELECT a.学号,b.课程号,b.成绩 FROM 学生 a,选课 b;WHERE

17、a.学号=b.学号 AND a.少数民族否如果采用内部联接方式，则命令SELECT a.姓名,b.课程号,b.成绩 FROM 学生 a INNER JOIN 选课 b;ON a.学号=b.学号 WHERE a.少数民族否所得到的结果完全相同。,Visual FoxPro,28,2.外部联接（Outer Join）(1)左外联接 也叫左联接（Left Outer Join），其系统执行过程是左表的某条记录与右表的所有记录依次比较，若有满足联接条件的，则产生一个真实值记录。若都不满足，则产生一个含有NULL值的记录。接着，左表的下一记录与右表的所有记录依次比较字段值，重复上述过程，直到左表所有记录

18、都比较完为止。联接结果的记录个数与左表的记录个数一致。SELECT a.学号,b.课程号,b.成绩 FROM 学生 a left JOIN 选课 b ON a.学号=b.学号 WHERE a.少数民族否,Visual FoxPro,29,（2）右外联接 也叫右联接（Right Outer Join），其系统执行过程是右表的某条记录与左表的所有记录依次比较，若有满足联接条件的，则产生一个真实值记录；若都不满足，则产生一个含有NULL值的记录。接着，右表的下一记录与左表的所有记录依次比较字段值，重复上述过程，直到左表所有记录都比较完为止。联接结果的记录个数与右表的记录个数一致。SELECT a.学

19、号,b.课程号,b.成绩 FROM 学生 a right JOIN 选课 b ON a.学号=b.学号 WHERE a.少数民族否（3）全外联接 也叫完全联接（Full Join），其系统执行过程是先按右联接比较字段值，然后按左联接比较字段值，重复记录不记入查询结果中。,Visual FoxPro,30,例 按性别顺序列出学生的学号、姓名、性别、课程名及成绩，性别相同的再先按课程后按成绩由高到低排序。,SELECT a.学号,a.姓名,a.性别,c.课程名,b.成绩 FROM 学生 a,选课 b,课程 c;WHERE a.学号=b.学号 AND b.课程号=c.课程号 ORDER BY a.性

20、别,c.课程名,b.成绩 DESC,Visual FoxPro,31,例 将上例的查询结果保存到test1.txt文本文件中。,SELECT a.学号,a.姓名,a.性别,c.课程名,b.成绩 FROM 学生 a,选课 b,课程 c;WHERE a.学号=b.学号 AND b.课程号=c.课程号 ORDER BY a.性别,c.课程名,b.成绩 DESCTO FILE test1,Visual FoxPro,32,例 查询学生所学课程和成绩，输出学号、姓名、课程名和成绩，并将查询结果存入testtable表中。,SELECT a.学号,a.姓名,b.课程名,b.成绩 FROM 学生 a,成绩

21、b;WHERE a.学号=b.学号 INTO CURSOR testSELECT a.学号,a.姓名,b.课程名,a.成绩FROM test a,课程 b;WHERE a.课程号=b.课程号 INTO TABLE testtable,Visual FoxPro,33,例 分别统计男女人数。例 分别统计男女中少数民族学生人数。,SELECT 性别,COUNT(性别)FROM 学生 GROUP BY 性别,SELECT 性别,COUNT(性别)FROM 学生 GROUP BY 性别 WHERE 少数民族否如果把命令写成如下形式就是错误的。SELECT 性别,COUNT(性别)FROM 学生;GRO

22、UP BY 性别 HAVING 少数民族否,Visual FoxPro,34,例 列出平均成绩大于80分的课程,SELECT 课程号,AVG(成绩)FROM 选课 GROUP BY 课程号HAVING AVG(成绩)=80,Visual FoxPro,35,6.2 SQL数据定义(p85),定义数据库、表和视图：关键词：CREATE命令 INSERT命令Create database 后勤管理Create table 寝室管理(姓名 c(8)primary key,;寝室号 c(4),;学号 c(6),；年龄 n(3,0)check(年龄0)error“年龄大于0”)Insert into 寝

23、室管理 values(“何进”,”0102”,”003701”,99),Visual FoxPro,36,例 利用SQL命令建立学生管理数据库，其中包含3个表：学生表、选课表和课程表。操作步骤如下：（1）用CREATE命令建立数据库。CREATE DATABASE D:学生管理（2）用CREATE命令建立学生表。CREATE TABLE 学生(学号 C(5)PRIMARY KEY,姓名 C(8),入学成绩 N(5,1)CHECK(入学成绩0)ERROR 成绩应该大于0！),Visual FoxPro,37,（3）建立课程表。CREATE TABLE 课程(课程号 C(5)PRIMARY KEY

24、,课程名 C(20),学分 N(1)（4）建立选课表。CREATE TABLE 选课(学号C（5），课程号 C(5)，;成绩 I CHECK(成绩=0 AND 成绩=100);ERROR成绩值的范围0100！DEFAULT 60,;FOREIGN KEY 学号 TAG 学号 REFERENCES 学生，；FOREIGN KEY 课程号 TAG 课程号 REFERENCES 课程),Visual FoxPro,38,SQL数据定义,CREATE TABLE TEST1(姓名 C(8),年龄 N(2),性别 L,班级 C(5),专业 C(20)CREATE VIEW STOCK_VIEW AS S

25、ELECT*FROM STOCK;WHERE 交易所=“上海”CREATE VIEW STOCK_VIEW AS SELECT；股票名称 AS 名称，价格 AS 股票价格，交易时间；FROM STOCK,Visual FoxPro,39,SQL数据定义,添加字段 alter table 寝室管理 add 水电费 y修改字段宽度 alter table 寝室管理 alter 寝室号 c(6)修改字段名 alter table 寝室管理 rename column 水电费 to 水电气费删除字段 alter table 寝室管理 drop column 年龄添加侯选索引 alter table 寝

26、室管理 add unique tag emp,Visual FoxPro,40,6.4 SQL数据操作,INSERT INTO TEST1(姓名,年龄,性别,班级,专业)；VALUES(HE,19,.T.,9986,SHICYINGKXIA)UPDATE 学生管理 SET 年龄=年龄+1DELETE FROM 学生管理 WHERE 姓名=“王海”,Visual FoxPro,41,例 将“学生”表中胡敏杰学生的籍贯改为广东。UPDATE 学生 SET 籍贯=“广东”WHERE 姓名=“胡敏杰”例 所有男生的各科成绩加20分 UPDATE 选课 SET 成绩=成绩+20 WHERE 学号 IN（

27、SELECT 学号 FROM 学生 WHERE 性别=“男”）以上命令中，用到了WHERE条件运算符“IN”和对用SELECT语句选择出的记录进行数据更新。注意UPDATE一次只能在单一的表中更新记录。,Visual FoxPro,42,删除表删除表的SQL命令：DROP TABLE 如：DROP TABLE AA DROP TABLE命令直接从磁盘上删除所指定的表文件。如果指定的表文件是数据库中的表并且相应的数据库是当前数据库，则从数据库中删除了表。否则虽然从磁盘上删除了表文件，但是记录在数据库文件中的信息却没有删除，此后会出现错误提示。所以要删除数据库中的表时，最好应使数据库是当前打开的数

28、据库，在数据库中进行操作。,Visual FoxPro,43,作业,P100 2,Visual FoxPro,44,作业,1.Sele 供应商号,供应商名 from 供应商表 where 地址=“北京”2.sele 订购单号 from 订购单表 where 供应商号=“s6”3.sele 订购单号,订购日期 from 订购单表 where 供应商号=“s6”and 职工号=“E6”4.sele 职工号,仓库号 from 订购单表 A,职工表 B where 供应商号=“S3”and A.职工号=B.职工号5.sele 供应商号,供应商名称 from 供应商表 a,订购单表 b where a.

29、供应商号 in(sele 供应商号 from 订购单表 where 职工号=“E1”)and b.职工号=“E3”and a.供应商号=b.供应商号 and a.地址=“北京”6.sele dist 供应商名称 from 供应商表 a,订购单表b where b.职工号 not in(sele 职工号 from 职工表 where 工资1220)and a.供应商号=b.供应商号7.sele 城市 from 仓库表 a,职工表 b,订购单表 c where c.供应商号=“s4”and c.职工号=b.职工号 and b.仓库号=a.仓库号,Visual FoxPro,45,8.sele 职工

30、号 from 订购单表 a,职工表 b,仓库表 c where a.供应商号=“S4”AND C.城市=“上海”and a.职工号=b.职工号 and b.仓库号=c.仓库号9.sele 订购单号 from 订购单表 a,职工表 b,供应商表 c where b.工资1230 and c.地址=“北京”and a.职工号=b.职工号 and a.供应商号=c.供应商号10.sele count(*)from 仓库表 11.sele*from 仓库表 where 面积=(sele max(面积)from 仓库表)12.sele avg(面积)from 仓库表 13.sele count(*)fr

31、om 职工表 where 工资1220 group by 仓库号14.sele count(dist a.供应商号)from 订购单表 a,职工表 b,仓库表 c where c.面积=（sele min(面积)from 仓库表）and a.职工号=b.职工号 and b.仓库号=c.仓库号15.sele a.*from 职工表 a where 工资(sele avg(b.工资)from 职工表 b where a.仓库号b.仓库号),Visual FoxPro,46,1.insert into 供应商表 values(“S9”,”智通公司”,”沈阳”)2.dele from 供应商表 where 供应商号 not in(sele 供应商号 from 订购单表)3.dele from 订购单表 where 职工号 in(sele 职工号 from 职工表 a,仓库表 b where 城市=“上海”and a.仓库号=b.仓库号)4.update 仓库表 set 面积=面积+100 where 城市=“北京”5.update 职工表 set 工资=工资*(1+0.10)where 工资(sele avg(工资)from 职工表),