连接查询sql199

root
233
文章
0
评论
2021年4月26日18:57:28 评论 3586字阅读11分57秒

连接查询sql199

多表查询的条件不能少于表数-1,不然就会出现笛卡尔积

语法:

SELECT  查询列表

FROM    表1 别名

【连接类型】  JOIN      表2    别名

ON        连接条件

    【WHERE  筛选条件】

    【group by  分组 】

    【having  筛选条件】

    【order by 排序列表】

分类:

内连接:inner,只会保留两个表都存在的数据(还记得之前的交集吗),这看起来意味着一些数据的丢失,在某些场景下会有问题.

外连接:

  • 左外:left 【outer】
  • 右外:right 【outer】
  • 全外:full 【outer】

交叉连接: cross

内连接INNER JOIN

语法:

SELECT 查询列表

FROM 表1 别名

inner join 表2  别名

on 连接条件:

分类:

  • 等值
  • 非等职
  • 自连接

等值连接

1.查询 员工名,部门名

SELECT last_name,department_name
FROM employees p1
INNER JOIN departments p2
ON p1.`department_id`=p2.`department_id`;

结果:

Whalen	       Adm
Hartstein	       Mar
Fay	               Mar
Raphaely	       Pur
Khoo	        Pur

2.查询名字中包含e的员工名和工种名(筛选)

SELECT last_name,job_title
FROM employees p1
INNER JOIN jobs p2
ON p1.`job_id`=p2.`job_id`
WHERE p1.`last_name` LIKE '%e%';

结果

De Haan	       Administration Vice President
Ernst	        Programmer
Lorentz	        Programmer
Greenberg	Finance Manager
Faviet	        Accountant

3.查询部门个数>3的城市名和部门数

SELECT COUNT(*) 部门个数,city
FROM departments p1
INNER JOIN locations p2
ON p1.`location_id`=p2.`location_id`
GROUP BY city
HAVING COUNT(*)>3

结果

21	Seattle

4.查询 那个部门的部门员e工个数>3的部门名和员工个数,并按个数降序

SELECT COUNT(*) 部门个数,department_name
FROM employees p1
INNER JOIN departments p2
ON p1.`department_id`=p2.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY 部门个数 DESC

结果

45	Shi
29	Sal
8	Exe
6	Fin
6	Pur
5	IT

5.查询员工名,部门名,工种名,并按部门名降序

SELECT last_name,department_name,job_title
FROM employees p1
INNER JOIN departments p2 ON p1.`department_id`=p2.`department_id`
INNER JOIN jobs p3 ON p1.`job_id`=p3.`job_id`
ORDER BY department_name DESC

查询

Mikkilineni	Shi	Stock Clerk
Dellinger	Shi	Shipping Clerk
Matos	Shi	Stock Clerk
Markle	Shi	Stock Clerk
Chung	Shi	Shipping Clerk
Weiss	Shi	Stock Manager
Atkinson	Shi	Stock Clerk
Gates	Shi	Shipping Clerk

 

非等职连接

1.查询员工工资级别

SELECT salary,grade_level
FROM employees p1
INNER JOIN job_grades p2
ON p1.`salary` BETWEEN p2.`lowest_sal` AND p2.`highest_sal`

结果

24000	E
17000	E
17000	E
9000	C
6000	C

2.查询每个工资级别的人数>20的人数,并且进行按工资级别排序

SELECT COUNT(*) 个数,salary,grade_level
FROM employees p1
INNER JOIN job_grades p2
ON p1.`salary` BETWEEN p2.`lowest_sal` AND p2.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

结果

38	9000	C
26	4800	B
24	2900	A

 

自连接,用在一张表当做2张表使用的时候

1.查询员工的名字,上级的名字

SELECT p1.last_name, p2.last_name
FROM employees p1
INNER JOIN employees p2
ON p1.`manager_id`=p2.`manager_id`
WHERE p1.`last_name` LIKE '%k%'

结果

Kochhar	Kochhar
Kaufling	Kochhar
Zlotkey	Kochhar
Kochhar	De Haan
Kaufling	De Haan
Zlotkey	De Haan
Kochhar	Raphaely
Kaufling	Raphaely
Zlotkey	Raphaely

 

外连接

应用场景:用于查询一个表中有,另一个表中没有的场景

特点

  • 1.外连接的查询结果为主表中的所有记录
    • 如果从表中有和它匹配的,则显示匹配的值
    • 如果从表中没有和它匹配的,则显示null
    • 外连接查询结果=内连接结果+住表中有而从表没有的记录
  • 2.主表从表的划分
    • 左外连接,left join左边的是主表
    • 右外连接,right join右边的是 住表
  • 3.左外和右外交换两个表的顺序,可以实现同样的效果
  • 4.全外连接=内连接的结果+表1中有但表2在没有的+表二中有但表1没有的

1.查询一下男朋友不在boys表里的girl名

SELECT p1.name,p2.*
FROM beauty p1
LEFT OUTER JOIN boys p2
ON  p1.`boyfriend_id`=p2.`id`

结果

周芷若	1	张无忌	100
小昭 	1	张无忌	100
赵敏 	1	张无忌	100
热巴 	2	鹿晗	800
Angelababy	3	黄晓明	50
王语嫣	4	段誉	300
柳岩		null	null           null 
苍老师	null 	null 	         null 
周冬雨	null 	null 	         null 
岳灵珊      null null            null 			
双儿	        null null            null 		
夏雪         null null            null

 

1.查询编号>3的女神的男朋友信息,如果有则列出详情,如果没有,用NULL填充

SELECT p1.*,p2.boyName
FROM beauty p1
LEFT OUTER JOIN boys p2
ON p2.`id`=p1.`boyfriend_id`
WHERE p1.id>3 ORDER BY p1.id DESC

结果

12	赵敏	女	1992-02-03 00:00:00	18209179577		1	张无忌
11	夏雪	女	1993-02-03 00:00:00	18209876579		9	null
10	王语嫣	女	1992-02-03 00:00:00	18209179577		4	段誉
9	双儿	女	1993-02-03 00:00:00	18209876579		9	null
8	小昭	女	1989-02-03 00:00:00	18209876567		1	张无忌
7	岳灵珊	女	1987-12-30 00:00:00	18219876577		9	null
6	周芷若	女	1988-02-03 00:00:00	18209876577		1	张无忌
5	周冬雨	女	1992-02-03 00:00:00	18209179577		9	null
4	热巴	女	1993-02-03 00:00:00	18209876579		2	鹿晗

2.查询哪个城市没有部门

SELECT city,p1.*
FROM locations p1
LEFT OUTER JOIN departments p2
ON p1.`location_id`=p2.`location_id`
WHERE p2.`department_id` IS NULL

结果

Roma	1000	1297 Via Cola di Rie	00989	Roma		IT
Venice	1100	93091 Calle della Testa	10934	Venice		IT
Tokyo	1200	2017 Shinjuku-ku	1689	Tokyo	Tokyo Prefecture	JP

 

3.查询部门名为SAL或IT的员工信息

SELECT p2.*,department_name
FROM departments p1
LEFT OUTER JOIN employees p2
ON p1.`department_id`=p2.`department_id`
WHERE p1.`department_name` IN('SAL','IT')

结果

 

 

 

 

 

 

 

 

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2021年4月26日18:57:28
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Mysql 创建表 MySQL

Mysql 创建表

Mysql 创建表   Mysql创建数据库 CREATE DATABASE db_name //字符集 CHARACTER SET charset_name //校验规则 COLLATE ...
Mysql数据类型 MySQL

Mysql数据类型

Mysql数据类型   INT整形类型---属性 unsigned/signed 无符号/有符号 unsigned无符号,创建一个无符号的列 mysql> create table z...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: