数据类型

字符类型
char：用于存储 固定长度 的字符串。最大长度为2000个字节。
varchar2：用于存储字符串数据。会根据实际数据的长度自动调整，因此在大多数情况下都会使用varchar2类型。最大长度是4000个字节。
中文（包括符号）占两个字节，英文占一个

1
2
select length('字符串a，') from dual --结果5（不区分中文）
select lengthb('字符串a，') from dual --结果9（区分中文）

**数值类型 **
number：具有精度和范围两个参数。精度指定所有数字位的个数，范围指定小数的位数。 如number（7,2）总位数7位，其中2位是小数，小数点前有5位整数，小数位数不能超过总位数。字符类型原理上不能用于运算，因此需要运算的数据需要使用number类型

**日期时间类型 **
date：可以存储日期和时间的组合数据。结合oracle提供的日期时间函数便地处理数据。日期可直接加减，不能乘除，加的是日期

1
select sysdate+365 from dual

timestamp：时间戳类型,可存放世纪、纪元、年、月、日、时、分、秒还可以存放秒后6 位。 一般只有对时间要求比较严格的业务（如话费计费）才用

CLOB/BLOB
CLOB：大文本文件类型，可存放word,excel等文档。
BLOB：二进制文件类型，可存放视频、音频等。

三大范式 （面试会问）

  • 第一范式：原子性的，不可分。 （列尽可能细分）所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
  • 第二范式：要求实体的属性完全依赖于主关键字。（一张表只做一件事） 所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。
  • 第三范式 ：属性不依赖于其它非主属性。（关系近） 第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 满足第三范式（3NF）必须先满足第二范式（2NF）。

数据存储方式

oracle数据库的存储结构可以分为逻辑存储结构和物理存储结构,对于这两种存储结构， oracle是分别进行管理的。

逻辑存储结构：oracle内部的组织和管理数据的方式。
oracle在逻辑上将保存的数据划分为一个个小单元来进行存储和维护，更高一级的逻辑存储结构都是由这些基本的小单元组成的。 逻辑结构类型按照尺寸从小到大分可分为：块(block)–>区(extent)–>段(segment)–>表空间(tablespace）

物理存储结构：oracle外部（操作系统）组织和管理数据的方式。
Oracle数据库逻辑上由一个或多个表空间组成，每个表空间在物理上由一个或多个数据 文件组成，而每个数据文件是有数据块构成的。所以，逻辑上数据存放在表空间中，而物理上存储在表空间所对应的数据文件中。
构成数据库物的物理文件主要有三种：

  • 数据文件：存放数据库数据。
  • 控制文件：存放数据库的基本信息，告诉数据库到哪里找到数据文件和重做日志文件等。对数据库的成功启动和正常运行是很重要的。
  • 重做日志文件：存放对数据的改变。至少两组，Oracle以循环方式来使用它们。

表和表空间的关系

表空间是由若干个数据段组成，存放表，视图，索引等。一个表只能属于一个表空间， 一个表空间可以放任意多个表，一个表空间至少有一个存储文件（.dbf结尾的文件），可以有多个，而且这多个数据文件可以在不同位置，一个用户有一个默认的表空间，一个用户可以在默认表空间外的其它表空间建表。

创建表空间

create tablespace 表空间名 datafile ‘数据文件路径’ size 初始大小（2G） autoextend on next 每次扩展的大小(100M) maxsize 最大容量（unlimited）;

1
2
3
4
create tablespace huangwenzhe429 --真的表空间名
datafile 'C:\workspace\sql\huangwenzhe429.dbf' --生成的文件名，可以不同，但建议与表空间名一样，数据库所在系统（虚拟机环境）应有对应路径
size 100M autoextend on next 50M maxsize unlimited;
--起始空间100M，用完后自动分配50M,最大空间无限

登录system管理员给用户权限（管理员用户名：system）

1
2
3
4
5
6
7
8
9
10
--grant 权限 to 用户名;
grant dba to scott --给scott用户管理员权限
grant resource,connect to scott; --连接权限和资源权限 
grant create any table to scott; --建表权限 
grant create any tablespace to scott; --建表空间权限 
grant select any table to scott; --只读权限 
grant create any view to scott; --创建视图权限 
grant select any table to scott; --预编译表的权限
--查看角色权限信息 
select * from role_sys_privs;

用户权限对比表： https://blog.csdn.net/weixin_36470210/article/details/116318174

创建临时表空间

一般存放select查询出来的虚拟表

1
2
3
create temporary tablespace huangwenzheTemp429 
tempfile 'C:\workspace\sql\huangwenzheTemp429.dbf' 
size 100M autoextend on next 50M maxsize unlimited;

创建用户

create user 用户名 identified by 密码 default tablespace 表空间名 temporary tablespace 临时表空间名;
default后面的语句是为用户指定默认表空间和临时表空间，如果不指定，默认的表空间 是users表空间，临时表空间是temp

1
2
3
4
create user huangwenzhe --用户名
identified by oracle --密码
default tablespace huangwenzhe429 --表空间名
temporary tablespace huangwenzheTemp429;  --临时表空间名

删除表空间

1
drop tablespace huangwenzhe including contents;

including contents选项用于删除表空间时包含其内容。如果不使用这个选项，表空间会 被删除，但数据文件仍然存在，磁盘空间不会被释放。使用这个选项可以确保表空间及 其内容被完全删除，从而释放磁盘空间

命令窗口登陆数据库

sqlplus 用户名/密码@数据库
sqlplus scott/oracle@orcl
（此处不需要分号，但命令窗口写sql语句结束一定要加分号）
![[Pasted image 20250429122129.png]]
命令窗口操作数据库应在数据库所在系统（虚拟机环境）中进行

扩容表空间

1
2
3
alter tablespace huangwenzhe429 
add datafile 'C:\workspace\sql\huangwenzhe429.dbf' 
size 100m autoextend on next 10m maxsize unlimited;

常见问题

system登录报错： ORA-01017（用户名/口令无效; 登录被拒绝）
打开cmd命令窗，输入sqlplus / as sysdba
1.修改密码：alter user 用户名 identified by 密码;
2.用户被锁定，解锁：alter user 用户名 account unlock;

建表相关

三大数据类型：number varchar2 date

六大约束

  • not null 非空约束
  • unique 唯一约束
  • primary key 主键约束（非空&唯一）
  • default 默认约束
  • check 检查约束（唯一带条件判断的约束）
  • references 外键约束

有外键约束的表为从表，没有的为主表，建表先主后从，删表先从后主

重复数据 = 冗余数据
null = 空
列名不应使用会高亮的关键字

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create table bm(  --表名为bm
	bmno number(4) primary key,
	bmmc varchar2(20) unique,
	bmdz varchar2(20) not null
);

--列级约束
create table yg( --表名为yg
	ygno number(6) primary key, --主键约束
	ygxm varchar2(20) not null, --非空约束
	job varchar2(20) not null, --not null无法使用表级约束
	rzrq date default sysdate, --默认约束，默认为当前日期
	gz number(7,2) check(gz>=1500), --唯一一个带条件判断的约束
	bmno number(4) references bm(bmno), --外键约束
	constraint zjys primary key(ygno), --表级约束
	constraint wjys foreign key(bmno) references bm(bmno)
	--表级约束语法
	--constraint 约束名 约束类型(列名)
)

--复合主键必须用表级约束
create table cj(
	sno number(5),
	course varchar2(20),
	score number(6,3),
	constraint fhzj primary key(sno,course)
)

--创建一个和select查询语句查询结果一样的表（包括结构和数据）
create table bm3 as
select * from scott.dept

插入数据

1
insert into bm values(10,'销售部门','桂林市');

删除表

1
drop table bm

练习

表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--创建表空间
create tablespace huangwenzhe429 
datafile 'C:\workspace\sql\huangwenzhe429.dbf' 
size 100M autoextend on next 50M maxsize unlimited;

--给予管理员权限
grant dba to scott;

--创建临时表空间
create temporary tablespace huangwenzheTemp429 
tempfile 'C:\workspace\sql\huangwenzheTemp429.dbf' 
size 100M autoextend on next 50M maxsize unlimited;

--创建用户
create user huangwenzhe
identified by oracle 
default tablespace huangwenzhe429 
temporary tablespace huangwenzheTemp429;

--给予用户管理员权限
grant dba to huangwenzhe;

--查看别的用户的表
select * from scott.emp;
select * from scott.dept;

--删除表空间
drop tablespace huangwenzhe429 including contents;

学生管理系统

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
--创建teacher表
create table teacher(
       TNO varchar2(10) primary key,
       TNAME varchar2(20) not null
);

--插入teacher数据
insert into teacher values('t001','刘阳');
insert into teacher values('t002','谌燕');
insert into teacher values('t003','胡明星');

--查看/删除teacher表
select * from teacher
drop table teacher

--创建student表
create table student(
       SNO varchar2(20) primary key,
       SNAME varchar2(30),
       SAGE number(2) check(SAGE between 6 and 40),
       SSEX varchar2(5) default '男'
);

--插入student数据
insert into student values('s001','张三',23,'男');
insert into student values('s002','李四',23,'男');
insert into student values('s003','吴鹏',25,'男');
insert into student values('s004','琴沁',20,'女');
insert into student values('s005','王丽',20,'女');
insert into student values('s006','李波',21,'男');
insert into student values('s007','刘玉',21,'男');
insert into student values('s008','萧蓉',21,'女');
insert into student values('s009','陈萧晓',23,'女');
insert into student values('s010','陈美',22,'女');
insert into student values('s12','谢丰琴',20,'女');
insert into student values('s13','谢中菊',20,'男');
insert into student values('s14','谢一才',20,'男');

--查看/删除student表
select * from student
drop table student

--创建course表
create table course(
       CNO varchar2(10) primary key,
       CNAME varchar2(30),
       TNO varchar2(20) references teacher(tno)
);

--插入course数据
insert into course values('c001','J2SE','t002');
insert into course values('c002','Java Web','t002');
insert into course values('c003','SSH','t001');
insert into course values('c004','Oracle','t001');
insert into course values('c005','SQL SERVER 2005','t003');
insert into course values('c006','C#','t003');
insert into course values('c007','JavaScript','t002');
insert into course values('c008','DIV+CSS','t001');
insert into course values('c009','PHP','t003');
insert into course values('c010','EJB3.0','t002');

--查看/删除course表
select * from course
drop table course

--创建sc表
create table sc(
       SNO varchar2(10) references student(sno),
       CNO varchar2(10) references course(cno),
       SCORE number(5,2) check(SCORE between 0 and 100),
       constraint fhzj primary key(sno,cno)
)

--插入sc数据
insert into sc values('s001','c001',78.90);
insert into sc values('s002','c001',80.90);
insert into sc values('s003','c001',81.90);
insert into sc values('s004','c001',60.90);
insert into sc values('s001','c002',82.90);
insert into sc values('s002','c002',72.90);
insert into sc values('s003','c002',81.90);
insert into sc values('s001','c003',59.00);
insert into sc values('s12','c005',89.00);
insert into sc values('s13','c008',87.00);
insert into sc values('s13','c007',65.00);
insert into sc values('s13','c010',85.00);
insert into sc values('s14','c007',100.00);
insert into sc values('s14','c005',60.00);
insert into sc values('s14','c006',92.00);
insert into sc values('s14','c008',95.00);
insert into sc values('s14','c010',91.00);
insert into sc values('s14','c004',57.00);

--查看/删除sc表
select * from sc
drop table sc