Oracle学习笔记0.1

阅读数: 次 2021-06-21

文章导航

Oracle学习代码笔记

数据库实例操作，表空间，增上改查语句,数据类型,序列…

⚡ ⚡ ⚡

Oracle

一. Oracle的特点

1.支持大数据库，多用户，高性能的事务处理
2.安全性好
3.提供新的分布式数据库能力
4.具有可移植性，可兼容性

系统可移植性好，使用方便，功能性强，适用于各类大，中，小，微机环境。它是一种高效率，可靠性好的适应高吞吐量的数据库

二. 使用数据库要启动的组件

使用数据库之前要先启动Oracle的Service(服务)组件，如需要远程连接要启动Listener(监听器)组件

三. Oracle用户区别

SYS用户是一个Oracle中的超级用户，主要用于数据库的维护和管理实例。
SYSTEM用户是Oracle是Oracle中的默认管理员，该用户拥有Oracle管理工具使用的内部表和试图，通常用来管理数据库中用户权限的储存。
SCOTT用户是Oracle数据库中的示范账号。

四. SQL数据结构语言有：

数据定义语言(DDL)：创建删除数据库对象
数据操作语言(DML)：操作数据库
数据控制语言(DCL)：用户权限的管理
事务控制语言(TCL)：维护数据的一致性（事务的提交，回滚）

五. 数据库实例

数据库是:物理存储数据(日志文件,数据文件,控制文件),一个数据库可对应多个实例,

实例是:是进程+内存，加载数据库后，Oarcle分配的一块内存空间（系统全局区SGA），每个实例都有一个标识符SID，通常SID于数据库名同名

启动实例就是装载数据库

六. 数据类型

1). 字符类型

Char 固定长度，最大2000字节，括号中限制多长就是多长
Varchar2 可变长度字符串类型，最大4000字节。多出的长度会正常利用，避免资源浪费

-- 字符串类型
create table test4
(
  a varchar(8),--系统内部识别,自动改为varchar2类型
  b varchar2(8)
)

-- 获取字符长度
select vsize('汉') from dual

2). 数字类型

Number(p): 表示整形,长度有实际业务需求定
Number(p,s): 表示小数类型，P表示总长度，S表示小数点精确到几位

-- number
create table test1
(
  a number,
  b int --保存在数据库中的类型为number类型，默认长度38
)
select * from test1;

create table test2
(
  a number(2),--长度2，整数
  b number(2,1),--总长度2，小数位1(-9.94<x.x<9.95)
  c number(2,-1)--总长度3(-994<xx0<995)
)
-- a
insert into test2(a) values(99);
insert into test2(a) values(-99);
--四舍五入，自动取整
insert into test2(a) values(9.9);
-- b
insert into test2(b) values(9.9);
insert into test2(b) values(-9.9);
insert into test2(b) values(9.94);
-- c
insert into test2(c) values(990);
insert into test2(c) values(-990);
insert into test2(c) values(994);
select * from test2;

3). 时间类型

Date 长度为7个字节，分别是，世纪，年，月，日，时，分，秒
Timpstamp 秒能精确到小数点后6位,常用于时间戳

create table test3
(
  a date
)
-- 系统时间
insert into test3 values(sysdate);
insert into test3 values(date'2020-02-24');
insert into test3 values('24-2月-2020');
insert into test3 values(to_date('2020-02-24 10:54:30','yyyy-MM-dd hh:mi:ss'));
select * from test3
--查询当前时间
select sysdate from dual;--虚表
--详细日期
select to_date('2020-02-24 10:54:30','yyyy-MM-dd hh:mi:ss') from dual;

4). 大对象类型

Clob: 最大容量为4G
Blob: 存放二进制大对象数据类型，类如:图片,视频,音频

七. Oracle数据库基本语法

1). 用户操作

系统用户

sys：超级管理员，只能用sysdba，sysoper身份登陆
system：管理员，可以使用normal进行登录
scott：示范账号

连接身份

normal：普通用户
sysdba：数据库管理员，主要用于管理用户，授权，数据，登录用户是“SYS”
sysoper：操作管理员，主要用户开启/关闭数据库，登陆用户是“PUBLIC”

-- 解锁用户
ALTER USER 用户名 ACCOUNT UNLOCK;
-- 查看当前实例SID
select instance_name from V$instance;
-- 创建用户(不声明使用系统默认表空间)
create user aaa identified by 123
-- 查询用户
select * from dba_users where username='AAA';
-- 修改密码
alter user aaa identified by 123
-- 用户授权
grant connect to aaa;--临时用户，可以连接，可以查看
grant resource to aaa;--资源权限，可以操作表
grant DBA to aaa;--管理员权限
grant connect,DBA,resource to aaa;--同时授予多个权限
grant select,insert on emp to aaa--授予用户对emp查询,添加权限
-- 查看用户权限
select * from dba_role_privs where grantee='AAA'--用户名大写
-- 撤销权限
revoke DBA from aaa;
revoke connect,resource from aaa;--撤销多个权限
-- 删除用户
alter user sm account lock;--首先将索要删除的用户锁定
select username,sid,serial# from v$session;--从【v$Session】表查看当前用户占用资源
alter system kill session '18,4'--杀死status为【 INACTIVE】的进程
drop user SM cascade;--级联删除(删除用户和用户下的表)

2). 表操作

创建表基本语法

Create table 表名(
  列名 数据类型 [约束],
  列名2 数据类型,
  Constraint 约束名称 约束类型
)
create table tbl_class(
  class_no number(6) primary key,
  class_name varchar2(200)
)

①创建/删除表

-- 创建班级表
create table tbl_class
(
  class_no number(3) primary key,--主键
  class_name varchar(50) not null--非空
)segment creation immediate;
--segment creation immediate延迟段技术(是序列递增从1开始)
-- 创建一张学生表
create table tbl_student
(
  stu_no number(6) primary key,--主键约束
  stu_name varchar2(200),
  age number(3) not null,
  sex char(2),
  --添加约束（直接添加）
  class_no number(6) references tbl_class(class_no),
  --也可以先声明列，在最后添加约束
  --constraint pk_classno foreign key(class_no) references tbl_class(class_no),--外键约束
  constraint check_sex check(sex='男' or sex='女')--check检查约束
)

-- 删除表
drop table tbl_class

②创建/删除约束

-- 约束
alter table tbl_student drop constraint pk_classno--删除约束
alter table tbl_student 
add constraint pk_classno foreign key(class_no) references tbl_classs(class_no)--添加约束
alter table tbl_student add constraint check_sex check('男' or '女')--添加check检查约束

--表中删除约束
alter table tbl_student
drop constraint pk_classno
--表中添加约束
alter table tbl_student
add constraint pk_classno foreign key(class_no) references tbl_class(class_no)

③表结构

添加，删除，修改某列

-- 表结构
alert table tbl_linkman
drop column name--删除表中某列
alert table tbl_linkman
add name varchar2(100)--表中添加某列
alert table tbl_linkman
modify name varchar2(50)--修改某列内容

实例：

--班级表添加一列
alter table tbl_class
add teacher_name varchar2(200)
--表删除一列
alter table tbl_class
drop column teacher_name
--表中修改列
alter table tbl_class
modify class_name varchar2(100)

3).数据操作语言

commit;—提交
rollback;—回滚

①添加数据

-- 添加语句
insert into tbl_class values(seq_classno.nextval,'TF11');
--表中一次性插入多条数据
--(1)利用union连接(一次性插入多行,不可用序列)
insert into  tbl_class(
  select 111,'TF12' from dual
  union 
  select 112,'TF13' from dual
  union 
  select 113,'TF14' from dual
  union 
  select 114,'TF15' from dual
)
--(2)insert into select(从另外一张表中复制出数据，两张表可以结构不一样，只要对应列的数量,类型都一致)
insert into tbl_class2(select * from tbl_class)
--(3)select into 新表 from 已经存在的表(新建一张表，粘贴数据，用于数据备份,不会复制约束)
create table tbl_calss3 as(select * from tbl_class)

②删除数据

-- 删除数据
--delete 表名 where 条件
delete from tbl_class where class_name='TF66' and class_no=6
--truncate table 表名 清除表中所有记录，不能写条件
truncate table tbl_class --有外键约束，删除不掉

③修改数据

1
2
3

-- 修改数据
update tbl_class set class_name='TF66' where class_no=6
update tbl_class set class_name='TF66' --不添加条件修改表中所有行数据

④查询数据

--查询语句
select * from tbl_class
-- 查询
/*
   select 列名....
   from 表名
   --以左表为主表，右表做匹配
   left join 表名2 on ...
   --只输出匹配到的数据
   jion 表名3 on ...
   where 条件
   group by --分组
   having --对分组后劫夺条件过滤，一般聚合函数的输出结果进行限制(相当于where)
   order by --排列顺序（asc⬆,desc⬇）
*/
-- 伪劣:表中不存在的列
--RowID：唯一标识（查询结果包含rowid时，可以直接编辑查询数据）
--RowNUM：排序（作为查询条件必须从1开始，不然没有查询结果）
select s.*,Rowid,rownum from tbl_linkman s;
--查询后，可直接编辑查询额数据
select * from tbl_linkman for update;
--要求查询工资最高的雇员信息
select * from emp where sal =(select MAX(sal) from emp)
--要求查询工资高于部门20中所有员工的雇员信息
select * from emp where sal >(select max(sal) from emp where deptno=20)
select * from emp where sal >all(select sal from emp where deptno=20)--all里面所有
--查询各部门中工资最低的雇员信息
--匹配多行多列
select * from emp where (deptno,sal) in(
select deptno,min(sal)
from emp
group by deptno --部门分组
)
--子查询作为虚拟表，进行查询
select * from emp b
join (
  select deptno,min(sal) as minsal
  from emp
  group by deptno
)a on a.deptno=b.deptno and a.minsal=b.sal
--查询负责管理其他雇员的管理员信息,exists主要用于子查询需要用到父查询的结果
select * from emp 领导
where exists(
  select * from emp 员工 where 领导.empno=员工.mgr
)
select * from emp where empno in(select distinct mgr from emp)
-- 分页
--rownum 代表行号
select a.*,rownum from(
  select a.*
  from emp a
  order by sal
)a
--要求每页5条
select * from(
  select a.*,rownum as rn from(
    select a.*
    from emp a
    order by sal
  )a
)b
-- >(当前页-1)*每页多少条 and <=(当前页-1)*每页多少条
where rn > 5 and rn<=10

-- 查询所有部门的人数和部门编号
select deptno,count(empno) from emp
group by deptno
-- 查询所有部门人数和部门名称
select dname,总数 from dept
join(
select deptno,count(empno) as 总数 from emp
group by deptno
)b
on dept.deptno =b.deptno
--或
select dname,总数 from dept a,
(
select deptno,count(empno) as 总数 from emp
group by deptno
)b
where a.deptno =b.deptno
-- 查询所有员工的员工编号，员工姓名，员工部门和员工薪水
select emp.empno as 员工编号,emp.ename as 员工姓名,dept.dname as 员工部门,(emp.sal+nvl(emp.comm,0)) as 员工薪水 from emp
left join dept on emp.deptno = dept.deptno
-- 列出受雇佣日期早于其直接上级的所有员工的信息
select * from emp 员工
where exists(
  select empno from emp 领导 
  where 领导.empno = 员工.mgr and 领导.hiredate>员工.hiredate
)

八. 序列

一般用于表中的主键自增
默认值为从1开始自增1，无上限
start with不能小于最小值，不能大于最大值
序列和自增的区别：自增不需要赋值，自动生成不重复且自增的值

create sequence seq_classno
start with 1 --从1开始
increment by 1 --每次递增1
Maxvalue 数字 --最大值
Minvalue 数字 --最小值
cache 数字 --缓存数量（把指定数量的数存到缓存区中，需要时取出，节省计算时间）
cycle --是否循环自增(当序列值达到最大值后，从头开始循环)

实例：

-- 序列
--创建一个序列，用于自增
create sequence seq_tblclass_classno
start with 1--从1开始
increment by 1--每次递增1
--删除序列
drop sequence seq_classno
--当前值 如果nextval一次都没有执行，那么currval不能用（dual是虚表）
select seq_tblclass_classno.currval from dual--当前值(使用当前值前，要先next下)
select seq_tblclass_classno.nextval from dual--下一个值
--添加可递增(依赖序列)
insert into tbl_class (class_no,class_name)values(seq_tblclass_classno.nextval,'哈');

九. 表空间

Oracle的所有文件以表空间为主，一个数据库可以有很多表空间，一个表空间只属于一个数据库，表空间是数据库的逻辑划分，具体的物理文件以表空间为主。

表空间分为

1.永久表空间：存储表，试图，存储过程等具体对象，不可释放。
2.临时表空间：用来存储缓存数据，重启数据库可以释放临时表空间。
3.UNDO表空间：用于保存事务的回退信息，用户不能在其中创建数据库对象。

-- 查看系统下表空间视图
select distinct(tablespace_name) from dba_free_space;--去除重复
--计算表空间大小
select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name;
--数据文件
select * from dba_data_files
--临时文件
select * from dba_temp_files
-- 创建表空间
create tablespace tsp_test01
datafile 'D:/a.dbf'--存放路径
size 10m--文件大小
autoextend no next 5m maxsize 30m--超出10m每次递增5m，最大为30m
-- 创建分布式表空间(数据文件分开放)
create tablespace tsp_test02
datafile 'D:/b.dbf'
size 10m
autoextend no next 5m maxsize 30m,
'D:/c.dbf'
size 10m
autoextend no next 5m maxsize 30m
-- 临时表空间
create temporary tablespace temp_test03--temporary临时
tempfile 'D:/temp_d.dbf'--tempfile临时文件
size 10m
-- 创建指定表空间用户
create user uname identified by 123
default tablespace tsp_test01--表空间
Temporary tablespace temp_test03--临时表空间
-- 修改表空间(表空间可以有多个文件)
alter tablespace tsp_test02
add datafile 'D:\APP\TENG\ORADATA\TEST\test_02.dbf' size 100m
-- 查看默认表空间
select * from database_properties where property_name like '%TABLESPACE%';
-- 删除表空间
drop tablespace tsp_test02
including contents and datafiles;--删除表空间下文件

本文作者： 腾飞
本文链接： https://www.tengfei.eu.org/article/b5e7337f.html
版权声明： 本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。转载请注明出处！