开发手册 欢迎您!
软件开发者资料库

数据限制和排序

数据限制和排序 - Oracle数据库11g管理员认证助理,OPN认证专家考试的准备指南。本指南可帮助您逐步在线准备证书考试,并且完全免费。此认证旨在使您获得赢得基于Oracle SQL的项目的简短列表。 Oracle技术认证是一种有价值的,行业认可的凭证,表明已经证明的知识水平和技能。

SELECT语句的基本功能是选择,投影和连接.从表中显示特定列称为项目操作.我们现在将专注于显示特定的输出行.这称为选择操作.可以通过向SELECT查询添加WHERE子句来选择特定行.事实上,WHERE子句出现在SELECT查询层次结构中的FROM子句之后.必须在所有方案中维护序列.如果违反,Oracle会引发异常.

语法:

  SELECT * | {[DISTINCT]列| expression [alias],..}  FROM table  [WHERE condition(s)]

在语法中,

  • WHERE子句是关键字

  • [条件]包含列名,表达式,常量,文字和比较运算符.

假设您的经理正在为您的组织制定季度预算.作为此活动的一部分,有必要列出每位员工的基本信息,但仅限于每年至少支付25,000美元的员工.下面的SQL查询完成了此任务.请注意以粗体文本显示的WHERE子句的使用.

SELECT Employee_ID, Last_Name, First_Name, SalaryFROM employeesWHERE Salary >= 25000;  EMPLOYEE_ID  LAST_NAME        FIRST_NAME       SALARY----------   ---------------  ---------------  -----------88303        Jones            Quincey          $30,550.0088404        Barlow           William          $27,500.0088505        Smith            Susan            $32,500.003 rows selected

需要注意的要点 -

  • SELECT子句只能包含一个WHERE子句.但是,可以使用AND或OR运算符将多个过滤条件附加到WHERE子句.

  • 谓词子句中的列,文字或表达式必须相似或者可互换的数据类型.

  • 列别名不能在WHERE子句中使用.

  • 字符文字必须用单引号括起来并且区分大小写.

  • 日期文字必须用单引号括起来并且格式敏感.默认格式为 DD-MON-RR .

比较运算符

比较运算符用于谓词中,以将一个术语或操作数与另一个术语进行比较. SQL提供了一整套平等,不等式和其他运算符.可以根据SELECT查询中的数据和过滤条件逻辑使用它们.在WHERE子句中使用比较运算符时,运算符两侧的参数(要比较的对象或值)必须是列名或特定值.如果使用特定值,则该值必须是数值或文字字符串.如果值是字符串或日期,则必须在单引号('')中输入值.

Oracle有九个比较运算符用于相等或不等式条件.

Operator  Meaning=         equal to <         less than>         greater than>=        greater than or equal to<=        less than or equal to!=        not equal to<>        not equal to

其他Oracle运营商是BETWEEN..AND, IN,LIKE和IS为NULL.

BETWEEN运算符

BETWEEN运算符可用于比较一定范围内的列值.指定范围必须具有下限和上限,在比较期间两者都包括在内.它的用法类似于复合不等式算子(

例如,SELECT查询中的WHERE条件 SALARY BETWEEN 1500 AND 2500 将列出这些员工其工资在1500到2500之间.

IN运算符

IN运算符用于测试给定值集中的列值.如果列可以等于给定集合中的任何值,则验证条件.使用IN运算符定义的条件也称为成员条件.

例如,WHERE条件 SALARY IN(1500,3000,2500) SELECT查询将限制salary为1500,3000或2500的行.

LIKE运算符

LIKE运算符用于模式匹配和SELECT查询中的通配符搜索.如果列值的一部分未知,则可以使用通配符替换未知部分.它使用通配符运算符来构建搜索字符串,因此搜索称为通配符搜索.这两个运算符是Percentile('%')和Underscore('_').下划线('_')替换单个字符,而百分位数('%')替换多个字符.它们也可以组合使用.

例如,下面的SELECT查询列出了姓氏以"SA"开头的员工的名字.

SELECT first_nameFROM employeesWHERE last_name LIKE 'SA%';

IS(NOT)NULL条件

需要注意的是,无法使用相等运算符测试NULL值.这是因为NULL值是未知的且未分配,而相等运算符测试一个确定的值. IS NULL运算符用作相等运算符来检查列的NULL值.

例如,SELECT查询中的WHERE条件 COMMISSION_PCT IS NULL 将列出没有佣金百分比的员工.

逻辑运算符

可以将多个过滤条件添加到WHERE子句谓词中.可以使用逻辑运算符AND,OR和NOT将多个条件组合在一起.

  • AND:加入两个或更多条件,并且仅在所有条件都为真时返回结果.

  • OR:加入两个或多个条件,并在任何条件为真时返回结果.

  • NOT:否定后面的表达式.

AND运算符链接WHERE子句中的两个或多个条件,并且仅当所有条件都为真时才返回TRUE.假设经理需要一份女性员工名单.此外,该列表应仅包括姓氏以字母"E"开头或字母表后面的员工.此外,结果表应按员工姓氏排序.有两个简单的条件需要满足. WHERE子句可以写成:WHERE Gender ='F'AND last_name>'E'.

SELECT last_name "Last Name", first_name "First Name", Gender "Gender"FROM employeesWHERE Gender = 'F' AND last_name > 'E'ORDER BY last_name;

OR运算符链接WHERE子句中的多个条件,如果任一条件返回true,则返回TRUE.假设您的组织经理的要求发生了一些变化.需要另一名员工名单,但在此列表中,员工应:(1)为女性或(2)姓氏以字母"T"开头或字母后面的字母.结果表应按员工姓氏排序.在这种情况下,可以满足两个条件中的任何一个以满足查询.女性员工应与名称满足第二个条件的员工一起列出.

NOT运算符用于否定表达式或conition.

ORDER BY子句

当您只显示几行数据时,可能无需对输出进行排序;但是,当您显示多行时,可以通过对信息进行排序来帮助管理人员做出决策.可以使用可选的ORDER BY子句对SELECT语句的输出进行排序.使用ORDER BY子句时,要排序的列名也必须是SELECT子句中指定的列名.

以下SQL查询使用ORDER BY子句按last_name列按升序对结果表进行排序.升序是默认的排序顺序.

SELECT last_name, first_nameFROM employeesWHERE last_name >= 'J'ORDER BY last_name;last_name        first_name---------------  ---------------Jones            QuinceyKlepper          RobertQuattromani      ToniSchultheis       Robert

排序也可以基于数值和日期值.排序也可以基于多个列进行.

默认情况下,ORDER BY子句将按升序对结果表中的输出行进行排序.我们可以使用关键字DESC(降序的缩写)来启用降序排序.替代默认值是ASC,它按升序排序,但ASC关键字很少使用,因为它是默认值.使用ASC或DESC可选关键字时,它必须遵循您在WHERE子句中排序的列名.

位置排序  - 所选列列表中列的数字位置可以在ORDER BY子句中给出,而不是列名.它主要用于UNION查询(稍后讨论).查询按工资对结果进行排序,因为它在列列表中显示为第二位.

 SELECT first_name,salary  FROM employees  ORDER BY 2;

替换变量

当对于不同的输入集必须多次执行SQL查询时,替换变量可以使用.替换变量可用于在查询执行之前提示用户输入.它们广泛用于基于查询的报告生成,其将来自用户的数据范围作为条件过滤和数据显示的输入.替换变量以单&符号(&)为前缀,以临时存储值.例如,

 SELECT EMPLOYEE_ID,LAST_NAME,SALARY  FROM employees  WHERE LAST_NAME =&last_name  OR EMPLOYEE_ID =&EMPNO;

执行上述SELECT查询时,oracle将'&'标识为替换变量.它会提示用户输入'last_name'和'EMPNO'的值,如下所示.

Enter value for last_name:Enter value for empno:

一旦用户为这两个变量提供输入,就会替换值,验证并执行查询.

需要注意的要点 -

  • 如果变量用于替换字符或日期值,则需要将文字括在单引号中.一种有用的技术是在处理字符和日期值时将&符号替换变量括在单引号中.

  • SQL Developer和SQL * Plus都支持替换变量和DEFINE/UNDEFINE命令.虽然SQL Developer或SQL * Plus不支持用户输入的验证检查(数据类型除外).

  • 您不仅可以在替换变量中使用替换变量SQL语句的WHERE子句,也可替换列名,表达式或文本.

使用双&符号替换变量

当在多个地方使用相同的替换变量时,然后为了避免再次重新输入相同的数据,我们使用双和号替换.在这种情况下,替换变量的值一旦输入,将在所有使用时刻被替换.

SELECT first_name, HIRE_DATE, SEPARATION_DATEFROM employeesWHERE HIRE_DATE LIKE '%&DT%' AND SEPARATION_DATE '%&&DT%'

请注意,在上述查询中,&DT的相同值被替换两次.因此,用户给出的值将在两个位置被替换.

DEFINE和VERIFY命令

设置变量的定义会话由SQL * Plus的DEFINE功能设置.可以在会话中定义变量,以避免在查询执行期间停止.只要在SQL查询中遇到Oracle,Oracle就会读取相同的变量.默认情况下处于ON状态.在DEFINE子句的帮助下,可以在查询执行之前在命令行中声明变量为 DEFINE variable = value; .

验证命令验证上面的替换显示作为OLD和NEW声明.它默认为OFF,可以使用SET命令设置为ON.

SQL> SET DEFINE ONSQL> SET VERIFY ONSQL> DEFINE NAME = MARTIN'SQL> SELECT first_name, SALARY FROM employeesWHERE first_name = '&NAME';OLD   1: select first_name, sal from employee where first_name = '&first_name'new   1: select first_name, sal from employee where first_name = 'MARTIN'first_name     SALARY-------        -------MARTIN         5000