数据库命令、函数及程序语句大全

  sre

数据库命令、函数及程序语句大全

VFP命令是一种实现对数据库系统进行操作的动词或短语。VFP命令可在VFP命令窗口中单独 运行,也可编制成程序成批运行,由命令和函数编制的程序可对数据库系统进行自动、复杂的操作,使数据库系统应用起来更加友好、方便。

¨         VFP常用命令分类表

类别 命令
建立文件类 CREATE,MODIFY,COPY,SAVE等
数据库操作类 USE,CLOSE,COPY,MODIFY STRUCTURE等
数据编辑类 BROWSE,EDIT,CHANG,REPLACE,UPDATE等
记录指针操作类 GOTO,LOCATE,SEEK,FIND,SKIP等
记录操作类 APPEND,DELETE,INSERT等
数据显示类 BROWSE,LIST,DISPLAY等
数据统计类 SUM,COUNT,AVERAGE,TOTAL等
存变量操作类 ACCEPT,INPUT,READ,SAVE等
程序控制类 DO,DO WHILE…ENDDO,IF…ENDIF等
系统设置类 SET TALK,SET DEFAULT TO等

¨         命令格式

VFP命令一般由命令动词和控制 短语构成。

一般格式:

命令动词[FROM<源文件>][TO<目标 文件>][记录范围][FOR/WHILE<条件>]

[FIELDS<字段名表>]

表示可选,<>表示必选, / 表示或选

例如:

(1)APPEND FROM C:\RSDA.DBF FOR 基本工资>100 FIELDS 编号,姓名,职称,基本工资。

从C:\RSDA.DBF 表中把基本工资大于100元的记录添加到当前表添加字段为:编号,姓名,职称,基本工资。

(2)COUNT FOR 职称=”高工”TO GG 统计高工的记录数到内存变量GG。

 

控制短语规定了对数据库操作的限制条件:

1.指向输入输出目标

FROM/TO

APPEND FROM,COPY FILE TO <目标文件>

2.限制记录范围(SCOPE)

ALL 所有记录

NEXT<n> 本记录后的第几个记录

RECORD<1> 第1个记录

REST 当前记录之后所有记录

3.选择字段

FIELDS<字段表>选择输入/输出的字段。

4.限制条件

FOR/WHILE<条件>

FOR 基本工资>100 .AND.基本工资<200

¨         命令格式规则

命令的行为单位,一行书写不完,在未尾加上“;”第二行接着写。

必须以命令动词开头,动词后的短语顺序不限。

命令中大小写字母等效。

命令动词和关键字可以只写前4个字符。

不得用命令动词或关键字作为自定义变量、 函数、程序名。

一.表结构的操作
1.创建表结构
命令1:CREATE [路径][表名]
例子:在A盘创建GZ2数据表。
CREATE A:GZ2
命令2:CREATE [新表名] FROM [旧表名]
例子:在A盘由GZ1创建GZ3数据表。
CREATE A:GZ3 FROM A:GZ1
命令3:COPY STRUCTRUE TO [路径][表名]
例子:在D盘生成一个与正在使用的表结构相同的新表文件MY
COPY STRUCTRUE TO D:\MY
2.表结构的显示
命令:LIST STRUCTURE
DISPLAY STRUCTURE
例子:显示当前数据表在结构。
LIST STRUCTURE
3.表结构的修改
命令:MODIFY STRUCTURE
作用:显示表文件结构,并允许修改此表的结构
说明:只有在用USE命令打开表文件以后,才能显示或修改表文件的结构。
例子:修改当前数据表的结构。
MODIFY STRUCTURE

二.表文件的打开和关闭
1.打开
命令:USE 表名 [INDEX 索引文件名] [EXCLUSIVE]
例子:打开A盘GZ1数据表。
USE A:\GZ1
例子:以独占方式打开当前表文件XX
USE XX EXCLUSIVE
2.关闭
命令:USE
例子:关闭当前打开的表。
USE

三.记录指针的定位
1. 绝对定位
命令:GO[TO] n|TOP|BOTTOM
说明: n定位到第n条记录,TOP定位到最上面一条记录,BOTTOM定位到最下端一条记录
2.相对定位
命令:SKIP [+|-]n
说明:它以当前记录为基准前移(-)或后移(+)n个记录,不选任选项,则默认记录指针后移一条记录。

四.表数据的添加
1.记录的追加
命令:APPEND [BLANK]
例子:向当前数据表尾部追加记录。
APPEND
例子:向当前数据表尾部追加一条空记录。
APPEND BLANK
2.记录的插入
命令:INSERT [BLANK][BEFORE]
例子:在当前记录后插入一条空记录。
INSERT BLANK
例子:在第3条记录前插入一条记录。
GO 3
INSERT BEFORE
3.用复制方法建新表记录
命令:COPY TO 新表名 [范围][FIELDS 字段表][FOR 条件]
例子:把当前表中“应发合计”大于1000元的记录复制到新数据表GZ2中。
COPY TO A:GZ2 FOR 应发合计>1000
4.从其他表取数据
命令:APPEND FROM 表名 [FOR 条件]
例子:把GZ1表中“应发合计”大于1000的记录复制到当前的表后面。
APPEND FROM GZ1 FOR 应发合计>1000

五.表记录的显示
1.浏览显示
命令:BROWSE [FIELDS 字段表][FOR 条件]
例子:显示当前表中“应发合计”大于1000元的记录的职工编号、姓名、应发合计三个字段。
BROWSE FIELDS 职工编号,姓名,应发合计 FOR 应发合计>1000
说明:此命令的结果以浏览窗口的形式出现,具有修改功能。
2.显示输出
命令:LIST|DISPLAY [OFF] [<范围>] [FIELDS] <表达式表> [WHILE <条件>] [FOR <条件>] [TO PRINT|TO FILE <文件>]
说明:(1)命令带OFF参数表示不显示记录号
(2)范围指定对哪些记录进行操作,(范围包括:RECORD n :第几号记录;NEXT n: 当前记录开始的几个记录;REST: 自当前记录开始至文件末尾的所有记录;ALL 所有的记录。不选范围则隐含范围为ALL)
(3)字段名表指在结果中需要出现的字段名,字段名与字段名之间用逗号分隔。不带时指显示所有字段。
(4)WHILE后跟条件,FOR后面也跟条件,区别是,WHILE后的条件如果满足,便停止查找;FOR后的条件查找所有满足条件的记录。
(5)TO PRINT和TO FILE <文件>分别表示将显示结果在打印机上打印出来和将显示结果输出到文件。
(6)DISPLAY命令在显示记录满屏后,要求用户按任意键继续显示,并且在该命令中,如果省略范围仅显示当前记录。LIST不分屏,如果省略范围显示 所有记录。

六.修改记录内容
1. 编辑或修改的特定记录
命令:CHANG|EDIT [范围][[FIELDS]字段表][FOR 条件] [WHILE <条件>]
例子:修改第五条记录
CHANGE|EDIT RECORD 5
2.成批自动替换记录
命令:REPLACE [范围] 字段 WITH 表达式 [FOR 条件]
例子:计算各记录“应发合计”字段的值。
REPLACE ALL 应发合计 WITH 职务工资+岗位津贴+保留奖金+保留津贴

七.删除记录
1.逻辑删除
命令:DELETE [范围][FOR 条件] (打删除标记)
2.物理删除
命令: PACK (删除有删除标记的记录)
ZAP (删除所有记录)
例子:删除“职工编号”空白的记录。
DELETE ALL FOR 职工编号=””
PACK

八.数据表排序
1.物理排序,生成一个新的表文件。
命令:SORT TO 新表名 ON 字段名 [/A][/D] [范围][FOR 条件]
例子:把当前表中记录按“应发合计”从大到小重新排序。
SORT TO A:GZ3 ON 应发合计 /D
2.逻辑排序,生成一个索引文件。
(一)标准索引文件:IDX
命令:
新建索引:INDEX ON 字段名表达式 TO 索引文件名
同时打开:USE 表名 INDEX 索引文件名
之后打开:SET INDEX TO 索引文件名
重新索引:INDEX ON 字段名表达式 TO 索引文件名
关闭索引:SET INDEX TO
例子:对当前数据表以“应发合计”从大到小排列。
INDEX ON -应发合计 TO GZ
(二)复合索引文件:CDX
命令:
新建索引:INDEX ON 字段名 TAG 标记 [ASCEDING|DESCENDING]
同时打开:USE 表名 ORDER [TAG] 标记
改变索引:SET ORDER TO [TAG] 标记
重新索引:INDEX ON 字段名 TAG 标记
关闭索引:SET ORDER TO
删除索引:DELETE TAG 标记
例子:对当前数据表以“应发合计”从大到小排列。
INDEX ON 应发合计 TAG HJ DESC

九.查找记录
1.顺序查找
命令:LOCATE FOR 条件 [范围]
继续查找命令:CONTINUE
例子:在当前的表中查找姓“张”的记录。
LOCATE FOR 姓名=”张”
2.索引查找
说明:根据当前的索引来查找记录。在使用命令前先要按准备查找的字段建立索引或排序
命令:FIND 字符串
SEEK 表达式
用FOUND()检查找到否
例子:在当前的表中查找姓“张”的记录。
FIND 张
SEEK “张”
3.查找函数
说明: 在使用命令前先要按准备查找的字段建立索引或排序
函数:SEEK()
例子:在当前的表中查找姓“张”的记录。
SEEK(“张”)

十.统计数据表记录个数
命令:COUNT [范围][FOR 条件][TO 变量]
例子:统计当前表中“应发合计”大于1000元的人数。
COUNT FOR 应发合计>1000

十一.数据表 数值字段的求和
命令:SUM [范围][FOR 条件][TO 变量]
例子:统计当前表中“应发合计”字段的总和。
SUM 应发合计

十二.算术平 均值的计算
命令:AVERAGE [范围][FOR 条件][TO 变量]
例子:统计当前表中“应发合计”字段的平均值。
AVERAGE 应发合计

十三.多功能 计算
命令:CALCULATE 表达式 [范围][FOR 条件][TO 变量]
例子:分别统计当前表中“应发合计”字段的人数、总和、平均值。
CALCULATE CNT(应发合计),SUM(应发合计),AVG(应发合计)

十四.数值运算函数

函数 操作 结果
SQRT(x) 求平方根 SQRT(9) 3
INT(x) 取整数 INT(3.14) 3
ROUND(x) 四舍五入 ROUND(3.14159,4) 3.1416
MOD(x,y) 求X除以Y的余数 MOD(25,4) 1
MAX(x,y) 求X,Y中的最大值 MAX(7,8) 8
MIN(x,y) 求X,Y中的最小值 MIN(1,2,3) 1

十五.字符中操作函数

函数名 操作 结果
UPPER(S) 小写字母 UPPER(abc) ABC
LOWER(S) 大写字母 LOWER(ACD) acd
LEN(S) 求字符串的长度 LEN(“中国1号”) 7
AT(S1,S2) 在字符串S2,找字 符串S1 AT(“H”,”CHINA”) 2
SUBSTR(S,I,N) 在S字符串中的第I 个字符起取N个字符 SUBSTR(‘TECHNOLO’,3,3) CHN
LEFT(S,N) 从字符串S左边取N 个字符 LEFT(“中国人”,4) 中国
RIGHT(S,N) 从字符串S右边取N 个字符 S=‘12345’
RIGHT(S,3)
345
SPACE(N) 生成N个空格 “合 计”+SPACE(2)+”123” 合计123
TRIM(S) 消除字符串尾部空格 TRIM(“姓名     “) 姓名
ALLTRI(S) 消除字符串中所有空格 ALLTRIM(”李      丽”) 李丽
STUFF(S1,N1,N2,S2) 用字符串S2替换 S1中第N1个字符起的N2个字符 STUFF(”NOW”,2,1,”E”) NEW
& 宏替换 P=“G2”
USE &P
USE G2

SQL Server 2005数据库常用命令

SQL分类:
DDL—数据定义语言(CREATE,ALTER,DROP,DECLARE)
DML—数据操纵语言(SELECT,DELETE,UPDATE,INSERT)
DCL—数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)
首先,简要介绍基础语句:
1、说明:创建数据库
CREATE DATABASE database-name
2、说明:删除数据库
drop database dbname
3、说明:备份sql server
— 创建 备份数据的 device
USE master
EXEC sp_addumpdevice ‘disk’, ‘testBack’, ‘c:\mssql7backup\MyNwind_1.dat’
— 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明:创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表:
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only
5、说明:删除新表drop table tabname
6、说明:增加一个列
Alter table tabname add column col type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
7、说明:添加主键: Alter table tabname add primary key(col)
说明:删除主键: Alter table tabname drop primary key(col)
8、说明:创建索引:create [unique] index idxname on tabname(col….)
删除索引:drop index idxname
注:索引是不可更改的,想更改必须删除重新建。
9、说明:创建视图:create view viewname as select statement
删除视图:drop view viewname
10、说明:几个简单的基本的sql语句
选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
查找:select * from table1 where field1 like ’%value1%’ —like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count(*) as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1
11、说明:几个高级查询运算词
A: UNION 运算符
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B: EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。
C: INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
12、说明:使用外连接
A、left outer join:
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B:right outer join:
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
C:full outer join:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
其次,大家来看一些不错的sql语句
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
法一:select * into b from a where 1<>1
法二:select top 0 * into b from a
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子:..from b in ‘”&Server.MapPath(“.”)&”\data.mdb” &”‘ where..
4、说明:子查询(表名1:a 表名2:b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明:显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明:外连接查询(表名1:a 表名2:b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明:在线视图查询(表名1:a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明:in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明:两张关联表,删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明:四表联查问题:
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where …..
12、说明:日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())>5
13、说明:一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明:前10条记录
select top 10 * form table1 where 范围
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等 等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明:随机取出10条数据
select top 10 * from tablename order by newid()
18、说明:随机选择记录
select newid()
19、说明:删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,…)
20、说明:列出数据库里所有的表名
select name from sysobjects where type=’U’
21、说明:列出表里的所有的
select name from syscolumns where id=object_id(‘TableName’)
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。
select type,sum(case vender when ‘A’ then pcs else 0 end),sum(case vender when ‘C’ then pcs else 0 end),sum(case vender when ‘B’ then pcs else 0 end) FROM tablename group by type
显示结果:
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明:初始化表table1
TRUNCATE TABLE table1
24、说明:选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
随机选择数据库记录的方法(使用Randomize函数,通过SQL语句实现)
对存储在数据库中的数据来说,随机数特性能给出上面的效果,但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案 是建立如下所示的循环:
Randomize
RNumber = Int(Rnd*499) +1

While Not objRec.EOF
If objRec(“ID”) = RNumber THEN
… 这里是执行脚本 …
end if
objRec.MoveNext
Wend

这很容易理解。首先,你取出1到500范围之内的一个随机数(假设500就是数据库内记录的总数)。然后,你遍历每一记录来测试ID 的值、检查其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码。假如你的RNumber 等于495,那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些,但相比更为稳固的企业解决方案这还是个小型数据库了,后者通常在一 个数据库内就包含了成千上万条记录。这时候不就死定了?
采用SQL,你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset,如下所示:
Randomize
RNumber = Int(Rnd*499) + 1

SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber

set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & ” = ” & objRec(“ID”) & ” ” & objRec(“c_email”)

不必写出RNumber 和ID,你只需要检查匹配情况即可。只要你对以上代码的工作满意,你自可按需操作“随机”记录。Recordset没有包含其他内容,因此你很快就能找到 你需要的记录这样就大大降低了处理时间。
再谈随机数
现在你下定决心要榨干Random 函数的最后一滴油,那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。
为了取出几条随机选择的记录并存放在同一recordset内,你可以存储三个随机数,然后查询数据库获得匹配这些数字的记录:
SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber & ” OR ID = ” & RNumber2 & ” OR ID = ” & RNumber3

假如你想选出10条记录(也许是每次页面装载时的10条链接的列表),你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成,但是 SELECT 语句只显示一种可能(这里的ID 是自动生成的号码):
SQL = “SELECT * FROM Customers WHERE ID BETWEEN ” & RNumber & ” AND ” & RNumber & “+ 9”
注意:以上代码的执行目的不是检查数据库内是否有9条并发记录。

随机读取若干条记录,测试过
Access语法:SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysql:Select * From 表名 Order By rand() Limit n
Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试, 现在记下以备后查)
语法:Select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where …
使用SQL语句 用…代替过长的字符串显示
语法:
SQL数据库:select case when len(field)>10 then left(field,10)+’…’ else field end as news_name,news_id from tablename
Access数据库:SELECT iif(len(field)>2,left(field,2)+’…’,field) FROM tablename;

Conn.Execute说明
Execute方法
该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集,该方法的使用格式分为以下两种:
1.执行SQL查询语句时,将返回查询得到的记录集。用法为:
Set 对象变量名=连接对象.Execute(“SQL 查询语言”)
Execute方法调用后,会自动创建记录集对象,并将查询结果存储在该记录对象中,通过Set方法,将记录集赋给指定的对象保存,以后对象变量就 代表了该记录集对象。
2.执行SQL的操作性语言时,没有记录集的返回。此时用法为:连接对象.Execute “SQL 操作性语句” [, RecordAffected][, Option]·RecordAffected 为可选项,此出可放置一个变量,SQL语句执行后,所生效的记录数会自动保存到该变量中。通过访问该变量,就可知道SQL语句队多少条记录进行了操作。·Option 可选项,该参数的取值通常为adCMDText,它用于告诉ADO,应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数,可使执行 更高效。
·BeginTrans、RollbackTrans、CommitTrans方法
这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物;RollbackTrans用于回滚事 务;CommitTrans用于提交所有的事务处理结果,即确认事务的处理。
事务处理可以将一组操作视为一个整体,只有全部语句都成功执行后,事务处理才算成功;若其中有一个语句执行失败,则整个处理就算失败,并恢复到处里前 的状态。
BeginTrans和CommitTrans用于标记事务的开始和结束,在这两个之间的语句,就是作为事务处理的语句。判断事务处理是否成功,可通 过连接对象的Error集合来实现,若Error集合的成员个数不为0,则说明有错误发生,事务处理失败。Error集合中的每一个Error对象,代表 一个错误信息。

LEAVE A COMMENT

Captcha Code