Understand MySQL multi table query in one article

Posted by claypots on Tue, 07 Dec 2021 16:43:24 +0100

1. Relationship between tables

one-on-one
Monogamy
One to many
One person can own more than one car
Create person table

CREATE TABLE person(id int PRIMARY KEY auto_increment,name VARCHAR(50));

Create car table

CREATE TABLE car ( cid INT PRIMARY KEY , 
cname VARCHAR ( 50 ),
color VARCHAR(25),
pid INT,
CONSTRAINT c_p_fk FOREIGN key(pid)  REFERENCES person(id)
);

Many to many
A teacher can correspond to multiple students, and a student can correspond to multiple teachers

Create teacher table

CREATE TABLE teacher(
tid int PRIMARY KEY auto_increment,
name VARCHAR(50),
age int,
gender char(1) DEFAULT 'male');

Create student table

CREATE TABLE student(
sid int PRIMARY KEY auto_increment,
name VARCHAR(50)not null,
age int,
gender char(1) DEFAULT 'male');

Create a relationship table between students and teachers

CREATE TABLE tea_stu_rel(
tid int,
sid int);

Add foreign key
Associate tid with teacher table and sid with student table

ALTER TABLE tea_stu_rel 
add CONSTRAINT fk_tid FOREIGN key(tid) REFERENCES teacher(tid);

ALTER TABLE tea_stu_rel add CONSTRAINT fk_sid FOREIGN key(sid)
  REFERENCES student(sid);

Create student score table

CREATE TABLE score (
id int(8) auto_increment PRIMARY key,
socre int(8),km VARCHAR(50),sid int(8)
);

Why split a table?

  • Avoid large amounts of redundant data

2. Multi table query

Merge result set
Merging the result set is to merge the query results of two select statements together

Two ways to merge result sets

  • UNION: remove duplicate records during merging
  • UNION ALL: duplicate records are not removed during merging
SELECT * FROM Table 1 UNION SELECT *FROM Table 2;
SELECT * FROM Table 1 UNION ALL SELECT* FROM Table 2;

matters needing attention
The two results to be merged: the number of columns and the type of columns must be the same.

Example

3. Connection query

What is a join query?
It can also be called cross table query. You need to associate multiple tables for query

3.1 Cartesian set

What is Cartesian set?

  • Suppose set A={a,b}, set B={0,1,2}
    Then the Cartesian product of two sets is {(a,0),(a,1),(a.2),(b,0),(b,1),(b,2)}.
    It can be extended to multiple sets
  • When you query two tables at the same time, the result of Cartesian set appears
SELECT * FROM teacher,student;


Remove Cartesian set

Keep the primary key consistent with the foreign key when querying

SELECT * FROM student st,score sc where st.sid=sc.sid;

The data in the main table refers to the data in the sub table
Its principle is to judge line by line, leave the equal and don't want the unequal at all

3.2. Query by connection type

3.2.1 internal connection

Equivalent connection

-- INNER It can be omitted
SELECT * FROM student st INNER JOIN score sc on st.sid = sc.sid;
  • The id number (value) of two tables appears at the same time
  • It is the same as the main foreign key of multi table associated query constraint, but the writing method has changed
  • Write only the main foreign key after ON
  • If there are still conditions, write where directly after it
  • If there are conditions after multi table associated query, write and directly

Multi table connection

Create chart of accounts and insert some data

CREATE TABLE course(cid int PRIMARY KEY,name VARCHAR(50));

Use 99 connection method

SELECT st.name,sc.socre,c.`name` FROM student st,score sc,course c
where st.sid=sc.sid and sc.sid=c.cid;

Using inline queries

SELECT st.`name`,sc.socre,c.`name` FROM student st 
JOIN score sc on st.sid=sc.sid
JOIN course c on sc.sid=c.cid;

Non equivalent connection

Prepare some tables first
Employee table

CREATE TABLE `emp` (
  `empno` int(11) NOT NULL,
  `ename` varchar(255) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `mgr` varchar(255) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `comm` double DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Salary scale (800 at level 1)

CREATE TABLE `salgrade` (
  `grade` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'Wage scale',
  `lowSalary` int(11) DEFAULT NULL COMMENT 'Minimum wage at this level',
  `highSalary` int(11) DEFAULT NULL COMMENT 'Maximum wage at this level',
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

Department table

CREATE TABLE `dept` (
  `deptno` bigint(2) NOT NULL AUTO_INCREMENT COMMENT 'Indicates the department number, which is composed of two digits',
  `dname` varchar(14) DEFAULT NULL COMMENT 'Department name, consisting of up to 14 characters',
  `local` varchar(13) DEFAULT NULL COMMENT 'Location of Department',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

insert data

INSERT INTO `emp` VALUES (7369, 'Sun WuKong', 'staff member', '7902', '2010-12-17', 800, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'Sun Shangxiang', 'salesman', '7698', '2011-2-20', 1600, 300, 30);
INSERT INTO `emp` VALUES (7521, 'Li Bai', 'salesman', '7698', '2011-2-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, 'Cheng Yaojin', 'manager', '7839', '2011-4-2', 2975, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'Daji', 'salesman', '7698', '2011-9-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, 'King Lanling', 'manager', '7839', '2011-5-1', 2854, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'Yu Ji', 'manager', '7839', '2011-6-9', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'Xiang Yu', 'inspector', '7566', '2017-4-19', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'Fei Zhang', 'CEO', NULL, '2010-6-12', 5000, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'Cai Wenji', 'salesman', '7698', '2011-9-8', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, 'Acor ', 'staff member', '7788', '2017-5-23', 1100, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'Liu Bei', 'staff member', '7698', '2011-12-3', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'Zhuge Liang', 'inspector', '7566', '2011-12-3', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'Luban', 'staff member', '7782', '2012-1-23', 1300, NULL, 10);

INSERT INTO `dept` VALUES (10, 'Finance Department', 'Beijing');
INSERT INTO `dept` VALUES (20, 'Research Department', 'Shanghai');
INSERT INTO `dept` VALUES (30, 'Sales Department', 'King Canyon');
INSERT INTO `dept` VALUES (40, 'Operation Department', 'Tencent building');

INSERT INTO `salgrade` VALUES (1, 700, 1200);
INSERT INTO `salgrade` VALUES (2, 1201, 1400);
INSERT INTO `salgrade` VALUES (3, 1401, 2000);
INSERT INTO `salgrade` VALUES (4, 2001, 3000);
INSERT INTO `salgrade` VALUES (5, 3001, 9999);

Example: query the name, salary, department name and salary grade of all employees

Problem solving ideas: divided into three steps

  1. Query the names and wages of all employees
  2. Query all employees' names, salaries and all departments
  3. Query the name, salary, department and of all employees

3.2.2 external connection

Left outer connection (left connection)

  • The left connection will find all the data in the left table and only the data meeting the conditions in the right table
  • For example, the left table is the student table, and the other table is the score table. If a student is absent from the exam, only the students with scores will be queried by using the inner connection, and the absent students can also be found by using the outer connection.
  • When querying, two tables do not need to establish foreign key constraints

Keywords: left joining

-- OUTER It can be omitted
SELECT * FROM student st LEFT OUTER JOIN score sc on st.sid = sc.sid;


Right outer connection (right connection)

  • The right connection will find all the data in the right table, and only the data meeting the conditions in the left table
  • From the perspective of the table, use the left connection to find out all the contents in the table on the left, and find out those that meet the conditions on the right. Use the right connection to find out all the data in the table on the right. Find the one on the left that meets the conditions.

3.2.3 natural connection

  • Join queries produce useless Cartesian sets, which we usually divide by using the main foreign key relationship equation
  • Natural connection doesn't need you to give the main foreign key equation. It will automatically find this equation, that is, it doesn't need to write conditions

requirement:

  • In two connected tables, columns with exactly the same column names and types are used as conditions
  • The same columns are removed

Example: query all information of student table and score table

-- We needed to write this
SELECT * from student,score WHERE student.sid=score.sid;

You can find that the query result has two SIDS

-- Natural connection only needs to be written like this
SELECT * FROM student NATURAL JOIN score;

Natural join removes duplicate columns

Topics: Database MySQL

Hot Topics

©2024 Programmer Think