db2

 select * from recruit fetch first 5 rows only

经典oracle sql

select   *   from  ( select  newtable. * ,rownum rownum_  from  ( select   *   from  s_emp) newtable  where  rownum  <=   20 )  where  rownum_  >   5

rownum在用于大于号时，不可以，必须先造一个table把rownum这个伪列当作这个新造出的表中的一个字段，才能进行特定行之间的查询。



DML Data manipulation language
   insert update delete

DDL Data definition language
   create alter drop rename trancate

Transaction control
   commit rollback savepoint

DCL
   GRANT REVOKE

==========================

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(7)
 LAST_NAME                                 NOT NULL VARCHAR2(15)
 FIRST_NAME                                         VARCHAR2(15)
 USERID                                             VARCHAR2(8)
 START_DATE                                         DATE
 COMMENTS                                           VARCHAR2(15)
 MANAGER_ID                                         NUMBER(7)
 TITLE                                              VARCHAR2(20)
 DEPT_ID                                            NUMBER(7)
 SALARY                                             NUMBER(11,2)
 COMMISSION_PCT                                     NUMBER(4,2)

select last_name,salary, 12*salary + 100 from s_emp;

支持操作符

支持别名

select last_name ln from s_emp;

支持Concatenation，级联操作符

select first_name||last_name from s_emp


对于NULL值

select  last_name,title,salary * commission_pct / 100  COMM  from  s_emp;

这样查询会有很多NULL值

有一个NVL函数，以便在NULL出现时，给予一个默认值。

select  last_name,title, salary * NVL(commission_pct, 0 ) / 100  COMM  from  s_emp;

commission_pct，出现NULL，就会被0替换。

Eliminate duplicate rows by using distinct in select clause;

select   distinct  name  from  s_dept;

=============================================================

SQL> select name,salary,deptno from ( select concat(last_name,first_name) name,salary,department_id deptno,rank() over (partition by department_id order by salary desc) rnk from employees) where rnk = 2 or rnk = 3;

NAME                                              SALARY     DEPTNO
--------------------------------------------- ---------- ----------
FayPat                                              6000         20
KhooAlexander                                       3100         30
BaidaShelli                                         2900         30
WeissMatthew                                        8000         50
KauflingPayam                                       7900         50
ErnstBruce                                          6000         60
AustinDavid                                         4800         60
PataballaValli                                      4800         60
PartnersKaren                                      13500         80
ErrazurizAlberto                                   12000         80
KochharNeena                                       17000         90

NAME                                              SALARY     DEPTNO
--------------------------------------------- ---------- ----------
De HaanLex                                         17000         90
FavietDaniel                                        9000        100
ChenJohn                                            8200        100
GietzWilliam                                        8300        110

15 rows selected.

SQL>

SQL> desc employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

SQL 排名问题

找出部门工资排名第二，三的员工

=====================

复习一下外连接Outer Join

SQL> desc s_emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(7)
 LAST_NAME                                 NOT NULL VARCHAR2(15)
 FIRST_NAME                                         VARCHAR2(15)
 USERID                                             VARCHAR2(8)
 START_DATE                                         DATE
 COMMENTS                                           VARCHAR2(15)
 MANAGER_ID                                         NUMBER(7)
 TITLE                                              VARCHAR2(20)
 DEPT_ID                                            NUMBER(7)
 SALARY                                             NUMBER(11,2)
 COMMISSION_PCT                                     NUMBER(4,2)

SQL> desc s_customer
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(25)
 ZIP_CODE                                           VARCHAR2(20)
 CREDIT_RA                                          VARCHAR2(25)
 SAL                                                NUMBER
 ID                                                 NUMBER(5)

客户表里的SAL字段是s_emp的外键。表示该客户的销售代表。

那么要想查出所有客户（包括没有销售代表的）所对应的销售代表记录。

  1  select e.last_name,e.id,c.name
  2  from s_emp e,s_customer c
  3  where e.id(+) = c.sal
  4* order by e.id

LAST_NAME               ID NAME
--------------- ---------- -------------------------
_dumas                  12 athletes attic
_dumas                  12 great athletes
_dumas                  12 bj athletics
_dumas                  12 athletic for all
_dumas                  12 sports,inc
                           athletics two
                           athletics one
                           shhes for sports
                           athletic attire
                           toms sporting goods

可以看到相应的部分客户并不存在销售代表也出现在结果集中，而用等值链接是不能查出的。

  1  select e.last_name,e.id,c.name
  2  from s_emp e,s_customer c
  3  where e.id = c.sal(+)
  4* order by e.id

查出所有销售代表所对应的客户。

LAST_NAME               ID NAME
--------------- ---------- -------------------------
_dumas                   8
hui                      8
aaa                     10
%ss                     11
_dumas                  11
_dumas                  12 sports,inc
_dumas                  12 athletic for all
_dumas                  12 bj athletics
_dumas                  12 great athletes
_dumas                  12 athletes attic
_dumas                  13

LAST_NAME               ID NAME
--------------- ---------- -------------------------
_dumas                  15
_dumas                  16
_dumas                  17
_dumas                  18
_dumas                  19
_dumas                  21
payn                    23
_dumas                  25
_dumas                  47
_dumas                  70
_dumas                  76

LAST_NAME               ID NAME
--------------- ---------- -------------------------
_dumas                  95
liganfeng              112
lgf                    122
lgf                    134
Biri                   333
qu                     555
_dumas                2271
tarena               25999
tarena               26999

空的旧更多了。

也就是哪边缺，哪边方加号。

这就是外连接。