Examples of SQL table relationships and connection query applications

Source: Internet
Author: User

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 ......

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.