Oracle第三天
一、回顾
内连接查询:
外连接: 特殊外连接(+)
子查询:返回一个值, 返回一列多行(in , =any ,=some ) , 返回多行多列(作为表使用) , 返回null
exists运算符:和in比较效率较高
分页: 伪列:rownum
--带有排序的分页:先排序, 作为表,再生成行号,座位表,条件筛选
课堂练习: 行列转换(条件表达式(case when ,decode))
集合运算符:交集(intersect),并集(union all,union),差集(minus)
递归查询:
二、学习目标
1. 管理表的DDL语句
2. 创建表&约束
3. DML语句
4. DDL语句(视图,索引,序列,同义词)
5. 数据库的备份
三,管理表的DDL语句
1. 表空间
Oracle体系结构
数据库:只有一个数据库
实例:后台运行一个进程,一个数据库可以对应多个实例
表空间:逻辑存储单位 书
数据文件:物理存储单位,体现dbf文件 纸
用户:面向用户管理,用户管理表空间,向表空间添加数据,最终存储到数据文件中
/*
--1.表空间
表空间创建语法
create tablespace 表空间名称
datafile '文件路径'
size 200M -- 指定文件的大小
autoextend on -- 开启自动扩展 , off
next 20M; -- 每次扩展20M;
*/
(此处注意须在system用户下执行,或者授予scott dba的权限)
create tablespace scottspace
datafile 'c:\scottspace.bdf'
size 200m
autoextend on
next 20m
--表空间的删除语法
--drop tablespace 表空间名(不能删除数据文件)
drop tablespace scottspace
--drop tablespace 表空间名 including contents and datafiles;(能删除数据文件)
drop tablespace scottspace including contents and datafiles
2. 用户
/*
语法:
create user 用户名 identified by 密码
default tablespace 表空间名称;
*/
create user lucy identified by tiger
default tablespace scottspace
(由于Oracle数据库对于权限管理非常严格,所以新新建的用户没有任何权限,这时就需要我们为其授予权限后才可以使用)
--- 权限管理
-- connect : 基本权限:create session
-- resource :开发人员权限,
-- dba :管理员权限
-- 赋予权限的语法
-- grant 权限列表 to 用户;
-- 回收权限
-- revoke 权限列表 from 用户;
grant connect to lucy;
grant resource to lucy;
--查询当前用户所有的权限
select * from session_privs;
revoke resource from lucy;
四、创建表&约束
-- 数据类型:
-- 字符串类型
-- char(10):固定长度字符串 ,最大长度:4000
-- varchar(10):可变长度字符串
-- varchar2(10):可变长度字符串,由oracle公司定义,承诺向后兼容
-- long : 存储量是 2G ,用clob取代
-- 数值类型
number: 整数
number(m,n):浮点数类型, m是总的位数,n 是小数点后的位数
-- 日期类型
-- date :相当于mysql中的datetime
-- timestamp : 时间戳类型,精确到秒后的9位
-- 大数据类型
-- blob: 存储量是4G,字节大数据类型,
-- clob:存储量是4G, 字符大数据类型,
-- 创建表的语法
/*
create table 表名(
列名 类型 约束,
列名 类型 约束,
列名 类型 约束,
....
)
*/
create table student(
sid number primary key,
sname varchar2(100),
age number
);
-- 修改表的操作
--添加列
--alter table 表名 add 列明 类型 约束
alter table student add sex varchar2(22);
--删除列
alter table 表名 drop colunm 列明;
alter table student drop column sex;
--修改列属性
alter table 表名 modify 列名 类型
alter table student modify sname varchar2(200);
--重命名列
alter table 表明 rename column 原列名 to 新列名
alter table student rename column age to gender
--- 约束:保证数据的完整性
-- 主键:唯一,非空
--非空:
-- 唯一
-- 默认
-- 检查
drop table student;
create table t_class(
cid number,
primary key(cid),
cname varchar2(200) not null,
adress varchar2(200) unique,
age number default 19 check(age between 1 and 150),
sex varchar2(50) check(sex in('男','女'))
);
drop table t_class;
-- 外键
-- 班级表和学生表
create table student(
sid number,
primary key(sid),
sname varchar2(200) not null,
cid number,
--设置外键
--constraint 约束名称 foreign key(外键列) references 主表(主键) on delete cascade
constraint fk_class_student foreign key(cid) references t_class(cid)
on delete cascade
);
--级联删除(不推荐)
delete from student where sid=1;
delete from t_class where cid=1;
select * from t_class
select * from student;
--物理外键:在建表时手动创建
--逻辑外键:由Java代码所控制
--强制删除主表
drop table t_class cascade constraint;
五、DML语句
--SQL语句的分类
-- DDL: 数据库定义语言:create drop , alter
-- DML: 数据库操作语言: insert update ,delete
-- DCL: 数据库控制语言:grant ,revoke
-- DQL: 数据库查询语言: select
-- insert into 表名 values();
-- insert into 表名(列名,...) values(值,...)
-- update 表名 set 列= 值, 列=值 where 条件
-- delete from 表 where 条件
-- truncate table 表名; -- 删除所有的记录(效率极高),摧毁表结构,重新建表
-- 事务的特性:原子性,一致性,隔离性,持久性
-- 数据库的隔离级别
-- read uncommited:读未提交
-- read comminted :读已提交
-- repeatable read :重复读
-- serializable : 串行化(序列化)
-- mysql :支持四个隔离级别,默认隔离级别:repeatable read
-- oracle:支持三个隔离级别(read commited ,serializable ,read only)
-- 默认的是:read commited
-- 事务的保存点(了解)
insert into student values(1,'a',1);
savepoint s1;
insert into student values(2,'b',1);
savepoint s2;
insert into student values(3,'c',1);
savepoint s3;
rollback to s2;
commit;
六、DDL语句
1. 视图
--- 视图: 是一张虚表, 不能存储记录,所有的记录都在基本表中 ,可以对视图进行增删改查
-- 语句:
-- create view 视图名 as DQL;
grant dba to scott;(此处由于Scott权限不够,所以需要先授予其dba权限)
-- 视图可以直接把它作为表查询,修改,删除,添加
create view emp_view as select * from emp;
select * from emp_view;
insert into emp_view(empno ,ename) values(1, 'rose');
select * from emp;
-- 作用一:可以屏蔽敏感列
create or replace view employee as
select empno,ename ,job ,mgr,deptno from emp;
select * from employee;
-- 作用二:简化操作
create or replace view employee as
select t.* ,rownum rn from (select * from emp order by sal desc) t
select * from employee where rn between 6 and 9;
-- 作用三:可以定义只读的视图
create or replace view emp_view as select * from emp with read only;
insert into emp_view(empno ,ename) values(2, 'mike');
2. 序列
-- 序列: sequence , 数列, 从1开始,依次递增,没上上限
/*
创建序列的语法
create sequence 序列名;
*/
create sequence emp_seq;
-- 属性: nextval ,currval(必须先执行依次nextval,才能使用)
select emp_seq.nextval from dual;
select emp_seq.currval from dual;
insert into emp(empno ,ename) values(emp_seq.nextval ,'lili');
select * from emp;
-- 完整的语法(了解)
/*
create sequence 序列名
start with 1 起始值
increment by 2 自增量
maxvalue 9999 最大值 ,nomaxvalue
minvalue 1 最小值 ,nominvalue
cycle 开启循环
cache 20 ; 缓存
*/
3. 索引
-- 索引:提高检索的效率
-- 前提:百万条记录以上 , 不经常修改的表
-- 语法: create index 索引名称 on 表名(列,列,...);
-- 添加百万条记录
create table a(
id number primary key,
aname varchar2(100)
);
create sequence a_seq;
select sys_guid() from dual;
declare
begin
for i in 1..1000000 loop
insert into a values(a_seq.nextval, sys_guid());
end loop;
end;
-- 单列索引
-- 添加索引前查询某一条记录: 0.453
select * from a where aname = '10C4668168AE4AECB61E4F8F6B668022';
-- 添加索引
create index a_index on a(aname);
-- 添加索引后查询某一条记录:0.062
select * from a where aname = 'DCC4B29D2E034BC0A7DEA68604CAAC22';
-- 复合索引 触发条件 (name ,address)
-- select * from 表 where name = '' and address = ''可以触发索引
-- select * from 表 where name = '' or address = '' 不可以触发索引
-- select * from 表 where name = '' 可以触发索引
-- select * from 表 where address = '' 不可以触发索引
4. 同义词
-- 同义词: 作用一: 跨用户访问 ,作用二:缩短表名
select * from scott.emp;
-- 创建同义词
--create synonym 同义词名称 for 用户.表;
create synonym emp for scott.emp;
select * from emp;
-- 缩短表名
create synonym e for scott.emp;
select * from e;
七、数据库的备份
-- 数据库的备份:
grant dba to itheima;
-- 导出: exp scott/tiger file='c:/scott.ora' [tables=(dept)]
-- 导入: imp itheima/itheima file='c:/scott.ora'
-- fromuser=scott touser=itheima [tables=(dept)]
Oracle从入门到精通第三天
最新推荐文章于 2024-08-07 07:15:00 发布