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

第4章关系数据库标准语言SQL15:05:254.1SQL概述SQL——StructuredQueryLanguage（结构查询语言）是一个功能强大的数据库语言。现在已成为关系数据库管理系统的标准语言。4.1.1SQL概念15:05:25SQL发展历程1976年诞生在IBM公司1986年出现标准，ANSI公布1987年成为国际标准，ISO公布SQL89SQL92SQL315:05:25SQL的特点1.是一种一体化语言，包含了数据定义、数据操纵、数据控制等功能2.高度的非过程化，不需要告诉计算机“怎样做”，只要说明“做什么”3.语言简洁，只有很少几条命令，接近自然语言4.可交互式用，又可嵌入程序设计语言中15:05:25SQL语言的基本命令（表4-1）SELECT：实现查询功能CREATE、DROP、ALTER：数据定义INSERT、UPDATE、DELETE：数据操作GRANT、REVOKE：数据控制15:05:254.1.2VFP对SQL的支持不同的数据库管理系统所支持的SQL不尽相同：VFP支持SQL的数据定义、数据查询、数据操作等功能，不支持数据控制功能。在VFP中不能使用命令GRANT、REVOKE15:05:254.2SQL的定义功能4.2.1表的定义与删除15:05:251.定义数据表命令格式：CREATETABLE|DBF表名[NAME长表名][FREE](字段名类型[(宽度)][NULL|NOTNULL][CHECK表达式[ERROR错误信息]][DEFAULT表达式][PRIMARYKEY|UNIQUE][,字段名]…)|FROMARRAY数组名15:05:25关键词说明：NAME：指定长表名FREE：指明建立自由表NULL：字段可否为空UNIQUE：建立候选索引PRIMARYKEY：建立主索引FROMARRAY：根据指定数组内容建立表15:05:25范例：CREATETABLE学生档案(学号c(6)PRIMARYKEY,姓名c(8)NOTNULL,年龄n(2)CHECK(年龄0)ERROR“年龄应大于0!”DEFAULT20)15:05:252.表的删除格式：DROPTABLE表名DROPVIEW视图名说明：删除数据库表时应在打开数据库后进行，该命令是直接删除表文件。删除表还可以用DELETEFILE表名15:05:254.2.2数据表结构的修改格式：ALTERTABLE表名ADD|DROP|ALTER[COLUMN]字段名类型(宽度)[NULL|NOTNULL][CHECK表达式[ERROR错误信息]][DEFAULT表达式][PRIMARYKEY|UNIQUE]功能：增加、删除或修改字段及数据完整性规则。15:05:25例1：为学生成绩表增加一物理成绩字段,并对用户输入作有效性检查。ALTERTABLE学生成绩;ADD物理n(6,2)SETCHECK物理0例2：对上例删除物理字段的有效性检查。ALTERTABLE学生成绩;ALTER物理DROPCHECK15:05:25例3：对上例，删除物理字段。ALTERTABLE学生成绩;DROPCOLUMN物理例4：删除学生成绩表中的主索引。ALTERTABLE学生成绩;DROPPRIMARYKEY15:05:254.3SQL的操作功能4.3.1数据记录的插入命令格式：INSERTINTO表名[(字段名表)]VALUES(表达式表)指明操作的表指明插入的字段指明插入对应字段的值15:05:25范例：向学生档案表中加入记录：（020179,王蕾,女,工商会计,85）INSERTINTO学生成绩(学号,姓名,性别,专业,综合考评);VALUES(“020179”,“王蕾”,“女”,“工商会计”,“85”)15:05:25VFP中插入命令的特殊格式：INSERTINTO表名;FROMARRAY数组名；|FROMMEMVAR说明：从指定数组或内存变量中向指定数据表插入数据记录。15:05:254.3.2数据更新命令格式：UPDATE表名;SET字段名=表达式[字段名2=表达式2];WHERE条件表达式功能：修改指定表中满足条件的记录的某些字段值。15:05:25范例：将学生档案表中所有女生的综合考评加1分。UPDATE学生档案;SET综合考评=综合考评+1;WHERE性别=女15:05:25命令格式：DELETEFROM表名[WHERE条件]功能：删除指定表中满足条件的记录。如不加限定条件则删除表中所有记录。4.3.3数据删除15:05:25范例：删除学生成绩表中2000130101号学生的记录。DELETEFROM学生成绩WHERE学号=“2000130101”注意：在VFP中本命令只是逻辑删除记录，要物理删除记录还要使用其它命令。15:05:254.4SQL的数据查询功能查询语句的一般格式：SELECT[ALL|DISTINCT]field1[,field2,...]FROMtable1[,table2,...][WHEREconditions][GROUPBYfield][HAVINGconditions][ORDERBYfield[ASC|DESC]]15:05:25٭各子句的作用说明：FROM是唯一必须的子句，指定查询来源；WHERE可选子句限定查询条件；GROUPBY子句对查询结果进行分组；HAVING子句只能用于GROUPBY之后，为每个分组指定条件；ORDERBY指定查询结果的显示顺序。15:05:254.4.1简单查询1.从表中查询全部记录SELECT*FROM学生档案说明：*代表所有字段“学生档案”指明查询结果来自的数据表15:05:25查询结果的过滤例：SELECTDISTINCT生源地FROM学生档案DISTINCT表示把查询结果中某一字段重复的记录过滤掉，相同的只留一条记录。15:05:252.查询满足条件的指定输出字段SELECT字段名表FROM表名WHERE条件表达式功能：从指定表中查询满足条件的记录，显示出指定字段15:05:25٭条件表达式的说明：表达式可以使用以下关系（比较）运算符：=、、、=、=、、LIKE、IN、BETWEEN逻辑运算符：NOTANDOR15:05:25例1：=与AND的使用SELECT姓名，性别，专业;FROM学生档案;WHERE出生日期={^1980-02-01};AND性别=“女”注意：不同数据类型数据实例的写法。日期型：{^1980-02-01}15:05:251.IN的使用例如：SELSCT*FROM学生档案;WHERE专业IN(‘市场营销’,’工商会计’)专业=‘市场营销’OR专业=’工商会计’NOT专业=‘信息管理’在只有三个专业的表中查询，以上条件等价。4.4.2查询中的特殊运算符15:05:25例：SELECT*FROM学生档案;WHERE姓名LIKE‘王_’OR；姓名LIKE‘周%’说明：_替代一个字符，%替代多个字符。2.LIKE的使用15:05:25例：SELECT*FROM学生档案;WHERE综合考评BETWEEN70AND90综合考评=70AND综合考评=90以上两个条件等价3.BETWEEN的使用15:05:254.4.3连接查询将两个或两个以上的表连接起来，从中进行查询，生成查询结果。例：SELECT学生档案.学号,姓名,英语;FROM学生档案,学生成绩;WHERE学生成绩.学号=学生档案.学号15:05:251.两表相同字段名前应加表名前缀；2.使用连接条件构造连接；3.使用关系运算符；4.为表指定别名：例；连接查询的说明15:05:25例2：SELECTD.学号,姓名,英语;FROM学生档案D,学生成绩C;WHERED.学号=C.学号15:05:25为使查询结果更加明确，可以为每个字段加上别名。例3：SELECTNO学号，NAME姓名；FROMXSDA٭关于为字段选择别名15:05:254.4.4嵌套查询在一个查询中可以嵌套一个或多个查询，从而完成复杂的查询任务。被嵌套的内层查询我们习惯上称之为子查询。子查询一般作为外层查询的查询条件。15:05:25例1（10-11）：在学生档案表中查询与“张文”在一个专业的学生。SELECT姓名,性别,专业；FROM学生档案WHERE;专业=（SELECT专业FROM学生档案；WHERE姓名=‘张文’）15:05:25例2（10-13）：查询与张文同专业的学生的成绩。SELECTD.学号,姓名,专业,高数,英语；FROM学生档案D，学生成绩C；WHERE专业=（SELECT专业；FROM学生档案WHERE姓名=‘张文’）；ANDD.学号=C.学号15:05:25例：SELECT*FROM学生档案ORDERBY学号DESCASC——升序、DESC——降序ORDERBY指定查询结果的显示顺序。4.4.5查询排序15:05:254.4.6分组汇总查询使用GROUPBY子句进行查询分组。在分组的基础上使用函数或表达式可以完成一些统计汇总工作。15:05:25AVG()、SUM()、ROUND(N,d)、DTOC()、CTOD()、LOWER()、UPPER()、MAX()、MIN()、COUNT()常用统计函数15:05:25应用示例例：分别求出学生成绩表中高数、英语、计算机三科成绩的平均分。SELECTAVG(高数),AVG(英语),AVG(计算机)FROM学生成绩15:05:25例：分别求出学生成绩表中高数、英语、计算机三科的总分。SELECTSUM(高数)高数总分,；SUM(英语)英语总分,SUM(计算机)计;算机总分FROM学生成绩15:05:25例：查询出学生档案表中学生所属专业的个数。SELECTCOUNT(DISTINCT专业);FROM学生档案例：查询出学生成绩表中高数最高分。SELECTMAX(高数)FROM学生成绩15:05:25HAVING短语SQL通过GROUPBY子句实现分组，通过HAVING短语对分组后的查询结果进行限定。15:05:25例：在学生档案、学生成绩两表中专业分别求男女生的人数、高数平均分，并只列出高数平均分高于80分的专业。SELECT专业,性别,COUNT(*)人数,;AVG(高数)高数平均分;FROM学生档案D,学生成绩C;WHERED.学号=C.学号;GROUPBY专业，性别;HAVINGAVG(高数)=8015:05:254.4.7空值查询利用NULL值进行查询操作，使用IS作判断动词。例：查询高数缺考的学生。SELECT学号FROM学生成绩;WHERE高数ISNULL15:05:254.4.8自连接查询将同一个表作为两个表关联起来查询。利用表的别名指定同一个表为两个表。例5.42：查询至少选修了课程号为C01和C02的学生的学号。15:05:25SELECTa.学号FROM选课登记a,选课登记bWHEREa.学号=b.学号;ANDa.课程号=C01;ANDb.课程号=C0215:05:254.4.9内外层互相关嵌套查询外层查询条件使用内层查询结果值，内层查询条件需要外层查询提供值，这种查询成为内外层互相关嵌套查询。15:05:25SELECT*FROM选课登记AWHERE成绩=(SELECTMAX(成绩)FROM选课登记BWHEREB.学号=A.学号)例：查询每位学生所选修的课程中成绩最高的课程成绩。15:05:254.4.10使用量词和谓词的查询在含有子查询的查询语句中，常要用到一些特殊的运算符：1、查询中的量词使用格式：表达式比较运算符[ANY|ALL|SOME]（子查询）量词运算符15:05:25ANY、SOME是同义词，在作比较运算时只要子查询中有一行能使结果为真，则结果就为真；ALL要求子查询中的所有行都使结果为真时，结果才为真。15:05:25例1：有两个表分别存放1、2班的学生档案信息，查询1班里比2班所有学生出生都早的学生的信息。SELECT*FROMXSDA1;WHERE出生年月ALL(SELECT;出生年