The way of programming: the relationship between MySQL series tables

FOREIGN key

Quick Understanding FOREIGN Key

The Employee Information table has three fields: work number name Department

The company has 3 departments, but there are 100 million of employees, that means the department this field needs to be repeated storage, the longer the department name, the more wasted

The drawbacks of putting data on a table

1. The organizational structure of the table is not clear

2. Wasted Space

3. Poor extensibility

Workaround:

We can definitely define a departmental table.

The Employee Information table is then associated with the table, and how it is associated, that is, foreign key

#The table type must be a InnoDB storage engine, and the associated field, that is, the field of another table specified by references, must be guaranteed to be uniqueCREATE TABLE Department (ID int primary key,name varchar (20) notnull) engine=InnoDB;#dpt_id foreign key, associating parent table (Department primary key ID), synchronizing updates, deleting synchronouslyCREATE TABLE employee (ID int primary key,name varchar (20) notnull,dpt_id int,constraint fk_name foreign Key (dpt_id) references Department (ID) on delete cascadeon update cascade) E Ngine=InnoDB;#insert a record into the parent table department firstINSERT INTO department values (1,'The love of the same Technology Limited Division'),(2,'Alex Human Resources department'),(3,'Sales Department');#insert a record into the child table employeeINSERT into employee values (1,'Egon', 1),(2,'alex1', 2),(3,'Alex2', 2),(4,'alex3', 2),(5,'Lee Tank', 3),(6,'Liu Plane', 3),(7,'Zhang Rocket', 3),(8,'Jungle Bomb', 3),(9,'Gatling', 3);#Delete the parent table department, and the corresponding record in the child's employee is then deleted .mysql> Delete fromdepartment where id=3; MySQL> select * fromemployee;+----+-------+--------+| ID | name |  dpt_id |+----+-------+--------+| 1 |      Egon |  1 | | 2 |      Alex1 |  2 | | 3 |      Alex2 |  2 | | 4 |      Alex3 | 2 |+----+-------+--------+#Update Parent Table department, the corresponding record in child employee is changedMysql> Update department set id=22222 where id=2; MySQL> select * fromemployee;+----+-------+--------+| ID | name |  dpt_id |+----+-------+--------+| 1 |      Egon |  1 | | 3 |  Alex2 |  22222 | | 4 |  Alex3 |  22222 | | 5 |  Alex1 | 22222 |+----+-------+--------+Demonstration

Demonstration

How to find the relationship between two tables

  analysis step:  #   1, first stand in the angle of the left table to find   Whether the left table of multiple records can correspond to a record of the right table, if so, prove a field of the left table foreign key right Table a field (usually ID)  #   2, then stand on the right side of the table to find the   #   3, summary:  #   Many-to-one:   Span style= "COLOR: #008000" >#   Many-to-many   If steps 1 and 2 are simultaneously established, then the two tables are proved to be one-way multi-pair, that is, many-to-many, you need to define a relational table of the two tables to specifically store their relationship  #   one-to-one:  if both 1 and 2 are not true, a record of the left table is the only record that corresponds to the right table, and vice versa. This is very simple, that is, on the left table foreign key to the right table based on the left table's foreign key field is set to unique 

Establish a relationship between tables

# one-to-many or multi-pair Three sheets: publishing house, author information, book one-to-many (or many to one): A publishing house can publish multiple books related ways: foreign key

===================== many to one =====================CREATE TABLE press (ID int primary KEY auto_increment,name varchar (20) ; CREATE table book (ID int primary KEY auto_increment,name varchar (20), press_id int notnull,foreign Key (press_id) references press (ID) on delete cascadeon update cascade), insert into press (name) VALUES ( c8>'Beijing industrial Mines Publishing house'),('People's music is not good to listen to publishers'),('intellectual property is not used by publishers') insert into book (name,press_id) VALUES ('Nine Yang Martial', 1),('Nine Yin Canon', 2),('Nine yin Bones claw', 2),('The lone Nine swords', 3),('Dragon 10 Spank', 2),('Sunflower Treasure', 3);

View Code

# Many-to-many Three sheets: publishing house, author information, book many-to-many: an author can write more than one book, a book can also have multiple authors, two-way to many, that is, many-to-many associations: foreign key+ a new table

===================== Many-to-many =====================CREATE table author (id int primary KEY auto_increment,name varchar (20));#This table holds the relationship between the author's table and the Book table, that is, to check the relationship between the two.CREATE TABLE Author2book (ID int notnull unique auto_increment,author_id int notnull,book_id int notnull,constraint fk_author foreign Key (author_id) references author (id) on delete cascadeon update cascade,constraint Fk_book foreign KEY (book_id) references book (id) on delete cascadeon update cascade,primary key (author_id,book_id));#Insert four authors, id rowInsert into author (name) VALUES ('Egon'),('Alex'),('Yuanhao'),('WPQ');#each author and his own masterpiece are as followsEgon: Nine Yang Martial magic nine yin scriptures nine yin Bones claw lone nine sword Dragon 10 slap Sunflower Treasure book Alex: nine Yang Magic Sunflower Treasure Book Yuanhao: Lone solitary nine sword Dragon 10 Slap Sunflower Treasure WPQ: Nine Yang Martial weapon insert into Author2book (author_id, BOOK_ID) VALUES (The),(The),(1,3),(1,4),(1,5),(1,6),(2,1),(2,6),(3,4),(3,5),(3,6),(4,1);

View Code

#  one to one Two tables: Student table and Customer table one-to: A student is a client, a client may become a school, i.e. one-to-one relationship association: foreign Key+unique

#must be student to foreign key table customer, so as to ensure that:#1 students must be a customer,#2 The customer is not necessarily a student, but it is possible to become a studentCREATE TABLE customer (ID int primary KEY auto_increment,name varchar (20) notnull,qq varchar (10) notNull,phone Char (16) notnull); CREATE TABLE student (ID int PRIMARY KEY auto_increment,class_name varchar (20) notnull,customer_id int Unique,#This field must be unique.Foreign KEY (customer_id) references customer (ID)#The field of the foreign key must be guaranteed uniqueOn Delete cascadeon update cascade);#Increase CustomerINSERT into customer (Name,qq,phone) VALUES ('Lee Plane','31811231', 13811341220),('King Cannon','123123123', 15213146809),('Guard Grenade','283818181', 1867141331),('Wutank','283818181', 1851143312),('win Rockets','888818181', 1861243314),('War Mines','112312312', 18811431230);#Increase studentINSERT into student (class_name,customer_id) VALUES ('Class 3', 3),('Weekend Issue 19', 4),('Weekend Issue 19', 5);

View Code

Modify the table ALTER TABLE

syntax:1. Modify the table name alter the table name RENAME a                           new table name; 2. Add field      ALTER table name                          add field name  data type [integrity constraint ...],                          add field name  data type [integrity constraint ...];      ALTER Table name                          ADD field name  data type [integrity constraint ...]  First;      ALTER Table name                          ADD field name  data type [integrity constraint ...]  After field name;                             3. Delete field      ALTER table name                           drop field name; 4. Modify field      ALTER TABLE name                           MODIFY  field name data type [integrity constraint ...];      ALTER Table table name change                           old field name new field name old data type [integrity constraint condition ...];      ALTER table name change                           old field name new data type [integrity constraint ...];

Copying tables

Copy table structure + record (key does not replicate: primary key, foreign key, and index) MySQL  from Service; Copy table structure only MySQL  from service where 1=2;        condition is false, no record is found for empty set (0.00 sec) MySQL from service where 1=2;  Query OK, 0 rows affected (0.00 sec) records:0  duplicates:0  warnings:0mysql> CREATE TABLE t 4 like employees;

Delete a table

DROP table name;

The way of programming: the relationship between MySQL series tables