今天要带大家看一个非常强大的数据库：PostgreSQL
环境：小白装的是postgreSQL 9.6，用的客户端是pgAdmin 4。
要求：对数据库和sql语言有基本的认识。
本文目录如下：

1、pgAdmin界面

相信不少人有使用MySQL、oracle或是SQLserver的经验，pgAdmin的结构稍微有些不一样。mytest是小白自己建的数据库，通过sql语句建的表默认都在架构下的public下，当然，建的函数、视图、触发器什么的也都在那儿。

点击小闪电，会弹出sql编辑器，可以写SQL语句。

2、表操作

2.1 创建数据表

建表都是通用的sql语句

create table student(
    s_id varchar(10) primary key,
    s_name varchar(20),
    s_age date,
    s_sex varchar(10)
);

create table course(
    c_id varchar(10) primary key,
    c_name varchar(20) not null ,
    t_id varchar(10),
    constraint uni_c_name unique(c_name)
);

create table teacher (
    t_id varchar(10) primary key,
    t_name varchar(20)
);

create table score (
    s_id varchar(10),
    c_id varchar(10),
    score varchar(10),
    primary key(s_id,c_id),
    constraint fk_s_id foreign key(s_id) references student(s_id),
    constraint fk_c_id foreign key(c_id) references course(c_id)
);

2.2 修改表属性

如果有外键关联的表，删除表前需要先删除外键关联

-- 删除外键
alter table score drop constraint fk_s_id;

-- 清空表
truncate table score;
-- 删除表
drop table if exists score;

-- 修改字段类型
alter table score alter column score type varchar(4);
-- 修改字段名
alter table student rename s_age to birth;

2.3 增

增加行（插入值）

insert into student(s_id,s_name,s_age,s_sex)
values('01','赵雷','1990-01-01','男'),
      ('02','钱电','1990-12-21','男'),
      ('03','孙风','1990-05-20','男'),
      ('04','李云','1990-08-06','男'),
      ('05','周梅','1991-12-01','女'),
      ('06','吴兰','1992-03-01','女'),
      ('07','郑竹','1989-07-01','女'),
      ('08','王菊','1990-01-20','女');

insert into course(c_id,c_name,t_id)
values('01','语文','02'),
      ('02','数学','01'),
      ('03','英语','03');

insert into teacher(t_id,t_name)
values('01','张三'),
      ('02','李四'),
      ('03','王五');
      
insert into score(s_id,c_id,score)
values('01','01',80),
      ('01','02',90),
      ('01','03',99),
      ('02','01',70),
      ('02','02',60),
      ('02','03',80),
      ('03','01',80),
      ('03','02',80),
      ('03','03',80),
      ('04','01',50),
      ('04','02',30),
      ('04','03',20),
      ('05','01',76),
      ('05','02',87),
      ('06','01',31),
      ('06','03',34),
      ('07','02',89),
      ('07','03',98);

增加列（在score表中增加一列评分）

alter table score add column evaluate text;
update score set evaluate=(
case 
when score::int >=90 then '优秀'
when (score::int <90)and(score::int >=60) then '及格'
else '不及格' 
end
);

2.4 删

删除行

-- 删行（删除“王菊”这个人的信息）
delete from student where s_name='王菊';

删除列（删掉之前新增的评级列）

-- 删列
alter table drop column evaluate;

3、数据类型

与其他数据库相似，PostgreSQL也支持数字型、字符串型、日期型、时间型以及布尔型等常用的数据类型，其特别之处在于其还额外支持数组、带时区的日期时间、时间间隔、区间、JSON、XML以及其他很多数据类型，此外还支持用户自定义数据类型。
网上也有比较详细的文档。详见：
https://www.runoob.com/postgresql/postgresql-data-type.html
在这里挑一些比较特别又好用的来讲。

3.1 serial

/* 创建一个1开始的序列*/
create sequence s start 1;
/*  创建一个stuff表*/
create table stuff(
    id bigint default nextval('s') primary key,
    name text
);
/*  插入值，只需要插入name，id序列自增*/
insert into stuff(name) values
('张三'),('李四'),('王五');
/* 查看表*/
select * from stuff;

此外，pgsql中还有一个很好用的生成序列的函数generate_series。

select generate_series(1,5,2);

生成了一个1到5的序列，步长为2。

3.2 interval

interval是一种时间间隔类型。

select (date '2019-08-16' + integer '7') as nextweek;

3.3 enum

enum是一种枚举类型，限定某一个字段的取值。

create type mood as enum('sad','ok','happy');
create table person(
    name text,
    current_mood mood
);
insert into person values
('Tom','sad'),
('Jerry','happy');

3.4 array

PostgreSQL 允许将字段定义成变长的多维数组。
数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。
下面创建一张表，共三个字段：姓名、每个季度工资、行程。

/*创建表*/
create table sal_emp(
    name text,
    pay_by_quarter int[],
    schedule text[][]
);
/*给表里插两条记录*/
insert into sal_emp values 
('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}'),
('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
/*查看表*/
select * from sal_emp;

select name,pay_by_quarter[2],schedule[1][2] from sal_emp;

也可以将某一列选出来放入一个数组。

select array(
    select s_name from student
);

也可以根据一维数组来构建多维数组。

select array_agg(f.t)
from(
    values('{Alex,Sonia}'::text[]),('{46,43}'::text[])
) as f(t);

用下面的方式取第一个和最后一个元素。

select ('{1,2,3,4,5}'::int[])[1] as NO1,
('{1,2,3,4,5}'::int[])[array_upper(('{1,2,3,4,5}'::int[]),1)] as NOn;

3.5 json

json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据，这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。

select array_to_json('{{1,5},{99,100}}'::int[]);

select row_to_json(row(1,'foo'));

4、函数

4.1 数学函数

4.2 字符串函数

select overlay('Txxxxas' placing 'hom' from 2 for 4);

下面这段可以实现查看每门课都有哪些学生选课（postgresql中的string_agg类似mysql中的group_concat）。

select a.c_id,string_agg(b.s_name,',') as s_name
from score a left join student b
on a.s_id=b.s_id
group by a.c_id;

select split_part('hello world !',' ',2);

select unnest(string_to_array('abc.123.z45', '.')) As x;

4.3 ARRAY函数

array_agg用法类似于string_agg，可以对照前面字符串函数来理解。

select a.c_id,array_agg(b.s_name) as s_name
from score a left join student b
on a.s_id=b.s_id
group by a.c_id;

unnest用法如下：

select * from unnest('{blind,mouse}'::text[], '{1,2,3}'::int[]) as f(t,i);

pgsql中的数组提取子元素有点类似python中的切片。

select ('{do,you,love,sql,?}'::text[])[2:4];

数组添加元素有点类似字符串的连接。

select '{1,2,3}'::int[] || 4 || 5;

4.4 JSON函数

select to_json('Fred said "Hi"'::text)

select array_to_json('{{1,5},{99,100}}'::int[]);

select row_to_json(row(1,'foo'));

select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');

select * from json_each('{"a":"foo","b":"bear"}');

select * from json_each_text('{"a":"foo","b":"bar"}');

select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"},"f7":66}','f7');

select json_object_keys('{"f1":"abc","f2":{"f3":"a","f4":"b"}}');

create table t_row(
    a int,
    b int
);
select * from json_populate_recordset(null::t_row,'[{"a":1,"b":2},{"a":3,"b":4}]');

select json_array_elements('[1,true,[2,false]]');

4.5 时间/日期函数

select current_date,
current_time,localtime,
current_timestamp,now();

select date_part('year',current_date) as 年
,date_part('month',current_date) as 月
,date_part('day',current_date) as 日;

效果等同于下面这段代码

select extract('year' from current_date) as 年
,extract('month' from current_date) as 月
,extract('day' from current_date) as 日;

select make_date(2019,5,20),
make_time(5,20,21),
make_timestamp(2019,5,20,5,20,21);

4.6 类型转换函数

首先要提的是，pgsql的语法中提供了一个很简便好使的类型转换方式，就是两个冒号。比如，我们建立的score表中成绩score这个字段是字符型的，可以用下面这种方式将其转为int型并做加减法。

select *,(score::int+1) from score limit 5;

此外，还有一些其他的转换函数。

5、存储过程

为了使sql语言更具有可移植性，很多数据库都支持将多句sql语言组合在一起成为一个单元来执行，这种方式在很多数据库中叫做“存储过程”，在pgsql中叫做“函数”。为了和第4部分自带函数的标题区分开，我们这里还是叫存储过程。
PostgreSQL的函数可分为基本函数、聚合函数、窗口函数和触发器函数
四大类。
PostgreSQL支持多种语言来编写过程函数，database中自带安装的有4中，可以通过下面的语句查看。

select lanname from pg_language;

除此之外的其他语言需要额外安装语言包。postgresql还支持小白最喜欢的python，当然，需要配置好python的环境，以及安装PL/Python2U或PL/Python3U。
当然，真正工作中的办公环境不会让你去装这些语言包和语言环境，所以在这里小白还是建议就用plpgsql好了，plpgsql语言其实已经非常强大了。
以下是一个例子，可以作为pgsql函数的模板。

create or replace function getTable(rows int)
return table(col1 int, col2 text)
as $$
begin
    return query select i * 2, i || '_text'
    from generate_series(1, rows, 1) as t(i);
end;
$$ language plpgsql;

select col1, col2 from getTable(2);

PostgreSQL入门