MySQL Study Notes 2 (Multi-Table operations) and mysql Study Notes

MySQL Study Notes 2 (Multi-Table operations) and mysql Study Notes

Foreign key: Associate two tables

Features:

1. The foreign key value from the table is a reference to the primary key of the master table.

2. The foreign key type of the slave table must be consistent with the primary key type of the master table.

 

Example:

Create two tables and prepare the data:

USE mybase; create table category (cid VARCHAR (32) primary key, cname VARCHAR (100); create table product (pid VARCHAR (32) primary key, pname VARCHAR (40 ), price DOUBLE, category_id VARCHAR (32); insert into category (cid, cname) VALUES ('c001', 'appliance '); insert into category (cid, cname) VALUES ('c002 ', 'apparel'); insert into category (cid, cname) VALUES ('c003 ', 'cosmetic'); insert into product (pid, pname, price, category_id) VALUES ('p001', 'lenovo ', '000000', 'c001'); insert into product (pid, pname, price, category_id) VALUES ('p002 ', 'haier ', '000000', 'c001'); insert into product (pid, pname, price, category_id) VALUES ('p003', 'raytheon ', '123 ', 'c001'); insert into product (pid, pname, price, category_id) VALUES ('p004 ', 'Jack JONES', '000000', 'c002 '); insert into product (pid, pname, price, category_id) VALUES ('p005 ', 'devs', '000000', 'c002'); insert into product (pid, pname, price, category_id) VALUES ('p006 ', 'player', '000000', 'c002'); insert into product (pid, pname, price, category_id) VALUES ('p007 ', 'jin Ba', '000000', 'c002'); insert into product (pid, pname, price, category_id) VALUES ('p008 ', 'channel', '000000', 'c003 '); insert into product (pid, pname, price, category_id) VALUES ('p009', 'applicable materia medica', '20140901 ', 'c003 ');

View Code

There is no problem in executing this Code:

DELETE FROM category WHERE cid='c003';

Add a foreign key relationship:

ALTER TABLE product ADD FOREIGN KEY(category_id) REFERENCES category(cid);

At this time, the data in the two tables has a foreign key relationship.

An error occurs when executing this Code:

DELETE FROM category WHERE cid='c003';

If you want to succeed, you must first Delete the two data in the product table related to it.

If you add data from a table, you cannot add records that do not exist in the master table. For example, you can add data whose category_id is c004.

 

 

Relationship between tables:

One to multiple:

Instances: customers and orders, categories and products

One category corresponds to multiple commodities, and one commodity can belong to only one category

The above example is a one-to-many case

 

Many to many:

Example: Student Course Selection

Select multiple courses for multiple students.

Pay attention to the many-to-many table creation methods:

Create a third table as an intermediate table with at least two fields

The two fields respectively serve as the foreign key pointing to the primary key of the respective Party:

Code:

Create table from TABLE (Field 1, Field 2 ,...) alter table add foreign key (Field 1) REFERENCES primary TABLE 1 (primary KEY); alter table add foreign key (Field 2) REFERENCES primary TABLE 2 (primary KEY );

 

One to one:

Instance: company address

One company corresponds to one address, one to one

It is not used in actual development. It is usually written in a table.

 

 

Multi-Table query:

Use the two tables and data created above:

SELECT * FROM category, product; SELECT * FROM category inner join product ON cid = category_id; SELECT * FROM category c, product p WHERE c. cid = p. category_id; SELECT * FROM category left join product ON cid = category_id; SELECT * FROM category right join product ON cid = category_id; subquery SELECT cid FROM category WHERE cname = 'cosmetic '; SELECT * FROM product WHERE category_id = (SELECT cid FROM category WHERE cname = 'cosmetic ');