学习oracle笔记基础.pdf

《学习oracle笔记基础.pdf》由会员分享，可在线阅读，更多相关《学习oracle笔记基础.pdf（22页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、学习 oracle 笔记-基础 学习 oracle 笔记 一、Sqlplus 基本命令(3)1.conn/connect 命令(3)2.disc/disconnect(3)3.password(3)4.show user(3)5.exit(3)二、用户管理(3)1.修改密码：password 用户名(3)2.用户管理.赋权限(3)3.用户管理.收回权限(3)4.用户管理.锁定用户(4)5.用户管理.终止密码(4)6.用户管理.不可使用旧密码(4)7.用户管理.删除配置文件(4)三、表管理(5)1.表和列的命名规则：(5)2.oracle 字段类型：(5)3.创建表(5)4.修改表(6)5.插入

2、数据(6)6.修改字段值(6)7.删除数据(6)四、表查询(7)1.基本查询(7)2.复杂查询(8)3.多表查询(9)4.子查询(9)（由已知通过公式计算出中间值，再应用中间值通过公式计算出下一个中间值，直到得出结果为止，称之为子查询或嵌套查询）(9)5.分页查询(10)6.用查询结果建表(11)五、数据库管理(11)1.创建新的数据库(11)六、Oracle 函数(11)1.按特定日期格式插入带日期列的记录(11)2.使用子查询插入大量数据(11)3.使用子查询更新大量数据(11)七、Oracle 事务(11)4.设置保存点：savepoint time1(保存点名);(11)5.只读事务:

3、(12)八、SQL 函数(12)1.字符函数(12)2.数学函数(13)3.日期函数：默认日期格式 dd-mon-yy（15-3 月-87）(14)4.转换函数：用于将数据类型从一种转为另外一种(14)5.系统函数(15)九、数据库管理(15)1.数据库管理员(15)2.数据库备份与恢复(16)3.数据字典与动态性能视图(18)十、表空间、数据文件(19)1.表空间：数据库逻辑组成部分。(19)2.Oracle 中逻辑结构包括表空间、段、区、块(19)3.表空间作用：(19)4.建立表空间（可使用客户端建）(19)5.改变表空间状态(20)十一、维护 oracle 数据完整性(21)1.确保数

4、据库的数据遵从一定的商业和逻辑规则（比如岁数为正数）(21)2.可以使用约束、触发器、应用程序（过程和函数）三种方法实现(21)3.约束，包括 not null,unique,primary key,foreign key,check(21)4.显示约束信息（查询数据字典视图）(22)5.列级定义：定义列的同时定义约束(23)6.表级定义：定义完所有列后在定义约束，但是 not null 只能在列级上定义.23 十二、索引(23)1.创建索引：(23)3.缺点(23)4.显示索引信息（查询数据字典视图）(23)十三、管理权限与角色(24)1.系统权限：控制用户是否可以执行特定命令等数据库操作，

5、如建表等。(24)2.对象权限(25)3.角色（权限的打包集合，便于权限快速分配）(25)一、Sqlplus 基本命令 1.conn/connect 命令 登录进命令窗口之后，要切换用户，用以下语法 conn 用户名/密码 as sysdba/sysoper 2.disc/disconnect 3.password 4.show user 5.exit 二、用户管理 1.修改密码：password 用户名 Alter user 用户名 identifild by 新密码 2.用户管理.赋权限 Grant 角色名 to 用户名（系统权限）Grant select on emp to 用户名（对象

6、权限）3.用户管理.收回权限 Revoke select on emp from 用户名 如果是对象权限，除了把权限赋予它，还可以让它把这权限赋予给其他人，则 Grant select on emp to 用户名 with grant option 如果是系统权限，除了把权限赋予它，还可以让它把这权限赋予给其他人，则 Grant connect to 用户名 with admin option 4.用户管理.锁定用户 指定 Scott 用户每天只能尝试 3 次登录，锁定时间为 2 天，实现方法：创建配置文件 SQLcreate profile lock_account（配 置 文 件 名）li

7、mit failed_login_attempts 3（尝试次数）password_lock_time 2（锁定天数）;更改用户 SQLalter user scott profile lock_account;解锁用户 SQLalter user 用户名 account unlock 5.用户管理.终止密码 让用户每隔 10 天修改密码，宽限期为 2 天 创建配置文件：SQLcreate profile myprofile limit password_life_time10 password_grace_time 2;分配配置文件：SQLalter user 用户名 profile myp

8、rofile 6.用户管理.不可使用旧密码 创建配置文件 SQLcreate profile 配置文件名 limit password_life_time10 password_grace_time 2 password_reuse_time 10;分配配置文件 SQLalter user 用户名 profile 配置文件名 7.用户管理.删除配置文件 SQLdrop profile 文件名【cascade】三、表管理 1.表和列的命名规则：1)必须以字母开头；2)不能超过 30 个字符 3)不能使用 oracle 的保留字 2.oracle 字段类型：1)字符型：a.char（定长）占空间大

9、，查询速度极快 b.varchar2（变长）节省空间 c.clob（字符型大对象）2)数字型：Number（范围达正负 10 的 38 次方），小数：number(5,2),整数：number(5)3)日期类型：1)Date 2)Timestamp（时间戳，精确度高）4)图片型 Blob（可存放图片、声音、视频）一般不把该类型的文件放入数据库中，而放在某文件夹下，然后把该路径放到 数据库中；出于安全性考虑，可把该类型的文件放入数据库中 3.创建表 SQLcreate table student(2 Xh number(4),3 Xm varchar2(20),4 Sex char(2),5 B

10、irthday date,6 Sal number(7,2)7);SQLcreate table class(2 Classid number(2),3 Cname varchar2(20)4);4.修改表 1)添加一个字段 SQL alter table student add(classid number(2);Desc 表名（查看表）2)修改字段长度 SQL alter table student modify(xm varchar2(30);3)修改字段类型/名字（不能有数据）SQL alter table student modify(xm char(30);4)删除一个字段 SQL

11、 alter table student drop column sal;5)修改表的名字 SQL rename student to stu;删除表 6)SQLdrop table student;5.插入数据 1)插入全字段：Insert into student values(A001,张三,男,01-5 月-05,10);Oracle 中日期格式默认为DD-MON-YY即31-6 月-14 修改默认格式 alter session set nls_date_format=yyyy-mm-dd;2)插入部分字段：Insert into student(xh,xm,sex)values(A

12、003,张三,男);3)插入空值：Insert into student(xh,xm,sex,birthday)values(A004,李四,男,null);6.修改字段值 1)改一个字段：Update student set sex=女 where xh=A001;2)修改多个字段：Update student set sex=男,birthday=1980-04-01where xh=A001;3)修改含有空值的记录：null 为空，不存在，不能参与+-*/等运算 7.删除数据 1)Delete from student;删除所有记录，表结构还在，写日志，可以恢复的，速度慢（SQLSave

13、point 时间点名称;SQLDelete from 表名 SQLRoolback to 时间点名称）则可恢复数据 2)Drop table student;删除表的结构和数据 3)Delete from student where xh=A001;删除一条记录 4)Truncate table student;删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快 四、表查询 1.基本查询 1)简单查询语句（select 语句不区分大小写，被查询的内容区分大小写）1)查看表结构：SQLdesc dept;2)查询所有列：select*from dept;3)查询指定列：sele

14、ct ename,sal,job,deptno from emp;4)过滤重复行：select distinct deptno,job from emp;5)使用算数表达式/别名：select sal*12 as“年薪”from emp;6)处理空值：select sal+nvl(comm,0)as“月收入”from emp;7)如何连接字符串(|)：select ename|is a|job from emp;8)Where 子句：条件为数值的，有无都一样；条件为日期的，格式为01-1 月-2005 9)Like：%表示无或者任意一个或多个字符；_表示任意单个字符 Select ename,

15、sal from emp where ename like S%;Select ename,sal from emp where ename like _O%;10)Where 中使用 in：Select*from emp Where empno in(7566,7312,7329);11)查询包含 null 的记录：select*from emp where mgr is null;12)逻辑操作符：select*from emp where(sal2000 or job=MANAGER)and ename like J%13)Order by Select*from emp order b

16、y sal asc/desc;Select*from emp order by deptno,sal desc;Select ename,(sal+nvl(comm,0)*12 as“年薪”from emp order by“年 薪”;2.复杂查询 1)数值计算函数：select 后面必须为同类型的对象，不可字段与分组函数一起使 用 Max,min,avg,sum,count Select max(sal),min(sal)from emp;select ename,sal from emp where sal=(select max(sal)from emp);select avg(sal)

17、,sum(sal)from emp;select count(*)from emp;select*from emp where sal(select avg(sal)from emp);2)Group by 用于对查询结果分组统计 select deptno,avg(sal),max(sal)from emp group by deptno;按分组的字段必须出现在查询列 select deptno,job,avg(sal),max(sal)from emp group by deptno,job order by deptno;3)Having 用于过滤分组后的显示结果，条件函数，等同wher

18、e的作用，在有聚合 函数的 select 中不能用 where，而是用 having 代替 where select deptno,avg(sal)from emp group by deptno having avg(sal)2174;4)总结：A.分组函数只能出现在选择列、having、order by 子句中；B.如果 select 语句中同时包含 group by，having，order by 那么这三者的 顺序是 group by，having，order by；C.在选择列中如果有列、表达式、分组函数，那么这些列和表达式必须有一 个出现在 group by 子句中，否则会出错。3

19、.多表查询 1)select ename,sal,dept.dname from emp,dept where emp.deptno=dept.deptno;2)select ename,sal,d.dname from emp E join dept D on E.deptno=D.deptno 别名如果是字母，并且括在双引号内的，一定要大写 3)select ename,sal,S.grade from emp E join salgrade S on E.sal=S.losal and E.sals.hisal;4)select ename,sal,S.grade,D.dname,D.d

20、eptno from emp E join salgrade S on E.sal=S.losal and E.sals.hisal join dept D on E.deptno=D.deptno order by D.deptno;5)select E1.empno,E1.ename,E2.ename from emp E1 join emp E2 on E1.MGR=E2.empno;4.子查询（由已知通过公式计算出中间值，再应用中间值通过公式计算出下一个中间值，直到得出结果为止，称之为子查询或嵌套查询）单行子查询：子查询返回值是单行单列 多行子查询：子查询返回值多行单列 多列子查询：子

21、查询返回值是多列 A.单行子查询：子查询返回值是单行单列 B.多行子查询：子查询返回值多行单列 1)select*from emp where deptno=(select deptno from emp where ename=SMITH);select*from emp where job in(select job from emp where deptno=10);2)select*from emp where salall(select sal from emp where deptno=30);select*from emp where sal(select max(sal)fro

22、m emp where deptno=30);3)select*from emp where salany(select sal from emp where deptno=30);select*from emp where sal(select min(sal)from emp where deptno=30)order by sal;C.多列子查询：子查询返回值单行多列 1)select*from emp where deptno=(select deptno from emp where ename=SMITH)and job=(select job from emp where ena

23、me=SMITH)and enameSMITH;2)select*from emp where(deptno,job)=(select deptno,job from emp where ename=SMITH)and enameSMITH;3)子查询作为一个临时表联合查询 select*from emp E join(select deptno,avg(sal)as部门平均工资 from emp group by deptno)A on E.deptno=A.deptno where E.salA.部门平均工资 order by E.deptno;给【列】加别名，可以加一个 as；给【表】加

24、别名，不可以加 as 5.分页查询 1)Rownum 分页 Select*from emp;Select e.*,rownum rn from(select*from emp)e;Select e.*,rownum rn from(select*from emp)e where rownum=10;Select*from(Select e.*,rownum rn from(select*from emp)e where rownum6;如果要指定查询列，只需改最里层的子查询的信息 如果要排序，只需要 order by 最里层的子查询即可 2)6.用查询结果建表 SQLcreate table

25、test_emp(id,ename,sal)as select empno,ename,sal from emp;7.合并查询 select*from emp where job=MANAGER union(关键词)select*from emp where sal=3000;关键词：Union（去处重复项的合并）union all（不去处重复项，全部合并）intersect(取交集)minus(取相减值，存在第一个表中，同时不存在第二个表中)五、数据库管理 1.创建新的数据库 使用工具创建：dbca（数据库管理助手）六、Oracle 函数 1.按特定日期格式插入带日期列的记录 to_date

26、(1988-10-20,yyyy-mm-dd)2.使用子查询插入大量数据 Insert into 目标表名(目标字段 1,目标字段 2,)select empno,ename,deptno from emp where deptno=10;3.使用子查询更新大量数据 Update emp set(job,sal,comm)=(select job,sal,comm from emp where enama=SMITH)where ename=SCOTT;七、Oracle 事务 4.设置保存点：savepoint time1(保存点名);回退到某事务点：rollback to time1;回退全

27、部：rollback;5.只读事务:1)DBA 设置：set transaction read only;2)DBA 只能查询到设置之前的数据，查询不到其他用户在这个时间点之后对数据 的增删改的数据 八、SQL 函数 1.字符函数 1)Lower(字段名):将字符串转化为小写的格式 select lower(ename)from emp;2)Upper(字段名):将字符串转化为大写的格式 3)Length(字段名):返回字符串的长度 select*from emp where length(ename)=5;4)Substr(字段名,m,n):取字符串的子串 select substr(ena

28、me,1,3)from emp;从第一个字符开始截取，一共截取 3 个字符 5)综合使用 select(upper(substr(ename,1,1)|lower(substr(ename,2,length(ename)-1)as 名字 from emp;6)Replace(char1,search_string,replace_string)Select replace(ename,A,A-A)from emp;7)Instr(char1,char2,n,m)取子串在字符串的位置 Instr(char1,char2,n,m)取子串在字符串的位置 Chan1 为搜索源，char2 为搜索目标，

29、n 为搜索开始位置，默认为 1，m 为第几 次出现目标。SQL select instr(azhangsanbcd,zhangsan)from dual;-返回 2 SQL select instr(oracle traning,ra,1,1)instring from dual;-返回 2 SQL select instr(oracle traning,ra,1,2)instring from dual;-返回 9 SQL select instr(oracle traning,ra,1,3)instring from dual;-返回0，根据条件，由于ra只出现二次，第四个参数3，就是说第

30、 3 次出现 ra 的位置，显然第 3 次是没有再出现了，所以结果返回 0。注意空格也算一个 字符 SQL select instr(abc,d)from dual;-返回 0 2.数学函数 Round(n,m)该函数用于执行四舍五入，如果省掉 m，则四舍五入到整数，如果 m 是正数，则四舍五入到小数点的 m 位后，如果 m 是负数，则四舍五入到小数点的 m 位前 SELECT round(23.75123)FROM dual;-返回 24 SELECT round(23.75123,-1)FROM dual;-返回 20 SELECT round(27.75123,-1)FROM dual;

31、-返回 30 SELECT round(23.75123,-3)FROM dual;-返回 0 SELECT round(23.75123,1)FROM dual;-返回 23.8 SELECT round(23.75123,2)FROM dual;-返回 23.75 SELECT round(23.75123,3)FROM dual;-返 回23.751 Trunc(n,m)该函数用于截取数字，如果省掉 m，就截取整数部分，如果 m 是正数，就截取到小数点的 m 位后，如果 m 是负数，则截取到小数点的前 m 位 Mod(m,n)该函数用于取余值：即取（m-整数个 n）剩下的值 select

32、 mod(10,2)from dual;-返回 0 SELECT MOD(10,3)FROM dual;-返回 1 Floor(n)返回小于或等于 n 的最大整数 select floor(10.9)from dual;-返回 10 Ceil(n)返回大于或等于 n 的最小整数 select ceil(10.1)from dual;-返回 11 Abs(n):返回数字 n 的绝对值 select abs(-10.1)from dual;-返回 10.1 3.日期函数：默认日期格式 dd-mon-yy（15-3 月-87）Sysdate:该函数返回系统时间 select sysdate from

33、 dual;(dual 是用于测试的虚拟表)Add_months(d,n)：该函数取值：d(日期)+n 个月结果的日期 select ADD_MONTHS(TO_DATE(15-11月-1961,dd-mon-yyyy),2)as 日期 from dual;-返回 1962-01-15 select ADD_MONTHS(30-11 月-1961,1)as 日期 from dual;-返回 1961-12-31 select ADD_MONTHS(30-11 月-1961,3)as 日期 from dual;-返回 1962-2-28 select*from emp where sysdate

34、=add_months(HIREDATE,12*30);-入职 30 年以上的员工 select ename,trunc(sysdate-hiredate)as 入职天数 from emp;Last_day(d):返回指定日期所在月份的最后一天 select*from emp where last_day(hiredate)-hiredate=2;-月末倒数第二天入职的员工 4.转换函数：用于将数据类型从一种转为另外一种 To_char 函数 Select ename,to_char(hiredate,yyyy-mm-dd hh24:mi:ss)from emp;select ename,to

35、_char(sal,L99999.99)from emp;【9】：显示数字，并忽略前面的 0【0】：显示数字，如位数不足，则用 0 补齐【.】：在指定位置显示小数点【，】：在指定位置显示逗号【$】：在数字前面加美元【L】：在数字前面加本地货币符号【C】：在数字前面加国际货币符号【G】：在指定位置显示组分隔符【D】：在指定位置显示小数点符号 select*from emp where to_char(hiredate,yyyy)=1980;select*from emp where to_char(hiredate,mm)=12;to_date 函数 to_date(sysdate,dd-mon

36、-yy)：该函数用于以 dd-monyy 格式取出 sysdate 日期。select to_date(sysdate,dd-mon-yy)from dual;5.系统函数 Sys_context select sys_context(userenv,db_name)from dual;【terminal】:当前会话客户所对应的终端的标识符【language】:语言【db_name】:当前数据库名称【nls_date_format】:当前会话客户所对应的日期格式【session_user】:当前会话客户所对应的数据库用户名【current_schema】:当前会话客户所对应的默认方案名【hos

37、t】：返回数据库所在主机的名称 九、数据库管理 1.数据库管理员 1)权限 职责：安装和升级数据库 建库、表空间、表、视图、索引 制定并实施备份与恢复计划 数据库权限管理，调优，故障排除 SQL 语句、存储过程、触发器、规则、约束、包 用户区别：Sys:存放数据字典的基表、视图，拥有 dba、sysdba、sysoper角色，具有最高权限 System：存放次一级的内部数据如特性等，拥有dba、sysdba角色 权限大小：sysdbasysoperdba 2)管理初始化参数 Show parameter 2.数据库备份与恢复 1)逻辑备份 Exp（导出）【以下语句，只能在 CMD 中运行】【（

38、运行-CMD-G：-cd G:oracle）可更改 CMD 盘符】【在 g:oracleproduct10.2.0db_1bin 中执行导出导入命令】导出分三种：1.导出表；2.导出方案；3.导出数据库 Userid：用于指定应用哪个用户名/口令导出 Tables：用于指定导出的表 Owner：用于指定导出的方案 Full=y：用于指定导出数据库 Inctype：用于指定导出的增量类型 Rows：用于指定导出是否要导出表中的数据 File：用于指定导出文件名 导出用户自己的表 Exp userid=scott/tigerorcl(实例名)tables=(emp,dept)file=d:e1.d

39、mp 导出其他用户的表 Exp userid=sys/orclorcl(实例名)tables=(scott.emp,scott.dept)file=d:e2.dmp 导出表结构 Exp userid=scott/tigerorcl tables=(emp,dept)file=d:e3.dmp rows=n 直接导出方式(速度快，但数据库字符集要与客户端字符集一致)Exp userid=scott/tigerorcl tables=(emp,dept)file=e4.dmp direct=y 导出方案 Exp userid=scoot/tigerorcl owner=scott file=d:s

40、cott.dmp Exp userid=system/orclorcl owner=(system,scott)file=d:system.dmp 导出全数据库 Exp userid=system/orclorcl full=y inctype=complete file=d:all.dmp IMP（导入）导入文件必须为 export 所导出的文件 也分 1.导入表；2.导入方案；3.导入全数据库 Userid：用于指定执行导入操作的用户名，口令连接字符串 Tables：用于指定执行导入操作的表 Fromuser：用于指定源用户 Touser：用于指定目标用户 File：用于指定导入文件名 F

41、ull=y：用于指定执行导入整个文件 Inctype：用于指定执行导入操作的增量类型 Rows：指定是否要导入表行 Ignore：如果表存在，则只导入数据 导入自己的表 Imp userid=Scott/tigerorcl tables=(emp,dept)file=d:e1.dmp 导入表到其他用户 要求该用户具有 dba 的权限，或是 imp_full_database 权限 Imp userid=system/orclorcl tables=(emp,dept)file=d:e2.dmp touser=scott 导入表结构 只导入表的结构而不导入数据 Imp userid=scott/

42、tigerorcl tables=(emp,dept)file=d:e3.dmp rows=y 导入数据 如果对象（比如表）已经存在，可以只导入表的数据 Imp userid=scott/tigerorcl tables=(emp,dept)file=d:e4.dmp ignore=y 导入自身方案 Imp userid=Scott/tiger file=d:e5.dmp 导入其他方案（要求该用户具有 DBA 权限）Imp userid=system/orcl file=d:e6.dmp fromuser=system touser=scott 导入数据库 Imp userid=system/

43、orcl full=y file=d:e7.dmp Imp userid=gxjglj/gxjglj full=y file=d:gxjglj.DMP 2)物理备份 3.数据字典与动态性能视图 1)数据字典是 oracle 数据库中最重要的组成部分，它提供了数据库的一些系统 信息 静态数据（比如人的肤色）记录系统信息 包含数据字典基表和数据字典视图 基表：存储基本信息，不能直接查询 视图：基于基表的视图，可查询，包括 user_*,all_*,dba_*User_tables 当前用户所拥有的表的视图 Select*from user_tables;All_tables 当前用户可以访问的表的

44、视图 Select*from all_tables;Dba_tables（用户需具有 DBA 角色）显示所有方案的表 所有者为 sys 只可 select 语句 用户名、权限、角色 建用户：用户信息存于数据字典中 给用户赋权限与角色：信息存于数据字典中 Dba_users：显示所有用户的视图 查询所有的系统权限 Dba_sys_privs：显示用户所具有的系统权限的视图 Select*from system_privilege_map;查询所有的对象权限 Dba_tab_privs 显示用户的对象权限的视图 Dba_col_privs：显示具有的列权限 Dba_role_privs：显示用户所

45、具有的角色 Oracle 有多少种角色 Select*from dba_roles;1.查询用户所包含的角色 Select*from dba_role_privs where grantee=用户名 2.查询角色包含的权限 系统权限 Select*from dba_sys_privs where grantee=DBA或 Select*from role_sys_privs where role=DBA 对象权限 Select*from dba_tab_privs where=grantee=角色名 查询数据库的表空间 Select*from dba_tablespaces;显示当前用户可以访

46、问的所有数据字典视图 Select*from dict where comments like%grant%显示当前数据库的全称 Select*from global_name 通过数据字典还可查询以下信息 对象定义情况 对象占用空间大小 列信息 约束信息 2)动态性能视图记载了例程启动的相关信息 动态数据（比如人跑的速度）记录当前例程活动信息 启动 oracle server 时建立，关闭 oracle server 时删除 以 V_$开始，同义词以 V$开始 由 DBA 或者特权用户查询 十、表空间、数据文件 1.表空间：数据库逻辑组成部分。层次关系：数据存于数据文件存于表空间（逻辑）数据库 2.Oracle 中逻辑结构包括表空间、段、区、块 层次关系：数据库包含表空间包含段包含区包含块 3.表空间作用：控制数据库占用磁盘空间 Dba 可以将不同的数据类型部署到不同的位置，有利于提高 I/O性能，同时利于备份和恢复等管理操作 4.建立表空间（可使用客户端建）/s.hisal/s.hisal;