MySQL's foreign key

Previous paragraph review

CREATE TABLE Table name (
Field name 1 type [(width) constraint],
Field Name 2 Type [(width) constraint],
Field Name 3 Type [(width) constraint]
);

#解释:
Type: What data type must be used to pass the value of the restriction field
Constraint: A constraint is an additional restriction that is added outside of a type

Note
1. Field names cannot be the same in the same table
2. Width and constraints are optional, field names and types are required
3. After the last field, do not add a comma

The disadvantage of storing all the data in a single table
1. The organizational structure of the table is not clear
2. Wasted Space
3. Very poor extensibility

Additional knowledge:
If you want to clear the table, use truncate TB1;
Function: Resets the entire table (including ID)

One, foreign key (key)

One of the many pairs of table relationships
1. Find a routine for the relationship between table and table

Example: EMP table DEP table
Step One:
Part1:
1, first stand on the left side of the EMP angle
2, to find the left-hand emp multiple records can correspond to a record of the right table dep
3. The meaning of translation 2:
Left table emp Multiple records = = Number of employees
Right table dep a record = = "a Department

Final translation results: Can multiple employees belong to one department?
If yes, a part2 process is required

Part2:
1, on the right side of the table dep angle
2, to find the right table dep multiple records can correspond to the left table EMP a record
3. The meaning of translation 2:
Right table dep multiple records = = Number of departments
Left table emp a record = = "an employee

Final translation results: Whether multiple departments can contain the same employee

If not, you can determine that the relationship between EMP and DEP is a one-way, many-to-one
How is it implemented?
A new dep_id field in the EMP table that points to the ID field of the DEP table


# What effect does foreign key bring?
#1, constraint 1: When a table is created, the associated table is DEP first to build the associated table emp

CREATE TABLE DEP (
ID int primary KEY auto_increment,
Dep_name Char (10),
Dep_comment Char (60)
);

CREATE TABLE EMP (
ID int primary KEY auto_increment,
Name Char (16),
Gender enum (' Male ', ' female ') not null default ' male ',
dep_id int,
Foreign KEY (dep_id) references dep (ID)
);

#2, constraint 2: When inserting records, you must first insert the associated table DEP to insert the associated table EMP
Insert into DEP (dep_name,dep_comment) values
(' SB-teaching ', ' SB-tutor ' study, teach Python course '),
(' Foreign Ministry ', ' old boy ' Shanghai campus Ambassador to Zhangjiang '),
(' NB Technology department ', ' NB Technical capacity Limited ');


INSERT into EMP (NAME,GENDER,DEP_ID) values
(' Alex ', ' Male ', 1),
(' Egon ', ' Male ', 2),
(' LXX ', ' Male ', 1),
(' wxx ', ' Male ', 1),
(' Wenzhou ', ' female ', 3);


#3, constraint 3: Updates and deletions need to take into account the relationship associated with the associated
Solution:
1, first delete the associated table emp, and then delete the associated table DEP, ready to rebuild
mysql> drop table emp;
Query OK, 0 rows affected (0.11 sec)

mysql> drop table dep;
Query OK, 0 rows affected (0.04 sec)


2, Reconstruction: New features, sync updates, delete synchronously
CREATE TABLE DEP (
ID int primary KEY auto_increment,
Dep_name Char (10),
Dep_comment Char (60)
);

CREATE TABLE EMP (
ID int primary KEY auto_increment,
Name Char (16),
Gender enum (' Male ', ' female ') not null default ' male ',
dep_id int,
Foreign KEY (dep_id) references dep (ID)
ON UPDATE cascade
ON DELETE Cascade
);
Insert into DEP (dep_name,dep_comment) values
(' SB-teaching ', ' SB-tutor ' study, teach Python course '),
(' Foreign Ministry ', ' old boy ' Shanghai campus Ambassador to Zhangjiang '),
(' NB Technology department ', ' NB Technical capacity Limited ');


INSERT into EMP (NAME,GENDER,DEP_ID) values
(' Alex ', ' Male ', 1),
(' Egon ', ' Male ', 2),
(' LXX ', ' Male ', 1),
(' wxx ', ' Male ', 1),
(' Wenzhou ', ' female ', 3);


# Sync Delete
Mysql> select * from DEP;
+----+------------------+-------------------------------------------------------------------------------------- ----+
| ID | Dep_name | dep_comment |
+----+------------------+-------------------------------------------------------------------------------------- ----+
| 1 | SB Teaching Department | SB Tutoring students to learn and teach Python courses |
| 2 | Ministry of Foreign Affairs | The old boy Shanghai campus Ambassador Zhangjiang Image |
| 3 | NB Technology Dept. | NB Technical Capacity Limited Department |
+----+------------------+-------------------------------------------------------------------------------------- ----+
3 Rows in Set (0.00 sec)

Mysql> select * from EMP;
+----+------------------+--------+--------+
| ID | name | Gender | dep_id |
+----+------------------+--------+--------+
| 1 | Alex |      Male | 1 |
| 2 | Egon |      Male | 2 |
| 3 | LXX |      Male | 1 |
| 4 | WXX |      Male | 1 |
| 5 | Wenzhou |      Female | 3 |
+----+------------------+--------+--------+
5 rows in Set (0.00 sec)

Mysql> Delete from Dep where id=1;
Query OK, 1 row affected (0.02 sec)

Mysql> select * from DEP;
+----+------------------+-------------------------------------------------------------------------------------- ----+
| ID | Dep_name | dep_comment |
+----+------------------+-------------------------------------------------------------------------------------- ----+
| 2 | Ministry of Foreign Affairs | The old boy Shanghai campus Ambassador Zhangjiang Image |
| 3 | NB Technology Dept. | NB Technical Capacity Limited Department |
+----+------------------+-------------------------------------------------------------------------------------- ----+
2 rows in Set (0.00 sec)

Mysql> select * from EMP;
+----+------------------+--------+--------+
| ID | name | Gender | dep_id |
+----+------------------+--------+--------+
| 2 | Egon |      Male | 2 |
| 5 | Wenzhou |      Female | 3 |
+----+------------------+--------+--------+
2 rows in Set (0.00 sec)

#同步更新
Mysql> select * from EMP;
+----+------------------+--------+--------+
| ID | name | Gender | dep_id |
+----+------------------+--------+--------+
| 2 | Egon |      Male | 2 |
| 5 | Wenzhou |      Female | 3 |
+----+------------------+--------+--------+
2 rows in Set (0.00 sec)

mysql> update DEP set id=200 where id = 2;
Query OK, 1 row affected (0.04 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from DEP;
+-----+------------------+------------------------------------------------------------------------------------- -----+
| ID | Dep_name | dep_comment |
+-----+------------------+------------------------------------------------------------------------------------- -----+
| 3 | NB Technology Dept. | NB Technical Capacity Limited Department |
| 200 | Ministry of Foreign Affairs | The old boy Shanghai campus Ambassador Zhangjiang Image |
+-----+------------------+------------------------------------------------------------------------------------- -----+
2 rows in Set (0.00 sec)

Mysql> select * from EMP;
+----+------------------+--------+--------+
| ID | name | Gender | dep_id |
+----+------------------+--------+--------+
| 2 | Egon |    Male | 200 |
| 5 | Wenzhou |      Female | 3 |
+----+------------------+--------+--------+
2 rows in Set (0.00 sec)


One of the table relationships to multiple


1, what is many-to-many
Between the two tables is a two-way, many-to-one relationship, called many-to-many
How is it implemented?
Create the third table, which has a field in the FK ID of the left table, and a field that is the ID of the FK right table


Create TABLE author (
ID int primary KEY auto_increment,
Name Char (16)
);

CREATE TABLE book (
ID int primary KEY auto_increment,
Bname Char (16),
Price int
);

Insert into author (name) values
(' Egon '),
(' Alex '),
(' Wxx ')
;
Insert into book (Bname,price) values
(' Python from entry to the ground ', 200),
(' Sunflower Sutra cutting to Mastery ', 800),
(' Nine Yin Canon ', 500),
(' Nine Yang Martial, 100 ')
;


CREATE TABLE Author2book (
ID int primary KEY auto_increment,
author_id int,
book_id int,
Foreign KEY (author_id) references author (id)
ON UPDATE cascade
ON DELETE CASCADE,
Foreign KEY (book_id) references book (ID)
ON UPDATE cascade
ON DELETE Cascade
);

Insert into Author2book (author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,1),
(3,2),
(3,3),
(3,4);
One to one of table relationships

A record of left table only corresponds to a record in the right table, and vice versa.
The essence is that the field with the corresponding storage ID is unique to guarantee the existence
CREATE TABLE Customer (
ID int primary KEY auto_increment,
Name Char (a) is not NULL,
QQ Char (TEN) is not NULL,
Phone char (+) NOT NULL
);

CREATE TABLE Student (
ID int primary KEY auto_increment,
Class_name char () NOT NULL,
customer_id int Unique, #该字段一定要是唯一的
Foreign KEY (customer_id) references customer (ID) #外键的字段一定要保证unique
ON DELETE Cascade
ON UPDATE cascade
);

Insert into customer (Name,qq,phone) values
(' Lee Jet ', ' 31811231 ', 13811341220),
(' King Cannon ', ' 123123123 ', 15213146809),
(' Guard grenade ', ' 283818181 ', 1867141331),
(' Wu Tank ', ' 283818181 ', 1851143312),
(' Win rockets ', ' 888818181 ', 1861243314),
(' War mines ', ' 112312312 ', 18811431230)
;


#增加学生
INSERT into student (class_name,customer_id) values
(' 3 classes ', 3),
(' Weekend 19 ', 4),
(' Weekend 19 ', 5)
;


Two Insert data inserts


1. Insert full data (sequential insertion)
Syntax One:
INSERT into table name (Field 1, Field 2, Field 3 ...) field N) VALUES (value 1, value 2, value 3 ...) Value n);

Syntax Two:
INSERT into table name values (value 1, value 2, value 3 ...) Value n);

2. Specify fields to insert data
Grammar:
INSERT into table name (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...);

3. Inserting more than one record
Grammar:
INSERT into table name VALUES
(value 1, value 2, value 3 ...) Value N),
(value 1, value 2, value 3 ...) Value N),
(value 1, value 2, value 3 ...) Value n);

4. Insert Query Results
Grammar:
INSERT into table name (Field 1, Field 2, Field 3 ...) Field N)
SELECT (field 1, Field 2, Field 3 ...) field N) from table 2
WHERE ...;

Three Update data updates


Grammar:
UPDATE Table name SET
field 1= value 1,
field 2= value 2,
WHERE CONDITION;

Example:
UPDATE mysql.user SET Password=password (' 123 ')
where user= ' root ' and host= ' localhost ';

Four delete data deletes


Grammar:
DELETE from table name
WHERE conition;

Example:
DELETE from Mysql.user
WHERE password= ';

Practice:
Update MySQL root user password to mysql123
Remove all users except the root user logged on locally

MySQL's foreign key