Исследуем тестовую базу с mysql в терминале.
Логи щупания mysql, тестовой базы world от mysql.com. Часть первая и единственная.
оглавление
- 01 Внешние ключи
- 02 Оператор SELECT
- 03 Агрегатные функции
- 04 JOIN Clause
01Внешние ключи
bash:mysql> CREATE DATABASE learn_mysql; Query OK, 1 row affected (0.00 sec) mysql> USE learn_mysql; Database changed mysql> CREATE TABLE parent ( -> id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE child ( -> id INT NOT NULL, -> parent_id INT, -> INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) -> REFERENCES parent(id) -> ON DELETE CASCADE -> ) ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql> SHOW CREATE TABLE child; SHOW CREATE TABLE parent; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | child | CREATE TABLE `child` ( `id` int(11) NOT NULL, `parent_id` int(11) NOT NULL, KEY `par_ind` (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) +--------+--------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------+ | parent | CREATE TABLE `parent` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------+--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO parent VALUES (1), (2), (3), (4); INSERT INTO child VALUES (1,1), (2,1), (3,1), (4,2), (5,4); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM parent; SELECT * FROM child; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec) +----+-----------+ | id | parent_id | +----+-----------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 4 | +----+-----------+ 5 rows in set (0.00 sec) mysql> mysql> /* попытка добавить ребенка с несуществующим родителем */ mysql> INSERT INTO child VALUES (6,6); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn_mysql`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE) mysql> mysql> mysql> DELETE FROM child WHERE id = 6; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM parent; SELECT * FROM child; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec) +----+-----------+ | id | parent_id | +----+-----------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 4 | +----+-----------+ 5 rows in set (0.00 sec) mysql> /* удаление родителей с детьми (ON DELETE CASCADE) */ mysql> DELETE FROM parent WHERE id = 1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM parent; SELECT * FROM child; +----+ | id | +----+ | 2 | | 3 | | 4 | +----+ 3 rows in set (0.00 sec) +----+-----------+ | id | parent_id | +----+-----------+ | 4 | 2 | | 5 | 4 | +----+-----------+ 2 rows in set (0.00 sec)
02Оператор SELECT
Оператор SELECT используется для извлечения записей из одной или нескольких таблиц в MySQL.
bash:SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]] [HAVING where_condition] [WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] ...] [ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE]] mysql> SHOW SCHEMAS; +---------------------+ | Database | +---------------------+ | ... | | information_schema | | world | | ... | +---------------------+ 11 rows in set (0.00 sec) mysql> USE world; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec) mysql> DESC city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM city LIMIT 10; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ 10 rows in set (0.00 sec) mysql> SELECT name, continent FROM country WHERE continent = 'Oceania'; +--------------------------------------+-----------+ | name | continent | +--------------------------------------+-----------+ | American Samoa | Oceania | | Australia | Oceania | | Cocos (Keeling) Islands | Oceania | | Cook Islands | Oceania | | Christmas Island | Oceania | | Fiji Islands | Oceania | | Micronesia, Federated States of | Oceania | | Guam | Oceania | | Kiribati | Oceania | | Marshall Islands | Oceania | | Northern Mariana Islands | Oceania | | New Caledonia | Oceania | | Norfolk Island | Oceania | | Niue | Oceania | | Nauru | Oceania | | New Zealand | Oceania | | Pitcairn | Oceania | | Palau | Oceania | | Papua New Guinea | Oceania | | French Polynesia | Oceania | | Solomon Islands | Oceania | | Tokelau | Oceania | | Tonga | Oceania | | Tuvalu | Oceania | | United States Minor Outlying Islands | Oceania | | Vanuatu | Oceania | | Wallis and Futuna | Oceania | | Samoa | Oceania | +--------------------------------------+-----------+ 28 rows in set (0.01 sec) mysql> SELECT left(name,15), continent FROM country WHERE continent = 'Oceania'; +-----------------+-----------+ | left(name,15) | continent | +-----------------+-----------+ | American Samoa | Oceania | | Australia | Oceania | | Cocos (Keeling) | Oceania | | Cook Islands | Oceania | | Christmas Islan | Oceania | | Fiji Islands | Oceania | | Micronesia, Fed | Oceania | | Guam | Oceania | | Kiribati | Oceania | | Marshall Island | Oceania | | Northern Marian | Oceania | | New Caledonia | Oceania | | Norfolk Island | Oceania | | Niue | Oceania | | Nauru | Oceania | | New Zealand | Oceania | | Pitcairn | Oceania | | Palau | Oceania | | Papua New Guine | Oceania | | French Polynesi | Oceania | | Solomon Islands | Oceania | | Tokelau | Oceania | | Tonga | Oceania | | Tuvalu | Oceania | | United States M | Oceania | | Vanuatu | Oceania | | Wallis and Futu | Oceania | | Samoa | Oceania | +-----------------+-----------+ 28 rows in set (0.00 sec) mysql> /* ALL и DISTINCT - модификаторы SELECT. указывают, следует ли возвращать повторяющиеся строки. */ mysql> /* ALL (по умолчанию) указывает, что должны быть возвращены все строки, включая дубликаты. */ mysql> /* DISTINCT определяет удаление повторяющихся строк из набора результатов. DISTINCTROW является синонимом DISTINCT */ mysql> SELECT DISTINCT continent FROM country; +---------------+ | continent | +---------------+ | North America | | Asia | | Africa | | Europe | | South America | | Oceania | | Antarctica | +---------------+ 7 rows in set (0.01 sec) mysql> /* вернуть количество строк с не пустым значением поля countryCode */ mysql> SELECT COUNT(countryCode) FROM city; +--------------------+ | COUNT(countryCode) | +--------------------+ | 4079 | +--------------------+ 1 row in set (0.01 sec) mysql> /* вернуть количество строк с УНИКАЛЬНЫМ И не пустым значением поля countryCode */ mysql> /* вернуть количество стран, города которых упоминаются в таблице city */ mysql> SELECT COUNT(DISTINCT countryCode) FROM city; +-----------------------------+ | COUNT(DISTINCT countryCode) | +-----------------------------+ | 232 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> mysql> /* Команда AS задает новое имя полям или таблицам при выборке из базы (то есть в самой базе изменения не происходят) */ mysql> SELECT DISTINCT continent AS new_name FROM country; +---------------+ | new_name | +---------------+ | North America | | Asia | | Africa | | Europe | | South America | | Oceania | | Antarctica | +---------------+ 7 rows in set (0.01 sec) mysql> /* Оператор AS можно опустить */ mysql> SELECT DISTINCT continent new_name FROM country; +---------------+ | new_name | +---------------+ | North America | | Asia | | Africa | | Europe | | South America | | Oceania | | Antarctica | +---------------+ 7 rows in set (0.00 sec) mysql> mysql> mysql> /* оператор LIMIT: ограничение количества возвращаемых строк. [LIMIT {[offset,] row_count | row_count OFFSET offset}] */ mysql> SELECT DISTINCT continent new_name FROM country LIMIT 1,2; +----------+ | new_name | +----------+ | Asia | | Africa | +----------+ 2 rows in set (0.00 sec) mysql> SELECT DISTINCT continent new_name FROM country LIMIT 2 OFFSET 1; +----------+ | new_name | +----------+ | Asia | | Africa | +----------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM city WHERE countrycode = 'RUS' LIMIT 5; +------+----------------+-------------+----------------+------------+ | ID | Name | CountryCode | District | Population | +------+----------------+-------------+----------------+------------+ | 3580 | Moscow | RUS | Moscow (City) | 8389200 | | 3581 | St Petersburg | RUS | Pietari | 4694000 | | 3582 | Novosibirsk | RUS | Novosibirsk | 1398800 | | 3583 | Nizni Novgorod | RUS | Nizni Novgorod | 1357000 | | 3584 | Jekaterinburg | RUS | Sverdlovsk | 1266300 | +------+----------------+-------------+----------------+------------+ 5 rows in set (0.00 sec) mysql> SELECT name, population, code FROM country WHERE population > 1e8 ORDER BY population DESC; +--------------------+------------+------+ | name | population | code | +--------------------+------------+------+ | China | 1277558000 | CHN | | India | 1013662000 | IND | | United States | 278357000 | USA | | Indonesia | 212107000 | IDN | | Brazil | 170115000 | BRA | | Pakistan | 156483000 | PAK | | Russian Federation | 146934000 | RUS | | Bangladesh | 129155000 | BGD | | Japan | 126714000 | JPN | | Nigeria | 111506000 | NGA | +--------------------+------------+------+ 10 rows in set (0.00 sec) mysql> SELECT name, population, code FROM country WHERE population > 1e8 AND population < 2.5e8 ORDER BY population DESC; +--------------------+------------+------+ | name | population | code | +--------------------+------------+------+ | Indonesia | 212107000 | IDN | | Brazil | 170115000 | BRA | | Pakistan | 156483000 | PAK | | Russian Federation | 146934000 | RUS | | Bangladesh | 129155000 | BGD | | Japan | 126714000 | JPN | | Nigeria | 111506000 | NGA | +--------------------+------------+------+ 7 rows in set (0.01 sec) mysql> SELECT name, continent, population, code FROM country WHERE population > 1e8 AND population < 2.5e8 AND continent = 'Africa' ORDER BY population DESC; +---------+-----------+------------+------+ | name | continent | population | code | +---------+-----------+------------+------+ | Nigeria | Africa | 111506000 | NGA | +---------+-----------+------------+------+ 1 row in set (0.01 sec) mysql> SELECT name, continent, population, code FROM country WHERE population > 126714000 AND population < 170115000 ORDER BY population DESC; +--------------------+-----------+------------+------+ | name | continent | population | code | +--------------------+-----------+------------+------+ | Pakistan | Asia | 156483000 | PAK | | Russian Federation | Europe | 146934000 | RUS | | Bangladesh | Asia | 129155000 | BGD | +--------------------+-----------+------------+------+ 3 rows in set (0.00 sec) mysql> SELECT name, continent, population, code FROM country WHERE population BETWEEN 126714000 AND 170115000 ORDER BY population DESC; +--------------------+---------------+------------+------+ | name | continent | population | code | +--------------------+---------------+------------+------+ | Brazil | South America | 170115000 | BRA | | Pakistan | Asia | 156483000 | PAK | | Russian Federation | Europe | 146934000 | RUS | | Bangladesh | Asia | 129155000 | BGD | | Japan | Asia | 126714000 | JPN | +--------------------+---------------+------------+------+ 5 rows in set (0.00 sec) mysql> SELECT name, continent, population, code FROM country WHERE population >= 126714000 AND population <= 170115000 ORDER BY population DESC; +--------------------+---------------+------------+------+ | name | continent | population | code | +--------------------+---------------+------------+------+ | Brazil | South America | 170115000 | BRA | | Pakistan | Asia | 156483000 | PAK | | Russian Federation | Europe | 146934000 | RUS | | Bangladesh | Asia | 129155000 | BGD | | Japan | Asia | 126714000 | JPN | +--------------------+---------------+------------+------+ 5 rows in set (0.01 sec) mysql> mysql> mysql> mysql> /* LIKE */ mysql> SELECT * FROM city WHERE LIKE '___u__'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '___u__'' at line 1 mysql> SELECT * FROM city WHERE name LIKE '___u__'; +------+---------+-------------+----------------------+------------+ | ID | Name | CountryCode | District | Population | +------+---------+-------------+----------------------+------------+ | 53 | Tafuna | ASM | Tutuila | 5200 | | 323 | Jequié | BRA | Bahia | 179128 | | 510 | Slough | GBR | England | 112000 | | 561 | Temuco | CHL | La Araucanía | 233041 | | 624 | Assuan | EGY | Assuan | 219017 | | 775 | Taguig | PHL | National Capital Reg | 467375 | | 792 | Butuan | PHL | Caraga | 267279 | | 796 | Baguio | PHL | CAR | 252386 | | 908 | Batumi | GEO | Adzaria [Atšara] | 137700 | | 909 | Sohumi | GEO | Abhasia [Aphazeti] | 111700 | | 1379 | Baquba | IRQ | Diyala | 114516 | | 1623 | Kurume | JPN | Fukuoka | 235611 | | 1628 | Atsugi | JPN | Kanagawa | 212407 | | 1645 | Suzuka | JPN | Mie | 184061 | | 1653 | Sakura | JPN | Chiba | 168072 | | 1753 | Kasuga | JPN | Fukuoka | 101344 | | 1773 | Kanuma | JPN | Tochigi | 93053 | | 1883 | Kisumu | KEN | Nyanza | 192733 | | 1884 | Nakuru | KEN | Rift Valley | 163927 | | 2190 | Heyuan | CHN | Guangdong | 120101 | | 2262 | Cúcuta | COL | Norte de Santander | 606932 | | 2534 | Toluca | MEX | México | 665617 | | 2685 | Pánuco | MEX | Veracruz | 90551 | | 2698 | Maputo | MOZ | Maputo | 1018938 | | 2705 | Mocuba | MOZ | Zambézia | 124700 | | 2755 | Kaduna | NGA | Kaduna | 342200 | | 2923 | Caguas | PRI | Caguas | 140502 | | 3106 | Erfurt | DEU | Thüringen | 201267 | | 3555 | Baruta | VEN | Miranda | 207290 | | 3576 | Araure | VEN | Portuguesa | 94269 | | 3634 | Kaluga | RUS | Kaluga | 339300 | +------+---------+-------------+----------------------+------------+ 31 rows in set (0.00 sec) mysql> SELECT * FROM city WHERE name LIKE 'Mex%'; +------+----------+-------------+-----------------+------------+ | ID | Name | CountryCode | District | Population | +------+----------+-------------+-----------------+------------+ | 864 | Mexico | PHL | Central Luzon | 109481 | | 2526 | Mexicali | MEX | Baja California | 764902 | +------+----------+-------------+-----------------+------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM city WHERE name LIKE '%exi%'; +------+-------------------+-------------+------------------+------------+ | ID | Name | CountryCode | District | Population | +------+-------------------+-------------+------------------+------------+ | 864 | Mexico | PHL | Central Luzon | 109481 | | 2515 | Ciudad de México | MEX | Distrito Federal | 8591309 | | 2526 | Mexicali | MEX | Baja California | 764902 | | 3856 | Lexington-Fayette | USA | Kentucky | 260512 | +------+-------------------+-------------+------------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM city WHERE name LIKE 'A%' AND CountryCode = 'RUS' LIMIT 5; +------+-----------+-------------+-----------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+-----------+------------+ | 3614 | Astrahan | RUS | Astrahan | 486100 | | 3628 | Arkangeli | RUS | Arkangeli | 361800 | | 3654 | Angarsk | RUS | Irkutsk | 264700 | | 3683 | Abakan | RUS | Hakassia | 169200 | | 3686 | Armavir | RUS | Krasnodar | 164900 | +------+-----------+-------------+-----------+------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM city WHERE name LIKE 'J%' AND CountryCode = 'RUS' LIMIT 5; +------+-----------------+-------------+----------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+----------------+------------+ | 3584 | Jekaterinburg | RUS | Sverdlovsk | 1266300 | | 3600 | Jaroslavl | RUS | Jaroslavl | 616700 | | 3655 | Joškar-Ola | RUS | Marinmaa | 249200 | | 3672 | Jakutsk | RUS | Saha (Jakutia) | 195400 | | 3681 | Juzno-Sahalinsk | RUS | Sahalin | 179200 | +------+-----------------+-------------+----------------+------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM city WHERE name IN ('Mexico', 'Arkangeli', 'Armavir', 'Astrahan'); +------+-----------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+---------------+------------+ | 864 | Mexico | PHL | Central Luzon | 109481 | | 3614 | Astrahan | RUS | Astrahan | 486100 | | 3628 | Arkangeli | RUS | Arkangeli | 361800 | | 3686 | Armavir | RUS | Krasnodar | 164900 | +------+-----------+-------------+---------------+------------+ 4 rows in set (0.00 sec) mysql> /* предикат ORDER */ mysql> SELECT * FROM city WHERE name IN ('Mexico', 'Arkangeli', 'Armavir', 'Astrahan') ORDER BY population DESC; +------+-----------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+---------------+------------+ | 3614 | Astrahan | RUS | Astrahan | 486100 | | 3628 | Arkangeli | RUS | Arkangeli | 361800 | | 3686 | Armavir | RUS | Krasnodar | 164900 | | 864 | Mexico | PHL | Central Luzon | 109481 | +------+-----------+-------------+---------------+------------+ 4 rows in set (0.01 sec) mysql> SELECT * FROM city WHERE name IN ('Mexico', 'Arkangeli', 'Armavir', 'Astrahan') ORDER BY population; +------+-----------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+---------------+------------+ | 864 | Mexico | PHL | Central Luzon | 109481 | | 3686 | Armavir | RUS | Krasnodar | 164900 | | 3628 | Arkangeli | RUS | Arkangeli | 361800 | | 3614 | Astrahan | RUS | Astrahan | 486100 | +------+-----------+-------------+---------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM city WHERE name IN ('Mexico', 'Arkangeli', 'Armavir', 'Astrahan') ORDER BY name; +------+-----------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+---------------+------------+ | 3628 | Arkangeli | RUS | Arkangeli | 361800 | | 3686 | Armavir | RUS | Krasnodar | 164900 | | 3614 | Astrahan | RUS | Astrahan | 486100 | | 864 | Mexico | PHL | Central Luzon | 109481 | +------+-----------+-------------+---------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM city WHERE name IN ('Mexico', 'Arkangeli', 'Armavir', 'Astrahan') ORDER BY name DESC; +------+-----------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+---------------+------------+ | 864 | Mexico | PHL | Central Luzon | 109481 | | 3614 | Astrahan | RUS | Astrahan | 486100 | | 3686 | Armavir | RUS | Krasnodar | 164900 | | 3628 | Arkangeli | RUS | Arkangeli | 361800 | +------+-----------+-------------+---------------+------------+ 4 rows in set (0.00 sec) mysql> /* оператор GROUP BY можно использовать в операторе SELECT для сбора данных по нескольким записям и группировки результатов по одному или нескольким столбцам */ mysql> SELECT continent FROM country GROUP BY continent; +---------------+ | continent | +---------------+ | Asia | | Europe | | North America | | Africa | | Oceania | | Antarctica | | South America | +---------------+ 7 rows in set (0.00 sec) mysql> /* группировка GROUP BY удобна при использовании агрегирующих функций */ mysql> /* подсчитаем население по континентам */ mysql> SELECT continent, SUM(population) FROM country GROUP BY continent; +---------------+-----------------+ | continent | SUM(population) | +---------------+-----------------+ | Asia | 3705025700 | | Europe | 730074600 | | North America | 482993000 | | Africa | 784475000 | | Oceania | 30401150 | | Antarctica | 0 | | South America | 345780000 | +---------------+-----------------+ 7 rows in set (0.01 sec) mysql> /* чтобы не повторяться, в GROUP BY можно указать просто номер столбца, указанного в SELECT */ mysql> SELECT continent, SUM(population) FROM country GROUP BY 1; +---------------+-----------------+ | continent | SUM(population) | +---------------+-----------------+ | Asia | 3705025700 | | Europe | 730074600 | | North America | 482993000 | | Africa | 784475000 | | Oceania | 30401150 | | Antarctica | 0 | | South America | 345780000 | +---------------+-----------------+ 7 rows in set (0.01 sec) mysql> /* совместим с сортировкой */ mysql> SELECT continent, SUM(population) FROM country GROUP BY 1 ORDER BY SUM(population) DESC; +---------------+-----------------+ | continent | SUM(population) | +---------------+-----------------+ | Asia | 3705025700 | | Africa | 784475000 | | Europe | 730074600 | | North America | 482993000 | | South America | 345780000 | | Oceania | 30401150 | | Antarctica | 0 | +---------------+-----------------+ 7 rows in set (0.01 sec) mysql> SELECT continent, region, SUM(population) FROM country GROUP BY 1,2 ORDER BY 1,2 DESC; +---------------+---------------------------+-----------------+ | continent | region | SUM(population) | +---------------+---------------------------+-----------------+ | Asia | Southern and Central Asia | 1490776000 | | Asia | Southeast Asia | 518541000 | | Asia | Middle East | 188380700 | | Asia | Eastern Asia | 1507328000 | | Europe | Western Europe | 183247600 | | Europe | Southern Europe | 144674200 | | Europe | Nordic Countries | 24166400 | | Europe | Eastern Europe | 307026000 | | Europe | British Islands | 63398500 | | Europe | Baltic Countries | 7561900 | | North America | North America | 309632000 | | North America | Central America | 135221000 | | North America | Caribbean | 38140000 | | Africa | Western Africa | 221672000 | | Africa | Southern Africa | 46886000 | | Africa | Northern Africa | 173266000 | | Africa | Eastern Africa | 246999000 | | Africa | Central Africa | 95652000 | | Oceania | Polynesia | 633050 | | Oceania | Micronesia/Caribbean | 0 | | Oceania | Micronesia | 543000 | | Oceania | Melanesia | 6472000 | | Oceania | Australia and New Zealand | 22753100 | | Antarctica | Antarctica | 0 | | South America | South America | 345780000 | +---------------+---------------------------+-----------------+ 25 rows in set (0.01 sec) mysql> SELECT continent, region, SUM(population) FROM country GROUP BY 1,2 ORDER BY 1,3 DESC; +---------------+---------------------------+-----------------+ | continent | region | SUM(population) | +---------------+---------------------------+-----------------+ | Asia | Eastern Asia | 1507328000 | | Asia | Southern and Central Asia | 1490776000 | | Asia | Southeast Asia | 518541000 | | Asia | Middle East | 188380700 | | Europe | Eastern Europe | 307026000 | | Europe | Western Europe | 183247600 | | Europe | Southern Europe | 144674200 | | Europe | British Islands | 63398500 | | Europe | Nordic Countries | 24166400 | | Europe | Baltic Countries | 7561900 | | North America | North America | 309632000 | | North America | Central America | 135221000 | | North America | Caribbean | 38140000 | | Africa | Eastern Africa | 246999000 | | Africa | Western Africa | 221672000 | | Africa | Northern Africa | 173266000 | | Africa | Central Africa | 95652000 | | Africa | Southern Africa | 46886000 | | Oceania | Australia and New Zealand | 22753100 | | Oceania | Melanesia | 6472000 | | Oceania | Polynesia | 633050 | | Oceania | Micronesia | 543000 | | Oceania | Micronesia/Caribbean | 0 | | Antarctica | Antarctica | 0 | | South America | South America | 345780000 | +---------------+---------------------------+-----------------+ 25 rows in set (0.00 sec) mysql> /* логирование вывода в файл */ mysql> \T /home/vagrant/projects/mysql_log Logging to file '/home/vagrant/projects/mysql_log' mysql> /* предикат HAVING */ mysql> /* получим данные по континентам, население которых больше населения северной америки */ mysql> SELECT continent, SUM(population) FROM country GROUP BY 1 HAVING SUM(population) > 482993000; +-----------+-----------------+ | continent | SUM(population) | +-----------+-----------------+ | Asia | 3705025700 | | Europe | 730074600 | | Africa | 784475000 | +-----------+-----------------+ 3 rows in set (0.00 sec)
03Агрегатные функции
Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение. Агрегатные функции, за исключением COUNT, не учитывают значения NULL. Агрегатные функции часто используются в выражении GROUP BY инструкции SELECT.
Все агрегатные функции являются детерминированными. Другими словами, агрегатные функции возвращают одну и ту же величину при каждом их вызове на одном и том же наборе входных значений.
- AVG - возвращает усреднённое значение всех выбранных значений данного поля (не NULL)
- COUNT - возвращает количество строк, имеющих отличные от NULL значения поля (не NULL)
- GROUP_CONCAT - возвращает объединенную строку (конкатенация выбранных значений)
- MAX - возвращает наибольшее из всех выбранных значений данного поля
- MIN - возвращает наименьшее из всех выбранных значений данного поля
- SUM - возвращает арифметическую сумму всех выбранных значений данного поля
bash:mysql> /* агрегатная функция AVG() */ mysql> /* возвращает усреднённое значение всех выбранных значений данного поля (не NULL) */ mysql> SELECT AVG(lifeexpectancy) FROM country; +---------------------+ | AVG(lifeexpectancy) | +---------------------+ | 66.48604 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT AVG(population) FROM country; +-----------------+ | AVG(population) | +-----------------+ | 25434098.1172 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT AVG(code) FROM country; +-----------+ | AVG(code) | +-----------+ | 0 | +-----------+ 1 row in set, 239 warnings (0.00 sec) mysql> mysql> mysql> /* агрегатная функция COUNT() */ mysql> /* возвращает количество строк, имеющих отличные от NULL значения поля (не NULL) */ mysql> SELECT COUNT(lifeexpectancy) FROM country; +-----------------------+ | COUNT(lifeexpectancy) | +-----------------------+ | 222 | +-----------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(code) FROM country; +-------------+ | COUNT(code) | +-------------+ | 239 | +-------------+ 1 row in set (0.00 sec) mysql> SELECT name, population, lifeexpectancy FROM country WHERE lifeexpectancy IS NULL; +----------------------------------------------+------------+----------------+ | name | population | lifeexpectancy | +----------------------------------------------+------------+----------------+ | Antarctica | 0 | NULL | | French Southern territories | 0 | NULL | | Bouvet Island | 0 | NULL | | Cocos (Keeling) Islands | 600 | NULL | | Christmas Island | 2500 | NULL | | Falkland Islands | 2000 | NULL | | Heard Island and McDonald Islands | 0 | NULL | | British Indian Ocean Territory | 0 | NULL | | Norfolk Island | 2000 | NULL | | Niue | 2000 | NULL | | Pitcairn | 50 | NULL | | South Georgia and the South Sandwich Islands | 0 | NULL | | Svalbard and Jan Mayen | 3200 | NULL | | Tokelau | 2000 | NULL | | United States Minor Outlying Islands | 0 | NULL | | Holy See (Vatican City State) | 1000 | NULL | | Wallis and Futuna | 15000 | NULL | +----------------------------------------------+------------+----------------+ 17 rows in set (0.00 sec) mysql> /* гарантированно посчитать все строки */ mysql> SELECT COUNT(*) FROM country; +----------+ | COUNT(*) | +----------+ | 239 | +----------+ 1 row in set (0.01 sec) mysql> /* количество строк, содержащих значение поля continent */ mysql> SELECT COUNT(continent) FROM country; +------------------+ | COUNT(continent) | +------------------+ | 239 | +------------------+ 1 row in set (0.00 sec) mysql> /* количество уникальных значений поля continent */ mysql> SELECT COUNT(DISTINCT continent) FROM country; +---------------------------+ | COUNT(DISTINCT continent) | +---------------------------+ | 7 | +---------------------------+ 1 row in set (0.00 sec) mysql> /* возвращает объединенную строку (конкатенация выбранных значений) */ mysql> SELECT GROUP_CONCAT(name) FROM country WHERE population > 1e8; +-------------------------------------------------------------------------------------------------+ | GROUP_CONCAT(name) | +-------------------------------------------------------------------------------------------------+ | Bangladesh,Brazil,China,Indonesia,India,Japan,Nigeria,Pakistan,Russian Federation,United States | +-------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> /* возвращает наибольшее из всех выбранных значений данного поля */ mysql> SELECT MAX(surfacearea), name, code FROM country GROUP BY name, code LIMIT 5; +------------------+----------------+------+ | MAX(surfacearea) | name | code | +------------------+----------------+------+ | 652090.00 | Afghanistan | AFG | | 28748.00 | Albania | ALB | | 2381741.00 | Algeria | DZA | | 199.00 | American Samoa | ASM | | 468.00 | Andorra | AND | +------------------+----------------+------+ 5 rows in set (0.01 sec) mysql> SELECT MAX(surfacearea), name, code FROM country GROUP BY surfacearea, name, code LIMIT 5; +------------------+-------------------------------+------+ | MAX(surfacearea) | name | code | +------------------+-------------------------------+------+ | 0.40 | Holy See (Vatican City State) | VAT | | 1.50 | Monaco | MCO | | 6.00 | Gibraltar | GIB | | 12.00 | Tokelau | TKL | | 14.00 | Cocos (Keeling) Islands | CCK | +------------------+-------------------------------+------+ 5 rows in set (0.01 sec) mysql> /* получение страны с максимальной площадью без использования агрегатной функции MAX() */ mysql> SELECT name, surfacearea FROM country ORDER BY 2 DESC LIMIT 1; +--------------------+-------------+ | name | surfacearea | +--------------------+-------------+ | Russian Federation | 17075400.00 | +--------------------+-------------+ 1 row in set (0.00 sec) mysql> /* получение страны с максимальной площадью с использованием агрегатной функции MAX() */ mysql> SELECT MAX(surfacearea), name, code FROM country GROUP BY surfacearea, name, code ORDER BY surfacearea DESC LIMIT 1; +------------------+--------------------+------+ | MAX(surfacearea) | name | code | +------------------+--------------------+------+ | 17075400.00 | Russian Federation | RUS | +------------------+--------------------+------+ 1 row in set (0.00 sec) mysql> mysql> mysql> /* агрегатная функция SUM() */ mysql> /* возвращает арифметическую сумму всех выбранных значений данного поля */ mysql> SELECT continent, SUM(surfacearea) FROM country GROUP BY continent ORDER BY 2 DESC; +---------------+------------------+ | continent | SUM(surfacearea) | +---------------+------------------+ | Asia | 31881005.00 | | Africa | 30250377.00 | | North America | 24214470.00 | | Europe | 23049133.90 | | South America | 17864926.00 | | Antarctica | 13132101.00 | | Oceania | 8564294.00 | +---------------+------------------+ 7 rows in set (0.00 sec) mysql> /* вернуть страны с максимальной площадью на своём континенте */ mysql> SELECT continent, MAX(surfacearea) FROM country GROUP BY continent ORDER BY 2 DESC; +---------------+------------------+ | continent | MAX(surfacearea) | +---------------+------------------+ | Europe | 17075400.00 | | Antarctica | 13120000.00 | | North America | 9970610.00 | | Asia | 9572900.00 | | South America | 8547403.00 | | Oceania | 7741220.00 | | Africa | 2505813.00 | +---------------+------------------+ 7 rows in set (0.00 sec) mysql> /* проверка */ mysql> SELECT name, surfacearea FROM country ORDER BY 2 DESC LIMIT 1; +--------------------+-------------+ | name | surfacearea | +--------------------+-------------+ | Russian Federation | 17075400.00 | +--------------------+-------------+ 1 row in set (0.00 sec) mysql> /* получение максимальной численности в какой либо одной стране на каждом континенте */ mysql> SELECT continent, MAX(population) FROM country GROUP BY continent; +---------------+-----------------+ | continent | MAX(population) | +---------------+-----------------+ | Asia | 1277558000 | | Europe | 146934000 | | North America | 278357000 | | Africa | 111506000 | | Oceania | 18886000 | | Antarctica | 0 | | South America | 170115000 | +---------------+-----------------+ 7 rows in set (0.00 sec) mysql> /* попытка получения страны с максимальной численностью на своём континенте */ mysql> SELECT name, code, population, continent -> FROM country -> WHERE population IN (SELECT MAX(population) FROM country GROUP BY continent); +----------------------------------------------+------+------------+---------------+ | name | code | population | continent | +----------------------------------------------+------+------------+---------------+ | Antarctica | ATA | 0 | Antarctica | | French Southern territories | ATF | 0 | Antarctica | | Australia | AUS | 18886000 | Oceania | | Brazil | BRA | 170115000 | South America | | Bouvet Island | BVT | 0 | Antarctica | | China | CHN | 1277558000 | Asia | | Heard Island and McDonald Islands | HMD | 0 | Antarctica | | British Indian Ocean Territory | IOT | 0 | Africa | | Nigeria | NGA | 111506000 | Africa | | Russian Federation | RUS | 146934000 | Europe | | South Georgia and the South Sandwich Islands | SGS | 0 | Antarctica | | United States Minor Outlying Islands | UMI | 0 | Oceania | | United States | USA | 278357000 | North America | +----------------------------------------------+------+------------+---------------+ 13 rows in set (0.00 sec)
04JOIN Clause
bash:mysql> /* upd: Пример неудачен, разницы между типами присоединений не показвает */ mysql> /* LEFT JOIN */ mysql> SELECT * -> FROM country -> LEFT JOIN city -> ON city.id = country.capital -> WHERE country.code = 'RUS'; +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | ID | Name | CountryCode | District | Population | +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ | RUS | Russian Federation | Europe | Eastern Europe | 17075400.00 | 1991 | 146934000 | 67.2 | 276608.00 | 442989.00 | Rossija | Federal Republic | Vladimir Putin | 3580 | RU | 3580 | Moscow | RUS | Moscow (City) | 8389200 | +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ 1 row in set (0.00 sec) mysql> /* RIGHT JOIN */ mysql> SELECT * -> FROM country -> RIGHT JOIN city -> ON city.id = country.capital -> WHERE country.code = 'RUS'; +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | ID | Name | CountryCode | District | Population | +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ | RUS | Russian Federation | Europe | Eastern Europe | 17075400.00 | 1991 | 146934000 | 67.2 | 276608.00 | 442989.00 | Rossija | Federal Republic | Vladimir Putin | 3580 | RU | 3580 | Moscow | RUS | Moscow (City) | 8389200 | +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ 1 row in set (0.00 sec) mysql> /* INNER JOIN */ mysql> SELECT * -> FROM country -> INNER JOIN city -> ON city.id = country.capital -> WHERE country.code = 'RUS'; +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | ID | Name | CountryCode | District | Population | +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ | RUS | Russian Federation | Europe | Eastern Europe | 17075400.00 | 1991 | 146934000 | 67.2 | 276608.00 | 442989.00 | Rossija | Federal Republic | Vladimir Putin | 3580 | RU | 3580 | Moscow | RUS | Moscow (City) | 8389200 | +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ 1 row in set (0.00 sec) mysql> /* CROSS JOIN */ mysql> SELECT * -> FROM country -> CROSS JOIN city -> ON city.id = country.capital -> WHERE country.code = 'RUS'; +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | ID | Name | CountryCode | District | Population | +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ | RUS | Russian Federation | Europe | Eastern Europe | 17075400.00 | 1991 | 146934000 | 67.2 | 276608.00 | 442989.00 | Rossija | Federal Republic | Vladimir Putin | 3580 | RU | 3580 | Moscow | RUS | Moscow (City) | 8389200 | +------+--------------------+-----------+----------------+-------------+-----------+------------+----------------+-----------+-----------+-----------+------------------+----------------+---------+-------+------+--------+-------------+---------------+------------+ 1 row in set (0.01 sec)
Капустин Яков (2019.11.17 02:22)