快速学完数据库

本来想写文章来复习的，后面发现越写越多，而且感觉本末倒置了，查询语句写的最少，其他倒是写的很详细，数据库知识真的太细太碎了，整理起来难度真的挺大的，如果是数据库小白，这篇文章肯定很有用，它没有过多的实战，带你了解数据库的基本框架和能够完成的任务，如果是数据库学过的话，那这篇文章可能对你而言只有前面数据库概述和设计数据库有用，数据库sql语句这部分非常的精简，几乎只是了解个框架，第一次写长文欢迎评论区大佬们的指正。

一、数据库系统概述

1.数据库系统的组成

--DB -> DBMS -> DBS -> user

--即由数据库(database)出发
--利用数据库管理系统(database manage system)创建数据库中的表(database schema)
-- 最后由user进行查询等等相关操作 

2.数据库的特点

-- 1.有结构的

-- 数据库按照关系模型来储存数据和联系数据
-- 一般采用二维表的形式进行实现

-- 2.集成的

--集成一系列数据
--并按照一定的原则进行分类
--尽量减少数据之间的冗余性

--3.可共享的

--可以同时被多个用户操作
--并可以指定权限等等

--4.统一管理的

--一般是由DBMS完成数据库的全部操作

--学习数据库主要研究然后高效的储存、使用和管理数据，不仅仅局限与只是查询数据以及存储数据的载体，这是一门颇具艺术性的课程

3.数据库的模式结构

三种模式

-- 外模式

--一般指将数据以一定的形式呈现给用户
--形式不是唯一的
--例如在各个浏览器查询的界面与各个软件等等查询的界面，都是外模式的一种体现


-- 概念模式

--即表中信息的描述，例如user(name,age)


-- 内模式

--存储结构、存储方法、存取策略等的描述，即设计表时的一些可选项

二级映射

-- 外模式到概念模式：逻辑独立性

--指概念模式改变时DBMS通过改变映射使得外模式不变
--例子：比如user表多了一个身高的字段即变成user(name,age,height),展示数据的应用程序界面不需要进行修改


-- 内模式到概念模式：物理独立性

--指内模式改变时DBMS通过改变映射使得概念模式不变
--例子：比如user表的一个height字段中数据类型想要从int到double，通过DBMS,概念模式无需改变


-- 数据独立性由逻辑独立性和物理独立性组成

--   即最终呈现给用户的数据库界面不受内模式以及概念模式的改变而改变

4.数据库建立的流程图

现实世界 -> 概念世界 -> E-R图 -> 二维表 -> 数据世界

5.关系数据库的一些术语

--  关系(relation)：就是一张表，用R表示关系的名称 
--  元组：也称记录，行，对应于数据库数据中的条记录即数据
--  属性：关系表中一列即代表一个属性，属性只能有一个属性名，而关系可以有多个属性，一般在表中称为字段 
--  域(domain)：即数据类型的取值范围，这里是指一般的取值，也可以是集合形式的取值范围
--  候选键：即可以唯一确定一条记录的字段，可能有多个
--  主键：就是在候选键中选取的一个
--  外键：即在其他表中为主键的字段
--  极端情况下，候选键只有一个属性或者全部属性才能构成一个候选键

6.关系数据库的数据完整性

-- 实体完整性

--即主键值唯一且不能为空值


--  参照完整性
--一个表的外键值要么为空要么就是其他表中的某一个主键值
--主要是为了确保数据的一致性


--  用户自定义完整性
--即用户自定义的规则，数据符合规则才是合法的，才可以插入表中

二、数据库设计思路以及规范

1.E-R图

基本概念

-- 矩形代表实体
-- 菱形代表联系
-- 椭圆形代表属性
-- 无向边，用于连接实体和属性

联系

联系也可以有属性

--指的是两个实体间的联系
--联系也可以有属性


-- 一对一的联系

--在两个实体之间任意一个加入另外一个实体的主键即可产生联系


-- 一对多的联系 

--在多端实体加入一端实体的主键即可产生联系


-- 多对多的联系

--需要另外创建一张表进行产生联系 
--按照需要建立一个两个字段的表即可

一个表的表示方式

-- 主键一般就在字段下面用下划线表示，表名一般就是实体的名字

--主键由单个字段构成
--表名(字段1，字段2，字段3，字段4，字段5) 

--主键由多个字段构成 
--表名(字段1，字段2，字段3，字段4，字段5)

2.数据库设计三范式

背景

不合理的数据库设计中存在以下问题

-- 数据冗余
--尽量少出现重复的数据，例如一个表中有好几个相同学生的学号姓名，只是选的课程不一样，这样很多记录中的学号和姓名明显是冗余的。

-- 更新异常 
--由于同一字段存在在同一个表中的不同的记录中，导致修改该字段，需要修改好多条记录，容易造成数据的不一致性

-- 插入异常 
--插入一条新数据时依赖其他现有的数据，导致插入不能很好的进行

-- 删除异常
--删除数据时，不可避免地删除掉某些我们需要地信息

所以一个好的数据库应该尽量避免以上问题

范式理论

预备知识，函数依赖

• 概念

--即对于某一个关系模型R，其属性集合为U，X,Y为U的子集，即每个X对应一个唯一的Y，即对于两条记录，若X对应的属性值相同，Y对应的属性值也相同，称X函数确定Y，Y函数依赖于X（其实这部分就是我们中学所学的函数，只是应用到数据库中讲的比较抽象而已，理解上就是按照函数来理解） 

• 特例

-- 平凡函数依赖
--即Y包含在X集合中，这个形成函数依赖是显然的（在数学术语中经常出现平凡这个字眼，其实就是某些特例，比较简单，没什么好研究的意思，比如线性方程组中的平凡解（即零向量）等等）
 
-- 非平凡函数依赖
--即Y不包含在X集合中

• 三种函数依赖

-- 完全函数依赖
--Y必需由X中的全部属性才能决定

-- 部分函数依赖 
--Y可以只由X中部分属性确定
 
-- 传递函数依赖
--即指X与Y之间存在其他的中间联系属性集合，例如，Z函数依赖于X，Y函数依赖于Z
--即存在某些中间关系
X->Z
Z->Y

• 候选码

--即表中可以唯一确定一条数据的属性或者属性集合，候选码可能有多个，可以选择其中一个作为主键

范式

满足一定约束条件的关系模式，即数据库表的设计满足一定的规范，关系模式的规范化即将低一级的关系模式分解成高一级的关系模式，表现为将表进行分解。

-- 1. 第一范式(1NF)
--所有属性都是不可再分的数据项，即属性不能再分，一般来讲数据库表中的默认满足1NF，不过也有一些反例，比如对于身份这个属性，出现男老师这个属性值，就说明表不满足1NF，男老师可以进行再分，即分为性别加身份
 
-- 2. 第二范式(2NF)
--在满足1NF的前提下，非主属性完全函数依赖于候选码，主属性即为候选码中的属性，故2NF即所有非主属性只能被候选码完全确定，不会出现候选码中的部分属性确定其他非主属性的情况

-- 3. 第三范式(3NF)
--在满足2NF的前提下，任一非主属性都不传递依赖于任何候选码

三、数据库SQL语句的基础——关系代数

这部分内容比较的抽象，不过作为sql语句的先导学习还是有帮助的

1.基本概念

-- 域
--同种数据类型值的集合，就像高中学过的函数中的定义域一样

-- 笛卡儿积
-- 所有域的所有取值集合，不重复，其中集合中的每个元素称为元组，例：D~1~中有3个元素，D~2~中有4个元素，则构成的笛卡儿积中的元素就有3*4=12种

-- 关系
--笛卡儿积的有限子集称为在域上的一个关系，即将元素绑定在一起，关系中的元素满足一定的条件

-- 行一般就指记录或者元组，列指属性
-- 列中的元素是同一种类型的，不同列可能具有相同的域，但不同列要具有不同的属性名，行和列的顺序是随意的，不会影响我们的操作

2.关系代数基本组成

常见运算符

集合运算符、比较运算符

-- 集合运算符：交、差、并、广义笛卡儿积
-- 比较运算符：大于、大于等于、小于、小于等于、等于、不等于
-- 这边的等于只要一个等号即可

专门运算符：$\sigma$,$\pi$,$\div$,$\infty$,$\times$

-- 即选择，投影，除法，连接

逻辑运算符：$\neg$,$\vee$,$\wedge$

一些运算符的详解

选择$\sigma$

• 基本形式

$\sigma_{[options]}(表)$

• 例子

$\sigma_{age=15}(student)$

--从学生表中查询年龄等于15的学生，其实等价于后面要学的where语句

投影$\pi$

本质上就是取出我们需要的字段，重新生成一张表

• 基本形式

$\pi_{[属性1],[属性2]}(表)$

• 例子：

$\pi_{age,name}(student)$

-- 表示选择student中的age和name字段构成一个关系，即一个表

连接$\infty$

将两个表按照一定的规则连接，一般默认就是自然连接，比较常用的是等值连接，也可以自己创建连接的规则

• 一般形式

  $R\underset{A\theta B}{\infty}S$

  其中$\theta$为比较运算符，A与B为比较对象，一般是表中的某个字段的比较

• 等值连接

  -- 1. 自然连接
  --特殊的等值连接，一般情况下连接符下面的条件可以省略，将两张表中相同属性组进行等值连接，最后会将重复的属性取掉
  
  -- 2. 等值连接
  --条件运算符是等号的连接

• 非等值连接

  即一般的条件判断连接，符合规则然后连接

--还有一些概念，例如左外连接，右外连接，全外连接，即连接时如果没有匹配到值做的一些处理，将没有值的部分置为空值，左即保留左边
--的全部元组，右即右边，全即全部

除法$\div$

• 象集

  考虑一个关系模式R（X，Y），其中X,Y均为属性组

  现对任意的元组a，a在X属性组上的取值等于某一给定的集合x，则此时满足条件的元组的属性组Y部分的取值即为象集

  记为：$Y_{x}$

  例子：

  有一个表如下图所示

  $X_1$	$X_2$	Y
  x1	x2	y1
  x1	x2	y2
  x1	x3	y1
  x2	x1	y2

  其中X~1~和X~2~为属性组X，则x = （x1,x2）在R中的象集$Y_x$ = {y1,y2}

  b = (x1,x3)在R中的象集$Y_x$ = {y1}以此类推

• 除法运算本质上就是象集的运算

  $Y_x$ 相当于 R$\div$ x

  此时x为一个元素表,除法运算考虑更一般的情况是一个一般的表

  考虑更一般的情况

  R(X,Y)和S(Y,Z)

  $R\div S$即返回X的属性列，即与S中Y都相同的元组的X属性组的取值

--总结一下，除法的运算，主要是考虑到一种需求，如果我们想要某个属性组中的值与给定表中的相同的元组，同时属性又不全要，就可以考虑使用除法

并、差$\cup$、-

并：R$\cup$S当且仅当R与S具有相同的属性，作用结果是元组数目不减少，可能不增

差：R-S也需要满足R和S具有相同的属性，作用结果是元组数目不增加，可能不减

笛卡尔积$\times$

--R ：n目关系，k~1~个元组
--S：m目关系，k~2~个元组

则R$\times$S的结果是产生（m+n）列和（k~1~$\times$k~2~）行的一个二维表

四、数据库SQL语句

1.DDL

即Database Define Language ，负责创建和解释关系型数据库的语言

这里包含两部分，表和视图

1.1建表、删表

1.1.1最简单的建表语句

create table student(
    name varchar(10) primary key,
    age int
)

创建了一个以name为主键的student表

1.1.2添加约束的建表

-- 一般约束可以分单列的约束创建和多列的约束创建
-- 单列的直接放在字段后面即可，多列的在建表语句的最后出现
-- 下面由于展示方便，没有指定约束名，这里以主键约束展示一下，但最好还是指定一下约束名
-- 不然到时候删除就有点麻烦，系统默认会随机生成一个约束名
ALTER TABLE student 
add  constraint key PRIMARY key(sno)

非空约束

作用：使得插入的数据不能为空值

--这里就是创建了一个非空约束
create table student(
    name varchar(10) primary key,
    age int not null
)

添加约束

-- 添加约束之前会检查表中的数据，如果有空值则不成功执行
alter table student
alter column age int not null

主键约束

作用：使得字段非空且唯一

--这里就是创建了一个主键约束
create table student(
    name varchar(10) primary key,
    age int
)

如果想从现有表进行添加主键约束

--首先要确保当前的要添加主键约束的字段非空
--不然就不满足实体完整性
create table student1(
    name varchar(10) not null,
    age int
)
ALTER TABLE student 
add  PRIMARY key(sno)

唯一约束

作用：与主键有点像，允许出现空值，但只能出现一次，保证键值的唯一性，一个表可以允许有多个唯一约束

--这里就是创建了一个唯一约束
create table student(
    name varchar(10) primary key,
    age int unique
)

添加的操作

alter table student
add unique (age)
--注意括号不能少

检查约束

作用：在每次系统插入或者更新数据时检查数据

--这里就是创建了一个检查约束
create table student(
    name varchar(10) primary key,
    age int check(age between 15 and 30)
)

添加的操作

alter table student
add check(age between 15 and 30)

• check 约束中可以使用系统自带的函数
• 但不能使用子查询
• 不能使用其他表的字段
• 一般采用and 或者 or关键字表示复杂的逻辑

外键约束

作用：给表中的列添加外键约束，使得插入的该列的值必须是合法的，即保证数据的一致性

--这里就是创建了一个外键约束
create table student(
    name varchar(10) primary key,
    age int foreign key(age) references course(age)
    --age int foreign key references course(age)
)
--这边由于是列级约束，所以可以省略foreign key 后面的列名

添加约束

alter table student
add foreign key(age) references course(age)

注意事项

foreign key 中引用的外键数必须和references 里面数目和类型一致
这里回顾一下外键的定义：一个表中的属性或者属性组是另一个表中的主键则称此属性或者属性组为外键

默认约束

作用：当插入数据不指定字段的属性值时，自动赋一个默认的值

--这里就是创建了一个默认约束
create table student(
    name varchar(10) primary key,
    age int default 18
)

添加约束的操作

--只能一条一条添加约束
alter table student
add default '18' for age

索引

--分为聚集索引和非聚集索引
--聚集索引只能有一个
--非聚集索引可以有多个
--索引旨在提高检索数据的速度，同时也需要付出一定的代价，即占用一部分空间
--聚集clustered 
--非聚集nonclustered
CREATE TABLE test(
age int not null
)

--创建索引
CREATE CLUSTERED index tt
on test(age)

--删除索引
drop index tt on test

自增列

create table student(
    name varchar(7) primary key,
    --以初始为1，间隔为2进行自增,无需用户指定
    id int indentity(1,2) 
)

计算列

create table student(
    name varchar(7) primary key,
    --以初始为1，间隔为2进行自增,无需用户指定
    id int indentity(1,2) ,
    aa as id *5
)

1.1.3删除表，约束

--这部分就比较简单了
--为了比较顺利的删除约束，这里补充一下如何查看约束名称
exec sp_help
--由于一开始没有指定约束名，所以需要通过这个命令查看

删除表

drop table student

删除约束

alter table student
drop constraint 名字
--多个的话采用括号括起来

1. 更新表，约束

   这部分其实有点重合，在上一部分以及讲了，这里展示一下基本结构

   --更新表
   alter table 表名
   alter column 修改的列
   
   --更新约束
   --这里其实是添加，约束不能直接更新的，要先删除然后再更新
   alter table 表名
   add 约束

1.2视图

1.2.1视图的应用背景

在写sql语句时有时需要会写很多重复的sql语句，但它们的功能基本相同，所以这时候重用sql语句就变得非常重要，于是就产生了视图这个工具。

1.2.2视图的创建

create view 名字  as
sql语句
--举个简单的例子
create view test_view as
select age,name from student
--增加检查条件
with check o

1.2.3视图的使用

--视图的使用其实和普通的表没有多大区别
--可视为一个表进行操作
select * from test_view
--此时就检索出age和name构成的记录
--一般来讲视图是用于检索数据的，当然也可以插入，更新，删除，但是会有限制，这部分在下面的误区会讲

1.2.4视图的一些误区

--首先视图并不是一个真实的表，它只是一个虚表，本质上并不储存数据，数据都在视图sql语句中的基表当中
--故视图在下列情况下不能被更新
1.使用分组
2.使用连接
3.使用子查询
4.使用并操作
5.使用聚集函数
6.使用distinct
7.使用计算列
--这些数据本质上都是由基表计算出来的，当基表中无这些数据，由于对视图的更新相当于对基表的更新，故不能更新

2.DML

2.1查询语句

基本查询语句

select 列名 
from 表名

select *
from 表名
--代表查询全部列的意思

--举个例子
--查找student中name列
select name
from student

where语句

对查询的记录增加条件

select 列名
from 表名
where 条件

--举个例子
--查找name为zhang的学生
select * 
from student
where name = 'zhang'

group by和having语句

--group by 顾名思义是分组查询，将数据按照一定的原则进行排序
--涉及到几个比较常见的聚集函数
--count，min，max，avg，sum
--count(*)代表统计全部的数据包括空值，count(具体列名)则代表非空的数据
--当条件中有出现聚集函数必须使用having子句进行限制，不能使用where
--聚集函数相当于在分组里面进行计算，故select 中选择的属性必须包含在group by子句中
--选择的列要么出现在group by 子句中要么出现在聚集函数中
--先分组再有having条件
select 列名
from 表名
where 条件
group by 列名

--举个例子
--查找name相同的个数
select name ,count(*)
from student
group name

join语句

--包括自然连接，等值连接，有条件的连接
--left join，right join ，full join
--这些join语句区别在于处理空值的原则不同
--left join即代表保留左表的全部数据，没有的值按照null表示，right 和full类似
select 列名
from 表1 join 表2 on 条件
--多个的话 from 表1 join 表2 on 条件 join 表3 on 条件
where 条件

order by语句

--排序，比较简单，默认升序排列
--desc 降序排列
--order by score desc
--asc 升序排列
--order by score asc
--一般位于sql语句最后

--举个例子
--将分数按照降序排列
select score
from student
order by score desc

--这里补充一个top 关键字
--top 2代表前两名
--top 2 with ties 代表考虑并列
select top 2 with ties score 
from student 

--percent 关键字
--选出前30%
select top 30 percent 
from student

子查询

由于很多时候我们的表不能满足我们需求，由于通过sql语句查询出来的表具有很好的灵活性，故引入子查询

使得我们可以用子查询的记录当成一个表进行操作

• 不相关子查询

  --顾名思义就是子查询与目前执行的查询语句相互独立
  --子查询本质就是一个sql语句

• 相关子查询

  --顾名思义就是子查询与主要的查询直接有关系
  --例如

一些注意事项

-- between and 包括范围点

-- like匹配符匹配中文时也将中文看成一个字符

-- 聚集函数相当于在分组里面进行计算，故select 中选择的属性必须包含在group by子句中
-- 选择的列要么出现在group by 子句中要么出现在聚集函数中
-- 先分组再有having条件，最后是order by排序，这里有个顺序

-- 对于group by分组的理解
-- 当按照group by后面的属性进行分组好之后
-- select选择的属性只能时group by 中的属性或者聚集函数构成的属性，否则
-- 就会出现另外一个字段对其进行分组，不符合常理

2.2插入语句

有两种方式

• 没有指定列名的插入语句

  insert into 表名 values(值1,值2)
  --这里必须输入表的全部属性上的值才可以执行，默认按照表的定义结构进行赋值
  --比较容易出错，不建议采取这种插入数据的方式
  --举个例子
  insert into student values('zhang',19)

• 指定列名的插入语句

  insert into 表名(字段1,字段2) values(值1,值2)
  --这里指定列名，值一一与前面的列名相对应
  --语句比较清晰，但存在需要写很多字段的繁琐操作
  --举个例子
  insert into student(name,age) values('yu',10)

2.3更新语句

update 表名
set 执行更新的操作
where 条件

--举个例子
--更新name为zhang的学生的名字变成hhh
update student
set name = 'hhh'
where name = 'zhang'

2.4删除语句

delete from 表名
where 条件

--举个例子
--删除name为zhang的学生
delete from student
where name = 'zhang'

--级联删除
--其实是在创建表时进行的
--一般用于外键约束时指定
--on delete cascade
create table student(
    name varchar(10) primary key,
    age int default 18 
)
create table student1(
  age int PRIMARY key ,
    name varchar(10), 
)
ALTER TABLE student 
add foreign key(age) references student1(age) on DELETE CASCADE

--类似的还有级联更新，这里就不详细写了
--on update cascade

3.DCL

• 授权

  --授予某种权限给用户
  grant 权限  to 用户

• 回收权限

  --回收权限
  revoke 权限 from 用户

• 拒绝权限

  --拒绝权限
  deny 权限 to 用户

五、数据库高级操作

这部分初学者很难使用到，如果这篇文章能有50收藏，我考完就写，太累了，先列个大纲，完成指标，写就完事了，希望能达到5000的阅读量

1.事务

四个特性（ACID）

-- 原子性:要么事务全部成功要么全部失败
-- 一致性:事务执行前后的完整性没有被破坏
-- 独立性: 一个事务不会被其他事务所干扰
-- 持久性: 事务一旦提交，对数据库的改变是永久的，不可恢复的

事务相关的sql语句

begin transation:
sql语句
--提交语句
commit transation

--回滚语句，即撤销操作，由于没有设置结点，所以默认返回最开始的语句
rollback transation

--设置结点
save transation '结点名字'

并发操作的一些问题

-- 丢失、修改数据
--指的是两个事务并发执行时对一个数据进行修改，导致其中一个事务的数据修改失效了，违背了事务的隔离性，形象的称为“写写冲突”

-- 读“脏数据”
--一个事务读取另一个事务还未提交的中间结果，导致读取到了错误的数据，称为“写读冲突”

-- 不可重复读
--事务1读取数据之后，事务2进行更新操作，导致事务1不能重现上一次的数据

解决的方案

-- 排他锁(X锁exclusive)
--写入数据的时候使用，若事务1对对象A采用X锁，则只允许事务1对A进行修改和读取操作，其他事务都不允许，直到X锁解除

-- 共享锁(S锁share)
--读取数据的时候使用
--事务1对某一个对象(指的是具体某一个数据)启用S锁，其他事务只能对此对象启用S锁，不能启用X锁

2.封锁

-- 一级封锁协议
--对事务T要修改的数据加X锁，直至事务结束才释放

-- 二级封锁协议
--在一级封锁协议的基础上，增加事务T对要读的数据加了S锁，读完立刻释放S锁

-- 三级封锁协议
--S锁直到事务结束才释放

--只有达到三级封锁协议才可以解决三个事务并发执行中的三个问题

3.两段锁协议

-- 1.读写之前要先获取对数据的封锁
-- 2.在释放一个封锁之后，事务不再获取任何其他的封锁

-- 遵守两段锁协议，这些数据一定不会出现那三种情况