Так уж получилось, что в процессе изучения Access неожиданно выяснилось, что для работы с этим приложением нужно ещё владеть базовыми знаниями SQL и Access. В данном сообщение начнем знакомство с SQL.
Первой книгой, которой я воспользуюсь для написания будет книга Бена Форта "Освой самостоятельно SQL. 10 минут на урок" и потом перейдем к другим книгам. Писать буду тезисы, заинтересовавшие меня по мере прочтения. Так что прошу прощения за отрывочность и перескоки.
Начнем с простого определения, что же такое SQL.
SQL (Structured Query Language) - язык структурированных запросов. Он был разработан для взаимодействия с базами данных.
Начнем с простого определения, что же такое SQL.
SQL (Structured Query Language) - язык структурированных запросов. Он был разработан для взаимодействия с базами данных.
Основные задачи и операторы:
- Для выборки данных из таблицы используется SELECT.
Синтаксис:
SELECT name
FROM table;
Операторы нечувствительны к разным регистрам, однако переменные да. Поэтому нужно следить за корректностью введенных переменных.
При необходимости вывести несколько столбцов, необходимо их записать в запросе через запятую. - Сортировка данных ORDER BY.
Синтаксис:
SELECT name
FROM table
ORDER BY name;
Данный оператор используется для сортировки. Важно следить, чтобы он был последним.
Для точной сортировки используется порядок сортировки, который задается словами ASC(ascending) и DESC(descending). По умолчанию используется сортировка ASC, то есть восходящая А-Я, поэтому прописывать ASC необязательно. Так же тип сортировки можно указывать для каждого столбца отдельно. - Фильтрация данных с помощью условия WHERE.
Синтаксис: (например)
SELECT name
FROM table
WHERE name>0.6;
В результате работы этого оператора показываются не все результирующие строки, а только те, которые удовлетворяют условию.
Так же можно использовать логические операторы, как AND и OR.
Ключевое слово IN используется для указания диапазона условий, любое из которых может быть выполнено. При этом значения, заключенные в скобки, перечисляются через запятую.
Ключевое слово IN обрабатывается быстрее, чем логическое слово OR.
Так же существует логический оператор NOT. Он служит для того, чтобы отрицать все предложения, следующие за ним. Обычно он вставляется перед названием столбца, значения которого нужно отфильтровать, а не после.
Оператор LIKE используется совместно с метасимволами для поиска строк, содержащих заданные символы. При этом знак %(процента) может заменить бесконечно много символов, а знак _(нижнее подчеркивание) только одно.
Конкатенация полей - это комбинирование значений (путем присоединения их друг к другу) для получения одного "длинного" значения.
В СУБД Access, SQL Server и Sybase для конкатенации используется знак плюс ("+"). В СУБД DB2, Oracle, PostgreSQL и Sybase используется знак ||. Оператор || более предпочтителен, так как он поддерживается большим количеством СУБД.
В MySQL не поддерживается конкатенация при помощи оператора + или ||. Здесь необходимо использовать функцию CONCAT().
Добавленные пробелы в SQL можно убирать с помощью функций RTRIM() - убирает пробелы справа; LTRIM() - удаляет левую часть строк, а также TRIM() - "обрезает" строку слева и справа.
Псевдоним - это альтернативное имя для поля или значения (чаще всего используется для переименования полей). Псевдоним присваивается при помощи ключевого слова AS (alias). При указании псевдонимов важно не использовать реальных названий столбцов в таблице, поскольку во многих реализациях SQL такое поведение не приветствуется - можно получить сообщение об ошибке.
Статистические функции
Функция AVG используется для возвращения среднего значения определенного столбца путем подсчета числа строк в таблице и суммирования их значений. Эту функцию можно использовать для возвращения среднего значения всех столбцов или определенных столбцов или строк.
Однако данную функцию можно использовать только для вычисления среднего значения определенного числового столбца, имя этого столбца должно быть указано в качестве параметра функции. Чтобы получить среднее значение нескольких столбцов, необходимо использовать несколько функций AVG (). Строки NULL игнорируются. При этом важно учитывать что использование оператора DISTINCT (убирает дублирующие строки) делает результат подобного запроса некорректным.
Функция COUNT () подсчитывает число строк:
Функция SUM () возвращает сумму (общую) значений в определенном столбце.Значения NULL игнорируются.
Все статистические функции можно использовать для выполнения вычислений над несколькими столбцами при помощи стандартных математических операторов.
Для всех статистических функций могут быть использованы ALL и DISTINCT.
DISTINCT убирает дублирующие значения из столбцов.
Статистические функции используются для получения итоговых данных. В SQL поддерживается пять статистических функций, каждая из которых может использоваться несколькими способами для возвращения только необходимых в данный момент результатов. Эти функции разработаны для повышения эффективности работы, обычно они возвращают результат быстрее, чем если бы расчеты производились в клиентском приложении.
Итоговые данные
Группирование дает возможность разделить все данные на логические наборы, благодаря чему становится возможным выполнение статистических вычислений отдельно по каждой группе.
Группы создаются с помощью предложения GROUP BY оператора SELECT.
Предложение GROUP BY указывает СУБД группировать данные и затем выполнять вычисление по каждой группе, а не по всему набору результатов.
Пример:
SELECT vend_id, COUNT (*) AS num_prods
FROM Products
GROUP BY vend_id;
В данном примере оператор SELECT предписывает вывести два столбца - vend_id. содержащий идентификатор поставщика продукта, и num_prods, содержащий вычисляемые поля (он создается с помощью функции COUNT(*)). Предложение GROUP BY указывает СУБД сортировать данные и группировать их по столбцу vend_id. В результате значения num_prods будет вычисляться по одному разу для каждой группы записей vend_id, а не один раз для всей таблицы products.
Однако есть важные правила для использования GROUP BY:
Статистические функции
Функция AVG используется для возвращения среднего значения определенного столбца путем подсчета числа строк в таблице и суммирования их значений. Эту функцию можно использовать для возвращения среднего значения всех столбцов или определенных столбцов или строк.
Однако данную функцию можно использовать только для вычисления среднего значения определенного числового столбца, имя этого столбца должно быть указано в качестве параметра функции. Чтобы получить среднее значение нескольких столбцов, необходимо использовать несколько функций AVG (). Строки NULL игнорируются. При этом важно учитывать что использование оператора DISTINCT (убирает дублирующие строки) делает результат подобного запроса некорректным.
Функция COUNT () подсчитывает число строк:
- В виде COUNT(*) для подсчета числа строк в таблице независимо от того, содержат столбцы значения NULL или нет.
- В виде COUNT(column) для подсчета числа строк, которые имеют значения в указанных столбцах, причем значения NULL игнорируются.
Функция MAX () возвращает самое большое значение из указанного столбца. Для этой функции необходимо указывать имя столбца.
Несмотря на то что функция MAX обычно используется для поиска наибольшего числового значения или даты, многие (но не все) СУБД позволяют использовать ее для возвращения наибольшего значения из всех столбцов, включая текстовые. При использовании с текстовыми данными функция возвращает строку, которая была последней, если бы данные были отсортированы по этой строке. Значения столбцов со значениями NULL игнорируются.
Функция MIN () возвращает самое маленькое значение из указанного столбца.
Функция SUM () возвращает сумму (общую) значений в определенном столбце.Значения NULL игнорируются.
Все статистические функции можно использовать для выполнения вычислений над несколькими столбцами при помощи стандартных математических операторов.
Для всех статистических функций могут быть использованы ALL и DISTINCT.
DISTINCT убирает дублирующие значения из столбцов.
Статистические функции используются для получения итоговых данных. В SQL поддерживается пять статистических функций, каждая из которых может использоваться несколькими способами для возвращения только необходимых в данный момент результатов. Эти функции разработаны для повышения эффективности работы, обычно они возвращают результат быстрее, чем если бы расчеты производились в клиентском приложении.
Итоговые данные
Группирование дает возможность разделить все данные на логические наборы, благодаря чему становится возможным выполнение статистических вычислений отдельно по каждой группе.
Группы создаются с помощью предложения GROUP BY оператора SELECT.
Предложение GROUP BY указывает СУБД группировать данные и затем выполнять вычисление по каждой группе, а не по всему набору результатов.
Пример:
SELECT vend_id, COUNT (*) AS num_prods
FROM Products
GROUP BY vend_id;
В данном примере оператор SELECT предписывает вывести два столбца - vend_id. содержащий идентификатор поставщика продукта, и num_prods, содержащий вычисляемые поля (он создается с помощью функции COUNT(*)). Предложение GROUP BY указывает СУБД сортировать данные и группировать их по столбцу vend_id. В результате значения num_prods будет вычисляться по одному разу для каждой группы записей vend_id, а не один раз для всей таблицы products.
Однако есть важные правила для использования GROUP BY:
- В предложениях GROUP BY можно указывать только столбцов, сколько вам необходимо. Это позволяет вкладывать группы одна в другую, благодаря чему обеспечивается тщательный контроль за тем, какие данные подлежат группированию.
- Если вы используете вложенные группы в предложении GROUP BY, данные суммируются для последней указанной вами группы. Другими словами, если введено группирование, вычисления осуществляются для всех указанных столбцов (вы не сможете вернуть данные для каждого отдельного столбца)
- Каждый столбец, указанный в предложении GROUP BY, должен быть столбцом выборки или выражением (но не функцией группирования). Если в операторе SELECT используется какое-то выражение, то же самое выражение должно быть указано в предложении GROUP BY. Псевдонимы применять нельзя.
- В большинстве реализаций SQL нельзя указывать в предложении GROUP BY столбцы, в которых содержатся данные переменной длины (т.е. столбцы, содержащие текстовые поля или поля комментариев).
- За исключением операторов статистических вычислений, каждый столбец, упомянутый в операторе SELECT, должен быть представлен в предложении GROUP BY.
- Если столбец, подлежащий группированию, содержит строку со значением NULL, оно будет возвращено в качестве группы. Если имеется несколько строк со значениями NULL, они будут сгруппированы вместе.
- Предложение GROUP BY должно следовать после предложения WHERE и до какого-либо предложения ORDER BY.
В дополнение к способности группировать данные с помощью предложения GROUP BY, SQL так же позволяет осуществлять фильтрацию - указывать, какие группы должны быть включены в результат. а какие исключены из него. Следует использовать предложение HAVING. Оно похоже на предложение WHERE. но существует одна разница. А именно WHERE проверяет и фильтрует данные построчно, а HAVING - в группах.
Иначе, WHERE фильтрует до того, как данные будут сгруппированы, а HAVING фильтрует после того, как данные были сгруппированы. Это важное различие. Строки, которые были выброшены по предложению WHERE, не будут включены в группу, иначе это могло бы изменить вычисляемые значения, которые, в свою очередь, могли бы повлиять на фильтрацию групп в предложении HAVING.
Используйте предложение HAVING только вместе с предложением GROUP BY, а предложение WHERE - для стандартной фильтрации на уровне строк.
ORDER BY используется для сортировки полученных результатов.
Обновление и удаление данных
Обновление с помощью UPDATE. Синтаксис:
UPDATE table
SET column.name = 'something'
WHERE (условие);
Оператор UPDATE всегда начинается с имени таблицы, подлежащей обновлению. Затем используется команда SET, чтобы ввести в столбец новое значение. Заканчивается он предложением WHERE, которое сообщает СУБД какая строка подлежит обновлению. При отсутствии условия можно обновить все строки таблицы на одно и то же значение. Для обновления нескольких столбцов необходимо указать их через запятую. При этом используется только одна команда SET.
Удаление с помощью DELETE. Синтаксис:
DELETE FROM table
WHERE (условие);
Требуется указать имя таблицы, из которой должны быть удалены данные. Этот оператор удаляет строки целиком, а не отдельные столбцы. Для удаления определенного столбца следует использовать оператор UPDATE. Оператор DELETE удаляет из таблицы отдельные строки или даже все строки за один раз, но он никогда не удаляет саму таблицу.
Для более быстрого удаления значений из всех строк таблицы лучше использовать оператор TRUNCATE TABLE, который делает это быстрее, так как не регистрирует изменения в данных.
Добавление данных
Обновление с помощью INSERT. Синтаксис:
INSERT INTO table
VALUES (' ', ' ',' ');
Данные, которые должны быть сохранены в каждом столбце таблицы, указываются в условии VALUES, значения должны быть приведены для каждого столбца. Если для какого-то столбца не имеется соответствующего значения, следует использовать значение NULL. Столбцы должны заполняться в порядке, котором они появились в определении таблицы.
Однако нет гарантий, что столбцы будут расположены в том же самом порядке, когда таблица будет реконструироваться в следующий раз. Более безопасный способ - указать явно все имена столбцов в круглых скобках, следующих после имени столбца. В этом случае так как предоставлены имена столбцов, то условие VALUES должно подобрать названные имена столбцов в порядке, в котором указаны столбцы, причем не обязательно в порядке, в каком они следуют в реальной таблице. Это удобно даже, если расположение столбцов в таблице изменится.
Для вставки в таблицу данных из другой таблицы следует использовать INSERT SELECT. Синтаксис:
INSERT INTO table1 (c1, c2,c3)
SELECT (c1,c2,c3)
FROM table 2;
Однако необходимо учитывать, что к именам столбцов не предъявляются никакие требования. В действительности СУБД вообще не обращает внимания на имена столбцов, возвращаемых оператором SELCT. Точнее, ею используется положение столбца, так что первый столбце в SELECT (независимо от имени) будет использован для заполнения первого указанного столбца таблицы и т.д.
Для копирования данных из одной таблицы в новую можно выполнить с помощью оператора SELECT INTO. Разница между INSERT SELECT и SELECT INTO состоит в том, что первый оператор экспортирует данные, а второй - импортирует.
Этот оператор создает новую таблицу и копирует в нее все содержимое другой таблицы. Можно использовать операторы WHERE и GROUP BY. Для добавления данных из нескольких таблиц можно использовать объединения. Данные можно добавить только в одну таблицу независимо от того, из скольких таблиц они были извлечены.
Создание таблицы
Чтобы создать таблицу необходимо использовать оператор CREATE TABLE. При этом нужно указать следующие данные:
Транзакции
Обработка транзакций обеспечивает сохранение целостности базы данных за счет того, что пакеты операций SQL выполняются полностью или не выполняются вовсе. Основные термины
Чтобы сделать транзакцию управляемой, нужно разбить ее SQl-операторы на логические части и явно указать, когда может быть выполнена отмена, а когда нет.
Курсоры
Курсор представляет собой запрос к базе данных, хранящийся на сервере СУБД, - это не оператор SELECT, но результирующее множество, выборка, полученная в результате действия оператора SELECT. После того как курсор сохранен, приложения могут "прокручивать" (просматривать) данные в прямом или обратном направлении, как только возникает такая потребность.
Курсоры используются главным образом интерактивными приложениями, предоставляющими пользователям возможность прокручивать отображаемые на экране данные вперед и назад, просматривать их или изменять.
Работу с курсором можно разделить на несколько четко выраженных стадий:
Далее буду использовать книгу: "SQL для простых смертных" Мартин Грабер.
Будем восполнять пробелы и дополнять предыдущую информацию.
Подразделы SQL
Язык определения данных (Data Definition language, DDL; в ANSI он называется также языком определения схемы (Schema Definition Lаnguage)) состоит из трех команд, которые создают объекты (таблицы, индексы, представления) в БД, Язык манипулирования данными (Data Manipulation Language, DML) - это множество команд, определяющих, какие данные представлены в таблицах в любой момент времени. Язык управления данными (Data Control Language, DCL) состоит из предложений, определяющих, может ли пользователь выполнить отдельное действие. Согласно ANSI, DCL является частью DDL. Важно не путать эти названия. Речь идет не о различных языках как таковых, а о разделах команд SQL, сгруппированных в соответствии с их функциональным назначением.
Использование агрегатной функции COUNT
COUNT со звездочкой (COUNT(*)) включает как NULL-значения, так и повторяющиеся значения. Также можно использовать аргумент ALL вместо звездочки. Однако в этом случае из расчета будут исключены NUUL-значения.
Нельзя использовать вложенные агрегатные функции. В случае необходимости дополнительного расчета внутри агрегатной функции следует использовать подзапрос.
Однако следует учесть, что существует возможность использовать в подзапросе оператор IN (операторы BETWEEN, LIKE, IS NULL в подзапросах применять нельзя). К тому же вложенные подзапросы выполняются быстрее, чем несколько запросов.
Привилегии - это те действия, которые может выполнять пользователь.
Привилегии SQL, определенные ANSI, являются объектными привилегиями (object privileges).Это означает, что пользователь имеет привилегию выполнить команду только для определенного объекта БД. Объектные привилегии связаны как с пользователями, так и с таблицами. Привилегия дается отдельному пользователю для отдельной таблицы либо для базовой таблицы, либо для представления. Пользователь, создавший таблицу, является её владельцем.
Привилегии назначаются с помощью команды GRANT.
Например, GRANT SELECT ON Customers TO Adrian;
Возможна передача группы привилегий или группе пользователей. В таком случае привилегии или пользователи перечисляются через запятую.
Для команд UPDATE и REFERENCES отдельно можно задать названия столбцов.
Можно сразу дать права какому-то пользователю на всё.
Например, GRANT ALL PROVILEGES ON Customers TO Stephen;
Или дать определенные права, но всем.
Например, GRANT SELECT ON Orders TO Public;
Но все эти варианты передают привилегии только от владельца другому пользователю. В случае, если необходимо предусмотреть дальнейшую передачу иному пользователю без участия владельца, то в конце необходимо указать WITH GRANT OPTION.
Например, GRANT SELECT ON Customers TO Adrian
WITH GRANT OPTION;
Для того, чтобы лишить привилегий пользователя необходимо использовать такой же синтаксис, но вместо GRANT прописывать REVOKE. Обычно отмена привилегий осуществляется каскадно.
Вставка из онлайн курс "Основы SQL"
Будем дальше восполнять пробелы и дополнять предыдущую информацию.
Ссылка на курс: http://www.intuit.ru/studies/courses/5/5/info
Основные категории команд языка SQL:
Обновление и удаление данных
Обновление с помощью UPDATE. Синтаксис:
UPDATE table
SET column.name = 'something'
WHERE (условие);
Оператор UPDATE всегда начинается с имени таблицы, подлежащей обновлению. Затем используется команда SET, чтобы ввести в столбец новое значение. Заканчивается он предложением WHERE, которое сообщает СУБД какая строка подлежит обновлению. При отсутствии условия можно обновить все строки таблицы на одно и то же значение. Для обновления нескольких столбцов необходимо указать их через запятую. При этом используется только одна команда SET.
Удаление с помощью DELETE. Синтаксис:
DELETE FROM table
WHERE (условие);
Требуется указать имя таблицы, из которой должны быть удалены данные. Этот оператор удаляет строки целиком, а не отдельные столбцы. Для удаления определенного столбца следует использовать оператор UPDATE. Оператор DELETE удаляет из таблицы отдельные строки или даже все строки за один раз, но он никогда не удаляет саму таблицу.
Для более быстрого удаления значений из всех строк таблицы лучше использовать оператор TRUNCATE TABLE, который делает это быстрее, так как не регистрирует изменения в данных.
Добавление данных
Обновление с помощью INSERT. Синтаксис:
INSERT INTO table
VALUES (' ', ' ',' ');
Данные, которые должны быть сохранены в каждом столбце таблицы, указываются в условии VALUES, значения должны быть приведены для каждого столбца. Если для какого-то столбца не имеется соответствующего значения, следует использовать значение NULL. Столбцы должны заполняться в порядке, котором они появились в определении таблицы.
Однако нет гарантий, что столбцы будут расположены в том же самом порядке, когда таблица будет реконструироваться в следующий раз. Более безопасный способ - указать явно все имена столбцов в круглых скобках, следующих после имени столбца. В этом случае так как предоставлены имена столбцов, то условие VALUES должно подобрать названные имена столбцов в порядке, в котором указаны столбцы, причем не обязательно в порядке, в каком они следуют в реальной таблице. Это удобно даже, если расположение столбцов в таблице изменится.
Для вставки в таблицу данных из другой таблицы следует использовать INSERT SELECT. Синтаксис:
INSERT INTO table1 (c1, c2,c3)
SELECT (c1,c2,c3)
FROM table 2;
Однако необходимо учитывать, что к именам столбцов не предъявляются никакие требования. В действительности СУБД вообще не обращает внимания на имена столбцов, возвращаемых оператором SELCT. Точнее, ею используется положение столбца, так что первый столбце в SELECT (независимо от имени) будет использован для заполнения первого указанного столбца таблицы и т.д.
Для копирования данных из одной таблицы в новую можно выполнить с помощью оператора SELECT INTO. Разница между INSERT SELECT и SELECT INTO состоит в том, что первый оператор экспортирует данные, а второй - импортирует.
Этот оператор создает новую таблицу и копирует в нее все содержимое другой таблицы. Можно использовать операторы WHERE и GROUP BY. Для добавления данных из нескольких таблиц можно использовать объединения. Данные можно добавить только в одну таблицу независимо от того, из скольких таблиц они были извлечены.
Создание таблицы
Чтобы создать таблицу необходимо использовать оператор CREATE TABLE. При этом нужно указать следующие данные:
- имя новой таблицы. оно выводится после ключевого слова CREATE TABLE
- имена и определения столбцов таблицы. разделенные запятыми
- в некоторых СУБД также требуется, чтобы было указано место размещения таблицы
Желательно разбивать код на несколько строк (для каждой переменной) для удобства чтения.
Для обновления таблицы следует использовать оператор ALTER TABLE.
Добавление столбца
ALTER TABLE table
ADD column.name;
Удаление столбца
ALTER TABLE table
DROP COLUMN column.name;
Удаление всей таблицы (а не значений)
DROP TABLE table;
Подзапросы.
Это запросы, которые вложены в другие запросы. Их также называют вложенные запросы, или подчиненные запросы. Подзапросы всегда обрабатываются, начиная с самого внутреннего оператора в направлении "изнутри наружу". Чаще всего подзапросы используют в операторах IN предложения WHERE и для заполнения вычисляемых столбцов.
Это запросы, которые вложены в другие запросы. Их также называют вложенные запросы, или подчиненные запросы. Подзапросы всегда обрабатываются, начиная с самого внутреннего оператора в направлении "изнутри наружу". Чаще всего подзапросы используют в операторах IN предложения WHERE и для заполнения вычисляемых столбцов.
Представление - это виртуальные таблицы. В отличие от таблиц, содержащих данные, представления содержат запросы, которые динамически выбирают данные, когда это необходимо. Следует обратить внимание, что представления тормозят системы, так как они не содержат данные, то им нужно каждый раз запрашивать данные. Используется CREATE VIEW для создания и DROP VIEW для удаления.
Представления чаще всего используются для упрощения выполнения сложных операций. После того как запрос подготовлен, его можно с легкостью использовать повторно, для этого не нужно разбираться в особенностях его работы.
Представления чаще всего используются для упрощения выполнения сложных операций. После того как запрос подготовлен, его можно с легкостью использовать повторно, для этого не нужно разбираться в особенностях его работы.
Хранимые процедуры
Это совокупность нескольких операторов, сохраненная для последующего использования. Использование:
- для упрощения сложных операций за счет инкапсуляции процессов в один блок, простой для выполнения
- для обеспечения непротиворечивости данных и вместе с тем без необходимости снова и снова воспроизводить одну и ту же последовательность шагов. Если все разработчики и приложения используют одни и те же хранимые процедуры, значит один и тот же код будет использоваться всеми
- побочным эффектом является предотвращение ошибок. Чем больше шагов необходимо выполнить, тем выше вероятность появления ошибок. Предотвращение ошибок обеспечивает целостность данных
- для упрощения управления изменениями. Если таблицы, имена столбцов, деловые правила изменяются, обновлять приходится только код хранимой процедуры и ничего больше. Побочный эффект - повышение безопасности. Ограничение доступа к основным данным только через хранимые процедуры снижает вероятность повреждения данных
- поскольку хранимые процедуры обычно сохраняются в компилированном виде, СУБД тратит меньше времени на обработку их команд. Это приводит к повышению производительности
- существуют элементы языка и некоторые возможности, реализуемые только в хранимых процедурах
Минусы: синтаксис всегда различен для каждой среды и хранимые процедуры всегда сложнее в написании.
Для исполнения хранимой процедуры используется оператор EXECUTE. Необходимо прописать имя хранимой процедуры и некоторые параметры, необходимые для перехода к ней.
Транзакции
Обработка транзакций обеспечивает сохранение целостности базы данных за счет того, что пакеты операций SQL выполняются полностью или не выполняются вовсе. Основные термины
- Транзакция (Transaction). Блок операторов SQL
- Отмена (Rollback). Процесс аннулирования указанных операторов SQL (такой процесс иногда называют "откат")
- Фиксация (Commit). Запись несохраненных операторов SQL в таблицы БД
- Точка сохранения (Savepoint). Временное состояние в ходе выполнения транзакции, в которое можно вернуться после отмены части операций пакета (в отличие от отмены всей транзакции). Иногда это состояние называется "точка отката"
Обработка транзакций используется в ходе управления действием операторов INSERT, UPDATE и DELETE. Нельзя отменить действие оператора SELECT, CREATE и DROP. Эти операторы можно использовать в блоке операторов транзакции, но если вам понадобится выполнить отмену (откат), действие этих операторов аннулировано не будет.
Чтобы сделать транзакцию управляемой, нужно разбить ее SQl-операторы на логические части и явно указать, когда может быть выполнена отмена, а когда нет.
Курсоры
Курсор представляет собой запрос к базе данных, хранящийся на сервере СУБД, - это не оператор SELECT, но результирующее множество, выборка, полученная в результате действия оператора SELECT. После того как курсор сохранен, приложения могут "прокручивать" (просматривать) данные в прямом или обратном направлении, как только возникает такая потребность.
Курсоры используются главным образом интерактивными приложениями, предоставляющими пользователям возможность прокручивать отображаемые на экране данные вперед и назад, просматривать их или изменять.
Работу с курсором можно разделить на несколько четко выраженных стадий:
- Прежде чем курсор может быть использован, его следует объявить (определить). В ходе этого процесса выборка данных не производится, просто определяется оператор SELECT, который будет использован, и некоторые опции курсора
- После объявления курсор может быть открыт для использования. В ходе этого процесса уже производится выборка данных согласно предварительно определенному оператору SELECT
- После того как курсор заполнен данными, могут быть извлечены (выбраны) отдельные необходимые строки
- После того как это сделано, курсор должен быть закрыт и, возможно, должны быть освобождены ресурсы, которые он занимал (в зависимости от СУБД)
Курсоры создаются с помощью оператора DECLARE. Он дает курсору имя и принимает оператор SELECT. Синтаксис для разных SQL разный.
Пример: курсор, который будет делать выборку всех клиентов, не имеющих адреса электронной почты, в виде части приложения, позволяющего служащему вводить недостающие адреса.
Для Oracle и PostgreSQL:
DECLARE CURSOS CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL
Другие операторы при использовании курсоров OPEN, CLOSE и FETCH.
OPEN CURSOR открывает курсор. CLOSE закрывает курсор. Оператор FETCH указывает строки, которые должны быть выбраны, откуда они должны быть выбраны и где их следует сохранить.
Далее буду использовать книгу: "SQL для простых смертных" Мартин Грабер.
Будем восполнять пробелы и дополнять предыдущую информацию.
Подразделы SQL
Язык определения данных (Data Definition language, DDL; в ANSI он называется также языком определения схемы (Schema Definition Lаnguage)) состоит из трех команд, которые создают объекты (таблицы, индексы, представления) в БД, Язык манипулирования данными (Data Manipulation Language, DML) - это множество команд, определяющих, какие данные представлены в таблицах в любой момент времени. Язык управления данными (Data Control Language, DCL) состоит из предложений, определяющих, может ли пользователь выполнить отдельное действие. Согласно ANSI, DCL является частью DDL. Важно не путать эти названия. Речь идет не о различных языках как таковых, а о разделах команд SQL, сгруппированных в соответствии с их функциональным назначением.
Использование агрегатной функции COUNT
COUNT со звездочкой (COUNT(*)) включает как NULL-значения, так и повторяющиеся значения. Также можно использовать аргумент ALL вместо звездочки. Однако в этом случае из расчета будут исключены NUUL-значения.
Нельзя использовать вложенные агрегатные функции. В случае необходимости дополнительного расчета внутри агрегатной функции следует использовать подзапрос.
Однако следует учесть, что существует возможность использовать в подзапросе оператор IN (операторы BETWEEN, LIKE, IS NULL в подзапросах применять нельзя). К тому же вложенные подзапросы выполняются быстрее, чем несколько запросов.
Привилегии - это те действия, которые может выполнять пользователь.
Привилегии SQL, определенные ANSI, являются объектными привилегиями (object privileges).Это означает, что пользователь имеет привилегию выполнить команду только для определенного объекта БД. Объектные привилегии связаны как с пользователями, так и с таблицами. Привилегия дается отдельному пользователю для отдельной таблицы либо для базовой таблицы, либо для представления. Пользователь, создавший таблицу, является её владельцем.
Привилегии назначаются с помощью команды GRANT.
Например, GRANT SELECT ON Customers TO Adrian;
Возможна передача группы привилегий или группе пользователей. В таком случае привилегии или пользователи перечисляются через запятую.
Для команд UPDATE и REFERENCES отдельно можно задать названия столбцов.
Можно сразу дать права какому-то пользователю на всё.
Например, GRANT ALL PROVILEGES ON Customers TO Stephen;
Или дать определенные права, но всем.
Например, GRANT SELECT ON Orders TO Public;
Но все эти варианты передают привилегии только от владельца другому пользователю. В случае, если необходимо предусмотреть дальнейшую передачу иному пользователю без участия владельца, то в конце необходимо указать WITH GRANT OPTION.
Например, GRANT SELECT ON Customers TO Adrian
WITH GRANT OPTION;
Для того, чтобы лишить привилегий пользователя необходимо использовать такой же синтаксис, но вместо GRANT прописывать REVOKE. Обычно отмена привилегий осуществляется каскадно.
Вставка из онлайн курс "Основы SQL"
Будем дальше восполнять пробелы и дополнять предыдущую информацию.
Ссылка на курс: http://www.intuit.ru/studies/courses/5/5/info
Основные категории команд языка SQL:
- DDL - язык определения данных
- DML - язык манипулирования данными
- DQL - язык запросов
- DCL - язык управления данными
- команды администрирования данных
- команды управления транзакциями
Определение структур базы данных (DDL)
Язык определения данных (Data Definition Language, DDL) позволяет создавать и изменять структуру объектов БД, например, создавать и удалять таблицы. Основными командами языка DDL являются: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX.
Манипулирование данными (DML)
Язык манипулирования данными (Data Manipulation Language. DML) используется для манипулирования информацией внутри объектов реляционной базы данных посредством трех основных команд: INSERT, UPDATE, DELETE.
Выборка данных (DQL)
Язык запросов DQL наиболее известен пользователям реляционной базы данных, несмотря на то, что он включает всего одну команду SELECT. Эта команда со своими многочисленными опциями и предложениями используется для формирования запросов к реляционной БД.
Язык управления данными (DCL- Data Control Language)
Команды управления данными позволяют управлять доступом к информации, находящейся внутри БД. Как правило, они используются для создания объектов, связанных с доступом к данным, а также служат для контроля над распределением привилегий между пользователями. Команды управления данными: GRANT, REVOKE
Команды администрирования данных
С помощью команд администрирования данных пользователь осуществляет контроль за выполняемыми действиями и анализирует операции БД; они также могут оказаться полезными при анализе производительности системы. Не следует путать администрирование данных с администрированием БД, которое представляет собой общее управление БД и подразумевает использование команд всех уровней.
Команды управления транзакциями
Существуют следующие команды, позволяющие управлять транзакциями БД: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.
Методы оптимизации запросов.
1) Переписать запрос
2) Индексы
3) Материализованное представление
Что может помочь при оптимизации:
- избегать коррелирующих и скалярных запросов
- не увлекаться заворачиванием запросов в функции
- не увлекаться сортировкой. Сортировка нужна обычно только конечным пользователям
- выбирать только нужную информацию
- использовать CTE. Табличные выражения вычисляются только раз и делают код запроса понятней
- использовать средства профилирования. EXPLAIN ANALYZE - второй друг
- индексы повысят производительность, если запросы выполняются часто, выбирают мало записей или из большой таблицы
О материализации и избыточности
Когда использовать? Тяжелые долгие запросы к read-only данным, индексы не помогают? Материализуйте и используйте избыточную информацию. Однако, если данные постоянно меняются, запросы быстрые или редкие, то вводить материализацию или избыточность излишне.
Методы оптимизации запросов.
1) Переписать запрос
2) Индексы
3) Материализованное представление
Что может помочь при оптимизации:
- избегать коррелирующих и скалярных запросов
- не увлекаться заворачиванием запросов в функции
- не увлекаться сортировкой. Сортировка нужна обычно только конечным пользователям
- выбирать только нужную информацию
- использовать CTE. Табличные выражения вычисляются только раз и делают код запроса понятней
- использовать средства профилирования. EXPLAIN ANALYZE - второй друг
- индексы повысят производительность, если запросы выполняются часто, выбирают мало записей или из большой таблицы
О материализации и избыточности
Когда использовать? Тяжелые долгие запросы к read-only данным, индексы не помогают? Материализуйте и используйте избыточную информацию. Однако, если данные постоянно меняются, запросы быстрые или редкие, то вводить материализацию или избыточность излишне.
Комментариев нет:
Отправить комментарий