postgresql-表的联接、插入、修改、更新

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

进入解释器

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

修改表结构
mydb=# alter table student add city int;
ALTER TABLE
mydb=# select * from student
mydb-# ;
??? name??? | age | city
------------+-----+------
?deepfuture |? 20 |
?未来?????? |? 20 |
(2 rows)

更新表
mydb=# update student set city=1 where name='deepfuture';
UPDATE 1
mydb=# update student set city=1 where name='未来';
UPDATE 1
mydb=# select * from student;
??? name??? | age | city
------------+-----+------
?deepfuture |? 20 |??? 1
?未来?????? |? 20 |??? 1
(2 rows)

mydb=# update student set city=2 where name='未来';
UPDATE 1

插入表

mydb=# insert into student values ('张三',21,1);
INSERT 0 1
mydb=# select * from student;
??? name??? | age | city
------------+-----+------
?deepfuture |? 20 |??? 1
?未来?????? |? 20 |??? 2
?张三?????? |? 21 |??? 1
(3 rows)

?创建新表并插入数据
mydb=# create table citys(name varchar(20),id int);
CREATE TABLE
mydb=# insert into citys values ('长沙',1);
INSERT 0 1
mydb=# insert into citys values ('湛江',2);
INSERT 0 1
mydb=# insert into citys values ('北京',3);
INSERT 0 1
mydb=# select * from citys
mydb-# ;
?name | id
------+----
?长沙 |? 1
?湛江 |? 2
?北京 |? 3
(3 rows)

表的联接
mydb=# select * from student,citys where student.city=citys.id;
??? name??? | age | city | name | id
------------+-----+------+------+----
?deepfuture |? 20 |??? 1 | 长沙 |? 1
?张三?????? |? 21 |??? 1 | 长沙 |? 1
?未来?????? |? 20 |??? 2 | 湛江 |? 2
(3 rows)

mydb=# select student.name as name,student.age as age,citys.name as city from st
udent,citys where student.city=citys.id;
??? name??? | age | city
------------+-----+------
?deepfuture |? 20 | 长沙
?张三?????? |? 21 | 长沙
?未来?????? |? 20 | 湛江
(3 rows)

mydb=#

?