postgresql-视图、删除记录

声明：本PostgreSQl实用指南系列为刘兴(http://deepfuture.iteye.com/)原创，未经笔者授权，任何人和机构不能转载

D:\pgsql>psql mydb
psql (8.4.2)
Type "help" for help.

删除记录
mydb=# delete from citys where name='北京';
DELETE 1
基本查询

mydb=# select * from citys
mydb-# ;
 name | id
------+----
 长沙 |  1
 湛江 |  2
(2 rows)

 

mydb=# select * from student
mydb-# ;
    name    | age | city
------------+-----+------
 deepfuture |  20 |    1
 未来       |  20 |    2
 张三       |  21 |    1
(3 rows)

mydb=# insert into citys values('上海',3)
mydb-# ;
INSERT 0 1
mydb=# select * from citys;
 name | id
------+----
 长沙 |  1
 湛江 |  2
 上海 |  3
(3 rows)

mydb=# select citys.name, count(*) from citys left outer join student on (studen
t.city=citys.id) group by citys.name,student.city;
 name | count
------+-------
 上海 |     1
 湛江 |     1
 长沙 |     2
(3 rows)

mydb=# select citys.name, count(student.city) from citys left outer join student
 on (student.city=citys.id) group by citys.name,student.city;
 name | count
------+-------
 上海 |     0
 湛江 |     1
 长沙 |     2
(3 rows)

创建视图

mydb=# create view citylist as select citys.name as 城市, count(student.city) as
 人数 from citys left outer join student on (student.city=citys.id) group by cit
ys.name,student.city;
CREATE VIEW

调用视图
mydb=# select * from citylist
mydb-# ;
 城市 | 人数
------+------
 上海 |    0
 湛江 |    1
 长沙 |    2
(3 rows)

mydb=#