多表查询和子查询

ゞ浴缸里的玫瑰 2022-07-15 10:30 316阅读 0赞

**主要介绍了多表查询，以及在多表/单表查询时常用的函数和关键字，介绍了如何使用子查询。**

# **多表查询** #

employee表，departments表，localtions表结构

![Center][]

![Image 1][]

## **引例：** ##

select

last\_name, department\_name

from employees, departments

**分析：**

![Image 1][] ![Center 1][]

经过联接可以根据employee\_id查到department\_id,然后根据deparment\_id查到department\_name

![Image 1][]

![Center 2][]

使用逗号进行表之间的连接。产生的结果就是一个笛卡尔集。左表的每一条记录要和右表的每一条记录进行连接。

若左表8条记录，右表20条记录，使用笛卡尔联接就会产生160条记录。

通常这种连接方式我们是不提倡的，影响效率。

笛卡尔集产生条件：

①所有表中的所有行互相连接

②在标间连接的时候没有设置连接条件，或者连接条件无效

## 笛卡尔乘积，逗号连接 ##

将country2表和city2表联接

SELECT

FROM

city2, country2

WHERE

countrycode = code;

## **使用ON 子句创建连接** ##

自然连接中是以具有相同名字的列为连接条件的。

可以使用 ON 子句指定额外的连接条件。 这个连接条件是与其它条件分开的。

ON 子句使语句具有更高的易读性。

### 等值连接 ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      SELECT &nbsp; &nbsp; 
    </div> 
    <div>
      &nbsp; &nbsp;列名1, 列名2, ... 
    </div> 
    <div>
      FROM &nbsp; &nbsp;&nbsp; 
    </div> 
    <div>
      &nbsp; &nbsp; 表1 
    </div> 
    <div>
      [INNER] JOIN &nbsp; &nbsp;&nbsp; 
    </div> 
    <div>
      &nbsp; &nbsp; 表2 
    </div> 
    <div>
      ON &nbsp; &nbsp;&nbsp; 
    </div> 
    <div>
      &nbsp; &nbsp; 联接条件(等值条件) 
    </div> 
    <div>
      JOIN &nbsp; &nbsp; 
    </div> 
    <div>
      &nbsp; &nbsp; 表3 
    </div> 
    <div>
      ON &nbsp; &nbsp; 
    </div> 
    <div>
      &nbsp; &nbsp;联接条件 
    </div> 
    <div>
      WHERE &nbsp; &nbsp; 
    </div> 
    <div>
      &nbsp; &nbsp;普通过滤条件 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

查询城市人口数大于100万的城市名称和所属国家

SELECT

ci.name city,

co.name country ,

ci.population cityPop

FROM

city2 ci

\[INNER\] JOIN

country2 co

ci.countrycode = co.code

WHERE

ci.population > 1000000;

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 
    <div>
      &nbsp;注意事项: 
    </div> 
    <div>
      &nbsp; &nbsp;&nbsp; 内联虽然和逗号一样,&nbsp;优先使用内联 
    </div> 
    <div>
      &nbsp; &nbsp;&nbsp; INNER关键字可以省略 
    </div> 
    <div>
      &nbsp; &nbsp;&nbsp; ON和WHERE虽然可以混用,&nbsp;但是不建议 
    </div> 
    <div>
      &nbsp; &nbsp;&nbsp; ON&nbsp;后面最好是联接条件 
    </div> 
    <div>
      &nbsp; &nbsp;&nbsp; WHERE&nbsp;后面是普通过滤条件 
    </div> 
    <div>
      &nbsp; &nbsp;&nbsp; 查询结果就是满足联接条件的行 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

区分重复的列名

使用表名前缀在多个表中区分相同的列。

在不同表中具有相同列名的列可以用表的别名加以区分。

好处：

使用别名可以简化查询。 使用表名前缀可以提高执行效率。

## 多个连接条件与 AND 操作符 ##

表的联接可以不止一个

查询一下国家和首都,只显示首都人口大于500万的国家

SELECT

co.name country,

ci.name capital,

ci.populatiON,

cl.language

FROM

country co

JOIN

city ci

co.capital = ci.id

JOIN

countrylanguage cl

co.code = cl.countrycode

WHERE

ci.populatiON > 5000000

AND

cl.isofficial = 'T';

## **外联接** ##

保证某个表的数据的完整性

LEFT JOIN 左外连接，保证左表的完整性

RIGHT JOIN 右外连接，保证右表的完整性

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      SELECT 
    </div> 
    <div>
      &nbsp; &nbsp; 列名1,列名2,... 
    </div> 
    <div>
      FROM 
    </div> 
    <div>
      &nbsp; &nbsp; 表1 
    </div> 
    <div>
      LEFT [OUTER] JOIN 
    </div> 
    <div>
      &nbsp; &nbsp; 表2 
    </div> 
    <div>
      ON 
    </div> 
    <div>
      &nbsp; &nbsp; 联接条件 
    </div> 
    <div>
      WHERE 
    </div> 
    <div>
      &nbsp; &nbsp; 普通过滤条件 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

查询所有国家和首都

SELECT

co.name country,

ci.name capital

FROM

country co

LEFT JOIN

city ci

co.capital = ci.id;

查询所有国家及官方语言

SELECT

country.name,

cl.language,

cl.isofficial

FROM

country

LEFT JOIN

countrylanguage cl

country.code = cl.countrycode

WHERE

cl.isofficial = 'T'

cl.isofficial is null

哪些国家没有首都

SELECT

co.name country,

ci.name capital,

co.capital,

ci.id

FROM

country co

LEFT JOIN

city ci

co.capital = ci.id

WHERE

ci.name is null;

## 单行函数 ##

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> LOWER('SQL COURSE') ----&gt; sql course&nbsp;将目标字符串转为小写</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> UPPER('SQL Course')----&gt; SQL COURSE&nbsp;将目标字符串转为大写</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> CONCAT('Hello', 'World')----&gt;HelloWorld&nbsp;联接两个字符串</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> SUBSTR('HelloWorld',1,5)----&gt;Hello&nbsp;&nbsp;获取子串</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> LENGTH('HelloWorld')----&gt;10&nbsp;&nbsp;&nbsp;获取字符串的长度</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> INSTR('HelloWorld', 'W')----&gt;6&nbsp;&nbsp;在目标位置插入字符</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> LPAD(salary,10,'*')----&gt;*****24000&nbsp;&nbsp;右对齐填充</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> RPAD(salary, 10, '*')----&gt;24000*****&nbsp;&nbsp;左对齐填充</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> TRIM(‘H’ FROM 'HelloWorld')-----&gt;elloWorld 截取字符串</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> REPLACE('abcd','b','m')----&gt;amcd&nbsp;&nbsp;替换目标字符</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> ROUND(45.926, 2)----&gt;45.93&nbsp;四舍五入</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> TRUNC(45.926, 2)----&gt;45.92&nbsp;&nbsp;截断</td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> MOD(1600, 300)----&gt;100&nbsp;&nbsp;求余</td> 
  </tr> 
 </tbody> 
</table>

常用的单行函数使用方式及效果

将所有员工的名字连接起来，并对工资进行右对齐填充

SELECT

CONCAT(first\_name, last\_name) name, lpad(salary, 10, '0')

FROM

company.employees;

把所有国家和首都名使用:连接起来,并全部转为大写显示

SELECT

UPPER(CONCAT(co.name, ' : ', ci.name))

FROM

country co

LEFT JOIN

city ci

co.capital = ci.id

##    组函数 ##

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 分组函数作用于一组数据，并对一组数据返回一个值</td> 
  </tr> 
 </tbody> 
</table>

查询所有员工的平均工资和最大最小值,求和.

SELECT

AVG(salary) 平均工资,

MAX(salary) 最高工资,

MIN(salary) minSalary,

SUM(salary) sumSalary

FROM company.employees;

查看表中的记录数,不要使用具体列名,除非是主键

SELECT

count(\*)

FROM

country;

SELECT

\--last\_name,

MIN(salary) 最低平资

FROM

company.employees;

SELECT

\--name,

max(population)

FROM

country

WHERE

continent = 'europe';

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 在统计计算中拒绝出现代表个体的信息，上边的name,last_name都属于个体信息，不能在统计计算中出现</td> 
  </tr> 
 </tbody> 
</table>

##    GROUP BY ##

分组函数和组函数配合使用进行查询

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      SELECT 
    </div> 
    <div>
      &nbsp; &nbsp; &nbsp;分组条件, 
    </div> 
    <div>
      &nbsp; &nbsp; &nbsp; 组函数（这里的组函数是作用于某一组成员） 
    </div> 
    <div>
      FROM 
    </div> 
    <div>
      &nbsp; &nbsp; &nbsp;表名 
    </div> 
    <div>
      GROUP BY 
    </div> 
    <div>
      &nbsp; &nbsp; &nbsp;分组条件 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

查询每个部门的平均工资

SELECT

department\_id, AVG(salary)

FROM

company.employees

GROUP BY

department\_id;

查询各大洲的平均寿命和最少人口数

SELECT

continent,

avg(lifeexpectancy) avgLife,

min(population) minPop

FROM

country

GROUP BY

continent

## HAVING ##

查询部门平均工资大于9000的部门id

SELECT

department\_id, AVG(salary)

FROM

company.employees

GROUP BY

department\_id

HAVING

avg(salary) > 9000;

查询哪些国家的语言超过10种要求显示国家名称

SELECT

co.name,

count(cl.language) langs

FROM

countrylanguage cl

right JOIN

country co

cl.countrycode = co.code

GROUP BY

co.name

HAVING

langs > 10

ORDER BY

langs;

查询中国的城市个数

SELECT

countrycode,

count(\*)

FROM

city

GROUP BY

countrycode

HAVING

countrycode = 'chn';

##    DISTINCT ##

对获取到的某些记录进行去重

在City表中有多少不同的地区。

SELECT

DISTINCT District

FROM

city;

## 小结： ##

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      当上述关键字参与查询是，提供一种思考思路。 
     <br> &nbsp;1) 判断需要从哪个表中取数据 确定FROM 
     <br> &nbsp;2) 一张表够吗? 如果不够 JOIN&nbsp; 
     <br> &nbsp;3) 需要保证某表数据完整吗? 如果需要 考虑外联, 继续考虑左右的问题 
     <br> &nbsp;4) 联接条件的确定 ON&nbsp; 
     <br> &nbsp;5) 判断是否需要虚表的中的所有数据, 如果不需要 WHERE&nbsp; 
     <br> &nbsp;6) 是否需要分组, 如果需要 GROUP BY, 进一步判断分组依据的列名. 
     <br> &nbsp;7) 把分组依据的列名放在SELECT后面 
     <br> &nbsp;8) 继续考虑SELECT究竟要选择哪些列(组函数) 
     <br> &nbsp;9) 分组的结果是都需要吗? 如果不需要 HAVING过滤 
    </div> 
    <div>
      &nbsp;10)是否需要对最终的显示排序 ORDER BY 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      一个完整的SQL语句： 
    </div> 
    <div>
      select 
    </div> 
    <div>
      &nbsp; &nbsp; 分组依据的列名, 
    </div> 
    <div>
      &nbsp; &nbsp; 其他的列, 组函数 
    </div> 
    <div>
      from 
    </div> 
    <div>
      &nbsp; &nbsp; 表名1(子查询) 
    </div> 
    <div>
      join 
    </div> 
    <div>
      &nbsp; &nbsp; 表名2 
    </div> 
    <div>
      on 
    </div> 
    <div>
      &nbsp; &nbsp; 表的联接条件 
    </div> 
    <div>
      left join 
    </div> 
    <div>
      &nbsp; &nbsp; 表名3 
    </div> 
    <div>
      on 
    </div> 
    <div>
      &nbsp; &nbsp; (外联接时on条件必须有) 表的联接条件 
    </div> 
    <div>
      where 
    </div> 
    <div>
      &nbsp; &nbsp; 过滤条件 
    </div> 
    <div>
      group by 
    </div> 
    <div>
      &nbsp; &nbsp; 分组依据的列名 
    </div> 
    <div>
      having 
    </div> 
    <div>
      &nbsp; &nbsp; 组函数 比较表达式(分组结果的过滤) 
    </div> 
    <div>
      order by 
    </div> 
    <div>
      &nbsp; &nbsp; 排序依据的列 desc 
    </div> 
    <div>
      limit 
    </div> 
    <div>
      &nbsp; &nbsp; 略过的记录数,最终显示限制的记录数(每页显示的记录数) 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

哪些国家没有列出任何使用语言？

SELECT

a.name

FROM

country a

LEFT JOIN

countrylanguage b

a.code = b.countrycode

WHERE

b.language is null;

\-----------------------------

SELECT

a.name, count(language)

FROM

country a

LEFT JOIN

countrylanguage b

a.code = b.countrycode

GROUP BY

a.name

HAVING

count(language) = 0;

列出在城市表中80%人口居住在城市的国家

SELECT

a.name,

(sum(b.populatiON) / a.populatiON) AS rate

FROM

country a

JOIN

city b

a.code = b.countrycode

GROUP BY

a.name

HAVING

rate >= 0.8;

Sweden国家说的是什么语言？

SELECT

a.name,

b.language

FROM

country a

JOIN

countrylanguage b

a.code = b.countrycode

WHERE

a.name = 'Sweden';

查询中国的每个省的总城市数量和总人口数

SELECT

district,

count(\*),

sum(population)

FROM

city

WHERE

countrycode = 'chn'

GROUP BY

district

ORDER BY

sum(population) desc;

\-------------------------------------

SELECT

countrycode,

district,

count(\*),

sum(population)

FROM

city

GROUP BY

district

HAVING

countrycode = 'chn'

ORDER BY

sum(population) desc;

# **子查询** #

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      子查询 (内查询) 在主查询之前一次执行完成。 
    </div> 
    <div>
      子查询的结果被主查询(外查询)使用 。 
    </div> 
    <div>
      可以用一个子查询替代上边的的表名。 
    </div> 
    <div>
      子查询，将查询操作嵌套在另一个查询操作中。先执行子查询，再执行外查询 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## **单行子查询** ##

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 只返回一行。 使用单行比较操作符。</td> 
  </tr> 
 </tbody> 
</table>

![Center 3][]

![Image 1][]

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      SELECT 
    </div> 
    <div>
      &nbsp; &nbsp; 列名1,列名2... 
    </div> 
    <div>
      FROM 
    </div> 
    <div>
      &nbsp; &nbsp; 表名(子查询) 
    </div> 
    <div>
      WHERE 
    </div> 
    <div>
      &nbsp; &nbsp; 过滤条件(同样也可以使用子查询) 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

查询谁的工资比'Abel'的工资高

SELECT

last\_name

FROM

employees

WHERE

salary >(SELECT

salary

FROM

employees

WHERE

last\_name = 'Abel');

### 在子查询中使用组函数 ###

查询城市人口最多的城市名称

SELECT

name

FROM

city

WHERE

population = (SELECT

max(population)

FROM

city);

查询最发达的国家

SELECT

name

FROM

country

WHERE

gnp = (SELECT

max(gnp)

FROM

country);

### 子查询中的 HAVING 子句 ###

查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT

department\_id, MIN(salary)

FROM

employees

GROUP BY

department\_id

HAVING MIN(salary) >(SELECT

MIN(salary)

FROM

employees

WHERE

department\_id = 50);

###    非法使用子查询 ###

![Center 4][] ![Image 1][]

### 子查询中的空值问题 ###

![Center 5][] ![Image 1][]

## **多行子查询** ##

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 返回多行。 使用多行比较操作符。</td> 
  </tr> 
 </tbody> 
</table>

![Center 6][]

![Image 1][]

### 在多行子查询中使用 ANY 操作符 ###

返回其它部门中比job\_id为‘IT\_PROG’部门 任一工资低的员工的员工号、姓名、job\_id 以及salary

SELECT

employee\_id, last\_name, job\_id, salary

FROM

employees

WHERE salary < ANY (SELECT

salary

FROM

employees

WHERE

job\_id = 'IT\_PROG')

AND job\_id <> 'IT\_PROG';

### 在多行子查询中使用 ALL 操作符 ###

返回其它部门中比job\_id为‘IT\_PROG’部门 所有工资都低的员工 的员工号、姓名、job\_id 以及salary

SELECT

employee\_id, last\_name, job\_id, salary

FROM

employees

WHERE salary < ALL (SELECT

salary

FROM

employees

WHERE

job\_id = 'IT\_PROG')

AND job\_id <> 'IT\_PROG';

###    子查询中的空值问题 ###

![Image 1][]

![Center 7][]

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      小结：在查询时基于未知的值时，应使用子查询 
    </div> 
    <div>
      &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 子查询可以返回多个结果/单个结果，结果个数不同应该使用不同的操作符 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

[Center]: /images/20220715/276585cc29c24715b5e565fa7da13e62.png
[Image 1]: 
[Center 1]: /images/20220715/b63c3e9535224f519a533cdfad00c19c.png
[Center 2]: /images/20220715/8128ddf34df7457cb6144446f7f3c304.png
[Center 3]: /images/20220715/3d3b44368c0f4114a3cc15c9d92139e1.png
[Center 4]: https://img-blog.csdn.net/20161113175251197?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 5]: https://img-blog.csdn.net/20161113175307554?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 6]: /images/20220715/8b702d8891a04a84a3d3fe70364d09a6.png
[Center 7]: /images/20220715/f87835938a854077b6d6bcaa2e03ab14.png