5.MySql查询语句

Fanta丶
10 阅读8分钟

查询关键字: SELECT 在一个正常的业务系统中，查询操作的频次是要远高于增删改的

1.基本语法

SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

上面的完整语法进行拆分，分为以下几个部分：

  • 基本查询（不带任何条件）
  • 条件查询（WHERE）
  • 聚合函数（count、max、min、avg、sum）
  • 分组查询（group by）
  • 排序查询（order by）
  • 分页查询（limit）
  • 手动练习案例

测试数据

-- 创建王者荣耀角色信息表
CREATE TABLE honor_of_kings_hero (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID，自增',
    name VARCHAR(50) NOT NULL COMMENT '角色名称',
    gender ENUM('男', '女') DEFAULT '男' COMMENT '性别',
    age INT COMMENT '年龄',
    release_date DATE COMMENT '角色发布时间',
    faction VARCHAR(50) COMMENT '所属阵营'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='王者荣耀角色信息表';

-- 插入20条王者荣耀角色测试数据
INSERT INTO honor_of_kings_hero(name, gender, age, release_date, faction) VALUES
('李白', '男', 25, '2016-03-01', '大唐'),
('王昭君', '女', 23, '2015-11-26', '北夷'),
('韩信', '男', 27, '2015-10-29', '大汉'),
('貂蝉', '女', 22, '2015-11-26', '魏'),
('吕布', '男', 30, '2015-10-29', '魏'),
('赵云', '男', 26, '2015-10-29', '蜀'),
('妲己', '女', 21, '2015-10-29', '商'),
('孙悟空', '男', 28, '2015-10-29', '西游'),
('小乔', '女', 20, '2015-10-29', '吴'),
('周瑜', '男', 29, '2015-10-29', '吴'),
('花木兰', '女', 24, '2016-02-01', '长城守卫军'),
('曹操', '男', 32, '2015-10-29', '魏'),
('甄姬', '女', 23, '2016-01-01', '魏'),
('后羿', '男', 25, '2016-01-01', null),
('安琪拉', '女', 19, '2016-01-01', '魔法学院'),
('诸葛亮', '男', 28, '2017-01-12', '蜀'),
('孙尚香', '女', 22, '2015-10-29', '吴'),
('关羽', '男', 31, '2015-10-29', '蜀'),
('露娜', '女', 24, '2016-02-01', '北夷'),
('张飞', '男', 29, '2016-02-01', '蜀');

1.基础查询

在基本查询的DQL语句中，不带任何的查询条件，查询的语法如下：

1). 查询多个字段:

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;

SELECT * FROM 表名 ;

注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

2). 字段设置别名:

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;

SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ...

3). 去除重复记录:SELECT DISTINCT 字段列表 FROM 表名;

A. 查询指定字段 name, faction, age，gender并返回

select name,gender,age,faction from honor_of_kings_hero;

B. 查询返回所有字段

select * from honor_of_kings_hero;

C. 查询所有角色的阵营,起别名

select faction as '阵营'from honor_of_kings_hero;

--as 可以省略
select faction '阵营'from honor_of_kings_hero;

D. 查询角色阵营有哪些(不要重复)

select distinct faction from honor_of_kings_hero;

2.条件查询

1). 语法SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

常用的比较运算符如下:

比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<> 或 !=不等于
BETWEEN ... AND ...在某个范围之内(含最小、最大值)
IN(...)在in之后的列表中的值，多选一
LIKE 占位符模糊匹配(_匹配单个字符, %匹配任意个字符）
IS NULL是NULL

常用的逻辑运算符如下:

逻辑运算符功能
AND 或 &&并且 (多个条件同时成立)
OR 或 | |并且 (多个条件同时成立)
NOT 或 !非 , 不是

A. 查询年龄等于 28 的角色

select * from honor_of_kings_hero where age=28;

B. 查询年龄小于 20 的角色信息

select * from honor_of_kings_hero where age<20;

C. 查询年龄小于等于 20 的角色信息

select * from honor_of_kings_hero where age=20;

D. 查询没有阵营信息的角色信息

select * from honor_of_kings_hero where faction is null;

E. 查询有阵营信息的角色信息

select * from honor_of_kings_hero where faction is not null;

F. 查询年龄不等于 22 的角色信息

select * from honor_of_kings_hero where age!=22;
select * from honor_of_kings_hero where age<>22;

G. 查询年龄在15岁(包含) 到 20岁(包含)之间的角色信息

select * from honor_of_kings_hero where age>=15 && age<=20;
select * from honor_of_kings_hero where age>=15 and age<=20;
select * from honor_of_kings_hero where age between 15 and 20;

H. 查询性别为 女 且年龄小于 25岁的角色信息

select * from honor_of_kings_hero where age<25 and gender='女';

I. 查询年龄等于23 或 29 或 32 的角色信息

select * from honor_of_kings_hero where age in(23,29,32);
select * from honor_of_kings_hero where age =23 or age=29 or age=32;

J. 查询姓名为两个字的角色信息

select * from honor_of_kings_hero where name like '__';

K. 查询姓名最后一位是 白的角色信息

select * from honor_of_kings_hero where name like '%白';

3.聚合函数 将一列数据作为一个整体，进行纵向计算 。

1).常见的聚合函数

函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和

2).语法 SELECT 聚合函数(字段列表) FROM 表名 ; 注意 : NULL值是不参与所有聚合函数运算的。

A. 统计角色数量

select COUNT( *) from honor_of_kings_hero;-- 统计的是总记录数  20

select count(1) from honor_of_kings_hero; -- 统计的是总记录数  20

select COUNT(faction) from honor_of_kings_hero; -- 统计的是faction字段不为null的记录数 19

B. 统计平均年龄

select AVG( age) from honor_of_kings_hero;

C. 统计最大年龄

select MAX(age) from honor_of_kings_hero; --32

D. 统计最小年龄

select MIN(age) from honor_of_kings_hero; --19

E. 统计阵营为魏的年龄之和

select SUM(age) from honor_of_kings_hero where faction='魏';

4.分组查询

1). 语法SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];

2). where与having区别

  • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  • 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

• 执行顺序: where > 聚合函数 > having 。

• 支持多字段分组, 具体语法为 : group by columnA,columnB

A. 根据性别分组 , 统计男性角色 和 女性角色的数量

select gender ,count(*) as '性别数量' from honor_of_kings_hero group by gender;

B. 根据性别分组 , 统计男性角色 和 女性角色的平均年龄

select gender,AVG(age) as '平均年龄' from honor_of_kings_hero group by gender;

C.查询年龄小于25，并根据阵营分组，获取角色数量大于等于2的阵营

select faction ,count(*) faction_count  from honor_of_kings_hero where age<25 group by faction having faction_count>=2;

5.排序查询

1). 语法SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

2). 排序方式ASC : 升序(默认值) DESC: 降序如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

A. 根据年龄对角色进行升序排序 asc默认可省略

select * from honor_of_kings_hero order by age  asc ;

B. 根据发布时间, 对角色进行降序排序

select  * from honor_of_kings_hero order by release_date desc;

C. 根据年龄对角色进行升序排序 , 年龄相同 , 再按照发布时间进行降序排序

select * from honor_of_kings_hero order by age asc,release_date desc;

6.分页查询

注意事项:

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

1). 语法 SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

A. 查询第1页角色数据, 每页展示10条记录

select * from honor_of_kings_hero limit 10;

B. 查询第2页角色数据, 每页展示10条记录 --------> (页码-1)*页展示记录数

select * from honor_of_kings_hero limit 10 , 10;

7.练习案例

1). 查询年龄为20,21,22,23岁的角色信息。

select * from honor_of_kings_hero where age in (20,21,22,23);

2). 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的角色。（下划线 _ 几个代表匹配几个字）

select * from honor_of_kings_hero where gender='男' and (age between 20 and 40) and name like '___';

3). 统计角色表中, 年龄小于30岁的 , 男性和女性的人数

select gender,COUNT(gender) as '人数' from honor_of_kings_hero where age<=30  group by  gender;

4). 查询所有年龄小于等于25岁的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按发布时间降序排序。

select name,age from honor_of_kings_hero where age<=25 order by age,release_date desc;

5). 查询性别为男，且年龄在20-40 岁(含)以内的前5个角色信息，对查询的结果按年龄升序排序，年龄相同按发布时间升序排序。

select * from honor_of_kings_hero where (age between 20 and 40) and gender='男' order by  age,release_date  limit 5;

执行顺序

image.png from ... where ... group by ... having ... select ... order by ... limit ...

avatar
文章
阅读
粉丝