Mysql全总结二-DQL语言之连接查询总结 2

文章目录

2.8 Sql92语法下的连接查询

2.8.1 前置知识

  • 连接查询: 又叫多表查询,当查询的字段来自于多个表时,就会用到连接查询.

主键(primary key): 唯一标识表中每行的这个列称为为主键.主键只能有一个而且其所在列不允许NULL值.
外键(foreign key): 外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系.外键可以有多个,可以重复,也可以为NULL.

笛卡尔积: 由当查询多个表时，由于没有添加有效的连接条件，导致多个表所有行实现完全连接的现象. 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数.

产生笛卡尔积的原因: 没有有效的连接条件
解决方法: 添加有效的连接条件

完全限定表名: 在引用的列可能出现二义性时,必须使用完全限定表明(表名.列名).

  • 连接查询的类别:

内连接就是 求两张表数据的交集部分,其种类主要是有等值连接,非等值连接和自连接.

外连接就是 求两张表数据的交集+主表中特有的数据,其种类主要是左外连接(左边的表是主表),右外连接(右边的表是主表),以及全外连接(每个表都是主表,全外连接就是要汇总所有连接的表的特有数据)

2.8.2 内连接之等值连接

等值连接: 基于两个表(或多个表)之间的相等测试.从左表中取出每一条记录，去右表中与所有的记录进行匹配：匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留。

格式:

SELECT 查询列表
FROM 表1, 表2
WHERE 表1.key = 表2.key
[GROUP BY]
[HAVING]
[ORDER BY]
[LIMIT]

举个栗子:

### 案例1. 查询 每个工作人员的工种,所在的部门以及他们的工资

SELECT last_name 名字, job_id 工种, department_name 部门,salary 工资
FROM employees, departments
WHERE departments.`department_id` = employees.`department_id`;

一般情况下, 我们通过给表名起别名来解决歧义

注意: 如果已经为表起了别名,则查询字段中不能再使用原始表名做限定!

### 案例2. # 查询员工名,工号,工种
###### 在表结构中, job_id在两张表中都存在.
########所以我们在SELECT查询时用到这个列名时需要指定表名噢!
SELECT last_name, j.job_id, job_title
FROM jobs j,employees e
WHERE j.`job_id` = e.`job_id`;

### 案例3. 
#加排序
#查询每个员工的工种名,员工的个数并且按员工个数排序

SELECT job_title,COUNT(*)
FROM jobs,employees
WHERE jobs.`job_id` = employees.`job_id`
GROUP BY job_title
ORDER BY COUNT(*);


#三表连接
# 员工名,部门名和城市名的查询

SELECT last_name, department_name, city
FROM employees e,departments d,locations lo
WHERE  e.`department_id` = d.`department_id`
AND d.`location_id` = lo.`location_id`;

对 等值连接的总结:

  1. 多表等值连接的结果为多表的交集部分
  2. n表连接,至少需要n-1个连接条件
  3. 多表的顺序没有要求
  4. 一般需要为表起别名
  5. 可以搭配前面介绍的所有子句使用(如 排序,分组,筛选)

2.8.2 内连接之非等值连接

连接条件(WHERE中的条件)是除等号之外的操作符

格式:

SELECT 查询列表
FROM 表1 别名, 表2 别名
WHERE  非等值的连接条件
[GROUP BY]
[HAVING]
[ORDER BY]
[LIMIT]

举个栗子:

# 查询员工的工资和工资级别

SELECT salary 工资, grade_level 工资级别
FROM employees, job_grades
WHERE salary BETWEEN lowest_sal AND highest_sal;

2.8.3 内连接之自连接

自连接的本意就是将一张表看成多张表来做连接

格式:

SELECT 查询列表
FROM 表1 别名1, 表1 别名2
WHERE  等值的连接条件
[GROUP BY]
[HAVING]
[ORDER BY]
[LIMIT]

格式:

SELECT 查询列表
FROM 表1, 表1
WHERE 表1.keyA = 表1.keyB
[GROUP BY]
[HAVING]
[ORDER BY]
[LIMIT]

举个栗子: (☆)

# 查询员工名以及其上司的名字

SELECT  a.last_name 员工名, a.manager_id 上司id,  b.last_name 上司名, b.employee_id
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;


# 查询每个国家下的部门个数大于2的国家编号

SELECT country_id, COUNT(*)
FROM departments de, locations lo
WHERE de.location_id = lo.`location_id`
GROUP BY country_id
HAVING COUNT(*) >2;

2.9 Sql99语法下的连接查询

格式:

 SELECT 查询列表
 FROM	表1 别名 
 # join用来连接第二张乃至第三..表,替代原来的逗号
 [连接类型] join 表2 别名
 # On 标识多表连接的连接条件
 On   连接条件
 # WHERE 是用来标识筛选条件
 WHERE 筛选条件

连接类型字段的值:

内连接: inner
外连接:

左外: left [outer]
右外: right[outer]
全外: full [outer]
交叉连接: cross

2.9 内连接

内连接格式:

SELECT 查询连接
FROM 表1 别名
Inner JOIN  表2 别名
ON 连接条件
WHERE 筛选条件

多张表怎么连接?

使用多个 INNER JOIN 表名 ON 连接条件
而且 连接条件一定要跟前后的表名有关联噢!

sql 99语法下的内连接的特点:

  1. 可以添加排序,分组和筛选
  2. inner 可以省略
  3. 筛选条件放在 WHERE 后面, 连接条件放在 ON 后面
  4. inner join 连接 和 sql92 语法中的等值连接效果一致,都是查询多表的交集;
2.9.1 SQL99 内连接之等值连接

等值连接原理同sql92一致, 格式稍有不同

举个栗子:

# 查询部门员工数>=3的部门名和员工个数并按个数降序（排序)

SELECT department_name 部门名, COUNT(last_name) 员工个数
FROM departments de
INNER JOIN employees emp
ON de.`department_id` = emp.`department_id`
GROUP BY department_name
HAVING COUNT(last_name) >= 3
ORDER BY COUNT(last_name) DESC;


#### 三表连接查询的🌰栗子:
# 查询员工名、部门名、工种名,并按部门名降序

SELECT last_name 员工名, department_name 部门名, job_title 工种名
FROM employees emp
INNER JOIN departments de ON emp.`department_id` = de.`department_id` 
INNER  JOIN jobs jo     ON jo.`job_id` = emp.`job_id`
ORDER BY department_name DESC;
2.9.2 SQL99 内连接之非等值连接

举个栗子:


# 查询员工的工资级别

SELECT last_name, salary, grade_level
FROM employees emp
INNER JOIN job_grades jg
WHERE emp.`salary` BETWEEN jg.lowest_sal AND jg.highest_sal;

#查工资级别的个数>2的个数,并按工资级别降序

SELECT grade_level, COUNT(*)
FROM employees
INNER JOIN job_grades
ON salary BETWEEN lowest_sal AND highest_sal
GROUP BY grade_level
HAVING COUNT(*)>2
ORDER BY grade_level DESC;
2.9.3 SQL99 内连接之自连接

原理同sql 92

举个栗子:

# 查询员工的姓名, 上级的姓名

SELECT empA.last_name 员工姓名, empB.last_name 上级姓名 
FROM employees empA
INNER JOIN employees empB
ON empA.`manager_id` = empB.employee_id;

2.10 外连接

外连接格式回顾:

左外: left [outer]
右外: right[outer]
全外: full [outer]

2.10.1 左外和右外连接的特点

应用场景: 用于查询多个表的交集数据以及主表特有部分的数据

特点:

  1. 外连接的查询结果为主表的所有记录
    - 如果从表中有和主表匹配的,则显示匹配的值
    - 如果从表中没有和主表匹配的,则显示NULL
  2. 左外连接, left join 左边的是主表
    右外连接, right join 右边的是主表
  3. 左外和右外交换两个表的顺序,可以实现同样的效果.

总结: 外连接查询的结果为主表和从表的交集部分,以及主表特有的部分!

如何确定哪个表是主表?

要查询的信息主要来自于哪个表,那么这个表就是主表.

举个栗子:

# 查询男朋友不在男神表的女神名

SELECT beauty.name, boys.boysfriend_id, boys.id
FROM beauty
LEFT OUTER JOIN boys
ON beauty.`boyfriend_id` = boys.`id`
WHERE boys.`id` IS NULL;


### 查询哪个部门没有员工

SELECT de.department_id, department_name, emp.last_name 
FROM departments de
LEFT OUTER JOIN employees emp
ON de.department_id = emp.department_id
WHERE emp.last_name IS NULL;
2.10.2 全外连接

Mysql不支持全外连接( full join)
全外连接 = 内连接的结果(多表的交集数据)+ 表1特有的 + 表2特有的.

2.10.3 交叉连接

交叉连接-cross join 就是sql99语法下 实现的笛卡尔积.

2.8-2.9 对连接查询的总结

2.10 子查询

出现在其他语句中的select语句查询, 称为子查询或内查询
相应的,外部的查询语句,称为主查询或外查询

  • 子查询知识点概览:
    在这里插入图片描述

2.10.1 子查询的类别

按子查询结果集的行列数不同:

查询类别解释
标量子查询结果集只有一行一列
列子查询结果集有一列多行
行子查询结果集有一行多列
表子查询结果集一般为多行多列

按子查询出现的位置:

位置支持的子查询类别
select后面仅支持标量子查询
from 后面支持表子查询
where 或 having后面标量子查询,列子查询,行子查询
exists 后面(相关子查询)表子查询
2.10.2 处于 where 或 having 后面的子查询(标量,列,行子查询)
  • 种类:
    • 1.标量子查询(单行子查询,一行一列)
    • 2.列子查询(多行子查询, 一列多行)
    • 3.行子查询(多行多列)
  • 特点:
      1. 子查询放在小括号内
      1. 子查询一般放在条件的右侧
      1. 标量子查询,一般配合着单行操作符(>,<,!= 等等)使用
      1. 列子查询, 一般配着多行操作符使用( in, any/some, all)
    • 5.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
2.10.2.1, 标量子查询(单行子查询)

举个栗子:

///
 #栗子 1. 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

  ##1. 查询 141号job_id
	SELECT job_id
	FROM employees
	WHERE employee_id=141;
  ##2, 查询143号 salary		
  
	SELECT salary 
	FROM employees
	WHERE employee_id=143;
	
  ## 3, 合并
	SELECT last_name, job_id, salary
	FROM employees
	WHERE job_id=(
		
		SELECT job_id
		FROM employees
		WHERE employee_id=141
	
	)AND salary >(
	
		SELECT salary 
		FROM employees
		WHERE employee_id=143
	
	);
///  
# 栗子 2. 返回公司工资最少的员工的last_name,job_id和salary

## 查到最少工资对应的id

SELECT  MIN(salary)
FROM employees

##合并

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary = (
	
	SELECT MIN(salary)
	FROM employees

)
///
# 栗子 3: 查询最低工资大于50号部门最低工资的部门id和其最低工资

## 1.50号部门的最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id= 50

## 2.合并

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (

	SELECT MIN(salary)
	FROM employees
	WHERE department_id= 50

);
2.10.2.2, 列子查询(多行子查询)
  • 1, 返回多行
  • 2, 使用下面的多行比较操作符
操作符含义
IN/NOT IN等于列表中的任意一个
ANY|SOME和子查询返回的某一个值比较
ALL和子查询返回的某一个值比较

记住:
>ANY 可在子查询中用MAX() 代替
<ANY 可在子查询中用MIN() 代替
=ANY 在子查询中跟 IN的作用相同
NOT IN在子查询中和 <>ALL 作用相同

切记子查询不要带分号,主查询最好别忘了分号!
#############  多行子查询

# 栗子1. 返回location_id是1400或1700的部门中的所有员工姓名


### 1.返回 loacation_id 是1400 或 1700 的部门id
SELECT department_id
FROM departments
WHERE location_id IN (1400, 1700)

### 2.合并
SELECT last_name
FROM employees
WHERE department_id IN (

	SELECT department_id
	FROM departments
	WHERE location_id IN (1400, 1700)

)


# 2.返回其它部门中比job_id为 'IT_PRoG'部门任一工资低的员工的:工号、姓名、job_id 以及salary
################ ANY 可用 MAX代替
##### 1. 查询 job_id = IT_PROG部门的工资
##########################  别忘了去重!!!!!!!1
SELECT DISTINCT salary
FROM employees
WHERE job_id ='IT_PROG'

##### 2.合并

SELECT DISTINCT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id !='IT_PROG'
AND salary < ANY(
	
	SELECT salary
	FROM employees
	WHERE job_id ='IT_PROG'

)
2.10.2.3, 行子查询(一行多列子查询或多行多列)

行子查询的适用情况

并列的子查询中条件的操作符一致(比如 都是=, 或都是 >)

举个栗子:

#### 行子查询
# 案例1: 查询员工编号最小并且工资最高的员工信息
###########行子查询用于并列子查询操作符一致的情况

SELECT employees.*
FROM employees
WHERE (employee_id, salary) = (

	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);

##上述栗子等同于下面的写法:

SELECT employees.*
FROM employees
WHERE employee_id =(

	SELECT MIN(employee_id)
	FROM employees
)AND salary=(

	SELECT MAX(salary)
	FROM employees
);
2.10.3 处于 select 后面的子查询(标量子查询)

举个栗子:

### select后面的子查询(标量子查询)
# 案例1: 查询每个部门的员工个数

>标量子查询,所以仅仅支持单行单列的子查询

SELECT de.*,(

	SELECT COUNT(*)
	FROM employees emp
	WHERE emp.department_id = de.`department_id`
)  员工个数
FROM departments de;
2.10.4 处于 from 后面的子查询(表子查询)

From后面的通常是表,
所以我们理所当然的把表子查询放在from后面,并且把这个子查询结果当做一张表来使用.
怎么使用? 起别名然后引用啊!!!
把主查询中的表与这张表进行连接查询即可求出想要的结果.

举个栗子:

### from 后面的表子查询

#案例1: 查询每个部门的平均工资的工资等级

#### 1.查询每个部门的平均工资

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id

#### 2. 2连接1的结果集和 job_grades表,
### 筛选条件为平均工资处于highest_sal 和 lowest_sal之间.
#####把第一步的查询的结果集(多行多列)看作是一张表
##### 从而把第一步的表与第二步需要的表采用内连接查询

SELECT avg_sal.*, jg.grade_level
FROM (
	SELECT department_id, AVG(salary) ag
	FROM employees
	GROUP BY department_id
) avg_sal
INNER JOIN job_grades jg
ON avg_sal.ag BETWEEN lowest_sal AND highest_sal;

///或者使用sql92语法

SELECT avg_salary.salary, grade_level
FROM job_grades,
(

	SELECT department_id, AVG(salary) salary
	FROM employees
	GROUP BY department_id

)  avg_salary
2.10.5 处于 exists 后面的子查询(相关子查询)

作用:判断查询,判断查询结果有没有值
格式: EXISTS(完整的查询语句), 不关心查询的具体结果, 只关心能不能查到数据
返回结果: 0或1.

举个栗子:

## 查询有员工的部门名

#### 题目的提示: 有员工. exists(员工信息)
SELECT department_name
FROM departments de
WHERE EXISTS (

	SELECT *
	FROM employees emp
	WHERE emp.`department_id` = de.`department_id`

);

### 查询没有女朋友的男神信息


## 根据题目的提示, 不存在女朋友, exists(女朋友信息)
SELECT * 
FROM boys bo
WHERE NOT EXISTS(
	SELECT *
	FROM beauty
	WHERE bo.id = beauty.`boyfriend_id`
);

2.11 分页查询

应用场景: 当要显示的数据,一页显示不全,需要分页提交sql请求.
核心方法: 使用LIMIT

  • 分页查询语法:
select 查询列表
from 表
[join type] join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset,size;

///offset 起始索引,从0开始的
///size 要显示的行数
  • 举个栗子:
# 查询前五条员工信息 

SELECT *
FROM employees
LIMIT 0,5;

# 有奖金 的员工信息,并且工资较高的前10名显示出来

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
  • LIMIT的特点
    1. limit语句放在查询语句的最后;
    2. 公式(设,要显示的页数page, 每页的条目数 size)
	select 查询列表
	from 表
	limit (page-1)*size, size;

	如: size=10
	page    起始索引
	1			0
	2			10
	3			20

2.12 union 联合查询

定义: 将多条查询语句的结果合并成一个结果.

语法:

查询语句1
union
查询语句2
union
...

应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致的时候.
特点:

  1. 因为union查询中,查询结果的列名跟第一个表查询列表相同. 所以联合查询要求多条查询语句的**查询列数**是一致的
  2. 要求多条查询语句中查询的每一列的**类型和顺序最好一致**
  3. Union 一般会对查询的结果去重,如果不想去重则 Union ALL(后面加上all)

相关的栗子:

posted @ 2022-05-26 20:31  青松城  阅读(33)  评论(0)  编辑  收藏  举报