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

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