Операторы заполнения БД book имеют следующий вид.
USE book;
SET CHARACTER SET cp1251;
DELETE FROM catalogs;
INSERT INTO catalogs VALUES (1,'Программирование');
INSERT INTO catalogs VALUES (2,'Интернет');
INSERT INTO catalogs VALUES (3,'Базы данных');
INSERT INTO catalogs VALUES (4,'Сети');
INSERT INTO catalogs VALUES (5,'Мультимедиа');
DELETE FROM books;
INSERT INTO books VALUES (1,'JavaScript в кармане','Рева О.Н.', 2008, 42.00, 10, 1);
INSERT INTO books VALUES (2,'Visual FoxPro 9.0','Клепинин В.Б.', 2007, 660.00, 2, 1);
INSERT INTO books VALUES (3,'C++ Как он есть','Тимофеев В.В.',2009, 218.00, 4, 1);
INSERT INTO books VALUES (4,'Создание приложений с помощью C#','Фаронов В.В.', 2008, 169.00, 1, 1);
INSERT INTO books VALUES (5,'Delphi. Народные советы','Шкрыль А.А.',2007,243.00,6,1);
INSERT INTO books VALUES (6,'Delphi. Полное руководство','Сухарев М.',2008,500.00,6,1);
INSERT INTO books VALUES (7,'Профессиональное программирование на PHP', 'Шлосснейгл Дж.', 2006, 309.00, 5, 1);
INSERT INTO books VALUES (8,'Совершенный код','Макконнелл С.', 2007, 771.00, 1, 1);
INSERT INTO books VALUES (9,'Практика программирования','Керниган Б.', 2004, 214.00, 12, 1);
INSERT INTO books VALUES (10,'Принципы маршрутизации в Internet','Хелеби С.', 2001, 428.00, 4, 2);
INSERT INTO books VALUES (11,'Поиск в Internet','Гусев В.С.',2004,107.00,2,2);
INSERT INTO books VALUES (12,'Web-конструирование','Дуванов А.А.', 2003, 177.00, 6, 2);
INSERT INTO books VALUES (13,'Самоучитель Интернет','Константинов Ю.П.', 2009, 121.00, 4, 2);
INSERT INTO books VALUES (14,'Популярные интернет-браузеры','Маринин С.А.', 2007, 82.00, 6, 2);
INSERT INTO books VALUES (15,'Общение в Интернете','Экслер А.', 2006, 85.00, 5, 2);
INSERT INTO books VALUES (16,'Базы данных','Малыхина М.П.', 2006, 326.00, 2, 3);
INSERT INTO books VALUES (17,'Базы данных. Разработка приложений','Рудикова Л.В.', 2006, 189.00, 6, 3);
INSERT INTO books VALUES (18,'Раскрытие тайн SQL','Оппель Э.', 2007, 200.00, 3, 3);
INSERT INTO books VALUES (19,'Практикум по Access','Золотова С.И.', 2007, 87.00, 6, 3);
INSERT INTO books VALUES (20,'Компьютерные сети','Танненбаум Э.', 2007, 630.00, 6, 4);
INSERT INTO books VALUES (21,'Сети. Поиск неисправностей','Бигелоу С.', 2005, 434.00, 4, 4);
INSERT INTO books VALUES (22,'Безопасность сетей','Брегг Р.', 2006, 462.00, 5, 4);
|
|
INSERT INTO books VALUES (23,'Анализ и диагностика компьютерных сетей', 'Хогдал Дж.', 2001, 344.00, 3, 4);
INSERT INTO books VALUES (24,'Локальные вычислительные сети', 'Епанешников А.', 2005, 82.00, 8, 4);
INSERT INTO books VALUES (25,'Цифровая фотография','Надеждин Н.', 2004, 149.00, 20,5);
INSERT INTO books VALUES (26,'Музыкальный компьютер для гитариста', 'Петелин Р.Ю.', 2004, 217.00, 15, 5);
INSERT INTO books VALUES (27,'Видео на ПК','Федорова А.',2003,231.00,10,5);
INSERT INTO books VALUES (28,'Мультипликация во Flash','Киркпатрик Г.', 2006, 211.00, 20, 5);
INSERT INTO books VALUES (29,'Запись CD и DVD','Гультяев А.К.', 2003, 167.00, 12, 5);
INSERT INTO books VALUES (30,'Запись и обработка звука на компьютере', 'Лоянич А.А.', 2008, 51.00, 8, 5);
DELETE FROM users;
INSERT INTO users VALUES (1,'Александр','Валерьевич','Иванов','58-98-78', 'ivanov@email.ru', 'active');
INSERT INTO users VALUES (2,'Сергей','Иванович','Лосев','90-57-77', 'losev@email.ru', 'passive');
INSERT INTO users VALUES (3,'Игорь','Николаевич','Симонов','95-66-61', 'simonov@email.ru', 'active');
INSERT INTO users VALUES (4,'Максим','Петрович','Кузнецов',NULL, 'kuznetsov@email.ru', 'active');
INSERT INTO users VALUES (5,'Анатолий','Юрьевич','Петров', NULL, NULL, 'lock');
INSERT INTO users VALUES (6,'Александр','Александрович','Корнеев','89-78-36', 'korneev@email.ru', 'gold');
DELETE FROM orders;
INSERT INTO orders VALUES (1,3,8,'2009-01-04 10:39:38',1);
INSERT INTO orders VALUES (2,6,10,'2009-02-10 09:40:29',2);
INSERT INTO orders VALUES (3,1,20,'2009-02-18 13:41:05',4);
INSERT INTO orders VALUES (4,4,20,'2009-03-10 18:20:00',1);
INSERT INTO orders VALUES (5,3,20,'2009-03-17 19:15:36',1);
Лабораторная работа № 4
Создание простых запросов на выборку
Теоретические сведения
Рассмотрим следующие вопросы:
· выборка данных из одной таблицы с епомощью оператора SELECT;
· использование в запросах операторов и встроенных функций MySQL.
Для выполнения запросов (извлечения строк из одной или нескольких таблиц БД) используется оператор SELECT. Результатом запроса всегда является таблица. Результаты запроса могут быть использованы для создания новой таблицы. Таблица, полученная в результате запроса, может стать предметом дальнейших запросов.
|
|
Общая форма оператора SELECT:
SELECT столбцы FROM таблицы
[WHERE условия]
[GROUP BY группа [HAVING групповые_условия] ]
[ORDER BY имя_поля]
[LIMIT пределы];
Оператор SELECT имеет много опций. Их можно использовать или не использовать, но они должны указываться в том порядке, в каком они приведены. Если требуется вывести все столбцы таблицы, необязательно перечислять их после ключевого слова select, достаточно заменить этот список символом *.
Список столбцов в операторе select используют, если нужно изменить порядок следования столбцов в результирующей таблице или выбрать часть столбцов.
Условия выборки. Гораздо чаще встречается ситуация, когда необходимо изменить количество выводимых строк. Для выбора записей, удовлетворяющих определенным критериям поиска, можно использовать конструкцию WHERE.
В запросе можно использовать ключевое слово DISTINCT, чтобы результат не содержал повторений уже имеющихся значений, например:
Сортировка. Результат выборки – записи, расположенные в том порядке, в котором они хранятся в БД. Чтобы отсортировать значения по одному из столбцов, необходимо после конструкции order by указать этот столбец, например:
Сортировку записей можно производить по нескольким столбцам (их следует указать после слов order by через запятую). Число столбцов, указываемых в конструкции order by, не ограничено.
По умолчанию сортировка производится в прямом порядке (записи располагаются от наименьшего значения поля сортировки до наибольшего). Обратный порядок сортировки реализуется с помощью ключевого слова desc:
|
|
Для прямой сортировки существует ключевое слово asc, но так как записи сортируются в прямом порядке по умолчанию, данное ключевое слово опускают.
Ограничение выборки. Результат выборки может содержать тысячи записей, вывод и обработка которых занимают значительное время. Поэтому информацию часто разбивают на страницы и предоставляют ее пользователю частями. Постраничная навигация используется при помощи ключевого слова limit, за которым следует число выводимых записей. Следующий запрос извлекает первые 5 записей, при этом осуществляется обратная сортировка по полю b_count:
Для извлечения следующих пяти записей используется ключевое слово limit с двумя цифрами. Первая указывает позицию, начиная с которой необходимо вернуть результат, вторая цифра – число извлекаемых записей, например:
При определении смещения нумерация строк начинается с нуля (поэтому в последнем примере для шестой строки указано смещение 5).
Группировка записей. Конструкция GROUP ВУ позволяет группировать извлекаемые строки. Она полезна в комбинации с функциями, применяемыми к группам строк. Эти функции (табл. 6) называются агрегатами (суммирующими функциями) и вычисляют одно значение для каждой группы, создаваемой конструкцией group by. Функции позволяют узнать число строк в группе, подсчитать среднее значение, получить сумму значений столбцов. Результирующее значение рассчитывается для значений, не равных null (исключение – функция count(*)). Допустимо использование этих функций в запросах без группировки (вся выборка – одна группа).
Пример использования функции count(), которая возвращает число строк в таблице, значения указанного столбца для которых отличны от NULL:
Таблица 6
| Обозначение | Описание |
| AVG ([DISTINCT] expr) | Возвращает среднее значение аргумента expr. В качестве аргумента обычно выступает имя столбца. Необязательное слово distinct позволяет обрабатывать только уникальные значения столбца expr |
| COUNT () | Подсчитывает число записей и имеет несколько форм. Форма COUNT (выражение) возвращает число записей в таблице, поле выражение для которых не равно null. Форма count(*) возвращает общее число строк в таблице независимо от того, принимает какое-либо поле значение null или нет. Форма COUNT (DISTINCT выражение1, выражение2,...) позволяет использовать ключевое слово distinct, которое позволяет подсчитать только уникальные значения столбца |
| MIN ([DISTINCT] expr) | Возвращает минимальное значение среди всех непустых значений выбранных строк в столбце expr. Необязательное слово distinct позволяет обрабатывать только уникальные значения столбца expr |
| MAX ([DISTINCT] expr) | Возвращает максимальное значение среди всех непустых значений выбранных строк в столбце expr. Необязательное слово distinct позволяет обрабатывать только уникальные значения столбца expr |
| STD (expr) | Возвращает стандартное среднеквадратичное отклонение в аргументе expr |
| STDDEV_SAMP (expr) | Возвращает выборочное среднеквадратичное отклонение в аргументе expr |
| SUM ([DISTINCT] expr) | Возвращает сумму величин в столбце expr. Необязательное слово distinct позволяет обрабатывать только уникальные значения столбца expr |
Использование ключевого слова distinct с функцией count() позволяет вернуть число уникальных значений b_cat_ID в таблице books, например:
В SELECT -запросе столбцу можно назначить новое имя с помощью оператора as. Например, результату функции count() присваивается псевдоним total:
Использование функций в конструкции where приведет к ошибке. В следующем примере показана попытка извлечения из таблицы catalogs записи с максимальным значением поля cat_ID:
Решение задачи следует искать в использовании конструкции order by:
Для извлечения уникальных записей используют конструкцию group by с именем столбца, по которому группируется результат:
При использовании group by возможно использование условия where:
Часто при задании условий требуется ограничить выборку по результату функции (например, выбрать каталоги, где число товарных позиций больше 5). Использование для этих целей конструкции where приводит к ошибке. Для решения этой проблемы вместо ключевого слова where используется ключевое слово having, располагающееся за конструкцией group by:
Запрос, извлекающий уникальные значения столбца b_cat_ID, большие двух:
При этом в случае использования ключевого слова where сначала производится выборка из таблицы с применением условия и лишь затем группировка результата, а в случае использования ключевого слова having сначала происходит группировка таблицы и лишь затем выборка с применением условия. Допускается использование условия having без группировки group by.
Использование функций. Для решения специфических задач при выборке удобны встроенные функции MySQL. Большинство функций предназначено для использования в выражениях SELECT и WHERE. Существуют также специальные функции группировки для использования в выражении GROUP BY (см. выше).
Каждая функция имеет уникальное имя и может иметь несколько аргументов (перечисляются через запятую в круглых скобках). Если аргументы отсутствуют, круглые скобки все равно следует указывать. Пробелы между именем функции и круглыми скобками недопустимы.
Число доступных для использования функций велико, в приложениях приведены наиболее полезные из них.
Пример использования функции, возвращающей версию сервера MySQL:
Отметим также возможность использования оператора SELECT без таблиц вообще. В такой форме SELECT можно использовать как калькулятор:
Можно вычислить любое выражение без указания таблиц, получив доступ ко всему разнообразию математических и других операторов и функций. Возможность выполнять математические расчеты на уровне SELECT позволяет проводить финансовый анализ значений таблиц и отображать полученные результаты в отчетах. Во всех выражениях MySQL (как в любом языке программирования) можно использовать скобки, чтобы контролировать порядок вычислений.
Операторы. Под операторами подразумеваются конструкции языка, которые производят преобразование данных. Данные, над которыми совершается операция, называются операндами.
В MySQL используются три типа операторов:
· арифметические операторы;
· операторы сравнения;
· логические операторы.
Арифметические операции. В MySQL используются обычные арифметические операции: сложение (+), вычитание (–), умножение (*), деление (/) и целочисленное деление DIV (деление и отсечение дробной части). Деление на 0 дает безопасный результат NULL.
Операторы сравнения. При работе с операторами сравнения необходимо помнить о том, что, за исключением нескольких особо оговариваемых случаев, сравнение чего-либо со значением NULL дает в результате NULL. Это касается и сравнения значения NULL со значением NULL:
Корректнее использовать следующий запрос:
Поэтому следует быть предельно внимательными при работе с операторами сравнения, если операнды могут принимать значения NULL.
Наиболее часто используемые операторы сравнения приведены в табл. 7.
Логические операторы. MySQL поддерживает все обычные логические операции, которые можно использовать в выражениях. Логические выражения в MySQL могут принимать значения 1 (истина), 0 (ложь) или NULL.
Кроме того, следует учитывать, что MySQL интерпретирует любое ненулевое значение, отличное от NULL, как значение «истина». Основные логические операторы приведены в табл. 8.
Практическая работа
При выполнении лабораторной работы необходимо:
· для заданной предметной области построить два простых запроса на выборку с использованием операторов и функций MySQL;
· составить отчет по лабораторной работе.
Таблица 7
| Оператор | Значение |
| = | Оператор равенства. Возвращает 1 (истина), если операнды равны, и 0 (ложь), если не равны |
| <=> | Оператор эквивалентности. Аналогичен обычному равенству, но возвращает только два значения: 1 (истина) и 0 (ложь). NULL не возвращает |
| <> | Оператор неравенства. Возвращает 1 (истина), если операнды не равны, и 0 (ложь), если равны |
| < | Оператор «меньше». Возвращает 1 (истина), если левый операнд меньше правого, и 0 (ложь) – в противном случае |
| <= | Оператор «меньше или равно». Возвращает 1 (истина), если левый операнд меньше правого или они равны, и 0 (ложь) – в противном случае |
| > | Оператор «больше». Возвращает 1 (истина), если левый операнд больше правого, и 0 (ложь) – в противном случае |
| >= | Оператор «больше или равно». Возвращает 1 (истина), если левый операнд больше правого или они равны, и 0 (ложь) – в противном случае |
| n BETWEEN min AND max | Проверка диапазона. Возвращает 1 (истина), если проверяемое значение n находится между min и max, и 0 (ложь) – в противном случае |
| IS NULL и IS NOT NULL | Позволяют проверить, является ли значение значением NULL или нет |
| n IN (множество) | Принадлежность к множеству. Возвращает 1 (истина), если проверяемое значение n входит в список, и 0 (ложь) – в противном случае. В качестве множества может использоваться список литеральных значений или выражений или подзапрос |
Таблица 8
| Оператор | Пример | Значение |
| AND | n AND m | Логическое И: истина AND истина = истина, ложь AND любое = ложь. Все остальные выражения оцениваются как NULL |
| OR | n OR m | Логическое ИЛИ: истина OR любое = истина, NULL OR ложь = NULL, NULL OR NULL = NULL, ложь OR ложь = ложь |
| NOT | NOT n | Логическое НЕТ: NOT истина = ложь, NOT ложь = истина. NOT NULL = NULL |
| XOR | n XOR m | Логическое исключающее ИЛИ: истина XOR истина = ложь, истина XOR ложь = истина, ложь XOR истина = истина, ложь XOR ложь = ложь, NULL XOR любое = NULL, любое XOR NULL = NULL |
Переменные SQL и временные таблицы. Часто результаты запроса необходимо использовать в последующих запросах. Для этого полученные данные необходимо сохранить во временных структурах. Эту задачу решают переменные SQL и временные таблицы. Объявление переменной начинается с символа @, за которым следует имя переменной. Значения переменным присваиваются посредством оператора select с использованием оператора присваивания:=. Например:
Объявляется переменная @ total, которой присваивается число записей в таблице books. Затем в рамках текущего сеанса в последующих запросах появляется возможность использования данной переменной. Переменная действует только в рамках одного сеанса соединения с сервером MySQL и прекращает свое существование после разрыва соединения.
Переменные также могут объявляться при помощи оператора set:
При использовании оператора set в качестве оператора присваивания может выступать обычный знак равенства =. Оператор set удобен тем, что он не возвращает результирующую таблицу. Не рекомендуется одновременно присваивать переменной некоторое значение и использовать эту переменную в одном запросе.
Переменная SQL позволяет сохранить одно промежуточное значение. Когда необходимо сохранить результирующую таблицу, прибегают к временным таблицам. Создание временных таблиц осуществляется при помощи оператора CREATE temporary table, синтаксискоторого ничем не отличается от синтаксиса оператора CREATE table.
Временная таблица автоматически удаляется по завершении соединения с сервером, а ее имя действительно только в течение данного соединения. Это означает, что два разных клиента могут использовать временные таблицы с одинаковыми именами без конфликта друг с другом или с существующей таблицей с тем же именем.