Oracle的sql语句百例训练.doc

Oracle系列一：简单SQL与单行函数使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下 emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号) dept部门表(deptno部门编号/dname部门名称/loc地点) 工资 薪金 佣金登录Oracle数据库1、sqlplus scott/tiger2、sqlplus /nologSQLconn scott/tiger若是使用SYS的账号进行登录的话，则使用以下语句SQLconn / as sysdba【1】EMP表内容查询SQL SELECT * FROM emp;出错，原因是没有找到该表，因为该表时SCOTT用户的表，所以查询时应该加上scott.emp就可以了【2】显示当前用户SQL show user【3】查看当前用户的所有表SQL SELECT * FROM tab;【4】若想重复执行上一条SQL语句，则在sqlplus命令行下输入/即可【5】查询一张表的结构，例如dept表SQL desc dept【6】在雇员表中查询雇员的编号、姓名、工作SQL SELECT empno,ename,job FROM emp;【7】可以为列名取别名，在Linux下Oracle如果英文别名不加上双引号则会变成大写SQL SELECT empno 编号,ename 姓名,job 工作 FROM emp;【8】查询所有的工作SQL SELECT DISTINCT job FROM emp;工作可能会重复，加上DISTINCT关键字【9】若要求按照以下的格式进行结果输出，如 NO:7469,Name:SMITH,Job:CLERKSQL SELECT NO:|empno|,Name:|ename|,Job:|job FROM emp;【10】要求列出每个雇员的姓名及年薪SQL SELECT ename,sal*12 income FROM emp;这里年薪最好用别名进行标识，可以一眼就能明白【11】查看每月可以得到奖金的雇员信息SQL SELECT * FROM emp WHERE comm is NOT NULL;【12】要求基本工资大于1500，同时可以领取奖金的雇员信息SQL SELECT * FROM emp WHERE sal1500 AND comm is NOT NULL;如果是或的是关系，则使用 OR【13】查询基本工资不大于1500，同时不可以领取奖金的雇员信息SQL SELECT * FROM emp WHERE NOT(sal1500 AND comm is NOT NULL);【14】查询在1981年雇佣的全部雇员信息，BETWEEN . AND 包含等于的情况SQL SELECT * FROM empWHERE hiredate BETWEEN 01-JAN-81 AND 31-DEC-81;【15】Oracle对大小敏感，所以查询时名字要区分大小写【16】要求查询出雇员编号不是 7369、7499的雇员信息SQL SELECT * FROM empWHERE empno NOT IN(7369,7499);【17】SQL中LIKE语句要注意通配符 % 和 _SQL SELECT * FROM empWHERE hiredate LIKE %81%;【18】查看雇员编号不是7369的雇员信息，使用或!=SQL SELECT * FROM empWHERE empno7369;【19】要求对雇员的工资由低到高进行排序，升序为默认(ASC)，降序(DESC)SQL SELECT * FROM empGROUP BY sal;【20】查看出部门号为10的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列SQL SELECT * FROM empWHERE deptno=10GROUP BY sal DESC,hiredate ASC;数据库系统中，每个数据库之间区别最大的就是在函数的支持上，单行函数是最简单的函数，单行函数分为1、字符函数：接受字符输入并且返回字符或数值2、数值函数：接受数值输入并返回数值3、日期函数：对日期型数据进行操作4、转换函数：将一种数据类型转换为另一种数据类型5、通用函数：NVL、DECODE 函数字符函数：【1】大小写转换 UPPER 和 LOWERSQL SELECT UPPER(smith) FROM dual;【2】将雇员姓名变为开头字母大写，INITCAPSQL SELECT INITCAP(ename) FROM emp;字符函数中有连接函数CONCAT，但不如 | 好用，还有字符串处理的一些函数字符串截取：substr()字符串长度：length()内容替换：replace()SQL SELECT substr(hello,1,3),length(hello),replace(hello,l,x) FROM dual;这里注意的是Oracle中字符串截取从0和从1开始都是一样的，谨防面试提问【3】要求显示所有雇员的姓名及姓名的后3个字符SQL SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;以上操作显得较为麻烦，substr()函数是可以倒着截取SQL SELECT ename,SUBSTR(ename,-3,3) FROM emp;数值函数：1、四舍五入：ROUND()2、截断小数位：TRUNC()3、取余(取模)：MODSQL SELECT ROUND(789.536) FROM dual;【1】保留2位小数,(如果是-2则对整数进行四舍五入，变为800了)SQL SELECT ROUND(783.56,2) FROM dual;【2】使用MOD()函数进行取余操作SQL SELECT MOD(10,3) FROM dual;日期函数：1、日期 - 数字 = 日期2、日期 + 数字 = 日期3、日期 - 日期 = 数字(天数)【1】求出当前日期SQL SELECT SYSDATE FROM dual;Oracle提供了以下的日期函数支持：MONTHS_BETWEEN():求出给定日期范围的月数ADD_MONTHS():在指定日期上加上指定的月数，求出之后的日期NEXT_DAY():下一个的今天的日期LAST_DAY():求出给定日期的最后一天日期【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数SQL SELECT empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;【3】验证 ADD_MONTHS()、NEXT_DAY()、LAST_DAY()SQL SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL;SQL SELECT NEXT_DAY(SYSDATE,MON) FROM DUAL;SQL SELECT LAST_DAY(SYSDATE) FROM DUAL;转换函数：1、TO_CHAR()： 将日期或数值转换成字符串2、TO_NUMBER()：将字符串转换成数字3、TO_DATE()： 将字符串转换成日期【1】将年月日进行分开，要指定拆分的通配符,yyyy-mm-ddSQL SELECT empno,ename,TO_CHAR(hiredate,yyyy) datetime FROM emp;【2】将薪水的数字进行格式化，$99,999表示美元，L99,999表示当地货币SQL SELECT empno,ename,TO_CHAR(sal,99,999) salary FROM emp;【3】TO_NUMBER()验证SQL SELECT TO_NUMBER(123)+TO_NUMBER(123) FROM DUAL;【4】TO_DATE()验证，如下例子执行后显示为 11-JUL-11SQL SELECT TO_DATE(2011-7-11,yyyy-mm-dd) FROM DUAL;通用函数：【1】求出每个雇员的年薪(应算上奖金)SQL SELECT empno,ename,(sal+comm)*12 FROM emp;由于comm中有NULL,NULL值计算后还是NULL,正确如下：SQL SELECT empno,ename,NVL(comm,0),(sal+NVL(comm,0)*12 income FROM emp;NVL可以理解为将NULL值转换为具体的内容，这里是0【2】DECODE()函数，该函数类似于 IF . ELSEIF.ELSE语法如下：DECODE(col/expression,选择1,结果1，选择2，结果2，.,默认)验证DECODE()函数SQL SELECT empno,ename,hiredate,DECODE(job,CLERK,业务员,SALESMAN,销售人员,MANAGER,经理,ANALYST,分析员,PRESIDENT,总裁) 职位FROM emp;SQL简单语句练习：【1】找出佣金高于薪金的60%的员工SQL SELECT * FROM emp WHERE commsal*0.6【2】找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料SQL SELECT * FROM empWHERE (deptno=20 AND job=MANAGER)OR (deptno=10 AND job=CLERK);【3】找出既不是经理又不是办事员但其薪金大于或等于2000的所有员工的资料SQL SELECT * FROM empWHERE job NOT IN(MANAGER,CLERK) AND sal = 2000;【4】找出有奖金的员工的不同国祚SQL SELECT DISTINCT job FROM empWHERE comm IS NOT NULL;【5】找出各月倒数第3天受雇的所有员工SQL SELECT * FROM empWHERE LAST_DAY(hiredate)-2=hiredate;【6】找出早于12年前受雇的员工SQL SELECT * FROM empWHERE MONTHS_BETWEEN(sysdate,hiredate)/12 12;【7】显示刚好为5个字符的员工的姓名SQL SELECT ename FROM empWHERE length(ename)=5;【8】显示不带有R的员工的姓名SQL SELECT ename FROM empWHERE ename NOT LIKE %R%;【9】显示员工的姓名和受雇日期，将最老的员工排在最前SQL SELECT * FROM empGROUP BY hiredate;【10】显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则按年份排序SQL SELECT ename,TO_CHAR(hiredate,yyyy) year,TO_CHAR(hiredate,mm) month FROM empORDER BY month,year;【11】找出在2月受聘的员工SQL SELECT * FROM empWHERE TO_CHAR(hiredate,mm)=2;【12】以年月日方式显示所有员工服务年限SQL SELECT ename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) year,TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12) month,TRUNC(MOD(sysdate-hiredate,30) dayFROM emp;Oracle系列二：多表复杂查询和事务处理多表查询应该注意去除笛卡尔积，一般多个表时会为表起个别名【1】要求查询雇员的编号、姓名、部门编号、部门名称及部门位置SQL SELECT e.empno,e.ename,d.deptno,d.dname,d.locFROM emp e,dept dWHERE e.deptno = d.deptno;【2】要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名(表自关联)SQL SELECT e.ename,e.job,m.enameFROM emp e,emp mWHERE e.mgr = m.empno;【3】对【2】进行扩充，将雇员所在部门名称同时列出SQL SELECT e.ename,e.job,m.ename,d.dnameFROM emp e,emp m,dept dWHERE e.mgr = m.empno AND e.deptno=d.deptno;【4】查询每个雇员的姓名、工资、部门名称，工资在公司的等级(salgrade)，及其领导的姓名所在公司的等级先确定工资等级表的内容SQL SELECT * FROM salgrade;查询每个雇员的姓名、工资、部门名称和工资在公司的等级SQL SELECT e.ename,e.sal,d.dname,s.gradeFROM emp e,dept d,salgrade sWHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;查询其领导姓名及工资所在公司的等级SQL SELECT e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.gradeFROM emp e,dept d,salgrade s,emp m,salgrade msWHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisalAND e.mgr = m.empno AND m.sal BETWEEN ms.losal AND ms.hisal;【5】左连接与右连接的概念，+在等号左边表示右连接，反之，左连接查询雇员的编号、姓名及其领导的编号、姓名SQL SELECT e.empno,e.ename,m.empno,m.enameFROM emp e,emp mWHERE e.mgr = m.empno(+);就发现将KING的那条记录也连过来了SQL1999语法中有如下几种连接(了解)1、交叉连接CROSS JOIN，产生笛卡尔积SQL SELECT * FROM emp CROSS JOIN dept;2、自然连接NATURAL JOIN,自动进行关联字段的匹配SQL SELECT * FROM emp NATURAL JOIN dept;3、使用USING子句，直接关联操作列SQL SELECT * FROM emp JOIN dept USING(deptno)WHERE deptno=30;4、使用ON子句，用户自己编写的条件SQL SELECT * FORM emp JOIN dept ON(emp.deptno = dept.deptno)WHERE deptno=30;5、左连接(左外连接、LEFT (OUTER) JOIN)、右连接(右外连接、RIGHT (OUTER) JOIN)组函数及分组统计1、COUNT():求出全部记录数2、MAX()：求出一组中最大值3、MIN():求出最小值4、AVG()：求出平均值5、SUM()：求和【1】求出每个部门的雇员数量SQL SELECT deptno,count(empno)FROM empGROUP BY deptno;【2】按部门分组，并显示部门的名称，及每个部门的员工数SQL SELECT d.dname,COUNT(e.empno)FROM emp e,dept dWHERE e.deptno=d.deptnoGROUP BY d.dname;【3】要求显示平均工资大于2000的部门编号和平均工资SQL SELECT deptno,AVG(sal)FROM empWHERE AVG(sal)2000GROUP BY deptno;出错，WHERE子句中不能出现分组函数的条件，要使用HAVING子句上述语句应该改为如下SQL SELECT deptno,AVG(sal)FROM empGROUP BY deptnoHAVING AVG(sal)2000【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要求从事同一工作的雇员月工资合计大于$5000,输出结果按月工资的合计升序排序按工作分组，求出非销售人员的月工资总和SQL SELECT job,SUM(sal)FROM empWHERE jobSALESMANGROUP BY job;对分组条件进行限制，然后进行排序,HAVING子句不能使用别名SQL SELECT job,SUM(sal) totalSalFROM empWHERE jobSALESMANGROUP BY jobHAVING SUM(sal) 5000ORDER BY totalSal;【3】分组函数可以嵌套使用，但是在SELECT列中就不能再出现该分组条件的列名了SQL SELECT deptno,MAX(AVG(sal)FROM empGROUP BY deptno;出错！修改如下SQL SELECT MAX(AVG(sal)FROM empGROUP BY deptno;【4】查询出比7654工资要高的全部雇员的信息首先要查询雇员编号7654的工资SQL SELECT sal FROM emp WHERE empno=7654;以上述条件的结果最后后续查询的依据SQL SELECT * FROM empWHERE sal(SELECT sal FROM emp WHERE empno=7654);子查询在操作中分为以下三类：1、单列子查询：返回的结果是一列的内容2、单行子查询：返回多个列，也可能是一条记录3、多行子查询：返回多个记录【1】要求查询工资比7654高，同时与7788从事相同工作的全部雇员SQL SELECT * FROM empWHERE sal(SELECT sal FROM emp WHERE empno=7654)AND job=(SELECT job FROM emp WHERE empno=7788);【2】要求查询 部门名称、部门员工数、部门平均工资，部门的最低收入雇员的姓名查询部门员工数、部门平均工资SQL SELECT deptno,COUNT(empno),AVG(sal)FROM empGROUP BY deptno;查询部门的名称，及最低收入雇员姓名，要进行表关联(子查询)SQL SELECT d.dname,ed.c,ed.a,e.enameFROM dept d,( SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min FROM emp GROUP BY deptno) ed, emp eWHERE d.deptno=ed.deptno AND e.sal = ed.min;若上述存在两个最低工资的情况，则会出错，在子查询中存在以下3种查询的操作符号IN：指定一个查询范围，例如查询每个部门的最低工资(返回值有多个)SQL SELECT * FROM empWHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);ANY：=ANY(与IN操作一样)、ANY(比最小大)、 SELECT * FROM empWHERE sal ALL(比最大要大)、 SELECT * FROM empWHERE (sal,NVL(comm,-1) IN (SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=20);数据库更新操作INSERT、UPDATE、DELETE【1】复制一张表，例如复制EMP表为MYEMPSQL CREATE TABLE MYTEMP AS SELECT * FROM emp;【2】将编号为7899的雇员的领导取消SQL UPDATE myemp SET mgr=null WHERE empno=7899;【3】更新时，一定要注意不能批量更新(加上WHERE子句)，多列更新例子如下SQL UPDATE myemp SET mgr=null,comm=null WHERE empno IN(7369,8899);【4】删除掉全部领取奖金的雇员SQL DELECT FROM emp WHERE comm is NOT NULL;事务处理 ACIDA：Atomicity 原子性：事务中的操作或者都完成，或者都取消C：Consistency 一致性：事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况I：Isolation 隔离性：当前的事务与其他未完成的事务是隔离的D：Durability 持久性：在COMMIT之后，数据永久保存在数据库中，在此之前，事务的操作都可以回滚验证事务过程：创建一张临时表，只包含部门10SQL CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno=10;删除emp10中的7782雇员SQL DELETE FROM emp10 WHERE empno=7782;再打开另一个窗口，发现数据还存在，此时如果可以使用以下的两种命令进行事务处理COMMIT 和 ROLLBACK 提交事务和回滚事务SQL查询练习【1】列出至少一个员工的所有部门SQL SELECT d.*,ed.cou FROM dept d,( SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno HAVING COUNT(empno) 1) edWHERE d.deptno=ed.deptno;【2】列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门SQL SELECT d.deptno,d.dname,e.empno,e.enameFROM dept d,emp eWHERE d.deptno = e.deptno(+);【3】列出所有CLERK(办事员)的姓名及其部门名称，部门的人数关联dept表SQL SELECT e.ename,d.dnameFROM emp e,dept dWHERE e.deptno=d.deptno and e.job=CLERK;使用GROUP BY 完成部门分组人数SQL SELECT e.ename,d.dname,ed.cou FROM emp e,dept d,( SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno) edWHERE job=CLERK AND e.deptno=d.deptno AND ed.deptno=e.deptno;Oracle系列三：表、（约束）索引、序列、视图的使用一、创建、删除、修改表建立表：Oracle中主要数据类型 VARCHAR2、NUMBER、DATE、CLOB(大量文本)、BLOB(图片、音乐、电影)如果只能复制一张表的结构，但不复制内容，则加上一个不可能成立的条件即可，例如SQL CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1=2)例如创建表Person如下：CREATE TABLE person( pid VARCHAR2(18), name VARCHAR2(30), age NUMBER(3), birthday DATE, sex VARCHAR(2) DEFAULT M);如果发现创建表后需要添加特定的列，例如address列，则可以使用ALTER TABLE命令SQL ALTER TABLE person ADD(address VARCHAR2(20);修改表中的列属性SQL ALTER TABLE person MODIFY(address VARCHAR2(30);在数据库程序开发中，很少去修改表结构，在IBM DB2中就没有提供ALTER TABLE命令在Oracle中提供RENAME命令对表进行重命名SQL RENAME person to personer;在Oracle中要清空一张表的数据，但又不需要回滚，需要立即释放资源(与DELETE区别)SQL TRUNCATE TABLE personer;（与DROP TABLE区别：前者删除内容，后者删除表）二、表的约束约束分类：主键约束、唯一约束、检查约束、非空约束、外键约束添加约束如下：CREATE TABLE person( pid VARCHAR2(18), name VARCHAR2(30) NOT NULL, age NUMBER(3) CHECK(age BETWEEN 0 AND 150), birthday DATE, sex VARCHAR(2) DEFAULT M , CONSTRAINT person_pid_pk PRIMARY KEY(pid), CONSTRAINT person_name_uk UNIQUE(name), CONSTRAINT person_sex_ck CHECK(sex IN(M,F);以上约束可以采取自动命名和手动命名现在要再添加一张表，使用主-外键约束CREATE TABLE book( bid NUMBER PRIMARY KEY, bname VARCHAR(20), bprice NUMBER(5,2), pid VARCHAR2(18), CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid);这时候如果要删除掉person表的话，就会出现错误，此时可以使用强制性的删除手段DROP TABLE person CASCADE CONSTRAINT;但是这种做法一般不用如果在person 和 book 表中添加记录，而在person表中进行删除一条记录时，假设该记录的pid被book表引用，那么会出现删除错误。如果希望一个表中的数据在删除时，能自动删除对应字表的记录，可以使用级联删除的操作CREATE TABLE book( bid NUMBER PRIMARY KEY, bname VARCHAR(20), bprice NUMBER(5,2), pid VARCHAR2(18), CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE);添加约束语法如下：ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段);约束类型命名规则：PRIMARY KEY:主键字段_PKUNIQUE：字段_UKCHECK：字段_CKFOREIGN KEY：父子段_子字段_FKSQL ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);SQL ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(name);SQL ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);SQL ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)ON DELETE CASCADE;删除约束语法如下：ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;SQL ALTER TABLE person DROP CONSTRAINT person_age_CK;SQL ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;ROWNUM伪列SQL SELECT ROWNUM,empno,ename FROM emp;ROWNUM采用自动编号的形式出现加入只想显示前5条记录，那么ROWNUM SELECT ROWNUM,empno,ename FROM emp;WHERE ROWNUM SELECT * FROM (SELECT ROWNUM m,empno,ename FROM emp WHERE ROWNUM5;不好的查询思路：select ROWNUM,empno,job,mgr from emp t where rownum=10MINUSselect ROWNUM,empno,job,mgr from emp t where rownum CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; (5条记录)SQL SELECT * FROM emp UNION SELECT * FROM emp20; (14条记录)SQL SELECT * FROM emp UNION ALL SELECT * FROM emp20; (19条记录)验证MINUS 和 INTERSECTSQL SELECT * FROM emp MINUS SELECT * FROM emp20; (9条记录)SQL SELECT * FROM emp INTERSECT SELECT * FROM emp20; (5条记录)案例：主键约束添加删除1、创建表的同时创建主键约束一、无命名 create table accounts ( accounts_number number primary key, accounts_balance number ); 二、有命名 create table accounts ( accounts_number number primary key, accounts_balance number, constraint yy primary key(accounts_number) );2、删除表中已有的主键约束一、无命名 SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME=accounts;找出主键名 ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;二、有命名 ALTER TABLE ACCOUNTS DROP CONTRAINT yy;3、向表中添加主键约束 ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);Oracle中视图的操作1、创建视图CREATE VIEW 视图名称 AS 子查询这条子查询是非常复杂的语句SQL CREATE VIEW empv20 AS SELECT empno,ename,job,hiredate FROM emp WHERE deptno=20;2、查询视图SQL SELECT * FROM empv20;3、删除视图SQL DROP VIEW empv20;如果要修改视图，则要先删除视图，在Oracle为了方便用户修改视图，提供了一个替换的命令CREATE OR REPLACE 视图名称 AS 子查询视图可以封装复杂的查询，例如查询部门名称，部门的人数，平均工资以及最低工资的雇员SQL CREATE OR REPLACE VIEW empv20 AS 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 eWHERE d.deptno=ed.deptno AND e.sal=e.min;在开中发每次都写这么长的SQL语句不方便，可以将其建立成视图，以上红色部门如果对视图进行更新操作，在视图中不应该包含真实数据，按以下命令进行操作SQL UPDATE empv20 SET deptno=30 WHERE empno=7369;发现视图已经正常更新，因为emp表中7369编号已经修改为30了，所以在创建视图是有条件的SQL提供了两个重要的参数WITH CHECK OPTION：不能更新视图的创建条件SQL CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20 WITH CHECK OPTION;创建条件不能进行更新了，但其他字段仍然可以更新SQL UPDATE empv20 SET ename=wilson WHERE empno=7369;所以这时可以使用视图的第2个条件：创建只读视图SQL CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20 WITH READ ONLY;Oracle系列四：数据库的设计分析一、序列的使用在很多数据库系统中都存在一个自动增长的列，如果在Oracle中要完成自动增长的功能，只能依靠序列完成序列的创建格式CREATE SEQUENCE sequenceINCREMENT BY nSTART WITH nMAXVALUE n| NOMAXVALUEMINVALUE n| NOMINVALUECYCLE|NOCYCLECACHE n|NOCACHE创建一个myseq的序列SQL CREATE SEQUENCE myseq;创建完该序列之后，所有的自动增长应该由用户自己处理nextVal:取得序列的下一个内容currVal:取得序列的当前内容建立一张表验证序列CREATE TABLE testseq( next NUMBER, curr NUMBER,);SQL INSERT INTO testseq(next,curr) VALUES(myseq.nextval,myseq.currval);将这条SQL执行5次，然后进行查表操作SQL SELECT * FROM testseq;可以发现,nextval的内容始终在自动增长，而curr使用取出当前操作的序列的结果，该序列增长幅度为1如果要进行修改，则加上 INCREMENT BY 长度的语句SQL DROP SEQUENCE myseq;SQL CREATE SEQUENCE myseq INCREMENT BY 2;发现每次取出的结果都是奇数 1,3,5,7,9，序列是从1开始的，我们可以指定序列的开始位置，例如SQL CREATE SEUENCE myseq MAXVALUE 10 INCREMENT BY 2 START WITH 2 CACHE 2 CYCLE;二、同义词的概念(了解)SQL SELECT SYSDATE FROM dual;dual是一张虚拟表，该表在SYS用户下有定义，可以使用以下语句查询到SQL SELECT * FROM tab WHERE TNAME=DUAL;此表在SYS下，但SCOTT用户却可以直接通过表名称访问，正常情况下我们是需要使用用户名.表名称该情况就是同义词的作用创建同义词：CREATE SYSNONYM 同义词名称 FOR 用户名.表名称;例如，将scott.emp 定义 emp 的同义词SQL CREATE SYSNONYM emp FOR scott.emp;删除同义词SQL DROP SYSNONYM emp;同义词这种特性只适合于Oracle数据库三、用户管理(*)创建用户: CREATE USER 用户名 IDENTIFIED BY 密码;SQL CREATE USER test IDENTIFIED BY test123;打开一个新的窗口使用test用户登录，发现其没有session权限无法进行登录，此时要进行授权SQL GRANT CREATE SESSION TO test;将创建SESSION权限给test用户，之后该用户可以正常登录，但是其没有创建表的权限Oracle中可以将多个权限定义成一组角色，分配该角色给用户即可在Oracle中主要提供了两个角色：CONNECT、RESOURCE，将这两个角色赋予test用户SQL GRANT CONNECT,RESOURCE TO test;管理员对用户密码进行修改：SQL ALTER USER test IDENTIFIED BY hello;在一般系统中，在用户进行第一次登录时可以修改密码，可以使用如下方式ALTER USER 用户名 PASSWORD EXPIRE;SQL ALTER USER test PASSWORD EXPIRE;这时会提示用户输入旧口令及新的密码锁住用户和对用户解锁SQL ALTER USER test ACCOUNT LOCK;SQL ALTER USER test ACCOUNT UNLOCK;此时，想查询SCOTT用户下的表EMP，发现没有权限，执行如下命令即可SQL GRANT SELECT,DELETE ON scott.emp TO test;收回权限的命令：SQL REVOKE SELECT,DELETE ON scott.emp FROM test;数据库的备份与恢复数据库备份：exp数据库恢复：imp嵌套表的概念(了解)嵌套表：在一个表中还包含另外一个子表首先为嵌套表指定类型，该类型需要单独定义SQL CREATE TYPE pr