Mysql database (2)

1 Simple constraints

constraint ： Limit the data in the table , Make sure the data is correct , effectiveness , integrity .

(1) Primary key constraint

characteristic ： The primary key constraint contains ： Non empty and the only two functions . A table can only have one column as the primary key , Primary keys are generally used to uniquely identify data in a table .

 （2） Unique constraint

 (3) Non empty constraint

 

2 Complex constraints

（1 ） Foreign key constraints

Make the relationship between tables , So as to ensure the accuracy of the data .

 

（2 ） Cascade update and cascade delete of foreign key

• What are cascading updates and cascading deletions
  • When I want to user Delete a user in the user table , I hope all orders of this user will be deleted as well

• When I want to user A user in the user table id modify , I hope that the order user number of the user in the order form will be changed as well

• Add cascading updates and cascading deletions

 

 

3 Multi table design

（1） one-on-one

• analysis
  • People and ID cards . A person has only one ID card , An ID card can only correspond to one person ！

• Principle of realization
  • Create a foreign key in any table , To associate the primary key of another table

 

 

 

（2） One to many

• analysis
  • Users and orders . A user can have multiple orders ！

• Commodity classification and commodity . There can be multiple products under one category ！

• Principle of realization
  • On the more side , Create foreign key constraints , To associate a party's primary key

 

 

 

（3） Many to many

• analysis
  • Students and courses . A student can choose multiple courses , A course can also be chosen by multiple students ！

• Principle of realization
  • You need to use the third table, the middle table , The middle table contains at least two columns , These two columns act as foreign keys in the middle table , Associate the primary keys of two tables respectively

 

4 Multi-table query  

 

--  establish db6 database 
CREATE DATABASE db6;
--  Use db6 database 
USE db6;

--  establish user surface 
CREATE TABLE USER(
        id INT PRIMARY KEY AUTO_INCREMENT,        --  user id
        NAME VARCHAR(20),                                --  User name 
        age INT                             --  User age 
);
--  Add data 
INSERT INTO USER VALUES (1,' Zhang San ',23);
INSERT INTO USER VALUES (2,' Li Si ',24);
INSERT INTO USER VALUES (3,' Wang Wu ',25);
INSERT INTO USER VALUES (4,' Zhao Liu ',26);


--  The order sheet 
CREATE TABLE orderlist(
        id INT PRIMARY KEY AUTO_INCREMENT,        --  Order id
        number VARCHAR(30),                                        --  The order no. 
        uid INT,    --  Foreign key field 
        CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
--  Add data 
INSERT INTO orderlist VALUES (1,'bk001',1);
INSERT INTO orderlist VALUES (2,'bk002',1);
INSERT INTO orderlist VALUES (3,'bk003',2);
INSERT INTO orderlist VALUES (4,'bk004',2);
INSERT INTO orderlist VALUES (5,'bk005',3);
INSERT INTO orderlist VALUES (6,'bk006',3);
INSERT INTO orderlist VALUES (7,'bk007',NULL);


--  Classification list 
CREATE TABLE category(
        id INT PRIMARY KEY AUTO_INCREMENT,  --  Classification of goods id
        NAME VARCHAR(10)                    --  Category name 
);
--  Add data 
INSERT INTO category VALUES (1,' Mobile phone digital ');
INSERT INTO category VALUES (2,' Computer office ');
INSERT INTO category VALUES (3,' Tobacco, wine, tea and sugar ');
INSERT INTO category VALUES (4,' Shoes and bags ');


--  Commodity list 
CREATE TABLE product(
        id INT PRIMARY KEY AUTO_INCREMENT,   --  goods id
        NAME VARCHAR(30),                    --  Name of commodity 
        cid INT, --  Foreign key field 
        CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
--  Add data 
INSERT INTO product VALUES (1,' Huawei mobile phones ',1);
INSERT INTO product VALUES (2,' Mi phones ',1);
INSERT INTO product VALUES (3,' Lenovo computer ',2);
INSERT INTO product VALUES (4,' Apple computer ',2);
INSERT INTO product VALUES (5,' Chinese cigarettes ',3);
INSERT INTO product VALUES (6,' Yuxi cigarette ',3);
INSERT INTO product VALUES (7,' Family planning supplies ',NULL);


--  In the middle of table 
CREATE TABLE us_pro(
        upid INT PRIMARY KEY AUTO_INCREMENT,  --  In the middle of table id
        uid INT, --  Foreign key field . It needs to be associated with the primary key of the user table 
        pid INT, --  Foreign key field . It needs to be associated with the primary key of the commodity table 
        CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
        CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
--  Add data 
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);

 

（2） Multi-table query - Cartesian product query ( understand )

• There are two tables , Get all the combinations of the two tables

• To complete a multi table query , We need to eliminate this useless data

• Multi table query format
• 

 

(3) Multi-table query - Internal connection query

Query principle

Inner join queries are part of the data where two tables intersect ( Data associated with primary and foreign keys )

• display type Inside even Pick up

•   implicit type Inside even Pick up

• 

  （4） Multi-table query - External connection query

• Left connection query ： Query all the data in the left table , And the left and right tables have the intersection part of the data

 

• Right connection ： Query all the data in the right table , And the left and right tables have the intersection part of the data

 

（5） Multi-table query - Subquery

Subquery ： The query statement is nested in the query statement . We call nested queries subqueries ！

 

--  Query the name of the oldest user 
SELECT MAX(age) FROM USER;              
--  Find out the highest age 
SELECT NAME,age FROM USER WHERE age=26; 
--  According to the highest age found , Search for name and age 
SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);
--  Check the order information of Zhang San and Li Si 
SELECT id FROM USER WHERE NAME=' Zhang San ' OR NAME=' Li Si ';   
--  Query Zhang San and Li Si's id
SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2; 
--  according to id Query order 
SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME=' Zhang San ' OR NAME=' Li Si ');
--  Query the order table id Greater than 4 Order information and user information of 
SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;

（6） Multi table query exercise

 

 

（7） Multi-table query - Self association query

Content ： There are data associations in the same table . You can query the same table multiple times ！ There are data associations in the same table . You can query the same table multiple times ！