SQL语句(1) - SELECT语句

SQL语句类型

分类	语句
DML，Data manipulation language，数据操作语言	SELECT、INSERT、UPDATE、DELETE、MERGE
DDL，Data definition language，数据定义语句	CREATE、ALTER、DROP、RENAME、TRUNCATE、COMMENT
DCL，Data control language，数据控制语句	GRANT、REVOKE
TCL，Transaction control language，事务控制语句	COMMIT、ROLLBACK、SAVEPOINT

使用SELECT语句检索数据

select * from employees;   -- 检索表中所有数据

  先执行from，再执行select语句。也就是说，先定位到emloyees这张表，之后再检索表中所有的数据。

desc employees;     -- 查看表结构

空值

  空值NULL既不是零也不是空格。任何值与空值计算结果都是空值。

定义列别名

• 列名与列别名之间可以有选择的使用AS关键字；
• 如果列别名包含空格或特殊字符，或者区分大小写，则需要双引号。单引号表示字符，双引号用于列别名。

SELECT employee_id, last_name as Name , salary*12 "Annual Salary"  FROM employees;

连接符

  连接符用两个竖线||表示。

select employee_id, first_name || ' ' || last_name as "employee name" from employees;

单引号

(1) 单引号用来表示字符或字符串；

select 'hello world' from dual;

  输出结果：hello world

(2) 单引号用作转义；

  当字符串中包含单引号时，需要特别处理。如果执行下面的语句，讲导致产生一个Oracle错误。

select 'it's an example' from dual;

  那么如何处理包含单引号的句子呢？主要可以使用两种办法。其中，最常见的是在字符串中应该出现的单引号前面再添加一个单引号。

select 'it''s an example' from dual;

  输出结果：it’s an example

  第二种办法就是使用q运算符。

q运算符

  使用两个单引号来处理字符串中应该出现单引号的方法，容易变得混乱和出错。使用q运算符可以进行简化。引用运算符的语法如下所示：

q'delimiter character literal which may include single quotes delimiter'

  其中，delimiter可以是任何字符或括号。大写Q和小写q都可以。

select q'[it's an example]' from dual;
select q'{it's an example}' from dual;
select q'(it's an example)' from dual;
select q'<it's an example>' from dual;
select q'Xit's an exampleX' from dual;

输出结果都是：it’s an example

WHERE限制行数据

• 使用=，<=，BETWEEN，IN，LIKE和NULL条件的比较运算符；
• 使用AND，OR和NOT运算符的逻辑条件。

使用LIKE运算符进行模式匹配

  使用LIKE运算符对有效的搜索字符串值执行通配符搜索。搜索条件可以包含文字字符或数字：

• %表示零个或多个字符。
• _表示一个字符。

SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;

使用NULL条件

  使用IS NULL或者IS NOT NULL运算符测试空值null。不可使用等号、小于号或大于号比较空值。

SELECT * FROM employees WHERE  manager_id is null ;

使用ORDER BY子句排序

  使用ORDER BY子句对检索到的行进行排序：

• ASC：升序排序， 默认升序;
• DESC：降序排序。

SELECT   last_name, job_id, department_id, hire_date
FROM     employees
ORDER BY hire_date ;

SQL行限制子句

  许多分析查询只需要数据集的一部分，例如组织中的前三名销售员。其他数据库可以使用top命令，然而Oracle没有top命令。Oracle12c版本之前，可以使用rownum。

ROWNUM

select * from 
( select employee_id, first_name, last_name, job_id, salary  
from employees  order by salary desc) t
where rownum <= 7;

运行结果：

  但rownum有其局限性，例如下面这行sql语句执行之后没有数据显示。因为rownum总是从1开始的，第一条不满足，第二条的rownum又成1。以此类推，所以永远没有满足条件的记录。

select * from  employees where rownum >= 7;

OFFSET FETCH

  Oracle12c版本，提供了新特性，限制行的子句如下：

SELECT *|{[DISTINC] column|expression [alias],...}
   FROM table
 [ WHERE …  ]
 [ ORDER BY …  ]
 [OFFSET offset { ROW | ROWS }]
[FETCH { FIRST | NEXT } [{ number_of_rows | percent percentage_of_roes }] { ROW | ROWS } 
  { ONLY | WITH TIES }]

  OFFSET子句指定要跳过多少行，才会到达结果集中的开始位置。如果没有OFFSET子句，OFFSET就默认为0，如果OFFSET子句是NULL、负数，或者偏移量大于所提供结果集中的总行数，OFFSET也默认为0。ROW和ROWS关键字的含义相同，只是用于单数或复数的区别。

  FETCH子句指定要返回的准确number_of_row(行数)或percentage_of_row(行数百分比)。没有这个句子就会返回整个结果集，从行号OFFSET+1到最后一行。FIRST和NEXT关键字含义相同。

  关键字ONLY或WITH TIES应与ROW/ROWS关键字一起使用。使用ROWS WITH TIES，结果中会展出与最后一行关键字段相同的数据，但这在指定了ORDER BY子句之后才有效。

select employee_id, first_name, last_name, job_id, salary 
 from employees 
 order by salary desc
 offset 2 rows
 fetch first 5 rows only;

 select employee_id, first_name, last_name, job_id, salary 
 from employees 
 order by salary desc
 fetch first 7 rows only;

运行结果：

  使用ROWS WITH TIES，查询结果中根据order by关键字，会展示出与最后一行关键字相同结果的数据。下面两例都是。

 select employee_id, first_name, last_name, job_id, salary 
 from employees 
 order by salary desc
 fetch next 7 rows with ties;

运行结果：

 select employee_id, first_name, last_name, job_id, salary 
 from employees 
 order by job_id 
 fetch next 7 rows with ties;

运行结果：

替代变量

  使用替代变量，可以临时存储带有“&”和双“&&”替换的值。

  使用替代变量可以补充以下内容：

• WHERE条件；
• ORDER BY子句；
• 列表达式；
• 表名；
• 整个SELECT语句。

  注意：不能出现在from后面。

使用单&替换变量

  使用以“&”为前缀的变量来提示用户输入值：

SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  employee_id = &employee_num ;

  对日期和字符值使用单引号：

SELECT last_name, department_id, salary*12
FROM   employees
WHERE  job_id = '&job_title' ;

  替换变量可以出现在列名、WHERE、ORDER BY上，但不能出现在from后面。

SELECT employee_id, last_name, job_id, &column_name
FROM   employees
WHERE  &condition
ORDER BY &order_column;

使用&&替换变量

  如果要重复使用变量值而不需要每次都提示用户输入，可以使用“&&”。使用“&&”只需要输入一次值。

select &&A from dual;

  当使用“&&”替换变量时，该变量会一直保持同一个值，使用undefine命令才可以消除。

在SQL * Plus中使用＆替换变量

使用DEFINE命令

  使用DEFINE命令创建变量并将其分配给变量。
  使用UNDEFINE命令删除变量。

DEFINE employee_num = 200;

SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  employee_id = &employee_num ;

UNDEFINE employee_num;

使用VERIFY命令

  在SQL Developer用值替换时，“SET VERIFY ON”会有窗口显示替换变量的前后语句。

  在sqlplus中，默认是" SET VERIFY ON "设置，会自动显示变量替换的过程。输入“SET VERIFY OFF” 可以关闭。