MySQL belongs to a relational database, the relationship between tables can be established, such as: student table and score table, add student number in the score table reference student number, so in the score table do not need to add duplicate student information, this relationship is also called primary foreign key relationship, can be set by setting foreign key constraint implementation. Add a foreign key constraint to a table to ensure referential integrity between tables. Add a foreign key constraint to a table to ensure referential integrity between tables.

Create table table name (table name type constraint,... , constraint foreign key name Foreign key references primary tableCopy the code

Code examples:

use mydb; Drop table if exists tb_score; create table tb_score ( score_id int primary key auto_increment, score_stu_id int, score int, score_course varchar(20), constraint fk_score_stu_id foreign key(score_stu_id) references tb_student(stu_id) );Copy the code

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

1. The left outer join is used to query the data of multiple tables and display all the data in the left table. If the data in the right table does not match the data in the left table, null is added. Grammar:

The select field... From left table left join right table on main table. Primary key = child table. Foreign keys;Copy the code

Code examples:

-- Left outer connection, Select s.tu_id, s.tu_name, c.core_course, c.core from tb_student s left join tb_score c on s.tu_id = select * from tb_student s left join tb_score c on s.tu_id = c.score_stu_id;Copy the code

Query all students who have taken exams

select s.stu_id,s.stu_name,c.score_course,c.score from tb_student s
 left join tb_score c on s.stu_id = c.score_stu_id where c.score is not null; 
Copy the code

Select s.tu_id, s.tu_name, c.core_course, c.core from tb_student s left join tb_score c on s.tu_id = select * from tb_student s left join tb_score c on s.tu_id = c.score_stu_id where c.score is null;Copy the code

2) Right outer connection

In contrast to the left join, all data from the right table is displayed, and inconsistencies in the left table are nulled.

Grammar:

The select field... From left table right join right table on main table Primary key = child table. Foreign keys;Copy the code

Code examples:

select s.stu_id,s.stu_name,c.score_course,c.score from tb_score c right join tb_student s 
on s.stu_id = c.score_stu_id;
Copy the code

Sometimes the use of comparison operators can cause errors when there is more than one query result in a subquery, so we need to use some keywords to help filter the results. The in keyword is used to equal either of the fields or data in the list. Code examples:

Select * from student where name and grade have been used; select * from student where name and grade have been used Select stu_name,score_course,score from tb_student inner join tb_score on tb_student.stu_id = select stu_name,score_course,score from tb_student inner join tb_score on tb_student.stu_id = Tb_score. Score_stu_id WHERE score =' score 'and score in(select score from tb_score where score =' score ');Copy the code

The any operator is used with the comparison operator. If the field is compared to any of the query results, the result is valid. Grammar:

Field comparison operation any(query result)Copy the code

Code examples:

Select stu_name,stu_address from tb_student where stu_address = 'wuhan' and stu_age > any(select * from tb_student where stu_address = 'wuhan' and stu_age > any Stu_age from tb_student where stu_address=' 98 ');Copy the code

In this chapter we learned the inner join, outer join, sub-query and other advanced query methods, sometimes the comprehensive use of these query methods is a certain difficulty, when we are familiar with them, query data is not difficult. Click “like” if it works 🙂