oracle学习笔记.doc

//查询数学分高于六十的学生信息
select , from (select * from grade where mark>60) a1,stu as a2,course a3
where = and = and =002
//排序
select mark from grade where stu_id = 1001 order by mark desc
select mark from grade where stu_id = 1001 order by mark asc
//取出重复的信息distinct 和unique
Select distinct customer_num
From orders
或
Select unique customer_num
From orders
30. 【】和||
Select phone[0,3]
From costomer
查询电话号码的前三位
Select ‘(‘||phone【1,3】||‘)‘||phone[5,12],company
From customer
字符串的相加
Where
Select order_num,order_date
From orders
Where order_date>’06/30/94’
Null
Select order_num,customer_num,ship_date
From orders
Where paid_date is null
Between 和and
Select customer_num,fname,pany
From customer
Where order_date between ‘05/01/94’ and ‘05/31/94’
In范围查询or
Select customer_num,fname,pany
From customer
Where customer_num in (110,150,111,118)
Mathches 和like
Select pany
From customer
pany matches ‘*sports’
Select *
From manufact
Where manu_name matches ‘[AN]*’
Select stock_num,manu_code,cat_advert
From catalog
Where cat_advert like ‘%\%%’
Escape 定义转移字符
Select stock_num,manu_code,cat_advert
From catalog
Where cat_advert like ‘%@%%’ escape ‘@’
Order by 加 asc/desc
Select manu-code,description,unut-price
From stock
Where description=’running shoes’
Order by unit-price
Select *
From stock
Order by manu-code asc,unit-price desc
Into temp
Select *
From customer
Where state=’CA’
Into temp cust_in_CA
多表查询
Select pany,lname,fname
From state,customer
Where code = state andsname = ‘California’
Select item_num
From items,stock
Where total_price >= unit_price*150
自连接
Select ,pany,
From customer c1, customer c2
Where =
And = 101
自连接与into temp
Select orders1, ship1,
orders2, ship2
from orders x, orders y
where >=5*
and i