Day461. Addition, deletion and modification of data processing - mysql

Addition, deletion and modification of data processing

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]);

To INSERT data for a specified field of a table is to INSERT 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 listed randomly 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 ignored during insertion, possibly because 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. You can quickly INSERT multiple rows from one or more tables into one table by using the combined statement composed of an INSERT statement and a SELECT statement.

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 a 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 that needs 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 a 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 MySQL 8: 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 calculated 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 as follows:

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

5, Comprehensive case

# 1. Create database test01_library

# 2. Create the table books. The table structure is 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

# 3. Insert a record 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)

id	name	authors	price	pubdate	note	num
1	Tal of AAA	Dickes	23	1995	novel	11
2	EmmaT	Jane lura	35	1993	joke	22
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

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

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

# 6. Delete record with inventory 0.

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

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

# 9. Find 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 of the note classification statistics book, it displays the inventory of more than 30 books

# 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, law shows law, medicine shows medicine, cartoon shows cartoon and Joe shows 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 total 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 out the most expensive book in novel

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

answer:

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

#Specify which database to use
USE test01_library;

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

#3. Insert a record 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);

# 4. Increase the price of novel books by 5.
UPDATE books SET price=price+5 WHERE note = 'novel';

# 5. 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';

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

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

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

# 9. Find 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 of the note classification statistics book, it displays the inventory of more than 30 books
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 seller'
  WHEN num=0 THEN 'No goods'
  ELSE 'normal'
  END AS "Inventory status"
FROM books;

# 18. Count the inventory of each note and total 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 out the most expensive book 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;

Posted by phpdev12 on Fri, 26 Nov 2021 18:12:50 -0800

Programmer Group

Day461. Addition, deletion and modification of data processing - mysql

Addition, deletion and modification of data processing

1, Insert data

1.1 practical problems

1.2 mode 1: add VALUES

1.3 method 2: insert query results into the table

2, Update data

3, Delete data

4, New feature of MySQL 8: calculation column

5, Comprehensive case

Hot Keywords