09-Mysql database — variants of foreign keys

create table press(
    id int primary key auto_increment,
    name varchar(20)
);

create table book(
    id int primary key auto_increment,
    name varchar(20),
    press_id int not null,
         constraint fk_book_press foreign key(press_id) references press(id)
    on delete cascade
    on update cascade
);

# First, insert the record into the associated table.Insert into press (name) values("Beijing industrial landmine press")(people's music is not good at publishing house).(intellectual property rights no use Publishing House);Go to the association table again.insert recordInsert into book (name, press_id) values(Joyoung magic, 1)("Jiu Yin Zhen Jing", 2)('nine Yin white bone claw ', 2).("Gu Gu Jiu Jian", 3)("dragon ten slap")', 2),("sunflower treasure Dian", 3);Query results:Mysql> select * from book;+ + + + - -- + - + +ID nAme press_id+ + + + - -- + - + +1, Joyoung, 1.2The 2 channels of the nine Yin classics3, 2 Yin and white bone claw.4, only 3.5Ten palm, 2.6, sunflower treasure Dian 3+ + + + - -- + - + +6 rows inSet (0 sec)Mysql> select * from press;+ + - -- + - -- +ID nameIt+ + - -- + - -- +(1) Beijing industrial landmine press.It2. People's music is not good.3. Intellectual property rights are useless.+ + - -- + - -- +ThreeRows in set (0 sec)

# The author table of the associated table is created, and the previous book table has been created in the relationship of many to one.Create table author (Id int primary key auto_increment,NaMe varchar (20));This table keeps the relationship between the author table and the book table, that is to say, check the relationship between the two party to check the table.Create table author2book (Id int notNull unique auto_increment,Author_id int not null,Book_id int not null,Constraint fk_Author foreign key (author_id) references author (ID)On delete cascadeOn update cascade,Constraint fk_book foreign key (book_id) references book (ID)On delete cascadeOn update CASCade,Primary key (author_id, book_id));Insert four authors, ID in turn.Insert into author (name) values ('egon'),'alex'), ('wusir'), ('yuanhao');The representative works of each writer.Egon: Joyoung Shen Gong, nine Yin Jing Jing, nine Yin bone claw, Gu Gu Jiu Jian, ten dragon palm, sunflower treasure.Alex: Joyoung magic, sunflower collectionWusIr: Gu Gu Jiu Jian, Jiang long ten palm, sunflower treasureYuanhao: Joyoung magicInsert the corresponding data in the author2book table.Insert into author2book (author_id, book_id)Values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(2,1),(2,6),(3,4),(3,5),(3,6),(4,1);You can do it now.Check author2book's correspondence with author and book.Mysql> select * from author2book;+ + - -- + - -- + - -- +ID autHor_id book_id+ + - -- + - -- + - -- +1, 1, 1,2, 1,23, 1, 3,4, 1, 4,5, 1, 5,61, 6,7, 2, 1,8, 2, 6,9, 3,410, 3, 5,11, 3, 6,12, 4, 1,+ - --+ + - -- + - - +12 rows in set (0 sec)

#For example, a user can only register a blog.Two tables: user tables (user) and blog tables (blog).Create user tablesCreate table user (Id int primary key auto_increment,Name varchar (20));Create a blog tableCreate table blog (Id int primary key auto_increment,URL varchar (100),User_id int unique,Constraint fk_user foreign key (user_id) referencEs user (ID)On delete cascadeOn update cascade);Insert records in user tablesInsert into user (name) values('alex'),('wusir'),('egon'),('xiaoma');Insert a blog entry record.Insert into blog (URL, user_id) values('http://www.cnblog/alex', 1)('http://www.cnblog/wusir', 2).('http://www.cnblog/egon', 3).('http://www.cnblOg/xiaoma', 4);Search for wusir's blog addressSelect URL from blog where user_id=2;