Краткий курс SQL

Материал из GedeminWiki
(Различия между версиями)
Перейти к: навигация, поиск
(→‎Представления)
(→‎Представления)
Строка 631: Строка 631:
  
 
   SELECT
 
   SELECT
−
     name, gp, w, d, l, gs, ga, w * 3 + d
+
     name, gp, w, d, l, gs, ga, (w * 3 + d) AS Pt
 
   FROM
 
   FROM
 
     lsn_vtable
 
     lsn_vtable
 
   ORDER BY
 
   ORDER BY
−
     8 DESC
+
     8 DESC, 6 DESC, 7 ASC
 +
 
 +
Теперь турнирная таблица приняла вполне достойный вид:
 +
 
 +
  NAME         GP W D L GS GA    PT
 +
  ==============================================================
 +
  Real Madrid 5 2 1 2 6 5    7
 +
  Milan         4 2 1 1 5 7    7
 +
  Chelsea 4 2 0 2 7 6    6
 +
  Barcelona 5 2 0 3 7 7    6
 +
 
 +
Обратите внимание на секцию ORDER BY. По правилам нашего турнира команды ранжируются сначала по количеству набранных очков, затем по количеству забитых голов и, в случае равенства двух показателей, по количеству пропущеных мячей.
  
 
=== Хранимые процедуры ===
 
=== Хранимые процедуры ===
  
 
[[Category:Учебный курс]]
 
[[Category:Учебный курс]]

Версия 22:54, 5 марта 2007

Информация в реляционной базе данных хранится в таблицах и в связях между таблицами. В архитектуре клиент-сервер, клиент посылает на сервер запрос и получает от него ответ -- результат выполнения запроса. Наибольшее распространение получил язык запросов SQL (акроним от Structured Query Language). Пример SQL команды, которая извлекает список контактов из таблицы GD_CONTACT: 

SELECT * FROM gd_contact

В свою очередь, язык SQL подразделяется на два подмножества: DDL (Data Definition Language) и DML (Data Manipulation Language). DDL определяет набор команд, с помощью которых в базе данных создаются структурные объекты, т.н. метаданные, -- таблицы, домены, внешние ключи, индексы, хранимые процедуры и т.п. На языке DML пишутся запросы на извлечение данных из базы, а так же на вставку, изменение или удаление записей из таблиц.

Содержание

Типы данных

При создании таблицы мы должны задать тип данных для каждой колонки. К нашим услугам набор встроенных типов:

  • INTEGER и SMALLINT -- целочисленные, 4-х и 2-х байтовые соответственно;
  • DOUBLE PRECISION и SINGLE PRECISION -- числа с плавающей точкой, 8 и 4-е байта;
  • NUMERIC и DECIMAL -- числа с фиксированной точкой. При определении типа указывается общее количество хранимых цифр в числе и количество знаков после десятичной точки. Например: NUMERIC(6, 2). Тип данных NUMERIC(18, 0) определяет 64-х битное целое число;
  • DATE, TIME, TIMESTAMP -- дата, время и дата с временем;
  • CHAR, VARCHAR -- строки. В скобках указывается максимальная длина строки, например: VARCHAR(200). Допустимые значения длины -- от 1 до 32000 символов. Тип данных CHAR всегда хранит строки фиксированной длины, при необходимости дополняя их справа пробелами;
  • BLOB -- двоичный объект.

Создание домена

Изучая предметную область разработчик базы данных часто сталкивается с тем, что встроенный тип слишком "широк" для хранения аттрибута рассматриваемой сущности. Например, если необходимо хранить в базе данных возраст человека, как целое число прожитых лет, то, очевидно, что типы данных INTEGER и SMALLINT предоставляют нам слишком широкие диапазоны: от минус двух до плюс двух миллиардов с копейками в первом случае и от минус 32768 до плюс 32767 -- во втором. Конечно, можно понадеяться, что оператор никогда не будет ошибаться и всегда будет вводить в базу корректные величины, но реалии жизни таковы, что если где-то может произойти ошибка -- она, увы, обязательно случится. К счастью, сервер предоставляет нам возможность создать свой тип данных, наложив на него необходимые ограничения. Тип данных в SQL называется доменом и для его создания служит команда CREATE DOMAIN: 

 CREATE DOMAIN dage 
   AS INTEGER 
   DEFAULT 0
   CHECK(VALUE >= 0 AND VALUE <= 120)

Рассмотрим приведенную выше команду. Мы попросили сервер создать домен CREATE DOMAIN с именем dage на основе целочисленного типа AS INTEGER, причем, если пользователь не укажет возраст, то будет использовано значение по умолчанию 0 -- DEFAULT 0, и значение поля должно находиться в пределах от 0 до 120 -- CHECK(VALUE >= 0 AND VALUE <= 120). Мы могли бы указать, что поле будет обязательно для заполнения -- NOT NULL, но в этом нет необходимости, так как NULL значение в любом случае не пройдет проверку CHECK.

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

Создание таблицы

Создадим таблицу для хранения списка футбольных команд. Для каждой команды предусмотрим следующие аттрибуты:

  • Название;
  • Год основания.

Сперва создадим три домена: 

 CREATE DOMAIN lsn_dintkey
   AS INTEGER
   NOT NULL

 CREATE DOMAIN lsn_dname
   AS VARCHAR(60)
   CHARACTER SET WIN1251
   NOT NULL
   COLLATE PXW_CYRL

 CREATE DOMAIN lsn_dfounded
   AS INTEGER
   CHECK(VALUE >= 1800 AND VALUE <=2007)

Мы будем использовать префикс lsn_ для всех объектов, создаваемых в рамках нашего курса, для того, чтобы не забывать, что это всего лишь урок -- lesson. Префиксы позволяют не запутаться в именах объектов на больших базах данных. Второй префикс -- d -- указывает на тип объекта метаданных -- домен. Обратите внимание, как при создании домена lsn_dname мы указали кодовую таблицу WIN1251 (Кирилические символы) и сличение (COLLATION PXW_CYRL). Последнее определяет порядок символов в алфавите конкретного языка и используется при сортировке информации.

Теперь, напишем DDL команду создания таблицы, которую назовем lsn_team: 

 CREATE TABLE lsn_team (
   id      lsn_dintkey,
   name    lsn_dname UNIQUE,
   founded lsn_dfounded,

   PRIMARY KEY(id)
 )

Выше, мы говорили только о двух аттрибутах, а в примере видим еще и третье поле -- id. В чем его предназначение?

Первичный ключ записи

В реляционной базе данных, каждая запись должна иметь первичный ключ -- значение поля или набора полей, которое однозначно идентифицирует эту запись, т.е. является уникальным в пределах данной таблицы. Первичный ключ необходим для соединения нескольких таблиц. Первичные ключи бывают натуральными и суррогатными. Натуральный первичный ключ -- это один или несколько атрибутов сущности, которые являются уникальными по природе задачи. Например, для человека натуральным первичным ключем мог бы являться его персональный номер из паспорта. Для организации -- учетный (идентификационный) номер налогоплательщика (УНН, ИНН). Для автомобиля -- номер кузова и т.д. Удобство натурального первичного ключа в том, что его не надо изобретать. Он уже есть по условиям задачи. На этом, впрочем, удобства заканчиваются и начинаются недостатки. Во-первых, натуральный первичный ключ в один прекрасный момент может стать не уникальным, если изменятся внешние, не зависящие от нас условия. Пример из жизни: некоторое время назад в Беларуси каждый банк или его филиал имел свой уникальный код, пока национальный банк не изменил условия игры так, что головное отделение и все его филиалы стали разделять один и тот же код банка. Теперь уже коомбинация из кода банка и номера филиала стала гарантировать уникальную идентификацию. И нет никаких гарантий, что в будущем не произойдут очередные изменения. Во-вторых, для каждой сущности, натуральный первичный ключ может иметь свой тип данных -- строка, число, дата, время и т.п. Такое разнообразие серьезно усложнит программный код, особенно реализующий интерфейс пользователя. Например, визуальный компонент выпадающий список со значениями из базы данных, придется научить работать с любым типом данных в качестве первичного ключа. И, наконец, натуральные первичные ключи, как правило, занимают на диске больше места. Личный номер человека -- это строка из двадцати символов. УНН организации имеет длину 9 символов и т.д. Суррогатный первичный ключ избавлен вышеперечисленных недостатков. Как правило, это уникальное целое число, которое присваивается с помощью т.н. генератора -- специального счетчика, генерирующего неповторяющуюся последовательность целых чисел.

Первичный ключ становится таковым после наложения ограничения PRIMARY KEY: 

 PRIMARY KEY (id)

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

name   lsn_dname UNIQUE

Автоматическое заполнение первичного ключа

Создадим генератор lsn_gunique: 

 CREATE GENERATOR lsn_gunique

Вновь созданный генератор имеет значение 0, которое можно изменить с помощью команды: 

 SET GENERATOR lsn_gunique TO <Новое значение>

Определить значение генератора можно с помощью SQL запроса: 

 SELECT GEN_ID(lsn_gunique, 0) FROM rdb$database

Из какой таблицы выполнять SELECT в данном случае не принципиально, но таблица RDB$DATABASE удобна тем, что всегда содержит одну и только одну запись.

Но, как значение генератора попадет в поле ID? В этом нам поможет такой объект метаданных, как триггер.

Создание триггера

Триггер -- это небольшая подпрограмма, написанная на специальном языке и выполняемая сервером при происхождении определенного события. Для таблицы существуют следующие события:

  • BEFORE INSERT -- перед вставкой записи;
  • AFTER INSERT -- после вставки записи;
  • BEFORE UPDATE -- перед изменением записи;
  • AFTER UPDATE -- после изменения записи;
  • BEFORE DELETE -- перед удалением записи;
  • AFTER DELETE -- после удаления записи.

Триггер может быть создан для конкретного события или для нескольких событий. В нашем примере необходимо создать триггер на событие BEFORE INSERT для таблицы lsn_team: 

 CREATE TRIGGER lsn_bi_team FOR lsn_team
   BEFORE INSERT
   POSITION 0
 AS BEGIN
   NEW.id = GEN_ID(lsn_gunique, 1);
 END

Позиция триггера (POSITION) определят порядок выполнения однотипных триггеров, если их несколько. Триггера с меньшим значением будут выполняться раньше. В теле триггера мы можем обратиться к значениям полей записи через метапаременные NEW и OLD. Причем, в триггерах на вставку записи доступна только переменная NEW. В триггерах на удаление -- только OLD. И, наконец, в триггерах на изменение записи можно обращаться как к старой, так и к новой версии записи. В триггерах, вызываемых перед осуществлением операции, можно менять значение полей записи, а в пост триггерах -- только читать.

Как нетрудно догадаться, функция GEN_ID принимает на вход два параметра: генератор и шаг приращения и возвращает новое значение генератора.

Вставка записей

Теперь, все готово для того, чтобы занести первую запись в нашу таблицу. Выполним для этого SQL команду INSERT: 

 INSERT INTO lsn_team (name, founded) VALUES ('Real Madrid', 1902)

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

 SELECT * FROM lsn_team

Результат: 

 id      name           founded
 ======================================
 1       Real Madrid    1902

Аналогичным образом добавим еще несколько записей: 

 INSERT INTO lsn_team (name, founded) VALUES ('Barcelona', 1901);
 INSERT INTO lsn_team (name, founded) VALUES ('Chelsea', 1905);
 INSERT INTO lsn_team (name, founded) VALUES ('Milan', 1899);

Проверим, что у нас получилось: 

 SELECT * FROM lsn_team

 id      name           founded
 ======================================
 1       Real Madrid    1902
 2       Barcelona      1901
 3       Chelsea        1905
 4       Milan          1899

Создаем вторую таблицу

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

  • Домашняя команда;
  • Гостевая команда;
  • Дата игры;
  • Количество голов, забитых домашней командой;
  • Количество голов, забитых гостевой командой.

Напишем DDL команду: 

 CREATE TABLE lsn_result (
   homekey     lsn_dintkey,
   awaykey     lsn_dintkey,
   gamedate    DATE DEFAULT CURRENT_DATE NOT NULL,
   homescore   SMALLINT,
   awayscore   SMALLINT,

   PRIMARY KEY (homekey, awaykey, gamedate),
   CHECK (homescore BETWEEN 0 AND 20),
   CHECK (awayscore BETWEEN 0 AND 20)
 )

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

Если при вводе информации пользователь не заполнит поле gamedate, то будет использована текущая дата на сервере. Для полей типа дата и время существуют следующие переменные:

  • CURRENT_DATE -- текущая дата;
  • CURRENT_TIME -- текущее время;
  • CURRENT_TIMESTAMP, NOW -- текущая дата и время.

Создание внешнего ключа

Создавая внешний ключ (FOREIGN KEY) мы говорим серверу, что некоторое поле в некоторой таблице будет содержать значение первичного ключа записи из другой или этой же таблицы. FOREIGN KEY является ограничением, подобным уже известным нам ограничениям PRIMARY KEY и CHECK. Но, как добавить ограничение, если таблица уже существует? Практически для каждого объекта метаданных язык DDL предоставляет нам три команды: CREATE для создания объекта, ALTER для изменения существующего объекта и DROP для удаления объекта. В данном случае, мы воспользуемся командой ALTER TABLE, которая позволяет вносить правки в уже существующую таблицу: 

 ALTER TABLE lsn_result ADD CONSTRAINT lsn_fk_result_homekey
   FOREIGN KEY (homekey) REFERENCES lsn_team (id)
   ON UPDATE CASCADE
   ON DELETE NO ACTION

Разберем данную команду:

  1. ALTER TABLE lsn_result -- Мы указываем серверу, что следует внести изменения в таблицу lsn_result;
  2. ADD CONSTRAINT lsn_fk_result_homekey -- В указанную таблицу добавляем новое ограничение с именем lsn_fk_result_homekey. Мы не говорили об этом ранее, но каждое ограничение может иметь свое имя. В дальнейшем, по имени можно вносить изменения в ограничение или даже вовсе удалить его из базы. Наверное, вы уже догадались, что для этих целей существуют команды: ALTER TABLE ALTER CONSTRAINT ... и ALTER TABLE DROP CONSTRAINT ... Пусть вас не смущает такое длинное имя, присвоенное нами ограничению. Его можно расшифровать следующим образом: lsn_ -- префикс нашей подсистемы, fk_ -- префикс типа объекта (FOREIGN KEY), result -- имя таблицы, homekey -- имя поля, на которое наложено ограничение.
  3. FOREIGN KEY (homekey) -- Ограничение типа внешний ключ на поле homekey;
  4. REFERENCES lsn_team (id) -- Которое содержит ссылку (значение первичного ключа записи) на таблицу lsn_team, на поле id;
  5. ON UPDATE CASCADE -- Если в таблице со списком футбольных команд изменится значение первичного ключа некоторой записи, то автоматически изменения произойдут во всех, ссылающихся на эту команду, записях таблицы lsn_result;
  6. ON DELETE NO ACTION -- Сервер запретит нам удалять футбольную команду для которой в базу данных занесен хотя бы один результат игры. Разумеется, мы могли бы написать правило ON DELETE CASCADE, но в этом случае при удалении команды удалились бы и все результаты игр с ее участием.

Аналогично создадим внешний ключ и для второй ссылки: 

 ALTER TABLE lsn_result ADD CONSTRAINT lsn_fk_result_awaykey
   FOREIGN KEY (awaykey) REFERENCES lsn_team (id)
   ON UPDATE CASCADE
   ON DELETE NO ACTION

Использование исключений

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

 CREATE EXCEPTION lsn_einvalidgamedate 'Некорректная дата игры'

Теперь напишем триггер: 

 CREATE TRIGGER lsn_biu_result FOR lsn_result
   BEFORE INSERT OR UPDATE
   POSITION 100
 AS 
   DECLARE VARIABLE D1 DATE;
   DECLARE VARIABLE D2 DATE;
 BEGIN
   SELECT founded FROM lsn_team WHERE id = NEW.homekey INTO :D1;
   SELECT founded FROM lsn_team WHERE id = NEW.awaykey INTO :D2;

   IF (NEW.gamedate < :D1 OR NEW.gamedate < :D2) THEN
   BEGIN
     EXCEPTION lsn_einvalidgamedate;
   END
 END

Заносим данные

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

Реал-Барселона 2:0 

 INSERT INTO lsn_result VALUES (1, 2, '01.05.2006', 2, 0)

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

Челси-Милан 2:3 

 INSERT INTO lsn_result VALUES (3, 4, '07.05.2006', 2, 3)

Челси-Реал 0:1 

 INSERT INTO lsn_result VALUES (3, 1, '09.05.2006', 0, 1)

и еще несколько результатов для разнообразия: 

 INSERT INTO lsn_result VALUES (3, 2, '19.05.2006', 3, 1)
 INSERT INTO lsn_result VALUES (4, 1, '12.05.2006', 1, 1)
 INSERT INTO lsn_result VALUES (4, 2, '25.05.2006', 0, 4)
 INSERT INTO lsn_result VALUES (2, 4, '09.06.2006', 0, 1)
 INSERT INTO lsn_result VALUES (3, 1, '19.06.2006', 2, 1)
 INSERT INTO lsn_result VALUES (2, 1, '29.06.2006', 1, 2)

Изменение данных

Предположим, что внося информацию о последнем матче мы ошиблись и указали неверный счет. Вместо 1:2 следовало вписать 2:1. Для изменения информации в базе данных используется команда UPDATE: 

 UPDATE lsn_result SET homescore = 2, awayscore = 1 
   WHERE homekey = 2 AND awaykey = 1 AND gamedate = '29.06.2006'

Громоздкость секции WHERE в данном случае объясняется нашим выбором составного натурального первичного ключа из трех полей. Используй мы суррогатный целочисленный ключ, все было бы намного проще. Секция WHERE не обязательна, но, если ее опустить, то команда изменит все записи, что присутствуют в таблице.

Удаление данных

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

 DELETE FROM lsn_result
   WHERE homekey = 2 AND awaykey = 1 AND gamedate = '29.06.2006'

Выполнение команды DELETE без секции WHERE полностью очистит указанную таблицу. 

 DELETE FROM lsn_result

Извлечение данных

Настало время поближе познакомиться с оператором SELECT, пожалуй, самой востребованной командой SQL. Мы уже знаем, что выражение SELECT * FROM <имя таблицы> извлечет все строки и все колонки из указанной таблицы. Если необходимо вывести только некоторые колонки, то вместо звездочки следует перечислить их названия, используя запятую в качестве разделителя. Следующая команда отобразит список команд с годом основания, но без идентификатора: 

 SELECT name, founded FROM lsn_team

Результат: 

 name           founded
 =======================
 Real Madrid    1902
 Barcelona      1901
 Chelsea        1905
 Milan          1899

Секция WHERE

Достаточно редко возникает необходимость в извлечении абсолютно всех записей из таблицы. Гораздо чаще из базы данных извлекаются записи, удовлетворяющие определенным критериям. В этом случае в SELECT запрос добавляется секция WHERE, где указывается накладываемое ограничение. Если ограничений несколько, то они могут быть связаны между собой логическими операторами AND (логическое И -- должно быть выполнено и левое и правое условие) или OR (логическое ИЛИ -- должно быть выполнено хотя бы одно из условий). Условия могут группироваться с помощью скобок. Для инвертирования логического выражения служит унарный оператор NOT (логическое отрицание). Значения колонок можно сравнивать с константами, значениями других колонок, результатами выражений или подзапросов. При этом используются следующие операторы:

  • =, <> или !=, >, <, >=, <= -- равно, не равно, больше, меньше, больше либо равно, меньше либо равно;
  • BETWEEN -- A BETWEEN B AND C эквивалентно выражению (A >= B) AND (A <= C);
  • IN (список констант или подзапрос) -- проверяет, принадлежит ли значение поля указанному множеству;
  • LIKE -- для строковых полей проверяет соответствие поля указанной маске. В тексте маски можно применять символы _ и %. Первый заменяет один произвольный символ в указанной позиции, а второй -- произвольное число символов;
  • CONTAINING, STARTING WITH -- для строковых полей: содержит, начинается с;

Приведем примеры некоторых запросов:

Все команды, основанные после 1900 года: 

 SELECT * FROM lsn_team WHERE founded > 1900

Команды, наименование которых начинается с буквы R: 

 SELECT * FROM lsn_team WHERE name LIKE 'R%'

Наименование команды с идентификатором 1: 

 SELECT name FROM lsn_team WHERE id = 1

Объединение таблиц

Если мы выполним запрос SELECT * FROM lsn_result, то получим малопонятную для человека информацию: 

 homekey  awaykey  gamedate    homescore   awayscore
 ===================================================
 1        2        01.05.2006  2           0
 3        4        07.05.2006  2           3
 3        1        09.05.2006  0           1
 3        2        19.05.2006  3           1
 4        1        12.05.2006  1           1
 4        2        09.06.2006  0           4
 2        4        25.05.2006  0           1
 3        1        19.06.2006  2           1
 2        1        29.06.2006  2           1

Для того, чтобы вместо идентификаторов команд вывести на экран их наименования необходимо объединить данные двух таблиц с помощью оператора JOIN: 

 SELECT 
   th.name AS homename, 
   ta.name AS awayname, 
   r.gamedate, 
   r.homescore, 
   r.awayscore
 FROM 
   lsn_result r 
     JOIN lsn_team th ON th.id = r.homekey
     JOIN lsn_team ta ON ta.id = r.awaykey

Получим следующий результат: 

 homename  awayname    gamedate    homescore   awayscore
 =======================================================
 Real      Barcelona   01.05.2006  2           0 
 Chelsea   Milan       07.05.2006  2           3
 Chelsea   Real        09.05.2006  0           1
 Chelsea   Barcelona   19.05.2006  3           1
 Milan     Real        12.05.2006  1           1
 Milan     Barcelona   09.06.2006  0           4
 Barcelona Milan       25.05.2006  0           1
 Chelsea   Real        19.06.2006  2           1
 Barcelona Real        29.06.2006  2           1

Сортировка информации

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

 SELECT 
   th.name AS homename, 
   ta.name AS awayname, 
   r.gamedate, 
   r.homescore, 
   r.awayscore
 FROM 
   lsn_result r 
     JOIN lsn_team th ON th.id = r.homekey
     JOIN lsn_team ta ON ta.id = r.awaykey
 ORDER BY
   r.gamedate ASC

Получим следующий результат: 

 homename  awayname    gamedate    homescore   awayscore
 =======================================================
 Real      Barcelona   01.05.2006  2           0
 Chelsea   Milan       07.05.2006  2           3
 Chelsea   Real        09.05.2006  0           1
 Milan     Real        12.05.2006  1           1
 Chelsea   Barcelona   19.05.2006  3           1
 Milan     Barcelona   09.06.2006  0           4
 Chelsea   Real        19.06.2006  2           1
 Barcelona Milan       25.05.2006  0           1
 Barcelona Real        29.06.2006  2           1

Вместо имени колонки можно указать ее порядковый номер (начиная с 1). Для сортировки по убыванию следует использовать параметр DESC. Например: 

 ...
 ORDER BY
   3 DESC

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

Агрегатные функции и группировка записей

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

SELECT 

  COUNT(*) AS GameCount,
  AVG(CAST(homescore AS NUMERIC(4,2))) AS HomeAvg,
  AVG(CAST(awayscore AS NUMERIC(4,2))) AS AwayAvg
FROM
  lsn_result

Результат: 

 GameCount   HomeAvg    AwayAvg
 ================================
         9      1.33       1.44

Обратите внимание, что поскольку поля homescore и awayscore у нас целочисленные, перед тем как передать их значения в функцию AVG, мы преобразовываем их к типу NUMERIC(4,2) с помощью функции CAST. В противном случае AVG вернет нам среднее значение, округленное до целого числа, т.е. -- 1 гол за игру.

Сервер поддерживает следующие функции:

  • COUNT -- количество записей в выборке;
  • SUM -- сумма значений (по числовой колонке);
  • AVG -- среднее арифметическое;
  • MIN, MAX -- минимальное и максимальное значение.

Агрегатные функции могут вычисляться как по всей выборке, так и по записям, сгруппированным по указанной колонке (колонкам). Например, посчитаем количество игр, которые каждая команда провела на своем поле: 

 SELECT
   t.name,
   COUNT(*) AS GameCount
 FROM
   lsn_result r JOIN lsn_team t 
     ON t.id = r.homekey
 GROUP BY
   t.name

Результат: 

 Name        GameCount
 ======================
 Barcelona           2
 Chelsea             4
 Milan               2
 Real Madrid         1

Результирующие данные можно отсортировать с помощью секции ORDER BY, а также исключить некоторые группы с помощью условия HAVING. Уберем команды, которые не играли ни разу на домашнем поле и расположим результаты в зависимости от количества сыгранных матчей: 

 SELECT
   t.name,
   COUNT(*) AS GameCount
 FROM
   lsn_result r JOIN lsn_team t 
     ON t.id = r.homekey
 GROUP BY
   t.name
 HAVING
   COUNT(*) > 1
 ORDER BY
   2 DESC

Получим такой результат: 

 Name        GameCount
 ======================
 Chelsea             4
 Barcelona           2
 Milan               2

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

 SELECT
   EXTRACT(YEAR FROM gamedate) || '.' ||
     EXTRACT(MONTH FROM gamedate),
   COUNT(gamedate) AS GameCount
 FROM 
   lsn_result
 GROUP BY
   1

Получим такое распределение: 

 F_1          GameCount
 =======================
 2006.5       6
 2006.6       3

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


Объединение результатов нескольких запросов

Перед нами стоит задача: вывести список дат с указанием победившей команды. Напишем запрос: 

 SELECT 
   r.gamedate,
   t.name
 FROM 
   lsn_result r JOIN lsn_team t
     ON t.id = r.homekey
 WHERE
   r.homescore > r.awayscore

Такой запрос выведет на экран список команд, которые победили на своем поле. Но, ведь не всегда побеждает домашняя команда. Список победивших гостевых команд можно получить таким образом: 

 SELECT 
   r.gamedate,
   t.name
 FROM 
   lsn_result r JOIN lsn_team t
     ON t.id = r.awaykey
 WHERE
   r.homescore < r.awayscore

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

 SELECT 
   r.gamedate,
   t.name
 FROM 
   lsn_result r JOIN lsn_team t
     ON t.id = r.homekey
 WHERE
   r.homescore > r.awayscore

 UNION ALL

 SELECT 
   r.gamedate,
   t.name
 FROM 
   lsn_result r JOIN lsn_team t
     ON t.id = r.awaykey
 WHERE
   r.homescore < r.awayscore

Получим на выходе: 

 GAMEDATE	NAME
 =========================
 01.05.2006	Real Madrid
 19.05.2006	Chelsea
 19.06.2006	Chelsea
 29.06.2006	Barcelona
 09.05.2006	Real Madrid
 25.05.2006	Barcelona
 07.05.2006	Milan
 09.06.2006	Milan

Обратите внимание, что мы написали UNION ALL, чтобы включить в результирующий набор записей все записи из первого запроса и все -- из второго. Чистый UNION возвращает набор уникальных записей, откидывая дубликаты.

Использование подзапросов

Мы имеем список результатов футбольных матчей. Попробуем на основании них вывести турнирную таблицу в том виде, в каком турнирные таблицы обычно печатают в спортивных изданиях. Наша турнирная таблица будет содержать название команды, количество сыгранных игр, количество побед, ничьих и поражений, а также забитые и пропущенные голы. Решить такую задачу можно, составив SQL запрос с подзапросами: 

 SELECT
   t.name,
   (SELECT COUNT(*) FROM lsn_result r WHERE r.homekey = t.id OR r.awaykey = t.id) AS GP,
   (COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
     + COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS W,
   (COALESCE((SELECT SUM(IIF(r.homescore = r.awayscore, 1, 0)) FROM lsn_result r 
     WHERE r.homekey = t.id OR r.awaykey = t.id), 0)) AS D,
   (COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
     + COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS L,
   (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.homekey = t.id), 0)
     + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS GS,
   (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.awaykey = t.id), 0)
     + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.homekey = t.id), 0)) AS GA
 FROM
   lsn_team t
 ORDER BY
   1

Так сложно! -- Скажете вы. Попробуем разобраться с каждой строкой представленного запроса поотдельности. 

 t.name

Тут все понятно. Выводим наименование команды. 

 (SELECT COUNT(*) FROM lsn_result r WHERE r.homekey = t.id OR r.awaykey = t.id) AS GP

Подсчитываем количество записей в таблице результатов игр в которых принимала участие текущая (т.е. соответствующая текущей строке основного запроса) команда. Это и есть подзапрос. Берем его в скобки и даем алиас GP. 

 (COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
   + COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS W

Если приглядеться внимательно, выражение состоит из двух подзапросов. Один определяет количество побед текущей команды в домашних матчах, а второй -- в гостевых. Мы воспользовались функцией IFF, которая принимает на вход три аргумента, первый из которых должен быть булевским варажением. Если Истина, то возвращается второй аргумент, если Ложь, -- третий. Таким образом IIF(r.homescore > r.awayscore, 1, 0) вернет единицу, если домашняя команда одержала победу (забила голов больше) и 0 -- в противном случае. Просумировав все единицы и нули с помощью уже известной нам функции SUM получим количество побед в домашних матчах. Второе слагаемое -- количество побед в гостях. Но, зачем тут еще и функция COALESCE? Попытаемся разобраться. Логика ее действия такова: вернуть первый аргумент, если он не равен NULL или второй -- в противном случае. Осталось понять, что такое NULL значение.

NULL логика

Что вернет функция SUM, если в запросе не будет ни одной записи? Ноль? Пустую строку? А если мы добавим запись в таблицу, но не укажем значение некоторой колонки? Что будет содержать эта колонка? И в первом и во втором случае ответ -- NULL значение. Т.е. специальное значение, которое показывает отсутствие данных. Работая с NULL следует помнить следующее:

  • Появление хотя бы одного NULL в выражении превращает результат всего выражения в NULL;
  • Появление хотя бы одного NULL в булевском выражении превращает его в Ложь. Если A = NULL и B = NULL, то A = B даст Ложь. Если A <> NULL, а B = NULL, то выражение A <> B опять даст Ложь. Для работы с НУЛЛами существует специальный оператор IS NULL, который возвращает истину, если его операнд не содержит никаких данных. Правильное сравнение двух переменных, каждая из которых может быть NULL выглядит следующим образом: (A = B) or (A IS NULL AND B IS NULL).

Теперь все встало на свои места. Если команда не выиграла ни одного матча дома (или в гостях), то функция COALESCE заменит NULL результат функции SUM на 0 и, таким образом, мы получим корректный итог сложения, а не NULL значение.

Остальные подзапросы идентичны разобранному нами выше. Итог наших стараний: 

 NAME	        GP	W	D	L	GS	GA
 ========================================================
 Barcelona	5	2	0	3	7	7
 Chelsea	4	2	0	2	7	6
 Milan	        4	2	1	1	5	7
 Real Madrid	5	2	1	2	6	5

Представления

Пристально взглянув на получившуюся у нас турнирную таблицу мы увидим, что она несколько отличается от тех таблиц, которые печатают в спортивных изданиях. Чего-то не хватает. Отсутствует колонка с очками, набранными командами. К сожалению, мы не можем написать в запросе выражение, сославшись на алиасы колонок, что-нибудь вроде: W * 3 + D (по три очка за победу и по одному за ничью), а переписывать повторно эти жуткие формулы с COALESCE как-то не хочется. Что ж, достроить нашу турнирную таблицу поможет такой объект метаданных, как Представление (VIEW). Представление создается на основе SELECT запроса и ведет себя подобно обычной таблице, т.е. его можно использовать в других SQL запросах. Данные представления не хранятся в базе, а при каждом обращении динамически подготавливаются сервером. Создается представление с помощью команды CREATE VIEW: 

CREATE VIEW lsn_vtable (name, gp, w, d, l, gs, ga) AS  
SELECT
  t.name,
  (SELECT COUNT(*) FROM lsn_result r WHERE r.homekey = t.id OR r.awaykey = t.id),
  (COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
    + COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
  (COALESCE((SELECT SUM(IIF(r.homescore = r.awayscore, 1, 0)) FROM lsn_result r 
    WHERE r.homekey = t.id OR r.awaykey = t.id), 0)),
  (COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
    + COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
  (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.homekey = t.id), 0)
    + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
  (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.awaykey = t.id), 0)
    + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.homekey = t.id), 0))
FROM
  lsn_team t

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

 SELECT
   name, gp, w, d, l, gs, ga, (w * 3 + d) AS Pt
 FROM
   lsn_vtable
 ORDER BY
   8 DESC, 6 DESC, 7 ASC

Теперь турнирная таблица приняла вполне достойный вид: 

 NAME	        GP	W	D	L	GS	GA    PT
 ==============================================================
 Real Madrid	5	2	1	2	6	5     7
 Milan	        4	2	1	1	5	7     7
 Chelsea	4	2	0	2	7	6     6
 Barcelona	5	2	0	3	7	7     6

Обратите внимание на секцию ORDER BY. По правилам нашего турнира команды ранжируются сначала по количеству набранных очков, затем по количеству забитых голов и, в случае равенства двух показателей, по количеству пропущеных мячей.

Хранимые процедуры

Источник — «https://gsbelarus.com/gs/wiki/index.php?title=%D0%9A%D1%80%D0%B0%D1%82%D0%BA%D0%B8%D0%B9_%D0%BA%D1%83%D1%80%D1%81_SQL&oldid=1823»
Персональные инструменты
Пространства имён

Варианты
Действия
Навигация
Инструменты