SQL quick start _ MySQL

In our daily work of using the SQL language, the most commonly used information is to query information from the database that has been established. Next, we will introduce in detail how to use the SQL language to perform various database query operations. SELECT... For convenience, we create the following data table named Store_Information in the database. Store_InformationStore_Na: The most commonly used SQL language is to query information from a database that has been created. Next, we will introduce in detail how to use the SQL language to perform various database query operations.

SELECT... FROM

For convenience, we create the following data table named Store_Information in the database.

Store_Information

Store_Name
Sales
Date

Los Angeles
$1500
Jan-10-2000

San Diego
$250
Jan-11-2000

Los Angeles
$300
Jan-12-2000

Boston
$700
Jan-12-2000


The simplest command used for database query in SQL is SELECT... FROM, syntax format:

SELECT "column_name" FROM "table_name"

For example, if you want to query all store names in the Store_Information data table, you can use the following command:

SELECT store_name FROM Store_Information

The query result is displayed as follows:

Store_Name

Los Angeles

San Diego

Los Angeles

Boston

If you want to query multiple fields at a time, you can add the names of the fields to be queried to the SELECT keyword in sequence and separate them with commas.

DISTINCT

The SELECT keyword allows you to query all the data of a specified field in a data table. If you want to query only information with different record values, you can use the DISTINCT keyword in SQL. The syntax format is as follows:

Select distinct "column_name"

FROM "table_name"

For example, we can use the following command to query all records with different record values in the Store_Information data table.

Select distinct Store_Name FROM Store_Information

The query result is as follows:

Store_Name

Los Angeles

San Diego

Boston

WHERE

In addition to selecting records with different record values, we may also need to query data in the database based on certain conditions. For example, we may need to query stores with sales of more than 1000 US dollars in the Store_Information data table. Therefore, you can use the WHERE keyword of SQL to set query conditions. The syntax format is as follows:

SELECT "column_name"

FROM "table_name"

WHERE "condition"

Therefore, we can use the following command to query store information with sales exceeding 1000 US dollars:

SELECT store_name FROM Store_Information WHERE Sales> 1000

The query result is displayed as follows:

Store_name

Los Angeles

Operation functions

Now we know that you can set flexible query conditions by determining the value when using SQL for database queries. To enhance the support for operations, SQL provides many practical computing functions for the majority of users. For example, we can directly call the SUM or AVG functions in the SQL command to calculate the total number and the average number respectively. The syntax format is as follows:

SELECT "function type" ("column_name ")

FROM "table_name"

To query the total sales of all stores in the Store_Information table, run the following command:

Select sum (Sales) FROM Store_Information

The query result is displayed as follows:

SUM (Sales)

$2750

COUNT

In addition to the SUM and AVG functions, the COUNT function is another commonly used computing function in SQL. The COUNT function can be used to calculate the number of records contained in a specified field in a data table. Syntax format:

Select count ("column_name ")

FROM "table_name"

For example, to query the number of store records in the Store_Information data table, run the following command:

Select count (store_name)

FROM Store_Information

The query result is displayed as follows:

Count (store_name)

4

The COUNT function can be used with the DISTINCT keyword to query the number of records with different record values in the specified field in the data table. For example, if you want to query the number of different stores in the Store_Information data table, you can use the following command:

Select count (DISTINCT store_name)

FROM Store_Information

The query result is displayed as follows:

Count (DISTINCT store_name)

3

GROUP

Next, let's take a look at the set functions in the SQL language. In the previous article, we used the SUM function to calculate the total sales of all stores. what should we do if we want to calculate the total sales of each store? To achieve this goal, we need to do two things: first, we need to query two fields: store name and sales. then, we use the SQL GROUP BY command to GROUP sales BY different stores and calculate the total sales of different stores. The syntax format of the group by command is:

SELECT "column_name1", SUM ("column_name2 ")

FROM "table_name"

Group by "column_name1"

You can use the following command to perform the preceding query:

SELECT store_name, SUM (Sales)

FROM Store_Information

Group by store_name

The query result is displayed as follows:

Store_name SUM (Sales)

Los Angeles $1800

San Diego $250

Boston $700

Note:

The group by keyword is generally used in SQL commands that query multiple fields at the same time and perform arithmetic operations on the fields.

HAVING

Another problem that users may want to solve when using the SQL language is to limit the output of the result calculated by sum or other set functions. For example, we may only want to see information about stores with a total sales volume of more than 1500 US dollars in the Store_Information table. then we need to use the HAVING clause. Syntax format:

SELECT "column_name1", SUM ("column_name2 ")

FROM "table_name"

Group by "column_name1"

HAVING (arithematic function condition)

(Group by clause is optional)

Therefore, we can use the following command to achieve the above query purpose:

SELECT store_name, SUM (sales)

FROM Store_Information

Group by store_name

Having sum (sales)> 1500

The query result is displayed as follows:

Store_name SUM (Sales)

Los Angeles $1800

Note:

HAVING clause instead of WHERE clause is used to set query conditions for set functions in SQL. Generally, HAVING clauses are placed at the end of an SQL command.

ALIAS

Next, we will focus on how to set aliases in SQL commands. In SQL, two types of aliases are generally used: field aliases and data table aliases.

Simply put, using field aliases can help us effectively organize the output results of queries. For example, in the multiple instances listed above, when we calculate the total sales volume of the store, SUM (sales) is displayed in the result ). Although SUM (sales) is not inconvenient for us to understand the query results, if we need to use multiple complex operations in the query, the results will not be so intuitive. If the field alias is used at this time, the readability of the query results will be greatly improved.

For a data table alias, we can place the alias directly after the data table name in the FROM clause. Data table aliases are extremely useful in connecting to multiple data tables for query.

The syntax format for fields and data table aliases is as follows:

SELECT "table_alias". "column_name1" "column_alias"

FROM "table_name" "table_alias"

That is, aliases are placed directly behind their respective names, separated by spaces.

Taking the Store_Information data table as an example, we can set the following field and data table alias in the SQL command used in the GROUP BY section:

SELECT A1.store _ name Store, SUM (Sales) "Total Sales"

FROM Store_Information A1

Group by A1.store _ name

The query result is displayed as follows:

Store Total Sales

Los Angeles $1800

San Diego $250

Boston $700

Connect multiple data tables

Finally, let's take a look at how to connect multiple data tables using SQL to query multiple data tables. For convenience, we have created two data tables named Store_Information and Region in the database.

Store_Information

Store_Name
Sales
Date

Los Angeles
$1500
Jan-10-2000

San Diego
$250
Jan-11-2000

Los Angeles
$300
Jan-12-2000

Boston
$700
Jan-12-2000


Region

Region_Name
Store_Name

East
Boston

East
New York

West
Los Angeles

West
San Diego


Next, let's take a look at how to query Sales by Region through the connection of data tables.

We noticed that the data table named Region contains Region and store fields, while the data table named Store_Information contains sales information for each store. Therefore, to obtain the sales information by region, we need to combine the information of two different data tables for query. Through the analysis of the above two data tables, we found that each data table contains a field named Store_Name. Therefore, we can use the following command to query:

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

The query result is displayed as follows:

REGION SALES

East: $700

West $2050

Note:

The first two lines of the preceding query command are used to specify the target fields to be queried, which are the Region_Name field in the Region data table and the total number of Sales field records in the Store_Information data table. Here, we set the aliases of the two fields to REGION and SALES respectively, and the aliases of the two data tables to A1 and A2 respectively. If we only use the field alias without setting the data table alias, the first line of the preceding SQL command will become the following form:

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

From this we can see that the effective use of data table aliases can greatly simplify the SQL commands for operations on multiple data tables.

The 3rd WHERE clause of the preceding query command sets the join conditions for two data tables. Because we want to ensure that the Store_Name field in the Region data table can correspond to the same name field in the Store_Information data table, we stipulate that the record values of the two fields should be equal. When connecting multiple data tables, you must accurately set the connection conditions for the data tables. if the WHERE clause is set incorrectly, many irrelevant data may appear in the query results.