0


三战MySQL数据库【终极篇】

MYSQL入门系列——第三篇

每篇前言:

  • 🏆🏆作者介绍:【孤寒者】—CSDN全栈领域优质创作者、HDZ核心组成员、华为云享专家Python全栈领域博主、CSDN原力计划作者
  • 🔥🔥本文已收录于三大数据库深入讲解专栏:《三大数据库深入讲解》
  • 🔥🔥热门专栏推荐:《Django框架从入门到实战》、《爬虫从入门到精通系列教程》、《爬虫高级》、《前端系列教程》、《tornado一条龙+一个完整版项目》。
  • 📝​📝本专栏面向广大程序猿,为的是大家入门并精通开发python项目常用的三大数据库:MySql,Redis,MongoDB。
  • 🎉🎉订阅专栏后****可私聊进一千多人Python全栈交流群(手把手教学,问题解答);进群可领取Python全栈教程视频 + 多得数不过来的计算机书籍:基础、Web、爬虫、数据分析、可视化、机器学习、深度学习、人工智能、算法、面试题等。
  • 🚀🚀加入我一起学习进步,一个人可以走的很快,一群人才能走的更远!

在这里插入图片描述

0.准备工作:

准备一个表,用于实操演示效果:
在这里插入图片描述
在这里插入图片描述

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

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

在这里插入图片描述

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

  1. 修改字段名:alter table tb_name change name new_name data_type;

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

在这里插入图片描述

  1. 修改字段类型:alter table tb_name modify field_name data_type;

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

在这里插入图片描述

  1. 添加字段:alter table tb_name add [column] field_name data_type;

column可加可不加。

在这里插入图片描述
小拓展:

添加字段到指定位置(即指定字段下面):
在这里插入图片描述
如果想要添加到第一行,则将after id改为first即可。

  1. 删除字段: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):

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

createtable  tb3(id intprimarykey,    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 要求用在主键上,一张表就一个主键)
createtable  ta( id intprimarykeyauto_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. .是通配符,代表任意字符,一个代表一个任意字符在这里插入图片描述

在这里插入图片描述

  1. {m,n}控制次数在这里插入图片描述在这里插入图片描述
  2. *零个到多个:在这里插入图片描述
  3. +一个到多个:在这里插入图片描述

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

(1)创建:

  1. 创建用户:
createuser'xiaoming'@'%' identified by'123456';

在这里插入图片描述
2. 赋予权限:

grantselecton test.student to'xiaoming'@'%';
  • select:赋予查看权限(对应有增删改查四种权限,all表示赋予所有权限);
  • test.student:数据库名.表名(*.*即为整个数据库);
  • ‘xiaoming’@‘%’:赋予权限的用户。
 flush privileges;
  • 最后别忘了刷新权限哦~

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

mysql -uxiaoming -p123456

(2)修改:

①修改用户名:

  1. 要先进入mysql数据库中哦: 因为用户信息表user就在这个数据库中~
use mysql;
  1. 修改用户名:
updateusersetuser='xm'whereuser='xiaoming';

别忘了刷新权限:

flush privileges;

更改成功:
在这里插入图片描述

②修改密码:

  • 修改密码为1234567:
set password for'xm'@'%'= password('1234567');

刷新下权限:

 flush privileges;

本文转载自: https://blog.csdn.net/qq_44907926/article/details/122323976
版权归原作者 孤寒者 所有, 如有侵权,请联系我们删除。

“三战MySQL数据库【终极篇】”的评论:

还没有评论