查询条件 | 谓词 |
---|---|
比较 | >,<,>=,<=,!=,<>,!>,!<,=,not+上述比较符,any/all+上述比较符 |
确定范围 | between and ,not between and |
确定集合 | in,not in |
字符匹配 | like,not like |
空值 | is null,is not null |
多重条件 | and ,or,not |
1.临时派生表
select * from table_name ,(select * from table_name) as new_table_name(column_name,column_name);
2.创建视图
create view view_name(column_name,column_name) as <子查询>
数据库中保存视图的定义,不保存视图中的数据,按照某种查询条件得到数据存放在视图中。
3.删除视图
drop view index_name;
4.更新视图
update view_name set column_name=value where ...
不是所有的视图都允许更新。
5.查找数据库中所有的view名
select * from pg_views;
获取数据库中所有的表名:
select * from pg_tables;
至于想看表中有哪些字段,可以limit 1,或是有这样的语句,还没有找到;
6.查看数据库中某个表的全部字段,及其字段类型
SELECT col_description(a.attrelid,a.attnum) as comment,pg_type.typname as typename,a.attname as name, a.attnotnull as notnull FROM pg_class as c,pg_attribute as a inner join pg_type on pg_type.oid = a.atttypid where c.relname = 'tablename' and a.attrelid = c.oid and a.attnum>0
只需要修改tablename即可。