oracle必知必会

前言

        本专题主要记录自己最近学的数据库，有兴趣一起补习的可以一起看看，有补充和不足之处请多多指出。希望专题可以给自己还有读者带去一点点提高。

数据库基本概念

        本模块有参考：数据库基本概念-CSDN博客

        数据库管理系统是一个由互相关联的数据的集合和一组用访问这些数据的程序组成，这个数据集合通常称作数据库，其中包含了关于某个企业的信息。DBMS的主要目标就是提供一种可以方便高效的存取数据库信息的途径。

        数据库由数据和表组成。主流数据库：​SQL Server、Oracle、DB2 、MySQL。

        关系数据库系统是基于关系模型的数据库系统​，关系模型的数据结构使用简单易懂的二维数据表​。非关系数据库也被称作NoSQL (Not Only SQL)，存储数据不以关系模型为依据,不需要固定的表格式。

        数据库对象包含表，视图，索引结构，用户，存储过程，触发器，函数。

数据库设计

        本模块有参考：数据库 之数据库设计浅知识 -- 设计概述、概念结构设计（E-R模型概述）、逻辑结构设计（函数依赖和范式）、物理结构设计_数据库概念架构设计、逻辑结构设计、物理结构设计-CSDN博客

        包含概念结构设计、逻辑结构设计、物理结构设计。

        数据库设计的目标是为用户和各种应用系统提供一个信息基础设施和高效率的运行环境。

        数据库设计的特点：结构和行为分离的设计。

        设计方法，新奥尔良（New Orleans）方法，基于E-R模型的数据库设计方法3NF（第三范式）的设计方法，面向对象的数据库设计方法，统一建模语言（UML）方法。

        设计的步骤，需求分析，设计概念结构，逻辑结构，物理结构，物理实现，使用和维护。

约束

        本模块有参考：Oracle中的约束-CSDN博客

        是一种用于限制表中数据的规则，它可以保证表中数据的完整性和一致性。约束通常会定义在表的列上，用于限制该列中的数据满足特定的条件，例如唯一性、非空、主键、外键等。

        作用：保证数据的完整性，保证数据的一致性，提高数据的查询效率，避免数据的错误和丢失。

主键约束

       用于保证表中每个记录的唯一性，通常会将主键定义为表中的一个列或多个列的组合。主键约束可以确保表中每个记录都有一个唯一的标识符，常用于定义基本表的主键，起唯一标识作用，其值不能为null，也不能重复，以此来保证实体的完整性。

      对于指定为primary key的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于unique所约束的唯一键，则允许为空。不能为同一个列或一组列既定义unique约束，又定义primary key约束。

注意： 

1.主键约束要求每个行都具有唯一标识符，因此主键列不能包含 NULL 值。
2. 主键约束可以涵盖多个列，但通常只有一个列被用作主键。
3. 定义主键时应该选择一个短、有意义且易于记忆的列名。
4. 主键列应该是简单的数据类型，如整数或字符串。
5. 主键列的值应该稳定，不应该经常更改。
6. 主键约束自动创建唯一索引，因此在选择主键列时应该考虑索引的效率和性能。
7. 在使用主键约束时，应该避免使用复杂的查询和连接操作，以提高查询效率。
8. 在插入数据时，应该确保主键列的值是唯一的，否则会触发主键冲突错误。
9. 在更新数据时，应该避免更改主键列的值，否则会破坏主键约束。
10. 在删除数据时，应该确保不会删除包含主键约束的行，否则会触发主键冲突错误。

唯一约束

        用于指明基本表在某一列或多个列的组合上的取值必须唯一，保证表中某个列的值是唯一的，但是允许空值，同时系统为保证其唯一性，最多只可以出现一个null值。唯一约束可以确保表中某个列的值不重复。
       定义unique约束的哪些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。unique既可用于列约束，也可用于表约束。

非空约束

        用于保证表中某个列不允许为空值。非空约束可以确保表中某个列的值不为空。
null值不是0也不是空白，更不是填入字符串“null”，而是表示“不知道”、“不确定”或“没有数据”的意思。当某一字段的值一定要输入才有意义的时候，则可以设置为not null。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用

外键约束

        用于建立表与表之间的关联关系，保证表之间的关系完整。会用于强制参照完整性，提供单个字段或者多个字段的参照完整性，通常会将外键定义为一个表中的列，该列引用另一个表中的主键列。其中，包含外部键的表称为从表(参照表)，包含外部键所引用的主键或唯一键的表称主表(被参照表)。

        外部键约束 foreign key约束指定某一个列或一组列作为外部键，系统保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。

检查约束

        用于限制某个列的取值范围，可以使用SQL表达式来定义检查约束。检查约束可以确保表中某个列的值符合特定的条件。比如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性。当执行insert语句或者update语句时，检查约束将验证数据。

禁用约束

        用于禁用某个约束，通常用于临时关闭约束以提高性能或进行数据维护。禁用约束可以使用ALTER TABLE语句来实现。具体步骤如下：

1. 使用ALTER TABLE语句，将要禁用约束的表的名称作为参数。
2. 在表名后面添加DISABLE CONSTRAINT子句，指定要禁用的约束名称。
3. 执行该ALTER TABLE语句，即可禁用指定的约束。

默认约束

        用于设置表中某个列的默认值，当插入数据时，如果没有指定该列的值，则默认使用该约束定义的值。默认值可以算做一个约束，因为语法和创建约束差不多。默认值约束并不会限制什么，当没有给他值的时候，就会默认给你一个值。

条件约束

        用于在特定条件下启用或禁用约束，可以使用条件约束来限制数据的插入、更新和删除操作，只有符合指定条件的数据才能被执行操作。常见的条件约束有CHECK约束和WHERE子句约束。

应用到表中的所有列约束

        一是用于保证表中所有列的值都是唯一的，但允许空值存在。二是用于限制表中所有列的取值范围或满足一定的条件。

约束的使用：

        alter table st1 add primary key(deptno)   增加约束

        alter table st1 rename constraint st2 to qwer; 约束重命名                                           

        create table emp100 as select * from emp;

        alter table emp100 add primary key(empno);   删除约束

规范化

        第一范式、第二范式，第三范式，BCNF

        第一范式：每个列都不可以再拆分。第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

        范式：简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。

        第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。。

事物

        四大特性（原子性，一致性，隔离性，持久性），隔离级别（PR，RC，RU，串行化）

        （1）原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。（2）一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态（3）隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，（4） 持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。或者这样理解：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

表连接*

        inner join,left join,right join,full join,cross join。

        表连接实际指多表连接查询。基本语法如下：

        select student.studentID,name,score.CourseID,cname,grade from student,course,score 
where student.studentID=score.studentID AND course.courseID=score.courseID AND grade >=60;

    其中student.studentID=score.studentID AND course.courseID=score.courseID 是三张表的连接条件，grade >=60是条件。
    表与表之前的这种连接查询是最传统的多表连接查询方式，可以把多个表连接起来，以满足复杂报告的需要。根据是否包含相关联的表中的
    匹配行和非匹配行，查询中的连接条件又分为内连接、外连接、自然连接。
    JOIN格式如下：
    SELECT column_list
    FROM table_name1 [join_type] JOIN table_name2 ON connection_condition
        [[join_type] JOIN table_name3 ON connection_condition[...]]
    where search_condition
    连接类型：
    [joint type] JOIN
    内连接：INNER JOIN
    外连接：[LEFT|RIGHT|FULL]OUTER JOIN
    交叉连接：CROSS JOIN
    ON 关键字后面是连接条件，与简单查询中写where关键字后面的连接条件相同。

内连接查询

       内连接是最常用的连接查询，一般常用INNER JOIN 关键字来指定内连接。INNER 可以省略。所谓内连接是指查询结果中包含满足连接条件的记录。当未指明链接类型时，默认为内连接。查询结果只包含两个表中相匹配的行。内连接可以分为等值内连接，不等值内连接和自然连接。

等值内连接

    等值内连接是在ON后面给出的连接条件中使用等号（=）运算符比较被连接的两张表的公共字段，其中=查询结果中只包含两张表的公共字段值相等的行，
    列可以是两表中的任意列。
    例1：scott 用户登录数据库，基于emp表和dept表，查询雇员工资大于2000的雇员编号
    姓名、工资、所在部门编号、部门名称。

    SELECT empno,ename,sal,e.deptno,dname
    FROM emp e INNER JOIN dept d ON e.deptno=d.deptno 
    WHERE SAL>2000

    
    例2：查询选修了数据库原理课程且成绩在80分以上的学生的学号、姓名、课程名称和成绩。

    SELECT s.studentID,sname,cname,grade,
    FROM student s JOIN score sc ON s.studentID=sc.studentID
        JOIN course c on sc.courseID=c.courseID
    WHERE canme='数据库原理' AND grade>80;

    
    不等值内连接
    不等值内连接是在连接条件中使用除“=”运算符以外的其他比较运算符比较被连续的公共字段。运算符包括 >,>=,<,<=,!>,!<,<> 。
    不等值内连接查询在实际应用中使用得较少。
    
    自然连接
    是一种特殊的等值内连接，他是由系统根据两表的同名字段自动等值比较的内连接，因此不需要用ON关键字指定连接条件。在使用自然连接
    时需要注意两表的同名字段不能用表明进行限制。因为进行的是等值比较，查询的结果集中同名字段的值完全一样的，所以如果在SELECT后面
    使用*号，那么在查询结果集中系统只包含一列同名字段和他的值。

    SELECT empno,ename,sa,deptno,dname
    FROM emp NATURAL JOIN dept
    WHERE SAL>2000;

外连接查询

    内连接查询是保证查询结果集中的所有行都要满足连接条件，而使用外连接查询时，它返回的查询结果集中不仅包含符合连接条件的行，而且还
    包含连接运算符左边的表（左表，左外连接时）或右边的表（右表，右外连接时），或两个连接表（完全外连接时）中的不符合连接条件的行。
    
    左外连接
    左外连接的结果集中包括两表连接后满足ON后面指定的连接条件的行（也就是内连接的结果集）和LEFT OUTER JOIN子句中指定的左表中不满足条件的行。
    也就是说左表中所有的行都会出现在查询的结果集中。如果左表的某行在右边中没有匹配行（即不满足条件的行），则这些相关联的记过集中右边的所有
    选择列均为NULL。
    例1
    查询每个部门包括的雇员，如某部门没有雇员，也要显示其情况。要求显示部门名称、雇员名字

SELECT dname,ename
FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;

    要求显示所有部门的名称，如果使用左外连接，那么部门信息表（dept表）应放在关键字LEFT JOIN左边。
    oracle数据库中使用特有的传统方法也可以实现两个表的左外连接，格式如下：

    FROM 表1,表2
    WHERE 表1.公共字段=表2.公共字段(+)

    
    注意，左外连接中(+)符号要在等号的右边，此时会将等号左边表中的所有列显示出来，等号右边表中只显示满足连接条件的行。

    SELECT dname,ename
    FROM dept,emp
    WHERE dept.deptno=emp.deptno(+);

    
2.右外连接(RIGHT JOIN)或(RIGHT OUTER JOIN)
   右外连接是左外连接的反向连接，将返回两表内连接的结果集和右边中不匹配的行。也就是返回RIGHT JOIN 关键字右边表中的所有行。
如果某行在左表中没有匹配行，则将为左表返回NULL。
    上面的可例子为：

    SELECT dname,ename
    FROM emp RIGHT OUTER JOIN dept ON  dept.deptno=emp.deptno;

    
    或者

    SELECT ename,dname
    FROM  emp,dept
    WHERE emp.deptno=deptno(+);

    
3.完全外连接（FULL JOIN ）或（FULL OUTER  JOIN）
    完全外连接查询的结果包括两个表内联的结果集和左表与右表中不满足条件的行。当某行在另一表中没有有匹配行时，则另一个表的选择列为NULL。
即两个表的所有行都将被返回。

    SELECT ename,dname
    FROM emp FULL OUTER JOIN  dept
    ON emp.deptno=dept.deptno;

    Oracle的传统方法不支持两个表的完全连接，因为一个关系运算符最多一个+符号。
    
交叉连接
    交叉连接（CROSS JOIN）是用左表中的每一行与右表中的每行进行连接，不能使用ON关键字。因此结果集中的行数是左表的行数乘以右表的行数，该
连接查询的全集就是两个表的笛卡尔乘积。

    SELECT *
    FROM emp CROSS JOIN dept;

    没有ON关键字，但可以使用where子句。

锁机制

        锁机制，死锁。

        这块也是大头，本次不全部展开，只讨论一些基础的概念和实例。

        死锁的概念：

        加锁（Locking）是数据库在并发访问时保证数据一致性和完整性的主要机制。任何事务都需要获得相应对象上的锁才能访问数据，读取数据的事务通常只需要获得读锁（共享锁），修改数据的事务需要获得写锁（排他锁）。当两个事务互相之间需要等待对方释放获得的资源时，如果系统不进行干预则会一直等待下去，也就是进入了死锁（deadlock）状态。

        比如：两个用户同时操作了一张表，其中一个用户修改表后未提交事务，另一个人在修改该表提交事务则会报错。这就是简单的死锁实例。

        如何避免死锁：

• 对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
• 所有的update和delete操作必须走唯一索引
• SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。
• 把SELECT放在Update语句前
• 避免事务中的用户等待交互

        乐观锁和悲观锁:

1、乐观锁：

        通过版本号来验证表单是否修改过，通过数据库加一个版本号字段来和前端传过来的版本号对比。如果一样说明这条记录别人没有修改可以提交。

2、悲观锁

        比如像mysql中的 for upate 一般结合事务一起用，用来锁数据行，如果非主键操作可能会锁整个表，一般用于主键查询。

总结

        本文主要介绍数据库的基本概念和名词，重点讨论表连接的部分，深入理解和实际会用很关键。