王勇-Java项目视频Oracle10G培训日志.docx

1、纲要 4
2、内容 4
2.1、SQL概述 4
2.2、什么是数据库 4
2.3、表 5
2.4、SQL的分类 5
2.5、演示数据的结构 5
2.5.1、怎么样可以取得演示数据表 5
2.5.2、如何查看表结构 6
2.5.3、表结构描述 6
2.6、简单查询 7
2.6.1、查询一个字段 7
2.6.2、查询多个字段 7
2.6.3、查询全部字段 8
2.6.4、计算员工的年薪 8
2.6.5、将查询出来的字段显示为中文 9
2.7、SQL Plus常用命令 9
2.7.1、set linesize 200 9
2.7.2、l(List) 10
27.3、run / r 10
2.7.4、save 11
2.7.5、get 11
2.7.6、ed(edit) 11
2.7.7、如何直接执行sql脚本 12
2.8、条件查询 12
2.8.1、等号操作符 12
2.8.2、<>操作符 14
2.8.3、between … and …操作符 14
2.8.4、is null 15
2.8.5、and 16
2.8.6、or 16
2.8.7、表达式的优先级 16
2.8.8、in 17
2.8.9、not 18
2.8.10、like 19
2.9、排序数据 20
2.9.1、单一字段排序 20
2.92、手动指定排序顺序 21
2.9.3、多个字段排序 22
2.9.3、使用字段的位置来排序 22
2.10、数据处理函数 23
2.10.1、lower 23
2.10.2、upper 24
2.10.3、substr 24
2.10.4、length 24
2.10.5、trim 25
2.10.6、to_date 25
2.10.7、to_char 26
2.10.8、to_number 27
2.10.9、nvl 27
2.10.10、case … when … then …end 28
2.10.11、decode 29
2.10.12、round 29
2.11、聚合函数 29
2.11.1、count 30
2.11.2、sum 30
2.11.3、avg 31
2.11.4、max 31
2.11.4、min 32
2.11.4、组合聚合函数 32
2.12、分组查询 33
2.12.1、group by 33
2.12.2、having 34
2.12.3、select语句总结 34
2.13、连接查询 35
2.13.1、SQL92语法 35
2.13.2、SQL99语法 38
2.14、子查询 39
2.14.1、在where语句中使用子查询，也就是在where语句中参加select语句 39
2.14.2、在from语句中使用子查询，可以将该子查询看做一张表 40
2.14.3、在select语句中使用子查询 41
2.15、union和minus 42
2.15.1、union可以合并集合〔相加〕 42
2.15.1、minus可以移出集合〔相减〕 42
2.16、rownum的使用 43
2.16.1、rownum隐含字段 43
2.16.2、取得前5条数据 43
2.16.3、取得大与第5条的所有数据 44
2.16.4、取得薪水最高的前5名 44
2.16.5、 采用rownum进行分页 45
2.17、添加、修改和删除 46
2.17.1、insert 46
2.17.2、update 50
2.17.2、delete 50
2.18、事务概述 50
2.19、表 51
2.19.1、创立表 51
2.19.2、创立表参加约束 52
2.19.3、t_student和t_classes完整例如 58
2.19.4、增加/删除/修改表结构 59
2.19.5、增加/删除/修改表约束 60
2.19.6、删除表 60
2.20、索引 61
2.20.1、建立索引 61
2.21、视图 61
2.21.1、删除视图 62
2.22、序列(Sequence) 63
2.22.1、创立序列 63
2.22.2、使用序列 63
2.22.3、删除序列 63
2.23、存储过程、触发器和游标 63
2.23.1、存储过程 63
2.23.2、触发器 64
2.24、游标 65
2.5、常用的DBA命令 66
2.5.1、查看用户拥有的数据库对象 66
2.5.2、查看约束信息 66
2.5.3、查看用户拥有的表 66
2.5.4、查看用户拥有的视图 66
2.5.5、查看用户拥有的触发器 66
2.5.6、查看用户拥有的序列 66
2.5.7、查看用户拥有的存储过程 66
2.5.8、查看用户拥有的索引 66
2.5.9、显示当前用户 67
2.5.10、切换用户 67
2.5.11、将用户赋予某种角色登录 67
2.5.12、查看所有的用户 67
2.5.13、查看用户拥有的权限 67
2.5.14、给用户加锁 67
2.5.15、给用户解锁 67
2.5.16、修改用户密码 68
2.5.17、新建用户 68
2.5.18、删除用户及相关对象 68
2.5.19、给用户授权〔多个采用逗号间隔〕 68
2.5.20、分配表空间usres给用户 68
2.5.21、授权表空间给用户 68
2.5.21、一个完整的过程,创立用户、创立表空间、授权、建表 69
2.25.22、导入和导出命令imp、exp 69
2.26、数据库设计的三范式 69
2.26.1、第一范式 69
2.26.2、第二范式 70

1、纲要
l SQL概述
l Oracle简介
l SQL plus概述
l 演示数据表结构
l 简单查询
l SQL plus常用命令
l 条件查询
l 排序数据
l 数据处理函数
l 聚合函数
l 分组查询
l 连接查询
l 子查询
l union和minus
l rownum的使用
l 添加，修改和删除
l 事务概述
l 表
l 索引
l 视图
l 序列
l 存储过程、触发器和游标
l 数据库设计三范式
l Oracle体系结构、概念及常用DBA操作
2、内容
2.1、SQL概述
SQL，一般发音为sequel，SQL的全称Structured Query Language)，SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准。
2.2、什么是数据库
数据库，通常是一个或一组文件，保存了一些符合特定规格的数据
数据库软件称为数据库管理系统〔DBMS〕，全称为DataBase Managed System，如：Oracle、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql
2.3、表
表是一种结构化的文件，可以用来存储特定类型的数据，如：学生信息，课程信息都可以放到表中。另外表都有特定的名称，而且不能重复。表中具有几个概念：列、行、主键。
学生信息表
学号〔主键〕
姓名
性别
年龄
00001
张三
男
20
00002
李四
女
20

列，通常也叫字段，表是由列构成的，列是具有类型的
行，也叫记录，表中的数据是按行〔记录〕存储的，表里可以有0条或多条记录
主键，主键是由列构成的，表中的每一行通常都有一个标识，主键可以由一个字段或多个字段构成，一个字段构成的主键称为单一主键，多个字段构成的主键称为复合主键，主键通常是不能修改的

2.4、SQL的分类
数据查询语言〔DQL〕，只有一个select
数据操纵语言〔DML〕，主要包括：insert/update/delete
数据定义语言〔DDL〕，主要包括：create/drop/alter
事务控制语言，主要包括：commit/rollback
数据控制语言，主要包括授权等等
2.5、演示数据的结构
2.5.1、怎么样可以取得演示数据表
select * from tab;
或
select table_name from user_tables;

2.5.2、如何查看表结构
desc dept;

表结构是由字段构成的，字段是有类型的
2.5.3、表结构描述
表名称：dept
描述：部门信息表
英文字段名称
中文描述
类型
DEPTNO
部门编号
NUMBER(2)
DNAME
部门名称
VARCHAR2(14)
LOC
位置
VARCHAR2(13)

表名称：emp
描述：员工信息表
英文字段名称
中文描述
类型
EMPNO
员工编号
NUMBER(4)
ENAME
员工姓名
VARCHAR2(10)
JOB
工作岗位
VARCHAR2(9)
MGR
上级经理
NUMBER(4)
HIREDATE
入职日期
DATE
SAL
薪水
NUMBER(7,2)
COMM

津贴
NUMBER(7,2)
DEPTNO
部门编号
NUMBER(2)
注：DEPTNO字段是外键，DEPTNO的值来源于dept表的主键，起到了约束的作用
表名称：salgrade
描述：薪水登记信息表
英文字段名称
中文描述
类型
GRADE
等级
NUMBER
LOSAL
最低薪水
NUMBER
HISAL
最高薪水
NUMBER
2.6、简单查询
2.6.1、查询一个字段
l 查询员工姓名
select ename from emp;

Select语句后面跟的是字段名称，select是关键字，select和字段名称之间采用空格隔开from表示将要查询的表，它和字段之间采用空格隔开
2.6.2、查询多个字段
l 查询员工的编号和姓名
select empno, ename from emp;

查询多个字段，select中的字段采用逗号间隔即可，最后一个字段，也就是在from前面的字段不能使用逗号了。
2.6.3、查询全部字段
可以将所有的字段放到select语句的后面，这种方案不方便，但是比拟清楚，我们可以采用如下便捷的方式查询全部字段
select * from emp;

采用select * from emp，虽然简单，但是*号不是很明确，建议查询全部字段将相关字段写到select语句的后面
2.6.4、计算员工的年薪
l 列出员工的编号，姓名和年薪
select empno, ename, sal*12 from emp;

在select语句中可以使用运算符，以上存在一些问题，年薪的字段名称不太明确
2.6.5、将查询出来的字段显示为中文
select empno as 员工编号, ename as 员工姓名, sal*12 as 年薪 from emp;

可以采用as关键字重命名表字段，其实as也可以省略，如：
select empno 员工编号, ename 员工姓名, sal*12 年薪 from emp;
2.7、SQL Plus常用命令
27.1、set linesize 200
set linesize 可以设置一行的字符数，默认为80个字符

以上set linesize 300,表示一行为300个字符
2.7.2、l(List)
可以显示缓存区中的最后执行的内容

2.7.3、run / r
以上三个命令功能是一致的，重新运行缓存区中的语句

2.7.4、save
save可以将最后一次在缓存区中执行的语句保存到文件

2.7.5、get
get可以将文件中的sql语句放到缓存区中，采用/或r或run，可以执行

2.7.6、ed(edit)
ed可以采用记事本来编辑缓存区中的内容

2.7.7、如何直接执行sql脚本
@c:\emp.sql;

2.8、条件查询
条件查询需要用到where语句，where必须放到from语句表的后面
支持如下运算符
运算法
说明
=
等于
<>或!=
不等于
<
小于
<=
小于等于
>
大于
>=
大于等于
between … and ….
两个值之间
is null
为null
In
包含，相当于多个or
Not
not可以取非，主要用在is 或in中
Like
like称为模糊查询，支持%或下划线匹配
%匹配任意个字符
下划线，一个下划线只匹配一个字符
28.1、等号操作符
l 查询薪水为5000的员工
select empno, ename, sal from emp where sal=5000;

l 查询job为MANAGER的员工
select empno, ename from emp where job=manager;

以上查询出现错误，因为job为字符串，所以出现了以上错误
select empno, ename from emp where job="manager";

仍然出现错误，在sql中字符串必须采用单引号，不能使用双引号
select empno, ename from emp where job='manager';

以上查询正确，但没有找到符合条件的数据，因为sql中的值是区分大小写的，但关键字不区分大小写，如以下写法等同以上写法：
SELECT empno, ename FROM emp where job='manager';

select empno, ename from emp where job='MANAGER';

以上输出正确，一定注意大小写问题及字符串必须采用单引号问题
2.8.2、<>操作符
l 查询薪水不等于5000的员工
select empno, ename, sal from emp where sal <> 5000

一下写法等同于以上写法，建议使用第一种写法
select empno, ename, sal from emp where sal != 5000;

数值也可以采用单引号引起来，如一下语句是正确的：
select empno, ename, sal from emp where sal <> '5000';
l 查询工作岗位不等于MANAGER的员工
select empno, ename from emp where job <> 'MANAGER';

2.8.3、between … and …操作符
l 查询薪水为1600到3000的员工(第一种方式，采用>和<=)
select empno, ename, sal from emp where sal >= 1600 and sal < =3000;

l 查询薪水为1600到3000的员工(第一种方式，采用between … and …)
select empno, ename, sal from emp where sal between 1600 and 3000;

关于between … and …，它是包含最大值和最小值的
2.8.4、is null
Null为空，但不是空串，为null可以设置这个字段不同填值，如果查询为null的字段，采用is null
l 查询津贴为空的员工
select * from emp where comm='';

以上无法查询出符合条件的数据，因为津贴字段为null，而不是空串，null和空串是不一样的
select * from emp where comm=null;

以上也无法查询出符合条件的数据，因为null类型比拟特殊，必须使用is来比拟
select * from emp where comm is null;

以上查询正确
2.8.5、and
and表示并且的含义，表示所有的条件必须满足
l 工作岗位为MANAGER,薪水大于2500的员工
select * from emp where job='MANAGER' and sal > 2500;

2.8.6、or
or，只要满足条件即可,相当于包含
l 查询出job为manager或者job为salesman的员工
select * from emp where job='MANAGER' or job='SALESMAN';

2.8.7、表达式的优先级
l 查询薪水大于1800，并且部门代码为20或30的员工〔错误的写法〕
select * from emp where sal > 1800 and deptno = 20 or deptno = 30;

以上输出不是预期结果，薪水小于1800的数据也被查询上来了，原因是表达式的优先级导致的，首先过滤sal > 1800 and deptno = 20，然后再将deptno = 30员工合并过来，所以是不对的
l 查询薪水大于1800，并且部门代码为20或30的〔正确的写法〕
select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);

关于运算符的问题：不用记，没有把握尽量采用括号
2.8.8、in
in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些
l 查询出job为manager或者job为salesman的员工

l 查询出薪水包含1600和薪水包含3000的员工
select * from emp where sal in(1600, 3000);

2.8.9、not
l 查询出薪水不包含1600和薪水不包含3000的员工〔第一种写法〕
select * from emp where sal <> 1600 and sal <> 3000;

l 查询出薪水不包含1600和薪水不包含3000的员工〔第二种写法
select * from emp where not (sal = 1600 or sal = 3000);

l 查询出薪水不包含1600和薪水不包含3000的员工〔第三种写法〕
select * from emp where sal not in (1600, 3000);

l 查询出津贴不为null的所有员工
select * from emp where comm is not null;

2.8.10、like
Like可以实现模糊查询，like支持%和下划线匹配
l 查询姓名以M开头所有的员工
select * from emp where ename like 'M%';

l 查询姓名以N结尾的所有的员工
select * from emp where ename like '%N';

l 查询姓名中包含O的所有的员工
select * from emp where ename like '%O%';

l 查询姓名中第二个字符为A的所有员工
select * from emp where ename like '_A%';

Like中%和下划线的差异？
%匹配任意字符出现的次数
下划线只匹配一个字符
Like 中的表达式必须放到单引号中，以下写法是错误的：
select * from emp where ename like _A%
2.9、排序数据
2.9.1、单一字段排序
排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面
l 按照薪水由小到大排序(系统默认由小到大)
select * from emp order by sal;

l 取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)
select * from emp where job='MANAGER' order by sal;

如果包含where语句order by必须放到where后面，如果没有where语句order by放到表的后面
以下写法是错误的：
select from emp order by sal where job='MANAGER';
l 按照多个字段排序，如：首先按照job排序，在按照sal排序

2.9.2、手动指定排序顺序
l 手动指定按照薪水由小到大排序
select * from emp order by sal asc;

l 手动指定按照薪水由大到小排序
select * from emp order by sal desc;

2.9.3、多个字段排序
l 按照job和薪水倒序
select * from emp order by job desc, sal desc;

如果采用多个字段排序如果根据第一个字段排序重复了，会根据第二个字段排序
2.9.3、使用字段的位置来排序
l 按照薪水升序
select * from emp order by 6;

不建议使用此种方式，采用数字含义不明确，程序不健壮
2.10、数据处理函数
Lower
转换小写
upper
转换大写
substr
取子串
length
取长度
trim
去空格
to_date
将字符串转换成日期
to_char
将日期或数字转换成字符串
to_number
将字符串转换成数字
nvl
可以将null转换成一个具体值
case
分支语句
decode
同case
round
四舍五入
2.10.1、lower
l 查询员工，将员工姓名全部转换成小写
select lower(ename) from emp;

2.10.2、upper
l 查询job为manager的员工
select * from emp where job=upper('manager');

2.10.3、substr
l 查询姓名以M开头所有的员工
select * from emp where substr(ename, 1, 1)=upper('m');

2.10.4、length
l 取得员工姓名长度为5的
select length(ename), ename from emp where length(ename)=5;

2.10.5、trim
trim会去首尾空格，不会去除中间的空格
l 取得工作岗位为manager的所有员工
select * from emp where job=trim(upper('manager '));

2.10.6、to_date
l 查询1981-02-20入职的员工〔第一种方法，与数据库的格式匹配上〕
select * from emp where HIREDATE='20-2月 -81';
l 查询1981-02-20入职的员工〔第二种方法，将字符串转换成date类型〕
select * from emp where hiredate=to_date('1981-02-20', 'YYYY-MM-DD');
等同
select * from emp where hiredate=to_date('1981-02-20 00:00:00', 'YYYY-MM-DD ');
默认为0时0分0秒

to_date可以将字符串转换成日期，具体格式to_date(字符串，匹配格式)
日期格式的说明
控制符〔不区分大小写〕
说明
YYYY
表示年
MM
表示月
DD
表示日
HH12,HH24
表示12小时制，表示24小时制
MI
表示分
SS
表示秒
2.10.7、to_char
l 查询1981-02-20以后入职的员工，将入职日期格式化成yyyy-mm-dd hh:mm:ss
select empno, ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') as hiredate from emp;

l 查询员工薪水参加千分位
select empno, ename, to_char(sal, '$999,999') from emp;

l 查询员工薪水参加千分位和保存两位小数
select empno, ename, to_char(sal, '$999,999.99') from emp;

将数字转换成字符串，格式
控制符
说明
9
表示一位数字
0
位数不够可以补零
$
美元符
L
本地货币符号
.
显示小数
,
显示千分位
2.10.8、to_number
将字符串转换成数值
select * from emp where sal>to_number('1,500', '999,999');

2.10.9、nvl
l 取得员工的全部薪水，薪水+津贴
select empno, ename, sal, comm, sal+comm from emp;

以上结果不正确，主要原因是津贴〔comm〕字段为null，所以无法计算，所以正确的做法是将津贴先转换成0，再计算。可以使用Oracle提供的nvl，该函数的语法格式为：nvl(表达式1，表达式2)，表达式1：指的是字段名称；表达式2：指的是将该字段的null转换成的值
l 采用nvl函数，取得员工的全部薪水，薪水+津贴
select empno, ename, sal, comm, sal+nvl(comm, 0) from emp;

以上结果是正确的，在做表设计的时候，关于数值字段最好不允许为null，可以设置缺省值
2.10.10、case … when … then …end
l 如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%
select empno, ename, job, sal, case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end as newsal from emp;

2.10.11、decode
同case …when …then … end
l 如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%
select empno, ename, job, sal, decode(job, 'MANAGER', sal*1.1, 'SALESMAN', sal*1.5) as newsal from emp;

2.10.12、round
四舍五入
select round(2345343.1234, 2) from dual;
Dual是oracle提供的，主要为了方便使用，因为select的时候需要用from
2.11、聚合函数
count
取得记录数
sum
求和
Avg
取平均
Max
取最大的数
min
取最小的数
2.11.1、count
l 取得所有的员工数
select count(*) from emp;

Count〔*〕表示取得所有记录，忽略null，为null值也会取得
l 取得津贴不为null员工数
select count(comm) from emp;

采用count(字段名称)，不会取得为null的记录
l 取得工作岗位的个数
select count(distinct(job)) from emp;

Distinct可以去除重复的记录
2.11.2、sum
Sum可以取得某一个列的和，如果是null会略
l 取得薪水的合计
select sum(sal) from emp;

l 取得津贴的合计
select sum(comm) from emp;

如果是null会略
l 取得薪水的合计〔sal+comm〕
select sum(sal+comm) from emp;

从以上结果来看，不正确，原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0
select sum(sal+nvl(comm, 0)) from emp;

2.11.3、avg
取得某一列的平均值
l 取得平均薪水
select avg(sal) from emp;

2.11.4、max
取得某个一列的最大值
l 取得最高薪水
select max(sal) from emp;

l 取得最晚入职得员工
select max(to_char(hiredate, 'yyyy-mm-dd')) from emp;

2.11.4、min
取得某个一列的最小值
l 取得最低薪水
select min(sal) from emp;

l 取得最早入职得员工
select min(to_char(hiredate, 'yyyy/mm/dd')) from emp;

2.11.4、组合聚合函数
可以将这些聚合函数都放到select中一起使用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

2.12、分组查询
分组查询主要涉及到两个子句，分别是：group by和having
2.12.1、group by
l 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计
select job, sum(sal) from emp group by job;

采用group by，聚合函数前面的所有字段必须参与分组
如果使用了order by，order by必须放到group by后面

l 按照工作岗位和部门编码分组，取得的工资合计
n 原始数据
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

n 分组语句
select job,deptno,sum(sal) from emp group by job,deptno;

2.12.2、having
如果想对分组数据再进行过滤需要使用having子句
取得每个岗位的平均工资大于2000
select job, avg(sal) from emp group by job having avg(sal) >2000;

分组函数的执行顺序：
1、 根据条件查询数据
2、 分组
3、 采用having过滤，取得正确的数据
2.12.3、select语句总结
一个完整的select语句格式如下
select 字段
from 表名
where …….
group by ……..
having …….
order by ……..
以上语句的执行顺序
1. 首先执行where语句过滤原始数据
2. 执行group by进行分组
3. 执行having对分组数据进行操作
4. 执行select选出数据
5. 执行order by排序
2.13、连接查询
2.13.1、SQL92语法
连接查询：也可以叫跨表查询，需要关联多个表进行查询
l 显示每个员工信息，并显示所属的部门名称
select ename, dname from emp, dept;
SQL> select ename, dname from emp, dept;

ENAME D