sql语句之query笔记

发表于 更新于 分类于 Disqus： 本文字数： 3.9k 阅读时长 ≈ 4 分钟

此为sql语句的开篇记录，作为我多次维护数据的应用笔记，以求日后方便。

统计类的查询

大多是统计类的函数的运用，主要有：

  • COUNT(): 计数
  • SUM(): 求和
  • AVG()：求平均值
  • MIN()：取最小值
  • MAX()：取最大值
1
2
3
4
5
select sum(`field`) as [alias] from [table] where [where case];
select count(1) as [alias] from [table] where [where case];
select avg(`field`) as [alias] from [table] where [where case];
select min(`field`) as [alias] from [table] where [where case];
select max(`field`) as [alias] from [table] where [where case];

count 里面一般传一个字段或者是一个数值，个人喜欢传 1

数据过滤

某种情况下你需要过滤某些数据，普遍的情况下，我们会用where语句来过滤。

所有数据

一般的查询来说，拿到所有数据，根本不需要where，就可以拿到所有语句。
但是我们要更新所有语句，或者快速删除所有数据(保留自增键)的时候，如果不写入where条件的话，执行语句会报错，提示必须要有where条件。
这里有一个小技巧。

既然必须要有where,那我们就写一个一个条件，比如1=1,1>0,1<2,foo<>bar等等.

1
2
3
4
where 1 = 1
where 1 > 0
where 1 < 2
where 'foo' <> 'bar'

然后我们的语句大概就是这个样子

1
2
3
select [field] from [table] where true;
update [table] set [field] = 'some value' where true;
delete from [table] where true;
去重
  • distinct 可以支持多个字段去重
  • group by 这个主要应用在聚合统计场景中，但是也可以去重
1
2
select distinct [field], [other field] from [table] where [where case];
select [field] from [table] where [where case] group by [field];
筛选重复数据
  • count 通过计数来筛选数据(计数值大于 1 的就是重复值，反之就是唯一值)
1
select count(1) as _c from [table] where [where case] group by [field];
查询子语句
  • 有些时候加入查询子语句更方便理解，如上面的查询就可以改写成这样的
1
2
select * from (select count(1) as _c from [table] where [where case] group by [field]) b where b._c > 1;
# b._c = 1;

是不是清晰多了。

题外话，一个面试题：请从数据库(people)中取出随机的五条数据（有效数据量不断会上升）。

解答：

1
2
3
4
5
6
7
# 1 先拿到最小id和最大id
select min(id) as minId from perple;
select max(id) as maxId from perple;
# 2 然后拿到一个随机id(介于最小，最大之间)
# 3 再拿到一条记录
select [field] from people where id > round(rand() * ([maxId] - [minId]) + [minId]) limit 1;
# 然后重复 5 次 2 ~ 3 操作

这里以PHP代码示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
<?php
/**
 * Created by PhpStorm.
 * User: caixian
 * Date: 2099/8/12
 * Time: 14:47
 */

namespace App\Controller;


use ...;

/**
 * @Route("/user")
 * Class User
 * @package App\Controller
 */
class User extends AbstractController
{
    /**
     * @var UserRepository
     */
    private $userRepository;

    /**
     * @var EntityManagerInterface
     */
    private $entityManager;

    /**
     * User constructor.
     * @param UserRepository              $userRepository
     * @param EntityManagerInterface      $entityManager
     */
    public function __construct (UserRepository $userRepository,
                                 EntityManagerInterface $entityManager)
    {
        $this->userRepository = $userRepository;
        $this->entityManager  = $entityManager;
    }

    /**
     * @Route("/info/rand", methods={"GET"}, name="randInfo")
     * @throws NonUniqueResultException
     */
    public function randInfo()
    {
        $qb = $this->userRepository->createQueryBuilder('t');

        $minId = $qb->select('min(t.id)')->getQuery()->getSingleScalarResult();
        $maxId = $qb->select('max(t.id)')->getQuery()->getSingleScalarResult();

        $randNum = 3;

        $results = [];
        $randQb = $this->userRepository->createQueryBuilder('r');

        while (true) {
            $randItem = $randQb->where('r.id >= round(rand() * :randNum + :minId)')
                ->setParameters([
                    'randNum' => bcsub($maxId, $minId),
                    'minId' => $minId
                ])
                ->setMaxResults(1)
                ->getQuery()
                ->getOneOrNullResult();

            $id = $randItem->getId();
            if (in_array($id, $results)) continue;

            array_push($results, $id);

            if (count($results) >= $randNum) break;
        }

        throw new Success(['data' => $results]);
    }

    // ...
}

To be continue