SQL 表查询之内连接
时间：2021-07-01 10:21:17
帮助过：5人阅读
# 查询每名员工的部门信息，用ON子句规定表连接条件
2 SELECT e.empno,e.ename,d.dname
FROM t_emp e
JOIN t_dept d
ON e.deptno
=d.deptno;
3 # 多种内连接写法
4 SELECT e.empno,e.ename,d.dname
FROM t_emp e
JOIN t_dept d
WHERE e.deptno
=d.deptno;
5 SELECT e.empno,e.ename,d.dname
FROM t_emp e , t_dept d
WHERE e.deptno
=d.deptno;
6 # 查询每个员工工号，姓名，部门名称，底薪，职位，工资等级
7 SELECT e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e,t_dept d,t_salgrade s
WHERE e.deptno
=d.deptno
AND e.sal
BETWEEN s.losal
AND s.hisal
ORDER BY s.grade;
8 # 查找与SCOTT部门相同的员工都有谁
9 # 子查询方式
10 SELECT ename
FROM t_emp
WHERE deptno
=(
SELECT deptno
FROM t_emp
WHERE ename
="SCOTT")
AND ename
!="SCOTT";
11 # 表连接方式
12 SELECT e2.ename
FROM t_emp e1
JOIN t_emp e2
ON e1.deptno
=e2.deptno
AND e2.ename
!="SCOTT"
WHERE e1.ename
="SCOTT";
13 # 查询底薪超过公司平均底薪的员工信息
14 # 错误方式
SELECT e2.empno,e2.ename,e2.sal
FROM t_emp e1
JOIN t_emp e2
ON e2.sal
>AVG(e1.sal)
15 # 将结果集作为一张表来与其他表做表连接
16 SELECT e.empno,e.ename,e.sal
FROM t_emp e
JOIN (
SELECT AVG(sal)
avg FROM t_emp) t
ON e.sal
>=t.
avg;
17 # 查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄(FLOOR强制舍位，CELL强制进位)
18 SELECT COUNT(
*),
AVG(e.sal),
MAX(e.sal),
MIN(e.sal),
FLOOR(
AVG(
DATEDIFF(NOW(),e.hiredate)
/365)) avghire
FROM t_emp e
JOIN t_dept d
ON d.deptno
=e.deptno
WHERE d.dname
="RESEARCH";
19 # 查询每种职业的最高工资，最低工资，平均工资，最高工资等级，最低工资等级
20 SELECT e.job,
COUNT(
*),
MAX(e.sal
+IFNULL(e.comm,
0)),
MIN(e.sal
+IFNULL(e.comm,
0)),
AVG(e.sal
+IFNULL(e.comm,
0)),
MAX(s.grade),
MIN(s.grade)
FROM t_emp e
JOIN t_salgrade s
ON e.sal
+IFNULL(e.comm,
0)
BETWEEN s.losal
AND s.hisal
GROUP BY e.job;
21 # 查询每个超过部门平均底薪的员工信息(将部门平均底薪和部门编号结果集作为一个表去连接)
22 SELECT e.deptno,e.ename,e.sal,t.
avg FROM t_emp e
JOIN (
SELECT AVG(sal)
avg,deptno
FROM t_emp
GROUP BY deptno) t
ON e.deptno
=t.deptno
AND e.sal
>=t.
avg;
SQL 表查询之内连接
标签：写法 超过 sql 强制 and style null 连接 sea
