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=#