MySQL multi table query

Time:2021-1-11
  • Query syntax:
    select
    List of column names
    from
    Table name list
    where….

    • Prepare SQL

      Create department table

      CREATE TABLE dept(
      id INT PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(20)
      );
      Insert into Dept (name) values (‘development department ‘), (‘marketing department’), (‘finance department ‘);

      Create employee table

      CREATE TABLE emp (
      id INT PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(10),
      Gender char (1), — gender
      Salary double
      join_ Date date
      dept_id INT,
      FOREIGN KEY (dept_ ID) references Dept (ID) — foreign key, associated department table (primary key of department table)
      );
      INSERT INTO emp(NAME,gender,salary,join_ date,dept_ ID) values (‘monkey King ‘,’male’, 7200, ‘2013-02-24’, 1);
      INSERT INTO emp(NAME,gender,salary,join_ date,dept_ ID) values (‘zhubajie ‘,’ male ‘, 3600,’ 2010-12-02 ‘, 2);
      INSERT INTO emp(NAME,gender,salary,join_ date,dept_ ID) values (‘tang Seng ‘,’male’, 9000, ‘2008-08-08’, 2);
      INSERT INTO emp(NAME,gender,salary,join_ date,dept_ ID) values (‘baigujing ‘,’female’, 5000, ‘2015-10-07’, 3);
      INSERT INTO emp(NAME,gender,salary,join_ date,dept_ ID) values (‘spider sperm ‘,’ female ‘, 4500,’ 2011-03-14 ‘, 1);

    • Cartesian product:

      • There are two sets a and B. take all the composition of these two sets.
      • In order to complete multi table query, we need to eliminate useless data
    • Classification of multi table queries:

      1. Internal connection query:
        1. Implicit join: using where condition to eliminate useless data

          • example:
            –Query all employee information and corresponding department information

          SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

          –Query the name and gender of the employee table. The name of the Department table
          SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

          SELECT
          T1. Name, — name of employee table
          T1. Gender, – gender of employee table
          T2. Name — name of department table
          FROM
          emp t1,
          dept t2
          WHERE
          t1.dept_id = t2.id;

        2. Explicit inner join:

          • Syntax: select field list from table name 1 [inner] join table name 2 on condition
          • For example:
            • SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
            • SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;
        3. Internal connection query:

          1. From which tables do you query data
          2. What are the conditions
          3. Which fields to query
      2. External link query:
        1. Left external connection:
          • Syntax: select field list from table 1 left [outer] join table 2 on condition;
          • All the data in the left table and its intersection are queried.
          • example:
            –Query all employee information. If an employee has a department, the Department name will be queried. If there is no department, the Department name will not be displayed
            SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;
        2. Right outer connection:
          • Syntax: select field list from table 1 right [outer] join table 2 on condition;
          • All the data in the right table and its intersection are queried.
          • example:
            SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;
      3. Subquery:
        • Concept: a nested query in a query is called a subquery.
          –Query the highest paid employee information
          –How much is the highest salary
          SELECT MAX(salary) FROM emp;

          –2 query employee information, and the salary is equal to 9000
          SELECT * FROM emp WHERE emp.salary = 9000;

          –This operation is completed by one SQL. Subquery
          SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

        • Different situations of subquery

          1. The result of the subquery is single row and single column

            • Subquery can be used as a condition to judge by operator. Operator: > > = < ==

            –Query the person whose salary is less than the average salary
            SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

          2. The result of subquery is multi row and single column

            • The subquery can be used as a condition and the operator in can be used to judge
              –Check all the staff information of finance department and marketing department
              Select id from dept where name =’finance department ‘or name =’marketing department’;
              SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
              –Subquery
              SELECT * FROM emp WHERE dept_ ID in (select id from dept where name =’finance department ‘or name =’marketing department’);
          3. The result of subquery is multi row and multi column

            • Subquery can be used as a virtual table
              –Query the employee information and department information of employees whose employment date is after November 11, 2011
              –Subquery
              SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > ‘2011-11-11’) t2
              WHERE t1.id = t2.dept_id;

            –Common internal connection
            SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > ‘2011-11-11’

      • Multi table query exercise

        --Department table
          CREATE TABLE dept (
            Id int primary key primary key, - Department ID
            DName varchar (50), -- department name
            LOC varchar (50) - Department location
          );
          
          --Add 4 departments
          INSERT INTO dept(id,dname,loc) VALUES 
          (10, 'teaching and research department', 'Beijing'),
          (20, Ministry of education and industry, Shanghai),
          (30, 'sales department', 'Guangzhou'),
          (40, 'finance department', 'Shenzhen');
          
          
          
          --Job list, job name, job description
          CREATE TABLE job (
            id INT PRIMARY KEY,
            jname VARCHAR(20),
            description VARCHAR(50)
          );
          
          --Add 4 jobs
          INSERT INTO job (id, jname, description) VALUES
          (1) 'chairman','manage the whole company, take orders'),
          (2,'manager ','Management staff'),
          (3) salesmen, selling products to customers),
          (4,'clerks','using office software ');
          
          
          
          --Employee list
          CREATE TABLE emp (
            Id int primary key, - employee ID
            Ename varchar (50), -- employee name
            job_ Id int, - Job ID
            Mgr int, - superior leader
            Join date
            Salary decimal (7,2), -- salary
            Bonus decimal (7,2), -- bonus
            dept_ Id int, -- department number
            CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
            CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
          );
          
          --Add employee
          INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
          (1001, 'Monkey King', 41004, '2000-12-17','8000.00 ', null, 20),
          (1002, 'Lu Junyi', 31006, '2001-02-20', '16000.00', '3000.00', 30),
          (1003, 'Lin Chong', 31006, '2001-02-22', '12500.00', '5000.00', 30),
          (1004, 'Tang Seng', 21009, '2001-04-02','29750.00 ', null, 20),
          (1005, 'Li Kui', 41006, '2001-09-28', '12500.00', '14000.00', 30),
          (1006, 'Songjiang', 21009, '2001-05-01','28500.00 ', null, 30),
          (1007, 'Liu Bei', 21009, '2001-09-01','24500.00 ', null, 10),
          (1008, 'zhubajie', 41004, '2007-04-19','30000.00 ', null, 20),
          (1009, 'Luo Guanzhong', 1, null, '2001-11-17', '50000.00', null, 10),
          (1010, 'Wu Yong', 31006, '2001-09-08', '15000.00', '0.00', 30),
          (1011, 'Shaseng', 41004, '2007-05-23','11000.00 ', null, 20),
          (1012, 'Li Kui', 41006, '2001-12-03', '9500.00', null, 30),
          (1013, 'xiaobailong', 41004, '2001-12-03', '30000.00', null, 20),
          (1014, 'Guan Yu', 41007, '2002-01-23','13000.00 ', null, 10));
          
          
          
          --Wage scale
          CREATE TABLE salarygrade (
            Grade int primary key, -- level
            Losalary int, - minimum wage
            Hisalary int - maximum wage
          );
          
          --Add 5 salary scales
          INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
          (1,7000,12000),
          (2,12010,14000),
          (3,14010,20000),
          (4,20010,30000),
          (5,30010,99990);
          
          --Demand:
          
          --1. Query all employee information. Query employee number, employee name, salary, job name, job description
          /*
          	analysis:
          		1. Employee number, employee name, salary, EMP table, job name and job description need to be queried
          		2. Query conditions emp.job_ id =  job.id
          
          */
          SELECT 
          	T1. ` ID ', - employee number
          	T1. ` ename ', - employee name
          	T 1. Salary
          	T2. ` jname ', - job title
          	T2. ` description '-- job description
          FROM 
          	emp t1, job t2
          WHERE 
          	t1.`job_id` = t2.`id`;
          
          
          
          --2. Query employee number, employee name, salary, job name, job description, department name and department location
          /*
          	analysis:
          		1. Employee number, employee name, salary EMP, job name, job description, job department name, Department location Dept
          		2. Conditions: emp.job_ id =  job.id  and  emp.dept_ id =  dept.id
          */
          
          SELECT 
          	T1. ` ID ', - employee number
          	T1. ` ename ', - employee name
          	T 1. Salary
          	T2. ` jname ', - job title
          	T2. ` description ', - job description
          	T3. ` dName ', - Department name
          	T3. ` LOC '- Department location
          FROM 
          	emp t1, job t2,dept t3
          WHERE 
          	t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;
             
          --3. Inquire about employee's name, salary and salary grade
          /*
          	analysis:
          		1. Employee name, salary EMP, salary grade
          		2. Conditions emp.salary  > = salarygrade.losalary  and  emp.salary  < = salarygrade.hisalary
          			emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
          */
          SELECT 
          	t1.ename ,
          	t1.`salary`,
          	t2.*
          FROM emp t1, salarygrade t2
          WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;
          
          
          
          --4. Query employee's name, salary, job name, job description, department name, Department location and salary grade
          /*
          	analysis:
          		1. Employee name, salary EMP, job name, job description, job department name, Department location, Dept, salary grade
          		2. Conditions: emp.job_ id =  job.id  and  emp.dept_ id =  dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
          			
          */
          SELECT 
          	t1.`ename`,
          	t1.`salary`,
          	t2.`jname`,
          	t2.`description`,
          	t3.`dname`,
          	t3.`loc`,
          	t4.`grade`
          FROM 
          	emp t1,job t2,dept t3,salarygrade t4
          WHERE 
          	t1.`job_id` = t2.`id` 
          	AND t1.`dept_id` = t3.`id`
          	AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;
          
          
          
          --5. Find out the department number, department name, Department location and department number
          
          /*
          	analysis:
          		1. Dept table of department number, department name and department location. Department number EMP table
          		2. Use group query. according to emp.dept_ ID complete grouping, query count (ID)
          		3. Use subquery to associate the query result of step 2 with the dept table
          		
          */
          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. Query the names of all employees and their direct superiors. Employees without leaders also need to query
          
          /*
          	analysis:
          		1. Name EMP, the name of the immediate superior EMP
          			*The ID and Mgr of EMP table are self related
          		2. Conditions emp.id  = emp.mgr
          		3. Query all data in the left table and intersection data
          			*Query using left outer join
          	
          */
          /*
          select
          	t1.ename,
          	t1.mgr,
          	t2.`id`,
          	t2.ename
          from emp t1, emp t2
          where t1.mgr = t2.`id`;
          
          */
          
          SELECT 
          	t1.ename,
          	t1.mgr,
          	t2.`id`,
          	t2.`ename`
          FROM emp t1
          LEFT JOIN emp t2
          ON t1.`mgr` = t2.`id`;

Recommended Today

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

Hello, I’m younger brother. A few days ago, I shared the second interview question, the interview site of search engine in MySQL. This question is the interview at normal temperature. After reading it, I’m sure you will gain something in terms of database engine If you haven’t read my first share, you can refer to […]