mysql 多表查询

狂花配

已于 2022-03-22 16:23:00 修改

阅读量532

点赞数

分类专栏：数据库文章标签： mysql

于 2022-03-22 16:20:57 首次发布

原文链接：https://www.cnblogs.com/bypp/p/8618382.html

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

创建表和数据

# 创建部门表
CREATE TABLE IF NOT EXISTS dept (
    did int not null auto_increment PRIMARY KEY,
    dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;


# 添加部门数据
INSERT INTO `dept` VALUES ('1', '教学部');
INSERT INTO `dept` VALUES ('2', '销售部');
INSERT INTO `dept` VALUES ('3', '市场部');
INSERT INTO `dept` VALUES ('4', '人事部');
INSERT INTO `dept` VALUES ('5', '鼓励部');

-- 创建人员表
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
  `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
  `hire_date` date NOT NULL,
  `did` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 添加人员数据

-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);

1. 单表查询

SELECT DISTINCT id,(chinses+english+math) as sum from student; -- distinct 去重查询  as 取别名
select * from student ORDER BY chinses DESC;  -- ORDER BY 按照某一列排序  ASC 升序 DESC 降序
SELECT avg(sal) from emp GROUP BY deptno HAVING avg(sal)>80;

2. limit 结果显示条数（分页查询）

select * from emp ORDER BY sal LIMIT 每页显示数 * (第几页-1)，每页显示数
例子：  select * from emp ORDER BY sal LIMIT 3,3

3. 多表联合查询

注意: 如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积

# 查询人员和部门所有信息
select * from person,dept

笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积

添加条件进行查询

# 查询人员和部门所有信息
select * from person,dept where person.dept_id = dept.did

注意: 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用

效果：多表关联查询得到的结果是多表的公共部分

4. 多表链接查询

# 多表连接查询语法(重点)
SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;

4.1、内连接查询（只显示复合条件的查询）

#查询人员和部门所有信息
select * from person inner join dept  on person.did =dept.did;

效果: 大家可能会发现, 内连接查询与多表联合查询的效果是一样的.

4.2、左外连接查询（左表中的数据优先全部显示）

#查询人员和部门所有信息
select * from person left join dept  on person.did =dept.did

效果:人员表中的数据全部都显示,而部门表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充

4.3、右外连接查询（右表中的数据优先全部显示）

#查询人员和部门所有信息
select * from person right join  dept  on  person.did =dept.did

效果:部门表中的数据全部都显示,而人员表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充

4.4、全连接查询 union、union all（显示左表中全部数据）

全连接查询：是在内连接的基础上增加左右两边没有显示的数据
　　注意: mysql并不支持全连接 full JOIN 关键字
　　注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能

#查询人员和部门的所有数据
SELECT * FROM person LEFT JOIN dept ON person.did = dept.did
UNION
SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did;

效果：人员和部门表中的数据都显示，不是公共的部分显示null

注意: UNION 和 UNION ALL 的区别:UNION 会去掉重复的数据,而 UNION ALL 则直接显示结果

#查询人员和部门的所有数据
SELECT * FROM person LEFT JOIN dept ON person.did = dept.did
UNION ALL
SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did;

5.子查询（嵌套查询）

注意: 第一次的查询结果可以作为第二次的查询的条件或者表名使用

子查询中可以包含：in、not in、any、all、exists 和 not exists等关键字. 还可以包含比较运算符：= 、 !=、> 、<等。

5.1、作为表名使用

# 语法
select * from (select * from person) as 表名;

注意: 一条语句中可以有多个这样的子查询,在执行时,最里层括号(sql语句) 具有优先执行权
注意: as 后面的表名称不能加引号(’’)

5.2、使用=

# 求最大工资那个人的姓名和薪水
select name,salary from person where salary=(select max(salary) from person)

5.3、使用>

# 求工资高于所有人员平均工资的人员
select name,salary from person where salary >(select avg(salary) from person);

5.4、any/some关键字

any和some的效果是一样的，用法也一样

# 假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么
select ...from ... where a > any(...);
# 或者
select ...from ... where a > result1 or a > result2 or a > result3;

select ...from ... where a > some(...);
->
select ...from ... where a > result1 or a > result2 or a > result3;

5.5、ALL关键字

# ALL关键字与any关键字类似，只不过上面的or改成and。即:
select ...from ... where a > all(...);  
# 或者
select ...from ... where a > result1 and a > result2 and a > result3;

5.6、exists/not exists 子查询返回true才执行外查询

# 语法
SELECT ... FROM table WHERE  EXISTS (sql语句)

语法理解：主查询(外部查询)会根据子查询验证结果（TRUE 或 FALSE）来决定主查询是否执行。

# 例子
SELECT * FROM person WHERE EXISTS (SELECT * FROM dept WHERE did=6)

此处内查询并没有查询到数据，因此返回false，所以外层循环不执行

5.6.1、NOT EXISTS刚好与之相反

SELECT * FROM person WHERE not EXISTS (SELECT * FROM dept WHERE did=6)

解读：部门表没有编号为6的部门，所以内查询返回false，not exists为内查询返回false时执行外查询，执行外查询并返回查询到的数据

5.7、exists关键字和其他查询条件一起使用

EXISTS关键字可以与其他的查询条件一起使用，条件表达式与EXISTS关键字之间用AND或者OR来连接

5.8多列子查询

select * 
		from emp 
		where (deptno,job) = (
						select deptno,job 
						from emp 
						where ename = 'SMITH'
		)

6. 临时表查询

需求: 查询高于本部门平均工资的人员

解析思路: 1.先查询本部门人员平均工资是多少.

2.再使用人员的工资与部门的平均工资进行比较

select * from person as p1,
(SELECT did,AVG(salary) avg from person GROUP BY did) as p2
where p1.did = p2.did AND p1.salary >p2.avg

在当前语句中，我们可以把上一次的查询结果当前做一张表来使用，因为p2表不是真是存在的,所以:我们称之为临时表。临时表：不局限于自身表，任何的查询结果集都可以认为是一个临时表。

7.limit 结果显示条数（分页查询）

# 查询person表中从第3行往后5条数据的信息】
select * from person  limit 2,5

limit后面跟两个参数（必须是整数常量），第一个参数表示开始的索引（从0开始），第二个参数表示数据条数

8.判断查询

8.1、if 关键字

需求1 :根据工资高低,将人员划分为两个级别,分别为高端人群和低端人群。显示效果:姓名,年龄,性别,工资,级别

# 语法: IF(条件表达式,"结果为true",'结果为false');
select p1.*, 
    IF(p1.salary >10000,'高端人群','低端人群') as '级别'
from person p1;

8.2、case when then else多个条件

8.2.1简单case函数

# 语法
select *
CASE sex
     WHEN '1' THEN '男'
     WHEN '2' THEN '女'
ELSE '其他' END
from dept;

8.2.2case搜索函数

# 语法
select *
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END
from dept;

需求2: 根据工资高低,统计每个部门人员收入情况,划分为富人,小资,平民,吊丝四个级别, 要求统计四个级别分别有多少人

SELECT dname '部门',
             sum(case WHEN salary >50000 THEN 1 ELSE 0 end) as '富人',
             sum(case WHEN salary between 29000 and 50000 THEN 1 ELSE 0 end) as '小资',
             sum(case WHEN salary between 10000 and 29000 THEN 1 ELSE 0 end) as '平民',
             sum(case WHEN salary <10000 THEN 1 ELSE 0 end) as '吊丝'
FROM person,dept where person.did = dept.did GROUP BY person.did

9.sql执行顺序

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

此篇作为随笔，方便日后查询
原文出自：https://www.cnblogs.com/bypp/p/8618382.html

狂花配

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 多表查询

创建表和数据# 创建部门表CREATE TABLE IF NOT EXISTS dept ( did int not null auto_increment PRIMARY KEY, dname VARCHAR(50) not null COMMENT '部门名称')ENGINE=INNODB DEFAULT charset utf8;# 添加部门数据INSERT INTO `dept` VALUES ('1', '教学部');INSERT INTO `dept` VALUE
复制链接

扫一扫