Oracle学习(三) — 数据库操作「建议收藏」

• Oracle表与用户关联
• 每一个用户，需要确定表存放位置，这个位置称为表空间
• 在Oracle中表空间分2中：永久表空间、临时表空间
  • 临时表空间：使用SQL语句操作时，结果先进入临时表空间
  • 永久表空间：当执行commit时，数据将从临时表空间到永久表空间。

用户权限和RBAC(了解)

• 赋予用户dba角色

grand dba to 用户名

代码100分

• RBAC(Role-Based Access Control) 基于角色的访问控制
  • 增强权限的可维护性

1、创建用户

• 语法

  代码100分-- 创建用户
  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、创建表空间(永久)

• 基本语法

  代码100分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 查询语句;

• 数据类型

• 实例

  -- 创建学生表
  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、常见约束

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);