Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief description of the steps to create MySQL foreign keys. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the steps of creating MySQL foreign keys can bring you some practical help.

1. Create a tabl

Foreign key: FOREIGN KEY (ordersid) references orders (id)

In the process of building a table

Create table team (

Id int primary key auto_increment

Name varchar (40)

);

Create table star (

Id int

Name varchar (40)

Team_id int

Foreign key (team_id) references team (id)

);

Insert into team values (null,'Toronto Raptors'), (null,'Milwaukee Bucks'), (null,'Boston Celtics'), (null,'Golden State Warriors'), (null,'Oklahoma City Thunder'), (null,'Dallas Mavericks')

Insert into star values (2) Ke Huai-Leonard, 1), (7), (3), (34), (22), (22), (11) Owen, 3), (20) Hayward, 3), (35) Durant, 4), (30) Curry, 4), (0), (13) Paul-George, 5) (77) 'Luke-Dong Chi', 6), (41) 'Nowitzki', 6)

(2) if the table already exists, add a foreign key by modifying the table statement.

ALTER TABLE table name ADD constraint FK_ID# foreign key name foreign key (foreign key field name) references exterior table name (main field name)

(3) Delete foreign keys

Alter table table name drop foreign key foreign key name

(4) operate the correlation table

Many-to-many relationship: create a new third-party relationship table, save the primary key of the two tables as foreign keys, and store the corresponding relationship between the primary keys of the two tables to save the relationship between the two tables.

One-to-one: creating foreign keys from the table

two。 Join query

(1) Multi-table design multi-table query

Select * from team,star; # result of multiplication of two tables

Select * from team,star where team.id = star.team_id; # filtering

~ Internal connection: natural connection

SELECT query field FROM Table 1 [INNER] JOIN Table 2 ON Table 1. Relation field = Table 2. Relation field

Select * from team inner join star on team.id = star.team_id

~ left outer join query: add records in the upper left table but not in the right table on the basis of the inner link

Select * from team left join star on team.id = star.team_id

~ right outer join query, adding records in the upper right table but not in the left table on the basis of the inner link

Select * from team right join star on team.id = star.team_id

~ full join query:

Select * from team full join star on team.id = star.team_id;#mysql is not supported

But union is supported.

Select from team left join star on team.id = star.team_id

Union

Select from team right join star on team.id = star.team_id

~ query the name of the No. 3 team and the names of the players in it

Select * from team inner join star on team.id = star.team_id where team_id = 3

Select team.name team name, star.name player from team inner join star on team.id = star.team_id where team_id = 3

3. Subquery

(1) for a subquery with the IN keyword, the inner query statement returns only one data column, and the values in this data column will be compared by the outer query statement, that is, nested query.

~ query the department to which players with numbers greater than 20 belong

Select name from team where id in (select team_id from star where id > 20)

~ query the department to which players not greater than 20 belong

Select name from team where id not in (select team_id from star where id = 20)

(2) subquery with EXISTS keyword: the subquery does not return any data, but only Ture or False. External query is executed only when it is Ture.

Select * from team where exists (select team_id from star where id > 20) # the field names of both tables should be the same

(3) subquery with any keyword: the ANY keyword means that any one of these conditions is satisfied.

Select * from team where id > any (select team_id from star where id = 20)

(4) subquery with ALL keyword: all conditions of internal query need to be satisfied at the same time.

Select * from team where id > all (select team_id from star where id > 20)

Select * from team where id > all (select team_id from star where id

= =

Select * from team where id > (select team_id from star where id = 0)

Create MySQL foreign key steps to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.