Data manipulation for MySQL

1. Inserting Data

inserting a single row of data ( inserts a single row of data if the found statement contains a set keyword)

Grammar

Insert into table_name set

< field 1>= value 1,

< field 2>= value 2,

< field 3>= value 3;

First look at the table structure

mysql> desc T1;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| ID | Int (11) |     YES | |       NULL | |

| name | varchar (20) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in Set (0.00 sec)

Inserting data

mysql> INSERT INTO T1 set

ID=10,

-name= ' xxx ';

Query OK, 1 row affected (0.09 sec)

View data

SELECT * from T1;

+------+-------+

| ID | name |

+------+-------+

| 1 | NULL |

| 1 | Xiaon |

| 2 | Kevin |

| 3 | Mark |

| 10 | xxx |

+------+-------+

5 rows in Set (0.00 sec)

Inserting multiple rows of data

Grammar

Insert into table_name

[( field 1, field 2, Field N)] square brackets indicate an optional

Values

( value 1, value 2, value N) , (Value 1N, value 2N, value 3N);

View table Structure

mysql> desc T1

;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| ID | Int (11) |     YES | |       NULL | |

| name | varchar (20) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in Set (0.00 sec)

Inserting data

mysql> INSERT INTO T1

(Id,name)

Values

(One, ' Fox '),

("Tom");

Query OK, 2 rows affected (0.01 sec)

Records:2 duplicates:0 warnings:0

View data

Mysql> select * from T1;

+------+-------+

| ID | name |

+------+-------+

| 1 | NULL |

| 1 | Xiaon |

| 2 | Kevin |

| 3 | Mark |

| 10 | xxx |

| 11 | Fox |

| 12 | Tom |

+------+-------+

7 Rows in Set (0.00 sec)

You can omit the contents of a column name if all the columns in a table are inserted into a value.

mysql> INSERT INTO T1 values ("Jack"), (+, ' Han ');

Query OK, 2 rows affected (0.01 sec)

Records:2 duplicates:0 warnings:0

View data

Mysql> select * from T1;

+------+-------+

| ID | name |

+------+-------+

| 1 | NULL |

| 1 | Xiaon |

| 2 | Kevin |

| 3 | Mark |

| 10 | xxx |

| 11 | Fox |

| 12 | Tom |

| 13 | Jack |

| 14 | Han |

+------+-------+

9 Rows in Set (0.00 sec)

Insert the specified column data

mysql> INSERT into T1 (ID) VALUES (15), (16);

Query OK, 2 rows affected (0.01 sec)

Records:2 duplicates:0 warnings:0

View data

Mysql> select * from T1;

+------+-------+

| ID | name |

+------+-------+

| 1 | NULL |

| 1 | Xiaon |

| 2 | Kevin |

| 3 | Mark |

| 10 | xxx |

| 11 | Fox |

| 12 | Tom |

| 13 | Jack |

| 14 | Han |

| 15 | NULL |

| 16 | NULL |

+------+-------+

Rows in Set (0.00 sec)

Insert query results into a table

Grammar

Insert into table_name1

( Field 1, field 2, field N)

Select field A, field B, field C from

Table_name2

[Where condition];

Create a new T3 Table

mysql> CREATE TABLE t3 (ID int (ten), name varchar (20));

Query OK, 0 rows affected (0.06 sec)

View table Structure

mysql> desc T3;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| ID | Int (10) |     YES | |       NULL | |

| name | varchar (20) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in Set (0.00 sec)

mysql> desc T1;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| ID | Int (11) |     YES | |       NULL | |

| name | varchar (20) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in Set (0.00 sec)

Inserting data

mysql> INSERT INTO T3 select Id,namefrom T1;

Query OK, rows affected (0.01 sec)

Records:11 duplicates:0 warnings:0

Compare data in two tables

Mysql> SELECT * from T3

;

+------+-------+

| ID | name |

+------+-------+

| 1 | NULL |

| 1 | Xiaon |

| 2 | Kevin |

| 3 | Mark |

| 10 | xxx |

| 11 | Fox |

| 12 | Tom |

| 13 | Jack |

| 14 | Han |

| 15 | NULL |

| 16 | NULL |

+------+-------+

Rows in Set (0.00 sec)

Mysql> select * from T1;

+------+-------+

| ID | name |

+------+-------+

| 1 | NULL |

| 1 | Xiaon |

| 2 | Kevin |

| 3 | Mark |

| 10 | xxx |

| 11 | Fox |

| 12 | Tom |

| 13 | Jack |

| 14 | Han |

| 15 | NULL |

| 16 | NULL |

+------+-------+

Rows in Set (0.00 sec)

2. Update Data

Grammar

Update table_name1 Set

field 1= value 1,

field 2= value 2,

Field n= value N

[Where condition];

Update data

mysql> Update T1 Set

-Name= ' lll '

, where

id=15;

Query OK, 1 row affected (0.09 sec)

Rows matched:1 changed:1 warnings:0

View data

Mysql> select * from T1;

+------+-------+

| ID | name |

+------+-------+

| 1 | NULL |

| 1 | Xiaon |

| 2 | Kevin |

| 3 | Mark |

| 10 | xxx |

| 11 | Fox |

| 12 | Tom |

| 13 | Jack |

| 14 | Han |

| 15 | lll |

| 16 | NULL |

+------+-------+

Rows in Set (0.00 sec)

If you do not write the where condition update data

mysql> Update T1 set name= ' lll ';

Query OK, Rows Affected (0.00 sec)

Rows matched:11 changed:10 warnings:0

View Data (you'll see that all the rows have changed, so you need to add a where statement)

Mysql> select * from T1;

+------+------+

| ID | name |

+------+------+

| 1 | lll |

| 1 | lll |

| 2 | lll |

| 3 | lll |

| 10 | lll |

| 11 | lll |

| 12 | lll |

| 13 | lll |

| 14 | lll |

| 15 | lll |

| 16 | lll |

+------+------+

Rows in Set (0.00 sec)

3. Delete Data

Grammar

Delete from table_name

[Where <condition>];

Delete data

mysql> Delete from T1 where id>13;

Query OK, 3 rows affected (0.01 sec)

View data

Mysql> select * from T1;

+------+------+

| ID | name |

+------+------+

| 1 | lll |

| 1 | lll |

| 2 | lll |

| 3 | lll |

| 10 | lll |

| 11 | lll |

| 12 | lll |

| 13 | lll |

+------+------+

8 rows in Set (0.00 sec)

Delete data without conditions

mysql> Delete from T1;

Query OK, 8 rows affected (0.01 sec)

View Data (all data is emptied without conditions)

Mysql> select * from T1;

Empty Set (0.00 sec)

4. Operation of Things

using tables in the INOODB data engine to support thing operations

By default MySQL turns on auto-commit

Begin to open a thing

ROLLBACK rolling back a thing

Commit commit a thing

Into things

Mysql> begin

;

Query OK, 0 rows Affected (0.00 sec)

Inserting data into a thing

mysql> INSERT INTO T1 values (1, ' liujing ');

Query OK, 1 row Affected (0.00 sec)

View data

Mysql> select * from T1;

+------+---------+

| ID | name |

+------+---------+

| 1 | liujing |

+------+---------+

1 row in Set (0.00 sec)

Open a new terminal

View T1 table Data (the data is empty, because this is another thing, things are isolated.) )

Mysql> SELECT * from t1

;

Empty Set (0.00 sec)

First Terminal

Things are done, commit changes, and this time the results will be written to the hard disk, can be read by other things.

Mysql> commit;

Query OK, 0 rows Affected (0.00 sec)

The newly opened terminal can be read to

Mysql> select * from T1;

+------+---------+

| ID | name |

+------+---------+

| 1 | liujing |

+------+---------+

1 row in Set (0.00 sec)

Rollback operation

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO T1 values (2, ' Jingcheng ');

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from T1;

+------+-----------+

| ID | name |

+------+-----------+

| 1 | liujing |

| 2 | Jingcheng |

+------+-----------+

2 rows in Set (0.00 sec)

Rolling back

mysql> rollback;

Query OK, 0 rows affected (0.01 sec)

Mysql> select * from T1;

+------+---------+

| ID | name |

+------+---------+

| 1 | liujing |

+------+---------+

1 row in Set (0.00 sec)

Commit Save

Mysql> commit;

Query OK, 0 rows Affected (0.00 sec)

View Configuration

Mysql> Show variables like '%commit ';

+--------------------------------+-------+

| variable_name | Value |

+--------------------------------+-------+

| autocommit | On |

| Innodb_flush_log_at_trx_commit | 1 |

+--------------------------------+-------+

2 rows in Set (0.01 sec)

Auto-Save is turned on by default.

Turn off auto-commit

Mysql> set autocommit=0;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like '%commit ';

+--------------------------------+-------+

| variable_name | Value |

+--------------------------------+-------+

| autocommit | OFF |

| Innodb_flush_log_at_trx_commit | 1 |

+--------------------------------+-------+

2 rows in Set (0.00 sec)

If you turn off autocommit, then whatever you enter, MySQL will assume that in one thing, when you enter a commit , it closes the thing, and it is equivalent to opening another thing at the same time.

When do I need to turn off auto-save?

When importing data in bulk, many single-line imports are recommended to turn off auto-save. Import and then turn on auto-save.

This can save MySQL resources, submit MySQL efficiency, otherwise a statement is equivalent to opening a thing.

This article from "Confidence snail" blog, declined reprint!

Data manipulation for MySQL