Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example of multi-table association in MySQL database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Catalogue

Many-to-one correlation

Many-to-many association

One-to-one association

Fuzzy query

Table constraint

Association between tables

Fuzzy query

You can find the data we want according to the content provided. Unlike the = query, take char type data and varchar type data for example:

Create table C1 (x char (10)); create table c2 (x varchar (10)); insert C1 values ('io'); insert c2 values (' io')

Fuzzy query uses like

Select * from C1 where x like 'io';select * from c2 where x like' io'

It can be found that the x in C1 is of char type. We can't display it by vaguely querying whether there is io, but we can query it by =.

Fuzzy query is more accurate, in this way, you must enter all the contents of this field before you can query it. Here, the length of the data stored in the char type is less than 10, so you use a space supplement, so when you query, you need to take the space with it.

We can also use the query method provided to us by the fuzzy query, where% represents any 0 or more characters.

Select * from C1 where x like 'io%'

If we only know that the second bit is an o, but we don't know the beginning and end, we can use: _ to represent any single character, followed by% matching the following characters.

Select * from C1 where x like'_ o%'

The syntax of the SQL fuzzy query is

"SELECT column FROM table WHERE column LIKE'; pattern';".

SQL provides four matching patterns:

% represents any 0 or more bai characters. The statement is as follows:

SELECT * FROM user WHERE name LIKE'; 3%'

Name will be identified as "Zhang San", "three-legged cat", "Tang Sanzang" and so on.

_ represents any single character. Statement:

SELECT * FROM user WHERE name LIKE'; three

Only find out "Tang Sanzang" so that the name is three words and the middle word is "three".

SELECT * FROM user WHERE name LIKE'; 3 _'

Just find out "three-legged cat" so that name is three words and the first word is "three".

[] represents one of the characters listed in parentheses (similar to a regular expression). Statement:

SELECT * FROM user WHERE name LIKE'; [Wang Li] San'

Will find "Zhang San", "Li San" and "Wang San" (instead of "Zhang Li Wang San")

If there are a series of characters (01234, abcde, etc.) in [], they can be abbreviated as "0-4" or "Amure".

SELECT * FROM user WHERE name LIKE'; Old [1-9]'

Will find out "old 1", "old 2", …... , "Lao 9"

If you want to find the "-" character, please put it first:'; Zhang San [- 1-9]'

[^] represents a single character that is not listed in parentheses. Statement:

SELECT * FROM user WHERE name LIKE'; [Zhang Li Wang] San'

Will find out "Zhao San" and "Sun San" who are not surnamed "Zhang", "Li", "Wang", etc.

SELECT * FROM user WHERE name LIKE'; Old [^ 1-4]'

Will rule out "old 1" to "old 4" to find "old 5", "old 6", …... , "Old Nine".

Table constraint

Introduction:

Constraints, like the width of the data type, are optional parameters

Function: to ensure the integrity and consistency of data

It is mainly divided into:

PRIMARY KEY (contention) identifies the field as the primary key of the table, and the unique identification record FOREIGN KEY (FK) identifies the field as the foreign key of the table NOT NULL marks that the field cannot be empty UNIQUE KEY (UK) identifies that the value of the field is unique AUTO_INCREMENT identifies the value of the field as automatically growing (integer type and primary key) DEFAULT sets the default value for the field UNSIGNED unsigned ZEROFILL is filled with 0

Not null: literally, after setting, each time you insert a value, you must set a value for this field.

Default: if no value is set for this field, use a default value that we defined after default

UNIQUE KEY: after a field sets this constraint, only one (unique) field can exist in the whole table for the value it sets.

PRIMARY KEY: the primary key primary key is the basis on which the innodb storage engine organizes data. Innodb calls it an index organization table. There must be one and only one primary key in a table. The primary key is the only identification that can determine a record

AUTO_INCREMENT: when set, each time a value is inserted into the table, this field will automatically increase by a number, but this field must be an integer type and a primary key

FOREIGN KEY: foreign key that associates a field of this table with a field of another table, and the value of this field must correspond to the value of the associated field.

When we create a table, we usually have an id field as the index identifier and set it as the primary key and self-increment.

Example:

Create table test (id int primary key auto_increment, identity varchar (18) not null unique key,-- ID card must be unique gender varchar (18) default 'male'); insert test (identity) values ('123456789012345678')

When the identity field is inserted with the same value, an error is reported because the field has a unique value set

Insert test (identity,gender) values (0123456789012345678)

We will find that there is something wrong with id, that is because the author has inserted the value twice before, but the value has not been successfully inserted, but the self-increment has been affected.

At this time, we can solve this problem by doing two operations.

Alter table test drop id;alter table test add id int primary key auto_increment first

Delete the id field and reset it.

It's amazing, isn't it? the underlying mechanism of MySQL. Vary conscience

It is also important to note that when we delete a record using delete, it does not affect self-increment.

Delete from test where id = 2position insert test (identity,gender) values ('1111111111111111111111' male')

With regard to this operation, if we only delete a single record, we can also adjust the self-increasing value using the method provided in the above order, and if we are deleting the entire table record, use the following method:

Truncate test

Effect demonstration: delete deletes the entire table record

Effect demonstration: truncate deletes the entire table record

Joint primary key

Make sure that some of the fields set as the primary key have the same data

One of the purposes of the primary key is to determine the uniqueness of the data, the difference between it and the only constraint is that the only constraint can have a null value, but the primary key cannot have a null value, and the joint primary key means that when a field may have duplicate values and cannot determine the uniqueness of this data, add a word and two fields join together to determine the uniqueness of this data. For example, you mentioned that id and name are joint primary keys. When inserting data, when the id is the same, the name is different, or the id is different, and the name is the same, the data is allowed to be inserted, but when the id and name are the same, the data is not allowed to be inserted.

Example:

Create table test (id int, name varchar (10), primary key (id,name)); insert test values (1dint 1)

If you insert data with the same primary key again, an error will be reported.

As long as you set the two fields of the primary key, there is no problem that the data is not exactly the same in one record.

In the case of foreign keys, we demonstrate the association between tables.

Association between tables

Let's first introduce the association between tables, and then we will learn about join table queries.

Associate multiple tables through a field or through a table.

Can't we deal with one form? why should we be related, like this?

Have you found a problem? some employees correspond to the same department. A table repeats records many times. With the increase in the number of employees, there will be more and more duplicate records, which takes up more space.

So we need to use a separate table for the department, and then associate the employee with one field to another table. We can use foreign keys or not. Let's demonstrate the benefits of foreign keys first.

Many-to-one correlation

For example, multiple employees correspond to one department.

Employee table, don't create it in a hurry, please look down

Create table emp (id int primary key auto_increment, name varchar (10) not null, dep_id int, foreign key (dep_id) references dep (id) on update cascade # cascade update on delete cascade); # cascade deletion

The function of the foreign key above is:

The dep_id field is associated with the id field of the dep table:

When the id field value of the dep table is modified, it will be changed together if there is a value under the dep_id field of the table that has the same value as the id of the dep table.

If the dep table deletes a record, when the dep_id of the emp table is aligned with the id value of the delete record of the dep table, the emp table record will also be deleted.

Note: the foreign key must already exist, so you need to create a department table before creating an employee table

Department table

Create table dep (id int primary key auto_increment, name varchar (16) not null unique key, task varchar (16) not null)

The data set in the dep_id field of the emp table must be an id that already exists in the dep table

So we need to insert records into the dep table first.

Insert dep (name,task) values ('IT',' technology'), ('HR',' recruitment'), ('sale',' sales')

Employee table insert record

Insert emp (name,dep_id) values ('jack',1), (' tom',2), ('jams',1), (' rouse',3), ('curry',2); # (' go',4) reported an error and could not be found in the id field of the associated foreign key

Note: if the data inserted in the dep_id field of our emp table does not exist in the id field of the dep table, an error will be reported.

Query the effect after we create it.

In this way, we associate the two tables. At present, we do not understand the multi-table query, but we first understand the association between the tables.

Let's take a look at synchronous updates and deletions. Changes to foreign keys will be affected by the associated table.

Update dep set id=33333 where id=3

Let's experience synchronous deletion again.

Delete from dep where id = 33333

This is the effect that foreign keys bring to us, both advantages and disadvantages:

Advantages: strong relevance, can only set existing content, and synchronize updates and deletions

Disadvantages: when a record in the foreign key table is deleted, all records related to associativity in the associated table will be deleted

Many-to-many association

Multiple tables are related to each other

For example, an author can write multiple books, or a book can have multiple authors, two-way one-to-many, that is, many-to-many

There is a drawback when using foreign keys, that is, which table should be created first? They all correspond to each other, isn't it contradictory? Solution: the third table, the id of the associated book and the author's id

Book table

Create table book (id int primary key auto_increment, name varchar (30))

Author table

Create table author (id int primary key auto_increment, name varchar (30))

Intermediate table: responsible for associating two tables

Create table authorRbook (id int primary key auto_increment, author_id int, book_id int, foreign key (book_id) references book (id) on update cascade on delete cascade, foreign key (author_id) references author (id) on update cascade on delete cascade)

Multiple authors are associated with a book, or an author is associated with multiple books, and the book should also reflect who is associated with it.

Book insert data:

Insert book (name) values ('break the Sky'), ('Tuluo mainland'), ('martial arts move the universe')

Author insert data:

Insert author (name) values ('jack'), (' tom'), ('jams'), (' rouse'), ('curry'), (' john')

The associated table inserts data:

Insert authorRbook (author_id,book_id) values (1), (1), (1), (1), (2), (2), (3), (4), (5), (5), (6)

At present, the corresponding relationship is:

Jack: fight against the sky, fight against the mainland, move the universe

Tom: break the sky and move the universe

Jams: Durou mainland

Rouse: break the firmament

Curry: break the sky and move the universe

Jhon: Durou mainland

An author can be produced in the writing of multiple books, and at the same time, each book will indicate the author of the book.

One-to-one association

Passers-by may become students of a certain school, that is, an one-on-one relationship.

Before that, passers-by did not belong to the school.

The principle is that the school turns passers-by into students through advertisements or telephone invitations.

Passerby table

Create table passers_by (id int primary key auto_increment, name varchar (10), age int); insert passers_by (name,age) values ('jack',18), (' tom',19), ('jams',23)

School table

Create table school (id int primary key auto_increment, class varchar (10), student_id int unique key, foreign key (student_id) references passers_by (id) on update cascade on delete cascade); insert school (class,student_id) values ('Mysql getting started to giving up', 1), ('Python getting started to Operation and maintenance', 3), ('Java from getting started to Music', 2)

In the design of data storage, we need to design the association relationship of the table in advance, and after all the relationship is designed, all that is left is to store the data in it. Later, we will learn about the relevant contents of the join table query, and query it in the form of a virtual table, and the queried data may come from multiple tables.

Table association, it is recommended to use the following ways

Many to many > many to one > one to one

This is the end of this article on "examples of multi-table associations in MySQL databases". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.