文章目录
使用DDL语句管理表
Oracle体系结构
表空间
- 创建表空间
逻辑单位：通常情况下，我们创建一个新的项目就会创建一个表空间，在表空间中创建表
- 创建表空间的语句
create tablespce [表空间名称]
detafile '文件的路径(在服务器上)'
size (大小/m)
autoextend on --自动扩展
next (每次自动扩展的大小/m)
- 练习：在D:盘下创建一个100m的表空间，表空间名为ycxy，设置每次扩展大小为10m
create namespace ycxy
datafile 'D:\ycxy.dbf'
size 100m
autoextend on
next 10m;
- 删除表空间
先在数据库中删除联系,再到硬盘中删除文件
- 删除语句
drop tablespace 表空间名称;
- 练习:删除名为ycxy的表空间
drop tablespace ycxy;
用户
创建用户
create user 用户名
identified by 密码
default tablespace 表空间名称
- create user ：后面是用户名
- identified by：后面是密码
- default tablespace：表空间名称
给用户赋予权限
Oracle中存在三个重要角色
- content角色：授予用户的，最基本的权利
- resource角色：授予开发人员的
- dba角色：
拥有全部特权，是系统最高权限，只有dba才可以创建数据库结构，并且系统权限也需要dba授出，dba可以操控任意用户的任意表，包括删除
角色授权
grant 角色 | 权限 to 用户
创建表
- 数据类型
| 数据类型 | 描述 |
|---|---|
| varchar2(size) | 可变长字符数据 |
| char(size) | 定长字符数据 |
| number（总长度，小数长度） | 可变长数值类型 |
| date | 日期型数据 |
| long | 可变长字符数据，最大可达2G |
| clob | 字符数据，最长可达4G |
| raw and long raw | 原始二进制数据 |
| blob | 二进制数据，最长可达4G |
| bfile | 存储外部文件的二进制数据，最大可达4G |
| rowid | 行地址 |
- 语句
create table 表名(
列1 数据类型 约束,
列2 数据类型 约束,
列3 数据类型 约束
);
修改表
/*
修改表：
添加列
修改列
删除列
修改列名
重命名表
--修改表名
rename test to stu;
--添加一列
alter table stu add phone varchar2(11);
--添加两列
alter table stu add (
mobile varchar2(11),
sex varchar2(2)
);
--修改列名：将sex改为gender
alter table stu rename column sex to gender;
--删除列
alter table stu drop column gender;
--删除表
drop table stu;
--修改列的类型
alter table stu modify sex varchar2(4);
*/
列的约束
- 单表约束
- 主键约束：primary key
- 非空约束：not null
- 唯一约束：unique
- 检查约束：check
- 多表约束
- 外键约束：保证表中的数据必须存在于主表中
语法： constraint 约束名称 foreign key (添加外键的字段) references 主键表(字段)
create table stu(
stu_id number primary key, --主键约束：只能唯一，不能为空
sname varchar2(10) unique, --唯一约束：null值可以多次插入
age varchar2(10) not null, --非空约束
gender varchar2(2) check(gender in ('男','女'))--检查约束
);
insert into stu values(1,'','13','男');
insert into stu values(2,'','13','男');
/*
外键约束：
商品表
商品分类表
constraint 约束名称 foreign key (添加外键的字段) references 主键表(字段)
*/
--创建商品分类表
create table category(
cid NUMBER primary key,
cname varchar2(10)
);
--创建商品表
create table product(
pid number primary key,
pname varchar2(20),
cid number ,
foreign key(cid) references category(cid) --给cid添加外键约束，数据来自于category表的cid字段
);
--创建完成以后再添加外键约束
alter table product add foreign key(cno) references category(cid);
删除表
在建立外键的情况下删除
- 强制删除：先删除外键关联表的外键约束,然后再删除自己, 先删除product的外键约束,再删除category
- 级联删除：添加外键约束,使用级联约束 ,在删除的时候,使用级联删除
--强制删除表：先删除外键关联表的外键约束,然后再删除自己, 先删除product的外键约束,再删除category
drop table product cascade constraint;--在表名后面添加cascade constraint表示强制删除
--级联删除
----添加外键约束,使用级联约束 ,在删除的时候,使用级联删除
alter table product add foreign key(cno) references category(cid) on delete cascade;
--级联删除 : 首先去从表中找有没有 关联数据, 如果在从表中找到关联数据,先删除从表中关联数据,然后再删除表中的数据
delete from category where cid = 2;
插入数据&删除数据&更新数据
/*
插入数据
insert into 表名 values(所有列的值)
insert into 表名 (列一，列二) values(值一，值二)
使用子查询插入数据
insert into 表名 查询语句
*/
/*
更新数据
update 表名 set 列名 = 列的值 where 列的条件
*/
/*
删除数据
delete from 表名 where条件
注意：delete和truncate的差别
truncate：
1. truncate属于DDL：数据定义语言，修改表的结构 alter create drop truncate
2. 先删除表，再创建表
3. 不支持事务操作
4. 执行删除效率高
delete：
1. delete属于DML：数据操纵语言，操作表中的数据 insert update delete
2. 逐条删除
3. 支持事务操作
*/
事务
/*
事务：逻辑上的一组操作，要么都成功，要么都失败
事务的四大特性：
1. 原子性
2. 一致性
3. 隔离性
4. 持久性
如果不考虑隔离级别会发生什么
1. 脏读
2. 虚读
3. 不可重复读
MYSQL隔离级别: READ UNCOMMITTED , READ COMMITTED, REPEATABLE READ, SERIALIAZABLE
ORACLE隔离级别: READ COMMITTED SERIALIZABLE READ ONLY
默认隔离级别: READ COMMITTED
*/
保存点：savepoint
- 案例
create table louti(
lou number primary key
);
/*
一组plsql语句
declare
begin --开始
--要执行的一组语句
exception --捕获异常
when others then
--发生异常时进行的操作
end; --结束
*/
declare
begin
insert into louti values(1);
insert into louti values(2);
insert into louti values(3);
insert into louti values(4);
insert into louti values(5);
savepoint dangban;
insert into louti values(5); --这行代码会发生异常
insert into louti values(6);
commit;
exception --捕获异常
when others then
rollback to dangban;
commit;
end;
视图
/*
视图：是对查询结果的一个封装
视图中的所有数据，都来自于它查询的那张表，视图本身不存储任何数据
1. 能够封装复杂的查询结果
2. 屏蔽表中的细节
语法：create [or replace] view 视图名称 as 查询语句 [with read only]
注意：通常不要通过视图去修改数据，在创建视图的时候通常加上with read only
*/
- demo
--创建一个名为view_test1的视图,(如果之前存在就替换它)数据来自emp表中的ename job sal
create or replace view view_test1 as select ename,job,sal from emp;
select * from view_test1;
create or replace view view_test2 as select ename,job,sal from emp with read only;
update view_test1 set ename = 'SMITH1' where ename = 'SMITH';
update view_test2 set ename = 'SMITH1' where ename = 'SMITH';
同义词
create synonym dept for view_test2; --给view_test2起另外一个名字dept,通过查询dept就可以查询view_test2里的数据
序列
/*
序列: 生成类似于 auto_increment 这种ID自动增长 1,2,3,4,5....
auto_increment 这个是mysql
语法:
create sequence 序列的名称
start with 从几开始
increment by 每次增长多少
maxvalue 最大值 | nomaxvalue
minvalue 最小值 | nominvalue
cycle | nocycle 是否循环 1,2,3,1,2,3
cache 缓存的数量3 | nocache 1,2,3,4,5,6
如何从序列获取值
currval : 当前值
nextval : 下一个值
注意: currval 需要在调用nextval之后才能使用
永不回头,往下取数据, 无论发生异常, 回滚
*/
- demo
--创建一个 1,3,5,7,9......30
create sequence seq_test1
start with 1
increment by 2
maxvalue 30
cycle
cache 10;
select seq_test1.nextval from dual;
select seq_test1.currval from dual;
--序列用的最多的一种写法
create sequence seq_test2;
select seq_test2.nextval from dual;
create sequence seq_test3
start with 1
increment by 2
maxvalue 30
minvalue 0
cycle
cache 10;
select seq_test3.nextval from dual;
- 用法
-- 序列: ORACLE使用来模拟ID自动增长的
create sequence seq_test2;
drop table test2;
create table test2(
tid number primary key,
tname varchar2(10)
)
insert into test2 values(seq_test2.nextval,'张三');
select * from test2;
索引
/*
索引:相当于是一本书的目录,能够提高我们的查询效率
如果某一列,你经常用来作为查询条件,那么就有必要创建索引,数据量比较的情况
语法:
create index 索引的名称 on 表名(列)
注意:主键约束自带主键索引, 唯一约束自带唯一索引
索引原理: btree balance Tree 平衡二叉树
如果某列作为查询条件的时候,可以提高查询效率,但是修改的时候,会变慢
索引创建好之后,过了一段,DBA都会去做重构索引
SQL调优:
1.查看执行计划F5
2. 分析里面的cost 和 影响行数, 想办法降低
*/
- demo
--五百万数据测试
create table wubaiwan(
name varchar2(30),
address varchar2(20)
);
insert into wubaiwan values('')
--插入500000万条数据
declare
begin
for i in 1..5000000 loop
insert into wubaiwan values('姓名'||i,'地址'||i);
end loop;
commit;
end;
--在没有添加索引的情况下,去查询 name='姓名3000000' --2.985
select * from wubaiwan where name='姓名3000000';
--创建索引 name 再去查询 name='姓名3000000'
create index ind_wubaiwan on wubaiwan(name);
select * from wubaiwan where name='姓名3000000'; --0.016
--在没有添加复合索引的情况下,再去查询 name='姓名3000000' and '地址3000000'
select * from wubaiwan where name='姓名3000000' and address='地址3000000'; --0.032
--创建复合索引的情况下, 再去查询
create index ind_wubaiwan2 on wubaiwan(name,address);
select * from wubaiwan where name='姓名3000000' and address='地址3000000'; --0.015
SQL分类
/*
SQL分类
DDL：数据定义语言，修改表的结构 alter create drop truncate
DML：数据操纵语言，操作表中的数据 insert update delete
DCL：数据控制语言，grant
DQL：数据查询语言，select
*/
汇总
/*
DDL表空间操作
创建表空间
创建用户
授权
创建表
子查询创建表
修改表 : 添加列,删除列,修改列,修改列名, 修改表名
约束:
主键约束,唯一约束,非空约束,检查约束,外键约束
外键约束:
强制删除
级联删除
DML表中数据:
插入数据
子查询插入数据
更新数据
删除数据: delete 和 truncate
事务操作:
savepoint 保存点
rollback to 保存点
ORACLE事务隔离级别 : READ COMMITTED
视图: 就像窗户一样, 封装查询结果 , 通常视图创建只读视图
序列: 主要是用来实现ID自增长
索引: 相当于是书的目录,能够提高查询效率, 原理 平衡二叉树, 每隔一段时间DBA都需要去重建索引
同义词: create synonym 名称 for 对象的名称
*/
2724
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
