结构化查询语言PPT课件

VisualFoxPro程序设计,刘鹏山西农业大学信息学院,.,2,本节内容,第七章结构化查询语言7.1SQL语言简介7.2数据定义7.3数据操作7.4数据查询7.5数据控制,.,3,引言,结构化查询语言(StructuredQueryLanguage)简称SQL。结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。,.,4,7.1SQL语言简介,SQL的全称是“结构化查询语言，最早是IBM的圣约瑟研究实验室为其关系型数据库管理系统SYSTEMR开发的一种查询语言，它的前身是SQUARE语言。1、SQL语言的组成数据定义数据操作数据控制嵌入式SQL使用,.,5,2、SQL语言具有许多的优点，概括地讲主要有以下几点：（1）转化特点（2）高度的非过程化（3）语法简单，易于掌握（4）统一的语法结构对待不同的工作方式（5）视图数据结构,.,6,7.2数据定义,SQL的数据定义功能很多，包括数据库的定义、表的定义、视图的定义、存储过程的定义、规则的定义以及索引的定义等，其中最主要、用途最广的是表的定义。本节详细讲述表的定义。,.,7,7.2.1创建表结构（1）格式：CreateTable|CursorNameFree()Null|NotNull)CheckErrorDefaultPrimaryKey|UniqueReferencesTagNocptrans（2）功能：创建一个表结构并定义表的字段名、字段类型、宽度以及小数位数等等。,.,8,（3）参数说明：CreateTable表示创建数据表，CreateCursor表示创建临时表。表示新建表文件的名称，Name表示为新建表指定一个长表名。Free参数表示建立的表是自由表。命令中（）Null|NotNull）指定新建数据表中的字段以及字段类型，宽度、小数位以及是否可以为空值等。Check表示指定该字段为合法值以及该字段值的约束条件。当输入数据与约束条件不符合的时候，弹出显示Error的指定内容。Default表示设置该字段的默认字段值。PrimaryKey子句表示为该字段创建一个主索引，索引标识名与字段名相同，主索引字段值必须唯一。ReferencesTag子句指定建立持久关系的父表，同时以该字段为索引关键字建立外索引，用该字段名作为索引标识名。Nocptrans：只对于字符型和备注型字段定义该子句，当该表转换为其他代码页时，Nocptrans子句禁止该字段转换。,.,9,【例1】创建一个学生表。CREATETABLEStudent;(SnoCHAR(9)PRIMARYKEY,;SnameCHAR(20)UNIQUE,SsexCHAR(2),;SageINT,SdeptCHAR(20),.,10,7.2.2修改表结构1.命令格式一（1）格式：AlterTableAdd|AlterColumn(长度,小数位数)Null|NotNullCheckErrorDefaultPrimaryKey|UniquePeferencesTagNocptrans,.,11,（2）功能：修改表的结构，为指定的表添加字段或者修改字段信息。（3）参数说明：表名1用于指定被修改表的表名。AddColumn（长度,小数位数）表示新增的字段的字段名、类型、长度及小数位数等。AlterColumn（长度,小数位数）表示要修改的字段的字段名、类型、长度及小数位数等。在Add子句中使用Check、PrimaryKey、Unique中的任一选项时需要删除所有数据，否则违反有效性规则，命令不被执行。在Alter子句中使用Check中的任一选项，要求被修改的字段已有的数据满足Check规则；使用PrimaryKey、Unique的任一选项时。要求被修改的字段已有的数据满足惟一性，不能有重复值。,.,12,【例2】向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDS_entranceDATE,.,13,2.命令格式二（1）格式：AlterTableAlterColumnNull|NotNullSetDefaultSetCheckError字符型文本信息2DropDefaultDropCheck,.,14,（2）功能：修改表的结构，设置或者更改表中指定字段的默认值（Default）及约束条件（Check）等，该命令只能用于数据库表。（3）参数说明：SetDefault表示为字段设置默认值。SetCheckError字符型文本信息2表示为字段设置约束条件。DropDefault表示删除字段的默认值设置。DropCheck,.,15,【例3】将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。ALTERTABLEStudent;ALTERCOLUMNSageINT,.,16,3.命令格式三（1）格式：AlterTableDropColumnSetCheckError字符型文本信息DropCheckAddPrimaryTagDropPrimaryKeyAddUniqueTagDropUniqueTagRenameColumnToNovalidate（2）功能：修改表的结构，包括更改、删除字段、重新设置约束条件、添加或者删除主索引等。值得注意的是，该命令对自由表只能进行删除、更改字段操作；对数据库表则可以进行上述所有操作。,.,17,（3）参数说明：DropColumn表示从指定的表中删除指定的字段。SetCheckError字符型本文信息为该表设置有效规则及发生错误时的提示信息。DropCheck表示删除该字段的合法值检查。AddPrimaryTag为该表建立主索引，一个表只能有一个主索引。DropPrimaryKey表示删除该表的主索引。AddUniqueTag表示为该表建立候选索引，一个表可以有多个候选索引。DropUniqueTag表示删除改表的候选索引。RenameColumnTo表示修改指定字段的字段名。Novalidate表示修改该表时，允许违反该表的数据完整性规则。,.,18,【例4】增加课程名称必须取唯一值的约束条件。ALTERTABLECourseADDUNIQUE(Cname)7.2.3删除表（1）格式：DropTable（2）功能：删除指定的数据表文件。,.,19,7.3数据操作,VisualFoxPro中的数据操作，是指对已有的表进行添加记录、更新记录和删除记录，SQL语言提供了以下三条更新语句：INSERT语句，用于插入记录；UPDATE语句，用于更新记录；DELETE语句，用于删除记录。,.,20,7.3.1插入数据插入数据指令INSERT具有如下两种格式：1.语法一（1）格式：InsertInto(字段名1,字段名2)Values(表达式1,表达式2)（2）功能：在指定的表尾添加一条新的记录，其值为Values后面的表达式的值。（3）参数说明：InsertInto：表示向指定的表中插入记录，当插入的不是完整的记录时，可用（字段名1,字段名2）指定字段。Values（表达式1,表达式2）：为指定的表字段赋予对应的值。,.,21,【例5】将一个新学生元组（学号：2002151；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage);VALUES(2002151,陈冬,男,IS,18)以上是按照列出的属性顺序(Sno,Sname,Ssex,Sdept,Sage)进行插入的。如果Student表本身的属性顺序就是如此，则不需要在Student后显示属性，直接可以写为：INSERTINTOStudentVALUES(2002151,陈冬,男,IS,18);,.,22,2.语法二（1）格式：InsertIntoFromArray|FromMemvar|（2）功能：在指定的表尾部追加一条记录，这条记录来自一个数组或者来自一个内存变量。（3）参数说明：FromArray：表示将已存在的数组内容赋给字段。FromMemvar：表示把同名内存变量的内容赋给字段。,.,23,【例6】从学生表中选出所有男生记录，插入表Man中。INSERTINTOMan;(Mno,Mname,Msex,Sdept,Mage)SELECTSno,Sname,Ssex,Sdept,Sage;FROMStudentWHERESsex=男,.,24,7.3.2更新数据（1）格式UpdateSet=,列名2=And|Or（2）功能对存储在表中的记录进行更新修改。有WHERE的表示对满足WHERE子句条件的元组进行修改。其中SET子句给出的是用于取代相应属性的列值。如果省略WHERE则表示要修改表中的所有元组。,.,25,（3）参数说明：指定要更新的数据所在表。Set=，列名2=And|Or：指定更新表中符合的记录。,.,26,修改一个元组的值【例7】将学号为20073049的学生年龄改为22岁。UPDATEStudentSETSage=22;WHERESno=20073049修改多个元组的值【例8】将所有学生的年龄增加一岁。UPDATEStudentSETSage=Sage+1;,.,27,7.3.3删除数据（1）格式：DeleteFromWhereAnd|Or（2）功能：删除表中指定的记录。Delete命令只是在记录前面添加了删除标记（即逻辑删除），并没有将记录从磁盘删除。要从磁盘上删除记录（即物理删除），则需要执行Pack命令。有WHERE的表示对满足WHERE子句条件的元组进行删除。如果省略WHERE则表示要删除表中的所有元组，但是表定义仍然存在。,.,28,删除一个元组的值【例9】删除学号为20073049的学生记录。DELETEFROMStudent;WHERESno=20073049删除多个元组的值【例10】删除所有学生记录。DELETEFROMStudent,.,29,7.4数据查询,数据库中最常见的操作是数据查询。SELECT语句是SQL语言中最重要的语句之一，执行查询功能。其基本形式由Select-From-Where组成，多个查询可以嵌套运行。另外在简单的查询中，查询可以由Select和From短语构成无条件查询，也可以由SelectFrom和Where构成有条件查询。SELECT语句的功能非常强大，下面我们来详细介绍一下Select的用法。,.,30,7.4.1Select语法结构SelectAll|Distinct.As,.AsFromForce!Inner|LeftOuter|RightOuter|FullOuterJoin!OnInto|ToFileAdditive|ToPrinterPrompt|ToScreenWhereAndAnd|OrAnd|OrGroupByHavingOrderByAsc|Desc,Asc|DescTopPercentUnionAllPreferenceNoconsolePlainNowait,.,31,1.Select子句（1）SelectAll|Distinct中All表示将所有选中的信息都输出来，包括重复的记录；Distinct则表示选出的记录中不包括重复的记录，如果有重复的记录则合并，即只输出一条。（2）.As中可以是字段名，也可以包含用户自定义的函数或者系统函数。是字段所在的表名。可用*号来代替，表示指定所有的字段。（3）As用于指定输出时使用的列标题，可以设置一个不同于字段名的名称。,.,32,2.From子句From子句主要用于指定要查询的表以及连接类型、连接条件等。（1）选择工作区与打开所指的表均由VISUALFOXPRO自行安排。对于非当前数据库，用“！”来指定该数据库中的表。是表的暂用名，取了本地名后，本命令中该表只可使用这个名字。（2）Join关键字用于连接其左右两个所指的表。Inner|LeftOuter|RightOuter|FullOuter表示指定两表联接时的联接类型，一共有四种，具体含义请查阅本章相关内容。（3）On子句：用于指定联接条件。（4）Force子句：严格按指定的联接条件来联接表，避免VISUALFOXPRO因进行联接优化而降低查询速度。,.,33,3.Into与To子句Into与To子句用于指定查询结果的输出去向，默认查询结果显示在浏览窗口中。（1）ToFile子句的表示输出到指定的文本文件，并取代原文件的内容。（2）Additive表示只在原文件中添加新数据，不删除原文件的内容。（3）ToPrinter表示输出到打印机，Prompt表示打印前先显示打印确认框。（4）ToScreen表示将查询结果输出到屏幕。（5）Into中的可以有三种选项，具体请查阅本章相关内容。,.,34,4.Where子句Where子句用于指定查询的条件，即过滤查询结果，若已用ON子句指定联接条件，Where子句中只能指定筛选条件，表示在已按联接条件产生的记录中筛选记录。也可以省去Join子句，一次性地在Where子句中指定联接条件和筛选条件。过滤条件是一个或者多个逻辑表达式，逻辑表达式的操作符请查阅本章相关内容，其中在字符串表达式中可以使用百分号（%）或者下划线（_）来作为通配符，下划线通配任意一个字符，百分号通配任意长度的字符串。,.,35,5.OrderBy子句指定查询结果中的记录按照OrderBy子句中的排序，默认为升序。只可以是字段，或者表示查询结果中列的位置的数字。选项Asc表示升序，Desc表示降序。Top子句必须与OrderBy子句同时使用。表示在复合条件的记录中选取的记录数，范围是132767，排序后并列的若干记录只计一个。含Percent选项时，表示百分比，记录数为小数时自动取整，范围是0.0199.99。,.,36,6.GroupBy子句指定查询结果将按照GroupBy子句中的进行分组，常用于分组统计，值得注意的是，作为分组依据的字段（或者表达式），它的字段值必须不能是惟一，即只有具有重复字段值的字段才能作为分组字段。Having子句与Where子句的功能一样，不同的是当Having子句与GroupBy子句连用时，Having子句可用于记录查询的限制条件。7.Union子句在Select-SQL命令中可以用Union子句嵌入另一个Select-SQL命令，使这两个命令的查询结果合并输出，但输出字段的类型和宽度必须一致。,.,37,8.其他子句除了上述子句以外，数据查询命令还有另外一些子句，其含义如下：（1）Preference子句：用于记载浏览窗口的配置参数，再次使用该子句时可用引用此配置。（2）Noconsole子句：禁止将输出送往屏幕，若指定过Into子句则忽略它的设置。（3）Plain子句：输出时省略字段名。（4）Nowait子句：显示浏览窗口后程序继续往下执行。,.,38,7.4.2查询的应用SQL数据库提供了以下几种查询方式，他们分别是基本查询、条件查询、多表查询（连接查询）、排序查询、集函数查询、分组查询、嵌套查询（子句查询）和集合查询。本节将介绍以上各种查询的方法。1.基本查询SELECT语句功能：从一个或多个表中检索数据。格式：SELECTALL/DISTINCT别名.AS,别名.AS，FROM！,.,39,【例11】查询“学生数据库”中“学生档案表”的全部信息SELECT*FROM学生选课管理!学生信息表,.,40,【例12】查询“学生数据库”中“学生档案表”的所有学生的“姓名”和“性别”。SELECT姓名,性别FROM;学生选课管理!学生信息表,.,41,【例13】根据“学生信息表”列出所有性别的种类SELECTDISTINCT性别FROM学生信息表,.,42,【例14】根据“学生信息表”输出所有学生的“姓名”和“年龄”SELECT姓名,YEAR(DATE()-;YEAR(出生日期)AS年龄FROM;学生信息表以上语句等同于：SELECT姓名，YEAR(DATE()-YEAR(出生日期)年龄FROM学生信息表,.,43,2.条件查询WHERE字句功能：指定查询条件格式：WHEREAND/OR,说明：其中条件表达式可以是单表的条件表达式，也可以是多表的条件表达式。,.,44,【例15】根据“学生信息表”输出男学生的学号和姓名SELECT姓名,学号,性别FROM学生信息表;WHERE性别=”男”,.,45,【例16】根据“学生信息表”输出“入学成绩”高于600分（含600分）的男同学的学号和姓名以及入学成绩SELECT姓名,学号,入学成绩,性别FROM;学生信息表WHERE入学成绩600AND性别=“男”,.,46,【例17】根据“学生信息表”输出“入学成绩”在600到700分之间的学生“姓名”和“入学成绩”。SELECT姓名,入学成绩FROM学生信息表;WHERE入学成绩BETWEEN600AND700该命令等同于：SELECT姓名,入学成绩FROM学生信息表;WHERE入学成绩=600AND入学成绩80,.,52,4.排序查询ORDERBY子句格式：ORDERBYASC/DESE,ASC/DESC,功能：对查询结果按照指定列的值进行排序说明：其中ASC参数为升序，DESC为降序。如果不加ASC或DESC参数，则默认为ASC排序。如果对多字段进行排序，要在OTDERBY后面的字段名与字段名之间加上逗号TOP选项格式：格式TOPPERCENT功能：输出满足条件的前几个记录,.,53,【例25】输出所有学生的成绩单，按“班级代码”列出“学号”、“姓名”、“性别”、“课程名称”、“课程代号”、和“期末成绩”，如果“班级代码”相同，再按“期末成绩”由高到低排序SELECT学生信息表.学号,学生信息表.姓名,;学生信息表.性别,成绩表.课程代号,;成绩表.课程名称,成绩表.成绩;From学生信息表,成绩表;Where学生信息表.学号=成绩表.学号;ORDERBY学生信息表.班级代码DESC,;成绩表.成绩DESC,.,54,.,55,【例26】输出“学生信息表”中“入学成绩”最高的前3个学生的“姓名”、“性别”和“入学成绩”SELECTTOP3姓名,性别,入学成绩;FROM学生信息表ORDERBY入学成绩DESC,.,56,5.集函数查询为了进一步方便使用者，增强检索功能，SQL提供了许多集函数。COUNT子句格式：COUNT(DISTINCT/ALL*)或COUNT(DISTINCT/ALL)功能：统计元组个数或一列中值的个数SUM子句格式：SUM(DISTINCT/ALL)功能：计算一列值的总和说明：计算的列必须为数值型,.,57,AVG子句格式：AVG(DISTINCT/ALL)功能：计算一列值的平均值说明：计算的列