计算机二级vfpSQL命令集合11.docx

计算机二级vfpSQL命令集合11.docx

《计算机二级vfpSQL命令集合11.docx》由会员分享，可在线阅读，更多相关《计算机二级vfpSQL命令集合11.docx（9页珍藏版）》请在冰豆网上搜索。

计算机二级vfpSQL命令集合11

Vf讲义第二部分

第一节关系数据库标准语言

1、SQL概述

（1）SQL（structuredquerylanguage）是结构化查询语言的缩写，是关系数据库的标准语言。

SQL的核心是查询，主要功能为：

数据查询（Select）、数据定义（Create、Drop,Alter）、数据操纵（Insert,Update,Delete）、数据控制（Grant,Revoke）

（2）特点：

A：

是一种一体化的语言

B：

是一种高度非过程化的语言

C：

非常简洁

D：

既可在命令窗口中使用，也可在程序中使用

2、数据查询

（1）简单查询

A：

格式：

select[distinct]字段名列表from表名[where条件]

select姓名,性别,出生日期fromzgda

select*fromzgda

selectdistinct职称fromzgda

selectdistinct性别,职称fromzgda

select*fromzgdawhere性别=”女”

select*fromzgdawhere职称="教授"

select*fromzgdawhere工资>700

select姓名,性别,婚否fromzgdawhere婚否=.t.

select*fromzgdawhere职称="教授"and性别="男"

select*fromzgdawhere性别="男"or出生日期<={^1975-01-01}

B：

注释：

Ø可用通配符“*”来代表所有字段

Ødistinct表示唯一显示，各个字段值完全一致的相同记录只显示一条记录

Øwhere条件构成了条件查询，如果缺省默认为所有记录

Ø查询窗口可以按ESC键关闭

（2）几种特殊的运算符

A：

in

Ø格式：

字段名[not]in（表达式1，表达式2，……）

select*fromzgdawhere职称in（"教授","助教"）

select*fromzgdawhere职称notin（"助教","讲师"）

B：

between

Ø格式1（函数）：

[not]between（字段名，表达式1，表达式2）

select*fromzgdawherebetween（工资,500,700）

Ø格式2（短语）：

字段名[not]between表达式1and表达式2

字段的取值范围在或不在：

>=表达式1并且<=表达式2

select*fromzgdawherenotbetween（奖金,100,200）

C：

like

Ø格式1（函数）：

like（表达式，字段名）

select*fromzgdawherelike（"王*",姓名）

格式2（短语）：

字段名like表达式

select*fromzgdawhere姓名like“_小_”

（3）排序

Ø格式：

select[topn[percent]][all/distinct]字段表达式[as新字段名]from表名orderby排序字段列表[asc]/[desc]

orderby短语的使用，asc表示升序，desc表示降序，默认升序

select*fromzgdaorderby工资

select*fromzgdaorderby出生日期desc

select*fromzgdaorderby奖金,工资desc

select*fromzgdawhere性别="女"orderby奖金desc

topn[percent]表示在符合查询条件的所有记录中，选取指定数量或百分比的记录，Top短语必须与orderby短语同时使用

selecttop3*fromzgdaorderby出生日期

selecttop2*fromzgdawhere婚否=.t.orderby工资desc

产生新字段的方法，字段表达式as新字段名

select姓名,职称,工资+奖金as实发工资fromzgda

select姓名,性别,year（date（））-year（出生日期）as年龄fromzgdawhere职称="助教"orderby年龄desc

（4）分组与计算查询

A：

计算查询

Øsum（求和字段名）

Øavg（字段名）

Øcount（*）

Ømax（字段名）

Ømin（字段名）

selectsum（工资）as工资合计fromzgda

selectsum（工资）as工资合计,avg（工资）as平均工资,max（工资）as最高工资,min（工资）as最低工资fromzgdawhere性别=”男”

selectcount（*）as职工人数fromzgda

selectcount（*）as职工人数fromzgdawhere职称=”教授”

注释：

以上函数均为列项求值；查询中只要有任一个（也可为多个）函数，如果没有groupby分组，则查询结果只有表中最后一条记录，如果有groupby分组，则查询结果留下每一类最后一条记录。

如果使用了以上函数，则在查询结果中只有分组字段和统计字段有效，其他字段均无实际意义。

B：

分组查询：

格式：

select字段表达式[as新字段名]from表名where条件Groupby分类字段having条件

注释：

Groupby按列的值对查询的行进行分组，查询结果留下每一类最后一条记录

select职称,sum（工资）as工资合计fromzgdagroupby职称

select性别,count（*）as职工人数fromzgdagroupby性别

select职称,性别,sum（工资）as工资合计,count（*）as人数fromzgdagroupby职称,性别orderby职称desc

where短语是指在原表中进行条件筛选，也就是说，只有满足条件的记录才参与分组。

Select职称,count（*）as人数fromzgdawhere性别=”女”groupby职称

Select性别,sum（工资）as工资合计fromzgdawhere职称=”讲师”groupby性别

select职称,性别,sum（工资+奖金）as工资总和,max（工资+奖金）as最高工资,avg（工资+奖金）as平均工资fromzgdagroupby职称,性别

Having包含在查询结果中的组必须满足的条件，即对分组的结果进行筛选，去掉不满足条件的分组，必须与Groupby同时使用

select职称,count（*）as职工人数fromzgdagroupby职称

select职称,count（*）as职工人数fromzgdagroupby职称having职工人数>=2

如果有Groupby短语，而无orderby短语时，查询的结果默认以分组字段的升序排列

如果Where和Having同时使用时，先执行Where，再执行Having

补充：

在查询的过程当中使用了统计函数，并且查询结果可能有多个值，此时使用groupby短语进行分组，在分组了以后，每组对应一条记录

（5）集合的并运算（union）

A：

是将两个select语句的查询结果合并成一个查询结果，默认情况下，合并的结果删除重复的记录。

用all表示显示所有记录（包含重复记录）

B：

合并条件：

Ø列数相同，并且对应字段的数据类型和宽度相同

Ø对应字段的取值范围相同

合并格式：

第一条完整的sql语句union第二条完整的sql语句

例如：

sele*fromzgdawhere性别=”女”unionsele*fromzgdawhere职称=”教授”

（6）利用空值查询

注：

查询空值时用：

字段名isnull

select*from订购单where订购日期isnull

查询不为空值时用：

字段名isnotnull

select*from订购单where订购日期isnotnull

（7）保存查询结果

格式：

select[all/distinct]字段表达式[as新字段名][topn[percent]]from表名Groupby分类字段having条件orderby排序字段列表[asc]/[desc]

A：

输出到临时表中：

intocursor表名

Ø执行完select语句后，临时表仍然保持打开和活动状态，但为只读

Ø一旦关闭临时表，查询结果则自动被删除

B：

输出到永久表中：

intodbf/table[表名]

Ø执行完select语句后，此表依然处于打开和活动状态

Ø这个表将永远保存在默认目录下

C：

输出到数组中：

intoarray数组名

Ø这里的数组为二维数组，每行一条记录，每列对应查询结果的一列，数组中有几行几列由查询结果中记录以及字段来确定

Ø数组中可以存放n个值，但他只属于一个内存变量

Ø数组的下标是从1开始的。

D：

输出到文本文件中：

tofile文件名[additive]

Ø扩展名为.txt,无格式仅是纯文本

Ø有additive表示将查询结果追加到此文件内容的后面

3、数据定义

（1）建立表（若建立数据库表，则必须事先建立或打开数据库）

格式：

createtable表名[free]（字段名1类型（宽度，小数位数）[check条件][error字符串][default默认值][NULL][NOTNULL][primarykey][unique]，…，…[，foreignkey索引表达式tag索引名references父表名[tag父表的索引名]]）

注释：

Øfree说明此表为自由表。

createtable注册free（姓名c（6）,性别c

（2）,出生年月d）

Ø类型只能用代表字母。

Øcheck为字段指定有效性规则；error表示出错时的提示信息

createtablestudent（姓名c（6）,性别c

（2）,年龄icheck年龄>=10error"年龄超范围"）

ØDefault表示默认值

createtablescore（学号c（10）,成绩n（7,2）default40）

createtable教师（姓名c（6）,性别c

（2）default"女",基本工资n（8,2））

ØNULL表示字段中允许取空值;NOTNULL表示字段中不能取空值。

createtable职工（编号c（5）,姓名c（6）,工资n（5,2）nulldefault.null.）

Øprimarykey表示建立主索引

Øunique表示建立候选索引

Øforeignkey表示建立普通索引，但不能单独使用，在此之前必须先给父表建立主索引或侯选索引，建立普通索引的同时和父表建立永久性关系。

若无[tag父表的索引名]选项，则表示将关系建立在父表的主索引上。

Ø不能建立唯一索引

（2）修改表结构

A：

字段

Ø修改字段名

altertable表名rename[column]原字段名to新字段名

Ø修改字段的宽度和类型

altertable表名alter[column]字段名类型（宽度，小数位数）

Ø添加新字段

altertable表名add[column]字段名类型（宽度，小数位数）

Ø删除字段名

altertable表名drop[column]字段名

B：

修改字段有效性

Ø修改规则及提示信息

altertable表名alter字段名setcheck条件[error字符串]

Ø修改字段的默认值

altertable表名alter字段名setdefault默认值

Ø删除规则及提示信息

altertable表名alter字段名dropcheck

Ø删除字段的默认值

altertable表名alter字段名dropdefault

补充：

添加新字段的同时设置有效性规则

C：

索引（表事先存在）

Ø建立主索引

altertable表名addprimarykey索引表达式tag索引名

Ø删除主索引

altertable表名dropprimarykey

Ø建立侯选索引

altertable表名addunique索引表达式tag索引名

Ø删除侯选索引

altertable表名dropuniquetag索引名

Ø建立两表之间的永久关系（建立普通索引）

altertable当前表（子表）addforeignkey索引表达式tag索引名references父表[tag父表的索引名]

Ø删除两表之间的永久关系（删除普通索引）

altertable表名（子表）dropforeignkeytag索引名

（3）表的删除（物理删除）

A：

删除自由表:

droptable表名

B：

删除数据库表：

Ø打开数据库

Ødroptable表名

4、操作功能

（1）插入记录

A：

插入单条记录

insertinto表名（字段名1，字段名2，…）values（表达式1，表达式2，…）

Ø注释：

如果要向当前表所有字段中插入所有值，则第一部分可以省略

Ø第二部分表达式的值必须与对应的字段类型相同

B：

将数组中的值插入到表中

Øinsertinto表名fromarray数组名

（2）删除记录（逻辑删除）

Ødeletefrom表名where条件

（3）有规律的修改记录或替换记录

Øupdate表名set要修改的字段名=用谁来修改where条件

5、联接查询

（1）简单联接查询

select多表中字段名列表from多表列表where联接条件

Ø基于多个表的查询，查询的结果可以出自多个表中

找出工资多于1230元的职工号和他们所在的城市

Select职工号,城市from职工,仓库where（工资>1230）and（职工.仓库号=仓库.仓库号）

找出工作面积大于400的仓库的职工号一级这些职工工作的城市

Select职工号,城市from仓库,职工where（面积>400）and（职工.仓库号=仓库.仓库号）

（2）嵌套查询

select字段列表from表名where条件或字段名关键字in（select一个字段名关键字from表名where条件）

例：

哪些城市至少有一个仓库的职工的工资为1250

Select城市from仓库where仓库号in（select仓库号from职工where工资=1250）

Ø查询的结果来自一个表，而查询的条件却涉及到其他表

Øin的前面必须写关键字段，子查询的查询结果只能有一个字段，在内容上必须和in前的一样，但它们却发属于两个表

Ø子查询的查询结果做为主查询的一个查询条件，子查询应该是一条完整的sql语句

Ø在嵌套查询当中，子查询中不能再使用嵌套查询

（2）超联接查询（基于多表的查询）

A：

两表：

selectfrom表1[inner/left/right/full]join表2on联接条件

Ø联接条件在ON短语中给出，联接类型在FROM短语中给出

ØINNERJOIN满足联接条件的记录才出现在查询结果中

ØLEFTJOIN在查询结果中包含JOIN左侧表中的所有记录，以及JOIN右侧表中匹配的记录

ØRIGHTJOIN在查询结果中包含JOIN右侧表中的所有记录，以及JOIN左侧表中匹配的记录

ØFULLJOIN在查询结果中包含JOIN两侧所有的匹配和不匹配记录

B：

三表

Øselect字段列表from第一个表innerjoin第二个表innerjoin第三个表on第三个表中关键字=第二个表中关键字on第二个表中关键字=第一个表中关键字