〖Python database development combat – MySQL chapter⑳〗 – data table inner join

Hits: 0

🎉 The faint thunder and the hazy sky. 🎉
🎉 But I hope the wind and rain come, I can keep you here. 🎉

Foreword :
✌ About the author: Husky thirsting for power✌, you can call me 🐶 Husky 🐶 , a blogger dedicated to TFS – full stack empowerment ✌
🏆 CSDN blog expert certification, MVP of the third season of the Nova Project full stack track , Huawei Cloud Sharing experts, Alibaba Cloud expert bloggers 🏆
📫 If there are mistakes in the knowledge points of the article, please correct me! Learn and progress together with everyone👀
💬 Life motto: Being superior to others is not noble. The real nobility should be superior to your past self. 💬
🔥 If you feel that the blogger’s article is not bad, please 👍 follow, like, and bookmark to support the blogger 👍

Column Series (click to unlock) Learning Route (click to unlock) knowledge orientation
🔥Python Full Stack Whitepaper🔥 Zero-based introductory articles It’s easy to get started in an easy-to-understand way, and you will completely fall in love with the charm of Python.
Advanced Grammar It mainly focuses on multi-threaded programming, regular expression learning, and project exercises that are close to actual combat.
Office Automation Realize the automatic operation of daily office software, save time and improve office efficiency.
Automated testing in practice From the perspective of actual combat, one step ahead of others, rapid transformation of test and development engineers.
Database development in practice Master the knowledge of relational and non-relational databases, and improve the ability of actual database development.
Introduction and actual combat of reptiles updating
Data Analysis updating
Front-end entry + flask full stack updating
django+vue full stack updating
Outreach – Introduction to Artificial Intelligence updating
The road to cybersecurity Stepping on the pit Record the pits encountered in the process of learning and drills, so that the later ones can catch up
Internet Security Knowledge Literacy Three days of fishing without a deep understanding of the principles will only make you a script kid.
vulhub shooting range vulnerability recurrence It makes vulnerability reproduction easy, and allows security researchers to focus more on the vulnerability principle itself.
Shell programming articles It does not involve the Linux foundation, and the final case will be biased towards the direction of security reinforcement. [to be completed]
WEB Vulnerability Attack and Defense The update will stop on September 3, 2021, and move to safe communities and small secret circles such as the Prophet community
Penetration Tool Use Collection The update will stop on September 3, 2021, and move to safe communities and small secret circles such as the Prophet community
little bit engineer Test Artifact – Charles Software test data packet capture and analysis artifact
Test Artifact – Fiddler Yiwen can learn to fiddle, and it is rare to learn how to eat upside down!
Test Artifact – Jmeter It is not only a performance testing artifact, but also can be used to build a lightweight interface automated testing framework.
RobotFrameWork An automated testing tool implemented by Python, this chapter only introduces the UI automation part.
Java implements UI automation The document was written in 2016, and the UI automation implemented by Java is still useful for reference.
MonkeyRunner There are not many application scenarios of this tool at present, the document has been deleted, and it is kept for the sake of good-looking layout.

Article directory

The content of this chapter is the inner join of multi-table join query, because MySQL is a [relational database] , and the data is split and reorganized in multiple data tables. Therefore, we are bound to extract data from multiple data tables, and we can achieve multi-table query through inner join and outer join of SQL statements. This part of the content requires us to focus on learning. During the learning process, a variety of cases will be interspersed to strengthen the use of the syntax of table joins. (To put it simply, it is actually querying the data set from multiple tables. This large paragraph is used by me to make up the number of words for the abstract!)

🐳 Extract data from multiple tables

  • If you want to extract data from multiple tables, you must specify their associated conditions. If the associated conditions are not defined, an unconditional connection will occur, and each piece of data between the tables will be matched and connected, thus resulting in a Cartesian product.

SELECT empno, ename, dname FROM t_emp JOIN t_dept;</p> <p>-- This is an example of a wrong grammar that causes a Cartesian product-- This kind of example does not conform to realistic logic, so we must give the connection conditions when doing table joins

SELECT e.empno, e.ename, d.dname FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno;</p> <p>-- Use the ON clause to specify the "connection condition" , which is that the "department number" in the "employee table" should be equal to the "department number" in the "department table" -- It should be noted that an alias must be created between the connected table and the table, otherwise if "ON deptno = deptno" is used directly, MySQL will not know which table this field belongs to -- "t_emp" table alias is "e" ; "t_dept" table alias is "d"

🐳 Classification of table joins

  • There are two types of table joins: inner join and outer join

🐳 Introduction and syntax of inner join

SELECT ...... FROM table 1 JOIN table 2 ON join condition;</p> <p>-- This is the most standard inner join syntax. Use the "JOIN" keyword to join two tables, and the "ON" clause specifies the conditions for the join.

-- Consider a question first: Since the "ON" clause specifies the join condition to filter the records that meet the conditions, can the join condition be written in the "WHERE" clause? -- The answer is completely possible, and yes, there is the following syntax.</p> <p>SELECT ... FROM table 1 JOIN table 2 WHERE join condition;</p> <p>-- Ignore the "ON" clause, use the "WHERE" clause instead

SELECT ...... FROM table 1 , table 2 WHERE join condition;</p> <p>-- Remove the "JOIN" keyword and use "," instead. -- If you want to connect how many tables, you can write the table name directly after the "FROM" clause, and finally define the join condition in the "WHERE" clause.

🐬 The working effect of inner join derived syntax

SELECT e.empno, e.ename, d.dname FROM t_emp e JOIN t_dept d WHERE e.deptno = d.deptno;</p> <p>-- Replace "ON" clause with "WHERE" clause

SELECT e.empno, e.ename, d.dname FROM t_emp e , t_dept d WHERE e.deptno = d.deptno;</p> <p>-- omit the "JOIN" keyword and replace it with a "," comma

🐬 Inner join small exercise①

  • Query each employee’s job number, name, department name, basic salary, position, salary grade

SELECT e.empno, e.ename, d.dname, e.sal, e.job, s.grade FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno JOIN t_salgrade s ON e.sal BETWEEN s.losal AND hisal;</p> <p>-- First use the "Department Number" of the "Employee Table" to associate the "Department Number" of the "Department Table" -- Then use the "Salary Level Range" of the "Salary Level Table" to associate the "Monthly Salary" of the "Employee Table" for grade match

🐬 Inner join exercise ②

*   About unknown conditions, the first thing you may think of is to use sub-query to achieve, OK, then first use "sub-query" to achieve
<hr />
<p><code>SELECT ename
FROM t_emp
WHERE deptno = (SELECT deptno FROM t_emp WHERE ename = "SCOTT")
AND ename != "SCOTT";</code></p>
<p>This query statement is still very easy to understand. It is very logical for human thinking, but this way of writing is not recommended. Because the query efficiency of the subquery is very slow, because the "FROM" clause queries the "t_emp" employee table, and each record extracted from the "employee table" is matched in the "WHERE" clause. Will and "subquery" will be re-executed again. When there is a large amount of data in the "employee table" (such as tens of thousands), the "WHERE" clause will re-execute the subquery.</p>
<p>Just imagine, is this kind of efficiency very low? Although it is said that this way of writing is very in line with human thinking and logical thinking, the database implementation will be very inefficient. So at this time, the best solution needs to be realized by using "table join"!</p>
<hr />
SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno = e2.deptno
WHERE e1.ename = "SCOTT"
AND e2.ename != "SCOTT";

-- You may think that it is strange to join "employee table" and "employee table" (self and yourself), but in actual syntax, this is feasible and there is no error
-- the principle is actually in the same data table , to extract different data separately; such syntax is far more efficient than subqueries

From this case, we also get a result: "The same data table can also be connected to the table, and there is no problem with the syntax and logical relationship."

You may also like...

Leave a Reply

Your email address will not be published.