ORACLE百例试炼一

hege

　　Oracle系列《一》：简单SQL与单行函数
　　【1】EMP表内容查询
　　SQL> SELECT * FROM emp;
　　出错，原因是没有找到该表，因为该表时SCOTT用户的表，所以查询时应该加上scott.emp就可以了
　　【2】显示当前用户
　　SQL> show user
　　【3】查看当前用户的所有表
　　SQL> SELECT * FROM tab;
　　【4】若想重复执行上一条SQL语句，则在sqlplus命令行下输入"/"即可
　　【5】查询一张表的结构，例如dept表
　　SQL> desc dept
　　【6】在雇员表中查询雇员的编号、姓名、工作
　　SQL> SELECT empno,ename,job FROM emp;
　　【7】可以为列名取别名，在Linux下Oracle如果英文别名不加上双引号则会变成大写
　　SQL> SELECT empno 编号,ename 姓名,job 工作 FROM emp;
　　【8】查询所有的工作
　　SQL> SELECT DISTINCT job FROM emp;  工作可能会重复，加上DISTINCT关键字
　　【9】若要求按照以下的格式进行结果输出，如
　　NO:7469,Name:SMITH,Job:CLERK
　　SQL> SELECT 'NO:'||empno||',Name:'||ename||',Job:'||job FROM emp;
　　【10】要求列出每个雇员的姓名及年薪
　　SQL> SELECT ename,sal*12 income FROM emp; 这里年薪最好用别名进行标识，可以一眼就能明白
　　【11】查看每月可以得到奖金的雇员信息
　　SQL> SELECT * FROM emp WHERE comm is NOT NULL;
　　【12】要求基本工资大于1500，同时可以领取奖金的雇员信息
　　SQL> SELECT * FROM emp WHERE sal>1500 AND comm is NOT NULL;
　　【13】查询基本工资不大于1500，同时不可以领取奖金的雇员信息 -------------如果是或的是关系，则使用 OR
　　SQL> SELECT * FROM emp WHERE NOT (sal>1500 OR comm is NOT NULL);
　　或
　　SQL>SELECT * FROM emp WHEREM salselect * from emp where to_char(hiredate,'yyyy')=1981;
　　【15】Oracle对大小敏感，所以查询时名字要区分大小写
　　【16】要求查询出雇员编号不是 7369、7499的雇员信息
　　SQL> SELECT * FROM emp WHERE empno NOT IN(7369,7499);
　　SQL> select * from emp where empno   !=  all(7369，7499)
　　【17】SQL中LIKE语句要注意通配符 % 和 _
　　SQL> SELECT * FROM emp WHERE hiredate LIKE '%81%';
　　【18】查看雇员编号不是7369的雇员信息，使用或!=
　　SQL> SELECT * FROM emp WHERE empno7369;
　　【19】要求对雇员的工资由低到高进行排序，升序为默认(ASC)，降序(DESC)
　　SQL> SELECT * FROM emp ORDER BY sal;
　　【20】查看出部门号为10的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列
　　SQL> SELECT * FROM emp WHERE deptno=10 ORDER BY sal DESC,hiredate ASC;
　　数据库系统中，每个数据库之间区别最大的就是在函数的支持上，单行函数是最简单的函数，单行函数分为
　　1、字符函数：接受字符输入并且返回字符或数值
　　2、数值函数：接受数值输入并返回数值
　　3、日期函数：对日期型数据进行操作
　　4、转换函数：将一种数据类型转换为另一种数据类型
　　5、通用函数：NVL、DECODE 函数
　　字符函数：
　　【1】大小写转换 UPPER 和 LOWER
　　SQL> SELECT UPPER('smith') FROM dual;
　　【2】将雇员姓名变为开头字母大写，INITCAP
　　SQL> SELECT INITCAP(ename) FROM emp;
　　字符函数中有连接函数CONCAT，但不如 || 好用，还有字符串处理的一些函数
　　字符串截取：substr()
　　字符串长度：length()
　　内容替换：replace()
　　查找字符：instr(ename,'A')=1(第一个字母为A的名字)
　　SQL> SELECT  substr('hello',1,3),length('hello'),replace('hello','l','x') FROM dual;
　　这里注意的是Oracle中字符串截取从0和从1开始都是一样的，谨防面试提问
　　【3】要求显示所有雇员的姓名及姓名的后3个字符
　　SQL> SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;
　　以上操作显得较为麻烦，substr()函数是可以倒着截取
　　SQL> SELECT ename,SUBSTR(ename,-3,3) FROM emp;
　　数值函数：
　　1、四舍五入：ROUND()
　　2、截断小数位：TRUNC()
　　3、取余(取模)：MOD
　　【1】保留1位小数,(如果是-2则对整数进行四舍五入，变为800了
　　SQL> SELECT ROUND(783.56,1) FROM dual;
　　结果是-------783.6
　　【2】截断小数位，
　　SQL> SELECT ROUND(783.56) FROM dual;
　　结果是-------783
　　【3】使用MOD()函数进行取余操作
　　SQL> SELECT MOD(10,3) FROM dual;
　　结果是--------1
　　日期函数：
　　1、日期 - 数字 = 日期
　　2、日期 + 数字 = 日期
　　3、日期 - 日期 = 数字(天数)
　　【1】求出当前日期
　　SQL> SELECT SYSDATE FROM dual;
　　Oracle提供了以下的日期函数支持：
　　1.MONTHS_BETWEEN():求出给定日期范围的月数
　　select months_between(to_date('2015-10','yyyy-mm'),to_date('1997-4','yyyy-mm') )from dual
　　2.ADD_MONTHS():在指定日期上加上指定的月数
　　select add_months(to_date('2015-10-1','yyyy-mm-dd'),1) from dual
　　3.求出之后的日期 NEXT_DAY():下一个的今天的日期
　　select next_day(sysdate,'星期三') from dual
　　4.LAST_DAY():求出给定日期的最后一天日期
　　select last_day(to_date('2015-10','yyyy-mm'))from dual
　　【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数
　　SQL> SELECT empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;
　　转换函数：
　　1、TO_CHAR()：  将日期或数值转换成字符串
　　2、TO_NUMBER()：将字符串转换成数字（字符串里是数字）
　　3、TO_DATE()：  将字符串转换成日期
　　【1】将年月日进行分开，要指定拆分的通配符,yyyy-mm-dd
　　SQL> SELECT empno,ename,TO_CHAR(hiredate,'yyyy') datetime FROM emp
　　【2】将薪水的数字进行格式化，'$99,999'表示美元，'L99,999'表示当地货币
　　SQL> SELECT empno,ename,TO_CHAR(sal,'L99,999) salary FROM emp;
　　【3】TO_NUMBER()验证
　　SQL> SELECT TO_NUMBER('123')+TO_NUMBER('123') FROM DUAL;
　　【4】TO_DATE
　　SQL> SELECT TO_DATE('2011-7-11','yyyy-mm-dd') FROM DUAL;
　　通用函数：
　　【1】求出每个雇员的年薪(应算上奖金)
　　SQL> SELECT empno,ename,(sal+comm)*12 FROM emp;
　　由于comm中有NULL,NULL值计算后还是NULL,正确如下：
　　SQL> SELECT empno,ename,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;
　　NVL可以理解为将NULL值转换为具体的内容，这里是0
　　【2】DECODE()函数，该函数类似于 IF ... ELSEIF...ELSE 语法如下：
　　DECODE(字段,选择1,结果1[，选择2，结果2，...,默认])
　　验证DECODE()函数
　　SQL>select  empno,ename,hiredate,DECODE
　　(job,'CLERK','业务员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') 职位 FROM emp;
　　SQL简单语句练习：
　　【1】找出佣金高于薪金的60%的员工
　　SQL> SELECT * FROM emp WHERE comm>sal*0.6
　　【2】找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料
　　SQL> SELECT * FROM emp
　　WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK');
　　【3】找出既不是经理又不是办事员但其薪金大于或等于2000的所有员工的资料
　　SQL> SELECT * FROM emp
　　WHERE job NOT IN('MANAGER','CLERK') AND sal >= 2000;
　　【4】找出有奖金的员工的不同工作
　　SQL> SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;
　　【5】找出各月倒数第3天受雇的所有员工
　　SQL> SELECT * FROM emp
　　WHERE LAST_DAY(hiredate)-2=hiredate;
　　【6】找出早于30年前受雇的员工
　　SQL> SELECT * FROM emp
　　WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 > 30;
　　【7】显示刚好为5个字符的员工的姓名
　　SQL> SELECT ename FROM emp WHERE length(ename)=5;
　　【8】显示不带有"R"的员工的姓名
　　SQL> SELECT ename FROM emp WHERE ename NOT LIKE '%R%';
　　【9】显示员工的姓名和受雇日期，将最老的员工排在最前
　　SQL> SELECT * FROM emp GROUP BY hiredate;
　　【10】显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则按年份排序
　　select   ename,  to_char(hiredate,'yyyy')  年份   ,  to_char(hiredate,'mm')  月份 from emp  order by 月份,年份
　　【11】找出在2月受聘的员工
　　SQL> SELECT * FROM emp
　　WHERE TO_CHAR(hiredate,'mm')=2;
　　【12】以年月日方式显示所有员工服务年限
　　SQL> SELECT ename,
　　TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12)year,
　　TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) month,
　　TRUNC(MOD(sysdate-hiredate,30)) day
　　FROM emp;