Oracle数据库入门

Oracle基础概念

数据库:
数据的仓库

以前存数据:内存,文件
内存: int num = 10; 问题,临时
文件: xml/json 解析/操作起来麻烦

永久存放,方便解析/处理-> 数据库
主流关系型数据库:

• Oracle

产品免费,服务收费,强大稳定性,安全性

• MySQL

MySQL AB开源,2008被SUN公司收购,然后和java打包2009年卖给了Oracle!有社区版和企业版!产品免费,服务收费!

• SqlServer

微软开发,强大的图形化工具,方便使用

• db2

IBM,多个操作系统,多个硬件

Oracle版本:
oracle8i/9i:internet,开始走向网络,之前单机
oracle10g: grid网格计算,提高访问速度,避免舍近求远问题!
oracle 12c: cloud,云开发

oracle11g:目前主流

不同版本只有部署运维差异较大,其他没有很大区别!

数据库服务器:
将数据库部署在服务器上!
Oracle服务器:

1. 基于关系型的数据库(RDBMS)
   关系型->二维表:Oracle/MySQL/SqlServer/DB2…
   非关系数据库: NoSQL:not noly sql, redis/mongodb: 基于key value结构
2. 组成结构:
   一个PGA对应一个客户端
   两阶段提交:减少数据库访问次数!
   

数据库安装

Oracle 安装过程

1. 官网下载安装包
2. 然后解压后，点击setup启动安装
   
   

3. 配置数据库密码
   
   

4. 继续安装
   

5. 等待加载
   

Oracle数据库不像其他数据库一样，Oracle只有一个实例!

6. 重点输入密码，继续安装
   

7. 安装成功
   

8. 验证
   通过刚刚我们未锁定的账户名和密码进行连接数据库！
   

连接Oracle

• 方式一

-- 打开cmd窗口
-- 1.输入sqlplus回车
sqlplus
-- 输入用户名
scott
-- 输入密码
123456

• 方式二
  

-- 找到sqlplus窗口
-- 然后操作和上述步骤一样
-- 输入用户名(方案名)
-- 输入密码

查看当前用户

show user;

方案就是用户,用户就是方案!

数据表的基本概念

实体:类似于java中的类
记录:java中的对象(一行数据)
字段:java中的属性,列
表:同一个实体中将记录和字段组合起来就是一张表!

查看当前系统表

• select * from tab;
  

• 查看emp表
  select * from emp
  

• 设置行长度!
  set linesize 150;
  如果我们设置行长度,可以看到一行显示不了这一条记录!
  

• set pagesize 300
  设置一页展示的大小
  

• 查看表结构
  desc table_name;
  

• 查询字段并起别名
  select empno as "编号",ename "姓名",job 月薪 from emp;
  
  注意:
  as: 可以省略!
  "":当遇到一些特殊字符,Oracle关键字,空格不能省略
  

• 清屏
  host cls;
  

• 修改上一条语句错误
  c /form/from;

c /form/from;
--显示修改后的sql!
-- /斜杠表示确认修改执行sql
/

• ed直接在记事本中修改上一条错误语句
  
  记得要有权限!管理员cmd窗口下可进行!
  关闭,回车,预览结果,/确认!
  

• 列的计算
  select empno, sal, sal*12 年薪 from emp;
  

• select控制列,where控制行!
  

• 日期/字符串/字符. 用单引号''
  

• 关键字/命令大小写不敏感
  

• 数据大小写敏感
  

• 运算符
  操作运算符: + - * / %
  关系运算符:> >= < <= =
  注意:
  等于:=
  不等于: !=或者<>
null判断:is null或者is not null
  
  逻辑运算符: or and not
  
  注意: 执行顺序
  where执行顺序:从右往左!

• null的计算
  任何数字和null计算都为null
  
  需要对null处理: null->0

  • nvl:if
    nvl(comm,0): if comm is null comm=0
    
  • nvl2: if else
    nvl2(comm,comm,0): if(comm==null) return 0 else return comm;
    

• 对查询的结果集去重
  distinct
  

• 连接符
  concat()或者 ||
  类似于java中字符串拼接hello+world-> helloworld
  
  注意:字符单引号

• dual临时表
  单行单列,学习时使用的临时表
  

• 日期
  修改日期格式 alter session set nls_date_format='yyyy-mm-dd';
  

• 范围查询:数字/日期
  between min and max; [min,max 闭区间
  
  

• 模糊查询
  like 通配符 %:任意字符 _:匹配一个字符
  
  查询姓名长度大于4的员工信息不可用函数
  
  查询字段中包含_的结果
  将_进行转义,转义符可以任意!
  like '%\_%' escape '\'; 用escape:表示转义字符!

• not in里不能出现null
  出现了null查询结果就必然为空!
  
  等价写法:
  select * from emp where comm not(comm = 300 or comm = 500);
  所以null不能和=判断!

• 排序
  order by asc(默认升序) desc(降序)
  order by 字段名|表达式|序号
  
  序号(就是第几列)
  
  表达式
  

• null默认是最大值
  
  通过nulls last 将null放最后!
  

• 追加命令
  a :append
  

• 多列排序
  select * from emp order by sal desc,comm asc;
  

Orcale函数

单行函数:一次操作一行
多行函数:一次操作多行返回一个结果

单行函数

字符函数:

• lower(变小写),upper(变大写),initcap(每个单词首字母大写)
  
• substr(str,begin,len); begin:从1开始数!
  
• length(str); 字符长度!
  
• lengthb(str) 字节数
  
  注意: 英文/数字字符和字节数一样!
  中文/特殊符号就不同!
  utf8: 一个汉字/符号 占3个字节!
  gbk: 占2个字节!
  
  
• instr(str1,str2); 在str1找str2返回位置(开始位置为0)
  
• lpad(str,n,'c')/rpad():str需要占n位,不够就用字符c填充!
  
• trim():去首尾任意字符,无参默认去空格!trim(c from str) 去指定字符c
  
• replace(str,c,r);: 将str字符中的c替换成r
  

数值函数

• round(num,n);对num第n位四舍五入,保留n位!
  n>0:保留几位小数!
  n=0:保留整数
  n<0:保留几位整数

• trunc(num,n);截取,舍尾!
  
• mod(num,n);对num进行n求余!
  

日期函数

• sysdate:关键字当前时间
  
• 格式化:日期-> 字符
  to_cahr(date,format) 日期date转成fromat格式字符
  
• 日期+-数字: 步幅是天,结果是日期
  
• 日期-日期:结果是天数
  
• months_between(日期1,日期2);:日期1-日期2得出相差多少月
  
• add_months(日期,n):日期+n个月
  
• last_day(日期):当前月最后是那一天
• next_day(date,w):下一个w星期几的时间!
  
• round(date,x)/trunc(date,x):对日期四舍五入或舍去 x可取:month/year/day对年月日四舍五入或者舍去

通用函数

• nvl,nvl2 :if if else
• nullif(a,b): if(a==b) null else a
  
• coalesce(a,b) if a!=null: a elif b!=null: b else null返回第一个不为null的值!
  
• 条件判断函数
  decode(字段,条件1,返回值1,条件2,返回2...最后表达式);
  
• case表达式

case 表达式
when 条件1 then 返回1
when 条件2 then 返回2
...
else 返回值n
end

![img])(https://i-blog.csdnimg.cn/blog_migrate/5fc8ec0c8f0e2f4d1748157ace1ad3d5.png)

转换函数

源类型	目标类型
nvarchar2/nchar等字符	number/date
number/date	nvarchar2/nchar等字符

• 显式转换
  
  to_number格式

格式	简介
9	数字
0	0
$	美元符号
L	本地货币符号
.	小数点
,	千分位

to_date格式

格式	简介	示例
yyyy	数字表示年	2019
year	英文表示年	twenty twelve
mm	数字表示的月	09
mon	月(与系统语言相关)	9月
dy	星期几(与系统语言相关)	星期一
day	星期几(与系统语言相关)	星期一
dd	每个月的第几天	02

多行函数/组函数/聚合函数

• count():求字段列数,且自动排除null
  
  和distinct组合使用可以求部门数!
  
• count max sum min avg这里和mysql一样!
  
• group by 分组注意事项

分组查询时,不在聚合函数中的列,必须在group by中!

多次分组

• 对组进行筛选 having
  

SQL语句

SQL语句类型:
SQL:数据查询语言select
DML:数据操作语言insert,delete,update
DDL: 数据定义语言 create/drop/truncate/alter table
DCL:数据控制语言 grant,revoke

DML

insert

语法:
insert into table_name (字段1,字段2,...) value (字段值1,字段值2...);
字段值和字段值一一对应,数据类型个数顺序一致!
注意:这里的into不能省略

多行插入

Oracle并不支持像MySQL一样的多行插入
insert into table_name (...)values(...),(...)...;
多行插入语法

insert all 
       into table_name  VALUES ('活塞',1990)
       INTO table_name  VALUES ('公牛',1991)
       INTO table_name  VALUES ('公牛',1992)
       SELECT 1 FROM DUAL; --需要借助dual表!

注意:
SQL92不能省略字段,SQL99标准可以省略字段名insert emp (可省略) values();

• 动态输入插入的值
  & 通过&占位符进行动态输入

insert into table_name(&name1,&name2...) values(&value1,&value2...);

批量插入数据

创建新表

批量插入前表不存在,插入后就生成了新表!创建表好插入数据一次搞定!

create table table_name
as select * from emp;

不但复制了emp表中的数据,并且将emp表结构也复制了!

1.复制表结构和全部数据

2.复制表结构和部分数据

3.创建表结构不存在数据

在旧表中插入

已存在表,然后进行数据插入!

方式一:

insert into table_name (empno,ename,sal) select empno,ename,sal from emp; 

方式二:

可以通过事务的方式进行多行插入!

begin
insert into emp
values(-1,'lisi','m',7788,'03-12月-81',9998,1000,20);
insert into emp
values(-2,'zhan','m',7788,'03-12月-81',9998,1000,20);
end;

如果插入海量数据:
1.数据泵
2.SQL Loader
3.外部表

delete

delete删除

语法:

delete from tab_name 
where ...;

注意:

1.不加where为全表删除,可以通过rollback回退
2.加上where删除表中部分数据
3.delete from tab_name 无*

全表删除

delete from tab_name; 删除表中的数据,删除完后可rollback回退

truncate from tab_name; 属于DDL语句不能回退!

测试delete和truncate删除全表的时间

打开sql执行时间
set timing on;
关闭sql执行时间
set timing off;

总结:

事务:
delete支持事务,truncat不支持事务!
时间:
对于少量数据: delete效率高,一行一行删除
对于海量数据:truncate效率高

• a. drop table丢弃整张表

• b. 重新创建表

闪回:
delete支持闪回,truncate不支持闪回
空间:
delete不会释放空间(换个空间放数据,类似于回收站)
truncate会释放空间,彻底删除
delete会产生碎片,truncate不会
产生碎片太多,可以整理碎片
a. alter table tab_name move;

b.可以通过数据导入导出

update

update tab_name set 字段名1=value1,字段名2 = value ...
where ...;

修改整张表数据!

where语句修改指定数据

DDL

create/drop/truncate/alter

create

创建表:
create table tab_name
(
	id number,
	name varchar(10),
	age number
);

注意事项:

1.权限和空间问题
2.表名命名规则:
a.字母开头
b.表名只包含:大小写字母,数字,_,&,#;
c.长度1-30个字符
d.不能与其他数据库中的对象重名(表,视图,索引,触发器,存储过程…)
e.不能与保留字重命
查看保留字: DBA账户
sqlplus / as sysdba
然后执行语句…

• 设置某个字段宽度:
  字符:col 字段名 for a7;
  数字: col 字段名 for 9999;
  

alter

• 追加新列

alter table tab_name add 字段名 字段类型;

• 修改列
  • 修改列的长度
    alter table tab_name modify 字段名 字段类型;
  • 修改类的类型
    alter table tab_name modify 字段名 字段类型;

注意:

blob/clob不能修改 ->先删除此列,重新追加!

• 删除列
  alter table tab_name drop column 字段名;
  

• 重命名列
  alter table tab_name rename column 字段名 to 新字段名名;

drop

drop删除表(放入回收站)

drop table tab_name;

drop删除后,表还在tab中,也就是放入了回收站

• 查看回收站:
  show recyclebin;
  

• 清空回收站:
  purge recyclebin;
  

• 还原回收站:
  闪回

删除表并清空回收站
drop table tab_name purge;

连接

内连接/外连接/自连接/层次连接

交叉连接(笛卡尔积),所有情况的组合,不推荐使用
select * from tab_name1,tab_name2;

• 内连接:

  • 通过where连接
    

  • 通过inner join on连接
    

多张表通过相同字段连接,匹配成功则显示,否则不显示

• 外连接

  • 左外连接

  以左表为基准,去匹配右表数据,如果匹配成功,则全部显示,匹配不成功右边部分为null

  --方式1
  select * from tab1 left (outer) join tab2 on 条件;
  -- 方式2 Oracle独有
  select * from tab1 t1,tab2 t2 where t1.no = t1.no(+); 
  

  

  • 右外连接

  以右表为基准,去匹配左表数据,如果匹配成功,则全部显示,匹配不成功左边部分为null

  -- 方式1
  select * from tab1 right (outer) join tab2 on 条件;
  -- 方式2 Oracle独有
  select * from tab1 t1,tab2 t2 where t1.no(+) = t1.no; 
  

  [！(img-ZVL9XlK3-1721894681792)(https://i-blog.csdnimg.cn/blog_migrate/58ced5ebf39c5b407d7cdec3b382a8b9.png)

  • 全外连接

  左外连接加上右外连接然后去重

• 自连接

  一张表当两张表用

select * from tab t1,tab t2 where ...;

• 层次连接

树形层次

​