搜索
第5章SQL语言与视图设计(6学时)computercenter.jlu.edu.cn吉林大学计算机教学与研究中心2/54SQL语言的组成SQL：StructuredQueryLanguage是结构化查询语言,也是关系数据库的通用语言SQL语言的组成：数据定义语言：用于建立（Create）、修改（Alter）和删除（Drop）数据库中的各类对象3/54SQL语言的组成数据操纵语言：在表中增加（Insert）、删除（Delete）和修改（Update）数据记录数据查询语言：SQL语言的核心内容，通过Select语句可以从一个或多个表中提取数据进行查询、排序、汇总和表联接等。数据控制语言：用于控制用户访问数据库，实现授权（Grant）和收回（Revote）授权，VFP并不支持这部分功能4/54VFP中的SQL语言对语句中所涉及表的处理：对已打开的表，保留其工作区。对未打开的表，自动在空闲的、号最小的工作区中打开。执行完语句后，不关闭文件。5/545.1.1建立自由表建立自由表：语句格式：CreateTable表名[Free](字段名1类型描述1[[Not]Null]Unique……[,字段名n类型描述[[Not]Null]]Unique)例5.1没有当前数据库，可以省略;有当前数据库且省略，则建立数据库表数据类型符号[(宽度[，小数位数])]例如：N（10，2）DNotNull（默认）表示字段不能为空值候选索引6/545.1.2建立数据库表语句格式：CreateTable表名(字段名1类型描述1[[Not]Null][Check逻辑表达式[Error字符表达式]][Default表达式][PrimaryKey|Unique]……[,字段名n类型描述n[[Not]Null]……][,PrimaryKey索引关键字表达式Tag主索引标识名|,Unique索引关键字表达式Tag候选索引标识名]）例如：Check入学年份1999And入学年份2021；Error“入学年份应该在2000～2020之间”；表达式的值为.T.，表示数据正确，通过检查。字段设为主索引关键字（主键），索引标识名为本字段名。字段设为候选索引关键字，索引标识名为字段名，一个表中可有多个候选索引关键字。多个字段构成主关键字，且可用函数。例如：primarykeyxm+dtoc(csrq)tagxc不能与字段中的PrimaryKey短语同用例5.2例5.37/545.1.3修改表结构语句格式1：AlterTable表名Add|Alter字段名类型描述[[Not]Null][Check逻辑表达式[Error字符表达式]][Default表达式][PrimaryKey|Unique]例5.4语句说明：在表中增加字段，修改表中字段的类型描述、有效性规则、默认值和主键。修改已有字段的类型描述（数据类型和宽度）、有效性规则、默认值和主键等信息增加新字段。例如：Add备注C(60)8/545.1.3修改表结构语句格式2：AlterTable表名Alter字段名[[Not]Null][SetCheck逻辑表达式[Error字符表达式]][DropCheck][SetDefault表达式][DropDefault]例5.5设置字段的有效性规则和默认值删除字段的有效性规则和默认值9/545.1.3修改表结构语句格式3：AlterTable表名[Drop字段名][AddPrimaryKey索引关键字表达式Tag主索引标识名][DropPrimaryKey][AddUnique索引关键字表达式Tag候选索引标识名][DropUniqueTag候选索引标识名][Rename原字段名To新字段名]例5.6增加候选索引删除字段字段改名删除候选索引删除主索引增加主索引10/545.1.4删除表语句格式：DropTable表名|？[Recycle]例5.7语句说明：删除表文件、结构化复合索引文件（CDX）和备注文件（FPT）。如当前打开了表所在的数据库，则同时删除数据库中表的相关信息。从弹出的窗口中选要删除的表11/545.2.1增加数据记录数据操纵语言可在表中增加（Insert）、删除（Delete）和修改（Update）数据记录。语句格式1：InsertInto表名[（字段名表）]Values(表达式表)语句说明：在指定的表尾部追加记录。12/545.2.1增加数据记录语句格式2：InsertInto表名FromArray数组名例5.9A(2,4)A(1,2)A(1,1)A(2,1)A(2,1)13/545.2.2修改数据记录语句格式：Update表名Set字段名1=表达式1[……,字段名n=表达式n][Where条件表达式]UpdatexsbSet姓名=马立伟;Where学号=22060101语句说明：用表达式的值修改对应字段的值。省略Where短语，修改表中全部记录相关字段的值14/545.2.2修改数据记录（1）区间运算：表达式1Between表达式2And表达式3（2）属于运算：表达式1IN（表达式表）（3）模糊运算：字符表达式1Like字符表达式2（4）判断Null运算：字段名Is[Not]Null如表达式1的值在表达式2和表达式3之间（含相等），则结果为.T.，否则，结果为.F.。例5.10如两个字符串匹配，则结果为.T.，否则，结果为.F.例5.12如字段的值为.Null.,则结果为.T.，否则，结果为假.F.。在VFP中可将任意数据类型的字段默认值设为.Null.。此值与空值不同，通过Empty(表达式)函数可判断空值。例5.13例5.14如表达式1的值等于表达式表中某个表达式的值，则结果为.T.，否则，结果为.F.。例5.1115/545.2.3给数据记录加删除标记“*”语句格式：DeleteFrom表名[Where条件表达式]例5.15带删除标记的记录仍然存放在表中，称之为逻辑删除的记录。用VFP命令Pack，可以将其彻底删除；用Recall命令可以去掉其删除标记。语句说明：如省略Where，则为表中全部记录加删除标记“*”。＊＊16/54基本语句格式：Select[表别名.]*|表达式1[[As]列名1]……,表达式n[[As]列名n]From[数据库名1!]表名1[[As]表别名1]……[,数据库名n!]表名n[[As]表别名n][Where条件表达式]5.3.1Select语句基本查询数据源。可从一个或多个表中提取数据。如ＸＳＢ数据投影。如学号和姓名。记录选择。如：民族码’01’17/54（1）[表别名.]*:包含对应表中的全部字段，如省略表别名，则查询结果中包含每个表中的所有字段。（2）表达式:按表达式i提取和运算第i列的数据。表达式i中可以用常数、变量、字段或VFP函数。（3）[As]列名称i:为第i列定义列名称。（4）统计函数例5.19函数名称函数格式说明求平均值AVG（表达式）求一列数据的平均值计数Count（参数）求记录个数，参数可以是任何表达式Count（*）求记录个数求最大值Max（表达式）求出字段值中的最大值求最小值Min（表达式）求出字段值中的最小值求和SUM（表达式）求一列数据的和5.3.1Select语句基本查询Select姓名,CJB.*FromXSB,CJB;WhereXSB.学号=CJB.学号Select;学号,课程码,考试成绩+课堂成绩+实验成绩;FromCJBSelect学号,课程码,;考试成绩+课堂成绩+实验成绩As期末成绩;FromCJB18/54From[数据库名i!]表名i[[As]表别名i]：查询数据的来源，当来源于多个表时，表名间用逗号，分开。当表为当前数据库或在默认目录中时，可以省略数据库名。[As]表别名i（可省略As）为表起临时别名，仅在本Select语句及其子查询中有效。如省略此项，则表名本身即为别名。为表起了临时别名后，表名不再为别名。5.3.1Select语句基本查询Select姓名,CJB.*FromXSXX!XSB,XSXX!CJB;WhereXSB.学号=CJB.学号SelectXSB.学号,姓名,考试成绩FromXSB,；CJBAsKWhereXSB.学号=K.学号19/54设置记录筛选条件Where条件表达式：从表中提取记录的条件或表之间的联接条件，仅查询满足条件的记录。例5.20例5.215.3.1Select语句基本查询SelectX.学号,姓名,考试成绩;FromXSBAsX,CJB;WhereX.学号=Cjb.学号And课程码='010101'20/545.3.2多表联接从多个表中提取数据，形成一个查询结果。21/541.设置表联接类型表名i联接类型i表名i+1：设置表间的联接类型，表名通过“联接类型”联接。4种联接类型InnerJoin：内部联接，仅联接两个表中符合联接条件的数据记录。5.3.2多表联接InnerJoin、RightJoin、LeftJoin或FullJoinSelectXSB.学号,姓名,考试成绩From;XSBInnerJoinCJB；OnXSB.学号=CJB.学号；Where课程码=01010122/54Right[Outer]Join：右联接，联接两个表中符合联接条件的记录，再追加右表中不符合联接条件的记录（左表中某些字段为.NULL.）。Left[Outer]Join：左联接，联接两个表中符合联接条件的记录，再追加左表中不符合条件的记录，(右表中某些字段为.NULL.)。Full[Outer]Join：完全联接，也称全联接。联接两个表中符合联接条件的记录，再追加上左和右表中不符合联接条件的记录。5.3.2多表联接SelectXSB.学号,姓名,课程码,考试成绩;FromXSBRightJoinCJB；OnXSB.学号=CJB.学号SelectXSB.学号,姓名,课程码,考试成绩;FromXSBLeftJoinCJB；OnXSB.学号=CJB.学号SelectXSB.学号,姓名,课程码,考试成绩;FromXSBFullJoinCJB；OnXSB.学号=CJB.学号23/542.设置表联接条件On条件表达式：在各个表内查找满足条件的记录，每找到一组满足条件的记录就联接成一条新记录如果表名之间用逗号，分隔，则不能使用On短语。只能通过Where实现联接，但执行速度较慢。例5.225.3.2多表联接SelectXSB.学号,姓名,课程码,考试成绩;FromXSBRightJoinCJB；OnXSB.学号=CJB.学号24/545.3.3处理查询结果1.设置排序参数OrderBy排序列[ASC|DESC]：设置查询结果的排序列和方式，排序列可以是查询结果中的列名或列序号。OrderBy课程名,期末成绩DESC,1例5.2325/542.数据分组（1）GroupBy分组列：分组列值相同的记录统计成一行。分组列可为结果中的列名或列号。可用多列实现多级分组。非统计和分组列的值为组中最后记录的值。Select课程码,Count(学号)As选课人数;Fromcjbgroupby课程码例5.245.3.3处理查询结果26/54（2）Having条件表达式：用GroupBy时，可用Having条件表达式短语对结果行进一步筛选，使结果中仅含符合“条件表达式”的数据行。Having短语通常与GroupBy结合，在没有GroupBy的Select语句中用Having时，其功能与Where相似，并且可以并列。例5.255.3.3处理查询结果Select课程名,Count(学号)As人数,;Avg(考试成绩)