Oracle数据库入门与实践-优快云博客

本文链接：https://blog.youkuaiyun.com/SYJ_1835_NGE/article/details/90739607

Oracle的学习二

Oracle的基本概念和安装

Oracle简介
ORACLE数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器（CLIENT/SERVER）或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了ORACLE知识，便能在各种类型的机器上使用它。
Oracle10g的安装
我的是安装在win xp系统上
注意：
- 输入口令和确认口令，如：orcl，点击下一步，
- 出现“口令管理”界面，点击“口令管理”，输入密码orcl
- 将SCOTT的对勾去掉（解锁scott账户），如下图所示，点击“确定”：
  并设置口令比如 tiger
在win xp系统上测试连接命令：sqlplus system/
客户端连接oracle
- 客户端连接数据库的意思：通过真实主机连接虚拟主机上的Oracle
- 将instantclient_12_1拷贝到你的没有中文路径的位置
- 进入到instantclient_12_1的路径下运行cmd
- 输入命令
  sqlplus system/密码@远程IP:1521/orcl
- 当你无法连接成功时，一定要先检查网络连接。
PLSQL Developer客户端工具的安装
- 安装目录不能有中文和空格，否则连接不上
- 第一次连接要先配置（打开软件不要登录点击取消）
- 从虚拟机中的oracle 安装目录中找到tnsnames.ora拷贝至D盘根目录（或其它位置），编辑此文件。
  
  将红框部分改成自己虚拟主机的IP地址
- PLSQL Developer点取消，Tools->>Preferences
- 设置环境变量
  TNS_ADMIN 为D盘根目录（tnsnames.ora所在目录）
- 关闭软件重新打开(输入密码 Database选择ORCL10)
PLSQL Developer中文乱码问题解决
- 1、查看服务器端编码
  select userenv（language）from dual；我实际查到的结果为：AMERICAN_AMERICA.ZHS16GBK
- 2、执行语句select*from V$NLS_PARAMETERS查看第一行中PARAMETER项中为NLS_LANGUAGE对应的VALUE项中是否和第一步得到的值一样。
  如果不是，需要设置环境变量。
  否则PLSQL客户端使用的编码和服务器端编码不一致，插入中文时就会出现乱码.
- 3、设置环境变量
  计算机->属性->高级系统设置->环境变量->新建设置变量名：NLS_LANG，变量值：第1步查到的值，我的是AMERICAN_AMERICA.ZHS16GBK
- 4、重新启动PLSQL，插入数据正常

Oracle的体系结构

数据库：
database Oracle数据库是数据的物理存储。这就包括（数据文件0RA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。
实例：
一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes）和内存结构（Memory Structures）组成。一个数据库可以有n个实例。
数据文件（dbf）：
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。
表空间：
表空间是0rac1e对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间（称之为system表空间）。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件（datafile）。一个数据文件只能属于一个表空间。

在这里插入图片描述

用户：
用户是在实例下建立的。不同实例中可以建相同名字的用户。
**注意：**表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。
由于orac1e的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！
表空间示意图

在这里插入图片描述

理解1：
Oracle数据库可以创建多个实例，每个实例可以创建多个表空间，每个表空间下可以创建多个用户（同时用户也属于表空间对应的实例）和数据库文件，用户可以创建多个表（每个表随机存储在一个或多个数据库文件中），如下图：
理解2：
理解1MS有误。

实例下有和，授权访问，是管理的，经授权在中创建，随机存储到不同的中。如下图所示：
SCOTT用户

参考SCOTT表结构

https://blog.youkuaiyun.com/SYJ_1835_NGE/article/details/90738962

基本查询

sql简介
结构化查询语言（Structured Query Language）简称SQL（发音：/'es kju:'el/“S-Q-L”），结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
DML（数据库操作语言）：其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
DDL（数据库定义语言）：其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE或DROPTABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
DCL（数据库控制语言）：它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

可能会用到的查询语句

-- 1.查看服务器端编码
select userenv（language）from dual；
-- 2.解锁scott用户并重新设置密码
alter user scott account unlock;
alter user scott identified by tiger;

-- oracle除了起别名使用双引号，其余的全部使用单引号

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

select empno as "员工编号",ename "员工名字",job "职位",mgr "领导编号",hiredate "入职日期",sal "工资",comm "奖金",deptno "部门编号" 
from emp;

2、查询emp表中员工的job信息，并去除重复（distinct）信息
```
select distinct(job) from emp;
```

3、查询emp表中员工的全年的工资总和(sal总和)

select ename "姓名",12*sal as "工资总和" from emp;

4、查询emp表中员工的全年收入总和(sal+comm的总和)

--nvl(comm,0)见到comm为null，替换成后面的值，避免了null值
select ename "姓名",12*sal+nvl(comm,0) as "收入总和" from emp;

5、查询emp表中员工编号，姓名（考察字符串的拼接）

-- 输出格式如下：编号:xxx，姓名：xxx
---- concat拼接方式
---- mysql支持无限打逗号
select concat(concat('编号:',empno),concat('，姓名：',ename)) from emp;

-- =============等价于======================

----Oracle的||方式   相当于+号                                 
select '编号：'||empno||'，姓名：'||ename  from emp;

条件查询和排序

1、查询工资大于1500的员工
```
select * from emp where sal>1500;
```

2、查询工资大于1500并且有奖金的雇员

select * from emp where sal>1500 and comm is not null;   ---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;       ---is null

5、查询员工姓名为smith的员工

select * from emp where ename='SMITH';                   ---oracle对大小写敏感

6、查询工资大于1500但小于3000的全部雇员

---- >=,<=方式
select * from emp where sal>=1500 and sal<=3000;
----  between and方  左开右闭
select * from emp where sal between 1500 and 3000;

7、查询1981-1-1到1981-12-31号入职的雇员（between and）

-- to_date将字符串转化为日期
select * from emp where hiredate between to_date('1981-1-1','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');

8、查询员工编号是7369,7654,7566的员工

---- OR方式
select * from emp where empno='7369' or empno='7654' or empno='7566';
---- IN方式        --IN 操作符允许我们在 WHERE 子句中规定多个值。只要符合一个就可以
select * from emp where empno in('7369','7654','7566');

9、查询雇员姓名是’SMITH’,‘ALLEN’,'WARD’的雇员信息
```
----IN方式
select * from emp where ename in('SMITH','ALLEN','WARD');
```
模糊查询like

% 零或者多个字符

_ 单一任何字符（下划线）

\ 特殊字符

[] 在某一范围内的字符，如[0-9]或者[aeth]

[^] 不在某范围内的字符，如[^0-9]或者[aeth]

后两种，需要Oracle 10g以上使用支持like的正则regexp_like
10、查询所有雇员姓名中第二个字符有‘M’的雇员
```
select * from emp where ename like '_M%';
```
11、查询名字中带有‘M’的雇员
```
select * from emp where ename like '%M%';
```
排序 order by

desc 降序

asc 升序

12、查询雇员的工资进行降序排序

select ename,sal from emp order by sal desc;                  ---升序
select ename,sal from emp order by sal asc;                   ---降序

13、查询雇员的奖金并做降序排序（关于nulls first/nulls last）

nulls first

nulls last

--- 将null值排到后面【nulls last】
select ename "姓名",comm "奖金" from emp order by comm   nulls last;

14、查询雇员的工资做降序排列并且其中奖金部分是升序排序

-- 从左往右查询，工资一样排奖金；在select中order by排在最后面
select  ename "姓名",sal "工资",comm "奖金" from emp order by sal desc,comm asc;

单行函数

伪表，虚表：dual 没有任何的实际意义，只是为了补全Oracle查询语法

字符函数

1、将’smith’转换成大写–关键字:upper

---只有去查询不存在的表才能将其转化为指定类型显示
select upper('smith') from dual;

2、将’SMITH’转换成小写–关键字:lower

select lower('SMITH') from dual;

---2.1将姓名列全部小写显示
select lower(ename) from emp;

3、将’smith’首字母大写–关键字:initcap

select initcap('smith') from dual;

---3.1将姓名列全部首字母大写
select initcap(ename) from emp;

4、将’helloworld’截取字符串成’hello’–关键字substr
```
--【左开右闭】
select substr('helloworld',0,5) from dual;
```
5、获取’hello’的字符串长度–关键字length
```
select length('hello') "长度" from dual;
```
6、将’hello’中的l用x进行替换–关键字replace
```
select replace('hello','l','x') "替换" from dual;
```
数值函数

7、将15.66进行四舍五入（从-2到2）–关键字round

select round(15.66,-2) from dual;    --0  1进位
select round(15.66,-1) from dual;    --20 5进位
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

8、将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

9、对15/3进行求余数–关键字mod
```
select mod(15,3) from dual;
```
日期函数
10、查询系统时间–关键字sysdate
```
select sysdate from dual;
```

11、查询雇员进入公司的周数

select (sysdate-hiredate)/7 from emp;
-- 查询员工的姓名首字母大写和入职的时间
select initcap(ename),hiredate from emp;

12、查询雇员进入公司的月数–关键字months_between

select initcap(ename) "姓名",months_between(sysdate,hiredate)  "入职总月数" from emp;

13、求出三个月后的日期–关键字add_months

select initcap(ename) "姓名",hiredate "入职时间" ,add_months(hiredate,3) "转正时间" from emp;

转换函数

14、将系统日期显示为yyyy-mm-dd hh:mi:ss(去掉补零和24小时显示时间)–关键字to_char

select to_char(sysdate,'yyyyfm-mm-dd hh24:mi:ss') from dual;
----显示成年月日   --2019年05月28日
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月'||to_char(sysdate,'dd')||'日'  from dual;

15、将字符串’1981-1-1’转换成日期类型–关键字to_date
```
select to_date('1981-1-1','yyyy-MM-dd') from dual;
```
通用函数
16、nvl空值的处理函数
```
select nvl(comm,0) from emp;
```

17、nvl2(判断值,空返回值,非空返回值)

-- 三目运算符 第一个条件为null成立返回第三个参数的值，第一个条件不为null成立返回第二个参数的值
select nvl2(comm,'1','2') from emp;

条件表达式

18、查询员工的job内容并转成中文显示

----decode方式
-- 依次转化，如果最后没有加上其他的话，就会出现列表为null
select ename,decode(job,'CLERK','柜员','SALESMAN','销售','MANAGER','经理','其他') from emp; 
----case when then end方式
select ename ,case job when 'CLERK' then '柜员'
                       when 'CLERK' then '柜员'
                       when 'SALESMAN' then '经理'
                       else '其他'
                       end from emp;

多行函数

1、查询所有员工记录数–关键字count
```
select count(ename) from emp;
```
2、查询佣金的总数–（如何查询某个字段的总数量）
```
select count(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 ;
```
分组函数

7、查询部门编号及人数–分组查询–关键字group by

select DEPTNO "部门编号", count(ename) "总人数" from emp group by DEPTNO ;

8、查询每个部门编号及平均工资

select DEPTNO "部门编号", avg(sal) "平均工资" from emp group by DEPTNO ;

9、查询部门名称，部门编号，平均工资

--- oracle如果查询的有普通字段，也必须出现在group by中
select DNAME "部门名称" ,emp.DEPTNO  "部门编号", avg(sal) "平均工资"  from emp,dept where emp.DEPTNO=dept.DEPTNO group by emp.DEPTNO,dept.dname;

-- 起别名
select DNAME "部门名称" ,e.DEPTNO  "部门编号", avg(sal) "平均工资"  from emp e,dept d where e.DEPTNO=d.DEPTNO group by e.DEPTNO,d.dname;

10、查询出部门人数大于5人的部门

-- where 先group by执行(where, group by, having ,order by)===group by之后要先再过滤就要使用having
select DEPTNO "部门编号" ,count(ename) "部门总人数" from emp  group by DEPTNO  having count(ename)>5;

11、查询部门编号，部门名称，平均工资且平均工资大于2000
```
select emp.DEPTNO,dname,avg(sal) from emp,dept  where emp.DEPTNO=dept.DEPTNO
group by emp.DEPTNO,dname having avg(sal)>2000
 
 -- 起别名
select e.DEPTNO,d.dname,avg(sal) from emp e,dept d where e.DEPTNO=d.DEPTNO
group by e.DEPTNO,d.dname having avg(sal)>2000;
```
全部教程是本人自己在学习过程中的总结和练习，都很基础写下来的目的主要是方便自己和他人的复习理解，也消除了笔记写下找不到的缺点。如有需要什么笔记或者文档可以留言。