1, Internal connection query
First create the tables student, course, and exam
CREATE TABLE student( uid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, age TINYINT UNSIGNED NOT NULL, sex enum("man", "woman") NOT NULL ); CREATE TABLE course( cid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, credit TINYINT UNSIGNED NOT NULL ); CREATE TABLE exam( uid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, time DATE NOT NULL, score FLOAT NOT NULL, PRIMARY KEY(uid, cid) );
We now want to check the personal information of classmate No. 1 and his grade of course No. 2
- First query the details of zahngsan in the student table:
select uid, name, age, sex from student where uid=1;
- Then query zhangsan's examination information in the examination table:
select time, score from exam where uid=1 and cid=2;
- Inner join merges the results of two queries
Because it is the common part of the query student table and examination table, the index uses inner join
select stu.uid, stu.name, stu.age, stu.sex, ex.time, ex.score from student as stu inner join exam as ex on stu.uid=ex.uid where ex.uid=1 and ex.cid=2;
- The course name cannot be seen in the above query. We use the inner connection again to connect three tables to query the information with the course name
select stu.uid, stu.name, stu.age, stu.sex, ex.time, ex.score, co.cname from exam as ex -- The first intermediate table is placed for connection, that is, the table that has a common part with the other two tables inner join student as stu on ex.uid=stu.uid -- Connect exam form and student form inner join course as co on ex.cid=co.cid -- Connect exam and course schedules where ex.uid=1 and ex.cid=2; -- Filter condition
5. Query the number of individuals with scores higher than 90 in each course
select course.cid, course.cname, count(*) cnt from course join exam on exam.cid=course.cid -- connect where exam.score > 90.0 -- Conditional filtering group by exam.cid -- grouping order by cnt desc; -- sort
6. Query the student information and course information with the highest score in cid=2
select co.cname, co.credit, stu.name, ex.score from exam ex inner join course co on ex.cid=co.cid inner join student stu on ex.uid=stu.uid where ex.cid=2 order by ex.score desc limit 1;
- Query the average score of the course cid=2
select avg(score) from exam where cid=2;
- Details of each course and GPA
select co.cname, co.credit, avg(score) from course co inner join exam ex on co.cid=ex.cid group by ex.cid;
stay MySQL database table operations and simple query statements It is mentioned in that the number of select attributes can also affect the speed of query. You can also use conditional filtering where < attributes with indexes > to speed up query. Now we will introduce using inner join to optimize query
Can it take less time to query multiple attributes?
Inner join optimization query
Optimization principle: because the attribute id with index is used when generating the small table (temporary table), the small table is generated quickly, and then the data of the small table is used in the large table t_user matches the id and uses the index, so it can speed up the query
select a.id, a.email, a.password from t_user inner join (select id from t_user limit 1500000, 10) b on a.id=b.id
2, Explain the query process of inner connection in detail
How does the database engine merge tables according to on a.uid=b.uid?
First, determine the large table and small table according to the amount of data. The small table is always the whole table scan. Take the uid of the small table to the large table to search for uid. Obviously, because small tables are always scanned by the whole table without using indexes, we generally build indexes for large tables to speed up the search.
For inner join, assume that table A is A large table and table B is A small table at the beginning. When the database engine takes all the data from table B to table A for matching, it finds that there is still A where in the SQL statement. At this time, it needs to filter the data to filter the data that meets the conditions. At this time, there may be less data satisfying the conditions in table A than in table B. This is because the table formed by the data satisfying the conditions in table A becomes A small table and the table formed by the data satisfying the conditions in table B becomes A large table. To sum up, first use where to filter the data, and then use the data from the small table to the large table to match the data that meets the on condition
For inner join, the sub condition of where is placed after on, and the effect and efficiency are the same. Because the MySQL engine will optimize the conditions behind on to where, where can use the index, and the efficiency is high.
3, Left and right connection
// left join displays all data in the left table. If the right table does not exist, it is NULL select * from student left join exam on student.uid=exam.uid; // right join displays all data in the right table. If the left table does not exist, it is NULL select * from student right join exam on student.uid=exam.uid;
The inner connection is a case sensitive table, and the outer connection is not a case sensitive table. For left and right connections, there is a table that needs to be searched by the whole table
Application scenario 1: view students who have not taken the exam
-- select distinct uid from exam An intermediate table will be generated for external use SQL query -- not in The hit to the index is not high. Generally, the whole table is scanned select * from student where uid not in (select distinct uid from exam); -- Using external connection, only the course number is displayed null Student information select a.* from student a left join exam b on a.uid=b.uid where b.cid is null;
External connections are often used in some scenarios that do not appear, such as goods that do not appear in any orders
Application scenario 2: view students who have not taken the No. 3 course examination
select a.* from student a inner join exam b on a.uid=b.uid where b.cid=3; -- Looks like left join,In fact, it has become inner join select a.* from student a left join exam b on a.uid=b.uid where b.cid=3;
After on, write the connection condition, and after where, write the filter condition
select a.* from student a left join exam b on a.uid=b.uid and b.cid=3 where b.cid is null;