关系数据库标准语言SQL(改2).ppt

第五章SQL关系数据库查询语言 5 1SELECT SQL查询 要点 SQL语言 StructueredQueryLanguage 是关系数据库的标准语言 是一种非过程化语言 它的大多数语句都是独立执行的 与上下文无关 它既不是数据库管理系统 也不是应用软件开发语言 只能用于对数据库中数据的操作 SQL语句最主要的功能就是查询功能 SQL是结构化查询语言有四大功能 用SELECT SQL命令直接查询 简单查询 一 SELECT SQL命令格式 格式 基本格式 SELECT AS FROM 功能 根据查询要求 输出查询的结果 注意 使用SQL语句不需要用use打开表 执行SQL语言将自动打开相关表例 select编号 姓名 性别 出生日期fromrcda例 select编号 价格 0 17as增值税from设备清单表Selectavg 价格 0 17as增值税均值from设备清单表 要点 用SELECT SQL命令直接查询 子句格式1 SELECT AS FROM orderby asc desc 说明 orderby子句用于指定查询结果中记录的排列顺序 注意 orderby后的表达式只可以是字段或表示查询结果中列的位置的数值例 select编号 姓名 性别 工资现状fromrcda Orderby性别 工资现状desc功能 将记录按性别升序排列 性别相同时按工资现状降序排列 要点 用SELECT SQL命令简单联接查询 子句格式2 SELECT AS FROM where 说明 where子句用于指定查询条件和联接条件 例 select编号 姓名 性别 工资现状 出生日期 fromrcdaWhere工资现状 5000例 查询大修过的设备的编号 名称和部门名Selectrcda 编号 rcda 姓名 zytc 专业 fromrcda zytcwherercda 编号 zytc 编号 要点 用SELECT SQL命令简单联接查询示例 例 找出成绩小于60的学生姓名以及该生成绩select姓名 成绩from学生情况表 选课成绩表 where 成绩 60 and 学生情况表 学号 选课成绩表 学号 例 找出课程编号为10001的学生姓名以及该生成绩select姓名 成绩from学生情况表 选课成绩表where 课程编号 10001 and 学生情况表 学号 选课成绩表 学号 嵌套查询 在WHERE子句中包含一个形如SELECT FROM WHERE的查询块 此查询块称为嵌套查询或子查询 包含子查询的语句称为父查询或外部查询 嵌套查询可以将一系列简单查询构成复杂查询 增强查询能力 子查询的嵌套层次最多可达到255层 以层层嵌套的方式构造来查询充分体现了SQL 结构化 的特点 嵌套查询在执行时由里向外处理 即先执行子查询再执行父查询 父查询要用到子查询的结果 1 返回一个值的子查询 当子查询的返回值只有一个时 可以使用比较运算符 将父查询和子查询连接起来 例 查询与 陈静 教师职称相同的教师姓名和职称 SELECT姓名 职称 FROM教师 WHERE职称 SELECT职称 FROM教师 WHERE姓名 陈静 2 使用IN 可以使用IN代替 ANY 子句返回的是一个集合 1 哪些城市至少有一个仓库的职工工资为1250SELECT城市FROM仓库WHERE仓库号 IN SELECT仓库号FROM职工WHERE工资 1250 2 查询学号为 S1 学生没有选修的学生的学号 课程号和成绩 SELECT学号 课程号 成绩 FROM选课 WHERE课程号NOTIN select课程号from选课where学号 s1 注意 使用IN相当于集合运算符 2 使用量词和谓词的查询 例1 ANY ALL SOME量词查询的格式 ANY ALL SOME 子查询 A 检索职工的工资大于或等于WH1仓库中任何一名职工工资的仓库号a selectdistinct仓库号from职工where工资 any select工资from职工where仓库号 WH1 或b selectdistinct仓库号from职工where工资 selectMIN 工资 from职工where仓库号 WH1 B 检索职工的工资大于或等于WH1仓库中所有职工工资的仓库号a selectdistinct仓库号from职工where工资 all select工资from职工where仓库号 WH1 或b selectdistinct仓库号from职工where工资 selectmax 工资 from职工where仓库号 WH1 4 使用EXISTS EXISTS用于判断子查询结果是否存在 带有EXISTS的子查询不返回任何实际数据 它只得到逻辑值 真 或 假 当子查询的查询结果集合为非空时 外层的WHERE子句返回真值 否则返回假值 NOTEXISTS与此相反 含有IN的查询通常可用EXISTS表示 但反过来不一定 NOT EXISTS 子查询 查询仓库表中至少有一个职工的仓库的信息 SELECT FROM仓库WHEREEXISTS SELECT FROM职工WHERE仓库号 仓库 仓库号 或select from仓库where仓库号in select仓库号from职工 A 检索那些仓库中还没有职工的仓库的信息SELECT FROM仓库WHERENOTEXISTS SELECT FROM职工WHERE仓库号 仓库 仓库号 或select from仓库where仓库号notin select仓库号from职工 特殊运算符查询 BETWEEN AND 意为 和 之间 例 查询成绩在80分到90分之间的选课信息 SELECT FROM选课WHERE成绩BETWEEN80AND90等价于 SELECT FROM选课WHERE成绩 80AND成绩 90例 查询成绩不在80分到90分之间的选课信息 SELECT FROM选课WHERE成绩NOTBETWEEN80AND90 特殊运算符查询LIKE 前面查询均属于完全匹配查询 当不知道完全精确的値时 用户还可以使用LIKE或NOTLIKE进行部分匹配查询 也称模糊查询 LIKE定义的一般格式为 LIKE属性名必须为字符型 字符串常量的字符可以包含如下两个特殊符号 表示任意长度的字符串 0个或多个 表示任意一个字符 在有的DBMS系统中 表示一个汉字需要两个下画线 而另一些系统则只需一个下画线 在例7 19中 表示一个汉字只用了一个下画线 示例 例1 查询所有姓 张 的学生的学号和姓名 SELECT学号 姓名 FROM学生 WHERE姓名LIKE 张 例2 从学生关系中查询不姓 黄 的学生信息 SELECT FROM学生WHERE姓名NOTLIKE 黄 排序 SQLSELECT可将查询结果排序 排序的短语格式如下 ORDERBYOrder Item ASC DESC Order Item ASC DESC 说明 可按升序或降序排序 允许按一列或多列排序 排序示例 例1 按学分进行升序查询课程信息 SELECT FROM课程ORDERBY学分ASC例2 先按学分进行升序排序 学分相同的再按课程名进行降序排序并输出全部课程信息 SELECT FROM课程ORDERBY学分 课程名DESC注意 ORDERBY对最终的查询结果进行排序 不能在子查询中使用此短语 分组与计算查询 GROUPBY短语格式如下 GROUPBYGroupColumn GroupColumn HAVINGFilterCondition 说明 可按一列或多列分组 还可以用HAVING进一步限定分组的条件 GROUPBY子句一般跟在WHERE子句之后 没有WHERE子句时 跟在FROM子句之后 HAVING子句必须跟在GROUPBY之后 不能单独使用 在查询中是先用WHERE子句限定元组 然后进行分组 最后再用HAVING子句限定分组 示例 例 求每个学生选课的考试成绩平均分 SELECT学号 AVG 成绩 FROM选课GROUPBY学号例 求平均成绩在80分以上的各课程的课程号与平均成绩 SELECT课程号 AVG 成绩 FROM选课 GROUPBY课程号HAVINGAVG 成绩 80 使用空值进行查询 空值的概念 空值就是缺值或还没有确定值 不能把它理解为任何意义的数据 比如表示价格的一个字段值 空值表示没有定价 而数值0可能表示免费 空值与空 或空白 字符串 数值0等具有不同的含义 空值查询示例 1 例 假设在选课中有些学生某门课程还没有考试 则成绩为空 试找出尚未考试的选课信息 SELECT FROM选课WHERE成绩ISNULL2 例 试找出成绩不为空的选课信息 SELECT FROM选课WHERE成绩ISNOTNULL 要点 子句格式3 JOIN子句 用于实现两表的联接 格式1 SELECT AS FROM INNER LEFT RIGHT FULL JOINON 功能 按on子句规定的联接条件 将JOIN左右两边的表进行联接 例 Selectrcda 编号 rcda 姓名 zytc 专业 fromrcdajoinzytconrcda 编号 zytc 编号 超联接查询 Innerjoin等价于join 要点 联接查询 多表查询示例例1方法1SELECTrcda 编号 rcda 姓名 rcda 工资现状 cjda 成果名称FROMrcdaINNERJOINcjda ONrcda 编号 cjda 编号方法2SELECTrcda 编号 rcda 姓名 rcda 工资现状 cjda 成果名称FROMrcda cjda WHERErcda 编号 cjda 编号 SELECT SQL命令使用举例 要点 中可用系统函数及功能如下 AVG 求其平均值COUNT 统计记录个数MIN 求其最小值MAX 求其最大值SUM 求其和见P154例 selectcount 编号 sum 工资现状 max 工资现状 min 工资现状 fromrcda注意 以上函数只能用于select语句中 不能单独使用 以上函数中的参数为字段名或字段名组成的表达式 简单的计算查询 用SELECT SQL命令直接查询 子句格式4 SELECT AS FROM groupby 说明 groupby子句用于分组统计 对记录按值进行分组 例 select性别 avg 工资现状 fromrcdagroupby性别例 求出每一设备的增值金额 Select编号 sum 增值 from增值表groupby编号例 求出各部门的设备总价Select部门 sum 价格 from设备清单表groupby部门 要点 用SELECT SQL命令直接查询 子句格式5 SELECT AS FROM groupby having 说明 having子句的作用如同where子句 但是当含有groupby子句时 只可用having子句用作记录查询的限制条件 不可用where子句设置查询条件 例 求出设备总价超过20000元的部门Select部门 sum 价格 from设备清单表 groupby部门havingsum 价格 20000 要点 集合的并运算 SQL支持集合的并 UNION 运算 即可以将两个SELECT语句的查询结果通过并运算合并成一个查询结果 注意 要求这样的两个查询结果具有相同的字段个数 并且对应字段的值要出自同一个值域 即具有相同的数据类型和取值范围 例1 如下语句的结果是城市为北京和上海的仓库信息select from仓库where城市 北京 union select from仓库where城市 上海 例2 如下语句的结果是paymentamount为1000 0000和2000 0000的payments信息select frompaymentswhere paymentamount 1000 0000 union select frompaymentswhere paymentamount 2000 0000 VisualFoxPro中SQLSELECT的几个特殊选项 1 将是显示部分结果格式 TOPnExpr PERCENT 参数 nExpr是数字表达式 当不使用PERCENT时 是1 32767间的整数 使用PERCENT时是0 01 99 99间的实数注意 TOP短语要与ORDERBY短语同时使用 示例 例1 显示工资最高的三位职工的信息select top3from职工orderby工资desc例2 显示工资最低的那30 职工的信息select top30percentfrom职工orderby工资例3 显示工资最高的3个职工的职工号 姓名 工资相关信息selecttop3职工号 姓名 工资from职工orderby工资desc例4 显示paymentamount最低的30 的相关信息select top30percentfrompaymentsorderbypaymentamount 查询结果输出 SELECT FROM WHERE使用INTO子句 可以将查询结果输出到一个新建的数据表或一个临时表 临时表只存储在内存中 关机自动消失 1 INTOTABLE 表示输出到数据表 2 INTOCURSOR 表示输出到临时表 说明 临时表中一个只读的DBF文件 当查询结束后该临时文件是当前文件 可像一般的DBF文件一样使用 当关闭文件时该文件将自动删除 查询结果输出 3 将结果存放在数组中格式 INTOARRAYArrayName4 将结果存放到文本文件中格式 TOFILEFileName ADDITIVE 5 将结果直接输出到打印机格式 TOPRINTER PROMPT 1 表中插入记录格式 INSERTINTO表名 字段名1 字段名2 VALUES 表达式1 表达式2 FROMARRAYArrayName 说明从指定的数组中插入记录值FROMMEMVAR 说明根据同名的内存变量来插入记录值 如果同名的变量不存在 那么相应的字段为默认值或空值 功能 在表尾追加一条新记录 并输入相应的数据说明 1 表不必事先打开 字段与表达式的类型必须相同2 若字段名缺省 就须按表结构字段顺序填写表达式 数据操作功能 插入记录示例 例 向课程表中插入记录 05 数理逻辑 6 03 插入完整的一条记录时可以省略对应的字段名 INSERTINTO课程VALUES 05 数理逻辑 6 03 例 向学生表中有选择的插入字段值 不能省略插入字段值的字段名 insertinto学生 学号 姓名 性别 values x8 亡命 男 注意 当一个表定义了主索引或侯选索引后 由于相应的字段具有关键字的特性 即不能为空 所以只能用此命令插入记录 2 更新 命令格式 UPDATETableNameSET字段名1 表达式1 字段名2 表达式2 WHERECondition说明 类似于REPLACE命令 通常嵌入在循环结构内 用另一个表的数据来修改本表 例 将课程关系中课程号为 01 的课程的学分加1分 UPDATE课程SET学分 学分 1WHERE课程号 01 或REPLACEALL学分WITH学分 1FOR课程号 01 3 删除 命令格式 DELETEFROMTableName WHERECondition 说明 类似于VFP的DELE FOR 命令 若无WHERE子句会删除表中的全部记录 此命令为逻辑删除 用PACK命令进行物理删除 例 删除课程关系中课程号为 05 的元组 DELETEFROM课程WHERE课程号 05 要点 1 创建表 命令格式 CREATETABLE数据表名 字段名1字段类型 字段宽度 小数位数 NULL NOTNULL CHECK ERROR DEFAULT PRIMARYKEY UNIQUE 字段名2 数据定义功能 建立数据库表示例 例 用SQLCREATE命令建立学生2数据表CREATETABLE学生2 学号C 2 NOTNULLPRIMARYKEY 姓名C 8 性别C 2 CHECK性别 男 OR性别 女 ERROR 性别只能是男或女 DEFAULT 男 生日D 格式1 Altertable表名alter字段名1字段类型 字段宽度 alter字段名2字段类型 字段宽度 功能 可以重新指定字段类型 宽度 小数位数格式2 Altertable表名add字段名字段类型 字段宽度 功能 对表结构添加字段 格式3 Altertable表名drop字段名功能 对表结构删除字段 格式4 Altertable表名rename字段名1to字段名2功能 将字段名1更改为字段名2 2 修改表结构 命令格式5 ALTERTABLE表名ALTER COLUMN 字段名1 NULL NOTNULL SETDEFAULT SETCHECK ERROR DROPDEFAULT DROPCHECK 功能说明 此格式主要用于定义 修改和删除字段一级的有效性规则和默认值定义 例 为课程关系增加学分大于2的有效性规则ALTERTABLE课程ALTER学分SETCHECK学分 2 视图操作 1 定义视图