DB2 730学习笔记7

iyth888

　　使用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)