postgreSQl sql语句

##要在一个操作中更改两个现有列的类型

ALTER TABLE "hbird"."company_main" ALTER COLUMN legal_person TYPE varchar(64);

##要将一个varchar类型的列添加到表中

ALTER TABLE "hbird"."company_main" ADD COLUMN legal_person varchar(30);

##从表中删除列

ALTER TABLE "hbird"."company_main" DROP COLUMN legal_person RESTRICT;

##重命名现有列

ALTER TABLE "hbird"."company_main" RENAME COLUMN address TO city;

##重命名现有表

ALTER TABLE "hbird"."company_main" RENAME TO company_mains;

查询sql执行效率结构

EXPLAIN SELECT * FROM company_main

查询重复的企业名称

SELECT * FROM "hbird"."company_main" a WHERE ((SELECT COUNT(*) FROM "hbird"."company_main" WHERE name = a.name) > 1) ORDER BY name DESC  

查询重复的法人名

SELECT * FROM "hbird"."company_main" a WHERE ((SELECT COUNT(*) FROM "hbird"."company_main" WHERE legal_person = a.legal_person) > 1) ORDER BY legal_person DESC  

统计重复的企业有多少个

SELECT name,count(name) as count FROM "hbird"."company_main" GROUP BY name HAVING count(name) >1 ORDER BY count DESC;
最后修改:4个月前
如果觉得我的文章对你有用,请随意赞赏

共有 0 条评论