Database MySQL – multi-table query

Directory

1. Multi-table relationship

1. One-to-many (many-to-one) relationship

2. Many-to-many relationship

3. One-to-one relationship

2. Overview of multi-table query

1 Overview

2. Classification

3. Inner connection

4. Outer connection

5. Self-connection

6. Joint query-union

Seven, sub query

1. Scalar subqueries

2. Column query

3. Row subqueries

4. Table subquery

1. Multi-table relationship

In project development, when designing the data table structure, the table structure will be analyzed and designed according to the business requirements and the relationship between the business sectors. Since the businesses are related to each other, there are also various table structures. There are basically three types of connections:

  1. One-to-many (many-to-one)
  2. many to many
  3. one to one

1. One-to-many (many-to-one) relationship

  • Case: the relationship between departments and employees
  • Relationship: One department corresponds to multiple employees, and one employee corresponds to one department
  • Realization: Create a foreign key on the many side, pointing to the primary key on the one side

2. Many-to-many relationship

  • Case: the relationship between students and courses
  • Relationship: A student can take multiple courses, and a course can also provide multiple student choices
  • Implementation: Create a third intermediate table, the intermediate table contains at least two foreign keys, which are associated with the primary keys of the two parties

create table student_course(
    id int auto_increment comment 'primary key' primary key ,
    studentid int not null comment 'student id',
    courseid int not null comment 'course id',
    constraint fk_courseid foreign key (courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references student(id)
)comment 'Student Course Intermediate Table';

3. One-to-one relationship

  • Case: the relationship between users and user details
  • Relationship: One-to-one relationship, mostly used for single-label splitting, put the basic fields of one table in one table, and put other detailed fields in another table to improve operational efficiency
  • Realization: Add a foreign key to any party, associate the primary key of the other party, and set the foreign key to be unique (UNIQUE)

2. Overview of multi-table query

1. Overview

  • Overview: Refers to querying data from multiple tables
  • Cartesian product: Cartesian product refers to all combinations of two sets A and B in mathematics. (In multi-table query, invalid Cartesian products need to be eliminated)
-- single table query
select * from emp;
-- Multi-table query
select * from emp,dept;
-- The query result is all combinations of the two tables

-- Eliminate Cartesian product
-- Add where condition
select * from emp,dept where emp.dept_id = dept.id;

2. Category

① Connection query

  • Inner join: It is equivalent to querying the intersection data of two tables A and B
  • Outer join:
    left outer connection Query all the data in the left table and the intersection of the two tables
    Right outer join Query all the data in the right table, and the intersection data of the two tables

  • Self-join: the connection query between the current table and itself, the self-join must use the table alias

② Sub link

3. Inner link

Inner join: It is equivalent to querying the intersection data of two tables A and B.

Inner join query syntax:

  • Implicit inner join: SELECT field list FROM table 1, table 2 WHERE condition… ;
  • Explicit inner connection: SELECT field list FROM table 1 [ INNER ] JOIN table 2 ON connection condition….;
-- inner link display
-- Table structure: emp, dept
-- Join condition: emp.dept_id = dept.id

-- 1. Query the name of each employee and the associated department name (implicit inner join)
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
-- indicate an alias
select e.name,d.name from emp e,dept d where e.dept_id = d.id;
-- 2. Query the name of each employee and the name of the management department (explicit inner connection)
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;
-- The inner keyword can be omitted
select e.name,d.name from emp e join dept d on e.dept_id = d.id;

4. Outer join

left Outer connection
  1. Based on the left table, match the data in the right table, and if it matches, display the matched data.
  2. If no match is found, the data in the left table will be displayed normally, and the data in the right table will be displayed as NULL.
right outer connection
  1. The right table is used as the benchmark, and the data in the left table is matched. If it matches, the matched data is displayed.
  2. If no match is found, the data in the right table will be displayed normally, and the data in the left table will be displayed as NULL.

Outer join query syntax:

  • Left outer join: SELECT field list FROM table 1 LEFT [ OUTER ] JOIN table 2 ON condition… ;
  • Right outer join: SELECT field list FROM table 1 RIGHT [ OUTER ] JOIN table 2 ON condition… ;
-- outer connection display
-- 1. Query all the data in the emp table and the corresponding department information (left outer join)
select e.*,d.name from emp e left join dept d on d.id = e.dept_id;
-- 2. Query all the information in the dept table and the corresponding employee information (right outer join)
select d.*,e.name from dept d right join emp e on d.id = e.dept_id;

5. Self-join

A self-join query can be an inner join query or an outer join query.

Self-join query syntax: SELECT field list FROM table A alias A JOIN table A alias B ON condition… ;

-- self-connection display
-- 1. Query the names of employees and their leaders
-- Table structure: emp
-- An alias must be used, otherwise the primary and secondary tables cannot be distinguished
select a.name,b.name from emp a,emp b where a.managerid = b.id;

-- 2. Query all employees emp and their leader's name emp. If the employee has no leader, it also needs to be queried (outer left)
-- Table structure: emp a, emp b
select a.name 'employee',b.name 'leadership' from emp a left join emp b on a.managerid = b.id;

6. Joint query-union

For union queries, the results of multiple queries are combined to form a new query result set.

Syntax:

SELECT field list FROM table A …

UNION [ ALL ]

SELECT field list FROM table B … ;

-- union , union all
-- 1. Query all employees whose salary is less than 5000 and employees whose age is greater than 50
-- union merge to remove duplicates
-- union all merge directly
select * from emp where salary < 5000
union
select * from emp where age > 50;

Note:

  • For the joint query, the number of columns and field types of multiple tables must be consistent.
  • union all will directly merge all the data together, and union will deduplicate the merged data.

7. Subquery

Concept: nested SELECT statements in SQL statements become nested queries, also known as subqueries.

Syntax: SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

The syntax outside the subquery can be any of INSERT / UPDATE / DELETE / SELECT .

① According to the subquery structure, it is divided into:

  • Scalar subquery (subquery result is a single value)
  • Column subquery (the result of the subquery is a column)
  • Row subquery (subquery result is one row)
  • Table subquery (the result of the subquery is multiple rows and multiple columns)

② According to the position of the subquery, it is divided into: after WHERE, after FROM, after SELECT

1. Scalar subquery

  • A subquery returns a single value (number, string, date, etc.), and in its simplest form, such a subquery is called a scalar subquery.
  • Commonly used operators: = <> > >= < <=
-- scalar subquery
-- 1. Query all employee information of "Sales Department"
-- a. Query the department id of "Sales Department"
select id from dept where name = 'Sales Department';
-- b. Query employee information according to the department id of "Sales Department"
select * from emp where dept_id = 4;
-- integration
select * from emp where dept_id = (select id from dept where name = 'sales department');

-- 2. Query the employee information after "Fang Dongbai" joined the job
-- a. Query the entry date of "Landlord Bai"
select entrydate from emp where name = 'Fang Dongbai';
-- b. Query the information of members who joined after the specified date
select * from emp where entrydate > '20090-02-12';
-- integration
select * from emp where entrydate > (select entrydate from emp where name = 'Fang Dongbai');

2. Column subquery

  • The result returned by a subquery is a column (can be multiple rows), this subquery is called a column subquery.
  • Common operators: IN, NOT IN, ANY, SOME, ALL
    Operation character description
    IN In the specified collection range, choose one more
    NOT IN Not within the specified collection range
    ANY In the list returned by the subquery, any one is sufficient
    SOME It is the same as ANY, ANY can be used wherever SOME is used
    ALL All values in the list returned by the subquery must satisfy 
    -- column subquery
-- 1. Query all employee information of "Sales Department" and "Marketing Department"
-- a. Query the department ids of "Sales Department" and "Marketing Department"
select id from dept where name = 'Sales Department' or name = 'Marketing Department';
-- b. Query employee information according to department id
select * from emp where dept_id in (2,4);
-- integration
select * from emp where dept_id in (select id from dept where name = 'sales department' or name = 'marketing department');
-- 2. Query information about employees whose salary is higher than that of all employees in the finance department
-- a. Query the staff salary of "Finance Department"
select id from dept where name = 'Finance Department';
select salary from emp where dept_id = (select id from dept where name = 'Finance Department');
-- b. Query employees with higher salaries than all employees in the financial department
select * from emp where salary > all ();
-- integration
select * from emp where salary > all
(select salary from emp where dept_id = (select id from dept where name = 'Finance Department'));

-- 3. Query information about employees whose salary is higher than any one in the research department
-- a. Query "R&D Department" personnel salary
select salary from emp where dept_id = (select id from dept where name = 'R & D department');
-- b. Query employees whose salary is higher than any one of them in the research department
select * from emp where salary > any
(select salary from emp where dept_id = (select id from dept where name = 'R & D department'));

3. Row subquery

  • The result returned by a subquery is a row (can be multiple columns), this subquery is called a row subquery.
  • Commonly used operators: = , <> , IN , NOT IN
-- row subquery
-- 1. Query the same employee information as "Zhang Wuji" with the same salary and immediate leadership
-- a. Inquire about the salary and immediate leadership of "Zhang Wuji"
select salary,managerid from emp where name = 'Zhang Wuji';
-- b. Query the same employee information as "Zhang Wuji"'s salary and direct leadership
select * from emp where salary = 12500 and managerid = 1;
-- integration
select * from emp where (salary,managerid) = (select * from emp where salary = 12500 and managerid = 1);

4. Table subquery

  • The result returned by a subquery is multiple rows and multiple columns. This kind of subquery is called a table subquery.
  • Commonly used operators: IN
-- table subquery
-- 1. Query the employee information with the same position and salary as "Luzhangke" and "Song Yuanqiao"
-- a. Query the position and salary of "Luzhangke" and "Song Yuanqiao"
select job,salary from emp where name = 'Luzhangke' or name = 'Song Yuanqiao';
-- b. Query the employee information with the same position and salary as "Luzhangke" and "Song Yuanqiao"
select * from emp where (job,salary) in (select job,salary from emp where name = 'Luzhangke' or name = 'Song Yuanqiao');
-- 2. Query the employee information whose entry date is after "2006-01-01" and its department information
-- a. Query employee information whose entry date is after "2006-01-01"
select * from emp where entrydate > '2006-01-01';
-- b. Query the corresponding department information
select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = e.id;

The knowledge points of the article match the official knowledge files, and you can further learn related knowledge MySQL entry skill tree Database composition Table 44974 people are studying systematically