Когда портятся хорошие выражения
До сих пор все SQL-выражения для работы с данными, приведенные в этой главе, были правильными и играли по правилам. Однако, исходя из описаний таблиц person и favorite_food, у вас есть много возможностей наделать ошибок при вставке или изменении данных.
Распространенные ошибки:
1. Неуникальный первичный ключ.
Поскольку описания таблиц включают создание ограничений первичного ключа, MySQL проверит, чтобы в таблицы не вводились дублирующие значения. Следующее выражение делает попытку обойти свойство автоприращения столбца person_id и создать в таблице person еще одну строку со значением person_id, равным 1:
INSERT INTO person > (person_id, fname, lname, gender, birth_date)
VALUES (1, 'Charles','Fulton', 'M', '1968-01-15');
Ничто не мешает (по крайней мере, в текущей схеме) создать две строки с идентичными именами, адресами, датами рождения и т. д., если в столбце person_id у них разные значения.
2. Несуществующий внешний ключ
Описание таблицы favorite_food включает создание ограничения внешнего ключа для столбца person_id. Это ограничение гарантирует, что все значения person_id, введенные в таблицу favorite_food, имеются в таблице person. Вот что произошло бы при попытке создания строки, нарушающей это ограничение:
INSERT INTO favorite_food (person_id, food)
VALUES (999, 'lasagna');
В этом случае таблица favorite_food считается дочерней (child), а таблица person – родителем (parent), поскольку таблица favorite_food зависит от данных таблицы person. Если требуется ввести данные в обе таблицы, сначала следует создать строку в person, а затем уже можно будет ввести данные в favorite_food. Ограничения внешнего ключа выполняются, только если таблицы создаются с использованием механизма хранения InnoDB.
3. Применение недопустимых значений
Столбец gender таблицы person может иметь только два значения: 'M' для мужчин и 'F' для женщин. Если по ошибке делается попытка задать любое другое значение, будет ошибка
UPDATE person
SET gender = 'Z'
WHERE person_id = 1;
Выражение update не даст сбой, но будет сформировано предупреждение. Чтобы увидеть описание предупреждения, можно выполнить команду show warnings
Сервер MySQL не забраковал выражение, но также и не произвел ожидаемых результатов. Чтобы решить эту проблему, сервер MySQL заполняет столбец gender пустой строкой ('') – определенно не тем, что предполагалось получить.
Оператор SELECT.
Для получения данных из таблиц используется оператор SELECT. Он состоит из нескольких блоков.
Разберем на примерах. В качестве примера, будем использовать таблицу:
Рассмотрим таблицу:
Запрос для создания такой таблицы:
CREATE DATABASE mydb;
CREATE TABLE IF NOT EXISTS `mydb`.`realization` (
`id_realization` INT NOT NULL AUTO_INCREMENT,
`Data` DATETIME NOT NULL,
`customer` VARCHAR(45) NOT NULL,
`product` VARCHAR(45) NOT NULL,
`count` INT NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`id_realization`))
ENGINE = InnoDB;
INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('2', '2007-10-03 13:56:00', 'Иванов', 'Принтер', '3', '3500');
INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('2', '2007-10-03 14:30:00', 'Петров', 'Принтер', '2', '3500');
INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('3', '2007-10-03 14:35:00', 'Сидоров', 'Ноутбук', '1', '45000');
INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('4', '2007-10-03 16:30:00', 'Калюбанов', 'Ноутбук', '1', '45000');
INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('5', '2007-10-04 10:30:00', 'Иванов', 'Мышь', '5', '1200');
INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('6', '2007-10-04 10:35:00', 'Лосев', 'Ноутбук', '1', '45000');
INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('7', '2007-10-04 11:35:00', 'Чуркин', 'Принтер', '1', '3500');
INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('8', '2007-10-05 10:35:00', 'Сидоров', 'Принтер', '4', '3500');
1. Для того чтобы получить всю таблицу будем использовать, уже знакомый нам, запрос:
SELECT * FROM realization;
SELECT – выбрать
* - выбрать все столбцы
FROM – из
2. Если требуется выбрать только некоторые столбцы указываем их через запятую после слова SELECT:
SELECT customer, product FROM realization;
3. Для устранения повторяющихся строк используется команда DISTINCT
SELECT DISTINCT customer FROM realization;
Обратная команда ALL – она включена по умолчанию.
4. Если требуется извлечь данные по какому-то условию, используется команда WHERE – ГДЕ :
SELECT * FROM realization WHERE customer = 'Иванов';
Получим только записи по клиенту Иванову.
5. Условия могут быть составными:
SELECT * FROM realization WHERE customer = 'Иванов' or customer = 'Сидоров';
6. В условиях можно применять знаки сравнения:
SELECT product FROM realization WHERE price<=3500;
7. Сравнивать можно и даты:
SELECT * FROM realization WHERE data<'2007-10-04 10:30:00';
При составлении условия в блоке WHERE используются операторы сравнения и логические операторы AND OR NOT, а также операторы IN, BETWEEN, LIKE, IS NULL
8. Получить все продажи, кроме принтера.
SELECT * FROM realization WHERE NOT product = 'Принтер';
9. Получить продажи товаров, все, кроме принтера и мыши
SELECT * FROM realization WHERE NOT (product = 'Принтер' OR product = 'Мышь');
10. Получить продажи По Сидорову и Лосеву:
SELECT * FROM realization WHERE customer = 'Лосев' or customer = 'Сидоров';
11. Но можно тоже самое сделать с помощью оператора IN:
SELECT * FROM realization WHERE customer IN ('Лосев', 'Сидоров');
Оператор IN определяет набор значений, в который данное значение может или может не быть включено.
12. BETWEEN определяет диапазон значений.
SELECT * FROM realization WHERE price BETWEEN 3500 AND 40000;
Границы диапазона включаются.
13. Оператор LIKE применим только для полей типа CHAR или VARCHAR. И осуществляет поиск подстроки по шаблону. Имеется два вида шаблонов:
Символ _ - замещает одиночный символ
Символ % замещает последовательность символов.
SELECT * FROM realization WHERE customer LIKE 'Л%'
SELECT * FROM realization WHERE customer LIKE 'Л_сев'
14. Часто бывает необходимо выбрать только те значения, которые не равны NULL. Для этого используют команду NOT NULL. Обратная команда IS NULL
SELECT * FROM realization WHERE customer IS NULL;
SELECT * FROM realization WHERE NOT customer IS NULL;
15. В выражении SELECT можно также использовать вычисляемые поля. О них более подробно будем проходить на других уроках. Сейчас кратко:
SELECT data, customer, product, count, price, count*price FROM realization;
Таким образом, мы получили сумму по товару. Но название столбца нам не нравится. Чтобы придать вычисляемому полю нормальное название, используем псевдоним:
SELECT data, customer, product, count, price, count*price AS summ FROM realization;
Встроенные функции
Каждый сервер БД включает множество встроенных функций для работы с данными. Выделяют функции для работы со строками, с датами, с числами.
Функции для работы со строками:
1. Одна из наиболее широко используемых строковых функций, возвращающих числа, – функция length() (длина), которая возвращает число символов в строке.
SELECT LENGTH(customer) FROM realization;
2. Кроме определения длины строки может потребоваться найти местоположение подстроки в строке. Для этого используется функция position() (положение)
SELECT POSITION('сев' IN customer) FROM realization;
Если не получается найти подстроку, функция position() возвращает 0. Порядковый номер первого символа в строке равен 1. Если функция position() возвращает значение 0, это указывает на то, что подстрока не найдена, а не на то, что подстрока обнаружена в строке на первой позиции.
3. Если требуется начать поиск не с первого символа целевой строки, необходимо использовать функцию locate(), аналогичную функции position() за тем исключением, что допускает третий необязательный параметр, предназначенный для задания стартовой позиции поиска.
SELECT LOCATE ('сев', customer, 3) FROM realization;
4. Еще одна функция, принимающая строки в качестве аргументов и возвращающая числа, – функция сравнения строк strcmp(). Strcmp(), которая реализована только в MySQL и не имеет аналогов в Oracle Database или SQL Server. Она принимает в качестве аргументов две строки и возвращает одно из следующих значений:
-1 если первая строка в порядке сортировки расположена до второй строки
0 если строки идентичны
1 если первая строка в порядке сортировки расположена после второй строки
SELECT STRCMP('12345','12345') 12345_12345; 0
SELECT STRCMP('abcd','xyz') abcd_xyz; -1
SELECT STRCMP('xyz','qrstuv') xyz_qrstuv; 1
5. Функции concat(). Соединяет строки.
SELECT CONCAT('Клиент: ', customer) customer_txt FROM realization;
Кроме упомянутых, сервера включают множество других встроенных функций для работы со строковыми данными. Более подробную информацию можно получить в справочном руководстве по SQL для конкретного сервера или универсальном справочнике по SQL, например «SQL in a Nutshell» (O’Reilly).
Функции для работы с числовыми данными:
Для вычислений доступны все обычные арифметические операторы (+, -, *, /), а для задания порядка вычислений – скобки. Большинство функций для работы с числовыми данными являются специфическими (минус, косинус и т.п.) изучать мы их не будем. Рассмотрим только некоторые:
1. Функция mod() – вычисляет остаток от деления.
SELECT MOD(10,4);
2. Функция pow() – возведение в степень.
SELECT POW(2,8);
3. Функция round() – округление.
SELECT ROUND(72.0909, 1), ROUND(72.0909, 2), ROUND(72.0909, 3);
4. Функция sign() (знак), возвращает: 1, если число отрицательн0, 0, если равно нулю, и 1, если число положительно.
5. Функция abs() – число по модулю.
Функции для работы с датами и временем
1. Функция now() – возвращает текущую дату
SELECT NOW();
2. Функции DAY(), MONTH(), YEAR() – возвращают соответственно день, месяц и год заданной даты.
3. Функция MySQL date_add(дата, интервал) позволяет добавить любой интервал (т. е. дни, месяцы, года) к заданной дате, чтобы получить другую дату
SELECT DATE_ADD(DATA, INTERVAL 5 DAY) FROM realization;
Типы интервалов
Second Количество секунд
Minute Количество минут
Hour Количество часов
Day Количество дней
Month Количество месяцев
Year Количество лет
Minute_second Количества минут и секунд, разделенные двоеточием
Hour_second Количества часов, минут и секунд, разделенные двоеточием
Year_month Количества лет и месяцев, разделенные дефисом
4. Функция last_day() (последний день) – последний день месяца
Все функции можно посмотреть в справочнике
http://www.mysql.ru/docs/man/Functions.html
Группировка и агрегаты.
Агрегатные функции:
Max() Возвращает максимальное значение из набора.
Min() Возвращает минимальное значение из набора.
Avg() Возвращает среднее значение набора.
Sum() Возвращает сумму значений из набора.
Count() Возвращает количество значений в наборе.
SELECT MAX(price),
MIN(price),
AVG(price),
SUM(price),
COUNT(*)
FROM realization;
Мало кого заинтересуют необработанные данные; тем, кто занимается анализом, потребуются обработанные данные, приведенные к виду, наиболее соответствующему их нуждам.
Необходимо понять, что такое группировка:
| ФИО | ТОВАР | КОЛИЧЕСТВО | ЦЕНА |
| Иванов | принтер | ||
| Петров | мышь | ||
| Иванов | принтер | ||
| Иванов | мышь | ||
| Петров | принтер | ||
| Петров | принтер | ||
| Лосев | Ноутбук | ||
| Лосев | ноутбук | ||
| Иванов | мышь |
Группировка может быть по какому-либо столбцу. Например, группировка по ФИО будет выглядеть следующим образом:
| ФИО | ТОВАР | КОЛИЧЕСТВО | ЦЕНА |
| Иванов | ИТОГ | ||
| Иванов | принтер | ||
| Иванов | принтер | ||
| Иванов | мышь | ||
| Иванов | мышь | ||
| Петров | ИТОГ | ||
| Петров | мышь | ||
| Петров | принтер | ||
| Петров | принтер | ||
| Лосев | ИТОГ | ||
| Лосев | Ноутбук | ||
| Лосев | ноутбук |
1. Это самая простая группировка:
SELECT customer, SUM(count*price)
FROM realization
GROUP BY customer;
Этот запрос выявит все группировки по клиентам и посчитает итог по каждой группировке. В данном случае итог – это сумма вычисляемого столбца count*price.
Здесь добавлен новый блок GROUP BY.
2. В некоторых случаях может понадобиться сформировать группы, охватывающие более одного столбца.
SELECT customer, product, SUM(count*price)
FROM realization
GROUP BY customer, product;
3. Если мы хотим увидеть не только окончательные итоги, но и промежуточные итоги, то необходимо использовать предложение with rollup в блоке group by:
SELECT customer, product, SUM(count*price)
FROM realization
GROUP BY customer, product with rollup;
4. При группировке данных тоже можно применять условия фильтрации к данным после
формирования групп. Этот тип условий фильтрации должен располагаться в блоке having.
SELECT customer, SUM(count*price)
FROM realization
GROUP BY customer
HAVING SUM(count*price)>5000;
Фильтр WHERE воздействует на данные до группировки, а HAVING– после создания групп. В блок having можно включить агрегатные функции, не перечисленные в блоке select, в блок WHERE нельзя включать агрегатные функции.
Связи между таблицами.
До сих пор мы работали с одной таблицей. Теперь посмотрим, как получать данные из нескольких таблиц. Большинство запросов все же обращены к двум, трем или даже более таблицам.
Чтобы получить информацию из нескольких таблиц их необходимо соединить. Соединений бывает несколько:
1. Внутреннее соединение
2. Внешнее соединение
3. Левое соединение
4. Правое соединение
5. Кросс-соединение
Для иллюстрации этих соединений рассмотрим две таблицы:
| СОТРУДНИКИ | ||
| ID | ФИО | ID_Отдел |
| Иванов | ||
| Петров | ||
| Сидоров | ||
| Лосев | ||
| Чуркин | ||
| 6 | Лисин | 5 |
| Конев | ||
| Попов | ||
| Иваненко | ||
| Туманов |
| Отделы | |
| ID | Наименование |
| Бухгалтерия | |
| Снабжение | |
| Продажи | |
| Маркетинг |
Обратите внимание, что есть отдел маркетинга, в таблице Отделы, в этом отделе никто не работает и есть некто Лисин, который работает в отделе, который не существует.
Внутреннее соединение получит у нас данные из таблиц, но только те, для которых есть связные данные в каждой таблице. Запрос будет выглядеть следующим образом:
SELECT *
FROM сотрудники INNER JOIN отделы
ON Сотрудники.ID_Отдел = Отделы.ID;
Левое соединение. Левой таблицей считается таблица, которая в запросе встречается первая, вторая таблица будет правой. При левом соединении мы получим все записи из левой таблицы, независимо от того есть ли соответствующая запись в правой таблице. Если соответствующей записи не будет, мы получим в результате NULL значение. Запрос будет выглядеть следующим образом:
SELECT *
FROM сотрудники LEFT JOIN отделы
ON Сотрудники.ID_Отдел = Отделы.ID;
Правое соединение аналогично левому, только будут выведены все записи из правой таблицы.
Запрос будет выглядеть следующим образом:
SELECT *
FROM сотрудники RIGHT JOIN отделы
ON Сотрудники.ID_Отдел = Отделы.ID;
Внешнее соединение. Получим все записи из всех таблиц, и по возможности соответствующие записи из связных таблиц. Запрос будет выглядеть следующим образом:
SELECT *
FROM сотрудники FULL OUTER JOIN отделы
ON Сотрудники.ID_Отдел = Отделы.ID;
Кросс объединение. Соответствует операции произведения в реляционной алгебре. Условие связи отсутствует и мы получим всевозможные соответствия между двумя таблицами.
SELECT *
FROM сотрудники CROSS JOIN отделы ;
Рассмотрим пример.
Схема будет следующая:
Запрос для схемы:
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- Schema Lessons
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema Lessons
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Lessons` DEFAULT CHARACTER SET utf8 ;
USE `Lessons` ;
-- -----------------------------------------------------
-- Table `Lessons`.`teachers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Lessons`.`teachers` (
`idteacher` INT NOT NULL,
`FIO` VARCHAR(100) NULL,
`DATA` DATE NULL,
PRIMARY KEY (`idteacher`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Lessons`.`courses`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Lessons`.`courses` (
`idcourses` INT NOT NULL,
`title` VARCHAR(100) NULL,
`hour_count` INT NULL,
PRIMARY KEY (`idcourses`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Lessons`.`lesson`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Lessons`.`lesson` (
`idlesson` INT NOT NULL,
`data` DATE NULL,
`class` CHAR(3) NULL,
`teachers_idteacher` INT NOT NULL,
`courses_idcourses` INT NOT NULL,
PRIMARY KEY (`idlesson`),
INDEX `fk_lesson_teachers_idx` (`teachers_idteacher` ASC),
INDEX `fk_lesson_courses1_idx` (`courses_idcourses` ASC),
CONSTRAINT `fk_lesson_teachers`
FOREIGN KEY (`teachers_idteacher`)
REFERENCES `Lessons`.`teachers` (`idteacher`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_lesson_courses1`
FOREIGN KEY (`courses_idcourses`)
REFERENCES `Lessons`.`courses` (`idcourses`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Заполним таблицу данными. Запрос на внесение данных:
INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('1', 'WEB-мастеринг. Основы серверного программирования.', '100');
INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('2', 'Web-маркетинг', '106');
INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('3', 'Разработка WEB-приложений ASR.net', '134');
INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('4', 'Базы данных', '146');
INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('5', 'Основы С++', '96');
INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('6', 'Современные языки программирования', '86');
INSERT INTO `lessons`.`teachers` (`idteacher`, `FIO`, `DATA`) VALUES ('1', 'Иванов Иван Иванович', '1982-10-12');
INSERT INTO `lessons`.`teachers` (`idteacher`, `FIO`, `DATA`) VALUES ('2', 'Петров Петр Петрович', '1970-12-12');
INSERT INTO `lessons`.`teachers` (`idteacher`, `FIO`, `DATA`) VALUES ('3', 'Лосев Игорь Викторович', '1975-08-08');
INSERT INTO `lessons`.`teachers` (`idteacher`, `FIO`, `DATA`) VALUES ('4', 'Чуркин Петр Иванович', '1991-01-13');
INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('1', '2016-11-07', '301', '1', '1');
INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('2', '2016-11-07', '302', '2', '1');
INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('3', '2016-11-07', '303', '3', '2');
INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('4', '2016-11-08', '301', '1', '2');
INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('5', '2016-11-08', '302', '2', '1');
INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('6', '2016-11-08', '303', '3', '4');
INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('7', '2016-11-09', '301', '1', '1');
INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('8', '2016-11-09', '302', '2', '5');
Требуется получить:
1. Кто из преподавателей читал курс 30 сентября
SELECT teachers.FIO FROM teachers
INNER JOIN lesson ON teachers.idteacher = lesson.teachers_idteacher
WHERE lesson.data = '2016-11-07';
2. Кто из преподавателей читает курсы WEB
SELECT distinct teachers.FIO, courses.title FROM teachers
INNER JOIN lesson ON teachers.idteacher = lesson.teachers_idteacher
INNER JOIN courses ON courses.idcourses = lesson. courses_idcourses
WHERE courses.title LIKE '%web%';
3. Какие курсы читает преподаватель Иванов
SELECT distinct courses.title FROM courses
INNER JOIN lesson ON courses.idcourses = lesson. courses_idcourses
INNER JOIN teachers ON teachers.idteacher = lesson.teachers_idteacher
WHERE teachers.FIO = 'Иванов Иван Иванович';
4. Когда последний раз преподаватели вели занятия
SELECT teachers.FIO, max(lesson.Data) FROM teachers
INNER JOIN lesson ON teachers.idteacher = lesson.teachers_idteacher
GROUP BY teachers.FIO;
5. Если есть преподаватели, которые курсы еще не читали, то записи о них отсутствуют в таблице уроков. Но что делать, если нам они тоже нужны в результате? Тогда соединение в запросе будет не внутренним, а левым:
SELECT teachers.FIO, max(lesson.Data) FROM teachers
LEFT JOIN lesson ON teachers.idteacher = lesson.teachers_idteacher
GROUP BY teachers.FIO;