MySQL练习——多表联查（99语法和92语法实现）

sql92语法
select {*|字段列名}
from 表名
where 查询条件;

sql99语法
select {*|字段列名}
from 表名
on 查询条件;
例如：

 -- sql92语法
 select ename,dname
 from emp,dept
 where emp.deptno=dept.deptno;
 
-- sql99语法
select ename,dname
from emp (inner)join dept
on emp.deptno=dept.deptno;

sql92语法和sql99语法的区别
1）逗号(“,”)换成(inner)join；
2）where换成on；
注：inner可以省略，写上inner可以增加代码的可读性。

sql99语法的优势
表连接和后面的where条件筛选，分离开来。
对于sql92语法来说，表连接用的是where，where筛选用的也是where，混淆在一起不清不楚。

练习：

-- 1. 显示所有员工的姓名，部门号和部门名称。
-- 92语法
SELECT
	e.emp_name, 
	e.dept_id, 
	d.dept_name
FROM
	emps e, 
	dept d
WHERE 
	e.dept_id = d.dept_id;
	
-- 99语法
SELECT
	e.emp_name, 
	e.dept_id, 
	d.dept_name
FROM
	emps e
LEFT JOIN
	dept d
ON 
	e.dept_id = d.dept_id;


-- 2. 查询90号部门员工的job_id和90号部门的location_id
-- 92语法
SELECT 
	e.job_id,
	d.location_id
FROM  
	emps e,
	dept d
WHERE
	e.dept_id = d.dept_id
AND 
	d.dept_id = 90;

-- 99语法
SELECT 
	e.job_id,
	d.location_id
FROM  
	emps e
JOIN
	dept d
ON
	e.dept_id = d.dept_id
AND 
	d.dept_id = 90;
	

-- 3. 选择所有有上级的员工的last_name , department_name , location_id , city
-- 92语法
SELECT 
	e.emp_name, 
	d.dept_name, 
	d.location_id, 
	l.city
FROM
	emps e,
	dept d,
	location l
WHERE
	e.dept_id = d.dept_id
AND
	d.location_id = l.location_id
AND
	e.mgr_id IS NOT NULL;

-- 99语法
SELECT 
	e.emp_name, 
	d.dept_name, 
	d.location_id,
	l.city
FROM
	emps e
JOIN
	dept d
ON e.dept_id = d.dept_id
JOIN
	location l
ON
	d.location_id = l.location_id
AND
	e.mgr_id IS NOT NULL;


-- 4. 选择在北京工作的员工的last_name , job_id , department_id , department_name 
-- 92语法
SELECT 
	e.emp_name, 
	e.job_id,
	d.dept_id,
	d.dept_name
FROM
	emps e,
	dept d,
	location l
WHERE
	e.dept_id = d.dept_id
AND
	d.location_id = l.location_id
AND
	l.city = "北京";

-- 99语法
SELECT 
	e.emp_name, 
	e.job_id,
	d.dept_id,	-- e.dept_id也可以
	d.dept_name
FROM
	emps e
JOIN
	dept d
JOIN
	location l
ON
	e.dept_id = d.dept_id
AND
	d.location_id = l.location_id
AND
	l.city = "北京";


-- 5. 选择所有员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
-- employees	Emp#	manager	Mgr#
-- kochhar	  101	  king	  100
-- 92语法
SELECT 
	e.emp_name AS 'employee', 
	e.emp_id AS 'Emp#',
	m.emp_name AS 'mgr_name',
	m.emp_id AS 'Mgr#'
FROM
	emps e,
	emps m
WHERE
	e.mgr_id = m.emp_id;

-- 99语法
SELECT 
	e.emp_name AS employee, 
	e.emp_id AS 'Emp#',
	m.emp_name mgr_name,
	m.emp_id 'Mgr#'
FROM
	emps e
LEFT JOIN
	emps m
ON
	e.mgr_id = m.emp_id;

-- 6. 查询各部门员工姓名和他们的同事姓名，结果类似于下面的格式
-- Department_id	Last_name 	colleague
-- 20	            fay	        hartstein
-- 92语法
SELECT 
	e.dept_id 'Department_id', 
	e.emp_name 'Emp_name', 
	c.emp_name 'Colleague'
FROM
	emps e,
	emps c
WHERE
	e.dept_id = c.dept_id
AND
	e.emp_id != c.emp_id;

-- 99语法
SELECT 
	e.dept_id 'Department_id', 
	e.emp_name 'Emp_name', 
	c.emp_name 'Colleague'
FROM 
	emps e
LEFT JOIN 
	emps c
ON
	e.dept_id = c.dept_id
AND
	e.emp_id != c.emp_id;

以下为数据库各表的创建与新增数据语句，可创建.sql文件粘贴以下数据，使用可视化工具Navicat进行导入

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50722
 Source Host           : localhost:3306
 Source Schema         : db10

 Target Server Type    : MySQL
 Target Server Version : 50722
 File Encoding         : 65001

 Date: 26/03/2023 16:09:39
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `dept_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `dept_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名字',
  `mgr_id` int(4) NULL DEFAULT NULL COMMENT '经理ID',
  `location_id` int(4) NULL DEFAULT NULL COMMENT '位置ID',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '行政部', 3, 1);
INSERT INTO `dept` VALUES (2, '技术部', 4, 1);
INSERT INTO `dept` VALUES (3, '销售部', 7, 2);
INSERT INTO `dept` VALUES (4, '后勤部', 8, 3);
INSERT INTO `dept` VALUES (5, '保洁部', NULL, 3);

-- ----------------------------
-- Table structure for emps
-- ----------------------------
DROP TABLE IF EXISTS `emps`;
CREATE TABLE `emps`  (
  `emp_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '员工工号',
  `emp_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `emp_sex` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工性别',
  `emp_age` int(3) NULL DEFAULT NULL COMMENT '员工年龄',
  `dept_id` int(4) NULL DEFAULT NULL COMMENT '部门',
  `hiredate` date NULL DEFAULT NULL COMMENT '入职时间',
  `job_id` int(4) NULL DEFAULT NULL COMMENT '职位ID',
  `salary` double(8, 2) NULL DEFAULT NULL COMMENT '薪资',
  `mgr_id` int(4) NULL DEFAULT NULL COMMENT '经理工号',
  `comm` double(7, 2) NULL DEFAULT NULL COMMENT '奖金',
  PRIMARY KEY (`emp_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emps
-- ----------------------------
INSERT INTO `emps` VALUES (1, 'Tom', '男', 28, 1, '2020-06-03', 1, 5600.00, NULL, 1500.00);
INSERT INTO `emps` VALUES (2, 'Jack', '男', 30, 1, '2010-05-06', 2, 8900.00, 1, NULL);
INSERT INTO `emps` VALUES (3, 'Bob', '男', 43, 2, '2010-05-06', 3, 12200.00, 1, 1500.00);
INSERT INTO `emps` VALUES (4, 'Rose', '女', 27, 2, '1998-02-05', 5, 8000.00, 2, NULL);
INSERT INTO `emps` VALUES (5, 'Ann', '男', 54, 3, '2010-05-06', 2, 6800.00, 3, 200.00);
INSERT INTO `emps` VALUES (6, 'Lucy', '女', 19, NULL, '1994-05-06', 2, 7300.00, 3, 30.00);
INSERT INTO `emps` VALUES (7, 'Cindy', '女', 29, 4, '2010-05-06', 1, 8000.00, 4, NULL);
INSERT INTO `emps` VALUES (8, 'Lily', '女', 32, 4, '2010-05-06', 3, 11000.00, 4, 100.00);

-- ----------------------------
-- Table structure for job
-- ----------------------------
DROP TABLE IF EXISTS `job`;
CREATE TABLE `job`  (
  `job_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '职位ID',
  `job_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职位名称',
  PRIMARY KEY (`job_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of job
-- ----------------------------
INSERT INTO `job` VALUES (1, '职员');
INSERT INTO `job` VALUES (2, '组长');
INSERT INTO `job` VALUES (3, '经理');
INSERT INTO `job` VALUES (4, '部长');
INSERT INTO `job` VALUES (5, '库长');

-- ----------------------------
-- Table structure for location
-- ----------------------------
DROP TABLE IF EXISTS `location`;
CREATE TABLE `location`  (
  `location_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '位置ID',
  `city` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市',
  PRIMARY KEY (`location_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of location
-- ----------------------------
INSERT INTO `location` VALUES (1, '北京');
INSERT INTO `location` VALUES (2, '上海');
INSERT INTO `location` VALUES (3, '大连');

-- ----------------------------
-- Table structure for sal_val
-- ----------------------------
DROP TABLE IF EXISTS `sal_val`;
CREATE TABLE `sal_val`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `level` int(4) NULL DEFAULT NULL,
  `min_val` int(8) NULL DEFAULT NULL,
  `max_val` int(8) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sal_val
-- ----------------------------
INSERT INTO `sal_val` VALUES (1, 1, 2000, 10000);
INSERT INTO `sal_val` VALUES (2, 2, 10001, 20000);
INSERT INTO `sal_val` VALUES (3, 3, 30001, 80000);

SET FOREIGN_KEY_CHECKS = 1;