MySQL数据查询的相关操作_mysql在查询结果中查询-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45726908/article/details/123693670

数据查询

文章目录

数据查询

select语句

数据查询语句中使用频率最高的是select语句.
数据库用户在客户端编写select语句, 然后发送给MySQL服务器, MySQL服务器实例将select语句进行解析、编译、执行, 从数据表中查询满足条件的若干记录, 将查询的结果集(result_set)返回客户端.

select [all | distinct] 字段列表 from 表名
[where 条件表达式]
[group by 字段名]
[having 条件表达式]
[order by 字段名 [asc | desc] ]
[limit [行号,] 行数];

说明:
select: 指定由查询返回的列.
from: 用于指定数据源, 指定字段列表所在的表或视图.
where: 指定记录的查询条件.
distinct: 去掉查询结果重复的记录.
group by: 用于对查询的数据进行分组.
having: 通常与group by一起使用, 用于查询分组后的数据筛选.
order by: 用于对查询结果的数据进行排序, asc升序, desc降序, 默认升序.
limit: 用于显示查询结果集的记录数.

简单查询

查询指定字段
字段之间使用","分隔.

select 字段列表 from 表名;
条件查询
MySQL可以只查询表中部分记录, 使用where子句, 给出选择记录的条件.

select 字段列表 from 表名 where 条件表达式;
多条件查询
查询语句中可以有多个条件, 使用逻辑运算符连接.

运算符说明
NOT或! 逻辑非
OR或|| 逻辑或
AND或&& 逻辑与
1. 带and的多条件查询
  and关键字可以用来连接多个查询条件, 使用and时只有满足所有查询条件的记录会被加入到结果集中.
  
  select 字段列表 from 表名 where 条件表达式1 and 条件表达式2 [… and 条件表达式n];
2. 带or的多条件查询
  or关键字只要满足查询条件中的任何一个, 那么此记录就会被加入到查询结果集中.
  
  select 字段列表 from 表名 where 条件表达式1 or 条件表达式2 [… or 条件表达式n];
  
  可用in关键字代替多条件逻辑或or, in关键字可以判断字段的值是否在集合中, 在则加入查询结果集中.
  
  select 字段列表 from 表名 where 字段名 [not] in(值1, 值2, …, 值n);

运算符	说明
NOT或!	逻辑非
OR或\|\|	逻辑或
AND或&&	逻辑与

模糊查询

当条件值不确定, 也就是说不能对字符串进行精确查询时, 可以使用like运算符与通配符实现模糊查询, like确定给定的字符串是否与给定的模式匹配.

MySQL中常用的通配符有两种: “%” 和 “_”.

"%"可以随时匹配一个或多个字符, 可以代表任意长度的字符串, 长度可以为0.
"_"只匹配一个字符.

字段名 [not] like 模式;

例如:
select 课程名称 from 课程信息 where 课程名称 like ‘%数据_’;

空值查询

is null关键字可以用来判断字段的值是否为空值null(is not null恰恰相反). 如果字段的值是空值, 该记录将被查询出来.

表达式 is [not] null;

查询结果排序

order by子句用于对查询结果集进行排序.结果集中的记录按照一个或多个字段进行排序, 排序的方向可以是升序(ASC)也可以是降序(DESC), 默认是升序.

order by 字段名1 [asc|desc], [… [asc|desc], 字段名n [asc|desc]];

对含有null值的字段进行排序时, 如果是按升序排列, null值将出现在最前面, 如果是按降序排列, null值将出现在最后面.

order by子句中的排序字段可以是数据表的字段, 也可以是查询结果中的字段, 例如:

select 学号, 姓名, 性别, year(now()) - year(出生日期) as 年龄, 班级编号 from student order by 年龄;

limit子句

查询表中的前几条或者中间几条记录, 可以使用谓语关键字limit实现.

select 字段列表 from 数据源 limit [start,] length;

说明:
start: 表示从第几行开始检索(start的值为0表示数据表中的第一行, 可以忽略)
length: 表示检索记录的行数

去除重复行

用distinct关键字可以去除查询结果中的重复记录.

select distinct 字段名 from 表名;

聚合函数与group by子句

聚合函数用于对一组值进行计算并返回一个汇总值.

MySQL聚合函数:

函数	作用
count(字段名)	返回某字段的总行数或表的总行数
max(字段名)	返回某字段的最大值
min(字段名)	返回某字段的最小值
avg(字段名)	返回莫字段的平均值
sum(字段名)	返回某字段值的和

count()函数
返回所选择集合中非null值的行的数目, 根据表中的某个字段统计总行数. count(*)统计时包含null值的行, count(*)是经过内部优化的, 能够快速返回表中所有的记录总数.

select count(学号) from student; (不包含null值)
或者
select count(*) from student; (包含null值)
max()函数与min()函数
用于统计数据表中某个字段值的最大值, min()函数可以求出表中某个字段值的最小值.

select max(成绩), min(成绩) from choose where 课程号 = ‘1’;
sum()函数与avg()函数
sum()函数可以求出表中某个字段值的总和. avg()函数可以求出表中某个字段值的平均值.

select sum(下单数量), avg(下单数量) from dingdan;
分组查询group by子句
group by子句将查询的数据按照某个字段(或多个字段)进行分组(字段值相同的记录作为一个分组), 通过group by子句将数据划分到不同的组中, 再统计每一组内的数据, 实现对记录的分组查询.
分组查询时, 所查询的字段必须包含在分组的字段中, 目的是使查询到的数据没有矛盾.

select 字段列表 from 表名 group by 字段列表;
having子句
having子句用于设置分组或聚合函数的过滤筛选条件, having子句通常与group by子句一起使用.

select 字段列表 from 表名
group by 字段名
having 条件表达式;

说明:
条件表达式: 一个逻辑表达式, 用于指定分组后的筛选条件.

例子: 统计选课门数超过2门的学生学号
select 学号, count(课程号) from choose
group by 学号 having count(课程号)>2;

多表连接查询

前面提到, MySQL为了避免数据冗余, 将大量数据分成若干数据表存放, 表与表之间的关联通过设置外键实现.
在检索数据时, 需要对多张表的数据进行筛选, 筛选出满足用户要求的数据.
查询语句中的from子句可指定多个数据源.

select 字段列表
from 表名1 [连接类型] join 表名2 on 表1和表2之间的连接条件;

说明:
连接类型: 主要分内连接inner和外连接outer, 外连接outer join又分左外连接left join、右外连接right join、全外连接full join.
ON: 表与表之间的连接条件, 表1.字段(值) = 表2.字段(值), 若条件为真则两表进行连接.

全外连接(full outer join)
表与表之间的连接没有任何条件筛选, 结果集中的字段数是连接表字段数的总和, 记录数是连接表记录数的乘积.

例如:
select student.*, class.* from class join student;

from子句产生的是一个中间结果, 表中的每条记录都是与其它表中记录交叉产生的所有可能的组合, 也就是笛卡尔积.
内连接(inner join)
按照某种条件筛选连接表时, 结果集中都是满足要求的记录, 这种表连接的方式就是内连接.

select 字段列表
from 表名1 [innner] join 表名2 on 表1和表2之间的连接条件;

其中inner关键字可以省略, 只需指定on的条件.
(同时省略inner和on时是全外连接!)

例子:
select buyer.* from dingdan join buyer on dingdan.买家ID = buyer.买家ID where 订单ID = 4;

两张数据表连接时如果存在相同的字段名, 在使用该字段时需要指出具体的表名, 即"表名.字段名", 例如"student.班级编号".
select查询语句中所有子句仍然适用于表的连接查询.
左外连接
左外连接的连接条件只筛选右边表的数据, 对左边的表不进行筛选.(左外连接就是以左边是主, 筛选右表数据,不筛选左表数据)

select 字段列表
from 表1 left join 表2 on 表1和表2之间的连接条件;

表1的所有记录必须出现在结果集中, 然后表1按指定条件与表2连接, 若表2中没有满足连接条件的记录, 则在结果集中表2的相应字段填入null.
右外连接
右外连接的连接条件只筛选左边表的数据, 对右边表的数据不进行筛选.(右外连接就是以右边是主, 筛选左表数据,不筛选右表数据)

select 字段列表
from 表1 right join 表2 on 表1和表2的连接条件;

表1右连接表2, 意味着查询结果集中须包含表2的全部记录, 然后表2按指定的连接条件与表1进行连接, 若表1中没有满足连接条件的记录, 则结果集中表1的相应字段填入null.
表的自身连接
两张表连接时如果表名相同称为表自身连接.

select 字段列表
from 表1 as 别名a join 表1 as 别名b on a和b之间的连接条件;

嵌套查询

将一个查询语句嵌套在另一个查询语句块的条件子句中的查询被称为嵌套查询, 又称子查询.
子查询不仅可以嵌套在select语句中, 还可以嵌套在update语句、insert语句、delete语句中.
包含子查询的select语句被称为主查询. 为了标记子查询和主查询的关系, 子查询必须写在括号()里.
子查询可以包含查询语句中的所有子句, 嵌套在主查询的select语句中的where子句中或having子句中.

比较运算符子查询
如果子查询结果返回的是一个单值, 则可以使用比较运算符和主查询进行连接.

select 字段列表 from 表1 where 表1中字段名比较运算符
(select 表2字段名 from 表2 where 条件表达式);

说明:
比较运算符: ‘>’、 ‘<’、 ‘=’、 ‘>=’、 ‘<=’、 ‘!=’、 ‘!<’、 ‘!>’、 ‘<>’.
IN运算符子查询
子查询结果是多个值时(一个列表), 与主查询的连接只能用IN运算符.

select 字段列表
from 表1 where 表1中字段名 [not] in
(select 表2中字段名 from 表2 where 条件表达式);
EXISTS运算符子查询
exists运算符用于检测子查询结果集是否包含有记录, 如果结果集中包含至少一条记录, 则exists的结果为true, 否则为false.
当返回值是true时, 主查询语句将进行查询; 当返回值是false时, 主查询语句将不会进行查询(或查询不出记录).

ANY、ALL运算符子查询
any、all运算符通常与比较运算符一起使用.
带any查询时, 主查询某个字段的值或字段表达式的值与子查询返回的一列值逐一进行比较, 若某次比较结果为true, 则整个表达式的值为true, 反之为false.
带all查询时, 主查询某个字段的值或字段表达式的值与子查询返回的一列值逐一进行比较, 若每次比较结果都是true, 则整个表达式的值为true, 反之为false.

运算符	语义	运算符	语义
>any	大于子查询结果中的某个值	>all	大于子查询中的所有值
>=any	大于等于子查询结果中的某个值	>=all	大于等于子查询结果中的所有值
< any	小于子查询结果中的某个值	< all	小于子查询结果中的所有值
<=any	小于等于子查询结果中的某个值	<=all	小于等于子查询结果中的所有值
=any	等于子查询结果中的某个值	=all	等于子查询结果中的所有值
!=any	不等于子查询结果中的某个值	!=	不等于子查询结果中的所有值

select 字段列表
from 表1 where 表1中字段或表达式比较运算符 all | any
(select 表2字段名 from 表2 [where 条件表达式]);

数据查询与数据更新

数据插入insert语句中使用select语句, 可以将数据表的查询结果添加到目标表中.

insert [into] 目标表名[(字段列表1)] select 字段列表2 form 数据源 [where 条件表达式];

①字段列表1和字段列表2的字段个数必须相同;
②对应字段的数据类型必须保持一致;
③字段列表1如果省略, 则字段列表2必须和目标表结构一致;
④select查询语句可以包含所有查询子句, 包括group by、having子句等;
⑤数据源可以是多张数据表, where子句可以忽略;

数据查询与数据修改
数据修改语句"update 表 set 字段名 = 值 where 条件表达式"中的set子句可用查询结果代替(查询结果必须是单值), where子句可用嵌套子查询.

update 目标表名
set 字段名 = (select 字段名或表达式
from 数据源 where 条件表达式)
where 目标表中的字段名运算符 (select 字段名或表达式
from 数据源 where 条件表达式);

说明:
运算符可以是比较运算符或IN运算符.
数据查询与数据删除
数据删除语句"delete from 表 where 条件表达式"中的where子句可用嵌套子查询

delete from 表名
where 字段名运算符(select 字段名或表达式
from 数据源 where 条件表达式);