ORACLE数据库学习总结

2020-03-02 05:46:47 来源：范文大全收藏下载本文

数据库学习总结-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，即网络技术。 Oracle11g

 Oracle体系结构一物理结构：文件系统控制文件数据文件日志文件

参数文件（不是数据库的组成成分）

 Oracel体系结构三逻辑结构： block 快 extent 盘区 segment 段

tablespace 表空间 datafile 数据文件

1  SQL * Plus下的常用命令

 连接到SQL*PLUS sqlplus user/paword[as sysdaba|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：查看tableName表结构 quit|exit：退出

disconnect：断开连接

clear screen：清屏，相当于Windows下的cls命令 select * from tab：列出当前用户下的所有表 @pata 执行pata制定的脚本文件

 Oracle常用基本数据类型 varchar2/varchar：变成字符串 char：定长字符串 Integer：整型

number(m,n)：数字型 smallint：短整型 float：浮点数

decimal：十进制数字（小数） date：日期型

二、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、delete、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 表名

 查询雇员的编号，姓名，工资

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 sal>1500;

 带有where条件查询语句的基本格式 select *|列名 from 表名 where 条件;

 比较运算符大于：> 小于：= 小于等于：

 限定查询 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 comm>0;  查询谁没有奖金

select empno,ename from emp where comm is null;

 限定查询 and 的使用

 查询工资大于1500，并且可以领取奖金的雇员

SQL> select empno,ename,sal,comm from emp where comm is not null and sal>1500;

 限定查询 or 的使用

 查询工资大于1500和可以领取奖金的雇员

select empno,ename,sal,comm from emp where sal>1500 or comm is not null;  查询没有奖金的雇员

select empno,ename,comm from emp where comm=0 or comm is null;

 限定查询使用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 sal

select empno,ename,hiredate from emp where hiredate between\'1-1月 1981\' and \'31-12月 1981\'; 注：日期格式日-月年，要匹配上

 限定查询字符串的比较

 查询姓名是\'SMITH\'员工的所有信息 select * from emp where ename=\'SMITH\'; select * from emp where ename=\'smith\'; 注：列值区分大小写

 限定查询 in的使用

 查询出编号7369,7499,7521的雇员的具体信息

select * from emp where empno=7369 or empno=7499 or empno=7521; select * from emp where empno in(7369,7499,7521);

 限定查询 not in的使用

 查询出雇员编号不是369,7499,7521的雇员的具体信息 select * from emp where empno not in(7369,7499,7521);

 限定查询 like的使用

 查询雇员的名字第二个字符是M的雇员信息 select * from emp where ename like\'_M%\'; 注：_匹配一个字符，%匹配0个多个字母（前缀或后缀的代表）  查询1982年入职的所有雇员的信息

select * from emp where hiredate like\'%82\';  查询工资中包含5的雇员信息

select * from emp sal where like\'%5%\';

 对结果排序-Oracle By  查询员工工资大于1500员工的信息，按工资排序 select * from emp where sal>1500 order by sal;  查询工资大于1500员工的信息，按工资降序，按雇佣日期升序排序 select * from emp where sal>1500 order by sal desc,hiredate asc; 注：ASC升序，DESC降序，默认ASC。

ascending [ə\'sendiŋ]上升的，降序排列（descend [di\'send] 的缩写）；

三、ORACLE标量函数和算数运算

字符函数的使用

 转换为大写字母

select upper(\'smith\') from dual; 注：dual是公共表。upper [\'?p?] 上面的，上部的

 转换为小写字母

select lower(\'SMITH\') from dual; 注：lower 放下

 每个单词的字母变成大写，其余字母小写 select initcap(\'hello world\') from dual

 串连接(concat):可以使用\"||\"进行串连接 select concat(\'HELLO\',\'WORLD\') from dual; select \'HELLO\'||\'WORLD\' from dual;

 求子串(SUBSTR) select substr(ename,1,3) from emp;

5 注：含义为截取ename字段从第一个字符开始，总共三个字符

 求长度(LENGTH) select 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.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) from dual; TRUNC(789.536,2) ----------------

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 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月 -12

 求出给定日期范围的月数(months_between) select empno,ename,months_between(sysdate,hiredate) from emp;

 下一个给定的星期是那个日期(next_day) select next_day(sysdate,\'星期一\') from dual; 注：表示在当前时间的前提下，下个礼拜一的具体日期

 求出给定日期所在月份的最后一天日期(last_day)

7 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,\'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 注：把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

 算数运算

 查询每个员工的年收入

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; 注：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 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的别名

关联查询-多表关联

四、 查询出每个雇员的姓名，工作，雇员的直接上级领导，以及部门名称 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; 注：(+)在左边，表示右连接，会列出所有右表中出现但是没有在左表中出现的行。

 查询雇员的编号，姓名及其领导的编号，将没有领导的员工也列出来 select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno(+); 注：(+)在右边表示左连接，会列出左表中出现但没有在右表中出现的行。

 交叉连接(cro join)：用来长生笛卡尔积 select * from emp cro join dept;

 自然连接(nutural join)：自动进行关联字段的匹配 select * from emp natural join 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(expreion) 求和 max(expreion) 求最大值

10 min(expreion) 求最小值 avg(expreion) 求平均数 count(expreion) 统计记录数

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) 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 条件...group by 列名...order by 列名...

 查询每个部门的人数

select deptno,count(*) from emp group by deptno; 规则：如果在select字句中，有不在列函数中的列，则该列一定要出现在group by之后。

分组查询having字句的使用

 显示出平均工资大于2000的部门编号和平均工资 select deptno,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 having avg(sal)>2000 order by avg(sal);

六、ORACLE_子查询

在where子句中使用子查询

 查询工资比7654雇员工资高的全部雇员信息

select * from emp where sal>(select sal from emp where empno=7654);

12 子查询-in的使用

 查询和smith或jones在同一部门，同一职位工作的员工

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：只要大于子查询中的任何一个值即可 select * from emp where sal>any (select min(sal) from emp group by deptno);



select empno from emp where sal

子查询all的使用

 >all：比最大的值大

select * from emp where sal>all

(select min(sal) from emp group by deptno); 注：select min(sal) from emp group by deptno结果为

MIN(SAL) ----------

950

800

1300 是每个部门当中的最少工资。



七、ORACLE_表的更新操作

 创建表副本

create table myemp as select * from emp;

 插入  到表中 insert的语法

insert into 表名(字段名1,字段名2,......) values(值1,值2......)

 为myemp中增加一条记录 insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7899,\'张三\',\'清洁工\',\'7369\',\'14-2月-2005\',9000,300,40); 注：给定的值要和指定的字段数一致。

1.如果全部字段都插入值的话，那字段名可以省略。即：

insert into 表名 values(值1,值2,.......); 2.如果只是部分字段插入值的话，字段名又想省略的话，那么，其余没有数据插入的字段用null填补。

 to_date：字符型转换成日期型

 批量插入记录格式：

insert into 表名

select 字段列表 from 表名 where 条件

注：insert表和select表列数等都一致例如：

create table test as select * from emp; insert into test select * from emp;

 修改记录

update表名set要修改的字段=新值，要修改的字段=新值，...where条件

 将雇员号为7896的雇员的奖金修改为100 update myemp set comm=100 where empno=7896; 注：执行修改操作时，一定要使用where来指定修改的条件，否则会改变表中的所有记录。

 删除记录

delete from 表名 where 条件

 删除雇员编号为7896的雇员信息 delete from myemp where empno=7896; 注：执行delete操作时，一定要使用where来指定修改的条件，否则会删除表中所有的记录。

 Oralce的事务处理

commit：提交事务 rollback：回滚操作

注：操作一旦执行了commit操作，就再也不能回滚操作了。

八、ORACLE_表与视图的管理

 oracle中的常见数据类型

varchar、varchar2：表示一个字符串，有长度限制，为255 number：

number(n)：表示一个整数，数字的长度是n，可以使用int number(n,m)：表示一个小数，数字的长度为n，整数长度m-n，可以使用float date：表示日期类型，日期要按照标准格式日期存放。 clob：大对象，表示大文本数据类型，可存放4G。

blob：大对象，表示二进制数据，最大可以存放4G，如电影，图片、歌曲

 创建表语法：

create table 表名( 字段名称1 字段类型[default 默认值], 字段名称1 字段类型[default 默认值], ............字段名称1 字段类型[default 默认值], );

 复制表

create table 表名 as(子查询); 注：当子查询不成立时，如果1=2，则只复制表的结果，不复制表的数据。

 删除表语法：

drop table 表名;

 修改表结构

 增加一列

alter table 表名 add(列名称列数据类型 default 默认值)

 删除列

15 alter table 表名 drop column 列名称;

 修改列的数据类型

alter table 表名称 modify(列的名称列的类型 default 默认值); 注：1.如果是更改数据的长度，则要求更改时，长度不能小于当前表中数据所具有的最大长度。

2.如果是更改数据类型，则要求更改时，该列的所有记录值都为空。

 修改表结构

 为表重命名

rename 旧表名 to 新表名; 注：这是oracle特有的操作

 将myemp表改名为iemp SQL> rename myemp to iemp; 表已重命名。  截断表

truncate table 表名; 意义：清空表中的所有数据，并且立即释放资源，该操作是不可回滚。

 约束-约束的分类

主键约束：表示一个唯一的标识，本身不能为空唯一约束：列值不允许重复

检查约束：检查一个列的内容是否合法

非空约束：不能为空值，如用户不能为空(no null) 外键约束：在两张表中进行约束的操作

 主键约束(primary key) 主键约束一般在id上使用，而且本身已经默认了不能为空，主键约束可以在建表的时候指定 create table person( pid varchar2(18) primary key, name varchar(30), age number(3), sex varchar2(2) default\'男\' );

使用constraint指定

constraint [kən\'streint] 约束；强制 create table person( pid varchar2(18), name varchar(30), age number(3), sex varchar2(2) default\'男\'

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 number(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 视图名称

注：当删除视图所在的表时，则视图也不能被使用

 序列

创建序列语法：sequence [\'si:kwəns] create sequence 序列名称; create sequence myseq; 序列的操作：

nextval：取得序列的下一个内容 currval：取得当前序列的内容

 创建表验证序列的操作

create table testseq(next number,curr number); insert into testseq values(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，循环两次。

学习oracle数据库的总结(图文整理)（定稿）

《ORACLE数据库学习总结.doc》

将本文的Word文档下载到电脑，方便收藏和打印

推荐度：

点击下载文档

ORACLE数据库学习总结

相关推荐