Oracle系列《一》：简单SQL与单行函数
使用s
cott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
工资 ＝ 薪金 ＋ 佣金 登录Oracle数据库 1、sqlplus scott/tiger 2、sqlplus /nolog SQL>conn scott/tiger
若是使用SYS的账号进行登录的话，则使用以下语句 SQL>conn / 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;
【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; 如果是或的是关系，则使用 OR
【13】查询基本工资不大于1500，同时不可以领取奖金的雇员信息
SQL> SELECT * FROM emp WHERE NOT(sal>1500 AND comm is NOT NULL);
【14】查询在1981年雇佣的全部雇员信息，BETWEEN .. AND 包含等于的情况
SQL> SELECT * FROM emp
WHERE hiredate BETWEEN '01-JAN-81' AND '31-DEC-81';
【15】Oracle对大小敏感，所以查询时名字要区分大小写
【16】要求查询出雇员编号不是 7369、7499的雇员信息 SQL> SELECT * FROM emp
WHERE empno NOT IN(7369,7499);
【17】SQL中LIKE语句要注意通配符 % 和 _ SQL> SELECT * FROM emp
WHERE hiredate LIKE '?%';
【18】查看雇员编号不是7369的雇员信息，使用<>或!= SQL> SELECT * FROM emp WHERE empno<>7369;
【19】要求对雇员的工资由低到高进行排序，升序为默认(ASC)，降序(DESC) SQL> SELECT * FROM emp GROUP BY sal;
【20】查看出部门号为10的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列 SQL> SELECT * FROM emp WHERE deptno=10
GROUP 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()
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
SQL> SELECT ROUND(789.536) 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():在指定日期上加上指定的月数，求出之后的日期 NEXT_DAY():下一个的今天的日期
LAST_DAY():求出给定日期的最后一天日期
【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数
SQL> SELECT
empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;