1 数据库对象
2 表
2.1 表的数据类型
2.2 表的约束
3 表空间
3.1 表空间是什么
3.2 表空间的语法
3.3 示例代码
4 用户
5 用户授权(DCL)
5.1 系统权限
5.2 对象权限
6 视图
6.1 视图是什么
6.2 视图的语法
6.3 代码示例
6.4 读写视图
7 序列
7.1 索引是什么
7.2 索引的语法
7.3 代码示例
8 索引
8.1 索引是什么
8.2 索引的语法
8.3 示例代码
8.4 创建索引的建议
9 同义词
9.1 同义词是什么
9.2 同义词的语法
1 数据库对象
Oracle中所有具有特殊功能的组件,统称为数据库对象。
所有的数据库对象都是使用DDL(数据定义语言)来创建(create)、修改(alter)和移除(drop)的。
数据库对象分为系统级别的和用户级别的。
- 系统级别:属于Oracle数据库的数据库对象。如:表空间、用户。
- 用户级别:属于某个用户的数据库对象。如:表、视图、序列、索引、同义词。
2 表
表就是一个用于存储数据的数据库对象。
-- 创建表
-- 需求:创建一个tb_user表,用户编号,用户名,年龄
create table tb_user(
-- 创建一个整型,长度为10位的用户编号
user_id number(10) not null primary key,
-- 可变长度的字符串
user_name varchar2(50) not null,
-- 年龄
age number(3)
);
-- 查看当前用户的表
-- tabs是Oracle用于存储当前用户表信息的一个内置表
select * from tabs;
-- 查看表tb_user的结构
desc tb_user;
-- 修改表
-- 增加字段
-- 语法:alter table <表名> add (<字段名> <数据类型>[(大小)][not null],<字段名> <数据类型>[(大小)][not null]...);
-- 需求:增加一个性别的
alter table tb_user add (user_sex char(3) );
-- 更新字段属性
-- 语法:alter table <表名> modify (<字段名> <数据类型>[(大小)][not null],<字段名> <数据类型>[(大小)][not null]...);
-- 需求:更新性别字段的属性为不可以为null
-- 注意:修改是通过字段名来标识修改
alter table tb_user modify (user_sex char(3) not null);
-- 更新字段名
-- 语法:alter table <表名> rename column <旧字段名> to <新字段名>
-- 需求:修改年龄字段age为user_age
alter table tb_user rename column age to user_age;
-- 删除字段
-- 语法:alter table <表名> drop (<字段名1>,<字段名2> ...);
alter table tb_user drop (user_age,user_sex);
-- 删除表
drop table tb_user;
2.1 表的数据类型
类型 | 说明 |
---|---|
varchar2 | 可变长度,字符串类型。使用varchar时Oracle会自动转成varchar2。英文字符占一个字节;GBK编码汉字占2个;UTF-8编码占3个字节。 |
nvarchar2 | 可变长度,字符串类型。使用varchar时Oracle也会自动转成varchar2。 如果是UTF-8,英文字符和汉字都是占个3字节就;GBK编码英文和汉字都是2个字符。 |
char | 固定长度字符串类型。 英文字符占一个字节;GBK编码汉字占2个;UTF-8编码占3个字节。 |
nchar | 固定长度字符串类型。 如果是UTF-8,英文字符和汉字都是占个3字节;GBK编码英文和汉字都是2个字符。 |
boolean | 布尔类型 |
number(p,s) | p表示长度,s表示精度,如果s不写或为0都表示整数,所以s有值时表示浮点型。如: number(10,0):表示一个可以存储10位长度的整数; number(10,2):表示一个整数部分是8位,小数部分是2位的浮点型。p最大值38位。 |
clob | 大文本类型,类似于mysql的text。最大存4G文本。 |
blob | 存储二进制数据,最大也是4G。 |
2.2 表的约束
表的约束有:主键约束(primary key)、非空约束(not null)、检查约束、唯一约束、外键约束。
-- 创建表
create table tb_user(
user_id number(10) not null primary key,
user_name varchar2(50) not null,
user_sex char(3)
);
-- 检查约束:所谓的检查约束就是,限制字段输入的值。
-- 需求:性别,只能输入男或者女
-- 给表加约束,其实就是修改表
-- alter table <表名> add constraint <约束名> check (<字段名 in ('值1','值2'...)>);
alter table tb_user add constraint ch_user_sex check (user_sex in('男','女'));
-- 测试
insert into tb_user values(1,'张三','男');
insert into tb_user values(2,'李四','妖');
-- 唯一约束:用于约束字段的值不能出现重复数据
-- 需求:user_name的名字不可以重复
-- alter table <表名> add constraint <约束名> unique (<字段名1>...)
alter table tb_user add constraint uni_user_name unique (user_name);
--测试
-- 数据库里面已经有张三的名字的记录,如果再插入,就失败。
insert into tb_user values(2,'张三','男');
-- 外键约束
-- 外键等同外键约束
-- 外键的作用是为了让我们快速了解表与表之间关系
-- 外键约束的作用是为了保证数据的完整性
-- 所谓的数据的完整性,就是如果一条数据已经有其他的数据引用了它,就不能删除。必须要将它的子记录删除,才可以删除。
-- 需求:创建一个用户类型表,一个用户表,一个用户类型有多个用户。
-- 用户类型
create table tb_user_type(
type_id number(10) not null primary key,
type_name varchar(50) not null
);
-- 创建表
drop table tb_user;
create table tb_user(
-- 编号
user_id number(10) not null primary key,
user_name varchar2(50) not null,
user_sex char(3),
type_id number(10) not null
);
-- 将用户表的type_id和用户类型表创建外键约束
alter table tb_user add constraint fk_type_id foreign key(type_id) references tb_user_type(type_id);
3 表空间
3.1 表空间是什么
表空间是一个存储用户数据的系统级别的数据库对象。它是Oracle最大的存储单元:每创建一个表空间必须创建一个数据文件。
推论:创建用户一定要有表空间存储该用户的数据。
如果创建用户不指定表空间,系统会自动将用的数据放在USERS表空间里面。
3.2 表空间的语法
-- 创建表空间
create tablespace <表空间名>
-- 属性
-- 用于指定创建的数据文件,注意如果不写路径放在$(ORACLE_HOME)/database
-- 如:F:\app\ranger\product\11.2.0\dbhome_1\database
datafile '路径/文件名.DBF'
-- 指定文件的初始大小
size <大小>
-- 数据超出了文件的大小,是否自动扩展
autoextends on|off --on表示支持,off表示不支持,默认是off
-- 如果支持文件自动扩展,每次扩展的大小,如果不设置默认扩展的就是初始大小
next <大小>;
3.3 示例代码
-- 创建一个表空间
-- 表空间是一个系统级别的数据库对象
-- 所有必须需要使用管理员创建
create tablespace xust_ts
datafile 'xust_ts.dbf'
size 100m
autoextend on
next 100m;
-- 查看表空间
-- 内置表
select * from dba_data_files;
-- 删除
-- 1.逻辑删除:仅仅删除数据文件和数据库的关联。文件不会删除。
drop tablespace xust_ts;
-- 2.物理删除:同时把数据文件也删除
drop tablespace xust_ts including contents and datafiles;
-- 表空间的修改(了解)
-- 表空间是支持一个表空间多个数据文件的。
-- 这样做的好处,就是可以实现一个表空间的数据放在多个硬盘上
-- 给表空间增加一个文件
-- alter tablespace <表空间名> add datafile '路径/文件名.DBF' size <大小>;
alter tablespace xust_ts add datafile 'xust_ts_02.dbf' size 10M;
-- 删除一个文件
-- 注意,有数据的表空间禁止删除,否则会丢失数据
-- alter tablespace <表空间名> drop datafile '路径/文件名.DBF';
alter tablespace xust_ts drop datafile 'xust_ts_02.dbf';
4 用户
-- 创建用户的语法
create user <用户名>
-- 属性
-- 密码
identified by <密码>
-- 指定表空间
default tablespace <表空间名>
-- 注意:用户也是一个系统级别的数据库对象,所以必须使用管理员创建
create user xust
-- 密码
identified by xust
-- 指定表空间
default tablespace xust_ts;
-- 查看用户详细信息
select * from dba_users;
-- 查看用户简单信息
select * from all_users;
-- 修改用户
-- 修改状态
-- 锁定用户
alter user xust account lock;
-- 解锁用户
alter user xust account unlock;
-- 修改密码
-- 语法:alter user <用户名> identified by <新密码>;
alter user xust identified by orcl;
-- 删除用户
-- 加上cascade,删除用户也把用户的数据删除
-- drop user <用户名> cascade
drop user xust cascade;
5 用户授权(DCL)
Oracle是一个非常安全的数据库。用户如果没有授权,是不能做任何操作的,包括登录。
用户的权限的类型有两种:
- 系统权限:就是用户访问自身模式(用户空间)的权限
- 对象权限:就是用户访问另一个用户的模式(用户空间)的数据库对象(表和视图)权限
5.1 系统权限
/*
语法:
grant <系统权限>|<角色> to <用户名>
*/
-- 用户登录的权限
-- 用户的每个一个系统权限都要先授权,后使用
grant create session to xust;
-- 查看系统权限
select * from dba_sys_privs;
-- 有很多的系统权限,如何记住?
-- 规律:
-- create 数据库对象类型
-- 如:创建表 create table
-- alter 数据库对象类型
-- drop 数据库对象类型
-- 注意:用户级别的数据库对象,create权限包括alter,drop
grant create table to xust;
-- 问题:如果系统权限一个一个授予,很麻烦。
-- Oracle通过角色来解决这个问题。一个角色可以有多个系统权限
-- 查看系统的角色
select * from dba_roles;
-- 常用的角色
-- DBA:授予DBA,说明这个是管理员
-- RESOURCE:授予基础操作的权限
-- CONNECT:授予登录的权限
-- 查看角色的权限
select * from dba_sys_privs where grantee='RESOURCE';
select * from dba_sys_privs where grantee='CONNECT';
-- 使用角色授权
grant connect , resource to xust;
-- 查询当前用户的系统权限
select * from user_sys_privs;
-- 查看角色
select * from user_role_privs;
-- 查看当前用户角色权限
select * from role_sys_privs;
-- 系统撤权
-- 语法:revoke <系统权限>|<角色> from <用户名>
revoke resource from xust;
5.2 对象权限
/**
对象权限授权语法:
grant insert,delete,update,select on 被访问的用户名.表名|视图名 to 被授权的用户名
*/
-- 需求:授予xust用户,访问scott用户的emp表的权限
grant select on scott.emp to xust;
select * from scott.emp;
-- 需求:将scott用户的emp,dept,salgrade表复制到xust里面
grant select on scott.emp to xust;
grant select on scott.dept to xust;
grant select on scott.salgrade to xust;
-- 复制表
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
create table salgrade as select * from scott.salgrade;
-- 查看当前用户的对象权限
select * from user_tab_privs;
-- 撤销对象权限语法:revoke insert,delete,update,select on 被访问的用户名.表名|视图名 from 被授权的用户名
-- 撤销xust对scott用户的对象权限
revoke select on scott.emp from xust;
revoke select on scott.dept from xust;
revoke select on scott.salgrade from xust;
6 视图
6.1 视图是什么
视图是一个由一个或者多个表查询的结果组成的虚拟表。组合这个视图的表称为基表。其实就是将查询的语句使用一个数据库对象保存起来。用于实现复杂查询的SQL语句重复使用。
6.2 视图的语法
-- 创建视图
create [or replace] view <视图名> as
select 语句;
-- or replace,如果有这关键字,表示如果视图已经存在,就覆盖
6.3 代码示例
-- 需求:创建一个视图,查询员工的信息和部门信息
-- 视图的结果不能有重名的列
-- 首先授权
grant create view to xust;
create or replace view vw_emp_all as
select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno = dept.deptno;
-- 查看视图
select * from user_views;
-- 使用视图
select * from vw_emp_all;
-- 删除视图
drop view vw_emp_all;
6.4 读写视图(了解)
Oracle里面,单表视图是可以读写的。多表视图是不可以写的,只能读。
读写视图,操作最后影响的是基表的数据。
-- 读写视图(单表视图), 很少用。
-- 用于限制另一个用户访问的字段
create or replace view vw_emp as
select * from emp;
-- 测试
insert into vw_emp(empno,ename) values(2001,'test1');
7 序列
7.1 索引是什么
序列是一个数据库对象级别的计数器。
Oracle不支持像MySQL那样,可以使用一个关键字(auto_increment)支持ID的自增长。 Oracle是通过序列来实现ID的自增长的。
序列的主要作用就是用于实现Oracle的ID自增长。
7.2 索引的语法
create sequence <序列名>
-- 属性
-- 步长
increment by <步长>
-- 开始位置
start with <开始位置>
-------------------------------------------------------------------------
-- 最大值
maxvalue <最大值>
-- 不限制最大值
nomaxvalue
-- 最小值
minvalue
-- 计数大于最大值的时候,是否重新开始
nocycle |cycle nocyle表示不重新开始,cycle超过最大值,重新开始
-- 缓存大小,默认是20
cache <大小>;
7.3 代码示例
-- 需求:创建一个用于emp表的序列
create sequence seq_emp
increment by 1 --步长
start with 1 --开始位置
nomaxvalue --没有最大值
nocycle --如果计数超过了最大值,不重新开始,直接报错
cache 40; --设置缓冲的计数的个数
-- 查看序列
select * from user_sequences;
-- 使用序列
-- 1.获得当前序列值(计数)
-- 格式:序列名.currval
select seq_emp.currval from dual;
-- 2.获得下一个序列值
select seq_emp.nextval from dual;
-- 使用序列插入数据
insert into emp(empno,ename) values(seq_emp.nextval,'s1');
-- 删除序列
drop sequence seq_emp;
-- (了解)当步长为负数时。是倒序序列
create sequence seq_emp_1
increment by -1 --步长
start with 5000 --开始位置
maxvalue 5000 --没有最大值
minvalue 1 --如果到了1,序列就无法往下计数。
nocycle --如果计数超过了最大值,不重新开始,直接报错
cache 40; --设置缓冲的计数的个数
select seq_emp_1.nextval from dual;
8 索引
8.1 索引是什么
索引是一个提高查询效率的数据库对象。
8.2 索引的语法
-- 创建语法
create [unique] index <索引名> on <表名>(<字段名1> asc |desc ,<字段名1>);
-- unique:表示唯一索引,创建唯一索引后,会自动加上唯一约束。
8.3 示例代码
select count(*) from tb_user;
-- 没有索引
select * from tb_user where user_name ='name19990000';
-- 有索引
select * from tb_user_index where user_name ='name19990000';
-- 给emp表的ename创建一个唯一索引
create unique index ind_ename on emp(ename asc);
-- 给emp表的工资创建一个索引,降序
create index ind_sal on emp(sal desc);
-- 查看索引
select * from user_indexes;
-- 删除索引
drop index ind_sal;
8.4 创建索引的建议
- 在经常查询的字段上创建索引
- 表太小不要创建索引。10W
- 字段的类型是大文本类型时,不要创建索引
9 同义词(了解)
9.1 同义词是什么
同义词就是一个数据库对象级别的别名。
作用:缩短表和视图名。
9.2 同义词的语法
-- 同义词
-- create [public] synonym <同义词> for < 表名|视图名>;
-- 需求:创建一个emp表的同义词e
-- 授权
grant create synonym to xust;
create synonym e for emp;
--查询别名
select * from e;
--插入数据
insert into e(empno ,ename ) values(999,'sy1');
-- 同义词属于用户
-- 公用同义词属于Oracle系统
注意:所有内置表都是一个公有同义词。记不清可以在里面找。