Multi-table management for SQL statements and SQL statement management

Multi-table management for SQL statements and SQL statement management
Multi-table management of SQL statements

A database usually has more than one table. Sometimes we need to associate multiple tables, which requires multi-table management statements.

 

1. Foreign key constraints

A non-primary key field in a table. If it is a primary key in another table, this field is called as a foreign key.

 

For example, there are two tables:

Student table-student ID, name, gender, class ID

Class table-class number, number of students

 

In the student table, the student ID is the primary key; in the class table, the class number is the primary key, and the 'class number 'in the student table is not the primary key. Therefore, the class number is a foreign key for the student table.

 

Statement: CONSTRAINT student_class_fk foreign key (classid) REFERENCES class (cid)

The foreign key name and sub-table attributes are bound to the master table attributes.

Create a master table first:

Create table class ( Cid int primary key, Ccount INT );

 

Then create a sub-table to add the foreign key:

Create table student ( Sid int primary key, Sname VARCHAR (20 ), Sex VARCHAR (4 ), Classid INT, CONSTRAINT student_class_fk foreign key (classid) REFERENCES class (cid) );

 

Foreign key:

 

 

2. Cascade operations

If you want to change the value of the primary key in the primary table and then change the value of the foreign key in the secondary table, cascade operations are required.

 

2.1 cascade update

CONSTRAINT student_class_fk foreign key (classid) REFERENCES class (cid) on update cascade -- cascade update (updates the data in the primary table and changes the secondary table)

 

2.2 cascading Deletion

CONSTRAINT student_class_fk foreign key (classid) REFERENCES class (cid) on delete cascade -- cascading deletion (deleting the data in the primary table will DELETE the secondary table)

 

3. Multi-Table query 3.1 cross Query

-- 2.1 cross query (generate Cartesian Product: Total records of Table 1 * Total records of table 2) (not expected) SELECT sid, cid FROM student, class;

 

Connection within 3.2

 

-- Connection Query within 2.2 (most frequently used) -- Feature: data must be displayed only when the table conditions are met (excluding null values) -- Multi-Table query ideas: 1) determine which tables are available 2) determine which fields are queried 3) determine the relationship between tables (condition data: N-1) -- Requirement: query the name and number of students in the first class. SELECT s. sname, c. ccount -- what fields FROM student s, class c -- tables WHERE s. classid = c. cid; -- Table Condition   SELECT s. sname, c. ccount FROM student s Inner join class c -- inner join: inner join query ON s. classid = c. cid;

 

3.3 left Outer Join

-- 2.3 left Outer Join -- Requirement: query which classes have students -- Left outer join query: use the table data on the left to match the table data on the right. If the table data on the right matches successfully, a record is displayed. If the match fails, null is displayed. SELECT s. sname, c. cid FROM s. sname Left outer join class c ON s. classid = c. cid;

 

3.4 Right Outer Join

 

-- 2.4 right outer join: opposite to left Outer Join -- Right outer join query: use the table data on the right to match the table data on the left. If the table data on the left matches successfully, a record is displayed. If the match fails, null is displayed. SELECT s. sname, c. cid FROM class c Right outer join s. sname ON s. classid = c. cid;

 

3.5 self-connection

-- 2.5 self-join (the current table is connected to the current table) Auto join virtualizes two identical tables in the same table, creates an alias, and connects two tables for query. SELECT e. ename, B. ename FROM employee e -- employee table Left outer join employee B-supervisor table ON e. boosId = B. eid;