Исследуем тестовую базу с mysql в терминале.

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.

Все агрегатные функции являются детерминированными. Другими словами, агрегатные функции возвращают одну и ту же величину при каждом их вызове на одном и том же наборе входных значений.

1. AVG - возвращает усреднённое значение всех выбранных значений данного поля (не NULL)
2. COUNT - возвращает количество строк, имеющих отличные от NULL значения поля (не NULL)
3. GROUP_CONCAT - возвращает объединенную строку (конкатенация выбранных значений)
4. MAX - возвращает наибольшее из всех выбранных значений данного поля
5. MIN - возвращает наименьшее из всех выбранных значений данного поля
6. 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)
	
		
	

04

JOIN 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)