搜索
您的当前位置:首页正文

oracle笔记

2022-05-28 来源:好走旅游网


1

2

3

4

5

6

图表 1

7

8

9

10

11

12

13

多表查询的基本语法

查一张以上的表,就叫做多表查询

例子:查询出雇员名称,部门名称和部门所在地的(一般多表查询要用别名)

14

统计记录数:

查询emp有多少条纪录

左右连接(重点)

select e.empno,e.ename,d.deptno,d.dname,d.loc from

emp e,dept d where e.deptno=d.deptno;

部门一共四个,这里只查询出三个,因为在雇员表中没有指定40部门的雇员,所以在消除笛卡尔乘机的时候没有条件符合40,如果喜欢40部门显示出来,就要用左右连接了。

15

select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno; (+)在左边,表示以右边的表为准,表示右链接。 40部门出来了,所以此时就用到了有连接,证明以下规律 (.+.).在左表示右连接....... (.+.)在右表示左连接........ 16

.SQL:1999对SQL的支持(了解)

范例:交叉连接(cross join)产生笛卡尔积 select * from empt CROSS JOIN dept;

查询结果 产生笛卡尔积

CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE DEPTNO=10; select * from emp NATURAL JOIN dept; 自动进行匹配 范例: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 e.deptno=30; 范例:左连接(左外连接)右连接(右外连接)LEFT JOIN RIGHT JOIN

组函数和分组统计(重点) 组函数

在SQL常用组函数有如下几个: COUNT()求全部记录数 MAX()求最大记录数 MIN()求最小记录数 AVG()平均 SUM()求和

分组统计

GROUP BY

select deptno,COUNT(empno) from emp GROUP BY deptno;

17

算出部门表的平均工资: select AVG(sal) from emp ; 算出每个部门的平均工资:

Select deptno,AVG(sal) from emp ;

之所以会出现这个错误是因为数据库不知道怎样在结果集中处理deptno列。考虑一下:这个查询既试图使用AVG聚合函数对多 行记录进行操作,却又试图从每行中获得deptno列的值;这两个操作是不可能同时完成的。此时必须提供一个GROUP BY子句告诉数据库将deptno列相同的行分组在一起,然后数据库就可以将这些组中的行传递给AVG函数。 警告:

如果查询中包含聚合函数,而所选择的列并不在聚合函数中,那么这些列就必须在GROUP BY子句中。

按部门分组,并显示部门名称,以及部门员工数 select d.dname,count(e.empno) 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;

18

之所以会出现这个错误是因为WHERE子句只能用来对单行而不是行组进行过滤。要过滤行组,可以使用HAVING子句。 范例:显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于¥5000。输出结果按月工资的合计升序排列 1.显示全部的非销售人员:job<>’SALESMAN’ select * from emp where job<>'salesman'; 2.按工作分组同时求出工资的总和 Select job,SUM(sal) from emp WHERE job<>’SALESMAN’ GROUP BY job; 3.对分组条件进行限制 Select job,SUM(sal) from emp WHERE job<>’SALESMAN’ GROUP BY job HAVING SUM(sal)>5000; 4.使用排序,按升序排列 Select job,SUM(sal) su from emp WHERE job<>’SALESMAN’ GROUP BY job HAVING SUM(sal)>5000 order by su; 分组的简单原则: 只要一列上存在重复的内容才考虑用分组 注意:分组函数可以嵌套使用,但是在组函数嵌套的时候不能再出现分组条件的查询语句 范例:求出平均工资最高的部门 错误代码: Select deptno,MAX(AVG(sal)) from emp GROUP BY deptno; Select MAX(AVG(sal)) from emp GROUP BY deptno;(正确) 19

子查询

范例:要求查询出比7654工资高的全部雇员信息 首先:要知道7654雇员的工资是多少

然后:以此查询结果为查询依据,只要其他工资大于sal,则表示符合条件

首先:查询出比7654工资高的全部雇员信息

select * from emp where sal>(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);

;

20

数据库更新操作

数据库的主要操作分为两种:

1..数据库的查询操作SELECT

2..数据库的更新操作 uUPDATE, DELETE, INSERT

此时为了保存原始的emp表的信息,在进行更新 删除 插入表前先将表复制一份 Create table myemp AS select * from emp;

此时数据已经复制出来

添加数据

Insert into emp(empno,ename,job,hiredate,sal ,deptno)

Values (7899,’张三’,’清洁工’,’20-2月-2000’,9000, 40);

使用简略写法(并不推荐),因为现在是要添加所有字段的内容,所以可以不写上任何字段名称,只要值的数量和顺序和数据库表中的顺序一致。 Insert into myemp values(7899,’张三’,’清洁工’,9000, 40);

之前插入数据的时候,日期的格式是使用了表中固定好的格式,如果现在有这样一个日期”2009-10-10”日期格式,那么现在如何把这种格式的日期插入进去呢? 使用TO_DATE()函数,将一个字符串类型的数据变为DATE类型的数据。 Insert into myemp(empno,ename,job,hiredate,sal ,deptno)

Values (7899,’张三’,’清洁工’,TO_DATE(‘2009-07-19’,’yyyy-mm-dd’),9000, 40);

修改数据

UPDATE 表名称 set 要修改的字段=新值,要修改的字段=新值….;

UPDATE 表名称 set 要修改的字段=新值,要修改的字段=新值…WHERE 修改条件.;

21

李兴华给出的标准答案是:

SELECT e.empno,e.ename,d.dname FROM emp e, emp m, dept d WHERE e.mgr=m.empno AND e.hiredate5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 左右关联问题

SELECT d.deptno,d.dname,e.ename,e.empno FROM dept d, emp e

WHERE d.deptno=e.deptno(+);

查询结构少了40部门的信息,用连接操作 SELECT d.deptno,d.dname,e.ename,e.empno FROM dept d, emp e

WHERE d.deptno=e.deptno(+);

25

6. 列出所有”CLERK”(办事员)的姓名及部门名称, 部门的人数

2.入手第一步:在emp表中查询出职位job为CLERK所在的部门名称(dept表)、

2…..SELECT e.ename ,d.dname FROM dept d,emp e WHERE e.job=’CLERK’ AND ○

e.deptno=d.deptno ;

3.部门人数肯定要用分组查询,如果是分组查询肯定要用GROUP BY,而上面的语句明显不能用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;

7. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数

SELECT job,COUNT(empno) FROM emp WHERE sal>1500 GROUP BY job;

结果出来,但是是错的。 李兴华

分析一:按工作分组,分组条件最低工资大于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;

26

8 列出在部门”SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号 1.Select deptno from dept where dname=’ SALES’; 将第一步骤作为条件写子查询 2.select ename from emp where deptno=( Select deptno from dept where dname=’SALES’); 9. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级。 1..算出平均工资: Select AVG(sal) from emp 2. select * from emp where sal>( Select AVG(sal) from emp); 答案:第一步求出公司的平均工资-1,第二步:列出薪金高于平均工资的所有雇员的信息-2 第三步:要求所在部门肯定要与部门表关联查所在部门的信息, 3. SELECT e.*,d.dname,d.loc FROM emp e,dept d WHERE sal>(SELECT AVG(sal) FROM emp ) AND e.deptno=d.deptno; 第四步要想查询上级领导肯定要与自身关联 4..SELECT e.empno,e.ename,m.ename,d.dname,d.deptno,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(+); 批注 [U3]: e.mgr=m.empno e表的领导编号等于m表的员工编号,emp表自身关联m表的员工=e表的领导,所以m.ename就是:\"领导”。 第五步:求出雇员的工资等级 SELECT e.empno,e.ename,s.grade,m.ename,d.dname,d.deptno,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; 批注 [U4]: 工资属于这个区间内表示这个等级。 27 10 列出与”SCOTT” 从事相同工作的所有员工及部门的名称。(dims) 第一步:SELECT job FROM emp e where e.ename=’SCOTT’; 第二步:SELECT e.ename, d.dname FROM emp e ,dept d where e.job=( SELECT job FROM emp e where e.ename=’SCOTT’) AND e.ename<>’SCOTT’ AND e.deptno=d.deptno; 11 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金(dims) SELECT e.ename,e.sal FROM emp e where sal IN (SELECT sal FROM emp where deptno=30) AND deptno<>30; 12 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.,部门名称 Select sal from emp where sal >(MAX(SELECT sal FROM emp where deptno=30)); 批注 [U5]: 合理的情况是没有deptno=30的 答案: 在之前的程序改,使用>ALL比最大的还要大 SELECT e.ename,e.sal,d.dname,d.loc FROM emp e ,dept d where sal >ALL (SELECT sal FROM emp where deptno=30) AND e.deptno<>30 AND e.deptno=d.deptno; 13 列出在每个部门工作的员工数量,平均工资和平均服务期限 第一步查询出每个部门的员工数量:SELECT d.dname,COUNT(empno) FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname; 答案:在第一步的基础上,第二步要读出平均工资和服务期限:尝试: SELECT d.dname,COUNT(empno),AVG(e.sal), AVG(e.hiredate) FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname; SELECT d.dname,COUNT(empno), AVG(e.sal), AVG(MONTHS_BETWEEN(sysdate,e.hiredate)/12) 年 FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname; 28 oracle中的months_between(date1,date2)函数可以实现年龄的计算,此函数的返回值为date1与date2之间的月数, 比如months_between(2008-8-8,2005-4-8)执行的结果是40(执行“select months_between('2008-8-8','2005-4-8') from dual;”不包括双引号)。date1和date2都是DATE类型的,用SYSDATE可以获得系统时间,用现在的时间减去变量中的时间再与年龄总月 数比较即可实现这一功能。 14 列出所有员工的姓名,部门名称和工资(dims) Select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno; 15 列出所有部门的详细信息和部门人数 第一步: 这种题型肯定是多表关联子查询,首先列出部门的人数 SELECT deptno dno, COUNT(empno) cou FROM emp GROUP BY deptno 第二步:把以上的结果当成一张临时表出现; SELECT d.*,ed.cou FROM dept d,(SELECT deptno dno,COUNT(empno) cou FROM emp CROUP BY deptno ) ed WHERE d.deptno=ed.dno; 16 列出各种工作的最低工资及从事此工作的雇员姓名 1.查找工资最低的工作:SELECT job,MIN(sal) FROM emp GROUP BY job; 2.SELECT e.ename,e.job,cou.minsal FROM (SELECT job,MIN(sal) minsal, empno FROM emp GROUP BY job) cou,emp e WHERE e.empno= cou.empno ; 答案: SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY job); 17 列出各个部门的MANAGER(经理)的最低薪金(dims) Select d.dname,MIN(e.sal) From emp e ,dept d Where e.deptno=d.deptno AND e.job=’MANAGER’ group by d.dname; 批注 [U6]: ORA-00979: 不是 GROUP BY 表达式.错误 18 列出所有员工的年工资,按年薪从低到高排序 在处理年薪的时候,要处理奖金,奖金要使用NVL函数处理 SELECT ename,(sal+NVL(comm,0))*12 income FROM emp ORDER BY income; 19 查出某个员工的上级主管,并要求出这些主管中的薪水超过3000 SELECT DISTINCT m.* FROM emp e,emp m WHERE e.mgr=m.empno AND m.sal>3000; 29

20 求出部门名称中,带’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;

21 给任职日期超过10年的人加薪10% UPDATE emp SET sal=sal+(sal*0.1)

WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 >10;

30

创建表和管理表(重点)

ORACLE 复制表的语法:CREATE TABLE 表名称 AS(子查询)

如果现在子查询写的是:SELECT * FROM emp 表示将表结构和内容一起复制

如果现在子查询写的是:SELECT * FROM emp WHERE 1=2; 加入一个永远不可能成立的条件,表示只是复制表结构不复制表内容。

CREATE TABLE person_f( Pid VARCHAR(18), Name VARCHAR(200), Age NUMBER(3), Birthday DATE, );

SEX VARCHAR(2) DEFAULT ‘男’

表的删除

DELETE TABLE 表名称

范例:删除person表 : DELETE TABLE person;

31

表的修改

范例:为person表增加address列

ALTER TABLE person ADD( address VARCHAR(200) DEFAULT ‘暂无地址’);

为表重命名

在Oracle中提供了RENAME命名,可以为表重新命名,但是此语法只能在Oracle中使用 语法格式: RENAME 旧的表名称 TO 新的表名称 范例:将person 重新命名为了 tperson RENAME person TO miaofang;

32

思考题

分析:这是一道面试题,只能用笛卡尔乘机:

Select e.country,d.country from country d ,country e where e.country<>d.country;

33

约束 约束的分类: 主键约束(PRIMARY KEY) 主键约束一般都在id上使用,而且本身已经默认了内容不能为空,主键约束可以在建表的时候指定: 范例:建立person表,在pid上增加主键约束 CREATE TABLE person( Pid VARCHAR(18) PRIMARY KEY, Name VARCHAR(200), Age NUMBER(3), Birthday DATE, ); SEX VARCHAR(2) DEFAULT ‘男’ insert into person values(null,'柯为权',123,TO_DATE(‘2009-07-19’,’yyyy-mm-dd’),’男'); 主键为空或者重复都会报错 34

范例:将person中的pid指定名称 CREATE TABLE person_a( Pid VARCHAR(18) , Name VARCHAR(200), Age NUMBER(3), Birthday DATE, SEX VARCHAR(2) DEFAULT ‘男’, CONSTRAINT person_pid_pk PRIMARY KEY(pid)

);

Constraint [kən'strent]:约束;限制 person_pid_pk:表示约束的名称

非空约束(NOT NULL)

CREATE TABLE person_a( Pid VARCHAR(18) NOT NULL , Name VARCHAR(200) NOT NULL, Age NUMBER(3), Birthday DATE, SEX VARCHAR(2) DEFAULT ‘男’, CONSTRAINT person_pid_pk PRIMARY KEY(pid)

);

表示插入了两个非空约束的声明,证明此时PID跟name是不允许为空的。唯一约束(UNIQUE)

表示一个字段中的内容是唯一的,其他列不允许重复 CREATE TABLE person_a( Pid VARCHAR(18) NOT NULL , Name VARCHAR(200) UNIQUE NOT NULL, Age NUMBER(3), Birthday DATE, SEX VARCHAR(2) DEFAULT ‘男’, CONSTRAINT person_pid_pk PRIMARY KEY(pid)

);

表示姓名唯一。

35

检查约束 (CHECK)

使用检查约束来判断一个列中插入的内容是否合法,例如:年龄,性别的取值范围。 CREATE TABLE person_a( Pid VARCHAR(18) NOT NULL , Name VARCHAR(200) NOT NULL, Age NUMBER(3) CHECK( age BETWEEN 0 AND 150), Birthday DATE, );

SEX VARCHAR(2) DEFAULT ‘男’ CHECK( sex I N (‘男’,’女’,’中性’)), CONSTRAINT person_pid_pk PRIMARY KEY(pid)

修改约束(了解)

一张表建立完成之后,则可以为其添加约束 DROP TABLE person CASCADE CONSTRAINT; CREATE TABLE person( Pid VARCHAR(18) PRIMARY KEY, Name VARCHAR(200), Age NUMBER(3), Birthday DATE, );

SEX VARCHAR(2) DEFAULT ‘男’

此时,需要为表中添加若干个约束,添加约束的语法如下:

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段); 关于约束类型的命名一定要统一:

1) PRIMARY KEY:主键字段_PK 2) UNIQUE:字段_UK 3) CHECK:字段_CK

4) FOREIGN KEY:父字段_子字段_FK

ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid); ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(pid);

ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);

ALTER TABLE person ADD CONSTRAINT person_sex_CK CHECK(sex IN (‘男’,’女’,’中’));

36

ROWNUM(重点)

ROWNUM:表示行号,实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。

范例:在查询雇员表上,加入ROWNUM

SELECT ROWNUM, empno,ename,job,sal,hiredate FROM emp;

从运行上看,ROWNUM本身采用自动编号的形式出现,有什么用处呢? 例如,现在假设只想显示前5条记录。条件中:ROWNUM=5

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM<=5;

能不能查中间5条呢?

37

如果现在想进行中间的截取操作,则只能采取子查询,例如现在假设每页显示5条,第2页应该显示6~10,那么对于数据库操作来讲,它在查询的时候应该首先查询1~10条,之后在查询的结果中取出后5条。

如果要取出最后5条数据

38

视图

1. 表的建立:CREATE TABLE 表名称

在ORACLE中的主要数据类型

|----VARCHAR2(长度):表示一个字符串,有长度限制 |----NUMBER():表示数字,可以表示整数或小数 |----DATE:表示日期格式,日期要按照指定的格式编写 |----CLOB:存储大文本对象,海量文字

2. 复制表:CREATE TABLE 表名称 AS 子查询

3. 一般表建立之后不要修改,如果非要修改使用ALTER TABLE指令

ALTER TABLE 有两种功能

| - 增加列:ALTER TABLE 表名称 ADD(字段名称 字段类型 DEFAULT 默认值) |- 修改列:ALTER TABLE 表名称 MODIFY(字段名称 字段类型 DEFAULT 默认值) 4. 约束:使用约束可以保证数据库表中的数据完整性 5. 在SQL中约束一共分为以下五种:

a) 但表上的约束 :

|- PRIMARY KEY :一个实体表一般都要去建立一个主键,表示唯一的编号 |- NOT NULL: 一个列的内容不予许插入空值 |- UNIQUE:表示此列的内容不允许重复

|- CHECK:表示此列的内容设置的时候要有限制 b)关联表上的约束:

| - FOREIGN KEY 表示子表的取值与父表的取值有所关联 |- 删除的时候应该先删除子表再删除父表 |- 如果现在要删除父表,可以采用ORACLE的强制手段 |-DROP TABLE 表名称 CASCADE CONSTRINT

| -级联删除:ON DELETE CASCADE,当父表中的内容被删除掉之后,子表的对应内容也被删除掉。

6. ROWNUM: 伪列:所有的记录中都存在此列,用以表示行的编号,在实际中可以通过此

列并结合子查询完成表中的部分数据显示。 7. 集合的操作:并,交,差 视图部分的知识点

1. 掌握视图的作用及定义(重) 2. 掌握序列的使用: SEQUENCE

3. 掌握PowerDesiger设计工作的使用 4. 了解同义词,了解用户管理 5. 理解数据库的设计范式

39

视图

视图的功能: 一个视图实际上就是封装了一条复杂的查询语句 创建视图的语法:

CREATE VIEW 视图名称 AS 子查询 实际上此时的子查询就表示一条非常复杂的语句,

范例:建立一个视图,包含全部的20部门的雇员信息(雇员编号,姓名,工作,雇佣日期)

CREATE VIEW empv20 AS SELECT empno,ename,job,hiredate FROM emp WHERE deptno=20;

视图创建完成之后,就可以就可以像查找表那样直接对视图进行查询的操作 范例:查询视图

SELECT * FROM empv20;

此时,是通过视图找到的20部门的所有数据,也就是发现,可以使用视图包装需要的查询语句。 此时,此视图只包含了四个字段的信息,如果现在希望多包含一个 “工资”sal , 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 ename,(sal+NVL(comm,0))*12 income FROM emp ORDER BY income;

如果在开发中每次都写如此之长的sql代码肯定不方便,所以就应该把它建立成视图以方便用户查询操作;

CREATE OR REPLACE VIEW empv21 AS

SELECT ename,(sal+NVL(comm,0))*12 income FROM emp ORDER BY income; 创建完视图之后,以后想得到之前的查询结果.直接写: SELECT * FROM empv21 ; 即可查询出结果

范例:创建一个只包含20部门的雇员的视图 CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20;

更新视图:在视图中是不应该包含真实数据的,而且在此程序中,创建的视图实际上是存在创建条件的,此条件是deptno=20,如果将视图中的7369的部门编号改为30;

40

范例:修改视图中的7369的部门编号 UPDATE empv20 SET deptno=30 WHERE empno=7369; SELECT * FROM empv20; 此时,已经提示视图正常的进行了更新,重新查询视图之后,会发现在视图中已经没有7369这个雇员了,那么emp表中呢? ---- 发现在emp表中的7369雇员的部门编号已经修改为30了,这样做明显不合适,因为创建视图的时候是有条件的,你一旦修改之后,则此条件就被破坏了,所以在创建视图的过程的SQL中提供了两个重要的参数: ---WITH CHECK OPTION:不能更新视图的创建条件 范例:在创建视图中使用此参数 CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20 WITH CHECK OPTION; 这时候再执行更新条件语句: UPDATE empv20 SET deptno=30 WHERE empno=7369; 批注 [U7]: 完整的创建视图语法 创建条件不能更新,那么其他字段呢?例如:现在将7369的雇员姓名修改Wie“史密斯” 范例:修改视图中7369的雇员编号; UPDATE empv20 SET ename=’ 史密斯’ WHERE empno=7369; 已更新一行; 但是,视图的本身作用还是用来查询的,所以不应该允许更改,所以此时可以使用第二个参数: WITH READ ONLY: 创建的视图只读 范例:创建只能读的视图 CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20 WITH READ ONLY; 此时提示视图无法更改,是只读的。 41 序列(重点) 李兴华:在oracle中序列是最重要的。 在很多数据库中都存在一个自动增长的列,如果现在要想在oracle中完成自动增长的功能,则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。 创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下: CREATE SEQUENCE 序列名 [INCREMENT BY n] [START WITH n] [{MAXVALUE/ MINVALUE n|NOMAXVALUE}] [{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表,观察序列的变化 批注 [U8]: 每次增长幅度 批注 [U9]: 从哪开始; 批注 [U10]: 最大值,最小值,没有最大值 批注 [U11]: 循环 批注 [U12]: 表示下一个值 批注 [U13]: 表示当前值 从结果中发现,nextval始终在进行自动增长的操作,而curr始终取出当前操作的序列的结果; 也就是说,现在的这种序列,每次增长的幅度是1,那么也可以修改序列的增长幅度: 可以使用以下的两个参数: 每次的增长幅度:INCREATE BY 从哪里开始:START WITH 42 范例:重新创建序列 删除序列:DROP SEQUENCE myseq; CREATE SEQUENCE myseq INCREMENT BY 2; 重建testseq表重新建立: DROP TABLE test2seq; CREATE TABLE test2seq( next NUMBER, curr NUMBER ); INSERT INTO test2seq(next,curr) VALUES( myseq.nextval, myseq.currval); ORACLE查看所有用户建的表: select table_name from user_tables;查看表结构: desc dept; 默认情况下序列是从1开始的,可以使用START WITH 指定其开始的位置。 CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10; 可以用工具建立序列, 但是序列用得最多也只有: CREATE SEQUENCE 序列名称; 批注 [U14]: 注意拼写 43

同义词(了解即可)

之前总是存在这样的一种查询语句: SELECT SYSDATE FROM dual;

之前一直强调,dual是一张虚拟表,那么虽然是虚拟表,可是此表到底是在哪里定义的呢? 如果现在使用system连接数据库,查询一张此张表是否属于system用户: 使用system用户连接: conn system/manager; 查找所有的表:select* from tab;

查找dual表: select * from tab where TNAME=’DUAL’; //找不到 Conn sys/change_on_install AS SYSDBA; select * from tab where TNAME=’DUAL’;

在sys用户下存在此表,此表在sys下,但是在scott用户下却可以直接通过表名称访问,那么正常情况下如果要访问不同用户的表需要使用”用户名.表名称”。

这就是同义词的作用,同义词,可以让其他用户通过一个名称方便访问 “用户名.表名称” 创建同义词的语法:

CREATE SYNONYM 同义词名称 FOR 用户名.表名称; 范例:将scott.emp定义emp 的同义词 CREATE SYNONYM emp FOR SCOTT.emp; 范例:删除同义词

DROP SYNONYM emp; 但是此种特性只适用于oracle

44

索引 索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种: 为什么添加了索引之后,会加快查询速度呢? 图书馆:如果杂乱地放书的话检索起来就非常困难,所以将书分类,然后再建一个箱子,箱子里面放卡片,卡片里面可以按类查询,按姓名查或者类别查,这样的话速度会快很多很多,这个就有点像索引。索引的好处就是提高你找到书的速度,但是正是因为你建了索引,就应该有人专门来维护索引,维护索引是要有时间精力的开销的,也就是说索引是不能乱建的,所以建索引有个原则:如果有一个字段如果不经常查询,就不要去建索引。现在把书变成我们的表,把卡片变成我们的索引,就知道为什么索引会快,为什么会有开销。 创建索引: 1. 单例索引 单例索引是基于单个列所建立的索引,比如: CREATE index 索引名 on 表名(列名) 2. 复合索引 复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引,但是要求列的组合必须不同,比如: Create index emp_idx1 on emp(ename,job); Create index emp_idx1 on emp(job,ename); 批注 [U15]: 这两个是不同的索引, 45 范例:CREATE table customer( customerId char(8) primary key, Name varchar2(50) not null, Address varchar2(50), Email varchar2(50) unique, Sex char(2) default ‘男’ check (sex in(‘男’,’女’)), Cardid char(10) ); SELECT * FROM customer where name=’sp’; 批注 [U16]: 如果经常要根据name去查询客户,如果这里数据非常海量,不加索引,这里将会非常浪费时间; 批注 [U17]: 如果数据非常海量这里建索引跟不建索引差别非常大。 建索引名为nameIdx表名为customer列名为name的索引: CREATE index nameIdx on customer(name); # 索引缺点分析 索引有一些先天不足: 1:建立索引,系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。 2:更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性。 实践表明,不恰当的索引不但于事无补,反而会降低系统性能。因为大量的索引在进行插入,修改和删除操作时比没有索引花费更多的系统时间。 比如在如下字段建立索引应该是不恰当的: 1..很少或从不引用的字段 2...逻辑型的字段,如男或女(是或否)等。综上所述,提高查询效率是以消耗一定的系统资源为代价的,索引不能盲目的建立,这是考验一个DBA是否优秀的很重要的指标。 批注 [U18]: 逻辑型的,比如说在sex字段上没必要建索引,因为它本身就很好找,”男”或”女”,很好区分,没必要建。 批注 [U19]: 除了我们加的name字段有索引,还有些默认的字段,Oracle看看只要类型是unique的,都会自动加索引 46

用户管理(了解)

在oracle中可以对用户进行建立及授权的操作。 创建用户的语法:

CREATE USER 用户名 IDENTIFIED BY 密码; 范例:创建一个test 用户,密码是test123; CREATE USER test IDENTIFIED BY test123; CREATE USER ke IDENTIFIED BY ke123;

如果想要创建用户,则首先应该使用管理员进行登陆; 创建用户之后,开启一个新的sqlplusw,并用此用户登陆;

提示test用户没有创建session的权限,没有session权限意味着无法登陆,如果要为session建立权限可以参照:(本文档oracle常见问题第6)

---------------------------------略------------------------------------

实际上一个新的用户的所有权限都需要分别赋予,假设现在需要把所有权限一次性赋予一个用户,则可以将这些权限定义成一个角色. 在oracle中提供了两个主要的角色:CONNECT,RESOURCE表示连接和资源操作,可以直接把这两个角色赋予test用户;

GRANT CONNECT, RESOURCE TO test; //这就把权限都给了test 修改用户的密码:

ALTER USER 用户名 IDENTIFIYED BY 密码; 范例:将test 的用户密码修改为hello

ALTER USER test IDENTIFIED BY hello;

在一般的系统中存在,在用户第一次登陆的时候可以修改密码。所以想要完成此功能,可以手工让一个密码失效,格式如下:

ALTER USER 用户名 PASSWORD EXPIRE; 范例:让test用户的密码失效

ALTER USER test PASSWORD EXPIRE;

这时候再用 conn test/hello 登陆就会提示你重新输入修改密码;

47

数据库的备份与恢复(了解)

把所有数据都备份到d://data之中, exp命令之后会提示你要备份的是哪个用户名下的数据

EXPDAT.DMP是导出文件的默认名称 不建议修改,然后就执行一系列的默认操作

备份完成,为了检验备份的效果,把数据库中的表全部删除, 之后使用imp命令将备份的文件恢复

48

数据库设计范式(了解)

嵌套表、可变数组,对于数据库的开发过程中像过程之类的基本上也都不使了,因为很多都用程序完成,而且,对于高级开发部分,游标,触发器,包,函数。基本上很少去直接调用。

数据库设计范式实际上非常重要,但是从实际的开发来看,如果真的全部按照范式去做,则这个程序没法写,包括查询语句也会变得复杂,(因为表关联过多,会产生大量的笛卡尔乘机

在Oracle中的scott用户的全部表,实际上就已经很好的体现一种设计思路;

第一范式:

1NF:目标就是表中每列都不可分割;

49

数据库设计分析 ( 重点 )

设计要求,要求设计一个网上购物程序(使用powerdesiger建立模型并编写测试数据),有如下的需求

1. 管理员可以在后台添加商品,每个商品属于一个商品组

2. 可以对管理员进行分组,对每一组进行分别授权,即一个管理员组可以有多个管理员,

一个管理员组有多个权限,一个管理员可以在多个组。

3. 用户可以自己购买商品,购买商品时要在订单表中添加信息,一个用户可以同时购买

多个商品,用户可以选择自己所在的地区进行商品的派送 4. 用户可以根据自己的购买积分,对商品进行折扣

50

GROUP BY 和 HAVING

在介绍GROUP BY 和 HAVING 子句前,我们必需先讲讲sql语言中一种特殊的函数:聚合函数,例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

SELECT SUM(population) FROM bbc

这里的SUM作用在所有返回记录的population字段上,结果就是该查询只返回一个结果,即所有国家的总人口数。

通过使用GROUP BY 子句,可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。当你指定 GROUP BY region 时, 属于同一个region(地区)的一组数据将只能返回一行值.也就是说,表中所有除region(地区)外的字段,只能通过 SUM, COUNT等聚合函数运算后返回一个值.

HAVING子句可以让我们筛选成组后的各组数据. WHERE子句在聚合前先筛选记录.也就是说作用在GROUP BY 子句和HAVING子句前.而 HAVING子句在聚合后对组记录进行筛选。

让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句,还采用第三节介绍的bbc表。

SQL实例:

一、显示每个地区的总人口数和总面积.

SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region

先以region把返回记录分成多个组,这就是GROUP BY的字面含义。分完组后,然后用聚合函数对每组中的不同字段(一或多条记录)作运算。

二、 显示每个地区的总人口数和总面积.仅显示那些面积超过1000000的地区。 SELECT region, SUM(population), SUM(area) FROM bbc

GROUP BY region

HAVING SUM(area) > 1000000

在这里,我们不能用where来筛选超过1000000的地区,因为表中不存在这样一条记录。相反,HAVING子句可以让我们筛选成组后的各组数据.

51

oracle常见问题

1..在打开PL/SQL DEVELOP的时候 回弹出: ORA-12170: TNS: 连接超时 关闭掉杀毒跟防火墙都无法解决:

点Net Manager --

设置好主机名,也可以设置IP 地址,但是每次都要重新设置。 Ok---成功解决!

52

2.查找SQL/PL生成 下一个序列号

在Oracle库中查询出下个自动生成的id号

3. ORA-00904 INVALID IDENTIFIER解决办法,字段名错误 ORA-00904 invalid identifier 这个错误是因为 字段名写错了 检查下字段名

3. oracle 10g 安装成功之后。scott用户被锁定问题

原因:默认Oracle10g的scott不能登陆。 解决:

(1)conn sys/sys as sysdba;//以DBA的身份登录 (2)alter user scott account unlock;// 然后解锁

(3)conn scott/tiger //弹出一个修改密码的对话框,修改一下密码就可以了 具体操作步骤如下: C:> sqlplus

请输入用户名:sys

输入口令:sys as sysdba //注意:在口令这里输入的密码后面必须要跟上 as sysdba 才可以。

SQL> alter user scott account unlock; 用户已更改. SQL> commit;

提交完成. SQL> conn scott/tiger 更改scott口令 新口令:tiger

重新键入新口令:tiger 口令已更改 已连接。 //完成。

4. ORACLE连接其他地址的库 在ORACLE安装目录下的:

C:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN的tnsnames.ora 文件

# tnsnames.ora Network Configuration File: C:\\oracle\\product\\10.2.0\\db_1\\network\\admin\nsnames.ora

53

tnsnames.ora # Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) orcl74= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.249.254.74)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) 如果要连接 jdbc.url =jdbc:oracle:thin:@10.249.254.74:1521:orcl pool.url=jdbc:oracle:thin:zhmwc/zhgmcc&$@10.249.254.74:1521:orcl jdbc.username =zhmwc jdbc.password =zhgmcc&$ 6. ORACLE权限问题 在用SCOTT用户创建视图的时候出现 批注 [U20]: 这就是对应地址 10.249.254.74的配置 54

55

解决办法是:

首先在开始--》运行——》SQLPLUS,然后输入 SYS/CHANGE_ON_INSTALL AS SYSDBA 以SYS权限登陆进去 然后可以进行操作:

grant create any view to SCOTT;//把创建视图的权限赋给scott;

在用scott登入:

SELECT * FROM USER_SYS_PRIVS; 这样就可以知道当前用户的权限

常用的赋权命令:

1.创建用户 CREATE USER TEST INDENTIFIED BY TEST; 这样就创建了一个用户名密码都为TEST的用户 2.登录权限GRANT CREATE SESSION TO TEST; 这样TEST用户就能成功登陆进去

3. 建表权限GRANT CREATE TABLE TO TEST;

4 .使用表空间权限GRANT UNLIMITED TABLESPACE TO TEST; 5. 查询当前用户的系统权限:SELECT * FROM USER_SYS_PRIVS; 这样就可以知道当前用户的权限

7. 撤销权限 REVOKE CREATE TABLE FROM TEST; 8. --创建用户

9. CREATE USER KEELTEST IDENTIFIED BY KEELTEST;

56

10. 分配所有权限GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW ,CREATE

ANY INDEX TO SCOTT;

11. GRANT UNLIMITED TABLESPACE TO SCOTT;

12.

Caused by: java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

这个错误折腾了我好长一段时间:后来发现是驱动器的问题 Oracle有两个jdbc的驱动包

把classes12.jar 换成ojdbc14.jar即可!! 13 .如何取得表中第6到第10条记录的值

如何取得表中第6到第10条记录的值 第一种方法,使用minus语句:

那么第一种方法就是取出前5条,再取出前10条,然后采用集合运算的方法把前10条减去前5条就OK了,SQL语句如下:

select * from t_report where rownum <= 10 minus

select * from t_report where rownum <= 5; 另外一种方法,采用子查询:

子查询的这种方法相对比较复杂一点,不过性能要比刚才的集合相减要好一些。这种方法首先在子查询中得到前10条数据,顺路也取得前10条数据的rownum做为表中一个新的列,然后再一次查询的时候取得刚才查询的rownum大于5的那些数据。SQL语句如下:

select * from (select t.*,rownum R from subscriber_attr t where rownum <= 10) where R > 5

57

通过上面的语句,就得到了6到第10条数据了。

注意:子查询中的rownum必须要有别名(上边语句中的R),否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。 PS:关于rownum

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。

如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立,所以查不到记录。

java.sql.SQLException: Io 异常: The Network Adapter could not establish the connection

select * from user ;查询出来的结果不可以更新

select * from jforum_users for update; 查询出来的结果可以更新; user表的userId跟jforum_user_groups表的userID关联

设user表的userID=403 在jforum_user_groups表设userID=403的groudID=2;管理员

select * from jforum_user_groups for update

58

因篇幅问题不能全部显示,请点此查看更多更全内容

热门图文

Top