Oracle数据库学习笔记.doc

Oracle学习笔记
▲oracle中忘记密码操作如下：
1.select username,password from dba_users;
2.connect sys/oracle as sysdba;
3.alter user system identified by manager;(将system用户的密码修改为manager)
4.修改之后用system用户登录数据库
connect system/manager
select * from tab where tabtype='TABLE'; 察看当前用户下的表
▲oracle中一些常用的操做：
1.创建用户：
create user jerry identified by tom account unlock;
2.给用户jerry授予connect权限
grant connect to jerry;
3.修改用户处于锁定或未锁定状态
alter user jerry account lock|unlock;
4.修改密码：password 用户名；
如果给自己修改密码，则可以不带用户名，如果给别人修改密码，则需要带用户名。
▲交互命令
1.&:可以代替变量，而该变量在执行时，需要用户输入。
select * from 表名 where job='&job';
2.edit：该命令可以编辑指定的sql脚本。
edit d:\a.sql
3.spool:该命令可以将sql*plus屏幕上的内容输出到指定文件中去。
spool on(打开)
spool d:\b.sql并输入spool off

linesize:用于控制每行显示多少个字符，默认80个字符。
set linesize 120;
pagesize:设置每页显示的行数目，默认是14，用法和linesize一样。至于其它环境参数的使用也是大同小异。
▲oracle用户管理
1.oracle要求用户密码不能以数字打头。
2.什么是表空间？表存在的空间，一个表空间是指向具体的数据文件。
3.创建用户的细节方法：
create user shunping identified by m123 default tablespace users temporary tablespace temp quota 3m on users;
identified by 表明该用户shunping将用数sele据库方式验证default tablespace users //用户的表空间在user上。
temporary tablespace temp //用户shunping的临时表健在temp空间。
quota 3m on users //表明用户shunping建立的数据对象（表、索引、视图、pl/sql块）最大只能是3m。
刚刚创建的用户是没有任何权限的，因此，需要dba给该用户授权，grant connect to shunping;如果你希望该用户建表没有空间的限制，grant resource to shunping;如果你希望这用户成为dba,grant dba to shunping;

grant create session to shunping;
oracle管理用户的机制（原理）是什么？
▲
使用system登录，然后回收角色：
revoke connect from shunping;
revoke resource from shunping;
删除用户：
drop user 用户名 [cascade]
当我们删除一个用户的时候，如果这个用户自己已经创建过数据对象，那么我们在删除用户的时候，需要加选项cascade，表示把这个用户删除同时，把该用户创建的数据对象一并删除。
注意：[]表示里面的内容可以要也可以不要，例如删除用户shunping：drop user shunping cascade;
当一个用户，创建好后，如果该用户创建了任意一个数据对象，这时，我们的dbms就会创建一个对应的方案与该用户对应，并且该方案的名字和用户名一致。
方案（schema）：当一个用户创建好后，如果该用户创建了任意一个数据对象，这时，我们的dbms就会创建一个对应的方案与该用户对应，并且方案的名字和用户名一致。
如果希望看到某个用户的方案究竟有什么数据对象，我们可以用pl/sql developer。
让shunping用户可以去查询scott的emp表：
步骤1.先用scott登录 conn scott/tiger;
步骤2.赋权限，grant select[update|delete| insert| all] on emp to shunping;
select * from scott.emp;
使用tea更新/删除/插入 scott的emp表。
conn tea/tea;
update scott.emp set job='Teacher'where job='&job';
delete from scott.emp where job='&job';
insert into scott.emp vaoues(8888,'ford','Teache',7698,'08-9月 -81'，1500,300,20)
想办法让tea把自己拥有的对scott.emp的权限转给stu;
conn scott/tiger;
grant all on scott.emp to tea with grant option;
conn tea/tea;
grant select on scott.emp to stu;
使用stu查询scott用户的emp表
conn stu/stu;
select * from scott.emp;
使用tea收回给 stu的权限
revoke select on scott.emp from stu;
with grant option表示得到权限的用户，可以把权限继续分配。
with admin option如果是系统权限，则带with admin option;
▲使用profile管理用户口令
概述：profile是口令限制，资源限制的命令集合，当建立数据时，oracle会自动建立名称为default的profile，当建立用户没有指定profile选项，那oracle就会将default分配给用户。
（1）账户锁定：概述：指定该账户（用户）登录时最多可以输入密码的次数，也可以指定用户锁定的时间（天）一般用dba的身份去执行该命令。
例子：指定scott这个用户最多只能尝试三次登录，锁定时间为2天。
创建profile文件
sql>create profile lock_account limit failed_ogin_attempts 3 password_lock_time 2;
sql>alter user tea profile lock_account;
给账户（用户）解锁
sql>alter user 用户名 account unlock;
终止口令：为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作。
例子：给前面创建的用户tea创建一个profile文件，要求该用户每隔10天要修改自家的登录密码，宽限期为2天。
sql>create porfile myprofile(文件名) limit password life time 10 password_grace_time 2;
sql>alter user tea（用户名） profile myprofile（文件名）;
解锁：alter user 用户名 account unlock;
▲口令历史
概述:如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样oracle就会将口令修改的信息存放到数据字典中，当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时
就提示用户重新输入密码。例子：
1）建立profile
sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 1;
password_resuse_time(密码宽限期)//指定口令可重用时间，即10天后就需要修改。
2）分配给某个用户
sql>alter user tea profile myporfile;
▲删除profile
概述：当不需要某个profile文件时，可以删除该文件。
sql>drop profile profile文件名
▲oracle数据库启动流程(cmd命令中启动)
oracle也可以通过命令行的方式启动，我们看看具体是怎样操作。
A.oracle启动流程-windows下
1.lsnrctl start (启动监听)
2.oradim -startup -sid 数据库实例名
B.oracle启动流程-linux下
1.lsnctl start (启动监听)
2.sqlplus sys/change_on_install as sysdba(以sysdba身份登录，在oracle10g后可以这样写）
sqlplus /nolog
conn sys/change_on_install as sysdba
3.startup
附加：systeminfo 查看windows系统的详细信息
▲oracle登录认证方式
1.oracle登录认证方式-windows下
1）操作系统认证：如果当前用户属于本地操作系统的ora_dba组（对于windows操作系统而言），即可通过操作系统认证。
2）oracle数据库验证（密码文件验证）。
对于普通用户，oracle默认使用数据库验证。
对于特权用户（ 户），oracle默认使用操作系统认证，如果验证不通过，再到数据库验证（密码文件验证）。通过配置sqlnet.ora文件，可以修改oracle登录认证方式。
SQLNET AUTHENTICATION_SEVICES=(NTS)是基于操作系统验证；
SQLNET AUTHENTICATION_SERVICES=(NONE)是基于Oracle验证；
SQLNET AUTHENTICATION_SERVICES=(NONE,NTS)是二者共存。
▲一些表的操作
1.添加字段（学生表中添加学生所在班级classid):alter table student add(classid number(2));
2.修改字段的长度：alter table student modify(xm varchar2(12));
3.修改字段的类型（不能有记录的）：alter table student modify (xh varchar2(5));
4.删除一个字段：alter table student drop column xh;
5.删除表：drop table student;
6.表的名字修改：rename student to stu;
7.字段如何修改名字：--先删除 alter table studnet drop column sal;--再添加 alter table student add(salary number(7,2));
▲ORACLE中默认的日期格式'DD-MON-YY' dd 日子(天) mon 月份 yy 2位的年 '09-6月-99' 1999年6月9号
1.修改日期的默认格式:alter session set nls_date_format='yyyy-mm-dd';
2.恢复oracle的默认格式：alter session set nls_date_format='dd-mon-yy';
3.查看日期的格式：set linesize 1000;
select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
4.永久设置日期格式：改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd;
▲删除delete
1.删除所有记录，表结构还在，写日志可以恢复的，速度慢
2.删除表的结构和数据：drop table student;
3.删除一条记录：delete from student where xh='A001';
4.删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快：truncate table student;
▲查询 select
select xh,xm,sex from student;
select * from student where xh like 'A%1'; %任意多个字符
select * from student where xh like 'A__1'; _1个字符
select * from student where xh like '%A%';
select * from student where xh like 'A%';
select * from student where xh like '%A';
select * from student where xh = 'A%';
select * from student order by birthday ; 升序 (order by birthday asc;)
select * from student order by birthday desc; --降序
select * from student order by birthday desc,xh asc; --按birthday 降序 按xh升(asc/默认)
select * from student where sex='女' or birthday='1999-02-01';
select * from student where sex='女' and birthday='1999-02-01';
select * from student where salary > 20 and xh <> 'B002'; (!=)
▲ORALCE的函数
单行函数
字符函数
concat 连接 ||
<1>显示dname和loc中间用-分隔
select deptno,dname||'----'||loc from dept;

dual哑元表 没有表需要查询的时候 可以用它
select 'Hello World' from dual;
select 1+1 from dual;
查询系统时间
select sysdate from dual;
<2> initcap 首字母大写
select ename,initcap(ename) from emp;
<3> lower 转换为小写字符
select ename,lower(ename) from emp;
<4> upper 转换为大写
update dept set loc=lower(loc);
update dept set loc=upper(loc);
<5> LPAD 左填充
select deptno,lpad(dname,10,' '),loc from dept;
<6> RPAD 右填充
<7> LTRIM 去除左边的空格
RTRIM 去除右边的空格
ALLTRIM 去除两边的空格
<8>replace 替换
translate 转换
select ename,replace(ename,'S','s') from emp;
用's'去替换ename中的'S'
select ename,translate(ename,'S','a') from emp;
<9> ASCII 求ASC码
chr asc码变字符
select ascii('A') from dual;
select chr(97) from dual;
select 'Hello'||chr(9)||'World' from dual;
'\t' ascii码是 9
'\n' ascii码是 10

select 'Hello'||'\t'||'World' from dual;

<10> substr 字符截取函数
select ename,substr(ename,1,3) from emp;
从第1个位置开始 显示3个字符
select ename,substr(ename,4) from emp;
从第4个位置开始显示后面所有的字符
<11> instr 测试字符串出现的位置
select ename,instr(ename,'S') from emp;
'S'第1次出现的位置
select ename,instr(ename,'T',1,2) from emp;
从第1个位置开始 测试'T'第2次出现的位置
<12> length 字符串的长度
select ename,length(ename) from emp;

日期和 时间函数
<1> sysdate 系统时间
select sysdate from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'DDD') from dual
select to_char(sysdate,'D') from dual
select to_char(sysdate,'DAY') from dual
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

select '''' from dual;
select to_char(sysdate,'SSSSS') from dual;
--从今天零点以后的秒数
<2> ADD_MONTHS 添加月份 得到一个新的日期
select add_months(sysdate,1) from dual;
select add_months(sysdate,-1) from dual;
select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;
select add_months(sysdate,12) from dual;
一年以后的今天
select add_months(sysdate,-12) from dual;
一年以前的今天

trunc(sysdate) 截取年月日
select sysdate+2 from dual;
数字代表的是天数

两个日期之间的差值代表天数

<3> last_day 某月的最后一天
select last_day(sysdate) from dual;

select add_months(last_day(sysdate)+3,-1) from dual;
本月第3天的日期
<4> months_between 两个日期之间的月数
select months_between(sysdate,'2005-02-01') from dual;
方向 sysdate - '2005-02-01'
select months_between('2005-02-01',sysdate) from dual;

转换函数
to_char 把日期或数字类型变为字符串
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

select sal,to_char(sal,'L9,999') from emp;
L本地货币
to_number 把字符串变成数字
select to_number('19990801') from dual;

to_date 把字符串变成日期
select to_date('19800101','yyyymmdd') from dual;

select to_char(to_date('19800101','yyyymmdd'),
'yyyy"年"mm"月"dd"日"') from dual;
数学函数
ceil(x) 不小于x的最小整数
ceil（12.4) 13
ceil(-12.4) -12
floor(x) 不大于x的最大整数
floor(12.5) 12
floor(-12.4) -13

round(x) 四舍五入
round(12.5) 13
round(12.456,2) 12.46

trunc(x) 舍去尾数
trunc(12.5) 12
trunc(12.456,2) 12.45
舍去日期的小时部分
select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;
mod(x,n) x除以n以后的余数
mod(5,2) 1
mod(4,2) 0

power(x,y) x的y次方
select power(3,3) from dual;

混合函数
求最大值
select greatest(100,90,80,101,01,19) from dual;

求最小值
select least(100,0,-9,10) from dual;

空值转换函数 nvl(comm,0) 字段为空值 那么就返回0 否则返回本身
select comm,nvl(comm,0) from emp;
comm 类型和 值的类型是 一致的

复杂的函数
decode 选择结构 (if ... elseif .... elesif ... else结构)

要求:
sal=800 显示低工资
sal=3000 正常工资
sal=5000 高工资
只能做等值比较

select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')
from emp;
表示如下的if else 结构
if sal=800 then
'低工资'
else if sal =3000 then
'正常工资'
else if sal = 5000 then
'高工资'
else
'没判断'
end if

sal > 800 sal -800 > 0

判断正负
sign(x) x是正 1
x是负 -1
x是0 0
select sign(-5) from dual;

如何做大于小于的比较？？？？
sal<1000 显示低工资 sal-1000<0 sign(sal-1000) = -1
1000<=sal<=3000 正常工资
3000<sal<=5000 高工资

select sal,decode(
sign(sal-1000),-1,'低工资',
decode(sign(sal-3000),-1,'正常工资',
0,'正常工资',1,
decode(sign(sal-5000),-1,'高工资','高工资')
)) as 工资状态 from emp;

一般的情况 decode(x,y1,z1,y2,z2,z3)
if x= y1 then
z1
else if x = y2 then
z2
else
z3
end if

分组函数 返回值是多条记录 或计算后的结果
group by
sum
avg

<1> 计算记录的条数 count

select count(*) from emp;
select count(1) from emp;

select count(comm) from emp; 字段上count 会忽略空值
comm不为空值的记录的条数

统计emp表中不同工作的个数 ????
select count(distinct job) from emp;

select distinct job from emp;
select distinct job,empno from emp;
select job,empno from emp;
得到的效果是一样的,distinct 是消去重复行
不是消去重复的列
<2>group by 分组统计
--在没有分组函数的时候
--相当于distinct 的功能
select job from emp group by job;

select distinct job from emp;

--有分组函数的时候
--分组统计的功能
统计每种工作的工资总额是多少??
select job,sum(sal) from emp
group by job; --行之间的数据相加

select sum(sal) from emp; --公司的工资总额

统计每种工作的平均工资是多少??
select job,avg(sal) from emp
group by job;

select avg(saL) from emp; --整个公司的平均工资

显示平均工资>2000的工作?？？
<1>统计每种工作的平均工资是多少
<2>塞选出平均工资>2000的工作

从分组的结果中筛选 having
select job,avg(sal) from emp
group by job
having avg(sal) > 2000;
group by 经常和having搭配来筛选

计算工资在2000以上的各种工作的平均工资？？？？
select job,avg(sal) from emp
where sal > 2000
group by job
having avg(sal) > 3000;

一般group by 和 having搭配
表示对分组后的结果的筛选
where子句 --- 用于对表中数据的筛选

<3> max min
select max(sal) from emp;
公司的最高工资
select min(sal) from emp ;
公司的最低工资

找每个部门的最高和最低的工资??
select deptno,max(sal),min(sal) from emp
group by deptno;
找每个工作的最高和最低的工资??
select job,max(sal),min(sal) from emp
group by job;
找每个部门中每种工作的最高和最低的工资??
select deptno,job,max(sal),min(sal)
from emp
group by deptno,job;

select max(sal),min(sal)
from emp
group by deptno,job;

单个字段如果没有被分组函数所包含，
而其他字段又是分组函数的话
一定要把这个字段放到group by中去

<4>关联查询
多张表,而表与表之间是有联系的

是通过字段中的数据的内在联系来发生
而不是靠相同的字段名来联系的或者是否有主外键的联系是没有关系的
select dname,ename from emp,dept;
笛卡尔积 (无意义的)
--当2个表作关联查询的时候一定要写关联的条件
--N个表 关联条件一定有N-1个

select dname,ename from mydept,myemp
where mydept.no = myemp.deptno;

多表查询的时候一定要有关联的条件
--使用的表的全名
select dname,ename from emp,dept
where emp.deptno = dept.deptno ;

--使用表的别名
select dname,ename,a.deptno from emp a,dept b
where a.deptno = b.deptno and a.deptno = 10;

--等值连接(内连接-两个表的数据作匹配a.deptno = b.deptno )
select dname,ename,a.deptno from
emp a inner join dept b
on a.deptno = b.deptno;
where a.deptno = 10;

--on写连接条件的
--where中写别的条件

--使用where/on
select dname,ename,a.deptno from emp a,dept b
where a.deptno = b.deptno and a.deptno=10;

--on中写连接条件
--where中写其他的条件
select dname,ename,a.deptno from
emp a inner join dept b
on a.deptno = b.deptno
where a.deptno = 10 ;

--外连接
左外连接 右外连接 全外连接
(+)写法只有在ORACLE中有效
select dname,ename,b.deptno
from emp a,dept b
where a.deptno(+) = b.deptno;
--标准写法
select dname,ename,b.deptno
from emp a right outer join dept b
on a.deptno = b.deptno