-- 查看数据库大小
select pg_size_pretty(pg_database_size('数据库名'));
--数据库中单个表的大小（不包含索引）
select pg_size_pretty(pg_relation_size('表名'));
--查出所有表（包含索引）并排序
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20
--查出表大小按大小排序并分离data与index
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes
-- 找到符合要求的表2数据，根据name列的值，找到符合要求的表1数据，并更新表1中content列的值
update tb1 set content="呵呵" where tb1.name in (select tb2.name from tb2.alias like '%站点%')
-- 关联表，然后赋值
update tb2 set tb2.fid = t.id from (select tb1.fid id from tb2
left join tb1 on tb2.name = tb1.name) t;
-- 提取列中一部分内容
update flevel4 set tmp_name = substr(log_name, 0, strpos(log_name, '区')+1) where log_name like '%区%';
-- 添加外键约束
alter table datalist
add constraint datalist_level3_id_fk
foreign key (level3_id) references level3 (id);
-- 修改数据库编码
update pg_database set encoding = pg_char_to_encoding('GBK') where datname = 'nyc';
-- 查看数据库编码
select * from pg_database where datname='nyc';
-- 纵向合并2个表
select * from table1 union all select * from table2
-- 删除表
drop table 表名;
-- 更新值
update 表名 set "Name" = "PQMC" where fid > 0; -- 列名Name的值等于列名PQMC的值
update 表名 set "Name" = 'PQMC' where fid > 0; -- 列名Name的值为'PQMC'
-- 获取表的所有列名
select column_name from information_schema.columns where table_name=表名;
-- 删除列
alter table 表名 drop column 列名;
-- 修改表名
alter table 表名 rename to 新表名;
-- 修改列类型，并设置默认值
alter table 表名 alter column "DJMC" set data type varchar using '9999';
-- 添加列
alter table "CQ" add column "DJMC" varchar;
-- 修改列名
alter table "CQ" rename "sjmc" to "SJMC";
-- 获取连接服务下的所有表名（表名开头不包含pg_和sql_）
select tablename from pg_tables
where tablename not like 'pg_%' and tablename not like 'sql_%'
-- 获取连接服务的所有数据库名
select datname from pg_database;
-- 表连接
select s.id, s.name, c.title from student as s
left join class as c on s.class_id = c.id; --通过student表的class_id列和class表的id列进行两个表连接
-- 为表添加外键(外键关联的一定是另一个表的主键)
alter table student -- 添加外键的表名
add constraint fk_student_lesson -- 外键的名称
foreign key(lesson_id) references lesson(id); --student表的lesson_id列的外键为lesson表的主键列id
-- 创建带外键的表
create table emp(
id int primary key,
name varchar(30),
salary nemueric(9,2),
dept_id int,
constraint fk_emp_dept foreign key(dep_id) references dept(id) --emp表的dept_id列的外键为dept表的主键列id
);
-- 为表添加主键
alter table student -- 添加主键的表名
add constraint pk_student_id -- 主键的名称
primary key(id); -- 主键的列名
-- 创建带有主键的表
create table emp(
id int primary key, -- 列级约束主键，不能指定主键名称
name varchar(30),
salary numeric(9,2)
);
create table emp(
id int,
name varchar(30),
salary numeric(9,2),
constraint pk_emp primary key(id) -- 表级约束的主键，可以指定主键的名称
);
-- 创建序列
create sequence teacher_id_seq start with 1; --从1开始的序列
-- 为表的列设置自增序列
alter table teacher alter column id set DEFAULT nextval('teacher_id_seq'::regclass); --teacher表的id列为自增序列
-- 更新自增序列，指定起始值
select setval('datalist_id_seq', 7); --起始值为7
select setval('teacher_id_seq', max(id)) from teacher; --起始值从当前最大值开始
-- 查询自增序列
select pg_get_serial_sequence('表名','列名');
select pg_get_serial_sequence('dt_teacher', 'id');
--修改seq的名字
alter sequence 序列名 rename to 新的序列名;
alter table dt313
alter column 列名[与序列名绑定的] set default nextval('public.新的序列名'::regclass);
--删除主键的seq
alter table 表名 alter column 列名 drop default;
drop sequence 序列名;
--添加主键的seq
create sequence 序列名;
alter table 表名
alter column 列名 set default nextval('public.序列名'::regclass);
alter sequence 序列名 owned by 表名.列名;
select setval('序列名', max(列名)) from 表名; --设置seq序号值从 绑定列 的最大值开始
