oracle基础2

oracle 的数据库对象：

表

用户

约束

索引

序列

视图

关于用户的操作

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

--对用户进行操作

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

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

createuserbjsxtidentifiedbybjsxt

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

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

--给用户授予权限

grantdbatoscott

grantconnect,resourcetobjsxt

--给用户撤销权限

revokedbafromscott

--修改密码

alteruserbjsxtidentifiedbyzgsxt

--删除用户

dropuserbjsxt

--创建表格

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

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

createtablestudent(

snonumber(6),

snamevarchar2(12),

gendervarchar2(3)default'男',

agenumber(3),

sdatedate,

clazzvarchar(10),

emailvarchar2(30)

);

select*fromstudent

--给表插入数据

commit

commit

updatestudentsetgender='女'wheresno=100003

updatestudentsetgender='男'wheresno=100002

关于表的操作

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

--给表格添加字段

altertablestudentadd(birthdaydate)--新字段的内容为null

altertablestudentadd(scorenumber(3)default100)--新字段的内容指定为默认值

--删除表的字段

altertablestudentdropcolumnbirthday

--修改字段的名称

altertablestudentrenamecolumnsdatetoenterdate

--修改字段的数据类型

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

altertablestudentmodify(birthdayvarchar2(20))

--重命名表

renamestudenttostu

---删除表操作

droptableemp

--查看回收站

select*fromrecyclebin

--从回收站还原表格

flashbacktableemptobeforedrop

select*fromstu

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

purgetableemp--净化

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

droptableemppurge

--清空回收站

purgerecyclebin

完整性约束分类

域完整性约束（非空not null，检查check）

实体完整性约束（唯一unique，主键primary key）

参照完整性约束（外键foreign key）

三种完整性约束的区别

域完整性约束:字段约束

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

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

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

非空约束 　　　　NN_表名_列名

唯一约束 　　　　UK_表名_列名

主键约束 　　　　PK_表名

外键约束 　　　　FK_表名_列名

检查约束 　　　　CK_表名_列名

--数据库对象--约束

--创建表格

--学号是主键

--姓名不能为空

--年龄范围18---30岁

-- Email唯一

--【1】主键约束--primarykey

--字段非空+唯一

droptablestudentpurge;

createtablestudent(

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

snonumber(6)primarykey,--简化的列级主键约束

snamevarchar2(12),

gendervarchar2(3)default'男',

agenumber(3),

sdatedate,

clazzvarchar(10),

emailvarchar2(30)

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

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

);

select*fromstudent

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

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

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

droptablestudentpurge;

createtablestudent(

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

snamevarchar2(12),

gendervarchar2(3)default'男',

agenumber(3),

sdatedate,

clazzvarchar(10),

emailvarchar2(30),

constraintsPK_STUDENTprimarykey(sno,sname)--表级设置主键约束

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

);

select*fromstudent

--【2】非空约束not null

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

droptablestudentpurge;

createtablestudent(

snonumber(6),

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

snamevarchar2(12)notnull,--简化的列级别设置非空约束

gendervarchar2(3)default'男',

agenumber(3),

sdatedate,

clazzvarchar(10),

emailvarchar2(30),

constraintsPK_STUDENTprimarykey(sno)--表级设置主键约束

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

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

);

--【3】唯一约束unique

--字段必须是唯一的，可以为null可以有多个null字段。

droptablestudentpurge;

createtablestudent(

snonumber(6),

snamevarchar2(12)constraintsnn_student_snamenotnull,

gendervarchar2(3)default'男',

agenumber(3),

sdatedate,

clazzvarchar(10),

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

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

constraintsPK_STUDENTprimarykey(sno),--表级设置主键约束

constraintsuk_student_emailunique(email)--表级别设置唯一约束

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

);

select*fromstudent

--【4】检查约束check

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

droptablestudentpurge;

createtablestudent(

snonumber(6),

snamevarchar2(12)constraintsnn_student_snamenotnull,

gendervarchar2(3)default'男'check(genderin('男','女')),--列级别的检查约束

agenumber(3),

sdatedate,

clazzvarchar(10),

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

constraintsPK_STUDENTprimarykey(sno),--表级设置主键约束

constraintsuk_student_emailunique(email),--表级别设置唯一约束

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

constraintsck_student_agecheck(agebetween18and24)--表级别的检查约束

);

select*fromstudent

--【5】外键约束foreign key

--创建主表clazz

createtablemyclazz(

cnovarchar2(6)primarykey,

cnamevarchar2(20)notnull,

locvarchar2(30)

);

--添加数据

insertintomyclazzvalues(100002,'502','北京尚学堂亦庄校区')

insertintomyclazzvalues(100003,'304','北京尚学堂西三旗校区')

insertintomyclazzvalues(100004,'304','北京尚学堂京燕龙校区')

select*frommyclazz

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

droptablestudentpurge;

createtablestudent(

snonumber(6),

snamevarchar2(12)constraintsnn_student_snamenotnull,

gendervarchar2(3)default'男'check(genderin('男','女')),--列级别的检查约束

agenumber(3),

sdatedate,

cnovarchar(6)referencesmyclazz(cno),--列级定义外键约束

emailvarchar2(30),

constraintsPK_STUDENTprimarykey(sno),--表级设置主键约束

constraintsuk_student_emailunique(email),--表级别设置唯一约束

constraintsck_student_agecheck(agebetween18and24)--表级别的检查约束

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

);

select*fromstudent

--外键级联删除

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

deletefrommyclazzwherecno=100004

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

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

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

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

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

droptablestudentpurge;

createtablestudent(

snonumber(6),

snamevarchar2(12)constraintsnn_student_snamenotnull,

gendervarchar2(3)default'男'check(genderin('男','女')),--列级别的检查约束

agenumber(3),

sdatedate,

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

emailvarchar2(30),

constraintsPK_STUDENTprimarykey(sno),--表级设置主键约束

constraintsuk_student_emailunique(email),--表级别设置唯一约束

constraintsck_student_agecheck(agebetween18and24),--表级别的检查约束

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

constraintsfk_student_cnoforeignkey(cno)referencesmyclazz(cno)ondeletesetnull--设置级联删除为set null

);

select*fromstudent

deletefrommyclazzwherecno=100001

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

---不能删除

droptablemyclazz

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

droptablemyclazzcascadeconstraints

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

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

createtablestudent(

snonumber(6),

snamevarchar2(12)constraintsnn_student_snamenotnull,---非空约束只能是列级的，不能在创建表之后再添加非空约束。

gendervarchar2(3)default'男',

agenumber(3),

sdatedate,

cnovarchar(6),

emailvarchar2(30)

);

droptablestudent

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

altertablestudentaddconstraintspk_studentprimarykey(sno);

altertablestudentaddconstraintsuk_student_emailunique(email);

altertablestudentaddconstraintsck_student_gendercheck(genderin('男','女'));

altertablestudentaddconstraintsck_student_agecheck(agebetween18and24);

altertablestudentaddconstraintsfk_student_cnoforeignkey(cno)referencesmyclazz(cno)ondeletecascade;

--删除约束

altertablestudentdropconstraintsck_student_gender;

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

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

--创建序列

createsequenceseq_student;

--访问序列的值

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

selectseq_student.nextvalfromdual--查询序列的下一个的值，每次查询序列的下一个的值，序列都会自动增长序列中定义的增量的值。

selectseq_student.currvalfromdual--查询序列当前的值。

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

createsequenceseq_stu

incrementby2

startwith100000

maxvalue99999999---|nomaxvalue10^27 or -1

minvalue100000--|nominvalue

cycle---|nocycle

nocache;--cache n|

selectseq_stu.nextvalfromdual

select*fromstudent

--删除序列

dropsequenceseq_stu.

----索引

--创建索引有两种方式

--1：自动创建，一个表中的primarykey和unique的列，都被数据库默认的创建了索引。

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

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

select*fromstudent

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

select*fromstudentwheresname='小明3'--0.016

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

createindexindex_student_snameonstudent(sname);

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

--删除索引

dropindexindex_student_sname;

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

createindexindex_student_snameonstudent(snamedesc);

--降序输出结果

selectsnamefromstudent

索引

开发中使用索引的要点：

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

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

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

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

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

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

定义：

视图是从若干基本表和（或）其他视图构造出来的表。

在创建一个视图时，只是存放的视图的定义，也即是动态检索数据的查询语句，而并不存放视图对应的数据

在用户使用视图时才去求相对应的数据。所以视图被称作“虚表”

作用：

可以限制对数据的访问，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

可以使复杂的查询变的简单。在编写查询后，可以方便地重用它而不必知道他的基本查询细节。

提供了对相同数据的不同显示

--视图view

--视图是虚表，而不是实际意义上存在的表。本质是从某些表中（也可能是视图）检索数据的sql语句。

--创建视图view or replace是可选的。

--授予scott dba权限

grantdbatoscott

--需要较高的权限

createorreplaceviewview_empas(selectempno,ename,job,mgr,hiredate,deptnofromemp);

createorreplaceviewview_emp1as(select*fromemp);

--使用view

select*fromview_emp;

--通过视图对视图对应的基本表进行dml操作

insertintoview_emp1values(8000,'JORDERN','CLERK','7369',sysdate,2000,100,10);

select*fromemp

--删除

deletefromview_emp1whereempno=8000

--设置视图为只读视图

createorreplaceviewview_emp1as(select*fromemp)withreadonly;

select*fromview_emp1;

---查询20号部门的员工的编号、姓名、薪水、部门名称，薪水等级

selecte.empno,e.ename,e.sal,d.dname,s.grade

fromempejoindeptd

one.deptno=d.deptno

joinsalgrades

whered.deptno=20

--将比较复杂的查询生成视图view，以后通过查询view的内容访问复杂的查询即可

--对于复杂的查询，往往不希望通过视图修改基本表，设置为只读视图即可。

createorreplaceviewview_emp_dept_salgradeas

(

selecte.empno,e.ename,e.sal,d.dname,s.grade

fromempejoindeptd

one.deptno=d.deptno

joinsalgrades

whered.deptno=20

)withreadonly;

select*fromview_emp_dept_salgrade;

--可以对相同的表创建不同的视图进行访问不同的字段。

--删除视图

dropviewview_emp

--事务Transaction

--事务（Transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位

--进行转账的模拟操作

select*fromt_account

--钱多的给钱少的转点钱500

updatet_accountsetmoney=money-500whereid=2;

updatet_accountsetmoney=money+500whereid=1;

--提交结束事务

commit

--回滚还原数据，结束事务

rollback

--在oracle中，没有事务开始的语句。一个Transaction起始于一条DML(Insert、Update和Delete )语句，结束于以下的几种情况：

--用户显式执行Commit语句提交操作或Rollback语句回退。

--当执行DDL(Create、Alter、Drop)语句事务自动提交。

--用户正常断开连接时，Transaction自动提交。

--系统崩溃或断电时事务自动回退。

分页查询

---rowid，rownum

--rowid：对应着数据库中的一条记录，是当一条记录被写入数据库的时候，由数据库管理系统生成。数据库系统自己来维护。

select*fromempwhererowid='AAAE51AAEAAAAHLAAA'

--rownum被称为伪列。不是数据库中存在的一个字段值。而是根据查询到的结果，添加上的一个逻辑顺序编号。

selectrownum,e.*fromempe

--显式查询的前5条数据

selectrownum,e.*fromempewhererownum

--查询6-10条的数据rownum永远是从1开始,第一条满足条件的数据rownum的值是1.不能使用>和>=

selectrownum,e.*fromempewhererownum>=6andrownum

--查询工资从高到低的前5条数据

select*fromemporderbysaldesc

---添加rownum

selecte.*fromempeorderbysaldesc

--使用子查询

selectrownum,T.*

from(selecte.*fromempeorderbysaldesc)T

whererownum

--查询6-10条通用的公式

selectrownum,T2.*from

(selectrownumR,T.*

from(selecte.*fromempeorderbysaldesc)T

whererownum--page*count

)T2whereT2.R>10--（page-1）*count

--count变量代表一页显式的条数

--page当前是第几页

第一范式：字段不能再分。

第二范式：不存在局部依赖（联合主键）。

第三范式：不含传递依赖（不含间接依赖）。

使用范式可以减少冗余，但是会降低性能

特定表的的设计可以违反第三范式，增加冗余提高性能。