Oracle Database（3） 操作符Query

本文摘抄自Oracle官方手册,复杂一点的查询，Page 2-18 Operator and Function in Queries.

Using Operatiors and Functions in Queries

SELECT select_list FROM source_list;

The select_list of a query can include SQL expressions, which can include SQL operators and SQL functions. These operators and functions can have table data as operands and arguments. The SQL expressions are evaluated, and their values appear in the results of the query.
This section contains:

• Using Arithmetic Operators in Queries
• Using Numeric Functions in Queries
• Using the Concatenation Operator in Queries
• Using Character Functions in Queries
• Using Datetime Functions in Queries
• Using Conversion Functions in Queries
• Using Aggregate Functions in Queries
• Using NULL-Related Functions in Queries
• Using CASE Expressions in Queries
• Using the DECODE Function in Queries

Using Arithmetic Operators in Queries

SQL supports the basic arithmetic operators: +,-,*,/ (加减乘除）.

SELECT LAST_NAME,
       SALARY / 30 "Daily Pay",
       SALARY "Monthly Pay",
       SALARY * 12 "Annual Pay"
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID = 90
 ORDER BY SALARY DESC;

根据薪水计算日薪、月薪和年薪

Using Numeric Functions in Queries

Numeric functions accept numeric input and return numeric values. Each numeric function returns a single value for each row that is evaluated. The numeric functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

SELECT LAST_NAME, ROUND(((SALARY * 12) / 365), 2) "Daily Pay"
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID = 100
 ORDER BY LAST_NAME;

ROUND(long, digit)是一个函数，取 long 的小数点后 digit 位数，此处取小数点后2位。

计算日薪

TRUNC()函数可以取整数

SELECT LAST_NAME, TRUNC((SALARY * 12) / 365) "Daily Pay"
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID = 100
 ORDER BY LAST_NAME;

取整

Using the Concatenation Operator in Queries

The concatenation operator (||) combines two strings into one string, by appending the second string to the first. For example, 'a'||'b'='ab'. You can use this operator to combine information from two columns or expressions in the same column of the report.

SELECT FIRST_NAME || ' ' || LAST_NAME "Name"
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID = 100
 ORDER BY LAST_NAME;

字符串链接起来，数据库存储URL时可以用到

Using Character Functions in Queries

Character functions accept character input. Most return character values, but some return numeric values. Each character function returns a single value for each row that is evaluated. The character functions that SQL supports are listed and described in Oracle Database SQL Language Reference.
The functions UPPER, INITCAP, and LOWER display their character arguments in uppercase, initial capital, and lowercase, respectively.
（大写、首字母大写、小写）

SELECT UPPER(LAST_NAME) "Last",
       INITCAP(FIRST_NAME) "First",
       LOWER(EMAIL) "E-Mail"
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID = 100
 ORDER BY EMAIL;

使用函数控制结果大小写

Using Datetime Functions in Queries

Datetime functions operate on DATE, time stamp, and interval values. Each datetime function returns a single value for each row that is evaluated. The datetime functions that SQL supports are listed and described in Oracle Database SQL Language Reference.
For each DATE and time stamp value, Oracle Database stores this information:

• Year
• Month
• Date
• Hour
• Minute
• Second
  。。。这块剩下的自行阅读，都是简单的操作符讲解。