Удаление записей из таблицы
Для удаления записей из таблицы можно использовать оператор DELETE.
Оператор удаления DELETE требует задания имени таблицы и необязательных условий.
DELETE from имя_таблицы [WHERE условия];Примечание: Если никакие условия не будут заданы, то удаляются все данные в таблице.
Предположим, один из специалистов по мультимедиа 'Василий Пупкин' уволился из компании. Надо удалить его запись.
DELETE from employee_dataWHERE l_name = 'Пупкин';Результат запроса приведен на рис. 5.19.
Рис. 5.19. Результат удаления записи из таблицы
Булевы (логические) операторы.
Рис. 6.1. Имена сотрудников, которые получают более 70000, но меньше 90000
Давайте выведем список сотрудников, фамилии которых начинаются с буквы К или Л.
SELECT l_name from employee_data wherel_name like 'К%' OR l_name like 'Л%'; На рис. 6.2. приведен результат запроса.
Рис. 6.2. Сотрудники, фамилии которых начинаются с буквы К или Л
Вот более сложный пример ... список имен и возраста сотрудников, фамилии которых начинаются с К или Л, и которые младше 30 лет.
SELECT f_name, l_name , age from employee_datawhere (l_name like 'К%' OR l_name like 'Л%') AND age < 30; На рис. 6.3. приведен результат запроса.
Рис. 6.3. Список имен и возраста сотрудников, фамилии которых начинаются с К или Л, и которые младше 30 лет
Обратите внимание на использование скобок в представленном выше операторе. Скобки предназначены для выделения различных логических условий и удаления двусмысленностей.
Оператор NOT поможет при поиске всех сотрудников, которые не являются программистами. (Программисты включают старших программистов, программистов мультимедиа и программистов).
SELECT f_name, l_name, title from employee_datawhere title NOT LIKE "%программист%"; На рис. 6.4. приведен результат запроса.
Рис. 6.4. Поиск всех сотрудников, которые не являются программистами
И последний пример перед упражнениями.
Показать всех сотрудников со стажем работы в компании более 3 лет, которые старше 30 лет.
select f_name, l_name from employee_datawhere yos > 3 AND age > 30; На рис. 6.5. приведен результат запроса.
Рис. 6.5. Все сотрудники, которые старше 30 лет, и имеют стаж работы более 3 лет
Задания
1. Вывести имена и фамилии всех сотрудников, которые получают зарплату не более 90000 и не являются программистами, старшими программистами или программистами мультимедиа.
2. Что делает следующий оператор?
SELECT l_name, f_name from employee_datawhere title NOT LIKE '%продавец%'AND age < 30; 3. Вывести все идентификационные номера и имена сотрудников в возрасте от 32 до 40 лет.
4. Выберите имена всех сотрудников в возрасте 32 лет, которые не являются программистами.
Возможные решения
| ||
|
Рис. 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, title -> from employee_data where -> title IN -> ('старший программист', -> 'программист мультимедиа');2. 2. mysql> select f_name, l_name, salary from -> employee_data where salary BETWEEN -> 70000 AND 90000; 
Рис. 6.8. Имена первых пяти сотрудников
Это первые пять записей таблицы.
Можно соединить оператор LIMIT с оператором ORDER BY. Таким образом, следующий оператор выведет четверых самых старых сотрудников компании.
SELECT f_name, l_name, age fromemployee_data ORDER BY age DESCLIMIT 4; На рис. 6.9. приведен результат запроса.
Рис. 6.9. Четверо самых старых сотрудников компании
Аналогично можно вывести двух самых младших сотрудников.
SELECT f_name, l_name, age fromemployee_data ORDER BY ageLIMIT 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 без условий изменит все данные столбца во всех строках. Надо быть очень осторожным при внесении изменений.
Задания
Рис. 7.1. Поиск минимальной зарплаты
Максимальное значение
select MAX(salary) from employee_data; На рис. 7.2. приведен результат запроса.
Рис. 7.2. Поиск максимальной зарплаты
Задания
Рис. 7.3. Минимальные надбавки
2.
mysql> select MAX(salary) from employee_data -> where title = 'программист'; На рис. 7.4. приведен результат запроса.
Рис. 7.4. Максимальная зарплата среди программистов
3. mysql> select MAX(age) from employee_data -> where title = 'продавец'; На рис. 7.5. приведен результат запроса.
Рис. 7.5. Возраст самого старого продавца
4. Вот один из способов сделать без использования агрегатных функций.
mysql> select f_name, l_name, age -> from employee_data -> order by age DESC limit 1; На рис. 7.6. приведен результат запроса.
Рис. 7.6. Имя и фамилия самого старого сотрудника
Поиск среднего значения и суммы
Суммирование значений столбца с помощью функции SUM
Агрегатная функция SUM() вычисляет общую сумму значений в столбце. Для этого необходимо задать имя столбца, которое должно быть помещено внутри скобок.
Давайте посмотрим, сколько компания BigFoot тратит на зарплату своих сотрудников.
select SUM(salary) from employee_data; На рис. 7.7. приведен результат запроса.
Рис. 7.7. Сумма всех зарплат
Аналогично можно вывести общую сумму надбавок, выдаваемых сотрудникам.
select SUM(perks) from employee_data; На рис. 7.8. приведен результат запроса.
Рис. 7.8. Сумма всех надбавок
Можно найти также общую сумму зарплаты и надбавок.
select sum(salary) + sum(perks) from employee_data; На рис. 7.9. приведен результат запроса.
Рис. 7.9. Общая сумма зарплаты и надбавок
Здесь показаны также дополнительные возможности команды SELECT. Значения можно складывать, вычитать, умножать или делить. В действительности можно записывать полноценные арифметические выражения.
Вычисление среднего значения
Агрегатная функция AVG() используется для вычисления среднего значения данных в столбце.
select avg(age) from employee_data; На рис. 7.10. приведен результат запроса.
Рис. 7.10. Средний возраст сотрудников
Пример выше вычисляет средний возраст сотрудников компании BigFoot, а следующий выводит среднюю зарплату.
select avg(salary) from employee_data; На рис. 7.11. приведен результат запроса.
Рис. 7.11. Средняя зарплата сотрудников
Задания
Рис. 7.12. Сумма всех возрастов сотрудников
2. mysql> select SUM(yos) from employee_data;
На рис. 7.13. приведен результат запроса.
Рис. 7.13. Общее количество лет стажа работы сотрудников
3. mysql> select SUM(salary), AVG(age) -> from employee_data where -> title = 'программист'; На рис. 7.14. приведен результат запроса.
Рис. 7.14. Сумма зарплат и средний возраст программистов
4. Этот оператор выводит процент зарплаты, которую сотрудники BigFoot получают в качестве надбавок.
mysql> select (SUM(perks)/SUM(salary) * 100) -> from employee_data; На рис. 7.15. приведен результат запроса.
Рис. 7.15. Процент зарплаты, которую сотрудники получают в качестве надбавок Именование столбцов
MySQL позволяет задавать имена для выводимых столбцов. Поэтому вместо f_name или l_name и т.д. можно использовать более понятные и наглядные термины. Это делается с помощью оператора AS.
select avg(salary) AS
'Средняя зарплата' from
employee_data;
На рис. 7.16. приведен результат запроса.
Рис. 7.16. Вывод средней зарплаты с использованием псевдо-имен столбцов.
Такие псевдо-имена могут сделать вывод более понятным для пользователей. Важно только помнить, что при задании псевдо-имен с пробелами необходимо заключать такие имена в кавычки. Вот еще один пример:
select (SUM(perks)/SUM(salary) * 100)
AS 'Процент надбавок' from
employee_data;
На рис. 7.17. приведен результат запроса.
Рис. 7.17. Вывод процента зарплаты, которую сотрудники получают в качестве надбавок с использованием псевдо-имен
Подсчет числа записей
Агрегатная функция COUNT() подсчитывает и выводит общее число записей. Например, чтобы подсчитать общее число записей в таблице, выполните следующую команду.
select COUNT(*) from employee_data;
На рис. 7.18. приведен результат запроса.
Рис. 7.18. Общее количество записей
Как мы уже знаем, знак * означает "все данные".
Теперь давайте подсчитаем общее число сотрудников, которые занимают должность "программист".
select COUNT(*) from employee_data
where title = 'программист';
На рис. 7.19. приведен результат запроса.
Рис. 7.19. Общее количество сотрудников-программистов
Группировка данных
Предложение GROUP BY позволяет группировать аналогичные данные. Поэтому, чтобы вывести все уникальные должности в таблице, можно выполнить команду
select title from employee_data
GROUP BY title;
На рис. 7.20. приведен результат запроса.
Рис. 7.20. Все уникальные должности сотрудников
Можно видеть, что это аналогично использованию DISTINCT в предыдущей лекции.
Вот как можно подсчитать число сотрудников имеющих определенную должность.
select title, count(*)
from employee_data GROUP BY title;
На рис. 7.21. приведен результат запроса.
Рис. 7.21. Количество сотрудников по должностям
В предыдущей команде MySQL сначала создает группы различных должностей, а затем выполняет подсчет в каждой группе.
Сортировка данных
Теперь давайте найдем и выведем число сотрудников, имеющих различные должности, и отсортируем их с помощью ORDER BY.
select title, count(*) AS Number
from employee_data
GROUP BY title
ORDER BY Number;
На рис. 7.22. приведен результат запроса.
Рис. 7.22. Количество сотрудников по должностям с сортировкой
Задания
Рис. 7.23. Число сотрудников, которые проработали более трех лет
2. mysql> select age, count(*)
-> from employee_data
-> GROUP BY age;
На рис. 7.24. приведен результат запроса.
Рис. 7.24. Количество сотрудников в группах одного возраста
3. mysql> select age, count(*)
-> from employee_data
-> GROUP BY age
-> ORDER by age DESC;
На рис. 7.25. приведен результат запроса.
Рис. 7.25. Количество сотрудников в группах одного возраста с обратной сортировкой
4. mysql> select title, AVG(age)
-> from employee_data
-> GROUP BY title;
На рис. 7.26. приведен результат запроса.
Рис. 7.26. Средний возраст сотрудников по должностям
5. mysql> select title, AVG(age)
-> AS 'средний возраст'
-> from employee_data
-> GROUP BY title
-> ORDER BY 'средний возраст' DESC;
На рис. 7.27. приведен результат запроса.
Рис. 7.27. Средний возраст сотрудников по должностям с использованием псевдо-имени столбца
Примечание: Нам нужно задать псевдо-имя для столбца, содержащего среднее значение возраста, чтобы его можно было сортировать.
Работа с датой
До сих пор мы имели дело с текстом (varchar) и числами (int). Чтобы понять тип данных date (дата) создадим еще одну таблицу, аналогично тому, как была создана первая таблица.
Создадим в текстовом редакторе файл employee_per.dat, который содержит оператор создания таблицы CREATE следующего вида:
CREATE TABLE employee_per (e_id int unsigned not null primary key, -- идентификационный номерaddress varchar(60), -- адресphone int, -- номер телефонаp_email varchar(60), -- адрес e-mailbirth_date DATE, -- дата рожденияsex ENUM('M', 'F'), -- полm_status ENUM('Y','N'), -- статусs_name varchar(40), -- имяchildren int); -- количество детей и последовательность операторов INSERT, например, такого вида. Количество записей может быть произвольно.
INSERT INTO employee_per (e_id, address, phone, p_email, birth_date, sex, m_status, s_name, children) values (1, 'Арбат, 12', 7176167, 'anna@yandex.ru', '1972-03-16', 'M', 'Y', 'Анна Петрова', 2); Затем загрузим этот файл, как мы делали раньше, в базу данных.
В системе Windows
1). Поместите файл в каталог c:\mysql\bin.
2). Выполните в приглашении DOS команду.
dosprompt> mysql employees <employee_per.dat 3). Запустите программу клиента mysql и проверьте, что таблица была создана, с помощью команды SHOW TABLES;.
В системе Linux
1). Перейдите в каталог, в котором находится файл.
2). В приглашении введите следующую команду:
$prompt> mysql employees <employee_per.dat -u your_username -p 3). Проверьте, что таблица была создана с помощью команды SHOW TABLES; в программе клиента mysql.
Данные таблицы можно вывести с помощью команды DESCRIBE.
mysql> DESCRIBE employee_per; Результат запроса представлен на рис. 9.1.
Рис. 9.1. Просмотр данных таблицы
Обратите внимание, что столбец birth_date имеет тип столбца date (дата). Здесь также присутствует еще один новый тип столбца ENUM, который был рассмотрен в лекции 4.
e-id: идентификатор сотрудника, такой же как в таблице employee_data
address: адрес сотрудника
phone: номер телефона
p_email: личный адрес e-mail
birth_date: дата рождения
sex: Пол сотрудника, мужской (M) или Женский (F)
m_status: семейное положение, в браке (Y) или холост (N).
s_name: Имя супруга (NULL, если сотрудник холост)
children: Число детей (NULL, если детей нет)
Особенности типа данных Date
Даты в MySQL всегда представлены с годом, за которым следует месяц и затем день месяца. Даты часто записывают в виде YYYY-MM-DD, где YYYY -- 4 цифры года, MM -- 2 цифры месяца и DD -- 2 цифры дня месяца.
Операции с датами
Тип столбца даты позволяет выполнять несколько операций, таких как сортировка, проверка условий с помощью операторов сравнения и т.д.
Использование операторов = и !=
select p_email, phone from employee_per where birth_date = '1969-12-31'; Результат запроса представлен на рис. 9.2.
Рис. 9.2. Поиск по дате рождения
Примечание: MySQL требует, чтобы даты были заключены в кавычки.
Использование операторов >= и <=
select e_id, birth_date from employee_per where birth_date >= '1970-01-01'; Результат запроса представлен на рис. 9.3.
Рис. 9.3. Поиск по дате рождения с использованием оператора >=
Дата добавления: 2016-02-04; просмотров: 938;