oracle入门

一、安装

二、启动服务：

a)OracleServiceORCL

b)OracleOraDb11g_home2TNSListener

三、用户管理

a)sys: 超级管理员，权限最大

b)system: 管理员

c)scott: 实例账户，密码：tiger

d)登录用户：

i.conn system/Manager1 as sysdba

e)

f)分配权限：grant权限名to 用户名;

g)权限：connect, resource, dba

h)撤销权限：revoke权限名from 用户名;

i)修饰用户密码：alter user 用户名identified by 新密码;

j)锁定用户：alter user 用户名account lock;

k)解锁用户：alter user 用户名account unlock;

l)删除用户：drop user 用户名[cascade];

四、表空间

a)

b)

五、灾难处理

a)使用sysdba身份强行登录

i.conn system/Manager1 as sysdba;

b)排错，强行启动数据库

i.alter database open;

c)离线删除

i.alter database datafile 'f:\etc.mdf' offline drop;

d)重启启动数据库

i.alter database open;

e)删除表空间

i.drop tablespace etc_space;

六、数据类型

a)字符型：能够存储字符

i.char: 固定长度，1-2000字节

ii.varchar2(长度) ：可变长度1-4000字节

iii.long: 可变长度2g

b)数值型：不区分小数和整数

i.number(总长度，小数位)

1.number(4,1)-999.9 – 999.9

2.number(5,2) -999.99 999.99

c)日期型：能够存储日期时间

i.data: 精确到秒

ii.timestamp: 精确到秒后六位

d)二进制：

i.RAW: 2000字节

ii.Long RAW: 2g

e)大对象：

i.Lob(large object) : 4g

ii.Clob: 文本大对象4g

iii.Blob: 二进制文件，4g

iv.Bfile: 外部文件大对象，外部文件的引用指针无限大

i.drop table 表名;

八、数据管理

a)添加：

i.insert into 表名values(值必须和表中的每个字段匹配)

ii.insert into 表名(字段名…) values(值…)

b)删除：

i.delete from 表名where 条件

c)修改：

i.update 表名set 字段名=值, 字段名=值…. Where 条件

d)查询：

i.select * from 表名;

ii.select 字段名1, 字段名2,…字段名n from 表名where 条件

e)查询是否为空：is null / is not null

f)模糊查询：like _一个字符%任意个字符

g)in: 是否与目标集合相等not in: 与in相反

h)between…and 在…之间

i.select ename,sal from emp where sal between 2000 and 4000

--查询emp表中所有的姓名和工资

select ename,sal from emp;

--查询佣金为空的员工姓名及工资

select ename,sal,comm from emp where comm is not null;

--查询姓名以字母'S'开头的员工(模糊查询)

SELECT*from emp where ename like 'S%'

--查询姓名第二个字母'O'的员工(模糊查询)

SELECT*from emp where ename like '_O%'

--查询姓名最后一个字母'T'的员工(模糊查询)

SELECT*from emp where ename like '%T'

--查询姓名有5个字符，最后一个字母'T'的员工(模糊查询)

SELECT*from emp where ename like '____T'

select*from emp where sal like '%3%'

select*from emp where sal like '3___'

--查询工资大于3000的所有员工信息

select*from emp where sal>3000

--查询所有的经理和办事的详细资料

select*from emp where job='MANAGER' or job='CLERK'

select*from emp where job in('MANAGER' , 'CLERK')

--查询工资是800，1500，5000的员工姓名，工资，

select ename,sal from emp where sal=800 or sal=1500 or sal=5000

select ename,sal from emp where sal not in(800,1500,3000,2250,1680,5000) --按参加工作时间从早到晚显示员工信息

select*from emp order by hiredate

--查询工资在2000--4000之间的员工姓名及工资

select ename,sal from emp where sal>=2000 and sal<=4000

select ename,sal from emp where sal between 2000 and 4000

第二天：

1.序列：

a)

b)

1 选择部门30中的所有员工

select*from emp where deptno=30;

2 列出所有办事员(CLERK)的姓名、编号和部门编号

select ename,empno,deptno from emp where job='CLERK'

3 找出佣金(COMM)高于薪金(SAL)的员工

select*from emp where comm>sal;

4 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)，

既不是经理又不是办事员但其薪金(SAL)大于或等于2000的所有员工的详细资料select*from emp where(deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK')or(job!='MANAGER' and job<>'CLERK' and sal>=2000)

5 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料select*from emp where deptno=10 and job='MANAGER' or deptno=20 and

job='CLERK'

6 找出不收取佣金或收取佣金低于100的员工

select*from emp where comm is null or comm<100

7 列出薪金比“SMITH”多的所有员工信息

select*from emp where sal>(select sal from emp where ename='SMITH')

8 列出与“SCOTT”从事相同工作的所有员工

select*from emp where job=(select job from emp where ename='SCOTT')

9 列出薪金高于部门30 中所有员工的平均薪金的所有员工的姓名和薪金

select ename,sal from emp where sal>(select avg(sal)from emp where deptno=30) 10 显示部门20的员工人数

select count(empno)from emp where deptno=20;

11 列出所有员工的年工资，按照年薪从低到高排序

select sal*12 s from emp order by s;

12 查询工资高于公司平均工资的所有员工

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

1.约束

a)主键：唯一，非空，primary key

b)非空：not null

c)检查：check

d)默认值：在没有添加数据时，将自动添加默认值default

e)唯一：unique

2.函数的分类：

a)单值函数：有且只有一个返回值

i.字符函数：处理字符串

ii.数学函数：处理数值

iii.日期函数：处理日期时间

iv.转换函数：类型的转换

v.混合函数：特定功能

3.分组函数：类似聚合函数sum, avg, max, min count

i.问题1：显示部门30中所有员工的工资总和

1.Select sum(sal) from emp where deptno=30;

ii.问题2：显示部门30中的工资平均

1.Select avg(sal) from emp where deptno=30;

iii.问题3：显示工资高于3000的员工个数

1.Select count(empno) from emp where sal>3000;

iv.问题4：显示工资最低的员工信息

1.Select * from emp where sal=(select min(sal) from emp);

4.字符函数

i.问题1：查询员工姓名和工作，中间使用”-”连接起来

1.函数：concat(字符串1，字符串2)

a)select concat(concat(ename,'-'),job)from emp;

2.扩展：使用字符串连接符“||”

a)select ename||'-'||job from emp;

ii.问题2：截取员工姓名的前3个字符和字符4及以后的内容显示

1.函数：字符串截取函数：substr(待处理的字符串,截取的起始位置,截几个)

2.注意：Oracle中的字符串下标从1开始

a)select substr(ename,1,3),substr(ename,4)from emp;

iii.问题4：显示员工姓名包含字母’T’的员工信息

1.函数：字符定位函数instr(待处理的字符串,要查询的字符,起始位置,第n次出现)

2.返回值：字母所在的位置，如果没有返回0

a)select ename from emp where instr(ename,'T',1,1)<>0;

3.扩展：查询字母’T’在员工姓名中第一次和第二次出现的位置

a)select ename,instr(ename,'T',1,1), instr(ename,'T',1,2)from emp;

iv.问题6：将用户姓名小写显示

1.函数：字符串小写转换lower(字符串) / upper

a)select lower(ename),upper(ename)from emp;

v.问题7: 将工作种类首字母大写显示

1.函数：首字母大写函数initcap(字符串)

a)select initcap(job)from emp;

vi.问题8：将用户姓名中的’T’替换成’O’显示

1.函数：替换函数：replace(待处理的字符串,要查找的字符串,要替换的字符串)

a)select ename,replace(ename,'T','O')from emp;

vii.显示姓名只有5个字母的员工信息

1.长度函数：length(字符串)

a)返回值：该字符串的长度

b)select ename from emp where length(ename)=5;

5.日期、时间函数

a)问题1：查询当前系统时间

i.时间函数：sysdate

ii.select sysdate from dual;

iii.dual:是一张无意义的表，专门用于函数的测试

b)查询12年前参加工作的员工

i.函数：月份差函数months_between(时间1，时间2)

ii.select ename,hiredate from emp where months_between(sysdate,hiredate)>=144;

c)查询在当月倒数第三天入职的员工信息

i.函数：last_day(时间)

1.select ename,hiredate from emp where

last_day(hiredate)-2=hiredate;

d)查询每个员工的工作天数

i.规则：Oracle中的时间相减得到的是两个时间的天数差

1.select ename,sysdate-hiredate||'天' from emp;

e)月份添加

i.add_months(待处理的时间，添加月份的数量)

1.select hiredate, add_months(hiredate,3)from emp;

6.转换函数

a)

1.insert into test_date values('22-12月-2012');

ii.解决方案2：使用to_date(要添加的日期,格式);

iii.insert into test_date values(to_date('2013-03-12 13:42:11','yyyy-mm-dd hh24:mi:ss'));

iv.问题2：按照年月日、时分秒、星期几等信息显示系统时间

v.to_char(时间，格式)

vi.select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day d ddd') from dual;

7.混合函数：

a)替空函数：nvl(字段名称,替换的值)

b)select sal+nvl(comm,0)from emp;

8.数值函数

a)问题1：按每月30计算员工的每日工资，要求结果四舍五入到小数点后2位

b)函数：round(数值，数度)

i.select sal/30,round(sal/30,2)from emp;

c)问题2：计算每个员工已经工作了多少个月，要求忽略小数部分

i.取整：trunc(时间);

ii.select ename,trunc(months_between(sysdate,hiredate))from emp;

d)ceil(数值) 向上取，得到一个整数

e)floor(数值) 向下取，得到一个整数

f)power(m,n) : m的n次方select power(2,3) 8

g)abs(数值) 绝对值

9.

一、多表查询

a)等连接

i.问题：显示员工的姓名，及其所在部门的名称

ii.分析：ename—emp dname – dept

iii.关系：emp(deptno) dept(deptno)

iv.select e.ename, d.dname from emp e,dept d where e.deptno=d.deptno;

b)外连接

i.问题：查询所有部门名称和对应的员工姓名，若该部门没有员工，则只

显示部门名称

ii.分析：部门名称dept-dname 员工姓名emp-ename

iii.select d.deptno,d.dname,e.ename from emp e,dept d where

e.deptno(+)=d.deptno;

iv.注意：Oracle中使用(+) 表示外连接，可以理解(+)所在字段的对侧为主要显示信息,对侧表示以=分开左右两部分

c)自连接

i.问题：显示员工姓名及其上级员工姓名

ii.分析：员工姓名emp-ename 上级姓名emp-ename

iii.select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+);

d)子查询

i.问题：查询工资高于公司平均工资的所有员工信息

1.select ename,sal from emp where sal>(select avg(sal)from emp);

二、集合，用于查询

a)并集：

i.

b)交集：

i.

c)差集：

i.

三、

a)用户：user

i.在建立其他对象之前，首先创建的是用户对象，它是其他对象的宿主，

建立、删除和维护用户

ii.

iii.

1.alter user 用户名identified by 新密码;

iv.锁定用户，需要管理员执行

1.Alter user 用户名account lock;

v.解锁用户，需要管理员执行

1.Alter user 用户名account unlock;

vi.授权，需要管理员执行connect, resource, dba

1.grant 权限名1,权限名2…. To 用户名;

vii.撤销权限，需要管理员执行

1.Revoke 权限名from 用户名;

viii.删除用户，需要管理员执行

1.Drop user 用户名[cascade];

b)表：table

i.

ii.查看表结构

1.desc 表名;

iii.添加新字段，address, telephone

iv.语法：alter table 表名add(字段名称类型)

1.alter table student add(address varchar2(20));

2.alter table student add(telepnone varchar2(20));

v.删除address字段

vi.语法：alter table 表名drop column 字段名;

1.alter table student drop column address;

vii.修改字段telepnone的类型为8位数字

1.注意：在修改字段类型之前需要原字段的内容清空

2.语法：alter table 列名modify(字段名,新类型)

3.alter table student modify(telepnone number(8))

viii.给表改名字

1.语法：rename 源名to 新名;

2.Rename student to stu;

ix.删除表

1.drop table 表名;

c)

第三天：

回顾：

1.分组函数：sum, avg, max, min, count

2.字符函数：

a)concat(字符串1，字符串2) ||

b)substr(字符串, 起始位置,截取的个数) 下标从1开始

c)instr(字符串，要查找的字符串，起始位置，第几次出现)

d)lower() / upper / initcap

e)replace(字符串,需要修改的字符串，修改成什么)

f)length(字符串)

3.日期函数：

a)sysdate 系统时间

b)months_between(时间1，时间2) 月份差

c)last_day(时间)

d)add_months(时间，添加月份)

4.数值函数

a)round四舍五入/ trunc整数截取/ ceil 向上取ceil(12.1) →13

/floor 向下取floor(12.9) →12

5.转换函数

a)insert into t1 values(’23-10月-2011’);

b)values(to_date(‘2011-12-22’,’yyyy-mm-dd’))

c)select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) from dual;

6.替空函数

a)nvl(字段名，替换的值)

b)select ename, nvl(comm,0) from emp;

7.多表查询

a)等连接

b)外连接

c)自连接

i.select e1.ename,e2.ename from emp e1, emp e2 where

e1.mgr=e2.empno(+)

d)子查询

8.集合

a)并集(union/union all)

i.union会消除查询结果中重复的记录，union all 不会消除重复

b)交集

c)差集

数据库对象

1.约束

a)作用：保证数据的完整性、正确性，减少数据的冗余

b)两种方式：

i.

ii.

1.添加主键约束

2.

3.

4.

5.

6.alter table 表名add constraint 约束名称check(约束条件)

a)alter table books add constraint CK_p check(price>=0);

7.添加唯一约束

8.alter table 表名add unique(字段名称);

a)alter table books add unique(bname);

9.添加外键约束

10.alter table 主表add constraint 约束名称foreign key(外键字段名)

references 主键表(主键字段名)

a)alter table books add constraint FK_kid foreign key(kid)

references kinds(kid);

11.删除约束

a)alter table student drop constraint FK_kid;

2.序列

a)作用：实现某字段的，一般是主键的自增长

b)语法：

i.create sequence 对象名; 表示该字段从1开始每次增长1

ii.create sequence 对象名start with 起始值increment by 增长量;

iii.例如：合建一个序列对象，从10开始，每次增长10

1.Create sequence seq_dno start with 10 increment by 10;

c)如何访问序列中的值

i.对象名.nextval

ii.对象名.currval Current Value

d)注意：多张表可以共享同一个序列对象，建议一张表使用一个序列

e)删除序列：drop sequence 序列名;

3.视图：

a)是一张虚拟的表，在数据库中本身不存在的数据表，只存在于内存中

b)作用：

i.简化SQL的查询语句

ii.提高数据库查询的安全性

c)语法：create or replace view 视图名称as 复杂的SQL语句

d)注意：用户必须拥有足够的权限来创建视图

i.select * from emp; //不安全

ii.select * from v_1;

e)删除视图：drop view 视图名;

4.同义词，需要足够的权限

a)作用：通过给表起别名的机制，能够提高查询的效率及安全性

b)语法：

i.create synonym 别名for 原表名;

ii.

c)

i.drop [public] synonym 别名;

5.索引

a)作用：提高查询速度

b)语法：create index 索引名on 表名(字段名);

c)规则：

i.只要涉及到了添加索引的字段就有可能被提速

ii.Oracle为每张数据表的主键自动添加了一个索引

iii.不带where条件的SQL语句不会被提速

iv.有where条件中有函数的字段不被提速

v.在where条件中有参与运算的字段不提速

vi.尽量少用in, not in, null, is null, like 等关键字

vii.查询语句，优先考虑子查询，其次考虑集合查询

viii.where条件中限制性强的条件要写在where子句的末尾

ix.多表查询时，返回最少行的表名及条件写在最后。

1.Select 表1, 表2, where 条件1 and 条件2;

x.对于联合索引，当where子句中有任何一个字段都提高查询效率

6.PL/SQL

a)PL: Procedure Language 过程语言，拥有大量的结构化语句，if ,for

b)SQL:Struct Query Language 结构化查询语言，用于执行CRUD操作

insert,select,update,delete

c)

d)

i.变量必须先定义后使用，标识符不区分大小写

ii.数据类型：

iii.第一种声明方式：

1.使用数据库中的字段类型来定义程序的变量

2.

iv.第二种声明方式：

1.使用属性来定义类型，将表中字段的类型引用过来

2.

v.

1.使用行属性引用类型，使用该类型可以存储数据表中的一行数据

2.

vi.

1.第一种方式，可以在变量声明的同时赋初始值

a)eno number(4):=10;

2.

3.

4.

a)例如：将emp表中的编号为7369的员工姓名存储到变量en中，

5.

a)dbms_output.put_line(输出的内容); //带换行的输出

b)dbms_output.put(输出的内容); //不带换行

c)Oracle默认情况下是关闭输出显示的，set serveroutput on;

d)

6.For

a)

b)

c)

d)

e)

f)

第四天：

1.

2.

3.

4.异常处理

a)使用when…then 方式，可以处理特定异常，和通用异常

i.

b)

i.When others then

c)自定义异常

i.

5.游标

a)隐式游标：Oracle有且仅有一个隐式游标，用于接收并处理数据，用户无

法控制。

b)显式游标

i.

ii.

iii.

iv.

v.

declare

--声明一个动态游标类型

type cur is ref cursor;

--声明一个动态游标

mycur cur;

--声明两个行级变量

erow emp%rowtype;

drow dept%rowtype;

begin

open mycur for select*from emp;

loop

fetch mycur into erow;

exit when mycur%notfound;

dbms_output.put_line(erow.ename);

end loop;

dbms_output.put_line('------------------------------------------');

close mycur;

--读取dept表的内容

open mycur for select*from dept;

loop

fetch mycur into drow;

exit when mycur%notfound;

dbms_output.put_line(drow.dname);

end loop;

--关闭游标

close mycur;

end;

6.存储过程

a)

b)

1.exec 存储过程名(参数列表);

c)

1.案例：编写存储过程，用于接收用户输入的员工编号，显示员工的

ii.

iii.

7.

a)

b)

8.

a)