MySQL federated query

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

  1. First query the details of zahngsan in the student table:
select uid, name, age, sex from student where uid=1;
  1. Then query zhangsan's examination information in the examination table:
select time, score from exam where uid=1 and cid=2;
  1. 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;

  1. 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;

  1. Query the average score of the course cid=2
select avg(score) from exam where cid=2;
  1. 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;

Keywords: Database MySQL

Added by JMM on Sun, 16 Jan 2022 22:14:42 +0200