2018-06-28 第五十三天 oracle

  1. 云栖社区>
  2. 博客>
  3. 正文

2018-06-28 第五十三天 oracle

winniehu 2018-06-28 20:12:45 浏览699
展开阅读全文

一、数据库对象

oracle 的数据库对象:表、用户、约束、索引、序列、视图

 

)关于用户的操作

--创建一个新的用户,然后给新的用户创建一张表,然后给表中添加一些数据。查询表中的数据。

 

-- 对用户进行操作

-- 创建用户 需要当前用户拥有dba 的权限。

--新创建的用户没有任何 的权限,连基本的登录的权限都没有。

create user hw identified by hw

 

--通过给用户授予角色来给用户授予一组权限。

--角色是一组权限的集合

-- 给用户授予权限

grant dba to scott

grant connect , resource to hw

 

-- 给用户撤销权限

revoke dba from scott

 

-- 修改密码

alter user hw identified by hwei

 

-- 删除用户

drop user hw

 

-- 创建表格

-- 建立一张用来存储学生信息的表

-- 字段包含学号、姓名、性别,年龄、入学日期、班级,email等信息

create table student(

       sno number(6),

       sname varchar2(12),

       gender varchar2(3) default '',

       age number(3),

       sdate date,

       clazz varchar(10),

       email varchar2(30)

);

 

select * from student

 

--给表插入数据

insert into student values(100001,'小刚','',20,sysdate,'501','xiaogang@qq.com')

commit

 

insert into student values(100002,'',null,20,sysdate,'501','xiaozhang@qq.com');

commit

 

insert into student (sno,sname,age,sdate,clazz,email) values (100003,'小蕾',30,sysdate,'501','xiaotiantian@qq.com');

 

update student  set gender='' where sno=100003

 

--表删除数据

delete from student where sno=100002

 

)关于表的操作

--对表的以及表的结构的操作

--给表格添加字段

alter table student add (birthday date)--新字段的内容为null

alter table student add (score number(3) default 100)--新字段的内容指定为默认值

 

--删除表的字段

alter table student drop column birthday

 

--修改字段的名称 

alter table student rename column sdate to enterdate

 

--修改字段的数据类型

-- 如果想要修改某一列的数据类型,那么该列的所有的数据都需要是null.

alter table student modify (birthday varchar2(20))

 

--重命名表

rename student to stu

 

---删除表操作

drop table emp

--查看回收站

select * from recyclebin

--从回收站还原表格

flashback table emp to before drop

select * from stu

 

--将回收站中的某个表删除

purge table emp--净化

 

--不进入回收站,直接删除

drop table emp purge

 

--清空回收站

purge recyclebin

 

(三)约束

完整性约束分类

域完整性约束(非空not null,检查check)

实体完整性约束(唯一unique,主键primary key)

参照完整性约束(外键foreign key)

 70


三种完整性约束的区别

域完整性约束:字段约束

实体完整性约束:行和行之间的约束

引用完整性约束:表和表之间的约束

 

命名规则推荐采用:约束类型_约束字段

非空约束 NN_表名_列名

唯一约束 UK_表名_列名

主键约束 PK_表名

外键约束 FK_表名_列名

检查约束 CK_表名_列名

 

--数据库对象--约束

 

-- 创建表格

-- 学号是主键

-- 姓名不能为空

-- 年龄范围18---30

-- Email唯一

 

--1】主键约束--primary key

-- 字段 非空 + 唯一  

drop table student purge;

create table student(

      -- sno number(6) constraints pk_student primary key, --列级 主键约束

       sno number(6) primary key,--简化的列级主键约束 

       sname varchar2(12),

       gender varchar2(3) default '',

       age number(3),

       sdate date,

       clazz varchar(10),

       email varchar2(30)

       --constraints PK_STUDENT primary key (sno)--表级设置主键约束

       --primary key (sno)--简化的表级设置主键约束

);

insert into student  values(100001,'小刚','',20,sysdate,'501','xiaogang@qq.com');

select * from student

 

--联合主键约束--primary key

-- 字段 的组合 必须是唯一的

-- 每一个字段的值都不能是null

drop table student purge;

create table student(

       sno number(6),--联合主键 约束 只能 在表级别设置

       sname varchar2(12),

       gender varchar2(3) default '',

       age number(3),

       sdate date,

       clazz varchar(10),

       email varchar2(30),

       constraints PK_STUDENT primary key (sno , sname)--表级设置主键约束

      -- primary key (sno , sname)--简化的表级设置联合主键约束

);

insert into student  values(100001,'小红','',20,sysdate,'501','xiaogang@qq.com');

insert into student  values(100002,'小刚','',20,sysdate,'501','xiaogang@qq.com');

select * from student

 

--2】非空约束  not null

--指定的域不能为null   姓名不能为空

drop table student purge;

create table student(

       sno number(6),

       --sname varchar2(12) constraints nn_student_sname not null, -- 只能在列级别设置  非空约束

       sname varchar2(12) not null,--简化的列级别设置非空约束

       gender varchar2(3) default '',

       age number(3),

       sdate date,

       clazz varchar(10),

       email varchar2(30),

       constraints PK_STUDENT primary key (sno)--表级设置主键约束

      -- primary key (sno , sname)--简化的表级设置联合主键约束

      --constraints nn_student_sname not null (sname)--非空约束 只能在 列级 设置,不能在表级别设置

);

insert into student  values(100001,null,'',20,sysdate,'501','xiaogang@qq.com');

 

--3】唯一约束 unique

--字段必须是唯一的,可以为null 但是只能有一条数据的值为 null

drop table student purge;

create table student(

       sno number(6),

       sname varchar2(12) constraints nn_student_sname not null,

       gender varchar2(3) default '',

       age number(3),

       sdate date,

       clazz varchar(10),

      -- email varchar2(30) unique,-- 列级别的唯一约束

      email varchar2(30),-- constraints uk_student_email unique,---列级别的唯一约束

       constraints PK_STUDENT primary key (sno),--表级设置主键约束

       constraints uk_student_email unique (email)--表级别设置唯一约束

       --自己测试是否可以使用简化的表级别的设置唯一约束

);

 

insert into student  values(100001,'小刚','',20,sysdate,'501','xiaogang@qq.com');

select * from student

 

--4】检查约束  check

-- 控制列字段的取值的范围

drop table student purge;

create table student(

       sno number(6),

       sname varchar2(12) constraints nn_student_sname not null,

       gender varchar2(3) default '' check (gender in ('' , '')),--列级别的检查约束

       age number(3),

       sdate date,

       clazz varchar(10),

       email varchar2(30),-- constraints uk_student_email unique,---列级别的唯一约束

       constraints PK_STUDENT primary key (sno),--表级设置主键约束

       constraints uk_student_email unique (email),--表级别设置唯一约束

       --constraints ck_student_age check (age >=18 and age <=24)--表级别的检查约束

       constraints ck_student_age check (age between 18 and 24)--表级别的检查约束

);

 

insert into student  values(100003,'小刚','',24,sysdate,'501','xiaogang2@qq.com');

select * from student

 

--5】外键约束  foreign key

--创建主表  clazz

create table myclazz(

       cno varchar2(6) primary key,

       cname varchar2(20) not null,

       loc varchar2(30)

);

--添加数据

insert into myclazz values(100002,'502','bj')

insert into myclazz values(100003,'306','sh')

insert into myclazz values(100001,'213','hf')

select * from myclazz

 

--从表 依赖于主表的字段必须是主表的主键

drop table student purge;

create table student(

       sno number(6),

       sname varchar2(12) constraints nn_student_sname not null,

       gender varchar2(3) default '' check (gender in ('' , '')),--列级别的检查约束

       age number(3),

       sdate date,

       cno varchar(6) references myclazz (cno),--列级定义外键约束

       email varchar2(30),

       constraints PK_STUDENT primary key (sno),--表级设置主键约束

       constraints uk_student_email unique (email),--表级别设置唯一约束

       constraints ck_student_age check (age between 18 and 24)--表级别的检查约束

      -- constraints fk_student_cno foreign key (cno) references myclazz (cno)--表级别设置外键约束

);

insert into student  values(100003,'小刚','',24,sysdate,'100001','xiaogang2@qq.com');

insert into student  values(100001,'小刚','',24,sysdate,'100005','xiaogang1@qq.com');

 

--外键级联删除

--删除主表中的某些数据

delete from myclazz where cno=100003

--在删除主表字段的时候,从表中的相关的数据的解决方案:

---提供了3种解决方法:

--1restrict  受限制的,默认的解决方案。不让删除。

--2cascade  级联删除,串联删除。  作用:如果主表中某些数据删除,那么从表中相关的数据一并被删除掉。

--3set null   将从表中相关的字段设置为null.

drop table student purge;

create table student(

       sno number(6),

       sname varchar2(12) constraints nn_student_sname not null,

       gender varchar2(3) default '' check (gender in ('' , '')),--列级别的检查约束

       age number(3),

       sdate date,

       cno varchar(6),-- references myclazz (cno),--列级定义外键约束

       email varchar2(30),

       constraints PK_STUDENT primary key (sno),--表级设置主键约束

       constraints uk_student_email unique (email),--表级别设置唯一约束

       constraints ck_student_age check (age between 18 and 24),--表级别的检查约束

       --constraints fk_student_cno foreign key (cno) references myclazz (cno) on delete cascade--表级别设置外键约束,并设置串联删除

       constraints fk_student_cno foreign key (cno) references myclazz (cno) on delete set null -- 设置级联删除为  set null

);

insert into student  values(100001,'小刚','',24,sysdate,'100001','xiaogang2@qq.com');

insert into student  values(100002,'小刚','',24,sysdate,'100001','xiaogang1@qq.com');

insert into student  values(100003,'小刚','',24,sysdate,'100002','xiaogang3@qq.com');

insert into student  values(100004,'小刚','',24,sysdate,'100002','xiaogang4@qq.com');

insert into student  values(100005,'小刚','',24,sysdate,'100003','xiaogang5@qq.com');

 

delete from myclazz where cno=100001

 

--删除 myclazz 主表,即使从表中没有一条记录,那么也不能直接删除被外键引用的主表。

---不能删除

drop table myclazz

--只能强制删除,把和当前表的相关的约束一并删除掉。

drop table myclazz cascade constraints

 

---创建表之后,添加约束

--创建student 表,创建表的过程中,不添加任何的约束

create table student(

       sno number(6),

       sname varchar2(12) constraints nn_student_sname not null,---非空约束只能是列级的,不能在创建表之后再添加非空约束。

       gender varchar2(3) default '' ,

       age number(3),

       sdate date,

       cno varchar(6),

       email varchar2(30)

);

 

drop table student

--给相应的字段添加约束

alter table student add constraints pk_student primary key (sno);

alter table student add constraints uk_student_email unique (email);

alter table student add constraints ck_student_gender check (gender in ('' , ''));

alter table student add constraints ck_student_age check (age between 18 and 24);

alter table student add constraints fk_student_cno foreign key (cno) references myclazz (cno) on delete cascade;

 

insert into student  values(100002,'小刚','',24,sysdate,'100001','xiaogang1@qq.com');

 

--删除约束

alter table student drop constraints ck_student_gender;

 

)序列-索引

--序列  Sequence  oralce 数据库专有的数据库对象。

--作用:用于某些有规律的逐渐递增的字段的值的生成。

--创建序列

create sequence seq_student;

 

--访问序列的值

--必须先访问 序列的 nextval 才能访问 currval.

select seq_student.nextval from dual--查询序列的下一个的值,每次查询序列的下一个的值,序列都会自动增长 序列中定义的增量的值。

select seq_student.currval from dual--查询序列当前的值。

 

insert into student  values(seq_student.nextval,'小刚','',24,sysdate,'100001','xiaogang5@qq.com');

 

-- 通过sql 去指定序列的属性

create sequence seq_stu

increment by --增量

start with 666 --开始数

maxvalue 99999999---|nomaxvalue 10^27 or -1

minvalue 666--|no minvalue

cycle---|nocycle --是否循环

nocache;--cache n| --是否缓存

 

select seq_stu.nextval from dual

 

insert into student  values(seq_stu.nextval,'小刚','',24,sysdate,'100001','xiaogang7@qq.com');

 

select * from student

 

--删除序列

drop sequence seq_stu.

 

----索引

--创建索引有两种方式

--1:自动创建,一个表中的primary key  unique 的列,都被数据库默认的创建了索引。

--2:手动创建 create index...

--给指定的表的字段添加索引

select * from student

--测试根据名字来查找内容,还没有添加索引

select * from student where sname='小明3'

 

--sname 添加索引  需要指明给哪个字段添加索引

create index index_student_sname on student (sname);

 

--索引一旦创建,自动使用。

 

--删除索引

drop index index_student_sname;

 

--希望通过查询sname 的内容是降序的

create index index_student_sname on student (sname desc);

--降序输出结果

select sname from student

 

索引:

开发中使用索引的要点:

1.索引数据可能要占用大量的存储空间。

2.索引改善检索操作的性能,但降低数据插入、修改和删除的性能。在执行这些操作时,DBMS必须动态地更新索引

3.限制表中索引的数目。索引越多,在修改表时对索引做出修改的工作量越大

4.并非所有数据都适合于索引。唯一性不好的数据(如省)从索引得到的好处不比具有更多可能值的数据(如姓名)从索引得到的好处多

5.索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据,则该数据可能是索引的备选。

6.可以在索引中定义多个列(如省加城市),这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序,则这种索引没有用处。

 

 70

网友评论

登录后评论
0/500
评论
winniehu
+ 关注