您的位置: 首页  >  IT文章  >  多表数据查询

多表数据查询

分类: IT文章 • 2023-03-08 00:22:13

Mysql Join语法以及性能优化

连表方式

内链接：只取两张表的共同部分

SELECT *
FROM employee
INNER JOIN department
ON employee.dep_id = department.id
View Code

左连接：在内连接的基础之上，保留左表记录，即便右表没有与之对应数据，无对于字段用NULL填充

SELECT *
FROM employee
LEFT JOIN department
ON employee.dep_id = department.id
View Code

右连接：内连接的基础之上，保留右表记录，即便左表没有数据与之对应，无对于字段用NULL填充

SELECT *
FROM employee
RIGHT JOIN department
ON employee.dep_id = department.id
View Code

全外连接：在内链接基础之上，没有对于关系的记录也将保留

实现原理，将左链接和右链接综合起来去重即可，UNION关键字

SELECT *
FROM employee
LEFT JOIN department
ON employee.dep_id = department.id
UNION
SELECT *
FROM employee
RIGHT JOIN department
ON employee.dep_id = department.id;
View Code

对虚拟表进行分组过滤操作

SELECT department.name ,avg(age)
FROM employee
INNER JOIN department
ON employee.dep_id = department.id
GROUP BY department.name
HAVING avg(age)>30
View Code

 SELECT语句完整语法

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
View Code

SELECT执行顺序

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT
View Code

子查询

IN，查询平均年龄在25岁以上的部门名

SELECT name
FROM department
WHERE id 
IN(
    SELECT dep_id
    FROM employee
    GROUP BY dep_id
    HAVING avg(age)>25);
View Code

=，查看技术部员工姓名

SELECT name 
FROM  employee
WHERE dep_id=(
                SELECT id
                FROM department
                WHERE name="技术部");
View Code

NOT IN，查看不足1人的部门名

SELECT name
FROM department
WHERE dep_id
NOT IN(            
    SELECT 
    DISTINCT dep_id
    FROM department);
View Code

>,查询大于所有人平均年龄的员工姓名与年龄

SELECT name,age
FROM employee
WHERE age
> (
    SELECT
    AVG(age)
    FROM employee);
View Code

EXISTS，判断查询是否有结果

SELECT *
FROM employee
WHERE    
EXISTS(
    SELECT id
    FROM department
    WHERE name="技术");
View Code

 把SELECT语句括起来加AS起别名后，下次可以继续对此做查询；

SELECT *
FROM 
    (SELECT id,name,sex
    FROM employee) AS t1;
View Code

每个部门最新入职的员工

SELECT * 
FROM employee
AS t1
INNER IN
(
    --：分组拿到单个组最大时间,起别名为t1
    SELECT post,max(hire_date)
    AS max_hire_date
    FROM employee
    GROUP BY post)
AS t2
ON t1.post=t2.post
WHERE t1.hire_date=t2.max_hire_date;
View Code

多表查询练习



相关推荐