MySQL Study ( 3、 … and )— Use join Make a connection query / Internal connection / The left outer join / Right connection / Self join / Sorting and paging / Subquery

01 Link query

Equivalent connection ： It can be used when two or more tables are related ,where Used in clauses = The equal sign is the qualification

Non equivalent connection ：where Clause using fuzzy matching as the qualification condition

02 Internal connection

# grammar ：inner join
# result ： Query the intersection of the result sets in two tables
# Example ： Query the information of the students who took the exam ( Student number , The student's name , Account No , fraction )
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
# Example ： Query the information of the students who took the exam ( Student number , The student's name , Subject name , fraction ) To use inner join nesting , Query account name by account number
SELECT s.StudentNo,StudentName,SubjectName,StudentResult
from student as s
inner join result as r
on s.StudentNo=r.StudentNo
inner join subject su
on r.SubjectNo=su.SubjectNo;

02 External connection

• The outer connection is divided into left outer connection and right outer connection

# The left outer join
# grammar ：left join
# Take the left table as a benchmark , The table on the right matches one by one , It doesn't match , Return the record in the left table , The table on the right shows NULL fill
# Example ： Check the student numbers of all students , The student's name , Subject name , fraction
# because student All the students in middle school ,result Middle school is the student who took the exam , So choose the left outer connection there left It refers to taking the left table as the benchmark , Rather than left join The following table serves as the left table
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
# result ： Those who don't take the exam will find out , Test scores and subject numbers are null Value padding

# Right connection
# grammar ：right join
# Take the right table as a benchmark , The table on the left matches one by one , It doesn't match , Return the record in the right table , On the left side of the table NULL fill
# Example ： Query the information of the students who took the exam ( Student number , The student's name , Account No , fraction )
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
# result ： Based on the right table , The table on the right shows the information of the students taking the exam . So the query can be completed .

03 Self join

# Self join ： The essence is to use one table as two .
# The query efficiency of self join is higher
# Example
# Create a table , In this table id Column properties are inconsistent , yes , we have id For the subject id Some are classified id
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ' subject id',
`typeId` INT(10) NOT NULL COMMENT ' Classification id',
`categoryName` VARCHAR(50) NOT NULL COMMENT ' name ',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
# insert data
INSERT INTO `category` (`categoryid`, `typeId`, `categoryName`)
VALUES('2','1',' information technology '),
('3','1',' software development '),
('4','3',' database '),
('5','1',' Art design '),
('6','3','web Development '),
('7','5','ps technology '),
('8','2',' Office information ');
# analysis ： The meaning of this data sheet is ：categoryId There are classifications in ID There are also topics ID, When classifying ID Corresponding typeId by 1 when , Corresponding categoryName For the classification name , If it's a project ID, Corresponding typeId!=1, here , Corresponding categoryName For the title of the project . So when making self join queries , Just use categoryId=typeId You can find the subject ID The corresponding classification ID.
# Self connect query ： Query the classification of the subject
SELECT a.categoryName AS ' subject ',b.categoryName AS ' classification '
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`typeId`
# It is equivalent to query in the following table ：
categoryid categoryName
2 information technology
3 software development
4 database
5 Art design
6 Web Development
7 PS technology
8 Office information
typeId categoryName
1 information technology
1 software development
3 database
1 Art design
3 Web open
5 PS technology
2 Office information

04 Sorting and paging

• Sort

# grammar ：ORDER BY
# ASC Represents ascending order DESC In descending order
# Example ： Check the students and their grades ( Student number , The student's name , Grade name ) In ascending order of student number
select StudentNo,StudentName,GradeName
from student as s
inner join grade as g
on s.GradeId=g.GradeID
order by StudentNo ASC ;

• Pagination

# grammar SELECT * FROM table LIMIT Starting page number , But it also shows the quantity
# Example ： Check the students and their grades ( Student number , The student's name , Grade name ) In ascending order of student number , From 6 Data starts , Show ten query results .
select StudentNo,StudentName,GradeName
from student as s
inner join grade as g
on s.GradeId=g.GradeID
order by StudentNo ASC
limit 6,10;

05 Subquery

Subquery ： In a query statement WHERE In the conditional clause , Another query statement is nested , Nested queries can consist of multiple subqueries , The solution is from inside to outside

# Example ： check C Language -1 Before 5 The grade information of students ( Student number , full name , fraction )
select r.studentNo,s.StudentName,r.StudentResult
from result as r
inner join student as s
on r.StudentNo=s.StudentNo
where r.SubjectNo=(select su.SubjectNo
from subject as su
where su.SubjectName='C Language -1' )
order by r.StudentResult desc
limit 0,5;