数据库学习笔记

Posted on 2021-05-11 Edited on 2023-02-15 In Computer

数据库入门

数据库相关从业

前面五种：重点是设计和应用，侧重于软件和数据逻辑层面。

数据库应用开发 (application development) > 除了基本的SQL方面的知识，还要对开发流程，软件工程，各种框架和开发工具等等 > 数据库应用开发这个方向上的机会最多，职位最多

数据建模专家 (data modeler) > 除了基本的SQL方面的知识，非常熟悉数据库原理，数据建模 > 负责将用户对数据的需求转化为数据库物理设计和物理设计 > 这个方向上在大公司（金融，保险，研究，软件开发商等）有专门职位， > 在中小公司则可能由程序员承担。

商业智能专家 (business intelligence - BI) > 主要从商业应用，最终用户的角度去从数据中获得有用的信息， > 涉及OLAP (online analytical processing) > 需要使用SSRS, cognos, crystal report等报表工具，或者其他一些数据挖掘，统计方面的软件工具

ETL开发 (ETL Developer) > 使用ETL工具或者自己编写程序在不同的数据源之间对数据进行导入，导出，转换， > 所接触的数据库一般数据量非常大，要求进行的数据转换也比较复杂。 > 和数据仓库和商业智能的关系比较密切。

在一些数据库应用规模很大的公司里面有专门的职位，中小公司里面则可能由程序员或者DBA负责这方面的工作。

数据构架师 (Data Architect) > 主要从全局上制定和控制关于数据库在逻辑这一层的大方向， > 也包括数据可用性，扩展性等长期性战略， > 协调数据库的应用开发，建模，DBA之间的工作。 > 这个方向上在大公司（金融，保险，研究，软件开发商等）有专门职位， > 在中小公司或者没有这个职位，或者由开发人员，DBA负责。

后面五种：重点是运营和维护，侧重与硬件和数据物理层面。

数据库管理员 (database administrator - DBA) > 数据库的安装，配置，调优，备份/恢复，监控，自动化等， > 协助应用开发（有些职位还要求优化SQL，写存储过程和函数等） > 这个方向上的职位相对少一些，但一般有点规模的公司还是会有这样的职位

数据仓库专家 (data warehouse - DW) > 应付超大规模的数据，历史数据的存储，管理和使用， > 和商业智能关系密切，很多时候BI和DW是放在一个大类里面的， > 但是我觉得DW更侧重于硬件和物理层上的管理和优化。

存储工程师 (storage engineer) > 专门负责提供数据存储方案，使用各种存储技术满足数据访问和存储需求， > 和DBA的工作关系比较密切。 > 对高可用性有严格要求（比如通信，金融，数据中心等）的公司通常有这种职位， > 这种职位也非常少。

性能优化工程师 (performance engineer) > 专长数据库的性能调试和优化，为用户提供解决性能瓶颈方面的问题。 > 我知道至少IBM, 微软和Oracle都有专门的数据库性能实验室(database performance lab）, > 也有专门的性能优化工程师，负责为其数据库产品和关键应用提供这方面的技术支持。 > 对数据库性能有严格要求的公司（比如金融行业）可能会有这种职位。 > 因为针对性很强，甚至要求对多种数据库非常熟悉，所以职位极少。

高级数据库管理员 (senior DBA) > 在DBA的基础上，还涉及上面3种职位的部分工作，具体包括下面这些： > 对应用系统的数据（布局，访问模式，增长模式，存储要求等）比较熟悉。 > 对性能优化非常熟悉，可以发现并优化从SQL到硬件I/O，网络等各个层面上的瓶颈 > 对于存储技术相对熟悉，可能代替存储工程师的一些工作， > 对数据库的高可用性技术非常熟悉（比如MSSQL的集群，ORACLE RAC/FailSafe, IBM的DPF, HADR等） > 对大规模数据库有效进行物理扩展（比如表分区）或者逻辑扩展（比如数据库分区，联合数据库等） > 熟悉各种数据复制技术，比如单向，双向，点对点复制技术，以满足应用要求。 > 灾难数据恢复过程的建立，测试和执行 > 这种职位一般只在对数据库要求非常高并且规模非常大（比如金融，电信，数据中心等）的公司需要， > 而且这种公司一般有一个专门独立负责数据库的部门或组。

关系型数据库

说到关系型数据库，不仅仅是涉及计算机概念，也涉及一些数学概念，例如表格的概念、包含关系、关系运算及关系代数等。

关系模型术语

表：关系型数据库由表集合而成，表是关系型数据库的一个基本组成，每个表具有唯一的名字。
关系：在关系型数据库中，关系指代表的概念，即一般可认为 关系 = 表。
元组：在关系型数据库中，元组指代行的概念，即一般可认为 元组 = 行。
属性：在关系型数据库中，属性指代列的概念，即一般可认为 属性 = 列。
域：属性下所有元素的可取值范围或取值类型。
原子域：域中的元素不可再分，则称为原子域。
空值：属性下元素为空(NULL)。
关系实例：即表实例，严谨说法是 “表示一个关系的特定实例，也就是所包含的一组特定的行”，就是不仅仅只有属性这一行，还具有特定的某些具体数据的表格。
关系模式：即表模式，严谨说法是“关系模式由属性序列及各属性对应域组成”，通俗地说就是，没有具体数据，而仅定义了属性和域的表格。
数据库模式：对数据库的逻辑设计。
数据库实例：对数据库中数据的快照。
超码：是一个或多个属性的集合，超码内的一个或多个属性组合可以唯一区分和标识一个元组。
候选码：最小超码。
主码：主码是候选码的一种，最重要的是，主码由设计者选中，具有人为性。
外码：用于表示关系之间的引用和被引用关系。即关系A中的某项属性来自于关系B中的某项属性，则称该引用过来的属性为外码。但据课本，是被引用的主码称为外码。

结构化查询语言SQL

发展历史

数据库必知词汇：SQL标准

SQL标准是由国际标准化组织（ISO）、美国国家标准委员会ANSI等制定的，对数据库管理系统的统一操作方式。

SQL 是 Structured Query Language 的缩写，规范的发音是 “ˌɛsˌkjuːˈɛl”。但是它的前身是著名的关系数据库原型系统 System R 所采用的 SEQUEL 语言，这也是为什么有很多人将其读作 “ˈsiːkwəl” 的来源。也有人类比 GNU 这个词的定义（GNU’s Not UNIX），认为 SQL 是 SQL Query Language 的缩写。

IBM对关系数据库以及SQL语言的形成和规范化产生了重大的影响，第一个版本的SQL标准SQL86就是基于System R的手册而来的。Oracle在1979年率先推出了支持SQL的商用产品。随着数据库技术和应用的发展，为不同RDBMS提供一致的语言成了一种现实需要。

对SQL标准影响最大的机构自然是那些著名的数据库产商，而具体的制订者则是一些非营利机构，例如国际标准化组织ISO、美国国家标准委员会ANSI等。各国通常会按照ISO标准和ANSI标准制定自己的国家标准。

“美国国家标准化组织(ANSI)”是一个核准多种行业标准的组织。SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI SQL作为国际标准。

SQL发展的简要历史： 1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86 1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89 1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2） 1999年，ISO/IEC 9075:1999，SQL:1999（SQL3） 2003年，ISO/IEC 9075:2003，SQL:2003 2008年，ISO/IEC 9075:2008，SQL:2008 2011年，ISO/IEC 9075:2011，SQL:2011 补充 1986 年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86。这是 ANSI 首次将 SQL 语言标准化的版本。 1989 年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89。增加了完整性约束。 1992 年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）。最重要的一个版本。引入了标准的分级概念。 1999 年，ISO/IEC 9075:1999，SQL:1999（SQL3）。变动最大的一个版本。改变了标准符合程度的定义；增加了面向对象特性、正则表达式、存储过程、Java 等支持。 2003 年，ISO/IEC 9075:2003，SQL:2003。引入了 XML、Window 函数等。 2008 年，ISO/IEC 9075:2008，SQL:2008。引入了 TRUNCATE 等。 2011 年，ISO/IEC 9075:2011，SQL:2011。引入了时序数据等。 2016 年，ISO/IEC 9075:2016，SQL:2016。引入了 JSON 等。

绝大多数人提起SQL标准，涉及的内容其实是SQL92里头最基本或者说最核心的一部分。SQL92本身是分级的，包括入门级、过度级、中间级和完全级，推荐泛读SQL92，增量式式阅读其他标准。而随着SQL标准的发展，其包含的内容实在太多了，而且有很多特性对新的SQL产品而言也越来越不重要了。从SQL99之后，标准中符合程度的定义就不再分级，而是改成了核心兼容性和特性兼容性；也没有机构来推出权威的SQL标准符合程度的测试认证了。

作用

数据库查询
定义数据结构
修改数据
说明安全性约束条件

基本数据类型

以上所有类型都可能包含一个被称作空值的特殊值。NULL表示一个缺失的值，即表示该值可能存在但并不为人所知，或者可能根本不存在。

关于char / varchar / nvarchar的比较

SQL完整性约束

例如，上方教员的名称为 “张三”，我想删除后保存，会破坏 instructor 关系下 name 的非空约束，SQL不通过这种操作。

数据库操作

新建表格

数据表格新建格式及样例如下：

其中r是关系名，即表名；A_x是属性名，即列名；D_x是属性的域，即指定了A的类型及可选约束，用于限制所允许的A_x取值的集合。

/*部门关系*/
create table department(
    dept_name varchar(20),
    building varchar(15),
    budget numeric(12,2),
    primary key(dept_name)
);

/*课程关系*/
create table course(
    coourse_id varchar(7),
    title varchar(50),
    dept_name varchar(20),
    credits numeric(2,0),
    primary key(course_id),
    foreign key (dept_name) references department
);

/*教员关系*/
create table instructor(
	ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    salary numeric(8,2),
    primary key(ID),
    foreign key(dept_name) references department
);

/*（课程）章节关系*/
create table section(
	course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4,0),
    building varchar(7),
    time_slot_id varchar(4),
    primary key(course_id, sec_id, semester, year),
    foreign key(course_id) references course
);

/*教师关系*/
create table teachers(
	ID varchar(5),
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4,0),
    primary key(ID, course_id, sec_id, semester, year),
    foreign key(course_id, sec_id, semester, year) references section,
    foreign key(ID) references instructor
);

foreign key 可以拿来当 primary key，如章节关系中所示。

not null 关键字约束表明 “在该属性上不允许空值” 。

插入

插入值过程中，所给出的值的顺序应与关系模式中的属性顺序一致。

删除

delete 命令可以用于删除元组。

如果直接使用命令 delete from student ，则将从 student 关系中删除所有元组。
使用其他语句可以执行特定删除，如：
- 使用命令 delete from instructor where salary between 13000 and 150000; ，删除所有工资在13000到15000美元之间的教师
- 其他操作……

Coder的孤岛