MySql multi-table design and multi-table query in plain English

MySql multi-table design and multi-table query in plain English

This article as MySql introduction, easy to understand, very suitable for a bit of MySql database foundation friends to read.

As is known to all, the database in our actual development has played a vital role, in today’s era of information explosion, the rapid development of various industries, with the increasing of data, it needs a good container to store the data, MySql is a relatively good database, and more used in the enterprise. Today we will introduce the MySql database multi-table design and multi-table query.

1. Multi-table design

Because MySql database is a relational database, there is an association between tables, so we design several database tables, we need to find the relationship between them, the relationship between multiple tables includes these several: One-to-one, one-to-many and many-to-many relationships, while in our actual development, the most common relationships are one-to-many and many-to-many, one-to-one relationships are relatively rare. Let’s look at one-to-many and many-to-many relationships.

A couple more: First of all, we want to know, one-to-many is refers to is a one-to-many relationship between two tables, actually this kind of relationship in our life is very easy to find, for example, in ancient times, China’s ancient system is polygamy, if we put the husband as a husband and wife as a wife table, that in ancient times, this would be a one-to-many relationship between the two tables. For example, the relationship between the user and order, a user can have multiple definitions, an order only belong to that a user, so we can put the user as a user list, the order as an order form, the relationship between them is not also a one-to-many relationship, so in our life, this relationship is also everywhere, then let’s to design, Design two tables that have a one-to-many relationship and have some kind of relationship between them.

User table: User computer table: Computer Known: a user can have multiple computers, one computer belongs to only one user

Create a user table:

create table user (id int primary key auto_increment, name varchar(20) ); Create a computer table:

Create table computer (id int primary key auto_increment, name vARCHar (20), uid int, Select * from user; select * from user; select * from user; Finished two tables have been created, but it’s not over, because of the relations between the two form at present is not wool, no constraints, in this the absence of constraints, we give two tables operation data can be arbitrarily, so that relations between the two tables is not nothing, also talk about what a one-to-many, so we want to build relationships, relationship building format is as follows.

Establish a foreign key association:

alter table computer add constraint uid_key foreign key (uid) references user(id); We can also create a one-to-many relationship between the two tables by creating a foreign key association at the same time as the computer table.

Create a computer table:

create table computer ( id int primary key auto_increment, name varchar(20), uid int, constraint uid_key foreign key (uid) references user(id); ) ; After setting the foreign key association, if we want to delete the user table is unable to delete successfully, because it has been associated with computer, but we can delete computer table, if computer table is deleted, we then delete the user table, it can be successfully deleted.

Many-to-many: many-to-many is a kind of what relationship, we might as well continue to look for this relationship, in real life, for example, the relationship between the students and the teacher, a student can be a lot of a teacher, a teacher can teach many students, if that was a one-to-many relationship, certainly yes, so let’s take the case example.

One student can be taught by many teachers, and one teacher can teach many students

Create a student table:

create table student ( id int primary key auto_increment, name varchar(20) ); Create a teacher table:

Create table teacher (id int primary key auto_increment, name vARCHar (20)); Well, when we write here, if the increase in the student list a column, that means a student can only corresponding to a teacher, a teacher can N corresponding to many students, and so it is a one-to-many relationship, that if you add a column to the teacher in fields, no increase student list, it became a teacher can only for a one of the students, A student can correspond to multiple teachers, so no matter how to add a field is not good, so this time need a third association table, as the student table and the teacher table association table.

Associative table:

create table t_s ( tid int, sid int, primary key(tid, sid), constraint t_key foreign key (tid) references teacher(id), Constraint t_key Foreign Key (TID) References teacher(ID) Constraint t_key Foreign Key (TID) references teacher(ID)

2, multi-table query

MySql multi-table query refers to query multiple tables, will meet certain conditions of the table in the data query, MySql multi-table query classification is not much, a total of several categories:

Cartesian product query outer link query: left outer join query, right outer link query inner join query: implicit inner join query, display inner join query Sub-query example: Suppose there are two tables A and B, the relationship between the two tables is one-to-many

Create table A;

Create table A (id int primary key auto_increment, name varchar(20)) where id int primary key auto_increment;

insert into a values(1, ‘aaa’); insert into a values(2, ‘bbb’); insert into a values(3, ‘ccc’); Create table b;

Create table B (id int primary key auto_increment, name varchar(20))

Insert into b values(1, ‘三’); Insert into B values(2, ‘三 ‘); Insert into b values(4, ‘王五’); Select * from table_name where table_name = ‘table_name’;

select

select * from a, b; Cartesian product queries are the simplest multi-table queries, but they are also the least useful, because the query results are meaningless, unreferrable, and the data is messy and repetitive. The cartesian product is called cartesian product because the results of the query and the results of the cartesian results when we studied mathematics, interspersed with the matching query effect. Results:

1 ‘aaa’ 1 ‘zhang’ 2 ‘BBB’ 1 ‘zhang’ 3 ‘CCC’ 1 ‘zhang’ 1 ‘aaa’ 2 ‘and’ 2 ‘BBB’ 2 ‘and’ 3 ‘CCC’ 2 ‘and’ 1 ‘aaa’ 4 ‘Cathy’ 2 ‘BBB’ 4 ‘Cathy’ 3 ‘CCC’ 4 ‘wang5’ inner join query:

Implicit inner join: SELECT

External link query:

Left outer join: select

Left outer join: select

Ex. :

Select name from a where id=(select id from b where name=’ aaa ‘); select name from a where id= ‘aaa’; Results: ‘aaa’ above several query format is more tables in MySql query is simpler, and without the complex query statements, of course, today’s interpretation of this a few queries is also the most basic introductory MySql query statements, in the actual development, there are a lot of a lot of complex query structure, so later also need to do some more exercises to consolidate multi-table queries. If friends can master the above content, that MySql multi-table design and multi-table query is also a starter.

The above content is this chapter to explain to you the knowledge point, the content is not much, but can help you especially zero-based friends learn MySql multi-table design and multi-table query, finally wish each learning MySql friends quickly to success, to the next level.