【MySQL速通篇】5000字吃透MySQL部分重要知识点

本文链接：https://blog.youkuaiyun.com/weixin_71690375/article/details/130984128

一、主键和外键知识点补充

✨1.1、主键的概念

1、什么时候用主键？2、主键有什么用处 3、一张表可以设置几个主键？4、一个主键只能是一列吗？5、主键和唯一索引有什么区别？

1）每个表应该有一个主键 。定义一个保证唯一标识每个logging的主键。

2）数据库主键，指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。主键主要是用与其他表的外键关联，以及文本记录的修改与删除。

3）一张表只可以有一个主键

4）主键不一定只有一列，有些表的主键是多个属性构成的。表定义为列的集合

5）主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。 1、主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。 2、唯一性索引列允许空值，而主键列不允许为空值。 3、主键列在创建时，已经默认为空值 + 唯一索引了。 4、主键可以被其他表引用为外键，而唯一索引不能。

✨1.2、主键的创建

🎉1.2.1、创建一个主键

比如我们要创建一张名为 "tb1"的表并且将它的id列设置为主键

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql">creat <span style="color:#0000ff">table</span> tb1(
		id <span style="color:#a31515">int</span> <span style="color:#0000ff">not</span> <span style="color:#0000ff">null</span> auto_increment <span style="color:#0000ff">primary</span> key,
)
</code></span></span>

🎉1.2.2、创建多个主键

为“tb1”的表创建多个主键

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql">creat <span style="color:#0000ff">table</span> tb1(
	id <span style="color:#a31515">int</span> <span style="color:#0000ff">not</span> <span style="color:#0000ff">null</span> auto_increment ,
	pid <span style="color:#a31515">int</span>(<span style="color:#880000">11</span>) <span style="color:#0000ff">not</span> <span style="color:#0000ff">NULL</span>,
	<span style="color:#0000ff">primary</span> key(id, pid)
)
</code></span></span>

🍀二、补充知识点

✨2.1、desc 表名;

desc tablenames 主要用来查看数据表的表结构

比如用以下命令创建了一张‘user’表

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql"><span style="color:#0000ff">create</span> <span style="color:#0000ff">table</span> <span style="color:#0000ff">user</span>(
id <span style="color:#a31515">int</span> auto_increment <span style="color:#0000ff">primary</span> key,
name <span style="color:#a31515">varchar</span>(<span style="color:#880000">10</span>),
gender <span style="color:#a31515">char</span>(<span style="color:#880000">2</span>)
)engine<span style="color:#ab5656">=</span>innodb <span style="color:#0000ff">default</span> charset<span style="color:#ab5656">=</span>utf8;
</code></span></span>

使用以上命令后可以得到如下结果:
编辑

✨2.2、show create table 表名;

该语句的功能：查看表创建时的定义

列如对上面的‘user’表执行该操作得到如下结果：
编辑
但是上面的结果看起来非常混乱，我们可以使用\G【\G 的作用是将查到的结构旋转90度变成纵向】使得结果更加美观
编辑

🍀三、自增列起始值设置

我们首先查看上面user表中的数据
编辑
不难看出这个表的id列已经自增到了5，其中show create table 表名；可以看出AUTO_INCREMENT=6,这个就表示接下来id列要递增成为的数字，
编辑
下一步我们使用 delete from user;语句来删除这个表

接着我们使用insert into user (name, gender) values('周周', '男');再向表中插入一列会发现id列是从6开始递增
编辑
这时我们就会想，可不可以重新从1开始，或者自定义开始递增时的值呢？

✨3.1、设置自增列初始值语句

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql"><span style="color:#0000ff">alter</span> <span style="color:#0000ff">table</span> <span style="color:#0000ff">user</span> AUTO_INCREMENT<span style="color:#ab5656">=</span><span style="color:#880000">1</span>;
</code></span></span>

编辑要特别注意如果设置的初始值小于原来表递增列最后一个数据的值，那么语句是不会生效的

🍀四、自增列步长设置

✨4.1、步长设置语法

步长设置我就不举例了，上面是设置递增列的初始值，这个是步长【不设置默认是1】

语法：

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql"><span style="color:#0000ff">set</span> session auto_increment_increment<span style="color:#ab5656">=</span><span style="color:#880000">2</span>; 	设置会话步长
<span style="color:#0000ff">show</span> session variables <span style="color:#0000ff">like</span> <span style="color:#a31515">'auto_inc%'</span>;	查看全局变量
</code></span></span>

但是要强调一点：

MySQL: 自增步长是基于会话级别的【登入一次mysql就是一次会话】，改变一次步长之后同一次会话创建的所有表的步长都会改变为你设置的步长；

SqlServer：自增步长：是基础表级别的：可以单独的对某一张表的步长进行设置，而不改变全局的步长；

总结：SqlServer方便一点

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql">MySQL: 自增步长
			基于会话级别：
				<span style="color:#0000ff">show</span> session variables <span style="color:#0000ff">like</span> <span style="color:#a31515">'auto_inc%'</span>;	查看全局变量
          <span style="color:#0000ff">set</span> session auto_increment_increment<span style="color:#ab5656">=</span><span style="color:#880000">2</span>; 	设置会话步长
				# <span style="color:#0000ff">set</span> session auto_increment_offset<span style="color:#ab5656">=</span><span style="color:#880000">10</span>;
			基于全局级别（接下来开的所有会话的步长都会改变）：
				<span style="color:#0000ff">show</span> <span style="color:#0000ff">global</span> variables <span style="color:#0000ff">like</span> <span style="color:#a31515">'auto_inc%'</span>;	    查看全局变量
          <span style="color:#0000ff">set</span> <span style="color:#0000ff">global</span> auto_increment_increment<span style="color:#ab5656">=</span><span style="color:#880000">2</span>; 	    设置会话步长
				# <span style="color:#0000ff">set</span> <span style="color:#0000ff">global</span> auto_increment_offset<span style="color:#ab5656">=</span><span style="color:#880000">10</span>;     设置起始值


		SqlServer：自增步长：
			基础表级别：
				<span style="color:#0000ff">CREATE</span> <span style="color:#0000ff">TABLE</span> `t5` (
				  `nid` <span style="color:#a31515">int</span>(<span style="color:#880000">11</span>) <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">NULL</span> AUTO_INCREMENT,
				  `pid` <span style="color:#a31515">int</span>(<span style="color:#880000">11</span>) <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">NULL</span>,
				  `num` <span style="color:#a31515">int</span>(<span style="color:#880000">11</span>) <span style="color:#0000ff">DEFAULT</span> <span style="color:#0000ff">NULL</span>,
				  <span style="color:#0000ff">PRIMARY</span> KEY (`nid`,`pid`)
				) ENGINE<span style="color:#ab5656">=</span>InnoDB AUTO_INCREMENT<span style="color:#ab5656">=</span><span style="color:#880000">4</span>, 步长<span style="color:#ab5656">=</span><span style="color:#880000">2</span> <span style="color:#0000ff">DEFAULT</span> CHARSET<span style="color:#ab5656">=</span>utf8

				<span style="color:#0000ff">CREATE</span> <span style="color:#0000ff">TABLE</span> `t6` (
				  `nid` <span style="color:#a31515">int</span>(<span style="color:#880000">11</span>) <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">NULL</span> AUTO_INCREMENT,
				  `pid` <span style="color:#a31515">int</span>(<span style="color:#880000">11</span>) <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">NULL</span>,
				  `num` <span style="color:#a31515">int</span>(<span style="color:#880000">11</span>) <span style="color:#0000ff">DEFAULT</span> <span style="color:#0000ff">NULL</span>,
				  <span style="color:#0000ff">PRIMARY</span> KEY (`nid`,`pid`)
				) ENGINE<span style="color:#ab5656">=</span>InnoDB AUTO_INCREMENT<span style="color:#ab5656">=</span><span style="color:#880000">4</span>, 步长<span style="color:#ab5656">=</span><span style="color:#880000">20</span> <span style="color:#0000ff">DEFAULT</span> CHARSET<span style="color:#ab5656">=</span>utf8
</code></span></span>

🍀五、唯一索引知识点

✨5.1、什么是唯一索引？

所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。唯一索引顾名思义不可以重复，但是可以为空，这也是它与主键的区别之一

✨5.3、创建唯一索引的方式

创建方法一：

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql"><span style="color:#0000ff">CREATE</span> <span style="color:#0000ff">UNIQUE</span> INDEX indexName <span style="color:#0000ff">ON</span> mytable(username(length)) 
</code></span></span>

创建方法二【联合唯一索引】：

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql"><span style="color:#0000ff">UNIQUE</span> indexName (列名,列名),
</code></span></span>

在创建表时的例子：

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql"><span style="color:#0000ff">create</span> <span style="color:#0000ff">table</span> t1(
			id <span style="color:#a31515">int</span> ....,
			num <span style="color:#a31515">int</span>,
			xx <span style="color:#a31515">int</span>,
			<span style="color:#0000ff">UNIQUE</span> 唯一索引名称 (列名,列名),
		# 示例
		<span style="color:#0000ff">UNIQUE</span> uql (num,xx),
		)
</code></span></span>

🍀六、外键变种详细知识点

✨6.1、什么是外键变种

顾名思义就是外键的多种形式，下面会通过举例子的方式讲述

✨6.2、外键变种之一对一

比如我们有两张表【用户表】和【博客表】，如果每个用户只能注册一个博客，那么用户账号与博客账号的外键关系就是一对一

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql">　　用户表:
　　　 id     name     age 
　　　　<span style="color:#880000">1</span>      xaiom    <span style="color:#880000">23</span>  
　　　　<span style="color:#880000">2</span>      eagon    <span style="color:#880000">34</span>
　　　　<span style="color:#880000">3</span>      lxxx     <span style="color:#880000">45</span>
　　　　<span style="color:#880000">4</span>      owen     <span style="color:#880000">83</span>
　　博客表:
　　　id         url        user_id  (外键 <span style="color:#ab5656">+</span> 唯一约束)
　　　　<span style="color:#880000">1</span>       <span style="color:#ab5656">/</span>xiaom       <span style="color:#880000">2</span>
　　　　<span style="color:#880000">2</span>       <span style="color:#ab5656">/</span>zekai            <span style="color:#880000">1</span>
　　　　<span style="color:#880000">3</span>       <span style="color:#ab5656">/</span>lxxx             <span style="color:#880000">3</span>
　　　　<span style="color:#880000">4</span>       <span style="color:#ab5656">/</span>owen             <span style="color:#880000">4</span>
</code></span></span>

✨6.3、外键变种之多对多

这个也是比较容易理解的,就比如我有两张表【用户表】和【主机表】，每个用户可以登入多台主机，同时每台主机也可以被多个用户同时使用，这种关系就是多对多

<span style="background-color:#f5f5fa"><span style="color:#23263b"><code class="language-sql">用户表:
                      id    name    phone 
                      <span style="color:#880000">1</span>    root1    <span style="color:#880000">1234</span>
                      <span style="color:#880000">2</span>    root2    <span style="color:#880000">1235</span>
                      <span style="color:#880000">3</span>    root3    <span style="color:#880000">1236</span>
                      <span style="color:#880000">4</span>    root4    <span style="color:#880000">1237</span>
                      <span style="color:#880000">5</span>    root5    <span style="color:#880000">1238</span>
                      <span style="color:#880000">6</span>    root6    <span style="color:#880000">1239</span>
                      <span style="color:#880000">7</span>    root7    <span style="color:#880000">1240</span>
                      <span style="color:#880000">8</span>    root8    <span style="color:#880000">1241</span>

主机表:

                      id    hostname    
                      <span style="color:#880000">1</span>    c1.com    
                      <span style="color:#880000">2</span>    c2.com    
                      <span style="color:#880000">3</span>    c3.com    
                      <span style="color:#880000">4</span>    c4.com    
                      <span style="color:#880000">5</span>    c5.com    

为了方便查询, 用户下面有多少台主机以及某一个主机上有多少个用户, 我们需要新建第三张表:
用户主机表:

                    id    userid    hostid
                      <span style="color:#880000">1</span>    <span style="color:#880000">1</span>    		<span style="color:#880000">1</span>
                      <span style="color:#880000">2</span>    <span style="color:#880000">1</span>    		<span style="color:#880000">2</span>
                      <span style="color:#880000">3</span>    <span style="color:#880000">1</span>    		<span style="color:#880000">3</span>
                      <span style="color:#880000">4</span>    <span style="color:#880000">2</span>    		<span style="color:#880000">4</span>
                      <span style="color:#880000">5</span>    <span style="color:#880000">2</span>    		<span style="color:#880000">5</span>
                      <span style="color:#880000">6</span>    <span style="color:#880000">3</span>    		<span style="color:#880000">2</span>
                      <span style="color:#880000">7</span>    <span style="color:#880000">3</span>    		<span style="color:#880000">4</span>    
创建的时候, userid 和 hostid 必须是外键, 然后联合唯一索引 <span style="color:#0000ff">unique</span>(userid, hostid)，（避免重复出现）【联合唯一索引在多对多的情况下可以视情况而写】

                  Django orm 也会设计
</code></span></span>