1、2018/10/11,1,SQL语言概述 SQL数据定义功能 （不考） SQL数据查询功能（重点） SQL数据修改功能（不考）,第五章 目 录,2018/10/11,2,5.1 SQL语言概述,SQL是Structured Query Language（结构化查询语言）的缩写，是一种用于关系数据库操作的标准语言，包括了对关系数据库的查询、操纵、定义、控制和管理等功能，是一个综合的、通用的、功能极强的关系数据库语言。,SQL语言按照功能可以分为三大类： 数据定义语言DDL：建立（Create）、删除（Drop）和修改（Alter）数据对象。 数据操纵语言DML：完成数据操作的命令，包括查询(Se
2、lect)、插入(Insert)、更新(Update)和删除(Delete)。 数据控制语言DCL：控制对数据库的访问，服务器的关闭、启动等(Grant、Revoke)。,2018/10/11,3,SQL语言具有以下特点： SQL是一种一体化的语言。 SQL是一种高度非过程化的语言。 SQL语言非常简洁，只有为数不多的几条命令。 SQL是关系数据库的通用语言。 SQL命令有交互式和嵌入式两种使用方式。,2018/10/11,4,5.2 SQL数据定义功能（不考）,5.2.1 创建数据库和表 1数据库定义 格式：Create Database 功能：创建一个数据库。,定义功能包括：数据库、数据表
3、、视图、存储过程、索引等对象。,2018/10/11,5,2表的定义 命令格式： Create Table | DBF TableName1 Name LongTableName Free (FieldName1 FieldType (nFieldWidth , nPrecision ) Null | Not Null Check LExpression1 Error cMessageText1 Default eExpression1 Primary Key | Unique References TableName2 Tag TagName1 , FieldName2 . , Primar
4、y Key eExpression2 Tag TagName2|, Unique eExpression3 Tag TagName3 , Foreign Key eExpression4 Tag TagName4 Nodup References TableName3 Tag TagName5 , Check lExpression2 Error cMessageText2) | From Array ArrayName,2018/10/11,6,说明： (1) 当前有打开的数据库时，表建立在此数据库中。 (2) 命令关键字Create Table 与Create DBF功能相同。TableN
5、ame1则为所创建表的名字。 (3) Name LongTableName ：为所创建的表定义长表名。只有当前有打开的数据库时，才能使用此选项。 (4) Free：创建表为自由表。 (5) FieldName1 FieldType (nFieldWidth , nPrecision：4个选项依次分别定义字段名、字段类型，数值型和字符型字段的宽度，数值型字段的小数位宽度。 (6) Null | Not Null：定义本字段是否可以为空。,2018/10/11,7,(7) Check lExpression1 Error cMessageText1：定义本字段的有效性规则和出错时的提示信息字符串。
6、 (8) Default eExpression1：定义本字段的默认值。 (9) Primary Key：将本字段定义为主关键字（主索引）字段。 (10) Unique：将本字段定义为侯选关键字（侯选索引）字段。 (11) References TableName2 Tag TagName1：说明本表与之建立永久联系的父表及父表的索引名。 (12) Foreign Key：定义一个外部（索引）关键字（非主索引），并与一个父表建立关联。 (13) From Array ArrayName：由一个数组创建表，数组中存放了有关表的每个字段的名称、类型、宽度、小数宽度等信息。,2018/10/11,8
7、,【例5.1】 表结构为：student(sno , sname , sex , birthday , dno)，用Create Table命令创建此表。命令如下： Create Table student ( sno C (6) , sname C(6) , sex C(2) , birthday D , dno C(3) 如果要在创建表的同时定义主索引、有效性规则等，命令可以写成下面的形式： Create Table student ( sno C (6) Primary Key Not Null , sname C(6) , sex C(2) ; Check sex =“男“ Or se
8、x=“女“ Error “错误“ , birthday D , dno C(3) ),2018/10/11,9,5.2.2 表的修改 表的修改格式1： Alter Table TableName1 Add | Alter Column FieldName1 FieldType (nFieldWidth , nPrecision) Null | Not Null Check lExpression1 Error cMessageText1 Default eExpression1 Primary Key | Unique References TableName2 Tag TagName1,20
9、18/10/11,10,说明： 这种格式用来添加字段或修改字段的参数，Add表示增加字段，Alter表示修改字段的参数，其它各选项含义与Create Table命令相同。该格式可以修改字段的类型、宽度、有效性规则等，但不能修改字段名，不能删除字段及已经定义的字段有效性规则。 在这种格式中，不管是否修改字段类型，都必须将字段名和字段类型一同在命令中列出。,2018/10/11,11,【例5.2】在例5.1创建的表student中加入一个新字段grade N(3)。 Alter Table student Add grade n(3) Check grade=0 And grade=100【例5.
10、3】将例5.1中创建的表student中的字段sname宽度改为10。 Alter Table student Alter sname C(10),2018/10/11,12,表的修改格式2： Alter Table TableName1 Alter Column FieldName2 Null | Not Null Set Default eExpression2 Set Check lExpression2 Error cMessageText2 Drop Default Drop Check 说明： 这种格式主要用于定义、修改和删除字段的有效性规则和默认值定义。,2018/10/11,1
11、3,【例5.4】将表Student中的字段sex 宽度改为1，并修改其有效性规则。 Alter Table student ; Alter sex c(1) Set Check sex=“F“ Or sex= “M “【例5.5】删除表Student中的字段sex的有效性规则 Alter Table student Alter sex Drop Check,2018/10/11,14,表的修改格式3： Alter Table TableName1 Drop Column FieldName3 Set Check lExpression3 Error cMessageText3 Drop Che
12、ck Add Primary Key eExpression3 Tag TagName2 For lExpression4 Drop Primary Key Add Unique eExpression4 Tag TagName3 For lExpression5 Drop Unique Tag TagName4 Add Foreign Key eExpression5 Tag TagName4 For lExpression6 References TableName2 Tag TagName5 Drop Foreign Key Tag TagName6 Save Rename Column
13、 FieldName4 To FieldName5,2018/10/11,15,说明： 这种格式可以删除字段（Drop Column）， 可以为字段改名（Rename Column FieldName4 To FieldName5），还可以增加或删除表一级的有效性规则、主索引等。【例5.6】删除表Student中的字段grade 。 Alter Table student Drop grade 【例5.7】将表Student中的字段dno改名为dn 。 Alter Table student Rename Column dno To dn,2018/10/11,16,5.2.3 表的删除 删除
14、表的命令格式： Drop Table Table_name 说明：Drop Table命令直接从磁盘删除表。如果要删除的表是数据库表，应当在数据库打开的情况下删除该表. 【例5.8】将表Student删除Drop Table Student,2018/10/11,17,5.3 SQL数据查询功能（重点）,Select命令是SQL的数据查询命令，是SQL语言的核心内容。 Select命令由多个子句构成，某些子句是必需的，而另外一些子句是可选的，Select命令的执行结果是生成一个新的关系。,2018/10/11,18,Select命令的基本结构： Select All | Distinct To
15、p nExpr Percent Alias. Select_Item As Column_Name , Alias. Select_Item As Column_Name . From DatabaseName! Table As Local_Alias Inner | Left Outer | Right Outer | Full Outer Join DatabaseName! Table As Local_Alias On JoinCondition Where JoinCondition And JoinCondition . And | Or FilterCondition And
16、| Or FilterCondition . Group By GroupColumn , GroupColumn . Having FilterCondition Order By Order_Item Asc | Desc , Order_Item Asc | Desc . Into Destination | To File FileName Additive | To Printer Prompt | To Screen Union All SelectCommand,2018/10/11,19,说明： Select子句说明要查询的数据项（Select_Item），其中可以包含字段名、
17、统计函数、表达式、常量，如果这一部分用“*”来代替，则表示输出表中的所有列（字段）。Select子句中的保留字: All表示显示查询结果的所有记录行； Distinct表示去掉查询结果中重复的记录行； ToP nExpr 表示只保留查询结果中排列在前面的、由数值表达式nExpr确定数量的行。 From子句是必需与Select子句联合使用的子句，不可缺少。它包含一个或多个表名，这些表是查询数据的来源。 Where子句通过设置条件来选择用户所需要的记录行。如果条件比较复杂，可以用一些操作符来描述条件。,2018/10/11,20, Group By子句用来对查询结果进行分组，通过分组可以完成较为复
18、杂的分组统计运算。 Having子句用来完成对分组的选择，即只在查询结果中保留符合条件的分组。 Order By子句可以设置查询结果中记录的排列顺序 Into子句是Visual FoxPro的SQLSelect命令特有的选项，用来确定查询结果的输出去向，例如输出去向可以是数组、数据表、临时表、打印机等。默认输出到Browse窗口。 Union子句功能是将两个Select命令的执行结果进行并运算，得到一个新的关系。,2018/10/11,21,SQL命令书写说明： 命令涉及两个以上表的数据时，这些表中的共有字段名前面必须冠以用“.”分隔的表名作为前缀，例如jsqk.jsbh。 命令需要分成几行时
19、，除最末行外，其它行末尾加分号。,注意: SQL命令中所涉及的表无需事先打开； 结果生成一个查询，以浏览窗口的形式显示； “数据工作期”列表中显示当前查询。,2018/10/11,22,5.3.1 基本查询最简单的查询命令，只包含Select、From子句，且数据只来自一个表。【例5.9】查询教师管理数据库中xb表的所有数据。 命令：Select * From xb 【例5.10】查询jsqk表中jsbh、xm、zc、jbgz字段的值。 命令： Select jsbh, xm, zc, xw, “在职“ From jsqk,添加显示字段,2018/10/11,23,5.3.2 条件查询 【例5
20、.11】查询jsqk表中zc字段为“教授”或“副教授”的教师的jsbh、xm、zc、gzrq字段的值。 命令： Select jsbh, xm , zc , gzrq From jsqk ； Where “教授” $ zc 【例5.12】查询jsqk表中zc字段为“教授”且性别为“女”的教师的jsbh、xm、zc、xw字段的值。 命令： Select jsbh, xm , zc , xw From jsqk ； Where zc= “教授“ And xb=”女”,2018/10/11,24,5.3.3 连接查询连接是关系的基本操作之一，连接查询是一种基于多个关系的查询，查询的数据和条件涉及两个
21、或两个以上的表。 【例5.13】在教师管理数据库中查询基础工资（jcgz）多于2000元的教师的xm、zc、jcgz和zwbt。 方法1： Select xm , zc , jcgz , zwbt From jsqk , gz ； Where jsqk.jsbh=gz.jsbh and jcgz=2000 方法2： Select xm , zc , jcgz , zwbt From jsqk Join gz；On jsqk.jsbh=gz.jsbh Where jcgz=2000,2018/10/11,25,多个表的连接查询 【例5.15】查询所有科研成果的作者姓名、系部名称、职称、成果名称。
22、 方法1： Select xm , zc , xbmc , cgmc From jsqk ； Join xb On jsqk.xbbh=xb.xbbh ; Join kyqk On jsqk.jsbh=kyqk.jsbh 方法2： Select xm , zc , xbmc , cgmc From jsqk , xb , kyqk ; Where jsqk.xbbh=xb.xbbh and ;jsqk.jsbh=kyqk.jsbh,2018/10/11,26,超连接查询 (1) 左连接 将满足连接条件的所有元组放在结果关系中，同时将第一个表（或称Join左边的表）中不满足连接条件的元组也放入结
23、果关系中，这些元组对应第二个表的属性值为空值。 (2) 右连接 将满足连接条件的所有元组放在结果关系中，同时将第二个表（或称Join右边的表）中不满足连接条件的元组也放入结果关系中，这些元组对应第一个表的属性值为空值。 (3) 全连接 将满足连接条件的所有元组放在结果关系中，同时将两个表中不满足连接条件的元组也放入结果关系中，这些元组对应另一个表的属性值为空值。,2018/10/11,27,超连接查询的SQL Select命令的格式如下： Select From Table Inner | Left | Right | Full Outer Join Table On Where 【例5.16
24、】查询所有人的科研成果。 Select xm , zc , cgmc From jsqk Left Join kyqk On jsqk.jsbh=kyqk.jsbh,2018/10/11,28,自连接查询 SQL不仅可以对多个关系进行连接操作，也可以将一个关系与其自身进行连接，这种连接称为自连接。在进行自连接查询时，一般要为表定义别名，所谓别名就是在From子句中，为表定义一个另外的名字，格式如下：【例5.17】 查询与姓名为“许斌”的教师同一系部的人。 Select a.xm From jsqk a , jsqk b Where ； b.xm=“许斌“ And a.xbbh=b.xbbh,2
25、018/10/11,29,5.3.4 嵌套查询当一个查询的Where条件子句中，需要用到另一个查询的结果时，后一个查询就是嵌套查询，或称为子查询。 嵌套查询中使用的运算符（一般用于子查询返回多个值时） In：测试是否在集合中。 Any和Some：与In功能近似，检测是否是集合中的某一个值。在Visual FoxPro中，Any、Some及All一般仅用于子查询得到的集合。 All：子查询结果关系中的所有行都使运算为真时，结果才为真。 Union：将两个查询的结果关系进行集合并运算。 Exists：判断子查询有无结果返回。,2018/10/11,30,【例5.18】查询有学位为博士的教师的系部名
26、称。 Select xbmc 系部名称 From xb ;Where xbbh=Any(Select xbbh From jsqk Where xw=“博士“) 嵌套查询中必须遵循的规则： 嵌套查询必须用括号括起来。 嵌套查询一般在Select子句中只能有一个列。 返回多行的嵌套查询可以和多值操作符一起使用，例如In操作符。 Between操作符不能和嵌套查询一起使用，却可以用于嵌套查询的条件中。 在Visual FoxPro中，嵌套查询层数不能超过2层。,2018/10/11,31,【例5.19】 查询没有职称为“教授”的教师的系部名称。 分析：先找出有教授的系部集合，不在此集合内的即是没有
27、职称为“教授”的教师的系部。 Select xbmc From xb Where xbbh !=All； (Select xbbh From jsqk Where zc=“教授“) 上述命令等价于： Select xbmc From xb Where xbbh Not In； (Select xbbh From jsqk Where zc=“教授“),再如：查询没有博士或硕士学位的教师姓名 Select xm from jsqk where xw not in (“硕士“,“博士“),2018/10/11,32,【例5.20】查询职称为“教授”或者有科研成果的教师姓名。 Select xm F
28、rom jsqk Where zc =“教授“ ; Union Select xm From jsqk Where jsbh In ；(Select jsbh From kyqk)【例5.21】查询有科研成果的教师姓名（有多项成果时，仅列出一次）。 Select xm From jsqk Where Exists ； (Select jsbh From kyqk where jsbh=jsqk.jsbh ),2018/10/11,33,SQL命令中的各类运算符 比较运算符：，!，=， 逻辑运算符： AND，OR，Not 数学运算符：，*，/ 谓词：In，Any (Some)，All，Union
29、，Exists，Between And ，Like，Is Null，Is Not Null 。 说明：Is Not Null 测试字段值是否为Null（空），Null可以与任何类型的数据匹配。Between And 判断字段值是否在指定的区间内。Like 判断字段值是否与某个模式匹配。可以使用通配符“%”和“_”（下划线），“%”代表多个字符，“_”代表一个字符。,2018/10/11,34,【例5.22】查询学历字段为空值的教师姓名。 Select xm From jsqk Where xl Is Null【例5.23】查询出生日期在70年代的教师姓名。 Select xm From jsq
30、k Where csrq Between ；1970/01/01 And 1979/12/31 【例5.24】查询学历为大学或大专的教师姓名。 Select xm From jsqk where xl Like “大% “可用 xl in(“大学“ ， “大专“),2018/10/11,35,5.3.5 数据统计查询 1. 统计函数 Count函数 格式：Count (*| Distinct ) 功能：统计行数或指定字段不为Null的数量。在查询中使用Count函数将会返回一个数值。 说明： 当Count函数使用Distinct 选项时，只有那些指定字段不重复的行才被计算在内，省略Distin
31、ct时，重复的行也被计算在内。 Count函数中使用星号（*）选项时，表示统计表中记录的总行数。 Distinct不能用于Count(*)，只能用于Count(字段名)。 【例5.25】查询计算机系的教师人数。 Select Count(*) 人数 From jsqk Where xbbh= ; (Select xbbh From xb Where xbmc=“计算机系“),2018/10/11,36, Sum函数 格式：Sum( Distinct ) 功能：返回一组数据行中指定字段的值的总和。 说明： Sum函数可以使用Distinct可选项,这时只有指定字段数据不重复的行才被计算在总和内，
32、由于一些数据行被忽略，总和可能不正确。 使用Sum函数时，指定字段的值必须是数值类型。Sum函数不能用于数值类型以外的数据类型，比如字符类型或日期类型。 【例5.27】查询计算机系的教师的基础工资总和。 Select Sum(jcgz) From jsqk, gz Where jsqk.jsbh=gz.jsbh ；And xbbh= (Select xbbh From xb Where xbmc=“计算机系“),2018/10/11,37, Avg函数 格式：Avg ( Distinct ) 功能：Avg函数用于返回一组数据行中指定字段的平均值。 说明： Avg函数使用Distinct选项时，
33、只有指定字段数据不重复的行才被计算在平均值内。 使用Avg函数时，指定字段必须是数值类型。 Min和Max函数 格式： Min（）Max（） 功能：Min函数用于返回一组数据行中指定字段的最小值,而Max函数用于返回一组数据行中指定字段的最大值。 说明：使用Min和Max函数时，系统会忽略那些Null值,2018/10/11,38,【例5.28】查询全部教师中的基础工资最高、最低值。 Select Min(jcgz) 最低工资，Max (jcgz) 最高工资 From gz【例5.29】查询全部教师中的基础工资最高、最低值及其姓名、职称 Select xm, zc, jcgz From jsq
34、k, gz; Where jsqk.jsbh=gz.jsbh And ; (jcgz =(Select Max(jcgz) From gz) ; Or jcgz =(Select Min(jcgz) From gz),2018/10/11,39,2分组与计算查询 格式：Group By , Having 说明： 当Where子句、Group By子句、Having子句同时出现时，执行顺序为Where子句、Group By子句、Having子句。 Having子句只能与Group By子句连用，不能单独使用,且只能放在Group By子句之后。,【例5.30】查询每个系部中教师基础工资的最大、最
35、小值，并显示系部编号。 Select xbbh 系别编号, Min(jcgz) 最低工资, Max (jcgz) 最高工资 From jsqk, gz ; Where jsqk.jsbh=gz.jsbh Group By xbbh,2018/10/11,40,【例5.32】查询教师的人数超过3人的系部，并显示系部编号。 Select xbbh 系部, Count(*) 人数 From jsqk ; Group By xbbh Having Count(*)=3 【例5.33】查询每个系部中男、女教师的人数，并显示系部编号。 Select xbbh, xb, Count(*) From jsqk
36、 ; Group By xbbh，xb (先按xbbh分组，再按xb分类),【例5.31】查询每个系部中教师的人数，并显示系部编号。 Select xbbh , Count(*) From jsqk ； Group By xbbh,2018/10/11,41,5.3.6 查询结果排序 格式：Order By Asc | Desc , Asc | Desc 说明： 在Order By子句中选择Asc或省略时，按指定字段的升序排序，选Desc时按降序排序，字段名1是排序的主关键字，字段名2是第二关键字之后是第三关键字、第四 。 与排序有关的另一短语是Top nExpr Percent，可以用来显示
37、查询结果的前面几项，加参数Percent时按百分比显示排序在前的项。 Order By是对最终的查询结果进行排序，不能在子查询中使用该子句。 排序是查询结果输出前的最后一步，因此Order By子句一般放在Select命令的末尾。,2018/10/11,42,【例5.34】按照出生日期升序显示jsqk表中的姓名、职称、出生日期。 Select xm , zc , csrq From jsqk Order By csrq 【例5.35】先按系部编号升序，再按gzrq降序显示jsqk表中的姓名、系部编号、职称、工作日期。 Select xm, xbbh, zc, gzrq From jsqk ;O
38、rder By xbbh , gzrq Desc 【例5.37】 显示gz表中jcgz字段最高的前15%的教师姓名和基础工资。 Select Top 15 Percent xm , jcgz From jsqk , gz ;Where gz.jsbh=jsqk.jsbh Order By jcgz Desc,2018/10/11,43,5.3.7 查询结果处理在Visual FoxPro中，SQL_Select命令可以设置查询结果的保存方式，一般情况下，省略查询结果去向说明时，查询结果显示在一个浏览(Browse)窗口中，此外，还可以将结果以下列方式保存： Into Array 这种方式将查询
39、结果保存在一个数组中，一般是二维数组，数组的每一行存放一条记录。如果查询结果只有一个值，也要以数组方式处理，这个值存放在数组的第一个元素中。 Into Cursor 将查询结果保存在一个只读的临时表中，且这个临时表自动成为当前打开的表。临时表的使用方式与数据库表和自由表相同。当该临时表关闭时，自动被删除，不能再次使用。,2018/10/11,44, Into Table 将查询结果保存在一个永久表中。 To File 将查询结果保存在一个文本文件中。 To Printer Prompt将查询结果送往打印机打印。有Prompt选项时，打印之前首先弹出设置对话框。 【例5.38】 查询工资表的数据
40、，分别存放到数组和临时表中。 Select * From gz Into Array x Select * From gz Into Cursor lsb,2018/10/11,45,5.3.8 视图语句（不考） 1创建视图 格式：Create View As 说明：上述命令中的Select查询命令可以是任意的，它定义了视图中的数据，视图中的字段名将与Select 命令中指定的字段名相同。【例5.39】 创建视图，包括jsqk、gz表中的gz.jsbh， xm，zc，jcgz,，zwbt字段。 Create View jsgz As Select gz.jsbh , xm , zc , jcg
41、z , zwbt From jsqk , gz Where gz.jsbh=jsqk.jsbh,2018/10/11,46,2删除视图 对于不再使用的视图，可以使用SQL语言中的删除视图命令来实现。 格式：Drop View 说明：上述语句的功能是删除数据库中的视图。,2018/10/11,47,5.4 SQL数据修改功能（不考）,5.4.1 插入数据 Visual FoxPro支持两种SQL插入命令的格式，第一种格式是标准格式，第二种格式是Visual FoxPro的特有格式。格式1：Insert Into (字段名1 , 字段名 2，； 字段名n ) Values(值1 , 值2 , ,
42、值n )格式2：Insert Into From Array ,2018/10/11,48,【例5.40】向xb表插入一条新记录，为所有字段赋值。 Insert Into xb Values(“109“,“法律系 “) 说明：由于xbbh是表xb的主关键字，插入的xbbh字段的值不能与原有的值重复。 【例5.41】 向jsqk表插入一条新记录，只为字段jsbh, xm, xb, zc赋值。 Insert Into jsqk (jsbh, xm, xb, zc, csrq) ; Values(“199009“ ,“和悦“ ,“女“ ,“助教“, 1982-01-08) 【例5.42】复制与插入命
43、令应用举例。 Select * From jsqk Where zc=“教授” Into Table zg Select * From jsqk Where zc != “教授” Into Array aa Insert Into zg From Array aa,2018/10/11,49,5.4.2 更新数据对表中已经存在的数据可以使用Update命令来更新。Update命令不会向表中增添新的记录，也不删除记录，它仅仅更新已经存在的数据。在数据库中，Update通常在某一时刻只能更新一个表，但可以同时更新一个表中的多个列。在一条命令中，可以根据需要更新表中的一行数据，也可以更新多行数据。格
44、式：Update Set ， Where 注意：使用Update命令时，如果没有Where子句，将更新表中所有的数据行。,2018/10/11,50,【例5.43】 在gz表中，为具有教授职称的教师增加的100元职务补贴（字段名zwbt）。 命令： Update gz Set zwbt=zwbt+100 Where jsbh In ；( Select jsbh From jsqk Where zc=“教授“),2018/10/11,51,5.4.3 删除数据 Delete命令用于从表中删除整行的数据，Delete命令在FoxPro 中是一种逻辑删除，要物理删除还要使用命令Pack。 格式： Delete From Where 【例5.44】 删除xb表中系部名称为Null的记录。 Delete From xb Where xbmc Is Null,
