cool hit counter Multi-Table Queries and Foreign Key Constraints for MySQL Databases_Intefrankly

Multi-Table Queries and Foreign Key Constraints for MySQL Databases

Warm tips: article dry, suggest bookmarking ~

I've seen a lot of messages from my friends.

Want to learn about MySQL multi-table queries ~

So let's talk today.

Multi-Table Queries for MySQL

Before we start let's add a bit of knowledge

foreign key constraint

While the constraint relationships we shared earlier belonged to single table constraints, external check constraints belong to constraint relationships between multiple tables ~

Table-to-table relationships

Before querying it we need to know that in a database there are generally many data tables to store information and the following relationships will exist between the tables.

1) One-to-many relationships.

For example.

There are many students in a class. There are many employees in a department.

Principles of table construction.

Create a field in the slave table (the side of many) that is made a foreign key pointing to the primary key of the master table (the side of one).

2) Many-to-many relationships.

For example.

A student needs to take many courses, and a course has many students taking it. Then there are the people and the characters.

Table-building principles.

Create an additional table that will serve as an intermediate table. The remaining two tables are primary tables, and the intermediate table contains at least two fields as foreign keys that point to the primary keys of each of the two primary tables.

3) One-to-one relationships.

For example.

We always say one carrot, one hole~

Table-building principles.

This can be seen as a special one-to-many relationship, built on the same principles as a one-to-many relationship, and can also be written in a table ~

Adding foreign key constraints

First create a few tables and write the data ~

student form

class timetable

First, it's a one-to-many relationship

class timetabletogether with student form( We have created student form The foreign key field was added when theclass_id, If you forgot to add it when you built the table, Fields can be added using what we've learned before ha~)

Adding foreign key constraints


alter table from table name add [constraint foreign key name] foreign key(field of foreign key from table) references master table name(primary key of master table);

If you don't remember the name of the foreign key you can use

show create table Table name;

to find the foreign key name

removing foreign key constraint


alter table table name drop foreign key foreign key name;

The role of foreign key constraints

1) Slave tables cannot be added, data that does not exist in the master table ~

(2) The master table cannot delete data referenced in the slave table

As for how a many-to-many relationship should create foreign keys I'll leave it to the guys to practice on their own~

Let's get to the main topic of the day

Multi-Table Query

1) Cross-query (basically won't be used)

select * from A,B;

The result is the product of two tables that will have some data in them that we don't need at all.

2) Inner join query

Use the keyword inner join, inner can be omitted.

The inner join query will display the data in both tables, where the relationship exists, as a query.

If we add a class as "Class 4" in the main table, but do not add student data for Class 4 in the slave table, then when using the inner join query, it will not display the information record of Class 4 ~

Implicit inner join query

Explicit inner join query

3) External Link Search

Using the keyword outer join, outer can be omitted.

Left outer join query

Right outer join query

Left outer join query will display all the data in the table on the left, and added to the table bynull to form a complete line of data, Right outer join query on the other hand...~

4) Sub-queries

The result of one select statement as part of another select statement

So that's it for today~

See you next time, guys.

I'm a newbie Nana.

Aspire to be a veteran driver who doesn't roll over

Please give me more guidance on the way to learn Java

Leave a comment on the post with any questions, Na is watching!

Collaboration,Contribution,Reprint,Copyright Contact Lina: Lina_Java

1、Super Space Saving Ideas for Server Log Backups
2、Five years for four executives 6000 employees cut 95 left 300 people why Wang Jianlin abandoned Wanda Netco
3、Derivation of logistic logistic regression formula and R language implementation
4、AcademiaLet the machine have its ear MIT proposes crossmodal machine learning model

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送