数据表变动

INSERT

通过INSERT语句插入单笔记录

                    INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
                
                    mysql> INSERT INTO student(age,classId,gender,name) VALUES (9,1,"F","xiaoli");
                    Query OK, 1 row affected (0.02 sec)

通过INSERT语句插入多笔记录

                    INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值11, 值12, ...),(值21,值22,....)... ;
                
                    mysql> INSERT INTO student(age,classId,gender,name) VALUES (9,1,"M","xiaoping"),(10,2,"F","xiaojia");
                    Query OK, 2 rows affected (0.02 sec)
                    Records: 2  Duplicates: 0  Warnings: 0

通过INSERT...SELECT语句，将表2中符合条件的字段值插入到表1中。

                    INSERT INTO <表名1>(字段1,字段2,...) SELECT 字段1,字段2,... FROM <表名2> WHERE <条件表达式> ,
                
                    mysql> INSERT INTO student(age,classId,gender,name) SELECT age,classId,gender,name FROM elderStudent WHERE age>10;
                    Query OK, 1 row affected (0.02 sec)
                    Records: 1  Duplicates: 0  Warnings: 0

UPDATE

使用UPDATE语句修改满足表达式条件的字段值。语法如下：

                    UPDATE <表名> SET 字段1 = 字段值1,字段2 = 字段值2 ... WHERE <条件表达式>
                
                    mysql> UPDATE student SET name = "Jr ping" WHERE id = 10;
                    Query OK, 1 row affected (0.02 sec)
                    Rows matched: 1  Changed: 1  Warnings: 0

多表关联修改。

                    UPDATE <表名1> INNER JOIN <表名2> ON  <条件表达式>  SET 字段 = 字段值...
                
                    mysql> UPDATE student t1 INNER JOIN elderStudent t2 ON t1.id = t2.id SET t1.name=t2.name;
                    Query OK, 1 row affected (0.01 sec)
                    Rows matched: 3  Changed: 1  Warnings: 0

DELETE

使用DELETE语句删除表中满足表达式条件的一条或多条记录

                    DELETE FROM <表名>  WHERE  <条件表达式>;
                
                    mysql> DELETE FROM student WHERE id = 1;
                    Query OK, 1 row affected (0.02 sec)