🍅程序员小王的博客:程序员小王的博客
🍅 欢迎点赞 👍 收藏 ⭐留言 📝
🍅 如有编辑错误联系作者,如果有比较好的文章欢迎分享给我,我会取其精华去其糟粕
🍅java自学的学习路线:java自学的学习路线
一、视图
视图就是封装了一条复杂查询的语句。
1、语法 1.:CREATE VIEW 视图名称 AS 子查询
范例:建立一个视图,此视图包括了 20 部门的全部员工信息
--范例:建立一个视图,此视图包括了20部门的全部员工信息createviewempdept20asselect*fromempwheredeptno=20;
视图创建完毕就可以使用视图来查询,查询出来的都是 20 部门的员工
--查询视图select*fromempdept20;
2、语法 2:CREATE OR replace VIEW 视图名称 AS 子查询
如果视图已经存在我们可以使用语法 2 来创建视图,这样已有的视图会被覆盖。
--替换视图createorreplaceviewempde20asselect*fromempwheredeptno=20;select*fromempde20;
3、优点:
视图是给sql查询语句定义名称,方便复用,简化SQL
安全性,对开发人员屏蔽掉表和字段信息.
4、注意:
视图相当于查询语句,不存储实际数据,视图基本不会占用硬盘空间 在效率上没有得到提高
删除视图: drop view 视图名;
二、索引
1、什么是索引?
理解:类似于书前面的目录,用于提高查询效率。索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。
注意:索引里存储数据(索引列数据和rowid),并且按照索引列升序
创建索引:
CREATE[UNIQUE]|[BITMAP]INDEXindex_name--unique表示唯一索引ONtable_name([column1[ASC|DESC],column2--bitmap,创建位图索引[ASC|DESC],…]|[express])[TABLESPACEtablespace_name][PCTFREEn1]--指定索引在数据块中空闲空间[STORAGE(INITIALn2)][NOLOGGING]--表示创建和重建索引时允许对表做DML操作,默认情况下不应该使用[NOLINE][NOSORT];--表示创建索引时不进行排序,默认不适用,如果数据已经是按照该索引顺序排列的可以使用
使用: 不需要手动使用, 默认使用索引字段为条件进行查询时,数据库会自动使用索引查找数据
注意:
1.索引不是创建的越多越好,索引占空间(有数据);增删改数据时,同时需要维护索引中的数据2.通常在经常被查询的列上创建索引3.主键和唯一约束的列,数据库会自动添加索引.4.删除索引:dropindex索引名;
2、单列索引
单列索引是基于单个列所建立的索引,比如:
CREATEindex索引名on表名(列名)
案例:给 person 表的 name 建立索引
createindexpname_indexonperson(name);
3、 复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引,但是
要求列的组合必须不同,比如:
Createindexemp_idx1onemp(ename,job);Createindexemp_idx1onemp(job,ename);
案例:给 person 表创建一个 name 和 gender 的索引
createindexpname_gender_indexonperson(name,gender)
4、索引的使用原则:
在大表上建立索引才有意义
在 where 子句后面或者是连接条件上的字段建立索引
表中数据修改频率高时不建议建立索引
5、索引原理
若没有索引,搜索某个记录时(例如查找name='whj')需要搜索所有的记录,因为不能保证只有一个whj,必须全部搜索一遍
若在name上建立索引,oracle会对全表进行一次搜索,将每条记录的name值哪找升序排列,然后构建索引条目(name和rowid),存储到索引段中,查询name为whj时即可直接查找对应地方
创建了索引并不一定就会使用,oracle自动统计表的信息后,决定是否使用索引,表中数据很少时使用全表扫描速度已经很快,没有必要使用索引
三、约束
1、主键约束:primary key
作用:用于唯一标识表中的一行数据,如学号、工号
特点:唯一、非空
语法:字段名 数据类型 primary key
注意:开发时,通常常每一张表都会有一个主键约束
2、唯一约束:unique
作用:标识该字段的值不允许重复。如身份证号、手机号
特点:唯一、可以为空
语法:字段名 数据类型 unique
3、非空约束:not null
作用:标识该字段的必须有内容,不允许为null;如学生姓名
特点:非空、可以重复
语法:字段名 数据类型 not null
4、检查约束:check(表达式)
作用:根据自定义规则限制该字段的内容。例如性别只能是男或女
语法:字段名 数据类型 check(检查约束的表达式)
常见使用场景:
性别只能是男或女:check(sexin('男','女'))或是check(sex='男'orsex='女')手机号码必须是11位:phonechar(11)check(phonelike'_____')或phonechar(11)check(length(pone)=11)邮箱必须有@:check(emaillike'%@%')
5、外键约束
作用:标识该字段的值不允许随便输入,必须是另一个表中的主键或唯一键中存在的值。 (标识当前表 [从表] 和另一个表 [主表] 之间的关系)
特点:可以重复、可以是null
语法:字段名 数据类型 references 主表名(主键/唯一键的字段名)
6、默认约束
默认值:default
作用:标识该字段在不填写任意内容,系统默认分配的内容
语法:字段名 数据类型 defualt 值 约束
注意:给定的默认值的类型必须和该字段的数据类型一致
7、联合约束
联合约束:使用表的任意一个字段无法标识主键或唯一约束时,使用多个键的组合来进行约束
常见联合约束:联合主键、联合唯一约束
语法:
1.联合主键:primarykey(字段名1,字段名2)2.联合唯一约束:unique(字段名1,字段名2)
(1)创建班级表
--学生表createtablet_class(cls_idnumber(10)primarykey,cls_namevarchar2(20)notnull);
(2)创建学生表
学生学号-主键、姓名、邮箱(必须有@)、出生日期(默认当前日期)、身份证号-唯一、所在班级编号
--学生表--学生学号-主键、姓名、邮箱(必须有@)、出生日期(默认当前日期)、身份证号-唯一、所在班级编号createtablet_student(stu_idnumber(5)primarykey,stu_namevarchar2(20)notnull,emailvarchar2(50)notnullcheck(emaillike'%@%'),birthdaydatedefaultsysdate,card_idchar(18)unique,cls_idnumber(5)referencest_class(cls_id));
(3)查询用户下所有的表
注意:查询库中所有的表:select * from tabs;
--查询数据库中所有的表select*fromtabs;
(4)联合约束案例
--学生表createtablet_student(s_idnumber(5)primarykey,s_namevarchar2(30)notnull);--课程表createtablet_course(c_idnumber(5)primarykey,c_namevarchar2(30)notnull);--描述:一个学生可以选择多门课程,定义关系表createtablet_sc(s_idnumber(5)referencest_student(s_id),c_idnumber(5)referencest_course(c_id),primarykey(s_id,c_id));--插入数据createsequencestud_seqinsertintot_studentvalues(stud_seq.nextval,'大宝贝')select*fromt_studentcreatesequencecour_seqstartwith100insertintot_coursevalues(cour_seq.nextval,'oracle')select*fromt_course--往关系表中插入数据insertintot_scvalues(2,101);--查询学生选课情况selects.s_id,s_name,c.c_namefromt_studentsjoint_scscons.s_id=sc.s_idjoint_courseconsc.c_id=c.c_idwheres.s_name='佳明'
四、事务(transaction)
1、什么是事务?
概念:数据库中最小的执行单元,通常由一条或是多条sql组成,所有sql都执行成功,事务才成功,则提交事务(commit);只要有一条sql执行失败,则事务失败,则事务回滚(rollback)
事务的大小取决于业务
(1)实际开发时,一个业务对应不同条数的sql,这多条sql的组成被称为事务(实现业务需要控制事务)(2)案例:转账业务:一个账户的钱转向另一个账户updateaccountsetbalance=balance-moneywhereid=894893updateaccountsetbalance=balance+moneywhereid=242546注意:转账的业务至少2条sql,构成一个事务(2条sql)开户:往account表中添加信息insertinto.....注意:开户的业务1条sql,构成一个事务(1条sql)
事务的边界
(1)开始:从第一天sql开始执行,事务开始(2)结束:a.DML语句(insert/delete/update)需要明确指定结束的边界:I.遇到:commit,提交事务,事务成功II.遇到:rollback,回滚事务,事务失败b.DDL语句:创建表的语句(createtable/createsequence),自动提交事务
2、回滚段:
数据库服务器DBServer会为每一个连接上的client,开辟一小块内存空间(称为回滚段),用于暂时存储sql语句的执行结果,所有的sql都执行成功,则提交事务(commit),会把自己回滚的数据真正的写入DB中;如果有一条sql执行失败,则事务回滚(rollback)时,则会清空自己回滚段里的数据,恢复到事务之前的结果。
3、锁
(1) 事务的底层采用锁的机制保证数据的安全性
(2) 数据库对每条数据分配一个锁,如果一个客户端执行为insert/delete/update操作时,获取当前操作数据锁标记,只要执行到commit或是rollback时,才释放锁标记,其他的客户端才能操作当前条数据
注意:select 查询操作无需考虑事务。
4、事务的特点:(ACID)
(1)事务的原子性(Atomic):事务中的多个sql语句是一个整体,要么全部成功,要么全部失败.(2)事务的一致性(Consistency):(数据的合理性),事务执行前后(无论失败还是成功),最终数据是合理的.(3)事务的隔离型(Isolation):多个事务并发时,事务之间相互独立,互不影响(4)事务的持久性(Durability):事务结束(成功或失败),对数据库的数据修改时永久的.
五、数据库范式
1、什么是范式?
简单的讲,范式就是在设计数据库时应当遵循的一些规范,可以在建表时起一些指导性作用。设计数据库表时遵循范式,能够减少冗余数据,使得对数据的管理更为科学。
2、第一范式
第一范式的要点是字段不可再分
案例:对于爱好属性,实际存储的数据可能为:
比如现在有需求需要修改所有电影爱好为影视,再有需求希望删除所有跳舞爱好,这些操作势必转换为对字符串的拼接、查询操作,非常不方便。
解决方法是将属性再次分割,分离到另外一张表中,变为这样
3、第二范式
第二范式是指属性不能部分依赖主属性
例如:
学号学生姓名课程编号课程名称成绩s001张三c001java90s001张三c002oracle80s002李四c002oracle60s003李四c001java50s003王五c002oracle100
如果这样设计一个表示学员选课的成绩表,表中能够起唯一标识的属性称之为主要属性,这张表中学号不能单独作为主属性,同样课程编号也不能单独作为主属性。因此有两个主要属性学号和课程编号。
成绩属性同时依赖于这两个主要属性,而学生姓名和课程名称只能部分依赖于主要属性。称学生姓名和课程名称属性为部分依赖,应当从本表分离出去。
现有表结构存在这样一些问题,例如,添加新学员会存在问题新加学员如果还没有选修课程,那么一些列必须留空;修改学员一样会有问题,张三改名张四,需要修改多条记录…
这些问题分别被成为添加异常、修改异常、删除异常,如果将这些部分依赖的属性分离出去,这些问题都可以避免,如:
学号学生姓名s001张三s002李四s003王五课程编号课程名称c001javac002oracle学号课程编号成绩s001c00190s001c00280s002c00260s003c00150s003c002100
首先去除了学员和课程的一些冗余信息,其次,添加修改删除学员也不会产生异常,对于课程也是同样道理。
4、第三范式
第三范式是指属性不能间接依赖于主属性
例如:
empnoenamedeptnodname7369Smith10财务部7499Scott10财务部7869King10财务部
如果存在如上表结构,dname直接依赖于deptno属性,deptno直接依赖于empno主属性,这种情况称dname间接依赖于empno。
这种依赖带来的问题是,要添加一个新部门,这个部门添加到哪里呢,类似的也存在修改异常与删除异常,解决办法还是将间接依赖于主属性(empno)的属性(dname)从此表分离出去。如下所示:
empnoenamedeptno7369Smith107499Scott107869King10deptnodname10财务部