SQL statements for beginners

SQL statements for beginners

1. Use the Select clause to retrieve records
The Select clause is the core of each data query. It tells the database engine what fields are returned.
The common form of Select clauses is:
Select *
This clause indicates "returning all fields that can be found in the specified record source ". This command is convenient because you do not need to know the name of the Field Retrieved from the table. However, retrieving all columns in a table is inefficient. Therefore, because only the required fields are retrieved, the query efficiency can be greatly improved.
2. Use the From clause to specify the record source
The From clause describes the record source for query and retrieval. The record source can be a table or another storage query.
You can also retrieve records from multiple tables, which will be described in later sections.
Example:
Select * From students to retrieve all records in the students table
3. Use the Where clause to describe conditions
The Where clause tells the database engine to limit the records it retrieves based on one or more provided conditions. A condition is an expression that can be true or false.
Example:
Select * From students Where name = "shadow"
The name field in students is returned as the shadow list. The returned results do not have a specific Order unless you use the Order By clause. This clause is described in the following sections.
Note: The text string delimiter in the Where clause is a double quotation mark. In VB, it is changed to a single quotation mark because the string delimiter in VB is a double quotation mark.
Supplement:
Using the And Or logic, you can link two Or more conditions to create a more advanced Where clause.
Example:
Select * From students Where name = "shadow" And number> 100
Returns the list of shadow numbers with names greater than 100.
Example:
Select * From students Where name = "shadow" And (number> 100 Or number <50)
Returns a list with name as shadow, number greater than 100, or less than 50.

Operators used in the Where clause
Operator Functions
<Less
<= Less than or equal
> Greater
> = Greater than or equal
= Equal
<> Not equal
Between is within a certain value range.
Like matches a pattern
In is included In a value list.
The equals and non-equals operators in SQL have the same meaning and use as those in VB.

Example:
(1). Between Operator
Use cust
Select * From students
Where number Between 1 and 100
The Between operator returns all record values within the defined boundary. In this example, all records in the range from 1 to 100 are returned.

(2). Like operator and wildcard
Use cust
Select * From students
Where name Like "% %"
The Like operator matches the record to a pattern you specify. In this example, any string containing "shadow" is returned.

Meanings of the four wildcards
Wildcard description
% Represents zero or multiple arbitrary characters
_ (Underline) represents any character
[] Any single character in the specified range
[^] Any single character not within the specified range

All examples are as follows:
Like "BR %" returns any string starting with "BR"
Like "br %" returns any string starting with "Br"
Like "% een" returns any string ended with "een"
Like "% en %" returns any string containing "en"
Like "_ en" returns the three strings ended with "en ".
Like "[CK] %" returns any string starting with "C" or "K"
Like "[S-V] ing" returns a four-character string ending with "ing" starting from S to V.
Like "m [^ C] %" returns any string starting with "M" and the second character is not "C.

4. Sort the results using order
The order by clause tells the database engine to sort the records it retrieves. You can sort any field or multiple fields in ascending or long order.
After a formal SELECT query, it contains an order by clause, followed by the field to be sorted (multiple can be entered.
Example:
Use cust
Select * from students
Where name like "% %"
Order by number
Sorts the returned results by number.

Sort in descending order
To sort in the long order, you only need to use the DESC keyword after the sorted field.
Example:
Use cust
Select * from students
Where name Like "% %"
Order By number Desc

5. Use Top to display the first or last record in a range.
You can use the Top keyword to display only a few records before or after a large record. In a query, the Top keyword and the sorting clause limit the result set to a few records or display part of the entire result set by a certain percentage.
Example:
Select Top 3 * From students return the first three records in the students table
Select Top 10 Percent * From students return the first 10% records in the students table
Select Top 3 * From students Order By number desc return the three records with the maximum number (last) in the students table

6. Use As to alias the field name
There are two reasons for this:
☆The field names of the tables involved are long. To make it easier to process fields in the result set.
☆The created query generates some calculated or aggregate columns, which must be named.
No matter what the reason for the field to be aliased, The As clause can be easily used in SQL.
Example:
Select number As student ID, name As name From students

7. Merge Query
Union Query is used to merge the content of two tables with the same field structure. It is useful if you want to display irrelevant records in multiple record sources in a single result set.
Example:
Select *
From students
Union
Select *
From students1
This query result set combines the records in students and students1 into a single result, and the output is exactly the same as before the original table is archived.
Note: by default, duplicate records are not returned for merged queries (this function is useful if the record archiving system does not delete the corresponding record after copying the record to the archive table ), you can add the All keyword to the merge query to display duplicate records.
Example:
Select *
From students
Union all
Select *
From students1
Duplicate records are not processed when the students table and students1 table are displayed in the merged query.
Supplement:
The Union operator allows two or more query results to be merged into one query result set. If the Union and join operators are compared, the Union operator increases the number of rows while the join operator increases the number of columns. When using Union, note that the structure of the columns in the two results must match, and the data type must be compatible.
The syntax of the Union operator is as follows:
Select select_list
From clause
Where clause
Group by clause
Having clause
Union [all]
Select select_list
From clause
Where clause
Group by clause
Having clause
Order by clause
Compute clause
For the Union operator, the following points need to be explained:
· By default, the Union operator deletes all redundant rows. If the all option is used, redundant rows are not deleted.
· All select_list columns in the Union statement must have the same number of columns, compatible data types, and appear in the same order.
· In the result set, the column name comes from the first select statement.


8. Connection Query
In actual use, you often need to retrieve data from two or more tables at the same time. The connection allows you to retrieve data from two or more tables at the same time and specify one or more columns in these tables as the connection condition. In SQL Server, two connection syntaxes can be used, one is the ANSI connection syntax, which is used in the from clause and the other is the SQL server connection statement, this is the connection used in the WHERE clause.
The ANSI connection syntax is as follows:
Select table_name.column_name, table_name.column_name ,...
From {table_name [join_type] Join table_name on search_conditions}
Where [search_conditions]
In the ANSI syntax format, you can use the ANSI connection keyword to determine the connection format used. For example:
☆Use the inner join keyword, and the result set contains only rows that meet the conditions.
☆Use the cross join keyword. The result set contains a combination of all rows in two tables.
☆Using the Outer Join keyword, The result set contains both the rows that meet the conditions and all the rows in a specific table.
The SQL Server connection syntax is as follows:
Select table_name.column_name, table_name.column_name ,...
From [table_name, Tab
 
 

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/21aspnet/archive/2007/03/20/1535559.aspx