select高级用法之各种连接

#多表联查，联表查询

1.传统连接

1）集合

#集合
[qiudao,zengdao,qiandao]
[80,90,100]

#数据库
id:[1,2,3]
name:[qiudao,zengdao,qiandao]

id:[1,2,3]
mark:[80,90,100]

2）建表

mysql> create table students(id int,name varchar(10));
Query OK, 0 rows affected (0.08 sec)

mysql> create table score(id int,mark int);
Query OK, 0 rows affected (0.05 sec)

3）插入数据

mysql> insert into students values(1,'qiudao'),(2,'qiandao'),(3,'zengdao');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score values(1,80),(2,90),(3,100);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

4）数据查询

#查看两个表的数据
mysql> select * from students;
+------+---------+
| id   | name    |
+------+---------+
|    1 | qiudao  |
|    2 | qiandao |
|    3 | zengdao |
+------+---------+
3 rows in set (0.00 sec)

mysql> select * from score;
+------+------+
| id   | mark |
+------+------+
|    1 |   80 |
|    2 |   90 |
|    3 |  100 |
+------+------+
3 rows in set (0.00 sec)

#查看邱导的分数
mysql> select students.name,score.mark from students,score where students.id=1 and score.id=1;

mysql> select students.name,score.mark from students,score where students.id=score.id and name='qiudao';
+--------+------+
| name   | mark |
+--------+------+
| qiudao |   80 |
+--------+------+
1 row in set (0.01 sec)

#查询所有学生成绩
mysql> select students.name,score.mark from students,score where students.id=score.id

5）练习题一：

连表查询：世界上小于100人的城市在哪个国家？请列出城市名字，国家名字与人口数量

#1.确认我要查哪些内容
国家名字  城市名字  城市人口数量   小于100人

#2.确认在哪个表
country.name   city.name   city.population   

#3.找出两个表相关联的字段
city.countrycode   country.code

#4.编写语句
mysql> select country.name,city.name,city.population from country,city where city.countrycode=country.code and city.population < 100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+
1 row in set (0.01 sec)

6）练习题二：

连表查询：世界上小于100人的城市在哪个国家，是用什么语言？请列出城市名字，国家名字与人口数量和国家语言

#1.确认我要查哪些内容
国家名字  城市名字  城市人口数量   国家使用的语言   小于100人

#2.确认在哪个表
country.name   city.name   city.population   countrylanguage.language

#3.找出三个表相关联的字段
country.code   city.countrycode   countrylanguage.countrycode

#4.写sql语句
mysql> select country.name,city.name,city.population,countrylanguage.language from country,city,countrylanguage where country.code=city.countrycode and city.countrycode=countrylanguage.countrycode and city.population < 100;
+----------+-----------+------------+-------------+
| name     | name      | population | language    |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown |         42 | Pitcairnese |
+----------+-----------+------------+-------------+
1 row in set (0.04 sec)

2.自连接

#自己查找相同字段，使用自连接，两个关联的表必须有相同字段和相同数据
SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM  city NATURAL JOIN countrylanguage 
WHERE population > 1000000
ORDER BY population;

#两个表中没有相同字段不行，字段相同值不同不行
SELECT country.name,city.name,city.population FROM city NATURAL JOIN country WHERE population < 100;

#注意：
1.自连接必须有相同字段和相同值
2.两个表中的数据必须完全相同

3.外连接(有问题)

1）左外连接

select city.name,city.countrycode,country.name,city.population
from city left join country 
on city.countrycode=country.code 
and city.population < 100;

2）右外连接

select city.name,city.countrycode,country.name,city.population
from city right join country 
on city.countrycode=country.code
and city.population < 100;

4.传统连接

世界上小于100人的城市在哪个国家？是用什么语言？

#1.分析要哪些内容？
城市的人口数量    城市名字    国家名字    国家语言

#2.分析数据所在库
city.population   city.name   country.name    countrylanguage.language

#3.找出三个表相关联内容
city.countrycode    country.code    countrylanguage.countrycode

#3.编写语句
select country.name,city.name,city.population,countrylanguage.language from city,country,countrylanguage where city.countrycode=country.code and country.code=countrylanguage.countrycode and city.population < 100;

5.自连接

#自己寻找两个表相关联的字段和数据
1.两个表字段必须完全相同
2.两个表字段下的数据必须完全相同

SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city NATURAL JOIN countrylanguage 
WHERE population < 100
ORDER BY population;

6.内连接

1）语法格式

select * from 表1 join 表2 on 相关联的条件 where 条件;

#注意：命中率（驱动的概念）
	表1 小表
	表2 大表
	
select * from 表1 inner join 表2 on 相关联的条件 where 条件;

2）例子1：两表联查

#小于100人的城市在哪个国家，国家代码是什么？
select city.name,city.population,city.countrycode,country.name 
from city join country on city.countrycode=country.code 
where city.population < 100;

3）例子2：三表联查

#世界上小于100人的城市在哪个国家？是用什么语言？
select country.name,city.name,city.population,countrylanguage.language
from city join country on city.countrycode=country.code 
join countrylanguage on country.code=countrylanguage.countrycode
where city.population < 100;