目录

Centos篇

1.进入退出

2.基本查看命令

3.库、表操作

4.字段类型

5.约束条件

6.INSERT语句

7.SELECT语句

8.WHERE语句

9.数据抽出选项

10.统计抽出数据

11.方便函数

12.更新和删除

13.变更表结构

14.操作多个表

15.视图

16.事务

windows篇

1.建立数据库连接

2.访问数据库

学习postgresql数据库视频学习笔记

Centos篇

1.进入退出

#进入postgres账户
su postgres
#退出（回到root账号）
su root
#输入密码（如果有）
2.基本查看命令
#查看数据库（账号目录下有效）
psql -l
#查看数据表（数据库目录下有效）
\dt
3.库、表操作
#建库
createdb komablog
#查看数据库
psql -l
#进入数据库
psql komablog
#建表
create table post (title varchar(255),content text);
#查看数据表
\dt
#查看数据库表信息
\d posts
#改表
alter table posts rename to komaposts;
#查看数据表
\dt
#删表
drop table komaposts;
#退出
\q
#建立sql文件（复用建表操作）
nano db.sql
...
create table posts (title varchar(255),content text);
...
psql komablog
\i db.sql
4.字段类型
数值型：
interger（int）
real
serial
文字型：
char
varchar
text
布尔型：
boolean
日期型：
date
time
timestamp
特色类型：
array
网络地址型（inet）
JSON型
XML型
5.约束条件
not null;不能为空
unique：在所有数据中唯一
check：字段设置条件
default：字段默认值
primary key(not null,unique):主键，不能为空，且唯一不重复
6.INSERT语句
insert into [table name]  (filed,...) values (value,...)
insert int posts(title,content) values (‘title2’,’content2’);
7.SELECT语句
select filed1,filed2... from [tablename]
8.WHERE语句
select * from users where score>20;
select * from users where score<30;
select * from users where score>20 and score <30;
select * from users where team=’勇士’;
select * from users where team!=’勇士’;
select * from users where player like ‘阿%’;
select * from users where player like ‘阿_’;
9.数据抽出选项
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 team ,score desc;
select * from users order by team desc,score desc;
select * from users order by score desc limit 3;
select * from users order by score desc limit 3 offset 1;
select * from users order by score desc limit 3 offset 2;
select * from users order by score desc limit 3 offset 3;
10.统计抽出数据
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 * from users where score=(select mim(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;
select team, max(score) from users group by team having max(score)>=25 order by max(score);
11.方便函数
length   //长度
concat  //链接两个字符串
alias    //起别名
substring  //切割字符串
random    //随机函数
select player,length(player) from users;
select player,concat(player,’/’,team)from users;
select player,concat(player,’/’,team) as “球员信息” from users;
select substring(team,1,1)as’球队首文字’ from users;
select concat(‘我’,substring(team,1,1))as’球队首文字’from users;
select random();
select *from users order by random();
select *from users order by random() limit 1;
Q：
1. concat 中的‘’在word中无法直接粘贴命令执行
2. as 后起别名需要用“”而不是‘’
12.更新和删除
update [table] set [filed=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 (‘勇士’,’骑士’);
delete from users where score >30;
13.变更表结构
alter table [tablename]...
create index ...
drop index...
\d users;
alter table users add fullname varchar(255);
\d users;
alter table users drop fullname;
\d users;
alter table users rename player to nba_player;
\d users;
alter table users alter nab_player type varchar(100);
\d users;
create index nba_player_index on users(nba_player);
\d users;
drop index nba_player_index;
14.操作多个表
select * from users;
select * from twitters;
select users.player,twitters.content from users,twitters where users.id=twitters.user_id;
select u.player,t.content from users as u,twitters as t where u.id=t.user_id;
select u.player,t.content from users as u,twitters as t where u.id=t.user_id and u.id=1;
15.视图
create view
drop view
select users.player,twitters.content from users,twitters where users.id=twitters.user_id;
create view curry_twitters as select u.player,t.content from users as u,twitters as t where u.id=t.id;
\dv
\d curry_twitters
select * from curry_twitters;
drop view curry_twitters;
\dv
16.事务
begin
commit
rollback
begin
sql1
sql2
...
commit
begin
sql1
sql2
...
rollback
windows篇
1.建立数据库连接
#接入PostgreSQL数据库
psql -h IP地址 -p 端口 -U 数据库名
（之后会要求输入数据库密码）
2.访问数据库
1、列举数据库：\l
 2、选择数据库：\c 数据库名
 3、查看该某个库中的所有表：\dt
 4、切换数据库：\c interface
 5、查看某个库中的某个表结构：\d 表名
 6、查看某个库中某个表的记录：select * from apps limit 1;
 7、显示字符集：\encoding
 8、退出psgl：\q