简介
数据库这门课程主要是目的是让我们能根据具体问题设计，创建并运维一个数据库
数据库设计阶段：
- 需求分析：
- 在这个阶段，设计者需要收集和理解所有潜在用户的信息需求。这通常涉及与用户沟通，明确数据库的目的、功能和性能要求。
- 设计师会编写软件需求规格说明书(SRS)，描述数据库应该做什么，以及初步的用户手册。
- 需求分析确保设计符合业务目标，并且解决了用户的具体问题。
- 概念结构设计：
- 这个阶段的目标是创建一个反映所有用户信息需求的概念性数据模型，通常是通过实体关系图(E-R图)来实现。
- E-R图定义了实体、属性和它们之间的关系，不依赖于任何特定的数据库管理系统(DBMS)。
- 概念设计帮助设计者和用户对数据的组织方式有一个共同的理解。
- 逻辑结构设计：
- 在这一阶段，概念设计被转化为特定DBMS的数据模型，如关系模型。
- 设计者将E-R图转换为表、列和键，以及可能的关系和约束。
- 逻辑设计还可能涉及到数据规范化，以减少数据冗余和提高数据一致性。
- 物理结构设计：
- 物理设计考虑如何在特定硬件和软件环境下最有效地存储和访问数据。
- 设计者会决定数据文件的布局、索引的使用、存储分配和数据分布。
- 目标是优化数据库性能，确保数据的可靠性和安全性。
- 数据库实施：
- 实施阶段涉及在实际的计算机系统中建立数据库，包括数据的加载和测试。
- 设计者会创建数据库模式，编写SQL语句，填充初始数据，以及执行测试以确保一切按计划运行。
- 数据库运行和维护：
- 数据库投入运行后，需要持续监控其性能，确保数据的安全性和完整性。
- 维护工作包括定期备份数据、恢复操作、性能调优、安全性更新和适应业务变化的结构调整。
- 这个阶段还包括用户培训和技术支持，以确保数据库的有效利用。
数据库
应用流程
- DBMS（数据库管理系统）：
- 作用： 数据库管理系统允许我们直接使用SQL语言来操作数据库。
- 类型： 分为关系型和非关系型数据库，而在这里我们使用的是关系型数据库
- 例子： MySQL、PostgreSQL、Oracle、Microsoft SQL Server
- 管理：Deaver， MySQL-Front，Navicat
- GUI（图形化界面）：
- 作用： 为了方便可视化管理数据库，我们使用了Hasura，这是一个图形化界面，让我们更直观地管理数据库的内容。
- 翻译器：
- 原因： 因为直接使用SQL语句编程操控数据库有时候比较繁琐。
- 具体应用： 在这个技术栈中，我们使用了Apollo，这就像一个翻译官，把我们用 TypeScript（ts） 编写的语句转换成数据库能理解的 SQL 语句。
- 查询语言：
- 作用： 为了使客户端能够按照自己的需求来获取数据，我们使用了GraphQL。
- 特点： GraphQL 允许客户端明确地指定需要哪些数据，避免了一次性获取大量不必要的信息。
综合起来，整个技术栈的流程是这样的：我们通过图形界面 Hasura 管理 postgreSQL 数据库，通过 Apollo 翻译 TypeScript 代码为数据库理解的 SQL 语句，并最终使用 GraphQL 作为查询语言，使得客户端能够更精准地获取所需的数据。这样的架构有助于提高开发效率和灵活性。
当我们谈论整个技术栈时，实际上涉及了一系列步骤，而在Web开发中，这些步骤可以被具体化为以下几个阶段：
DBMS->GUI->翻译器->查询语言
在web中具体如下:
postgreSQL->Hasura->Apollo+ts->GraphQL
数据库分类
- 关系型数据库(SQL)：
- 类比于Excel，数据以表的形式存储，具有行和列。
- 代表性的关系型数据库有MySQL、Oracle、SQL Server、DB2、SQLlite。
- 数据存储通过表之间、行和列之间的关系来进行。
- 最常用的数据库类型
- 非关系型数据库(NoSQL - Not Only SQL)：
- 数据以{key: value}的形式存储，强调灵活性。
- 代表性的非关系型数据库有Redis、MongoDB。
- 数据存储通过对象的自身属性来决定操作，支持动态更新。
设计
E-R图
即实体关系图（Entity-Relationship Diagram），用来对我们需要管理的现实数据进行第一步抽象，便于理清逻辑
- 实体：用矩形表示，如教师，学生
- 属性：用圆形表示，如编号，姓名
- 关系，用菱形表示，如选课
如下e-r图
关系模型
转换方法
- 第一步：将各个实体的名字转换为各个关系模式的名字
- 第二步：实体的属性就是关系的属性，实体的码就是关系的码，并加下划线
- 第三步：实体间联系的转换
- 1对n联系：将1方的主码加入n方作为外码，并同时将联系的属性加
入n方 - n对m联系：将联系本身转换为一个关系模式，将联系双方的主码加
入其中设为码，并将联系的属性也加入其中
上面的e-r图转换为关系模型如下，共四个实体，两个关系
- 班级 (班级号, 学生人数, 专业)
- 学生 (学生学号, 姓名, 年龄, 性别,班级)
- 课程 (课程号, 课程名, 课时,学分)
- 教师 (教师编号, 姓名, 年龄, 职称, 性别)
- 任课 (教师编号, 课程号,时间)
- 选课 (学生学号,课程号, 成绩)
关系
数据库的理论工具，具体实际应用中一般不涉及
关系理论
候选码
基于集合论，主要是用于化简减少数据库的冗余性。
研究对象有属性集合如U={A，B，C，D，E，G}，函数依赖集如F={AB->C，CD->E，E->A，A->G}
通过对函数依赖集进行分析，找出能够推导出所有属性的属性组，即候选码
由函数依赖可以得到
- 一定属于候选码的属性：只出现在左边，或者左右都没出现
- 可能属于候选码的属性：左右都出现
- 不属于候选码的属性：只出现在右边
然后就从一定属于候选码的属性组开始求闭包，如果不能推导出全部，则添加可能属于候选码的，直到求出所有的候选码组合。
这里的候选码的意思是可以作为主码的候选属性组。也就是sql中的primary key
范式
- 1NF：所有属性都是不可分割的数据项，是关系数据库需要满足的最低要求
- 2NF：在满足1NF的前提下，不包含非主属性对码的部分函数依赖（即每
一个非主属性都完全函数依赖于码） - 3NF：在满足2NF的前提下，不包含非主属性对码的传递函数依赖（即码
应该直接决定非主属性，不能间接决定） - BCNF：消除任何属性对候选码的传递依赖，即每一个决定因素都包含
码，表现为在函数依赖集当中，左边的都包含候选码（整个属性组！）
我们一般将最终会将数据库化为bcnf，减少冗余，提高性能
关系代数
查询语言
关系代数的基本运算包括：
- 选择（Selection）：
- 选择是从关系中选取满足特定条件的元组（行）。记作σ（sigma），例如σ®，其中R是关系名，是谓词，表示选择的条件。
- sql中的where
- 投影（Projection）：
- 投影是从关系中选择特定的属性（列），并丢弃其他属性。记作π（pi），例如π®，其中是要保留的属性列表。
- sql中的select
- 联接（Join）：
- 联接是从两个关系中选择那些满足某个条件的行的组合。联接操作结合了笛卡尔积和选择操作，通常用于结合具有公共属性的关系。常见的联接类型包括θ-联接、等值联接和自然联接。
- sql中的from里面包含多表时会自动自然连接
- 差集（Difference）：
- 差集是从一个关系中删除另一个关系中出现的所有行。记作R - S，即在R中但不在S中的行。
- sql中的except
- 除法（Division）：
- 除法是一种更复杂的运算，用于从两个关系中找到满足特定条件的行的集合。它通常用于查找在第一个关系的每一行中都出现的第二个关系的属性的值。
- sql中一般用两层not exist嵌套子查询实现
- 如查询满足条件R的所有S，另外有二者的关系RS
SELECT S.attr from S
WHERE NOT EXISTS
(SELECT *FROM R
WHERE NOT EXiSTS
(SELECT *FROM RS
WHERE S.attr1=RS.attr1 AND R.attr2=RS.attr2));SQL
简介
SQL（Structured Query Language）是一种用于管理和操作关系型数据库管理系统（RDBMS）的特定领域语言。它是一种标准化的语言，用于定义和操作关系型数据库中的数据。SQL允许用户执行诸如查询数据、插入新数据、更新现有数据和删除数据等操作。
分为四种
- DDL：数据库定义语言（define）
- DML：数据库操作管理语言（manage）
- DQL：数据库查询语言（query）
- DCL：数据库控制语言（control）
DDL
一个database包含多张表，表用于存储行列的信息
数据库
创建
create database [if not exists] new;删除
drop database [if exists] old;使用
use school;
-- 如果表名或字段名是一个特殊字符，就需要`name`查看
show databases;
show create database school; -- 查看创建数据库的语句表
创建
格式
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集类型][注释]
-- 行内用空格分隔，每行后面跟英文逗号，最后一行不加逗号例
create table [if not exists] `student`(
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
primary key(`id`)
)engine=innodb default charset=utf8查看
show create table student; -- 查看student数据表的定义语句
desc student; -- 显示student表的结构修改
alter table student rename as teacher;
alter table student add age int(2); -- 增加表的字段
alter table student modify age varchar(2); -- 修改字段约束
alter table student change age year varchar(4); -- 字段重命名
alter table student drop age;删除
drop table [if exists] student;DML
添加
insert into student([`name`,`pwd`]) values('alice','123456'),('bob','qwerty');
-- 由于主键自增，我们可以省略
-- 如果不写表的字段，默认一一匹配修改
update `student` set `name`='cathy', `pwd`='888888' where id = 1;
-- 不指定条件的情况下，会改动所有行修改的值可以是常值，也可以用变量赋值
条件：where子句，执行逻辑判断
常用操作符：
- =, >, <, >=, <=：与Java相同
- <>或!=：不等于
- between…and…：闭区间
- and：&&
- or：||
删除
delete from `student` where id=1;清空
truncate table `student`;
-- 优于delete from `student`与delete相比
相同点：都能删除数据，都不会删除表结构
不同点：truncate会重新设置自增列，计数器会清零
DQL
创建数据库
下面查询演示基于这个数据库
-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
USE `school`;
-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR(50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard` (`identitycard`),
KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`subjectno` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT(4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入学生数据，这里只添加了2行，其余自行添加
INSERT INTO `student` (`studentno`, `loginpwd`, `studentname`, `sex`, `gradeid`, `phone`, `address`, `borndate`, `email`, `identitycard`)
VALUES
(1000, '123456', '张伟', 0, 2, '13800001234', '北京朝阳', '1980-1-1', 'text123@qq.com', '123456198001011234'),
(1001, '123456', '赵强', 1, 3, '13800002222', '广东深圳', '1990-1-1', 'text111@qq.com', '123456199001011233');
-- 插入成绩数据，这里仅插入了一组，其余自行添加
INSERT INTO `result` (`studentno`, `subjectno`, `examdate`, `studentresult`)
VALUES
(1000, 1, '2013-11-11 16:00:00', 85),
(1000, 2, '2013-11-12 16:00:00', 70),
(1000, 3, '2013-11-11 09:00:00', 68),
(1000, 4, '2013-11-13 16:00:00', 98),
(1000, 5, '2013-11-14 16:00:00', 58);
-- 插入年级数据
INSERT INTO `grade` (`gradeid`, `gradename`) VALUES (1, '大一'), (2, '大二'), (3, '大三'), (4, '大四'), (5, '预科班');
-- 插入科目数据
INSERT INTO `subject` (`subjectno`, `subjectname`, `classhour`, `gradeid`)
VALUES
(1, '高等数学-1', 110, 1),
(2, '高等数学-2', 110, 2),
(3, '高等数学-3', 100, 3),
(4, '高等数学-4', 130, 4),
(5, 'C语言-1', 110, 1),
(6, 'C语言-2', 110, 2),
(7, 'C语言-3', 100, 3),
(8, 'C语言-4', 130, 4),
(9, 'Java程序设计-1', 110, 1),
(10, 'Java程序设计-2', 110, 2),
(11, 'Java程序设计-3', 100, 3),
(12, 'Java程序设计-4', 130, 4),
(13, '数据库结构-1', 110, 1),
(14, '数据库结构-2', 110, 2),
(15, '数据库结构-3', 100, 3),
(16, '数据库结构-4', 130, 4),
(17, 'C#基础', 130, 1);查询
选择
select version(); -- 查询系统版本（函数）
select 100*3-1 as 计算结果; -- 用于计算（计算表达式）
select @@auto_increment_increment; -- 查询自增的步长（变量）查询的是一个代码表达式,参考顺序
select [all | distinct]
{* | table.* | [table.field1[as alias1]][,table.field2[as alias2]][...]}
from table_name [as table_alias]
[left | right | inner join table_name2]
[where ...]
[group by ...]
[having]
[order by ...]
[limit {[offset,]row_count | row_countOFFSET offset}];指定查询字段
select * from student -- 查询全部学生
select `studentno`,`studentname` from student -- 查询指定字段
select `studentno` [as] 学号 from student -- 查询结果中表头用别名显示，也可以给表起别名
select concat('姓名：',studentname) [as] 新名字 from student
select `studentresult`+1 as 提分后 from result去重
select distinct `studentno` from result-- 查询有哪些同学参加了考试匹配
使用where条件子句
作用：检索数据中符合条件的值
返回：布尔值
逻辑运算符
- and或&&：a and b
- or或||：a or b
- not或!：not a
例如
select `studentno` from result where not `studentno` = 1000模糊
本质：比较运算符
- is null / is not null：是否为空
- between and
- like：a like b，SQL匹配，若a匹配b，则结果为真
- in：a in (a1,a2,…)，a在a1,a2,…中，则结果为真，必须完全符合
-- 查询姓刘的同学
SELECT `studentno`, `studentname` FROM `student` WHERE `studentname` LIKE '刘%';
-- 查询姓刘、名为单字的同学
SELECT `studentno`, `studentname` FROM `student` WHERE `studentname` LIKE '刘_';
-- 查询姓刘、名为双字的同学
SELECT `studentno`, `studentname` FROM `student` WHERE `studentname` LIKE '刘__';
-- 查询名字中间有嘉字的同学
SELECT `studentno`, `studentname` FROM `student` WHERE `studentname` LIKE '%嘉%';
-- 查询指定的学生
SELECT `studentno`, `studentname` FROM `student` WHERE `studentno` IN (1001, 1002, 1003);
-- 查询家乡为北京的学生，注意'北京%'是非法的
SELECT `studentno`, `studentname` FROM `student` WHERE `address` IN ('北京');
-- 查询地址为空的学生
SELECT `studentno`, `studentname` FROM `student` WHERE `address` = '' OR `address` IS NULL;连表
join on：连接查询
where：等值查询
on是对生成临时表时的判断条件（省空间），where是临时表生成后对其筛选的条件
-- 查询参加了考试的同学（要求得到其学号、姓名、科目编号、分数）
/*思路：
1、分析需求：分析查询的字段来自哪些表
2、确定使用哪种连接查询（这两个表中哪个数据是相同的？）
3、确定判断条件
*/
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno分页和排序
排序：升序asc；降序desc
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
limit 5,5 -- 第6-10条数据DCL
授权
GRANT SELECT, INSERT ON table_name TO user_name;收回权限
REVOKE UPDATE ON table_name FROM user_name;拒绝权限
DENY DELETE ON table_name TO user_name;授予带授权选项的权限
GRANT SELECT ON table_name TO user1 WITH GRANT OPTION;创建角色
CREATE ROLE role_name;将权限授予角色
GRANT role_name TO user_name;从用户中撤销角色
REVOKE role_name FROM user_name;并发
事务
即一段实现特定功能的sql语句，以事务为单位进行执行，因为某些操作会耗时比较多从而阻塞，如where执行过程，所以需要并发来提高性能
并发
并发会遇到的问题，对同一个数据的读取先后顺序，会导致不同的结果
面对这个问题，我们最常用的手段就是封锁
而封锁会面临的死锁的问题，所以我们这里使用两段锁协议来封锁
两段锁协议(2PL)，即把加锁和解锁过程分为两个阶段
- 获得封锁 : 在对任何数据进行读、写操作之前，事务首先要获得对该数据的封锁
- 释放封锁∶在释放一个封锁之后，事务不再申请和获得任何其他封锁
运维
日志文件
必须遵循两条规则
- 登记的次序严格按照并发事务执行的时间次序
- 必须先写日志文件，再写数据库。
转储的冗余数据是指日志文件，数据库后备副本
故障
分类
- 事务故障：一个事务未能成功完成其预定的操作，通常是由程序错误、逻辑错误或数据错误引起的。
- 系统故障：由于硬件故障（如电源中断）、操作系统崩溃或数据库管理系统软件错误等原因，导致系统突然停止运行的情况。
- 介质故障：存储数据库数据的物理介质（如磁盘、固态硬盘等）发生故障，导致数据丢失或变得不可访问。
恢复
- 事务故障的恢复：由系统自动完成，对用户透明:利用日志文件撤销(UNDO)此事务已对数据库进行的修改
- 系统故障的恢复：由系统重新启动时自动完成:撤销(UNDO)故障发生时未完成的事务，重做(REDO)已完成的事务
- 介质故障的恢复：最严重的一种故障:重装数据库，重做(REDO)已完成的事务
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
