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';