Basic Maintenance of the examination system-connection to the basic table

This time, I was lucky enough to help my sister with basic maintenance of the examination system. The direct point was to manage the examination staff, add, delete, modify, and query. The most important thing was to query the students and check the information given by the teacher, finally, confirm the examination personnel. During the query process, they encountered such a situation:

When checking the English examination personnel, a related view is created to facilitate the query. The tables involved in this view include tb_student (basic student information ), tb_executiveclass (the class involved), tb_departmentname (Major), tb_collegename (school ), tb_english mainly refers to the establishment of an overall information table based on the basic information of students and the information related to the college and English exams. The first part of information check is to check whether the information in the view is exactly the same as the English information table given by the teacher.

After busy for half a day, I finally made a right outer connection to the table according to the idea of my sister-in-law. I checked the table based on the student's English classification information:

The related statements are as follows:

Select distinct
DBO. tb_student.studentname, DBO. tb_student.studentcode, DBO. tb_student.sex, DBO. example, DBO. tb_department.departmentname,
DBO. tb_college.collegename, DBO. tb_grade.gradename, DBO. tb_engish.engisfenji, DBO. tb_engish.engishclass
From DBO. tbr_depcollegelink inner join
DBO. tb_department on DBO. tbr_depcollegelink.mongomentid = DBO. tb_department.mongomentid inner join
DBO. tb_college on DBO. tbr_depcollegelink.collegeid = DBO. tb_college.collegeid inner join
DBO. tbr_executiveclassdeplink on DBO. tb_department.departmentid = DBO. tbr_executiveclassdeplink.departmentid inner join
DBO. tbr_stuexecutiveclasslink inner join
DBO. tb_executiveclass on DBO. tbr_stuexecutiveclasslink.executiveclassid = DBO. tb_executiveclass.executiveclassid inner join
DBO. tb_student on DBO. tbr_stuexecutiveclasslink.studentid = DBO. tb_student.studentid on
DBO. tbr_executiveclassdeplink.executiveclassid = DBO. tb_executiveclass.executiveclassid inner join
DBO. tbr_executiveclassgradelink on DBO. tb_executiveclass.executiveclassid = DBO. tbr_executiveclassgradelink.executiveclassid inner join
DBO. tb_grade on DBO. tbr_executiveclassgradelink.gradeid = DBO. tb_grade.gradeidRight Outer Join
DBO. tb_english on DBO. tb_student.studentcode = DBO. tb_english.studentcode

The result is as follows:

Why is this happening? The reason is, of course, very simple. Because the data in the t_english table is directly imported into the Excel table given by the teacher, the equivalent amount of data will appear if external connections are used, however, for some reason, when I first added the basic information, I forgot the student, or I only deleted the student after I switched to a major, but I forgot to add it to the basic student table, and so on. As a result, the basic student table is empty, this is why.

A simple query is performed through the connection operation of the basic table, and then a comparison is performed to find that these five data entries are real. Ah! This time it really helped me a lot.

In fact, the right outer join is only a connection type in the basic table. In addition, the connection type of the data table is divided:

1) Internal Connection: equivalent connection

2) external connection:Check whether Table A or table B is based on the information of Table A. On the contrary, use the right outer join. Full outer join is not restricted for both tables.

3) Natural join: The join occurs in a table.

The following is an example to describe in detail:

Table tb_student and table tb_english

For natural connections, it is generally used in the case of public attributes. If the two links do not have public attributes, the natural connections are converted to the Cartesian Product for operation.

Decathlon product:

SELECT     dbo.TB_English.studentCode, dbo.TB_English.englisfenji, dbo.TB_Student.StudentCodes, dbo.TB_Student.StudentNameFROM         dbo.TB_English CROSS JOIN                      dbo.TB_Student

Display Effect:

Internal Connection:

SELECT     dbo.TB_English.studentCode, dbo.TB_English.englisfenji, dbo.TB_Student.StudentCodes, dbo.TB_Student.StudentNameFROM         dbo.TB_English <span style="color:#ff0000;">INNER JOIN</span>                      dbo.TB_Student ON dbo.TB_English.studentCode = dbo.TB_Student.StudentCodes

Left Outer Join:

SELECT     dbo.TB_English.studentCode, dbo.TB_English.englisfenji, dbo.TB_Student.StudentCodes, dbo.TB_Student.StudentNameFROM         dbo.TB_English<span style="color:#ff0000;"> LEFT OUTER JOIN</span>                      dbo.TB_Student ON dbo.TB_English.studentCode = dbo.TB_Student.StudentCodes

Outer right connection:

SELECT     dbo.TB_English.studentCode, dbo.TB_English.englisfenji, dbo.TB_Student.StudentCodes, dbo.TB_Student.StudentNameFROM         dbo.TB_English <span style="color:#ff0000;">RIGHT OUTER JOIN</span>                      dbo.TB_Student ON dbo.TB_English.studentCode = dbo.TB_Student.StudentCodes

All external connections:

SELECT     dbo.TB_English.studentCode, dbo.TB_English.englisfenji, dbo.TB_Student.StudentCodes, dbo.TB_Student.StudentNameFROM         dbo.TB_English <span style="color:#ff0000;">FULL OUTER JOIN</span>                      dbo.TB_Student ON dbo.TB_English.studentCode = dbo.TB_Student.StudentCodes

Summary:

This operation on basic information has made you familiar with SQL queries and so on. It is true that you are familiar with practical knowledge! Although it is only a small knowledge point, this basic information maintenance helps you better understand these basic learning needs to be solid! This knowledge seems simple, but if you do not know it, it will actually greatly reduce your query efficiency!