Преподаватель который помогает студентам и школьникам в учёбе.

Основы языка SQL

Содержание:

Введение.

Курсовая работа представляет собой теорию по основам программирования в языке запросов SQL. В ней будут рассмотрена взаимосвязь между SQL реляционной базой данных. А также рассмотрим базовые операторы. Знакомство с языком SQL не может быть без познания запросов тут будет рассмотрены, как и базовые запросы с простейшими операторами так и функции.

1. Реляционная модель данных.

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

Реляционная модель данных - это способ рассмотрения данных, то есть предписание для способа представления данных (посредством таблиц) и для способа работы с таким представлением (посредством операторов). Она связана с тремя аспектами данных: структурой (объекты), целостностью и обработкой данных (операторы).

Структура данных в реляционной модели данных

Реляционная модель данных предусматривает структуру данных, обязательными объектами которой являются:

отношение;
атрибут;
домен;
кортеж;
степень;
кардинальность;
первичный ключ.

Отношение - это плоская (двумерная) таблица, состоящая из столбцов и строк

Атрибут - это поименованный столбец отношения.

Домен - это набор допустимых значений для одного или нескольких атрибутов.

Кортеж - это строка отношения.

Степень определяется количеством атрибутов, которое оно содержит

Кардинальность - это количество кортежей, которое содержит отношение.

Первичный ключ - это уникальный идентификатор для таблицы.

Соответствие между формальными терминами реляционной модели данных и неформальными:

отношение (формальный термин) - таблица (неформальный термин);
атрибут - столбец;
кортеж - строка или запись;
степень - количество столбцов;
кардинальное число - количество строк;
первичный ключ - уникальный идентификатор;
домен - общая совокупность допустимых значений.

Свойства отношений:

уникальное имя отношения;
уникальное имя атрибута;
нет одинаковых кортежей;
кортежи не упорядочены сверху вниз;
атрибуты не упорядочены слева направо;
все значения атрибутов атомарные (нормализованное отношение).

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

Виды отношений:

именованное отношение;
базовое отношение;
производное отношение;
выражаемое отношение;
представление (view);
снимки (snapshot);
результат запроса;
промежуточный результат.

Очень важно знать о первичных ключах. Очевидно, что каждая база данных должна работать так, чтобы можно было обратиться к произвольному значению, хранящемуся в любой из ее таблиц. Но, поскольку все значения размещаются в таблицах, точнее – на пересечениях строк и столбцов этих таблиц, очевидно и то, что местоположение любого значения однозначно определяется, если известны конкретные таблица, столбец и строка. Не менее очевидно, что любые таблицу и столбец можно однозначно идентифицировать по их уникальным номерам. Однако строки уникальных имен не имеют. Тем не менее нам нужен механизм их однозначной идентификации. Такой механизм в реляционной модели существует и называется первичный ключ (Primary key). Поскольку, как мы видим, первичный ключ – это некая функция на строках произвольной таблицы, ее удобно связать со столбцами этой таблицы, которые тоже принимают свои значения на строках, то есть являются их функциями. Поэтому первичный ключ надо представлять себе как столбец или, в крайнем случае, набор столбцов особого рода.

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

Типы данных:

В языке SQL для определения домена служит понятие типа данных. Итак, тип данных имеет следующие характеристики:

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

Основные типы данных:

Строковые, или текстовые, типы данных (Character string) используются для представления текста.
Битовые типы данных (Bit String) служат для того, чтобы представлять двоичные числа.
Точные числовые типы данных (Exact numeric) применяются для представления точных числовых значений.
Действительные числовые типы данных (Approximate numeric), или числа с плавающей точкой, соответствующие приближенным действительным числам, применяют для того, чтобы хранить приближенные числовые значения.
Календарные типы данных. Для отображения даты и времени суток следует применять календарные типы данных (Datetime).
Интервальные типы данных следует применять для того, чтобы обозначать расстояние между датами или двумя отметками времени суток.

Чтобы как-то отобразить отсутствующие или неизвестные данные, вы можете воспользоваться значением null. Специальное значение NULL означает отсутствие данных, констатацию того факта, что значение неизвестно. По умолчанию это значение могут принимать столбцы и переменные любых типов, если только на них не наложено ограничение NOT NULL. Также, СУБД автоматически добавляет ограничение NOT NULL к столбцам, включенным в первичный ключ таблицы.
Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: =, <, >, like… Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий не ограничивается состояниями ИСТИНА и ЛОЖЬ. Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО.

После того как мы рассмотрели то как структурируются данные с которыми работает SQL мы можем прейти к основам этого языка программирования.

2. Оператор CREATE TABLE.

Создав новую БД, сообщим MySQL, что теперь мы собираемся работать именно с ней. Выбор активной БД выполняется командой: USE <имя базы>;
Переведём это описание на язык SQL:

CREATE TABLE weather_log (

id INT AUTO_INCREMENT PRIMARY KEY,

city_id INT,

day DATE,

temperature INT,

cloud TINYINT DEFAULT 0

);

Чтобы ввести многострочную команду в командной строке используйте символ \ в конце каждой строки (кроме последней).

Теперь создадим таблицу:

CREATE TABLE cities (

id INT AUTO_INCREMENT PRIMARY KEY,

name CHAR(128)

)

Первичный ключ

Первичный ключ — это особенное поле, в котором сохраняется уникальный идентификатор записи. Он нужен, чтобы у программиста и базы данных всегда была возможность однозначно обратиться к одной конкретной записи для её чтения, обновления или удаления.
Если назначить поле первичным ключом, то БД будет следить за тем, чтобы значение в этом поле больше не повторялось в таблице.
А если ещё и добавить аттрибут AUTO_INCREMENT, то MySQL при добавлении новых записей будет заполнять это поле сама. AUTO_INCREMENT будет играть роль счётчика — каждая новая запись в таблице получит значение на единицу больше максимального существующего значения.

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

3. SQL-запросы.

Запросы это основа программирования на SQL

Общая структура sql-запроса выглядит так.

SELECT (столбцы или * для выбора всех столбцов(обязательно))

FROM (таблица(обязательно))

WHERE (условие/фильтрация(необязательно))

GROUP BY (столбец, по которому хотим сортировать данные(необязательно))

HAVING (условие/фильтрация на уровне сгруппированных данных(необязательно))

ORDER BY (столбец, по которому хотим отсортировать вывод(необязательно)

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

Выбрать все (обозначается как *) из таблицы Table:

SELECT * FROM Table

Выбрать столбцы TableID, TableName из таблицы Table:

SELECT TableID, TableName FROM Table

WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам. До сих пор в результат выполнения каждого запроса включались все строки, какие только есть в соответствующей таблице. Если не все из них нужны, можно применить предложение WHERE и от фильтровать лишнее из результата. Необходимо знать, что именно способность фильтровать результаты характеризует команду SELECT как очень мощную. В предложении WHERE вам следует указать какое-нибудь условие отбора, включающее одно или несколько таких условий, которым должны удовлетворять строки произвольной таблицы, чтобы быть пропущенными через фильтр. Здесь под условием поиска, или предикатом, как его еще называют, понимается логическое выражение, которое может принимать три значения, а именно: «истина», «ложь» и «неизвестно». При этом существенно, что значение «неизвестно» появилось в этой схеме благодаря значению null. Фильтрование предложением WHERE осуществляется таким образом, что те и только те строки, для которых заданное условие принимает значение истины, включаются в результат исполнения запроса, а все остальные строки из этого результата исключаются. SQL предоставляет в распоряжение программиста целый ряд операторов, где под произвольным оператором понимается набор символов или ключевое слово, определяющие конкретные действия, которые необходимо совершить над некими значениями или какими-либо другими элементами.

Фильтрация по одному условию и одному значению:

SELECT * FROM Table

WHERE City = Rome

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

SELECT * FROM Table

WHERE City IN (Moscow, Paris)

Или

SELECT * FROM Table

WHERE City NOT IN (London,Bern)

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

SELECT * FROM Table

WHERE Country = Russia AND City NOT IN (Pscov, Tula) AND TableID > 15

SELECT * FROM Table

WHERE City IN ( London , Berlin) OR TableID > 4

GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).

При использовании GROUP BY обязательно:

перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.

Группировка количества клиентов по городу:

SELECT City, COUNT(TableID) FROM Table

GROUP BY City

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:

SELECT ProductID, COUNT(OrderID), SUM(Quantity) FROM OrderDetails GROUP BY ProductID

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

SELECT City, COUNT(CustomerID) AS Number_of_clients FROM Customers GROUP BY City

HAVING — необязательный элемент запроса, который отвечает за фильтрацию на уровне сгруппированных данных (по сути, WHERE, но только на уровень выше).
Фильтрация агрегированной таблицы с количеством клиентов по городам, в данном случае оставляем в выгрузке только те города, в которых не менее 5 клиентов:

SELECT City, COUNT(TableID) FROM Table GROUP BY City HAVING COUNT(TableID) >= 5

В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:

SELECT City, COUNT(TAbleID) AS number_of_clients FROM Table

GROUP BY City

HAVING number_of_clients >= 5

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

SELECT City, COUNT(TableID) AS number_of_clients FROM Table WHERE CustomerName NOT IN (Stavropol,Tula)

GROUP BY City

HAVING number_of_clients >= 5

ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы. При выполнении запроса СУБД выдает строки в случайном порядке, так как реляционная модель определяет, что порядок строк не имеет никакого значения для табличных операций. Зато мы сами можете расположить строки с использованием предложения ORDER BY и отсортировать их по какому-нибудь столбцу или группе столбцов либо в восходящем (от самого нижнего к самому верхнему) либо в нисходящем (от самого верхнего к самому нижнему) порядке. О значении «верха и низа» подробнее говорится в тексте врезки этого раздела. Предложение ORDER BY всегда является последним предложением команды SELECT.

Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:

SELECT * FROM Tables ORDER BY City

По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:

SELECT * FROM Tables ORDER BY TableID DESC

JOIN — необязательный элемент, используется для объединения таблиц по ключу, который присутствует в обеих таблицах. Перед ключом ставится оператор ON.

Запрос, в котором соединяем таблицы Order и Tables по ключу TableID, при этом перед названиям столбца ключа добавляется название таблицы через точку:

SELECT * FROM Orders

JOIN Tables ON Orders.TableID = Tables.TableID

Может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой:

SELECT * FROM Orders

JOIN Tables on Orders.TableID = Tables.TableID

WHERE Tables.TableID >10

4. Функции

SQL-функции выполняют произвольный список операторов SQL и возвращают результат последнего запроса в списке. В простом случае (не с множеством) будет возвращена первая строка результата последнего запроса. (Помните, что понятие «первая строка» в наборе результатов с несколькими строками определено точно, только если присутствует ORDER BY.) Если последний запрос вообще не вернёт строки, будет возвращено значение NULL.

Кроме того, можно объявить SQL-функцию как возвращающую множество (то есть, несколько строк), указав в качестве возвращаемого типа функции SETOF некий_тип, либо объявив её с указанием RETURNS TABLE(столбцы). В этом случае будут возвращены все строки результата последнего запроса. Подробнее это описывается ниже.

Тело SQL-функции должно представлять собой список SQL-операторов, разделённых точкой с запятой. Точка с запятой после последнего оператора может отсутствовать. Если только функция не объявлена как возвращающая void, последним оператором должен быть SELECT, либо INSERT, UPDATE или DELETE с предложением RETURNING.

Любой набор команд на языке SQL можно скомпоновать вместе и обозначить как функцию. Помимо запросов SELECT, эти команды могут включать запросы, изменяющие данные (INSERT, UPDATE и DELETE), а также другие SQL-команды. (В SQL-функциях нельзя использовать команды управления транзакциями, например COMMIT, SAVEPOINT, и некоторые вспомогательные команды, в частности VACUUM.) Однако последней командой должна быть SELECT или команда с предложением RETURNING, возвращающая результат с типом возврата функции. Если же вы хотите определить функцию SQL, выполняющую действия, но не возвращающую полезное значение, вы можете объявить её как возвращающую тип void.

Аргументы SQL-функций

К аргументам SQL-функции можно обращаться в теле функции по именам или номерам. Ниже приведены примеры обоих вариантов.

Чтобы использовать имя, объявите аргумент функции как именованный, а затем просто пишите это имя в теле функции. Если имя аргумента совпадает с именем какого-либо столбца в текущей SQL-команде внутри функции, имя столбца будет иметь приоритет. Чтобы всё же перекрыть имя столбца, дополните имя аргумента именем самой функции, то есть запишите его в виде имя_функции.имя_аргумента. (Если и это имя будет конфликтовать с полным именем столбца, снова выиграет имя столбца. Неоднозначности в этом случае вы можете избежать, выбрав другой псевдоним для таблицы в SQL-команде.)

Старый подход с нумерацией позволяет обращаться к аргументам, применяя запись $n: $1 обозначает первый аргумент, $2 — второй и т. д. Это будет работать и в том случае, если данному аргументу назначено имя.

Если аргумент имеет составной тип, то для обращения к его атрибутам можно использовать запись с точкой, например: аргумент.поле или $1.поле. И опять же, при этом может потребоваться дополнить имя аргумента именем функции, чтобы сделать имя аргумента однозначным.

Аргументы SQL-функции могут использоваться только как значения данных, но не как идентификаторы. Например, это приемлемо:

INSERT INTO mytable VALUES ($1);

Функции SQL с базовыми типами.

Простейшая возможная функция SQL не имеет аргументов и просто возвращает базовый тип, например integer:

CREATE FUNCTION one() RETURNS integer AS $$

SELECT 1 AS result;

$$ LANGUAGE SQL;

-- Альтернативная запись строковой константы:

CREATE FUNCTION one() RETURNS integer AS '

SELECT 1 AS result;

' LANGUAGE SQL;

SELECT one();

one

-----

Заметьте, что мы определили псевдоним столбца в теле функции для её результата (дали ему имя result), но этот псевдоним не виден снаружи функции. Вследствие этого, столбец результата получил имя one, а не result.

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

CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$

SELECT x + y;

$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

answer

--------

Мы также можем отказаться от имён аргументов и обращаться к ним по номерам:

CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$

SELECT $1 + $2;

$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

answer

--------

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

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$

UPDATE bank

SET balance = balance - debit

WHERE accountno = tf1.accountno;

SELECT 1;

$$ LANGUAGE SQL;

Пользователь может выполнить эту функцию, чтобы дебетовать счёт 17 на 100 долларов, так:

SELECT tf1(17, 100.0);

В этом примере мы выбрали имя accountno для первого аргумента, но это же имя имеет столбец в таблице bank. В команде UPDATE имя accountno относится к столбцу bank.accountno, так для обращения к аргументу нужно записать tf1.accountno. Конечно, мы могли бы избежать этого, выбрав другое имя для аргумента.

На практике обычно желательно получать от функции более полезный результат, чем константу 1, поэтому более реалистично такое определение:

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$

UPDATE bank

SET balance = balance - debit

WHERE accountno = tf1.accountno;

SELECT balance FROM bank WHERE accountno = tf1.accountno;

$$ LANGUAGE SQL;

Эта функция изменяет баланс и возвращает полученное значение. То же самое можно сделать в одной команде, применив RETURNING:

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$

UPDATE bank

SET balance = balance - debit

WHERE accountno = tf1.accountno

RETURNING balance;

$$ LANGUAGE SQL;

Заключение

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

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

1. Дейт, К. Дж. SQL и реляционная теория. Как грамотно писать код на SQL/К.Дж.Дейт.-М.:Символ-плюс,2017.-480c.

2. Дунаев, В. В. Базы данных. Язык SQL для студента / В.В. Дунаев. - М.: БХВ-Петербург, 2016. - 288 c.

3. Оппель, Эндрю Дж. SQL. Полное руководство / Оппель Эндрю Дж.. - М.: Диалектика / Вильямс, 2016. - 902 c.

4. Прайс, Джейсон Oracle Database 11g: SQL. Операторы SQL и программы PL/SQL / Джейсон Прайс. - М.: ЛОРИ, 2016. - 660 c.

5. Макдоналд Oracle PL/SQL практические решения / Макдоналд и др. - М.: СПб: ДиаСофт, 2017. - 560 c.

6. Нанда Oracle PL/SQL для администраторов баз данных / Нанда, др. А. и. - М.: Символ, 2017. - 496 c.