表的创建
table DEPT
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80020
Source Host : localhost:3306
Source Schema : bjpowernode
Target Server Type : MySQL
Target Server Version : 80020
File Encoding : 65001
Date: 06/01/2021 12:13:18
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`DEPTNO` int(0) NOT NULL,
`DNAME` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`LOC` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- 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');
INSERT INTO `dept` VALUES (50, 'renshibu', 'shanghai');
SET FOREIGN_KEY_CHECKS = 1;
table EMP
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80020
Source Host : localhost:3306
Source Schema : bjpowernode
Target Server Type : MySQL
Target Server Version : 80020
File Encoding : 65001
Date: 06/01/2021 12:14:45
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`EMPNO` int(0) NOT NULL,
`ENAME` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`JOB` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`MGR` int(0) NULL DEFAULT NULL,
`HIREDATE` date NULL DEFAULT NULL,
`SAL` double(7, 2) NULL DEFAULT NULL,
`COMM` double(7, 2) NULL DEFAULT NULL,
`DEPTNO` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`EMPNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- 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);
SET FOREIGN_KEY_CHECKS = 1;
table salgrade
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80020
Source Host : localhost:3306
Source Schema : bjpowernode
Target Server Type : MySQL
Target Server Version : 80020
File Encoding : 65001
Date: 06/01/2021 12:14:51
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`GRADE` int(0) NULL DEFAULT NULL,
`LOSAL` int(0) NULL DEFAULT NULL,
`HISAL` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- 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);
SET FOREIGN_KEY_CHECKS = 1;
-- SELECT MAX(SAL) MAXSAL FROM emp GROUP BY DEPTNO;
SELECT e.ENAME,e.SAL,e.DEPTNO FROM emp e INNER JOIN (SELECT DEPTNO,MAX(SAL) MAXSAL FROM emp GROUP BY DEPTNO) tt on e.SAL = tt.MAXSAL and e.DEPTNO = tt.DEPTNO;
-- SELECT DEPTNO,AVG(SAL) avgSal FROM emp GROUP BY DEPTNO;
SELECT * FROM emp INNER JOIN (SELECT DEPTNO,AVG(SAL) avgSal FROM emp GROUP BY DEPTNO) tt on emp.SAL > tt.avgSal;
SELECT e.DEPTNO,avg(sg.GRADE) FROM emp e INNER JOIN salgrade sg on e.sal >= sg.LOSAL AND e.sal <= sg.HISAL GROUP BY e.DEPTNO;
SELECT ENAME,JOB,SAL FROM emp ORDER BY SAL DESC LIMIT 0,1;
SELECT DEPTNO ,AVG(SAL) avgSal FROM emp GROUP BY DEPTNO ORDER BY avgSal DESC LIMIT 1
SELECT tt.DEPTNO,salgrade.GRADE FROM(SELECT DEPTNO ,AVG(SAL) avgSal FROM emp GROUP BY DEPTNO ORDER BY avgSal DESC LIMIT 1) tt INNER JOIN salgrade on tt.avgSal >= salgrade.LOSAL and tt.avgSal <= salgrade.HISAL
SELECT dept.DNAME ,tt.DEPTNO,tt.avgSAl FROM (SELECT DEPTNO,AVG(SAL) avgSal FROM emp GROUP BY DEPTNO ) tt INNER JOIN dept WHERE tt.DEPTNO = dept.DEPTNO ORDER BY tt.avgSal LIMIT 0,1;
SELECT ENAME FROM emp WHERE SAL > (SELECT MAX(SAL) FROM emp WHERE EMPNO NOT in (SELECT MGR FROM emp WHERE mgr is not NULL GROUP BY MGR))
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 5
SELECT * FROM emp ORDER BY SAL DESC LIMIT 5,5;
SELECT * FROM emp ORDER BY HIREDATE DESC LIMIT 0,5;
SELECT tt.GRADE,COUNT(tt.DEPTNO) as count FROM (SELECT emp.DEPTNO , salgrade.GRADE FROM emp INNER JOIN salgrade on emp.SAL > salgrade.LOSAL and emp.SAL < salgrade.HISAL) tt GROUP BY tt.GRADE
SELECT e1.ENAME,e2.ENAME FROM emp e1 LEFT JOIN emp e2 on e1.MGR = e2.EMPNO;
SELECT tt.*,dept.DNAME FROM (SELECT e1.ENAME,e2.EMPNO MGRNO,e2.ENAME MGRNAME ,e2.DEPTNO FROM emp e1 LEFT JOIN emp e2 on e1.MGR = e2.EMPNO WHERE e1.HIREDATE < e2.HIREDATE) tt INNER JOIN dept on tt.DEPTNO = dept.DEPTNO
SELECT dept.DNAME,emp.* FROM emp RIGHT JOIN dept on dept.DEPTNO = emp.DEPTNO
总结 :
JOIN 按照功能大致分为如下三类:
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
SELECT dept.DEPTNO , dept.DNAME, tt.count FROM (SELECT DEPTNO,count(DEPTNO) count FROM emp GROUP BY DEPTNO ) tt,dept WHERE dept.DEPTNO = tt.DEPTNO AND tt.count >= 5
SELECT * FROM emp WHERE sal > ( SELECT sal FROM emp WHERE ENAME = "SMITH" )
SELECT tt.ENAME,dept.DNAME, dd.count FROM ( SELECT * FRO![M emp WHERE JOB = "CLERK" ) tt INNER JOIN dept on tt)
DEPTNO = dept.DEPTNO INNER JOIN (SELECT DEPTNO,COUNT(DEPTNO) count FROM emp GROUP BY DEPTNO) dd on dd.DEPTNO = tt.DEPTNO;
SELECT JOB ,COUNT(EMPNO) FROM emp GROUP BY JOB HAVING min(sal) > 1500
20. # 列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.思路
SELECT ENAME FROM emp WHERE DEPTNO = (SELECT DEPTNO FROM dept WHERE DNAME = "SALES" )
SELECT tt.ENAME ,tt.JOB ,dept.DNAME,tt.MGR,dd.grade FROM ( SELECT * FROM emp WHERE sal > ( SELECT avg(sal) FROM emp ) ) tt INNER JOIN dept on tt.DEPTNO = dept.DEPTNO INNER JOIN salgrade dd on tt.SAL > dd.LOSAL AND tt.SAL < dd.HISAL
SELECT emp.ENAME,dept.DNAME FROM emp,dept WHERE JOB = ( SELECT JOB FROM emp WHERE ENAME = "SCOTT" ) AND dept.DEPTNO = emp.DEPTNO;
SELECT * FROM emp WHERE sal in (SELECT SAL FROM emp WHERE DEPTNO = 30 ) AND DEPTNO != 30;
SELECT * FROM emp WHERE SAL > ( SELECT max(SAL) maxsal FROM emp WHERE DEPTNO = 30 ) AND DEPTNO != 30
SELECT DEPTNO , COUNT(EMPNO) count,AVG(sal) avgsal ,avg( (to_days( now() ) - to_days
( hiredate ) ) / 365) FROM emp GROUP BY DEPTNO ;
SELECT emp.ENAME,dept.DNAME,emp.SAL FROM emp INNER JOIN dept on emp.DEPTNO = dept.DEPTNO;
SELECT * FROM dept INNER JOIN (SELECT DEPTNO,COUNT(EMPNO) FROM emp GROUP BY DEPTNO ) tt on dept.DEPTNO = tt.DEPTNO ;
SELECT emp.ENAME,tt.* FROM ( SELECT JOB,min(sal) minsal FROM emp GROUP BY JOB ) tt ,emp WHERE tt.minsal = emp.SAL;
SELECT DEPTNO, min(SAL) FROM emp WHERE JOB = "MANAGER" GROUP BY DEPTNO;
SELECT ENAME,(sal + IFNULL(COMM,0))*12 FROM emp ORDER BY sal DESC;
SELECT e1.ENAME empname,e2.ENAME mgrname FROM emp e1 INNER JOIN emp e2 on e1.MGR = e2.EMPNO WHERE e2.SAL > 3000