oracle SQL集锦

下面的语句都是本人整理出的,并且都在oracle XE上测试通过。下边的函数都没有做说明,相信大家都知道怎么用了,呵呵。

1  普通的查询语句,例如:
     
SELECT last_name, department_id FROM employees;
2   查询视图
     
SELECT employee_id, last_name, job_title, department_name, country_name, 
       region_name 
FROM emp_details_view;
3   更换列名查询
     
SELECT employee_id "Employee ID number", last_name "Employee last name", 
             first_name "Employee first name" 
FROM employees;
4   联接查询
     自然联接:要求两个表中的公共列必须有相同的名称和结构类型,否则报错。
     
SELECT employee_id, last_name, first_name, department_id, 
             department_name, manager_id  
FROM employees  NATURAL JOIN departments;
     查询三张或以上表时用USING,前提是用来连接两张表的列必须名称相同。
     
SELECT e.employee_id, e.last_name, e.first_name, e.manager_id, department_id, 
             d.department_name, d.manager_id 
FROM employees e 
             
JOIN departments d USING (department_id);
     联接时加上WHERE 子句。
  
SELECT e.employee_id, e.last_name, e.first_name, e.department_id,
             d.department_name, d.manager_id, d.location_id, l.country_id 
FROM employees e
             
JOIN departments d ON e.department_id = d.department_id
             
JOIN locations l ON d.location_id = l.location_id
             
WHERE l.location_id = 1700;
     自我联接:
     
SELECT e.employee_id emp_id, e.last_name emp_lastname, m.employee_id mgr_id,
             m.last_name mgr_lastname
             
FROM employees e 
             
JOIN employees m ON e.manager_id = m.employee_id;
      
LEFT OUTER JOIN:左表中所有的记录即使在右表中没有联接的都会被查询出来。
     
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
             
FROM employees e LEFT OUTER JOIN departments d
             
ON (e.department_id = d.department_id);
     
RIGHT OUTER JOIN:右表中所有的记录即使在左表中没有联接的都会被查询出来。
     
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
            
FROM employees e RIGHT OUTER JOIN departments d 
            
ON (e.department_id = d.department_id);
     
FULL OUTER JOIN:左右表中的记录全部被查询出来。
     
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
            
FROM employees e FULL OUTER JOIN departments d
            
ON (e.department_id = d.department_id);

5    绑定变量查询:Oracle将已解析、已编译的SQL连同其他内容存储在共享池中,这是SGA中一个非常重要的存储结构(内存结构主要分为SGA和PGA)。而绑定变量查询在Oracle里执行时只编译一次,随后就会把这个查询计划存储在一个共享池中以便重用,即所谓的软解析。具体的使用方法如下:
        
SELECT * FROM employees WHERE employee_id = :employee_id
6      查询虚拟列:所谓的虚拟列在Oracle里类似表格的列,但并非存储在表中。查询虚拟列时将返回一个值,因此它又类似与函数。Oracle里的虚拟列有:ROWNUM, SYSDATE, and USER。eg:
        
SELECT SYSDATE "NOW" FROM DUAL;
        
SELECT USER FROM DUAL;
        
SELECT employee_id, hire_date, SYSDATE FROM employees WHERE ROWNUM < 10;
7    带函数查询:
      Using Numeric Functions:
        
SELECT employee_id, ROUND(salary/302) "Salary per dayFROM employees;
        
SELECT employee_id, TRUNC(salary/300) "Salary per dayFROM employees;
        
SELECT employee_id, MOD(employee_id, 2FROM employees;
      Using 
Character Functions:
        
SELECT employee_id, UPPER(last_name), LOWER(first_name) FROM employees;
        
SELECT employee_id, INITCAP(first_name), INITCAP(last_name) FROM employees;
        
SELECT employee_id, RTRIM(first_name) || ' ' || LTRIM(last_name) FROM employees;
        
SELECT employee_id, TRIM(last_name) || '' || TRIM(first_name) FROM employees;
        
SELECT employee_id, RPAD(last_name, 30' '), first_name FROM employees;
        
SELECT employee_id, SUBSTR(last_name, 110FROM employees;
        
SELECT LENGTH(last_name) FROM employees;
        
SELECT employee_id, REPLACE(job_id, 'SH''SHIPPING'FROM employees
                    
WHERE SUBSTR(job_id, 12= 'SH';
        Using Date Functions:
        
SELECT employee_id, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "Months Employed" 
                     
FROM employees;
        
SELECT employee_id, EXTRACT(YEAR FROM hire_date) "Year Hired" FROM employees;
        
SELECT EXTRACT(YEAR FROM SYSDATE) || EXTRACT(MONTH FROM SYSDATE) || 
                   EXTRACT(
DAY FROM SYSDATE) "Current Date" FROM DUAL;
        
SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 3FROM employees;
        
SELECT employee_id, hire_date, LAST_DAY(hire_date) "Last day of month
                   
FROM employees;
        
SELECT SYSTIMESTAMP FROM DUAL;
      Using Conversion Functions:
        
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY AD') "Today" FROM DUAL;
        
SELECT TO_CHAR(SYSDATE, 'FMMonth DD YYYY') "Today" FROM DUAL;
        
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Now" FROM DUAL;
        
SELECT hire_date, TO_CHAR(hire_date,'DS') "Short Date" FROM employees;
        
SELECT hire_date, TO_CHAR(hire_date,'DL') "Long Date" FROM employees;
        
SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) || 
                      TO_CHAR(EXTRACT(
MONTH FROM SYSDATE),'FM09'|| 
                      TO_CHAR(EXTRACT(
DAY FROM SYSDATE),'FM09') "Current Date" FROM DUAL;
        
SELECT TO_CHAR(CURRENT_DATE'DD-MON-YYYY HH24:MI:SS') "Current Date" FROM DUAL;
        
SELECT TO_CHAR(salary,'$99,999.99') salary FROM employees;
        
SELECT TO_NUMBER('1234.99'+ 500 FROM DUAL;
        
SELECT TO_NUMBER('11,200.34''99G999D99'+ 1000 FROM DUAL;
        
SELECT TO_DATE('27-OCT-98''DD-MON-RR'FROM DUAL;
        
SELECT TO_DATE('28-Nov-05 14:10:10''DD-Mon-YY HH24:MI:SS'FROM DUAL;
        
SELECT TO_DATE('January 15, 2006, 12:00 A.M.''Month dd, YYYY, HH:MI A.M.')
                     
FROM DUAL;
        
SELECT TO_TIMESTAMP('10-Sep-05 14:10:10.123000''DD-Mon-RR HH24:MI:SS.FF')
                     
FROM DUAL;
        Using Aggregate Functions:
      
SELECT COUNT(*) "Employee CountFROM employees WHERE manager_id = 122;
        
SELECT COUNT(*) "Employee Count", manager_id  FROM employees 
                    
GROUP BY manager_id  ORDER BY manager_id
        
SELECT COUNT(commission_pct) FROM employees;
        
SELECT COUNT(DISTINCT department_id) FROM employees;
        
SELECT MIN(salary), MAX(salary), AVG(salary), job_id FROM employees 
                    
GROUP BY job_id  ORDER BY job_id;
        
SELECT RANK(2600) WITHIN GROUP
                  (
ORDER BY salary DESC) "Rank of $2,600 among clerks"
                   
FROM employees WHERE job_id LIKE '%CLERK';
        
SELECT job_id, employee_id, last_name, salary, DENSE_RANK() OVER
                  (PARTITION 
BY job_id ORDER BY salary DESC) "Salary Rank (Dense)"
                   
FROM employees WHERE job_id = 'SH_CLERK';
        
SELECT employee_id, salary, hire_date, STDDEV(salary) 
                   
OVER (ORDER BY hire_date) "Std Deviation of Salary"
                   
FROM employees WHERE job_id = 'ST_CLERK';
        Using 
NULL Value Functions:
        
SELECT commission_pct, NVL(commission_pct, 0FROM employees;
        
SELECT phone_number, NVL(phone_number, 'MISSING'FROM employees;
        
SELECT employee_id , last_name,commission_pct, salary, 
                    NVL2(commission_pct, salary 
+ (salary * commission_pct), salary) income
                    
FROM employees;
         Using Conditional Functions:
         
SELECT employee_id, hire_date , salary,
                    
CASE WHEN hire_date < TO_DATE('01-JAN-90'THEN salary*1.20
             
WHEN hire_date < TO_DATE('01-JAN-92'THEN salary*1.15
             
WHEN hire_date < TO_DATE('01-JAN-94'THEN salary*1.10 
             
ELSE salary*1.05 END  "Revised Salary"
                    
FROM employees;
         
SELECT employee_id, job_id , salary,
                    DECODE(job_id, 
'PU_CLERK', salary*1.05,
                 
'SH_CLERK', salary*1.10,
                 
'ST_CLERK', salary*1.15,
                             salary) "Revised Salary"
                    
FROM employees;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值