5 Язык sql

Идентификаторы

Идентификаторы языка SQL предназначены для обозначения объектов в БД, например таблиц (отношений) или полей (атрибутов). Идентификаторы пользователя должны соответствовать следующим правилам:

• идентификатор может содержать символы [A..Z, a..z, 0..9] (без пробелов);

• идентификатор должен начинаться с буквы;

• идентификатор не может иметь длину более 128 символов;

• идентификатор не может совпадать с зарезервированными словами.

Типы данных

Основными типами данных в стандарте SQL сведены в таблице 1.

Таблица 1. – Типы данных в стандарте SQL

Тип данных	Объявление
Символьный постоянной длины	char (<длина>)
Символьный переменной длины	varchar (<максимальная длина>)
Целый	int
С плавающей точкой	decimal, numeric
Дата	Date /*Значения вводятся в кавычках */

При реализации стандарта в различных СУБД возможны другие названия типов, например, time, dateTime.

В interbase есть еще двоичный тип не ограниченного размера blob. В других СУБД есть text и image.

Скалярные операторы

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

Наиболее простые скалярные операторы – арифметические операторы (*, +, -, /). Кроме того, возможно использование функций, приведенных в таблице 2.

Таблица 2. – Основные функции стандарта SQL

Функция	Назначение	Пример
1	2	3
Char_length	Возвращает длину строки, переданной функции в качестве аргумента	Char_length(‘строка’). Результат работы: 6
Cast	Преобразует значение выражения одного типа к выражению другого типа	Cast (‘500’ as integer) Результат работы: 500
||	Конкатенация строк	‘ab’||’12’ Результат работы: ‘ab12’
Current_user или User	Функция возвращает символьную строку, соответствующую текущему пользователю, подключенному к БД
Lower	Преобразование всех символов строки к нижнему регистру	lower(‘ABcdD’) Результат работы: ‘abcdd’

Продолжение таблицы 2

1	2	3
Upper	Преобразование всех символов строки к верхнему регистру	upper(‘ABcdD’) Результат работы: ‘ABCDD’
Position	Функция возвращает позицию одной строки в пределах другой строки.	Position (‘ab’, ‘dfabfg’) Результат работы: 3
Substring	Функция выполняет выделение подстроки из заданной строки.	Substring(‘asdwq’, 2, 3) Результат работы: ‘sdw’
Current_date, Current_time	Функции возвращают соответственно текущую дату и текущее время

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

Язык определения данных

Та часть языка SQL, которая служит для создания объектов базы данных, называется DDL (Data Definition Language). Таблицы создаются оператором

create table <имя таблицы>

(<имя поля> <тип поля> [null | not null] [default <значение по умолчанию>] [, …])

Из приведенного описания видно, что для каждого поля можно указать возможность принимать неопределенные значения. Неопределенные значения могут принимать поля любого типа. Такое значение обозначается как Null и не является каким-либо конкретным значением, так как символизирует отсутствие знаний об этом значении. Указание такой характеристики для поля не является обязательным. При ее отсутствии в различных СУБД принимается либо Null (Nullable), либо Not Null

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

Если первичный ключ состоит из одного атрибута, то конструкция primary key указывается в рамках create table сразу за определением соответствующего поля.

Если первичный ключ состоит из нескольких атрибутов, то конструкция primary key указывается после объявления последнего поля (но до закрывающейся скобки), а после нее в круглых скобках перечисляются атрибуты первичного ключа.

Все атрибуты первичного ключа должны быть объявлены как not null.

Рассмотрим примеры создания таблиц. Пусть имеются следующие отношения

Предмет (название: строка 250 символов,

сокращение: строка 20 символов);

Студент (зачетка: целое,

ФИО: строка 250 символов);

Изучает (зачетка: целое,

название: строка 250 символов,

оценка: строка 10 символов)

Создание соответствующих таблиц будет иметь вид

Create table subject

( subject_name char(250) not null primary key,

subject_short char(20)

)

Create table student

( student_num int not null primary key,

student_name char(250) not null

)

Create table study

( subject_name char(250) not null,

student_num int not null,

mark char(10),

primary key (subject_name, student_num)

)

Таблицы удаляются операцией drop table <имя таблицы>.

Некоторые СУБД позволяют изменять структуру таблиц при помощи операции alter table. Для того, чтобы удалить поле из таблицы эта операция используется следующим образом:

alter table <имя таблицы>

drop <имя поля>

Для добавления столбца в таблицу выполняется следующая конструкция:

alter table <имя таблицы>

add <имя поля> <тип поля> [null | not null]

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

alter table <имя таблицы>

add primary key (список атрибутов первичного ключа)

/*Если у таблицы уже есть первичный ключ, то будет ошибка. Первичный ключ должен быть всегда, несмотря на то, что СУБД позволяют и по другому. */

Простые запросы DML

Чтение данных из БД производится с помощью оператора SELECT. Результатом работы этого оператора является отношение. Общий формат оператора SELECT имеет следующий вид.

Select [distinct]

{* | <выражение> [as <имя>] [, ...]}

from <имя таблицы> [<псевдоним>] [, ...]

[where <условие>]

[group by <список полей> [having <условие>]]

[order by <список полей>]

Рассмотрим основные разделы запроса.

Select – устанавливает, какие поля должны присутствовать в результирующем отношении.

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

Where – указывает условие, которому должны отвечать кортежи результирующего отношения.

Group by – служит для создания групп

Having – задает условие, на основе которого группы включаются или не включаются в результирующее отношение.

Order by – определяет упорядоченность кортежей результирующего отношения при выводе.

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

При обращении к полям используется следующая конструкция <имя таблицы>.<имя поля>. Если имя поля уникально для данного запроса, то имя таблицы при обращении может быть пропущено. Вместо имени таблицы можно использовать ее псевдоним, определяемый в разделе from запроса. Кроме того, псевдонимы используются для того, чтобы таблицу можно было несколько раз использовать в одном запросе.

Рассмотрим простейший запрос. Пусть необходимо получить информацию обо всех студентах.

select * from student

Использование символа «*» в данном запросе означает, что необходимо вывести все поля таблиц раздела from.

Если необходимо получить просто список фамилий, то можно воспользоваться одним из следующих запросов:

select student.student_name from student

select t1.sudent_name

from student t1 (использование псевдонима)

select student_name

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

При выводе можно переименовать столбцы следующим образом

select student_num as field1, student_name as field2

from student

Ниже приведен пример использования выражений при построении запросов.

select

cast(student_num as char(6)) ||

student_name as single_field

from student

Теперь обратим внимание на таблицу study. Пусть необходимо узнать какие оценки получали студенты. Запрос вида

select mark from study

позволит получить результат вида

mark

отлично

хорошо

удовлетв.

отлично

хорошо

Для того, чтобы получить кортежи без повторений нужно использовать конструкцию distinct в разделе select запроса. То есть нужно написать:

select distinct mark from study

Теперь рассмотрим более подробно раздел Where. Этот раздел содержит отдельные условия, которые могут соединяться при помощи операций and и or. К условиям может применяться операция отрицания not.

Существует пять основных типов условий в запросах.

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

select subject_name

from study

where mark=’неудовл.’

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

select student_num

from study

where mark=’неудовл.’ and subject_name = ’история’

2. Диапазон. Проверяется, попадает ли результат вычисления выражения в заданный диапазон значений. Для формирования такого условия используется оператор between. Например, запрос, выводящий информацию о студентах, номера зачеток которых находятся в диапазоне от 10 до 100, имеет следующий вид.

select * from student

where student_num between 10 and 100

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

select * from study

where subject_name in

(‘история’, ‘философия’, ‘математика’)

4. Соответствие шаблону. Проверяется, отвечает ли некоторое строковое значение заданному шаблону. В языке SQL существует два специальных символа шаблона, используемых при проверке символьных значений. «%» - представляет любую последовательность из нуля или более символов. «_» - представляет любой одиночный символ. Проверка соответствия шаблону осуществляется с помощью оператора Like. Например, найдем все предметы, в названии которых встречается слово «системы».

Select * from subject

where subject_name like ‘%системы%’

5. Значение Null. Проверяется, содержит ли данное поле неопределенное значение. Для сравнения со значением Null используется оператор is null. Например, запрос, выводящий информацию о неизвестных оценках, выглядит следующим образом.

select * from study

where mark is null

Изменение содержимого БД.

Добавление кортежей в БД осуществляется с использованием оператора INSERT. Существует две формы этого оператора.

Первая форма предназначена для добавления единственного кортежа и имеет вид:

Insert into <имя таблицы> [(<имя поля> [,…]]

values (<значение> [,…])

Количество значений должно совпадать либо с количеством имен полей в запросе, либо с общим количеством полей в таблице, если список полей отсутствует. Список полей должен быть указан, если известны значения не всех полей кортежа. В этом случае неуказанным полям будет присвоено неопределенное значение (Null).

Например, выполним запрос, добавляющий информацию о том, что студент с номером зачетки 12 изучает историю, но еще не получил оценку по этому предмету.

insert into study (student_num, subject_name)

values (12, ‘история’)

Вторая форма оператора insert позволяет вставлять в таблицу несколько кортежей, полученных при помощи оператора select. В этом случае оператор имеет вид:

Insert into <имя таблицы> [(<имя поля> [,…]]

select …

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

Пусть при проектировании БД была допущена ошибка, и таблица subject не была создана изначально. Такая ситуация могла возникнуть в случае, когда изначально не было известно о существовании атрибута «сокращение». Если добавить этот атрибут в состав отношения «Изучает», то получится функциональная зависимость не от первичного ключа. Поэтому должна быть создана отдельная таблица. Для перекачивания в нее данных можно воспользоваться следующим запросом:

insert into subject (subject_name)

select distinct subject_name from study

Модификация кортежей осуществляется с помощью оператора UPDATE. Этот оператор имеет следующий формат.

Update <имя таблицы>

set <имя поля>=<значение> [, …]

[where <условие>]

Используя, этот оператор, можно изменить значения нескольких полей кортежа. Кортежи, которые подлежат изменению, задаются разделом where запроса. Формат раздела where аналогичен разделу where оператора select. Если раздел where пропущен в запросе update, то будут модифицированы все кортежи отношения.

Построим запрос, который вместо всех неудовлетворительных оценок проставит неопределенное значение

Update study

set mark=null

where mark=’неудовл.’

Удаление кортежей из таблицы осуществляется оператором DELETE. Его формат приведен ниже.

Delete from <имя таблицы>

[where <условие>]

Раздел where аналогичен оператору select. Отсутствие этого раздела в операторе delete приведет к удалению всех кортежей отношения.

Запрос, удаляющий всю информацию об обучения студента с номером зачетки 10, имеет вид:

Delete from study

where student_num=10

Многотабличные запросы.

Вернемся к оператору select.

Чаще всего запросы строятся более чем по одной таблице. В этом случае все необходимые для построения запроса таблицы перечисляются в разделе from, а в разделе where кроме обычных условий обязательно указываются условия соединения по связям между таблицами. Если эти условия пропустить, то в результате получится декартово произведение таблиц, а такой запрос достаточно редко встречается на практике.

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

Select student_name, subject_short

from student t1, subject t2, study t3

where t3.student_num=t1.student_num

and t2.subject_name=t3.subject_name

Можно проследить соответствие подобных запросов с запросами на реляционной алгебре. Раздел from соответствует декартову произведению таблиц, раздел where – селекции этого произведения, а раздел select – проекции полученного отношения.

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

Select student_name, subject_short,mark

from student t1, subject t2, study t3

where t3.student_num=t1.student_num

and t2.subject_name=t3.subject_name

and t3.mark<>’неудовл.’

order by student_name, subject_short

Когда в запросе соединяются две таблицы, то они перечисляются в разделе from, а в разделе where записываются условия равенства по связи. Однако такие запросы не вернут кортежей из одного отношения, если нет соответствия в другом.

Например, вывести ФИО и оценки студента, но в список включить и студентов, которые еще не сдали ни одного экзамена.

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

Внешнее соединение в стандарте SQL имеет три разновидности: левое, правое и полное в зависимости от того, кортежи какого из отношений обязательно должны войти в результат. Указание на внешнее соединение может быть помещено в разделе from любого SQL запроса.

from отношение 1 left | right | full отношение 2 on <условие1> left | right | full отношение 3 on <условие2>…

Левое соединение в любом случае сохраняет все кортежи первого отношения

При правом внешнем соединении сохраняются кортежи второго отношения

Полное соединение сохраняет кортежи обоих отношений.

Поставленную задачу решаем следующим образом.

Select student_name, mark

from student t1 left join study t2

on t2.student_num=t1.student_num

В стандарте SQL есть возможность писать запросы с использованием операции объединения. Для этого используется оператор UNION, в качестве аргументов которого выступают запросы select:

select ...

union

select ...

Агрегирующие функции

Стандарт SQL определяет пять агрегирующих функций, представленных в таблице 3.

Таблица 3. – Функции агрегирования

Функция	Назначение
1	2
count	Возвращает количество строк в поле, указанном в качестве параметра.

Продолжение таблицы 3.

1	2
sum	Возвращает сумму значений в указанном поле
avg	Возвращает среднее значение в указанном поле
min	Возвращает минимальное значение в указанном поле
max	Возвращает максимальное значение в указанном поле

Все функции принимают в качестве аргумента имя поля.

Sum, avg – принимают в качестве атрибутов имена только числовых полей.

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

Выражение Count(*) вернёт количество кортежей результирующего отношения.