This article will introduce the use of Mysql in detail, and some common pits, and how to use Nodejs to connect to the database, and then open source a set of CRM system based on Mysql database

0.0. The Node framework of the CRM system is intended to use Eggjs or Nestjs.

0.1. Pre-demand

  • The database visualization software used in this article is for NavicatPremium installation problems please contact baidu or Google for help

  • Install mysql database and start it. My version is 8.0

  • This is the Mysql information on my MAC

1, the introduction of SQL

  • Structured Query Language (SQL): Structured Query Language. It is essentially a rule that defines operations on all relational databases.
  • General grammar rules
    • SQL statements can be written on single or multiple lines, ending with a semicolon
    • You can use whitespace and indentation to enhance the readability of statements
    • SQL statements of the Mysql database are case insensitive. You are advised to use uppercase keywords
    • Single line comment: — Comment content # Comment content (this is unique to mysql)
    • Multi-line comments: /※ Comments content ※/
  • Classification of SQL
    • Data Definition Language (DDL): a Data Definition Language used to operate databases, tables, and columns
    • Data Manipulation Language (DML): Data Manipulation Language. Used to add, delete, change the data of the table in the database
    • Data Query Language (DQL): Data Query Language. Used to query records of tables in a database
    • Data Control Language (DCL): Data Control Language. Used to define database access and security levels, and to create users

DDL operates databases and tables

2.1. DDL query and create database

  • Query all databases

      SHOW DATABASES;
Copy the code

    • As is shown in

  • Query the creation statement of a database

    SHOW CREATE DATABASE Specifies the name of the DATABASE.Copy the code

    • As is shown in

  • Creating a database

    CREATE DATABASE Specifies the name of the DATABASE.Copy the code
    • As is shown in

      If the database is created successfully and the left database is not displayed, right-click the link name (test1) and click refresh

  • Create database (determine if database does not exist)

    CREATE DATABASE IF NOT EXISTS Specifies the name of the DATABASE.Copy the code
    • As is shown in

  • Create database (determine if database does not exist and specify character set)

    CREATE DATABASE IF NOT EXISTS Specifies the name of the DATABASE CHARACTER SET.Copy the code
    • As is shown in

2.2 DDL modify, delete, use database

  • Alter database (alter character set UTf8 -> GBK)

    ALTER DATABASE DATABASE name CHARACTER SET GBK;Copy the code

  • Deleting a Database

    DROP DATABASE Specifies the DATABASE name.Copy the code

  • Delete database (determine, delete if database exists)

    DROP DATABASE IF EXISTS Indicates the DATABASE nameCopy the code

  • Using a database

    USE Database name;Copy the code

  • View the current database in use

     SELECT DATABASE();
Copy the code
    • As is shown in

2.3 DDL operates databases and data tables

  • DDL queries data tables

    • Query all tables in the database

        SHOW TABLES;
Copy the code

    • As is shown in

    • Query the table structure

      DESC table name;Copy the code

    • As is shown in

      Field to explain

      • FieId: field
      • Type: indicates the Type of the field
      • Null: Indicates whether the current field is empty
      • Key: Key, index evaluated by the optimizer for final use
      • Default: the Default value
      • Extra: Indicates additional information

    • Query character set in data table

          SHOW TABLE STATUS FROM mysql LIKE 'user';
Copy the code

  • DDL creates data tables

    • Create table 
      CREATE TABLE datatype constraint, datatype constraint... Column name data type constraint);Copy the code

      • type
        • INT corresponds to number in JS
        • VARCHAR corresponds to string in JS
        • DATE corresponds to DATE in JS yyyY-MM-DD
        • TIME corresponds to HH:MM:SS of Date in JS
        • DATETIME corresponds to Date in THE JS format YYYY-MM-DD HH:MM:SS
        • TIMESTAMP corresponds to the new Date().getTime() TIMESTAMP in JS
      • For more data types, see Mysql Data Types
      • When creating a multi-column table, separate each column with a comma. Do not write a semicolon on the last column, otherwise an error will be reported
    • Modify the name of the table 
      ALTER TABLE name RENAME TO new TABLE name;Copy the code
    • Modify the character set of the table 
      ALTER TABLE name CHARACTER SET;Copy the code
    • Add a column to the table 
      ALTER TABLE name ADD column name data type;Copy the code
    • Modify the data type of a column 
      ALTER TABLE TABLE name MODIFY column name data type;Copy the code
    • Modify column names and data types 
      ALTER TABLE table_name CHANGE column name New column name Data type of new column name;Copy the code
    • Delete a column 
      ALTER TABLE table_name DROP table_name;Copy the code
    • As is shown in

  • DDL deletes tables

    DROP TABLE TABLE name;Copy the code

  • DDL deletes the table (judge, delete the table if it exists)

    DROP TABLE IF EXISTS Specifies the name of the TABLE.Copy the code

3. Add, delete, and modify DML table data (the data creation statement in this part of the screenshot is inDDL creates data tablesThe chapter is cut from the book.

  • DML adds table data

    • Add data to the specified column, with the same number of values as the number of columns and a one-to-one mapping of data types

      INSERT INTO table name VALUES (VALUES,...). ;Copy the code

      • Select * from db where id = 1 and username = 3
      • Select statements are involved in the screenshot, which will be explained in the DQL section

    • Add data to all columns

      INSERT INTO table name (1, 2,...) VALUES(1, 2...) ;Copy the code

    • Add data to all columns (short, the number of values should be the same as the number of columns in the table, and the data type should be one-to-one)

      INSERT INTO VALUES(1, 2,...) ;Copy the code

    • Batch add data to tables (multiple values separated by commas and terminated with semicolons)

      INSERT INTO table name (1, 2,...) VALUES(1, 2...) ,(value 1, value 2...) . ;Copy the code
      INSERT INTO VALUES(1, 2,...) ,(value 1, value 2...) . ;Copy the code

  • DML modifies table data

    UPDATE table_name SET table_name 1= 1, table_name 2= 2,... [] the WHERE condition;Copy the code

    When modifying table data, write a WHERE statement; otherwise, all columns will be modified

  • DML deletes table data

    DELETE FROM table name [WHERE condition];Copy the code

    When deleting a table, write a WHERE statement; otherwise, all data in the table will be deleted

————- databaseDDL, DMLCommonly used statements, often stepped on the pit are basically over, the following is about to enterDQLPart — — — — — — — — — — — — — — — –

3. DQL query data

Prefixes: All of them[]Both represent optional statements

3.1 Data preparation before query. The data created in DDL stage is not enough to support the following explanation. Therefore, new table data needs to be created

-- Data preparation; -- Use database; -- USE db1; -- -- Create table; CREATE TABLE product(id INT, -- id; Name VARCHAR(30), -- commodity name; Price DOUBLE, -- commodity price; Brand VARCHAR(30), -- Commodity brand; Stock INT, -- Inventory of goods; Insert_time DATE); INSERT INTO product VALUES (1, 'huawei ',' huawei ', 30, '2020-05-08'), (2, 'Nokia ', 30, '2020-05-08') (5999.99, 3, 'apple', 'huawei, 32,' 2020-05-09 '), (4, 'huawei computer', 8999.99, 'huawei, 33,' 2020-06-07 '), (5, 'nokia PC, 3999.99, "nokia", 34, '2020-07-07'), (6, 'apple', 12990.99 'apple', 35, '2020-08-07'), (7, 'asustek computer, 6999.99,' asustek, null, '2020-09-07'); SELECT * FROM product;Copy the code

3.2 The syntax and sequence of DQL queries are as follows

SELECT field list FROM table name list WHERE condition list GROUP BY GROUP list HAVING GROUP list ORDER BY sorting ascending and descending (ASC,desc) LIMITCopy the code

  • All the query

    SELECT * FROM table_name;Copy the code

  • Query specified column data, or query all data from specified columns

    SELECT columns 1, 2,... FROM the name of the table;Copy the code

  • Remove duplicate queries

    SELECT DISTINCT columns 1, 2... FROM the name of the table;Copy the code

  • Evaluates the value of the column

    SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM)));Copy the code

    • If the SQL statement is writtenSELECT name, stock+10 FROM product;So asustek’s stock is still null;

  • Alias query

    SELECT * FROM table_name where table_name = 1 and table_name = 1;Copy the code

  • Alias query (shorthand, with Spaces between column names and aliases)

    SELECT * FROM table_name where table_name = 1;Copy the code

3.2.1 DQL table data Query — Conditional query

  • grammar

    SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM)));Copy the code

  • Please refer to the query condition category

    • Mysql WHERE sentence 1
    • Mysql WHERE sentence 2

  • Next, we’ll use the chestnut 🌰 to illustrate some WHERE condition statements that are commonly used at work

    • 1, query the inventory of goods greater than 33;

          SELECT * FROM product WHERE stock>33;
Copy the code

    • 2. Query products with nokia brand;

      SELECT * FROM product WHERE brand=' nokia ';Copy the code

    • 3, query the amount of goods between 5000-10000;

      SELECT * from product WHERE price> 1000 AND price <1000; SELECT * FROM product WHERE price BETWEEN 5000 AND 10000;Copy the code

    • 4. Query the commodity information of inventory 30,33,35

      SELECT * FROM product WHERE stock = 30 OR stock = 33 OR stock = 35; SELECT * FROM product WHERE stock IN(30,33,35);Copy the code

    • 5, Select null from inventory

          SELECT * FROM product WHERE stock IS NULL; 
Copy the code

    • 6, query the inventory of goods not null

          SELECT * FROM product WHERE stock IS NOT NULL;
Copy the code

    • SQL > select * from product where name = nokia

      SELECT * FROM product WHERE name LIKE 'nokia %';Copy the code

    • Select * from product whose name is;

      SELECT * FROM product WHERE name = '% ';Copy the code

    • SQL > select * from product where product name is 5 characters (result is Nokia phone and Nokia computer);

          SELECT * FROM product WHERE name LIKE '_____';
Copy the code

3.2.2 DQL table data query — Aggregation function query

  • What is an aggregate function and what can it do

    • An aggregation function is a vertical calculation of a column of data as a whole

  • Syntax (WHERE is optional)

    SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM))Copy the code

  • Aggregation function method function classification

    • Mysql aggregate function

  • Next we will continue to illustrate some of the aggregation functions commonly used in our work through some of the nuts 🌰

      1. Query the total number of items in product
      SELECT COUNT(*) FROM product;
Copy the code
      1. Get the item with the highest price among the items;

      “ SELECT MAX(price) FROM product;

      Copy the code
      1. Obtain minimum inventory;
      SELECT MIN(stock) FROM product;
Copy the code
      1. Get total inventory (sum commodity inventory)
      SELECT SUM(stock) FROM product;
Copy the code
      1. Acquire the total inventory of goods of nokia brand;
      SELECT SUM(stock) FROM product WHERE brand=' nokia ';Copy the code

3.2.3 DQL table data query — sort query

  • What is a sort query and what can it do
    • The data in the columns of the table is arranged in ascending order and descending order
  • grammar 
    SELECT columns 1, 2,... FROM 表名 [WHERE 表名] ORDER BY 表名 1 [WHERE 表名] ORDER BY 表名 1 [WHERE 表名]Copy the code
    • WHERE is optional
    • There are only two types of sorting, ascending (ASC) descending (DESC). The default sort is ascending (ASC).
    • More than one column can be sorted in the same table. If there are more than one sorting condition, the second condition will only be judged if the first condition is the same
  • Next we will still go through some chestnuts 🌰 to some commonly used in workSorting queryMake notes
      1. Sort items in ascending order
          SELECT * FROM product ORDER BY stock ASC;
Copy the code
      1. Query the information of mobile phones contained in the product name and sort the amount in descending order;
      SELECT * FROM product WHERE name like '% phone %' ORDER BY price DESC;Copy the code
      1. Sort in ascending order by amount. If the amount is the same, sort in descending order by inventory.
      SELECT * FROM product ORDER BY price ASC, stock DESC;
Copy the code

3.2.4 DQL table data Query — Group query

  • grammar

    SELECT table name FROM table name [WHERE condition] GROUP BY GROUP name [HAVING condition] [ORDER BY sort]Copy the code

  • Chestnuts time, next we will still through some chestnuts 🌰 to work on some commonly used grouped queries to illustrate

      1. According to the brand grouping, get the total amount of each group of goods;
      SELECT brand, SUM(price) FROM product GROUP BY brand;
Copy the code
      1. Group the goods with an amount of more than 5000 yuan into brand groups to obtain the total amount of each group of goods;
      SELECT brand, SUM(price) FROM product WHERE price>5000 GROUP BY brand;
Copy the code
      1. The goods with a total amount of more than 5000 yuan are grouped into brands to obtain the total amount of each group of goods, and only the goods with a total amount of more than 8000 yuan are displayed
      SELECT brand, SUM(price) getSum FROM product WHERE price >5000 GROUP BY brand HAVING getSum >8000
Copy the code
      1. Group the goods with a total amount of more than 5000 yuan into brand groups, obtain the total amount of each group of goods, only display the goods with a total amount of more than 8000 yuan, and sort in descending order according to the total amount;
      SELECT brand, SUM(price) getSum FROM product WHERE price>5000 GROUP BY brand HAVING getSum>8000 ORDER BY getSum DESC;
Copy the code

3.2.4 DQL table data query — paging query

  • grammar

    SELECT * FROM table name [WHERE condition] [GROUP BY condition] [HAVING condition] [ORDER BY sort (ASC, DESC)] LIMIT N,M SELECT * FROM table name [WHERE condition] [GROUP BY condition] [HAVING condition] [ORDER BY sort (ASC, DESC)] LIMIT N,MCopy the code

  • Time is up, and we will continue to explain some of the common paging queries at work through some of the following 🌰

      1. How many items per page — three pieces of data are displayed;
      SELECT * FROM product LIMIT 0 , 3;
Copy the code

      • There are now seven items in the product table, and when we pass 0,3 to limit, it will display the first three items
      • When we pass 3,3 to limit, it will display the numbers 4 through 6
      • When we pass 6 and 3 to limit, it will only display the seventh data
      • So the first argument to limit starts at 0
      • If the limit query number is greater than the total number of entries, only the remaining total entries are displayed

————- databaseDQLCommonly used statements, often stepped on the pit is also basically finished, the following is about to enterThe constraintPart — — — — — — — — — — — — — — — –

4, constraints,

  • What is a sorted query
    • Constraints are basically a series of restrictions on the columns in a table. Ensure the correctness, validity and integrity of data!!
  • Classification of constraints
    • PRIMARY KEY Constraints Each table can have only one PRIMARY KEY, and columns cannot duplicate, and null values are not allowed
    • The AUTO_INCREMENT constraint takes effect when the PRIMARY KEY is AUTO_INCREMENT. If null is written in the AUTO_INCREMENT column when data is added, the AUTO_INCREMENT constraint starts from 1
    • SQL > alter table table UNIQUE
    • NOT NULL Column data in a non-null constraint table cannot be NULL
    • The FOREIGN KEY constraint is used to enforce the join between one or more columns of data from two tables (primary and secondary)
    • FOREIGN KEY ON UPDATE CASCADE CASCADE of FOREIGN KEY updates
    • FOREIGN KEY ON DELETE CASCADE DELETE FOREIGN KEY CASCADE
  • Chestnut time, next we will still through some chestnut 🌰 to some commonly used in the workThe constraintMake notes
    • Primary key constraint
      • Create table (id, name, age) where age id is primary key 
        CREATE TABLE student(
 id INT PRIMARY KEY,
 name VARCHAR(30),
 age INT
);
Copy the code
        • INSERT INTO sc VALUES(null, “三”,33); Columns with primary keys are not allowed to be null
      • Remove the primary key 
        ALTER TABLE student DROP PRIMARY KEY;
Copy the code
      • Add a separate group primary key to the column 
        ALTER TABLE student MODIFY id INT PRIMARY KEY; 
Copy the code
    • Primary key increment constraint
      • Create student table (ID, name, age) age ID is set to primary key 
        CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30),
 age INT
);
Copy the code
        • INSERT INTO sc VALUES(null, “三”,33); I can write it this way, because I set the primary key increment constraint, and I get 1, 3, 22
      • Delete the autoincrement constraint 
        ALTER TABLE student MODIFY id INT;
Copy the code
      • Add a separate increment constraint to the column 
        ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
Copy the code
    • The only constraints
      • Create table (id, name,age) age id = primary key; age = unique 
        CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30),
 age INT UNIQUE
);
Copy the code
        • INSERT INTO sc VALUES(null, “三”,33); ,INSERT INTO sc VALUES(null, “三”,33); When the same data is added to a unique constraint age, the data will fail to be added
      • Delete unique constraints 
        ALTER TABLE student DROP INDEX age;
Copy the code
      • Add a unique constraint to the column individually 
        ALTER TABLE student MODIFY age INT UNIQUE;
Copy the code
        • After removing the unique constraint and adding the same value to the column in the table, set the unique constraint on that column
    • Not null constraint
      • Create table (id,name,age) age id = primary key; age = unique; name = non-empty 
        CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30) NOT NULL,
 age INT UNIQUE
);
Copy the code
        • INSERT INTO student VALUES(null, null,33); This will give you an error
      • Delete a non-null constraint 
        ALTER TABLE student MODIFY name varchar(30);
Copy the code
      • Add a separate non-null constraint to the column 
        ALTER TABLE student MODIFY name  varchar(30) NOT NULL;
Copy the code
        • Add a null value to a null column after deleting a non-null constraint. Add a non-null constraint to a null column after deleting a non-null constraint. Add a non-null constraint to a null column after deleting a non-null constraint
    • Foreign key constraints

      • What is a foreign key constraint and what can it do

        • When tables are associated with each other, data accuracy cannot be guaranteed without related data constraints
        • For example, the fingerprints of people and people belong to two tables respectively, and we need to associate the fingerprints of people and people. At this time, constraints are needed to associate the two tables, so as to ensure the accuracy of data

      • grammar

        CREATE TABLE name (CONSTRAINT FOREIGN KEY name FOREIGN KEY name) CREATE TABLE name (CONSTRAINT FOREIGN KEY name FOREIGN KEY name) Create user table; CREATE TABLE user( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL UNIQUE, age INT NOT NULL UNIQUE ); -- -- Add data; INSERT INTO user VALUES(null, "三 三",33), (null," 三 三", 23); Create fingerprint table and add foreign key; CREATE TABLE fingerprint( id INT PRIMARY KEY AUTO_INCREMENT, number VARCHAR(40), uid INT, CONSTRAINT fu_fk1 FOREIGN KEY (uid) REFERENCES user(id) ); INSERT INTO fingerprint VALUES(null, '12234',1),(null, "2234",2); SELECT * FROM fingerprint;Copy the code

        • The UID in the figure corresponds to the ID in the User table, which means that data with ID 1 in the Fingerprint table is associated with data with ID 1 in the user table.
        • If you add a piece of data to the Fingerprint table associated with a primary key that is not present in the User table, an error is reported
        • If you delete data with ID 2 from the user table, an error is reported because the data with ID 2 is already associated with the Fingerprint table

      • Remove the foreign key

        ALTER TABLE TABLE name DROP FOREIGN KEY FOREIGN KEY name ALTER TABLE fingerprint DROP FOREIGN KEY fu_fk1;Copy the code

      • Add foreign keys separately

        ALTER TABLE name ADD CONSTRAINT FOREIGN KEY name REFERENCES Primary TABLE name ALTER TABLE fingerprint ADD CONSTRAINT fu_fk1 FOREIGN KEY (uid) REFERENCES user(id);Copy the code

————- databaseThe constraintCommonly used statements, often stepped on the pit is also basically finished, the following is about to enterMulti-table operationPart — — — — — — — — — — — — — — — –

5. Multiple table operations

  • What are multi-table operations
    • That is, there are multiple tables, and there is a certain association between tables, which requires foreign keys to constrain
  • Type of multi-table operation
    • One to one
    • More than a pair of
    • Many to many

5.1 one-to-one

  • Applicable scenario

    • For example, a person can only have one set of fingerprints, and one set of fingerprints can only correspond to one person
    • For example, one person can only have one ID card, and one ID card can only correspond to one person

  • Principle of building table

    • Create a foreign key for any table and unassociate the primary key of another table

  • For example, 🌰

    Create the person table; CREATE TABLE person(id INT PRIMARY KEY AUTO_INCREMENT, -- Name VARCHAR(30) NOT NULL; INSERT INTO person VALUES (null, "三"), (null," 三"); Select * from person; select * from person; CREATE TABLE card(id INT PRIMARY KEY AUTO_INCREMENT, -- unique id; Naem VARCHAR(200) NOT NULL UNIQUE, CONSTRAINT cp_fk1 FOREIGN KEY (PID) REFERENCES person(ID)); INSERT INTO card VALUES (null, "12345",1), (null,"22345",2);Copy the code
    • At this point, the one-to-one association is created. At this point, you say, “how did I know they were connected?” no panic, NavicatPremium provides a visual table diagram

5.2. One to many

  • Applicable scenario

    • Users and orders. A user can have multiple orders
    • Product categories and products. There can be more than one product category

  • Principle of building table

    • Establish a foreign key on the many party, associating the primary key of the one party

  • For example, 🌰

    Create user table; CREATE TABLE user(id INT PRIMARY KEY AUTO_INCREMENT, -- VARCHAR(30) -- user name); Add data to user table; INSERT INTO user VALUES (null, '三'), (null,' 三'); Create order table; CREATE TABLE orderList(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), -- uid INT, Uid indicates the ID in the associated user table. CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES user(id) ); INSERT INTo orderList VALUES (NULL, 'Huawei mobile ',1),(NULL,' Huawei Computer ',1),(NULL, 'iPhone ',2),(NULL,' Smarthphone ',2);Copy the code
    • Let’s look at the one-to-many diagram

5.3 Many to many

  • Applicable scenario

    • Students and courses. A student can choose more than one course, and courses can be selected by more than one student

  • Principle of building table

    • In the many-to-many case, only the third intermediate table can be used to associate the table to be associated. The intermediate table must contain at least two columns, which are used as the foreign key of the intermediate table to associate the primary key of the corresponding table

  • For example, 🌰

    Create db2 database; CREATE DATABASE db2; -- Use db2 database; USE db2; Create the person table; CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(30) -- user name); -- -- Add data; INSERT INTO sc VALUES (null, "三"), (null," 三"); -- Create class schedules; CREATE TABLE course(id INT PRIMARY KEY AUTO_INCREMENT, -- Name VARCHAR(30) -- course name; ; INSERT INTO sc VALUES (null, "score "), (null," score "); Create chapter 3 intermediate table; CREATE TABLE STU_course (id INT PRIMARY KEY AUTO_INCREMENT, -- Select * from student where id = 1; select * from student where id = 1; Select * from cid where CONSTRAINT sc_fk1 FOREIGN KEY (SID) REFERENCES student(id) CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id) ); INSERT INTO stu_course VALUES (null, 1,1),(null, 2,1),(null, 2,1),(null, 2,2);Copy the code
    • The association structure for the middle table STU_course looks like this
      • Zhang SAN –> Chinese
      • Zhang SAN –> Math
      • Li Si –> Chinese
      • Li Si –> Math
    • The diagram is as follows

5.4. Multi-table query

  • Prepare a wave of data

    CREATE TABLE user ( id INT PRIMARY KEY auto_increment, NAME VARCHAR(20) NOT NULL, age INT NOT NULL ); INSERT INTO VALUES (null, "三", 23), (null," 三", 24), (null, "三", 25), (null," 三", 26); CREATE TABLE orderlist ( id INT PRIMARY KEY auto_increment, number VARCHAR(30), uid INT, CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES user(id) ); -- -- Add data; INSERT INTO orderlist VALUES (NULL, "001", 1), (NULL, "002", 1), (NULL, "003", 2), (NULL, "004", 2), (NULL, "005", 3), (NULL, "006", 3), (NULL, "007", null); -- CREATE TABLE category ( id INT PRIMARY KEY auto_increment, NAME VARCHAR(20) NOT NULL ); INSERT INTO category VALUES (null," mobile "), (null," office "), (null," tobacco "), (null," shoes and boots "); CREATE TABLE product ( id INT PRIMARY KEY auto_increment, NAME VARCHAR(30) NOT NULL, cid INT, CONSTRAINT pc_fk1 FOREIGN KEY(cid) REFERENCES category(id) ); INSERT INTO product VALUES (null," huawei ", 1), (null," xiaomi ", 1), (null," lenovo ", 2), (null," apple ", 2), (null," Zhonghua ", 3), (NULL," Yuxi Cigarettes ", 3), (NULL," Family Planning Products ", NULL); -- User -- commodity intermediate table mapping; CREATE TABLE us_pro (upID INT PRIMARY KEY auto_increment, uid INT, --) Pid INT, -- establish a relationship with the item list; CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES user(id), CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id) ); INSERT INTO us_pro VALUES ( null, 1, 1 ), ( null, 1, 2 ), ( null, 1, 3 ), ( null, 1, 4 ), ( null, 1, 5 ), ( null, 1, 6 ), ( null, 1, 7 ), ( null, 2, 1 ), ( null, 2, 2 ), ( null, 2, 3 ), ( null, 2, 4 ), ( null, 2, 5 ), ( null, 2, 6 ), ( null, 2, 7 ), ( null, 3, 1 ), ( null, 3, 2 ), ( null, 3, 3 ), ( null, 3, 4 ), ( null, 3, 5 ), ( null, 3, 6 ), ( null, 3, 7 ), ( null, 4, 1 ), ( null, 4, 2 ), ( null, 4, 3 ), ( null, 4, 4 ), ( null, 4, 5 ), ( null, 4, 6 ), ( null, 4, 7 );Copy the code

    • The structure correlation diagram looks like this

5.4.1 Internal join query

  • Principle of query
    • An inner join query is a query for data that has an intersection part
  • For example, 🌰

    • Example Query user information and order information

      SELECT * FROM user u INNER JOIN orderList o ON U.id = o.ID; SELECT * FROM user u, orderList o WHERE u.ID = o.ID;Copy the code

5.4.2 External connection query

  • Left outer join query

    • Principle of query
      • Query all data from the left table and all data from the intersection of the two tables

  • Right outer join query

    • Principle of query
      • Query all data from the right table where two tables intersect

  • For example, 🌰

    Select * from user table; select * from user table; SELECT * FROM USER u LEFT OUTER JOIN orderList o ON U.ID = o.ID; SELECT * FROM USER u LEFT OUTER JOIN orderList o ON U.ID = o.ID; SELECT * FROM orderList o RIGHT OUTER JOIN USER u ON U.ID = o.ID;Copy the code

5.4.3 Sub-query

  • Principle of query

    • The query result of the current table continues to be queried as a query condition

  • For example, 🌰

    SELECT * FROM user WHERE age=(SELECT MAX(age) FROM user); -- multiple rows and single column; -- Query the order table of John and John; SELECT * FROM orderlist WHERE uid in (SELECT u.id FROM user u WHERE id in (1,2)); -- multiple rows and columns; Select * from user where id>4; SELECT * FROM user u RIGHT JOIN (SELECT * FROM orderList WHERE ID >4) o on U.id = o.ID; SELECT * FROM user u,(SELECT * FROM orderList WHERE ID >4) o WHERE id = o.ID;Copy the code

— — — — — — — — — — – work involves database CRUD knowledge about these, welcome to ask questions — — — — — — — — — — — —

— — — — — — — — — — – update — — — — — — — — — — — —

  • Nodej connect to Mysql- Series 1