class=”markdown_views prism-github-gist”>

Addition, deletion, modification and query of MySQL tables

• CRUD is an acronym for the four words Create, Retrieve, Update, and Delete.
• The operation of the table is based on a set of records;
• Addition and deletion can only take one record as the basic unit.

1. Add (Create / Insert)

• Single quotes, double quotes ====” are used in strings, date and time in data records;
• Backquotes ====” used in the structure of the library name, table name, field name;
• You can omit backticks at all as long as you are careful to avoid using keywords as library names, surfaces, field names.

1.1 Insert – create table

--Build Table--"Student Table Just use the interface
 CREATE TABLE `db_11_20`.`students` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `sn` INT NOT NULL COMMENT 'student number',
   `name` VARCHAR(45) NOT NULL,
   `email` VARCHAR(200) NULL,
   PRIMARY KEY (`id`),
   UNIQUE INDEX `sn_UNIQUE` (`sn` ASC));
 

1.2 Single row data + full column insert

• Insert one row at a time, and the inserted data contains the data of all fields;

• Since all fields are inserted, the field part can be omitted in SQL;

• The order of the values ​​inserted later must be the same as the order in the table structure.

• -- id, sn, name, email
   INSERT INTO students VALUES (100, 10000, 'Tang Sanzang', NULL);
   -- Since id is the primary key and sn is the unique key, these two fields cannot be repeated
   INSERT INTO students VALUES (101, 10001, 'Monkey King', '11111');
   

1.3 Single row data + specified column insertion

• Insert one row at a time, specify the insertion of the column;

• id (self-increment, considered to have a default value); sn name (no default value); email (allowed to be NULL as a default value, which is NULL); so sn, name must have when inserting value;

• INSERT INTO students (sn, name) VALUES (20001, 'Barefoot Daxian');
   -- The order of the specified fields is not required to be related to the order of creating tables
   -- The order of the fields should be the same as the order of the incoming values
   INSERT INTO students ( name, sn) VALUES ('Niujiao Daxian',20003);
   

1.4 Multi-row data + full column insert

• INSERT INTO students VALUES
   (204, 30001, 'Jia Baoyu', '[email protected]')  ,	-- a record
   (205, 30002, 'Lin Daiyu', NULL), -- a record
   (206, 30003, 'Xue Baochai', NULL); -- a record
   

1.5 Multi-row data + specified column insertion

• INSERT INTO students (sn, name, email) VALUES
   (40001,'Song Jiang','[email protected]'),
           (40002,'Wang Ying','[email protected]');
   

2. Query (Retrieve)

• Retrieval function is the core function in DBMS; the principle behind DBMS query/retrieval is relational algebra;
• 

2.1 Query – Create a table

• -- create test results table
   DROP TABLE IF EXISTS exam_result;
   CREATE TABLE exam_result (
    id INT,
    name VARCHAR(20),
    chinese DECIMAL(3,1),
    math DECIMAL(3,1),
    english DECIMAL(3,1)
   );
   -- insert test data
   INSERT INTO exam_result (id, name, chinese, math, english) VALUES
    (1,'Tang Sanzang', 67, 98, 56),
    (2,'Monkey King', 87, 78, 77),
    (3,'Pig Wu Neng', 88, 98, 90),
    (4,'Cao Mengde', 82, 84, 67),
    (5,'Liu Xuande', 55, 85, 45),
    (6,'Sun Quan', 70, 73, 78),
    (7,'Song Gongming', 75, 65, 30);
   

2.2 Full column query

• Represents that the order of all fields is given in the order in which the table was built;

• No WHERE condition is given, which means that all data will be displayed;

• It is not recommended to use directly, there are disadvantages:

  • All fields will be displayed. In practice, only the required fields are often queried, and the performance is better;
  • There is no WHERE condition. When a table has tens of millions of records, the query will be very slow and may be stuck;

• SELECT * FROM exam_result;
   

2.3 Specified column query

• Specify column’s�Sort;

• order by can use the alias of the field;

Exercise:

• Query the user data in the user table that meets the following conditions:
  1. ID is between 1 to 200 or 300 to 500, and the account accout column is not empty
  2. 2. The recharge amount is above 1000.

SELECT * FROM user WHERE (id (BETWEEN 1 AND 200) OR (BETWEEN 300 AND 500)) AND account IS NOT NULL AND amount > 1000;
 

• Add a new record in the book table: Java core technology, author “Cay S. Horstman”, price 56.43, classified as “computer technology”;

INSERT INTO books VALUES ('Java Core Technology', 'Cay S. Horstman', 56.43, 'Computer Technology');
 

3. Update

• Specify the where condition (if you don’t write where, it is considered that all records meet the condition), you must be able to select a batch of records (maybe only 1, or 1 none);

• You can only modify the values ​​of the specified fields in this batch of records at one time;

• UPDATE exam_result SET chinese = 60 WHERE id BETWEEN 1 AND 3;
  
   UPDATE exam_result SET chinese = 30 WHERE id = 1;
   UPDATE exam_result SET chinese = 60 WHERE id = 2;
   UPDATE exam_result SET chinese = 90 WHERE id = 3;
  
   -- All records in the table meet the conditions, and Chinese becomes 40
   UPDATE exam_result SET chinese = 40;
  
   -- When modification is allowed, modify multiple fields at a time
   -- Fields are used directly, separated
   UPDATE exam_result SET chinese = 10, math = 20, english = 30 WHERE id BETWEEN 1 AND 3;
  
   -- When modifying, modify based on the original value of the field
   UPDATE exam_result SET math = math/2 WHERE not (id BETWEEN 1 AND 3);
   -- Note, math -= 3, math += 10 are not supported, only math = math - 3, math = math + 10
   

• Error: Error Code: 1264.Out of range value for column ‘math’ at row 40.000sec;

• Indicates that when inserting or modifying, the value of a field exceeds the data range specified by the field data type (out of range);

4. Delete

• First select a batch of records through the where condition, and delete all records that meet the conditions;

• Without the where condition, it means that all records meet the condition;

• -- Goal: Only delete Monkey King
   -- There is nothing between delete and from
   DELETE FROM exam_result WHERE id = 2;
   DELETE FROM exam_result WHERE name = 'Monkey King';
   DELETE FROM exam_result WHERE name LIKE 'Sun__';-- correct premise, knowing that there is only Sun Wukong in the table
   DELETE FROM exam_result WHERE name LIKE 'Grandson%'; -- error (did not meet expectations, not a database error) will also delete Sun Quan in the table
   -- Without the where condition, it means that all records meet the condition, so all the data in the whole table will be deleted (use with caution)
   DELETE FROM exam_result;
   

• Suggestions

  • 1. Before deleting, use select + the same where condition to confirm whether the data meets expectations;

  • 2. The primary key can be used to delete, and the primary key is preferred; since the primary key will not be repeated, it is the most accurate to use the primary key;

  • 3. From the perspective of data security, core data must be backed up;

  • INSERT INTO db_11_23.exam_result SELECT * FROM db_11_20.exam_result;
     

• Truncate the table: DDL operation, for the operation of the table structure, it is regarded as resetting the table structure (reset);

• -- Mainly reflect the auto-increment id, focus on the generated id
   truncate exam_result;
   insert into exam_result (name, math, chinese, english) values ​​('A', 1, 1, 1);-- auto-increment id becomes 1
  
   --Compared with full table data deletion: DML operation, for the data operation in the table, the table structure does not change
   delete from exam_result;
   insert into exam_result (name, math, chinese, english) values ​​('A', 1, 1, 1); -- auto-increment id becomes 8
   

• Truncate, like delete, is also used with caution;

• If you have clearly cleared the table, using truncate will be faster than delete from; truncate has a time complexity of O(1): only need to move the table structure, no matter how many pieces of data; delete from The time complexity is O(n): traverse and delete records one by one;

• Truncate of the graphical interface:

•