Практическая работа № 4 MySQL. Однотабличные запросы к БД
Объём учебного времени – 2ч
Методические рекомендации
1. Цель работы: Научиться создавать однотабличные запросы к базе данных.
2. Основные теоретические положения:
Синтаксис оператора SELECT
Одним из основных операторов DML является оператор SELECT. Этот оператор позволяет извлекать данные из таблиц, формируя различные запросы. Применение оператора содержит в себе все возможности реляционной алгебры. Различают концептуальную фактическую схему выполнения оператора SELECT. Первая схема описывает логическую последовательность выполняемых операций. Фактическая схема, используя оптимизатор по скорости выполнения запроса, выбирает наилучшую последовательность.
Синтаксис языка запросов к данным (DML), являющегося одним из подмножеств языка SQL, состоит всего из одной команды SELECT. Эта команда вместе с множеством параметров и предложений служит для формирования запросов к базе данных. Запросы формируются для извлечения из таблиц базы данных информации соответствующей некоторым требованиям, задаваемым пользователем. Оператор SELECT не используется автономно, вместе с ним обязательно должны задаваться уточняющие предложения. Эти предложения могут быть обязательны ми и дополнительными. Обязательным является только одно предложение — FROM, без которого оператор SELECT не может работать.
Здесь за ключевым словом SELECT следует список полей, которые возвращаются в результате выполнения запроса:
- имена полей в списке разделяются через запятую;
- для выборки всех полей таблицы (таблиц) используется символ подстановки *
- параметр All (задан по умолчанию) означает, что результат выборки будет содержать все записи, включая дублирующие.
- при использовании параметра DISTINCT результат запроса не будет содержать дублирующихся строк
Синтаксис:
SELECT Items
FROM tables
[WHERE condition]
[GROUP BY group type]
[HAVING WHERE_definition]
[ORDER BY order_type]
[LIMIT limit_criteria];
где tables - названия таблиц, из которых происходит извлечение данных,
WHERE - проверка на соответствие полей условию condition,
GROUP BY - группирование результатов запроса по полю group type,
HAVING - отбор групп по условию,
ORDER BY - сортировка данных по полю order_type,
LIMIT - ограничение количества записей в результате запроса по критерию limit_criteria.
2.2. Задание условий при выборке данных
Специальные операторы языка SQL, применяемые для задания условий, можно разделить на следующие группы:
- операторы сравнения (=; <>; <; >; <=; >=);
- логические операторы (IS NULL, BETWEEN...AND, IN, LIKE, EXISTS, UNIQUE, ALL, ANY);
- операторы объединения (And, Or);
- операторы отрицания (IS NOT NULL NOT BETWEEN NOT IN NOT LIKE NOT EXISTS NOT UNIQUE).
Результатом выполнения каждого из этих операторов является логические значение (true или false). Если для некоторой записи оператор возвращает значение true, то запись включается в результат выборки, если false — не включается.
Оператор LIKE применяется для сравнения значения поля со значением, заданным при помощи шаблонов. Для задания шаблонов используются два символа:
- знак процента (%) — заменяет последовательность символов любой (в том числе и нулевой) длины;
- символ подчёркивания (_) — заменяет любой символ.
Пример 4.1 Найдём в таблице Клиенты записи, в которых фамилия клиента начинается с буквы «М»:
SELECT Фамилия. Имя. Отчество. Телефон
FROM Клиенты
WHERE Фамилия LIKE 'М%’;
Пример 4.2 Найдем в этой же таблице записи, для которых номер телефона начинается с символов (816)025-61, а две последние цифры неизвестны:
SELECT Фамилия. Имя, Отчество. Телефон
FROM Клиенты
WHERE Телефон LIKE '(816)025-61__';
2.3. Вычисляемые поля
Язык SQL позволяет создавать вычисляемые поля в тексте запроса. Для реализации этой функции в запросе просто приводится выражение, в котором используются арифметические и математические операторы, а также имена полей в качестве переменных. В результате выполнения запроса с вычисляемыми полями выборка будет содержать не только информацию из таблиц базы данных, но и дополнительную информацию, полученную в результате вычисления заданного выражения.
При создании вычисляемого поля можно использовать следующие арифметические операторы:
□ оператор сложения (+);
□ оператор вычитания (-);
□ оператор умножения (*);
□ оператор деления (/).
Помимо арифметических операторов допускается использование ряда математических функций, например:
□ ABS — вычисление абсолютного значения;
□ ROUND — округление;
□ SQR — извлечение квадратного корня;
□ ЕХР — экспонента;
□ LOG — натуральный логарифм;
□ SIN, COS, TAN — тригонометрические функции.
2.4. Упорядочение данных
Для упорядочения данных в выборке, полученной в результате выполнения запроса, используется предложение ORDER BY. Синтаксис оператора SELECT в этом случае будет следующим:
SELECT {* | ALL I DISTINCT поле. поле2.....noneN}
FROM Таблица
WHERE условие
ORDER BY поле (ASC | DESC};
После ключевых слов ORDER BY сначала указывается имя поля (полей), по которому производится сортировка, а затем — режим сортировки:
- ASC — информация располагается в порядке возрастания значения указанного поля (для текстовых полей — в алфавитном порядке), этот режим используется по умолчанию;
- DESC — информация располагается в порядке убывания значений указанного поля (для текстовых полей — в порядке, обратном алфавитному).
Например, чтобы отсортировать список товаров по алфавиту, следует использовать следующий запрос:
SELECT Категория. Наименование. Цена
FROM Товары
ORDER BY Наименование;
Псевдонимы полей
В SQL-запросах можно изменять имена полей. Задаваемые при этом новые имена называются псевдонимами (aliases). Их удобно применять при задании в запросе вычисляемых полей. С помощью псевдонимов этим полям можно присваивать осмысленные имена. Псевдоним помещается после имени поля или вычисляемого выражения через ключевое слово AS.
В качестве примера воспользуемся предыдущим запросом, задав в нем псевдонима для вычисляемых полей:
SELECT [Код товара], Цена, Заказано, Продано, Цена *Продано AS [Сумма продажи]. Цена *3аказано AS [Сумма заказа]. Цена *Заказано-Цена*Продано AS [Разность] FROM Продажи
Агрегатные функции используются в предложении SELECT запроса, в качестве аргументов используются имена полей. В запросах используются следующие агрегатные функции:
- COUNT подсчёт количества строк, которые выбрал запрос
- SUM арифметическая сумма выбранных значений данного поля
- AVG среднее значение выбранных значений
- MAX наибольшее значение поля
- MIN наименьшее значение поля
Группировка данных
Группировка данных — это объединение записей в соответствии со значениями некоторого поля.
Для группировки результатов выборки совместно с оператором SELECT используется предложение GROUP BY. Данное предложение должно следовать после предложения WHERE, но перед предложением ORDER BY. После ключевых слов GROUP указывается список полей, включённых в выборку с помощью оператора SELECT, причем нужно обязательно указывать все отбираемые поля (за исключением полей, относящихся к агрегирующим функциям), хотя порядок их перечисления после предложения GROUP BY может не соответствовать порядку списка после слова SELECT.
Синтаксис оператора SELECT с предложением GROUP BY следующий: