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

This time, I was lucky enough to help my teacher and sister with basic maintenance of the examination system. The direct point is to manage the examination staff, add, delete, modify, and query. The most basic thing is to query the students, it is used to check the information given by the teacher, and finally determine the person taking the test. This problem occurs during the Query Process:

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 see whether the information in the view is one-to-one with the English information table given by the teacher.

After busy for half a day, I finally made a right outer connection to the table based on my sister's ideas. 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 following results are displayed:

Why is this happening? The reason is of course very easy, because the data in the t_english table is directly imported in accordance with the Excel table given by the teacher, so assume that the external connection will show the equivalent amount of data, but for some reason, why? I forgot this student when I got the basic information, or I just deleted it when I switched to a major, but I forgot to add it to my major? So that the basic student table is empty.

Through the connection operation of the basic table, a simple query is performed, and then a comparison is performed to find that these five pieces of data 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:

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 made me familiar with SQL queries and so on. I am not sure if I am familiar with practical knowledge! Although it is only a small knowledge point, through this basic information maintenance, we have made myself more clear that these basic learning must be solid! This knowledge seems simple, but if you do not understand it, it will actually greatly reduce your query efficiency!

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