54-2·100 sqlserver多对多查询-部门和员工表详情注释版

--01 部门表
create table t_dept(
ID int identity(1,1),
Depname varchar(100) null, --部门名称 
loc nvarchar(100) null,-- Location 所在地 

primary key(ID)

);
go
--查询部门表
select * from t_dept;


--删除数据
delete from t_dept where id>0 and id<5;
--add 部门信息
insert into t_dept(Depname,loc)values('法师部','A国');
insert into t_dept(Depname,loc)values('战士部','B国');
insert into t_dept(Depname,loc)values('射手部','C国');

--02 员工表
create table  t_emp(
ID int identity(1,1),
name varchar(100) null,  --员工姓名
sal decimal(18,1) null,
dept_id int not null, --外键设置. 参考部门的主键. 部门ID简写 
primary key(ID),
foreign key(dept_id) references t_dept(ID),
);
go
--查询员工表 
select * from t_emp;
--add data  部门表 主键ID从5开始. 2个表的关系字段为部门ID，dept_id 
insert into t_emp(name,sal,dept_id)values('诸葛亮',3000,5);
insert into t_emp(name,sal,dept_id)values('周瑜',2000,5);
insert into t_emp(name,sal,dept_id)values('关羽',1500,7); --找对应的关联字段的ID 
insert into t_emp(name,sal,dept_id)values('张飞',1000,7);
insert into t_emp(name,sal,dept_id)values('孙尚香',5000,9);
--查询每个员工的全部信息和部门全部信息 01 
select * from t_emp as a 
left join t_dept as b
on a.dept_id=b.ID; -- 员工表为主表，部门表为从表。
/*

about 查询 

1. 查询每个员工的名字和所在部门的名称

2. 查询A国的员工姓名

3. 查询张飞的部门名称

4. 查询工资在2000以下员工姓名，所在地


*/

--1. 查询每个员工的名字和所在部门的名称
-- 逻辑关系分析：员工表的dept_id 部门ID关联部门表的主键 ID.
--关联查询  左关联
-- 1. 查询每个员工的名字和所在部门的名称
select name,Depname  from t_emp as a  
left join t_dept as b
on a.dept_id=b.ID 
--扩展训练 拓展锻炼learn and write01
--1.1查询每个员工的名字,薪水和所在部门的名称
select name,sal,Depname  from t_emp as a  
left join t_dept as b
on a.dept_id=b.ID 
--1.2 查询每个员工的名字,薪水，部门ID和所在部门的名称
select name as 员工名字,sal as 薪水,dept_id as 部门ID,   Depname as 所在部门  from t_emp as a  
left join t_dept as b
on a.dept_id=b.ID 
--显示部门在前面，员工姓名在后面. 这是主表在上，部门表为主表，员工表为从表. 谁为主表，谁的查询信息查询就靠前.
-- 1.3 查询每个部门的名称和员工的名字.
select depname,name from t_dept as a
left join t_emp as b 
on a.ID=b.dept_id

/*
2 分析
查询基础3条件：数据，来源，条件
1 首先A国信息在部门表。关联查询 
2 员工姓名. 员工姓名自然在员工表中，关联员工表查询，需要查询A国的所有员工姓名，员工表为主表，用左关联查询，主表在上. 从表在下
3 条件查询. A国的ID主键确认是5,6. 关联学生表的部门ID是？ 为5. dept_id 部门ID为5. 关联字段为 5.
3.1 条件选择： 仅仅取用部门ID 为5的数据. 
查询的关键是 员工表中 部门ID的信息不同
首先想查哪国， 修改部门ID就行了。A国的部门ID为5. B国为7.修改数字就行了。
*/
-- 2. 查询A国的员工姓名
select name as A国员工姓名 from t_emp as a 
left join t_dept as b
on a.dept_id=b.ID
where a.dept_id=5; -- 重要的是员工表关联字段的信息.A国的部门ID为5. B国为7. 



--查询部门表
select * from t_dept;
--查询员工表 
select * from t_emp;

--3. 查询张飞的部门名称
/*
1 张飞在员工表.
1.1 张飞在员工表的（部门ＩＤ）信息是什么？ 为数字7. 
1.2 查询7 指向的部门名称是什么？ 
2 所在部门-在部门表.
3 条件查询. 用左关联查询 
需要 张飞员工表的部门ID等于 部门表的主键ID7. 才能关联联系起来. 就能查询出来张飞的部门名称信息了 
   
注意：
因为name不是唯一标识
可能有两个叫张飞的
ID就是身份证号，是唯一标识
*/
--3  查询张飞的部门名称
select name,Depname from t_emp as a 
left join t_dept as b
on a.dept_id=b.ID
where  a.name='张飞';

    /*
1 工资字段在员工表
2 
      
*/
--4 查询工资在2000以下员工姓名，所在地
select name as 员工姓名,loc as 所在地 from t_emp as a 
left join t_dept as b
on a.dept_id=b.ID
where sal<2000;


 
select * from t_dept as a 
left join t_emp as b
on a.id=b.dept_id;
 
--分组查询
select name   from t_emp group by name;

--部分字段,去掉星号，选择需要的列写入 
select name  from t_emp;