Mysql Join-Join Query (Middle)

Mysql Join-Join Query (Middle)

know

As far as my usual data contact is concerned, join query is not very complicated, it is not very necessary to have patience and logic, just a little bit to find out the data, stitch, etc. No skill, more practice will be done. It's all about joining tables according to common key s, combining union, where, subquery these when querying..., it's really not difficult, it's just a little annoying, sometimes nested many layers, and will be dizzy.

Realization

Here I find some training courses on the Internet for demonstration. In fact, the training materials on the market, what to say, have the advantage of covering a wide range of fields, attaching importance to applications, but have the shortcomings that are superficial and only suitable for introductory understanding.

  • Departmental table dept(id, name)
  • Employee table emp(id, name, gender, salary, join_date, dept_id)
  • Association key: dept.id ~ dept_id (I do not use foreign keys)

Preparing data

-- mysql -u root -p ....
-- show database;
-- use cj;
-- show tables;
-- New Table Test Data
drop table if exists emp;
drop table if exists dept;

create table dept(
    id int primary key auto_increment,
    name varcahr(20)
);
-- Employee Sheet
create table emp(
    id int primary key auto_increment,
    name varchar(20),
    gender char(1),
    salary double,
    join_date date,
    dept_id int
);

-- Insert Test Data
insert into dept(name) values
("Marketing Department"), ("Finance Department"), ("R&D Department");

insert into emp values
(0, 'Third','male',20000,'2016-02-24',1);
(0, 'penis','male',18000,'2017-12-02',2);
(0, 'Asia','male',17000,'2018-08-08',1);
(0, 'flowing smoothly and incessantly','female',14000,'2018-10-07',3);
(0, 'Old Four','female',15000,'2014-03-14',1);
(0, 'Yang Ge','male',3000,'2019-03-14',null);

-- See
select * from emp;
+----+------+--------+--------+------------+---------+
| id | name | gender | salary | join_date  | dept_id |
+----+------+--------+--------+------------+---------+
|  1 | Third | male     |  20000 | 2016-02-24 |       1 |
|  2 | penis | male     |  18000 | 2017-12-02 |       2 |
|  3 | Asia | male     |  17000 | 2018-08-08 |       1 |
|  4 | flowing smoothly and incessantly | female     |  14000 | 2018-10-07 |       3 |
|  5 | Old Four | female     |  15000 | 2014-03-14 |       1 |
|  6 | Yang Ge | male     |   3000 | 2019-03-14 | NULL    |
+----+------+--------+--------+------------+---------+

select * from dept;
+----+--------+
| id | name   |
+----+--------+
|  1 | Marketing Department |
|  2 | Finance Department |
|  3 | R&D Department |

Trial join and subquery

scalar subquery

-- Query highest paid employee information
-- Subquery(scalar): Find out high wages first, Then as where Conditions
select * 
from emp e
where
    e.salary = (
        select max(salary)
        from emp
    );
  
 +----+------+--------+--------+------------+---------+
| id | name | gender | salary | join_date  | dept_id |
+----+------+--------+--------+------------+---------+
|  1 | Third | male     |  20000 | 2016-02-24 |       1 |
+----+------+--------+--------+------------+---------+

Column subquery, with in judgment

-- Inquire about Marketing and R&D

-- where (emp.dept_id) IN (dept.id Medium qualified IDS)
select * 
from emp e
where e.dept_id in (
    select id
    from dept d
    where d.name in ("Marketing Department", "R&D Department")
);

+----+------+--------+--------+------------+---------+
| id | name | gender | salary | join_date  | dept_id |
+----+------+--------+--------+------------+---------+
|  1 | Third | male     |  20000 | 2016-02-24 |       1 |
|  3 | Asia | male     |  17000 | 2018-08-08 |       1 |
|  4 | flowing smoothly and incessantly | female     |  14000 | 2018-10-07 |       3 |
|  5 | Old Four | female     |  15000 | 2014-03-14 |       1 |
+----+------+--------+--------+------------+---------+

-- Schema 2 table join inner join Re-filter
select *
from (
    select e.*, d.name as "department"
    from emp e, dept d
    where e.dept_id = d.id 
 ) as a
where a.department in ("Marketing Department", "R&D Department");
    
-- Option 3 Improvement 2
select e.*, d.name as "department"
from emp e, dept d
where (
    (e.dept_id = d.id) and
    (d.name in ("Marketing Department", "Finance Department"))
);

Query specific fields, using subqueries and join s, respectively

-- Query Employees Employed After 2018 id, Full name, date, Department Name

-- Skill: Join tables first as alias, By alias where... , Last Write Query Field
select * 
from 
    emp as e
    dept d -- as Omittable
where 
    e.xxx 
    d.xxx 
    
-- Last Write the Field to Query
select e.xx, e.xx, d.xx
select e.id, e.name, e.join_date, d.name as "department"
from 
    emp e,
    dept d
where 
    e.dept_id = d.id  -- No Department counts
    and e.join_date >= "2018/1/1";
    
+----+------+------------+--------+
| id | name | join_date  | department   |
+----+------+------------+--------+
|  3 | Asia | 2018-08-08 | Marketing Department |
|  4 | flowing smoothly and incessantly | 2018-10-07 | R&D Department |
+----+------+------------+--------+

-- Scenario 2 Subquery
select e.id, e.name, e.join_date, d.name
from (
    -- Screen out ahead of time
    select * 
    from emp
    where join_date >="2018/1/1"
) as e,
dept as d
where e.dept_id = d.id;

Enhance join exercises

Preparing data

  • Departmental table dept(id, d_ame, location)
  • job(id, j_name, description)
  • Employee table emp(id, e_name, job_id, leader, join_date, salary, bonus, dept_id)
  • Salary scale salary_grade(grade, s_min, s_max)
  • Association (insist on not using foreign keys) job.id ~emp.job_id, emp.dept_id ~ dept.id
drop table if exists dept;
drop table if exists job;
drop table if exists emp;
drop table if exists salary_grade;

-- Department table
create table dept(
    id int primary key,
    d_name varchar(20),
    location varchar(30)
);

-- Job table
create table job(
    id int primary key,
    j_name varchar(20),
    description varchar(30)  
);

-- Employee Sheet
create table emp(
    id int primary key,
    e_name varchar(10),
    job_id int,
    leader varchar(10), -- Direct Leadership
    join_date date,
    salary decimal(8,2),
    bonus decimal(7,2), -- bonus
    dept_id int
);

-- Wage scale
create table salary_grade(
    grade int primary key, -- level
    s_min int, -- Lower bound
    s_max int -- upper bound
);

-- Add Test Data 
INSERT INTO dept VALUES 
(10,'R&D Department','Shenzhen'),
(20,'Product Department','Shenzhen'),
(30,'Sales Department','Guangzhou'),
(40,'Finance Department','Shanghai');

 INSERT INTO job (id, j_name, description) VALUES
 (1, 'CEO', 'Manage the entire company, receive orders'),
 (2, 'manager', 'Management staff'),
 (3, 'Sale', 'Sell goods to customers'),
 (4, 'Clerk', 'Functional Management');
 
 
 insert into salary_grade values 
 (1,7000,12000),
 (2,12010,14000),
 (3,14010,20000),
 (4,20010,30000),
 (5,30010,99990);


INSERT INTO emp VALUES 
(1001,'Sun WuKong',4,1004,'2014-12-17','8000.00',NULL,20),
(1002,'Lu Junyi',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'Lin Chong',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'Tang Monk',2,1009,'2018-04-02','29750.00',NULL,20),
(1005,'Li Kui',4,1006,'2017-09-28','12500.00','14000.00',30),
(1006,'Song River',2,1009,'2015-05-01','28500.00',NULL,30),
(1007,'Liu Bei',2,1009,'2019-09-01','24500.00',NULL,10),
(1008,'Zhu Bajie',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'Luo Guanzhong',1,NULL,'2016-11-17','50000.00',NULL,10),
(1010,'Wu Yong',3,1006,'2017-09-08','15000.00','0.00',30),
(1011,'Sand Monk',4,1004,'2012-05-23','11000.00',NULL,20),
(1012,'Li Kui',4,1006,'2008-12-03','9500.00',NULL,30),
(1013,'Little White Dragon',4,1004,'2016-12-03','30000.00',NULL,20),
(1014,'Guan Yu',4,1007,'2019-01-23','13000.00',NULL,10);

Preview a wave of test data

-- Preview a wave
mysql> select * from dept;
+----+--------+----------+
| id | d_name | location |
+----+--------+----------+
| 10 | R&D Department | Shenzhen     |
| 20 | Product Department | Shenzhen     |
| 30 | Sales Department | Guangzhou     |
| 40 | Finance Department | Shanghai     |
+----+--------+----------+

mysql> select * from job;
+----+--------+--------------------+
| id | j_name | description        |
+----+--------+--------------------+
|  1 | CEO    | Manage the entire company, receive orders |
|  2 | manager   | Management staff       |
|  3 | Sale   | Sell goods to customers     |
|  4 | Clerk   | Functional Management           |
+----+--------+--------------------+

mysql> select * from salary_grade;
+-------+-------+-------+
| grade | s_min | s_max |
+-------+-------+-------+
|     1 |  7000 | 12000 |
|     2 | 12010 | 14000 |
|     3 | 14010 | 20000 |
|     4 | 20010 | 30000 |
|     5 | 30010 | 99990 |
+-------+-------+-------+

mysql> select * from emp limit 5;
+------+--------+--------+--------+------------+----------+----------+---------+
| id   | e_name | job_id | leader | join_date  | salary   | bonus    | dept_id |
+------+--------+--------+--------+------------+----------+----------+---------+
| 1001 | Sun WuKong |      4 | 1004   | 2014-12-17 | 8000.00  | NULL     |      20 |
| 1002 | Lu Junyi |      3 | 1006   | 2001-02-20 | 16000.00 | 3000.00  |      30 |
| 1003 | Lin Chong   |      3 | 1006   | 2001-02-22 | 12500.00 | 5000.00  |      30 |
| 1004 | Tang Monk   |      2 | 1009   | 2018-04-02 | 29750.00 | NULL     |      20 |
| 1005 | Li Kui   |      4 | 1006   | 2017-09-28 | 12500.00 | 14000.00 |      30 |
+------+--------+--------+--------+------------+----------+----------+---------+

case1: (2 tables) Query employee information, including employee number, name, salary, position name, job description

  • Process: comb the tables and fields involved; join conditions of tables
  • Tips: from > where > group by and aggregate function > having > order > select

Analysis:

  • emp table: id, e_name, salary
  • job table: j_name, description
  • Connection condition: left join or inner join, emp.job_id = jop.id
-- use left join, peacetime excel Of vlookup That's how it works
select *
from emp e
    left join job d
    on e.job_id = d.id;
select 
    e.id,
    e.e_name,
    e.salary,
    d.j_name,
    d.description
from emp e
    left join job d
    on e.job_id = d.id;

-- out
+------+--------+----------+--------+--------------------+
| id   | e_name | salary   | j_name | description        |
+------+--------+----------+--------+--------------------+
| 1009 | Luo Guanzhong | 50000.00 | CEO    | Manage the entire company, receive orders |
| 1004 | Tang Monk   | 29750.00 | manager   | Management staff       |
| 1006 | Song River   | 28500.00 | manager   | Management staff       |
| 1007 | Liu Bei   | 24500.00 | manager   | Management staff       |
| 1002 | Lu Junyi | 16000.00 | Sale   | Sell goods to customers     |
| 1003 | Lin Chong   | 12500.00 | Sale   | Sell goods to customers     |
| 1010 | Wu Yong   | 15000.00 | Sale   | Sell goods to customers     |
| 1001 | Sun WuKong | 8000.00  | Clerk   | Functional Management           |
| 1005 | Li Kui   | 12500.00 | Clerk   | Functional Management           |
| 1008 | Zhu Bajie | 30000.00 | Clerk   | Functional Management           |
| 1011 | Sand Monk   | 11000.00 | Clerk   | Functional Management           |
| 1012 | Li Kui   | 9500.00  | Clerk   | Functional Management           |
| 1013 | Little White Dragon | 30000.00 | Clerk   | Functional Management           |
| 1014 | Guan Yu   | 13000.00 | Clerk   | Functional Management           |
+------+--------+----------+--------+--------------------+

case2: (3 tables) Query employee number, name, salary, position, Department name, Department location

  • emp table: id, e_name, salary
  • job table: j_name
  • dept table: d_name, location
  • Connection: Left connection, emp.job_id = job.id, emp.dept_id = dept.id
-- I usually check it out first, Do it again where
select * 
from emp e
    left join job j
    on e.job_id = j.id
    left join dept d  -- Continue to connect
    on e.dept_id = d.id;
select
    e.id,
    e.e_name,
    e.salary,
    j.j_name,
    -- d.d_name,
    d.location
from emp e
    left join job j
    on e.job_id = j.id
    -- Continue Connecting...
    left join dept d
    on e.dept_id = d.id
limit 3;

+------+--------+----------+--------+----------+
| id   | e_name | salary   | j_name | location |
+------+--------+----------+--------+----------+
| 1009 | Luo Guanzhong | 50000.00 | CEO    | Shenzhen     |
| 1004 | Tang Monk   | 29750.00 | manager   | Shenzhen     |
| 1006 | Song River   | 28500.00 | manager   | Guangzhou     |
+------+--------+----------+--------+----------+

case3: Query employee name, salary, salary level (between....and, criteria for Join)

Analysis

  • emp table: e_name, salary
  • salary_grade table: grade
  • Connection condition: JOIN, e.salary BETWEEN s.s_min AND s.s_max
select 
    e.e_name,
    e.salary as "salary",
    s.grade as "pay grade"
from emp e
    join
    salary_grade s
    on e.salary between s.s_min and s.s_max
limit 5;
    
+--------+----------+----------+
| e_name | salary     | pay grade |
+--------+----------+----------+
| Sun WuKong | 8000.00  |        1 |
| Lu Junyi | 16000.00 |        3 |
| Lin Chong   | 12500.00 |        2 |
| Tang Monk   | 29750.00 |        4 |
| Li Kui   | 12500.00 |        2 |
+--------+----------+----------+
5 rows in set (0.11 sec)

case4: (4 tables) Query employee name, salary, position, department, Department location, salary level

Analysis

  • emp table: e_name, salary
  • job table: j_name
  • dept table: d_name, location
  • salary_grade table: grade
  • Connection conditions e, j, d, s:
    • e.job_id = j.id
    • e.dept_id = d.id
    • e.salary BETWEEN s.s_min AND s.s_max
select *
from emp e             
    left join job j    -- Job List
    ON e.job_id = j.id
    left join dept d   -- Department table
    ON e.dept_id = d.id
    join salary_grade s -- PayLevels
    ON e.salary between s.s_min and s.s_max
    
limit 1;
    

left stitches together a super large table

+------+--------+--------+--------+------------+----------+---------+---------+----+--------+----------------+----+--------+----------+-------+-------+-------+
| id   | e_name | job_id | leader | join_date  | salary   | bonus   | dept_id | id | j_name | description    | id | d_name | location | grade | s_min | s_max |
+------+--------+--------+--------+------------+----------+---------+---------+----+--------+----------------+----+--------+----------+-------+-------+-------+
| 1001 | Sun WuKong |      4 | 1004   | 2014-12-17 | 8000.00  | NULL    |      20 |  4 | Clerk   | Functional Management       | 20 | Product Department | Shenzhen     |     1 |  7000 | 12000 |
select 
    e.e_name, 
    e.salary,
    j.j_name,
    d.d_name,
    d.location,
    s.grade
from emp e             
    left join job j    -- Job List
    ON e.job_id = j.id
    left join dept d   -- Department table
    ON e.dept_id = d.id
    join salary_grade s -- PayLevels
    ON e.salary between s.s_min and s.s_max
    
limit 5;
    
+--------+----------+--------+--------+----------+-------+
| e_name | salary   | j_name | d_name | location | grade |
+--------+----------+--------+--------+----------+-------+
| Sun WuKong | 8000.00  | Clerk   | Product Department | Shenzhen     |     1 |
| Lu Junyi | 16000.00 | Sale   | Sales Department | Guangzhou     |     3 |
| Lin Chong   | 12500.00 | Sale   | Sales Department | Guangzhou     |     2 |
| Tang Monk   | 29750.00 | manager   | Product Department | Shenzhen     |     4 |
| Li Kui   | 12500.00 | Clerk   | Sales Department | Guangzhou     |     2 |
+--------+----------+--------+--------+----------+-------+

case5: Query Department number, name, location, number of people, join group by, aggregate function, etc.

Analysis: Connect first, then group by department + aggregate

  • dept table: id, d_name, location
  • emp table: id (for count)
  • Connection condition: make a connection dept.id = emp.dept_id
select 
    d.id,
    d.d_name,
    d.location,
    -- No count(*) null Don't join the calculation
    count(e.id) as "Number of people"
from dept d
    left join emp e
    on d.id = e.dept_id
-- Group by Department
group by d.d_name;

+----+--------+----------+------+
| id | d_name | location | Number of people |
+----+--------+----------+------+
| 20 | Product Department | Shenzhen     |    5 |
| 10 | R&D Department | Shenzhen     |    3 |
| 40 | Finance Department | Shanghai     |    0 |
| 30 | Sales Department | Guangzhou     |    6 |
+----+--------+----------+------+

case6: (self-connect) Query the name of an employee and his or her leader

Analysis: Alias through as for self-join (id field and leader field are self-related)

  • emp table e1: e_name
  • emp table e2: e.leader
  • Connection condition: left connection, e1.leader = e2.id
select 
    e1.e_name as "Employee Name",
    e1.e_name as "Direct Leadership"
from emp as e1
    left join emp as e2
    on e1.leader = e2.id;
    
limit 5;
    
+----------+----------+
| Employee Name | Direct Leadership |
+----------+----------+
| Sun WuKong   | Sun WuKong   |
| Lu Junyi   | Lu Junyi   |
| Lin Chong     | Lin Chong     |
| Tang Monk     | Tang Monk     |
| Li Kui     | Li Kui     |
+----------+----------+
14 rows in set (0.11 sec)

summary

  • Table join mode I use frequency left join > inner join
  • Join field on equivalents or criteria are acceptable
  • The key to joining multiple tables is to analyze which tables and their fields are needed, determine how they are joined, and whether they need to be nested or aggregated...
  • Technically, try to write standardized sql statements for easy reading, from > where > group by and aggregate function > having > order > select
  • Next, add a wave of self-connection (provincial-urban linkage or fan interaction) and union, group by +to aggregate these...

Tags: MySQL Database Excel SQL

Posted on Sat, 09 Nov 2019 14:51:51 -0500 by invincible_virus