Identity (функция) (transact-sql)identity (function) (transact-sql)

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref


SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

資料類型Data Types

FILESTREAM 屬性不會傳送至新的資料表。The FILESTREAM attribute does not transfer to the new table. FILESTREAM BLOB 會以 varbinary(max) BLOB 的形式被複製並儲存在新資料表中。FILESTREAM BLOBs are copied and stored in the new table as varbinary(max) BLOBs. 在沒有 FILESTREAM 屬性的情況下,varbinary(max) 資料類型會有 2 GB 的限制。Without the FILESTREAM attribute, the varbinary(max) data type has a limitation of 2 GB. 如果 FILESTREAM BLOB 超過這個值,系統就會引發錯誤 7119 並且停止此陳述式。If a FILESTREAM BLOB exceeds this value, error 7119 is raised and the statement is stopped.

當您將現有的識別欄位選入新的資料表時,除非下列其中一個狀況成立,否則新資料行會繼承 IDENTITY 屬性:When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

  • SELECT 陳述式包含聯結。The SELECT statement contains a join.

  • 利用 UNION 來聯結多個 SELECT 陳述式。Multiple SELECT statements are joined by using UNION.

  • 在選取清單中,重複列出識別欄位。The identity column is listed more than one time in the select list.

  • 識別欄位是運算式的一部分。The identity column is part of an expression.

  • 識別欄位來自遠端資料來源。The identity column is from a remote data source.

如果其中任何一個狀況成立,都會將資料行建立成 NOT NULL,而不是繼承 IDENTITY 屬性。If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. 如果新的資料表需要識別欄位,但是無法使用這種資料行,或者您想要與來源識別欄位不同的初始或遞增值,請使用 IDENTITY 函數在選取清單中定義此資料行。If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. 請參閱下面<範例>一節中的<使用 IDENTITY 函數來建立識別欄位>。See «Creating an identity column using the IDENTITY function» in the Examples section below.

备注Remarks

由于 SELECT 语句的复杂性,下面按子句说明详细的语法元素和参数:Because of the complexity of the SELECT statement, detailed syntax elements and arguments are shown by clause:

SELECT 语句中的子句顺序非常重要。The order of the clauses in the SELECT statement is significant. 可以省略可选子句,但这些子句在使用时必须按适当的顺序出现。Any one of the optional clauses can be omitted, but when the optional clauses are used, they must appear in the appropriate order.

只有当这些语句的选择列表包含给函数的局部变量赋值的表达式时,在用户定义函数中才允许有 SELECT 语句。SELECT statements are permitted in user-defined functions only if the select lists of these statements contain expressions that assign values to variables that are local to the functions.

对于由四部分组成的名称,若其中的服务器名称使用的是 OPENDATASOURCE 函数,则该名称可以在表名能够在 SELECT 语句内出现的任何位置作为表源使用。A four-part name constructed with the OPENDATASOURCE function as the server-name part can be used as a table source wherever a table name can appear in a SELECT statement. 无法为 Azure SQL 数据库Azure SQL Database 指定由四部分组成的名称。A four-part name cannot be specified for Azure SQL 数据库Azure SQL Database.

某些应用于 SELECT 语句的语法限制涉及到远程表。Some syntax restrictions apply to SELECT statements that involve remote tables.

Section 9. Modifying data

In this section, you’ll learn how to change the contents of an Oracle database. The SQL commands for modifying data are referred to as Data Manipulation Language (DML).

  • INSERT – learn how to insert a row into a table.
  • INSERT INTO SELECT – insert data into a table from the result of a query.
  • INSERT ALL – discuss multitable insert statement to insert multiple rows into a table or multiple tables.
  • UPDATE – teach you how to change the existing values of a table.
  • DELETE – show you how to delete one or more row from a table.
  • MERGE – walk you through the steps of performing a mixture of insertion, update, and deletion using a single statement.

E.E. 使用相互關聯的子查詢Using correlated subqueries

這是一項相依於外部查詢來取得其值的相互關聯子查詢。A correlated subquery is a query that depends on the outer query for its values. 這項查詢可以重複執行,針對外部查詢可選取的每個資料列各執行一次。This query can be executed repeatedly, one time for each row that may be selected by the outer query.

第一個範例顯示語意相等的查詢,以說明使用 關鍵字和 關鍵字之間的差異。The first example shows queries that are semantically equivalent to illustrate the difference between using the keyword and the keyword. 兩者都是有效子查詢,擷取產品模型是長袖標誌緊身內衣之各項產品名稱的單一執行個體的, 和 資料表的 號碼相符。Both are examples of a valid subquery that retrieves one instance of each product name for which the product model is a long sleeve logo jersey, and the numbers match between the and tables.


下一個範例會使用 來擷取 資料表中獎金是 ,且 和 資料表中員工識別碼相符的每一位員工姓名執行個體。The next example uses and retrieves one instance of the first and last name of each employee for which the bonus in the table is and for which the employee identification numbers match in the and tables.

這個陳述式中先前的子查詢無法在外部查詢之外獨立評估。The previous subquery in this statement cannot be evaluated independently of the outer query. 它需要 值,但在 SQL Server Database EngineSQL Server Database Engine 檢查 中的不同資料列時,這個值會跟著改變。It requires a value for , but this value changes as the SQL Server Database EngineSQL Server Database Engine examines different rows in .

您也可以在外部查詢的 子句中,使用相關的子查詢。A correlated subquery can also be used in the clause of an outer query. 這個範例會尋找最大標價大於模型平均值兩倍的產品模型。This example finds the product models for which the maximum list price is more than twice the average for the model.

這個範例利用相關的子查詢來尋找銷售了特定產品的員工名稱。This example uses two correlated subqueries to find the names of employees who have sold a particular product.

Выборка данных. Команда SELECT

Последнее обновление: 13.07.2017

Для получения данных применяется команда SELECT. В упрощенном виде она имеет следующий синтаксис:

SELECT список_столбцов FROM имя_таблицы

Например, пусть ранее была создана таблица Products, и в нее добавлены некоторые начальные данные:

CREATE TABLE Products
(
	Id INT IDENTITY PRIMARY KEY,
	ProductName NVARCHAR(30) NOT NULL,
	Manufacturer NVARCHAR(20) NOT NULL,
	ProductCount INT DEFAULT 0,
	Price MONEY NOT NULL
);

INSERT INTO Products 
VALUES 
('iPhone 6', 'Apple', 3, 36000),
('iPhone 6S', 'Apple', 2, 41000),
('iPhone 7', 'Apple', 5, 52000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Mi6', 'Xiaomi', 5, 28000),
('OnePlus 5', 'OnePlus', 6, 38000)

Получим все объекты из этой таблицы:

SELECT * FROM Products

Символ звездочка * указывает, что нам надо получить все столбцы.

Получение всех столбцов с помощью символа звездочки * считается не очень хорошей практикой, так как, как правило, не все столбцы бывают нужны. И более оптимальный подход заключается в указании всех необходимых столбцов после слова SELECT. Исключение составляет тот случай, когда надо получить данные по абсолютно всем столбцам таблицы. Также использование символа * может быть предпочтительно в таких ситуациях, когда в точности не известны названия столбцов.

Если нам надо получить данные не по всем, а по каким-то конкретным столбцам, то тогда все эти спецификации столбцов перечисляются через запятую после SELECT:

SELECT ProductName, Price FROM Products

Спецификация столбца необязательно должна представлять его название. Это может быть любое выражение, например, результат арифметической операции. Так, выполним следующий запрос:

SELECT ProductName + ' (' + Manufacturer + ')', Price, Price * ProductCount 
FROM Products

Здесь при выборке будут создаваться три столбца. Первый столбец представляет результат объединения двух столбцов ProductName и Manufacturer. Второй столбец — стандартный столбец Price. А третий столбец представляет значение столбца Price, умноженное на значение столбца ProductCount.

С помощью оператора AS можно изменить название выходного столбца или определить его псевдоним:

SELECT 
ProductName + ' (' + Manufacturer + ')' AS ModelName, 
Price,  
Price * ProductCount AS TotalSum
FROM Products

В данном случае результатом выборки являются данные по 3-м столбцам. Первый столбец ModelName объединяет столбцы ProductName и Manufacturere, второй представляет стандартный столбец Price. Третий столбец TotalSum хранит произведение столбцов ProductCount и Price. При этом, как в случае со столбцом Price, необязательно определять название результирующего столбца с помощью AS.

DISTINCT

Оператор DISTINCT позволяет выбрать уникальные строки. Например, в нашем случае в таблице может быть по несколько товаров от одних и тех же производителей. Выберем всех производителей:

SELECT DISTINCT Manufacturer
FROM Products

В данном случае критерием разграничения строк является столбец Manufacturer. Поэтому в результирующей выборке будут только уникальные значения Manufacturer. И если, к примеру, в базе данных есть два товара с производителем Apple, то это название будет встречаться в результирующей выборке только один раз.

SELECT INTO

Выражение SELECT INTO позволяет выбрать из одной таблицы некоторые данные в другую таблицу, при этом вторая таблица создается автоматически. Например:

SELECT ProductName + ' (' + Manufacturer + ')' AS ModelName, Price
INTO ProductSummary
FROM Products

SELECT * FROM ProductSummary

После выполнения этой команды в базе данных будет создана еще одна таблица ProductSummary, которая будет иметь два столбца ModelName и Price, а данные для этих столбцов будут взяты из таблицы Products:

При выполнении этой команды таблица, в которую идет выборка (в данном случае ProductSummary), не должна существовать в базе данных.

Но, допустим, мы потом решили добавить все данные из таблицы Products в уже существующую таблицу ProductSummary. В этом случае можно опять же использовать команду INSERT:

INSERT INTO ProductSummary
SELECT ProductName + ' (' + Manufacturer + ')' AS ModelName, Price
FROM Products

Здесь добавляемые значения фактически представляют результат выборки из таблицы Products.

НазадВперед

範例Examples

A.A. 指定多個來源的資料行,藉以建立資料表Creating a table by specifying columns from multiple sources

下列範例會從各個員工相關和地址相關的資料表中選取七個資料行,藉以建立 AdventureWorks2012AdventureWorks2012 資料庫的 資料表。The following example creates the table in the AdventureWorks2012AdventureWorks2012 database by selecting seven columns from various employee-related and address-related tables.

B.B. 使用最低限度記錄來插入資料列Inserting rows using minimal logging

下列範例會建立 資料表,然後插入 資料表的資料列。The following example creates the table and inserts rows from the table. 此範例會假設 AdventureWorks2012AdventureWorks2012 資料庫的復原模式設定為 FULL。The example assumes that the recovery model of the AdventureWorks2012AdventureWorks2012 database is set to FULL. 為了確保使用最低限度記錄,AdventureWorks2012AdventureWorks2012 資料庫的復原模式會在插入資料列之前設定為 BULK_LOGGED,然後在 SELECT…INTO 陳述式之後重設為 FULL。To ensure minimal logging is used, the recovery model of the AdventureWorks2012AdventureWorks2012 database is set to BULK_LOGGED before rows are inserted and reset to FULL after the SELECT…INTO statement. 此程序可確保 SELECT…INTO 陳述式會在交易記錄中使用最小的空間並有效率地執行作業。This process ensures that the SELECT…INTO statement uses minimal space in the transaction log and performs efficiently.

C.C. 使用 IDENTITY 函數來建立識別欄位Creating an identity column using the IDENTITY function

下列範例會使用 IDENTITY 函數,在 AdventureWorks2012AdventureWorks2012 資料庫的新資料表 中建立識別欄位。The following example uses the IDENTITY function to create an identity column in the new table in the AdventureWorks2012AdventureWorks2012 database. 因為定義此資料表的 SELECT 陳述式包含聯結,導致 IDENTITY 屬性無法傳送至新的資料表,所以需要進行此步驟。This is required because the SELECT statement that defines the table contains a join, which causes the IDENTITY property to not transfer to the new table. 請注意,在 IDENTITY 函數中指定的初始和遞增值與來源資料表 中 資料行的初始和遞增值不同。Notice that the seed and increment values specified in the IDENTITY function are different from those of the column in the source table .

D.D. 指定遠端資料來源的資料行,藉以建立資料表Creating a table by specifying columns from a remote data source

下列範例將示範三種根據遠端資料來源在本機伺服器上建立新資料表的方法。The following example demonstrates three methods of creating a new table on the local server from a remote data source. 此範例一開始會建立遠端資料來源的連結。The example begins by creating a link to the remote data source. 然後,連結的伺服器名稱 會指定於第一個 SELECT…INTO 陳述式的 FROM 子句和第二個 SELECT…INTO 陳述式的 OPENQUERY 函數中。The linked server name, is then specified in the FROM clause of the first SELECT…INTO statement and in the OPENQUERY function of the second SELECT…INTO statement. 最後,第三個 SELECT…INTO 陳述式會使用 OPENDATASOURCE 函數,以便直接指定遠端資料來源,而非使用連結的伺服器名稱。The third SELECT…INTO statement uses the OPENDATASOURCE function, which specifies the remote data source directly instead of using the linked server name.


適用於: SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

E.E. 從使用 PolyBase 建立的外部資料表匯入Import from an external table created with PolyBase

將資料從 Hadoop 或 Azure 儲存體匯入至 SQL Server 以便持續儲存。Import data from Hadoop or Azure Storage into SQL Server for persistent storage. 請使用 來匯入外部資料表所參考的資料,以便持續儲存在 SQL Server 中。Use to import data referenced by an external table for persistent storage in SQL Server. 在第二個步驟中,快速建立關聯式資料表,然後在資料表上建立資料行存放區索引。Create a relational table on-the-fly and then create a column-store index on top of the table in a second step.

適用於: SQL Server 2019 (15.x)SQL Server 2019 (15.x)。Applies to: SQL Server 2019 (15.x)SQL Server 2019 (15.x).

S.S. 利用三個 SELECT 陳述式的 UNION 來顯示 ALL 和括號的作用Using UNION of three SELECT statements to show the effects of ALL and parentheses

下列範例會利用 來結合有 5 個相同資料列的三份資料表的結果。The following examples use to combine the results of three tables that all have the same 5 rows of data. 第一個範例利用 來顯示重複的記錄,以及傳回所有的 15 個資料列。The first example uses to show the duplicated records, and returns all 15 rows. 第二個範例利用不含 的 來刪除三個 陳述式之組合結果中重複的資料列,並傳回 5 個資料列。The second example uses without to eliminate the duplicate rows from the combined results of the three statements, and returns 5 rows.

第三個範例搭配第一個 來使用 ,用括號括住未使用 的第二個 。The third example uses with the first and parentheses enclose the second that is not using . 第二個 會先處理,因為它在括號中,且會傳回 5 個資料列,因為並未使用 選項,複本會移除。The second is processed first because it is in parentheses, and returns 5 rows because the option is not used and the duplicates are removed. 這 5 個資料列利用 關鍵字,與第一個 的結果結合起來。These 5 rows are combined with the results of the first by using the keywords. 這並不會在兩組 5 個資料列之間移除複本。This does not remove the duplicates between the two sets of 5 rows. 最終結果有 10 個資料列。The final result has 10 rows.

SELECT 陳述式的邏輯處理順序Logical Processing Order of the SELECT statement

下列步驟顯示 SELECT 陳述式的邏輯處理順序或繫結順序。The following steps show the logical processing order, or binding order, for a SELECT statement. 此順序會決定在某個步驟中定義的物件提供給後續步驟之子句使用的時間。This order determines when the objects defined in one step are made available to the clauses in subsequent steps. 例如,如果查詢處理器可繫結至 (存取) FROM 子句中定義的資料表或檢視表,這些物件及其資料行就會提供給所有後續步驟使用。For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. 反之,因為 SELECT 子句是步驟 8,所以前面的子句無法參考該子句中定義的任何資料行別名或衍生資料行。Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. 不過,ORDER BY 子句等後續子句都可以參考它們。However, they can be referenced by subsequent clauses such as the ORDER BY clause. 陳述式的實際執行方式由查詢處理器決定,其順序可能與此清單不同。The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  1. FROMFROM
  2. 開啟ON
  3. JOINJOIN
  4. WHEREWHERE
  5. GROUP BYGROUP BY
  6. WITH CUBE 或 WITH ROLLUPWITH CUBE or WITH ROLLUP
  7. HAVINGHAVING
  8. SELECTSELECT
  9. DISTINCTDISTINCT
  10. 排序依據ORDER BY
  11. 頂端TOP

警告

通常會按照上述順序。The preceding sequence is usually true. 不過,在一些不常見的情況下,順序可能會有不同。However, there are uncommon cases where the sequence may differ.

例如,假設您在檢視表上有叢集索引,而該檢視表排除某些資料表資料列,且檢視表的 SELECT 資料行清單使用 CONVERT 將資料類型從 varchar 變更為 integer。For example, suppose you have a clustered index on a view, and the view excludes some table rows, and the view’s SELECT column list uses a CONVERT that changes a data type from varchar to integer. 在此情況下,CONVERT 的執行順序可能會在 WHERE 子句之前。In this situation, the CONVERT may execute before the WHERE clause executes. 這確實是不常見的情況。Uncommon indeed. 如果在您的案例中順序相當重要,通常可以修改您的檢視表來避免順序不同。Often there is a way to modify your view to avoid the different sequence, if it matters in your case.

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

SELECT INTO

The command copies data from one table and inserts it into a new table.

The following SQL statement creates a backup copy of Customers:

SELECT * INTO CustomersBackup2017 FROM Customers;

The following SQL statement uses the IN clause to copy the table into a new table in another database:

SELECT * INTO CustomersBackup2017 IN ‘Backup.mdb’ FROM Customers;

The following SQL statement copies only a few columns into a new table:

SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers;

The following SQL statement copies only the German customers into a new table:

SELECT * INTO CustomersGermany FROM Customers WHERE Country = ‘Germany’;

The following SQL statement copies data from more than one table into a new table:


SELECT Customers.CustomerName, Orders.OrderIDINTO CustomersOrderBackup2017 FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SQL SELECT INTO Примеры

Следующая инструкция SQL создает резервную копию клиентов:

SELECT * INTO CustomersBackup2017 FROM Customers;

Следующая инструкция SQL использует предложение in для копирования таблицы в новую таблицу в другой базе данных:

SELECT * INTO CustomersBackup2017 IN ‘Backup.mdb’ FROM Customers;

Следующая инструкция SQL копирует только несколько столбцов в новую таблицу:

SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers;

Следующая инструкция SQL копирует только немецких клиентов в новую таблицу:

SELECT * INTO CustomersGermany FROM Customers WHERE Country = ‘Germany’;

Следующая инструкция SQL копирует данные из более чем одной таблицы в новую таблицу:

SELECT Customers.CustomerName, Orders.OrderIDINTO CustomersOrderBackup2017 FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Совет: SELECT INTO может также использоваться для создания новой пустой таблицы с использованием схемы другой. Просто добавьте предложение WHERE, которое заставляет запрос не возвращать данные:

SELECT * INTO newtableFROM oldtableWHERE 1 = 0;

❮ Назад Дальше ❯

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

M.M. 使用 HAVING 與 SUM 及 AVGUsing HAVING with SUM and AVG

下列範例會依產品識別碼來分組 資料表,且只會包括訂單總計超出 ,平均訂單數量小於 的產品群組。The following example groups the table by product ID and includes only those groups of products that have orders totaling more than and whose average order quantities are less than .

若要查看總銷售額大於 的產品,請使用這項查詢:To see the products that have had total sales greater than , use this query:

如果您要確定每項產品的計算都至少包含了一千五百個項目,請利用 來刪除傳回銷售總計小於 項的產品。If you want to make sure there are at least one thousand five hundred items involved in the calculations for each product, use to eliminate the products that return totals for fewer than items sold. 此查詢看起來像這樣:The query looks like this:

SQL Справочник

SQL Ключевые слова ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Функции Функции строк ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Функции чисел ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Функции дат ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Функции расширений BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server функции Функции строк ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER Функции чисел ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN Функции дат CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR Функции расширений CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access функции Функции строк Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase Функции чисел Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val Функции дат Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year Другие функции CurrentUser Environ IsDate IsNull IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник


С этим читают