数据操作语言（DML）

Itbestboy 2021-06-28

257

1、DML(数据操纵语言)

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

2、插入数据

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

insert into 表名（[字段1],[字段2],[字段3]....） values（值1，值2，值3......）

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

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 多行同时插入

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

案例;在emp表中同时插入两条记录：


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 表名 set 字段名 = 需要更新的内容.............[where 条件]

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

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

mysql> UPDATE emp SET EMPNO = 8888;
1062 - Duplicate entry '8888' for key 'PRIMARY'  因为EMPNO为主键，主键唯一且不能为空，所以在不带where条件时会报错
 当带上where条件时，如果数据表中没有改名字，便会更新成功。
 之前的数据：
 +-------+--------+----------+------+------------+------+------+--------+------+
| 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

也可以对多个字段同时更新

案例：更新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 表名 where 条件。

当没加where条件时，将会将数据表中的全部数据删除。

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

案例:删除员工工资为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：删除comm为空的员工信息

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

5、复制表

当一个表查询出来的数据需要重新建立一张表示，就可以使用复制语句进行复制，其实质是创建表。

具体语法：

create table 表名 [as] select 需要查询的字段 from 表名 where 条件，其实as后面跟的语句必须是DQL语句

案例：把emp表中的empno,ename,sal,comm四个字段复制为一张名为emp_fuzhi 的表。

mysql> CREATE TABLE emp_fuzhi AS SELECT empno,ename,sal,comm FROM emp;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp_fuzhi;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int(4)      | NO   |     | NULL    |       |
| ename | varchar(10) | YES  |     | NULL    |       |
| sal   | double(7,2) | YES  |     | NULL    |       |
| comm  | double(7,2) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

数据库

文章转载自Itbestboy，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

数据操作语言（DML）

1、DML(数据操纵语言)

2、插入数据

3、 更新数据

4、 删除数据

5、 复制表

评论

3、更新数据

4、删除数据

5、复制表