全国二级VFP等级考试培训课件第4章 结构化查询语言SQL

第4章结构化查询语言SQLSQL(StructuredQueryLanguage)：结构化查询语言，是现在应用最广泛的关系数据库语言。4.1SQL概述SQL语言的特点：一体化语言；高度非过程化语言；语言简洁（表4.1）；可以直接以命令方式交互使用。VF在SQL方面支持数据定义、数据查询和数据操纵功能。,4.2SQL的数据查询功能SELECT命令的格式P1284.2.1投影查询SELECTALL/DISTINCTTOPPERCENT.AS,.ASFROM!,!ORDERBYASC/DESC,ASC/DESC说明:(1)FROM!-指明要查询的所有数据表。!指定非当前数据库表.,(2).AS:可以是字段名,也可是表达式，而是字段所在的表名；AS表示可以给输出时显示的列标题重新命名。可用*来表示,此时指定所有的字段。例：查询学生的基本情况SELE学号，姓名，入校总分，性别，出生年月FROM学生例：查询教师表的全部信息SELE*FROM教师,(3)TOPPERCENT：指定查询结果包括特定数目的行数(或包括全部行数的百分比）注：使用TOP子句时必须用ORDERBY子句例：查询入校总分前五名学生基本情况，按入校总分由低到高进行排序，同时指定表中的字段在查询结果中的列标题SELECTTOP5学号AS学生编号,；姓名AS学生姓名,入校总分AS高考分数；FROM学生ORDERBY入校总分,(4)ALL(缺省)表示选出的记录中包含重复记录,DISTINCT则表示选出的记录中不包含重复记录.例：查询选修了课程的学生号SELEDISTINCT学号FROM选课练习：1)列出SX表中所有记录的名称和价格2)列出SX表中所有记录的编号、名称和是否是主要设备的情况，并注明“编号”为“货物货号”，“名称”为“货物名称”，即：SELECT名称,价格FROMSXSELECT编号AS货物货号，名称AS货物名称，主要设备；FROMSX,4.2.3条件查询使用WHERE子句一、比较大小例查询选修课程号为“C140”的学生的学号和成绩SELECT学号，成绩FROM选课；WHERE课程号=“c140”例查询成绩高于90分的学生的学号、课程号和成绩SELECT学号，课程号，成绩FROM选课；WHERE成绩=90,二、多重条件查询当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连接成复合逻辑表达式，其优先级为NOT、AND、OR，当然可以用括号改变优先级。例:查询选修课程号为“C120”或“C140”且分数大于等于80分学生的学号、课程号和成绩SELECT学号,课程号,成绩FROM选课；WHERE(课程号=“c120”OR课程号=“c140”)；AND成绩=80,三、确定范围例：查询工资在1500至2000之间的教师的教师号SELECT教师号,姓名,职称FROM教师；WHERE工资BETWEEN1500AND2000例：查询工资不在1500至2000之间的教师的教师号SELECT教师号,姓名,职称FROM教师；WHERE工资NOTBETWEEN1500AND2000,四、确定集合“IN”：查询属性属于指定集合的元组例：查询选修了课程“C140”或“C160”且成绩在80分以上的学生的学号、课程号和成绩SELECT学号,课程号,成绩FROM选课；WHERE课程号IN(“c140”，“c160”)AND成绩=80“NOTIN”：查询指定集合外的元组例：查询没有选修“C140”，也没有选修“C160”课程且成绩在80和90分之间的学生的学号、课程号和成绩SELECT学号,课程号,成绩FROM选课；WHERE课程号NOTIN(“c140”，“c160”)AND成绩;BETWEEN85AND90,五、部分匹配查询格式：LIKE注：属性名必须为字符型%表示任意长度的字符串_表示任意一个字符或汉字，有的系统要用两个下画线才表示一个汉字。例：查询所有姓“张”的学生的学号和姓名SELECT学号,姓名FROM学生;WHERE姓名LIKE张%例：查询姓名中第二个汉字是“红”的学生的学号和姓名SELECT学号,姓名FROM学生;WHERE姓名LIKE_红%,练习：1)查询STUD表中女生的姓名、性别和成绩2)在教工数据表JG中，查询职称为“助教”或“讲师”人员的姓名，工资和部门。SELECT姓名，工资，部门（1）JGWHERE职称（2）1、SELECT姓名，性别，成绩FROMSTUDWHERE性别=“女”2、SELECT姓名,工资,部门FROMJGWHERE职称IN(“助教”,“讲师”),4.2.4统计查询常用函数及功能：AVG：按列计算平均值SUM：按列计算值的总和COUNT：按列值统计个数MAX：求一列中的最大值MIN：求一列中的最小值说明：在以上函数中，可使用ALL或DISTINCTDISTINCT：表示在计算时取消指定列中的重复值ALL(默认值)：表示不取消重复值,例：求学号为“s0201109”学生的总分和平均分（显示学号）SELECT学号,SUM(成绩)AS总分,AVG(成绩)AS平均分;FROM选课WHERE学号=“s0201109”例：求选修课程号为“C140”学生的最高分、最低分及之间相差的分数(显示课程号)SELECT课程号,MAX(成绩)AS最高分,MIN(成绩)AS最低分,MAX(成绩)-MIN(成绩)AS相差分数;FROM选课WHERE课程号=“c140”,例：求入校总分在580分以上的学生的人数SELECTCOUNT(学号)AS入校总分在580分以上的人数;FROM学生WHERE入校总分=580例：统计选课表中有多少门课SELECTCOUNT(DISTINCT课程号)AS选课表中课程数;FROM选课注：COUNT(*)用来统计元组个数，不消除重复行，不允许使用DISTINCT.例：求教师表中“教授”和“副教授”的人数SELECTCOUNT(*)AS教授和副教授的人数;FROM教师WHERE职称IN(“教授”,“副教授”),4.2.5分组查询一、GROUPBY子句使用使用GROUPBY子句可以将查询结果按属性万或属性万组合在等的方向上进行分组，每组在属性列或属性列组合上具有相同的值。例：查询各位教师的教师号及其任课门数。SELECT教师号，COUNT(*)AS任课门数;FROM授课GROUPBY教师号说明：GROUPBY子句按教师号的值分组，所有具有相同教师号的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。,二、HAVING子句若在分组后还要按照一定的条件进行筛选，则需要使用HAVING子句。例：查询选修两门以上课程的学生学号和选课门数。SELECT学号AS选课在两门以上学生的学号,COUNT(*)AS选课门数;FROM选课GROUPBY学号HAVINGCOUNT(*)=2说明：GROUPBY子句按学号分组，所有具有相同学号的元组为一组，对每一组使用COUNT进行计算，统计出每位学生选课的门数，HAVING子句用于去掉不满足选课门数在两门以上的组。,注意：当在一个SQL查询中同时使用WHERE子句、GROUPBY子句和HAVING子句时，其顺序是WHERE、GROUPBY、HAVING。WHERE与HAVING子句的根本区别在于作用对象不同，WHERE子句作用于基本表或视图，从中选择满足条件的元组，而HAVING子句作用于组，选择满足条件的组，必须用于GROUPBY子句之后，但GROUPBY子句可以没有HAVING子句。例：在课程“c120”、“c140”、“c150”和“c160”中查询学生平均成绩在80分以上课程的学生的平均分（显示课程号）。,SELECT课程号,AVG(成绩)AS平均分FROM选课;WHERE课程号IN(“c120”,“c140”,“c150”,“c160”);GROUPBY课程号HAVINGAVG(成绩)=80说明：WHERE子句在选课表中筛选出课程号为“c120”,“c140”,“c150”和“c160”的记录，GROUPBY子句按课程号的值分组，具有相同课程号的记录为一组，对每一组的成绩使用函数AVG进行计算，最后得到平均成绩在80分以上的课程的学生的平均分。,4.2.6查询的排序当需要对查询结果排序时，可用ORDERBY子句对查询结果按一个或多个属性列的升序(ASC)或降序(DESC)排列，默认值为升序。ORDERBY子句必须出现在其他子句之后。例：查询选修了课程“c140”的学生学号和成绩，并按成绩降序排列。SELECT学号,成绩FROM选课WHERE课程号=“c140”;ORDERBY成绩DESC例：查询选修“c120”,“c130”和“c150”课程学生的学号、课程号和成绩，查询结果按课程号升序排列，课程号相同再按成绩降序排列。select学号,成绩from选课;where课程号in(c120,c130,c150);orderby课程号,成绩desc,例：求选课在三门以上且各门课程均及格的学生的学号及其平均成绩，查询结果按学号降序列出。select学号,avg(成绩)as平均成绩from选课;where成绩=60;groupby学号havingcount(*)=3orderby学号desc说明：此语句的执行过程为：FROM取出整个选课数据表，WHERE筛选出成绩=60的记录，GROUPBY将选出的记录按学号分组，AVING筛选选课三门以上的分组，SELECT从剩下的组成提取学号平均成绩，ORDERBY将选取结果按学号排序。,4.2.7连接查询当一个查询同时涉及多个表时，称为连接查询。连接查询实际上是通过各个表之间共同属性列的关联来查询数据的，数据表之间的联系是通过表的字段值来体现的，这个字段称为连接字段。连接操作的目的就是通过加在连接字段上的条件将多个表连接起来，以便从多个表中查询数据。连接查询是关系数据库中最主要的查询，包括等值与非等值查询、自身连接查询、外连接查询等。一、等值与非等值查询表连接的两种方法方法一：表之间满足一定条件的行进行行连接，此时FROM子句中指明进行行连接的表名，WHERE子句指明连接的列名及其连接条件。,即：在WHERE子句中加入连接条件，同时在FROM子句中列出要查询的多个表，用逗号隔开可实现多表查询。例：查询陈静老师所讲授的课程号SELECT教师.教师号,姓名,课程号FROM教师,授课；WHERE教师.教师号=授课.教师号AND姓名=“陈静”注：表中的相同字段在使用时必须以.的格式引用。也可查询三个以上的表，例：查询学生姓名、所选课程名及成绩SELECT姓名,课程名,成绩FROM选课,学生,课程；WHERE选课.学号=学生.学号AND选课.课程号=课程.课程号,方法二：利用关键字JOIN进行连接INNERJOIN：内连接。只提取两个表中同时满足联接条件的数据，此为默认值。LEFTOUTERJOIN：左连接。只提取联接条件左侧表的所有数据和满足联接条件的右侧表的数据。RIGHTOUTERJOIN：右连接。只提取联接条件中右侧表的所有数据和满足联接条件的左侧表的数据。FULLJION：全连接。提取两表所有数据。当将JOIN关键词放于FROM子句中时，应有关键词ON与之相对应，以表明连接的条件。,例：方法二SELECT教师.教师号,姓名,课程号FROM教师；INNERJOIN授课ON教师.教师号=授课.教师号AND姓名=“陈静”二、自身连接查询当一个表与其自己进行连接操作时，称为表的自身连接，表的自身连接主要用于同一个表中数据的比较。例：查询所有比“陈静”工资高的教师姓名、职称、工资和陈静的工资。selex.姓名,x.工资,y.工资as陈静工资；from教师asx,教师asy；wherx.工资y.工资andy.姓名=“陈静,三、外连接查询在外部连接中，参与连接的表有主从之分，以主表中的每行数据去匹配从表中的数据列，符合连接条件的数据将直接显示，对于那些不符合条件的列，将填上NULL值显示。外部连接分为左外部连接和右外部连接，以主表所在的方向区分外部连接，主表在左边称为左连接，主表在右边称为右连接。例：以外连接的方式查询教师姓名、职称及所授课程名。SELECT姓名,职称,课程名FROM教师；LEFTJOIN授课ON教师.教师号=授课.教师号；LEFTJOIN课程ON授课.课程号=课程.课程号,4.2.8嵌套查询在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为嵌套查询或子查询，包含子查询的语句称为父查询或外部查询。子查询的嵌套层次最多可达255层。一、返回一个值的子查询当子查询的返回值只有一个时，可以使用比较运算符将父查询和子查询连接起来。例：查询与“陈静”教师职称相同的教师姓名和职称。SELECT姓名,职称FROM教师；WHERE职称=(SELECT职称FROM教师WHERE姓名=“陈静”),二、返回一组值的子查询如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。1、使用ANY例：查询讲授课程号为“c140”的教师姓名。SELECT姓名FROM教师；WHERE教师号=ANY(SELECT教师号FROM授课WHERE课程号=“c140”)2、使用IN可以使用IN代替“=ANY”,三、使用ALL例：查询高于男生入校总分最高分的女生姓名和入校总分SELECT姓名,入校总分FROM学生；WHERE入校总分ALL(SELECT入校总分FROM学生WHERE性别=“男”)；AND性别=“女”四、使用EXISTSEXISTS用于判断子查询结果是否存在。带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOTEXISTS则相反。含有IN的查询通常可用EXISTS表示，但反过来不一定。例：查询讲授课程号为c140的教师姓名SELECT姓名FROM教师；WHEREEXISTS(SELECT*FROM授课WHERE教师.教师号=授课.教师号AND课程号=c140),4.2.9查询结果输出使用INTO子句，可以将查询结果输出到一个新建的数据表或一个临时表，临时表只存储在内存中，关机后自动消失。其中：INTOTABLE表示输出到数据表INTOCURSOR表示输出到临时表例：将每个同学选修的课程名及成绩输出到数据表（表名为学生成绩）SELECT姓名,课程名,成绩FROM学生,选课,课程；WHERE学生.学号=选课.学号AND选课.课程号=课程.课程号；INTOTABLE学生成绩USE学生成绩BROWSE,说明：还有一些输出可选项，其含义如下：TOFILE将查询结果输出到文本文件TOPRINTER将查询结果打印输出TOSCREEN将查询结果输出到VisualFoxPro主窗口。,4.4SQL的数据定义功能,4.4.1创建数据表命令:CREATETABLE|DBFFREE(,小数位NULL/NOTNULLCHECKERRORDEFAULTPRIMARYKEY/UNIQUE,),功能：创建数据表结构。说明如下:FREE：指定创建自由表,当数据库没有打开时,不必指定该项。NULL/NOTNULL：表示是否允许字段值为空值。CHECK：用于指定字段的有效性规则。ERROR：指定当输入的字段值违反有效性规则时，显示提示信息。DEFAULT：认定字段的默认值。PRIMARYKEY：用于设置字段为主索引，一个表中只能有一个主索引，UNIQUE用于设置候选索引，不允许出现NULL值，同一个字段不能既是主索引，也就是说，PRIMARYKEY和UNIQUE不能同时出现在一个字段的定义中。注:选项CHECK、DEFAULT和PRIMARYKEY对非.DBC表不可用。,例：创建学生基本情况数据库表CREATETABLE学生(学号C(8),姓名C(8),性别C(2)DEFAULT“男”,出生年月D,入校总分N(3)4.4.2修改数据表命令1:ALTERTABLE|DBFADD/ALTERCOLUMN,小数位NULL/NOTNULLCHECKERRORDEFAULTPRIMARYKEY/UNIQUE,功能：修改基本表的结构说明：ADD：增加新列ALTER：修改列例：在“学生”表中增加一个电话和住址列ALTERTABLE学生ADD电话C(8)ALTERTABLE学生ADD住址C(8)或ALTERTABLE学生ADD电话C(8)ADD住址C(8),命令2:ALTERTABLE|DBFDROPCOLUMNSETDEFAULTDROPDEFAULTSETCHECKERRORDROPCHECKADDPRIMARYKEYDROPPRIMARYKEYADDUNIQUEDROPUNIQUETAGRENAMECOLUMNTO,说明:DROP用于删除列、默认值、有效性规则，主键、候选索引。SET用于为已有的字段设置默认值和有效性规则。ADD用于添加主索引和候选索引。RENAME用于修改表中的一个字段名。例：把“学生”表中姓名字段加宽到10位字符ALTERTABLE学生ALTERCOLUMN姓名CHAR(10)例：将“学生”表中“入校总分”字段改名为“高考成绩”ALTERTABLE学生RENAMECOLUMN入校总分TO高考成绩,4.4.3表的删除,DROPTABLE表名从磁盘上直接删除表名所对应的.dbf文件。在使用该命令删除数据库表的时候应该在数据库打开的状态下删除，否则会出现错误提示。,4.3SQL的数据更新功能4.3.1插入数据记录格式1：INSERTINTO(,)VALUES(,)功能:在表的末端追加包含指定值的新记录。例如：在表选课中加入一条记录，其字段值分别是：s0201112,c120,85。insertinto选课(学号,课程号,成绩);values(s0201112,c120,85),说明:(1)字段名的排列顺序不一定要和表定义时的顺序一致，但当指定字段名时，VALUES子句值的排列顺序必须和字段名的排列顺序一致，个数相等，数据类型一一对应，INTO语句中没有出现的字段名，新记录在这些字段上将取空值（如果在表定义时说明了NOTNULL的属性不能取空值）。如果INTO子句没有带任何字段名，则插入的新记录必须在每个属性列上均有值。(2)各字段名和数据必须用逗号分开，字符型数据要用引号引起来，日期型数据需要用函数CTOD进行转换。,格式2：INSERTINTOFROMARRAY数组名|FROMMEMVAR注意：当一个表中设置了主索引或候选索引后应用此命令来添加数据。,4.3.2修改数据记录格式:UPDATESET=,=,WHERE功能：用新的值更新表中的记录说明：当WHERE子句省略时，则修改表中的所有记录。例：把授课数据表中的教师号为“t1101”的教师所授的课程号改为“c170”。upda授课set课程号=c170where教师号=t1101例upda教师set职称=副教授where姓名=陈静例upda教师set工资=工资*1.2where工资=2000例upda选课set成绩=0；where学号in(select学生.学号from学生,选课；where学生.学号=选课.学号and姓名=王小平),4.3.3删除数据记录格式：DELETEFROMWHERE功能：给要删除的记录作标记说明：当WHERE子句省略时，则删除表中的所有记录。例：给选课表里成绩等于90的记录作上删除标记。delefrom选课wher成绩=90,第5章查询与视图5.1查询查询是一个预先定义好的sqlsele语句。获得的查询结果可以按照一定的输出类型定向输出查询结果。5.1.2查询设计器进入“查询设计器”的方法：1、菜单法：“文件”“新建”选择“查询”“新建文件”2、命令法：CREATEQUERY3、通过项目管理器创建查询,创建查询使用“查询设计器”创建查询需要经过以下几个步骤：(1)选择需要从中获取信息的表或视图(2)选择查询结果中需要的字段或字段表达式(3)若是多表查询，需要给出表之间联接的表达式(4)指定查询记录的选择条件(5)设置排序和分组的选项(6)选择查询结果的输出方式5.1.2查看SQL在“查询设计器”中单击右键，选择“查看SQL”可以显示由查询操作所产生的SQL命令，显示的命令只能阅读，不能修改。,5.1.3指定查询去向,在查询设计完成后，在“查询设计器”中单击右键，选择“输出设置”，可进行“查询去向”的选择，其中包括了7个按钮，表示查询结果不同的输出类型：(1)浏览：在浏览窗口中显示查询结果（默认）(2)临时表：将查询的结果保存于临时表中(3)表：将查询的结果作为表文件保存(4)图形：将查询的结果作为图形输出(5)屏幕：将查询的结果在当前的窗口中显示(6)报表：将查询的结果在发送到报表文件(7)标签：将查询的结果发送到标签文件,5.1.4运行、保存和修改查询1、运行查询：在“查询设计器”中单击右键，选择“运行查询”，可得到查询的结果。或者：在命令窗口中输入命令：DO(注：扩展名.qpr不能少）2、保存查询：当关闭“查询设计器”窗口或按Ctrl+W就可以保存查询。3、修改查询：MODIFYQUERY打开已有的查询文件既可对相关查询进行修改。查询菜单打开“查询设计器”后，系统菜单中会自动增加一个“查询”菜单，该菜单包含“查询设计器”下部窗格中各个选项卡包含的所有选项，也包含快捷菜单和“查询设计器”工具栏的大部分功能。,5.2视图5.2.1概念视图是数据库具有的一个特有功能，数据库打开时，视图才可使用。视图只能创建在某个数据库中。视图是创建自定义并可更新的数据集合。它是一个虚拟表，所谓虚拟，是因为视图的数据是从已有的数据库表或其他视图中抽配得来的。这些数据在数据库中并不实际存储，仅在其数据词典中存储视图的定义。但视图一经定义，就成为数据库的组成部分，可以像数据库表一样接受用户的查询。视图分为本地视图和远程视图。,5.2.2视图的创建本地视图所依赖的数据是本地表或视图一、建立视图的方法先打开要创建的数据库，“新建|视图”二、视图设计器的使用说明：1、“字段”选项卡：设置查询输出字段若查询输出的不是单个字段信息，而是由字段构成的表达式，可在“函数和表达式”中设置2、“联接”选项卡：设置多个表之间的联接条件,(1)内部联接：只返回完全满足联接条件的记录(2)左联接：返回左侧表中的所有记录和右侧表中相匹配的记录即：左表某记录与右表所有记录比较字段值，若有满足联接条件的，则