【PostgreSQL】常用SQL-育师

一、数据库操作

1. 查询服务器版本

-- 1.1 查询详细版本信息 select version(); -- 1.2 查看版本信息（简洁版） show server_version; -- 1.3 查看数字版本信息（含小版本号） show server_version_num; -- 或 select current_setting('server_version_num'); -- 1.4 转换版本号为整数类型（便于数值比较） select current_setting('server_version_num')::integer;

2. 创建数据库

create database testdb;

3. 修改数据库

-- 重命名数据库 alter database testdb rename to new_name; -- 修改数据库最大并发连接数 alter database testdb connection limit 10; -- 修改数据库默认表空间 alter database testdb set tablespace new_tablespace;

4. 删除数据库

-- 安全删除（不存在则不报错） drop database if exists testdb;

5. 其他数据库相关查询

-- 查询所有数据库用户 select usename from pg_user;

二、表操作

1. 新建表

-- 创建基础表（含自增主键、默认值） create table student( id serial primary key, -- 自增主键（默认从1开始，步长1） name varchar(64) not null, -- 姓名，非空 age integer not null, -- 年龄，非空 sex integer not null, -- 性别，非空 createtime timestamp without time zone not null default now(), -- 创建时间，默认当前时间 updatetime timestamp without time zone -- 更新时间 ); -- 复制表结构+数据（仅复制数据和结构，不复制约束/索引） create table student_copy as select * from student;

2. 删除表

注意：原内容中delete table student是错误写法，正确写法如下：

-- 删除表（含结构+数据，不可逆） drop table if exists student; -- 仅清空表数据（保留结构） -- 方式1：逐行删除（可回滚，速度慢） delete from student; -- 方式2：快速清空（不可回滚，速度极快） truncate table student;

3. 查询表相关信息

-- 查询student表是否存在（方式1：通过系统表pg_class） select * from pg_class where relname = 'student' and relkind = 'r'; -- 查询student表是否存在（方式2：通过系统视图pg_tables，更直观） select * from pg_tables where tablename = 'student';

4. 修改表

4.1 表结构操作

-- 4.1.1 重命名表 alter table student rename to new_student; -- 4.1.2 添加字段（非空约束需确保已有数据符合条件） alter table student add column height integer not null; -- 4.1.3 删除字段 alter table student drop column sex; -- 4.1.4 重命名字段 alter table student rename column name to new_name; -- 4.1.5 查看/修改字段属性 -- a) 查询表所有字段的完整属性（名称、类型、非空、注释） select c.relname as table_name, col_description(a.attrelid, a.attnum) as column_comment, format_type(a.atttypid, a.atttypmod) as column_type, a.attname as column_name, a.attnotnull as is_not_null from pg_class as c, pg_attribute as a where a.attrelid = c.oid and a.attnum > 0 and c.relname = 'student'; -- b) 查询表中指定字段的属性 select c.relname as table_name, col_description(a.attrelid, a.attnum) as column_comment, format_type(a.atttypid, a.atttypmod) as column_type, a.attname as column_name, a.attnotnull as is_not_null from pg_class as c, pg_attribute as a where a.attrelid = c.oid and a.attnum > 0 and c.relname = 'student' and a.attname = 'name'; -- c) 修改字段类型（int4→int8，无数据冲突时） alter table student alter column sex type bigint; -- d) 强制转换字段类型（处理空值/非数值文本） alter table student alter column name type integer using (trim(name))::integer; -- e) 增加/删除字段约束 -- e1 非空约束 -- 增加非空约束（需先确保字段无NULL值） delete from student where updatetime is null; -- 清理不符合约束的数据 alter table student alter column updatetime set not null; -- 删除非空约束 alter table student alter column updatetime drop not null; -- e2 检查约束（自定义条件） delete from student where age <= 3; -- 清理不符合条件的数据 alter table student add constraint ck_student_check_age check(age > 3); -- 添加检查约束 -- 删除检查约束 alter table student drop constraint ck_student_check_age; -- e3 唯一约束（多字段组合唯一） alter table student add constraint uk_student_unique_name_age unique(name,age); -- 删除唯一约束 alter table student drop constraint uk_student_unique_name_age;

4.2 表记录操作

-- 4.2.1 插入记录 -- 插入单条记录 insert into student (name, age, sex, createtime, updatetime) values('Tom', '18', 1, '2018-11-29 17:00:02', '2018-11-29 17:00:02'); -- 从其他表批量插入符合条件的记录 insert into student1 select * from student2 where age > 18; -- 4.2.2 删除记录 -- 删除指定条件记录 delete from student where id = 1; delete from student where age > 18; delete from student where createtime <= '2018-01-01 00:00:00'; -- 4.2.3 查询记录 -- 查询全部记录 select * from student; -- 查询符合条件的指定字段 select name, age, sex from student where age > 18; -- 查询数据库连接信息（排查连接/锁问题） select * from pg_stat_activity; -- 包含客户端user、IP、执行语句、状态、耗时等 -- 4.2.4 修改记录 -- 更新符合条件记录的更新时间（保留到秒） update student set updatetime = date_trunc('second', now()) where age = 18;