《卸甲笔记》-基础语法对比

2021-09-14 12:39:37

以Oracle中sottt用户下的数据为例，PPAS 中scott用户下面的数据由Oracle迁移而来
1 查询emp表中的数据

Oracle

[root@test03 ~]# su -  oracle
[oracle@test03 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 16 22:05:19 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/scott
Connected.
SQL> select * from emp;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      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


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      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


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      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


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      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


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


14 rows selected.

PPAS

[root@test06 ~]# su – enterprisedb
-bash-4.1$ psql -U scott -d scott
Password for user scott: 
psql.bin (9.3.1.3)
Type "help" for help.
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
(14 rows)

2查询出公司的雇员雇佣情况，所以希望通过数据库可以查找到每个雇员的编号，姓名，基本工资3个信息进行浏览
Oracle

SQL> select empno,ename,job from emp;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER
      7654 MARTIN     SALESMAN
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK

     EMPNO ENAME      JOB
---------- ---------- ---------
      7900 JAMES      CLERK
      7902 FORD       ANALYST
      7934 MILLER     CLERK

14 rows selected.

PPAS

scott=# select empno,ename,job from emp;
 empno | ename  |    job    
-------+--------+-----------
  7369 | SMITH  | CLERK
  7499 | ALLEN  | SALESMAN
  7521 | WARD   | SALESMAN
  7566 | JONES  | MANAGER
  7654 | MARTIN | SALESMAN
  7698 | BLAKE  | MANAGER
  7782 | CLARK  | MANAGER
  7788 | SCOTT  | ANALYST
  7839 | KING   | PRESIDENT
  7844 | TURNER | SALESMAN
  7876 | ADAMS  | CLERK
  7900 | JAMES  | CLERK
  7902 | FORD   | ANALYST
  7934 | MILLER | CLERK
(14 rows)

3查询公司中国所有雇员的职位信息
Oracle

SQL> select job from emp;

JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK

JOB
---------
CLERK
ANALYST
CLERK

14 rows selected.

PPAS

scott=# select job from emp;
    job    
-----------
 CLERK
 SALESMAN
 SALESMAN
 MANAGER
 SALESMAN
 MANAGER
 MANAGER
 ANALYST
 PRESIDENT
 SALESMAN
 CLERK
 CLERK
 ANALYST
 CLERK
(14 rows)

4 显示的职位包含 了太多的重复内容，使用distinct关键字去掉全部的重复内容

Oracle

SQL> select distinct job from emp;

JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

PPAS

scott=# select distinct job from emp;
    job    
-----------
 SALESMAN
 CLERK
 MANAGER
 PRESIDENT
 ANALYST
(5 rows)

5 查询雇员编号、职位
Oracle

SQL> select distinct empno,job from emp;

     EMPNO JOB
---------- ---------
      7369 CLERK
      7499 SALESMAN
      7521 SALESMAN
      7566 MANAGER
      7654 SALESMAN
      7698 MANAGER
      7782 MANAGER
      7788 ANALYST
      7839 PRESIDENT
      7844 SALESMAN
      7876 CLERK

     EMPNO JOB
---------- ---------
      7900 CLERK
      7902 ANALYST
      7934 CLERK

14 rows selected.

PPAS

scott=# select distinct empno,job from emp;
 empno |    job    
-------+-----------
  7844 | SALESMAN
  7934 | CLERK
  7839 | PRESIDENT
  7876 | CLERK
  7788 | ANALYST
  7698 | MANAGER
  7521 | SALESMAN
  7369 | CLERK
  7900 | CLERK
  7499 | SALESMAN
  7782 | MANAGER
  7654 | SALESMAN
  7902 | ANALYST
  7566 | MANAGER
(14 rows)

6通过数据库查询出所有雇员的编号，雇员姓名和年基本工资，日常基本工资，以作为年终奖金的发放标准
Oracle

SQL> select empno,ename,sal*12,sal/30 from emp;

     EMPNO ENAME      SAL*12     SAL/30
---------- ---------- ---------- ----------
      7369 SMITH        9600 26.6666667
      7499 ALLEN       19200 53.3333333
      7521 WARD        15000 41.6666667
      7566 JONES       35700 99.1666667
      7654 MARTIN       15000 41.6666667
      7698 BLAKE       34200     95
      7782 CLARK       29400 81.6666667
      7788 SCOTT       36000    100
      7839 KING        60000 166.666667
      7844 TURNER       18000     50
      7876 ADAMS       13200 36.6666667

     EMPNO ENAME      SAL*12     SAL/30
---------- ---------- ---------- ----------
      7900 JAMES       11400 31.6666667
      7902 FORD        36000    100
      7934 MILLER       15600 43.3333333

14 rows selected.

PPAS

scott=# select empno,ename,sal*12,sal/30 from emp;
 empno | ename  | ?column? |       ?column?       
-------+--------+----------+----------------------
  7369 | SMITH  |  9600.00 |  26.6666666666666667
  7499 | ALLEN  | 19200.00 |  53.3333333333333333
  7521 | WARD   | 15000.00 |  41.6666666666666667
  7566 | JONES  | 35700.00 |  99.1666666666666667
  7654 | MARTIN | 15000.00 |  41.6666666666666667
  7698 | BLAKE  | 34200.00 |  95.0000000000000000
  7782 | CLARK  | 29400.00 |  81.6666666666666667
  7788 | SCOTT  | 36000.00 | 100.0000000000000000
  7839 | KING   | 60000.00 | 166.6666666666666667
  7844 | TURNER | 18000.00 |  50.0000000000000000
  7876 | ADAMS  | 13200.00 |  36.6666666666666667
  7900 | JAMES  | 11400.00 |  31.6666666666666667
  7902 | FORD   | 36000.00 | 100.0000000000000000
  7934 | MILLER | 15600.00 |  43.3333333333333333
(14 rows)

7 公司每个雇员在年底的时候可以领取5000元的年终奖，查询雇员的编号、姓名和增长后的年基本工资（不包括佣金）
Oracle

SQL> select empno,ename,sal*12+5000 from emp;

     EMPNO ENAME      SAL*12+5000
---------- ---------- -----------
      7369 SMITH        14600
      7499 ALLEN        24200
      7521 WARD         20000
      7566 JONES        40700
      7654 MARTIN        20000
      7698 BLAKE        39200
      7782 CLARK        34400
      7788 SCOTT        41000
      7839 KING         65000
      7844 TURNER        23000
      7876 ADAMS        18200

     EMPNO ENAME      SAL*12+5000
---------- ---------- -----------
      7900 JAMES        16400
      7902 FORD         41000
      7934 MILLER        20600

14 rows selected.

PPAS

scott=# select empno,ename,sal*12+5000 from emp;
 empno | ename  | ?column? 
-------+--------+----------
  7369 | SMITH  | 14600.00
  7499 | ALLEN  | 24200.00
  7521 | WARD   | 20000.00
  7566 | JONES  | 40700.00
  7654 | MARTIN | 20000.00
  7698 | BLAKE  | 39200.00
  7782 | CLARK  | 34400.00
  7788 | SCOTT  | 41000.00
  7839 | KING   | 65000.00
  7844 | TURNER | 23000.00
  7876 | ADAMS  | 18200.00
  7900 | JAMES  | 16400.00
  7902 | FORD   | 41000.00
  7934 | MILLER | 20600.00
(14 rows)

8公司每个月为雇员增加200元的补助金，此时，要求可以查询出每个雇员的编号，姓名，基本年工资
Oracle

SQL> select empno,ename,(sal+200)*12+5000 from emp;

     EMPNO ENAME      (SAL+200)*12+5000
---------- ---------- -----------------
      7369 SMITH          17000
      7499 ALLEN          26600
      7521 WARD           22400
      7566 JONES          43100
      7654 MARTIN          22400
      7698 BLAKE          41600
      7782 CLARK          36800
      7788 SCOTT          43400
      7839 KING           67400
      7844 TURNER          25400
      7876 ADAMS          20600

     EMPNO ENAME      (SAL+200)*12+5000
---------- ---------- -----------------
      7900 JAMES          18800
      7902 FORD           43400
      7934 MILLER          23000

14 rows selected.

PPAS

scott=# select empno,ename,(sal+200)*12+5000 from emp;
 empno | ename  | ?column? 
-------+--------+----------
  7369 | SMITH  | 17000.00
  7499 | ALLEN  | 26600.00
  7521 | WARD   | 22400.00
  7566 | JONES  | 43100.00
  7654 | MARTIN | 22400.00
  7698 | BLAKE  | 41600.00
  7782 | CLARK  | 36800.00
  7788 | SCOTT  | 43400.00
  7839 | KING   | 67400.00
  7844 | TURNER | 25400.00
  7876 | ADAMS  | 20600.00
  7900 | JAMES  | 18800.00
  7902 | FORD   | 43400.00
  7934 | MILLER | 23000.00
(14 rows)

9为查询结果设置别名

Oralce

SQL> select empno 雇员编号,ename 雇员姓名,(sal+200)*12+5000 as 年薪 from emp;

  雇员编号雇员姓名        年薪
---------- ---------- ----------
      7369 SMITH       17000
      7499 ALLEN       26600
      7521 WARD        22400
      7566 JONES       43100
      7654 MARTIN       22400
      7698 BLAKE       41600
      7782 CLARK       36800
      7788 SCOTT       43400
      7839 KING        67400
      7844 TURNER       25400
      7876 ADAMS       20600

  雇员编号雇员姓名        年薪
---------- ---------- ----------
      7900 JAMES       18800
      7902 FORD        43400
      7934 MILLER       23000

14 rows selected.

PPAS

scott=# select empno 雇员编号,ename 雇员姓名,(sal+200)*12+5000 as 年薪 from emp;
 雇员编号 | 雇员姓名 |   年薪   
----------+----------+----------
     7369 | SMITH    | 17000.00
     7499 | ALLEN    | 26600.00
     7521 | WARD     | 22400.00
     7566 | JONES    | 43100.00
     7654 | MARTIN   | 22400.00
     7698 | BLAKE    | 41600.00
     7782 | CLARK    | 36800.00
     7788 | SCOTT    | 43400.00
     7839 | KING     | 67400.00
     7844 | TURNER   | 25400.00
     7876 | ADAMS    | 20600.00
     7900 | JAMES    | 18800.00
     7902 | FORD     | 43400.00
     7934 | MILLER   | 23000.00
(14 rows)

10在select子句中使用常量，为以上的查询增加一个货币的描述
Oracle

SQL>  select empno as 雇员编号,ename as 雇员姓名,(sal+200)*12+5000 as 年薪,'￥' as 货币 from emp; 

  雇员编号 雇员姓名              年薪 货币
---------- -------------------- ---------- ------
      7369 SMITH             17000 ￥
      7499 ALLEN             26600 ￥
      7521 WARD              22400 ￥
      7566 JONES             43100 ￥
      7654 MARTIN             22400 ￥
      7698 BLAKE             41600 ￥
      7782 CLARK             36800 ￥
      7788 SCOTT             43400 ￥
      7839 KING              67400 ￥
      7844 TURNER             25400 ￥
      7876 ADAMS             20600 ￥

  雇员编号 雇员姓名              年薪 货币
---------- -------------------- ---------- ------
      7900 JAMES             18800 ￥
      7902 FORD              43400 ￥
      7934 MILLER             23000 ￥

14 rows selected.

PPAS

scott=# select empno as 雇员编号,ename as 雇员姓名,(sal+200)*12+5000 as 年薪,'￥' as 货币 from emp;                                  
 雇员编号 | 雇员姓名 |   年薪   | 货币 
----------+----------+----------+------
     7369 | SMITH    | 17000.00 | ￥
     7499 | ALLEN    | 26600.00 | ￥
     7521 | WARD     | 22400.00 | ￥
     7566 | JONES    | 43100.00 | ￥
     7654 | MARTIN   | 22400.00 | ￥
     7698 | BLAKE    | 41600.00 | ￥
     7782 | CLARK    | 36800.00 | ￥
     7788 | SCOTT    | 43400.00 | ￥
     7839 | KING     | 67400.00 | ￥
     7844 | TURNER   | 25400.00 | ￥
     7876 | ADAMS    | 20600.00 | ￥
     7900 | JAMES    | 18800.00 | ￥
     7902 | FORD     | 43400.00 | ￥
     7934 | MILLER   | 23000.00 | ￥
(14 rows)

11使用“||”进行连接显示
Oracle

SQL>  select '编号是:'||empno||'的雇员姓名是:'||ename||',基本工资是:'||sal 雇员信息 from emp;

雇员信息
--------------------------------------------------------------------------------
编号是:7369的雇员姓名是:SMITH,基本工资是:800
编号是:7499的雇员姓名是:ALLEN,基本工资是:1600
编号是:7521的雇员姓名是:WARD,基本工资是:1250
编号是:7566的雇员姓名是:JONES,基本工资是:2975
编号是:7654的雇员姓名是:MARTIN,基本工资是:1250
编号是:7698的雇员姓名是:BLAKE,基本工资是:2850
编号是:7782的雇员姓名是:CLARK,基本工资是:2450
编号是:7788的雇员姓名是:SCOTT,基本工资是:3000
编号是:7839的雇员姓名是:KING,基本工资是:5000
编号是:7844的雇员姓名是:TURNER,基本工资是:1500
编号是:7876的雇员姓名是:ADAMS,基本工资是:1100

雇员信息
--------------------------------------------------------------------------------
编号是:7900的雇员姓名是:JAMES,基本工资是:950
编号是:7902的雇员姓名是:FORD,基本工资是:3000
编号是:7934的雇员姓名是:MILLER,基本工资是:1300

14 rows selected.

PPAS

scott=# select '编号是:'||empno||'的雇员姓名是:'||ename||',基本工资是:'||sal 雇员信息 from emp;
                     雇员信息                      
---------------------------------------------------
 编号是:7369的雇员姓名是:SMITH,基本工资是:800.00
 编号是:7499的雇员姓名是:ALLEN,基本工资是:1600.00
 编号是:7521的雇员姓名是:WARD,基本工资是:1250.00
 编号是:7566的雇员姓名是:JONES,基本工资是:2975.00
 编号是:7654的雇员姓名是:MARTIN,基本工资是:1250.00
 编号是:7698的雇员姓名是:BLAKE,基本工资是:2850.00
 编号是:7782的雇员姓名是:CLARK,基本工资是:2450.00
 编号是:7788的雇员姓名是:SCOTT,基本工资是:3000.00
 编号是:7839的雇员姓名是:KING,基本工资是:5000.00
 编号是:7844的雇员姓名是:TURNER,基本工资是:1500.00
 编号是:7876的雇员姓名是:ADAMS,基本工资是:1100.00
 编号是:7900的雇员姓名是:JAMES,基本工资是:950.00
 编号是:7902的雇员姓名是:FORD,基本工资是:3000.00
 编号是:7934的雇员姓名是:MILLER,基本工资是:1300.00
(14 rows)
上一篇：Mysql 通过frm&ibd 恢复数据


下一篇：【Bug笔记】The superclass "javax.servlet.http.HttpServlet" was not found on the Java Build Path