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;