oracle数据库很多操作和mysql差不多，oracle是最早使用sql查询语言的数据库，有mysql开发经验的开发人员上手oracle还是很快的。
1.首先oracl运算符相关的计算和mysql一样,举例，查询时间sysdate,运算符，别名
SQL> select sysdate ,sysdate+1,sysdate-1 , 8+3*2 as dengyu from dual;
SYSDATE SYSDATE+1 SYSDATE-1 DENGYU
2018-03-18 2018-03-19 2018-03-17 14
2.连接符，用 ‘||’表示：把列与列，列与字符连接在一起（注意别名用“”则区分大小写）
select last_name || first_name as "NaMe" from employees ;
NaMe---------------------------------------------KingStevenKochharNeenaDe
HaanLexHunoldAlexanderErnstBruce------------------------------------------;
3 FROM employees;
Employee Details-----------------------------------------King is a AD_PRESKochhar is a AD_VPDe Haan is a AD_VPHunold is a IT_PROGErnst is a IT_PROGAustin is a IT_PROGPataballa is a IT_PROG
select table_name from dba_tables
3.同样distinct的使用方式和mysql中一样，都是去重。select distinct(name) from a.同样desc A查看表结构
4. 查询数据库中所有表.
select table_name from dba_tables where owner='orcl001';
select table_name from dba_tables
5.其余between ..and ,in ，not in ,and ,or,is null ,like等使用和mysql一致。
SQL> SELECT last_name, salary 2 FROM employees 3 WHERE salary BETWEEN 2500 AND 3500;LAST_NAME SALARY------------------------- ----------Khoo 3100.00Baida 2900.00Tobias 2800.00Himuro 2600.00Colmenares 2500.00 SQL> SELECT employee_id, last_name, salary, manager_id 2 FROM employees 3 WHERE manager_id IN (100, 101, 201);EMPLOYEE_ID LAST_NAME SALARY MANAGER_ID----------- ------------------------- ---------- ---------- 101 Kochhar 17000.00 100 102 De Haan 17000.00 100 108 Greenberg 12000.00 101 114 Raphaely 11000.00 100 120 Weiss 8000.00 100 121 Fripp 8200.00 100 SQL> SELECT first_name 2 FROM employees 3 WHERE first_name LIKE 'S%';FIRST_NAME--------------------StevenShelliSigalShantaStevenStephen 回避特殊符号的：使用转义符。例如：将[%]转为[\%]、[_]转为[\_]，然后再加上[ESCAPE ‘\’] 即可
6.order by 排序和mysql使用一样，asc 升序，desc降序
SQL> SELECT last_name, job_id, department_id, hire_date 2 FROM employees 3 ORDER BY hire_date ;LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE------------------------- ---------- ------------- -----------King AD_PRES 90 1987-06-17Whalen AD_ASST 10 1987-09-17Kochhar AD_VP 90 1989-09-21Hunold IT_PROG 60 1990-01-03Ernst IT_PROG 60 1991-05-21De Haan AD_VP 90 1993-01-13
7.其余的oracle函数，单行函数，多行函数和mysql使用一致
SQL> SELECT last_name, job_id, salary, 2 CASE job_id WHEN 'IT_PROG' THEN 1.10*salary 3 WHEN 'ST_CLERK' THEN 1.15*salary 4 WHEN 'SA_REP' THEN 1.20*salary 5 ELSE salary END "REVISED_SALARY" 6 FROM employees;LAST_NAME JOB_ID SALARY REVISED_SALARY------------------------- ---------- ---------- --------------King AD_PRES 24000.00 24000Kochhar AD_VP 17000.00 17000De Haan AD_VP 17000.00 17000Hunold IT_PROG 9000.00 9900Ernst IT_PROG 6000.00 6600Austin IT_PROG 4800.00 5280Pataballa IT_PROG 4800.00 5280Lorentz IT_PROG 4200.00 4620Greenberg FI_MGR 12000.00 12000............................. 需要使用if-elseif-else格式时，可以使用deocode替换。DECODE(col|expression, search1, result1 , [, search2, result2,...,] [, default])SQL> SELECT last_name, job_id, salary, 2 DECODE(job_id, 'IT_PROG', 1.10*salary, 3 'ST_CLERK', 1.15*salary, 4 'SA_REP', 1.20*salary, 5 salary) 6 REVISED_SALARY 7 FROM employees;LAST_NAME JOB_ID SALARY REVISED_SALARY------------------------- ---------- ---------- --------------King AD_PRES 24000.00 24000Kochhar AD_VP 17000.00 17000De Haan AD_VP 17000.00 17000Hunold IT_PROG 9000.00 9900Ernst IT_PROG 6000.00 6600Austin IT_PROG 4800.00 5280Pataballa IT_PROG 4800.00 5280Lorentz IT_PROG 4200.00 4620Greenberg FI_MGR 12000.00 12000Faviet FI_ACCOUNT 9000.00 9000...............................
8.oracle的join系列和mysql基本一样。
网友评论