vfp 第6章 结构化查询语言SQL

第6章结构化查询语言SQLSQL的操作功能6.3SQL的定义功能6.2SQL语言概述6.1SQL的查询功能6.46.1SQL语言概述SQL语言的主要优点：SQL语言是一种一体化的语言，提供了完整的数据定义和操纵功能。SQL语言具有完备的查询功能。SQL语言非常简洁，易学易用。SQL语言是一种高度非过程化的语言。SQL语言既能以交互方式直接使用，也能嵌入到各种高级语言中使用。SQL语言不仅能对数据表进行各种操作，也可对视图进行操作。6.2SQL的定义功能6.2.1建立表结构格式：CREATETABLE|DBF表名1[NAME长表名][FREE](字段名字段类型[(字段宽度[,小数位数])][NULL][NOTNULL][CHECK逻辑表达式1[ERROR文本信息1]][DEFAULT表达式1][PRIMARYKEY|UNIQUE][REFERENCES表名2[TAG标识名1]][NOCPTRANS][,字段名2…][,PRIMARYKEY表达式2TAG标识名2|,UNIQUE表达式3TAG标识名3][,FOREIGNKEY表达式4TAG标识名4[NODUP]REFERENCES表名3[TAG标识名5]][,CHECK逻辑表达式2[ERROR文本信息2]])|FROMARRAY数组名【例6-1】创建一个名为“通信录”的自由表，含有姓名、生日、电话号码、手机号码4个字段。CREATETABLE通信录FREE(姓名C(8),;生日D,家庭电话C(14),手机号码C(12))【例6-2】创建一个“商品管理”数据库，在其中创建一个“供应商”表，包含供应商号、供应商名称、地址、电话、传真5个字符型字段，并以“供应商号”字段为关键字创建一个主索引。CREATEDATABASE商品管理CREATETABLE供应商(供应商号C(8)PRIMARYKEY,;供应商名称C(16),地址C(24),电话C(14),传真C(8))【例6-3】在“商品管理”数据库中创建一个“商品”表，包含商品号、商品名称、单价、数量、供应商号5个字段；以“商品号”为关键字创建一个主索引并为其设定一个默认值，为“数量”建立一个取值范围；再以“供应商”表为父表通过共有的“供应商号”为关键字建立两表间的永久关系。OPENDATABASE商品管理CREATETABLE商品(商品号C(8)DEFAULTJP_10109PRIMARYKEY,;商品名称C(16),单价N(8,2),;数量N(4)CHECK数量=10AND数量5000;ERROR数量范围应在10到5000之间！,;供应商号C(8),;FOREIGNKEY供应商号TAG供应商号REFERENCES供应商)6.2.2修改表结构1．命令格式1格式：ALTERTABLE表名1ADD|ALTER[COLUMN]字段名1字段类型[(字段宽度[,小数位数])][NULL][NOTNULL][CHECK逻辑表达式1[ERROR文本信息1]][DEFAULT表达式1][PRIMARYKEY|UNIQUE][REFERENCES表名2[TAG标识名1]][NOCPTRANS]功能：为指定的表增加指定的字段，或者修改指定的字段。【例6-4】为例6-1创建的“通信录”表添加一个宽度为24的“家庭住址”字段，并将其“姓名”字段的宽度改为10。ALTERTABLE通信录ADD家庭住址C(24)ALTERTABLE通信录ALTER姓名C(10)2．命令格式2格式：ALTERTABLE表名1ALTER[COLUMN]字段名2[NULL][NOTNULL][SETDEFAULT表达式2][SETCHECK逻辑表达式2[ERROR文本信息2]][DROPDEFAULT][DROPCHECK]功能：设置或删除指定表中指定字段的默认值和（或）约束条件。【例6-5】在例6-3创建的“商品”表中，为“单价”字段设置一个默认值“888.88”，并删除“数量”字段的条件约束。OPENDATABASE商品管理ALTERTABLE商品ALTER单价SETDEFAULT888.88ALTERTABLE商品ALTER数量DROPCHECK3．命令格式3格式：ALTERTABLE表名1[DROP[COLUMN]字段名3][SETCHECK逻辑表达式3[ERROR文本信息3]][DROPCHECK][ADDPRIMARYKEY表达式3TAG标识名2][DROPPRIMARYKEY][ADDUNIQUE表达式4[TAG标识名3]][DROPUNIQUETAG标识名4][ADDFOREIGNKEY表达式5TAG标识名4REFERENCES表名2[TAG标识名5]][DROPFOREIGNKEYTAG标识名6[SAVE]][RENAMECOLUMN字段名4TO字段名5][NOVALIDATE]功能：删除指定表中的指定字段、设置或删除指定表中指定字段的约束条件、增加或删除主索引、候选索引、外索引，以及对字段名重新命名等。【例6-6】在例6-1创建的“通信录”表中，删除“家庭住址”字段，并将其“电话号码”字段更名为“家庭电话”。ALTERTABLE通信录DROPCOLUMN家庭住址ALTERTABLE通信录RENAMECOLUMN电话号码TO家庭电话6.2.3建立视图格式：CREATEVIEW视图名[(字段名1[,字段名2]…)]ASselect语句【例6-8】在“商品管理”数据库中，创建一个名为“贵重商品”的视图，由“商品”表中单价大于1000元的商品记录构成。OPENDATABASE商品管理CREATESQLVIEW贵重商品AS;SELECT*FROM商品WHERE单价1000USE贵重商品BROWSE6.2.4删除表格式：DROPTABLE表名【例6-10】删除“商品管理”数据库中的“供应商”表，相应命令如下。OPENDATABASE商品管理DROPTABLE供应商6.3SQL的操作功能6.3.1插入纪录1．命令格式1格式：INSERTINTO表名[(字段名1[,字段名2,…])]VALUES(表达式1[,表达式2,…])功能：在指定表的尾部添加一条新记录，并将指定的值赋给对应的字段。2．命令格式2格式：INSERTINTO表名FROMARRAY数组名|FROMMEMVAR功能：由指定数组或内存变量的值在指定表的尾部添加一条新记录。6.3.2更新数据格式：UPDATE表名SET字段名1=表达式1[,字段名2=表达式2…][WHERE逻辑表达式]功能：对于指定的表中符合条件的记录，用指定的表达式值来更新指定的字段值。6.3.3删除纪录格式：DELETEFROM表名[WHERE逻辑表达式]功能：对指定表中符合条件的记录，进行逻辑删除。6.4SQL的查询功能6.4.1SQL查询命令格式：SELECT[ALL|DISTINCT][TOP数值表达式[PERCENT]]检索项[AS列名][,检索项[AS列名]…]FROM[数据库名!]表名[[AS]逻辑别名][[INTO目的地]|[TOFILE文件名]|[TOPRINTER]|[TOSCREEN]][WHERE连接条件[AND连接条件…][AND|OR筛选条件[AND|OR筛选条件…]]][GROUPBY列名[,列名…]][HAVING筛选条件][ORDERBY排序项[ASC|DESC][,排序项[ASC|DESC]…]]功能：根据指定的条件从一个或多个表中检索并输出数据。6.4.2简单查询从单个表中检索满足条件的记录数据。【例6-16】查询Student表中所有外地（籍贯非北京）女生的姓名、年龄、出生日期与籍贯。SELECT姓名,年龄,出生日期,籍贯FROMStudent;WHERE性别=女AND籍贯!=北京【例6-17】查询Student表中有哪些班级的学生（不重复显示相同的班级）。SELECTDISTINCT班级FROMStudent6.4.3特殊运算符使用BETWEEN、LIKE、IN和NOTIN等特殊运算符，使查询更为方便灵活。【例6-21】在Student表中查询年龄在19到20之间的学生记录，并按出生日期排序后显示出来。SELECT*FROMStudent;WHERE年龄BETWEEN19AND20;ORDERBY出生日期【例6-22】在Student表中查询并输出所有姓李的学生记录。SELECT*FROMStudent;WHERE姓名LIKE李%【例6-23】在Student表中查询所有籍贯为“内蒙古”或“山东”的学生记录。SELECT*FROMStudent;WHERE籍贯IN(内蒙古,山东)6.4.4嵌套查询在一个SELECT查询命令的WHERE短语中，包含另一个SELECT查询命令。【例6-25】依据“图书管理”数据库中Student表和Borrow表的数据，列出未曾借过书的男生记录。SELECT*FROMStudent;WHERE性别=男AND学号;NOTIN(SELECT学号FROMBorrow)6.4.5多表查询基于多个相关联数据表的查询。【例6-26】依据Student表和Borrow表的数据，查询借阅“大学英语”书的学生的学号、姓名、性别和所在班级。SELECTStudent.学号,Student.姓名,Student.班级,Borrow.借书日期;FROMStudent,Borrow;WHEREStudent.学号=Borrow.学号AND;Borrow.书名=大学英语6.4.6超联接查询通过包含在SELECT-SQL命令中的FROM…JOIN…ON…短语来实现基于多个相关数据表的查询。格式：SELECT……FROM表名INNER|LEFT|RIGHT|FULLJOIN表名ON联接条件WHERE……其中：INNERJOIN等价于JOIN，为普通的内部联接，即查询结果中仅包含两个表中与联接字段值相匹配的记录。LEFTJOIN为左联接，即查询结果中包含左侧表中的所有记录，以及两个表中与联接字段值相匹配的记录。RIGHTJOIN为右联接，即查询结果中包含右侧表中的所有记录，以及两个表中与联接字段值相匹配的记录。FULLJOIN为完全联接，即查询结果中包含两个表中与联接字段值相匹配的和不相匹配的所有记录。ON联接条件短语用来指定两个表之间的联接条件。6.4.7统计查询通过如下几个库函数实现对查询结果的统计。SUM()计算指定数值列的总和AVG()计算指定数值列的平均值MAX()求指定（数值、字符、日期）列的最大值MIN()求指定（数值、字符、日期）列的最小值COUNT()求查询结果数据的行（记录）数【例6-32】查询Student表中年纪最小学生的生日，以及男生的平均年龄。SELECTMAX(出生日期)FROMStudentSELECTAVG(年龄)FROMStudentWHERE性别=男【例6-33】查询Student表中籍贯为“山东”的学生人数。SELECTCOUNT(*)AS山东学生人数FROMStudent;WHERE籍贯=山东6.4.8分组查询通过GROUPBY短语将检索得到的数据依据某个字段的值划分为多个组后输出。【例6-35】依据Student表中的数据，分别统计各个班级的人数。SELECT班级,COUNT(*)AS人数;FROMStudentGROUPBY班级【例6-36】分别统计Student表中男生、女生的最大年龄与最小年龄。SELECT性别,MAX(年龄)AS最大年龄,MIN(年龄)AS最小年龄;FROMStudentGROUPBY性别