MySQL learning notes (Advanced)

Posted by sniperscope on Mon, 14 Feb 2022 09:55:10 +0100

1, Restraint

1. Foreign key constraints

Foreign key constraint concept
- Make the relationship between tables, so as to ensure the accuracy of data!
Add foreign key constraints when creating tables
- Why foreign key constraints

 -- establish db2 database
  CREATE DATABASE db2;
  -- use db2 database
  USE db2;
  
  -- establish user User table
  CREATE TABLE USER(
  	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  	NAME VARCHAR(20) NOT NULL             -- full name
  );

      -- Add user data
      INSERT INTO USER VALUES (NULL,'Zhang San'),(NULL,'Li Si'),(NULL,'Wang Wu');
      
      -- establish orderlist Order form
      CREATE TABLE orderlist(
      	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
      	number VARCHAR(20) NOT NULL,          -- Order number
      	uid INT                               -- Order user
      );
      -- Add order data
      INSERT INTO orderlist VALUES (NULL,'h001',1),(NULL,'h002',1),
      (NULL,'h003',2),(NULL,'h004',2),
      (NULL,'h005',3),(NULL,'h006',3);
      
      -- Add an order, but there is no user. Is that reasonable?
      INSERT INTO orderlist VALUES (NULL,'h007',8);
      -- Delete the user Wang Wu, but Wang Wu still has many orders in the order table. Is that reasonable?
      DELETE FROM USER WHERE NAME='Wang Wu';
      
      -- Therefore, we need to add foreign key constraints to make the two tables have a relationship

Foreign key constraint format

   CONSTRAINT Foreign key name FOREIGN KEY (Foreign key column name of this table) REFERENCES Main table name(Primary key column name of main table)

Create tables and add foreign key constraints

  -- establish user User table
  CREATE TABLE USER(
  	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  	NAME VARCHAR(20) NOT NULL             -- full name
  );
  -- Add user data
  INSERT INTO USER VALUES (NULL,'Zhang San'),(NULL,'Li Si'),(NULL,'Wang Wu');
  
  -- establish orderlist Order form
  CREATE TABLE orderlist(
  	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  	number VARCHAR(20) NOT NULL,          -- Order number
  	uid INT,                              -- Order user
  	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)   -- Add foreign key constraint
  );
  -- Add order data
  INSERT INTO orderlist VALUES (NULL,'h001',1),(NULL,'h002',1),
  (NULL,'h003',2),(NULL,'h004',2),
  (NULL,'h005',3),(NULL,'h006',3);
  
  -- Add an order, but there is no user. Cannot add
  INSERT INTO orderlist VALUES (NULL,'h007',8);
  -- Delete the user Wang Wu, but Wang Wu still has many orders in the order table. Cannot delete
  DELETE FROM USER WHERE NAME='Wang Wu';

Delete foreign key constraint

-- Standard grammar
ALTER TABLE Table name DROP FOREIGN KEY Foreign key name;

-- Delete foreign key
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

Add foreign key constraint after creating table

   -- Standard grammar
    ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key column name of this table) REFERENCES Main table name(Primary key column name);
    
    -- Add foreign key constraint
    ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

2. Cascade update and cascade deletion of foreign keys (understand)

What are cascading updates and cascading deletions
- When I want to delete a user in the user table, I want all orders of that user to be deleted
- When I want to modify a user id in the user table, I want the order user number of the user in the order table to be modified accordingly
Add cascading updates and cascading deletions

-- Add foreign key constraints and cascade update standard syntax
ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key column name of this table) REFERENCES Main table name(Primary key column name) ON UPDATE CASCADE;

-- Add foreign key constraints and add cascading deletion standard syntax
ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key list name) REFERENCES Main table name(Primary key column name) ON DELETE CASCADE;

-- Add foreign key constraints, and add cascading update and cascading delete standard syntax at the same time
ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key column name of this table) REFERENCES Main table name(Primary key column name) ON UPDATE CASCADE ON DELETE CASCADE;


-- Delete foreign key constraint
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

-- Add foreign key constraints, and add cascading updates and cascading deletions at the same time
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Will Wang five users id Change to 5    In order table uid It was also modified
UPDATE USER SET id=5 WHERE id=3;

-- Delete Wang Wu user     All orders of the user in the order table will also be deleted
DELETE FROM USER WHERE id=5;

2, Multi table design

1. One to one

analysis
- Person and ID card. A person has only one ID card, and one ID card can only correspond to one person!
Realization principle
- Establish a foreign key in any table to associate with the primary key of another table
SQL demo

   -- establish db5 database
    CREATE DATABASE db5;
    -- use db5 database
    USE db5;
    
    -- establish person surface
    CREATE TABLE person(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(20)
    );
    -- Add data
    INSERT INTO person VALUES (NULL,'Zhang San'),(NULL,'Li Si');
    
    -- establish card surface
    CREATE TABLE card(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	number VARCHAR(50),
    	pid INT UNIQUE,
    	CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) -- Add foreign key
    );
    -- Add data
    INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

graphic

2. One to many

analysis
- Users and orders. One user can have multiple orders!
- Commodity classification and commodity. There can be multiple products under one category!
Realization principle
- A foreign key constraint is established on the multiple party to associate the primary key of one party
SQL demo

   /*
    	Users and orders
    */
    -- establish user surface
    CREATE TABLE USER(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(20)
    );
    -- Add data
    INSERT INTO USER VALUES (NULL,'Zhang San'),(NULL,'Li Si');
    
    -- establish orderlist surface
    CREATE TABLE orderlist(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	number VARCHAR(20),
    	uid INT,
    	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)  -- Add foreign key constraint
    );
    -- Add data
    INSERT INTO orderlist VALUES (NULL,'h001',1),(NULL,'h002',1),(NULL,'h003',2),(NULL,'h004',2);
    
    
    /*
    	Commodity classification and commodity
    */
    -- establish category surface
    CREATE TABLE category(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(10)
    );
    -- Add data
    INSERT INTO category VALUES (NULL,'Mobile digital'),(NULL,'Computer office');
    
    -- establish product surface
    CREATE TABLE product(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(30),
    	cid INT,
    	CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)  -- Add foreign key constraint
    );
    -- Add data
    INSERT INTO product VALUES (NULL,'Huawei P30',1),(NULL,'millet note3',1),
    (NULL,'Lenovo computer',2),(NULL,'Apple Computer',2);

graphic

3. Many to many

analysis
- Students and courses. A student can choose multiple courses, and a course can also be selected by multiple students!
Realization principle
- You need to use the middle table of the third table. The middle table contains at least two columns. These two columns are used as the foreign keys of the middle table to associate the primary keys of the two tables respectively
SQL demo

-- establish student surface
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- Add data
INSERT INTO student VALUES (NULL,'Zhang San'),(NULL,'Li Si');

-- establish course surface
CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- Add data
INSERT INTO course VALUES (NULL,'language'),(NULL,'mathematics');

-- Create intermediate table
CREATE TABLE stu_course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sid INT, -- For and student Tabular id Foreign key Association
	cid INT, -- For and course Tabular id Foreign key Association
	CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- Add foreign key constraint
	CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- Add foreign key constraint
);
-- Add data
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);

graphic

3, Multi table query

1. Multi table query - data preparation

SQL statement

 -- 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);
    
    
    -- Order form
    CREATE TABLE orderlist(
    	id INT PRIMARY KEY AUTO_INCREMENT,	-- order id
    	number VARCHAR(30),					-- Order number
    	uid INT,    -- Foreign key field
    	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
    );
    -- Add data
    INSERT INTO orderlist VALUES (1,'h001',1);
    INSERT INTO orderlist VALUES (2,'h002',1);
    INSERT INTO orderlist VALUES (3,'h003',2);
    INSERT INTO orderlist VALUES (4,'h004',2);
    INSERT INTO orderlist VALUES (5,'h005',3);
    INSERT INTO orderlist VALUES (6,'h006',3);
    INSERT INTO orderlist VALUES (7,'h007',NULL);
    
    
    -- Commodity classification table
    CREATE TABLE category(
    	id INT PRIMARY KEY AUTO_INCREMENT,  -- Commodity classification id
    	NAME VARCHAR(10)                    -- Commodity classification name
    );
    -- Add data
    INSERT INTO category VALUES (1,'Mobile digital');
    INSERT INTO category VALUES (2,'Computer office');
    INSERT INTO category VALUES (3,'Tobacco, wine, tea and sugar');
    INSERT INTO category VALUES (4,'Shoes, boots, luggage');
    
    
    -- Commodity list
    CREATE TABLE product(
    	id INT PRIMARY KEY AUTO_INCREMENT,   -- commodity id
    	NAME VARCHAR(30),                    -- Trade name
    	cid INT, -- Foreign key field
    	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
    );
    -- Add data
    INSERT INTO product VALUES (1,'Huawei Mobile',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 cigarette',3);
    INSERT INTO product VALUES (6,'Yuxi cigarette',3);
    INSERT INTO product VALUES (7,'contraceptives',NULL);
    
    
    -- Intermediate table
    CREATE TABLE us_pro(
    	upid INT PRIMARY KEY AUTO_INCREMENT,  -- Intermediate 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 combinations of these two tables
To complete multi table query, you need to eliminate these useless data
Multi table query format

    SELECT
    	Column name list
    FROM
    	Table name list
    WHERE
    	condition...

- Cartesian product query

    -- Standard grammar
    SELECT Listing FROM Table name 1,Table name 2,...;
    
    -- query user Table and orderlist surface
    SELECT * FROM USER,orderlist;

3. Multi table query - internal connection query

Query principle
- Internal connection query refers to the partial data with the intersection of two tables (the data associated with the main foreign key)
Explicit inner join

  -- Standard grammar
    SELECT Listing FROM Table name 1 [INNER] JOIN Table name 2 ON condition;
    
    -- Query user information and corresponding order information
    SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
    SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;
    
    -- Query the user information and the corresponding order information, and alias it
    SELECT * FROM USER u JOIN orderlist o ON u.id=o.uid;
    
    -- Query user name and age. And order number
    SELECT
    	u.`name`,	-- full name
    	u.`age`,	-- Age
    	o.`number`	-- Order number
    FROM
    	USER u          -- User table
    JOIN
    	orderlist o     -- Order form
    ON 
    	u.`id` = o.`uid`;

Implicit inner connection

 -- Standard grammar
    SELECT Listing FROM Table name 1,Table name 2 WHERE condition;
    
    -- Query user name and age. And order number
    SELECT
    	u.`name`,	-- full name
    	u.`age`,	-- Age
    	o.`number`	-- Order number
    FROM
    	USER u,		-- User table
    	orderlist o     -- Order form
    WHERE
    	u.`id`=o.`uid`;

4. Multi table query - external connection query

Left outer connection
- Query principle
  - Query all the data of the left table, and the data with the intersection of the left and right tables
- Basic demonstration

   -- Standard grammar
      SELECT Listing FROM Table name 1 LEFT [OUTER] JOIN Table name 2 ON condition;
      
      -- Query all user information and the corresponding order information of the user
      SELECT
      	u.`name`,	-- full name
      	u.`age`,	-- Age
      	o.`number`	-- Order number
      FROM
      	USER u          -- User table
      LEFT OUTER JOIN
      	orderlist o     -- Order form
      ON
      	u.`id`=o.`uid`;

Right outer connection
- Query principle
  - Query all the data in the right table and the data with intersection with the left and right tables
- Basic demonstration

-- Basic grammar
  SELECT Listing FROM Table name 1 RIGHT [OUTER] JOIN Table name 2 ON condition;
  
  -- Query all order information and the user information to which the order belongs
  SELECT
  	u.`name`,	-- full name
  	u.`age`,	-- Age
  	o.`number`	-- Order number
  FROM
  	USER u          -- User table
  RIGHT OUTER JOIN
  	orderlist o     -- Order form
  ON
  	u.`id`=o.`uid`;

5. Multi table query - sub query

Introduction to sub query
- The query statement is nested in the query statement. We will call nested queries sub queries!
Subquery - the result is single row and single column
- Can be used as a condition, use the operator to judge!
- Basic demonstration

-- Standard grammar
  SELECT Listing FROM Table name WHERE Listing=(SELECT Aggregate function(Listing) FROM Table name [WHERE condition]);
  
  -- Query the name of the oldest user
  SELECT MAX(age) FROM USER;              -- Find the maximum age
  SELECT NAME,age FROM USER WHERE age=26; -- Query the name and age according to the maximum age
  SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);

Subquery - the result is multi row and single column
- You can use the operator in or not in as a condition to judge!
- Basic demonstration

  -- Standard grammar
  SELECT Listing FROM Table name WHERE Listing [NOT] IN (SELECT Listing FROM Table name [WHERE condition]); 
  
  -- Query the order information of Zhang San and Li Si
  SELECT id FROM USER WHERE NAME='Zhang San' OR NAME='Li Si';   -- Query users of Zhang San and Li Si 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');

Subquery - the result is multi row and multi column
- You can participate in the query as a virtual table!
- Basic demonstration

  -- Standard grammar
  SELECT Listing FROM Table name [alias],(SELECT Listing FROM Table name [WHERE condition]) [alias] [WHERE condition];
  
  -- Query in order table id Order information and user information greater than 4
  SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;

6. Multi table query exercise

Query the user's number, name and age. Order number

   /*
    analysis:
    	User's number, name, age user table order number orderlist table
    	Condition: user id = orderlist. uid
    */
    SELECT
    	t1.`id`,	-- User number
    	t1.`name`,	-- User name
    	t1.`age`,	-- User age
    	t2.`number`	-- Order number
    FROM
    	USER t1,       -- User table
    	orderlist t2   -- Order form
    WHERE
    	t1.`id` = t2.`uid`;

Query all users. User's number, name and age. Order number

 /*
    analysis:
    	User's number, name, age user table order number orderlist table
    	Condition: user id = orderlist. uid
    	Query all users and use the left external connection
    */
    SELECT
    	t1.`id`,	-- User number
    	t1.`name`,	-- User name
    	t1.`age`,	-- User age
    	t2.`number`	-- Order number
    FROM
    	USER t1        -- User table
    LEFT OUTER JOIN
    	orderlist t2   -- Order form
    ON
    	t1.`id` = t2.`uid`;

Query all orders. User's number, name and age. Order number

 /*
    analysis:
    	User's number, name, age user table order number orderlist table
    	Condition: user id = orderlist. uid
    	Query all orders and use the right outer connection
    */
    SELECT
    	t1.`id`,	-- User number
    	t1.`name`,	-- User name
    	t1.`age`,	-- User age
    	t2.`number`	-- Order number
    FROM
    	USER t1         -- User table
    RIGHT OUTER JOIN
    	orderlist t2    -- Order form
    ON
    	t1.`id` = t2.`uid`;

Query the information of users older than 23 years old. Display the user's number, name and age. Order number

   /*
    analysis:
    	User's number, name, age user table order number orderlist table
    	Condition: user age > 23 AND user. id = orderlist. uid
    */
    /*
    select
    	t1.`id`,	-- User number
    	t1.`name`,	-- User name
    	t1.`age`,	-- User age
    	t2.`number`	-- Order number
    from
    	user t1,     -- User table
    	orderlist t2 -- Order form
    where
    	t1.`age` > 23
    	and
    	t1.`id` = t2.`uid`;
    */
    SELECT
    	t1.`id`,	-- User number
    	t1.`name`,	-- User name
    	t1.`age`,	-- User age
    	t2.`number`	-- Order number
    FROM
    	USER t1       -- User table
    LEFT OUTER JOIN
    	orderlist t2  -- Order form
    ON
    	t1.`id` = t2.`uid`
    WHERE
    	t1.`age` > 23;

Query the information of Zhang San and Li Si users. Display the user's number, name and age. Order number

  /*
    analysis:
    	User's number, name, age user table order number orderlist table
    	Condition: user id = orderlist. uid AND user. Name in ('zhang San ',' Li Si ');
    */
    SELECT
    	t1.`id`,	-- User number
    	t1.`name`,	-- User name
    	t1.`age`,	-- User age
    	t2.`number`	-- Order number
    FROM
    	USER t1,        -- User table
    	orderlist t2    -- Order form
    WHERE
    	t1.`id` = t2.`uid`
    	AND
    	-- (t1.`name` = 'Zhang San' OR t1.`name` = 'Li Si');
    	t1.`name` IN ('Zhang San','Li Si');

Query the number and classification name of commodity classification. Commodity name under classification

  /*
    analysis:
    	No. and category name of commodity classification table commodity name product table under category
    	Condition: category id = product. cid
    */
    SELECT
    	t1.`id`,	-- Classification number
    	t1.`name`,	-- Classification name
    	t2.`name`	-- Trade name
    FROM
    	category t1,	-- Commodity classification table
    	product t2	    -- Commodity list
    WHERE
    	t1.`id` = t2.`cid`;

Query all commodity classifications. Number and classification name of commodity classification. Commodity name under classification

  /*
    analysis:
    	No. and category name of commodity classification table commodity name product table under category
    	Condition: category id = product. cid
    	Query all commodity classifications and use the left outer connection
    */
    SELECT
    	t1.`id`,	-- Classification number
    	t1.`name`,	-- Classification name
    	t2.`name`	-- Trade name
    FROM
    	category t1	-- Commodity classification table
    LEFT OUTER JOIN
    	product t2	-- Commodity list
    ON
    	t1.`id` = t2.`cid`;

Query all product information. Number and classification name of commodity classification. Commodity name under classification

  /*
    analysis:
    	No. and category name of commodity classification table commodity name product table under category
    	Condition: category id = product. cid
    	Query all product information and use the right external connection
    */
    SELECT
    	t1.`id`,	-- Classification number
    	t1.`name`,	-- Classification name
    	t2.`name`	-- Trade name
    FROM
    	category t1	-- Commodity classification table
    RIGHT OUTER JOIN
    	product t2	-- Commodity list
    ON
    	t1.`id` = t2.`cid`;

Query all users and all products. Display the user's number, name and age. Trade name

 /*
    analysis:
    	User's number, name and age user table commodity name product table middle table us_pro
    	Condition: us_pro.uid = user.id AND us_pro.pid = product.id
    */
    SELECT
    	t1.`id`,	-- User number
    	t1.`name`,	-- User name
    	t1.`age`,	-- User age
    	t2.`name`	-- Trade name
    FROM
    	USER t1,	-- User table
    	product t2,	-- Commodity list
    	us_pro t3	-- Intermediate table
    WHERE
    	t3.`uid` = t1.`id`
    	AND
    	t3.`pid` = t2.`id`;

Users can see these two products. Display the user's number, name and age. Trade name

  /*
    analysis:
    	User's number, name and age user table commodity name product table middle table us_pro
    	Condition: us_pro.uid = user.id AND us_pro.pid = product.id AND user.name IN ('zhang San ',' Li Si ')
    */
    SELECT
    	t1.`id`,	-- User number
    	t1.`name`,	-- User name
    	t1.`age`,	-- User age
    	t2.`name`	-- Trade name
    FROM
    	USER t1,	-- User table
    	product t2,	-- Commodity list
    	us_pro t3	-- Intermediate table
    WHERE
    	(t3.`uid` = t1.`id` AND t3.`pid` = t2.`id`)
    	AND
    	-- (t1.`name` = 'Zhang San' or t1.`name` = 'Li Si');
    	t1.`name` IN ('Zhang San','Li Si');

7. Multi table query - Auto Association query

Introduction to Auto Association query
- There are data associations in the same table. You can query the same table multiple times!
Auto correlation query demonstration

   -- Create employee table
    CREATE TABLE employee(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(20),
    	mgr INT,
    	salary DOUBLE
    );
    -- Add data
    INSERT INTO employee VALUES (1001,'Sun WuKong',1005,9000.00),
    (1002,'Zhu Bajie',1005,8000.00),
    (1003,'Sand monk',1005,8500.00),
    (1004,'Little white dragon',1005,7900.00),
    (1005,'Tang Monk',NULL,15000.00),
    (1006,'Wu Song',1009,7600.00),
    (1007,'Li Kui',1009,7400.00),
    (1008,'Lin Chong',1009,8100.00),
    (1009,'Song Jiang',NULL,16000.00);
    
    -- Query the names of all employees and their direct superiors. Employees without superiors also need to query
    /*
    analysis:
    	Employee name employee table direct superior name employee table
    	Condition: employee mgr = employee. id
    	Query all the data of the left table and the intersection part of the data with the left and right tables, and use the left outer connection
    */
    SELECT
    	t1.name,	-- Employee name
    	t1.mgr,		-- Superior number
    	t2.id,		-- Employee number
    	t2.name     -- Employee name
    FROM
    	employee t1  -- Employee table
    LEFT OUTER JOIN
    	employee t2  -- Employee table
    ON
    	t1.mgr = t2.id;

4, View

1. Concept of view

A view is a virtual data table
This virtual table does not actually exist in the database
The function is to encapsulate the results of some complex query statements into a virtual table. When you have the same complex query in the later stage, you can query this virtual table directly
To put it bluntly, a view encapsulates the results of a SELECT query statement into a virtual table, so when we create a view, we should focus on the SELECT query statement

2. Benefits of view

simple
- Users who use views do not need to care about the table structure, association conditions and filter conditions. Because what is saved in this virtual table is the result set with filtered conditions
security
- A view can set permissions so that users who access the view can only access the result set they are allowed to query
Data independence
- Once the structure of the view is determined, the impact of changes in the table structure on users can be shielded, and adding columns to the source table has no impact on the view; If the column name of the source table is modified, it can be solved by modifying the view without affecting the visitors

3. View data preparation

 -- establish db7 database
    CREATE DATABASE db7;
    
    -- use db7 database
    USE db7;
    
    -- establish country surface
    CREATE TABLE country(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	country_name VARCHAR(30)
    );
    -- Add data
    INSERT INTO country VALUES (NULL,'China'),(NULL,'U.S.A'),(NULL,'Russia');
    
    -- establish city surface
    CREATE TABLE city(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	city_name VARCHAR(30),
    	cid INT, -- Foreign key column. relation country Primary key column of table id
    	CONSTRAINT cc_fk1 FOREIGN KEY (cid) REFERENCES country(id)
    );
    -- Add data
    INSERT INTO city VALUES (NULL,'Beijing',1),(NULL,'Shanghai',1),(NULL,'New York',2),(NULL,'Moscow',3);

4. Created view

Create view syntax

-- Standard grammar
CREATE VIEW View name [(Column name list)] AS Query statement;

Common multi table query, query city and country

   -- Common multi table query, query city and country
    SELECT
    	t1.*,
    	t2.country_name
    FROM
    	city t1,
    	country t2
    WHERE
    	t1.cid = t2.id;
    	
    -- If you often need to query such data, you can create a view

Create view basic presentation

  -- Create a view. Save the query results to this virtual table
    CREATE
    VIEW
    	city_country
    AS
    	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

Basic presentation of creating a view and specifying column names

  -- Create a view and specify the column name. Save the query results to this virtual table
    CREATE
    VIEW
    	city_country2 (city_id,city_name,cid,country_name) 
    AS
    	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

5. Query of view

Query view syntax

 -- Standard grammar
    SELECT * FROM View name;

Basic demonstration of query view

  -- Query view. Querying this virtual table is equivalent to querying the city and country
    SELECT * FROM city_country;
    
    -- Queries the view for the specified column name
    SELECT * FROM city_country2;
    
    -- Query all data tables and the view will also be queried
    SHOW TABLES;

Query view creation syntax

  -- Standard grammar
    SHOW CREATE VIEW View name;

Basic demonstration of query view creation statement

SHOW CREATE VIEW city_country;

6. View modification

Modify data in view tables

 -- Standard grammar
    UPDATE View name SET Listing=value WHERE condition;
    
    -- Change the city name in the view table to Beijing
    UPDATE city_country SET city_name='Beijing' WHERE city_name='Beijing';
    
    -- Query view
    SELECT * FROM city_country;
    
    -- query city surface,Beijing is also revised to Beijing
    SELECT * FROM city;
    
    -- Note: the data in the source table will be modified automatically when the view table data is modified

Modify view table structure

   -- Standard grammar
    ALTER VIEW View name [(Column name list)] AS Query statement;
    
    -- Query view 2
    SELECT * FROM city_country2;
    
    -- Modify the column name of view 2 city_id by id
    ALTER
    VIEW
    	city_country2 (id,city_name,cid,country_name)
    AS
    	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

7. Delete view

Delete view

   -- Standard grammar
    DROP VIEW [IF EXISTS] View name;
    
    -- Delete view
    DROP VIEW city_country;
    
    -- Delete view 2, if any
    DROP VIEW IF EXISTS city_country2;

8. Summary of views

A view is a virtual data table
This virtual table does not actually exist in the database
To put it bluntly, a view encapsulates the results of a SELECT query statement into a virtual table, so when we create a view, we should focus on the SELECT query statement
Benefits of view
- simple
- security
- Data independence

Topics: Database MySQL

Programmer Think

MySQL learning notes (Advanced)

1, Restraint

1. Foreign key constraints

2. Cascade update and cascade deletion of foreign keys (understand)

2, Multi table design

1. One to one

2. One to many

3. Many to many

3, Multi table query

1. Multi table query - data preparation

2. Multi table query - Cartesian product query (understand)

3. Multi table query - internal connection query

4. Multi table query - external connection query

5. Multi table query - sub query

6. Multi table query exercise

7. Multi table query - Auto Association query

4, View

1. Concept of view

2. Benefits of view

3. View data preparation

4. Created view

5. Query of view

6. View modification

7. Delete view

8. Summary of views

Hot Topics