Функции

Select avg(количество) average, min(количество) minimum,

Select count (distinct номер_поставщика) from SPJ

Агрегатные функции.

II. Использование функций

Select номер_детали, название, вес from P where вес in (12, 16, 17)

Результат:	номер_детали	Название	вес
	P1	Гайка
	P2	Болт
	P3	Винт
	P5	Кулачок

Подготовьте запрос и проверьте полученный результат.

12. Выбор по шаблону.

Для запросов с поиском по шаблону, основанных на поиске подстрок в полях типа CHARACTER, используются ключевое слова LIKE.

Включение в выражение ключевого слова NOT порождает условие c обратным смыслом. Ключевое слово LIKE соответствует стандарту ANSI.

СИМВОЛ	ЗНАЧЕНИЕ
LIKE
%	Заменяет последовательность символов
-	Заменяет любой одиночный символ
\	Отменяет специальное назначение следующего за ним символа

Примеры.

а) Выбрать список деталей, начинающихся с буквы "Б"[9]

Select номер_детали, название, вес from P where название like "Б%"

Результат:	номер_детали	название	вес
	P5	Болт
	P6	Блюм

Примеры.

а) Выдать общее количество поставщиков.

Select count (*) from S

Результат: 5

Подготовьте запрос и проверьте полученный результат.

б) Выдать общее количество поставщиков, поставляющих в настоящее время детали.

Результат: 4

Подготовьте запрос и проверьте полученный результат.

в) Выдать количество поставок для детали P2.

Select count (*) from SPJ where номер_детали='P2'

Результат: 5

Подготовьте запрос и проверьте полученный результат.

г) Выдать общее количество поставляемых деталей 'P2'.

Select sum (количество) from SPJ where номер_детали='P2'

Результат: 1000

Подготовьте запрос и проверьте полученный результат.

д) Выдать средний, минимальный и максимальный объем поставок для поставщика S1 с соответствующим заголовком.

max(количество) maximum from SPJ where номер_поставщика='S1'

Результат:	average	minimum	maximum
	216.6

Подготовьте запрос и проверьте полученный результат.

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. Выдать все триплеты "номер поставщика, номер детали и номер изделия", такие, что в каждом триплете указанные поставщик, деталь и изделие являются попарно соразмещенными в одном городе.

Вариант 4.

1. Выдать номера и фамилии поставщиков, поставляющих по крайней мере одну деталь, поставляемую по крайней мере одним поставщиком, который поставляет по крайней мере одну красную деталь. Т.е. необходимо выдать полные сведения о всех поставщиках которые поставляют такие детали, которые есть в поставках поставщиков, поставляющих красные детали.

2. Выдать список деталей, поставляющихся для всех изделий, изготавливаемых в Лондоне.

3. Выдать номера деталей, поставляемых каким-либо поставщиком из Лондона.

4. Выдать номера деталей, поставляемых для какого-либо изделия из Лондона.

Вариант 5.

1. Выдать номера изделий, для которых детали поставляются по крайней мере одним поставщиком не из того же самого города, что и изделие.

2. Выдать список всех поставок, в которых количество деталей находится в диапазоне от 300 до 750 включительно.

3. Выдать номера изделий, использующих, по крайней мере, одну деталь, поставляемую поставщиком S1. Т.е. показать такие изделия, для производства которых пригодились бы детали, поставляемые поставщиком S1.

4. Выдать номера и названия деталей, поставляемых для какого-либо изделия в Лондоне.

Варианты заданий на составление запросов по модификации информации из таблиц базы данных

Вариант 1.

1. Увеличить на 10 рейтинг всех поставщиков, рейтинг которых в настоящее время меньше, чем рейтинг поставщика S4.

2. Постройте таблицу, содержащую список номеров изделий, которые либо находятся в Лондоне, либо для них поставляются детали каким-нибудь поставщиком из Лондона.

Вариант 2.

1. Удалить все изделия, для которых нет поставок деталей.

2. Построить таблицу с номерами поставщиков и парами номеров деталей, таких, что некоторый поставщик поставляет обе указанные детали. При этом пары вида P1 и P2, а также P2 и P1 считать одинаковыми.

Вариант 3.

1. Увеличить размер поставки на 10 процентов для всех поставок тех поставщиков, которые поставляют какую-либо красную деталь.

2. Построить таблицу с комбинациями "цвет детали-город, где хранится деталь", исключая дубликаты пар (цвет-город).

Вариант 4.

1. Построить таблицу, содержащую список номеров деталей, которые поставляются либо каким-нибудь поставщиком из Лондона, либо для какого-либо изделия в Лондон.

2. Вставить в таблицу S нового поставщика с номером S10 с фамилией Уайт из города Нью-Йорк с неизвестным рейтингом.

Вариант 5.

1. Удалить все изделия из Рима и все соответствующие поставки.

2. Построить таблицу с упорядоченным списком всех городов, в которых размещаются по крайней мере один поставщик, деталь или изделие.

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

1. Что такое коррелированный запрос? Чем отличается коррелированный запрос от некоррелированного?

2. Какие существуют ограничения на формирование коррелированного запроса?

3. Каким образом сохранить результаты запроса в таблице?

4. Какими средствами SQL реализуются следующие операции реляционной алгебры: ограничение, декартово произведение, проекция, пересечение, объединение, разность, соединение?

5. Что такое внешнее соединение?

6. В каких случаях вместо фразы IN можно использовать операцию сравнения?

7. Какие существуют средства группирования в SQL? Как они используются?

[1]Эта и все, описанные ниже команды добавлены начиная с версии 5.12

[2] Атрибутыssl_cipher [2], x509_issuer, x509_subject обязательны для заполнения для версии сервера 5.12

[3] Для случая, если работаете на том же компьютере где запущен сервер БД

[4] Даны для справки, для текущей версии сервера может быть существенно расширены

[5] В случае если данные вставляются не во все ячейки таблицы то это указывается при формировании инструкции tablename(id, id2), где tablename – имя таблицы, а id, id2 наименования атрибутов таблицы.

[6] В случае использования табулятора \t

[7] В случае Enter \r\n

[8] См утилиту Mysqldump

[9] Примечание. Корректно работает только при задании кодировки по умолчанию. Задается в разделе MYSQLD default_character_set=win1251

Дата добавления: 2015-05-10