Database learning-Data Operations

Database learning-Data Operations 1. Insert Syntax: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT },...), (...),... [ONDUPLICATEKEYUPDATEcol_nameexpr,...] or: INS

Database learning-Data Operations 1. Insert Syntax: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT },...), (...),... [on duplicate key update col_name = expr,...] or: INS

Database learning-Data Operations

I. insert data

The Insert syntax is as follows:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT },...),(...),...

[On duplicate key update col_name = expr,...]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name = {expr | DEFAULT },...

[On duplicate key update col_name = expr,...]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT...

[On duplicate key update col_name = expr,...]

INSERT... VALUES and INSERT... SET statements INSERT rows according to the specified VALUES. INSERT statements in the form of INSERT... select insert the rows selected from other tables.

The following operations are performed based on the structure of the a1 table:

1. Insert a single row

# Method 1:

INSERT into a1 VALUES (1, 'jack', 'male', 0, 'py ');

This is a big danger because it is highly dependent on the column definition order in the table. That is to say, the above data must correspond to the field to be stored. Once the table structure changes, an error will be inserted.

# Method 2 security, but tedious

INSERT into a1 (id, name, sex, type, s1) VALUES (2, 'lama', 'femal', 1, 'json ');

The parentheses after the table name clearly indicate the column name, so that the number can be seated.

# Method 3: similar to method 2, set

INSERT a1 SET id = 3, name = 'Tom ', sex = 'male', type = 0, s1 = 'C #';

# INTO is optional in Insert syntax

According to the table structure, Insert can omit Column Operations. The omitted conditions are as follows:

(1) The column definition runs Null (no value or Null value)

(2) The column defines the default value.

The preceding code can be written as follows:

INSERT a1 (name, s1) VALUES ('gsxj ', 'python ');

INSERT a1 set name = 'hangsan ';

2. Insert multiple rows

# Method 1: (tedious) multiple entries are separated by semicolons

INSERT into a1 (name, sex, type, s1) VALUES ('lisi', 'male', 1, 'python ');

INSERT into a1 (name, sex, type, s1) VALUES ('hangzhou', 'male', 3, 'java ');

# Method 2: Separate multiple data entries with commas

INSERT into a1 (name, sex, type, s1) VALUES ('lili', 'female, 3, 'java'), ('zn', 'male', 2, 'C #');

3. Insert the Retrieved Data

Use insert select to perform operations. The syntax is as follows:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT...

[On duplicate key update col_name = expr,...]

Using INSERT... SELECT, you can quickly INSERT multiple rows into a table from one or more tables.

Instance:

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

Ii. Update Data

The Update syntax is as follows:

Single-table Syntax:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1 = expr1 [, col_name2 = expr2...]

[WHERE where_definition]

[Order by...]

[LIMIT row_count]

Multiple-table Syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references

SET col_name1 = expr1 [, col_name2 = expr2...]

[WHERE where_definition]

The UPDATE syntax uses the new value to UPDATE columns in the original table rows.

SET clause: indicates the columns to be modified and the values to be given.

WHERE clause: Specifies the rows to be updated. If there is no WHERE clause, all rows are updated.

Order by clause: Updates rows in the specified ORDER.

LIMIT clause: used to specify a LIMIT to LIMIT the number of rows that can be updated.

The UPDATE Statement supports the following modifiers:

If the LOW_PRIORITY keyword is used, the UPDATE execution is delayed until no other clients read from the table.

If you use the IGNORE keyword, the update statement will not be interrupted even if an error occurs during the update process. If duplicate keyword conflicts occur, these rows are not updated. If the column is updated, the new value will cause data conversion errors, and these rows will be updated to the closest legal value.

If you use tbl_name to access a column in an expression, UPDATE uses the current value in the column. For example, the following statement sets the age column to be greater than the current value:

Mysql> UPDATE person SET age = age + 1;

The UPDATE value is evaluated from left to right. For example, the following statement doubles the age column and then adds it:

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

If you set a column to its current value, MySQL will notice this, but will not update it.

If the column defined as not null is updated to NULL, the column is set to the default value corresponding to the column type, and the number of warnings is accumulated. For numeric type, the default value is 0. For string type, the default value is null string (''). For date and time types, the default value is" zero.

UPDATE returns the number of actually changed rows. The Mysql_info () c api function returns the number of matched and updated rows and the number of warnings generated during the UPDATE process.

You can use LIMIT row_count to LIMIT the UPDATE range. The LIMIT clause is a LIMIT for matching rows. As long as the row_count rows that can satisfy the WHERE clause are found, the statement is aborted, regardless of whether these rows are changed.

If an UPDATE statement contains an order by clause, the row is updated in the ORDER specified BY the clause.

You can also perform UPDATE operations on multiple tables. The table_references clause lists the tables contained in the Union. The following is an example:

UPDATE items, month SET items. price = month. price

WHERE items. id = month. id;

The preceding example shows the internal Union using the comma operator, but the multiple-table UPDATE statement can use any types of Union allowed in the SELECT statement, such as left join.

Note: you cannot use order by, LIMIT, or multiple-table UPDATE simultaneously.

In a modified multiple-table UPDATE, some columns are referenced. You only need the UPDATE permission for these columns. Some columns are read but not modified. You only need the SELECT permission for these columns.

1. Update a specific row in the table

The where clause is required to update a specific row. Otherwise, all rows are updated.

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 is an integer.

Using ignore will ignore some type errors and convert similar types as much as possible.

UPDATE person, a1 set person. type = a1.type WHERE person. id = a1.id;

2. Update all rows in the table

See

Iii. delete data

Delete Syntax:

Single Table Syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_definition]

[Order by...]

[LIMIT row_count]

Multi-Table Syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

Tbl_name [. *] [, tbl_name [. *]...]

FROM table_references

[WHERE where_definition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

FROM tbl_name [. *] [, tbl_name [. *]...]

USING table_references

[WHERE where_definition]

Delete from person WHERE id = 1;

Note:

(1): Delete from table_name deletes the content rather than the table.

(2): If the TABLE is cleared, the faster way is to use the truncate TABLE, which is much faster than the delete TABLE. The truncate is equivalent to deleting the entire TABLE, and then re-creating a TABLE instead of deleting data one by one.

Truncate TABLE person;