datagrip怎么查看表结构_[基础篇]-数据库-03-数据库的基本操作2-管理表结构

数据库的基本操作2-管理表结构管理表结构（DDL）1.创建自己的用户/*创建自己的普通用户a.拥有管理员权限管理员，以管理员身份登录b.创建自己的用户（设置用户名、密码）c.赋予普通用户权限管理员登录选择’sysdba‘普通用户登录选择’normal‘*/--以管理员身份登录，创建新用户,并赋予用户权限create user hzgg identified...

尧灵ing

7707人浏览 · 2020-12-27 20:47:19

尧灵ing · 2020-12-27 20:47:19 发布

数据库的基本操作2-管理表结构

管理表结构（DDL）

1.创建自己的用户

/*
  创建自己的普通用户
  a.拥有管理员权限管理员，以管理员身份登录
  b.创建自己的用户（设置用户名、密码）
  c.赋予普通用户权限
  管理员登录选择’sysdba‘
  普通用户登录选择’normal‘
*/
--以管理员身份登录，创建新用户,并赋予用户权限
create user hzgg identified by "hzgg"
grant connect,resource to hzgg
--回收权限
revoke connect,resource from hzgg
--删除用户
drop user hzgg cascade

2.表结构和数据类型

选中指定的表，右键选择查看，能够查阅到有关表格的详细信息

数据类型

描述

3.新建表（Create Table）

建表规则

表名和列名

必须以字母开头
必须是1-30个字符
只能包含A-Z a-z _$ 和#
不能和用户定义的其他对象重名
不能是oracle 的关键字
Oracle默认存储都是大写的

CREATE TABLE 语句语法

必须指定:
- 表名
- 列名, 数据类型, 数据类型的大小

要求必须具备以个资源才可以建表:
- CREATE TABLE权限
- 存储空间

create table student(
     sid number(10),        --学生id
     sname varchar2(20),    --学生姓名
     address varchar2(30),  --学生地址
     note1 varchar2(200),   --冗余字段1
     note2 varchar2(200)    --冗余字段2
     );
select * from student;

使用子查询创建表

使用 AS subquery 选项，将创建表和插入数据结合起来

指定的列和子查询中的列要一一对应
通过列名和默认值定义列
使用子查询创建表举例

--利用现有的表创建新的表（用于屏蔽原有表的部分信息，亦可用视图实现）

create table mytable
as
select s.sid,s.sname,s.address
from student s;
select * from mytable;

4.修改表结构（Alter Table）

4.1语法说明

4.2示例

追加一个新列

--1.增加新的列
alter table student add(password varchar2(20));

修改一个列

--2.修改一个已有的列（此处的修改仅仅只是修改列的数据类型）
alter table student add(descr varchar2(20));
alter table student modify(descr varchar2(100));
--修改列的名称（涉及重命名）
alter table student rename column note1 to email;

删除一个列

--3.删除一个列
alter table student drop column note2;

5.清空表（Truncate）

Truncate关键字删除表中所有的数据，释放表空间，但表依然存在的

--4.清空表的数据（只是清空数据，而不删除表的结构）
--insert into mytable  values(1,'小李','杭州');
--形式1：truncate table 表名
truncate table mytable;
select * from mytable;
--形式2：delete 表名
delete mytable;

6.删除表（Drop Table）

drop关键字实现的是将数据和表结构都删除掉，且所有的涉及到的索引全部被删除

truncate清空表仅仅是清空数据表结构依然存在。清空表的时候，所有运行的相关事务都会被自动提交

--5.删除表（删除表的数据、表的结构、表的索引）
drop table mytable;

7.改变对象的名称

执行RENAME语句改变表, 视图, 序列, 或同义词的名称
必须是对象的拥有者

--重命名表、序列、视图、或是同义词
rename student to stu;

8.约束

约束说明

约束是表一级的限制
如果存在依赖关系约束可防止错误数据的删除
约束的类型
Primary key 主键约束
Unique key 唯一约束
Not null 非空约束
Foreign key 外键约束
Check 检查约束
约束的规则：
用户可以自定义约束，使用 oracle server的sys_cn格式命名
约束创建的时机
创建表的时候，同时创建约束
表结构创建完成后，再去添加约束
约束可以定义在列一级，或者是表一级
通过数据字典查询约束
约束按照定义位置划分
- 表级约束：可以将定义完成表之后再去定义约束，同时可以在多个字段上定义
- 列级约束：在定义表的时候就必须定义，这个约束只能定义在一个字段上

约束定义名称格式

非空约束　　　　NN_表名_列名
　　唯一约束　　　　UK_表名_列名
　　主键约束　　　　PK_表名
　　外键约束　　　　FK_表名_列名
　　条件约束　　　　CK_表名_列名
　　默认约束　　　　DF_表名_列名

约束案例分析

主键约束Primary key：
主键约束在表中定义一个主键来唯一确定表中每一行数据的标识符
（主键约束要求对应列满足非空、唯一两个条件）
主键约束可以是列级约束，也可以是表级约束
唯一约束Unique key：
唯一性约束唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的
唯一性约束可以是表级约束，也可以是列级约束，可以在定义表的时候定义约束，也可以定义完成后再定义约束，并且可以同时在多个字段上定义
非空约束Not null：
非空约束只能是列级约束，只能在定义表的时候去定义并且不能同时定义在多个字段上
外键约束Foreign key：
如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。外键是用来实现参照完整性的
外键约束可以是列级约束，也可以是表级约束
检查约束Check：
检查约束是列级约束，也是表级约束定义每一行必须满足check 的条件

案例分析1

创建表格

/*
   创建teacher表：
   tid   教师id         -- 主键  
   tname 教师姓名       --not null
   t_birthday 教师生日  
   要求定义t_birthday不为空，约束名称为t_birthday_nn
*/
create table teacher(
       tid number(10) primary key,  --主键
       tname varchar2(20) not null, --非空
       t_birthday date constraint t_birthday_nn not null
 --为列t_birthday创建非空约束，约束名称为t_birthday_nn
);
select * from teacher;

查看当前创建的约束，可以通过指令查看，也可直接查看创建的表的相关信息

方式1：通过查询数据字典进行查阅-->“user_constranits”

--查阅数据字典，查看相应的索引
--1.查询当前用户定义的所有约束：user_constrains
select *
from user_constraints
where table_name = 'TEACHER';
--p:主键约束
--R:外键约束
--C:检查约束和非空约束
--U:唯一约束

--2.查看约束是建立在哪个字段上：用户字典user_cons_columns 
select *
from user_cons_columns
where table_name = 'TEACHER';

方式2：通过右键点击表名，选择“查看”选项，随后查看相应的“索引选项卡”的内容

数据测试

--数据测试
select * from teacher;
--1.正常插入数据
insert into teacher values(1001,'张老师','13-4月-1998');
insert into teacher values(1002,'白老师','20-6月-1989');

--2.非法插入数据

--a.违反主键约束：主键tid要求唯一、非空
insert into teacher(tname,t_birthday) values('李老师','3-8月-1999');

insert into teacher values(1001,'李老师','3-8月-1999');

--b.违反非空约束：tname、t_birthday要求非空
insert into teacher values(1003,'','3-1月-2000');
insert into teacher values(1004,'小黑','');

案例分析2

创建表格

--创建表格
/*
   创建学生student表
   sid     学生id    --主键,表级约束
   sname   学生姓名  --唯一,表级约束
   sage    学生年龄  --非空,列级约束
   address 学生住址  --非空,列级约束
*/
create table student(
       sid number(10) ,
       sname varchar2(20),
       sage number(4) not null,
       address varchar2(30) not null
);
select * from student;
--在表定义之后创建表级约束
alter table student add constraint sid_pk primary key(sid);
alter table student add constraint sname_un unique(sname);
--查看索引信息
select *
from user_cons_columns
where table_name = 'STUDENT';

--非法操作 :not null不能够定义为表级约束，只能定义为列级约束

alter table student add constraint sage_nn not null(sage);

数据测试

---测试数据
select * from student;
--1.正常插入数据
insert into student values(2015001,'张三',18,'浙江杭州');
insert into student values(2015002,'李四',19,'浙江杭州');

--2.非法插入数据
--a.违反唯一约束：sname  唯一
insert into student values(2015003,'张三',18,'浙江杭州');

--主键约束、非空约束例子参考案例1，此处不作累述

案例分析3

创建表格

--表级约束和列级约束 
/*
  列级约束：在定义列的时候同时创建约束
            列名1 数据类型 约束类型，
            列名2 数据类型 约束类型，
            .......
  表级约束：在列或表定义之后在创建约束
            可对单个字段或者是多个字段添加约束
            a.对字段添加完成后再次添加约束
              constraints 约束名称 约束类型(列名)
              constraints 约束名称 约束类型(列名1，列名2...)
            b.定义完表之后再添加约束
               alter table 表名 add constraints 约束名称 约束类型(列名)
               alter table 表名 add constraints 约束名称 约束类型(列名1，列名2...)
 5种约束中只有not null非空约束为列级约束，其它4种即可为列级约束，又可为表级约束
*/
--创建表格
/*
  创建员工表：employee
  eid     员工id    --主键
  ename   员工姓名  --唯一
  address 员工地址  --非空
*/
create table employee(
  eid number(10),
  ename varchar2(20),
  address varchar2(30) not null,
 --对字段添加完成后再次添加约束,仅限于表级约束
  constraints pk_employee_eid  primary key(eid),
  constraints uk_employee_ename unique(ename)
);
select * from employee;
--查看EMPLOYEE的约束信息
select *
from user_cons_columns u
where u.table_name = 'EMPLOYEE';

/*
  创建顾客表：customer
  cid     顾客id    --主键
  cname   顾客姓名  
  address 顾客地址  
  由ename与address作为联合约束条件，约束类型为unique
*/
create table customer(
  cid number(10) primary key,
  cname varchar2(20),
  address varchar2(30) not null
);
select * from customer;
--在表定义完之后为customer中的指定列创建约束条件，仅限于表级约束
alter table customer 
add constraint uk_customer_cname_address unique(cname,address);
select * from customer;
--查看CUSTOMER的约束信息
select *
from user_cons_columns u
where u.table_name = 'CUSTOMER';

数据测试

--数据测试
--1.正常插入数据
--employee表中插入数据
insert into employee values(1001,'张三','浙江杭州');
insert into employee values(1002,'李四','广东惠州');
select * from employee;

--customer表中插入数据
insert into customer values(1001,'张三','浙江杭州');
insert into customer values(1002,'李四','广东惠州');
select * from customer;

--2.非法插入数据
--employee表中ename为‘唯一’，因此插入失败
insert into employee values(1003,'张三','浙江温州');

--customer表中将cname与address联合作为唯一约束，要满足两个字段同时不同

案例分析4

创建表格

--创建表格
/*
  创建部门表deptments
  dept_id     部门id     --主键
  dept_name   部门名称   --not null
  address     部门地址   --not null
*/
create table departments(
       dept_id number(10) primary key,
       dept_name varchar2(20) not null,
       address varchar2(30) not null
);
select * from departments;

/*
   创建员工表employees
   employee_id   员工id      --主键
   employee_name 员工姓名    --not null
   salary        员工工资    --not null
   dept_id       员工部门id  --创建外键
*/
create table employees(
       employee_id number(10) primary key,
       employee_name varchar2(20) not null,
       salary number(10) not null,
       dept_id number(10),
       constraint fk_employees_departments 
       foreign key(dept_id) references departments(dept_id)
);
--查看创建的约束信息
select *
from user_cons_columns
where table_name = 'EMPLOYEES';

/*
  亦可定义完表之后创建外键约束
  创建格式：
  alter table 表名
  add constraint 约束名称
  foreign key（列名）references 引用表名（引用列名）
  举例分析：
  alter table employees
  add constraint fk_employees_departments 
  foreign key(dept_id) references departments(dept_id);
*/
数据测试
--数据测试
--a.增加数据
select * from departments;
select * from employees;
--1.正常插入数据
--在departments部门表中添加数据
insert into departments values(10,'人事部','浙江杭州');
insert into departments values(20,'财务部','浙江温州');
insert into departments values(30,'开发部','浙江台州');
--在employees员工表中添加数据
insert into employees values(1001,'小李',10000,10);
insert into employees values(1002,'小白',8000,20);
insert into employees values(1003,'小黑',12000,30);
--2.非法插入数据
--插入部门id为不存在的部门的员工信息
insert into employees values(1004,'小张',3000,50);

--b.删除数据
--删除问题：
--a.删除没有员工的部门：此处以40号部门为例，成功删除！
delete 
from departments
where dept_id = 40;

--b.删除存在员工的部门：此处以10号部门为例，删除出错！
delete 
from departments
where dept_id = 10;

--b.删除数据
--删除问题：
--1.删除没有员工的部门：此处以40号部门为例，成功删除！
delete 
from departments
where dept_id = 40;
--2.删除存在员工的部门：此处以10号部门为例，删除出错！
delete 
from departments
where dept_id = 10;
--c.引入“级联删除”概念
/*
   根据不同业务需求,在创建外键的时候设置相应的删除方式
   a.如果部门删除,但是公司正常运行,只需要把员工的部门设置为空即可
     不会删除相应的员工信息
     on delete set null  在删除父项同时设置子项内容为空
   b.如果部门删除 但是公司一并解散   
     在删除部门的同时一并删除员工信息
     on delete cascade   级联删除
*/
--重新创建emlpoyees表，设置相应的外键约束，插入数据，测试不同的删除方式
drop table employees;
create table employees(
       employee_id number(10) primary key,
       employee_name varchar2(20) not null,
       salary number(10) not null,
       dept_id number(10),
       constraint fk_employees_departments 
       foreign key(dept_id) references departments(dept_id) 
 -- on delete set null
       on delete cascade
);
select * from employees;
--插入数据
insert into departments values(10,'人事部','浙江杭州');
insert into employees values(1001,'小李',10000,10);
--删除数据
delete 
from departments
where dept_id = 10;
--查询相应的部门信息
select * from departments;
/*
   结果分析：
   在删除含有员工的部门时，分别对应以下两种情况
   a.on delete set null：删除部门，不删除员工信息，部门置空
   b.on delete cascade：删除部门及其对应的所有员工信息
*/

案例分析5

创建表格

--创建表格
/*
   创建人员表person
   pid      人员id    --主键
   pname    人员名称  --not null
   age      年龄      --0<=age<=150
   gender   性别      --男、女
   dept_id  部门编号  --10,20,30     
*/
create table person(
       pid number(10) primary key,
       pname varchar2(20) not null,
       age number(3) check(age>=0 and age<=150),
       gender varchar2(4),
       dept_id number(10),
 --定义完列名之后定义表级约束
       constraint ck_person_gender check(gender='男' or gender='女')
);
select * from person;
--在定义表之后在创建约束
alter table person
add constraint ck_person_dept_id  check(dept_id in (10,20,30,40));
--查看创建的约束
select *
from user_cons_columns
where table_name = 'PERSON';

数据分析

--数据测试
--1.正常插入数据
insert into person values(1001,'张三',18,'男',10);
insert into person values(1002,'小吴',50,'男',20);
insert into person values(1003,'小燕',29,'女',30);
--2.非法插入数据
--a.年龄超出范围
insert into person values(1004,'小李',151,'男',40);

--b.性别超出范围
insert into person values(1004,'小李',27,'中',40);

--c.部门编号超出范围
insert into person values(1004,'小李',27,'男',50);

数据字典

Oracle中数据库表可以分为用户自定义的表和数据字典两类

用户自定义的表由用户自己创建并维护，其中保存了用户的信息
数据字典表是由oracle自动创建并维护的一组表

数据字典表主要分为三类

DBA—所有的方案的对象
ALL—当前用户可以访问的所有对象
User—当前用户可以访问的对象信息

--数据字典
--a.查看当前用户的缺省表空间 
select username,default_tablespace from user_users;

--b.查看当前用户的角色 (系统赋予了什么权限)
select * from  user_role_privs;

--c.查看当前用户的系统权限和表级权限 
select * from user_sys_privs;

select * from user_tab_privs;

--查看信息
--a.查看当前用户创建的所有表
select table_name from user_tables;

--b.查看当前用户可以访问的表 
select table_name from all_tables;

约束管理

下面约束只能定义在列级的约束是：unique、not null、foreign key 、 primary key

分析：not null非空约束只能定义为列级约束，其余4种约束（主键约束、唯一约束、检查约束、外键约束）均可以通过列级约束或者事表级约束实现创建

约束的查询

--约束查询
--查询数据字典：查询当前用户创建的所有约束user_constraints
select *
from user_constraints;
--查询用户字典:需要查看约束是建立在哪个字段上user_cons_columns 
select  * 
from user_cons_columns;
--可以使用where条件限制选择要查询的条件
创建表之后添加约束
在创建表之后再添加约束针对的是表级约束，not null不能够用这种方式进行定义，其只能用列级约束
定义格式：
alter table 表名
add constraint 约束名称 约束类型(列名);
针对外键约束定义的格式：
alter table 表名
add constraint 约束名称 
foreign(列名) references 引用表(引用列名);

删除约束

删除约束格式：

alter 表名
drop constraint 约束名;

--删除约束

alter table person 
drop constraint ck_person_dept_id
select *
from user_constraints
where table_name = 'PERSON';

完整SQL参考：

/*
  创建自己的普通用户
  a.拥有管理员权限管理员，以管理员身份登录
  b.创建自己的用户（设置用户名、密码）
  c.赋予普通用户权限
  管理员登录选择’sysdba‘
  普通用户登录选择’normal‘
*/
--以管理员身份登录，创建新用户,并赋予用户权限
create user hzgg identified by "hzgg"
grant connect,resource to hzgg
--回收权限
revoke connect,resource from hzgg
--删除用户
drop user hzgg cascade


--以普通用户身份登录用户“normal”,随后进入操作

--创建表格
/*    
   在创建表格设置字段的时候需要注意以下几个问题
   a.特殊情况的考虑
   b.设置合理的字段长度
   c.设置合理的冗余字段（在之后的程序改进中可能会用到的字段）
*/
create table student(
     sid number(10),        --学生id
     sname varchar2(20),    --学生姓名
     address varchar2(30),  --学生地址
     note1 varchar2(200),   --冗余字段1
     note2 varchar2(200)    --冗余字段2
     );
select * from student;

--利用现有的表创建新的表（用于屏蔽原有表的部分信息，可用视图实现）
create table mytable
as
select s.sid,s.sname,s.address
from student s;
select * from mytable;

--修改表的结构
--1.增加新的列
alter table student add(password varchar2(20));

--2.修改一个已有的列（此处的修改仅仅只是修改列的数据类型）
alter table student add(descr varchar2(20));
alter table student modify(descr varchar2(100));

--修改列的名称（涉及重命名）
alter table student rename column note1 to email;

--3.删除一个列
alter table student drop column note2;

--4.清空表的数据（只是清空数据，而不删除表的结构）
--insert into mytable  values(1,'小李','杭州');
truncate table mytable;
select * from mytable;
delete mytable;

--5.删除表（删除表的数据、表的结构、表的索引）
drop table mytable;

--重命名表、序列、视图、或是同义词
rename student to stu;



--约束
--案例分析1
/*
   创建teacher表：
   tid   教师id         -- 主键  
   tname 教师姓名       --not null
   t_birthday 教师生日  
   要求定义t_birthday不为空，约束名称为t_birthday_nn
*/
create table teacher(
       tid number(10) primary key,  --主键
       tname varchar2(20) not null, --非空
       t_birthday date constraint t_birthday_nn not null
       --为列t_birthday创建非空约束，约束名称为t_birthday_nn
);
select * from teacher;

--查阅数据字典，查看相应的索引
--1.查询当前用户定义的所有约束：user_constrains
select *
from user_constraints
where table_name = 'TEACHER';
--P代表主键约束  U代表唯一键约束  
--C代表检查和非空约束   R代表外键约束
--2.查看约束是建立在哪个字段上：用户字典user_cons_columns 
select *
from user_cons_columns
where table_name = 'TEACHER';

--数据测试
select * from teacher;
--1.正常插入数据
insert into teacher values(1001,'张老师','13-4月-1998');
insert into teacher values(1002,'白老师','20-6月-1989');
--2.非法插入数据
--a.违反主键约束：主键tid要求非空、唯一
insert into teacher(tname,t_birthday) values('李老师','3-8月-1999');
insert into teacher values(1001,'李老师','3-8月-1999');
--b.违反非空约束：tname、t_birthday要求非空
insert into teacher values(1003,'','3-1月-2000');
insert into teacher values(1004,'小黑','');

--------------------------------------------
--案例分析2
--创建表格
/*
   创建学生student表
   sid     学生id    --主键,表级约束
   sname   学生姓名  --唯一,表级约束
   sage    学生年龄  --非空,列级约束
   address 学生住址  --非空,列级约束
*/
create table student(
       sid number(10) ,
       sname varchar2(20),
       sage number(4) not null,
       address varchar2(30) not null
);
select * from student;
drop table student;
--查看索引信息
select *
from user_cons_columns
where table_name = 'STUDENT';
--在表定义之后创建表级约束
alter table student add constraint sid_pk primary key(sid);
alter table student add constraint sname_un unique(sname);
--非法操作 :not null不能够定义为表级约束，只能定义为列级约束
alter table student add constraint sage_nn not null(sage);

--测试数据
select * from student;
--1.正常插入数据
insert into student values(2015001,'张三',18,'浙江杭州');
insert into student values(2015002,'李四',19,'浙江杭州');
--2.非法插入数据
--a.违反唯一约束：sname  唯一
insert into student values(2015003,'张三',18,'浙江杭州');
--主键约束、非空约束例子参考案例1，此处不作累述

--------------------------------------------------
--案例分析3：表级约束与列级约束
--表级约束和列级约束 
/*
  列级约束：在定义列的时候同时创建约束
            列名1 数据类型 约束类型，
            列名2 数据类型 约束类型，
            .......
  表级约束：在列或表定义之后在创建约束
            可对单个字段或者是多个字段添加约束
            a.对字段添加完成后再次添加约束
              constraints 约束名称 约束类型(列名)
              constraints 约束名称 约束类型(列名1，列名2...)
            b.定义完表之后再添加约束
               alter table 表名 add constraints 约束名称 约束类型(列名)
               alter table 表名 add constraints 约束名称 约束类型(列名1，列名2...)
   5种约束中只有not null非空约束为列级约束，其它4种即可为列级约束，又可为表级约束
*/
--创建表格
/*
  创建员工表：employee
  eid     员工id    --主键
  ename   员工姓名  --唯一
  address 员工地址  --非空
*/
create table employee(
  eid number(10),
  ename varchar2(20),
  address varchar2(30) not null,
  --对字段添加完成后再次添加约束,仅限于表级约束
  constraints pk_employee_eid  primary key(eid),
  constraints uk_employee_ename unique(ename)
);
select * from employee;
--查看EMPLOYEE的约束信息
select *
from user_cons_columns u
where u.table_name = 'EMPLOYEE';
/*
  创建顾客表：customer
  cid     顾客id    --主键
  cname   顾客姓名  
  address 顾客地址  
  由ename与address作为联合约束条件，约束类型为unique
*/
create table customer(
  cid number(10) primary key,
  cname varchar2(20),
  address varchar2(30) not null
);
select * from customer;
--在表定义完之后为customer中的指定列创建约束条件，仅限于表级约束
alter table customer 
add constraint uk_customer_cname_address unique(cname,address);
select * from customer;
--查看CUSTOMER的约束信息
select *
from user_cons_columns u
where u.table_name = 'CUSTOMER';

--数据测试
--1.正常插入数据
--employee表中插入数据
insert into employee values(1001,'张三','浙江杭州');
insert into employee values(1002,'李四','广东惠州');
select * from employee;
--customer表中插入数据
insert into customer values(1001,'张三','浙江杭州');
insert into customer values(1002,'李四','广东惠州');
insert into customer values(1003,'张三','浙江温州');
insert into customer values(1004,'王五','浙江杭州');
select * from customer;
--2.非法插入数据
--employee表中ename为‘唯一’，因此插入失败
insert into employee values(1003,'张三','浙江温州');
--customer表中将cname与address联合作为唯一约束，只要两个字段中有一个不同即可
--即如果两个字段完全相同方不能插入
insert into employee values(1005,'张三','浙江杭州');


------------------------------------------
--案例分析4：主键约束、外键约束测试
--创建表格
/*
  创建部门表deptments
  dept_id     部门id     --主键
  dept_name   部门名称   --not null
  address     部门地址   --not null
*/
create table departments(
       dept_id number(10) primary key,
       dept_name varchar2(20) not null,
       address varchar2(30) not null
);
select * from departments;

/*
   创建员工表employees
   employee_id   员工id      --主键
   employee_name 员工姓名    --not null
   salary        员工工资    --not null
   dept_id       员工部门id  --创建外键
*/
create table employees(
       employee_id number(10) primary key,
       employee_name varchar2(20) not null,
       salary number(10) not null,
       dept_id number(10),
       constraint fk_employees_departments 
       foreign key(dept_id) references departments(dept_id)
);
--查看创建的约束信息
select *
from user_cons_columns
where table_name = 'EMPLOYEES';
/*
  亦可定义完表之后创建外键约束
  创建格式：
  alter table 表名
  add constraint 约束名称
  foreign key（列名）references 引用表名（引用列名）
  举例分析：
  alter table employees
  add constraint fk_employees_departments 
  foreign key(dept_id) references departments(dept_id);
*/

--数据测试
--a.增加数据
select * from departments;
select * from employees;
--1.正常插入数据
--在departments部门表中添加数据
insert into departments values(10,'人事部','浙江杭州');
insert into departments values(20,'财务部','浙江温州');
insert into departments values(30,'开发部','浙江台州');
insert into departments values(40,'后勤部','浙江杭州');
--在employees员工表中添加数据
insert into employees values(1001,'小李',10000,10);
insert into employees values(1002,'小白',8000,20);
insert into employees values(1003,'小黑',12000,30);
--2.非法插入数据
--插入部门id为不存在的部门的员工信息
insert into employees values(1004,'小张',3000,50);


--b.删除数据
--删除问题：
--a.删除没有员工的部门：此处以40号部门为例，成功删除！
delete 
from departments
where dept_id = 40;
--b.删除存在员工的部门：此处以10号部门为例，删除出错！
delete 
from departments
where dept_id = 10;
--c.引入“级联删除”概念
/*
   根据不同业务需求,在创建外键的时候设置相应的删除方式
   a.如果部门删除,但是公司正常运行,只需要把员工的部门设置为空即可
     不会删除相应的员工信息
     on delete set null  在删除父项同时设置子项内容为空
   b.如果部门删除 但是公司一并解散   
     在删除部门的同时一并删除员工信息
     on delete cascade   级联删除
*/
--重新创建emlpoyees表，设置相应的外键约束，插入数据，测试不同的删除方式
drop table employees;
create table employees(
       employee_id number(10) primary key,
       employee_name varchar2(20) not null,
       salary number(10) not null,
       dept_id number(10),
       constraint fk_employees_departments 
       foreign key(dept_id) references departments(dept_id) 
      -- on delete set null
       on delete cascade
);
select * from employees;
--插入数据
insert into departments values(10,'人事部','浙江杭州');
insert into employees values(1001,'小李',10000,10);
--删除数据
delete 
from departments
where dept_id = 10;
--查询相应的部门信息
select * from departments;
/*
   结果分析：
   在删除含有员工的部门时，分别对应以下两种情况
   a.on delete set null：删除部门，不删除员工信息，部门置空
   b.on delete cascade：删除部门及其对应的所有员工信息
*/

----------------------------------------------------------------
--案例分析5：检查约束
--创建表格
/*
   创建人员表person
   pid      人员id    --主键
   pname    人员名称  --not null
   age      年龄      --0<=age<=150
   gender   性别      --男、女
   dept_id  部门编号  --10,20,30     
*/
create table person(
       pid number(10) primary key,
       pname varchar2(20) not null,
       age number(3) check(age>=0 and age<=150),
       gender varchar2(4),
       dept_id number(10),
       --定义完列名之后定义表级约束
       constraint ck_person_gender check(gender='男' or gender='女')
);
select * from person;
--在定义表之后在创建约束
alter table person
add constraint ck_person_dept_id  check(dept_id in (10,20,30,40));
--查看创建的约束
select *
from user_cons_columns
where table_name = 'PERSON';
--数据测试
--1.正常插入数据
insert into person values(1001,'张三',18,'男',10);
insert into person values(1002,'小吴',50,'男',20);
insert into person values(1003,'小燕',29,'女',30);
--2.非法插入数据
--a.年龄超出范围
insert into person values(1004,'小李',151,'男',40);
--b.性别超出范围
insert into person values(1004,'小李',27,'中',40);
--c.部门编号超出范围
insert into person values(1004,'小李',27,'男',50);

----------------------------------------------------------------------
--数据字典
--查看当前用户的缺省表空间 
select username,default_tablespace from user_users; 
--查看当前用户的角色 (系统赋予了什么权限)
select * from  user_role_privs; 
--查看当前用户的系统权限和表级权限 
select * from user_sys_privs; 
select * from user_tab_privs;

--a.查看当前用户创建的所有表
select table_name from user_tables;
--b.查看当前用户可以访问的表 
select table_name from all_tables;

--约束查询
--查询数据字典：查询当前用户创建的所有约束user_constraints
select *
from user_constraints;
--查询用户字典:需要查看约束是建立在哪个字段上user_cons_columns 
select  * 
from user_cons_columns;
--可以使用where条件限制选择要查询的条件

--删除约束
alter table person 
drop constraint ck_person_dept_id
select *
from user_constraints
where table_name = 'PERSON';

DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐