Oracle SQL Foundation (II): SELECT statement

In order to query the data from the database, you need to use the most SELECT statement in the SQL language. We introduce the basic syntax of the SELECT statement, subqueries, query data from multiple tables, and then parse the instance.

first, the basic syntax of the SELECT statement

The SELECT statement is like a filter superimposed on a database table, that is, the selection query is used to locate database-specific columns and rows. The following is the underlying syntax for the SELECT statement.

SELECT [all| DISTINCT select_list

from {Table_name|view_name}

[WHERE search_condition]

[GROUP by Group_by_expression]

[Having search_condition]

[ORDER by Order_by_expression [asc| DESC]]

The expression enclosed in brackets [] is optional, and the expression enclosed in curly braces {} must select one of them. Here we parse each clause of the SELECT statement in detail.

1.SELECT clause

(1) SELECT clause: Specify the columns returned by the query, you can return more than one column at a time, separated by ",". Column aliases can be defined with spaces or keywords as.

(2) In the SELECT clause, you should avoid using wildcards * To query data that meets certain criteria as much as possible to improve query efficiency.

(3) The function of the keyword DISTINCT is deduplication, the default is all, displaying all data.

2.FROM clause

(1) FROM clause: Used to specify the table or view to be queried, separate tables or views with ",", spaces, or keyword as to define column aliases.

(2) Full format FROM clause: from User_name.table.name; (User name. Table name)

3.WHERE clause

(1) WHERE clause: Specifies the search criteria for the returned rows in the retrieved table, without which all rows of the default satisfy the criteria.

(2) Comparison operators:>, <, >=, <=, =, <>,! =, between ... And ...

(3) Logical operators: And, OR, not

(4) Multiline operator: in (set), any (condition), all (Conditon)

(4) In addition to the above operators, there is the is null/is not NULL condition, like condition (% pass all characters, _ with one character)

4.GROUP BY clause

(1) GROUP BY clause: divides rows in a table into groups, and you can use group functions to return summary information for each group.

(2) A GROUP BY clause can contain more than one column, cannot use a column alias, and cannot use where to constrain the grouping result.

(3) Group functions: AVG, SUM, MAX, MIN, COUNT, STDDEV (Standard deviation), VARIANCE (variance).

5.HAVING clause

(1) HAVING clause: Specifies the filter criteria for a group or aggregation, which is typically used in combination with the GROUP BY clause.

(2) The having filter can use the same operator as where, to constrain the groupings created by the group by.

6.ORDER BY clause

(1) ORDER BY clause: Specifies the rows returned by the sort display, ASC is sorted in ascending order (default), and Desc is sorted in descending order.

(2) unless the FOR UPDATE clause is used, the ORDER by clause should be put last.

(3) An ORDER BY clause can specify an expression, an alias as a sort condition, or a multi-column sort.

Second, sub-query

A subquery is a SELECT statement, which is a clause that is embedded in another SELECT statement. Using subqueries You can build powerful statements with simple statements. They are useful when you need to select rows from a table with data that depends on the table itself.

You can put subqueries in many SQL clauses, including: WHERE clause, HAVING clause, FROM clause, CREATE VIEW statement, CREATE TABLE statement, UPDATE statement, Insert statement in the INTO clause and in the SET clause of the Updata statement.

A subquery must be placed in parentheses. Place the subquery on the right side of the comparison condition to increase readability. Sub-query According to query results can be divided into: single-row sub-query, multiline subquery, Dolez query.

Iii. querying data from multiple tables (multiple table connections)

Querying data from multiple tables is also known as a multi-table connection, which allows you to retrieve data from two or more tables based on the logical relationships between the tables. The connection types for ORACLE SQL are: internal, external, and self-connected. The inner connection is divided into: equivalent connection, non-equivalent connection. The outer connection is divided into: Left outer connection, right outer connection, full outer connection. In addition, we will introduce the following SQL99 standard connection syntax.

In the process of multi-table connection, using table aliases can speed up database access, help save SQL code smaller, and save storage.

1. Internal connection

Inner joins eliminate rows that do not match another table when at least one row in the two table meets the join condition to return rows. The inner connection is divided into equivalent connections and non-equivalent connections.

(1) Equivalent connection

The equivalent connection is one of the most used connections, the equivalent connection can be imagined as a large table, split it into two sub-tables, and two sub-tables contain a column of large table (also may be multi-column), and the equivalent connection is to regroup two child tables into a large table.

Main syntax: ... WHERE Table1.column=table2.column.

(2) Non-equivalent connection

A non-equivalent connection is a connection condition that differs from an equivalent operation (=), such as; WHERE Table1.column between table2.min (column) and Table2.max (column).

2. External connection

An outer join returns all rows in at least one of the tables or views mentioned in the FROM clause, as long as those rows conform to any where or having search condition. All rows in the left table referenced by the left outer join are retrieved, along with all rows in the right table referenced by the right outer join, and all rows in the two table are put back in the full outer join.

(1) Left outer connection

The data list includes all rows from the left table that satisfy the query criteria. To display rows in table1 that do not meet the criteria, the following two statements are available:

#第一种方式SELECT table1.column1,table2.column2from table1,table2WHERE table1.column1= Table2.column2 (+) #第二种方式SELECT table1.column1,table2.column2JOIN table2 on( Table1.column1=table2.column2)             

Left OUTER JOIN

(2) Right outer connection

The data list includes all rows for the right-hand table that satisfy the query criteria. To display rows in table2 that do not meet the criteria, the following two statements are available:

#第一种方式 SELECT Table1.column1,table2.column2  from Table1,table2 WHERE table1.column1 (+)=table2.column2# second way SELECT  Table1.column1,table2.column2from rightOUTERJOIN  table2   on (table1.column1=table2.column2)

Right OUTER JOIN

(3) Full-outer connection

The data contains all the columns that satisfy the query criteria, in order to display rows in table1 and table2 that do not meet the criteria, the following statements are available:

SELECT Table1.column1,table2.column2  from  Full OUTER JOIN table2  on (Table1.column1=table2.column2)

Full OUTER JOIN

3. Self-connect

The two tables from which the connection is connected are all from the same column and can be either equivalent or non-equivalent connections. Self-join is a very small form of connection that can be replaced with subqueries. The following is an example of a self-join.

SELECT || '  '| | M.last_name  from Employees W, Employees m WHERE = m.employee_id;

Self JOIN

4.sql1999 Connection Syntax

The SQL1999 standard, referred to as SQL99, is the SQL standard that is defined by the International Standards Organization in 1999, and the first few connection syntaxes are unique to Oracle, and we describe the connection syntax for SQL99 in Oracle.

(1) Cross connection

Equivalent to two tables without a filter condition, returns the set of Cartesian product operations that satisfy the query criteria record.

Main syntax: ... From table1 cross JOIN table2.

(2) Natural connection

Equivalent to an equivalent join, provided that two tables have the same name column. If not, it is a cross-connect. If the column name is the same, but the column type is different, an error occurs, and the using clause can be used to handle the situation.

Main syntax: ... From table1 NATURAL JOIN table2.

(3) on clause

Equivalent to equivalent connections and non-equivalent connections. For a connection condition of a natural connection, it is basically an equivalent connection of all columns with the same name, in order to specify any condition, or to specify the column to concatenate, you can use the ON clause to connect. The following is an example of creating a three-to-one connection with an ON clause.

Select employee_id, city, Department_name  from Employees e Join  on = e.department_id Join  on = l.location_id;

On clause

Four, select statement instance parsing

For the sake of intuitive understanding, we first introduce the table structure of the Department table (DEPT), Employee table (EMP), Payroll scale (Salgrade) under the Scott user.

1. Department Table (DEPT)

Serial number	Name	Type	Describe
1	DEPTNO	Number (2)	Represents the department number, consisting of two digits
2	Dname	VARCHAR2 (14)	Department name, up to a maximum of 14 characters
3	LOC	VARCHAR2 (13)	Department location

2. Employee List (EMP)

Serial number	Name	Type	Describe
1	EMPNO	Number (4)	Number of employees, consisting of four digits
2	Ename	VARCHAR2 (10)	The name of the employee, consisting of 10-bit characters
3	JOB	VARCHAR2 (9)	Position of employee
4	MGR	Number (4)	Employee's corresponding leader number
5	HireDate	DATE	Date of employee employment
6	SAL	Number (7,2)	Base pay, where two decimal places, five-bit integers
7	COMM	Number (7,2)	Bonuses, gold
8	DEPTNO	Number (2)	Department number where the employee is located

3. Salary scale (Salgrade)

Serial number	Name	Type	Describe
1	GRADE	Number	Level of wages
2	Losal	Number	Minimum wage at this level
3	Hisal	Number	Maximum wage at this level

Now, we need to find out employees with a salary level of 2-4 after January 1, 1981, and group them by department number to find the department number (DEPTNO), department name (DNAME), number of employees (NUM_EMPNO), minimum wage (min_sal) that meet the criteria. ), the highest salary (max_sal), the average salary (mean_sal), the average salary (Sal_grade), and then the search results are sorted by the "wage level" and "Maximum Salary" two fields. The query results are as follows

This example is equivalent to a simple data modeling process that uses subqueries and multi-table joins in SELECT statements to query and consolidate information under specific requirements. The detailed code is as follows

SELECTNew_deptno Deptno,--Department NumberDept.dname,--Department NameNum_empno,--number of department employeesMin_sal,--Departmental minimum wageMax_sal,--Department Maximum salaryMean_sal,--Department Average salary       --find out the salary level of the department average salary        Case        whenMean_salbetween(SelectLosal fromSalgradewhereGrade=1) and(SelectHisal fromSalgradewhereGrade=1)        Then 1        whenMean_salbetween(SelectLosal fromSalgradewhereGrade=2) and(SelectHisal fromSalgradewhereGrade=2)        Then 2        whenMean_salbetween(SelectLosal fromSalgradewhereGrade=3) and(SelectHisal fromSalgradewhereGrade=3)        Then 3        whenMean_salbetween(SelectLosal fromSalgradewhereGrade=4) and(SelectHisal fromSalgradewhereGrade=4)        Then 4       ELSE 5       ENDSal_grade from-----Select the department number that meets the criteria, the number of department employees, the department's minimum wage, the department's highest salary, and the average salary of the department(    SELECTnewemp. Deptno New_deptno,Count(distinctempno) Num_empno,min(SAL) min_sal,Max(SAL) max_sal,avg(SAL) mean_sal from    -----Select all information for employees with a salary level of 2-4 years after January 1, 1981    (        SELECT *  fromEMPWHEREHireDate>To_date ('1981-1-1','YYYY-MM-DD')          andSalbetween(SELECTLosal fromSalgradewhereGRADE=2) and(SELECTHisal fromSalgradeWHEREGRADE=4)) NewempGROUP  byNewemp.deptno) Left OUTER JOINDept on(New_deptno=Dept.deptno)ORDER  bysal_grade,max_sal;

SELECT statement instance

Oracle SQL Foundation (II): SELECT statement