MySQL Data manipulation

One, increase

1 Insert full data (sequential insert)    syntax One:    insert into table name (Field 1, Field 2, Field 3 ...) field N) VALUES (value 1, value 2, value 3 ...) Value n);    Syntax two:    INSERT into table name values (value 1, value 2, value 3 ...) Value n); 2 . Specify field Insert data    syntax:    insert INTO table name (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...); 3 Insert multiple record    syntax: INSERT into    table name values        (value 1, value 2, value 3 ...) Value n),        (value 1, value 2, value 3 ...) Value n),        (value 1, value 2, value 3 ...) Value n);         4 Insert Query result    syntax:    insert INTO table name (Field 1, Field 2, Field 3 ...) Field N)                     SELECT (Field 1, Field 2, Field 3 ...) field N) from table 2                    WHERE ...;

Instance:

1Insert full data (sequential insertion)

mysql> insert INTO auth (id,name,age,address) VALUES (null, ' Fuyong ', 18, ' Henan '); Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO AUTH values (null, ' Xiaohua ', 16, ' Sichuan '); Query OK, 1 row affected (0.07 sec)

2. Specify fields to insert data

mysql> insert INTO auth (id,name) VALUES (null, ' xiaoming '); Query OK, 1 row affected (0.05 sec)

3. Inserting more than one record

mysql> INSERT INTO Auth value (null, ' LeBron ', 33, ' Cleveland '), (null, ' Kobe ', 42, ' Los Angeles '); Query OK, 2 rows affected (0.07 sec) records:2  duplicates:0  warnings:0

4. Inserting query Results

mysql> INSERT INTO auth2 select * from Auth; Query OK, 6 rows affected (0.06 sec) records:6  duplicates:0  warnings:0

　

Second, change

Syntax:    UPDATE Table name SET        Field 1= value 1,        field 2= value 2,        where CONDITION;

Examples are as follows:

mysql> Update auth Set name = ' Fu yong ' where id = 2; Query OK, 1 row affected (0.03 sec) Rows matched:1  changed:1  warnings:0

　　

Iii. deletion of the specified data by deleting 1

Syntax:    DELETE from table name         WHERE conition;

Mysql> Delete from auth where id = 1; Query OK, 1 row affected (0.04 sec)

2, delete all data (the original maximum primary key will be recorded, re-created when the primary key will add 1 on this basis), will write the log, the data can be restored, slow

delete from表名

Mysql> Delete from auth2; Query OK, 6 rows affected (0.09 sec)

3, empty the entire table (primary key information will also be emptied, re-created when the primary key will start from 1), do not write the log, the data is not recoverable, fast 　truncate table 表名;

mysql> truncate TABLE auth2; Query OK, 0 rows affected (0.28 sec)

　　

Iv. 1, single-table query

Http://www.cnblogs.com/fu-yong/p/8495469.html

2. Multi-Table Query

Http://www.cnblogs.com/fu-yong/p/8495473.html

MySQL Data manipulation