MYSQL操作4-----复杂查询¶
1.连表查询¶
Ⅰ. 连表查询即查询的结果来自多张表
Ⅱ. 连表查询的分类
内连接：又称为等值连接
外连接
左外连接：以左表为依据让右表进行连接。左表的数据会全部显示，右表与左表相同的数据显示，不同的数据使用null填充。
右外连接：以右表为依据让左表进行连接。右表的数据会全部显示，左表与右表相同的数据显示，不同的数据使用null填充。
完全外连接：左右外连接的数据合并到一起。
Ⅲ. 语句
内连接：
select * from 表1 inner join 表2 on 表1.列表=表2.列表
左连接：
select * from 表1 left join 表2 on 表1.列表=表2.列表
右连接：
select * from 表1 right join 表2 on 表1.列表=表2.列表
案例：
a. 建表
mysql> use school;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| school |
+------------+
1 row in set (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> create table A(姓名 char(10),毕业院校 char(20));
Query OK, 0 rows affected (0.01 sec)
mysql> create table B(姓名 char(10),职业 char(20));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into A values('杨过','哈工大'),('郭靖','北大'),('黄蓉','武大'),('风清扬','西交大');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into B (姓名,职业) values('杨过','作家'),('杨逍','演员'),('孙杨','运动员'),('黄蓉','考研');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
查询A表中毕业院校及职业
b. 分析
查询毕业院校和职业。
毕业院校来自于A表，职业来自于B表，所以需要连表查询。
语句写法：表名卸载join两边，on后跟连接依据（表中相同值的列表）
操作：
mysql> select A.姓名,毕业院校,职业 from A inner join B on A.姓名=B.姓名; #或
mysql> select * from A inner join B on A.姓名=B.姓名;
+--------+--------------+--------+
| 姓名 | 毕业院校 | 职业 |
+--------+--------------+--------+
| 杨过 | 哈工大 | 作家 |
| 黄蓉 | 武大 | 考研 |
+--------+--------------+--------+
2 rows in set (0.00 sec)
mysql> select * from A left join B on A.姓名=B.姓名;
+-----------+--------------+--------+--------+
| 姓名 | 毕业院校 | 姓名 | 职业 |
+-----------+--------------+--------+--------+
| 杨过 | 哈工大 | 杨过 | 作家 |
| 郭靖 | 北大 | NULL | NULL |
| 黄蓉 | 武大 | 黄蓉 | 考研 |
| 风清扬 | 西交大 | NULL | NULL |
+-----------+--------------+--------+--------+
4 rows in set (0.00 sec)
mysql> select * from A right join B on A.姓名=B.姓名;
+--------+--------------+--------+-----------+
| 姓名 | 毕业院校 | 姓名 | 职业 |
+--------+--------------+--------+-----------+
| 杨过 | 哈工大 | 杨过 | 作家 |
| NULL | NULL | 杨逍 | 演员 |
| NULL | NULL | 孙杨 | 运动员 |
| 黄蓉 | 武大 | 黄蓉 | 考研 |
+--------+--------------+--------+-----------+
4 rows in set (0.00 sec)
Ⅳ. 三张表相连
表1 ---------------- 表2 ---------------- 表3
列名X ----- 列名X 列名Y ----- 列名Y
SELECT 字段列表 FROM
表1 INNER JOIN 表2 ON 表1.列名X=表2.列名X
INNER JOIN 表3 ON 表2.列名Y=表3.列名Y
准备案例：
导入word.sql。world库中有三张表city、country、countrylanguage
city表的结结构：
name：城市名称
countrycode：国家代码
District：省、州
population：人口数量
country表的结构：
code：国家代码
name：国家名
Continet：大洲名
surfacearea：国土面积
Ⅴ. 分组查询
语法：
select * from 表名 [where 条件] group by 分组依据 [having 条件]
案例：
a. 统计city表中中国每个省的城市数量
mysql> select district,count(*) from city where countrycode='CHN' group by District;
+----------------+----------+
| district | count(*) |
+----------------+----------+
| Shanghai | 1 |
| Peking | 2 |
| Chongqing | 1 |
| Tianjin | 1 |
| Hubei | 22 |
| Heilongjiang | 21 |
| Liaoning | 21 |
| Guangdong | 20 |
| Sichuan | 21 |
| Jiangsu | 25 |
| Jilin | 20 |
| Shaanxi | 8 |
| Shandong | 32 |
| Zhejiang | 16 |
| Henan | 18 |
| Hebei | 12 |
| Shanxi | 9 |
| Yunnan | 5 |
| Hunan | 18 |
| Jiangxi | 11 |
| Fujian | 12 |
| Gansu | 7 |
| Guizhou | 6 |
| Anhui | 16 |
| Xinxiang | 10 |
| Guangxi | 9 |
| Inner Mongolia | 13 |
| Qinghai | 1 |
| Ningxia | 2 |
| Hainan | 2 |
| Tibet | 1 |
+----------------+----------+
31 rows in set (0.01 sec)
b. 统计city表中中国每个省的人口数量
mysql> SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
+----------------+-----------------+
| district | SUM(population) |
+----------------+-----------------+
| Shanghai | 9696300 |
| Peking | 7569168 |
| Chongqing | 6351600 |
| Tianjin | 5286800 |
| Hubei | 8547585 |
| Heilongjiang | 11628057 |
| Liaoning | 15079174 |
| Guangdong | 9510263 |
| Sichuan | 7456867 |
| Jiangsu | 9719860 |
| Jilin | 7826824 |
| Shaanxi | 4297493 |
| Shandong | 12114416 |
| Zhejiang | 5807384 |
| Henan | 6899010 |
| Hebei | 6458553 |
| Shanxi | 4169899 |
| Yunnan | 2451016 |
| Hunan | 5439275 |
| Jiangxi | 3831558 |
| Fujian | 3575650 |
| Gansu | 2462631 |
| Guizhou | 2512087 |
| Anhui | 5141136 |
| Xinxiang | 2894705 |
| Guangxi | 2925142 |
| Inner Mongolia | 4121479 |
| Qinghai | 700200 |
| Ningxia | 802362 |
| Hainan | 557120 |
| Tibet | 120000 |
+----------------+-----------------+
31 rows in set (0.01 sec)
c. 统计city表中中国人口超过1000万的省
mysql> SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>1000000;
+----------------+-----------------+
| district | SUM(population) |
+----------------+-----------------+
| Shanghai | 9696300 |
| Peking | 7569168 |
| Chongqing | 6351600 |
| Tianjin | 5286800 |
| Hubei | 8547585 |
| Heilongjiang | 11628057 |
| Liaoning | 15079174 |
| Guangdong | 9510263 |
| Sichuan | 7456867 |
| Jiangsu | 9719860 |
| Jilin | 7826824 |
| Shaanxi | 4297493 |
| Shandong | 12114416 |
| Zhejiang | 5807384 |
| Henan | 6899010 |
| Hebei | 6458553 |
| Shanxi | 4169899 |
| Yunnan | 2451016 |
| Hunan | 5439275 |
| Jiangxi | 3831558 |
| Fujian | 3575650 |
| Gansu | 2462631 |
| Guizhou | 2512087 |
| Anhui | 5141136 |
| Xinxiang | 2894705 |
| Guangxi | 2925142 |
| Inner Mongolia | 4121479 |
+----------------+-----------------+
27 rows in set (0.00 sec)
d. 统计亚洲各个国家的人口数量
mysql> SELECT country.name,SUM(city.population) FROM city INNER JOIN country ON city.countrycode=country.code WHERE country.continent='asia' GROUP BY country.name;
+----------------------+----------------------+
| name | SUM(city.population) |
+----------------------+----------------------+
| Afghanistan | 2332100 |
| United Arab Emirates | 1728336 |
| Armenia | 1633100 |
| Azerbaijan | 2464000 |
| Bangladesh | 8569906 |
| Bahrain | 148000 |
| Brunei | 21484 |
| Bhutan | 22000 |
| China | 175953614 |
| Cyprus | 349400 |
| Georgia | 1880900 |
| Hong Kong | 3300633 |
| Indonesia | 37485695 |
| India | 123298526 |
| Iran | 26032990 |
| Iraq | 8926041 |
| Israel | 2813000 |
| Jordan | 1847677 |
| Japan | 77965107 |
| Kazakstan | 5484200 |
| Kyrgyzstan | 812100 |
| Cambodia | 805055 |
| South Korea | 38999893 |
| Kuwait | 261252 |
| Laos | 628452 |
| Lebanon | 1340000 |
| Sri Lanka | 1545000 |
| Macao | 437500 |
| Maldives | 71000 |
| Myanmar | 6203000 |
| Mongolia | 773700 |
| Malaysia | 4605141 |
| Nepal | 1132403 |
| Oman | 537096 |
| Pakistan | 31546745 |
| Philippines | 30934791 |
| North Korea | 6476751 |
| Palestine | 902360 |
| Qatar | 355000 |
| Saudi Arabia | 10636700 |
| Singapore | 4017733 |
| Syria | 4477784 |
| Thailand | 7953161 |
| Tajikistan | 685500 |
| Turkmenistan | 972600 |
| East Timor | 47900 |
| Turkey | 28327028 |
| Taiwan | 13569336 |
| Uzbekistan | 5183900 |
| Vietnam | 9364813 |
| Yemen | 1743700 |
+----------------------+----------------------+
51 rows in set (0.00 sec)
e. 统计亚洲人口数量前五的国家和省及人口数量