Oracle Notes (iv) Simple queries, qualifying queries, sorting data

Oracle Notes (iv) Simple queries, qualifying queries, sorting data

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 are well developed, primarily because Oracle is the world's first database product to adopt SQL statements.

SQL is powerful, and it can be summed up into the following groups:

• DML (data manipulation Language) operational language for retrieving or modifying data
• DDL data definition Language, which defines the structure of data, creates, modifies, or deletes database objects
• DCL Data Control Language language for defining 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:

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

from table name [alias];

Example: querying all records of a dept table

SELECT * FROM Dept;

Example: querying the number, name, and base salary of each employee

SELECT empno, ename, Sal from EMP;

Example: querying the positions of each employee

SELECT job from EMP;

This time found that the query out of the job content of duplicate data, and the reason why the data will be duplicated, the main reason is not to eliminate duplicate records now, you can use distinct eliminate all the duplicate content:

SELECT DISTINCT job from EMP;

However, for duplicate data, it refers to the repetition of the records for each column in a row, which is called duplicate data.

Example: find out the name and position of each employee

SELECT DISTINCT ename, job from EMP;

In the operation of a simple query, you can also use the arithmetic characters of each math.

Example: requires the name, position, and basic annual salary of each employee to be displayed

SELECT ename, Job, sal* from EMP;

But this time the display column appears a "sal*12", this must be displayed query column, but this column name is not easy to browse, so at this point can be an alias.

SELECT ename, Job, sal* income from EMP;

But for aliases, it is recommended not to use Chinese, as long as the development of the program, should avoid Chinese.

Example: because the company's welfare is very high, each month has 200 yuan of food subsidy and 100 yuan fare subsidy, this time of the annual salary

SELECT ename, Job, (sal+) * Income from EMP;

Example: The company will have a one-month base salary at the end of each year.

SELECT ename, Job, (sal+) * +salincome from EMP;

You can also use the ' | | ' in a simple query The field of the connection query.

Example: observing "| |" The use

SELECT Empno | | ', ' | | Ename from EMP;

Because "," is a string that is output as-is, it must be enclosed in "'", that is, in the SQL statement, "'" represents a string.

Example: requires that the current database be displayed in the following format:

The employee number is: 7369 the employee name is: SMITH, base salary is: 800, position is: clerk! "

Now equivalent to find: empno, ename, Sal, job fields, and use the ' | | ' To connect.

SELECT ' Employee number is: ' | | Empno | | ' The employee's name is: ' | | ename | | ', the base salary is: ' | | Sal | | ', Position is: ' | | Job | | ‘! ' Employee information from EMP;

It is important to remember that the content on the alias is not enclosed in "'" and that only the content that appears in the SELECT clause uses "'".

Second, limited query

In the previous simple query, all the records were displayed, but now you can filter the displayed records, which is the work of qualifying the query, which is the addition of a WHERE clause on the basis of the previous syntax to specify the qualification, at which time the syntax is as follows:

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

from table name [alias]

[WHERE condition (S)];

Multiple conditions can be added after the WHERE clause, the most common being the basic relational operations:>, >=, <, <=,! = (<>), between, and, like, in, are NULL, and, or, not;

1 , relational operations

Example: asking for all employee information with a base salary above 1500

SELECT * from emp WHERE sal>;

Example: find out all positions are clerk's employee information

SELECT * from emp WHERE job= ' clerk ';

The result of the query is not returned at this time, mainly because all the data is case-sensitive in the Oracle database, so the code is modified as follows:

SELECT * from emp WHERE job= ' clerk ';

The above just operates on one condition, and now you can manipulate multiple conditions, which can be connected using and OR OR.

Example: querying all employee information between payroll and 1500~3000

SELECT * from EMP

WHERE sal>= and sal<=;

Example: find out if a job is a clerk, or all the information of a salesperson

SELECT * from EMP

WHERE job= ' clerk ' OR job= ' salesman ';

Example: find out if a job is a clerk, or all the information of a salesperson, and ask them to pay more than 1200

SELECT * from EMP

WHERE (job= ' clerk ' OR job= ' salesman ') and sal>;

Example: Querying all employee information that is not a clerk

SELECT * from emp WHERE job<> ' clerk ';

SELECT * from emp WHERE job!= ' clerk ';

SELECT * from emp WHERE not job= ' clerk ';

2 , scope judgment: between ... And ...

The "between minimum and maximum value" represents a range of judging processes.

Example: ask for employee information about base pay in 1500~3000

SELECT * from EMP

 the ;

Example: It is now possible to between ... And ... Operation Negation

SELECT * from EMP

 the ;

"Between ... And ... "Operators are not only useful for numbers, but also for dates.

Example: asking for all employee information hired in 1981

Time range: 1981-01-01~1981-12-31, use the HireDate field to indicate the date of employment;

The contents of the HireDate field can be represented by a string: ' January-January-1981 ' ~ ' 3 January-December-81 '

SELECT * from EMP

WHERE hiredate between ' January-January -1981 ' and ' 3 January-December-81 ';

3 , judging whether it is empty: is (not) NULL

Use this syntax to determine whether the contents of a field are "null", but null and the number 0 and the empty string are two concepts.

Example: Find out all employee information for receiving bonuses

SELECT * from EMP

WHERE Comm is not NULL;

SELECT * from EMP

WHERE not comm is NULL;

Example: find out all employees who do not receive bonuses

SELECT * from EMP

WHERE Comm is NULL;

4 , the judgment of the specified scope: inch operator

The in operator represents the scope of a specified query, for example, there is now a query requirement:

Example: Querying employee information for employee number 7369, 7566, 7799

If you follow the earliest practices, use the or operation to complete;

SELECT * from EMP

WHERE empno=7369 or empno=7566 or empno=7799;

If you now use the new operator in, the code is simple;

SELECT * from EMP

WHERE empno in (7369,7566,7799);

And what if we're using not? is not in the specified range.

SELECT * from EMP

WHERE empno not in (7369,7566,7799);

Note: questions about not in

If the in operator is now used, NULL exists in the scope of the query and does not affect the query;

SELECT * from EMP WHERE empno in (7369,7566, NULL);

If the not-in operator is now in use, if NULL is present in the query scope, then all data is queried.

SELECT * from EMP WHERE empno not in (7369,7566, NULL);

For this limitation, remember now as a feature, and later explain why not NULL cannot appear in the in.

5 , fuzzy query: like clauses

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 kind clause implementation, but must be reminded that the search engine is not like the query.

But to use the LIKE clause you must recognize two matching symbols:

Match a single character: _; -A

Match any number of characters:%; 0, one, multiple

Example: ask to query all employee information that begins with the letter A in an employee's name

SELECT * from emp WHERE ename like ' A% ';

Example: asking for full employee information that the second letter in the employee's name is a

SELECT * from emp WHERE ename like ' _a% ';

Example: asking for an employee with the letter A in the employee's name

SELECT * from emp WHERE ename like '%a% ';

You can now also use the not operation to reverse the operation:

SELECT * from emp WHERE ename '%a% ';

However, for a LIKE clause, it is not always possible to represent it on string data, which can be represented on arbitrary data:

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

Description: about using the LIKE clause

In development, a database's fuzzy query must use the LIKE clause, but there is one big point to note when using the LIKE clause: If you do not set any query keywords on the fuzzy query (' percent '), the query is all recorded:

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

This feature can help users save a lot of code, so be sure to remember.

Third, the sorting of data

When the data returns a query result, all data is sorted by employee number by default, and, of course, the ORDER BY clause can now be used to specify the desired sort action column, at which point the SQL syntax 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 the SQL statements, and there are several explanations for sorting:

You can specify more than one sort of field when sorting.

There are two ways of sorting:

• Ascending (ASC): default, not write is also ascending;
• Descending (DESC): User needs to specify, from large to small sort;

Example: query all employees for information, ordered by salary

SELECT * from emp ORDER by Sal;

SELECT * from emp ORDER by Sal ASC;

Example: sorting in descending order

SELECT * from emp ORDER by Sal DESC;

Example: require all employee information to be queried, sorted by salary from highest to lowest, and if the wage is the same, the date of employment is sorted from early to late

There must be two fields sorted at this time: salary (DESC), employment Date (ASC);

SELECT * from emp ORDER by Sal DESC, HireDate ASC;

Sql> SELECT * from emp ORDER by Sal DESC, HireDate ASC;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7839 KING President 1 July-November-81 5000 10

7902 FORD ANALYST 7566 March-December-81 3000 20

7566 JONES MANAGER 7839 February-April-81 2975 20

7698 BLAKE MANAGER 7839 January-May-81 2850 30

7782 CLARK MANAGER 7839 September-June-81 2450 10

7499 ALLEN salesman 7698 20月-February-81 1600 300 30

7844 TURNER salesman 7698 August-September-81 1500 0 30

7934 MILLER Clerk 7782 2 March-January-82 1300 10

7521 WARD salesman 7698 2 February-February-81 1250 500 30

7654 MARTIN salesman 7698 2 August-September-81 1250 1400 30

7876 ADAMS Clerk 7788 2 March-May-87 1100 20

7900 JAMES Clerk 7698 March-December-81 950 30

7369 SMITH Clerk 7902 1 July-December-80 800 20

7788 SCOTT Clerk 7566 1 September-April-87 800 20

14 rows have been selected.

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

Oracle Notes (iv) Simple queries, qualifying queries, sorting data