Simple tutorial on SQL statements in linux --- simple tutorial on table connection and SQL

Simple tutorial on SQL statements in linux --- simple tutorial on table connection and SQL

Now we will introduce the concept of Join. To understand the connection, we need to use a lot of commands we have introduced before. Let's assume that we have the following two tables,

Store_InformationTable

Store_Name	Sales	Txn_Date
Los Angeles	1500	05-Jan 1999
San Diego	250	07-Jan-1999
Los Angeles	300	08-Jan 1999
Boston	700	08-Jan 1999

GeographyTable

Region_Name	Store_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

We need to know the turnover (Sales) of each region (Region_Name ).GeographyThis table shows the stores in each zone.Store_InformationTell us the turnover of each store. If we want to know the turnover of each area, we need to link the data in these two different tables. After carefully understanding the two tables, we will find that they can be connected through the same column, Store_Name. We will first list SQL statements, and then discuss the meaning of each clause:

SELECT A1.Region _ Name REGION, SUM (A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.Store _ Name = A2.Store _ Name
Group by A1.Region _ Name;

Result:

REGION	SALES
Eas	700
West	2050

In the first row, we tell SQL to select two columns: the first column isGeographyThe Region_Name column in the table (we have an alias called REGION); the second column isStore_InformationThe Sales field in the table (alias: SALES ). Note that table aliases are useful here:GeographyThe table alias is A1,Store_InformationThe table alias is A2. If we do not use a table alias, the first line will become

SELECT Geography. Region_Name REGION, SUM (Store_Information.Sales) SALES

Obviously, this is much more complicated. Here we can see the function of table alias: It makes SQL statements easy to understand, especially when the SQL statement contains several different tables.

Next let's look at the third line, which isWHEREClause. This is where we explain the connection conditions. Here, we want to confirmGeographyThe value of the Store_Name field andStore_InformationThe values of the Store_Name field in the table are equal. ThisWHEREThe clause is the soul character of a connection, because its role is to determine that the connection between two tables is correct. IfWHEREClause is incorrect, so we are very likely to get a Cartesian Join ). The flute connection will result in all possible combinations between every two rows of all two tables. In this example, the flute connection Results 4x4 = 16 rows.

Linux test results:

1. Create a Geography table first

2. Insert response data in the phase table

3. Connection Query