mysql多重select_MYSQL之select的高级用法

作用：

# 多表联查，联表查询

1.传统连接

1.集合

#集合

[xiaoqiu,xiaowang,qiandao]

[80,90,100]

#数据库

id:[1,2,3]

name:[xiaoqiu,xiaowang,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,'xiaoqiu'),(2,'qianqian'),(3,'xiaowang');

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 | xiaoqiu |

| 2 | qiandao |

| 3 | xiaowang|

+------+---------+

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)

# 查看xiaoqiu的分数

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='xiaoqiu';

+--------+------+

| name | mark |

+--------+------+

| xiaoqiu | 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 * 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;

4.外连接(有问题)

1.左外连接

select city.name,city.countrycode,country.name,city.population

from city left join country

on city.countrycode=country.code

and city.population < 100 limit 5;

+----------------+-------------+------+------------+

| name | countrycode | name | population |

+----------------+-------------+------+------------+

| Kabul | AFG | NULL | 1780000 |

| Qandahar | AFG | NULL | 237500 |

| Herat | AFG | NULL | 186800 |

| Mazar-e-Sharif | AFG | NULL | 127800 |

| Amsterdam | NLD | NULL | 731200 |

+----------------+-------------+------+------------+

2.右外连接

select city.name,city.countrycode,country.name,city.population

from city right join country

on city.countrycode=country.code

and city.population < 100 limit 5;

+------+-------------+-------------+------------+

| name | countrycode | name | population |

+------+-------------+-------------+------------+

| NULL | NULL | Aruba | NULL |

| NULL | NULL | Afghanistan | NULL |

| NULL | NULL | Angola | NULL |

| NULL | NULL | Anguilla | NULL |

| NULL | NULL | Albania | NULL |

+------+-------------+-------------+------------+