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

空的舊更多了。

也就是哪邊缺，哪邊方加號。

這就是外連接。