数据库表操作【oracle】

数据库表操作

数据库表是基本的数据存储集合，由行和列组成。

表名和列名遵循如下命名规则：

• 必须以字母开头

• 必须在 1–30 个字符之间

• 允许包含的字符: A–Z, a–z, 0–9, _, $, 和 #

• 不能和用户定义的其他对象（表）重名

• 不能是Oracle 的保留字

• Oracle默认存储表名是都是大写

表的创建

创建一张表必须具备：

1. Create Table的权限
2. 表空间的使用权限。

如果没有这两个权限, 可以向dba申请授权。

• 数据库表中字段的数据类型

  数据库类型	描述
  VARCHAR2(size)	可变长字符数据
  CHAR(size)	定长字符数据
  NUMBER(p,s)	可变长数值数据
  DATE	日期型数据
  LONG	可变长字符数据,最大可达2G
  CLOB	字符数据，最大可达4G
  RAW and LONG RAW	原始二进制数据
  BLOB	二进制数据，最大可达4G
  BFILE	存储外部文件的二进制数据，最大可达4G
  ROWID	行地址

创建数据库表

-- 语法
CREATE TABLE 表名称
(
列名称1 数据类型 [default 值],
列名称2 数据类型 [default 值],
列名称3 数据类型 [default 值],
....
)

• 创建表

• 创建表时指定默认值

• 通过一个已经存在的创建一个结构相同的表

-- 通过一个已经存在的创建一个结构相同的表
create table xxx as select * from yyy where 1=2;

表的修改

• 添加一个新列

alter table 表名 add 列名 类型;

• 修改列属性

alter table 表名 modify 列名 类型;

• 列名重命名

alter table 表名 rename column 旧列名 to 新列名;

• 删除列

alter table 表名 drop column 列名;

表的重命名和删除

• 表的重命名

rename 旧名 to 新名;

• 表的删除

drop table 表名; -> 可以闪回
-- 闪回
flashback table 表名 to before drop;

drop table 表名 purge; -> 不可闪回

表约束

表约束: 约束的是表中的字段(列)

表约束的种类:

• not null -> 非空
• unique -> 唯一
• check -> 检查性约束
• primary key -> 主键约束 (非空 + 唯一)
• foreign key -> 外键约束
  • 当前字段在取值的时候, 这个值在另外一个表中某个字段值是对应的

create table dept_back as select * from dept;


-- alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）
-- not all 不能通过这个命令进行约束的指定
alter table dept_back add constraint pk_detp_back primary key (deptno);

-- 删除列的约束
alter table 表名 drop constraint 约束名;

表约束的添加

create table persion(
    id number,			-- 主键
    age number,			-- 非空
    sex varchar(5),		-- 检查约束	
    name varchar(20),	-- 唯一
    deptno number		-- 外键
);

• 表级别约束,约束是额外写到create table 中的

create table test(

id number,constraint 约束的名字 约束的条件(字段名)`

)

表级别的约束示例：

-- 通过表级约束添加
-- 不能添加 not null 的表级约束
create table persion(
    id number,			-- 主键
    age number,			-- 非空
    sex varchar(5),		-- 检查约束	
    name varchar(20),	-- 唯一
    deptno number,		-- 外键
    constraint pkxxxx_id primary key(id),
    constraint ckxxx_sex check(sex in('man','woman')),
    constraint unxxxx_name unique(name),
    -- detp 表中的deptno字段作为了当前表中deptno的外键
    -- 当前表中的detpno的取值受 dept 表中 deptno 值的约束
    constraint fk_xxxxdeptno foreign key(deptno) references dept(deptno)
);

• 列级别的约束: 约束是写在 创建的列的后边

  列级别约束设置

-- 自动添加约束的名字
create table persion(
    id number primary key,			-- 主键
    age number not null,			-- 非空
    sex varchar(5) check(sex in('man', 'woman')),		-- 检查约束	
    name varchar(20) unique,	-- 唯一
    deptno number references dept(deptno)		-- 外键
);

-- 自己指定约束的名字
create table persion(
    id number constraint pk_num_id primary key,			-- 主键
    age number constraint nn_age_xxx not null,			-- 非空
    sex varchar(5) constraint ck_sex_xxx check(sex in('man', 'woman')),		-- 检查约束	
    name varchar(20) constraint un_name_xxx unique,	-- 唯一
    -- 如果dept表中的deptno的字段值作为外键被其他表引用
    -- 在dept表中删除被使用的这个行, 行里边有被使用的字段
    -- 如果就是像删除添加 on delete set null
    deptno number constraint fxxk_deptno_xxx references dept(deptno) on delete set null	-- 外键
);

其他oracle对象

• 视图

视图是一张虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成，视图不是真实存在磁盘上的。

• 视图不占用磁盘空间, 不是真实的表, 可以作为表来使用
• 可以通过视图查询数据表中的数据
• 视图是对一系列查询操作的封装

创建视图

-- 创建视图
create view  视图名
as
select 列名|表达式 from 表名 where 条件... [with read only] 

示例

select d.dname "部门名称", 
	   e.ename "员工",
       e.job "职务",
       e.sal "原工资",
       case e.job
           when '船长' then
            e.sal + 1000
           when '厨师' then
            e.sal + 800
           else
            e.sal + 400
       end "涨后工资"
  from emp e, dept d where e.deptno=d.deptno;
  
  
-- 将上边的操作封装成一视图
create view salnew as
  select d.dname "部门名称", 
	   e.ename "员工",
       e.job "职务",
       e.sal "原工资",
       case e.job
           when '船长' then
            e.sal + 1000
           when '厨师' then
            e.sal + 800
           else
            e.sal + 400
       end "涨后工资"
  from emp e, dept d where e.deptno=d.deptno;

删除视图

-- 删除视图
drop view 视图名	

查询视图

-- 查询视图
select * form salnew

查看创建出的视图

-- 查看当前用户下的表
select vtable_name from user_tables; == select * from tab;

-- 查看当前用户下创建的视图
select view_name from user_views;

• 视图与表的区别

  1. 表需要占用磁盘空间，视图不需要, 但是视图可以当做表来使用

     查询视图和查询表的方式相同

  2. 视图不能添加索引(所以查询速度略微慢点)

  3. 使用视图可以简化复杂查询

  4. 视图的使用可以限制数据访问, 有利于提高安全性

• 总结:

  • 提供视图的目的是为了简化表的查询

  • 不建议通过视图对表进行修改

  • 不允许通过视图修改表的数据, 可以创建只读视图

视图创建需要有相关权限

-- 一般是在某个用户下创建视图, 这个用户有可能没有创建视图的权限
-- 需要sysdba给这个用户授权

-- 切换到dba --> sys用户
conn / as sysdba;
-- 授权
grant create view to 用户名; -- 授权创建视图
grant create table to 用户名; -- 授权可以创建表

-- 回收授权
revoke create view from 用户名;

索引

通俗的来讲，索引在表中的作用，相当于书的目录对书的作用。使用索引能够提高数据的查询效率。

当用户创建索引, oracle会维护一个索引表, 该表记录了创建索引使用的列和rowid的对应关系, 查询的时候可以通过列的值找到对应的行地址.

创建索引

create index 索引名 on 表名(列名1, 列名2, ...);

create index index_empno on tmp(empno, ename);

查看索引

select index_name from user_indexes;

删除索引

drop index 索引名;

• 注意: 要在where条件后边使用创建索引的列

序列

序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。

其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

创建序列

-- 创建序列
CREATE SEQUENCE 序列名
　　[INCREMENT BY n]   -- 序列的步长，可省略默认为1，如果出现负值， 按照此步长递减
　　[START WITH n]     -- 序列的初始值(即产生的第一个值)，默认为1
　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}] -- MAXVALUE 定义序列生成器能产生的最大值
　　									   -- MINVALUE定义序列生成器能产生的最小值
　　									   -- NOMAXVALUE是默认选项， 没有最大和最小值
　　[{CYCLE|NOCYCLE}]	    -- CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环，默认不循环
　　[{CACHE n| NOCACHE}]; -- CACHE(缓冲)定义存放序列的内存块的大小，默认为20。
　　                      -- NOCACHE表示不对序列进行内存缓冲。

删除序列

drop  sequence 序列名;

查看创建的序列

select sequence_name from user_sequences;

序列的使用

-- 序列名.[currval/nextval]
	-- currval：表示当前值，新序列若没使用过nextval不能直接使用当前值，会报错；
	-- nextval：表示序列的下一个值
	

-- 序列创建之后, 必须调用一次nextval才能将序列中的第一个数值取出
-- 在一个sql语句中连续代用nextval, 取出的是同一个值
SQL> select myseq.nextval, myseq.nextval from dual;

使用序列给某些字段赋值

-- 使用序列给某些字段赋值
SQL> insert into mytmp values(myseq.nextval);

同义词

Oracle的同义词（synonyms）从字面上理解就是别名的意思，和视图的功能类似，就是一种映射关系。
就是数据库表的别名

举例:

有两个用户scott 和 luffy, scott有一张叫做 emp 的表, 如果想要让luffy访问scott用户的 emp表, 应该如何处理 ?

• scott用户需要授权给luffy, 允许luffy访问emp表

• luffy对scott的emp表进行查询

select * from emp; – 有问题, 因为emp表示不是luffy的, 是scott用户的

select * from scott.emp; --> 如果scott.emp这个名字很长, 就可以给他起个别名, 这个别名就叫同义词

创建同义词

-- 创建语法
-- 创建的同义词名可以和原名字相同或者不同
CREATE  SYNONYM  同义词名称 FOR USER.TABLE_NAME;

查看当前用户下创建的同义词

SQL> select synonym_name from user_synonyms;

删除同义词

drop synonym 同义词名;

同义词的使用

• 创建新用户

create user 用户名 identified by 密码; 

• 删除用户

 drop user 用户名;

• 授权, 让新用户有登录数据库的资格

  grant create session to 用户名; 

scott用户给luffy授权, 运行luffy查询emp表

  -- 在scott用户下操作
  grant select on emp to luffy;

  -- 切换到luffy用户
  -- 通过luffy用户查询scott的emp表
  -- 在查询的时候可以给 scott.emp 设置一个别名, 便于操作
  select * from scott.emp;

  -- 在luffy用户下给 scott.emp 创建同义词
  SQL> create synonym emp for scott.emp;
  create synonym emp for scott.emp

ORA-01031: 权限不足

  -- 切换到sys用户, 使用该用户给 luffy 授权, 让luffy有创建同义词的权限
  grant create synonym to luffy;

-- 切换到luffy用户, 再次执行创建同义词的命令
  SQL> create synonym emp for scott.emp;

  -- luffy用户可以使用同义词进行表的查询
  select * from emp;

防火墙

安装oracle的虚拟机, 必须要先报防火墙关闭, 这样客户端才能成功连接上oracle服务器

查看防火墙状态

$ systemctl status firewalld

打开防火墙

$ systemctl start firewalld

关闭防火墙

# 关闭防火墙
$ systemctl stop firewalld

永久关闭

# 永久关闭
$ systemctl disable firewalld

永久打开

$ systemctl enable firewalld