MySQL02

database
Computer Learning
发布日期:   2023-12-05
更新日期:   2024-01-20
文章字数:   6.3k
阅读时长:   22 分
阅读次数:  

  • 01.MySQL-DQL

    • DQL英文全称是Data Query Language(数据查询)语言），用来查询数据库表中的记录。

    • 关键字：SELECT

    • 重要性：查询这个操作是数据库中最常见、重要的操作，一个正常的业务系统中，查询基本可以占据90%以上

      • 1.比如说CRM系统，每次点击左边的菜单栏都会发起一次或多次查询操作，点击课程管理菜单后会显示课程相关数据信息，前端只负责将数据以好看的样式展示出来，最终的数据都是在数据库中存储和管理的(比如课程中的一条数据对应着数据库表结构当中的一条记录，当我们点击课程管理菜单后，就可以将数据库表结构中的数据查询出来)img

      • 2.页面也允许用户在上面根据条件查询（比如在课程名称中输入Java，就可以查询出课程名称中含有Java关键字的课程，添加条件适用人群为小白学员，就可查询到对应数据,也可查询时间范围，对数据排序，分页展示数据加快页面加载速度，同时提高了用户使用体验）img

    • 语法：

      • select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 hanving 分组后条件列表 order by 排序字段列表 limit 分页参数

      • 基本查询：select 字段列表 from 表名列表（字段列表代表我们要查询表中的哪些字段，字段之间用逗号分隔，from后面跟上表名列表，也就是说我们可以从多张表中进行查询）

      • 条件查询（where）：在基本查询后面可以加上where，where后面跟上条件列表（可以有多个条件）

      • 分组查询（group by）：group by跟上要分组的字段，如果分组之后还要进行条件的筛选，还需要加上关键字having，having之后加的是分组的条件

        • 比如有一张员工表，里面有一个字段是gender性别，要想知道男性员工和女性员工各有多少人，就可以用性别字段进行分组，分完组之后再统计男性女性个多少人

      • 排序查询（order by）：order by后跟上排序的字段（支持对多个字段进行排序）

      • 分页查询（limit）：在sql语句的后面加上一个关键字limit，limit之后跟上分页参数

      • ppt图示

        • img

    • 准备测试数据：数据库db03下操作，新建控制台mysql_02，资料DQL第一个sql脚本双击打开（其中是上节课的表，插上许多数据），cv到idea，全选执行在db02创建一张表，含29条数据

    • 01.DQL-基本查询

      • 语法

        • 查询多个字段：select 字段1,字段2,字段3 from 表名;

        • 查询所有字段（通配符）：select * from 表名;（不推荐：1.不直观，2.性能低）

        • 设置别名：select 字段 [ as 别名1 ], 字段2 [ as 别名2 ] from 表名;（不需要写方括号，只代表可选）

        • 去除重复记录：select distinct 字段列表 from 表名;

      • 需求

        • 题目img

        • 自己写的img

        • 老师的（工作几种？最后的null代表还没有分配职位，职位一共有四种）img

      • tips：

        • 1.写好select 查询的字段之后会报错，不用管，因为没有表名，他不知道指定的字段存不存在，写好from加表名就不会报错了，这些字段已经被识别出来了

        • 2.要写多个或所有字段的话可以先打出selrct from 表名;然后再打字段列表，这样就会有字段和全部字段的提示。

        • 3.以后写项目要查询所有字段推荐写select 字段1,字段2,….. from 表名（先打出后面的），而不推荐select * from 表名：1.不直观2.性能低（课上节省时间会使用*）

        • 4.起别名的as可以省略，但是别名中如果有空格或者特殊符号需要给别名加上引号（单引号/双引号）

      • ppt图示

        • img

    • 02.DQL-条件查询

      • 语法—条件查询：select 字段列表 from 表名 where 条件列表;

      • 条件列表的条件可以有多个，学习条件查询主要就是学会条件的构建方式，并且SQL语句中构建条件的运算符有很多，主要分为比较运算符和逻辑运算符

      • 比较运算符

        • >：大于，>=：大于等于，<：小于，<=：小于等于

        • <> 或 != ：不等于，=：等于

        • between … and … ：在某个范围之内（含最小、最大值）（between后跟上最小值，and后跟上最大值）

        • in(…)：在in之后的列表中的值，多选一

        • like 占位符：模糊匹配（_匹配单个字符，%匹配任意个字符）

        • is null：是null

      • 逻辑运算符：

        • and 或 &&：并且（多个条件同时成立）

        • or 或 || ：或者（多个条件任意一个成立）

        • not 或 ! ：非，不是

      • 需求

        • 题目imgimg

        • 代码（我的和老师的大差不差）img

      • tips

        • 1.select * from tb_emp where name = ‘杨逍’;因为name是字符串，所以杨逍也要用引号引起来

        • 2.null只能用is判断，而不像其他数据类型用=判断

        • 3.查询结果显示台可以看到查询到数据的条数img

        • 4.并列条件and和&&双与的效果是一样的，只不过sql语句一般写and

        • 5.如果有范围可以用between and 来代替>= <= ，日期也能用于比较运算哦

        • 6.如果查询多个确定的值，就可以in(),里面放上这些值即可（满足其中一个即可）

      • ppt图示

        • img

    • 03.DQL-分组查询

      • 先进行聚合函数的讲解：分组查询操作一般会伴随着聚合统计进行

        • 比如说我们要根据员工的性别来进行分组，分别计算男性员工多少人，女性员工多少人，既要根据gender这个字段进行分组，还要进行分组之后的聚合统计操作，这里就会涉及到数据库中的聚合函数

      • 聚合函数：

        • 介绍：将一列数据作为一个整体，进行纵向计算。

        • 语法：select 聚合函数(字段列表) from 表名;

        • 常见聚合函数：

          • count：统计数量

          • max：最大值

          • min：最小值

          • avg：平均值

          • sum：求和

        • 需求

          • 题目img

          • 代码img

        • tips

          • 1.聚合函数不对null值进行运算，所以要统计数据数，要count非空字段，比如说name，username都是非空字段

            • count员工表是id数img

            • count员工表job数（含一个job为null的员工）img

          • 2.count()函数参数里可以放三个类型的东西：1.count(字段)2.count(常量)3.count(*) <–推荐

            • 1.常量可以是数字0，1，2，3…或者是字符串’A’，只要count一个不为null的常量都可以，因为null不参与聚合函数的运算img

            • 2.是通配的意思，通过就可以求这取张表的总数据量，项目开发时推荐使用count(*)，因为MySQL数据库底层专门对此做了优化处理

          • 3.avg()求平均值的返回值是有小数的

        • ppt图示img

      • 分组查询–语法：select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];

      • 需求

        • 题目img

        • 我的代码img

        • 老师的代码img

      • tips

        • 1.在分组查询的字段列表写会报错，因为分组了，再写就没意义了，所以这里可以写的是分组字段和聚合函数 （分组字段后返回的数据是这两类）

        • 2.想要写分组的过滤条件，不能写在where后面，因为在where之后不能写count聚合函数，只能写在关键字having之后加上聚合函数构建的条件

      • 面试题：where与having的区别

        • 1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

        • 2.判断条件不同：where不能√聚合函数进行判断，而having可以。

      • 注意事项：

        • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

        • 执行顺序：where > 聚合函数 > having。

      • ppt图示

        • img

    • 04.DQL-排序查询

      • 语法–排序查询：select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1,字段2 排序方式2… ；

      • 排序方式：1.ASC：升序（默认值）2.DESC：降序

      • 需求

        • 题目img

        • 我的代码img

        • 老师的代码（ASC报黄是因为多余了）img

      • sql语句中只有第一个排序字段相同时，才会根据第二个排序字段进行排序

      • ppt图示

        • img

    • 05.DQL-分页查询

      • 场景：分页查询非常常见，如果说我们一次性查询出来的数据比较多，就会进行分页展示，以提高用户的使用体验

        • 比如说京东的搜索页面，它的最下方就有一个这样的分页条，数据分页之后，用户点击对应的页码，就可以加载出这一页的最新数据img

        • 2.再比如说CRM的课程管理菜单，用户在查看数据的时候，只需要点击对应的页码，就可以加载出这一页的数据img

      • 语法-分页查询：select 字段列表 from 表名 limit 起始索引,查询记录数;

        • 查询语句后面加上关键字limit，limit之后指定两个分页参数，用逗号分隔（起始索引：指的是我们要从哪一条数据往后进行查询，起始索引是从零开始的。查询记录数：指的是这一页需要展示多少条数据）

      • 需求

        • 题目img

        • 我的代码（×：起始页码是1，所以第一条和第二条一样，下面也是，多了，需求2，3，4起始索引应该是0，5，10）img

        • 修改后+查询结果展示img

      • 起始索引 = （页码 - 1） * 每页展示记录数

      • tip：查询记录数就是查询结果会展示的记录数，从起始索引开始展示，表中的每条数据的索引就是正常的序号-1

      • 注意事项：

        • 1.起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数。

          • 将来前端并不会把起始索引传过来，它传递的都是页码，比如说查询第1，2，3…页，传递过来后，我们需要在执行SQL语句时，把页码换算成对应的起始索引

        • 2.分页查询是数据库的方言，不同的数据库有着不同的实现，MySQL中是LIMIT。

          • Oracle数据库是通过row number伪列配合子查询实现的

        • 3.如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。（起始索引默认为0）

    • 06.DQL-案例

      • 案例一

        • 题目img

        • 我的代码img

        • 写代码要看啊页面原型的需求img

        • 老师的代码img

        • tip：1.模糊匹配可以在要模糊查询的字前后都加上%2.选中SQL语句，ctrl+Alt+L对选中SQL语句格式化3.完成案例时，一定要按照页面原型和需求文档来编写对应的SQL

      • 案例二

        • 题目： 这类的需求也叫弧形报表，就是做数据统计，然后再将统计好的数据以可视化的形式展示出来img

        • 分析：我们只需要统计男性员工和女性员工多少人就可以了，我们把数据统计出来后返回给前端数据，前端数据再借助一些现成的报表组件库就可以完成报表的渲染展示了

        • 这里以ECharts为例，Echarts是百度开源的一个组件库，现在已经是Apache的孵化项目，所以域名是apache.org，官网是echarts.apache.org，点击所有事例就能看到Echarts给我们提供的所有报表组件（折线图/柱状图/饼状图），点开后就可以看到左侧的JS源码和右侧的效果预览图

        • 去除底部的data的三栏数据，修改剩下两栏的name属性值，修改series的name属性值，完成了一个简单的报表img

        • 而这部分最核心的就是数据，需要从数据库中查询出来

        • 我的代码imgimg

        • 但是需要的是男和女字样，所以还要改成男性员工，女性员工，需要用if函数：if(条件表达式,true取值,false取值)，但是这样写表头比较长，可以跟上性别作为别名imgimg

        • 第二个案例需要转化四个字样，就可以用另外一个流程控制函数case()：case 表达式 when 值1 then 结果1 when 值2 then 结果2 … else … end（类似Java的switch函数），写完打上括号起别名即可imgimg

      • 案例小结图示

        • img

      • DQL小结

        • img

  • 02.多表设计

    • 概述：项目开发中，在进行数据库表结构设计时，会根据业务需求以及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本分为三种：

      • 电商网站中，有很多的商品数据，每个商品都有一个品牌，比如说华为或者联想，然而某一个商品也要归属于商品分类中的一类，而且一个商品有很多规格，而一种规格可能很多商品都有，业务关系错综复杂imgimg

    • 一对多（多对一）

      • 需求：

        • 根据页面原型及需求文档，完成部门及员工模块的表结构设计（一个部门有多个员工，但一个部门只能属于一个员工）img

        • 我写的img

        • 老师的img

        • 完善先前员工表的归属部门，类型与部门表的id一样img

        • 这样一来，员工表的dept_id关联的就是部门表的主键，比如说2号部门是教研部img

        • 这层关系中：部门表示一的一方，员工表示多的一方，一个部门会对应多个员工，但一个员工只能归属多个部门，所以在一对多的关系中，我们也把一的一方叫做父表，多的一方叫做子表，因为一个父亲可以有多个儿子，但一个儿子只能有有一个父亲

        • 总结：只需要在数据库表结构中多的一方来增加一个字段关联一的一方的主键就可以了

      • 外键

        • 多表问题分析

          • 插入测试数据（资料第一个文件，cvINSERT语句，选中执行）

            • img

          • 删除id为一的部门学工部，但是员工表中id为一的员工依然存在

            • 图示imgimg

          • 现象：部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整】不一致问题

          • 问题原因分析：目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。（刚刚只在逻辑上完成了dept_id和dept的统一，但是数据库层面并未建立任何联系）

          • 解决办法：外键约束（我们所学的第五种约束）

        • 外键语法

          • 创建表时指定：create table 表名( 字段名 数据类型,…[constraint] [外键约束] foreign key(外键字段名) references 主表(字段名) );

          • 建完表后，添加外键：alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);

        • 图形化界面添加外键：（首先先恢复数据，否则会报错-数据不完整）

          • 1.右击子表表格，选择Modify Table修改表

          • 2.点击上面的Foreign Keys，点击加号添加外键

          • \3. Name指的是要添加的外键字段名字，Target Table指的是要关联的主表

          • 4.旁边的Colums的from填子表的要关联的字段，To填父表要关联的字段

          • 图形化操作图示img

        • 表格中带蓝色钥匙字段的就是外键

          • img

        • 此时再次删掉部门表的部门会报错：不能删掉父表的一列，因为有一个外键约束（tb_emp的dept_id字段）

          • img

        • 而删掉五号部门就可以，因为emp表中的员工没有任何一个部门关联五号部门

        • 物理外键（用的很少，甚至禁用）

          • 概念：使用foreign key定义外键关联另外一张表。（刚刚添加的外键）

          • 缺点：

            • 1.影响增删改效率（需要检查外键关系）。

            • 2.仅用于单节点数据库，不适用于分布式、集群场景。（比如大项目分库分表）

            • 3.容易引发数据库的死锁问题，消耗性能。

        • 逻辑外键（业务开放中推荐使用）

          • 概念：在业务层逻辑中，解决外键关联（数据库层面刚刚建立的外键delete按钮直接删掉，删掉之后就没有物理外键关联了，这样就解决了物理外键的几个问题，此时就需要在业务代码中通过代码来保证数据的一致性和完整性）

          • 通过逻辑外键，就可以很方便的解决上述问题。

    • 一对一

      • 案例：用户与身份证信息的关系

        • 一张用户信息表，基本信息（id、姓名、性别、手机号、学历）和身份信息（生日，身份证号，签发机关，有效期开始时间、结束时间），如果在业务系统中基本信息的查询频率特别高，但身份信息查询频率很低，出于查询效率的考虑，就可以将这张大表拆分成两张小表img

        • 一张是用户基本信息表(tb_user)，一张是用户身份信息表（tb_user_card），这两张表就是一对一的关系，一个用户只能关联一个身份证号，一个身份证号又只能对应一个用户，img

        • 那如何在数据库层面体现一对一的关系呢，其实只要把它看作一种特殊的一对多，在任意一张表添加外键即可，这个外键要关联另外一方的主键，为了保证一对一的关系，可以为这个外键加上约束UNIQUE唯一约束，这样就保证了一个用户不能对应两个身份证img

      • 关系：一对一关系：多用于多表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

      • 实现：在任意一方加入外键，关联另外一方的主键，并设置外键为唯一的（UNIQUE）

      • 例如查询 白眉鹰王用户的身份证号：查询白眉鹰王的id根据用户id到身份表查询身份证号即可

      • 此时修改身份表的用户id都为一，就会报错

        • img

    • 多对多

      • 案例：学生与课程的关系

      • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

      • 但是多对多就不能像一对多或者多对多一样添加外键，因为无法在一个单元格放入多个值

        • img

      • 实现：建立第三章中间表，中甲表至少包含两个外键，分别关联两方主键

        • 比如说查询黛绮丝，id为1的学生选修了几门课程，在学生课程关系表一目了然，courseid是123，在课程表对应Java，PHP，MySQLimg

        • 再比如说想看有几个学生选了Java课程，拿着Java课程的id：1取学生课程关系表寻找也能一目了然：两位位id–1和2，就可以根据这个studentid当学生表查找数据，最终就知道黛绮丝和谢逊选修了Javaimg

      • 例如

        • 建表语句：关系表通过设置外键关联了学生表和课程表（物理外键，但是项目开放中物理外键已经很少用了）img

      • 显示关系图

        • ctrl多选选中三张表，选择Diagrams，再选择Show Visualization查看img

        • 关系图img

    • 案例

      • 题目img

      • 我写的img

      • 打开资料中的苍穹外卖页面原型（管理后台：登录.html打开，点击登录进去；用户端：点餐.html打开）

      • 介绍：餐厅的老板和员工都可以打开管理端，登录到管理后台，配置菜品分类，套餐分类等，当后台配置后，用户就可以基于微信小程序进行点餐了，

        • 点菜界面左侧是菜品分类，右侧就是该分类的菜品列表，点击加号就可以完成点餐列表img

      • 步骤：（要掌握）

        • 1.阅读页面原型及需求文档，分析各个模块涉及到的表结构，及表结构之间的关系。

        • 2.根据页面原型及需求文档，分析各个表结构中具体的字段及约束。

      • 1.关系分析：

        • 分类与菜品：一个分类可以有多菜品，但是一个菜品只能有一个分类，是一对多的关系，所以在多的一方添加外键即可，也就是分类的idimg

        • 分类和套餐的关系：一个分类对应多个套餐，但是一个套餐只能有一个分类（一对多）img

        • 添加套餐时，冠以选择关联的菜品，那就说明一个套餐对应多个菜品，但是一个菜品也能添加在不同的套餐中，所以菜品和套餐是多对多的关系，在数据库层面需要通过建立一张中间表来实现，中间表有两个字段，分别是两张表的主键img

        • 最终的关系示意图img

      • 2.字段类型及约束分析：

        • 分类管理页面可以看到有哪些字段，往后拉能看到页面说明

        • 基于图形化界面创建表（工作绝大多数情况下都是根据图形化界面来操作，简单方便）

        • 一、分类表category

          • 1.添加主键id，类型无符号整型，主键约束，注释：主键ID

          • 2.菜品/套餐名称限制在=字符范围：2-20字符，变相的说明了该字段是非空的，页面原型和需求中没有说明分类是唯一的，我们不确定的话可以找产品经理做一个确认，有可能没有清晰表示出来，确定好这个字段是唯一的，就勾选上unique唯一约束，有不清楚的字段一定要确认，非空，这里先加上唯一约束

          • 3.分类类型只有两类，直接用标识代替即可（无符号小范围整数），非空

          • 5.状态就两个值，要么启用，要么停用，这里用tinyint类型，无符号，约定0是停用，1是启用，默认就为0，需要勾上非空，有默认值，而且二选一

          • 6.最后添加两个基础字段create_time创建时间和update_time修改时间，非空，类型为datetime，至此建好了分类表img

        • 二、菜品表dish（字段说明在新建菜品中，口味选择暂不考虑）（这里用的逻辑外键）

          • 1.菜品分类关联分类表的主键：category_id,类型与分类表主键类型一致–int unsigned

          • 2.价格长度限制在8位，还有两位小数，那就用deciaml(8,2)：总共8位，保留两位小数

          • 3.图片并非储存图片，而是图片的存储路径，而且长度比较长，用varchar(300)img

        • 三、套餐表setmeal（字段说明在新建套餐页面中）（完成此表即建立好了分类和菜品，分类和套餐的关系）

          • 套餐菜品在中间表创建，不在套餐表创建img

        • 套餐菜品关系表setmeal_dish

          • 基础字段id主键要有

          • 关联的两张表的主键，setmeal_id字段和dish_id字段

          • 但是还有一个份数字段需要添加，因为在添加套餐时需要指定添加菜品份数，用tinyint unsigned即可img

          • 这里中间表就不用添加create_time和update_time的基础字段了img

      • 最后梳理一下这四张表的关系：

        • 1.分类表与菜品表是一对多的关系，一个分类下对应多个菜品，菜品表中有个外键字段category_id，关联的就是分类表的主键img

        • 2.分类表与套餐表也是一对多的关系，一个分类下对应多个套餐，所以套餐表中有个外键字段category_id，关联的就是分类表的主键img

        • 3.而对于菜品和套餐是多对多的关系，所以建立了一张套餐菜品的中间表，中间表就记录了每一个套餐管理练的菜品和这个关联的菜品的份数

    • 小结

      • 一对一可以添加外键unique保证唯一性img
文章作者: Ceasteo
文章链接: https://starlike-daydream.github.io/Cesteo.github.io/Cesteo.github.io/mysql02/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Ceasteo !
database
评论
  目录