1. Агрегатные функции.
Примеры.
а) Выдать общее количество поставщиков.
Select count (*) from S
Результат: 5
Подготовьте запрос и проверьте полученный результат.
б) Выдать общее количество поставщиков, поставляющих в настоящее время детали.
Select count ( distinct номер_поставщика ) from SPJ
Результат: 4
Подготовьте запрос и проверьте полученный результат.
в) Выдать количество поставок для детали P2.
Select count (*) from SPJ where номер_детали='P2'
Результат: 5
Подготовьте запрос и проверьте полученный результат.
г) Выдать общее количество поставляемых деталей 'P2'.
Select sum (количество) from SPJ where номер_детали='P2'
Результат: 1000
Подготовьте запрос и проверьте полученный результат.
д) Выдать средний, минимальный и максимальный объем поставок для поставщика S1 с соответствующим заголовком.
Select avg(количество) average, min(количество) minimum,
max(количество) maximum from SPJ where номер_поставщика='S1'
| Результат: | average | minimum | maximum |
| 216.6 | 100 | 400 |
Подготовьте запрос и проверьте полученный результат.
2. Ниже привиден перечень всех функций, используемых в операторе Select
Функции
select_expression может содержать следующие функции и операторы:
| + - * / | Арифметические действия. |
| % | Остаток от деления (как в C) |
| | & | Битовые функции (используется 48 бит). |
| - С | Мена знака числа. |
| ( ) | Скобки. |
| BETWEEN(A, B, C) | (A >= B) AND (A <= C). |
| BIT_COUNT() | Количество бит. |
| ELT(N, a, b, c, d) | Возвращает a, если N == 1, b, если N == 2 и т. д. a, b, c, d строки. ПРИМЕР: ELT(3, "First", "Second", "Third", "Fourth") вернет "Third". |
| FIELD(Z, a, b, c) | Возвращает a, если Z == a, b, если Z == b и т. д. a, b, c, d строки. ПРИМЕР: FIELD("Second", "First", "Second", "Third", "Fourth") вернет "Second". |
| IF(A, B, C) | Если A истина (!= 0 and != NULL), то вернет B, иначе вернет C. |
| IFNULL(A, B) | Если A не null, вернет A, иначе вернет B. |
| ISNULL(A) | Вернет 1, если A == NULL, иначе вернет 0. Эквивалент ('A == NULL'). |
| NOT ! | NOT, вернет TRUE (1) или FALSE (0). |
| OR, AND | Вернет TRUE (1) или FALSE (0). |
| SIGN() | Вернет -1, 0 или 1 (знак аргумента). |
| SUM() | Сумма столбца. |
| = <> <= < >= > | Вернет TRUE (1) или FALSE (0). |
| expr LIKE expr | Вернет TRUE (1) или FALSE (0). |
| expr NOT LIKE expr | Вернет TRUE (1) или FALSE (0). |
| expr REGEXP expr | Проверяет строку на соответствие регулярному выражению expr. |
select_expression может также содержать один или большее количество следующих математических функций.
| ABS() | Абсолютное значение (модуль числа). |
| CEILING() | () |
| EXP() | Экспонента. |
| FORMAT(nr, NUM) | Форматирует число в формат '#, ###, ###.##' с NUM десятичных цифр. |
| LOG() | Логарифм. |
| LOG10() | Логарифм по основанию 10. |
| MIN(), MAX() | Минимум или максимум соответственно. Должна иметь при вызове два или более аргументов, иначе рассматривается как групповая функция. |
| MOD() | Остаток от деления (аналог %). |
| POW() | Степень. |
| ROUND() | Округление до ближайшего целого числа. |
| RAND([integer_expr]) | Случайное число типа float, 0 <= x <= 1.0, используется integer_expr как значение для запуска генератора. |
| SQRT() | Квадратный корень. |
select_expression может также содержать одну или больше следующих строковых функций.
| CONCAT() | Объединение строк. |
| INTERVAL(A, a, b, c, d) | Возвращает 1, если A == a, 2, если A == b... Если совпадений нет, вернет 0. A, a, b, c, d... строки. |
| INSERT(org, strt, len, new) | Заменяет подстроку org[strt...len(gth)] на new. Первая позиция строки=1. |
| LCASE(A) | Приводит A к нижнему регистру. |
| LEFT() | Возвращает строку символов, отсчитывая слева. |
| LENGTH() | Длина строки. |
| LOCATE(A, B) | Позиция подстроки B в строке A. |
| LOCATE(A, B, C) | Позиция подстроки B в строке A, начиная с позиции C. |
| LTRIM(str) | Удаляет все начальные пробелы из строки str. |
| REPLACE(A, B, C) | Заменяет все подстроки B в строке A на подстроку C. |
| RIGHT() | Get string counting from right. |
| RTRIM(str) | Удаляет хвостовые пробелы из строки str. |
| STRCMP() | Возвращает 0, если строки одинаковые. |
| SUBSTRING(A, B, C) | Возвращает подстроку из A, с позиции B до позиции C. |
| UCASE(A) | Переводит A в верхний регистр. |
Еще несколько просто полезных функций, которые тоже можно применить в select_expression.
| CURDATE() | Текущая дата. |
| DATABASE() | Имя текущей базы данных из которой выполняется выбор. |
| FROM_DAYS() | Меняет день на DATE. |
| NOW() | Текущее время в форматах YYYYMMDDHHMMSS или "YYYY-MM-DD HH:MM:SS". Формат зависит от того в каком контексте используется NOW(): числовом или строковом. |
| PASSWORD() | Шифрует строку. |
| PERIOD_ADD(P:N) | Добавить N месяцев к периоду P (в формате YYMM). |
| PERIOD_DIFF(A, B) | Возвращает месяцы между A и B. Обратите внимание, что PERIOD_DIFF работает только с датами в форме YYMM или YYYMM. |
| TO_DAYS() | Меняет DATE (YYMMDD) на номер дня. |
| UNIX_TIMESTAMP([date]) | Возвращает метку времени unix, если вызвана без date (секунды, начиная с GMT 1970.01.01 00:00:00). При вызове со столбцом TIMESTAMP вернет TIMESTAMP. date может быть также строкой DATE, DATETIME или числом в формате YYMMDD (или YYYMMDD). |
| USER() | Возвращает логин текущего пользователя. |
| WEEKDAY() | Возвращает день недели (0 = понедельник, 1 = вторник, ...). |
Групповые функции в операторе select:
Следующие функции могут быть использованы в предложении GROUP:
| AVG() | Среднее для группы GROUP. |
| SUM() | Сумма элементов GROUP. |
| COUNT() | Число элементов в GROUP. |
| MIN() | Минимальный элемент в GROUP. |
| MAX() | Максимальный элемент в GROUP. |
Задание:
1. Выполнить проверку запросов из:
· 1го раздела (2, 6, 7, 9, 12), 2го раздела (а, б, д)
2. Подготовить 3 запроса с использованием различных функций работа с полем дата, со строковыми данными (в том числе групповых).
3. Подготовить и выполнить средствами СУБД MySQL 4 запроса по выборке информации из таблиц базы данных с использованием агрегатных функций..
4. Подготовить и выполнить средствами СУБД MySQL 2 запроса по модификации информации (вставка, удаление, замещение) из таблиц базы данных для решения нижеприведенных задач. При этом в тех заданиях, где речь идет о создании таблиц, предполагается формировании постоянной таблицы базы данных.
Варианты заданий на составление запросов по выборке информации из таблиц базы данных
Вариант 1.
1. Для каждой поставляемой для некоторого изделия детали выдать ее номер, номер изделия и соответствующее общее поставляемое количество деталей.
2. Выдать все триплеты "номер поставщика, номер детали и номер изделия", такие, что в каждом триплете указанные поставщик, деталь и изделие не являются попарно соразмещенными в одном городе.
3. Выдать номера изделий, для которых детали полностью поставляет поставщик S1. Т.е. поставляемых поставщиком S1 деталей достаточно для полного комплектования изделия. Состав деталей изделия можно оценить на основе базового набора данных таблицы поставка, имея в виду что в базовом наборе данных отражен полный состав всех изделий.
4. Выдать номера и фамилии поставщиков, поставляющих детали для какого-либо изделия с деталью P1 в количестве, большем, чем средний объем поставок детали P1 для этого изделия.
Вариант 2.
1. Выдать общее количество деталей P1, поставляемых поставщиком S1.
2. Выдать все пары названий городов, таких, что какой-либо поставщик из первого города поставляет детали для некоторого изделия, изготовляемого во втором городе.
3. Выдать номера изделий, использующих только детали, поставляемые поставщиком S1.
4. Выдать номера деталей, поставляемых каким-либо поставщиком из Лондона, для изделия, изготавливаемого также в Лондоне.
Вариант 3.
1. Выдать номера и фамилии поставщиков, поставляющих одну и ту же деталь для всех перечисленных изделий. Перечень изделий согласовать с преподавателем.
2. Выдать общее число изделий (не деталей), для которых поставляет детали поставщик S1.
3. Выдать номера изделий, детали для которых поставляет каждый поставщик, поставляющий какую-либо красную деталь. Т.е. необходимо получить такие номера изделий, детали для которой поставляются всеми поставщиками, среди поставляемых деталей которого есть детали красного цвета.
4. Выдать все триплеты "номер поставщика, номер детали и номер изделия", такие, что в каждом триплете указанные поставщик, деталь и изделие являются попарно соразмещенными в одном городе.