MySQL中DML的常用操作

---

DML(Data Manipulation Language)数据操作语言，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是必须要掌握的指令，DML和SQL中的select熟称CRUD（增删改查）。

---

插入操作

插入单行

方式一

insert into 表名[(字段,字段)] values (值,值);

方式二

insert into 表名 set 字段 = 值,字段 = 值;

插入多行

方式一

insert into 表名 [(字段,字段)] values (值,值),(值,值),(值,值);

方式二

insert into 表 [(字段,字段)] 数据来源select语句;

---

更新操作

单表更新

update 表名 [[as] 别名] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件];

多表更新

update 表1 [[as] 别名1],表名2 [[as] 别名2]
set [别名.]字段 = 值,[别名.]字段 = 值
[where条件]

---

删除操作

使用delete删除

delete单表删除

delete [别名] from 表名 [[as] 别名] [where条件];

delete多表删除

delete [别名1,别名2] from 表1 [[as] 别名1],表2 [[as] 别名2] [where条件];

使用truncate删除

truncate 表名;

drop，truncate，delete之间的区别

	drop	truncate	delete
条件删除	不支持	不支持	支持
删除表结构	支持	不支持	不支持
删除表内容	支持	支持	支持
事务的删除方式	不支持	不支持	支持
触发触发器	否	否	是
SQL语言	DDL	DDL	DML
删除速度	快	较快	一般

---

查询操作

SELECT基础查询

查询常量

select 常量值1,常量值2,常量值3;
mysql> select 1,'b';
+---+---+
| 1 | b |
+---+---+
| 1 | b |
+---+---+
1 row in set (0.00 sec)

查询表达式

select 表达式;
mysql> select 1+2,3*10,10/3;
+-----+------+--------+
| 1+2 | 3*10 | 10/3  |
+-----+------+--------+
|  3 |  30 | 3.3333 |
+-----+------+--------+
1 row in set (0.00 sec)

查询指定的字段

select 字段1,字段2,字段3 from 表名;
mysql> select a,b from test1;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)

查询所有的列

select * from 表名
mysql> select * from test1;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)

SELECT条件查询

等于（=）

select 列名 from 表名 where 列 = 值;

不等于（<>、!=）

select 列名 from 表名 where 列 <> 值;
或者
select 列名 from 表名 where 列 != 值;

大于（>）

select 列名 from 表名 where 列 > 值;

AND（并且）

select 列名 from 表名 where 条件1 and 条件2;

OR（或者）

select 列名 from 表名 where 条件1 or 条件2;

like（模糊查询）

select 列名 from 表名 where 列 like pattern;

pattern中可以包含通配符，有以下通配符：
%：表示匹配任意一个或多个字符
_：表示匹配任意一个字符。

BETWEEN AND(区间查询)

selec 列名 from 表名 where 列名 between 值1 and 值2;

IN查询

select 列名 from 表名 where 字段 in (值1,值2,值3,值4);

NOT IN查询

select 列名 from 表名 where 字段 not in (值1,值2,值3,值4);

IS NULL（返回值为空的记录）

select 列名 from 表名 where 列 is null;

IS NOT NULL（返回值不为空的记录）

select 列名 from 表名 where 列 is not null;

SELECT分组查询

单字段分组

mysql> SELECT
      user_id 用户id, COUNT(id) 下单数量
    FROM
      t_order
    GROUP BY user_id;
+----------+--------------+
| 用户id  | 下单数量   |
+----------+--------------+
|   1001 |       3 |
|   1002 |       4 |
|   1003 |       2 |
+----------+--------------+
3 rows in set (0.00 sec)

多字段分组

mysql> SELECT
      user_id 用户id, the_year 年份, COUNT(id) 下单数量
    FROM
      t_order
    GROUP BY user_id , the_year;
+----------+--------+--------------+
| 用户id  | 年份  | 下单数量   |
+----------+--------+--------------+
|   1001 |  2017 |       1 |
|   1001 |  2018 |       2 |
|   1002 |  2018 |       3 |
|   1002 |  2019 |       1 |
|   1003 |  2018 |       1 |
|   1003 |  2019 |       1 |
+----------+--------+--------------+
6 rows in set (0.00 sec)

分组前筛选数据（使用WHERE关键字）

mysql> SELECT
      user_id 用户id, COUNT(id) 下单数量
    FROM
      t_order t
    WHERE
      t.the_year = 2018
    GROUP BY user_id;
+----------+--------------+
| 用户id  | 下单数量   |
+----------+--------------+
|   1001 |       2 |
|   1002 |       3 |
|   1003 |       1 |
+----------+--------------+
3 rows in set (0.00 sec)

where后是不可以跟聚合函数的

分组后筛选数据（使用having关键字）

mysql> SELECT
     user_id 用户id, COUNT(id) 下单数量
    FROM
     t_order t
    WHERE
     t.the_year = 2018
    GROUP BY user_id
    HAVING count(id)>=2;
+----------+--------------+
| 用户id  | 下单数量   |
+----------+--------------+
|   1001 |       2 |
|   1002 |       3 |
+----------+--------------+
2 rows in set (0.00 sec)

having后是可以跟聚合函数的

SELECT排序与分页

排序查询（order by）

select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc];

单字段排序

mysql> select * from test2 order by a desc;
+------+----------+
| a   | b     |
+------+----------+
|  100 | javacode |
|  10 | jack   |
|   8 | tom    |
|   5 | ready   |
+------+----------+
4 rows in set (0.00 sec)

多字段排序

mysql> select * from stu order by age desc,id asc;
+------+-----+---------------+
| id  | age | name      |
+------+-----+---------------+
| 1004 |  20 | 张国荣     |
| 1005 |  20 | 刘德华     |
| 1010 |  19 | 梁朝伟     |
| 1001 |  18 | 路人甲Java   |
| 1003 |  18 | 张学友     |
+------+-----+---------------+
5 rows in set (0.00 sec)

分页查询（limit）

select 列 from 表 limit [offset,] count;

获取前n项记录

select 列 from 表 limit 0,n;
或者
select 列 from 表 limit n;

获取排名第n到m的记录

select 列 from 表 limit n-1,m-n+1;

开发过程中，分页我们经常使用，分页一般有2个参数：
page：表示第几页，从1开始，范围[1,+∞)
pageSize：每页显示多少条记录，范围[1,+∞)
如：page = 2，pageSize = 10，表示获取第2页10条数据。我们使用limit实现分页，语法如下：

select 列 from 表名 limit (page - 1) * pageSize,pageSize;