【JAVADAY20】-MYSQL数据库34个案例

65 阅读 0 评论 43 点赞

我是靠谱客的博主唠叨歌曲，最近开发中收集的这篇文章主要介绍【JAVADAY20】-MYSQL数据库34个案例，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

数据库:

-- MySQL dump 10.13  Distrib 5.7.37, for Win64 (x86_64)
--
-- Host: localhost    Database: bjpowernode
-- ------------------------------------------------------
-- Server version	5.7.37

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `bjpowernode`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bjpowernode` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `bjpowernode`;

--
-- Table structure for table `dept`
--

DROP TABLE IF EXISTS `dept`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dept` (
  `DEPTNO` int(11) NOT NULL AUTO_INCREMENT,
  `DNAME` varchar(14) NOT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COMMENT='閮ㄩ棬琛?;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dept`
--

LOCK TABLES `dept` WRITE;
/*!40000 ALTER TABLE `dept` DISABLE KEYS */;
INSERT INTO `dept` VALUES (10,' ACCOUNTING ','NEW YORK'),(20,' RESEARCH ','DALLAS '),(30,' SALES ','CHICAGO '),(40,' OPERATIONS ','BOSTON ');
/*!40000 ALTER TABLE `dept` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `emp`
--

DROP TABLE IF EXISTS `emp`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL AUTO_INCREMENT,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(11) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  UNIQUE KEY `emp_EMPNO_uindex` (`EMPNO`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `emp`
--

LOCK TABLES `emp` WRITE;
/*!40000 ALTER TABLE `emp` DISABLE KEYS */;
INSERT INTO `emp` VALUES (7369,' SMITH ','CLERK',7902,'1980-12-17',800.00,NULL,20),(7499,' ALLEN ','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),(7521,' WARD ','SALESMAN',7698,'1981-02-20',1250.00,500.00,30),(7566,' JONES ','MANAGER ',7698,'1981-04-02',2975.00,NULL,20),(7654,' MARTIN ','SALESMAN',7839,'1981-09-28',1250.00,1400.00,30),(7698,' BLAKE ','MANAGER ',7698,'1981-05-01',2850.00,NULL,30),(7782,' CLARK ','MANAGER ',7839,'1981-06-09',2450.00,NULL,10),(7788,' SCOTT ','ANALYST ',7566,'1987-04-19',3000.00,NULL,20),(7839,' KING ','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,' TURNER ','SALESMAN ',7698,'1981-09-08',1500.00,0.00,30),(7876,' ADAMS ','CLERK',7788,'1987-05-23',1100.00,NULL,20),(7900,' JAMES ','CLERK',7698,'1981-12-03',950.00,NULL,30),(7902,' FORD ','ANALYST ',7566,'1981-12-03',3000.00,NULL,20),(7934,' MILLER ','CLERK',7782,'1982-01-23',1300.00,NULL,10);
/*!40000 ALTER TABLE `emp` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `salgrade`
--

DROP TABLE IF EXISTS `salgrade`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `salgrade` (
  `GRADE` int(11) DEFAULT NULL,
  `LOSAL` int(11) DEFAULT NULL,
  `HISAL` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `salgrade`
--

LOCK TABLES `salgrade` WRITE;
/*!40000 ALTER TABLE `salgrade` DISABLE KEYS */;
INSERT INTO `salgrade` VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
/*!40000 ALTER TABLE `salgrade` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-03-13 16:58:29

				console.sql
select *
from emp;
select *
from dept;
select *
from salgrade;
select DEPTNO '部门号', max(SAL) '最大薪资'
from emp
where SAL > 3000
group by DEPTNO;

#找出每个部门的平均薪资，要求显示平均薪资高于2500的
select DEPTNO, avg(SAL)
from emp
group by DEPTNO
having avg(SAL) > 2500;
select *
from dept;
select d.DNAME '部门名', e.ENAME '员工名'
from emp e
         join dept d on e.DEPTNO = d.DEPTNO;
select *
from salgrade;
select e.ENAME, e.SAL, s.GRADE
from emp e
         join salgrade s on SAL between LOSAL and HISAL;
select e1.ENAME '员工', e2.ENAME
from emp e1
         join emp e2 on e1.MGR = e2.EMPNO;

#自连接 一张表作2张表用
select e1.ENAME '员工', e2.ENAME
from emp e1
         join emp e2 on e1.MGR = e2.EMPNO;
select e.ENAME, d.DNAME
from emp e
         right outer join dept d on e.DEPTNO = d.DEPTNO;


select e.ENAME '员工名', d.DNAME '所在部门名', e.SAL '薪资', s.GRADE '工资等级'
from emp e
         join dept d on e.DEPTNO = d.DEPTNO
         join salgrade s on e.SAL between s.LOSAL and s.HISAL;
select e1.ENAME '员工', e2.ENAME '老板'
from emp e1
         left join emp e2 on e1.MGR = e2.EMPNO;
select e.ENAME '员工名', d.DNAME '所在部门名', e.SAL '薪资', s.GRADE '工资等级', l.ENAME '老板'
from emp e
         left join emp l on e.MGR = l.EMPNO
         join dept d on e.DEPTNO = d.DEPTNO
         join salgrade s on e.SAL between
    s.LOSAL and s.HISAL
order by s.GRADE desc;


select SAL
from emp
where SAL > (select min(SAL)
             from emp);
select *
from emp;

select t.*, s.GRADE
from salgrade s
         join
     (select e.JOB, avg(e.SAL) as avg
      from emp e
      group by e.JOB) t on t.avg between s.LOSAL and s.HISAL;
select *
from salgrade;

#每个部门最高薪水的人员名称   xxxxx
select e.ENAME '姓名', e.DEPTNO '部门', t.sal '最大工资'
from emp e
         join (select DEPTNO, max(SAL) as 'sal' from emp group by DEPTNO) t on e.DEPTNO = t.DEPTNO
where e.SAL = t.sal;

#那些人的薪水在部门的平均薪水之上
#1、部门的平均薪水
#2、找每一个部门的人的薪水 XXX
select e2.ENAME '姓名', e2.SAL
from emp e2
         join (select e.DEPTNO '部门', avg(SAL) '平均薪水'
               from emp e
                        join dept d on e.DEPTNO = d.DEPTNO
               group by e.DEPTNO) t on e2.DEPTNO = t.部门
where e2.SAL > t.平均薪水;

#取得部门中所有人的平均的薪水等级
#1、先获取每个人的平均薪水等级 X
select e.DEPTNO, avg(GRADE)
from emp e
         join salgrade s on e.SAL between s.LOSAL and s.HISAL
group by e.DEPTNO;

#不用max 取得最高薪水 X
select SAL '最高薪水'
from emp e
order by e.SAL desc
limit 0,1;


#取得平均薪水最高的部门的部门编号 X
select e.DEPTNO, avg(e.SAL)
from emp e
group by e.DEPTNO
order by avg(e.SAL) desc
limit 0,1;

#取得平均薪水最高的部门的部门名称
#order by是在select之后排序 最后是limit
select d.DNAME, avg(e.SAL)
from emp e
         join dept d on e.DEPTNO = d.DEPTNO
group by e.DEPTNO
order by avg(e.SAL) desc
limit 0,1;

#求平均薪水等级最低的部门的部门名称 X
select d.DNAME, avg(s.GRADE)
from emp e
         join salgrade s on e.SAL between s.LOSAL and s.HISAL
         join dept d on e.DEPTNO = d.DEPTNO
group by d.DNAME desc
limit 0,1;

#取得比普通员工的最高薪水还要高的领导人姓名
select e1.ENAME, e1.SAL, e2.ENAME, e2.SAL
from emp e1
         left join emp e2
                   on e1.MGR = e2.EMPNO
where e1.SAL > e2.SAL;

#取得薪水最高的前5名员工
select ENAME, SAL
from emp
order by SAL desc
limit 0,5;

#取得薪水最高的6-10名员工
select ENAME, SAL
from emp
order by SAL desc
limit 5,5;

#取得最后入职的5名员工
select ENAME, HIREDATE
from emp
order by HIREDATE desc
limit 0,5;

#取得每个薪水等级有多少员工
select s.GRADE, count(s.GRADE) as '员工数'
from emp e
         join salgrade s on e.SAL between s.LOSAL and s.HISAL
group by s.GRADE;

#
select S.SNAME
from s
         join SC ON S.SNO = SC.SNO
         JOIN C ON SC.CNO = C.CNO
WHERE C.CNAME != 'liming';


#列出所有员工及领导的姓名
select e1.ENAME '员工s', e2.ENAME '领导'
from emp e1
         left join emp e2
                   on e1.MGR = e2.EMPNO;

#列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select e1.ENAME '员工', e1.EMPNO '工号', d.DNAME
from emp e1
         left join emp e2
                   on e1.MGR = e2.EMPNO
         join dept d on e1.DEPTNO = d.DEPTNO
where e1.HIREDATE < e2.HIREDATE
;

#列出部门名称和这些部门的员工信息
select d.DNAME, e.EMPNO, e.ENAME
from emp e
         right join dept d on e.DEPTNO = d.DEPTNO;

#列出至少有5个员工的所有部门
select DEPTNO, count(EMPNO)
from emp
group by DEPTNO
having count(EMPNO) > 5;

#列出薪资比 SMITH多的所有员工 信息
select e.*
from emp e
where SAL > (select SAL from emp where ENAME = ' SMITH ');

#列出所有CLERK的姓名及其部门名称，部门的人数
select em.ENAME, d.DNAME, t.count
from emp em
         join dept d on em.DEPTNO = d.DEPTNO
         join
     (select e.DEPTNO as 'deptno', count(e.DEPTNO) as 'count'
      from emp e
      group by e.DEPTNO) t on t.deptno = em.DEPTNO
where em.JOB = 'CLERK';

#列出最低薪金大于1500的各种工作以及从事此工作的全部雇佣人数
select s.JOB, s.count, t.min
from (select JOB, count(*) as 'count' from emp group by JOB) s
         join (select JOB, min(SAL) as min
               from emp
               group by JOB) as t on t.JOB = s.JOB
where t.min > 1500;

#列出薪资高于公司平均薪资的所有员工，所在部门，上级领导，雇员的工资等级
select avg(SAL) as 'avgsal'
from emp;
select emp.ENAME, d.DEPTNO, e.ENAME, s.GRADE
from emp
         left join emp e on emp.MGR = e.EMPNO
         join dept d on emp.DEPTNO = d.DEPTNO
         join salgrade s on emp.SAL between s.LOSAL and s.HISAL
where emp.SAL > (select avg(SAL) as 'avgsal'
                 from emp);


#列出与scott从事相同工作的所有员工及部门名称
select e.ENAME, d.DNAME
from emp e
         join dept d on e.DEPTNO = d.DEPTNO
where e.JOB = (select e1.JOB from emp e1 where e1.ENAME = ' SCOTT');

#列出薪资高于在部门30工作的所有员工的新进的员工姓名
select e.ENAME, d.DNAME
from emp e
         join dept d on e.DEPTNO = d.DEPTNO
where e.SAL > (select max(e2.SAL) from emp e2 where e2.DEPTNO = 30);

#列出在每个部门工作的员工数量
select DEPTNO, count(DEPTNO), avg(SAL), avg(timestampdiff(year, HIREDATE, now()))
from emp
group by DEPTNO;

#列出所有员工的姓名，部门名称和工资
select ENAME, d.DNAME, SAL
from emp
         join dept d on emp.DEPTNO = d.DEPTNO;

#列出所有部门的详细信息和人数
select d.*, count(e.DEPTNO) as 'count'
from dept d
         left join emp e on d.DEPTNO = e.DEPTNO
group by d.DEPTNO;

#列出各种工作的最低工资及从事此工作且工资最低的雇员姓名
select ENAME,t.*
from emp
         join (select e1.JOB, min(e1.SAL) as 'minsal'
               from emp e1
               group by e1.JOB) as t on emp.JOB=t.JOB and emp.SAL=t.minsal;

#列出各个部门的MANAGER的最低薪资
select DEPTNO,min(SAL)
from emp
where JOB='MANAGER'
group by DEPTNO;

#列出所有员工的年工资，按年薪从低到高排序
select ENAME '姓名',SAL*12 as '年工资'
from emp
order by SAL;

#求出员工领导的薪水超过3000的员工姓名和领导名称
select e.ENAME,e2.ENAME
from emp e left join emp e2 on e.MGR=e2.EMPNO
where e2.SAL>3000;

#求出部门名称种，带有'S'字符的部门员工的工资合计，部门人数
select d.DNAME '部门名称',count(e.DEPTNO) as '该部门人数',sum(e.SAL) '工资和'
from emp e right join dept d on e.DEPTNO = d.DEPTNO
where d.DNAME like '%s%'
group by d.DEPTNO;

#给认知日期超过40年的员工加薪30% 仅查询结果加30无修改
select ENAME as '员工姓名',SAL*1.3 as '总薪水'
from emp
where timestampdiff(year,HIREDATE,now())>40
order by SAL desc ;