第3章VF中SQL语言的应用1.ppt

第十章 VF中SQL语言的应用,第一节 SQL语言概述 第二节 数据查询第三节 数据操纵第四节 数据定义,SQL语言简介,SQL（Structured Query Language）语言是一种十分重要的标准数据库语言。SQL语言1986年成为国际标准语言,SQL语言有两种使用方式，即可以作为自含式语言，在数据库管理系统中独立使用，又可以作为嵌入式语言，嵌入许多高级语言（如C、FORTRAN、COBOL）中使用。,SQL语言的特点1SQL语言是一种一体化的语言 SQL语言集数据定义、数据操纵、数据查询和数据控制的功能于一体，语言风格统一，可以独立完成数据库的全部操作，。,2SQL语言是一种高度非过程化的语言。它没有必要一步步告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。它的大多数语句都是独立执行、与上下文无关。,3语言简洁，易学易用,SQL的命令动词SQL功能 命令动词数据查询 SELECT数据定义 CREATE、DROP、ALTER数据操纵 INSERT、UPDATE、DELETE数据控制 GRANT、REVOKE（VFP中不能用）,操作不需要打开表,7,二、SQL语言的基本概念SQL语言支持关系型数据库的三级模式结构。其中外模式对应于视图（View）和部分基本表（Base Table），模式对应于基本表，内模式对应于存储文件。基本表是本身独立存在的表，在SQL语言中一个关系对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引存放在存储文件中。存储文件的逻辑结构组成了关系型数据库的内模式。而存储文件的物理文件结构是任意的。视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库只存放在视力的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。,1、数据定义命令*CREATE TABLE-SQL（建立表结构）ALTER TABLE-SQL（修改表结构）DROP TABLE-SQL（删除表）,2、数据修改命令 INSERT-SQL（插入数据）*DELETE-SQL（删除数据）*UPDATE-SQL（更新数据）,3、SQL数据查询 在数据库中对数据的操作，很多时侯是查询，因此，数据查询是数据库的核心操作。而在SQL语言中，查询语言中只有一条查询命令，即SELECT语句。,10.2 数据查询,数据查询通过Select语句表达语句格式SELECT ALL|DISTINCT，FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC,10.2 数据查询,SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDER BY子句：对查询结果表按指定列值的升序或降序排序,10.2 数据查询,整个Select语句的含义：根据Where子句的条件表达式，从From子句指定的基本表或视图中找出满足条件的元组，再按Select子句中的目标列表达式，选出元组中的属性值形成结果表。如果有Group子句，则将结果按的值进行分组，该属性列值相等的元组为一个组。如果Group子句带Having短语，则只有满足指定条件的组才可以输出。如果有Order子句，则结果还要按的值升序或降序排列。,10.2 数据查询,注意：编写SQL语句之前，要知道各个表的结构和联系，即数据库的模式。SQL一般写成多行，用续行符(;)接在最后，但SQL语句完成的那行不加续行符。表达式和SQL中的符号的书写必须使用半角符号,10.2.1 单表查询,单表查询：From子句后只有一个表的查询From子句的格式：FROM 数据库名!表名 AS本地别名 数据库名!：可选，用来指出哪个数据库中的表本地别名：可选，给数据表起另一个名字，在后面可以用别名代替原表名分下面三种使用情况举例1.查询所有的列2.查询指定的列3.查询经过计算的值或更改列标题名,1.查询所有列,如果SELECT中的为*，表示查询所有列例10.1 返回“学生表”中的所有行和所有列set default to“D:我的数据库项目”open database“D:我的数据库项目数据库教学管理数据库.dbc”select*;from 教学管理数据库!学生表例10.2 查询成绩表中的所有记录set default to“D:我的数据库项目”open database“D:我的数据库项目数据库教学管理数据库.dbc”select*;from 成绩表,2.查询指定列(投影操作),要查询指定列，要在SELECT的中指定列名，在这些列上进行投影。例10.3 检索学生表中的学号、姓名、性别和籍贯set default to“D:我的数据库项目”open database“D:我的数据库项目数据库教学管理数据库.dbc”select 学号,姓名,性别,籍贯;from 学生表,3.查询经过计算的值或更改列标题名,SELECT子句中可以使用运算符来对列进行计算得到结果SELECT子句可以用更改字段名例10.4 对学生表的入学成绩除以600，求相对成绩，其显示的字段名为“相对成绩”set default to“D:我的数据库项目”open database“D:我的数据库项目数据库教学管理数据库.dbc”select 学号,姓名,入学成绩/600 as 相对成绩;from 学生表例10.5 求所有学生在2004年的年龄set default to“D:我的数据库项目”open database“D:我的数据库项目数据库教学管理数据库.dbc”select 学号,姓名,2004-year(出生日期)as 年龄;from 学生表,二、选择表中的若干元组(选择操作),使用SELECT语句的WHERE子句的条件来实行选择操作1.消除取值重复的行有时两个本来不完全相同的元组，在选择某些列后，可能变成完全相同了。如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL为默认值)，则表示不取消重复值。例10.6：输出学生表中所有的籍贯。select distinct 籍贯;from 学生表,二、选择表中的若干元组(选择操作),2.查询满足条件的元组查询满足指定条件的元组可以通过WHERE子句实现，WHERE子句常用的查询条件如表10.1所示。,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(1)大小比较在WHERE子句中可以使用关系运算符来构成条件。关系运算符包括：、=、)、=。例10.8：查找入学成绩大于等于600的同学的学号、姓名和入学成绩。select 学号,姓名,入学成绩;from 学生表;where 入学成绩=600,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(1)大小比较例10.9：查找学生表中的少数民族学生。select 学号,姓名;from 学生表;where 少数民族否=.T.例10-10：求1987年以后出生的学生学号和姓名。select 学号,姓名,出生日期;from 学生表;where 出生日期=1987/01/01,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(2)多重条件查询SELECT语句提供逻辑运算符AND和OR，可用来组合联结多个查询条件。例10.11：查找入学成绩在500分以上的少数民族学生的学号、姓名和少数民族否。select 学号,姓名,少数民族否;from 学生表;where 入学成绩=500 and 少数民族否=.T.例10.12：查找入学成绩在570分以上的女性学生，显示学号、姓名、性别和入学成绩。select 学号,姓名,性别,入学成绩;from 学生表;where 入学成绩=570 and 性别=女AND的优先级高于OR，但我们可以用括号改变优先级。,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(3)确定范围谓词BETWEENAND用来限定范围，其中BETWEEN后是范围的下限(即低值)，AND后是范围的上限（即高值）。例10.13：求入学成绩在500到600间的学生学号、姓名和入学成绩。select 学生表.学号,学生表.姓名,学生表.入学成绩;from 教学管理数据库!学生表;where 学生表.入学成绩 between 500 and 600上述SQL语句的条件等价于如下语句where 学生表.入学成绩=500 and 学生表.入学成绩=600,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(3)确定范围如果要求入学成绩不在500到600间的学生学号、姓名和入学成绩，使用下列SQL语句：select 学生表.学号,学生表.姓名,学生表.入学成绩;from 教学管理数据库!学生表;where 学生表.入学成绩 not between 1000 and 600,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(4)确定集合使用谓词 IN,NOT IN：用逗号分隔的一组取值例10.14：求籍贯为“江西南昌”或“吉林”的学生学号、姓名和籍贯。select 学生表.学号,学生表.姓名,学生表.籍贯;from 教学管理数据库!学生表;where 学生表.籍贯 in(吉林,江西南昌)上述SQL语句可以等价于如下SQL语句。select 学生表.学号,学生表.姓名,学生表.籍贯;from 教学管理数据库!学生表;where 学生表.籍贯=吉林;or 学生表.籍贯=江西南昌,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(4)确定集合例10.15：求籍贯不为“江西南昌”和“吉林”的学生学号、姓名和籍贯。select 学生表.学号,学生表.姓名,学生表.籍贯;from 教学管理数据库!学生表;where 学生表.籍贯 not in(吉林,江西南昌)上述SQL语句可以等价于如下SQL语句。select 学生表.学号,学生表.姓名,学生表.籍贯;from 教学管理数据库!学生表;where 学生表.籍贯!=吉林;and 学生表.籍贯!=江西南昌,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(5)字符匹配谓词LIKE可用来进行字符串的匹配。格式：NOT LIKE ESCAPE：指定匹配模板，固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代 LIKE 谓词，用#、!=或 运算符取代 NOT LIKE 谓词通配符：%(百分号)和_(下划线)%代表任意长度(长度可以为0)的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串 代表任意单个字符。例如a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(5)字符匹配例题例10-16：查找以姓“李”开头的学生学号和姓名。select 学号,姓名;from 学生表;where 姓名 like 李%“例10-17：查找以“强”字为最后一个字符的学生学号和姓名。select 学号,姓名;from 学生表;where 姓名 like%强,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(5)字符匹配例题例10-18：查找第二个字符为“永”字的学生学号和姓名。select 学号,姓名;from 学生表;where 姓名 like _永%“例10-19：查找江西籍的男性学生的学号和姓名select 学号,姓名,性别,籍贯;from 学生表;where 籍贯 like 江西%;and 性别=男,二、选择表中的若干元组(选择操作),2.查询满足条件的元组之(5)字符匹配例题例10-20：查找吉林或辽宁籍的学生学号、姓名、性别和籍贯。select 学号,姓名,性别,籍贯;from 学生表;where 籍贯 like 吉林%;or 籍贯 like 辽宁%“例10-21：查找非吉林和辽宁籍的学生学号、姓名、性别和籍贯。select 学号,姓名,性别,籍贯;from 学生表;where not(籍贯 like 吉林%);and not(籍贯 like 辽宁%),二、选择表中的若干元组（查询结果输出到表）,2.查询满足条件的元组之(6)查询结果输出到表SELECT默认输出给用户浏览。SELECT同时提供INTO或TO子句来将查询结果输出重定向，其格式为：INTO|TO FILE ADDITIVE|TO PRINTER1）INTO 表示将查询结果保存到目标中，目标的形式有三种：ARRAY：将查询结果存到指定的数组中。CURSOR：将查询结果存到一个游标中。所谓游标是一个临时表，不同之处在于一旦游标关闭就被删除。DBF|TABLE：将查询结果存到一个表，如果该表已经打开，则系统自动关闭该表。如果已经设置了SET SAFETY OFF(将安全功能关闭)，则重新打开它不提示。如果没有指定后缀，则默认为.dbf。在SELECT命令执行完后，该表为打开状态。,二、选择表中的若干元组（查询结果输出到表）,2.查询满足条件的元组之(6)查询结果输出到表2）TO FILE ADDITIVE 将查询结果输出到指定的文本文件，ADDTIVE表示将结果追加到原文件后面，否则将覆盖原有文件。3）TO PRINTER 的功能是将查询结果送打印机输出。例10-22：将籍贯为“吉林”或“辽宁”的学生学号、姓名、性别和籍贯输出到D盘的根目录。open database“D数据库教学管理数据库.DBC”&打开数据库,set default to D:where 籍贯 like“吉林%”or 籍贯 like“辽宁%”&运行查询close all&关闭数据库use D:aa.dbf&打开数据库aa.dbfbrowse&浏览查询结果,三、对查询结果排序,使用ORDER BY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示,三、对查询结果排序举例,例10-23：查询所有学生的入学成绩，查询结果按入学成绩的降序排列。select 学号,姓名,入学成绩;from 学生表;order by 入学成绩 desc例10-24：查询所有学生的入学成绩，查询结果按入学成绩的升序排列。select 学号,姓名,入学成绩;from 学生表;order by 入学成绩,三、对查询结果排序举例,例10-25：按入学成绩排降序和出生日期排降序输出学号、姓名、入学成绩和出生日期。select 学号,姓名,入学成绩,出生日期;from 学生表;order by 入学成绩 desc,出生日期 desc,四、使用聚集函数,聚集函数：指作用在一个集合上的函数，如求和函数sum、求平均值avg函数 SQL中的聚集函数,四、使用聚集函数例题,例10-26：求学生表中入学成绩在600分以上的人数。select count(*);from 学生表;where 入学成绩=600例10-27：求学生表中入学成绩的平均成绩select avg(入学成绩);from 学生表,五、对查询结果分组,使用GROUP BY子句分组 细化集函数的作用对象未对查询结果分组，集函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组,例10-28：按课程类别号，求各种类别课程的门数 select 课程类别号,count(*);from 课程表;group by 课程类别号,五、对查询结果分组,例10-29：求各个年级的班数 select 年级,count(*);from 班级表;group by 年级,五、对查询结果分组,GROUP BY子句的用法GROUP BY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和聚集函数,