文章目录
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];
- 在
多行删除
或者涉及重要数据
的操作中,建议使用事务进行管理,确保操作的原子性,并在出现错误时能够回滚到操作前的状态。- 如果表间存在
外键约束(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
);