除了序列（Sequence）和同义词（Synonym）外，其他语句基本上都在自己创建的iTerm数据中用过了，所以这章感觉不难。

--第7章开始 --7.1.2 插入单行记录 SELECT * FROM emp; INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno ) VALUES (7890, '刘七', '副理', 7566, TO_DATE ('2001-08-15', 'YYYY-MM-DD'), 8000, 300, 20 ); INSERT INTO emp VALUES (7891, '刘七', '副理', 7566, TO_DATE ('2001-08-15', 'YYYY-MM-DD'), 8000, 300, 20); --7.1.3 插入默认值和Null值 --默认值 INSERT INTO emp (empno, ename, deptno ) VALUES (7892, '张八', 20 ); SELECT * FROM emp WHERE empno = 7892; INSERT INTO emp (empno, ename, deptno ) VALUES (7892, '张八', 20 ); INSERT INTO emp (empno, ename, deptno ) VALUES (7898, 'O''Malley', 20 ); -- 插入NUll值 SELECT * FROM emp; INSERT INTO emp VALUES(7893,'霍九',NULL,NULL,NULL,NULL,NULL,20); INSERT INTO emp VALUES(7894,'霍十','',NULL,'',NULL,NULL,20); INSERT INTO emp VALUES(7895,USER,NULL,NULL,TRUNC(SYSDATE),3000,200,20); SELECT * FROM emp WHERE empno = 7895; --7.1.4 使用子查询插入多行数据 DROP TABLE emp_copy; CREATE TABLE emp_copy AS SELECT * FROM emp WHERE 1=2; INSERT INTO emp_copy SELECT * FROM emp WHERE deptno = 20; SELECT * FROM emp; INSERT INTO emp_copy SELECT * FROM emp WHERE deptno = 20; --指定要插入数据的列名 INSERT INTO emp_copy (empno, ename, job, mgr, deptno) SELECT empno, ename, job, mgr, deptno FROM emp WHERE deptno = 30; --使用Insert插入多表数据 CREATE TABLE emp_dept_10 AS SELECT * FROM emp WHERE 1=2; CREATE TABLE emp_dept_20 AS SELECT * FROM emp WHERE 1=2; CREATE TABLE emp_dept_30 AS SELECT * FROM emp WHERE 1=2; CREATE TABLE emp_copy AS SELECT * FROM emp WHERE 1=2; INSERT FIRST WHEN deptno = 10 --如果部门编号为10 THEN INTO emp_dept_10 --则插入到emp_dept_10表 WHEN deptno = 20 --如果部门编号为20 THEN INTO emp_dept_20 --则插入到emp_dept_20表 WHEN deptno = 30 --如果部门编号为30 THEN INTO emp_dept_30 --则插入到emp_dept_30表 ELSE --如果deptno不为10、20或者是30 INTO emp_copy --则插入到emp_copy 表 SELECT * FROM emp; --查询emp表中的所有数据，插入到目标表 SELECT rowid,x.* from emp x; select * from emp_dept_20 TRUNCATE TABLE emp_dept_10 ; TRUNCATE TABLE emp_dept_20 ; TRUNCATE TABLE emp_dept_30 ; TRUNCATE TABLE emp_copy ; INSERT FIRST WHEN deptno = 10 --如果部门编号为10 THEN INTO emp_dept_10 --插入到emp_dept_10，使用VALUES指定字段 (empno, ename, sal, deptno) VALUES (empno, ename, sal, deptno) WHEN deptno = 20 --如果部门编号为20 THEN INTO emp_dept_20 --插入到emp_dept_20，使用VALUES指定字段 (empno, ename) VALUES (empno, ename) WHEN deptno = 30 --如果部门编号为30 THEN INTO emp_dept_30 --插入到emp_dept_30，使用VALUES指定字段 (empno, ename, hiredate) VALUES (empno, ename, hiredate) ELSE --如果部门编号即不为10、20或30 INTO emp_copy --插入到emp_copy，使用VALUES指定字段 (empno, ename, deptno) VALUES (empno, ename, deptno) SELECT * FROM emp; --指定插入子查询 commit; SELECT * FROM emp_dept_10; --7.2.1更新单行记录 SELECT * FROM emp; UPDATE emp SET sal=3000 WHERE empno=7369; UPDATE emp SET sal=3000,comm=200,mgr=7566 WHERE empno=7369; SELECT * FROM emp; SELECT AVG(y.sal) FROM emp y WHERE y.deptno=20; --7.2.2使用子查询更新记录 UPDATE emp x SET x.sal = (SELECT AVG (y.sal) FROM emp y WHERE y.deptno = x.deptno) WHERE x.empno = 7369; UPDATE emp SET sal = (SELECT sal FROM emp WHERE empno = 7782) WHERE empno = 7369; select * from emp_history; DROP TABLE emp_history; UPDATE emp x SET (x.sal, x.comm) = (SELECT AVG (y.sal), MAX (y.comm) FROM emp y WHERE y.deptno = x.deptno) WHERE x.empno = 7369; CREATE TABLE emp_history AS SELECT * FROM emp; UPDATE emp_history x SET (x.sal, x.comm) = (SELECT sal, comm FROM emp y WHERE y.empno =x.empno ) WHERE x.empno = 7369; SELECT * FROM emp_history; SELECT * from emp; --使用多表关联的形式进行更新 UPDATE (SELECT x.sal sal, y.sal sal_history, x.comm comm, y.comm comm_history FROM emp x, emp_history y WHERE x.empno = y.empno AND x.empno = 7369) SET sal_history = sal, comm_history = comm; UPDATE /*+bypass_ujvc*/ (SELECT x.sal sal, y.sal sal_history, x.comm comm, y.comm comm_history FROM emp x, emp_history y WHERE x.empno = y.empno AND x.empno = 7369) SET sal_history = sal, comm_history = comm; DELETE FROM emp WHERE empno=7894 INSERT INTO emp VALUES(7894,'霍十','',DEFAULT,'',NULL,NULL,20); --7.2.3 使用Merge合并表行 SELECT * FROM emp_copy; SELECT * FROM emp; MERGE INTO emp_copy c --目标表 USING emp e --源表，可以是表、视图或子查询 ON (c.empno = e.empno) WHEN MATCHED THEN --当匹配时，进行UPDATE操作 UPDATE SET c.ename = e.ename, c.job = e.job, c.mgr = e.mgr, c.hiredate = e.hiredate, c.sal = e.sal, c.comm = e.comm, c.deptno = e.deptno WHEN NOT MATCHED THEN --当不匹配时，进行INSERT操作 INSERT VALUES (e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno); --7.3.1 删除单行记录 DELETE FROM emp WHERE empno=7903; DELETE FROM dept WHERE deptno=20; SELECT * FROM dept; --7.3.2 使用子查询删除记录 DELETE FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = '销售部'); DELETE FROM emp_copy; DELETE FROM emp x WHERE EXISTS (SELECT 1 FROM emp_copy WHERE empno = x.empno); --等价语句 DELETE FROM emp x WHERE empno IN (SELECT empno FROM emp_copy WHERE empno = x.empno); --7.3.3 使用Truncate清除表数据 TRUNCATE TABLE dept; ALTER TABLE dept ENABLE CONSTRAINT pk_dept; ALTER TABLE emp DISABLE CONSTRAINT PK_EMP; CREATE TABLE dept_copy AS SELECT * FROM dept; TRUNCATE TABLE dept; INSERT INTO dept SELECT * FROM dept_copy; ----7.5.2 创建数据序列 CREATE SEQUENCE invoice_seq INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE; SELECT object_name,object_id,object_type FROM user_objects WHERE object_name = 'INVOICE_SEQ'; SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences; SELECT invoice_seq.CURRVAL,invoice_seq.NEXTVAL FROM DUAL; SELECT invoice_seq.CURRVAL FROM DUAL; SELECT invoice_seq.CURRVAL,invoice_seq.NEXTVAL FROM DUAL; CREATE TABLE invoice ( invoice_id NUMBER PRIMARY KEY, --自动编号，唯一，不为空 vendor_id NUMBER NOT NULL, --供应商ID invoice_number VARCHAR2(50) NOT NULL, --发票编号 invoice_date DATE DEFAULT SYSDATE, --发票日期 invoice_total NUMBER(9,2) NOT NULL, --发票总数 payment_total NUMBER(9,2) DEFAULT 0 --付款总数 ) select * from invoice; INSERT INTO invoice (invoice_id, vendor_id, invoice_number, invoice_total ) VALUES (invoice_seq.NEXTVAL, 10, 'INV' || invoice_seq.CURRVAL, 100 ); SELECT invoice_id, vendor_id, invoice_number, invoice_total FROM invoice; ALTER SEQUENCE invoice_seq INCREMENT BY 2 ALTER SEQUENCE invoice_seq INCREMENT BY 2 MAXVALUE 10 NOCACHE NOCYCLE; SELECT * FROM SCOTT.EMP; SELECT * FROM emp; SELECT userenv('LANG') FROM DUAL; CREATE PUBLIC SYNONYM scottemp FOR scott.emp; DROP PUBLIC SYNONYM scottemp;