Shulou(Shulou.com)06/01 Report--

The following content mainly brings you MySQL basic handouts-SQL insert, update, delete operation process, the knowledge mentioned here, slightly different from books, are professional and technical personnel in contact with users, summed up, has a certain experience sharing value, hope to bring help to the majority of readers.

MySQL Database Basics (VI)--SQL Insert, Update, Delete Operations 1. Insert Data 1. Insert Data for All Fields of a Table

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

INSERT INTO table_name (column_list) VALUES (value_list);insert into `TSubject` (subjectID,subjectName,BookName,Publisher)values ('0004 ',' English','College English Travel America',' Tsinghua Publishing') optionally insert into `TSubject` values ('0005 ',' Advanced Mathematics','Advanced Mathematics Textbook',' Tsinghua Publishing') 2. Insert data for specified fields of the table

Inserting data for a specified field of a table is inserting values into only some fields in an INSERT statement, while the values of other fields are the default values for the table definition.

The inserted column must be specified

insert into TSubject (subjectID,subjectName) values ('0006 ',' Advanced Mathematics 2') insert into TSubject (subjectName,subjectID) values ('Data Structure','0007')

INSERT statement can insert multiple records into the data table at the same time, specify multiple value lists when inserting, each value list is separated by commas, the basic syntax is as follows:

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

Examples:

insert into TSubject (subjectName,subjectID) values ('C #Development','0008'),('Apple Development',' 0009') 4. Insert query results into the table

A combination of INSERT and SELECT statements allows you to quickly insert multiple rows from one or more tables into a single table.

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

example

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

MySQL uses UPDATE statements to update records in a table, either by updating specific rows or by updating all rows at once.

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

Examples:

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 after the student's name 1988 freshman 1987 sophomore 1986 junior 1985 senior

update TStudent set sname=CONCAT(sname,case year(birthday)%5 when 0 then 'senior' when 1 then 'junior' when 2 then 'sophomore' else 'freshman' end) where year(birthday)>=1985 and year(birthday)20

Example: Mark the name of the student who failed with an *

update TStudent a join TScore b on a.` StudentID`=b.` StudentID`set a.` Sname`=concat(sname,'*') where b.mark] ;

Delete students with student numbers less than 00010

delete from TStudent where studentid] ;

Delete students with scores less than 60

delete a from TStudent a join TScore b on a.` StudentID`=b.` StudentID`where b.mark90);

For the above on MySQL basic handouts-SQL insert, update, delete operation process, if you still need to know more can continue to pay attention to our industry push new, if you need to get professional answers, you can contact the official website before and after sales, I hope this article can bring you a certain knowledge update.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.