Oracle基本命令与简单查询
1.1简单查询语句
在scott用户下有几张表，先了解下一下这几张表在作用。
1雇员表（emp）
2部门表（dept）
3工资等级表（SALGRADE）
4奖金表（BONUS）
简单查询语句语法格式：
|
SELECT *| 具体的别 别名 FROM 表名称； |
例如只查询emp表雇员的编号，姓名，工作：
|
select empno,ename,job from emp; |
查询的时候可以指定查询的返回列的名称，即为一个列取一个别名：
|
select empno 编号,ename 姓名,job 工作 from emp; |
查询工作：
|
select job from emp; |
发现存在重复值，可以通过DISTINCT消除所有的重复列：
|
SELECT {DISTINCT} *|具体的列 别名 FROM 表名称 |
在Oracle中提供字符串连接操作，使用“||”表示，所有的其他信息可以使用“’”括起来。
练习
现在要求查询雇员的编号，姓名，工作，但是显示的格式：
编号是：7369的雇员，姓名是：SMITH，工作是：CLEKE
|
SQL> select '编号是：'||empno ||'的雇员，姓名是：'||ename||'，工作是：'|| job from emp; |
在查询中可以使用四则运算功能，例如求出每个雇员的姓名及年薪。
|
SQL> select ename ,sal*12 from emp; |
1.2限定查询（WHERE）
限定查询的语法：
|
SELECT {DISTINCT} *|具体列 别名 FROM 表名称 {WHERE 条件（s）} |
例：查询出所有工资大于1500的所有雇员信息：
|
select * from emp where sal>1500; |
例：查询每个月可以得到奖金的雇员信息
为空：IS NULL;不为空：IS NOT NULL。
|
select * from emp where comm is not null; |
例：查询工资大于1500，同时可以拿到奖金的雇员信息：
两个条件同时满足，可以用“and”连接。
|
SQL> select * from emp where sal>1500 and comm is not null; |
例：查询工资大于1500，或者可以拿到奖金的雇员信息：
两个条件满足一个，可以用“or”进行连接。
|
select * from emp where sal>1500 or comm is null; |
例：查询工资不大于1500，或者拿不到奖金的雇员信息：
用NOT取反操作
|
SQL> select * from emp where not(sal>1500 and comm is not null); |
例：查询工资大于1500，不大于3000的雇员信息
语法格式
|
字段 BETWEEN 最小值 AND 最大值 |
|
SQL> select * from emp where sal between 1500 and 3000; |
例：查询1981年雇佣的全部雇佣：
日期表示的时候要加“'”
|
SQL> select * from emp where hiredate between '01-1月 -81' and '30-12月 -81'; |
例：查询名字叫SMITH的雇员
|
SQL> select * from emp where ename = 'SMITH'; |
例：查询雇员编号为7369、7499、7521的雇员信息：
|
SQL> select * from emp where empno =7369 or empno=7499 or empno=7521; |
指定从查询范围，可以用IN操作符完成，语法格式：
|
字段 IN（值1，值2，…，值3） |
不在指定范围，可以用NOT IN 操作符完成
|
字段 NOT IN（值1，值2，…，值3） |
注意：在使用NOT IN进行范围判断的时候，如果范围里面包含有null，那么不会返回任何结果。
以上查询语句可以写成：
|
select * from emp where empno in(7369,7499,7521); |
IN中指定了表中范围外的内容，不影响程序运行。
模糊查询：LIKE
SQL使用LIKE语句完成模糊查询，在LIKE语句中主要使用以下两种通配符：
l “%”：可以匹配任意长度的内容；
l “-”：可以匹配一个长度的内容。
例：查询所有雇员姓名中第二个字母包含“M”的雇员信息
|
SQL> select * from emp where ename like '_M%'; |
例：查询所有雇员姓名中包含“M”的雇员信息
|
SQL> select * from emp where ename like '%M%'; |
例：查询1981年雇佣的雇员信息
|
select * from emp where hiredate like '%81'; |
在操作条件中还可以使用>、>=、 =、 <、 <=。
不等于符号：SQL中有两种形式“<>”、“!=”。
1.3对结果进行排序（ORDER BY）
ORDER BY排序语法格式：
|
SELECT {DISTINCET} *|具体列 别名 FROM 表名称 {WHERE 条件（s）} {ORDER BY 排序的字段1，排序的字段2， ASC|DESC} ->ASC升序，DESC降序，默认按升序排 |
例：要求按雇员的工作由低到高排序
|
SQL> select * from emp order by sal asc; |
例：要求查询出10部门的所有雇员信息，按工资有高到低排序，如果工资相等，按雇佣日期由早到晚排序。
|
SQL> select * from emp where deptno=10 order by sal desc,hiredate asc; |
1.4单行函数
单行函数语法：
|
Function_name(colum|expression,[arg1,arg2,…,]) |
参数说明：
l Function_name:函数名称；
l Column：数据库列名；
l Expression：字符串与计算表达式；
l Arg:函数中使用的参数
单行函数分类：
l 字符函数：接受字符输入并返回字符或数值；
l 数值函数：接受数值输入并返回数值；
l 日期函数：对日期值数据进行操作；
l 转换函数：从一种数据类型转换为另一种数据类型；
l 通用函数：NVL函数、DECODE函数。
1.4.1字符函数
可以将大写字符变为小写，还可以求出字符的长度。
在Oracle中所有的函数如果要想进行验证，也必须编写SQL语句。为了方便用户进行一些验证或者一些不需要查询表的查询操作，专门提供了一个dual的虚拟表。
例：将小写变为大写
|
SQL> select upper('smith') from dual; |
例：一般用户在查询一个人姓名的时候，可能考虑到这个人的姓名是大写字母还是小写字母呢，此时可以使用upper函数完成。
|
SQL> select * from emp where ename = upper（'smith'); |
在Oracle数据库里面本身数据是区分大小写的，但是在实际的项目开发中有许多代码本身的不区分大小写关系的，所以用户进行信息查询的时候也往往不会考虑到字母的大小写问题。
例：由用户输入要查询的雇员姓名，而后显示雇员的完整信息
l 在Oracle中如果要想实现数据的输入操作，可以使用代替变量的方式完成，格式“&标记”
|
select * from emp where ename=upper('&inputname'); |
例：使用lower（）函数将字符串变为小写
|
SQL> select lower('HELLO') from dual; |
例：用initcap()函数将单词第一个字母变成大写，其他变成小写。
|
SQL> select initcap('HELLO') from dual; |
例：将雇员表中雇员姓名变为首字母大写。
|
SQL> select initcap(ename) from emp; |
例：字符串连接除了可以用“||”外，还可以用CONCAT（）函数进行连接操作。
|
SQL> select concat('hello','word') from dual; |
l 字符串截取：substr(str,offset,len);
l 字符串长度：length(str);
l 内容替换：replace(str,c1,c2); 字符串str中将c1变为c2
|
SQL> select substr('hello',1,4), length('hello'), replace('hello','h','t') from dual; |
面试题：Oracle中substr()函数截取点是从0还是从1开始？
|-从0和1都可以，因为Oracle比较智能。
例：要求显示所有雇员姓名和姓名后三个字符
|
SQL> select ename,substr(ename,length(ename)-2) from emp; |
实际上substr()函数中提供了一种可以倒着截取的方式，只要输入的位置是负数就表示倒着进行。
|
SQL> select ename,substr(ename,-3,3) from emp; |
1.4.2数值函数
四舍五入：ROUND();
截断小数位：TRUNC();
取余（取模）:MOD();
例：执行四舍五入。
|
SQL> select round(2.31323) from dual; |
例：对整数进行四舍五入进位。
|
SQL> select round(324.32,-2) from dual; |
例：保留2位小数。
|
SQL> select trunc(32.23232,2) from dual; |
TRUNC()与ROUND()不同的是，在TRUNC中，不会保留任何的小数，而且小数点不会进行四舍五入操作。
|
SQL> select trunc(32.427,2) from dual; |
|
SQL> select trunc(32.427) from dual; |
例：使用负数表示位数。
|
SQL> select trunc(372.827,-2) from dual; |
例：使用MOD()函数取余操作
|
SQL> select mod(10,3) from dual; |
1.4.3日期函数
日期的加减操作规律：
l 日期-数字=日期
l 日期+数字=日期
l 日期-日期=数字（天数）
例：显示10部门雇员进入公司的星期数。
|-获取当前的时间：SYSDATE
|
SQL> select sysdate from dual; |
|
SQL> select round((sysdate-hiredate )/7) from emp; |
l MONTHS_BETWEEN(DATE1，DATE2):求出所给日期范围的月数；
l ADD_MONTHS(date,months):在给定日期上加上指定月数，求出之后的日期
l NEXT_DAY(date,’星期几’):下一个的星期几是那个日期
l LAST_DAY(date);求出所给日期的所在月份的最后一天日期
验证MONTHS_BETWEEN(DATE1，DATE2)
|
SQL> select ename,months_between(sysdate,hiredate) from emp; |
验证ADD_MONTHS(date,months)
|
SQL> select add_months(sysdate,4) from dual; |
验证NEXT_DAY(date,’星期几’)
|
SQL> select next_day(sysdate,'星期一') from dual; |
验证LAST_DAY(date)
|
SQL> select last_day(sysdate) from dual; |
1.4.4转换函数
l TO_CHAR(日期，通配符):日期转换成字符串
l TO_NUMBER():字符串转化成数字
l TO_DATE():字符串转换成日期
(1)TO_CHAR()函数
日期年、月、日拆分的通配符：
l 年：’y’，年是四位的数字，可以使用’yyyy’表示；
l 月：’m’，月是两位的数字，可以使用’mm’表示；
l 日：’d’，日是两位的数字，可以使用’dd’表示；
l 年-月-日：’yyyy-mm-dd’
例：对雇员表的日期进行拆分
|
SQL> select to_char(hiredate,'yyyy')year,to_char(hiredate,'mm') month,to_char(hiredate,'dd')day from emp; |
|
SQL> select ename, to_char(hiredate,'yyyy-mm-dd') from emp; |
从运行结果可以看出日期一位数的时候会在前面补0，如果不想要0的话，可以用fm去掉这些0。
|
SQL> select ename, to_char(hiredate,'fmyyyy-mm-dd') from emp; |
TO_CHAR()函数还可以用在数字中，一般中国使用“，”分割比较长的数据，可以使用to_char()进行格式化。
|
SQL> select ename,to_char(sal,'99,999') from emp; |
可以使用以下两种符号表示不同金额：
l $:表示美元
l L:表示Local的缩写。以本地的语言进行金额的显示
|
SQL> select ename,to_char(sal,'$99,999') from emp; |
(2)TO_NUMBER()函数
将字符串转换成数字
|
SQL> select to_number('123')+to_number('234') from dual; |
(3)TO_DATE()函数
将字符串转换成DATE型数据。
|
SQL> select to_date('2016-09-12','yyyy-mm-dd') from dual; |
1.4.5通用函数
（1）NVL()函数
例：求出每个雇员的年薪（sal+comm）*12。
|
SQL> select ename ,(sal+comm)*12 from emp; |
造成这样的结果是因为有些雇员的奖金为NULL，NULL值计算之后结果还是NULL。遇到这种情况就可以使用NVL()函数，可以将一个指定的NULL值变为指定的内容。
|
SQL> select empno,ename,nvl(comm,0),(sal+nvl(comm,0))*12 from emp; |
（2）DECODE()函数
DECODE()函数在面试中最有可能问到的问题，类似于IF…ELSEIF…ELSE语句。
语法：
|
DECODE(col/expression,search1,result1[,search2,result2,…][,default]) |
说明：
l Col/expression:为列名或者表达式
l Search1、search2…searchi:用于比较的条件
l Result1、result2…resulti:为返回值
如果col/expression与search相比较，结果相同的话，返回result，如果没有与col/expression相同的结果，返回默认值default。
|
SQL> select decode(1,1,'内容是1',2,'内容是2','没有匹配的') from dual; |
结果是：内容是1
|
SQL> select decode(5,1,'内容是1',2,'内容是2','没有匹配的') from dual; |
结果是：没有匹配的
例：雇员的工作：
|-CLERK:业务员
|-SALESMAN：销售员
|-MANAGER：经理
|-ANALYST：分析员
|-PRESIDENT：总裁
现要求查询雇员的编号，姓名，雇佣日期及工作，将工作替换成以上内容。
|
SQL> select empno,ename,hiredate,decode(job,'CLERK','业务员','SALESMAN','销售员','MANAGER','经理', 'ANALYST','分析员','PRESIDENT','总裁') from emp; |
