表、约束、rownum、rowid、视图

 

一、表的增、删、改

 

1、创建表

create table person (

pid varchar2(18),

name varchar2(30),

age number(3),

birthdate date,

sex varchar2(2) default '男'

);

给表添加一行信息

insert into person

values(777,'ASD',35,to_date('19970816','yyyymmdd'),'男')

 

 

 

2、删除表 

语法格式：

delete from  

truncate table  ----表名 截断表，清空表数据 保持表结构，不可回滚。

drop table person;

 

 

3、修改表结构

(1)增加一列

 语法格式

 alter table 表名 add (字段列表，数据类型，默认值)

 例：给person表 增加地址字段列表 ，默认暂无地址

 alter table person add (address varchar2(60) default '暂无地址');

 

(2)删除一列

 alter table 表名 drop column 列名

 例：删除字段列表为地址的一列

 alter table person drop column address;

 

(3)修改列的数据类型

 alter table 表名 modify(字段列表，数据类型， 默认值)

 alter table person modify ( pid varchar(20) default '无');

 alter table person modify ( pid varchar(23))---修改字符串长度(修改的数值大于等于当前字符长度)

 注：--修改时该列记录空值才能修改

 

(4)为表重命名

 rename 旧名 to 新名--oracle 特有的操作

 rename person to tperson ;

(5)对列重命名

alter table 表名 rename column 字段列表 to 字段列表

 

 

二、约束

 

约束的分类：

(1)主键约束 表示唯一，不能为空

(2)唯一约束 列值不允许重复

(3)非空约束 不能为空值

(4)检查约束  检查一个列的内容是否合法

(5)外键约束 在两张表进行约束

 

 

1、主键约束 primary key--主键约束一般在 id 使用，本身已经默认不能为空，可以在建表的时候指定。

方法一：

create table person (

pid varchar2(18) primary key,

wname varchar2(30),

age number(3),

birthdate date,

sex varchar2(2) default '男'

);

 

方法二：

create table person (

pid varchar2(18) ,

wname varchar2(30),

age number(3),

birthdate date,

sex varchar2(2) default '男',

constraint person_pid_pk primary key(pid)

);

 

 

2、非空约束 not null--表示一个字段列表的内容不能为空，插入数据时必须插入。

create table person (

pid varchar2(18),

wname varchar2(30) not null,

age number(3),

birthdate date,

sex varchar2(2) default '男'

);

 

 

3、唯一约束 unique  --表示一个字段列表的内容不能重复出现。

方法一：

create table person (

pid varchar2(18),

name varchar2(30) unique not null,

age number(3),

birthdate date,

sex varchar2(2) default '男'

);

 

方法二：

create table person (

pid varchar2(18) ,

name varchar2(30) not null,

age number(3),

birthdate date,

sex varchar2(2) default '男',

constraint person_name_uk unique (name)

);

 

 

4、检查约束  check ---来判断一列中插入数据的内容是否合法

方法一：

create table person (

pid varchar2(18) ,

name varchar2(30),

age number(3) check (age between 1 and 100),

birthdate date,

sex varchar2(2) default '男' check(sex in('男','女','中'))

);

方法二：

create table person (

pid varchar2(18),

name varchar2(30),

age number(3), 

birthdate date,

sex varchar2(2) default '男',

constraint  person_age_ck check(age between 1 and 100),

constraint  person_sex_ck check(sex in('男','女','中'))

);

 

 

5、主外键约束 foreign key 

 

(1)创建 person 表

create table person (

pid varchar2(18) ,

name varchar2(30) not null,

age number(3)  not null,

birthdate date,

sex varchar2(2) default '男',

constraint person_pid_pk primary key(pid),

constraint  person_name_uk unique (name),

constraint  person_age_ck check(age between 1 and 100),

constraint  person_sex_ck check(sex in('男','女','中'))

);

 

(2)创建 book 表

create table book (

bid number primary key not null,

bname varchar2(30),

bprice number(5,2),

pid varchar2(18),

constraint person_book_bid_fk foreign key(bid) references person(pid)

);

--也可直接使用 foreign key(bid) references person(pid)

 

注：--注意事项

(1)在子表中设置的外键约束必须是父表中的主键约束

(2)在删除时先删子表再删父表

(3)级联删除； drop table book constraint 

(4)在建表的时候也可以指定级联删除 

create table book (

bid number primary key not null,

bname varchar2(30),

bprice number(5,2),

pid varchar2(18),

constraint person_book_bid_fk foreign key(bid) references person(pid) on delete cascade

);

 

 

6、约束管理

(1)增加约束

alter table 表名 add constraint 约束名称 约束类型（约束字段）

分别给book表增加主键约束和外键约束

alter table book add constraint book_bid_pk primary key (bid);

alter table book add constraint person_book_bid_fk foreign key(bid) references person(pid) on delete cascade;

 

(2)约束的命名规范（建议）

 primary key: 表名称_字段名称_pk 如：constraint book_bid_pk primary key (bid)

 unique     : 表名称_字段名称_uk 如：constraint person_name_uk unique (name)

 check      : 表名称_字段名称_ck 如：constraint person_age_ck check(age between 1 and 100)

 

(3)删除约束

alter table 表名 drop constraint 约束名称

 

 

 

三、rownum 与 rowid 的使用--伪列

select emp.*,rownum from emp

 

1、rownum ---表示行号，是一个伪列，可以在每一张表中出现

(1)查询前五条记录

select emp.* ,rownum from emp where rownum<=5

(2)查询第三条到第五条的记录

select * from (select emp.* ,rownum num from emp) a where num between 3 and 5

 

2、rowid --表的伪列，用于唯一标识表行，间接给出了表行的物理位置，删除重复数据，（删除数据最快的方法）

select myemp.* ,rowid from myemp

 

create table myemp as select * from emp

insert into myemp (empno,ename,job,mgr,hiredate,sal,comm,deptno)---insert插入数据时自动生成 rowid

values (7991,'SMITH','前台',7369,'06-5月-2019',2000,200,20);

 

select ename ,max(rowid) from myemp  group by ename

 

delete from myemp where rowid not in(select max(rowid)from myemp  group by ename );--删除重复数据

 

 

使用rownum分页

select * from emp where rownum<6 order by empno ---显示前5条记录

select * from emp where rownum != 8 ---显示前7条记录

select * from

(select rownum r,emp.* from emp where rownum <= 10 order by empno)

where r >=5 ---显示第5到10条记录

 

select * from 

(select rownum r,emp.* from emp)

where r between 5 and 10

 

---使用分析函数 row_number 分页显示

--注意：在使用ROWNUM 时，只有当Order By 的字段是主键时，查询结果才会先排序再计算ROWNUM

select * from (select ename, row_number() over(order by empno) r

from emp e) a

where a.r >= 5 and a.r <= 10;

 

 

四、视图

视图概念

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

 

视图的好处（优点）

1、简单性，看到的就是需要的，视图不仅可以简化用户对数据的理解，也可以简化用户的操作，那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件

2、安全性，通过视图用户只能查询和修改他们所见到的数据，数据库中的其他数据既看不见也取不到，通过视图，用户可以被限制在数据不同子集上

3、逻辑数据独立性，视图可以使应用程序和数据库表，在一定程度上独立，如果没有视图，应用一定是建立在表上的，有了视图之后，程序可以建立在视图之上，从而程序和数据库表被视图分割开来

4、视图可以简化复杂查询，限制数据访问，提高数据安全性，视图是一个虚拟表，本身不存储数据

 

1、视图的创建

create view  视图名称 as 子查询

create table tt as select * from emp

eg：

创建部门20 的员工信息 包含 empno,ename ,sal ,deptno

grant create view to scott---授权

create view empv20 as select empno,ename,sal,deptno from emp;

 

在DOS窗口中操作：

1、登录sys

2、授权 grant create view to scott

3、登录 scott 创建视图

 

2、查询视图

select view_name from user_views;

 

3、删除视图

语法

drop view 视图名称 

drop view empv21;

 

例题：创建一个视图EMP_VIEW，视图中包含员工姓名、部门编号，部门名称。并且数据只有部门编号为20和30的数据。

create view EMP_VIEW as select a.ename,b.deptno,b.dname

from emp a,dept b where a.deptno=b.deptno and deptno in('20','30');

4、物化视图

含义：

就是具有物理存储的特殊视图，占据物理空间，就像表一样；

是远程数据的本地副本，或者用来生成基于数据表求和的汇总表

 

 

五、序列

语法格式

create sequence 序列名

max value num

min value num

increment by num start with num

cache num

cycle

 

1、创建序列

（1）创建序列 create sequence myseq;

 

（2）创建测试表

create table testseq(

next number,

curr number

)

（3）插入

insert into testseq values(myseq.nextval,myseq.currval)

（4）查看

select * from testseq;

 

--默认值为2

create sequence myseq1 increment by 2;

 

create table testseq1(

next number,

curr number

)

 

insert into testseq1 values(myseq1.nextval,myseq1.currval)

select * from testseq1;

 

--起始值为 10 ，以 2 来增长

create sequence myseq2 increment by 2 start with 10;

 

create table testseq2(

next number,

curr number

)

 

insert into testseq2 values(myseq2.nextval,myseq2.currval)

select * from testseq2;

 

2、创建循环列循环列

create sequence myseq3 maxvalue 9 increment by 2 start with 1 cache 2 cycle;--1、建序列

create table testseq3(-----2 、建测试表

next number,

curr number

)

insert into testseq3 values(myseq3.nextval,myseq3.currval)---3 、插入

select * from testseq3;--4 、查看