Oracle multi table query intermediate table creation example tutorial

Time:2022-5-13

Creation of intermediate table for Oracle multi table query

Classification of relationships between tables:

  1. One to one (understanding):
* e.g. person and ID card
* analysis: a person has only one ID card, and one ID card can only correspond to one person
  2. One to many (many to one):
* e.g. departments and employees
* analysis: a department has multiple employees, and one employee can only correspond to one department
  3. Many to many:
* e.g. students and courses
* analysis: a student can choose many courses, and a course can also be selected by many students

Specific implementation method:

  1. One to many (many to one):
* e.g. departments and employees
* implementation method: establish a foreign key on one of the multiple parties and point to the primary key of one party.
  
  2. Many to many (key):
* e.g. students and courses
* implementation method: the third intermediate table is needed to realize the many to many relationship. The middle table contains at least two fields, which are used as the foreign keys of the third table and point to the primary keys of the two tables respectively
  
  3. One to one (understanding):
* e.g. person and ID card
* implementation method: one-to-one relationship. You can add a unique foreign key on either side to point to the primary key of the other party.

Detailed and typical examples are as follows

User table user

CREATE TABLE USER(
id VARCHAR(32) PRIMARY KEY,
email VARCHAR(50) UNIQUE NOT NULL,
username VARCHAR(50),
PASSWORD VARCHAR(50)

)
Insert into user (ID, email, username, password) value ('1 ',' 111 ',' Xiaoming ',' 11111 ')
Insert into user (ID, email, username, password) value ('2 ',' 222 ',' Xiaohong ',' 22222 ')
Insert into user (ID, email, username, password) value ('3 ',' 333 ',' Xiaozhang ',' 33333 ')

The query result is

用户表详情

Role table

CREATE TABLE role(
id VARCHAR(32) PRIMARY KEY,
roleName VARCHAR(50) ,
roleDesc VARCHAR(50)
)

Insert into role (ID, rolename, roledesc) value ('1 ',' engineer ',' 111 ')
Insert into role (ID, rolename, roledesc) value ('2 ',' doctor ',' 222 ')
Insert into role (ID, rolename, roledesc) value ('3 ',' teacher ',' 333 ')

The query result is

角色表详情

Intermediate table


CREATE TABLE users_role(
userId VARCHAR(32),
roleId VARCHAR(32),
PRIMARY KEY(userId,roleId),
FOREIGN KEY (userId) REFERENCES users(id),
FOREIGN KEY (roleId) REFERENCES role(id)
)

INSERT INTO users_role(userId,roleId )VALUE('1','2')
INSERT INTO users_role(userId,roleId )VALUE('2','2')
INSERT INTO users_role(userId,roleId )VALUE('3','3')

The query result is

中间表详情

Query by associating intermediate tables

Pay special attention to the writing of this SQL

The query result will be based on the ID of the intermediate table (it must be understood)

--The query result will be based on the ID of the intermediate table
SELECT * FROM users a ,role b, users_role c WHERE a.id=c.userid AND b.id=c.roleid

The query result is:

进行表的多对多查询的结果

summary

This is the end of this article about the creation of Oracle multi table query intermediate table. For more information about the creation of Oracle multi table query intermediate table, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!

Recommended Today

Application security of JSP

1、 OverviewWhen network programming becomes more and more convenient, the system function becomes more and more powerful, but the security decreases exponentially. I’m afraid this is the misfortune and sadness of network programming. Various dynamic content generation environments have prospered www. their design goal is to give developers more power and end users more convenience. […]