mysql基础操作（四）：简单多表查询

-- 创建部门  （主表）
create table dept(
	id int primary key auto_increment,
	name varchar(20)
);
-- 创建员工  （副表）
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	deptId int,                                                    --     级联修改          级联删除
	constraint employee_dept_fk foreign key(deptId) references dept(id) on update cascade on delete cascade -- 设置外键
);

insert into dept(name) values('软件开发部'),('软件维护部'),('秘书部');
insert into emplpyee(name, deptId) values('张三', 1),('李四', 2),('王五', 3), ('陈六', 2);

1.交叉连接查询（笛卡尔乘积：出来的结果是两张表的乘积 4*3 = 12）错误的

-- 产生笛卡尔积的原因：没有足够的连接条件
-- 需求：员工及其部门
select employee.name, dept.name from employee, dept;

多表查询的步骤：   
    1）确定查询哪些表
    2）确定查询哪些字段
    3）确定连接条件

语句涉及比较长之后可以分行写，语法遇到分号结束
一般我们可以给表一个别名：这里的as可以省略

-- 2.内连接查询

-- 内连接的效果：只有满足连接条件的数据才会显示出来

select e.name, d.name 
		from employee as e, dept as d  -- 可写为：from employee e, dept d
		where e.deptId = d.id;

-- 以上写法等同于：

select e.name, d.name
		from employee e
		inner join dept d
		on e.deptId = d.Id;

-- 3.外连接查询

-- 3.1 左外连接查询

-- 需求：查询部门及其部门的员工（部门全部都要显示）  
--       包括即使员工为null都要显示出来
-- 效果：左表 部门要全部显示出来，  左外连接查询 优先查询左表
--       右表的数据当满足连接条件的时候就显示满足条件的数据
--       当不满足条件时，则就显示一个null
-- 预期结果：
      --  软件开发部  张三
	  --  软件开发部  王五
	  --  软件维护部  李四
	  --  软件维护部  陈六
	  --  秘书部      null

select d.name, e.name
		from dept d
        left outer join employee e	
		on e.deptId = d.id;

-- 3.2 右外连接查询

-- 同上，右表优先显示，和上表效果一样的显示：
select d.name, e.name
		from employee e
		right outer join dept d
		on d.id = e.deptId;

-- 4.自连接查询

-- 现在有一个表如下：
-- id     name     deptId     bossId（上司id也是员工id）
-- 1      张三     4          null
-- 2      李四     5          1
-- 3      王五     4          2
-- 4      陈六     5          3

-- 需求：查询员工姓名及其上司姓名
-- 预期结果：
--    员工  上司 
   -- 张三  null
   -- 李四  张三
   -- 王五  李四
   -- 陈六  王五

select e.name, b.name
		from employee e 
		left outer join employee b
		on e.bossId = b.id;

join 和 union 的区别：

left join 表示至少2表要有1个匹配字段才能返回数据；

union则不需要有关联字段就可以返回数据；

而且union要求多次查询的结果列数必须一样;

例如：

select a,b
from t1
union 
select a,b
from t2

必须要求，t1,t2都是显示两个字段

select查询中的格式：

-- select 字段名，可以为*  可用聚合
-- from 表名 子查询中可跟limit
-- join
-- where 条件限制  子查询中可跟聚合
-- group by 分组语句  having 统计函数的比较语句 可跟聚合
-- order by 排序语句 可跟聚合
-- limit

补充：case when的使用

select wokshop_id as '车间编号',
sum(case when worker_sex='男' then 1 else 0 end) as '男',
sum(case when worker_sex='女' then 1 else 0 end) as '女'
from XXX;

SELECT            
    case                   -------------如果
    when sex='1' then '男' -------------sex='1'，则返回值'男'
    when sex='2' then '女' -------------sex='2'，则返回值'女'  
    else 0                 -------------其他的返回'其他’
    end                    -------------结束
from   sys_user            --------整体理解： 在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女' 否则返回'其他’