Oracle入门1

2023-03-25 16:25 由 te9uila 发表于 #数据库

Oracle

Oracle起源

创始人：拉里-埃里森

用户分类

管理员用户：具有数据库中最高权限，可以任意访问所有用户的数据

普通用户：被管理员创建，所有的权限来自管理员的分配

创建普通用户

默认创建用户的时候，普通用户是没有权限的，需要管理员分配

create user 用户名 identified by 用户密码;

Oracle默认的管理员：sys，sysdba

能够赋予的权限：

权限	意义
connect	连接（允许用户登录）
resource	资源（允许用户创建数据）
dba

使用管理员用户赋予权限

# 赋予权限
grant connect to 用户名;
grant resource to 用户名;
grant connect,resource to 用户名;

# 撤回权限
revoke connect from wyf;
revoke resource from wyf;

用户状态 - lock/unlock

一般情况下，在创建用户的时候默认都是解锁状态

create user 用户名 identified by 密码 account lock;

# 解锁scott用户：是Oracle中的一个特殊用户，默认具有所有权限，但是默认被锁定
alter user scott account unlock;
# 修改scott用户密码为123456
alter user scott identified by a12345;

Oracle的基础存储单元

表：

表名：访问表的时候需要提供表的名称以及表所属的用户。

！如果访问表，来自于当前用户自己，则用户名可以省略

select * from scott.EMP;
# 使用用户scott
select * from EMP;

字段：决定了表中的每一行数据能够存储的数据

字段值：代表表里字段的具体数值

！表的字段再定义的时候，必须指定字段的数据类型

# 创建用户信息表
# 定义数据表的时候，表名可以使用下划线连接
create table user_info(
	id number(8),   # number代表的是数字类型
    money number(10,2),   # （）里面的数字第一位表示整数最高位数，第二位代表小数最高位数
    name varchar2(20),  # 最多容纳20个字节大小的文字
    # varchar2代表不定长出具类型，占用的空间大小于内容有关
    
    gender char(3),  # 常见储存字符的数据类型char(),varchar()
    # char代表定长数据类型，占用的空间大小于内容无关，存储数据不足规定的空间则使用空格填充
    # char是一个典型的空间换时间的数据类型
    # 阿里的开发手册中规定，如果字段存储的数据长度几乎一致，则使用char类型
    addr varchar2(200),
    birthday date  #存储日期类型的数据
);

文字所占据的大小是和文字所使用的字符集有关，文字再计算机底层进行存储的时候，存储的并不是文字本身，而是文字所对应的字符集编码

字符集

类似于字典（每一页只存放一个字），页码及对应的是字符集编码.第一版字符集（ASCII码）其中的一个字符占据一个字节（八位二进制）

gb2312字符集，一共收录了6700个汉字，此字符集中，中文字符的存储使用两个字节，英文字符使用一个字节，不支持绝大部分的生僻字和繁体字

gbk 相当于gb2312字符集的拓展，兼容gb2312的同时，收录了20000个汉字（包括生僻字，繁体字）

gb18030 完全兼容gb2312 和gbk 并且收纳了中国国内少数民族的文字，收录了日韩汉字，一共收录了70000个字符

big5 主要针对繁体中文，收录了13000+汉字（弯弯使用）

ANSI 编码，并不是一个固定的编码，计算机操作系统使用的编码是什么，ANSI 就代表什么

！国内windows使用的编码为（gbk编码）

unicode 万国码美国发明，包含了世界上几乎所有国家的所有文字，unicode 存储的字符量太大，因此Unicode使用的最多字节数量为4字节

Unicode拓展字符集：UTF-8（使用1-4个字节）, UTF-16（使用2个或4个字节） , UTF-32（所有字符使用4个字节），UTF-8nm4（支持emoji表情）

！计算机在使用字符集的时候分为两种，一种是写入文本的时候使用的字符集，一种是读取文本的时候使用的字符集，两者使用的字符集必须一致！

！数据库对字符集编码支持是不一样的，在安装Oracle的时候，定义的字符集为UTF-8。英文字符存储使用一个字节，中文字符存储使用三个字节

约束

为了限制某些不符合要求的数据

create table student(
	sno number(8) primary key,  # 主键约束，代表数据的唯一性，一张表里只能有一个主键，不可以重复，不可以为空
    sname char(12),
    gender char(3),
    major char(30),
    classno number(4),
    age number(2),
    addr varchar2(200),
    enrolldate date
);
# 检查约束: alter table 表名 add constraint 约束名称 check(约束内容);
# 约束命名规范：见名知意
# 插入字段值如果非法，则报错
alter table student add constraint ch_stu_gen check(gender = '男' or gender = '女');
alter table student add constraint ch_stu_age check(age between 18 and 25);
# 非空约束
alter table student modify(sname not null);
# 默认约束 再插入数据的时候如果没有土工字段值，则约束提供默认值
alter table student modify(addr default '南京');
# 唯一约束 限制字段中插入的值不可以重复
alter table student add constraint un_stu_addr unique(addr);
# 查看表中的约束(表名为大写！)
select * from user_constraints where table_name = '表名';

插入数据（insert）

# 全字段插入，按照表中字段顺序，为所有字段提供值，数字可以直接使用，字符串用单引号
# sysdate是Oracle自带的函数，表示的是当前时间
insert into student values(20260322,'吴亦凡','男','坐牢',1101,22,'北京看守所',sysdate);
insert into student values(20260323,'wyf','男','网工',1101,22,'紫金',sysdate);
# to_date函数，指定日期类型数据插入,参数一：需要转换日期格式的字符串。参数二：对应的日期格式
insert into student values(20260324,'tmp1','男','软工',1101,21,'南邮',to_date('2020-09-01','YYYY-MM-DD'));
# 指定字段插入，只给部分字段提供值，需要声明字段名，字段值与其一一对应
insert into student
(sno,sname,gender,major,classno,age,enrolldate)
values
(20260325,'tmp2','男','网工',1101,21,sysdate);

更新语句（update）

update 表名 set 更改内容 where 表格主键;
# 同时修改多的值用逗号隔开
update product set name = '七喜', price = 77 where id=2;
# 多条件查询，条件之间用and/or隔开
update product set price = 3, addr = '南京新港' where price > 3 and addr = '南京';

删除语句（delete）

# 根据你提供的体哦啊缉拿删除指定行的数据
delete from product where name ='可乐';
delete from product where id<8 and addr = '南京新港';
# 如果没有写条件，默认删除表内的所有信息
delete from product;

修改字段（alter）

# 添加字段
alter table student add mary char(6);
# 删除字段
alter table student drop column marry;
# 重命名字段
alter table studnet rename column sno to sid;  # 将sno重命名为sid
# 修改字段的数据类型
# 如果原本是字符串类型，不可以修改为number和其他，必须保证修改后，数据库的内容不被影响
alter table studnet modify classno number(10);

# 可以用来修改密码，修改用户的锁定状态
alter user 用户名 identified by 新密码;  # 修改密码
alter user 用户名 account unlock;  # 解锁用户

语句分类

1.DDL语句（数据定义语言）

create（建表） drop（删表） alter（修改字段） truncate（清空表数据，并且不可恢复）

2.DML语句（数据操纵语言）

insert （插入）update（更新） .....

Oracle规定所有的DML操作的数据，不会直接提交到数据库中，而是保存在本地会话所对应的日志文件中（缓存）。当用户执行commit指令的时候，会话才会将本地日志文件中的数据上传到数据库上，并且在提交的同时，会清空日志文件。

Oracle的设计目的是为了降低数据库底层磁盘的读写次数，提高数据效率。

TCL事务控制语言

1.commit 提交当前会话中的日志文件中的数据到数据库中，并且清空本地日志文件的内容

2.savepoint 记录当前日志文件中的数据状态，类似单机游戏的存档

3.rollback 数据回滚，将日志文件中的数据回滚到指定savepoint的状态

！plsql 会在用户关闭窗口或者是切换用户的时候，对文件进行自动提交（仅限普通的正常退出方式，任务管理器强制退出不行）

rollback to 存档点名称;
# 如果直接使用rollback，则将数据的状态回滚到上一次commit的时候
insert into test values(1);
insert into test values(2);
savepoint A;
insert into test values(3);
insert into test values(4);
rollback to A;
# 执行以上指令后，日志文件里有两条数据1,2，但是数据库内一条数据都没有
insert into test values(1);
insert into test values(2);
savepoint A;
insert into test values(3);
insert into test values(4);
commit;
rollback to A;
select * from test;
# 执行以上指令后，数据库里有4条数据，select查询可以查到4条数据
# 当commit进行提交之后，会清空日志，同时会清空A存档点，因此回滚的时候就找不到A存档点，会报错。。。

什么是事务：

多个DML操作可以当作是一个事务。

# 举个栗子：
# A给B转100rmb， 经历了两个过程，A余额-100，B余额+100
update wallet set money = money - 100 where name = 'A';
update wallet set money = money + 100 where name = 'B';
# 一张两条语句就可以当作一个整体，也就是事务

数据库事务特性

原子性

事务所对应的sql语句，要么全部提交，要么全部不提交。保证数据是完全正确的才能提交，目的是保证数据的安全性。

一致性

所有用户所获取的统一数据应该是一致的。

持久性

确保所有数据都能够长期的存储在数据库中。执行完DML操作之后，必须进行commit提交。

隔离性

两个事务不可以同时操作同一条数据，A事务操作数据的时候，B事务要么在A操作之前操作，要么等到A事务结束再执行。

外键约束（完整性约束）

外键的作用是用来保证数据的完整性。

外键约束的特性：

1.附表在插入数据的过程中，必须在主表中有关联的记录，否则数据无法插入

2.删除主表的数据之气那，必须删除附表中的关联的全部数据，否则无法删除主表数据

# 测试环境
# 学生表(student)
create table student(
       stuid varchar2(8) primary key,  # 学生学号
       sname char(15)  # 姓名
);
# 成绩表(score)
create table score(
       scid number(8) primary key,  # 试卷编号
       term varchar2(2) check(term='s1' or term='s2'), # 学期
       stuid varchar2(8), # 学生学号
       exam varchar2(20) not null, # 考试科目
       grade number(4,1) default 0 not null, # 考试成绩
       constraint fk_stu_score_stuid foreign key(stuid) references student(stuid) # 设置外键
);
# 当向成绩表插入数据的时候，成绩表的学生id必须能够在学生表里找到对应的主键id，否则无法插入数据
insert into student values ('10001','测试学生1');
insert into score values (1,'s1','10001','科目1',100);
# 以上可以成功插入数据
insert into score values (1,'s1','10002','科目1',100);
# 不能插入，因为student表中并没有stuid为10002中的对应数据

# 添加外键约束（建表后）
# 建表
create table worker(
	wid number(4) primary key,
    wname varchar2(20),
    manager number(4),
    deptno number(4)
);

create table dep(
	depno number(4) primary key,
    dname varchar2(20)
);
insert into dep values(1,'开发部门');
insert into dep values(2,'测试部门');

# 建立外键约束
alter table 表名 add constraint 外键约束名称 foreign key (字段名) references 主表名(关联字段名);
# 不同表相互关联的外键约束
alter table worker add constraint fk_worker_dep_depno foreign key(deptno) references dep(depno);
# 同一张表内部关联的外键约束
alter table worker add constraint fk_worker_wid_manager foreign key(manager) references worker(wid);

# 插入数据
insert into worker values(1,'员工1',null,1);
insert into worker values(2,'员工2',1,1);

热门相关：首席的独宠新娘大神你人设崩了寂静王冠寂静王冠明月照大江