MySQL之多表查询

表和数据:

-- 创建部门
CREATE TABLE dept (
    id int not null AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10) not null COMMENT '部门名称'
)
-- 添加部门数据
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', '鼓励部');

-- 创建人员
CREATE TABLE `person` (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(50) NOT NULL,
  age tinyint(4) DEFAULT '0',
  gender enum('','') NOT NULL DEFAULT '',
  salary decimal(10,2) NOT NULL DEFAULT '2500.00',
  hire_date date NOT NULL,
  dept_id int(11) DEFAULT NULL,
  CONSTRAINT fk_per_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
)
-- 添加人员数据
-- 教学部
INSERT INTO `person` VALUES ('1', '小A', '28', '', '5300.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', '小B', '23', '', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', '小C', '30', '', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', '小D', '18', '', '6680.00', '2014-06-21', '1');
-- 销售部
INSERT INTO `person` VALUES ('5', '小E', '20', '', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '小F', '20', '', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '小G', '20', '', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '小H', '20', '', '2000.00', '2015-06-21', '2');
-- 市场部
INSERT INTO `person` VALUES ('9', '小I', '21', '', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '小J', '22', '', '4000.00', '2015-07-15', '3');
-- 人事部
INSERT INTO `person` VALUES ('11', '小K', '17', '', '5000.00', '2014-06-21', '4');
-- 鼓励部
INSERT INTO `person` VALUES ('12', '小L', '33', '', '100000.00', '2018-02-21', null);
View Code

一、多表联合查询

-- 多表查询语法
select  字段1,字段2 from 表1,表2 [where 条件];
注意: 如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为"笛卡尔乘积"
笛卡尔乘积公式 : A表中数据条数 * B表中数据条数  = 笛卡尔乘积

-- 查询人员和部门所有信息
select * from person,dept;
id name age gender salary hire_date dept_id id1 name1

1    A    2853000    2010-06-21    1    1    教学部
1    A    2853000    2010-06-21    1    2    销售部
1    A    2853000    2010-06-21    1    3    市场部
1    A    2853000    2010-06-21    1    4    人事部
1    A    2853000    2010-06-21    1    5    鼓励部
2    B    238000    2011-02-21    1    1    教学部
2    B    238000    2011-02-21    1    2    销售部
2    B    238000    2011-02-21    1    3    市场部
2    B    238000    2011-02-21    1    4    人事部
2    B    238000    2011-02-21    1    5    鼓励部
3    C    306500    2015-06-21    1    1    教学部
3    C    306500    2015-06-21    1    2    销售部
3    C    306500    2015-06-21    1    3    市场部
3    C    306500    2015-06-21    1    4    人事部
3    C    306500    2015-06-21    1    5    鼓励部
4    D    186680    2014-06-21    1    1    教学部
4    D    186680    2014-06-21    1    2    销售部
4    D    186680    2014-06-21    1    3    市场部
4    D    186680    2014-06-21    1    4    人事部
4    D    186680    2014-06-21    1    5    鼓励部
5    E    203000    2015-02-21    2    1    教学部
5    E    203000    2015-02-21    2    2    销售部
5    E    203000    2015-02-21    2    3    市场部
5    E    203000    2015-02-21    2    4    人事部
5    E    203000    2015-02-21    2    5    鼓励部
6    F    202000    2018-01-30    2    1    教学部
6    F    202000    2018-01-30    2    2    销售部
6    F    202000    2018-01-30    2    3    市场部
6    F    202000    2018-01-30    2    4    人事部
6    F    202000    2018-01-30    2    5    鼓励部
7    G    202000    2018-02-27    2    1    教学部
7    G    202000    2018-02-27    2    2    销售部
7    G    202000    2018-02-27    2    3    市场部
7    G    202000    2018-02-27    2    4    人事部
7    G    202000    2018-02-27    2    5    鼓励部
8    H    202000    2015-06-21    2    1    教学部
8    H    202000    2015-06-21    2    2    销售部
8    H    202000    2015-06-21    2    3    市场部
8    H    202000    2015-06-21    2    4    人事部
8    H    202000    2015-06-21    2    5    鼓励部
9    I    214000    2014-07-21    3    1    教学部
9    I    214000    2014-07-21    3    2    销售部
9    I    214000    2014-07-21    3    3    市场部
9    I    214000    2014-07-21    3    4    人事部
9    I    214000    2014-07-21    3    5    鼓励部
10    J    224000    2015-07-15    3    1    教学部
10    J    224000    2015-07-15    3    2    销售部
10    J    224000    2015-07-15    3    3    市场部
10    J    224000    2015-07-15    3    4    人事部
10    J    224000    2015-07-15    3    5    鼓励部
11    K    175000    2014-06-21    4    1    教学部
11    K    175000    2014-06-21    4    2    销售部
11    K    175000    2014-06-21    4    3    市场部
11    K    175000    2014-06-21    4    4    人事部
11    K    175000    2014-06-21    4    5    鼓励部
12    L    331000000    2018-02-21        1    教学部
12    L    331000000    2018-02-21        2    销售部
12    L    331000000    2018-02-21        3    市场部
12    L    331000000    2018-02-21        4    人事部
12    L    331000000    2018-02-21        5    鼓励部
笛卡尔乘积示例

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

-- 查询人员和部门所有信息
select * from person,dept where person.dept_id = dept.id;
id name age gender salary hire_date dept_id id1 name1
1    小A    285300    2010-06-21    1    1    教学部
2    小B    238000    2011-02-21    1    1    教学部
3    小C    306500    2015-06-21    1    1    教学部
4    小D    186680    2014-06-21    1    1    教学部
5    小E    203000    2015-02-21    2    2    销售部
6    小F    202000    2018-01-30    2    2    销售部
7    小G    202000    2018-02-27    2    2    销售部
8    小H    202000    2015-06-21    2    2    销售部
9    小I    214000    2014-07-21    3    3    市场部
10    小J    224000    2015-07-15    3    3    市场部
11    小K    175000    2014-06-21    4    4    人事部
View Code

二、多表连接查询

-- 多表连接查询语法(重点)
select 字段列表
    from 表1 inner|left|right join 表2
on 表1.字段 = 表2.字段;

内连接查询 (只显示符合条件的数据)

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

效果:内连接查询与多表联合查询的效果是一样的。

全连接查询(显示左右表中全部数据)

  • 全连接查询:是在内连接的基础上增加,左右两边没有显示的数据。
  • 注意:mysql并不支持全连接 full JOIN 关键字
  • 注意:但是mysql提供了UNION关键字,使用 UNION 可以间接实现 full JOIN 功能
-- 查询人员和部门所有信息
select * from person left join dept on person.dept_id = dept.id
union
select * from person right join dept on person.dept_id = dept.id;

三、子语句查询

  • 子查询(嵌套查询):查多次, 多个select;
  • 注意:第一次的查询结果可以作为第二次的查询的 条件 或者 表名 使用;
  • 子查询中可以包含:in、not in、any、all、exists 和 not exists 等关键字,还可以包含比较运算符 = 、 !=、> 、<等。

作为表名使用

select * from (select * from person) as 表名;

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

求最大工资那个人的姓名和薪水

select name,salary from person where salary=(select max(salary) from person);

求工资高于所有人员平均工资的人员

-- 求平均工资
select avg(salary) from person;
-- 工资大于平均工资的 人的姓名、工资
select name,salary from person where salary>平均工资;
-- 合并
select name,salary from person where salary>(select avg(salary) from person);

关键字

假设any内部的查询语句返回的结果个数是三个,如:result1,result2,result3,那么,

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

select ...from ... where a > all(...);
->
select ...from ... where a > result1 and a > result2 and a > result3;
all
some关键字和any关键字是一样的功能。所以:

select ...from ... where a > some(...);
->
select ...from ... where a > result1 or a > result2 or a > result3;
some
EXISTSNOT EXISTS 子查询语法如下:

SELECT ... FROM table WHERE  EXISTS (subquery)
-- 该语法可以理解为:主查询(外部查询)会根据子查询验证结果(TRUE 或 FALSE)来决定主查询是否得以执行。

SELECT * FROM person
WHERE NOT EXISTS (SELECT * FROM dept WHERE id=5);
-- 此处内层循环并没有查询到满足条件的结果,因此返回false,外层查询不执行。
-- EXISTS刚好与之相反。
exists

四、其他查询

临时表查询

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

解析思路:

  1. 先查询本部门人员平均工资是多少
  2. 再使用人员的工资与部门的平均工资进行比较
-- 先查询部门人员的平均工资
select dept_id,avg(salary) as "平均工资" from person group by dept_id;
-- 再使用人员的工资与部门的平均工资进行比较
select * from person as p1,
(select dept_id,avg(salary) as "平均工资" from person group by dept_id) as p2
where p1.dept_id=p2.dept_id and p1.salary >p2.平均工资;

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

判断查询(if关键字)

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

select p1.name,p1.age,p1.salary, 
if (p1.salary>5000,"高端人群","低端人群") as "级别"
from person p1;
-- 语法: if (条件表达式,"结果为true",'结果为false');

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

-- 语法一
select case 
    when state = '1' then '成功'
    when state = '2' then '失败'
    else '其他' end 
from 表;
 
-- 语法二:
select case age
    when 23 then '23岁'
    when 27 then '27岁'
    when 30 then '30岁'
    else '其他岁' end
from 表;
语法
select dept.name as '部门',
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.dept_id = dept.id group by dept_id;

 

posted @ 2018-10-08 23:54  就俗人一个  阅读(173)  评论(0编辑  收藏  举报