Как вывести столбец из таблицы sql

Примеры SELECT (Transact-SQL)

В этой статье приведены примеры использования инструкции SELECT .

В этой статье требуется AdventureWorks2022 пример базы данных, которую можно скачать на домашней странице примеров и проектов сообщества Microsoft SQL Server.

А. Использование SELECT для получения строк и столбцов

В следующем примере приведены три примера кода. В ходе выполнения первого примера кода возвращаются все строки (предложение WHERE не указано), а также все столбцы (используется звездочка, * ) таблицы Product базы данных AdventureWorks2022 .

USE AdventureWorks2022; GO SELECT * FROM Production.Product ORDER BY Name ASC; -- Alternate way. USE AdventureWorks2022; GO SELECT p.* FROM Production.Product AS p ORDER BY Name ASC; GO 

В ходе выполнения данного примера кода происходит выдача всех строк (предложение WHERE не задано) и подмножества столбцов ( Name , ProductNumber , ListPrice ) таблицы Product базы данных AdventureWorks2022 . Дополнительно выведено название столбца.

USE AdventureWorks2022; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product ORDER BY Name ASC; GO 

В ходе выполнения данного примера кода происходит выдача всех строк таблицы Product , для которых линейки продуктов начинаются символом R и для которых длительность изготовления не превышает 4 дней.

USE AdventureWorks2022; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = 'R' AND DaysToManufacture < 4 ORDER BY Name ASC; GO 

B. Использование SELECT с заголовками столбцов и вычислениями

В ходе выполнения следующего примера возвращаются все строки таблицы Product . В результате выполнения первого примера выдаются все объемы продаж и скидки по всем продуктам. Во втором примере вычисляется годовой доход от продажи каждого вида продукции.

USE AdventureWorks2022; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO 

Данный запрос вычисляет доход от продажи по каждому виду продукции для каждого заказа.

USE AdventureWorks2022; GO SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ', p.Name AS ProductName FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName ASC; GO 

C. Использование DISTINCT с SELECT

В приведенном ниже примере для предотвращения получения повторяющихся заголовков используется оператор DISTINCT .

USE AdventureWorks2022; GO SELECT DISTINCT JobTitle FROM HumanResources.Employee ORDER BY JobTitle; GO 

D. Создание таблиц с помощью SELECT INTO

В следующем примере в базе данных #Bicycles создается временная таблица tempdb .

USE tempdb; GO IF OBJECT_ID(N'#Bicycles', N'U') IS NOT NULL DROP TABLE #Bicycles; GO SELECT * INTO #Bicycles FROM AdventureWorks2022.Production.Product WHERE ProductNumber LIKE 'BK%'; GO 

В данном примере создается постоянная таблица NewProducts .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL DROP TABLE dbo.NewProducts; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED; GO SELECT * INTO dbo.NewProducts FROM Production.Product WHERE ListPrice > $25 AND ListPrice < $100; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL; GO 

Д. Использование сопоставленных вложенных запросов

Коррелированный запрос — это запрос, зависящий от результатов выполнения другого запроса. Этот запрос можно выполнять многократно, один раз для каждой строки, которая может быть выбрана внешним запросом.

В первом примере представлены семантически эквивалентные запросы для демонстрации различий в использовании ключевых слов EXISTS и IN . В обоих примерах приведены допустимые вложенные запросы, извлекающие по одному экземпляру продукции каждого наименования, для которых модель продукта — «long sleeve logo jersey» (кофта с длинными рукавами, с эмблемой), а значения столбцов ProductModelID таблиц Product и ProductModel совпадают.

USE AdventureWorks2022; GO SELECT DISTINCT Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND pm.Name LIKE 'Long-Sleeve Logo Jersey%' ); GO -- OR USE AdventureWorks2022; GO SELECT DISTINCT Name FROM Production.Product WHERE ProductModelID IN ( SELECT ProductModelID FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND Name LIKE 'Long-Sleeve Logo Jersey%' ); GO 

В следующем примере используется и извлекается IN один экземпляр первого имени и имени семьи каждого сотрудника, для которого указан 5000.00 бонус в SalesPerson таблице, и для которого идентификаторы сотрудников совпадают в Employee таблицах и SalesPerson таблицах.

USE AdventureWorks2022; GO SELECT DISTINCT p.LastName, p.FirstName FROM Person.Person AS p INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN ( SELECT Bonus FROM Sales.SalesPerson AS sp WHERE e.BusinessEntityID = sp.BusinessEntityID ); GO 

Предыдущий вложенный запрос в этом операторе нельзя оценивать независимо от внешнего запроса. Он требует значения параметра Employee.EmployeeID , однако это значение меняется, когда ядро СУБД SQL Server обрабатывает строки в Employee .

Коррелированный вложенный запрос также может использоваться в предложении HAVING внешнего запроса. В данном примере осуществляется поиск моделей продуктов, для которых максимальная цена в каталоге в два раза превышает среднюю цену по нему.

USE AdventureWorks2022; GO SELECT p1.ProductModelID FROM Production.Product AS p1 GROUP BY p1.ProductModelID HAVING MAX(p1.ListPrice) >= ( SELECT AVG(p2.ListPrice) * 2 FROM Production.Product AS p2 WHERE p1.ProductModelID = p2.ProductModelID ); GO 

В этом примере используются два сопоставленных вложенных запроса для поиска имен сотрудников, которые продали определенный продукт.

USE AdventureWorks2022; GO SELECT DISTINCT pp.LastName, pp.FirstName FROM Person.Person pp INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN ( SELECT SalesPersonID FROM Sales.SalesOrderHeader WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ProductID IN ( SELECT ProductID FROM Production.Product p WHERE ProductNumber = 'BK-M68B-42' ) ) ); GO 

F. Использование GROUP BY

В следующем примере находится общий объем продаж для каждого заказа в базе данных.

USE AdventureWorks2022; GO SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY SalesOrderID ORDER BY SalesOrderID; GO 

Так как в запросе используется предложение GROUP BY , то для каждого заказа выводится только одна строка, содержащая общий объем продаж.

G. Использование GROUP BY с несколькими группами

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

USE AdventureWorks2022; GO SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price], SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY ProductID, SpecialOfferID ORDER BY ProductID; GO 

H. Использование GROUP BY и WHERE

В следующем примере после извлечения строк, содержащих цены каталога, превышающие $1000 , происходит их разделение на группы.

USE AdventureWorks2022; GO SELECT ProductModelID, AVG(ListPrice) AS [Average List Price] FROM Production.Product WHERE ListPrice > $1000 GROUP BY ProductModelID ORDER BY ProductModelID; GO 

I. Использование GROUP BY с выражением

В следующем примере производится группировка с помощью выражения. Можно сгруппировать по выражению, если выражение не включает агрегатные функции.

USE AdventureWorks2022; GO SELECT AVG(OrderQty) AS [Average Quantity], NonDiscountSales = (OrderQty * UnitPrice) FROM Sales.SalesOrderDetail GROUP BY (OrderQty * UnitPrice) ORDER BY (OrderQty * UnitPrice) DESC; GO 

J. Использование GROUP BY с ORDER BY

В следующем примере для каждого типа продуктов вычисляется средняя цена, а также осуществляется сортировка полученных результатов по возрастанию.

USE AdventureWorks2022; GO SELECT ProductID, AVG(UnitPrice) AS [Average Price] FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ProductID ORDER BY AVG(UnitPrice); GO 

K. Использование предложения HAVING

В первом из приведенных ниже примеров показывается использование предложения HAVING с агрегатной функцией. В нем производится группировка строк таблицы SalesOrderDetail по коду продукта, а также удаляются строки, соответствующие продуктам, для которых средний объем заказа не превышает пяти. Во втором примере показывается использование предложения HAVING без агрегатной функции.

USE AdventureWorks2022; GO SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID; GO 

В данном запросе внутри предложения LIKE используется предложение HAVING .

USE AdventureWorks2022; GO SELECT SalesOrderID, CarrierTrackingNumber FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, CarrierTrackingNumber HAVING CarrierTrackingNumber LIKE '4BD%' ORDER BY SalesOrderID ; GO 

L. Использование HAVING и GROUP BY

В следующем примере показано использование предложений GROUP BY , HAVING , WHERE и ORDER BY в одной инструкции SELECT . В результате его выполнения в группах и сводных значениях не учитываются строки, соответствующие продуктам с ценами выше $25 и средним объемом заказов ниже 5. Также осуществляется сортировка результатов по ProductID .

USE AdventureWorks2022; GO SELECT ProductID FROM Sales.SalesOrderDetail WHERE UnitPrice < 25.00 GROUP BY ProductID HAVING AVG(OrderQty) >5 ORDER BY ProductID; GO 

M. Использование HAVING с СУММ и AVG

В следующем примере производится группировка строк таблицы SalesOrderDetail по коду продукта, а затем выводятся только те группы, для которых общий объем продаж составляет более $1000000.00 , а средний объем заказа не превышает 3 .

USE AdventureWorks2022; GO SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3; GO 

Чтобы просмотреть продукты с общим объемом продаж, превышающих $2000000.00 , используйте следующий запрос:

USE AdventureWorks2022; GO SELECT ProductID, Total = SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $2000000.00; GO 

Если вы хотите убедиться в наличии не менее 1500 элементов, участвующих в вычислениях для каждого продукта, используйте HAVING COUNT(*) > 1500 для устранения продуктов, возвращающих итоги для меньшего количества 1500 проданных элементов. Этот запрос выглядит следующим образом.

USE AdventureWorks2022; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 1500; GO 

О. Использование указания оптимизатора INDEX

В следующем примере показаны два способа использования указания оптимизатора INDEX . В первом примере показано, как принудительно принудить оптимизатора использовать некластеризованный индекс для получения строк из таблицы. Во втором примере выполняется проверка таблицы с помощью индекса 0.

USE AdventureWorks2022; GO SELECT pp.FirstName, pp.LastName, e.NationalIDNumber FROM HumanResources.Employee AS e WITH (INDEX (AK_Employee_NationalIDNumber)) INNER JOIN Person.Person AS pp ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Force a table scan by using INDEX = 0. USE AdventureWorks2022; GO SELECT pp.LastName, pp.FirstName, e.JobTitle FROM HumanResources.Employee AS e WITH (INDEX = 0) INNER JOIN Person.Person AS pp ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO 

M. Использование OPTION и подсказок GROUP

В следующем примере демонстрируется совместное использование предложений OPTION (GROUP) и GROUP BY .

USE AdventureWorks2022; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (HASH GROUP, FAST 10); GO 

O. Использование указания запроса UNION

В следующем примере используется указание запроса MERGE UNION .

USE AdventureWorks2022; GO SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours FROM HumanResources.Employee AS e1 UNION SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours FROM HumanResources.Employee AS e2 OPTION (MERGE UNION); GO 

P. Использование UNION

При выполнении следующего примера в результирующий набор включается содержимое столбцов ProductModelID и Name таблиц ProductModel и Gloves .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO -- Here is the simple union. USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO 

В. Использование SELECT INTO с UNION

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

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.ProductResults', 'U') IS NOT NULL DROP TABLE dbo.ProductResults; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO USE AdventureWorks2022; GO SELECT ProductModelID, Name INTO dbo.ProductResults FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO SELECT ProductModelID, Name FROM dbo.ProductResults; 

R. Использование UNION двух операторов SELECT с ORDER BY

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

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO /* INCORRECT */ USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) ORDER BY Name UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO /* CORRECT */ USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO 

S. Использование UNION трех инструкций SELECT для отображения эффектов ALL и круглых скобок

В следующих примерах используются UNION для объединения результатов трех таблиц, которые имеют одинаковые пять строк данных. В первом примере используется предложение UNION ALL , в результате чего выдаются все 15 строк. Второй пример используется без ALL исключения повторяющихся UNION строк из объединенных результатов трех SELECT операторов и возвращает пять строк.

В третьем примере с первым предложением ALL используется ключевое слово UNION , а во втором предложении UNION вместо ключевого слова ALL используются скобки. Второй UNION обрабатывается сначала, так как он находится в скобках, и возвращает пять строк, так как ALL параметр не используется и дубликаты удаляются. Эти пять строк объединяются с результатами первого SELECT с помощью UNION ALL ключевое слово. В данном случае повторяющиеся строки двух множеств, состоящих из пяти строк, не удаляются. Окончательный результат состоит из 10 строк.

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.EmployeeOne', 'U') IS NOT NULL DROP TABLE dbo.EmployeeOne; GO IF OBJECT_ID('dbo.EmployeeTwo', 'U') IS NOT NULL DROP TABLE dbo.EmployeeTwo; GO IF OBJECT_ID('dbo.EmployeeThree', 'U') IS NOT NULL DROP TABLE dbo.EmployeeThree; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeOne FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeTwo FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeThree FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Union ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL ( SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree ); GO 

Связанный контент

• CREATE TRIGGER (Transact-SQL)
• CREATE VIEW (Transact-SQL)
• DELETE (Transact-SQL)
• EXECUTE (Transact-SQL)
• Выражения (Transact-SQL)
• INSERT (Transact-SQL)
• LIKE (Transact-SQL)
• Операторы set — UNION (Transact-SQL)
• Операторы set — EXCEPT и INTERSECT (Transact-SQL)
• UPDATE (Transact-SQL)
• WHERE (Transact-SQL)
• PathName (Transact-SQL)
• SELECT — предложение INTO (Transact-SQL)

Обратная связь

Были ли сведения на этой странице полезными?

SQL - Урок 4. Выборка данных - оператор SELECT

Итак, в нашей БД forum есть три таблицы: users (пользователи), topics (темы) и posts (сообщения). И мы хотим посмотреть, какие данные в них содержатся. Для этого в SQL существует оператор SELECT. Синтаксис его использования следующий:

SELECT что_выбрать FROM откуда_выбрать;

Вместо "что_выбрать" мы должны указать либо имя столбца, значения которого хотим увидеть, либо имена нескольких столбцов через запятую, либо символ звездочки (*), означающий выбор всех столбцов таблицы. Вместо "откуда_выбрать" следует указать имя таблицы.

Давайте сначала посмотрим все столбцы из таблицы users:

SELECT * FROM users;

Вот и все наши данные, которые мы вносили в эту таблицу. Но предположим, что мы хотим посмотреть только столбец id_user (например, в прошлом уроке, нам надо было для заполнения таблицы topics (темы) знать, какие id_user есть в таблице users). Для этого в запросе мы укажем имя этого столбца:

SELECT id_user FROM users;

Ну, а если мы захотим посмотреть, например, имена и e-mail наших пользователей, то мы перечислим интересующие столбцы через запятую:

SELECT name, email FROM users;

Аналогично, вы можете посмотреть, какие данные содержат и другие наши таблицы. Давайте посмотрим, какие у нас существуют темы:

SELECT * FROM topics;

Сейчас у нас всего 4 темы, а если их будет 100? Хотелось бы, чтобы они выводились, например, по алфавиту. Для этого в SQL существует ключевое слово ORDER BY после которого указывается имя столбца по которому будет происходить сортировка. Синтаксис следующий:

SELECT имя_столбца FROM имя_таблицы ORDER BY имя_столбца_сортировки;

По умолчанию сортировка идет по возрастанию, но это можно изменить, добавив ключевое слово DESC

Теперь наши данные отсортированы в порядке по убыванию.

Сортировку можно производить сразу по нескольким столбцам. Например, следующий запрос отсортирует данные по столбцу topic_name, и если в этом столбце будет несколько одинаковых строк, то в столбце id_author будет осуществлена сортировка по убыванию:

Сравните результат с результатом предыдущего запроса.

Очень часто нам не нужна вся информация из таблицы. Например, мы хотим узнать, какие темы были созданы пользователем sveta (id=4). Для этого в SQL есть ключевое слово WHERE, синтаксис у такого запроса следующий:

SELECT имя_столбца FROM имя_таблицы WHERE условие;

Для нашего примера условием является идентификатор пользователя, т.е. нам нужны только те строки, в столбце id_author которых стоит 4 (идентификатор пользователя sveta):

SELECT * FROM topics WHERE id_author=4;

Или мы хотим узнать, кто создал тему "велосипеды":

Конечно, было бы удобнее, чтобы вместо id автора, выводилось его имя, но имена хранятся в другой таблице. В последующих уроках мы узнаем, как выбирать данные из нескольких таблиц. А пока узнаем, какие условия можно задавать, используя ключевое слово WHERE.

Оператор	Описание
= (равно)	Отбираются значения равные указанному

SELECT * FROM topics WHERE id_author=4;

SELECT * FROM topics WHERE id_author>2;

SELECT * FROM topics WHERE id_author =2;

Научись программировать на Python прямо сейчас!

• Научись программировать на Python прямо сейчас
• Бесплатный курс

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

Как вывести столбец из таблицы sql

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

mysql> SELECT 1 + 1; -> 2

Все используемые ключевые слова должны быть даны точно в порядке, показанном выше. Например, предложение HAVING должно прийти после любого предложения GROUP BY и перед любым предложением ORDER BY .

Выражение SELECT может применять псевдоним, используя AS . Псевдоним используется при задании в выражении имени столбца и может быть указан с предложениями ORDER BY или HAVING . Пример:

mysql> select concat(last_name,', ',first_name) AS full_name from mytable ORDER BY full_name;

mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name; mysql> select t1.name, t2.salary from employee t1, info t2 where t1.name = t2.name;

mysql> select college, region, seed from tournament ORDER BY region, seed; mysql> select college, region AS r, seed AS s from tournament ORDER BY r, s; mysql> select college, region, seed from tournament ORDER BY 2, 3;

mysql> select col_name from tbl_name HAVING col_name > 0;

Пишите это взамен:

mysql> select col_name from tbl_name WHERE col_name > 0;

В MySQL Version 3.22.5 или позже Вы можете также писать запросы подобно этому:

mysql> select user,max(salary) from users group by user HAVING max(salary)>10;

В старых версиях MySQL Вы можете писать взамен это:

mysql> select user,max(salary) AS sum from users group by user HAVING sum>10;

SELECT a,COUNT(b) FROM test_table GROUP BY a DESC

mysql> select * from table LIMIT 5,10; # Вернуть строки 6-15

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

mysql> select * from table LIMIT 5; # Вернуть первые 5 строк

• Сам символ ESCAPED BY
• Первый символ в FIELDS TERMINATED BY
• Первый символ в LINES TERMINATED BY

SELECT a,b,a+b INTO OUTFILE "/tmp/result.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" FROM test_table;

MySQL поддерживает следующие синтаксисы JOIN для применения в операторе выбора SELECT :

table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference INNER JOIN table_reference join_condition table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference join_condition table_reference LEFT [OUTER] JOIN table_reference table_reference NATURAL [LEFT [OUTER]] JOIN table_reference < oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr >table_reference RIGHT [OUTER] JOIN table_reference join_condition table_reference RIGHT [OUTER] JOIN table_reference table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Здесь table_reference определен как:

table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]

а join_condition как:

ON conditional_expr | USING (column_list)

Вы никогда не должны иметь никаких условий в части ON , которые используются, чтобы ограничить то, какие строки Вы получите в наборе результатов. Если Вы хотите оговорить то, которые строки должны быть в результате, Вы должны делать это в предложении WHERE .

Обратите внимание, что в версиях до Version 3.23.17 INNER JOIN не обрабатывал join_condition !

Последний синтаксис LEFT OUTER JOIN , показанный выше, существует только для совместимости с ODBC:

Ссылка таблицы может быть оснащена псевдонимом с использованием tbl_name AS alias_name или tbl_name alias_name :

mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;

mysql> select table1.* from table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id is NULL;

A LEFT JOIN B USING (C1,C2,C3. )

Определено, чтобы быть семантически идентичным выражению ON :

A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3.

Вот некоторые примеры:

mysql> select * from table1,table2 where table1.id=table2.id; mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> select * from table1 LEFT JOIN table2 USING (id); mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id; mysql> select * from table1 USE INDEX (key1,key2) WHERE key1=1 and key2=2 AND key3=3; mysql> select * from table1 IGNORE INDEX (key3) WHERE key1=1 and key2=2 AND key3=3;

SELECT . UNION [ALL] SELECT . [UNION SELECT . ]

UNION реализован только в версии MySQL 4.0.0 и выше.

UNION используется, чтобы объединить результаты из многих инструкций SELECT в один набор результатов.

Команды SELECT представляют собой нормальные команды выбора, но со следующими ограничениями:

• Только последняя команда SELECT может иметь INTO OUTFILE .
• Только последняя команда SELECT может иметь ORDER BY .

Если Вы не используете ключевое слово ALL для UNION , все возвращенные строки будут уникальны, подобно тому, как если бы Вы сделали DISTINCT для общего набора результатов. Если Вы определяете ALL , то Вы получите все строки соответствий из всех используемых инструкций SELECT .

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name. )] VALUES (expression. ),(. ). INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name. )] SELECT . INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=expression, col_name=expression, .

INSERT вставляет новые строки в существующую таблицу. Форма INSERT . VALUES вставляет строки, основываясь на явно определенных значениях. INSERT . SELECT вставляет строки, выбранные из другой таблицы или таблиц. Форма INSERT . VALUES с несколькими списками значений поддержана в MySQL Version 3.22.5 или позже. Синтаксис col_name=expression введен, начиная с MySQL Version 3.22.10 или выше.

tbl_name таблица, в которую строки должны быть вставлены. Список имен столбцов или предложение SET указывает, для которых столбцов инструкция определяет значения:

• Если Вы не определяете никакого списка столбцов для INSERT . VALUES или INSERT . SELECT , значения для всех столбцов нужно обеспечить в списке VALUES() или через SELECT . Если Вы не знаете порядка столбцов в таблице, используйте DESCRIBE tbl_name , чтобы выяснить.
• Любой столбец, для которого значение явно не задано, будет установлен в значение по умолчанию. Например, если Вы определяете список столбцов, который не называет все столбцы в таблице, неназванные столбцы будут установлены в их значения по умолчанию. Задание значений по умолчанию описано в разделе "7.3 Синтаксис CREATE TABLE ".
• Выражение expression может обратиться к любому столбцу, который был установлен ранее в списке значения. Например, можно задать:

mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

Но нельзя скомандовать:

mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

Если Вы используете INSERT . SELECT или INSERT . VALUES со многими списками значений, Вы можете использовать функцию C API mysql_info() , чтобы получить информацию относительно запроса. Формат информационной строки показан ниже:

Records: 100 Duplicates: 0 Warnings: 0

Duplicates указывает число строк, которые не могли быть вставлены потому, что они дублируют некоторое существующее уникальное индексное значение. Warnings показывает число попыток вставить значения столбца, которые были проблематичны по какой-либо причине. Предупреждения могут происходить при любом из следующих условий:

• Вставка NULL в столбец, который был объявлен как NOT NULL . Столбец установлен в значение по умолчанию.
• Установка числового столбца в значение, которое находится вне диапазона столбца. Значение отсечено к соответствующей границе диапазона.
• Установка числового столбца к значению типа 10.34 a . Конечный будет мусор удален, и остающаяся числовая часть будет вставлена. Если значение не имеет смысла как число вообще, столбец установлен в 0 .
• Вставка строки, которая превышает максимальную длину столбца, в столбец типов CHAR , VARCHAR , TEXT или BLOB . Значение усечено к максимальной длине столбца.
• Вставка значения, которое является запрещенным для этого типа столбца, в столбец даты или времени. Столбец установлен к соответствующему нулевому значению для типа.

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT .

Командой INSERT . SELECT Вы можете быстро вставлять много строк в таблицу из одной или нескольких таблиц.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;

Следующие условия работают для команды INSERT . SELECT :

• Целевая таблица инструкции INSERT не может появляться в предложении FROM части SELECT запроса потому, что в ANSI SQL запрещено выполнять SELECT над той же самой таблицей, в которую Вы вставляете. Проблема состоит в том, что SELECT , возможно, нашел бы записи, которые были вставлены ранее в течение той же самой операции. При использовании предложений sub-select, ситуация могла бы очень легко запутаться!
• Столбцы AUTO_INCREMENT работают как обычной.
• Вы можете использовать функцию C API mysql_info() , чтобы получить информацию относительно запроса. Подробности в разделе "8.3 Синтаксис INSERT ".
• Чтобы гарантировать, что модификации регистрируются и могут использоваться, чтобы пересоздать первоначальные таблицы, MySQL не будет позволять параллельные вставки в течение выполнения INSERT . SELECT .

Вы можете, конечно, также использовать REPLACE вместо INSERT , чтобы перезаписать старые строки.

INSERT DELAYED .

Опция DELAYED для инструкции INSERT специфична для MySQL и является очень полезной, если Вы имеете клиентуру, которая не может ждать завершения INSERT . Это общая проблема, когда Вы используете MySQL для регистрации и также периодически выполняете инструкции SELECT и UPDATE , которые занимают много времени. DELAYED введен в MySQL Version 3.22.15. Это MySQL-расширение для стандарта ANSI SQL92.

INSERT DELAYED работает только с таблицами ISAM и MyISAM . Обратите внимание, что таблицы MyISAM поддерживают параллельные SELECT и INSERT , если не имеется никаких свободных блоков в середине файла данных, Вы очень редко должны использовать INSERT DELAYED с таблицами типа MyISAM .

Когда Вы используете INSERT DELAYED , клиент получит сообщение OK сразу, а вот строка будет вставлена, когда таблица не будет использоваться любым другим потоком.

Другая польза от применения INSERT DELAYED в том, что вставки многих клиентов будут связаны вместе и записаны в одном блоке. Это намного быстрее, чем выполнение многих отдельных вставок.

Обратите внимание, что в настоящее время поставленные в очередь строки сохранены только в памяти, пока они не вставлены в таблицу. Это означает, что, если Вы уничтожаете mysqld крутыми мерами ( kill -9 ), или если mysqld рухнет неожиданно, любые поставленные в очередь строки, которые не были записаны на диск, потеряются!

Следующее описывает подробно, что случается, когда Вы используете опцию DELAYED вместе с командами INSERT или REPLACE . В этом описании "поток" представляет собой поток, который получил команду INSERT DELAYED , и "драйвер" обозначает поток, который обрабатывает все инструкции INSERT DELAYED для специфической таблицы.

• Когда поток выполняет инструкцию DELAYED для таблицы, будет создан драйвер: чтобы обработать все инструкции DELAYED для этой таблицы, если никакого такого драйвера еще не существует.
• Поток проверяет, приобрел или нет драйвер блокировку DELAYED . Если нет, он сообщает, чтобы драйвер это сделал. Блокировка DELAYED может быть получена, даже если другие потоки имеют блокировку READ или WRITE на таблице. Однако, драйвер будет ждать ALTER TABLE или FLUSH TABLES , чтобы гарантировать, что текущая структура таблицы современна.
• Поток выполняет инструкцию INSERT , но вместо того, чтобы записать строку в таблицу, он помещает копию конечной строки в очередь, которая управляется драйвером. Любые ошибки синтаксиса будут отмечены потоком и сообщены программе пользователя.
• Пользователь не может сообщать число дубликатов или значения AUTO_INCREMENT для возникающей в результате строки. Он не может получить эти данные с сервера, поскольку возвраты INSERT перед операцией вставки был завершены.
• Файл регистрации модификаций обновляется драйвером, когда строка будет реально вставлена в таблицу. В случае вставки нескольких строк файл регистрации модификаций меняется, когда вставлена первая строка.
• После того, как каждые delayed_insert_limit строк написаны, драйвер проверяет, являются или нет любые инструкции SELECT ждущими своей очереди. Если это так, драйвер позволяет им выполниться перед продолжением своей работы.
• Когда драйвер больше не имеет строк в очереди, он разблокирует таблицу. Если никакая новая команда INSERT DELAYED не появилась в течение последних delayed_insert_timeout секунд, драйвер завершается.
• Если больше, чем delayed_queue_size строк, ждущих обработки, уже в специфической очереди драйверов, поток, запрашивающий INSERT DELAYED ждет до появления участка памяти в очереди. Это выполнено, чтобы гарантировать, что сервер mysqld не использует всю память для отсроченной очереди.
• Поток драйвера обнаружится в списке процесса MySQL с меткой delayed_insert в столбце Command . Это будет уничтожаться, если Вы выполняете команду FLUSH TABLES или уничтожаете поток с помощью KILL thread_id . Однако, драйвер сначала сохранит все поставленные в очередь строки в таблицу перед выходом. В это время система не будет принимать никакие новые команды INSERT от других потоков. Если Вы выполняете команду INSERT DELAYED после того, как это было выполнено, новый поток драйвера будет создан.
• Обратите внимание, что вышеупомянутое означает, что команды INSERT DELAYED имеют более высокий приоритет, чем нормальные команды INSERT , если уже имеется драйвер INSERT DELAYED ! Другие команды модификации должны будут ждать до опустошения очереди INSERT DELAYED , уничтожения драйвера (через KILL thread_id ) или выполнения FLUSH TABLES .
• Следующие переменные состояния обеспечивают информацию относительно выполнения команд INSERT DELAYED :

Переменная	Значение
Delayed_insert_threads	Число обработчиков потоков
Delayed_writes	Число строк, вставленных с помощью INSERT DELAYED
Not_flushed_delayed_rows	Число строк, ждущих записи

Обратите внимание, что INSERT DELAYED медленнее, чем нормальная INSERT, если таблица сейчас не используется. Имеется также дополнительная нагрузка для сервера, чтобы обработать отдельный поток для каждой таблицы, на которой Вы используете INSERT DELAYED . Это означает, что Вы должны использовать INSERT DELAYED только тогда, когда Вы, действительно, уверены, что нуждаетесь в этом!

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, . ] [WHERE where_definition] [LIMIT #]

UPDATE модифицирует столбцы в существующих строках таблиц с новыми значениями. Предложение SET указывает, какие столбцы изменять, и значения, которые им должны быть заданы. Предложение WHERE , если задано, определяет, какие строки должны модифицироваться. Иначе модифицируются все строки. Если определено предложение ORDER BY , строки будут модифицироваться в том порядке, который определен.

Если Вы определяете ключевое слово LOW_PRIORITY , выполнение UPDATE будет отсрочено до наступления момента, когда никакие клиенты не читают из таблицы.

Если Вы определяете ключевое слово IGNORE , инструкция модификации не будет прерываться, даже если мы получаем ошибки дублирования ключа в течение модификации. Строки, которые могли бы вызвать конфликты, не будут модифицироваться вообще.

Если Вы обращаетесь к столбцу из tbl_name в выражении, UPDATE использует текущее значение столбца. Например, следующая инструкция устанавливает столбец age к следующему за его текущим значением:

mysql> UPDATE persondata SET age=age+1;

Назначения UPDATE оценены слева направо. Например, следующая инструкция удваивает столбец age , затем увеличивает его на 1:

mysql> UPDATE persondata SET age=age*2, age=age+1;

Если Вы устанавливаете столбец к значению, которое он в настоящее время имеет, MySQL не обращает внимания на это и не модифицирует столбец!

UPDATE возвращает число строк, которые были фактически изменены. В MySQL Version 3.22 или позже функция C API mysql_info() возвращает число строк, которые были согласованы и модифицировались, и число предупреждений, которые произошли в течение UPDATE .

В MySQL Version 3.23 Вы можете использовать LIMIT # , чтобы гарантировать, что только заданное число строк будет изменено.

DELETE [LOW_PRIORITY | QUICK] FROM table_name [WHERE where_definition] [ORDER BY . ] [LIMIT rows] DELETE [LOW_PRIORITY | QUICK] table_name[.*] [table_name[.*] . ] FROM table-references [WHERE where_definition]

DELETE удаляет строки, которые удовлетворяют условию, заданному в where_definition из таблицы table_name и возвращает число удаленных записей.

Если Вы выдаете DELETE без предложения WHERE , все строки будут удалены. Если Вы делаете это в режиме AUTOCOMMIT , это сработает аналогично TRUNCATE . Подробности в разделе "8.7 Синтаксис TRUNCATE ". В MySQL 3.23 DELETE без предложения WHERE возвратит ноль как число обработанных записей.

Если Вы действительно хотите знать, сколько записей удалены, когда Вы удаляете все строки из таблицы, Вы можете использовать инструкцию DELETE этой формы:

mysql> DELETE FROM table_name WHERE 1>0;

Обратите внимание, что это НАМНОГО медленнее, чем DELETE FROM table_name без предложения WHERE потому, что эта команда удаляет строки по одной.

Если Вы определяете ключевое слово LOW_PRIORITY , выполнение DELETE будет отсрочено до наступления момента, когда никакие клиенты не читают из таблицы.

Если Вы определяете слово QUICK , то драйвер таблицы не будет объединять индексные листья в течение процесса удаления, что может ускорять некоторые виды удаления.

В MyISAM-таблицах удаленные записи поддерживаются в связанном списке, и старые позиции записей повторно используются вызовом INSERT . Чтобы освободить неиспользуемое место и уменьшить размеры файла, используйте команду OPTIMIZE TABLE или утилиту myisamchk , чтобы реорганизовать таблицы. OPTIMIZE TABLE проще, но myisamchk быстрее. Подробности в разделе "4.6 Синтаксис OPTIMIZE TABLE ".

Вы можете удалять строки из многих таблиц в то же самое время, а также иметь дополнительные таблицы, которые используются для поиска.

Символ .* после имени таблицы появился для совместимости с Access :

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

В вышеупомянутом случае мы удаляем строки соответствий только из таблиц t1 и t2 .

ORDER BY и использование нескольких таблиц в DELETE реализовано, начиная с MySQL 4.0.

Если предложение ORDER BY используется, строки будут удалены в заданном порядке. Это удобно в сочетании с LIMIT . Например:

DELETE FROM somelog WHERE user='jcole' ORDER BY timestamp LIMIT 1

Это удалит самую старую запись (по timestamp ), где строка соответствует предложению WHERE .

MySQL-специфичная опция LIMIT rows функции DELETE сообщает серверу максимальное число строк, которые будут удалены прежде, чем управление будет возвращено пользователю. Это может использоваться, чтобы гарантировать, что специфическая команда DELETE не займет слишком много времени. Вы можете просто повторять команду DELETE , пока число обрабатываемых строк не меньше, чем значение LIMIT .

TRUNCATE TABLE table_name

В версии 3.23 TRUNCATE TABLE отображен на вызов COMMIT; DELETE FROM table_name . Подробности в разделе "8.6 Синтаксис DELETE ".

Разница между TRUNCATE TABLE и DELETE FROM .. :

• Truncate удаляет и пересоздает таблицу, что намного быстрее, чем удаление строк одну за другой.
• Не транзакционно-безопасно: Вы получите ошибку, если имеете активную транзакцию или активную блокировку таблицы.
• Не возвращает число удаленных строк.
• Пока файл определения таблицы table_name.frm корректен, таблица может быть пересоздана, даже если данные или индексный файл повреждены.

TRUNCATE представляет собой Oracle-расширение языка SQL.

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name. )] VALUES (expression. ),(. ). REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name. )] SELECT . REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name=expression, col_name=expression.

REPLACE работает в точности подобно INSERT , за исключением того, что, если старая запись в таблице имеет то же самое значение, что и новая запись на уникальном индексе, старая запись будет удалена прежде, чем новая запись вставлена в таблицу. Подробности в разделе "8.3 Синтаксис INSERT ".

Другими словами, Вы не можете обращаться к значениям старой строки из инструкции REPLACE . В некоторых старых версиях MySQL могли, но это было ошибкой, которая исправлена.

При использовании вызова REPLACE mysql_affected_rows() возвратит 2, если новая строка заменила старую. Это потому, что в этом случае одна строка была вставлена, а затем дубликат был удален.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [(col_name. )]

Команда LOAD DATA INFILE читает строки из текстового файла в таблицу. Если ключевое слово LOCAL определено, файл читается с компьютера пользователя. Если LOCAL не определено, файл должен быть размещен на сервере. LOCAL доступен в MySQL Version 3.22.6 или более поздней.

По соображениям безопасности, при чтении текстовых файлов, размещенных на сервере, файлы должны находиться в каталоге баз данных или быть читаемыми всеми. Также, чтобы использовать LOAD DATA INFILE на файлах сервера, Вы должны иметь привилегию file . Подробности в разделе "10.6 Привилегии, предоставляемые MySQL".

Если Вы определяете ключевое слово LOW_PRIORITY , выполнение LOAD DATA будет отложено до тех пор, пока другая клиентура не прекратит читать из таблицы.

Если Вы определяете ключевое слово CONCURRENT с таблицей типа MyISAM , то другие потоки могут получать данные из таблицы в то время, как выполняется LOAD DATA . Использование этой опции будет, конечно, воздействовать на эффективность LOAD DATA , даже если никакой другой поток не использует таблицу в то же самое время.

Использование LOCAL будет немного медленнее, чем разрешение серверу обращаться к файлам непосредственно потому, что содержание файла должно пропутешествовать с компьютера пользователя на сервер. С другой стороны, Вы не нуждаетесь в привилегии file , чтобы загрузить свои локальные файлы.

Если Вы используете MySQL ниже 3.23.24, Вы не можете читать из FIFO с помощью LOAD DATA INFILE . Если Вы должны читать из FIFO (например, вывод из gunzip), используйте вместо этого команду LOAD DATA LOCAL INFILE .

Вы можете также загружать файлы данных, используя полезность mysqlimport . Она функционирует, посылая команду LOAD DATA INFILE на сервер. Опция --local заставляет mysqlimport читать файлы данных с компьютера пользователя. Вы можете определять опцию --compress , чтобы получить лучшую эффективность при работе с медленными сетями, если пользователь и сервер поддерживают сжатый протокол.

При размещении файлов на компьютере сервера, он использует правила:

• Если абсолютное имя пути задано, сервер использует его как есть.
• Если относительное имя пути с одним или более ведущими компонентами задано, поиск файла выполняется относительно каталога данных сервера.
• Если имя файла без ведущих компонентов задано, сервер ищет файл в каталоге текущей базы данных.

Обратите внимание, что эти правила означают, что файл, заданный как ./myfile.txt , читается из каталога данных сервера в то время, как файл myfile.txt читается из каталога текущей базы данных. Например, следующая инструкция LOAD DATA читает файл data.txt из каталога базы данных для db1 потому, что db1 текущая база данных, даже при том, что инструкция явно загружает файл в таблицу в базе данных db2 :

mysql> USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

Ключевые слова REPLACE и IGNORE контролируют обработку записей, которые дублируют существующие записи на уникальных значениях ключа. Если Вы определяете REPLACE , новые строки заменяют существующие, которые имеют то же самое уникальное значение ключа. Если Вы определяете IGNORE , строки, которые дублируют существующие на уникальном значении ключа, будут пропущены. Если Вы не определяете никакую опцию, ошибка происходит, когда будет найдено двойное значение ключа, и остальная часть текстового файла игнорируется.

Если Вы загружаете данные из локального файла, используя ключевое слово LOCAL , сервер не имеет никакого способа остановить передачу файла в середине операции, так что значение по умолчанию такое же, как если определена опция IGNORE .

Если Вы используете LOAD DATA INFILE на пустой таблице MyISAM , все неуникальные индексы будут созданы в отдельном пакете (подобно REPAIR ). Это обычно делает LOAD DATA INFILE намного быстрее, когда Вы имеете много индексов.

LOAD DATA INFILE представляет собой дополнение к SELECT . INTO OUTFILE . Подробности в разделе "8.1 Синтаксис SELECT ". Чтобы писать данные из базы данных в файл, используйте SELECT . INTO OUTFILE . Чтобы читать файл обратно в базу данных, используйте LOAD DATA INFILE . Синтаксис предложений FIELDS и LINES тот же самый для обеих команд. Оба предложения факультативные, но FIELDS должно предшествовать LINES , если оба определены.

Если Вы определяете предложение FIELDS , каждое из подпредложений ( TERMINATED BY , [OPTIONALLY] ENCLOSED BY и ESCAPED BY ) также факультативны за исключением того, что Вы должны определить по крайней мере одно из них.

Если Вы не определяете предложение FIELDS , значения по умолчанию были бы такие же, как если бы Вы написали это:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Если Вы не определяете предложение LINES , значения по умолчанию были бы такие же, как если бы Вы написали это:

LINES TERMINATED BY '\n'

Другими словами, значения по умолчанию заставляют LOAD DATA INFILE действовать следующим образом при чтении ввода:

• Найти границы строки по newlines.
• Разделить строки на поля через символы табуляции.
• Не ожидать, что поля будут заключены внутрь любых символов цитирования.
• Интерпретировать местонахождения позиций табуляции, newline или \ предшествующего \ как литеральные символы, которые являются частью значений поля.

SELECT . INTO OUTFILE по умолчанию должен действовать следующим образом при записи вывода:

• Записать позиции табуляции между полями.
• Не включать поля внутрь любых символов цитирования.
• Использовать \ , чтобы выйти из позиций табуляции, newline или символов \ , которые происходят внутри значений поля.
• Записать newlines в конце строк.

Обратите внимание, что, чтобы писать FIELDS ESCAPED BY '\\' , Вы должны определить две наклонных черты влево для любого значения, которое нужно читать как одиночную наклонную черту влево.

Опция IGNORE number LINES может использоваться, чтобы игнорировать заголовок столбца в начале файла:

mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;

Когда Вы используете SELECT . INTO OUTFILE в тандеме с LOAD DATA INFILE , чтобы записать данные из базы данных в файл, а затем читаете файл обратно в базу данных позже, параметры обработки поля и строк для обеих команд должны соответствовать. Иначе LOAD DATA INFILE не будут интерпретировать содержание файла правильно. Предположите, что Вы используете SELECT . INTO OUTFILE , чтобы записать файл с полями, разграниченными запятыми:

mysql> SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM . ;

Чтобы прочитать разграниченный запятыми файл обратно в базу данных, правильная инструкция была бы такой:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';

Если взамен Вы пробовали читать файл инструкцией, показанной ниже, это не будет работать потому, что это инструктирует LOAD DATA INFILE искать позиции табуляции между полями:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '\t';

Вероятный результат: каждая входная строка интерпретируется как одиночное поле без разделения на поля.

LOAD DATA INFILE может использоваться, чтобы читать файлы, полученные из внешних источников. Например, файл в формате dBASE будет иметь поля, разделяемые запятыми и заключенные в двойные кавычки. Если строки в файле завершены newlines, команда, показанная ниже, иллюстрирует параметры обработки, которые Вы использовали бы, чтобы загрузить файл:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Любое из поля или параметров обработки может определять пустую строку ( '' ). Если она не пустая, значения FIELDS [OPTIONALLY] ENCLOSED BY и FIELDS ESCAPED BY должны быть одиночным символом. Значения FIELDS TERMINATED BY и LINES TERMINATED BY могут быть больше, чем одним символом. Например, чтобы писать строки, которые завершены парами carriage return-linefeed, или читать файл, содержащий такие строки, определите LINES TERMINATED BY '\r\n' .

Например, чтобы читать файл записей, которые отделяются строкой %% , в таблицу SQL, Вы можете сделать:

create table jokes (a int not null auto_increment primary key, joke text not null); load data infile "/tmp/jokes.txt" into table jokes fields terminated by "" lines terminated by "\n%%\n" (joke);

FIELDS [OPTIONALLY] ENCLOSED BY управляет цитированием полей. Для вывода ( SELECT . INTO OUTFILE ), если Вы опускаете слово OPTIONALLY , все поля включены символом ENCLOSED BY . Пример такого вывода (использование запятой как разделителя поля) ниже:

"1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \" quote","102.20" "4","a string containing a \", quote and comma","102.20"

Если Вы определяете OPTIONALLY , символ ENCLOSED BY используется только для того, чтобы включить поля типов CHAR и VARCHAR :

1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a \" quote",102.20 4,"a string containing a \", quote and comma",102.20

Обратите внимание, что местонахождение символа ENCLOSED BY внутри значения поля экранируется приписыванием символа ESCAPED BY . Также обратите внимание, что, если Вы определяете пустое значение ESCAPED BY , возможно генерировать вывод, который не может читаться правильно средствами LOAD DATA INFILE . Например, вывод, показанный выше, появился бы как показано ниже, если бы символ ESC был пустым. Заметьте, что второе поле в четвертой строке содержит запятую после кавычки, которая ошибочно появляется и завершает поле:

1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20

Для ввода символ ENCLOSED BY , если он вообще задан, будет удален с концов значений поля. Это истинно, определен или нет OPTIONALLY . OPTIONALLY не имеет никакого эффекта на входной интерпретации. Местонахождения символа ENCLOSED BY , которому предшествует символ ESCAPED BY , интерпретируются как часть текущего значения поля. Кроме того, дублированные символы ENCLOSED BY , встречающиеся внутри полей, интерпретируются как одиночные символы ENCLOSED BY , если поле непосредственно начинается этим символом. Например, если определено ENCLOSED BY '"' , кавычки будут обработаны как показано ниже:

"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss

• Символ FIELDS ESCAPED BY .
• Символ FIELDS [OPTIONALLY] ENCLOSED BY .
• Первый символ значений FIELDS TERMINATED BY и LINES TERMINATED BY .
• ASCII 0 (что фактически написано после символа ASCII 0 , не нулевой байт).

Если символ FIELDS ESCAPED BY пуст, никакие символы не экранируются вовсе. Не стоит определять пустой символ ESC, особенно, если значения полей в Ваших данных содержат любой из символов в списке выше.

Для ввода, если символ FIELDS ESCAPED BY не пуст, местонахождения этого символа будут удалены, и следующий символ принимается буквально как часть значения поля. Исключительные ситуации: 0 или N (например, \0 или \N , если символ ESC задан как \ ). Эти последовательности интерпретируются как 0 и NULL соответственно. Ниже изложены правила для обработки NULL .

За подробностями относительно синтаксиса экранировки с символом \ отсылаю Вас к разделу "3.1 Литералы: как писать строки и числа".

В некоторых случаях параметры обработки полей и строк взаимодействуют:

• Если LINES TERMINATED BY пустая строка, а FIELDS TERMINATED BY не пустая, строки также будут завершены FIELDS TERMINATED BY .
• Если обе строки FIELDS TERMINATED BY и FIELDS ENCLOSED BY пусты ( '' ), используется фиксированный формат. С этим форматом никакие разделители не используются между полями. Вместо этого, значения столбцов пишутся и читаются, используя "экранную" ширину. Например, если столбец объявлен как INT(7) , значения для столбца будут написаны, используя 7-символьные поля. На вводе значения для столбца будут получены, читая 7 символов. Формат фиксированной строки также воздействует на обработку значений NULL (см. ниже). Обратите внимание, что этот формат не будет работать, если Вы используете многобайтный набор символов.

Обработка значений NULL изменяется, в зависимости от параметров FIELDS и LINES , которые Вы используете:

• Для заданных по умолчанию значений FIELDS и LINES NULL будет написан как \N для вывода, а \N читается как NULL для ввода (подразумевается, что символ ESCAPED BY определен как \ ).
• Если FIELDS ENCLOSED BY не пуст, поле, содержащее литеральное слово NULL , читается как значение NULL (это отличается от слова NULL , заключенного внутри символов FIELDS ENCLOSED BY , которое читается как строка 'NULL' ).
• Если FIELDS ESCAPED BY пустое, NULL будет написан как слово NULL .
• С форматом фиксированной строки (когда FIELDS TERMINATED BY и FIELDS ENCLOSED BY оба пусты), NULL будет записан как пустая строка. Обратите внимание, что это делает неразличимыми значения NULL и пустые строки в таблице, когда они записаны в файл потому, что и то и другое пишется как как пустые строки. Если Вы должны при чтении файла иметь возможность разобраться что есть что, не следует применять фиксированный формат строки.

• Строки фиксированной длины ( FIELDS TERMINATED BY и FIELDS ENCLOSED BY вместе пустые) в сочетании со столбцами BLOB или TEXT .
• Если Вы определяете один разделитель, который является префиксом другого, команда LOAD DATA INFILE не будет способна интерпретировать ввод правильно. Например, следующее предложение FIELDS проблемное:

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

Следующий пример загружает все столбцы таблицы persondata :

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Никакой список полей не определен, так что LOAD DATA INFILE ожидает строки, содержащие поле для каждого столбца таблицы. Используются заданные по умолчанию значения FIELDS и LINES .

Если Вы желаете загрузить только некоторые из столбцов таблицы, определите список полей:

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2. );

Вы должны также определить список полей, если порядок полей во входном файл отличается от порядка столбцов в таблице. Иначе MySQL не может выяснить, как совмещать входные поля со столбцами таблицы.

Если строка имеет слишком мало полей, столбцы, для которых никакого входного поля не присутствует, будут установлены в значения по умолчанию. Задание этих значений по умолчанию подробно описано в разделе "7.3 Синтаксис CREATE TABLE ".

Пустое значение поля интерпретируется не так, как если бы оно отсутствовало вовсе, а именно:

• Для строковых типов, столбец установлен в пустую строку.
• Для числовых типов, столбец установлен в 0 .
• Для типов даты и времени, столбец установлен к соответствующему нулю для типа. Подробности в разделе "5.2 Типы Date и Time".

Обратите внимание, что здесь все происходит точно так же, как если Вы назначаете пустую стргоку явно как значение соответствующего поля с помощью вызовов INSERT или UPDATE .

Столбцы TIMESTAMP будут установлены только к текущей дате и времени, если для столбца имеется значение NULL , или (только для первого столбца TIMESTAMP ) если столбец типа TIMESTAMP не учтен в списке полей, когда такой список определен. Если входная строка имеет слишком много полей, лишние поля игнорируются.

LOAD DATA INFILE расценивает весь ввод как строки, так что Вы не можете использовать числовые значения для столбцов типов ENUM или SET , как Вы можете это делать инструкциями INSERT . Все значения ENUM и SET должны быть определены как строки!

Если Вы используете C API, Вы можете получать информацию относительно запроса, вызывая функцию mysql_info() когда запрос LOAD DATA INFILE обработан. Формат информационной строки показывается ниже:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

Предупреждения происходят при тех же самых обстоятельствах, что и с командой INSERT (подробности в разделе "8.3 Синтаксис INSERT "), за исключением того, что LOAD DATA INFILE также генерирует предупреждения, когда имеется слишком мало или слишком много полей во входной строке. Предупреждения не сохранены где-нибудь. Число предупреждений может использоваться только как индикация, если все пошло нормально. Если Вы получаете предупреждения и хотите знать точно, почему Вы их получили, один способ сделать это состоит в том, чтобы использовать SELECT . INTO OUTFILE в другой файл и сравнить его с первоначальным входным файлом.

Если Вы нуждаетесь в LOAD DATA , чтобы читать из канала, Вы можете использовать следующий прием:

mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat < /dev/tcp/10.1.1.12/4711 >/nt/mysql/db/x/x mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Если Вы используете версию MySQL до 3.23.25, Вы можете делать вышеупомянутое только с помощью LOAD DATA LOCAL INFILE .

SQL SELECT и запросы на выборку данных

Оператор языка SQL SELECT - основная строительная конструкция для создания любого, простого или сложного запроса к базе данных. Без него, как и без фундамента для постройки, невозможно получить ни одну выборку данных из базы. На этом уроке мы узнаем, как построить запрос для получения

• простой выборки данных без условий (выбор всех строк во всех столбцах или всех строк в определённых столбцах);
• выборки данных с одним или несколькими условиями (выбор определённых строк), которые заданы в секции WHERE с помощью предикатов и дополительный операторов;
• как использовать оператор SELECT в подзапросах .

SELECT для выбора столбцов таблицы

Запрос с оператором SELECT для выбора всех столбцов таблицы имеет следующий синтаксис:

SELECT * FROM ИМЯ_ТАБЛИЦЫ

То есть для выбора всех столбцов таблицы после слова SELECT нужно ставить звёздочку.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Работать будем с базой данных фирмы - Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными - в файле по этой ссылке .

Пример 1. Итак, есть база данных фирмы - Company1. В ней есть таблица Org (Структура фирмы) и Staff (Сотрудники). Требуется выбрать из таблиц все столбцы. Соответствующий запрос для выбора всех столбцов из таблицы Org выглядит следующим образом (на MS SQL Server - с предваряющей конструкцией USE company1;):

SELECT * FROM Org

Этот запрос вернёт следующее (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Запрос для выбора всех столбцов из таблицы Staff выглядит следующим образом (на MS SQL Server - с предваряющей конструкцией USE company1;):

SELECT * FROM Staff

Этот запрос вернёт следующее:

Для выбора определённых столбцов таблицы нам потребуется вместо звёздочки перечислить через запятую названия всех столбцов, которые требуется выбрать:

SELECT ВЫБИРАЕМЫЕ_СТОЛБЦЫ FROM ИМЯ_ТАБЛИЦЫ

Пример 2. Пусть требуется из таблицы Org выбрать столбцы Depnumb и Deptname, в которых содержатся данные соответственно о номерах отделов фирмы и об их названиях. Запрос для получения такой выборки будет следующим (на MS SQL Server - с предваряющей конструкцией USE company1;):

SELECT Deptnumb, Deptname, FROM Org

А из таблицы Staff нужно выбрать столбцы Dept, Name, Job, в которых содержатся соответственно данные о номере отдела, в котором трудится сотрудник, его имени и должности (на MS SQL Server - с предваряющей конструкцией USE company1;):

SELECT Dept, Name, Job FROM Staff

• SELECT и WHERE для выбора строк таблицы
• Использование SELECT и предикатов IN, AND, OR, BETWEEN, LIKE
• SELECT и ORDER BY - сортировка (упорядочение) строк
• SELECT и DISTINCT - удаление дубликатов строк
• Оператор SELECT в подзапросах SQL
• Группировка вместе с сортировкой в запросе: что ставится раньше - GROUP BY или ORDER BY?
• Оператор SELECT для получения выборок из нескольких таблиц