经典_Oracle的sql语句百例训练

1、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 /nologSQL>conn scott/tiger若是使用SYS的账号进行登录的话，则使用以下语句SQL>c

2、onn / 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,e

3、name,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|',Jo

4、b:'|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 sal>1500 AND comm is NOT NULL;如果是或的是关系，则使用 OR【13】查询基本工资不大于1500

5、，同时不可以领取奖金的雇员信息SQL> SELECT * FROM emp WHERE NOT(sal>1500 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

6、empWHERE empno NOT IN(7369,7499);【17】SQL中LIKE语句要注意通配符 % 和 _SQL> SELECT * FROM empWHERE hiredate LIKE '%81%'【18】查看雇员编号不是7369的雇员信息，使用<>或!=SQL> SELECT * FROM empWHERE empno<>7369;【19】要求对雇员的工资由低到高进行排序，升序为默认(ASC)，降序(DESC)SQL> SELECT * FROM empGROUP BY sal;【20】查看出部门号为10的雇员信息，查

7、询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列SQL> SELECT * FROM empWHERE deptno=10GROUP BY sal DESC,hiredate ASC;数据库系统中，每个数据库之间区别最大的就是在函数的支持上，单行函数是最简单的函数，单行函数分为1、字符函数：接受字符输入并且返回字符或数值2、数值函数：接受数值输入并返回数值3、日期函数：对日期型数据进行操作4、转换函数：将一种数据类型转换为另一种数据类型5、通用函数：NVL、DECODE 函数字符函数：【1】大小写转换 UPPER 和 LOWERSQL> SELECT UPPER(

8、9;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;这里注意的是O

9、racle中字符串截取从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则对整数进行四舍

10、五入，变为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():下一个的今天的日期LA

11、ST_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

12、) 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_C

13、HAR(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)*

14、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,'CLER

15、K','业务员','SALESMAN','销售人员',MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') 职位FROM emp;SQL简单语句练习：【1】找出佣金高于薪金的60%的员工SQL> SELECT * FROM emp WHERE comm>sal*0.6【2】找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料SQL> SELECT * FROM

16、 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】找出各

17、月倒数第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 enam

18、e 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

19、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】要求查询雇员的编号

20、、姓名、部门编号、部门名称及部门位置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 em

21、p e,emp m,dept dWHERE e.mgr = m.empno AND e.deptno=d.deptno;【4】查询每个雇员的姓名、工资、部门名称，工资在公司的等级(salgrade)，及其领导的姓名所在公司的等级<1>先确定工资等级表的内容SQL> SELECT * FROM salgrade;<2>查询每个雇员的姓名、工资、部门名称和工资在公司的等级SQL> SELECT e.ename,e.sal,d.dname,s.gradeFROM emp e,dept d,salgrade sWHERE e.deptno=d.deptno AND

22、e.sal BETWEEN s.losal AND s.hisal;<3>查询其领导姓名及工资所在公司的等级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】左

23、连接与右连接的概念，"+"在等号左边表示右连接，反之，左连接查询雇员的编号、姓名及其领导的编号、姓名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 * FR

24、OM 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()：求出

25、一组中最大值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 emp

26、WHERE AVG(sal)>2000GROUP BY deptno;出错，WHERE子句中不能出现分组函数的条件，要使用HAVING子句上述语句应该改为如下SQL> SELECT deptno,AVG(sal)FROM empGROUP BY deptnoHAVING AVG(sal)>2000【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要求从事同一工作的雇员月工资合计大于$5000,输出结果按月工资的合计升序排序<1>按工作分组，求出非销售人员的月工资总和SQL> SELECT job,SUM(sal)FROM empWHERE j

27、ob<>'SALESMAN'GROUP BY job;<2>对分组条件进行限制，然后进行排序,HAVING子句不能使用别名SQL> SELECT job,SUM(sal) totalSalFROM empWHERE job<>'SALESMAN'GROUP BY jobHAVING SUM(sal) > 5000ORDER BY totalSal;【3】分组函数可以嵌套使用，但是在SELECT列中就不能再出现该分组条件的列名了SQL> SELECT deptno,MAX(AVG(sal)FROM empGRO

28、UP BY deptno;出错！修改如下SQL> SELECT MAX(AVG(sal)FROM empGROUP BY deptno;【4】查询出比7654工资要高的全部雇员的信息<1>首先要查询雇员编号7654的工资SQL> SELECT sal FROM emp WHERE empno=7654;<2>以上述条件的结果最后后续查询的依据SQL> SELECT * FROM empWHERE sal>(SELECT sal FROM emp WHERE empno=7654);子查询在操作中分为以下三类：1、单列子查询：返回的结果是一列的内容

29、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】要求查询 部门名称、部门员工数、部门平均工资，部门的最低收入雇员的姓名<1>查询部门员工数、部门平均工资SQL> SELECT deptno,COUNT(empno),AVG(sal)FRO

30、M empGROUP BY deptno;<2>查询部门的名称，及最低收入雇员姓名，要进行表关联(子查询)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：指定一个查询范围，例如查询每

31、个部门的最低工资(返回值有多个)SQL> SELECT * FROM empWHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);ANY：=ANY(与IN操作一样)、>ANY(比最小大)、<ANY(比最大小)SQL> SELECT * FROM empWHERE sal <ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);ALL: >ALL(比最大要大)、<ALL(比最小的小),SQL语句类似上面SQL多列子查询示例如下SQL> SELECT * F

32、ROM 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>

33、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之后，数据永久保存在

34、数据库中，在此之前，事务的操作都可以回滚验证事务过程：<1>创建一张临时表，只包含部门10SQL> CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno=10;<2>删除emp10中的7782雇员SQL> DELETE FROM emp10 WHERE empno=7782;再打开另一个窗口，发现数据还存在，此时如果可以使用以下的两种命令进行事务处理COMMIT 和 ROLLBACK 提交事务和回滚事务SQL查询练习【1】列出至少一个员工的所有部门SQL> SELECT d.*,ed.cou FROM

35、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"(办事员)的姓名及其部门名称，部门的人数<1>关

36、联dept表SQL> SELECT e.ename,d.dnameFROM emp e,dept dWHERE e.deptno=d.deptno and e.job='CLERK'<2>使用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 e

37、d.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  VAR

38、CHAR2(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命令在Ora

39、cle中提供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)

40、 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  N

41、UMBER 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表中进行删除一条记录时，假设该记录

42、的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 TAB

43、LE 表名称 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 pers

44、on_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 p

45、erson_book_pid_fk;ROWNUM伪列SQL> SELECT ROWNUM,empno,ename FROM emp;ROWNUM采用自动编号的形式出现加入只想显示前5条记录，那么ROWNUM<=5SQL> SELECT ROWNUM,empno,ename FROM emp;WHERE ROWNUM<=5;但是如果要查询5-10条的记录的话，则查询不出，只能采用子查询的方式SQL> SELECT * FROM (SELECT ROWNUM m,empno,ename     FROM emp   

46、 WHERE ROWNUM<=10) tmpWHERE tmp.m>5;不好的查询思路：select ROWNUM,empno,job,mgr from emp t where rownum<=10MINUSselect ROWNUM,empno,job,mgr from emp t where rownum<=5集合操作：在Oracle中提供了3类集合操作:并、交、差UNION：将多个查询结果组合到一个查询结果之中，没有重复值UNION ALL：与UNICON不同的是包含重复值INTERSECT：返回多个查询结果中相同的部分MINUS：返回两个查询结果的差集

47、验证UNION和UNION ALLSQL> 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> SE

48、LECT * 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_num

49、ber) );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、创建视图

50、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 子查询视图可以封装复杂的查询，例如查询部门名称，部门的人数，平均工资以及最低工资的

51、雇员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

52、> 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'

53、; 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| NOMAXVALUEMI

54、NVALUE 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次，然后进行查表

55、操作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 WI

56、TH 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> C

57、REATE 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用户，之后该用户可以正常登录，但是其没有

58、创建表的权限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