Oracle basic query filtering sorting instance

 

Basic query:

 

View plain

-- Query Information of all employees

Select * from emp;

-- Set the row width

Set linesize 120;

-- Set the column width to the width of four digits

Col empno for 9999;

-- Set the column width. a indicates the string length of eight digits.

Col ename for a8

-- Set pageSize to display 30 records per page

Set pagesize 30;

-- SQL supports arithmetic expressions. Note: if an expression contains null values, the entire expression is empty.

Select empno, ename, sal, sal * 12, comm, sal * 12 + comm from emp;

-- Use the filter empty function. If comm is empty, it will be replaced by 0.

Select empno, ename, sal, sal * 12, comm, sal * 12 + nvl (comm, 0) from emp;

-- Three methods of Alias are used. There is no difference between double quotation marks in the alias: Double quotation marks can contain spaces and special characters, but not double quotation marks.

Select empno as "employee ID", ename "name", sal monthly salary, comm, sal * 12 + nvl (comm, 0) annual income from emp;

-- About disctinct, take the same record only once

Select distinct deptno from emp;

-- When disctinct and multiple values are the same, deptno and job are considered the same records.

Select distinct deptno, job from emp;

-- Connector usage

Select ename | 'salary: '| sal from emp;

Note:

 

The SQL language is case insensitive.

SQL statements can be written in one or more rows.

Keywords cannot be abbreviated or branch

Each clause is generally written by a branch.

Use indentation to improve statement readability.

 

Filtering and sorting

 

View plain

-- Query System Parameters

Select * from v $ nls_parameters;

-- Modify the Date Format

Alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd ';

-- The conversion date of the hermit mode will affect the performance.

Select * from emp where hiredate = '2017-11-17 ';

-- Display mode conversion date

Select * from emp where hiredate = to_date ('2017-11-17 ', 'yyyy-mm-dd ');

-- Comparison operator: queries employees whose salaries are greater than 1000 and smaller than 2000. between and includes boundaries

Select * from emp where sal> = 1000 and sal <= 2000;

Select * from emp where sal between 1000 and 2000;

-- In: queries employees of Department 10 and department 20 in the collection.

Select * from emp where deptno = 10 or deptno = 20;

Select * from emp where deptno in (10, 20 );

-- Fuzzy query: queries employees whose names start with "S"

Select * from emp where ename like's % ';

-- To query employees whose names contain four characters, four underscores are required.

Select * from emp where ename like '____';

-- Query the employees whose names contain underscores (_). _ escape is required when underlines are special characters. escape is declared by escape.

Select * from emp where ename like '% \ _ %' escape '\';

-- Query employees whose bonuses are not empty

Select * from emp where comm is not null;

-- Query employee information, sorted by monthly salary

Select * from emp order by sal;

-- A command: APPEND Command. Change the above to sort in descending order.

A desc

-- Query employee information, sorted by annual salary

Select empno, ename, sal, sal * 12 from emp order by sal * 12;

-- Order by can be followed by an alias

Select empno, ename, sal, sal * 12 annual salary from emp order by annual salary;

-- Order by can be followed by the sequence number

Select empno, ename, sal, sal * 12 from emp order by 4;

-- Order by applies to multiple columns, first sorting by the first column, and then sorting by the second column

Select * from emp order by deptno, sal;

-- Order by applies to multiple columns. in descending order, desc is required for each column.

Select * from emp order by deptno desc, sal desc

-- Route null values to the end

Select * from emp order by comm desc nulls last;

-- Shield/enable feedback

Set feedback off/set feedback on

 

Sorting rules:

It can be sorted by the column name in the select statement.

Sort by alias column name

It can be sorted by the sequential values of the column names in the select statement.

If you want to sort by multiple columns, the rule is to first sort by the first column, if the same, then sort by the second column; and so on

From: Fu rongkang Column