DB2 730学习笔记7
使用INSERT语句在表或视图中添加新的行。INSERT INTO staff VALUES (1212,'Cerny',20,'Sales',3,90000.00,30000.00)
INSERT INTO staff VALUES (1213,'Cerny',20,'Sales',3,90000.00,30000.00)
也可以写成：
INSERT INTO staff (id,name,dept,job,years,salary,comm) VALUES (1212,'Cerny',20,'Sales',3,90000.00,30000.00),(1213,'Cerny',20,'Sales',3,90000.00,30000.00)
可以指定完全选择来表识出要从其它表或视图复制数据。
CREATE TABLE PERS LIKE staff
INSERT INTO pers SELECT id,name,dept,job,years,salary,comm FROM staff WHERE dept = 38
使用UPDATE语句修改表或视图中的数据。
UPDATE pers SET dept = 51,salary = 70000 where id = 30 或 UPDATE pers SET (dept,salary) = (51,70000) where id = 30
使用DELETE语句删除数据
DELETE FROM persWHERE id in (1212,1213)
使用MERGE语句将有条件更新、插入或删除操作组合起来。
创建表MY_EMP：
CREATE TABLE MY_EMP LIKE EMPLOYEE
插入测试数据：
INSERT INTO my_emp (empno, firstnme, midinit, lastname, workdept,phoneno, hiredate, job, edlevel, sex, birthdate, salary) VALUES ('000015', 'MARIO', 'M', 'MALFA', 'A00','6669', '05/05/2000', 'ANALYST', 15, 'M', '04/02/1973', 59000.00)
INSERT INTO my_emp (empno, firstnme, midinit, lastname, edlevel, salary) VALUES ('000010', 'CHRISTINE', 'I', 'HAAS', 18, 66600.00)
MERGE语句：
MERGE INTO employee AS e USING (SELECT empno, firstnme, midinit, lastname, workdept, phoneno,hiredate, job, edlevel, sex, birthdate, salary FROM my_emp) AS m
ON e.empno = m.empno
WHEN MATCHED THEN UPDATE SET (salary) = (m.salary)
WHEN NOT MATCHED THEN INSERT (empno, firstnme, midinit, lastname, workdept, phoneno,hiredate, job, edlevel, sex, birthdate, salary) VALUES (m.empno, m.firstnme, m.midinit, m.lastname,m.workdept, m.phoneno, m.hiredate, m.job, m.edlevel,m.sex, m.birthdate, m.salary)
页:
[1]