Точки сохранения и откаты транзакции




В SQL System R существовали два специальных оператора для установки так называемых точек сохранения транзакции и для отката транзакции к ранее установленной точке сохранения. В литературе, относящейся к System R, обсуждение этих возможностей практически не содержится, из чего неявно следует, что они не были реализованы.

Прямолинейная реализация этого механизма не вызывает особых технических затруднений, но и не очень полезна, потому что после выполнения частичного отката транзакции для успешного продолжения работы прикладной программы потребовалось бы и восстановить ее состояние в соответствующей точке, а это никак не поддерживается. Понятно, что при более тщательной проработке должны быть увязаны механизмы точек сохранения и контроля целостности. Например, было бы естественно, чтобы при выполнении оператора ENFORCE INTEGRITY, если какие-либо ограничения целостности нарушаются, происходил автоматический откат транзакции к ближайшей точки сохранения, в которой нарушения целостности БД не было. Это значительно усложнило бы реализацию, но было бы очень полезно. Аналогично, можно было бы использовать механизм точек сохранения при автоматических откатах транзакций по причине возникновения синхронизационных тупиков.

Отметим еще два важных свойства языка SQL System R, которые в разных видах присутствуют во всех развитых последующих вариантах языка.

Встроенный SQL

В SQL System R присутствуют специальные операторы, поддерживающие встраивание операторов SQL в традиционные языки программирования (в System R основным таким языком был PL/1).

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

Для этого в язык вводится понятие курсора, с которым связывается оператор выборки. Над определенным курсором можно выполнять оператор OPEN, означающий материализацию отношения-результата запроса, оператор FETCH, позволяющий выбрать очередной кортеж результирующего отношения в память программы, и оператор CLOSE, означающий конец работы с данным курсором.

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

Динамический SQL

Для упрощения создания интерактивных SQL-ориентированных систем в SQL System R были включены операторы, позволяющие во время выполнения транзакции откомпилировать и выполнить любой оператор SQL.

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

Оператор DESCRIBE служит для получения информации об указанном операторе SQL, ранее подготовленном с помощью оператора PREPARE. C помощью этого оператора можно узнать, во-первых, является ли подготовленный оператор оператором выборки, и во-вторых, если это оператор выборки, получить полную информацию о числе и типах столбцов результирующего отношения.

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

Подводя итог приведенному краткому описанию основных черт SQL System R, отметим, что несмотря на недостаточную техническую проработку, в идейном отношении язык содержал все необходимые средства, позволяющие использовать его как базовый язык СУБД.

 

Нам надо создать базу данных, которую мы назовем forum.

Для этого в SQL существует оператор create database.

Создание базы данных имеет следующий синтаксис: create database имя_базы_данных;

 

Теперь в этой базе данных нам надо создать 3 таблицы: темы, пользователи и сообщения. Но перед тем, как это делать, нам надо указать серверу в какую именно БД мы создаем таблицы, т.е. надо выбрать БД для работы. Для этого используется оператор use. Синтаксис выбора БД для работы следующий: use имя_базы_данных;


Для создания таблиц в SQL существует оператор create table. Создание базы данных имеет следующий синтаксис:

create table имя_таблицы (имя_первого_столбца тип, имя_второго_столбца тип, ..., имя_последнего_столбца тип );

 

 

show databases - показать все имеющиеся БД,

show tables - показать список таблиц текущей БД (предварительно ее надо выбрать с помощью оператора use),

describe имя_таблицы - показать описание столбцов указанной таблицы.

 

А сегодня мы рассмотрим последний оператор - drop, он позволяет удалять таблицы и БД. Например, давайте удалим таблицу topics. Так как мы два шага назад выбирали БД forum для работы, то сейчас ее выбирать не надо, можно просто написать: drop table имя_таблицы;
drop database имя_базы данных;

 

Числовые типы данных Тип данных Объем памяти Диапазон Описание

TINYINT (M) 1 байт от -128 до 127 или от 0 до 255 Целое число. Может быть объявлено положительным с помощью ключевого слова UNSIGNED, тогда элементам столбца нельзя будет присвоить отрицательное значение. Необязательный параметр М - количество отводимых под число символов. Необязательный атрибут ZEROFILL позволяет свободные позиции по умолчанию заполнить нулями. Примеры: TINYINT - хранит любое число в диапазоне от -128 до 127. TINYINT UNSIGNED - хранит любое число в диапазоне от 0 до 255. TINYINT (2) - предполагается, что значения будут двузначными, но по факту будет хранить и трехзначные. TINYINT (3) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 002.

SMALLINT (M) 2 байта от -32768 до 32767 или от 0 до 65535 Аналогично предыдущему, но с большим диапазоном. Примеры: SMALLINT - хранит любое число в диапазоне от -32768 до 32767. SMALLINT UNSIGNED - хранит любое число в диапазоне от 0 до 65535. SMALLINT (4) - предполагается, что значения будут четырехзначные, но по факту будет хранить и пятизначные. SMALLINT (4) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 0002.

MEDIUMINT (M) 3 байта от -8388608 до 8388608 или от 0 до 16777215 Аналогично предыдущему, но с большим диапазоном. Примеры: MEDIUMINT - хранит любое число в диапазоне от -8388608 до 8388608. MEDIUMINT UNSIGNED - хранит любое число в диапазоне от 0 до 16777215. MEDIUMINT (4) - предполагается, что значения будут четырехзначные, но по факту будет хранить и семизначные. MEDIUMINT (5) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 00002.

INT (M) или INTEGER (M) 4 байта от -2147683648 до 2147683648 или от 0 до 4294967295 Аналогично предыдущему, но с большим диапазоном. Примеры: INT - хранит любое число в диапазоне от -2147683648 до 2147683648. INT UNSIGNED - хранит любое число в диапазоне от 0 до 4294967295. INT (4) - предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные. INT (5) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 00002.

BIGINT (M) 8 байта от -263 до 263-1 или от 0 до 264 Аналогично предыдущему, но с большим диапазоном. Примеры: BIGINT - хранит любое число в диапазоне от -263 до 263-1. BIGINT UNSIGNED - хранит любое число в диапазоне от 0 до 264. BIGINT (4) - предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные. BIGINT (7) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 0000002.

BOOL или BOOLEAN 1 байт либо 0, либо 1 Булево значение. 0 - ложь (false), 1 - истина (true).

DECIMAL (M,D) или DEC (M,D) или NUMERIC (M,D) M + 2 байта зависят от параметров M и D Используются для величин повышенной точности, например, для денежных данных. M - количество отводимых под число символов (максимальное значение - 64). D - количество знаков после запятой (максимальное значение - 30). Пример: DECIMAL (5,2) - будет хранить числа от -99,99 до 99,99.

FLOAT (M,D) 4 байта мин. значение +(-) 1.175494351 * 10-39 макс. значение +(-) 3. 402823466 * 1038 Вещественное число (с плавающей точкой). Может иметь параметр UNSIGNED, запрещающий отрицательные числа, но диапазон значений от этого не изменится. M - количество отводимых под число символов. D - количество символов дробной части. Пример: FLOAT (5,2) - будет хранить числа из 5 символов, 2 из которых будут идти после запятой (например: 46,58).

DOUBLE (M,D) 8 байт мин. значение +(-) 2.2250738585072015 * 10-308 макс. значение +(-) 1.797693134862315 * 10308 Аналогично предыдущему, но с большим диапазоном. Пример: DOUBLE - будет хранить большие дробные числа.

Необходимо понимать, чем больше диапазон значений у типа данных, тем больше памяти он занимает. Поэтому, если предполагается, что значения в столбце не будут превышать 100, то используйте тип TINYINT. Если при этом все значения будут положительными, то используйте атрибут UNSIGNED. Правильный выбор типа данных позволяет сэкономить место для хранения этих данных. Строковые типы данных Тип данных Объем памяти Максимальный размер Описание

CHAR (M) M символов М символов Позволяет хранить строку фиксированной длины М. Значение М - от 0 до 65535. Примеры: CHAR (8) - хранит строки из 8 символов и занимает 8 байтов. Например, любое из следующих значений: '', 'Иван','Ирина', 'Сергей' будет занимать по 8 байтов памяти. А при попытке ввести значение 'Александра', оно будет усечено до 'Александ', т.е. до 8 символов.

VARCHAR (M) L+1 символов М символов Позволяет хранить переменные строки длиной L. Значение М - от 0 до 65535. Примеры: VARCHAR (3) - хранит строки максимум из 3 символов, но пустая строка '' занимает 1 байт памяти, строка 'a' - 2 байта, строк 'aa' - 3 байта, строка 'aaa' - 4 байта. Значение более 3 символов будет усечено до 3.

BLOB, TEXT L+2 символов 216-1 символов Позволяют хранить большие объемы текста. Причем тип TEXT используется для хранения именно текста, а BLOB - для хранения изображений, звука, электронных документов и т.д.

MEDIUMBLOB, MEDIUMTEXT L+3 символов 224-1 символов Аналогично предыдущему, но с большим размером.

LONGBLOB, LONGTEXT L+4 символов 232-1 символов Аналогично предыдущему, но с большим размером. ENUM ('value1', 'value2', ...,'valueN') 1 или 2 байта 65535 элементов Строки этого типа могут принимать только одно из значений указанного множества. Пример: ENUM ('да', 'нет') - в столбце с таким типом может храниться только одно из имеющихся значений. Удобно использовать, если предусмотрено, что в столбце должен храниться ответ на вопрос. SET ('value1', 'value2', ...,'valueN') до 8 байт 64 элемента Строки этого типа могут принимать любой или все элементы из значений указанного множества. Пример: SET ('первый', 'второй') - в столбце с таким типом может храниться одно из перечисленных значений, оба сразу или значение может отсутствовать вовсе. Календарные типы данных Тип данных Объем памяти Диапазон Описание DATE 3 байта от '1000-01-01' до '9999-12-31' Предназначен для хранения даты. В качестве первого значения указывается год в формате "YYYY", через дефис - месяц в формате "ММ", а затем день в формате "DD". В качестве разделителя может выступать не только дефис, а любой символ отличный от цифры. TIME 3 байта от '-838:59:59' до '838:59:59' Предназначен для хранения времени суток. Значение вводится и хранится в привычном формате - hh:mm:ss, где hh - часы, mm - минуты, ss - секунды. В качестве разделителя может выступать любой символ отличный от цифры. DATATIME 8 байт от '1000-01-01 00:00:00' до '9999-12-31 23:59:59' Предназначен для хранения и даты и времени суток. Значение вводится и хранится в формате - YYYY-MM-DD hh:mm:ss. В качестве разделителей могут выступать любые символы отличные от цифры. TIMESTAMP 4 байта от '1970-01-01 00:00:00' до '2037-12-31 23:59:59' Предназначен для хранения даты и времени суток в виде количества секунд, прошедших с полуночи 1 января 1970 года (начало эпохи UNIX). YEAR (M) 1 байт от 1970 до 2069 для М=2 и от 1901 до 2155 для М=4 Предназначен для хранения года. М - задает формат года. Например, YEAR (2) - 70, а YEAR (4) - 1970. Если параметр М не указан, то по умолчанию считается, что он равен 4.

Тип данных NULL Вообще-то это лишь условно можно назвать типом данных. По сути это скорее указатель возможности отсутствия значения. Например, когда вы регистрируетесь на каком-либо сайте, вам предлагается заполнить форму, в которой присутствуют, как обязательные, так и необязательные поля. Понятно, что регистрация пользователя невозможна без указания логина и пароля, а вот дату рождения и пол пользователь может указать по желанию. Для того, чтобы хранить такую информацию в БД и используют два значения: NOT NULL (значение не может отсутствовать) для полей логин и пароль, NULL (значение может отсутствовать) для полей дата рождения и пол. По умолчанию всем столбцам присваивается тип NOT NULL, поэтому его можно явно не указывать. Пример: create table users (login varchar(20), password varchar(15), sex enum('man', 'woman') NULL, date_birth date NULL); Таким образом, мы создаем таблицу с 4 столбцами: логин (не более 20 символов) обязательное, пароль (не более 15 символов) обязательное, пол (мужской или женский) не обязательное, дата рождения (тип дата)

Но в модели нашей БД поле id_author является внешним ключом, т.е. оно может иметь только те значения, которые есть в поле id_user таблицы users. Для того, чтобы указать это в SQL есть ключевое слово FOREIGN KEY (), которое имеет следующий синтаксис: FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);

 

 

Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется для внесения данных во все поля таблицы: INSERT INTO имя_таблицы VALUES ('значение_первого_столбца','значение_второго_столбца', ..., 'значение_последнего_столбца');

 

Итак, в нашей БД forum есть три таблицы: users (пользователи), topics (темы) и posts (сообщения). И мы хотим посмотреть, какие данные в них содержатся. Для этого в SQL существует оператор SELECT. Синтаксис его использования следующий: SELECT что_выбрать FROM откуда_выбрать;

 

Сейчас у нас всего 4 темы, а если их будет 100? Хотелось бы, чтобы они выводились, например, по алфавиту. Для этого в SQL существует ключевое слово ORDER BY после которого указывается имя столбца по которому будет происходить сортировка. Синтаксис следующий: SELECT имя_столбца FROM имя_таблицы ORDER BY имя_столбца_сортировки;

 

В конце desc сортировка по возрастанию

 

Очень часто нам не нужна вся информация из таблицы. Например, мы хотим узнать, какие темы были созданы пользователем sveta (id=4). Для этого в SQL есть ключевое слово WHERE, синтаксис у такого запроса следующий: SELECT имя_столбца FROM имя_таблицы WHERE условие;

 

 

Отбираются значения, находящиеся между указанными Пример: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3;

 

Отбираются значения, соответствующие указанным Пример: SELECT * FROM topics WHERE id_author IN (1, 4);
Отбираются значения, кроме указанных Пример: SELECT * FROM topics WHERE id_author NOT IN (1, 4);

 

Отбираются значения, соответствующие образцу Пример: SELECT * FROM topics WHERE topic_name LIKE 'вел%';

 

Вообще, корректный синтаксис объединения с условием выглядит так: SELECT имя_таблицы_1.имя_столбца1_таблицы_1, имя_таблицы_1.имя_столбца2_таблицы_1, имя_таблицы_2.имя_столбца1_таблицы_2, имя_таблицы_2.имя_столбца2_таблицы_2 FROM имя_таблицы_1, имя_таблицы_2 WHERE имя_таблицы_1.имя_столбца_по_которому_объединяем = имя_таблицы_2.имя_столбца_по_которому_объединяем;

 

А что, если нам надо лишь узнать сколько сообщений на форуме имеется. Для этого можно воспользоваться встроенной функцией COUNT(). Эта функция подсчитывает число строк. Причем, если в качестве аргумента этой функции выступает *, то подсчитываются все строки таблицы. А если в качестве аргумента указывается имя столбца, то подсчитываются только те строки, которые имеют значение в указанном столбце. В нашем примере оба аргумента дадут одинаковый результат, т.к. все столбцы таблицы имеют тип NOT NULL. Давайте напишем запрос, используя в качестве аргумента столбец id_topic: SELECT COUNT(id_topic) FROM posts;

 

Итак, в наших темах имеется 4 сообщения. Но что, если мы хотим узнать сколько сообщений имеется в каждой теме. Для этого нам понадобится сгруппировать наши сообщения по темам и вычислить для каждой группы количество сообщений. Для группировки в SQL используется оператор GROUP BY. Наш запрос теперь будет выглядеть так: SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic;

 

Предположим, мы решили, что нашему форуму нужны модераторы. Для этого в таблицу users надо добавить столбец с ролью пользователя. Для добавления столбцов в таблицу используется оператор ALTER TABLE - ADD COLUMN. Его синтаксис следующий: ALTER TABLE имя_таблицы ADD COLUMN имя_столбца тип; Давайте добавим столбец role в таблицу users: ALTER TABLE users ADD COLUMN role varchar(20); Столбец появился в конце таблицы:

 

Теперь надо назначить роль модератора какому-нибудь пользователю, пусть это будет sergey с id=1. Для обновления уже существующих данных служит оператор UPDATE. Его синтаксис следующий: UPDATE имя_таблицы SET имя_столбца=значение_столбца WHERE условие; Давайте сделаем Сергея модератором: UPDATE users SET role='модератор' WHERE id_user=1;

 

AVG() Функция возвращает среднее значение столбца.

COUNT() Функция возвращает число строк в столбце.

MAX() Функция возвращает самое большое значение в столбце.

MIN() Функция возвращает самое маленькое значение в столбце.

SUM() Функция возвращает сумму значений столбца.

 

 





©2015-2017 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.

Обратная связь

ТОП 5 активных страниц!