🎈 Write in front
🙋♂️ Hello everyone, I'm super dream. We all know that we have to deal with the database almost every day, whether in study or daily work. In order to better operate the database, our SQL knowledge reserve is essential. If you want to master SQL well, you must practice and study every day. Next, Xiaomeng will lead our partners to start leetcode MySQL intensive training, review and consolidate our SQL knowledge by focusing on real problems, and be able to skillfully use SQL statements in future work and study. Xiaomeng will attach the corresponding knowledge points at the back of each question to facilitate the partners to check and make up the deficiencies.
🙋♂️ If you don't understand anything in the learning process, you are welcome to leave a message and ask questions in the comment area. Xiaomeng will tell you everything.
catalogue
Topic 2: the second highest salary
Topic 3: employees who exceed the manager's income
Inner connection and outer connection
Topic 4: find duplicate email addresses
Topic 5: customers who never order
Inner connection and outer connection
Topic 1: combine two tables
Topic overview
Title:
Write an SQL query that meets the following conditions: no matter whether the person has address information or not, the following information of {person needs to be provided based on table 1 and table 2:
FirstName, LastName, City, State
LeetCode original title address, click to enter~
Table 1: Person
+-------------+---------+ | Listing | type | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId yes Person Table primary key
Table 2: Address
+-------------+---------+ | Listing | type | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId yes Address Table primary key PersonId It's a watch Person Foreign key for
Problem solving ideas
1. Let's read through the title first and look at table 1 (person) and table 2 (address) mentioned in the title. From the structure of the table, we can see that table 1 (person) is the person's name information and table 2 (address) is the person's address information.
2. We all need to query the information of the four fields (firstname, LastName, city and state) through the Person table and AddressId table. The query result is the column names in two tables, so multi table query is required.
3. Maybe not everyone has address information, so some people will have the information of City and State fields, and some people will not, and the corresponding display is null. Considering that some people may not have address information, if the query structure wants to query everyone, all the data in Table 1 (person) needs to be retained, so the left join is used.
4. The personId in the table Address is the external keyword of the table Person. The two tables are joined through the personId.
Code test
Here we use the left outer connection
select FirstName, LastName, City, State from Person left join Address on Person.PersonId = Address.PersonId;
Is the test performed on LeetCode successful
Consistent with the expected results, passed!
Summary of knowledge points
Join outside join table query
This topic mainly investigates the relevant knowledge points of the external connection of the joint table query. Next, I will take my friends to quickly review the relevant knowledge points of the external connection to help you learn and consolidate.
There are three types of external connections: left join, right join and full join. Here we omit the keyword "outer".
An important feature of outer join: at least one party retains the complete set, and no matching row is replaced with NULL.
The following little dream briefly describes these three external connections:
1. LEFT OUTER JOIN, referred to as LEFT JOIN, left outer connection (left connection)
The result set retains all rows of the left table, but the right table contains only rows that match the left table. The corresponding NULL behavior of the right table is NULL.
SELECT * FROM Table 1 LEFT JOIN Table 2 ON Table 1.xx = Table 2.xx
2. RIGHT OUTER JOIN, RIGHT JOIN for short, right outer join (RIGHT JOIN)
The result set retains all rows of the right table, but the left table contains only rows that match the right table. The corresponding NULL behavior of the left table is NULL.
SELECT * FROM Table 1 RIGHT JOIN Table 2 ON Table 1.xx = Table 2.xx
3. FULL OUTER JOIN, referred to as FULL JOIN, is an all external connection
All rows of the two tables will be displayed in the result table.
SELECT * FROM Table 1 FULL JOIN Table 2 ON Table 1.xx = Table 2.xx
Topic 2: the second highest salary
Topic overview
Employee table
Employee surface +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
- The second highest Salary in the mployee} table. If there is no second highest Salary, the query should return null.
- For example, in the above "Employee" table, the SQL query should return "200" as the second highest salary.
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
https://leetcode-cn.com/problems/combine-two-tables/Problem solving ideas
Mode 1
- First, we write a select query statement to find out the highest salary in the Employee table.
select max(salary) from Employee;
2. Then we take the query statement in the first step as a clause. We make salary less than the maximum salary obtained by the clause through the where condition. In short, I found the highest salary less than the highest salary, which is the second highest salary.
select max(salary) from Employee where salary < (select max(salary) from Employee);
3. Next, we need to make a null judgment according to the meaning of the question. If there is no second highest salary, we will return a null value. Here we use the ifnull function (the summary of knowledge points in the article will explain the ifnull function ~).
Solution:
select ifnull(( select max(salary) from Employee where salary < (select max(salary) from Employee)),null) as SecondHighestSalary;
Mode 2
- First, we sort the salary field in descending order.
- De duplicate the value in alary through distinct. There may be some friends here who asked why there is no duplicate value of salary in the employee table. Why do you want to add and remove duplicates? Xiaomeng told his partners that there is little data in the title table. When the data is large, we can't guarantee that there is no same data. When duplicate data occurs and we do not perform the de duplication operation, we cannot judge the position of the second highest data through descending sorting. After the de duplication operation, we can clearly know that the second highest data is in the second place through descending sorting. Therefore, in order to be cautious and correct, we need to add distinct.
- After the above de reordering operations, we clearly know that the second highest salary is the second highest salary, and then query the second highest salary through limit 1, 1 or limit 1 offset1. (limit1, 1: the previous 1 means to skip a piece of data, and the latter 1 means to get a piece of data, which means to skip a piece of data and get a piece of data from the second piece of data.) (limit 1 offset 1: the previous 1 means to take a data, and the latter 1 means to skip a data. The whole meaning is to skip a data and take a data from the second data.)
- Next, we need to make a null judgment according to the meaning of the question. If there is no second highest salary, we will return a null value.
Solution:
##limit
select
ifnull(
(select distinct Salary
from Employee
order by Salary desc
limit 1,1),
null) as SecondHighestSalary;
## limit offset
select
ifnull(
(select distinct Salary
from Employee
order by Salary desc
limit 1 offset 1),
null) as SecondHighestSalary;Code test
Mode 1
select ifnull(( select max(salary) from Employee where salary < (select max(salary) from Employee)),null) as SecondHighestSalary;
The output is consistent with the expected result, and the answer is successful!
Mode 2
limit
select
ifnull(
(select distinct Salary
from Employee
order by Salary desc
limit 1,1),
null) as SecondHighestSalary;
The output is consistent with the expected result, and the answer is successful!
limit offset
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
The output is consistent with the expected result, and the answer is successful!
Summary of knowledge points
limit clause
Let's briefly review the knowledge points of limit and limit offset. We should always review the old and know the new~
- limit n clause means to read n pieces of data
- limit n, m clause means: skip n pieces of data and read M pieces of data
- limit n is equivalent to limit 0,n
- limit m offset n clause means: skip n pieces of data and read M pieces of data
ifnull function
Through today's question, I believe my friends have some understanding of ifnull function.
Ifnull (expression, y) function explanation:
If the expression of the first parameter is null, the value of the second parameter y is returned (null value is returned in this question).
If the expression of the first parameter is not NULL, the value of the expression of the first parameter is returned.
Topic 3: employees who exceed the manager's income
Topic overview
Employee table
+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+
Title: the Employee table contains all employees, and their managers also belong to employees. Each Employee has an Id and a list of the Id of the corresponding Employee's manager. Given the Employee table, write an SQL query that gets the names of employees whose income exceeds their managers. In the above table, Joe is the only Employee whose income exceeds his manager. The query results are as follows.
+----------+ | Employee | +----------+ | Joe | +----------+
Problem solving ideas
First of all, according to the meaning of the question, the Employee table includes all employees, and their managers also belong to employees. Each Employee has an Id and a list of the Id of the corresponding Employee's manager. Then we can see that the field ManagerId is the manager Id of the corresponding Employee. According to the Employee table, the manager Id corresponding to joe is 3 and the manager Id corresponding to Henry is 4. Managers also belong to employees. id3 corresponds to Sam, Id4 corresponds to Max, that is, joe corresponds to Sam and Henry corresponds to Max.
Because the table contains both Employee information and manager information, we need to obtain the information twice. We can regard Employee as both member work table and manager table. The title requires us to find out if the Employee's Salary is greater than the manager's, then we can connect the id field with the ManagerId field and add a condition that the Employee Salary is greater than the manager Salary. The specific methods are as follows.
Method 1
There are two kinds of information in the table: employees and managers. We need to obtain the information twice, and then use the Where statement to do conditional screening to find out the data that employees' wages are greater than managers.
Problem solution 1:
SELECT staff.Name AS 'Employee' FROM
Employee AS staff,
Employee AS manager
WHERE
staff.ManagerId = manager.Id
AND staff.Salary > manager.SalaryMethod 2
There are two kinds of information about employees and managers in the table. We need to obtain the information twice. In addition to using the where statement, we can also use the inner connection to screen the conditions through the on statement to find out the data that employees' wages are greater than managers.
Problem solution 2:
SELECT staff.NAME AS 'Employee' FROM Employee AS staff
JOIN
Employee AS manager
ON
staff .ManagerId = manager.Id
AND staff .Salary > manager.SalaryCode test
Method 1
SELECT staff.Name AS 'Employee' FROM
Employee AS staff,
Employee AS manager
WHERE
staff.ManagerId = manager.Id
AND staff.Salary > manager.Salary
Consistent with the predicted results, success!
Method 2
SELECT staff.NAME AS 'Employee' FROM Employee AS staff
JOIN
Employee AS manager
ON
staff .ManagerId = manager.Id
AND staff .Salary > manager.Salary
Consistent with the predicted results, success!
Summary of knowledge points
Inner connection and outer connection
Xiaomeng takes her partners through the inner connection and outer connection in a very simple way.
Table 1 classa
Table 2 classb
1. Inner join (join is inner join by default)
The intersection of Table 1 and table 2 is shown by the above two performances
select
classa.id as aid,classb.id as bid
from
classa
inner join classb on classa.id = classb.id;The result of the query is the intersection of classa and classb
2. Left outer joint
The result set retains all rows of the left table, but the right table contains only rows that match the left table. The corresponding NULL behavior of the right table is NULL.
select
classa.id as aid,classb.id as bid
from
classa
left join classb on classa.id = classb.id;
3. right join
The result set retains all rows of the right table, but the left table contains only rows that match the right table. The corresponding NULL behavior of the left table is NULL.
select
classa.id as aid,classb.id as bid
from
classa
right join classb on classa.id = classb.id;
4. full join
All rows of the two tables will be displayed in the result table.
select
classa.id as aid,classb.id as bid
from
classa
full join classb on classa.id = classb.id;Attention, guys!!!
MySQL does not support full join!!! MySQL does not support full join!!! MySQL does not support full join!!!
Say important things three times!!! How to achieve the same effect as full join? It should be realized by using union. The specific SQL statements are as follows
select
classa.id as aid,classb.id as bid
from
classa left join classb on classa.id = classb.id
union
select
classa.id as aid,classb.id as bid
from
classa right join classb on classa.id = classb.id;
Topic 4: find duplicate email addresses
Topic overview
Title:
Write an SQL query to find all duplicate e-mail addresses in the Person table.
Person table
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
Based on the above input, your query should return the following results:
+---------+ | Email | +---------+ | a@b.com | +---------+
Problem solving ideas
In fact, this question is very simple. I believe the little friends already know what to do. Hurry to LeetCode and write down the answer for a test!! After the test, you can see whether Xiaomeng is consistent with everyone's ideas. If there are many ideas, please leave a message and write down your ideas and solutions, and we can learn from each other~
According to the meaning of the question, I need to find out all duplicate e-mail addresses in the table, that is, the data corresponding to the Email field is duplicate. Then we can use the count function to get the number of Email addresses in the Email. As long as the number is greater than 1, it is required in the title. Next, according to this idea, Xiaomeng lists three solutions. If you have other ideas, you are welcome to add them~
Method 1
We can first find out the number of emails and corresponding emails, and take the found contents as a temporary table. By looking up the temporary table, we can find out those whose Email number is greater than 1. In this way, we can find out the required [find all duplicate Email addresses in the table] of the title.
Solution:
select Email from (select Email, count(Email) as num from Person group by Email) as temporary where num > 1;
Method 2
We can group emails through group by, and then conduct conditional screening through having. After having, we can use the aggregation function, which is very convenient (there will be a brief explanation of group by and having in the summary of knowledge points, and young partners remember to check ~). Through the aggregation function count (Email) to conduct condition screening after having, find out those whose Email number is greater than 1, so that we can find out the required [find all duplicate Email addresses in the table] of the title.
Solution:
select Email from Person group by Email having count(Email) > 1;
Method 3
In the third method, you can spread your thinking. How can you know repeated emails without the count function? How? Xiaomeng comes to take our partners to think together. First of all, we can think about it. The first two methods essentially judge whether the number of emails is greater than 1. If it is greater than 1, it is the answer we want. If it is not greater than 1, it is not. Have you noticed that although the Email of the Person table is repeated, but the id is unique, we can regard the Person table as two tables, one a table and one b table. Connect two tables. Add the Email of table a after the where statement, which is equal to the Email of table b and the id of table a, which is not equal to the id of table b. Then query the Email and do the de duplication operation to get the answer we want.
Solution:
select distinct a.Email from Person a,Person b where a.Email = b.Email and a.id <> b.id;
Code test
Method 1
select Email from (select Email, count(Email) as num from Person group by Email) as temporary where num > 1;
Enter the solution and test
Test successful!
Method 2
select Email from Person group by Email having count(Email) > 1;
Enter the solution and test
Test successful!
Method 3
select distinct a.Email from Person a,Person b where a.Email = b.Email and a.id <> b.id;
Enter the solution and test
Test successful!
Summary of knowledge points
group by and having clauses
The data in SQL can be grouped by column name and can be used together with aggregate function to facilitate our query and acquisition of data.
Example:
SELECT count(stuname) FROM student GROUP BY student_class;
The GROUP BY statement can be used together with the WHERE statement. Of course, there is a very important place. I hope you can write it down. It's very important! - > When there are aggregate functions, WHERE statements and GRUOP BY statements in an SQL, what is their execution order? WHERE > GROUP BY > aggregate function.
Therefore, we cannot use aggregate functions in the WHERE statement to execute filter conditions. If we use them, an error will be reported. In this case, we can use aggregate functions in the HAVING clause to perform filter condition screening. Friends must remember!
Finally, Xiaomeng will share the execution order of each keyword of SQL query statement. You can take a small notebook and write it down. It's very useful~
[key] SQL execution sequence:
- Execute FROM first
- Then perform WHERE condition filtering
- Then execute GROUP BY grouping
- Then execute SELECT and aggregate function
- Then execute HAVING condition filtering
- Then perform ORDER BY sorting
Topic 5: customers who never order
Topic overview
Customers table:
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Orders table:
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
A website contains two tables, the Customers table and the Orders table. Write an SQL query to find all Customers who never order anything.
For example, given the above table, your query should return:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
Problem solving ideas
According to the title, we first look at what the title requires us to check. We should find out the Customers who never order anything. According to this, we can know from the Orders table that Customers with CustomerId 1 and 3 have ordered things, while Customers with CustomerId 2 and 4 have not ordered anything. Then check the customer names corresponding to id2 and id4 in the Customers table.
Method 1
1. According to the solution idea, we can first write a sub query to find out the corresponding CustomerId customer Id in the Orders order table
select customerid from orders;
2. After finding out the customer Id, we will know who has bought things and who never buys things. We then use the NOT IN clause to restrict the customer Id. if the customer Id in the Customers table is NOT IN the Orders table, it is required by the title.
select customers.name as 'Customers' from customers where customers.id not in (select customerid from orders);
Method 2
We link the Customers table with the Orders table through the left outer link. We only need to check the data in the Orders table that is NULL after the link, that is, the Customers who have never bought anything.
select c.name as Customers from Customers c left join Orders o on c.id = o.CustomerId where o.id is null;
Code test
Method 1
SQL code
select customers.name as 'Customers' from customers where customers.id not in (select customerid from orders);
Execute code, test
Test successful!
Method 2
SQL code
select c.name as Customers from Customers c left join Orders o on c.id = o.CustomerId where o.id is null;
Execute code, test
Test successful
Summary of knowledge points
Inner connection and outer connection
Table 1 classa
Table 2 classb
1. Inner join (join is inner join by default)
The intersection of Table 1 and table 2 is shown by the above two performances
select
classa.id as aid,classb.id as bid
from
classa
inner join classb on classa.id = classb.id;The result of the query is the intersection of classa and classb
2. Left outer joint
The result set retains all rows of the left table, but the right table contains only rows that match the left table. The corresponding NULL behavior of the right table is NULL.
select
classa.id as aid,classb.id as bid
from
classa
left join classb on classa.id = classb.id;
3. right join
The result set retains all rows of the right table, but the left table contains only rows that match the right table. The corresponding NULL behavior of the left table is NULL.
select
classa.id as aid,classb.id as bid
from
classa
right join classb on classa.id = classb.id;
4. full join
All rows of the two tables will be displayed in the result table.
select
classa.id as aid,classb.id as bid
from
classa
full join classb on classa.id = classb.id;Attention, guys!!!
MySQL does not support full join!!! MySQL does not support full join!!! MySQL does not support full join!!!
Say important things three times!!! How to achieve the same effect as full join? It should be realized by using union. The specific SQL statements are as follows
select
classa.id as aid,classb.id as bid
from
classa left join classb on classa.id = classb.id
union
select
classa.id as aid,classb.id as bid
from
classa right join classb on classa.id = classb.id;
😀 Thank you for your support. If you have any questions, please leave a message and ask. Xiaomeng will know everything and say everything!
