1 multi meter
1.1 multi table description
In actual development, a project usually needs many tables to complete
For example, the database of a shopping mall project needs many tables: user table, classification table, commodity table, order table
1.2 disadvantages of single table
1.2.1 table building

CREATE DATABASE db3 CHARACTER SET utf8;

-- 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, 'R & D department', 'Guangzhou');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Zhao Si', 21, 'R & D department', 'Guangzhou');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Guangkun', 20, 'R & D department', 'Guangzhou');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Xiaobin', 20, 'Sales Department', 'Shenzhen');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Gorgeous autumn', 22, 'Sales Department', 'Shenzhen');
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('Dalingzi', 18, 'Sales Department', 'Shenzhen');

1.2.2 single table problems

Redundancy: large amount of duplicate data in the same field
1.3 solutions
1.3.1 design as two tables
1 multi table design method
Department department table: id, dep_name，dep_location
employee table: eid, ename, age, dep_id
2 table building

-- Create department table
-- Party, main table
CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);

-- Create employee table
-- Multiple, from table
CREATE TABLE employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	age INT,
	dept_id INT
)

-- Add department table data add two 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 ('Xiaobin', 20, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Gorgeous autumn', 22, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Dalingzi', 18, 2);

SELECT * FROM employee;

1.3.2 table relation analysis
Relationship between department table and employee table:

1 there is a field dept in the employee table_ The ID corresponds to the primary key in the Department table. This field in the employee table is called the foreign key
2. The employee table with the foreign key 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 table design
When we are in the dept_ It is obviously unreasonable that the Department id that does not exist can be entered in the id, and the data can still be added

-- 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, constrain dept_id must be an id that exists in the Department table
1.4 foreign key constraints
To create a foreign key constraint:

/*
     Foreign key constraint
	Function: foreign key constraint can create a corresponding relationship between two tables, thus ensuring the integrity of master-slave table reference
     Foreign key
	The foreign key refers to the field corresponding to the primary key of the primary table in the slave table
     Master and slave tables
	Primary table: the table where the primary key id is located, one party
	Slave table: the table where the foreign key field is located, one side more

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

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

-- Add data normally (the primary key corresponding to the foreign key of the slave table)
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 ('Xiaobin', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('Gorgeous autumn', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('Dalingzi', 18, 2);

-- Insert a bad piece of data
-- After adding a foreign key constraint, a mandatory foreign key constraint check will be generated to ensure the integrity and consistency of the data
INSERT INTO employee (ename,age,dept_id) VALUES ('error',18,3);

1.4.3 deleting foreign key constraints
Syntax format:

alter table drop foreign key foreign key constraint name from the table;

(1) Delete foreign key constraint

-- delete employee Foreign key constraint in table, foreign key constraint name emp_dept_fk
ALTER TABLE enployee DROP FOREIGN KEY emp_dept_fk;

(2) Add the foreign key back
Grammatical format

ALTER TABLE from ADD [CONSTRAINT] [FOREIGN KEY constraint name] FOREIGN KEY (FOREIGN KEY field name) REFERENCES main table (primary key field name);

You can omit the foreign key name, and the system will automatically generate a
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCE department(id);

1.4.4 precautions for foreign key constraints
(1) The foreign key type of the slave table must be the same as the primary key type of the master table, otherwise the creation fails

(2) When adding data, you should first add the data in the main table

-- 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('Laohu',24,3);

(3) When deleting data, you should first delete the data from the table

-- 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
-- The data in the main table cannot be deleted due to error reporting,Because there is a reference to this data in the slave table
DELETE FROM department WHERE id = 3;

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

1.4.5 cascade delete operation (understand)
If you want to delete the master table data and the slave table data at the same time, you can use the cascade delete operation

cascading deletion
ON DELETE CASCADE

Delete employee table, re create, add cascading delete

-- 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 ('Xiaobin', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('Gorgeous autumn', 22, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('Dalingzi', 18, 2);

-- Delete record with department number 2
DELETE FROM department WHERE id = 2;

The record with foreign key value 2 in employee table has also been deleted

2 multi table relation design
In the actual development, a project usually needs many tables to complete. For example, a shopping mall project needs multiple tables such as category, products, orders, and so on. There is a certain relationship between the data of these tables. Next, let's learn about the design of multi table relationship

2.1 one to many relationship (common)
One to many relationship (1: n)
=E.g. classes and students, departments and employees, customers and orders, classifications and commodities
One to many tabulation principle
=Create a field in the slave table (party) as a foreign key to the primary key of the master table (party)

2.2 many to many relationship (common)
Many to many (m: n)
=Examples: teachers and students, students and courses, users and roles
Many to many tabulation principle
=You need to create a third table with at least two fields in the middle table, which are used as foreign keys to the primary keys of each party

2.3 one to one relationship (understanding)
One to one (1:1)
=) is not widely used in actual development, because one-to-one can be created into a table
One to one table building principle
=The primary key of the UNIQUE primary table and the foreign key (UNIQUE) of the subordinate table form a primary foreign key relationship, and the foreign key is UNIQUE

2.4 list of design provinces and cities
(1) Analysis: the relationship between provinces and cities is one to many, and a province contains many cities

(2) SQL implementation

#Create a provincial table (primary table, note: be sure to add a primary key constraint)
CREATE TABLE province(
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(20),
     description VARCHAR(20)
);

#Create city table (slave table, note: the foreign key type must be consistent with the primary key of the primary table)
CREATE TABLE city(
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(20),
     description VARCHAR(20),
     pid INT,
     -- Add foreign key constraint
     CONSTRAINT province_city_fk FOREIGN KEY (pid) REFERENCES province (id)
);

(3) View table relationships

2.5 list of design actors and roles
(1) Analysis: actors and roles are many to many relationships. An actor can play multiple roles, and a role can also be played by different actors

(2) SQL implementation & add foreign key constraint

#Create cast
CREATE TABLE actor(
   id INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(20)
);

#Create role table
CREATE TABLE role(
   id INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(20)
);

#Create middle table
CREATE TABLE actor_role(
   -- Intermediate table's own primary key
   id INT PRIMARY KEY AUTO_INCREMENT,
   
   -- point actor Foreign key of table
   aid INT,
   -- point role Foreign key of table
   rid INT
);

-- For middle table aid Field, adding a foreign key constraint to the primary key of the actor table
ALTER TABLE actor_role ADD FOREIGN KEY (aid) REFERENCES actor (id);

-- For middle table rid Field, adding a foreign key constraint to the primary key of the role table
ALTER TABLE actor_role ADD FOREIGN KEY (aid) REFERENCES role (id);

(3) View table relationships

3 multi table query
3.1 what is multi table query
DQL: query multiple tables to get the required data
For example, if we want to query the goods under the household appliance classification, we need to query the two tables of classification and goods
3.2 data preparation

-- establish db3_2 Database specified encoding
CREATE DATABASE db3_2 CHARACTER SET utf8;

USE db3_2;

#Classification table (primary table of one party)
CREATE TABLE category(
	cid VARCHAR(32) PRIMARY KEY,
	cname VARCHAR(50)
);

#Commodity list
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 for putting on the shelf, 0 for putting off the shelf
	category_id VARCHAR(32),
	-- Add foreign key constraint
	FOREIGN KEY (category_id) REFERENCES category (cid)
);

#Classification data 
INSERT INTO category(cid,cname) VALUES('c001','household electrical appliances');
INSERT INTO category(cid,cname) VALUES('c002','Footwear');
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');

3.3 Cartesian product
Cross connect queries, because Cartesian product will be generated, so it will not be used
(1) Grammatical format

SELECT field name FROM table 1, table 2;

(2) Use cross connect to query commodity table and classification table

SELECT * FROM category,products;

(3) Observe the query result and generate Cartesian product (the result can't be used)

Cartesian product:
Suppose set A={a,b}, set B={0,1,2}, then the Cartesian product of two sets is {(a.0),(a,1),(a,2),(b,0),(b,1),(b,2).

3.4 classification of multi table query
3.4.1 internal connection query
Characteristics of inner join: match the data in two tables by specified conditions. If the data is matched, it will be displayed. If the data is not matched, it will not be displayed
For example, match by: foreign key of slave table = primary key of main table
3.4.1.1 implicit internal connection
Write multiple table names directly after the from clause. Use where to specify the join condition. This join method is implicit inner join
Use where condition to filter useless data
Syntax format:

SELECT field name FROM left table and right table WHERE connection conditions;

(1) Query all product information and corresponding classification information

#Implicit inner connection
SELECT * FROM products,category WHERE category_id = cid;

(2) Query the commodity name and price of the commodity table, as well as the classification information of the commodity
It is convenient for us to query (prompt) by aliasing tables

SELECT
	p.`pname`,
	p.`price`,
	c.`cname`
FROM products p,category c WHERE p.`category_id` = c.`cid`;
-- Quotation marks``It's automatic, don't worry

(3) Inquire what kind of products Gree air conditioner belongs to

#Find out what kind of goods Gree air conditioner belongs to
SELECT p.`pname`,c.`cname` FROM products p,category c WHERE P.`category_id`=c.`cid` AND p.`pid`=`p002`;

3.4.1.2 explicit internal connection
Use inner join on in this way, explicit inner connection
Grammatical format

SELECT field name FROM left table [INNER] JOIN right table ON condition;
--inner can be omitted

(1) Query all product information and corresponding classification information

# Explicit inner join query
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;

(2) Query the commodity name and price with price greater than 500 under footwear and clothing classification

# Query the commodity name and price with price greater than 500 under footwear and clothing classification
-- Some things we need to be sure of
-- 1.Query several tables products & category
-- 2.Table connection from table.Foreign key = Primary key of primary table
-- 3.Query criteria cname = 'Footwear' and price > 500
-- 4.Fields to query pname price
SELECT
	p.pname,
	p.price
FROM products p INNER JOIN category c ON p.category_id = c.cid
WHERE p.price > 500 AND cname = 'Footwear';

The output result is:

3.4.2 external connection query
3.4.2.1 left outer connection
Left outer join, which can be omitted
Features of left outer connection:
Based on the left table, match the data in the right table. If it matches the upper table, the matched data will be displayed;
If no match is found, the data in the left table will be displayed normally, and the display on the right will be null;
(1) Grammatical format

SELECT field name FROM left table LEFT [OUTER] JOIN right table ON condition;

-- Left outer connection query
SELECT * FROM category c LEFT JOIN products p ON c.`cid`=p.`category_id`;

(2) Left outer connection, query the number of goods under each category

# Query the number of goods under each category
/*
	1.Connection condition: primary table. Primary key = secondary table. Secondary key
	2.Query criteria: each classification needs to be grouped
	3.Fields to query: classification name, number of goods under classification
*/
SELECT 
	c.`cname` AS 'Classification name',
	COUNT(p.`pid`) AS 'Number of products'
FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`
GROUP BY c.`cname`;

3.4.2.2 right outer connection
RIGHT OUTER JOIN, which can be omitted
Features of right outer connection:
Based on the right table, match the data in the left table. If it can be matched, display the matched data
If there is no match, the data in the right table will be displayed normally, and the data in the left table will be displayed as null
(1) Grammatical format

SELECT field name FROM left table RIGHT [OUTER] JOIN right table ON condition;

SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

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

4 subquery
4.1 what is subquery
Sub query concept: the result of one select query statement as part of another
Features of subquery:
=Subqueries must be placed in parentheses
=Child query is generally used as query criteria of parent query
Common sub query categories
=where type sub query: use the result of the sub query as the comparison condition of the parent query
=from sub query: the results of the sub query are used as a table for the parent query
=exists type child query: the result of a child query is a single column and multiple rows, similar to an array. The parent query uses the IN function, including the result of the child query
4.2 sub query results as query criteria
Syntax format:

SELECT query field FROM table WHERE field = (subquery);

(1) Query the highest price commodity information by sub query

# Query the highest price commodity information by sub query
-- 1.Find out the highest price first
SELECT MAX(price) FROM products;

-- 2.Take the highest price as a condition to obtain commodity information
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

(2) Query the commodity name and price under the cosmetics classification

# Query the commodity name and price under the cosmetics category
-- Find out the classification of cosmetics first id
SELECT cid FROM category WHERE cname = 'Cosmetics';
-- According to classification id，To query the corresponding commodity information in the commodity table
SELECT 
	p.`pname`,
	p.`price` 
FROM products p
WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = 'Cosmetics');

3. Query the information of goods less than the average price

-- 1.Query average price
SELECT AVG(price) FROM products;

-- 2.Query goods with less than average price
SELECT * FROM products WHERE price < (SELECT AVG(price) FROM products);

4.3 the result of sub query is used as a table
Syntax format:

SELECT query field FROM table alias WHERE condition;

(1) Query the commodity information whose price is greater than 500, including the commodity name, commodity price, and the classification name of the commodity

-- 1.Query the data of classification table first
SELECT * FROM category;

-- 2.Use the above query statement as a table
SELECT 
	p.`pname`,
	p.`price`,
	c.cname
FROM products p 
-- When a subquery is used as a table, it needs an alias to access the fields in the table
INNER JOIN (SELECT * FROM category) c 
ON p.`category_id` = c.cid WHERE p.`price` > 500;

4.4 subquery results are single column and multiple rows
The result of the subquery is similar to an array. The parent query uses the IN function, which contains the result of the subquery
Grammatical format

SELECT query field FROM table WHERE field IN (sub query);

(1) Query the classification (name) of goods whose price is less than 2000

#Query the classification (name) of goods whose price is less than 2000

-- First, query the classification of goods whose price is less than 2000 ID
SELECT DISTINCT category_id FROM products WHERE price < 2000;

-- According to the classification id Information, query classification name
-- report errors: Subquery returns more than 1 row The result of the subquery is greater than one row
SELECT * FROM category 
WHERE cid = (SELECT DISTINCT category_id FROM products WHERE price < 2000);

Using the IN function, in(c002,c003)

-- Subquery obtains single column and multi row data
SELECT * FROM category
WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);

(2) Query all commodity information under household appliances and footwear

#Query all commodity information under household appliances and footwear
-- First find out the classification of home appliances and footwear ID
SELECT cid FROM category WHERE cname IN ('household electrical appliances','Footwear');

-- according to cid Query commodity information under classification
SELECT * FROM products
WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('household electrical appliances','Footwear'));

4.5 sub query summary
1. If the sub query finds a field (single column), it will be used as a condition after where
2. If multiple fields (multiple columns) are found in the sub query, it will be used as a table (alias is required)

5 database design
5.1 three paradigms of database (least space)
Concept: three paradigms are the principles of database design
=In order to build a database with small redundancy and reasonable structure, certain rules must be followed when designing the database. In a relational database, such rules are called paradigms. A paradigm is a summary of a design requirement. If we want to design a relational database with reasonable structure, we must meet certain paradigms;
=The paradigm that meets the minimum requirement is the first paradigm (1NF). On the basis of the first paradigm, the second paradigm (2NF) that further meets more specification requirements is called, and the rest paradigms are analogy. Generally speaking, the database only needs to meet the third paradigm (3NF);
5.1.1 first normal form 1NF
Concept:
=Atomicity, making columns inseparable
=The first paradigm is the most basic paradigm. The fields in the database table are all single attribute and cannot be subdivided. If each field in the data table is the smallest data unit that cannot be subdivided, the first paradigm is satisfied.
Example: in the address information table, the country column can be further split, which does not conform to the first normal form

5.1.2 second normal form 2NF
Concept:
=The goal is to ensure that each column in the table is related to the primary key
=A table can only describe one thing
example:
=There are actually two things described in the student information table, one is the student's information, the other is the course information
=If it is placed in a table, the data will be redundant. If the student information is deleted, the score information will also be deleted

5.1.3 third normal form 3NF
Concept:
=Eliminate transmission dependence
=Table information, if it can be derived, should not be stored in a single field
Example: the total amount can be calculated by the number and price fields. Do not record in the table again (space is the least)

5.2 database anti three paradigms
5.2.1 concept
Anti normalization refers to improving the read performance of a database by adding redundant or duplicate data
Waste storage space, save query time (space for time)
5.2.2 what are redundant fields?
In a design database, a field belongs to one table, but it appears in another or more tables at the same time, which is exactly equivalent to its meaning representation in the original table, so this field is a redundant field
5.2.3 examples of anti three paradigms
There are two tables, user table and order table. The user table has field name, and the order table also has field name

Usage scenario:
=When you need to query the "order table" and "all data" and only need the "user table" name field, there is no redundant field, you need to join to connect the user table. If the amount of data in the table is very large, then this connection query will consume the performance of the system very much;
=In this case, redundant fields can be used. If there are redundant fields, we need a table
5.2.4 summary
To create a relational database design, we have two options
1. Follow the rules of paradigm theory as much as possible, and have as few redundant fields as possible to make the database design look exquisite, elegant and fascinating
2. Reasonably add the lubricant of redundant field, reduce the join, and make the database perform better and faster