Язык SQL

Типы данных SQL Типы данных, используемые в стандартном SQL, можно подразделить на следующие группы: строковые типы; числовые типы; типы для представления даты и времени.

Строковые типы Определены два строковых типа: символьные строки фиксированной длины; Данные всегда занимают один и тот же объем памяти, определяемый при объявлении поля, независимо от реального размера строки, занесенной в поле CHARACTER (n), где n – длина строки символьные строки переменной длины; Длина строк переменной длины не является постоянной для всех данных, а зависит от реального размера строки VARCHAR(n), n – число, определяющее максимально возможную длину строки

Числовые типы Числовые типы подразделяются на: целочисленные типы; INTEGER (от – до ); SMALLINT (– до ) вещественные типы с фиксированной точкой; Предназначен для точного представления дробных чисел. DECIMAL (n, m), где n – длина числового значения; m – количество знаков, после от десятичной точки. вещественные типы с плавающей точкой; FLOAT – числа с одинарной точностью; DOUBLE – числа с двойной точностью.

Типы для представления даты и времени DATE – используется для хранения даты; TIME – используется для хранения времени; TIMESTAMP – хранит дату и время; INTERVAL – хранит промежуток времени между двумя датами или между двумя моментами времени.

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

Оператор CREATE TABLE Для создания таблицы используется оператор CREATE TABLE. Синтаксис этого оператора имеет следующий вид: CREATE TABLE имя таблицы (имя_поля_1 тип данных,имя_поля_2 тип данных, … … … … имя_поля_N тип данных)

Пример Создание таблицы ФИЗИЧЕСКИЕ ЛИЦА CREATE TABLE Физические лица (Код_физического_лица Integer, ИмяVARCHAR (25), Фамилия VARCHAR (25), Отчество VARCHAR (25), Дата рождения DATE, Адрес VARCHAR (50), Телефон VARCHAR(25))

Оператор ALTER TABLE Созданная таблица может быть модифицирована с использованием оператора ALTER TABLE. С помощью этого оператора можно добавлять и удалять поля таблицы, изменять тип данных полей, добавлять и удалять ограничения. В общем виде синтаксис оператора ALTER TABLE выглядит следующим образом ALTER TABLE имя_таблицы [MODIFY] [имя_поля тип_данных] [ADD] [имя_поля тип_данных] [DROP] [имя_поля ]

Действие, выполняемое оператором ALTER TABLE, определяется ключевым словом, указываемым после имени таблицы: MODIFY – изменяет определение поля; ADD – добавляет новое поле в таблицу; DROP – удаляет поле из таблицы.

Пример Добавить в таблицу ФИЗИЧЕСКИЕ ЛИЦА поле, в котором будет содержаться адрес электронной почты сотрудника ALTER TABLE Физические_лица ADD( CHARACTER (25))

Пример Если же требуется изменить тип данных существующего поля, то следует использовать оператор ALTER TABLE в паре с ключевым словом MODIFY ALTER TABLE имя_таблицы MODIFY (имя_поля, тип_данных)

Пример Удаление существующего поля выполняется вызовом оператора ALTER TABLE с ключевым словом DROP: ALTER TABLE имя_таблицы DROP (имя_поля).

Оператор DROP TABLE Для удаления таблиц используется оператор DROP TABLE. Синтаксис этого оператора имеет следующий вид: DROP TABLE имя_таблицы [RESTRICT | CASCADE] Если при вызове оператора DROP TABLEиспользуется вое слово RESTRICT и на удаляемую таблицу ссылается какое-либо представление или ограничение, то при выполнении оператора удаления таблицы будет сгенерировано сообщение об ошибке. Если же использовать ключевое слово CASCADE, то удаление таблицы будет выполнено и вместе с таблицей будут удалены все ссылающиеся на нее представления и ограничения.

Задание ограничений. Ограничения используются для того, чтобы обеспечить достоверность и непротиворечивость информации в БД. Существует достаточно большое количество различного рода ограничений, из которых мы рассмотрим лишь основные: ограничение NOT NULL; ограничение первичного ключа; ограничение UNIQUE; ограничение внешнего ключа; ограничение CHECK.

Ограничение NOT NULL Ограничение NOT NULL может быть установлено для любого поля реляционной таблицы. При наличии этого ограничения запрещается ввод значений NULL в поле, для которого это ограничение установлено. Ограничение NOT NULL устанавливается при создании таблицы с помощью оператора CREATE TABLE.

Чтобы задать ограничение NOT NULL для некоторого поля следует просто указать NOT NULL после указанного типа поля: CREATE TABLE имя таблицы ( имя_поля_1 тип_данных_ NOT NULL, имя_поля_2 тип данных NULL, … … … имя_поля_N тип данных NOT NULL)

Пример В таблице, содержащей личные данные о сотрудниках организации, можно задать ограничение NOT NULL для полей в которых будут содержаться имя и фамилия сотрудника. Поэтому оператор создания таблицы ФИЗИЧЕСКИЕ ЛИЦА следует видоизменить следующим образом CREATE TABLE Физические лица ( Код_физического_лица Integer, ИмяVARCHAR (25) NOT NULL, Фамилия VARCHAR (25) NOT NULL, Отчество VARCHAR (25), Дата рождения DATE, Адрес VARCHAR (50), Телефон VARCHAR(25))

Ограничение первичного ключа Первичные ключи указываются при создании таблицы. Так как поля, входящие в состав первичного ключа, не могут принимать значение NULL, то для них обязательным является ограничение NOT NULL

Ограничение первичного ключа может быть задано двумя путями. В том случае когда первичный ключ состоит только из одного поля, то он может быть задан с помощью ключевых слов PRIMARY KEY, указываемых при описании поля в операторе CREAT TABLE CREATE TABLE имя таблицы ( имя_поля_1 тип_данных_ NOT NULL PRIMARY KEY, имя_поля_2 тип данных, … … … имя_поля_N тип данных NOT NULL)

Первичный ключ может быть задан в конце описания таблицы, после описания всех полей. Для этого также используется ключевая фраза PRIMARY KEY, после которой в круглых скобках указывается имя поля, составляющего первичный ключ: CREATE TABLE имя таблицы ( имя_поля_1 тип_данных_ NOT NULL, имя_поля_2 тип данных, … … … имя_поля_N тип данных NOT NULL PRIMARY KEY (имя_поля_1))

Второй способ особенно удобен для задания составных первичных ключей. В этом случае в скобках следует указать через запятую все поля, составляющие первичный ключ: CREATE TABLE имя таблицы ( имя_поля_1 тип_данных_ NOT NULL, имя_поля_2 тип данных, имя_поля_3 тип_данных_ NOT NULL, … … … имя_поля_N тип данных NOT NULL PRIMARY KEY (имя_поля_1, имя_поля_3))

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

CREATE TABLE имя таблицы ( имя_поля_1 тип_данных_ NOT NULL PRIMARY KEY, имя_поля_2 тип данных UNIQUE, имя_поля_3 тип_данных_ NOT NULL, … … … имя_поля_N тип данных NOT NULL UNIQUE)

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

CREATE TABLE имя таблицы ( имя_поля_1 тип_данных_ NOT NULL PRIMARY KEY, имя_поля_2 тип данных имя_поля_3 тип_данных_ NOT NULL, … … … имя_поля_N тип данных NOT NULL UNIQUE, UNIQUE (имя_поля 2, имя_поля_3))

Ограничение внешнего ключа Ограничение внешнего ключа является основным механизмом для поддержания ссылочной целостности базы данных. Поле, определяемое в качестве внешнего ключа, используется для ссылки на поле другой таблицы, обычно называющееся родительским ключом, а таблица, на которую внешний ключ ссылается, называется родительской таблицей (родительский ключ часто является первичным ключом родительской таблицы). Типы полей внешнего и родительского ключа обязательно должны быть идентичны. А вот имена полей могут быть разными. Однако во избежание путаницы желательно и имена полей для внешнего и родительского ключей задавать одинаковыми. Внешний ключ не обязательно должен состоять только из одного поля. Подобно первичному ключу, внешний ключ может состоять из любого числа полей, которые обрабатываются как единый объект. Поля родительского ключа, на который ссылается составной внешний ключ, должны следовать в том же порядке, что и во внешнем ключе. Когда поле таблицы является внешним ключом, оно определенным образом связано с таблицей, на которую этот ключ ссылается.

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

СОТРУДНИКИ – содержит информацию о профессиональных данных сотрудников; ФИЗИЧЕСКИЕ ЛИЦА - содержит информацию о личных данных сотрудников; ДОЛЖНОСТИ – содержит информацию о должностях организации. Основной таблицей является таблица СОТРУДНИКИ, которая ссылается на две другие таблицы и, соответственно, должна иметь два внешних ключа. В качестве родительских ключей в таблицах ФИЗИЧЕСКИЕ ЛИЦА и ДОЛЖНОСТИ используются первичные ключи.

Ограничения внешнего ключа (FOREIGN KEY) может быть задано либо в операторе CREAT_TABLE, либо с помощью оператора ALTER Синтаксис ограничения FOREIGN_KEY имеет следующий вид: FOREIGN_KEY имя_внешнего ключа (список полей внешнего ключа) REFERENCES имя_родительской_таблицы (список полей родительского ключа)

Пример CREATE TABLE groups (id NUMBER CONSTRAINT groups_pk PRIMARY KEY, num_gr CHAR(8) not null, form CHAR(2) not null, CONSTRAINT groups_uk UNIQUE (num_gr, form), CHECK CHAR(2)); CREATE TABLE students (id NUMBER CONSTRAINT stud_pk PRIMARY KEY, surname CHAR(100) not null, name CHAR(100) not null, patron CHAR(100) not null, gr_id number not null, CONSTRAINT stud_gr_id_fk FOREIGN KEY (gr_id) REFERENCES groups( id)); gr_id – внешний ключ, ссылающийся на первичный ключ таблицы GROUPS. Каждый студент из таблицы STUDENTS учится в какой-то группе из таблицы GROUPS.

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

CREATE TABLE Физические лица ( Код_физического_лица Integer NOT NULL PRIMERY KEY, ИмяVARCHAR (25) NOT NULL, Фамилия VARCHAR (25) NOT NULL, Отчество VARCHAR (25), Дата рождения DATE, Адрес VARCHAR (50), Телефон VARCHAR(25)) CREATE TABLE ДОЛЖНОСТИ (Код_должности Integer NOT NULL PRIMERY KEY, Должность VARCHAR (25) NOT NULL UNIOUE, Разряд Integer NOT NULL, Зарплата DECIMAL (7,3) NOT NULL) CREATE TABLE CОТРУДНИКИ (Код_сотрудника Integer NOT NULL PRIMERY KEY, Код_должности Integer, Код_физического_лица Integer NOT NULL, Рейтинг DECIMAL (4,2), Дата_приема DATE NOT NULL, Дата_увольнения DATE, FOREIGN_KEY Физ_ВК (Код_физического_лица) REFERENCES Физические_лица (Код_физического_лица) FOREIGN_KEY Долж_ВК (Код_должности) REFERENCES Должности (Код_должности))

Ограничение CHECK Ограничение CHECK используется для проверки допустимости данных, вводимых в поле таблицы. Ограничение CHECK состоит из ключевого слова CHECK, сопровождаемого предложением предиката, который использует указанное поле. Любая попытка модифицировать или вставить значение поля, которое могло бы сделать этот предикат неверным, будет отклонена. Значение ограничения CHECK производится при создании таблицы. Для этого после описания полей таблицы указывается ключевая фраза: CONSTRAINT имя_ограничения CHECK (ограничение).

Пример В рассматриваемом нами примере базы данных сотрудников организации ограничение может быть задано, например для поля «Разряд» таблицы ДОЛЖНОСТИ. Допустим, разряд не может превышать 20. Тогда оператор создания таблицы ДОЛЖНОСТИ, в котором задано это ограничение, будет иметь следующий вид CREATE TABLE ДОЛЖНОСТИ ( Код_должности Integer NOT NULL PRIMERY KEY, Должность VARCHAR (25) NOT NULL UNIOUE, Разряд Integer NOT NULL, Зарплата DECIMAL (7,2) NOT NULL CONSTRAINT CHK_RATE CHECK (Разряд

Задание значений по умолчанию. Для полей таблицы можно задавать значения по умолчанию, которые будут заноситься в поля при добавлении новой записи в таблицу, если значения этих полей не определены. Для задания значения по умолчанию используется директива DEFAULT, которая указывается в команде CREAT TABLE при описании поля, для которого устанавливается значение по умолчанию:

CREAT TABLE ( … имя_поля_N тип_данных DEFAULT = значение_по_умолчанию В рассматриваемом примере значение по умолчанию может быть установлено, например, для поля «Рейтинг» таблицы СОТРУДНИКИ CREATE TABLE CОТРУДНИКИ ( Код_сотрудника Integer NOT NULL PRIMERY KEY, Код_должности Integer, Код_физического_лица Integer NOT NULL, Рейтинг DECIMAL (4,2) DEFAULT=0, Дата_приема DATE NOT NULL, …)

Манипулирование данными Для манипулирования данными, хранящимися в базе данных, используется группа операторов SQL, выделяемых в качестве отдельного типа команд, называемых языком манипулирования данными (DML – Data Manipulation language). С помощью операторов DML пользователь может загружать в таблицы новые данные, модифицировать и удалять существующие данные.

В языке SQL определены только три основные операторы DML: INSERT; UPDATE; DELETE.

Добавление в таблицу новой информации Процесс ввода в таблицу БД новой информации обычно называется загрузкой данных. Для загрузки данных используется оператор INSERT.

Добавление к таблице новой записи Для добавления к таблице новой записи используется следующая синтаксическая форма оператора INSERT: INSERT INTO имя_таблицы VALUES (значение_1, значение_2, …, значение_N) При использовании данной формы оператора INSERT список VALUES должен содержать количество значений, равное количеству полей таблицы. Причем тип данных каждого из значений, указываемых в списке VALUES, должен совпадать с типом данных поля, соответствующему этому значению. Значения, относящиеся к символьным типам и датам, должны быть заключены в апострофы. В списке значений может также использоваться значение NULL.

Пример Для добавления новой записи в таблицу ДОЛЖНОСТИ следует использовать следующий оператор INSERT: INSERT INTO Должности VALUES (12, Ведущий программист, 12, )

Ввод данных в отдельные поля таблицы Добавлении данных в таблицу можно заполнять не все поля, а лишь некоторые из них. В этом случае используется следующая синтаксическая форма оператора INSERT: INSERT INTO имя_таблицы (имя_поля 1, имя_поля 2, …, имя поля_N) VALUES (значение_1, значение_2, …, значение_N)

ПРИМЕР При добавлении информации о новом сотруднике в таблицу ФИЗИЧЕСКИЕ ЛИЦА необходимо указать только информацию о полном имени сотрудника INSERT INTO Физические_лица (Код_физического_лица, Имя, Фамилия, отчество) VALUES (234, Иванов, Федор,Михайлович) При выполнении данного оператора во все остальные поля будет занесено значение NULL.

Занесение в таблицу данных, содержащихся в другой таблице Объединяя операторы INSERT и SELECT, можно добавить в таблицу данные, полученные в результате выполнения запроса из другой таблицы (таблиц). Синтаксис оператора INSERT в этом случае будет иметь следующий вид INSERT INTO имя_таблицы (имя_поля_1, имя_поля_2, …, имя поля_N) Select [* | имя_поля_1, имя_поля_2, …, имя_поля_N] FROM имя таблицы WHERE условие

Изменение данных, хранящихся в таблице Для изменения данных, уже занесенных в таблицу, используется оператор UPDATE. Данный оператор не добавляет новых записей в таблицу, а заменяет существующие записи на новые. Оператор UPDATE может быть применен как к одному полю таблицы (наиболее часто используемый случай), так и к нескольким полям. Количество изменяемых записей зависит от потребностей пользователя – с помощью UPDATE можно изменить как одну, так и несколько записей (вплоть до изменения значения всех записей, содержащихся в таблице).

Модификация данных в одном поле Для изменения данных только в одном из полей таблицы используется наиболее простая форма оператора UPDATE, имеющая следующий вид: UPDATE имя_таблицы SET имя_поля = значение [WHERE условие]

Пример UPDATE Физические лица SET телефон = (495) WHERE Код_физического_лица = 16 Данный оператор изменит значение номера телефона только для записи, соответствующей сотруднику, зарегистрированному в БД под номером 16. Если бы мы не задали ограничительного условия в приведенном выше операторе, то значение номера телефона было бы изменено для всех записей в таблице

Изменение значений в нескольких полях таблицы С помощью оператора UPDATE можно изменять значения в нескольких полях таблицы. Для этого следует указать после ключевого слова SET одно, а несколько полей: UPDATE имя_таблицы SET имя_поля_1 = значение_1 имя_поля_2 = значение_2 … имя_поля_N = значение_N [WHERE условие] Использование оператора в данной форме ничем не отличается от рассмотренного ранее. Здесь точно так же нужно быть очень осторожным при формировании условия

Удаление данных из таблицы Удаление данных из таблицы выполняется с помощью оператора DELETE. Данный оператор полностью удаляет всю запись, а не данные отдельных полей. Синтаксис оператора DELETE имеет следующий вид: DELETE FROM имя_таблицы [WHERE условие] Удаляемые записи определяются в соответствии с условием, заданным с помощью необязательного предложения WHERE. При отсутствии предложения WHERE в операторе DELETE данные будут удалены из всей таблицы.

Использование SQL для выборки данных из таблицы Язык запросов DQL. Язык запросов, являющийся одной из категорий языка SQL, состоит всего из одного оператора SELECT. Эта команда вместе с множеством опций и предложений используется для формирования запрос к базе данных. Запросы формируются для извлечения из таблиц баз данных информации, соответствующей некоторым требованиям, задаваемым пользователем. Оператор SELECT не используется автономно, вместе с ним обязательно должны задаваться уточняющие предложения. Предложения используемые с командой SELECT, могут быть обязательными и дополнительными. Обязательным является только одно предложение FROM, без которого оператор SELECT не может использоваться.

Простейшая форма оператора SELECT. SELECT {* | ALL | DISTINCT field1, field2,..., fieldnN} FROM table1 {, table2,.., tableN} Здесь за ключевым словом SELECT следует список полей, которые возвращаются в результате выполнения запроса: имена полей в списке разделяются через запятую; для выборки всех полей таблицы (таблиц) используется символ подстановки «*» опция ALL (задана по умолчанию) означает, что результат выборки будет содержать все записи, включая дублирующие друг друга; при использовании опции DISTINCT результат запроса не будет содержать дублирующих строк.

Пример 1. Выборка всей информации всей информации из таблицы «Товары» SELECT * FROM Товары 2. Запроса возвращающий из этой таблицы значения только трех полей: «Код товара», «Наименование» и «Цена»: SELECT [Код товара], Наименование, Цена FROM Товары

Пример 3. В таблице несколько товаров с одинаковым наименованием, а мы хотим просто знать наименования всех товаров, которые есть в таблице (без повторений). Тогда используем опцию DISTINCT SELECT DISTINCT Наименование FROM Товары

Задание условий при выборке данных Для ограничения отбираемой из базы данных информации оператор SELECT позволяет использовать условие, которое задается с помощью предложения WHERE. В случае реализации условной выборки оператор SELECT имеет следующий вид SELECT {* | ALL | DISTINCT field1, field2,..., fieldnN} FROM table1 {, table2,.., tableN} WHERE.условие Специальные операторы языка SQL, применяемые для задания условия можно разделить на следующие группы: операторы сравнения логические операторы операторы объединения операции отрицания

Логические операторы К логическим относятся операторы, в которых для задания ограничений используются специальные ключевые слова. В SQL определены следующие логические операторы: IS NULL- сравнения текущего значения со значением NULL, BETWEEN…AND- отбор записей, в которых значения поля находятся внутри заданного диапазона, IN- выборка значений, в которых значение некоторого поля соответствует хотя бы одному из значений заданного списка (Страна IN ('Беларусь', 'Украина', 'Казахстан')

LIKE- применяется для сравнения значения поля со значением, заданным при помощи шаблонов. Для задания шаблонов используются два символа: знак процента «%» - заменяет последовательность любой (в том числе нулевой) длины; символ подчеркивания «_» заменяет любой единичный символ. WHERE Фамилия LIKE 'М%