Oracle快速入门（创建、修改、删除）创建数据库、表空间，用户，设置权限，修改表，表的五大约束

一、Oracle的体系结构
数据库————>数据库实例ORCL——>表空间（用户里面订单创建表）——>数据文件
地球 —————>中国————————————> 省份（人民）———————————————>土地山川河流
1
2
在这里插入图片描述

二、创建表空间（逻辑单位，通常我们新建一个项目，就会新建一个表空间，在表空间中创建用户来创建表）
1、创建表空间的语法
create tablespace 表空间的名称
datafile ‘文件路径（服务器上）’,
size 大小,
autoextend on 自动扩展
next 每次扩展的大小
1
2
3
4
5
2、创建表空间（itzheng）
（1）登录系统用户的前提下
在这里插入图片描述

（2）创建表空间的SQL
create tablespace itzheng – 创建表空间名称
datafile ‘c:\itzheng.dbf’ – 创建dbf文件
size 100m – 设置文件大小100m
autoextend on – 设置自动扩展
next 10m; – 设置每次自动扩展10m
1
2
3
4
5
（3）成功创建
在这里插入图片描述

三、删除表空间
1、删除表空间的SQL语句
这条语句删除是逻辑关系并不会把原有的文件删除

drop tablespace itzheng;
1
2、手动删除服务器上创建的文件
在这里插入图片描述
成功删除
在这里插入图片描述

3、再次创建表空间
create tablespace itzheng
datafile ‘c:\itzheng.dbf’
size 100m
autoextend on
next 10m;
1
2
3
4
5
在这里插入图片描述
如果直接删除该文件是无法删除的
在这里插入图片描述
如果要删除需要先删除表关系

四、创建用户（并设置权限）
1、语法（创建用户）
create user 用户名
identified by 密码
default tablespace 表空间的名称
1
2
3
2、创建用户
在itzheng表空间当中创建用户itzhenguser密码为root

create user itzhenguser
identified by root
default tablespace itzheng
1
2
3
3、登录用户，发现无法登录
登录用户itzhenguser
在这里插入图片描述
无法登录：缺少创建会话的权限，登录被拒绝
在这里插入图片描述

4、授予用户权限（后登录）
新创建的用户没有任何权限

（1）Oracle当中以及存在三个重要的角色：connect角色，resource角色，dba角色。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

（2）设置权限：语法
授权 grant 角色 | 权限 to 用户
1
（3）实现语句
grant connect to itzhenguser;
1
（4）登录授予权限的用户
登录itzhenguser
在这里插入图片描述
登录成功
在这里插入图片描述

5、授予dba的角色(登录切换回system用户)
grant dba to itzhenguser;
1
6、设置完权限后(登录切换回itzhenguser用户)
在这里插入图片描述

7、设置完dba权限后查询任意用户的全部的内容
select * from scott.emp;
1
在这里插入图片描述

五、创建表
1、语法
create table 表名(
列名 列的类型（列的约束）,
列名 列的类型（列的约束）
);
1
2
3
4
2、列的类型
varchar ,在Oracle当中，是目前是支持的，但是不保证以后还支持
varchar2(长度) ：可变字符长度 varchar2(10) hello 占5个字符
char(长度)：固定长度字符 char(10) hello 占10个字符，用空格填充
number(总长度：小数长度) 数字类型
date 年月日时分秒
timestamp 时间戳，比date类型更加精确04-SEP-20 06.48.53.991000 PM +08:00

3、创建test1表
create table test1(
name1 varchar2(10),
name2 char(10),
age number(2,3)
);
1
2
3
4
5
4、向test1表当中插入数据
insert into test1(name1,name2) values (‘hello’,‘hello’);
1
5、查询刚创建好的test1表
select * from test1;
1
在这里插入图片描述
可以通过点击光标的方式看出对应的数据的长度
在这里插入图片描述
在这里插入图片描述

6、在开发过程当中要注意的问题
（1）完全匹配查询
第一种情况：可以匹配查询到test1当中的name1的数据

select * from test1 where name1 like ‘hello’;
1
第二种情况：不可以匹配查询到test2当中name2对应的数据，因为name2当中的数据是有长度的并且有空格的，

select * from test1 where name2 like ‘hello’;//查不出数据
1
匹配查询的时候需要在后放加上空格

select * from test1 where name2 like 'hello ';
1
在这里插入图片描述

（2）插入数据（小数长度不能大于等于总长度）
上面在定义的时候，设置总长度为2小数位长度为3（age number(2,3)）

所以下面在插入数据的时候，小数位

insert into test1(age) values(2.333);
1
在这里插入图片描述

（3）Oracle当中的数据类型
在这里插入图片描述
LONG/CLOB:可以存放一本小说。
BLOB（二进制大对象）：存放电影 Java存进去，再读取出来。

7、使用子查询的方式创建表
create table 表名称 as 查询语句；
1
复制了scott用户下的emp表

create table emp as select * from scott.emp;
1
在itzhenguser下查询emp表

select * from emp;
1
在这里插入图片描述

这种方式创建的表是有一个弊端的。

注意：只会复制表结构和表当中的数据，不会复制列的约束。
上面这种方式查询语句是没有任何结果的。

select * from scott.emp where 1=2;
1
在这里插入图片描述
如果查询语句有结果，就是复制 表结果和数据

如果查询语句没有结果，就是复制表结果

所以通过查询出来空语句来通过子查询创建的表是没有任何结果

复制空表的结果还是空

create table emp1 as select * from scott.emp where 1=2;
1
select * from emp1;
1
在这里插入图片描述

六、修改表
1、添加列
(1)创建一张表
create table stu(
stuid number,
sname varchar(10)
);
1
2
3
4
（2）在Command Window窗口当中，查看表结构
在这里插入图片描述
在这里插入图片描述
DDL:数据定义语言，修改结构alter create drop truncate
DML:数据操纵语言，操纵表中的数据 insert update delete
DCL：数据控制语言，grant
DQL :select

（3）添加一列（修改表的结构）
在SQL Windows窗口当中

1
在Command Window窗口当中：修改成功
在这里插入图片描述

（4）添加多列
alter table stu add (mobile varchar2(11),
sex varchar2(2)
);
1
2
3
在这里插入图片描述
在这里插入图片描述

2、修改列的类型
alter table stu modify sex varchar2(4);
1
修改成功
在这里插入图片描述

3、删除列
alter table stu drop column gender;
1
删除成功
在这里插入图片描述

4、修改列名（将sex该为gerder）
alter table stu rename column sex to gender;
1
修改成功
在这里插入图片描述

5、重命名表名
rename stu to student;
1
在这里插入图片描述

6、删除表
drop table student;
1
在这里插入图片描述

七、表的五大约束
列的约束：约束主要用来约束表当中数据的规则

（一）单表约束：primary key 不能为空，必须唯一
（二）多表约束
1、主键约束
（1）先创建表
create table student (
stuid number primary key,
sname varchar2(10) unique,
age varchar2(10) not null,
gender varchar2(4) check(gender in (‘男’,‘女’))
);
1
2
3
4
5
6
select * from student;
1
创建成功
在这里插入图片描述

（2）插入数据
insert into student values(1,‘张三’,‘31’,‘男’);
insert into student values(2,‘李四’,‘31’,‘男’);
1
2
select * from student;
1
在这里插入图片描述

（3）实际测试，违反注主键约束
–主键的约束违反
insert into student values(1,‘张三’,‘31’,‘男’);
insert into student values(1,‘李四’,‘31’,‘男’);
1
2
3
在这里插入图片描述

2、非空约束
违反非空约束

insert into student values(1,‘赵六’,null,‘男’);
1
在这里插入图片描述

3、唯一约束
违反唯一约束

– 唯一约束违反
insert into student values(1,‘王五’,‘31’,‘男’);
insert into student values(2,‘赵六’,‘31’,‘男’);
1
2
3
在这里插入图片描述

4、检查约束
（1）在mysql当中是可以写的，但是mysql是直接忽略了检查约束
（2）在oracle当中违反检测约束
– 检查约束
insert into student values(1,‘李七’,‘31’,‘妖’);
1
2
在这里插入图片描述

5、外键约束
主要是用来约束从表A当中的记录，必须是存在于主表B当中的

（1）商品分类表
create table category(
cid number primary key,
cname varchar2(20)
);
1
2
3
4
（2）创建一个商品表
create table product(
pid number primary key,
pname varchar2(20),
cno number
);
1
2
3
4
5
（3）插入一些数据
在category表当中插入数据

insert into category values(1,‘手机数码’);
1
在没有外键约束的情况下，向product表当中插入没有意义的数据依旧可以插入

insert into product values(10,‘锤子’,11);
1
在category 表当中没有11这个对应的cid

（4）添加外键对其进行约束
alter table product add foreign key(cno) references category(cid);
1
在这里插入图片描述
因为在上面插入的数据就有错误
先删除对应的数据
使用TRUNCATE TABLE用于删除表中的所有行
然后在添加外键约束

alter table product add foreign key(cno) references category(cid);
1
再次插入

（5）再次插入这条没有意义的数据
insert into product values(2,‘锤子’,11);–插入失败
1
在category 表当中没有11这个cid ，所以报错
在这里插入图片描述

（6）插入正确的数据
insert into product values(10,‘锤子’,1);
1
在这里插入图片描述

（7）如果非得在product 插入上面的10,‘锤子’,11号，先在主表中插入数据，在往子表当中插入数据
先向category当中插入

insert into category values(11,‘电脑办公’);
1
在这里插入图片描述
再向product表当中插入数据

insert into product values(2,‘锤子’,11);–插入成功
1
在这里插入图片描述

insert into product values(11,‘外星人’,11)
1
在这里插入图片描述

（8）删除Category
drop table category;
1
表中记录被外键关联，无法删除
在这里插入图片描述

（9）强制删除表（不建议使用）：先删除外键关联表的外键约束，然后再删除自己，先删除product的外键约束，再删除category
drop table category cascade constraint;
1
查询表结构

select * from category;
1
证明已经没有该表
在这里插入图片描述

6、级联删除：首先从表当中找有没有关联数据，如果在从表当中找到关联数据，先删除从表当中关联数据，然后再删除主表当中的
（1）先删除product。
drop table product;
1
这是上面创建的两个表都没有了

（2）重新创建两个新的表。
create table category(
cid number primary key,
cname varchar2(20)
);
create table product(
pid number primary key,
pname varchar2(20),
cno number
);
1
2
3
4
5
6
7
8
9
（2）添加外键约束，使用级联约束。
alter table product add foreign key(cno) references category(cid) on delete cascade;
–在删除的时候使用级联删除
1
2
（3）重新插入一些测试数据。
insert into category values(2,‘办公电脑’);
insert into product values(11,‘外星人’,2);
1
2
（4）添加on delete cascade，在删除的时候使用级联删除
使用级联删除

delete from category where cid = 2;
1
两张表都没有了数据
在这里插入图片描述
在这里插入图片描述

八、插入数据
1、语法
insert into 表名 values(所有列的值都要对应写上)
insert into 表名(列1,列2) values (值1,值2);
1
2
2、使用子查询插入数据
insert into 表名 查询语句
1
3、实际插入数据
在这里插入图片描述

（1）将emp当中的10号部门的员工信息，插入到emp1当中
insert into emp1 select * from emp where deptno = 10;
1
插入成功
在这里插入图片描述

九、更新数据
1、语法
update 表名 set 列名 = 列的值 [where 条件]
1
2、更新数据（修改数据）
update emp1 set ename = ‘HUAAN’ where ename = ‘KING’;
1
修改成功：将KING的名字改为了HUAAN
在这里插入图片描述

十、删除数据
1、语法
delete from 表名 {where 条件}
1
2、实际操作
删除empno 为7839的所在行的数据

delete from emp1 where empno = 7839;
1
在这里插入图片描述

3、delete 和 truncate的区别
delete truncate
DML DDL
逐条删除 先删除表再创建表
支持事务操作 不支持事务操作
通常情况下执行效率较高