oracle的sql语句
从查询结果可以看出，所有的职位都被相应的中文替换了。
1.7、SQL语法练习
现在我们再次通过相应的练习对scott账户下的四张表做进一步的熟悉。
选择部门30中的所有员工。
select * from emp where deptno=30;
此查询包含一个限定条件
列出所有业务员的姓名，编号和部门编号。
select empno 编号,ename 姓名,deptno 部门编号 from emp where job='CLERK';
此查询应用了别名，单个限定条件，需要注意的是Oracle是区分大小写的，所以我们要将业务员大写为“CLERK”才能查询出数据，或者使用upper()函数。
select empno 编号,ename 姓名,deptno 部门编号 from emp where job=upper('clerk');
找出佣金高于工资的雇员信息
select * from emp where comm>sal;
此查询为单个限定条件的查询
找出佣金高于工资的60%的雇员信息
select * from emp where comm>sal*0.6;
此查询使用了比较运算符和四则运算符
找出部门10中所有经理和部门20中所有业务员的详细信息。
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
此查询使用了多限定查询，逻辑运算符and 和 or ，并且使用()将多个限定条件包含作为一个整体看待。
看一个比较复杂的需求。找出部门10中所有经理，部门20中所有业务员，既不是经理又不是业务员但是工资大于或等于2000的所有雇员信息。
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or (sal>=2000 and job not in('MANAGER','CLERK'));
找出领取佣金的雇员的不用工作。这个需求包含的信息有，工作会重复复，所以我们需要使用关键字distinct消除重复的记录，能够领取佣金说明comm is not null
select distinct job from emp where comm is not null;
找出不能领取佣金或者领取的佣金低于100的雇员信息
select * from emp where comm is null or comm<100;
找出每个月中倒数第三天雇佣的雇员信息，我们分析下，Oracle为我们提供了一个叫做last_day()的函数，它的功能是查询出指定日期的最后一天对应的日期，但是我们这里是要查询倒数第三天，如何处理?我们按照倒序的思想，将最后一天减去2不就得到了倒数第三天吗?
select * from emp where last_day(hiredate)-2=hiredate;
来一个很有趣的需求，找出早于12年前雇佣的雇员信息，我们将这个需求转变下，我们知道Oracle中有一个函数叫做months_between()，它的作用是查询出给定的日期的差值，这个差值是月份数，所以，我们这么考虑
select * from emp where months_between(sysdate,hiredate)/12>12;
看一个字符函数的应用。以首字母大写的方式显示所有雇员的姓名
select initcap(ename) from emp;
显示雇员姓名长度正好是5的雇员姓名
select ename from emp where length(ename)=5;
显示雇员姓名中不含“R”的雇员姓名
select ename from emp where ename not like '%R%';
此查询使用了like语句做模糊查询，当like后指定的关键字为“%%”时，表示查询出所有数据
显示所有雇员姓名的前三个字符
select substr(ename,0,3) from emp;
select substr(ename,1,3) from emp;
此处应该强调的是，截取点的指定中，0和1的效果是一样的。
显示所有雇员的姓名，并且用“a”替换所有的“A”
select ename,replace(ename,'A','a') from emp;
此处要强调的是replace()函数的参数中，第一个表示需要做替换操作的列名称，第二个参数是新的字符串，第三个参数表示的是原始的字符串，也就是需要替换的字符串。
显示工龄为10或以上的雇员姓名和雇佣日期
select ename,hiredate from emp where months_between(sysdate,hiredate)/12>10;
显示雇员的详细信息，并按照姓名排序
select * from emp order by ename;
显示雇员的姓名和雇佣时间，根据其工龄，将最老的雇员排在最前面
select ename,hiredate from emp order by hiredate;
显示所有雇员的姓名、工作和工资，按照工作降序排列，若工作相同则按照工资排序
select ename,job,sal from emp order by job desc,sal;
显示所有雇员的姓名、雇佣的年份，月份，按照雇佣日期所在月排序，如果月份相同则将最早的年份的雇员排在最前面。此需求首先要求出所有雇员的雇佣月份，使用to_char()函数求出月份
select ename,to_char(hiredate,'mm') month,to_char(hiredate,'yyyy') year from emp order by month,year;
显示在一个月为30天的情况下所有雇员的日薪，忽略余数。忽略余数使用round()函数完成
select ename,round(sal/30) from emp;
找出在每年2月份雇员的所有雇员信息
select * from emp where to_char(hiredate,'mm')=2;
此处还是使用了to_char()函数求出月份
对于每个雇员，显示其到今天为止的总天数
select ename,round(sysdate-hiredate) from emp;
显示雇员姓名中包含“A”的所有雇员姓名
select ename from emp where ename like '%A%';
以年月日的方式显示所有雇员的工龄。年，求出总月数/12，此时会产生小数，但是我们不能再这里进行四舍五入，而是采用trunc()函数得到整数部分
select ename,trunc(months_between(sysdate,hiredate)/12) year from emp;
现在工龄的年得到了，下面求出月份，我们知道年除完之后的余数就是月，使用取余函数进行处理
select ename ,trunc(months_between(sysdate,hiredate)/12) year,trunc(mod(months_between(sysdate,hiredate),12)) month from emp;
得到月份后，如何求出天数呢?我们知道日期-日期得到的是天数(需要做取整处理)，将总天数/30(假设每月为30天)得到的就是剩余的天数值
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;
这样就完成了上面的查询操作。
第三次
1、Oracle
1.1、多表查询
1) 多表查询的基本语法
前面的查询都是针对一张表的操作，如果在查询的时候涉及到多张表，那么就称为多表查询，夺标查询的语法如下：
select *|具体的列名 from 表名称1,表名称2 where 条件表达式 order by 排序字段 asc|desc;
下面看一个例子，对emp表和dept表进行查询
select * from emp,dept;
如此多的数据，我们要向知道当前的记录总数，如何操作呢?
select count(*) from emp,dept;--56
select count(*) from emp;--14
select count(*) from emp;--4
此处查询使用count(*|具体的列名)查询总记录数
上面的三条查询语句分别得到了多表查询，单表查询的总记录数，很明显的看到，多表查询的记录数56并不等于单表查询的总记录数之和18，怎么回事呢?因为，在进行多表查询时，会产生笛卡尔积，如果表的数据越多，那么笛卡尔积就会越大。如果现在有5张表，每张表有10000条数据，查询5张表会产生10000的5次方条数据，所以在实际开发中多表查询不建议过多的使用。
要向去掉笛卡尔积，可以使用关联来实现。现在我们知道emp表中有字段deptno，dept表中有字段deptno，emp.deptno=dept.deptno就是灌篮字段。在多表查询中加入where语句就可以消除笛卡尔积
select * from emp,dept where emp.deptno=dept.deptno;
此时查询的记录数为14条，但是如果表名过长的话，不方便我们使用，所以一般我们会为表名设置别名，
select * from emp e,dept d where e.deptno=d.deptno;
如果在进行多表查询时，最好为表名设置别名
要求查询出雇员的编号、雇员姓名、部门编号、部门名称及部门位置
select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
要求查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名
select e.ename,e.job,m.ename from emp e,emp m where e.mgr=m.empno;
此处查询将emp表做自身的关联
继续扩展之前的程序，要求将雇员素在部门名称同时列出
select e.ename,e.job,m.ename,d.dname from emp e,emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno;
查询出每个雇员的姓名，工资，部门名称，工资等级，以及领导的姓名及工资所在公司的等级
先确定工资等级表
select * from 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;
查询其领导的姓名及工资等级
select e.ename,e.sal,m.ename,decode(ms.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),decode(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),d.dname from emp e,emp m,dept d,salgrade s,salgrade ms where e.mgr=m.empno and m.sal between ms.losal and ms.hisal and e.deptno=d.deptno and e.sal between s.losal and s.hisal;
2) 左、右连接
现在我们先查询下dept表中的数据
select * from dept;
可以看到，dept表中包含了4条记录，现在我们将emp表和dept表关联查询，查询一下指定的字段
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
有查询结果可以看到，部门表中的部门号只出现了3个，因为在雇员表中没有指定40部门的雇员，但是我们现在需要查看两张表关联后的完整信息，该如何进行呢?
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno;
现在的结果中没有指定雇员的部门信息也显示出来了。这里我们使用到了右连接。有如下规律：
(+)在=左边表示右连接,查询时以=右边的表作为标准
(+)在=右边表示左连接，查询时以=左边的表作为标准
在前面我们有过如下操作：查询雇员的编号，姓名及其领导的编号、姓名
select e.empno,e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
但是我们仔细观察会发现，雇员名称为KING的雇员信息没有显示出来，我们知道KING为president，没有领导，所以按照上面的方式是不能查询出来的，修改如下：
select e.empno,e.ename,m.ename from emp e,emp m where e.mgr=m.empno(+);
发现在加入做链接后KING出现了。
3) SQL:1999语法对SQL的支持
SQL:1999语法格式如下：
select table1.column,table2.column from table1
[cross join table2]|
[natural join table2]|
[join table2 using(column_name)]|
[join table2 on(table1.column_name=table2.column_name)]|
[left|right|full outer join table2 on(table1.column_name=table2.column_name)];
交叉连接(cross join)：产生笛卡尔积
select * from emp cross join dept;--56
自然连接(natural join)：自动进行关联字段的匹配
select * from emp natural join dept;
相当于
select * from emp,dept where emp.deptno=dept.deptno;
using子句：直接关联的操作列
select * from emp e join dept d using(deptno) where deptno=30;
on子句，用户自己编写连接的条件
select * from emp e join dept d on(e.deptno=d.deptno) where d.deptno=30;
左连接(左外连接)、右连接(右外连接)：left join,right join
select e.ename,d.deptno,d.dname,d.loc from emp e right outer join dept d on(e.deptno=d.deptno);
1.2、组函数及分组统计
什么是分组?
举例吧，把所有男生分为一组，把所有女生分为一组。
如果想要求出每一组的平均身高，评价呢年龄等，就需要使用分组函数。
1) 组函数
在SQL中常用的组函数有以下几个：
count()-->求出全部的记录数
max()-->求出一组数据中的最大值
min()-->求出一组数据中的最小值
avg()-->求出平均值
sum()-->求和
count()函数：
现在我们需要查询出emp中有多少个雇员
select count(*) from emp;--14
max()、min()，求最大最小值，一般是针对数字的应用
求出所有雇员的最低工资
select min(sal) from emp;
求出所有雇员的最高工资
select max(sal) from emp;
sum()、avg()，求和，求平均值
求出部门20中的总工资
select sum(sal) from emp where deptno=20;
求出所有雇员的平均工资
select avg(sal) from emp;
2) 分组统计
要想使用分组统计，则首先应该固定其语法，使用group by进行分组，此时SQL语法格式如下：
select *|具体的列
from 表名称1
where 条件表达式
group by 分组条件
order by 排序字段 asc|desc
求出每个部门的雇员数量，这里需要按照部门编号划分，及按照deptno分组
select deptno,count(empno) from emp group by deptno;
求出每个部门的平均工资
select deptno,avg(sal)
from emp
group by deptno;
现在，我们观察下面的一行代码：
select deptno,count(empno) from emp;
以上代码不能正确执行，报错为：不是单组分组函数，为什么呢?
如果程序中使用了分组函数，则有两种可以使用的情况：
1-程序中存在了group by，并指定了分组条件，这样可以将分组条件一起查询出来。
2-如果不使用分组的话，则只能单独的使用分组函数
在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。
看下面的代码：
select deptno,empno,count(empno) from emp group by deptno;
程序会报错，提示empno不是group by表达式，因为在这里，我们使用了组函数count(empno),group by deptno,根据先前的规则，empno的出现是不合法的。
按照部门分组，并显示部门的名称，及每个部门的员工数
select d.dname,count(e.ename) from dept d,emp e where d.deptno=e.deptno group by d.dname;
要求显示出平均工资大于2000的部门编号和平均工资
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;
程序报错，提示avg(sal)>2000处不允许使用分组函数。因为分组函数只能在分组中使用，不允许出现在where语句之中，那么如果现在假设要指定分组的条件，则只能通过第二种条件的指令，having，此时SQL语法格式为：
select * | 具体的列名
from 表名称
where 条件表达式
group by 分组条件
having 分组条件
order by 排序字段 asc|desc
所以，我们使用having完成上面的操作
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
下面我们看一个这样的需求：显示非销售人员工作名称以及从事同一工作的雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按照月工资的合计升序排列
·-首先我们查询出全部的非销售人员，限定条件job<>'SALESMAN'
select * from emp where job<>'SALESMAN';
·-按照工作名称分组，并且求出每组的工资总和
select job,sum(sal) from emp
where job<>'SALESMAN'
group by job;
·-对分组的调价进行限制，工资总和大于5000
select job,sum(sal) from emp
where job<>'SALESMAN'
group by job
having sum(sal)>5000;
·-对查询结果按照月工资的合计升序排列
select job,sum(sal) from emp
where job<>'SALESMAN'
group by job
having sum(sal)>5000
order by sum(sal) asc;
下面我们总结下分组的简单原则：
--只要一列上存在重复的内容才有可能考虑到分组
使用分组时的注意点：
--分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句
例如，我们要求求出平均工资最高的部门工资
select deptno,max(avg(sal)) from emp
group by deptno;
程序会报错，提示deptno不是单组分组函数
修改代码如下：
select max(avg(sal)) from emp
group by deptno;
1.3、子查询
子查询：在一个查询的内部还包括另外一个查询，则此查询称为子查询，子查询的格式如下：
select * | 具体的列名称
from 表名称
where 条件表达式(
select * | 具体的列名称
from 表名称
where 条件表达式(
...
)
group by 分组条件
having 分组条件
order by 排序字段 asc|desc
)
group by 分组条件
having 分组条件
order by 排序字段 asc|desc
要求查询出比7654工资要高的全部雇员信息
·-首先要知道7654雇员的工资是多少
select sal from emp where empno=7654;
·-上面查询的结果作为最后查询的子查询结果，只要是其他的工资大于上面的查询结果，则表示符合条件。
select * from emp where sal>(select sal from emp where empno=7654);
应该要强调的是，所有的子查询语句必须在“()”中编写。
子查询在操作上分为三类：
单列子查询：返回的结果是某列的一个内容，出现的几率最高
单行子查询：返回多个列，有可能是一条完整的记录
多行子查询：返回多条记录
要求查询出工资比7654高，同时与7788从事相同工作的全部雇员信息
·-查询出7654的工资
select sal from emp where empno=7654;
·-查询出7788的工作名称
select job from emp where empno=7788;
·-总和查找
select * from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788);
要求查询出工资最低的雇员姓名，工作，工资
·-求出最低工资
select min(sal) from emp;
·-以最低工资为条件进一步查询
select ename,job,sal from emp
where sal=(select min(sal) from emp);
要求查询出：部门名称，部门的员工数，部门的平均工资，部门的最低收入的雇员姓名，此时，程序需要两张表关联：dept、emp
·-如果要想求出每个部门的员工数，平均工资，要使用分组统计，这里我们按照deptno进行分组
select deptno,count(empno),avg(sal) from emp
group by deptno;
·-但是我们要查询的是部门的名称，所以这里需要与dept表进行关联
select d.dname,ed.c,ed.a
from dept d,
(select deptno,count(empno) c,avg(sal) a from emp
group by deptno) ed
where d.deptno=ed.deptno;
·-求出最低收入的雇员姓名
select d.dname,ed.c,ed.a,e.ename
from dept d,(select deptno,count(empno) c,avg(sal) a,min(sal) min from emp
group by deptno) ed,emp e
where d.deptno=ed.deptno and e.sal=ed.min;
但是此程序中有一个问题，如果一个部门中同时存在两个给你工资最低的雇员，则程序会出现错误。
在子查询中，存在以下三种查询的操作符号：
in、any、all
in操作符的作用是指定一个查询的范围
求出每个部门的最低工资的雇员信息。
分析：每个部门的最低工资，返回值肯定是多个，所以此时可以使用in指定一个操作范围。
select * from emp
where sal in(select min(sal) from emp group by deptno);
any操作符的一般用法：=any(与in操作符的功能完全一样)、>any(比里面最小的值要大)、<any(比里面最大的值要小)
select * from emp
where sal=any(select min(sal) from emp group by deptno);
all操作符的一般用法：>all(比最大值要大)、<all(比最小值要小)
select * from emp
where sal>all(select min(sal) from emp group by deptno);
对于子查询来讲，还可以进行多列子查询，一个子查询中同时返回多个查询的列。
select * from emp
where (sal,nvl(comm,-1)) in(select sal,nvl(comm,-1) from emp where deptno=20);
1.4、数据库更新操作
数据库的主要操作分为两种：
--数据库的查询操作：select
--数据库的更新操作：insert、update、delete
此时，为了保存原始的emp表的信息，在进行增加、修改、删除操作之前，先将此表复制一份
create table myemp as select * from emp;
1) 添加数据
添加数据的语法是：
insert into 表名称[(字段名1,字段名2,......)] values(值1,值2,......);
为myemp表添加一条新的记录，按照标准的做法完成
insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7899,'张三','清洁工',7369,'14-2月 -1995',9000,300,40);
在添加数据时，需要强调的是：对于数字，不用加单引号，但是字符串必须加上单引号。可以使用简略写法，当我们想全部的阻断插入数据时，可以不用指定需要插入数据的字段名，但是，我们并不推荐这种写法，因为这样写的坏处在于，当我们向部分字段插入数据时，需要和表的字段顺序一一对一个才能插入成功，而使用标准写法时，只需要指定的字段名和value中的值一一对应就可以完成插入操作。
插入部分数据，现在要求插入一个新的雇员，但是此雇员暂时没有领导，也没有奖金，也就是说，在插入数据的时候，mgr和comm字段的值要置空。
第一种做法：不明确写出要插入的字段名 ，没有数据的字段写成null
insert into myemp values(8889,'王五','清洁工',null,'14-2月 -1982',9000,null,40);
第二种做法：明确写出要插入的字段名
insert into myemp(empno,ename,job,hiredate,sal,deptno) values(8889,'王五','清洁工','14-2月 -1982',9000,40);
在上面的插入操作中，我们应该发现了这么一个问题，插入的日期都是按照Oracle默认的日期格式进行书写的，但是，现在有一个“2009-01-19”这样格式的日期，该如何插入到数据库表中呢?我们英爱还记得在前面学习单行函数的时候，介绍了一个叫做to_date()的函数，该函数的功能是将一个字符串类型的数据变为date类型的数据。
insert into myemp(empno,ename,job,hiredate,sal,deptno) values(8888,'赵六','保洁工',to_date('2009-01-19','yyyy-mm-dd'),9000,40);
2) 修改数据
在SQL语法中使用update语句就可以完成数据的修改功能，此语句的语法格式如下：
修改全部：update 表名称 set 要修改的字段=新值,要修改的字段=新值,......;
修改局部：update 表名称 set 要修改的字段=新值,要修改的字段=新值,...... where 修改条件;
但是，从一般的开发角度上讲，我们都在修改数据时加上修改条件
现在将myemp表中的所有雇员的奖金修改为1000-->修改全部
update myemp set comm=1000;
将编号为7899的雇员的工资修改为5000-->指定了更新条件
update myemp set sal=5000 where empno=7899;
将编号为7899的雇员的领导取消
update myemp set mgr=null where empno=7899;
注意点：在进行数据库更新的操作时，一定要写上更新的条件，拒绝批量更新。
将7369、8899、7788的领导及奖金取消
update myemp set mgr=null,comm=null where empno in(7369,7788,8899);
3) 删除数据
在SQL语法中可以使用delete命令删除记录，语法格式如下：
删除全部：delete from 表名称;
删除局部：delete from 表名称 where 删除条件;
删除编号是7899的雇员信息
delete from myemp where empno=7899;
删除编号是8889,7889,8888的雇员信息
delete from myemp where empno in(8889,7889,8888);
删除掉全部领取奖金的雇员
delete from myemp where comm is not null;
删除表的全部内容，此时不需要指定删除条件
delete from myemp;
在实际开发中不建议使用全部删除，在执行删除命令的时候都要指定删除条件。
1.5、事务处理
创建一个只包含10部门雇员信息的临时表
create table emp10 as select * from emp where deptno=10;
删除emp10中7782的雇员信息
delete from emp10 where empno=7782;
当我们再次查询emp10表的数据时，该数据确实删除了，接下来，我们做如下操作，再次开启一个sqlplus窗口，再次查询emp10的数据，我们发现雇员编号为7782的雇员信息仍然存在，这是为什么?
这就是Oracle中事务处理的概念了。
事务处理：所谓事务处理就是保证数据的完整性，所有的操作要么同时成功，要么同时失败。
在Oracle中对于每一个连接到数据库的窗口(sqlplus、sqlplusw)连接之后实际上都会与数据库建立一个session，即：每一个连接到数据库上的用户表示创建了一个session。
一个session对数据库所做的修改，不会立刻反映到数据库的真实数据之上，是允许回滚的，当一个session提交所有的操作之后，数据库才真正的做出了修改。
在数据库的操作中提供了以下两个主要命令完成事物的处理：
--提交事物：commit
--回滚事物：rollback
如果数据已经被提交了则肯定无法回滚，所以，回滚只有在事物未被提交时才有效。
在Oracle中关于事物的处理上夜壶存在一种死锁的概念。
一个session如果更新了数据库中的记录，其他session是无法立刻更新的，要等待对方提交之后才允许更新。
下面我们来测试下Oracle的事务处理是如何进行的。
首先，我们在窗口1中查询出emp10的数据
select * from emp10;
现在做一个更新的操作，将编号为7934的雇员的工资更改为3000
update emp10 set sal=3000 where empno=7934;
现在我们再次查询emp10的数据，发现，编号为7934的雇员工资确实更改为3000来 ，但是我们应该知道，对于这个更新操作，我们是没有提交的，我们现在再打开一个窗口，查询emp10的数据，发现编号为7934的雇员工资还是1300，这就验证了我们所说的“事物未提交”，接下来，我们在窗口2中进行一个更新操作，将7839的奖金设置为10000
update emp10 set comm=10000 where empno=7839;
下面我们在窗口1中提交事物
在窗口2中再次查询emp10的数据，更新成功。
在窗口2中提交事物
同样在窗口1中查询emp10的数据，更新成功。
1.6、查询练习
列出至少有一个员工的所有部门
select deptno ,count(empno) from emp
group by deptno;
此查询使用了组函数，分组，注意，如果不加分组，该程序会报错
列出部门人数大于1的所有部门编号
select deptno,count(empno) from emp
group by deptno having count(empno)>1;
这里没使用where设置查询限定条件，因为where子句中时不能出现函数的。
通过部门表，查询出部门的信息
select d.*,ed.cou from dept d,(select deptno,count(empno) cou from emp
group by deptno) ed
where d.deptno=ed.deptno;
列出工资比“SMITH”多的所有雇员。
--求出SMITH的工资
select sal from emp where ename='SMITH';
--将上面的结果作为查询条件，只要工资比上面的结果大的就是符合条件的
select * from emp where sal>(select sal from emp where ename='SMITH');
列出所有员工的姓名和其直接上司的姓名
--此程序属于自身关联查询，为了得到KING，我们使用左连接查询，以等号左边的表为标准
select e.ename,m.ename from emp e,emp m
where e.mgr=m.empno(+);
列出雇佣日期早于其直接上级的所有雇员的编号、姓名和部门名称
--自身关联，查找mgr=empno的同时还要比较hiredate，我们先查询编号和姓名
select e.empno,e.ename
from emp e,emp m
where e.mgr=m.empno and e.hiredate<m.hiredate;
--如果要加入部门名称，我们需要加入dept表，做表关联查询
select e.empno,e.ename,d.dname
from emp e,emp m,dept d
where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;
列出部门名称和这些部门的雇员信息，同时列出那些没有雇员的部门
--左右关联问题，以=右边的表为标准
select d.deptno,d.dname,e.empno,e.ename
from dept d,emp e
where d.deptno=e.deptno(+);
列出所有“CLERK”的姓名和其部门名称，部门的人数
--找出所有CLERK的姓名和部门编号
select ename,deptno from emp
where job='CLERK';
--要想查询部门名称，则必须使用dept表
select e.ename,d.dname from emp e,dept d
where job='CLERK' and e.deptno=d.deptno;
--部门人数要用到分组完成，一旦使用分组，肯定是group by
select e.ename,d.dname,ed.cou
from emp e,dept d,(select deptno, count(empno) cou from emp group by deptno) ed
where job='CLERK' and e.deptno=d.deptno and ed.deptno=e.deptno;
列出最低工资大于1500的各种工作以及从事此工作的全部雇员人数
--按工作分组，分组条件是最低工资大于1500
select job,min(sal) from emp
group by job having min(sal)>1500;
--工作就出来了，之后再求全部的雇佣人数
select e.job,count(e.empno)
from emp e
where e.job in(
select job from emp
group by job having min(sal)>1500
)
group by e.job;
列出在部门“SALES”工作的员工姓名，假定不知道销售部的部门编号
--通过dept表查询出销售部的部门编号
select deptno from dept where dname='SALES';
--将上面的查询结果作为下一步查询的条件
select ename from emp
where deptno=(
select deptno from dept where dname='SALES'
);
列出工资高于公司平均工资的所愿雇员，所在部门，上级领导，公司的工资等级
--求出公司的平均工资
select avg(sal) from emp;
--列出工资高于平均工资的雇员信息
select * from emp where sal>(select avg(sal) from emp);
--与部门表关联，查出所在部门的信息
select e.*,d.dname,d.loc from emp e,dept d
where sal>(select avg(sal) from emp) and e.deptno=d.deptno;
--要想查出上级领导，需要和自身进行关联查询
select e.empno,e.ename,m.ename,m.empno,d.deptno,d.dname,d.loc
from emp e,dept d,emp m
where e.sal>(select avg(sal) from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+);
--与工资等级表关联，查出工资等级
select e.empno,e.ename,s.grade,m.ename,m.empno,d.deptno,d.dname,d.loc
from emp e,dept d,emp m,salgrade s
where e.sal>(select avg(sal) from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.sal between s.losal and s.hisal;
列出与scott从事相同工作的所有雇员及部门名称
--找到scott的工作
select job from emp where ename='SCOTT';
--找到和上面查询工作相同的雇员
select * from emp where job=(select job from emp where ename='SCOTT');
--使用dept表查询出所在部门名称
select e.*,d.dname from emp e,dept d
where job=(select job from emp where ename='SCOTT')
and ename!='SCOTT'
and d.deptno=e.deptno;
列出工资等于部门30中雇员的工资的所有雇员姓名和工资
--查出部门30中雇员的工资
select sal from emp where deptno=30;
--找出工资等于上面结果的雇员姓名
select ename,sal from emp
where sal in(select sal from emp where deptno=30)
and deptno!=30;
列出工资高于在30部门工作的所有雇员的工资的雇员姓名和工资，部门名称
--在之前的程序上进行修改，使用>all，比最大的还要大
select ename,sal
from emp where sal>all(
select sal from emp where deptno=30
)
and deptno!=30;
--使用dept表，查询出部门名称
select e.ename,e.sal,d.dname from emp e,dept d
where sal>all(
select sal from emp where deptno=30
)
and e.deptno!=30
and e.deptno=d.deptno;
列出在每个部门工作的雇员数量，平均工资和平均工龄
--求出每个部门的雇员数量，按照部门名称分组
select d.dname,count(e.empno)
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
--求出每个部门的平均工资和工龄
select d.dname,count(e.empno),avg(e.sal),avg(months_between(sysdate,hiredate)/12) 年
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
列出所有雇员的姓名、部门名称和工资
--直接两张表关联
select e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno;
列出所有部门的详细信息和部门的人数
--列出每个部门的雇员人数
select deptno,count(empno) cou
from emp
group by deptno;
--把以上的查询作为一张临时表
select d.*,ed.cou from dept d,
(select deptno dto,count(empno) cou from emp group by deptno ) ed
where d.deptno=ed.dto;
查询结果中没包含40部门，修改如下
select d.*,nvl(ed.cou,0) from dept d,
(select deptno dto,count(empno) cou from emp group by deptno ) ed
where d.deptno=ed.dto(+);
列出各种工作的最低工资及从事此工作的雇员姓名
--按照工作分组，使用min()求出最低工资
select job,min(sal) from emp group by job;
--按照工资查询出雇员的信息
select * from emp
where sal in(select min(sal) from emp group by job);
列出各个部门的MANAGER的最低工资
select deptno,min(sal)
from emp
where job='MANAGER'
group by deptno;
列出所有雇员的年薪，按照年薪从低到高排序
select ename,(sal+nvl(comm,0))*12 yearsal from emp order by yearsal asc;
查询某个雇员的上级主管，并求出这些主管中的薪水超过3000的
select distinct m.* from emp e,emp m
where e.mgr=m.empno and m.sal>3000;
求出部门名称中带“S”的部门雇员的工资合计和部门人数
--查询部门表的部门名称，使用模糊查询，来确定部门的编号
select deptno from dept where dname like '%S%';
--查询出符合上述条件的雇员工资合计和部门人数
select deptno,sum(sal),count(empno) from emp
where deptno in(select deptno from dept where dname like '%S%')
group by deptno;
第四次
1、Oracle
1.1、 创建和管理表
1) 常用的数据类型
varchar\varchar2-->表示的是一个字符串，有长度限制，255,
number-->number(n):表示一个整数，数字的长度是n，可以使用int
number(m,n):表示一个小数，数字小数长度为n，整数长度为m-n，可以使用float
date-->表示日期的类型，日期要按照标准的日期格式进行存放
clob-->大对象，表示大文本数据，一般可以存放4G的文本
blob-->大对象，表示二进制数据，最大可以存放4G，如：电影，歌曲，图片
2) 表的建立
表的建立还是按照标准的语法进行，但是在表的建立时有时候会指定约束，那么此处先给出一个建立表的简单语法。
create table 表名称(
字段名称1 字段类型 [default 默认值],
字段名称2 字段类型 [default 默认值],
....
字段名称n 字段类型 [default 默认值]
)
在前面我们使用了一种这样的语法来创建表：
create table 表名称 as (子查询)--将子查询的结果作为一张表
如果现在子查询写的是：select * from emp;表示将表结构和表的内容一起复制
如果现在子查询写的是：select * from emp where 1=2;加入了一个永远都不会成立的条件，则此时表示我们复制的只是表的结构，不复制表的内容
复制表结构：
create table temp as(select * from emp where 1=2);
3) 表的删除
表的删除语法如下：
drop table 表名称;
4) 表的修改
在SQL语法操作中，提供了alter指令，通过alter指令就可以增加新的列
为emp表添加一个address列
alter table emp add(address varchar2(200) default'暂无地址');
修改emp表中的ename，将长度改为50
alter table emp modify(ename varchar2(50));
5) 为表重命名
在Oracle中提供了rename命令，可以为表重新进行命名，但是此语句只能在Oracle中使用。语法格式如下：
rename 旧的表名称 to 新的表名称;
6) 截断表
如果现在我们需要清空一张表的数据，但是同时不需要回滚，可以立刻释放资源就需要使用截断表的语法：
truncate table 表名称;
思考下面的问题：现在有一张国家表，里面只有一个国家名称的字段，内容如下：“中国、美国、巴西、荷兰“，现在要求通过查询实现对战功能：
中国->美国
中国->巴西
中国->荷兰
美国->中国
美国->巴西
美国->荷兰
......
分析：本程序只能使用笛卡尔积完成
首先，建立一张表
create table national(
name varchar2(30)
)
向表中增加测试数据
insert into national(name) values('中国');
insert into national(name) values('美国');
insert into national(name) values('巴西');
insert into national(name) values('荷兰');
查询的时候表自己和自己关联
select n1.name,n2.name from national n1,national n2 where n1.name<>n2.name;
1.2、约束
在数据库表的开发中，余数是必不可少的支持。使用约束可以更好的保证数据库中的数据完整性。
数据库中的约束分类：
--在实际中，约束主要分为以下五种：
···主键约束primary key：主键表示是一个唯一的标识，本身是不能为空的
|-例如：身份证号是唯一的，不可重复，不可为空
···唯一约束unique：在一个表中只允许建立一个主键约束，而其他列如果不希望出现重复值的话，则可以使用唯一约束。
···检查约束：检查一个列的内容是否合法
|-例如：年龄。只能在0~150之间
|-例如：性别，只能是男、女、中性
···非空约束：姓名这样的字段里面的内容就不能为空
···外键约束：在两张表中进行约束操作。
1) 主键约束(primary key)
主键约束一般都是使用在id上，而且本身已经默认了内容不能为空，主键约束可以再建表的时候指定
现在我们建立一张person表，在pid上增加主键约束
drop table person;
create table person(
pid varchar2(18) primary key,
name varchar2(200),
age number(3),
birthday date,
sex varchar2(3) default '男'
)
现在我们向表中插入数据
insert into person(pid,name,age,birthday,sex) values('1111111111111111','张三',30,to_date('1976-08-09','yyyy-mm-dd'),'女');
insert into person(pid,name,age,birthday,sex) values('1111111111111111','李四',30,to_date('1976-08-04','yyyy-mm-dd'),'男');
当插入第二条语句时，会提示：违反唯一约束，那么我们将pid的值设置为null
insert into person(pid,name,age,birthday,sex) values(null,'李四',30,to_date('1976-08-04','yyyy-mm-dd'),'男');
同样会提示错误：无法将 NULL 插入 ("SCOTT"."PERSON"."PID")，以上的约束是系统自动分配好的约束名称，也可以通过constraint指定一个约束的名字，
将person表的pid指定名称
drop table person;
create table person(
pid varchar2(18),
name varchar2(200),
age number(3),
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid)
)
2) 非空约束(not null)
使用非空约束，表示一个字段的内容不允许为空，即：插入数据的时候必须插入内容
drop table person;
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid)
)
3) 唯一约束(unique)
表示一个字段中的内容是唯一的，其他列不允许重复。
假设：现在姓名不允许出现重名的情况
drop table person;
create table person(
pid varchar2(18),
name varchar2(200) unique not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid)
)
4) 检查约束(check)
使用检查约束来判断一个列中插入的内容是否合法，例如，年龄的取值范围，性别的取值范围
drop table person;
create table person(
pid varchar2(18),
name varchar2(200) unique not null,
age number(3) not null check(age between 0 and 150),
birthday date,
sex varchar2(3) default '男' check(sex in('男','女','中')),
constraint person_pid_pk primary key(pid)
)
5) 主-外键约束(foreign key)
之前的约束都是针对一张表的，那么主-外键约束是针对两张表的约束。为什么需要主-外键约束呢?
要求完成一个程序，一本书只属于一个人
书本身应该是一张表，一本书中必然有一个字段表示属于某个人的
drop table person;
drop table book;
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid),
constraint person_name_uk unique(name),
constraint person_age_ck check(age between 0 and 150),
constraint person_sex_ck check(sex in('男','女','中'))
);
create table book(
bid number primary key not null,
bname varchar2(20),
bprice number(5,2),
pid varchar2(18)
);
insert into person(pid,name,age,birthday,sex) values('1111111111111111','张三',30,to_date('1976-08-09','yyyy-mm-dd'),'女');
insert into book(bid,bname,bprice,pid) values(1,'JAVA SE',89.9,'0000000000000');
在插入第二条数据前，我们看看pid字段的值，很明显，在我们的person表中不存在这样的person，那么， 这样的数据时不应该插入到数据库中的，为了解决这样的问题，我们使用主-外键关联，关联之后字表的数据要跟随父表的数据内内容。
drop table person;
drop table book;
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid),
constraint person_name_uk unique(name),
constraint person_age_ck check(age between 0 and 150),
constraint person_sex_ck check(sex in('男','女','中'))
);
create table book(
bid number primary key not null,
bname varchar2(20),
bprice number(5,2),
pid varchar2(18),
constraint person_book_pid_fk foreign key(pid) references person(pid)
);
现在我们再次执行数据的插入操作，此时系统提示：违反完整约束条件 (SCOTT.PERSON_BOOK_PID_FK) - 未找到父项关键字，根据上面的分析没我们修改如下：
insert into book(bid,bname,bprice,pid) values(1,'JAVA SE',89.9,'1111111111111111');
此时插入数据成功。
在使用主-外键关联的时候有几点需要注意：
|-在子表中设置的外键在父表中必须是主键
|-删除时应该先删除子表，再删除父表
在主-外键关联中也可以使用级联删除
以现有数据库中的数据为例
delete from person where pid='1111111111111111';
要删除person表中编号为1111111111111111的人员，但是这个人在book表中存在一本书的记录。提示错误：违反完整约束条件 (SCOTT.PERSON_BOOK_PID_FK) - 已找到子记录，那么，如果想删除成功必须先删除book表中pid对应的记录，再删除此人的信息
如果我们希望一个表中的数据在删除时，可以自动删除掉其对应的子表记录，则可以使用级联删除来实现。
drop table person;
drop table book;
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid),
constraint person_name_uk unique(name),
constraint person_age_ck check(age between 0 and 150),
constraint person_sex_ck check(sex in('男','女','中'))
);
create table book(
bid number primary key not null,
bname varchar2(20),
bprice number(5,2),
pid varchar2(18),
constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade
);
6) 修改约束
如果一张表已经建立完成，则可以为其添加约束
关于约束类型的命名，一定要统一：
--primary key-->主键字段_pk
--unique-->字段_uk
--check-->字段_ck
--foreign key-->父字段_子字段_fk
为person添加一个约束
alter table person add constraint person_pid_pk primary key(pid);
将person的主键约束删除掉该怎么操作呢?
alter table person drop constraint person_pid_pk;
1.3、rownum
rownum：表示行号，实际上这是一个列的列名，但是这个列我们称为伪列，此列尅在每张表中出现。
例如，在我们查询雇员表的时候，加上rownum这个列名称
select rownum,empno,ename,job,sal,hiredate from emp;
从执行的效果来看，rownum本身采用自动编号的形式出现。
我们扩展下rownum的应用，现在我们只想显示前5条雇员信息，该如何实现呢?
select rownum,empno,ename,job,sal,hiredate from emp where rownum<=5;
既然可以查询前5条数据，那么，我们现在要求提高了，查询中间的5条数据
select rownum,empno,ename,job,sal,hiredate from emp where rownum between 5 and 10;
看似没有问题的语句却查不出数据来，到底哪里出错了呢?
如果现在要想进行中间的截取操作，则只能采用子查询，例如现在假设每页显示5条，第二页应该显示6~10条，那么对于数据库操作来讲，它在查询的时候应该首先查询出1~10条，之后再在查询的结果中截取出后5条。
select * from (select rownum m,empno,ename,job,sal,hiredate from emp where rownum<=10) temp where temp.m>5;
如果现在要求输出最后的4条呢?
select * from (select rownum m,empno,ename,job,sal,hiredate from emp where rownum<=15) temp where temp.m>10;
1.4、集合操作
在Oracle中提供了三种类型集合的操作，并(union)、交(intersect)、差(minus)
|-union：将多个查询的结果组合到一个查询结果中，没有重复的内容
|-union all：将多个查询结果组合到一个查询之中，但是包含了重复值
|-intersect：返回多个查询结果中相同的部分
|-minus：返回两个查询结果的差集
为了更好的观察查询结果，我们将复制emp表，将20部门的雇员信息取出来
create table emp20 as select * from emp where deptno=20;
1) 验证union
返回两个集合的所有内容，不包含重负的内容
select * from emp
union
select * from emp20;
2) 验证union all
返回两个集合的所有内容，包含重复内容
select * from emp
union all
select * from emp20;
3) 验证intersect
返回多个查询结果中相同的部分
select * from emp
intersect
select * from emp20;
因为两张表中只有20部门的雇员信息是重复的，所有实际上返回的相同内容就是表emp20的内容
4) 验证minus
返回两个查询结果的差集
select * from emp
minus
select * from emp20;
1.5、交表、约束、查询综合练习
题目背景：
有某个学生运动会比赛信息的数据库，保存了如下的表：
|-运动员sporter(运动员编号sporterid，运动员姓名name，运动员性别sex，所属系号department)
|-项目item(项目编号itemid，项目名称itemname，项目比赛地点location)
|-成绩grade(运动员编号sporterid，项目编号itemid，积分mark)
功能要求
1) 建表
--定义各个表的主码外码约束
--运动员的姓名和所属系别不能为空值
--积分要么为空值，要么为6、4、2、0，分别代表第一、第二、第三和其他名次的积分
create table sporter(
sporterid number(4) primary key not null,
name varchar2(50) not null,
sex varchar2(3) not null,
department varchar2(30) not null,
constraint sporter_sex_ck check(sex in('男','女'))
);
create table item(
itemid varchar2(4) primary key not null,
itemname varchar2(50) not null,
location varchar2(50) not null
);
create table grade(
sporterid number(4),
itemid varchar2(4),
mark number(2),
constraint sporter_grade_sporterid_fk foreign key(sporterid) references sporter(sporterid) on delete cascade,
constraint sporter_item_itemid_fk foreign key(itemid) references item(itemid) on delete cascade,
constraint grade_mark_ck check(mark in(6,4,2,0))
);
2) 数据
运动员sporter
insert into sporter(sporterid,name,sex,department) values(1001,'李明','男','计算机系');
insert into sporter(sporterid,name,sex,department) values(1002,'张三','男','数学系');
insert into sporter(sporterid,name,sex,department) values(1003,'李四','男','计算机系');
insert into sporter(sporterid,name,sex,department) values(1004,'王二','男','物理系');
insert into sporter(sporterid,name,sex,department) values(1005,'李娜','女','心理系');
insert into sporter(sporterid,name,sex,department) values(1006,'孙俪','女','数学系');
项目item
insert into item(itemid,itemname,location) values('x001','男子五千米','一操场');
insert into item(itemid,itemname,location) values('x002','男子标枪','一操场');
insert into item(itemid,itemname,location) values('x003','男子跳远','二操场');
insert into item(itemid,itemname,location) values('x004','女子跳高','二操场');
insert into item(itemid,itemname,location) values('x005','女子三千米','三操场');
积分grade
insert into grade(sporterid,itemid,mark) values(1001,'x001',6);
insert into grade(sporterid,itemid,mark) values(1002,'x001',4);
insert into grade(sporterid,itemid,mark) values(1003,'x001',2);
insert into grade(sporterid,itemid,mark) values(1004,'x001',0);
insert into grade(sporterid,itemid,mark) values(1001,'x003',4);
insert into grade(sporterid,itemid,mark) values(1002,'x003',6);
insert into grade(sporterid,itemid,mark) values(1004,'x003',2);
insert into grade(sporterid,itemid,mark) values(1005,'x004',6);
insert into grade(sporterid,itemid,mark) values(1006,'x004',4);
3) 要求
求出目前总积分最高的系名，及其积分
--所有的系名都在sporter表中，而积分在grade表中，所以sporter和grade进行关联查询
select s.department,sum(g.mark) sum
from sporter s,grade g
where s.sporterid=g.sporterid
group by s.department
order by sum desc;
--使用rownum最方便
select * from(
select s.department,sum(g.mark) sum
from sporter s,grade g
where s.sporterid=g.sporterid
group by s.department
order by sum desc)
where rownum=1;
第五次
1、Oracle数据库
1.1、视图
视图的功能：一个视图实际上就是封装了一条复杂的查询语句
创建视图的语法如下：
create view 视图名称 as 子查询
|-实际上此时的子查询就表示一条非常复杂的查询语句
建立一个视图：此视图包含了全部的20部门的雇员信息(雇员编号，姓名，工作，雇佣日期)
create view empv20 as select empno,ename,job,hiredate from emp where deptno=20;
视图创建完成之后，就可以像查找表那样直接对视图进行查询的操作了。
select * from empv20;
此时，我们通过视图查询出20部门的雇员信息，也就是，可以使用视图包装的查询语句完成我们的操作。但是，我们思考下，现在这个视图中同样只包含了4个字段的信息，如果，现在希望多包含一个字段呢?
create view empv20 as select empno,ename,job,sal,hiredate from emp where deptno=20;
此时，系统会报错，名称已有现有对象使用。也就是说，该名称的视图已经存在了，所以，在创建视图的时候是不允许重名的，那么，我们只能先删除掉这个视图然后进行新视图的创建。该如何删除视图呢?
drop view 视图名称;
所以，类似于删除表的操作，我们将上面创建的视图empv20删除
drop view empv20;
删除成功后，重新执行创建视图的语句
create view empv20 as select empno,ename,job,sal,hiredate from emp where deptno=20;
但是，我们应该发现，如果所有的代码都这样去写肯定很麻烦，因为如果要想对视图进行修改操作，则肯定先要删除掉视图，再进行新视图的创建才能达到目的，所以在Oracle中为了方便用户修改视图，提供了一种替换的命令，此时完整的视图创建语法如下：
create or replace 视图名称 as 子查询;
按照上面的语法格式，我们在更改视图的时候就不需要先执行删除操作了，系统会为用户自动进行删除及重建的功能。
create or replace view empv20 as select empno,ename,job,sal,hiredate from emp where deptno=20;
此时，系统不会提示任何错误，表示该视图删除及创建成功。
我们说视图实际上是封装了一个非常复杂的查询语句。下面我们使用视图来封装一个非常复杂的查询。此查询返回部门名称、部门人数、平均工资以及最低工资的雇员姓名。首先看看以前的写法
select d.dname,ed.c,ed.a,e.ename
from dept d,
(
select count(empno) c,deptno,avg(sal) a,min(sal) min from emp e
group by deptno
) ed,emp e
where d.deptno=ed.deptno and e.sal=ed.min;
如果在开发中每次都写如此之长的SQL语句，则肯定很不方便，所以此时就可以通过建立师视图简化操作，方便用户做查询。
create or replace view myempv as
select d.dname,ed.c,ed.a,e.ename
from dept d,
(
select count(empno) c,deptno,avg(sal) a,min(sal) min from emp e
group by deptno
) ed,emp e
where d.deptno=ed.deptno and e.sal=ed.min;
在以后的操作中只需要查询该视图就可以得到结果，而不需要再次执行那么长的SQL语句。
创建一个只包含20部门的雇员视图
create or replace view mepv20
as
select * from emp where deptno=20;
视图创建成功。下面进行视图的更新操作，我们应该记住，在视图中是不应该包含真实数据的，而且在此程序中，创建的视图实际上是存在创建条件的，此条件是deptno=20.如果现在将视图中的7369的部门编号修改为30呢?
update empv20 set deptno=30 where empno=7369;
更新成功，现在我们查询该视图，
select * from mepv20;
发现在视图中已经没有7369这个雇员了。那么，在我们的原始表emp中呢?
select * from emp;
发现在emp表中的编号为7369的雇员的部门编号已经修改为30，我们思考下，这样的更新操作合适吗?很明显，是不合适的，我们在创建视图的时候是有条件的，一旦修改之后，该条件被破坏。所以在创建视图的时候SQL中提供了两个很重要的参数：
|-with check option：不能更新视图的创建条件
下面我们在视图创建中使用此参数
create or replace view empv20
as
select * from emp where deptno=20
with check option;
我们再对创建的视图进行更新操作
update mepv20 set deptno=30 where empno=7566;
此时，系统报错，提示：视图 with check option where 子句违规
很明显，创建条件不能更新，那么其他字段呢，例如：现在将7566的雇员姓名修改为“约翰”
update empv20 set ename='约翰' where empno=7566;
更新成功，也就是说在使用了上述的with约束后，在更新视图时，除了创建条件不能更新其他字段均可以更新。
但是，我们说视图本身的作用还是用来查询的，所以不应该允许修改，所以此时可以使用第二个参数：
|-with read only：创建的视图只读，即只能读取操作
创建只读视图
create or replace view empv20
as
select * from emp where deptno=20
with read only;
再次执行更新操作，更新雇员的姓名
update empv20 set ename='约翰' where empno=7566;
提示错误：无法对只读视图进行DML操作。
1.2、序列
在很多数据库系统中都存在一个自动增长的列，如果现在要想在Oracle中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理。
序列的创建格式：
create sequence sequence
[increment by n][start with n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[cycle | nocycle]
[{cache n | nocache}];
创建一个myseq的序列，验证自动增长的操作
create sequence myseq;
序列创建完成之后，所有的自动增长应该由用户自己处理，在序列中提供了以下两种操作：
|-nextVal：取得序列的下一个内容
|-currVal：取得序列的当前内容
现在我们先建立一张用于验证序列的表
create table testseq(
next number,
curr number
);
下面向表中添加数据，添加数据的时候需要手工使用序列
使用序列插入数据
insert into testseq(next,curr) values(myseq.nextval,myseq.currval);
将以上的插入语句执行5次
我们查询下testseq表，看看序列的变化
select * from testseq;
从查询结果中我们发现，nextval的内容始终在进行自动增长的操作，而curr使用取出当前操作的序列结果，也就是说，现在的这种序列，每次增长的幅度是1，那么也可以修改序列的增长幅度。
可以使用以下的一个参数：
|-每次增长长度：increment by 长度
重新建立序列
drop sequence myseq;
create sequence myseq increment by 2;
此时，序列已经正常的创建，创建之后来测试下，序列的操作，重新创建testseq表
drop table testseq;
create table testseq(
next number,
curr number
);
重新进行数据的插入操作，插入5次
insert into testseq(next,curr) values(myseq.nextval,myseq.currval);
再次查询testseq表，观察序列的变化
select * from testseq;
从序列的结果来看，每次取得都是奇数。
默认情况下，序列从1开始的，那么可以使用start with来指定其开始的位置
drop sequence myseq;
create sequence myseq increment by 2 start with 10;
这里指定序列开始点为10，以后直接从10开始进行序列的计算。
下面我们重新创建下该序列，让其取值固定在1、3、5、7、9，并循环序列
drop sequence myseq;
create sequence myseq
maxvalue 10
increment by 2 start with 1
cache 2 cycle;
重新建立testseq表，插入数据，测试最终的结果，可以发现序列的内容是循环出现的，但是我们说在实践中，序列使用最多的语法是：create sequence 序列名称。其他选项使用默认值。
1.3、同义词
在前面我们使用过这样的查询语句：
select sysdate from dual;
我们知道dual是一张虚拟表，那么虽然是虚拟表，可是此表到底是在哪里定义的呢?
通过测试，我们发现在sys账户下存在dual表。现在问题出现了，既然dual表是sys账户下的，那么根据我们前面的知识，如果想要在scott用户中使用dual表时，正确的做法是：
select sysdate from sys.dual;
但是我们在scott账户下是这样写的
select sysdate from dual;
这是为什么呢?此时，实际上就是同义词的作用。什么是同义词?同义词可以让其他用户通过一个名称方便的访问”用户名.表名称“。
创建同义词的语法：
create synonym 同义词名称 for 用户名.表名称;
下面我们将scott表的emp定义emp的同义词：
create sysnonym emp for scott.emp;
如果要删除一个同义词，可以使用如下的语法格式：
drop sysnonym 同义词名称;
所以，删除上面创建的同义词：
drop sysnonym emp;
但是，此种特性只适用于Oracle数据库。
1.4、用户管理
在Oracle中可以对用户进行建立和授权的操作。
创建用户的语法是：
create user 用户名 identified by 密码;
假设现在要创建一个test用户，密码为123
create user test identified by 123;
创建用户成功过后，是否可以登录呢?显然是不行的，在登录请安必须为新创建的用户授予相关的权限才能执行相应的操作。
为用户授权的格式如下：
grant 权限1,权限2,...... to 用户名;
所以，为了新创建的用户test能够连接数据库，我们需要为它授权
grant create session to test;
之后我们就能使用test正常登陆了。
那么，我们开始常见一张表吧。
可是，我们发现，系统又提示权限不足。很明显的知道，当前用户test没有创建表的权限，既然如此，我们就为它授予创建表的权限。在此之前，我们给出一个这样的结论：对于一个新创建的用户，所有的权限均要分别赋予，该用户才能进行相应的操作。如果现在假设要想把多个权限一次性赋予一个用户，则可以讲这些权限先定义成一组角色的集合。
在Oracle中提供了两个主要角色：connect、resource，可以直接把这啷个角色赋予test用户。
grant connect,resource to test;
突然，test用户密码忘记了，那么如何修改一个用户的密码呢?当然该操作只有超级管理员才有权限
alter user 用户名 identified by 密码;
将test的用户密码修改为hello
alter user test identified by hello;
在一般的系统中存在这样的情况，在用户第一次登陆的时候可以修改密码，所以要想完成此功能，可以手工让一个密码失效，格式如下：
alter user 用户名 password expire;
现在我们可以设置test的当前密码失效
alter user test password expire;
如果系统中某个用户需要被锁住，该如何操作呢?
alter user 用户名 account lock;
现在由于某些原因，我们需要将test用户锁住
alter user test account lock;
那么，test用户就不能连接数据库了。
如果要解锁test呢?
alter user 用户名 account unlock;
好了，现在我们解锁test
alter user test account unlock;
那么，test用户就能正常连接数据库了。
现在我们需要使用test用户来访问scott用户下的emp表
select * from scott.emp;
按照前面的知识，该查询时没有问题的，但是系统提示错误说：scott.emp表不存在。怎么回事?
再想想前面的一句话，”我们需要为新创建的用户分别授予相应的权限来执行相应的操作“，很明显，test没有访问其他用户下表的权限，所以这么操作
grant select,delete on scott.emp to test;
我们将查询和删除emp表的权限授予给test用户
既然可以授予权限，那么也可以回收权限，回收权限使用revoke语法。，语法格式如下：
revoke 权限名 on 用户表名称 from 用户;
如果我们不希望test用户查询和删除scott的emp表，
revoke select,delete on scott.emp from test;
1.5、数据库的备份和恢复
数据库在运行的期间都是需要不断的进行备份的，万一假设系统崩溃了，可以从备份中恢复数据。
Oracle在安装完成之后可以使用两个命名进行数据库的备份和恢复：
|-数据库备份：exp
|-数据库恢复：imp
1.6、嵌套表
嵌套表：在一个表中还包含另外一个子表
例如：现在有一种情况，一个部门可能承接多个项目，如果此时，按照最原始的方法设计，那么定义两张表，department表，project表
create table department(
deptno number(2) primary key not null,
dname varchar2(50) not null
);
create table project(
proid number(4) primary key not null,
proname varchar2(50) not null,
deptno number(2),
constraint department_project_deptno foreign key(deptno) references department(deptno) on delete cascade
);
这是我们最常见的思路，而且本身也属于一个正确的做法，但是在Oracle中引入了嵌套表的概念，可以直接将项目表的类型作为一个department表的字段类型，达到嵌套的目的。
但是，要想完成一个嵌套表的制作，则首先要保证一点：因为数据库在创建数据表的时候都要指定字段的类型，所以嵌套表本身也需要同样指定类型，那么这种类型就需要单独的定义：
create type project_ty as object(
proid number(4),
proname varchar2(50),
prodate date
);
/
类型创建成功之后，并不意味着此类型可以直接使用，因为此类型是一个完整的类型，所以要为此类型指定一个名称
create type project_nt as table of project_ty;
/
以上的操作表示以后直接使用project_nt表示project_ty类型，就类似于varchar2表示字符串是一样的，此时可以使用此类型创建department表
create table department(
deptno number(2) primary key not null,
dname varchar2(50) not null,
projects project_nt
)nested table projects store as project_nt_tab_temp;
对于插入语句来讲，需要指定每个project_ty的类型
insert into department(deptno,dname,projects)
values(
1,'技术部',
project_nt(
project_ty(1001,'ERP',sysdate),
project_ty(1002,'CRM',sysdate),
project_ty(1003,'OA',sysdate)
)
);
此时，查询嵌套表，可以返回多个项目
select * from department;
如果这个时候，我们需要查看一个部门的全部项目的话，则需要查询嵌套表
select * from table
(select projects from department where deptno=1);
如果现在我们需要更新项目编号为1001的项目名称，将此项目名称更新为“测试项目”
update table (select projects from department where deptno=1) pro
set value(pro)=project_ty(1001,'测试项目',to_date('1998-09-21','yyyy-mm-dd')) where pro.proid=1001;
1.7、可变数组
可变数组属于嵌套表的升级版，在可变数组中，实际上就是将内部的嵌套表的内容的长度进行了限制。
例如，一个部门有多个工人，如果按照可变数组的做法，肯定首先要做出一个工人的类型。
create type worker_info as object(
id number(4),
name varchar2(50),
sex varchar2(6)
);
/
下面再定义数组类型
create type worker_info_list as varray(10) of worker_info;
/
定义部门表，一个部门中可能存在多个工人
drop table department;
create table department(
deptno number(2) primary key not null,
dname varchar2(50) not null,
workers worker_info_list
);
插入测试数据
insert into department(deptno,dname,workers)
values(20,'后勤部',
worker_info_list(
worker_info(1,'张三','男'),
worker_info(2,'李四','男'),
worker_info(3,'王五','男')
)
);
查询全部
select * from department;
除了以上的所有内容之外，对于数据库的开发中，像过程之类的基本不用了，因为现在的很多地方都使用程序完成功能。
而且，对于高级开发部分：游标、触发器、包、函数。基本上很少去直接调用。
1.8、数据库设计范式
数据库设计范式实际上非常的重要，但是从实际的开发来看，如果真的全部按照范式去做，则这个程序没法写，包括查询语句也会变得复杂。
在Oracle中的scott用户的全部表，实际上就已经很好的体现了一张设计思路，雇员-部门的关系。
1) 第一范式
例如，现在假设有如下的数据库创建脚本
create table person(
pid number(4) primary key not null,
name varchar2(50),
info varchar(200)
);
插入以下测试数据
insert into person(pid,name,info) values(1111,'张三','1983年11月23日出生，现在的住址是：北京市西城区。。。。。');
实际上对于人员来看，由以下几部分组成：
|-生日：1983年1月23日
|-省市：北京
|-地区：西城区
|-详细的信息：。。。。。
每个字段不可再分，所以，以上的数据库创建脚本修改如下：
create table person(
pid number(4) primary key not null,
name varchar2(50),
birthday date,
area varchar2(200),
subarea varchar2(200),
address varchar2(200)
);
这种设计看上去每个字段是不可再分的，但是我们应该会注意到，在一些网站的注册中，会要求用户分别输入“姓”和“名”，所以，可将上面的设计修改如下：
create table person(
pid number(4) primary key not null,
姓 varchar2(50),
名 varchar2(50),
birthday date,
area varchar2(200),
subarea varchar2(200),
address varchar2(200)
);
所以，在设计表字段的时候，最好保证每个字段均不能再分。
2) 第二范式
第一范式的要求非常简单，保证每个字段有意义。但是如果所有的操作都使用第一范式，那么会存在问题：
现在建立一张学生选课表：学号、姓名、年龄、课程名称、成绩、学分
create table selectcourse(
stuno varchar2(50),
stuname varchar2(50),
stuage number,
cname varchar2(50),
grade number,
credit number
);
以上的脚本符合第一范式的要求，但是如果按照第一范式设计的话，会存在问题：
insert into selectcourse values('s001','张三',21,'JAVA',89,0.3);
insert into selectcourse values('s001','李四',20,'JAVA',78,0.3);
insert into selectcourse values('s001','王五',23,'JAVA',80,0.3);
insert into selectcourse values('s001',赵六',22,'JAVA',90,0.3);
从以上的数据库脚本上可以发现，所有的课程信息冗余了，而且还存在以下问题：
|-如果一门课程没有一个学生选择，则此而成就从学校彻底消失了
|-课程中本身也应该包含一个课程的编号，但是如果按照以上的设计，则课程编号肯定重复
|-如果要更改课程信息，则要更改许多条记录
我们使用第二范式修改数据库脚本：
|-学生是一个实体--学生表
create table student(
stuno varchar2(50) primary key not null,
stuname varchar2(50),
stuage number
);
|-课程也应该是一个实体--课程表
create table course(
cid number(5) primary key not null,
cname varchar2(50),
credit number
);
|-学生选课信息也是一个实体--学生选课表
create table selectcourse(
stuno varchar2(50),
cid number(5),
grade number,
加入外键关联，因为学生没了，成绩就没了，因为课程没了，成绩就没了
);
以上设计解决了以下问题：
|-学生不选课的时候，课程信息不会消失
|-更新课程的时候直接更新课程表即可
|-所有的关联关系在关系表中体现。
3) 第三范式
在实际开发中，第三范式的使用是最多的。
例如，现在要求设计一张学生表，包含学号、姓名、年龄、所在院校、学院地址、学院电话，此时肯定不能使用第一范式，但是现在如果使用的是第二范式呢?
create table student(
stuno varchar2(50) primary key not null,
stuname varchar2(50),
stuage number
);
create table collage(
cid number(4) primary key not null,
cname varchar2(50) not not null,
caddress varchar2(200) not nul,
ctel varchar2(200) not null
);
create table studentcollage(
stuno varchar2(50),
cid number(4),
设置主-外键关系
);
按照上面的设计，一个学生可以同时在多个学院同时上课，多个学院会同时有同一个学生，此时，最好的做法是：一个学院包含多个学生，一个学生属于一个学院，实际上，此设计就完全类似于部门和雇员表的设计结构。
create table collage(
cid number(4) primary key not null,
cname varchar2(50) not not null,
caddress varchar2(200) not nul,
ctel varchar2(200) not null
);
create table student(
stuno varchar2(50) primary key not null,
stuname varchar2(50),
stuage number,
cid number(4),
建立主-外键关系
);
该设计是一个很明确的一对多的关系设计。
数据库的唯一原则：
|-数据库表的关联查询越少越好，SQL语句的复杂度越低越好。
1.9、数据库设计工具
在实际中数据库也有自己的设计工具，比较常用的就是Sybase的PowerDesigner开发工具，此工具可以方便的做各种设计，启动之后，可以使用此工具，进行数据库的建模设计。
启动PowerDesigner后，选择新建，Physical Data Model，选择Oracle数据库
下面使用PowerDesigner工具将Oracle中的dept和emp表进行还原
创建表--在工具中进行主-外键的操作--得到关系之后，就可以通过Powerdesigner工具进行数据库脚本的创建了。
1.10、数据库设计分析
1) 要求
设计要求，要求设计一个网上购物程序(使用Powerdesigner建立模型并编写测试数据)，有以下的需求
|-管理员可以再后台添加商品，每个商品属于一个商品组
|-可以对管理员进行分组，对每一组进行分别授权，即一个管理员组可以有多个管理员，一个管理员组有多个权限，一个管理员可以再多个组
|-用户可以自己购买商品，购买商品时要在订单表中添加信息，一个用户可以同时购买多个商品，用户可以选择自己所在的地区进行商品的派送
|-用户可以根据自己的购买积分，对商品进行折扣
2) 实现
根据第一个要求，一个商品属于一个商品组，则此时应该建立一个“一对多”的关系
根据第二个要求，可以对管理员进行分组，需要管理员表、管理员组表、权限表、管理员-管理员组表、管理员组-权限表
管理员和商品表也要存在关系
需要一个用户表，与其产生关系的有地区表、子地区表、订单表、订单详情表、积分表
正常情况下，一份订单肯定会按照以上的格式显示，那么请问，这样一来要查询多少张表?
|-用户表(用户姓名、用户电话、用户地址)
|-地区表-子地区表(用户地区)
|-订单表、订单详情表(商品总价、订单日期、邮政编码)
本查询需要同时查询6张表。本程序中的所有代码都是按照标准范式完成的，所以此时出现了以上的问题。
在开发中减少多表查询的方法可以通过冗余数据完成。
Oracle 笔记
1
韩顺平老师 oracle教程笔记
1.Oracle认证，与其它数据库比较，安装 Oracle安装会自动的生成sys用户和system用户: (1)sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限，该用户默认的密码是change_on_install (2)system用户是管理操作员，权限也很大。具有sysoper角色，没有create database的权限，默认的密码是manager (3)一般讲，对数据库维护，使用system用户登录就可以拉 也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。
2.Oracle的基本使用--基本命令
sql*plus的常用命令
连接命令 1.conn[ect] 用法：conn 用户名/密码@网络服务名[as sysdba/sysoper]当用特权用户身份连接时，必须带上as sysdba或是as sysoper 2.disc[onnect] 说明: 该命令用来断开与当前数据库的连接 3.psssw[ord] 说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。 4.show user 说明: 显示当前用户名 5.exit 说明: 该命令会断开与数据库的连接，同时会退出sql*plus 文件操作命令 1.start和@ 说明: 运行sql脚本 案例: sql>@ d:\a.sql或是sql>start d:\a.sql 2.edit 说明: 该命令可以编辑指定的sql脚本 案例: sql>edit d:\a.sql,这样会把d:\a.sql这个文件打开 3.spool 说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去。 案例: sql>spool d:\b.sql 并输入 sql>spool off 交互式命令 1.& 说明：可以替代变量，而该变量在执行时，需要用户输入。 select * from emp where job='&job'; 2.edit 说明：该命令可以编辑指定的sql脚本 案例：SQL>edit d:\a.sql 3.spool 说明：该命令可以将sql*plus屏幕上的内容输出到指定文件中去。 spool d:\b.sql 并输入 spool off 显示和设置环境变量 概述：可以用来控制输出的各种格式，set show如果希望永久的保存相关的设置，可以去修改glogin.sql脚本
Oracle 笔记
2
1.linesize 说明：设置显示行的宽度，默认是80个字符 show linesize set linesize 90 2.pagesize说明：设置每页显示的行数目，默认是14 用法和linesize一样 至于其它环境参数的使用也是大同小异
3.oracle用户管理
oracle用户的管理 创建用户 概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用。 create user 用户名 identified by 密码; (oracle有个毛病，密码必须以字母开头，如果以字母开头，它不会创建用户) 给用户修改密码 概述：如果给自己修改密码可以直接使用 password 用户名 如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限 SQL> alter user 用户名 identified by 新密码 删除用户 概述：一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有drop user的权限。 比如 drop user 用户名 【cascade】 在删除用户时，注意： 如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数cascade; 用户管理综合案例 概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令revoke。 为了给讲清楚用户的管理，这里我给大家举一个案例。 SQL> conn xiaoming/m12; ERROR: ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied 警告: 您不再连接到 ORACLE。 SQL> show user; USER 为 "" SQL> conn system/p; 已连接。 SQL> grant connect to xiaoming; 授权成功。 SQL> conn xiaoming/m12; 已连接。 SQL> 注意：grant connect to xiaoming;在这里，准确的讲，connect不是权限，而是角色。
现在说下对象权限，现在要做这么件事情： * 希望xiaoming用户可以去查询emp表 * 希望xiaoming用户可以去查询scott的emp表
Oracle 笔记
3
grant select on emp to xiaoming * 希望xiaoming用户可以去修改scott的emp表 grant update on emp to xiaoming * 希望xiaoming用户可以去修改/删除，查询，添加scott的emp表 grant all on emp to xiaoming * scott希望收回xiaoming对emp表的查询权限 revoke select on emp from xiaoming //对权限的维护。 * 希望xiaoming用户可以去查询scott的emp表/还希望xiaoming可以把这个权限继续给别人。 --如果是对象权限，就加入 with grant option grant select on emp to xiaoming with grant option 我的操作过程： SQL> conn scott/tiger; 已连接。 SQL> grant select on scott.emp to xiaoming with grant option; 授权成功。 SQL> conn system/p; 已连接。 SQL> create user xiaohong identified by m123; 用户已创建。 SQL> grant connect to xiaohong; 授权成功。 SQL> conn xiaoming/m12; 已连接。 SQL> grant select on scott.emp to xiaohong; 授权成功。 --如果是系统权限。 system给xiaoming权限时： grant connect to xiaoming with admin option 问题：如果scott把xiaoming对emp表的查询权限回收，那么xiaohong会怎样? 答案：被回收。 下面是我的操作过程： SQL> conn scott/tiger; 已连接。 SQL> revoke select on emp from xiaoming; 撤销成功。 SQL> conn xiaohong/m123; 已连接。 SQL> select * from scott.emp; select * from scott.emp 第 1 行出现错误: ORA-00942: 表或视图不存在 结果显示：小红受到诛连了。。 使用profile管理用户口令 概述：profile是口令限制，资源限制的命令集合，当建立数据库的，oracle会自动建立名称为default的profile。当建立用户没
Oracle 笔记
4
有指定profile选项，那么oracle就会将default分配给用户。 1.账户锁定 概述：指定该账户(用户)登陆时最多可以输入密码的次数，也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令。 例子：指定scott这个用户最多只能尝试3次登陆，锁定时间为2天，让我们看看怎么实现。 创建profile文件 SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2; SQL> alter user scott profile lock_account; 2.给账户(用户)解锁 SQL> alter user tea account unlock; 3.终止口令 为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba的身份来操作。 例子：给前面创建的用户tea创建一个profile文件，要求该用户每隔10天要修改自己的登陆密码，宽限期为2天。看看怎么做。 SQL> create profile myprofile limit password_life_time 10 password_grace_time 2; SQL> alter user tea profile myprofile; 口令历史 概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。 例子： 1)建立profile SQL>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10 password_reuse_time //指定口令可重用时间即10天后就可以重用 2)分配给某个用户
删除profile 概述：当不需要某个profile文件时，可以删除该文件。 SQL> drop profile password_history 【casade】 注意：文件删除后，用这个文件去约束的那些用户通通也都被释放了。。 加了casade，就会把级联的相关东西也给删除掉
4.oracle表的管理(数据类型，表创建删除，数据CRUD操作)
期望目标
? 1.掌握oracle表的管理(创建/维护)
? 2.掌握对oracle表的各种查询技巧
? 3.学会创建新的oracle数据库 oracle的表的管理 表名和列的命名规则
? 必须以字母开头
? 长度不能超过30个字符
? 不能使用oracle的保留字
? 只能使用如下字符 A-Z，a-z，0-9，$,#等
oracle支持的数据类型? 字符类 char 定长 最大2000个字符。
Oracle 笔记
5
例子：char(10) ‘小韩’前四个字符放‘小韩’，后添6个空格补全 如‘小韩 ’ varchar2(20) 变长 最大4000个字符。 例子：varchar2(10) ‘小韩’ oracle分配四个字符。这样可以节省空间。 clob(character large object) 字符型大对象 最大4G char 查询的速度极快浪费空间，查询比较多的数据用。 varchar 节省空间 数字型 number范围 -10的38次方 到 10的38次方 可以表示整数，也可以表示小数 number(5,2) 表示一位小数有5位有效数，2位小数 范围：-999.99到999.99 number(5) 表示一个5位整数 范围99999到-99999 日期类型 date 包含年月日和时分秒 oracle默认格式 1-1月-1999 timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。 ? 图片 blob 二进制数据 可以存放图片/声音 4G 一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。 怎样创建表 建表 --学生表 create table student ( ---表名 xh number(4), --学号 xm varchar2(20), --姓名 sex char(2), --性别 birthday date, --出生日期 sal number(7,2) --奖学金 ); --班级表 CREATE TABLE class( classId NUMBER(2), cName VARCHAR2(40) ); 修改表 ? 添加一个字段 SQL>ALTER TABLE student add (classId NUMBER(2)); ? 修改一个字段的长度 SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30)); ? 修改字段的类型/或是名字(不能有数据) 不建议做 SQL>ALTER TABLE student modify (xm CHAR(30)); ? 删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面) SQL>ALTER TABLE student DROP COLUMN sal;
Oracle 笔记
6
? 修改表的名字 很少有这种需求 SQL>RENAME student TO stu; ? 删除表 SQL>DROP TABLE student; 添加数据 所有字段都插入数据 INSERT INTO student VALUES ('A001', '张三', '男', '01-5月-05', 10); oracle中默认的日期格式‘dd-mon-yy’ dd日子(天) mon 月份 yy 2位的年 ‘09-6月-99’ 1999年6月9日 修改日期的默认格式(临时修改，数据库重启后仍为默认;如要修改需要修改注册表) ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd'; 修改后，可以用我们熟悉的格式添加日期类型： INSERT INTO student VALUES ('A002', 'MIKE', '男', '1905-05-06', 10); 插入部分字段 INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); 插入空值 INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null); 问题来了，如果你要查询student表里birthday为null的记录，怎么写sql呢? 错误写法：select * from student where birthday = null; 正确写法：select * from student where birthday is null; 如果要查询birthday不为null,则应该这样写： select * from student where birthday is not null; 修改数据 ? 修改一个字段 UPDATE student SET sex = '女' WHERE xh = 'A001'; ? 修改多个字段 UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001'; 修改含有null值的数据 不要用 = null 而是用 is null; SELECT * FROM student WHERE birthday IS null; ? 删除数据 DELETE FROM student; 删除所有记录，表结构还在，写日志，可以恢复的，速度慢。 Delete 的数据可以恢复。 savepoint a; --创建保存点 DELETE FROM student; rollback to a; --恢复到保存点 一个有经验的DBA，在确保完成无误的情况下要定期创建还原点。 DROP TABLE student; --删除表的结构和数据; delete from student WHERE xh = 'A001'; --删除一条记录; truncate TABLE student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
5.oracle表查询(1)
在我们讲解的过程中我们利用scott用户存在的几张表(emp，dept)为大家演示如何使用select语句，select语句在软件编程中非常有用，希望大家好好的掌握。
Oracle 笔记
7
emp 雇员表 clerk 普员工 salesman 销售 manager 经理 analyst 分析师 president 总裁 mgr 上级的编号 hiredate 入职时间 sal 月工资 comm 奖金 deptno 部门 dept部门表 deptno 部门编号 accounting 财务部 research 研发部 operations 业务部 loc 部门所在地点 salgrade 工资级别 grade 级别 losal 最低工资 hisal 最高工资 简单的查询语句 ? 查看表结构 DESC emp; ? 查询所有列 SELECT * FROM dept; 切忌动不动就用select * SET TIMING ON; 打开显示操作时间的开关，在下面显示查询时间。 CREATE TABLE users(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30)); INSERT INTO users VALUES('a0001', '啊啊啊啊', 'aaaaaaaaaaaaaaaaaaaaaaa'); --从自己复制，加大数据量 大概几万行就可以了 可以用来测试sql语句执行效率 INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users; SELECT COUNT (*) FROM users;统计行数 ? 查询指定列 SELECT ename, sal, job, deptno FROM emp; ? 如何取消重复行DISTINCT SELECT DISTINCT deptno, job FROM emp; ?查询SMITH所在部门，工作，薪水 SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH'; 注意：oracle对内容的大小写是区分的，所以ename='SMITH'和ename='smith'是不同的
Oracle 笔记
8
? 使用算术表达式 nvl null 问题：如何显示每个雇员的年工资? SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; ? 使用列的别名 SELECT ename "姓名", sal*12 AS "年收入" FROM emp; ? 如何处理null值 使用nvl函数来处理 ? 如何连接字符串(||) SELECT ename || ' is a ' || job FROM emp; ? 使用where子句 问题：如何显示工资高于3000的 员工? SELECT * FROM emp WHERE sal > 3000; 问题：如何查找1982.1.1后入职的员工? SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982'; 问题：如何显示工资在2000到3000的员工? SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000; ? 如何使用like操作符 %：表示0到多个字符 _：表示任意单个字符 问题：如何显示首字符为S的员工姓名和工资? SELECT ename,sal FROM emp WHERE ename like 'S%'; 如何显示第三个字符为大写O的所有员工的姓名和工资? SELECT ename,sal FROM emp WHERE ename like '__O%'; ? 在where条件中使用in 问题：如何显示empno为7844, 7839,123,456 的雇员情况? SELECT * FROM emp WHERE empno in (7844, 7839,123,456); ? 使用is null的操作符 问题：如何显示没有上级的雇员的情况? 错误写法：select * from emp where mgr = ''; 正确写法：SELECT * FROM emp WHERE mgr is null;
6.oracle表查询(2)
? 使用逻辑操作符号 问题：查询工资高于500或者是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J? SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%'; ? 使用order by 字句 默认asc 问题：如何按照工资的从低到高的顺序显示雇员的信息? SELECT * FROM emp ORDER by sal; 问题：按照部门号升序而雇员的工资降序排列 SELECT * FROM emp ORDER by deptno, sal DESC; ? 使用列的`别名排序 问题：按年薪排序 select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc; 别名需要使用“”号圈中,英文不需要“”号
Oracle 笔记
9
? 分页查询 等学了子查询再说吧。。。。。。。。 Clear 清屏命令 oracle表复杂查询 ? 说明 在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句 数据分组 ——max，min， avg， sum， count 问题：如何显示所有员工中最高工资和最低工资? SELECT MAX(sal),min(sal) FROM emp e; 最高工资那个人是谁? 错误写法：select ename, sal from emp where sal=max(sal); 正确写法：select ename, sal from emp where sal=(select max(sal) from emp); 注意：select ename, max(sal) from emp;这语句执行的时候会报错，说ORA-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数....... 但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数，就是说：如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。这是语法规定的 问题：如何显示所有员工的平均工资和工资总和? 问题：如何计算总共有多少员工问题：如何 扩展要求： 查询最高工资员工的名字，工作岗位 SELECT ename, job, sal FROM emp e where sal = (SELECT MAX(sal) FROM emp); 显示工资高于平均工资的员工信息 SELECT * FROM emp e where sal > (SELECT AVG(sal) FROM emp); ? group by 和 having子句 group by用于对查询的结果分组统计， having子句用于限制分组显示结果。 问题：如何显示每个部门的平均工资和最高工资? SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; (注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了) 问题：显示每个部门的每种岗位的平均工资和最低工资? SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job; 问题：显示平均工资低于2000的部门号和它的平均工资? SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; ? 对数据分组的总结 1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中) 2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by 3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。 如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 这里deptno就一定要出现在group by 中 多表查询 ? 说明 多表查询是指基于两个和两个以上的表或是视图的查询。在实际应用中，查询单个表可能不能满足你的需求，(如显示sales部门位置和其员工的姓名)，这种情况下需要使用到(dept表和emp表)
Oracle 笔记
10
问题：显示雇员名，雇员工资及所在部门的名字【笛卡尔集】? 规定：多表查询的条件是 至少不能少于 表的个数-1 才能排除笛卡尔集 (如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合) SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; 问题：显示部门号为10的部门名、员工名和工资? SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10; 问题：显示各个员工的姓名，工资及工资的级别? 先看salgrade的表结构和记录 SQL>select * from salgrade; GRADE LOSAL HISAL ------------- ------------- ------------ 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; 扩展要求： 问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序? SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno; (注意：如果用group by，一定要把e.deptno放到查询列里面) ? 自连接 自连接是指在同一张表的连接查询 问题：显示某个员工的上级领导的姓名? 比如显示员工‘FORD’的上级 SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD'; 子查询 ? 什么是子查询 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。 ? 单行子查询 单行子查询是指只返回一行数据的子查询语句 请思考：显示与SMITH同部门的所有员工? 思路： 1 查询出SMITH的部门号 select deptno from emp WHERE ename = 'SMITH'; 2 显示 SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 数据库在执行sql 是从左到右扫描的， 如果有括号的话，括号里面的先被优先执行。 ? 多行子查询 多行子查询指返回多行数据的子查询 请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号 SELECT DISTINCT job FROM emp WHERE deptno = 10; SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); (注意：不能用job=..，因为等号=是一对一的) ? 在多行子查询中使用all操作符
Oracle 笔记
11
问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号? SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30); 扩展要求： 大家想想还有没有别的查询方法。 SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 执行效率上， 函数高得多 ? 在多行子查询中使用any操作符 问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号? SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30); 扩展要求： 大家想想还有没有别的查询方法。 SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30); ? 多列子查询 单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。 请思考如何查询与SMITH的部门和岗位完全相同的所有雇员。 SELECT deptno, job FROM emp WHERE ename = 'SMITH'; SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); ? 在from子句中使用子查询 请思考：如何显示高于自己部门平均工资的员工的信息 思路： 1. 查出各个部门的平均工资和部门号 SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 2. 把上面的查询结果看做是一张子表 SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 如何衡量一个程序员的水平? 网络处理能力， 数据库， 程序代码的优化程序的效率要很高 小总结： 在这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。 注意：别名不能用as，如：SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 在ds前不能加as，否则会报错 (给表取别名的时候，不能加as;但是给列取别名，是可以加as的) ? 分页查询 按雇员的id号升序取出 oracle的分页一共有三种方式 1.根据rowid来分 select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc; 执行时间0.03秒 2.按分析函数来分 select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980; 执行时间1.01秒 3.按rownum来分
Oracle 笔记
12
select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980; 执行时间0.1秒 其中t_xiaoxi为表名称，cid为表的关键字段，取按cid降序排序后的第9981-9999条记录，t_xiaoxi表有70000多条记录。 个人感觉1的效率最好，3次之，2最差。 //测试通过的分页查询okokok select * from (select a1.*, rownum rn from(select ename,job from emp) a1 where rownum<=10)where rn>=5; 下面最主要介绍第三种：按rownum来分 1. rownum 分页 SELECT * FROM emp; 2. 显示rownum[oracle分配的] SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; rn相当于Oracle分配的行的ID号 3.挑选出6—10条记录 先查出1-10条记录 SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10; 如果后面加上rownum>=6是不行的， 4. 然后查出6-10条记录 SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 5. 几个查询变化 a. 指定查询列，只需要修改最里层的子查询 只查询雇员的编号和工资 SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; b. 排序查询，只需要修改最里层的子查询 工资排序后查询6-10条数据 SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6; ? 用查询结果创建新表 这个命令是一种快捷的建表方式 CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp; 创建好之后，desc mytable;和select * from mytable;看看结果如何? 合并查询 ? 合并查询 有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus 多用于数据量比较大的数据局库，运行速度快。 1). union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 2).union all 该操作符与union相似，但是它不会取消重复行，而且不会排序。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
Oracle 笔记
13
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。 3). intersect 使用该操作符用于取得两个结果集的交集。 SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 4). minus 使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。 SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; (MINUS就是减法的意思) 创建数据库有两种方法： 1). 通过oracle提供的向导工具。√ database Configuration Assistant 【数据库配置助手】 2).我们可以用手工步骤直接创建。
7.java操作oracle 内容介绍 1.上节回顾 2.java程序如何操作oracle √ 3.如何在oracle中操作数据 4.oracle事务处理 5.sql函数的使用 √ 期望目标 1.掌握oracle表对数据操作技巧 2.掌握在java程序中操作oracle 3.理解oracle事物概念 4.掌握oracle各种sql函数 java连接oracle ? 介绍 前面我们一直在plsql中操作oracle，那么如何在java 程序中操作数据库呢? 下面我们举例说明，写一个java，分页显示emp表的用户信息。
package com.sp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//演示 如何使用 jdbc_odbc桥连接方式
public class TestOracle {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Oracle 笔记
14
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc.odbc:testConnectOracle", "scott",
"tiger");
// 从下面开始，和SQL Server一模一样
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while (rs.next()) {
//用户名
System.out.println("用户名： "+rs.getString(2));
//默认是从1开始编号的
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
在得到连接那里，要去配置数据源，点击控制面板-->系统和安全-->管理工具-->数据源(ODBC), 打开后点添加,如图： 可以看到，有个Oracle in OraDb10g_home1的驱动，它是Oracle安装完后自动加上去的。 选中 后，点完成，再填如下信息,如图： 这样配好后基本就可以了，但为了安全起见，建议大家测试一下，点击 Test Connection按钮， 测试通过后点ok,然后数据源就生成了，如图： 然后把数据源名称写进jdbc.odbc:???里。 这里要注意：jdbcodbc能不能远程连接呢?不能远程连接，也就是你这样写的话就意味着java程 序和oracle数据库应该是在同一台机器上，因为这里没有指定IP地址，肯定默认就是本地。 如 果要远程连，就用jdbc,jdbc是可以远程连的。 运行TestOracle.java,控制台输出....................... 可惜我没运行成功，说 java.sql.SQLException: No suitable driver found for jdbc.odbc:testConnectOracle at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at com.sp.TestOracle.main(TestOracle.java:18) 不知道为什么。。。 接下来讲解用JDBC的方式连接Oracle
package com.sp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//使用 jdbc连接oracle
public class TestOracle2 {
public static void main(String[] args) {
try {
// 1.加载驱动
Oracle 笔记
15
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection
("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
// 从下面开始，和SQL Server一模一样
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while (rs.next()) {
//用户名
System.out.println("用户名： "+rs.getString(2));
//默认是从1开始编号的
}
} catch (Exception e) {
e.printStackTrace();
}
}
} 记得要把驱动包引入，classes12.jar 运行，。。。。 再次可惜，我还是没运行成功，错误是： java.sql.SQLException: Io 异常: The Network Adapter could not establish the connection at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334) at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:418) at oracle.jdbc.driver.OracleDriver.getConnectionInstance (OracleDriver.java:521) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at com.sp.TestOracle2.main(TestOracle2.java:18) 我也不知道为什么。。。 幽怨了。。 接下来建个web project，来测试oracle的分页，挺麻烦，不记录了。。 在oracle中操作数据 - 使用特定格式插入日期值 ? 使用 to_date函数 请大家思考： 如何插入列带有日期的表，并按照年-月-日的格式插入? insert into emp values (9998, 'xiaohong', 'MANAGER', 7782, to_date('1988-12- 12', 'yyyy-mm-dd'), 78.9, 55.33, 10); 注意： insert into emp values (9998, 'xiaohong', 'MANAGER', 7782, '12-12月-1988', 78.9, 55.33, 10); 这句语句是可以成功运行的 使用子查询插入数据 ? 介绍 当使用valus子句时，一次只能插入一行数据，当使用子查询插入数据时，一条inset语句可以插
Oracle 笔记
16
入大量的数据。当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据。 把emp表中10号部门的数据导入到新表中 create table kkk(myId number(4), myName varchar2(50), myDept number(5)); insert into kkk (myId, myName, myDept) select empno, ename, deptno from emp where deptno = 10; ? 介绍 使用update语句更新数据时，既可以使用表达式或者数值直接修改数据，也可以使用子查询修改 数据。 问题：希望员工SCOTT的岗位、工资、补助与SMITH员工一样。 update emp set(job, sal, comm)=(select job, sal, comm from emp where ename='SMITH') where ename='SCOTT';
8.oracle中事务处理
? 什么是事务 事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml(数据操作语言，增删改，没有查询)语句要么全部成功，要么全部失败。 如：网上转账就是典型的要用事务来处理，用于保证数据的一致性。 dml 数据操作语言 银行转账、QQ申请、车票购买 ? 事务和锁 当执行事务操作时(dml语句)，oracle会在被作用的表上加锁，防止其它用户修改表的结构。这里对我们的用户来来讲是非常重要的。 .....其它进程排序，知道1号进程完成，锁打开，2号进程进入。依次进行，如果有进程级别较高的，可以插队。 ? 提交事务 当执行用commit语句可以提交事务。当执行了commit语句之后，会确认事务的变化、结束事务。删除保存点、释放锁，当使用commit语句结束事务之后，其它会话将可以查看到事务变化后的新数据。 保存点就是为回退做的。保存点的个数没有限制 ? 回退事务 在介绍回退事务前，我们先介绍一下保存点(savepoint)的概念和作用。保存点是事务中的一点。用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。当执行rollback时，通过指定保存点可以回退到指定的点，这里我们作图说明。 ? 事务的几个重要操作 1.设置保存点 savepoint a 2.取消部分事务 rollback to a 3.取消全部事务 rollback 注意：这个回退事务，必须是没有commit前使用的;如果事务提交了，那么无论你刚才做了多少个保存点，都统统没有。 如果没有手动执行commit,而是exit了，那么会自动提交 ? java程序中如何使用事务 在java操作数据库时，为了保证数据的一致性，比如账户操作(1)从一个账户中减掉10$(2)在另一个账户上加入10$,我们看看如何使用事务?
package com.sp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
Oracle 笔记
17
import java.sql.Statement;
public class TestTrans {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
Statement sm = ct.createStatement();
// 从scott的sal中减去100
sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
int i = 7 / 0;
// 给smith的sal加上100
sm.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");
// 关闭打开的资源
sm.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
运行，会出现异常，查看数据库，SCOTT的sal减了100，但是SMITH的sal却不变，很可怕。。。 我们怎样才能保证，这两个操作要么同时成功，要么同时失败呢?
package com.sp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestTrans {
public static void main(String[] args) {
Connection ct = null;
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
// 加入事务处理
ct.setAutoCommit(false);// 设置不能默认提交
Statement sm = ct.createStatement();
// 从scott的sal中减去100
sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
int i = 7 / 0;
Oracle 笔记
18
// 给smith的sal加上100
sm.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");