Oracle 基础--笔记.doc

Oracle数据库学习笔记
这是学生时代自学oracle时所做的笔记。

1.1介绍及基本查询入门
在sqlplusw 中可以用其他用户连接，例如：system 用户
命令格式为：conn 用户名/密码[AS SYSDABA|SYSPER]

Sql> conn system/manger

☆show user --------------显示当前用户名
☆select * from tab; ----------得到全部表的名称

▲NUMBER(4) : 表示是数字，长度为4
▲VARCHAR2(10) : 表示是字符，最多可容纳10个字符
▲DATE ：表示日期
▲ＮＵＭＢＥＲ（７，２）： 表示是数字，其中，小数位占两位，总共７位。

在sqlplusw 中可以输入一个“ / ”表示重复执行上一条语句的操作。

使用 DISTINCT 关键字去掉重复的查询列。

查询时可以指定查询的返回列的名称，即为一个列起别名：
如： SELECT empno 编号，enname 姓名 FROM emp ;

要求格式形的字符串输出，例如：
编号是 7160（empno）的雇员姓名是 某某（ename）
SELECT ‘编号是 ’ || empno || ‘的雇员姓名是：’ || ename || FROM emp ;

△在Oracle 中 ‘||’用于字符串连接操作，固定信息要用“ ’ ” 括起来。

在查询中使用四则运算：
SELECT empno，sal*12 年薪 FROM emp ;

1.2 限定查询
语法：
SELECT {DISTINCT} *|具体的列 别名
FROM 表名称
{WHERE 条件(s)}
例如：1、查询每月可以得到奖金的雇员信息。
·奖金是comm字段 ，内容不为空（null）
·不为空的表示： IS NOT NULL
SELECT *FROM emp WHERE comm IS NOT NULL ;

2、要求查询出基本工资大于1500，同时可以领取奖金的雇员信息。
·应满足两个条件，必须用 AND 操作符进行条件的连接。
SELECT * FROM emp WHERE sal>1500 AND comm IS NOT NULL;

如果要表现出“或者”的概念使用“ OR ”进行连接，表示两个条件有一个满足即可。

·之前使用NOT 可以取反，把真的条件变为假的，假的变为真的。
范例：要求查询出工资不大于1500，同时不可以领取奖金的雇员信息。
·此时相当于是整体呃条件取反。
SELECT * FROM emp WHERE NOT (sal>1500 AND comm IS NOT NULL);
通过程序可以发现，通过括号表示一组的条件

范例：查询基本工资大于1500，但是小于3000的全部雇员信息。
·条件两个：sal>1500, sal<3000
方法1：SELECT * FROM emp WHERE sal>1500 AND sal<3000;
SQL语法中，提供了一个专门指范围的过滤语句：BETWEEN…AND…
语法格式：BETWEEN 最小值 AND 最大值;
方法2：SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;
实际上，BETWEEN …AND…操作等价于：sal>=1500 AND sal <=3000,包含了等于的功能。

范例：查询出1981年雇佣的全部雇员信息。
·1981年1月1日~1981年12月31日之间的雇员信息
·日期表示的时候要加入单引号” ‘ “
SELECT *FROM emp
WHERE hiredate BETWEEN ‘1-1月 -81’ AND ‘31-12月-81’;

结论：BETWEEN …AND…除了可以支持数字之外，也可以支持日期的查询。
·日期实际上也是以数字的形式表示出来。

范例：要求查询出姓名是Smith的雇员信息。
·此时告诉了要查询的名字，条件：ename=’smith’;
SELECT * FROM emp WHERE ename=’smith’; 执行此语句无返回结果，因为Oracle中是对大小写敏感的，所以查询代码应修改如下：
SELECT * FROM emp WHERE ename=’Smith’;

范例：要求查询出编号是7369、7499、7521的雇员的具体信息。
·实际上，此时是指定了查询的范围，在SQL中语法中可以用 IN 操作完成
语法格式：
字段 IN（值1,值2,值3,……..）
SELECT * FROM emp WHERE empno IN (7369,7499,7521);

范例：要求查询出编号不是7369、7499、7521的雇员的具体信息。
SELECT * FROM emp WHERE empno NOT IN (7369,7499,7521);

范例：要求查询出姓名是SMITH,ALLEN, KING的雇员的具体信息。
SELECT * FROM emp WHERE ename IN (‘SMITH’,’ALLEN’,’KING’);
提示：
如果在指定的查询范围中指定了额外的内容，则不影响程序的运行。

在一般常见的站点中经常发现有模糊查询功能，即：输入一个指定的关键字，把符合内容全部查询出来，在SQL中使用 LIKE语句来完成。
·“ % ”：可以匹配任意长度的内容。
·“ _ ”：可以匹配一个 长度的内容。
范例：查询出所有雇员中第二个字母包含“M”的雇员信息。
SELECT * FROM emp WHERE ename LIKE ‘_M’;
范例：查询出所有雇员姓名中包含“M”的雇员信息。
SELECT *FROM emp WHERE ename LIKE ‘%m%’;
但是，如果在使用LIKE的时候没有指定查询的关键字，则表示查询全部。

不等于符号：“ <> ”、“ != ”

1.3 对结果进行排序（ORDER BY 字句）（重点）
在SQL中可以使用ORDER BY 子句对查询结果进行排序，例如：现在使用查询全部的子句。
SELECT * FROM emp;
如果需要对使用指定的列进行排序，则就必须使用ORDER BY语句，语法格式如下：
SELECT {DISTINCT} * |具体的列 别名
FROM 表名称
{WHERE 条件(s)}
{ORDER BY 排序的字段1,排序的字段2 ASC|DESC}

范例：要求按照工资由低到高排序。
SELECT * FROM emp ORDER BY sal;
如果在排序的时候没有指定排序规则，则默认为升序。

1.4 单行函数（重点）
单行函数语法：
function_name(column | expression,[arg1,arg2,arg3,…])
参数说明：
·function_name:函数名称
·column：数据库列名
·expression：字符串或计算表达式
·arg1，arg2：在函数中使用参数

1.4.1 单行函数
是专门处理字符的，如可以将大写字符变为小写，还能求出字符的长度。
范例：将姓名变为大写字母。
SELECT UPPER (‘smith’) FROM dual;

SELECT * FROM emp WHERE ename=UPPER(Smith);

还可以使用 initcap() 函数将单词的第一个字母大写。
SELECT initcap(‘HELLO WORLD’) FROM dual;

范例：使用此函数将雇员表中的雇员姓名变为开头字母大写。
SELECT INITCAP(ename) FROM emp;

字符串除了用 ” || ” 连接外，还可以用CONCAT() 函数进行连接操作。
SELECT CONCAT(‘hello’，’world’) FROM dual;
此时已经完成了连接，但是没有 ” || ” 好使。

在字符函数中可以进行字符串的截取，求出字符串的长度，进行指定内容的替换：
·字符串截取：substr()
·字符串长度：length()
·内容替换： replace()
SELECT substr(‘hello’,1,3) 字符串截取,
length(‘My love’) 字符串长度,
replace(‘hello’,’l’,’x’) 字符串替换 --将l 替换为 x。
FROM dual;

但是在 substr() 函数有时候有一点需要提醒大家注意，跟面试有关。
·Oracle 中 substr() 的截取点是从0还是1开始。
|- 从0或从1开始效果是一样的，因为Oracle比较智能。
范例：要求显示所用雇员的姓名及姓名的后三个字符。
SELECT ename ,SUBSTR(ename,LENGTH(ename)-2) FROM emp;
此时功能实现，但是操作比较麻烦。实际上，在 substr() 提供了一种非常便利的截取方式，只要输入的位置是负数就表示倒着进行。
SELECT ename ,SUBSTR(ename,-3,3) FROM emp;
--表示从倒数第三个字符开始截取长度为3的字符串

1.4.2 数值函数

范例：执行四舍五入操作
SELECT ROUND(756.756) FROM DUAL; --结果是757
在 ROUND() 函数中也可以指定四舍五入的位数
范例：保留两位小数。
SELECT ROUND(28.28282828,5) FROM dual; --结果是28.28283

在使用ROUND() 函数时，可以直接对整数进行四舍五入进位。
SELECT ROUND(28.2828,-1);-- -1表示从小数点向左第一位可以进行四舍五入进位，结果30

SELECT TRUNC(28.2828) FROM dual;

通过 TRUNC() 函数，可以指定小数点的保留位数。
SELECT TRUNC(2828.2828,2) FROM dual; -- 2表示保留两位小数点，结果是2828.28
SELECT TRUNC(2828.2828,-2) FROM dual; -- -2表示整数低位的两位变零，结果是2800

使用 MOD() 函数取余。
范例：使用 MOD() 函数进行取余操作。
SELECT MOD(10,3) FROM dual; -- 10作为被除数，3是除数

1.4.3 日期函数

范例：显示10部门雇员进入公司的星期数
·如果要想完成此操作，则首先必须知道当前的日期，在Oracle中使用 sysdate 表示。
SELECT SYSDATE FROM dual; --当前日期
·求星期数：当前日期-雇佣日期=天数，天数/7=星期数。
SELECT empno,ename, ROUND((syadate-hiredate)/7 ) FROM emp;

在Oracle 中提供了以下的日期函数支持：
·MONTHS_BETWEEN():求出指定日前范围的月数
·ADD_MONTHS():在指定的日期上加上指定的月数，求出之后的日期
·NEXT_DAY():下一个的今天是哪一个日期
·LAST_DAY():求出给定的最后一天的日期。

范例：验证MONTHS_BETWEEN()
SELECT empno,ename,MONTHS_BETWEEN(sysdate,hiredate) FROM emp;

程序中有小数点，就用ROUND() 函数进行四舍五入操作。

范例：验证ADD_MONTHS () 函数
SELECT ADD_MONTHS(sysdate,4) FROM dual; -- 4表示当前日期要加上4个月

范例：验证NEXT_DATE() 函数
·此函数给出下一次给定的日期。
SELECT NEXT_DATE(sysdate,’星期一’) FROM dual; --当前日期起，下个星期一的日期

范例：验证LAST_DATE() 函数
·一个日期的最后一天。
SELECT LAST_DAY(sysdate) FROM dual;

1.4.4 转换函数
转换函数主要有一下集中：
★TO_CHAR : 转换成字符串
★TO_NUMBER : 转换成数字
★TO_DATE : 转换成日期

范例：查询所有雇员的雇员编号，姓名，雇佣日期。
·要求将年，月，日进行分开
·此时就可以用TO_CHAR()函数进行拆分，拆分的时候必须指定拆分的通配符：
|-年：y，年是四位的数字，所以使用 yyyy 表示
|-月：m，月是两位的数字，所以使用 mm 表示
|-日：d，日是二位的数字，所以使用 dd 表示

SELECT empno,ename,TO_CHAR(hiredate,’yyyy’)year,
TO_CHAR(hiredate,’mm’)months,
TO_CHAR(HIRE,’dd’)days FROM emp;

还可以使用to_char() 进行日期显示转换功能
·Oracle 中默认的日期格式是：18-3月 -10 （日-x月 –年）
·中国喜欢的格式：2010-03-18
SELECT empno,ename,TO_CHAR(hiredate,’yyyy-mm-dd’) FROM emp;

to_char() 函数除了可以用在日期上，还可以用在数字上。
例如：查询全部的雇员编号、姓名、工资

===========================================================================

△TO_NUMBER() 是可以把字符串编程数字的一种函数，所得的数字可以进行加法操作。
SELECT TO_NOMBER(‘123’) + TO_NOMBER(‘123’) FROM dual;

△TO_DATE()

1.4.5 通用函数
范例：要求求出每个雇员的年薪
·求出年薪的时候应该加上奖金的，格式（sal*comm）*12
·有些雇员的奖金是NULL，NULL值计算之后结果还是NULL，所以面对这样的情况，可以使用NVL 函数，可以将一个指定的null 的值变为指定的内容。
SELECT empno,ename,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;

如果需要进行计算的时候，对于NULL必须使用NVL() 函数进行一个转换操作。

范例：验证DECODE()函数
SELECT DECODE(1,1,’内容是1’,2,’内容是2’,3,’内容是3’) FROM dual

使用DECODE() 输出信息有所更改了！

总结：

2.知识点
2.1上次课程的主要知识点

3.1 多表查询
语法如下：
SELECT {DISTINCT} *| 查询列1 别名,查询列2 别名,..
FROM 表名称1 别名,表名称2 别名,…..
{WHERE 条件(s)}
{ORDER BY 排序字段 ASC | DESC ,排序字段 ASC | DESC,….}

范例：下面使用了多表查询。同时查询emp和dept表
SELECT * FROM emp,dept;
SELECT * FROM emp,dept
WHERE emp.deptno=dept.deptno;
但是现在又存在一个新的问题，如果现在表名称过长的话？所以在使用的时候一般会为表其别名。
SELECT * FROM emp,dept
WHERE e.deptno,d.deptno;

范例：要求查询出雇员的编号，雇员的姓名、部门的编号、部门名称及部门位置。
SELECT e.empno,e.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;

思考：
要求查询出每个雇员的姓名、工资、部门名称、工资在公司的等级（salgrade）。
·工资等级表的内容（查询salgrade表）
SELECT e.ename, e.sal,d.dname,s,grade
FROM emp e, dept d,salgrade s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

3.2 左右连接

这里使用到了右连接。
·“ (+) ”在 = 左边表示右连接，
·“ (+) ”在 = 右边表示左连接。

3.3 数组及分组统计（重点）
3.3.1 组函数
在SQL中常用的组函数有以下几个：
·COUNT() : 求出全部记录数。
·MAX()　 : 求出一组中的最大值。
·MIN() 　: 求出最小值
·AVG()　 :　求出平均值
·SUM()　 :　求和

范例：COUNT() 函数
SELECT COUNT(empno) FROM emp ;

范例：求出员工的最高工资。
SELECT MAX(sal) FROM emp;

3.3.2 分组统计

范例：求出每个部门的雇员数量。肯定应该按照部门编号划分，按deptno分组。
SELECT deptno ,COUNT(empno) FROM emp
GUOUP BY deptno ;

注意点：观察以下代码。
SELECT deptno,COUNT(empno) FROM emp;

以上代码不能正确执行，是因为：
1、 如果程序中使用了分组函数，则有两种可以使用的情况：
·程序中存在了GUOUP BY ，并指定了分组条件，这样可以将分组条件一起查询出来。
·如果不使用分组的话，则只能单独使用分组函数。
2、在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。
SELECT deptno ,empno,COUNT(empno)
FROM emp
GROUP BY deptno;
此时提示 empno 不是GUOUP BY表达式，所以无法使用。

范例：按部门分组，并显示部门名称，及每个部门的员工数。
SELECT d.dname,COUNT(e.empno)
FROM dept d, emp e
WHERE d.deptno=e.deptno
GROUP BY d.dname

分组函数只能在分组中使用，不能在WHERE语句中出现，那么如果现在要假设要指定分组的条件，则只能通过第二种条件的指令：HAVING ,此时的SQL语法格式：

范例：使用HAVING完成以上操作。
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno HAVING AVG(sal)>2000;

1、 显示全部的非销售人员：job<>’SALESMAN’
SELECT job FROM emp WHERE job<>’ SALESMAN;

2、 按工作分组，同时求出工资的总和。
SELECT job ,SUM(sal)
FROM emp
WHERE job<>’SALESMAN’
GROUP BY job;

3、 对分组条件进行限制，工资总和大于5000.
SELECT job,SUM(sal)
FROM emp
WHERE job<>’SALESMAN’
GROUP BY job HAVING SUM(sal)>5000;
4、 使用排序，按升序排序。ORDER BY su
SELECT job,SUM(sal) SU
FROM emp
WHERE job<>’SALESMAN’
GROUP BY job HAVING SUM(sal)>5000
ORDER BY SU;

分组的简单原则：
·只要一列上存在重复的内容才有可能考虑分组。

范例：求出平均工资最高的部门。

3.4 数据库更新操作

3.4.1 添加数据
添加数据的语法：
INSERT INTO 表名称[(字段名称1,字段名称2,…)] VALUES(值1,值2,…);

范例：为myemp表添加一条新纪录
INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VELUES (1228,’赖志’,’设计师’,1228,’24-3月-2010’,10000,1500,0001)

必须提醒：
·对于数字，不用加“ ‘ ”,对于字符串才加“ ‘ ”。
·使用简略的写法（不推荐），因为现在是要添加所有字段的内容，所以此处可以不写上任何字段名称，只要值的数量及顺序与数据表中的一致即可。
例如：
INSERT INTO myemp(8989,’八尺琼’,’教师’,2010,’ 24-3月-2010’ ,1500,1000,0002);

·使用TO_DATE()函数将一个字符串类型的数据变为DATE类型的数据。
INSERT INTO myemp(9988,’悟空’,’打妖怪’,TO_DATE(‘2010-03-24’,’yyyy-mm-dd’ ),9000,30);

3.4.1 修改数据

把myemp表的comm都更改为1000.
UPDATE myemp SET comm=1000;
将编号为1228雇员的的奖金改为1500元。
UPDATE myemp SET comm=1500 WHERE empno=1228;

3.4.3 删除数据
在SQL语句中可以使用DELETE命令输出记录，语法格式如下：
删除全部：DELETE FROM 表名称;
删除局部：DELETE FROM 表名称 WHERE 删除条件;

删除编号为8899、7889、8889、8888的雇员信息
DELETE FROM myemp WHERE empno IN(8899,7889,8899,8888);

删除的时候不指定条件则表示删除全部内容。