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