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当前是第几页
第一范式:字段不能再分。
第二范式:不存在局部依赖(联合主键)。
第三范式:不含传递依赖(不含间接依赖)。
使用范式可以减少冗余,但是会降低性能
特定表的的设计可以违反第三范式,增加冗余提高性能。
领取专属 10元无门槛券
私享最新 技术干货