SQL语言基础-检索数据

检索数据

        用户对表或视图最常进行的操作就是检索数据，检索数据可以通过SELECT语句来实现，该语句由多个子句组成，通过这些子句可以完成筛选、投影和连接等各种数据操作，最终得到用户想要的查询结果。
        格式：
        select {[distinct|all]columns|*}
        [into table_name]
        from {tables|views|other select}
        [where conditions]
        [group by columns]
        [having conditions]
        [order by columns]

        select子句：用于选择数据表、视图中的列。

        into子句：用于将原表的结构和数据插入新表中。

        from子句：用于指定数据来源，包括表，视图和其他select语句。
    where子句：用于对检索的数据进行筛选。
    group by子句：用于对检索结果进行分组显示。
    having子句：用于从使用group by子句分组后的查询结果中筛选数据行。
    order by子句：用来对结果集进行排序（包括升序和降序）。

 

1. 简单查询

 

        只包含SELECT子句和FROM子句的查询就是简单查询，SELECT子句和FROM子句是SELECT语句的必选项。如果要检索指定数据表的所有列，可以在SELECT子句后面使用星号（*）来实现。在检索一个数据表时，要注意该表所属于的模式。如果在指定表所属的模式内部检索数据，则可以直接使用表名；如果不在指定表所属的模式内部检索数据，则不但要查看当前模式是否具有查询的权限，而且还要在表名前面加上其所属的模式名称。例：

       上面的SELECT语句若要在SYSTEM模式下执行，则需要在dept前面加上scott，即“scott.dept“。上面的例子中，from子句的后面只有一个数据表，实际上可以在from子句的后面指定多个数据表，每个数据表之间使用逗号（,）分隔开，例：

 

SQL>select * from scott.dept, scott.salgrade;

 
        用户可以指定查询表中的某些列而不是全部列，并且被指定列的顺序不受限制，指定部分列也称作投影操作。这些列名紧跟在SELECT关键字的后名，每个列名之间用逗号隔开。利用SELECT指定列的好处就是可以改变列在查询结果中的默认显示顺序。例：

 

 

       上面显示结果中列的显示顺序与emp表结构的自然顺序不同。
       在Oracle数据库中，有一个标识行中唯一特性的行标识符，该行标识符的名称为ROWID。行标识符ROWID是Oracle数据库内部使用的隐藏列，由于该隐藏列实际上并不是定义在表中，所以也称为伪列。伪列ROWID长度为18位字符，包含了该行数据在Oracle数据库中的物理地址。用户使用DESCRIBE命令是无法查到ROWID列的，但是可以在SELECT语句中检索到该列。例：

 

SQL>select rowid, job, ename from scott.emp;

 
        在使用SELECT语句时，对于数字数据和日期数据都可以使用算术表达式。在SELECT语句中可以使用算术运算符，包括加（+）、减（-）、乘（*）、除（/）和括号。另外，在SELECT语句中不仅可以执行单独的数学运算，还可以执行单独的日期运算以及列名关联的运算。例，检索emp表的sal列，把其值调整为原来的1.1倍：

 

 

        由于许多数据表的列名都是一些英文缩写，用户为了方便查看检索结果，常常需要为这些列指定别名。在Oracle系统中，为列指定别名既可以使用AS关键字，也可以不使用任何关键字而直接指定。例：检索emp表的指定列（empno,ename,job），并使用as关键字为这些列指定中文的别名：

 

 

        在默认情况下，结果集中包含所有符合查询条件的数据行，这样结果集中就有可能出现重复数据。而在实际的应用中，这些重复的数据除了占据较大的显示空间外，可能不会给用户带来太多有价值的东西，这样就需要除去重复的记录，保留唯一的记录即可。在SELECT语句中，可以使用DISTINCT关键字来限制在查询结果显示不重复的数据，该关键字用在SELECT子句的列表前面。例：

 

SQL>select distinct job from scott.emp;

 

 

2. 筛选查询

        在SELECT语句中使用WHERE子句可以实现对数据行的筛选操作，只有满足WHERE子句中判断条件的行才会显示在结果集中，而那些不满足WHERE子句判断条件的行则不包括在结果集中。通过筛选数据，可以从大量的数据中得到用户所需要的数据。在SELECT语句中，WHERE子句位于FROM子句之后。

        语法：SELECT columns_list FROM table_name WHERE conditional_expression
    columns_list：字段列表。
    table_name：表名。
    conditional_expression：筛选条件表达式。

        比较筛选：可以在WHERE语句中使用比较运算符来筛选数据，这样只有满足筛选条件的数据行才会被检索出来，不满足比较条件的数据行则不会被检索出来。在进行比较筛选的过程中，字符串和日期的值必须使用单引号标识，否则Oracle会提示“标识符无效”。例：

 

SQL>select empno, ename, sal from scott.emp where sal>1500;

 
        另外，有两个特殊的“比较筛选”操作：
        A {operator} ANY (B)：表示A与B中的任何一个元素进行operator运算符的比较，只要有一个比较值为true，就返回数据行。
        A={operator} ALL (B)：表示A与B中的所有元素进行operator运算符的比较，只有与所有元素比较值都为true，就返回数据行。
        例，使用ALL关键字过滤工资（sal）同时不等于3000，950和800的员工记录：

 

 

        使用特殊关键字筛选：SQL语言提供了LIKE、IN、BETWEEN和IS NULL等关键字来筛选匹配的数据，这些关键字的功能分别是匹配字符串、查询目标值、限定值的范围和判断值是否为空等。可以在关键字前加上NOT，表示否定的判断。

 

       LIKE关键字：在WHERE子句中使用LIKE关键字查询数据的方式也称为字符串模式匹配或字符串模糊查询。LIKE关键字需要使用通配符在字符串内查找指定的模式，所以需要了解常用的通配符。常用的通配符有下划线“_”，它代表任意一个字符；百分号“%”，它代表任意数据的字符。例如，“K%”表示以K字母开头的任意长度的字符串，“%M%”表示包含字母M的任意长度的字符串，“_MRKJ”表示5个字符长度且后面4个字符是MRKJ的字符串。例，使用LIKE关键字匹配以字母S开头的任意长度的员工名称：

 

       IN关键字：当测试一个数据值是否匹配一组目标值中的一个时，通常使用IN关键字来指定列表搜索条件。IN关键字的格式是IN（目标值1,目标值2,目标值3…），目标值的项目之间必须使用逗号分隔，并且括在括号中。例，在emp表中使用IN关键字查询职务为“PRESIDENT”、“MANAGER”和“ANALYST”中任意一种的员工信息：

 

        BETWEEN关键字：需要返回某一个数值是否位于两个给定的值之间，可以使用范围条件进行检索。通常使用BETWEEN…AND和NOT…BETWEEN…AND。使用BETWEEN…AND查询条件时，指定的第一个值必须小于第二个值。因为BETWEEN…AND实质是查询条件“大于等于第一个值，并且小于等于第二个值”的简写形式。即BETWEEN…AND要包括两端的值，等价于比较运算符（>=…<=）。例，在emp表中，使用“BETWEEN…AND”关键字查询工资（sal）在2000到3000之间的员工的信息：

 

        IS NULL关键字：空值（NULL）从技术上来说就是未知的、不确定的值，但空值和空字符串不同，因为空值是不存在的值，而空字符串是长度为0的字符串。因为空值代表的是未知的值，所以并不是所有的空值都相等。这样就不能用“=”运算符来检测空值。所以SQL引入了个IS NULL关键字来检测特殊值之间的等价性，并且IS NULL关键字通常在WHERE子句中使用。例，在HR模式下，使用IS NULL关键字过滤出locations表中省份或州（state_province）的名称为空值的街道地址信息：

 

       逻辑筛选：逻辑筛选是指在WHERE子句中使用逻辑运算符AND、OR和NOT进行数据筛选操作，那么这些逻辑运算符可以把多个筛选条件组合起来，这样便于用户获取更加准确的数据记录。
AND逻辑运算符：表示两个逻辑表达式之间“逻辑与的关系”，完全可以使用“AND”运算符加“比较运算符”来代替“BETWEEN…AND”关键字。例，在表emp中，使用“AND”运算符查询工资（sal）在2000到3000之间的员工信息：

 

       OR逻辑运算符：表示两个逻辑表达式之间“逻辑或”的关系，两个表达式的结果中有一个为true，则这个逻辑表达式的值就为true。例，在emp表中，使用OR逻辑运算符查询工资小于2000或工资大于3000的员工信息：

 

select empno,ename,sal from scott.emp where sal<2000 or sal>3000;

 

 

3. 分组查询

        数据分组的目的是用来汇总数据或为整个分组显示单行的汇总信息，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。通常在查询结果集中使用GROUP BY子句对记录进行分组。在SELECT子句中，GROUP BY子句位于FROM子句之后。
        语法：

        SELECT columns_list FROM table_name [WHERE conditional_expression] GROUP BY columns_list
    columns_list：字段列表，在GROUP BY子句中也可以指定多个列分组。
    table_name：表名。
    conditional_expression：筛选条件表达式。

       GROUP BY子句可以基于指定某一列的值将数据集合划分为多个分组，同一组内所有记录在分组属性上具有相同的值，也可以基于指定多列的值将数据集合划分为多个分组。例，在emp表中，按照部门编号（deptno）和职务（job）列进行分组：

 

        GROUP BY子句经常与聚集函数一起使用。如果SELECT子句中包含聚集函数，则计算每组的汇总值，当用户指定GROUP BY时，选择列表中任一聚集表达式内的所有列都应包含在GROUP BY列表中，或者GROUP BY表达式必须与选择列表表达式完全匹配。例，在emp表中，通过分组方式计算每个部门的平均工资：

 

       HAVING子句对GROUP BY子句选择出来的结果进行再次筛选，最后输出符合HAVING子句中条件的记录。HAVING子句的语法跟WHERE子句的语法“相类似”，唯一不同的是HAVING子句中可以包含聚集函数count、avg、sum等。例，在emp表中，首先通过分组的方式计算出每个部门的平均工资，然后再能过HAVING子句过滤出平均工资大于2000的记录信息：

 

4. 排序查询

        在检索数据时，如果把数据从数据库中直接读取出来，这时查询结果将按默认顺序排列。在SELECT语句中，可以使用GROUP BY子句对检索的结果集进行排序，该子句位于FROM子句之后。ORDER BY子句可以根据查询结果中的一个列或多个列对查询结果进行排序，并且第一个排序项是主要的排序依据，其余那些是次要的排序依据。
        语法：

        SELECT columns_list FROM table_name [WHERE conditional_expression] [GROUP BY columns_list] ORDER BY {order_by_expression [ASC|DESC]}
    columns_list：字段列表，这里的GROUP BY子句中也可以指定多个列分组。
    table_name：表名。
    conditional_expression：筛选条件表达式。
       order_by_expression：表示要排序的列名或表达式。关键字ASC表示按升序排列，这也是默认的排列方式；关键字DESC表示按降序排列。
       检索emp表中的所有数据，并按照部门编号（deptno）、员工编号（empno）排序：

 

 

5. 多表关联查询

       在实际的应用系统开发中会涉及多个数据表，每个表的信息不是独立存在的，而是若干个表之间的信息存在一定的关系，这样当用户查询某一个表的信息时，很可能需要查询关联数据表的信息，这就是多表关联查询。SELECT语句自身是支持多表关联查询的，多表关联查询要比单表查询复杂得多。在进行多表关联查询时，可能会涉及表别名、内连接、外连接、自然连接、自连接和交叉连接等概念。

        表别名：在多表关联查询时，如果多个表之间存在同名的列，则必须使用表名来限定列的引用。但是，随着查询变得越来越复杂，语句就会因为每次限定列必须输入表名而变得冗长乏味。对于这种情况，SQL语言提供了设定表别名的机制，使用简短的表别名就可以替代原有连长的表名称，这样就可以大大缩减语句的长度。需要注意一点，一旦在FROM子句中为表指定了别名，则必须在其他的子句中都使用表别名，而不允许再使用原来的表名，否则将出现标识符无效的错误提示。例，通过deptno（部门编号）列来关联emp表和dept表，并检索这两个表中相关字段的信息：

 

       内连接：内连接是一种常用的多表关联查询方式，一般使用关键字INNER JOIN来实现。其中，INNER关键字可以省略，当只使用JOIN关键字时，语句只表示内连接操作。在使用内连接查询多个表时，必须在FROM子句之后定义一个ON子句，该子句用来指定两个表实现内连接的“连接条件”。需要注意的是，在内连接的检索结果中，所有记录行都是满足连接条件的。

       格式：SELECT columns_list FROM table_name1[INNER] JOIN table_name2 ON join_condition;
   columns_list：字段列表。
   table_name1和table_name2：两个要实现内连接的表。
   join_condition：实现内连接的条件表达式。
      通过deptno字段来内连接emp表和dept表，并检索这两个表中相关字段的信息：

 

        外连接：使用内连接进行多表查询时，返回的查询结果中只包含符合查询条件和连接条件的行。内连接消除了和另一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集，外连接不只列出与连接条件匹配的行，还能列出左表（左外连接时）、右表（右外连接时）或两个表（完全外连接时）中所有符合搜索条件的数据行。外连接种类通常有以下三种：
    左外连接：关键字为LEFT OUTER JOIN或LEFT JOIN。
    右外连接：关键字为RIGHT OUTER JOIN或RIGHT JOIN。
    完全外连接：关键字为FULL OUTER JOIN或FULL JOIN。

       左外连接的查询结果不仅包含了满足连接条件的数据行，而且还包含左表（FROM子句后第一个表）中不满足连接条件的数据行。例，首先使用insert语句在emp表中插入新记录（注意没有为deptno和dname列插入值，即它们的值为null），然后实现在emp表和dept表之前通过deptno列进行左外连接：

 

insert into emp(empno,ename,job) value(9527,’EAST’,’SALESMAN’);

 

 

       右外连接的查询结果不仅包含了满足连接条件的数据行，而且还包含右表（FROM子句后第二个表）中不满足连接条件的数据行。

       完全外连接的查询结果不仅包含了满足连接条件的数据行，而且还包含左表和右表中不满足连接条件的数据行。

       自然连接：自然连接是指在检索多个表时，Oracle会将第一个表中的列与第二个表中的具有相同名称的列进行自动连接。在自然连接中，用户不需要明确指定进行连接的列，这个任务由Oracle自动完成，自然连接使用“NATURAL JOIN”关键字。由于自然连接强制要求表之间必须具有相同的列名称，这样容易在设计表时出现不可预知的错误，所以在实际应用系统开发中很少用到自然连接。但毕竟这是一种多表关联查询数据的方式，在某些特定情况下还是有一定的使用价值。另外需要注意的是，在使用自然连接时，不能为列指定限定词（即表名或表的别名），否则Oracle系统会弹出“ORA-25115：NATURAL连接中使用的列不能有限定词”的错误提示。例，在emp表中检索工资（sal字段）大于2000的记录，并实现emp表与dept表的自然连接：

 

       自连接：在应用系统开发中，用户可能会拥有“自引用式的外键”。“自引用式外键”是指表中的一个列可以是该表主键的一个外键。比如，在SCOTT模式下，emp表中某一行的mgr列值（管理者列）可能是另一行的empno列值（员工列），因为管理者本身也是公司的员工。这样用户就可以通过mgr列与empno列的关系，实现查询某个管理者所管理的下属员工信息。例：

 

        交叉连接：交叉连接实际上就是不需要任何连接条件的连接，它使用CROSS JOIN关键字来实现。交叉连接的执行结果是一个笛卡儿积，这种查询结果是非常冗余的，但可以通过WHERE子句来过滤出有用的记录信息。例，在SCOTT模式下，通过交叉连接dept表和emp表计算出查询结果的行数：