sql 语句学习

SQL编程笔记

1 从表中检索信息

1.1 从一个表中检索信息

表的每行都有一个rowid,可以通过下面的语句来显示。

SQL> select rowid, customer_id from customers;

ROWID              CUSTOMER_ID
------------------ -----------
AAAR34AALAAAACFAAA           1
AAAR34AALAAAACFAAB           2
AAAR34AALAAAACFAAC           3
AAAR34AALAAAACFAAD           4
AAAR34AALAAAACFAAE           5

日期函数及运算

SQL> select to_date('10-OCT-10') + 30 as yue_feng from dual;

YUE_FENG
---------
09-NOV-10

列运算和列别名

SQL> select name, price*2/5 + 8 - 1*4*2 as price_after from products;

NAME                           PRICE_AFTER
------------------------------ -----------
Modern Science                        7.98
Chemistry                               12
Supernova                           10.396
Tank War                              5.58
Z Files                             19.996
2412: The Return                      5.98
Space Force 9                        5.396
From Another Planet                  5.196
Classical Music                      4.396
Pop 3                                6.396
Creative Yell                        5.996

NAME                           PRICE_AFTER
------------------------------ -----------
My Front Line                        5.396

12 rows selected.

使用串联操作符合并输出结果

SQL> select first_name || '-' || last_name as "Customer Name" from customers;

Customer Name
---------------------
John-Brown
Cynthia-Green
Steve-White
Gail-Black
Doreen-Blue

空值与NOL()函数

SQL> select customer_id, first_name, last_name, NVL(phone,'unknown phone number') AS PhoneNumber from customers where phone is null;

CUSTOMER_ID FIRST_NAME LAST_NAME  PHONENUMBER
----------- ---------- ---------- --------------------
          5 Doreen     Blue       unknown phone number

禁止显示重复行

SQL> select distinct product_id from purchases;

PRODUCT_ID
----------
         1
         2
         3

使用where子句过滤

SQL> select * from customers where customer_id > 2;

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          3 Steve      White      16-MAR-71 800-555-1213
          4 Gail       Black                800-555-1214
          5 Doreen     Blue       20-MAY-70

SQL> select * from customers where customer_id > any(2, 3, 4);

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          3 Steve      White      16-MAR-71 800-555-1213
          4 Gail       Black                800-555-1214
          5 Doreen     Blue       20-MAY-70

SQL操作符

LIKE IN BETWEEN IS NULL IS NAN IS INFINITE NOT

-- like 操作符
SQL> select * from customers where first_name like '_o%';

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          1 John       Brown      01-JAN-65 800-555-1211
          5 Doreen     Blue       20-MAY-70
          
-- in 操作符
SQL> select * from customers where customer_id in (2, 3, 5);

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          2 Cynthia    Green      05-FEB-68 800-555-1212
          3 Steve      White      16-MAR-71 800-555-1213
          5 Doreen     Blue       20-MAY-70
          
-- between操作符
SQL> select * from customers where customer_id between 1 and 3;

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          1 John       Brown      01-JAN-65 800-555-1211
          2 Cynthia    Green      05-FEB-68 800-555-1212
          3 Steve      White      16-MAR-71 800-555-1213
         
-- 逻辑操作符
SQL> select * from customers where dob > '01-JAN-1970' and customer_id > 3;

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          5 Doreen     Blue       20-MAY-70

-- 操作符的优先级 and的优先级大于or
SQL> select * from customers where dob > '01-JAN-1970'
  2  or customer_id < 2
  3  and phone like '%1211';

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          1 John       Brown      01-JAN-65 800-555-1211
          3 Steve      White      16-MAR-71 800-555-1213
          5 Doreen     Blue       20-MAY-70

使用order by排序

-- 默认升序 即在后面加 ASC
SQL> select * from customers order by last_name;

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          4 Gail       Black                800-555-1214
          5 Doreen     Blue       20-MAY-70
          1 John       Brown      01-JAN-65 800-555-1211
          2 Cynthia    Green      05-FEB-68 800-555-1212
          3 Steve      White      16-MAR-71 800-555-1213

-- 声明为降序 即在后面加了DESC
SQL> select * from customers order by last_name desc;

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE
----------- ---------- ---------- --------- ------------
          3 Steve      White      16-MAR-71 800-555-1213
          2 Cynthia    Green      05-FEB-68 800-555-1212
          1 John       Brown      01-JAN-65 800-555-1211
          5 Doreen     Blue       20-MAY-70
          4 Gail       Black                800-555-1214

1.2 从两个表中检索信息