1. Insert Data
1.1 Mode 1: Add by VALUES
Case 1: Insert data in default order for all fields of a table
INSERT INTO Table Name VALUES (value1,value2,....);
Each field in the table needs to be assigned a value in the value list, and the order of values must be the same as the order in which the fields in the data table are defined.
Give an example:
INSERT INTO departments VALUES (70, 'Pub', 100, 1700); INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);
Scenario 2: Insert data for a specified field of a table
INSERT INTO Table Name(column1 [, column2, ..., columnn]) VALUES (value1 [,value2, ..., valuen]);
Inserting data for a specified field of a table means inserting values into only part of the field in an INSERT statement, while the values of other fields are the default values for the table definition. Randomly list column names in the INSERT clause, but once listed, the value1 to insert in VALUES,... valuen needs to correspond to column1,... columnn columns. If the types are different, you will not be able to insert, and MySQL will produce errors.
Give an example:
INSERT INTO departments(department_id, department_name) VALUES (80, 'IT');
Scenario 3: Insert multiple records at the same time
The INSERT statement can insert multiple records into the data table at the same time, specifying multiple value lists at the time of insertion. Each value list is separated by commas, and the basic syntax is as follows:
INSERT INTO table_name VALUES (value1 [,value2, ..., valuen]), (value1 [,value2, ..., valuen]), ...... (value1 [,value2, ..., valuen]);
INSERT INTO table_name(column1 [, column2, ..., columnn]) VALUES (value1 [,value2, ..., valuen]), (value1 [,value2, ..., valuen]), ...... (value1 [,value2, ..., valuen]);
Give an example:
mysql> INSERT INTO emp(emp_id,emp_name) -> VALUES (1001,'shkstart'), -> (1002,'atguigu'), -> (1003,'Tom'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
When INSERT is used to insert multiple records at the same time, MySQL returns additional information that is not available when performing a single-line insert. The meaning of this information is as follows: < Records: Indicates the number of records inserted.
Duplicates: Indicates records that were ignored during insertion, possibly because they contained duplicate primary key values. Warnings: Indicates problematic data values, such as data type conversion.
1.2 Mode 2: Insert query results into tables
INSERT can also insert the results of a SELECT statement query into a table without having to enter the values of each record one at a time. Instead, it can quickly insert multiple rows from one or more tables into a table using a combination of an INSERT statement and a SELECT statement. The basic grammar format is as follows:
INSERT INTO Target table name (tar_column1 [, tar_column2, ..., tar_columnn]) SELECT (src_column1 [, src_column2, ..., src_columnn]) FROM Source table name [WHERE condition]
Give an example:
INSERT INTO emp2 SELECT * FROM employees WHERE department_id = 90; INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';
2. Update Data
Use the UPDATE statement to update the data. The syntax is as follows:
UPDATE table_name SET column1=value1, column2=value2, ... , column=valuen [WHERE condition]
You can update more than one data at a time. If you need to roll back the data, you need to ensure that SET AUTOCOMMIT = FALSE; before DML.
UPDATE employees SET department_id = 70 WHERE employee_id = 113;
3. Delete Data
Delete data from a table using a DELETE statement:
DELETE FROM table_name [WHERE <condition>];
DELETE FROM departments WHERE department_name = 'Finance';
If the WHERE clause is omitted, all data in the table will be deleted.
4. New features of MySQL8: Computed Columns
What is a computed column? Simply put, the value of one column is calculated from another column. For example, if column a has a value of 1 and column B has a value of 2, column C does not need to be inserted manually. Defining a+b results in the value of c, then C is the calculated column, which is calculated from another column.
In MySQL 8.0, both CREATE TABLE and ALTER TABLE support adding computed columns.
Example: Define the data table tb1, then define the field id, field a, field b, and field c, where field C is the calculated column used to calculate the value of a+b. First create the test table tb1 with the following statements:
CREATE TABLE tb1( id INT, a INT, b INT, c INT GENERATED ALWAYS AS (a + b) VIRTUAL );
Insert the presentation data as follows:
INSERT INTO tb1(a,b) VALUES (100,200);
Query the data in data table tb1 with the following results:
mysql> SELECT * FROM tb1; +------+------+------+------+ | id | a | b | c | +------+------+------+------+ | NULL | 100 | 200 | 300 | +------+------+------+------+ 1 row in set (0.00 sec)