SQL语句学习记录(一)

一道例题

一.表查询

T1.查询

给定一张表

employees

员工号employee_id

上司员工号 manager_id

(1)查询并显示所有存在下属员工的员工号

建表：

插入测试数据：

insert into employees values(2,1); 
insert into employees values(3,2); 
insert into employees values(4,2); 
insert into employees values(5,3); 

答案：

select a.employee_id from employees a, employees b 
where a.employee_id=b.manager_id 
group by a.employee_id 

(2)查询有下属的员工的id和名字

新建一张表emp_info

员工号 employee_id

员工名 employee_name

插入测试数据：

insert into emp_info values(2,'Tom'); 
insert into emp_info values(3,'Jerry'); 
insert into emp_info values(4,'Yanke'); 
insert into emp_info values(5,'Japs'); 

答案：

select employee_id,employee_name from emp_info 
where employee_id in( 
select a.employee_id from employees a, employees b 
where a.employee_id=b.manager_id 
group by a.employee_id 
) 

 

写的磕磕绊绊，决定要对SQL语句好好复习一番，复习选用菜鸟教程跟着敲一遍