Skip to content

Latest commit

 

History

History
200 lines (122 loc) · 10.9 KB

SQL.md

File metadata and controls

200 lines (122 loc) · 10.9 KB
Raw

数据库知识点归纳

[TOC]

SQL语言分类

  • 数据定义语言（DDL），用来建立数据库、数据库对象和定义列的命令。包括：create、alter、drop。
  • 数据操纵语言(DML)，用来操纵数据库中数据的命令。包括：select、insert、update、delete。
  • 数据控制语言(DCL)，用来控制数据库组件的存取许可、权限等的命令。包括：grant、deny、revoke。

SQL常用命令

创建表：

create table Student(
		id number primary key,
		name varchar(50) not null );

创建视图：

create view myView as
		select * from Student;

创建索引：

create unique index myIndex on Student(name);

插入条目：

insert into Student (id,name)  values (11,'tom');

插入视图(实际会改变表)：

insert into myView (id,name) values (22,'james');

更新数据：

update Student set name='Jack' where id = 11;

删除数据：

delete from table where name ='james';

超键、候选键、主键、外键

超键(超码)：在关系中能唯一标识元组的属性集 称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键(候选码)：是最小超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。表的外键是另一表的主键, 外键可以有重复的, 可以是空值。

视图

视图代表数据库的一个虚拟表，其内容由查询定义，同真实表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据集形式存在，行和列数据来自由定义是的查询所引用的表，并且在引用视图时动态生成。

从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改和删除操作。

索引

利用索引可以快速访问数据库表中的特定信息，索引是对数据库中一个或多个列的值进行排序的结构。

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

索引的分类

1.聚集索引： 聚集索引的意思可以理解为顺序排列，比如一个主键自增的表即为聚集索引，即id为1的存在于第一条，id为2的存在于第二条...假使数据库中是使用数组来存放的这张表中的数据，那么如果我需要查找第100条，那么直接第一条数据的地址加上100即为第一百条的地址，一次就能查询出来。数据库中的数据只能按照一个顺序进行排列，所以聚集索引一个数据库只能有一个。

在mysql中，不能自己创建聚集索引，主键即为聚集索引，如果没有创建主键，那么默认非空的列为聚集索引，如果没有非空的列那么会自动生成一个隐藏列为聚集索引。

2.非聚集索引：非聚集索引可以简单理解为有序目录，是一种以空间换取时间的方法。举个例子，在一个user表中，有一个id_num，即身份号，此不为主键id，那么这些数据在存储的时候都是无序的，比如 id为1的id_num为100，id为2的id_num为97，id为3的id_num为98，id为4的id_num为99，id为5的id_num为96。。。id为67的id_num为56。。。 那么如果我要查找id_num为56的人，那么只能一条一条的遍历，n条就需要查询n次，时间复杂度为O(n)，这是非常耗费性能的。

现在就需要为id_num增加非聚集索引，添加了非聚集索引后，会给id_num进行排序（内部使用结构为B+树），并且排序后，我只需要查询此目录(即查询B+树)，很快就知道为id为56的在数据库中的第67条，而不需要在去遍历表中的所有数据。所以，在非聚集索引中，不重复的数据越多，那么索引的效率越高。

索引的操作

1.创建索引

  • 创建普通索引:以下三种方式都可以创建普通索引

    CREATE INDEX 索引名 ON 表名(列名1，列名2,...);

    ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...);

    CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );

  • 创建唯一索引：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique。 下面三种模式都可以创建唯一索引：

    CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);

    ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);

    CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );

2.删除索引

以下两种方式用于删除索引:

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

3.查看索引

//查看一个表中的所有索引
SHOW INDEX FROM table_name;

索引的优点：

  • 索引加快数据库的检索速度。

  • 唯一索引可以确保每一行数据的唯一性。

  • 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

  • 索引降低了插入、删除、修改等维护任务的速度。

  • 索引需要占物理和数据空间。

事务

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作。事务处理可以确保事务性单元内的所有操作要么都成功完成，要么全部执行失败。

事务的特性：

  • 原子性：要么全部执行，要么全部不执行。
  • 一致性：事务在完成时，必须使所有的数据保持一致状态。
  • 隔离性：由并发事务所做的修改必须与热火和其他并发事务所做的修改隔离。
  • 持久性：事务完成之后，它对于系统的影响是永久性的。

逻辑查询处理顺序

  1. FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
  2. ON：对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。
  3. OUTER(JOIN)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
  4. WHERE：对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.
  5. GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
  6. CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.
  7. HAVING：对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.
  8. SELECT：处理SELECT列表，产生VT8.
  9. DISTINCT：将重复的行从VT8中移除，产生VT9.
  10. ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).
  11. TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

完整性约束

关系完整性是为保证数据库中数据的正确性和相容性，对关系模型提出的某种约束条件或规则。完整性通常包括域完整性，实体完整性、参照完整性和用户定义完整性，其中域完整性，实体完整性和参照完整性，是关系模型必须满足的完整性约束条件。

  • 域完整性约束：域完整性是保证数据库字段取值的合理性，常见的域完整性约束就是：检查(CHECK)，默认值(DEFULT)，非空(NOT NULL)。
  • 实体完整性：实体完整性是指关系的主关键字不能重复也不能取空值。
  • 参照完整性，参照完整性是定义关系之间联系的主键与外键的约束条件。
  • 用户定义完整性，实体完整性和参照完整性适用于任何关系型数据库，它主要针对关系的主键和外键取值必须有效而做出的约定。

三范式

  • 第一范式：在关系模式R中的每一个具体关系r中，如果每个属性值都是不可再分的最小数据单位 ，则称R是第一范式的关系。
  • 第二范式：如果关系模式R(U，F)中的所有非主属性都完全依赖于任意一个候选关键字，则称关系R 是属于第二范式的。
  • 第三范式：如果关系模式R(U，F)中的所有非主属性对任何候选关键字都不存在传递信赖 ，则称关系R是属于第三范式的。即在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y。

内连接，外连接，交叉连接

连接两个表的SQL语句为：

select * from table1 <JOIN操作> table2 on <条件>

1.内连接：JOIN 或`` ，内连接使用比较运算符根据每个表共有的列的值来匹配两个表的行。

2.外连接：外连接包括左外连接，右外连接，全外连接。

  • 左外连接：LEFT JOIN 或LEFT OUTER JOIN ，左外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。(意思就是一定要保留左边，右边没有匹配则置为空)
  • 右外连接：RIGHT JOIN 或RIGHT OUTER JOIN ，与左外连接相反，右外连接将保留右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
  • 全外连接：FULL JOIN 或FULL OUTER JOIN ，全外联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。 全外连接，连接后的表左右两边都可能包含null.

3.交叉连接：CROSS JOIN交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。