MySQL

Keywords: MySQL Database SQL Stored Procedure

MySQL

Open source relational database management system

Chapter 1 Installation

MSI installation
Website downloaded on the official website
After downloading, install next all the way. The installation path defaults to c: Program Files MySQL, when there is no data folder in the MySQL Server 5.7 bin directory.
Configure the environment of mysql in the path of the environment variable (for example, my C: Program Files mysql MySQL Server 5.7 bin;).
Next you need to modify my-default configuration file for mysql

# basedir = C:\Program Files\MySQL\MySQL Server 5.7
# datadir = C:\Program Files\MySQL\MySQL Server 5.7\data

Next, configure it in cmd.
Run cmd as an administrator. After entering the bin folder:
1. Run the mysqld-initialize command, and a data folder will be automatically generated under MySQL Server 5.7.
2. Enter mysqld -install
3. net start mysql starts MySQL service. If you download version 5.7 of MySQL, Mysql's name defaults to MySQL57 on windows services, so running net start/stop mysql on cmd is invalid and must be changed to net start/stop mysql 57.
Next time you want to open it, just enter it as an administrator and enter the third step.

MySQL login

Sign in:
Local Connection
mysql –uroot -p
- Remote Connection
mysql –uwgb –hXXX.XXX.XXX.XXX –p
You can enter mysql parameters
Exit: MySQL > exit;
mysql > quit;
mysql > \q;
Modify the MySQL prompt:
Prompt changed to localhost

parameter	describe
/D	Full Date
/d	Current database
/h	Server name
/u	Current user

Common commands:
Display server version
SELECT VERSION();
Current date and time
SELECT NOW();
Current user
SELECT USER();
SET NAMES gbk; // affects only the display data of the client, and does not affect the data in the real data table.

Statement Specification:
All uppercase keywords and function names
All lowercase database name, table name, field name
The SQL statement must end with a semicolon

Operating database

Create a database
CREATE {DATDABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
View the list of tables under the current server
SHOW {DATABASE | SCHEMA} [LIKE 'pattern' | WHERE expr]
modify the database
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
Delete the database
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Chapter 2 Data Types and Operational Data Tables

It refers to the data characteristics of columns, stored procedure parameters, expressions and local variables, which determine the form of data storage.

Data table operation

Data table is a part of database and the basis of other objects.

Open the database
USE database name
Create data tables
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
…
)
View data tables
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
View the data table structure
SHOW COLUMNS FROM tbl_name
Insert record INSERT
INSERT [INTO] tbl_name [(col_name,…)] VALUES(val,…)
All fields are assigned values
- Search Record SELECT
  SELECT expr,… FROM tb1_name
  Null and non-null values recorded:
  NULL, field values can be null
  NOT NULL, field values are forbidden to be null
  AUTO_INCREMENT
  Automatic numbering, which must be combined with the primary key, starts at 1 (floating point of integer or decimal 0) by default.
  PRIMARY KEY
  Each data table can only have one primary key, which guarantees the uniqueness of the record. The primary key is NOT NULL automatically.
  UNIQUE KEY
  Unique constraint guarantees the uniqueness of records. Fields can be null NULL, with multiple unique constraints per table
  DEFAULT
  When inserting records, default values are automatically assigned to fields if they are not explicitly assigned.

Chapter 3 Constraints and Modifications of Data Tables

Constraints ensure data integrity and consistency
Constraints are classified into table-level constraints and column-level constraints.
Constraint type:
NOT NULL
PRIMARY KEY
UNIQUE KEY
DEFAULT
FOREIGN KEY
Maintain data consistency and integrity; implement one-to-one or one-to-many relationships
Requirements for foreign key constraints:
Parent and child tables must use the same storage engine, and temporary tables are prohibited. (The child table refers to the parent table)
Data table storage engine can only be InnoDB
Foreign key columns and reference columns must have similar data types. The length of the number or whether there are symbolic bits must be the same; the length of the character can be different.
Foreign key columns and reference columns (id in the example) must be indexed. If the reference column does not have an index, Mysql automatically creates the index. (Foreign key column does not exist, will not automatically.)
Default Storage Engine for Editing Datasheets
Mysql configuration file
default-storage-engine=INNODB

Provincial ID is a foreign key

mysql> CREATE TABLE pro(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL);
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) REFERENCES pro(id)
    -> );

View the index:
SHOW INDEXES FROM pro;
SHOW INDEXES FROM proG; grid form
Reference operations for foreign key constraints
1. CASCADE: Deletes or updates matched rows from parent tables and automatically deletes or updates matched rows from child tables
2. SET NULL: Delete or update rows from the parent table and set the foreign key in the child table as NULL. If you use this option, you must ensure that subtable columns do not specify NOT NULL
3. RESTRICT: Deny deletion or update of parent table
4. NO ACTION Standard SQL Keyword, same as RESTRICT in mysql
Records must now be inserted into the parent table and then into the child table.

mysql> CREATE TABLE users1(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) REFERENCES pro(id) ON DELETE CASCADE
    -> );

mysql> INSERT pro (pname )VALUES('A');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT pro (pname )VALUES('B');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT pro (pname )VALUES('C');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM pro;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
|  3 | C     |

mysql> INSERT users1 (username,pid )VALUES('tom', 3);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT users1 (username,pid )VALUES('jih', 7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `pro` (`id`) ON DELETE CASCADE)
mysql> INSERT users1 (username,pid )VALUES('tyom',1);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT users1 (username,pid )VALUES('rosm',3);
Query OK, 1 row affected (0.02 sec)


mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | tom      |    3 |
|  3 | tyom     |    1 |
|  4 | rosm     |    3 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> DELETE FROM pro WHERE id = 3;
Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM pro;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  3 | tyom     |    1 |
+----+----------+------+
1 row in set (0.00 sec)

Constraints on a data column, column level constraints (declared at column definition and after column definition)
Constraints created by multiple data columns are table-level constraints that can only be declared after column definitions.

Modify the data table

Add and delete fields
Add single row
ALTER TABLE tb1_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
Add multiple columns
ALTER TABLE tb1_name ADD [COLUMN] (col_name column_definition,…)
Delete columns:
ALTER TABLE tb1_name DROP [COLUMN] (col_name column_definition,…)

mysql> ALTER TABLE users1 ADD passqord  VARCHAR(32) NOT NULL AFTER username;
Query OK, 0 rows affected (0.37 sec)

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| passqord | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+

Adding primary key constraints (only 1)
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)
Adding Unique Constraints
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [index_type] (index_col_name,..)
Add/delete default constraints
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEAFULT}

mysql> SHOW COLUMNs FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);

mysql> SHOW COLUMNs FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
+----------+----------------------+------+-----+---------+-------+

mysql> ALTER TABLE users2 ADD UNIQUE (username);
mysql> show create table users2;
+--------+-----------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                       |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| age      | tinyint(3) unsigned  | NO   |     | 15      |       |
+----------+----------------------+------+-----+---------+-------+

Delete primary key constraints
ALTER TABLE users2 DROP PRIMARY KEY;
Delete unique constraints
ALTER TABLE users2 DROP {INDEX|KEY} index_name;

mysql> SHOW INDEXEs FrOM users2 \G;
*************************** 1. row ***************************
        Table: users2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users2
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:

Delete foreign key constraints

Display current constraints:

mysql> show create table users2;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                             |
+--------+----------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL,
  `age` tinyint(3) unsigned NOT NULL DEFAULT '15',
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `pro` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table users2;
+--------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                         
+--------+--------------------------------------
| users2 | CREATE TABLE `users2` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL,
  `age` tinyint(3) unsigned NOT NULL DEFAULT '15',
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Remove the index on the pid:
mysql> ALTER TABLE users2 DROP index pid;

| users2 | CREATE TABLE `users2` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL,
  `age` tinyint(3) unsigned NOT NULL DEFAULT '15',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Modifying column definitions
Let the id field come first:
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
Modify data types
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL ;
Modify column names
ALTER TABLE users2 CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER] col_name
Modify the type and name at the same time
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
![][18]
Conclusion:
![][19]

Chapter 4 Records in Operational Data Table

1. insertion

The first is:

mysql> CREATE TABLE users4(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) not null,
    -> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
    -> sex boolean);
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT users4 VALUES(NULL, 'johnm','789', 25,1 );
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM users4;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tim      |          |  25 |    1 |
|  2 | johnm    | 789      |  25 |    1 |
+----+----------+----------+-----+------+

Set the primary key to NULL and automatically ascend the order
Or:
INSERT users4 VALUES(default, 'scyda','789', 25,1 );
INSERT users4 VALUES(default, 'scyda','789', 5 * 5,1 );
If the field is DEFAULT, it can be replaced by DEFAULT without assignment.
INSERT users 4 VALUES (NULL,'Rose', md5('123'), DEFAULT, 0);md5 is a hash value of 123
Second species:
The first difference is that subQueries can be used
INSERT [INTO] tbl_name SET col_name ={expr | DEFAULT},…
mysql> INSERT users4 SET username ='Ben',password='456';
Query OK, 1 row affected (0.05 sec)
Third species:
INSERT [INTO] tbl_name [(col_name,…)] SELECT …
This method inserts the query results into the specified data table

2. Updating Records

MySQL > UPDATE users 4 SET age = age + 5; all ages plus 5

mysql> UPDATE users4 SET age = age - id, sex = 0 WHERE id % 2 = 0;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM users4;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tim      |          |  30 |    1 |
|  2 | johnm    | 789      |  28 |    0 |
|  3 | scyda    | 789      |  30 |    1 |
|  6 | tom      | 123      |  24 |    0 |
|  7 | Ben      | 456      |  15 | NULL |
+----+----------+----------+-----+------+

3. Delete records

DELETE FROM tbl_name [WHERE where_condition]

mysql> DELETE FROM users4 WHERE id = 7;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM users4;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tim      |          |  30 |    1 |
|  2 | johnm    | 789      |  28 |    0 |
|  3 | scyda    | 789      |  30 |    1 |
|  6 | tom      | 123      |  24 |    0 |
+----+----------+----------+-----+------+

Delete id7 and insert it at this time. The new ID is 8.

4. SELECT

SELECT select_expr[,select_expr…]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC|DESC],…]
[HAVING where_condition]
[ORDER BY {col_name|expr|position} [ADC|DESC],…]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
Query expression:
Each expression represents a desired column and must have at least one.
Multiple columns are marked by commas in English; they are all columns of all columns, tbl_name. Name table; they can be aliased with [AS]ailas_name; aliases can be used in GROUP BY,ORDER BY or HAVING clauses.

mysql> SELECT 3 + 5;
+-------+
| 3 + 5 |
+-------+
|     8 |
+-------+
mysql> SELECT id,username FROM users4;//id and username order is not required
+----+----------+
| id | username |
+----+----------+
|  1 | tim      |
|  2 | johnm    |
|  3 | scyda    |
|  6 | tom      |
|  8 | 111      |
+----+----------+
mysql> SELECT users4.* FROM users4;
mysql> SELECT id AS userId,username AS uname FROM users4;
+--------+-------+
| userId | uname |
+--------+-------+
|      1 | tim   |
|      2 | johnm |
|      3 | scyda |
|      6 | tom   |
|      8 | 111   |
+--------+-------+
mysql> SELECT id username FROM users4;//alias
+----------+
| username |
+----------+
|        1 |
|        2 |
|        3 |
|        6 |
|        8 |
+----------+

5. WHERE
If the WHERE clause is not specified, all records are displayed; in a WHERE expression, Mysql-supported functions or operators can be used.
6. GROUP BY
[GROUP BY {col_name |position} [ASC|DESC],..]

mysql> SELECT sex FROM users4 GROUP BY sex;
+------+
| sex  |
+------+
| NULL |
|    0 |
|    1 |
mysql> SELECT sex FROM users4 GROUP BY 1;

7. HAVING
Grouping condition

mysql> SELECT sex FROM users4 GROUP BY 1 HAVING count(id) >= 2;
+------+
| sex  |
+------+
|    0 |
|    1 |

8. ORDER BY
Sort the query results

mysql> SELECT * FROM users4 ORDER BY id DESC;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  8 | 111      | 22       |  22 | NULL |
|  6 | tom      | 123      |  24 |    0 |
|  3 | scyda    | 789      |  30 |    1 |
|  2 | johnm    | 789      |  28 |    0 |
|  1 | tim      |          |  30 |    1 |
+----+----------+----------+-----+------+
// First, according to age, if the same age, id descending order
mysql> SELECT * FROM users4 ORDER BY age;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  8 | 111      | 22       |  22 | NULL |
|  6 | tom      | 123      |  24 |    0 |
|  2 | johnm    | 789      |  28 |    0 |
|  1 | tim      |          |  30 |    1 |
|  3 | scyda    | 789      |  30 |    1 |
+----+----------+----------+-----+------+
mysql> SELECT * FROM users4 ORDER BY age,id DESC;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  8 | 111      | 22       |  22 | NULL |
|  6 | tom      | 123      |  24 |    0 |
|  2 | johnm    | 789      |  28 |    0 |
|  3 | scyda    | 789      |  30 |    1 |
|  1 | tim      |          |  30 |    1 |
+----+----------+----------+-----+------+

8. LIMIT
Limit the number of query results returned
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

mysql> SELECT * FROM users4;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tim      |          |  30 |    1 |
|  2 | johnm    | 789      |  28 |    0 |
|  3 | scyda    | 789      |  30 |    1 |
|  6 | tom      | 123      |  24 |    0 |
|  8 | 111      | 22       |  22 | NULL |
+----+----------+----------+-----+------+
mysql> SELECT * FROM users4 LIMIT 2;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tim      |          |  30 |    1 |
|  2 | johnm    | 789      |  28 |    0 |
+----+----------+----------+-----+------+
mysql> SELECT * FROM users4 LIMIT 3,2; // Offset
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  6 | tom      | 123      |  24 |    0 |
|  8 | 111      | 22       |  22 | NULL |
+----+----------+----------+-----+------+
mysql> SELECT * FROM users4 ORDER BY id DESC LIMIT 2,2;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  3 | scyda    | 789      |  30 |    1 |
|  2 | johnm    | 789      |  28 |    0 |
+----+----------+----------+-----+------+

mysql> CREATE TABLE test (
    -> id TINYINT UNSIGNED PRIMARY KEY  AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
// Write users4 to test
mysql> INSERT test(username) SELECT username FROM users4 WHERE age >= 25;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | tim      |
|  2 | johnm    |
|  3 | scyda    |

LIMIT offset formula: current page number minus one times the number of records displayed per page

Chapter 5 Subquery and Connection

Subquery

Subqueries are nested within queries and must always appear in parentheses. Subqueries can contain multiple keywords or conditions, such as DISTINCT,GROUP BY,ORDER BY, LIMIT, functions, etc.
The outer queries of sub-queries can be SELECT,INSERT,UPDATE,SET or DO.
Example sentence:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
Among them, SELECT * FROM t1... Called Outer Query (or Outer Statement),
SELECT column N1 FROM T2 is called Sub Query [sub query].
So we say that sub-queries are nested inside the external queries. In fact, it is possible to Re-Nest sub-queries within sub-queries.
Subqueries must appear between parentheses. Subqueries can return scalars, rows, columns or subqueries.
Line-level subqueries:
SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
A row-level subquery returns a maximum of one row.
Optimizing subqueries

Subqueries using comparison operators

mysql> SELECT AVG(goods_price) FROM tdb_goods;
+------------------+
| AVG(goods_price) |
+------------------+
|     5636.3636364 |
+------------------+
// Keep the last two decimal points
mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
+---------------------------+
| ROUND(AVG(goods_price),2) |
+---------------------------+
|                   5636.36 |
+---------------------------+
mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price >= 5636;

Using subqueries:

mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods);

mysql> select goods_price from tdb_goods where goods_cate='Super edition';
+-------------+
| goods_price |
+-------------+
|    4999.000 |
|    4299.000 |
|    7999.000 |
+-------------+
mysql> select * from tdb_goods where goods_cate = 'Super edition';
+----------+---------------------------------+------------+------------+-------------+---------+------------+
| goods_id | goods_name                      | goods_cate | brand_name | goods_price | is_show | is_saleoff |
+----------+---------------------------------+------------+------------+-------------+---------+------------+
|        5 | X240(20ALA0EYCD) 12.5 Inch superpole | Super edition     | association       |    4999.000 |       1 |          0 |
|        6 | U330P 13.3 Inch superpole            | Super edition     | association       |    4299.000 |       1 |          0 |
|        7 | SVP13226SCB 13.3 Inch Touch Superpole  | Super edition     | SONY       |    7999.000 |       1 |          0 |
+----------+---------------------------------+------------+------------+-------------+---------+------------+

If the sub-query returns multiple results, the following modifications can be used:
COMPARISON OPERATORS MODIFIED WITH ANY SOME ALL
operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)

Operators and keywords	ANY	SOME	ALL
'>' >=	minimum value	minimum value	Maximum value
< <=	Maximum value	Maximum value	minimum value
=	Arbitrary value	Arbitrary value
< > !=			Arbitrary value

mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price > ANY(select goods_price from tdb_goods where goods_cate = 'Super edition');
+----------+----------------------------------+-------------+
| goods_id | goods_name                       | goods_price |
+----------+----------------------------------+-------------+
|        2 | Y400N 14.0 Inch laptop         |    4899.000 |
|        3 | G150TH 15.6 Inch Gamebook            |    8499.000 |
|        5 | X240(20ALA0EYCD) 12.5 Inch superpole  |    4999.000 |
|        7 | SVP13226SCB 13.3 Inch Touch Superpole   |    7999.000 |
|       13 | iMac ME086CH/A 21.5 Inch-in-one computer  |    9188.000 |
|       16 | PowerEdge T110 II The server          |    5388.000 |
|       17 | Mac Pro MD878CH/A Professional desktop computers |   28888.000 |
|       18 |  HMZ-T3W Head-mounted display            |    6999.000 |
|       20 | X3250 M4 Rack Server 2583 i14     |    6888.000 |
|       21 |  HMZ-T3W Head-mounted display            |    6999.000 |
+----------+----------------------------------+---------
mysql> select goods_id,goods_name,goods_price from tdb_goods WHERE goods_price = ANY(select goods_price from tdb_goods where goods_cate = 'Super edition');
+----------+---------------------------------+-------------+
| goods_id | goods_name                      | goods_price |
+----------+---------------------------------+-------------+
|        5 | X240(20ALA0EYCD) 12.5 Inch superpole |    4999.000 |
|        6 | U330P 13.3 Inch superpole            |    4299.000 |
|        7 | SVP13226SCB 13.3 Inch Touch Superpole  |    7999.000 |
+----------+---------------------------------+-------------+

Subqueries using [NOT] IN

= ANY Operator and IN Equivalent
! = ALL or <> ALL is equivalent to NOT IN
Excluding the three superbooks, there are 19 left:
MySQL > select goods_id, goods_name, goods_price from tdb_goods WHERE goods_price!= ALL (select goods_price from tdb_goods where goods_cate='superbook');

Subqueries using [NOT] EXISTS

If the subquery returns any rows, EXISTS returns true, otherwise false.

Multi table updating

This means updating the parameters of this table with reference to another table.
Create a table

mysql> CREATE TABLE IF NOT EXISTS tdb_goods_cates(
    -> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> cate_name VARCHAR(40) NOT NULL);

mysql> SELECT goods_cate from tdb_goods GROUP BY goods_cate;
+---------------+
| goods_cate    |
+---------------+
| Desktop computer        |
| Tablet PC      |
| The server/Workstation |
| Game book        |
| Notebook        |
| Notebook Accessories    |
| Super edition        |
+---------------+

Write the query results to the data table:
INSERT [INTO] tbl_name [(col_name,..)] SELECT…

mysql> insert tdb_goods_cates(cate_name) select goods_cate FROM tdb_goods GROUP BY goods_cate;
Query OK, 7 rows affected (0.02 sec)
mysql> select * from tdb_goods_cates;
+---------+---------------+
| cate_id | cate_name     |
+---------+---------------+
|       1 | Desktop computer        |
|       2 | Tablet PC      |
|       3 | The server/Workstation |
|       4 | Game book        |
|       5 | Notebook        |
|       6 | Notebook Accessories    |
|       7 | Super edition        |
+---------+---------------+

Use foreign keys to update multiple tables
Connection type:
INNER JOIN, Internal Connection
In MYsql, JOIN,CROSS JOIN and INNER JOIN are equivalent
LEFT[OUTER] JOIN, Left Outside Connection
RIGHT[OUTER] JOIN Right External Connection
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
-> SET goods_cate = cate_id;

One-step multi-table update

Create a data table and write the query results to the data table
CREATE TABLE
[(create_difinition)]
select_statement

mysql> select brand_name from tdb_goods group by brand_name;
+------------+
| brand_name |
+------------+
| IBM        |
| ASUS       |
| Acer       |
| HP       |
| DELL       |
| SONY       |
| association       |
| Apple       |
| Raytheon       |
+------------+
// At the time of creation:
mysql> CREATE TABLE tdb_goods_brands(
    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> brand_name VARCHAR(40) NOT NULL)
    -> select brand_name from tdb_goods group by brand_name;
Query OK, 9 rows affected (0.28 sec)

mysql> show tables;
+------------------+
| Tables_in_goods  |
+------------------+
| tdb_goods        |
| tdb_goods_brands |
| tdb_goods_cates  |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from tdb_goods_brands \G;
*************************** 1. row ***************************
  brand_id: 1
brand_name: IBM
*************************** 2. row ***************************
  brand_id: 2
brand_name: ASUS
*************************** 3. row ***************************
  brand_id: 3
brand_name: Acer
*************************** 4. row ***************************
  brand_id: 4
brand_name: HP
*************************** 5. row ***************************
  brand_id: 5
brand_name: DELL
*************************** 6. row ***************************
  brand_id: 6
brand_name: SONY
*************************** 7. row ***************************
  brand_id: 7
brand_name: association
*************************** 8. row ***************************
  brand_id: 8
brand_name: Apple
*************************** 9. row ***************************
  brand_id: 9
brand_name: Raytheon
9 rows in set (0.00 sec)

Update the tdb-goods table (id) by referring to the brand table.
Both tables have brand_name, which is wrong

mysql> update tdb_goods inner join tdb_goods_brands on brand_name = brand_name
    -> set brand_name = brand_id;
//Alias
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous
mysql> update tdb_goods AS a inner join tdb_goods_brands AS b on a.brand_name = b.brand_name
    -> set a.brand_name = brand_id;
Query OK, 22 rows affected (0.09 sec)

mysql> select * from tdb_goods \G;
*************************** 1. row ***************************
  goods_id: 1
 goods_name: R510VC 15.6Inch notebook
 goods_cate: Notebook
 brand_name: 2
goods_price: 3399.000
    is_show: 1
 is_saleoff: 0

At this point in show columns from tdb_goods; in brand_name and goods_cate, or
VARCHAR(40) type, after modification, look at the table structure

mysql> ALTER TABLE tdb_goods
    -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
    -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
Query OK, 22 rows affected (0.68 sec)

mysql> show columns from tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field       | Type                   | Null | Key | Default | Extra          |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
| goods_name  | varchar(150)           | NO   |     | NULL    |                |
| cate_id     | smallint(5) unsigned   | NO   |     | NULL    |                |
| brand_id    | smallint(5) unsigned   | NO   |     | NULL    |                |
| goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
| is_show     | tinyint(1)             | NO   |     | 1       |                |
| is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
+-------------+------------------------+------+-----+---------+----------------+
mysql> select * from tdb_goods \G;
*************************** 1. row ***************************
   goods_id: 1
 goods_name: R510VC 15.6Inch notebook
    cate_id: 5
   brand_id: 2
goods_price: 3399.000
    is_show: 1
 is_saleoff: 0

Insert records in the tdb_goods_cates and tdb_goods_brands tables, respectively:
INSERT tdb_goods_cates(cate_name) VALUES('router'), ('switch'), and ('network card');
INSERT tdb_goods_brands(brand_name) VALUES('Haier'), ('Tsinghua Tongfang'), and ('Shenzhou');
Write arbitrary records in the tdb_goods data table
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES('LaserJet Pro P1606dn black and white laser printer','12','4','1849');
There's a mistake here. Cat_id doesn't have 12:

mysql> select * from tdb_goods_cates;
+---------+---------------+
| cate_id | cate_name     |
+---------+---------------+
|       1 | Desktop computer        |
|       2 | Tablet PC      |
|       3 | The server/Workstation |
|       4 | Game book        |
|       5 | Notebook        |
|       6 | Notebook Accessories    |
|       7 | Super edition        |
|       8 | Router        |
|       9 | Switch        |
|      10 | Network card          |
+---------+---------------+

Connect

Mysql supports JOIN operations in SELECT statements, multi-table updates, and multi-table deletion statements.
table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference ON conditional_expr
Data tables can be aliased using tbl_name AS alias_name or tbl_name alias_name.
table_subquery can be used as a subquery in the FROM clause, and such a subquery must be given an alias for it.
1. Internal connection (showing qualified records for left and right tables)
JOIN, CROSS JOIN, INNER JOIN Equivalence
2. Left outer connection (showing all records of the left table and records of the right table meeting the connection criteria)
LEFT [OUTER] JOIN
3. Right Outer Connection
RIGHT [OUTER] JOIN
Use the keyword ON to set the connection conditions, or WHERE to replace (typically used for filtering result set records)

mysql> select goods_id, goods_name,cate_name from tdb_goods inner join tdb_goods_cates
    -> on tdb_goods.cate_id = tdb_goods_cates.cate_id;
+----------+------------------------------------------------------------------------+---------------+
| goods_id | goods_name                                                             | cate_name     |
+----------+------------------------------------------------------------------------+---------------+
|        1 | R510VC 15.6 Inch notebook                                                  | Notebook        |
|        2 | Y400N 14.0 Inch laptop                                               | Notebook        |
|        3 | G150TH 15.6 Inch Gamebook                                                  | Game book        |
|        4 | X550CC 15.6 Inch notebook                                                  | Notebook        |
|        5 | X240(20ALA0EYCD) 12.5 Inch superpole                                        | Super edition        |
|        6 | U330P 13.3 Inch superpole                                                   | Super edition        |
|        7 | SVP13226SCB 13.3 Inch Touch Superpole                                         | Super edition        |
|        8 | iPad mini MD531CH/A 7.9 Inch Tablet Computer                                    | Tablet PC      |
|        9 | iPad Air MD788CH/A 9.7 Inch Tablet Computer (16) G WiFi Edition)                      | Tablet PC      |
|       10 |  iPad mini ME279CH/A Equipment Retina Display 7.9 Inch Tablet Computer (16) G WiFi Edition) | Tablet PC      |
|       11 | IdeaCentre C340 20 Inch-in-one computer                                         | Desktop computer        |
|       12 | Vostro 3800-R1206 Desktop computer                                             | Desktop computer        |
|       13 | iMac ME086CH/A 21.5 Inch-in-one computer                                        | Desktop computer        |
|       14 | AT7-7414LP Desktop computer( i5-3450 Quad core 4 G 500G 2G Self evident DVD Key mouse Linux )     | Desktop computer        |
|       15 | Z220SFF F4F06PA Workstation                                                  | The server/Workstation |
|       16 | PowerEdge T110 II The server                                                | The server/Workstation |
|       17 | Mac Pro MD878CH/A Professional desktop computers                                       | The server/Workstation |
|       18 |  HMZ-T3W Head-mounted display                                                  | Notebook Accessories    |
|       19 | Business Backpack                                                           | Notebook Accessories    |
|       20 | X3250 M4 Rack Server 2583 i14                                           | The server/Workstation |
|       21 |  HMZ-T3W Head-mounted display                                                  | Notebook Accessories    |
|       22 | Business Backpack                                                           | Notebook Accessories    |

Because the 23 just joined does not meet the connection conditions, so only 22.
Left outside:
mysql> select goods_id, goods_name,cate_name from tdb_goods left join tdb_goods_cates
-> on tdb_goods.cate_id = tdb_goods_cates.cate_id;
There is this article:
23 | LaserJet Pro P1606dn Black and White Laser Printer | NULL|
+———-+————-
Right outside:
The newly added item No. 23 does not conform to the right table, so it does not exist. It only exists when inserted into the right table.
***************** 23. row *****************
goods_id: NULL
goods_name: NULL
Cat_name: Router
***************** 24. row *****************
goods_id: NULL
goods_name: NULL
cate_name: Switch
***************** 25. row *****************
goods_id: NULL
goods_name: NULL
Cat_name: Network Card
25 rows in set (0.00 sec)

Multi table connection

mysql> select goods_id,goods_name,cate_name,brand_name, goods_price from tdb_goods AS g  inner join
    -> tdb_goods_cates AS c on g.cate_id = c.cate_id
    -> inner join tdb_goods_brands AS b on g.brand_id = b.brand_id \G
*************************** 1. row ***************************
   goods_id: 1
 goods_name: R510VC 15.6 Inch notebook
  cate_name: Notebook
 brand_name: ASUS
goods_price: 3399.000
*************************** 2. row ***************************
   goods_id: 2
 goods_name: Y400N 14.0 Inch laptop
  cate_name: Notebook
 brand_name: association
goods_price: 4899.000
*************************** 3. row ***************************
   goods_id: 3
 goods_name: G150TH 15.6 Inch Gamebook
  cate_name: Game book
 brand_name: Raytheon
goods_price: 8499.000
*************************** 4. row ***************************
   goods_id: 4
 goods_name: X550CC 15.6 Inch notebook
  cate_name: Notebook
 brand_name: ASUS
goods_price: 2799.000
*************************** 5. row ***************************
   goods_id: 5
 goods_name: X240(20ALA0EYCD) 12.5 Inch superpole
  cate_name: Super edition
 brand_name: association
goods_price: 4999.000
*************************** 6. row ***************************
   goods_id: 6
 goods_name: U330P 13.3 Inch superpole
  cate_name: Super edition
 brand_name: association
goods_price: 4299.000
*************************** 7. row ***************************
   goods_id: 7
 goods_name: SVP13226SCB 13.3 Inch Touch Superpole
  cate_name: Super edition
 brand_name: SONY
goods_price: 7999.000
*************************** 8. row ***************************
   goods_id: 8
 goods_name: iPad mini MD531CH/A 7.9 Inch Tablet Computer
  cate_name: Tablet PC
 brand_name: Apple
goods_price: 1998.000
*************************** 9. row ***************************
   goods_id: 9
 goods_name: iPad Air MD788CH/A 9.7 Inch Tablet Computer (16) G WiFi Edition)
  cate_name: Tablet PC
 brand_name: Apple
goods_price: 3388.000
*************************** 10. row ***************************
   goods_id: 10
 goods_name:  iPad mini ME279CH/A Equipment Retina Display 7.9 Inch Tablet Computer (16) G WiFi Edition)
  cate_name: Tablet PC
 brand_name: Apple
goods_price: 2788.000
*************************** 11. row ***************************
   goods_id: 11
 goods_name: IdeaCentre C340 20 Inch-in-one computer
  cate_name: Desktop computer
 brand_name: association
goods_price: 3499.000
*************************** 12. row ***************************
   goods_id: 12
 goods_name: Vostro 3800-R1206 Desktop computer
  cate_name: Desktop computer
 brand_name: DELL
goods_price: 2899.000
*************************** 13. row ***************************
   goods_id: 13
 goods_name: iMac ME086CH/A 21.5 Inch-in-one computer
  cate_name: Desktop computer
 brand_name: Apple
goods_price: 9188.000
*************************** 14. row ***************************
   goods_id: 14
 goods_name: AT7-7414LP Desktop computer( i5-3450 Quad core 4 G 500G 2G Self evident DVD Key mouse Linux )
  cate_name: Desktop computer
 brand_name: Acer
goods_price: 3699.000
*************************** 15. row ***************************
   goods_id: 15
 goods_name: Z220SFF F4F06PA Workstation
  cate_name: The server/Workstation
 brand_name: HP
goods_price: 4288.000
*************************** 16. row ***************************
   goods_id: 16
 goods_name: PowerEdge T110 II The server
  cate_name: The server/Workstation
 brand_name: DELL
goods_price: 5388.000
*************************** 17. row ***************************
   goods_id: 17
 goods_name: Mac Pro MD878CH/A Professional desktop computers
  cate_name: The server/Workstation
 brand_name: Apple
goods_price: 28888.000
*************************** 18. row ***************************
   goods_id: 18
 goods_name:  HMZ-T3W Head-mounted display
  cate_name: Notebook Accessories
 brand_name: SONY
goods_price: 6999.000
*************************** 19. row ***************************
   goods_id: 19
 goods_name: Business Backpack
  cate_name: Notebook Accessories
 brand_name: SONY
goods_price: 99.000
*************************** 20. row ***************************
   goods_id: 20
 goods_name: X3250 M4 Rack Server 2583 i14
  cate_name: The server/Workstation
 brand_name: IBM
goods_price: 6888.000
*************************** 21. row ***************************
   goods_id: 21
 goods_name:  HMZ-T3W Head-mounted display
  cate_name: Notebook Accessories
 brand_name: SONY
goods_price: 6999.000
*************************** 22. row ***************************
   goods_id: 22
 goods_name: Business Backpack
  cate_name: Notebook Accessories
 brand_name: SONY
goods_price: 99.000
22 rows in set (0.00 sec)

Several points about connection:
External connection:
A LEFT JOIN B join_condition
The result set of table B depends on table A (records in table A are shown in table B, otherwise they cannot be displayed)
The result set of data table A depends on all data tables according to the left join condition (except table B)
The left outer join condition determines how to retrieve table B (without specifying the WHERE condition), i.e.
If a record in table A meets the WHERE criteria, but there is no record in table B that meets the join criteria, all additional B rows listed as null (NULL) will be generated.
If the record lookup using the inner join does not exist in the join data table, and try the following operation in the WHERE clause: when col_name IS NULL, if col_name is defined as NOT NULL, mysql will stop searching for more rows after finding the record that meets the join criteria.

Infinitely categorized data table design:
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
Household appliances, computers and offices are top-level categories with parent_id of 0.

mysql> select * from tdb_goods_types;
+---------+------------+-----------+
| type_id | type_name  | parent_id |
+---------+------------+-----------+
|       1 | Household Electric Appliances   |         0 |
|       2 | Computer, Office |         0 |
|       3 | Large household appliances     |         1 |
|       4 | Living appliances   |         1 |
|       5 | Flat panel TV   |         3 |
|       6 | Air conditioner       |         3 |
|       7 | Electric fan     |         4 |
|       8 | Water dispenser     |         4 |
|       9 | Computer machine   |         2 |
|      10 | Computer accessories   |         2 |
|      11 | Notebook     |         9 |
|      12 | Super edition     |         9 |
|      13 | Game book     |         9 |
|      14 | CPU        |        10 |
|      15 | Host       |        10 |
+---------+------------+-----------+

Self connection
The same data table joins itself. On the left is the child table son, and on the right is the parent table.

mysql> SELECT s.type_id,s.type_name,p.type_name from tdb_goods_types as s
    -> left join tdb_goods_types as p
    -> on s.parent_id = p.type_id;
+---------+------------+------------+
| type_id | type_name  | type_name  |
+---------+------------+------------+
|       1 | Household Electric Appliances   | NULL       |
|       2 | Computer, Office | NULL       |
|       3 | Large household appliances     | Household Electric Appliances   |
|       4 | Living appliances   | Household Electric Appliances   |
|       5 | Flat panel TV   | Large household appliances     |
|       6 | Air conditioner       | Large household appliances     |
|       7 | Electric fan     | Living appliances   |
|       8 | Water dispenser     | Living appliances   |
|       9 | Computer machine   | Computer, Office |
|      10 | Computer accessories   | Computer, Office |
|      11 | Notebook     | Computer machine   |
|      12 | Super edition     | Computer machine   |
|      13 | Game book     | Computer machine   |
|      14 | CPU        | Computer accessories   |
|      15 | Host       | Computer accessories   |
+---------+------------+------------+

Conversely:

mysql> select p.type_id,p.type_name,s.type_name from tdb_goods_types as p left join
    -> tdb_goods_types as s on s.parent_id = p.type_id;

Number of subclasses of the parent class:

mysql> select p.type_id,p.type_name,count(s.type_name) child_count from tdb_goods_types as p left join
    -> tdb_goods_types as s on s.parent_id = p.type_id group by p.type_name order by p.type_id;
+---------+------------+-------------+
| type_id | type_name  | child_count |
+---------+------------+-------------+
|       1 | Household Electric Appliances   |           2 |
|       2 | Computer, Office |           2 |
|       3 | Large household appliances     |           2 |
|       4 | Living appliances   |           2 |
|       5 | Flat panel TV   |           0 |
|       6 | Air conditioner       |           0 |
|       7 | Electric fan     |           0 |
|       8 | Water dispenser     |           0 |
|       9 | Computer machine   |           3 |
|      10 | Computer accessories   |           2 |
|      11 | Notebook     |           0 |
|      12 | Super edition     |           0 |
|      13 | Game book     |           0 |
|      14 | CPU        |           0 |
|      15 | Host       |           0 |
+---------+------------+-------------+

Multiple table deletion
select * from tdb_goods has duplicate records.
A total of 23 records are shown here, 21 records (2 duplicates)

mysql> select goods_id,goods_name from tdb_goods group by goods_name;
+----------+------------------------------------------------------------------------+
| goods_id | goods_name                                                             |
+----------+------------------------------------------------------------------------+
|       18 |  HMZ-T3W Head-mounted display                                                  |
|       10 |  iPad mini ME279CH/A Equipment Retina Display 7.9 Inch Tablet Computer (16) G WiFi Edition) |
|       23 |  LaserJet Pro P1606dn Black and White Laser Printer                                   |
|       14 | AT7-7414LP Desktop computer( i5-3450 Quad core 4 G 500G 2G Self evident DVD Key mouse Linux )     |
|        3 | G150TH 15.6 Inch Gamebook                                                  |
|       11 | IdeaCentre C340 20 Inch-in-one computer                                         |
|       13 | iMac ME086CH/A 21.5 Inch-in-one computer                                        |
|        9 | iPad Air MD788CH/A 9.7 Inch Tablet Computer (16) G WiFi Edition)                      |
|        8 | iPad mini MD531CH/A 7.9 Inch Tablet Computer                                    |
|       17 | Mac Pro MD878CH/A Professional desktop computers                                       |
|       16 | PowerEdge T110 II The server                                                |
|        1 | R510VC 15.6 Inch notebook                                                  |
|        7 | SVP13226SCB 13.3 Inch Touch Superpole                                         |
|        6 | U330P 13.3 Inch superpole                                                   |
|       12 | Vostro 3800-R1206 Desktop computer                                             |
|        5 | X240(20ALA0EYCD) 12.5 Inch superpole                                        |
|       20 | X3250 M4 Rack Server 2583 i14                                           |
|        4 | X550CC 15.6 Inch notebook                                                  |
|        2 | Y400N 14.0 Inch laptop                                               |
|       15 | Z220SFF F4F06PA Workstation                                                  |
|       19 | Business Backpack                                                           |

mysql> select goods_id,goods_name from tdb_goods group by goods_name having count(goods_name) >= 2;
+----------+-----------------------+
| goods_id | goods_name            |
+----------+-----------------------+
|       18 |  HMZ-T3W Head-mounted display |
|       19 | Business Backpack          |
+----------+-----------------------+
2 rows in set (0.02 sec)

The two items shown above are to be deleted. Delete t1 and keep items with small id

mysql> delete t1 from tdb_goods as t1 left join (select goods_id,goods_name from tdb_goods group by goods_name having count(goods_name) >= 2) as t2 on t1.goods_name = t2.goods_name where t1.goods_id > t2.goods_id;
Query OK, 2 rows affected (0.16 sec)

Looking at it, we found that 21 and 22 were deleted, and 21 was located at 23.

mysql> select * from tdb_goods \G
*************************** 21. row ***************************
   goods_id: 23
 goods_name:  LaserJet Pro P1606dn Black and White Laser Printer
    cate_id: 12
   brand_id: 4
goods_price: 1849.000
    is_show: 1
 is_saleoff: 0

Chapter 6 Built-in Function Library

Character function
NUMERICAL OPERATORS AND FUNCTIONS
COMPARISON OPERATORS AND FUNCTIONS
Date-time function
Information function
Aggregate function

1. Character function

Function name
CONCAT()	Character linking
CONCAT_WS()	Use the specified delimiter for character concatenation
FORMAT()	NumberFormatter
LOWER()	Converting to lowercase letters
UPPER()	Convert to capital letters
LEFT()	Get the left character
RIGHT()	Get the right character
LENGTH()	Get string length
RIGHT()	Get the right character
LTRIM()	Delete leading spaces
RTRIM()	Delete subsequent spaces
TRIM()	Delete Leader and Follow-up
SUBSTRING()	substr
[NOT]LIKE()	pattern matching
REPLACE()	String substitution

The leading character is the space before the first character
Following is the space after the last character

mysql> use test1;
Database changed
mysql> select concat('test1', '-','imooc');
+------------------------------+
| concat('test1', '-','imooc') |
+------------------------------+
| test1-imooc                  |
+------------------------------+
mysql> select concat(first_name,last_name) as fullname from test;
+----------+
| fullname |
+----------+
| AB       |
| cd       |
| tom123   |
| NULL     |
+----------+
mysql> select concat_ws('|','A','B','C');
+----------------------------+
| concat_ws('|','A','B','C') |
+----------------------------+
| A|B|C                      |
+----------------------------+
1 row in set (0.00 sec)
mysql> select format(12345.75,1);
+--------------------+
| format(12345.75,1) |
+--------------------+
| 12,345.8           |
+--------------------+
mysql> select lower('Mysql');
+----------------+
| lower('Mysql') |
+----------------+
| mysql          |
+----------------+
mysql> select lower (left('Mysql',2));
+-------------------------+
| lower (left('Mysql',2)) |
+-------------------------+
| my                      |
+-------------------------+
mysql> SELECT length('mysql ');// Include spaces
mysql> select length('  mysql    ');
+-----------------------+
| length('  mysql    ') |
+-----------------------+
|                    11 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select length(ltrim('  mysql    '));
+------------------------------+
| length(ltrim('  mysql    ')) |
+------------------------------+
|                            9 |
+------------------------------+
//Delete the leading? Character in the string
mysql> select trim(leading'?' from '??mysql');
+---------------------------------+
| trim(leading'?' from '??mysql') |
+---------------------------------+
| mysql                           |
+---------------------------------+
mysql> select trim(trailing'?' from '??mysql???');
+-------------------------------------+
| trim(trailing'?' from '??mysql???') |
+-------------------------------------+
| ??mysql                             |
+-------------------------------------+
//Delete before and after
mysql> select trim(both '?' from '??mysql???');
mysql> select replace ('??my??sql???','?','');
+---------------------------------+
| replace ('??my??sql???','?','') |
+---------------------------------+
| mysql                           |
+---------------------------------+

mysql> select substring('mysql',1,2);
+------------------------+
| substring('mysql',1,2) |
+------------------------+
| my                     |
+------------------------+
mysql> select substring('mysql',1);//Cut to the end
// - 1 intercept from the last
mysql> select substring('mysql',-1);
+-----------------------+
| substring('mysql',-1) |
+-----------------------+
| l                     |
+-----------------------+

Cut two from the first place, and the number in the database starts from 1.
% Represents any character
_ Represents any character

// 1 indicate true
mysql> select 'mysql' like 'm%';
+-------------------+
| 'mysql' like 'm%' |
+-------------------+
|                 1 |
+-------------------+
mysql> select * from test;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A          | B         |
| c          | d         |
| NULL       | 11        |
| tom%       | 123       |

mysql> select * from test where first_name like '%o%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom%       | 123       |
+------------+-----------+
// The first and last% are wildcards, and here the second is also considered wildcards.
mysql> select * from test where first_name like '%%%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A          | B         |
| c          | d         |
| tom%       | 123       |
+------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from test where first_name like '%1%%' escape '1';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom%       | 123       |
+------------+-----------+

2. Numeric Operators

mysql> select 3 + 4;
+-------+
| 3 + 4 |
+-------+
|     7 |
+-------+

Name	describe
CEIL()	Entering and finishing
DIV	Integer division
FLOOR()	Let's get the whole thing done.
MOD	Remainder (modulus)
POWER()	exponentiation
ROUND()	Rounding
TRUNCATE()	Digital interception

mysql> select ceil(3.01); // 4
mysql> select 3/4; // 0.7500
mysql> select 3 div 4; // 0
MySQL > select 5% 3; / / equivalent to 5 mod 3, 2
mysql> select power(3,3); //27
MySQL > select round (3.652,2); / / reserve two decimal places, 3,65
mysql> select round(3.652,1); //3.7
mysql> select round(3.652,0); //4
mysql> select truncate(125.89,0);//125
MySQL > select truncate (125.89, - 1); / 120 remove the whole one

3. Comparing Operators and Functions

Name	describe
[NOT]BETWEEN…AND..	[No] within the scope
[NOT]IN()	[No] within the range of listed values
IS [NOT] NULL	[No] Empty

MySQL > select 15 between 1 and 20; / / output is 1
mysql> select 10 in (5,10,15); // 1
mysql> select null is null; // 1
MySQL > select "is null;//empty string is not null
// Usage: Check which user's first_name is empty

mysql> select * from test where first_name is null;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| NULL       | 11        |
+------------+-----------+
mysql> select * from test where first_name is not null;

4. Date-time function

function	Name
NOW()	Current date and time
CURDATE()	current date
CURTIME()	current time
DATE_ADD()	Date change
DATEDIFF()	Date difference
DATE_FORMAT()	DateFormatter

SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);
2015-03-12
SELECT DATE_ADD('2014-3-12',INTERVAL -3 WEEK);
SELECT DATE_DIFF('2013-3-12','2014-3-12');
-365
SELECT DATE_FORMAT('2013-3-2','%m/%d/%Y'); // Default Leader 0
03/02/2014

5. Information function

function	Name
CONNECTION_ID()	connection ID
DATABASE()	Current database
LAST_INSERT_ID()	Last insert record
USER()	Current user
VERSION()	Version information

// View current
mysql> DESC table1;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| first_name  | varchar(20) | YES  |     | NULL    |       |
| second_name | varchar(20) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
mysql> alter table table1 ADD id SMALLINT UNSIGNED
    -> KEY AUTO_INCREMENT FIRST;

 id          | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name  | varchar(20)          | YES  |     | NULL    |                |
| second_name | varchar(20)          | YES  |     | NULL    |
mysql> insert table1(first_name,second_name) values('11','22');
// Get the id number newly written at present:
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                5 |
+------------------+
// last_insert_id(), if you continue insert two, only the id:6 written in the first entry is returned.

6. Aggregation function

function	Name
AVG()	average value
COUNT()	count
MAX()	Maximum value
MIN()	minimum value
SUM()	Summation

select * from tdb_goods LIMIT 1; // Just look at one.
mysql> select ROUND(avg(goods_price),2) as avg_price from tdb_goods;
+-----------+
| avg_price |
+-----------+
|   5845.10 |
+-----------+
// How many records are there?
mysql> select count(goods_id) as counts from tdb_goods;
+--------+
| counts |
+--------+
|     20 |
+--------+
mysql> select max(goods_price) as counts from tdb_goods;

7. Encryption function

function	Name
MD5()	Information Abstraction Algorithms
PASSWORD()	cryptographic algorithm

mysql> SELECT MD5('admin')
    -> ;
+----------------------------------+
| MD5('admin')                     |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.08 sec)
// password is used to modify passwords
mysql> SET PASSWORD = PASSWORD('123');

If you are ready for a web page, recommend MD5

Chapter 7 Custom Functions

User-defined functions (UDF) are a way to extend mysql in the same way as built-in functions.

Necessary conditions for custom functions: parameters, return values

CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} rourine_body

mysql> select date_format(now(), '%y year%m month %h spot');
+-------------------------------------+
| date_format(now(), '%y year%m month %h spot') |
+-------------------------------------+
| 16 09:00 DECEMBER                       |
+-------------------------------------+

mysql> create function f1() returns VARCHAR(30)
    -> return date_format(now(),'%Y year%m month%d day %h Points:%i Points:%s second');
Query OK, 0 rows affected (0.11 sec)

mysql> select f1();
+---------------------------------+
| f1()                            |
+---------------------------------+
| 2016 Dec. 14 09:21:17 |
+---------------------------------+

// Taking ginseng
mysql> create function f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
    -> returns float(10,2) unsigned
    -> return (num1 + num2)/2;
Query OK, 0 rows affected (0.03 sec)

mysql> select f2(10,15);
+-----------+
| f2(10,15) |
+-----------+
|     12.50 |
+-----------+
1 row in set (0.03 sec)
//Censored function
DROP function adduser;
// All commands are passed // terminated
mysql> delimiter //
mysql> create function adduser(username VARCHAR(20))
    -> returns INT UNSIGNED
    -> return
    -> insert test(username) values(username);
    -> last_insert_id();
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test(username) values(username);
last_insert_id()' at line 4
mysql> create function adduser(username VARCHAR(20))
    -> returns INT UNSIGNED
    -> begin
    -> insert test(username) values(username);
    -> return last_insert_id();
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> select adduser('rose');
    -> //
+-----------------+
| adduser('rose') |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.08 sec)

stored procedure

SQL commands - > Mysql engine - > (analysis) grammatically correct - > recognizable commands - > (execution) results - > (return) client

Stored procedures are precompiled sets of SQL statements and control statements stored in a name and processed as a unit.
Stored procedures are stored in databases, can receive parameters, and can have multiple return values

Enhancing the Function and Flexibility of SQL Statements
Achieve faster execution speed
Reducing network traffic
Create stored procedures:
CREATE [DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic…] routine_body

proc_parameter:
[IN| OUT |INOUT] parameter_name type
Parameters:
IN, indicating that the value of this parameter must be specified when calling the stored procedure
OUT, which means that the value of this parameter can be changed by the stored procedure and can be returned
INOUT, which indicates that the value of this parameter is specified when invoked and can be changed and returned
Characteristic:
COMMENT: Notes
CONTAINS SQL: Statements that contain SQL statements but do not contain read or write data
NO SQL: No SQL statements
READS SQL DATA: Statements that contain read data
MODIFIES SQL DATA: Statements containing write data
SQL SECURITY{DEFINER|INVOKER} specifies who has permission to execute

Procedures are made up of legitimate SQL statements (record additions, deletions, changes and multiple table connections, database or data table cannot be created)
Composite structure: BEGIN. END
Can include declarations, loops, control structures

Call stored procedures:
CALL sp_name([parameter[,…]])
CALL sp_name[()]

mysql> CREATE procedure sp1() select version();
Query OK, 0 rows affected (0.14 sec)
mysql> call sp1;
+------------+
| version()  |
+------------+
| 5.7.16-log |

IN:

mysql> desc test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(20) | YES  |     | NULL    |       |
| last_name  | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
mysql> create procedure removeUserId(IN id int unsigned)
    -> begin
    -> delete from test where id = id;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call removeUserId(3);
    -> //
Query OK, 4 rows affected (0.07 sec)
mysql> select * from test//
Empty set (0.00 sec)

The empty set is because where id = id, so the parameters cannot be the same as the fields in the data table

Stored procedures cannot modify the body of the procedure, they can only be deleted first.

mysql> create procedure removeUserId(in p_id INT UNSIGNED)
    -> begin
    -> delete from test where id = p_id;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test//
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | 1         |
|  2 | b          | 1         |
|  3 | c          | 123       |
+----+------------+-----------+

mysql> call removeUserId(3);//
Query OK, 1 row affected (0.10 sec)
mysql> select * from test//
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | 1         |
|  2 | b          | 1         |
+----+------------+-----------+
2 rows in set (0.00 sec)

mysql> create procedure removeAndReturnName(in p_id int unsigned, out countNum  int unsigned)
    -> begin
    -> delete from test where id =p_id;
    -> select count(id) from test into countNum;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> call removeAndReturnName(2, @nums);
    -> //
Query OK, 1 row affected (0.09 sec)
mysql> select @nums//
+-------+
| @nums |
+-------+
|     1 |
+-------+

With @ is the user variable

Insert two records at a time, and row_count() table updates the total number of records

mysql> insert test (username) values('c'),('d');
    -> select row_count();//
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

+-------------+
| row_count() |
+-------------+
|           2 |
+-------------+
1 row in set (0.10 sec)

mysql> update test set username = concat(username, '--a') where id <= 2;
    -> //
Query OK, 2 rows affected (0.08 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select row_count();//
+-------------+
| row_count() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

Delete by age, then return the number of deleted records and the number of remaining records

mysql> create procedure removeUserByAgeAndReturnInfos( in p_age smallint unsigned,out deleteNums int unsigned, out userCount smallint unsigned)
    -> begin
    -> delete from users where age = p_age;
    -> select row_count() into deleteNums;
    -> select count(id) from users into userCount;
    -> end //
Query OK, 0 rows affected (0.00 sec)
// call
call  removeUserByAgeAndReturnInfos(23, @a,@b);
// View the returned results
select @a,@b;

Reference link

Posted by StevenJ on Mon, 01 Apr 2019 01:03:30 -0700

Programmer Group

MySQL

MySQL

Chapter 1 Installation

MySQL login

Operating database

Chapter 2 Data Types and Operational Data Tables

Data table operation

Chapter 3 Constraints and Modifications of Data Tables

Modify the data table

Chapter 4 Records in Operational Data Table

1. insertion

2. Updating Records

3. Delete records

4. SELECT

Chapter 5 Subquery and Connection

Subquery

Subqueries using comparison operators

Subqueries using [NOT] IN

Subqueries using [NOT] EXISTS

Multi table updating

One-step multi-table update

Connect

Multi table connection

Chapter 6 Built-in Function Library

1. Character function

2. Numeric Operators

3. Comparing Operators and Functions

4. Date-time function

5. Information function

6. Aggregation function

7. Encryption function

Chapter 7 Custom Functions

stored procedure

Hot Keywords