第5章关系数据库标准语言SQL

*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第,5,章,关系数据库标准语言,SQL,SQL,全称是结构化查询语言（,Structured Query Language,），它是国际标准数据库语言，如今无论是,Visual,Foxpro,、,Access,这样的微机上常用的小型数据库管理系统，还是,Oracle,、,Sybase,、,Informix,、,SQL Server,这样的大型数据库管理系统，都支持,SQL,语言。,SQL,命令可以替代多条,Visual FoxPro,命令。,5.1 SQL,语言概述,结构化查询语言,SQL,是一种介于关系代数与关系,演算之间的语言。它的主要特点如下：,1.,一体化语言,2.,高度非过程化语言,3,语言简洁，易学易用,4,统一的语法结构,5,视图数据结构,6,Visual FoxPro,中的,SQL,5.2 SQL,的数据查询功能,数据查询是对数据库中的数据按指定条件和,顺序进行检索输出。,5.2.1 SELECT,语句格式,Select,语句的基本格式为：,select from where ,5.2.2,投影查询,投影查询是指从表中查询全部列或部分列。,1.,查询全部字段,【,例,5-1】,查询职工信息表中全部信息。,SELECT * FROM,职工信息表,等价于下面的查询语句：,SELECT,职工号,姓名,性别,年龄,婚否,职称,部门编号,出生日期,FROM,职工信息表,说明：星号“*”表示表中所有字段,2.,查询部分字段,【,例,5-2】,查询职工信息表中职工号，姓名和年龄。,SELECT,职工号,姓名,年龄,FROM,职工信息表,3.,取消重复记录,在,SELECT,语句中，可以使用,DISTINCT,来取消查询结果中重复的记录。,【,例,5-3】,查询职工表中所有的部门编号。,SELECT DISTINCT,部门编号,FROM,职工信息表,4.,查询经过计算的表达式,【,例,5-4】,查询职工工资表中的职工号，基本工资，岗位工资，院龄工资，职务工资，税金和实发工资。,SELECT,职工号,基本工资,岗位工资,院龄工资,职务津贴,税金,;,(,基本工资,+,岗位工资,+,院龄工资,+,职务津贴,-,税金,) AS,实发工资,FROM,职工工资表,说明：,AS,用于修改查询结果中指定列的列名。,AS,也可以省略。,5.2.3,条件查询,SELECT-SQL,语句的查询方式很丰富，使用,WHERE,子句可以查询满足某些条件的记录。在,WHERE,子句中可以用关系运算符、逻辑运算符及特殊运算符构成较复杂的条件表达式。,1.,单条件查询,【,例,5-5】,查询职工信息表中已婚职工的信息。,SELECT * FROM,职工信息表,WHERE,婚否,【,例,5-6】,查询职工表中年龄大于等于,30,岁的职工的姓名，年龄和职称。,SELECT,姓名,年龄,职称,FROM,职工信息表,WHERE,年龄,=30,2.,多重条件查询,【,例,5-7】,查询职工信息表中出生日期小于,1970,年副教授的姓名、年龄、职称和出生日期。,SELECT,姓名,年龄,职称,出生日期,FROM,职工信息表,WHERE,职称,=”,副教授”,AND;,YEAR(,出生日期,)2000,【,例,5-15】,统计职工信息表中有几个部门。,SELECT COUNT(DISTINCT,部门编号,) AS;,部门数,FROM,职工信息表,【,例,5-16】,统计职工部门表中职工人数的最大人数、最小人数、平均数和总人数。,SELECT MAX(,职工人数,)AS,最大人数,;,MIN(,职工人数,) AS,最小人数,;,AVG(,职工人数,) AS,平均人数,SUM(,职工人数,) ;,AS,总人数,FROM,职工部门表,2.,分组查询,SQL,可以利用,GROUP BY,子句按某个字段或多个字段进行分组，每组在某个字段或多个字段组合上的值要相同。,【,例,5-17】,分组求职工信息表中男女职工的平均年龄。,SELECT,性别, AVG(,年龄,) FROM,；,职工信息表,GROUP BY,性别,【,例,5-18】,查询职工信息表中至少有,4,个职工的部门。,SELECT,部门编号,COUNT(*) AS,人数,FROM,职工信；息表,GROUP BY,部门编号,HAVING COUNT(*)=4,说明：,HAVING,子句限定分组条件。,5.2.5,排序查询,在,SQL,语言中，,ORDER BY,子句用于对查询结果按一个或多个查询列进行排序。其中包括升序（,ASC,）和降序（,DESC,），缺省值为升序。,1.,单列排序,【,例,5-19】,查询职工信息表中年龄,40,岁以上按出生日期字段降序排列的全部职工信息。,SELECT * FROM,职工信息表,;,WHERE,年龄,=40 ORDER BY,出生日期,DESC,2.,多列排序,查询结果中按多个查询列排序。,【,例,5-20】,查询职工信息表中，部门编号为“,103”,的职工信息，结果按性别的升序、年龄的降序排列。,SELECT * FROM,职工信息表,WHERE;,部门编号,=”103”ORDER BY,性别,年龄,DESC,3.,前面部分记录查询,在查询语句中，,TOP PERCENT,子句查询满足条件的前面部分记录，其中的表达式必为数值表达式。,【,例,5-21,】,查询职工信息表中，年龄最大的三人信息。,SELECT * TOP 3 FROM,职工信息表,;,ORDER BY,年龄,DESC,【,例,5-22】,查询职工信息表中，年龄最小的,20%,的员工信息。,SELECT * TOP 20 PERCENT FROM,职工信息表,;,ORDER BY,年龄,5.2.6,连接查询,一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。,内连接查询,命令的语法格式如下：,SELECT FROM , WHERE,连接条件,AND,查询条件,说明：连接条件为：表,1.,公共字段,=,表,2.,公共字段,2.,自连接查询,自身连接查询指连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接的查询。在连接查询中，为了区分字段名，常用表名作为字段名的前缀，有时显得很麻烦。因此，,SQL,语言中允许表名定义别名。,命令的语法格式如下：,. ,【,例,5-25】,查询职工信息表中，年龄比曾玲珑年长的职工姓名和年龄。,SELECT a.,姓名,a.,年龄,FROM,职工信息表,a, ;,职工信息表,b;,WHERE a.,年龄,b.,年龄,AND b.,姓名,=”,曾玲珑”,5.2.7,超连接查询,SQL,中,FROM,子句后的连接称为超连接，超连接有四种形式。,命令的语法格式如下：,SELECT.FROM INNER | LEFTOUTER|RIGHTOUTER|FULLOUTER;,JOIN ! AS,Local_AliasON,其中：,OUTER,关键字可被省略，包含,OUTER,强调这是一个外连接（,outer join,）。,1,内部连接,使用,INNER JOIN,形式的连接称为内部连接，,INNER JOIN,等价于,JOIN,。,INNER JOIN,与普通连接相同：只有满足条件的记录才出现在查询结果中。,【,例,5-26】,将职工信息表和职工部门表按内部形式连接，包含职工号、姓名、部门名称字段。,SELECT A.,职工号,姓名,部门名称,FROM ;,职工信息表,A INNER JOIN,职工部门表,B;,ON A.,部门编号,=B.,部门编号,2,左连接,LEFTOUTER JOIN,称为左连接，在查询结果中包含,JOIN,左侧表中的所有记录，以及,JOIN,右侧表中匹配的记录。,3,右连接,RIGHTOUTER JOIN,称为右连接，在查询结果中包含,JOIN,右侧表中的所有记录，以及,JOIN,左侧表中匹配的记录。,4,完全连接,FULLOUTER JOIN,称为完全连接，在查询结果中包含,JOIN,两侧表中的所有匹配记录和不匹配的记录。,5.2.8,嵌套查询,带有比较运算符的子查询,在嵌套查询中，当子查询的结果是一个单值（只有一个记录或一个字段值 ），可以用,、,=,、,2000),3.,带有,ANY,或,ALL,谓词的子查询,在嵌套查询中，子查询的返回值不止一个，则必须在,WHERE,子句中指明应怎样使用这些返回值。通常使用谓词,ANY,和,ALL,。,IN,也可等同使用。,【,例,5-32】,查询职工的年龄大于部门编号,=“101”,的任何一个职工年龄的职工的姓名。,SELECT,姓名,FROM,职工信息表,;,WHERE,年龄,ANY;,(SELECT,年龄,FROM,职工信息表,;,WHERE,部门编号,=”101”),【,例,5-33】,查询职工的年龄大于部门编号,=“101”,的所有职工年龄的职工的姓名。,SELECT,姓名,FROM,职工信息表,;,WHERE,年龄,ALL;,(SELECT,年龄,FROM,职工信息表,;,WHERE,部门编号,=”101”),4.,带有,EXISTS,谓词的子查询,在嵌套查询中，,EXISTS,是用来检查子查询中是否有结果返回（是否为空）。,NOT EXISTS,表示是空的结果集。,【,例,5-34】,查询职工库中，不存在的部门名称的职工信息。,SELECT * FROM,职工信息表,WHERE NOT EXISTS;,(SELECT * FROM,职工部门表,WHERE,部门编号,=,职工,;,信息表,.,部门编号,),5.2.9,集合查询,UNION,子句可以进行集合的并运算，即可以将两个,SELECT,语句的查询结果合并成一个查询结果。当然，要求进行并运算的两个查询结果具有相同的字段个数，并且对应字段的值要具有相同的数据类型和取值范围。,UNION,子句的语法格式：, UNION ALL ,【,例,5-35】,查询职工信息表中，助教和讲师的职工信息。,SELECT * FROM,职工信息表,;,WHERE,职称,=”,助教”,UNION ;,SELECT * FROM,职工信息表,;,WHERE,职称,=”,讲师”,5.2.10,查询去向,SELECT,语句默认的输出去向是在浏览窗口中显示查询结果。可使用特殊的子句来修改,SELECT,语句的查询结果的输出去向。,1.,将查询结果存放到永久表中,使用子句,INTO DBF|TABLE ,，可以将查询结果存放到永久表中，为,DBF,文件并成为当前表。,2,将查询结果存放到临时文件中,使用子句,INTO CURSOR ,3,将查询结果存放到数组中,使用子句,INTO ARRAY ,4,将查询结果存放到文本文件中,使用子句,TO FILE ADDITIVE,5,将查询结果直接输出到打印机,使用子句,TO PRINTER PROMPT,5.3 SQL,的数据操纵功能,5.3.1,插入数据,插入数据是不断地向已存在的表中插入新记录，这由,INSERT,命令实现。,命令的语法格式如下：,INSERT INTO ( ,) ;,VALUES (,),功能：在指定表的表尾添加一条新记录，其值为,VALUES,后面表达式的值。,【,例,5-40】,在职工信息表中插入一条新记录。,INSERT INTO,职工信息表,(,职工号,姓名,性别,年龄,;,婚否,职称,部门编号,出生日期,);,VALUES(”107033”,”,张为民”,”,男”,24,.T.,;,”,助教,”107”, 1985/04/01),5.3.2,更新数据,更新数据就是对存储在表中的记录进行修改，命令为,UPDATE,。可以对用,SELECT,语句选择出的记录进行数据更新。,命令的语法格式如下：,UPDATE ,；,SET =,=,；,WHERE AND|OR ,【,例,5-41】,给职工信息表中所有女职工的年龄增加,1,岁。,UPDATE,职工信息表,SET,年龄,=,年龄,+1 WHERE,性别,=”,女”,5.3.3,删除数据,DELETE,语句可以为指定的数据表中的记录加删除标记。,命令的语法格式如下：,DELETE FROM ,数据库名,!;,WHERE AND | OR ,【,例,5-42】,给职工信息表中男职生的记录加上删除标记。,DELETE FROM,职工信息表,WHERE,性别,=”,男”,5.4 SQL,的数据定义功能,SQL,使用数据定义语言,DDL,实现定义功能，它包含,3,个基本命令，即：,CREATE,、,ALTER,、,DROP,，如表,5-4,所示。利用这些命令可以方便地完成对各类数据对象的创建、修改和删除，如数据库、数据表、视图、索引和永久关系。,5.4.1,建立表结构,在,SQL,语言中，用,CREATE TABLE,命令建立表的结构。该命令可以指明表名及结构，包括表中各字段的名字、类型、精度、比例、是否允许空值及参照完整性规则。,【,例,5-43】,建立学生表，该表不属于任何数据库为自由表，其结构为见表,5-5 .,CREATE TABLE,学生,FREE(,学号,C(10),姓名,C(8),;,性别,L,年龄,N(3),入学年月,D NULL),【,例,5-44】,假设已经建立了职工数据库，在职工中建立职工信息表，该表结构见第,4,章。,OPEN DATABASE,职工,&,打开职工数据库,CREATE TABLE,职工信息表,(,职工号,C(5) ;,PRIMARY KEY,姓名,C(8) NOT NULL,;,性别,C(2),年龄,N(3) CHECK,年龄,=20 AND,年龄,=0”,其默认值为,0,。,ALTER TABLE,职工信息表,ADD;,基本工资,N(4,0) CHECK,基本工资,=0;,ERROR “,基本工资必须大于或等于,0”DEFAULT 0,2.,修改字段及数据完整性规则,【,例,5-49】,将职工信息表中的“职称”字段宽度修改为,C(8),。,ALTER TABLE,职工信息表,ALTER,职称,C(8),【,例,5-50】,设置职工信息表中的“婚否”字段的默认值为,.T.,。,ALTER TABLE,职工信息表,ALTER,婚否,;,SET DEFAULT .T.,3.,修改字段名,【,例,5-9】,将职工信息表中的“出生日期”字段名修改为“出生时间”。,ALTER TABLE,职工信息表,RENAME,出生日期,;,TO,出生时间,4.,删除字段,【,例,5-51】,删除职工信息表中的“基本工资”字段。,ALTER TABLE,职工信息表,DROP,基本工资,5.4.3,删除数据表,在,SQL,语言中，删除表使用,DROP TABLE,命令。,【,例,5-52】,删除数据库表选课。,OPEN DATABASE,职工,DROP TABLE,职工部门表,5.4.4,定义,/,删除视图,1.,定义视图,视图是根据对表的查询定义的（详见第,6,章）,.,【,例,5-53】,定义一个视图，它包含教授的姓名、年龄和职称信息。,CREATE VIEW,教授,AS;,SELECT,姓名,年龄,职称,FROM,职工信息表,;,WHERE,职称,=”,教授”,2,删除视图,【,例,5-54】,下述代码删除视图,v_,本科。,DROP VIEW,教授,本章小结,1,关系数据库的标准语言,SQL,是所有关系数据库系统都能使用的统一语言，掌握了它，等于掌握了所有数据库系统操作的基本方法，因此要求大家都要熟练掌握它。标准语言,SQL,是一种具有一体化并高度非过程化的语言、语言简洁，易学易用、有通一的语法结构、通一的视图数据结构等特点。,2,关系数据库的标准语言,SQL,只有,7,个命令动词，其中,SELECT,动词是其中最重要的一个，主要是负责数据查询。本章详细地通过实例讲解了,SQL,查询功能的使用方法。可以实现简单查询、条件查询、统计及分组查询、排序查询、连接查询、超连接查询、嵌套查询、使用库函数查询、集合查询、查询去向等方法。要求学生熟练掌握。,3,SQL,数据操纵功能包括插入数据、更新数据和删除数据。在这一部分的学习中，学生们可以了解当数据要批量完成的时候，可以更好的运用,SQL,的操纵功能。,4,SQL,数据定义功能包括：定义基本表、定义视图、修改表结构（包括增加或修改字段及数据完整性规则、删除字段）定义同义词和建立索引。在这之前，我们学习过运用交互式的方法建表、视图及完整性。学习,SQL,定义功能后学生们可以更好的应用到程序中。,