Today’s sharing started, please give us more advice ~
1. Joint query
Definition and data preparation
In real development, data often comes from different tables, so multiple tables need to be queried jointly. A multi-table query is a cartesian product of data from multiple tables:
The Cartesian product is understood by an example:
If there are a1, A2,a3 in table A and b1, B2,b3 in table B, the Cartesian product is
a1b1,a1b2,a1b3
a2b1,a2b2,a2b3,
a3b1,a3b2,a3b3
All data in table A can correspond to all data in table B;
First, prepare three tables and their corresponding data:
Class list:
The student table:
The curriculum:
Score sheet:
2. The inner connection
The information is filtered by the columns shared by the two tables to achieve the purpose of query as soon as possible.
Grammar:
Select * from table 1 aliases 1 [inner] join table 2 aliases 2 on join conditions and other conditions
Select * from table 1, alias 1, alias 2 where link condition and other conditions
2.1 Example 1:
Query xu Xian’s score;
Take a look at the whole query process;
Student table and score table link query directly:
Select * from student inner join score;
As well as
There are 189 rows of data in the query table, and each person corresponds to all the results in the table, that is to say, each person has 21 results, so 9 students have 189 data, which corresponds to the cartesian product mentioned above.
We need to condition the query: we know that in the score table we have student_id and course_id, so we need to filter the scores from the score table for ids equal to those from student.
select * from student inner join score on student.id=score.student_id;
At this point, we see the table, all the results and I are corresponding, now you can go to screen xu Xian;
select * from student inner join score on student.id=score.student_id and student.id =4;
At this point, the query is over, we have got xu Xian’s scores, but only the course ID does not know the name of the course.
To make improvements:
select stu.sn,stu.name,sco.score,cou.name from student stu inner join score sco on stu.id=sco.student_id
inner join course on score.course_id=course.id and student.id=4;
Query the student sn and name in the student table, score in the score table, and name in the class schedule;
First of all, through the student ID in the score table and the student id in the table to find the current result of each student, and then through the course ID in the score table to query the corresponding course name in the class schedule;
The above code can also be queried without an inner join:
2.2 Query the total score of all students
Stu. id=sco.student_id. This condition is used to query the corresponding score belonging to each student.
select stu.sn,stu.name,sum(score) from student stu,score sco where stu.id=sco.student_id;
In this way, the query result does not separate the students, but queries the scores of a single student, and it is not the total score of a single student, but adds all the scores in the score table. But why only Li Kui is displayed? Remove the sum and try again.
select stu.sn,stu.name,score from student stu,score sco where stu.id=sco.student_id;
As can be seen, since no query condition is set for a single student, the display results add all the scores together and show only the first student.
Improve the above code: add groups to separate each student’s score;
3. The outside connection
The outer connection is divided into left outer connection and right outer connection. If the table on the left is fully displayed in a federated query, we say left-outer join; The table on the right shows exactly what we call the right outer join.
The first is the left join, where the left table is the main table and all the content in the left table is displayed.
select stu.id,stu.name,sco.score from student stu left join score sco on stu.id=sco.student_id group by stu.id;
Right join, with the right table as the main, to display the contents of the right table:
select stu.id,stu.name,sco.score from student stu right join score sco on stu.id=sco.student_id group by stu.id;
You can see that the left link shows all the contents of the left table (student) and the right link shows all the contents of the right table (score). Null does not show the contents of the left table. If you do not specify the left and right links, the intersection of the two tables is not empty.
Let’s see how to distinguish the left and right tables:
select stu.id,stu.name,sco.score from score sco left join student stu on stu.id=sco.student_id group by stu.id;
Mysql > alter table score (student); mysql > alter table score (student); mysql > alter table score (student); mysql > alter table score (student); mysql > alter table score (student);
4. Since the connection
Example: query information about all computer principles scores higher than Java scores.
First look at the information in the class schedule:
You can see: computer principle id=3; Java’s id = 1;
Therefore, we need to search the score with ID =3 >id=1 in the score table;
You need to compare yourself with your own content, which is called self-linking;
Self-link implementation:
It should be noted that when we implement this query, we should pay attention to the student ID needs to be equal, because we are querying the same student’s computer theory score is higher than Java score information.
Select s2.*; select s2.*; Course_id =3;
The above query can be expressed as: when the student IDS in tables S1 and S2 are equal; Course_id =1 and Course_id =3 in table 1, where the grade of 1 is less than the grade of 3, and display the information in table 2.
In fact, the information in Table 1 and Table 2 was compared. Firstly, the information with the same student ID was screened out, and then the information with the result of courseID =3 in Table 2 being greater than coureseID =1 in Table 1 was screened out and displayed.
5. The subquery
A subquery is a SELECT statement embedded in another SQL statement, also known as a nested query
Example: Query Xu Xian’s classmates;
First of all, you must know which class Xu Xian is in.
Select * from student where classes_id = (select classes_id from student where stu.
Multi-line subquery:
To enquire about the results of Chinese or English courses:
The keywords IN and NOT IN
In combination with the above nesting approach, you need to know the language or English course ID first.
Select * from score where course_id in(select id from course where name=’ language ‘or name=’ English ‘);
6. Merge query
In practice, to combine the results of multiple select operations, you can use the set operators union, union all. When UNION and UNION ALL are used, the fields in the result set must be the same.
Important: The format of the query results should be consistent:
Select * from course where id < 3 and name = ‘English’;
union all
This operator is used to get the union of two result sets. When used, repeatability in the result set is not removed.
Example: Query for courses whose ID is less than 3, or whose name is “Java”
Today’s share has ended, please forgive and give advice!
How do I get it?
Forward to share this article, background private letter xiaobian: “1” can be obtained. (Note: Forwarding sharing, thank you)