Методические указания по дисциплине “Базы данных и субд”

Введение

Язык SQL де-факто является стандартным языком известных реляционных СУБД, о чем также свидетельствует тот факт, что множество альтернативных подходов (документо-ориентированные, графовые и др.) объединяются сегодня теоретиками БД в рамки общей концепции NoSQL (англ. not only SQL), набирающей все большую популярность. С другой стороны, реляционные БД по-прежнему являются наиболее распространенными, а применение альтернативных подходов возможно лишь в достаточно специфичных областях приложения, в то время, как эффективность реляционной модели данных доказана временем и практически не зависит от области применения [1].

Язык SQL традиционно делится на несколько частей, объединяющих группы операторов по их назначению. В данном пособии рассматриваются язык определения данных – ЯОД (англ. Data definition language – DDL) и язык манипулирования данными – ЯМД (англ. Data manipulation language – DML) [2].

Язык определения данных (DDL)

Операторы данной группы языка SQL используются с целью определения схемы данных в реляционной базе данных: создание таблиц, представлений, последовательностей, хранимых процедур, триггеров и других сущностей, реализуемых конкретной СУБД, а также их модификация и удаление. Также при определении таблиц возможно определить поддерживаемые ограничения целостности [1,2]: уникальность, диапазон значений, первичный ключ, внешний ключ и др.

Перечислим некоторые операторы языка DDL:

• 
  [ CREATE | DROP ] TABLE – создание/удаление таблицы;
  
• 
  [ CREATE | DROP ] INDEX – создание/удаление индекса;
  
• 
  [ CREATE | DROP ] VIEW – создание / удаление представления;
  
• 
  ALTER TABLE – изменение структуры таблицы.
  

Язык манипулирования данными (DML)

Операторы подъязыка манипулирования данными предназначены для модификации постоянных данных в реляционной СУБД. К операторам данной группы традиционно относят следующие [2]:

• 
  INSERT – вставка данных в таблицу БД;
  
• 
  UPDATE – обновление данных в таблице БД;
  
• 
  DELETE – удаление данных в таблице БД.
  

Операторы UPDATE или DELETE могут за один свой вызов модифицировать сразу любое число записей, содержащихся в таблице. Множество этих записей задается предикатом, который указывается в секции WHERE. Оператор INSERT может быть использован как для вставки одной записи в таблицу, так и для вставки сразу нескольких записей, которые (например) являются результатами выполнения запроса SELECT (примеры для конкретных СУБД приведены ниже).

Примеры использования в различных СУБД

MySQL

Синтаксис оператора создания таблицы в РСУБД MySQL следующий:

CREATE TABLE table_name

(

column_name1 type [modifiers]

[, column_name2 type [modifiers]]

)

В качестве имен таблиц и полей в MySQL допускается использовать любые символы из установленного в системе локального набора. Если в качестве идентификатора таблицы или ее поля требуется указать имя, совпадающее по написанию с ключевым словом или оператором языка, то его требуется обрамить символом «`».

Для удаления таблицы достаточно написать:

DROP TABLE [IF EXISTS] table_name;

Необязательная часть (IF EXISTS) в данном случае используется, если заранее не известно существует ли удаляемая таблица в БД (при этом, если таблицы не существует, то не произойдет ошибки выполнения запроса).

В MySQL допустимо использовать следующие типы данных: INT, REAL, DATE, TEXT(длина), TIME, CHAR(длина), VARCHAR(длина), LONGTEXT(длина), CHAR BINARY, BLOB, и др. В MySQL также поддерживаются поля типа перечисление (ENUM) и множество (SET).

СУБД MySQL поддерживает стандартный синтаксис оператора вставки данных (INSERT):

INSERT INTO table_name (column1, column2, … columnN)

VALUES (value1, value2, … valueN) [,(value1, value2, … valueN)]

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

Запросы на изменение данных (UPDATE) подчинены следующему синтаксису:

UPDATE table_name

SET column1=value1, column2=value2, ..., columnN=valueN

[WHERE clause]

Условный предикат (clause) является необязательным и определяет то, какие из имеющихся в таблице table_name записей должны быть обновлены, т.е. в них значение поля column1 будет установлено в value1, значение поля column2 будет установлено в value2 и т.д.

Удаление данных (DELETE) реализуется следующим образом:

DELETE FROM table_name [WHERE clause]

В заключение рассмотрим небольшой скрипт, демонстрирующий возможности языка SQL в СУБД MySQL (проверено для версии сервера 5.1.40).

# удаление таблиц, если они существуют

DROP TABLE IF EXISTS `select`,test, test2;

# создание таблицы с именем select

CREATE TABLE `select`

(

`group` int(11) NOT NULL DEFAULT '0', # значение по-умолчанию 0

`insert` double,

PRIMARY KEY (`group`) # ограничение первичный ключ

);

# создание таблицы с использованием оператора SELECT

CREATE TABLE test2 AS

SELECT * FROM `select`;

# переименование столбца и изменение его типа

ALTER TABLE test2 CHANGE `group` id INT(20);

# определение первичного ключа для таблицы test2

ALTER TABLE test2 ADD PRIMARY KEY(id);

# переименование таблицы select

ALTER TABLE `select` RENAME test;

# добавление ограничения уникальности для поля insert

ALTER TABLE test ADD UNIQUE uinsert (`insert`);

# определение значения по-умолчанию

ALTER TABLE test ALTER COLUMN `insert` SET DEFAULT 0.123;

# вставка 3х строк в таблицу test2

INSERT INTO test2 VALUES (1,0.1), (2,0.2), (3,0.3);

# удаление первичного ключа в таблице test

ALTER TABLE test DROP PRIMARY KEY;

# добавление поля с авто-увеличением

ALTER TABLE test

ADD COLUMN i1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

# вставка данных в таблицу test с частичным указанием

# значений строки

INSERT INTO test(`group`) VALUES (0);

# вставка данных в таблицу test из запроса SELECT

INSERT INTO test(`insert`,`group`)

SELECT `insert`, id FROM test2;

# увеличение значений поля insert для всех строк

# в таблице test на 1

UPDATE test SET `insert` = `insert` + 1;

# удаление строк из таблицы test,

# в которых значение поля group < 2

DELETE FROM test WHERE `group` < 2;

# создание поля с типом перечисление из трех значений

ALTER TABLE test ADD COLUMN e1 ENUM('a','b','c');

# установка значения поля e1 для всех записей

UPDATE test SET e1 = 'c';

# следующая команда установит значения поля e1 для всех

# строк таблицы в null, так как ‘d’ не определено среди

# допустимых значений последовательности

UPDATE test SET e1 = 'd';
PostgreSQL

ОРСУБД PostgreSQL предоставляет похожие возможности в части использования операторов языка SQL. Рассмотрим некоторые специфичные особенности данной СУБД [3]:

• 
  осуществляется преобразование идентификаторов к нижнему регистру, тогда как в большинстве СУБД в соответствии со стандартом SQL92 происходит преобразование к верхнему регистру;
  
• 
  таблицы внутри базы данных делятся по схемам, поэтому для обращения к таблицам (за исключением тех, которые расположены в «схеме по-умолчанию») требуется указывать имя схемы: public.test (схема public, таблица test);
  
• 
  используется обрамление защищенных идентификаторов символом «”» в целях отмены обязательного преобразования имен к нижнему регистру, а также для обеспечения возможности использования в качестве идентификаторов зарезервированных слов;
  
• 
  имеется возможность создания собственных типов данных командой CREATE TYPE;
  
• 
  имеется возможность создания временных таблиц (TEMPORARY), которые автоматические уничтожаются в конце текущего сеанса;
  
• 
  имеется возможность наследования структуры одной или нескольких базовых таблиц (ключевое слово INHERITS);
  
• 
  имеется возможность обновления данных из нескольких источников, за счет добавления секции FROM оператору UPDATE. Применение секции FROM означает, что перед обновлением таблица будет объединена (JOIN) с таблицей (таблицами), указанной в секции FROM, после чего будет запущенно обновление записей в таблице с именем table_name в соответствии с определенными в секции SET правилами:
  

UPDATE table_name [ [ AS ] alias ]

SET { column = { expression | DEFAULT } |

( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

[ FROM from_list ] [ WHERE condition ]

• 
  существует секция RETURNING в запросах, модифицирующих данные (UPDATE, INSERT), что позволяет выполнять одним запросом модификацию и чтение результатов (полезно в случае использования полей типа serial при чтении сгенерированных СУБД значений);
  
• 
  наличие возможностей создания агрегатных функций, доменов и типов данных, ролей, правил, триггеров, операторов, пользователей, хранимых процедур и т.д., а также правил преобразования типов и перекодирования символов в текстовых данных.
  

Пример скрипта, демонстрирующего возможности языка SQL в СУБД PostgreSQL (версия 9.1), представлен ниже. Как видно из примера, если использование операторов подъязыка DML практически не отличается от рассмотренного ранее для MySQL, то использование операторов языка DDL отличается от рассмотренного ранее. Имя схемы нигде не указывается, поэтому таблицы создаются внутри схемы public (если конфигурацией не определена другая схема).

-- удаление таблиц, если они существуют

DROP TABLE IF EXISTS "select", test, test2;

-- создание таблицы с именем select

CREATE TABLE "select"

(

"group" numeric(11,0) NOT NULL DEFAULT '0', -- значение по-умолчанию 0

"insert" real,

CONSTRAINT select_pkey PRIMARY KEY ("group") -- ограничение первичный ключ

);

-- создание таблицы с использованием оператора SELECT

CREATE TABLE test2 AS

SELECT * FROM "select";

-- переименование столбца и изменение его типа

ALTER TABLE test2 RENAME COLUMN "group" TO id;

ALTER TABLE test2 ALTER COLUMN id TYPE int;

-- определение первичного ключа для таблицы test2

ALTER TABLE test2 ADD PRIMARY KEY(id);

-- переименование таблицы select

ALTER TABLE "select" RENAME TO test;

-- добавление ограничения уникальности для поля insert

ALTER TABLE test ADD CONSTRAINT uinsert UNIQUE ("insert");

-- определение значения по-умолчанию

ALTER TABLE test ALTER COLUMN "insert" SET DEFAULT 0.123;

-- вставка 3х строк в таблицу test2

INSERT INTO test2 VALUES (1,0.1), (2,0.2), (3,0.3);

-- удаление первичного ключа в таблице test

ALTER TABLE test DROP CONSTRAINT select_pkey;

-- добавление поля с авто-увеличением

ALTER TABLE test

ADD COLUMN i1 serial NOT NULL PRIMARY KEY;

-- вставка данных в таблицу test с частичным указанием

-- значений строки

INSERT INTO test("group") VALUES (0) RETURNING *;

-- вставка данных в таблицу test из запроса SELECT

INSERT INTO test("insert","group")

SELECT "insert", id FROM test2;

-- увеличение значений поля insert для всех строк

-- в таблице test на 1

UPDATE test SET "insert" = "insert" + 1;

-- удаление строк из таблицы test,

-- в которых значение поля group < 2

DELETE FROM test WHERE "group" < 2;

-- создание поля с типом перечисление из трех значений

DROP TYPE IF EXISTS etype;

CREATE TYPE etype AS ENUM('a','b','c');

ALTER TABLE test ADD COLUMN e1 etype;

-- установка значения поля e1 для всех записей

UPDATE test SET e1 = 'c' RETURNING *;

-- следующая команда вызовет ошибку, так как

-- ‘d’ не определено среди допустимых значений

-- созданного ранее типа etype

UPDATE test SET e1 = 'd';
Задание для самостоятельного выполнения

Задание включает в себя выполнение следующих этапов для выбранной СУБД:

1. 
   Реализовать скрипт создания базы данных с использованием инструкций части DDL языка SQL;
   
2. 
   При создании таблиц не забывать указывать необходимые ограничения целостности и значения по умолчанию;
   
3. 
   Проинициировать таблицы созданной базы данных, выполняющие роль справочников, данными, с использованием инструкций части DML языка SQL;
   
4. 
   Полученные инструкции (п.1-3) объединить в файл-скрипт и представить преподавателю для защиты.
   

Список литературы

1. 
   К. Дж. Дейт. Введение в системы баз данных. 8-е издание. – М:Вильямс, 2006. – 847 с.
   
2. 
   Подвальный С.Л., Сергеева Т.И., Гранков М.В. Базы данных: модели данных, SQL, проектирование: учеб. пособие. – Ростов-на-Дону: Издательский центр ДГТУ, 2007. – 320 с.
   
3. 
   John C. Worsley and Joshua D. Drake. PostgreSQL для профессионалов. – СПб: Питер, 2003. – 498 с.