Ключевые слова ALL и DISTINCT
Краткое описание операторов языка SQL
Работа с операторами SQL
Выбор данных
Выбор данных представляет собой наиболее часто встречающуюся операцию, выполняемую с помощью SQL. Оператор SELECT — один из самых важных операторов этого языка, применяемый для выбора данных. Синтаксис этого оператора имеет следующий вид:
SELECT column-list FROM table-list [WHERE where-clause] [ORDER BY order-by-clause]
Операторы SELECT должны содержать слова SELECT и FROM; другие ключевые слова, такие как WHERE или ORDER BY, являются необязательными.
За ключевым словом SELECT следуют сведения о том, какие именно поля необходимо включить в результирующий набор данных. Звездочка (*) обозначает все поля таблицы, например:
SELECT *
Для выбора одной колонки применяется следующий синтаксис:
SELECT CompanyName Пример выбора нескольких колонок имеет вид:
SELECT CompanyName, ContactName, ContactTitle
Если выбор данных осуществляется из нескольких таблиц и при этом выбираются одноименные поля из разных таблиц, следует ссылаться на имена таблиц для полной идентификации полей, включаемых в результирующий набор данных, например:
SELECT Customers.CompanyName, Shippers.CompanyName
Предложение FROM
Для указания имен таблиц, из которых выбираются записи, применяется ключевое слово FROM, например:
SELECT * FROM Customers
Этот запрос возвратит все поля из таблицы Customers.
Если в результирующем наборе данных нужны только поля CompanyName и ContactName, мы можем ввести следующее предложение SELECT:
SELECT CompanyName, ContactName FROM Customers
Пример запроса к более чем одной таблице приведен ниже:
SELECT Customers.CompanyName, Shippers.CompanyName FROM Customers, Shippers
Предложение WHERE
Для фильтрации результатов, возвращаемых оператором SELECT, можно использовать предложение WHERE, синтаксис которого имеет вид:
WHERE expression1 [{AND | OR} expression2 […]]
Например, вместо получения полного списка продуктов можно ограничиться только теми из них, у которых значение поля CategoryID равно 4:
SELECT * FROM Products WHERE CategoryID = 4
В предложении WHERE можно использовать различные выражения, например:
SELECT * FROM Products WHERE CategoryID = 2 AND SupplierID > 10
или:
SELECT ProductName, UnitPrice FROM Products WHERE CategoryID = 3 OR UnitPrice < 50
или:
SELECT ProductName, UnitPrice FROM Products WHERE Discontinued IS NOT NULL
Выражение ‘IS NOT NULL’ означает, что соответствующая колонка результирующего набора данных не может содержать пустых значений. В предложении WHERE можно использовать один из шести операторов отношений, определенных в SQL. Эти операторы приведены в табл. 1.
Таблица 1 Оператор Описание
< Меньше
<= Меньше или равно
<> Не равно
= Равно
> Больше
>= Больше или равно
Помимо перечисленных выше простых операторов сравнения, можно использовать и специальные операторы сравнения, приведенные в табл. 2.
Таблица 2 Оператор Описание
ALL Применяется совместно с операторами сравнения при сравнении со списком значений
ANY Применяется совместно с операторами сравнения при сравнении со списком значений
BETWEEN Применяется при проверке нахождения значения внутри заданного интервала (включая его границы)
IN Применяется для проверки наличия значения в списке
LIKE Применяется при проверке соответствия значения заданной маске
Приведем несколько примеров применения этих операторов. Для сопоставления данных с маской применяется ключевое слово LIKE:
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘M%’
В данной маске символ ‘%’ (процент) заменяет любую последовательность символов, а символ ‘_’ (подчеркивание) — один любой символ. Тот же самый результат может быть получен следующим способом:
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName BETWEEN ‘M’ AND ‘N’
В последнем примере мы можем расширить область поиска. В частности, при поиске компаний с именами, начинающимися с букв от A до C, можно выполнить следующий оператор SELECT:
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName BETWEEN ‘A’ AND ‘D’
Используя оператор LIKE, мы можем сузить диапазон поиска, применив более сложную маску для сравнения. Например, чтобы найти компании, содержащие в своем названии подстроку bl, можно применить следующий запрос:
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘%bl%’
Маска ‘%bl%’ показывает, что до и после искомой подстроки может быть любое количество произвольных символов.
Используя оператор IN, можно задать список значений, в котором должно содержаться значение поля:
SELECT CompanyName, ContactName FROM Customers WHERE CustomerID IN (‘ALFKI’, ‘BERGS’, ‘VINET’)
Операторы AND, OR и NOT
Мы уже рассматривали пример применения оператора AND для логических операций, связанных с требованием, чтобы запись удовлетворяла двум разным критериям. Рассмотрим следующий запрос:
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘S%’ AND Country = ‘USA’
Результатом выполнения этого запроса будет список заказчиков, находящихся в США, название которых начинается с буквы S.
Оператор OR позволяет выбрать записи, удовлетворяющие хотя бы одному из перечисленных условий, в то время как оператор NOT используется для исключения из набора данных записей, удовлетворяющих данному условию. Например, можно применить оператор OR для поиска всех заказчиков, либо находящихся в Калифорнии, либо имеющих название, начинающееся с буквы S (и при этом находящихся где угодно):
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘S%’ OR Region=’CA’
В этом случае результирующий набор данных будет содержать записи, в которых значение поля CompanyName удовлетворяет первому условию, плюс все записи, в которых значение поля Region удовлетворяет второму условию.
Теперь рассмотрим пример применения оператора NOT. Для исключения некоторых заказчиков из результирующего набора данных можно использовать запрос вида:
SELECT CompanyName, ContactName FROM Customers WHERE Country NOT IN (‘USA’, ‘UK’)
В результате выполнения этого запроса мы получим список заказчиков из всех стран, кроме США и Великобритании.
Предложение ORDER BY
Предложение ORDER BY (необязательное) применяется для сортировки результирующего набора данных по одной или нескольким колонкам. Для определения порядка сортировки используются ключевые слова ASC (по возрастанию) или DESC (по убыванию). По умолчанию данные сортируются по возрастанию. Синтаксис предложения ORDER BY имеет вид:
ORDER BY column1 [{ASC | DESC}] [, column2 [{ASC | DESC}] [,…]
Например, для сортировки сотрудников по фамилии и затем по имени следует использовать следующий SQL-запрос:
SELECT LastName, FirstName, Title FROM Employees ORDER BY LastName, FirstName
Если сортировка данных требуется в убывающем порядке (например, требуется список продуктов в порядке убывания цен), используется ключевое слово DESC:
SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC
Связывание таблиц
Как мы уже убедились, можно создавать запросы, позволяющие извлечь данные из нескольких таблиц. Одна из возможностей сделать это заключается в связывании таблиц по одному или нескольким полям. Обратите внимание на то, что без связывания таблиц в результате запроса получится набор данных, содержащий все возможные комбинации строк каждой из исходных таблиц (известное также как декартово произведение):
SELECT ProductName, CategoryName FROM Products, Categories
в то время как запрос, показанный ниже, приводит к отображению списка продуктов с указанием, к какой категории принадлежит данный продукт:
SELECT ProductName, CategoryName FROM Products, Categories WHERE Products.CategoryID = Categories.CategoryID
Можно сравнить результаты этих двух запросов. В общем случае синтаксис для связывания таблиц имеет вид:
SELECT column-list FROM table1, table2 WHERE table1.column1=table2.column2
Следующие несколько примеров связывания таблиц характерны для Microsoft Access и Microsoft SQL Server и могут не работать с другими СУБД, однако мы полагаем, что иллюстрируемая ими функциональность достаточно важна.
Существует несколько типов связывания таблиц. Например, следующий оператор SQL осуществляет так называемое внутреннее соединение таблиц (inner join) — в этом случае в результирующем наборе данных содержатся записи, в которых значения в связанных полях совпадают:
SELECT ProductName, CategoryName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
Так называемые внешние соединения (outer joins) позволяют нам включить в результат запроса все строки из одной таблицы и соответствующие им строки из другой таблицы. Например:
SELECT ProductName, CategoryName FROM Products LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID
Это было так называемое левое внешнее соединение (left outer join). Существуют также правые внешние соединения (right outer join), возвращающие все строки из второй (то есть правой) таблицы и соответствующие им строки из другой таблицы:
SELECT ProductName, CategoryName FROM Products RIGHT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID
Комбинируя левое и правое внешние соединения, можно получить полное внешнее соединение, возвращающее все данные из обеих таблиц:
SELECT ProductName, CategoryName FROM Products FULL OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID
Для получения всех комбинаций строк из обеих таблиц (декартова произведения) можно использовать ключевое слово CROSS JOIN без указания связываемых полей:
SELECT ProductName, CategoryName FROM Products CROSS JOIN Categories
Если в запросе используется более трех таблиц, можно иcпользовать вложенные соединения.
Предложение GROUP BY
Для вычисления суммарных значений на основе данных одной или нескольких таблиц можно использовать предложение GROUP BY, имеющее такой синтаксис:
GROUP BY {column1} [, …]
Например, следующий запрос связывает две таблицы, сортирует их по полю CustomerID, для каждого значения CustomerID создает одну строку в результирующем наборе данных и вычисляет количество значений поля OrderID для каждого значения CustomerID:
SELECT Customers.CustomerID, COUNT (Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID
В приведенном выше примере запроса мы использовали в предложении SELECT агрегатную функцию COUNT, вычисляющую количество значений.
Предложение HAVING
Предложение HAVING имеет назначение, сходное с предложением WHERE, но используется с агрегатными данными. Например:
SELECT Customers.CustomerID, COUNT (Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID HAVING COUNT(Orders.OrderID) >= 10
Этот запрос аналогичен предыдущему, но в результирующий набор данных включены только заказчики, разместившие десять или более заказов.
Ключевые слова ALL и DISTINCT
До этого момента мы рассматривали, как извлечь все или заданные колонки из одной или нескольких таблиц. Для управления выводом дублирующихся строк результирующего набора данных можно использовать ключевые слова ALL или DISTINCT в предложении SELECT. Ключевое слово DISTINCT указывает, что строки результирующего набора данных должны быть уникальны, тогда как ключевое слово ALL указывает, что возвращать следует все строки. Например, для извлечения названий стран, в которых имеются заказчики, можно использовать следующий запрос:
SELECT DISTINCT Country FROM Customers
Отметим, что ключевое слово ALL используется по определению. Если в запросе требуется вывести более одной колонки и при этом использовано слово DISTINCT, то результирующий набор данных будет содержать различные строки, но некоторые значения одного и того же поля в разных строках могут совпадать.
Ключевое слово TOP
Ключевое слово TOP может быть использовано для возврата первых n строк или первых n процентов таблицы. Например, запрос:
SELECT TOP 10 * FROM PRODUCTS ORDER BY ProductName
возвращает первые 10 продуктов из таблицы, тогда как запрос:
SELECT TOP 25 PERCENT * FROM PRODUCTS ORDER BY ProductName
вернет первую четверть записей таблицы.
Модификация данных
До сих пор мы изучали операторы SQL для извлечения данных. Помимо этого язык SQL может быть использован для обновления и удаления данных, копирования записей в другие таблицы и выполнения многих других операций. Ниже мы рассмотрим операторы UPDATE, DELETE и INSERT, используемые для решения некоторых из этих задач.
Оператор UPDATE
Для изменения значений в одной или нескольких колонках таблицы применяется оператор UPDATE. Синтакcис этого оператора имеет вид:
UPDATE tableSET column1 = expression1 [, column2 = expression2] [,…] [WHERE criteria]
Выражение в предложении SET может быть константой или результатом вычислений. Например, для повышения цен всех продуктов, стоящих меньше 10 долл., можно выполнить следующий запрос:
UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE UnitPrice < 10
Оператор DELETE
Для удаления строк из таблиц следует использовать оператор DELETE, синтаксис которого имеет вид:
DELETE FROM table [WHERE criteria]
Внимание! Предложение WHERE не является обязательным, но если вы забудете его включить, из таблицы будут удалены все записи.
Например, для удаления из списка всех продуктов, которые больше не поставляются, можно выполнить следующий запрос:
DELETE FROM Products WHERE Discontinued = 1
Отметим, что полезно использовать оператор SELECT с тем же синтаксисом, что и оператор DELETE, чтобы проверить, какие именно записи будут удалены, прежде чем действительно их удалять. Ниже показан оператор SELECT для приведенного выше запроса на удаление данных:
SELECT ProductName FROM Products WHERE Discontinued = 1
Можно использовать в предложении WHERE более сложный критерий для определения того, какие записи должны быть удалены. Предположим, нам нужно удалить из списка клиентов тех из них, кто не имел заказов до определенной даты. Сначала для этого следует выполнить следующий SELECT, чтобы определить, что именно мы удаляем:
SELECT CompanyName FROM Customers WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)
а затем заменить оператор SELECT на оператор DELETE:
DELETE FROM Customers WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)
Замечание. При использовании в операторах SQL даты или времени, а также полей, содержащих такие данные, следует уточнить синтаксис таких предложений в документации из комплекта поставки используемой СУБД.
Оператор INSERT
Для добавления записей в таблицы следует использовать оператор INSERT, синтаксис которого имеет вид:
INSERT [INTO] table ( [column_list] { VALUES ( { DEFAULT | NULL | expression } } [, …] )
Например, для добавления нового клиента в таблицу Customers можно использовать следующий запрос:
INSERT INTO Customers (CustomerID, CompanyName) VALUES (‘XYZFO’, ‘XYZ Deli’)
Модификация метаданных
Существует несколько операторов SQL для управления метаданными, используемых для создания, изменения или удаления баз данных и содержащихся в них объектов (таблиц, представлений и др.). Мы рассмотрим некоторые из них: CREATE TABLE, ALTER TABLE и DROP.
Оператор CREATE TABLE
Для создания новой таблицы необходимо использовать оператор CREATE TABLE, синтаксис которого имеет вид:
CREATE TABLE table ( column1 type1 [(size1)][CONSTRAINT _ column-constraint1] [, column2 type2 [(size2)][CONSTRAINT _ column-constraint2] [, ...]] [CONSTRAINT table-constraint1 _ [,table-constraint2 [, ...]]]);
В этом операторе следует указать имя поля, тип данных для него (тип данных должен поддерживаться данной СУБД), длину (для некоторых типов полей) и, если нужно, серверные ограничения (с применением ключевого слова CONSTRAINT). Например, следующий запрос создает таблицу с именем Simple с четырьмя колонками — LastName, FirstName, EMail и HomePage:
CREATE TABLE Simple (FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255) )
Мы можем расширить эту таблицу добавлением поля PersonID, которое будет использовано как первичный ключ:
CREATE TABLE Simple ( PersonID Integer NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255) )
и указать, что комбинация полей LastName и FirstName должна быть уникальна:
CREATE TABLE Simple ( PersonID Integer NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255), CONSTRAINT SimpleConstraint UNIQUE (FirstName, LastName) )
Используя предложение SELECT и ключевое слово INTO, мы можем создавать новые таблицы, основанные на условии, указанном в предложении WHERE. Например:
SELECT * INTO NewOrders FROM Orders WHERE OrderDate > 1/1/97 Этот запрос создаст новую таблицу NewOrders и заполнит ее данными о заказах начиная с 1 января 1997 года.
Оператор ALTER TABLE
Для изменения структуры существующей таблицы можно использовать оператор ALTER TABLE. Применяя его, можно добавить или удалить поле или серверное ограничение. Существует четыре разновидности оператора ALTER TABLE.
Первая разновидность этого оператора используется для добавления колонки к таблице, и ее синтаксис имеет вид:
ALTER TABLE table ADD [COLUMN] column datatype [(size)] [CONSTRAINT sinlge-column-constraint]
В запросах такого вида определяется имя таблицы, имя нового поля, его тип данных и, если нужно, размер. Помимо этого можно указать серверное ограничение, связанное с данным полем. Например, для добавления поля Phone к таблице Simple, созданной ранее, можно выполнить следующий запрос:
ALTER TABLE Simple ADD Phone varchar(30)
Вторая разновидность оператора ALTER TABLE применяется для добавления серверных ограничений к таблице, а ее синтаксис имеет вид:
ALTER TABLE table ADD CONSTRAINT constraint
Такие запросы позволяют только добавлять индексы, позволяющие использовать соответствующие поля в качестве первичных или внешних ключей.
Третья разновидность предложения ALTER TABLE применяется для удаления поля из таблицы:
ALTER TABLE table DROP [COLUMN] column
Ключевое слово COLUMN использовать не обязательно. Например:
ALTER TABLE Simple DROP Phone
Обратите внимание на то, что для удаления проиндексированных полей следует сначала удалить индекс. Это можно сделать с помощью четвертой разновидности предложения ALTER TABLE:
ALTER TABLE table DROP CONSTRAINT index
Ниже приведен пример такого запроса:
ALTER TABLE Simple DROP CONSTRAINT PrimaryKey
Оператор DROP
Для удаления таблиц или индексов можно использовать оператор DROP, имеющий две разновидности. Первая из них применяется для удаления таблицы из базы данных:
DROP TABLE table
Вторая разновидность используется для удаления индекса:
DROP INDEX index ON table