ROWID 行标识符 18位
ROWNUM 行号
例:select rowid,rownum from customers;
========================================
算术运算
+,-,*,/
例:select 2*6 from dual;
========================================
日期运算
to_date(将一个字符串转换成日期类型)
例:select to_date('25-MAR-2008')+2 from dual;
========================================
理解别名
特别的,如果希望别名里面保持有空格的话 ,别名可用""引起来
例:select price*2 "Double Price" from products;
另外可选用关键字AS
例:select price as "price" from products
========================================
连接字符 ||
例:select first_name || ' ' ||last_name as "Customer Name" from customers
这样获取的结果集如: John Brown
========================================
空值
空值并不是空字符串,而是表示该列的值未知
区分空值和空字符串用函数 nvl(,)
========================================
比较值
ANY,ALL
查询出比2,3,4任一个都大的值
例:select * from customers where customer_id > any(2,3,4)
查询出比2,3,4都大的值
例:select * from customers where customer_id > all(2,3,4)
========================================
SQL操作符
like,in,between,is null,is nan,is infinite
特殊地,like 后如要对下划线和百分号进行匹配,需要用到转义符 \ 和 ESCAPE选项
例:select name from promotions where name like '%\%%' escape '\';
结果集,会列出 20%,30%等匹配值
特殊地,如果列表中包含一个空值,则 not in返回false,即不返回任何行
例:select * from cutomers where customer_id not in (2,3,4,null);
========================================
ORDER BY
默认是按升序对字段进行排列
特殊地,可以根据列的次序指定对哪一列进行排序:1表示按第一列进行排序
例:select * from customers order by 1;