(5): operations on data

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

First, insert the data

The Insert syntax is as follows:

    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT... VALUES and INSERT... SET statement according to the insert row value specified. INSERT... SELECT statement inserts selected from the other rows in the table.

The following operations according to the A1 table, is the structure of the A1 table

1, Insert row

#One way: 
INSERT into a1 VALUES(1,'Jack','Male',0,'py');

This is very dangerous, because it is highly dependent on the definition of the order of columns in the table, that is above the field corresponding to data must be stored, once the table structure change will insert error.

#Methods 2 safe mode, but it is more complicated
INSERT into a1(id,name,sex,type,s1) VALUES(2,'Lama','Female',1,'json');

The name of the table after the parentheses indicates the column name, so you can personally.

#Three: with two different approaches but equally satisfactory results in essence, this is the use of set
INSERT a1 SET id=3,name='Tom',sex='Male',type=0,s1='C#';
#INTO Optional in Insert syntax

According to the Insert table structure, some can omit the column operation, the following omit condition:

(1), Define the column operation Null (no value or null)

(2), This column defines the default value

The above can be written as follows:

INSERT a1 (name,s1) VALUES('Gsxj','Python');
INSERT a1 set name='Zhangsan';

2, Insert rows

#A: (complicated) multiple separated by semicolons
INSERT into a1 (name,sex,type,s1) VALUES('Lisi','Male',1,'Python');
INSERT into a1 (name,sex,type,s1) VALUES('Wangwu','Male',3,'Java');
#Methods: the data of two separated by commas
INSERT into a1 (name,sex,type,s1) VALUES('Lili','Female',3,'java'),('Zen','Male',2,'C#');

3, Insert the retrieved data

The use of insert select, the syntax is as follows:

    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

The use of INSERT...SELECT, You can quickly from one or more tables to a table insert multiple rows. 


INSERT into a1 (name,type)SELECT name,type FROM person;

Two, update the data

The Update syntax is as follows:

Single-Table grammar: 

    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-Table grammar: 

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

UPDATE grammar can be with new values for update the original table rows in each column.

The SET clause: instructions to modify which columns and give what values.

The WHERE clause to specify which rows should be updated. If no WHERE clause, then the update all rows.

The ORDER BY clause: in accordance with the designated order on line update.

The LIMIT clause: for a given limit, limit the number of rows can be updated.

The UPDATE statement supports the following modifiers:

If you use the LOW_PRIORITY keyword, the UPDATE implementation has been delayed, until there is no other client to read from the table.

If you use the IGNORE keyword, then error occurred even during the update, the update statement will not interrupt. If there is a duplicate keyword conflict, the row is not updated. If the column is updated, the new value will cause the data conversion errors, the row is updated with the value of the closest legitimate.

If the tbl_name access through a column in an expression, UPDATE uses the current values in the column. For example, the following statement in the age of column is set to more than the current value:

mysql> UPDATE person SET age=age+1;

UPDATE assignment is from left to right evaluation. For example, the following statement on the age column double, then increase:

mysql> UPDATE person SET age=age*2, age=age+1;

If a column is set to its current contains value, MySQL will pay attention to this point, but does not update.

If the NOT has already been defined for the NULL column is updated to NULL, the default value corresponding to the column is set to and the type of the column, and the cumulative number of warnings. For digital type, the default value is 0; for the string type, the default value is an empty string ("); the date and time type, the default value is “ zero&rdquo value;.

The number of UPDATE will return the actual change line. The number of C API Mysql_info () function returns the matching and the updated rows, and the number of generated in the process of UPDATE warning.

You can use the LIMIT row_count to limit the scope of UPDATE. The LIMIT clause is a matching limited. As long as that can satisfy the WHERE clause of the row_count statement, the suspension, regardless of these lines have been changed.

If a UPDATE statement includes a ORDER BY clause, according to the order specified by clause update rows.

You can also perform includeA plurality of table UPDATE. The table_references clause lists included in the joint in the table. The following is an example of:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The above examples show the internal joint use of the comma operator, but the multiple-table UPDATE statement in the SELECT statement can be used to allow any type of joint, such as the LEFT JOIN.

Note: you can't ORDER BY or LIMIT and multiple-table using UPDATE.

In a change in multiple-table UPDATE, some columns referenced. You need only the column UPDATE permissions. Some of the column to be read, but not modify. You need only the column SELECT permissions.

1, Update table in a specific row

Update the specific line requires where, otherwise it will update all rows.

update person set name = 'Kimi' WHERE id =2;
update person set type = 0 WHERE age>21;
update person set type = 0 WHERE age>21 LIMIT 1, 
update person set type = 4 WHERE age>21 ORDER BY name limit 1, 
#Use the ignore keyword
update ignore person set type = '3' WHERE id = 3; #Note that the type type as integer
The use of ignore will ignore some types of errors, as far as possible similar type conversion. 
UPDATE person,a1 set person.type=a1.type WHERE person.id=a1.id;

2, All rows are updated in the table


Three, delete data

Delete grammar:

Single scale grammar: 

    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
Multi table grammar: 

    tbl_name[.*] [, tbl_name[.*] ...]
    FROM table_references
    [WHERE where_definition]

    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]
DELETE FROM person WHERE id =1;

Be careful:

(1):Delete from table_name to delete the content is not table

(2):If the empty table, then faster way is to use truncate TABLE, faster than the delete, truncate is equivalent to delete the entire table, and then re create a table, rather than a delete data.

truncate TABLE person;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Buck at December 16, 2013 - 4:14 PM