Basic Learning for Oracle (ii)-Basic query

First, the basic query statement

(1) View Current user

Show user;

(2) View the table under the current user

SELECT * from tab;

(3) View the structure of the employee table

DESC EMP;

(4) Select all Columns

SELECT * from EMP;

(5) Select a specific column

SELECT Empno,ename,sal,comm,deptno from EMP;

(6) Define null values

A: null value is not valid, unspecified, unknown value.

B: null value is not a space or 0.

C: The value of a data expression that contains a null value is null.

--null value differs from 0, all null values are null--select employee_id,       last_name,       salary,       commission_pct,       salary * (1 + commission_pct) from  employees;

After rewriting:

--NVL function----Format: NVL (e1,e2)----Explanation: If E1 is null, the function returns E2, otherwise it returns E1. --select employee_id,       last_name,       salary,       commission_pct,       salary * (1 + NVL (commission_pct,0))  From employees;

(7) using aliases

A: Write directly after the column name.

B: Add the keyword as between the column name and the alias.

C: Use double quotation marks.

--use alias--select employee_id ID,       last_name as name,       12*salary "Annual_sal" from employees;

(8) Delete duplicate rows

--Use the keyword "DISTINCT" To remove duplicate lines--select DISTINCT department_id from employees;

Second, filtering and sequencing

(1) WHERE clause

A: Use the WHERE clause to filter out rows that do not meet the criteria.

The B:where clause follows the FROM clause.

Select Employee_id,last_name,salaryfrom employeeswhere employee_id > 200;

(2) characters and dates

A: The characters and dates are enclosed in single quotes.

B: Character case sensitive, date format sensitive.

C: The default date format is: Dd-mm month-yyyy.

Select employee_id, last_name, salary, hire_date from  employees where hire_date= ' 1 July-September-1987 ';

This is inconvenient to write, we usually write:

Select employee_id, last_name, salary, hire_date from  employees where TO_CHAR (hire_date, ' yyyy-mm-dd ') = ' 1987-09-17 ‘;

Basic Learning for Oracle (ii)-Basic query