mysql增删改查4组语句_MySQL增删改查之DML语句详解

MySQL增删改查之DML语句详解

DML语句

主要指数据库对表记录的操作，包括表记录的插入(insert)、更新(update)、删除(delete)和查询(select)等

以下我们的操作都基于下表进行：

mysql> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field    | Type          | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename    | varchar(20)   | YES  |     | NULL    |       |

| hiredate | date          | YES  |     | NULL    |       |

| sal      | decimal(10,2) | YES  |     | NULL    |       |

| deptno   | int(2)        | YES  |     | NULL    |       |

+----------+---------------+------+-----+---------+-------+

4 rows in set (0.02 sec)

一、 插入记录

插入记录的sql语句语法：INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn);

1. 指定列名：

mysql> insert into emp (ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);

Query OK, 1 row affected (0.00 sec)

2. 不指定列名，但需要和数据表字段的顺序一致：

mysql>  insert into emp values('lisa','2003-02-01','3000',2);

Query OK, 1 row affected (0.00 sec)

我们可以用以下方法来查看表记录

mysql> select * from emp;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 2000.00 |      1 |

| lisa  | 2003-02-01 | 3000.00 |      2 |

+-------+------------+---------+--------+

2 rows in set (0.00 sec)

3. 对于含可空字段、非空但是含有默认值的字段、自增字段，可以不用在 insert 后的字段列表里面出现，values 后面只写对应字段名称的 value，这些没写的字段可以自动设置为 NULL、默认值、自增的下一个数字，这样在某些情况下可以大大缩短 SQL 语句的复杂性。

例如，针对该表，4列默认都为null, 我们尝试只对表中的 ename 和 sal 字段显式插入值：

mysql> insert into emp (ename,sal) values('dony',1000);

Query OK, 1 row affected (0.00 sec)

再输出已插入记录：

mysql> select * from emp;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 2000.00 |      1 |

| lisa  | 2003-02-01 | 3000.00 |      2 |

| dony  | NULL       | 1000.00 |   NULL |

+-------+------------+---------+--------+

3 rows in set (0.00 sec)

我们会看到未插入值的两列插入了默认值

如果我们想同时插入多列，来节省数据库访问次数，提高插入效率，可用以下语法：INSERT INTO tablename (field1, field2,……fieldn)

VALUES

(record1_value1, record1_value2,……record1_valuesn),

(record2_value1, record2_value2,……record2_valuesn),

……

(recordn_value1, recordn_value2,……recordn_valuesn)

;

我们尝试插入3列 ,具体如下所示：

mysql> insert into emp (ename,deptno) values ('a',10),('b',11),('c',12);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 2000.00 |      1 |

| lisa  | 2003-02-01 | 3000.00 |      2 |

| dony  | NULL       | 1000.00 |   NULL |

| a     | NULL       |    NULL |     10 |

| b     | NULL       |    NULL |     11 |

| c     | NULL       |    NULL |     12 |

+-------+------------+---------+--------+

6 rows in set (0.00 sec)

二、 更新记录

更新记录的sql语句语法：UPDATE tablename SET field1=value1，field2.=value2，……fieldn=valuen [WHERE CONDITION]

如果不指定条件，会对表中所有记录进行更新：

mysql>update emp set sal = 1000;

Query OK, 5 rows affected (0.00 sec)

Rows matched: 6  Changed: 5  Warnings: 0

mysql> select * from emp;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 1000.00 |      1 |

| lisa  | 2003-02-01 | 1000.00 |      2 |

| dony  | NULL       | 1000.00 |   NULL |

| a     | NULL       | 1000.00 |     10 |

| b     | NULL       | 1000.00 |     11 |

| c     | NULL       | 1000.00 |     12 |

+-------+------------+---------+--------+

6 rows in set (0.00 sec)

如果只想更新特定列，需要通过where语句指定:

mysql>update emp set sal = 2000 where deptno >= 10;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from emp;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 1000.00 |      1 |

| lisa  | 2003-02-01 | 1000.00 |      2 |

| dony  | NULL       | 1000.00 |   NULL |

| a     | NULL       | 2000.00 |     10 |

| b     | NULL       | 2000.00 |     11 |

| c     | NULL       | 2000.00 |     12 |

+-------+------------+---------+--------+

6 rows in set (0.00 sec)

如果想更新多个属性列，用逗号隔开，更新属性列时，还可以使用当前属性列中的内容：

mysql>update emp set sal = 2000,ename=deptno where deptno >= 10;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from emp;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 1000.00 |      1 |

| lisa  | 2003-02-01 | 1000.00 |      2 |

| dony  | NULL       | 1000.00 |   NULL |

| 10    | NULL       | 2000.00 |     10 |

| 11    | NULL       | 2000.00 |     11 |

| 12    | NULL       | 2000.00 |     12 |

+-------+------------+---------+--------+

6 rows in set (0.00 sec)

三、 删除记录

语法为：DELETE FROM tablename [WHERE CONDITION]

尝试把hiredate为null的不合法语句进行删除，注意属性为null的where条件检索写法：

mysql> delete from emp where hiredate is null;

Query OK, 4 rows affected (0.00 sec)

mysql> select * from emp;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 1000.00 |      1 |

| lisa  | 2003-02-01 | 1000.00 |      2 |

+-------+------------+---------+--------+

2 rows in set (0.00 sec)

四、查询记录

select语句的语法非常复杂，我们稍微把一些常用的罗列出来，不包括连接查询，如下所示：

SELECT

[ DISTINCT]

column1,column2/*

[FROM table_references

[WHERE where_definition]

[GROUP BY {col_name | expr | position}

[ASC | DESC]

[HAVING where_definition]

[ORDER BY {col_name | expr | position}

[ASC | DESC]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

最常用的，我们通过column,即列名来查询出我们需要的数据，如果我们想查询所有列，可以用“*”来代替所有列名，而where则给出了我们的查询条件，mysql会根据我们的查询条件查询出特定列数据。

下面我们来看看其它常用选项的用法

1. distinct是指选择出不重复的，我们可以通过以下来观察：

mysql> select sal from emp;

+---------+

| sal     |

+---------+

| 1000.00 |

| 1000.00 |

+---------+

2 rows in set (0.00 sec)

mysql> select distinct sal from emp;

+---------+

| sal     |

+---------+

| 1000.00 |

+---------+

1 row in set (0.00 sec)

mysql> select distinct sal,ename from emp;

+---------+-------+

| sal     | ename |

+---------+-------+

| 1000.00 | zzx1  |

| 1000.00 | lisa  |

+---------+-------+

2 rows in set (0.00 sec)

可以看出distinct是选出所有列都不重复的数据

2. group by :按特定条件分类，我们看下面例子：

mysql> select * from emp;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 1000.00 |      1 |

| lisa  | 2003-02-01 | 1000.00 |      2 |

| a     | NULL       | 2000.00 |   NULL |

| b     | NULL       | 2000.00 |   NULL |

+-------+------------+---------+--------+

4 rows in set (0.00 sec)

mysql> select count(*),sal from emp group by sal;

+----------+---------+

| count(*) | sal     |

+----------+---------+

|        2 | 1000.00 |

|        2 | 2000.00 |

+----------+---------+

2 rows in set (0.00 sec)

可见我们根据sal将记录分成了两组，并查询出每组的人数

3. order by 根据特定列进行排序

我们根据sal来排序(默认升序asc)，如果想用降序，则使用desc

mysql> select * from emp order by sal;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| zzx1  | 2000-01-01 | 1000.00 |      1 |

| lisa  | 2003-02-01 | 1000.00 |      2 |

| a     | NULL       | 2000.00 |   NULL |

| b     | NULL       | 2000.00 |   NULL |

+-------+------------+---------+--------+

4 rows in set (0.00 sec)

mysql> select * from emp order by sal desc;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| a     | NULL       | 2000.00 |   NULL |

| b     | NULL       | 2000.00 |   NULL |

| zzx1  | 2000-01-01 | 1000.00 |      1 |

| lisa  | 2003-02-01 | 1000.00 |      2 |

+-------+------------+---------+--------+

4 rows in set (0.00 sec)

4. having condition 用于辅助查询的条件过滤：

mysql> select * from emp where sal = 1000 having deptno = 2;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| lisa  | 2003-02-01 | 1000.00 |      2 |

+-------+------------+---------+--------+

1 row in set (0.00 sec)

5. limit,限制查询数据

索引从0开始，查询出1到3条

mysql> select * from emp limit 1,3;

+-------+------------+---------+--------+

| ename | hiredate   | sal     | deptno |

+-------+------------+---------+--------+

| lisa  | 2003-02-01 | 1000.00 |      2 |

| a     | NULL       | 2000.00 |   NULL |

| b     | NULL       | 2000.00 |   NULL |

+-------+------------+---------+--------+

3 rows in set (0.00 sec)

2019-11-22 21:33