Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Тема 4: «Основы языка SQL. Команды управления данными – DDL. Команды манипулирования данными – DML»
Следующие команды создают или удаляют базы данных, таблицы или изменяют их структуру:
CREATE DATABASE создает новую базу данных;
DROP DATABASE удаляет базу данных и любые таблицы, которые она содержит;
CREATE TABLE создает новую таблицу; p
DROP TABLE удаляет таблицу и данные, которые она содержит;
ALTER TABLE изменяет структуру существующей таблицы;
CREATE INDEX добавляет индекс к таблице;
DROP INDEX удаляет индекс из таблицы.
Чтобы создать новую базу данных, используйте команду CREATE DATABASE. Следующая команда создает базу данных mydb:
CREATE DATABASE mydb;
После того, как база данных создана, можно создавать новые таблицы. Для этого используют команду CREATE TABLE. Мы ее рассмотрим чуть позже.
Если вы пробуете создать базу данных, а она уже существует, то произойдет ошибка. Чтобы этого не происходило, добавьте к команде IF NOT EXISTS:
CREATE DATABASE IF NOT EXISTS mydb;
В этом случае база данных будет создана, если нет одноименной. В противном случае, команда ничего не делает. Это может быть полезно в приложениях, которые должны гарантировать, что данная база существует.
Создавая базу данных вы не гарантированы, что сможет сразу с ней работать. Чтобы сделать новую базу данных активной, дайте команду USE:
USE mydb;
Чтобы увидеть список доступных баз данных, используйте команду SHOW DATABASES. Эта команда не будет показывать имена баз данных, к которым вы не имеете доступа. Чтобы увидеть список таблиц базы данных, используйте SHOW TABLES FROM db_name. Можно использовать команду SHOW TABLES, если db_name - имя текущей базы данных.
Если база данных вам больше не нужна, то ее можно удалить командой DROP DATABASE
DROP DATABASE mydb;
Не нужно уничтожать таблицы базы данных перед ее удалением. DROP DATABASE не требует, чтобы база данных была пустой. Эта команда будет выполнена, даже если база данных содержит таблицы. DROP DATABASE удаляет таблицы в процессе удаления базы данных.
DROP DATABASE - радикальная команда. Вы должны использовать ее продуманно. Нет никакой команды, чтобы "отменить" DROP DATABASE. Если вы удаляете базу данных по ошибке, то восстановить ее вы можете только из своих резервных копий.
Подробнее о данных командах – в электронном курсе.
К. Дейта трактует минимальный набор операций для построения языков манипуляции баз данных, как часть реляционной модели.
То, что язык манипуляции данными — это часть модели, ясно из следующих соображений. С одной стороны язык должен позволять извлекать, обрабатывать и возвращать данные в базу. С другой стороны язык должен учитывать структуру базы данных. Например, язык должен отражать тот факт, что данные о взаимосвязанных сущностях хранят в отдельных таблицах, т. е. для ответа на некоторые вопросы необходимо "соединять" данные разных таблиц.
Как определить минимальный набор операций для построения языка? Для ответа на этот вопрос обратим внимание на тот факт, что в качестве базовой структуры данных реляционной модели Эдгар Кодд выбрал отношение. Этот выбор был не случаен:
графически отношение можно представить таблицей, а именно так мы и привыкли представлять данные;
манипуляции данными отношений описаны математически, а значит предсказуемы.
Наша привычка работать с таблицами позволяет легко представить все возможные манипуляции их данными, а значит определить минимально необходимое число реляционных операций. Для разработчика приложений баз данных — это конечное число базовых команд языка SQL (Structured Query Language — структурированный язык запросов).
Сейчас вы познакомитесь с базовыми командами языка SQL. В теории баз данных это не единственный язык. Аналогичный язык разрабатывала компания IBM. Благодаря этим разработкам сейчас существуют средства визуального построения SQL команд.
Язык SQL — это программная реализация реляционной алгебры и реляционного исчисления, к авторам которых относят Кодда, Лакруа и Пиро. Достаточно подробно этот вопрос рассмотрен, например, в книге Дж. Ульмана "Основы систем баз данных". Она вышла в издательстве "Финансы и статистика" в 1983 г.
Реляционные операции делят на две группы: унарные и бинарные. Первые из них описывают манипуляции с одним отношением, а вторые — не менее, чем с двумя. К унарным относят операцию проекция и выборка. С их описания мы и начнем
Как мы уже отмечали SQL — это язык, который был математически описан (предсказан).
В качестве подтверждения данного высказывания все операции реляционного исчисления будем предварять их математическими формулировками.
Проекция может быть записана следующим образом:
Ее результат — это множество уникальных кортежей t ( k) отношения R с атрибутами u [ i 1], u[ i 2], ..., u [i k].
Другими словами, операция проекция позволяет отобразить вертикальное подмножество данных. То есть ту часть данных, например, отношения Lease(NLease, NTn, NOn, AdR, LDate):
|
1 |
2 |
1 |
Р-Корсакова 32/15 |
01.01.1997 |
|
2 |
3 |
2 |
Кирова 118/2 |
12.08.1998 |
|
3 |
1 |
3 |
Металлургов 56 |
14.05.1999 |
Рисунок 1 - Данные таблицы Lease
которая показывает, кто у кого арендует недвижимость (на рис.1 затенены).
Конечно, данный вид результата не столь очевиден, чтобы понять, что Иванов арендует у Ивашко, Петров у Иванова, а Сидоров у Петрова. Но придется потерпеть. Сейчас наша цель — познакомиться с языком манипуляции данными.
Примечание
Как видим результат исполнения SQL команды — множество записей (картежей). Поэтому Э. Кодд назвал теоретическую модель SQL как "реляционное исчисление с переменными-кортежами".
Выборка { t/R(t) Λ (t [ i] ϑ w)} это множество кортежей t, удовлетворяющих предикату t [ i] ϑ w.
Здесь ϑ — одна из бинарных операций, описанных ниже.
|
Оператор |
Описание |
|
= |
Равно |
|
<> |
Не равно |
|
> |
Больше |
|
< |
Меньше |
|
>= |
Больше или равно |
|
<= |
Меньше или равно |
Другими словами, операция выборка позволяет отобразить горизонтальное подмножество данных отношения (на рис.2 затенено).
|
Owner(NOn, Ow, AdO) |
||
|
1 |
Иванов |
Мира 36 |
|
2 |
Петров |
Правды 2/36 |
|
3 |
Ивашко |
Кирова 18 |
Рисунок 2 - Данные таблицы Owner
На языке SQL операция выборка в данном случае записывается так:p
SELECT Ow
FROM Owner
WHERE AdO = 'Мира 36'
Рассмотренные нами операции унарны, то есть применимы к одному отношению.
Далее мы рассмотрим операции, которые применимы к двум таблицам.
Пересечение отношений R и S
{ t/R ( t) Λ – ( R ( t) – S ( t))}
включает кортежи, которые принадлежат как R, так и S.p
Другими словами, пересечение — это одинаковые записи идентичных по структуре таблиц. Например, результат пересечения отношений (см. рис.3)
|
Tenant(NTn, Tn, AdT) |
|
Owner(NOn, Ow, AdO) |
||||
|
1 |
Иванов |
Мира 36 |
|
1 |
Иванов |
Мира 36 |
|
2 |
Петров |
Правды 2/36 |
|
2 |
Петров |
Правды 2/36 |
|
3 |
Сидоров |
Курская 18 |
|
3 |
Ивашко |
Кирова 18 |
Рисунок 3 - Данные отношений Tenant и Owner
представляет собой список владельцев, которые арендуют недвижимость, как показано на рис.4:
|
Иванов |
Мира 36 |
|
Петров |
Правды 2/36 |
Рисунок 4 - Результат пересечения таблицы Tenant и Owner
Разность отношений R и S, записываемая как
{ t/R ( t) Λ – S ( t)}
представляет собой множество кортежей t, которые принадлежат R, но не принадлежат S.
Например, по данным отношений Tenant и Owner можно определить, кто из владельцев не арендует недвижимость. Это — Сидоров.
Следующая SQL команда реализует операцию разность:
SELECT Tn, AdT
FROM Tenant
WHERE Tn NOT IN (SELECT Ow FROM Owner)
Объединение отношений R и S, выражаемое в исчислении как
{ t/R ( t) V S ( t)}
представляет собой множество кортежей t, которые принадлежат R и кортежи отношения S, которых нет в R.
Например, по данным отношений Tenant и Owner можно составить список клиентов риелтерской конторы, как показано на рис.5:
|
Иванов |
Мира 36 |
|
Ивашко |
Кирова 18 |
|
Петров |
Правды 2/36 |
|
Сидоров |
Курская 18 |
Рисунок 5 - Список клиентов риелтерской конторы
Декартово произведение отношений R и S, которые соответственно имеют арности r и s представляет собой совокупность кортежей длиной r+s таких, что
{t(r+s)/(∃u(r))(∃v(s))(R(u)LS(ν)Lt[1]=u[i1]Λ...Λt[r+s]=ν[ s])}
Примечание
Под арностью понимают количество атрибутов отношения.
В реляционной модели для манипуляции с отношениями используют различные виды соединений, которые являются подмножеством декартова произведения.
Операции соединения подразделяются на два вида — внутренние и внешние.
Так внутренние соединение отношений R и S арности r и s, соответственно
{t( r+s)/R(t)ΛS (t))}
представляет собой множество кортежей t длинны r+s, образуемых из кортежей отношений R и S, которые имеют одинаковые значения общих атрибутов.
Например, по данным отношений, приведенных на рис.6.
|
Rent(Type, Rn) |
|
|
Realty(AdR, Type) |
|
|
2-к. квартира |
90 |
|
Р-Корсакова 32/15 |
2-к. квартира |
|
1-к. квартира |
60 |
|
Металлургов 56 |
дом |
|
дом |
1800 |
|
Кирова 118/2 |
1-к. квартира |
Рисунок 6 - Данные отношений Realty и Rent
следующей SQL-командой стандарта ANSI-89
SELECT DISTINCT Rent.Rn, Realty.AdR
FROM Rent, Realty
WHERE Realty.Typ = Rent.Typ
можно просмотреть стоимость аренды недвижимости.
В стандарте ANSI-92 SQL-команда операции соединения содержат зарезервированное слово JOIN.
SELECT DISTINCT Rent.Rn, Realty.AdR
FROM Rent INNER JOIN Realty
ON Realty.Typ = Rent.Typ
|
90 |
Р-Корсакова 32/15 |
|
60 |
Металлургов 56 |
|
1800 |
Кирова 118/2 |
Рисунок 7 – Результат внутреннего соединения Rent и Realty
В этом разделе описаны функции, операторы, предикаты и приведены способы упорядочивания выборок.
DML команды могут использовать функции, которые позволяют анализировать извлеченную из базы данных информацию. Например, получение итоговых значений.
В соответствие со стандартом ANSI SQL поддерживает агрегатные функции. Их применяют для определения:
суммарного значения — SUM();
среднего арифметического всех определенных значений (отличных от NULL) — AVG();
минимального значения — MIN();
максимального значения — MAX();
числа значений, которые удовлетворяют некоторому критерию — COUNT();
числа значений, отличных от NULL — COUNT(*).
Следует отметить, что функции применяют к столбцам, а не самой таблице.
Например, вычислить количество типов объектов аренды
SELECT COUNT(*)
FROM Rent
Определить число объектов, стоимость аренды которых больше 60 у.e. можно так:
SELECT COUNT(Rn)
FROM Rent
WHERE Rn > 60
SQL также поддерживает строковые функции:
Например, удалить предшествующую цепочку, которая состоит из символов '2'
SELECT TRIM(LEADING '2' from Ow)
FROM owner
Например, вывести типы квартир
SELECT SUBSTRING(Typ FROM 1 FOR 4)
FROM Rent
WHERE SUBSTRING(Typ FROM 6) = 'квартира'
Данный запрос, используя функцию SUBSTRING(Typ FROM 1 FOR 4) позволяет отобразить первых 4 символа поля Typ, при условии, что все его символы в текущей записи (с 6-го до последнего) — 'квартира'.
SQL позволяет извлекать дату и время, используя функцию работы с датами:
EXTRACT (<данное> FROM <имяПоля>)
Например, необходимо извлечь значение года, которое хранится в поле LDate:
SELECT EXTRACT(YEAR FROM LDate)
FROM Lease
Можно также извлекать месяц (MONTH), день (DAY), час (HOUR), минуты (MINUTE) и секунды (SECOND).
Значения дат можно сравнивать друг с другом, сравнивать с относительными датами, вычитать одну дату из другой. Например, получить номера договоров аренды заключенных за 2 последних года
SELECT NLease
FROM Lease
WHERE '1999.12.31' — LDate < 365 * 2 + 1
Отметим, что допускаются следующие форматы представления даты: '1999-OCT-27', '02/27/10' и '01.10.27'. Например, получить номера договоров аренды заключенных после 1 января 2003 года:p
SELECT NLease, LDate
FROM Lease
WHERE LDate > '2001-JUN-01'
Язык SQL кроме функций поддерживает арифметические операторы
|
Оператор |
Описание |
|
+ |
Сложение |
|
- |
Вычитание |
|
∗ |
Умножение |
|
/ |
Деление |
Например, с помощью умножения можно определить, какой будет арендная плата, если ее увеличить на 20%
SELECT 1.2*Rn AS UpRenta
FROM Rent
Квалификатор AS присваивает название столбцу результата.
Порядок вычисления выражений подчиняется общепринятым правилам: сначала выполняется умножение и деление, а затем — сложение и вычитание. Операции одного уровня выполняются слева направо. Разрешено применять скобки для изменения порядка вычислений.
Язык SQL поддерживает операторы сравнения.
|
Оператор |
Описание |
|
= |
Равно |
|
<> |
Не равно |
|
> |
Больше |
|
< |
Меньше |
|
>= |
Больше или равно |
|
<= |
Меньше или равно |
|
IS NULL |
Не определено |
|
LIKE |
Подобно |
Элементами сравнения могут быть:
Примечание
Элементы сравнения должны быть сопоставимых типов.
Продемонстрируем применение оператора "не равно" на примере получения списка недвижимости, в который не входят дома:
SELECT AdR
FROM Realty
WHERE Typ <> "дом"
Поясним оператор IS NULL.
Предположим, что типы объектов аренды уже известны и введены в таблицу, а арендная плата за некоторые из них еще ни разу не назначалась. Такую ситуацию в таблице регистрируют как NULL — пустое, не определенное значение.
Допустим, необходимо получить список объектов аренды, плата для которых еще не назначена. Для этого можно использовать следующую команду:
SELECT Typ
FROM Rent
WHERE Rn IS NULL
Поясним оператор LIKE.
Его используют только с символьными данными. Он проверяет, соответствует ли текущее значение поля строке с указанной маской. В качестве маски используются все разрешенные символы (с учетом строчных и прописных букв), а также специальные символы: % (знак процента), который замещает любое количество символов (в том числе и 0), а также _ (символ подчеркивания), замещающий только один символ.
Например, получить список адресов, где арендуют квартиры
SELECT AdR, Typ
FROM Realty
WHERE Typ LIKE "%кварт%"
Если требуется найти строку, которая содержит специальные символы (%, _), то:
с помощью ключевого слова ESCAPE определите так называемый escape-символ;
поместите его перед специальным символом, чтобы указать, что искомый символ следует а escape-символом.
Escape-символом может быть любой, никогда не появляющийся в упоминаемом столбце как информационный символ. Часто для этих целей используются символы @ и ~.
Например, получить список адресов, которые содержат _ (знак подчеркивания)
SELECT AdR, Typ
FROM Realty
WHERE Typ LIKE "%@_%" ESCAPE "@"
К логическим операторам относят AND, OR и NOT.
Ниже описаны условия, когда значения предикатов — "истина", при условии, что они содержат следующие операторы:
|
Оператор |
Условие истинности значения предиката |
|
AND |
Связываемые этим оператором условия истинны |
|
OR |
Хотя бы одно из связываемых этим оператором условий истинно |
|
NOT |
Условие, перед которым стоит этот оператор ложно |
В одном предикате логические операторы выполняются в следующем порядке: сначала выполняется оператор NOT, затем AND и только после этого оператор OR. Для изменения порядка выполнения операторов используют скобки.
Например, необходимо получить список квартир, арендная плата за которые не превышает 100 у.е.:
SELECT Typ, Rn
FROM Rent
WHERE (Typ LIKE "%кварт%") AND (Rn <= 100)
Если вам необходимо выбрать записи, значения полей которых принадлежат некоторому диапазону, то используйте предикатBETWEEN. Например, необходимо просмотреть список квартир, арендная плата которых находится в диапазоне от 50 до 100 у.е.:
SELECT Typ
FROM Rent
WHERE Rn BETWEEN 50 AND 100
Если вам необходимо получить список владельцев, который начинается с фамилии "Иванов", а остальные следуют вплоть до "Клименко", то этот запрос будет выглядеть так:
SELECT Ow
FROM Owner
WHERE Ow BETWEEN "Иванов" AND "Клименко"
Предикат BETWEEN с отрицанием NOT позволяет получить выборку записей, значения полей которых находятся вне границ. Например, получить список договоров аренды, которые были заключены до 1.08.01 или после 1.03.02:
SELECT NLease
FROM Lease
WHERE LDate NOT BETWEEN "01.08.01" AND "02.03.01"
Для работы со списками используют предикат IN. Он позволяет проверять совпадения значения выражения, с каким либо элементом списка. Например, для просмотра адресов, где проживают арендаторы "Петров" и "Сидоров" воспользуйтесь командой:
SELECT AdT
FROM Tenant
WHERE Tn IN ("Петров", "Сидоров"))
Для просмотра адресов недвижимости, где проживают остальные арендаторы, можно использовать предикат IN с частицей NOT:
SELECT AdT
FROM Tenant
WHERE Tn NOT IN ("Петров", "Сидоров"))
Часто в выборке необходимо изменять порядок следования строк. Иногда отобранные данные необходимо сгруппировать, например, по цехам, а иногда сама выборка является промежуточным результатом перед получением интересующих данных.
3.3.1 Изменение порядка строк выборки
Порядок строк выборки изменяют конструкцией:
ORDER BY <порядок_строк> [ASC/DESC]p
Способ упорядочивания определяют зарезервированные слова ASC — "по возрастанию" и DESC — "по убыванию". Способом по умолчанию является ASC.
Порядок строк выборки может задаваться либо именами столбцов, либо их номерами.
Упорядочивание с использованием имен столбцов. Например, получить список арендаторов, который упорядочен по фамилиям в алфавитном порядке:
SELECT Tn
FROM Tenant
ORDER BY Tn
Упорядочивание с использованием номеров столбцов. Например, получить список арендаторов, упорядоченный по адресам арендуемых объектов:
SELECT Tn, AdT
FROM Tenant
ORDER BY 2
Допускается использование нескольких уровней вложенности при упорядочивании выводимой информации. При этом разрешается смешивать оба способа. Например, если стоимость аренды разных объектов одного типа различна, то получить список объектов недвижимости, упорядоченный сначала по типу объектов, а среди них — по убыванию арендной платы можно так
SELECT Typ, Rn
FROM Rent
ORDER BY Typ, 2 DESC
3.3.2 Группировка выборки
Конструкция GROUP BY инициирует группировку строк указанных FROM таблиц по указанному перечню столбцов. Далее к каждой группе применяется команда SELECT так, что данные каждой группы принимают единственное значение, например, результат арифметического выражения или одной из агрегатных SQL-функций.
Например, определить суммарную годовую стоимость аренды объектов Ивановым можно следующей SQL командой:
SELECT Tenant.Tn, (SUM(12*Rent.Rn)) AS Total
FROM Realty, Lease, Tenant, Rent
WHERE (Tenant.Tn = 'Иванов')
AND (Lease.NTn = Tenant.NTn)
AND (Realty.AdR = Lease.AdR)
AND (Rent.Typ = Realty.Typ)
GROUP BY Tenant.Tn
3.3.3 Выборки из выборок
Предложение HAVING определяет критерии, используемые для исключения групп из выборки, точно также как предложение WHERE делает это для строк.
Например, если необходимо определить максимальная стоимость, каких арендуемых объектов превышает 100 у.е., то следует воспользоваться следующей SQL командой
SELECT Tenant.Tn, (MAX(Rn)) AS Renta
FROM Realty, Lease, Tenant, Rent
WHERE (Tenant.NTn = Lease.NTn)
AND (Lease.AdR = Realty.AdR)
AND (Realty.Typ = Rent.Typ)
GROUP BY Tenant.Tn
HAVING MAX(Rn) > 100