MySQL中关联查询相关语句

最新推荐文章于 2025-01-05 19:48:40 发布

转载最新推荐文章于 2025-01-05 19:48:40 发布 · 157 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/feixiangdecainiao/p/10803768.html

文章标签：

#数据库

该博客为转载内容，转载自https://www.cnblogs.com/feixiangdecainiao/p/10803768.html ，涉及数据库相关知识。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- emp和job查询
SELECT 
    t1.`id`,
    t1.`ename`,
    t1.`salary`,
    t2.`jname`,
    t2.`descriptions`
FROM 
    emp t1,job t2,dept t3
WHERE t1.`job_id`=t2.`id`AND t3.`id`=t1.`dept_id`;


-- 查询工资的等级
SELECT 
    t1.ename,t1.`salary`,t2.`grade`
FROM 
    emp t1,salarygrade t2
WHERE t1.`salary`BETWEEN t2.`hisalar`AND t2.`hisalar`;

SELECT 
    t1.`id`,t1.`dname`,t1.`loc`,t2.total
FROM
    dept t1,
    (SELECT dept_id,COUNT(id) total
    FROM 
        emp
    GROUP BY emp.`dept_id`) t2
WHERE t1.`id`=t2.dept_id;



SELECT
    t1.`ename`,
    t1.`mgr`,
    t2.`id`,
    t2.`ename`
FROM
    emp t1,emp t2
WHERE t1.`id`=t2.`mgr`;

SELECT 
t1.`ename`,
t1.`mgr`,
t2.`id`,
t2.`ename`
FROM emp t1
LEFT JOIN emp t2
ON t1.`mgr`=t2.`id`