VF中SQL语句大全.docx

VFP的SQL命令和语句大全1.CREATE?TABLE一般格式为：CREATE?TABLE?|dbf?表文件名（字段名1?字段类型[（字段宽度[，小数位数]）][，字段名2?字段类型[（字段宽度[，小数位数]）]]??）例：CREATE?TABLE?xscj(xh?c(8),xm?c(6),xb?c(2),;cj?n(5,1),ksrq?d)2.ALTER?TABLE命令格式：添加字段ALTER?TABLE?xscj?ADD?COLUMN?kcmc?C(14)重命名字段?ALTER?TABLE?xscj?RENAME?COLUMN?kcmc?TO?kc删除字段?ALTER?TABLE?xscj?DROP?COLUMN?kc3.INSERT一般格式为：INSERT?INTO?表名[（字段名1[，字段名2??]）]?VALUES?（表达式1[，表达式2??]）?例：INSERT?INTO?xscj（xh，xm，cj）?VALUES??张良,?85.5）4.UPDATE命令格式：?UPDATE?表名?SET?字段1?=?表达式1?[，字段2?=?表达式2??]；WHERE?条件表达式例：?UPDATE?xscj?SET?cj?=?cj*1.05?WHERE?cj805.DELETE命令格式：?DELETE?FROM?表名?[WHERE?条件表达式]例：DELETE?FROM?xscj?WHERE?cj606.SELECT-SQL命令创建查询常用的格式：SELECT?表名1.?字段名1?[AS?标题名1],?表名1.?字段名2?[AS?标题名2],??FROM?数据库名!?表名1?[，数据库名!?表名2?]?[,?数据库名!?表名3?][TO?FILE?文本文件名?|?into?table?|?into?cursor?表文件名?][?WHERE?选定条件][GROUP?BY?分组字段名?][HAVING?分组中的满足条件?][ORDER?BY?排序字段名1?[ASC?|?DESC]?[,?排序字段名2?[ASC?|?DESC]?...]]示例：浏览学生中的查询学生?student.qpr?:SELECT?表xs.xh?as?学号,?表xs.xm?as?姓名,?表xs.xb?as?性别,?;表xs.csrq?as?出生日期,?表xs.bj?as?班级名称;FROM?数据test!表xs;WHERE?表xs.bj?=cs1;ORDER?BY?表xs.xh;INTO?TABLE?xs浏览成绩中的按课程查询?sub_cj.qpr?:SELECT?表xs.bj?as?班级名称,?COUNT(表xs.xh)?as?班级人数,?AVG(表cj.cj)?as?平均分,?;?MAX(表cj.cj)?as?最高分,?MIN(表cj.cj)?as?最低分;FROM?数据test!表xs,?数据test!表cj,?数据test!表kc;WHERE?表cj.xh?=?表xs.xh;AND?表kc.kcm?=?表cj.kcm;AND?表cj.kcm?=?sc1;GROUP?BY?表xs.bj;ORDER?BY?表xs.bj;INTO?TABLE?sub_cj7.CREATE?SQL?VIEW?命令创建视图打开数据库，用命令来创建视图：OPEN?DATABASE?数据库名CREATE?SQL?VIEW?视图文件名?AS?SQL-SELECT?语句例:?OPEN?DATABASE?SJCREAT?SQL?VIEW?SCORE?AS?SELECT?SJCJ.XH,?SJCJ.CJ?;FROM?SJ!SJCJ?WHERE?SJCJ.KCH=”计算机基础”8.CREATE?TABLE-SQL?命令创建数据库表命令一般格式：Create?table?表名1?[free]?(?字段名1?字段类型代号?[?(字段宽度[,小数位数?]?)?][null?|?not?null]?[check?字段规则1?[error?“信息1”?]?]?[default?默认值][primary?key?|?unique]?[references?表名2?[tag?索引标识1?]?][?,?字段名2?字段类型代号?[?(字段宽度?[,?小数位数])?]?[null?|?not?null]?][check?字段规则2?[error?“信息2”?]?]?[default?默认值?]?)?|?from?array?数组名?使用示例：creat?data?xscjgl??创建学生成绩管理数据库create?table?xs?(;xh?c(4)?default?1201?primary?key,;xm?c(6),?;xb?c(2)?check?xb=男?or?xb=女?error性别只