MySQL数据操作（增、删、改、查）

DQL、DML概念讲解

• DQL：数据库查询语言，指的是select语句
• DML：数据库操纵语言，指的是insert、update、delete语句

一、insert

插入数据分为单个插入、批量插入。

1.1、 单个插入

insert into 表名[(字段1, 字段2 ...)] values (值1， 值2);

或者

insert into 表名 set 字段1 = 值1 , 字段2 = 值2 ...;

• 值和字段需要一一对应。
• 如果是字符类型或日期类型，值需要用单引号括起来；如果是数值类型，不需要用单引号括起来。
• 字段和值的个数必须一致，位置对应。
• 字段如果不能为空，则必须插入值。
• 可以为空的字段可以不用插入值，但需要注意：字段和值都不写；或字段写上，值用null代替。
• 表名后面的字段可以省略不写，此时表示所有字段，顺序和表中字段顺序一致。

1.2、批量插入

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

或

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

数据来源select语句可以有很多种写法，需要注意：select返回的结果和插入数据的字段数量、顺序、类型需要一致。

二、delete

delete from table_name [where condition];

1. 在多行删除或者涉及重要数据的操作中，建议使用事务进行管理，确保操作的原子性，并在出现错误时能够回滚到操作前的状态。
2. 如果表间存在外键约束（Foreign Key Constraint），直接删除相关记录可能导致违反约束。此时，可能需要先处理依赖关系（如删除子表记录或调整外键约束设置），或者在删除语句中使用级联删除（CASCADE）等选项。

三、update

update table_name set column1 = value1, column2 = value2, ...
	[where condition]
	[order by ...]
	[limit ...]

• 事务管理：对于涉及多个行或重要数据的更新操作，应使用事务确保操作的原子性，并在出现错误时能够回滚到操作前的状态。
• 触发器：MySQL支持定义触发器（trigger），在执行update操作前后自动执行特定的SQL语句，用于实现复杂的业务规则、审计追踪等。

四、select

查询操作需要重点关注以下几点：

• 查询什么：数据表的记录的列值，函数
• 从哪里查询：单表、多表
• 查询的条件：where条件查
• 查询的结果如何显示：排序、分页、分组

select {* | <字段列名>}
	[
		from <表 1>, <表 2> ...
		[where <表达式>]
		[group by <字段1>，<字段2>...]
		[having <expression> [{<operator> <expression>}…]]
		[order by 字段 [asc|desc], ...]
		[
			limit [<offset>,] <row count>
		]
]

• order by后的字段后面必须要有asc或者desc
• slect后面可以有*、n个字段或者聚合函数。字段中可以使用聚合函数

4.2 基本的查询

select  字段1, 字段2, 字段3 ...  from 表名 [as] 别名;

• select关键字后接的可以是 函数、表达式、*(表中所有的列)
• select关键字后面的字段可以使用别名：select 字段 [as] 别名 from 表名 [as] 别名

4.3 条件查询

select 列名 from 表名 where 列 运算符 值

运算符有：

4.3.1. 条件查询运算符

操作符	描述
=	等于
<>或者！=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

• 值如果是字符串类型，需要用单引号或者双引号引起来。
• sql语句中尽量使用<>来做不等判断
• 对于>、<、>=、<=，数值按照大小比较；字符按照ASCII码对应的值进行比较，比较时按照字符对应的位置一个字符一个字符的比较。

4.3.2 逻辑查询运算符

当我们需要使用多个条件进行查询的时候，需要使用逻辑查询运算符。

逻辑运算符	描述
AND	多个条件都成立
OR	多个条件中满足一个

4.3.3 模糊查询like

select 列名 from 表名 where 列 like pattern;

pattern中可以包含通配符，有以下通配符：

• %：表示匹配任意一个或多个字符；
• _：表示匹配任意一个字符。

4.3.4 范围查询 between....end

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围，这些值可以是数值、文本或者日期，属于一个闭区间查询。

selec 列名 from 表名 where 列名 between 较小值 and 较大值;

• 返回对应的列的值在[较小值,较大值]区间中的记录
• 两个临界值不要调换位置，只能是大于等于左边的值，并且小于等于右边的值。

4.3.5 in/not in

in 操作符允许我们在 where 子句中规定多个值。

not in和in刚好相反，in是列表中被匹配的都会被返回，not in是和列表中都不匹配的会被返回。

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

• in 后面括号中可以包含多个值，对应记录的字段满足in中任意一个都会被返回

• in列表的值类型必须一致或兼容

• in列表中不支持通配符。

4.3.6 is null /is not null

查询运算符（=、<、>、!=）、like、between and、in、not in对NULL值查询不起效。mysql为我们提供了查询空值的语法：is null、is not null、<=>（安全等于）

• <=>：既可以判断NULL值，又可以判断普通的数值，可读性较低，用得较少
• 建议创建表的时候，尽量设置表的字段不能为空，给字段设置一个默认值

4.4 排序查询

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

4.5 分页查询

select 字段1,字段2,字段3 ... from 表名
	limit [<offset>,] <row count>

• offset：从结果集中的第几行开始返回数据，偏移量从0开始计数。
• row_count：返回的行数，即每页显示的数据条数。

4.6 分组查询

select 列 from 表名
	where [查询条件]
	group by [分组表达式]
	having [分组过滤条件]
	order by [排序条件]
	limit [offset,] count;

• where和having的区别
  • where是在分组（聚合）前对记录进行筛选，而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果。
  • 可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后面的聚集函数相同。having中可以使用聚合函数，但是where不能使用聚合函数

4.7 连接查询

当我们查询的数据来源于多张表的时候，我们需要用到连接查询，连接查询使用率非常高。

连接查询可以分为：

• 内连接
• 外连接
• 左连接
• 右连接

1、笛卡尔积

介绍连接查询之前，我们需要先了解一下笛卡尔积。

笛卡尔积简单点理解：有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。假如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

java伪代码表示如下：

for(Object eleA : A){    
  for(Object eleB : B){        
    System.out.print(eleA+","+eleB);    
  }
}

sql中笛卡尔积语法

select 字段 from 表1,表2[,表N];
或者
select 字段 from 表1 join 表2 [join 表N];

2、内连接

同时将两表作为参考对象，根据ON后给出的两表的条件将两表连接起来。结果则是两表同时满足ON后的条件的部分才会列出

select 字段 from 表1 inner join 表2 on 连接条件;
或
select 字段 from 表1 join 表2 on 连接条件;
或
select 字段 from 表1, 表2 [where 关联条件];

内连接相当于在笛卡尔积的基础上加上了连接的条件。

当没有连接条件的时候，内连接上升为笛卡尔积。

过程用java伪代码如下：

for(Object eleA : A){    
  for(Object eleB : B){        
    if(连接条件是否为true){            
      System.out.print(eleA+","+eleB);        
    }    
  }
}

3、外连接

外连接涉及到2个表，分为：主表和从表，要查询的信息主要来自于哪个表，谁就是主表。

外连接查询结果为主表中所有记录。如果从表中有和on条件匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。

最终：外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。

外连接分为2种：

左外连接（左连接）：使用left join关键字，left join左边的是主表。

右外连接（右连接）：使用right join关键字，right join右边的是主表。

4、左连接

是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

select 列 from 主表 left join 从表 on 连接条件;

5、右连接

是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

案例演示

内连接

select * from a_table a inner join b_table b on a.a_id = b.b_id;

查询结果：

左连接

先查左边表所有数据，然后按on条件拼接

select * from a_table a left join b_table b on a.a_id = b.b_id;

查询结果：

右连接

先查右边表所有数据，然后按on条件拼接

select * from a_table a right join b_table b on a.a_id = b.b_id;

查询结果：

4.8 子查询

• 出现在select语句中的select语句，称为子查询或者内查询。外部的select查询语句称为主查询或外查询。
• 我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询。

4.8.1 单行子查询

子查询返回的结果只有一条记录称为单行子查询。

单行子查询中使用的比较操作符：

// 查询工资大于149号员工工资的员工的信息
select last_name  from employees
	where salary > (
		select salary from employees
               where employee_id = 149
    );

4.8.2 多行子查询

子查询返回的结果有多条记录称为多行子查询。多行比较操作符：

• 重点关注any与all的区别

4.8.3 相关子查询

子查询中引用了外部查询中出现的表的列。它比非相关子查询慢。

select * from employees e
where salary > (
  select AVG(salary) from employees
  	where department = e.department
);