430 likes | 584 Views
GROUP BY. сделка | товар | сумма 1 | банан | 4242 2 | яблоко | 90 3 | груша | 209 4 | банан | 31337 5 | яблоко | 2039. GROUP BY. SELECT `tovar`, SUM(`summa`) FROM `sdelki` GROUP BY `tovar`;. GROUP BY. +---------+--------------+
E N D
GROUP BY сделка | товар | сумма 1 | банан | 4242 2 | яблоко | 90 3 | груша | 209 4 | банан | 31337 5 | яблоко | 2039
GROUP BY SELECT `tovar`, SUM(`summa`) FROM `sdelki` GROUP BY `tovar`;
GROUP BY +---------+--------------+ | `tovar` | SUM(`summa`) | +---------+--------------+ | банан | 35579 | | груша | 209 | | яблоко | 2129 | +---------+--------------+
GROUP BY ... HAVING SELECT `tovar`, AVG(`summa`) FROM `sdelki` GROUP BY `tovar` HAVING AVG(`summa`) > 100;
GROUP BY +---------+--------------+ | `tovar` | AVG(`summa`) | +---------+--------------+ | банан | 17789.5000 | | груша | 209.0000 | +---------+--------------+
GROUP BY отдел | товар | сумма 1 | банан | 4242 1 | яблоко | 90 1 | груша | 209 1 | банан | 31337 2 | яблоко | 2039 2 | яблоко | 6302
GROUP BY ... HAVING SELECT `otdel`, `tovar`, AVG(`summa`) FROM `sdelki2` GROUP BY `tovar`; ???
GROUP BY +-------+---------+------------+ | otdel | tovar | avg(summa) | +-------+---------+------------+ | 1 | банан | 17789.5000 | | 1 | груша | 209.0000 | | 1 | яблоко | 2810.3333 | +-------+---------+------------+
GROUP BY SELECT `otdel`, `tovar`, AVG(`summa`) FROM `sdelki2` GROUP BY `tovar`, `otdel`; ???
GROUP BY +-------+---------+------------+ | otdel | tovar | avg(summa) | +-------+---------+------------+ | 1 | банан | 17789.5000 | | 1 | груша | 209.0000 | | 1 | яблоко | 90.0000 | | 2 | яблоко | 4170.5000 | +-------+---------+------------+
WHERE: условия SELECT ... FROM ... WHERE условие1 AND условие2 OR (условие3 AND условие4); Для сравнения можно использовать: =, <, >, >=, <=, <>
WHERE: условия SELECT * FROM `sdelki2` WHERE `tovar`='яблоко' OR (`tovar`<>'груша' AND `summa`>5000); ???
WHERE: условия +---------+-------+ | tovar | summa | +---------+-------+ | яблоко | 90 | | банан | 31337 | | яблоко | 2039 | | яблоко | 6302 | +---------+-------+
WHERE: логические операторы • NOT – отрицание • IS NULL – не определено • BETWEEN ... AND – принадлежность диапазону • IN – принадлежность множеству • LIKE – соответствие шаблону (”%”)
WHERE: логические операторы SELECT `tovar,``summa` FROM `sdelki2` WHERE `summa` NOT BETWEEN 0 AND 5000; ???
WHERE: логические операторы +--------------+-------+ | tovar | summa | +--------------+-------+ | банан | 31337 | | яблоко | 6302 | +--------------+-------+
WHERE: логические операторы SELECT `tovar,``summa` FROM `sdelki2` WHERE `tovar` IN ('груша', 'яблоко'); ???
WHERE: логические операторы +--------------+-------+ | tovar | summa | +--------------+-------+ | яблоко | 90 | | груша | 209 | | яблоко | 2039 | | яблоко | 6302 | +--------------+-------+
WHERE: логические операторы SELECT `tovar,``summa` FROM `sdelki2` WHERE `tovar` LIKE '%а%'; ???
WHERE: логические операторы +------------+-------+ | tovar | summa | +------------+-------+ | банан | 4242 | | груша | 209 | | банан | 31337 | +------------+-------+
Как идентифицировать конкретную строку в таблице?
Первичный ключ • Найти поле с неповторяющимися значениями • Найти несколько полей с неповторяющимися значениями • Создать специальное поле с уникальными значениями – идентификатор записи
Первичный ключ: MySQL CREATE TABLE `t1` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ... );
Первичный ключ: MySQL SERIAL == BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
Первичный ключ: MySQL CREATE TABLE `t1` ( `id` SERIAL PRIMARY KEY, ... );
Первичный ключ: MySQL CREATE TABLE `t2` ( `id` SERIAL, `another_id` BIGINT UNSIGNED UNIQUE, PRIMARY KEY (`id`, `another_id`) );
Внешний ключ • Для добавления в одну таблицу ссылки на значения из другой • Значения внешнего ключа одной таблицы совпадают со значениями первичного ключа другой таблицы
Внешний ключ: MySQL CREATE TABLE `laptops` ( `id` SERIAL PRIMARY KEY, `manufacturer_id` BIGINT UNSIGNED NOT NULL, CONSTRAINT `made_by` FOREIGN KEY(`manufacturer_id`) REFERENCES `manufacturers`(`id`) ) TYPE=InnoDB;
Ссылочная целостность: что? • Добавление новой строки-потомка – значение ее внешнего ключа должно быть равно одному из значений первичного ключа таблицы-предка • Обновление внешнего ключа в строке-потомке – аналогично предыдущему
Ссылочная целостность: что? • Удаление строки-предка – значения всех внешних ключей станут неверными • Обновление строки-предка – аналогично предыдущему
Ссылочная целостность: как? • Перед выполнением INSERT и UPDATE автоматически проверяются значения внешних ключей • Другие проблемы решить сложнее, поскольку есть разные варианты: • Запретить операцию? • Автоматически обновлять/удалять строки-потомки? • Ставить в NULL?
Ссылочная целостность: как? Предусмотрены правила удаления (ON DELETE) и обновления (ON UPDATE): • RESTRICT / NO ACTION – запрещать удаление/обновление первичного ключа, если есть потомки (вариант по умолчанию) • CASCADE – автоматические удаление/изменение • SET NULL – присваивать NULL
Ссылочная целостность: MySQL CREATE TABLE `laptops` ( ... CONSTRAINT `made_by` FOREIGN KEY(`manufacturer_id`) REFERENCES `manufacturers`(`id`) ON DELETE CASCADE ) TYPE=InnoDB;
Ссылочная целостность: MySQL CREATE TABLE `manufacturers` ( `id` SERIAL PRIMARY KEY, `name` VARCHAR(42) ) TYPE=InnoDB;
Ссылочная целостность: MySQL CREATE TABLE `laptops` ( `id` SERIAL PRIMARY KEY, `manufacturer_id` BIGINT UNSIGNED NOT NULL, CONSTRAINT `made_by` FOREIGN KEY(`manufacturer_id`) REFERENCES `manufacturers`(`id`) ON DELETE CASCADE ) TYPE=InnoDB;
Ссылочная целостность: MySQL INSERT INTO `manufacturers` (`name`) VALUES ('Sony'); INSERT INTO `manufacturers` (`name`) VALUES ('Lenovo'); INSERT INTO `manufacturers` (`name`) VALUES ('Acer'); INSERT INTO `manufacturers` (`name`) VALUES ('Dell'); INSERT INTO `manufacturers` (`name`) VALUES ('ASUSTeK');
Ссылочная целостность: MySQL +----+---------+ | id | name | +----+---------+ | 1 | Sony | | 2 | Lenovo | | 3 | Acer | | 4 | Dell | | 5 | ASUSTeK | +----+---------+
Ссылочная целостность: MySQL INSERT INTO `laptops` (`manufacturer_id`) VALUES (1); INSERT INTO `laptops` (`manufacturer_id`) VALUES (1); INSERT INTO `laptops` (`manufacturer_id`) VALUES (1); INSERT INTO `laptops` (`manufacturer_id`) VALUES (2); INSERT INTO `laptops` (`manufacturer_id`) VALUES (2);
Ссылочная целостность: MySQL +----+-----------------+ | id | manufacturer_id | +----+-----------------+ | 8 | 1 | | 9 | 1 | | 10 | 1 | | 11 | 2 | | 12 | 2 | +----+-----------------+
Ссылочная целостность: MySQL DELETE FROM `manufacturers` WHERE `name`='Sony'; SELECT * FROM `laptops`; ???
Ссылочная целостность: MySQL +----+-----------------+ | id | manufacturer_id | +----+-----------------+ | 11 | 2 | | 12 | 2 | +----+-----------------+