Oracle Simple Query Qualified Query Data Sorting SQL Statement Example and Detailed Notes

1. Simple query

SQL (Structured Query Language), a 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.
The reason why Oracle database develops very well is mainly because Oracle is the earliest database product that adopts SQL statement in the world.
SQL is powerful and can be divided into the following groups:

DML ( Data Manipulation Language )   Data manipulation language, used to retrieve or modify data 

DDL ( Data Definition Language )   Data definition language, which is used to define the structure of data and create, modify or delete database objects 

DCL ( Data Control Language )   Data control language, which is used to define the permissions of the database 

Simple query refers to querying all the data in a table. The syntax of simple query is as follows:

SELECT [DISTINCT] * |  Field  [ Alias ] [, Field  [ Alias ]] FROM  Table name  [ Alias ];  

Example:

SELECT * FROM dept;-- Query dept All records of the table   
SELECT empno, ename, sal FROM emp;-- Find out the number, name and basic salary of each employee   
SELECT job FROM emp;-- Find out the position of each employee. At this time, I found the query job Duplicate data appears in the content.   
SELECT DISTINCT job FROM emp;-- Use DISTINCT Eliminate all duplicates. But for duplicate data, it refers to 1 The records of each column in the row are duplicate, which is called duplicate data.   
SELECT DISTINCT ename, job FROM emp;-- Find out the name and position of each employee   
SELECT ename, job, sal*12 FROM emp;-- Using various mathematical 4 Operator, requiring that every 1 Name, position and basic annual salary of employees   
SELECT ename, job, sal*12 income FROM emp;-- List for the displayed query 1 Alias, for aliases, it is recommended not to use Chinese, as long as it is the development of the program, it is necessary to avoid Chinese.   
SELECT ename, job, (sal+300)*12 income FROM emp;-- Because of the high welfare of the company, it is available every month 200 Yuan of food allowance and 100 Yuan fare subsidy, annual salary at this time   
SELECT ename, job, (sal+300)*12+sal income FROM emp;-- The company will send more at the end of each year 1 Basic monthly salary   
SELECT empno || ',' || ename FROM emp;-- You can also use the || "Joins the fields of the query.   
SELECT ' The employee number is: ' || empno || ' The name of the employee is: ' || ename || ' The basic salary is: ' || sal || ' The position is: ' || job || '! '  Employee information  FROM emp;-- It is required that the current database be displayed in the following format: "The employee number is: 7369 The name of the employee is: SMITH The basic salary is: 800 The position is: CLERK! "  

Because "," is a string output as is, it must be enclosed by "'", that is, in SQL statements, "'" represents a string.
1 Be sure to remember that the content on the alias is not enclosed by "'", but only the content appearing in the SELECT clause is enclosed by "'".

2. Limit queries

In the previous simple query, all records were displayed, but now the displayed records can be filtered, which belongs to the work of limited query. Limited query is to add an WHERE clause on the basis of the previous syntax to specify the limited conditions. At this time, the syntax is as follows:

SELECT [DISTINCT] * |  Field  [ Alias ] [, Field  [ Alias ]]   

FROM  Table name  [ Alias ]   

[WHERE  Condition (S)];  

Several conditions can be added after the WHERE clause, the most common of which is the basic relational operation: > , > =, < , < =,! = ( < > ), BETWEEN, AND, LIKE, IN, IS, NULL, AND, OR, NOT;

1. Relational operation

SELECT * FROM emp WHERE sal>1500;-- Ask to find out that the basic salary is higher than 1500 All employee information of   
SELECT * FROM emp WHERE job='clerk';-- Query all employees whose positions are clerks   
SELECT * FROM emp WHERE job='CLERK';-- The corresponding query results were not returned above, mainly because the Oracle In the database, all data is case sensitive   
SELECT * FROM emp WHERE sal>=1500 AND sal<=3000;-- Inquire about salary in 1500~3000 All employee information between multiple conditions can be used AND Or OR Make a connection operation   
SELECT * FROM emp WHERE job='CLERK' OR job='SALESMAN';-- Find out all the information about whether the position is clerk or salesperson   
SELECT * FROM emp WHERE (job='CLERK' OR job='SALESMAN') AND sal>1200;-- Find out all the information about whether the position is clerk or sales person, and ask these employees to earn more than 1200  
SELECT * FROM emp WHERE job<>'CLERK';-- Query all employees who are not clerks   
SELECT * FROM emp WHERE job!='CLERK';  
SELECT * FROM emp WHERE NOT job='CLERK'; 

2. Range judgment: BETWEEN … AND …

"BETWEEN minimum value AND maximum value" indicates the judgment process of one range. The "BETWEEN … AND …" operator is useful not only for numbers, but also for dates.

SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;-- Ask to find out the basic salary in 1500~3000 Employee information of    

SELECT * FROM emp WHERE NOT sal BETWEEN 1500 AND 3000;-- You can also right now BETWEEN … AND Operational negation    

SELECT * FROM emp WHERE hiredate BETWEEN '01-1 Month -1981' AND '31-12 Month -81';-- Require to query all employee information within the time range   

3. Judge whether it is empty: IS (NOT) NULL

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

SELECT * FROM emp WHERE comm IS NOT NULL;-- Query all employees who receive bonus information    

SELECT * FROM emp WHERE NOT comm IS NULL;   

SELECT * FROM emp WHERE comm IS NULL;-- Find out all employees who do not receive bonuses   

4. Judgment of specified range: IN operator

The IN operator represents a scope that specifies 1 query

SELECT * FROM emp WHERE empno=7369 OR empno=7566 OR empno=7799;-- Use OR Operation found that the employee number is 7369 , 7566 , 7799 Employee information of   
SELECT * FROM emp WHERE empno IN (7369,7566,7799);-- Use IN Operation found that the employee number is 7369 , 7566 , 7799 Employee information of   
SELECT * FROM emp WHERE empno NOT IN (7369,7566,7799); Use NOT IN Operation found that the employee number is not 7369 , 7566 , 7799 Employee information of   
SELECT * FROM emp WHERE empno IN(7369,7566,null);-- Used IN Operator, the scope of the query exists null , does not affect the query;   
SELECT * FROM emp WHERE empno NOT IN(7369,7566,null);-- Using the NOT IN Operator, if there is a query scope null It means querying all the data.  

5. Fuzzy query: LIKE clause
The function of LIKE clause is to provide fuzzy search operation, for example, the search operation appearing on some programs belongs to the realization of LIKE clause, but it must be reminded that the query on search engine is not LIKE. However, to use the LIKE clause, you must recognize two matching symbols:

 Match a single character: _; -> 1 A 

 Match any number of characters: %; -> 0 A, 1 A number of 

SELECT * FROM emp WHERE ename LIKE 'A%';-- Require to query employee names with letters A All employee information at the beginning   
SELECT * FROM emp WHERE ename LIKE '_A%';-- Request to find out the first in the employee's name 2 The letters are A All employee information of   
SELECT * FROM emp WHERE ename LIKE '%A%';-- Ask to find out the employee name with letters A Employees of   
SELECT * FROM emp WHERE ename NOT LIKE '%A%';-- Use NOT Operation, the function of negating the operation   
SELECT * FROM emp WHERE ename LIKE '%1%' OR hiredate LIKE '%1%' OR sal LIKE '%1%';-- For LIKE Clause, which can be represented on arbitrary data: 

In development, the fuzzy query of database definitely uses LIKE clause, but there is one biggest note when using LIKE clause: If you don't set any query keywords on the fuzzy query ('%%'), it means querying all records:

SELECT * FROM emp WHERE ename LIKE '%%' OR hiredate LIKE '%%' OR sal LIKE '%%';  

3. Sorting of data

When the data returns the query result, all the data is sorted by employee number by default. Of course, you can now use the "ORDER BY" clause to specify the action column that needs to be sorted. At this time, the syntax of SQL is 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 written at the end of all SQL statements and has the following instructions for sorting:
You can specify multiple sorted fields when sorting;
There are two ways to sort: 1. Ascending (ASC): By default, it is also ascending without writing; 2. Descending order (DESC): The user needs to specify and sort from big to small;

SELECT * FROM emp ORDER BY sal;-- Query the information of all employees and ask for sorting by salary   
SELECT * FROM emp ORDER BY sal ASC;  
SELECT * FROM emp ORDER BY sal DESC;-- Arrange in descending order   
SELECT * FROM emp ORDER BY sal DESC, hiredate ASC;-- Query all the employee information, according to the salary from high to low sort, if the salary is the same, according to the employment date from early to late sort  

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