Postgresql相关基础知识

postgresql命令行常用命令

常用命令

• 展示数据库
  \l

• 切换数据库
  \c dbname [ username ] [ host ] [ port ]

• 展示当前数据库下所有关系
  \d
  \d “Account” 展示Account表字段信息

• 展示当前数据库下所有schema信息
  \dn

---

postgresql的基础数据类型

• 数值型
  integer(int)
  real (类似与float)
  serial
• 文字型
  char
  varchar
  text
• 布尔型
  boolean
• 日期型
  time
  timestamp
  date
• 其他类型参考网站
  www.postgresql.org/docs/9.5/static/datatype.html

---

表字段的约束条件

create table posts(
	id serial primary key,
	title varchar(255) not null,
	content text check(length(content) > 3),
	is_draft boolean default TRUE,
	is_del boolean default FALSE,
	created_date timestamp default 'now'
);

• 约束说明
  not null 不能为空
  unique 在所有数据中必须唯一
  check 字段设置条件
  default 字段默认值
  primary key(not null, unique) 主键， 不能为空， 且不能重复

insert应用

insert into [tablename] (field, ... ) values (value, ...)

select应用

select * from [tablename];
select [values] from [tablename];
\x开启/关闭扩展显示模式

---

where语句的基本使用

使用where语句来设定select， update， delete 语句数据抽出的条件。

select * from users;
select * from users where score > 20;
select * from users where score > 20 and score < 30;
select * from users where team = '123';
select * from users where player like 'a%'; // % 代表以a开头的所有数据
select * from users where player like 'a_' // d代表两个字符且第一个字符为a

---

数据抽出排序

• order by
• limit
• offset
  偏移量

select * from users order by score asc;
select * from users order by score desc;
select * from users order by team;
select * from users order by team, score;
select * from users order by score desc limit 3;
select * from users order by score desc lijmit 3 offset 1;
select * from users order by score desc limit 3 offset 3; //可以用于分页功能

---

统计抽出数据

• distinct
  将重复的数据去除
• sum
• max/min
• group by/having
  having是针对于group by 的过滤条件

select distinct team from users;
select sum(score) from users;
select max(score) from users;
select min(score) from users;
select * from users where score = (select max(score) from users); //去除当前信息中分数最大球员的全部信息
select team, max(score) from users group by team;
select team, max(score) from users group by team having max(score) >= 25 order by max(score);

方便的函数

• length
• concat
  连接两个字符串
• alias
• substring
  切割字符串
• random

其他函数参考网址 https://www.postgresql.org/docs/9.5/static/functions/html

select player, length(player) from users;
select player, concat(player,'/', team) from users;
select player,concat(player, '/', team) as "球员信息" from users;
select concat('我', subString(team,1,1) )as "球队首文字" from users;
select * from users order by random();

更新与删除

update [table] set [field=newvalue,...] where ...
delete from [table] where...// 物理删除(推荐不使用)

update users set score = 29.1 where player = '阿展';
update users set score = score + 1 where team = '勇士';
update users set score = score + 100 where team IN ('勇士'， ‘七十’);
deletee from users where score > 30;