SELECT * FROM cust WHERE manu_code LIKE '_R_'
AND unit_price >= 100
ORDER BY description, unit_price
SELECT * FROM cust WHERE manu_code MATCHES '?R?'
SELECT * FROM stock
WHERE manu_code MATCHES '[A-H]*'
SELECT * FROM stock
WHERE description LIKE 'bicycle%'
ORDER BY description, manu_code
SELECT * FROM stock
WHERE description MATCHES 'bicycle*'
ORDER BY description, manu_code
SELECT * FROM cust_calls
WHERE res_descr LIKE '%!%%' ESCAPE '!'
SELECT catalog_num, stock_num, manu_code, cat_advert,
cat_descr
FROM catalog
WHERE cat_advert[1,4] = 'High'
SELECT customer_num, user_id, call_code,
call_dtime, res_dtime - call_dtime span
FROM cust_calls
ORDER BY 5
SELECT DBSERVERNAME server, tabid FROM systables
WHERE tabid <= 4
UPDATE customer
SET (fname, lname) = ('Barnaby', 'Dorfler')
WHERE customer_num = 103
DELETE FROM stock USING stock, manufact
WHERE stock.manu_code != manufact.manu_code
in和exists
in 是把外表和内表作hash
连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。
一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in
不要在字段上用转换函数,尽量在常量上用
select id from employee where to_char(create_date,'yyyy-mm-dd')='2012-10-31' (错)
select id from employee where create_date=to_date('2012-10-31','yyyy-mm-dd') (对)