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;