1. Insert data

1.1 practical problems

Solution: use the INSERT statement to INSERT data into the table.

1.2 mode 1: add VALUES

Using this syntax, only one piece of data can be inserted into the table at a time.

Case 1: insert data in the default order for all fields of the table

INSERT INTO Table name
VALUES (value1,value2,....);

In the value list, you need to specify values for each field in the table, and the order of values must be the same as that when defining fields in the data table.

give an example:

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);

Case 2: insert data for the specified field of the table

INSERT INTO Table name(column1 [, column2, ..., columnn])
VALUES (value1 [,value2, ..., valuen]);

Inserting data into the specified field of the table means inserting values into only some fields in the INSERT statement, while the values of other fields are the default values when the table is defined.

The column names are randomly listed in the INSERT clause, but once listed, the value1,... valuen to be inserted in VALUES needs to correspond to the column1,... columnn columns one by one. If the type is different, it cannot be inserted, and MySQL will generate an error.

give an example:

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

Case 3: insert multiple records at the same time

INSERT statement can INSERT multiple records into the data table at the same time. When inserting, specify multiple value lists. Each value list is separated by commas. The basic syntax format is as follows:

INSERT INTO table_name
VALUES
(value1 [,value2, ..., valuen]),
(value1 [,value2, ..., valuen]),
......
(value1 [,value2, ..., valuen]);

perhaps

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 using INSERT to INSERT multiple records at the same time, MySQL will return some additional information that is not available during single line insertion. The meaning of these information is as follows:
● Records: indicates the number of Records inserted.
● Duplicates: indicates the records that are ignored during insertion. The reason may be that these records contain duplicate primary key values.
● Warnings: data values indicating problems, such as data type conversion.

An INSERT statement that inserts multiple rows of records at the same time is equivalent to the INSERT statement that inserts multiple single rows, but the INSERT statement with multiple rows is more efficient in the process. Because MySQL executes a single INSERT statement to INSERT multiple rows of data faster than using multiple INSERT statements, it is best to INSERT multiple records by using a single INSERT statement.

Summary:

• VALUES can also be written as VALUE, but VALUES is standard.
• Character and date data should be enclosed in single quotes.

1.3 method 2: insert query results into the table

INSERT can also INSERT the query results of the SELECT statement into the table. At this time, it is not necessary to input the values of each record one by one. Just use the combined statement composed of an INSERT statement and a SELECT statement to quickly INSERT multiple rows from one or more tables into one table.

The basic syntax 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]

• Add a subquery to the INSERT statement.
• You do not have to write the VALUES clause.
• The list of values in the subquery should correspond to the column names in the INSERT clause.

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]

• Multiple pieces of data can be updated at one time.
• If you need to roll back data, you need to ensure that it is set before DML: SET AUTOCOMMIT = FALSE;
• Use the WHERE clause to specify the data to be updated.

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

• If you omit the WHERE clause, all data in the table will be updated.

 UPDATE copy_emp
 SET department_id = 110;

• Data integrity error in update

UPDATE employees
SET department_id = 55
WHERE department_id = 110;

Note: Department 55 does not exist

3. Delete data

• Use the DELETE statement to DELETE data from the table
  

DELETE FROM table_name [WHERE <condition>];

table_name specifies the table to DELETE; "[WHERE]" is an optional parameter that specifies the deletion condition. If there is no WHERE clause, the DELETE statement will DELETE all records in the table.

• Use the WHERE clause to delete the specified record.

DELETE FROM departments
WHERE department_name = 'Finance';

• If the WHERE clause is omitted, all data in the table will be deleted

DELETE FROM copy_emp;

• Data integrity error in delete

DELETE FROM departments
WHERE department_id = 60;

Note: You cannot delete a row that contains a primary key that is used as a foreign key in another table

4. New feature of mysql8: calculation column

What is a calculated column? Simply put, the value of a column is calculated from other columns. For example, if the value of column A is 1, the value of column b is 2, and column c does not need to be manually inserted, and the result of defining a+b is the value of c, then c is the calculation column, which is calculated from other columns.

In MySQL 8.0, adding calculation columns is supported in both CREATE TABLE and ALTER TABLE. Let's take CREATE TABLE as an example.

For example: define data table tb1, and then define field id, field a, field b and field c, where field c is the calculation column, which is used to calculate the value of a+b. First, create the test table tb1 with the following statement:

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

Insert presentation data with the following statement:

INSERT INTO tb1(a,b) VALUES (100,200);

Query the data in data table tb1, and the results are as follows:

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL | 100  | 200  | 300  |
+------+------+------+------+
1 row in set (0.00 sec)

Update the data in the data with the following statement:

mysql> UPDATE tb1 SET a = 500;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

5. Comprehensive cases

# 01. Create database test01_library

# Create table 02 and books as follows:

Field name	Field description	data type
id	Book number	INT
name	title	VARCHAR(50)
authors	author	VARCHAR(100)
price	Price	FLOAT
pubdate	Publication date	YEAR
note	explain	VARCHAR(100)
num	stock	INT

# 03. Insert records into the books table

   # 1) Insert the first record without specifying the field name
   # 2) Specify all field names and insert the second record
   # 3) Insert multiple records at the same time (all remaining records)

# 04. Increase the price of novel books by 5.

# 05. Change the price of the book named EmmaT to 40 and the description to drama.

# 06. Delete the record with inventory 0.

# 07. Books with a letter in the title of statistical books

# 08. Count the number and total inventory of books with a letter in the book title

# 09. Find out the "novel" books and arrange them in descending order of price

# 10. Query the book information and arrange it in descending order according to the inventory. If the inventory is the same, arrange it in ascending order according to the note

# 11. Count the number of books by note

# 12. According to the inventory quantity of note classification statistics book, it shows that the inventory quantity exceeds 30

# 13. Query all books, display 5 books per page, and display the second page

# 14. According to the inventory quantity of the note classification statistics book, the inventory with the largest quantity is displayed

# 15. Query the book with a title of 10 characters, excluding the spaces inside

# 16. Query the title and type of the book, where the note value is novel to display the novel, law to display the law, medicine to display the medicine, cartoon to display the cartoon, and joke to display the joke

# 17. Query the book title and inventory. If the num value exceeds 30, it will show unsalable; if it is greater than 0 and less than 10, it will show best-selling; if it is 0, it will show that it needs to be out of stock

# 18. Count the inventory of each note and sum up the total

# 19. Count the number of each note and sum up the total

# 20. Statistics of the top three books in stock

# 21. Find out the earliest published book

# 22. Find the book with the highest price in novel

# 23. Find the book with the largest number of words in the title, without spaces

answer:

#01. Create database test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';

#Specify which database to use
USE test01_library;

#02. Create table books
CREATE TABLE books(
      id INT,
      name VARCHAR(50),
      `authors` VARCHAR(100) ,
      price FLOAT,
      pubdate YEAR ,
      note VARCHAR(100),
      num INT
);

#03. Insert records into the books table
    # 1) Insert the first record without specifying the field name
       INSERT INTO books
       VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);
    # 2) Specify all field names and insert the second record
      INSERT INTO books (id,name,`authors`,price,pubdate,note,num)
      VALUES(2,'EmmaT','Jane lura',35,1993,'Joke',22);
    # 3) Insert multiple records at the same time (all remaining records)
      INSERT INTO books (id,name,`authors`,price,pubdate,note,num) VALUES
     (3,'Story of Jane','Jane Tim',40,2001,'novel',0),
     (4,'Lovey Day','George Byron',20,2005,'novel',30),
     (5,'Old land','Honore Blade',30,2010,'Law',0),
     (6,'The Battle','Upton Sara',30,1999,'medicine',40),
     (7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);
     
# 04. Increase the price of novel books by 5.
UPDATE books SET price=price+5 WHERE note = 'novel';

# 05. Change the price of the book named EmmaT to 40 and the description to drama.
UPDATE books SET price=40,note='drama' WHERE name='EmmaT';

# 06. Delete the record with inventory 0.
DELETE FROM books WHERE num=0;

# 07. Books with a letter in the title of statistical books
SELECT * FROM books WHERE name LIKE '%a%';

# 08. Count the number and total inventory of books with a letter in the book title
SELECT COUNT(*),SUM(num) FROM books WHERE name LIKE '%a%';

# 09. Find out the "novel" books and arrange them in descending order of price
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;

# 10. Query the book information and arrange it in descending order according to the inventory. If the inventory is the same, arrange it in ascending order according to the note
SELECT * FROM books ORDER BY num DESC,note ASC;

# 11. Count the number of books by note
SELECT note,COUNT(*) FROM books GROUP BY note;

# 12. According to the inventory quantity of note classification statistics book, it shows that the inventory quantity exceeds 30
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;

# 13. Query all books, display 5 books per page, and display the second page
SELECT * FROM books LIMIT 5,5;

# 14. According to the inventory quantity of the note classification statistics book, the inventory with the largest quantity is displayed
SELECT note,SUM(num) sum_num FROM books GROUP BY note ORDER BY sum_num DESC LIMIT 0,1;

# 15. Query the book with a title of 10 characters, excluding the spaces inside
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name,' ',''))>=10;


# 16. Query the title and type of the book,
/*Where note value is novel, law shows law, medicine shows medicine, cartoon shows cartoon and joke shows joke*/

SELECT name AS "title" ,note, CASE note
   WHEN 'novel' THEN 'novel'
   WHEN 'law' THEN 'law'
   WHEN 'medicine' THEN 'medicine'
   WHEN 'cartoon' THEN 'Cartoon'
   WHEN 'joke' THEN 'joke'
   END AS "type"
FROM books;

# 17. Query the book title and inventory. If the num value exceeds 30, it will show unsalable; if it is greater than 0 and less than 10, it will show best-selling; if it is 0, it will show that it needs to be out of stock
SELECT name,num,CASE
   WHEN num>30 THEN 'Unsalable'
   WHEN num>0 AND num<10 THEN 'Best selling'
   WHEN num=0 THEN 'No goods'
   ELSE 'normal'
   END AS "Inventory status"
FROM books;

# 18. Count the inventory of each note and sum up the total
SELECT IFNULL(note,'Total inventory') AS note,SUM(num) FROM books GROUP BY note WITH ROLLUP;

# 19. Count the number of each note and sum up the total
SELECT IFNULL(note,'Total') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP;

# 20. Statistics of the top three books in stock
SELECT * FROM books ORDER BY num DESC LIMIT 0,3;

# 21. Find out the earliest published book
SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;

# 22. Find the book with the highest price in novel
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 0,1;

# 23. Find the book with the largest number of words in the title, without spaces
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1;