vue循环指令el-table-column展示图片src路径拼接 在Vue中，使用el-table-column展示图片时，可以通过拼接图片路径来设置src属性。这里提供一个简单的例子： <template> <el-table :data="tableData" style="width: 100%"> <el-table-column prop="date" label="日期" width="180"></el-table-column> <el-table-column label="图标" width="180"> <template slot-scope="scope"> <img :src="getImageUrl(scope.row.icon)" alt="图标" /> </template> </el-table-column> <!-- 其他列 --> </el-table> </template> <script> export default { data() { return { tableData: [ { date: '2016-05-02', icon: 'image1' }, { date: '2016-05-04', icon: 'image2' }, // 其他数据... ], baseImagePath: 'path/to/your/images/' // 图片的基础路径 }; }, methods: { getImageUrl(icon) { return `${this.baseImagePath}${icon}.png`; // 拼接图片路径 } } }; </script>

正则表达式，将字符串分割两部分 @Test public void split() { String file_id = "group1/M01/00/00/wKjljWXLZxWAeK7JAAmOXAH6cKY805.png"; String[] splitStr = file_id.split("/"); String group_name = splitStr[0]; String remoteFileName = ""; for (int i = 1; i < splitStr.length; i++) { remoteFileName += splitStr[i]; if (i != splitStr.length - 1) { remoteFileName += "/"; } } System.out.println("group_name = " + group_name); System.out.println("remoteFileName = " + remoteFileName); }group_name = group1 remoteFileName = M01/00/00/wKjljWXLZxWAeK7JAAmOXAH6cKY805.png

秘钥认证与分发脚本（免密码登录） @TOC1.集群批量管理--秘钥认证1.1.概述管理更加轻松:两个节点，通过密钥形式进行访问,不需要输入密码,单向服务要求(应用场景)::star::star::star::star::star:==一些服务在使用前要求我们做秘钥认证==手动写批量管理脚本名字:密钥认证,免密码登录,双机互信1.2.原理1.3.极速上手指南角色主机名ip管理机m0110.0.0.0.7被管理节点nfs0110.0.0.0.17被管理节点web0110.0.0.0.27被管理节点backup10.0.0.0.37hostnamectl set-hostname backup1.3.0.基本检查#ping ping 172.16.1.xxx #22端口 sshd服务开启或可以访问 nmap -p22 172.16.1.31 172.16.1.71.3.1.创建密钥对ssh-keygen -t rsa #注意创建的啥时候也可以不加-t 通过rsa方法对数据进行加密.1.3.2.分发公钥ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.0.0.171.3.3.连接测试ssh 10.0.0.17 w ssh 10.0.0.17 hostname -I温馨提示: ssh-copy-id后公钥被存放在对方服务器的用户家目录下面的.ssh下面. 名字叫:authorized_keys1.4.自动化创建与分发秘钥阻碍::one:创建秘钥对:two:分发公钥的时候:yes/no (yes后会把信息保存到.ssh/known_hosts):three:分发公钥的时候:输入密码1.4.1.自动化创建秘钥ssh-keygen -f ~/.ssh/id_rsa -P '' ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''-f用于指定私钥的位置 -P 密码短语 设置为空1.4.2.自动化分发公钥阻碍 : 密码#安装密码提供密码 yum install -y sshpass #sshpass命令基本使用 # -p指定密码 # 先: ssh 10.0.0.7 hostname -I # 检查是否需要输入yes/no # 然后使用: sshpass -p123456 ssh 10.0.0.17 hostname -I 10.0.0.7 172.16.1.7 #使用sshpass 与ssh-copy-id分发公钥 # 先: ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.17 # 检查是否需要输入yes/no # 然后使用: sshpass -p123456 ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.17温馨提示: sshpass与ssh-copy-id的时候如果第1次连接,提示yes/no,sshpass失效了.补充说明: sshpass适用于给ssh相关的命令提供密码:ssh,scp,ssh-copy-id.阻碍: 第1次连接的时候提示yes/no温馨提示: 第1次远程的提示 yes/no,主机密钥信息检查,输入yes后存放到~/.ssh/known_hosts 解决思路: 临时取消即可,连接的时候不检查主机信息. -o StrictHostKeyChecking=no 临时不检查主机信息.sshpass -p123456 ssh-copy-id -i ~/.ssh/id_rsa.pub -o StrictHostKeyChecking=no 10.0.0.171.4.3.自动化创建与分发脚本ssh-keygen -t rsa -f ~/.ssh/id_rsa -P '' sshpass -p123456 ssh-copy-id -i ~/.ssh/id_rsa.pub -o StrictHostKeyChecking=no 10.0.0.17vim fenfamiyao.sh #!/bin/bash export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin color_function(){ color () { RES_COL=60 MOVE_TO_COLOR="echo -en \\033[${RES_COL}G" SETCOLOR_SUCCESS="echo -en \\033[1;32m" SETCOLOR_FAILURE="echo -en \\033[1;31m" SETCOLOR_WARNING="echo -en \\033[1;33m" SETCOLOR_NORMAL="echo -en \E[0m" echo -en "\E[1;35m$1\E[0m" && $MOVE_TO_COLOR echo -n "[" if [ $2 = "success" -o $2 = "0" ] ;then ${SETCOLOR_SUCCESS} echo -n $" OK " elif [ $2 = "failure" -o $2 = "1" ] ;then ${SETCOLOR_FAILURE} echo -n $"FAILED" else ${SETCOLOR_WARNING} echo -n $"WARNING" fi ${SETCOLOR_NORMAL} echo -n "]" echo } [ $# -eq 0 ] && echo "Usage: `basename $0` {success|failure|warning}" color $1 $2 } . /etc/os-release # 一键创建秘钥对 分发秘钥对 #1.vars pass=123456 ips="10.0.0.17 10.0.0.27 10.0.0.37" # 加入判断sshpass命令是否存在,如果不存在则安装 if [[ ${ID} =~ ^(rocky|rhel|centos) ]];then rpm -q sshpass &>/dev/null || yum -y install sshpass &>/dev/null elif [[ ${ID} =~ ^(ubuntu) ]];then dpkg -V sshpass &>/dev/null || apt -y install sshpass &>/dev/null else color_function "比支持此系统:${ID}" 2 exit fi #2.创建密钥对 if [ -f ~/.ssh/id_rsa ];then echo "已经创建过密钥对" else echo "正在创建密钥对" ssh-keygen -t rsa -f ~/.ssh/id_rsa -P '' &>/dev/null if [ $? -eq 0 ];then color_function "创建密钥对成功" 0 else color_function "创建密钥对失败" 1 exit fi fi #3.通过循环发送公钥 for ip in $ips do sshpass -p${pass} ssh-copy-id -i ~/.ssh/id_rsa.pub -o StrictHostKeyChecking=no ${ip} &>/dev/null if [ $? -eq 0 ];then color_function "${ip}:秘钥已发送..." 0 else color_function "${ip}:秘钥发送失败..." 1 fi done #4.批量在所有机器上执行命令 检查 for ip in $ips do hostname=`ssh ${ip} hostname` if [ $? -eq 0 ];then color_function "${ip}:${hostname}检测成功..." 0 else color_function "${ip}:${hostname}检测失败..." 1 fi doneendl

文件上传下载-io-流的理解-笔记 流就是中间桥梁// 第一步 把文件转成输入流 InputStream is = file.getInputStream(); // 第二步 把 从输入流 读到 代码 byte[] buffer = new byte[1024]; int len = is.read(buffer); // 上面的信息量很大，首先buffer的长度是1024 这是为了方便读 // 其次， read这个方法的返回值是 实际独到的字节数，也就是说是 <= 1024的数字 // 所以， 想一次性把 输入流中的数据 全都读入到缓冲区buffer， 那buffer就要足够大， 占用内存也会很大 // 如果要把代码中的数据，再存入本地，那就需要 输出流 // 第一步： 创建一个输出流，传入的参数是目的地 OutputStream os = new FileOutputStream(new File(realPath, uploadFileName)); // 第二步： 写 os.write(buffer, 0, len); os.flush(); // 关闭资源 os.close(); is.close();

MySQL数据库练习【三】 @TOC一、建库建表-数据准备create database company; use company; -- 创建部门表 DROP TABLE IF EXISTS dept; CREATE TABLE `dept` ( `deptno` int(11) PRIMARY KEY NOT NULL COMMENT '部门编号', `dname` varchar(14) NOT NULL COMMENT '部门名称', `loc` varchar(13) NOT NULL COMMENT '部门所在地' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'; -- 向部门表插入数据 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DALLAS'), (30,'SALES','CHICAGO'), (40,'OPERATIONS','BOSTON'); -- 创建员工表 DROP TABLE IF EXISTS emp; CREATE TABLE `emp` ( `empno` int(11) PRIMARY KEY NOT NULL COMMENT '员工编号', `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名', `job` varchar(9) DEFAULT NULL COMMENT '工作岗位', `mgr` int(11) DEFAULT NULL COMMENT '直属领导', `hiredate` date DEFAULT NULL COMMENT '入职时间', `sal` double DEFAULT NULL COMMENT '工资', `comm` double DEFAULT NULL COMMENT '奖金', `deptno` int(11) DEFAULT NULL COMMENT '所属部门' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'; -- 向员工表插入数据 INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20), (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30), (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30), (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20), (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30), (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30), (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10), (7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20), (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10), (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30), (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20), (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30), (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20), (7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10); -- 创建工资等级表 DROP TABLE IF EXISTS salgrade; CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT '等级', `losal` double DEFAULT NULL COMMENT '最低工资', `hisal` double DEFAULT NULL COMMENT '最高工资' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表'; -- 向工资等级表插入数据 INSERT INTO salgrade VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);员工表emp部门表dept工资等级表salgradeempno：员工编号deptno：部门编号grade：等级ename：员工姓名dname：部门名称losal：最低工资job：工作岗位loc：部门所在地hisal：最高工资mgr：直属领导 hiredate：入职时间 sal：工资 comm：奖金 deptno：所属部门 empnoenamejobmgrhiredatesalcommdeptno7369SMITHCLERK79021980-12-17800NULL207499ALLENSALESMAN76981981-02-201600300307521WARDSALESMAN76981981-02-221250500307566JONESMANAGER78391981-04-022975NULL207654MARTINSALESMAN76981981-09-2812501400307698BLAKEMANAGER78391981-05-012850NULL307782CLARKMANAGER78391981-06-092450NULL107788SCOTTANALYST75661987-07-033000NULL207839KINGPRESIDENTNULL1981-11-175000NULL107844TURNERSALESMAN76981981-09-0815000307876ADAMSCLERK77881987-07-131100NULL207900JAMESCLERK76981981-12-03950NULL307902FORDANALYST75661981-12-033000NULL207934MILLERCLERK77821981-01-231300NULL10deptnodnameloc10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONgradelosalhisal17001200212011400314012000420013000530019999二、动力节点—Mysql—经典34道习题1、查询每个部门最高薪水的人员名称 2、哪些人的薪水在部门的平均薪水之上 3、取得部门中（所有人的）平均的薪水等级 4、不准用组函数（Max），查询最高薪水 5、查询平均薪水最高的部门的部门编号 6、查询平均薪水最高的部门的部门名称 7、求平均薪水的等级最低的部门的部门名称 8、查询比普通员工(员工代表没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名 9、查询薪水最高的前五名员工 10、查询薪水最高的第六到第十名员工 11、查询查询最后入职的 5 名员工 12、查询每个薪水等级有多少员工 13、面试题： 14、列出所有员工及领导的姓名 15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称 16、列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门 17、列出至少有 5 个员工的所有部门 18、列出薪金比"SMITH" 多的所有员工信息 19、列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数 20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数,按照工作岗位分组求最小值 21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号 22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级 23、列出与"SCOTT" 从事相同工作的所有员工及部门名称 24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金 25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称 26、列出在每个部门工作的员工数量, 平均工资和平均服务期限 26、在mysql当中怎么计算两个日期的“年差”，差了多少年【知识点】 27、列出所有员工的姓名、部门名称和工资 28、列出所有部门的详细信息和人数 29、列出各种工作的最低工资及从事此工作的雇员姓名 30、列出各个部门的 MANAGER( 领导) 的最低薪金 31、列出所有员工的 年工资, 按 年薪从低到高排序 32、求出员工领导的薪水超过3000的员工名称与领导 33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数 34、给任职日期超过 30 年的员工加薪 10%三、动力节点—Mysql—经典34道答案3.1、查询每个部门最高薪水的人员名称-- 第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值) select deptno, max(sal) from emp group by deptno;mysql> select -> deptno, -> max(sal) -> from emp -> group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000 | | 20 | 3000 | | 30 | 2850 | +--------+----------+ 3 rows in set (0.00 sec)-- 第二步：将以上的查询结果当做一张临时表t， -- t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal select e.ename, t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno = e.deptno and t.maxsal = e.sal;mysql> select -> e.ename, -> t.* -> from emp e -> join (select deptno,max(sal) as maxsal from emp group by deptno) t -> on t.deptno = e.deptno and t.maxsal = e.sal; +-------+--------+--------+ | ename | deptno | maxsal | +-------+--------+--------+ | BLAKE | 30 | 2850 | | SCOTT | 20 | 3000 | | KING | 10 | 5000 | | FORD | 20 | 3000 | +-------+--------+--------+ 4 rows in set (0.00 sec)select e1.ename, e1.sal, count(distinct e2.sal) rank from emp e1 join emp e2 on e1.deptno = e2.deptno and e1.sal <= e2.sal group by e1.empno order by rank;mysql> select -> e1.ename, -> e1.sal, -> count(distinct e2.sal) rank -> from emp e1 -> join emp e2 on e1.deptno = e2.deptno and e1.sal <= e2.sal -> group by e1.empno -> order by rank; +--------+------+------+ | ename | sal | rank | +--------+------+------+ | SCOTT | 3000 | 1 | | FORD | 3000 | 1 | | KING | 5000 | 1 | | BLAKE | 2850 | 1 | | JONES | 2975 | 2 | | ALLEN | 1600 | 2 | | CLARK | 2450 | 2 | | MILLER | 1300 | 3 | | TURNER | 1500 | 3 | | ADAMS | 1100 | 3 | | WARD | 1250 | 4 | | MARTIN | 1250 | 4 | | SMITH | 800 | 4 | | JAMES | 950 | 5 | +--------+------+------+ 14 rows in set (0.00 sec)select e1.ename, e1.sal, count(distinct e2.sal) rank from emp e1 join emp e2 on e1.deptno = e2.deptno and e1.sal <= e2.sal group by e1.empno having rank = 1 order by rank;mysql> select -> e1.ename, -> e1.sal, -> count(distinct e2.sal) rank -> from emp e1 -> join emp e2 on e1.deptno = e2.deptno and e1.sal <= e2.sal -> group by e1.empno -> having rank = 1 -> order by rank; +-------+------+------+ | ename | sal | rank | +-------+------+------+ | SCOTT | 3000 | 1 | | FORD | 3000 | 1 | | KING | 5000 | 1 | | BLAKE | 2850 | 1 | +-------+------+------+ 4 rows in set (0.01 sec)3.2、哪些人的薪水在部门的平均薪水之上-- 第一步：找出每个部门的平均薪水 select e.deptno, avg(e.sal) from emp e group by e.deptno;mysql> select -> e.deptno, -> avg(e.sal) -> from emp e -> group by e.deptno; +--------+--------------------+ | deptno | avg(e.sal) | +--------+--------------------+ | 10 | 2916.6666666666665 | | 20 | 2175 | | 30 | 1566.6666666666667 | +--------+--------------------+ 3 rows in set (0.00 sec)-- 第二步：将以上查询结果当做t表，t和emp表连接 -- 条件：部门编号相同，并且emp的sal大于t表的avgsal select e.ename, e.sal, t.deptno, round(t.avgsal,2) '平均薪资' from emp e join (select deptno,avg(sal) avgsal from emp group by deptno) t on e.deptno = t.deptno and e.sal > t.avgsal order by e.deptno;mysql> select -> e.ename, -> e.sal, -> t.deptno, -> round(t.avgsal,2) '平均薪资' -> from emp e -> join (select deptno,avg(sal) avgsal from emp group by deptno) t -> on e.deptno = t.deptno and e.sal > t.avgsal -> order by e.deptno; +-------+------+--------+--------------+ | ename | sal | deptno | 平均薪资 | +-------+------+--------+--------------+ | KING | 5000 | 10 | 2916.67 | | JONES | 2975 | 20 | 2175.00 | | SCOTT | 3000 | 20 | 2175.00 | | FORD | 3000 | 20 | 2175.00 | | ALLEN | 1600 | 30 | 1566.67 | | BLAKE | 2850 | 30 | 1566.67 | +-------+------+--------+--------------+ 6 rows in set (0.00 sec)3.3、查询部门中（所有人的）平均的薪水等级3.3.1.平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。-- 第一步：找出每个人的薪水等级 -- emp e和salgrade s表连接 -- 连接条件：e.sal between s.losal and s.hisal select e.ename, e.sal, e.deptno, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;mysql> select -> e.ename, -> e.sal, -> e.deptno, -> s.grade -> from emp e -> join salgrade s on e.sal between s.losal and s.hisal; +--------+------+--------+-------+ | ename | sal | deptno | grade | +--------+------+--------+-------+ | SMITH | 800 | 20 | 1 | | ALLEN | 1600 | 30 | 3 | | WARD | 1250 | 30 | 2 | | JONES | 2975 | 20 | 4 | | MARTIN | 1250 | 30 | 2 | | BLAKE | 2850 | 30 | 4 | | CLARK | 2450 | 10 | 4 | | SCOTT | 3000 | 20 | 4 | | KING | 5000 | 10 | 5 | | TURNER | 1500 | 30 | 3 | | ADAMS | 1100 | 20 | 1 | | JAMES | 950 | 30 | 1 | | FORD | 3000 | 20 | 4 | | MILLER | 1300 | 10 | 2 | +--------+------+--------+-------+ 14 rows in set (0.00 sec)-- 第二步：基于以上的结果继续按照deptno分组，求grade的平均值 select e.deptno, avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;mysql> select -> e.deptno, -> avg(s.grade) -> from emp e -> join salgrade s on e.sal between s.losal and s.hisal -> group by e.deptno; +--------+--------------+ | deptno | avg(s.grade) | +--------+--------------+ | 10 | 3.6667 | | 20 | 2.8000 | | 30 | 2.5000 | +--------+--------------+ 3 rows in set (0.00 sec)3.3.2.平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值select e.deptno, avg(e.sal) from emp e group by e.deptno;mysql> select -> e.deptno, -> avg(e.sal) -> from emp e -> group by e.deptno; +--------+--------------------+ | deptno | avg(e.sal) | +--------+--------------------+ | 10 | 2916.6666666666665 | | 20 | 2175 | | 30 | 1566.6666666666667 | +--------+--------------------+ 3 rows in set (0.00 sec)select t.deptno, round(t.avgsal,2), s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;mysql> select -> t.deptno, -> round(t.avgsal,2), -> s.grade -> from (select deptno,avg(sal) avgsal from emp group by deptno) t -> join salgrade s on t.avgsal between s.losal and s.hisal; +--------+-------------------+-------+ | deptno | round(t.avgsal,2) | grade | +--------+-------------------+-------+ | 10 | 2916.67 | 4 | | 20 | 2175.00 | 4 | | 30 | 1566.67 | 3 | +--------+-------------------+-------+ 3 rows in set (0.00 sec)3.4、不准用组函数（Max），查询最高薪水select ename,sal from emp order by sal desc limit 1;mysql> select ename,sal from emp order by sal desc limit 1; +-------+------+ | ename | sal | +-------+------+ | KING | 5000 | +-------+------+ 1 row in set (0.00 sec)select max(sal) from emp;mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000 | +----------+ 1 row in set (0.01 sec)select ename, sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);mysql> select -> ename, -> sal -> from emp -> where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal); +-------+------+ | ename | sal | +-------+------+ | KING | 5000 | +-------+------+ 1 row in set (0.00 sec)3.5、查询平均薪水最高的部门的部门编号-- 第一步：找出每个部门的平均薪水 select deptno,round(avg(sal),2) avgsal from emp group by deptno;mysql> select deptno,round(avg(sal),2) avgsal from emp group by deptno; +--------+---------+ | deptno | avgsal | +--------+---------+ | 10 | 2916.67 | | 20 | 2175.00 | | 30 | 1566.67 | +--------+---------+ 3 rows in set (0.01 sec)-- 第二步：降序选第一个 select deptno,round(avg(sal),2) avgsal from emp group by deptno order by avgsal desc limit 1;mysql> select deptno,round(avg(sal),2) avgsal from emp group by deptno order by avgsal desc limit 1; +--------+---------+ | deptno | avgsal | +--------+---------+ | 10 | 2916.67 | +--------+---------+ 1 row in set (0.00 sec)select deptno, avg(sal) avgsal from emp group by deptno having avgsal = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t); -- 使用having语句过滤mysql> select -> deptno, -> avg(sal) avgsal -> from emp -> group by deptno -> having avgsal = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t); +--------+--------------------+ | deptno | avgsal | +--------+--------------------+ | 10 | 2916.6666666666665 | +--------+--------------------+ 1 row in set (0.00 sec)3.6、查询平均薪水最高的部门的部门名称select e1.deptno, d.dname, e1.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) e1 join dept d on e1.deptno = d.deptno having e1.avgsal = (select max(e.avgsal)from (select deptno,avg(sal) avgsal from emp group by deptno) e);mysql> select -> e1.deptno, -> d.dname, -> e1.avgsal -> from (select deptno,avg(sal) avgsal from emp group by deptno) e1 -> join dept d on e1.deptno = d.deptno -> having e1.avgsal = (select max(e.avgsal)from (select deptno,avg(sal) avgsal from emp group by deptno) e); +--------+------------+--------------------+ | deptno | dname | avgsal | +--------+------------+--------------------+ | 10 | ACCOUNTING | 2916.6666666666665 | +--------+------------+--------------------+ 1 row in set (0.00 sec)select d.dname, avg(e.sal) avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno order by avgsal desc limit 1;mysql> select -> d.dname, -> avg(e.sal) avgsal -> from emp e -> join dept d on e.deptno = d.deptno -> group by e.deptno -> order by avgsal desc -> limit 1; +------------+--------------------+ | dname | avgsal | +------------+--------------------+ | ACCOUNTING | 2916.6666666666665 | +------------+--------------------+ 1 row in set (0.00 sec)3.7、求平均薪水的等级最低的部门的部门名称select e.avgsal, s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) e join salgrade s on e.avgsal between s.losal and s.hisal group by deptno;mysql> select -> e.avgsal, -> s.grade -> from (select deptno,avg(sal) avgsal from emp group by deptno) e -> join salgrade s on e.avgsal between s.losal and s.hisal -> group by deptno; +--------------------+-------+ | avgsal | grade | +--------------------+-------+ | 2916.6666666666665 | 4 | | 2175 | 4 | | 1566.6666666666667 | 3 | +--------------------+-------+ 3 rows in set (0.00 sec)select e.avgsal, s.grade, d.deptno, d.dname from (select deptno,avg(sal) avgsal from emp group by deptno) e join dept d on e.deptno = d.deptno join salgrade s on e.avgsal between s.losal and s.hisal group by deptno order by s.grade asc limit 1;mysql> select -> e.avgsal, -> s.grade, -> d.deptno, -> d.dname -> from (select deptno,avg(sal) avgsal from emp group by deptno) e -> join dept d on e.deptno = d.deptno -> join salgrade s on e.avgsal between s.losal and s.hisal -> group by deptno -> order by s.grade asc limit 1; +--------------------+-------+--------+-------+ | avgsal | grade | deptno | dname | +--------------------+-------+--------+-------+ | 1566.6666666666667 | 3 | 30 | SALES | +--------------------+-------+--------+-------+ 1 row in set (0.00 sec)3.8、查询比普通员工(员工代表没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名-- 普通员工没有出现在mgr上，查找mgr select distinct mgr from emp where mgr is not null;mysql> select distinct mgr from emp where mgr is not null; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | 7788 | | 7782 | +------+ 6 rows in set (0.00 sec)员工编号没有在以上范围内的都是普通员工 -- 第一步：找出普通员工的最高薪水！ -- not in在使用的时候，后面小括号中记得排除NULL select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);mysql> select max(sal) -> from emp -> where empno not in (select distinct mgr from emp where mgr is not null); +----------+ | max(sal) | +----------+ | 1600 | +----------+ 1 row in set (0.00 sec)-- 第二步：找出高于1600的,比普通员工最高薪水的经理人名字 select ename,sal from emp where sal > ( select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));mysql> select ename,sal from emp -> where sal > ( -> select max(sal) -> from emp -> where empno not in (select distinct mgr from emp where mgr is not null)); +-------+------+ | ename | sal | +-------+------+ | JONES | 2975 | | BLAKE | 2850 | | CLARK | 2450 | | SCOTT | 3000 | | KING | 5000 | | FORD | 3000 | +-------+------+ 6 rows in set (0.00 sec)3.9、查询薪水最高的前五名员工select ename, sal from emp order by sal desc limit 5;mysql> select -> ename, -> sal -> from emp -> order by sal desc -> limit 5; +-------+------+ | ename | sal | +-------+------+ | KING | 5000 | | FORD | 3000 | | SCOTT | 3000 | | JONES | 2975 | | BLAKE | 2850 | +-------+------+ 5 rows in set (0.00 sec)mysql> select -> ename, -> sal -> from emp -> order by sal desc -> limit 0,5; +-------+------+ | ename | sal | +-------+------+ | KING | 5000 | | FORD | 3000 | | SCOTT | 3000 | | JONES | 2975 | | BLAKE | 2850 | +-------+------+ 5 rows in set (0.00 sec)3.10、查询薪水最高的第六到第十名员工select ename, sal from emp order by sal desc limit 5,5;mysql> select -> ename, -> sal -> from emp -> order by sal desc -> limit 5,5; +--------+------+ | ename | sal | +--------+------+ | CLARK | 2450 | | ALLEN | 1600 | | TURNER | 1500 | | MILLER | 1300 | | WARD | 1250 | +--------+------+ 5 rows in set (0.00 sec)3.11、查询最后入职的 5 名员工select ename, hiredate from emp order by hiredate desc limit 0,5;mysql> select -> ename, -> hiredate -> from emp -> order by hiredate desc limit 0,5; +-------+------------+ | ename | hiredate | +-------+------------+ | ADAMS | 1987-07-13 | | SCOTT | 1987-07-03 | | JAMES | 1981-12-03 | | FORD | 1981-12-03 | | KING | 1981-11-17 | +-------+------------+ 5 rows in set (0.00 sec)3.12、查询每个薪水等级有多少员工-- 第一步：找出每个员工的薪水等级 select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;mysql> select -> e.ename, -> e.sal, -> s.grade -> from emp e -> join salgrade s on e.sal between s.losal and s.hisal; +--------+------+-------+ | ename | sal | grade | +--------+------+-------+ | SMITH | 800 | 1 | | ALLEN | 1600 | 3 | | WARD | 1250 | 2 | | JONES | 2975 | 4 | | MARTIN | 1250 | 2 | | BLAKE | 2850 | 4 | | CLARK | 2450 | 4 | | SCOTT | 3000 | 4 | | KING | 5000 | 5 | | TURNER | 1500 | 3 | | ADAMS | 1100 | 1 | | JAMES | 950 | 1 | | FORD | 3000 | 4 | | MILLER | 1300 | 2 | +--------+------+-------+ 14 rows in set (0.00 sec)-- 第二步：继续按照grade分组统计数量 select s.grade, count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;mysql> select -> s.grade, -> count(*) -> from emp e -> join salgrade s on e.sal between s.losal and s.hisal -> group by s.grade; +-------+----------+ | grade | count(*) | +-------+----------+ | 1 | 3 | | 2 | 3 | | 3 | 2 | | 4 | 5 | | 5 | 1 | +-------+----------+ 5 rows in set (0.00 sec)3.13、面试题：有 3 个表 S(学生表)，C（课程表），SC（学生选课表）S（SNO，SNAME）代表（学号，姓名）C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）问题：1，找出没选过“黎明”老师的所有学生姓名。2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。3，即学过 1 号课程又学过 2 号课所有学生的姓名。3.14、列出所有员工及领导的姓名select e1.ename '员工', e2.ename '领导' from emp e1 join emp e2 on e1.mgr = e2.empno;mysql> select -> e1.ename '员工', -> e2.ename '领导' -> from emp e1 -> join emp e2 on e1.mgr = e2.empno; +--------+--------+ | 员工 | 领导 | +--------+--------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+--------+ 13 rows in set (0.00 sec)3.15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称emp e1 员工表 emp e2 领导表 e1.mgr = e2.empno and e1.hiredate < e2.hiredate select e1.ename '员工', e1.hiredate, e2.ename '领导', e2.hiredate, d.dname '部门名称' from emp e1 join emp e2 on e1.mgr = e2.empno join dept d on e1.deptno = d.deptno where e1.hiredate < e2.hiredate;mysql> select -> e1.ename '员工', -> e1.hiredate, -> e2.ename '领导', -> e2.hiredate, -> d.dname '部门名称' -> from emp e1 -> join emp e2 on e1.mgr = e2.empno -> join dept d on e1.deptno = d.deptno -> where e1.hiredate < e2.hiredate; +--------+------------+--------+------------+--------------+ | 员工 | hiredate | 领导 | hiredate | 部门名称 | +--------+------------+--------+------------+--------------+ | SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH | | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES | | WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES | | JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH | | BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES | | CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING | | MILLER | 1981-01-23 | CLARK | 1981-06-09 | ACCOUNTING | +--------+------------+--------+------------+--------------+ 7 rows in set (0.00 sec)3.16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门select e.*, d.dname '部门名称' from dept d left join emp e on d.deptno = e.deptno;mysql> select -> e.*, -> d.dname '部门名称' -> from dept d -> left join emp e on d.deptno = e.deptno; +-------+--------+-----------+------+------------+------+------+--------+--------------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | 部门名称 | +-------+--------+-----------+------+------------+------+------+--------+--------------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | RESEARCH | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | SALES | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | SALES | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | RESEARCH | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | SALES | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | SALES | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | ACCOUNTING | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | RESEARCH | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | ACCOUNTING | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | SALES | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | RESEARCH | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | SALES | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | RESEARCH | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | ACCOUNTING | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | OPERATIONS | +-------+--------+-----------+------+------------+------+------+--------+--------------+ 15 rows in set (0.00 sec)3.17、列出至少有 5 个员工的所有部门-- 每个部门的员工数量 select deptno,count(*) from emp group by deptno;mysql> select deptno,count(*) from emp group by deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+----------+ 3 rows in set (0.00 sec)-- 按照部门编号分组，计数，筛选出 >= 5 select deptno, count(*) from emp group by deptno having count(*) >= 5;mysql> select -> deptno, -> count(*) -> from emp -> group by deptno -> having count(*) >= 5; +--------+----------+ | deptno | count(*) | +--------+----------+ | 20 | 5 | | 30 | 6 | +--------+----------+ 2 rows in set (0.00 sec)3.18、列出薪金比"SMITH" 多的所有员工信息select sal from emp where ename = 'smith';mysql> select sal from emp where ename = 'smith'; +------+ | sal | +------+ | 800 | +------+ 1 row in set (0.00 sec)select ename, sal from emp where sal > (select sal from emp where ename = 'smith');mysql> select -> ename, -> sal -> from emp -> where sal > (select sal from emp where ename = 'smith'); +--------+------+ | ename | sal | +--------+------+ | ALLEN | 1600 | | WARD | 1250 | | JONES | 2975 | | MARTIN | 1250 | | BLAKE | 2850 | | CLARK | 2450 | | SCOTT | 3000 | | KING | 5000 | | TURNER | 1500 | | ADAMS | 1100 | | JAMES | 950 | | FORD | 3000 | | MILLER | 1300 | +--------+------+ 13 rows in set (0.00 sec)3.19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数select ename,job from emp where job = 'clerk';mysql> select ename,job from emp where job = 'clerk'; +--------+-------+ | ename | job | +--------+-------+ | SMITH | CLERK | | ADAMS | CLERK | | JAMES | CLERK | | MILLER | CLERK | +--------+-------+ 4 rows in set (0.00 sec)select e.ename, e.job, d.dname, d.deptno from emp e join dept d on e.deptno = d.deptno where job = 'clerk';mysql> select -> e.ename, -> e.job, -> d.dname, -> d.deptno -> from emp e -> join dept d on e.deptno = d.deptno -> where job = 'clerk'; +--------+-------+------------+--------+ | ename | job | dname | deptno | +--------+-------+------------+--------+ | SMITH | CLERK | RESEARCH | 20 | | ADAMS | CLERK | RESEARCH | 20 | | JAMES | CLERK | SALES | 30 | | MILLER | CLERK | ACCOUNTING | 10 | +--------+-------+------------+--------+ 4 rows in set (0.00 sec)select deptno,count(*) deptcount from emp group by deptno;mysql> select deptno,count(*) deptcount from emp group by deptno; +--------+-----------+ | deptno | deptcount | +--------+-----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+-----------+ 3 rows in set (0.00 sec)select t1.*, t2.deptcount from (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno where job = 'clerk') t1 join (select deptno,count(*) deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno;mysql> select -> t1.*, -> t2.deptcount -> from (select e.ename,e.job,d.dname,d.deptno from emp e -> join dept d on e.deptno = d.deptno -> where job = 'clerk') t1 -> join (select deptno,count(*) deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno; +--------+-------+------------+--------+-----------+ | ename | job | dname | deptno | deptcount | +--------+-------+------------+--------+-----------+ | SMITH | CLERK | RESEARCH | 20 | 5 | | ADAMS | CLERK | RESEARCH | 20 | 5 | | JAMES | CLERK | SALES | 30 | 6 | | MILLER | CLERK | ACCOUNTING | 10 | 3 | +--------+-------+------------+--------+-----------+ 4 rows in set (0.00 sec)3.20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数,按照工作岗位分组求最小值select job,min(sal) from emp group by job;mysql> select job,min(sal) from emp group by job; +-----------+----------+ | job | min(sal) | +-----------+----------+ | ANALYST | 3000 | | CLERK | 800 | | MANAGER | 2450 | | PRESIDENT | 5000 | | SALESMAN | 1250 | +-----------+----------+ 5 rows in set (0.00 sec)select job,count(*),min(sal) from emp group by job having min(sal) > 1500;mysql> select job,count(*),min(sal) from emp group by job having min(sal) > 1500; +-----------+----------+----------+ | job | count(*) | min(sal) | +-----------+----------+----------+ | ANALYST | 2 | 3000 | | MANAGER | 3 | 2450 | | PRESIDENT | 1 | 5000 | +-----------+----------+----------+ 3 rows in set (0.01 sec)3.21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号-- 部门编号 select deptno from dept where dname = 'sales';mysql> select deptno from dept where dname = 'sales'; +--------+ | deptno | +--------+ | 30 | +--------+ 1 row in set (0.00 sec)-- 员工姓名 select ename from emp where deptno = (select deptno from dept where dname = 'sales');mysql> select ename from emp where deptno = (select deptno from dept where dname = 'sales'); +--------+ | ename | +--------+ | ALLEN | | WARD | | MARTIN | | BLAKE | | TURNER | | JAMES | +--------+ 6 rows in set (0.00 sec)3.22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级select avg(sal) avgsal from emp;mysql> select avg(sal) avgsal from emp; +-------------------+ | avgsal | +-------------------+ | 2073.214285714286 | +-------------------+ 1 row in set (0.00 sec)select e1.ename '员工名字', d.dname '所在部门', e2.ename '上级领导', s.grade '工资等级' from emp e1 left join emp e2 on e1.mgr = e2.empno join dept d on e1.deptno = d.deptno join salgrade s on e1.sal between s.losal and s.hisal where e1.sal > (select avg(sal) from emp);mysql> select -> e1.ename '员工名字', -> d.dname '所在部门', -> e2.ename '上级领导', -> s.grade '工资等级' -> from emp e1 -> left join emp e2 on e1.mgr = e2.empno -> join dept d on e1.deptno = d.deptno -> join salgrade s on e1.sal between s.losal and s.hisal -> where e1.sal > (select avg(sal) from emp); +--------------+--------------+--------------+--------------+ | 员工名字 | 所在部门 | 上级领导 | 工资等级 | +--------------+--------------+--------------+--------------+ | JONES | RESEARCH | KING | 4 | | BLAKE | SALES | KING | 4 | | CLARK | ACCOUNTING | KING | 4 | | SCOTT | RESEARCH | JONES | 4 | | KING | ACCOUNTING | NULL | 5 | | FORD | RESEARCH | JONES | 4 | +--------------+--------------+--------------+--------------+ 6 rows in set (0.00 sec)3.23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称select * from emp where ename = 'scott';mysql> select * from emp where ename = 'scott'; +-------+-------+---------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+---------+------+------------+------+------+--------+ | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | +-------+-------+---------+------+------------+------+------+--------+ 1 row in set (0.00 sec)select e.*, d.dname from emp e join dept d on e.deptno = d.deptno where e.job = (select job from emp where ename = 'scott') and e.ename <> 'scott';mysql> select -> e.*, -> d.dname -> from emp e -> join dept d on e.deptno = d.deptno -> where e.job = (select job from emp where ename = 'scott') and e.ename <> 'scott'; +-------+-------+---------+------+------------+------+------+--------+----------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | dname | +-------+-------+---------+------+------------+------+------+--------+----------+ | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | RESEARCH | +-------+-------+---------+------+------------+------+------+--------+----------+ 1 row in set (0.00 sec)3.24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金select * from emp where deptno = 30;mysql> select * from emp where deptno = 30; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | +-------+--------+----------+------+------------+------+------+--------+ 6 rows in set (0.00 sec)select distinct sal from emp where deptno = 30;mysql> select distinct sal from emp where deptno = 30; +------+ | sal | +------+ | 1600 | | 1250 | | 2850 | | 1500 | | 950 | +------+ 5 rows in set (0.00 sec)select ename, sal from emp where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;mysql> select -> ename, -> sal -> from emp -> where sal in (select distinct sal from emp where deptno = 30) -> and deptno <> 30; Empty set (0.00 sec)3.25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称select max(sal) from emp where deptno = 30;mysql> select max(sal) from emp where deptno = 30; +----------+ | max(sal) | +----------+ | 2850 | +----------+ 1 row in set (0.00 sec)select e.ename, e.sal, d.dname from emp e join dept d on e.deptno = d.deptno where e.sal > (select max(sal) from emp where deptno = 30);mysql> select -> e.ename, -> e.sal, -> d.dname -> from emp e -> join dept d on e.deptno = d.deptno -> where e.sal > (select max(sal) from emp where deptno = 30); +-------+------+------------+ | ename | sal | dname | +-------+------+------------+ | JONES | 2975 | RESEARCH | | SCOTT | 3000 | RESEARCH | | KING | 5000 | ACCOUNTING | | FORD | 3000 | RESEARCH | +-------+------+------------+ 4 rows in set (0.00 sec)3.26、列出在每个部门工作的员工数量, 平均工资和平均服务期限没有员工的部门，部门人数是0select d.deptno, count(e.ename), ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime from dept d left join emp e on d.deptno = e.deptno group by d.deptno;mysql> select -> d.deptno, -> count(e.ename), -> ifnull(avg(e.sal),0) as avgsal, -> ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime -> from dept d -> left join emp e on d.deptno = e.deptno -> group by d.deptno; +--------+----------------+--------------------+----------------+ | deptno | count(e.ename) | avgsal | avgservicetime | +--------+----------------+--------------------+----------------+ | 10 | 3 | 2916.6666666666665 | 42.3333 | | 20 | 5 | 2175 | 39.8000 | | 30 | 6 | 1566.6666666666667 | 42.0000 | | 40 | 0 | 0 | 0.0000 | +--------+----------------+--------------------+----------------+ 4 rows in set (0.00 sec)3.26、在mysql当中怎么计算两个日期的“年差”，差了多少年【知识点】TimeStampDiff(间隔类型, 前一个日期, 后一个日期) timestampdiff(YEAR, hiredate, now()) 间隔类型： SECOND 秒， MINUTE 分钟， HOUR 小时， DAY 天， WEEK 星期 MONTH 月， QUARTER 季度， YEAR 年3.27、 列出所有员工的姓名、部门名称和工资select e.ename, d.dname, e.sal from emp e join dept d on e.deptno = d.deptno order by e.sal desc;mysql> select -> e.ename, -> d.dname, -> e.sal -> from emp e -> join dept d on e.deptno = d.deptno -> order by e.sal desc; +--------+------------+------+ | ename | dname | sal | +--------+------------+------+ | KING | ACCOUNTING | 5000 | | SCOTT | RESEARCH | 3000 | | FORD | RESEARCH | 3000 | | JONES | RESEARCH | 2975 | | BLAKE | SALES | 2850 | | CLARK | ACCOUNTING | 2450 | | ALLEN | SALES | 1600 | | TURNER | SALES | 1500 | | MILLER | ACCOUNTING | 1300 | | MARTIN | SALES | 1250 | | WARD | SALES | 1250 | | ADAMS | RESEARCH | 1100 | | JAMES | SALES | 950 | | SMITH | RESEARCH | 800 | +--------+------------+------+ 14 rows in set (0.00 sec)3.28、列出所有部门的详细信息和人数select d.*, count(e.ename) from dept d left join emp e on d.deptno = e.deptno group by d.deptno;mysql> select -> d.*, -> count(e.ename) -> from dept d -> left join emp e on d.deptno = e.deptno -> group by d.deptno; +--------+------------+----------+----------------+ | deptno | dname | loc | count(e.ename) | +--------+------------+----------+----------------+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | | 40 | OPERATIONS | BOSTON | 0 | +--------+------------+----------+----------------+ 4 rows in set (0.00 sec)3.29、列出各种工作的最低工资及从事此工作的雇员姓名select job,min(sal) from emp group by job;mysql> select job,min(sal) from emp group by job; +-----------+----------+ | job | min(sal) | +-----------+----------+ | ANALYST | 3000 | | CLERK | 800 | | MANAGER | 2450 | | PRESIDENT | 5000 | | SALESMAN | 1250 | +-----------+----------+ 5 rows in set (0.00 sec)select e.ename, t.job, t.minsal from emp e join (select job,min(sal) minsal from emp group by job) t on e.job = t.job and e.sal = t.minsal;mysql> select -> e.ename, -> t.job, -> t.minsal -> from emp e -> join (select job,min(sal) minsal from emp group by job) t -> on e.job = t.job and e.sal = t.minsal; +--------+-----------+--------+ | ename | job | minsal | +--------+-----------+--------+ | SMITH | CLERK | 800 | | WARD | SALESMAN | 1250 | | MARTIN | SALESMAN | 1250 | | CLARK | MANAGER | 2450 | | SCOTT | ANALYST | 3000 | | KING | PRESIDENT | 5000 | | FORD | ANALYST | 3000 | +--------+-----------+--------+ 7 rows in set (0.00 sec)3.30、列出各个部门的 MANAGER( 领导) 的最低薪金select deptno, min(sal) from emp where job = 'manager' group by deptno;mysql> select -> deptno, -> min(sal) -> from emp -> where job = 'manager' -> group by deptno; +--------+----------+ | deptno | min(sal) | +--------+----------+ | 10 | 2450 | | 20 | 2975 | | 30 | 2850 | +--------+----------+ 3 rows in set (0.00 sec)3.31、列出所有员工的 年工资, 按 年薪从低到高排序select ename, (sal + ifnull(comm,0) ) * 12 yearsal from emp order by yearsal asc;mysql> select -> ename, -> (sal + ifnull(comm,0) ) * 12 yearsal -> from emp -> order by yearsal asc; +--------+---------+ | ename | yearsal | +--------+---------+ | SMITH | 9600 | | JAMES | 11400 | | ADAMS | 13200 | | MILLER | 15600 | | TURNER | 18000 | | WARD | 21000 | | ALLEN | 22800 | | CLARK | 29400 | | MARTIN | 31800 | | BLAKE | 34200 | | JONES | 35700 | | SCOTT | 36000 | | FORD | 36000 | | KING | 60000 | +--------+---------+ 14 rows in set (0.01 sec)3.32、求出员工领导的薪水超过3000的员工名称与领导select e1.ename '员工', e1.sal '员工薪水', e2.ename '领导', e2.sal '领导薪水' from emp e1 join emp e2 on e1.mgr = e2.empno where e2.sal > 3000;mysql> select -> e1.ename '员工', -> e1.sal '员工薪水', -> e2.ename '领导', -> e2.sal '领导薪水' -> from emp e1 -> join emp e2 on e1.mgr = e2.empno -> where e2.sal > 3000; +--------+--------------+--------+--------------+ | 员工 | 员工薪水 | 领导 | 领导薪水 | +--------+--------------+--------+--------------+ | JONES | 2975 | KING | 5000 | | BLAKE | 2850 | KING | 5000 | | CLARK | 2450 | KING | 5000 | +--------+--------------+--------+--------------+ 3 rows in set (0.00 sec)3.33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数select d.*, count(e.ename), ifnull(sum(e.sal),0) sumsal from dept d left join emp e on d.deptno = e.deptno where d.dname like '%s%' group by d.deptno;mysql> select -> d.*, -> count(e.ename), -> ifnull(sum(e.sal),0) sumsal -> from dept d -> left join emp e on d.deptno = e.deptno -> where d.dname like '%s%' -> group by d.deptno; +--------+------------+---------+----------------+--------+ | deptno | dname | loc | count(e.ename) | sumsal | +--------+------------+---------+----------------+--------+ | 20 | RESEARCH | DALLAS | 5 | 10875 | | 30 | SALES | CHICAGO | 6 | 9400 | | 40 | OPERATIONS | BOSTON | 0 | 0 | +--------+------------+---------+----------------+--------+ 3 rows in set (0.00 sec)3.34、给任职日期超过 30 年的员工加薪 10%-- 复制一张表 create table emp_bak as select * from emp;mysql> create table emp_bak as select * from emp; Query OK, 14 rows affected (0.01 sec) Records: 14 Duplicates: 0 Warnings: 0 mysql> select * from emp_bak; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ 14 rows in set (0.00 sec)-- 更新表 update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;mysql> update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30; Query OK, 14 rows affected (0.01 sec) Rows matched: 14 Changed: 14 Warnings: 0 mysql> select * from emp; +-------+--------+-----------+------+------------+--------------------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+--------------------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 880.0000000000001 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1760.0000000000002 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1375 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3272.5000000000005 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1375 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 3135.0000000000005 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2695 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3300.0000000000005 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5500 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1650.0000000000002 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1210 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1045 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3300.0000000000005 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1430.0000000000002 | NULL | 10 | +-------+--------+-----------+------+------------+--------------------+------+--------+ 14 rows in set (0.00 sec)endl