Oracle学习笔记

我会带着你远行 2024-02-18 15:40 54阅读 0赞

**1.Oracle实例:位于物理内存里的数据结构，Oracle用它来管理，反问数据库。Oracle实例就是平常所述的服务。**

**2.PL/sql developer 使用管理员身份运行（window7/windows8的用户）**

**3.每次开始操作都要开启服务（oracle服务一般只要开启两个 serviceORCL和Listener）**

**4.企业管理器中有:实例:orcl数据库，orcl数据库里有system用户，system用户或者dba用户的权限比较大，可以管理其他用户**

**5.对象:指定该用户可以操作那些用户**

**系统:设置真正的权限**

**角色:对对象和系统权限的封装**

**6.给一个列取别名的三种方式:**

**①在列名和别名之间加入as关键字，得到的结果是大写。**

**②直接在列名后面写别名，得到的结果也是大写**

**③在列名后加as和别名，并把别名用双引号括起，可以得到想要的格式。**

**7.删除一个服务:①将DOS窗口以管理员身份运行**

**②输入sc delete +服务名**

**8.distinct:滤重eg:select distinct department\_id form employees**

**9.sql与sql\*plus的区别:**

**sql是一种语言，关键字不能缩写，是用语句控制数据表的定义信息和表中的数据。**

**sql\*plus是一种环境，Oracle的特性之一，关键字可以缩写(desc,ed)，命令不能改变数据库中的数据的值**

**10.字符串用‘’括起来，别名用””括起来**

**11.常见的数据库对象：表、视图、序列、索引、同义词**

**12.创建表名的命名规则：**

**• 必须以字母开头**

**• 必须在1–30 个字符之间**

**• 必须只能包含A–Z, a–z, 0–9, \_, $, 和 \#**

**• 必须不能和用户定义的其他对象重名**

**• 必须不能是Oracle 的保留字**

**1. 基本的查询语句**

**(1).select *sysdate* from *dual*;**

**(2).select *\** from employees;**

**(3).select *employee\_id,last\_name,job\_id* fromemployees;**

**(4). select sysdate+1 from dual;(*日期只能做加减法运算不能做乘除运算*)**

**(5). select *last\_name "name"*,commission\_pct\*2 from employees;（*别名是多个单词且中间用空格隔开时候用””来取别名*）**

**(6).select *last\_name as name* ,commission\_pct\*2from employees;**

**(7).select *last\_name name* ,commission\_pct fromemployees;**

**(8).select last\_name||'\`ssalary is'||' 'salary from employees(*连接符的使用*)**

**(9).select distinct department\_id from employees**

**(10).select last\_name, *distinct* department\_id fromemployees;(*使用distinct导致前后列不能一一对应*)**

**(11).select *distinct* job\_id from employees;**

**注意：①sql语言大小写不敏感,SQL可以写在一行或者多行，关键字不能被缩写，也不能分开写，各个子句一般要分开写，用缩进提高代码的可读性。**

**②空值不是0空值参与运算以后还是空值。**

**③日期和字符串只能在’’中出现**

**2. 过滤和排序数据**

**(1).select employee\_id,last\_name,salary from employeeswhere department\_id=90;**

**(2).select employee\_id ,last\_name,salary from employeeswhere last\_name=’Chen’(‘’中的内容严格区分大小写);**

**(3).selec employee\_id,last\_name,salary from employees wherelower(last\_name)=’chen’;**

**(4). select last\_name,hire\_date from employees wherehire\_date=’7-6月-19994’;**

**(5).select last\_name,hire\_date from employees whereto\_char(hire\_date,’yyyy-mm-dd’)=’1994-06-07’**

**(6)select last\_name,salary from employees wheresalary=>6000 and salary<=8000;**

**(7)select last\_name,salary from employees where salarybetween 6000 and 8000(包含边界);**

**(8)select last\_name,salary from employees where salary in(6000,8000);(刚好为6000和8000)**

**(9)select last\_name,salary from employees where salary=6000or salary=8000;**

**(10)select last\_name,salary from employees where last\_namelike ‘%a%’;**

**(11)select last\_name,salary from employees where last\_namelike ‘\_\_a%’;**

**(12)select last\_name,salary from employees where last\_namelike ’- -%’;**

**(13)select last\_name,salary from employees where last\_namelike ‘%\\\_%’ escape ‘\\’(转义字符的使用);**

**(14) select last\_name,salary from employees wherecommission\_pct is null;**

**(15)select last\_name,salary from employees wheredepartment\_id =80 order by desc(asc)(排序默认为asc)**

**(16)select last\_name,salary from employees order by salarydesc,last\_name asc;(多重排序)**

**(17) select last\_name, job\_id, hire\_date from employeeswhere to\_char(hire\_date,'yyyy-mm-dd') between '1998-02-01' and'1998-05-01' (日期取范围)**

**3. 单行函数（一行记录对应一个结果）**

**(1) 字符函数**

**1） 大小写控制**

**(1)select *lower*(‘ATZHAOLIUBAO’),*upper*(‘AtZhaoLiuBao’),*initcap*(‘atzhao liu bao’);**

**(2)select \* from employees where *lower*(last\_name)=’king’;**

**(3)select \* from employees where *upper*(last\_name)=’KING’;**

**(4)select \* from employees where *initcap*(last\_name)=’King’;**

**2） 字符控制**

**(1)select concat(‘hello’,’world’) from dual;//helloworld**

**(2)select substr(‘helloworld’,2,4);//ello(2,4分别代表起始位置和长度)**

**(3)select length(‘helloworld’) from dual;//10**

**(4)select instr(‘hellojava’,’l’) from dual;//3**

**(4)select instr(‘hellojava’,’w’) from dual;//0**

**(5)select employee\_id,last\_name,lpad(salary,10,’\*’) fromemployees;(左对齐函数)**

**(6)select employee\_id,last\_name,rpad(salary,10,’\*’) from employees;(右对齐函数)**

**(7)select trim(‘h’ from ‘helloworld’) from dual;(去除首尾满足要求的字符)**

**(8)select replace(‘abbced’,’b’,’m’);(替换所有满足条件的字符)**

**(2) 数值函数**

**1).select round(435.25,2) from dual;//435.25**

**2)select round(435.45) from dual;//435**

**3)select round(435.45,-2) from dual;//400**

**4)select trunc(435.45,1) from dual;//435.4**

**5)select trunk(435.25) from dual;//435**

**6)select trunk(435.45,-1) from dual;//430**

**7)select mod(1100,200) from duall;//100(求余函数)**

**(3) 日期函数**

**1) selectemployee\_id,last\_name,months\_between(sysdate-hire\_date) from employees;(判断两个日期之间相差多少个月)**

**2) select add\_months(sysdate,2),add\_months(sysdate,-3);(某个日期加减多少个月后的日期)**

**3) select next\_day(sysdate,’星期日’) fromdual;(离某个日期最近的下一个星期几对应的日期)**

**4) select last\_day(sysdate) fromdual;(某个日期的当前月的最后一天)**

**5) select last\_name,hire\_date fromemployees where hire\_date=last\_day(hire\_date)-1;**

**6) select round(sysdate,’month’),trunk(sysdate,’hh’)from dual;(日期的求大约和截断)**

**(4) 通用函数**

**1) 隐式数据类型转换**

**(1) varchar2←→number**

**select 12+’2’ fromdual;//14**

**(2) varchar2←→date**

**select sysdate+’2’ fromdual;**

**(3) char→number**

**(4) char→date**

**(5) date←→varchar2←→number**

**2) 显示数据类型转换**

**date←→varchar2←→number**

**(1) select last\_name,hire\_date fromemployees where to\_char(hire\_date,’yyyy\_mm\_dd’)=’1994-06-07’;**

**(2) select last\_name,hire\_date fromemployees where to\_char(hire\_date,’yyyy”年”mm”月”dd”日”’)=’1994”年”6“月”dd“日”’;**

**(3) selectto\_char(123456.789,’000,000,999.99’) from dual;//123,156.789;**

**(4) selectto\_char(123456.789,’$000,000,999.99’) from dual;//123,156.789;//$123,156.789**

**(5) selectto\_char(123456.789,’L000,000,999.99’) from dual;//￥123,156.789;**

**(5) 通用函数（适用于任何数据类型，包括空值）**

**1)nvl(x1,x2);(如果x1为空则返回x2)**

**(1)select employee\_id,last\_name,salary\*(12+nvl(commission\_pct,0))from employees;**

**(2)select last\_name,nvl(to\_char(department\_id),’没有部门’) fromemployees;**

**2)nvl2(x1,x2,x3);(x1不为空返回x2,x1为空返回x3)**

**3)nullif(x1,x2);(相等返回null不等返回x1)**

**4)coalesce(x1,x2,x3…xn);(如果前一个为空则返回后一个)**

**(6)条件表达式**

**1)case …when…then…when…then…else…end**

**(1)selectemployee\_id,last\_name,department\_id,**

**case department\_id**

**when 10 then salary\*1.1**

**when 20 then salary\*1.2**

**else salary\*1.3**

**end new\_sal**

**from employees**

**where department\_id in(10,20,30);**

**3) decode();**

**(1) selectemployee\_id,last\_name,department\_id,**

**decode(**

**department\_id,10,salary\*1.1,**

**20,salary\*1.2,**

**salaty**

**) as new\_sal**

**from employees**

**where department\_id in(10,20,30);**

**(2) selectlast\_name,length(last\_name) from employees order by last\_name asc**

**4. 多表查询(如果没有过滤条件则会出现“笛卡尔集错误”)**

**(1) 等值连接(等值与非等值都属于内连接)**

**select e.last\_name,e.department\_id,d.department\_name,l.city**

**from employees e,departments d,locations l**

**where e.department\_id=d.department\_id andd.location\_id=l.location\_id;**

**(2) 非等值连接**

**select employee\_id,last\_name,salary,grade\_level**

**from employees e,job\_gredes j**

**where e.salary between j.lowest\_sal and j.highest\_sal;**

**(3) 外连接**

**1) 左外连接**

**select e.last\_name,e.department\_id,d.department\_name**

**from employees e,departments d**

**where e.department\_id=d.department\_id (+);**

**2)右外连接**

**select e.last\_name,e.department\_id,d.department\_name**

**from employees e,departments d**

**where e.department\_id(+)=d.department\_id ;**

**(4) 使用SQL:1999语法连接**

**(1) 使用cross join 连接出现”笛卡尔集”错误**

**select e.last\_name,e.department\_id,d.department\_name**

**from employees e cross join departments d;**

**(2) 使用nature join连接时限制条件过多而使结果不完整**

**select last\_name,department\_id,department\_name**

**from employees nature join departments;**

**(3) 使用using 连接(使用using连接时必须要保证两个表的连接条件的列名一样，并且数据类型也要一样)**

**select last\_name,department\_id,department\_name**

**from employees join departments**

**using(department\_id);**

**(4) 用on来连接**

**(1)select e.last\_name,e.department\_id,d.department\_name**

**from employees e**

**join departments d on e.department\_id=d.department\_id;**

**(2) selecte.last\_name,e.department\_id,d.department\_name,l.city**

**from employees e**

**join departments d on e.department\_id=d.department\_id**

**join locations l on d.location\_id=l.location\_id;**

**(5) 使用full outer join…on实现满外连接**

**select last\_name,d.department\_id,department\_name**

**from employees e full outer join departments d**

**on e.department\_id=d.department\_id;**

**(6) 自连接**

**selectemp.last\_name,manager.last\_name,manager.salary,manager.email**

**from employees emp,employees manager**

**where emp.manager\_id=manager.employee\_id andlower(emp.last\_name)=’chen’;**

**5. 分组函数**

**(1) 常用的分组函数：avg()、count()、max()、min()、sum()、stddev()（方差）**

**(2) select avg(salary),count(salary),max(salary),min(salary),sum(salary) from employees;**

**(3) selectmax(last\_name),max(hire\_date) from dual;**

**(4) max()\\min()\\count()中的参数可以是number、varchar、date类型。**

**(5) avg()\\sum()只能应用在number类型。**

**(6) select count(1),count(2),count(\*)from employees;//107,107,107;**

**(7) avg()=sum()/count();**

**(8) select count(commission\_pct)from employees;//35(说明count()函数只计算非空的);**

**(9) select count(nvl(commission\_pct,1))from employees;//107(单行函数与组函数嵌套使用)**

**(10) select count(distinct department\_id) from employees;//11**

**(11) select department\_id,avg(salary) from employees group bydeparment\_id;**

**(12) select department\_id,avg(salary) from employees where department\_idin(20,30,40) group by department\_id;**

**(13) select department\_id,avg(salary),job\_id from employees group bydeparment\_id,job\_id;**

**//在select中凡是不是组函数的列都必须要出现在group by字句中**

**(14) select department\_id,avg(salary)**

**from employees**

**having avg(salary)>6000**

**group by department\_id**

**order by avg(salary) asc;//不能在where字句中使用组函数，可以在having字句中**

***注意：当有组函数出现但右需要过滤条件时应该用having而不能使用where***

**6. 子查询**

**(1)单行子查询(只能使用单行比较操作符< ,>,>=,<=,<>)**

**(1)Eg:谁的工资比Abel的高？**

**select last\_name,salary fromemployees**

**where salary>(select salary fromemployees where lower(last\_name)=’abel’);**

**(2)查询Chen的manager的信息。**

**select \* from employees**

**where employee\_id=(select manager\_id from employees wherelower(last\_name)=’chen’);**

**(3)查询最低工资大于50号部门最低工资的部门的id和其最低工资。**

**selectdepartment\_id,min(salary)**

**from employees**

**group bydepartment\_id**

**having min(salary)>(selectmin(salary) where department\_id=50);**

**(2)多行子查询(关键字in、any、all )**

**Eg:返回其他部门中比job\_id为’IT\_PROG’部门任一工资低的员工的员工号、姓名、job\_id以及salary。**

**selectemployee\_id,last\_name,job\_id,salary**

**from employees**

**wherejob\_id<>’IT\_PROG’**

**andsalary<any(select salary from employees where job\_id=’IT\_PROG’);**

**7.创建和管理表(以下的DDL语句均不可回滚)**

**(1)创建表的第一种方式：**

**createtable info(**

**tid number(10),**

**tname varchar2(20),**

**hire\_date date,**

**salary number(5,2)**

**)**

**(2)创建表的第二种方式（使用子查询来参创建表）**

**（1）create tableinfo**

**as**

**selectemployee\_id ,last\_name,hire\_date,salary**

**where department\_id=80;(数据也被复制过来)**

**（2）create tableinfo**

**as**

**selectemployee\_id ,last\_name,hire\_date,salary**

**where 1=2(创建空表)**

**(3)修改表(对默认值的修改只影响今后对表的修改)**

**(1)增加一列**

**altertable info add(email varchar2(20));**

**(2)修改一列**

**Altertable info modify(tid number(20));**

**Altertable info modify(salary varchar2(20,2) default 2000);(设置默认值)**

**(3)删除一个列**

**Altertable info dorp column email;**

**(4)重命名一个列**

**Altertable info rename column salary to sla;**

**(4)删除表**

**Drop table info;(不可回滚)**

**Truncate tableinfo;(清空表，留下表结构，不可回滚)**

**8.数据处理(均能回滚)**

**(1)向表中插入数据(方式一)**

**Insert into info values(001,’zlb’,null,null);**

**Insert into info(tid,tname) values(002,’zxc’);**

**(2)向表中插入数据(方式二)**

**Insert intoinfo(tid,tname,salary,hire\_date)**

**Selectemployee\_id,last\_name,salary,hire\_date**

**From employees**

**Where department\_id=80;**

**(3)创建脚本**

**insert into info(tid,tname,salary,hire\_date)values(&id,'&name',&salary,'&hire\_date');**

**(4)更新数据**

**Update info setsalary=105 where tid=2;**

**若:commit 则数据被永久修改，不能rollback**

**若：直接rollback则能回到修改之前的状态**

**(5)删除数据**

**Delete from info where tid=1;**

**(6)数据库事务处理**

**(1)设置保存点：savepointa;**

**(2)回到保存点a:rollbackto savepoint a;**

**9.约束**

**(1)非空约束（not null）只能作用在列级**

**Create table emp2(**

**Idnumber(10) constraint emp2\_id\_nn not null,**

**Namevarchar2(20) constraint emp2\_name\_nn not null,**

**Salaryvarchar2(20)**

**);**

**(2)唯一性约束（unique）**

**Createtable emp3(**

**Idnumber(10) constraint emp3\_id\_uk unique,--列级约束**

**Namevarchar2(20) constraint emp3\_name\_nn not null,--列级约束**

**Salaryvarchar2(20),**

**Email vachar2(20) ,**

**constraint emp3\_email\_uk unique(Email)--表级约束**

**);**

**(3)主键约束（primarykey）**

**Createtable emp4(**

**Idnumber(10) constraint emp4\_id\_pk primary key,--列级约束**

**Namevarchar2(20) constraint emp4\_name\_nn not null,--列级约束**

**Salaryvarchar2(20),**

**Emai4l vachar2(20) ,**

**constraint emp4\_email\_uk unique(Email)--表级约束**

**);**

**主键:唯一且非空**

**(4)外键约束（foreignkey…references）**

**Createtable emp4(**

**Id number(10),**

**Name varchar2(20),**

**Salary varchar2(20),**

**Emai4l vachar2(20) ,**

**Department\_id varchar2(20),**

**constraintemp4\_email\_uk unique(Email),--表级约束**

**foreign key(department\_id)references departments(department\_id)—表级约束使用系统----提供的默认约束名（列级约束也可以使用系统默认提供的约束名）**

**);**

**A.on delete cascade:级联删除(可以在列级，也可以在表级)**

**Create table emp4(**

**Id number(10),**

**Namevarchar2(20),**

**Salary varchar2(20),**

**Emai4l vachar2(20) ,**

**Department\_id varchar2(20),**

**constraint emp4\_email\_uk unique(Email),--表级约束**

**foreignkey(department\_id) references departments(department\_id) on delete cascade**

**);**

**B. on delete set null：级联置空（可以在列级，也可以在表级）**

**Create table emp4(**

**Id number(10),**

**Name varchar2(20),**

**Salary varchar2(20),**

**Emai4l vachar2(20) ,**

**Department\_id varchar2(20),**

**constraintemp4\_email\_uk unique(Email),--表级约束**

**foreignkey(department\_id) references departments(department\_id) on delete set null**

**);**

**(5)检查约束（check）**

**Createtable emp4(**

**Id number(10),**

**Name varchar2(20),**

**Salary varchar2(20) constraintemp4\_salary\_check check(salary>1500 and salary<30000)**

**);**

**(6)操作约束**

**a.添加非空约束**

**altertable emp4 modify(salary varchar(20) not null);**

**b.删除约束**

**alter table emp4 drop constraintemp4\_name\_nn;(若没有显示的命名则在删除的时候写系统自动命名的约束)**

**c.添加唯一性约束**

**alter table emp5 add constraintemp5\_name\_uk unique(name);**

**d.有效化或无效化约束**

**无效化：alter table emp3disable constraint emp3\_eamil\_uk;**

**有效化：alter table emp3eable constraint emp3\_eamil\_uk;(要想此约束有效化成功则必须先处理email中重复的数据)**

**10.视图（view）**

**(1)createview emp\_view**

**as select employee\_id,last\_name**

**from employees**

**where department\_id=80;(创建视图的唯一方式)**

**(2)createview emp\_view2**

**as select e.employee\_id,e.last\_name,d.department\_id,l.loacation**

**from employees e ,department d,location l;(基于多个表创建视图，可以简化查询)**

**(3)createor repalace view emp\_view**

**as select e.employee\_id,e.last\_name,d.department\_id**

**from employees e ,department d;(修改或替换视图)**

**(4)create or repalace view emp\_view**

**as select e.employee\_id,e.last\_name,d.department\_id**

**from employees e,department d where d.department\_id=e.department\_id**

***with read only*;(将视图设置为只读)**

**(5)复杂视图与简单视图的区别：如果使用到分组函数则是复杂视图，如果没有使用到分组函数就是简单视图**

**create view emp\_view**

**as select department\_id,avg(salary) avg\_sal--用到组函数的列一定要取别名**

**from employees**

**group by department\_id;**

**注意：当视图定义中包含以下元素之一时不能使用update:**

**(1)组函数**

**(2)group by子句**

**(3)distinct 关键字**

**(4)rownum 伪列**

**(5)列的定义为表达式**

**(6)表中非空的列在视图定义中未包括**

**(6)Top-N分析(分析查询一个列中最？的n个值)**

**Eg:查询公司工资前10的员工。**

**selectrownum,employee\_id,last\_name,salary**

**from(select employee\_id,last\_name,salary**

**fromemployees**

**orderby salary desc)**

**whererownum<=10;**

**eg:查询公司工资前40-50的员工。(ps:伪列只能使用<=，不能使用>=否则则没有结果)**

**selectrn,employee\_id,last\_name,salary**

**from(**

**selectrownum,employee\_id,last\_name,salary**

**from(select employee\_id,last\_name,salary**

**fromemployees**

**orderby salary desc**

**)**

**Where rn>40and rn<=50;**

**11.其他数据库对象**

**(1)序列:可供多个用户用来产生唯一数值的数据库对象**

**• 自动提供唯一的数值**

**• 共享对象**

**• 主要用于提供主键值**

**• 将序列值装入内存可以提高访问效率**

**a.创建序列**

**Create sequence empseq**

**Increment by 10 – 每次增长10**

**Start with 10 – 从10 开始增长**

**Maxvalue 100**

**Cycle**

**Nocatche 不需要缓存登录**

**b.查看序列**

**select empseq.nextval fromdual;(首次使用时必须使用nextval这个属性来提供初始值)**

**select empseq.curval from dual;(当前值)**

**c.使用序列**

**insert intoemployees(employee\_id,last\_name,salary)**

**values(empseq.nextval,’zlb’,4000);**

**注意：将序列值装入内存可提高访问效率**

**序列在下列情况下出现裂缝:**

**• 回滚**

**• 系统异常**

**• 多个表同时使用同一序列**

**如果不将序列的值装入内存(NOCACHE), 可使用表 USER\_SEQUENCES查看序列当前的有效值**

**d.修改序列**

**alter sequence empseq**

**increment by1**

**start with 1**

**nocycle;**

**e.删除序列**

**drop sequence empseq;**

**修改序列的注意事项**

**• 必须是序列的拥有者或对序列有 ALTER 权限**

**• 只有将来的序列值会被改变**

**• 改变序列的初始值只能通过删除序列之后重建序列的方法实现**

**(2)索引：**

**• 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中**

**• 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度**

**• 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引**

**• 在删除一个表时,所有基于该表的索引会自动被删除**

**• 通过指针加速 Oracle 服务器的查询速度**

**• 通过快速定位数据的方法，减少磁盘 I/O**

**a.创建索引**

**• 自动创建: 在定义PRIMARY KEY 或UNIQUE 约束后系统自动在相应的列上创建唯一性索引**

**• 手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询**

**create index emp\_id\_ix onemployees(employee\_id);**

**b.删除索引**

**drop index  emp\_id\_ix;**

**c.什么时候创建索引？**

**以下情况可以创建索引:**

**• 列中数据值分布范围很广**

**• 列经常在 WHERE 子句或连接条件中出现**

**• 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%**

**d.什么时候不要创建索引？**

**下列情况不要创建索引:**

**• 表很小**

**• 列不经常作为连接条件或出现在WHERE子句中**

**• 查询的数据大于2%到4%**

**• 表经常更新**

**(3)同义词(synonym)**

**a.创建一个同义词**

**create synonym e foremployees;(物理文件)**

**select \* frome;//(e=employees)**

**b.删除同义词**

**drop synonym e;**

**12.控制用户权限**

**• 数据库安全性:**

**– 系统安全性**

**– 数据安全性**

**• 系统权限: 对于数据库的权限**

**• 对象权限: 操作数据库对象的权限**

**(1)创建用户(必须要有创建用户的权限，一般用DBA用户登录并创建用户)**

**create user atzlb01**

**identified by atzlb01;**

**User created**

**(2)授予用户系统权限**

**•  以应用程序开发者为例, 一般具有下列系统权限:**

**– CREATE SESSION（创建会话）**

**– CREATE TABLE（创建表）**

**– CREATE SEQUENCE（创建序列）**

**– CREATE VIEW（创建视图）**

**– CREATE PROCEDURE（创建过程）**

**Grant create session to atzlb01;(授予atzlb01登录权限)**

**(3)创建用户的表空间**

**用户拥有create table权限之外，还需要分配相应的表空间才可开辟存储空间用于创建的表**

**ALTER USERatzlb01QUOTAUNLIMITED**

**ONusers**

**(4)一般用户具有修改自己密码的权限**

**Alter user atzlb01**

**Identified by atzlb;**

**(5)创建角色**

**Create role my\_role;**

**Grant createtable,create session,create view to my\_role;**

**Grant my\_role to atzlab;**

**(6)对象**

**• 不同的对象具有不同的对象权限**

**• 对象的拥有者拥有所有权限**

**• 对象的拥有者可以向外分配权限**

**Grant select,update**

**On scott.employees**

**To atzalb01;(系统用户向外分配权限)**

**Grant select,update**

**On employees**

**To atzalb01;(用户本身向外分配权限)**

**•  WITH GRANTOPTION 使用户同样具有分配权限的权利**

**GRANT select, insert**

**ON departments**

**TO scott**

**WITH GRANT OPTION;**

**• 向数据库中所有用户分配权限**

**GRANT select**

**ON alice.departments**

**TO PUBLIC;**

**收回对象权限**

**• 使用 REVOKE 语句收回权限**

**• 使用 WITH GRANTOPTION 子句所分配的权限同样被收回**

**REVOKE select, insert**

**ON departments**

**FROM scott;**

**Revoke succeeded.**

**13.set运算符**

**(1)union:求并集（去重）**

**SELECT employee\_id, job\_id**

**FROM employees**

**UNION**

**SELECT employee\_id, job\_id**

**FROM job\_history;**

**(2)union all:求并集（不去重）**

**SELECT employee\_id, job\_id,department\_id**

**FROM employees**

**UNION ALL**

**SELECT employee\_id, job\_id, department\_id**

**FROM job\_history**

**ORDER BY employee\_id;**

**(在使用set操作符进行操作时如果使用别名，则只与union前面半句的别名为准，忽略后面半句的别名)**

**(3)INTERSECT 操作符返回两个结果集的交集**

**SELECTemployee\_id EID, job\_id JID, department\_id DID**

**FROM employees**

**INTERSECT**

**SELECT employee\_id, job\_id,department\_id**

**FROM job\_history**

**ORDER BY EID;**

**(4)MINUS操作符：返回两个结果集的差集**

**SELECTemployee\_id EID, job\_id JID, department\_id DID**

**FROM employees**

**MINUS**

**SELECT employee\_id, job\_id,department\_id**

**FROM job\_history**

**ORDER BY EID;**

**使用 SET 操作符注意事项**

**• 在SELECT 列表中的列名和表达式在数量和数据类型上要相对应**

**• 括号可以改变执行的顺序**

**• ORDER BY 子句:**

**– 只能在语句的最后出现**

**– 可以使用第一个查询中的列名, 别名或相对位置**

**selectemployee\_id emp\_id,department\_id, dept\_id,to\_char(null)**

**fromemployees**

**union**

**selectto\_number(null),department\_id,department\_name**

**fromemployees**

**order byemp\_id;(如果数据类型不能匹配采用该方法解决)**

**使用相对位置排序举例**

**COLUMNa\_dummy NOPRINT**

**SELECT'sing' AS "My dream", 3 a\_dummy**

**FROM dual**

**UNION**

**SELECT'I\`d like to teach', 1**

**FROM dual**

**UNION**

**SELECT'the world to', 2**

**FROM dual**

**ORDER BY2;**

**14.高级子查询**

**(1)多列子查询:主查询与子查询返回的多个列进行比较**

**多列子查询中的比较分为两种:**

**• 成对比较**

**• 不成对比较**

**成对比较举例:**

**Eg:查询与141号或174号员工的manager\_id和department\_id相同的其他员工的employee\_id, manager\_id, department\_id**

**SELECT employee\_id, manager\_id, department\_id**

**FROM employees**

**WHERE (manager\_id, department\_id) IN**

**(SELECT manager\_id,department\_id**

**FROM employees**

**WHERE employee\_id IN (141,174))**

**AND employee\_idNOT IN (141,174);**

**不成对比较举例**

**SELECT employee\_id,manager\_id, department\_id**

**FROM employees**

**WHERE manager\_id IN  
(SELECT manager\_id**

**FROM employees**

**WHERE employee\_id IN (174,141))**

**AND department\_id IN  
(SELECT department\_id**

**FROM employees**

**WHERE employee\_id IN (174,141))**

**AND employee\_id NOT IN(174,141);**

**(2)在from字句中使用子查询**

**Eg:返回比本部门平均工资高的员工的last\_name, department\_id, salary及平均工资**

**SELECT a.last\_name, a.salary,**

**a.department\_id, b.salavg**

**FROM employees a, (SELECT department\_id,**

**AVG(salary) salavg**

**FROM employees**

**GROUP BY department\_id) b**

**WHERE a.department\_id = b.department\_id**

**AND a.salary > b.salavg;**

**(3)单列子查询表达式:单列子查询表达式是在一行中只返回一列的子查询**

**在 CASE 表达式中使用单列子查询:**

**Eg:显式员工的employee\_id,last\_name和location。其中，若员工department\_id与location\_id为1800的department\_id相同，则location为’Canada’,其余则为’USA’。**

**SELECTemployee\_id, last\_name,**

**(CASE**

**WHEN department\_id =**

**(SELECTdepartment\_id FROM departments**

**WHERElocation\_id = 1800)**

**THEN 'Canada' ELSE 'USA' END) location**

**FROM employees;**

**在 ORDER BY 子句中使用单列子查询**

**Eg:查询员工的employee\_id,last\_name,要求按照员工的department\_name排序**

**SELECT employee\_id, last\_name**

**FROM employees e**

**ORDER BY (SELECT department\_name**

**FROM departments d**

**WHERE e.department\_id = d.department\_id);**

**(4)相关子查询：子查询中使用主查询中的列**

**Eg:查询员工中工资大于本部门平均工资的员工的last\_name,**

**salary和其department\_id**

**SELECTlast\_name, salary, department\_id**

**FROM employees outer**

**WHERE salary >  
(SELECT AVG(salary)**

**FROM employees**

**WHERE department\_id =**

**outer.department\_id);**

**(5)EXISTS 操作符**

**• 如果在子查询中存在满足条件的行:**

**– 不在子查询中继续查找**

**– 条件返回 TRUE**

**• 如果在子查询中不存在满足条件的行:**

**– 条件返回 FALSE**

**– 继续在子查询中查找**

**Eg：查询公司管理者的employee\_id,last\_name,job\_id,department\_id信息**

**SELECTemployee\_id, last\_name, job\_id, department\_id**

**FROM employees outer**

**WHERE EXISTS ( SELECT 'X'**

**FROM employees**

**WHERE manager\_id =**

**outer.employee\_id);**

**(6)NOT EXISTS操作符应用举例**

**查询departments表中，不存在于employees表中的部门的department\_id和department\_name**

**SELECTdepartment\_id, department\_name**

**FROM departments d**

**WHERE NOT EXISTS (SELECT 'X'**

**FROM employees**

**WHERE department\_id**

**= d.department\_id);**

**15.plsql基本语法**

**（1）基本语法格式**

**declare**

**声明变量、类型、游标**

**Begin**

**程序执行部分**

**Exception**

**异常的处理**

**End;**

**declare**

**v\_salnumber(10,2);//employees.salary%type;(动态获取类型)**

**v\_emailvachar(20);**

**begin**

**selectsalary,employees into v\_salary,v\_number from employees where employee\_id=100;**

**dbms\_output.put\_line(v\_sal||’,’||v\_email);**

**end;**

**16.plsql\_记录类型**

**declare**

**emp\_record isrecord(-- 声明一个记录类型**

**v\_sal  employees.salary%type,**

**v\_email  employees.email%type**

**);**

**v\_emp\_record emp\_record;-- 定义一个记录类型的成员变量**

**selectsalary,employees into v\_emp\_record from employees where employee\_id=100;**

**dbms\_output.put\_line(v\_emp\_record.v\_sal||’,’|| v\_emp\_record .v\_email);(必须先设置set serveroutput on 命令才能输出)**

**end;**

**赋值符号： :=**

**17.流程控制**

**(1)if…then…elseif…then…else…endif;**

**a.declare**

**v\_sal employees.salary%type;**

**begin**

**select salary into v\_sal**

**from employees**

**where department\_id=150;**

**if v\_sal>10000**

**then dbms\_output.putline(‘salary>10000’);**

**elseif v\_sal>5000**

**then dbms\_output.putline(‘salary>5000’);**

**else dbms\_output.putline(‘salary<5000’);**

**end if;**

**end;**

**b. declare**

**v\_salemployees.salary%type;**

**v\_temp varchar2(20);**

**begin**

**select salary into v\_salfrom employees where employee\_id=150;**

**if v\_sal>=10000 thenv\_temp:='salary>=10000';**

**elsif v\_sal>=5000then v\_temp:='10000>salary>=5000';**

**elsev\_temp:='salary<5000';**

**end if;**

**dbms\_output.put\_line(v\_temp);**

**end;(使用临时变量)**

**(2)case…when..then…when…then…else;**

**declare**

**v\_sal employees.salary%type;**

**v\_temp varchar2(20);**

**begin**

**select salary into v\_sal from employeeswhere employee\_id=150;**

**v\_emp:=**

**casetrunc(v\_sal/5000) when 0 then ‘salary<5000’**

**when 1 then ‘5000<salary<10000’**

**else ‘salary>10000’**

**end;**

**dbms\_output.put\_line(v\_temp);**

**(3)循环结构**

**a.loop循环**

**declare**

**v\_num number(10):=1;**

**begin**

**loop**

**dbms\_output.putline(v\_num);**

**exit when v\_num>100;**

**v\_unm=v\_num+1;**

**end loop;**

**end;**

**b.while循环**

**declare**

**v\_i number(5) :=1;**

**begin**

**while v\_i<=100 loop**

**dbms\_output.put\_line(v\_i);**

**v\_i:=v\_i+1;**

**end loop;**

**end;**

**c.for循环**

**begin**

**for c in 1..100 loop**

**dbms\_output.put\_line(c);**

**end loop;**

**end;**

**18.游标的使用**

**游标的属性：%found 布尔属性，当最近一次读记录时成功返回。则值为true**

**%notfound与%found相反**

**%isopen布尔属性，当游标打开时返回true**

**%rowcount数字型属性，返回已从游标中读取的记录数**

**declare**

**v\_sal employees.salary%type;**

**v\_emp\_id employees.employee\_id%type;**

**--定义游标**

**cursor emp\_sal\_cursor is select salary,employee\_id from employees wheredepartment\_id=80;**

**begin**

**--打开游标**

**open emp\_sal\_cursor;**

**--读取游标**

**fetch emp\_sal\_cursor into v\_sal,v\_emp\_id;**

**while emp\_sal\_cursor%found loop**

**dbms\_output.put\_line(v\_sal||':'||v\_emp\_id);**

**fetch emp\_sal\_cursor into v\_sal,v\_emp\_id;**

**end loop;**

**--关闭游标**

**close emp\_sal\_cursor;**

**end;**

**游标的综合使用**

**declare**

**-- 定义一个记录类型**

**typeemp\_record is record(**

**v\_id employees.employee\_id%type,**

**v\_sal employees.salary%type,**

**v\_hire\_date employees.hire\_date%type**

**);**

**-- 定义一个游标**

**cursor c\_cursor is selectemployee\_id,salary,hire\_date from employees where department\_id=80;**

**begin**

**-- 打开游标**

**open c\_cursor;**

**-- 读取游标**

**fetch c\_cursor into emp\_record;**

**while c\_cursor%found loop**

**dbms\_output.put\_line(emp\_record.v\_id||emp\_record.v\_sla||v\_hire\_date) ;**

**fetch c\_cursor into emp\_recorde;**

**end loop;**

**-- 关闭游标**

**close c\_cursor;**

**end;**

**19.异常处理**

**(1)异常分类：预定义异常,非预定义的异常，用户自定义的异常**

**预定义异常**

**declare**

**v\_salary employees.salary%type;**

**begin**

**select salary into v\_salary**

**from employees**

**where employee\_id>100;**

**dbms\_output.put\_line(v\_salary);**

**exception**

**when too\_many\_rows**

**then dbms\_output.put\_line('输出的行过多');**

**when others**

**then dbms\_output.put\_line('出现其他类型的异常');**

**end;**

**非预定义的异常**

**declare**

**e\_delete\_exception exception;**

**pragma exception\_init(e\_delete\_exception,-2292);**

**begin**

**delete from employees where employee\_id=100;**

**exception**

**when e\_delete\_exception**

**then dbms\_output.put\_line('违反完整性约束条件，故不可删除此用户');**

**end;**

**用户自定义的异常**

**declare**

**e\_too\_high\_sal exception;**

**v\_sal employees.salary%type;**

**begin**

**select salary into v\_sal from employees where employee\_id=100;**

**ifv\_sal>10000**

**thenraise e\_too\_high\_sal;-- 抛出异常**

**endif;**

**exception**

**when e\_too\_high\_sal then dbms\_output.put\_line('工资太高了！');**

**end;**

**20.存储过程/存储函数**

**(1)存储函数和存储过程的唯一区别是函数总向调用者返回数据，而过程则不返回数据。**

**-- 存储函数**

**create or replace function func\_name(dept\_id number, salary number)**

**return number**

**is**

**-- 函数的声明部分：函数的使用过程中，需要声明的变量、记录类型、游标**

**begin**

**-- 函数的执行体**

**exception**

**-- 处理函数执行过程中的异常**

**end;**

**hello\_world 实例**

**create or replace function hello\_world**

**return varchar2**

**is**

**begin**

**return 'hello world';**

**end;**

**调用函数：begindbms\_output.put\_line(hello\_world); end;(也可使用select语句)**

**带参函数:**

**create or replace function hello\_world(v\_log varchar2)**

**return varchar2**

**is**

**begin**

**dbms\_output.put\_line('人家是函数的啦，么么~~');**

**return 'hello world'||v\_log;**

**end;**

**删除函数：drop functionhello\_world；**

**Out 型返回值**

**关于 OUT 型的参数: 因为函数只能有一个返回值, PL/SQL 程序可以通过 OUT 型的参数实现有多个返回值**

**要求: 定义一个函数: 获取给定部门的工资总和和 该部门的员工总数(定义为 OUT 类型的参数).**

**要求: 部门号定义为参数, 工资总额定义为返回值.**

**create or replace function sum\_sal(dept\_id number,total\_count out number)**

**returnnumber**

**is**

**cursor sal\_cursoris select salary from employees where department\_id = dept\_id;**

**v\_sum\_salnumber(8) := 0;**

**begin**

**total\_count:= 0;**

**for c insal\_cursor loop**

**v\_sum\_sal := v\_sum\_sal + c.salary;**

**total\_count := total\_count + 1;**

**endloop;**

**--dbms\_output.put\_line('sum salary: ' || v\_sum\_sal);**

**returnv\_sum\_sal;**

**end;**

**执行函数:**

**delare**

**v\_total number(3):= 0;**

**begin**

**dbms\_output.put\_line(sum\_sal(80, v\_total));**

**dbms\_output.put\_line(v\_total);**

**end;**

**要求: 定义一个函数: 获取给定部门的工资总和和 该部门的员工总数(定义为 OUT 类型的参数).**

**要求: 部门号定义为参数, 工资总额定义为返回值.**

**create or replace function sum\_sal(dept\_id number,total\_count out number)**

**returnnumber**

**is**

**cursorsal\_cursor is select salary from employees where department\_id = dept\_id;**

**v\_sum\_salnumber(8) := 0;**

**begin**

**total\_count:= 0;**

**for c insal\_cursor loop**

**v\_sum\_sal := v\_sum\_sal + c.salary;**

**total\_count := total\_count + 1;**

**endloop;**

**--dbms\_output.put\_line('sum salary: ' || v\_sum\_sal);**

**returnv\_sum\_sal;**

**end;**

**执行函数:**

**delare**

**v\_total number(3):= 0;**

**begin**

**dbms\_output.put\_line(sum\_sal(80, v\_total));**

**dbms\_output.put\_line(v\_total);**

**end;**

**存储过程**

**Eg：定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数**

**create or replace procedure sum\_sal\_procedure(dept\_id number,v\_sum\_sal out number)**

**is**

**cursor sal\_cursor isselect salary from employees where department\_id = dept\_id;**

**begin**

**v\_sum\_sal := 0;**

**for c in sal\_cursorloop**

**--dbms\_output.put\_line(c.salary);**

**v\_sum\_sal :=v\_sum\_sal + c.salary;**

**end loop;**

**dbms\_output.put\_line('sum salary: ' || v\_sum\_sal);**

**end;**

**\[执行\]**

**declare**

**v\_sum\_sal number(10) :=0;**

**begin**

**sum\_sal\_procedure(80,v\_sum\_sal);**

**end;**

**21.触发器**

**(1)触发器在数据库里以独立的对象存储**

**一个helloworld级别的触发器**

**create or replacetrigger hello\_trigger**

**after**

**update on employees**

**--for each row**

**begin**

**dbms\_output.put\_line('hello...');**

**--dbms\_output.put\_line('old.salary:'||:OLD.salary||',new.salary'||:NEW.salary);**

**end;**

**然后执行：update employees setsalary = salary + 1000;**

**(2)触发器的 helloworld: 编写一个触发器, 在向 emp 表中插入记录时, 打印 'helloworld'**

**create or replace trigger emp\_trigger**

**after**

**insert on emp**

**for each row**

**begin**

**dbms\_output.put\_line('helloworld');**

**end;**

**(3)行级触发器: 每更新 employees 表中的一条记录, 都会导致触发器执行**

**create or replace trigger employees\_trigger**

**after**

**update on employees**

**for each row**

**begin**

**dbms\_output.put\_line('修改了一条记录!');**

**end;**

**(4)语句级触发器: 一个 update/delete/insert语句只使触发器执行一次**

**create or replace trigger employees\_trigger**

**after**

**update on employees**

**begin**

**dbms\_output.put\_line('修改了一条记录!');**

**end;**

**(5)使用 :new, :old 修饰符**

**create or replace trigger employees\_trigger**

**after**

**update on employees**

**for each row**

**begin**

**dbms\_output.put\_line('old salary: ' || :old.salary || ', new salary: '|| :new.salary);**

**end;**

**(6) 编写一个触发器, 在对 my\_emp 记录进行删除的时候, 在 my\_emp\_bak 表中备份对应的记录**

**1). 准备工作:**

**create table my\_emp asselect employee\_id id, last\_name name, salary sal from employees**

**create table my\_emp\_bak asselect employee\_id id, last\_name name, salary sal from employees where 1 = 2**

**2). 备份工作**

**create or replace trigger bak\_emp\_trigger**

**before delete on my\_emp**

**for each row**

**begin**

**insert into my\_emp\_bakvalues(:old.id, :old.name, :old.sal);**

**end;**

Oracle学习笔记

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle学习笔记

相关 Oracle学习笔记总结

相关 Oracle学习笔记

相关 oracle 学习笔记 - 视图

相关 oracle学习笔记

相关 Oracle学习笔记

相关 Oracle 学习笔记

相关 Oracle学习笔记

相关 oracle学习笔记2

相关 oracle学习笔记_触发器