求极值
select min(sdate) from salegoods;
上面的语法和下面的语句得出的效果是一样的,不过下面的语句执行更快,并且效果一样
select * from salegoods;
1,排序
下面两条语句等价
select customer_num,order_num,po_num,order_date from orders order by 4,1;
select customer_num,order_num,po_num,order_date from orders order by order_date,customer_num;
部分列降序排序
select customer_num,order_num,po_num,order_date from orders order by 4 desc,1;
选择子串
select zipcode[1,3],customer_num from customer order by zipcode;
2,选择特定的行
包含行
select customer_num,call_code,call_dtime,res_dtime from cust_calls where user_id='maryj';
排除行
select customer_num,company,city,state from odin.customer where state != 'CA'
select customer_num,company,city,state from odin.customer where state <> 'CA'
指定一定范围内的行
select catalog_num,stock_num,manu_code,cat_advert from catalog where catalog_num between 10005 and 10008;
select catalog_num,stock_num,manu_code,cat_advert from catalog where catalog_num>= 10005 and catalog_num <= 10008;
排除一定范围内的行
select fname,lname,city,state from customer where zipcode not between '94000' and '94999' ordey by state;
使用where 子句来查找值的子集
select lname,city,state,phone from 'Aleta'.customer where state = 'AZ' OR state = 'NJ' order by lname;
select lname,city,state,phone from 'Aleta'.customer where state in ('AZ','NJ') order by lname;
使用not in 语句
select lanem,city,state,phone from ALeta.customer where state not in ('AZ','NJ') order by state;
表示null 值
select order_num,customer_num,po_num,ship_date from orders where paid_date is null order by customer_num;
使用and,or和not构造复合条件
select order_num,customer_num,po_num,ship_date from orders where paid_date is null and ship_date is not null order by customer_num;
like
select stock_num,manu_code,description,unit_price from stock where description like 'bicycle%'
and manu_code not like 'prc' order by description,manu_code;
在where 子句中使用下标
select catalog_num,stock_num,manu_code,cat_advert, cat_descr from catalog where cat_advert[1,4] = 'high';
使用first子句来选择特定的行
使用first子句来返回state表中的前5行。
select first 5 * from salegoods;
select first 5 * from salegoods order by sdate;(执行效果极其差)
算术运算符
select sdate,shopid,price*1.07 from salegoods;
select customer_num,call_code,call_dtime,res_dtime-call_dtime from cust_calls order by customer_num;
使用显示标号
select sdate,shopid,price*1.07 price2 from salegoods;
case表达式
select order_num,order_date,
case
when ship_date is null
then "order not shipped"
end
from orders
对派生列进行排序
select customer_num,call_code,call_dtime,res_dtime-call_dtime span from cust_calls order by span;
在select 语句中使用rowid,下面语句查询各行的rowid
select rowid,* from manufact;
多个表的select语句。
1,创建笛卡儿积
select * from customer,state;(不实用,没有意义)
2,创建连接
跨连接(IDS)
select * from customer cross join state
等值连接
select * from manufact,stock where manufact.manu_code = stock.manu_code
select order_num,order_date,ship_date,cust_calls.* from orders,cust_calls where call_dtime >= ship_date
and cust_calls.customer_num = orders.customer_num order by orders.customer_num;
使用别名
select s.stock_num,s.manu_code,s.description,s.unit_price,c.catalog_num,c.cat_advert,m.lead_time
from stock s,catalog c,manufact m
where s.stock_num = c.stock_num
and s.manu_code = c.manu_code
and s.manu_code = m.manu_code
and s.manu_code in ('hro','hsk')
and s.stock_num between 100 and 301
order by catalog_num;
select order_num, lname,fname,phone
from masterdb@central:customer c,sales@wester:orders o
where c.customer_num = o.customer_num
and order_num <= 1010
into temp 子句
select distinct stock_num,manu_name,description,unit_price,unit_price*1.05 adj_price from stock,manufact
where manufact.manu_code = stock.manu_code
into temp stockman;
select * from stockman;
第3章,从复杂类型选择数据
创建row类型和表的sql语法
create row type zip_t
(
z_code char(5),
z_suffix char(4)
)
create row type address_t
(
street varchar(20),
city varchar(20),
state char(2),
zip zip_t
)
create row type employee_t
(
name VARCHAR(30),
address address_t,
salary INTEGER
)
create table employee of type employee_t
create table student
(
s_name varchar(30),
s_address ROW(street varchar(20),city varchar(20),
state char(2),zip varchar(9)),
grade_point_avg decimal(3,2)
)
选择包含行类型数据的列
select address.city,address,state from employee;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/312079/viewspace-245651/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/312079/viewspace-245651/