Oracle simple query, qualified query, data sort SQL statement examples and detailed annotations _oracle

First, simple query

SQL (structured query Language) Structured Query language is a database query and programming language for accessing data and querying, updating, and managing relational database systems. ANSI (American National Standards Institute) claims that SQL is the standard language for relational database management systems.
Oracle databases have developed well, mainly because Oracle is the world's first database product to use SQL statements.
SQL is powerful and, to sum up, it can be divided into the following groups:

Copy Code code as follows:

DML (manipulation Language) data manipulation language for retrieving or modifying data
DDL (data definition Language) Data definition language, used to define the structure of data, create, modify, or delete database objects
DCL (Data Control Language), which defines permissions for a database

A simple query refers to querying all the data in a table, and the syntax for a simple query is as follows:

Copy Code code as follows:

SELECT [DISTINCT] * | field [Alias] [, Field [alias]] from table name [alias];

Example:

 All records from dept;--Query Dept Table select Empno, ename, sal from emp;--query each employee's number, name, and basic worker The SELECT job from emp;--queries the position of each employee.  
This time found that duplicate data appeared in the job contents of the query. The SELECT DISTINCT job from emp;--uses DISTINCT to eliminate all duplicate content.  
However, for duplicate data, the record of each column in a row repeats, which is called duplicate data. Select DISTINCT ename, the job from emp;--queries each employee's name, position SELECT ename, job, sal*12 from emp;--use each mathematical arithmetic character, and requires that each employee's name, position, basic  
Yearly salary SELECT ename, job, sal*12 income from emp;--an alias for the query displayed, for aliases, it is recommended not to use Chinese, as long as the development of the program, to avoid Chinese. Select Ename, Job, (sal+300) *12 income from emp;--as the company's welfare is high, there is a monthly allowance of 200 dollars for meals and 100 yuan fare subsidy, this time the annual salary SELECT ename, job, (sal+300 *12+sal income from emp;--Company will make a one-month base salary SELECT empno at the end of each year | ',' || Ename from emp;--can also use the "| |" in a simple query  
The field of the connection query. SELECT ' Employee number is: ' | | Empno | | ' The employee's name is: ' | | ename | | ', the basic salary is: ' | | Sal | | ', the position is: ' | | Job | | '! 
' Employee information from emp;--requires that the current database be displayed in the following format: "Employee number is: 7369 the employee name is: SMITH, base salary is: 800, position is: clerk!" 

Because "," is a string that is output-only, it must be enclosed in "', i.e., in an SQL statement," ' "represents a string.
It is important to remember that the content on the alias is not enclosed by "'", and that only what appears in the SELECT clause uses "'.

Second, limited query

In the previous simple query, is to display all the records, but now you can filter the records that are displayed, and this is the job of qualifying the query, which is to add a WHERE clause on the basis of the previous syntax to specify the qualification, at which point the syntax is as follows:

Copy Code code as follows:

SELECT [DISTINCT] * | field [Alias] [, Field [alias]]
from table name [alias]
[WHERE condition (S)];

More than one condition can be added after a WHERE clause, the most common condition being the basic relational operations:>, >=, <, <=,!= (<>), BETWEEN, and, like, in, are NULL, and, or, not;

1. Relational operation

SELECT * from EMP where sal>1500;--requires that all employees with a base salary above 1500  
be queried select * from emp where job= ' clerk ';--query for employee information for all positions as clerks C2/>select * from EMP where job= ' clerk ', which does not return the corresponding query results, mainly because in the Oracle database, all data is case-sensitive  
SELECT * from EMP where sal& gt;=1500 and sal<=3000;--query for all employee information between wages 1500~3000, multiple conditions can be connected using and or OR,  
SELECT * from emp WHERE job= ' clerk ' or job= ' salesman ';--query out the position is a clerk, or a salesperson's full information  
SELECT * from emp WHERE (job= ' clerk ' OR job= ' salesman ') and sal>1200;-- Queries out positions are clerks, or all of the information of the salesperson, and requires those employees to pay more than 1200  
select * from emp WHERE job<> ' clerk ';--query all employee information that is not a clerk  
Select * FROM emp WHERE job!= ' clerk ';  

2, the scope of judgment: BETWEEN ... And ...

"BETWEEN minimum and maximum" indicates a range of judging processes. "BETWEEN ... And ... "Operators are not only useful for numbers but also useful for dates.

Copy Code code as follows:

SELECT * from EMP where Sal BETWEEN 1500 and 3000;--asks to inquire about employee information for base pay in 1500~3000
SELECT * from emp WHERE not sal BETWEEN 1500 and 3000;--can now also be BETWEEN ... And ... Operation Negation
SELECT * from EMP where hiredate BETWEEN ' January-January -1981 ' and ' 3 January-December-81 ';--require all employee information to be queried in time range

3, judge whether is empty: is (not) null

Use this syntax to determine whether the content on a field is "null", but null and number 0 and an empty string are two concepts.

Copy Code code as follows:

SELECT * from EMP WHERE comm are not null;--query all employee information for bonuses
SELECT * from EMP WHERE isn't comm is NULL;
SELECT * from EMP WHERE comm is null;--query all employees who do not receive bonuses

4. The judgment of the specified range: in operator

The in operator represents the scope of a specified query

SELECT * from emp WHERE empno=7369 or empno=7566 or empno=7799;--query for employee information with employee number 7369, 7566, 7799,  
select * from emp W Here empno in (7369,7566,7799);--use in to query employee information with employee number 7369, 7566, 7799  
SELECT * from emp WHERE empno is not in (7369,7566,77 99) Use the not in action to query for employee information with employee number not 7369, 7566, 7799  
SELECT * from emp WHERE empno in (7369,7566,null);--using the in operator, Null exists in the scope of the query and does not affect the query;  

5, fuzzy query: LIKE clause
The function of the LIKE clause is to provide the operation of the Fuzzy Lookup, for example: Some programs appear on the search operation, all belong to the implementation of such clauses, but must be reminded that search engine queries are not like. But to use the LIKE clause you must know two matching symbols:

Copy Code code as follows:

Match a single character: _; -> A
Match any number of characters:%; -> 0, one, multiple

SELECT * from emp where ename like ' a% ';--asks to query all employee information that starts with the letter A in the employee's name  
SELECT * from emp where ename like ' _a% ';--asking for a query out of an employee's name The second letter is all employee information for a  
select * from emp where ename like '%a% ';--Request to query for employee  
SELECT * from emp where ename is not lik with letter A in employee's name E '%a% '--function to reverse operations using the NOT Operation  
SELECT * from emp WHERE ename like '%1% ' or ' hiredate like '%1% ' or sal like '%1% ';--for Lik The e clause, which can be represented on any data:

In development, the database's fuzzy query certainly uses the LIKE clause, but when using the LIKE clause, there is one of the biggest points of attention: if you do not set any query keywords on the fuzzy query ('%% '), it means that all records are queried:

Copy Code code as follows:

SELECT * from emp WHERE ename like ' percent ' or ' hiredate like ' percent ' or sal like '% ';

Iii. Sequencing of data

When the data returns the query results, all data is sorted by employee number by default, and, of course, you can now use the ORDER BY clause to specify the sort of action column that you want, and this time the SQL syntax is as follows:

Copy Code code as follows:

SELECT [DISTINCT] * | field [Alias] [, Field [alias]]
from table name [alias]
[WHERE condition (S)]
[Order BY Field [asc| DESC] [, Field [asc| DESC],...]];

The ORDER BY clause is what is written at the end of all SQL statements, and there are several explanations for sorting:
You can specify multiple sorted fields when sorting;
There are two ways to sort: 1, Ascending (ASC): default, not write is also ascending; 2, Descending (DESC): Users need to specify, from large to small sort;

The SELECT * from emp sequence by sal;--queries all employees for information, requiring that the SELECT * from emp orders by Sal ASC be sorted by payroll  
;  
SELECT * from EMP ordered by Sal desc;--in descending order  

For sorting operations, it is generally used only where needed, and it is important to remember that the ORDER BY clause is the last part of all SQL statements.