Addition, deletion, modification and query of advanced MySQL data (DML)

My blog

increase

INSERT INTO stu(studentNo, loginPwd, studentName, sex, gradeId, phone, address, bornDate, mail, identityCard) VALUE ('123456','00000',' Jane ',' Secret ','1','1888888888',' Changsha ','2000-1-1','[email protected]','43032000000000');

INSERT INTO  Table name ( Field 1, Field 2) VALUE ( value 1, value 2);
--  If not numerical , Please use single quotation marks , Such as ：(' value 1',' value 2')

Insert multiple rows

INSERT INTO  Table name ( Field 1, Field 2) VALUE ( value 1, value 2),( value 1, value 2);
--  This allows you to insert multiple lines at once 

Insert the results of the query into a new table

Format 1

INSERT INTO  Table name ( Field 1, Field 2) SELECT  Field 1, Field 2 FROM  Original table name ;
--  There is no need to say more about the use methods and problems that will arise , Try it yourself 

Format 2

SELECT  Field 1, Field 2 INTO  The new name of the table  FROM  Original table name ;

Delete

-- DELETE FROM  Table name  WHERE [ Conditions ];
-- WHERE [ Conditions ] Don't write , Not writing is to unconditionally delete all , Conditional is to delete the qualified ！！！
DELETE FROM stu WHERE studentNo = '888888';

Delete all data in the table

-- TRUNCATE TABLE  Table name ;
TRUNCATE TABLE stu;

Be careful ： Use this statement to delete data in the table , Will reset auto increment , And the execution efficiency is better than DELETE.

Change

UPDATE statement we use UPDATE key word .

-- UPDATE  Table name  SET  Field name  = ' field value ', Field name  = ' field value ' WHERE [ Conditions ] 
-- WHERE [ Conditions ] Don't write , Not writing is unconditionally modifying all , Conditional is to modify what meets the conditions ！！！
UPDATE stu SET studentNo = '888888' WHERE studentNo = '123456';

check

SELECT * FROM stu WHERE studentNo = '888888';

This is a simple query statement . ditto ,WHERE It can be omitted , If not WHERE Conditions , Is to unconditionally query all .

We can also write more complex .

SELECT * FROM stu WHERE studentNo = '888888' GROUP BY studentNo ORDER BY studentNo ASC ;

We decompose , Take a look one by one .

notes ：[ ] Inner represents an unnecessary statement .

SELECT * or < List of field names > FROM < Table name or view > [WHERE < Query criteria >] [GROUP BY  Fields to be grouped ] [ORDER BY  Fields to sort  [ASC or DESC] ] ;

• * Express all .
• FROM After that, it is mainly connected with the data source , It can be single or multiple .
• WHERE For conditional screening .
• GROUP BY You can group the queried data according to the specified fields .
• HAVING Used to filter groups , Is for GROUP BY Separate groups for screening and so on .
• ORDER BY Used to sort , Sort by a column ,ASC It's in ascending order ,DESC It's in descending order