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 null;


--5.去除重复值
select customer_id
from purchases;

select distinct customer_id
from purchases;

select unique customer_id
from purchases;


--6.比较运算
select *
from customers
where customer_id = 2

select *
from customers
where customer_id <> 2

select *
from products
where rownum <= 3

--查询customer_id大于2,3,4中任意一个的记录
select *
from customers
where customer_id > any(2,3,4);

--查询customer_id比2,3,4都大的记录
select *
from customers
where customer_id > all(2,3,4)


--7.SQL操作符
--like操作符
select *
from customers
where first_name like '_o%'  --以任意一个字符开头，第二个字符为o，的字符串

select *
from customers
where first_name not like '_o%'  --排除，以任意一个字符开头第二个字符为o的字符串
 
--如果需要对一个字符串中的下划线或%进行匹配时，可以通过escape选项标识这些字符 
--escape选项中的反斜杠表示了前面like的字符串中反斜杠后的字符只是普通字符，而不是特殊字符
select name
from promotions
where name like '%\%%' escape '\';  

--这里不再用反斜杠\来做转义字符，而是通过一个字符Z来表示转义字符，表示这个字符后的字符是个普通字符
select name
from promotions
where name like '%Z%%' escape 'Z';

--这里会报错，因为一旦把字符Z作为转义字符，那么在like的模式字符串中使用这个Z这个字符就必须按规定，
--后面必须跟上一个字符，否则就会报错：ORA-01424: 转义符之后字符缺失或非法
select name
from promotions
where name like '%Z%%Z' escape 'Z';

--这里不会报错，因为转义字符后面包含了一个字符，虽然这个字符也是转义字符，但后一个转义字符z，
--被前面的转义字符转义了，也就是一个普通的字符，也就是可以匹配以Z字符结尾的记录
select name
from promotions
where name like '%Z%%ZZ' escape 'Z';

--这里也不会报错，而且能返回记录，ZZ%中第一个Z是转义字符，把第二个Z转义为普通字符，
--所以后面的%还是一个特殊字符，表示匹配0-N个任意字符，所以会返回包含了字符%，同时也包含了Z字符的数据
select name
from promotions
where name like '%Z%%ZZ%' escape 'Z';


--in操作符
select *
from customers
where customer_id in (2,3,4);

--1,5不是2,3,4，但是1,5是不是null，是无法判断的，总会返回false，所以返回0条记录。
--就是如果在not in 的列表中有null值，那么最后不会返回任何结果集。
select *
from customers
where customer_id not in (2,3,4,null); 


--between操作符
select *
from customers
where customer_id between 2 and 4

--逻辑操作符：and 和 or
select *
from customers
where customer_id >= 2 and dob > '1971-01-01'


select *
from customers
where customer_id >= 2 or dob > '1970-01-01'

--注意and和or的优先级,and优于or
select *
from customers
where dob > '1970-01-01' 
      or customer_id < 2
      and phone like '%1211'


--8.排序
--通过order by关键字指定排序的列和顺序（asc升序，desc降序）
--排序的列可以不包含在select字句的字段列表中
select customer_id,
       dob,
       phone
from customers
order by first_name asc,last_name desc

--不用指定列名，而是直接通过列在结果集中的顺序指定
select customer_id,
       first_name,
       last_name,
       dob,
       phone
from customers
order by 2 asc,3 desc


--9.表关联
--9.1 适用于Oracle Database 8i及更低版本，应该使用SQL/86标准语法。
--内连接
select p.name,
       pt.name
from products p,product_types pt
where p.product_type_id = pt.product_type_id
order by p.name

--左外连接
--以products为主表，会返回products所有的记录，包括product_type_id为null的记录
--以product_types为辅表，对应的关联字段要加上外连接操作符：(+)
select p.name,
       pt.name
from products p,product_types pt
where p.product_type_id = pt.product_type_id(+) 

--右外连接
--以product_types为主表，会返回所有产品类型，
--以products为辅表，对应的关联字段要加上外连接操作符：(+)
select p.name,
       pt.name
from products p,product_types pt
where p.product_type_id(+) = pt.product_type_id


--外连接的限制
--不能在连接的两端使用oracle的连接操作符
/*
错误报告:
SQL 错误: ORA-01468: 一个谓词只能引用一个外部联接的表
*/
select p.name,
       pt.name
from products p,product_types pt
where p.product_type_id(+) = pt.product_type_id(+)


--不能同时使用一个外连接条件和另外一个使用or操作符的连接条件
/*
错误报告:
SQL 错误: ORA-01719: OR 或 IN 操作数中不允许外部联接运算符 (+)
*/
select p.product_type_id,pt.product_type_id,
       p.name,
       pt.name
from products p,product_types pt
where p.product_type_id(+) = 1
      or p.product_type_id(+) = pt.product_type_id;


--需要注意的是，但是下面的查询确没有报错，可以正常运行
select p.product_type_id,pt.product_type_id,
       p.name,
       pt.name
from products p,product_types pt
where p.product_type_id(+) = pt.product_type_id
      and p.product_type_id(+) in (1,2,3,4);


--笛卡尔积
select p.product_type_id,
       pt.product_type_id
from products p,product_types pt


--自连接，也就是一个表和自己关联
--下面的语句只会出现 有上级的人员
select e.first_name || ' ' || e.last_name || ' works for ' || 
       m.first_name || ' ' || m.last_name
from employees e,employees m
where e.manager_id = m.employee_id
order by e.first_name;

--显示所有的人员，不管是否有上级
--从下面的语句可以看出，在oracle中字符串与空值连接，还是会返回字符串
select e.first_name || ' ' || e.last_name || ' works for ' || 
       m.first_name || ' ' || m.last_name,
       
       e.first_name || ' ' || e.last_name || ' works for ' || 
       nvl(m.last_name,'the shareholders')      
from employees e,employees m
where e.manager_id = m.employee_id（+）
order by e.first_name;


--9.2 适用于Oracle Database 9i及更高版本，可以使用SQL/92标准语法。
--如果关联字段的名称是相同的，可以通过using关键字来简化关联条件
--内连接
select p.name,
       pt.name
from products p
inner join product_types pt
        on p.product_type_id = pt.product_type_id
order by p.name

--通过using来简化
select p.name,
       pt.name
from products p
inner join product_types pt
using(product_type_id)
order by p.name

select p.name,
       pt.name,
       p.product_type_id    --SQL 错误: ORA-25154: USING 子句的列部分不能有限定词
from products p
inner join product_types pt
using(product_type_id)
order by p.name


select p.name,
       pt.name,
       product_type_id
from products p
inner join product_types pt
using(p.product_type_id)     --SQL 错误: ORA-01748: 此处只允许简单的列名
order by p.name


--左外连接
--以products为主表，会返回products所有的记录，包括product_type_id为null的记录
--以product_types为辅表.
select p.name,
       pt.name
from products p
left join product_types pt
       on p.product_type_id = pt.product_type_id

--右外连接
--以product_types为主表，会返回所有产品类型，
--以products为辅表
select p.name,
       pt.name
from products p
right join product_types pt
        on  p.product_type_id = pt.product_type_id


--全外连接
select p.name,
       pt.name
from products p
full join product_types pt
       on p.product_type_id = pt.product_type_id


--笛卡尔积
select p.product_type_id,
       pt.product_type_id
from products p
cross join product_types pt


--自连接，也就是一个表和自己关联
--下面的语句只会出现 有上级的人员
select e.first_name || ' ' || e.last_name || ' works for ' || 
       m.first_name || ' ' || m.last_name
from employees e
inner join employees m
        on e.manager_id = m.employee_id
order by e.first_name;


--显示所有的人员，不管是否有上级
--从下面的语句可以看出，在oracle中字符串与空值连接，还是会返回字符串
select e.first_name || ' ' || e.last_name || ' works for ' || 
       m.first_name || ' ' || m.last_name,
       
       e.first_name || ' ' || e.last_name || ' works for ' || 
       nvl(m.last_name,'the shareholders')      
from employees e
left  join employees m
        on e.manager_id = m.employee_id
order by e.first_name;