emp 员工表（empno 员工号/ename 员工姓名/job 工作/mgr 上级编号/hiredate 受雇日期/sal 薪金/comm 佣金/deptno 部门编号）
--------------------------------------------------------dept表-------------------------------------------------
--------------------------------------------salgrade----------------------------------------
dept 部门表（deptno 部门编号/dname 部门名称/loc 地点）
----------------------------------------------------------emp表-------------------------------------------------------------
-
DROP
TABLE
IF
EXISTS
`emp`;
-
CREATE
TABLE
`emp` (
-
`EMPNO`
int(
4)
NOT
NULL,
-
`ENAME`
varchar(
10)
DEFAULT
NULL,
-
`JOB`
varchar(
9)
DEFAULT
NULL,
-
`MGR`
int(
4)
DEFAULT
NULL,
-
`HIREDATE`
date
DEFAULT
NULL,
-
`SAL`
int(
7)
DEFAULT
NULL,
-
`COMM`
int(
7)
DEFAULT
NULL,
-
`DEPTNO`
int(
2)
DEFAULT
NULL,
-
PRIMARY
KEY (
`EMPNO`),
-
KEY
`FK_DEPTNO` (
`DEPTNO`),
-
CONSTRAINT
`FK_DEPTNO` FOREIGN
KEY (
`DEPTNO`)
REFERENCES
`dept` (
`DEPTNO`)
-
)
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8;
-
-
-- ----------------------------
-
-- Records of emp
-
-- ----------------------------
-
INSERT
INTO
`emp`
VALUES (
'7369',
'SMITH',
'CLERK',
'7902',
'1980-12-17',
'800',
null,
'20');
-
INSERT
INTO
`emp`
VALUES (
'7499',
'ALLEN',
'SALESMAN',
'7698',
'1981-02-20',
'1600',
'300',
'30');
-
INSERT
INTO
`emp`
VALUES (
'7521',
'WARD',
'SALESMAN',
'7698',
'1981-02-22',
'1250',
'500',
'30');
-
INSERT
INTO
`emp`
VALUES (
'7566',
'JONES',
'MANAGER',
'7839',
'1981-04-02',
'2975',
null,
'20');
-
INSERT
INTO
`emp`
VALUES (
'7654',
'MARTIN',
'SALESMAN',
'7698',
'1981-09-28',
'1250',
'1400',
'30');
-
INSERT
INTO
`emp`
VALUES (
'7698',
'BLAKE',
'MANAGER',
'7839',
'1981-05-01',
'2850',
null,
'30');
-
INSERT
INTO
`emp`
VALUES (
'7782',
'CLARK',
'MANAGER',
'7839',
'1981-06-09',
'2450',
null,
'10');
-
INSERT
INTO
`emp`
VALUES (
'7788',
'SCOTT',
'ANALYST',
'7566',
'1987-04-19',
'3000',
null,
'20');
-
INSERT
INTO
`emp`
VALUES (
'7839',
'KING',
'PRESIDENT',
null,
'1981-11-17',
'5000',
null,
'10');
-
INSERT
INTO
`emp`
VALUES (
'7844',
'TURNER',
'SALESMAN',
'7698',
'1981-09-08',
'1500',
'0',
'30');
-
INSERT
INTO
`emp`
VALUES (
'7876',
'ADAMS',
'CLERK',
'7788',
'1987-05-23',
'1100',
null,
'20');
-
INSERT
INTO
`emp`
VALUES (
'7900',
'JAMES',
'CLERK',
'7698',
'1981-12-03',
'950',
null,
'30');
-
INSERT
INTO
`emp`
VALUES (
'7902',
'FORD',
'ANALYST',
'7566',
'1981-12-03',
'3000',
null,
'20');
-
INSERT
INTO
`emp`
VALUES (
'7934',
'MILLER',
'CLERK',
'7782',
'1982-01-23',
'1300',
null,
'10');
--------------------------------------------------------dept表-------------------------------------------------
-
DROP
TABLE
IF
EXISTS
`dept`;
-
CREATE
TABLE
`dept` (
-
`DEPTNO`
int(
2)
NOT
NULL,
-
`DNAME`
varchar(
14)
DEFAULT
NULL,
-
`LOC`
varchar(
13)
DEFAULT
NULL,
-
PRIMARY
KEY (
`DEPTNO`)
-
)
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8;
-
-
-- ----------------------------
-
-- Records of dept
-
-- ----------------------------
-
INSERT
INTO
`dept`
VALUES (
'10',
'ACCOUNTING',
'NEW YORK');
-
INSERT
INTO
`dept`
VALUES (
'20',
'RESEARCH',
'DALLAS');
-
INSERT
INTO
`dept`
VALUES (
'30',
'SALES',
'CHICAGO');
-
INSERT
INTO
`dept`
VALUES (
'40',
'OPERATIONS',
'BOSTON');
--------------------------------------------salgrade----------------------------------------
-
DROP
TABLE
IF
EXISTS
`salgrade`;
-
CREATE
TABLE
`salgrade` (
-
`grade`
int(
11)
NOT
NULL
DEFAULT
'0',
-
`losal`
int(
11)
DEFAULT
NULL,
-
`hisal`
int(
11)
DEFAULT
NULL,
-
PRIMARY
KEY (
`grade`)
-
)
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8;
-
-
-- ----------------------------
-
-- Records of salgrade
-
-- ----------------------------
-
INSERT
INTO
`salgrade`
VALUES (
'1',
'700',
'1200');
-
INSERT
INTO
`salgrade`
VALUES (
'2',
'1201',
'1400');
-
INSERT
INTO
`salgrade`
VALUES (
'3',
'1401',
'2000');
-
INSERT
INTO
`salgrade`
VALUES (
'4',
'2001',
'3000');
-
INSERT
INTO
`salgrade`
VALUES (
'5',
'3001',
'9999');
---------------------------------------------------------------练习-----------------------------------------------------------
-
SELECT *
from emp
-
-
/*1-数据分组的重要函数: max、min、avg、sum、count*/
-
SELECT ename,sal
FROM emp
WHERE sal= (
SELECT
max(sal)
from emp )
-
-
/*2-查询出工资高于平均工资的员工信息*/
-
SELECT ename,job,sal
from emp
WHERE SAL>(
SELECT
avg(sal)
from emp)
-
/*查询出每个部门的平均工资和最高工资;*/
-
SELECT
avg(sal),
max(sal),deptno
from emp
group
by deptno
-
/*查询出每个部门的每种岗位的平均工资和最低工资;*/
-
SELECT
avg(sal),
min(sal),DEPTNO,job
from emp
group
by deptno,job
-
/*查询出平均工资低于2000的部门号和它的平均工资.*/
-
-
SELECT
avg(sal),deptno
FROM emp
GROUP
BY DEPTNO
having
avg(sal)<
2000
-
-
SELECT
avg(sal),deptno
FROM emp
GROUP
BY DEPTNO
having
avg(sal)>
2000
ORDER
BY
avg(sal)
-
/*查询出每个员工的姓名、工资及其工资的级别*/
-
SELECT e.ename,e.sal,s.grade
from emp e,salgrade s
WHERE e.sal
BETWEEN s.losal
AND s.hisal
-
-
/*查询出某个员工的上级领导的姓名*/
-
SELECT e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno
-
-
/*询出工资比部门编号30的所有员工的工资高的员工的姓名、工资和部门编号*/
-
-
SELECT ename,sal,deptno
from emp
where sal>all (
SELECT sal
from emp
WHERE deptno=
30)
-
-
/*查询出与SMITH的部门和岗位完全相同的所有雇员*/
-
SELECT *
FROM emp
WHERE (job,deptno)=(
SELECT job ,deptno
FROM emp
WHERE ename=
'SMITH')
-
/*查询出高于自己部门平均工资的员工的信息*/
-
SELECT e1.ename,e1.sal
FROM emp e1,(
SELECT deptno,
avg(sal) mysal
FROM emp
GROUP
BY deptno) e2
WHERE e1.deptno=e2.deptno
AND e1.sal>e2.mysal
-
-
/*谁挣的钱的最多*/
-
select ename, sal
from emp
where sal = (
select
max(sal)
from emp);
-
-
/*求部门人中那些人的薪水最高*/
-
/*SELECT ename,DEPTNO,max(sal) from emp GROUP BY deptno;*/
-
-
select ename, sal,emp.deptno
-
from emp
-
join (
select
max(sal) max_sal, deptno
from emp
group
by deptno) t
-
on (emp.sal =t.max_sal
and emp.deptno =t.deptno)
-
-
-
SELECT *
FROM salgrade
-
/*部门平均薪水的等级*/
-
SELECT t.deptno,s.grade
FROM salgrade s,
-
(
SELECT deptno,
AVG(sal)
avg
from emp
GROUP
BY deptno) t
-
WHERE t.avg
between s.losal
and s.hisal;
-
-
select deptno,avg_sal,grade
from
-
(
select deptno,
avg(sal) avg_sal
from emp
group
by deptno) t
-
join salgrade s
on (t.avg_sal
between s.losal
and s.hisal);
-
-
/*那些人是经理人*/
-
select ename
from emp
where empno
in (
select
distinct mgr
from emp);
-
或
-
select
distinct d.ename
from emp e ,emp d
where e.mgr=d.empno
-
/*不用组函数求最高薪水*/
-
select
distinct sal
-
from emp
-
where sal
not
in
-
(
select
distinct e1.sal
from emp e1
join emp e2
on (e1.sal < e2.sal))
-
/*平均薪水最高的部门编号*/
-
SELECT deptno ,
max(
avg)
FROM
-
(
SELECT
avg(sal)
avg,deptno
FROM emp
GROUP
BY deptno)t
-
-
/*平均薪水最高的部门名称*/
-
SELECT *
from dept
-
SELECT d.deptno,d.dname,
max(
avg)
FROM
-
(
SELECT
avg(sal)
avg,deptno
FROM emp
GROUP
BY deptno) t,dept d
-
WHERE t.deptno=d.deptno
-
/*平均薪水的等级最低的部门名称*/
-
-
SELECT t.dname,t.deptno,
min(s.grade) grade,t.avg_sal
FROM
-
(
SELECT
avg(sal) avg_sal,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno= d.deptno
GROUP
BY e.deptno)t
-
join salgrade s
on(t.avg_sal
between s.losal
and s.hisal);
-
-
或
-
-
select dname, t1.deptno,grade,avg_sal
from
-
(
-
select deptno, grade, avg_sal
from
-
(
select deptno,
avg(sal) avg_sal
from emp
group
by deptno)t
-
join salgrade s
on(t.avg_sal
between s.losal
and s.hisal)
-
-
)t1
-
join dept
on (t1.deptno=dept.deptno)
-
where t1.grade=
-
(
select
min(grade)
from(
-
select deptno, grade, avg_sal
from
-
(
select deptno,
avg(sal) avg_sal
from emp
group
by deptno)t
-
join salgrade s
on(t.avg_sal
between s.losal
and s.hisal)
-
)m
-
);
-
-
/*比普通员工的最高薪水还要高的经理人*/
-
select ename
-
from emp
-
where empno
in (
select
distinct mgr
from emp
where mgr
is
not
null)
and sal >
-
(
select
max(sal)
from emp
where empno
not
in (
select
distinct mgr
from emp
where mgr
is
not
null))
