mySQL基础之连接查询

最新推荐文章于 2023-02-13 17:13:44 发布
最新推荐文章于 2023-02-13 17:13:44 发布
阅读量3k 收藏 18
点赞数 4
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45342806/article/details/109688717
版权

进阶6：连接查询

含义：又称多表查询,当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行、表2 有n行，结果 = m*n 行

发生原因：当查询多个表时，没有有效的连接条件
如何避免：添加有效的连接条件

分类：
			按年代分类：
	1.		sql 92标准：仅仅支持内链接
								
								等值连接
								非等值连接
								自连接
								
		不稳定			也支持一部分外连接(用于oracle、sqlserver\mysql不支持) 
			
	2.		sql 99标准 [推荐]：支持内连接+外连接(左外和右外) + 交叉连接
			
			按功能分类：
									内连接:
											等值连接
											非等值连接
											自连接
											
		  
									外连接：
											左外连接
											右外连接
											全外连接(mysql不支持)
									交叉连接

SELECT * FROM beauty;

SELECT * FROM boys;

SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

一、sql 92标准

1、等值连接

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

语法：
			SELECT 查询列表
			FROM 表1 别名,表2 别名
			WHERE 表1.key = 表2.key
			【AND 筛选条件】
			【GROUP BY 分组字段】
			【HAVING 分组后的筛选】
			【ORDER BY 排序字段】
案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

USE myemployees;
案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM	employees,departments
WHERE employees.`department_id` = departments.`department_id`;

2、为表起别名

1.提高语句的简洁度
2.区分多个重名的字段

注意：如果为表起了别名,则查询的字段就不能使用原来的表名去限定
查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id;

3、两个表的顺序可以调换

查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM jobs j,employees e
WHERE e.job_id = j.job_id;

4、能加筛选条件

案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';

5、可以加分组

案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city;
案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_id = e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,manager_id;

6、可以加排序

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

7、可以实现三表连接

案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city LIKE 's%'
ORDER BY department_name DESC;

2、非等值连接

语法：
		SELECT 查询列表
		FROM 表1 别名,表2 别名
		WHERE 非等值的连接条件
		【and 筛选条件】
		【GROUP BY 分组字段】
		【HAVING 分组后的筛选】
		【ORDER BY 排序字段】
案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

/*
SELECT * FROM job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);

INSERT INTO job_grades
VALUES ('A',1000,2999);

INSERT INTO job_grades
VALUES ('B',3000,5999);

INSERT INTO job_grades
VALUES ('C',6000,9999);

INSERT INTO job_grades
VALUES ('D',10000,14999);

INSERT INTO job_grades
VALUES ('E',15000,24999);

INSERT INTO job_grades
VALUES ('F',25000,40000);

*/

3.自连接

语法：
		SELECT 查询列表
		FROM 表 别名1,表 别名2
		WHERE 等值的连接条件
		【and 筛选条件】
		【GROUP BY 分组字段】
		【HAVING 分组后的筛选】
		【ORDER BY 排序字段】
案例：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

--------------------------------练习题1----------------------------------

1.显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary)
FROM employees;
2.查询员工表的employee_id,job_id,last_name,按department_id降序,salary升序
SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_id DESC,salary ASC;
3.查询员工表的job_id中包含 a 和 e 的，并且 a 在 e 的前面
SELECT job_id
FROM employees
WHERE job_id LIKE '%a%e%';
4.已知表student，里面有id（学号），name,gradeId(年级编号)
已知表grade，里面有id(年级编号),name(年纪名)
已知表result,里面有id,score,studentNo(学号)
要求查询姓名、年级名、成绩
SELECT s.name,g.name,r.score
FROM student s,grade g,result r
WHERE s.id = r.studentNo
AND g.id = s.gradeid;
5.显示当前日期，去除前后空格，截取子字符串的函数
SELECT NOW();
SELECT TRIM([字符 FROM] '');

SELECT SUBSTR(str,startIndex);
SELECT SUBSTR(str,startIndex,LENGTH(str));

---------------------------------练习题2--------------------------------

1.显示所有员工的姓名，部门号和部门名称
USE myemployees;

SELECT first_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.department_id = 90;
3.选择所有有奖金的员工的last_name,department_name,location_id,city
SELECT last_name,department_name,l.location_id,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND commission_pct IS not NULL;
4.选择city在Toronto工作的员工的 last_name,job_id,department_id,department_name
SELECT last_name,job_id,d.department_id,department_name
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city = 'Toronto';
5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY department_name,job_title;
6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY country_id
HAVING COUNT(*) > 2;
7.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp# manager Mgr#
kochhar 101 King 100

SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = "kochhar";

二、sql 99语法

语法：
			SELECT 查询列表
			FROM 表1 别名            # 主表
			【连接类型】 join 表2 别名
			on 连接条件
			【where 筛选条件】
			【GROUP BY 分组】
			【having 筛选条件】
			【ORDER BY 排序列表】
			
分类：

	内连接：inner
	外连接
					左外：left 【outer】
					右外: right 【outer】
					全外：full 【outer】
			
	交叉连接：cross

一> 内连接

内连接就是关联的两张或多张表中，根据关联条件，显示所有匹配的记录，匹配不上的，不显示。
语法：
			SELECT 查询列表
			FROM 表1 别名      # 主表
			inner join 表2 别名
			on 连接条件;
			
			
分类：
			等值
			非等值
			自连接
特点：
			添加排序、分组、筛选
			inner可以省略
			筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
			inner join连接和sql 92语法中的等值连接效果是一样的,都是查询多表的交集

一、等值连接

1.查询员工名、部门名(调换位置)

SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
ON e.department_id = d.department_id;

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

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

3.查询部门个数>3的城市名和部门个数(分组+筛选)

i.查询每个城市的部门个数
ii.在i结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM  departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING count(*) >3 ;

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

i.查询每个部门的员工个数
SELECT count(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name;
ii.在i结果上筛选员工个数>3的记录,并排序
SELECT count(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name
HAVING count(*) > 3
ORDER BY count(*) DESC;

5.查询员工名、部门名、工种名，并按部门名排序(添加三表连接)

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON e.job_id = j.job_id
ORDER BY department_name DESC;

二、非等值连接

查询员工的工资级别

SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

查询工资级别>20的个数,并且按工资级别降序

SELECT COUNT(*),grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC;

三、自连接

查询员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';

二>外连接

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

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

引入：查询男朋友 不在男神表的女神名

USE girls;
INSERT into boys values(8,"奥利奥",111);
insert into boys values(9,"尕小焱",110);

SELECT * FROM beauty;
SELECT * FROM boys;

左外连接

SELECT b.name,bo.*
FROM beauty b
left outer join boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id IS NOT NULL;

右外连接

SELECT b.name,bo.*
FROM boys bo
left outer join beauty b
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;
案例1：查询哪个部门没有员工
USE myemployees;  #使用myemployees库
左外
SELECT d.*,e.employee_id
FROM departments d       # 主表
RIGHT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
右外
SELECT d.*,e.employee_id
FROM employees e       # 主表
RIGHT OUTER JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
全外
USE girls;
SELECT b.*,bo.*
FROM beauty b          # 主表
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;
交叉连接
SELECT b.*,bo.*
FROM beauty b         # 主表
CROSS JOIN boys bo;

---------------------------多表连接练习题---------------------------

一、查询编号>3的女神的男朋友信息,如果有则列出详细,如果没有,用null填充
USE girls;

SELECT b.id,b.name,bo.*
FROM beauty b    # 主表
left OUTER join boys bo
ON b.boyfriend_id = bo.id
WHERE b.id >3;
二、查询哪个城市没有部门
USE myemployees;

SELECT city
FROM departments d    # 主表
RIGHT OUTER JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_id IS NULL;
三、查询部门名为SAL或IT的员工信息
 SELECT e.*,department_name
 FROM departments d
 LEFT JOIN employees e
 ON d.department_id = e.department_id
 WHERE d.department_name IN('SAL','IT');

SQL99语法

1. 内连接
	语法：
		select 查询列表
		from 表1 别名
		inner join 表2 别名 on 连接条件
		where 筛选条件
		group by 分组列表
		having 分组后的筛选
		order by 排序列表
		limit 子句;

	特点：
		1. 表的顺序可以调换
		2. 内连接的结果 = 多表的交集
		3. n表连接至少需要n-1个连接条件

	分类：
		1. 等值连接
		2. 非等值连接
		3. 自连接


2. 外连接
	1. 语法：
		select 查询列表
		from 表1 别名
		left|right|full [inner] join 表2 别名 on 连接条件
		where 筛选条件
		group by 分组列表
		having 分组后的筛选
		order by 排序列表
		limit 子句;
	2. 特点：
			1. 查询的结果 = 主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
			2. left join左边的就是主表,right join右边的就是主表			full join两边都是主表
			3. 一般用于查询除了交集部分的剩余的不匹配的行


3. 交叉连接
	1. 语法：
		select 查询列表
		from 表1 别名
		cross join 表2 别名;
	2. 特点：
		类似于笛卡尔乘积
尕焱aYan
关注
  • 4
    点赞
  • 踩
  • 18
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
php基础之连接mysql数据库和查询数据
09-10
主要介绍了php连接mysql数据库和查询数据的方法和示例,需要的朋友可以参考下
史上最牛mysql-06 (多表连接)
小北呱的博客
12-06 3310
多表连接 个人博客 ：www.xiaobeigua.icu 多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。 1. 一个案例引发的多表连接 1.1 案例说明 从多个表中获取数据： #案例：查询员工的姓名及其部门名称 SELECT last_n
【数据库学习】十 连接查询--SQL99语法
愿万事胜意
10-10 591
相对于AQL92语法，更加推荐SQL99标准：支持内连接+外连接(左外和右外)+交叉连接。 语法： select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】 【group by 分组】 【having 筛选条件】 【order by 排序列表】 分类： 内连接：inner 外连接 左外:left...
MYSQL（连接查询）
qq_61741696的博客
04-27 1099
连接查询 含义： 又称多表查询，当查询的字段来自多个表时用到连接查询。 按年代分类： sql192标准，sql199标准（推荐） 按功能分类： 1.内连接：等值连接，非等值连接，自链接 2.外连接：左外连接，右外连接，全外连接 3.交叉连接 等值连接 #等值连接 #查询员工名和对应的部门名 SELECT last_name 员工名,department_name 部门名 FROM employees,departments WHERE employees.`department_id`
【Oracle数据库】查询练习
Lemon的罐头
04-23 3677
数据库查询练习基础查询条件查询 基础查询 1.查询所有的部门编号： Select deptno from emp; 2.查询所有有人的部门编号： select ename,deptno from emp; 3.查询所有岗位名称： Select job from emp; 条件查询 4.查询所有薪水超过两千的员工信息 Select * from where sal>2000; 5.查询所有20部门的员工姓名，编号及薪水 Select ename,empno,sal from where dept
SQL 根据部门Id查询所有子部门信息
柱子pg的博客
12-22 1355
SQL 根据部门Id查询所有子部门信息
Mysql学习记录（三）多表查询
although1的博客
03-26 284
分类 内连接 隐式内连接: 使用where条件消除无用数据 -- 查询所有员工信息和对应的部门信息 select * from emp,dept where emp.dept_id = dept.id; 显式内连接 语法：select 字段 from 表名 inner join 另一表名 on 条件语句 -- 查询所有员工信息和对应的部门信息 select * from emp inner ...
mysql之sql语句练习
m0_63199267的博客
02-13 1390
mysql之sql语句练习 单表查询 多表查询
07mysql连接查询
01-16
这是我自己学习mysql时的学习笔记，每一个知识点都是自己动手写的，涵盖了mysql中的几乎全部的基础知识点，查询，子查询，分组，排序，常用函数，多表连接，视图，变量，存储过程，函数，分支结构，循环结构等等
MySQL优化之连接优化
12-16
上文MySQL优化之缓存优化 这篇文章中提到了一个很重要的概念，就是show variables是用来表示系统编译或者配置在my.cnf中的变量值。而show status则称之为状态值，显示的是当前服务实例运行所具有的状态信息，是一个...
0.6 MySQL表基础连接
最新发布
08-03
0.6 MySQL表基础连接
mysql学习day8
qq_44967348的博客
09-15 322
进阶6：连接查询 SELECT name,boyName FROM beauty,boys; #笛卡尔乘积现象 #为避免，可以添加有效的连接条件 SELECT name,boyName FROM beauty,boys WHERE beauty.boyfriend_id=boys.id; /* 分类： 按年代标准sq192、sq199 按功能分类 内连接、外连接、交叉连接 / #一、sq92标准 #1、等值连接 / 1.多表等值连接的结果为多表的交集部分 2.n表连接，至少需要n-1个连接条件 3.多表的顺
sql查询员工名字，所在部门，所在区域名字
weixin_42593549的博客
12-29 913
你可以使用如下的 SQL 查询来获取员工名字、所在部门、所在区域名字: SELECT e.name AS '员工名字', d.name AS '部门名字', r.name AS '区域名字' FROM employees e JOIN departments d ON e.department_id = d.id JOIN regions r ON d.region_id = r.id 在这个查...
MySQL小题
weixin_67450855的博客
07-02 2107
多看多记多理解
Mysql笔记（2）【DQL】
boyi6573的博客
09-08 575
自己所学MySQL数据库笔记
MySQL学习笔记——课后练习
热门推荐
java小豪的博客
02-16 1万+
MySQL学习笔记的课后练习
数据库的多表查询
qq_44972352的博客
08-20 432
-- 1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。 SELECT d.deptno, d.dname, d.loc, COUNT("renshu") FROM t_dept d, t_employees e WHERE e.deptno=d.deptno /*内连接:*/ /*方言*/ SELECT e.`deptno`,d.`dname`, d.`loc` , COUNT(e.`deptno`) FROM t_dept d, t_employees e WHERE e.`d
SQL练习题二
kygooooo的博客
01-05 1273
8、列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道该部门的部门编号。1、列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。4、列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。5、列出部门名称和这些部门的员工信息(人数、平均工资)，同时列出那些没有员工的部门。12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。17、列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数。
Oracle数据库练习2
m0_46684679的博客
09-24 1796
Oracle数据库练习2： 主要是emp、dept这两张表 emp表： dept表： 1、列出至少有一个员工的所有部门 多表查询，按部门编号（deptno）分组，再对每个组里人员编号（empno）进行大于等于1的过滤筛选 SELECT d.deptno,COUNT(e.empno) FROM emp e,dept d WHERE e.deptno = d.deptno(+) HAVING COUNT(e.ename) >= 1 GROUP by d.deptno; 2、列出薪金比“SMITH
MySQL数据库 - 连接查询
04-21
连接查询（Join）是SQL中最重要、最基础的查询操作之一，用于将两个或多个表中符合特定条件的记录组合在一起，形成一个更加全面的结果集。常见的连接方式有内连接（inner join）、外连接（outer join）、左连接...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值