Oracle数据库学习 （3）Oracle基本语法与常用函数

一：dual表的介绍：

概述：dual表是一个单行单列的虚拟表，是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中。

查看dual表的结构如下：select * from dual;

查询得知dual是一个一行一列的表，数据类型为VERCHAR2(1)，dual表中只有一个数据'X', Oracle有内部逻辑保证dual表中永远只有一条数据。

 

dual表的常用用途：

1、查看当前连接用户：select user from dual;

2、查看当前日期、时间:

（1）查看默认时间格式：SELECT SYSDATE FROM dual;

（2）查看指定日期格式：SELECT TO_CHAR(SYSDATE,'yyyy"年"MM"月"dd"日" hh24:mi:ss') AS "当前时间" FROM dual;

 

3、用作计算器：select 7*7 as "计算结果" from dual;

 

二：Oracle的运算符

1、比较运算符

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于（也可以是!=）

 

2、其他运算符

操作符	含义
BETWEEN...AND	在两个值之间，包括边界值
IN()	等于()里其中的一个值
LIKE	模糊查询
IS NULL	空值
IS NOT NULL	非空值
ESCAPE	定义转义字符，如模糊查询里，(1)：定义"_"为自身字符"_"可以写成：like 'IT\_%' escape '\' （2）：定义"%"为自身字符"%"可以写成：like '%IT\%%' escape '\'

 

3、逻辑运算符

操作符	含义
AND	逻辑与
OR	逻辑或
NOT	逻辑否

 

4、ORDER BY :默认按照从小到大排序

ORDER BY [字段名] ASC：从小到大

ORDER BY [字段名] DESC：从大到小

ORDER BY [字段名1] [字段名2] DESC：按照多个列排序

 

运算符章节的实例：

1、查询 last_name 为 'King' 的员工信息：select first_name, last_name from employees where last_name ='King'

2、查询工资在 5000 -- 10000 之间的员工信息：select * from employees where salary between 5000 and 10000;

3、查询工资等于 6000, 7000, 8000, 9000, 10000 的员工信息：select * from employees where salary in (6000, 7000, 8000, 9000, 10000)

4、查询 LAST_NAME 中有 'o' 字符的所有员工信息.：select * from employees where last_name like '%o%'

5、查询 LAST_NAME 中第二个字符是 'o' 的所有员工信息.：select * from employees where last_name like '_o%'；

6、查询 LAST_NAME 中含有 '_' 字符的所有员工信息：（1）先将其中一个员工改成含有_字符：update employees set last_name = 'Jones_Tom'  where employee_id = 195

（2）执行查询语句：select * from employees where last_name like '%\_%' escape '\';

7、查询 COMMISSION_PCT 字段为空的所有员工信息：select last_name, commission_pct from employees where commission_pct is null;

8、查询 COMMISSION_PCT 字段不为空的所有员工信息：select last_name, commission_pct from employees where commission_pct is not null;

 

 

三：单行函数

1、大小写控制函数

（1）LOWER()，会将字符串转为小写：SELECT LOWER('ORACLE Study') FROM dual;

 

（2）UPPER() ，会将字符串转为大写：SELECT UPPER('ORACLE Study') FROM dual;

（3）INITCAP()，会将每个字符串首字母变为大写：SELECT INITCAP('ORACLE Study') FROM dual;

 

2、字符控制函数

（1）CONCAT()，连接字符串函数：SELECT CONCAT('Hello', 'World') FROM dual;

（2）SUBSTR()，截取字符串函数：SELECT SUBSTR('Hello World', 1, 5) FROM dual;

（3）LENGTH()，获取字符串长度函数：SELECT LENGTH('Hello World') FROM dual;

（4）INSTR()，获取指定字符串的位置：SELECT INSTR('Hello World','W') FROM dual;

（5）LPAD()，向左补齐指定字符：SELECT LPAD('salary',10,'*') FROM dual;

（6）RPAD()，向右补齐指定字符：SELECT RPAD('salary',10,'*') FROM dual;

（8）REPLACE()，替换字符串：SELECT REPLACE('abbbcd', 'b', 'x') FROM dual;

 

3、数字函数

（1）ROUND()：四舍五入函数

（1.1）保持一位小数：SELECT ROUND(55.923,1) FROM dual;

（1.2）不保留小数：SELECT ROUND(55.923) FROM dual;

（1.3）拿整数位四舍五入：SELECT ROUND(55.923, -1) FROM dual;

 

（2）TRUNC()：截取函数，跟ROUND函数相比，这里不需要四舍五入，取具体的几位数

（2.1）截取两位小数：SELECT TRUNC(55.929, 2) FROM dual;

（2.2）截取整数：SELECT TRUNC(55.929) FROM dual;

（2.3）根据整数位截取：SELECT TRUNC(155.929,-2) FROM dual;

 

（3）MOD()：取余函数：SELECT MOD(100,40) FROM dual;

 

 

4、日期函数

（1）MONTHS_BETWEEN():计算两个日期相差的月数。

（2）ADD_MONTHS()：向指定日期加上若干月。SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;

（3）NEXT_DAY()：指定日期的下一个星期 * 对应的日期。SELECT NEXT_DAY(SYSDATE,3) FROM dual;

（4）LAST_DAY()：本月的最后一天。SELECT LAST_DAY(SYSDATE) FROM dual;

 

5、通用函数

（1）NVL(expr1,expr2):将expr1中的空值转换成一个expr2的值。

（1.1）如果expr1为空，就转成expr2的值：SELECT NVL(NULL, 0) FROM dual;

（1.2）如果expr2不为空，就为原来值：SELECT NVL(3, 0) FROM dual;

 

（2）NVL2(expr1,expr2,expr3):expr1不为NULL，返回expr2；为NULL，返回expr3。

（2.1）SELECT NVL2(3, 0, 1) FROM dual;

（2.2）SELECT NVL2(null, 0, 1) FROM dual;

 

3、NULLIF(expr1,expr2)：如果expr1与expr2相等就返回NULL，不等就返回expr1

（3.1）SELECT NULLIF(3, 3) FROM dual;

（3.2）SELECT NULLIF(2, 3) FROM dual;

 

4、COALESCE(expr1,expr2...exprn)：NVL2的更高级版，如果expr1为空返回expr2，如果expr2再为空返回expr3，如此类推

 

5、CASE表达式：

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

语法解析：当expr满足expr1时，返回expr1指定的值，满足expr2时返回expr2指定的值...

 

6、DECODE函数：

DECODE(col|expression, search1, result1 ,
                     [, search2, result2,...,]
                     [, default])

语法解析：当列或者表达式满足search1时，返回result1,满足search2时，返回result2...

 

 

单行函数章节的实际例子：

1、查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

select employee_id,last_name,salary,salary*1.2 "new salary" from employees;

2、将员工的姓名按首字母排序，并写出姓名的长度（length）

select last_name,length(last_name) from employees order by last_name asc;

3、查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）

select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month from employees

4、查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列

select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month from employees order by workded_month desc;

5、使用decode函数，按照兼任的职位输出对应的等级"Grade"

职位	对应等级
AD_PRES	A
ST_MAN	B
IT_PROG	C
SA_REP	D
ST_CLERK	E

select last_name "Last_name",job_id "Job_id",decode(job_id,'AD_PRES','A','ST_MAN','B', 'IT_PROG','C', 'SA_REP','D', 'ST_CLERK','E') "Grade" from employees;

6、上述的第5个实例用case函数实现：

select last_name "Last_name",job_id "Job_id",case job_id when 'AD_PRES'then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_REP' then 'D'
when 'ST_CLERK' then'E' end  "Grade"
from employees;