The most common command inside SQL is a SELECT statement for retrieving data. The syntax is:
SELECT [All | Distinct [ON (Expression [, ...])]]]]]]]]]
* | express [as output_name] [, ...]
[INTO [TEMPORARY | TEMP] [TABLE] New_TABLE]
[From from_item [, ...]]]
[WHERE CONDITION]
[Group by express "[, ...]]
[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 demonstrate the syntax of the SELECT statement through different examples. Tables for these examples are defined in the supplier and component database.
1.4.1.1. Simple SELECT
Here is some simple example of using the SELECT statement:
EXAMPLE 1-4. Simple query with conditions
To find all the records of the field Price greater than 10 from the table part, we write the following query:
SELECT * FROM PART
Where price> 10;
Then get the table:
PNO | PNAME | PRICE
----- ------- --------
3 | BOLT | 15
4 | CAM | 25
Use "*" in the SELECT statement to retrieve all properties in the table. If we only want to retrieve the properties PNAME and Price from the table part, we use the following statement:
SELECT PNAME, PRICE
From part
Where price> 10;
This is our result:
PNAME | PRICE
-------- --------
Bolt | 15
CAM | 25
caution
SQL's SELECT statement corresponds to "Projection" instead of "selection" instead of "select) (see)
Relationship calculation gets details).
The conditions in the WHERE clause can also be logically connected with keyword or, and not:
SELECT PNAME, PRICE
From part
Where pname = 'bolt' and
(Price = 0 or Price <= 15);
This will generate the following results:
PNAME | PRICE
-------- --------
Bolt | 15
Arithmetic operations can be used in the target list and the WHERE clause. For example, if we want to know how much if we buy two parts, we can use the following query:
SELECT PNAME, Price * 2 As Double
From part
WHERE PRICE * 2 <50;
This way we get:
PNAME | DOUBLE
-------- ---------
SCREW | 20
NUT | 16
Bolt | 30
Please note that Double next to the keyword AS is a new name of the second column. This tip can be used for each element in the target list, gives them a new title displayed in the result column. This new title is often referred to as an alias. This alias cannot be used elsewhere in the query.
1.4.1.2. Joins (connection)
The following example shows how the SQL is connected.
To connect three tables Supplier, Part, and Sells on a common property, 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;
And what 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 is a common naming property (SNO and PNO) between these relationships. Now we can distinguish between public naming properties of different tables, just simply use the alias for each relationship to do a prefix. The combination is calculated using the same method shown in an internal join. First, you can calculate the Cartesian SUPPLIER × Part × SELLS. Then select the records given to the conditions given in the WHERE clause (that is, the value of the public naming property must be equal). Finally, we map all attributes outside S.SNAME and P.PNAME.
Another way to connect is to use the SQL JoIn syntax of the following:
SELECT SNAME, PNAME AUPPLIER
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)
A connection table created with a join syntax is a table reference list item that appears in the FROM clause. Other table references, including table names or other Join clauses, if separated by commas, can be included in the FROM clause. The table logically logically and any other list listed in the FROM clause is the same.
SQL Join has two main types, CROSS JOINs and conditional connections. Conditional connections can also be further subdivided according to the declared connection conditions (ON, USING, or NATURAL) and its application (INNER or OUTER connection).
Connection Type
Cross Join
{T1} cross join {t2}
A cross connection receives two tables T1 and T2 that have N rows and M rows, and then returns a connection table containing the cross-product NXM strip. For each row R1, T2 of T1, each line R2 of T2 is connected to the R1 connection to generate a connection JR, JR contains fields of all R1 and R2. Cross Join is actually an Inner Join on true. Conditions Join
{T1} [Natural] [Inner | {Left | Right | Full} [Outer]] JOIN {T2} {on search condition | Using (JOIN Column List)}
A condition Join must declare its connection conditions by providing one (and only one) Natural, ON, or USING. The ON clause accepts a Search Condition, which is the same as a WHERE clause. The USING clause accepts a list of field names separated by commas, and there must be these fields in the connection table, and use those fields to connect these tables, and the generated connection table contains all other fields for each common field and two tables. Natural is the abbreviation of the using clause, which lists all public field names in two tables. Side effects using using and NATURAL are only one copy of each connection appears in the result table (compared to the JOIN as the previously defined relationship).
[Inner] join
For each row R1 of T1, the connected table has a connection condition that is full with R1 in T2.
For all join, Inner and Oters are optional. Inner is default. LEFT, Right, and Full are only used for Outer Join.
Left [Outer] Join
First, execute an Inner Join. Then, if there is a row in T1, it does not satisfy the connection condition, then a connection row is returned, the field of the line T2 is NULL.
Tips: The connected form is unconditionally containing all the rows in T1.
Right [Outer] Join
First, execute an Inner Join. Then, if there is a row in T2, the rows of any T1 do not satisfy the connection conditions, then a connection row is returned, and the field of the row is null.
Tip: The connected table has unconditionally contains all the lines in T2.
Full [Outer] Join
First, execute an Inner Join. Then, if there is a row in T1 to any T2 does not satisfy the connection condition, then a connection row is returned, and the field of the line of T1 is NULL. Similarly, if there is a row in t2 to any T1's row does not satisfy the connection condition, then a connection row is returned, the field of the line T2 is NULL.
Tip: The connected form is unconditionally with each row of T1 and every line from T2.
All types of join can be linked or nested together, while T1 and T2 can be a connection generated table. We can use the circular arc to control the order of Join, if we do not active control, then the connection order is from left to right.
1.4.1.3. Aggregation operator
SQL provides some aggregation operators (such as AVG, Count, SUM, MIN, MAX), which are parameters in an expression. This expression is calculated as long as it is a line that satisfies the WHERE clause, and then the aggregation operator calculates the collection of this input value. Typically, a result of a gathering to calculate the entire SELECT statement is generating a result. However, if a group is declared in a query, the database will perform independent calculations for each group, and the result of the aggregation is in accordance with each group (see next). EXAMPLE 1-5. Aggregation
If we want to know the average price of all parts in Table Part, we can use the following:
SELECT AVG (Price) AS AVG_PRICE
From part;
turn out:
AVG_PRICE
-----------
14.5
If we want to know how many components stored in the table part, we can use statements:
SELECT Count (PNO)
From part;
get:
Count
-------
4
1.4.1.4. Packet gather
SQL allows us to divide records in a table into groups. The aggregation operator described above can then be applied to these groups (that is, the value of the aggregation operator is no longer operated on the values of all the columns of the declarations, but is operated for all values of a group. Such aggregation functions It is calculated independently for each group.)
The group of records is implemented by the keyword group by, and the Group B is followed by a list of attributes of the definition group. If we use statements Group by A1, & TDOT;, AK we divide the relationship into groups, which is only the same set when both records are reached on all attributes A1, & TDOT;, AK, they are the same group.
EXAMPLE 1-6. Gathering
If we want to know how many parts for each supplier sell, we can write inquiry:
Select S. Sno, S. S.SName, Count (SE.PNO)
From Supplier S, Sells Se
WHERE S.SNO = S.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, you will be generated by Subplier 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 same records as those 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 aggregated operators in each group to generate the final result of the query given above.
Please note that if you want a result of a query using Group By and aggregation operators, the properties for packets must also appear in the target list. All properties that have not appeared in the Group By clause can only be selected by using the aggregation function. Otherwise, there will be no unique values associated with other fields.
It should also be noted that the aggregation is meaningless, for example, AVG (Max)), because SELECT only makes a round of packets and gatherings. You can get this result, using a temporary table or use a subselect to use a subselect to get the first level of aggregation.
1.4.1.5. Having
The Having clause makes it very like a WHERE clause, which is only used to calculate those groups that meet the conditions given in the HAVING clause. In fact, WHER is filtered out before grouping and aggregation, and Having does not need in Group after group. Therefore, WHERE cannot use a result of a gathering function. On the other hand, we have no reason to write a Having that does not involve the gathering function. If your condition does not contain a gather, you can also write it in WHERE, so you can avoid aggregation operations for those you are going to abandon.
EXAMPLE 1-7. HAVING
If we want to know the supplier who sells more than one part, use the following query:
Select S. Sno, S. S.SName, Count (SE.PNO)
From Supplier S, Sells Se
WHERE S.SNO = S.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. Subproof
In the WHERE and HAVING clauses, it is allowed to use subqueries (subsets) in any place to generate values. In this case, this value must first be calculated from the subquery. The use of subquery expands the expression of SQL.
EXAMPLE 1-8. Subproof
If we want to know all parts that are more expensive than named 'Screw', we can use the following query:
SELECT *
From part
WHERE PRICE> (SELECT Price from Part
WHERE PNAME = 'screw');
turn out:
PNO | PNAME | PRICE
----- ------- --------
3 | BOLT | 15
4 | CAM | 25
When we check the above queries, we will find a select keyword. The first at the beginning of the query - we will call it as the outer select - and the other is in the WHERE clause, becomes an embedded query - we will be called the inner SELECT. Each record of the outer SELECT must first calculate the inner layer SELECT. After completing all calculations, we know the price of records named 'Screw' components, and then we can check those prices more expensive. (In this case, in this example, the inner query only needs to be executed once because it does not depend on the external query.) If we want to know the supplier who does not sell any parts (for example, we want to put these supplies Commercial from the database), we use:
SELECT *
From support
WHERE NOT EXISTS
(Select * from Sells SE
WHERE se.sno = s.sno);
In our example, the result will be empty because each vendor is sold at least one component. Please note that S. Sno from outer SELECT is used in the inner select of the WHERE clause. As mentioned earlier, the child query calculates every outer query, that is, the value of S.SNO is always made from the actual record of the outer SELECT.
1.4.1.7. Subproof inside from FRO
A particularly subquery is used to put them in the FROM clause. This feature is useful because such subquers can output multiple columns and multi-lines, while the subquery used in the expression must generate a result. The child in from From also allows us to get more than one round packet / aggregation feature, without having to help temporary tables.
EXAMPLE 1-9. Subway inside from
If we want to know the home in all our suppliers, we can't use max (special), but we can write 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 for each supplier (because of its group by) and then we gather all rows in outer queries.
1.4.1.8. Union, INTERSECT, EXCEPT (United, intersection, phase)
These operators respectively calculate the unity, intersection, and collection theory of tuples generated by the two subquers.
Example 1-10. Union, INTERSECT, EXCEPT
The following example is Union example:
Select S. Sno, S.SName, S.City
From support
Where s.sname = 'Jones'
Union
Select S. Sno, S.SName, S.City
From support
WHERE S.SNAME = 'adams';
produces result:
SNO | SNAME | City
----- ------- --------
2 | Jones | Paris
3 | ADAMS | VIENNA
Here is an example of intersect:
Select S. Sno, S. S. S.Sname, S.cityFrom Supplier S
WHERE S.SNO> 1
INTERSECT
Select S. Sno, S.SName, S.City
From support
WHERE S.SNO <3;
produces result:
SNO | SNAME | City
----- ------- --------
2 | Jones | Paris
The group of the two queries will return is that SNO = 2
Finally an example of Except:
Select S. Sno, S.SName, S.City
From support
WHERE S.SNO> 1
Except
Select S. Sno, S.SName, S.City
From support
WHERE S.SNO> 3;
turn out:
SNO | SNAME | City
----- ------- --------
2 | Jones | Paris
3 | ADAMS | VIENNA
