sqlserver 关联查询基础查询 部门表和学生表

32 work 

   

--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 

 

 -- 2. 查询A国的员工姓名
 /*
   查询基础3条件：数据，来源，条件
  1 首先A国信息在部门表。关联查询 
  2 员工姓名. 员工姓名自然在员工表中，关联员工表查询，需要查询A国的所有员工姓名，员工表为主表，用左关联查询，主表在上. 从表在下
  3 条件查询. A国的ID主键确认是5,6. 关联学生表的部门ID是？ 为5. dept_id 部门ID为5. 关联字段为 5.
  3.1 条件选择： 仅仅取用部门ID 为5的数据. 
  查询的关键是 员工表中 部门ID的信息不同
  */
 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;
 

效果