数据库基本结构及原理

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。
用户通过数据库管理系统访问数据库中的数据。
数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。
数据库：存储、维护和管理数据的集合。

数据库系统一般由4个部分组成：

1、数据库是指长期存储在计算机内的，有组织，可共享的数据的集合。

2、硬件：构成计算机系统的各种物理设备，包括存储所需的外部设备。

3、软件：包括操作系统、数据库管理系统及应用程序。

4、人员：主要有4类。第一类为系统分析员和数据库设计人员，系统分析员负责应用系统的需求分析和规范说明，和用户及数据库管理员一起确定系统的硬件配置，并参与数据库系统的概要设计。第二类为应用程序员，负责编写使用数据库的应用程序。第三类为最终用户，他们利用系统的接口或查询语言访问数据库。第四类为数据库管理员，负责数据库的总体信息控制。

1.数据库(Database,DB)是什么
数据库是按一定结构组织并可以长期存储在计算机内、在逻辑上保持一致的、可共享的大量相关联数据的集合。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为在一定组织范围内的各种用户所共享
广义数据库：Oracle，MySQL，PostgreSQL，Redis，Hbase
2.数据库的应用场景
电商网站：用户账号信息，商品信息，用户订单信息
网上论坛：主题，发帖，回复
3.数据库存在的目的
存储，管理，维护大量数据
提供比Excel，磁盘文件读写等更好的性能和可靠性
如何实现一个数据库？ 例：（1）实现一个存储管理学生信息程序 （2）支持基本的按名字读取功能 （3）最简单的，数据存成文本文件，例student.txt
需求1：查询名叫“xx”的学生
（1）打开student.txt （2）读取文件中下一行数据 （3）判断姓名字段是否是“xx”；如果是则返回 （4）如果不是则顺序扫描整个文件直至末尾 （5）关闭student.txt

需求2：更快的查找（索引）
（1）每次读取扫描整个文件很慢
（2）如果能直接根据姓名定位到数据行
（3）增加一个文件存储名字到完整数据行的文件偏移映射
（4）需求：访问速度 方法：独立的索引文件

补充：
信息（Information）是现实世界事物的存在方式或运动状态的反映，具有特定的语义，具有可感知、可存储、可加工、可传递和可再生等自然属性
数据（Data）是描述现实世界事物的符号记录，指用物理符号记录下来的可以见别的信息。物理符号包括数字、文字、图形、图像、声音及其他特殊符号。数据的各种 表现形式都可以经过数字化后存入计算机
数据与信息的关联：数据是信息的符号表示，可称为载体；信息时数据的内涵，是数据的语义解释

需求3：更快读取（缓存）
（1）即使有索引还是每次都要去磁盘上读“xx”的信息
（2）磁盘IO是非常慢的（ms），但访问内存却很快（ns）
（3）如果内存可用，可在内存里面缓存部分数据
（4）需求：访问加速 方法：缓存/Cache

需求4：通用接口
（1）要同时支持WEB服务器访问，支持App访问
（2）为了推广使用存储系统，组设计一套通用访问接口
（3）要求：满足基本数据操作需求，支持各种语言
（4）方法：结构化查询语言----SQL（structure query language）
sql语句-->词法解析关键字（解析出：关键字、单词、运算符等）-->语法解析（识别并判断执行类型）-->读取（read）、插入（inser）、更新（update）、删除（delete）操作

需求5：更优的执行策略
（1）有了SQL，查询就复杂了，比如支持了更多的索引时，如何执行
（2）用户发起一个查询“select * from student where 学号=xxx and 名字=xx”
（3）该选用哪个索引？名字索引？还是学号索引？
（4）方法：查询优化器（当有多个访问路径时，查询优化器可帮助我们选择一个执行更快的执行路径）

需求6：服务更多的用户
（1）真实的数据库很强大，需提供大量客户并发访问
（2）需求：多用户同时高效读写，互不阻塞
（3）方法：需以多进程或多线程或异步响应用户的并发请求

其他需求
（1）需要记录用户的访问日志以便排查问题，需要全日志以及慢查询日志
（2）并发的读写访问需要加锁，防止数据被写坏
（3）为了高效管理磁盘存储空间，需要独立磁盘物理空间管理模块
（4）主从复制系统，以实现集群的高可用
 

数据库语言四大类
DQL（Data Query Language）：数据库查询语言。

DDL（Data Definition Language）：数据库定义语言。定义关系模式、删除关系、修改关系模式。

关系、属性
DML（Data Manipulation Language）：数据库操纵语言。插入元组、删除元组、修改元组。

元组
DCL（Data Control Language）：数据库控制语言。用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果。

GRANT、revoke
commit / rollback work
完整性
实体完整性：要求每个关系（表）有且仅有一个主键，每一个主键值必须唯一，而且不允许为“空”（NULL）或重复。

主键
参照完整性：对于永久关系的相关表，在更新、插入或删除记录时，如果只改其一，就会影响数据的完整性。如删除父表的某记录后，子表的相应记录未删除，致使这些记录称为孤立记录。对于更新、插入或删除表间数据的完整性。

外键
自定义完整性

基础操作
创建关系
create table instructor2(
id char(5) not null,
name varchar(20) not null,
dept_name varchar(20) not null,
salary numeric(8,2),
primary key(id)
);
结果如图。可看出对属性进行了相关定义。

插入元组

insert into 关系名 values(每个属性对应的值)；
insert into instructor2 values(00001,'ai','math',2500.37);
insert into instructor2 values(00002,'la','math',3700.89);
insert into instructor2 values(00003,'bi','math',5400.45);
insert into instructor2 values(00004,'ma','english',1450.23);
insert into instructor2 values(00005,'ye','english',3421.34);
insert into instructor2 values(00006,'er','technology',10000.65);

删除所有元组

delete from 关系名；
删除了元组，但关系和属性还存在。

删除关系

drop table 关系名；
删除了关系，进行select查询时会出现关系名无效的错误。

属性操作

alter table 关系名 add 新属性 数据类型，新属性 数据类型，⋯
⋯
；

例：alter table instructor2 add firstname varchar(20),lastname varchar(20);

alter table 关系名 drop 属性，⋯
⋯
；

例：alter table instructor2 drop firstname ,lastname ;
查询语句基础结构

select对应投影Π    from对应笛卡尔积
where对应选择σ
�
含义
（1）.为from子句列出的关系产生笛卡尔积。
（2）.在（1）的结果上应用where子句中指定的谓词（条件）。
（3）.对于（2）中的元组，输出select子句中指定的属性。
select *(属性1，属性2，...)
from 关系名1，关系名2，...
where 条件1 and 条件2 and ...;
例

select id,name,salary
from instructor2
where salary>4000;
结果如下

去重操作（distinct）

对于一些属性来说，在某些元组中存在相同的值，有时我们需要对结果去重，得到正确答案。
select distinct 属性名
from 关系名
where 条件

例如：当我们想要查看系名时，多个老师可能位于同一个系，不去重将产生下列结果：

显然这不是我们想要的答案。我们只需要知道存在哪些系，而无需知道个数：

更名操作（as）

优点
方便：长名字变短名字，便于书写语句。
同一关系笛卡尔积：区分属性。
对属性：
对关系：
select 属性 as 属性别名
from 关系名
where 条件

select 属性
from 关系名 as 关系别名
where 条件

例：属性别名

select name as instructor_name,salary
from instructor2
where salary>3500;

结果如下

例：关系别名

select i1.id,i2.name
from instructor2 as i1,instructor2 as i2
where i1.id=i2.id and i2.salary>3500;

结果如下

字符串运算（like）

SQL使用一对单引号来标示字符串，例‘math’。
百分号（%）：匹配任意子串
下划线（_）：匹配任意一个字符
select 属性
from 关系名
where 属性 like '字符形式';

例

select *
from instructor2
where salary like '%0_.%';

结果如下

显示次序（order by）

默认升序：asc
降序：desc
select 属性
from 关系名
where 条件
order by 属性 升序/降序；
例

select * from instructor2  order by salary desc;
结果如下

between⋯
⋯
and⋯
⋯

属性值介于某个范围内
select *
from instructor2
where 属性名 between ... and ... ;

例

select *
from instructor2
where salary between 3000 and 6000;

结果如下

分组聚集（group by）

聚集函数：平均值（avg）、最小值（min）、最大值（max）、总和（sum）、计数（count）。
出现在select语句中但没有被聚集的属性只能是出现在group by子句中的属性。
错误查询
select dept_name,id,avg(salary)
from instructor2
group by dept_name;

结果如下

having子句（出现group by时使用）

有group by时，select 和 having子句中出现的属性有所限制。
任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中。
正确查询
select dept_name,avg(salary) as avg_salary
from instructor2
group by dept_name
having avg(salary)>3000

结果如下

集合成员资格

in：测试元组是否是集合的成员
not in：测试元组是否不是集合的成员
in和 not in 操作符用于枚举集合。
例
select *
from instructor2
where name not in('ai','ye','er');

结果如下

例： 注意内层查询语句的select对应的属性应与外层查询语句的where对应的属相同。

select *
from instructor2
where name in (select name
from instructor2
where salary>3000
);

过程：相当于两层for循环

结果如下

集合的比较

>some：至少比某一个要大
>all：比所有的都大。
>some、<some、>=some、<script type="math/tex" id="MathJax-Element-47"><=</script>some、<script type="math/tex" id="MathJax-Element-48"><></script>some
all、<script type="math/tex" id="MathJax-Element-52"><</script>all、<script type="math/tex" id="MathJax-Element-53"><</script>all
=some等价于in，<>some并不等价于not in。
<script type="math/tex" id="MathJax-Element-56"><></script>all等价于not in，=all不等价于in。
例：查询工资大于3000的教师姓名

select *
from instructor2
where name =some(select name
from instructor2
where salary>3000
);

结果如下

例：查询工资小于等于3000的教师姓名

select *
from instructor2
where name <>all(select name
from instructor2
where salary>3000
);

错误查询实例
例：查询工资小于等于3000的教师姓名

select *
from instructor2
where name <>some(select name
from instructor2
where salary>3000
);

结果如下（分析：内层查询中只要有一条元组的salary>3000并且name和外层查询name值不相等即可）

例：查询工资大于3000的教师姓名

select *
from instructor2
where name =all(select name
from instructor2
where salary>3000
);

结果如下（分析：内层查询中满足salary>3000的所有元组的name都和外层查询name值相等才可以）

空关系测试（exists）

存在exists是相对于某一条元组，因而子查询中是select *。
例：查询在2009年秋季学期和2010年春季学期同时开课的所有课程

select course_id
from section as S
where semester='Fall' and year=2009 and exists(select *
from section as T
where semester='Spring' and year=2010
and S.course_id=T.course_id
);

删除某些元组
例：从instructor关系中删除与‘math’系教师有关的所有元组

delete from instructor
where dept_name='math';

删除所有元组
delete from instructor;
1删除的特殊情况

内层语句只有一条元组。
例：删除工资低于大学平均工资的教师记录。

delete from instructor2
where salary < (
select avg(salary)
from instructor2
);

select *
from instructor2;

结果如下

插入一些元组

insert into 关系名 values(⋯
⋯
);一次只能插入一条元组。
insert into 关系名 selec查询语句可一次性插入多条。
例

insert into instructor2
select id,name,dept_name,18000
from student
where dept_name='math';

更新元组
update 关系名
set 语句
where 条件；

例：只给工资低于7000元的教师涨工资。

update instructor2
set salary=salary*1.05;
where salary<7000;

例：给工资低的教师涨5%，大于7000的涨3%

update instructor2
set salary=case
when salary<=7000 then salary*1.05
else salary*1.03
end

 

参考：https://blog.csdn.net/liu450668867/article/details/80071201

https://blog.csdn.net/YEN_CSDN/article/details/51814912

https://blog.csdn.net/weixin_40087851/article/details/81978347

https://blog.csdn.net/qq_36982160/article/details/89258056