[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`;
收藏
淘帖
踩
