sql基础及应用

准备

使用客户端工具pgadmin4登录。 用户名 feidao@ifeidao.com 密码 feidao

1. 添加新的服务器

   General(通常) -> Name(名称) -> 测试 Connection -> Host name/address(主机名称/地址) -> 172.16.12.2 Connection -> Port(端口) -> 5432 Connection -> Maintenance database(维护数据库) -> postgres Connection -> Username(用户名) -> feidao Connection -> Password(密码) -> feidao

   最后点击保存即可。

2. 打开数据库

   点击左侧导航树 (Servers) 下刚才新建的数据库连接，名字叫测试。分别展开Databases->feidao->Schemas -> public -> Tables。这里将会列出所有该库中创建的表，但现在因为还没有添加表，所以现在看不到任何表。

数据操作（准备）

1. 创建表

   打开菜单Tools->Query Tool.然后使用以下脚本创建表。

    create table star(
        _id character varying(50) not null,
        name character varying(50) not null,
        sex numeric(1,0)
    ) with (
        oids=false
    );
   

   然后按下F5执行这段脚本，在左侧导航树的Tables下，现在应该就会看到有一个表，名字叫star，即为我们刚才创建的一个表。

2. 插入一条数据记录

   使用以下脚本添加一条数据记录：

    insert into star(_id, name, sex) values ('1', 'John', 1);
   

3. 删除数据记录

    delete from star as t1 where t1.name = 'John';
   

4. 删除表

    drop table star;
   

   这时候左侧导航树可能不会自动刷新，可以手动在Tables上右键选择Refresh刷新即可看到，刚才创建的表已经没有了。

5. 插入一些必要的测试数据。

    create table star(
        _id character varying(50) not null,
        name character varying(50) not null,
        sex numeric(1,0)
    ) with (
        oids=false
    );
    insert into star(_id, name, sex) values ('1', 'john', 1),('2', 'jack', 1),('3', 'jean', 1);
   
    create table fruit_price (
        _id character varying(50) not null,
        name character varying(50) not null,
        price numeric(50,2)
    ) with (
        oids=false
    );
    insert into fruit_price(_id, name, price) values ('1', 'apple', 1.00),('2', 'banana', 3.00),('3', 'tomato', 0.00);
   
    create table star_hobby (
        _id character varying(50) not null,
        star character varying(50) not null,
        hobby character varying(5000) not null
    ) with (
        oids=false
    );
   
    insert into star_hobby(_id, star, hobby) values ('1', '1', 'eat, drink, smoke'),('2', '2', 'sleep'),('3', '3', 'eat, drink, smoke, sleep');
   
    create table star_works (
        _id character varying(50) not null,
        star character varying(50) not null,
        works character varying(5000) not null
    ) with (
        oids=false
    );
   
    insert into star_works(_id, star, works) values ('1', '1', 'smoke in the night'),('2', '2', 'sleep in the day'),('3', '3', 'smoke and sleep day and night');
   

数据插入

上面已经提到了一些简单的数据操作，现在我们依照以前的一篇数据操作的文档来进行sql的操作介绍

1. 增加数据（单条记录）

    insert into star (_id, name, sex) values('0ea5', '悟能', 1);
    insert into fruit_price (_id, name, price) values('4', '番茄', 0.5);
    insert into star_hobby(_id, star, hobby) values('4','0ea5','吃和睡');
   

2. 增加数据（多条记录）

    insert into star_works(_id, star, works) values('4','0ea5','九吃钉耙'),('5','0ea5','嫦娥仙子');
   

数据编辑

比如在我们初始化的数据中，有一个明星名字叫jean的，她的姓名为男，我们发现了这个错误，所以我们要将名字为jean的这条数据的性别sex修改为女（0）

update star set sex = 0 where name='jean';

简单查询

1. 简单查询

    select _id, name, price from fruit_price as t1;
   

2. 别名

    select t1._id as id, t1.name as fruit_name, t1.price as fruit_price from fruit_price as t1;
   

   使用别名是一个好习惯,特别是多表查询的时候.

3. 带排序的查询

    select t1._id as id, t1.name as fruit_name, t1.price as fruit_price from fruit_price as t1 order by t1.price;
   

注意： order by默认为升序(asc)，如果要使用降序排序，要使用desc：

select t1._id as id, t1.name as fruit_name, t1.price as fruit_price from fruit_price as t1 order by t1.price desc;

分页查询

因为我们现在的测试数据不够多，所以我们假定一页只显示2条数据(page_size=2)，当前为第二页(page_no=2).这时我们要用到两个postgre数据库中两个关键字：limit和offset。

limit

表示最多返回多少条数据记录，后面一般跟一个正整数。我们这里应该为2

select t1._id as id, t1.name as fruit_name, t1.price as fruit_price from fruit_price as t1 order by t1.price limit 2;

以上脚本执行的结果为:

offset

表示返回数据时从前面后偏移多少条记录。我们对上面的查询添加上offset参数之后

select t1._id as id, t1.name as fruit_name, t1.price as fruit_price from fruit_price as t1 order by t1.price offset 2;

查询出来的结果为：

分页

从上面的解释中不难看出，我们如果想查询出来第2页（每页2条数据），limit的值应为2，即page_size的值。而offset应为多少呢，它的计算公式应为

(page_no - 1 ) * page_size

将参数代入以上公式，即可得出offset的值为(2 - 1) * 2 = 2;

所以我们要进行的查询的sql如下:

select t1._id as id, t1.name as fruit_name, t1.price as fruit_price from fruit_price as t1 order by t1.price limit 2 offset 2;

函数

我们通常用的函数有count,sum,avg,max,min.

比如我们通常用的获取数据记录的总数，可以使用以语句：

select count(*) from fruit_price as t1;

其它几个函数使用方法与count类似，有些在具体使用时甚至要搭配group by。这里先不介绍了。

唯一值

有时候我们可能希望过滤掉某个表里的重复的值，比如我们要查一下作品表star_works中不重复的明星的唯一标识(star)们。

select distinct t1.star as star_id from star_works as t1;

甚至计算一下它的数据总条数。

select count(distinct t1.star) as stars from star_works as t1;

子查询

子查询就是将查询出来的结果当作另外的一条表再次使用它进行查询，一般前面会跟in关键字。

比如我们想查询所有男明星的作品

select t1.works as 作品 from star_works t1 where t1.star in (select t2._id from star as t2 where t2.sex = 1);

多表查询

接上例，假如说我们需要同时知道明星的名字，可以使用以下查询

select t1.works as 作品, t3.name as 明星 from star_works t1, star t3 where t1.star in (select t2._id from star as t2 where t2.sex = 1) and t1.star=t3._id;

而事实上你可能会发现，这时我们其实可以不使用子查询了

select t1.works as 作品, t2.name as 明星 from star_works t1, star t2 where t2.sex = 1 and t1.star=t2._id;

它与上面的结果是一样的，不是吗。并且，我们从语法上分析也是对的。分析一下有到底有什么不同。

事实上，这时的多表查询其实就是一个内连接查询。下面我们会讲到。

连接(join)

join 子句用于把来多个表的行连接起来成为一个表（查询结果）。

为了能够将连接部分讲得更清楚一些，我们需要再添加2条数据。

insert into star (_id, name, sex) values('5', '三藏', 1);
insert into star_works(_id, star, works) values('6', '5', '取经');

这2条数据以前面的有什么不同呢？这位明星三藏他有作品(取经)，但是没有爱好。

内连接(inner join)

inner join只连接匹配的行，不匹配的结果不会在结果集中出现。

接下来我们来进行一个内连接查询

select t1.name, t2.works from star as t1 inner join star_works t2 on t2.star=t1._id;

而inner关键字是可以省略的

select t1.name, t2.works from star as t1 join star_works t2 on t2.star=t1._id;

细观察你可能就会发现了，这个结果其实就是我们在前面提到的多表关联查询。

select t1.name, t2.works from star as t1, star_works t2 where t2.star=t1._id;

这时如果我们需要查询数据总条数，也是可以的

select count(*) from star as t1, star_works t2 where t2.star=t1._id;

接着我们再来查一下明星们的爱好（注意三藏这位明星）：

select t1.name, t2.hobby from star as t1, star_hobby t2 where t2.star=t1._id;

仔细查询查询结果就会发现，结果中没有三藏这位明星相关的数据。因为三藏大师并没有其它的爱好，所以使用内连接的时候，查询结果里不会出现。如果想要这些数据怎么办呢？答案是使用外连接。

注意：在使用count的时候请使用count(*)而不是count(t1.name)这样的方式。它们的区别是，使用某个字段进行计数的时候，它会排除该字段为null的情况，这通常不是我们想要的，如果要排除空值，你完全可以在where条件里添加。

外连接(outer join)

左外连接(left outer join)

左连接返回左边表中的全部记录，右边表中如果没有与之关联的记录，则查询结果为null。

因为内连接没有能把三藏大师查出来，而我们有时候确实希望能查出来，怎么办呢？看以下语句：

select t1.name, t2.hobby from star as t1 left outer join star_hobby t2 on t2.star=t1._id;

outer关键字可以省略

select t1.name, t2.hobby from star as t1 left join star_hobby t2 on t2.star=t1._id;

结果为

可以看到，上面查出来的结果当中,name为三藏这条数据的hobby字段值为null

右外连接(right outer join)

右连接与左连接相反，右连接返回右边表中的全部记录，左边表中如果没有与之关联的记录，则查询结果为null。所以，上面左连接的例子调整一下表的次序，将left修改为right。结果完全一样。

select t1.name, t2.hobby from star as t1 right join star_hobby t2 on t2.star=t1._id;
select t1.name, t2.hobby from star as t1 right outer join star_hobby t2 on t2.star=t1._id;

完整连接(full outer join)

完整连接其实就是综合左连接和右连接，将两边不存在的数据也都查出来。结果显示为null，但是这种情形不是很常用（如果你能够掌握左连接和右连接，这种非常容易理解），不作详细介绍。

分组(group by)

分组是将查询的结果进行分组聚合，在一些使用场景中非常方便。

比如我们需要根据名字进行分组，统计一下明星们的作品数量。可以使用以下查询：

select t1.name, count(t2.works) as c from star as t1 join star_works t2 on t2.star=t1._id group by t1.name order by c desc;

如果进行分页查询

select t1.name, count(t2.works) as c from star as t1 join star_works t2 on t2.star=t1._id group by t1.name order by c limit 3 offset 2

要查总数据条数

select count(*) from (select t1.name, count(t2.works) as c from star as t1 join star_works t2 on t2.star=t1._id group by t1.name order by c) as t3;

练习： 数字校园项目中，行政党群项目中党费管理页面中用到一些比较复杂的统计，可以试着将党费的表创建到关系型数据库中，使用sql做统计。

提示： 直接在pgadmin查询工具中就可以完成。