Brief introduction
In the actual situation, for example in a university, there are many teachers, teachers have their own research direction and title. And, perhaps not every teacher has a graduate student, if a new teacher, may not have brought graduate students. So, if the leader asks to find out all the teachers with the number of graduate students (no graduate students with 0), then you need to use the left/right outer connection of the query.
In this paper, we will analyze and give a practical solution to the similar situation.
The table structure of this article:
- Graduate student table structure:
CREATE TABLE ' g_student ' (
' GID ' int (one) not NULL auto_increment,
' Name ' varchar (DEFAULT NULL),
' Gender ' char (1) DEFAULT NULL,
' Start_time ' Date DEFAULT NULL,
' Tid ' int (one) DEFAULT NULL,
PRIMARY KEY (' gid '),
KEY ' Fk_student_teacher ' (' tid '),
CONSTRAINT ' Fk_student_teacher ' FOREIGN KEY (' tid ') REFERENCES ' teacher ' (' tid ')
) Engine=innodb auto_increment=5 DEFAULT Charset=utf8;
CREATE TABLE ' Teacher ' (
' Tid ' int (one) not NULL auto_increment,
' Name ' varchar (DEFAULT NULL),
' job_title ' varchar DEFAULT NULL,
' Major ' varchar DEFAULT NULL,
PRIMARY KEY (' tid ')
) Engine=innodb auto_increment=6 DEFAULT Charset=utf8;
Insert INTO ' teacher ' (' tid ', ' name ', ' Job_title ', ' major ') values
(1, ' Shen ', ' Professor ', ' image '),
(2, ' Liu ', ' Professor ', ' database '),
(3, ' Yellow ', ' associate Professor ', ' machine learning '),
(4, ' Jarvis ', ' Lecturer ', ' Multimedia '),
(5, ' King ', ' associate Professor ', ' machine learning ');
Insert INTO ' g_student ' (' gid ', ' name ', ' Gender ', ' start_time ', ' tid ') values
(1, ' A ', ' Male ', ' 2015-01-01 ', 1),
(2, ' B ', ' Male ', ' 2015-09-01 ', 2),
(3, ' C ', ' Male ', ' 2015-09-01 ', 3),
(4, ' D ', ' female ', ' 2015-09-01 ', 1);
Querying items
- Please find out the number of graduate students each tutor has.
Analysis:
Not every teacher has a graduate student, so the number of graduate students without a graduate student should be 0. External connections are therefore required
SELECT COUNT (gs.name), T. ' Name ' from G_student GS right JOIN teacher T
On GS. ' Tid ' =t. ' Tid '
GROUP by GS. ' Tid ';
Here, if you want to use a LEFT join, then the positions of g_student and teacher need to be exchanged
SQL query--sql left join/sql right JOIN