Oracle Database 11g SQL 开发指南学习笔记：从数据库中检索数据

 

--设置日期格式
alter session set nls_date_format = 'yyyy-mm-dd';

--1.
--查询某些字段
select customer_id,
       first_name,
       last_name,
       dob,
       phone
from customers;


--查询所有列
select *
from customers;

--过滤行
select *
from customers
where customer_id = 2;


--行标识符，伪列：rowid
select rowid,
       customer_id
from customers;


--行号，伪列：rownum
select rownum,
       customer_id
from customers;



--2.算术运算
select 2 * 6 from dual;

--日期运算
select to_date('2007-05-08'),
       to_date('2007-05-08') + 2 ,
       to_date('2007-05-08') - 8,
       to_date('2007-05-08') - to_date('2007-04-08')
from dual;


--字符串连接运算
select first_name || ' ' || last_name
from customers;


--dual表包含一个varchar2数据类型的列，列名为dummy，而且只包含一行，值为x
desc dual;

select * from dual;


--列运算
select name,
       price,
       price + 2,
       price * 10 + 2
from products;


--注意运算符的优先级，（）可用于指定操作符的执行顺序
select 10 * 12 / 3 - 1,
       10 * (12 / 3 - 1)
from dual;



--3.列别名
select price * 2 double_price,            --列别名会变为大写
       price * 2 + 1 as "double price",   --使用双引号后，可以保持列别名的大小写、空格
       
       price * 4 as double_price          --可以使用可选的关键字as
from products;


--4.空值
--查询dob列为空的数据
select customer_id,
       first_name,
       last_name,
       dob
from customers
where dob is null;


--查询dob列为非空的数据
select customer_id,
       first_name,
       last_name,
       dob
from customers
where dob is not null;


--nvl函数：转换空值为一个可以理解的值
select customer_id,
       first_name,
       last_name,
       nvl（dob，'2100-01-01') as dob
from customers
where dob is