MySQL表中数据的操作（DML）

DML

主要是对表中的数据进行增、删、改的操作

所有的操作以下面emp为例

+------+-----------+--------+------------+------------+------+--------+---------+-------+
| id   | userName  | gender | birthday   | entry_date | job  | salary | resume  | image |
+------+-----------+--------+------------+------------+------+--------+---------+-------+
|    2 | toneTwo   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    3 | toneThree | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    4 | toneFour  | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    5 | toneFive  | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    1 | toneOne   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    6 | toneSix   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
+------+-----------+--------+------------+------------+------+--------+---------+-------+

一、查询表中所有的数据

SELECT * FROM emp

+------+-----------+--------+------------+------------+------+--------+---------+-------+
| id   | userName  | gender | birthday   | entry_date | job  | salary | resume  | image |
+------+-----------+--------+------------+------------+------+--------+---------+-------+
|    2 | toneTwo   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    3 | toneThree | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    4 | toneFour  | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    5 | toneFive  | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    1 | toneOne   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
|    6 | toneSix   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy | NULL  |
+------+-----------+--------+------------+------------+------+--------+---------+-------+

二、数据库中插入数据操作

INSERT INTO emp(id, userName, gender, birthday, entry_date, job, salary, resume, image) VALUES
(7, "wangwu", "man", "1992-01-29", "2019-03-29", "iOS", 50000, "good boy", "2345555");

+------+-----------+--------+------------+------------+------+--------+----------+---------+
| id   | userName  | gender | birthday   | entry_date | job  | salary | resume   | image   |
+------+-----------+--------+------------+------------+------+--------+----------+---------+
|    2 | toneTwo   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    3 | toneThree | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    4 | toneFour  | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    5 | toneFive  | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    1 | toneOne   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    6 | toneSix   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    7 | wangwu    | man    | 1992-01-29 | 2019-03-29 | iOS  |  50000 | good boy | 2345555 | 新插入的数据
+------+-----------+--------+------------+------------+------+--------+----------+---------+

三、更新表中所有工资的数据我

UPDATE emp SET salary=5000;

四、带条件更新表中的数据

UPDATE emp SET salary=2000 WHERE userName="wangwu";

修改 wangwu 的工资 2000
+------+-----------+--------+------------+------------+------+--------+----------+---------+
| id   | userName  | gender | birthday   | entry_date | job  | salary | resume   | image   |
+------+-----------+--------+------------+------------+------+--------+----------+---------+
|    2 | toneTwo   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    3 | toneThree | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    4 | toneFour  | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    5 | toneFive  | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    1 | toneOne   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    6 | toneSix   | enger  | 2015-09-30 | 2015-08-23 | NULL |   5000 | goodboy  | NULL    |
|    7 | wangwu    | man    | 1992-01-29 | 2019-03-29 | iOS  |   2000 | good boy | 2345555 |
+------+-----------+--------+------------+------------+------+--------+----------+---------+

五、删除表中某一条数据

DELETE FROM emp WHERE userName='wangwu';

六、删除表中所有的数据（直接先删除表，然后再创建一个新的表）

TRUNCATE TABLE emp;

DELETE 删除之后数据还能够找回，TRUNCATE： 删除数据无法找回