PostgreSQL基本操作二（新建表，插入数值，natural join，修改数值，添加列和默认值）

1、新建两个如下的table

create table Employee(
	EmployeeID serial primary key,
	name varchar(30),
	surname varchar(30),
	departmentname varchar(30),
	annualsalary int);

create table Department(
	DepartmentID serial primary key,
	departmentname varchar(30),
	Manager varchar(30));

2、给两个表中插入数值

insert into department(departmentname,manager)
values ('Finance', 'George'), ('Sales', 'Harriet'), ('Producion', 'Charles');

insert into employee (name, surname, departmentname, annualsalary)
values ('Harry', 'Smith', 'Finance',22000) , ('Sally', 'Jones', 'Sales', 31000),('George', 'Taylor', 'Finance', 30000), ('Harriet', 'Davies', 'Sales', 20000),('Charlie', 'Evans', 'Producion', 40000),('Clementine', 'Wilson', 'Producion', 38000);

3、用natural join连接两个表（natural join是等值连接，不用专门指定连接条件，系统自动寻找两个表中相同的值匹配）

select * from department natural join employee;

结果：

4、在表employee中，把Charlie的department改为“Sales”，工资改为30000

update employee 
set departmentname='Sales', annualsalary=30000
where name='Charlie';

5、将“budget”列添加到department表中，默认值为130000

alter table department add budget int default 130000;

update是修改表中的数值，alter是对表进行处理，详见alter的使用

6、修改budget，finance为130000，Sales为230000，Production为180000

update department 
set budget=230000 
where departmentname='Sales';
update department 
set budget=180000
where departmentname='Producion';