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:
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.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.