〖Python database development practice – MySQL chapter ㉓〗 – single-line subquery and multi-line subquery syntax rules

Hits: 0

Manyoshu
🎉 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

In this chapter, we will continue to learn the syntax of “subquery”. In the previous chapter, we divided “WHERE subquery”, “FROM subquery” and “subquery” according to the “clause” of “subquery”. “SELECT subqueries”. In this chapter, we will divide the subquery by the number of result set records of the subquery. Let’s take a look at it next.

🐳 Single-line subquery and multi-line subquery

  • The result set of a single-row subquery has only one record
  • Multi-row subquery result set has multiple rows of records
  • The difference between the two
    • Single-row subqueries can appear in “SELECT clause”, “FROM clause”, “WHERE clause”.
    • Multi-row subqueries can only appear in “FROM clause” and “WHERE clause”.

Why multi-row subqueries can only appear in “FROM clause”, “WHERE clause”?

When a subquery returns multiple rows, it’s fine if it’s in the “FROM” clause. Because the result set will be treated as a temporary table to make a join to other data tables, it does not matter how many results of the subquery are.

If multiple rows of records appear in the “WHERE” clause, special expression symbols can be used to judge, which will be described in detail below.

However, it is absolutely impossible if multiple rows of results appear in the “SELECT” clause, because one record in other fields of the “SELECT” clause cannot be merged with multiple records in the subquery.


SQL statement demonstration case:

SELECT ename, deptno FROM t_emp WHERE deptno IN (SELECT deptno FROM t_emp WHERE ename IN("FORD", "MARTIN")) AND ename NOT IN ("FORD", "MARTIN");</p> <p>-- First write a "SELECT" subquery to get the result set of the department where "FORD" and "MARTIN" belong-- Then use the result set of the department as a "WHERE" condition to query all employees belonging to the department- - And use the NOT IN keyword to exclude "FORD" and "MARTIN" -- this is the result set of all colleagues of "FORD" and "MARTIN"

🐳 Multiline subquery operator (keyword)

  • In the “WHERE” clause, in addition to the “IN” keyword used in the example above. You can also use the “ALL”, “ANY”, and “EXISTS” keywords to process the conditional judgment of the multi-line expression result set.

🐬 ALL keyword SQL example

  • Query the information of employees with higher monthly salary than “FORD” and “MARTIN”

SQL statement example:

SELECT ename, sal, deptno
FROM t_emp
WHERE sal >= ALL
(SELECT sal
FROM t_emp
WHERE ename IN("FORD", "MARTIN"))
AND ename NOT IN ("FORD", "MARTIN");

🐬 ANY keyword SQL example

  • Query the information of employees whose monthly salary is higher than any one of “FORD” and “MARTIN”

SQL statement example:

SELECT ename, sal, deptno
FROM t_emp
WHERE sal >= ANY
(SELECT sal
FROM t_emp
WHERE ename IN("FORD", "MARTIN"))
AND ename NOT IN ("FORD", "MARTIN");

🐬 EXISTS keyword SQL example

  • The “EXISTS” keyword is particularly difficult to understand. To put it simply, in the “WHERE” clause, we used the condition and the result of the subquery to judge, but now the condition has been changed to a subtype of the “EXISTS” keyword. query inside.

The syntax is as follows:

SELECT ... FROM table name WHERE EXISTS (subquery);</p> <p>-- It can be seen that there is no conditional judgment in the "WHERE" clause, and the conditional judgment is written in the subquery after the "EXISTS" keyword-- The "NOT" keyword can also be used before the "EXISTS" keyword, so i.e. "Keep results that do not meet the 'EXISTS' condition"


Example of SQL statement: (query employee information whose salary grade is 3 or 4)

SELECT empno, ename, sal
FROM t_emp
WHERE EXISTS(
SELECT * FROM t_salgrade
WHERE sal BETWEEN losal AND hisal
AND grade IN (3, 4)
);

You may also like...

Leave a Reply

Your email address will not be published.