Oracle学习(三)数据库操作

  • Z时代
  • 2024-01-10
  • 分类：综合

database

  • Oracle表与用户关联

  • 每一个用户，需要确定表存放位置，这个位置称为表空间

  • 在Oracle中表空间分2中：永久表空间、临时表空间

    • 临时表空间：使用SQL语句操作时，结果先进入临时表空间

    • 永久表空间：当执行commit时，数据将从临时表空间到永久表空间。

用户权限和RBAC(了解)

  • 赋予用户dba角色

grand dba to 用户名

  • RBAC(Role-Based Access Control) 基于角色的访问控制

    • 增强权限的可维护性

1、创建用户

  • 语法

    -- 创建用户
    create user 用户名
    identified by 密码
    default tablespace 表空间
    temproary tablespace 临时表空间
    quota 大小 on 表空间;  //当前用于指定的表空间的限额大小

  • 实例

    -- 创建用户
    create user tqyl
    identified by tqyl
    default tablespace tqyl
    temporary tablespace tqyltemp
    quota 50m on tqyl;
    --赋予用户所有权限(管理员权限dba)
    -- grant dba to 用户名;
    grant dba to tqyl;

2、创建表空间(永久)

  • 基本语法

    create tablespace 表空间名称
    datafile "表空间保存位置"
    size 默认表空间大小
    autoextend on;    -- 空间大小自动增强，如果储存量超过初始大小，则开始自动扩容

  • 实例

    create tablespace tqyl
    datafile "E:workspaceJavaOraclespace	qyl.dbf"
    size 10m
    autoextend on;

  • 删除表空间

    -- 删除表空间
    drop tablespace tqyl
    including contents --删除表内容
    and datafiles;     --删除表空间物理文件

3、创建临时表空间

  • 语法

    • 与创建表空间有2处不一样temporary和tempfile

    -- 创建临时表空间
    create temporary tablespace 临时表空间名称
    tempfile "临时表空间位置"
    size 10m
    autoextend on;

  • 实例

    -- 创建临时表空间
    create temporary tablespace tqyltemp
    tempfile "E:workspaceJavaOraclespace	qyltemp.dbf"
    size 10m
    autoextend on;

4、表的操作

4.1、创建表

  • 创建一个新表

create table 表名(
	字段名   类型   [约束],
    字段名2  类型   [约束],
);

  • 拷贝一个表(结构+数据)

create table 表名 as 查询语句;

  • 数据类型

分类

类型

描述

数值类型

NUMBER

表示整数和浮点数 NUMBER(5) 最大可以存的数为99999 NUMBER(5,2) 最大可以存的数为

INT/INTEGER

整数

FLOAT

双精度浮点数

字符类型

CHAR(n)

定长字符

VARCHAR2（n）

变长字符

日期类型

DATE

日期，精确到秒

TIMESTAMP

日期类型，精确到小数秒

大对象类型

LOB

8-12TB数据 并可分段处理

CLOB

存储字符,最大可以存4个G

BLOB

存储二进制数据,最多可以存4个G

  • 实例

    -- 创建学生表
    create table student(
      stuid int,
      stuname varchar2(20),
      sex char(1),
      age number(3,0),
      birthday date,
      email varchar2(20),
      phone char(11)
    );
    -- 拷贝一个表
    select * from hr.employees;
    create table emp as select * from hr.employees;

4.2、修改表

alter table 表名 具体操作  数据;
-- 添加
alter table 表名 add (列描述,....);
-- 修改
alter table 表名 modify (列描述,....);
-- 删除列
alter table 表名 drop column 列名;

4.3、删除表

drop table 表名

5、约束

5.1、什么是约束？

保证数据库数据的完整性和一致性的一套机制。

  • 我们使用约束限定表中数据(不能随意添加、不能随意修改、不能随意删除)

5.2、常见约束

关键字

描述

特点

primary key

关键字约束

被关键字约束修饰的列 不允许为空，不允许重复

not null

非空约束

被非空约束修饰的列 不允许为空

unique

唯一约束

被唯一约束修饰的列 不允许重复

default

默认约束

被默认约束修饰的列 如果只为null，使用默认值

foreign key

外键约束

通过主外键关系描述一对多关系

==check==

检查约束

被检查约束修饰的列 修改数据时，使用检查的条件进行校验

5.3、主键约束

  • 添加约束

-- 主键约束
-- 方式1：创建表，声明字段时，添加约束 (只能指定一个字段为主键)
create table pk01(
  pid int primary key,
  name varchar2(20)
);
-- 方式2：创建表，声明字段后，添加约束 （可以设置联合主键）【推荐】
create table pk02(
  pid int,
  name varchar2(20),
  constraint pk02_pid primary key (pid)
);
-- 方式3：创建后，修改表结构，添加约束 【推荐】
create table pk03(
  pid int,
  name varchar2(20)
);
alter table pk03 add constraint pk03_pid primary key (pid);

  • 删除约束

-- 删除主键
alter table pk03 drop primary key;

  • 主键验证

    insert into pk01(pid,name) values(1,"jack");  -- 不允许重复执行
    insert into pk01(name) values("jack");        -- 不允许为null
    commit;

5.4、唯一、非空、默认

  • 唯一约束，添加约束方式与主键约束一直，也就是可以采用3种方式

-- 唯一约束 （第三种）
create table un01(
  pid int,
  name varchar2(20)
);
alter table un01 add constraint un01_name unique(name);

  • 唯一验证

insert into un01(pid,name) values(1,"jack");  -- 不允许重复执行
insert into un01(name) values("rose");        -- 可以执行
commit;

  • 非空约束

-- 非空约束
create table nn01(
  pid int,
  name varchar2(20) not null 
);

  • 非空验证

insert into nn01(pid,name) values(1,"jack");  -- 允许重复录入
insert into nn01(pid) values(2);                --不允许为null
commit;

  • 默认约束

-- 默认约束
create table de01(
  pid int,
  name varchar2(20) default("游客")
);

  • 默认验证

insert into de01(pid) values(2);       -- 只录入id，name默认游客
commit;

5.4.1、检查约束

-- 检查校验
create table ch01(
  pid int,
  sex char(10) check( sex in ("男","女") ),
  age int check( age >=0 and age <=100 )
);

  • 检查校验

insert into ch01(pid,sex,age) values(1,"男",18); -- 可以录入
insert into ch01(pid,sex,age) values(2,"妖",18); -- 不允许
commit;

5.5、联合主键

  • 联合主键：2个以上字段，组合在一起为主键，称为联合主键

  • 应用场景：多对多关系中间表

-- 联合主键（中间表）
drop table student_course;
create table student_course(
  sid varchar2(20) ,
  cid varchar2(20) ,
  score number(4,1)
);
alter table student_course add constraint student_course_pk primary key (sid,cid);
insert into student_course(sid,cid,score) values("s001","c001",100); --不允许重复
commit;

6、DML(增删改)

  • DML：数据操作语言，完成数据库增删改操作

6.1、添加

-- 完整用法
insert into 表名(字段1,字段2,...) values(值1,值2,...)
-- 省略用法：值顺序必须与表中的列顺序对应
insert into 表名 values(值1,值2,...)

6.2、修改

-- 语法1：更新表中的所有数据
update 表名 set 字段1=值1,字段2=值2,...
-- 语法2：更新指定数据（条件）
update 表名 set 字段1=值1,字段2=值2,... where 条件

6.3、删除

-- 语法1：删除所有
delete from 表名;
-- 语法2：删除指定数据（条件）
delete from 表名 where 条件;
-- 语法3：
truncate table 表名;

  • delete和trunacte区别：

    • delete 删除表中的数据，支持回滚。

    • truncate删除表结构，重新创建。

7、外键约束

7.1、概述

  • 名词解释：

    • 主表：多表关系中的1表，又称为主表。

    • 主表的主键：1表的primary key 修饰字段的。

    • 从表：多表关系中的多表，又称为从表。

    • 从表的外键：用于存放从表对应的主键主键的字段。

7.2、语法

  • 外键约束：使主表主键和从表的外键，形成主外键关系(也称为一对多关系)

alter table 从表名称 add constraint 外键名称 foreign key (外键名称) references 主表名称 (主表主键)

  • 实例

-- 外键约束练习
-- 1. 创建主表（分类表），必须声明主键
create table tb_category(
  id varchar2(32) primary key,
  name varchar2(50)
);
-- 2. 创建从表（商品表）
create table tb_product(
  id varchar2(32),
  price number(4,1),
  cid varchar2(32)
);
-- 3. 添加主外键关系
alter table tb_product add constraint product_fk foreign key (cid) references tb_category (id);

以上是 Oracle学习(三)数据库操作 的全部内容， 来源链接： utcz.com/z/533843.html

回到顶部