Oracle Basic Queries

Access Oracle with CMD

Sqlplus username/password @ip address: 1521/database name

Like Sqlplus scott/[email PROTECTED]:1521/ORCL.

Syntax format and instance of the SELECT statement

　　SELECT * | {[DISTINCT] column | expression [alias], ...} from table;

1. Query syntax

SELECT * | Column name from table name

　　

　　

2. Alias usage

Aliases can be used in the result column of a query

Select column name as "Alias", column name as "Alias" ... from table name;

in aliases, there is no double quotation mark difference is that there is no special symbol or keyword in the alias, and as can be omitted, it is recommended to write full and double quotation marks, so as not to encounter double quotation marks

3. Eliminate duplicate data

SELECT DISTINCT * | Column name, ... from EMP;

　　

Duplicate rows can be eliminated by using distinct, if multiple columns must be repeated in order to remove duplicates

4, query in arithmetic

Check each employee's annual income (annual salary + bonus)

Select Ename,sal,comm,sal*12+comm as "annual income" from EMP;

Above we found that the bonus is NULL, the annual income is also followed by NULL, so our SQL statement is wrong, so where does the problem occur? Actually, there's a problem with the empty value.

　　What is a null value?

Null value is not valid, unspecified, unknown, or unpredictable value

Null value is not a space or 0

　　　　Note: Expressions that contain null are NULL

A null value is never equal to a null value

So the above SQL statement to non-empty processing, non-empty processing function has two NVL and NVL2, here we use NVL (parameter 1, parameter 2), when the parameter 1 is null, the value of the parameter 2

Select ENAME,SAL,COMM,SAL*12+NVL (comm,0) as "annual income" from EMP;

　　

　　Connector | |

　　String Connection Query

Query employee's number, name, and salary

Number is: xxxx (number) of XXXX (name) employee monthly salary is xxxx (salary)

Select ' number is ' | | Empno | | ' Of ' | | ename | | ' Employee's salary is ' | | Sal as "description" from EMP;

　　

　　Note: Connector | | Be sure to use single quotes when connecting strings, and always use double quotes for aliases.

Oracle Basic Queries