AI 助理
备案控制台
开发者社区 数据库 文章 正文

《卸甲笔记》-多表查询之一

2016-06-22 1455
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

1查询emp表中数据量 14条数据
Oracle

SQL> select count(*) from emp;

  COUNT(*)
----------
    14

PPAS

scott=# select count(*) from emp;
 count 
-------
    14
(1 row)

2查询dept表中的数据库 4条数据
Oracle

SQL> select count(*) from dept;

  COUNT(*)
----------
     4

PPAS

scott=# select count(*) from dept;
 count 
-------
     4
(1 row)

3查询所有的雇员和部门的全部详细信息
Oracle

SQL> select * from emp,dept;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO     DEPTNO DNAME         LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      10 ACCOUNTING      NEW YORK
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      10 ACCOUNTING      NEW YORK
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      10 ACCOUNTING      NEW YORK
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      10 ACCOUNTING      NEW YORK
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      10 ACCOUNTING      NEW YORK
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      10 ACCOUNTING      NEW YORK
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      10 ACCOUNTING      NEW YORK
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      10 ACCOUNTING      NEW YORK
      7839 KING       PRESIDENT         17-NOV-81           5000               10      10 ACCOUNTING      NEW YORK
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      10 ACCOUNTING      NEW YORK
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      10 ACCOUNTING      NEW YORK
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      10 ACCOUNTING      NEW YORK
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      10 ACCOUNTING      NEW YORK
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      10 ACCOUNTING      NEW YORK
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      20 RESEARCH         DALLAS
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      20 RESEARCH         DALLAS
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      20 RESEARCH         DALLAS
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      20 RESEARCH         DALLAS
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      20 RESEARCH         DALLAS
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      20 RESEARCH         DALLAS
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      20 RESEARCH         DALLAS
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      20 RESEARCH         DALLAS
      7839 KING       PRESIDENT         17-NOV-81           5000               10      20 RESEARCH         DALLAS
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      20 RESEARCH         DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      20 RESEARCH         DALLAS
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      20 RESEARCH         DALLAS
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      20 RESEARCH         DALLAS
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      20 RESEARCH         DALLAS
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      30 SALES         CHICAGO
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      30 SALES         CHICAGO
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      30 SALES         CHICAGO
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      30 SALES         CHICAGO
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      30 SALES         CHICAGO
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      30 SALES         CHICAGO
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      30 SALES         CHICAGO
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      30 SALES         CHICAGO
      7839 KING       PRESIDENT         17-NOV-81           5000               10      30 SALES         CHICAGO
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      30 SALES         CHICAGO
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      30 SALES         CHICAGO
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      30 SALES         CHICAGO
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      30 SALES         CHICAGO
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      30 SALES         CHICAGO
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      40 OPERATIONS      BOSTON
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      40 OPERATIONS      BOSTON
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      40 OPERATIONS      BOSTON
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      40 OPERATIONS      BOSTON
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      40 OPERATIONS      BOSTON
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      40 OPERATIONS      BOSTON
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      40 OPERATIONS      BOSTON
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      40 OPERATIONS      BOSTON
      7839 KING       PRESIDENT         17-NOV-81           5000               10      40 OPERATIONS      BOSTON
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      40 OPERATIONS      BOSTON
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      40 OPERATIONS      BOSTON
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      40 OPERATIONS      BOSTON
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      40 OPERATIONS      BOSTON
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      40 OPERATIONS      BOSTON

56 rows selected.

PPAS

scott=# select * from emp,dept;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno | deptno |   dname    |   loc    
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     10 | ACCOUNTING | NEW YORK
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     30 | SALES      | CHICAGO
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     40 | OPERATIONS | BOSTON
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     10 | ACCOUNTING | NEW YORK
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     20 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     40 | OPERATIONS | BOSTON
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30 |     10 | ACCOUNTING | NEW YORK
  30 |     20 | RESEARCH   | DALLAS| 22-FEB-81 00:00:00 | 1250.00 |  500.00 |   --More--
  30 |     30 | SALES      | CHICAGO 22-FEB-81 00:00:00 | 1250.00 |  500.00 |   --More--
  30 |     40 | OPERATIONS | BOSTON| 22-FEB-81 00:00:00 | 1250.00 |  500.00 |   --More--
  20 |     10 | ACCOUNTING | NEW YORK02-APR-81 00:00:00 | 2975.00 |         |   --More--
  20 |     20 | RESEARCH   | DALLAS| 02-APR-81 00:00:00 | 2975.00 |         |   --More--
  20 |     30 | SALES      | CHICAGO 02-APR-81 00:00:00 | 2975.00 |         |   --More--
  20 |     40 | OPERATIONS | BOSTON| 02-APR-81 00:00:00 | 2975.00 |         |   --More--
  30 |     10 | ACCOUNTING | NEW YORK28-SEP-81 00:00:00 | 1250.00 | 1400.00 |   --More--
  30 |     20 | RESEARCH   | DALLAS| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |   --More--
  30 |     30 | SALES      | CHICAGO 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |   --More--
  30 |     40 | OPERATIONS | BOSTON| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |   --More--
  30 |     10 | ACCOUNTING | NEW YORK01-MAY-81 00:00:00 | 2850.00 |         |   --More--
  30 |     20 | RESEARCH   | DALLAS| 01-MAY-81 00:00:00 | 2850.00 |         |   --More--
  30 |     30 | SALES      | CHICAGO 01-MAY-81 00:00:00 | 2850.00 |         |   --More--
  30 |     40 | OPERATIONS | BOSTON| 01-MAY-81 00:00:00 | 2850.00 |         |   --More--
  10 |     10 | ACCOUNTING | NEW YORK09-JUN-81 00:00:00 | 2450.00 |         |   --More--
  10 |     20 | RESEARCH   | DALLAS| 09-JUN-81 00:00:00 | 2450.00 |         |   --More--
  10 |     30 | SALES      | CHICAGO 09-JUN-81 00:00:00 | 2450.00 |         |   --More--
  10 |     40 | OPERATIONS | BOSTON| 09-JUN-81 00:00:00 | 2450.00 |         |   --More--
  20 |     10 | ACCOUNTING | NEW YORK19-APR-87 00:00:00 | 3000.00 |         |   --More--
  20 |     20 | RESEARCH   | DALLAS| 19-APR-87 00:00:00 | 3000.00 |         |   --More--
  20 |     30 | SALES      | CHICAGO 19-APR-87 00:00:00 | 3000.00 |         |   --More--
  20 |     40 | OPERATIONS | BOSTON| 19-APR-87 00:00:00 | 3000.00 |         |   --More--
  10 |     10 | ACCOUNTING | NEW YORK17-NOV-81 00:00:00 | 5000.00 |         |   --More--
  10 |     20 | RESEARCH   | DALLAS| 17-NOV-81 00:00:00 | 5000.00 |         |   --More--
  10 |     30 | SALES      | CHICAGO 17-NOV-81 00:00:00 | 5000.00 |         |   --More--
  10 |     40 | OPERATIONS | BOSTON| 17-NOV-81 00:00:00 | 5000.00 |         |   --More--
  30 |     10 | ACCOUNTING | NEW YORK08-SEP-81 00:00:00 | 1500.00 |    0.00 |   --More--
  30 |     20 | RESEARCH   | DALLAS| 08-SEP-81 00:00:00 | 1500.00 |    0.00 |   --More--
  30 |     30 | SALES      | CHICAGO 08-SEP-81 00:00:00 | 1500.00 |    0.00 |   --More--
  30 |     40 | OPERATIONS | BOSTON| 08-SEP-81 00:00:00 | 1500.00 |    0.00 |   --More--
  20 |     10 | ACCOUNTING | NEW YORK23-MAY-87 00:00:00 | 1100.00 |         |   --More--
  20 |     20 | RESEARCH   | DALLAS| 23-MAY-87 00:00:00 | 1100.00 |         |   --More--
  20 |     30 | SALES      | CHICAGO 23-MAY-87 00:00:00 | 1100.00 |         |   --More--
  20 |     40 | OPERATIONS | BOSTON| 23-MAY-87 00:00:00 | 1100.00 |         |   --More--
  30 |     10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 |  950.00 |         |   --More--
  30 |     20 | RESEARCH   | DALLAS| 03-DEC-81 00:00:00 |  950.00 |         |   --More--
  30 |     30 | SALES      | CHICAGO 03-DEC-81 00:00:00 |  950.00 |         |   --More--
  30 |     40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 |  950.00 |         |   --More--
  20 |     10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 | 3000.00 |         |   --More--
  20 |     20 | RESEARCH   | DALLAS| 03-DEC-81 00:00:00 | 3000.00 |         |   --More--
  20 |     30 | SALES      | CHICAGO 03-DEC-81 00:00:00 | 3000.00 |         |   --More--
  20 |     40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 | 3000.00 |         |   --More--
  10 |     10 | ACCOUNTING | NEW YORK23-JAN-82 00:00:00 | 1300.00 |         |   --More--
  10 |     20 | RESEARCH   | DALLAS| 23-JAN-82 00:00:00 | 1300.00 |         |   --More--
  10 |     30 | SALES      | CHICAGO 23-JAN-82 00:00:00 | 1300.00 |         |   --More--
  10 |     40 | OPERATIONS | BOSTON| 23-JAN-82 00:00:00 | 1300.00 |         |   --More--
(56 rows)

4消除查询结果中的笛卡尔积
Oracle

SQL>  select * 
  2  from emp,dept
  3  where emp.deptno=dept.deptno;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO     DEPTNO DNAME         LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      10 ACCOUNTING      NEW YORK
      7839 KING       PRESIDENT         17-NOV-81           5000               10      10 ACCOUNTING      NEW YORK
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      10 ACCOUNTING      NEW YORK
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      20 RESEARCH         DALLAS
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      20 RESEARCH         DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      20 RESEARCH         DALLAS
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      20 RESEARCH         DALLAS
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      20 RESEARCH         DALLAS
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      30 SALES         CHICAGO
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      30 SALES         CHICAGO
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      30 SALES         CHICAGO
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      30 SALES         CHICAGO
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      30 SALES         CHICAGO
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      30 SALES         CHICAGO

14 rows selected.

PPAS

scott=# select * 
scott-# from emp,dept
scott-# where emp.deptno=dept.deptno;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno | deptno |   dname    |   loc    
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30 |     30 | SALES      | CHICAGO
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30 |     30 | SALES      | CHICAGO
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
(14 rows)

5查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息
Oracle

SQL> select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc
  2  from emp,dept
  3  where emp.deptno=dept.deptno;

     EMPNO ENAME      JOB        SAL DNAME        LOC
---------- ---------- ---------- ---------- --------------- ----------
      7782 CLARK      MANAGER           2450 ACCOUNTING        NEW YORK
      7839 KING       PRESIDENT        5000 ACCOUNTING        NEW YORK
      7934 MILLER     CLERK           1300 ACCOUNTING        NEW YORK
      7566 JONES      MANAGER           2975 RESEARCH        DALLAS
      7902 FORD       ANALYST           3000 RESEARCH        DALLAS
      7876 ADAMS      CLERK           1100 RESEARCH        DALLAS
      7369 SMITH      CLERK        800 RESEARCH        DALLAS
      7788 SCOTT      ANALYST           3000 RESEARCH        DALLAS
      7521 WARD       SALESMAN           1250 SALES        CHICAGO
      7844 TURNER     SALESMAN           1500 SALES        CHICAGO
      7499 ALLEN      SALESMAN           1600 SALES        CHICAGO
      7900 JAMES      CLERK        950 SALES        CHICAGO
      7698 BLAKE      MANAGER           2850 SALES        CHICAGO
      7654 MARTIN     SALESMAN           1250 SALES        CHICAGO

14 rows selected.

PPAS

scott=# select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc
scott-# from emp,dept
scott-# where emp.deptno=dept.deptno;
 empno | ename  |    job    |   sal   |   dname    |   loc    
-------+--------+-----------+---------+------------+----------
  7369 | SMITH  | CLERK     |  800.00 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 1600.00 | SALES      | CHICAGO
  7521 | WARD   | SALESMAN  | 1250.00 | SALES      | CHICAGO
  7566 | JONES  | MANAGER   | 2975.00 | RESEARCH   | DALLAS
  7654 | MARTIN | SALESMAN  | 1250.00 | SALES      | CHICAGO
  7698 | BLAKE  | MANAGER   | 2850.00 | SALES      | CHICAGO
  7782 | CLARK  | MANAGER   | 2450.00 | ACCOUNTING | NEW YORK
  7788 | SCOTT  | ANALYST   | 3000.00 | RESEARCH   | DALLAS
  7839 | KING   | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK
  7844 | TURNER | SALESMAN  | 1500.00 | SALES      | CHICAGO
  7876 | ADAMS  | CLERK     | 1100.00 | RESEARCH   | DALLAS
  7900 | JAMES  | CLERK     |  950.00 | SALES      | CHICAGO
  7902 | FORD   | ANALYST   | 3000.00 | RESEARCH   | DALLAS
  7934 | MILLER | CLERK     | 1300.00 | ACCOUNTING | NEW YORK
(14 rows)

6通过别名查询雇员的编号、姓名、职位、基本工资、部门名称、部门位置
Oracle

SQL> select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
  2  from emp e,dept d
  3  where e.deptno=d.deptno;

     EMPNO ENAME      JOB        SAL DNAME        LOC
---------- ---------- ---------- ---------- --------------- ----------
      7782 CLARK      MANAGER           2450 ACCOUNTING        NEW YORK
      7839 KING       PRESIDENT        5000 ACCOUNTING        NEW YORK
      7934 MILLER     CLERK           1300 ACCOUNTING        NEW YORK
      7566 JONES      MANAGER           2975 RESEARCH        DALLAS
      7902 FORD       ANALYST           3000 RESEARCH        DALLAS
      7876 ADAMS      CLERK           1100 RESEARCH        DALLAS
      7369 SMITH      CLERK        800 RESEARCH        DALLAS
      7788 SCOTT      ANALYST           3000 RESEARCH        DALLAS
      7521 WARD       SALESMAN           1250 SALES        CHICAGO
      7844 TURNER     SALESMAN           1500 SALES        CHICAGO
      7499 ALLEN      SALESMAN           1600 SALES        CHICAGO
      7900 JAMES      CLERK        950 SALES        CHICAGO
      7698 BLAKE      MANAGER           2850 SALES        CHICAGO
      7654 MARTIN     SALESMAN           1250 SALES        CHICAGO

14 rows selected.

PPAS

scott=# select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
scott-# from emp e,dept d
scott-# where e.deptno=d.deptno;
 empno | ename  |    job    |   sal   |   dname    |   loc    
-------+--------+-----------+---------+------------+----------
  7369 | SMITH  | CLERK     |  800.00 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 1600.00 | SALES      | CHICAGO
  7521 | WARD   | SALESMAN  | 1250.00 | SALES      | CHICAGO
  7566 | JONES  | MANAGER   | 2975.00 | RESEARCH   | DALLAS
  7654 | MARTIN | SALESMAN  | 1250.00 | SALES      | CHICAGO
  7698 | BLAKE  | MANAGER   | 2850.00 | SALES      | CHICAGO
  7782 | CLARK  | MANAGER   | 2450.00 | ACCOUNTING | NEW YORK
  7788 | SCOTT  | ANALYST   | 3000.00 | RESEARCH   | DALLAS
  7839 | KING   | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK
  7844 | TURNER | SALESMAN  | 1500.00 | SALES      | CHICAGO
  7876 | ADAMS  | CLERK     | 1100.00 | RESEARCH   | DALLAS
  7900 | JAMES  | CLERK     |  950.00 | SALES      | CHICAGO
  7902 | FORD   | ANALYST   | 3000.00 | RESEARCH   | DALLAS
  7934 | MILLER | CLERK     | 1300.00 | ACCOUNTING | NEW YORK
(14 rows)

7查询出每个雇员的编号、姓名、雇用日期、基本工资、工资等级
Oracle

SQL> select e.empno,e.ename,e.hiredate,e.sal,s.grade
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal;

     EMPNO ENAME      HIREDATE          SAL       GRADE
---------- ---------- ------------ ---------- ----------
      7369 SMITH      17-DEC-80       800           1
      7900 JAMES      03-DEC-81       950           1
      7876 ADAMS      23-MAY-87      1100           1
      7521 WARD       22-FEB-81      1250           2
      7654 MARTIN     28-SEP-81      1250           2
      7934 MILLER     23-JAN-82      1300           2
      7844 TURNER     08-SEP-81      1500           3
      7499 ALLEN      20-FEB-81      1600           3
      7782 CLARK      09-JUN-81      2450           4
      7698 BLAKE      01-MAY-81      2850           4
      7566 JONES      02-APR-81      2975           4
      7788 SCOTT      19-APR-87      3000           4
      7902 FORD       03-DEC-81      3000           4
      7839 KING       17-NOV-81      5000           5

14 rows selected.

PPAS

scott=# select e.empno,e.ename,e.hiredate,e.sal,s.grade
scott-# from emp e,salgrade s
scott-# where e.sal between s.losal and s.hisal;
 empno | ename  |      hiredate      |   sal   | grade 
-------+--------+--------------------+---------+-------
  7369 | SMITH  | 17-DEC-80 00:00:00 |  800.00 |     1
  7876 | ADAMS  | 23-MAY-87 00:00:00 | 1100.00 |     1
  7900 | JAMES  | 03-DEC-81 00:00:00 |  950.00 |     1
  7521 | WARD   | 22-FEB-81 00:00:00 | 1250.00 |     2
  7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 |     2
  7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 |     2
  7499 | ALLEN  | 20-FEB-81 00:00:00 | 1600.00 |     3
  7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 |     3
  7566 | JONES  | 02-APR-81 00:00:00 | 2975.00 |     4
  7698 | BLAKE  | 01-MAY-81 00:00:00 | 2850.00 |     4
  7782 | CLARK  | 09-JUN-81 00:00:00 | 2450.00 |     4
  7788 | SCOTT  | 19-APR-87 00:00:00 | 3000.00 |     4
  7902 | FORD   | 03-DEC-81 00:00:00 | 3000.00 |     4
  7839 | KING   | 17-NOV-81 00:00:00 | 5000.00 |     5
(14 rows)

8使用DECODE()函数完成查询出每个雇员的编号、姓名、雇用日期、基本工资、工资等级
Oracle

SQL> select e.empno,e.ename,e.hiredate,e.sal,
  2  DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
  3  from emp e,salgrade s
  4  where e.sal between s.losal and s.hisal;

     EMPNO ENAME      HIREDATE          SAL GRADE
---------- ---------- ------------ ---------- --------------------
      7369 SMITH      17-DEC-80       800 E等工资
      7900 JAMES      03-DEC-81       950 E等工资
      7876 ADAMS      23-MAY-87      1100 E等工资
      7521 WARD       22-FEB-81      1250 D等工资
      7654 MARTIN     28-SEP-81      1250 D等工资
      7934 MILLER     23-JAN-82      1300 D等工资
      7844 TURNER     08-SEP-81      1500 C等工资
      7499 ALLEN      20-FEB-81      1600 C等工资
      7782 CLARK      09-JUN-81      2450 B等工资
      7698 BLAKE      01-MAY-81      2850 B等工资
      7566 JONES      02-APR-81      2975 B等工资
      7788 SCOTT      19-APR-87      3000 B等工资
      7902 FORD       03-DEC-81      3000 B等工资
      7839 KING       17-NOV-81      5000 A等工资

14 rows selected.

PPAS

scott=# select e.empno,e.ename,e.hiredate,e.sal,
scott-# DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
scott-# from emp e,salgrade s
scott-# where e.sal between s.losal and s.hisal;
 empno | ename  |      hiredate      |   sal   |  grade  
-------+--------+--------------------+---------+---------
  7369 | SMITH  | 17-DEC-80 00:00:00 |  800.00 | E等工资
  7876 | ADAMS  | 23-MAY-87 00:00:00 | 1100.00 | E等工资
  7900 | JAMES  | 03-DEC-81 00:00:00 |  950.00 | E等工资
  7521 | WARD   | 22-FEB-81 00:00:00 | 1250.00 | D等工资
  7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 | D等工资
  7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 | D等工资
  7499 | ALLEN  | 20-FEB-81 00:00:00 | 1600.00 | C等工资
  7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | C等工资
  7566 | JONES  | 02-APR-81 00:00:00 | 2975.00 | B等工资
  7698 | BLAKE  | 01-MAY-81 00:00:00 | 2850.00 | B等工资
  7782 | CLARK  | 09-JUN-81 00:00:00 | 2450.00 | B等工资
  7788 | SCOTT  | 19-APR-87 00:00:00 | 3000.00 | B等工资
  7902 | FORD   | 03-DEC-81 00:00:00 | 3000.00 | B等工资
  7839 | KING   | 17-NOV-81 00:00:00 | 5000.00 | A等工资
(14 rows)

9查询每个雇员的姓名、职位、基本工资、部门名称、工资所在公司的工资等级
Oracle

SQL> select e.ename,e.job,e.sal,d.dname,s.grade,
  2  DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
  3  from emp e,dept d,salgrade s
  4  where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

ENAME       JOB             SAL DNAME              GRADE GRADE
---------- ---------- ---------- --------------- ---------- --------------------
KING       PRESIDENT        5000 ACCOUNTING          5 A等工资
FORD       ANALYST        3000 RESEARCH          4 B等工资
SCOTT       ANALYST        3000 RESEARCH          4 B等工资
JONES       MANAGER        2975 RESEARCH          4 B等工资
BLAKE       MANAGER        2850 SALES              4 B等工资
CLARK       MANAGER        2450 ACCOUNTING          4 B等工资
ALLEN       SALESMAN        1600 SALES              3 C等工资
TURNER       SALESMAN        1500 SALES              3 C等工资
MILLER       CLERK        1300 ACCOUNTING          2 D等工资
WARD       SALESMAN        1250 SALES              2 D等工资
MARTIN       SALESMAN        1250 SALES              2 D等工资
ADAMS       CLERK        1100 RESEARCH          1 E等工资
JAMES       CLERK         950 SALES              1 E等工资
SMITH       CLERK         800 RESEARCH          1 E等工资

14 rows selected.

PPAS

scott=# select e.ename,e.job,e.sal,d.dname,s.grade,
scott-# DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
scott-# from emp e,dept d,salgrade s
scott-# where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
 ename  |    job    |   sal   |   dname    | grade |  grade  
--------+-----------+---------+------------+-------+---------
 SMITH  | CLERK     |  800.00 | RESEARCH   |     1 | E等工资
 ADAMS  | CLERK     | 1100.00 | RESEARCH   |     1 | E等工资
 JAMES  | CLERK     |  950.00 | SALES      |     1 | E等工资
 WARD   | SALESMAN  | 1250.00 | SALES      |     2 | D等工资
 MARTIN | SALESMAN  | 1250.00 | SALES      |     2 | D等工资
 MILLER | CLERK     | 1300.00 | ACCOUNTING |     2 | D等工资
 ALLEN  | SALESMAN  | 1600.00 | SALES      |     3 | C等工资
 TURNER | SALESMAN  | 1500.00 | SALES      |     3 | C等工资
 JONES  | MANAGER   | 2975.00 | RESEARCH   |     4 | B等工资
 BLAKE  | MANAGER   | 2850.00 | SALES      |     4 | B等工资
 CLARK  | MANAGER   | 2450.00 | ACCOUNTING |     4 | B等工资
 SCOTT  | ANALYST   | 3000.00 | RESEARCH   |     4 | B等工资
 FORD   | ANALYST   | 3000.00 | RESEARCH   |     4 | B等工资
 KING   | PRESIDENT | 5000.00 | ACCOUNTING |     5 | A等工资
(14 rows)

表的连接操作
10向emp表中 添加一条新的记录
Oracle

SQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
  2  values (8888,'SongYuejie','CLERK','7369',SYSDATE,800,100,null);

1 row created.

SQL> select * from emp;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
      8888 SongYuejie CLERK           7369 22-JUN-16        800       100
      7369 SMITH      CLERK           7902 17-DEC-80        800               20
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30
      7566 JONES      MANAGER           7839 02-APR-81           2975               20
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20
      7839 KING       PRESIDENT         17-NOV-81           5000               10
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20
      7900 JAMES      CLERK           7698 03-DEC-81        950               30
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10

15 rows selected.

PPAS

scott=# insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
scott-# values (8888,'SongYuejie','CLERK','7369',SYSDATE,800,100,null);
INSERT 0 1
scott=# select * from emp;
 empno |   ename    |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+------------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH      | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN      | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD       | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES      | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN     | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE      | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK      | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT      | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING       | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER     | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS      | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES      | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD       | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER     | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
  8888 | SongYuejie | CLERK     | 7369 | 22-JUN-16 11:09:17 |  800.00 |  100.00 |       
(15 rows)

11将emp和dept表联合查询，使用内连接（等值连接）
Oracle

SQL> select *
  2  from emp e,dept d
  3  where e.deptno=d.deptno;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO     DEPTNO DNAME         LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
      7839 KING       PRESIDENT         17-NOV-81           5000               10      10 ACCOUNTING      NEW YORK
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      10 ACCOUNTING      NEW YORK
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      10 ACCOUNTING      NEW YORK
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      20 RESEARCH         DALLAS
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      20 RESEARCH         DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      20 RESEARCH         DALLAS
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      20 RESEARCH         DALLAS
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      20 RESEARCH         DALLAS
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      30 SALES         CHICAGO
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      30 SALES         CHICAGO
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      30 SALES         CHICAGO
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      30 SALES         CHICAGO
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      30 SALES         CHICAGO
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      30 SALES         CHICAGO

14 rows selected.

PPAS

scott=# select *
scott-# from emp e,dept d 
scott-# where e.deptno=d.deptno;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno | deptno |   dname    |   loc    
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30 |     30 | SALES      | CHICAGO
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30 |     30 | SALES      | CHICAGO
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
(14 rows)

12使用左外连接，显示雇员8888的信息
Oracle

SQL> select *
  2  from emp e,dept d
  3  where e.deptno=d.deptno(+);

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO     DEPTNO DNAME         LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      10 ACCOUNTING      NEW YORK
      7839 KING       PRESIDENT         17-NOV-81           5000               10      10 ACCOUNTING      NEW YORK
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      10 ACCOUNTING      NEW YORK
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      20 RESEARCH         DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      20 RESEARCH         DALLAS
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      20 RESEARCH         DALLAS
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      20 RESEARCH         DALLAS
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      20 RESEARCH         DALLAS
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      30 SALES         CHICAGO
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      30 SALES         CHICAGO
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      30 SALES         CHICAGO
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      30 SALES         CHICAGO
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      30 SALES         CHICAGO
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      30 SALES         CHICAGO
      8888 SongYuejie CLERK           7369 22-JUN-16        800       100

15 rows selected.

PPAS

scott=# select *
scott-# from emp e,dept d 
scott-# where e.deptno=d.deptno(+);
 empno |   ename    |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno | deptno |   dname    |   loc    
-------+------------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
  7369 | SMITH      | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7499 | ALLEN      | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO
  7521 | WARD       | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO
  7566 | JONES      | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7654 | MARTIN     | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO
  7698 | BLAKE      | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30 |     30 | SALES      | CHICAGO
  7782 | CLARK      | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7788 | SCOTT      | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7839 | KING       | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7844 | TURNER     | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO
  7876 | ADAMS      | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7900 | JAMES      | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30 |     30 | SALES      | CHICAGO
  7902 | FORD       | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7934 | MILLER     | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  8888 | SongYuejie | CLERK     | 7369 | 22-JUN-16 11:09:17 |  800.00 |  100.00 |        |        |            | 
(15 rows)

本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！”

文章标签：
关系型数据库
SQL
Oracle
数据库
宋跃杰
目录
相关文章
2019ab
|
6月前
|
关系型数据库 MySQL
Mysql基础第十七天,使用子查询
Mysql基础第十七天,使用子查询
2019ab
32 0
2019ab
|
6月前
|
关系型数据库 MySQL
Mysql基础第二十天,组合查询
Mysql基础第二十天,组合查询
2019ab
39 0
Mysql基础第二十天,组合查询
2019ab
|
6月前
|
关系型数据库 MySQL
Mysql基础第四天,Mysql数据类型
Mysql基础第四天,Mysql数据类型
2019ab
34 0
Mysql基础第四天,Mysql数据类型
可口也可樂、
|
11月前
|
SQL Oracle 关系型数据库
MySQL基础-多表查询
MySQL基础-多表查询
可口也可樂、
103 0
砖业洋__
|
存储 机器学习/深度学习 算法
面试之前，MySQL表连接必须过关！——表连接的原理
什么是连接查询？笛卡尔积如何避免？内连接和外连接的概念是什么？表连接的原理是什么？Simple Nested-Loop Join、Index Nested-Loop Join、Block Nested-Loop Join、Hash Join分别是什么概念？怎样分析表连接使用了哪种连接算法？本文带你一探究竟！
砖业洋__
157 0
面试之前，MySQL表连接必须过关！——表连接的原理
cciafumrihyou
|
关系型数据库 MySQL
MySQL基础-多表查询案例
代码如下
cciafumrihyou
141 0
cciafumrihyou
|
SQL 关系型数据库 MySQL
MySQL基础-多表查询之外连接
外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：
cciafumrihyou
117 0
cciafumrihyou
|
关系型数据库 MySQL
MySQL基础-多表查询之内连接
内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。
cciafumrihyou
103 0
一切总会归于平淡
|
SQL 关系型数据库 MySQL
MySQL基础之多表查询
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
一切总会归于平淡
141 1
MySQL基础之多表查询
游客af6ph6rww4mlo
|
SQL JavaScript 关系型数据库
MySQL基础教程15——多表查询
笛卡尔积显示出来的数据会有我们不需要的，因此我们可以用条件筛选掉不需要的条件。
游客af6ph6rww4mlo
224 0