数据库2020最新全套oracle数据库教程

数据库2020最新全套oracle数据库教程

使用虚拟机安装Oracle_连接模式

net模式：前三位ip不一致
桥接模式：链接局域网里

oracle客户端

下载地址：http://www.winwin7.com/soft/15440.html

oracle安装

Oracle数据库下载安装和卸载简单说明

通过PLSQL连接数据库
安装时路径不能有空格和中文
连接虚拟机数据库时需要关闭防火墙
本地连接：sqlplus system/orcl
远程连接：sqlplus system/orcl@192.168.176.129:1521/orcl

MES：就是你本地命名的名称
ADDRESS：服务器的地址
PROTOCOL：使用的协议，这里是TCP的
PORT：服务器的监听端口号
SERVICE_NAME：服务器服务名称

本地plsql
D:\app\Administrator\product\11.2.0\client_1\network\admin
D:\Program Files (x86)\Oracle\Instant Client\network\admin
配置环境变量

1. 变量名：ORACLE_HOME 变量值：D:\app\Administrator\product\11.2.0
   即：ORACLE_HOME＝$ORACLE_BASE/product/version
2. 变量名：TNS_ADMIN 变量值：E:\PLSQLDeveloper\PLSQL\instantclient_11_2
   即；TNS_ADMIN告诉sqlplus在哪里可以找到tnsnames.ora文件.
3. 变量名：NLS_LANG 变量值： SIMPLIFIED CHINESE_CHINA.ZHS16GBK
   自带：AMERICAN_AMERICA.AL32UTF8
4. 修改Path变量，在后面添加 E:\PLSQLDeveloper\PLSQL\instantclient_11_2

配置oracle监听文件
找到E:\PLSQLDeveloper\PLSQL\instantclient_11_2路径下的tnsnames.ora文件，用编辑器打开（记事本也可以）。
根据自己实际需要进行编辑，如下图所示。ORCL_27是显示的数据库名，HOST后面填的是所要连接的地址。编辑好以后保存。

打开E:\PLSQLDeveloper\PLSQL\PLSQL Developer文件夹下的plsqldev.exe文件，右击，以管理员身份运行。
填入你要连接的数据库的用户名和密码，并在数据库一栏的下拉列表中选择你要连接的数据库。若在此步无法显示数据库名称，则进行下述步骤
点击工具-》首选项，如下图所示。
Oracle主目录名
E:\PLSQLDeveloper\PLSQL\instantclient_11_2\OCI库
E:\PLSQLDeveloper\PLSQL\instantclient_11_2\oci.dll
重新启动就可以啦~

注意事项
配置环境变量时要根据自己的安装路径配置
Oracle主目录名和OCI库要根据自己的安装路径配置

oracle语法函数

更改密码

• select userenv(‘language’) from dual; --AMERICAN_AMERICA.ZHS16GBK
  –解锁scott用户并重新设置密码
  alter user scott account unlock;
  alter user scott identified by tiger;

基本查询

-  查询所有emp中的信息，并用中文进行字段重命名

select * from emp;
select EMPNO as “员工编号”,ename “员工姓名”,job “职位”,mgr “领导编号”,hiredate “入职日期”, sal “工资”, comm “奖金”,deptno “部门编码” from emp;
- 查询emp表中员工的job信息，并去除重复信息
select distinct(job) from emp;
- 查询emp表中员工的全年的工资总和（sal总和）
select ename, sal12 from emp;
- 查询emp表中员工的全年收入总和（sal+comm的总和）
select ename, sal12+nvl(comm,0) from emp; 遇见空值替换
- 查询emp表中员工编号，姓名
–输出格式如下，编号：xxx， 姓名：xxx
-concat拼接效果
select concat(empno,ename) from emp;
select concat(concat(‘编号：’,empno),concat(’,姓名：’,ename)) from emp;
----Oracle||方式
select ‘编号：’ ||empno ||’,姓名:’||ename from emp;
2. 条件查询
–1、查询工资大于1500的员工
select ename ,sal from emp where sal>=1500;
–2、查询工资大于1500并且有奖金的雇员
select ename ,sal,comm from emp where sal>=1500 and comm is not null ;
–3、查询工资大于1500或者有奖金的雇员
select * from emp where sal >=1500 or comm is not null;
–4、查询工资大于1500并且没有奖金的雇员
select * from emp where sal >= 1500 and comm is null;
–4、查询工资大于1500并且或者奖金的雇员
select * from emp where sal >= 1500 or comm is null;
–5、查询员工姓名为smith的员工
select * from emp where ename=‘SMITH’;

范围查询

–1、查询工资大于1500但小于3000的全部雇员
–>=,<=方式
select * from emp where sal >=1500 and sal <=3000;
–between and方式
select * from emp where sal between 1500 and 3000;
–2、查询1981-1-1到1981-12-31入职的员工（between and）
select * from emp where hiredate between to_date(‘1981-1-1’,‘yyyy-MM-dd’) and to_date(‘1981-12-31’,‘yyyy-MM-dd’);
–3、查询员工编号是7369,7654,7566的员工
------or的方式
select * from emp where empno = 7369 or empno =7654 or empno = 7566;
------in的方式
select * from emp where empno in(7369,7654,7566);
–4、查询员工姓名是’SMITH’,‘ALLEN’,'WARD’的员工
select * from emp where ename in (‘SMITH’,‘ALLEN’,‘WARD’);

模糊查询（like）

–1、查询所有雇员名字中第二个字符有’M’的员工
select * from emp where ename like ‘_M%’;
–2、查询所有雇员名字中有’M’的员工
select * from emp where ename like ‘%M%’;
–3、查询雇员编号不是7369的雇员信息
--------<>方式
select * from emp where empno <> 7369;
--------！=方式
select * from emp where empno != 7369;

排序order by

–1、查询员工的工资进行降序排序
select ename,sal from emp order by sal desc;
–2、查询员工奖金并做降序（nulls first /mulls last）
select ename, comm from emp order by comm desc nulls first;
select ename,comm from emp order by comm desc nulls last;
–3、查询员工的工资做降序排列并且其中奖金部分是升序排列
select ename,sal,comm from emp order by sal desc ,comm asc;
6. 单行函数

字符函数

–1、将’smith’转换成大写–关键字：upper
select upper(‘smith’) from dual;
–2、将‘SMITH’转换成小写–关键字：lower
select lower(‘SMITH’) from dual;
select lower(ename) from emp;
–3、将’SMITH’首字母大写–关键字
select initcap(‘smith’) from dual;
select initcap(ename) from emp;
–4、将‘helloworld’截取成hello–关键字（substr）
select substr(‘helloworld’,1,5) from dual;
–5、获取‘hello’的字符长度–length
select length(‘hello’) from dual;
–6、将‘hello’中的1用x进行替换–关键字replace

数值函数

–1、将15.66进行四舍五入（）
select round(15.66,-2) from dual; --0
select round(15.66,-1) from dual;–20
select round(15.66,0) from dual;–16
select round(15.66,1) from dual;–15.7
select round(15.66,2) from dual;–15.66
–2、将15.66进行截断（从-2到2）关键字trunc
select trunc(15.66, -2) from dual;–0
select trunc(15.66,-1) from dual;–10
select trunc(15.66,0) from dual;–15
select trunc(15.66,1) from dual;–15.6
select trunc(15.66,2) from dual;–15.66
–3、对15/3进行进行求余数–关键字mod
select mod(15,3) from dual;

日期查询

–1、获取oracle数据库的系统时间–sysdate
select sysdate from dual;
–2、查询雇员进入公司的周数
select ename, (sysdate-hiredate)/7 from emp;
–3、查询雇员进入公司的月数–关键字months_between
select ename,months_between(sysdate,hiredate) from emp;
–4、求出三个月后的日期–关键字add_months
select add_months(hiredate,3),hiredate from emp;

转换函数

–1、将系统日期显示为yyyy-mm-dd hh:mi:ss(去掉补零和24小时显示时间)–关键词to_char转字符串
select to_char(sysdate,‘yyyyfm-mm-dd hh24:mi:ss’) from dual;
–2、显示成年月日–转字符串
select to_char(sysdate，‘yyyy’)|| ‘年’||to_char(sysdate,‘mm’) ||‘月’ || to_char(sysdate,‘dd’) || ‘日’ from dual;
–3、将字符串转‘1981-1-1’换成日期类型–关键词to_date
select to_date(‘1981-1-1’,‘yyyy-mm-dd’) from dual;
select to_number

空值的处理函数

–1、空值的处理函数
select ename, nvl(comm,0) from emp ;
–2、nvl2(判断值，非空返回值，空返回值)
select nvl2(null,‘1’,‘2’) from dual;–2
select nvl2(‘xxx’,‘1’,‘2’) from dual;–1

条件表达式

–1、查询员工的job内容并转成中文显示
-------------decode方式
select * from emp;
select ename,job,decode(job,‘CLERK’,‘柜员’，‘SALESMAN’,‘销售’，‘MANAGER’,‘经理’,‘ANALYST’,‘工程师’，‘其他’) from emp;
--------------case when then end
select ename,case job when ‘CLERK’ then ‘柜员’
when ‘SALESMAN’ then ‘销售’
when ‘MANAGER’ then ‘经理’
when ‘ANALYST’ then ‘工程师’
else ‘其他’ end from emp;

聚合函数

1、查询所有员工记录数–关键词count
select count(*) from emp;
2、查询佣金的总数
select sum(comm) from emp;
3、查询最低工资(min)
select min(sal) from emp;
4、查询最高工资(max)
select max(sal) from emp;
5、查询平均工资(avg)
select avg(sal) from emp;
6、查询20号部门的员工工资总和
select sum(sal) from emp where deptno =20;

分组函数

-1、查询部门编号及人数—分组查询关键字group
select deptno,count() from emp group by deptno ;
–2、查询每个部门编号及平均工资
select deptno ,avg(sal) from emp group by deptno;
–3、查询部门名称，部门编号，平均工资
select dname,emp.deptno,avg (sal)from emp,dept where dept.deptno = emp.deptno group by emp.deptno,dname;
–普通字段必须出现在group by里
–4、查询部门人数大于5人的部门
select dept.dname ,count() from dept,emp where emp.deptno= dept.deptno group by emp.deptno ,dname having count(*) >5;
–5、查询部门编号，部门名称，平均工资且平均工资大于2000
select emp.deptno,dept.dname,avg(sal) from emp,dept
where emp.deptno = dept.deptno
group by emp.deptno,dept.dname
having avg(sal) > 2000;

多表关联查询

–查询员工编号，姓名，部门编号，部门名称，部门地址，中文显示员工工资等级，以及领导编号，姓名，部门编号，部门名称，部门地址。
select table_name from user_tables;
select * from dept;
select * from emp;
select * from salgrade;
select e1.empno,e1.ename,e1.deptno ,d1.dname ,d1.loc,decode(s1.grade,1,’ 一级 ',2,‘二级’,3,‘三级’,4,‘四级’) “等级”,e2.empno,e2.ename,e2.deptno,d2.dname,d2.loc
from emp e1,emp e2,dept d1, dept d2,salgrade s1,salgrade s2
where e1.mgr = e2.empno and e1.deptno = d1.deptno and e2.deptno = d2.deptno and e1.sal between s1.losal and s1.hisal and e2.sal between s2.losal and s2.hisal

外连接

–查询员工编号，姓名，领导编号，领导姓名（包括没领到的）
----left join on的方式
select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
select e1.empno, e1.ename ,e1.mgr, e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
–====orcle(+)的方式
select e1.empno ,e1.ename, e1.mgr, e2.ename from emp e1,emp e2 where e1.empno = e2.mgr(+);

子查询

–、查询比雇员7654工资高的，同事从事和7788一样的工作
select * from emp
where emp.sal >(select sal from emp where empno =7654)
and emp.job =(select job from emp where empno =7788);

—2、查询每个部门最低工资及最低工资的部门及员工
select e1.minsal,dept.dname,emp.ename
from (select min(sal) minsal，deptno from emp group by deptno) e1,emp,dept
where e1.deptno = emp.deptno and e1.minsal = emp.sal and e1.deptno = dept.deptno;

–找出员工表中工资最高的前三名
select * from (select rownum ,emp.* from emp order by sal desc) where rownum <= 3;
–找到员工表中薪水大于本部门平均工资的所有员工
select e1.avgsal ,emp.* from (select avg(sal) avgsal,deptno from emp group by deptno)e1 ,emp
where e1.avgsal < emp.sal and e1.deptno = emp.deptno;

–统计每年入职员工的个数(行列转换)
select sum(e.hcount)“总计”,sum(decode(hdate,‘1980’,hcount))“1980”,avg(decode(hdate,‘1981’,hcount))“1981”,min(decode(hdate,‘1982’,hcount))“1982”,max(decode(hdate,‘1987’,hcount))“1987” from (select to_char(hiredate,‘yyyy’)hdate,count(*)hcount from emp group by to_char(hiredate,‘yyyy’) )e

分页查询

–1、查询员工表，将员工工资进行降序查询，并进行分页取出第一页，一页三条记录
select e2.* from (select rownum r, e1.* from (select * from emp order by sal desc)e1 )e2
where r > 3 and r < 7;

集合运算

–1、查询工资大于1200并且job是salesman（intersect）
select * from emp where sal > 1200
intersect
select * from emp where job = ‘SALESMAN’;
–2、查询工资大于1200或者job是SALESMAN（all取消去重）
select * from emp where sal > 1200
union
select * from emp where job = ‘SALESMAN’
–3、求工资大于1200和job是SALESMAN的差集（minus）
select * from emp where sal > 1200
minus
select * from emp where job =‘SALESMAN’
–===================================exists/not exists
/*
select … where exists (查询语句)
exists:当前查询结果不为null，返回ture
当前查询结果为null，返回false
*/
–1、查询出有员工的部门
select * from bonus;
select * from dept;
select * from dept where exists (select * from emp where dept.deptno = emp.deptno)
select * from dept where not exists (select * from emp where dept.deptno = emp.deptno)
–查询不是领导的员工
select * from emp e1 where not exists(select * from emp e2 where e1.empno = e2.mgr )
DDL

创建数据库

–1、创建itcast0001表空间
create tablespace itcase297 --创建表空间
datafile ‘e:/itcase297.dbf’ --保存文件位置
size 100m --默认文件大小
autoextend on --是否可拓展（on/off 是/否）
next 10m; --每一次拓展多少
–2、创建itcase297用户
create user itcase297 identified by itcase297 --创建用户、设置密码
default tablespace itcase297 --默认表空间
–3、为itcase297赋予dba权限
grant dba to itcase297;
–4、删除表空间、并将物理文件一并删除
drop tablespace itcase297 including contents and datafiles;
drop user itcase297 cascade ;

创建表

–1、创建person表、字段为pid、pname
create table personit(
pid number,
pname varchar(50)
)
select * from person;
–=====================orcl数据表中的增删改查
/*
oracle 中事务需要手动commit提交
*/

–1、为person表添加一条数据
insert into person values (1,‘cgx’);
–2、修改person中表中的一条数据
update person set pid = ‘zhang’ where pid = 1;
–3、删除person表中的一条数据
delete from person where pid = 1;
commit;

修改列表属性

–1、给person增加sex性别列，类型为number（1）
alter table person add sex number(1);
–2、修改person表列sex的类型为char
alter table person modify sex char(1)
–3、修改person的sex列名为gender
alter table person rename column sex to gender;
select * from person
–4、删除person表中的gender列
alter table person drop column gender;
–5、删除person表中的所有数据
delete from person where 1=1;
–6、摧毁person表（truncate table 表名）
/直接摧毁表结构后重建表，比delete快，单没法按照条件删除/
truncate table person;
约束
drop table person;
–1、创建person表，pia为主键，paname，gender（主键约束primary key）特性：非空、唯一
drop table person;
–primary key方式
create table person(
pid number primary key,
pname varchar2(50),
gender number
)

insert into person values(1,‘cgx’,1);
commit;

–constraint 主键名 primary key（字段），自定义方式
create table person(
pid number,
pname varchar(50),
gender number,
constraint PK_PID primary key(pid)
)
–2、创建person表，pame非空，gender(not null)
create table person(
pid number,
pname varchar(50) not null,
gender number
)
insert into person(pid,gender) values (1,0);
commit;

直接输入1次#，并按下space后，将生成1级标题。
输入2次#，并按下space后，将生成2级标题。
以此类推，我们支持6级标题。有助于使用TOC语法后生成一个完美的目录。

功能快捷键

撤销：Ctrl/Command + Z
重做：Ctrl/Command + Y
加粗：Ctrl/Command + B
斜体：Ctrl/Command + I
标题：Ctrl/Command + Shift + H
无序列表：Ctrl/Command + Shift + U
有序列表：Ctrl/Command + Shift + O
检查列表：Ctrl/Command + Shift + C
插入代码：Ctrl/Command + Shift + K
插入链接：Ctrl/Command + Shift + L
插入图片：Ctrl/Command + Shift + G
查找：Ctrl/Command + F
替换：Ctrl/Command + G

如何改变文本的样式

强调文本 强调文本

加粗文本 加粗文本

标记文本

删除文本

引用文本

H₂O is是液体。

2¹⁰ 运算结果是 1024.

插入链接与图片

链接: link.

图片:

带尺寸的图片:

居中的图片:

居中并且带尺寸的图片:

当然，我们为了让用户更加便捷，我们增加了图片拖拽功能。

如何插入一段漂亮的代码片

去博客设置页面，选择一款你喜欢的代码片高亮样式，下面展示同样高亮的 代码片.

// An highlighted block
var foo = 'bar';

生成一个适合你的列表

• 项目
  • 项目
    • 项目

1. 项目1
2. 项目2
3. 项目3

• 计划任务
• 完成任务

创建一个表格

一个简单的表格是这么创建的：

项目	Value
电脑	$1600
手机	$12
导管	$1

设定内容居中、居左、居右

使用:---------:居中
使用:----------居左
使用----------:居右

第一列	第二列	第三列
第一列文本居中	第二列文本居右	第三列文本居左

SmartyPants

SmartyPants将ASCII标点字符转换为“智能”印刷标点HTML实体。例如：

TYPE	ASCII	HTML
Single backticks	'Isn't this fun?'	‘Isn’t this fun?’
Quotes	"Isn't this fun?"	“Isn’t this fun?”
Dashes	-- is en-dash, --- is em-dash	– is en-dash, — is em-dash

创建一个自定义列表

Markdown

Text-to- HTML conversion tool

Authors

John

Luke

如何创建一个注脚

一个具有注脚的文本。¹

注释也是必不可少的

Markdown将文本转换为 HTML。

KaTeX数学公式

您可以使用渲染LaTeX数学表达式 KaTeX:

Gamma公式展示 $\Gamma (n)=(n-1)!\forall n\in \mathbb{N}$ 是通过欧拉积分

$$\Gamma (z)={\int }_0^{\infty }{t}^{z-1}{e}^{-t}dt.$$

你可以找到更多关于的信息 LaTeX 数学表达式here.

新的甘特图功能，丰富你的文章

• 关于 甘特图 语法，参考 这儿,

UML 图表

可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图：

这将产生一个流程图。:

• 关于 Mermaid 语法，参考 这儿,

FLowchart流程图

我们依旧会支持flowchart的流程图：

• 关于 Flowchart流程图 语法，参考 这儿.

导出与导入

导出

如果你想尝试使用此编辑器, 你可以在此篇文章任意编辑。当你完成了一篇文章的写作, 在上方工具栏找到 文章导出 ，生成一个.md文件或者.html文件进行本地保存。

导入

如果你想加载一篇你写过的.md文件，在上方工具栏可以选择导入功能进行对应扩展名的文件导入，
继续你的创作。

---

1. 注脚的解释 ↩︎