oracle学习笔记.docVIP

//查询数学分高于六十的学生信息 select ,a2.class from (select * from grade where mark60) a1,stu as a2,course a3 where a1.stu_id=a2.id and a1.coru_id=a3.cid and a3.cid=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,lname,company From customer Where order_date between ‘05/01/94’ and ‘05/31/94’ In范围查询or Select customer_num,fname,lname,company From customer Where customer_num in (110,150,111,118) Mathches 和like Select customer_num,company From customer Where company 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 customer_num,company,lname,fname From state,customer Where code = state andsname = ‘California’ Select item_num From items,stock Where total_price = unit_price*150 自连接 Select c1.customer_num,pany,c1.state From customer c1, customer c2 Where c1.state = c2.state And c2.customer_num = 101 自连接与into temp Select x.order_num orders1,x.ship_date ship1, y.order_num orders2,y.ship_date ship2 from orders x, orders y where x.ship_weight=5*y.ship_weight and x.ship_date is not null and y.ship_date is not nul