数据库教程:postgreSQL的crud操作详解

  ​ //命令行操作  ​ createdb database_name;  ​ create database database_name;  psql -l  //展示所有数据库  psql database_name //进入数据库  

  ​ dropdb database;  ​ drop database database_name;

  ​ 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 '这是一个学生信息表';

  ​ drop table students;

  insert into 表名(cloum1,cloum2) value(a,b);

  //分页查询limit和offset结合使用。   select * from users order by score desc limit 3;  select * from users order by score desc limit 3 offset 3;

  select team,max(score) from users group by team having max(score)>25 order by max(score);

   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;

  update [table] set [field=newvalue,…] where …  delete from [table] where …  

   update users set score = score + 100 where team in ("勇士","骑士");

  //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);

  //两表的关联查询  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;

  //创建一个视图(通过 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;   

  eg：  begin;  update users set score = 50 where player = '库里';  update users set score = 60 where player = '哈登';  commit; //如果不想跟新可以 rollback;

  /******************************************************************  * 表名：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;

  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;    }  }