Использование вложенных запросов

Еще одной альтернативой операций «… JOIN» может выступать использование вложенных запросов или т.н. подзапросов. Типовым примером использования такого рода запросов является получение данных из одной таблицы по условию, выполнение которого проверяется для данных из другой таблицы. Например, для получения информации о первом пациенте, для которого был поставлен диагноз после '12.01.2006', можно составить следующий запрос:

SELECT name FROM Patient

WHERE patient_id = (SELECT patient_id FROM Diagnosis

WHERE diag_date > '12.01.2006' ORDER BY diag_date DESC LIMIT 1);

Здесь инструкция LIMIT 1 выбирает только первую запись, так как при использовании подзапроса и условия «=» на поле, вложенный запрос должен возвращать всегда одну запись. В противном случае будет выдано сообщение об ошибке «Subquery returns more than 1 row».

При использовании под запросов возможна не только операция «=» для объединения «главного» и «дочернего» запросов. Существуют следующие варианты операций объединения: ANY, IN, SOME. Синтаксис их таков:

operand comparison_operator ANY (subquery)

operand IN (subquery)

operand comparison_operator SOME (subquery)

здесь: operand – поле главного запроса для отбора нужных строк,

comparison_operator – оператор сравнения значения данных в этом поле с

результатами под запроса (= > < >= <= <> ).

При этом

■ оператор ANY добавит запись в результат, если значения поля главного запроса встретилось хотя бы один раз при выполнении подзапроса.

■ оператор IN добавит запись в результат, если значения поля главного запроса встретилось в множестве всех записей, возвращённых подзапросом.

■ оператор SOME добавит запись в результат, если значения поля главного запроса встретилось больше чем один раз при выполнении подзапроса.

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

SELECT name FROM Patient

WHERE patient_id IN (SELECT patient_id FROM Diagnosis

WHERE diag_date > '12.01.2006');

Использование пользовательских переменных

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

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

SELECT @min_date:=MIN(diag_date),@max_date:=MAX(diag_date) FROM

Diagnosis;

SELECT * FROM Diagnosis WHERE (diag_date =@max_date OR

diag_date=@max_date)

Использование атрибута AUTO_INCREMENT

Атрибут AUTO_INCREMENT может использоваться для генерации уникального идентификатора для новых строк:

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

);

INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),

("lax"),("whale");

SELECT * FROM animals;

Вы можете получить AUTO_INCREMENT ключ с помощью функции SQL LAST_INSERT_ID() или с помощью функции mysql_insert_id() интерфейса C.

Для многострочной вставки, LAST_INSERT_ID() / mysql_insert_id() на самом деле вернут AUTO_INCREMENT значение для первой вставленной записи. Это сделано для того, чтобы многострочные вставки можно было повторить на других серверах.

В таблицах типа MyISAM и BDB можно определить AUTO_INCREMENT для вторичного столбца составного ключа. В этом случае значение, генерируемое для автоинкрементного столбца, вычисляется как MAX(auto_increment_column)+1) WHERE prefix=given-prefix. Столбец с атрибутом AUTO_INCREMENT удобно использовать, когда данные нужно помещать в упорядоченные группы.

CREATE TABLE animals (

grp ENUM('fish','mammal','bird') NOT NULL,

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (grp,id)

);

INSERT INTO animals (grp,name)

VALUES("mammal","dog"),("mammal","cat"),

("bird","penguin"),("fish","lax"),("mammal","whale");

SELECT * FROM animals ORDER BY grp,id;

Обратите внимание, что в этом случае значение AUTO_INCREMENT будет использоваться повторно, если в какой-либо группе удаляется строка, содержащая наибольшее значение AUTO_INCREMENT.

Получение системной информации об объектах БД

Как быть, если вы забыли имя базы или таблицы, или структуру какой- либо из таблиц (например имена столбцов)? В MySQL эта проблема решается при помощи нескольких системных команд, выводящих информацию о базе данных и содержащихся в ней таблицах.

Запрос SHOW DATABASES выводит список управляемых сервером баз данных. Определить, какая из них выбрана в данный момент, можно с помощью функции DATABASE():

SELECT DATABASE ();

Если ни одна из баз в настоящее время не выбрана, результат выполнения запроса будет пуст.

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

SHOW TABLES;

Узнать структуру таблицы Patient можно при помощи функции

DESCRIBE,:

DESCRIBE Patient;

которая выводит информацию о каждом из столбцов таблицы в следующем

формате

FIELD - имя столбца,

TYPE - тип данных, к которому относится этот столбец,

NULL - может ли данный столбец содержать значения NULL,

KEY - является ли этот столбец индексным,

DEFAULT - указывает значение данного столбца по умолчанию.

Если для таблицы созданы индексы, информацию о них можно

получить с помощью

SHOW INDEX FROM Patient.

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

В соответствии с тестовой БД, разработанной в ходе выполнения предыдущих лабораторных работ и пользуясь приведенным выше описанием запросов, поддерживаемых СУБД MySQL, необходимо выполнить следующие действия:

(1) Создать, выполнить и сохранить запросы SELECT, используя функции работы с датами для всех таблиц БД, содержащими даты.

(2) Создать, выполнить и сохранить запросы SELECT, применяя способы работы с полями, содержащими значения NULL для всех таблиц БД с возможными NULL-полями.

(3) Создать, выполнить и сохранить запросы SELECT, применяя методы сравнения по шаблонам для всех таблиц БД.

(4) Создать, выполнить и сохранить запросы SELECT для выборки данных из всех таблиц, применяя при этом запросы к нескольким таблицам БД. При этом необходимо использование всех вариантов операций JOIN.

(5) Создать, выполнить и сохранить запросы SELECT для выборки данных из всех таблиц, применяя при этом запросы к нескольким таблицам БД. При этом необходимо использование всех вариантов операций подзапросов.

(6) Создать, выполнить и сохранить запросы SELECT, применяя использование пользовательских переменных для всех таблиц БД.

(7) Создать, выполнить и сохранить запросы, используя атрибут

AUTO_INCREMENT для всех таблиц БД.

Контрольные вопросы

1) Знать синтаксис SQL команд для работы с датами и NULL-полями;

2) Уметь описать возможности команд сравнения данных по шаблонам;

3) Что такое регулярные выражения и зачем они нужны.

4) Чем отличается операция INNER JOIN от OUTER JOIN.

5) В чём сходства и различия операций LEFT JOIN и RIGHT JOIN.

6) Возможно ли заменить оператор IN другими операторами для работы с

подзапросами. Если да, то какими операторами возможна замена и

каким образом.

7) Возможно ли использование пользовательских переменных в

подзапросах. Если да, то каким образом.

8) Что такое атрибут AUTO_INCREMENT и зачем он нужен.

9) Как получить список полей нужной таблицы из некоторой БД.

ЛАБОРАТОРНАЯ РАБОТА № 5