随便玩玩之PostgreSQL（第二章）单表数据查询

随便玩玩之PostgreSQL（第二章）单表数据查询

未经授权不得转载

第二章 单表数据查询
数据库的基本功能就是数据增查改删，倘若不可以，要她还有什么意义。数据查询功能不仅仅是查询，而且还能筛选，并且格式化显示。
数据查询使用SELECT语句，本章介绍如何使用SELECT查询（列出）数据库中的数据。

---

2.1查询数据库cj表中全部信息
方法一：使用“*”通配符查询所有数据（所有字段数据）。

SELECT * FROM cj;

结果如图。

方法二：制定所有字段（列名）。每个字段用逗号（,）隔开。

SELECT id,kc,bj,xm,yw,sx,yy FROM cj;

结果如图。

---

2.2查询数据库中指定字段信息（只显示出想要的列）。
只要是表内的字段就可以，重复也可以，字段数量最多1664个。

SELECT xm,yw FROM cj;

结果列出所有学生的姓名和语文成绩。

---

2.4查询指定记录（只显示想要的行）。
数据库的信息本来就包含了各种信息，如cj表中包括一班、二班、三班的每一个学生的考试成绩。倘若查询一班所有人的成绩，可以查出所有人的成绩，然后在EXCEL中删掉二班、三班的。数据量少的时候可以这样，数据量多的时候速度就会下降。不过，我们可以通过限制查询条件进行查询，按条件查询（列出）数据。

SELECT * FROM cj WHERE bj='一班';

结果如图。

 语句中的等号（=）表示只查询bj字段为一班的学生的成绩。等号为判断符，其他判断符为<>（或者!=，表示不等于），<（表示左边数值小于右边数值）,<=（表示小于等于右边数值）,>（表示大于右边数值），>=（表示大于等于右边数值）,BETWEEN AND（表示介于两个数值之间）。
如：

--查询语文成绩大于60的学生。
SELECT * FROM cj WHERE yw > 60;

--查询语文成绩在60到69之间的学生。
SELECT id,bj,xm,yw FROM cj WHERE yw BETWEEN 60 AND 69;
--或者
SELECT id,bj,xm,yw FROM cj WHERE yw>=60 AND yw<69; --结果与第四行语句相同。

--查询语文成绩不是60到69的学生。
SELECT * FROM cj WHERE yw NOT BETWEEN 60 AND 69;
--或者
SELECT * FROM cj WHERE yw>69 OR yw <60;

--查询英语成绩等60的学生。
SELECT * FROM cj WHERE yy=60；

--查询英语成绩等于60且必须等于70的学生（无意义，没结果且不合逻辑）。
SELECT * FROM cj WHERE yy=60 AND yy=70;

--查询张三的成绩。
SELECT * FROM cj WHERE xm=’张三’;

--查询张三其中考试的成绩。
SELECT * FROM cj WHERE xm='张三' AND kc='期中考试';

--查询语文成绩大于80和（或）小于65的学生。
SELECT * FROM cj WHERE yw > 80 OR yw<65;

 

2.4多条件查询
多条件查询使用AND或OR操作符，可以指定2个及以上的条件。

AND操作符限定满足所有条件才会有记录返回。
如：查询期中考试三班语文成绩大于80的学生。

SELECT * FROM cj WHERE kc='期中考试' AND bj='三班' AND yw>80;

OR操作符只需所有条件中满足一个条件即可。
如：查询语文成绩大于80或一班的学生。

SELECT * FROM cj WHERE yw>80 OR bj='一班';

用IN操作符实现OR操作符功能。
如：查询英语成绩等于60和（或）70的学生。

--OR操作符
SELECT * FROM cj WHERE yy=60 OR yy=70;
--IN操作符
SELECT * FROM cj WHERE yy in (60,70)；

---

 2.5 去掉查询结果中的重复记录。
cj表中共有18个学生的信息，每一个信息都有一个班级。想要从表中得到有多少个班级，只有查询出所有班级字段，然后去掉重复的，只保留一个，即可得到班级数量。使用DISTINCT关键字即可去掉重复记录，只保留一个记录值。

SELECT DISTINCT bj FROM cj;

结果如图。

结果把所有的班级只列出一个，只需要数一数有多少行即可得出有三个班级。

也有不想数行的方法，使用count()函数即可实现。

SELECT  count(DISTINCT bj)  FROM cj;

结果如图。

---

2.6对查询结果排序
默认查询结果没有排序，有可能每次的查询结果顺序都不一样。使用ORDER BY子句可以对结果进行排序。
如：从低到高列出所有学生的语文成绩。

SELECT xm,yw,bj,kc FROM cj ORDER BY yw;

结果如图。


如：先分期中考试、期末考试，在从低到高列出所有学生语文成绩。

SELECT kc,yw,bj,xm FROM cj ORDER BY kc,yw;

结果如图。

默认排序为1-9，A-Z，逆序排列只需要在字段后加DESC关键字即可，DESC约束其前的字段，如果对所有排序条件逆序，需在所有关键字后加DESC。
如：列出从高到低列出所有学生期中考试语文成绩。

SELECT kc,yw,bj,xm FROM cj WHERE kc='期中考试' ORDER BY kc,yw DESC;

结果如图。

---

2.7查询结果分组
分组排序使用GROUP BY子句。
但是分组之后组字段显示的值是什么？做班级成绩汇报时，一般说一班有多少个人，语文平均分是多少，语文最高分是多少，最低分是多少，不会说出每个人的成绩，也不会随便说一个人的成绩。所以分组之后显示的是计算的结果，计算使用聚合函数。常用的聚合函数有：计数量count()，求最大值max()，求最小值min()，计总和sum()，求平均avg()。
如：计算出各班的期中考试平均。

SELECT bj,avg(yw),avg(sx),avg(yy) FROM cj WHERE kc='期中考试' GROUP BY bj;

结果如图。

小数点太多？可以用round()函数截取。

SELECT bj,round(avg(yw),2),avg(sx),avg(yy) FROM cj WHERE kc='期中考试' GROUP BY bj;

结果如图。

一行的SQL语句太长？可以分行写SQL语句，只要在最后有一个分号就可以。

SELECT     bj,
         round(avg(yw),2),
         avg(sx),avg(yy)
    FROM cj
    WHERE kc='期中考试'
    GROUP BY bj;

结果如图。

语句换行不影响查询结果。

表头都是AVG，不美观？使用AS关键字设置别名即可。

SELECT     bj AS "班级",
         round(avg(yw),2) AS "语文平均分",
         avg(sx)  AS "数学平均分",
         avg(yy)  AS "英语平均分"
    FROM cj
    WHERE kc='期中考试'
    GROUP BY bj;

结果如图。

AS的功能是给字段起一个别名。

数据没有排序，看起来不方便？分组之后再排序。

SELECT     bj AS "班级",
         round(avg(yw),2) AS "语文平均分",
         avg(sx)  AS "数学平均分",
         avg(yy)  AS "英语平均分"
    FROM cj
    WHERE kc='期中考试'
    GROUP BY bj
    ORDER BY avg(yw);

结果如图。

注：最后的ORDER BY指定的不是字段本身，而是使用聚合函数计算后的字段。已经给avg(yw)设置了别名，那么ORDER BY 能使用别名作为排序条件吗？当然可以。

SELECT     bj AS "班级",
         round(avg(yw),2) AS "语文平均分",
         avg(sx)  AS "数学平均分",
         avg(yy)  AS "英语平均分"
    FROM cj
    WHERE kc='期中考试'
    GROUP BY bj
    ORDER BY "语文平均分";

结果如图。

---

 

2.8查询结果分组排序后再筛选
使用WHERE关键字可以筛选过滤数据，但是WHERE的筛选只能是指定选择记录的条件，请记住：WHERE关键字是用在记录上的。对于分组之后的组有该如何筛选？HAVING关键字用来筛选分组之后的数据。
如：筛选出期中考试语文平均分大于70的班级。

SELECT   bj AS "班级",
             round(avg(yw),2) AS "语文平均分",
             avg(sx)  AS "数学平均分",
             avg(yy)  AS "英语平均分"
    FROM cj
    WHERE kc='期中考试'
    GROUP BY bj
    HAVING avg(yw)>70;        

结果如图。

---

2.9限制查询结果数量。
SELECT将返回所有匹配的行，可能是表中的所有行，如仅仅需要返回第一行或前几行，使用LIMIT关键字。
如：查询cj前3行的数据。

SELECT * FROM cj LIMIT 3;

结果如图。

也可以查询从第4行开始的3条记录。

SELECT * FROM cj LIMIT 3 OFFSET 4;

结果如图。

数据库本身的记录（行）从0开始计算，第4行应理解为数据库的第5个记录。

---

2.10 阶段性总结示例:使用聚合函数实现分类汇总
count()函数计算表内行的总数。方法有两种：count(*)计算表内行的总数，包括空值。Count(字段名)计算指定列行的总数，忽略空值。
sum()函数计算一个列的所有记录累计总和。
avg()函数计算一个列的所有记录的平均值。
Max()函数返回一个列的所有记录中的最大值。
Min()函数返回一个列的所有记录中的最小值。
如：分类汇总功能。

SELECT   bj                          AS "班级"         ,
         count(xm)                   AS "人数"         ,
         round(sum(yw)/count(xm),2)  AS "语文"         ,
      -- round(avg(yw),2)            AS "语文"         ,
         max(yw)                     AS "语文最高分"    ,
         min(yw)                     AS "语文最低分"    ,
         round(avg(sx),2)            AS "数学平均分"    ,
         max(sx)                     AS "数学最高分"    ,
         min(sx)                     AS "数学最低分"    ,
         round(avg(yy),2)            AS "英语平均分"    ,
         max(yy)                     AS "英语最高分"    ,
         min(yy)                     AS "英语最低分"
    FROM cj
    WHERE kc = '期中考试'
    GROUP BY bj
    ORDER BY avg(yw);

结果如图。

使用方法非常灵活，一条语句搞定。

---

 

 2.11子查询
子查询是把一个查询嵌套在另一个查询中。子查询又叫内部查询，相对于内部查询，包含着子查询的叫外部查询。
子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之一：select、insert、update、delete、set或 者do。
子查询可以在SELECT后，FROM后，WHERE后，GROUP BY后和ORDER BY后。
根据返回值类型，子查询分为标量子查询（单一值子查询，返回一个值）、列子查询（返回一列值）、行子查询（返回一行值）、表子查询（返回一个表的值），
可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS 。
2.11.1标量子查询
标量子查询返回一个值，可以与外部查询使用=、>、<、>=、<=和<>符号进行比较判断，如果子查询返回的不是一个标量值，而外部查询使用了比较符和子查询的结果进行了比较，那么就会抛出异常。
如：查询期中考试语文成绩高于张三的学生。

SELECT * 
    FROM cj 
    WHERE yw > 
        (SELECT yw FROM cj WHERE kc='期中考试' AND xm ='张三') 
        AND kc='期中考试';

结果如图:

得到的结果是把期中考试语文成绩高于张三的学生全部列出来，最后的AND kc='期中考试'判断条件是将结果限定在期中考试内，去掉这一句会列出期中考试和期末考试成绩。

其执行循序为：先执行(SELECT yw FROM cj WHERE kc='期中考试' AND xm = '张三')，得到张三的期中考试语文成绩，为64，再和yw>组成判断表达式yw>64；yw>64和AND kc='期中考试'组成逻辑与表达式（两个结果都为真），最后执行外部语句（整个语句）。

关于子查询请记住，语句里有括号，则先执行括号内部查询（子查询），得到结果，作为外部查询的一部分，再执行部查询。

也可以把子查询放在SELECT后。

如：查询张三的期中考试和期末考试语文成绩，以及两者之间的差距。

SELECT  xm    AS "姓名" ,
        yw    AS "期中考试" ,
        (SELECT yw FROM cj WHERE kc='期末考试' AND xm='张三')       AS "期末考试" ,
        yw - (SELECT yw FROM cj WHERE kc='期末考试' AND xm='张三')  AS "差距"
    FROM   cj
    WHERE  kc='期中考试' AND xm='张三';

结果如下：

如上所释，先执行括号内语句（子查询），结果作为外部语句（整个语句）组成部分，再执行外部语句。

2.11.2列子查询

列子查询返回一个列的数据，可以使用 = > < >= <= <> 这些操作符对子查询的结果进行比较，通常子查询的位置在比较式的右侧。可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。

ALL操作符是所有、全部的意思。只有全部符合条件，外部语句才有结果。例如，>(1,2,3)表示必须大于3。(注：NOT IN 与<>ALL等效)

如：查询语文成绩比数学最高分还高的同学。

SELECT
       xm     AS "姓名" ,
       yw     AS "语文成绩" ,
       (SELECT max(sx) FROM cj)   AS "全部学生数学最高分"
    FROM cj
    WHERE yw > ALL (SELECT sx FROM cj);   --左边大于右边最大的

结果如图。

ANY操作符是任意一个的意思。满足任何一个即可。例如：>ANY(1,2,3)表示大于1即可。

SOME操作符与ANY相同。

如：查询数学成绩比语文最低分高的学生。

SELECT  xm  AS "姓名",
        sx  AS "数学",
        (SELECT min(yw) FROM cj)  AS "语文最低分"
    FROM cj
    WHERE sx > ANY (SELECT yw FROM cj)
    ORDER BY sx DESC;

结果如图。

当判断对象中一个是另一个的子集时，ANY与IN等效，结果一样。

 2.11.3行子查询

如：查询表内信息是否在另一个表中完全一致。

SELECT * 
    FROM (SELECT * FROM cj WHERE id = 5) id5  --FROM后的子查询得到一行数据
    WHERE (id,kc,bj,xm,yw,sx,yy) IN (SELECT * FROM cj);

结果如图。

2.11.4表子查询

结果得到一个表。

如：查询所有学生的期中考试成绩。

SELECT * 
    FROM (SELECT * FROM cj WHERE kc='期中考试') kc_qz;
-- 和 SELECT * FROM cj WHERE kc='期中考试'; 语句的执行结果相同，不同的是查询的表不一样。

结果如下。

代码内有注释。结果相同，查询的表不相同。行3的语句是直接从cj表内查询；行2的语句是执行一个查询，得到的结果为表。

注：给表起别名只需在表后加空格和名称。

 2.11.5 阶段性总结示例：利用子查询求班级平均分差距。

SELECT kc_qz.bj    AS "期中班级"                          ,
       kc_qm.bj    AS "期末班级"                          , 
    -- 增加班级一列（kc_qm.bj），用来识别班级，无特殊意义
       round(kc_qz.yw1,2)                AS "期中语文"    ,
       round(kc_qm.yw2,2)                AS "期末语文"    , 
       round(kc_qm.yw2-kc_qz.yw1,2)      AS "语文差距"    , 
    -- 分数差距，正数表示期末考试相对期中考试有提升，负数表示下降
       round(kc_qz.sx1,2)                AS "期中数学"    ,
       round(kc_qm.sx2,2)                AS "期末数学"    ,
       round(kc_qm.sx2 - kc_qz.sx1,2)    AS "数学差距"    ,
       round(kc_qz.yy1,2)                AS "期中英语"    ,
       round(kc_qm.yy2,2)                AS "期末英语"    ,
       round(kc_qm.yy2 - kc_qz.yy1,2)    AS "英语差距"
    FROM
        (SELECT bj,
                avg(yw) AS "yw1",
                avg(sx) AS "sx1",
                avg(yy) AS "yy1"
            FROM cj
            WHERE kc='期中考试'
            GROUP BY bj) kc_qz,
        (SELECT bj,
               avg(yw) AS "yw2",
               avg(sx) AS "sx2",
               avg(yy) AS "yy2"
            FROM cj
            WHERE kc='期末考试'
            GROUP BY bj) kc_qm
    WHERE  kc_qz.bj = kc_qm.bj      -- 表连接查询，下一章内容
    ORDER BY kc_qz.bj;

结果如图。

看起来是30行，实际上只是一条语句。

 

至此，第二章结束，简单介绍了单表查询，语句都很简单，但是很神奇。