Make writing a habit together! This is the 9th day of my participation in the “Gold Digging Day New Plan Β· April More text Challenge”. Click here for more details.
Write at the front π
Multi-table query process: calculate the Cartesian product of multiple tables, filter the records in the cartesian product based on some criteria
Note: When querying multiple tables, use the fields associated with the two tables as conditions
1. Data preparation β¨
Create a person identity table
CREATE TABLE identity(
id INT PRIMARY KEY AUTO_INCREMENT,# key
name varchar(20) # Identity name
);
Add department table data
INSERT INTO identity (name) VALUES('pirates'), ('navy'), ('Revolutionary Army'), ('Dragon Man');
Create op character table
CREATE TABLE op (
id INT PRIMARY KEY AUTO_INCREMENT,# key
name VARCHAR(20), # the name
gender enum('male'.'woman'.'δΊΊε¦'),# gender
fruit VARCHAR(30), # Fruit power
reward BIGINT.# Reward money, unit: Bailey
ity_id INT.Outside the # key
FOREIGN KEY (ity_id) REFERENCES identity (id) # op table associated with identity table
);
-- Add character data
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('luffy'.1.'Fruit of Man - Phantom Beast - Nika Form'.1500000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('sauron'.1.null.320000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('yellow apes'.1.'Sparkling Fruit'.null.2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES (alita.2.'Slippery fruit'.5000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Ivankov'.3.'Hormonal fruit'.null.3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('saab'.1.'Burn the fruit'.602000000.3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('XiaoFeng'.3.'Imitation fruit'.32000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('ace'.1.'Burn the fruit'.550000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Dusky'.2.null.null.2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Little Sadie'.2.null.null.2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('the warring states'.1.'Fruit of All - Phantom Animal Species - Buddha Form'.null.2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('bear'.1.'Meatball fruit'.296000000.3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('red'.1.'Face fruit'.4028900000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('robin'.2.'Flower flower fruit'.130000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Joker'.1.'Thread fruit'.340000000.null);
Copy the code
Cartesian product phenomenon π
The essence of cartesian product: permutations and combinations
1οΈ Cartesian product π
Cartesian product formula: Number of data in table A * number of data in table B = Cartesian product
-- Requirement: Query all people and their identities
SELECT * FROM op,identity; This query will display 15*4=60 records
Copy the code
- If you query directly without conditions, the following results occur, which we callCartesian product phenomenonπ
2 How to remove the effect of cartesian product phenomenon π¨π§
- We find that not all combinations of data are useful, so we need to filter out useless data through conditions.
/* Set the filter criteria */
Select * from op where ity_id = id
SELECT * FROM op,identity WHERE op.ity_id=identity.id;
SQL > alter table L (R); SQL > alter table L (R)
SELECT * FROM op AS L,identity AS R WHERE L.ity_id=R.id;
Copy the code
- The demo results are as follows: π
Three, internal connection query πΉ
An inner join query displays only the data that meets the criteria. There are two types of inner join: implicit inner join and outer join
- Inner join queries for left and right tablesIntersection part:
A studying B
, as shown in the following figure π
1οΈ implicit internal connection π
- It’s also mentioned above
WHERE
You can filter redundant data by setting association conditions
/ * * / grammar
SELECTThe field nameFROMLeft table, right tableWHEREconditions/ * sample * /
-- Query all people and their identities
SELECT * FROM op,identity WHERE op.ity_id=identity.id;
Copy the code
2οΈ display internal connection π₯
- Using INNER JOIN… ON statement, INNER can be omitted
/ * * / grammar
SELECTThe field nameFROMThe left table [INNER] JOINTo the right tableONconditions/ * sample * /
-- Requirement: Query xiao Feng's fruit ability and identity
# 1. Determine which tables to query
SELECT * FROM op AS L INNER JOIN identity AS R;
# 2. Determine the join conditions for the table
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
# 3. Determine the query criteria
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id WHERE L.name='XiaoFeng';
# 4. Determine the query field
SELECT L.name,L.fruit,R.name FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id WHERE L.name='XiaoFeng';
# 5. The standard writing method is for reference only, and the line writing is convenient to add notes
SELECT
L.name ASName,-- Query field: character name
L.fruit ASThe fruit,Query field: Character fruit ability
R.name ASidentity-- Query field: Person identity
FROM
op AS L Select * from 'op'
INNER JOIN
identity AS R Select * from table where id = 'identity'
ON
L.ity_id=R.id Select * from op where ity_id = id
WHERE
L.name='XiaoFeng'; -- Query condition: the character whose name is Xiao Feng
Copy the code
-
The demo results are as follows: π
-
Of course, if you use WHERE AND, you get the same result
SELECT
L.name ASName,-- Query field: character name
L.fruit ASThe fruit,Query field: Character fruit ability
R.name ASidentity-- Query field: Person identity
FROM
op AS L, Select * from 'op'
identity AS R Select * from table where id = 'identity'
WHERE
L.ity_id=R.id Select * from op where ity_id = id
AND
L.name='XiaoFeng'; -- Query condition: the character whose name is Xiao Feng
Copy the code
3οΈ summary of internal connection enquiry procedure π§
-
Determine which tables to query
-
Determine the table join condition
-
Determine the query criteria
-
Determine the query field
Four, external connection query π
It is divided into left outer join and right outer join query
1οΈ cable π»
- Returns all records in the left table and displays NULL in the corresponding field if there is no match in the right table.
- Left external connection is used
LEFT OUTER JOIN ... ON
OUTER can be omitted
/ * * / grammar
SELECTThe field nameFROMThe left tableLEFT [OUTER] JOINTo the right tableONconditions/ * sample * /
-- Use inner join query
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
-- Use the left outer join query
SELECT * FROM op AS L LEFT JOIN identity AS R ON L.ity_id=R.id;
Copy the code
- The demo results are as follows: π
- Use inner join queries
- Use the left outer join to query
2οΈ right outer connection πΌ
- Returns all records in the right table and displays NULL in the corresponding field if there is no match in the left table.
- Right outer connection is used
RIGHT OUTER JOIN ... ON
OUTER can be omitted
/ * * / grammar
SELECTThe field nameFROMThe left tableRIGHT [OUTER ]JOINTo the right tableONconditions/ * sample * /
-- Use inner join query
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
Use the right outer join to query
SELECT * FROM op AS L RIGHT JOIN identity AS R ON L.ity_id=R.id;
Copy the code
- The demo results are as follows: π
- Use inner join queries
- Use the right outer join to query
3 summary of οΈ external connection πΈ
- Left outer connection and right outer connection is the same truth, the general use of left outer connection more
- External join can be understood as: ensure that all data of the left table (or right table) is displayed on the basis of the inner join. If the right table (or left table) does not meet the conditions, null is displayed
5. Sub-query π³
Also called nested query, the result of one query can be used as the condition or table name of another query
- Here are three cases of subqueries π
1οΈ one line and one row π₯
- The result of a subquery is a single row, single column, and the parent query uses comparison operators such as >, <, <>, =, and so on
/ * * / grammar
SELECTQuery fieldFROM 葨 WHEREField = (subquery);/ * sample * /
# Demand: Query all information about the man with the highest reward
-- 1. Check the maximum reward in the op table
SELECT MAX(reward) FROM op;
-- 2. According to the highest reward to the OP table query the corresponding character information
SELECT * FROM op WHERE reward = (SELECT MAX(reward) FROM op);
Copy the code
- The demo results are as follows: π
2οΈ multi-row and single row π΄
- The result of a child query is a collection of rows and columns used by the parent query
IN
The operator
/ * * / grammar
SELECTQuery fieldFROM 葨 WHEREfieldIN(subquery);/ * sample * /
# request: query who is a pirate and navy
-- Query the id of pirate and navy first
SELECT id FROM identity WHERE name in('pirates'.'navy');
Select * from user where id = ""
SELECT * FROM op WHERE ity_id in (SELECT id FROM identity WHERE name in('pirates'.'navy'));
Copy the code
- The demo results are as follows: π
3οΈ multi-row and multi-row πΉ
- Subquery results are multi-row, multi-column, inFROMBehind as
Virtual table
- Pay attention to: This virtual table needs to be fetched
The alias
Otherwise, the table does not have a name and the fields in the table cannot be accessed
/ * * / grammar
SELECTQuery fieldFROM(Subquery)ASVirtual table aliasWHEREConditions;/ * sample * /
# request: query all information of characters whose bounty is less than the average value, including basic information and owning identity
-- 1. Find the basic information of the character whose bounty is less than average in the OP table
SELECT * FROM op WHERE reward<(SELECT AVG(reward) FROM op);
-- 2. Query idendity table information, combine it with virtual table information, and set filter criteria
SELECT * FROM (SELECT * FROM op WHERE reward<(SELECT AVG(reward) FROM op)) AS L,identity AS R WHERE L.ity_id=R.id;
Copy the code
- The demo results are as follows: π
4οΈ subquery summary π§
- As long as the subquery result isSingle rowWhile the,WHEREBehind as
conditions
- As long as the subquery result isMultiple columnsWhile the,FROMBehind as
Virtual table
Perform a secondary query
Write it at the back π»
Thank you for watching β¨ have any deficiencies, welcome to point out oh π nuggets operation students audit hard π