select distinct vend_id, prod_price from Products;
除非指定的列vend_id和prod_price完全相同,否则所有的行都会被检索出来。
select prod_name from Products where ROWNUM <= 5;
限制返回前5行。
select prod_name from Products order by prod_name,vend_id;
order by子句必须是select语句的最后一条子句,否则会报错。order by 可以选择一到多个列排序,排序的顺序按order by 后面的顺序进行,排序的列可以不是要检索的列。
select prod_id, prod_price, prod_name from Products order by 2, 3;
按位置顺序排序。先按prod_price(2)排序,再按prod_name(3)排序。
select prod_id, prod_price, prod_name from Products order by prod_price desc, prod_name;
默认为升序排序(ASC),使用desc来进行降序排序。
select prod_price, prod_name from Products where prod_price >= 3.49;
select prod_name, prod_price from Products where prod_price between 5 and 10;
select prod_id, prod_price, prod_name from Products where vend_id='DLL01' and prod_price<=4;
select prod_name, prod_price from Products where vend_id='DLL01' OR vend_id='BRS01';
利用where子句进行数据过滤。OR操作符只要满足第一个条件就不再计算第二个条件了。
select prod_name, prod_price from Products where vend_id='DLL01' OR vend_id='BRS01' and prod_price>=10;
select prod_name, prod_price from Products where (vend_id='DLL01' OR vend_id='BRS01') and prod_price>=10;
上述两条SQL的执行意思不同,因为and的执行优先级比or高,所以vend_id='BRS01' and prod_price>=10会作为一个单独条件,于是要利用圆括号来进行明确分组。
select prod_name, prod_price from Products where vend_id in ('DLL01','BRS01') order by prod_name;
in功能与or相当,性能比or好,可以在in中包含其他select语句。
select prod_name from Products where NOT vend_id='DLL01' order by prod_name;
not用来否定其后所跟的任何条件。
select prod_id, prod_name, from Products where prod_name like 'Fish%';--匹配以Fish开头的prod_name的行
select prod_id, prod_name, from Products where prod_name like '%Fish';--匹配以Fish结尾的prod_name的行
select prod_id, prod_name, from Products where prod_name like '%Fish%';--匹配包含Fish的prod_name的行
select prod_id, prod_name from Products where prod_name like '_ inch teddy bear';
通配符%可以匹配多个字符,而一个通配符_匹配一个字符。
通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。
使用通配符尽量不要将它们用在搜索模式的开始处。
select vend_name || '(' vend_country || ')' from Vendors order by vend_name;
使用拼接符将值联结到一起(将一个值附加到另一个值)构成单个值。
函数RTRIM()会去掉值右边的所有空格。(LTRIM()去电字符串左边的空格,TRIM()去掉字符串左右两边的空格)
select RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')' as vend_title from Vendors order by vend_name;
使用as为新列赋予别名。(一个未命名的列不能用于客户端应用中,因为客户端没法引用它)
别名的用途:在实际的表列名包含不合法的字符(如空格)时重新命名它,在原来的名字含混会容易误解时扩充它。
select prod_id, quantity, item_price, quantity*item_price as expanded from OrderItems where order_num=20008;
在select语句中使用算数操作符。
select vend_name, UPPER(vend_name) as vend_name_upcase from Vendors order by vend_name;
使用函数。
字符串处理函数:
select order_num from Orders where to_number(to_char(order_date, 'YYYY'))=2012;
select order_num from Orders where order_date between to_date('01-01-2012') and to_date('12-31-2012');
to_char()函数用来提取日期的成分,to_number()用来将提取出来的成分转换为数值。
to_date()函数用来将两个字符串转换为日期。
数值处理函数:
select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_nax, avg(prod_price) as price_avg fron Products;
select AVG(distinct prod_price) as avg_price from Products where vend_id='DLL01';
利用distinct关键字,计算不同价格的平均值。
select vend_id, count(*) as num_prods from Products group by count(*);
select cust_id, count(*) as orders from Orders group by cust_id having count(*) >=2;
分组。group by子句可以包含任意数目的列,如果分组列中包含NULL的行,则NULL将作为一个分组返回。group by子句必须出现在where子句之后,order by子句之前。
子查询:
select cust_name,cust_contact
from customers
where cust_id in (select cust_id
from orders
where order_num in (select order_num
from orderitems
where prod_id='RGANO1'));
作为子查询的select语句智能查询单个列。
select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as orders from customers order by cust_name;
联结:
等值联结(内联结):
select vend_name,prod_name,prod_price from venders,products where venders.vend_id=products.vend_id;
如果没有where子句,第一个表的每一行将与第二个表中的每一行配对,检索出来的行数是第一个表中的行数乘以第二个表的行数。
select vend_name,prod_name,prod_price from venders inner join products on venders.vend_id=products.vend_id;
联结多个表:
select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id=orders.cust_id and orderitems.order_num=orders.order_num and prod_id='RGANO1';
联结的表越多,性能下降越厉害。
组合查询:
select cust_name,cust_contact,cust_email
from customers
where cust_state in ('IL','IN','MI')
UNION
select cust_name,cust_contact,cust_email
from customers
where cust_name='fun4all';
union取两个结果的合集(重复行会被取消,使用union all则返回所有匹配行)作为查询结果集。union中的每个查询必须包含相同的列,表达式或聚集函数,列的顺序不一定要相同,但列数据的类型必须兼容。
从一个表复制到另一个表:
select * into custcopy from customers;
更新多个列的值:
update customers
set cust_contact='Sam Roberts',
cust_email='sam@toyland.com'
where cust_id='10086';