《经典Oracle的sql语句百例训练》由会员分享，可在线阅读，更多相关《经典Oracle的sql语句百例训练（30页珍藏版）》请在金锄头文库上搜索。
1、Oracle 系列一：简单 SQL 与单行函数 使用 scott/tiger 用户下的 emp 表和 dept 表完成下列练习，表的结构说明如下 emp 员工表(empno 员工号/ename 员工姓名/job 工作/mgr 上级编号/hiredate 受雇日期/sal 薪金/comm 佣金/deptno 部门编号) dept 部门表(deptno 部门编号/dname 部门名称 /loc 地点) 工资 薪金 佣金登录 Oracle 数据库1、sqlplus scott/tiger2、sqlplus /nologSQLconn scott/tiger若是使用 SYS 的账号进行登录的话，则使用
2、以下语句SQLconn / as sysdba【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
3、;【7】可以为列名取别名，在 Linux 下 Oracle 如果英文别名不加上双引号则会变成大写SQL SELECT empno 编号,ename 姓名,job 工作 FROM emp;【8】查询所有的工作SQL SELECT DISTINCT job FROM emp; 工作可能会重复，加上 DISTINCT 关键字【9】若要求按照以下的格式进行结果输出，如 NO:7469,Name:SMITH,Job:CLERKSQL SELECT NO:|empno|,Name:|ename|,Job:|job FROM emp;【10】要求列出每个雇员的姓名及年薪SQL SELECT ename,sa
4、l*12 income FROM emp;这里年薪最好用别名进行标识，可以一眼就能明白【11】查看每月可以得到奖金的雇员信息SQL SELECT * FROM emp WHERE comm is NOT NULL;【12】要求基本工资大于 1500，同时可以领取奖金的雇员信息SQL SELECT * FROM emp WHERE sal1500 AND comm is NOT NULL;如果是或的是关系，则使用 OR【13】查询基本工资不大于 1500，同时不可以领取奖金的雇员信息SQL SELECT * FROM emp WHERE NOT(sal1500 AND comm is NOT N
5、ULL);【14】查询在 1981 年雇佣的全部雇员信息，BETWEEN . AND 包含等于的情况SQL SELECT * FROM empWHERE hiredate BETWEEN 01-JAN-81 AND 31-DEC-81;【15】Oracle 对大小敏感，所以查询时名字要区分大小写【16】要求查询出雇员编号不是 7369、7499 的雇员信息SQL SELECT * FROM empWHERE empno NOT IN(7369,7499);【17】SQL 中 LIKE 语句要注意通配符 % 和 _SQL SELECT * FROM empWHERE hiredate LIKE
6、%81%;【18】查看雇员编号不是 7369 的雇员信息，使用 SELECT * FROM empWHERE empno SELECT * FROM empGROUP BY sal;【20】查看出部门号为 10 的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列SQL SELECT * FROM empWHERE deptno=10GROUP BY sal DESC,hiredate ASC;数据库系统中，每个数据库之间区别最大的就是在函数的支持上，单行函数是最简单的函数，单行函数分为1、字符函数：接受字符输入并且返回字符或数值2、数值函数：接受数值输入并返回数值3、日
7、期函数：对日期型数据进行操作4、转换函数：将一种数据类型转换为另一种数据类型5、通用函数： NVL、DECODE 函数字符函数：【1】大小写转换 UPPER 和 LOWERSQL SELECT UPPER(smith) FROM dual;【2】将雇员姓名变为开头字母大写，INITCAPSQL SELECT INITCAP(ename) FROM emp;字符函数中有连接函数 CONCAT，但不如 | 好用，还有字符串处理的一些函数字符串截取：substr()字符串长度：length()内容替换：replace()SQL SELECT substr(hello,1,3),length(hell
8、o),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、取余(取模)：MODSQL SELECT ROUND(789.5
9、36) FROM dual;【1】保留 2 位小数,(如果是-2 则对整数进行四舍五入，变为 800 了)SQL SELECT ROUND(783.56,2) FROM dual;【2】使用 MOD()函数进行取余操作SQL SELECT MOD(10,3) FROM dual;日期函数：1、日期 - 数字 = 日期2、日期 + 数字 = 日期3、日期 - 日期 = 数字(天数)【1】求出当前日期SQL SELECT SYSDATE FROM dual;Oracle 提供了以下的日期函数支持：MONTHS_BETWEEN():求出给定日期范围的月数ADD_MONTHS():在指定日期上加上指定
10、的月数，求出之后的日期NEXT_DAY():下一个的今天的日期LAST_DAY():求出给定日期的最后一天日期【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数SQL SELECT empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;【3】验证 ADD_MONTHS()、NEXT_DAY() 、LAST_DAY()SQL SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL;SQL SELECT NEXT_DAY(SYSDATE,MON) FROM DUAL;SQL SELECT LAST_DAY
11、(SYSDATE) FROM DUAL;转换函数：1、TO_CHAR() ： 将日期或数值转换成字符串2、TO_NUMBER()：将字符串转换成数字3、TO_DATE() ： 将字符串转换成日期【1】将年月日进行分开，要指定拆分的通配符,yyyy-mm-ddSQL SELECT empno,ename,TO_CHAR(hiredate,yyyy) datetime FROM emp;【2】将薪水的数字进行格式化，$99,999表示美元，L99,999表示当地货币SQL SELECT empno,ename,TO_CHAR(sal,99,999) salary FROM emp;【3】TO_NU
12、MBER()验证SQL SELECT TO_NUMBER(123)+TO_NUMBER(123) FROM DUAL;【4】TO_DATE() 验证，如下例子执行后显示为 11-JUL-11SQL 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(c
13、omm,0)*12 income FROM emp;NVL 可以理解为将 NULL 值转换为具体的内容，这里是 0【2】DECODE()函数，该函数类似于 IF . ELSEIF.ELSE语法如下：DECODE(col/expression,选择 1,结果 1，选择 2，结果 2，., 默认)验证 DECODE()函数SQL SELECT empno,ename,hiredate,DECODE(job,CLERK,业务员,SALESMAN,销售人员,MANAGER,经理,ANALYST,分析员,PRESIDENT,总裁) 职位FROM emp;SQL 简单语句练习：【1】找出佣金高于薪金的 6
14、0%的员工SQL SELECT * FROM emp WHERE commsal*0.6【2】找出部门 10 中所有经理(MANAGER)和部门 20 中所有办事员(CLERK)的详细资料SQL SELECT * FROM empWHERE (deptno=20 AND job=MANAGER)OR (deptno=10 AND job=CLERK);【3】找出既不是经理又不是办事员但其薪金大于或等于 2000 的所有员工的资料SQL SELECT * FROM empWHERE job NOT IN(MANAGER,CLERK) AND sal = 2000;【4】找出有奖金的员工的不同国祚
15、SQL SELECT DISTINCT job FROM empWHERE comm IS NOT NULL;【5】找出各月倒数第 3 天受雇的所有员工SQL SELECT * FROM empWHERE LAST_DAY(hiredate)-2=hiredate;【6】找出早于 12 年前受雇的员工SQL SELECT * FROM empWHERE MONTHS_BETWEEN(sysdate,hiredate)/12 12;【7】显示刚好为 5 个字符的员工的姓名SQL SELECT ename FROM empWHERE length(ename)=5;【8】显示不带有 R的员工的姓名
16、SQL SELECT ename FROM empWHERE ename NOT LIKE %R%;【9】显示员工的姓名和受雇日期，将最老的员工排在最前SQL SELECT * FROM empGROUP BY hiredate;【10】显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则按年份排序SQL SELECT ename,TO_CHAR(hiredate,yyyy) year,TO_CHAR(hiredate,mm) month FROM empORDER BY month,year;【11】找出在 2 月受聘的员工SQL SELECT * FROM empWHERE TO
