第8章SQL语言、查询与视图

第八章SQL语言、查询与视图第八章SQL语言、查询与视图本章主要介绍关系数据库标准语言SQL，并在此基础上介绍查询与视图。通过查询设计器和视图设计器，可以从数据库中提取出目前用户所需要的数据，并保存起来，以便日后使用。而借助SQL语言可以更深入地操作数据库，建立更高效与复杂的查询与视图。第八章SQL语言、查询与视图8.1结构化查询语言SQLSQL(StructuredQueryLanguage)语言是1974年由Boyce和Chamberlin提出的，在IBM公司的SanJoseResearchLaboratory研制的SystemR上实现了这种语言。VisualFoxPro不支持全部的SQL语言，仅支持SELECT、CREAT、ALTER、INSERT、DELETE、UPDATE等几种命令，其功能如下：第八章SQL语言、查询与视图8.1.1SQL的数据定义1、定义表命令格式：CREATTABLE<表名>(<字段名1><类型>(<长度>[，<小数位数>])[，<字段名2><类型>(<长度>[，<小数位数>])]例8.1用SQL创建学生表

CREATTABLE学生(学号C(6)，姓名C(8)，性别L，出生日期D，奖学金N(6,2))说明：新表将在最低的可用工作区打开，并可通过它的别名来访问。新表以独占方式打开，与SETEXCLUSIVE的设置无关。第八章SQL语言、查询与视图2、修改表命令格式：ALTERTABLE<表名>ADD|ALTER[COLUMN]字段名1类型[(字段宽度[，小数位数])]参数说明：ADD[COLUMN]字段名1：指定要添加的字段名，一个单独的表可包含最多255个字段，如果一个或更多的字段允许空值，该限制数降至254上字段。ADD/ALTER[COLUMN]字段名1：指定要修改的字段名。例8.2向学生表添加年龄字段ALTERTABLE学生ADD年龄N(2)第八章SQL语言、查询与视图8.1.2SQL的数据修改VisualFoxPro提供了三条命令进行数据操作。1、INSERT命令格式1：INSERTINTO表名[(字段名1[，字段名2，])]VALUES(表达式1[，表达式2，])第八章SQL语言、查询与视图格式2：INSERTINTO表名FROMARRAY数组名|FROMMEMVAR说明：数组名，指定输入到新记录的字段值。如果存在字段名参数表，则数组各个元素的值依次赋予指定的字段名；否则，数组各个元素的值依次赋予表结构定义的字價。如果数组元素的个数多于字段的个数，则忽略多余的数组元素；如果数组元素的个数少于字段的个数，则多余的字段要么为表设计中定义的默认值，要么为空值。例8.3向学生表中插入一条记录。INSERTINTO学生(学号，姓名，性别)VALUES(“990203”，“杨阳”，.T.)第八章SQL语言、查询与视图2、DELETE命令DELETE命令给表中的记录加上删除标记。格式：DELETEFROM[数据库名!]表名[WHERE过滤条件1[AND|OR过滤条件2]]例8.4删除学生表中的李华DELETEFROM学生WHERE姓名=“李华”第八章SQL语言、查询与视图3、UPDATE命令UPDATE命令用新值替换表中现有记录的值。格式：UPDATE[数据库名!]表名SET字段名1=表达式1[，字段名2=表达式2]WHERE过滤条件1[AND|OR过滤条件2]]例8.5将学生表中林虹的奖学金增加100元。UPDATE学生SET奖学金=奖学金+100WHERE姓名=“林虹”第八章SQL语言、查询与视图8.1.3SQL的数据查询格式：SELECT[ALL|DISTINCT]<字段名>[AS列名][字段名[AS列名]，]FROM<表或视图名1>[[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOIN[<表或视图名2>]ON<联接条件>]][WHERE<条件表达式>[AND<条件表达式>]][GROUPBY<分组表达式>[，<分组表达式>，]][HAVING<条件表达式>][ORDERBY<字段名>[ASC|DESC]][INTO<输出目标>]|[TOFILE<文件>[ADDITIVE]]|[TOPRINTER[PROMPT]]|[TOSCREEN]第八章SQL语言、查询与视图说明：SELECT后紧跟的是将要查询的数据；FROM子句指出查询数据的来源，即属于哪个表；WHERE子句表示查询的数据满足的条件；GROUP子句说明查询结果的分组依据；HAVING子句限定分组所需满足的条件；ORDERBY子句用来对查询的结果进行排序。第八章SQL语言、查询与视图1、简单查询(1)只有FROM子句查询指定列例8.6查询学生表中的学生的年龄SELCET姓名，年龄FROM学生查询全部列列出表中的所有字段，一是在SELECT后列出所有字段名，一是使用“*”。(注意，有的数据库管理系统不支持“*”)例8.7查询学生表中全体学生的所有信息。SELCECT*FROM学生或SELECT学号，姓名，性别，出生日期，简历第八章SQL语言、查询与视图(2)WHERE子句说明：关键字LIKE可以用来进行字符串的匹配，语法格式如下：[NOT]LIKE‘<匹配串>’{ESCAPE‘<换码字符>”}在匹配串中可以使用通配符“&”，“_”。%代表任意长度(长度可为0)的字符串_(下划线)代表任意单个字符第八章SQL语言、查询与视图例8.8查询9901班的全体学生SELECT*FORM学生WHERESUBSTR(学号,1,4)="9901"例8.9查询成绩在70到80之间的学生SELECT学号，成绩FROM成绩WHERE成绩BETWEEN70AND80例8.10查询20岁和22岁的学生名单SELECT姓名FROM学生WHERE年龄IN(20,22)例8.11查询姓“王”的学生SELECT姓名FROM学生WHERE姓名LIKE“王%”第八章SQL语言、查询与视图(3)空值查询例8.12查询缺考学生的学号和课程号SELECT学号，课程号FROM成绩WHERE成绩ISNULL(4)GROUPBY子句将查询结果按某一列或多列值分组，值相等的为一组。例8.13将学生表中的学生按年龄进行分组，并统计各组的人数SELECT年龄，COUNT(姓名)FROM学生GROUPBY年龄第八章SQL语言、查询与视图(5)ORDERBY子句例8.14查询获得奖学金的学生姓名和奖学金，查询结果按奖学金降序排序。SELECT姓名，奖学金FROM学生WHERE奖学金>0的ORDERBY奖学金DESC(6)计算列SELECT关键字后不仅可以跟字段名，还可以是表达式。例8.15查询全体学生的姓名和年龄SELECT姓名，YEAR(DATE())-YEAR(出生日期)AS年龄FROM学生第八章SQL语言、查询与视图2、嵌套查询例8.16查询选修了“0001”课程的学生名单SELECT姓名FROM学生WHERE学号INSELECT学号FROM成绩WHERE课程号=“0001”第八章SQL语言、查询与视图3、联接查询一个查询同时涉及两个以上的表，则称为联接查询。联接有4种类型，在FROM子句指明：INNERJOIN：内部联接，结果集中包含联接条件左右的两个表中满足联接条件的记录。LEFTJOIN：左联接，结果集中包含联接条件左边的表中所有记录和联接条件右边的表中满足联接条件的记录。RIGHTJOIN：右联接，结果集中包含联接条件右边的表中所有记录和联接条件左边的表中满足联接条件的记录。FULLJOIN：完全联接，结果集中包含两个表中的所有记录，不论是否满足联接条件。缺省为内部联接，除了四种连接外，还可以用WHERE条件实现查询。第八章SQL语言、查询与视图例8.17查询每个学生的选课成绩(1)内部联接SELECT学生.*,成绩.*FROM学生INNERJOIN成绩;ON学生.学号=成绩.学号(2)左联接，除满足条件的记录外，左表中不满足条件的记录也出现在结果中SELECT学生.*,成绩.*FROM学生LEFTJOIN成绩;ON学生.学号=成绩.学号(3)右联接，除满足条件的记录外，右表中不满足条件的记录也出现在结果中SELECT学生.*,成绩.*FROM学生RIGHTJOIN成绩;ON学生.学号=成绩.学号第八章SQL语言、查询与视图(4)完全联接，结果包括两个表中的所有记录SELECT学生.*,成绩.*FROM学生FULLJOIN成绩;ON学生.学号=成绩.学号(5)用WHERE子句实现，结果同内部联接。SELECT学生.*，成绩.*FROM学生，成绩WHERE学生.学号=成绩.学号第八章SQL语言、查询与视图4、使用列函数SQL的列函数：注意：列函数中可以使用DISTINCT，表示计算时取消指定列中的重复值。在COUNT()函数中，“*”与DISTINCT不能同时使用。第八章SQL语言、查询与视图5、查询结果的输出定向：第八章SQL语言、查询与视图8.2建立查询利用SQL命令可以方便、迅速地建立查询，但是对初学者来说，还是复杂了些。在VisualFoxPro中，还可以使用查询向导和查询设计器来生成查询文件。8.2.1查询向导根据第七章建立的数据库，使用查询向导建立如下查询：显示“学生”表的学号、姓名及选修课程的成绩，按“学号”升序排序。第八章SQL语言、查询与视图1、打开查询向导在“文件”菜单中选择“新建”，在弹出的新建对话框中，在文件类型中选择查询，选择向导按钮，弹出对话框，其中列出了3种向导供用户选择，查询向导、交叉表向导、图形向导。选择查询向导，按“确定”按钮，进入查询向导第一步。查询向导第一步：选择要查询的表及字段。查询向导第二步：为表建立关系查询向导第三步：设置联接条件查询向导第四步：筛选记录第八章SQL语言、查询与视图查询向导第五步：记录排序查询向导第六步：限制记录查询向导第七步：完成保存查询：仅保存建立的查询保存并运行查询：保存查询后立即运行查询保存查询并在“查询设计器”中修改最后，运行查询第八章SQL语言、查询与视图8.2.2查询设计器使用查询设计器的步骤如下：打开查询设计器打开(或添加)数据库或自由表选择字段设置联接筛选排序依据分组依据杂项第八章SQL语言、查询与视图1、打开查询设计器在选择“文件”菜单中选择“新建”，在弹出的“新建”对话框的文件类型中选择“查询”，单击“新建文件”按钮，打开查询设计器，同时还为查询选择表或者视图，单击“添加”按钮添加到查询设计器中。每添加一个新表，如果与查询设计器中已有的表有相同字段，则系统提示在两表中设置联接条件，建立联接的两表间的联接字段有直线相连。如果还想添加自由表，单击“其他”按钮。所有的表或视图添加完毕，则可以通过选项卡来定义输出结果，查询设计器中包括字段、联接、筛选、排序依据、分组依据、杂项6个选项卡。在查询设计器中，可以在建立查询的过程中查看查询结果，单击常用工具栏上的！按钮或在“查询”菜单中选择“运行查询”命令即可。第八章SQL语言、查询与视图2、字段在该选项卡除了选择源表中的各字段外，还可以为查询添加统计项。3、联接如果在创建查询前已经在表之间建立了关系，VisualFoxPro将自动创建相关的联接，默认为内部联接。如果没有，在选择表或视图时，系统会提示建立联接，联接条件有4种类型，建立好联接后，在联接选项卡中显示当前已经建立的联接。在联接选项卡中可以修改已建立的联接、建立新的联接及删除联接。第八章SQL语言、查询与视图4、筛选在筛选选项卡可以设置查询条件。5、排序依据在该选项中设置查询结果的输出顺序。6、分组依据所谓分组即是将一组类似的记录压缩成一个记录，这样就可以完成基于一组记录的计算。分组还可以与其它函数联合使用，如SUM、AVG、COUNT等。第八章SQL语言、查询与视图8.3视图视图是一个定制的虚拟表，其数据可以来源于一个表或多个表，或其他视图。视图本身并不保存任何数据。视图可分为本地视图和远程视图。视图是可更新的，修改视图中的数据时，实际上是直接修改了其数据源中的数据。第八章SQL语言、查询与视图建立视图有以下优点：保障数据的安全性和完整性。简化对数据库的操作。从多角度分析同一数据。视图兼有表和查询的特点。第八章SQL语言、查询与视图8.3.1创建视图创建视图也有两种方式：视图向导和视图设计器，另外，与查询一样还可以用SQLSELECT语言来创建视图。一、视图设计器的使用步骤：打开数据库打开视图设计器选择字段设置联接筛选排序依据分组依据更新条件杂项第八章SQL语言、查询与视图二、更新条件选项卡视图设计器同查询设计器很类似，只是多了一个“更新条件”选项卡，其它选项卡的使用同查询设计器。1、表指定视图中可以更新的表，系统默认是“全部表”。2、“字段名”列表框显示可标志为关键字字段或可更新字段的字段名。第八章SQL语言、查询与视图在“字段名”列表框中列出了选定表的所有选定字段，在这些字段名的左面的两列是标记列，其中钥匙列用来确定关键字字段，铅笔列确定可更新字段。关键字段：若钥匙列中显示“√”标记，则表明该字段是关键字段，用鼠标单击字段前的小方块按钮进行设定。可更新字段：在铅笔列中显示“√”标记，则表明该字段是可更新的，也可以通过鼠标单击字段前的小方块按钮进行设定。重置关键字：单击该按钮，系统自动使用每个表的主索引作为关键字，在钥匙列加上“√”标记。全部更新：选择除了关键字字段以外的所有选定字段进行更新，并对“字段名”列表框中铅笔列有“√”标记的字段发送SQL更新。系统通过关键字使视图和源表相联系。只有指定关键字后，系统才允许选择通过视图来更新源表的字