Oracle table data query for getting Started (i)

The table used in this article is the three tables that are available from Scott users:

Basic SELECT statement

Basic syntax:

SELECT [DISTINCT] *| {Columnl,column2,column3.} From table [where {condition}];

Select [whether to reject duplicate data] *| {Field name (column name), Field name 2 (column Name 2), field name 3 (column name 3) ...} from table name [where {condition}]; Precautions:

1, select specify which columns to query data;

2. Column name specified;

3. * representative query all columns;

4, from the specified query which table;

5, distinct optional, refers to the display of the results, whether to eliminate duplicate data;

6, where condition.

Oracle Table Basic Query--Simple query statement

View table Structure

SQL>DESC table name;

Querying all Columns

Sql>select * from table name;

Querying a specified column

Sql>select column 1, column 2, column 3,.. from table name;

How to cancel duplicate rows

Sql>select distinct deptno,job from EMP;

Enquiry Smith's salary, job, department

Sql>select Sal,job,deptno from emp where ename= ' SMITH ';

Special NOTE:Oracle is case insensitive to SQL statements, but is case sensitive to query content. This is different from SQL Server, where SQL Server is not case-sensitive to query content.

Using an arithmetic expression

Show annual salary for each employee

Sql>select ENAME,SAL*13+NVL (comm,0) *13 from EMP;

Using Aliases for columns

Sql>select ename "Name", SAL*13+NVL (comm,0) *13 "annual income" from EMP;

Sql>select ename name, SAL*13+NVL (comm,0) *13 annual income from EMP;

Sql>select ename as "name", SAL*13+NVL (comm,0) *13 as "annual income" from EMP;

Special Note:When using aliases, Oracle can use double quotation marks or no use or use as to indicate aliases. However, you cannot use single quotes. SQL Server is able to use double quotes, single quotes.

How to handle null values

use the NVL function to handle

Use of the NVL function, Description:NVL is a function provided by Oracle and is used to handle null values. SQL Server provides a isnull function to handle null values.

Sql>select ENAME,SAL*13+NVL (comm,0) *13 from EMP;

NVL (value 1, value 2) explains: NVL value 1 is null when the value is 2, and the value 1 is not NULL when the value 1 is the original value.

How to connect strings (||)

Sql>select ename | | ' Annual Income ' | | (SAL*13+NVL (comm,0) *13) "Annual income of Employees" from EMP;

|| In the query, you want to make multi-column content as a column of content return can use | | Connector.

Oracle Table Basic Query--Simple query statement

using the WHERE clause

how to show employees with wages above 3000

Sql>select ename,sal from EMP where sal>3000;

How to find employees who have been in employment since 1982.1.1

Sql>select ename,hiredate from emp where hiredate> ' January-January-82 ';

You can also use the To_char function to convert a date type before you make a date comparison, as follows:

Sql>select ename,hiredate from EMP where To_char (hiredate, ' yyyy-mm-dd ') > ' 1982-1-1 ';

There is a certain discrepancy between the characters. It is not recommended.

How to show employees with a salary of 2000 to 2500

Sql>select * from EMP where sal>=2000 and sal<=2500;

Sql>select * from EMP where Sal between and 2500;

Note:between is a specified range of values, such as: Between and 2500, take 2000 to 2500 values, including 2000 and 2500

How to use the LIKE operator

%: denotes any 0 to more characters

_: Represents any single character

How to display the first character as Employee name and salary of S

Sql>select ename,sal from emp where ename like ' s% ';

How to display a third character in uppercase o The names and wages of all employees

Sql>select ename,sal from emp where ename link ' __o% ';

in the where condition using in

How to display an employee with a empno of 123,345,800 ...

Sql>select * from EMP where empno=123 or empno=345 or emp=800;

Sql>select * from EMP where empno in (123,345,800);

Operator with is null

How to show an employee without a superior

Sql>select * from EMP where Mgr is null;

Using Logical manipulation symbols

Search for employees with a salary higher than 500 or job manager, and also meet their first and last name uppercase J

Sql>select * from EMP where (sal>500 or job= ' MANAGER ") and (ename like ' j% ');

using the ORDER BY clause

How to display employee information in order of low to high wages

Sql>select * from emp ORDER by SAL ASC;

NOTE:ASC writes or does not write in ascending order, from small to large, and Desc is descending sort from large to small.

The employee's entry time is sorted in descending order by department number ascending

Sql>select * from emp ORDER BY deptno,hiredate Desc;

Sort by using the alias of a column

Sql>select ename,sal*12 "annual salary" from the EMP Order by "annual salary" ASC;

aliases need to be used in the "" Ring.

Oracle table data query for getting Started (i)