📄 4.19.txt
字号:
--创建部门表
DROP TABLE DEPARTMENT;
CREATE TABLE DEPARTMENT (
DEPTID NUMBER(3),
DEPTNAME VARCHAR2(20),
LEADER VARCHAR2(10)
);
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(1,'JAVA','TOMCAT');
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(2,'MARKETING','HARRY');
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(3,'HR','ANN');
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(4,'TEST','ROSE');
COMMIT;
SELECT * FROM DEPARTMENT;
--创建员工表
DROP TABLE EMPLOYEE ;
CREATE TABLE EMPLOYEE(
EMPID NUMBER(6),
EMPNAME VARCHAR2(10),
DEPTID NUMBER(3),
SALARY NUMBER(10,3),
BIRTHDAY DATE
);
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(1 ,'ZHAO',1,1000.33, TO_DATE('1980-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(2 ,'QIAN',1,1000.33, TO_DATE('1981-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(3 ,'SUNN',2,3000.33, TO_DATE('1982-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(4 ,'LI',2,4000.33, TO_DATE('1983-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(5 ,'ZHOU',8,5000.33, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(6 ,'ZHOU',8,6000.33, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(7 ,'ZHOU',3,9000.33, TO_DATE('1984-09-10','YYYY-MM-DD'));
COMMIT;
SELECT * FROM EMPLOYEE;
--关联查询 查询JAVA部门所有人员姓名，工资
--1 先在部门表查询部门ID
SELECT DEPTID FROM DEPARTMENT WHERE DEPTNAME ='JAVA'
--2 根据查询的部门ID，在员工表查询其它信息
SELECT E.EMPID,E.EMPNAME,E.SALARY FROM EMPLOYEE E WHERE E.DEPTID = 1
--查询所有部门的人员信息
SELECT D.DEPTID AS DID,E.DEPTID AS EID,D.DEPTNAME,E.EMPNAME,E.SALARY
FROM DEPARTMENT D,EMPLOYEE E
WHERE D.DEPTID = E.DEPTID ;
--通过 D.DEPTNAME='JAVA'去掉不需要的记录
SELECT D.DEPTNAME,E.EMPNAME,E.SALARY FROM DEPARTMENT D,EMPLOYEE E
WHERE D.DEPTID = E.DEPTID AND D.DEPTNAME='JAVA';
-- (+)放在缺少信息的一边
--查询各部门都有哪些员工
SELECT D.DEPTNAME,E.EMPID,E.EMPNAME,E.SALARY FROM EMPLOYEE E,DEPARTMENT D
WHERE D.DEPTID = E.DEPTID(+);
--查询所有员工都在那个部门
SELECT D.DEPTNAME,E.EMPID,E.EMPNAME,E.SALARY FROM EMPLOYEE E,DEPARTMENT D
WHERE D.DEPTID(+) = E.DEPTID;
--标准SQL
SELECT
D.DEPTNAME,E.EMPNAME,E.SALARY FROM DEPARTMENT D
JOIN EMPLOYEE E
ON D.DEPTID = E.DEPTID;
SELECT
D.DEPTNAME,E.EMPNAME,E.SALARY FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E
ON D.DEPTID = E.DEPTID;
SELECT
D.DEPTNAME,E.EMPNAME,E.SALARY FROM DEPARTMENT D
RIGHT JOIN EMPLOYEE E
ON D.DEPTID = E.DEPTID;
--子查询
--1 先在部门表查询部门ID
SELECT DEPTID FROM DEPARTMENT WHERE DEPTNAME ='JAVA'
--2 根据查询的部门ID，在员工表查询其它信息
SELECT E.EMPID,E.EMPNAME,E.SALARY FROM EMPLOYEE E
WHERE E.DEPTID = 1
-- 使用子查询
SELECT E.EMPID,E.EMPNAME,E.SALARY FROM EMPLOYEE E
WHERE E.DEPTID = (SELECT DEPTID FROM DEPARTMENT WHERE DEPTNAME ='JAVA')
--查询ZHAO所在的部门名称
SELECT DEPTID FROM EMPLOYEE WHERE EMPNAME = 'ZHAO';
SELECT DEPTNAME FROM DEPARTMENT WHERE DEPTID = 1;
SELECT DEPTNAME FROM DEPARTMENT
WHERE DEPTID = (SELECT DEPTID FROM EMPLOYEE WHERE EMPNAME = 'ZHAO')
-- IN ANY ALL
--用于多行子查询
SELECT * FROM EMPLOYEE
WHERE DEPTID IN (SELECT DEPTID FROM DEPARTMENT)
-->ANY 大于最小值
SELECT * FROM EMPLOYEE
WHERE SALARY >ANY (SELECT SALARY FROM EMPLOYEE WHERE DEPTID =8)
--<ANY 小于最大值
SELECT * FROM EMPLOYEE
WHERE SALARY <ANY (SELECT SALARY FROM EMPLOYEE WHERE DEPTID =8)
-->ALL 大于最大值
SELECT * FROM EMPLOYEE
WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DEPTID =8)
--<ALL 小于最小值
SELECT * FROM EMPLOYEE
WHERE SALARY < ALL (SELECT SALARY FROM EMPLOYEE WHERE DEPTID =8)
--从查询结果中查询
select empid from (select * from employee)
select empid from (select empid,salary from employee)
select num from (select empid num,salary from employee)
--Top n
select a.*, rownum
from (
select * from EMPLOYEE order by SALARY desc
) a
where rownum <= 10
--查询工资排名为6到10的信息
select *
from (
select a.*, rownum as rownum1
from (select * from EMPLOYEE order by salary desc) a
where rownum <= 10
)
where rownum1 > 5
--查询工资排名为6到10的信息
SELECT *
FROM (
SELECT EMPID,EMPNAME,SALARY , ROWNUM AS ROWNUM1
FROM (SELECT EMPID,EMPNAME,SALARY FROM EMPLOYEE WHERE SALARY IS NOT NULL ORDER BY SALARY DESC)
WHERE ROWNUM <=10
)
WHERE ROWNUM1 > 5
--ex
--创建部门表
DROP TABLE DEPARTMENT;
CREATE TABLE DEPARTMENT (
DEPTID NUMBER(3),
DEPTNAME VARCHAR2(20),
LEADER VARCHAR2(10)
);
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(1,'JAVA','TOMCAT');
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(2,'MARKETING','HARRY');
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(3,'HR','ANN');
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(4,'TEST','ROSE');
INSERT INTO DEPARTMENT (DEPTID,DEPTNAME,LEADER)
VALUES(9,'OFFICE','ROSE');
COMMIT;
SELECT * FROM DEPARTMENT;
DROP TABLE EMPLOYEE ;
CREATE TABLE EMPLOYEE(
EMPID NUMBER(6),
EMPNAME VARCHAR2(10),
DEPTID NUMBER(3),
SALARY NUMBER(10,3),
BIRTHDAY DATE
);
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(1 ,'ZHAO',1,1234.33, TO_DATE('1980-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(2 ,'QIAN',1,1234.33, TO_DATE('1981-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(3 ,'SUNN',2,8888.33, TO_DATE('1982-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(4 ,'LI',2,8888.33, TO_DATE('1983-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(5 ,'ZHOU',9,5000, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(6 ,'ZHOU',8,4444.33, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(7 ,'ZHOU',8,3333, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(8 ,'ZHOU',8,2222, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(9 ,'ZHOU',8,2222, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(10 ,'ZHOU',8,5555, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(11 ,'ZHOU',8,5555, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(12 ,'ZHOU',8,3344, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(13 ,'ZHOU',8,4433, TO_DATE('1984-09-10','YYYY-MM-DD'));
INSERT INTO EMPLOYEE(EMPID,EMPNAME,DEPTID ,SALARY ,BIRTHDAY )
VALUES(14 ,'ZHOU',8,2233, TO_DATE('1984-09-10','YYYY-MM-DD'));
COMMIT;
SELECT * FROM EMPLOYEE;
--单表
--查询EMPLOYEE表全部信息
--给JAVA部门每人工资增加5000
--给JAVA部门增加一个员工 姓名：HARRY，工资：60000
--HARRY没有通过试用期，删除他的相关信息
--查询本月过生日的员工姓名和生日
--查询比ZHAO工资高的全部员工信息
--查询工资最高的前2个员工
--多表关联 子查询
--查询公司各部门都有哪些员工
--查询公司员工都在什么部门
--查询JAVA部所有员工的信息
-- 把MARKETING部门的LEE转到JAVA部
--分别查询各部门的平均工资，员工数量，每个部门工资总和，各部门最高工资和最低工资
--查询比JAVA部门的工资高的全部员工姓名和所在部门
SELECT D.DEPTNAME ,E.EMPNAME ,E.SALARY FROM DEPARTMENT D , EMPLOYEE E
WHERE E.SALARY > ALL(
SELECT E.SALARY FROM EMPLOYEE E, DEPARTMENT D
WHERE D.DEPTID = E.DEPTID AND D.DEPTNAME = 'JAVA' )
AND D.DEPTID = E.DEPTID
-- 考虑 NULL
SELECT NVL(D.DEPTNAME,'NODEPT') ,E.EMPNAME ,E.SALARY FROM DEPARTMENT D , EMPLOYEE E
WHERE E.SALARY > ALL(
SELECT E.SALARY FROM EMPLOYEE E, DEPARTMENT D
WHERE D.DEPTID = E.DEPTID AND D.DEPTNAME = 'JAVA' )
AND D.DEPTID (+)= E.DEPTID
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -