A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

© 徐壹 初级黑马   /  2019-1-3 13:37  /  1049 人查看  /  0 人回复  /   0 人收藏 转载请遵从CC协议 禁止商业使用本文

[SQL] 纯文本查看 复制代码
/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50549
Source Host           : localhost:3306
Source Database       : mydb

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

Date: 2017-11-13 15:22:12
*/

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for `emp`
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `ENAME` VARCHAR(20) DEFAULT NULL,
  `JOB` VARCHAR(20) DEFAULT NULL,
  `MGR` BIGINT(20) DEFAULT NULL,
  `HIREDATE` DATE DEFAULT NULL,
  `SAL` DOUBLE(7,2) DEFAULT NULL,
  `COMM` DOUBLE(7,2) DEFAULT NULL,
  `DEPTNO` BIGINT(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=INNODB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8;

-- ----------------------------
-- 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-04-19', '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-05-23', '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');

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50549
Source Host           : localhost:3306
Source Database       : mydb

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

Date: 2017-11-13 15:22:02
*/
-- 禁用外键约束
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for `dept`
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `DNAME` VARCHAR(20) DEFAULT NULL,
  `LOC` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=INNODB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

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

-- 01.查询每个雇员的编号、姓名、职位。

SELECT EMPNO,ENAME,JOB FROM emp;

-- 02.查询出所有的职位,使用 DISTINCT 消除掉显示的重复行记录。

SELECT DISTINCT job FROM emp;

-- 03.计算每个雇员的编号、姓名、基本年薪。年薪 = (工资 + 奖金) * 12,(使用 IFNULL 函数判断 NULL)

SELECT EMPNO,ENAME,(SAL + IFNULL(COMM,0)) * 12 FROM emp;

-- 05.查询基本工资高于 2000 的全部雇员信息。

SELECT ENAME,SAL FROM emp WHERE SAL > 2000;
	
-- 07.查询出所有不是 CLERK 的详细信息。

SELECT * FROM emp WHERE JOB != 'CLERK';

-- 08.查询出所有销售人员( SALESMAN )的基本信息,并且要求销售人员的工资高于 1300。

SELECT * FROM emp WHERE JOB = 'SALESMAN' AND SAL > 1300;

-- 09.查询出工资范围在 1500 ~ 3000 之间的全部雇员信息(包含 1500 和 3000 )。

SELECT * FROM emp WHERE SAL BETWEEN 1500 AND 3000;

-- 10.查询出所有经理或者是销售人员的信息,并且要求这些人的基本工资高于 1500。

SELECT * FROM emp WHERE (JOB = 'SALESMAN' OR JOB = 'MANAGER') AND SAL > 1500;

-- 13.查询所有领取奖金高于 100 的雇员信息。

SELECT * FROM emp WHERE COMM > 100;

-- 14.查询出雇员编号是 7369、7566、9999 的雇员信息。

SELECT * FROM emp WHERE EMPNO = 7369 OR EMPNO = 7566 OR EMPNO = 9999;

-- 21.查询部门 30 中的所有员工。

SELECT * FROM emp WHERE DEPTNO = 30;

-- 22.查询出所有办事员( CLERK )的姓名,编号和部门编号。

SELECT ENAME,EMPNO,DEPTNO FROM emp WHERE JOB = 'CLERK';

-- 23.查询出奖金高于薪金的员工。

SELECT * FROM emp WHERE COMM > SAL;

-- 24.查询出奖金高于薪金的 60% 的员工。

SELECT * FROM emp WHERE COMM > SAL * 0.6;

-- 25.查询出部门 10 中所有经理( MANAGER )和部门 20 中所有办事员( CLERK )的详细资料。

SELECT
	*
FROM
	emp,dept
WHERE
	((emp.`DEPTNO` = 10 AND emp.`JOB` = 'MANAGER') OR
	(emp.`DEPTNO` = 20 AND emp.`JOB` = 'CLERK'))
	AND emp.`DEPTNO` = dept.`DEPTNO`;

-- 26.查询出部门 10 中所有经理,部门 20 中所有办事员,既不是经理又不是办事员但其薪金大于或等于 2000 的所有员工的信息。

SELECT
	*
FROM
	emp,dept
WHERE 
	((emp.`JOB` = 'MANAGER' AND emp.`DEPTNO` = 10) OR 
	(emp.`JOB` = 'CLERK' AND emp.`DEPTNO` = 20) OR 
	(emp.`SAL` >= 2000 AND emp.`JOB`NOT IN('CLERK') AND
	emp.`JOB` NOT IN('MANAGER'))) AND
	emp.`DEPTNO` = dept.`DEPTNO`;

-- 27.查询出收取了奖金的员工从事的工作。

SELECT DISTINCT JOB FROM emp WHERE COMM != 0;

-- 28.查询出不收取奖金或收取的奖金低于 100 的员工。

SELECT * FROM emp WHERE IFNULL(COMM,0) < 100;

-- 29.查询出不带有 `db1`「R」的员工的姓名。

SELECT ENAME FROM emp WHERE ENAME NOT LIKE '%R%';

-- 30.查询出每个雇员的姓名、职位、领导姓名。

SELECT t1.`ENAME`,t1.`JOB`,t2.`ENAME` FROM emp t1,emp t2 WHERE t1.`MGR` = t2.`EMPNO`;

-- 31.查询出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列。

SELECT
	t1.`EMPNO`,t1.`ENAME`,t1.`MGR`,t2.`ENAME`
FROM
	emp t1,emp t2
WHERE
	t1.`MGR`=t2.`EMPNO`
ORDER BY
	t2.`SAL` DESC;

-- 32.查询出在销售部( SALES )工作的员工姓名、基本工资、雇佣日期、部门名称。(不知道销售部编号)。

SELECT
	t1.`ENAME`,t1.`SAL`,t1.`HIREDATE`,t2.`DNAME`
FROM
	emp t1,dept t2
WHERE
	t1.`DEPTNO` = t2.`DEPTNO` AND t2.`DNAME` = 'SALES';

-- 33.查询出所有员工的姓名、部门名称和工资。

SELECT t1.`ENAME`,t2.`DNAME`,t1.`SAL` FROM emp t1,dept t2 WHERE t1.`DEPTNO`=t2.`DEPTNO`;

-- 34.查询出所有员工的年工资,所在部门名称,按年薪从低到高排序。

SELECT
	t1.`ENAME`,t1.`SAL` * 12 yearsal,t2.`DNAME`
FROM
	emp t1,dept t2
WHERE
	t1.`DEPTNO` = t2.`DEPTNO`
ORDER BY
	yearsal ASC;

-- 35.查询出某个员工的上级主管及所在部门名称,并要求出这些主管中的薪水超过 3000。

SELECT DISTINCT
	t2.`ENAME`,t3.`DNAME`
FROM
	emp t1,emp t2,dept t3
WHERE
	t1.`MGR` = t2.`EMPNO` AND t2.`DEPTNO` = t3.`DEPTNO` AND t2.`SAL` > 3000;

-- 36.查询出公司的最高和最低工资。

SELECT MAX(SAL),MIN(SAL) FROM emp;

-- 37.查询出每个部门的人数、平均工资,只显示部门编号。

SELECT DEPTNO,COUNT(DEPTNO),AVG(SAL) FROM emp GROUP BY DEPTNO;

-- 38.查询出每种职位的最高和最低工资。

SELECT JOB,MAX(SAL),MIN(SAL) FROM emp GROUP BY JOB;

-- 39.查询平均工资高于 2000 的职位信息,以及从事此职位的雇员人数、平均工资。

SELECT job,COUNT(job),AVG(sal) FROM emp GROUP BY job HAVING AVG(sal) > 2000;

-- 40.查询员工编号大于其领导编号的每个员工的姓名、职位、领导名称。

SELECT
	t1.`ENAME`,t1.`JOB`,t2.`ENAME`
FROM
	emp t1,emp t2
WHERE
	t1.`EMPNO` > t1.`MGR` AND t1.`MGR` = t2.`EMPNO`;

-- 41.查询出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资。

SELECT
	t1.`DEPTNO`,t2.`DNAME`,AVG(sal),MIN(sal),MAX(sal)
FROM
	emp t1,dept t2
WHERE
	t1.`DEPTNO` = t2.`DEPTNO`
GROUP BY
	t2.`DNAME`;

-- 42.查询出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门。

SELECT
	t2.`DNAME`,COUNT(ename),AVG(sal)
FROM
	emp t1
RIGHT JOIN
	dept t2
ON
	t1.`DEPTNO` = t2.`DEPTNO`
GROUP BY
	t2.`DNAME`;

-- 43.查询工资比 smith 更高的全部员工信息。

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

-- 44.查询工资和职位和 smith 相同的所有员工信息。  

SELECT
	*
FROM
	emp
WHERE
	sal = (SELECT sal FROM emp WHERE ename = 'SMITH') AND 
	job = (SELECT job FROM emp WHERE ename = 'SMITH');

-- 45.查询各部门的部门编号,部门名词,部门地址,员工人数和平均工资。

SELECT
	t2.`DEPTNO`,t2.`DNAME`,t2.`LOC`,COUNT(sal),IFNULL(AVG(sal),0)
FROM
	emp t1
RIGHT JOIN
	dept t2
ON
	t1.`DEPTNO` = t2.`DEPTNO`
GROUP BY
	t2.`DNAME`;

0 个回复

您需要登录后才可以回帖 登录 | 加入黑马