3-3 data Query

3-3 data Query

Select definition

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]    * | expression [ AS output_name ] [, ...]    [ FROM from_item [, ...] ]    [ WHERE condition ]    [ GROUP BY expression [, ...] ]    [ HAVING condition [, ...] ]    [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]    [ FOR UPDATE [ OF tablename [, ...] ] ]    [ LIMIT { count | ALL } ]    [ OFFSET start ]

Select definition Interpretation

The From_item can be:
[ONLY] table_name [*]
[[As] alias [(Column_alias_list)]]
|
(select)
[As] alias [(Column_alias_list)]
|
From_item [NATURAL] Join_type From_item
[On Join_condition | USING (Join_column_list)]

Expression
The column/field name of the table or an expression.

Output_name
Use the AS clause to declare another name for a column/field or an expression. This name is used primarily to mark output columns for display. It can represent the value of a column/field in the ORDER by and GROUP by clauses. However, Output_name cannot be used in a WHERE or HAVING clause, instead of an expression.

From_item
A table reference, a subquery, or a JOIN clause. See below.

Condition
A Boolean expression that gives a true or false result. See the WHERE and having clauses described below.

Select
A SELECT statement that can have all attributes except the ORDER by,for UPDATE, and the LIMIT clause (even those statements can be used if parentheses are added)
The from term can include:

table_name
The name of an existing table or view. If only is declared, only the table is scanned. If none is declared, the table and all its derived tables (if any) are scanned. You can follow the table name followed by a * to represent the sweep, but in the current version, this is the default feature. (In versions prior to PostgreSQL 7.1, only is the default feature.) ）

Alias
Used in front of the table_name. The replacement name, used to abbreviate or eliminate the ambiguity of a table since it was connected. (The same table will be scanned several times at this time.) If you write an alias, you can also write a list of field aliases, providing alternate names for one or several fields of the table.

Select
A subquery that appears in the FORM clause. Its output acts as if it was creating a temporary table for this SELECT command during its lifetime. Please note that this subquery must be surrounded by a garden parenthesis. And you have to alias it.

Join_type
[INNER] Join, left [OUTER] join, right [OUTER] join, full [OUTER] join, or cross JOIN. One of For INNER and OUTER connection types, one of the NATURAL on Join_condition, or USING (join_column_list), must appear. For cross joins, none of the above items can appear.

Join_condition
A condition limit. Similar to the WHERE condition, except that it applies only to the two from_item connected in this join clause.

Join_column_list
A USING field list (A, B, ...) is the on condition Left_table.a = right_table.a and left_table.b = right_table.b ... The abbreviation.

Rows
The query that you declared returns the rows of all result sets.

Count
The count of rows returned by the query.

Describe
SELECT returns the row of records from one or more tables. The selected Hou Sheng is all rows that satisfy the WHERE condition. Or, if the WHERE statement is omitted, all rows in the table are selected. (see WHERE clause).

In fact, the rows returned are not the rows generated directly by the From/where/group by/having clause; in fact, the output line is formed by calculating the select output expression for each selected row. You can write a * on the output list that represents all the columns of the selected row. Also we can take table_name.* to represent the line from the table.

DISTINCT will remove all duplicate rows from the selected result set. All (the default) returns all Hou Sheng, including duplicate rows.

DISTINCT on deletes rows that match all of the expressions you declare, leaving only the first row of each repeating set. The DISTINCT on expression is explained by the same rules as the ORDER by entry, see below. Note that the "first row" of each repeating set here is unpredictable, unless we use order by to ensure that the row we want is the first to appear. For example

    SELECT DISTINCT ON (location) location, time, report    FROM weatherReports    ORDER BY location, time DESC;

Retrieves the most recent weather forecast for each region. But if we don't use order by to force each region to go through the time-value sequence, we get a report of unpredictable times for each region.

The GROUP BY clause allows a user to divide a table into groups of different rows that match one or more numeric values. (Refer to the GROUP by clause.) ）

Having allows you to select only those row groups that satisfy the declaration criteria (see HAVING clause. ）

Order by causes the rows returned to be in the order in which they are declared. If an order by is not given, the order of the output rows is generated in the order in which the system considers the cost to be minimal. (see ORDER by clause.) ）

Multiple SELECT queries can be combined with union,intersect, and EXCEPT operators. Use parentheses to determine the order of these operators, if necessary.

The UNION operator calculation is a collection of rows that are returned by those participating in the query. If all is not declared, then duplicate rows are deleted. (see UNION clause.) ）

INTERSECT gives two queries for a common row. If all is not declared, then duplicate rows are deleted. (see INTERSECT clause.) ）

EXCEPT gives the row that exists in the first query and does not exist in the second query. If all is not declared, then duplicate rows are deleted. (see EXCEPT clause.) ）

The FOR UPDATE clause allows the SELECT statement to perform an exclusive lock on the selected row.

The LIMIT clause allows the user to return a subset of the results generated by a query. (See the LIMIT clause.) ）

You must have SELECT permission to read values from the table (see Grant/revoke statement. ）

Each clause is detailed in the FROM clause

The FROM clause declares one or more source tables for SELECT. If more than one source table is declared, the conceptual result is the Cartesian product of all rows of all source tables-but usually increases the restriction to limit the rows returned to a small subset of the Cartesian product.

If the from item is a simple table name, it implicitly includes rows from the table's child table (inheriting child tables). Only the rows from the table's child tables will be eliminated. Before PostgreSQL 7.1, this was the default result, and the row to get the child table was implemented by appending a * after the table name. This old-fashioned property can be sql_inheritance to OFF by command SET; Gets

The from item can also be a parenthesized subquery (note that the subquery requires an alias clause!). ）． This feature is useful because it is the only way to get multi-layered groupings, aggregates, or sorts in a single query.

Finally, the from item can be a JOIN clause that combines two simple from items. (use parentheses when necessary to describe the nesting order.) ）

A cross join or INNER join is a simple Cartesian product, as you would get from the two items listed in the top-level from. The cross join is equivalent to INNER join on (TRUE), which means that no rows are deleted by the condition. These connection types are only handy for symbols, because they do not do much more than you only use from and WHERE.

The left OUTER JOIN returns all the qualifying Cartesian product (that is, all the combined rows that match its on condition), plus all rows in the right-hand side table that do not have an on-hand line that matches the on condition. Such a left-hand line is extended to full length by inserting a NULL extension to the right-hand side line. Please note that when deciding which rows are eligible, only the JOIN's own on or USING is considered. Then consider the on or WHERE condition of the outer layer.

Instead, the right OUTER JOIN returns all the connected rows, plus all unmatched left-hand rows (insert NULL extension to full-length on hand). This sentence is simply symbolic, because you can change the left and right inputs instead of using the OUTER JOIN.

The full OUTER join returns all the connection rows, plus all unmatched left-hand rows (the right insert null expands to full length), plus all unmatched right-hand lines (insert null extension to full length on the left hand side).

In addition to all join types except the cross join, you must write on Join_condition, USING (join_column_list), and one of the NATURAL. In most cases it will be on: you can write any conditional expression that involves two connection tables. USING Field List (A, B, ...) ) is on condition Left_table.a = right_table.a and left_table.b = right_table.b ... The abbreviation. In addition, USING assumes that only one of the two peers is contained in the JOIN output, not two. NATURAL is an abbreviation for the USING list of all similar name fields in the table.

WHERE clause

The optional where condition has the following common form:

WHERE boolean_expr    boolean_expr 可以包含任意个得出布尔值的表达式。通常表达式会是     expr cond_op expr    或 log_op expr

Here cond_op can be: =,<,<=, >,>= or <>, or conditional operations character all,any,in,like, etc., or user-defined operators, and log_op can be: And,or,not. SELECT ignores all rows where the WHERE condition is not true.

GROUP BY clause

Group by declares a table that is divided into groups, which is derived from the application using the following clause:

GROUP by expression [, ...]

GROUP by will compress all rows that share the same value on the combined columns into one row. If there are aggregate functions, these aggregate functions calculate all rows for each group and calculate a separate value for each group (if there is no group BY, the aggregation function calculates a value for all rows selected). When GROUP by is present, the SELECT output expression is illegal for any non-combined column except in the aggregate function, because there is more than one possible return value for a non-combined column.

An entry in GROUP by can also be either the name of the output column or the ordinal (SELECT expression), or an arbitrary expression that forms a numeric value from the input column. When there is semantic ambiguity, a GROUP by name is interpreted as an input column/field name instead of an output column/field name.

Union clause

Table_query UNION [All] Table_query
[ORDER by expression [ASC | DESC | USING operator] [, ...]
[LIMIT {Count | All} [{OFFSET |,} start]]
Here Table_query indicates any selection expression without an ORDER by,for UPDATE, or a LIMIT clause. (if surrounded by parentheses, ORDER by and LIMIT can be placed in sub-expressions.) If there are no parentheses, these clauses will be used for the result of the UNION instead of the input expression to their right-hand side. ）

The result set of the UNION operator is the collection of results returned by all the queries involved. The two SELECT that are the UNION direct operands must produce the same number of fields, and the corresponding fields must have compatible data types.

By default, the result of the UNION does not contain any duplicate rows unless the ALL clause is declared. All stop the elimination of repetitive actions.

Multiple UNION operators in the same SELECT statement are computed from left to right, unless they are identified by parentheses.

Currently, for UPDATE cannot be declared in the result or input of the UNION.

INTERSECT clause

Table_query INTERSECT [All] Table_query
[ORDER by expression [ASC | DESC | USING operator] [, ...]
[LIMIT {Count | All}]
[OFFSET start]
Here Table_query declares any selection expression that does not have an ORDER by,for UPDATE, or a LIMIT clause.

INTERSECT is similar to UNION, except that it gives rows that appear in two queries, not all rows of two queries.

The result of INTERSECT does not contain any duplicate rows unless you declare the ALL option. After using all, one has m repeats in L and N repeats in R will appear min (m,n) times.

Multiple INTERSECT operators in the same SELECT statement are computed from left to right, unless the order is indicated by parentheses. INTERSECT is more tightly bound than union-that is, a union B INTERSECT C will read a Union (b INTERSECT c) Unless you declare it in parentheses.

except clause

Table_query EXCEPT [All] Table_query
[ORDER by expression [ASC | DESC | USING operator] [, ...]
[LIMIT {Count | All}]
[OFFSET start]
Here Table_query declares any selection expression that does not have an ORDER by,for UPDATE, or a LIMIT clause.

EXCEPT is similar to UNION, except that EXCEPT gives rows that exist on the left query output and do not exist on the right side of the query output.

The result of EXCEPT does not contain any duplicate rows unless the all option is declared. With all, a line with M repeats in L and a row with n duplicates in R will appear Max (m-n,0).

Multiple EXCEPT operators in the same SELECT statement are computed from left to right, unless the order is indicated by parentheses. The EXCEPT and UNION binding levels are the same.

Limit clause

LIMIT {Count | All}
OFFSET start
Here the count declares the maximum number of rows to return, while the start declaration starts the number of rows that are ignored before the row is returned.

LIMIT allows you to retrieve a portion of a row that is generated by another part of the query. If a limit count is given, the number of rows returned does not exceed which limit. If an offset is given, the number of rows is ignored before the start of the return row.

When using limit, a good habit is to use an ORDER BY clause to limit the result rows to a unique order. Otherwise you will get an unpredictable subset of queries returned-you might want lines tenth through 20th, but in what order? Unless you declare order BY, you don't know what the order is.

Starting with PostgreSQL 7.0, the query optimizer takes the limit into account when generating query planning, so you are likely to get different plans for different values of limit and OFFSET (generating different lines). Therefore, selecting a subset of different query results with different limit/offset values will not produce consistent results unless you use ORDER by to force a predictable order of results. This is not a problem, because SQL is inherently unique, because SQL does not guarantee any particular order of results generated by the query unless the order BYE constraint sequence is used.

exists clauses and NOT EXISTS statements

exists represents the existence of quantifiers, a subquery with a exists predicate does not return any data, only the logical truth TRUE or the logical false value false when using exists, if the inner query result is not empty, the outer WHERE clause returns TRUE, otherwise the false value is returned

Select Sname
From student
where exists (SELECT * from SC where sno = Student.sno and CNO = ' 1 ')
Execution process: First take the outer query in the student table of the first tuple, according to its domain memory query related property value processing inner query, if the WHERE clause return value is true, then take the sname of the tuple in the outer query into the result table, and then take the student table of the next tuple, repeat the process Until the outer stuent table is all checked out.

Inner link, left outer connection, right outer connection, inner join ... on

>语句格式:FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X

On Member. Field number = table X. Field number

SELECT *
From (((Member INNER join Membersort on Member.membersort=membersort.membersort) INNER join Memberlevel on Member.memberl Evel=memberlevel.memberlevel)
INNER join memberidentity on member.memberidentity=memberidentity.memberidentity) INNER join wedlock on member.wedlock= Wedlock.wedlock
ORDER by Memberdate DESC
The function of an inner join IS to list the records associated with the two tables, which must be associated records.

Left outer connection, outer join ... on

Statement format:
from [table name 1] LEFT join [table name 2]
on [table name 1. field A]〈 relational operator 〉[table name 2. Field B]
In fact, the function of the leftmost join is to leave the table All records in Name 1 are reserved, and field B in Table name 2 on the right is displayed with the record corresponding to table name 1. field a (when using SELECT *). The right join
is the opposite of the left join. The function of the
outer join is to keep all the records in the left table, while the right table retains only the associated records, that is, the inner JOIN is executed first, and then the left table has no associated records with the right table
reserved, and the field on the right side is null.

Use a LEFT outer join
assumes that the authors table and the publishers table are joined on the city column. The results show only the authors residing in the city where the publisher resides (in this case Abraham Bennet and Cheryl Carson).

To include all authors in the results, regardless of whether the publisher lives in the same city, use the SQL-92 left outward join. The following is a query and result for the Transact-SQL LEFT OUTER join:

Use pubs
SELECT a.au_fname, A.au_lname, P.pub_name
from authors TER JOIN Publishers P
on a.city = p.city
ORDER by p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

"The first All data in the authors table is listed, because there is no pub_name field in the authors table, so null "

is the result set:

au_fname au_lname pub_name

Reginald Blotchet-halls NULL
Michel defrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns Macfeather NULL
Heather McBadden NULL
Michael O ' Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems

The left OUTER JOIN includes all rows of the authors table in the result, regardless of whether it matches the city column in the publishers table. Note: Most of the authors listed in the results do not have matching data, so the rows

The pub_name column contains a null value.

Right outer connection, outer join ... on

Opposite the left join.

Suppose you join the authors table and the publishers table on the city column. The results show only the authors residing in the city where the publisher resides (in this case Abraham Bennet and Cheryl Carson). SQL-92 right OUTER join operator

Right OUTER JOIN indicates that, regardless of whether there is a matching data in the first table, the result will contain all rows from the second table.

To include all publishers in the results, regardless of whether or not a publisher resides in the city, use the SQL-92 right-side join. The following is a query and result of a Transact-SQL right-outward join:

Use pubs
SELECT A.au_fname, A.au_lname, P.pub_name
From authors as a right OUTER JOIN publishers as P
On a.city = p.city
ORDER by p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Here is the result set:

au_fname au_lname pub_name

Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
Null NULL Binnet & Hardley
Null NULL Five Lakes Publishing
Null NULL GGG&G
Null NULL Lucerne Publishing
Null null NEW Moon Books
Null NULL Ramona Publishers
Null NULL Scootney Books

You can further restrict outer joins by using predicates, such as comparing joins to constants. The following example contains the same right outer join, but eliminates the title of a book that sells less than 50 books:

Use pubs
SELECT s.stor_id, S.qty, T.title
From sales s right OUTER JOIN titles T
On s.title_id = t.title_id
and S.qty > 50
ORDER by s.stor_id ASC

Fully connected, full join ... on (infrequently used, only for outer)

The

Feature combines the three coalition forces above, performing a inner join first, and then preserving the extra fields of the two tables, but the corresponding field of the connection is null.

If you want to join several tables, in the join operation we can do nested operations, there are three tables: Table 1, table 2, table 3, now join three tables:

  from (table 1 INNER JOIN table 2 on table 1. Sequence number = table 2. Serial number) INNER JOIN table 3 on table 1. Serial number = Table 3. Serial number  

Use full outer joins
to preserve unmatched information by including unmatched rows in the join results, use a full outer join. Microsoft? SQL Server? 2000 Complete OUTER JOIN operator full OUTER join, this operator includes all rows from two tables, regardless of whether another table has a value that matches

. The

assumes that the authors table and the publishers table are joined on the city column. The results show only the authors residing in the city where the publisher resides (in this case Abraham Bennet and Cheryl Carson). SQL-92 full OUTER JOIN operation

Operator indicates that, regardless of whether there are matching data in the table, the result will include all rows from two tables.

To include all authors and publishers in the results, regardless of whether a publisher or publisher in the city lives in the same city, use a full outer join. The following is a query and result for a Transact-SQL full OUTER join:

Use pubs
SELECT a.au_fname, A.au_lname, P.pub_name
from authors a Uter JOIN Publishers P
on a.city = p.city
ORDER by p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Below is the result Set:

au_fname au_lname pub_name

Reginald Blotchet-halls NULL
Michel defrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns Macfeather NULL
Heather McBadden NULL
Michael O ' Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
Null NULL Binnet & Hardley
Null NULL Five Lakes Publishing
Null NULL GGG&G
Null NULL Lucerne Publishing
Null null NEW Moon Books
Null NULL Ramona Publishers
Null NULL Scootney Books

nested queries

In the SQL language, a select-from-where statement becomes a query block. A query that is nested in the WHERE clause of another query block or in the condition of having a phrase becomes a nested query

Select Sname
From student
Where Sno in (select Sno from SCC where CNO = ' 2 ');

In this example, the downlevel query block select Sono from SC where sc where cno = ' 2 ' is nested in the condition of the upper query. The upper query block is called the outer query or the parent query, and the underlying query block becomes an inner query or a subquery.

Some nested queries can be replaced with join operations, some of which cannot be substituted. At present, the management system of commercial relational database is not perfect for the optimization of nested query. So in practical application, the query can be used as the connection query as far as possible.

Unrelated subqueries

The query criteria of a subquery do not depend on the parent query and become unrelated subqueries.
Select Sno, Sname, sdept
From student
where Sdept in (select Sdept from student where sname = ' Liu Chen ')

Related sub-query

The query criteria in a subquery depend on the parent query, which is called a correlated subquery. The entire statement becomes a related nested query statement.
Select Sno, CNO
From SC X
Where Grade >= (select AVG (grade) from SC y where Y.sno = X.sno)

X is the alias of the table SC, also known as a tuple variable, which can be used to represent a tuple of SC. The inner query is the average score of all elective courses for a student, as for which student's average score depends on the value of the parameter X.sno, which is related to the domain parent query, so such queries become related subqueries.

3-3 data Query