做测试不会 SQL？超详细的 SQL 查询语法教程来啦！ – 测试派

Auth:焱讲       Date:2021/02/19       Cat:文章精选       Word:共6878字

已关闭评论
文章目录 「隐藏」
  1. 前言
  2. 1、数据准备
  3. 2、基本查询
  4. 3、条件查询
    1. 1、比较运算符
    2. 2、逻辑运算符
    3. 3、模糊查询
    4. 4、范围查询
    5. 5、空判断
    6. 6、优先级说明
  5. 4、排序
  6. 5、聚合函数
    1. 1、计数
    2. 2、最大值
    3. 3、最小值
    4. 4、求和
    5. 5、平均值
  7. 6、分组查询
    1. 1、group by
    2. 2、group by + group_concat()
    3. 3、group by + 聚合函数
    4. 4、group by + having
    5. 5、group by + with rollup
    6. 5、分页
  8. 7、子查询
  9. 8、连接查询

做测试不会 SQL？超详细的 SQL 查询语法教程来啦！

前言

作为一名测试工程师，工作中在对测试结果进行数据比对的时候，或多或少要和数据库打交道的，要和数据库打交道，那么一些常用的 SQL 查询语法必须要掌握。最近有部分做测试小伙伴表示 SQL 查询不太会，问我有没有 SQL 查询语法这一块的文档可以学习，于是小编就整理了这篇超详细的 SQL 查询语法教程，来给大家参考学习！

1、数据准备

创建数据库、数据表

-- 创建数据库
create database test1 charset=utf8;

-- 使用数据库
use test1;

-- 创建students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

学生表字段说明

字段 说明
id 主键 id
name 学生姓名
age 学生年龄
height 学生身高
gender 学生性别
cls_id 所属班级 id(外键)
is_delete 逻辑删除（，默认为 0，1 表示删除） 
-- 创建classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

班级表字段说明

字段 说明
id 主键 id
name 班级名称

准备数据

-- 向classes表中插入数据
insert into classes values (6, "python01"), (0, "python02");
-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月',18,180.00,2,2,1),
(0,'项羽',29,185.00,1,1,0),
(0,'刘刘',59,175.00,1,2,1),
(0,'王老麻子',38,160.00,2,1,0),
(0,'老李',28,150.00,4,2,1),
(0,'老张',18,172.00,2,1,1),
(0,'老牛',36,NULL,1,1,0),
(0,'张飞',58,181.00,1,2,0),
(0,'关羽',74,166.00,2,2,0),
(0,'刘备',66,162.00,3,3,1),
(0,'孙权',77,180.00,2,4,0),
(0,'曹操',66,170.00,1,4,0),
(0,'周瑜',55,176.00,2,5,0);

2、基本查询

  • 查询所有字段
select * from 表名;
例：
select * from students;
  • 查询指定字段
select 列1,列2,... from 表名;
例:
select name from students;
  • 使用 as 给字段起别名
select id as 序号, name as 名字, gender as 性别 from students;
  • 可以通过 as 给表起别名
-- 如果是单表查询 可以省略表明
select id, name, gender from students;

-- 表名.字段名
select students.id,students.name,students.gender from students;

-- 可以通过 as 给表起别名 
select s.id,s.name,s.gender from students as s;
  • 消除重复行
  • 在 select 后面列前使用 distinct 可以消除重复的行 
select distinct 列1,... from 表名;
例：
select distinct gender from students;

3、条件查询

使用 where 子句对表中的数据筛选，结果为 true 的行会出现在结果集中

  • 语法如下：
select * from 表名 where 条件;
例：
select * from students where id=1;
  • where 后面支持多种运算符，进行条件的处理
    • 比较运算符
    • 逻辑运算符
    • 模糊查询
    • 范围查询
    • 空判断

1、比较运算符

  • 等于： =
  • 大于： >
  • 大于等于： >=
  • 小于： <
  • 小于等于： <=
  • 不等于： != 或 <>

例 1：查询编号大于 3 的学生

select * from students where id > 3;

例 2：查询编号不大于 4 的学生

select * from students where id <= 4;

例 3：查询姓名不是“关羽”的学生

select * from students where name != '关羽';

例 4：查询没被删除的学生

select * from students where is_delete=0;

2、逻辑运算符

  • and
  • or
  • not

例 5：查询编号大于 3 的女同学

select * from students where id > 3 and gender=0;

例 6：查询编号小于 4 或没被删除的学生

select * from students where id < 4 or is_delete=0;

3、模糊查询

  • like
  • % 表示任意多个任意字符
  • _表示一个任意字符

例 7：查询姓黄的学生

select * from students where name like '黄%';

例 8：查询姓黄并且“名”是一个字的学生

select * from students where name like '黄_';

例 9：查询姓刘或叫飞的学生

select * from students where name like '刘%' or name like '%飞';

4、范围查询

  • in 表示在一个非连续的范围内

例 10：查询编号是 1 或 3 或 8 的学生

select * from students where id in(1,3,8);
  • between ... and ...表示在一个连续的范围内

例 11：查询编号为 3 至 8 的学生

select * from students where id between 3 and 8;

例 12：查询编号是 3 至 8 的男生

select * from students where (id between 3 and 8) and gender=1;

5、空判断

  • 注意：null 与''是不同的
  • 判空 is null

例 13：查询没有填写身高的学生

select * from students where height is null;
  • 判非空 is not null

例 14：查询填写了身高的学生

select * from students where height is not null;

例 15：查询填写了身高的男生

select * from students where height is not null and gender=1;

6、优先级说明

  • 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
  • and 比 or 先运算，如果同时出现并希望先算 or，需要结合()使用

4、排序

为了方便查看数据，可以对数据进行排序

语法：

select * from 表名 order by 列1 asc|desc

说明

  • 将行数据按照列 1 进行排序，如果某些行列 1 的值相同时，则按照列 2 排序，以此类推
  • 默认按照列值从小到大排列（asc）
  • asc 从小到大排列，即升序
  • desc 从大到小排序，即降序

例 1：查询未删除男生信息，按学号降序

select * from students where gender=1 and is_delete=0 order by id desc;

例 2：查询未删除学生信息，按名称升序

select * from students where is_delete=0 order by name;

例 3：显示所有的学生信息，先按照年龄从大--> 小排序，当年龄相同时 按照身高从高--> 矮排序

select * from students  order by age desc,height desc;

5、聚合函数

为了快速得到统计数据，经常会用到如下 5 个聚合函数

1、计数

  • count(*)表示计算总行数，括号中写星与列名，结果是相同的

例 1：查询学生总数

select count(*) from students;

2、最大值

  • max(列)表示求此列的最大值

例 2：查询女生的编号最大值

select max(id) from students where gender=2;

3、最小值

  • min(列)表示求此列的最小值

例 3：查询未删除的学生最小编号

select min(id) from students where is_delete=0;

4、求和

  • sum(列)表示求此列的和

例 4：查询男生的总年龄

select sum(age) from students where gender=1;

-- 平均年龄
select sum(age)/count(*) from students where gender=1;

5、平均值

  • avg(列)表示求此列的平均值

例 5：查询未删除女生的编号平均值

select avg(id) from students where is_delete=0 and gender=2;

6、分组查询

1、group by

  1. group by 的含义：将查询结果按照 1 个或多个字段进行分组，字段值相同的为一组
  2. group by 可用于单个字段分组，也可用于多个字段分组
select * from students;

#按性别进行分组
select gender from students group by gender;

根据 gender 字段来分组，gender 字段的全部值有 4 个'男','女','中性','保密'，所以分为了 4 组 当 group by 单独使用时，只显示出每组的第一条记录， 所以 group by 单独使用时的实际意义不大

2、group by + group_concat()

  1. group_concat(字段名)可以作为一个输出字段来使用，
  2. 表示分组之后，根据分组结果，使用 group_concat()来放置每一组的某字段的值的集合
select gender from students group by gender;

#显示分组后，每个组的名字
select gender,group_concat(name) from students group by gender;


#显示分组后，每个组的id
select gender,group_concat(id) from students group by gender;

3、group by + 聚合函数

  1. 通过 group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个 值的集合 做一些操作
#分组后显示年龄
select gender,group_concat(age) from students group by gender;


#分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  32.6000 |
| 女     |  23.2857 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+

#分别统计性别为男/女的人的个数
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
+--------+----------+

4、group by + having

  1. having 条件表达式：用来分组查询后指定一些条件来输出查询结果
  2. having 作用和 where 一样，但 having 只能用于 group by
select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

5、group by + with rollup

  1. with rollup 的作用是：在最后新增一行，来记录当前列里所有记录的总和
select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+


select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+

5、分页

当数据量过大时，在一页中查看数据是一件非常麻烦的事情，这个时候就需要多数据进行分页，下面来看看 SQL 分页查询

语法

select * from 表名 limit start,count

说明

  • 从 start 开始，获取 count 条数据

例 1：查询前 3 行男生信息

select * from students where gender=1 limit 0,3;

示例：分页

  • 已知：每页显示 m 条数据，当前显示第 n 页
  • 求总页数：此段逻辑后面会在 python 中实现
    • 查询总条数 p1
    • 使用 p1 除以 m 得到 p2
    • 如果整除则 p2 为总数页
    • 如果不整除则 p2+1 为总页数
  • 求第 n 页的数据
select * from students where is_delete=0 limit (n-1)*m,m

7、子查询

子查询

在一个 select 语句中，嵌入了另外一个 select 语句， 那么被嵌入的 select 语句称之为子查询语句

子查询分类

  • 标量子查询： 子查询返回的结果是一个数据(一行一列)
  • 列子查询： 返回的结果是一列(一列多行)
  • 行子查询： 返回的结果是一行(一行多列)

标量子查询

  1. 查询班级学生平均年龄
  2. 查询大于平均年龄的学生

查询班级学生的平均年龄

select * from students where age > (select avg(age) from students);

列级子查询

  • 查询还有学生在班的所有班级名字
    1. 找出学生表中所有的班级 id
    2. 找出班级表中对应的名字
select name from classes where id in (select cls_id from students);

行级子查询

  • 需求： 查找班级年龄最小，身高最矮的学生
  • 行元素： 将多个字段合成一个行元素，在行级子查询中会使用到行元素
select * from students where height=(select min(height) from students where age=(select min(age) from students));

子查询中特定关键字使用

  • in 范围
    • 格式： 主查询 where 条件 in (列子查询)

8、连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回，这中情况下就需要使用到连接查询了，下面给大家介绍一下常用的 3 中连接查询语法：

  • 1、内连接：

    查询的结果为两个表匹配到的数据

    • 语法 
    select * from 表1 inner join 表2 on 表1.列 = 表2.列
    • 例：使用左连接查询班级表与学生表 
    select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
  • 2、右连接：

    查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用 null 填充

    • 语法 
    select * from 表1 right join 表2 on 表1.列 = 表2.列
    • 例：使用右连接查询班级表与学生表 
    select * from students as s right join classes as c on s.cls_id = c.id;
  • 3、左连接：

    查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用 null 填充

    • 语法 
    select * from 表1 left join 表2 on 表1.列 = 表2.列
    • 例：查询学生姓名及班级名称 
    select * from students as s left join classes as c on s.cls_id = c.id;

常用的连接查询语法就给大家介绍到这里了，更多的连接查询语法大家可以扩展学习

本文首发于：做测试不会 SQL？超详细的 SQL 查询语法教程来啦！ – 测试派-焱讲
      

评论已关闭！