Сайт о телевидении

Основные операторы SQL. Синтаксис и примеры использования оператора SELECT.

Основу языка SQL составляют операторы, условно разбитые не несколько групп по выполняемым функциям.

Можно выделить следующие группы операторов (перечислены не всœе операторы SQL):

Операторы DDL (Data Definition Language) - операторы определœения объектов базы данных

· CREATE SCHEMA - создать схему базы данных

· DROP SHEMA - удалить схему базы данных

· CREATE TABLE - создать таблицу

· ALTER TABLE - изменить таблицу

· DROP TABLE - удалить таблицу

· CREATE DOMAIN - создать домен

· ALTER DOMAIN - изменить домен

· DROP DOMAIN - удалить домен

· CREATE COLLATION - создать последовательность

· DROP COLLATION - удалить последовательность

· CREATE VIEW - создать представление

· DROP VIEW - удалить представление

Операторы DML (Data Manipulation Language) - операторы манипулирования данными

· SELECT - отобрать строки из таблиц

· INSERT - добавить строки в таблицу

· UPDATE - изменить строки в таблице

· DELETE - удалить строки в таблице

· COMMIT - зафиксировать внесенные изменения

· ROLLBACK - откатить внесенные изменения

Операторы защиты и управления данными

· CREATE ASSERTION - создать ограничение

· DROP ASSERTION - удалить ограничение

· GRANT - предоставить привилегии пользователю или приложению на манипулирование объектами

· REVOKE - отменить привилегии пользователя или приложения

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

Наиболее важными для пользователя являются операторы манипулирования данными (DML).

Примеры использования операторов манипулирования данными

INSERT - вставка строк в таблицу

Пример 1 . Вставка одной строки в таблицу:

VALUES (4, "Иванов");

UPDATE - обновление строк в таблице

Пример 3 . Обновление нескольких строк в таблице:

SET PNAME = "Пушников"

WHERE P.PNUM = 1;

DELETE - удаление строк в таблице

Пример 4 . Удаление нескольких строк в таблице:

WHERE P.PNUM = 1;

Примеры использования оператора SELECT

Оператор SELECT является фактически самым важным для пользователя и самым сложным оператором SQL. Он предназначен для выборки данных из таблиц, ᴛ.ᴇ. он, собственно, и реализует одно их базовых назначение базы данных - предоставлять информацию пользователю.

Оператор SELECT всœегда выполняется над некоторыми таблицами, входящими в базу данных.

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

Результатом выполнения оператора SELECT всœегда является таблица. Τᴀᴋᴎᴍ ᴏϬᴩᴀᴈᴏᴍ, по результатам действий оператор SELECT похож на операторы реляционной алгебры. Любой оператор реляционной алгебры должна быть выражен подходящим образом сформулированным оператором SELECT. Сложность оператора SELECT определяется тем, что он содержит в себе всœе возможности реляционной алгебры, а также дополнительные возможности, которых в реляционной алгебре нет.

Порядок выполнения оператора SELECT

Для того чтобы понять, как получается результат выполнения оператора SELECT, рассмотрим концептуальную схему его выполнения. Эта схема является именно концептуальной, т.к. гарантируется, что результат будет таким, как если бы он выполнялся шаг за шагом в соответствии с этой схемой. На самом делœе, реально результат получается более изощренными алгоритмами, которыми "владеет" конкретная СУБД.

Стадия 1. Выполнение одиночного оператора SELECT

В случае если в операторе присутствуют ключевые слова UNION, EXCEPT и INTERSECT, то запрос разбивается на несколько независимых запросов, каждый из которых выполняется отдельно:

Шаг 1 (FROM) . Вычисляется прямое декартовое произведение всœех таблиц, указанных в обязательном разделœе FROM. В результате шага 1 получаем таблицу A.

Шаг 2 (WHERE) . В случае если в операторе SELECT присутствует раздел WHERE, то сканируется таблица A, полученная при выполнении шага 1. При этом для каждой строки из таблицы A вычисляется условное выражение, приведенное в разделœе WHERE. Только те строки, для которых условное выражение возвращает значение TRUE, включаются в результат. В случае если раздел WHERE опущен, то сразу переходим к шагу 3. В случае если в условном выражении участвуют вложенные подзапросы, то они вычисляются в соответствии с данной концептуальной схемой. В результате шага 2 получаем таблицу B.

Шаг 3 (GROUP BY) . В случае если в операторе SELECT присутствует раздел GROUP BY, то строки таблицы B, полученной на втором шаге, группируются в соответствии со списком группировки, приведенным в разделœе GROUP BY. В случае если раздел GROUP BY опущен, то сразу переходим к шагу 4. В результате шага 3 получаем таблицу С.

Шаг 4 (HAVING) . В случае если в операторе SELECT присутствует раздел HAVING, то группы, не удовлетворяющие условному выражению, приведенному в разделœе HAVING, исключаются. В случае если раздел HAVING опущен, то сразу переходим к шагу 5. В результате шага 4 получаем таблицу D.

Шаг 5 (SELECT) . Каждая группа, полученная на шаге 4, генерирует одну строку результата следующим образом. Вычисляются всœе скалярные выражения, указанные в разделœе SELECT. По правилам использования раздела GROUP BY, такие скалярные выражения должны быть одинаковыми для всœех строк внутри каждой группы. Для каждой группы вычисляются значения агрегатных функций, приведенных в разделœе SELECT. В случае если раздел GROUP BY отсутствовал, но в разделœе SELECT есть агрегатные функции, то считается, что имеется всœего одна группа. В случае если нет ни раздела GROUP BY, ни агрегатных функций, то считается, что имеется столько групп, сколько строк отобрано к данному моменту. В результате шага 5 получаем таблицу E, содержащую столько колонок, сколько элементов приведено в разделœе SELECT и столько строк, сколько отобрано групп.

Стадия 2. Выполнение операций UNION, EXCEPT, INTERSECT

В случае если в операторе SELECT присутствовали ключевые слова UNION, EXCEPT и INTERSECT, то таблицы, полученные в результате выполнения 1-й стадии, объединяются, вычитаются или пересекаются.

Стадия 3. Упорядочение результата

В случае если в операторе SELECT присутствует раздел ORDER BY, то строки полученной на предыдущих шагах таблицы упорядочиваются в соответствии со списком упорядочения, приведенном в разделœе ORDER BY.

Основные операторы SQL. Синтаксис и примеры использования оператора SELECT. - понятие и виды. Классификация и особенности категории "Основные операторы SQL. Синтаксис и примеры использования оператора SELECT." 2017, 2018.

Оглавление документа

Функции любой СУБД включают:

• создание, удаление, изменение базы данных (БД);
• добавление, изменение, удаление, назначение прав пользователя;
• внесение, удаление и изменение данных в БД (таблиц и записей);
• выборку данных из БД.

К первым двум функциям имеют доступ только администраторы СУБД или привилегированные пользователи. Рассмотрим, как решаются последние две задачи (на самом деле это семь задач).

Прежде чем что-либо делать с данными, нужно создать таблицы, в которых эти данные будут храниться, научиться изменять структуру этих таблиц и удалять их, если потребуется. Для этого в языке SQL существуют операторы CREATE TABLE, ALTER TABLE и DROP TABLE.

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

Оператор CREATE TABLE создает таблицу с заданным именем в текущей базе данных. Правила для допустимых имен таблицы приведены в документации. Если нет активной текущей базы данных или указанная таблица уже существует, то возникает ошибка выполнения команды.

В версии Mysql 3.22 и более поздних имя таблицы может быть указано как имя_базы_данных.имя_таблицы. Эта форма записи работает независимо от того, является ли указанная база данных текущей.

В версии Mysql 3.23 при создании таблицы можно использовать ключевое слово TEMPORARY. Временная таблица автоматически удаляется по завершении соединения, а ее имя действительно только в течение данного соединения. Это означает, что в двух разных соединениях могут использоваться временные таблицы с одинаковыми именами без конфликта друг с другом или с существующей таблицей с тем же именем (существующая таблица скрыта, пока не удалена временная таблица). В версии Mysql 4.0.2 для создания временных таблиц необходимо иметь привилегии CREATE TEMPORARY TABLES.

В версии Mysql 3.23 и более поздних можно использовать ключевые слова IF NOT EXISTS для того, чтобы не возникала ошибка, если указанная таблица уже существует. Следует учитывать, что при этом идентичность структур этих таблиц не проверяется.

Каждая таблица представлена набором определенных файлов в директории базы данных.

1.1. Синтаксис

CREATE TABLE
имя_таблицы [(определение_столбца,...)]
[опции_таблицы]

В выражении определение_столбца перечисляют, какие столбцы должны быть созданы в таблице. Каждый столбец таблицы может быть пустым (NULL), иметь значение по умолчанию, являться ключом или автоинкрементом. Кроме того, для каждого столбца обязательно указывается тип данных, которые будут в нем храниться. Если не указывается ни NULL, ни NOT NULL, то столбец интерпретируется так, как будто указано NULL. Если поле помечают как автоинкремент (AUTO_INCREMENT), то его значение автоматически увеличивается на единицу каждый раз, когда происходит добавление данных в таблицу и в это поле записывается пустое значение (NULL, т.е. ничего не
записывается) или 0. Автоинкремент в таблице может быть только один, и при этом он обязательно должен быть проиндексирован.

1.2. Последовательность

AUTO_INCREMENT начинается с 1. Наличие автоинкремента является одной из особенностей Mysql. Формально описание столбца (определение_столбца) выглядит так:

имя_столбца тип

Тип столбца (тип в выражении определение_столбца) может быть одним из следующих:

• целый: INT[(length)]
• действительный: REAL[(length,decimals)]
• символьный: CHAR(length) и VARCHAR(length)
• дата и время: DATE и TIME
• для работы с большими объектами: BLOB
• текстовый: TEXT
• перечислимое множество: ENUM(value1,value2,value3,...) и SET(value1,value2,value3,...)

Полный список типов смотрите в документации Mysql.

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

PRIMARY KEY (имя_индексируемого_столбца, ...)

или

KEY [имя_индекса] (имя_индексируемого_столбца,...)

или

INDEX [имя_индекса] (имя_индексируемого_столбца,...)

или

UNIQUE [имя_индекса]

или

FULLTEXT [имя_индекса]
(имя_индексируемого_столбца,...)

или

FOREIGN KEY [имя_индекса]
(имя_индексируемого_столбца,...)

или

CHECK (expr)

При задании всех этих элементов указывается список полей (столбцов), которые будут входить в индекс, ключ или ограничение, имя_индексируемого_столбца записывается следующим образом:

Имя_столбца [(длина_индекса)]
FOREIGN KEY, CHECK и REFERENCES на самом деле ничего не делают в Mysql. Они добавлены только для совместимости с другими SQL-серверами.

Поэтому на них мы останавливаться не будем.

Кроме всего перечисленного, при создании таблицы можно указать некоторые ее свойства (опции_таблицы), например такие:

• тип таблицы: TYPE = {BDB | HEAP | ISAM | Inno DB | MERGE | MRG_MYISAM | MYISAM }
• начальное значение счетчика автоинкремента: AUTO_INCREMENT = число
• средняя длина строк в таблице: AVG_ROW_LENGTH = число
• комментарии к таблице (строка из 60 символов): COMMENT = «строка» максимальное и минимальное предполагаемое число строк: MAX_ROWS = число и MIN_ROWS = число

И последний (опять же опциональный) элемент команды CREATE – это выражение SELECT (select_выражение). Синтаксис такой:

SELECT ...
(любое корректное выражение SELECT )

Если при создании таблицы в команде CREATE указывается выражение SELECT, то все поля, полученные выборкой, добавляются в создаваемую таблицу.

Пример 10.1. Создание таблицы Persons

Mysql>CREATE TABLE Persons
(id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR (50 ), last_name
VARCHAR (100 ), death_date INT ,
description TEXT, photo INT ,
citienship CHAR (50 ) DEFAULT "Russia");

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

Показать все базы данных:

Mysql>SHOW databases;

Сделать текущей базу данных book и показать все таблицы в ней:

Mysql>use book;
Mysql>show tables;

Показать все столбцы в таблице Persons:

Mysql> show columns from Persons;

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

Оператор DROP TABLE удаляет одну или несколько таблиц. Все табличные данные и определения удаляются, так что при работе с этой командой следует соблюдать осторожность.

Синтаксис:

DROP TABLE имя_таблицы
[, имя_таблицы,...]

В версии Mysql 3.22 и более поздних можно использовать ключевые слова IF EXISTS, чтобы предупредить ошибку, если указанные таблицы не существуют.

Опции RESTRICT и CASCADE позволяют упростить перенос программы с других СУБД. В данный момент они не задействованы.

Mysql> DROP TABLE IF EXISTS Persons,
Artifacts, test;

Пример 10.2. Использование оператора DROP TABLE

3. Оператор ALTER TABLE

Оператор ALTER TABLE обеспечивает возможность изменять структуру существующей таблицы. Например, можно добавлять или удалять столбцы, создавать или уничтожать индексы или переименовывать столбцы либо саму таблицу. Можно также изменять комментарий для таблицы и ее тип.

Синтаксис:

ALTER TABLE имя_таблицы
alter_specification
[, alter_specification ...]

Можно производить следующие изменения в таблице (все они записываются в alter_specification):

• добавление поля:

ADD определение_столбца
или
ADD (определение_столбца,
определение_столбца,...)

• добавление индексов:

ADD INDEX [имя_индекса] (имя_индексируемого_столбца,...) или ADD
PRIMARY KEY (имя_индексируемого_столбца,...) или ADD UNIQUE
[имя_индекса] (имя_индексируемого_столбца,...) или ADD FULLTEXT
[имя_индекса] (имя_индексируемого_столбца,...)

• изменение поля:

ALTER имя_столбца {SET DEFAULT literal | DROP DEFAULT } или
CHANGE старое_имя_столбца определение_столбца или
MODIFY определение_столбца

• удаление поля, индекса, ключа:

DROP имя_столбца
DROP PRIMARY KEY
DROP INDEX имя_индекса

• переименование таблицы:

RENAME новое_имя_таблицы

• переупорядочение полей таблицы:

ORDER BY поле
или
опции_таблицы

Если оператор ALTER TABLE используется для изменения определения типа столбца, но DESCRIBE имя_таблицы показывает, что столбец не изменился, то, возможно, Mysql игнорирует данную модификацию по одной из причин, описанных в специальном разделе документации. Например, при попытке изменить столбец VARCHAR на CHAR Mysql будет продолжать использовать VARCHAR, если данная таблица содержит другие столбцы с переменной длиной.

Оператор ALTER TABLE во время работы создает временную копию исходной таблицы. Требуемое изменение выполняется на копии, затем исходная таблица удаляется, а новая переименовывается. Это делается для того, чтобы в новую таблицу автоматически попадали все обновления, кроме неудавшихся. Во время выполнения ALTER TABLE исходная таблица доступна для чтения другими клиентами.

Операции обновления и записи в этой таблице приостанавливаются, пока не будет готова новая таблица. Следует отметить, что при использовании любой другой опции для ALTER TABLE, кроме RENAME, Mysql всегда будет создавать временную таблицу, даже если данные, строго говоря, и не нуждаются в копировании (например, при изменении имени столбца).

Пример 10.3. Добавим в созданную таблицу Persons поле для записи года рождения человека:

Mysql> ALTER TABLE Persons
ADD bday INTEGER AFTER last_name;

Пример 10.3. Добавление в таблицу Persons поля для записи года рождения человека

Итак, мы научились работать с таблицами: создавать, удалять и изменять их.

Теперь разберемся, как делать то же самое с данными, которые в этих таблицах хранятся.

4. Оператор SELECT

Оператор SELECT применяется для извлечения строк, выбранных из одной или нескольких таблиц. То есть с его помощью мы задаем столбцы или выражения, которые надо извлечь (select_выражения), таблицы (table_references), из которых должна производиться выборка, и, возможно, условие (where_definition), которому должны соответствовать данные в этих столбцах, и порядок, в котором эти данные нужно выдать.

Кроме того, оператор SELECT можно использовать для извлечения строк, вычисленных без ссылки на какую-либо таблицу. Например, чтобы вычислить, чему равно 2*2, нужно просто написать
Mysql> SELECT 2*2;

Упрощенно структуру оператора SELECT можно представить следующим образом:
SELECT select_выражение1, select_выражение2,
...

Квадратные скобки означают, что использование находящегося в них оператора необязательно, вертикальная черта | означает перечисление возможных вариантов. После ключевого слова ORDER BY указывают имя столбца, число (целое беззнаковое) или формулу и способ упорядочения (по возрастанию – ASC, или по убыванию – DESC). По умолчанию используется упорядочение по возрастанию.

Когда в select_выражении мы пишем «*», это значит выбрать все столбцы. Кроме «*» в select_выражения могут использоваться функции типа max, min и avg.

Пример 10.4. Выбрать из таблицы Persons все данные, для которых поле first_name имеет значение "Александр":

Пример 10.4. Использование оператора SELECT

Выбрать название и описание (title, description) артефакта под номером 10:

5. Оператор INSERT

Оператор INSERT вставляет новые строки в существующую таблицу. Оператор имеет несколько форм. Параметр имя_таблицы во всех этих формах задает таблицу, в которую должны быть внесены строки. Столбцы, для которых задаются значения, указываются в списке имен столбцов (имя_столбца) или в части SET.

Синтаксис:

VALUES (выражение,...),(...),...
Эта форма команды INSERT вставляет строки в соответствии с точно указанными в команде значениями. В скобках после имени таблицы перечисляются столбцы, а после ключевого слова VALUES – их значения.

Например:

Mysql> INSERT INTO Persons
(last_name, bday) VALUES
("Иванов", "1934");

вставит в таблицу Persons строку, в которой значения фамилии (last_name) и даты рождения (bday) будут заданы соответственно как «Иванов» и
«1934».

• INSERT

имя_таблицы [(имя_столбца,...)]
SELECT ...

Эта форма команды INSERT вставляет строки, выбранные из другой таблицы или таблиц.

Например:

вставит в таблицу Artifacts в поле «автор» (author) значение идентификатора, выбранного из таблицы Persons по условию, что фамилия человека Иванов.

INSERT
имя_таблицы
SET имя_столбца=выражение,
имя_столбца=выражение, ...

Например:

Mysql> INSERT INTO Persons
SET last_name="Петров",
first_name="Иван";

Эта команда вставит в таблицу Persons в поле last_name значение «Петров», а в поле first_name – строку «Иван».

Форма INSERT... VALUES со списком из нескольких значений поддерживается в версии Mysql 3.22.5 и более поздних. Синтаксис выражения имя_столбца=выражение поддерживается в версии Mysql 3.22.10 и более поздних.

Действуют следующие соглашения.

• Если не указан список столбцов для INSERT... VALUES или INSERT... SELECT, то величины для всех столбцов должны быть определены в списке VALUES() или в результате работы SELECT. Если порядок столбцов в таблице неизвестен, для его получения можно использовать DESCRIBE имя_таблицы.
• Любой столбец, для которого явно не указано значение, будет установлен в свое значение по умолчанию. Например, если в заданном списке столбцов не указаны все столбцы в данной таблице, то не упомянутые столбцы устанавливаются в свои значения по умолчанию.
• Выражение expression может относиться к любому столбцу, который ранее был внесен в список значений. Например, можно указать следующее:

Mysql>
VALUES (15 ,col1*2 );

Но нельзя указать:

Mysql> INSERT INTO имя_таблицы (col1,col2)
VALUES (col2*2 ,15 );

Мы еще не обсудили три необязательных параметра, присутствующих во всех трех формах команды: LOW_PRIORITY, DELAYED и IGNORE.

Параметры LOW_PRIORITY и DELAYED используются, когда с таблицей работает большое число пользователей. Они предписывают устанавливать приоритет данной операции перед операциями других пользователей. Если указывается ключевое слово LOW_PRIORITY, то выполнение данной команды INSERT будет задержано до тех пор, пока другие клиенты не завершат чтение этой таблицы. В этом случае клиент должен ожидать, пока данная команда вставки не будет завершена, что в случае интенсивного использования таблицы может потребовать значительного времени. В противоположность этому команда INSERT DELAYED позволяет данному клиенту продолжать операцию сразу же, независимо от других пользователей.

Если в команде INSERT указывается ключевое слово IGNORE, то все строки, имеющие дублирующиеся ключи PRIMARY или UNIQUE в этой таблице, будут проигнорированы и не внесены в таблицу. Если не указывать IGNORE, то данная операция вставки прекращается при обнаружении строки, имеющей дублирующееся значение существующего ключа.

6. Оператор UPDATE

Синтаксис:

Оператор UPDATE обновляет значения существующих столбцов таблицы в соответствии с введенными значениями. В выражении SET указывается, какие именно столбцы следует модифицировать и какие величины должны быть в них установлены. В выражении WHERE, если оно присутствует, задается, какие строки подлежат обновлению. В остальных случаях обновляются все строки. Если задано выражение ORDER BY, то строки будут обновляться в указанном в нем порядке.

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

Если указывается ключевое слово IGNORE, то команда обновления не будет прервана, даже если возникнет ошибка дублирования ключей. Строки, из-за которых возникают конфликтные ситуации, обновлены не будут.

В дистрибутив PHP входит расширение, содержащее встроенные функции для работы с базой данных Mysql. В этой лекции мы познакомимся с некоторыми основными функциями для работы с Mysql, которые потребуются для решения задач построения web-интерфейсов с целью отображения и наполнения базы данных. Возникает вопрос, зачем строить такие интерфейсы? Для того чтобы вносить информацию в базу данных и просматривать ее содержимое могли люди, не знакомые с языком запросов SQL. При работе с web-интерфейсом для добавления информации в базу данных человеку нужно просто ввести эти данные в html-форму и отправить их на сервер, а наш скрипт сделает все остальное. А для просмотра содержимого таблиц достаточно просто щелкнуть по ссылке и зайти на нужную страницу.

Для наглядности будем строить эти интерфейсы для таблицы Artifacts, в которой содержится информация об экспонатах виртуального музея информатики. В предыдущей лекции мы уже приводили структуру этой коллекции, а также ее связи с коллекциями описания персон (Persons) и изображений (Images). Напомним, что каждый экспонат в коллекции Artifacts описывается с помощью следующих характеристик:

• название (title);
• автор (author);
• описание (description);
• альтернативное название (alternative);
• изображение (photo).

Название и альтернативное название являются строками менее чем 255 символов длиной (т.е. имеют тип VARCHAR(255)), описание - текстовое поле (имеет тип TEXT), а в полях «автор» и «изображение» содержатся идентификаторы автора из коллекции Persons и изображения экспоната из коллекции Images соответственно.

И над данными таблиц.

Язык SQL называют встроенным , т.к. он содержит функций полноценного языка разработки, а ориентируется на доступ к данным, вследствие чего он входит в состав средств разработки приложений. Стандарты языка SQL поддерживают языки программирования Pascal, Fortran, COBOL, С и др.

Существует 2 метода использования встроенного SQL :

• статическое использование языка (статический SQL ) – в тексте программы содержатся вызовы функций SQL, которые включают в исполняемый модуль после компиляции.
• динамическое использование языка (динамический SQL ) – динамическое построение вызовов функций SQL и их интерпретация. Например , можно обратиться к данным удаленной БД в процессе выполнения программы.

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

Определение 1

Представление – это таблица, которая формируется в результате выполнения запроса.

Основные операторы языка запросов SQL

Операторы языка SQL условно разделяются на 2 подъязыка :

1. Язык определения данных DDL ;
2. Язык манипулирования данными DML .

В таблице символом * помечены специфические операторы языка.

Рассмотрим важнейшие операторы SQL.

Оператор создания таблицы:

Имя таблицы, которая создается, и имя хотя бы одного столбца (поля) являются обязательными операндами. Для имени столбца необходимо указать тип данных, которые будут в нем храниться.

Для отдельных полей можно указывать дополнительные правила контроля значений, которые в них вводятся. Например, NOT NULL обозначает, что поле не может быть пустым и в него должно быть введено значение.

Пример 1

Для создания таблицы books каталога книг, которая содержит поля:

type – тип книги,

name – название книги,

price – цена книги

оператор может выглядеть следующим образом:

Оператор изменения структуры таблицы:

При изменении структуры таблицы можно добавлять (ADD ), изменять (MODIFY ) или удалять (DROP ) один или несколько столбцов таблицы. Правила записи данного оператора такие же, как и для оператора CREATE TABLE . Чтобы удалить столбец указывать не нужно.

Пример 2

Для добавления к таблице books поля number , в котором будет храниться количество книг, можно записать оператор:

Оператор удаления таблицы:

Пример 3

Например, чтобы удалить существующую таблицу с именем books достаточно воспользоваться оператором:

Оператор создания индекса:

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

Необязательная опция UNIQUE отвечает за обеспечение уникальности значений во всех столбцах, которые указаны в операторе.

ASC задает автоматическую сортировку значений в столбцах в порядке возрастания (по умолчанию), а DESC – в порядке убывания.

Оператор удаления индекса:

Оператор создания представления:

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

Оператор удаления представления:

Оператор выборки записей:

Оператор SELECT производит выборку и вычисления над данными из одной или нескольких таблиц. Результат выполнения оператора – ответная таблица, которая содержит (ALL ) или не содержит (DISTINCT ) строки, которые повторяются.

Операнд FROM содержит список таблиц, из которых берутся записи для отбора данных.

Оператор изменения записей:

Новые значения полей в записях могут не содержать значений (NULL ) или вычисляться согласно арифметическому выражению.

Оператор вставки новых записей:

В первой записи оператора INSERT вводятся новые записи с заданными значениями в столбцах.

Во втором записи оператора INSERT вводятся новые строки, отобранные из другой таблицы через предложение SELECT .

Оператор удаления записей:

В результате выполнения оператора удаляются из указанной таблицы строки, удовлетворяющие условию, которое определено необязательным операндом WHERE . Если операнд WHERE не указан, то удаляются все записи таблицы.

Основу языка SQL составляют операторы, условно разбитые не несколько групп по выполняемым функциям. Можно выделить следующие группы операторов (перечислены не все операторы SQL):

Операторы ddl (Data Definition Language)

Операторы DDL - операторы определения объектов базы данных

CREATE SCHEMA - создать схему базы данных

DROP SHEMA - удалить схему базы данных

CREATE TABLE - создать таблицу

ALTER TABLE - изменить таблицу

DROP TABLE - удалить таблицу

CREATE DOMAIN - создать домен

ALTER DOMAIN - изменить домен

DROP DOMAIN - удалить домен

CREATE COLLATION - создать последовательность

DROP COLLATION - удалить последовательность

CREATE VIEW - создать представление

DROP VIEW - удалить представление

Операторы dml (Data Manipulation Language)

Операторы DML - операторы манипулирования данными

SELECT - отобрать строки из таблиц

INSERT - добавить строки в таблицу

UPDATE - изменить строки в таблице

DELETE - удалить строки в таблице

COMMIT - зафиксировать внесенные изменения

ROLLBACK - откатить внесенные изменения

Операторы защиты и управления данными

CREATE ASSERTION - создать ограничение

DROP ASSERTION - удалить ограничение

GRANT - предоставить привилегии пользователю или приложению на манипулирование объектами

REVOKE - отменить привилегии пользователя или приложения

Кроме того, есть группы операторов установки параметров сеанса, получения информации о базе данных, операторы статического SQL, операторы динамического SQL. Наиболее важными для пользователя являются операторы манипулирования данными (DML).

Примеры использования операторов манипулирования данными

INSERT - вставка строк в таблицу

Пример 1 . Вставка одной строки в таблицу:

VALUES (4, "Иванов");

Пример 2 . Вставка в таблицу нескольких строк, выбранных из другой таблицы (в таблицу TMP_TABLE вставляются данные о поставщиках из таблицы P, имеющие номера, большие 2):

TMP_TABLE (PNUM, PNAME)

SELECT PNUM, PNAME

WHERE P.PNUM>2;

UPDATE - обновление строк в таблице

Пример 3 . Обновление нескольких строк в таблице:

SET PNAME = "Пушников"

WHERE P.PNUM = 1;

DELETE - удаление строк в таблице

Пример 4 . Удаление нескольких строк в таблице:

WHERE P.PNUM = 1;

Пример 5 . Удаление всех строк в таблице:

Примеры использования оператора SELECT

Оператор SELECT является фактически самым важным для пользователя и самым сложным оператором SQL. Он предназначен для выборки данных из таблиц, т.е. он, собственно, и реализует одно их основных назначение базы данных - предоставлять информацию пользователю.

Оператор SELECT всегда выполняется над некоторыми таблицами, входящими в базу данных.

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

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

Синтаксис оператора SELECT имеет следующий вид:

SELECT <список атрибутов>/* FROM <список таблиц>

В квадратных скобках указываются элементы, которые могут в запросе отсутствовать.

Выдать список всех студентов .

SELECT * FROM student

SELECT id_st, surname FROM student

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

Выдать список оценок, которые получил студент с кодом "1" .

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

В предложении WHERE можно записывать выражение с использованием арифметических операторов сравнения (<, >, и т.д.) и логических операторов (AND, OR, NOT ) как и в обычных языках программирования.

Наряду с операторами сравнения и логическими операторами для составления условий в языке SQL (из-за специфики области применения) существуют ряд специальных операторов, которые, как правило, не имеют аналогов в других языках. Вот эти операторы :

• IN – вхождение в некоторое множество значений;
• BETWEEN – вхождение в некоторый диапазон значений;
• LIKE – проверка на совпадение с образцом;
• IS NULL – проверка на неопределенное значение.

Оператор IN используется для проверки вхождения в некоторое множество значений. Так, запрос

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

Того же результата можно добиться, используя оператор BETWEEN :

Выдать список всех студентов, фамилии которых начинаются с буквы А .

В этом случае удобно использовать оператор LIKE .

Оператор LIKE применим исключительно к символьным полям и позволяет устанавливать, соответствует ли значение поля образцу. Образец может содержать специальные символы:

_ (символ подчеркивания) – замещает любой одиночный символ;

% (знак процента) – замещает последовательность любого числа символов.

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

• MIN – минимальное значение в столбце;
• MAX – максимальное значение в столбце;
• SUM – сумма значений в столбце;
• AVG – среднее значение в столбце;
• COUNT – количество значений в столбце, отличных от NULL.

Следующий запрос считает среднее среди всех баллов, полученных студентами на экзаменах.

SELECT AVG(mark) FROM mark_st

Естественно, можно использовать агрегатные функции совместно с предложением WHERE :

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

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