MYSQL入门系列——第三篇

• 每篇前言：
• 0.准备工作：
• 1.表结构修改：(关键字alter)
• 2.约束条件：
• • (1)默认约束(default):
  • (2)非空约束(not null):
  • (3)唯一约束(unique key)：
  • (4)主键约束(primary key):
  • (5)自增长约束(auto_increment):
  • (6)外键约束(foreign key):
  • • 实战讲解—外键约束代码操作：
• 3.表关系：
• • (1)一对一关系—主键连接主键
  • (2)多对一（或者称一对多）关系—非主键连接主键
  • (3)多对多关系:
• 4.拓展：
• • 1.视图：（把查询出来的结果变成一张表）
  • 2.索引：
  • • （1）为什么要有索引？
    • （2）那么，为什么sql语句直接查询很慢呢？
    • （3）索引原理：
    • （4）什么情况下使用到索引：
    • （4）什么情况下不要建立索引：
    • （5）使用：
  • 3.mysql正则：（regexp）
  • • 实操讲解：
  • 4.mysql创建拥有指定权限的用户&&修改用户名和密码操作
  • • （1）创建：
    • （2）修改：
    • • ①修改用户名：
      • ②修改密码：

每篇前言：

• 🏆🏆作者介绍：【孤寒者】—CSDN全栈领域优质创作者、HDZ核心组成员、华为云享专家Python全栈领域博主、CSDN原力计划作者

• 🔥🔥本文已收录于三大数据库深入讲解专栏：《三大数据库深入讲解》
• 🔥🔥热门专栏推荐：《Django框架从入门到实战》、《爬虫从入门到精通系列教程》、《爬虫高级》、《前端系列教程》、《tornado一条龙+一个完整版项目》。
• 📝​📝本专栏面向广大程序猿，为的是大家入门并精通开发python项目常用的三大数据库：MySql，Redis，MongoDB。
• 🎉🎉订阅专栏后可私聊进一千多人Python全栈交流群（手把手教学，问题解答）； 进群可领取Python全栈教程视频 + 多得数不过来的计算机书籍：基础、Web、爬虫、数据分析、可视化、机器学习、深度学习、人工智能、算法、面试题等。
• 🚀🚀加入我一起学习进步，一个人可以走的很快，一群人才能走的更远！

---

0.准备工作：

准备一个表，用于实操演示效果：

1.表结构修改：(关键字alter)

1. 修改表名：alter table 表名 rename to 新表名;

温馨小提示：alter修改字段的时候，字段的类型一定要加上。

2. 修改字段名：alter table tb_name change name new_name data_type;

name为要修改的字段名，new_name为新的字段名，注意：字段名后面一定要加字段类型。

3. 修改字段类型：alter table tb_name modify field_name data_type;

注意修改字段类型时一定要注意不要超出范围。

4. 添加字段：alter table tb_name add [column] field_name data_type;

column可加可不加。

小拓展：

添加字段到指定位置（即指定字段下面）：

如果想要添加到第一行，则将after id改为first即可。

5. 删除字段：alter table tb_name drop [column] field_name;

column可加可不加。

2.约束条件：

约束是一种限制，通过对表中的数据做出限制，来确保表中数据的完整性，唯一性。

(1)默认约束(default):

插入数据的时候，如果没有明确为字段赋值，则自动赋予默认值,在没有设置默认值的情况下，默认值为NULL.

create table tb( id int default ‘a’ , name varchar(20));

• 主动使用默认值： insert into 表名 value(default,default)；

• 修改默认值： alter table 表名 modify 字段名 字段类型 default 新的默认值;（使用modify修改字段类型的命令修改默认值）

(2)非空约束(not null):

限制一个字段的值不能为空，Insert的时候必须为该字段赋值。如果不赋值，则使用默认值null，但规定非空，即不能为null，所以会报错。

• 注意：空字符不等于NULL。

create table tb( id int not null, name varchar(20));

删除非空约束，直接在后面加上null。

• 添加非空约束：

alter table tb_name modify field_name data_type not null;

(3)唯一约束(unique key)：

限制一个字段的值不重复，该字段的数据不能出现重复的。
作用：确保字段中值的唯一。

create table tb2( id int unique key, name varchar(20));

• 删除唯一约束： drop index 字段名 on 表名;

(4)主键约束(primary key):

作用： 通常每张表都需要一个主键来体现唯一性，每张表里面只能有一个主键。
特点： 主键 = 非空 + 唯一。

create table  tb3(id int primary key,    name varchar(20));

• 删除主键： alter table 表名 drop primary key;

增加主键（共三种方式）：

1. alter table 表名 add primary key(字段名);

2. alter table tb_name change name new_name data_type primary key;

3. alter table tb_name modify field_name data_type primary key；

(5)自增长约束(auto_increment):

默认值从1开始，每次在最后一个值的基础上增加1。

• 自动编号，和主键组合使用，一个表里面只能有一个自增长(原因：auto_increment 要求用在主键上,一张表就一个主键)

create table  ta( id int primary key auto_increment,    name varchar(20));

• 设置自增长的增长幅度（默认为1）： set @@auto_increment_increment = 指定步长;

(6)外键约束(foreign key):

（建议给下面第三部分—表关系看完之后再来看看本部分，会有茅塞顿开的感觉哦~）

外键：通过外键关联两张表，必须连接另外一张表里面的主键，另外一张表就是主表，本表就是副表。

保持数据的一致性,我有的你一定有， 你没有的， 我绝对没有。
（这里的我指副表；你指主表）

• 主表和副表是因为使用外键连接表才产生的概念。

• 删除外键约束：alter table h2 drop foreign key fk_name;

• 创建外键约束：alter table 表名1 add constraint 表名2 foreign key(id1) references 表名3(id2);
  （创建一个外键约束，自己这张表(h1)里面的id连接另外一张表(h2)里面的id）
  表名1代表要添加外键约束的表；
  表名2代表外键名字；
  id1代表自己表里的字段；
  表名3代表外键表名；
  id2代表主键字段名。

注意：

1. 外键名字可以随便取，因为如果你创建表的时候就增加外键，系统默认会有一个外键名字，可以通过show create table 表名;查看。
2. 自己的这张表连接另外一张表的主键（注意连接的一定要是主键），连接的主表里面没有的数据，添加外键的这张表里也一定不能有。

实战讲解—外键约束代码操作：

• 第一张表有主键，第二张表没主键且没在创建表的时候就增加外键约束，所以下面增加外键：

• 通过命令查看创建的外键约束：

• 删除外键约束：

1. b表中的id_b字段，只能添加 id_a中已有的数据 。
   即插入修改规则： 主表没有的这个字段值，副表也不能有。

2. a表中id_a 被参照的数据， 不能被修改和删除
   即删除规则：先删除副表，副表里面没有的数据，主表才能删除。

3.表关系：

(1)一对一关系—主键连接主键

自己这张表的主键连接另外一张表的主键。通过外键的方式来实现！！！（给主键的字段连接外键）

实操引入：假设有两张表，一张是学生表student，学生表中有学号、姓名、学院，但学生还有些比如电话，家庭住址等比较私密的信息，这些信息不会放在学生表当中，会新建一个学生的详细信息表data1来存放。这时的学生表和学生的详细信息表两者的关系就是一对一的关系，因为一个学生只有一条详细信息。用主键加主键的方式来实现这种关系。

下面是实操讲解（主要目的是讲解，所以我将两张表的字段做了简化）：

• 第一步：学生表的创建
  
  

• 第二步：学生详细信息表的创建：
  注意：通过外键关联两张表，必须连接另外一张表里面的主键，则另外一张表（student）就是主 表 ,本表data1就是副表。
  
  

• 第三步：通过外连接实现一对一：
  这里还体现了外键约束。
  

• 注意：删除数据的时候：
  要先删除副表的，再删除主表的，才能删掉 。如果直接删除主表的将报错。
  

(2)多对一（或者称一对多）关系—非主键连接主键

自己的这张表的非主键连接另外一张表的主键。通过外键的方式实现。（给非主键的字段连接外键）

• 第一步：创建一个没有主键的表：
  没有主键的这张表的非主键连接另外一张有主键的表的主键。则实现了多对一。
  因为：非主键可以重复，主键唯一。
  
• 第二步：创建一个有主键的表：
  
  
• 第三步：通过外连接查看多对一：
  

(3)多对多关系:

思路是非主键连接非主键，但是外键必须连接主键，所以必须有一个中间表，负责结合两张表的主键

联合主键： （1,1）

• 添加联合主键：alter table 表名 add primary key(字段1，字段2)；

实操引入：学生要报名选修课，一个学生可以报名多门课程，一个课程有很多的学生报名，那么学生表和课程表两者就形成了多对多关系。

下面是实操讲解（主要目的是讲解，所以我将两张表的字段做了简化）：

• 第一步：创建第一个表：
  
  
  

• 第二步：创建第二个表：
  

• 第三步：上面已经创建了两张表，还需要一张中间表：
  这张中间表就将上面两张表都连接起来了，实现了多对多：
  
  上面这张图的primary key(s_id,c_id)是创建联合主键

  • foreign key(s_id) references student(id)是关联学生的id
  • foreign key(s_id) references cours(c_id)是关联课程的c_id
    
    但这样查看不直观，所以用连接查询看：
    

4.拓展：

1.视图：（把查询出来的结果变成一张表）

特点：可以引用多张表。是一张虚表！！！

优点：

• 简单化，数据所见即所得

• 安全性，用户只能查询或者修改他们看得到的数据

• 逻辑独立性，可以屏蔽真实表结构变化带来的影响。比如：如果给一张有主键的表建立视图，那么这张表的视图是没有主键的。

缺点：

• 性能较差，简单的查询也会变得复杂

• 修改不方便，复杂的聚合视图基本无法修改

1. 创建视图： create view 视图名字 as select * from 表名；

一张表形成的视图是可以进行增删改查的；对视图进行修改，对应的原表也会跟着修改。

但是多张表（通过连接查询生成的视图）形成的视图不能进行修改操作。

2. 删除视图： drop view 视图名字;

2.索引：

（1）为什么要有索引？

• 原因：sql语句直接查询太慢了！

（2）那么，为什么sql语句直接查询很慢呢？

1. 查询语句写的太烂；

2. 索引失效；

3. 关联查询太多了；

4. 服务器问题。

（3）索引原理：

索引原理可以理解为把数据放进一本本书里面，查询不需要一个个数据查询；而是先找到放所需数据的书，在找页…可以加快查询数据的速度！

（4）什么情况下使用到索引：

1. 主键自动建立唯一索引

2. 频繁查询条件的字段

3. 外键关系自动建立索引

4. 经常需要改变的数据不要建立索引

5. where条件里用到的字段不要创建索引

6. order by 要建立索引

7. 聚合和分组字段也要建立索引

（4）什么情况下不要建立索引：

1. 表记录太少

2. 经常需要增删改的数据不要建立索引

3. 太多重复且平均的字段不要建立索引

（5）使用：

1. 建立索引：
   create index 索引名字 on 表名(字段名);

2. 删除索引：
   drop index 索引名字 on 表名;

3. 显示表里索引：
   show index from 表名;

小知识点：

如果是连接查询
explain 连接查询语句;
这样可以查看查询表的先后顺序。

3.mysql正则：（regexp）

正则表达式大家肯定都熟的不能再熟了（如果不熟的可以去看看Python基础分栏里的正则表达式讲解！），所以此处就简单使用几个常见的说明下如何在MYSQL中使用即可~

符号	含义
^h	以h开头
a$	以a结尾
[^h]	不是h的都可以
+	一个到多个
*	零个到多个
{m,n}	控制次数
.	通配符，任意字符

实操讲解：

准备工作—使用的表：

1. 找到name中含有h的：
   

2. 找到name中以h开头的：
   

3. 找到名字中以a结尾的：
   

4. 只要包含h的都找到：
   

5. 只要是name里不是h的都找到：
   

6. .是通配符，代表任意字符，一个代表一个任意字符
   

7. {m,n}控制次数
   

8. *零个到多个：
   

9. +一个到多个：
   

4.mysql创建拥有指定权限的用户&&修改用户名和密码操作

（1）创建：

1. 创建用户：

 create user'xiaoming'@'%' identified by '123456';

2. 赋予权限：

 grant select on test.student to 'xiaoming'@'%';

• select：赋予查看权限（对应有增删改查四种权限，all表示赋予所有权限）；
• test.student：数据库名.表名（*.*即为整个数据库）；
• ‘xiaoming’@‘%’：赋予权限的用户。

 flush privileges;

• 最后别忘了刷新权限哦~

测试：
以新用户登录即可，只拥有指定数据库，表的指定权限：

mysql -uxiaoming -p123456

（2）修改：

①修改用户名：

1. 要先进入mysql数据库中哦：
   因为用户信息表user就在这个数据库中~

use mysql;

2. 修改用户名：

 update user set user = 'xm' where user = 'xiaoming';

别忘了刷新权限：

flush privileges;

更改成功：

②修改密码：

• 修改密码为1234567：

set password for 'xm'@'%' = password('1234567');

刷新下权限：

 flush privileges;