Original article & Experience summary & from the university to A factory all the way sunshine vicissitudes

For details, please click www.coderccc.com

Basic MySQL operations can include two aspects: common MySQL statements such as the frequently used add, Delete, modify and check (CRUD) statements and advanced MySQL functions such as stored procedures, triggers, transactions, etc. And these two aspects can be subdivided as follows:

MySQL > Select * from ‘MySQL’;
1. CRUD for a table (or database)
2. CRUD for table data, where table data queries are the most used and more complex. Query can be divided into single table SELECT query, multi-table JOIN query (INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN) and UNION and UNION ALL query
3. The execution order of keywords in an SQL statement
Advanced features of MySQL
1. The stored procedure
2. Transaction processing
3. The trigger

1. Table (or database) operation statement

1.1. Query table (or database)

Get all available databases:SHOW DATABASES;
Select database:USE customers;
To display the database server status information:SHOW STATUS;
To display the security permissions of an authorized user:SHOW GRANTS;
To display database server or warning information:SHOW ERRORSorSHOW WARNINGS;
Used to display the create statement when the database is created:SHOW CREATE DATABASE customers;
Used to display the create statement when the table is created:SHOW CREATE TABLE customers;
Gets all the tables available in the currently selected database:SHOW TABLES;
Gets information about all columns in the table:SHOW COLUMNS FROM tableName; Also the DESCRIBE statement has the same effect:DESCRIBE tableName;

1.2. Create a table (or database)

CREATE DATABASE customers;
Tables can be created using the CREATE TABLE statement:
```
 CREATE TABLE customers(
   cust_id INT NOT NULL AUTO_INCREMENT,
   cust_name CHAR(50) NOT NULL,
   cust_age INT NULL DEFAULT 18,
   PRIMARY KEY(cust_id)
 )ENGINE=INNODB;
Copy the code
```
Here are some details:
1. If a NULL value is allowed, the value of the column is NOT given when the column is inserted. If a NOT NULL value is allowed, the value of the column must be given when the column is inserted or updated.
2. DEFAULT indicates the DEFAULT value of the column. If the column value is not given, the DEFAULT value will be used when inserting rows.
3. PRIMARY KEYUsed to specify a primary key. A primary key can specify a column of data. A primary key can be a combination of multiple columns, such asPRIMARY KEY(cust_id,cust_name);
4. ENGINE Specifies the ENGINE type. Common engine types are :(1) InnoDB is an engine that supports reliable transaction processing, but does not support full-text search; (2) MyISAM is a high-performance engine that supports full-text search, but not transactions; (3) MEMORY is functionally equivalent to MyISAM, but because the data is stored in MEMORY, it is very fast (especially suitable for temporary tables);
When creating a table, you can use a FOREIGN KEY to create a FOREIGN KEY. That is, a FOREIGN KEY in one table points to a PRIMARY KEY in another table. FOREIGN KEY The FOREIGN KEY is used to restrict the join action of the broken table and ensure the data integrity of the two tables. It also prevents illegal data from being inserted into a foreign key column, since the column value must point to another table’s primary key. Instance as follows:
```
 CREATE TABLE Orders
 (
 Id_O int NOT NULL,
 OrderNo int NOT NULL,
 Id_P int,
 PRIMARY KEY (Id_O),
 FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
 )
Copy the code
```

1.3 Delete table (or database)

Deleting a database:DROP DATABASE customers;
Delete table, use the DROP TABLE clause:DROP TABLE customers.

1.4 update the table

To update TABLE structure information, use the ALTER TABLE clause, for example, to ADD a column to the TABLE: ALTER TABLE Vendors ADD vend_name CHAR(20); It is also often used to define foreign keys, such as:
```
 ALTER TABLE customers 
 ADD CONSTRAINT fk_custormer_orders
 FOREIGN KEY(cust_id) REFERENCES orders (order_cust)
Copy the code
```
RENAME the table using the RENAME clause. RENAME TABLE backup_customers TO customers, backup_vendors TO vendors; Change multiple table names spaced by commas

2 Table data operation statements

2.1 Querying Table Data

Basic query statement

Query information about single, multiple, or all columns in a table based on filter conditionsSELECT FROM WEHERE:SELECT cust_id,cust_name FROM customers WHERE cust_id=10086; The filter condition operators are: =, <>,! =, <, < =, >, > =, BETWEEN AND, IS NULL;
To deduplicate a column of informationDISTINCT:SELECT DISTINCT cust_name FROM customers;
Limit the number of rows in a single-column query result:SELECT cust_name FROM customers LIMIT 5; LIMIT is followed by a value, which means that 5 rows of data are taken starting at row 0. If the LIMIT is 5, 5 means that you start at row 5 (the actual row 6 record in the database), and you get 5 rows of data. Note: The data is counted from row 0;
ORDER BYClause takes one or more columns and sorts the output according to them:SELECT cust_id,cust_name FROM customers ORDER BY cust_id DESC, cust_name;
INOperator to specify a range of conditions, each of which can be matched:SELECT cust_id, cust_name FROM customers WHERE cust_id IN (1000,2000). IN addition, the NOT operator can be used IN conjunction with the IN operator to indicate that all data that does NOT meet the criteria is retrieved.
LIKEThe operator is used to indicate fuzzy queries, with wildcards such as **%**, where % indicates any number of occurrences of any character;_._Indicates that only one character can be matched:SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE '%happy%';
Group query is used and certain group filtering conditions can be metGROUP BY HAVING. To retrieve order numbers with total order amounts greater than or equal to 50 and order total amounts, and sort by total amounts:SELECT order_num,SUM(quantity*item_price) AS order_total FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY order_total
Comparison of WHERE and HAVING. WHERE is row-level filtering, while HAVING is group-level filtering. Data filtered out by WHERE does not appear in the group. The WHERE wildcard and join of multiple WHERE clauses also apply to the HAVING clause;
(1) The GROUP BY clause can be nested (that is, groups can be grouped across multiple columns)GROUP BY cust_id, cust_name), but the data summary is carried out on the last specified group; (2) Each column listed in the GROUP BY clause must be either a retrieval column or a valid expression. (3) If there are NULL values, return NULL values as a group, if there are multiple rows of NULL values, they are divided into a group
Queries nested within other queries, calledThe subquery. The process is executed from the inside out, and the results of the inner query are used as the conditions of the outer query:SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy'). Of course, multi-table queries can be joined queries.

Join query

Inner joins, also known as inner joins, are based on equality tests between two tables. If the filter condition is not added, the cartesian product is created.SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id; It is also possible to use WHERE for multi-table JOIN queries, but INNER JOIN is more recommended.
The outer connection includes the left outer connectionLEFT JOINTo the outer rightRIGHT JOINAnd the whole connectionFULL JOIN. For example, query the order number of each customer:SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id; LEFT JOIN returns all data from the LEFT table, RIGHT JOIN returns all data from the RIGHT table, and FULL JOIN returns all data from the LEFT and RIGHT tables.
Join queries are used in conjunction with aggregate functions. For example, to query the order number of each customer:SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id;

Combination query

The UNION must contain two or more SELECT queries, and each pass must contain the same columns, expressions, or aggregation functions. The data types do not have to be identical. MySQL does implicit type conversions. SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001100);
UNION returns the deduplicated result, or UNION ALL can be used if deduplicated is not required.
Multiple combined queries can be sorted using ORDER BY, but the final result set is sorted, not a single SELECT query within it, so there is only one ORDER BY clause for a combined query. SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id

Use functions to process data

SELECT Concat (vendName,'(‘,vendCountry,’)’) FROM vendors ORDER BY vendName;
SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems;
Text processing functions such as Upper(),LTrim(),RTrim(), and so on. For example, use the Upper function to convert text to vendors ORDER BY vendName: SELECT vendName, Upper(vendName) FROM Vendors ORDER BY vendName;
Time and Date handlers, such as Date(),Day(), etc. SELECT custId, orderNum FROM orders WHERE Date(orderDate)=’2015-09-01′;
Numerical processing functions, such as Abs(),Cos(), etc.
A common aggregation function. Such as AVG(),COUNT(),MAX(),MIN(), and SUM(). SELECT COUNT(*) AS numbers, MIN(prod_price) AS price_min, AS price_max,AVG(prod_price) AS price_avg FROM products;

2.2 Inserting Table data

Inserting row data INTO a table can be done using the INSERT INTO clause. It is safer to specify column names.INSERT INTO customers (cust_name, cust_email) VALUES('happy','[email protected]'); Column names can be omitted in the INSERT INTO clause if the column is allowed to be defined as NULL or if the default value is given when the column is defined.
If you insert multiple rows of data, you can separate multiple sets of values with commas.INSERT INTO customers (cust_name, cust_email) VALUES('happy','[email protected]'),('smart','[email protected]');
To INSERT the queried data into the table, use the INSERT SELECT statement.INSERT INTO customers(cust_id,cust_contact) SELECT cust_id, cust_contact FROM customers WHERE cust_id>5; SELECT can contain the FILTER condition WHERE. INSERT SELECT is usually used to copy table data

2.3 Updating table Data

If you want to UPDATE table data, use the UPDATE clause:UPDATE customers SET cust_name ='happy',cust_email='[email protected]' WHERE cust_id = 1001;
Note: If you specify a row without the WHERE condition, all data in a column will be updated.

2.4 Delete table data

If you DELETE data from a table, you can use the DELETE clause.DELETE FROM customers WHERE cust_id = 10086; The data to be deleted must be a row of the table, not a column. Therefore, in contrast to the UPDATE clause, the DELETE clause does not specify the column, but only the specific table name.
Note: If you do not add a WHERE condition, all rows in the table will be deleted. In addition, DELETE only deletes the data in the table, not the table structure information.
To DELETE all data in a table, use TRUNCATE, which is more efficient than DELETE.

3. Keyword execution order in SQL

Each keyword in the SQL statement is executed sequentially, and each step generates a virtual table, which is returned as the final result of the execution. The following is the order in which the commonly used keywords are executed:

(8)SELECT (9)DISTINCT<select_list>
(1)FROM <left_table>
(3)<join_type> JOIN <right_table>
(2)            ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY<group_by_list>
(6)WITH{CUBE|ROLLUP}
(7)HAVING<having_condition>
(10)ORDER BY<order_by_list>
(11)LIMIT<limit_number>
Copy the code

FROM: Computes the Cartesian product of the table on the left and the table on the right of FROM, producing the virtual table VT1;
ON: Performs ON filtering ON virtual table VT1. Only rows that meet

conditions are recorded in virtual table VT2.
JOIN: If it is an OUT JOIN, then the unmatched rows in the reserved table (such as the left table or the right table) are added to the virtual table VT2 as external rows, resulting in virtual table VT3.
WHERE: Perform WHERE condition filtering on the virtual table VT3. Only the records matching

are put into the virtual table VT4;
GROUP BY: GROUP the virtual table VT4 according to the columns in the GROUP BY clause to generate the virtual table VT5.
CUBE | ROLLUP: VT5 of virtual table CUBE or a ROLLUP operation, produce VT6 virtual table;
HAVING: HAVING condition filtering is performed on virtual table VT6. Only records matching

are inserted into virtual table VT7.
SELECT: Perform the SELECT operation to SELECT the specified column and insert it into the virtual table VT8.
DISTINCT: Deduplication is performed on the records in virtual table VT8 to generate virtual table VT9.
ORDER BY: ORDER BY

to create VT10;
LIMIT: Fetch the records of the specified row, generate virtual table VT11, and return the results.

Index of 4.

The establishment of MySQL index is very important for the efficient operation of MySQL. The index can greatly improve the retrieval speed of MySQL. Indexes include single column indexes and composite indexes. Single-column indexes, where an index contains only a single column, and composite indexes, where an index contains multiple columns.

4.1 Creating an Index

There are two ways to CREATE indexes. One is to CREATE indexes directly by using CREATE INDEX, and the other is to add indexes by modifying the TABLE structure by using ALTER TABLE statement.

Using the CREATE INDEX

Grammar:
```
  CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
  [USING index_type]
  ON table_name (index_col_name,...)
Copy the code
```
The corresponding syntax variable information is as follows:

[UNIQUE|FULLTEXT|SPATIAL]

The three keywords in brackets indicate the types of indexes to be created. They indicate the unique index, full-text index, and spatial index respectively. If we do not specify any keywords, the default is plain index.

index_name

Index_name specifies the name of the index, which is defined by users to facilitate future management operations such as modifying the index.

index_type

Index_type specifies how an index is implemented. In MySQL, there are two different types of indexes — BTREE and HASH indexes. Only BTREE can be used in tables stored in MyISAM and InnoDB. The default value is BTREE. Both HASH and BTREE indexes can be used in tables with MEMORY or HEAP storage engines. The default value is HASH.

index_colname

Index_col_name indicates the name of the field in which the index is to be created. You can also create a composite index for multiple fields by separating the field names with commas (,). In addition, for fields of type CHAR or VARCHAR, we can use only the first part of the field content to create an index, by adding a directive like (length) after the corresponding field name, indicating that only the first length of the field content is needed to create the index. Here, we take the cust_name field of the Customers table (type VARCHAR(50)) as an example and use the six character prefixes of the CUST_NAME field to create the index.
```
 CREATE INDEX idx_cust_name ON user (cust_name(6));
Copy the code
```

Using the ALTER TABLE

Grammar:

 ALTER TABLE table_name
 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
 (index_col_name,...) [USING index_type]
Copy the code

4.2 Deleting an Index

Select * from table where index = index;

ALTER TABLE table_name
DROP INDEX index_name;
Copy the code

For example, to delete an index named IDX_CUST_name, run the following SQL statement:

ALTER TABLE customers
DROP INDEX idx_cust_name;
Copy the code

4.3 Modifying an Index

In MySQL, there is no direct instruction to modify the index. In general, we need to delete the original index, and then create an index with the same name as needed, so as to achieve the operation of modifying the index.

ALTER TABLE user DROP INDEX IDx_user_username; CREATE INDEX IDX_CUST_NAME ON customers (CUST_NAME (8));Copy the code

4.4 Viewing indexes

In MySQL, viewing the indexes in a database table is as simple as using either of the following two commands.

FROM db_name SHOW INDEX FROM table_name [FROM db_name] If the user db_name command is not used to specify a specific database, you must add db_name. Prefix SHOW INDEX FROM [db_name.]table_nameCopy the code

Stored procedures

What is a stored procedure? A stored procedure is simply a collection of one or more MySQL statements saved for reusability or to achieve complex business functions. It can be regarded as a batch file.
Why use stored procedures? (1) Simplify complex operations by encapsulating processing in easy-to-use units; (2) Data integrity is ensured by not requiring repeated establishment of a series of processing steps, so that if all developers and applications use the same stored procedure, the code is the same; (3) Simplify the management of changes. If the table name, column name, or business logic changes, only the code of the stored procedure needs to be changed, and the developer using it does not even need to know about the changes. (4) Improved performance because using stored procedures is faster than using SQL statements alone; (5) Stored procedures can be used to write more flexible code. Therefore, stored procedures have three characteristics: simple reuse, security, and high performance;
Disadvantages of stored procedures? (1) Stored procedure writing is more complex than basic SQL statements and requires higher skills; (2) There may be no permission to create stored procedures, and the database administrator may restrict the permission to create stored procedures, allowing users to use stored procedures, but not allowing users to create stored procedures freely;

Creating a stored procedure

Create a stored procedure. If the total amount of a user’s order needs to be counted, if the user needs to pay taxes, the total amount of the order needs to be added with taxes

DELIMITER // CREATE PROCEDURE ordertotal( IN custid INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2))COMMENT 'obtain total order price' BEGIN /*declare variable for total*/ declare total A DECIMAL (8, 2); DECLARE taxrate INT DEFAULT 6; /*get the order total*/ SELECT SUM(item_price*item_quantity) INTO total FROM customers WHERE cust_id = custid; /*is this taxable? */ IF taxable THEN SELECT total+(total/100*taxrate) INTO total; END IF; SELECT total INTO ototal; END //Copy the code

Use the CREATE PROCEDURE statement to CREATE the PROCEDURE. () IN parentheses are the parameters of the stored PROCEDURE. The types of parameters are: 1. 2. OUT type, which represents the result returned by the stored procedure. When calling the stored procedure, we need to pass in the variable starting with @; 3. The INOUT type indicates that stored procedures can be passed in or out. DECLARE declares a variable, such as total, taxRate. Note that in MySQL, variables are defined first by the variable name and then by the data type. 3. The stored procedure logic is written between BEGIN and END. 4. Assign values to variables using the INTO keyword; 5. Because each SQL statement in the stored procedure uses; As a DELIMITER, it conflicts with a single SQL, so DELIMITER can be used to redefine the classifier. For example, DELIMITER is defined as a DELIMITER and natural stored procedures END with END // instead of END. Also, when the // delimiter is paired, it will revert to the default “;” As a delimiter;

Executing a stored procedure

The stored procedure is executed using the CALL clause, which takes the name of the stored procedure along with the parameters to be passed.
```
 CALL ordertotal(1,TRUE,@total);
 SELECT @total;
Copy the code
```

If the stored procedure defines input parameters of type OUT, then the stored procedure needs to be executed with variables, such as @total in this case, and the variables start with @. If there are no parameters in the stored procedure, use empty parentheses and CALL OrderTotal ();

Deleting a stored Procedure

To DROP a stored PROCEDURE, use the DROP PROCEDURE clause. Such asDROP PROCEDURE ordertotal;

Querying stored procedures

To display statements that CREATE a stored PROCEDURE, use SHOW CREATE PROCEDURE. Such asSHOW CREATE PROCEDURE ordertotal;
You can query the status of all stored procedures. If you use COMMENT to add comments when defining stored procedures, you can view the status. You can also filter the results with LIKE. Such asSHOW PROCEDURE STATUS LIKE '%order%';

6. Transaction processing

What is a transaction?

Transactions are used to maintain database integrity by ensuring that batches of MySQL operations are executed either completely or not at all. Transaction processing is a mechanism for managing MySQL operations that must be executed in batches, either as a whole or not at all.
Key concepts:
1. Transaction: a set of SQL statements;
2. Rollback: the process of undoing a specified SQL statement.
3. Commit: Write the result of an unstored SQL statement to a database table.
4. Reservation point: a temporary placeholder set in a transaction to which a rollback can be published;
How do I create an execution transaction?
```
 START TRANSACTION;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);
 SELECT * FROM customers;
 SAVEPOINT insertinto;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);
 ROLLBACK TO insertinto;
Copy the code
```
The result of execution is that the insert data (‘1’,5,18) is valid because the fallback will only start after the SAFEPOINT retention point, that is, the results of SQL statements executed before SAFEPOINT retention point are still valid.

Here are some details:
1. STAET TRANSACTION is used to indicate that the following SET of SQL statements is a TRANSACTION;
2. SAFEPOINT for designated reservation point insertinto;
3. ROLLBACK TO indicates the ROLLBACK from the specified reservation point. That is, the execution results of SQL statements before the reservation point are still valid. ROLLBACK means that all SQL statements executed since the STAET TRANSACTION will be undone.

MySQL commit (write or save) is automatic and is called an implicit commit. However, in a transaction block, the COMMIT is not implied and is committed using the COMMIT clause. Such as:

 START TRANSACTION;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);
 COMMIT;
Copy the code

COMMIT a transaction. Data is written to the table only when both SQL statements are executed successfully.

7. The trigger

What is a trigger?

When one SQL statement occurs, triggers are used to automatically execute other SQL statements. Triggers can only respond to DELETE, INSERT, or UPDATE.
Create trigger?

When creating a trigger, you need to give the following four important pieces of information: 1. 2. Table associated with trigger; 3. When the trigger is executed (before or after the action is executed)

Since triggers can only respond to certain types of operations, there are only three types of triggers that can be created :INSERT, DELETE, and UPDATE.

The INSERT trigger

INSERT trigger code can refer to a virtual table named NEW. NEW can be used to access the row that has just been inserted. 2. In the BEFORE INSERT trigger, the value in NEW can be updated; 3. For the AUTO_INCREMENT column, NEW contains 0 before INSERT execution and NEW custom generated values after INSERT execution.

Create an INSERT trigger that returns the ID of the currently inserted row each time a row is inserted.
```
/* CREATE TRIGGER insertcustomers AFTER INSERT ON customers FOR EACH ROW SELECT new.cust_id INTO @newinSerTID; /* CREATE TRIGGER insertcustomers AFTER INSERT ON customers FOR EACH ROW SELECT new.cust_ID INTO @newinSerTID; INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18); SELECT @newinsertid;Copy the code
```
Here are some details:
1. Use CREATE TRIGGER to CREATE a TRIGGER.
2. AFTER INSERT indicates that the trigger does not perform the feature operation until AFTER row data has been inserted.
3. FOR EACH ROW indicates that the trigger works FOR EACH ROW that is inserted.
4. For the INSERT trigger, you can use the virtual table NEW to use the row data that was just inserted. For example, in this example,SELECT NEW.cust_id INTO @newinsertidTo assign the id of the newly inserted row to the variable @newinSertid;
The DELETE trigger

A DELETE trigger needs to know two things before or after the execution of a DELETE statement:
1. In the DELETE trigger code, you can reference a virtual table named OLD to access the deleted row;
2. The OLD table can only be read and cannot be updated, whereas the INSERT trigger can update the inserted row with NEW.
For example, for the CUSTOMERS table, when deleting a row of data, return cust_ID and CUST_NAME of the deleted data:
```
/* CREATE DELETE TRIGGER */ DELIMITER // CREATE TRIGGER insertcustomers AFTER DELETE ON customers FOR EACH ROW BEGIN SELECT OLD.cust_name INTO @deletecustname; SELECT OLD.cust_id INTO @deletecustid; END // /* Call DELETE trigger */ DELETE FROM customers WHERE cust_id = 3; SELECT @deletecustname; SELECT @deletecustid;Copy the code
```
It is basically the same as creating an INSERT trigger, except you can only use OLD to access the deleted row data in a DELETE trigger.

The UPDATE trigger

The UPDATE trigger is executed before or after the UPDATE statement. There are a few things to know:
1. BEFORE UPDATE triggers can use NEW and OLD to access data. AFTER UPDATE triggers can only use OLD to access data.
2. In the BEFORE UPDATE trigger, the value in NEW can be changed, allowing the data to be changed for the UPDATE;
3. Rows in OLD can only be read, not updated;
An example of an UPDATE trigger is as follows:
```
*/ DELIMITER // CREATE TRIGGER insertcustomers BEFORE UPDATE ON customers FOR EACH ROW BEGIN SELECT NEW.cust_name INTO @beforeupdate; SET NEW.cust_name = 'reset_name'; SELECT OLD.cust_name INTO @afterupdate; END // /* Call UPDATE trigger */ UPDATE customers SET CUST_name = 'happy' WHERE CUST_id = 5; SELECT @beforeupdate; SELECT @afterupdate;Copy the code
```
The output is @beforeUpdate as’ happay ‘and @AfterUpdate as ‘reset_name’. Here are some details:
1. The data in the NEW virtual table can be changed, as used hereSET NEW.cust_name = 'reset_name';, will be updatedCust_name changed from "Happy" to "reset_NAME";
2. BEFORE UPDATE triggers can use NEW and OLD to access data. AFTER UPDATE triggers can use NEW to access data.
Delete trigger?

To DROP triggers, use the DROP TRIGGER statement, such as DROP TRIGGER insertCustomers; . Triggers cannot be updated or overwritten, and must be deleted if they are to be modified.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL command, an article for you to do all

1. Table (or database) operation statement

1.1. Query table (or database)

1.2. Create a table (or database)

1.3 Delete table (or database)

1.4 update the table

2 Table data operation statements

2.1 Querying Table Data

2.2 Inserting Table data

2.3 Updating table Data

2.4 Delete table data

3. Keyword execution order in SQL

Index of 4.

4.1 Creating an Index

4.2 Deleting an Index

4.3 Modifying an Index

4.4 Viewing indexes

Stored procedures

6. Transaction processing

7. The trigger

MySQL command, an article for you to do all

1. Table (or database) operation statement

1.1. Query table (or database)

1.2. Create a table (or database)

1.3 Delete table (or database)

1.4 update the table

2 Table data operation statements

2.1 Querying Table Data

2.2 Inserting Table data

2.3 Updating table Data

2.4 Delete table data

3. Keyword execution order in SQL

Index of 4.

4.1 Creating an Index

4.2 Deleting an Index

4.3 Modifying an Index

4.4 Viewing indexes

Stored procedures

6. Transaction processing

7. The trigger

Related Posts

I decompiled Java 10’s native variable type inference

Why does the nil error value in Go not equal nil? | Go theme month

Maven Settings. XML, rounding