oracle笔记

一、主流数据库：

access：小型数据库，100人内，对安全要求不高，比如留言本、信息系统等

sql server、mysql、informix：中型数据库，负载日访问量5000-15000，成本在万元内，比如商务网站

sybase、db2、oracle：大型数据库，负载可以处理海量数据库，安全性很高，贵

1、安装oracle数据库

iso文件，需要使用虚拟demon管理器

数据库取名不能超过8个字符

oracle安装会自动生成sys用户和system用户

sys：超级用户，具有最高权限，具有sysdba角色，有create database权限，密码：change_on_install

system：管理操作员，权限也很大，具有sysoper角色，没有create database权限，密码：manager，一般讲，对数据库维护使用system用户登录

2、oracle启动

在管理--服务--OracleServicemysql、OracleOraHome90TNSListener。

3、oracle卸载

运行oracle的universal installer

运行regedit，进入注册表，删除时一定要小心别删错了

进入HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE,右键删除HKEY_LOCAL_MACHINE\CurrentControlSet\Services下以oracle开头的文件

重启计算机，然后删除硬盘上的oracle目录，如果该目录不让删除，那么把这个目录改成一个别的名字，然后重启机器，再删除它。

删除再program files下的oracle目录

4、oracle管理工具        pl/sql developer最好

A、直接在程序启动sql*plus

B、在运行中输入sqlplus

查看用户：show user

5、sql*plus常用命令

a、连接命令：conn【ect】    conn 用户名/密码@网络服务器【as sysdab/sysoper】，当使用特权用户登录时，必须带上as sysdab/sysoper

conn system/manager        连接system用户

b、disc【onnect】  断开连接

c、passw【ord】  修改密码

d、show user  查看当前用户

e、exit 断开连接并退出

f、start和@  运行sql脚本

sql>@d:\a.sql 或sql>startd:\a.sql

g、edit  编辑指定的sql脚本

sql>edit d: \a.sql

h、spool  将sql*plus屏幕上的内容输出到指定文件中

sql>spool d: \b.sql

sql>spool off

i、&  可以替代变量，而该变量在执行时，需要用户输入

sql>select * from emp where job=’&job’

j、linesize   设置显示行的宽度，默认是80个字符

sql>show linesize

sql>set linesize 90

k、pagesize   设置每页显示的行数目，默认是14，用法同linesize

二、oracle用户管理

1、创建用户 create user     具备dba权限

conn system/manager

sql>create user xiaoming identified by m123；    密码m123，密码必须字母开头

2、修改密码

sql>password xiaoming；

3、删除用户     自己不能删除自己

删除用户时，已经创建了表，那么就需要在删除时带参数cascade，drop user 用户名 cascade；

sql>drop user xiaoming；

4、grant  赋权限

sql>grant connect to xiaoming；

sql>grant resource to xiaoming；

sql>create table test(userId varchar2(30),username varchar2(30);

sql>desc test          可查看table中的内容

希望xiaoming用户可以去查询scott的emp表

sql>grant select on emp to xiaoming；

sql>conn xiaoming/m1234

sql>select * from scott.emp；

希望xiaoming用户可以去修改、查询、添加、删除scott的emp表

sql>grant all on emp to xiaoming；

5、revoke  回收权限

scott希望收回xiaoming对emp表的权限

sql>revoke select on emp from xiaoming；

希望xiaoming用户可以去查询scott的emp表，同时还希望xiaoming可以把这个权限传给xiaohong

sql>grant select on emp to xiaoming with grant option；

sql>conn xiaoming/m1234;

sql>grant select on scott.emp to xiaohong;

注：如果是对象权限，需要在后面加入with grant option

sql>grant select on emp to xiaoming with grant option；

如果是系统权限

sql>grant connect to xiaoming with admin option；

如果scott把xiaoming对emp表的查询权限回收，则xaiohong的权限也被一起回收。

6、profile 是口令限制、资源限制的集合，当建立数据库时，oracle则会自动建立名称为default的profile

a、账户锁定，指定账户登录最多可输入的密码次数

例：指定tea这个用户最多能尝试3次登录，锁定时间为2天

sql>create profile lock_account limit failed_login_attemps 3 password_lock_time 2；

sql>alter user tea profile lock_account；

b、给用户解锁

sql>alter user tea account unlock；

c、终止口令，让用户定期修改密码

例：给用户tea创建一个profile文件，要求该用户每隔10天要修改自家的登录密码，宽限期为2天；

sql>create profile myprofile limit password_life_time 10 password_grace_time 2；

sql>alter user tea profile myprofile；

d、口令历史，修改密码时不能使用以前的密码

例：sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10；

password_reuse_time指定口令可重用时间即10天后就可以重用

e、删除profile

sql>drop profile password_history【cascade】

三、oracle表的管理

1、表明和列的命令规则

必须以字母开头、长度不能超过30字符、不能使用oracle的保留字，只能使用如下字符 A-Z，a-z，0-9，￥，#等

2、支持的数据类型

a、char   定长  最大2000字符；    sql>create table users (username char(10)); 10字符都被占用，适合定长的，比如身份证号，查询速度极快；

b、varchar2     变长   最大4000字符；    sql>create table users(username vchar(10)); 若不足10位，则只分配几位就可以，节省空间；

c、clob   字符型大对象   最大4G

d、number  范围-10的-38次方到10的38次方

   number(5,2)，表示一个小数有5位有效数，2位小数

   number(5)，表示一个五位整数

e、date  日期类型

f、blob  图片   二进制数据，可存放图片/声音   4G

3、建表

学生表

sql>create table student(xh number(4), xm varchar2(20), sex char(2), birthday date, sal number(7,2));

班级表

sql>create table classes(classid number(2),cname varchar(20));

a、添加一个字段

sql>alter table student add(classid number(2));

sql>desc student;    查看表结构

b、修改字段的长度

sql>alter table student modify(xm varchar2(30));

c、修改字段的类型或名字，如果有数据尽量不动

sql>alter table student modify(xm char(30));

d、删除字段，最好不要动

sql>alter table student drop column sal；

e、修改表的名字

sql>rename student to stu；

f、删除表

sql>drop table student；

g、对标添加数据

sql>insert into student values(‘A001’,’张三’,’男’,’01-5月-05’,100);   说明：oracle中默认日期格式’DD-MON-YY’

改日期的默认格式

sql>alter session set nls_date_format = ‘yyyy-mm-dd’;

插入部分字段

sql>insert into student (xh,xm,sex) values(‘A003’,’JOHN’,’女’);

插入空值

sql>insert into student (xh,xm,sex,birthday) values(‘A003’,’JOHN’,’女’,null);

sql>select * from student where birthday is null；   查询生日为空的人员

h、改字段

sql>update student set sex=’女’ where xh=’A001’;

sql>update student set sal=sal/2,classid=3 where sex=’男’；

如果为null的话

sql>update student set sal=sal/2 where sex is null；

i、删除数据

sql>delete from student；   删除所有记录，表结构还在

sql>drop table student；    删除表的结构和数据

sql>delete from student where xh=’A001’；    删除一条记录

sql>truncate table student；   删除表中的所以记录，表结构还在，无法找回删除的记录，速度快

4、表的查询

a、查看表结构：sql>desc dept；

b、查询所有列：sql>select * from dept；

c、查询指定列：select ename,sal,job from emp;

d、如何取消重复行：sql>select distinct depton,job from emp;

set timing on查看查询所有时间

insert into users(userid,username,userpss)  select * from users;  快速复制

count(*); 查看有多少条记录

例1：查询SMITH的薪水、工作、所在部门

sql>select ename,sal,jib,depton from emp where ename=’SMITH’；

sql>select ename,sal*13 +nvl(comm,0)*13 “年工资” from emp;

sql>select ename|| ‘ is a ‘ || job from emp;

nvl(comm,0)，若comm为空值，用0代替，若不为空，则为实际值

例二：查询1982.1.1后入职的员工

sql>select ename,hiredate from emp where hiredate>’1-1月-1982’;

like操作符：%表示任意0到多个字符，_表示任意单个字符

例三：显示首字符为S的员工姓名和工资

sql>select ename,sal from emp where ename like ‘S%’;

显示第三个字符为大写O的所有员工姓名和工资

sql>select ename，sal from emp where ename like’__O%’;

例四：如何显示empno为123,345,800.的雇员情况

sql>select * from emp where empno in(123,345,800);

order by按字段从低到高排序

例五：按照工资排序

sql>select * from emp order by sal;

sql>select * from emp order by sal desc；

按照部门升号而雇员工资降序

sql>select * from emp order by deptno asc , sal desc;

使用列的别名排序

sql>select ename, sal+nvl(comm,0)*12 “年薪” from emp order by “年薪” asc；

数据分组max，min，avg，sum，count

例六：如何显示所有员工中最高工资和最低工资

sql>select ename, sal from emp where sal=(select max(sal) from emp);

显示工资高于平均工资的员工姓名，工作岗位

sql>select avg(sal);

sql>select ename,job,sal where sal>(select avg(sal) from emp);

group by:用于对查询的结果分组统计

having:用于限制分组显示结果

例七：显示每个部门的每种岗位的平均工资和最低工资

sql>select avg(sal),max(sal),deptno ,job from emp group by deptno ,job;

显示平均工资低于2000的部门号和它的平均工资

sql>select avg(sal),max(sal) ,deptno from emp group by deptno having avg(sal)>2000;

分组函数只能出现在选择列、having、order by子句中

如果在select语句中同时包含group by、having、order by，他们的顺序是group by、having、order by；

在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则会出错；

例八：多表查询

显示雇员名，雇员工资及所在的部门名字

sql>select a1.ename ,a1.sal ,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;

如何显示部门号为10的部门号，员工名和工资

sql>select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;

显示各个员工的姓名，工资及其工资的级别

sql>select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.high and a2.low;

显示雇员名，雇员工资及所在的部门名字，并按部门排序

sql>select a1.ename ,a2.dname ,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;

例九：自连接，在同一张表的连接查询

显示某个员工的上级领导的名字

sql>select worker.ename ,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=’FORD’;

例十：子查询，嵌入在其它sql语句中的select语句

显示与SMITH同一部门的所有员工

sql>select deptno from emp where ename=’SMITH’;

sql>select * from emp where deptno=( select deptno from emp where ename=’SMITH’);

如何查询和部门10的工作相同的雇员名字、岗位、工资、部门号

sql>select distinct job from emp where deptno=10;

sql>select * from emp where job in (select distinct job from emp where deptno=10);

如何显示工资比部门30的所有员工的工资高的员工姓名，工资和部门号；

sql>select ename, sal, dept from emp where sal>all(select sal from emp where deptno=30);

如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号

sql>select ename, sal, dept from emp where sal>any(select sal from emp where deptno=30);

如何查询与smith的部门和岗位完全相同的所有雇员

sql>select deptno,job from emp where ename=’SMITH’;

sql>select * from emp where (deptno,job)=( select deptno,job from emp where ename=’SMITH’);

如何显示高于自己部门平均工资的员工信息

sql>select deptno,avg(sal) mysal from emp group by deptno;

sql>select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,( select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;

4、oracle分页

sql>select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;

如果要指定查询列，只需修改最里层的即可

sql>select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal) a1 where rownum<=10) where rn>=6；

5、用查询结果创建新表

create table mytable (id,ename,sal) as select empno,ename,sal from emp;

6、合并查询 union(并集，取消重复行)，union all(与union相似，不会取消重复行)，intersect(交集)，minus(差集)

四、oracle中如何操作数据

1、使用to_date函数

sql>insert into emp values(9998,’’,’MANAGER’,7782,to_date(‘1982-12-12’,’yyyy-mm-dd’),78.9,55.33,10);

2、将一张表中的部分数据放在另外一张表中

sql>create table kkk(myid number(4),myname vanchar2(50),mydept number(5));

sql>insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10;

例一：希望员工scott的岗位、工资、补助与smith员工一样

sql>update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=’SMITH’) where ename=’SCOTT’;

五、sql函数使用

1、字符函数

a、lower(char)：将字符串转化为小写的格式；

b、upper(char)：将字符串转化为大写格式

c、length(char)：返回字符串的长度

d、substr(char,m,n)：取字符串的子串

例：显示正好为5个字符的员工的姓名

sql>select * from emp where length(ename)=5;  

显示所有员工姓名的前三个字符

sql>select substr(ename,1,3) from emp;        

以首字母大写的方式显示所有员工的姓名

sql>select upper(substr(ename,1,1)) from emp;    

sql>select lower(substr(ename,2,length(ename)-1)) from emp;

sql> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

e、replace(char1,search_string,replace_string)

f、instr(char1,char2,[,n[,m]])    取子串在字符串中的位置

例：显示员工姓名，用我是A替换A

select replace(ename,’ A’,’我是A’) from emp;

2、数学函数

a、round(n,[m])： 四舍五入

b、trunc(n,[m])   截取数值

c 、mod(m,n)    取余数

d、floor(n)  返回小于或等于n的最大整数

e、ceil(n)   返回大于或等于n的最小整数

例：显示在一个月为30天的情况所有员工的日薪金，忽略余数

sql>select trunk(sal/30),ename from emp;

3、日期函数

a、sysdate：该函数返回系统时间    select sysdate from dual；

b、add_months(d,n)     时间点d再加上n个月

c、last_day(n)  返回指定日期所在月份的最后一天；

sql>select * from emp where sysdate>add_months(hiredate,8);8个月前入职员工

sql>select trunk(sysdate-hiredate) “入职天数” , ename from emp;   对于每个员工，显示其加入公司的天数；

sql>select hiredate,ename from emp where last_day(diredate)-2=hiredate;

六、维护数据的完整性

1、约束：用于确保数据库数据满足特定的商业规则

a、not null  非空

b、unique   唯一

c、primary key   主键

d、foreign key   外键 

e、check  指约束表中某一个或者某些列中可接受的数据值或者数据格式

例：商店表的设计案例

商品goods（商品号goodsid，商品名goodsname，单价unitprice，商品类别category，供应商provider）；

客户customer（客户号customerid，姓名name，地址address，电邮email，性别sex，身份证cardid）；

购买purchase（客户号customerid，商品号goodsid，购买数量nums）；

建表，1、每个标的主外键，2、客户的姓名不能为空，3、单价必须大于0，购买数量必须在1-30之间，4、电邮不能够重复，5、客户的性别必须是男或者女，默认是男

sql>create table goods(goodsid char(8) primary key , goodsname varchar2(30) , unitprice number(7,2) check(unitprice > 0) , category varchar2(8) , provider varchar2(30));

sql>create table customer(customerid char(8) primary key, name varchar2(50) not null, address varchar2(50), emailvarchar2(50) unique, sex char(2) default ‘男’ check (sex in(‘男’,’女’))，cardid char(18));

sql>create table purchase(customerid char(8) references customer(customerid),goodsid char(8) references goods(goodsid),nums number(10) check (nums between 1 and 30));

如果建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束

例：

sql>alter table goods modify goodsname not null；修改商品名不能为空

sql>alter table customer add constraint cardunique unique(card);   增加身份证不能重复

sql>alter table customer add constraint addresscheck check(address in (‘hd’,’dc’,’xc’));增加客户住址只能是上面3个

2、删除约束

alter table 表名 drop constraint 约束名称；

说明：在删除主键约束的时候，可能有错误，因为两张表存在主从关系时，在删除主键约束时，必须带上cascade选项

alter table 表名 drop primary key cascade；

七、pl/sql编程

pl/sql是oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入sql语言，还可以定义变量和常量。

1、创建一个存储过程，该过程可以向某表中添加记录

create table mytest(name varchar2(30),passwd varchar2(30));

create procedure sp_pro1 is

begin

insert into mytest values(‘lifurong’,’m123’);

end;

/-----加/执行

exec sp_pro1

如果以前存在该过程名，则可以替换create or replace procedure sp_pro1 is

如何查看错误信息：show error

如何调用该过程

exec 过程名（参数值1，参数值2。。。）；

call 过程名（参数值1，参数值2。。。）

2、删除某个用户

create or replace procedure sp_pro2 is

begin

delete from mytest where name=’lifurong’;

end;

3、块编程：过程、函数、触发器、包

编写规范：

a、注释

单行注释  select * from emp where empno=7788； ---取得员工信息

多行注释  /*….*/来划分

b、标识符号的命名规范

定义变量时，建议用v_作为前缀v_sal

定义常量时，建议用c_作为前缀c_rate

定义游标时，建议用_cursor作为后缀，emp_cursor

当定义例外时，建议用e_作为前缀e_error

 

增加知识点：

1、rank()/dense_rank() over(partition by e.deptno order by e.sal desc)语法。
over: 在什么条件之上。
partition by e.deptno: 按部门编号划分；
order by e.sal desc: 按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by否则非法）
rank()/dense_rank(): 分级
整个语句的意思就是：在按部门划分的基础上，按工资从高到低对雇员进行分级，“级别”由从小到大的数字表示（最小值一定为1）。

那么rank()和dense_rank()有什么区别呢？
rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。
dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。

 2、join方式

inner join、left join、right join，通过on条件语句将连个表进行拼接

on条件语句最好用=号对两个表的主外键进行连接，且on条件语句不能省略；

a、inner join

目的：将两表中符合on条件的所有记录都找出来

典型应用：将存在多关系的引用表放在左表，将存在一关系的被引用表放在右表，通过=号将主外键进行连接，通过右表设定过滤条件，选出相应的且主键唯一的左表记录

b、left join

目的：将左表所有记录列出，右表中只要符合on条件的，与左表记录相拼合，不符合条件的，填以null值

典型应用：将存在多关系的应用放在左表，将存在一关系的被引用表放在右表，通过对右表设定过滤条件，选出相应且主键唯一的左表记录

c、right join

目的：将右表的所有记录列出，左表中只要符合on条件的，与右表记录相拼接，不符合条件的，填以null值

典型应用：可转化成left join。

3、to_char 是把日期或数字转换为字符串

4、“exists”和“in”的效率问题

1) select * from T1 where exists(select * from T2 where T1.a=T2.a) ;

    T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

2) select * from T1 where T1.a in (select T2.a from T2) ;

     T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。

exists 用法：

1）句中的“select * from T2 where T1.a=T2.a” 相当于一个关联表查询，

     相当于“select * from T1,T2  where T1.a=T2.a”；

    “exists（xxx）”它只在乎括号里的数据能不能查找出来，是否存在这样的记录，如果存在，这1）句的where 条件成立。

in的用法：

2）句中的“select * from T1 where T1.a in (select T2.a from T2) ”，

这里的“in”后面括号里的语句搜索出来的字段的内容一定要相对应，一般来说，T1和T2这两个表的a字段表达的意义应该是一样的，否则这样查没什么意义。