SQL query Statement

Use the SELECT statement to implement the query operation of the database. At the same time, it can also use a variety of clauses to group the results of the query statistics, totals, sorting and other operations. The SELECT statement can also generate a second table (either a temporary table or a permanent table) for the query results.
The syntax format for the SELECT statement is:

In a SELECT statement, clauses can be omitted, but must be listed in the order described above.
Simple query:
A simple SQL query includes only the select list, the FROM clause, and the WHERE clause, which describes the query column, the table or view of the query operation, and the search criteria.
Example: Query the "Business" category of books and their prices, published in the title table in 1991.
SELECT Title,price
from titles
WHERE DATEPART (year,pubdate) =1991 and type= ' business '
1. The SELECT list statement
SELECT list statement (select_list) specifies the selected column, which can be composed of a list of column names, asterisks, expressions, variables (including local variables and global variables).
(1) Select all columns
with asterisks to select all columns in the specified table or view
Select * FROM Discounts
Select the specified columns and specify their display order
Specify column names in the SELECT list statement to select different columns. Columns are separated by commas, showing the order in which the data in the results are sorted in the order of the column names.
Select Discount,discounttype from Discounts
in the SELECT list, you can also perform arithmetic operations on numeric values (including addition, subtraction, multiplication, addition, modulo, and so on).
where the Add, subtract, multiply, and except operations apply to any numeric column (such as Int,smallint,tinyint,decimal,numeric,float,real,money,smallmoney, etc.). The modulo operation cannot be used for money and Smallmoneyu data type columns.
When a value is evaluated, if the column value is null (NULL), the result of all arithmetic operations performed is still empty:
SELECT discounttype, ' lowqty+50 ' =lowqty+50
from discounts
In the select list, you can also specify the display format for string constants or variable output results:

DECLARE @var char ()
Select @VAR = ' Discount range '
SELECT ' discount name: ', Discounttype, @var, discount
Select Discounts
(2) Delete duplicate rows
Select statements can use the all| DISTINCT option to display all rows (all) or delete duplicate rows (DISTINCT), all at the default. When you use the DISTINCT option, the select list values that are duplicated for all data are displayed only once. The
Select DISTINCT Country from Publishers
2.FROM clause
FROM clause specifies the SELECT statement query and the table or view associated with the query. You can specify up to 16 tables or views in the FROM clause, separated by commas, and if the tables or views belong to different databases, you can qualify a table or view object with the database. Owner name. Object Format.
SELECT au_id,titles.title_id
//due to the "title_id" column in all two tables, add "titles." Avoid ambiguity
from Titles,titleauthor
where titles.title_id=titleauthor.title_id
in the FROM clause, you can specify an alias for each table or view. Aliases are separated by a space immediately after the object name, and can then be used to reference the columns in the table. The
SELECT au_id,t.title_id
from titles T,titleauthor ta
where t.title_id=ta.title_id
restricts search conditions with a WHERE clause, Use the WHERE clause in the SELECT statement to specify the query criteria.
The operator that can be included in the where statement, as shown in the following table:

Example 1: scope operator: Intersect book price for $10~$30 Books
SELECT Title,price
From titles
WHERE Price BETWEEN $ and $
Example 2: List operator: Listing publishers in German and French publishing houses
SELECT pub_name
From publishers
WHERE Country in (' Germany ', ' France ')
3. Pattern matching characters
pattern-matching characters [not] like are often used in fuzzy conditional queries, which determine whether column values match the specified string format and can be used for char,varchar,datetime and smalldatetime data types. There are several wildcard characters you can use:
Percent%: can match characters of any type of length;
Underline _: Matches a single character, which is often used to limit the length of the character of an expression;
square brackets []: Specifies a character, string, or range, requiring that the matched object be any one of them;
[^]: It takes the same value as [], but it requires that the matched object be any character other than the specified character.

Example 6: List of publishers whose names are 5 characters long and begin with ' GG '
SELECT pub_name
From publishers
WHERE pub_name like ' Gg_ '
Example 7: null-value judges: Finding books that are not yet priced
SELECT Title,price
From titles
Whrer Price is NULL
Example 8: logical operator: List of books whose receipts are below $ or above $, and where sales are less than 5000
SELECT Price,ytd_sales,title
From titles
WHERE (price<$5 OR price>$15) and ytd_sales<5000
Sorting query Results
In the SELECT statement, the query results are sorted by one or more columns using the ORDER BY statement. The syntax format for the ORDER BY clause is:

Example: List "Business" category of book identification and price, the result is sorted by: The price from high to low, title_id column from low to high.
SELECT Title_id,price
From titles
WHERE type= ' Business '
ORDER BY Price DESC,TITLE_ID,ASC