VFsql查询语言

第六章SQL查询语言的使用学习目标：（1）了解SQL的基本知识（2）掌握SQL的数据查询操作（3）熟悉SQL的数据定义功能（4）熟悉SQL的数据操纵功能6.1SQL基础知识SQL（StructuredQueryLanguage）结构化查询语言，是一种介于关系代数与关系运算之间的语言。SQL语言动词SQL功能动词数据查询select数据定义creat，drop，alter数据操纵insert，update，delete数据控制grant，revoke6.2SQL的数据定义功能一、创建 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 格式1：CREATETABLE<表名>[FREE]([<字段名1>]类型(长度)[,[<字段名2>]类型(长度)……])功能：创建一个以<表名>为表的名字，以指定的字段属性定义数据表。说明：定义表的各个属性时，需要指明其数据类型及长度。例1：创建新表stu2，其结构和学生表相同。creattablestu2(学号C(8)，姓名C(8)，性别C(2)，出生日期D，入校总分N(3，0)，三好生L，特长M，照片G）格式2：CREATETABLE<表名>[FREE]([<字段名1>]类型(长度)[,[<字段名2>]类型(长度)……])[NULL|NOTNULL][CHECK<表达式>[ERROR"提示信息"]][DEFAULT<表达式>]FREE：说明定义的表是自由表。NULL：允许一个字段为空值。如果一个或多个字段允许包含空值，一个表最多可以定义254个字段。NOTNULL：不允许字段为空值，即字段必须取一个具体的值。CHECK<表达式>：定义字段级的有效性规则。<表达式>是逻辑型表达式。ERROR"提示信息"：定义字段的错误信息。当字段中的数据违背了字段的完整性约束条件时，VisualFoxPro就会显示“提示信息”定义的出错信息。DEFAULT<表达式>：定义字段的默认值，<表达式>的数据类型必须和字段类型一致。例2：在数据库xsgl.DBC中，使用命令建立“jsj.DBF”表，表结构包括(学号C(8),姓名C(6),笔试N(5,1),上机N(5,1))，并设置学号为主索引，笔试和上机的有效性规则是>0，如果违反有效性规则，系统提示：“成绩必须大于0”。creatdatabasexsgl.DBCcreattablejsj.DBF(学号C(8)primarykey,姓名C(6),笔试N(5,1)check笔试>0error“成绩必须大于0”,上机N(5,1)check上机>0error“成绩必须大于0”)二、修改表的结构1、增加字段格式：ALTERTABLE<表名>ADD<字段名><字段类型>[(<宽度>[,<小数位数>])]；[NULL|NOTNULL][CHECK<表达式>[ERROR"提示信息"]]；[PRIMARYKEY|UNIQUE]功能：为指定的表的指定字段进行添加指定的字段。例3：为xsdb表添加两个字段：“平均分”字段N(5,1)和“总分”字段N(5,1)。altertablexsdb.dbfadd平均分N(5,1)check平均分>80error“平均分要大于>80!”altertablexsdb.dbfadd总分N(5,1)注意：check对非数据库表（.dbc）不可用。2、修改字段格式：ALTERTABLE<表名>ALTER<字段名>类型(长度)[NULL|NOTNULL][SETCHECK<表达式>[ERROR"提示信息"]]功能：为指定的表的指定字段进行修改指定的字段。例4：在xsdb表中，修改两个字段：“学号”字段C（10）和“英语”字段N（6,1）。altertablexsdb.DBFalter学号C(10)altertablexsdb.DBFalter英语N(6,1)例5：修改或定义上机字段的有效性规则。altertablejsj.dbfalter上机setcheck上机>0error"上机应该大于0!"3、删除字段ALTERTABLE<表名>Drop<字段名1>[Drop<字段名2>……];功能：删除指定的表中指定字段。例6：删除xsdb表中的平均分、奖学金和备注字段。altertablexsdb.dbfdrop平均分drop奖学金drop备注4、修改字段名格式：altertable<表名>rename[column]<字段名1>to<字段名2>功能：将表中<字段名1>的名字修改为<字段名2>。例7：将jsj表的笔试字段名改为笔试成绩。altertablejsj.dbfrenamecolumn笔试to笔试成绩三、删除表格式：DROPTABLE<表名>功能：从数据库和磁盘上将表直接删除掉。例8：删除jsj表droptablejsj.dbf四、视图的定义和删除格式:CREATEVIEW<视图名>[ASSELECT-SQL命令]。功能：按照AS子句中的SELECT-SQL命令提出的查询要求，创建一个本地或远程的SQL视图。视图的名称由命令中<视图名>指定。1、定义视图例9：从“xsgl”数据库所含有的学生和选课两个表中抽取学号、姓名、和课程号3个字段，组成名称为“myview”的SQL视图。opendatabasexsglcreatviewmyviewasselect学生.学号，学生.姓名，选课.课程号from学生.dbf，选课.dbfwhere学生.学号=选课.学号2、查询视图例10：查询“xsgl”数据库中的“myview”视图，要求显示学生姓名和课程号opendatabasexsglselect姓名，课程号frommyview3、删除视图格式：dropview<视图名>功能：删除数据库中指定的视图。例11：删除数据库“xsgl”中的“myview”视图opendatabasexsgldropviewmyview6.3SQL的数据查询功能一、SELECT语句格式SELECT[ALL|DISTINCT][[<别名>.]<选项>[AS<显示列名>]…];FROM<表名>[联接方式JOIN表名][ON联接条件];WHERE<过滤条件>;ORDERBY<排序字段>[ASC/DESC];GROUPBY<分组筛选条件>;HAVING分组筛选条件>;INTO<查询去向>;功能：查询。二、投影查询1、查询部分字段在select语句后列出要查询的字段，之间用逗号隔开。例1：从学生表中查询学号，姓名，性别和出生日期。select学号，姓名，性别，出生日期from学生2、查询全部字段可以在select后面列出全部字段，也可以用星号“*”来表示全部字段。例2：查询教师表中的全部字段select*from教师select教师号，姓名，性别，职称，工资，政府津贴from教师或者3、取消重复 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 使用distinct取消查询结果中的重复记录例3：查询选课表中有成绩记录的学生学号。selectdistinct学号from选课4、查询经过计算的表达式查询的列可以是字段，也可以是计算后的表达式。例4：从教师表中查询教师的姓名，工资和涨50%以后的工资。select姓名，工资，工资*1.5from教师select姓名，工资，工资*1.5as涨后工资from教师或者as用来修改查询结果中指定列的列名。其中as可以省略三、条件查询使用where来指定查询条件，常用的比较运算符如表运算符含义举例=、>、<、>=<=、!=、<>比较大小入校总分>600not、and、or多重条件入校总分>600and性别=“男”betweenand、notbetweenand确定范围入校总分between500and600运算符含义举例in、notin确定集合学号in（“s0803001”，“s0803002”）like、notlike字符匹配姓名like“王%”isnull、notisnull空值查询学号isnull1、简单条件查询例5：从学生表中查询三好生的信息select*from学生where三好生=.t.例6：从学生中查询7月份所生的学生的学号、姓名、性别和出生日期select学号，姓名，性别，出生日期from学生wheremonth（出生日期）=7例7：从学生表中查询入校总分大于600分的女生或者入校总分小于600的男生的学号、姓名和入校总分。select学号，姓名，入校总分from学生where（入校总分>600and性别=“女”）or（入校总分<600and性别=“男”）2、多重条件查询例8：从学生表中查询姓名中含有“张”或“小”的学生的姓名，学号。select学号，姓名from学生where“张”$姓名or“小”$姓名例9：从学生表中查询1990年1月1日以后出生，且入校总分大于等于600或小于等于590分的学生信息。select*from学生where出生日期>{^1990/01/01}and(入校总分>=600or入校总分<=590)3、确定范围例10：从选课表中查询成绩在85分到100分（含85和100分）之间的学生学号、课程号和成绩。select*from选课where成绩between85and100例11：从选课表中查询成绩不在85分到100分之间的学生学号、课程号和成绩。select*from选课where成绩notbetween85and1004、确定集合例12：从学生表中查询学号为”s0803001”、”s0803003”、”s0803004”的学生信息。select*from学生where学号in（”s0803001”，”s0803003”，”s0803004”）例13：从学生表中查询学号不为”s0803001”、”s0803003”、”s0803004”的学生信息。select*from学生where学号notin（”s0803001”，”s0803003”，”s0803004”）5、部分匹配查询%表示任意长度的字符串_表示任意一个字符例14：从学生表中查询所有姓张的同学信息。select*from学生where姓名like“张%”例15：从学生表中查询名字第二个字为“小”的同学信息select*from学生where姓名like“_小%”6、空值查询使用isnull或者isnotnull来查询某个字段的值是否为空值例16：从学生表中查询姓名不为空值的记录。select*from学生where姓名isnotnull四、统计查询函数名称功能avg按列计算平均值sum按列计算值的总和count按列统计个数max求一列中的最大值min求一列中的最小值常用统计函数及其功能注意：sum和avg函数只能对数值型字段进行计算。distinct：取消重复值all：不取消重复值缺省：不取消重复值例17：查询学生中的学生入校总分最高值、最低值和平均值。selectmax（入校总分）as最高分，min（入校总分）as最低分，avg（入校总分）as平均分数from学生例18：统计学生中的三好生个数selectcount（三好生）as三好生人数from学生where三好生=.t.例19：统计选课表中选了课的学生人数。selectcount（distinct学号）as学生人数from选课五、分组查询groupby可以将查询结果按照某个字段值或者多个字段值的组合进行分组。不使用groupby的统计函数是对所有记录进行统计，使用了groupby将按照某个字段的相同值进行统计。例20：统计选课表中每门课程的平均成绩。select课程号，avg（成绩）as平均成绩from选课groupby课程号1、简单分组查询2、限制分组查询使用having可以对分组查询后的结果进行有条件限制的输出。例21：统计选课表中平均分高于80分的课程号。select课程号，avg（成绩）as平均成绩from选课groupby课程号having平均成绩>80六、查询的排序使用orderby可以按字段值排序，asc为升序，desc为降序，默认为升序。1、单列排序例21：查询学生表中的学生信息，并按照入校总分的降序排列。select*from学生orderby入校总分desc例22：统计选课表中每门课程的平均成绩，并按照平均分数的降序排列select课程号，avg（成绩）as平均成绩from选课groupby课程号orderby平均成绩descselect课程号，avg（成绩）as平均成绩from选课groupby课程号orderby2desc或者2表示以查询结果的第2列作为排序依据2、多列排序例23：查询学生表中的学生信息，并按照入校总分的降序排列，若入校总分相同，按照学号的升序排列。select*from学生orderby入校总分desc，学号七、内连接查询连接条件：两表的公共字段值相等，常用书写格式是：表1.公共字段=表2.公共字段例23：从授课表和课程表中，查询各个教师担任的课程的课程号及课程名。select授课.教师号，授课.课程号，课程.课程名from授课，课程where授课.课程号=课程.课程号select授课.教师号，授课.课程号，课程.课程名from授课join课程on授课.课程号=课程.课程号或者例23：学生表和选课表中查询学号为s0803004和s0803005的学生的选课情况，显示其学号、课程号和成绩。select学生.学号，选课.课程号，选课.成绩from学生，选课where学生.学号=选课.学号and（学生.学号=“s08030004”or学生.学号=“s08030005”）select学生.学号，选课.课程号，选课.成绩from学生join选课on学生.学号=选课.学号where（学生.学号=“s08030004”or学生.学号=“s08030005”）或者八、自连接查询将同一个表与其自身进行连接，称为自连接。在自连接查询涉及的字段前面，用别名加以限制。定义表别名的语法是：<表名><别名>例24：查询入校总分大于谢小芳的学生姓名、入校总分。select学生.姓名，学生.入校总分from学生，学生bwhere学生.入校总分>b.入校总分andb.姓名=“谢小芳”九、修改查询去向1、intodbf<表名>intotable<表名>将查询结果保存到以<表名>命名的永久表中，并打开该表作为当前文件2、intocursor<临时表文件名>将查询结果保存到以<临时表文件名>命名的临时表中，该文件为只读表，当关闭查询相关的表文件时，该临时文件会被自动删除。3、intofile<文本文件名>[addtive]将查询结果保存到以<文本文件名>命名的文本文件中，扩展名为txt，如果有addtive，查询结果追加到文本文件的尾部，否则覆盖该文本文件。4、intoarray<数组名>将查询结果保存到<数组名>指定的数组中，一般将存放查询结果的数组作为二维数组来使用。5、toprinter将查询结果输出到打印机。十、嵌套查询1、带比较运算符的嵌套查询例25：查询入校总分大于谢小芳的学生姓名、入校总分。（1）查询谢小芳的入校总分select入校总分from学生where姓名=“谢小芳”此结果的显示为：谢小芳入校总分为610（2）查询入校总分大于610分的学生姓名、入校总分。select姓名，入校总分from学生where入校总分>（select入校总分from学生where姓名=“谢小芳”）（3）将610用第（1）步中的select语句替换select姓名，入校总分from学生where入校总分>6102、in谓词嵌套查询例26：查询有选课记录的学生信息。（1）在选课表中查询选了课的学生学号select(distinct学号)from选课查询结果为“s0803002”，“s0803003”，“s0803004”，“s0803005”（2）查询学号为“s0803002”，“s0803003”，“s0803004”，“s0803005”的学生信息。select*from学生where学号in（“s08030002”，“s08030003”，“s08030004”，“s08030005”）（3）将“s0803002”，“s0803003”，“s0803004”，“s0803005”用第（1）步中的select语句替换select*from学生where学号in（selectdistinct学号from选课）3、exists谓词嵌套查询例27：查询学号为s0803002的学生选修的课程名称select课程名from课程whereexists（select*from选课where选课.课程号=课程.课程号and学号=“s0803002”）例28：查询学号为s0803002的学生没有选修的课程名称select课程名from课程wherenotexists（select*from选课where选课.课程号=课程.课程号and学号=“s0803002”）4、any、all、some谓词嵌套查询例29：查询入校总分大于某个三好生的女生的信息。select*from学生where入校总分>any(select入校总分from学生where三好生=.t.)and性别=“女”例30：查询入校总分大于所有三好生的学生信息。select*from学生where入校总分>all(select入校总分from学生where三好生=.t.)此处的any也可以用some替换十一、集合查询用union可以进行集合查询例31：查询入校总分大于600分或者小于590分的学生姓名。要求使用集合查询。select姓名from学生where入校总分>600unionselect姓名from学生where入校总分<590注意：使用union集合查询时，会自动取消重复记录。6.4SQL的数据操纵功能一、插入数据格式：insertinto<表名>[(<字段名1>[,<字段名2>[,…]])]values(<表达式1>[,<表达式2>[,…]])例1、在课程表中插入一条新记录（“c170”，“操作系统”，48）insertinto课程values（“c170”，“操作系统”，48）二、更新数据格式：update[<数据库名>！]<表名>set<字段名1>=<expr1>[,<字段名2>=<expr2>…]where<条件>功能：用指定的新值更新记录。例2、在课程表中课程号为“c170”的课时数改为32。update课程set课时=32where课程号=“c170”三、删除数据格式：deletefrom<表名>[where<条件>]功能：逻辑删除一条或多条记录说明：没有where子句，表示逻辑删除所有记录。例3、逻辑删除课程表中课程号为“c170”的记录deletefrom课程where课程号=“c170”注意：若要物理删除必须使用pack命令。