MySQL. Links between tables. JOIN operator
In last lesson we created “sport” database and added three tables (countries, sports and teams). Furthermore we created links between tables with foreign keys. But we didn’t take into consideration one problem – how we can to delete or update data in these tables. In this lesson we’ll modify our database and make some queries simultaneously
If you want to update or delete data in one table, and this table links with another table, you need to write on update cascade on delete restrict while foreign key declaration. In this situation we mean, while update data in child table data in parent table will be update. But deleting restricts. Other variants are possible, for example, set null – while deleting in child table null value will be set in parent table.
Also we’ll consider inner join, left join and right join. Result will be the set of data from two tables.
All data from first table and data which satisfy condition from the second table will be in result set while left join. Also data with null value from the second table will be in result set
Also we’ll consider inner join, left join and right join. Result will be the set of data from two tables.
All data from first table and data which satisfy condition from the second table will be in result set while left join. Also data with null value from the second table will be in result set
Code lesson
create table countries ( country_id int unsigned not null primary key auto_increment, country_name varchar(255) not null );
insert into countries (country_id, country_name) values (1, 'Россия'),(null, 'Англия'),(null, 'Испания'),(null, 'Италия'), (null, 'Германия'), (null, 'Франция');
create table teams ( team_id int unsigned not null primary key auto_increment, country_id int unsigned not null, team_name varchar(255), foreign key(country_id) references countries(country_id) on update cascade on delete restrict);
insert into teams (team_id, country_id, team_name) values (1, 1, 'ЦСКА'), (null, 2, 'Манчестер Юнайтед'), (null, 3, 'Реал Мадрид'), (null, 4, 'Лацио'), (null, 5, 'Бавария'), (null, 6, 'ПСЖ');
create table players (player_id int unsigned not null primary key auto_increment, team_id int unsigned, country_id int unsigned not null, player_name varchar(255), foreign key(team_id) references teams(team_id) on update cascade on delete set null, foreign key(country_id) references countries(country_id) on update cascade on delete restrict);
0 Comments