/**
*子查询
*/
数据库:
/*
SQLyog v10.2
MySQL - 5.1.62-community : Database - sience
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_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 */;
/*Table structure for table `dept` */
CREATE TABLE `dept` (
`id` varchar(36) NOT NULL,
`deptno` varchar(36) NOT NULL COMMENT '部门编号',
`dname` varchar(36) NOT NULL COMMENT '部门名称',
`loc` varchar(36) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `dept` */
insert into `dept`(`id`,`deptno`,`dname`,`loc`) values ('1','01','测试部','测试'),('2','02','开发部','开发'),('3','04','售前部','售前'),('4','05','运维部','运维');
/*Table structure for table `emp` */
CREATE TABLE `emp` (
`empno` varchar(36) NOT NULL COMMENT '员工编号',
`ename` varchar(36) NOT NULL COMMENT '员工姓名',
`job` varchar(20) NOT NULL COMMENT '职位',
`sal` varchar(20) NOT NULL COMMENT '工资',
`deptno` varchar(36) NOT NULL COMMENT '部门编号',
`mgr` varchar(20) NOT NULL COMMENT '上级领导编号',
`comm` varchar(20) DEFAULT NULL COMMENT '奖金',
`hiredate` datetime NOT NULL COMMENT '入职时间',
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `emp` */
insert into `emp`(`empno`,`ename`,`job`,`sal`,`deptno`,`mgr`,`comm`,`hiredate`) values ('002','李四','开发','6000','02','002','20','2016-03-01 14:21:28'),('1','张三','测试','3000','01','001','20','2016-03-01 14:21:28'),('3','琳子','测试','4500','01','003','20','2017-03-01 14:35:07'),('4','糖衣','售前','5000','04','004','50','2016-07-11 14:36:03'),('5','古道','开发','7000','05','005','100','2015-03-01 14:36:50');
/*Table structure for table `salgrade` */
CREATE TABLE `salgrade` (
`id` varchar(36) NOT NULL,
`losal` varchar(36) DEFAULT NULL COMMENT '最低工资',
`hisal` varchar(36) DEFAULT NULL COMMENT '最高工资',
`grade` varchar(10) NOT NULL COMMENT '工资等级',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `salgrade` */
insert into `salgrade`(`id`,`losal`,`hisal`,`grade`) values ('',NULL,NULL,''),('1','1000','3000','5'),('2','3100','6000','4'),('3','6100','8000','3'),('4','8100','9000','2');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
查询emp中工资高于平均工资的员工编号,姓名,工资
SELECT AVG(sal) FROM emp#首先我们需要先查询出平均工资
通过子查询进行对比
SELECT
e.empno,
e.ename,
e.sal
FROM
emp e
WHERE sal >
(SELECT
AVG(sal)
FROM
emp)
#查询emp表中工资高于04号部门最高工资的员工的编号,姓名,职位,工资,部门编号
SELECT MAX(sal)FROM emp e WHERE e.deptno=04#先查询员工最高工资,然后接条件,工资的部门编号要等于04
SELECT e.empno,e.ename,e.job,e.sal,e.deptno FROM emp e WHERE e.sal>(SELECT MAX(sal)FROM emp e WHERE e.deptno=04)
查询名字是‘李四’所属的部门的编号,名称
SELECT deptno FROM emp WHERE ename=’李四’#先查出李四所属部门的编号
SELECT dname FROM dept WHERE deptno=(SELECT deptno FROM emp WHERE ename=’李四’)#得到李四的编号后,根据编号能得到部门其他信息
查出emp表中工资最低的员工的编号,姓名,职位,工资
SELECT MIN(sal) FROM emp#首先查询最低员工工资
SELECT empno,ename,job,sal FROM emp WHERE sal=(SELECT MIN(sal) FROM emp)
查询部门名称是‘开发部’下员工的编号,姓名,职位,部门编号
SELECT dname FROM dept WHERE dname=’开发部’
SELECT empno,ename,job,deptno FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname=’开发部’)
查询部门地址loc是‘测试’的部门下的所有员工的信息
SELECT deptno FROM dept WHERE loc=’测试’
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc=’测试’)
查询emp表中工资在5000-8000之间的员工所属的部门的编号,名称
SELECT deptno FROM emp WHERE sal BETWEEN 5000 AND 8000
SELECT deptno,dname FROM dept WHERE deptno IN (SELECT deptno FROM emp WHERE sal BETWEEN 5000 AND 8000)
查询跟、李四、同一个部门的员工信息,包含’李四‘
SELECT deptno FROM emp WHERE ename=’李四’#首先查询出李四的部门编号
SELECT * FROM emp WHERE deptno= (SELECT deptno FROM emp WHERE ename=’李四’)#根据部门编号,得到员工信息
查询跟、李四、同一个部门的员工信息,不包含’李四‘
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE ename=’李四’) AND ename !=’李四’#!=不包含
查询跟、李四、同一个工作的员工信息,不包含’李四‘
SELECT job FROM emp WHERE ename=’李四’
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename=’李四’)AND ename <> ‘李四’#<>代表不包含