1.3 MySQL multi table & foreign key & database design, multi table relationship, multi table query, internal and external connection query, sub query, three paradigms and anti three paradigms

-- establish emp Table primary key auto increment
CREATE TABLE emp(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT ,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);

-- Add data
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Zhang million', 20, 'research and development
 Department', 'Guangzhou');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Zhao Si', 21, 'research and development
 Department', 'Guangzhou');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Guangkun', 20, 'research and development
 Department', 'Guangzhou');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Xiao bin', 20, 'sale
 Department', 'Shenzhen');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Yanqiu', 22, 'sale
 Department', 'Shenzhen');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Da Lingzi', 18, 'sale
 Department', 'Shenzhen');

1.2.2 single table problems

Redundancy, a large number of duplicate data in the same field

1.3 solutions

1.3.1 two tables are designed

-- Create department table
-- a party,Main table
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- Create employee table
-- Many parties ,From table
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT
);

-- Add 2 departments
INSERT INTO department VALUES(NULL, 'R & D department','Guangzhou'),(NULL, 'Sales Department', 'Shenzhen');
SELECT * FROM department;

-- Add employee,dep_id Indicates the Department of the employee
INSERT INTO employee (ename, age, dept_id) VALUES ('Zhang million', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Zhao Si', 21, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Guangkun', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Xiao bin', 20, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Yanqiu', 22, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Da Lingzi', 18, 2);
SELECT * FROM employee;

Table 1.3.2 relationship analysis

Relationship between department table and employee table

1) There is a field dept in the employee table_ ID corresponds to the primary key in the Department table. This field in the employee table is called a foreign key

2) The employee table with foreign keys is called the slave table, and the table with the primary key corresponding to the foreign key is called the master table

1.3.3 problems in multi meter design

When we are in Dept of employee table_ If you enter a nonexistent Department id in the id, the data can still be added. Obviously, this is unreasonable

-- Insert a piece of data without Department
INSERT INTO employee (ename,age,dept_id) VALUES('unknown',35,3);

In fact, we should ensure that the dept added to the employee table_ ID, which must exist in the Department table
Solution: use foreign key constraint, constraint dept_id, which must be the id existing in the Department table

1.4 foreign key constraints

1.4.1 what is a foreign key

/*
    Foreign key constraint
        Function: foreign key constraint can generate a corresponding relationship between two tables, so as to ensure the integrity of the reference of master-slave tables
        
    Foreign key: refers to the field corresponding to the master table in the slave table
    
    Master and slave tables
        Primary table: the table in which the primary key id is located. One party of the primary key id
        Slave table: the table in which the foreign key field is located, and the more one
*/

1.4.2 creating foreign key constraints

/*
    Syntax format for adding foreign key constraints
        1, Add foreign keys when creating tables
        create table Table name(
            Fields
            [constraint] [Foreign key constraint name] foreign key references main table (primary key field)
        );

        2, Adding foreign keys to existing tables        
        ALTER TABLE From the table ADD [CONSTRAINT] [FOREIGN KEY constraint name] FOREIGN KEY (FOREIGN KEY field name) REFERENCES main table (primary key field name);

*/

-- Create employee table add foreign key
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARBINARY(20),
    age INT,
    dept_id INT,    -- Foreign key field, Points to the primary key of the main table
    -- Add foreign key constraint
    CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

-- Add data normally (The foreign key of the slave table corresponds to the primary table primary key)
INSERT INTO employee (ename, age, dept_id) VALUES ('Zhang million', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Zhao Si', 21, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Guangkun', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Xiao bin', 20, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Yanqiu', 22, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Da Lingzi', 18, 2);


-- After adding foreign key constraints, A mandatory foreign key constraint check will be generated to ensure the integrity and consistency of data
-- Insert a piece of problematic data (department id non-existent)
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (ename, age, dept_id) VALUES ('error', 18, 3);

1.4.3 delete foreign key constraint

/*
    Delete foreign key constraint
    Syntax format
        alter table drop foreign key the name of the foreign key constraint from the table
*/

-- delete employee Foreign keys in tables
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- After creating the table,Add foreign key
-- Syntax format: alter table Table name ADD [CONSTRAINT] [Foreign key constraint name] FOREIGN KEY (Foreign key field name) REFERENCES Main table(Primary key field name);
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REF ERENCEs department(id);

-- Abbreviation, Do not write foreign key constraint name, Automatic generation employee_ibfk_1 Foreign key constraint name
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department(id);

1.4.4 precautions for foreign key constraints

/*
    Considerations for foreign key constraints
        1, The foreign key type of the slave table must be consistent with the primary key type of the primary table, otherwise the creation fails
        
        2, When adding data, you should first add the data of the master table, and then add the data of the slave table
        
        3, When deleting data, delete the data from the table first
*/

-- Add a new Department
INSERT INTO department(dep_name,dep_location) VALUES('Marketing Department','Beijing');
-- Add an employee who belongs to the marketing department
INSERT INTO employee(ename,age,dept_id) VALUES('Lao Hu',24,3);


-- When deleting data, you should first delete the data from the table
-- report errors Cannot delete or update a parent row: a foreign key constraint fails
-- Reason for error reporting: this data in the main table cannot be deleted,Because there is a reference to this data in the slave table
DELETE FROM department WHERE id = 3;

-- Delete the associated data from the table first
DELETE FROM employee WHERE dept_id = 3;
-- Then delete the data of the main table
DELETE FROM department WHERE id = 3;

1.4.5 cascade deletion (understand)

If you want to delete the data of the primary table as well as the data of the secondary table, you can use cascade deletion

/*
    cascading deletion
        This means that while deleting the data in the master table, you can delete the data in the related slave table
    
    Implementation statement: on delete cascade
*/

-- Recreate add cascade operation
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
-- Add cascade delete
ON DELETE CASCADE
);


-- Add data
INSERT INTO employee (ename, age, dept_id) VALUES ('Zhang million', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Zhao Si', 21, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Guangkun', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('Xiao bin', 20, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Yanqiu', 22, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Da Lingzi', 18, 2);


-- Delete the record with department number 2
-- employee The data in will also be deleted synchronously
DELETE FROM department WHERE id = 2;

2. Multi table relation design

/*
    Three relationships between tables
        1, One to many relationships (1vN common): classes and students, departments and employees
        2, Many to many relationships (NvN common): students and course actors and roles
        3, One to one relationship (1v1 understanding): ID card and individual 
*/

One to many relationship (1.2)

2.2 many to many relationship (common)

2.3 one to one relationship (understanding)

One to one (1:1) is rarely used in practical development Because one-to-one can be created into a table.
The principle of one-to-one table creation is that the foreign key is UNIQUE. The primary key of the primary table and the foreign key (UNIQUE) of the slave table form the primary foreign key relationship, and the foreign key is UNIQUE

2.4 design Provincial & municipal table

-- Create provincial table master table, 1 Party of
USE db3;
CREATE TABLE province(
    id INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20),
    pdesc VARCHAR(20)
);

-- Create city table, Foreign key pointing id
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(20),
    cdesc VARCHAR(20),
    pid INT,
    
    -- Add foreign key constraint
    CONSTRAINT pro_city_fk FOREIGN KEY(pid) REFERENCES province(id) 
);

ALTER TABLE city CHANGE id cid INT;

SELECT * FROM city;

2.5 design actors and roles table

-- Many to many relationship, List of actors and roles

-- Create cast
CREATE TABLE actor(
    a_id INT PRIMARY KEY AUTO_INCREMENT,
    a_name VARCHAR(20)
);

-- Create role table
CREATE TABLE role(
    r_id INT PRIMARY KEY AUTO_INCREMENT,
    r_name VARCHAR(20)
);

-- Create intermediate table
CREATE TABLE media(
    -- Primary key of intermediate table
    m_id INT PRIMARY KEY AUTO_INCREMENT,

    -- point actor Primary key of
    aid INT,

    -- point role Primary key of
    rid INT,
    CONSTRAINT a_m_fk FOREIGN KEY(aid) REFERENCES actor(a_id),
    CONSTRAINT r_m_fk FOREIGN KEY(rid) REFERENCES role(r_id)
);

-- Rename intermediate table
RENAME TABLE media TO actor_role;


-- Delete intermediate table foreign key constraint
ALTER TABLE actor_role DROP FOREIGN KEY a_m_fk;
ALTER TABLE actor_role DROP FOREIGN KEY r_m_fk;

-- Add intermediate table foreign key constraint
ALTER TABLE actor_role ADD CONSTRAINT a_m_fk FOREIGN KEY(aid) REFERENCES actor(a_id);
ALTER TABLE actor_role ADD CONSTRAINT r_m_fk FOREIGN KEY(rid) REFERENCES role(r_id);

3. Multi table query

3.1 what is multi table query

/*
    Syntax of multi table query
        select Word list segment from table name list;
*/

-- establish db3_2 database,Specify encoding
CREATE DATABASE db3_2 CHARACTER SET utf8;

-- Create classification table and commodity table
-- Classification table (One party master table)
CREATE TABLE category (
    cid VARCHAR(32) PRIMARY KEY ,
    cname VARCHAR(50)
);
-- Commodity list (Multiparty slave table)
CREATE TABLE products(
    pid VARCHAR(32) PRIMARY KEY ,
    pname VARCHAR(50),
    price INT,
    flag VARCHAR(2), #Whether to put on the shelf is marked as: 1 means to put on the shelf and 0 means to get off the shelf
    category_id VARCHAR(32),
    -- Add foreign key constraint
    FOREIGN KEY (category_id) REFERENCES category (cid)
);


-- insert data
-- Classified data
INSERT INTO category(cid,cname) VALUES('c001','household electrical appliances');
INSERT INTO category(cid,cname) VALUES('c002','Shoes and clothes');
INSERT INTO category(cid,cname) VALUES('c003','Cosmetics');
INSERT INTO category(cid,cname) VALUES('c004','automobile');
-- Commodity data
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','Millet TV',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','Gree air conditioner',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','Midea refrigerator',4500,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','Basketball shoes',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','Sports pants',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T Shirt',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','pizex',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','GHb ',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','Dabao',200,'1','c003');


/*
    Cartesian product
*/

-- The query results of multi table query and cross join query will produce Cartesian product, It cannot be used normally
SELECT * FROM products, category;

3.2 Cartesian product

/*
    Cartesian product
*/

-- The query results of multi table query and cross join query will produce Cartesian product, It cannot be used normally
SELECT * FROM products, category;

3.4 classification of multi table query

3.4.1 inner join query (implicit / explicit)

/*
    Internal connection query
        Features: match the contents of the two tables through the specified conditions, and those that do not match will not be displayed
        
        Implicit inner connection
            Syntax format: select field name from left table, right table where connection conditions
        
        Explicit inner join
            Syntax format: select field name from left table [inner] join right table on join condition
*/

-- Implicit inner connection
-- 1,Query all commodity information and corresponding classification information
SELECT * FROM products, category WHERE category_id = cid;

-- 2,Query the commodity name and price in the commodity table, And commodity classification information
-- In multi table query,You can use aliasing tables to simplify queries
SELECT 
    p.`pname`,
    p.`price`,
    c.`cname`
FROM products p, category c WHERE p.`category_id` = c.`cid`;

-- Query the category of Gree air conditioner
SELECT
    c.`cname`,
    p.`pname`
FROM products p, category c WHERE p.`category_id` = c.`cid` AND p.`pname` = 'Gree air conditioner';


-- Explicit inner join
-- 1,Query all commodity information and corresponding classification information
SELECT * FROM products p INNER JOIN category c ON p.`category_id` = c.`cid`;

-- 2,Query footwear classification, Name and price of goods with price greater than 500
/*
    There are a few things to be sure before you inquire
        1. Determine how many tables products & category to query
        2. Join condition for table p.`category_id` = c.`cid`;  From the table Foreign key = main table Primary key
        3. The fields used for query are commodity name and commodity price
        4. Query condition classification = shoes and clothing, price > 500;
*/

SELECT
    p.`pname`,
    p.`price`
FROM products p 
INNER JOIN category c ON p.`category_id` = c.`cid`
WHERE p.`price` > 500 AND  c.`cname` = 'Shoes and clothes';

3.4.2 external connection query (left / right)

/*
    External connection query
        Left outer connection
            Syntax format: keyword left [outer] join
                select Field name from left table left join right table on join condition
            characteristic:
                Based on the left table, the data in the right table will be displayed if it can be matched
                If there is no match, the data in the left table will be displayed normally and the data in the right table will be null
                
        Right outer connection
            Syntax format: keyword right [outer] join
                select Field name from left table right join right table on condition
            characteristic:
                Based on the right table, match the data in the left table. If it can be matched, it will be displayed
                If no match is found, the data in the right table is displayed normally and the data in the left table is null
*/

-- Query of left outer connection
SELECT
    * 
FROM category c 
LEFT JOIN products p ON c.`cid` = p.`category_id`;



-- Query the number of goods under each category
/*
    1.Table to query
    2.Query condition grouping statistics
    3.Query the number of goods under the field classification
    4.Table connection conditions
*/
SELECT
    c.`cname`,
    COUNT(p.`pid`)
FROM category c 
-- Table connection
LEFT JOIN products p ON c.`cid` = p.`category_id`
-- grouping
GROUP BY c.`cname`;


-- Query of right outer connection
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

3.4.3 summary of various connection modes

Inner join: inner join, which only obtains the data of the intersection part of two tables
Left outer join: left join, which queries all the data in the left table and the parts that intersect with the right table based on the left table
Right outer join: right join, based on the right table, queries all the data in the right table and the parts that intersect with the left table

4. Subquery

4.1 what is a subquery

/*
    Subquery
        The result of a select statement as part of another select statement
        
        Feature: subqueries must be placed in parentheses
        Used as the condition of parent query (in most cases)
*/

-- Query the commodity information with the highest price
-- 1.Find the highest price
SELECT MAX(price) FROM products; -- 5000
-- 2.Find out the commodity information according to the highest price
SELECT * FROM products WHERE price = 5000; 

-- Use one SQL Complete sub query method
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products );

4.2 sub query results are used as query criteria

/*
    Classification of subqueries
        where Type sub query: use the results of the sub query as the comparison criteria of the parent query
        from Type sub query: use the results of the sub query as a table
        exists Type sub query: when the query result is single column and multiple rows, the result of the sub query can be used as the condition in the in function of the parent query
*/

-- Sub query as query criteria
-- 1.Query the commodity name and commodity price under cosmetics classification
    -- Query the classification of cosmetics id
    SELECT cid FROM category WHERE cname = 'Cosmetics';
    
    -- According to cosmetics id Query corresponding commodity information
    SELECT
        p.`pname`,
        p.`price`
    FROM products p
    WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = 'Cosmetics');
    
    
-- 2.Query commodity information that is less than the average price
    -- Find the average price
    SELECT AVG(price) FROM products;
    
    -- Get product information that is less than the average price
    SELECT 
    * 
    FROM products 
    WHERE price < (SELECT AVG(price) FROM products);

4.3 The results of sub query are used as a table

-- 3.Query in goods,Commodity information with price greater than 500,Including commodity name, commodity price and classification name of commodity
    -- Subquery method
    SELECT  * FROM category;
    
    SELECT 
        p.`pname`,
        p.`price`,
        c.cname
    FROM products p 
    -- be careful: When the result of subquery is used as a table,To give an alias,Otherwise, the fields in the table cannot be accessed
    INNER JOIN (SELECT  * FROM category) c ON p.`category_id` = c.cid 
    WHERE p.price > 500;

4.4 sub query results are single column and multiple rows

/*
    The result of the sub query is single column and multiple rows, which is used as the condition of the in function of the parent query
    Syntax format:
        select Field name from table name where field in (subquery);
*/

-- 4.Inquire about goods with a price less than 2000,From which categories(name)
    -- Query the classification of goods less than 2000 id   Single column and multiple rows
    SELECT DISTINCT category_id FROM products WHERE price < 2000;
    
    -- Classified by id Query classified information
    SELECT 
    * 
    FROM category 
    WHERE cid IN 
    (SELECT DISTINCT category_id FROM products WHERE price < 2000);
    
    
-- 5.Query all commodity information under household appliances and shoes and clothing
    -- First of all, we should obtain the classification of household appliances and shoes and clothing id
    SELECT cid FROM category WHERE  cname IN ('household electrical appliances','Shoes and clothes');
    
    -- According to classification id Find product information
    SELECT * FROM products WHERE category_id IN
    (SELECT cid FROM category WHERE  cname IN ('household electrical appliances','Shoes and clothes'));

4.5 sub query summary

-- Summary of subqueries
    -- 1. If a subquery is a field(Single column), So here it is where Make conditions later
    -- 2. If there are multiple fields(Multi column), Just use it as a table(To alias)

5. Database design

5.1 three paradigms of database (space saving)

/*
    Three paradigms of database
        Design rules of database
        
        Function: create a database with less redundancy and reasonable structure
        
        Paradigm is the requirement (specification) of designing database
        
        First paradigm (1NF): a paradigm that meets the minimum requirements
            
            The column is atomic, that is, it is required that the column cannot be split
        
        Second paradigm (2NF): further meet more specifications on the basis of meeting the first paradigm
        
            A table can only describe one thing
        
        The third paradigm (3NF): analogy above
        
            Eliminate the transmission dependency. If the information in the table can be deduced, do not design a separate field to record
            
        The three paradigms are the principle of space saving
*/

5.2 database anti three paradigms

/*
    Anti three paradigm
        Improve the read performance of the database by adding redundant or duplicate data
        Waste storage space and save query time (space for time)
        
        Redundant field
            A field belongs to one table but appears in multiple tables
*/

5.3 summary

/*
    summary
        1, Try to design the database according to the rules of three paradigms
        2, You can reasonably add redundant fields, reduce join operations, and make the database execute faster
*/

Tags: MySQL

Posted by roy on Tue, 10 May 2022 18:19:19 +0300

Algorithm Zone