1.基本select（笔记）

求极值
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/