一、插入数据
INSERT INTO dept(deptno,dname,loc)
VALUES (50,'DEVELOPMENT','DEOROIT');
1. 插入空值
(1)隐含法
INSERT INTO dept(deptno,dname)
VALUES (60,'MIS');
(2)显示法
INSERT INTO dept
VALUES (70,'FINANCE',NULL);
2. 插入日期值
INSERT INTO emp (empno,ename,job,
mgr,hiredate,sal,comm,deptno)
VALUES(7196,'GREEN','SALSMAN',7782,SYSDATE,2000,NULL,10);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(2296,'AROMANO','SALSMAN',7782,
TO_DATE('2月 3,1997','MON DD,YYYY'),1300,NULL,10);
CREATE TABLE manager AS
SELECT * FROM emp WHERE 1=0;
INSERT INTO manager
SELECT * FROM emp WHERE job='MANAGER';
二、修改数据
UPDATE emp SET deptno=20 WHERE empno=7782;
UPDATE emp SET deptno=20;
一次修改多个列
UPDATE emp SET deptno=20,sal=sal+100 WHERE deptno=10;
嵌套子查询修改:
把部门编号为10的员工,编号调整为20,工资在原有基础上,增加所有人的平均工资。
UPDATE emp
SET deptno=20,
sal=sal+(SELECT AVG(sal) FROM emp)
WHERE deptno=10;
ALTER TABLE emp
ADD(dname VARCHAR2(14));
UPDATE emp e
SET dname=(SELECT dname
FROM dept d
WHERE deptno=e.deotno)
三、删除数据
- DELETEFROM emp
WHERE job='CLERK';
- DELETEFROM emp;
- DELETEFROM emp
WHERE deptno=(SELECT deptno
FROM dept
WHERE dname='SALES');
- 删除有工作记录的员工
DELETE FROM emp e
WHERE 0<(SELECT count(empno)
FROM emp_jobhistory
WHERE empno=e.empno);
练习
- INSERTINTO dept
VALUES(80,'HR','SY');
- INSERTINTO dept
VALUES(90,'MARKET',NULL);
- INSERTINTO emp
VALUES(8888,'BOB','CLERK',7788,
TO_DATE('1985-03-03','YYYY-MM-DD'),3000,NULL,NULL);
- CREATETABLE emp_back AS
SELECT * FROM emp WHERE 1=0;
INSERT INTO emp_back
SELECT * FROM emp WHERE hiredate>TO_DATE('1982-1-1','YYYY-MM-DD');
- UPDATEemp SET comm=0 WHERE comm IS NULL;
- UPDATEemp SET sal=sal+500
WHERE deptno IN (SELECT deptno
FROM dept WHERE loc='NEW YORK'
OR loc='CHICAGO');
SELECT * FROM emp;
- UPDATEemp SET hiredate=hiredate+10 WHERE
deptno=20
AND hiredate>TO_DATE('1982','yyyy');