SQL关系模型的知识梳理总结
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li>关系模型<ul class="second_class_ul"><li>
主键</li><li>
外键</li><ul class="third_class_ul"><li>
一对多</li><li>
多对多</li><li>
一对一</li></ul></ul></li><li>
索引<ul class="second_class_ul"><li>
索引的概念与用法</li><ul class="third_class_ul"></ul><li>
唯一索引</li><ul class="third_class_ul"></ul><li>
Tips</li><ul class="third_class_ul"></ul></ul></li></ul></div><div id="navCategory">
<h5 class="catalogue">
目录</h5>
<ul class="first_class_ul">
<li>
关系模型
<ul class="second_class_ul">
<li>
主键</li>
<li>
外键
<ul class="third_class_ul">
<li>
一对多</li>
<li>
多对多</li>
<li>
一对一</li>
</ul>
</li>
</ul>
</li>
<li>
索引
<ul class="second_class_ul">
<li>
索引的概念与用法
<ul class="third_class_ul"></ul>
</li>
<li>
唯一索引
<ul class="third_class_ul"></ul>
</li>
<li>
Tips
<ul class="third_class_ul"></ul>
</li>
</ul>
</li>
</ul>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
关系模型</h2>
<p>
关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。</p>
<p>
表的每一行称为记录（Record），记录是一个逻辑意义上的数据。</p>
<p>
表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。</p>
<p>
字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为<code>NULL</code>。注意<code>NULL</code>表示字段数据不存在。一个整型字段如果为<code>NULL</code>不表示它的值为0，同样的，一个字符串型字段为<code>NULL</code>也不表示它的值为空串<code>''</code>。</p>
<p>
通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。</p>
<p>
和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。</p>
<p>
在关系数据库中，关系是通过主键和外键来维护的。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
主键</h3>
<p>
例如，假设我们把<code>name</code>字段作为主键，那么通过名字小明或小红就能唯一确定一条记录。但是，这么设定，就没法存储同名的同学了，因为插入相同主键的两条记录是不被允许的。</p>
<p>
对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。</p>
<p>
由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。</p>
<p>
所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。</p>
<p>
因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均<em>不可</em>用作主键。</p>
<p>
作为主键最好是完全业务无关的字段，我们一般把这个字段命名为<code>id</code>。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
外键</h3>
<p class="maodian">
</p>
<p class="maodian"></p><h4>
一对多</h4>
<p>
当我们用主键唯一标识记录时，我们就可以在<code>students</code>表中确定任意一个学生的记录：</p>
<table>
<thead><tr>
<th>
id</th>
<th>
name</th>
<th>
other columns…</th>
</tr></thead>
<tbody>
<tr>
<td>
1</td>
<td>
小明</td>
<td>
…</td>
</tr>
<tr>
<td>
2</td>
<td>
小红</td>
<td>
…</td>
</tr>
</tbody>
</table>
<p>
我们还可以在<code>classes</code>表中确定任意一个班级记录：</p>
<table>
<thead><tr>
<th>
id</th>
<th>
name</th>
<th>
other columns…</th>
</tr></thead>
<tbody>
<tr>
<td>
1</td>
<td>
一班</td>
<td>
…</td>
</tr>
<tr>
<td>
2</td>
<td>
二班</td>
<td>
…</td>
</tr>
</tbody>
</table>
<p>
但是我们如何确定<code>students</code>表的一条记录，例如，<code>id=1</code>的小明，属于哪个班级呢？</p>
<p>
由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个<code>classes</code>的记录可以对应多个<code>students</code>表的记录。</p>
<p>
为了表达这种一对多的关系，我们需要在<code>students</code>表中加入一列<code>class_id</code>，让它的值与<code>classes</code>表的某条记录相对应：</p>
<table>
<thead><tr>
<th>
id</th>
<th>
class_id</th>
<th>
name</th>
<th>
other columns…</th>
</tr></thead>
<tbody>
<tr>
<td>
1</td>
<td>
1</td>
<td>
小明</td>
<td>
…</td>
</tr>
<tr>
<td>
2</td>
<td>
1</td>
<td>
小红</td>
<td>
…</td>
</tr>
<tr>
<td>
5</td>
<td>
2</td>
<td>
小白</td>
<td>
…</td>
</tr>
</tbody>
</table>
<p>
这样，我们就可以根据<code>class_id</code>这个列直接定位出一个<code>students</code>表的记录应该对应到<code>classes</code>的哪条记录。</p>
<p>
例如：</p>
<p>
小明的<code>class_id</code>是<code>1</code>，因此，对应的<code>classes</code>表的记录是<code>id=1</code>的一班；小红的<code>class_id</code>是<code>1</code>，因此，对应的<code>classes</code>表的记录是<code>id=1</code>的一班；小白的<code>class_id</code>是<code>2</code>，因此，对应的<code>classes</code>表的记录是<code>id=2</code>的二班。</p>
<p>
在<code>students</code>表中，通过<code>class_id</code>的字段，可以把数据与另一张表关联起来，这种列称为外键。</p>
<p>
外键并不是通过列名实现的，而是通过定义外键约束实现的：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_582640">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">ALTER</code> <code class="sql keyword">TABLE</code> <code class="sql plain">students</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">ADD</code> <code class="sql keyword">CONSTRAINT</code> <code class="sql plain">fk_class_id</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">FOREIGN</code> <code class="sql keyword">KEY</code> <code class="sql plain">(class_id)</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">REFERENCES</code> <code class="sql plain">classes(id)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
其中，外键约束的名称<code>fk_class_id</code>可以任意，<code>FOREIGN KEY (class_id)</code>指定了<code>class_id</code>作为外键，<code>REFERENCES classes (id)</code>指定了这个外键将关联到<code>classes</code>表的<code>id</code>列（即<code>classes</code>表的主键）。</p>
<p>
通过定义外键约束，关系数据库可以保证无法插入无效的数据。**即如果<code>classes</code>表不存在<code>id=99</code>的记录，<code>students</code>表就无法插入<code>class_id=99</code>的记录。</p>
<p>
由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，<code>class_id</code>仅仅是一个普通的列，只是它起到了外键的作用而已。</p>
<p>
要删除一个外键约束，也是通过<code>ALTER TABLE</code>实现的：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_134814">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">ALTER</code> <code class="sql keyword">TABLE</code> <code class="sql plain">students</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">DROP</code> <code class="sql keyword">FOREIGN</code> <code class="sql keyword">KEY</code> <code class="sql plain">fk_class_id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h4>
多对多</h4>
<p>
通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。</p>
<p>
多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系</p>
<p class="maodian">
</p>
<p class="maodian"></p><h4>
一对一</h4>
<p>
一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。</p>
<p>
细心的话会发现，既然是一对一关系，那为啥不给<code>students</code>表增加一个<code>mobile</code>列，这样就能合二为一了？</p>
<p>
如果业务允许，完全可以把两个表合为一个表。但是，有些时候，如果某个学生没有手机号，那么，<code>contacts</code>表就不存在对应的记录。实际上，一对一关系准确地说，是<code>contacts</code>表一对一对应<code>students</code>表。</p>
<p>
还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表<code>user_info</code>和用户详细信息表<code>user_profiles</code>，大部分时候，只需要查询<code>user_info</code>表，并不需要查询<code>user_profiles</code>表，这样就提高了查询速度。</p>
<p>
总结：关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
索引</h2>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
索引的概念与用法</h3>
<p>
在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。</p>
<p>
索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。**通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。</p>
<p>
例如，对于<code>students</code>表：</p>
<table>
<thead><tr>
<th>
id</th>
<th>
class_id</th>
<th>
name</th>
<th>
gender</th>
<th>
score</th>
</tr></thead>
<tbody>
<tr>
<td>
1</td>
<td>
1</td>
<td>
小明</td>
<td>
M</td>
<td>
90</td>
</tr>
<tr>
<td>
2</td>
<td>
1</td>
<td>
小红</td>
<td>
F</td>
<td>
95</td>
</tr>
<tr>
<td>
3</td>
<td>
1</td>
<td>
小军</td>
<td>
M</td>
<td>
88</td>
</tr>
</tbody>
</table>
<p>
如果要经常根据<code>score</code>列进行查询，就可以对<code>score</code>列创建索引：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_187771">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">ALTER</code> <code class="sql keyword">TABLE</code> <code class="sql plain">students</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">ADD</code> <code class="sql keyword">INDEX</code> <code class="sql plain">idx_score(score);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<blockquote>
<p>
使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。</p>
</blockquote>
<p>
索引名称是任意的，索引如果有多列，可以在括号里依次写上。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_206090">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">ALTER</code> <code class="sql keyword">TABLE</code> <code class="sql plain">students</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">ADD</code> <code class="sql keyword">INDEX</code> <code class="sql plain">idx_name_score (</code><code class="sql keyword">name</code><code class="sql plain">, score);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。**反过来，如果记录的列存在大量相同的值，例如<code>gender</code>列，大约一半的记录值是<code>M</code>，另一半是<code>F</code>，因此，对该列创建索引就没有意义。</p>
<p>
可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。</p>
<p>
对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
唯一索引</h3>
<p>
在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。</p>
<p>
但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设<code>students</code>表的<code>name</code>不能重复：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_243158">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">ALTER</code> <code class="sql keyword">TABLE</code> <code class="sql plain">students</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">ADD</code> <code class="sql keyword">UNIQUE</code> <code class="sql keyword">INDEX</code> <code class="sql plain">uni_name(</code><code class="sql keyword">name</code><code class="sql plain">);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
通过<code>UNIQUE</code>关键字我们就添加了一个唯一索引。</p>
<p>
也可以只对某一列添加一个唯一约束而不创建唯一索引：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_554009">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">ALTER</code> <code class="sql keyword">TABLE</code> <code class="sql plain">students</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">ADD</code> <code class="sql keyword">CONSTRAINT</code> <code class="sql plain">uni_name </code><code class="sql keyword">UNIQUE</code> <code class="sql plain">(</code><code class="sql keyword">name</code><code class="sql plain">);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这种情况下，<code>name</code>列没有索引，但仍然具有唯一性保证。</p>
<p>
无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。</p>
<p>
这里的意思是说，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。</p>
<p>
因此，索引可以在使用数据库的过程中逐步优化。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
Tips</h3>
<p>
1.通过对数据库表创建索引，可以提高查询速度。但索引越多，插入和更新的速度越慢。</p>
<p>
2.索引加得不好，查询不会变快，甚至会变慢。</p>
<p>
3.通过创建唯一索引，可以保证某一列的值具有唯一性。</p>
<p>
4.数据库索引对于用户和应用程序来说都是透明的。</p>
<p>
以上就是SQL关系模型的知识梳理总结的详细内容，更多关于SQL关系模型的资料请关注其它相关文章！</p>
<p>
原文链接：https://blog.csdn.net/weixin_38526306/article/details/87349628</p>
頁:
[1]