sql 2 查询基础

2019-03-31

sql 2 查询基础

查询

列的查询

通过select语句查询并选取数据的过程为匹配查询或查询（Query）
格式：select <列名>, ... from <表名>;
注意：select子句指定从表中查询列的名称（列多个用逗号分隔），from子句指定数据表的名称

从表t1查询列t1_id, t1_title和t1_day

1	select t1_id, t1_title, t1_day from t1;

查询表中所有列

格式：select * from <表名>;

select * from t1;

t1_id | t1_name | t1_title | t1_jin | t1_chu |   t1_day
-------+---------+----------+--------+--------+------------
3     | 运动衫  | 衣服     |   5000 |   2333 |
1     | 衬衫    | 衣服     |   1000 |    500 | 2019-09-20
2     | 打孔器  | 办公用品 |    500 |    320 | 2019-09-11
4     | 菜刀    | 厨房用品 |     66 |     12 | 2019-09-20
5     | 高压锅  | 厨房用品 |   2222 |    111 | 2019-01-12
6     | 叉子    | 厨房用品 |    555 |      1 | 2019-09-20
7     | 圆珠笔  | 办公用品 |  77777 |        | 2019-03-01

为列设定别名

sql语句中使用as为列设定别名，原列名并没改变
格式：select <列名> as 别名, ... from <表名>;

select t1_id as id,t1_name as name, t1_title as title from t1;

id | name | title
----+------+-------

别名可以使用汉字，用双引号括起来

select t1_id as "编号", t1_name as "名字", t1_title as "题目" from t1;

编号 | 名字 | 题目
------+------+------

常数的查询

select语句不仅可以写列，也可以写常数（整数，字符串，日期）

查询t1表中列aaa是商品列bbb是38 列ccc是2019-02-01 和t1_id t1_title出来

1	select "商品" as aaa, 38 as bbb, "2019-02-01" as ccc, t1_id, t1_title from t1;

从结果中删除重复的行

格式：select distinct <列名>... from <表名>;
注意：null也会一类数据，去重只保留一行

去t1_name列中重复的内容

select distinct t1_title from t1; 

t1_title
----------
衣服
办公用品
厨房用品

distinct去重多列时，两字段必须完全相同才会去重。

select distinct t1_title,t1_day from t1; 

t1_title |   t1_day
----------+------------
办公用品 | 2019-03-01
衣服     | 2019-09-20
衣服     |
厨房用品 | 2019-01-12
办公用品 | 2019-09-11
厨房用品 | 2019-09-20

可使用group by子句去重参考文章 https://www.jianshu.com/p/7d26008f97f4

根据where语句来选择记录

select语句可以通过where子句来指定查询的条件
格式：select <列名>, ... from <表名> where <条件表达式>;

查询t1_title值为衣服且列为t1_title和t1_name的行

select t1_title, t1_name from t1 where t1_title='衣服'; 

t1_title | t1_name
----------+---------
衣服     | 运动衫
衣服     | 衬衫

流程：

1）先选取符合条件的行
2）在选取对应的列

注释

注释对sql执行没有任何影响，可以使用汉语和英语，在sql任何行中都可以插入

单行注释

格式：-- 内容

多行注释

格式： /* 内容 */

算术运算符

加减乘除 + - * /
注意：表达式中可用括号提高优先度，所有包含null运算结果都为null

查询出列t1_chu的值的两倍并用t1_chu x2显示出来

select t1_name,t1_chu * 2 as "t1_chu x2" from t1;

t1_name | t1_chu x2
---------+-----------
运动衫  |      4666
衬衫    |      1000
打孔器  |       640
菜刀    |        24
高压锅  |       222
叉子    |         2
圆珠笔  |

比较运算符

比较运算符可以对字符，数字和日期等数据类型的列和值进行比较

含义	符号
等于	=
不等于	<>
大于	>
小于	<
大于等于	>=
小于等于	<=
为空	is null
不为空	is not null

注意：不等于规范使用<> 个别DBMS使用!= ，字符串比较大小先比较第一位再比较第二位

查询出t1_jin是500的记录

select t1_name,t1_title from t1 where t1_jin = 500;

t1_name | t1_title
---------+----------
打孔器  | 办公用品

查询出t1_jin不是500的记录

select t1_name,t1_title from t1 where t1_jin <> 500;

t1_name | t1_title
---------+----------
运动衫  | 衣服
衬衫    | 衣服
菜刀    | 厨房用品
高压锅  | 厨房用品
叉子    | 厨房用品
圆珠笔  | 办公用品

查询出日期2019-05-01之前的记录

select t1_name,t1_day from t1 where t1_day < '2019-05-01';

t1_name |   t1_day
---------+------------
高压锅  | 2019-01-12
圆珠笔  | 2019-03-01

查询出t1_jin比t1_chu大于等于500的记录

select t1_name, t1_jin, t1_chu from t1 where t1_jin - t1_chu >= 500;

t1_name | t1_jin | t1_chu
---------+--------+--------
运动衫  |   5000 |   2333
衬衫    |   1000 |    500
高压锅  |   2222 |    111
叉子    |    555 |      1

查询t1_chu为空的记录

select t1_name, t1_chu from t1 where t1_chu is null;

t1_name | t1_chu
---------+--------
圆珠笔  |

逻辑运算符

not 非 and 与 or 或

查询t1_chu非大于等于500（小于500）的记录

select t1_name, t1_chu from t1 where not t1_chu >=500;

t1_name | t1_chu
---------+--------
打孔器  |    320
菜刀    |     12
高压锅  |    111
叉子    |      1

查询出t1_title是厨房用品和t1_chu大于200的记录

select t1_name, t1_title, t1_chu from t1 where t1_title='厨房用品' and t1_chu >100;

t1_name | t1_title | t1_chu
---------+----------+--------
高压锅  | 厨房用品 |    111

查询出t1_title是厨房用品或者t1_chu大于200的记录

select t1_name, t1_title, t1_chu from t1 where t1_title='厨房用品' or t1_chu >100;

t1_name | t1_title | t1_chu
---------+----------+--------
运动衫  | 衣服     |   2333
衬衫    | 衣服     |    500
打孔器  | 办公用品 |    320
菜刀    | 厨房用品 |     12
高压锅  | 厨房用品 |    111
叉子    | 厨房用品 |      1

查询出是办公用品并且日期是2019-09-20或者2019-09-11的记录

select t1_name, t1_title, t1_day from t1 where t1_title ='办公用品' and (t1_day ='2019-09-20' or t1_day ='2019-09-11');

t1_name | t1_title |   t1_day
---------+----------+------------
打孔器  | 办公用品 | 2019-09-11

聚合查询

聚合：把多行汇聚成一行

聚合函数	对表中的数据进行某种操作或者计算时使用的函数
count	计算表中记录数（行数）
sum	计算表中数值列的数据合计值
avg	计算表中数值列的数据平均值
max	求出表中任意列中数据的最大值
min	求出表中任意列中数据的最小值

count(*) 计算总行数（包括null行）

查询t1中总行数

select count(*) from t1;  #*表示所有列

count
-------
     7

count(列名) 计算某列的非空的行数（不包括null行）

查询t1_day列中非空的总行数

select count(t1_day) from t1;

count
-------
     6

sum(列名) 计算某列的合计（只适用用数字类型，不包括null行）

计算t1_jin和t1_chu的合计

select sum(t1_jin), sum(t1_chu) from t1;

  sum  | sum
-------+------
87120 | 3277

avg(列名) 计算某列的平均值（只适用用数字类型，不包括null行）

计算t1_jin和t1_chu的平均值

select avg(t1_jin), avg(t1_chu) from t1;

        avg         |         avg
--------------------+----------------------
12445.714285714286 | 546.1666666666666667

max(列名) 计算某列的最大值（适合任何类型）

min(列名) 计算某列的最小值（适合任何类型）

计算t1_jin的最大值和最小值

select max(t1_jin), min(t1_jin) from t1;

  max  | min
-------+-----
77777 |  66

先去重再使用聚合函数计算

select count(distinct t1_title) from t1;

count
-------
     3

对表进行分组

group by子句可以让列把一样的值分为一组，还可以结合聚合函数一起使用。group by 指定的列称为聚合键
格式：select <列名1>, ... from <表名> group by <列名1>, ...;

按照日期分组并统计次数（null值也会统计）

select t1_day, count(*) from t1 group by t1_day;

   t1_day   | count
------------+-------
            |     1
2019-09-20 |     3
2019-09-11 |     1
2019-03-01 |     1
2019-01-12 |     1

当select语句中有where子句，group by子句要写在其后面，执行顺序为 from — where — group by — select

格式：select <列名1>, ... from <表名> where <条件表达式> group by <列名1>, ...;

查询出t1_title是办公用品并按照t1_jin分组的记录

select t1_jin,count(*) from t1 where t1_title = '办公用品' group by t1_jin;

t1_jin | count
--------+-------
    500 |     2
  77777 |     1

group by注意事项
1）使用group by子句，select子句不能出现在聚合链以为的列名

select t1_name,t1_jin,count(*) from t1 where t1_title = '办公用品' gr
oup by t1_jin;
ERROR:  column "t1.t1_name" must appear in the GROUP BY clause or be used in an
aggregate function

2）group by子句不能使用select子句定义的别名，原因是先执行group by子句不识别别名

1
2
3

select t1_jin as sb,count(*) from t1 where t1_title = '办公用品' grou
p by t1_jin as sb;
ERROR:  syntax error at or near "as"

3）group by子句结果是随机的
4）只有select子句，having子句和order by子句才可以使用聚合函数

1 2	select t1_jin,count() from t1 where count() =2 group by t1_jin; ERROR: aggregate functions are not allowed in WHERE

为聚合结果指定条件

having子句用来指定分组的条件
where子句是数据行的条件
having子句写在group by子句之后
格式：select <列名1>, ... from <表名> where <条件表达式> group by <列名1>, ... having <条件表达式>;

查询出列t1_title分组统计行数后是3的记录

select t1_title, count(*) from t1 group by t1_title having count(*) = 3;

t1_title | count
----------+-------
办公用品 |     3
厨房用品 |     3

查询出t1_title分组求t1_jin平均值后小于1000的记录

select t1_title, avg(t1_jin) from t1 group by t1_title having avg(t1_jin) < 1000;

t1_title |         avg
----------+----------------------
厨房用品 | 947.6666666666666667

注意：使用having子句只能出现三种元素：常数 聚合函数 group by指定的列名（聚合键）

由于where子句是指定行的条件，having子句指定分组的条件。当出现某个条件放在where或者having结果都是一样时，默认应该把聚合键所对应的条件写到where上

select t1_title, count(*) from t1 where t1_title = '衣服' group by t1_title;

t1_title | count
----------+-------
衣服     |     2

select t1_title, count(*) from t1 group by t1_title having t1_title ='衣服';

t1_title | count
----------+-------
衣服     |     2

对查询结果进行排序

由于select子句查询结果是随机排序的
因此使用order by子句可以对查询结果进行排序，asc表升序 desc表降序。
order by子句是写在select语句中的末尾。
格式：select <列名1>, ... from <表名> order by <排序基准列1> ... ;

按照t1_jin升序排序

select t1_id,t1_name,t1_jin from t1 order by t1_jin;

t1_id | t1_name | t1_jin
-------+---------+--------
4     | 菜刀    |     66
2     | 打孔器  |    500
8     | 书本    |    500
6     | 叉子    |    555
1     | 衬衫    |   1000
5     | 高压锅  |   2222
3     | 运动衫  |   5000
7     | 圆珠笔  |  77777

按照t1_jin降序排序

select t1_id,t1_name,t1_jin from t1 order by t1_jin desc;

t1_id | t1_name | t1_jin
-------+---------+--------
7     | 圆珠笔  |  77777
3     | 运动衫  |   5000
5     | 高压锅  |   2222
1     | 衬衫    |   1000
6     | 叉子    |    555
2     | 打孔器  |    500
8     | 书本    |    500
4     | 菜刀    |     66

按照t1_jin升序排序，但值一样时按照t1_id降序来排序

select t1_id,t1_name,t1_jin from t1 order by t1_jin, t1_id desc;

t1_id | t1_name | t1_jin
-------+---------+--------
4     | 菜刀    |     66
8     | 书本    |    500
2     | 打孔器  |    500
6     | 叉子    |    555
1     | 衬衫    |   1000
5     | 高压锅  |   2222
3     | 运动衫  |   5000
7     | 圆珠笔  |  77777

order by子句中可以使用别名，原因是select语句的执行顺序为 from — where — group by — having — select — order by

select t1_id as id,t1_name,t1_jin as sb from t1 order by sb, id desc;

  id  | t1_name |  sb
------+---------+-------
4    | 菜刀    |    66
8    | 书本    |   500
2    | 打孔器  |   500
6    | 叉子    |   555
1    | 衬衫    |  1000
5    | 高压锅  |  2222
3    | 运动衫  |  5000
7    | 圆珠笔  | 77777

order by子句可以使用select子句中为使用的列或者聚合函数

以t1_chu排序，t1_chu没在 select子句中使用

select t1_id,t1_name,t1_jin from t1 order by t1_chu;

t1_id | t1_name | t1_jin
-------+---------+--------
6     | 叉子    |    555
4     | 菜刀    |     66
5     | 高压锅  |   2222
2     | 打孔器  |    500
1     | 衬衫    |   1000
3     | 运动衫  |   5000
7     | 圆珠笔  |  77777
8     | 书本    |    500

以聚合函数count降序排序

select t1_title,count(*) from t1 group by t1_title order by count(*) desc;

t1_title | count
----------+-------
办公用品 |     3
厨房用品 |     3
衣服     |     2