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;