postgresql学习笔记
欢迎大家指出问题!
版本从6.0开始支持sql解释器
安装一路下一步。
1.创建数据库
//命令行操作 createdb database_name; create database database_name; psql -l //展示所有数据库 psql database_name //进入数据库
2.删除数据库
dropdb database; drop database database_name;
3.创建表
create table 表名 (title varchar(255), content text); //修改表名 alter table posts rename to 表名;
//查看表信息(结构) \d 表名 //执行这个sql文件 \i a.sql //切换数据显示方式(横向和纵向显示) \x
create table posts ( id serial primary key, title varchar(255) not null, content text check(length(content) > 3), is_draft boolean default false, created_date timestamp default 'now');
create table public.students ( id integer not null, name character(128), subjects character(1), constraint student2_pkey primary key (id) ) with ( oids=false ); alter table public.students owner to postgres; comment on table public.students is '这是一个学生信息表';
案例表:
4.删除表
drop table students;
5.数据类型
数值型:
integer real serial(序列型,一般用于自增字段)
文字型:
char varchar text
布尔型:
boolean
日期型:
date time timestamp
特色类型:
array inet(网口类型) json xml
6.添加表约束
unique
:所在数据中值必须唯一
check
:字段设置条件,可以指定函数check(length(content) > 3)必须超过3个字符
default
:字段默认值
7.insert语句
insert into 表名(cloum1,cloum2) value(a,b);
8.数据抽出选项:
order by asc
升序 desc
降序
limit
限制返回数据条数
offset
偏移量(从哪条数据开始)
//分页查询limit和offset结合使用。 select * from users order by score desc limit 3; select * from users order by score desc limit 3 offset 3;
9.统计抽出数据
distinct
去重
group by/having
(在group by
之后进行再次筛选) 分组
select team,max(score) from users group by team having max(score)>25 order by max(score);
10.方便的函数
length concat
(连接两个字符串) alias
(别名) substring
(截取字符串) random
select player,length(player) from users; select player,concat(player,"/",team) from users; select player,concat(player,"/",team) as "球员信息" from users; select concat('我',substring(team,1,1)) as "球队首文字" from users; //随机抽奖 select player from users order by random() limit 1;
11.更新和删除
update [table] set [field=newvalue,…] where … delete from [table] where …
eg:
update users set score = score + 100 where team in ("勇士","骑士");
12.变更表结构
//alter table [tablename] … //给表添加一条fullname字段 alter table users add fullname varchar(255);
//给哪个表的字段建索引(索引提高查询效率,但是增删效果不好) create index 索引名 on users(字段名); //删除索引 drop index 索引名;
//修改列名 alter table users rename player to nba_player; //修改字段的的长度 alter table users alter nba_player type varchar(128);
13.操作多个表
//两表的关联查询 select users.player,twitters.content from users,twitters where users.id = twitters.user_id; //别名 select u.player,t.content from users as u,twitters as t where u.id = t.user_id;
14.使用视图
视图:视图就是一个select语句,把业务系统中常用的select语句简化成一个类似于表的对象,便于简单读取和开发。 (对于经常使用的select语句建立视图便于编码和管理)
//创建一个视图(通过 \dv 查看视图描述) create view curry_twitters as select u.player,t.content from users as u,twitters as t where u.id = t.user_id; //要进行查询时 select * from curry_twitters; //删除视图 drop view curry_twitters;
15.使用事务
数据库事务:是指作为单个逻辑工作单元执行的一系列操作,要么一起成功,要么一起失败。必须满足acid(原子性、 一致性、隔离性、持久性)
postgresql
数据库事务使用
begin
开启事务
commit
提交
rollback
回滚
eg: begin; update users set score = 50 where player = '库里'; update users set score = 60 where player = '哈登'; commit; //如果不想跟新可以 rollback;
补充:postgresql一般crud存储过程参考
这里是一份经过再三调试测试而成功的postgres数据库单表crud存储过程,请注意,对于多结果的返回方式,请查看getpagebycondition的书写方式,用的是refcursor,返回一个cursor,同时可以返回其他out,inout参数,但是refcursor必须在事务中调用,所以java端的调用过程需要注意,好吧,我同时放出一份dal样板,大家可以直接copy来用。
/****************************************************************** * 表名:test3 * made by 码农下的天桥 ******************************************************************/ --use mydb;--你可以指定自己的数据库 /****************************************************************** ****************************各种常用查询*************************** ******************************************************************/ ------------------------------------ --用途:复杂形式的查询语句,用于查询分页数据。 --这个是泛用型的,假如你要根据用户输入去查询,那么最好不要用这个了, --以免出现sql注入。 --参数说明: ---_offset int 需要取的记录的开始位置 ---_limit int 需要获取记录的总条数,针对分页而言,就是分页的pagesize。 ---_columns varchar(800) 需要获取的字段 ---_where varchar(800) 需要过滤的条件譬如: where id<10 可以带where,不过建议不要带。 ---_orderby varchar(800) 需要进行排序的提交,譬如:order by id ---_totalcount int 返回总共记录条数。 ---_totalpages int 返回总共页数。 ------------------------------------ create or replace function test3_getlistbycondition( inout pageindex int, inout pagesize int, in _columns varchar(800), in _where varchar(800), in _orderby varchar(800), out _totalcount int, out _totalpages int) returns setof record as $$ declare condition_columns varchar(800); declare condition_where varchar(800); declare condition_orderby varchar(800); declare _dymatic_sql varchar(1600); declare _beginno int; declare _dynamic_getcount varchar(1600); declare _theoffset int; declare _tmpint1 int; begin condition_where:=ltrim(rtrim(coalesce(_where,''))); condition_orderby:=ltrim(rtrim(coalesce(_orderby,'order by t3id'))); condition_columns:=ltrim(rtrim(coalesce(_columns,'*'))); --分析传过来的参数,构造动态sql语句。 if "character_length"(condition_where)>0 then if strpos(condition_where, 'where ')!=1 then condition_where:='where ' || condition_where; end if; end if; --order by 语句构造 if "character_length"(condition_orderby)>0 then if strpos(condition_orderby, 'order ')!=1 then condition_orderby:='order by '||condition_orderby; end if; end if; --判断pageindex是否合法及pagesize是否合法 if pageindex<1 then pageindex:=1; end if; if pagesize<1 then pagesize:=20; end if; _dynamic_getcount:='select count(*) from test3 '||condition_where|| ' ' ; execute _dynamic_getcount into _totalcount; if _totalcount<1 then pageindex:=1; return; end if; --计算总共页数 _tmpint1:=_totalcount%pagesize; if _tmpint1=0 then _totalpages:=_totalcount / pagesize; else _totalpages:=(_totalcount-_tmpint1)/pagesize+1; end if; if _totalpages < pageindex then pageindex:=_totalpages; end if; _theoffset:=(pageindex-1) * pagesize+1; _dymatic_sql:='select '||condition_columns||' from test3 '||condition_where||' '||condition_orderby||' limit '||pagesize||' '|| ' offset '||_theoffset||' '; --raise info '动态构造语句为:%',_dymatic_sql; return query execute _dymatic_sql; end; $$ language plpgsql volatile; ------------------------------------ --用途:复杂形式的查询语句,用于查询多条记录数据。 --这个是泛用型的,假如你要根据用户输入去查询,那么最好不要用这个了, --以免出现sql注入。 --参数说明: ---_offset int 需要取的记录的开始位置 ---_limit int 需要获取记录的总条数,针对分页而言,就是分页的pagesize。 ---_columns varchar(800) 需要获取的字段 ---_where varchar(800) 需要过滤的条件譬如: where id<10 可以带where,不过建议不要带。 ---_orderby varchar(800) 需要进行排序的提交,譬如:order by id ---_totalcount int 返回总共记录条数。 ------------------------------------ create or replace function test3_getpagebycondition( inout pageindex int, inout pagesize int, in _columns varchar(800), in _where varchar(800), in _orderby varchar(800), out _totalcount int, out _totalpages int, out _refcursor refcursor ) returns setof record as $$ declare condition_columns varchar(800); declare condition_where varchar(800); declare condition_orderby varchar(800); declare _dymatic_sql varchar(1600); declare _beginno int; declare _dynamic_getcount varchar(1600); declare _theoffset int; declare _tmpint1 int; begin condition_where:=ltrim(rtrim(coalesce(_where,''))); condition_orderby:=ltrim(rtrim(coalesce(_orderby,'order by t3id'))); condition_columns:=ltrim(rtrim(coalesce(_columns,'*'))); --分析传过来的参数,构造动态sql语句。 if "character_length"(condition_where)>0 then if strpos(condition_where, 'where ')!=1 then condition_where:='where ' || condition_where; end if; end if; --order by 语句构造 if "character_length"(condition_orderby)>0 then if strpos(condition_orderby, 'order ')!=1 then condition_orderby:='order by '||condition_orderby; end if; end if; --判断pageindex是否合法及pagesize是否合法 if pageindex<1 then pageindex:=1; end if; if pagesize<1 then pagesize:=20; end if; _dynamic_getcount:='select count(*) from test3 '||condition_where|| ' ' ; execute _dynamic_getcount into _totalcount; if _totalcount<1 then pageindex:=1; return; end if; --计算总共页数 _tmpint1:=_totalcount%pagesize; if _tmpint1=0 then _totalpages:=_totalcount / pagesize; else _totalpages:=(_totalcount-_tmpint1)/pagesize+1; end if; if _totalpages < pageindex then pageindex:=_totalpages; end if; _theoffset:=(pageindex-1) * pagesize+1; _dymatic_sql:='select '||condition_columns||' from test3 '||condition_where||' '||condition_orderby||' limit '||pagesize||' '|| ' offset '||_theoffset||' '; --raise info '动态构造语句为:%',_dymatic_sql; open _refcursor for execute _dymatic_sql; return next; end; $$ language plpgsql volatile; ------------------------------------ --用途:获取其中一条记录 ------------------------------------ create or replace function test3_getrecord(in _id integer) returns setof test3 as $$ begin return query select * from test3 where t3id=_id limit 1 offset 0; end; $$ language plpgsql volatile; ------------------------------------ --用途:复杂形式的查询语句,用于查询前面第几条记录,这个就相当好了 --这个是泛用型的,假如你要根据用户输入去查询,那么最好不要用这个了, --以免出现sql注入。 --参数说明: ---_topn int 需要取的topn条记录。 ---_columns varchar(800) 需要获取的字段 ---_where varchar(800) 需要过滤的条件譬如: where id<10 可以带where,不过建议不要带。 ---_orderby varchar(800) 需要进行排序的提交,譬如:order by id ------------------------------------ create or replace function test3_gettopnbycondition(in _topn int,in _columns varchar(800),in _where varchar(800),in _orderby varchar(800)) returns setof test3 as $$ declare condition_columns varchar(800); declare condition_where varchar(800); declare condition_orderby varchar(800); declare _dymatic_sql varchar(1600); begin condition_where:=ltrim(rtrim(coalesce(_where,''))); condition_orderby:=ltrim(rtrim(coalesce(_orderby,'order by t3id'))); condition_columns:=ltrim(rtrim(coalesce(_columns,'*'))); --分析传过来的参数,构造动态sql语句。 if "character_length"(condition_where)>0 then if strpos(condition_where, 'where ')!=1 then condition_where:='where ' || condition_where; end if; end if; --order by 语句构造 if "character_length"(condition_orderby)>0 then if strpos(condition_orderby, 'order ')!=1 then condition_orderby:='order by '||condition_orderby; end if; end if; _dymatic_sql:='select '||condition_columns||' from test2 '||condition_where||' '||condition_orderby||' limit '||cast(_topn as varchar)|| ' offset 0 '; --raise info '动态构造语句为:%',_dymatic_sql; return query execute _dymatic_sql; end; $$ language plpgsql volatile; /****************************************************************** *****************************记录删除****************************** ******************************************************************/ ------------------------------------ --用途:删除多条记录 ------------------------------------ create or replace function test3_deletelist(in ids varchar(800),out status boolean,out msg varchar(200)) returns record as $$ declare _arr_ids int[]; declare _str_ids "text"; declare _str_sql varchar(1600); declare _effects int; begin if "character_length"(ids)<1 then status:=false; msg:='没有指定需要删除的数据!'; return; end if; _arr_ids:=tools_str2intarray(ids, ','); _str_ids:=tools_stringify(_arr_ids,','); --pkey为主键,自增的整数, <@ 表示判断pkey是不是在数组里面。是不是很方便? /*动态构造执行*/ --_str_sql:='delete from test3 where t3id in ('||_str_ids||') ;'; --execute _str_sql; /*直接执行*/ delete from test3 where t3id =any( _arr_ids); get diagnostics _effects = row_count; if _effects>0 then status:=true; msg:='成功删除'||_effects||'条记录!'; else status:=false; msg:='没有删除任何记录!'; end if; end $$ language plpgsql volatile; /****************************************************************** ****************************添加及编辑***************************** ******************************************************************/ ------------------------------------ --用途:增加一条记录 ------------------------------------ create or replace function test3_insert( in __t3name varchar(400) , in __t_birthday date , in __myage smallint , in __isadmin boolean , in __myintro text , in __price float , out __t3id integer, out _status boolean, out _msg varchar(200)) returns record as $$ begin insert into test3 ( "t3name","t_birthday","myage","isadmin","myintro","price" ) values( __t3name,__t_birthday,__myage,__isadmin,__myintro,__price ); /*判断添加记录是否成功。*/ if found then _status:=true; _msg:='成功添加记录.'; __t3id:=currval(pg_get_serial_sequence('test3', 't3id')); else _status:=false; _msg:='无法添加记录!'; end if; end; $$ language plpgsql volatile; ------------------------------------ --用途:修改一条记录 ------------------------------------ create or replace function test3_update( in __t3name varchar(400) , in __t_birthday date , in __myage smallint , in __isadmin boolean , in __myintro text , in __price float , in __t3id integer, out _status boolean, out _msg varchar(200)) returns record as $$ begin update test3 set "t3name"=__t3name,"t_birthday"=__t_birthday,"myage"=__myage,"isadmin"=__isadmin,"myintro"=__myintro,"price"=__price where t3id=__t3id; /*判断保存记录是否成功。*/ if found then _status:=true; _msg:='成功保存记录.'; else _status:=false; _msg:='无法保存记录!'; end if; end; $$ language plpgsql volatile;
对应dal调用文件:
package easisweb.dal; import easisweb.config.dbpool; import easis.common.stringutil; import easis.util.datarow; import easis.util.datatable; import easis.util.datatablehelper; import java.util.date; import easis.dbutility.pooledconnection; import java.sql.*; import java.util.list; import java.util.arraylist; import easis.util.operationresult; import easis.util.pagerresult; import easisweb.model.test3model; /** * 这是利用codegen工具生成的自动访问数据库的一个模板,作者为“码农下的天桥” *生成的类名称: * @author 码农下的天桥 * @version 1.00 */ public class test3dal { /*表格各种column*/ public static final string col_t3id="test3"; public static final string col_t3name="test3"; public static final string col_t_birthday="test3"; public static final string col_myage="test3"; public static final string col_isadmin="test3"; public static final string col_myintro="test3"; public static final string col_price="test3"; public static final string pkcolumn="t3id"; /** *存储过程名称:test3_listbycondition *存储过程参数: *@param pageindex *@param pagesize *@param columns 需要获取的字段 *@param condition where条件语句 *@param ordercolumn order by排序语句 * *@return 分页对象 */ public pagerresult getpagelistbycondition(int pageindex,int pagesize, string columns, string condition, string ordercolumn){ pagerresult pres=new pagerresult(); //output参数定义 int _total = 0 ; int _pagesize = 0 ; int _pageindex = 0 ; int _totalpages = 0 ; //output参数定义结束 //调用存储过程 datatable res__datatable=new datatable(); try{ pooledconnection __myconn=dbpool.getconnection(); __myconn.setautocommit(false); // return refcursor must within a transaction callablestatement _stmt=__myconn.preparecall("{ call test3_getpagebycondition( ?, ?, ?, ?, ?, ?, ?, ?)}"); _stmt.setint(1,pageindex); _stmt.setint(2,pagesize); _stmt.registeroutparameter(1,types.integer); _stmt.registeroutparameter(2,types.integer); _stmt.setstring(3,columns); _stmt.setstring(4,condition); _stmt.setstring(5,ordercolumn); _stmt.registeroutparameter(6, types.integer); _stmt.registeroutparameter(7, types.integer); _stmt.registeroutparameter(8,types.other); _stmt.execute(); resultset __rslist=(resultset)_stmt.getobject(8); res__datatable=datatablehelper.rs2datatable(__rslist); //取回参数 _total=_stmt.getint(6); pres.totalrecords=_total; _pageindex=_stmt.getint(1); pres.totalrecords=_total; _pagesize=_stmt.getint(2); pres.pageindex=_pageindex; pres.pagesize=_pagesize; _totalpages=_stmt.getint(7); pres.totalpages=_totalpages; pres.datasource=res__datatable; //--提交并还原 __myconn.commit(); __myconn.setautocommit(true); //返回游标必须在一个事务中,提交完以后将autocommit还原。 //释放资源 __rslist.close(); _stmt.close(); __myconn.close();} catch (exception __e){ system.out.println("在运行[test3dal]的list_condition时候出现错误。"); __e.printstacktrace(); } return pres; } /** *存储过程名称:test3_insert *存储过程参数: *param t3id 【主键】 t3name t_birthday myage isadmin myintro price * *@return */ public operationresult insert(test3model model){ operationresult __ores=new operationresult(); /*output参数定义*/ int t3id = 0 ; datarow returninfo=new datarow(); boolean status = false ; string message = "" ; /*output参数定义结束*/ /*调用存储过程*/ try{ pooledconnection __myconn=dbpool.getconnection(); callablestatement _stmt=__myconn.preparecall("{call test3_insert(?,?,?,?,?,?,?,?,?)}"); _stmt.setobject(1,model.t3name,types.varchar); _stmt.setobject(2,new timestamp(model.t_birthday.gettime()),types.date); _stmt.setobject(3,model.myage,types.smallint); _stmt.setobject(4,model.isadmin,types.boolean); _stmt.setobject(5,model.myintro,types.varchar); _stmt.setobject(6,model.price,types.float); _stmt.registeroutparameter(7,types.integer,-1); _stmt.registeroutparameter(8, types.boolean,1); _stmt.registeroutparameter(9, types.varchar,200); _stmt.execute(); /*取回参数*/ t3id=_stmt.getint(7); status=_stmt.getboolean(8); message=_stmt.getstring(9); __ores.id= t3id; __ores.status=status; __ores.message=message; /*释放资源*/ _stmt.close(); __myconn.close();} catch (exception __e){ __e.printstacktrace(); __ores.message=__e.tostring(); } return __ores; } /** *存储过程名称:test3_update *存储过程参数: * t3id【主键】 t3name t_birthday myage isadmin myintro price * *@return */ public operationresult update(test3model model){ operationresult __ores=new operationresult(); /*output参数定义*/ boolean status = false ; string message = "" ; /*output参数定义结束*/ /*调用存储过程*/ datatable res__datatable=new datatable(); try{ pooledconnection __myconn=dbpool.getconnection(); callablestatement _stmt=__myconn.preparecall("{ call test3_update( ?,?,?,?,?,?,?,?,?)}"); _stmt.setobject(1,model.t3name,types.varchar); _stmt.setobject(2,new timestamp(model.t_birthday.gettime()),types.date); _stmt.setobject(3,model.myage,types.smallint); _stmt.setobject(4,model.isadmin,types.boolean); _stmt.setobject(5,model.myintro,types.varchar); _stmt.setobject(6,model.price,types.float); _stmt.setint(7,model.t3id); _stmt.registeroutparameter(8, types.boolean,1); _stmt.registeroutparameter(9, types.varchar,400); _stmt.execute(); /*取回参数*/ status=_stmt.getboolean(8); message=_stmt.getstring(9); __ores.status=status; __ores.message=message; /*释放资源*/ _stmt.close(); __myconn.close();} catch (exception __e){ __e.printstacktrace(); } return __ores; } /** *存储过程名称:test3_deletelist *存储过程参数: *@param ids 【参数名称:ids 参数类型:nvarchar 对应java类型:string 长度:400 】 * *@return */ public operationresult deletelist( string ids){ /*output参数定义*/ operationresult __ores=new operationresult(); boolean status = false ; string message = "" ; /*output参数定义结束*/ /*调用存储过程*/ datatable res__datatable=new datatable(); try{ pooledconnection __myconn=dbpool.getconnection(); callablestatement _stmt=__myconn.preparecall("{ call test3_deletelist( ?, ?, ?)}"); _stmt.setstring(1,ids); _stmt.registeroutparameter(2, types.boolean,1); _stmt.registeroutparameter(3, types.varchar,400); _stmt.execute(); /*取回参数*/ status=_stmt.getboolean(2); message=_stmt.getstring(3); __ores.status=status; __ores.message=message; /*释放资源*/ _stmt.close(); __myconn.close();} catch (exception __e){ __e.printstacktrace(); } return __ores; } /** *存储过程名称:test3_getrecord *存储过程参数: *@param t3id 【参数名称:id 参数类型:int 对应java类型:int 长度:非字符类型 】 * *@return datatable对象。 */ public test3model getrecord( int t3id ){ /*调用存储过程*/ datatable res__datatable=new datatable(); test3model model=new test3model(); try{ pooledconnection __myconn=dbpool.getconnection(); callablestatement _stmt=__myconn.preparecall("{ call test3_getrecord( ?)}"); _stmt.setint(1,t3id); resultset __rslist =_stmt.executequery(); res__datatable=datatablehelper.rs2datatable(__rslist); model=tryparsemodel(res__datatable.get(0)); /*释放资源*/ __rslist.close(); _stmt.close(); __myconn.close();} catch (exception __e){ __e.printstacktrace(); } return model; } /** *存储过程名称:test3_top_condition *存储过程参数: *@param topn 【参数名称:topn 参数类型:int 对应java类型:int 长度:非字符类型 】 *@param columns 【参数名称:columns 参数类型:nvarchar 对应java类型:string 长度:800 】 *@param condition 【参数名称:condition 参数类型:nvarchar 对应java类型:string 长度:800 】 *@param ordercolumn 【参数名称:ordercolumn 参数类型:nvarchar 对应java类型:string 长度:800 】 * *@return datatable对象。 */ public datatable top_condition( int topn, string columns, string condition, string ordercolumn ){ /*调用存储过程*/ datatable res__datatable=new datatable(); try{ pooledconnection __myconn=dbpool.getconnection(); callablestatement _stmt=__myconn.preparecall("{ call test3_top_condition( ?, ?, ?, ?)}"); _stmt.setint(1,topn); _stmt.setstring(2,columns); _stmt.setstring(3,condition); _stmt.setstring(4,ordercolumn); resultset __rslist =_stmt.executequery(); res__datatable=datatablehelper.rs2datatable(__rslist); /*释放资源*/ __rslist.close(); _stmt.close(); __myconn.close();} catch (exception __e){ __e.printstacktrace(); } return res__datatable; } public test3model tryparsemodel(datarow drow){ test3model model=new test3model(); if(drow==null){ return model; } /* return "boolean"; return "date"; return "double"; return "float"; return "int"; return "long"; return "string"; return "object"; */ /*尝试赋值*/ model.t3id = drow.get("t3id").toint(); model.t3name = drow.get("t3name").tostring(); model.t_birthday = drow.get("t_birthday").todate(); model.myage = drow.get("myage").toint(); model.isadmin = drow.get("isadmin").toboolean(); model.myintro = drow.get("myintro").tostring(); model.price = drow.get("price").tofloat(); return model; } public list<test3model> tryparselist(list<datarow> datalist){ list<test3model> modellist=new arraylist<test3model>(); if(datalist==null){ return modellist; } for(datarow drow :datalist){ modellist.add(tryparsemodel(drow)); } return modellist; } }
这只是一份模板而已。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方,望不吝赐教。