网站建设如何投放广告,wordpress 多个子站点,如何美化wordpress页面,wordpress拖拽上传目录
1.准备数据表
2.表之间的关系
3.题目
3.1 取得每个部门最高薪水的人员名称
3.2 哪些人的薪水在部门的平均薪水之上
3.3 取得部门中#xff08;所有人的#xff09;平均的薪水等级
3.4 不准用组函数#xff08;Max #xff09;#xff0c;取得最高薪水
3.5 取…目录
1.准备数据表
2.表之间的关系
3.题目
3.1 取得每个部门最高薪水的人员名称
3.2 哪些人的薪水在部门的平均薪水之上
3.3 取得部门中所有人的平均的薪水等级
3.4 不准用组函数Max 取得最高薪水
3.5 取得平均薪水最高的部门的部门编号
3.5 取得平均薪水最高的部门的部门名称
3.6 取得薪水最高的第六至第十名员工
3.7 取得每个薪水等级有多少员工
3.8 列出所有员工及领导的名字
3.9 列出至少有5个员工的所有部门
3.10 列出薪金比SMITH 多的所有员工信息
3.11 列出所有CLERK( 办事员) 的姓名及其部门名称, 部门的人数
3.12 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数按照工作岗位分组求最小值。 对之前学习的mysql基础和进阶进行一些练习巩固前面学完的知识。 1.准备数据表 CREATE TABLE DEPT(DEPTNO int(2) not null ,DNAME VARCHAR(14) ,LOC VARCHAR(13),primary key (DEPTNO));
CREATE TABLE EMP(EMPNO int(4) not null ,ENAME VARCHAR(10),JOB VARCHAR(9),MGR INT(4),HIREDATE DATE DEFAULT NULL,SAL DOUBLE(7,2),COMM DOUBLE(7,2),primary key (EMPNO),DEPTNO INT(2));CREATE TABLE SALGRADE( GRADE INT,LOSAL INT,HISAL INT );INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, ACCOUNTING, NEW YORK);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, RESEARCH, DALLAS);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, SALES, CHICAGO);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
40, OPERATIONS, BOSTON);
commit;INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, SMITH, CLERK, 7902, 1980-12-17
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, ALLEN, SALESMAN, 7698, 1981-02-20
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, WARD, SALESMAN, 7698, 1981-02-22
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, JONES, MANAGER, 7839, 1981-04-02
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, MARTIN, SALESMAN, 7698, 1981-09-28
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, BLAKE, MANAGER, 7839, 1981-05-01
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, CLARK, MANAGER, 7839, 1981-06-09
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, SCOTT, ANALYST, 7566, 1987-04-19
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, KING, PRESIDENT, NULL, 1981-11-17
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, TURNER, SALESMAN, 7698, 1981-09-08
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, ADAMS, CLERK, 7788, 1987-05-23
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, JAMES, CLERK, 7698, 1981-12-03
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, FORD, ANALYST, 7566, 1981-12-03
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, MILLER, CLERK, 7782, 1982-01-23
, 1300, NULL, 10);
commit;INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
commit;
之间运行上面的sql需要用到的数据表就建立好了。
2.表之间的关系 DEPT部门表 DEPTNO部门编号整数类型主键。DNAME部门名称。LOC部门所在地。 EMP员工表 EMPNO员工编号整数类型主键。ENAME员工姓名。JOB员工职位。MGR上级经理的员工编号整数类型。HIREDATE入职日期日期类型默认为NULL。SAL薪资。COMM奖金。DEPTNO所属部门编号整数类型。 SALGRADE薪资等级表 GRADE薪资等级整数类型。LOSAL最低薪资整数类型。HISAL最高薪资整数类型。
3.题目
3.1 取得每个部门最高薪水的人员名称
思路 使用INNER JOIN将EMP表和DEPT表连接以获取员工和其所在部门的相关信息。 使用子查询计算每个部门的最高薪水。子查询中使用GROUP BY和MAX函数来找到每个部门的最高薪水。 在主查询中通过比较员工的薪水和其所在部门的最高薪水筛选出薪水等于最高薪水的员工。 结果集中包括了每个部门中薪水最高的员工的姓名、部门名称和薪水。
select d.dname as 部门名称, e.ename as 员工姓名, e.sal as 薪水
from emp e
inner join dept d on e.deptno d.deptno
where (e.deptno, e.sal) in (select deptno, max(sal)from empgroup by deptno
);
结果 3.2 哪些人的薪水在部门的平均薪水之上
思路 主查询中使用INNER JOIN连接EMP表e和DEPT表d以获取员工和其所在部门的相关信息。 使用INNER JOIN连接一个子查询该子查询计算每个部门的平均薪水。子查询中使用GROUP BY和AVG函数来计算每个部门的平均薪水。 在主查询中通过比较员工的薪水e.sal和其所在部门的平均薪水dept_avg.avg_dept_salary筛选出薪水高于所在部门平均薪水的员工。 结果集包括了员工姓名、员工薪水、部门名称和部门平均薪水以便比较员工薪水与部门平均薪水。
SELECT e.ename AS 员工姓名, e.sal AS 员工薪水, d.dname AS 部门名称, avg_dept_salary AS 部门平均薪水
FROM emp e
INNER JOIN dept d ON e.deptno d.deptno
INNER JOIN (SELECT deptno, AVG(sal) AS avg_dept_salaryFROM empGROUP BY deptno
) dept_avg ON e.deptno dept_avg.deptno
WHERE e.sal dept_avg.avg_dept_salary;结果 3.3 取得部门中所有人的平均的薪水等级
1.首先要取得所有人的薪水等级再按部门编号分组
2.按照部门编号分组后再取平均值
select e.DEPTNO as 部门编号,avg(s.GRADE) as 部门平均薪资等级from emp einner join salgrade son e.SAL between s.LOSAL and s.HISAL group by e.DEPTNO; 3.4 不准用组函数Max 取得最高薪水
第一种方法使用降序取第一个数据
select ENAME,SAL from emp order by SAL desc limit 1;
第二种方法使用自连接再进行not in 去查找
select SAL from emp where sal
not in (select distinct a.SAL from emp a join emp b on a.SAL b.SAL)
3.5 取得平均薪水最高的部门的部门编号
用avg取平均值按照deptno分组最后降序排序取第一个值
select DEPTNO as 部门编号,avg(SAL) as 平均薪水 from emp
group by DEPTNO order by avg(SAL) desc limit 1; 3.5 取得平均薪水最高的部门的部门名称
按照dname来分组最后采用降序排序
select d.DNAME,avg(e.SAL) as avgsal from emp e
join dept d
on e.DEPTNO d.DEPTNO
group by d.DNAME
order by avgsal desc limit 1; 3.6 取得薪水最高的第六至第十名员工
降序排序取6-10
select ENAME,SAL from emp order by sal desc limit 5,5;
3.7 取得每个薪水等级有多少员工
按照grand进行分组再求和
select s.GRADE,COUNT(*) from emp e
inner join salgrade s
on e.SAL between s.LOSAL and s.HISAL
group by s.GRADE;
3.8 列出所有员工及领导的名字
select a.ENAME 员工 ,b.ENAME 领导 from emp a
left join emp b on a.mgr b.EMPNO;
3.9 列出至少有5个员工的所有部门 使用INNER JOIN将EMP表e和DEPT表d连接以获取员工和其所在部门的相关信息。 使用GROUP BY子句按部门名称d.dname进行分组以计算每个部门中的员工数量。 使用HAVING子句筛选出员工数量至少为5的部门。
select d.DNAME as 部门名称,count(*) as 员工数量
from emp e inner join dept d on e.DEPTNO d.DEPTNO
group by d.DNAME having count(*) 5;
3.10 列出薪金比SMITH 多的所有员工信息
直接筛选出来然后比较就行
select ENAME,SAL from emp
where SAL (select SAL from emp where ename SMITH) order by SAL desc ;
3.11 列出所有CLERK( 办事员) 的姓名及其部门名称, 部门的人数
1.从EMP表中选择职位为CLERK的员工的姓名ename、所在部门编号deptno。
2.使用INNER JOIN将上述查询结果与DEPT表连接以获取员工所在部门的名称dname。
3.使用子查询计算每个部门的员工人数并使用GROUP BY子句按部门编号进行分组。
4.最后将上述查询的结果用作子查询并再次使用INNER JOIN将部门人数deptcount与部门名称dname连接以获取最终结果。
SELECT t1.ename AS 员工姓名, t1.dname AS 部门名称, t2.deptcount AS 部门人数
FROM (SELECT e.ename, d.dname, e.deptnoFROM emp eJOIN dept d ON e.deptno d.deptnoWHERE e.job CLERK
) t1
JOIN (SELECT deptno, COUNT(*) AS deptcountFROM empGROUP BY deptno
) t2 ON t1.deptno t2.deptno; 3.12 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数按照工作岗位分组求最小值。
select job,sal, count(*) as 从事该工作工资低于1500的人数 from emp
group by job having min(SAL) 1500; 后续会继续增加些题目。。。。。。
