SQL Select Syntax (GO)

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

SELECT [All | DISTINCT [On (expression[, ...] ) ] ]    * |expression[ASOutput_name] [, ...] [INTO [temporary | TEMP] [TABLE]new_table] [FromFrom_item[, ...] ] [WHEREcondition] [GROUP byexpression[, ...] ] [havingcondition[, ...] ] [{UNION | INTERSECT | EXCEPT [All]}Select] [ORDER byexpression[ASC | DESC | USINGoperator] [, ...] ] [For UPDATE [ofclass_name[, ...] ]] [LIMIT {Count| All} [{OFFSET |,}Start]]

Now we will demonstrate the complex syntax of the SELECT statement through a different example. The tables used for these examples are defined in the suppliers and parts database .

1.4.1.1. Simple Select

Here are some simple examples of using SELECT statements:

Example 1-4. A simple query with conditions

To find out all records of the field price greater than 10 from the table part, we write the following query:

SELECT * from "    WHERE price > 10;

Then get the table:

PNO |  PNAME  |  Price-----+---------+--------  3  |  Bolt   |  4  |  Cam    |   25

Using "*" in the SELECT statement will retrieve all the properties in the table. If we only want to retrieve the property PNAME and price from the table part, we use the following statement:

SELECT PNAME, price from part    WHERE price > 10;

This time our results are:

                      PNAME  |  Price                     --------+--------                      Bolt   |                      Cam    |   25

Please noteThe Select statement for SQL corresponds to the "projection" (map) in the relational calculus instead of "selection" (see relational Calculus for more information).

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

SELECT PNAME, price from part    WHERE PNAME = ' Bolts ' and         (Price = 0 OR Price <= 15);

this produces the following result:

PNAME  |  Price--------+--------Bolt   |   15

Arithmetic operations can be used in the target list and in the WHERE clause. For example, if we want to know how much it costs if we buy two parts, we can use the following query:

SELECT PNAME, Price * 2 as a DOUBLE from a    WHERE price * 2 < 50;

so we get:

PNAME  |  DOUBLE--------+---------screw  |    Nut    |    Bolt   |    30

Note that the DOUBLE following the keyword as is the new name for the second column. This technique can be used for each element in the target list, giving them a new caption that appears in the result column. This new title is often called an alias. This alias cannot be used elsewhere in the query.

1.4.1.2. Joins (Connection)

The following example shows how a connection is implemented in SQL.

To connect three tables Supplier,part and SELLS on a common attribute, we typically 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;      

And the result we get 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 relationship because there are public named properties (SNO and PNO) between those relationships. Now we can distinguish between the common naming properties of different tables, simply by prefixing the alias of each relationship with a dot. A union is calculated using the same method as shown in an inner join . First, the Cartesian product supplierxpartxsells is calculated. Then select the records that satisfy the conditions given in the WHERE clause (that is, the values of the public named properties must be equal). Finally, we map out all attributes except S.sname and P.pname.

Another way to connect is to use the following SQL JOIN syntax:

Select Sname, pname from Supplierjoin 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)  

A connection table, created with the join syntax, is a list item that appears in the FROM clause before any where,group by or HAVING clause. Other table references, including table names or other JOIN clauses, can be included in the FROM clause if separated by commas. The tables generated by the connection are logically the same as any other tables listed in the FROM clause.

There are two main types of SQL join, cross JOIN (unconditional connection) and conditional connections . Conditional joins can also be further subdivided based on the declared join conditions (on,using, or NATURAL) and how it is applied (INNER or OUTER connections).

Connection type

Cross JOIN

{ T1 } Cross JOIN { T2 }

A cross join receives two tables T1 and T2, respectively, with N rows and M rows, and then returns a join table containing the cross-product NxM record. For each row of T1 r1,t2 each line R2 the table row that generated the connection with the R1 connection JR,JR contains all the R1 and R2 fields. The cross join is actually a INNER join on TRUE.

Conditional JOIN

{ T1 } [NATURAL] [INNER | {left | Right | Full} [OUTER]] JOIN { T2 } {on search condition | USING ( join column list )}

A conditional JOIN must declare its join condition by providing a (and only one) Natural,on, or using a keyword. The ON clause accepts a search condition, which is the same as a WHERE clause. The using clause accepts a comma-delimited list of field names, which must all be in the join table, and joins the tables with those fields, and the resulting join table contains each of the common fields and all the other fields of the two tables. NATURAL is an abbreviation for the USING clause, which lists all the common field names in two tables. The side effect of using and NATURAL is that only one copy of each connected field appears in the result table (compared to the JOIN of the relational calculus defined earlier).

[INNER] JOIN

For each line of T1 R1, the connected table has a row in T2 that satisfies the join condition with R1.

For all joins, INNER and OUTER are optional. INNER is the default. Left,right, and full are only used for OUTER joins.

Left [OUTER] JOIN

First, perform a INNER JOIN. Then, if there is a row in T1 that does not satisfy the join condition for any T2 row, then a connection row is returned, and the T2 field of the row is null.

Tip: the joined table unconditionally contains all the rows in the T1.

Right [OUTER] JOIN

First, perform a INNER JOIN. Then, if there is a row in T2 that does not satisfy the join condition for any T1 row, then a connection row is returned, and the T1 field of the row is null.

Tip: the joined table unconditionally contains all the rows in the T2.

Full [OUTER] JOIN

First, perform a INNER JOIN. Then, if there is a row in T1 that does not satisfy the join condition for any T2 row, then a connection row is returned, and the T1 field of the row is null. Similarly, if there is a row in T2 that does not satisfy the join condition for any T1 row, then a connection row is returned, and the T2 field of the row is null.

tip: tables that are joined unconditionally have each row from T1 and each line from T2.

All types of joins can be chained together or nested together, and both T1 and T2 can be tables that the connection generates. We can use parentheses to control the order of joins, and if we do not actively control, then the connection order is left to right.

1.4.1.3. Aggregation operators

SQL provides some aggregation operators (for example, Avg,count,sum,min,max), which take an expression as arguments. As long as the row satisfies the WHERE clause, the expression is evaluated, and the aggregation operator calculates the set of input values. Typically, a aggregation evaluates to the entire SELECT statement to produce a result. However, if a group is declared within a query, the database will be evaluated individually for each group, and the aggregation result is based on each group (see section below).

Example 1-5. Gathered

We would like to know the average price of all the 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. Grouping aggregation

SQL allows us to divide the records in a table into groups. The aggregation operators described above can then be applied to these groups (that is, the value of the aggregation operator no longer operates on the values of all declared columns, but instead on all values of a group. Such aggregation functions are computed independently for each group. ）

The grouping of records is implemented by the keyword Group by, followed by a list of attributes that define the composition of the group. If we use the statement GROUP by A1, ⃛ ak We divide the relationship into groups so that when and only if two records are in all properties a1, ⃛ K agree, they are the same group.

Example 1-6. Gathered

If we want to know how many parts each vendor 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 we'll take a look at what's going on. First make a connection to the table 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

We then put those 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 case, we have four groups and now we can apply the aggregate operator COUNT to each group, generating the final result of the query given above.

Note that if you want a result of a query that uses group BY and aggregate operators to make sense, the attributes used for grouping must also be present in the target list. All properties that are not present in the GROUP by clause can only be selected by using the aggregate function. Otherwise, there is no unique value associated with the other fields.

Also note that aggregation on aggregation is meaningless, for example, AVG (MAX (SNO)), because SELECT only does a round grouping and aggregation. You can get this result by using a temporary table or using a sub-SELECT in the FROM clause to do the first level of aggregation.

1.4.1.5. Having

The HAVING clause is performed very much like a WHERE clause and is only used to calculate the groups that satisfy the conditions given in the HAVING clause. In fact, where we filter out the input rows we don't need before grouping and aggregating, we have groups that are not needed after the group. Therefore, WHERE cannot use the result of a clustered function. On the other hand, there is no reason to write a having that does not involve aggregation functions. If your condition does not include aggregation, then you can also write it in where, so that you can avoid the aggregation of the rows you are going to discard.

Example 1-7. Having

If we want to know which vendors 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 has    COUNT (SE. PNO) > 1;

and get:

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

1.4.1.6. Sub-query

In the WHERE and HAVING clause, the subquery (sub-selection) is allowed to be used wherever the value is to be generated. In this case, the value must first come from the subquery's calculation. The use of subqueries extends the expressive power of SQL.

Example 1-8. Sub-query

If we want to know all the parts that are more expensive than the one 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   |  4  |  Cam    |   25

When we check the above query, we find that two times the SELECT keyword appears. The first one at the beginning of the query-what we will call the outer select-and the other in the WHERE clause becomes an embedded query-what we will call the inner select. Each record of the external select must first calculate the inner select. After all the calculations are done, we know the price of the records named ' Screw ' parts, and then we can check for more expensive records. (In fact, in this case, the inner query only needs to be executed once, because it does not depend on the high state of the outer query.) )

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

SELECT * from     SUPPLIER S    where not EXISTS        (select * from SELLS se         where SE. SNO = S.sno);

In our case, the result column will be empty because each vendor sells at least one component. Notice that we used the S.SNO from the outer select in the inner select of the WHERE clause. As mentioned earlier, the subquery is computed once for each outer query, that is, the value of S.sno is always taken from the actual record of the outer SELECT.

1.4.1.7. Subqueries in From

The use of a somewhat special subquery is to put them in the FROM clause. This feature is useful because such subqueries can output multiple columns and multiple rows, whereas subqueries used in expressions must produce a result. The subquery in from will also allow us to get more than one round of grouping/aggregation features without resorting to temporary tables.

Example 1-9. Subquery from inside

If we want to know the highest average part price in all our suppliers, we can't use MAX (avg), but we can write:

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 for each vendor (because of its GROUP by) and then we aggregate all the rows in the outer query.

1.4.1.8. Union, Intersect, Except (union, intersect, dissimilarity)

These operators calculate the union of tuples produced by two sub-queries respectively, the intersection and the differences in the set theory.

Example 1-10. Union, Intersect, Except

The following example 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 ';    

Produce results:

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

Here is an example of intersection (INTERSECT):

Select S.sno, S.sname, s.city from    SUPPLIER S    WHERE s.sno > 1INTERSECT    SELECT s.sno, S.sname, s.city    F ROM SUPPLIER S    WHERE S.sno < 3;

Produce results:

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

The tuples returned by the two queries are the sno=2.

The last is an example of a EXCEPT:

Select S.sno, S.sname, s.city from    SUPPLIER S    WHERE s.sno > 1EXCEPT    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

SQL Select Syntax (GO)