Справочник по SQL http

Справочник по SQL [ Cкачайте файл, чтобы посмотреть ссылку ]
13 TOC \o "1-3" \u 14Введение 13 PAGEREF _Toc342521800 \h 14315
DDL (Data Definition Language) 13 PAGEREF _Toc342521801 \h 14315
DML (Data Manipulation Language) 13 PAGEREF _Toc342521802 \h 14315
прочие команды 13 PAGEREF _Toc342521803 \h 14415
Структура базы данных 13 PAGEREF _Toc342521804 \h 14415
ключи, ограничения 13 PAGEREF _Toc342521805 \h 14415
индексы 13 PAGEREF _Toc342521806 \h 14515
представления 13 PAGEREF _Toc342521807 \h 14515
процедуры и функции 13 PAGEREF _Toc342521808 \h 14515
триггеры 13 PAGEREF _Toc342521809 \h 14515
схема 13 PAGEREF _Toc342521810 \h 14515
пользователи и сессии 13 PAGEREF _Toc342521811 \h 14615
Инструментарий 13 PAGEREF _Toc342521812 \h 14615
скачать СУБД 13 PAGEREF _Toc342521813 \h 14615
инсталляция 13 PAGEREF _Toc342521814 \h 14615
выполнение запросов 13 PAGEREF _Toc342521815 \h 14615
использование примеров 13 PAGEREF _Toc342521816 \h 14715
Комментарий 13 PAGEREF _Toc342521817 \h 14715
Идентификаторы 13 PAGEREF _Toc342521818 \h 14715
Обзор типов данных 13 PAGEREF _Toc342521819 \h 14815
Интервал времени 13 PAGEREF _Toc342521820 \h 14815
Oracle 13 PAGEREF _Toc342521821 \h 14915
PostgreSQL 13 PAGEREF _Toc342521822 \h 14915
MySQL 13 PAGEREF _Toc342521823 \h 141015
Литералы 13 PAGEREF _Toc342521824 \h 141015
Выражения и операции 13 PAGEREF _Toc342521825 \h 141115
cтроковые операции 13 PAGEREF _Toc342521826 \h 141115
алгебраические операции 13 PAGEREF _Toc342521827 \h 141115
операции отношения 13 PAGEREF _Toc342521828 \h 141215
логические операции и предикаты 13 PAGEREF _Toc342521829 \h 141215
условные выражения 13 PAGEREF _Toc342521830 \h 141215
прочие операции 13 PAGEREF _Toc342521831 \h 141315
Обзор функций 13 PAGEREF _Toc342521832 \h 141315
математические функции 13 PAGEREF _Toc342521833 \h 141315
строковые функции 13 PAGEREF _Toc342521834 \h 141415
работа с датами 13 PAGEREF _Toc342521835 \h 141415
Преобразование типов 13 PAGEREF _Toc342521836 \h 141515
функции Oracle 13 PAGEREF _Toc342521837 \h 141615
функции PostgreSQL 13 PAGEREF _Toc342521838 \h 141715
функции MySQL 13 PAGEREF _Toc342521839 \h 141715
Создание таблицы 13 PAGEREF _Toc342521840 \h 141815
значения полей по умолчанию 13 PAGEREF _Toc342521841 \h 141815
ключи и ограничения 13 PAGEREF _Toc342521842 \h 141815
настройка внешнего ключа 13 PAGEREF _Toc342521843 \h 141915
создание по выборке 13 PAGEREF _Toc342521844 \h 141915
дополнительные параметры таблицы 13 PAGEREF _Toc342521845 \h 141915
Изменение, удаление таблицы 13 PAGEREF _Toc342521846 \h 142015
переименование таблицы или ее столбца 13 PAGEREF _Toc342521847 \h 142015
добавление/удаление столбца 13 PAGEREF _Toc342521848 \h 142015
первичный ключ 13 PAGEREF _Toc342521849 \h 142015
уникальный ключ 13 PAGEREF _Toc342521850 \h 142015
внешний ключ 13 PAGEREF _Toc342521851 \h 142115
безошибочное удаление таблиц 13 PAGEREF _Toc342521852 \h 142115
Добавление записей 13 PAGEREF _Toc342521853 \h 142115
значения по умолчанию 13 PAGEREF _Toc342521854 \h 142215
подзапросы 13 PAGEREF _Toc342521855 \h 142215
вставка по условию 13 PAGEREF _Toc342521856 \h 142215
Обновление записей 13 PAGEREF _Toc342521857 \h 142215
обновление подзапросом 13 PAGEREF _Toc342521858 \h 142315
обновление по данным другой таблицы 13 PAGEREF _Toc342521859 \h 142315
Удаление записей 13 PAGEREF _Toc342521860 \h 142415
Выборка записей 13 PAGEREF _Toc342521861 \h 142415
выборка констант 13 PAGEREF _Toc342521862 \h 142515
выборка по столбцам таблиц 13 PAGEREF _Toc342521863 \h 142515
синонимы (алиасы) 13 PAGEREF _Toc342521864 \h 142515
уникальные записи 13 PAGEREF _Toc342521865 \h 142615
выборка по условию 13 PAGEREF _Toc342521866 \h 142615
выборка по группам 13 PAGEREF _Toc342521867 \h 142715
Соединения 13 PAGEREF _Toc342521868 \h 142715
Агрегатные функции, группировка данных 13 PAGEREF _Toc342521869 \h 142915
Операции над выборками 13 PAGEREF _Toc342521870 \h 143015
Добавление итогов 13 PAGEREF _Toc342521871 \h 143115
Нумерация записей 13 PAGEREF _Toc342521872 \h 143215
Oracle 13 PAGEREF _Toc342521873 \h 143215
MySQL 13 PAGEREF _Toc342521874 \h 143215
PostgreSQL 13 PAGEREF _Toc342521875 \h 143315
Обеспечение уникальности первичного ключа 13 PAGEREF _Toc342521876 \h 143315
столбцы с автоинкрементом 13 PAGEREF _Toc342521877 \h 143415
Индексы 13 PAGEREF _Toc342521878 \h 143415
Представления 13 PAGEREF _Toc342521879 \h 143515
Динамический SQL (DSQL) 13 PAGEREF _Toc342521880 \h 143615
Процедурные операторы 13 PAGEREF _Toc342521881 \h 143615
блоковая структура кода 13 PAGEREF _Toc342521882 \h 143615
присвоение 13 PAGEREF _Toc342521883 \h 143715
условный оператор 13 PAGEREF _Toc342521884 \h 143715
оператор выбора 13 PAGEREF _Toc342521885 \h 143715
метки 13 PAGEREF _Toc342521886 \h 143815
безусловный цикл 13 PAGEREF _Toc342521887 \h 143815
цикл с предусловием 13 PAGEREF _Toc342521888 \h 143815
цикл по счетчику 13 PAGEREF _Toc342521889 \h 143815
цикл по элементам 13 PAGEREF _Toc342521890 \h 143915
операторы выхода/продолжения итерации 13 PAGEREF _Toc342521891 \h 143915
Выборка в переменные 13 PAGEREF _Toc342521892 \h 144015
Хранимые процедуры 13 PAGEREF _Toc342521893 \h 144015
Oracle 13 PAGEREF _Toc342521894 \h 144015
PostgreSQL 13 PAGEREF _Toc342521895 \h 144115
MySQL 13 PAGEREF _Toc342521896 \h 144115
вызов процедур 13 PAGEREF _Toc342521897 \h 144215
Исключения 13 PAGEREF _Toc342521898 \h 144215
Oracle 13 PAGEREF _Toc342521899 \h 144215
PostgreSQL 13 PAGEREF _Toc342521900 \h 144215
MySQL 13 PAGEREF _Toc342521901 \h 144315
Курсоры 13 PAGEREF _Toc342521902 \h 144315
Oracle 13 PAGEREF _Toc342521903 \h 144415
PostgreSQL 13 PAGEREF _Toc342521904 \h 144415
MySQL 13 PAGEREF _Toc342521905 \h 144515
Триггеры 13 PAGEREF _Toc342521906 \h 144515
Oracle 13 PAGEREF _Toc342521907 \h 144615
PostgreSQL 13 PAGEREF _Toc342521908 \h 144615
MySQL 13 PAGEREF _Toc342521909 \h 144715
Числа прописью 13 PAGEREF _Toc342521910 \h 144715
Транзакции, конкурирующие запросы 13 PAGEREF _Toc342521911 \h 145015
Схема 13 PAGEREF _Toc342521912 \h 145115
PostgreSQL 13 PAGEREF _Toc342521913 \h 145215
Oracle 13 PAGEREF _Toc342521914 \h 145215
MySQL 13 PAGEREF _Toc342521915 \h 145215
Управление аккаунтами 13 PAGEREF _Toc342521916 \h 145315
пользователи 13 PAGEREF _Toc342521917 \h 145315
права доступа 13 PAGEREF _Toc342521918 \h 145315
права доступа MySQL 13 PAGEREF _Toc342521919 \h 145415
права доступа PostgreSQL 13 PAGEREF _Toc342521920 \h 145515
права доступа Oracle 13 PAGEREF _Toc342521921 \h 145515
удаление прав доступа 13 PAGEREF _Toc342521922 \h 145615
роли 13 PAGEREF _Toc342521923 \h 145615
роли PostgreSQL 13 PAGEREF _Toc342521924 \h 145615
роли Oracle 13 PAGEREF _Toc342521925 \h 145715
роли MySQL 13 PAGEREF _Toc342521926 \h 145715
Файловый вывод/ввод 13 PAGEREF _Toc342521927 \h 145715
Oracle 13 PAGEREF _Toc342521928 \h 145715
MySQL 13 PAGEREF _Toc342521929 \h 145815
PostgreSQL 13 PAGEREF _Toc342521930 \h 145815
Информация о базе данных 13 PAGEREF _Toc342521931 \h 145915
15
Введение
SQL (Structured Query Language) - язык разработанный специально для обслуживания реляционных баз данных. Главная цель SQL предоставить интерфейс с базой данных, поэтому он может использоваться всеми видами пользователей:
разработчики приложений;
администраторы базы данных;
простые конечные пользователи (редко).
Очень часто SQL любят называть языком четвертого поколения, в котором программист указывает что ему нужно, а не как что-то сделать.
Внутри SQL часто выделяют два языка или две группы команд, которые рассмотрены ниже. Также SQL не отвергает процедурное программирование, позволяя создавать процедуры, функции и триггеры. Но эти возможности как и реализация ООП зависят от конкретной СУБД. В принципе стандарты языка разрабатываются как SQL99 и SQL2003, и возможно в будущем эти различия не станут столь значимыми.
DDL (Data Definition Language)
Команды этой группы управляют структурой базой данных:
создавать, изменять и уничтожать БД и ее объекты (CREATE, ALTER, DROP);
переименовать объекты (RENAME);
стереть все данные в объектах без удаления объектной структуры (TRUNCATE);
добавлять комментарий к объектам БД. (COMMENT).
DML (Data Manipulation Language)
Команды этой группы позволяют запрашивать и обрабатывать данные в существующих объектах:
получить данные из одной и более таблиц (SELECT);
добавлять новые записи в таблицу (INSERT);
изменять данные в таблицах (UPDATE);
изменять или вставлять данные (MERGE);
удалять данные (DELETE).
Команды управления транзакциями позволяют управлять изменениями, сделанные DML командами, другими словами подтвердить или отменить изменение:
применить изменения, т.е. совершить транзакцию (COMMIT);
отменить последние изменения, т.е. сделать откат базы данных с точки сохранения (ROLLBACK);
сохранить текущее состояние базы данных (SAVEPOINT).
прочие команды
Остальные команды, например управляющие привилегиями (GRANT, REVOKE), можно отнести к административным.
Структура базы данных
Большинство современных баз данных (хранилища) являются объектно-реляционными, т.е. множеством объектов, как двухмерные таблицы данных, связанных между собой. Комплекс программ управляющих объектами БД и обеспечивающих ее целостность обычно называют системой управления базой данных, или сокращенно СУБД. Как правило, конечный пользователь БД работает не с СУБД, а со специально разработанными программами с дружественным интерфейсом, скрывающих структуру БД и SQL запросы. Сравнивая с объектно-ориентированным программированием, можно сказать, что каждая таблица задает тип объекта данных, где столбец - свойство объекта. А каждая строка представляет непосредственно экземпляр объекта.
Строку таблицы иногда называют записью таблицы, а ячейку записи полем.
ключи, ограничения
[ Cкачайте файл, чтобы посмотреть картинку ]
Для различных служебных целей в таблице можно создать ключи:
первичный ключ (primary key) - множество столбцов, по которым можно уникально идентифицировать запись в таблице. Поля таких столбцов обязательно должны иметь значение и не повторяться. В нашем примере столбец id во всех таблицах может использоваться для первичного ключа;
уникальный ключ (unical key) - множество столбцов, чье общее значение должно быть уникально. Например, если в уникальный ключ входит два столбца, то две записи могут иметь одинаковое значение в первом столбце только в том случае, если значения во втором столбце у них различны;
внешний ключ (foreign key) - определяет ссылку на запись в другой таблице. Значения полей столбца входящего во внешней ключ одной таблицы идентичны значениям полей столбца другой таблицы (обычно это первичный ключ), на которую ссылается ключ. Пустое значение (null) для внешних ключей разрешается, что означает отсутствие ссылки на данный момент. Использование внешних ключей устраняет избыточность информации в базе данных, позволяя не вводить одни и те же данные несколько раз. В нашем примере столбцы fk_author и fk_janr являются внешними ключами, ссылающиеся на первичные ключи таблиц авторов и жанров соответственно. Таблицы без внешних ключей, как таблицы жанров и авторов называют словарями или справочниками.
Кроме ключей обычно позволяется наложить ограничения (constraints) на значения столбцов таблицы. За правильностью обычно следит СУБД. Например, если попытаться вставить в таблицу две записи с одинаковым значением первичного ключа, то сразу возникнет ошибка. То же самое произойдет, если в качестве значения внешнего ключа указать ссылку на не существующую запись или значение поля не удовлетворяет условию ограничения.
индексы
Если предполагается частый поиск записей или сортировка, то используемые при этом столбцы индексируют. Для этого создается специальный объект БД - индекс, содержащий ссылки (индексы) на все записи таблицы отсортированных по одному и более столбцов. Для внутренней организации индексов могут использоваться различные структуры данных как деревья. Столбцы, входящие в первичный или уникальный ключ, обычно индексируются автоматически.
представления
Конечной целью создания БД являются запросы данных от пользователей. Если запрос используется клиентскими программами, например, для генерации отчета или очень сложен (код запроса занимает страницы), то имеет смысл сохранить его. Для хранения запросов используются специальные объекты - представления или на сленге вьюхи (от view), содержащие в себе код запроса данных на языке SQL. Представления также называют виртуальными таблицами, которые отличаются от обычных таблиц тем, что доступны только для чтения. В результате код новых запросов с подзапросами становится более понятным. А также упрощается работа создателей отчетов, которые могут даже не знать структуру БД, ведь им достаточно знать имя представления.
процедуры и функции
Серьезные СУБД поддерживают в различной степени динамический SQL, с которым связаны дополнительные типы объектов. В первую очередь это хранимые функции и процедуры. Их аргументы могут быть как входными, так и выходными, т.е. в них можно сохранить результат работы процедуры. В Oracle множество функций, процедур, различные пользовательские типы и переменные можно объединить в специальный объект - пакет (package), выполняющий роль модуля или библиотеки обычных языков программирования. Например, пакет DBMS_OUTPUT служит для вывода данных из хранимых процедур. Помимо обычных функций, СУБД поддерживают агрегатные функции, работающие сразу со множеством записей. К таким функциям относятся подсчет записей в выборке, поиск записи с минимальным или максимальным значением в указанном поле.
триггеры
Для обработки событий вставки, изменения и удаления записей в таблице могут быть назначены триггеры - процедурные объекты, автоматически выполняющиеся при наступлении указанного события.
схема
Объект схема (schema) позволяет логически сгруппировать множество других объектов БД для определенной цели. Например, одна схема может служить для управления книжным фондом библиотеки, а другая ее бухгалтерией. Таким образом, несколько клиентских программ могут работать с одной СУБД не мешая друг другу.
пользователи и сессии
Информация о пользователях также хранится в БД. Каждый пользователь характеризуется именем, паролем для входа, правами доступа к объектам БД и числом одновременных подключений (сессий). Для облегчения права доступа могут быть сгруппированы в роли. Ограничение в правах разумно не только с точки зрения безопасности целостности БД (можно случайно уничтожить таблицу с данными) и сокрытия данных (например, адреса клиентов). Клиентские программы могут анализировать доступные права и выстраивать соответствующий пользовательский интерфейс, например, убрать лишние пункты меню.
В каждой СУБД свой перечень используемых типов объектов. Выше обозначены лишь более-менее стандартные объекты.
Инструментарий
скачать СУБД
В первую очередь необходимо достать какую-либо СУБД:
MySQL является самой популярной СУБД с открытым кодом заточенной для web-приложений. Скачать можно c оффициального сайта [ Cкачайте файл, чтобы посмотреть ссылку ]
СУБД Oracle является популярной среди разработчиков производственных приложений. Если осилите регистрацию, то сможете скачать дистрибутив бесплатно на сайте [ Cкачайте файл, чтобы посмотреть ссылку ]
PostgreSQL также является СУБД с открытым кодом. Используется как для web-приложений, так и для производственных приложений. Реализуются практически все возможности SQL стандарта, с другой стороны по синтаксису близка Oracle. Скачать можно на сайте [ Cкачайте файл, чтобы посмотреть ссылку ]
инсталляция
Инсталляция первых двух баз под Windows для учебных целей не сложно, просто следуем шагам инсталлятора. При этом просим создать базу по умолчанию и запоминаем пароли системных пользователей: root для MySQL, system для Oracle.
Для PostgreSQL скачиваем инсталлятор postgresql-8.3.3-1.zip и устанавливаем СУБД, либо в виде программы (далее инструкции для этого типа инсталляции), либо в виде службы. Все утилиты находятся в поддиректории bin. Делаем инициализацию БД, с указанием директории, где будут хранится данные:  initdb -D "D:/PostgreSQL/8.3/data"  В результате будет создана база данных postgres с одной общедоступной схемой. Запустить СУБД можно командой:  pg_ctl start -D "D:/PostgreSQL/8.3/data" -l logfile  Соответственно остановить можно командой:  pg_ctl stop -D "D:/PostgreSQL/8.3/data" -l logfile  По умолчанию создается пользователь с системными правами с именем пользователя на компьютере. Если оно не устраивает, нового пользователя (например, root) при работающей СУБД можно создать утилитой createuser.exe.
выполнение запросов
В поставку Oracle входит программа SQL plus, для входа запрашиваются следующие параметры:
username: system
password: ваш пароль
database: orcl
Под database понимается строка связи, известная как tns имя. По умолчанию создается строка связи orcl. Посмотреть и исправить имена можно в файле ORACLE_HOME/network/admin/tnsnames.ora  Консоль SQL plus не так удобна. Если есть возможность, то лучше купить программу по типу PL/SQL Developer.
В поставку PostgreSQL входит утилита pgAdmin3.exe. Несмотря на название, она позволяет выполнять любые sql запросы. Добавте в ней новый сервер, например, со следующими параметрами:
name: pg (любое имя для сохранения подключения)
host: localhost
user: root
password: вашпароль (можно оставить пустым)
С оффициального сайта MySQL можно скачать визуальные утилиты, среди которых есть MySQLQueryBrowser. Для подключения к ней можно использовать, например, следующие параметры:
server host: localhost
port: 3306 (по умолчанию)
username: root
password: ваш пароль
default schema: test (создается по умолчанию)
использование примеров
В приведенных в этом руководстве примерах используется схема по умолчанию. В MySQL она задается при соединении (схема test создается автоматически), в Oracle для каждого пользователя своя одноименная схема, в PostgreSQL это схема public. В противном случае перед именами объектов (таблиц, хранимых процедур и т.п.) необходимо через точку указывать имя схемы, а пользователь должен иметь соответствующие права. Более подробно можно посмотреть в соответствующих пунктах.

-- используем схему по умолчанию
select * from tblname;

-- используем схему sсhemaname
select * from sсhemaname.tblname;
Комментарий
В SQL коде комментарий оформляется следующим образом.

/* много
строчный комментарий
*/

-- однострочный комментарий
Для MySQL после начала однострочного комментария пробел обязателен.
Идентификаторы
Идентификатор - последовательность символов и цифр, служащая для именавания различных объектов как таблицы, имена столбцов и т.д.
Допустимыми символами являются: 'A'-'Z', 'a'-'z','_', '0'-'9'. Цифры не могут быть первым символом. Регистр букв обычно значения не имеет.
По станадарту строка, заключенная в двойные кавычки, также является идентификатором. Причем в строку могут входить прочие символы, например, буквы русского алфавита и пробелы. В MysQL дополнительно поддерживаются идентификаторы в обратных одинарных кавычках `` (не апостроф, а кавычка где ~ или русская Ё, перед клавишей 1). А двойные кавычки могут использоваться только в режиме ANSI_QUOTES.
Обзор типов данных
Ниже приведен список основных типов данных для столбцов таблиц:
BOOLEAN - логический тип;
TINYINT - целочисленный тип размером в один байт;
SMALLINT - целочисленный тип размером в два байта;
INT,INTEGER - целочисленный тип размером в четыре байта;
BIGINT - целочисленный тип размером в восемь байт;
DEC, NUMERIC, DECIMAL - тип для вещественных чисел с фиксированной точкой;
FLOAT - тип для вещественных чисел с плавающей точкой малой точности;
DOUBLE PRECISION - тип для вещественных чисел с плавающей точкой двойной точности;
CHAR - строка фиксированного размера;
NCHAR - строка фиксированного размера с поддержкой национальных символов;
VARCHAR - строка переменного размера;
NVARCHAR - строка переменного размера с поддержкой национальных символов;
DATE - дата;
TIME - время;
TIMESTAMP - временная отметка;
INTERVAL - интервал времени;
BINARY LARGE OBJECT - двоичные данные больших размеров. В большинстве СУБД называется BLOB;
CHARACTER LARGE OBJECT - символьные данные больших размеров. Может называться как CLOB или TEXT.
В Oracle все числовые типы реализованы через собственный встроенный тип NUMBER. В некоторых базах как MySQL DOUBLE PRECISION можно заменить на просто DOUBLE. При определении вещественных типов возможны два параметра, определяющих общее число знаков в числе и число знаков после запятой, например DOUBLE PRECISION(10,2).
Строки также имеют параметр определяющие размер строки или максимальный размер строки.
Вместо ANSII типов VARCHAR и NVARCHAR в Oracle рекомендуется использовать типы VARCHAR2 и NVARCHAR2. Для символьных типов приставка N в Oracle подразумевает использование Unicode символов. MS Access всегда использует только Unicode символы (т.е. VARCHAR и NVARCHAR являются синонимами). В MySQL тип символов задается настройкой базы, или явным указанием используемой кодировки и таблицы сравнения строк без учета регистра.
В некоторых базах нет строгого разграничения между временными типами, и все имена типов являются синонимами.
Интервал времени
Синтаксис и реализация интервалов отличается на разных СУБД.
Oracle
Данный тип разделен на два: интервал по годам и интервал по дням. В первом случае можно указать точность интервала только до месяца (по умолчанию год). Во втором случае до различных долей секунды. Кроме этого указывается точность значения временных промежутков в количестве цифр. Если точность не совпадает, то выводится сообщение об ошибке.

-- годовые интервалы
-- интервал в 99 лет
INTERVAL '99' YEAR

-- интервал в 999 лет в скобках
-- указана точность для годов
INTERVAL '999' YEAR(3)

-- интервал в 999 лет и 3 месяца в скобках
-- после TO указывается точность самого промежутка
-- месяцы указываются через -
INTERVAL '999-3' YEAR(3) TO MONTH

-- интервал в 99 лет и два месяца
-- это отрицательный интервал
INTERVAL '-99-2' YEAR TO MONTH

-- дневные интервалы
-- интервал в 200 дней
INTERVAL '200' DAY(3)

-- интервал в 200 дней и 6 часов
INTERVAL '200 6' DAY(3) TO HOUR

-- интервал в 200 дней, 6 часов
-- и 10 минут
INTERVAL '200 6:10' DAY(3) TO MINUTE

-- интервал в 200 дней, 6 часов,
-- 10 минут и 7 секунд
INTERVAL '200 6:10:7' DAY(3) TO SECOND

-- интервал в 200 дней, 6 часов,
-- 10 минут, 7 секунд и 333 милисекунды
INTERVAL '200 6:10:7.333' DAY(3) TO SECOND(3)

-- пример интервала в запросе
-- выборка интервала в два дня
select INTERVAL '2' day from dual;
PostgreSQL
интервалы указываются в виде строки, в которой перечисляются значение и тип промежутка:
microsecond - микросекунды;
millisecond - милисекунды;
second - секунды;
minute - минуты;
hour - часы;
day - дни;
week - недели;
month - месяцы;
year - года;
century - век;
millennium - тысячелетие.
Слова можно употреблять и во множественном числе. Если интервал начинается с дней, то можно использовать короткий формат строки как в Oracle для дневных интервалов.

-- интервал в три года
INTERVAL '3 year'

-- интервал в три года и три дня
INTERVAL '3 years 3 day'

-- интервал в три года, три дня
-- и 3 минуты
INTERVAL '3 year 3 day 3 minute'

-- интервал в 3 дня, 7 часов,
-- 7 минут и 5 секунд
INTERVAL '3 7:07:05'

-- пример интервала в запросе
-- выборка интервала в два дня
select INTERVAL '2 day';
MySQL
Только сложные интервалы, состоящие из более одного типа промежутков, указываются в строке. Для этих целей введены дополнительные по сравнению с PostgreSQL имена для промежутков:
second_microsecond - секунды и микросекунды, формат строки 's.m';
minute_microsecond - минуты и микросекунды, формат строки 'm.m';
minute_second - минуты и секунды, формат строки 'm:s';
hour_microsecond - часы и микросекунды, формат строки 'h.m';
hour_second - часы, минуты и секунды, формат строки 'h:m:s';
hour_minute - часы и минуты, формат строки 'h:m';
day_microsecond - день и микросекунды, формат строки 'd.m';
day_second - дни, часы, минуты и секунды, формат строки 'd h:m:s';
day_minute - дни, часы и минуты, формат строки 'd h:m';
day_hour - дни и часы, формат строки 'd h';
year_month - года и месяцы, формат строки 'y-m'.
MySQL интервалы используются в выражениях с временными типами данных, использовать их в качестве конечного типа для столбцов запрещено.

-- интервал в три года
INTERVAL 3 year

-- интервал в 3 дня, 7 часов,
-- 7 минут и 5 секунд
INTERVAL '3 7:07:05' day_second

-- пример интервала в запросе
-- выборка интервала в два дня
-- ошибка, столбец не может быть типа INTERVAL
select INTERVAL '2 day';

-- правильно, к дате прибавляем интервал
select date '2009-01-01'+INTERVAL '3 7:07:05' day_second
Литералы
Литералы служат для непосредственного представления данных, ниже приведен список стандартных литерал:
целочисленные - 0, -34, 45;
вещественные - 0.0, -3.14, 3.23e-23;
строковые - 'текст', n'текст', 'don''t!';
дата - DATE '2008-01-10';
время - TIME '15:12:56';
временная отметка - TIMESTAMP '2008-02-14 13:32:02';
логический тип - true, false;
пустое значение - null.
Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.
В MySQL для временных литерал строка должна быть заключена в скобки: DATE ('2008-01-10').
Формат даты по умолчанию обычно определяется настройкой БД. Продвинутые СУБД могут автоматически определять некоторые форматы (DATE ('2008.01.10')) или как в Oracle имеют функцию преобразования (to_date('01.02.2003','dd.mm.yyyy')). Для упрощения во многих СУБД там, где подразумевается дата, перед строкой необязательно ставить имя типа.
Выражения и операции
Для построения выражений SQL включает стандартные операции, ряд дополнительных предикатов (булевских конструкций) и функций. В MySQL для встроенных функций между именем и открывающей скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной функции в БД. Oracle не поддерживает логические выражения в перечислении select.
cтроковые операции
|| - соединение строк, в некоторых СУБД операнды автоматически преобразуются в строковый тип. В MS Access используется &.

select 'hello'||' world'
select 'hello'||' world' from dual -- для Oracle
алгебраические операции
+ - сложение;
- - вычитание;
* - умножение;
/ - деление;
mod - остаток от деления. Oracle: mod(6,2). MySql: 6 mod 2.
Операции + и - также используются при работе со временем и интервалами. В Oracle и PostgreSQL возможна разница между датами. Результат возвращается в виде интервала в днях. Ниже приведен пример добавления к дате интервала.

-- для PostgreSQL
select date '2009-01-01'+INTERVAL '3 7:07:05'

-- для Oracle
select date '2009-01-01' +
INTERVAL '3 7:07:05' day to second
from dual;

-- для MySQL
select date '2009-01-01'+
INTERVAL '3 7:07:05' day_second
Ко времени можно прибавлять целое число, но результат зависит от конкретной СУБД.

-- для Oracle, 1 интерпретируется как день
select date '2009-01-01'+1 from dual

-- для PostgreSQL, 1 интерпретируется как день
select date '2009-01-01'+1

-- для MySQL, 1 интерпретируется как год
select date '2009-01-01'+1
операции отношения
< - меньше;
<= - меньше либо равно;
> - больше;
>= - больше либо равно;
= - равно;
<>,!= - не равно;
логические операции и предикаты
and - логическое и;
or - логическое или;
nor - отрицание;
between - определяет, находится ли значение в указанном диапазоне:  выражение BETWEEN значение_с AND значение_по
exists - определяет есть ли в указанной выборке хотя бы одна запись  EXISTS (select ...)  Для скорости в подзапросе обычно выбирают константу, а не поля записей, так как в данном случае нам важны не данные, а факт существования записей;
in - определяет, входит ли указанное значение в указанное множество:  выражение IN (значение1,...,значениеn)  В качестве множества значений может служить корректная выборка  выражение IN (select ...)
is null - является ли указанное выражение NULL значением: выражение IS NULL
like - определяет, удовлетворяет ли строка указанному шаблону: строковое_выражение LIKE шаблон [ESCAPE еск_символ] Знак % в шаблоне интерпретируется как строка любой длины, знак _ как любой символ. В конструкции ESCAPE еск_символ указывается символ ESCAPE последовательности, который отменит обычную интерпретацию символов '_' и '%'. В последних стандартах включены предикаты SIMILAR и LIKE_REGEX расширяющие возможности LIKE, используя в качестве шаблона регулярные выражения.
условные выражения
case - условный оператор, имеющий следующий синтаксис:
CASE WHEN условие THEN результат
[WHEN условиеn THEN результатn]
[ELSE результат_по_умолчанию]
END
decode(expr,s1,r1[,sn,rn][,defr]) - сравнивает выражение expr с каждым выражением si из списка. Если выражения равны то возвращается значение равное ri. Если ни одно из выражений в списке не равно expr, то возвращается defr или NULL, если defr не было указано. Эта функция доступна только в Oracle и в большинстве случае заменяет оператор CASE;
coalesce(arg1,...,argn) - возвращает первый аргумент в списке не равный null. Для двух аргументов в Oracle можно воспользоваться функцией nvl;
greatest(arg1,...,argn) - возвращает наибольший аргумент в списке;
least(arg1,...,argn) - возвращает наименьший аргумент в списке;
nullif((arg1,arg2) - возвращает null если два аргумента равны, иначе первый аргумент.
Ниже приведен пример использования выражения в запросе выбора данных.

-- для MySQL, PostresSQL
-- в скобках наше выражение
select ('молоко' LIKE '%оло%') as result;

-- эмулировать логический тип в запросах данных
-- для Oracle можно с помощью CASE
select case
-- в скобках наше условие
when (2 BETWEEN 0 AND 3 )
then 1
else 0
end as result from dual;
прочие операции
В каждой СУБД свой набор операций, выше были приведены наиболее употребительные. Например, в PosgreSQL можно использовать и такие операции:
^ - возведение в степень;
|/ - квадратный корень;
||/ - кубический корень;
! - постфиксный факториал;
!! - префиксный факториал;
@ - абсолютное значение.
Обзор функций
В арсенале каждой СУБД обязательно имеется набор встроенных функций для обработки стандартных типов данных. В MySQL для встроенных функций между именем и открывающей скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной функции в БД. В некоторых СУБД, как Oracle, если функция не имеет аргументов, то скобки можно опустить.
математические функции
abs(x) - абсолютное значение;
ceil(x) - наименьшее целое, которое не меньше аргумента;
exp(x) - экспонента;
floor(x) - наибольшее целое, которое не больше аргумента;
ln(x) - натуральный логарифм;
power(x, y) - возводит x в степень y;
round(x [,y]) - округление x до y разрядов справа от десятичной точки. По умолчанию y равно 0;
sign(x) - возвращает -1 для отрицательных значений x и 1 для положительных;
sqrt(x) - квадратный корень;
trunc(x [,y]) - усекает x до у десятичных разрядов. Если у равно 0 (значение по умолчанию), то х усекается до целого числа. Если у меньше 0, от отбрасываются цифры слева от десятичной точки.
Тригонометрические функции работают с радианами:
acos(x) - арккосинус;
asin(x) - арксинус;
atan(x) - арктангенс;
cos(x) - косинус;
sin(x) - синус;
tan(x) - тангенс.
строковые функции
ascii(string) - возвращает код первого символа, эта функция обратна функции CHR;
chr(x) - возвращает символ с номером х, в MySQL это функция char;
length(string) - возвращает длину строки;
lower(string) - понижает регистр букв;
upper(string) - повышает регистр букв;
ltrim(string1[, string2]) - удаляет слева из первой строки все символы встречающиеся во второй строке. Если вторая строка отсутствует, то удаляются пробелы. В MySQL второй аргумент не поддерживается;
rtrim(string1[, string2]) - аналогична функции ltrim, только удаление происходит справа;
trim(string) - удаляет пробелы с обоих концов строки;
lpad(string1, n[, string2]) - дополняет первую строку слева n символами из второй строки, при необходимости вторая строка дублируется. Если string2 не указана, то используется пробел;
rpad(string1, n[, string2]) - аналогична функции lpad, только присоединение происходит справа;
replace(string1, c1, c2) - заменяет все вхождения символа/подстроки c1 на c2. Для простого удаления всех вхождений c1, в качестве третьего аргумента надо указать пустую строку (''). В Oracle третий аргумент не обязателен, и по умолчанию равен пустой строке;
instr(string1, string2[, a][, b]) - возвращает b вхождение строки string2 в строке string1 начиная с позиции a. Если a отрицательно, то поиск происходит справа. По умолчанию a и b присваиваются значение 1. В MySQL последние два аргумента не поддерживаются. В PostgreSQL данной функции нет, однако ее реализация дана в документации, как раз для совместимости с Oracle;
substr(string, pos, len) - возвращает подстрку с позиции pos и длины len.
работа с датами
В рассматриваемых СУБД для обработки времени мало общего. Самый минимум у Oraсle:
current_date - глобальная переменная содержащая текущую дату. Можно использовать и в других СУБД;
trunc(d,s) - приводит дату к началу указанной временной отметки, например к началу месяца. В PostgreSQL есть аналогичная функция date_trunc(s,d). В MySQL для этих целей может использоваться функция date_format(d,s), но она возвращает результат в виде строки;
add_months(d,n) - добавляет к дате указанное число месяцев;
last_day(d) - последний день месяца, содержащегося в аргументе;
months_between(d1,d2) - возвращает число месяцев между датами.
Ниже приведены допустимые форматы в строковом параметре s для функций trunc и date_trunc соответственно:
квартал - q, quarter;
год - yyyy, year;
месяц - mm, month;
неделя - ww, week;
день - dd, day;
час - hh, hour;
минута - mi, minute.
Такие функции как last_day в других СУБД реализуются с помощью арифметики времени и преобразования типов. Так что при желании можно написать соответствующую функцию. Ниже приведена выборка последнего дня указанной даты.

-- для PostgreSQL
select cast( (date_trunc('month', date '2009-01-15')
+ interval '1 month') as date) - 1 as d

-- для MySQL
select date ( date_format('2009-01-15','%Y-%m-01'))
+ interval 1 month
- interval 1 day as d
Преобразование типов
Множество типов разрешенные для преобразования в констркуции CAST AS определяется реализацией СУБД. Так в MySQL может преобразовать только следующие типы: binary[(n)], char[(n)], date, datetime, decimal[(m[,d])], signed [integer], time, unsigned [integer]. А в Oracle, кроме преобразования встроенных типов, можно преобразовывать выборки со множеством записей в массивы.

-- MySQL
select CAST('5.3' AS decimal)+2
select CAST( (select '5.3') AS decimal(6,2))+2.0

-- Oracle
select CAST('5,22' AS double precision) +2 from dual

-- PostgreSQL
select CAST('5.22' AS double precision) +2
В PostgreSQL более расширенные возможности по преобразованию. Во-первых, можно добавить собственное преобразование для встроенных и пользовательских типов. Во-вторых, есть собственный более удобный оператор преобразования типов ::.

select cast('tru' as boolean);
select cast('fa' as boolean);
-- ошибка, строка не похожа на 'true', 'false'
-- и не равна строкам '1' или '0'
select cast('ok' as boolean)

-- создадим функцию преобразования
-- просто указываем какие строки
-- понимать как true значение,
-- все остальные строки будут false значением
CREATE OR REPLACE FUNCTION to_bool(varchar)
RETURNS boolean
AS $$
SELECT $1 = 'true' or $1 = 'tru' or
$1 = 'tr' or $1 = 't'
or $1 = '1' or $1='ok'$$
LANGUAGE SQL;

-- создаем преобразование типа varchar в boolean
CREATE CAST (varchar AS boolean)
WITH FUNCTION to_bool(varchar)
AS ASSIGNMENT;

-- теперь можно так
select cast ( 'ok'::varchar as boolean);
select cast( varchar 'ok' as boolean);
select 'ok'::varchar::boolean;

-- уничтожение преобразования
DROP CAST IF EXISTS (varchar AS boolean) ;

В большинстве случае необходимо преобразование в строку либо из строки. Для этого случая СУБД предоставляют дополнительные функции.
функции Oracle
to_char(date [,format[,nlsparams]]) - дату в строку;
to_char(number [,format[,nlsparams]]) - число в строку;
to_date(string[,format[,nlsparams]]) - строку в дату;
to_number( string [ ,format[, nlsparams] ]) - строку в число;
to_timestamp(string, format) - строку во время.
В этих функциях format описание формата даты или числа, а nlsparams - национальные параметры. Формат строки для даты задается следующими элементами:
"" - вставляет указанный в ковычках текст;
AD, A.D. - вставляет AD с точками или без точек;
ВС, B.C. - вставляет ВС с точками или без точек;
СС, SCC - вставляет век, SCC возвращает даты ВС как отрицательные числа;
D - вставляет день недели;
DAY - вставляет имя дня, дополненное пробелами до длины в девять символов;
DD - вставляет день месяца;
DDD - вставляет день года;
DY1 - вставляет сокращенное название дня;
FF2 - вставляет доли секунд вне зависимости от системы счисления;
НН, НН12 - вставляет час дня (от 1 до 12);
НН24 - вставляет час дня (от 0 до 23);
MI - вставляет минуты;
MM - вставляет номер месяца;
MOMn - вставляет сокращенное название месяца;
MONTHn - вставляет название месяца, дополненное пробелами до девяти символов;
RM - вставляет месяц римскими цифрами;
RR - вставляет две последние цифры года;
RRRR - вставляет весь год;
SS - вставляет секунды;
SSSSS - вставляет число секунд с полуночи;
WW - вставляет номер недели года (неделя - 7 дней от первого числа, а не от понедельника до воскресенья);
W - вставляет номер недели месяца;
Y.YYY - вставляет год с запятой в указанной позиции;
YEAR, SYEAR - вставляет год, SYEAR возвращает даты ВС как отрицательные числа;
YYYY, SYYYY - вставляет год из четырех цифр, SYYYY возвращает даты ВС как отрицательные числа;
YYY, YY, Y - вставляет соответствующее число последних цифр года.
Формат числовой строки задается следующими элементами:
$ - вставляет знак доллара перед числом;
В - вставляет пробелы для целой части десятичного числа, если она равна нулю;
MI - вставляет знак минус в конце (например, '999.999mi');
S - вставляет знак числа в начале или в конце (например,'s9999' или '9999s');
PR - записывает отрицательное число в уголвых скобках (например,'999.999pr');
D - вставляет разделитель десятичной точки в указанной позиции (например, '999D999');
G - вставляет групповой разделитель в указанной позиции (например,'9G999G999'). При этом дробная часть числа отбрасывается;
С - вставляет ISO идентификатор валюты в начале или в конце числа (например, 'с9999' или '9999с');
L - вставляет локальный символ валюты в в начале или в конце числа (например, 'l9999' или '9999l');
, - вставляет запятую в указанной позиции вне зависимости от группового разделителя;
. - вставляет десятичную точку в указанной позиции вне зависимости от разделителя десятичной точки;
V - возвращает значение, умноженное на 10^n, где n равно числу девяток после V. В случае необходимости это значение округляется;
ЕЕЕЕ - 9.99ЕЕЕЕ возвращает значение в экспоненциальной форме записи;
RM - RM значение будет записано римскими цифрами в верхнем регистре;
rm - rm значение будет записано римскими цифрами в нижнем регистре;
0 - вставляет нули, вместо пробелов в начале строки или в конце, например, 9990 вставляет нули, вместо пробелов в конце строки;
9 - каждая 9 определяет значащую цифру.

select to_char(sysdate,
'"системное время: "DD-MON-YY hh24.mi:ss CC "век"')
as c
from dual;

select to_date('01012009','ddmmyyyy') as c
from dual;

select to_char(-10000,'99G999D99L',
'NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''baks'' ') as c
from dual;

select to_char(9.12345,'099.99') as c
from dual
функции PostgreSQL
to_char(timestamp, format) - время в строку;
to_char(interval, format) - интервал времени в строку;
to_char(number, format) - число в строку;
to_date(str, format) - строку в дату;
to_number(str, format) - строку в число;
to_timestamp(str, format) - строку во время.
Основные элементы форматирования совпадают с Oracle.
функции MySQL
date_format(date,format) - дату в строку;
time_format(time,format) - время в строку;
format(number,precision) - число в cтроку типа '#,###,###.##', где число знаков определяется вторым аргументом.
Ниже приведен список основных элементов форматирования для даты и времени:
%c - месяц числом;
%d - день месяца;
%H - часы (от 0 до 24);
%h - часы (1 до 12);
%i - минуты;
%s - секунды;
%T - время в формате "hh:mm:ss";
%Y - год, четыре цифры;
%y - год, две цифры.
< /ul>

select date_format(date '2010-02-01',
'%c месяца %d дней %Y год') as c
Создание таблицы
Для создания простой таблицы используется команда CREATE TABLE. Ниже приведен пример для Oracle, для других тип number надо заменить на integer или double.

CREATE TABLE tblname (
id number,
num number, -- число
-- фиксированная строка
fstr char(45),
-- строка с переменным размером, но не больше 45
str varchar(45),
birth date -- дата
);
значения полей по умолчанию
После типа столбца можно задать дополнительные свойства столбца как значение по умолчанию и указать, возможно ли значение null. Для одних БД порядок принципиален (Oracle) для других нет (MySQL).

CREATE TABLE tblname (
id number,
num number DEFAULT 3.14 NOT NULL,
fstr char(45),
str varchar(45),
birth date
);
ключи и ограничения
Обычно после перечисления столбцов в определении таблицы указывают дополнительные ограничения, например, ключи. Естественно, таблица, на которую ссылается внешний ключ должна существовать, а тип внешнего ключа должен совпадать с типом столбца на который ссылается. Например, для MySQL, если столбец внешнего ключа был определен как fk_key1 INTEGER, а первичный ключ таблицы tb как id INTEGER UNSIGNED, то таблица просто не будет создана. В некоторых базах ограничения можно задавать как свойство столбца, но на мой взгляд слишком длинно получается.

-- правильно для большинства реализаций SQL
CREATE TABLE tblname (
id integer,
fstr char(45),
str varchar(45),
-- в MySQL precision можно опустить
num double precision,
birth date,
fk_key1 integer,

-- столбец id используем в качестве первичного ключа
CONSTRAINT pk_tblname PRIMARY KEY (id),

-- уникальный ключ, т.е. значения в столбце fstr
-- будут уникальны
CONSTRAINT uk_tblname1 UNIQUE (fstr),

-- ограничение на значение
CONSTRAINT tblname_numcheck CHECK (num<100.34),

-- внешний ключ, в скобка столбец нашей таблицы
-- tb таблица, id столбец на которые ссылается
-- внешний ключ
CONSTRAINT fk_tblname FOREIGN KEY (fk_key1)
REFERENCES tb(id)
);

-- именовать ограничения не обязательно
-- в этом случае СУБД сгенерирует имена сама
CREATE TABLE tblname (
id integer,
fstr char(45),
str varchar(45),
num double,
birth date,
fk_key1 integer,

PRIMARY KEY (id),
UNIQUE (fstr),
CHECK (num<100.34),
FOREIGN KEY (fk_key1)
REFERENCES tb(id)
);
настройка внешнего ключа
Что произойдет, если послана команда на удаление записи в родительской таблице на которую ссылается внешний ключ? По умолчанию СУБД не позволит удалить такую запись и выведет сообщение об ошибке. Однако есть еще как минимум два возможных варианта, которые задаются при определении внешнего ключа. Первый, это установить значение внешнего ключа в null, естественно при определении столбца не должно быть указано NOT NULL. А второй вариант, удалить все записи в дочерней таблице, которые ссылаются на удаляемую запись в родительской таблице. Такое удаление называется каскадным.

-- определение ключа, с установкой в null
-- при удалении записи на которую происходит ссылка
FOREIGN KEY (fk_key1)
REFERENCES tb(id)
ON DELETE SET NULL

-- определение ключа с каскадным удалением
FOREIGN KEY (fk_key1)
REFERENCES tb(id)
ON DELETE CASCADE
создание по выборке
Существует еще один способ создания таблицы - по выбранным данным. В этом случае новая таблица имеет не только те же столбцы, что и столбцы выборки, но и те же данные. Таким образом, легко сохранить большой объем данных в отдельной таблице.

-- сохраняем данные tblname в tblname1
create table tblname1 as select * from tblname
дополнительные параметры таблицы
В зависимости от СУБД после закрывающей скобки могут идти дополнительные опции, например управление табличным пространством. Если они не указаны, то используются значения по умолчанию.

-- пример для Oracle
CREATE TABLE tblname (
...
)
PCTFREE 10
PCTUSED 40
TABLESPACE users
STORAGE
(INITIAL 50K
NEXT 50K
MAXEXTENTS 10
PCTINCREASE 25
);

-- пример для MySQL
CREATE TABLE tblname (
...
)
ENGINE = InnoDB;
Изменение, удаление таблицы
Команда ALTER позволяет изменить объект, команда DROP удалить объект из БД.
переименование таблицы или ее столбца

-- переименование таблицы
ALTER TABLE tblname RENAME TO tblname3;

-- переименование столбца oldname в newname
ALTER TABLE tblname RENAME oldname TO newname;
добавление/удаление столбца
Во время развития БД иногда требуется добавить новый или удалить старый столбец в той или иной таблице. В приведенном ниже примере по стандарту ключевое слово COLUMN не обязательно, а в Oracle при добавлении столбца оно запрещено вовсе. В MySQL при добавлении столбца можно указать местоположение ключевыми словами FIRST или AFTER имя_столбца.

-- добавление
ALTER TABLE tblname ADD COLUMN num1 integer;

-- удаление
ALTER TABLE tblname DROP COLUMN num1;
первичный ключ
Ниже приведен пример добавления и удаления первичного ключа таблицы. MySQL не поддерживает стандартного необязательного параметра CASCADE, определяющего поведение при удалении ограничения.

-- добавление
ALTER TABLE tblname
ADD CONSTRAINT pk_tblname PRIMARY KEY (id);
-- удаление
ALTER TABLE tblname
DROP PRIMARY KEY;

-- с удалением всех записей в других таблицах,
-- ссылающихся на этот ключ
ALTER TABLE tblname DROP PRIMARY KEY CASCADE;
уникальный ключ

-- добавление
ALTER TABLE tblname
ADD CONSTRAINT uk_tblname2 UNIQUE (birth)

--удаление
ALTER TABLE tblname DROP CONSTRAINT uk_tblname2;

-- удаление для MySQL
ALTER TABLE tblname DROP KEY uk_tblname2;
внешний ключ
MySQL автоматически создает одноименный индекс для внешнего ключа, но при удалении ключа индекс не удаляет.

-- добавление
ALTER TABLE tblname
ADD CONSTRAINT fk_tblname2 FOREIGN KEY(num1)
REFERENCES tb (id);

-- удаление
ALTER TABLE tblname DROP CONSTRAINT fk_tblname2;

-- удаление для MySQL
ALTER TABLE tblname DROP FOREIGN KEY fk_tblname2;
ALTER TABLE tblname DROP INDEX fk_tblname2;
Ниже приведен пример добавление удаление ограничения на значение.
ALTER TABLE tblname -- добавление
ADD CONSTRAINT chk_tblname1 CHECK (num>10);

-- удаление, не для MySQL
ALTER TABLE tblname
DROP CONSTRAINT chk_tblname1;
Для уничтожения таблицы служит команда DROP TABLE.
-- если уверены в уничтожении таблицы
DROP TABLE tblname;
безошибочное удаление таблиц
В приведенном выше примере, если уничтожаемая таблица не существует, то появится сообщение об ошибке. Некоторые СУБД поддерживают расширенный синтаксис, позволяющий избежать данную ошибку. Обычно это используется в скриптах, где перед созданием новой таблицы на всякий случай уничтожают таблицу с таким же именем.

-- если таблица есть, она уничтожится,
-- иначе ничего не произойдет
DROP TABLE IF EXISTS tblname;
Добавление записей
Для добавления записей в таблицу используется команда INSERT INTO. Пусть существует следующая таблица.

CREATE TABLE tblname (
id integer default 0,
num double precision default 3.14,
CONSTRAINT pk_tblname PRIMARY KEY (id)
);
Ниже приведен запрос на добавление записи в эту таблицу с использованием полного синтаксиса. Для применения внесенных изменений дополнительно необходимо послать команду commit. Возможно, в используемой вами программе есть опция включения/выключения автоматической посылки этой команды, или запуск этой команды выведено на видное место в пользовательском интерфейсе. В последующих примерах эта команда будет опущена.

-- вставка записи
insert into tblname(id,num) values(2,2.9);
-- применяем внесенные в БД изменения
commit;
-- вывод всех данных таблицы для проверки
select * from tblname;
Если задаются значения всех столбцов, то их не обязательно перечислять после имени таблицы.
INSERT INTO tblname VALUES(5,2.9);
значения по умолчанию
Если для новой записи в качестве значения поля достаточно значения по умолчанию, то его можно не указывать в списке столбцов, либо в качестве значения указать default.

-- не указываем поле num в списке столбцов
INSERT INTO tblname(id) VALUES(3);

-- используем default
INSERT INTO tblname(id,num) VALUES(4,DEFAULT);

-- если бы определение столбца в MySQL было бы таким
-- id integer default 0 AUTO_INCREMENT,
-- или мы написали тригер генерирующий
-- значения первичного ключа в Oracle
INSERT INTO tblname(num) VALUES(DEFAULT);

-- короткая запись предыдущей команды для MySQL
INSERT INTO tblname VALUES();
подзапросы
В качестве значений можно использовать подзапрос. Как и в предыдущих примерах, число столбцов в выборке должно соответствовать перечислению столбцов в команде insert into.

-- число столбцов в выборке соответствует
-- числу столбцов в таблице
INSERT INTO tblname SELECT ...

-- в выборке должен быть один столбец
INSERT INTO tblname(id) SELECT ...
вставка по условию
Продвинутые СУБД предоставляют расширенные возможности по вставке данных. Например, в Oracle можно произвести вставку в несколько таблиц, и если нужно можно указать условия.

-- вставка в таблицы tbl1, tbl2, tbl3
-- по условию
INSERT ALL
-- в таблицу tbl1 если поле val в выборке меньше 100
WHEN val < 100 THEN
INTO tbl1
WHEN val > 100 AND val < 200 THEN
INTO tbl2
ELSE
INTO tbl3
SELECT ..., my_val AS val, ...;
Обновление записей
Для обновления записей используется команда UPDATE, где в конструкции SET перечисляются обновляемые поля записей, а в необязательной конструкции WHERE указывается условие при котором запись будет обновлена. Пусть есть следующая таблица с данными.

-- тестовая таблица
create table tblname (
id integer not null,
num double precision default 0.0,
num2 double precision default 5.5,

constraint pk_tblname primary key (id)
);

-- тестовые записи
insert into tblname(id,num) values(1,1.0);
insert into tblname(id,num) values(2,2.0);
insert into tblname(id,num) values(3,3.0);
insert into tblname(id,num) values(4,4.0);
insert into tblname(id,num) values(5,5.0);
insert into tblname(id,num) values(6,6.0);
commit;

-- просмотр результата вставки
select * from tblname
Ниже приведен пример обновления полей num и num2 всех записей таблицы, у которых значение первичного ключа (т.е. поле id) четное число.

UPDATE tblname SET
-- задаем значение по умолчанию
num = default,
num2 = num2*2
WHERE id%2=0; -- для Oracle: WHERE mod(id,2)=0;

commit;

-- просмотр результата обновления
select * from tblname order by id
обновление подзапросом
В качестве значения можно использовать подзапрос с одним столбцом. Ниже приведен пример обновления нечетных записей. В качестве подзапроса для простоты используется выборка констант.

UPDATE tblname SET
num = (select 2 )
WHERE id%2!=0;

-- для Oracle
UPDATE tblname SET
num = (select 2 from dual)
WHERE mod(id,2)!=0;
обновление по данным другой таблицы
Очень часто необходимо обновить поля данными из других таблиц. Для этого создадим еще одну таблицу с данными tblname1 таким же образом как tblname. Новая таблица будет служить источником данных. В качестве соответствия между записями двух таблиц выберем условие, при котором первичный ключ первой таблицы больше первичного ключа новой таблицы на 1. Также для удобства в запросах используем локальные синонимы (алиасы) для имен таблиц.

-- для Oracle
-- пользуемся тем, что в этой СУБД можно
-- обновить группу столбцов одним запросом.
-- предикат exist предотвращает изменение
-- первой записи значениями null
UPDATE tblname a SET
(num, num2)=
(
select num,num2
from tblname1 b
where a.id=b.id+1
)
WHERE exists (
select 1
from tblname1 b
where a.id=b.id+1
);

-- для PostgreSQL
UPDATE tblname as a SET
num=b.num,
num2=b.num2
FROM tblname1 as b
WHERE a.id=b.id+1;

-- для MySQL
UPDATE tblname a, tblname1 b SET
a.num=b.num,
a.num2=b.num2
WHERE a.id=b.id+1;
Oracle позволяет обновить одну таблицу, входящую в выборку, если она жестко связана с другими таблицами выборки первичными или уникальными ключами. Если в следующем примере попробовать указать условие a.id=b.id+1 для решения предыдущей задачи, то получим ошибку.

UPDATE
(
select a.*, b.num srcnum, b.num2 srcnum2
from tblname a, tblname1 b
where a.id=b.id
) a
SET
a.num=a.srcnum,
a.num2=a.srcnum2;
Удаление записей
Для удаления записей из таблицы служит команда DELETE FROM. В конструкции WHERE указывается условие, по которому отбираются удаляемые записи.

-- удалить определенные записи (четные)
DELETE FROM tblname
WHERE id%2 = 0; -- для Oracle: WHERE mod(id,2)=0;

-- удаление всех записей таблицы
DELETE FROM tblname;
Выборка записей
Выбор данных выполняется командой SELECT. Ниже приведен примерный список используемых ею конструкций и ключевых слов, полный же список зависит от реализации СУБД:
AS - определяет временный синоним источника данных или столбца;
FROM - указывает источники данных как таблицы, представления, другие выборки. По необходимости здесь можно указать соединение источников - каким образом запись одного источника сопоставляется с записью другого;
WHERE - позволяет указать условия по которым нужно производить отбор данных. Если хоть одно из перечисленных условий не выполняется, запись не попадает в выборку. Здесь также можно указать соединение источников;
ORDER BY - позволяет отсортировать выборку по указанным полям;
ASC, DESC - задают направление сортировки;
GROUP BY - позволяет разбить выборку на группы по указанному полю. Все записи, имеющие одно и то же значение в указанном поле, будут принадлежать одной группе;
HAVING - позволяет задать условие включения группы в выборку. Набор возможных условий как у WHERE плюс возможность использования агрегатных функций;
FOR UPDATE - позволяет заблокировать выбранные данные для изменения;
DISTINCT - позволяет включить в выборку только уникальные записи. Конечно это замедляет запрос, но бывает необходимо при использовании агрегатных функций.
Это наверно самая сложная команда, поэтому ее изучение лучше разбить на несколько частей. Здесь рассмотрим эту команду в общем, а соединения, группировку данных и подобное более детально чуть позже. Пусть имеется следующая таблица с указанными данными.

-- создание таблицы
create table tblname (
id integer,
num double precision,

constraint pk_tblname primary key (id)
);

-- вставка данных
insert into tblname values(1,2.0);
insert into tblname values(2,3.3);
insert into tblname values(3,6.6);
insert into tblname values(4,2.0);
insert into tblname values(5,3.3);
insert into tblname values(6,6.6);
commit;
выборка констант
Для выбора констант может указываться любой источник. Однако, если мы хотим просто подсчитать значение какого-то выражения, то указывать источник имеющий сотню тысяч записей затратно по ресурсам. Поэтому многие СУБД позволяют выбрать константы без указания источника. Oracle не поддерживает синтаксис SELECT без FROM, а для выбора констант используется специальная системная таблица dual.

-- вычисляем три простых выражения
SELECT 2+2, 2*2 , 2/2;
SELECT 2+2, 2*2 , 2/2 FROM dual; -- для Oracle

-- в выборке будет 6 одинаковых записей
SELECT 2+2, 2*2 , 2/2 FROM tblname;
выборка по столбцам таблиц
Если источники данных указаны, то кроме констант можно выбирать столбцы или строить выражения с их использованием. Столбец определяется как имя_источника.имя_столбца. Если источник данных один, то его имя можно опустить. Для выбора всех столбцов всех источников используется символ *. Аналогично можно выбрать все столбцы указанного источника: имя_источника.*. Ниже приведен пример выборки всех записей нашей таблицы.

-- перечисляем столбцы
SELECT tblname.id, tblname.num FROM tblname;
SELECT tblname.num FROM tblname;
SELECT tblname.num*2 FROM tblname;

-- все столбцы указанного источника
SELECT tblname.* FROM tblname;

-- все столбцы всех источников
SELECT * FROM tblname;
синонимы (алиасы)
При выборе данных можно назначать временные синонимы источникам данных и используемым столбцам. А в некоторых случаях необходимо. Например, когда источник подзапрос соединяется с другим источником, именование подзапроса обязательно. Другой пример, это объединение нескольких выборок, имена столбцов которых должны совпадать. Ключевое слово AS как правило необязательно, а в Oracle разрешено только для столбцов.

-- t синоним таблицы tblname
SELECT t.id, t.num
FROM tblname AS t; -- не для Oracle
SELECT t.id, t.num
FROM tblname t; -- для всех

-- именование подзапроса, используемого
-- как источник данных
SELECT * FROM (select num from tblname ) t

-- источник подзапрос соединяется
-- с другим источником
SELECT t.tid, t2.num
FROM (
select id as tid, num as tnum
from tblname
) t,
tblname t2
WHERE t.tid=t2.id

-- синоним для столбца
SELECT t.num*2 AS num2 FROM tblname t;

-- "двойное число" русский синоним для столбца
SELECT t.num*2 AS "двойное число"
FROM tblname t;
SELECT t.id "ид", t.num*2 AS "двойное число"
FROM tblname t;

-- `` расширение MySQL
SELECT t.num*2 AS `двойное число` FROM tblname t;
уникальные записи
Записи выборки считаются одинаковыми, если значения соответствующих полей одинаковы. Поэтому для демонстрации distinct на нашей таблице нужно исключить первичный ключ (поле id) из выборки.

-- есть одинаковые записи
SELECT t.num FROM tblname t;

-- только уникальные записи
SELECT DISTINCT t.num FROM tblname t;
Конструкция ORDER BY позволяет последовательно отсортировать сразу по нескольким столбцам. Столбцы, по которым происходит сортировка, желательно проиндексировать.

-- сортировка записей по полю num
SELECT * FROM tblname ORDER BY num

-- восходящая сортировка
SELECT * FROM tblname t
ORDER BY t.num ASC

-- нисходящая сортировка
SELECT * FROM tblname
ORDER BY t.num DESC
выборка по условию
Конструкция WHERE позволяет ограничить множество выбираемых записей. Ниже приведено несколько примеров.

SELECT * FROM tblname t WHERE t.id > 3

-- следующие примеры возвращают одни и те же записи
SELECT * FROM tblname t WHERE t.id=2 or t.id=4;

SELECT * FROM tblname t WHERE t.id in (2,4);

SELECT * FROM tblname t
WHERE t.id in(
-- для Oracle: select 2 as c from dual
select 2 as c
union
-- для Oracle: select 4 as c from dual
select 4 as c
); -- для Oracle

SELECT t.* FROM
tblname t,
(
-- для Oracle: select 2 as c from dual
select 2 as c
union
-- для Oracle: select 4 as c from dual
select 4 as c
) t2
WHERE t.id=t2.c;
выборка по группам
И напоследок пару примеров группировки данных.

-- группировка по полям num и id
SELECT t.num, t.id FROM tblname t
GROUP BY num, id;

-- первый столбец будет отсортирован по возрастанию
-- второй столбец каждой группы по убыванию
SELECT t.num, t.id FROM tblname t
GROUP BY num, id
ORDER BY num ASC, id DESC

-- исключаем из выборки группу 3.3
SELECT t.num, t.id FROM tblname t
GROUP BY num, id
HAVING num!=3.3
ORDER BY num ASC, id DESC
Соединения
Пусть имеются следующие две таблицы с данными.

-- таблица цветов
create table colors(
id integer,
cname varchar(45), -- название цвета

constraint pk_colors primary key (id)
);

insert into colors values(1,'красный');
insert into colors values(2,'синий');
insert into colors values(3,'зеленый');
insert into colors
· values(4,'белый');
insert into colors values(5,'черный');
insert into colors values(6,'бурмалиновый');

-- таблица объектов мира
create table objs (
id integer,
oname varchar(45), -- название объекта
fk integer, -- ссылка на цвет объекта

constraint pk_objs primary key (id),
constraint fk_objs foreign key (fk)
references colors(id)
);

insert into objs values(1,'небо',2);
insert into objs values(2,'уголь',5);
insert into objs values(3,'лист',3);
insert into objs values(4,'водка',null );
insert into objs values(5,'ягода',1);

commit;
Ниже приведен пример запроса из двух таблиц без условия соединения. В результате возвращается выборка из 30 записей, где для каждой записи первой таблицы сопоставлены по очереди все записи второй таблицы, или другими словами, все комбинации. select o.oname, c.cname from objs o, colors c;
Для присоединения к каждой записи первой таблицы, только ту запись из второй таблицы, на которую происходит ссылка, необходимо указать условие. Это можно сделать в конструкции where или в конструкции from. Ниже приведен пример запроса с внутренним соединением.

-- условие в where
select o.oname, c.cname
from objs o, colors c
where o.fk=c.id;

-- условие в from с помощью
-- конструкции JOIN ON
select o.oname, c.cname from
objs o INNER JOIN colors c ON (o.fk=c.id );
К одному источнику данных можно последовательно присоединять сколько угодно других источников.

select o.oname, c.cname, o2.oname as oname2
from
(objs o INNER JOIN colors c ON (o.fk=c.id ))
INNER JOIN (select * from objs ) o2 ON
(o.id=o2.id)
В нашем случае внутреннего соединения не достаточно, так как из выборки выпадает запись с пустым значением внешнего ключа. Если же поставить условие "o.fk=c.id or o.fk is null", то вновь получим лишние записи. Данная задача решается с помощью внешних соединений. Если по условию для записи одной таблицы не найдено ни одной записи из другой таблицы, то присоединится пустая запись. Слова одной и другой использованы здесь не случайно. При внешних соединениях явно указывается к какой таблице происходит присоединение. В ниже приведенном примере ключевое слово OUTER в большинстве СУБД можно опустить.

-- присоединяем к левой таблице
-- в результате в выборке будет 5 записей
-- в столбце cname будет одно пустое поле
select o.oname, c.cname from
(objs o LEFT OUTER JOIN colors c ON (o.fk=c.id ))

-- присоединяем к правой таблице
-- в результате в выборке будет 6 записей
-- в столбце oname будет два пустых поля
select o.oname, c.cname from
(objs o RIGHT OUTER JOIN colors c
ON (o.fk=c.id ))

-- в расширении Oracle можно указать (+)
-- в условии для той таблицы, для которой
-- будут генерироваться пустые записи
select o.oname, c.cname
from objs o, colors c
-- присоединяем к левой таблице
where o.fk=c.id(+)

select o.oname, c.cname
from objs o, colors c
-- присоединяем к правой таблице
where o.fk(+)=c.id
Пустое значение в выборке можно заменить функцией coalesce.

-- в MySQL между coalesce и скобкой не должно
-- быть пробелов.
select o.oname, coalesce(c.cname,'цвет не задан')
from (objs o LEFT OUTER JOIN colors c
ON (o.fk=c.id ));
Агрегатные функции, группировка данных
Для группировки данных в запросе select используется конструкция group by, в которой должны быть перечислены те же столбцы, что и после select. Ниже приведен пример вывода данных по группам для таблицы bills.

-- таблица счетов
create table bills(
id integer,
d date, -- дата счета
summ double precision ,-- сумма счета
constraint pk_bills primary key (id)
);

-- вставка данных
insert into bills
values(1, date '2008-01-01', 5.5);
insert into bills
values(2, date '2008-02-01', 3.14);
insert into bills
values(3, date '2008-03-01', 10.14);
insert into bills
values(4, date '2008-01-01', 7.2);
insert into bills
values(5, date '2008-02-01', 6.4);
insert into bills
values(6, date '2008-03-01', 2.5);
commit;

-- вывод данных по группам
select t.d, t.summ from bills t
group by t.d, t.summ
Сами по себе группы редко используются, и предыдущий пример выборки можно заменить сортировкой. Другое дело, если необходимо воспользоваться одной из групповых функций, называемых агрегатными:
avg([DISTINCT|ALL] column) - среднее значение по указанному столбцу;
count(*|[DISTINCT|ALL] соlumn) - количество элементов в выборке или в группе определяемой указанным столбцом;
sum([DISTINCT | ALL] соlumn) - сумма значений указанного столбца;
max(соlumn) - максимальное значение в столбце;
min(соlumn) - минимальное значение в столбце.
Ключевое слово DISTINCT позволяет игнорировать повторные значения в столбце, ALL обрабатывает все значения в столбце (по умолчанию), * позволяет включить в обработку поля с null значением. В MySQL между именем функции и скобкой не должно быть пробелов. Ниже приведен пример использования агрегатных функций в качестве выбираемых данных. Если агрегатная функция используется в выборке без group by, то она применяется ко всем записям выборки, иначе для каждой группы в отдельности. И в любом случае в перечислении select нельзя смешивать групповые столбцы с не групповыми.

-- статистические данные по всем месяцам
select count(*) as "число записей",
max(t.summ) as "макс. сумма",
min(t.summ) as "мин. сумма",
avg(t.summ) as "средняя сумма",
sum(t.summ) as "общая сумма"
from bills t;

-- статистические данные по каждому месяцу
select t.d as "месяц", count(1) as "число записей",
max(t.summ) as "макс. сумма",
min(t.summ) as "мин. сумма",
avg(t.summ) as "средняя сумма",
sum(t.summ) as "общая сумма"
from bills t
group by t.d
Агрегатные функции можно использовать в выражениях условия в конструкции having для отбора группы.

-- отбираем группы у которых общая сумма больше 12
select t.d as "месяц", count(*) as "число записей",
max(t.summ) as "макс. сумма",
min(t.summ) as "мин. сумма",
avg(t.summ) as "средняя сумма",
sum(t.summ) as "общая сумма"
from bills t
group by t.d
having sum(t.summ)>12
Операции над выборками
Так как выборка по сути является множеством, то и доступные операции над ними соответствующие:
UNION - объединение, в конечной выборке записи из обоих запросов;
INTERSECT - пересечение, в конечной выборке записи входящие в оба запроса;
EXCEPT - исключение, в конечной выборке записи входящие только в первый запрос.
Запросы участвующие в таких операциях должны следовать нескольким условиям. Иметь одинаковое число столбцов, соответствующие столбцы должны быть одного типа. Тип данных столбца должен быть простым, т.е. не разрешаются типы подобные blob. MySQL 5 поддерживает только UNION, в Oracle EXCEPT для других целей, а для исключения используется MINUS.

-- from dual только для Oracle
-- в MySQL нельзя заключить
-- запросы в круглые скобки
select 1 as i from dual
UNION
select 2 as i from dual

UNION -- попробуйте также INTERSECT и EXCEPT

select 2 as i from dual
UNION
select 3 as i from dual;
По умолчанию в результирующую выборку попадают только уникальные записи. Для включения всех записей используется ключевое слово ALL после имени операции. Например, в следующем примере будет две записи со значением 2.

select 1 as i from dual
UNION
select 2 as i from dual

UNION ALL

select 2 as i from dual
UNION
select 3 as i from dual;
Добавление итогов
Еще раз рассмотрим таблицу bills созданную в пункте об агрегатных функциях. Предположим мы хотим вывести все суммы, а в конце выборки добавить итоговую сумму. Наиболее универсальным способом является объединение двух запросов.

-- в MySQL между cast и ( не должно быть пробелов
select cast(t.d as char(12)) as d, t.summ
from bills t

union

select 'ИТОГ', sum(t.summ)
from bills t;
Для решения подобных задач в стандарте введена конструкция ROLLUP генерирующая дополнительную строку. Если в определении столбца агрегатная функция не используется, то соответствующее поле в этой строке заполняется значением null. В противном случае заполняется значением выражения столбца, причем агрегатная функция выполняется ко всем записям основной выборки.

-- для Oracle, столбцы группировки перешли в ROLLUP
select
coalesce(cast(t.d as char(12)),'ИТОГ') as d,
sum(t.summ) as summ
from bills t
group by ROLLUP(t.d, t.summ)

-- для MySQL
select
coalesce(cast(t.d as char(12)),'ИТОГ') as d,
sum(t.summ) as summ
from bills t
group by t.d, t.summ WITH ROLLUP;
А теперь предположим мы хотим вывести все суммы с итогами по каждой группе и в конце выборки общий итог. Ниже приведен пример с использованием объединений. Чтобы общий итог был точно в конце выборки, задаем в поле d максимальню дату. В Oracle и Postgre можно оставить значение null.

select t.d1, t.summ from (

select
cast(t.d as char(12)) as d1,
t.d,
t.summ
from bills t

union

select -- выборка итогов по группам
'итого' as d1,
t.d,
sum(t.summ) as summ
from bills t
group by t.d

union

select -- выборка общего итога
'ИТОГО' as d1,
DATE '9999-01-01' as d,
sum(t.summ) as summ
from bills t

) t
order by d,summ
Подобную задачу можно решить с помощью стандартной конструкции CUBE, если она уже реализована в СУБД. Куб генерирует не только общий итог, но и все возможные под итоги. Ниже приведен пример использования куба. Для упрощения кода пустые значения не заменяются.

select * from (
select
t.d,
t.summ,
sum(t.summ) as itog
from bills t
-- для MySQL: group by t.d, t.summ WITH CUBE
group by cube(t.d, t.summ) -- для Oracle
order by d
) t
where -- убираем не нужные под итоги
not(t.summ=itog and d is null) or
(d is null and summ is null)
Нумерация записей
В стандарт SQL2003 уже добавлена функция row_number(), если она еще не реализована в вашей версии БД, используйте следующие методы.
Oracle
В Oracle для нумерации записей введен псевдостолбец rownum.

select rownum, t.*
from tblname t
order by id desc

-- rownum можно использовать в условиях
-- попробуйте операции <> и >
select rownum, t.*
from tb t
where rownum < 4
order by id desc;
MySQL
В MySQL для этого надо воспользоваться переменной. Чтобы увидеть результат следующего примера в MySQLQueryBrowser, необходимо начать транзакцию (на панели кнопка после слова Transaction). Далее выполняем приведенные в примере команды и затем завершаем транзакцию (соседняя кнопка с галочкой).

-- устанавливаем значение локальной переменной в 0
-- ее можете назвать как хотите
set @rownum:=0;

-- выборка с нумерацией
select @rownum:[email protected]+1, t.*
from tblname t
order by id desc;
PostgreSQL
В PostgreSQL для этих целей можно выделить последовательность и сбрасывать ее перед новой выборкой.

-- создаем временную (для текущей сессии)
-- последовательность seqrownum для нумерации записей
create temp sequence seqrownum;

-- сбрасываем последовательность
select setval('seqrownum',1);

-- выборка с нумерацией
select nextval('seqrownum')-1, t.*
from tblseq t
order by id desc;
Обеспечение уникальности первичного ключа
В некоторых СУБД как Oracle и PostgreSQL для обеспечения уникальности первичного ключа можно воспользоваться объектом последовательность, генерирующий последовательность целых чисел. По умолчанию 1027 максимальное значение для восходящей последовательности в Oracle и 1063 в PostgreSQL. Так что одной последовательности вполне хватит на несколько таблиц. В Oracle последовательность имеет два псевдостолбца:
CURRVAL - текущее значение последовательности;
NEXTVAL - увеличивает текущее значение на 1, а затем возвращает его.
В PostgreSQL для этой цели используются одноименные функции. Кроме этого есть функция setval, позволяющая задать текущее значение последовательности. Это можно использовать, например, при нумерации записей в выборке.

-- создаем последовательность
-- с параметрами по умолчанию
CREATE SEQUENCE seq;

-- создаем тестовую таблицу
create table tblseq(
id integer not null,
num double precision,
constraint pk_tblseq primary key (id)
);

-- вставляем данных для Oracle
insert into tblseq values(seq.nextval,3.14);
insert into tblseq values(seq.nextval,6.14);
insert into tblseq values(seq.nextval,7.14);
commit;

-- вставляем данные для PostgreSQL
insert into tblseq values(nextval('seq'),3.14);
insert into tblseq values(nextval('seq'),6.14);
insert into tblseq values(nextval('seq'),7.14);
commit;

-- проверяем
select * from tblseq;

-- уничтожение последовательности
DROP SEQUENCE seq;
Чтобы сделать присваивание значения первичного ключа автоматическим, в PostgreSQL надо подкорректировать значение столбца по умолчанию в определении таблицы, а для Oracle написать тригер.

-- для PostgreSQL
create table tblseq(
id integer not null DEFAULT nextval('seq'),
num double precision,
constraint pk_tblseq primary key (id)
);

-- для Oracle, создаем тригер tblseq_autoid
create or replace trigger tblseq_autoid
before insert on tblseq
for each row
declare
begin
select seq.nextval into :new.id from dual;
end tblseq_autoid;

-- теперь значение id будет устанавливаться
-- автоматически следующим значением
-- последовательности seq
insert into tblseq(num) values(3.14);
insert into tblseq(num) values(6.14);
insert into tblseq(num) values(7.14);
commit;
столбцы с автоинкрементом
Как в MS Access введен специальный тип idenity или counter, так в MySQL и PostgreSQL можно использовать тип serial для автоматического создания последовательности связанной со столбцом таблицы. В MySQL также можно воспользоваться атрибутом столбца AUTO_INCREMENT в определении таблицы. Фактически, тип serial эквивалент определения BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. В PostgreSQL для последовательности типа serial используется целое в 4 байта (serial4). Если это кажется малым, можно воспользоваться типом serial8.

-- для MySQL
CREATE TABLE tblname (
id integer unsigned AUTO_INCREMENT,
...

-- для MySQL и PostgreSQL
create table tblseq(
id serial,
num double precision,
constraint pk_tblseq primary key (id)
);

-- значение id будет устанавливаться автоматически
-- следующим значением
insert into tblseq(num) values(3.14);
insert into tblseq(num) values(6.14);
insert into tblseq(num) values(7.14);
commit;
При разработке клиентских программ необходимо знать значение первичного ключа последней вставленной записи для позиционирования на нее. В PostgreSQL для этого есть функция lastval(), возвращающая самое последнее сгенерированное значение для какой-либо последовательности (не важно созданной через SEQUENCE, или через тип serial) в текущей сессии. В MySQL аналогичная функция называетсяLAST_INSERT_ID().
Индексы
Индексы служат для сортировки таблиц по указанным столбцам. Они существенно ускоряют работу с большими данными. Ниже приведен минимальный синтаксис создания индекса.

-- пусть есть некая таблица tblname
-- со столбцом tblcol
create table tblname(
...
tblcol date;
...
);

-- создаем индекс ind_tblname_d для этой таблицы
CREATE INDEX ind_tblname_d ON tblname (tblcol);
В большинстве СУБД позволяется индексация по нескольким столбцам. Первичные и уникальные ключи обычно индексируются автоматически.
Представления
Представления позволяют сохранить код выборки в базе данных. После этого имя представления используется в других выборках также как и имя обычной таблицы. Таким образом, упрощается написание новых запросов. Для конкретности примера, пусть существуют следующие таблицы с данными.

-- создаем первую таблицу
create table dicevents(
id integer,
s varchar(45),

constraint pk_dicevents primary key(id)
);

-- вставка данных
insert into dicevents
values(1,'пожар');
insert into dicevents
values(2,'авария');
insert into dicevents
values(3,'взрыв газа');

-- создаем вторую таблицу
create table evt(
id integer,
d date,
summ double precision,
evtname integer,

constraint pk_evt primary key(id),
constraint fk_evt foreign key(evtname)
references dicevents(id)
);

-- вставка данных
insert into evt
values(1,date '2008-01-02',102000,1);
insert into evt
values(2,date '2007-12-31',67000,3);
insert into evt
values(3,date '2008-01-05',32020.3,2);
insert into evt
values(4,date '2008-01-06',99000,null);
Для создания представлений используется команда CREATE VIEW. По желанию в команду можно включить OR REPLACE. В этом случае новое представление заменит старое, если оно существует. Ниже приведены примеры создания представления на соединение наших таблиц.

CREATE OR REPLACE VIEW evtlst
AS -- далее следует запрос
select t.d as "дата",
dt.s as "событие",
t.summ as "ущерб"
from evt t left join dicevents dt on (t.evtname=dt.id)
order by t.d;

-- названия столбцов можно задать после
-- имени представления
-- в MySQL двойные кавычки возможны
-- только в режиме ANSI SQL
CREATE OR REPLACE VIEW evtlst
("дата","событие","ущерб")
as
select t.d, dt.s, t.summ
from evt t left join dicevents dt on (t.evtname=dt.id)
order by t.d;
Выборка из представления не отличается от выборки из обычной таблицы.

-- выборка из представления
select * from evtlist;
Для уничтожения представления используется команда DROP VIEW. Некоторые СУБД поддерживают расширенный синтаксис с IF EXISTS, позволяющий избежать ошибки, если уничтожаемого представления не существует.

-- уничтожение представления
DROP VIEW eventlist;

-- уничтожение представления, если оно
-- существует
DROP VIEW IF EXISTS eventlist;
Динамический SQL (DSQL)
Динамический SQL это расширение, позволяющее писать подпрограммы на процедурном языке с использованием команд SQL. PostgreSQL поддерживает несколько процедурных языков как PL/Tcl и PL/Perl. Здесь же рассматривается PL/pgSQL, который очень близок по синтаксису и своим возможностям с Oracle PL/SQL. Процедурный язык в MySQL более ограничен и находится на стадии разработки.
Для использования языка PL/pgSQL, его надо установить в БД командой CREATE LANGUAGE plpgsql;
Процедурные операторы
блоковая структура кода
Процедурный язык имеет блоковую структуру. Группировка команд производится операторами BEGIN END. Вложенные блоки разрешаются. Oracle позволяет независимый блок, у остальных внешний блок должен быть в составе функции или т.п. Оператор DECLARE позволяет объявить локальные переменные блока. По умолчанию переменные инициализируются в null. Для удобства в Oracle и PostgreSQL можно использовать два псевдотипаtype и rowtype. Первый определяет тип переменной такой же как у столбца указанной таблицы. Второй определяет тип переменной как структуру соответствующую записи указанной таблицы.

-- Oracle, PostgreSQL
DECLARE
i integer:=3;
t tblname.id%type;
trow tblname%rowtype;
BEGIN
...
END;

-- MySQL
BEGIN
-- локальная переменная
DECLARE o integer DEFAULT 3;
DECLARE i double precision DEFAULT 3.14;
...
END;
присвоение
Для присвоения значения переменной используется операция :=. В MySQL присвоение должно происходить в операторе SET (по стандарту).

-- Oracle, PostgreSQL
i:=23;

-- MySQL
SET i:=23;
SET i=23;
условный оператор
Условный оператор IF позволяет выполнить ряд команд, если указанное условие истинно. Внутри него может быть сколько угодно не обязательных алтернативных условий ELSE IF, и одного необязательного слова ELSE, определяющего что делать, если ни одно условие не выполнено.

-- Oracle
IF i >3 THEN
delete from tblname where id>40;
ELSIF i=-4 then -- альтернативное условие
delete from tblname where id=4;
ELSE
delete from tblname ;
END if;

-- MySQL
IF i >3 THEN
delete from tblname where id>40;
ELSEIF i=-4 then -- альтернативное условие
delete from tblname where id=4;
ELSE
delete from tblname ;
END IF;

-- в PostgreSQL ELSEIF синоним ELSIF,
-- так что оба приведенных выше примера верны
оператор выбора
В первом синтаксисе оператор выбора CASE позволяет выполнить тот или иной код в зависимости от значения указанного выражения. Во втором синтаксисе конструкция CASE аналогична условному оператору. Обратите внимание, что в отличие от CASE, используемого в обычных командах SQL, этот оператор завершается словами END CASE, а не просто END. Данный оператор не поддерживается в PostgreSQL.

-- Oracle
CASE i
WHEN 2 THEN i:=4;
WHEN 5 THEN i:=0;
ELSE i:=5;
END CASE;

-- MySQL
CASE i
WHEN 2 THEN
set i:=4;
WHEN 5 THEN
set i:=0;
ELSE
set i:=5;
END CASE;
метки
Метки полезны, когда есть вложенные циклы и нужно выйти из внешнего внутри вложенного. Фактически они именует блок или цикл. А в MySQL они обязательны для операторов выхода из цикла и продолжения итерации.

-- Oracle, PostgreSQL
<>

-- MySQL
l1:
безусловный цикл
Безусловный цикл определяется оператором LOOP. Другими словами условие выхода указывается явно внутри тела цикла.

-- Oracle, PostgreSQL
-- оператор выхода EXIT
-- без метки
LOOP
i:=i+1;
EXIT WHEN i>11;
END LOOP;

-- Oracle, PostgreSQL
-- с меткой
<>LOOP
i:=i+1;
EXIT l1 WHEN i>11;
END LOOP l1;

-- MySQL
-- оператор выхода LEAVE
l1: LOOP
set i:=i+1;
if i>11 then
LEAVE l1;
end if;
END LOOP l1;
цикл с предусловием
Цикл с предусловием определяется оператором WHILE.

-- Oracle, PostgreSQL
WHILE i < 10 LOOP
i:=i+1;
END LOOP;

-- MySQL
WHILE i < 10 DO
set i:=i+1;
END WHILE;
цикл по счетчику
MySQL не поддерживает цикл по счетчику. Счетчик в виде переменной объявлять не надо. По умолчанию счетчик изменяется на 1, если присутствует ключевое слово REVERSE на -1.

-- Oracle, PostgreSQL
FOR j IN 1..10 LOOP
i:=i+1;
END LOOP;

-- Oracle
FOR j IN REVERSE 1..10 LOOP
i:=i+1;
END LOOP;

-- PostgreSQL
FOR j IN REVERSE 10..1 LOOP
i:=i+1;
END LOOP;
цикл по элементам
Для обработки записей запроса добавлен специальный цикл. В PostgreSQL переменная, в которой будет храниться текущая запись, должна быть объявлена явно с типом RECORD. Также вместо одной переменной можно указать список переменных, соответствующих столбцам запроса.

-- Oracle
FOR cur IN
(select * from tblname )
LOOP
i:=i+cur.id;
END LOOP;

-- PostgreSQL
declare
i integer := 1;
cur RECORD;
begin

FOR cur IN
(select * from tblname )
LOOP
i:=i+cur.id;
END LOOP;
...
операторы выхода/продолжения итерации
Для выхода из цикла и перехода на следующую итерацию служат операторы EXIT и CONTINUE в Oracle и PostgreSQL. В MySQL это операторы LEAVE и ITERATE.

-- Oracle, PostgreSQL
<>for m in 1..20 loop
for n in 1..30 loop

if n=15 then
CONTINUE;
end if;

i:=n+m;
...

-- выход из обоих циклов
EXIT l1 WHEN i>50;
end loop;
end loop l1;

-- MySQL
l1: loop
set i:=i+1;

if i=5 then
ITERATE l1;
end if;
...

if i>11 then
LEAVE l1;
end if;
end loop l1;
Выборка в переменные
В динамический SQL добавлен особый синтаксис для команды SELECT, позволяющий сохранить выбранные данные в указанные переменные. Если запрос возвращает более одной записи или ни одной, то возникает исключение.

-- Oracle, PostgreSQL
-- переменная r типа tblname%rowtype;
SELECT * INTO r FROM tblname WHERE id=1;

-- PostgreSQL
-- переменная r типа record
SELECT * INTO r FROM tblname WHERE id=1;

-- Oracle, PostgreSQL, MySQL
-- типы и колличество переменных v соответствует
-- типам и колличеству столбцов в выборке
SELECT * INTO v1, v2,... FROM tblname WHERE id=1;
Хранимые процедуры
Процедуры и функции основные места, где разрешено использования процедурного расширения, что позволяет решать более сложные задачи. Процедуры не возвращают значений как это делают функции, однако результат их работы можно сохранить в аргументах. Для этого им надо задать атрибут OUT, по умолчанию же аргументы обычно имеют атрибут IN.
Некоторые СУБД позволяют перегрузку, т.е. создание нескольких процедур или функций с одним именем, но с разным числом или типами аргументов.
Использование хранимых процедур уменьшает нагрузку на клиентские машины, за счет нагрузки на сервер. Во-вторых, они облегчают разработку клиентских программ, особенно их модернизацию. Так как при незначительных изменениях хранимой процедуры не нужно компилить заново клиентскую программу (вызывающую хранимую процедуру) и раздавать новые версии пользователям.
Для примера создадим процедуру вставки пяти записей в следующую таблицу:

create table tblname (
id integer,
i integer,

constraint pk_tblname primary key(id)
);
Oracle
В Oracle можно задать аргументам значения по умолчанию. Конструкция OR REPLACE позволяет пересоздать процедуру, если она существует без вывода сообщения об ошибке. Для уничтожения процедуры служит командаDROP PROCEDURE. Перегрузка разрешена только внутри пакетов.

-- создание процедуры
CREATE OR REPLACE PROCEDURE insert5(n IN OUT integer)
AS
o integer :=n; -- локальная переменная
BEGIN
delete from tblname where id < 6;
insert into tblname values(1,o);
insert into tblname values(2,o);
insert into tblname values(3,o);
insert into tblname values(4,o);
insert into tblname values(5,o);
commit;
n:=5;
END insert5;

-- уничтожение процедуры
drop procedure insert5;
PostgreSQL
Для создания процедур и функций в PostgreSQL используется одна конструкция. В первом случае мы просто не указываем тип возвращаемого значения. Более того, это запрещено, если выходных параметров более одного. Если выходных аргументов нет, то в качестве возвращаемого типа надо указать void. Также нельзя задавать аргументам значение по умолчанию, но это компенсируется механизмом перегрузки.

-- создание процедуры
CREATE OR REPLACE FUNCTION insert5(n INOUT integer)
AS $$
DECLARE
o integer :=5; -- локальная переменная
BEGIN
delete from tblname where id < 6;
insert into tblname values(1,o);
insert into tblname values(2,o);
insert into tblname values(3,o);
insert into tblname values(4,o);
insert into tblname values(5,o);
commit;
n:=5;
END
$$
LANGUAGE plpgsql;

-- нет выходных аргументов
CREATE OR REPLACE FUNCTION pdemo()
returns void
AS $$
DECLARE
r integer;
BEGIN
r:=3;
END
$$
LANGUAGE plpgsql;

-- уничтожение процедуры
DROP FUNCTION insert5(n INOUT integer);
MySQL
Ниже приведен пример процедуры для MySQL.

-- создание процедуры
DELIMITER $$

DROP PROCEDURE IF EXISTS insert5 $$
CREATE PROCEDURE insert5 (INOUT n integer)
BEGIN
declare o integer default 1;
set o:=n;
delete from tblname where id < 6;
insert into tblname values(1,o);
insert into tblname values(2,o);
insert into tblname values(3,o);
insert into tblname values(4,o);
insert into tblname values(5,o);
commit;
set n:=5;
END $$

DELIMITER ;

-- уничтожение процедуры
DROP PROCEDURE insert5;
вызов процедур
Хотя по стандарту вызов процедур делается командой CALL, в Oracle она разрешена только вне процедурного блока. А внутри блока вызывается обычным образом. PostgreSQL вообще не поддерживает эту команду и для вызова процедуры используется команда SELECT.

CALL procname();

-- Oracle, внутри процедурного блока
procname();

-- PostgreSQL
select pdemo();
Исключения
Рассматриваемые здесь СУБД позволяют по-своему обработать исключения - ситуации возникновения ошибок. В Oracle и Postgre это достигается добавлением в блок секции EXCEPTION, в котором и происходит обработка исключения.
Для конкретности рассмотрим исключения, возникающие во время SELECT INTO для следующей таблицы с данными.

create table tblname (
id integer,
constraint pk_tblname primary key(id)
);

insert into tblname values(1);
insert into tblname values(2);
insert into tblname values(3);
insert into tblname values(4);
insert into tblname values(5);
commit;
Oracle

create or replace function F(n in integer)
return integer
as
o integer :=5;
begin

BEGIN
select id into o
from tblname
where id=n or id=2*n;

EXCEPTION
WHEN NO_DATA_FOUND THEN
o:=0;
WHEN TOO_MANY_ROWS THEN
o:=999;
END;

return o;
end F;
PostgreSQL
Ключевое слово strict позволяет генерировать исключения.

create or replace function F(n in integer)
returns integer
as
$$
DECLARE
o integer :=5;
begin

BEGIN
select id into STRICT o
from tblname
where id=n or id=2*n;

EXCEPTION
WHEN NO_DATA_FOUND THEN
o:= 0;
WHEN TOO_MANY_ROWS THEN
o:= 999;
END;

return o;
end;
$$
LANGUAGE 'plpgsql';
MySQL
В MySQL обработчик исключения объявляется вначале блока при этом указывается продолжать ли выполение после обработки (CONTINUE) или выйти из блока (EXIT). Сама обработка должна занимать только один оператор, обычно это SET или BEGIN END.

DELIMITER $$

DROP FUNCTION IF EXISTS f $$
CREATE FUNCTION f (n integer ) RETURNS integer
BEGIN
declare o integer default 0;

begin
DECLARE CONTINUE HANDLER FOR 1329 set o:=0;
DECLARE CONTINUE HANDLER FOR 1172
begin
set o:=999;
...
end;

select id into o
from tblname
where id=n or id=2*n;
end;

return o;
END $$

DELIMITER ;
Курсоры
Курсор - объект, позволяющий работать с областью памяти, в которой сохраняются записи указанного запроса данных. В стандарте определены три команды управления курсором:
OPEN - открывает курсор;
FETCH - берет текущую запись из результирующего набора записей. После каждого применения FETCH следующая запись становится активной. Структурная переменная или список переменных указанных во фрагменте INTO должны соответствовать столбцам записи;
CLOSE - закрывает курсор, в большинстве СУБД закрытие курсора делается автоматически в конце блока, в котором курсор объявлен.
Oracle
В Oracle для определения успешного выбора записи используются два атрибута курсора:
FOUND - истина, если запись найдена;
NOTFOUND - истина, если запись не найдена.
Для сохранения данных всех записей курсора в массивы используется конструкция BULK COLLECT. Ниже приведен пример функции подсчитывающий число записей в таблице tblname с четным идентификатором с помощью курсоров.

-- создание функции
CREATE OR REPLACE FUNCTION curdemo
RETURN integer
IS
-- курсор
cursor cur is select * from tblname;
-- можно указать какого типа будет запись курсора
cursor cur2 return tblname%rowtype
is select * from tblname;
val tblname%rowtype;
r integer:=0;
BEGIN
OPEN cur;
loop
FETCH cur INTO val;

-- если записи нет
EXIT WHEN cur%NOTFOUND;

if mod(val.id,2)=0 then
r:=r+1;
end if;
end loop;
CLOSE cur;
return(r);
END curdemo;

-- проверка
select curdemo() from dual;
PostgreSQL
В PostgreSQL для определения успешного выбора записи курсора используется специальная переменнаяFOUND. Кроме стандартных возможностей позволяется перемещаться по курсору командой MOVE без выбора данных. Во-вторых, выбор записи и перемещение можно делать не последовательно, а перепрыгивая через записи и в любом направлении. Ниже приведен аналогичный пример для PostgreSQL.

-- создание функции
CREATE OR REPLACE FUNCTION curdemo()
RETURNS integer
AS $$
DECLARE
val tblname%rowtype;
-- курсор
cur CURSOR IS select * from tblname;
r integer:=0;
BEGIN
OPEN cur;
loop
FETCH cur INTO val;

-- если записи нет
EXIT WHEN not FOUND ;

if mod(val.id,2)=0 then
r:=r+1;
end if;
end loop;
CLOSE cur;
return r;
END;
$$
LANGUAGE plpgsql;

-- проверка
select curdemo();
MySQL
Ниже приведен аналогичный пример для MySQL.

-- создание функции
DELIMITER $$
DROP FUNCTION IF EXISTS curdemo
CREATE FUNCTION curdemo()
RETURNS integer
BEGIN
declare r integer default 0;
declare val integer default 0;
declare nodata boolean default false;
-- курсор
declare cur CURSOR FOR SELECT id FROM tblname;
-- обработчик отсутствия следующей записи в курсоре
declare continue handler for not found set nodata=true;

OPEN cur; -- открываем курсор
l1:loop
FETCH cur INTO val;

-- если записи нет
if nodata then
close cur; -- закрываем курсор
leave l1; -- выходим из цикла
end if;

if mod(val,2)=0 then
set r:=r+1;
end if;
end loop l1;

return r;
END;
$$
DELIMITER;

-- проверка
select curdemo();
Триггеры
Триггер это процедура обработки события связанного с таблицей, которая вызывается автоматически. Ниже приведен список стандартных событий:
удаление записи из таблицы;
вставка записи в таблицу;
изменение записи в таблице.
Дополнительно можно указать до или после производить обработку события. Внутри триггера доступны две особых переменных определяющих значение старой и новой записи. Для изменения доступна только вторая переменная, и то, если обработка происходит до наступления события. Для примера создадим триггер на вставку для следующей таблицы:

-- создание таблицы
create table tblname (
id integer,
str varchar(45),
dt date,

constraint pk_tblname primary key (id)
);
Назначение нашего тригера в подмене null значений поля dt текущей датой перед вставкой новой записи.
Oracle

-- создаем тригер
create or replace trigger tgins_tblname
before insert on tblname
for each row
declare
-- место для локальных переменных
r integer;
begin

if :old.dt is null then
:new.dt := current_date;
end if;

end tgins_tblname;

-- проверка
insert into tblname
values(1,'text info',null);
commit;
select * from tblname;

-- отключить тригер
ALTER TRIGER test_trig DISABLE;

-- включить тригер
ALTER TRIGER test_trig ENABLE;

-- удаление тригера
DROP TRIGER test_trig;
PostgreSQL
В PostgreSQL триггер не содержит кода, а только указывает на функцию, которую нужно запускать. Причем не любая функция, а возвращающая тип trigger. Кроме переменных new и old в триггерных процедурах доступны дополнительные переменные, которые тоже могут пригодится.

-- триггерная функция
-- в ней доступны переменные new и old
CREATE FUNCTION tgpins_tblname() RETURNS trigger
AS $$
BEGIN

if new.dt is null then
new.dt:=current_date;
end if;

RETURN NEW;
END;
$$
LANGUAGE plpgsql;

-- а теперь создаем сам тригер
CREATE TRIGGER tgins_tblname
BEFORE INSERT ON tblname
FOR EACH ROW
EXECUTE PROCEDURE tgpins_tblname();

-- проверка
insert into tblname
values(1,'text info',null);
commit;
select * from tblname;

-- отключить тригер
ALTER TABLE DISABLE TRIGGER tgins_tblname;

-- включить тригер
ALTER TABLE ENABLE TRIGGER tgins_tblname;

-- удаление тригера
DROP TRIGGER IF EXISTS tgins_tblname ON tblname;
MySQL

-- создаем тригер
DELIMITER $
CREATE TRIGGER tgins_tblname
BEFORE INSERT ON tblname
FOR EACH ROW
BEGIN

if new.dt is null then
set new.dt:=current_date();
end if;

END$
DELIMITER;

-- проверка
insert into tblname
values(1,'text info',null);
commit;
select * from tblname;

-- удаление тригера
DROP TRIGGER IF EXISTS tgins_tblname;
Числа прописью
В практической жизни для печати различных документов полезна функция преобразования числа в строку, где число указано словами. Ниже приведен пример решения этой задачи на Oracle для чисел меньших миллиона. При желании диапазон чисел легко расширить до необходимого. Переписать код для другой СУБД также не представляет никакой сложности. Возможно можно и проще, но привожу, что первое пришло в голову.

-- таблица сопоставления чисел и строк
create table numbers
(
n integer,
gender integer,
str varchar(20),

CONSTRAINT pk_numbers PRIMARY KEY (n,gender)
);

/*
0 - без разницы какой род
1 - мужской род
2 - женский род
3 - средний род
*/

insert into numbers values(0, 0,'ноль');
insert into numbers values(1, 1,'один');
insert into numbers values(1, 2,'одна');
insert into numbers values(1, 3,'одно');
insert into numbers values(2, 1,'два');
insert into numbers values(2, 2,'две');
insert into numbers values(3, 0,'три');
insert into numbers values(4, 0,'четыре');
insert into numbers values(5, 0,'пять');
insert into numbers values(6, 0,'шесть');
insert into numbers values(7, 0,'семь');
...
insert into numbers values(19, 0,'девятьнадцать');

insert into numbers values(20, 0,'двадцать');
insert into numbers values(30, 0,'тридцать');
...
insert into numbers values(90, 0,'девяносто');

insert into numbers values(100, 0,'сто');
insert into numbers values(200, 0,'двести');
...
insert into numbers values(900, 0,'девятьсот');

insert into numbers values(1000, 0,'тысяча');
insert into numbers values(4000, 0,'тысячи');
insert into numbers values(5000, 0,'тысяч');
commit;

-- сохраняет в r число до 100 прописью
create or replace procedure n100_inwords
(
an integer,
ag in integer,
r out varchar
)
is
tmp integer:=0;
st varchar(255);
begin

-- числа меньшие 20
if an < 20 then

select t.str into r
from numbers t
where t.n=an and
(t.gender=ag or
t.gender=0
);

else

tmp:=mod(an,10);

if tmp !=0 then
-- единицы
select t.str into st
from numbers t
where t.n=tmp and (t.gender=ag or t.gender=0);
end if;

-- десятки
tmp:= an-tmp;
select t.str into r
from numbers t
where t.n=tmp and (t.gender=ag or t.gender=0);

r:=r||' '||st;
end if;
end n100_inwords;

-- сохраняет в r число до 1000 прописью
create or replace procedure n1000_inwords
(
an integer,
ag in integer,
r out varchar
)
is
tmp integer;
st varchar(255);
begin
tmp:= mod(an,100);

if tmp!=0 or (tmp=0 and an=0) then
n100_inwords(tmp,ag,st);
end if;

-- сотни
tmp:=an-tmp;

if tmp>0 then
select t.str into r
from numbers t
where t.n=tmp and
(t.gender=ag or t.gender=0);

r:=r||' '||st;
else
r:=st;
end if;
end n1000_inwords;


-- сохраняет в r число до 1000000 прописью
create or replace procedure n1000000_inwords
(
an in integer,
ag in integer,
r out varchar
)
is
tmp integer;
tmp1 integer;
st varchar(255);
begin

tmp:= mod(an,1000);

if tmp>0 or (tmp=0 and an/1000=0) then
n1000_inwords(tmp,ag,r);
else
r:='';
end if;

-- тысячи
tmp:=an-tmp;

if tmp>0 then
tmp1:= mod(tmp,10000);

select t.str into st
from numbers t
where
(t.n=1000 and tmp1=1000) or
(t.n>1000 and t.n < =4000
and tmp1>1000 and tmp1 < =4000) or
(t.n>=5000 and (tmp1>=5000 or tmp1=0));

r:=st||' '||r;
tmp:=tmp / 1000;
n1000_inwords(tmp,2,st);
r:=st||' '||r;
end if;
end n1000000_inwords;

-- возвращает строку - число до 1000000 прописью
create or replace function int_inwords
(
n in integer,
g in integer
)
return varchar2
is
r varchar2(255);
begin
n1000000_inwords(n,g,r);
return r;
end int_inwords;

-- проверка
select int_inwords(815334,1)||' руб. '||
int_inwords(32,2)||' коп.'
as summa
from dual
Транзакции, конкурирующие запросы
Под транзакцией понимается некоторая группа DML команд. Все изменения сделанные ими, сохраняются в отдельной области памяти до окончательного подтверждения изменений (успешное завершение транзакции), либо до их отмены. Если во время транзакции делается запрос на выборку данных, то создается отдельное представление. Для чего нужны транзакции? В англоязычной литературе концепция транзакций описывается абривиатурой ACID:
атомарность - выполнение или не выполнение всех DML команд входящих в тразакцию;
целостность БД - завершение транзакции не должно нарушать целостность БД;
изоляция - можно отображать либо исходные данные, которые были до начала транзакции, либо новые данные после выполнения транзакции;
сохранность данных - если пользователю пришло подтверждение выполнения транзакции, то его изменения не будут отменены по каким-либо причинам.
В стандарте предусмотрены следующие команды управления транзакциями:
START TRANSACTION - явное начало транзакции. Команда не поддерживается в Oracle. В MySQL и PostgreSQL можно использовать синоним begin (не путать с блоковым оператором begin, после которого нет разделителя). Если начало транзакции явно не указано, то PostgreSQL считает каждую DML команду отдельной транзакцией. В Oracle транзакции следуют одна за другой. То есть первая DML команда открывает транзакцию, следующие команды становятся частью этой транзакции, пока не будет вызвана команда commit или rollback. Если режим автоподтверждения (autocommit) включен, то MySQL работает также как PostgreSQL, иначе как Oracle;
COMMIT - завершить транзакцию, применяя все сделанные изменения;
ROLLBACK - завершить транзакцию, отменяя все сделанные изменения. Если точка отката не указана, то отменяется вся текущая транзакция;
SAVEPOINT - сохранить точку отката;
RELEASE SAVEPOINT - уничтожить точку отката, что позволяет освободить часть ресурсов до завершения транзакции;
SET TRANSACTION - устанавливает характеристики текущей транзакции. Если транзакция не начата явно, то эта команда игнорируется в PostgreSQL.
Ниже приведен пример использования некоторых команд внутри выполняемого блока в Oracle.

BEGIN
...
SAVEPOINT pt1; -- сохранили точку отката
...
EXCEPTION
WHEN VALUE_ERROR THEN -- неправильное значение
-- SAVEPOINT можно опустить
ROLLBACK TO SAVEPOINT pt1;
WHEN OTHERS THEN -- другие исключения
...
END;
Команда select ... for update, блокирует записи таблицы от изменений другими пользователями. При этом нельзя использовать другие конструкции как distinct, group by.

-- блокируем запись для обновления
SELECT * FROM tbl1
WHERE id=2
FOR UPDATE;

-- обновляем записи через UPDATE
...

-- завершаем транзакцию, блокировка снимается
COMMIT;
Для блокировки нескольких таблиц в различных режимах используется команда lock table. Блокировка снимается при завершении текущей транзакции.

-- блокируем таблицу в режиме SHARE
-- запросы данных разрешены
-- изменение таблицы запрещено
-- блокировать таблицу в режиме EXCLUSIVE запрещено
LOCK TABLE tbl2
IN SHARE MODE;

-- блокируем таблицу в режиме EXCLUSIVE
-- и не ждем, если другой пользователь уже заблокировал таблицу
LOCK TABLE tbl1
IN EXCLUSIVE MODE
NOWAIT;
В MySQL поддержка транзакций зависит от используемого способа хранения таблиц. Так для таблиц на движке InnoDB возможны транзакции, а для MyISAM нет. В последнем случае отсутствие одновременного обновления одних и тех же данных разными пользователями гарантируется самой СУБД. Невозможность отката, означает, необходимость самостоятельной предварительной проверки на ошибки перед внесением изменений. Такой подход увеличивает скорость работы от трех до пяти раз за счет уменьшения использования памяти, дискового пространства и процессора.
Схема
Объект схема позволяет логически сгруппировать множество других объектов. Доступ к объектам схемы осуществляется через точку. По стандарту в команду создания схемы позволяется вкладывать дополнительные команды создающие различные объекты схемы и раздающие права на эти объекты. А также владельцем всех объектов схемы должен являться владелец схемы.
PostgreSQL
В PostgreSQL есть пару отличий от стандарта. Хотя по стандарту все объекты схемы принадлежат владельцу схемы, в PostgreSQL объекты могут принадлежать различным пользователям. Это случается, если им были даны соответствующие права.
Во-вторых, в текущей версии СУБД порядок вложенных команд создания объектов схемы может иметь значение. В качестве вложенных команд могут быть команды создания таблиц, представлений, индексов, последовательностей, триггеров и команды раздачи привилегий. Если перед именем объекта имя схемы не указывается, то используется схема public.

-- создание схемы с объектами
-- владельцем схемы и объектов объявляется
-- пользователь root
CREATE SCHEMA newchema AUTHORIZATION root
create table tblname (
id integer,
str varchar(45),
constraint pk_tblname primary key (id)
)
create view v_tblname
as select * from tblname
grant select on tblname to public
grant select on tblname to public
;

-- переименование схемы
ALTER SCHEMA newchema RENAME TO newnama2;

-- смена владельца схемы
ALTER SCHEMA root OWNER TO anyuser;

-- уничтожение схемы если в ней нет объектов
DROP SCHEMA IF EXISTS newchema;

-- уничтожение схемы и всех ее объектов
DROP SCHEMA IF EXISTS newchema CASCADE;
Oracle
В Oracle для каждого пользователя автоматически создается одноименная схема с полным доступом. А командаCREATE SCHEME служит лишь для наполнения схемы множеством таблиц, представлений и правами доступами к ним другим пользователям. Ниже приведен пример, как пользователь root за одну команду создает одну таблицу, одно представление и открывает к ним общий доступ.

CREATE SCHEMA AUTHORIZATION root
CREATE TABLE tblname (
id integer,
str varchar(45),
dt date,
constraint pk_tblname primary key (id)
)
CREATE VIEW v_tblname
as select * from tblname
GRANT select ON tblname TO public
GRANT select ON tblname TO public
;
MySQL
В MySQL схема является синонимом отдельной базы данных на сервере и не поддерживает никаких вложенных команд. Единственное что можно указать при ее создании, это используемую кодировку и таблицу сравнения строк. При уничтожении схемы, права пользователей на эту схему не уничтожаются. Если перед именем объекта имя схемы не указывается, то используется схема, указанная при подключении пользователя. Если она не была указана, то выводится сообщение об ошибке. Сменить схему по умолчанию можно командойUSE.

-- создание схемы
CREATE SCHEMA IF NOT EXISTS newchema;

-- делаем схему newchema
-- схемой по умолчанию
USE newchema;

-- уничтожение схемы
DROP SCHEMA IF EXISTS newchema;
Управление аккаунтами
пользователи
Аккаунт или учетная запись характеризуется пользователем и правами доступа (привилегиями) назначенных пользователю. Обычно аккаунты защищены паролем.

-- создание пользователя
-- Oracle
CREATE USER username
IDENTIFIED BY psw

-- MySQL
CREATE USER username
IDENTIFIED BY 'pswd';

-- PostgreSQL
CREATE USER username
PASSWORD 'pswd';

-- уничтожения пользователя
DROP USER username;
права доступа
Права доступа обеспечивают уровень безопасности как для сокрытия данных от посторонних лиц, так и безопасность всей структуры БД от не преднамеренного уничтожения объектов. Команда добавления привелегийGRANT имеет три составляющие: имя привелегии, объект, на который даются права, и кому даются права - пользователь или роль.
Чтобы задать все доступные права на указанный объект, в стандарте предусмотрена конструкция ALL PRIVILEGES. Конструкция WITH GRANT OPTION дает разрешение пользователю самому назначать права. Чтобы сделать некоторые права на указанные объекты общедоступными в Oracle и PostgreSQL в качестве имени пользователя нужно указать public.

-- разрешаем пользователю username
-- вставлять, обновлять, вставлять и удалять записи
-- из таблицы tblname в текущей схеме
GRANT select, update, insert, delete
ON tblname
TO username;

-- разрешаем пользователю username
-- выполнять процедуру proc и функцию func

-- Oracle
GRANT execute ON proc TO username;
GRANT execute ON func TO username;

-- MySQL
GRANT execute ON PROCEDURE proc TO username;
GRANT execute ON FUNCTION func TO username;

-- PostgreSQL, если у процедуры или у функции
-- есть аргументы, то их надо указать
GRANT EXECUTE
ON FUNCTION proc()
TO username;

-- ---------------------
GRANT ALL PRIVILEGES
ON tblname
TO username
WITH GRANT OPTION;

-- Oracle, PostgreSQL
GRANT select
ON tblname
TO public;
права доступа MySQL
В MySQL схемы и пользователи независимы, поэтому при назначении прав необходимо указывать конкретную схему (имя_схемы.*) или распространять право на все схемы сразу (*.*). Последний вариант в MySQL называется глобальным уровнем назначением прав.
По сравнению с Oracle список прав не так велик из-за отсутствия некоторых типов объектов и объединения использования нескольких команд в одну привилегию. Ниже приведен список основных прав:
ALL PRIVILEGES - все права на указанный объект за исключением назначения прав на этот объект;
CREATE - право на создание таблицы;
ALTER - право на изменение таблицы;
DROP - право на уничтожение таблицы;
LOCK TABLES - право на блокировку таблицы;
CREATE TEMPORARY TABLES - право на создание временных таблиц;
CREATE ROUTINE - право на создание хранимых процедур и функций;
ALTER ROUTINE - право на изменение или уничтожение хранимых процедур и функций;
CREATE VIEW - право на создание представления;
TRIGGER - право на создание и уничтожение триггеров;
INDEX - права на создание и уничтожение индексов;
EXECUTE - право на выполнения хранимых процедур и функций;
EVENT - право на создание событий;
CREATE USER - права на создание, уничтожение, переименование пользователя и снятия все прав. Назначается только на глобальном уровне;
SELECT - право на выборку;
DELETE - право на удаление;
INSERT - право на вставку;
UPDATE - право на обновление;
FILE - право на использование команд SELECT ... INTO OUTFILE и LOAD DATA INFILE;
PROCESS - право на просмотр всех процессов командой SHOW PROCESSLIST;
SHOW DATABASES - право на просмотр списка схем;
SHOW VIEW - право на просмотр списка представлений;
SHUTDOWN - право на закрытие.

-- право на создание таблицы в схеме test
grant create on test.* to username;
-- право на уничтожение таблицы в схеме test
grant create on test.* to username;

-- право на создание, уничтожение, переименование
-- пользователя и снятие привилегий
GRANT create user ON *.* TO username
-- с правом назначать привилегии
WITH GRANT OPTION;
права доступа PostgreSQL
В PostgreSQL права задаются только на существующие объекты следующих типов: table (таблица), view (представление), sequence (последовательность), database (база данных), function (функция), procedural language (процедурный язык), schema (схема) и tablespace (табличное пространство). За исключение таблиц перед именами объектов обязательно указывается их тип. Ниже приведен список привилегий:
ALL PRIVILEGES - все привилегии на указанный объект;
CREATE - право на создание. Для базы данных это создание схем внутри ее. Для схем это создание новых объектов внутри ее. Для табличного пространства это создание таблиц, индексов и временных файлов внутри него;
REFERENCES - право на создание внешний ключей;
TRIGGER - право создания триггера на указанную таблицу;
CONNECT - право на соединение с указанной базой данных;
TEMPORARY, TEMP - право на создание временных таблиц;
EXECUTE - право на выполнение функций;
USAGE - право на использование указанного языка для написания функций. Для схемы это доступ к объектам внутри ее. Для последовательности это право использовать функции currval и nextval;
SELECT - право на выборку;
DELETE - право на удаление;
INSERT - право на вставку;
UPDATE - право на обновление.

-- все права на указанные базы данных
GRANT ALL PRIVILEGES
ON DATABASE dbname1, dbname2
TO username
WITH GRANT OPTION;

-- право на выбор из двух представлений
GRANT select
ON VIEW view_name1, view_name2
TO public;
права доступа Oracle
В Oracle команды назначения прав не так актуальны за счет готовых ролей и как указывалось ранее, каждый пользователь имеет полный доступ к одноименной схеме. В Oracle имя привилегии определяет и действие, и тип объектов.
Ключевое слово ANY указывает, что привилегия распространяется на все схемы, иначе только на текущую (из которой вызывается команда GRANT).

-- право на создание таблицы
GRANT create table TO username;

-- право на создание любых таблиц
GRANT create any table TO username;

-- право на создание триггера
GRANT create trigger TO username;

-- право на уничтожение любой последовательности
GRANT drop any sequence TO username;

-- право на изменение любой таблицы
GRANT alter any table TO username;

-- право на соединение к БД
GRANT create session TO username;
удаление прав доступа
Удаление прав делается командой REVOKE.

-- удаление права выборки из таблицы
-- tblname пользователю username
REVOKE select ON tblname FROM username;

-- PostgreSQL
REVOKE GRANT OPTION FOR ALL PRIVILEGES
ON DATABASE dbname1, dbname2
FROM username;

-- MySQL
REVOKE ALL PRIVILEGES, GRANT OPTION FROM username;
роли
Для удобства управления привилегиями используются роли. Им можно назначать права так же, как и пользователям или использовать вместо имени привилегии в командах GRANT и REVOKE.
роли PostgreSQL
В PostgreSQL роль и пользователь являются одним типом объекта. Различие в командах CREATE USER и CREATE ROLE, заключается в том, что в первом случае по умолчанию определяется имя с возможностью соединения (параметр LOGIN). Во втором случае соединение запрещено (параметр NOLOGIN). Группы пользователей, существовавшие в предыдущих версиях, теперь также слились с ролями. Ниже приведен список параметров определяющих роль:
SUPERUSER, NOSUPERUSER - определяют является ли новая роль суперпользователем - пользователем не имеющего ограничений внутри базы;
CREATEDB, NOCREATEDB - может ли новая роль создавать базы данных;
CREATEROLE, NOCREATEROLE - может ли новая роль создавать новые роли;
LOGIN, NOLOGIN - может ли новая роль использоваться для соединения к БД;
CONNECTION LIMIT n - если роли доступно соединение к БД, то n указывает предел этих соединений. По умолчанию значение -1, означающее отсутствие предела;
PASSWORD psw - определяет пароль для соединения;
ENCRYPTED, UNENCRYPTED - шифровать или нет пароль в системном каталоге;
VALID UNTIL 'временная отметка' - определяет действие пароля до указанной временной отметки;
IN ROLE lst - определяет список ролей lst, добавляемых к новой;
ROLE lst - определяет список ролей, которые станут членами новой группы;
ADMIN lst - аналогично предыдущему плюс указанные роли сами могут добавлять другие роли в эту группу.

-- создание новой роли
CREATE ROLE rolename WITH
SUPERUSER
CREATEDB
NOLOGIN;

-- добавление права роли
GRANT select ON tblname TO rolename;

-- добавление роли пользователю
GRANT rolename TO username;

-- удаляем все права на таблицу
-- нельзя уничтожить роль, если она используется
-- для доступа к объекту
REVOKE ALL PRIVILEGES ON tblname FROM rolename;

-- уничтожение роли
DROP ROLE IF EXISTS rolename;
роли Oracle
Синтаксис создания ролей в Oracle также схож с синтаксисом создания пользователей.

-- создание роли
CREATE ROLE rolename;

-- роль с паролем
CREATE ROLE rolename
IDENTIFIED BY pswd;

-- добавление права роли
GRANT select ON tblname TO rolename;

-- добавление роли пользователю
GRANT rolename TO username;

-- уничтожение роли
DROP ROLE rolename;
роли MySQL
MySQL пока не поддерживает ролей.
Файловый вывод/ввод
Большинство программ, работающих с БД, позволяют сохранять выборки в различных форматах, но не все позволяют сохранить в виде простого текстового файла. Например, была ситуация, когда в БД была необходимая информация о сотрудниках (более тысячи человек) для одной стандартной формы. А формат файла, описывающий эту форму, оказался текстовым. Поэтому проблема решилась выборкой в файл и вставкой его содержимого в файл формы. А соответствующий отдел был избавлен от лишней работы. Для конкретности примера, воспользуемся следующей таблицей с данными.

-- таблица цветов
create table colors(
id integer,
cname varchar(45),
constraint pk_colors primary key (id)
);

insert into colors values(1,'красный');
insert into colors values(2,'синий');
insert into colors values(3,'зеленый');
insert into colors values(4,'белый');
insert into colors values(5,'черный');
commit;
Oracle
В Oracle вывод в файл реализуется с помощью команды SQL plus spool. Она служит как для начала вывода в файл, так и для остановки. По умолчанию расширение файла lst.

-- открываем вывод в файл
spool c:\\myfile;

-- выборка
select t.id || ' ' || t.cname from colors t;

-- закрываем вывод в файл
spool off;
Так как это не SQL команда, то ее нельзя использовать внутри PL/SQL блока, но можно поместить блок между этими командами. По этой же причине, чтобы выполнить этот пример в PL/SQL developer, нужно открыть Command window.
На некоторых операционных системах поддерживается дополнительный параметр out, позволяющий сразу распечатать выборку на принтере установленном по умолчанию.

-- открываем вывод в файл
spool c:\myfile;

-- выборка
select t.id || ' ' || t.cname from colors t;

-- закрываем вывод файл и распечатываем
spool out;
MySQL
В MySQL данная задача реализована аналогично сохранению выборки в списке переменных в команде select.

select *
into outfile 'c:/myfile.lst' -- указываем файл
fields terminated by ',' -- разделитель полей
enclosed by '"' -- заключить каждое поле между "
lines terminated by '\\n' -- разделитель строки
from colors;
Если какой-либо разделитель не нужен, то его определение можно опустить. В отличие от Oracle здесь нельзя вывести более одной выборки в один файл.
Команда LOAD DATA INFILE является обратной и позволяет загрузить данные из файла в таблицу.
Например пусть есть файл myfile.lst, со следующим содержимым в обычной для Windows русской кодировке.
"50", "светло-коричневый"
"51", "темно-коричневый"
Тогда загрузить данные можно следующей командой.

load data infile 'c:/myfile.lst'
ignore -- пропускаем если данные уже в таблице
into table colors
character set cp1251 -- кодировка исходного файла
fields
terminated by ',' -- разделитель полей
enclosed by '"' -- поле заключено между "
lines terminated by '\\n'; -- разделитель строки
Если таблица colors использует другую кодировку, например utf-8, данные преобразуются корректно.
PostgreSQL
Команда copy в PostgreSQL позволяет, как сохранить выборку в файле, так загрузить данные из файла. Файловый источник должен существовать заранее, при записи автоматически он не создается. При повторном копировании содержимое файла перезаписывается. При чтении из файла в таблицу, кодировки строковых данных должны совпадать.

-- простой вывод данных в файл
copy (select * from colors) to 'c:/myfile.lst';

-- предположим, база настроена на кодировку utf-8,
-- тогда и строки сохранятся в файл в utf-8
copy
(
select '"50", "светло-коричневый"'
union
select '"51", "темно-коричневый"'
) to 'c:/myfile.lst';

-- а теперь загрузим данные в таблицу
copy colors (id, cname) from 'c:/myfile.lst'
with delimiter ',' -- разделитель полей
null 'null' -- чем заменять пустые значения
cvs -- CSV настройки
quote '"' -- поле заключено между "
escape '\\\\' ; -- ESC символ, для пропуска "
Информация о базе данных
В стандарте SQL определена особая схема information_schema, содержащая информацию о базе данных. Например, таблица tables хранит значения различных параметров всех таблиц, как их имена. Само число параметров зависит от СУБД.

-- PostgreSQL, MySQL
select * from information_schema.tables;
В старых версиях Oracle пока не реализована эта возможность. А использование системных таблиц или пакета dbms_metadata по ряду причин не так удобны с точки зрения разработки инструментов управления БД.
В MySQL имеется специальная команда для получения такого рода информации - show. В PostgreSQL тоже есть одноименная команда, но она служит для других целей.

-- список таблиц в схеме test
SHOW tables from test;

-- посмотреть SQL код создания таблицы tblname
SHOW CREATE TABLE tblname;

-- подобным образом просматриваются другие
-- типы объектов,
-- однако некоторые команды у меня не заработали,
-- как просмотр кода функции
SHOW FUNCTION CODE myfunc;









13 PAGE 14115



Заголовок 1 Заголовок 2 Заголовок 315

Приложенные файлы

  • doc 24737798
    Размер файла: 656 kB Загрузок: 0

Добавить комментарий