SQL Fundamentals || Oracle SQL语言
DUAL is a public table that you can use to view results from functions and calculations.
The DUAL table is owned by the user SYS and can be accessed by all users.
It contains one column, DUMMY, and one row with the value X.
SQL Fundamentals || Single-Row Functions || 字符函数 character functions
SQL Fundamentals || Single-Row Functions || 数字函数number functions
SQL Fundamentals || Single-Row Functions || 日期函数date functions
SQL Fundamentals || Single-Row Functions || 转换函数 Conversion function
SQL Fundamentals || Single-Row Functions || 通用函数 General function
Using Single-Row Functions to Customize Output
Oracle SQL supplies a rich library of in-built functions which can be employed for various tasks. The essential capabilities of a functions can be the case conversion of strings, in-string or substring operations, mathematical computations on numeric data, and date operations on date type values. SQL Functions optionally take arguments from the user and mandatorily return a value.
- Describe various types of functions available in SQL
- Use character, number, and date functions in SELECT statements
字符、数字、日期函数
1、SQL Functions
Functions are a very powerful feature of SQL. They can be used to do the following:
|
Perform calculations on data |
执行数据计算 |
|
Modify individual data items |
修改单独的数据项 |
|
Manipulate output for groups of rows |
操纵行组的输出 |
|
Format dates and numbers for display |
格式化日期和数字进行显示 |
|
Convert column data types |
转换列数据类型 |
SQL functions sometimes take arguments and always return a value.
SQL有时候接收参数并总是返回一个值.
2、Two Types of SQL Function：
|
Single-row functions |
单行函数 These functions operate on single rows only and return one result per row. 单行函数只操作单个行并为每一行返回一个结果. - Single row functions are the one who work on single row and return one output per row. For example, length and case conversion functions are single row functions. |
|
Multiple-row functions |
多行函数 - Multiple row functions work upon group of rows and return one result for the complete set of rows. They are also known as Group Functions. |
3、Single-row functions单行函数
Single row functions
Single row functions can be character functions, numeric functions, date functions, and conversion functions. Note that these functions are used to manipulate data items. These functions require one or more input arguments and operate on each row, thereby returning one output value for each row. Argument can be a column, literal or an expression. Single row functions can be used in SELECT statement, WHERE and ORDER BY clause.
Single-row functions are used to manipulate data items. They accept one or more arguments and return one value for each row that is returned by the query.
（1）特点
|
Manipulate data items |
操作数据项 |
|
Accept arguments and return one value |
接收参数并返回一个值 |
|
Act on each row that is returned |
每一行进行操作 |
|
Return one result per row |
每一行返回一个值 |
|
Many modify the data type |
单行函数可以修改数据类型 |
|
Can be nested |
单行函数可以嵌套 |
|
Accept arguments that can be a column or an expression An argument can be one of the following:
|
函数接收的参数可以是列名或者表达式
|
(2)、语法
Function_name [(arg1,arg2….)]
（3）、类型
|
字符函数 character functions |
Accept character input and can return both character and number values. |
|
数字函数 number functions |
Accept numeric input and return numeric values. |
|
日期函数 date functions |
Operate on values of the DATE data type (All date functions return a value of the DATE data type except the MONTHS_BETWEEN function, which returns a number) 所有日期函数都返回一个DATE类型的值，除了MONTHS_BETWEEN函数，它返回一个数字. |
|
转换函数 Conversion function |
Convert a value from one data type to another |
|
通用函数 General function |
NVL：对空值做处理 NVL2：对空值做处理 NULLIF：对空值做处理 COALESCE CASE DECODE General functions The SELECT query below demonstrates the use of NVL function. SELECT first_name, last_name, salary, NVL (commission_pct,0) FIRST_NAME LAST_NAME SALARY NVL(COMMISSION_PCT,0) |