MySQL Database Basics (vi)--SQL INSERT, UPDATE, delete operation

MySQL Database Basics (vi)--SQL INSERT, UPDATE, delete operation one, insert data 1, insert data for all fields of the table

Inserting data using a basic INSERT statement requires specifying the table name and the values inserted into the new record.

INSERT INTO table_name (column_list) VALUES (value_list);insert into `TSubject` (subjectID,subjectName,BookName,Publisher)values (‘0004‘,‘英语‘,‘大学英语走遍美国‘,‘清华出版社‘)可以不指定插入的列insert into `TSubject` values (‘0005‘,‘高等数学‘,‘高等数学教材‘,‘清华出版社‘)

2. Insert data for the specified field of the table

Inserts data for the specified field of the table, that is, inserting values into only a subset of the fields in the INSERT statement, while the values of the other fields are the default values when the table is defined.
The inserted column must be established

insert into TSubject (subjectID,subjectName) values (‘0006‘,‘高等数学2‘)insert into TSubject (subjectName,subjectID) values (‘数据结构‘,‘0007‘)

3. Inserting multiple records at the same time

The INSERT statement can insert multiple records into a data table at the same time, specify multiple value lists when inserting, and separate each value list with a comma, with the basic syntax in the following form:

INSERT INTO table_name (column_list) VALUES (value_list1),  (value_list2),..., (value_listn);

Example:

insert into TSubject (subjectName,subjectID) values (‘C#开发‘,‘0008‘),(‘苹果开发‘,‘0009‘)

4. Insert the query results into the table

A combined statement consisting of an INSERT statement and a SELECT statement can quickly insert multiple rows from one or more tables into a table.

INSERT INTO  table_name1  (column_list1)    SELECT (column_list2) FROM table_name2           WHERE (condition)

Example

insert into ST (subectid,subjectName) select  subjectid,subjectName from TSubject where `Publisher` is not null

II. Update Data 1, change records according to the conditions of this table

MySQL updates the records in the table using the UPDATE statement to update specific rows or to update all rows at the same time.

UPDATE table_name      SET column_name1 = value1,     column_name2=value2,……,     column_namen=valuen     WHERE (condition);

Example:

update `TStudent` set sname=concat(sname,‘net‘) where class=‘net‘update `TStudent` set sname=left(sname,3) where class=‘net‘

According to the student's birthday Mark 1988 freshman 1987 sophomore 1986 Big 31,985 year seniors

update TStudent set sname=CONCAT(sname,case year(birthday)%5   when 0 then ‘大四‘ when 1 then ‘大三‘ when 2 then ‘大二‘ else ‘大一‘ end)   where year(birthday)>=1985 and year(birthday)<=1988

2. Change the record according to the conditions of another table

UPDATE table_nameA a join table_nameB b on a. column_name1=b. column_name1     SET a.column_name1 = value1,    a.column_name2=value2,……,     a.column_namen=valuen    WHERE b. column_name2>20

Example: The name of a student who has failed will be marked with a *

update TStudent a join TScore b on a.`StudentID`=b.`StudentID`set a.`Sname`=concat(sname,‘*‘) where b.mark<60

Change the columns of two tables at the same time
Example: A student with a score of less than 60 points, plus 5 points, and add a tag to the student's name +

update TStudent a join TScore b on a.`StudentID`=b.`StudentID`set a.`Sname`=concat(sname,‘+‘), b.mark=b.`mark`+5 where b.mark<60;

Subqueries can also achieve the same functionality
The following statement adds a # sign to the student's name with a score greater than 98

update TStudent set Sname=concat(sname,‘#‘) where studentid in(select studentid from TScore where mark>98);

Third, delete data 1, according to the conditions of this table to delete records

Delete data from a data table using the DELETE statement, the DELETE statement allows the WHERE clause to specify the delete condition.
DELETE FROM table_name [WHERE condition>] ;
Remove students with a student number less than 00010
delete from TStudent where studentid<‘00010‘

2, according to the conditions of another table to delete records

DELETE a FROM table_a a join table_b b on a.column1=b.column1 [WHERE condition>] ;
Delete students with fractions less than 60 points

delete a from TStudent a join TScore b on a.`StudentID`=b.`StudentID`where b.mark<60

You can also use subqueries to implement.
Delete students with scores greater than 90 in the score table
delete from TStudent where studentid in (select studentid from TScore where mark>90);

MySQL Database Basics (vi)--SQL INSERT, UPDATE, delete operation