VF第6章sql查询语言.ppt

第六章SQL查询语言的使用 学习目标 1 了解SQL的基本知识 2 掌握SQL的数据查询操作 3 熟悉SQL的数据定义功能 4 熟悉SQL的数据操纵功能 6 1SQL基础知识 SQL StructuredQueryLanguage 结构化查询语言 是一种介于关系代数与关系运算之间的语言 SQL语言动词 6 2SQL的数据定义功能 一 创建表 格式1 CREATETABLE FREE 类型 长度 类型 长度 功能 创建一个以为表的名字 以指定的字段属性定义数据表 说明 定义表的各个属性时 需要指明其数据类型及长度 例1 创建新表stu2 其结构和学生表相同 creattablestu2 学号C 8 姓名C 8 性别C 2 出生日期D 入校总分N 3 0 三好生L 特长M 照片G 格式2 CREATETABLE FREE 类型 长度 类型 长度 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 DBC creattablejsj DBF 学号C 8 primarykey 姓名C 6 笔试N 5 1 check笔试 0error 成绩必须大于0 上机N 5 1 check上机 0error 成绩必须大于0 二 修改表的结构 1 增加字段 格式 ALTERTABLEADD 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 修改字段 格式 ALTERTABLEALTER类型 长度 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 删除字段 ALTERTABLEDrop Drop 功能 删除指定的表中指定字段 例6 删除xsdb表中的平均分 奖学金和备注字段 altertablexsdb dbfdrop平均分drop奖学金drop备注 4 修改字段名 格式 altertablerename column to 功能 将表中的名字修改为 例7 将jsj表的笔试字段名改为笔试成绩 altertablejsj dbfrenamecolumn笔试to笔试成绩 三 删除表 格式 DROPTABLE 功能 从数据库和磁盘上将表直接删除掉 例8 删除jsj表 droptablejsj dbf 四 视图的定义和删除 格式 CREATEVIEW ASSELECT SQL命令 功能 按照AS子句中的SELECT SQL命令提出的查询要求 创建一个本地或远程的SQL视图 视图的名称由命令中指定 1 定义视图 例9 从 xsgl 数据库所含有的学生和选课两个表中抽取学号 姓名 和课程号3个字段 组成名称为 myview 的SQL视图 opendatabasexsgl creatviewmyviewasselect学生 学号 学生 姓名 选课 课程号from学生 dbf 选课 dbfwhere学生 学号 选课 学号 2 查询视图 例10 查询 xsgl 数据库中的 myview 视图 要求显示学生姓名和课程号 opendatabasexsgl select姓名 课程号frommyview 3 删除视图 格式 dropview 功能 删除数据库中指定的视图 例11 删除数据库 xsgl 中的 myview 视图 opendatabasexsgl dropviewmyview 6 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来指定查询条件 常用的比较运算符如表 1 简单条件查询 例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成绩notbetween85and100 4 确定集合 例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 四 统计查询 常用统计函数及其功能 注意 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平均成绩desc select课程号 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 intodbfintotable将查询结果保存到以命名的永久表中 并打开该表作为当前文件 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入校总分 610 2 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学号