草庐IT

postgresql表操作

荼靡， 2024-04-29 原文

postgresql数据库操作

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  -- 插入数据，不给值，默认值为3
);

1.2 查看表

  • 查看所有表
    \d
  • 查看表信息
    -- \d 表名
\d flow

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】

  • >

    -- 查找年龄大于30
select * from info where age>30;
-- 查找id大于1
select * from info where id>1;

  • =

    -- 查找id等于1
select * from info where id=1;

  • >=

    -- 查找id大于等于1
select * from info where id>=1;

  • !=

    -- 查找id不等于1
select * from info where id!=1;

  • between and 

    -- 查找2<=id<=4
select * from info where id between 2 and 4;

  • and

    -- 查找name="wxy" 并且 age=19
select * from info where name="wxy" and age=19;

  • or

    -- 查找name="wxy" 或者 age=49
select * from info where name="wxy" or age=49;
-- 查找name="wxy" 或者 email="123@qq.com" 里面 age=49
select * from info where (name="wxy" or email="123@qq.com") and age=49;

  • in

    -- 查找id 等于1或4或6
select * from info where id in (1,4,6);
-- 查找id 在depart表中存在的id
select * from info where id in (select id from depart);

  • not in

    -- 查找id不等于1或4或6
select * from info where id not in (1,4,6);

  • exists

    -- 查找是否有select * from depart where id=5，如果有，就查询select * from info
select * from info where exists(select * from depart where id=5);

  • not exists

    -- 查找是否有select * from depart where id=5，如果没有，就查询select * from info
select * from info where not exists(select * from depart where id=5);

  • 子查询

    -- 查找id>2 里面的age>10的信息
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

    -- 从第二个位置开始取，取三个，offset从0开始
select * from info limit 3 offset 2;

3.3数据表达式查询

  • where

    SELECT * FROM flow WHERE "from" = 1000;

  • like用于模糊查询

    • %【匹配表示0到多个】

      select * from info where name like '%wxy';

    • _【匹配一个】

      select * from info where name like "_wxy";

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;
-- 只获取根据年龄分组的获取最大的id
select * from info where id in (select max(id) from info group by age);

    -- 根据age分组，再查找count(id)>2 
select age,count(id) from info group by age having count(id)>2;
-- 聚合条件，having放后面
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 映射【根据条件添加列，聚合操作】

  • 获取自己想要的列

    • 获取想要的数据列
      -- 只获取id,name两列
  select id,name from info;
  -- 将name列起别名为n
  select id,name as n from info;
  -- 增加一列123,所有值都为123
  select id,name as n,123 from info;

    -- 查找id,name,添加num列值为666，
-- 添加mid列值为depart表中id的最大值，
-- 添加nid列值为depart表中id的最小值
select id,
name,
666 as num,
(select max(id) from depart) as mid,
(select min(id) from depart) as nid,
age 
from info;

    -- 查找id,name，添加一列x1,info中的depart_id=depart中的id为条件，找depart表中的title作为x1的值

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

    -- 添加一列为v1,当depart_id=1时，将v1的值赋值为 "第1部门" 
   select id,
    name,
    case depart_id when 1 then '第1部门' end v1
    from info;

  • case when then else end

    -- 添加一列为v2,当depart_id=1时，将v2的值赋值为 "第1部门" ,否则赋值为 "其他"
select 
id,
name,
case depart_id when 1 then '第1部门' else '其他' end v2 
from info;

  • case when then ... when then ... when then ... else... end 

    -- 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)

  • desc【降序】
    select * from info order by age desc;
  • asc【升序】
    select * from info order by age asc;

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;
postgresql表spanclasstoken数据库sql

有关postgresql表操作的更多相关文章

  1. ruby - 如何使用 Selenium Webdriver 根据 div 的内容执行操作？ - 2

    我有一个使用SeleniumWebdriver和Nokogiri的Ruby应用程序。我想选择一个类，然后对于那个类对应的每个div，我想根据div的内容执行一个Action。例如，我正在解析以下页面:https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=puppies这是一个搜索结果页面，我正在寻找描述中包含“Adoption”一词的第一个结果。因此机器人应该寻找带有className:"result"的div，对于每个检查它的.descriptiondiv是否包含单词“adoption

  2. ruby-on-rails - 如何处理 Grape 中特定操作的过滤器之前？ - 2

    我正在我的Rails项目中安装Grape以构建RESTfulAPI。现在一些端点的操作需要身份验证，而另一些则不需要身份验证。例如，我有users端点，看起来像这样:moduleBackendmoduleV1classUsers现在如您所见，除了password/forget之外的所有操作都需要用户登录/验证。创建一个新的端点也没有意义，比如passwords并且只是删除password/forget从逻辑上讲，这个端点应该与用户资源。问题是Grapebefore过滤器没有像except,only这样的选项，我可以在其中说对某些操作应用过滤器。您通常如何干净利落地处理这种情况？

  3. ruby-on-rails - 在 Ruby on Rails 中发送响应之前如何等待多个异步操作完成？ - 2

    在我做的一些网络开发中，我有多个操作开始，比如对外部API的GET请求，我希望它们同时开始，因为一个不依赖另一个的结果。我希望事情能够在后台运行。我找到了concurrent-rubylibrary这似乎运作良好。通过将其混合到您创建的类中，该类的方法具有在后台线程上运行的异步版本。这导致我编写如下代码，其中FirstAsyncWorker和SecondAsyncWorker是我编写的类，我在其中混合了Concurrent::Async模块，并编写了一个名为“work”的方法来发送HTTP请求:defindexop1_result=FirstAsyncWorker.new.async.

  4. ruby-on-rails - 如何让 datamapper 与 postgresql 数据库一起工作？ - 2

    我已经找到了几个使用datamapper的示例，并且能够让它们正常工作。不过，所有这些示例都是针对sqlite数据库的。我正在尝试将数据映射器与postgresql一起使用。我将datamapper中的调用从sqlite3更改为postgres，并且我已经安装了dm-postgres-adapter。但它仍然不起作用。我还需要做什么？ 最佳答案 与SQLite不同，PostgreSQL不将数据库存储在单个文件中。在你拥有createdyourdatabase之后，尝试这样的事情:DataMapper.setup:default,{:

  5. ruby - 在 Ruby 中是否有一种惯用的方法来操作 2 个数组？ - 2

    a=[3,4,7,8,3]b=[5,3,6,8,3]假设数组长度相同，是否有办法使用each或其他一些惯用方法从两个数组的每个元素中获取结果？不使用计数器？例如获取每个元素的乘积:[15,12,42,64,9](0..a.count-1).eachdo|i|太丑了...ruby1.9.3 最佳答案 使用Array.zip怎么样？:>>a=[3,4,7,8,3]=>[3,4,7,8,3]>>b=[5,3,6,8,3]=>[5,3,6,8,3]>>c=[]=>[]>>a.zip(b)do|i,j|c[[3,5],[4,3],[7,6],

  6. ruby-on-rails - 如何让 Rails View 返回其关联的操作名称？ - 2

    我有一个非常简单的Controller来管理我的Rails应用程序中的静态页面:classPagesController我怎样才能让View模板返回它自己的名字，这样我就可以做这样的事情:#pricing.html.erb#-->"Pricing"感谢您的帮助。 最佳答案 4.3RoutingParametersTheparamshashwillalwayscontainthe:controllerand:actionkeys,butyoushouldusethemethodscontroller_nameandaction_nam

  7. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  8. ruby-on-rails - Rails 迁移中的 PostgreSQL 点类型 - 2

    我想使用PostgreSQL中的point类型。我已经完成了:railsgmodelTestpoint:point最终的迁移是:classCreateTests当我运行时:rakedb:migrate结果是:==CreateTests:migrating====================================================--create_table(:tests)rakeaborted!Anerrorhasoccurred,thisandalllatermigrationscanceled:undefinedmethod`point'for#/hom

  9. ruby-on-rails - Ruby on Rails 单表继承(STI)和单元测试问题(使用 PostgreSQL) - 2

    我正在使用带有单个“帐户”表的STI模型来保存用户和技术人员的信息(即用户...8)错误:test_the_truth(用户测试):ActiveRecord::StatementInvalid:PGError:ERROR:关系“技术人员”不存在:从“技术人员”中删除...从本质上讲，标准框架不承认Technicians和Users表(或PostgreSQL称它们为“关系”)不存在，事实上，应该别名为Accounts。有什么想法吗？我对RoR比较陌生，不知道如何解决这个问题而又不完全删除STI。 最佳答案 原来问题是由于存在:./te

  10. ruby - rails 3.2.2(或 3.2.1)+ Postgresql 9.1.3 + Ubuntu 11.10 连接错误 - 2

    我正在使用PostgreSQL9.1.3(x86_64-pc-linux-gnu上的PostgreSQL9.1.3，由gcc-4.6.real(Ubuntu/Linaro4.6.1-9ubuntu3)4.6.1，64位编译)和在ubuntu11.10上运行3.2.2或3.2.1。现在，我可以使用以下命令连接PostgreSQLsupostgres输入密码我可以看到postgres=#我将以下详细信息放在我的config/database.yml中并执行“railsdb”，它工作正常。开发:adapter:postgresqlencoding:utf8reconnect:falsedat

随机推荐