Oracle入门--外键约束及PL\SQL 图标窗口+DML语句(2)

1.1 外键约束

1.1.1 概述

• 名词解释：

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

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

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

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

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

1.1.3 PL/SQL 图表窗口

• 通过PL/SQL 提供图表窗口(Diagram Window) 进行查看
  

1.2.4 PL/SQL 自动大写

Tools->Preferences->Editor，将Keyword case选择Uppercase

2.1 用户权限和RBAC

• 赋予用户dba角色

grant dba to 用户名

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

• 增强权限的可维护性
  

2.2 表的操作

2.2.1 创建表

• 语法1：创建一个新表

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

• 语法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;

2.2.2 修改表

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

2.2.3 删除表

drop table 表名

2.3 约束

2.3.1 概述

• 什么是约束？

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

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

• Oracle约束和MySQL约束，很多都相同，但也存在部分差异

• 表和约束 关联时机

  • 1.创建表，声明字段时，添加约束
  • 2.创建表，声明字段后，添加约束
  • 3.创建表后，修改表，添加约束

2.3.2 常见约束

关键字	描述	特点
primary key	关键字约束	被关键字约束修饰的列 不允许为空，不允许重复
not null	非空约束	被非空约束修饰的列 不允许为空
unique	唯一约束	被唯一约束修饰的列 不允许重复
default	默认约束	被默认约束修饰的列 如果只为null，使用默认值
foreign key	外键约束	通过主外键关系描述一对多关系
check	检查约束	被检查约束修饰的列 修改数据时，使用检查的条件进行校验

2.3.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;

2.3.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;

2.3.5 检查约束

-- 检查校验
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;

2.3.6 联合主键

• 联合主键：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;

2.4 DML （增删改）

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

2.4.1 添加

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

2.4.2 修改

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

2.4.3 删除

-- 语法1：删除所有
delete from 表名;

-- 语法2：删除指定数据（条件）
delete from 表名 where 条件;

-- 语法3：
truncate table 表名;

• delete 和 truncate 区别：
  • delete 删除表中的数据，支持回滚。
  • truncate 删除表结构，重新创建。