1 实现代码----orcale 插入删除更新 create table pet( name varchar(20) primary key, owner varchar(20), species varchar(20) not null, sex varchar(1) not null, birth date not null, death date, CONSTRAINT ck_sex CHECK (sex in('m','f')) ); --插入测试数据 insert into pet values ('Fluffy','Harold','cat','f','2003-10-12','2010-08-12'); insert into pet values ('Claws','Gwen','cat','m','2004-08-10',''); insert into pet values ('Buffy','null','dog','f','2009-08-11',''); insert into pet values ('Fang','Benny','dog','m','2000-05-15',''); insert into pet values ('Bowser','Diane','dog','m','2003-04-16','2009-11-12'); insert into pet values ('Chirpy','null','bird','f','2008-05-19',''); select * from pet;
2 实现代码update pet set owner ='Kevin' where name ='Fang'; commit;
3 实现代码update pet set owner ='Duck' where Owner is null; commit;
4 实现代码 delete from pet where death is not null; commit;
5 实现代码 delete from pet; commit;
1.创建表:
创建员工表employee,字段如下:
id(员工编号),name(员工名字),gender(员工性别),salary(员工薪资)
2.插入数据
1,‘张三’,‘男’,2000
2,‘李四’,‘男’,1000
3,‘王五’,‘女’,4000
3.修改表数据
3.1 将所有员工薪水修改为5000元
3.2将姓名为张三的员工薪水修改为3000元
3.3将姓名为李四的员工薪水修改为4000元,gener改为女
3.4 将王五的薪水在原有基础上增加1000元
CREATE TABLE IF NOT EXISTS employee(
id INT,
name VARCHAR(20),
gender VARCHAR(2),
salary INT
);
INSERT INTO employee VALUES(1,'张三','男',2000),
(2,'李四','男',1000),
(3,'王五','女',4000);
UPDATE employee SET salary = 5000;
UPDATE employee set salary = 3000 WHERE name ='张三';
UPDATE employee SET salary = 4000 , gender='女' WHERE name='李四';
UPDATE employee SET salary= salary +1000 WHERE name='王五';