Тестовая предметная область

Для изучения возможности построения SQL-запросов нами выбрана фрагмент предметной области (ПрО) «Лечебное учреждение», который содержит информацию о врачах, их пациентах и о возможных болезнях этих пациентов. Для данной ПрО разработаны следующие бизнес-правила:

1) В лечебном учреждении работает множество врачей

2) У врача может лечиться несколько пациентов, каждый пациент может

лечиться у нескольких врачей.

3) Для каждого врача известно его ФИО, должность и дата приёма на работу.

4) По каждому пациенту известны его ФИО и дата рождения.

5) Каждая болезнь характеризуется названием и симптомами.

6) Врач для каждой болезни каждого своего пациента ставит диагноз и назначает курс лечения.

Структура БД для данной ПрО, соответствующая всем описанным бизнес-правилам, представлена ниже в виде DDL - команд для создания таблиц.

CREATE TABLE `doctor` (

`doctor_id` int(11) NOT NULL auto_increment,

`name` varchar(20) default NULL,

`caption` varchar(20) default NULL,

`work_start` datetime default NULL,

PRIMARY KEY (`doctor_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `patient` (

`patient_id` int(11) NOT NULL auto_increment,

`name` varchar(20) default NULL,

`birthdate` datetime default NULL,

PRIMARY KEY (`patient_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `illness` (

`ill_id` int(11) NOT NULL auto_increment,

`caption` varchar(20) default NULL,

`symptoms` varchar(1000) default NULL,

PRIMARY KEY (`ill_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `diagnosis` (

`diag_id` int(11) NOT NULL auto_increment,

`doctor_id` int(11) NOT NULL,

`patient_id` int(11) NOT NULL,

`ill_id` int(11) NOT NULL,

`diag_date` datetime default NULL,

`descr` varchar(1000) default NULL,

PRIMARY KEY (`diag_id`),

FOREIGN KEY (`doctor_id`) REFERENCES `doctor` ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY (`patient_id`) REFERENCES `patient` ON DELETE

CASCADE ON UPDATE CASCADE,

FOREIGN KEY (`ill_id`) REFERENCES `illness` ON DELETE CASCADE ON

UPDATE CASCADE

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Создание и работа с запросами к БД с помощью EMS SQL Manager

Lite.

Для отображения редактора запросов активируйте БД для работы (дважды щёлкните на ней в списке слева) а затем выберите в меню Tools- >New SQL Editor. На экране появится следующее окно с редактором (помечен красной меткой – см. рисунок 5.1).

Рисунок 5.1 – Окно редактора запросов

В выделенной области набирается текст запроса и затем выполняется при помощи команды Execute (см. Рисунок 5.2).

Рисунок 5.2 – Диалог выполнения команды Execute

Результаты запроса отобразятся в виде таблицы (см. Рисунок 3).

5.3 – Результаты выполнения запроса

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

Для запросов приведенных ниже используются следующие таблицы в тестовой БД.

Запросы INSERT

Запросы INSERT применяются для добавления новых данных в таблицы БД. Возможны следующие варианты синтаксиса данного вида запросов.

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT},...),(...),...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, ...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT ...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

В первых двух вариантах добавляемые данные входят в запрос, в третьем в таблицу вносятся информация, полученная на основании запроса (SELECT) на выборку из других таблиц. Подробное описание всех параметров запросов находится в документации к MySql5 (Chapter 13. SQL Statement Syntax). Для тестовой ПрО возможны следующие запросы для добавления информации:

1. Добавление нового врача, пациента и болезни

INSERT INTO doctor (name, caption, work_start)

VALUES ('doctor1', 'caption1', '2001.01.01')

INSERT INTO patient (name, birthdate)

VALUES ('patient1', '1981.01.01')

INSERT INTO illness (caption, symptoms)

VALUES ('ill1', 'description 1')

2. Добавление нового диагноза

INSERT INTO DIAGNOSIS (doctor_id, patient_id, ill_id, diag_date, descr)

VALUES (1, 1, 1, NOW(), 'diagnoz 1')

Здесь в поле даты диагноза внесётся текущее время, возвращаемое стандартной функцией MySql5 «NOW ()», которая определяет текущую календарную дату.

Запросы UPDATE

Запросы UPDATE применяются для обновления или редактирования существующих данных в таблицах БД. Возможны следующие варианты синтаксиса данного вида запросов.

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

UPDATE [LOW_PRIORITY] [IGNORE] table_references

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_condition]

В первом случае обновлению подлежат данные из одной таблицы, а во втором из нескольких. При этом условие WHERE используется для фильтрации записей, которые нужно обработать. Подробное описание всех параметров запросов находится в документации к MySql5 (Chapter 13. SQL Statement Syntax).

Для тестовой ПрО типичны следующие запросы для обновления информации:

1. Изменение ФИО доктора и пациента (например при смене фамилии).

UPDATE doctor SET name = 'new doctor1' WHERE doctor_id = 1

UPDATE patient SET name = 'new patient1' WHERE patient_id = 1

2. Изменение диагноза доктором у пациента.

UPDATE diagnosis SET diag_date = now(), descr = 'new description1'

WHERE patient_id = 1 AND doctor_id = 1 AND ill_id = 1

Запросы DELETE

Запросы DELETE применяются для удаления данных из таблицы БД. Возможны следующие варианты синтаксиса данного вида запросов.

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

tbl_name[.*] [, tbl_name[.*]] ...

FROM table_references

[WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

FROM tbl_name[.*] [, tbl_name[.*]] ...

USING table_references

[WHERE where_condition]

В первом случае удалению подлежат данные из одной таблицы, а в остальных из нескольких. При этом условие WHERE используется для фильтрации записей, которые нужно удалить. Подробное описание всех параметров запросов находится в документации к MySql5 (Chapter 13. SQL Statement Syntax).

Для тестовой ПрО необходимы следующие запросы для удаления информации:

1. Удаление доктора и пациента.

DELETE FROM doctor WHERE doctor_id = 1

DELETE FROM patient WHERE patient_id = 1

2. Удаление диагноза доктора у пациента.

DELETE FROM diagnosis WHERE patient_id = 1

AND doctor_id = 1 AND ill_id = 1

Следует заметить, что удаление данных из таблицы докторов или пациентов приведёт к каскадному удалению всех связанных с ними диагнозов. Данная особенность реализована с помощью механизма внешних ключей, определённых в таблице диагнозов (см. вид соответствующей команды CREATE TABLE в п.1.2). Подобный контроль над данными необходим для обеспечения целостности данных: в нашем случае для избежания ситуаций наличия диагноза для несуществующего пациента или доктора.

Запросы SELECT

Запросы SELECT применяются для извлечения или выборки данных из таблиц БД. Полный синтаксис для данного вида запросов:

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ...

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name' export_options

| INTO DUMPFILE 'file_name']

[FOR UPDATE | LOCK IN SHARE MODE]]

Подробное описание всех параметров запросов находится в документации к MySql5 (Chapter 13. SQL Statement Syntax).

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

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

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

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

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

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

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

1) Знать синтаксис SQL команд insert, update, delete и select;

2) Описать процесс создания, выполнения и сохранения запросов;

3) Уметь выполнять запросы для манипуляции данными.

4) В чём отличие конструкций ORDER BY и GROUP BY?

5) В чём отличие конструкций WHERE и HAVING?

6) Как определить максимальное значение в каком-либо столбце?

7) Возможно ли подсчитать общее количество записей, возвращаемое

запросом?

8) Как определить количество записей в каждой группе записей при

запросе с GROUP BY?

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

Тема:ИЗУЧЕНИЕ РАСШИРЕННЫХ ВОЗМОЖНОСТЕЙ ЯЗЫКА

МАНИПУЛИРОВАНИЯ ДАНЫМИ В СУБД MYSQL 5.0

Цель работы:

Ознакомиться с расширенными возможностями языка SQL для манипулирования данных, поддерживаемыми в СУБД MySQL 5.0. Изучить возможности для работы с такого рода запросами с помощью утилиты EMSMySQL Manager Lite. Используя тестовую БД, разработанную в ходе выполнения лабораторной работы № 2-2, создать набор запросов для извлечения данных, используя расширенные средства языка SQL.

Общие сведения

Вычисление дат

В MySQL имеется несколько функций, реализующих арифметические операции над значениями полей, имеющими тип данных “календарная дата / время” (этот тип данных задается с помощью описателя datetime в соотвествующей команде CREATE) Такие функции позволяют, в частности, вычислять временной интервал на основе данных из полей этого типа или получать определенные части календарной даты: день, месяц или год. Например, определить стаж работы некоторого врача, записи о котором хранятся ) можно, если вычислить разницу между текущим годом и годом его приёма, а из результата вычесть единицу, если текущий день находится к началу календаря ближе, нежели день начала работы. Приведенный ниже запрос выводит дату начала работы врача, его стаж и текущую дату.

SELECT name, work_start, CURRENT_DATE,

(YEAR(CURRENT_DATE)-YEAR(work_start))

(RIGHT(CURRENT_DATE,5)<RIGHT(work_start,5))__

AS stage FROM doctor;

В этом примере стандартная функция YEAR() выделяет значений года из текущей даты, полученной в результате применения другой функции: CURRENT_DATE(),а функция RIGHT() возвращает пять крайних справа символов, представляющих календарный день (MM-DD). Часть выражения, сравнивающая даты, выдает 1 или 0, что позволяет уменьшить результат на единицу, если текущий день находится к началу календаря ближе, чем день принятия на работу врача (значение в поле данных work_start. Все это вычисляемое выражение смотрится несколько громоздко, поэтому вместо него в заголовке соответствующего столбца результатов выполнения данного запроса выводится имя – псевдоним: stage - "стаж".

А как поступать, если потребуется определить, дни рождения каких пациентов наступят в следующем месяце? Для таких расчетов день и год значения не имеют; а из столбца, содержащего дату рождения, нас интересует только месяц. В MySQL предусмотрено несколько функций для получения частей поля типа даты - это такие функции как YEAR(), MONTH(), и DAYOFMONTH(). В данном случае нам подойдет функция MONTH(). Увидеть работу этой функции можно с помощью простого запроса, выводящего дату рождения birthdate и MONTH(birthdate):

SELECT name, birthdate, MONTH(birthdate) FROM Patient;

Можно написать небольшой запрос, который будет работать вне зависимости от того, какой сейчас месяц. Функция DATE_ADD() позволяет прибавить к дате некоторый интервал времени. Если добавить к значению, возвращаемому функцией NOW(), месяц, а затем извлечь из получившейся даты номер месяца при помощи функции MONTH(), мы получим именно тот месяц, который нам нужен:

SELECT name, birthdate FROM Patient

WHERE MONTH(birthdate) = MONTH(DATE_ADD(NOW(), INTERVAL 1

MONTH));

В нашем случае запрос вернёт пустой набор записей.

Работа с значениями NULL

Обработка NULL-значений, т.е. таких полей, которые содержат отсутствующее или неизвестное значение, производится специальным образом.. Например, проверить некоторое поле на равенство значению NULL с помощью обычных арифметических операторов сравнения (=, < или <>) невозможно. Это отлично иллюстрирует результат выполнения следующего запроса:

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

Вместо этого нужно воспользоваться логическими операторами: IS

NULL и IS NOT NULL:

SELECT 1 IS NULL, 1 IS NOT NULL;

Отметим, что в MySQL значения 0 или NULL приравнивается к логическому значению FALSE, а все остальные - к TRUE. По умолчанию значение "истина" для булевого оператора равно 1. Два NULL-значения считаются одинаковыми при вычислениях в условии GROUP BY в соотвествующем операторе SELECT. При выполнении условия ORDER BY с опцией ASC (по возрастанию) NULL-значения идут в первую очередь, и в последнюю - если используется условие ORDER BY ... DESC (упорядочить по убыванию) .

Сравнение по шаблонам

В MySQL реализовано стандартное для SQL сравнение по шаблонам, а также особый тип такого сравнения - он основан на использовании выражений, подобных применяющимся в служебных программах операционной системы Unix (таких, как vi, grep и sed). В SQL при сравнении по шаблону символ ‘_’ обозначает любой одиночный символ, а ‘%’ - определенное количество символов (включая ноль символов). В MySQL в SQL-шаблонах по умолчанию не учитывается регистр символов. При работе с шаблонами SQL использование операторов = или <> не допускается, вместо этого применяются логические операторы сравнения LIKE или NOT LIKE.

Найти все имена, кончающиеся на ‘1’, можно следующим образом:

SELECT * FROM Patient WHERE name LIKE "%1";

Найти все имена, содержащие ‘p’, можно следующим образом:

SELECT * FROM Patient WHERE name LIKE "%р%";

Найти все имена, содержащие ровно пять символов, можно при помощи

шаблонного символа ‘_’:

SELECT * FROM pet WHERE name LIKE "_____";

В нашем случае будет возвращен пустой набор записей.

Во втором типе шаблонов, предусмотренных в MySQL, используются т.н. расширенные регулярные выражения (regular expression). При поиске совпадений на основе такого шаблона шаблоном нужно пользоваться операторами REGEXP и NOT REGEXP (или их синонимами - RLIKE и NOT RLIKE).

Ниже приведены некоторые характеристики расширенных регулярных выражений:

● ‘.’ обозначает любой символ.

● Класс символов '[...]' обозначает любой из символов в скобках. Например, '[abc]' обозначает ‘a’, ‘b’ или ‘c’. Набор символов можно обозначить с помощью дефиса. '[a-z]' обозначает любую букву нижнего регистра, а '[0-9]' - любую цифру.

● ‘*’обозначает ноль или более экземпляров символа, стоящего перед ним. Например, 'x*' обозначает любое количество символов ‘x’, '[0-9]*' обозначает любое количество цифр, а '.*' - любое количество любых символов.

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

``Закрепить'' шаблон так, чтобы проверять совпадения с началом или концом значения можно с помощью символов ‘^’ (начало) или ‘$’ (конец), которые располагаются в начале или в конце шаблона соответственно. Чтобы продемонстрировать применение механизма регулярных выражений, приведенные выше запросы LIKE здесь переписаны с использованием REGEXP.

Найти все имена, кончающиеся на ‘1’, можно при помощи символа ‘$’, привязывающего шаблон к концу имени:

● SELECT * FROM Patient WHERE name REGEXP "1$";

Начиная с версии MySQL 3.23.4, заставить REGEXP учитывать регистр символов можно с помощью ключевого слова BINARY. В этом запросе положительный результат поиска будет получен только при обнаружении символа нижнего регистра в начале имени:

SELECT * FROM Patient WHERE name REGEXP BINARY "^p";

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