案例 多表查询

 

 

 

 

 

 

2.
SELECT
       t1.`id`, -- 员工编号
       t1.`ename`,-- 员工姓名
       t1.`salary`,
       t1.`mgr`,
       t3.`jname`,
       t3.`description`,
       t2.`loc`
       
FROM 
	emp t1,
	dept t2,
	job t3
WHERE 
	t1.`dept_id`=t2.`id`AND
	t1.`job_id`=t3.`id`;


3.
	
SELECT
        t1.`ename`,
        t1.`salary`,
        t2.`grade`
FROM
	emp t1,
	salarygrade t2
WHERE
	t1.`salary`BETWEEN t2.`losalary` AND t2.`hisalary`;



4.
SELECT
        t1.`ename`,
        t1.`salary`,
        t4.`jname`,
        t4.`description`,
        t3.`loc`,
        t2.`grade`
FROM
	emp t1,
	salarygrade t2,
	job t4,
	dept t3
WHERE
	t1.`salary`BETWEEN t2.`losalary` AND t2.`hisalary`
    AND
	t1.`dept_id`=t3.`id`
    AND
    t1.`job_id`=t4.`id`;



5.注意这里是嵌套查询
SELECT
	t1.`id`,
	t1.`dname`,
	t1.`loc`,
	t2.total
FROM
	dept t1,
	(SELECT 
             dept_id,COUNT(id) total
	FROM
	     emp
	GROUP BY dept_id) t2    分组查询
	         
WHERE
	t1.`id`=t2.dept_id;




6.注意这里是重名的表  
SELECT
	t1.`ename`,
	t2.`ename`
FROM 
	emp t1
LEFT JOIN
	emp t2
ON 
        t1.`mgr`=t2.`id`