--设置日期格式
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