2022年ORACLE数据库学习总结 .pdf

《2022年ORACLE数据库学习总结 .pdf》由会员分享，可在线阅读，更多相关《2022年ORACLE数据库学习总结 .pdf（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1 数据库学习总结 -Marlon 目录一、ORACLE_ 简介 . 1 二、ORACLE_ 简单查询 . 3 三、ORACLE 标量函数和算数运算 . 5 四、ORACLE_ 多表查询 . 9 五、ORACLE_ 列函数和分组 . 10 六、ORACLE_ 子查询 . 12 七、ORACLE_ 表的更新操作 . 13 八、ORACLE_ 表与视图的管理 . 15 一、ORACLE_ 简介 Oralce 数据库发展Oracle 8 Oracle 8i ：i 表示 internet ，标识着Oracle 公司正式进军互联网。Oracle9i Oracle10g ：g 表示 grid，即网络技术。

2、Oracle11g Oracle 体系结构一物理结构：文件系统控制文件数据文件日志文件参数文件（不是数据库的组成成分） Oracel 体系结构三逻辑结构：block 快extent 盘区segment 段tablespace 表空间datafile 数据文件名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 18 页 - - - - - - - - - 2 SQL * Plus下的常用命令连接到 SQL*PLUS sqlplus user/passwordas sysdaba

3、|sysoper sqlplus/nolog 启动数据库startup mount 启动实例，打开控制文件，但不打开数据文件startup nomount 只启动实例关闭数据库shutdown immediate 迫使每个用户执行为当前的SQL 语句，立即断开连接shutdown transactional 迫使用户执行完当前事务时，断开连接shutdown abort 强制关闭数据库常用 SQL 命令show user ：查看当前连接的用户connect scott/tiger： 采用 scott 的用户名 /tiger 的密码连接数据库desc table_name：查看 tableNam

4、e表结构quit|exit ：退出disconnect ：断开连接clear screen ：清屏，相当于Windows下的 cls 命令select * from tab：列出当前用户下的所有表pata 执行 pata 制定的脚本文件 Oracle 常用基本数据类型varchar2/varchar：变成字符串char ：定长字符串Integer ：整型number(m,n) ：数字型smallint ：短整型float ：浮点数decimal ：十进制数字（小数）date ：日期型名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - -

5、 名师精心整理 - - - - - - - 第 2 页，共 18 页 - - - - - - - - - 3 二、ORACLE_ 简单查询SQL 结构化查询语言（ Structured Query Language） SQL 分类1. 数据定义语言（Data Definition Language，DDL ） ：create 、alter 、drop。create table 表名 ; alter table add 新列名数据类型 ; drop table 表名 ; 2. 数据操纵语言（Data Manipulation Language ，DML ） ：insert 、update 、de

6、lete 、select 。Insert into 表名 (字段 1,字段 2 ) values( 值 1,值 2); update student set sage=22 where sno=200215；(将学生 200215 的年龄改为22 岁) select distinct job from emp；去除重复行3. 数据控制语言（Data Control Language，DCL ） ：commit work 、rollback work 。查询雇员的所有信息select * from emp；*表示所有列查询语句的格式select *| 列名 from 表名查询雇员的编号，姓名，工

7、资select * from empno,ename,sal from emp; 查询所有职位select job from emp; select distinct job from emp; distinct ： 有区别的（去除重复行）查询工资大于1500 的雇员信息，列出编号，用户名，工资select empno,ename,sal from emp where sal1500; 带有 where 条件查询语句的基本格式select *| 列名 from 表名where 条件 ; 比较运算符大于： 小于： = 小于等于： = 不等于： !=或者 名师资料总结 - - -精品资料欢迎下载

8、- - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 18 页 - - - - - - - - - 4 限定查询is null 和 is not null 的使用查询每月可以得到奖金的雇员select empno,ename,comm from emp where comm is not null; select empno,ename,comm from emp where comm is not null and comm0; 查询谁没有奖金select empno,ename from emp where com

9、m is null; 限定查询and 的使用查询工资大于1500 ，并且可以领取奖金的雇员SQL select empno,ename,sal,comm from emp where comm is not null and sal1500; 限定查询or 的使用查询工资大于1500 和可以领取奖金的雇员select empno,ename,sal,comm from emp where sal1500 or comm is not null; 查询没有奖金的雇员select empno,ename,comm from emp where comm=0 or comm is null; 限定查

10、询使用 not 对条件整体取反查询工资不大于1500 并且不能领取奖金的雇员select empno,ename,sal,comm from emp where sal1500 or comm is not null); 限定查询between.and.的使用查询基本工资大于等于1500 并且小于等于3000 的雇员select empno,ename,sal from emp where sal=1500 and sal1500 order by sal; 查询工资大于1500 员工的信息，按工资降序，按雇佣日期升序排序select * from emp where sal1500 orde

11、r by sal desc,hiredate asc; 注： ASC 升序， DESC 降序，默认ASC 。ascending ?sendi? 上升的，降序排列（descend disend 的缩写）；三、ORACLE 标量函数和算数运算字符函数的使用转换为大写字母select upper(smith) from dual; 注： dual 是公共表。 upper ?p? 上面的，上部的转换为小写字母select lower(SMITH) from dual; 注： lower 放下每个单词的字母变成大写，其余字母小写select initcap(hello world) from dual

12、串连接 (concat): 可以使用 | 进行串连接select concat(HELLO,WORLD) from dual; select HELLO|WORLD from dual; 求子串 (SUBSTR) select substr(ename,1,3) from emp; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 18 页 - - - - - - - - - 6 注：含义为截取ename 字段从第一个字符开始，总共三个字符求长度 (LENGTH) sele

13、ct length(ename) from emp; 串替换 (REPLACE) select replace(HELLO WORLD,WOR,WEL) from dual; 结果：REPLACE(HE - HELLO WELLD 注：第一个逗号是原字符，第二个逗号是原字符的一部分，第三部分是替换原字符那个部分。要求显示所有雇员的姓名和姓名的后三个字符select ename,substr(ename,length(ename)-2) from emp; select ename,substr(ename,-3) from emp; 四舍五入 (Round) select round(789.

14、536) from dual; ROUND(789.536) - 790 四舍五入 (Round) ：指定保留小数位数select round(789.536,2) from dual; - 789.54 四舍五入 (Round) ：对整数四舍五入select round(789.536,-2) from dual; ROUND(789.536,-2) - 800 截断小数位 (TRUNC) select trunc(789.536) from dual; TRUNC(789.536) - 789 截断小数位 (TRUNC): 指定保留小数位select trunc(789.536,2) fr

15、om dual; TRUNC(789.536,2) - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 18 页 - - - - - - - - - 7 789.53 截断小数位 (TRUNC): 对整数截断select trunc(789.536,-2) from dual; TRUNC(789.536,-2) - 700 除空格（ TRIM ）SQL SELECT TRIM ( 55) FROM DUAL; TRIM(55) - 55 取余数 (MOD) select

16、 mod(10,3) from dual; 日期函数运算规律：日期 +数字 =日期日期 -数字 =日期日期 -日期 =数字（天数）查询当前日期select sysdate from dual; SYSDATE - 10-3 月 -12 显示 10 部门员工进入公司的星期数select empno,ename,round(sysdate-hiredate)/7) from emp where deptno=10; 在指定日期上加入指定的月数之后的日期(ADD_MONTHS) select add_months(sysdate,4) from dual; ADD_MONTHS(SYS - 10-7

17、 月 -12 求出给定日期范围的月数(months_between) select empno,ename,months_between(sysdate,hiredate) from emp; 下一个给定的星期是那个日期(next_day) select next_day(sysdate,星期一 ) from dual; 注：表示在当前时间的前提下，下个礼拜一的具体日期求出给定日期所在月份的最后一天日期(last_day) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共

18、18 页 - - - - - - - - - 8 select last_day(sysdate) from dual; 转换成字符串 (to_char) 查询雇员号，姓名，以及入职的年份select empno,ename,to_char(hiredate,yyyy) from emp; 注： yyyy 匹配年份， mm 匹配月份， dd 匹配日使用 to_char 设置日期的显示格式select empno,ename,to_char(hiredate,yyyy-mm-dd) from emp; 使用 fm 去掉前导0 select empno,ename,to_char(hiredate

19、,fmyyyy-mm-dd) from emp; EMPNO ENAME TO_CHAR(HI - - - 7369 SMITH 1980-12-17 7499 ALLEN 1981-2-20 注：正常的值是1981-02-20 ，02 前面的 0 去掉，变成了1981-2-20. 通过 to_char 设置数值的格式select empno,ename,to_char(sal,99,999) from emp; 说明： 9 代表 1 位数字EMPNO ENAME TO_CHAR - - - 7369 SMITH 800 7499 ALLEN 1,600 7521 WARD 1,250 注：把

20、 sal 的值分成两个部分，后三位为一个部分，前两位为一个部分，用逗号隔开。显示余额select empno,ename,to_char(sal,$99,999) from emp; 说明： $代表美元转换成数字 (to_number) select to_number(123)+to_number(123) from dual; TO_NUMBER(123)+TO_NUMBER(123) - 246 转换成日期 (to_date) select to_date(2009-07-31,yyyy-mm-dd) from dual; TO_DATE(2009- - 31-7 月 -09 名师资料总

21、结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 18 页 - - - - - - - - - 9 算数运算查询每个员工的年收入select empno,ename,(sal+comm)*12 from emp; 年收入 =工资 +奖金查询每个员工的年收入select empno,ename,(sal+comm)*12 income from emp; 查询每个员工的年收入select empno,ename,(sal+NVL(comm,0)*12 income from emp;

22、注： NVL(comm,0) ，当 comm 的值为 null 时，用 0 替换四、ORACLE_ 多表查询格式：select 列名 1 别名 1,. from 表名 1,表名 2,. where 条件order by 列名查询员工的编号，姓名，部门编号，部门名称select empno,ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno; 关联查询 -为表命别名查询员工的编号，姓名，部门编号，部门名称select empno,ename,e.deptno,dname from emp e,dept d where

23、 d.deptno=e.deptno; 注：这里的e 是 emp 的别名， d 是 dept 的别名关联查询 -自连接查询出每个雇员的姓名、工作、雇员的直接上级领导的姓名select e.empno,e.ename,e.job,m.ename mname from emp e,emp m where e.mgr=m.empno; 注： mname 是 m.ename 的别名关联查询 -多表关联名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 18 页 - - - - - -

24、 - - - 10 查询出每个雇员的姓名，工作，雇员的直接上级领导，以及部门名称select e.ename,e.job,m.ename,d.dname from emp e,dept d,emp m where e.mgr=m.empno and e.deptno=d.deptno; 注：当查询的字段间的关系涉及到n 个表时，则n 个表之间关联。左、右外连接查询员工编号，姓名，所在部门号，部门名称，将没有员工的部门也显示出来select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno; 注： (+)在

25、左边，表示右连接，会列出所有右表中出现但是没有在左表中出现的行。查询雇员的编号，姓名及其领导的编号，将没有领导的员工也列出来select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno(+); 注： (+)在右边表示左连接，会列出左表中出现但没有在右表中出现的行。交叉连接 (cross join) ：用来长生笛卡尔积select * from emp cross join dept; 自然连接 (nutural join) ：自动进行关联字段的匹配select * from emp natural joi

26、n dept; using 子句：直接指定操作关联列select * from emp join dept using(deptno) on 子句：用户自己编写连接条件select * from emp join dept on emp.deptno=dept.deptno; left join ：左外连接 right join ：右外连接五、ORACLE_ 列函数和分组常用的列函数sum(expression) 求和max(expression) 求最大值名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - -

27、 - - - - 第 10 页，共 18 页 - - - - - - - - - 11 min(expression) 求最小值avg(expression) 求平均数count(expression) 统计记录数count(distinct colname) 统计去除重复行记录数nvl(comm,0) 当 comm 为 null 时，用 0 替换列函数的使用查询员工的记录数，员工工资的总和，平均工资，最高工资，最低工资select count(*) count_emp,sum(sal) sum_sal, max(sal) max_sal,min(sal) min_sal, avg(sal)

28、avg_sal from emp; 列函数的使用-对 null 的处理查询所有员工的年收入select sum(sal+comm) from emp; SUM(SAL+COMM) - 7800 注：当 comm 为 null 时， sal+comm是没有结果值的select sum(sal)+sum(comm) from emp; SUM(SAL)+SUM(COMM) - 31225 select sum(sal+nvl(comm,0) from emp; SUM(SAL+NVL(COMM,0) - 31225 分组查询语法格式select 列名 .,列函数from 表名 . where 条件

29、 . group by 列名 . order by 列名 . 查询每个部门的人数select deptno,count(*) from emp group by deptno; 规则：如果在select 字句中，有不在列函数中的列，则该列一定要出现在group by 之后。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 18 页 - - - - - - - - - 12 分组查询having字句的使用显示出平均工资大于2000 的部门编号和平均工资select dept

30、no,avg(sal) from emp group by deptno having avg(sal)2000; SQL 语句的书写顺序select 列名 ,列函数from 表名where 条件group by 列名having 条件order by 列名SQL 语句的执行顺序from where group by having select order by 查询 20,30 部门的平均工资，并将平均工资大于2000 的输出，输出结果按平均工资排序select deptno,avg(sal) from emp where deptno in(20,30) group by deptno h

31、aving avg(sal)2000 order by avg(sal); 六、ORACLE_ 子查询在 where子句中使用子查询查询工资比7654 雇员工资高的全部雇员信息select * from emp where sal(select sal from emp where empno=7654); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 18 页 - - - - - - - - - 13 子查询 -in 的使用查询和 smith 或 jones 在同一

32、部门，同一职位工作的员工select ename from emp where (deptno,job) in (select deptno,job from emp where ename in(SMITH,JONES); 子查询 -any 的使用 =any ：与 in 操作符的效果一致查询和 smith 或 jones 在同一部门，同一职位工作的员工select ename from emp where (deptno,job)=any(select deptno,job from emp where ename in(SMITH,JONES); any ：只要大于子查询中的任何一个值即可

33、select * from emp where salany (select min(sal) from emp group by deptno); any ：只要小于子查询中的任何一个值即可select empno from emp where salall ：比最大的值大select * from emp where salall (select min(sal) from emp group by deptno); 注： select min(sal) from emp group by deptno结果为MIN(SAL) - 950 800 1300 是每个部门当中的最少工资。 ren

34、ame myemp to iemp; 表已重命名。截断表truncate table 表名 ; 意义：清空表中的所有数据，并且立即释放资源，该操作是不可回滚。约束 -约束的分类主键约束：表示一个唯一的标识，本身不能为空唯一约束：列值不允许重复检查约束：检查一个列的内容是否合法非空约束：不能为空值，如用户不能为空(no null) 外键约束：在两张表中进行约束的操作主键约束 (primary key) 主键约束一般在id 上使用，而且本身已经默认了不能为空，主键约束可以在建表的时候指定create table person( pid varchar2(18) primary key, name

35、varchar(30), age number(3), sex varchar2(2) default男 ); 使用 constraint 指定constraint k ?nstreint约束；强制create table person( pid varchar2(18), name varchar(30), age number(3), sex varchar2(2) default男 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 18 页 - - - - - -

36、- - - 17 constraint person_pid_pk primary key(pid); ); 非空约束 (not null) create table person( pid varchar2(18) primary key, name varchar(30) not null, age number(3), sex varchar2(2) default男 ); 唯一约束 (unique) create table person( pid varchar2(18) primary key, name varchar(30) unique not null, age numbe

37、r(3), sex varchar2(2) default男 ); 视图概念：一个视图实质是封装了一条复杂的SQL 语句创建视图语法：create view 视图名称as 子查询创建部门20 员工的雇员信息create view empv20 as select * from emp where deptno=20; 注：当创建视图以后，可以像操作表一样操作视图。注：视图的操作会影响到表的操作。with check option 不能更新创建视图的条件with read only 创建只读视图删除视图语法：drop view 视图名称注：当删除视图所在的表时，则视图也不能被使用序列创建序列语法

38、：sequence si:kw?ns create sequence 序列名称 ; create sequence myseq; 序列的操作：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 18 页 - - - - - - - - - 18 nextval ：取得序列的下一个内容currval ：取得当前序列的内容创建表验证序列的操作create table testseq(next number,curr number); insert into testseq va

39、lues(myseq.currval,myseq.nextval); 创建序列指定每次增长的增量create sequence myseq increment by 2; 注：每次增长2 创建序列指定开始的序列，默认的序列从1 开始。create sequence myseq increment by 2 start with 10; 注：序列从10 开始，每次增长2 创建一个序列1，3，5， 7，9. create sequence myseq maxvalue 9 increment by 2 start with 1 cache 2 cycle; 注：序列从1 开始，每次增长2，最大值为9，循环两次。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 18 页 - - - - - - - - -