MySQL Multi-table summary

1. Creation syntax for multiple tables

ALTER TABLE from the table name add constraint foreign key name foreign key (from the table class name) references Main Table name (Main table column);

Prepare the data:

CREATE DATABASE One2many; Use One2many; #创建学生表CREATE TABLE stu (stuid varchar (+) PRIMARY key,name varchar (+), number INT, adress varchar (+)); Inser T into Stu values (' s001 ', ' Huluwa ', 123456, ' Gourd Mountain '); INSERT into Stu values (' s002 ', ' Automan ', 654321, ' Hokkaido '); #创建成绩表CREATE Table score (scoid varchar (+), Chinese int,math int,english int,studentid VARCHAR (+)  #在成绩表中添加一列, storing student's table secondary school ID); Nsert into score values (' SC1 ', 100,90,90, ' s001 '); INSERT into score values (' SC2 ', 90,80,90, ' s002 '); INSERT INTO score VALUES (' sc3 ', 90,70,70, ' s001 '); INSERT into score values (' Sc4 ', 100,95,95, ' s002 ');

In the above two tables, the Seudentid class in the score table stores the information of the secondary school students in the Stu table, which can be found in the StudentID table by Stu values in the score table, and the information of the student corresponding to the grade is obtained by the value of the report. However, at this point two tables do not add a foreign key relationship, you can arbitrarily add the value of the data type from the table (this value does not have the corresponding information in the main table). The main table can be arbitrarily delete student information, deleted from the table will not be able to query, but the corresponding test scores are still there, simply said that the main table can It is possible to add data from a table, which is not compliant with business logic

Add a PRIMARY KEY constraint

ALTER TABLE score ADD CONSTRAINT stu_score FOREIGN KEY (StudentID) REFERENCES Stu (STUID);

When the primary table arbitrarily deletes the data (not deleted from the table) or arbitrarily adds data from the table (there is no corresponding value in the main table), an error occurs.------A FOREIGN KEY constraint fails

2. Multi-Table Query

1.

MySQL Multi-table summary