数据库管理系统——Oracle

标签：管理系统 -- 数据库查询 emp 表名 Oracle where select

学习视频：01-day01-oracle-数据库相关概念介绍_哔哩哔哩_bilibili

Oracle管理系统的介绍

Oracle数据库的数据管理（增删改查）

Oracle账户管理

Oracle二维表管理

Oracle是市场上目前很流行的大型数据库，适用于大型项目的数据存储。合理的管理数据的存储和读取。

软件的安装和卸载

下载Oracel XE，next即可。

下载PLSql：第三方提供的客户端插件

卸载Oracel XE（注册表要卸载干净）

软件的安装和卸载记得百度，解决问题的能力很重要。

软件的使用

验证安装成功

安装目录：C:\xxx\xxx\xxx\18.0.0\

密码：123456

开始-->sql命令黑窗口-->进行如下操作

验证成功后我们就开始用它了~

用之前介绍一下

数据库管理系统+自带库，数据库管理系统只有一个，数据库是多个的，每个库都有自己的名字，SID是数据库的唯一标识符。Oracel XE版本默认安装数据库库名为XE。

软件服务介绍

服务相当于是程序在操作系统的平台上运行的方法，须开启服务才能使用。

举例：双击登录扣扣（开启服务，监听数据），退出扣扣（关闭服务）

软件目录结构

ordata:数据库的相关信息

product:数据库管理系统的相关信息

bin目录:数据库管理系统软件的启动目录（sql plus）

jdbc目录:数据库和Java连接所需的jar包

Network:系统所需网络配置目录

log目录：Oracle异常日志信息存储目录

软件体系架构

Oracle是基于C/S架构的交互流程

Oracle客户端（位置地域不限，需IP、端口号、协议、库名）-->Oracle服务器-->数据库

我们在浏览器定位某个资源或者使用扣扣网页版，都需要输入需IP、端口号、协议、库名

在使用客户端时只需要用户名和密码即可，为什么呢？

软件原理：本地网络服务配置（客户端）和监听配置（服务器）

在连接监听配置（服务器）时报错，注意监听配置文件

Oracle客户端会自动加载其中的文件内容，连接配置好的数据库。这个在本地网络服务配置C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora文件里，该文件存储的是键值对。

plsql第三方提供的客户端插件（相当于数据库应用系统）

OracleXE自带账户：XE

账户名

system：系统账户（数据库管理系统的管理员使用的）

sys：超级用户（专门给Oracle工程师使用的）

区别：sys账户比system账户的权限要多。

注意：OracleXE版本安装好后，不自带普通用户的。

Oracle 11g和Oracle 1og自带一个普通用户scott，默认密码是tiger，并给该用户自带了四张二维表数据，我们需要创建一个普通用户来练习SQL语句。

Oracle新建用户，必须使用System账户进行用户创建

1.打开plsql，使用system账户登录，注意：登录身份选择sysdba，不要选择normal。

2.新建sql命令窗口

解决方法：https://www.cnblogs.com/fzj16888/p/5538137.htm

上面好像只能使用C##解决，更改数据库容器我没有成功，哭。

在oralce数据库安装完成之后，没有scott用户以及练习表，如何建立用户以及系统提供的表呢？ <https://www.cnblogs.com/kyeup/p/9286681.html>

在oracle下载的文件包里就可以找到

oracle\product\10.2.0\db_1\RDBMS\ADMIN\scott.sql（前面不用看，直接在电脑文件那搜索RDBMS，然后跟着目录向下找，拷贝到桌面，在PL\SQL中导入表。我成功啦~）

---------------------------------------------------------------------------------------------------------------------------------

--Oracle学习记录
--创建用户用户名 identified by 密码
create user scott identified by 123456
--给用户赋予权限
  --赋予数据库登录连接权限
  grant connect to scott;
  --赋予资源操作权限
  grant resource to scott;
--切换到scott用户登录
--在plsql的工具中选择导入表--sql导入--点击浏览选择桌面文件
--scott账户下导入4张表：emp员工信息表、dept部门表、salegrade薪资等级表、bonus津贴表

忘记Oracle账户的用户密码

com打开window命令窗口

若出现sqlplus不是内部命令的错误，是因为Oracle的环境变量配置有问题。

为什么要配置环境变量呢？为了懒

配环境变量是为了减少我们在cmd命令窗口需要写的文件路径。

下面开始练习操作数据库里里面的数据啦~

结构化数据库操作数据库语言：SQL

SQL是一种结构化查询语言，是一种专门用来与数据库通信的语言。它与其他（英语、Java等）语言不一样，设计SQL的目的是很好地完成一项任务，即提供一种从数据库中读写数据的简单有效的方法。SQL不是某个特定的DBMS专有的语言，事实上任意两个DBMS实现的SQL都不完全相同。

DQL（查询）:select（单表、多表、子）

DML（操作）：insert、update、delete

DDL（定义）：creat、alter、drop

DCL（控制）：grant、revoke

TCL（事物控制）：SAVEPOINT、ROLLBACK、SETTRANSACTION、COMMIT

数据操作语言针对表中的数据，而数据定义语言针对数据库对象（表、索引、视图、触发器、存储过程、函数、表空间）

select

--多条SQL语句必须以分号分隔.
--SQL语句不区分大小写，但是许多SQL开发人员喜欢对所有SQL关键字使用大小写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。
--select语句从表中检索一个或多个数据列，它的用途是从一个或多个表中检索信息。
--单表查询：想选择什么，从什么地方选择
--查询表的所有数据使用通配符*
--一般不建议使用通配符,会降低检索和应用程序的性能，但是当不明确列名时，使用通配符能检索出未知的列名。
select * from 表名;
--查询表中指定字段的数据
--在检索多个列时，须在字段名之间加逗号
select 字段1、字段2... from 表名;
--给查询结果中的字段使用别名
select 字段1 别名、字段2 别名... from 表名;
select 字段1 "别名"、字段2 "别名"... from 表名;
select 字段1 as 别名、字段2 as 别名... from 表名;
--as关键字可以省略不写，别名中没有特殊字符双引号也可以省略不写
--连接符||
select 字段||’的姓名是’字段 as 信息 from 表明
--信息是最后数据查询出来的字段名
select 字段||’的姓名是’字段,字段名... 信息 from 表明
--去除重复，distinct关键字时不能部分使用，它应用于所有列而不仅是前置它的列
select distinct 字段 from 表明
--单字段排序：desc降序、asc升序（默认）
select * from 表名 order by 字段名 desc
--多字段排序：先按照字段1排序，如果字段1的值相同，则按照字段2排序，依次类推。
select * from 表名 order by 字段名1，字段名2 desc
--字段的逻辑运算，这些进行逻辑运算后显示的结果并没有改变数据库中的数据。
select 字段名1、字段名2*2+1000 from 表名
--数据表示：从上述输出可以看到，sql语句一般返回原始的、无格式的数据。数据的格式化是一个表示问题，而不是一个检索问题。因此，表示一般在显示该数据的应用程序中规定。
--这些关键字都可以混用
--使用where子句查询筛选:使用运算符=,>,<,>=,<=,<>,
--查询**的个人信息
select * from 表名 where 字段名='**'
--查询**的薪资信息
select 字段名 from 表名 where 字段名='**'
--查询工资大于1000的员工信息
select * from 表名 where 工资>1000
--查看入职日期在81年后的员工信息
select * from 表名 where hiredate>='01-1月-1981' order by hiredate
--Oracle默认的日期格式为日-月-年
--查询有津贴的员工信息（可能存在0.00）
select * from 表名 where 津贴 is not null and 津贴>0
--模糊查询like
--查询姓名中包含s的
select * from 表名 where 姓名 like '%S%'
--查询姓名中以s开头的
select * from 表名 where 姓名 like 'S%'
--查询姓名中以s结尾的
select * from 表名 where 姓名 like '%S'
---查询姓名中第二个字符为A的
select * from 表名 where 姓名 like '_A%'
---查询姓名中含字符为_的,escape可以将指定的字符变为转义字符，而转义字符可以将特殊字符转为普通字符
--_在Oracle中代表特殊字符，表示一个任意字符的占位符，需要将它转换成普通字符
select * from 表名 where 姓名 like '%/_%' escape '/'
--查询工作为1、2、3的员工信息
select * from 表名 where 工作='1' or 工作='2' or 工作='3'
select * from 表名 where 工作 in('1','2','3')
--or和in关键字中，in中只能为一个字段名工作，而or可以不同。
--查询工资在2000-3000之间的员工信息
select * from 表名 where 工资>'2000' and 工资<'3000';
--使用between and 关键字，包含了两头的数据
select * from 表名 where 工资 between 2000 and 3000;
--找出佣金高于薪金60%的员工
select * from emp where comm>sal*0.6;
--找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料
select * from emp where (deptno =10 and job='MANAGER' ) or (deptno =20 and job='CLERK')
--显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序
select 姓名、工作、薪金 from 表名 order by 工作 desc,薪金 desc
--查询工作为1，2且工资大于2500的员工
select * from 表名 (where 工作='1' or 工作='2') and 工资>2500
--and的优先级大于or,故用括号

函数：是Oracle提供的用来进一步修饰或者处理数据的方法

--函数
--单行函数：字符
--查询所有的员工信息，员工姓名显示小写
select lower(姓名) from 表名
--查询所有的员工信息，员工姓名显示大写
select ININTCAP(姓名) from 表名
--可以和字段混用
--单行函数：数值
--伪表：真实存在的表，是为了方便进行数据的验证而临时存在的表，表名叫dual
select * from dual
select abs(-1),ceil(2.2),floor(3.3) from dual
--绝对值，向上取整，向下取整
--日期
select months_between ('01-1月-2020','01-1月-2021') from dual
--多行函数（max、min、avg、sum、count）
--查询员工的最高工资，
select 员工 from 表名 where 工资=（select max(sal) from 表名）
--查询工资有多少员工
select count(*) from 表名
--查询公司有多少工作种类
select count(distinct job) from 表名
--多行函数是对查询的数据进行统计，多行函数不能和普通字段以及单行函数直接混用，除非分组
--转换函数和其他函数
  --to_number()
  --to_char()
  --to_date()
--可以指定显示格式，9表示占位符，例如999,999,会将数字按照三个一组使用逗号隔开
--0也可作占位符，但是如果真实数据位数不足，会使用0作补位
select to_char(123456789,'$999,999,999') from 表名
--$123,456,789
select to_char(123456789,'L999,999,999') from 表名
--￥123,456,789
--其他函数：
--nvl（）：如果字段不为null，则返回该字段的值，如为null则返回新的值
--nvl2():如果字段不为null，则执行处理1，如不为null则执行处理二
--decode（字段名，值1，处理1，值2，处理2...公共处理）
--如果字段的值与decode中的条件值相同则执行对应的处理，如果都没有则执行公共处理
--查询员工的薪水信息（工资+奖金）
select 员工名,岗位,工资+nvl（奖金,0）from 表名
select 员工名,岗位,工资+nvl2（奖金,处理1,处理2）from 表名
select 员工名,岗位,工资+nvl2（奖金,工资+薪金,工资）from 表名
--显示员工职称
select 员工,工作, decode(工作,'manager','经理') from 表名

分组和分组筛选

--分组查询和筛选
--查询不同部门的最高工资
select * from 表名 order by 部门,工资
select 部门,max(工资) from 表名 group by 部门
--查询不同部门的不同工资岗位的人数
select 部门,工资,count(*) from 表名 group by 部门,工资
--查询不同部门的不同工作岗位的人数
select 部门,工作,count(*) from 表名 group by 部门,工作
--查询不同部门的不同工作岗位的并且人数大于1的信息
--这个报错：select 部门,工作,count(*) from 表名 where count(*)>1 group by 部门,工作
--分组筛选：having
select 部门,工作,count(*) from 表名 where group by 部门,工作 having count(*)>1
--这里涉及一个筛选的先后顺序
--where：from-->where-->group by-->select
--在where子句中不允许出现多行函数
--having:from-->group by-->select-->having
--having必须与分组结合使用
--where执行效率比having高，所以在不使用多行函数时，建议使用where
--查询部门号大于10的不同部门的不同工作岗位的人数
select 部门,工作 count(*) from 表名 group by 部门,工作 having 部门>10

增删改

--数据库的增删改&数据备份
--注意：增删改修改的数据SQL语句执行完毕后，不会马上进行数据的写入，还需要手动对数据进行提交，如果数据有问题还可以回滚
insert into 表名() values()
--删除数据
delete from 表名
truncate table 表名
--上面两个都可以删除表中的记录，但是truncate效率高于delete
--删除表
--更新表
update 表名 set 字段名=新的值，字段名=新的值... where 条件
drop table

数据的备份

--数据的备份
  --表级别的全部备份
  create table deptBak as select * from dept
  --备份表只备份了表结构和数据，约束不会备份
  --表级别的部分备份
  create table deptBak as select 字段名... from dept

作业处理以及总结

1.查看字符长度函数 length（字段名）返回字段的字符长度

2.获取当前系统时间 sysdate

3.排序中可以使用别名

多表联合查询

以上筛选都是单表查询，下面来学习多表查询。

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表，联结是利用SQL的select能执行的最重要的操作——利用单条select语句检索出存储在多个表中的数据。最重要的是，联结是一种机制，不是物理实体，它在实际的数据库表中不存在，是根据需要建立，存在于查询的执行当中。能够有效的使用联结表之前，需要了解一下关系表和关系数据库设计的一些基础知识。

关系表

理解关系表可以通过这样一个例子：有一个包含产品目录的数据库表，每种物品要存储的信息包括产品描述和价格以及该产品的供应商信息，假如有同一位供应商提供了多种物品，那该如何存储供应商信息呢？（供应商名、地址、联系方式）

如果将上述信息保存在同一张表种，对于提供了多种物品的同一个供应商就会产生重复此信息，浪费了时间和存储空间；一旦信息发生改变，都需要改动。

关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用值关联。

产品表（表内含供应商信息表主键的字段）

供应商信息表

此时供应商信息表的主键又叫作产品表的外键

外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

SQL92查询方式

--笛卡尔积：由没有联结条件的表关系返回的结果称为笛卡尔积。检索出的行的数目将第一个表中的行数乘以第二表中的行数。
select * from 表1,表2
--这个结果会出现不正确的信息，所以要执行where子句进行等值数据的筛选
--等值连接查询：条件是字段的值相等，字段名可以不同
select * from 表1,表2 where 表1.字段=表2.字段
--建议使用完全限定列名：表名.列名，效率高且不会出错
--可以给表名使用别名
select * from 表1 a,表2 b where a.字段=b.字段
--不等值连接
--查询员工姓名、工作、工作、工资等级
select * from 员工表、工资等级表 where 员工表.sal>=工资.losal and 员工表.sal<=工资.hisal
--等级是根据工资在losal字段和hisal字段区间决定
--自连接：此连接需要的两个表其实是相同的两个表，需要使用别名来进行区分
--查询员工姓名、工资、薪资及上级领导姓名
select * from 表1 a,表1 b where a.领导=b.员工姓名
--外连接,目的是为了显示对应字段没有值的数据
--左外：查询员工姓名、工作、薪资、部门名称及没有部门的员工信息
select * from emp e,dept d where e.deptno=d.deptno(+)
--右外：查询员工姓名、工作、薪资、部门名称及没有员工信息的部门
select * from emp e,dept d where e.deptno(+)=d.deptno

SQL99查询方式

--笛卡尔积：使用cross join关键字
select * from emp cross join dept
--筛选
--自然连接：底层先笛卡尔积，然后按照同名同值字段自动进行等值筛选，无法进行92方法里面的等值连接筛选情况。
select * from emp natural join dept
--using:指明使用指定的字段对联合查询的结果进行等值筛选，该字段必须是两表的同名等值字段
select * from emp inner join dept using(deptno...)
--使用on关键字进行自定义连接条件筛选
--SQL语句的可读性很重要，关键字要各司其职,就像下面，on进行联结，where筛选
select * from emp inner join dept on emp.deptno=dept.deptno where sal>2000
不建议select * from emp inner join dept on emp.deptno=dept.deptno and sal>2000
--inner可以省略不写
--外连接
--左外
select * from emp left outer join dept on emp.deptno=dept.deptno
--右外
select * from emp right outer join dept on emp.deptno=dept.deptno
--全外
select * from emp full outer join dept on e1.deptno=dept.deptno
--outer可以省略不写
--自连接：此连接需要的两个表其实是相同的两个表，需要使用别名来进行区分
--查询员工姓名、工资、薪资及上级领导姓名
select * from emp e1 inner join emp e2 on e1.mgr=e2.empno

SQL92和SQL99实现三表联合查询

--完成三表联合查询
--SQL92实现
--查询员工信息及部门名称及所在城市名称
select * from emp e,dept d,city c where e.deptno=d.deptno and d.loc=c.cid
--查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
select *
from emp e,dept d,city c
where (e.deptno=d.deptno and d.loc=c.cid and sal>2000) or (e.deptno=d.deptno and d.loc=c.cid and comm is not null)
--SQL92易于书写，难于阅读（语句结构不清晰）
--结论
select 内容（别名，连接符，去除重复，函数，逻辑运算）
from 表名
where 条件（连接、普通筛选、where子句关键字）
group by 分组字段
having 多行函数筛选
order by 排序字段
--SQL99实现
--查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
select *
from emp e
inner join dept d
on e.deptno=d.deptno
inner join city c
on d.loc=c.cid
where e.sal>2000 or e.comm is null
order by 字段
--难于书写，易于阅读
--总结
select
inner join
on
where
group by
having
order by

子查询

--单行字查询，筛选条件不明确需要执行一次查询，并且查询结果只有一个数据
--查询所有比雇员1工资高的员工信息
select * from emp where sal>（select sal from emp where sal='雇员1'）
--即嵌套在查询的查询
--多行字查询:一个字段n个值
all 所有
any 任意
in任意存在，相当于=any
not in 都不存在
--查询工资高于任意一个clerr的所有员工信息
select * from emp where sal>(select min(sal)from emp where job='clerr')
select * from emp where sal>any (select sal from emp where job='clerr')
--查询工资高于所有s的员工信息
select * from emp where sal >(select (max)sal from emp where job='s')
select * from emp where sal all>(select sal from emp where job='s')
--查询部门20中同部门10的雇员工资一样的雇员信息
select job from emp where deptno=10
select * from emp where (job='m' or job='p' or job='c') and deptno = 20
select * from emp where job in (select job from emp where deptno=10) and deptno = 20

账户管理

角色：一系列权限的集合

权限：具备某类事物的操作的能力

创建账户

-使用system账户，并使用dba身份登录

-create user tian identified by 123456

维护账户

-赋予权限

-grant connect to tian --给用户赋予登录权限

-grant resource to tian --给用户资源操作权限

-grant dba to tian --给用户赋予dba权限

-select * from scott.emp --查看其他用户的表使用用户名.表名

-revoke dba from tian --删除权限或者用户名

删除账户

-drop user tian

二维表管理

二维表创建

学生表

create table student（

sno number（10）primary key not null,

sname varchar2(100) chack(),

sage number(3) chack(sage>0 and sage<150),

ssex char(4),

sfav varchar2(500),

sbirth date,

sqq varchar2(30) unique

cno number(10) reference clazz（cno）

）

班级表

create table clazz(

cno number(10) primary key,

cname varchar2(100) not null

)

添加测试数据

insert into student values (1,'',''...'01-1月-1986')

insert into student values

(2,'',''...to_date('01-月-1986','yyyy-mm-dd'))

数据类型

数值类型number：number（10，2）：总长度为10，小数点为2

varchar2（a）：字符最大长度为a，实际存储内存长度是根据字符大小来分配，动态分配存储空间，节省空间。

char（a）：不管字符数据长度是多大，直接开辟a大小的空间存储数据，存储效率高于varchar2。

date（）：

主键约束

-直接在创建表的字段名后使用primary key

-在创建表语句最后面加constraints pk_表名_字段名 primary key(字段名)

-在创建表后使用

alter table 表名 add constraints pk_表名_字段名 primary key(字段名)

非空约束

not null

检查约束

check(字段名 is not null)

check()

唯一约束

unique：例如身份证号、扣扣号等

外键约束：在子表中插入数据在父表中不存在，会自动报错。当一张表中的某个字段的值需要依赖另一张表的字段的值，则使用外键约束。使用外键时无法删除父表数据，除非级联删除。

在创建学生表时使用reference

在添加外键约束时,使用on delete cascade,当删除父表数据时，自动删除子表相关所有数据，这样就无法保存级联历史数据。

使用 on delete set null,则删除父表数据时，将子表中的依赖字段值设为null，但是子表依赖字段不能添加非空约束。

总结：删除两个关联表数据时，先解除外键关联，将相关数据删除后再添加关联。

由于第三方插件试用期已过，记录只能止于此啦。