(五)练习Query查询语句

1.创建数据库

2.在Query中输入如下:

/*
Navicat MySQL Data Transfer

Source Server         : forCourse
Source Server Version : 50162
Source Host           : localhost:3306
Source Database       : forselect

Target Server Type    : MYSQL
Target Server Version : 50162
File Encoding         : 65001

Date: 2015-07-11 21:58:10
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `bonus`
-- ----------------------------
DROP TABLE IF EXISTS `bonus`;
CREATE TABLE `bonus` (
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of bonus
-- ----------------------------

-- ----------------------------
-- Table structure for `dept`
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dname` varchar(15) DEFAULT NULL,
  `loc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=gbk;

-- ----------------------------
-- 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');

-- ----------------------------
-- Table structure for `emp`
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ename` varchar(15) DEFAULT NULL,
  `job` varchar(10) DEFAULT NULL,
  `mgr` int(10) unsigned DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-07-13', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-07-13', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

-- ----------------------------
-- Table structure for `salgrade`
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(10) unsigned DEFAULT NULL,
  `losal` int(10) unsigned DEFAULT NULL,
  `hisal` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- ----------------------------
-- 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');

3.练习查询语句

– 01.查询员工表所有数据，并说明使用*的缺点
SELECT * from emp;

– 02.查询职位（JOB）为’PRESIDENT’的员工的工资
SELECT sal from emp WHERE job=‘PRESIDENT’;

– 03.查询佣金（COMM）为0或为NULL的员工信息
SELECT * from emp where comm=0 or comm is null;

– 04.查询入职日期在1982-5-1到1981-12-31之间的所有员工信息
SELECT * from emp WHERE TO_DAYS(hiredate) <=TO_DAYS(‘1982-05-01’) and TO_DAYS(hiredate) >=TO_DAYS(‘1981-12-31’);

– 05.查询所有名字长度为4的员工的员工编号，姓名
SELECT * from emp WHERE LENGTH(ename)=4;

– 06.显示10号部门的所有经理（‘MANAGER’）和20号部门的所有职员（‘CLERK’）的详细信息
SELECT * FROM emp where (deptno=10 and job=‘MANAGER’) or (deptno=20 and job=‘CLERK’);

– 07.显示姓名中没有’L’字的员工的详细信息或含有’SM’字的员工信息
select * from emp where ename not like ‘%L%’ and ename NOT like ‘%L’ and ename not like ‘L%’ or ename like ‘%SM%’;

– 08.显示各个部门经理（‘MANAGER’）的工资
SELECT ename ,sal from emp WHERE job =‘MANAGER’;

– 09.显示佣金（COMM）收入比工资（SAL）高的员工的详细信息
SELECT * from emp WHERE comm>sal;

– 10.把hiredate列看做是员工的生日，求本月过生日的员工（考察知识点：单行函数）
SELECT ename ,hiredate from emp where MONTH(hiredate)=MONTH(NOW());

– 11.把hiredate列看做是员工的生日，求下月过生日的员工（考察知识点：单行函数）
SELECT ename ,hiredate from emp where MONTH(hiredate)=IF(MONTH(NOW())=12,0,12)+1;

– 12.求1982年入职的员工（考察知识点：单行函数）
SELECT * from emp WHERE YEAR(hiredate)=1982;

– 13.求1981年下半年入职的员工（考察知识点：单行函数）
SELECT * from emp WHERE YEAR(hiredate)=1981 and MONTH(hiredate)>6;

– 14.求1981年各个月入职的员工个数（考察知识点：组函数）
SELECT mon ,COUNT(mon) sum from (SELECT MONTH(hiredate) mon from emp WHERE YEAR(hiredate)=1981) c GROUP BY mon;

– 15.查询各个部门的平均工资
SELECT deptno,avg(sal) from emp GROUP BY deptno;

– 16.显示各种职位的最低工资
SELECT job,MIN(sal) from emp GROUP BY job;

– 17.按照入职日期由新到旧排列员工信息
SELECT * from emp ORDER BY hiredate desc;

– 18.查询员工的基本信息，附加其上级的姓名
SELECT a.*,b.ename from emp a LEFT JOIN emp b on a.mgr=b.empno;

– 19.显示工资比’ALLEN’高的所有员工的姓名和工资
SELECT ename,sal from emp WHERE sal>(SELECT sal from emp WHERE ename=‘ALLEN’);

– 20.显示与’SCOTT’从事相同工作的员工的详细信息
SELECT * from emp WHERE job=(SELECT job from emp WHERE ename=‘SCOTT’);

– 21.显示销售部（‘SALESMAN’）员工的姓名
SELECT ename from emp WHERE job=‘SALESMAN’;

– 22.显示与30号部门’MARTIN’员工工资相同的员工的姓名和工资
SELECT ename,sal from emp WHERE sal=(SELECT sal from emp WHERE deptno=30 AND job=‘MARTIN’);

– 23.查询所有工资高于平均工资的销售人员（‘SALESMAN’）
SELECT * from emp WHERE job=‘SALESMAN’ and sal>(SELECT avg(sal) from emp);

– 24.显示所有职员的姓名及其所在部门的名称和工资
SELECT a.ename ,b.dname ,a.sal from emp a LEFT JOIN dept b on a.deptno = b.deptno;

– 25查询在研发部（‘RESEARCH’）工作员工的编号，姓名，工作部门，工作所在地
SELECT a.empno,a.ename,b.dname,b.loc from emp a LEFT JOIN dept b on a.deptno=b.deptno WHERE b.dname=‘RESEARCH’;

– 26.查询各个部门的名称和员工人数
SELECT b.dname,COUNT(*) from emp a RIGHT JOIN dept b on a.deptno=b.deptno GROUP BY a.deptno;

– 27.查询各个职位员工工资大于平均工资（平均工资包括所有员工）的人数和员工职位
SELECT job,IFNULL(COUNT(*),0) sum from emp WHERE sal>(SELECT avg(sal) from emp) GROUP BY job;

– 28.查询工资相同的员工的工资和姓名
SELECT a.sal, a.ename from emp a ,emp b WHERE a.sal=b.sal and a.ename!=b.ename;

– 29.查询工资最高的3名员工信息
SELECT * from emp ORDER BY sal DESC LIMIT 0,3;

– 30.按工资进行排名，排名从1开始，工资相同排名相同（如果两人并列第1则没有第2名，从第3名继续排）
SELECT a.ename,COUNT() from emp a, emp b WHERE a.sal<b.sal GROUP BY a.ename ORDER BY COUNT();
– 31.求入职日期相同的（年月日相同）的员工
SELECT a.hiredate,b.ename from emp a,emp b WHERE a.hiredate=b.hiredate and a.ename!=b.ename;

– 32.查询每个部门的最高工资
SELECT a.deptno,b.dname,IFNULL(max(a.sal),0) from emp a RIGHT JOIN dept b on a.deptno=b.deptno ORDER BY ;

– 33.查询每个部门，每种职位的最高工资
SELECT b.dname,a.job,max(a.sal) FROM emp a RIGHT JOIN dept b on a.deptno = b.deptno GROUP BY b.dname,a.job;

– 34.查询每个部门有多少人
SELECT b.dname,COUNT(ename) from emp a RIGHT JOIN dept b on a.deptno = b.deptno GROUP BY dname;

– 35.查询出king所在部门的部门号\部门名称\部门人数
select a.deptno,dname,count(*) from emp a ,dept b where a.deptno=b.deptno
and a.deptno=(
select deptno from emp where
ename =‘KING’
)
group by a.deptno

– 36.查询出king所在部门的工作年限最大的员工名字
select ename,hiredate from emp where deptno =
(
select deptno from emp where ename=‘KING’
) order by hiredate limit 0,1

– 37.查询出管理员工人数最多的人的名字和他管理的人的名字
select count(*) num ,ms_name from (
select a.ename , b.ename ms_name from emp a ,emp b where a.mgr=b.empno
) a group by ms_name order by num desc limit 0,1

– 38.查询出工资成本最高的部门的部门号和部门名称
select deptno ,dname from dept where deptno = (select deptno from emp group by deptno order by sum(sal) desc limit 0,1)

– 39.查询出工资不超过2500的人数最多的部门名称
select deptno ,dname from dept where deptno =(select deptno from emp where sal<=2500 group by deptno order by count(1) limit 0,1)

– 40.查询出没有下属员工的人的名字和他的职位
select ename ,job from emp where empno not in (select distinct mgr empno from emp where mgr is not null )
select ename ,job from emp a where not exists (select empno from emp b where a.empno=b.mgr)
–41.现在公司要给员工增加工龄工资,规则是:30*工作年限,请按以下格式显示下面结果
部门名称 员工姓名 原工资 增加额度 新工资
select ename,sal, sal+timestampdiff(year,hiredate,now())*30 newSal,
timestampdiff(year,hiredate,now())*30 addNUm from emp
– 42.针对dept和emp表,查询出下面格式的结果.(要求使用外联接,没有员工的部门名也要显示示,员工姓名如果是空值,要求用"不存在代替;如果工资是空值,要求用0代替")
部门名称 员工姓名 工资
select dname,ifnull(ename,‘not exists’)ename ,ifnull(sal,‘not exists’)sal from emp a right join dept b on a.deptno=b.deptno
– 43.查询出工资高于全体平均工资人数最多的部门编号,部门名称和员工姓名,工资.
select a.deptno,dname,ename,sal from emp a left join dept b on
a.deptno=b.deptno
where a.deptno=(
select deptno from emp where sal>(select avg(sal) from emp) group by deptno order by count(1) desc limit 0,1
)