实验3 SQL基本查询与SQL Plus的使用

第 1 页 共 19 页 实验实验 3 SQL 基本查询与基本查询与 SQL Plus 的使用的使用 实验目的与要求实验目的与要求 1 熟悉 Oracle SQL Plus 的使用 2 掌握 SQL 基本查询 实验内容与步骤实验内容与步骤 3 1 启动启动 SQL Plus 要调用 SQL Plus 需依次完成如下步骤 单击单击 Start 开始开始 按钮 按钮 指向指向 Programs 程序程序 组图标 组图标 选择选择 Oracle oracle10g 图图 3 1 选择选择 Application Development 应用程序开发应用程序开发 选项 选项 单击单击 SQL Plus 启动 SQL 之后 将会出现图 3 2 所示的屏幕 填写相应的详细资料 按 Tab 键转到下一个选项 安装 Oracle 时创建的默认用户为 Scott 密码为 tiger 主机字 符串是数据库名称 TestDB 对本机而言 也可空着 若只是希望启动 SQL Plus 不想 以任何用户身份登录 则可在用户名处输入 nolog 登录 第 2 页 共 19 页 图图 3 2 a 单击 OK 确定 完成登录 将会出现如下图所示的屏幕 显示产品信息 图图 3 3 SQL 是 SQL 提示符 在此提示符下提供要执行的 SQL 命令和语句 下面给出可通过 scott tiger 登录访问的默认表及其列名 在本实验中 我们将使 用这些表 EMPDEPTSALGRADE 第 3 页 共 19 页 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC GRADE LOSAL HISAL 3 2从表中查看数据从表中查看数据 表中的数据可通过使用 SELECT 语句进行查看 3 2 1查看所有列查看所有列 语法如下 SELECT FROM tablename 其中 表示将显示所有列 实验实验 3 1 部门表中查看所有记录和所有字段值 请执行如下步骤 在在 SQL 提示符下键入提示符下键入 Select from dept 从部门选择 从部门选择 按按 Enter 将得到图 3 4 中显示的结果 图图 3 4 3 2 2查看选择列查看选择列 要查看选择列 请输入列名 并用逗点而不是 隔开 第 4 页 共 19 页 语法如下 SELECT columnname1 columnname2 columnnameN FROM tablename 实验实验 3 2 只显示部门表中的部门名和位置 执行如下所示的语句 图图 3 5 请给出运行结果 请给出运行结果 某些列标题由于其列大小限制而使用缩写词 要将字符列的显示宽度设置为 12 请给出如下语句 COLUMN columnname FORMAT A12 COLUMN 语句不能以分号结尾 因为它是格式化命令 语句不能以分号结尾 因为它是格式化命令 通过 COLUMN 命令可以使用的选项包括 选项选项功能功能 FORMAT指定数据格式 HEADING texttext 为列标题 JUSTIFY L R C将标题对齐为 LEFT RIGHT CENTER WRAP TRUNC在列中回行或截断数据 通过 FORMAT 选项可用的掩码包括 掩码掩码功能功能 Ann 个字母数字字符 99 9 不含前导零的数字 第 5 页 共 19 页 00 含前导零的数字 显示的小数点 V隐藏的小数点 先导 MI数字后面是减号 PR负值包括在 之内 B显示空白而不是零 要显示包含前导零的薪金 请在 SQL 提示符下给出如下命令 SQL column sal format 009999 实验实验 3 3 设置格式之后 执行如下所示的命令 图图 3 5 请给出运行结果 请给出运行结果 这样 所有雇员的薪金就以包含前导零的格式显示 要删除此格式 请在提示符 下输入如下给定命令 SQL column sal format 999999 第 6 页 共 19 页 3 2 3查看符合条件的行查看符合条件的行 可以使用 WHERE 子句完成对某些相关数据行的条件检索 WHERE 子句中指 定的条件称为谓词 谓词可以是任意数据类型 字符 数字或日期 像 AND OR 和 NOT 这样的逻辑运算符也可以用于合并谓词 语法如下 SELECT columnname1 columnname2 columnnameN FROM tablename WHERE 实验实验 3 4 要从雇员表中只显示那些在部门 20 工作的雇员 执行如下所示的语句 图图 3 6 请给出运行结果 请给出运行结果 在谓词中可以有效使用的其他运算符还有 1 IN 运算符 运算符 此运算符用于显式规定可以接受的值组 实验实验 3 5 要只显示属于 CLERK 办事员 或 MANAGER 经理 的雇 员 执行执行如下命令 图图 3 7 第 7 页 共 19 页 请给出运行结果 请给出运行结果 另一方面 NOT IN 运算符将返回与条件不匹配的行 2 BETWEEN 运算符运算符 此运算符与 IN 运算符相似 不过可以指定值的范围而不是 匹配的值组 实验实验 3 6 要只显示在 03 JAN 82 1982 年 1 月 1 日 和 33 DEC 87 1987 年 12 月 31 日 之间加入的雇员 请给出如下命令 图图 3 8 请给出运行结果 请给出运行结果 第 8 页 共 19 页 NOT BETWEEN 运算符将获取与条件不匹配的行 3 LIKE 运算符 运算符 该运算符用于匹配字符串或字符串的一部分 称为子字符串 该运算符使用通配符扩展其字符串匹配功能 有两种通配符与 LIKE 运算符一起 使用 下划线下划线 这表示任意单字符 百分号百分号 这表示多字符的序列 实验实验 3 7 要显示姓名以 J 开头的雇员 请给出如下命令 图图 3 9 请给出运行结果 请给出运行结果 第 9 页 共 19 页 3 2 4查看汇总数据 使用统计函数 查看汇总数据 使用统计函数 GROUP BY 子句根据字段值对行进行分组 该子句在应用时将与聚合函数联合 有时 也要求对已分组的查询进行条件检索 可以使用 HAVING 子句对已分组查 询进行条件检索 HAVING 子句与 WHERE 子句相似 只不过 WHERE 子 句用于行 而 HAVING 子句用于已分组的结果 语法如下 SELECT columnname1 aggregate function columnname2 FROM tablename GROUP BY columnname1 实验实验 3 8 要从雇员表中显示付给经理的最高薪金 执行如下所示的语句 图图 3 10 请给出运行结果 请给出运行结果 第 10 页 共 19 页 聚合函数通常与 Group by 和 Having 子句一起使用 要讨论的聚合函数是 1 Count 此函数对包括 Null 值的查询所返回的行数进行记数 实验实验 3 9 要查找每个工作类别的人数 执行执行如下所示的语句 图图 3 11 请给出运行结果 请给出运行结果 2 Max 从查询返回的值中返回最大值 3 Min 从查询返回的值中返回最小值 4 Avg 用来确定某个列中出现的值的平均数 5 Sum 用来获取查询输出的行的算术和 实验实验 3 10 要从雇员表中显示付给经理的最高 最低 平均和总薪金 执行如下 所示的语句 图图 3 12 请给出运行结果 请给出运行结果 第 11 页 共 19 页 3 2 5以指定顺序查看数据以指定顺序查看数据 ORDER BY 子句可帮助按照希望出现的顺序检索查询 可以通过指定 DESC 参 数或 ASC 参数来按升序顺序或降序顺序进行排序 如果没有指定任何参数 则默认 为升序排序 还可对多列进行排序 语法如下 SELECT columnname1 columnname2 columnnameN FROM tablename WHERE ORDER BY columnname1 columnname2 columnnameN 实验实验 3 11 要以雇员的工作的顺序显示收入大于或等于 3000 的雇员 执行如下 所示的语句 图图 3 13 请给出运行结果 请给出运行结果 第 12 页 共 19 页 3 2 6使用计算列查看数据使用计算列查看数据 除了从数据库表中直接提取的列外 SQL 还可以用于计算多个列中的数据的值 可在查询中使用 SQL 表达式执行所有类型的算术操作 唯一必须注意的是 这些表 达式涉及的列应该只属于数字数据类型 语法如下 SELECT columnname1 columnname2 columnname3 FROM tablename 实验实验 3 12 要从雇员表中显示雇员总薪金 执行执行如下语句 图图 3 14 请给出运行结果 请给出运行结果 注意 注意 在上面的命令中 NVL 函数用来将空值转换为数字值 我们在此将其转 换为 0 我们必须按任意表达式执行此操作 表达式中包含空值 因为操作数返回 第 13 页 共 19 页 NULL 因此 为避免在任何操作中出现这种错误 我们使用 NVL 函数 3 3 退出退出 SQL 在 SQL 提示符下键入 Exit 退出 或执行如下步骤 从从 Main Menu 主菜单主菜单 中选择 中选择 File 文件文件 选项 选项 单击单击 Exit 退出退出 您也可以在命令提示符下输入您也可以在命令提示符下输入 Exit 3 4 实验练习 实验练习 在在 scott 用户模式下 完成下查询需求 给出相关查询语句和运行结果 用户模式下 完成下查询需求 给出相关查询语句和运行结果 1 列出各种类别 SALES 工作的雇员的姓名 假定不知道销售部的部门编号 2 列出薪金高于公司平均水平的所有雇员 3 列出与 SCOTT 从事相同工作的所有雇员 第 14 页 共 19 页 4 列出分配有雇员数量的所有部门的详细信息 即使是分配有 0 个雇员 5 列出各种类别工作的最低工资 6 列出各个部门 MANAGER 的最低薪金 7 列出按计算的字段排序的所有雇员的年薪 8 选择部门 30 中的雇员 第 15 页 共 19 页 9 列出所有办事员的姓名 编号和部门 10 找出佣金高于薪金的雇员 11 找出佣金高于薪金 60 的雇员 12 找出部门 10 中所有经理和部门 20 中所有办事员的详细资料 第 16 页 共 19 页 13 找出部门 10 中所有经理 部门 20 中所有办事员以及既不是经理又不是办事员 但其薪金大于或等于 2000 的所有雇员的详细