Examples of SQL table relationships and connection query applications

The examples in the books I read when I used to learn connection queries have no practical significance. I just put two tables together and only understood the syntax, but I don't know why I used connection queries, what are the benefits of connection query. Recently, when I was studying Hibernate and reviewing SQL, I thought of a particularly good case (experience: it is difficult to learn SQL statements without learning database programming, because most of the data in the database is designed for program calls, I don't understand the conversion process from the database to the application ).

The knowledge of SQL language learning is not deep, and SQL is the query language of relational databases. To learn SQL, you must start with the relationship. The so-called relationship is actually the relationship between two objects. For example, the two independent objects of students and courses are many-to-many relationships. The link between the two is that students choose courses, A new table can be created and selected. Complex queries and important knowledge points in SQL are usually concentrated on the new tables generated by two objects. Therefore, grasp the derived tables and work harder on the derived tables, you can learn SQL well. There is also a one-to-multiple relationship between similar objects. The two are independent and closely connected. For example, in the BBS system, the two are similar to the parent-subordinate relationship between people, two people are originally independent, but they are associated with each other because they are in one unit and work. (the opposite is the one-to-many relationship between students and classes. The two are very different ).

Case 1: electives (relationship between students and courses)

After going to college, there will no longer be paper transcripts. At the end of each semester, you will always check your score on the school's educational administration system. Now let's think about the connection query used to view the overall score. It involves two tables: Student table and student table. The student ID and name fields of the student table are used. The student ID, course number, and score fields of the student table are used. The simulation implementation is as follows:

Table Structure:

Score Table: Score	Student table: Students	Course schedule: courses

Internal join query result: the student table is connected to the internal join table, which is filtered by class.

Query statement	Display result
Select st. Name, st. classid, SC. courseid, SC. Score From students as St Inner join scores as SC On st. ID = SC. studentid Where St. classid = '13 ';

Result of left outer join query: The left Outer Join orders table of the student table, filtered by class

Query statement	Display result
Select st. Name, st. classid, SC. courseid, SC. Score From students as St Left Outer Join scores as SC On st. ID = SC. studentid Where St. classid = '13 ';

Outer right join: the result is the same as that of inner join, because in this example, the current score is not a student.

Query statement
Select st. Name, st. classid, SC. courseid, SC. score from students as St right Outer Join scores as SC On st. ID = SC. studentid where classid = 13;

Case 2: The curriculum (the relationship between the teaching table and the class table) is derived from the student management system case 3: The Movie Star table (the relationship between the film and the actors) is derived from the film management system case 4: the document category table (the relationship between articles and types) is derived from the blog system.

To be continued ......