SQL select syntax

The most common command in SQL is the SELECT statement, which is used to retrieve data. Syntax:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ AS output_name ] [, ...]
    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
    [ 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 class_name [, ...] ] ]
    [ LIMIT { count | ALL } [ { OFFSET | , } start ]]

Now we will use different examples to demonstrate the complex syntax of the SELECT statement. The tables used for these examples areSupplier and parts Database.

1.4.1.1. Simple select

Here are some simple examples of using the SELECT statement:

Example 1-4. Simple query with conditions

To retrieve all records with the price field greater than 10 from the table part, write the following query:

SELECT * FROM PART
    WHERE PRICE > 10;

Then obtain the table:

 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25

Use "*" in the SELECT statement to retrieve all the attributes in the table. If we only want to retrieve the attributes pname and price from the table part, we use the following statement:

SELECT PNAME, PRICE 
    FROM PART
    WHERE PRICE > 10;

The result is:

                      PNAME  |  PRICE
                     --------+--------
                      Bolt   |   15
                      Cam    |   25

Note that the "projection" (ing) in the corresponding relational calculus of the SELECT statement of SQL, rather than "selection" (selection) (seeRelational AlgorithmObtain detailed information ).

The conditions in the WHERE clause can also be logically connected with the not keyword or, and:

SELECT PNAME, PRICE 
    FROM PART
    WHERE PNAME = 'Bolt' AND
         (PRICE = 0 OR PRICE <= 15);

The following result is generated:

 PNAME  |  PRICE
--------+--------
 Bolt   |   15

You can use arithmetic operations in the target list and where clause. For example, if we want to know how much we need to buy two parts, we can use the following query:

SELECT PNAME, PRICE * 2 AS DOUBLE
    FROM PART
    WHERE PRICE * 2 < 50;

In this way, we get:

 PNAME  |  DOUBLE
--------+---------
 Screw  |    20
 Nut    |    16
 Bolt   |    30

Note that the double column after the keyword as is the new name of the second column. This technique can be used to assign each element in the target list a new title displayed in the result column. This new title is usually called an alias. This alias cannot be used elsewhere in the query.

1.4.1.2. Joins (connection)

The following example shows how to implementConnection.

To connect three tables, supplier, part, and sells, we usually use the following statement:

SELECT S.SNAME, P.PNAME
    FROM SUPPLIER S, PART P, SELLS SE
    WHERE S.SNO = SE.SNO AND
          P.PNO = SE.PNO;

The result is:

 SNAME | PNAME
-------+-------
 Smith | Screw
 Smith | Nut
 Jones | Cam
 Adams | Screw
 Adams | Bolt
 Blake | Nut
 Blake | Bolt
 Blake | Cam

In the from clause, we use an alias for each link, because these links have common naming attributes (SNO and PNO ). Now we can differentiate the public naming attributes of different tables, simply adding a dot with the alias of each link as the prefix. Used togetherAn internal connection
Is calculated in the same way as shown in. First, calculate the flute product supplier × part × sells. Then select
Clause ). Finally, all attributes except S. sname and P. pname are mapped.

Another method to connect is to use the following SQL join Syntax:

select sname, pname from supplier
JOIN sells USING (sno)
JOIN part USING (pno);

Giving again:

 sname | pname
-------+-------
 Smith | Screw
 Adams | Screw
 Smith | Nut
 Blake | Nut
 Adams | Bolt
 Blake | Bolt
 Jones | Cam
 Blake | Cam
(8 rows) 

I
A join table created using the join syntax is a join table that appears in the from clause, in any where, group by, or having
Table reference list items before the clause. Other table references, including table names or other join clauses, can be included in the from clause if they are separated by commas.
The logic of the joined table is the same as that of any other table listed in the from clause.

SQL join has two main types: cross join (unconditional join) andConditional connection. Conditional connections can also be declared based onConnection Conditions(ON, using, or natural) further subdivided into the method of its application (inner or outer connection.

Connection Type

Cross join

{T1}Cross join{T2}

I
Cross join receives two tables T1 and T2 with N rows and m rows respectively, and then returns a connection table containing nxm records of the cross product.
For each row of T1 R1, each row of T2 R2 is connected to R1 to generate the connection Table Line Jr. Jr contains all the R1 and R2 fields. Cross
Join is actually an inner join on true.

Conditional join

{T1} [Natural] [inner | {left | right | full} [outer]Join{T2} {OnSearch Condition| Using (Join column list)}

A join condition must be declared by providing one (and only one) Natural, on, or using keyword. The on Clause accepts oneSearch Condition,
It is the same as a where clause. The using clause accepts a list of field names separated by commas (,). These fields must be included in the connection table,
And use those fields to connect these tables. The generated connection table contains each common field and all other fields in the two tables. Natural is using
The abbreviation of the clause, which lists all the common field names in the two tables. The side effect of using and natural is that each connected field has only one copy that appears in the result table.
(Compared with the join of the previously defined relational calculus ).

[Inner]Join

For each row of R1 on T1, the connected table on T2 has a full row connected with R1.

Inner and Outer are optional for all join operations. Inner is default. Left, right, and full are only used for outer join.

Left [outer]Join

First, execute an inner join. Then, if one row in T1 does not meet the connection conditions for any T2 row, a connection row is returned, and the T2 field of the row is null.

TIPS:The joined table unconditionally contains all rows in T1.

Right [outer]Join

First, execute an inner join. Then, if one row in T2 does not meet the connection conditions for any T1 row, a connection row is returned, and the field T1 in this row is null.

TIPS:The joined table unconditionally contains all rows in T2.

Full [outer]Join

First
First, execute an inner join. Then, if one row in T1 does not meet the connection conditions for any T2 row, then return a connection row, the T1 field of the row.
It is null. Similarly, if one row in T2 does not meet the connection condition for any T1 row, a connection row is returned, and the T2 field of the row is null.

TIPS:The joined table unconditionally owns each row from T1 and each row from T2.

All types of join can be linked together or nested together.T1AndT2The join sequence is left to right if we do not control the join sequence.

1.4.1.3. Clustering Operator

SQL
Aggregation operators (such as AVG, Count, sum, Min, and Max) are provided. These Aggregation operators take an expression as parameters. As long as the where
The clause row calculates the expression, and then the aggregation operator calculates the set of input values. Generally, the result of an aggregation on the entire SELECT statement is
Generate a result. however, if a group is declared in a query, the database performs an independent calculation for each group and the aggregation result is displayed in each group (see the following section ).

Example 1-5. Aggregation

If we want to know the average price of all parts in the table part, we can use the following query:

SELECT AVG(PRICE) AS AVG_PRICE
    FROM PART;

The result is:

 AVG_PRICE
-----------
   14.5

If we want to know how many parts are stored in the table part, we can use the statement:

SELECT COUNT(PNO)
    FROM PART;

Get:

 COUNT
-------
   4

1.4.1.4. Group Aggregation

SQL allows us to divide the records in a table into groups. Then the clustering operator described above can be applied to these groups (that is, the value of the clustering operator is no longer an operation on the values of all declared columns, but an operation on all values of a group. In this way, clustering functions are calculated independently for each group .)

Record groups are grouped by keywordsGroupImplemented,GroupA list of attributes composed of a definition group is followed. If we use the statementGroup by A1, & tdot;, AKWe divide the relationship into groups. In this way, when two records are consistent on all attributes A1, & tdot;, and AK, they are in the same group.

Example 1-6. Aggregation

If we want to know how many parts each supplier sells, we can write a query like this:

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME;

Get:

 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  2  | Jones |   1
  3  | Adams |   2
  4  | Blake |   3

Then let's take a look at what happened. First generate the connection between supplier and sells:

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
   1   |  Smith  |   2
   2   |  Jones  |   4
   3   |  Adams  |   1
   3   |  Adams  |   3
   4   |  Blake  |   2
   4   |  Blake  |   3
   4   |  Blake  |   4

Then we put the records with the same attributes S. SnO and S. sname in the group:

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
                 |   2
--------------------------
   2   |  Jones  |   4
--------------------------
   3   |  Adams  |   1
                 |   3
--------------------------
   4   |  Blake  |   2
                 |   3
                 |   4

In our example, we have four groups and now we can apply the aggregation operator count to each group to generate the final result of the above query.

Note that if you want to make the results of a query using the group by and Aggregation operators meaningful, the attributes used for grouping must also appear in the target list. All attributes that do not appear in the group by clause can only be selected by using the aggregate function. Otherwise, no unique value is associated with other fields.

It is also worth noting that aggregation on aggregation is meaningless, for example, AVG (max (SNO), because select only groups and aggregates in one round. you can obtain this result by using a temporary table or using a sub-select in the from clause to aggregate the first level.

1.4.1.5. Having

Having
The clause is very similar to the WHERE clause. It is only used to calculate groups that meet the conditions given in the having clause. Actually, where
Filters out Input rows that we don't need before grouping and clustering, while having filters out unnecessary groups after group. Therefore, where
We cannot use the result of a clustering function. On the other hand, we have no reason to write a having that does not involve clustering functions. If your condition does not contain clustering, you can also write it in
In the WHERE clause, this will avoid clustering operations on the rows you are about to discard.

Example 1-7. Having

If we want to know the suppliers that sell more than one part, use the following query:

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME
    HAVING COUNT(SE.PNO) > 1;

And get:

 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  3  | Adams |   2
  4  | Blake |   3

1.4.1.6. subquery

In the where and having clauses, subqueries (subselection) can be used wherever a value is to be generated ). In this case, the value must first be calculated from the subquery. The use of subqueries extends the SQL expression capability.

Example 1-8. subquery

If we want to know all parts that are more expensive than those named 'screw', we can use the following query:

SELECT * 
    FROM PART 
    WHERE PRICE > (SELECT PRICE FROM PART
                   WHERE PNAME='Screw');

The result is:

 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25

When
When we check the preceding query, we will find two select keywords. The first is at the beginning of the query-we will call it the outer select-and the other is
In the WHERE clause, it becomes an embedded query-we will call it an inner select. Each record of the SELECT statement on the external layer must calculate the inner layer.
Select. After all the calculations are completed, we know the price recorded for the 'screw' part, and then we can check the more expensive records.
(In fact, in this example, the inner-layer query only needs to be executed once because it does not depend on the higher status of the outer-layer query .)

If we want to know suppliers that do not sell any parts (for example, we want to delete these suppliers from the database), we use:

SELECT * 
    FROM SUPPLIER S
    WHERE NOT EXISTS
        (SELECT * FROM SELLS SE
         WHERE SE.SNO = S.SNO);

In
In our example, The result column will be empty because each supplier sells at least one part. Please note that we use the source from outer layer in the inner select of the where clause
Select S. Sno. As mentioned above, subqueries calculate each outer query once, that is, the value of S. Sno is always from the outer select
.

1.4.1.7. subquery in from

Some special subqueries are used to place them in the from clause. this feature is useful because such a subquery can output multiple columns and multiple rows, and the subquery used in the expression must generate a result. subqueries in from also allow us to obtain grouping/clustering features for more than one round, without the need to resort to temporary tables.

Example 1-9. subquery in from

If we want to know the highest average part price among all our suppliers, we cannot use max (AVG (price), but we can write like this:

SELECT MAX(subtable.avgprice)
    FROM (SELECT AVG(P.PRICE) AS avgprice
          FROM SUPPLIER S, PART P, SELLS SE
          WHERE S.SNO = SE.SNO AND
                P.PNO = SE.PNO
          GROUP BY S.SNO) subtable;

This subquery returns a row (because of its group by) for each supplier and then aggregates all rows in the outer query.

1.4.1.8. Union, intersect, except T (Union, intersection, difference)

These operators calculate the Union, intersection, and difference of the tuples produced by two subqueries respectively.

Example 1-10. Union, intersect, limit t

The following is an example of union:

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Jones'
UNION
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Adams';    

Result:

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna

The following is an example of intersect:

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 1
INTERSECT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO < 3;

Result:

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris

The tuples returned by both queries are of SnO = 2.

Finally, an example of memory T is provided:

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 1
EXCEPT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 3;

The result is:

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna

Author's blog:Http://blog.csdn.net/meskgron/Related Articles

Globalization Based on ASP. NET

SQL select syntax

ADO and ADO. net

JDBC connection format

Two Methods for uploading multiple files using JavaBean