30分钟入门Oracle sql语句

发布时间：2020-12-12 15:49:31 所属栏目：百科来源：网络整理

导读：SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; SELECT * FROM departments t; SELECT department_id,location_id FROM departments; SELECT last_name,salary,salary + 300 FROM employees; SELECT last_name,12*salary+100 FROM employ

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;

SELECT * FROM departments t;

SELECT department_id,location_id FROM departments;

SELECT last_name,salary,salary + 300 FROM employees;

SELECT last_name,12*salary+100 FROM employees;

SELECT last_name,12*(salary+100) FROM employees;

SELECT last_name,job_id,commission_pct FROM employees;

SELECT last_name,12*salary*commission_pct FROM employees;

SELECT last_name AS name,commission_pct comm FROM employees;

SELECT last_name "Name",salary*12 "Annual Salary" FROM employees;

SELECT last_name||job_id AS "Employees" FROM employees;

SELECT last_name ||' is a '||job_id

AS "Employee Details"

FROM employees;

SELECT department_name ||

q'[,it's assigned Manager Id: ]'

|| manager_id

AS "Department and Manager"

FROM departments;

SELECT department_id

FROM employees;

SELECT DISTINCT department_id

FROM employees;

DESC[RIBE] tablename

DESC employees

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table

[WHERE condition(s)];

SELECT employee_id,last_name,department_id

FROM employees

WHERE department_id = 90 ;

SELECT last_name,department_id

FROM employees

WHERE last_name = 'Whalen' ;

SELECT last_name,salary

FROM employees

WHERE salary BETWEEN 2500 AND 3500 ;

SELECT employee_id,manager_id

FROM employees

WHERE manager_id IN (100,101,201) ;

SELECT first_name

FROM employees

WHERE first_name LIKE 'S%' ;

SELECT last_name

FROM employees

WHERE last_name LIKE '_o%' ;

SELECT last_name,manager_id

FROM employees

WHERE manager_id IS NULL ;

SELECT employee_id,salary

FROM employees

WHERE salary >=10000

AND job_id LIKE '%MAN%' ;

SELECT employee_id,salary

FROM employees

WHERE salary >= 10000

OR job_id LIKE '%MAN%' ;

SELECT last_name,job_id

FROM employees

WHERE job_id

NOT IN ('IT_PROG','ST_CLERK','SA_REP') ;

SELECT last_name,department_id,hire_date

FROM employees

ORDER BY hire_date ;

SELECT last_name,hire_date

FROM employees

ORDER BY hire_date DESC ;

SELECT employee_id,salary*12 annsal

FROM employees

ORDER BY annsal ;

SELECT last_name,salary

FROM employees

ORDER BY department_id,salary DESC;

SELECT employee_id,department_id

FROM employees

WHERE employee_id = &employee_num ;

SELECT last_name,salary*12

FROM employees

WHERE job_id = '&job_title' ;

SELECT employee_id,&column_name

FROM employees

WHERE &condition

ORDER BY &order_column ;

SELECT employee_id,&&column_name

FROM employees

ORDER BY &column_name ;

DEFINE employee_num = 200

SELECT employee_id,department_id

FROM employees

WHERE employee_id = &employee_num ;

UNDEFINE employee_num

SET VERIFY ON

SELECT employee_id,department_id

FROM employees

WHERE employee_id = &employee_num;

LOWER LOWER('SQL Course')

UPPER UPPER('SQL Course')

INITCAP INITCAP('SQL Course')

CONCAT CONCAT('Hello','World')

SUBSTR SUBSTR('HelloWorld',1,5)

LENGTH LENGTH('HelloWorld')

INSTR INSTR('HelloWorld','W')

LPAD | RPAD LPAD(salary,10,'*')

RPAD(salary,'*')

TRIM TRIM('H' FROM 'HelloWorld')

REPLACE REPLACE('JACK and JUE','J','BL')

SELECT employee_id,department_id

FROM employees

WHERE last_name = 'higgins';

no rows selected

SELECT employee_id,department_id

FROM employees

WHERE LOWER(last_name) = 'higgins';

SELECT employee_id,CONCAT(first_name,last_name) NAME,

job_id,LENGTH (last_name),

INSTR(last_name,'a') "Contains 'a'?"

FROM employees

WHERE SUBSTR(job_id,4) = 'REP';

ROUND(45.926,2)

TRUNC(45.926,2)

MOD(1600,300)

SELECT ROUND(45.923,2),ROUND(45.923,0),

ROUND(45.923,-1)

FROM DUAL;

SELECT ROUND(45.923,ROUND(45.923),-1)

FROM DUAL;

SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKS

FROM employees

WHERE department_id = 90;

MONTHS_BETWEEN

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

ADD_MONTHS ADD_MONTHS ('11-JAN-94',6)

NEXT_DAY NEXT_DAY ('01-SEP-95','FRIDAY')

LAST_DAY LAST_DAY ('01-FEB-95')

ROUND

TRUNC

ROUND(SYSDATE,'MONTH')

ROUND(SYSDATE,'YEAR')

TRUNC(SYSDATE,'MONTH')

TRUNC(SYSDATE,'YEAR')

VARCHAR2 or CHAR NUMBER

VARCHAR2 or CHAR DATE

NUMBER VARCHAR2

DATE VARCHAR2

SELECT last_name,

TO_CHAR(hire_date,'fmDD Month YYYY')

AS HIREDATE

FROM employees;

SELECT TO_CHAR(salary,'$99,999.000') SALARY

FROM employees

WHERE last_name = 'Ernst';

SELECT last_name,

UPPER(CONCAT(SUBSTR (LAST_NAME,8),'_US'))

FROM employees

WHERE department_id = 60;

NVL (expr1,expr2)

NVL2 (expr1,expr2,expr3)

NULLIF (expr1,expr2)

COALESCE (expr1,...,exprn)

NVL(commission_pct,0)

NVL(hire_date,'01-JAN-97')

NVL(job_id,'No Job Yet')

SELECT last_name,NVL(commission_pct,

(salary*12) + (salary*12*NVL(commission_pct,0)) AN_SAL

FROM employees;

SELECT last_name,commission_pct,

NVL2(commission_pct,

'SAL+COMM','SAL') income

FROM employees WHERE department_id IN (50,80);

SELECT first_name,LENGTH(first_name) "expr1",

last_name,LENGTH(last_name) "expr2",

NULLIF(LENGTH(first_name),LENGTH(last_name)) result

FROM employees;

SELECT last_name,

COALESCE(manager_id,-1) comm

FROM employees

ORDER BY commission_pct;

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

SELECT last_name,

CASE job_id WHEN 'IT_PROG' THEN 1.10*salary

WHEN 'ST_CLERK' THEN 1.15*salary

WHEN 'SA_REP' THEN 1.20*salary

ELSE salary END "REVISED_SALARY"

FROM employees;

DECODE(col|expression,search1,result1

[,search2,result2,]

[,default])

SELECT last_name,

DECODE(job_id,'IT_PROG',1.10*salary,

'ST_CLERK',1.15*salary,

'SA_REP',1.20*salary,

salary)

REVISED_SALARY

FROM employees;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!