1. mysql> select f_name, l_name from employee_data2. -> where salary <= 900003. -> AND title NOT LIKE '%программист%';
Команда выводит имена (фамилию, за которой следует имя) сотрудников, которые не работают в отделе продаж и моложе 30 лет.
6. mysql> SELECT l_name, f_name from employee_data7. -> where title NOT LIKE '%продавец%'8. -> AND age < 30;9. mysql> select emp_id, f_name, l_name from10. -> employee_data where age > 3211. -> and age < 40;
13. mysql> select f_name, l_name from employee_data14. -> where age = 32 AND15. -> title NOT LIKE '%программист%';
Операторы IN и BETWEEN
Чтобы найти сотрудников, которые являются разработчиками Web или системными администраторами, можно использовать оператор SELECTследующего вида:
SELECT f_name, l_name, title from -> employee_data where -> title = 'разработчик Web' OR -> title = 'системный адм.'; На рис. 6.6. приведен результат запроса.
Рис. 6.6. Поиск сотрудников, которые являются разработчиками Web или системными администраторами
В SQL имеется более простой способ сделать это с помощью оператора IN (в множестве). Его использование не представляет никаких трудностей.
SELECT f_name, l_name, title from -> employee_data where title -> IN ('разработчик Web', 'системный адм.'); Результат будет аналогичен рис. 6.6.
Использование NOT перед IN позволяет вывести данные, которые не входят в множество, определяемое условиемIN. Следующий оператор выводит список сотрудников, которые не занимают должность программиста или системного администратора.
SELECT f_name, l_name, title from -> employee_data where title NOT IN -> ('программист', 'системный адм.'); Оператор BETWEEN используется для определения целочисленных границ. Поэтому вместо age >= 32 AND age <= 40можно использовать age BETWEEN 32 AND 40.
select f_name, l_name, age from -> employee_data where age BETWEEN -> 32 AND 40; На рис. 6.7. приведен результат запроса.
Рис. 6.7. Поиск сотрудников, возраст которых лежит в промежутке от 32-х до 40 лет
NOT также можно использовать вместе с BETWEEN, как в следующем операторе, который выводит сотрудников, зарплата которых меньше 90000 или больше 150000.
select f_name, l_name, salary -> from employee_data where salary -> NOT BETWEEN -> 90000 AND 150000; Задания
1. Найдите всех сотрудников, которые занимают должность "старший программист" и "программист мультимедиа".
2. Выведите список имен сотрудников, зарплата которых составляет от 70000 до 90000.
3. Что делает следующий оператор?
SELECT f_name, l_name, title fromemployee_data where title NOT IN('программист', 'старший программист','программист мультимедиа'); 4. Вот более сложный оператор, который объединяет BETWEEN и IN. Что он делает?
SELECT f_name, l_name, title, agefrom employee_data wheretitle NOT IN('программист', 'старший программист','программист мультимедиа') AND ageNOT BETWEEN 28 and 32; Возможные решения
1. mysql> select l_name, f_name, title2. -> from employee_data where3. -> title IN4. -> ('старший программист',5. -> 'программист мультимедиа');
7. 2. mysql> select f_name, l_name, salary from8. -> employee_data where salary BETWEEN9. -> 70000 AND 90000;
Выводятся имена и должности всех, кто не является программистом.
12. mysql> SELECT f_name, l_name, title from13. -> employee_data where title NOT IN14. -> ('программист', 'старший программист',15. -> 'программист мультимедиа');
Этот оператор выводит список тех, кто не является программистом, и которые младше 28 или старше 32 лет.
17. mysql> SELECT f_name, l_name, title, age from18. -> employee_data where title NOT IN19. -> ('программист', 'старший программист',20. -> 'программист мультимедиа') AND21. -> age NOT BETWEEN 28 AND 32;
Упорядочивание данных
Рассмотрим вопрос о том, как можно изменить порядок вывода данных, извлеченных из таблиц MySQL, используя предложение ORDER BY оператораSELECT.
Извлекаемые до сих пор данные всегда выводились в том порядке, в котором они были сохранены в таблице. В действительности SQL позволяет сортировать извлеченные данные с помощью предложения ORDER BY. Это предложение требует имя столбца, на основе которого будут сортироваться данные. Давайте посмотрим, как можно вывести имена сотрудников с упорядоченными по алфавиту фамилиями сотрудников (в возрастающем порядке).
SELECT l_name, f_name from
employee_data ORDER BY l_name;
А вот так сотрудников можно отсортировать по возрасту.
SELECT f_name, l_name, age
from employee_data
ORDER BY age;
Предложение ORDER BY может сортировать в возрастающем порядке (ASCENDING или ASC) или в убывающем порядке (DESCENDING или DESC) в зависимости от указанного аргумента.
Чтобы вывести список сотрудников в убывающем порядке, можно использовать следующий оператор.
SELECT f_name from employee_data
ORDER by f_name DESC;
Примечание: Возрастающий порядок (ASC) используется по умолчанию.
Задания
1. Вывести список сотрудников в порядке, определяемом зарплатой, которую они получают.
2. Выведите список сотрудников в убывающем порядке их стажа работы в компании.
3. Что делает следующий оператор?
SELECT emp_id, l_name, title, age
from employee_data ORDER BY
title DESC, age ASC;
4. Вывести список сотрудников (фамилию и имя), которые занимают должность "программист" или "разработчик Web" и отсортировать их фамилии по алфавиту.
Возможные решения
1. mysql> SELECT f_name, l_name, salary2. -> from employee_data3. -> ORDER BY salary;
5. mysql> SELECT f_name, l_name, yos6. -> from employee_data7. -> ORDER by yos;
Оператор выводит список сотрудников с идентификационным номером, должностью, и возрастом, отсортированный по должности в убывающем порядке и по возрасту в возрастающем порядке.
10. mysql> SELECT emp_id, l_name, title, age11. -> from employee_data ORDER BY12. -> title DESC, age ASC; Примечание: Сначала сортируются должности в убывающем порядке. Затем для каждой должности сортируется возраст сотрудников в возрастающем порядке.
13. mysql> SELECT l_name, f_name from employee_data14. -> where title IN ('программист',15. -> 'разработчик Web') ORDER BY l_name;
Ограничение количества извлекаемых данных
Далее рассмотрим, как ограничить число записей, выводимых операторомSELECT.
По мере увеличения таблиц возникает необходимость вывода только подмножества данных. Этого можно добиться с помощью предложения LIMIT.
Например, чтобы вывести из таблицы имена только первых пяти сотрудников, используется оператор LIMIT с аргументом равным 5.
SELECT f_name, l_name fromemployee_data LIMIT 5; На рис. 6.8. приведен результат запроса.
Рис. 6.8. Имена первых пяти сотрудников
Это первые пять записей таблицы.
Можно соединить оператор LIMIT с оператором ORDER BY. Таким образом, следующий оператор выведет четверых самых молодых сотрудников компании.
SELECT f_name, l_name, age fromemployee_data ORDER BY ageLIMIT 4; На рис. 6.9. приведен результат запроса.
Рис. 6.9. Четверо самых молодых сотрудников компании
Аналогично можно вывести двух самых старших сотрудников.
SELECT f_name, l_name, age fromemployee_data ORDER BY age LIMIT 2; На рис. 6.10. приведен результат запроса.
Рис. 6.10. Двое самых старших сотрудников
Извлечение подмножеств
LIMIT можно использовать также для извлечения подмножества данных, используя дополнительные аргументы.
Общая форма оператора LIMIT имеет следующий вид:
SELECT (что-нибудь) from таблица LIMIT начальная строка, извлекаемое число записей; SELECT f_name, l_name fromemployee_data LIMIT 6,3; На рис. 6.11. приведен результат запроса.
Рис. 6.11. Извлечение трёх строк начиная с 6-й
Будут извлечены три строки, начиная с шестой.
Задания
Найдите имена 5 самых молодых сотрудников компании.
Извлеките 5 записей, начиная с 10 строки.
Выведите имя и зарплату сотрудника, который получает самую большую зарплату.
Что делает следующий оператор?
SELECT emp_id, age, perksfrom employee_data ORDER BYperks DESC LIMIT 10; Возможные решения
1. mysql> SELECT f_name, l_name, age from2. -> employee_data ORDER BY age3. -> LIMIT 5;
5. mysql> SELECT * from employee_data6. -> LIMIT 10, 5;
8. mysql> select f_name, l_name, salary9. -> from employee_data10. -> ORDER BY salary DESC11. -> LIMIT 1;
Этот оператор выводит идентификационный номер, возраст и надбавки 10 сотрудников, которые получают наибольшие надбавки.
14. mysql> SELECT emp_id, age, perks15. -> from employee_data ORDER BY16. -> perks DESC LIMIT 10;
Ключевое слово DISTINCT
Рассмотрим теперь, как выбрать и вывести записи таблиц MySQL с помощьюключевого слова DISTINCT (РАЗЛИЧНЫЙ), использование которого исключает появление повторяющихся данных.
Чтобы вывести все должности базы данных компании, можно выполнить следующий оператор:
select title from employee_data; На рис. 6.12. приведен результат запроса.
Рис. 6.12. Все должности базы данных компании
Можно видеть, что список содержит повторяющиеся данные. Предложение SQL DISTINCT выводит только уникальные данные. Вот как оно используется.
select DISTINCT title from employee_data; На рис. 6.13. приведен результат запроса.
Рис. 6.13. Все должности базы данных компании без повторов
Из этого можно видеть, что в компании имеется 11 уникальных должностей.
Уникальные записи можно также отсортировать с помощью ORDER BY.
select DISTINCT age from employee_dataORDER BY age; На рис. 6.14. приведен результат запроса.
Рис. 6.14. Значения возраста сотрудников компании без повторов
DISTINCT часто используется вместе с функцией COUNT, которая будет рассмотрена далее.
Задания
Сколько уникальных вариантов зарплаты имеется в компании? Представьте их в убывающем порядке.
Сколько различных имен имеется в базе данных?
Возможные решения
1. select distinct salary from2. employee_data order by salary DESC;
4. mysql> select distinct f_name from employee_data;
Изменение записей
Команда UPDATE выполняет изменение данных в таблицах. Она имеет очень простой формат.
UPDATE имя_таблицы SETимя_столбца_1 = значение_1,имя_столбца_2 = значение_2,имя_столбца_3 = значение_3, ...[WHERE условия]; Как и все другие команды SQL можно вводить ее на одной строке или на нескольких строках.
Рассмотрим несколько примеров.
Предположим, директор увеличил свою зарплату на 20000 и надбавки на 5000. Его предыдущая зарплата была 200000, а надбавки были 50000.
UPDATE employee_data SETsalary=220000, perks=55000WHERE title='директор'; На рис. 6.15. приведен результат запроса.
Рис. 6.15. Данные обновлены
Можно проверить эту операцию, выводя данные из таблицы.
select salary, perks fromemployee_data WHEREtitle = 'директор'; На рис. 6.16. приведен результат запроса.
Рис. 6.16. Данные обновлены
В действительности предыдущую зарплату знать не требуется. Можно воспользоваться арифметическими операторами. Следующий оператор сделает то же самое, при этом исходные данные знать заранее не требуется.
UPDATE employee_data SETsalary = salary + 20000,perks = perks + 5000WHERE title='директор'; Результат запроса аналогичен рис. 6.15.
В качестве другого примера можно попробовать изменить название должности "разработчик Web" на "программист Web".
mysql> update employee_data SET -> title = 'программист Web' -> WHERE title = 'разработчик Web'; На рис. 6.17. приведен результат запроса.
Рис. 6.17. Данные обновлены
Важно также перед выполнением изменений внимательно изучить часть оператора с условием, так как легко можно изменить не те данные. Оператор UPDATE без условий изменит все данные столбца во всех строках. Надо быть очень осторожным при внесении изменений.
Задания
Измените фамилию Чащина на Петрова. Внесите соответствующие изменения в базу данных.
Название должности "программист мультимедиа" необходимо изменить на "специалист по мультимедиа".
Увеличьте зарплату всем сотрудниками (кроме директора) на 10000.
Возможные решения
1. mysql> update employee_data SET2. -> l_name = 'Петрова'3. -> WHERE l_name = 'Чашина'; 4. Примечание: Если бы были еще сотрудники с фамилией Чащина, то эти записи также были бы изменены. В таком случае может помочь столбец emp_id, так как он содержит уникальные значения. Лучше использовать значениеemp_id вместо l_name, как в следующем примере:
5. mysql> update employee_data SET6. -> l_name = 'Петрова'7. -> WHERE emp_id = 4;8. mysql> update employee_data set9. -> title = 'специалист по мультимедиа'10. -> where title = 'программист мультимедиа';
12. mysql> update employee_data set13. -> salary = salary + 1000014. -> where title != 'директор';
7. Лекция: Команды обработки данных
Поиск минимального и максимального значений
Минимальное значение
Максимальное значение
Задания
Возможные решения
Поиск среднего значения и суммы
Суммирование значений столбца с помощью функции SUM
Рис. 6.6. Поиск сотрудников, которые являются разработчиками Web или системными администраторами
В SQL имеется более простой способ сделать это с помощью оператора IN (в множестве). Его использование не представляет никаких трудностей.
SELECT f_name, l_name, title from -> employee_data where title -> IN ('разработчик Web', 'системный адм.'); Результат будет аналогичен рис. 6.6.
Использование NOT перед IN позволяет вывести данные, которые не входят в множество, определяемое условиемIN. Следующий оператор выводит список сотрудников, которые не занимают должность программиста или системного администратора.
SELECT f_name, l_name, title from -> employee_data where title NOT IN -> ('программист', 'системный адм.'); Оператор BETWEEN используется для определения целочисленных границ. Поэтому вместо age >= 32 AND age <= 40можно использовать age BETWEEN 32 AND 40.
select f_name, l_name, age from -> employee_data where age BETWEEN -> 32 AND 40; На рис. 6.7. приведен результат запроса.
Рис. 6.7. Поиск сотрудников, возраст которых лежит в промежутке от 32-х до 40 лет
NOT также можно использовать вместе с BETWEEN, как в следующем операторе, который выводит сотрудников, зарплата которых меньше 90000 или больше 150000.
select f_name, l_name, salary -> from employee_data where salary -> NOT BETWEEN -> 90000 AND 150000; Задания
1. Найдите всех сотрудников, которые занимают должность "старший программист" и "программист мультимедиа".
2. Выведите список имен сотрудников, зарплата которых составляет от 70000 до 90000.
3. Что делает следующий оператор?
SELECT f_name, l_name, title fromemployee_data where title NOT IN('программист', 'старший программист','программист мультимедиа'); 4. Вот более сложный оператор, который объединяет BETWEEN и IN. Что он делает?
SELECT f_name, l_name, title, agefrom employee_data wheretitle NOT IN('программист', 'старший программист','программист мультимедиа') AND ageNOT BETWEEN 28 and 32; Возможные решения
1. mysql> select l_name, f_name, title2. -> from employee_data where3. -> title IN4. -> ('старший программист',5. -> 'программист мультимедиа'); 
Рис. 6.8. Имена первых пяти сотрудников
Это первые пять записей таблицы.
Можно соединить оператор LIMIT с оператором ORDER BY. Таким образом, следующий оператор выведет четверых самых молодых сотрудников компании.
SELECT f_name, l_name, age fromemployee_data ORDER BY ageLIMIT 4; На рис. 6.9. приведен результат запроса.
Рис. 6.9. Четверо самых молодых сотрудников компании
Аналогично можно вывести двух самых старших сотрудников.
SELECT f_name, l_name, age fromemployee_data ORDER BY age LIMIT 2; На рис. 6.10. приведен результат запроса.
Рис. 6.10. Двое самых старших сотрудников
Извлечение подмножеств
LIMIT можно использовать также для извлечения подмножества данных, используя дополнительные аргументы.
Общая форма оператора LIMIT имеет следующий вид:
SELECT (что-нибудь) from таблица LIMIT начальная строка, извлекаемое число записей; SELECT f_name, l_name fromemployee_data LIMIT 6,3; На рис. 6.11. приведен результат запроса.
Рис. 6.11. Извлечение трёх строк начиная с 6-й
Будут извлечены три строки, начиная с шестой.
Задания
Рис. 6.12. Все должности базы данных компании
Можно видеть, что список содержит повторяющиеся данные. Предложение SQL DISTINCT выводит только уникальные данные. Вот как оно используется.
select DISTINCT title from employee_data; На рис. 6.13. приведен результат запроса.
Рис. 6.13. Все должности базы данных компании без повторов
Из этого можно видеть, что в компании имеется 11 уникальных должностей.
Уникальные записи можно также отсортировать с помощью ORDER BY.
select DISTINCT age from employee_dataORDER BY age; На рис. 6.14. приведен результат запроса.
Рис. 6.14. Значения возраста сотрудников компании без повторов
DISTINCT часто используется вместе с функцией COUNT, которая будет рассмотрена далее.
Задания
Рис. 6.15. Данные обновлены
Можно проверить эту операцию, выводя данные из таблицы.
select salary, perks fromemployee_data WHEREtitle = 'директор'; На рис. 6.16. приведен результат запроса.
Рис. 6.16. Данные обновлены
В действительности предыдущую зарплату знать не требуется. Можно воспользоваться арифметическими операторами. Следующий оператор сделает то же самое, при этом исходные данные знать заранее не требуется.
UPDATE employee_data SETsalary = salary + 20000,perks = perks + 5000WHERE title='директор'; Результат запроса аналогичен рис. 6.15.
В качестве другого примера можно попробовать изменить название должности "разработчик Web" на "программист Web".
mysql> update employee_data SET -> title = 'программист Web' -> WHERE title = 'разработчик Web'; На рис. 6.17. приведен результат запроса.
Рис. 6.17. Данные обновлены
Важно также перед выполнением изменений внимательно изучить часть оператора с условием, так как легко можно изменить не те данные. Оператор UPDATE без условий изменит все данные столбца во всех строках. Надо быть очень осторожным при внесении изменений.
Задания