SQL statement examples and detailed annotations for simple queries, restricted queries, and data sorting in Oracle

1. Simple Query

Structured Query Language (SQL) is a database Query and programming Language used to access data and Query, update, and manage relational database systems. ANSI (American National Standards Institute) claims that SQL is the standard language for relational database management systems.
Oracle databases have developed very well, mainly because Oracle is the world's first database product that uses SQL statements.
SQL is powerful. In summary, it can be divided into the following groups:

Copy codeThe Code is as follows: DML (Data Manipulation Language) Data operation Language, used to retrieve or modify Data
Data Definition Language (DDL), used to define the Data structure, create, modify, or delete database objects.
DCL (Data Control Language) Data Control Language, used to define database Permissions

A simple query refers to querying all the data in a table. the syntax of a simple query is as follows:
Copy codeThe Code is as follows: SELECT [DISTINCT] * | field [alias] [, field [alias] FROM table name [alias];

Example:

SELECT * FROM dept; -- Query all records in the dept table SELECT empno, ename, sal FROM emp; -- query the number, name, and basic salary of each employee SELECT job FROM emp; -- query the positions of each employee. At this time, duplicate data is found in the queried job content. Select distinct job FROM emp; -- use DISTINCT to remove all duplicate content. For duplicate data, the record of each column in a row is repeated. Select distinct ename, job FROM emp; -- query the names and positions of each employee, SELECT ename, job, sal * 12 FROM emp; -- use the four Arithmetic Operators of each mathematics, each employee's name, position, basic annual salary SELECT ename, job, sal * 12 income FROM emp; -- an alias for the displayed query column. For aliases, we recommend that you do not use Chinese characters. As long as the program is developed, you must avoid Chinese characters. SELECT ename, job, (sal + 300) * 12 income FROM emp; -- because of the high welfare of the company, 200 yuan of meal subsidies and 100 yuan of fare subsidies are provided each month, SELECT ename, job, (sal + 300) * 12 + sal income FROM emp; -- the company will pay one more month's basic salary at the end of each year. SELECT empno | ', '| ename FROM emp; -- you can also use' | 'to connect to the queried field in a simple query. SELECT 'employee ID: '| empno |' the employee name is '| ename |'. The basic salary is '| sal | ', job title: '| job | '! 'Employee information FROM emp; -- the current database is required to display in the following format: "employee ID: 7369: employee name: SMITH, basic salary: 800, position: CLERK!"

Because "," is an output string, it must be enclosed by "'". That is, "'" indicates a string in an SQL statement.
Be sure to remember that the content on the alias should not be included in the 'clause, but the content only appears in the SELECT clause.

Ii. Restricted Query

In the previous simple query, all the records are displayed, but now you can filter the displayed records, which is a restricted query, a where clause is added based on the previous syntax to specify the conditions. The syntax is as follows:
Copy codeThe Code is as follows: SELECT [DISTINCT] * | field [alias] [, field [alias]
FROM table name [alias]
[WHERE condition (S)];
You can add multiple conditions after the WHERE clause. The most common conditions are basic relational operations:>, >=, <, <= ,! = (<>), BETWEEN, AND, LIKE, IN, is null, AND, OR, NOT;

1. relational operation

SELECT * FROM emp WHERE sal> 1500; -- Query all employees whose basic salary is higher than 1500. SELECT * FROM emp WHERE job = 'cler '; -- query the employee information of all clerks. SELECT * FROM emp WHERE job = 'cler'; -- the query result is not returned, mainly because it is in the Oracle database, all data is case-sensitive SELECT * FROM emp WHERE sal> = 1500 AND sal <= 3000; -- the query salary is between 1500 AND ~ All employee information between 3000. You can use and or to connect multiple conditions. SELECT * FROM emp WHERE job = 'cler' OR job = 'salesman '; -- SELECT * FROM emp WHERE (job = 'cler' OR job = 'salesman') AND sal> 1200 if the job is a clerk or a salesperson; -- Query all information about the job as a CLERK or salesperson and require these employees to pay more than 1200 SELECT * FROM emp WHERE job <> 'cler '; -- Query all employee information that is not a clerk SELECT * FROM emp WHERE job! = 'Cler'; SELECT * FROM emp where not job = 'cler ';

2. Range judgment:... AND...

"BETWEEN minimum AND maximum" indicates a range judgment process. "... AND ..." Operators are not only useful for numbers, but also for dates.
Copy codeThe Code is as follows: SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000; -- the basic salary must be 1500 ~ 3000 of employees
SELECT * FROM emp where not sal BETWEEN 1500 AND 3000; -- you can also... AND... Reverse operation
SELECT * FROM emp WHERE hiredate BETWEEN '01-January-1981 'AND '31-December-81'; -- Query all employee information within the specified time range

3. Determine if it is null: IS (NOT) NULL

This syntax can be used to determine whether the content of a field is "null", but null, number 0, and null string are two concepts.
Copy codeThe Code IS as follows: SELECT * FROM emp WHERE comm is not null; -- Query Information about all employees who receive the bonus
SELECT * FROM emp where not comm is null;
SELECT * FROM emp WHERE comm is null; -- Query all employees who do not receive the bonus

4. Determine the specified range: IN Operator

The IN operator specifies a query range.

SELECT * FROM emp WHERE empno = 7369 OR empno = 7566 OR empno = 7799; -- the "OR" operation is used to query information of employees whose IDs are 7369, 7566, and 7799. SELECT * FROM emp WHERE empno IN (7799 ); -- The IN operation is used to query information about employees whose employee numbers are 7369, 7566, and 7799. SELECT * FROM emp WHERE empno not in (72.16,7566, 7799 ); the not in operation is used to query information about employees whose employee numbers are NOT 7369, 7566, and 7799. SELECT * FROM emp WHERE empno IN (72.16,7566, null); -- The IN operator is used, the query range contains null, which does NOT affect the query. SELECT * FROM emp WHERE empno not in (7369 , 7566, null); -- the not in operator is used. If null exists IN the query range, all data is queried.

5. fuzzy query: LIKE clause
The LIKE clause function provides fuzzy search operations. For example, all search operations on some programs belong to the implementation of the LIKE clause, but it must be reminded that, the query on the search engine is not LIKE. However, to use the LIKE clause, you must recognize two matching symbols:
Copy codeThe Code is as follows: match a single character: _;-> 1
Match any number of characters: %;-> 0, 1, and multiple

SELECT * FROM emp WHERE ename LIKE 'a % '; -- Query all employee information starting with the letter A in the employee's name. SELECT * FROM emp WHERE ename LIKE' _ A % '; -- Query all employee information with the second letter A in the employee name. SELECT * FROM emp WHERE ename LIKE '% A % '; -- query the employee SELECT * FROM emp WHERE ename not like '% A %' with the letter A in the employee name. -- use NOT, SELECT * FROM emp WHERE ename LIKE '% 100' OR hiredate LIKE' % 100'; -- for the LIKE clause, it can be expressed on any data:

During development, the LIKE clause is certainly used for Fuzzy queries in the database, but there is a biggest note when using the LIKE clause: if no keyword is set for fuzzy search ('%'), all records are queried:
Copy codeThe Code is as follows: SELECT * FROM emp WHERE ename LIKE '%' OR hiredate LIKE '%' OR sal LIKE '% ';

Iii. Data Sorting

After the data returns the query results, all data is sorted BY employee numbers BY default. Of course, you can also use the "order by" clause to specify the operation columns to be sorted, the SQL syntax at this time is as follows:
Copy codeThe Code 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 describes the sorting as follows:
Multiple sorting fields can be specified during sorting;
There are two sorting methods: 1. Ascending Order (ASC): by default, it is also ascending without writing; 2. Descending Order (DESC): You need to specify the order from large to small;

SELECT * FROM emp order by sal; -- Query Information of all employees. SELECT * FROM emp order by sal ASC according to the wage ORDER; SELECT * FROM emp order by sal DESC; -- SELECT * FROM emp order by sal DESC, hiredate ASC in descending ORDER; -- Query all employee information, sorted BY salary FROM high to low. If the salary is the same, sort by employment date from morning to night

For sorting operations, the order by clause is generally used only as needed, and it must be remembered that the order by clause is the last part of all SQL statements.