oracle中插入多行数据技巧--集合应用--TRUNCATE，delete区别

CREATE TABLE EMPINFO(
       EMP_ID VARCHAR(10) PRIMARY KEY,
       NAME VARCHAR(6),
       BIRTHDAY DATE,
       SAL NUMBER(4)
);
CREATE TABLE INTEG(
       INTEGER_ID VARCHAR(10) PRIMARY KEY,
       HABITUDE VARCHAR(10),
       SCORE NUMBER(4)
);
--建一张关系表 m-m
CREATE TABLE thing(
       EMP_ID CHAR(10),
       INTEGER_ID CHAR(10)
);
--alter SESSION SET NLS_DATE_FORMAT="YYYY-MM--DD";
--INSERT INTO EMPINFO VALUES('LEX_001','张三',2001-2-2,600);
INSERT INTO EMPINFO VALUES('LEX_001','张四',TO_DATE('2001-2-3','YYYY-MM-DD'),700);
INSERT INTO EMPINFO VALUES('LEX_002','张四',TO_DATE('2001-2-3','YYYY-MM-DD'),700);
INSERT INTO EMPINFO VALUES('LEX_003','张五',TO_DATE('2001-2-4','YYYY-MM-DD'),800);
INSERT INTO EMPINFO VALUES('LEX_004','张六',TO_DATE('2001-2-5','YYYY-MM-DD'),900);
INSERT INTO EMPINFO VALUES('LEX_005','张七',TO_DATE('2001-2-6','YYYY-MM-DD'),1000);
INSERT INTO EMPINFO VALUES('LEX_006','张八',TO_DATE('2001-2-7','YYYY-MM-DD'),1100);
INSERT INTO EMPINFO VALUES('LEX_007','张九',TO_DATE('2001-2-8','YYYY-MM-DD'),1200);
SELECT * FROM  EMPINFO;
INSERT INTO INTEG VALUES('I_001','公司类别',4);
INSERT INTO INTEG VALUES('I_002','学习类别',2.5);
INSERT INTO INTEG VALUES('I_003','协作类别',2.5);
INSERT INTO INTEG VALUES('I_004','其他类别',1);

INSERT INTO thing VALUES('IEX_0001','I_001');
INSERT INTO thing VALUES('IEX_0002','I_002');
INSERT INTO thing VALUES('IEX_0003','I_003');
INSERT INTO thing VALUES('IEX_0004','I_004');

 

--第二次：

SELECT E.EMP_ID,E.NAME,E.BIRTHDAY,E.SAL
FROM EMPINFO E
WHERE E.SAL >= all (--SOME相等用some -->= <=等用some
SELECT EMP.SAL FROM EMPINFO EMP
--
where EMP.EMP_ID = E.EMP_ID
);
SELECT E.EMP_ID,E.NAME,E.BIRTHDAY,E.SAL
FROM EMPINFO E
GROUP BY E.EMP_ID,E.NAME,E.BIRTHDAY,E.SAL

SELECT E.SAL,COUNT(*)
FROM EMPINFO E
GROUP BY E.SAL
HAVING COUNT(*)>1;
--SAL为700的有两个

SELECT * FROM EMPINFO;
SELECT E.SAL--,COUNT(*)
FROM EMPINFO E
GROUP BY E.SAL
--HAVING COUNT(*)>0
ORDER BY e.SAL DESC;--ORDER BY SAL也可以多列排序 当有两条记录相同

select E.EMP_ID as 用户ID FROM  EMPINFO E
UNION ALL       --UNION ALL加上all去掉重复记录，并且字段必须相同
SELECT T.EMP_ID FROM  THING T;-- 用户ID不能放在这里，不能改变名字

select E.EMP_ID as 用户ID FROM  EMPINFO E
Intersect        --交集
SELECT T.EMP_ID FROM  THING T;

select E.EMP_ID as 用户ID FROM  EMPINFO E
MINUS       --差集
SELECT T.EMP_ID FROM  THING T;
--由于thing表没有score项
--error
UPDATE SET SAL = SAL*(SELECT SCORE FROM thing
WHERE INTEG_ID = 'I_003');
SELECT * FROM THING
where EMP_ID=''
ALTER TABLE THING ADD SCORE NUMBER(5);
--INSERT INTO THING(,,S想在已经存在的表中插入单行的score分数
SELECT * FROM THING
DELETE FROM B;
TRUNCATE TABLE B;--非常危险的删除项

--插入多行
insert INTO EMPINFO (EMP_ID,NAME,BIRTHDAY,SAL )
SELECT 'LEX_005','张七',TO_DATE('2001-2-6','YYYY-MM-DD'),1000 from dual
union 
SELECT 'LEX_006','张八',TO_DATE('2001-2-7','YYYY-MM-DD'),1100 from dual
union
SELECT 'LEX_007','张九',TO_DATE('2001-2-8','YYYY-MM-DD'),1200 from dual

select * from EMPINFO ;