1、DML(数据操纵语言)

​ 在进行数据库操作时，首先就是要使用DML（数据操纵语言），数据操纵语言主要由插入数据（insert）、更新数据（update）以及删除数据（delete）组成，这些操作主要是针对

数据表中的数据。

2、插入数据（INSERT）

当需要向一张表中插入数据时，可以使用insert语句进行插入。具体语法：

语法一：一般用在不插入所有数据时
INSERT INTO table_name ([ field1], [field2],...[fieldN] )
                      VALUES
                      ( [value1], [value2],...[valueN] );
                      
语法二：一般用在插入所有数据时
INSERT INTO table_name 
                      VALUES
                      ( [value1], [value2],...[valueN] );
或者
INSERT INTO table_name ( field1, field2,...fieldN )
                      VALUES
                      ( value1, value2,...valueN );

通俗理解：
insert into 表名（[字段1],[字段2],[字段3]....） values（值1，值2，值3......）

2.1 插入全部数据

插入所有数据就是把张表中所包含的所有字段都赋值，插入所有字段的数据，注意值得顺序和字段的顺序一定要一致，同时也要注意下字段的约束条件，否则会报错。

------------->>>>案例: 向emp表中插入一条记录： EMPNO=7369, ENAME= ‘SMITH’, JOB=‘CLERK’, MGR=7092, HIREDATE=1980-12-17, SAL=800, COMM=Null, DEPTNO =20.AGE=Null

语法二：

mysql> INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20,NULL);
Query OK, 1 row affected

mysql> select * from emp;
+-------+-------+-------+------+------------+-----+------+--------+------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL | COMM | DEPTNO | age  |
+-------+-------+-------+------+------------+-----+------+--------+------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL |     20 | NULL |
+-------+-------+-------+------+------------+-----+------+--------+------+
1 row in set

语法一：
或者表后面带上字段名，一定要注意顺序一致
mysql> INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO,AGE) VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30,NULL);
Query OK, 1 row affected

mysql> select * from emp;
+-------+-------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+-------+----------+------+------------+------+------+--------+------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 | NULL |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
+-------+-------+----------+------+------------+------+------+--------+------+
2 rows in set


如果顺序不一致，则会报错

mysql> INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO,AGE ) VALUES ( 'WARD',7521,'SALESMAN',7698,'1981-02-22',1250,500,30,NULL); 
1366 - Incorrect integer value: 'WARD' for column 'EMPNO' at row 1

2.2 插入部分数据

在插入部分数据时要注意字段和插入值得对应关系，最重要的是注意下字段的约束条件。

---------->>>>案例：在emp表中插入一条记录：EMPNO=7989

mysql> INSERT INTO emp(EMPNO) VALUES (7989);
Query OK, 1 row affected

mysql> select * from emp;
+-------+-------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+-------+----------+------+------------+------+------+--------+------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 | NULL |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
|  7989 | NULL  | NULL     | NULL | NULL       | NULL | NULL | NULL   | NULL |
+-------+-------+----------+------+------------+------+------+--------+------+
3 rows in set

2.3 多行同时插入

在进行插入时可以多行同时插入，但是要注意字段和值得一致性以及约束条件。

mysql> INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO,AGE) VALUES 
									  ( 7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20,NULL),

									 ( 7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30,NULL); 
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from emp;
+-------+--------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+--------+----------+------+------------+------+------+--------+------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 | NULL |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 | NULL |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 | NULL |
|  7989 | NULL   | NULL     | NULL | NULL       | NULL | NULL | NULL   | NULL |
+-------+--------+----------+------+------------+------+------+--------+------+
5 rows in set

注

在插入数据时一定要注意字段的约束条件以及字段与值得对应关系。

3、 更新数据

当数据表的数据需要更新时，就可以使用update语句进行更新，具体语法：

UPDATE table_name
SET column1=newvalue1,column2=newvalue2,...
WHERE some_column=some_value;


通俗语法：
update 表名 set  字段名1 = 需要更新的内容1，字段名2 = 需要更新的内容2.............[where  条件]

3.1 不加where条件

当不加where是更新的是全表中改字段的记录，如果此时该字段有约束则可能会报错。

mysql> UPDATE emp SET EMPNO = 8888;
1062 - Duplicate entry '8888' for key 'PRIMARY'  因为EMPNO为主键，主键唯一且不能为空，所以在不带where条件时会报错

3.2 加where条件

在使用update语句时最好加上限制条件，否则可能会报错，使数据混乱。

 之前的数据：
 +-------+--------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+--------+----------+------+------------+------+------+--------+------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 | NULL |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 | NULL |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 | NULL |
|  7989 | NULL   | NULL     | NULL | NULL       | NULL | 2000 | NULL   | NULL |
+-------+--------+----------+------+------------+------+------+--------+------+
5 rows in set

更新操作
 mysql> UPDATE emp SET EMPNO = 8888 where COMM = 2000;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+-------+--------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+--------+----------+------+------------+------+------+--------+------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 | NULL |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 | NULL |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 | NULL |
|  8888 | NULL   | NULL     | NULL | NULL       | NULL | 2000 | NULL   | NULL |
+-------+--------+----------+------+------------+------+------+--------+------+
5 rows in set

3.3 对多个字段更新

-------------->>>>案例：更新emp表中那个comm=2000的员工的工资sal 和MGR

mysql> UPDATE emp SET MGR  =7902,SAL = 1000 where COMM = 2000;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+-------+--------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+--------+----------+------+------------+------+------+--------+------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 | NULL |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 | NULL |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 | NULL |
|  7989 | NULL   | NULL     | 7902 | NULL       | 1000 | 2000 | NULL   | NULL |
+-------+--------+----------+------+------------+------+------+--------+------+
5 rows in set

4、删除数据

当需要删除某条数据时就可以使用delete语句进行删除。具体语法

DELETE FROM table_name
WHERE  column=value;


当不加where条件时，会删除整张表中的数据，但是表的结构不会改变。

4.1 不加where条件

当没加where条件时，会将数据表中的数据全部删除，但是不会删除表的结构，此时表还存在，只不过是一张空表。

mysql> delete from emp;
Query OK, 4 rows affected
mysql> select * from emp;
Empty set

4.2 加where条件

------------->>>>案例1:删除工资为2000的员工。

mysql> delete from emp where comm=2000;
Query OK, 1 row affected

mysql> select *from emp;
+-------+--------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+--------+----------+------+------------+------+------+--------+------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 | NULL |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 | NULL |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 | NULL |
+-------+--------+----------+------+------------+------+------+--------+------+
4 rows in set

------------->>>>案例2：删除津贴为空的员工信息。

原始数据：
mysql> select *from emp;
+-------+--------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+--------+----------+------+------------+------+------+--------+------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 | NULL |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 | NULL |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 | NULL |
+-------+--------+----------+------+------------+------+------+--------+------+

执行语句
mysql> delete from emp where comm is null;
Query OK, 2 rows affected

执行后的数据
mysql> select * from emp;
+-------+--------+----------+------+------------+------+------+--------+------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | age  |
+-------+--------+----------+------+------------+------+------+--------+------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 | NULL |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 | NULL |
+-------+--------+----------+------+------------+------+------+--------+------+
2 rows in set