-
Object naming convention: database name. owner name. object name. The first two can be omitted. The default value is the current database and the owner is dbo.
Alias: database name as database table name is mainly to increase the readability of the select statement. If an alias has been formulated for the data table, then
In the corresponding SQL statement, all explicit references to the data table must use aliases instead of the data table name.
The select statement is the most frequent activity in data retrieval. Before retrieving, you must first know where it is stored. The select statement can be composed of multiple query sub-statements and can be nested.
The select statement can retrieve: all rows and columns, all rows and specific columns, limited range of rows, rows matching a set of values, retrieval based on unknown values
rows, hide rows with duplicate values, and retrieve rows based on each search condition.
Basic structure: SELECT [ALL|DISTINCT]select_list
[INTO [new_table_name]]
FROM{table_name|view_name,...}
[WHERE search_conditons]
[GROUP BY group_by_list]
[HAVING search_conditions]
[ORDER BY order_list [asc|desc]]
The difference between WHERE and HAVING is that WHERE selects the entire table, while HAVING selects the elements in the group.
select clause
SELECT [ALL|DISTINCT] [TOP N] select_list
select_list can represent field names or other expressions.
all specifies that duplicate rows can be displayed in the result set, distinct does not display the same rows, and TOP n [PERCENT] specifies that only the first n rows are output from the query result set. n is an integer between 0 and 4294967295. If PERCENT is also specified, only the first n percent of rows are output from the result set. When specified with PERCENT, n must be an integer between 0 and 100.
example:
USE NORTHWIND
SELECT PRODUCTID,ORDERID,UnitPrice*Quantity AS SUM
INTO KKKKKK
FROM [Order Details]
WHERE UnitPrice*Quantity>10000;
The AS clause can be used to change the result set column names or to specify names for derived columns. In this case, it is clearer what sum represents, otherwise, the arithmetic
The formula will be named by the system. Therefore, the UnitPrice*Quantity after where in this statement cannot be written as sum, because sum is the result.
The name used in the set has not been specified in the search criteria.
Moreover, the table order details in this example contains the keyword order, so it cannot be used directly and must be added [].
The INTO clause is used to store the result set in a new table. Select into cannot be used with the compute clause. The select clause specifies calculations.
It will be given a name when listed.