今天要帶大家看一個(gè)非常強(qiáng)大的數(shù)據(jù)庫(kù)：PostgreSQL
環(huán)境：小白裝的是postgreSQL 9.6删性，用的客戶端是pgAdmin 4。
要求：對(duì)數(shù)據(jù)庫(kù)和sql語(yǔ)言有基本的認(rèn)識(shí)钳吟。
本文目錄如下：
1躺孝、pgAdmin界面
相信不少人有使用MySQL祖搓、oracle或是SQLserver的經(jīng)驗(yàn)瓮钥，pgAdmin的結(jié)構(gòu)稍微有些不一樣筋量。mytest是小白自己建的數(shù)據(jù)庫(kù)烹吵，通過(guò)sql語(yǔ)句建的表默認(rèn)都在架構(gòu)下的public下，當(dāng)然毛甲，建的函數(shù)年叮、視圖具被、觸發(fā)器什么的也都在那兒玻募。
點(diǎn)擊小閃電，會(huì)彈出sql編輯器一姿，可以寫SQL語(yǔ)句七咧。
2、表操作
2.1 創(chuàng)建數(shù)據(jù)表
建表都是通用的sql語(yǔ)句
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 修改表屬性
如果有外鍵關(guān)聯(lián)的表叮叹，刪除表前需要先刪除外鍵關(guān)聯(lián)
-- 刪除外鍵
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','孫風(fēng)','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','語(yǔ)文','02'),
('02','數(shù)學(xué)','01'),
('03','英語(yǔ)','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表中增加一列評(píng)分）
alter table score add column evaluate text;
update score set evaluate=(
case
when score::int >=90 then '優(yōu)秀'
when (score::int <90)and(score::int >=60) then '及格'
else '不及格'
end
);
2.4 刪
刪除行
-- 刪行（刪除“王菊”這個(gè)人的信息）
delete from student where s_name='王菊';
刪除列（刪掉之前新增的評(píng)級(jí)列）
-- 刪列
alter table drop column evaluate;
3艾栋、數(shù)據(jù)類型
與其他數(shù)據(jù)庫(kù)相似，PostgreSQL也支持?jǐn)?shù)字型蛉顽、字符串型蝗砾、日期型、時(shí)間型以及布爾型等常用的數(shù)據(jù)類型携冤，其特別之處在于其還額外支持?jǐn)?shù)組悼粮、帶時(shí)區(qū)的日期時(shí)間、時(shí)間間隔曾棕、區(qū)間扣猫、JSON、XML以及其他很多數(shù)據(jù)類型翘地，此外還支持用戶自定義數(shù)據(jù)類型申尤。
網(wǎng)上也有比較詳細(xì)的文檔。詳見(jiàn)：
https://www.runoob.com/postgresql/postgresql-data-type.html
在這里挑一些比較特別又好用的來(lái)講衙耕。
3.1 serial
/* 創(chuàng)建一個(gè)1開(kāi)始的序列*/
create sequence s start 1;
/* 創(chuàng)建一個(gè)stuff表*/
create table stuff(
id bigint default nextval('s') primary key,
name text
);
/* 插入值昧穿，只需要插入name，id序列自增*/
insert into stuff(name) values
('張三'),('李四'),('王五');
/* 查看表*/
select * from stuff;
此外橙喘，pgsql中還有一個(gè)很好用的生成序列的函數(shù)generate_series粤咪。
select generate_series(1,5,2);
生成了一個(gè)1到5的序列，步長(zhǎng)為2渴杆。
3.2 interval
interval是一種時(shí)間間隔類型寥枝。
select (date '2019-08-16' + integer '7') as nextweek;
3.3 enum
enum是一種枚舉類型，限定某一個(gè)字段的取值磁奖。
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 允許將字段定義成變長(zhǎng)的多維數(shù)組囊拜。
數(shù)組類型可以是任何基本類型或用戶定義類型，枚舉類型或復(fù)合類型比搭。
下面創(chuàng)建一張表冠跷，共三個(gè)字段：姓名、每個(gè)季度工資、行程蜜托。
/*創(chuàng)建表*/
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;
也可以將某一列選出來(lái)放入一個(gè)數(shù)組抄囚。
select array(
select s_name from student
);
也可以根據(jù)一維數(shù)組來(lái)構(gòu)建多維數(shù)組。
select array_agg(f.t)
from(
values('{Alex,Sonia}'::text[]),('{46,43}'::text[])
) as f(t);
用下面的方式取第一個(gè)和最后一個(gè)元素橄务。
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 數(shù)據(jù)類型可以用來(lái)存儲(chǔ) JSON（JavaScript Object Notation）數(shù)據(jù)幔托， 這樣的數(shù)據(jù)也可以存儲(chǔ)為 text，但是 json 數(shù)據(jù)類型更有利于檢查每個(gè)存儲(chǔ)的數(shù)值是可用的 JSON 值蜂挪。
select array_to_json('{{1,5},{99,100}}'::int[]);
select row_to_json(row(1,'foo'));
4重挑、函數(shù)
4.1 數(shù)學(xué)函數(shù)
4.2 字符串函數(shù)
select overlay('Txxxxas' placing 'hom' from 2 for 4);
下面這段可以實(shí)現(xiàn)查看每門課都有哪些學(xué)生選課（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函數(shù)
array_agg用法類似于string_agg棠涮，可以對(duì)照前面字符串函數(shù)來(lái)理解谬哀。
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中的數(shù)組提取子元素有點(diǎn)類似python中的切片。
select ('{do,you,love,sql,?}'::text[])[2:4];
數(shù)組添加元素有點(diǎn)類似字符串的連接严肪。
select '{1,2,3}'::int[] || 4 || 5;
4.4 JSON函數(shù)
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 時(shí)間/日期函數(shù)
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 類型轉(zhuǎn)換函數(shù)
首先要提的是史煎，pgsql的語(yǔ)法中提供了一個(gè)很簡(jiǎn)便好使的類型轉(zhuǎn)換方式，就是兩個(gè)冒號(hào)驳糯。比如篇梭，我們建立的score表中成績(jī)score這個(gè)字段是字符型的，可以用下面這種方式將其轉(zhuǎn)為int型并做加減法结窘。
select *,(score::int+1) from score limit 5;
此外很洋，還有一些其他的轉(zhuǎn)換函數(shù)。
5隧枫、存儲(chǔ)過(guò)程
為了使sql語(yǔ)言更具有可移植性喉磁，很多數(shù)據(jù)庫(kù)都支持將多句sql語(yǔ)言組合在一起成為一個(gè)單元來(lái)執(zhí)行，這種方式在很多數(shù)據(jù)庫(kù)中叫做“存儲(chǔ)過(guò)程”官脓，在pgsql中叫做“函數(shù)”协怒。為了和第4部分自帶函數(shù)的標(biāo)題區(qū)分開(kāi)，我們這里還是叫存儲(chǔ)過(guò)程卑笨。
PostgreSQL的函數(shù)可分為基本函數(shù)孕暇、聚合函數(shù)、窗口函數(shù)和觸發(fā)器函數(shù)
四大類赤兴。
PostgreSQL支持多種語(yǔ)言來(lái)編寫過(guò)程函數(shù)妖滔，database中自帶安裝的有4中，可以通過(guò)下面的語(yǔ)句查看桶良。
select lanname from pg_language;
除此之外的其他語(yǔ)言需要額外安裝語(yǔ)言包座舍。postgresql還支持小白最喜歡的python，當(dāng)然陨帆，需要配置好python的環(huán)境曲秉，以及安裝PL/Python2U或PL/Python3U采蚀。
當(dāng)然，真正工作中的辦公環(huán)境不會(huì)讓你去裝這些語(yǔ)言包和語(yǔ)言環(huán)境承二，所以在這里小白還是建議就用plpgsql好了榆鼠，plpgsql語(yǔ)言其實(shí)已經(jīng)非常強(qiáng)大了。
以下是一個(gè)例子亥鸠，可以作為pgsql函數(shù)的模板妆够。
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);
