1.首先创建两个表 sql语句如下:
users:(用户表)
CREATE TABLE users(id bigint,departid bigint,name text)
depart:(部门表)
CREATE TABLE depart(id bigint,departname text)
users表中的departid与depart中的id相关联。
2.插入数据
users:(用户表)
insert into users(id,departid,name) values(1,1,'a')insert into users(id,departid,name) values(2,2,'b')insert into users(id,departid,name) values(3,3,'c')
depart:(部门表)
insert into depart(id,departname) values(1,'b')insert into depart(id,departname) values(2,'c')3.插入后可以select,查看结果select * from usersselect * from depart4.update语句:a.更新users(用户)表中name为a的,部门departname名字为b的users名字为g。--错误:此处更新所有的users表信息,可以与下边的sql进行比较,发from后存在users u,只要where为true,即更新所有的users。update users set name='g' from users u,depart d where u.departid=d.id and d.departname='b' and u.name='a'--正确update users u set name='g' from depart d where u.departid=d.id and d.departname='b' and u.name='a'--正确update users set name='k' where departid = (select d.id from depart d,users u where u.departid=d.id and d.departname='b' and u.name='a')数据恢复sqlupdate users set name = 'a' where id = 1update users set name = 'b' where id = 2update users set name = 'c' where id = 3