/*
Navicat Oracle Data Transfer
Oracle Client Version : 10.2.0.5.0
Source Server : MyOracle
Source Server Version : 110200
Source Host : localhost:1521
Source Schema : KAIFAMIAO
Target Server Type : ORACLE
Target Server Version : 110200
File Encoding : 65001
Date: 2021-10-19 17:16:36
*/
-- ----------------------------
-- Table structure for DEPT
-- ----------------------------
DROP TABLE "KAIFAMIAO"."DEPT";
CREATE TABLE "KAIFAMIAO"."DEPT" (
"ID" NUMBER NOT NULL ,
"DNAME" VARCHAR2(20 BYTE) NULL ,
"LOC" VARCHAR2(100 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of DEPT
-- ----------------------------
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1001', '开发部', '西安');
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1002', '市场部', '兰州');
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1003', '销售部', '北京');
-- ----------------------------
-- Table structure for EMP
-- ----------------------------
DROP TABLE "KAIFAMIAO"."EMP";
CREATE TABLE "KAIFAMIAO"."EMP" (
"ID" NUMBER NOT NULL ,
"ENAME" VARCHAR2(50 BYTE) NULL ,
"JOB_ID" NUMBER NULL ,
"MGR" NUMBER NULL ,
"JOINDATE" DATE NULL ,
"SALARY" NUMBER(7,2) NULL ,
"BONUS" NUMBER(7,2) NULL ,
"DEPT_ID" NUMBER NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of EMP
-- ----------------------------
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10001', '张斌', '102', '0', TO_DATE('2019-08-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '9500', '8000', null);
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10002', '顾辞', '101', '1', TO_DATE('2019-08-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10003', '陈平安', '103', '2', TO_DATE('2019-10-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10004', '赔钱', '104', '2', TO_DATE('2019-10-10 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8300', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10005', '张三丰', '101', '1', TO_DATE('2019-09-19 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '4000', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10006', '曹慈', '101', '1', TO_DATE('2019-10-18 18:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '3900', '1003');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10007', '左右', '106', '2', TO_DATE('2019-10-13 15:30:10', 'YYYY-MM-DD HH24:MI:SS'), '4500', '2500', '1003');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10008', '白也', '105', '2', TO_DATE('2019-10-01 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4500', '2000', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10009', '助理1', '107', '3', TO_DATE('2020-04-29 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4000', '0', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100010', '助理2', '107', '3', TO_DATE('2021-05-10 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4000', '1000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100011', '助理3', '107', '3', TO_DATE('2019-04-20 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '3000', '0', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100012', '助理4', '107', '3', TO_DATE('2018-10-29 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '3000', '1000', '1003');
-- ----------------------------
-- Table structure for INFOS
-- ----------------------------
DROP TABLE "KAIFAMIAO"."INFOS";
CREATE TABLE "KAIFAMIAO"."INFOS" (
"STUID" VARCHAR2(7 BYTE) NOT NULL ,
"STUNAME" VARCHAR2(10 BYTE) NOT NULL ,
"GENDER" VARCHAR2(5 BYTE) NOT NULL ,
"AGE" NUMBER(2) NOT NULL ,
"SEAT" NUMBER(2) NOT NULL ,
"ENROLLDATE" DATE NULL ,
"STUADDRESS" VARCHAR2(50 CHAR) DEFAULT '地址不详' NOT NULL ,
"CLASSNO" VARCHAR2(4 BYTE) NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of INFOS
-- ----------------------------
INSERT INTO "KAIFAMIAO"."INFOS" VALUES ('s100102', '林冲', '男', '22', '2', TO_DATE('2021-10-19 14:45:14', 'YYYY-MM-DD HH24:MI:SS'), '西安', '1001');
INSERT INTO "KAIFAMIAO"."INFOS" VALUES ('s100104', '阮小二', '男', '26', '3', TO_DATE('2021-10-18 21:16:10', 'YYYY-MM-DD HH24:MI:SS'), '地址不详', '1001');
-- ----------------------------
-- Table structure for JOB
-- ----------------------------
DROP TABLE "KAIFAMIAO"."JOB";
CREATE TABLE "KAIFAMIAO"."JOB" (
"ID" NUMBER NOT NULL ,
"JNAME" VARCHAR2(30 BYTE) NULL ,
"DESCRIPTION" VARCHAR2(200 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of JOB
-- ----------------------------
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('101', '部门经理', '负责整个部门的具体工作,向总经理汇报');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('103', '开发工程师', '负责部门的具体开发工作,向部门经理汇报');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('102', '总经理', '负责公司整体的运行');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('104', '运维测试工程师', '负责开发部产品的运维测试工作');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('105', '市场开发人员', '负责公司市场的开发拓展');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('106', '销售员', '负责销售公司产品');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('107', '助理', '协助上一级员工完成工作');
-- ----------------------------
-- Table structure for SALARYGRADE
-- ----------------------------
DROP TABLE "KAIFAMIAO"."SALARYGRADE";
CREATE TABLE "KAIFAMIAO"."SALARYGRADE" (
"GRADE" NUMBER NOT NULL ,
"LOSALARY" NUMBER NULL ,
"HISALARY" NUMBER NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of SALARYGRADE
-- ----------------------------
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('1', '9000', '10000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('2', '8000', '9000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('3', '5500', '7000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('4', '3000', '5000');
-- ----------------------------
-- Table structure for SCORES
-- ----------------------------
DROP TABLE "KAIFAMIAO"."SCORES";
CREATE TABLE "KAIFAMIAO"."SCORES" (
"ID" NUMBER NULL ,
"TERM" VARCHAR2(2 BYTE) NULL ,
"STUID" VARCHAR2(7 BYTE) NOT NULL ,
"EXAMNO" VARCHAR2(7 BYTE) NOT NULL ,
"WRITTENSCORE" NUMBER(4,1) NOT NULL ,
"LABSCORE" NUMBER(4,1) NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of SCORES
-- ----------------------------
INSERT INTO "KAIFAMIAO"."SCORES" VALUES ('1001', 'S2', 's100104', '4', '98', '89');
-- ----------------------------
-- Sequence structure for MYSEQ
-- ----------------------------
DROP SEQUENCE "KAIFAMIAO"."MYSEQ";
CREATE SEQUENCE "KAIFAMIAO"."MYSEQ"
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999999999999999999999999
START WITH 21
CACHE 20;
-- ----------------------------
-- Indexes structure for table DEPT
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table DEPT
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."DEPT" ADD PRIMARY KEY ("ID");
-- ----------------------------
-- Indexes structure for table EMP
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table EMP
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."EMP" ADD PRIMARY KEY ("ID");
-- ----------------------------
-- Uniques structure for table INFOS
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."INFOS" ADD UNIQUE ("STUNAME");
ALTER TABLE "KAIFAMIAO"."INFOS" ADD UNIQUE ("STUID");
-- ----------------------------
-- Checks structure for table INFOS
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (AGE >=0 AND AGE<=100);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ((CLASSNO >='1001' AND CLASSNO<='1999') OR
(CLASSNO >='2001' AND CLASSNO<='2999'));
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (GENDER = '男' OR GENDER = '女');
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (SEAT >=0 AND SEAT <=50);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUID" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUNAME" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("GENDER" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("AGE" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("SEAT" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUADDRESS" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("CLASSNO" IS NOT NULL);
-- ----------------------------
-- Indexes structure for table JOB
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table JOB
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."JOB" ADD PRIMARY KEY ("ID");
-- ----------------------------
-- Indexes structure for table SALARYGRADE
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table SALARYGRADE
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SALARYGRADE" ADD PRIMARY KEY ("GRADE");
-- ----------------------------
-- Checks structure for table SCORES
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK (TERM = 'S1' OR TERM ='S2');
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("STUID" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("EXAMNO" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("WRITTENSCORE" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("LABSCORE" IS NOT NULL);
-- ----------------------------
-- Foreign Key structure for table "KAIFAMIAO"."EMP"
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."EMP" ADD FOREIGN KEY ("DEPT_ID") REFERENCES "KAIFAMIAO"."DEPT" ("ID");
ALTER TABLE "KAIFAMIAO"."EMP" ADD FOREIGN KEY ("JOB_ID") REFERENCES "KAIFAMIAO"."JOB" ("ID");
-- ----------------------------
-- Foreign Key structure for table "KAIFAMIAO"."SCORES"
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SCORES" ADD FOREIGN KEY ("STUID") REFERENCES "KAIFAMIAO"."INFOS" ("STUID");
使用上表完成数据查询
1.选择某一个部门的所有员工
select e.id, e.ename, j.jname, e.salary, e.bonus, d.dname
from job j join emp e on j.id = e.job_id
join dept d on d.id = e.dept_id
where e.dept_id =1003;
2.列出所有第二级员工的姓名,编号,部门编号
select e.ename, e.id, e.dept_id from emp e where mgr =2;
3.找出奖金高于薪水的员工
select*from emp where bonus > salary;
4.找出奖金高于薪水60%的员工
select*from emp where bonus > salary *0.6;
5.找出部门某个部门的经理和某个部门的所有第二级员工的详细资料【两个部门不相同】
select*from emp where dept_id =1001and mgr =1or mgr =(select mgr from emp where dept_id =1002and mgr =2);
-- select distinct j.jname from emp e join job j on e.job_id = j.id where e.bonus != 0;selectdistinct j.jname from emp e join job j on e.job_id = j.id where e.bonus isnotnull;
8.找出不收取奖金或者奖金低于5000 的员工
selectdistinct j.jname from emp e join job j on e.job_id = j.id where e.bonus =0or e.bonus <5000;
9.找出各月倒数第三天受雇的所有员工
SELECT*FROM EMP WHERE JOINDATE = LAST_DAY(JOINDATE)-2;
10.找出早于2年前受雇的员工
select*from emp where JOINDATE <= add_months(sysdate,-24);select*from emp where JOINDATE <= JOINDATE +INTERVAL'-2'YEAR;
基于SCOTT 示例数据库进行练习
1.列出至少一个员工的所有部门
-- 在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。SELECT E.DEPTNO,D.DNAME,COUNT(E.EMPNO)AS EMPNUM FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
GROUPBY E.DEPTNO,D.DNAME
HAVINGCOUNT(E.EMPNO)>1-- 这样写不太严谨 如果 编号和名字不对应就会有问题 所以上面可以不去查 名字 查编号用编号分组-- 想要全部列出 可以用分词查询结果作为表和dept表连接-- having 写 group by 之后之前都可以select d.deptno, d.dname, d1.cou from dept d,(select deptno,count(empno) cou havingcount(empno)>1groupby deptno)where d.deptno = d1.deptno
2.列出薪水比’SMITH‘多的所有员工
SELECT*FROM EMP
WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME ='SMITH')
3.列出所有员工的姓名及其直接上级的姓名
SELECT E.ENAME AS EMPNAME, E1.ENAME AS MGRNAME FROM EMP E
JOIN EMP E1 ON E.MGR = E1.EMPNO
4.列出受雇日期早于其直接上级的所有员工
SELECT E.*FROM EMP E
JOIN EMP E1 ON E.MGR = E1.EMPNO
WHERE E.HIREDATE > E1.HIREDATE
5.列出部门名称和这些部门的员工信息 同时列出那些没有员工的部门
SELECT D.DNAME,D.DEPTNO, E.*FROM EMP E
RIGHTJOIN DEPT D ON E.DEPTNO = D.DEPTNO
-- leftselect
d.deptno,d.dname,e.empno,e.ename
from
dept d leftjoin emp e
on
d.deptno=e.deptno
-- Oracle 特殊用法 +SELECT D.DNAME,D.DEPTNO, E.*FROM EMP E , DEPT D
WHERE E.DEPTNO (+)= ED.DEPTNO
select
d.deptno,d.dname,e.empno,e.ename
from
dept d ,emp e
where
d.deptno=e.deptno(+)
SELECT ENAME FROM EMP
WHERE DEPTNO =(SELECT DEPTNO FROM DEPT WHERE DNAME ='SALES')-- 2 select
e.ename,d.dname
from
emp e,dept d
where
d.dname='SALES'and
e.deptno=d.deptno
8.列出薪水高于公司平均薪水的所有员工
select ename,sal from emp where sal >(selectavg(sal)from emp)
9.列出薪水等于30部门中员工的薪水的所有员工的姓名和薪水
select ename,sal,deptno from emp where sal in(select sal from emp where deptno =30)
10.列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水
select ename,sal,deptno from emp where sal >ALL(select sal from emp where deptno =30)-- 2select
ename,sal
from
emp
where
sal>(selectmax(sal)from emp where deptno=30)
根据ER图创建表结构/*Navicat Oracle Data TransferOracle Client Version : 10.2.0.5.0Source Server : MyOracleSource Server Version : 110200Source Host : localhost:1521Source Schema : KAIFAMIAOTarget Server Type : ORACLETarget S