postgreSQL数据库常用语法

短命女 2022-12-28 07:42 202阅读 0赞

## postgreSQL常用语法 ##

### 1、CRUD增删改查 ###

#### 创建用户角色 ####

create  user ldc  with password 'ldc-';

#### 创建数据库 ####

create DATABASE school_info 
    ENCODING  ='utf-8'  --指定字符集 
    TABLESPACE = pg_default 
    owner ldc; --设置数据库所有者
    grant all privileges on database school_info  to ldc;  --将 school_info 数据库的所有权限赋予 ldc

#### 创建表 ####

-- 创建班级表
    create table class_info
    (
        id                    serial not null
                              constraint class_info_pk
                              primary key,
        name                  varchar,
        write_date            timestamp
    );
    
    comment on table class_info is '班级表';
    comment on column class_info.name is '班级名称';
    comment on column class_info.write_date is '修改时间';
    alter table class_info  owner to ldc;   -- 修改表拥有者为ldc
    
    
    --创建表
    -- 创建学生表，id自增
    -- id serial not null 表示id自增
    -- id integer not null 表示id不自增
    create table student
    (
        id                    serial not null
                              constraint student_pk
                              primary key,
        name                  varchar,
        class_id             integer  references "class_info"("id"),
        height                numeric,
        weight                numeric,
        write_date            timestamp
    );
    
    comment on table student is '学生表';
    comment on column student.name is '名称';
    comment on column student.class_id is '班级ID';
    comment on column student.height is '身高';
    comment on column student.weight is '体重';
    comment on column student.write_date is '修改时间';
    alter table student  owner to ldc;   -- 修改表拥有者为ldc

#### 增加记录 ####

insert into "class_info" (name,write_date) values('高一八班', '2010-09-09 11:33:00');
    
    insert into "student" (name,class_id, height, weight, write_date) 
    	values ('小梁',1,160,50, '2010-09-09 12:33:00'),
    	('小刘',1,155,50, '2010-10-08 13:33:00'),
    	('小强',1,175,60, '2010-11-12 13:33:00');

#### 删除表 ####

-- 如果表存在就先删除
    drop table if exists student;

#### 删除记录 ####

delete from student where name='小梁'

#### 删除字段 ####

alter table 表名 drop column 列名 ;
    比如
    alter table student drop column sex ;

#### 更新记录 ####

update student set name='大梁' where id=3

#### 新增或更新 ####

--如果id冲突就更新
    insert into student(id, name,class_id) 
    	values(1,'小兰',1)
    	on conflict(id) 
    	do update set name ='小芳';
    
    --如果id冲突就什么也不做
    insert into  student(id, name,class_id)
       values(3,'小明',1)
       on conflict(id) do nothing;

#### 联合子集更新 ####

# 联合子集更新，把sale_order_line的name连接换行符，然后按id更新到表a_test中对应的name
    update a_test set name=array_to_string(array(select name from sale_order_line where order_id=a_test.id),'<br/>');

#### 把一个表中的数据插入到另一个表中 ####

--把一个表中的数据插入到另一个表中
    insert into 目标表名 (column1,column2,columnn) select value1,value2,valuen from  源表名
    比如：
    insert into student (name, classs_name,create_date) select  student_name as name, class_name, now() from class_table;

#### 增加字段 ####

alter table student add column sex bool;

#### 查看用户角色 ####

select * from pg_roles;

#### 查看当前时间 ####

now()
    select now()

#### 查看表所有字段 ####

select * from information_schema.columns where table_schema='public' and table_name='student';

#### 查看数据库所有表名 ####

select tablename from pg_tables where schemaname='public'

### 2、按条件查询 ###

#### 升降序 ####

-- 对查询结果按id降序显示
    select * from student order by id desc
    
    -- 对查询结果按id升序显示
    select * from student order by id asc

#### 转义字符 ####

-- 转义字符, 查找name中包含单引号的记录
    select * from student where name like  E'%\'%'

#### 查看表记录总数 ####

方式一：

select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc;

方式二：

select count(*) from student;

### 3、常用函数 ###

#### array:将结果转换为数组 ####

SELECT array(SELECT "name" FROM student);

结果：`{小芳,小刘,大梁}`

#### array\_to\_string:将数组合并为字符串 ####

select array_to_string(array[1,2,3], ',');

结果：`1,2,3`

#### cast:类型转换 ####

select cast(id as varchar) from student;  --把id 从integer转成varchar

#### concat:字符串拼接 ####

select 
    concat('学生:id=', cast(s.id as varchar), '姓名:',s.name, '班级:',ci.name) 
    from student as s
    left join class_info as ci on ci.id=s.class_id

结果：`学生:id=1姓名:小芳班级:高一八班`

#### concat\_ws:多字符串拼接(不用转换类型) ####

select 
    concat_ws('学生:id=', s.id, '姓名:',s.name, '班级:',ci.name) 
    from student as s
    left join class_info as ci on ci.id=s.class_id

#### substring:字符截取 ####

select substring('abcd',1,2); -- 表示下标从1开始,截取2个字符

结果：`ab`

#### row\_number():定义行编号 ####

--对行记录定义行编号，使用函数ROW_NUMBER()
    select           
    		ROW_NUMBER() OVER (ORDER BY id desc) AS sequence_number,
    		id,name
    from
    		student

#### array\_agg:把表达式变成一个数组 ####

-- 名称降序然后组合成数组
    select array_agg(name order by name asc) from student

结果：`{大梁,小芳,小刘}`

#### unnest:一行变多行 ####

select unnest(array_agg(name order by name asc)) from student

结果：`{大梁,小芳,小刘}` \->

`大梁`

`小芳`

`小刘`

#### array:把结果变成数组类型 ####

select ARRAY(select unnest(array_agg(name order by name asc))) from student

结果：`{大梁,小芳,小刘}`

#### 合并查询同一列的多条记录 ####

# PostgreSQL合并查询同一列的多条记录，针对一对多，多对多字段
    比如表：
    
    id   name                  
    1    小明                   
    1    小红                     id   name
    1    小亮        --> 1 小明，小亮，小红
    2    小强                     2    小强，小王
    2    小王
    
    SELECT 
        id, array_to_string(ARRAY(SELECT unnest(array_agg(name order by name desc))),',') AS all_name
    FROM  
        student
    GROUP BY id;

#### to\_char：类型转换 ####

select to_char(write_date, 'yyyy-MM-dd hh24:MI:ss') from student

#### case:枚举 ####

--case语句
    select
    case 
    	when score > 80 and score < 90 then '良'
    	when score > 90 then '优秀'
    else '中'
    end as result
    from student;

#### with ：临时表 ####

--临时表、字符串合并、类型转换、时间格式转换、当前时间
    WITH TEMP AS ( 
    	SELECT CAST (concat (write_date, '-01' ) AS TIMESTAMP ) 
    	   AS account_period_time 
    		 FROM student AS s ) 
    SELECT
    	account_period_time,
    	to_char(CURRENT_DATE,'yyyy-MM-dd hh24:MI:ss') as current_date,
    	to_char( account_period_time, 'yyyy' ) as year,
    	to_char( account_period_time, 'MM' ) as month,
    	to_char( account_period_time, 'dd' ) as day 
    FROM TEMP

结果：

account_period_time	current_date	    year	month	day
    2019-06-01 0:00:00	2020-06-24 00:00:00	2019	 06	     01
    2019-06-01 0:00:00	2020-06-24 00:00:00	2019	 06	     01

#### 多个临时表 ####

# 多个临时表
    WITH temp_student AS ( SELECT ID, NAME, sex FROM student WHERE sex = TRUE ),
    temp_class AS (
    	SELECT
    		ID,
    		NAME,
    		student_id,
    		teacher_id
    	FROM
    		the_class 
    	),
    	temp_teacher AS (
    	SELECT 
    	  ID,
    		NAME,
    		age 
    	FROM
    		teacher 
    	) 
    	SELECT
    	ts.NAME AS student_name,
    	tc.NAME AS class_name,
    	te.NAME AS teacher_name
    	from temp_student as ts
    	LEFT JOIN temp_class AS tc ON tc.student_id = ts.ID  
    	LEFT JOIN teacher AS te ON te.id = tc.teacher_id

#### coalesce:返回参数中的第一个非null的值 ####

-- null转成有意义的值
    select coalesce(name, '') as name from student;  --name为null，就转为空字符串

### 4、其它 ###

# 保留重复记录中的最小id的SQL语句
    select min(id) as id,co1,co2 from test group by co1,co2
    
    # 使用 interval 时间相加减(+/-)
    当前时间 + 10秒，
    select to_char(now() + interval '10 second', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;
    当前时间 - 10秒
    select to_char(now() + interval '-10 second', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;
     
    当前时间 + 10分，
    select to_char(now() + interval '10 minute', 'yyyy-mm-dd hh24:mi:ss')  as reqDate  from account_period;
     
    当前时间 + 10时，
    select to_char(now() + interval '10 hour', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;
     
    当前时间 + 10天，
    select to_char(now() + interval '10 day', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;
    
    当前时间 + 10年，
    select to_char(now() + interval '10 year', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;
    
    
    # 判断字段是否全为数字
    select '1234' ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'
    select '1234a' ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'
    select * from student where score  ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'

## 后记 ##

【后记】为了让大家能够轻松学编程，我创建了一个公众号【轻松学编程】，里面有让你快速学会编程的文章，当然也有一些干货提高你的编程水平，也有一些编程项目适合做一些课程设计等课题。

也可加我微信【1257309054】，拉你进群，大家一起交流学习。  
如果文章对您有帮助，请我喝杯咖啡吧！

公众号

![公众号][20200317124808234.jpg]

![赞赏码][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xtX2lzX2Rj_size_16_color_FFFFFF_t_70]

关注我，我们一起成长~~

[20200317124808234.jpg]: /images/20221120/af7f0fb87ab4405d8021f38100ccb69f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xtX2lzX2Rj_size_16_color_FFFFFF_t_70]: /images/20221120/f6b202bb425d4f1387c4c41bd2f9fadb.png