postgresql数据库操作
- 1.表基本操作
- 1.1创建表
- 1.2 查看表
- 1.3清空表数据
- 1.4删除表
- 1.5退出数据库
- 2.表属性操作
- 2.1增加列
- 2.2删除列
- 2.3修改列属性
- 2.4增加列非空约束
- 2.5 增加列唯一约束
- 2.6删除主键约束
- 2.7创建主键约束
- 3.表数据操作
- 3.1数据查询
- 3.2数据条件查询【where】
- 3.3数据表达式查询
- 3.4函数查询
- 3.5分组
- 3.6 映射【根据条件添加列,聚合操作】
- 3.7 排序(order by)
- 3.8 左右连表【多表查询】
- 3.9 联合【union】
- 3.10 数据增删改
- 4.表关系
- 4.1 一对多【设置一个外键】
- 4.2 多对多【设置多个外键】
1.表基本操作
1.1创建表
create table tb4(
id BIGSERIAL not null primary key,
name varchar(16) not null,
email varchar(32) null,
age int default 3
);
1.2 查看表
1.3清空表数据
TRUNCATE TABLE flow;
1.4删除表
DROP TABLE flow;
1.5退出数据库
\q
2.表属性操作
2.1增加列
ALTER TABLE flow ADD age int;
2.2删除列
ALTER TABLE flow DROP COLUMN age;
2.3修改列属性
ALTER TABLE flow ALTER COLUMN date TYPE varchar;
Alter TABLE point alter column point TYPE geometry USING point ::geometry;
select st_astext(geo) from test;
2.4增加列非空约束
alter table flow alter column age set not null;
2.5 增加列唯一约束
ALTER TABLE flow ADD CONSTRAINT FlowUniqueConstraint UNIQUE(age);
ALTER TABLE flow DROP CONSTRAINT FlowUniqueConstraint ;
2.6删除主键约束
\d flow;
ALTER TABLE flow DROP CONSTRAINT flow_pkey;
2.7创建主键约束
ALTER TABLE flow ADD CONSTRAINT FlowPrimaryKey PRIMARY KEY (ID);
ALTER TABLE flow DROP CONSTRAINT FlowPrimaryKey ;
3.表数据操作
3.1数据查询
- 查询表所有数据
select * from flow;
- 查询表前100条数据
SELECT * FROM flow ORDER BY id ASC LIMIT 100;
- 查询表后100条数据
SELECT * FROM flow ORDER BY id DESC LIMIT 100;
- 查询表某几列数据
SELECT id,date FROM flow;
SELECT id as n1,date as n2 FROM flow;
3.2数据条件查询【where】
-
>
select * from info where age>30;
select * from info where id>1;
-
=
select * from info where id=1;
-
>=
select * from info where id>=1;
-
!=
select * from info where id!=1;
-
between and
select * from info where id between 2 and 4;
-
and
select * from info where name="wxy" and age=19;
-
or
select * from info where name="wxy" or age=49;
select * from info where (name="wxy" or email="123@qq.com") and age=49;
-
in
select * from info where id in (1,4,6);
select * from info where id in (select id from depart);
-
not in
select * from info where id not in (1,4,6);
-
exists
select * from info where exists(select * from depart where id=5);
-
not exists
select * from info where not exists(select * from depart where id=5);
-
子查询
select * from (select * from info where id>2) as T where age>10;
-
is
SELECT * FROM flow WHERE "to" is not null;
-
limit
select * from info limit 5;
-
limit offset
select * from info limit 3 offset 2;
3.3数据表达式查询
3.4函数查询
count
SELECT COUNT(*) AS "flow" FROM flow;
sum
SELECT sum("from") AS "flow" FROM flow;
max
SELECT max("from") FROM flow;
min
SELECT min("from") FROM flow;
DISTINCT
【过滤重复值】SELECT DISTINCT id FROM depart;
3.5分组
-
group by
select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
select age,count(1) from info group by age;
select depart_id,count(id) from info group by depart_id;
-
having
【先执行分组再判断条件】
select depart_id,count(id) from info group by depart_id having count(id)>2;
select age,max(id) from info group by age;
select * from info where id in (select max(id) from info group by age);
select age,count(id) from info group by age having count(id)>2;
select age,count(id) from info where id>4 group by age having count(id)>2;
select age,
count(id)
from info where id>2 group by age having count(id)>1 order by age desc limit 1;
- 要查询的表info
- 条件 id>2
- 根据age分组
- 对分组后的数据再根据聚合条件过滤 count(id)>1
- 根据age从大到小排序
- 获取第1条
3.6 映射【根据条件添加列,聚合操作】
-
获取自己想要的列
select id,name from info;
select id,name as n from info;
select id,name as n,123 from info;
select id,
name,
666 as num,
(select max(id) from depart) as mid,
(select min(id) from depart) as nid,
age
from info;
select id ,
name,
(select title from depart where depart.id=info.depart_id) as x1
from info;
select
id ,
name,
(select title from depart where depart.id=info.depart_id) as x1,
(select title from depart where depart.id=info.id) as x2
from info;
-
case when then end
select id,
name,
case depart_id when 1 then '第1部门' end v1
from info;
-
case when then else end
select
id,
name,
case depart_id when 1 then '第1部门' else '其他' end v2
from info;
-
case when then ... when then ... when then ... else... end
select id,
name,
case depart_id when 1 then '第一部门' end v1,
case depart_id when 2 then '第1部门' else '其他' end v2,
case depart_id when 1 then '第一部门' when 2 then '第2部门' else '其他' end v3,
case when age<18 then '少年' end v4,
case when age<18 then '少年' else '油腻男' end v5,
case when age<18 then '少年' when age<30 then '青年' else '油腻男' end v6
from info;
3.7 排序(order by)
3.8 左右连表【多表查询】
-
主表
主表 left outer join 从表 on 主表.x=从表.id
select * from info left outer join depart on info.depart_id=depart.id;
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id=depart.id;
-
从表
从表 right outer join 主表 on 主表.x = 从表.id
select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id=depart.id;
3.9 联合【union】
-
列数需相同【union】
select id,title from depart
union
select id,name from info;
-
【union】自动去重
select id,title from depart
union
select id,name from info;
-
获取所有【union all不去重】
select id from depart
union all
select id from info;
3.10 数据增删改
- 修改数据
UPDATE covid19_policy SET id=1 WHERE id=0;
- 删除数据
DELETE FROM covid19_policy WHERE ID = 2;
- 添加数据
INSERT INTO flow (id, date, "from","to",flow_people) VALUES (0, '2020-01-01', 1,2,1);
INSERT INTO flow (id, date, "from","to",flow_people) VALUES (500000002, '2020-01-01', 1,2,1),(500000001, '2020-01-01', 1,2,1);
4.表关系
4.1 一对多【设置一个外键】
- 需要两张表来存储信息,且两张表存在
一对多
或多对一
关系
- constraint fk_info1_depart1 foreign key (depart_id) references depart1(id)
create table depart1(
id int not null primary key,
title varchar(16) not null
);
create table info1(
id int not null primary key,
name varchar(16) not null,
email varchar(32) not null,
age int,
depart_id int not null,
constraint fk_info1_depart1 foreign key (depart_id) references depart1(id)
) ;
alter table info1 add CONSTRAINT fk_info1_depart1 foreign key (depart_id) references depart1(id);
alter table info1 drop CONSTRAINT fk_info1_depart1;
4.2 多对多【设置多个外键】
- 需要三张表来存储信息,两张单表+关系表,创造出两张单表之间
多对多关系
create table boy(
id int not null primary key,
name varchar(16) not null
);
create table girl(
id int not null primary key,
name varchar(16) not null
);
create table boy_girl(
id int not null primary key,
boy_id int not null,
girl_id int not null,
constraint fk_boy foreign key (boy_id) references boy(id),
constraint fk_girl foreign key (girl_id) references girl(id)
);
alter table boy_girl add constraint fk_boy foreign key (boy_id) references boy(id);
alter table boy_girl add constraint fk_girl foreign key (girl_id) references girl(id);
constraint fk_info1_depart1 foreign key (depart_id) references depart1(id)
alter table boy_girl drop CONSTRAINT fk_boy;
alter table boy_girl drop CONSTRAINT fk_girl;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)