Перед тем как погрузиться в детали SQL, в общих чертах познакомимся с языком, его перспективами и принципами работы. В данной статье приводится краткий обзор основных его возможностей и функций. Ее цель не в том, чтобы сделать вас специалистом по написанию инструкций SQL (это задача последующих публикаций моего блога). Данная статья дает лишь общее представление о возможностях языка.
Простая база данных
Примеры, приводимые в этой статье, основаны на простой реляционной базе данных маленькой торговой компании. Структура этой базы данных изображена на рис. 1. В ней хранится информация, необходимая для реализации небольшого приложения по обработке заказов. Инструкции по созданию этой простой базы данных вы найдете в приложении А, "Учебная база данных", так что можете проверять работу описываемых запросов на практике прямо во время чтения. В базе хранится следующая информация:
- о клиентах, которые покупают товары компании;
- о заказах, сделанных клиентами;
- о служащих компании, которые продают товары клиентам;
- об офисах, где работают служащие.
Эта база данных, как и большинство других, является моделью "реального мира". Данные, содержащиеся в ней, представляют реальные сущности: клиенты, заказы, служащие компании и офисы. Для каждой сущности имеется собственная отдельная таблица. Например, в таблице SALESREPS каждый продавец представлен отдельной строкой, а каждый столбец хранит определенную информацию о продавце, такую как его имя или офис, где он работает. Запросы к базе данных, создаваемые с помощью SQL, отражают события, происходящие в реальном мире: клиенты делают, отменяют и изменяют заказы, владелец компании нанимает и увольняет служащих и т.д. Давайте посмотрим, что можно делать с этими данными с помощью SQL.
Рис. 1. Простая реляционная база данных
Выборка данных при помощи SQL
Вначале просмотрим список офисов с указанием города, где размещается офис, и объема продаж офиса с начала года по текущий день. Инструкция SQL, которая извлекает информацию из базы данных, называется select. Приведенная ниже инструкция SQL выбирает из базы данных интересующую вас информацию.
SELECT CITY, OFFICE, SALES
FROM OFFICES;
CITY OFFICE SALES
------------ ------- ------------
Denver 22 $186,042.00
New York 11 $692,637.00
Chicago 12 $735,042.00
Atlanta 13 $367,911.00
Los Angeles 21 $835,915.00Инструкция SELECT применяется во всех SQL-запросах на выборку данных. Так, далее приведен запрос, который запрашивает список имен и текущих объемов продаж по всем служащим в базе данных. Кроме того, в запросе приводится планируемый объем продаж и номер офиса, где работает служащий. В этом случае данные извлекаются из таблицы SALESREPS. Инструкция SELECT запрашивает для каждого офиса три вида данных: город, номер офиса и объем продаж. Еще она определяет, что данные находятся в таблице OFFICES, в которой хранится информация об офисах. Результаты запроса приведены сразу после рассматриваемой инструкции в форме таблицы. Заметим, что форматирование вывода результатов запроса зависит от конкретной реализации SQL и может изменяться при переходе от одной СУБД к другой.
SELECT NAME, REP_OFFICE, SALES, QUOTA FROM SALESREPS;
NАМЕ REP OFFICE SALES QUOTA
------------- ---------- ----------- -----------
Bill Adams 13 $367,911.00 $350,000.00
Mary Jones 11 $392,725.00 $300,000.00
S11e Smith 21 $474,050.00 $350,000.00
Sam Clark 11 $299,912.00 $275,000.00
ВоЬ Smith 12 $142,594.00 $200,000.00
Dan Roberts 12 $305,673.00 $300,000.00
Tom Snyder NULL $75,985.00 NULL
Larry Fitch 21 $361,865.00 $350,000.00
Paul Cruz 12 $286,775.00 $275,000.00
Nancy Angelli 22 $186,042.00 $300,000.00SQL позволяет также запрашивать вычисляемые результаты. Например, можно попросить вычислить сумму, на которую каждый служащий опережает план или отстает от него. Значения NULL в строке продавца Тома Снайдера представляют отсутствующие или неизвестные данные. Он — новичок в компании и пока не получил ни назначения в определенный офис, ни планируемый объем продаж. Но тем не менее он уже осуществил некоторое количество продаж, о чем свидетельствуют выведенные данные в его строке.
SELECT NAME, SALES, QUOTA, (SALES – QUOTA)
FROM SALESREPS;
NAME SALES QUOTA (SALES–QUOTA)
-------------- ------------ ------------ --------------
Bill Adams $367,911.00 $350,000.00 $17,911.00
Mary Jones $392,725.00 $300,000.00 $92,725.00
Sue Smith $474,050.00 $350,000.00 $124,050.00
Sam Clark $299,912.00 $275,000.00 $24,912.00
Bob Smith $142,594.00 $200,000.00 –$57,406.00
Dan Roberts $305,673.00 $300,000.00 $5,673.00
Tom Snyder $75,985.00 NULL NULL
Larry Fitch $361,865.00 $350,000.00 $11,865.00
Paul Cruz $286,775.00 $275,000.00 $11,775.00
Nancy Angelli $186,042.00 $300,000.00 –$113,958.00Запрашиваемые данные (включая вычисленную разницу между объемом продаж и планом) снова представлены в виде строк и столбцов таблицы. Возможно, вы хотели бы получить сведения о служащих, которые не выполняют план. SQL позволяет легко получить такую информацию, добавив в предыдущий запрос операцию сравнения.
SELECT NAME, SALES, QUOTA, (SALES – QUOTA)
FROM SALESREPS
WHERE SALES < QUOTA;
NAME SALES QUOTA (SALES–QUOTA)
-------------- ------------ ------------ -------------
Bob Smith $142,594.00 $200,000.00 –$57,406.00
Nancy Angelli $186,042.00 $300,000.00 –$113,958.00С помощью этого же приема можно получить список больших заказов и определить, кто сделал конкретный заказ, какие товары и в каких количествах были заказаны. SQL, кроме того, позволяет упорядочить заказы по их стоимости.
SELECT ORDER_NUM, CUST, PRODUCT, QTY, AMOUNT
FROM ORDERS
WHERE AMOUNT > 25000.00
ORDER BY AMOUNT;
ORDER_NUM CUST PRODUCT QTY AMOUNT
---------- ----- -------- ---- -----------
112987 2103 4100Y 11 $27,500.00
113069 2109 775C 22 $31,350.00
112961 2117 2A44L 7 $31,500.00
113045 2112 2A44R 10 $45,000.00
Получение итоговых данных в SQL
SQL можно использовать не только для выборки данных, но и для получения итоговых значений по содержимому базы данных. Какова средняя стоимость заказов в базе данных? Следующий запрос обеспечивает вычисление средней стоимости.
SELECT AVG(AMOUNT)
FROM ORDERS;
AVG(AMOUNT)
------------
$8,256.37Можно также узнать среднюю стоимость всех заказов, сделанных конкретным клиентом.
SELECT AVG(AMOUNT)
FROM ORDERS
WHERE CUST = 2103;
AVG(AMOUNT)
-----------
$8,895.50Наконец, давайте найдем общую стоимость всех заказов, сделанных каждым клиентом. Для этого сгруппируем заказы по номерам клиентов, а затем просуммируем их по каждому клиенту.
SELECT CUST, SUM(AMOUNT)
FROM ORDERS
GROUP BY CUST;
CUST SUM(AMOUNT)
----- ------------
2101 $1,458.00
2102 $3,978.00
2103 $35,582.00
2106 $4,026.00
2107 $23,132.00
2108 $7,255.00
2109 $31,350.00
2111 $6,445.00
2112 $47,925.00
2113 $22,500.00
2114 $22,100.00
2117 $31,500.00
2118 $3,608.00
2120 $3,750.00
2124 $3,082.00
Добавление данных (INSERT)
SQL можно использовать и для добавления в таблицы новых данных. Предположим, что вы открыли в Далласе новый офис "Western" с плановым объемом продаж $275000. Ниже приведена инструкция INSERT, которая добавляет в соответствующую таблицу новый офис с номером 23.
INSERT INTO OFFICES (CITY, REGION, TARGET, SALES, OFFICE)
VALUES ('Dallas', 'Western', 275000.00, 0.00, 23);
1 row inserted.Аналогично, если служащая Мери Джонс (номер 109) заключает договор с новым клиентом, компанией Acme Industries, приведенная ниже инструкция INSERT добавит в соответствующую таблицу имя клиента с номером 2125 и лимитом кредита в $25000.
INSERT INTO CUSTOMERS (COMPANY, CUST_REP, CUST_NUM, CREDIT_LIMIT)
VALUES ('Acme Industries', 109, 2125, 25000.00);
1 row inserted.
Как можно заметить, механизм базы данных возвращает сообщение (1 row inserted) о том, что инструкция сработала. Точный текст и форматирование ответа варьируются от одной реализации SQL к другой.
Удаление данных (DELETE)
Точно так же, как инструкция INSERT добавляет в таблицу новые данные, инструкция DELETE удаляет данные из таблицы. Если через несколько дней компания Acme Industries решит отказаться от ваших услуг и уйти к конкуренту, вы сможете удалить из базы данных информацию о ней с помощью следующей инструкции.
DELETE FROM CUSTOMERS
WHERE COMPANY = 'Acme Industries';
1 row deleted.Если вы решите уволить всех служащих, чей объем продаж меньше плана, то сможете удалить их имена из базы данных с помощью такой инструкции.
DELETE FROM SALESREPS
WHERE SALES < QUOTA;
2 rows deleted.
Обновление данных (UPDATE)
SQL можно использовать и для обновления информации, уже содержащейся в базе данных. Например, чтобы увеличить лимит кредита для компании First Corp, до $75000, можно воспользоваться следующей инструкцией UPDATE.
UPDATE CUSTOMERS
SET CREDIT_LIMIT = 75000.00
WHERE COMPANY = 'First Corp.';
1 row updated.С помощью инструкции UPDATE можно вносить в базу данных несколько обновлений одновременно. Например, следующая инструкция UPDATE увеличивает план для всех продавцов на $15000.
UPDATE SALESREPS
SET QUOTA = QUOTA + 15000.00;
8 rows updated.
Защита данных и права доступа
Важной задачей базы данных является защита информации от несанкционированного доступа. Предположим, ваш секретарь Мери прежде не имела разрешения на ввод в базу данных сведений о новых клиентах. С помощью следующей инструкции SQL можно дать ей такое разрешение.
GRANT INSERT
ON CUSTOMERS
TO MARY;
Privilege granted.Аналогично приведенная ниже инструкция дает Мери разрешение на изменение данных о клиентах и чтение информации о них с помощью SELECT.
GRANT UPDATE, SELECT
ON CUSTOMERS
TO MARY;
Privilege granted.Если вы решите запретить Мери добавлять в базу данных сведения о новых клиентах, для этого достаточно воспользоваться инструкцией REVOKE.
REVOKE INSERT
ON CUSTOMERS
FROM MARY;
Privilege revoked.Точно так же следующая инструкция revoke отменит все привилегии Мери на доступ и модифицирование данных о клиентах.
REVOKE ALL
ON CUSTOMERS
FROM MARY;
Privilege revoked.
Создание базы данных
Для того чтобы в базе данных можно было хранить информацию, сначала необходимо определить ее структуру. Предположим, что вы хотите расширить нашу учебную базу данных, добавив в нее таблицу с информацией о товарах, которые продает ваша компания. Для каждого товара должны храниться следующие данные:
- идентификатор производителя — три символа;
- идентификатор товара — пять символов;
- описание — до тридцати символов;
- цена товара;
- количество товара.
Приведенная далее инструкция CREATE table определяет новую таблицу для хранения указанных данных о товарах.
CREATE TABLE PRODUCTS
(MFR_ID CHAR(3),
PRODUCT_ID CHAR(5),
DESCRIPTION VARCHAR(30),
PRICE DECIMAL(9,2),
QTY_ON_HAND INTEGER);
Table created.Хотя инструкция CREATE TABLE не столь очевидна, как инструкции, рассматривавшиеся ранее, тем не менее она все же довольно проста. Эта инструкция присваивает новой таблице имя PRODUCTS и определяет для каждого из пяти ее столбцов имя и тип данных, хранимых в нем. Идентификаторы производителя и товара хранятся в виде последовательностей символов фиксированной длины, описание товара — в виде строки переменной длины, цена представляет собой десятичные данные (действительное число), а количество — целочисленное значение.
После того как таблица создана, ее можно заполнять данными. Вот инструкция INSERT, предназначенная для ввода данных о новой партии изделия Size 7 Widget (товар ACI-41007) в количестве 250 единиц по цене $225 за штуку.
INSERT INTO PRODUCTS (MFR_ID, PRODUCT_ID, DESCRIPTION, PRICE, QTY_ON_HAND)
VALUES ('ACI', '41007', 'Size 7 Widget', 225.00, 250);
1 row inserted.Наконец, если позднее вы решите, что в базе данных больше не требуется хранить информацию о товарах, то можете удалить таблицу (вместе со всеми данными, которые в ней содержатся) с помощью инструкции DROP TABLE.
DROP TABLE PRODUCTS;
Заключение
В данной статье показаны основные возможности SQL и на примере наиболее распространенных инструкций SQL продемонстрирован синтаксис языка. Итак, подведем итоги.
- SQL используется для выборки информации из базы данных с помощью инструкции
SELECT. Можно извлечь все данные из таблицы или лишь часть из них, отсортировать их и получить итоговые значения, вычисляя суммы и средние величины. - SQL используется для изменения информации в базе данных. Инструкция
INSERTдобавляет данные, инструкцияDELETEудаляет их, а инструкция update обновляет существующие данные. - SQL используется для управления доступом к базе данных. С помощью инструкций SQL предоставляются и отменяются разного рода привилегии для различных пользователей.
- SQL используется для создания и изменения базы данных путем определения структуры новых таблиц и удаления таблиц, ставших ненужными, для чего применяются инструкции
CREATEиDROP.
В следующей статье мы рассмотрим историю и перспективы языка SQL, а также существующие в отросли стандарты.
