GROUP BY

1. GROUP BY сделка | товар | сумма 1 | банан | 4242 2 | яблоко | 90 3 | груша | 209 4 | банан | 31337 5 | яблоко | 2039

2. GROUP BY SELECT `tovar`, SUM(`summa`)‏ FROM `sdelki` GROUP BY `tovar`;

3. GROUP BY +---------+--------------+ | `tovar` | SUM(`summa`) | +---------+--------------+ | банан | 35579 | | груша | 209 | | яблоко | 2129 | +---------+--------------+

4. GROUP BY ... HAVING SELECT `tovar`, AVG(`summa`)‏ FROM `sdelki` GROUP BY `tovar` HAVING AVG(`summa`) > 100;

5. GROUP BY +---------+--------------+ | `tovar` | AVG(`summa`) | +---------+--------------+ | банан | 17789.5000 | | груша | 209.0000 | +---------+--------------+

6. GROUP BY отдел | товар | сумма 1 | банан | 4242 1 | яблоко | 90 1 | груша | 209 1 | банан | 31337 2 | яблоко | 2039 2 | яблоко | 6302

7. GROUP BY ... HAVING SELECT `otdel`, `tovar`, AVG(`summa`)‏ FROM `sdelki2` GROUP BY `tovar`; ???

8. GROUP BY +-------+---------+------------+ | otdel | tovar | avg(summa) | +-------+---------+------------+ | 1 | банан | 17789.5000 | | 1 | груша | 209.0000 | | 1 | яблоко | 2810.3333 | +-------+---------+------------+

9. GROUP BY SELECT `otdel`, `tovar`, AVG(`summa`)‏ FROM `sdelki2` GROUP BY `tovar`, `otdel`; ???

10. GROUP BY +-------+---------+------------+ | otdel | tovar | avg(summa) | +-------+---------+------------+ | 1 | банан | 17789.5000 | | 1 | груша | 209.0000 | | 1 | яблоко | 90.0000 | | 2 | яблоко | 4170.5000 | +-------+---------+------------+

11. WHERE: условия SELECT ... FROM ... WHERE условие1 AND условие2 OR (условие3 AND условие4); Для сравнения можно использовать: =, <, >, >=, <=, <>

12. WHERE: условия SELECT * FROM `sdelki2` WHERE `tovar`='яблоко' OR (`tovar`<>'груша' AND `summa`>5000); ???

13. WHERE: условия +---------+-------+ | tovar | summa | +---------+-------+ | яблоко | 90 | | банан | 31337 | | яблоко | 2039 | | яблоко | 6302 | +---------+-------+

14. WHERE: логические операторы • NOT – отрицание • IS NULL – не определено • BETWEEN ... AND – принадлежность диапазону • IN – принадлежность множеству • LIKE – соответствие шаблону (”%”)‏

15. WHERE: логические операторы SELECT `tovar,``summa` FROM `sdelki2` WHERE `summa` NOT BETWEEN 0 AND 5000; ???

16. WHERE: логические операторы +--------------+-------+ | tovar | summa | +--------------+-------+ | банан | 31337 | | яблоко | 6302 | +--------------+-------+

17. WHERE: логические операторы SELECT `tovar,``summa` FROM `sdelki2` WHERE `tovar` IN ('груша', 'яблоко'); ???

18. WHERE: логические операторы +--------------+-------+ | tovar | summa | +--------------+-------+ | яблоко | 90 | | груша | 209 | | яблоко | 2039 | | яблоко | 6302 | +--------------+-------+

19. WHERE: логические операторы SELECT `tovar,``summa` FROM `sdelki2` WHERE `tovar` LIKE '%а%'; ???

20. WHERE: логические операторы +------------+-------+ | tovar | summa | +------------+-------+ | банан | 4242 | | груша | 209 | | банан | 31337 | +------------+-------+

21. Как идентифицировать конкретную строку в таблице?

22. Первичный ключ • Найти поле с неповторяющимися значениями • Найти несколько полей с неповторяющимися значениями • Создать специальное поле с уникальными значениями – идентификатор записи

23. Первичный ключ: MySQL CREATE TABLE `t1` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ... );

24. Первичный ключ: MySQL SERIAL == BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

25. Первичный ключ: MySQL CREATE TABLE `t1` ( `id` SERIAL PRIMARY KEY, ... );

26. Первичный ключ: MySQL CREATE TABLE `t2` ( `id` SERIAL, `another_id` BIGINT UNSIGNED UNIQUE, PRIMARY KEY (`id`, `another_id`)‏ );

27. Внешний ключ • Для добавления в одну таблицу ссылки на значения из другой • Значения внешнего ключа одной таблицы совпадают со значениями первичного ключа другой таблицы

28. Внешний ключ: 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;

29. Ссылочная целостность: что? • Добавление новой строки-потомка – значение ее внешнего ключа должно быть равно одному из значений первичного ключа таблицы-предка • Обновление внешнего ключа в строке-потомке – аналогично предыдущему

30. Ссылочная целостность: что? • Удаление строки-предка – значения всех внешних ключей станут неверными • Обновление строки-предка – аналогично предыдущему

31. Ссылочная целостность: как? • Перед выполнением INSERT и UPDATE автоматически проверяются значения внешних ключей • Другие проблемы решить сложнее, поскольку есть разные варианты: • Запретить операцию? • Автоматически обновлять/удалять строки-потомки? • Ставить в NULL?

32. Ссылочная целостность: как? Предусмотрены правила удаления (ON DELETE) и обновления (ON UPDATE): • RESTRICT / NO ACTION – запрещать удаление/обновление первичного ключа, если есть потомки (вариант по умолчанию)‏ • CASCADE – автоматические удаление/изменение • SET NULL – присваивать NULL

33. Ссылочная целостность: MySQL CREATE TABLE `laptops` ( ... CONSTRAINT `made_by` FOREIGN KEY(`manufacturer_id`) REFERENCES `manufacturers`(`id`)‏ ON DELETE CASCADE ) TYPE=InnoDB;

34. Ссылочная целостность: MySQL CREATE TABLE `manufacturers` ( `id` SERIAL PRIMARY KEY, `name` VARCHAR(42)‏ ) TYPE=InnoDB;

35. Ссылочная целостность: 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;

36. Ссылочная целостность: 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');

37. Ссылочная целостность: MySQL +----+---------+ | id | name | +----+---------+ | 1 | Sony | | 2 | Lenovo | | 3 | Acer | | 4 | Dell | | 5 | ASUSTeK | +----+---------+

38. Ссылочная целостность: 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);

39. Ссылочная целостность: MySQL +----+-----------------+ | id | manufacturer_id | +----+-----------------+ | 8 | 1 | | 9 | 1 | | 10 | 1 | | 11 | 2 | | 12 | 2 | +----+-----------------+

40. Ссылочная целостность: MySQL DELETE FROM `manufacturers` WHERE `name`='Sony'; SELECT * FROM `laptops`; ???

41. Ссылочная целостность: MySQL +----+-----------------+ | id | manufacturer_id | +----+-----------------+ | 11 | 2 | | 12 | 2 | +----+-----------------+